michangarrito/database/schemas/14-codi-spei.sql

-- =============================================================================
-- MICHANGARRITO - 14 CODI/SPEI
-- =============================================================================
-- Pagos CoDi (Cobro Digital) y SPEI (Sistema de Pagos Electronicos)
-- =============================================================================

-- Cuentas virtuales SPEI (CLABE por tenant)
CREATE TABLE IF NOT EXISTS sales.virtual_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    provider VARCHAR(20) NOT NULL DEFAULT 'stp',
    -- stp, arcus, conekta, openpay

    clabe VARCHAR(18) UNIQUE NOT NULL,
    beneficiary_name VARCHAR(100),

    status VARCHAR(20) DEFAULT 'active',
    -- active, suspended, closed

    metadata JSONB,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_virtual_accounts_tenant ON sales.virtual_accounts(tenant_id);
CREATE INDEX idx_virtual_accounts_clabe ON sales.virtual_accounts(clabe);

CREATE TRIGGER update_virtual_accounts_updated_at
    BEFORE UPDATE ON sales.virtual_accounts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Transacciones CoDi
CREATE TABLE IF NOT EXISTS sales.codi_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    qr_data TEXT NOT NULL,
    qr_image_url TEXT,

    amount DECIMAL(10,2) NOT NULL,
    reference VARCHAR(50),
    description VARCHAR(200),

    status VARCHAR(20) DEFAULT 'pending',
    -- pending, confirmed, expired, cancelled

    expires_at TIMESTAMPTZ NOT NULL,
    confirmed_at TIMESTAMPTZ,

    provider_transaction_id VARCHAR(100),
    provider_response JSONB,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_codi_transactions_tenant ON sales.codi_transactions(tenant_id);
CREATE INDEX idx_codi_transactions_sale ON sales.codi_transactions(sale_id);
CREATE INDEX idx_codi_transactions_status ON sales.codi_transactions(status);
CREATE INDEX idx_codi_transactions_reference ON sales.codi_transactions(reference);

CREATE TRIGGER update_codi_transactions_updated_at
    BEFORE UPDATE ON sales.codi_transactions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Transacciones SPEI
CREATE TABLE IF NOT EXISTS sales.spei_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    virtual_account_id UUID REFERENCES sales.virtual_accounts(id),
    sale_id UUID REFERENCES sales.sales(id),

    amount DECIMAL(10,2) NOT NULL,

    sender_clabe VARCHAR(18),
    sender_name VARCHAR(100),
    sender_rfc VARCHAR(13),
    sender_bank VARCHAR(50),

    reference VARCHAR(50),
    description VARCHAR(200),
    tracking_key VARCHAR(50),

    status VARCHAR(20) DEFAULT 'received',
    -- received, reconciled, disputed

    received_at TIMESTAMPTZ NOT NULL,
    reconciled_at TIMESTAMPTZ,

    provider_data JSONB,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_spei_transactions_tenant ON sales.spei_transactions(tenant_id);
CREATE INDEX idx_spei_transactions_account ON sales.spei_transactions(virtual_account_id);
CREATE INDEX idx_spei_transactions_sale ON sales.spei_transactions(sale_id);
CREATE INDEX idx_spei_transactions_tracking ON sales.spei_transactions(tracking_key);

CREATE TRIGGER update_spei_transactions_updated_at
    BEFORE UPDATE ON sales.spei_transactions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Configuracion CoDi/SPEI por tenant
CREATE TABLE IF NOT EXISTS sales.payment_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    codi_enabled BOOLEAN DEFAULT false,
    codi_provider VARCHAR(20),
    codi_merchant_id VARCHAR(100),
    codi_qr_expiry_minutes INTEGER DEFAULT 5,

    spei_enabled BOOLEAN DEFAULT false,
    spei_provider VARCHAR(20),
    spei_auto_reconcile BOOLEAN DEFAULT true,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

CREATE TRIGGER update_payment_config_updated_at
    BEFORE UPDATE ON sales.payment_config
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Funcion para generar referencia unica de CoDi
CREATE OR REPLACE FUNCTION generate_codi_reference(p_tenant_id UUID)
RETURNS TEXT AS $$
DECLARE
    ref TEXT;
BEGIN
    -- Generar referencia: MCH + 6 digitos aleatorios + timestamp
    ref := 'MCH' || to_char(now(), 'YYMMDDHH24MISS') || substr(md5(random()::text), 1, 4);
    RETURN upper(ref);
END;
$$ LANGUAGE plpgsql;

-- Funcion para obtener resumen de pagos CoDi/SPEI del dia
CREATE OR REPLACE FUNCTION get_codi_spei_summary(p_tenant_id UUID, p_date DATE DEFAULT CURRENT_DATE)
RETURNS TABLE (
    codi_count INTEGER,
    codi_total DECIMAL(10,2),
    spei_count INTEGER,
    spei_total DECIMAL(10,2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        (SELECT COUNT(*)::INTEGER FROM sales.codi_transactions
         WHERE tenant_id = p_tenant_id AND status = 'confirmed'
         AND DATE(confirmed_at) = p_date) as codi_count,
        (SELECT COALESCE(SUM(amount), 0) FROM sales.codi_transactions
         WHERE tenant_id = p_tenant_id AND status = 'confirmed'
         AND DATE(confirmed_at) = p_date) as codi_total,
        (SELECT COUNT(*)::INTEGER FROM sales.spei_transactions
         WHERE tenant_id = p_tenant_id
         AND DATE(received_at) = p_date) as spei_count,
        (SELECT COALESCE(SUM(amount), 0) FROM sales.spei_transactions
         WHERE tenant_id = p_tenant_id
         AND DATE(received_at) = p_date) as spei_total;
END;
$$ LANGUAGE plpgsql;