-- MySQL Workbench Synchronization
-- Generated: 2019-11-11 15:33
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: haipham



ALTER TABLE `examapplicants` 
CHANGE COLUMN `location` `location` ENUM('Ha Noi', 'Ho Chi Minh', 'Hà Nội', 'Hồ Chí Minh') NULL DEFAULT 'Ha Noi' ;

ALTER TABLE `examdetails` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`major_id`, `knowledge_id`, `level_id`, `exam_code`);
;

CREATE TABLE IF NOT EXISTS `examessays` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_code` VARCHAR(45) NOT NULL,
  `essay_content` TEXT NULL DEFAULT NULL,
  `contents` TEXT NOT NULL,
  `create_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 26
DEFAULT CHARACTER SET = utf8;

ALTER TABLE `questions` 
ADD COLUMN `similar_content` ENUM('1', '0') NULL DEFAULT '0' COMMENT '0: ko trùng | 1: có trùng' AFTER `edit_by`;


-- -----------------------------------------------------
-- Placeholder table for view `score_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `score_group` (`counter` INT, `user_id` INT, `object_id` INT, `exam_code` INT, `major_id` INT, `score` INT);


USE `eytestonlineorg`;

-- -----------------------------------------------------
-- View `score_group`
-- -----------------------------------------------------
USE `eytestonlineorg`;
CREATE  OR REPLACE  VIEW `score_group` AS select count(`examapplicants`.`id`) AS `counter`,`examapplicants`.`user_id` AS `user_id`,`examapplicants`.`object_id` AS `object_id`,`examapplicants`.`exam_code` AS `exam_code`,`examapplicants`.`major_id` AS `major_id`,(if(isnull(`examapplicants`.`exam_score`),0,`examapplicants`.`exam_score`) + if(isnull(`examapplicants`.`essay_score`),0,`examapplicants`.`essay_score`)) AS `score` from `examapplicants` where ((`examapplicants`.`is_finish` = 'done') or (`examapplicants`.`is_finish` = 'fail')) group by `examapplicants`.`id`;

DELIMITER $$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `anwsers_AFTER_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `anwsers_AFTER_INSERT`
AFTER INSERT ON `anwsers`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT *, count(anwsers.check) as is_correctly FROM anwsers
    join questions on anwsers.question_id = questions.id
	WHERE anwsers.question_id = NEW.question_id and 
    questions.type_anwser_id = 'single' and anwsers.check = 1
    group by anwsers.check
    having is_correctly > 1
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'This question has only ONE answer';
  END IF;
  /*============================================================*/
  IF (EXISTS(
	SELECT * FROM anwsers
    join questions on anwsers.question_id = questions.id
	WHERE anwsers.question_id = NEW.question_id and 
    questions.type_anwser_id = 'essay'
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Answer type is essay';
  END IF;
  /*============================================================*/
   IF (EXISTS(
	SELECT * FROM anwsers
	WHERE anwsers.question_id = NEW.question_id and 
    anwsers.content is null
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Please input content of question';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `anwsers_AFTER_UPDATE` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `anwsers_AFTER_UPDATE`
AFTER UPDATE ON `anwsers`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT *, count(anwsers.check) as is_correctly FROM anwsers
    join questions on anwsers.question_id = questions.id
	WHERE anwsers.question_id = NEW.question_id and 
    questions.type_anwser_id = 'single' and anwsers.check = 1
    group by anwsers.check
    having is_correctly > 1
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'This question has only ONE answer';
  END IF;
  /*============================================================*/
  IF (EXISTS(
	SELECT * FROM anwsers
    join questions on anwsers.question_id = questions.id
	WHERE anwsers.question_id = NEW.question_id and 
    questions.type_anwser_id = 'essay'
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Answer type is essay';
  END IF;
  /*============================================================*/
   IF (EXISTS(
	SELECT * FROM anwsers
	WHERE anwsers.question_id = NEW.question_id and 
    anwsers.content is null
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Please input content of question';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `examquestions_BEFORE_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `examquestions_BEFORE_INSERT`
BEFORE INSERT ON `examquestions`
FOR EACH ROW
BEGIN
  IF (EXISTS(
	SELECT * FROM examquestions
	WHERE NEW.down_time <= 0 or NEW.limit_join <= 0
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'The time to register the exam is over or the limit of applicants is reached';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `examquestions_BEFORE_UPDATE` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `examquestions_BEFORE_UPDATE`
BEFORE UPDATE ON `examquestions`
FOR EACH ROW
BEGIN
  IF (EXISTS(
	SELECT * FROM examquestions
	WHERE NEW.down_time <= 0 or NEW.limit_join <= 0
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'The time to register the exam is over or the limit of applicants is reached';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `permissiondefaults_AFTER_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `permissiondefaults_AFTER_INSERT`
AFTER INSERT ON `permissiondefaults`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.id and
    permissiondefaults.slug_page is null and
    permissiontypes.is_page = 1
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Slug name is required';
  END IF;
  /*=================*/
  IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
	WHERE permissiondefaults.id = NEW.id and
    permissiondefaults.index_page = -1 and
    permissiondefaults.permissionmode_id < 6
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Index is required';
  END IF;
  /*=================*/
    IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.id and
    (permissiontypes.id > 4 and (permissiondefaults.slug_page is not null or permissiondefaults.index_page <> -1)) or
    (permissiontypes.id = 0 and permissiondefaults.index_page = -1) or
    (permissiontypes.id = 4 and permissiondefaults.index_page = -1)
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Invalid permission';
  END IF;
  /*=================*/
    IF (EXISTS(
	SELECT count(permissiondefaults.index_page) as duplicate_index FROM permissiondefaults
    join permissionmodes on permissiondefaults.permissionmode_id = permissionmodes.id
	WHERE permissiondefaults.index_page <> -1 and
    permissionmodes.id < 6
    group by permissiondefaults.index_page
    having duplicate_index > 1
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Index cannot be duplicated';
  END IF;
  /*=================*/
    IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.parentdefault_id
    and permissiondefaults.permissionmode_id >= NEW.permissionmode_id
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Parent mode is wrong';
  END IF;
  /*=================*/
  IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
	WHERE permissiondefaults.id = NEW.id and
    NEW.parentdefault_id= 0 and
    permissiondefaults.permissionmode_id > 4
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Root must be Page or Nav';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `permissiondefaults_AFTER_UPDATE` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `permissiondefaults_AFTER_UPDATE`
AFTER UPDATE ON `permissiondefaults`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.id and
    permissiondefaults.slug_page is null and
    permissiontypes.is_page = 1
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Slug name is required';
  END IF;
  /*=================*/
  IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
	WHERE permissiondefaults.id = NEW.id and
    permissiondefaults.index_page = -1 and
    permissiondefaults.permissionmode_id < 6
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Index is required';
  END IF;
  /*=================*/
    IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.id and
    (permissiontypes.id > 4 and (permissiondefaults.slug_page is not null or permissiondefaults.index_page <> -1)) or
    (permissiontypes.id = 0 and permissiondefaults.index_page = -1) or
    (permissiontypes.id = 4 and permissiondefaults.index_page = -1)
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Invalid permission';
  END IF;
  /*=================*/
    IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
    join permissiontypes on permissiontypes.id = permissionmodes.permissiontype_id
	WHERE permissiondefaults.id = NEW.parentdefault_id
    and permissiondefaults.permissionmode_id >= NEW.permissionmode_id
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Parent mode is wrong';
  END IF;
  /*=================*/
  IF (EXISTS(
	SELECT * FROM permissiondefaults
    join permissionmodes on permissionmodes.id = permissiondefaults.permissionmode_id
	WHERE permissiondefaults.id = NEW.id and
    NEW.parentdefault_id= 0 and
    permissiondefaults.permissionmode_id > 4
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Root must be Page or Nav';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `permissiondefaults_BEFORE_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `permissiondefaults_BEFORE_INSERT`
BEFORE INSERT ON `permissiondefaults`
FOR EACH ROW
BEGIN
IF NEW.permissionmode_id < 6
	THEN
	SET NEW.index_page = (select max(index_page) + 1 from permissiondefaults);
    END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `permissiongroups_BEFORE_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `permissiongroups_BEFORE_INSERT`
BEFORE INSERT ON `permissiongroups`
FOR EACH ROW
BEGIN
SET NEW.index_group = (select max(index_group) +1 from permissiongroups);
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `users_BEFORE_INSERT` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `users_BEFORE_INSERT`
BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT *,FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(NEW.birthday, '%Y-%m-%d %H:%i:%s'))/365) as age 
    FROM users having age < 14
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Invalid age';
  END IF;
END$$

USE `eytestonlineorg`$$
DROP TRIGGER IF EXISTS `users_BEFORE_UPDATE` $$

USE `eytestonlineorg`$$
CREATE

TRIGGER `users_BEFORE_UPDATE`
BEFORE UPDATE ON `users`
FOR EACH ROW
BEGIN
IF (EXISTS(
	SELECT *,FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(NEW.birthday, '%Y-%m-%d %H:%i:%s'))/365) as age 
    FROM users having age < 14
)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Invalid age';
  END IF;
END$$


DELIMITER ;


