-- -----------------------------------------------------
-- Table `Quizzes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Quizzes` (
  `QZ_ID` INT NOT NULL AUTO_INCREMENT,
  `quizname` VARCHAR(255) NOT NULL,
  `Title` VARCHAR(1024) NOT NULL,
  `IntroText` TEXT NULL,
  `EpiText` TEXT NULL,
  `NumQuestions` INT NOT NULL,
  `Author` VARCHAR(255) NULL,
  `Argument` VARCHAR(255) NULL,
  `Season` VARCHAR(255) NULL,
  `DateCreated` DATETIME NULL,
  `DateModified` DATETIME NULL,
  PRIMARY KEY (`QZ_ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Person`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Person` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `PersonName` VARCHAR(255) NOT NULL,
  `UserIdentity` VARCHAR(255) NOT NULL,
  `UserPassword` VARCHAR(255) NOT NULL,
  `UserRole` INT NOT NULL,
  `Info` TEXT NULL,
  `IsEnabled` TINYINT(1) NOT NULL,
  `DateCreated` DATETIME NULL,
  `DateModified` DATETIME NULL,
  `DateLastAccess` DATETIME NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `PersonRole`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `PersonRole` (
  `ID` INT NOT NULL,
  `RoleName` VARCHAR(80) NOT NULL,
  `RoleDescription` VARCHAR(500) NULL,
  `IsEnabled` TINYINT(1) NOT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Categories`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Categories` (
  `QZ_ID` INT NOT NULL,
  `CategoryName` VARCHAR(80) NOT NULL,
  `Description` TEXT NULL,
  `MaxQuestForQuiz` INT NULL,
  `IsRandom` TINYINT(1) NULL,
  PRIMARY KEY (`CategoryName`, `QZ_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Questions`
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `Questions` (
  `QZ_ID` INT NOT NULL,
  `Num` INT NOT NULL,
  `CategoryName` VARCHAR(80) NOT NULL,
  `TextQuestion` MEDIUMTEXT NOT NULL,
  `Type` INT NOT NULL,
  `Weight` FLOAT NOT NULL,
  `Include` TINYINT(1) NOT NULL,
  `TextFormat` INT NOT NULL,
  `SortAnswers` TINYINT(1) NOT NULL,
  `TextAbove` MEDIUMTEXT NULL,
  `TextBelow` MEDIUMTEXT NULL,
  `Remark` MEDIUMTEXT NULL,
  `PutSeparator` TINYINT(1) NULL,
  `Timeout` INT NULL,
  `Html` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `Num`),
  INDEX `Category_FK_idx` (`CategoryName` ASC, `QZ_ID` ASC),
  CONSTRAINT `Quest_FK`
    FOREIGN KEY (`QZ_ID`)
    REFERENCES `Quizzes` (`QZ_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Category_FK`
    FOREIGN KEY (`CategoryName` , `QZ_ID`)
    REFERENCES `Categories` (`CategoryName` , `QZ_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ChoiceAnswers`
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `ChoiceAnswers` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `TypeOfAns` INT NOT NULL,
  `SingleAns` TINYINT(1) NOT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`),
  CONSTRAINT `ChoiceAns_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ChoiceAnswerItems`
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `ChoiceAnswerItems` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `NumAns` INT NOT NULL,
  `TextFormat` INT NOT NULL,
  `Ans` MEDIUMTEXT NOT NULL,
  `RightAns` TINYINT(1) NOT NULL,
  `Hint` MEDIUMTEXT NULL,
  `Rem` MEDIUMTEXT NULL,
  `PointsIn` INT NULL,
  `PointsOut` INT NULL,
  `Html` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
  CONSTRAINT `ChoiceItems_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `ChoiceAnswers` (`QZ_ID` , `NumQst`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BooleanAnswerItems`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BooleanAnswerItems` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `NumAns` INT NOT NULL,
  `Sentence` MEDIUMTEXT NOT NULL,
  `TrueAns` TINYINT(1) NOT NULL,
  `TextFormat` INT NOT NULL,
  `Html` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
  CONSTRAINT `BoolAns_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FillGapAnswers`
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `FillGapAnswers` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `Phrase` MEDIUMTEXT NOT NULL,
  `Type` INT NOT NULL,
  `TextFormat` INT NOT NULL,
  `Html` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`),
  CONSTRAINT `FillGap_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `MatchingAnswers`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `MatchingAnswers` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `Type` INT NOT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`),
  CONSTRAINT `Match_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `PairNames`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `PairNames` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `NumAns` INT NOT NULL,
  `LeftItem` MEDIUMTEXT NOT NULL,
  `RightItem` MEDIUMTEXT NOT NULL,
  `TextFormat` INT NOT NULL,
  `Html` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
  CONSTRAINT `PairNames_FK`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `MatchingAnswers` (`QZ_ID` , `NumQst`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CustomQuestionTypes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `CustomQuestionTypes` (
  `QZ_ID` INT NOT NULL,
  `CustomName` VARCHAR(255) NOT NULL,
  `Description` TEXT NULL,
  `Version` INT NULL,
  `Subversion` INT NULL,
  `IconData` MEDIUMTEXT NULL,
  `IncludeConfirmButton` TINYINT(1) NULL,
  `ValuateRightWrong` TINYINT(1) NULL,
  `MaxScore` INT NULL,
  `MinScore` INT NULL,
  `JscriptCode` MEDIUMTEXT NULL,
  `HtmlCode` MEDIUMTEXT NULL,
  PRIMARY KEY (`QZ_ID`, `CustomName`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CustomParameters`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `CustomParameters` (
  `QZ_ID` INT NOT NULL,
  `CustomName` VARCHAR(255) NOT NULL,
  `ParamName` VARCHAR(255) NOT NULL,
  `IsEncrypt` TINYINT(1) NOT NULL,
  `Type` INT NOT NULL,
  `DefValue` VARCHAR(255) NULL,
  PRIMARY KEY (`QZ_ID`, `CustomName`, `ParamName`),
  CONSTRAINT `FK_Params`
    FOREIGN KEY (`QZ_ID` , `CustomName`)
    REFERENCES `CustomQuestionTypes` (`QZ_ID` , `CustomName`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CustomHeadTags`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `CustomHeadTags` (
  `QZ_ID` INT NOT NULL,
  `CustomName` VARCHAR(255) NOT NULL,
  `FileName` VARCHAR(255) NOT NULL,
  `Content` MEDIUMTEXT NOT NULL,
  `ContentType` INT NOT NULL,
  PRIMARY KEY (`FileName`, `CustomName`, `QZ_ID`),
  INDEX `FK_CustomHeadTags_idx` (`QZ_ID` ASC, `CustomName` ASC),
  CONSTRAINT `FK_CustomHeadTags`
    FOREIGN KEY (`QZ_ID` , `CustomName`)
    REFERENCES `CustomQuestionTypes` (`QZ_ID` , `CustomName`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CustomAnswers`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `CustomAnswers` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `CustomName` VARCHAR(255) NOT NULL,
  `ParamName` VARCHAR(255) NOT NULL,
  `ParamValue` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`, `CustomName`, `ParamName`),
  INDEX `FK_CustomAns_To_CustomParams_idx` (`QZ_ID` ASC, `CustomName` ASC, `ParamName` ASC),
  CONSTRAINT `FK_CustomAns_To_Question`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_CustomAns_To_CustomParams`
    FOREIGN KEY (`QZ_ID` , `CustomName` , `ParamName`)
    REFERENCES `CustomParameters` (`QZ_ID` , `CustomName` , `ParamName`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Slides`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Slides` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `SlideName` VARCHAR(80) NOT NULL,
  `Content` LONGTEXT NOT NULL,
  `TextFormat` INT NOT NULL,
  `BeforeQst` TINYINT(1) NOT NULL,
  `IsExternal` TINYINT(1) NOT NULL,
  `HtmlFileName` VARCHAR(255) NULL,
  `SpeechFileName` VARCHAR(255) NULL,
  `Timeout` INT NULL,
  PRIMARY KEY (`QZ_ID`, `NumQst`, `SlideName`),
  CONSTRAINT `FK_Slide_To_Question`
    FOREIGN KEY (`QZ_ID` , `NumQst`)
    REFERENCES `Questions` (`QZ_ID` , `Num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `SlideDocs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SlideDocs` (
  `QZ_ID` INT NOT NULL,
  `NumQst` INT NOT NULL,
  `SlideName` VARCHAR(80) NOT NULL,
  `DocFileName` VARCHAR(255) NOT NULL,
  `LinkName` VARCHAR(500) NOT NULL,
  PRIMARY KEY (`DocFileName`, `QZ_ID`, `NumQst`, `SlideName`),
  INDEX `FK_Doc_To_Slide_idx` (`QZ_ID` ASC, `NumQst` ASC, `SlideName` ASC),
  CONSTRAINT `FK_Doc_To_Slide`
    FOREIGN KEY (`QZ_ID` , `NumQst` , `SlideName`)
    REFERENCES `Slides` (`QZ_ID` , `NumQst` , `SlideName`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `QuizSession`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `QuizSession` (
  `PersonID` INT NOT NULL,
  `SessionID` VARCHAR(36) NOT NULL,
  `DateCreated` DATETIME NOT NULL,
  `IpLogin` VARCHAR(46) NULL,
  `SessionData` MEDIUMTEXT NULL,
  `DateLastUpdate` DATETIME NULL,
  `QuizID` INT NULL,
  `IpLogout` VARCHAR(46) NULL,
  `IsRecoverable` TINYINT(1) NULL,
  PRIMARY KEY (`PersonID`,`SessionID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Quiz_Result`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `QuizResult` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `QuizName` VARCHAR(200) NOT NULL,
  `QuizTitle` VARCHAR(200) NOT NULL,
  `QuizID` INT NULL,
  `UserName` VARCHAR(200) NOT NULL,
  `UserLogin` VARCHAR(200) NOT NULL,
  `DateCompleted` DATETIME NOT NULL,
  `DateReceived` DATETIME NOT NULL,
  `QuestsNum` INT NOT NULL,
  `HighestMark` INT NOT NULL,
  `FinalMark` DOUBLE NOT NULL,
  `TotalTime` INT NOT NULL,
  `ElapsedTime` INT NOT NULL,
  `RightQuestsNum` INT NOT NULL,
  `WrongQuestsNum` INT NOT NULL,
  `NumOfRetake` INT NOT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Quiz_Result_Report`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `QuizResultReport` (
  `ID` INT NOT NULL,
  `Report` MEDIUMTEXT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `QuizResultQuestion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `QuizResultQuestion` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `IdQuizResult` INT NOT NULL,
  `QuestNum` INT NOT NULL,
  `QuestType` INT NOT NULL,
  `Weight` DOUBLE NOT NULL,
  `ShortTextQuestion` VARCHAR(1000) NULL,
  `Valid` INT NOT NULL,
  `Score` INT NOT NULL,
  `MaxScore` INT NOT NULL,
  `MinScore` INT NOT NULL,  
  PRIMARY KEY (`ID`),
  CONSTRAINT `FK_ResultQuestion_To_Result`
    FOREIGN KEY (`IdQuizResult`)
    REFERENCES `QuizResult` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `QuizResultAnswer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `QuizResultAnswer` (
  `IdResultQuestion` INT NOT NULL,
  `AnswerNum` INT NOT NULL,
  `Choice` VARCHAR(500) NOT NULL,
  `Valuation` VARCHAR(500) NOT NULL,
  `IsGuess` BIT NOT NULL,
  `Score` INT NULL,
  `AdditionalText` VARCHAR(1000) NULL,
  `ShortTextAnswer` VARCHAR(1000) NULL,
  `ShortTextRemark` VARCHAR(1000) NULL,
  PRIMARY KEY (`IdResultQuestion`,`AnswerNum`),
  CONSTRAINT `FK_ResultAnswer_To_ResultQuestion`
    FOREIGN KEY (`IdResultQuestion`)
    REFERENCES `QuizResultQuestion` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Settings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Settings` (
  `DbVersion` INT NOT NULL,
  `DateCreated` DATETIME NULL,
  `DateModified` DATETIME NULL)
ENGINE = InnoDB;

INSERT INTO Settings (DbVersion,DateCreated,DateModified) SELECT * FROM (SELECT 1,NOW() as date1,NOW() as date2) AS tmp  WHERE NOT EXISTS (SELECT * FROM Settings);

DELETE FROM PersonRole;
INSERT INTO PersonRole (ID,RoleName,RoleDescription,IsEnabled) VALUES (1,'admin','',1);
INSERT INTO PersonRole (ID,RoleName,RoleDescription,IsEnabled) VALUES (2,'user','',1);