FM / sql

/home/suroeste/public_html/payments.transportessuroeste.com/sql/

/home/suroeste/public_html/payments.transportessuroeste.com/sql

Name	Size	Mode	Actions
database-cpanel.sql	27787	0644	edit dl rm
database.sql	26971	0644	edit dl rm
database_xampp.sql	20973	0644	edit dl rm

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;

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
-- ============================================================================

cmd: