CREATE DATABASE IF NOT EXISTS stackoverflow_29333110;
USE stackoverflow_29333110;

DROP TABLE IF EXISTS `participer`;
DROP TABLE IF EXISTS `versionquestionnaire`;
DROP TABLE IF EXISTS `questionnaire`;
DROP TABLE IF EXISTS `utilisateurs`;

CREATE TABLE IF NOT EXISTS `participer` (
  `statut_Invitation` tinyint(1) NOT NULL,
  `NumVersion` int(11) NOT NULL,
  `InviteCode` varchar(255) NOT NULL,
  `IdQuest` int(11) NOT NULL,
  PRIMARY KEY (`NumVersion`,`InviteCode`,`IdQuest`),
  KEY `FK_Participer_NumVersion` (`NumVersion`),
  KEY `FK_Participer_IdQuest` (`IdQuest`),
  KEY `FK_Participer_InviteCode` (`InviteCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `participer` (`statut_Invitation`, `NumVersion`, `InviteCode`, `IdQuest`) VALUES
(0, 2, '2548', 1),
(0, 2, '8742', 1);



CREATE TABLE IF NOT EXISTS `questionnaire` (
  `IdQuest` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(256) DEFAULT NULL,
  `DateCreation` date,
  PRIMARY KEY (`IdQuest`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


INSERT INTO `questionnaire` (`IdQuest`, `Nom`, `DateCreation`) VALUES
(1, 'Parions Sport', '2015-03-23'),
(2, 'GPS', '2015-03-23');

CREATE TABLE IF NOT EXISTS `utilisateurs` (
  `InviteCode` varchar(255) NOT NULL,
  `Email` varchar(25) NOT NULL,
  `DateNaissance` date DEFAULT NULL,
  `Ville` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`InviteCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `utilisateurs` (`InviteCode`, `Email`, `DateNaissance`, `Ville`) VALUES
('1235314', 'bocchi@gmail.com', NULL, NULL),
('2548', 'bocchiTEST@gmail.com', NULL, NULL),
('337752493652424404824466004444846460026', 'dylan@gmail.com', NULL, NULL),
('53131172170670664482420846024208824402', 'dylan2@gmail.com', NULL, NULL),
('5446544', 'lool', NULL, NULL),
('8742', 'max@gmail.com', NULL, NULL);

-- --------------------------------------------------------



CREATE TABLE IF NOT EXISTS `versionquestionnaire` (
  `NumVersion` int(11) NOT NULL,
  `DateExpiration` date DEFAULT NULL,
  `SommeNote` float DEFAULT NULL,
  `NbReponses` int(11) DEFAULT NULL,
  `IdQuest` int(11) NOT NULL,
  PRIMARY KEY (`NumVersion`,`IdQuest`),
  KEY `FK_VersionQuestionnaire_IdQuest` (`IdQuest`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `versionquestionnaire` (`NumVersion`, `DateExpiration`, `SommeNote`, `NbReponses`, `IdQuest`) VALUES
(1, '2015-03-26', 120, 2, 1),
(1, '2015-03-30', 100, 1, 2),
(2, '2015-03-26', 210, 3, 1),
(2, '2015-03-30', 300, 4, 2);


ALTER TABLE `participer`
  ADD CONSTRAINT `FK_Participer_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `versionquestionnaire` (`IdQuest`),
  ADD CONSTRAINT `FK_Participer_InviteCode` FOREIGN KEY (`InviteCode`) REFERENCES `utilisateurs` (`InviteCode`),
  ADD CONSTRAINT `FK_Participer_NumVersion` FOREIGN KEY (`NumVersion`) REFERENCES `versionquestionnaire` (`NumVersion`);


ALTER TABLE `versionquestionnaire`
  ADD CONSTRAINT `FK_VersionQuestionnaire_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `questionnaire` (`IdQuest`);

DELETE FROM `versionquestionnaire` WHERE `versionquestionnaire`.`NumVersion` = 1 AND `versionquestionnaire`.`IdQuest` = 1;

DROP DATABASE stackoverflow_29333110;