
INSERT INTO `sys_appconfig` (`id`, `setting`, `value`) VALUES (NULL, 'correo_notificacion_ips_no_autorizadas', NULL);


ALTER TABLE `sys_invoices` ADD `company_id` INT NULL DEFAULT NULL AFTER `userid`, ADD `company_name` VARCHAR(500) NULL DEFAULT NULL AFTER `company_id`;


ALTER TABLE `sys_pg` ENGINE=InnoDB;
ALTER TABLE `sys_invoices` ENGINE=InnoDB;
ALTER TABLE `sys_invoiceitems` ENGINE=InnoDB;

INSERT INTO `sys_pmethods` (`id`, `name`, `sorder`) VALUES ('4', 'POS Terminal', '5');
INSERT INTO `sys_pmethods` (`id`, `name`, `sorder`) VALUES ('2', 'Saving Account', '6');

CREATE TABLE `crm_accounts_company` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `idCompany` int(11) DEFAULT NULL,
  `crm_account_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `crm_accounts_company`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_cac_idcompany_from_sys_companies` (`idCompany`),
  ADD KEY `fk_cac_crm_account_id` (`crm_account_id`);
 

ALTER TABLE `crm_accounts_company`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `crm_accounts_company`
  ADD CONSTRAINT `fk_cac_crm_account_id` FOREIGN KEY (`crm_account_id`) REFERENCES `crm_accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_cac_idcompany_from_sys_companies` FOREIGN KEY (`idCompany`) REFERENCES `sys_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
 

CREATE TABLE `company_documents` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `sys_companies_id` int(11) DEFAULT NULL,
  `nombre_original` varchar(255) DEFAULT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `nombre_img_sistema` varchar(255) DEFAULT NULL,
  `url` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `company_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_cd_sys_companies_from_sc` (`sys_companies_id`);

 
ALTER TABLE `company_documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

 
ALTER TABLE `company_documents`
  ADD CONSTRAINT `fk_cd_sys_companies_from_sc` FOREIGN KEY (`sys_companies_id`) REFERENCES `sys_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

 

CREATE TABLE `authorized_ip_setting` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `tipo_bloqueo` int(11) DEFAULT NULL COMMENT '1/aceptar solo ip, 2/rechazar solo ip',
  `fecha_post` timestamp NULL DEFAULT NULL,
  `fecha_update` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
ALTER TABLE `authorized_ip_setting`
  ADD PRIMARY KEY (`id`);

 
ALTER TABLE `authorized_ip_setting`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
 

CREATE TABLE `authorized_ip_staff` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `staff_id` int(11) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `tipo_bloqueo` int(11) DEFAULT NULL,
  `fecha_post` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `authorized_ip_staff`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `authorized_ip_staff`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
 

-- para agregar un nuevo acceso de configuracion
ALTER TABLE `sys_users` ADD `access_configuration` INT NULL DEFAULT NULL;



 
 
CREATE TABLE `localization_terminal` (
  `id` int(11) NOT NULL,
  `localization` varchar(255) DEFAULT NULL,
  `stripe_configuration_id` int(11) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `zip_code` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `id_terminal_location_stripe` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `localization_terminal`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_lt_ltid_from_sys_pg` (`stripe_configuration_id`);
 
ALTER TABLE `localization_terminal`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

 
ALTER TABLE `localization_terminal`
  ADD CONSTRAINT `fk_lt_ltid_from_sys_pg` FOREIGN KEY (`stripe_configuration_id`) REFERENCES `sys_pg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
 

-- otras query
CREATE TABLE `reader_terminal` (
  `id` int(11) NOT NULL,
  `identification` varchar(255) DEFAULT NULL,
  `stripe_configuration_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `registration_code` varchar(500) DEFAULT NULL,
  `id_reader_stripe` text DEFAULT NULL,
  `serial_number_stripe` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `reader_terminal`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_rt_lid_from_location_terminal` (`location_id`);

 
ALTER TABLE `reader_terminal`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

 
ALTER TABLE `reader_terminal`
  ADD CONSTRAINT `fk_rt_lid_from_location_terminal` FOREIGN KEY (`location_id`) REFERENCES `localization_terminal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;



-- payment_terminal

CREATE TABLE `payment_terminal` (
  `id` int(11) NOT NULL,
  `payment_intent_id` text DEFAULT NULL,
  `process_payment_intent_id` text DEFAULT NULL,
  `id_invoice` int(11) DEFAULT NULL,
  `id_transactions_temp` int(11) DEFAULT NULL,
  `amount` double DEFAULT NULL,
  `payment_status` varchar(255) DEFAULT NULL,
  `secret_key_pg` text DEFAULT NULL,
  `public_key_pg` text DEFAULT NULL,
  `date_verify` datetime DEFAULT NULL,
  `date_create` datetime DEFAULT NULL,
  `account` varchar(255) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `mensaje_error` varchar(500) DEFAULT NULL,
  `codigo_error` varchar(255) DEFAULT NULL,
  `name_staff` varchar(255) DEFAULT NULL,
  `id_staff` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
 
ALTER TABLE `payment_terminal`
  ADD PRIMARY KEY (`id`);

 
ALTER TABLE `payment_terminal`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

-- invoice_details_bank_account y invoice_detail_status
 
CREATE TABLE `invoice_detail_status` (
  `id` int(11) NOT NULL,
  `status` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

 
INSERT INTO `invoice_detail_status` (`id`, `status`) VALUES
(1, 'Paid'),
(2, 'Pending'),
(3, 'Rejected');
 
ALTER TABLE `invoice_detail_status`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `invoice_detail_status`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

 
 
-- invoice_details_bank_account
CREATE TABLE `invoice_details_bank_account` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `invoices_id` int(11) DEFAULT NULL,
  `account` varchar(500) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `id_transactions_temp` int(11) DEFAULT NULL,
  `amount` double DEFAULT NULL,
  `payment_status` int(11) DEFAULT NULL COMMENT '1=pago 2=pendiente 3=error',
  `configuration_id_stripe` int(11) DEFAULT NULL,
  `sk_stripe` text DEFAULT NULL,
  `pi_stripe` text DEFAULT NULL,
  `payment_method_stripe` text DEFAULT NULL,
  `mensaje_error` varchar(500) DEFAULT NULL,
  `codigo_error` varchar(500) DEFAULT NULL,
  `date_verify` datetime DEFAULT NULL,
  `date_create` datetime DEFAULT NULL,
  `name_staff` varchar(255) DEFAULT NULL,
  `id_staff` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `invoice_details_bank_account`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_idba_ps_from_ps` (`payment_status`);

 
ALTER TABLE `invoice_details_bank_account`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
 
ALTER TABLE `invoice_details_bank_account`
  ADD CONSTRAINT `fk_idba_ps_from_ps` FOREIGN KEY (`payment_status`) REFERENCES `invoice_detail_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
 
 
