CREATE TABLE IF NOT EXISTS `applicant_guardian` (
  `serial_agd` INT NOT NULL AUTO_INCREMENT COMMENT 'Serial del pais para la dirección solo se aceptrá a ecuador',
  `serial_cou` INT(11) NOT NULL,
  `serial_ste` INT(11) NOT NULL,
  `serial_cnt` INT(11) NOT NULL,
  `serial_par` INT(11) NOT NULL,
  `first_name_agd` VARCHAR(150) NULL,
  `last_name_agd` VARCHAR(150) NULL,
  `id_agd` VARCHAR(150) NULL,
  `email_agd` VARCHAR(150) NULL,
  `phone_agd` VARCHAR(150) NULL,
  `main_street_agd` VARCHAR(200) NULL,
  `number_street_agd` VARCHAR(200) NULL,
  `secondary_street_agd` VARCHAR(200) NULL,
  `sector_agd` VARCHAR(200) NULL,
  `status_agd` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  `number_applicants_agd` INT(11) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_agd`),
  UNIQUE INDEX `serial_agd_UNIQUE` (`serial_agd` ASC),
  INDEX `fk_applicant_guardian_country_idx` (`serial_cou` ASC),
  INDEX `fk_applicant_guardian_state1_idx` (`serial_ste` ASC),
  INDEX `fk_applicant_guardian_counties1_idx` (`serial_cnt` ASC),
  INDEX `fk_applicant_guardian_parish1_idx` (`serial_par` ASC),
  CONSTRAINT `fk_applicant_guardian_country`
    FOREIGN KEY (`serial_cou`)
    REFERENCES `country` (`serial_cou`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_guardian_state1`
    FOREIGN KEY (`serial_ste`)
    REFERENCES `state` (`serial_ste`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_guardian_counties1`
    FOREIGN KEY (`serial_cnt`)
    REFERENCES `counties` (`serial_cnt`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_guardian_parish1`
    FOREIGN KEY (`serial_par`)
    REFERENCES `parish` (`serial_par`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `applicant_student` (
  `serial_ast` INT NOT NULL AUTO_INCREMENT,
  `serial_agd` INT NOT NULL,
  `serial_scy` INT(11) NOT NULL,
  `serial_set` INT(11) NOT NULL,
  `serial_lvl` INT(11) NOT NULL,
  `serial_cou_last_school_ast` INT(11) NOT NULL COMMENT 'Pais de procedencia',
  `first_name_ast` VARCHAR(150) NULL,
  `last_name_ast` VARCHAR(150) NULL,
  `id_ast` VARCHAR(150) NULL,
  `gender_ast` ENUM('MALE', 'FEMALE') NULL,
  `school_city_last_school_ast` VARCHAR(150) NULL,
  `last_school_ast` VARCHAR(150) NULL,
  `birthdate_ast` VARCHAR(150) NULL,
  `has_siblins_in_school_ast` ENUM('YES', 'NO') NULL,
  `parent_ex_student_ast` ENUM('YES', 'NO') NULL,
  `lives_with_ast` ENUM('YES', 'NO') NULL,
  `applicant_type_ast` ENUM('NEW', 'REENTRY', 'RETRY') NULL,
  `has_visited_school_ast` ENUM('YES', 'NO') NULL DEFAULT 'NO',
  `flag1_ast` ENUM('YES', 'NO') NULL,
  `flag2_ast` ENUM('YES', 'NO') NULL,
  `flag3_ast` ENUM('YES', 'NO') NULL,
  `status_applicant_ast` ENUM('ACCEPTED', 'REJECTED') NULL,
  `observation_ast` TEXT NULL,
  `relationship_ast` ENUM('MOTHER','FATHER','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NULL,
  `ip_address_ast` VARCHAR(45) NULL,
  `status_ast` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_ast`),
  INDEX `fk_applicant_student_school_year1_idx` (`serial_scy` ASC),
  INDEX `fk_applicant_student_level1_idx` (`serial_lvl` ASC),
  INDEX `fk_applicant_student_section1_idx` (`serial_set` ASC),
  INDEX `fk_applicant_student_country1_idx` (`serial_cou_last_school_ast` ASC),
  INDEX `fk_applicant_student_applicant_guardian1_idx` (`serial_agd` ASC),
  CONSTRAINT `fk_applicant_student_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_level1`
    FOREIGN KEY (`serial_lvl`)
    REFERENCES `level` (`serial_lvl`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_section1`
    FOREIGN KEY (`serial_set`)
    REFERENCES `section` (`serial_set`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_country1`
    FOREIGN KEY (`serial_cou_last_school_ast`)
    REFERENCES `country` (`serial_cou`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_applicant_guardian1`
    FOREIGN KEY (`serial_agd`)
    REFERENCES `applicant_guardian` (`serial_agd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `applicant_guardian`
DROP FOREIGN KEY `fk_applicant_guardian_counties1`,
DROP FOREIGN KEY `fk_applicant_guardian_country`,
DROP FOREIGN KEY `fk_applicant_guardian_parish1`,
DROP FOREIGN KEY `fk_applicant_guardian_state1`;
ALTER TABLE `applicant_guardian`
CHANGE COLUMN `serial_cou` `serial_cou` INT(11) NULL ,
CHANGE COLUMN `serial_ste` `serial_ste` INT(11) NULL ,
CHANGE COLUMN `serial_cnt` `serial_cnt` INT(11) NULL ,
CHANGE COLUMN `serial_par` `serial_par` INT(11) NULL ;
ALTER TABLE `applicant_guardian`
ADD CONSTRAINT `fk_applicant_guardian_counties1`
  FOREIGN KEY (`serial_cnt`)
  REFERENCES `counties` (`serial_cnt`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_guardian_country`
  FOREIGN KEY (`serial_cou`)
  REFERENCES `country` (`serial_cou`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_guardian_parish1`
  FOREIGN KEY (`serial_par`)
  REFERENCES `parish` (`serial_par`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_guardian_state1`
  FOREIGN KEY (`serial_ste`)
  REFERENCES `state` (`serial_ste`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- Para envío de mails desde el formulario (depende del colegio)
ALTER TABLE `employee_section`
CHANGE COLUMN `type_user` `type_user` ENUM('DIRECTOR', 'ASSISTANT', 'USER', 'TRANSPORTATION_COORDINATOR', 'SECRETARY', 'JUSTIFICATION_COORDINATOR', 'DESTINATION_MAIL_FATHER_ADMISSION', 'APPLICANT_STUDENT_APPROVER') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT 'TRANSPORTATION_COORDINATOR' COMMENT '' ;

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (303,419,9,2,1,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (304,419,9,15,1,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (305,419,9,3,2,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (306,419,9,5,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (307,419,9,6,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (308,419,9,7,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (309,419,9,8,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (310,419,9,9,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (311,419,9,10,3,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (312,419,9,16,4,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (313,419,9,17,4,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (314,419,9,18,4,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (315,419,9,11,5,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (316,419,9,12,5,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (317,419,9,13,5,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (318,419,9,4,6,'NO','ACTIVE','APPLICANT_STUDENT_APPROVER');

-- Para envío de mails internos (depende del colegio)
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (319,419,9,2,1,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (320,419,9,15,1,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (321,419,9,3,2,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (322,419,9,5,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (323,419,9,6,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (324,419,9,7,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (325,419,9,8,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (326,419,9,9,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (327,419,9,10,3,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (328,419,9,16,4,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (329,419,9,17,4,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (330,419,9,18,4,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (331,419,9,11,5,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (332,419,9,12,5,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');
insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (333,419,9,13,5,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

insert into employee_section(serial_emp_set, serial_emp, serial_scy, serial_lvl, serial_set, principal_set, status, type_user)
values (334,419,9,4,6,'NO','ACTIVE','DESTINATION_MAIL_FATHER_ADMISSION');

-- allowed pages para nuevo menú de aplicantes

insert into process(serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc, id_exclusion)
values (671,525,'Gestionar aplicaciones públicas','laravel/public/applicantInternal',0.50,'LOCKABLE', null);

insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getindex');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getsections');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getlevels');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@poststudents');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@posthasvisitedschool');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@postflagone');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@postflagtwo');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@postflagthree');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@poststatusapplicant');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@postobservation');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getinfo');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getform');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'applicantinternalcontroller@getaddinstitution');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'admissioncontroller@postcreatedataadmissionaplicant');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (671,'admissioncontroller@postaddinstitution');

--nuevo campo
ALTER TABLE school_setup
ADD COLUMN web_magazine_sst VARCHAR(100) NULL DEFAULT NULL AFTER web_view_sst;

ALTER TABLE applicant_guardian
ADD COLUMN city_residence_agd VARCHAR(200) NULL AFTER secondary_street_agd;

SET SQL_SAFE_UPDATES = 0;
UPDATE school_setup SET web_magazine_sst='https://www.tomasmoro.ec/revista-institucional/' WHERE serial_ste=19;

--tabla de rompimiento para registrar datos de padre y madre desde formulario
CREATE TABLE `applicant_guardian_student` (
  `serial_ags` int(11) NOT NULL AUTO_INCREMENT,
  `serial_agd` int(11) NOT NULL,
  `serial_ast` int(11) NOT NULL,
  `relationship_ast` enum('MOTHER','FATHER','OTHER') DEFAULT NULL,
  `status_ags` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ags`),
  KEY `fk_serial_agd` (`serial_agd`),
  KEY `fk_serial_ast` (`serial_ast`),
  CONSTRAINT `fk_serial_ast` FOREIGN KEY (`serial_ast`) REFERENCES `applicant_student` (`serial_ast`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_agd` FOREIGN KEY (`serial_agd`) REFERENCES `applicant_guardian` (`serial_agd`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

  ALTER TABLE `applicant_guardian`
ADD COLUMN `parent_ex_student` ENUM('YES', 'NO') NULL DEFAULT NULL AFTER `updated_at`;

ALTER TABLE `applicant_guardian`
CHANGE COLUMN `parent_ex_student` `parent_ex_student` ENUM('YES', 'NO') NULL DEFAULT NULL AFTER `sector_agd`;

ALTER TABLE `applicant_guardian_student`
ADD COLUMN `relationship_ast` ENUM('MOTHER', 'FATHER', 'OTHER') NULL DEFAULT NULL AFTER `serial_ast`;

ALTER TABLE `applicant_guardian_student`
ADD COLUMN `created_at` DATETIME NULL DEFAULT NULL AFTER `status_ags`,
ADD COLUMN `updated_at` DATETIME NULL DEFAULT NULL AFTER `created_at`;

ALTER TABLE `applicant_guardian`
ADD COLUMN `work_company_agd` VARCHAR(45) NULL DEFAULT NULL AFTER `parent_ex_student`;

-- Campos para links de información tank you page

ALTER TABLE `school_setup`
ADD COLUMN `web_payments_sst` VARCHAR(100) NULL DEFAULT NULL AFTER `web_magazine_sst`,
ADD COLUMN `web_contact_us_sst` VARCHAR(100) NULL DEFAULT NULL AFTER `web_payments_sst`,
ADD COLUMN `web_primary_sst` VARCHAR(100) NULL DEFAULT NULL AFTER `web_contact_us_sst`;

-- Campo que tiene link de redirección a thank you page según nivel
ALTER TABLE `level_group_school_year`
ADD COLUMN `form_thank_page` VARCHAR(100) NULL DEFAULT NULL AFTER `use_equivalence_pai_for_report`;