-- Tabla de bancos
CREATE TABLE banks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) UNIQUE NOT NULL,
    base_url VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de configuración de APIs por banco
CREATE TABLE bank_apis (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_id INT NOT NULL,
    api_name VARCHAR(100) NOT NULL, -- 'payment_reconciliation', 'movement_query', 'payment_notification'
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL DEFAULT 'POST',
    api_key VARCHAR(255),
    headers JSON,
    parameters_mapping JSON, -- Mapeo de parámetros entre nuestro formato y el del banco
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_id) REFERENCES banks(id),
    UNIQUE KEY unique_bank_api (bank_id, api_name)
);

-- Tabla de transacciones
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_uuid VARCHAR(36) UNIQUE NOT NULL,
    bank_id INT NOT NULL,
    external_reference VARCHAR(100), -- Referencia del banco
    internal_reference VARCHAR(100), -- Nuestra referencia interna
    payer_document VARCHAR(20),
    payer_phone VARCHAR(20),
    beneficiary_phone VARCHAR(20),
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'VES',
    transaction_date DATETIME,
    bank_origin VARCHAR(10),
    status ENUM('pending', 'completed', 'failed', 'reconciled', 'disputed') DEFAULT 'pending',
    transaction_type ENUM('payment_mobile', 'transfer', 'other') DEFAULT 'payment_mobile',
    raw_request JSON,
    raw_response JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_id) REFERENCES banks(id),
    INDEX idx_transaction_uuid (transaction_uuid),
    INDEX idx_external_reference (external_reference),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Tabla de logs de notificaciones webhook
CREATE TABLE webhook_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT,
    bank_id INT NOT NULL,
    payload JSON NOT NULL,
    headers JSON,
    ip_address VARCHAR(45),
    response_sent JSON,
    status_code INT,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id),
    FOREIGN KEY (bank_id) REFERENCES banks(id)
);

-- Tabla de API keys para seguridad de nuestra API
CREATE TABLE api_keys (
    id INT PRIMARY KEY AUTO_INCREMENT,
    key_name VARCHAR(100) NOT NULL,
    api_key VARCHAR(255) UNIQUE NOT NULL,
    secret_key VARCHAR(255) NOT NULL,
    system_id VARCHAR(100), -- Identificador del sistema que consume nuestra API
    is_active BOOLEAN DEFAULT true,
    permissions JSON, -- Permisos específicos
    rate_limit_per_minute INT DEFAULT 60,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    INDEX idx_api_key (api_key)
);

-- Tabla de configuración del sistema
CREATE TABLE system_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSON,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);