-- =============================================================
-- ARCHIVO: 25-payment-terminals.sql
-- DESCRIPCION: Terminales de pago TPV (MercadoPago, Clip)
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-25
-- =============================================================
-- Basado en: michangarrito INT-004 (MercadoPago) e INT-005 (Clip)
-- =============================================================

-- =====================
-- SCHEMA: payment_terminals
-- =====================
CREATE SCHEMA IF NOT EXISTS payment_terminals;

-- =====================
-- ENUMS
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'terminal_provider') THEN
        CREATE TYPE payment_terminals.terminal_provider AS ENUM (
            'mercadopago',
            'clip',
            'stripe_terminal'
        );
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'terminal_payment_status') THEN
        CREATE TYPE payment_terminals.terminal_payment_status AS ENUM (
            'pending',
            'processing',
            'approved',
            'authorized',
            'in_process',
            'rejected',
            'refunded',
            'partially_refunded',
            'cancelled',
            'charged_back'
        );
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'payment_method_type') THEN
        CREATE TYPE payment_terminals.payment_method_type AS ENUM (
            'card',
            'qr',
            'link',
            'cash',
            'bank_transfer'
        );
    END IF;
END$$;

-- =====================
-- TABLA: tenant_terminal_configs
-- Credenciales de proveedores por tenant (no por branch)
-- =====================
CREATE TABLE IF NOT EXISTS payment_terminals.tenant_terminal_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Proveedor
    provider payment_terminals.terminal_provider NOT NULL,
    name VARCHAR(100) NOT NULL,

    -- Credenciales (encriptadas AES-256)
    credentials JSONB NOT NULL DEFAULT '{}',
    -- MercadoPago: { access_token, public_key, collector_id }
    -- Clip: { api_key, secret_key, merchant_id }

    -- Configuración específica del proveedor
    config JSONB DEFAULT '{}',
    -- MercadoPago: { statement_descriptor, notification_url }
    -- Clip: { default_currency, webhook_secret }

    -- Estado
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    verification_error TEXT,
    verified_at TIMESTAMPTZ,

    -- Límites
    daily_limit DECIMAL(12,2),
    monthly_limit DECIMAL(12,2),
    transaction_limit DECIMAL(12,2),

    -- Webhook
    webhook_url VARCHAR(500),
    webhook_secret VARCHAR(255),

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),

    UNIQUE(tenant_id, provider, name)
);

-- =====================
-- TABLA: terminal_payments
-- Transacciones procesadas por terminales TPV
-- =====================
CREATE TABLE IF NOT EXISTS payment_terminals.terminal_payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Configuración usada
    config_id UUID REFERENCES payment_terminals.tenant_terminal_configs(id),
    branch_terminal_id UUID REFERENCES core.branch_payment_terminals(id),

    -- Proveedor y referencia externa
    provider payment_terminals.terminal_provider NOT NULL,
    external_id VARCHAR(255),
    external_status VARCHAR(50),

    -- Monto
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'MXN',

    -- Estado
    status payment_terminals.terminal_payment_status DEFAULT 'pending',

    -- Método de pago
    payment_method payment_terminals.payment_method_type DEFAULT 'card',

    -- Datos de tarjeta (parciales, no sensibles)
    card_last_four VARCHAR(4),
    card_brand VARCHAR(20),
    card_type VARCHAR(20), -- credit, debit, prepaid

    -- Cliente
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    customer_name VARCHAR(200),

    -- Descripción
    description TEXT,
    statement_descriptor VARCHAR(50),

    -- Referencia interna (orden, venta, etc.)
    reference_type VARCHAR(50), -- sale, order, invoice
    reference_id UUID,

    -- Comisiones
    fee_amount DECIMAL(10,4),
    fee_details JSONB,
    net_amount DECIMAL(12,2),

    -- Reembolso
    refunded_amount DECIMAL(12,2) DEFAULT 0,
    refund_reason TEXT,

    -- Respuesta del proveedor
    provider_response JSONB,

    -- Error
    error_code VARCHAR(50),
    error_message TEXT,

    -- Timestamps
    processed_at TIMESTAMPTZ,
    refunded_at TIMESTAMPTZ,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id)
);

-- =====================
-- TABLA: terminal_webhook_events
-- Eventos de webhook recibidos
-- =====================
CREATE TABLE IF NOT EXISTS payment_terminals.terminal_webhook_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

    -- Proveedor y evento
    provider payment_terminals.terminal_provider NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_id VARCHAR(255), -- ID del evento en el proveedor

    -- Referencia
    payment_id UUID REFERENCES payment_terminals.terminal_payments(id),
    external_id VARCHAR(255),

    -- Payload completo
    payload JSONB NOT NULL,
    headers JSONB,

    -- Validación de firma
    signature_valid BOOLEAN,

    -- Procesamiento
    processed BOOLEAN DEFAULT false,
    processed_at TIMESTAMPTZ,
    processing_error TEXT,
    retry_count INTEGER DEFAULT 0,

    -- Idempotencia
    idempotency_key VARCHAR(255),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(provider, event_id)
);

-- =====================
-- TABLA: terminal_retry_queue
-- Cola de reintentos para operaciones fallidas
-- =====================
CREATE TABLE IF NOT EXISTS payment_terminals.terminal_retry_queue (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

    -- Referencia
    payment_id UUID REFERENCES payment_terminals.terminal_payments(id),
    provider payment_terminals.terminal_provider NOT NULL,

    -- Operación
    operation VARCHAR(50) NOT NULL, -- create_payment, refund, check_status
    payload JSONB NOT NULL,

    -- Reintentos
    attempts INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 5,
    next_retry_at TIMESTAMPTZ,

    -- Error
    last_error TEXT,
    last_error_code VARCHAR(50),

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, processing, completed, failed
    completed_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- TABLA: terminal_payment_links
-- Links de pago generados
-- =====================
CREATE TABLE IF NOT EXISTS payment_terminals.terminal_payment_links (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    config_id UUID REFERENCES payment_terminals.tenant_terminal_configs(id),

    -- Proveedor y referencia
    provider payment_terminals.terminal_provider NOT NULL,
    external_id VARCHAR(255),

    -- URL del link
    url VARCHAR(1000) NOT NULL,
    short_url VARCHAR(500),

    -- Monto
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'MXN',

    -- Descripción
    title VARCHAR(200),
    description TEXT,

    -- Estado
    status VARCHAR(30) DEFAULT 'active', -- active, paid, expired, cancelled

    -- Pago asociado (cuando se paga)
    payment_id UUID REFERENCES payment_terminals.terminal_payments(id),

    -- Expiración
    expires_at TIMESTAMPTZ,

    -- Referencia interna
    reference_type VARCHAR(50),
    reference_id UUID,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id)
);

-- =====================
-- INDICES
-- =====================

-- tenant_terminal_configs
CREATE INDEX IF NOT EXISTS idx_terminal_configs_tenant
    ON payment_terminals.tenant_terminal_configs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_terminal_configs_provider
    ON payment_terminals.tenant_terminal_configs(provider);
CREATE INDEX IF NOT EXISTS idx_terminal_configs_active
    ON payment_terminals.tenant_terminal_configs(is_active) WHERE is_active = true;

-- terminal_payments
CREATE INDEX IF NOT EXISTS idx_terminal_payments_tenant
    ON payment_terminals.terminal_payments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_terminal_payments_provider
    ON payment_terminals.terminal_payments(provider);
CREATE INDEX IF NOT EXISTS idx_terminal_payments_status
    ON payment_terminals.terminal_payments(status);
CREATE INDEX IF NOT EXISTS idx_terminal_payments_external
    ON payment_terminals.terminal_payments(external_id);
CREATE INDEX IF NOT EXISTS idx_terminal_payments_reference
    ON payment_terminals.terminal_payments(reference_type, reference_id);
CREATE INDEX IF NOT EXISTS idx_terminal_payments_created
    ON payment_terminals.terminal_payments(created_at DESC);

-- terminal_webhook_events
CREATE INDEX IF NOT EXISTS idx_webhook_events_tenant
    ON payment_terminals.terminal_webhook_events(tenant_id);
CREATE INDEX IF NOT EXISTS idx_webhook_events_provider
    ON payment_terminals.terminal_webhook_events(provider);
CREATE INDEX IF NOT EXISTS idx_webhook_events_processed
    ON payment_terminals.terminal_webhook_events(processed, created_at)
    WHERE processed = false;
CREATE INDEX IF NOT EXISTS idx_webhook_events_idempotency
    ON payment_terminals.terminal_webhook_events(idempotency_key);

-- terminal_retry_queue
CREATE INDEX IF NOT EXISTS idx_retry_queue_next
    ON payment_terminals.terminal_retry_queue(next_retry_at)
    WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_retry_queue_tenant
    ON payment_terminals.terminal_retry_queue(tenant_id);

-- terminal_payment_links
CREATE INDEX IF NOT EXISTS idx_payment_links_tenant
    ON payment_terminals.terminal_payment_links(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payment_links_status
    ON payment_terminals.terminal_payment_links(status);
CREATE INDEX IF NOT EXISTS idx_payment_links_expires
    ON payment_terminals.terminal_payment_links(expires_at)
    WHERE status = 'active';

-- =====================
-- RLS POLICIES
-- =====================

ALTER TABLE payment_terminals.tenant_terminal_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_terminals.terminal_payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_terminals.terminal_webhook_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_terminals.terminal_retry_queue ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_terminals.terminal_payment_links ENABLE ROW LEVEL SECURITY;

-- Políticas de aislamiento por tenant
CREATE POLICY tenant_configs_isolation ON payment_terminals.tenant_terminal_configs
    USING (tenant_id = current_setting('app.current_tenant', true)::UUID);

CREATE POLICY terminal_payments_isolation ON payment_terminals.terminal_payments
    USING (tenant_id = current_setting('app.current_tenant', true)::UUID);

CREATE POLICY webhook_events_isolation ON payment_terminals.terminal_webhook_events
    USING (tenant_id = current_setting('app.current_tenant', true)::UUID);

CREATE POLICY retry_queue_isolation ON payment_terminals.terminal_retry_queue
    USING (tenant_id = current_setting('app.current_tenant', true)::UUID);

CREATE POLICY payment_links_isolation ON payment_terminals.terminal_payment_links
    USING (tenant_id = current_setting('app.current_tenant', true)::UUID);

-- =====================
-- FUNCIONES
-- =====================

-- Función para calcular siguiente retry con backoff exponencial
CREATE OR REPLACE FUNCTION payment_terminals.calculate_next_retry(
    p_attempts INTEGER,
    p_base_delay_seconds INTEGER DEFAULT 60
) RETURNS TIMESTAMPTZ AS $$
BEGIN
    -- Backoff exponencial: base * 2^attempts
    -- Ejemplo con base 60s: 60s, 120s, 240s, 480s, 960s
    RETURN NOW() + (p_base_delay_seconds * POWER(2, p_attempts)) * INTERVAL '1 second';
END;
$$ LANGUAGE plpgsql;

-- Función para marcar pagos expirados
CREATE OR REPLACE FUNCTION payment_terminals.expire_old_pending_payments()
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    UPDATE payment_terminals.terminal_payments
    SET status = 'cancelled',
        updated_at = NOW(),
        error_message = 'Payment expired'
    WHERE status = 'pending'
      AND created_at < NOW() - INTERVAL '24 hours';

    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- Función para expirar links de pago
CREATE OR REPLACE FUNCTION payment_terminals.expire_payment_links()
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    UPDATE payment_terminals.terminal_payment_links
    SET status = 'expired',
        updated_at = NOW()
    WHERE status = 'active'
      AND expires_at IS NOT NULL
      AND expires_at < NOW();

    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- =====================
-- COMENTARIOS
-- =====================

COMMENT ON SCHEMA payment_terminals IS 'Terminales de pago TPV (MercadoPago, Clip, Stripe Terminal)';
COMMENT ON TABLE payment_terminals.tenant_terminal_configs IS 'Configuración de credenciales de proveedores TPV por tenant';
COMMENT ON TABLE payment_terminals.terminal_payments IS 'Transacciones procesadas por terminales TPV';
COMMENT ON TABLE payment_terminals.terminal_webhook_events IS 'Eventos de webhook recibidos de proveedores TPV';
COMMENT ON TABLE payment_terminals.terminal_retry_queue IS 'Cola de reintentos para operaciones fallidas';
COMMENT ON TABLE payment_terminals.terminal_payment_links IS 'Links de pago generados';

-- =============================================================
-- FIN DEL ARCHIVO
-- =============================================================