
-- -----------------------------------------------------
-- Table Quizzes
-- -----------------------------------------------------
CREATE TABLE Quizzes (
  QZ_ID INT NOT NULL,
  quizname VARCHAR(255) NOT NULL,
  Title VARCHAR(1024) NOT NULL,
  IntroText CLOB NULL,
  EpiText CLOB NULL,
  NumQuestions INT NOT NULL,
  Author VARCHAR(255) NULL,
  Argument VARCHAR(255) NULL,
  Season VARCHAR(255) NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL,
  PRIMARY KEY (QZ_ID));

 CREATE SEQUENCE QUIZ_ID_SEQ;
  
 CREATE OR REPLACE TRIGGER QUIZ_ID_TRIG 
	BEFORE INSERT ON Quizzes 
	FOR EACH ROW
	WHEN (new.QZ_ID IS NULL)
	BEGIN
	  SELECT QUIZ_ID_SEQ.NEXTVAL
	  INTO   :new.QZ_ID
	  FROM   dual;
	END;
/

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

CREATE TABLE Person (
  ID INT NOT NULL,
  PersonName VARCHAR(255) NOT NULL,
  UserIdentity VARCHAR(255) NOT NULL,
  UserPassword VARCHAR(255) NOT NULL,
  UserRole INT NOT NULL,
  Info CLOB NULL,
  IsEnabled CHAR(1) NOT NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL,
  DateLastAccess TIMESTAMP NULL,
  PRIMARY KEY (ID));

CREATE SEQUENCE USER_ID_SEQ;
  
 CREATE OR REPLACE TRIGGER USER_ID_TRIG 
	BEFORE INSERT ON Person 
	FOR EACH ROW
	WHEN (new.ID IS NULL)
	BEGIN
	  SELECT USER_ID_SEQ.NEXTVAL
	  INTO   :new.ID
	  FROM   dual;
	END;
/
-- -----------------------------------------------------
-- Table PersonRole
-- -----------------------------------------------------
CREATE TABLE PersonRole (
  ID INT NOT NULL,
  RoleName VARCHAR(80) NOT NULL,
  RoleDescription VARCHAR(500) NULL,
  IsEnabled CHAR(1) NOT NULL,
  PRIMARY KEY (ID));

-- -----------------------------------------------------
-- Table Categories
-- -----------------------------------------------------
CREATE TABLE Categories (
  QZ_ID INT NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  Description CLOB NULL,
  MaxQuestForQuiz INT NULL,
  IsRandom NUMBER(1,0) NULL,
  PRIMARY KEY (CategoryName, QZ_ID));



-- -----------------------------------------------------
-- Table Questions
-- -----------------------------------------------------
CREATE TABLE Questions (
  QZ_ID INT NOT NULL,
  Num INT NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  TextQuestion CLOB NOT NULL,
  Type INT NOT NULL,
  Weight FLOAT NOT NULL,
  Include NUMBER(1,0) NOT NULL,
  TextFormat INT NOT NULL,
  SortAnswers NUMBER(1,0) NOT NULL,
  TextAbove CLOB NULL,
  TextBelow CLOB NULL,
  Remark CLOB NULL,
  PutSeparator NUMBER(1,0) NULL,
  Timeout INT NULL,
  Html CLOB 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, 
  CONSTRAINT Category_FK
    FOREIGN KEY (CategoryName , QZ_ID)
    REFERENCES Categories (CategoryName , QZ_ID)
    ON DELETE CASCADE
    );



-- -----------------------------------------------------
-- Table ChoiceAnswers
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  TypeOfAns INT NOT NULL,
  SingleAns NUMBER(1,0) NOT NULL,
  PRIMARY KEY (QZ_ID, NumQst),
  CONSTRAINT ChoiceAns_FK
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES Questions (QZ_ID , Num)
    ON DELETE CASCADE
    );



-- -----------------------------------------------------
-- Table ChoiceAnswerItems
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  TextFormat INT NOT NULL,
  Ans CLOB NOT NULL,
  RightAns NUMBER(1,0) NOT NULL,
  Hint CLOB NULL,
  Rem CLOB NULL,
  PointsIn INT NULL,
  PointsOut INT NULL,
  Html CLOB NULL,
  PRIMARY KEY (QZ_ID, NumQst, NumAns),
  CONSTRAINT ChoiceItems_FK
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES ChoiceAnswers (QZ_ID , NumQst)
    ON DELETE CASCADE
    );



-- -----------------------------------------------------
-- Table BooleanAnswerItems
-- -----------------------------------------------------
CREATE TABLE BooleanAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  Sentence CLOB NOT NULL,
  TrueAns NUMBER(1,0) NOT NULL,
  TextFormat INT NOT NULL,
  Html CLOB NULL,
  PRIMARY KEY (QZ_ID, NumQst, NumAns),
  CONSTRAINT BoolAns_FK
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES Questions (QZ_ID , Num)
    ON DELETE CASCADE
    );



-- -----------------------------------------------------
-- Table FillGapAnswers
-- -----------------------------------------------------
CREATE TABLE FillGapAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  Phrase CLOB NOT NULL,
  Type INT NOT NULL,
  TextFormat INT NOT NULL,
  Html CLOB NULL,
  PRIMARY KEY (QZ_ID, NumQst),
  CONSTRAINT FillGap_FK
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES Questions (QZ_ID , Num)
    ON DELETE CASCADE
    );



-- -----------------------------------------------------
-- Table MatchingAnswers
-- -----------------------------------------------------
CREATE TABLE 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
    );



-- -----------------------------------------------------
-- Table PairNames
-- -----------------------------------------------------
CREATE TABLE PairNames (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  LeftItem CLOB NOT NULL,
  RightItem CLOB NOT NULL,
  TextFormat INT NOT NULL,
  Html CLOB NULL,
  PRIMARY KEY (QZ_ID, NumQst, NumAns),
  CONSTRAINT PairNames_FK
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES MatchingAnswers (QZ_ID , NumQst)
    ON DELETE CASCADE
    );


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

CREATE TABLE CustomQuestionTypes (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  Description CLOB NULL,
  Version INT NULL,
  Subversion INT NULL,
  IconData CLOB NULL,
  IncludeConfirmButton NUMBER(1,0) NULL,
  ValuateRightWrong NUMBER(1,0) NULL,
  MaxScore INT NULL,
  MinScore INT NULL,
  JscriptCode CLOB NULL,
  HtmlCode CLOB NULL,
  PRIMARY KEY (QZ_ID, CustomName));



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

CREATE TABLE CustomParameters (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  ParamName VARCHAR(255) NOT NULL,
  IsEncrypt NUMBER(1,0) 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
 );



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

CREATE TABLE CustomHeadTags (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  FileName VARCHAR(255) NOT NULL,
  Content CLOB NOT NULL,
  ContentType INT NOT NULL,
  PRIMARY KEY (FileName, CustomName, QZ_ID),
  CONSTRAINT FK_CustomHeadTags
    FOREIGN KEY (QZ_ID , CustomName)
    REFERENCES CustomQuestionTypes (QZ_ID , CustomName)
    ON DELETE CASCADE
);



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

CREATE TABLE 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),
  CONSTRAINT FK_CustomAns_To_Question
    FOREIGN KEY (QZ_ID , NumQst)
    REFERENCES Questions (QZ_ID , Num)
    ON DELETE CASCADE,
  CONSTRAINT FK_CustomAns_To_CustomParams
    FOREIGN KEY (QZ_ID , CustomName , ParamName)
    REFERENCES CustomParameters (QZ_ID , CustomName , ParamName)
    ON DELETE CASCADE
);



-- -----------------------------------------------------
-- Table Slides
-- -----------------------------------------------------
CREATE TABLE Slides (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  SlideName VARCHAR(80) NOT NULL,
  Content CLOB NOT NULL,
  TextFormat INT NOT NULL,
  BeforeQst NUMBER(1,0) NOT NULL,
  IsExternal NUMBER(1,0) 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
);



-- -----------------------------------------------------
-- Table SlideDocs
-- -----------------------------------------------------
CREATE TABLE 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),
  CONSTRAINT FK_Doc_To_Slide
    FOREIGN KEY (QZ_ID , NumQst , SlideName)
    REFERENCES Slides (QZ_ID , NumQst , SlideName)
    ON DELETE CASCADE
);

-- -----------------------------------------------------
-- Table `QuizSession`
-- -----------------------------------------------------
CREATE TABLE QuizSession (
  PersonID INT NOT NULL,
  SessionID VARCHAR(36) NOT NULL,
  DateCreated TIMESTAMP NOT NULL,
  IpLogin VARCHAR(46) NULL,
  SessionData CLOB NULL,
  DateLastUpdate TIMESTAMP NULL,
  QuizID INT NULL,
  IpLogout VARCHAR(46) NULL,
  IsRecoverable NUMBER(1,0) NULL,
  PRIMARY KEY (PersonID,SessionID));

-- -----------------------------------------------------
-- Table QuizResult
-- -----------------------------------------------------
CREATE TABLE QuizResult (
  ID INT NOT NULL,
  QuizName VARCHAR(200) NOT NULL,
  QuizTitle VARCHAR(200) NOT NULL,
  QuizID INT NULL,
  UserName VARCHAR(200) NOT NULL,
  UserLogin VARCHAR(200) NOT NULL,
  DateCompleted TIMESTAMP NOT NULL,
  DateReceived TIMESTAMP NOT NULL,
  QuestsNum INT NOT NULL,
  HighestMark INT NOT NULL,
  FinalMark FLOAT(12) 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));


 CREATE SEQUENCE RESULT_ID_SEQ;
  
 CREATE OR REPLACE TRIGGER RESULT_ID_TRIG 
	BEFORE INSERT ON QuizResult 
	FOR EACH ROW
	WHEN (new.ID IS NULL)
	BEGIN
	  SELECT RESULT_ID_SEQ.NEXTVAL
	  INTO   :new.ID
	  FROM   dual;
	END;
/
-- -----------------------------------------------------
-- Table `Quiz_Result_Report`
-- -----------------------------------------------------
CREATE TABLE QuizResultReport (
  ID INT NOT NULL,
  Report CLOB NULL,
  PRIMARY KEY (ID));

-- -----------------------------------------------------
-- Table QuizResultQuestion
-- -----------------------------------------------------
CREATE TABLE QuizResultQuestion (
  ID INT NOT NULL,
  IdQuizResult INT NOT NULL,
  QuestNum INT NOT NULL,
  QuestType INT NOT NULL,
  Weight FLOAT NOT NULL,
  ShortTextQuestion VARCHAR(500) 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);
	
CREATE SEQUENCE RESULT_QUESTION_ID_SEQ;
  
 CREATE OR REPLACE TRIGGER RESULT_QUESTION_ID_TRIG 
	BEFORE INSERT ON QuizResultQuestion 
	FOR EACH ROW
	WHEN (new.ID IS NULL)
	BEGIN
	  SELECT RESULT_QUESTION_ID_SEQ.NEXTVAL
	  INTO   :new.ID
	  FROM   dual;
	END;
/
-- -----------------------------------------------------
-- Table QuizResultAnswer
-- -----------------------------------------------------
CREATE TABLE QuizResultAnswer (
  IdResultQuestion INT NOT NULL,
  AnswerNum INT NOT NULL,
  Choice VARCHAR(500) NOT NULL,
  Valuation VARCHAR(500) NOT NULL,
  IsGuess CHAR(1) 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);

-- -----------------------------------------------------
-- Table Settings
-- -----------------------------------------------------
CREATE TABLE Settings (
  DbVersion INT NOT NULL,
  DateCreated TIMESTAMP NULL,
  DateModified TIMESTAMP NULL);

DELETE FROM Settings;
INSERT INTO Settings (DbVersion,DateCreated,DateModified) SELECT 1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP FROM DUAL;

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);