/home/suroeste/public_html/payments.transportessuroeste.com/sql
Edit: /home/suroeste/public_html/payments.transportessuroeste.com/sql/database-cpanel.sql (27787B)
-- ============================================================================
-- BASE DE DATOS - TRANSPORTES SUROESTE - PASARELA DE PAGOS EPAYCO
-- ============================================================================
-- Version adaptada para cPanel / Hosting Compartido
-- - Sin SET GLOBAL (requiere SUPER privilege)
-- - Sin CREATE EVENT (requiere EVENT privilege)
-- - Los eventos se reemplazan con cron jobs de cPanel
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ============================================================================
-- CREAR BASE DE DATOS (en cPanel se crea desde el panel, no desde SQL)
-- Si tienes acceso, descomenta las siguientes lineas:
-- ============================================================================
-- CREATE DATABASE IF NOT EXISTS `transportes_suroeste_payments`
-- CHARACTER SET utf8mb4
-- COLLATE utf8mb4_unicode_ci;
-- USE `transportes_suroeste_payments`;
-- ============================================================================
-- TABLA: api_clients - Clientes autorizados para consumir la API
-- ============================================================================
DROP TABLE IF EXISTS `webhook_deliveries`;
DROP TABLE IF EXISTS `refunds`;
DROP TABLE IF EXISTS `daily_reconciliation`;
DROP TABLE IF EXISTS `transaction_logs`;
DROP TABLE IF EXISTS `transaction_status_history`;
DROP TABLE IF EXISTS `security_events`;
DROP TABLE IF EXISTS `audit_trail`;
DROP TABLE IF EXISTS `rate_limit_tracking`;
DROP TABLE IF EXISTS `blocked_ips`;
DROP TABLE IF EXISTS `transactions`;
DROP TABLE IF EXISTS `api_clients`;
DROP TABLE IF EXISTS `system_config`;
CREATE TABLE `api_clients` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`client_uuid` CHAR(36) NOT NULL COMMENT 'UUID unico del cliente',
`client_name` VARCHAR(100) NOT NULL COMMENT 'Nombre del cliente/empresa',
`api_key` VARCHAR(64) NOT NULL COMMENT 'API Key publica',
`api_secret_hash` VARCHAR(255) NOT NULL COMMENT 'Secret hasheado',
`webhook_url` VARCHAR(500) NULL COMMENT 'URL para callbacks',
`webhook_secret` VARCHAR(64) NULL COMMENT 'Secret para firmar webhooks',
`allowed_ips` TEXT NULL COMMENT 'IPs permitidas en formato JSON',
`rate_limit` INT UNSIGNED DEFAULT 100 COMMENT 'Limite de peticiones por minuto',
`is_active` TINYINT(1) DEFAULT 1,
`environment` VARCHAR(20) DEFAULT 'sandbox',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_access_at` DATETIME NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_client_uuid` (`client_uuid`),
UNIQUE KEY `uk_api_key` (`api_key`),
INDEX `idx_is_active` (`is_active`),
INDEX `idx_environment` (`environment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Clientes autorizados para consumir la API';
-- ============================================================================
-- TABLA: transactions - Transacciones de pago principales
-- ============================================================================
CREATE TABLE `transactions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`transaction_uuid` CHAR(36) NOT NULL COMMENT 'UUID unico de transaccion',
`client_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK a api_clients',
`ticket_reference` VARCHAR(100) NOT NULL COMMENT 'Referencia del ticket del cliente',
`internal_reference` VARCHAR(50) NOT NULL COMMENT 'Referencia interna unica',
`epayco_ref` VARCHAR(50) NULL COMMENT 'Referencia de ePayco',
`epayco_transaction_id` VARCHAR(50) NULL COMMENT 'ID de transaccion ePayco',
`epayco_session_id` VARCHAR(100) NULL COMMENT 'Session ID de Smart Checkout',
`amount` DECIMAL(15,2) NOT NULL COMMENT 'Monto de la transaccion',
`currency` CHAR(3) DEFAULT 'COP' COMMENT 'Codigo ISO de moneda',
`tax` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Impuesto',
`tax_base` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Base gravable',
`status` VARCHAR(20) DEFAULT 'pending',
`status_code` VARCHAR(10) NULL COMMENT 'Codigo de estado ePayco',
`status_message` VARCHAR(255) NULL COMMENT 'Mensaje de estado',
`payment_method` VARCHAR(50) NULL COMMENT 'Metodo de pago usado',
`payment_method_type` VARCHAR(20) NULL,
`card_last_four` CHAR(4) NULL COMMENT 'Ultimos 4 digitos de tarjeta',
`card_brand` VARCHAR(20) NULL COMMENT 'Marca de la tarjeta',
`bank_name` VARCHAR(100) NULL COMMENT 'Nombre del banco',
`payer_email` VARCHAR(500) NULL COMMENT 'Email del pagador (cifrado)',
`payer_document_type` VARCHAR(10) NULL COMMENT 'Tipo documento',
`payer_document` VARCHAR(255) NULL COMMENT 'Numero documento (cifrado)',
`payer_name` VARCHAR(500) NULL COMMENT 'Nombre pagador (cifrado)',
`payer_phone` VARCHAR(255) NULL COMMENT 'Telefono pagador (cifrado)',
`description` VARCHAR(500) NULL COMMENT 'Descripcion de la compra',
`ip_address` VARCHAR(45) NOT NULL COMMENT 'IP del cliente',
`user_agent` VARCHAR(500) NULL,
`request_signature` VARCHAR(128) NOT NULL COMMENT 'Firma HMAC de la solicitud',
`response_signature` VARCHAR(128) NULL COMMENT 'Firma de respuesta ePayco',
`extra_data` TEXT NULL COMMENT 'Datos adicionales del ticket',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`processed_at` DATETIME NULL COMMENT 'Fecha de procesamiento',
`expires_at` DATETIME NULL COMMENT 'Fecha de expiracion',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_transaction_uuid` (`transaction_uuid`),
UNIQUE KEY `uk_internal_reference` (`internal_reference`),
INDEX `idx_client_id` (`client_id`),
INDEX `idx_status` (`status`),
INDEX `idx_created_at` (`created_at`),
INDEX `idx_epayco_ref` (`epayco_ref`),
INDEX `idx_ticket_reference` (`ticket_reference`),
INDEX `idx_status_created` (`status`, `created_at`),
CONSTRAINT `fk_transactions_client` FOREIGN KEY (`client_id`)
REFERENCES `api_clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Transacciones de pago - Tabla principal';
-- ============================================================================
-- TABLA: transaction_status_history - Historial de estados
-- ============================================================================
CREATE TABLE `transaction_status_history` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`transaction_id` BIGINT UNSIGNED NOT NULL,
`previous_status` VARCHAR(20) NULL,
`new_status` VARCHAR(20) NOT NULL,
`status_code` VARCHAR(10) NULL,
`status_message` VARCHAR(255) NULL,
`changed_by` VARCHAR(100) DEFAULT 'system' COMMENT 'Quien cambio el estado',
`ip_address` VARCHAR(45) NULL,
`extra_data` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_transaction_id` (`transaction_id`),
INDEX `idx_created_at` (`created_at`),
INDEX `idx_new_status` (`new_status`),
CONSTRAINT `fk_status_history_transaction` FOREIGN KEY (`transaction_id`)
REFERENCES `transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Historial de cambios de estado - Auditoria';
-- ============================================================================
-- TABLA: transaction_logs - Logs detallados de transacciones
-- ============================================================================
CREATE TABLE `transaction_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`transaction_id` BIGINT UNSIGNED NULL,
`log_uuid` CHAR(36) NOT NULL,
`epayco_session_id` VARCHAR(100) NULL,
`log_type` VARCHAR(20) NOT NULL,
`action` VARCHAR(100) NOT NULL COMMENT 'Accion realizada',
`endpoint` VARCHAR(255) NULL COMMENT 'Endpoint llamado',
`http_method` VARCHAR(10) NULL,
`http_status` SMALLINT UNSIGNED NULL,
`request_headers` TEXT NULL COMMENT 'Headers de la peticion (sanitizados)',
`request_body` TEXT NULL COMMENT 'Body de la peticion (cifrado)',
`response_body` TEXT NULL COMMENT 'Body de la respuesta (cifrado)',
`response_time_ms` INT UNSIGNED NULL COMMENT 'Tiempo de respuesta en ms',
`ip_address` VARCHAR(45) NOT NULL,
`user_agent` VARCHAR(500) NULL,
`error_code` VARCHAR(50) NULL,
`error_message` TEXT NULL,
`stack_trace` TEXT NULL COMMENT 'Solo en ambiente desarrollo',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_log_uuid` (`log_uuid`),
INDEX `idx_transaction_id` (`transaction_id`),
INDEX `idx_log_type` (`log_type`),
INDEX `idx_action` (`action`),
INDEX `idx_created_at` (`created_at`),
INDEX `idx_ip_address` (`ip_address`),
CONSTRAINT `fk_logs_transaction` FOREIGN KEY (`transaction_id`)
REFERENCES `transactions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Logs detallados de todas las operaciones';
-- ============================================================================
-- TABLA: audit_trail - Auditoria completa del sistema (Compliance)
-- ============================================================================
CREATE TABLE `audit_trail` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`audit_uuid` CHAR(36) NOT NULL,
`entity_type` VARCHAR(50) NOT NULL COMMENT 'Tipo de entidad afectada',
`entity_id` VARCHAR(50) NULL COMMENT 'ID de la entidad',
`action` VARCHAR(30) NOT NULL,
`actor_type` VARCHAR(20) NOT NULL DEFAULT 'system',
`actor_id` VARCHAR(100) NULL,
`actor_ip` VARCHAR(45) NOT NULL,
`actor_user_agent` VARCHAR(500) NULL,
`old_values` TEXT NULL COMMENT 'Valores anteriores (cifrado si sensible)',
`new_values` TEXT NULL COMMENT 'Valores nuevos (cifrado si sensible)',
`description` VARCHAR(500) NULL,
`request_id` CHAR(36) NULL COMMENT 'ID unico de la peticion',
`session_id` VARCHAR(100) NULL,
`risk_level` VARCHAR(20) DEFAULT 'low',
`is_suspicious` TINYINT(1) DEFAULT 0,
`metadata` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_audit_uuid` (`audit_uuid`),
INDEX `idx_entity` (`entity_type`, `entity_id`),
INDEX `idx_action` (`action`),
INDEX `idx_actor` (`actor_type`, `actor_id`),
INDEX `idx_created_at` (`created_at`),
INDEX `idx_risk_level` (`risk_level`),
INDEX `idx_is_suspicious` (`is_suspicious`),
INDEX `idx_request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Auditoria completa - Cumplimiento normativo bancario';
-- ============================================================================
-- TABLA: security_events - Eventos de seguridad
-- ============================================================================
CREATE TABLE `security_events` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`event_uuid` CHAR(36) NOT NULL,
`event_type` VARCHAR(50) NOT NULL,
`severity` VARCHAR(20) NOT NULL DEFAULT 'low',
`source_ip` VARCHAR(45) NOT NULL,
`target_resource` VARCHAR(255) NULL,
`client_id` BIGINT UNSIGNED NULL,
`description` TEXT NOT NULL,
`raw_request` TEXT NULL COMMENT 'Peticion completa (para analisis)',
`blocked` TINYINT(1) DEFAULT 0 COMMENT 'Si se bloqueo la peticion',
`reported` TINYINT(1) DEFAULT 0 COMMENT 'Si se reporto a seguridad',
`resolved` TINYINT(1) DEFAULT 0,
`resolved_at` DATETIME NULL,
`resolved_by` VARCHAR(100) NULL,
`resolution_notes` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_event_uuid` (`event_uuid`),
INDEX `idx_event_type` (`event_type`),
INDEX `idx_severity` (`severity`),
INDEX `idx_source_ip` (`source_ip`),
INDEX `idx_client_id` (`client_id`),
INDEX `idx_created_at` (`created_at`),
INDEX `idx_resolved` (`resolved`),
CONSTRAINT `fk_security_client` FOREIGN KEY (`client_id`)
REFERENCES `api_clients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registro de eventos de seguridad';
-- ============================================================================
-- TABLA: rate_limit_tracking - Control de limite de peticiones (Anti-DDoS)
-- ============================================================================
CREATE TABLE `rate_limit_tracking` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`identifier` VARCHAR(100) NOT NULL COMMENT 'IP o API Key',
`identifier_type` VARCHAR(20) NOT NULL,
`endpoint` VARCHAR(255) NOT NULL,
`request_count` INT UNSIGNED DEFAULT 1,
`window_start` DATETIME NOT NULL,
`window_end` DATETIME NOT NULL,
`is_blocked` TINYINT(1) DEFAULT 0,
`blocked_until` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_identifier` (`identifier`),
INDEX `idx_is_blocked` (`is_blocked`),
INDEX `idx_window` (`window_start`, `window_end`),
INDEX `idx_blocked_until` (`blocked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Control de rate limiting para prevencion DDoS';
-- ============================================================================
-- TABLA: blocked_ips - IPs bloqueadas
-- ============================================================================
CREATE TABLE `blocked_ips` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`ip_address` VARCHAR(45) NOT NULL,
`reason` VARCHAR(255) NOT NULL,
`blocked_by` VARCHAR(100) DEFAULT 'system',
`is_permanent` TINYINT(1) DEFAULT 0,
`expires_at` DATETIME NULL,
`request_count_at_block` INT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_ip_address` (`ip_address`),
INDEX `idx_expires_at` (`expires_at`),
INDEX `idx_is_permanent` (`is_permanent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lista de IPs bloqueadas';
-- ============================================================================
-- TABLA: webhook_deliveries - Entregas de webhooks
-- ============================================================================
CREATE TABLE `webhook_deliveries` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`delivery_uuid` CHAR(36) NOT NULL,
`transaction_id` BIGINT UNSIGNED NOT NULL,
`client_id` BIGINT UNSIGNED NOT NULL,
`webhook_url` VARCHAR(500) NOT NULL,
`payload` TEXT NOT NULL COMMENT 'Payload enviado (cifrado)',
`signature` VARCHAR(128) NOT NULL COMMENT 'Firma HMAC del payload',
`http_status` SMALLINT UNSIGNED NULL,
`response_body` TEXT NULL,
`response_time_ms` INT UNSIGNED NULL,
`attempt_number` TINYINT UNSIGNED DEFAULT 1,
`max_attempts` TINYINT UNSIGNED DEFAULT 5,
`status` VARCHAR(20) DEFAULT 'pending',
`error_message` TEXT NULL,
`next_retry_at` DATETIME NULL,
`delivered_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_delivery_uuid` (`delivery_uuid`),
INDEX `idx_transaction_id` (`transaction_id`),
INDEX `idx_client_id` (`client_id`),
INDEX `idx_status` (`status`),
INDEX `idx_next_retry` (`next_retry_at`),
CONSTRAINT `fk_webhook_transaction` FOREIGN KEY (`transaction_id`)
REFERENCES `transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_webhook_client` FOREIGN KEY (`client_id`)
REFERENCES `api_clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registro de entregas de webhooks';
-- ============================================================================
-- TABLA: refunds - Reembolsos
-- ============================================================================
CREATE TABLE `refunds` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`refund_uuid` CHAR(36) NOT NULL,
`transaction_id` BIGINT UNSIGNED NOT NULL,
`epayco_refund_id` VARCHAR(50) NULL,
`amount` DECIMAL(15,2) NOT NULL,
`reason` VARCHAR(500) NOT NULL,
`status` VARCHAR(20) DEFAULT 'pending',
`status_message` VARCHAR(255) NULL,
`requested_by` VARCHAR(100) NOT NULL,
`approved_by` VARCHAR(100) NULL,
`processed_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_refund_uuid` (`refund_uuid`),
INDEX `idx_transaction_id` (`transaction_id`),
INDEX `idx_status` (`status`),
CONSTRAINT `fk_refund_transaction` FOREIGN KEY (`transaction_id`)
REFERENCES `transactions` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registro de reembolsos';
-- ============================================================================
-- TABLA: daily_reconciliation - Conciliacion diaria
-- ============================================================================
CREATE TABLE `daily_reconciliation` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`reconciliation_date` DATE NOT NULL,
`client_id` BIGINT UNSIGNED NULL COMMENT 'NULL para totales generales',
`total_transactions` INT UNSIGNED DEFAULT 0,
`approved_count` INT UNSIGNED DEFAULT 0,
`rejected_count` INT UNSIGNED DEFAULT 0,
`pending_count` INT UNSIGNED DEFAULT 0,
`total_amount` DECIMAL(18,2) DEFAULT 0.00,
`approved_amount` DECIMAL(18,2) DEFAULT 0.00,
`refunded_amount` DECIMAL(18,2) DEFAULT 0.00,
`net_amount` DECIMAL(18,2) DEFAULT 0.00,
`commission_amount` DECIMAL(18,2) DEFAULT 0.00,
`status` VARCHAR(20) DEFAULT 'pending',
`discrepancy_notes` TEXT NULL,
`reconciled_by` VARCHAR(100) NULL,
`reconciled_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_reconciliation_date` (`reconciliation_date`),
INDEX `idx_status` (`status`),
CONSTRAINT `fk_reconciliation_client` FOREIGN KEY (`client_id`)
REFERENCES `api_clients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Conciliacion diaria de transacciones';
-- ============================================================================
-- TABLA: system_config - Configuracion del sistema
-- ============================================================================
CREATE TABLE `system_config` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`config_key` VARCHAR(100) NOT NULL,
`config_value` TEXT NOT NULL,
`config_type` VARCHAR(20) DEFAULT 'string',
`is_encrypted` TINYINT(1) DEFAULT 0,
`description` VARCHAR(255) NULL,
`updated_by` VARCHAR(100) NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuracion del sistema';
SET FOREIGN_KEY_CHECKS = 1;
-- ============================================================================
-- VISTAS PARA REPORTES
-- ============================================================================
CREATE OR REPLACE VIEW `v_daily_transaction_summary` AS
SELECT
DATE(t.created_at) as transaction_date,
c.client_name,
COUNT(*) as total_transactions,
SUM(CASE WHEN t.status = 'approved' THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN t.status = 'rejected' THEN 1 ELSE 0 END) as rejected,
SUM(CASE WHEN t.status = 'pending' THEN 1 ELSE 0 END) as pending,
SUM(CASE WHEN t.status = 'approved' THEN t.amount ELSE 0 END) as approved_amount,
SUM(t.amount) as total_amount
FROM transactions t
JOIN api_clients c ON t.client_id = c.id
GROUP BY DATE(t.created_at), c.client_name
ORDER BY transaction_date DESC;
CREATE OR REPLACE VIEW `v_recent_security_events` AS
SELECT
se.event_uuid,
se.event_type,
se.severity,
se.source_ip,
c.client_name,
se.description,
se.blocked,
se.created_at
FROM security_events se
LEFT JOIN api_clients c ON se.client_id = c.id
WHERE se.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY se.created_at DESC;
-- ============================================================================
-- PROCEDIMIENTOS ALMACENADOS
-- ============================================================================
DELIMITER //
-- Procedimiento: Limpiar rate limits expirados
-- Ejecutar via cron job de cPanel cada hora
CREATE PROCEDURE `sp_cleanup_rate_limits`()
BEGIN
DELETE FROM rate_limit_tracking
WHERE window_end < DATE_SUB(NOW(), INTERVAL 1 HOUR);
UPDATE blocked_ips
SET is_permanent = 0
WHERE expires_at IS NOT NULL AND expires_at < NOW() AND is_permanent = 0;
DELETE FROM blocked_ips
WHERE is_permanent = 0 AND expires_at < NOW();
END //
-- Procedimiento: Generar reporte de conciliacion diaria
-- Ejecutar via cron job de cPanel diariamente a las 2 AM
CREATE PROCEDURE `sp_generate_daily_reconciliation`(IN p_date DATE)
BEGIN
DELETE FROM daily_reconciliation WHERE reconciliation_date = p_date;
INSERT INTO daily_reconciliation (
reconciliation_date, client_id, total_transactions,
approved_count, rejected_count, pending_count,
total_amount, approved_amount, net_amount, status
)
SELECT
p_date,
client_id,
COUNT(*),
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END),
SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END),
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END),
SUM(amount),
SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END),
SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END) -
COALESCE((SELECT SUM(r.amount) FROM refunds r
JOIN transactions t2 ON r.transaction_id = t2.id
WHERE DATE(r.created_at) = p_date
AND t2.client_id = transactions.client_id
AND r.status = 'approved'), 0),
'pending'
FROM transactions
WHERE DATE(created_at) = p_date
GROUP BY client_id;
INSERT INTO daily_reconciliation (
reconciliation_date, client_id, total_transactions,
approved_count, rejected_count, pending_count,
total_amount, approved_amount, net_amount, status
)
SELECT
p_date,
NULL,
COUNT(*),
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END),
SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END),
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END),
SUM(amount),
SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END),
SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END),
'pending'
FROM transactions
WHERE DATE(created_at) = p_date;
END //
DELIMITER ;
-- ============================================================================
-- NOTA SOBRE EVENTOS PROGRAMADOS (cPanel)
-- ============================================================================
-- En hosting compartido NO se puede usar SET GLOBAL event_scheduler = ON
-- ni CREATE EVENT. En su lugar, configurar cron jobs en cPanel:
--
-- Limpiar rate limits cada hora:
-- 0 * * * * /usr/bin/mysql -u USUARIO -pCONTRASENA BASEDATOS -e "CALL sp_cleanup_rate_limits();"
--
-- Conciliacion diaria a las 2 AM:
-- 0 2 * * * /usr/bin/mysql -u USUARIO -pCONTRASENA BASEDATOS -e "CALL sp_generate_daily_reconciliation(DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY));"
--
-- Alternativa con PHP (si mysql CLI no esta disponible):
-- 0 * * * * /usr/local/bin/php /home/USUARIO/transportes-suroeste/cron/cleanup.php
-- 0 2 * * * /usr/local/bin/php /home/USUARIO/transportes-suroeste/cron/reconciliation.php
-- ============================================================================
-- ============================================================================
-- TRIGGERS PARA AUDITORIA AUTOMATICA
-- ============================================================================
DELIMITER //
CREATE TRIGGER `trg_transaction_status_change`
AFTER UPDATE ON `transactions`
FOR EACH ROW
BEGIN
IF OLD.status != NEW.status THEN
INSERT INTO transaction_status_history (
transaction_id, previous_status, new_status,
status_code, status_message, changed_by
) VALUES (
NEW.id, OLD.status, NEW.status,
NEW.status_code, NEW.status_message, 'system'
);
END IF;
END //
CREATE TRIGGER `trg_client_access`
BEFORE UPDATE ON `api_clients`
FOR EACH ROW
BEGIN
IF NEW.last_access_at != OLD.last_access_at OR
(OLD.last_access_at IS NULL AND NEW.last_access_at IS NOT NULL) THEN
SET NEW.updated_at = CURRENT_TIMESTAMP(6);
END IF;
END //
DELIMITER ;
-- ============================================================================
-- INDICES ADICIONALES PARA RENDIMIENTO
-- ============================================================================
CREATE INDEX `idx_trans_client_status_date` ON `transactions` (`client_id`, `status`, `created_at`);
CREATE INDEX `idx_audit_date_action` ON `audit_trail` (`created_at`, `action`);
CREATE INDEX `idx_security_date_type` ON `security_events` (`created_at`, `event_type`);
-- ============================================================================
-- DATOS INICIALES
-- ============================================================================
INSERT INTO `system_config` (`config_key`, `config_value`, `config_type`, `description`) VALUES
('maintenance_mode', 'false', 'boolean', 'Modo mantenimiento activo'),
('max_transaction_amount', '10000000', 'integer', 'Monto maximo por transaccion en centavos'),
('min_transaction_amount', '1000', 'integer', 'Monto minimo por transaccion en centavos'),
('transaction_timeout_seconds', '300', 'integer', 'Tiempo de expiracion de transaccion'),
('webhook_max_retries', '5', 'integer', 'Intentos maximos de webhook'),
('rate_limit_requests_per_minute', '100', 'integer', 'Limite de peticiones por minuto'),
('rate_limit_ban_duration_minutes', '60', 'integer', 'Duracion del baneo por exceder limite');
SET FOREIGN_KEY_CHECKS = 1;
-- ============================================================================
-- FIN DEL SCRIPT
-- ============================================================================