CREATE TABLE IF NOT EXISTS `QuizState` (
  `Code` INT NOT NULL,
  `Description` VARCHAR(30) NULL,
  PRIMARY KEY (`Code`))
ENGINE = InnoDB;

DELETE FROM QuizState;
INSERT INTO QuizState (Code,Description) VALUES (0,'Draft');
INSERT INTO QuizState (Code,Description) VALUES (1,'Completed');
INSERT INTO QuizState (Code,Description) VALUES (2,'Candidate');
INSERT INTO QuizState (Code,Description) VALUES (10,'Published');
INSERT INTO QuizState (Code,Description) VALUES (98,'Archived');
INSERT INTO QuizState (Code,Description) VALUES (99,'Deleted');

ALTER TABLE Quizzes ADD State INT NOT NULL DEFAULT 0;
ALTER TABLE Quizzes ADD Link VARCHAR(255) NULL;
ALTER TABLE QuizResult ADD FinalPoints DOUBLE NULL;
ALTER TABLE QuizResult ADD NotAnsweredQuestsNum INT NULL;
ALTER TABLE QuizResult ADD NotValuatedQuestsNum INT NULL;
ALTER TABLE QuizResultQuestion ADD Points DOUBLE NULL;

ALTER TABLE Person ADD CONSTRAINT FK_PersonRole FOREIGN KEY (UserRole) REFERENCES PersonRole(ID); 
ALTER TABLE Quizzes ADD CONSTRAINT FK_QuizState FOREIGN KEY (State) REFERENCES QuizState(Code);
ALTER TABLE QuizSession ADD CONSTRAINT FK_Person FOREIGN KEY (PersonID) REFERENCES Person(ID); 
ALTER TABLE QuizSession ADD CONSTRAINT FK_QuizResult FOREIGN KEY (QuizID) REFERENCES QuizResult(ID); 
--ALTER TABLE QuizResultReport ADD CONSTRAINT FK_QuizResult FOREIGN KEY (ID) REFERENCES QuizResult(ID);



INSERT INTO Person (PersonName,UserIdentity,UserPassword,UserRole,IsEnabled) SELECT * FROM (SELECT 'Administrator' AS PersonName,'admin' AS UserIdentity,'21232f297a57a5a743894a0e4a801fc3' AS UserPassword,1 AS UserRole,1 AS IsEnabled) AS tmp WHERE NOT EXISTS (SELECT UserIdentity FROM Person WHERE UserIdentity = 'admin');
INSERT INTO Person (PersonName,UserIdentity,UserPassword,UserRole,IsEnabled) SELECT * FROM (SELECT 'Anonymous' AS PersonName, 'anonymous@anonymous.nowhere' AS UserIdentity,'294de3557d9d00b3d2d8a1e6aab028cf' AS UserPassword,2 AS UserRole,1 AS IsEnabled) AS tmp WHERE NOT EXISTS (SELECT UserIdentity FROM Person WHERE UserIdentity = 'anonymous@anonymous.nowhere');

UPDATE Settings SET DbVersion = 2
