
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `lenasys` ;
-- -----------------------------------------------------
CREATE USER 'dbsk'@'localhost' IDENTIFIED BY 'Tomten2009';
GRANT ALL PRIVILEGES ON 'lenasys'.* TO 'dbsk'@'localhost' WITH GRANT OPTION;
-- -----------------------------------------------------
-- Table `lenasys`.`Users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Users` (
  `userName` VARCHAR(20) NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `passwd` VARCHAR(45) NULL ,
  `userType` INT NULL , -- teacher or student
  `ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
  PRIMARY KEY (`userName`) ,
  UNIQUE INDEX `userName_UNIQUE` (`userName` ASC) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Courses`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Courses` (
  `courseID` VARCHAR(10) NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `courseData` VARCHAR(10) NULL , -- Short explenation about course, example: G1N 7,5hp
  PRIMARY KEY (`courseID`) ,
  UNIQUE INDEX `courseID_UNIQUE` (`courseID` ASC) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`StudentCourseRegistrations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
  `courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
  `userName` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL , -- Example G14234
  PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
  INDEX `fk_StudentCourseRegistrations_Courses1_idx` (`courseID` ASC) ,
  CONSTRAINT `fk_StudentCourseRegistrations_Users`
    FOREIGN KEY (`userName` )
    REFERENCES `lenasys`.`Users` (`userName` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StudentCourseRegistrations_Courses1`
    FOREIGN KEY (`courseID` )
    REFERENCES `lenasys`.`Courses` (`courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Quizzes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
  `quizNr` INT NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `quizData` VARCHAR(45) NULL ,
  `allowMultipleReplies` BOOLEAN NULL , 
  `autoCorrected` BOOLEAN NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually.
  `openingDate` DATETIME NULL ,  -- time for the student to do the quiz
  `closingDate` DATETIME NULL ,
  PRIMARY KEY (`quizNr`, `courseID`) ,
  INDEX `fk_Quizzes_Courses1_idx` (`courseID` ASC) ,
  UNIQUE INDEX `quizNr_UNIQUE` (`quizNr` ASC) ,
  CONSTRAINT `fk_Quizzes_Courses1`
    FOREIGN KEY (`courseID` )
    REFERENCES `lenasys`.`Courses` (`courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`QuizQuestions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
  `questionID` INT NOT NULL , -- The id of the individual question in the quiz.
  `quizNr` INT NOT NULL , -- The quiz from wich the question is from
  `courseID` VARCHAR(10) NOT NULL ,
  `questionData` VARCHAR(45) NULL , -- the question
  `correctAnswer` VARCHAR(45) NULL , -- the answer
  PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,
  UNIQUE INDEX `questionID_UNIQUE` (`questionID` ASC) ,
  INDEX `fk_QuizQuestions_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
  CONSTRAINT `fk_QuizQuestions_Quizzes1`
    FOREIGN KEY (`quizNr` , `courseID` )
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`AssignedQuizzes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
  `courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
  `userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
  `courseID` VARCHAR(10) NOT NULL ,
  `quizNr` INT NOT NULL ,
  `quizCourseID` VARCHAR(10) NOT NULL ,
  `answers` VARCHAR(45) NULL , -- answers provided by the student
  `answerHash` VARCHAR(45) NULL , -- a security measure
  `answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
  `grade` VARCHAR(8) NULL ,
  `gradeComment` VARCHAR(200) NULL ,
  PRIMARY KEY (`userName`, `courseID`, `quizNr`, `courseID`) ,
  INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
  CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`
    FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
    REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_AssignedQuizzes_Quizzes1`
    FOREIGN KEY (`quizNr` , `quizCourseID` )
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `quizCourseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Examples`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Examples` (
  `exampleName` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `orderNumber` INT NULL , -- the order of the examples in the same category 
  `description` VARCHAR(200) NULL ,
  PRIMARY KEY (`exampleName`, `courseID`) ,
  INDEX `fk_Examples_Courses1_idx` (`courseID` ASC) ,
  UNIQUE INDEX `exampleName_UNIQUE` (`exampleName` ASC) ,
  CONSTRAINT `fk_Examples_Courses1`
    FOREIGN KEY (`courseID` )
    REFERENCES `lenasys`.`Courses` (`courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Pages`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Pages` (
  `pageName` VARCHAR(45) NOT NULL ,
  `exampleName` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `orderNumber` INT NULL , 
  `pageColumn` INT NOT NULL , -- which column in the view the file is presented
  PRIMARY KEY (`pageName`, `exampleName`, `courseID`) ,
  INDEX `fk_Pages_Examples1_idx` (`exampleName` ASC, `courseID` ASC) ,
  CONSTRAINT `fk_Pages_Examples1`
    FOREIGN KEY (`exampleName` , `courseID` )
    REFERENCES `lenasys`.`Examples` (`exampleName` , `courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Files`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (
  `fileName` VARCHAR(20) NOT NULL ,
  `fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild
  `codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
  `dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
  PRIMARY KEY (`fileName`) ,
  UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `lenasys`.`PageFiles`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`PageFiles` (
  `fileName` VARCHAR(20) NOT NULL ,
  `pageName` VARCHAR(45) NOT NULL , -- 
  `exampleName` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`fileName`, `pageName`, `exampleName`, `courseID`) ,
  INDEX `fk_PagesFiles_Files1_idx` (`fileName` ASC) ,
  INDEX `fk_PagesFiles_Pages1_idx` (`pageName` ASC, `exampleName` ASC, `courseID` ASC) ,
  CONSTRAINT `fk_PagesFiles_Files1`
    FOREIGN KEY (`fileName` )
    REFERENCES `lenasys`.`Files` (`fileName` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PagesFiles_Pages1`
    FOREIGN KEY (`pageName` , `exampleName` , `courseID` )
    REFERENCES `lenasys`.`Pages` (`pageName` , `exampleName` , `courseID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `lenasys`.`FileKeywords`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileKeywords` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `fileName` VARCHAR(20) NOT NULL ,
  `keyword` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`, `fileName`) ,
  INDEX `fk_FileKeywords_Files1_idx` (`fileName` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  CONSTRAINT `fk_FileKeywords_Files1`
    FOREIGN KEY (`fileName` )
    REFERENCES `lenasys`.`Files` (`fileName` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `lenasys`.`FileInterestingLines`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `fileName` VARCHAR(20) NOT NULL ,
  `beginAt` INT NULL ,
  `endAt` INT NULL ,
  PRIMARY KEY (`id`, `fileName`) ,
  INDEX `fk_FileInterestingLines_Files1_idx` (`fileName` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  CONSTRAINT `fk_FileInterestingLines_Files1`
    FOREIGN KEY (`fileName` )
    REFERENCES `lenasys`.`Files` (`fileName` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
---------------------------------------------------
insert into `lenasys`.`Users` (
  `userName` ,
  `name`  ,
  `passwd`,
  `userType`,
  `ssn`) VALUES (`Per`,`Per Student`,md5(`Syp9393`),`student`);

USE `lenasys` ;

-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. 
