
-- -----------------------------------------------------
-- Table Quizzes
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Quizzes' AND XTYPE='U')
CREATE TABLE Quizzes (
  QZ_ID INT NOT NULL IDENTITY (1,1),
  quizname VARCHAR(255) NOT NULL,
  Title VARCHAR(1024) NOT NULL,
  IntroText VARCHAR(MAX) NULL,
  EpiText VARCHAR(MAX) 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))

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

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Person' AND XTYPE='U')
CREATE TABLE Person (
  ID INT NOT NULL IDENTITY (1,1),
  PersonName VARCHAR(255) NOT NULL,
  UserIdentity VARCHAR(255) NOT NULL,
  UserPassword VARCHAR(255) NOT NULL,
  UserRole INT NOT NULL,
  Info VARCHAR(MAX) NULL,
  IsEnabled BIT NOT NULL,
  DateCreated DATETIME NULL,
  DateModified DATETIME NULL,
  DateLastAccess DATETIME NULL,
  PRIMARY KEY (ID))


-- -----------------------------------------------------
-- Table PersonRole
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='PersonRole' AND XTYPE='U')
CREATE TABLE PersonRole (
  ID INT NOT NULL,
  RoleName VARCHAR(80) NOT NULL,
  RoleDescription VARCHAR(500) NULL,
  IsEnabled BIT NOT NULL,
  PRIMARY KEY (ID))


-- -----------------------------------------------------
-- Table Categories
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Categories' AND XTYPE='U')
CREATE TABLE Categories (
  QZ_ID INT NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  Description VARCHAR(MAX) NULL,
  MaxQuestForQuiz INT NULL,
  IsRandom BIT NULL,
  PRIMARY KEY (CategoryName, QZ_ID))



-- -----------------------------------------------------
-- Table Questions
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Questions' AND XTYPE='U')
CREATE TABLE Questions (
  QZ_ID INT NOT NULL,
  Num INT NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  TextQuestion VARCHAR(MAX) NOT NULL,
  Type INT NOT NULL,
  Weight FLOAT NOT NULL,
  Include BIT NOT NULL,
  TextFormat INT NOT NULL,
  SortAnswers BIT NOT NULL,
  TextAbove VARCHAR(MAX) NULL,
  TextBelow VARCHAR(MAX) NULL,
  Remark VARCHAR(MAX) NULL,
  PutSeparator BIT NULL,
  Timeout INT NULL,
  Html VARCHAR(MAX) 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)



-- -----------------------------------------------------
-- Table ChoiceAnswers
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='ChoiceAnswers' AND XTYPE='U')
CREATE TABLE ChoiceAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  TypeOfAns INT NOT NULL,
  SingleAns BIT 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
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='ChoiceAnswerItems' AND XTYPE='U')
CREATE TABLE ChoiceAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  TextFormat INT NOT NULL,
  Ans VARCHAR(MAX) NOT NULL,
  RightAns BIT NOT NULL,
  Hint VARCHAR(MAX) NULL,
  Rem VARCHAR(MAX) NULL,
  PointsIn INT NULL,
  PointsOut INT NULL,
  Html VARCHAR(MAX) 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
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='BooleanAnswerItems' AND XTYPE='U')
CREATE TABLE BooleanAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  Sentence VARCHAR(MAX) NOT NULL,
  TrueAns BIT NOT NULL,
  TextFormat INT NOT NULL,
  Html VARCHAR(MAX) 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
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='FillGapAnswers' AND XTYPE='U')
CREATE TABLE FillGapAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  Phrase VARCHAR(MAX) NOT NULL,
  Type INT NOT NULL,
  TextFormat INT NOT NULL,
  Html VARCHAR(MAX) 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
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='MatchingAnswers' AND XTYPE='U')
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
    ON UPDATE CASCADE)



-- -----------------------------------------------------
-- Table PairNames
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='PairNames' AND XTYPE='U')
CREATE TABLE PairNames (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  LeftItem VARCHAR(MAX) NOT NULL,
  RightItem VARCHAR(MAX) NOT NULL,
  TextFormat INT NOT NULL,
  Html VARCHAR(MAX) 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
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='CustomQuestionTypes' AND XTYPE='U')
CREATE TABLE CustomQuestionTypes (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  Description VARCHAR(MAX) NULL,
  Version INT NULL,
  Subversion INT NULL,
  IconData VARCHAR(MAX) NULL,
  IncludeConfirmButton BIT NULL,
  ValuateRightWrong BIT NULL,
  MaxScore INT NULL,
  MinScore INT NULL,
  JscriptCode VARCHAR(MAX) NULL,
  HtmlCode VARCHAR(MAX) NULL,
  PRIMARY KEY (QZ_ID, CustomName))



-- -----------------------------------------------------
-- Table CustomParameters
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='CustomParameters' AND XTYPE='U')
CREATE TABLE CustomParameters (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  ParamName VARCHAR(255) NOT NULL,
  IsEncrypt BIT 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)



-- -----------------------------------------------------
-- Table CustomHeadTags
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='CustomHeadTags' AND XTYPE='U')
CREATE TABLE CustomHeadTags (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  FileName VARCHAR(255) NOT NULL,
  Content VARCHAR(MAX) 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
    ON UPDATE CASCADE)



-- -----------------------------------------------------
-- Table CustomAnswers
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='CustomAnswers' AND XTYPE='U')
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
    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)



-- -----------------------------------------------------
-- Table Slides
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Slides' AND XTYPE='U')
CREATE TABLE Slides (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  SlideName VARCHAR(80) NOT NULL,
  Content VARCHAR(MAX) NOT NULL,
  TextFormat INT NOT NULL,
  BeforeQst BIT NOT NULL,
  IsExternal BIT 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)



-- -----------------------------------------------------
-- Table SlideDocs
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='SlideDocs' AND XTYPE='U')
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 NO ACTION
    ON UPDATE NO ACTION)


-- -----------------------------------------------------
-- Table `QuizSession`
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='QuizSession' AND XTYPE='U')
CREATE TABLE QuizSession (
  PersonID INT NOT NULL,
  SessionID VARCHAR(36) NOT NULL,
  DateCreated DATETIME NOT NULL,
  IpLogin VARCHAR(46) NULL,
  SessionData VARCHAR(MAX) NULL,
  DateLastUpdate DATETIME NULL,
  QuizID INT NULL,
  IpLogout VARCHAR(46) NULL,
  IsRecoverable BIT NULL,
  PRIMARY KEY (PersonID,SessionID))

-- -----------------------------------------------------
-- Table QuizResult
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='QuizResult' AND XTYPE='U')
CREATE TABLE QuizResult (
  ID INT NOT NULL IDENTITY (1,1),
  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 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))


-- -----------------------------------------------------
-- Table QuizResultReport
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='QuizResultReport' AND XTYPE='U')
CREATE TABLE QuizResultReport (
  ID INT NOT NULL,
  Report VARCHAR(MAX) NULL,  
  PRIMARY KEY (ID))


-- -----------------------------------------------------
-- Table QuizResultQuestion
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='QuizResultQuestion' AND XTYPE='U')
CREATE TABLE QuizResultQuestion (
  ID INT NOT NULL IDENTITY (1,1),
  IdQuizResult INT NOT NULL,
  QuestNum INT NOT NULL,
  QuestType INT NOT NULL,
  [Weight] FLOAT(12) 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
    ON UPDATE CASCADE)


-- -----------------------------------------------------
-- Table QuizResultAnswer
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='QuizResultAnswer' AND XTYPE='U')
CREATE TABLE 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)


-- -----------------------------------------------------
-- Table Settings
-- -----------------------------------------------------
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='Settings' AND XTYPE='U')
CREATE TABLE Settings (
  DbVersion INT NOT NULL,
  DateCreated DATETIME NULL,
  DateModified DATETIME NULL)

INSERT INTO Settings (DbVersion,DateCreated,DateModified)
SELECT 1,GETDATE(),GETDATE()
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)