-- MySQL Script generated by MySQL Workbench
-- 03/08/18 21:25:33
-- 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
-- -----------------------------------------------------


-- -----------------------------------------------------
-- Table `incidence_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_category`;
CREATE TABLE IF NOT EXISTS `incidence_category` (
  `serial_ica` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `incidence_serial_ica` INT(11) NULL DEFAULT NULL,
  `name_ica` VARCHAR(80) NOT NULL,
  `attendance_relation_ica` ENUM('NONE', 'FI', 'FJ') NOT NULL DEFAULT 'NONE',
  `status_ica` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ica`))
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indices de la tabla `incidence_category`
--
ALTER TABLE `incidence_category`
 ADD KEY `fk_incidence_category_parent_idx` (`incidence_serial_ica`);

 --
-- Filtros para la tabla `incidence_category`
--
ALTER TABLE `incidence_category`
ADD CONSTRAINT `fk_incidence_category_parent` FOREIGN KEY (`incidence_serial_ica`) REFERENCES `incidence_category` (`serial_ica`);



-- -----------------------------------------------------
-- Table `incidence_setup`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_setup`;
CREATE TABLE IF NOT EXISTS `incidence_setup` (
  `serial_ise` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_ica` INT(11) NOT NULL,
  `frequency_ise` INT(1) NOT NULL DEFAULT 1,
  `value_ise` DECIMAL(2) NULL,
  `lower_discipline_level_ise` ENUM('YES', 'NO') NOT NULL DEFAULT 'NO',
  `by_subject_ise` ENUM('YES', 'NO') NOT NULL DEFAULT 'NO',
  `status_ise` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ise`),
  CONSTRAINT `fk_incidence_setup_incidence_category`
    FOREIGN KEY (`serial_ica`)
    REFERENCES `incidence_category` (`serial_ica`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE INDEX `fk_incidence_setup_incidence_category_idx` ON `incidence_setup` (`serial_ica` ASC);


-- -----------------------------------------------------
-- Table `incidence_year_level`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_year_level`;
CREATE TABLE IF NOT EXISTS `incidence_year_level` (
  `serial_iyl` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `serial_lvl` INT(11) NOT NULL,
  `status_iyl` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_iyl`))
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indices de la tabla `incidence_year_level`
--
ALTER TABLE `incidence_year_level`
 ADD KEY `fk_incidence_year_school_year_idx` (`serial_scy`), 
 ADD KEY `fk_incidence_year_level_idx` (`serial_lvl`);

 --
-- Filtros para la tabla `incidence_year_level`
--
ALTER TABLE `incidence_year_level`
ADD CONSTRAINT `fk_incidence_year_level` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`),
ADD CONSTRAINT `fk_incidence_year_school_year` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);


-- -----------------------------------------------------
-- Table `incidence_student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_student`;
CREATE TABLE IF NOT EXISTS `incidence_student` (
  `serial_ist` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_ica` INT(11) NOT NULL,
  `serial_usr` INT(11) NOT NULL,
  `serial_std` INT(11) NOT NULL,
  `serial_sbc` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `serial_sbp` INT(11) NOT NULL,
  `serial_scd` INT(11) NULL,
  `date_ist` DATE NOT NULL,
  `insert_ist` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `comentary_ist` VARCHAR(80) NULL DEFAULT NULL,
  `status_ist` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ist`)
  )
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indices de la tabla `incidence_student`
--
ALTER TABLE `incidence_student`
 ADD KEY `fk_incidence_student_incidence_category_idx` (`serial_ica`), 
 ADD KEY `fk_incidence_student_user_idx` (`serial_usr`), 
 ADD KEY `fk_incidence_student_student_idx` (`serial_std`),
 ADD KEY `fk_incidence_student_subject_course_idx` (`serial_sbc`), 
 ADD KEY `fk_incidence_student_optional_subject_idx` (`serial_osb`),
 ADD KEY `fk_incidence_student_subperiod_idx` (`serial_sbp`), 
 ADD KEY `fk_incidence_student_schedule_idx` (`serial_scd`);

 --
-- Filtros para la tabla `incidence_student`
--
ALTER TABLE `incidence_student`
ADD CONSTRAINT `fk_incidence_student_incidence_category` FOREIGN KEY (`serial_ica`) REFERENCES `incidence_category` (`serial_ica`),
ADD CONSTRAINT `fk_incidence_student_user` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
ADD CONSTRAINT `fk_incidence_student_student` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
ADD CONSTRAINT `fk_incidence_student_subject_course` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`),
ADD CONSTRAINT `fk_incidence_student_optional_subject` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`),
ADD CONSTRAINT `fk_incidence_student_subperiod` FOREIGN KEY (`serial_sbp`) REFERENCES `subperiod` (`serial_sbp`),
ADD CONSTRAINT `fk_incidence_student_schedule` FOREIGN KEY (`serial_scd`) REFERENCES `schedule` (`serial_scd`);


-- -----------------------------------------------------
-- Table `incidence_student_summary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_student_summary`;
CREATE TABLE IF NOT EXISTS `incidence_student_summary` (
  `serial_iss` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_ica` INT(11) NOT NULL,
  `serial_std` INT(11) NOT NULL,
  `total_iss` INT(5) NOT NULL DEFAULT 0,
  `cycle_iss` INT(5) NOT NULL DEFAULT 0,
  `status_iss` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_iss`)
 )
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indices de la tabla `incidence_student_summary`
--
ALTER TABLE `incidence_student_summary`
 ADD KEY `fk_incidence_student_summary_incidence_category_idx` (`serial_ica`), 
 ADD KEY `fk_incidence_student_summary_student_idx` (`serial_std`);

 --
-- Filtros para la tabla `incidence_student_summary`
--
ALTER TABLE `incidence_student_summary`
ADD CONSTRAINT `fk_incidence_student_summary_incidence_category` FOREIGN KEY (`serial_ica`) REFERENCES `incidence_category` (`serial_ica`),
ADD CONSTRAINT `fk_incidence_student_summary_student` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);


-- -----------------------------------------------------
-- Table `incidence_student_detail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `incidence_student_detail`;
CREATE TABLE IF NOT EXISTS `incidence_student_detail` (
  `serial_isd` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_iss` INT(11) NOT NULL,
  `serial_sbc` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `total_isd` INT(5) NOT NULL DEFAULT 0,
  `cycle_isd` INT(5) NOT NULL DEFAULT 0,
  `status_isd` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_isd`)
)
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indices de la tabla `incidence_student_detail`
--
ALTER TABLE `incidence_student_detail`
 ADD KEY `fk_incidence_student_detail_incidence_student_summary_idx` (`serial_iss`), 
 ADD KEY `fk_incidence_student_detail_subject_idx` (`serial_sbc`),
 ADD KEY `fk_incidence_student_detail_optional_subject_idx` (`serial_osb`);

 --
-- Filtros para la tabla `incidence_student_detail`
--
ALTER TABLE `incidence_student_detail`
ADD CONSTRAINT `fk_incidence_student_detail_incidence_student_summary` FOREIGN KEY (`serial_iss`) REFERENCES `incidence_student_summary` (`serial_iss`),
ADD CONSTRAINT `fk_incidence_student_detail_subject` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`),
ADD CONSTRAINT `fk_incidence_student_detail_optional_subject` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`);


ALTER TABLE `incidence_year_level` ADD `max_time_iyl` TIME NULL DEFAULT NULL AFTER `serial_lvl`;
ALTER TABLE `incidence_year_level` ADD `serial_set` INT(11) NOT NULL AFTER `serial_scy`;
ALTER TABLE incidence_year_level RENAME incidence_year_section_level;
ALTER TABLE `incidence_setup` CHANGE `lower_discipline_level_ise` `lower_discipline_level_ise` TINYINT(1) NOT NULL DEFAULT '0', CHANGE `by_subject_ise` `by_subject_ise` TINYINT(1) NOT NULL DEFAULT '0';

ALTER TABLE `incidence_student` ADD `value_ist` DECIMAL(2,0) NULL AFTER `comentary_ist`, ADD `serial_usr_delete_ist` INT(11) NULL AFTER `value_ist`, ADD `comentary_delete_ist` VARCHAR(80) NULL AFTER `serial_usr_delete_ist`;
ALTER TABLE `incidence_setup` CHANGE `value_ise` `value_ise` DECIMAL(5,2) NULL DEFAULT NULL;
ALTER TABLE `incidence_student` CHANGE `value_ist` `value_ist` DECIMAL(5,2) NULL DEFAULT NULL;

ALTER TABLE `incidence_student` ADD `by_subject_ist` TINYINT(1) NOT NULL DEFAULT '0' AFTER `value_ist`;
ALTER TABLE `incidence_category` CHANGE `attendance_relation_ica` `attendance_relation_ica` ENUM('NONE','UNEXCUSED_ABSENCE','EXCUSED_ABSENCE','DELAY') BINARY CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NONE';


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

-- inserto proceso de configuracion en el menú
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('659', '143', 'Configuración de Incidencias de Comportamiento', 'laravel/public/incidenceSetup/index', '4', 'LOCKABLE');

-- allowed by process
-- incidence setup
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getindex');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postindex');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getincidencecategory');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getlevelsetup');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postlevelsetup');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@createarrayforincidencelevel');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getincidencelevelsbyschoolyearsection');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postsavecategories');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getfrequencysetup');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@crearopcionesfrecuencia');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postfrequencysetup');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getfrequenciessetup');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@checksubcategoryincidencestudenttotals');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@validarsesion');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getcategoriesbyyear');

-- allowed incidence student
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindex');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getschedule');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getemployeebyserialusr');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getscheduledata');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getschedulebyteacher');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudents');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@studentloaddata');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudentsadmin');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudentincidencesrequirementload');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@validateoptionalsubjectincourseschedule');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@validatedatemaxtime');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getstudentincidencesload');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindexguardian');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getstudentsbyschoolyear');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getperiodsbystudentschoolyear');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getincidencesbystudentyearperiod');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindexadmin');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getsectionlevelspecialtybyyear');
INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where  link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getteachersbyyearsectionlevelspecialty');


ALTER TABLE `incidence_category` CHANGE `name_ica` `name_ica` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `incidence_student` CHANGE `comentary_ist` `comentary_ist` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, CHANGE `comentary_delete_ist` `comentary_delete_ist` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


-- proceso administracion de incidencias
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('660', '143', 'Administración de Incidencias de Comportamiento', 'laravel/public/incidenceStudent/index-admin', '4', 'LOCKABLE');


-- allowed administracion de incidencias
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getindexadmin');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getsectionlevelspecialtybyyear');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getteachersbyyearsectionlevelspecialty');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getschedulebyteacher');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@poststudentsadmin');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@poststudentincidencesrequirementload');
insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getstudentincidencesload');

ALTER TABLE `incidence_student_summary` ADD `serial_sbp` INT(11) NOT NULL AFTER `serial_std`;
ALTER TABLE `incidence_student_summary` ADD KEY `fk_incidence_student_summary_subperiod_idx` (`serial_sbp`);
ALTER TABLE `incidence_student_summary` ADD  CONSTRAINT `fk_incidence_student_summary_subperiod_idx` FOREIGN KEY (`serial_sbp`) REFERENCES `subperiod`(`serial_sbp`);