-- SCRIPT PARA ASOCIAR EL SIMBOLO Y CODIGO DE MONEDA.

ALTER TABLE `currency` 
ADD COLUMN `sign_crr` VARCHAR(5) NULL DEFAULT NULL AFTER `name_crr`,
ADD COLUMN `code_crr` VARCHAR(5) NULL DEFAULT NULL AFTER `sign_crr`;


-- BOTON DE PAGOS ---

-- INFORMACION DEL BOTON 

CREATE TABLE `payment_button` (
  `serial_pbn` INT(11) NOT NULL,
  `serial_crr` INT(11) NOT NULL,  
  `merchant_name_pbn` VARCHAR(100) NOT NULL,
  `service_pbn` VARCHAR(100) NULL DEFAULT NULL,
  `type_pbn` ENUM('PAYCLUB','ALIGNET') NULL DEFAULT NULL,
  `adquirer_id_pbn` VARCHAR(45) NULL DEFAULT NULL,
  `merchant_id_pbn` VARCHAR(45) NULL DEFAULT NULL,
  `vector_pbn` VARCHAR(100) NULL DEFAULT NULL,
  `sign_private_key_pbn` VARCHAR(200) NULL DEFAULT NULL,
  `sign_public_key_pbn` VARCHAR(200) NULL DEFAULT NULL,
  `cipher_private_key_pbn` VARCHAR(200) NULL DEFAULT NULL,
  `cipher_public_key_pbn` VARCHAR(200) NULL DEFAULT NULL,
  `path_logo` VARCHAR(200) NULL DEFAULT NULL,
  `path_terms_conditions` VARCHAR(200) NULL DEFAULT NULL,
  `vpos_url_pbn` VARCHAR(500) NULL DEFAULT NULL,
  `local_id` VARCHAR(45) NULL DEFAULT NULL,
  `key` VARCHAR(45) NULL DEFAULT NULL,
  `xml_process` VARCHAR(200) NULL DEFAULT NULL,
  PRIMARY KEY (`serial_pbn`),
  INDEX `fk_currency_payment_button_idx` (`serial_crr` ASC),
  CONSTRAINT `fk_currency_payment_button`
    FOREIGN KEY (`serial_crr`)
    REFERENCES `currency` (`serial_crr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
ALTER TABLE `payment_button` 
ADD COLUMN `id_group_pbn` VARCHAR(45) NULL DEFAULT NULL AFTER `vector_pbn`;


-- RELACION DE TIPO DE DOCUMENTO POR BOTON DE PAGOS.

CREATE TABLE `group_payment_button` (
  `serial_gpb` INT(11) NOT NULL,
  `serial_pcg` INT(11) NOT NULL,
  `serial_pbn` INT(11) NOT NULL,
  `status_gpb` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gpb`),
  INDEX `fk_payment_group_button_idx` (`serial_pbn` ASC),
  INDEX `fk_payment_group_button_serial_pcg_idx` (`serial_pcg` ASC),
  CONSTRAINT `fk_payment_group_button_serial_pbn`
    FOREIGN KEY (`serial_pbn`)
    REFERENCES `payment_button` (`serial_pbn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_payment_group_button_serial_pcg`
    FOREIGN KEY (`serial_pcg`)
    REFERENCES `payment_concept_group` (`serial_pcg`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
-- RELACION DE NUMERO DE TRANSACCION CON BOTON Y TIPO DE DOCUMENTO

ALTER TABLE `current_deferred` 
ADD COLUMN `serial_gpb` INT(11) NULL DEFAULT NULL AFTER `confirm_status_cdf`,
ADD INDEX `fk_group_payment_button_cdf_idx` (`serial_gpb` ASC);
ALTER TABLE `current_deferred` 
ADD CONSTRAINT `fk_group_payment_button_cdf`
  FOREIGN KEY (`serial_gpb`)
  REFERENCES `group_payment_button` (`serial_gpb`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  ALTER TABLE `current_deferred` 
ADD COLUMN `card_cdf` VARCHAR(50) NULL DEFAULT NULL AFTER `serial_fic`;


-- SE AGREGA ESTADOS DE TRANSACCION DE ALIGNET 
ALTER TABLE `current_deferred` 
CHANGE COLUMN `confirm_status_cdf` `confirm_status_cdf` ENUM('NEW','PENDING','OK','KO','00','01','05') NOT NULL DEFAULT 'NEW' ;

-- CAMPO QUE INDICA SI UN RUBRO SE DIVIDE EN FACTURACIÓN DE ACUERDO A LO RECIBIDO EN LAS CUOTAS PAGADAS.
ALTER TABLE year_level_specialty_payment_concept 
ADD COLUMN `invoice_by_quota` ENUM('YES','NO') NOT NULL DEFAULT 'YES' AFTER `check_require`;