-- SCRIPT DE CREACION DE TABLA
CREATE TABLE `admission_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `description` VARCHAR(256) NULL,
  PRIMARY KEY (`id`)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_unicode_ci);
  
-- INSERCION DE TIPOS DE ADMISION
INSERT INTO `admission_type` (`id`,`name`, `description`) VALUES (1,'NEW STUDENT', 'Nuevo estudiante.');
INSERT INTO `admission_type` (`id`,`name`, `description`) VALUES (2,'NEW STUDENT WITH SIBLINGS', 'Nuevo estudiante con hermanos aplicantes.');
INSERT INTO `admission_type` (`id`,`name`, `description`) VALUES (3,'NEW STUDENT WITH SIBLINGS AT SCHOOL', 'Nuevo estudiante con hermanos en el colegio.');
INSERT INTO `admission_type` (`id`,`name`, `description`) VALUES (4,'NEW STUDENT WITH EX STUDENT BROTHER', 'Nuevo estudiante con hermanos ex-alumnos.');
INSERT INTO `admission_type` (`id`,`name`, `description`) VALUES (5,'RE-ENTRY', 'Reingreso.');

-- SCRIPT DE ACTUALIZACION DE TABLA DE ADMISIONES
ALTER TABLE `admission` 
CHANGE COLUMN `type_admission` `type_admission` INT NOT NULL ,
ADD INDEX `fk_admission_type_idx` (`type_admission` ASC);

SET foreign_key_checks = 0;


ALTER TABLE `admission` 
ADD CONSTRAINT `fk_admission_type`
  FOREIGN KEY (`type_admission`)
  REFERENCES `admission_type` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  
ALTER TABLE `previous_education_aspirant` 
ADD COLUMN `academic_report` INT(11) NOT NULL AFTER `previous_note_disciplien_asstu`,
ADD INDEX `documents_idx` (`academic_report` ASC);

ALTER TABLE `previous_education_aspirant`
ADD CONSTRAINT `documents`
  FOREIGN KEY (`academic_report`)
  REFERENCES `aspirant_documents` (`serial_asdc`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
ALTER TABLE `previous_education_aspirant`
CHANGE COLUMN `academic_report` `academic_report` INT(11) NULL ;

ALTER TABLE `previous_education_aspirant` 
CHANGE COLUMN `previous_year_asstu` `previous_year_asstu` INT(11) NULL DEFAULT NULL AFTER `serial_sch`,
CHANGE COLUMN `previous_level_asstu` `previous_level_asstu` INT(11) NULL DEFAULT NULL ,
ADD COLUMN `previous_section_asstu` INT(11) NULL AFTER `previous_year_asstu`;

ALTER TABLE `previous_education_aspirant` 
ADD COLUMN `previous_school_year_asstu` INT(11) NULL AFTER `previous_year_asstu`;

ALTER TABLE `previous_education_aspirant` 
ADD COLUMN `status_pred` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' AFTER `academic_report`;

ALTER TABLE `previous_education_aspirant` 
CHANGE COLUMN `previous_note_asstu` `previous_note_asstu` VARCHAR(20) NULL DEFAULT NULL ,
CHANGE COLUMN `previous_note_disciplien_asstu` `previous_note_disciplien_asstu` VARCHAR(20) NULL DEFAULT NULL ;

SET foreign_key_checks = 1;


-- ALLOWED PAGES PROCESS NUEVA APLICACION
insert into allowed_pages_by_process values (427, 'admissioncontroller@getcreate');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postcreate');
insert into allowed_pages_by_process values (427, 'admissioncontroller@getcreatedataadmission');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postaspirants');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postcheckciuniqueaspirant');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postvalidatebirthdate');
insert into allowed_pages_by_process values (427, 'admissioncontroller@getcontextschool');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postsearchaspirantguardianforci');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postcreatedataadmission');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postsearchaspirant');
insert into allowed_pages_by_process values (427, 'familycontroller@postfamilybyid');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postcontextaspirantfamily');
insert into allowed_pages_by_process values (427, 'admissioncontroller@postcontextstudent');

-- ALLOWED PAGES PROCESS RESTABLECER CONTRASENA
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@getindex');
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@postcreate');
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@postsearchrepresent');
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@getsearchaspirant');
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@postcheckemailunique');

-- ALLOWED PAGES PROCESS RESTABLECER CONTRASENA
INSERT INTO allowed_pages_by_process VALUES (415,'resetpasscontroller@postcheckemailunique');

-- MODIFY
INSERT INTO allowed_pages_by_process VALUES (467, 'admissioncontroller@postcheckciunique');

-- PROCESS
INSERT INTO process (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`) VALUES ('606', '321', 'Completar informacion de aspirante', 'laravel/public/Admission/alias-login');
INSERT INTO process (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`) VALUES ('607', '321', 'Reportes de admisiones', '#');
INSERT INTO process (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`) VALUES ('608', '607', 'Reporte general de aplicaciones', 'laravel/public/admissionReports/report-with-informs');


-- MODIFY
INSERT INTO allowed_pages_by_process VALUES (606, 'admissioncontroller@getaliaslogin'); 
INSERT INTO allowed_pages_by_process VALUES (606, 'admissioncontroller@postaliaslogin'); 
INSERT INTO allowed_pages_by_process VALUES (608, 'admissionreportcontroller@getreportwithinforms'); 
INSERT INTO allowed_pages_by_process VALUES (608, 'admissionreportcontroller@poststudentsectionlevelyear'); 
INSERT INTO allowed_pages_by_process VALUES (608, 'sectioncontroller@getsections'); 
INSERT INTO allowed_pages_by_process VALUES (608, 'levelcontroller@postactivelevels'); 
INSERT INTO allowed_pages_by_process VALUES (608, 'admissionreportcontroller@postgeneratepdf'); 
INSERT INTO allowed_pages_by_process VALUES (419, 'reportsadmissioncontroller@postschoolyearaspirants'); 
INSERT INTO allowed_pages_by_process VALUES (423, 'reportsadmissioncontroller@postsectionsnotesreport'); 
INSERT INTO allowed_pages_by_process VALUES (425, 'committecontroller@postaspirantsready');
INSERT INTO allowed_pages_by_process VALUES (446, 'bursarcontroller@postaspirantsreadypayment');
INSERT INTO allowed_pages_by_process VALUES (427, 'admissioncontroller@postcheckemailunique');