-- -----------------------------------------------------
-- Table Quizzes
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS Quizzes (
  QZ_ID SERIAL,
  quizname VARCHAR(255) NOT NULL,
  Title VARCHAR(1024) NOT NULL,
  IntroText TEXT NULL,
  EpiText TEXT NULL,
  NumQuestions INTEGER NOT NULL,
  Author VARCHAR(255) NULL,
  Argument VARCHAR(255) NULL,
  Season VARCHAR(255) NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL,
  PRIMARY KEY (QZ_ID));


-- -----------------------------------------------------
-- Table Person
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS Person (
  ID SERIAL,
  PersonName VARCHAR(255) NOT NULL,
  UserIdentity VARCHAR(255) NOT NULL,
  UserPassword VARCHAR(255) NOT NULL,
  UserRole INTEGER NOT NULL,
  Info TEXT NULL,
  IsEnabled BOOLEAN NOT NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL,
  DateLastAccess TIMESTAMP NULL,
  PRIMARY KEY (ID));


-- -----------------------------------------------------
-- Table PersonRole
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS PersonRole (
  ID INTEGER NOT NULL,
  RoleName VARCHAR(80) NOT NULL,
  RoleDescription VARCHAR(500) NULL,
  IsEnabled BOOLEAN NOT NULL,
  PRIMARY KEY (ID));



-- -----------------------------------------------------
-- Table Categories
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS Categories (
  QZ_ID INTEGER NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  Description TEXT NULL,
  MaxQuestForQuiz INTEGER NULL,
  IsRandom BOOLEAN NULL,
  PRIMARY KEY (CategoryName, QZ_ID));



-- -----------------------------------------------------
-- Table Questions
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS Questions (
  QZ_ID INTEGER NOT NULL,
  Num INTEGER NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  TextQuestion TEXT NOT NULL,
  Type INTEGER NOT NULL,
  Weight FLOAT NOT NULL,
  Include BOOLEAN NOT NULL,
  TextFormat INTEGER NOT NULL,
  SortAnswers BOOLEAN NOT NULL,
  TextAbove TEXT NULL,
  TextBelow TEXT NULL,
  Remark TEXT NULL,
  PutSeparator BOOLEAN NULL,
  Timeout INTEGER NULL,
  Html TEXT NULL,
  PRIMARY KEY (QZ_ID, Num),
  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);

CREATE INDEX IF NOT EXISTS Category_FK_idx ON Questions (CategoryName, QZ_ID);


-- -----------------------------------------------------
-- Table ChoiceAnswers
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS ChoiceAnswers (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  TypeOfAns INTEGER NOT NULL,
  SingleAns BOOLEAN 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);



-- -----------------------------------------------------
-- Table ChoiceAnswerItems
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS ChoiceAnswerItems (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  NumAns INTEGER NOT NULL,
  TextFormat INTEGER NOT NULL,
  Ans TEXT NOT NULL,
  RightAns BOOLEAN NOT NULL,
  Hint TEXT NULL,
  Rem TEXT NULL,
  PointsIn INTEGER NULL,
  PointsOut INTEGER NULL,
  Html TEXT 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);



-- -----------------------------------------------------
-- Table BooleanAnswerItems
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS BooleanAnswerItems (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  NumAns INTEGER NOT NULL,
  Sentence TEXT NOT NULL,
  TrueAns BOOLEAN NOT NULL,
  TextFormat INTEGER NOT NULL,
  Html TEXT 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);



-- -----------------------------------------------------
-- Table FillGapAnswers
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS FillGapAnswers (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  Phrase TEXT NOT NULL,
  Type INTEGER NOT NULL,
  TextFormat INTEGER NOT NULL,
  Html TEXT 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);



-- -----------------------------------------------------
-- Table MatchingAnswers
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS MatchingAnswers (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  Type INTEGER 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);



-- -----------------------------------------------------
-- Table PairNames
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS PairNames (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  NumAns INTEGER NOT NULL,
  LeftItem TEXT NOT NULL,
  RightItem TEXT NOT NULL,
  TextFormat INTEGER NOT NULL,
  Html TEXT 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);



-- -----------------------------------------------------
-- Table CustomQuestionTypes
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS CustomQuestionTypes (
  QZ_ID INTEGER NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  Description TEXT NULL,
  Version INTEGER NULL,
  Subversion INTEGER NULL,
  IconData TEXT NULL,
  IncludeConfirmButton BOOLEAN NULL,
  ValuateRightWrong BOOLEAN NULL,
  MaxScore INTEGER NULL,
  MinScore INTEGER NULL,
  JscriptCode TEXT NULL,
  HtmlCode TEXT NULL,
  PRIMARY KEY (QZ_ID, CustomName));



-- -----------------------------------------------------
-- Table CustomParameters
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS CustomParameters (
  QZ_ID INTEGER NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  ParamName VARCHAR(255) NOT NULL,
  IsEncrypt BOOLEAN NOT NULL,
  Type INTEGER 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);



-- -----------------------------------------------------
-- Table CustomHeadTags
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS CustomHeadTags (
  QZ_ID INTEGER NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  FileName VARCHAR(255) NOT NULL,
  Content TEXT NOT NULL,
  ContentType INTEGER NOT NULL,
  PRIMARY KEY (FileName, CustomName, QZ_ID),
  CONSTRAINT FK_CustomHeadTags
    FOREIGN KEY (QZ_ID , CustomName)
    REFERENCES CustomQuestionTypes (QZ_ID , CustomName)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE INDEX IF NOT EXISTS FK_CustomHeadTags_idx ON CustomHeadTags (QZ_ID, CustomName);

-- -----------------------------------------------------
-- Table CustomAnswers
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS CustomAnswers (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  ParamName VARCHAR(255) NOT NULL,
  ParamValue VARCHAR(255) NOT NULL,
  PRIMARY KEY (QZ_ID, NumQst, CustomName, ParamName),
  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);


CREATE INDEX IF NOT EXISTS FK_CustomAns_To_CustomParams_idx ON CustomAnswers (QZ_ID, CustomName, ParamName);


-- -----------------------------------------------------
-- Table Slides
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS Slides (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  SlideName VARCHAR(80) NOT NULL,
  Content TEXT NOT NULL,
  TextFormat INTEGER NOT NULL,
  BeforeQst BOOLEAN NOT NULL,
  IsExternal BOOLEAN NOT NULL,
  HtmlFileName VARCHAR(255) NULL,
  SpeechFileName VARCHAR(255) NULL,
  Timeout INTEGER 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);



-- -----------------------------------------------------
-- Table SlideDocs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS SlideDocs (
  QZ_ID INTEGER NOT NULL,
  NumQst INTEGER NOT NULL,
  SlideName VARCHAR(80) NOT NULL,
  DocFileName VARCHAR(255) NOT NULL,
  LinkName VARCHAR(500) NOT NULL,
  PRIMARY KEY (DocFileName, QZ_ID, NumQst, SlideName),
  CONSTRAINT FK_Doc_To_Slide
    FOREIGN KEY (QZ_ID , NumQst , SlideName)
    REFERENCES Slides (QZ_ID , NumQst , SlideName)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE INDEX IF NOT EXISTS FK_Doc_To_Slide_idx ON SlideDocs (QZ_ID, NumQst, SlideName);


-- -----------------------------------------------------
-- Table `QuizSession`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS QuizSession (
  PersonID INTEGER NOT NULL,
  SessionID VARCHAR(36) NOT NULL,
  DateCreated TIMESTAMP NOT NULL,
  IpLogin VARCHAR(46) NULL,
  SessionData TEXT NULL,
  DateLastUpdate TIMESTAMP NULL,
  QuizID INTEGER NULL,
  IpLogout VARCHAR(46) NULL,
  IsRecoverable BOOLEAN NULL,
  PRIMARY KEY (PersonID,SessionID));


-- -----------------------------------------------------
-- Table Quiz_Result
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS QuizResult (
  ID SERIAL,
  QuizName VARCHAR(200) NOT NULL,
  QuizTitle VARCHAR(200) NOT NULL,
  QuizID INTEGER NULL,
  UserName VARCHAR(200) NOT NULL,
  UserLogin VARCHAR(200) NOT NULL,
  DateCompleted TIMESTAMP NOT NULL,
  DateReceived TIMESTAMP NOT NULL,
  QuestsNum INTEGER NOT NULL,
  HighestMark INTEGER NOT NULL,
  FinalMark REAL NOT NULL,
  TotalTime INTEGER NOT NULL,
  ElapsedTime INTEGER NOT NULL,
  RightQuestsNum INTEGER NOT NULL,
  WrongQuestsNum INTEGER NOT NULL,
  NumOfRetake INTEGER NOT NULL,
  PRIMARY KEY (ID));


-- -----------------------------------------------------
-- Table Quiz_Result_Report
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS QuizResultReport (
  ID INTEGER NOT NULL,
  Report TEXT NULL,
  PRIMARY KEY (ID));


-- -----------------------------------------------------
-- Table QuizResultQuestion
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS QuizResultQuestion (
  ID SERIAL,
  IdQuizResult INTEGER NOT NULL,
  QuestNum INTEGER NOT NULL,
  QuestType INTEGER NOT NULL,
  Weight REAL NOT NULL,
  ShortTextQuestion VARCHAR(1000) NULL,
  Valid INTEGER NOT NULL,
  Score INTEGER NOT NULL,
  MaxScore INTEGER NOT NULL,
  MinScore INTEGER NOT NULL,  
  PRIMARY KEY (ID),
  CONSTRAINT FK_ResultQuestion_To_Result
    FOREIGN KEY (IdQuizResult)
    REFERENCES QuizResult (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE);


-- -----------------------------------------------------
-- Table QuizResultAnswer
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS QuizResultAnswer (
  IdResultQuestion INTEGER NOT NULL,
  AnswerNum INTEGER NOT NULL,
  Choice VARCHAR(500) NOT NULL,
  Valuation VARCHAR(500) NOT NULL,
  IsGuess BOOLEAN NOT NULL,
  Score INTEGER 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);



-- -----------------------------------------------------
-- Table Settings
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS Settings (
  DbVersion INTEGER NOT NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL);


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','',true);
INSERT INTO PersonRole (ID,RoleName,RoleDescription,IsEnabled) VALUES (2,'user','',true);