ALTER TABLE `optional_subject_level`
ADD COLUMN `options_osl` SET('HAS_SIMULATED_GRADES') NULL AFTER `has_schedule`;

ALTER TABLE `subject_course`
CHANGE COLUMN `options_sbc` `options_sbc` SET('SHOW_IN_REPOR_CARD', 'HAS_SIMULATED_GRADES') NULL DEFAULT 'SHOW_IN_REPOR_CARD' ;

CREATE TABLE `range_simulated_grades` (
  `serial_rsg` INT NOT NULL AUTO_INCREMENT,
  `start_rsg` VARCHAR(45) NULL,
  `end_rsg` VARCHAR(45) NULL,
  `type_rsg` ENUM('QUALITATIVE', 'QUANTITATIVE') NOT NULL DEFAULT 'QUANTITATIVE',
  `status_rsg` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rsg`));

  CREATE TABLE IF NOT EXISTS `category_international_baccalaureate` (
  `serial_cib` INT NOT NULL AUTO_INCREMENT,
  `description_cib` VARCHAR(45) NULL,
  `status_cib` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cib`));


 CREATE TABLE IF NOT EXISTS `international_baccalaureate_subject` (
  `serial_ibs` INT NOT NULL AUTO_INCREMENT,
  `serial_cib` INT NOT NULL,
  `serial_rsg` INT NOT NULL,
  `name_ibs` VARCHAR(45) NULL,
  `description_ibs` VARCHAR(45) NULL,
  `status_ibs` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ibs`),
  INDEX `fk_international_baccalaureate_category_internaciona_idx` (`serial_cib` ASC),
  INDEX `fk_international_baccalaureate_range_simulated_grade_idx` (`serial_rsg` ASC),
    CONSTRAINT `fk_international_baccalaureate_category_internaciona`
      FOREIGN KEY (`serial_cib`)
      REFERENCES `category_international_baccalaureate` (`serial_cib`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_international_baccalaureate_range_simulated_grade`
      FOREIGN KEY (`serial_rsg`)
      REFERENCES `range_simulated_grades` (`serial_rsg`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);


  CREATE TABLE IF NOT EXISTS `related_international_baccalaureate` (
  `serial_rib` INT NOT NULL AUTO_INCREMENT,
  `serial_sbc` INT NULL,
  `serial_osb` INT NULL,
  `status_rib` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `serial_ibs` INT NOT NULL,
  PRIMARY KEY (`serial_rib`),
  INDEX `fk_related_international_baccalaureate_subject_course_idx` (`serial_sbc` ASC),
  INDEX `fk_related_international_baccalaureate_optional_subject1_idx` (`serial_osb` ASC),
  INDEX `fk_related_international_baccalaureate_idx` (`serial_ibs` ASC),
  CONSTRAINT `fk_related_international_baccalaureate_subject_course`
    FOREIGN KEY (`serial_sbc`)
    REFERENCES `subject_course` (`serial_sbc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_related_international_baccalaureate_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_related_international_baccalaureate`
    FOREIGN KEY (`serial_ibs`)
    REFERENCES `international_baccalaureate_subject` (`serial_ibs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `entry_simulated_grades` (
  `serial_esg` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT NOT NULL,
  `serial_lvl` INT NULL,
  `serial_set` INT NULL,
  `start_date_esg` DATE NULL,
  `end_date_esg` DATE NULL,
  `description_esg` VARCHAR(45) NOT NULL,
  `status_esg` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_esg`),
  INDEX `fk_lvl_esg_idx` (`serial_lvl` ASC),
  INDEX `fk_set_esg_idx` (`serial_lvl` ASC),
  INDEX `fk_scy_esg_idx` (`serial_scy` ASC),
CONSTRAINT `fk_lvl_esg`
  FOREIGN KEY (`serial_lvl`)
  REFERENCES `level` (`serial_lvl`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `fk_set_esg`
  FOREIGN KEY (`serial_set`)
  REFERENCES `section` (`serial_set`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `fk_scy_esg`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION);


  CREATE TABLE IF NOT EXISTS `international_subject_entry` (
  `serial_ise` INT NOT NULL AUTO_INCREMENT,
  `serial_ibs` INT NOT NULL,
  `serial_esg` INT NOT NULL,
  `status_ise` ENUM('AT_DATE', 'ENABLE', 'INACTIVE') NOT NULL DEFAULT 'AT_DATE',
  PRIMARY KEY (`serial_ise`),
  INDEX `fk_international_subject_entry_international_baccalaureate__idx` (`serial_ibs` ASC),
  INDEX `fk_international_subject_entry_entry_simulated_grades1_idx` (`serial_esg` ASC),
  CONSTRAINT `fk_international_subject_entry_international_baccalaureate_su1`
    FOREIGN KEY (`serial_ibs`)
    REFERENCES `international_baccalaureate_subject` (`serial_ibs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_international_subject_entry_entry_simulated_grades1`
    FOREIGN KEY (`serial_esg`)
    REFERENCES `entry_simulated_grades` (`serial_esg`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `student_simulated_grades` (
  `serial_ssg` INT NOT NULL AUTO_INCREMENT,
  `serial_ise` INT NOT NULL,
  `serial_std` INT NOT NULL,
  `grade_ssg` VARCHAR(45) NULL,
  `status_ssg` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ssg`),
  INDEX `fk_student_simulated_grades_international_entry_idx` (`serial_ise` ASC),
  INDEX `fk_student_simulated_grades_student_idx` (`serial_std` ASC),
  CONSTRAINT `fk_student_simulated_grades_international_entry`
    FOREIGN KEY (`serial_ise`)
    REFERENCES `international_subject_entry` (`serial_ise`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student_simulated_grades_student`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


CREATE TABLE `extra_point_simulated` (
  `serial_eps` INT NOT NULL AUTO_INCREMENT,
  `first_ibs` INT NOT NULL,
  `second_ibs` INT NOT NULL,
  `first_grade` VARCHAR(45) NOT NULL,
  `second_grade` VARCHAR(45) NOT NULL,
  `extra_point` VARCHAR(45) NOT NULL,
  `status_eps` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_eps`),
  INDEX `fk_first_ibs_idx` (`first_ibs` ASC),
  INDEX `fk-second_ibs_idx` (`second_ibs` ASC),
  CONSTRAINT `fk_first_ibs`
    FOREIGN KEY (`first_ibs`)
    REFERENCES `international_baccalaureate_subject` (`serial_ibs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk-second_ibs`
    FOREIGN KEY (`second_ibs`)
    REFERENCES `international_baccalaureate_subject` (`serial_ibs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `international_baccalaureate_subject`
ADD COLUMN `options_ibs` SET('FIRST_SBJ_EXT_POINT', 'SECOND_SBJ_EXT_POINT') NOT NULL AFTER `description_ibs`;


-- allowed
insert into process (serial_prc, prc_serial_prc, name_prc, link_prc, weight_prc, status_prc) values (672,199,'Calificar Notas Anticipadas','laravel/public/studentSimulatedGrades/index',303,'LOCKEABLE');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getindex');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@postinformationbycourseandentry');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getinternationalsubjectgradesbystudent');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getexcelsimulatedgradesbyentry');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getexcelsimulatedgradesbystudent');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getexcelstudentsgradebysubject');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@postsimulatedgradesbystudent');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@postsimulatedgradesbysubject');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'studentsimulatedgradesreportcontroller@getstudentsimulatedgradesbysubject');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'simulatedgradesreportcontroller@postlevelsectionyear');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'simulatedgradesreportcontroller@postentriessimulatedgrades');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (490,'simulatedgradesreportcontroller@postcheckconfiguredsubjects');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (477,'gradereportscontroller@getstudentsbyguardiansimulatedgrades');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (477,'studentsimulatedgradesreportcontroller@postsimulatedgradesbystudent');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (672,'studentsimulatedgradescontroller@getindex');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (672,'studentsimulatedgradescontroller@poststudentsimulatedgrades');
INSERT INTO `allowed_pages_by_process` (`serial_prc`,`url_dsc`) VALUES (672,'studentsimulatedgradescontroller@postsavestudentsimulatedgrade');