-- cambios pedidos por requerimientos, un recap puede tener varios documentos, varias instituciones financieras, varias tarjetas de credito

ALTER TABLE `recap` 
DROP FOREIGN KEY `fk_rcp_pcg`;
ALTER TABLE `recap` 
DROP COLUMN `serial_pcg`,
DROP INDEX `fk_rcp_pcg_idx` ;

-- tabla paraasignar documentos a recap
CREATE TABLE `document_recap` (
  `serial_drc` INT NOT NULL AUTO_INCREMENT,
  `serial_pcg` INT NULL,
  `serial_rcp` INT NULL,
  `status_drcl` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_drc`));

  
  ALTER TABLE `recap` 
ADD COLUMN `agreement_name_rcp` VARCHAR(45) NULL DEFAULT NULL AFTER `serial_fin`,
ADD COLUMN `commere_code_rcp` VARCHAR(45) NULL AFTER `agreement_name_rcp`,
ADD COLUMN `company_count_code_rcp` VARCHAR(45) NULL DEFAULT NULL AFTER `commere_code_rcp`,
ADD COLUMN `status_rcp` ENUM('ACTIVE', 'INACTIVE') NULL AFTER `recap_number`;

ALTER TABLE `recap` 
ADD COLUMN `value_rcp` DOUBLE NULL AFTER `recap_number`;

ALTER TABLE `recap` 
ADD COLUMN `type_rcp` ENUM('MANUAL', 'BASCH') NULL DEFAULT NULL AFTER `value_rcp`;
ALTER TABLE `recap` 
ADD COLUMN `month_rcp` VARCHAR(3) NULL AFTER `type_rcp`;
ALTER TABLE `recap` 
ADD COLUMN `number_ticket_pdt` INT NULL COMMENT 'NUMERO DE BOLETA' AFTER `recap_number`;




ALTER TABLE `payment_detail` 
CHANGE COLUMN `date_voucher_pdt` `date_voucher_pdt` DATE NULL DEFAULT NULL COMMENT 'fecha de vale' ,
CHANGE COLUMN `number_voucher_pdt` `number_voucher_pdt` INT(11) NULL DEFAULT NULL COMMENT 'numero de vale' ,
ADD COLUMN `payment_date_pdt` DATE NULL COMMENT 'fecha de pago' AFTER `serial_rcp`,
ADD COLUMN `indicted_pdt` ENUM('YES', 'NO') NULL COMMENT 'procesado en un recap' AFTER `payment_date_pdt`,
ADD COLUMN `notes_pdt` TEXT NULL COMMENT 'notas observaciones' AFTER `payment_date_pdt`,
ADD COLUMN `registered_accounting_pdt` ENUM('YES', 'NO') NULL COMMENT 'registrado en contabilidad' AFTER `notes_pdt`;

  
  ALTER TABLE `document_recap` 
ADD INDEX `fk_drc_pcg_idx` (`serial_pcg` ASC),
ADD INDEX `fk_drc_rcp_idx` (`serial_rcp` ASC);
ALTER TABLE `document_recap` 
ADD CONSTRAINT `fk_drc_pcg`
  FOREIGN KEY (`serial_pcg`)
  REFERENCES `payment_concept_group` (`serial_pcg`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_drc_rcp`
  FOREIGN KEY (`serial_rcp`)
  REFERENCES `recap` (`serial_rcp`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


-- tabla para guardar varias tarjetas de credito por institucion financiera por recap 
 CREATE TABLE `recap_financial_institution_card` (
  `serial_rfc` INT NOT NULL AUTO_INCREMENT,
  `serial_rcp` INT NULL COMMENT 'recap',
  `serial_fic` INT NULL COMMENT 'tarjeta de credito por institucion financiera',
  `status_rfc` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_rfc`),
  INDEX `fk_rfc_rcp_idx` (`serial_rcp` ASC),
  INDEX `fk_rfc_fic_idx` (`serial_fic` ASC),
  CONSTRAINT `fk_rfc_rcp`
    FOREIGN KEY (`serial_rcp`)
    REFERENCES `recap` (`serial_rcp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rfc_fic`
    FOREIGN KEY (`serial_fic`)
    REFERENCES `financial_institution_card` (`serial_fic`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
COMMENT = 'Tabla de rompimiento entre recap y tarjetas de credito instituciones financieras y recap';

-- eliminar columnas que no se ocuparon para el recap originalmente si se necesitaban
ALTER TABLE `recap` 
DROP FOREIGN KEY `fk_rcp_fin`,
DROP FOREIGN KEY `fk_rcp_ccd`;
ALTER TABLE `recap` 
DROP COLUMN `serial_fin`,
DROP COLUMN `serial_ccd`,
DROP INDEX `fk_rcp_fin_idx` ,
DROP INDEX `fk_rcp_ccd_idx` ;