--
--  Évolutions base de données nouvelle version WikiLerni avec notamment ajout des quizs groupés.
--

CREATE TABLE `Groups` (
  `id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `introduction` text COLLATE utf8mb4_unicode_ci,
  `publishingAt` datetime DEFAULT NULL COMMENT 'If null, the questionnaire with the questions for each element of the group is not published, but its elements may be.',
  `language` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fr',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `CreatorId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `Groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `Questionnaires_slug_unique` (`slug`),
  ADD KEY `CreatorId` (`CreatorId`);

ALTER TABLE `Groups`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `Answers` CHANGE `QuestionnaireId` `QuestionnaireId` INT(11) NULL DEFAULT NULL;
ALTER TABLE `Answers` ADD `GroupId` INT(11) NULL DEFAULT NULL AFTER `QuestionnaireId`;
ALTER TABLE `Answers` ADD CONSTRAINT `Answers_ibfk_9` FOREIGN KEY (`GroupId`) REFERENCES `Groups`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `Questionnaires` ADD `rankInGroup` INT(2) UNSIGNED NULL DEFAULT NULL COMMENT 'Allows you to classify the questionnaire if it belongs to a group.' AFTER `estimatedTime`;
ALTER TABLE `Questionnaires` ADD `GroupId` INT(11) NULL DEFAULT NULL AFTER `CreatorId`;
ALTER TABLE `Questionnaires` ADD CONSTRAINT `Questionnaires_ibfk_2` FOREIGN KEY (`GroupId`) REFERENCES `Groups`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `Subscriptions`
  DROP `noticeOk`;

ALTER TABLE `Users`
  DROP `newsletterOk`;