CREATE TABLE `transfer` (
  `serial_trn` INT NOT NULL AUTO_INCREMENT,
  `reference_trn` VARCHAR(200) NULL,
  `img_receipt_trn` VARCHAR(300) NULL,
  `value_trn` DECIMAL NULL,
  `status_transfer_trn` ENUM('PENDING', 'REJECTED', 'APPROVED') NULL,
  `status_trn` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_trn`));


CREATE TABLE `student_transfer` (
  `serial_stt` INT NOT NULL AUTO_INCREMENT,
  `serial_trn` INT NULL,
  `serial_std` INT NULL,
  `scp_stt` VARCHAR(45) NULL,
  `status_stt` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_stt`),
  INDEX `fk_serial_trf_idx` (`serial_trn` ASC),
  INDEX `fk_serial_stt_std_idx` (`serial_std` ASC),
  CONSTRAINT `fk_serial_stt_trn`
    FOREIGN KEY (`serial_trn`)
    REFERENCES `transfer` (`serial_trn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_stt_std`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `demoboton`.`student_transfer` 
CHANGE COLUMN `scp_stt` `cps_stt` VARCHAR(45) NULL DEFAULT NULL ;

ALTER TABLE `transfer` 
ADD COLUMN `checked_by_parent_trn` ENUM('YES', 'NO') NULL AFTER `value_trn`;

ALTER TABLE `transfer` 
ADD COLUMN `serial_usr` INT NULL AFTER `serial_trn`,
ADD COLUMN `created_at` DATETIME NULL AFTER `status_trn`,
ADD COLUMN `updated_at` DATETIME NULL AFTER `created_at`,
ADD INDEX `fk_trn_usr_idx` (`serial_usr` ASC);
ALTER TABLE `transfer` 
ADD CONSTRAINT `fk_trn_usr`
  FOREIGN KEY (`serial_usr`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  ALTER TABLE `student_transfer` 
 CHANGE COLUMN `scp_stt` `cps_stt` VARCHAR(45) NULL DEFAULT NULL ,
ADD COLUMN `created_at` DATETIME NULL AFTER `status_stt`,
ADD COLUMN `updated_at` DATETIME NULL AFTER `created_at`;


ALTER TABLE `transfer` 
ADD COLUMN `serial_scy` INT NULL AFTER `serial_usr`,
ADD INDEX `fk_trn_scy_idx` (`serial_scy` ASC);
ALTER TABLE `transfer` 
ADD CONSTRAINT `fk_trn_scy`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  ALTER TABLE `student_transfer` 
ADD COLUMN `value_stt` DECIMAL NULL AFTER `cps_stt`;

ALTER TABLE `transfer` 
ADD COLUMN `comment_trn` TEXT NULL AFTER `serial_scy`;

ALTER TABLE `transfer` 
ADD COLUMN `serial_usr_update` INT NULL AFTER `serial_scy`;
ALTER TABLE `transfer` 
ADD CONSTRAINT `fk_trn_usr_update`
  FOREIGN KEY (`serial_usr`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;



ALTER TABLE `authority_email` 
CHANGE COLUMN `type_auth` `type_auth` SET('BURSAR', 'ADMITION', 'ADDTRAVEL', 'ADDFOOD', 'REMOVETRAVEL', 'REMOVEFOOD', 'CHECK_TRANSFERS') NULL DEFAULT NULL ;

INSERT INTO `authority_email` (`serial_scy`, `email_ath`, `description_ath`, `type_auth`, `status_ath`) VALUES ('11', 'flbenalcazar@rutatec.com', 'Email para verificar transferencias', 'CHECK_TRANSFERS', 'ACTIVE');

ALTER TABLE `transfer` 
ADD COLUMN `date_trnasfer_trn` DATE NULL AFTER `value_trn`;

ALTER TABLE `student_year` 
CHANGE COLUMN `bank_sty` `bank_sty` ENUM('PICHINCHA', 'PACIFICO', 'OTHER') NULL DEFAULT NULL ;


ALTER TABLE `payment_method` 
ADD COLUMN `url_img_pym` VARCHAR(200) NULL AFTER `acount_banck_pym`;
ALTER TABLE `school_setup` 
ADD COLUMN `contract_sst` VARCHAR(300) NULL AFTER `time_unit`;

update school_setup set contract_sst='https://learnbox.tomasmoro.k12.ec/learnbox/files/Contrato_SERVICIOS_EDUCATIVOS_2021-2022_final.pdf';

ALTER TABLE `current_deferred_debts` 
ADD COLUMN `serial_scy` INT NULL AFTER `serial_fic`,
ADD INDEX `fk_cdd_scy_idx` (`serial_scy` ASC);
ALTER TABLE `current_deferred_debts` 
ADD CONSTRAINT `fk_cdd_scy`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `invoice` 
ADD COLUMN `sended_email_phy` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `is_paid_phy`;


INSERT INTO `democapacitacion`.`process` (`prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('156', 'Revisión de transferencias', 'laravel/public/currentpayments/index-internal', '1', 'LOCKABLE');