CREATE TABLE `recap` (
  `serial_rcp` INT NOT NULL AUTO_INCREMENT,
  `serial_pcg` INT NULL,
  `serial_pym` INT NULL,
  `serial_ccd` INT NULL,
  `serial_fin` INT NULL,
  `establishment_unic_code_rcp` VARCHAR(45) NULL,
  `dues_number_rcp` VARCHAR(45) NULL,
  `start_vaucher_rcp` INT NULL,
  `send_date` DATE NULL,
  `proccess_date` DATE NULL,
  `recap_number` INT NULL,
  PRIMARY KEY (`serial_rcp`),
  INDEX `fk_rcp_pcg_idx` (`serial_pcg` ASC),
  INDEX `fk_rcp_pym_idx` (`serial_pym` ASC),
  INDEX `fk_rcp_ccd_idx` (`serial_ccd` ASC),
  INDEX `fk_rcp_fin_idx` (`serial_fin` ASC),
  CONSTRAINT `fk_rcp_pcg`
    FOREIGN KEY (`serial_pcg`)
    REFERENCES `payment_concept_group` (`serial_pcg`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rcp_pym`
    FOREIGN KEY (`serial_pym`)
    REFERENCES `payment_method` (`serial_pym`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rcp_ccd`
    FOREIGN KEY (`serial_ccd`)
    REFERENCES `credit_card` (`serial_ccd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rcp_fin`
    FOREIGN KEY (`serial_fin`)
    REFERENCES `financial_institution` (`serial_fin`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
	ALTER TABLE `payment_detail` 
ADD COLUMN `date_voucher_pdt` DATE NULL AFTER `status_pdt`,
ADD COLUMN `number_voucher_pdt` INT NULL AFTER `date_voucher_pdt`;



	
	ALTER TABLE `payment_detail` 
ADD COLUMN `serial_rcp` INT NULL DEFAULT NULL AFTER `number_voucher_pdt`,
ADD INDEX `fk_pdt_rcp_idx` (`serial_rcp` ASC);
ALTER TABLE `payment_detail` 
ADD CONSTRAINT `fk_pdt_rcp`
  FOREIGN KEY (`serial_rcp`)
  REFERENCES `recap` (`serial_rcp`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  
  CREATE TABLE `recap_header` (
  `serial_rch` INT NOT NULL,
  `serial_pym` INT NULL,
  `serial_ccd` INT NULL,
  `serial_fin` INT NULL,
  `establishment_unic_code_rch` VARCHAR(45) NULL,
  `agreement_name_rch` VARCHAR(45) NULL,
  `commere_code_rch` VARCHAR(45) NULL,
  `company_count_code_rch` VARCHAR(45) NULL,
  PRIMARY KEY (`serial_rch`),
  INDEX `fk_rch_pym_idx` (`serial_pym` ASC),
  INDEX `fk_rch__ccd_idx` (`serial_ccd` ASC),
  INDEX `fk_rch_fin_idx` (`serial_fin` ASC),
  CONSTRAINT `fk_rch_pym`
    FOREIGN KEY (`serial_pym`)
    REFERENCES `payment_method` (`serial_pym`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rch_ccd`
    FOREIGN KEY (`serial_ccd`)
    REFERENCES `credit_card` (`serial_ccd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rch_fin`
    FOREIGN KEY (`serial_fin`)
    REFERENCES `financial_institution` (`serial_fin`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

	
	ALTER TABLE `financial_institution_card` 
ADD COLUMN `credit_type_fic` ENUM('CURRENT', 'CURRENT_DEFERRED', 'DEFERRED_OWN') NULL AFTER `status_fic`;

ALTER TABLE `payment_detail` 
ADD COLUMN `serial_pdt` INT NULL AFTER `status_pdt`;

ALTER TABLE `payment_detail` 
CHANGE COLUMN `serial_pdt` `serial_pdt` INT(11) NULL DEFAULT NULL FIRST;

set @secuencial:=0;
SET SQL_SAFE_UPDATES=0;
update payment_detail set serial_pdt=@secuencial:=@secuencial+1;

ALTER TABLE `payment_detail` 
CHANGE COLUMN `serial_pdt` `serial_pdt` INT(11) NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`serial_pdt`);

ALTER TABLE `payment_detail` 
ADD COLUMN `number_ticket_pdt` BIGINT NULL COMMENT 'numero de boleta' AFTER `registered_accounting_pdt`;


insert into allowed_pages_by_process values('497','paymentcontroller@getpaymentdetail');
insert into allowed_pages_by_process values('497','paymentcontroller@getpaymentdetailbyserialpdt');