text: -- MySQL Script generated by MySQL Workbench
  -- Sun Mar 29 13:33:10 2020
  -- Model: New Model    Version: 1.0
  -- MySQL Workbench Forward Engineering
  
  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  
  -- -----------------------------------------------------
  -- Schema db_student_resource_IS
  -- -----------------------------------------------------
  DROP SCHEMA IF EXISTS `db_student_resource_IS` ;
  
  -- -----------------------------------------------------
  -- Schema db_student_resource_IS
  -- -----------------------------------------------------
  CREATE SCHEMA IF NOT EXISTS `db_student_resource_IS` DEFAULT CHARACTER SET utf8 ;
  USE `db_student_resource_IS` ;
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Users`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Users` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Users` (
    `idUser` INT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(45) NOT NULL,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `username` VARCHAR(45) NULL,
    `password` TEXT NULL,
    `isAdmin` TINYINT(1) NULL,
    `user_timestamp` TIMESTAMP(1) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `user_verification_code` VARCHAR(45) NOT NULL,
    `isActive_User` TINYINT NULL DEFAULT 0,
    PRIMARY KEY (`idUser`),
    UNIQUE INDEX `emailAddress_UNIQUE` (`email` ASC) VISIBLE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Files`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Files` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Files` (
    `idFile` INT NOT NULL AUTO_INCREMENT,
    `file_description` TINYTEXT NOT NULL,
    `source` TEXT NOT NULL,
    `title` VARCHAR(45) NOT NULL,
    `plagiarism_percent` INT NULL,
    `file_timestamp` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `language` JSON NULL,
    `sentiment` JSON NULL,
    `key_phrase` JSON NULL,
    `entity` JSON NULL,
    `isPublic` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`idFile`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Teachers`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Teachers` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Teachers` (
    `idTeacher` INT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(45) NOT NULL,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `username` VARCHAR(45) NULL,
    `password` TEXT NULL,
    PRIMARY KEY (`idTeacher`),
    UNIQUE INDEX `emailAddress_UNIQUE` (`email` ASC) VISIBLE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Subjects`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Subjects` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Subjects` (
    `idSubject` INT NOT NULL AUTO_INCREMENT,
    `subject_name` VARCHAR(45) NOT NULL,
    `subject_timestamp` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`idSubject`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Classes`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Classes` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Classes` (
    `idClass` INT NOT NULL AUTO_INCREMENT,
    `class_description` TINYTEXT NULL,
    `subjects_idSubject` INT NOT NULL,
    `class_code` VARCHAR(45) NULL,
    `teachers_idTeacher` INT NOT NULL,
    `class_title` VARCHAR(45) NULL,
    `isActive_Class` TINYINT(1) NOT NULL DEFAULT 1,
    `cover` VARCHAR(45) NULL,
    PRIMARY KEY (`idClass`, `subjects_idSubject`, `teachers_idTeacher`),
    INDEX `fk_Classes_Subjects_idx` (`subjects_idSubject` ASC) VISIBLE,
    INDEX `fk_Classes_Teachers1_idx` (`teachers_idTeacher` ASC) VISIBLE,
    CONSTRAINT `fk_Classes_Subjects`
      FOREIGN KEY (`subjects_idSubject`)
      REFERENCES `db_student_resource_IS`.`Subjects` (`idSubject`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Classes_Teachers1`
      FOREIGN KEY (`teachers_idTeacher`)
      REFERENCES `db_student_resource_IS`.`Teachers` (`idTeacher`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Activities`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Activities` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Activities` (
    `idActivity` INT NOT NULL AUTO_INCREMENT,
    `activity_description` TINYTEXT NULL,
    `activity_timestamp` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `classes_idClass` INT NOT NULL,
    `total_items` DECIMAL NOT NULL,
    `activity_title` VARCHAR(45) NULL,
    `activity_DueDate` TIMESTAMP NULL,
    `isActive_Activity` TINYINT(1) NOT NULL DEFAULT 1,
    `Files_idFile` INT NULL,
    PRIMARY KEY (`idActivity`, `classes_idClass`),
    INDEX `fk_Activities_Classes1_idx` (`classes_idClass` ASC) VISIBLE,
    INDEX `fk_Activities_Files1_idx` (`Files_idFile` ASC) VISIBLE,
    CONSTRAINT `fk_Activities_Classes1`
      FOREIGN KEY (`classes_idClass`)
      REFERENCES `db_student_resource_IS`.`Classes` (`idClass`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Activities_Files1`
      FOREIGN KEY (`Files_idFile`)
      REFERENCES `db_student_resource_IS`.`Files` (`idFile`)
      ON DELETE CASCADE
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`class_user`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`class_user` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`class_user` (
    `users_idUser` INT NOT NULL,
    `classes_idClass` INT NOT NULL,
    `confirmed` TINYINT(1) NOT NULL DEFAULT 0,
    INDEX `fk_Users_has_Classes_Classes1_idx` (`classes_idClass` ASC) VISIBLE,
    INDEX `fk_Users_has_Classes_Users1_idx` (`users_idUser` ASC) VISIBLE,
    PRIMARY KEY (`users_idUser`, `classes_idClass`),
    CONSTRAINT `fk_Users_has_Classes_Users1`
      FOREIGN KEY (`users_idUser`)
      REFERENCES `db_student_resource_IS`.`Users` (`idUser`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Users_has_Classes_Classes1`
      FOREIGN KEY (`classes_idClass`)
      REFERENCES `db_student_resource_IS`.`Classes` (`idClass`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`activity_user`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`activity_user` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`activity_user` (
    `users_idUser` INT NOT NULL,
    `activities_idActivity` INT NOT NULL,
    `score` VARCHAR(45) NULL,
    `Files_idFile` INT NULL,
    `activity_submitted` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`users_idUser`, `activities_idActivity`),
    INDEX `fk_Users_has_Activities_Activities1_idx` (`activities_idActivity` ASC) VISIBLE,
    INDEX `fk_Users_has_Activities_Users1_idx` (`users_idUser` ASC) VISIBLE,
    INDEX `fk_activity_user_Files1_idx` (`Files_idFile` ASC) VISIBLE,
    CONSTRAINT `fk_Users_has_Activities_Users1`
      FOREIGN KEY (`users_idUser`)
      REFERENCES `db_student_resource_IS`.`Users` (`idUser`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Users_has_Activities_Activities1`
      FOREIGN KEY (`activities_idActivity`)
      REFERENCES `db_student_resource_IS`.`Activities` (`idActivity`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_activity_user_Files1`
      FOREIGN KEY (`Files_idFile`)
      REFERENCES `db_student_resource_IS`.`Files` (`idFile`)
      ON DELETE CASCADE
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Searches`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Searches` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Searches` (
    `idSearch` INT NOT NULL AUTO_INCREMENT,
    `term` VARCHAR(45) NULL,
    `search_timestamp` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Users_idUser` INT NOT NULL,
    PRIMARY KEY (`idSearch`, `Users_idUser`),
    INDEX `fk_Searches_Users1_idx` (`Users_idUser` ASC) VISIBLE,
    CONSTRAINT `fk_Searches_Users1`
      FOREIGN KEY (`Users_idUser`)
      REFERENCES `db_student_resource_IS`.`Users` (`idUser`)
      ON DELETE CASCADE
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`Bookmarks`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`Bookmarks` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`Bookmarks` (
    `users_idUser` INT NOT NULL,
    `files_idFile` INT NOT NULL,
    `bookmark_timestamp` TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`users_idUser`, `files_idFile`),
    INDEX `fk_Users_has_Files_Files1_idx` (`files_idFile` ASC) VISIBLE,
    INDEX `fk_Users_has_Files_Users1_idx` (`users_idUser` ASC) VISIBLE,
    CONSTRAINT `fk_Users_has_Files_Users1`
      FOREIGN KEY (`users_idUser`)
      REFERENCES `db_student_resource_IS`.`Users` (`idUser`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Users_has_Files_Files1`
      FOREIGN KEY (`files_idFile`)
      REFERENCES `db_student_resource_IS`.`Files` (`idFile`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `db_student_resource_IS`.`ci_sessions`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `db_student_resource_IS`.`ci_sessions` ;
  
  CREATE TABLE IF NOT EXISTS `db_student_resource_IS`.`ci_sessions` (
    `id` VARCHAR(40) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `timestamp` INT(10) UNSIGNED NOT NULL DEFAULT 0,
    `data` BLOB NOT NULL,
    `ci_sessions_timestamp` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`))
  ENGINE = InnoDB;
  
  USE `db_student_resource_IS` ;
  
  -- -----------------------------------------------------
  -- procedure score_backup
  -- -----------------------------------------------------
  
  USE `db_student_resource_IS`;
  DROP procedure IF EXISTS `db_student_resource_IS`.`score_backup`;
  
  DELIMITER $$
  USE `db_student_resource_IS`$$
  create PROCEDURE score_backup()
  
  begin
  
  set @sql_text2 = concat("SELECT * into OUTFILE 'svnhs_student_resource_center/system/database/backup/backup_score", DATE_FORMAT(now(),'%Y%m%d-%H%i%s'),'.csv', "' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM db_student_resource_is.activity_user");
  
  PREPARE s1 FROM @sql_text2;
  
  EXECUTE s1;
  
  DROP PREPARE s1;
  
  end$$
  
  DELIMITER ;
  
  -- -----------------------------------------------------
  -- procedure exec_qry
  -- -----------------------------------------------------
  
  USE `db_student_resource_IS`;
  DROP procedure IF EXISTS `db_student_resource_IS`.`exec_qry`;
  
  DELIMITER $$
  USE `db_student_resource_IS`$$
  create procedure exec_qry(IN p_sql VARCHAR(100)) 
  BEGIN
    SET @tquery = p_sql;
    PREPARE stmt FROM @tquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END$$
  
  DELIMITER ;
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  