-- para crear fases por materia, escala de notas para los riterios
ALTER TABLE `student_period_exam` ADD COLUMN `observations` TEXT NULL AFTER `grade_stx`;

-- -----------------------------------------------------
-- Table `phase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phase` (
  `serial_ph` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(256) NOT NULL,
  `description` TEXT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ph`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `criteria_average_equivalence`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `criteria_average_equivalence` (
  `serial_cae` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `min_range` DECIMAL(5,3) NOT NULL,
  `max_range` DECIMAL(5,3) NOT NULL,
  `equivalence` VARCHAR(64) NOT NULL,
  `description` TEXT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cae`),
  INDEX `fk_criteria_average_equivalence_school_year1_idx` (`serial_scy` ASC),
  CONSTRAINT `fk_criteria_average_equivalence_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `phase_student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phase_student` (
  `serial_phs` INT NOT NULL AUTO_INCREMENT,
  `serial_ph` INT NOT NULL,
  `serial_std` INT(11) NOT NULL,
  `serial_prd` INT(11) NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_phs`),
  INDEX `fk_phase_student_phase1_idx` (`serial_ph` ASC),
  INDEX `fk_phase_student_student1_idx` (`serial_std` ASC),
  INDEX `fk_phase_student_subject1_idx` (`serial_sbj` ASC),
  INDEX `fk_phase_student_optional_subject1_idx` (`serial_osb` ASC),
  INDEX `fk_phase_student_period1_idx` (`serial_prd` ASC),
  CONSTRAINT `fk_phase_student_phase1`
    FOREIGN KEY (`serial_ph`)
    REFERENCES `phase` (`serial_ph`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_student_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_student_subject1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_student_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_student_period1`
    FOREIGN KEY (`serial_prd`)
    REFERENCES `period` (`serial_prd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `criteria_range`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `criteria_range` (
  `serial_crr` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `min_range` DECIMAL(5,3) NOT NULL,
  `max_range` DECIMAL(5,3) NOT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_crr`),
  INDEX `fk_criteria_range_school_year1_idx` (`serial_scy` ASC),
  CONSTRAINT `fk_criteria_range_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `grade_criteria_range`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `grade_criteria_range` (
  `serial_gcr` INT NOT NULL AUTO_INCREMENT,
  `serial_gct` INT(11) NOT NULL,
  `serial_crr` INT NOT NULL,
  `serial_lvl` INT(11) NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `description_es` TEXT NULL,
  `description_en` TEXT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gcr`),
  INDEX `fk_grade_criteria_range_grade_criteria1_idx` (`serial_gct` ASC),
  INDEX `fk_grade_criteria_range_criteria_range1_idx` (`serial_crr` ASC),
  INDEX `fk_grade_criteria_range_level1_idx` (`serial_lvl` ASC),
  INDEX `fk_grade_criteria_range_subject1_idx` (`serial_sbj` ASC),
  INDEX `fk_grade_criteria_range_optional_subject1_idx` (`serial_osb` ASC),
  CONSTRAINT `fk_grade_criteria_range_grade_criteria1`
    FOREIGN KEY (`serial_gct`)
    REFERENCES `grade_criteria` (`serial_gct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_range_criteria_range1`
    FOREIGN KEY (`serial_crr`)
    REFERENCES `criteria_range` (`serial_crr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_range_level1`
    FOREIGN KEY (`serial_lvl`)
    REFERENCES `level` (`serial_lvl`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_range_subject1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_range_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `phase_range`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phase_range` (
  `serial_phr` INT NOT NULL AUTO_INCREMENT,
  `serial_ph` INT NOT NULL,
  `serial_gct` INT(11) NOT NULL,
  `serial_crr` INT NOT NULL,
  `serial_lvl` INT(11) NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `description_es` TEXT NULL,
  `description_en` TEXT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_phr`),
  INDEX `fk_phase_range_criteria_range1_idx` (`serial_crr` ASC),
  INDEX `fk_phase_range_phase1_idx` (`serial_ph` ASC),
  INDEX `fk_phase_range_grade_criteria1_idx` (`serial_gct` ASC),
  INDEX `fk_phase_range_level1_idx` (`serial_lvl` ASC),
  INDEX `fk_phase_range_subject1_idx` (`serial_sbj` ASC),
  INDEX `fk_phase_range_optional_subject1_idx` (`serial_osb` ASC),
  CONSTRAINT `fk_phase_range_criteria_range1`
    FOREIGN KEY (`serial_crr`)
    REFERENCES `criteria_range` (`serial_crr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_range_phase1`
    FOREIGN KEY (`serial_ph`)
    REFERENCES `phase` (`serial_ph`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_range_grade_criteria1`
    FOREIGN KEY (`serial_gct`)
    REFERENCES `grade_criteria` (`serial_gct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_range_level1`
    FOREIGN KEY (`serial_lvl`)
    REFERENCES `level` (`serial_lvl`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_range_subject1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_phase_range_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `grade_criteria_academic_area`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `grade_criteria_academic_area` (
  `serial_gcaa` INT NOT NULL AUTO_INCREMENT,
  `serial_gct` INT(11) NOT NULL,
  `serial_aca` INT(11) NOT NULL,
  `serial_scy` INT(11) NOT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gcaa`),
  INDEX `fk_grade_criteria_academic_area_grade_criteria1_idx` (`serial_gct` ASC),
  INDEX `fk_grade_criteria_academic_area_academic_area1_idx` (`serial_aca` ASC),
  INDEX `fk_grade_criteria_academic_area_school_year1_idx` (`serial_scy` ASC),
  CONSTRAINT `fk_grade_criteria_academic_area_grade_criteria1`
    FOREIGN KEY (`serial_gct`)
    REFERENCES `grade_criteria` (`serial_gct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_academic_area_academic_area1`
    FOREIGN KEY (`serial_aca`)
    REFERENCES `academic_area` (`serial_aca`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grade_criteria_academic_area_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `criteria_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `criteria_type` (
  `serial_crt` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_crt`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `subject_criteria_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `subject_criteria_type` (
  `serial_sct` INT NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `criteria_type` ENUM('YEAR','PHASE') NOT NULL DEFAULT 'YEAR',
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sct`),
  INDEX `fk_subject_criteria_type_subject1_idx` (`serial_sbj` ASC),
  INDEX `fk_subject_criteria_type_optional_subject1_idx` (`serial_osb` ASC),
  CONSTRAINT `fk_subject_criteria_type_subject1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_subject_criteria_type_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `criteria_period_average`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `criteria_period_average` (
  `serial_cpa` INT NOT NULL AUTO_INCREMENT,
  `serial_prd` INT(11) NOT NULL,
  `serial_std` INT(11) NOT NULL,
  `serial_gct` INT(11) NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `grade` INT NOT NULL,
  `observations` TEXT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cpa`),
  INDEX `fk_criteria_period_average_period1_idx` (`serial_prd` ASC),
  INDEX `fk_criteria_period_average_student1_idx` (`serial_std` ASC),
  INDEX `fk_criteria_period_average_subject1_idx` (`serial_sbj` ASC),
  INDEX `fk_criteria_period_average_optional_subject1_idx` (`serial_osb` ASC),
  INDEX `fk_criteria_period_average_grade_criteria1_idx` (`serial_gct` ASC),
  CONSTRAINT `fk_criteria_period_average_period1`
    FOREIGN KEY (`serial_prd`)
    REFERENCES `period` (`serial_prd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_criteria_period_average_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_criteria_period_average_subject1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_criteria_period_average_optional_subject1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_criteria_period_average_grade_criteria1`
    FOREIGN KEY (`serial_gct`)
    REFERENCES `grade_criteria` (`serial_gct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

--- INSERT EN TABLAS DE EQUIVALENCIA


INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '0', '3', '1', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '4', '7', '2', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '8', '12', '3', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '13', '17', '4', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '18', '22', '5', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '23', '27', '6', 'ACTIVE');
INSERT INTO `criteria_average_equivalence` (`serial_scy`, `min_range`, `max_range`, `equivalence`, `status`) VALUES ('1', '28', '32', '7', 'ACTIVE');


-- la descripcion de la nota se ingresa el campo 'description' en la tabla 'criteria_average_equivalence'