-- MySQL Script generated by MySQL Workbench
-- 04/21/16 16:46:46
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema rutademic
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema ismaq
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `ability`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ability` ;

CREATE TABLE IF NOT EXISTS `ability` (
  `serial_abl` INT NOT NULL AUTO_INCREMENT,
  `name_abl` VARCHAR(100) NULL,
  `description_esp_abl` TEXT NULL,
  `description_eng_abl` TEXT NULL,
  `status_abl` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_abl`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `achievement_level`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `achievement_level` ;

CREATE TABLE IF NOT EXISTS `achievement_level` (
  `serial_alv` INT NOT NULL AUTO_INCREMENT,
  `name_alv` VARCHAR(45) NULL,
  `status_alv` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_alv`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `desprition_achievement_level`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `desprition_achievement_level` ;

CREATE TABLE IF NOT EXISTS `description_achievement_level` (
  `serial_dal` INT NOT NULL AUTO_INCREMENT,
  `description_eng_dal` TEXT NULL,
  `description_esp_dal` TEXT NULL,
  `status_dal` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_dal`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ability_academic_area`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ability_academic_area` ;

CREATE TABLE IF NOT EXISTS `ability_academic_area` (
  `serial_aaa` INT NOT NULL AUTO_INCREMENT,
  `serial_abl` INT NOT NULL,
  `serial_aca` INT(11) NOT NULL,
   serial_scy INT(11) NOT NULL,
  `status_aaa` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_aaa`),
  CONSTRAINT `fk_ability_has_academic_area_ability1`
    FOREIGN KEY (`serial_abl`)
    REFERENCES `ability` (`serial_abl`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ability_has_academic_area_academic_area1`
    FOREIGN KEY (`serial_aca`)
    REFERENCES `academic_area` (`serial_aca`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
	CONSTRAINT `fk_ability_scy_ability1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
	)
ENGINE = InnoDB;

CREATE INDEX `fk_ability_has_academic_area_academic_area1_idx` ON `ability_academic_area` (`serial_aca` ASC);

CREATE INDEX `fk_ability_has_academic_area_ability1_idx` ON `ability_academic_area` (`serial_abl` ASC);


-- -----------------------------------------------------
-- Table `subject_year_grade_abilities`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `subject_year_grade_abilities` ;

CREATE TABLE IF NOT EXISTS `subject_year_grade_abilities` (
  `serial_sga` INT NOT NULL AUTO_INCREMENT,
  `serial_aaa` INT NULL,
  `serial_osb` INT(11) NULL,
  `serial_sbj` INT(11) NOT NULL,
  `status_sga` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_sga`),
  CONSTRAINT `fk_achievement_level_subject_year_has_ability_academic_area_a2`
    FOREIGN KEY (`serial_aaa`)
    REFERENCES `ability_academic_area` (`serial_aaa`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_achievement_level_subject_year_has_ability_academic_area_o1`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_achievement_level_subject_year_has_ability_academic_area_s1`
    FOREIGN KEY (`serial_sbj`)
    REFERENCES `subject` (`serial_sbj`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_achievement_level_subject_year_has_ability_academic_area_idx` ON `subject_year_grade_abilities` (`serial_aaa` ASC);

CREATE INDEX `fk_achievement_level_subject_year_has_ability_academic_area_idx2` ON `subject_year_grade_abilities` (`serial_osb` ASC);

CREATE INDEX `fk_achievement_level_subject_year_has_ability_academic_area_idx3` ON `subject_year_grade_abilities` (`serial_sbj` ASC);


-- -----------------------------------------------------
-- Table `achievement_level_subject_year`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `achievement_level_subject_year` ;

CREATE TABLE IF NOT EXISTS `achievement_level_subject_year` (
  `serial_als` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `serial_alv` INT NOT NULL,
  `serial_dal` INT NOT NULL,
  `color_als` VARCHAR(45) NULL,
  `value_alv` DECIMAL(5,2) NULL,
  `status_als` ENUM('ACTIVE', 'INACTIVE') NULL,
  `serial_sga` INT NOT NULL,
  PRIMARY KEY (`serial_als`),
  CONSTRAINT `fk_achievement_level_subject_year_desprition_achievement_level1`
    FOREIGN KEY (`serial_dal`)
    REFERENCES `description_achievement_level` (`serial_dal`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_achievement_level_subject_year_achievement_level1`
    FOREIGN KEY (`serial_alv`)
    REFERENCES `achievement_level` (`serial_alv`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_achievement_level_subject_year_subject_year_grade_abilities1`
    FOREIGN KEY (`serial_sga`)
    REFERENCES `subject_year_grade_abilities` (`serial_sga`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_achievement_level_subject_year_desprition_achievement_le_idx` ON `achievement_level_subject_year` (`serial_dal` ASC);

CREATE INDEX `fk_achievement_level_subject_year_achievement_level1_idx` ON `achievement_level_subject_year` (`serial_alv` ASC);

CREATE INDEX `fk_achievement_level_subject_year_subject_year_grade_abilit_idx` ON `achievement_level_subject_year` (`serial_sga` ASC);


-- -----------------------------------------------------
-- Table `student_subject_ability_year_grade`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `student_subject_ability_year_grade` ;

CREATE TABLE IF NOT EXISTS `student_subject_ability_year_grade` (
  `serial_ssg` INT NOT NULL AUTO_INCREMENT,
  `serial_std` INT NOT NULL,
  `serial_als` INT UNSIGNED NOT NULL,
  `status_ssg` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_ssg`),
  CONSTRAINT `fk_student_has_subject_year_grade_ability_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student_has_subject_year_grade_ability_achievement_level_s1`
    FOREIGN KEY (`serial_als`)
    REFERENCES `achievement_level_subject_year` (`serial_als`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_student_has_subject_year_grade_ability_student1_idx` ON `student_subject_ability_year_grade` (`serial_std` ASC);

CREATE INDEX `fk_student_has_subject_year_grade_ability_achievement_level_idx` ON `student_subject_ability_year_grade` (`serial_als` ASC);


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


ALTER TABLE `subject_course` 
ADD COLUMN `abilities_sbc` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `project_sbc`;

ALTER TABLE `optional_subject` 
ADD COLUMN `abilities_osb` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `project_osb`;


-- verificar el serial_prs correspondiente al proceso

INSERT INTO `process` (serial_prc,`prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES (494,'363', 'Calificar Habilidades', 'laravel/public/gradeAblities/index', '4', 'LOCKABLE');


ALTER TABLE `achievement_level` 
CHANGE COLUMN `name_alv` `name_alv` TEXT NULL DEFAULT NULL ;

ALTER TABLE `subject_year_grade_abilities` 
DROP FOREIGN KEY `fk_achievement_level_subject_year_has_ability_academic_area_s1`;
ALTER TABLE `subject_year_grade_abilities` 
CHANGE COLUMN `serial_sbj` `serial_sbj` INT(11) NULL ;
ALTER TABLE `subject_year_grade_abilities` 
ADD CONSTRAINT `fk_achievement_level_subject_year_has_ability_academic_area_s1`
  FOREIGN KEY (`serial_sbj`)
  REFERENCES `subject` (`serial_sbj`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  

SELECT * FROM allowed_pages_by_process;
insert INTO allowed_pages_by_process values(494,'gradeabilitycontroller@getindex');
insert INTO allowed_pages_by_process values(494,'gradeabilitycontroller@postachievementslevelsubject');
insert INTO allowed_pages_by_process values(494,'gradeabilitycontroller@poststutentabilities');
insert INTO allowed_pages_by_process values(494,'gradeabilitycontroller@postsavestudentabilities');