CREATE TABLE IF NOT EXISTS `reason_economic_help` (
  `serial_reh` INT NOT NULL AUTO_INCREMENT,
  `name_reh` VARCHAR(100) NULL,
  `status_reh` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_reh`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `economic_help` (
  `serial_ech` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT NOT NULL,
  `serial_reh` INT NOT NULL,
  `previous_ech` ENUM('YES', 'NO') NULL,
  `time_previous_ech` VARCHAR(100) NULL,
  `have_university_sons_ech` ENUM('YES', 'NO') NULL,
  `university_sons_ech` VARCHAR(100) NULL,
  `university_assistance_sons_ech` VARCHAR(100) NULL,
  `other_person_aport_ech` ENUM('YES', 'NO') NULL,
  `travel_last_year_ech` VARCHAR(100) NULL,
  `family_disability_ech` ENUM('YES', 'NO') NULL,
  `relationship_ech` VARCHAR(100) NULL,
  `treatment_cost_family_ech` VARCHAR(100) NULL,
  `status_ech` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ech`),
  INDEX `fk_economic_help_school_year_idx` (`serial_scy` ASC),
  INDEX `fk_economic_help_reason_scholarship1_idx` (`serial_reh` ASC),
  CONSTRAINT `fk_economic_help_school_year`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_economic_help_reason_scholarship1`
    FOREIGN KEY (`serial_reh`)
    REFERENCES `reason_economic_help` (`serial_reh`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `student_economic_help` (
  `serial_seh` INT NOT NULL AUTO_INCREMENT,
  `serial_std` INT NOT NULL,
  `serial_ech` INT NOT NULL,
  `status_seh` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_seh`),
  INDEX `fk_student_economic_help_student1_idx` (`serial_std` ASC),
  INDEX `fk_student_economic_help_economic_help1_idx` (`serial_ech` ASC),
  CONSTRAINT `fk_student_economic_help_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student_economic_help_economic_help1`
    FOREIGN KEY (`serial_ech`)
    REFERENCES `economic_help` (`serial_ech`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `guardian_economic_help` (
  `serial_geh` INT NOT NULL AUTO_INCREMENT,
  `serial_grd` INT NOT NULL,
  `serial_ech` INT NOT NULL,
  `salry_geh` VARCHAR(100) NULL,
  `comissions_geh` VARCHAR(100) NULL,
  `other_income_geh` VARCHAR(100) NULL,
  `total_income_geh` VARCHAR(100) NULL,
  `basic_expenses_geh` VARCHAR(100) NULL,
  `monthly_rent_geh` VARCHAR(100) NULL,
  `monthly_fee_mortgage_geh` VARCHAR(100) NULL,
  `monthly_fee_vehicle_geh` VARCHAR(100) NULL,
  `school_pension_geh` VARCHAR(100) NULL,
  `university_payment_geh` VARCHAR(100) NULL,
  `other_expenses_geh` VARCHAR(100) NULL,
  `total_expenses_geh` VARCHAR(100) NULL,
  `total_geh` VARCHAR(100) NULL,
  `value_house_geh` VARCHAR(100) NULL,
  `value_deparment_geh` VARCHAR(100) NULL,
  `grand_land_geh` VARCHAR(100) NULL,
  `ubication_property_geh` VARCHAR(100) NULL,
  `porperty_mortgaged_geh` VARCHAR(100) NULL,
  `brand_vehicle_geh` VARCHAR(100) NULL,
  `plate_vehicle_geh` VARCHAR(100) NULL,
  `value_vehicle_geh` VARCHAR(100) NULL,
  `other_vechicle_geh` VARCHAR(100) NULL,
  `savings_account_geh` ENUM('YES', 'NO') NULL,
  `entity_savings_account_geh` VARCHAR(100) NULL,
  `current_account_geh` ENUM('YES', 'NO') NULL,
  `entity_current_account_geh` VARCHAR(100) NULL,
  `credit_card_one_geh` VARCHAR(100) NULL,
  `credit_card_two_geh` VARCHAR(100) NULL,
  `job_certificate_geh` ENUM('YES', 'NO') NULL,
  `sri_certificate_geh` ENUM('YES', 'NO') NULL,
  `rpd_certificate_geh` ENUM('YES', 'NO') NULL,
  `predial_tax_geh` ENUM('YES', 'NO') NULL,
  `contract_rent_geh` ENUM('YES', 'NO') NULL,
  `vehicle_registration_geh` ENUM('YES', 'NO') NULL,
  `bank_certificate_geh` ENUM('YES', 'NO') NULL,
  `invoice_basic_service_geh` ENUM('YES', 'NO') NULL,
  `divorce_certificate_geh` ENUM('YES', 'NO') NULL,
  `juvenil_court_certificate_geh` ENUM('YES', 'NO') NULL,
  `proof_expenses_health_geh` ENUM('YES', 'NO') NULL,
  `status_geh` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_geh`),
  INDEX `fk_guardian_economic_help_guardian1_idx` (`serial_grd` ASC),
  INDEX `fk_guardian_economic_help_economic_help1_idx` (`serial_ech` ASC),
  CONSTRAINT `fk_guardian_economic_help_guardian1`
    FOREIGN KEY (`serial_grd`)
    REFERENCES `guardian` (`serial_grd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_guardian_economic_help_economic_help1`
    FOREIGN KEY (`serial_ech`)
    REFERENCES `economic_help` (`serial_ech`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `registration_process_economic_help` (
  `serial_rpe` INT NOT NULL AUTO_INCREMENT,
  `name_page_rpe` VARCHAR(100) NULL,
  `link_rpe` VARCHAR(100) NULL,
  `name_icon_no_selected` VARCHAR(100) NULL,
  `name_icon_selected` VARCHAR(100) NULL,
  `order_rpe` VARCHAR(100) NULL,
  `status_rpe` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rpe`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `registration_process_economic_help_user` (
  `serial_rpeu` INT NOT NULL AUTO_INCREMENT,
  `serial_usr` INT NOT NULL,
  `serial_scy` INT NOT NULL,
  `serial_rpe` INT NOT NULL,
  `date_rpeu` DATETIME NULL,
  `status_rpeu` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rpeu`),
  INDEX `fk_registration_process_economic_help_user_user1_idx` (`serial_usr` ASC),
  INDEX `fk_registration_process_economic_help_user_registration_pro_idx` (`serial_rpe` ASC),
  INDEX `fk_registration_process_economic_help_user_school_year1_idx` (`serial_scy` ASC),
  CONSTRAINT `fk_registration_process_economic_help_user_user1`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_registration_process_economic_help_user_registration_proce1`
    FOREIGN KEY (`serial_rpe`)
    REFERENCES `registration_process_economic_help` (`serial_rpe`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_registration_process_economic_help_user_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `school_year`
CHANGE COLUMN `activities_scy` `activities_scy` SET('NOTAPPLY', 'INSCRIPTION', 'EXTRACURRICULAR', 'CRON', 'ENABLE_SCALE', 'QLT_SUBJ_LAST_POS_MIN', 'NOT_SHOW_TOTAL_AVERAGES', 'APPLY_DCM_IN_ASIGN_PAYMENT_MODE', 'HAS_TRAVEL_WIZARD', 'CHECK_PAYMENTS_IN_TRAVEL', 'CHECK_PAYMENTS_IN_GENERAL_REPORT', 'USE_INFORMATIVE_DEBTS', 'RESET_TRAVEL_FORMAT_UPDATE_ADDRESS', 'CHECKLIST', 'HIDE_SCLAE_POINTS', 'CHECK_PAYMENTS_IN_REGISTER_TO_EXTRACURRICULAR', 'REGISTER_OBSERVATIONS_BY_SUBJRECT_BY_SUBPERIOD', 'IN_THIRD_PARCIAL_SET_MAX_GRADE', 'SHOW_STUDENT_PHOTO_ON_REPORT', 'CHECK_SUMMATIVE_IN_GRADE', 'APPLICATION_FORM', 'ECONOMIC_HELP') NULL DEFAULT 'NOTAPPLY' COMMENT 'status par inscripciones' ;

UPDATE `school_year` SET `activities_scy`='NOTAPPLY,APPLICATION_FORM,ECONOMIC_HELP' WHERE `serial_scy`='10';

ALTER TABLE `school_year`
ADD COLUMN `email_economic_help_scy` TEXT NULL AFTER `setup_skills`;

insert into process(serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc, id_exclusion)
values (728,null,'Solicitud de beca','laravel/public/economicHelp',288.00,'LOCKABLE', null);

insert into process(serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc, id_exclusion)
values (730,728,'Llenar formulario','laravel/public/economicHelp',1.00,'LOCKABLE', null);

insert into process(serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc, id_exclusion)
values (737,728,'Reporte general','laravel/public/economicHelpReport',2.00,'LOCKABLE', null);

insert into allowed_pages_by_process(serial_prc, url_dsc)
values (730,'economichelpcontroller@getindex');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (730,'economichelpcontroller@getform');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (730,'economichelpcontroller@postsearchstudent');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (730,'economichelpcontroller@postsaveform');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (730,'economichelpcontroller@postsavereasonform');

insert into allowed_pages_by_process(serial_prc, url_dsc)
values (737,'economichelpreportcontroller@getindex');
insert into allowed_pages_by_process(serial_prc, url_dsc)
values (737,'economichelpreportcontroller@postEconomicHelpReport');


insert into reason_economic_help(name_reh)
values ('Pérdida de trabajo');
insert into reason_economic_help(name_reh)
values ('Calamidad doméstica');
insert into reason_economic_help(name_reh)
values ('Enfermedad crónica/catastrófica');
insert into reason_economic_help(name_reh)
values ('Discapacidad física temporalmente o permanente');
insert into reason_economic_help(name_reh)
values ('Otro');


insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (1,'INFORMACIÓN DEL/LOS ESTUDIANTES',null,null,null,1);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (2,'MOTIVOS DE LA SOLICITUD',null,null,null,2);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (3,'INFORMACIÓN FAMILIAR',null,null,null,3);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (4,'INGRESOS MENSUALES',null,null,null,4);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (5,'PROPIEDADES',null,null,null,5);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (6,'REFERENCIAS BANCARIAS',null,null,null,6);

insert into registration_process_economic_help(serial_rpe, name_page_rpe, link_rpe, name_icon_no_selected, name_icon_selected, order_rpe)
values (7,'DOCUMENTACIÓN',null,null,null,7);