-- -----------------------------------------------------
-- Table `postulation_payment_concept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_payment_concept` (
  `serial_ppy` INT NOT NULL,
  `name_apy` VARCHAR(45) NULL,
  `status_apy` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_ppy`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `postulation_year_payment_concept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_year_payment_concept` (
  `serial_pyp` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `serial_ppy` INT NOT NULL,
  `serial_txn` INT(11) NOT NULL,
  `status_pyp` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_pyp`),
  INDEX `fk_aplicant_year_payment_concept_school_year1_idx` (`serial_scy` ASC),
  INDEX `fk_aplicant_year_payment_concept_applicant_payment_concept1_idx` (`serial_ppy` ASC),
  CONSTRAINT `fk_aplicant_year_payment_concept_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_aplicant_year_payment_concept_applicant_payment_concept1`
    FOREIGN KEY (`serial_ppy`)
    REFERENCES `postulation_payment_concept` (`serial_ppy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_form`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_form` (
  `serial_pfr` INT NOT NULL AUTO_INCREMENT,
  `serial_usr` INT(11) NOT NULL,
  `serial_scy` INT(11) NOT NULL,
  `start_date_pfr` DATETIME NULL,
  `end_date_pfr` DATETIME NULL,
  `status_pfr` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_pfr`),
  INDEX `fk_form_school_year1_idx` (`serial_scy` ASC),
  INDEX `fk_applicant_form_user1_idx` (`serial_usr` ASC),
  CONSTRAINT `fk_form_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_form_user1`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_answer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_answer` (
  `serial_pns` INT NOT NULL AUTO_INCREMENT,
  `name_pns` TEXT NOT NULL,
  `status_pns` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_pns`))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `postulation_question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_question` (
  `serial_ppq` INT NOT NULL AUTO_INCREMENT,
  `name_ppq` TEXT NULL,
  `type_ppq` ENUM('MULTIPLECHOISE', 'DIRECTRESPONSE', 'BOTH') NULL,
  `status_ppq` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_ppq`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_student` (
  `serial_pst` INT NOT NULL AUTO_INCREMENT,
  `id_pst` VARCHAR(45) NULL,
  `first_name_pst` VARCHAR(45) NULL,
  `last_name_pst` VARCHAR(45) NULL,
  `birthdate_std` DATETIME,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `status_pst` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_pst`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_form_question_answer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_form_question_answer` (
  `serial_pfq` INT NOT NULL AUTO_INCREMENT,
  `serial_pfr` INT NOT NULL,
  `serial_ppq` INT NOT NULL,
  `serial_pns` INT NULL,
  `status_afr` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_pfq`),
  INDEX `fk_applicant_form_qustion_answer_applicant_form1_idx` (`serial_pfr` ASC),
  INDEX `fk_applicant_form_qustion_answer_applicant_qustion1_idx` (`serial_ppq` ASC),
  INDEX `fk_applicant_form_qustion_answer_applicant_answer1_idx` (`serial_pns` ASC),
  CONSTRAINT `fk_applicant_form_qustion_answer_applicant_form1`
    FOREIGN KEY (`serial_pfr`)
    REFERENCES `postulation_form` (`serial_pfr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_form_qustion_answer_applicant_qustion1`
    FOREIGN KEY (`serial_ppq`)
    REFERENCES `postulation_question` (`serial_ppq`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_form_qustion_answer_applicant_answer1`
    FOREIGN KEY (`serial_pns`)
    REFERENCES `postulation_answer` (`serial_pns`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_current_deferred`
-- -----------------------------------------------------
CREATE TABLE `postulation_current_deferred` (
  `serial_pcr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_pst` int(11) DEFAULT NULL,
  `serial_fic` int(11) DEFAULT NULL,
  `card_cdd` varchar(50) DEFAULT NULL,
  `card_pcr` varchar(50) DEFAULT NULL,
  `code_pcr` varchar(50) DEFAULT NULL,
  `total_quotas_pcr` int(11) NOT NULL,
  `quota_amount_pcr` decimal(10,2) NOT NULL,
  `paid_quotas_pcr` int(11) DEFAULT NULL,
  `last_month_paid_quota_pcr` varchar(3) DEFAULT NULL,
  `owner_pcr` varchar(300) NOT NULL,
  `kinship_pcr` varchar(300) NOT NULL,
  `transaction_date_pcr` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `students_pcr` varchar(60) DEFAULT NULL,
  `credit_code_pcr` varchar(20) DEFAULT NULL,
  `type_pcr` enum('ACADEMIC','NON_ACADEMIC') NOT NULL,
  `status_pcr` enum('ACTIVE','INACTIVE','PAID') NOT NULL DEFAULT 'ACTIVE',
  `confirm_status_pcr` varchar(45) NOT NULL DEFAULT 'NEW',
  `serial_gpb` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `requestid` varchar(45) DEFAULT NULL,
  `expiration_date` varchar(100) DEFAULT NULL,
  `banck_pcr` varchar(200) DEFAULT NULL,
  `last_digits_card_pcr` varchar(45) DEFAULT NULL,
  `document_pcr` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`serial_pcr`)
  ) ENGINE = InnoDB;

ALTER TABLE `postulation_current_deferred` 
ADD INDEX `fk_pcr_fic_idx` (`serial_fic` ASC),
ADD INDEX `fk_pcr_gpb_idx` (`serial_gpb` ASC);
ALTER TABLE `postulation_current_deferred` 

ADD CONSTRAINT `fk_pcr_fic`
  FOREIGN KEY (`serial_fic`)
  REFERENCES `financial_institution_card` (`serial_fic`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_pcr_gpb`
  FOREIGN KEY (`serial_gpb`)
  REFERENCES `group_payment_button` (`serial_gpb`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `postulation_current_deferred` 
DROP FOREIGN KEY `fk_pcr_pst`;
ALTER TABLE `postulation_current_deferred` 
ADD INDEX `fk_pcr_pst_idx` (`serial_pst` ASC),
DROP INDEX `fk_pcr_pst_idx` ;
ALTER TABLE `postulation_current_deferred` 
ADD CONSTRAINT `fk_pcr_pst`
  FOREIGN KEY (`serial_pst`)
  REFERENCES `postulation_student` (`serial_pst`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


--------------------------------------
-- Table `postulation_student_form`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_student_form` (
  `serial_psf` INT NOT NULL AUTO_INCREMENT,
  `serial_pst` INT NULL,
  `serial_pyp` INT NULL,
  `sequential_psf` INT NULL,
  `number_invoice_psf` DOUBLE NULL,
  `total_before_taxes_psf` DOUBLE NULL,
  `net_total_psf` DOUBLE NULL,
  `total_discounts_psf` DOUBLE NULL,
  `total_psf` DOUBLE NULL,
  `emition_date_psf` DATETIME NULL,
  `access_key_psf` VARCHAR(200) NULL,
  `required_to_maintain_accounting_psf` ENUM('YES', 'NO') NULL,
  `identification_type_psf` ENUM('04', '05', '06') NULL,
  `business_name_psf` VARCHAR(500) NULL,
  `id_psf` VARCHAR(25) NULL,
  `email_paying_psf` VARCHAR(500) NULL,
  `address_psf` VARCHAR(500) NULL,
  `serial_usr_invoiced_psf` INT(11) NULL,
  `phone_paying_psf` VARCHAR(45) NULL,
  `status_psf` ENUM('ACTIVE', 'INACTIVE') NULL,
  `due_date_psf` DATETIME NULL,
  `serial_usr_update` INT(11) NULL,
  `xml_ttl` TEXT NULL,
  `status_invoice_psf` VARCHAR(45) NULL,
  `result_invoice_psf` VARCHAR(45) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `applicant_current_deferred_serial_pcr` INT NOT NULL,
  `applicant_form_serial_pfr` INT NOT NULL,
  PRIMARY KEY (`serial_psf`),
  INDEX `fk_applicant_student_form_applicant_student1_idx` (`serial_pst` ASC),
  INDEX `fk_applicant_student_form_user1_idx` (`serial_usr_invoiced_psf` ASC),
  INDEX `fk_applicant_student_form_user2_idx` (`serial_usr_update` ASC),
  INDEX `fk_applicant_student_form_aplicant_year_payment_concept1_idx` (`serial_pyp` ASC),
  INDEX `fk_applicant_student_form_applicant_current_deferred1_idx` (`applicant_current_deferred_serial_pcr` ASC),
  INDEX `fk_applicant_student_form_applicant_form1_idx` (`applicant_form_serial_pfr` ASC),
  CONSTRAINT `fk_applicant_student_form_applicant_student1`
    FOREIGN KEY (`serial_pst`)
    REFERENCES `postulation_student` (`serial_pst`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_form_user1`
    FOREIGN KEY (`serial_usr_invoiced_psf`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_form_user2`
    FOREIGN KEY (`serial_usr_update`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_form_aplicant_year_payment_concept1`
    FOREIGN KEY (`serial_pyp`)
    REFERENCES `postulation_year_payment_concept` (`serial_pyp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_form_applicant_current_deferred1`
    FOREIGN KEY (`applicant_current_deferred_serial_pcr`)
    REFERENCES `postulation_current_deferred` (`serial_pcr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_student_form_applicant_form1`
    FOREIGN KEY (`applicant_form_serial_pfr`)
    REFERENCES `postulation_form` (`serial_pfr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_form_question_answer_student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_form_question_answer_student` (
  `serial_pfs` INT NOT NULL AUTO_INCREMENT,
  `serial_pfq` INT NULL,
  `answer_pst` TEXT NULL,
  `status_pfs` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `applicant_student_form_serial_psf` INT NOT NULL,
  PRIMARY KEY (`serial_pfs`),
  INDEX `fk_applicant_form_qustion_answer_student_applicant_form_qus_idx` (`serial_pfq` ASC),
  INDEX `fk_applicant_form_qustion_answer_student_applicant_student__idx` (`applicant_student_form_serial_psf` ASC),
  CONSTRAINT `fk_applicant_form_qustion_answer_student_applicant_form_qusti1`
    FOREIGN KEY (`serial_pfq`)
    REFERENCES `postulation_form_question_answer` (`serial_pfq`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_applicant_form_qustion_answer_student_applicant_student_fo1`
    FOREIGN KEY (`applicant_student_form_serial_psf`)
    REFERENCES `postulation_student_form` (`serial_psf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `postulation_payment_detail`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `postulation_payment_detail` (
  `serial_ppd` INT NOT NULL,
  `applicant_payment_detailcol` VARCHAR(45) NULL,
  `applicant_student_form_serial_psf` INT NOT NULL,
  `payment_detail_serial_pdt` INT(11) NOT NULL,
  `payment_method_serial_pym` INT(11) NOT NULL,
  PRIMARY KEY (`serial_ppd`),
  INDEX `fk_applicant_payment_detail_applicant_student_form1_idx` (`applicant_student_form_serial_psf` ASC),
  INDEX `fk_postulation_payment_detail_payment_method1_idx` (`payment_method_serial_pym` ASC),
  CONSTRAINT `fk_applicant_payment_detail_applicant_student_form1`
    FOREIGN KEY (`applicant_student_form_serial_psf`)
    REFERENCES `postulation_student_form` (`serial_psf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_postulation_payment_detail_payment_method1`
    FOREIGN KEY (`payment_method_serial_pym`)
    REFERENCES `payment_method` (`serial_pym`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `postulation_student_form` 
DROP FOREIGN KEY `fk_applicant_student_form_applicant_current_deferred1`,
DROP FOREIGN KEY `fk_applicant_student_form_applicant_form1`;
ALTER TABLE `postulation_student_form` 
CHANGE COLUMN `applicant_current_deferred_serial_pcr` `applicant_current_deferred_serial_pcr` INT(11) NULL ,
CHANGE COLUMN `applicant_form_serial_pfr` `applicant_form_serial_pfr` INT(11) NULL ;
ALTER TABLE `postulation_student_form` 
ADD CONSTRAINT `fk_applicant_student_form_applicant_current_deferred1`
  FOREIGN KEY (`applicant_current_deferred_serial_pcr`)
  REFERENCES `postulation_current_deferred` (`serial_pcr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_student_form_applicant_form1`
  FOREIGN KEY (`applicant_form_serial_pfr`)
  REFERENCES `postulation_form` (`serial_pfr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `postulation_student_form` 
DROP FOREIGN KEY `fk_applicant_student_form_applicant_current_deferred1`,
DROP FOREIGN KEY `fk_applicant_student_form_applicant_form1`;
ALTER TABLE `postulation_student_form` 
CHANGE COLUMN `applicant_current_deferred_serial_pcr` `serial_pcr` INT(11) NULL DEFAULT NULL AFTER `serial_pyp`,
CHANGE COLUMN `applicant_form_serial_pfr` `serial_pfr` INT(11) NULL DEFAULT NULL AFTER `serial_pcr`;
ALTER TABLE `postulation_student_form` 
ADD CONSTRAINT `fk_applicant_student_form_applicant_current_deferred1`
  FOREIGN KEY (`serial_pcr`)
  REFERENCES `postulation_current_deferred` (`serial_pcr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_student_form_applicant_form1`
  FOREIGN KEY (`serial_pfr`)
  REFERENCES `postulation_form` (`serial_pfr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  ALTER TABLE `postulation_student_form` 
ADD COLUMN `postulation_date_psf` DATETIME NULL AFTER `serial_pfr`;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `status_application_psf` ENUM('ACCEPTED', 'REJECTED') NULL AFTER `postulation_date_psf`,
ADD COLUMN `commet_psf` TEXT NULL AFTER `status_application_psf`;

ALTER TABLE `postulation_student` 
ADD COLUMN `email_pst` VARCHAR(200) NULL AFTER `birthdate_std`;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `serial_usr_status_aplication_psf` INT NULL AFTER `postulation_date_psf`,
ADD COLUMN `rmail_status_aplication_psf` TEXT NULL AFTER `serial_usr_status_aplication_psf`,
ADD INDEX `fk_applicant_student_form_user3_idx` (`serial_usr_status_aplication_psf` ASC);
ALTER TABLE `postulation_student_form` 
ADD CONSTRAINT `fk_applicant_student_form_user3`
  FOREIGN KEY (`serial_usr_status_aplication_psf`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `date_status_aplicatioon_psf` DATETIME NULL AFTER `rmail_status_aplication_psf`;

ALTER TABLE `postulation_year_payment_concept` 
ADD COLUMN `value_pyp` DOUBLE NULL AFTER `serial_txn`;

ALTER TABLE `group_payment_button` 
CHANGE COLUMN `type_gpb` `type_gpb` ENUM('currentDefered', 'current', 'register', 'CURRENTPAYMENTS', 'FEEDINGPAYMENTS', 'POSTULATION') NULL DEFAULT 'currentDefered' ;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `taxes_psf` VARCHAR(45) NULL AFTER `number_invoice_psf`;

ALTER TABLE `postulation_current_deferred` 
ADD COLUMN `intertest_amount_pcr` DOUBLE NULL AFTER `paid_quotas_pcr`,
ADD COLUMN `installment_amount_pcr` DOUBLE NULL AFTER `intertest_amount_pcr`,
ADD COLUMN `total_amount_pcr` DOUBLE NULL AFTER `installment_amount_pcr`;

ALTER TABLE `postulation_student_form`
CHANGE COLUMN `status_invoice_psf` `status_invoice_psf` SET('PAID', 'SETTED_SAFI', 'VOID', 'INVOICED_SAFI', 'PAID_SAFI', 'PARTIALLY') NULL DEFAULT NULL ;


ALTER TABLE `postulation_payment_detail`
DROP FOREIGN KEY `fk_applicant_payment_detail_applicant_student_form1`,
DROP FOREIGN KEY `fk_postulation_payment_detail_payment_method1`;
ALTER TABLE `postulation_payment_detail`
DROP COLUMN `payment_detail_serial_pdt`,
DROP COLUMN `applicant_payment_detailcol`,
CHANGE COLUMN `applicant_student_form_serial_psf` `serial_psf` INT(11) NULL ,
CHANGE COLUMN `payment_method_serial_pym` `serial_pym` INT(11) NULL ;
ALTER TABLE `postulation_payment_detail`
ADD CONSTRAINT `fk_applicant_payment_detail_applicant_student_form1`
  FOREIGN KEY (`serial_psf`)
  REFERENCES `postulation_student_form` (`serial_psf`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_postulation_payment_detail_payment_method1`
  FOREIGN KEY (`serial_pym`)
  REFERENCES `payment_method` (`serial_pym`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `postulation_payment_detail`
ADD COLUMN `description_ppd` VARCHAR(250) NULL AFTER `serial_pym`,
ADD COLUMN `city_ppd` VARCHAR(45) NULL AFTER `description_ppd`,
ADD COLUMN `amount_ppd` DOUBLE NULL AFTER `city_ppd`,
ADD COLUMN `kinship_ppd` VARCHAR(45) NULL AFTER `amount_ppd`,
ADD COLUMN `owner_ppd` VARCHAR(45) NULL AFTER `kinship_ppd`,
ADD COLUMN `id_owner_ppd` VARCHAR(45) NULL AFTER `owner_ppd`,
ADD COLUMN `date_ppd` DATETIME NULL AFTER `id_owner_ppd`,
ADD COLUMN `payment_date_ppd` DATE NULL AFTER `date_ppd`,
ADD COLUMN `status_ppd` ENUM('ACTIVE', 'INACTIVE') NULL AFTER `payment_date_ppd`,
ADD COLUMN `created_at` DATETIME NULL AFTER `status_ppd`,
ADD COLUMN `updated_at` DATETIME NULL AFTER `created_at`;

ALTER TABLE `postulation_student_form`
CHANGE COLUMN `total_before_taxes_psf` `total_before_taxes_psf` DOUBLE(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `net_total_psf` `net_total_psf` DOUBLE(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `total_discounts_psf` `total_discounts_psf` DOUBLE(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `total_psf` `total_psf` DOUBLE(10,2) NULL DEFAULT NULL ;

ALTER TABLE `postulation_current_deferred`
CHANGE COLUMN `intertest_amount_pcr` `intertest_amount_pcr` DOUBLE(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `installment_amount_pcr` `installment_amount_pcr` DOUBLE(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `total_amount_pcr` `total_amount_pcr` DOUBLE(10,2) NULL DEFAULT NULL ;

ALTER TABLE `postulation_payment_detail`
CHANGE COLUMN `serial_ppd` `serial_ppd` INT(11) NOT NULL AUTO_INCREMENT ;

ALTER TABLE `postulation_student_form`
ADD COLUMN `code_pearson_psf` VARCHAR(100) NULL AFTER `date_status_aplicatioon_psf`;

ALTER TABLE `postulation_student_form`
ADD COLUMN `date_send_pearson_notification_test_pst` DATETIME NULL AFTER `result_invoice_psf`,
ADD COLUMN `serial_usr_pearson_notification_test_pst` INT NULL AFTER `date_send_pearson_notification_test_pst`,
ADD COLUMN `text_email_notification_test_pst` TEXT NULL AFTER `serial_usr_pearson_notification_test_pst`,
ADD INDEX `fk_applicant_student_form_user4_idx` (`serial_usr_pearson_notification_test_pst` ASC);
ALTER TABLE `postulation_student_form`
ADD CONSTRAINT `fk_applicant_student_form_user4`
  FOREIGN KEY (`serial_usr_pearson_notification_test_pst`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `postulation_question`
CHANGE COLUMN `type_ppq` `type_ppq` ENUM('MULTIPLECHOISE', 'DIRECTRESPONSE', 'BOTH', 'SELECT', 'RADIO', 'CHECKBOX', 'DATE', 'EMAIL') NULL DEFAULT NULL ;

ALTER TABLE `postulation_student`
ADD COLUMN `address_pst` VARCHAR(200) NULL AFTER `birthdate_std`;

ALTER TABLE `postulation_student_form`
DROP FOREIGN KEY `fk_applicant_student_form_applicant_current_deferred1`;

ALTER TABLE `postulation_answer`
CHANGE COLUMN `status_pns` `status_pns` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_form`
CHANGE COLUMN `status_pfr` `status_pfr` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_form_question_answer`
CHANGE COLUMN `status_afr` `status_afr` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_form_question_answer_student`
CHANGE COLUMN `status_pfs` `status_pfs` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_payment_concept`
CHANGE COLUMN `status_apy` `status_apy` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_payment_detail`
CHANGE COLUMN `status_ppd` `status_ppd` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_question`
CHANGE COLUMN `status_ppq` `status_ppq` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_student`
CHANGE COLUMN `status_pst` `status_pst` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_student_form`
CHANGE COLUMN `status_psf` `status_psf` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;

ALTER TABLE `postulation_year_payment_concept`
CHANGE COLUMN `status_pyp` `status_pyp` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;
ALTER TABLE `text_email` 
CHANGE COLUMN `alias_txn` `alias_txn` ENUM('DEBTOR', 'PAYMENT_CARD', 'REGISTER_PAYMENT', 'NOTIFICATION', 'POSTULATION') NULL DEFAULT NULL ;

ALTER TABLE `text_email` 
ADD COLUMN `vars_email_txn` VARCHAR(45) NULL AFTER `notification_push_txn`;

-- UPDATE `text_email` SET `vars_email_txn`='_GEERO_NOMBRE_ESTUDIANTE_,_IMAGEN_CONSEJOS_PARA_RENDIR_TU_EXAMEN_,_FECHA_EXAMEN_,_CODIGO_PEARSON_,_CEDULA_ ,_IMAGEN_COMISION_FULBRIGHT_' WHERE `serial_txn`='5';

ALTER TABLE `postulation_student_form` 
ADD COLUMN `serial_usr_register_result_psf` INT NULL AFTER `text_email_notification_test_pst`,
ADD COLUMN `register_result_psf` ENUM('ACCEPTED', 'REJECTED') NULL AFTER `serial_usr_register_result_psf`,
ADD COLUMN `comment_register_result_pst` TEXT NULL AFTER `register_result_psf`,
ADD COLUMN `email_register_result_psf` TEXT NULL AFTER `comment_register_result_pst`;

ALTER TABLE `postulation_student_form` 
ADD INDEX `fk_applicant_student_form_user5_idx` (`serial_usr_register_result_psf` ASC);
ALTER TABLE `postulation_student_form` 
ADD CONSTRAINT `fk_applicant_student_form_user5`
  FOREIGN KEY (`serial_usr_register_result_psf`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

UPDATE `text_email` SET `name_txn`='ETP Fulbright matrícula' WHERE `serial_txn`='4';
UPDATE `text_email` SET `name_txn`='ETP Fulbright exámen de ublicación' WHERE `serial_txn`='5';

insert into allowed_pages_by_process values(867,'inscriptionreportcontroller@getindex');
insert into allowed_pages_by_process values(867,'inscriptionreportcontroller@getinvoicemonth');
insert into allowed_pages_by_process values(867,'inscriptionreportcontroller@getpaymentconceptgroups');
insert into allowed_pages_by_process values(867,'inscriptionreportcontroller@postcompletereport');
insert into allowed_pages_by_process values(867,'inscriptionreportcontroller@poststudentpaid');
insert into allowed_pages_by_process values(867,'saficontroller@postinvoicedata');
insert into allowed_pages_by_process values(867,'saficontroller@postsaveinvoicedata');
insert into allowed_pages_by_process values(867,'invoicetmcontroller@postinvoicedata');

ALTER TABLE `postulation_student_form` 
ADD COLUMN `grade_register_result_psf` VARCHAR(45) NULL AFTER `register_result_psf`;


ALTER TABLE `postulation_student_form` 
CHANGE COLUMN `email_register_result_psf` `text_email_register_result_psf` TEXT NULL DEFAULT NULL ;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `date_register_redult_psf` DATETIME NULL AFTER `text_email_register_result_psf`;


ALTER TABLE `postulation_form` 
ADD COLUMN `text_email_accepted_pfr` TEXT NULL AFTER `end_date_pfr`,
ADD COLUMN `text_email_rejected_pfr` TEXT NULL AFTER `text_email_accepted_pfr`;
ALTER TABLE `postulation_student_form` 
CHANGE COLUMN `grade_register_result_psf` `grade_register_result_psf` VARCHAR(15) NULL DEFAULT NULL ;

ALTER TABLE `postulation_form` 
ADD COLUMN `text_email_paids_pfr` TEXT NULL AFTER `end_date_pfr`;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `text_email_paids_psf` TEXT NULL AFTER `date_status_aplicatioon_psf`;

ALTER TABLE `postulation_student_form` 
ADD COLUMN `serial_set` INT NULL AFTER `date_register_redult_psf`,
ADD COLUMN `serial_lvl` INT NULL AFTER `serial_set`,
ADD INDEX `fk_applicant_student_form_level_idx` (`serial_lvl` ASC),
ADD INDEX `fk_applicant_student_form_section_idx` (`serial_set` ASC);
ALTER TABLE `postulation_student_form` 
ADD CONSTRAINT `fk_applicant_student_form_level`
  FOREIGN KEY (`serial_lvl`)
  REFERENCES `level` (`serial_lvl`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_applicant_student_form_section`
  FOREIGN KEY (`serial_set`)
  REFERENCES `section` (`serial_set`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

INSERT INTO `demo_fulbright_preubas`.`process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`) VALUES ('870', '199', 'Administrar postulaciones', 'laravel/public/postulantAdmin', '1');

insert into allowed_pages_by_process values(870,'postulantadmincontroller@getindex');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postdates');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@poststudents');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@getinfo');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@getLoademailset');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@getaddinstitution');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@getform');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postsaveform');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postsendemail');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postexcelreport');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postLoadsendemail');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postsendemailnotification');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@getregisterresult');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postregisterresult');
insert into allowed_pages_by_process values(870,'postulantadmincontroller@postsavecodeperason');


update postulation_student_form set text_email_paids_pfr ='<p style="text-align:justify">Estimad _GEERO_NOMBRE_ESTUDIANTE_ ,</p>
 
 <p style="text-align:justify">_IMAGEN_REGISTRO_EXITOSO_</p>
 
 <p style="text-align:justify">Su registro al Examen de Ubicaci&oacute;n ha sido <strong>exitoso</strong>.</p>
 
 <p style="text-align:justify">Recibir&aacute; su link de acceso al examen, usuario y contrase&ntilde;a junto con los pasos a seguir <u>24hrs antes de la fecha seleccionada</u>.</p>
 
 <p style="text-align:justify"><u>Los resultados del ex&aacute;men ser&aacute;n enviados a su correo electr&oacute;nico a partir del 16 de febrero.</u></p>
 
 <p style="text-align:justify">&nbsp;</p>
 
 <p>Le recordamos que este examen est&aacute; dirigido <u>&uacute;nicamente</u> a personas <u>mayores de edad (+18 a&ntilde;os)</u><strong> </strong>que deseen ingresar a nuestro programa de ingl&eacute;s <strong>Ciclo III del 10 de abril al 16 de junio de 2023 o curso de preparaci&oacute;n TOEFL en febrero 2023.</strong></p>
 
 <p>&nbsp;</p>
 ', text_email_accepted_pfr='<p style="text-align:justify">Estimad _GEERO_NOMBRE_ESTUDIANTE_ ,</p>
 
 <p style="text-align:justify">&nbsp;</p>
 
 <p style="text-align:justify">El resultado de tu examen de ubicaci&oacute;n es _CALIFICACION_/100. Para matricularte en nivel _NIVEL_APROBADO_ responde este correo adjuntando tu C&eacute;dula de Identidad <em>(imagen legible de ambos lados)</em> y completando el siguiente formulario:</p>
 
 <p style="text-align:justify">_LINK_MATRICULA_, con su n&uacute;emro d ec&eacute;dula como usuario y contrase&ntilde;a&nbsp;</p>
 
 <p style="text-align:justify">&nbsp;</p>
 
 <p style="text-align:justify"><strong>Costo de la matr&iacute;cula (m&aacute;s material digital): </strong>_COSTO_</p>
 
 <p style="text-align:justify">Por favor toma en cuenta las siguientes indicaciones y recomendaciones para que no tengas inconvenientes durante el proceso de matr&iacute;cula.&nbsp;</p>
 
 <p style="text-align:justify">1. Aseg&uacute;rate de tener a <strong>info@fulbrightetp.org</strong> en tus contactos para que siempre recibas nuestros correos y no sean enviados a SPAM o correo no deseado.&nbsp;&nbsp;</p>
 
 <p style="text-align:justify">2. Recibir&aacute;s el link de pago<u> a partir del 6 de diciembre.</u> Desde ese momento, tienes <u>72 horas m&aacute;ximo para realizar el pago</u>. Despu&eacute;s de ese periodo de tiempo, el link caducar&aacute; y tendr&aacute;s que hacer todo el proceso nuevamente.&nbsp;</p>
 
 <p style="text-align:justify">3. Debes realizar tu pago a trav&eacute;s del link de pago <u>&uacute;nicamente</u>. Puedes utilizar tu tarjeta de d&eacute;bito o cr&eacute;dito. No es necesario que nos env&iacute;es el comprobante de pago.</p>
 
 <p style="text-align:justify">4. Una vez confirmado el pago, recibir&aacute;s un correo de confirmaci&oacute;n de matr&iacute;cula. El ciclo inicia el 16 de enero de 2023.&nbsp;</p>
 
 <p style="text-align:justify"><u>Recuerda que para tomar el curso de preparaci&oacute;n TOEFL debes tener un resultado m&iacute;nimo de 66/100 en el examen de ubicaci&oacute;n. Te invitamos a que estudies ingl&eacute;s con Fulbright para que puedas mejorar y acceder al curso TOEFL en el futuro. </u></p>
 
 <p>&nbsp;</p>
 ', text_email_rejected_pfr='<p style="text-align:justify">Estimado/a aspirante,</p>
 
 <p style="text-align:justify">Est&aacute;s recibiendo este correo ya que te registraste para el examen de ubicaci&oacute;n pero no completaste el proceso.&nbsp;</p>
 
 <p style="text-align:justify">Tu resultado es alguna de estas opciones: expirado, abandonado o incompleto, ya que no tomaste la prueba de ubicaci&oacute;n.&nbsp;</p>
 
 <p style="text-align:justify">Puedes registrarte en el nivel &quot;Introductory&quot; que es nuestro primer nivel, para eso, responde a este mail para poder ayudarte con el proceso de matr&iacute;cula. O puede inscribirse nuevamente en nuestra siguiente convocatoria.</p>
 ';