-- Instalación / actualización de tablas para integración con MercadoPago
-- Ejecutar este script sobre la base de datos de clientes.printcenter.digital

-- 1. Tabla de pasarelas de pago
CREATE TABLE IF NOT EXISTS pc_payment_gateways (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    config_json TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Registro inicial para MercadoPago (si no existe)
INSERT INTO pc_payment_gateways (name, code, is_enabled, config_json)
VALUES (
    'MercadoPago',
    'MERCADOPAGO',
    0,
    '{
        "environment":"sandbox",
        "access_token":"",
        "public_key":"",
        "back_urls":{
            "success":"https://clientes.printcenter.digital/pago/mercadopago_success.php",
            "failure":"https://clientes.printcenter.digital/pago/mercadopago_failure.php",
            "pending":"https://clientes.printcenter.digital/pago/mercadopago_pending.php"
        },
        "notification_url":"https://clientes.printcenter.digital/pago/mercadopago_webhook.php"
    }'
)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 3. Tabla de pagos
CREATE TABLE IF NOT EXISTS pc_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    gateway_code VARCHAR(50) NOT NULL,
    preference_id VARCHAR(100) NULL,
    mp_payment_id VARCHAR(100) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'CLP',
    raw_notification_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_order (order_id),
    KEY idx_gateway (gateway_code),
    KEY idx_pref (preference_id),
    KEY idx_mp (mp_payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Ajustes en la tabla de pedidos (ajustar nombre de tabla si es distinto)
--   Este bloque usa ADD COLUMN IF NOT EXISTS para evitar errores por columnas duplicadas.
ALTER TABLE pc_orders
    ADD COLUMN IF NOT EXISTS payment_status VARCHAR(20) NOT NULL DEFAULT 'SIN_PAGO' AFTER total_amount,
    ADD COLUMN IF NOT EXISTS payment_gateway_code VARCHAR(50) NULL AFTER payment_status,
    ADD COLUMN IF NOT EXISTS payment_reference VARCHAR(100) NULL AFTER payment_gateway_code;
