ALTER TABLE `year_level_specialty_payment_concept` 
CHANGE COLUMN `type_payment_concept_ypc` `type_payment_concept_ypc` ENUM('TRANSPORT', 'LIFE_INSURANCE', 'UNEMPLOYMENT_INSURANCE', 'ACCCIDENT_INSURANCE', 'SCHOOL_SUPPLIES', 'SUPPLIES', 'REGISTER', 'FOOD', 'OTHER', 'REGISTER_NEW_STUDNETS', 'FEEDING') NULL DEFAULT NULL ;


INSERT INTO `payment_concept_group` (`serial_pcg`, `name`, `toname`, `description`, `order_payment`, `ruc_pcg`, `description_name_pcg`, `status`) VALUES ('4', 'ALIMENTACIÓN', 'FATHER', 'ALIMENTACIÓN', '2', '1791862864001', 'ALM', 'ACTIVE');


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

CREATE TABLE `invoice_data_feeding_payments` (
  `serial_idf` INT NOT NULL AUTO_INCREMENT,
  `serial_cdf` INT NULL,
  `identification_type_idf` VARCHAR(45) NULL,
  `business_name_idf` VARCHAR(300) NULL,
  `id_idf` VARCHAR(45) NULL,
  `address_idf` VARCHAR(45) NULL,
  `phone_paying_idf` VARCHAR(45) NULL,
  `email_paying_idf` VARCHAR(45) NULL,
  `status_idf` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_idf`),
  INDEX `fk_idf_cdf_idx` (`serial_cdf` ASC),
  CONSTRAINT `fk_idf_cdf`
    FOREIGN KEY (`serial_cdf`)
    REFERENCES `current_deferred` (`serial_cdf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;


CREATE TABLE `current_deferred_feeding` (
  `serial_cdf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_fic` int(11) DEFAULT NULL,
  `card_cdd` varchar(50) DEFAULT NULL,
  `card_cdf` varchar(50) DEFAULT NULL,
  `code_cdf` varchar(50) DEFAULT NULL,
  `total_quotas_cdf` int(11) NOT NULL,
  `quota_amount_cdf` decimal(10,2) NOT NULL,
  `paid_quotas_cdf` int(11) DEFAULT NULL,
  `last_month_paid_quota_cdf` varchar(3) DEFAULT NULL,
  `owner_cdf` varchar(300) NOT NULL,
  `kinship_cdf` varchar(300) NOT NULL,
  `transaction_date_cdf` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `students_cdf` varchar(60) DEFAULT NULL,
  `credit_code_cdf` varchar(20) DEFAULT NULL,
  `type_cdf` enum('ACADEMIC','NON_ACADEMIC') NOT NULL,
  `status_cdf` enum('ACTIVE','INACTIVE','PAID') NOT NULL DEFAULT 'ACTIVE',
  `confirm_status_cdf` 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_cdf` varchar(200) DEFAULT NULL,
  `last_digits_card_cdf` varchar(45) DEFAULT NULL,
  `document_cdf` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`serial_cdf`),
  KEY `fk_fic_relationship_338` (`serial_fic`),
  KEY `fk_fic_relationship_340` (`serial_usr`),
  KEY `fk_fic_group_payment_button_cdf_idx` (`serial_gpb`),
  KEY `fk_fic_relationship_year_cdf_idx` (`serial_scy`),
  CONSTRAINT `fk_fic_group_payment_button_cdd` FOREIGN KEY (`serial_gpb`) REFERENCES `group_payment_button` (`serial_gpb`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_fic_group_payment_button_cdf` FOREIGN KEY (`serial_gpb`) REFERENCES `group_payment_button` (`serial_gpb`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_fic_relationship_338` FOREIGN KEY (`serial_fic`) REFERENCES `financial_institution_card` (`serial_fic`),
  CONSTRAINT `fk_fic_relationship_340` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  CONSTRAINT `fk_fic_relationship_year_cdf` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

ALTER TABLE `invoice_data_feeding_payments` 
ADD COLUMN `created_at` DATETIME NULL AFTER `status_idf`,
ADD COLUMN `updated_at` DATETIME NULL AFTER `created_at`;

CREATE TABLE `student_current_deferred_feeding_dates_to_pay_payment_concept` (
  `serial_scr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_std` int(11) DEFAULT NULL,
  `serial_dts` int(11) DEFAULT NULL,
  `serial_ypc` int(11) DEFAULT NULL,
  `serial_cdf` int(11) DEFAULT NULL,
  `status_scr` enum('ACTIVE','INACTIVE') DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`serial_scr`),
  KEY `fk_scd_dts_idx` (`serial_std`),
  KEY `fk_scd_dts_idx1` (`serial_dts`),
  KEY `fk_scd_ypc_idx` (`serial_ypc`),
  KEY `fk_scd_cdf_idx` (`serial_cdf`)
) ENGINE=InnoDB ;

CREATE TABLE `transfer_feeding_paymets` (
  `serial_trn` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_usr_update` int(11) DEFAULT NULL,
  `comment_trn` text,
  `reference_trn` varchar(200) DEFAULT NULL,
  `img_receipt_trn` varchar(300) DEFAULT NULL,
  `value_trn` decimal(10,2) DEFAULT NULL,
  `date_trnasfer_trn` date DEFAULT NULL,
  `checked_by_parent_trn` enum('YES','NO') DEFAULT NULL,
  `status_transfer_trn` enum('PENDING','REJECTED','APPROVED') DEFAULT NULL,
  `status_trn` enum('ACTIVE','INACTIVE') DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`serial_trn`),
  KEY `fk_trn_usr_idx` (`serial_usr`),
  KEY `fk_trn_scy_idx` (`serial_scy`)
) ENGINE=InnoDB ;


ALTER TABLE `transfer_feeding_paymets` 
ADD INDEX `fk_trn_usr_up_idx` (`serial_usr_update` ASC);
ALTER TABLE `transfer_feeding_paymets` 
ADD CONSTRAINT `fk_trn_usr`
  FOREIGN KEY (`serial_usr`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_trn_scy`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_trn_usr_up`
  FOREIGN KEY (`serial_usr_update`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `transfer` 
ADD INDEX `fk_trn_usr_up_idx` (`serial_usr_update` 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,
ADD CONSTRAINT `fk_trn_scy`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_trn_usr_up`
  FOREIGN KEY (`serial_usr_update`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `student_transfer_feeding_payments` 
ADD CONSTRAINT `fk_stt_trn`
  FOREIGN KEY (`serial_trn`)
  REFERENCES `transfer_feeding_paymets` (`serial_trn`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_std_trn`
  FOREIGN KEY (`serial_std`)
  REFERENCES `student` (`serial_std`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `invoice_data_feeding_payments` 
ADD COLUMN `serial_trn` INT NULL AFTER `serial_cdf`,
ADD INDEX `fk_idf_trn_idx` (`serial_trn` ASC);
ALTER TABLE `invoice_data_feeding_payments` 
ADD CONSTRAINT `fk_idf_trn`
  FOREIGN KEY (`serial_trn`)
  REFERENCES `transfer_feeding_paymets` (`serial_trn`)
  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', 'CHECK_FEEDING_PAYMENTS_TRANSFERS') NULL DEFAULT NULL ;

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

CREATE TABLE IF NOT EXISTS `student_transfer_feeding_payments` (
  `serial_stt` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_trn` INT(11) NULL DEFAULT NULL,
  `serial_std` INT(11) NULL DEFAULT NULL,
  `serial_ypc` INT(11) NULL DEFAULT NULL,
  `serial_dts` INT(11) NULL DEFAULT NULL,
  `cps_stt` VARCHAR(45) NULL DEFAULT NULL,
  `value_stt` DECIMAL(10,2) NULL DEFAULT NULL,
  `status_stt` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`serial_stt`),
  INDEX `fk_serial_trf_idx` (`serial_trn` ASC),
  INDEX `fk_serial_stt_std_idx` (`serial_std` ASC),
  INDEX `fk_ypc_trn_idx` (`serial_ypc` ASC),
  INDEX `fk_dts_trn_idx` (`serial_dts` ASC),
  CONSTRAINT `fk_dts_trn`
    FOREIGN KEY (`serial_dts`)
    REFERENCES `dates_to_current_payments` (`serial_dts`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_std_trn`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_stt_trn`
    FOREIGN KEY (`serial_trn`)
    REFERENCES `transfer_feeding_paymets` (`serial_trn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ypc_trn`
    FOREIGN KEY (`serial_ypc`)
    REFERENCES `year_level_specialty_payment_concept` (`serial_ypc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = latin1;

INSERT INTO `payment_button` (`serial_pbn`, `serial_crr`, `merchant_name_pbn`, `service_pbn`, `path_logo`, `url_web_page_bank`, `path_terms_conditions`, `info_pbn`, `vpos_url_pbn`, `url_return`, `login`, `trankey`, `type_pbn`, `partner_pbn`) VALUES ('20', '47', 'U.E. Tomas Moro pagos en corriente', 'PAGOS EN DE ALIMENTACIÓN', 'https://static.placetopay.com/placetopay-logo.svg', '<p align=\'justify\'> Por favor seleccione <b>Placetopay</b> si usted va a pagar la colegiatura a 10 meses sin intereses con tarjetas de crédito Diners Club o Visa y MasterCard del Banco Pichinhca o American Express del Banco Guayaquil. <p>', 'files/TCUETM.txt', '<p align=\'justify\'> Por favor seleccione <b>Placetopay</b> si usted va a cancelar en corriente con tarjetas de crédito Diners Club o Visa y MasterCard del Banco Pichincha o American Express del Banco Guayaquil. <p>', 'https://test.placetopay.ec/redirection/', 'https://learnbox.tomasmoro.k12.ec/demoboton/laravel/public/feedingpayments/return', '2b2a1f3a86f5d3bd2e98e87e2b3321dd', 'A76IY5kpY3593O3A', 'WALLET_P2P', 'Placetopay');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('840', '156', 'Pago de alimentación', 'laravel/public/feedingpayments', '135.60', 'LOCKABLE');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('841', '402', 'Alimentación', 'laravel/public/reportfeeding', '1', 'LOCKABLE');

ALTER TABLE `payment_method` 
CHANGE COLUMN `type_pym` `type_pym` ENUM('CARD', 'PAYCHECK', 'DEBIT', 'TRANSFER', 'PAYROLL', 'OCP', 'FEEDING') NULL DEFAULT NULL ;


INSERT INTO `payment_method` (`serial_pym`, `serial_ptm`, `name_pym`, `requires_owner_info_pym`, `requires_financial_pym`, `requires_credit_card_pym`, `type_pym`, `avalible_preaproved_pym`, `status_pym`, `name_acount_pym`, `acount_type_pym`, `acount_number_pym`, `acount_ruc_pym`, `acount_banck_pym`, `url_img_pym`) VALUES (NULL, '3', 'Cheque post-fechado', 'YES', 'NO', 'NO', 'FEEDING', 'NO', 'ACTIVE', 'UNIDAD EDUCATIVA TOMAS MORO', 'CORRIENTE', '3421017104', '1791862864001', 'PICHINCHA', 'https://learnbox.tomasmoro.k12.ec/learnbox/img/payment_button/transferencia.jpg');
UPDATE `payment_method` SET `name_pym`='Transferencia de alimentación', `name_acount_pym`='LORENZO ÁLVAREZ MALO', `acount_type_pym`='AHORROS', `acount_number_pym`='2207043915', `acount_ruc_pym`='1705088944001' WHERE `serial_pym`='28';
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('844', '156', 'Revisión de transferencias  de alimentación', 'learnbox/laravel/public/feedingpayments/index-internal', '0.01', 'LOCKABLE');