erp-core-database-v2/ddl/55-financial-payments.sql

-- =============================================================
-- ARCHIVO: 55-financial-payments.sql
-- DESCRIPCION: Pagos contables (cobros y pagos)
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-20
-- DEPENDE DE: 50-financial-schema.sql, 51-financial-accounts.sql, 52-financial-journals.sql, 53-financial-entries.sql
-- NOTA: Este modulo es para pagos desde perspectiva CONTABLE.
--       Para pagos operativos ver 24-invoices.sql (schema billing)
-- =============================================================

-- =====================
-- TABLA: payments
-- Pagos contables (cobros entrantes y pagos salientes)
-- =====================
CREATE TABLE IF NOT EXISTS financial.payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    company_id UUID,

    -- Partner (cliente o proveedor)
    partner_id UUID NOT NULL,  -- FK a partners.partners

    -- Tipo de pago
    payment_type financial.payment_type_enum NOT NULL,  -- inbound (cobro), outbound (pago)

    -- Metodo de pago
    payment_method financial.payment_method_enum NOT NULL,

    -- Monto
    amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),

    -- Moneda
    currency_id UUID,  -- FK a catalogo de monedas

    -- Fecha de pago
    payment_date DATE NOT NULL,

    -- Referencia
    ref VARCHAR(255),  -- Numero de cheque, referencia bancaria, etc.

    -- Estado
    status financial.payment_status_enum DEFAULT 'draft',

    -- Relacion con contabilidad
    journal_id UUID REFERENCES financial.journals(id) ON DELETE RESTRICT,
    journal_entry_id UUID REFERENCES financial.journal_entries(id) ON DELETE SET NULL,

    -- Notas
    notes TEXT,

    -- Audit columns
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),

    -- Publicacion/Contabilizacion
    posted_at TIMESTAMPTZ,
    posted_by UUID REFERENCES auth.users(id)
);

-- Indices para payments
CREATE INDEX IF NOT EXISTS idx_financial_payments_tenant ON financial.payments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_payments_company ON financial.payments(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_payments_partner ON financial.payments(partner_id);
CREATE INDEX IF NOT EXISTS idx_financial_payments_type ON financial.payments(payment_type);
CREATE INDEX IF NOT EXISTS idx_financial_payments_method ON financial.payments(payment_method);
CREATE INDEX IF NOT EXISTS idx_financial_payments_date ON financial.payments(payment_date);
CREATE INDEX IF NOT EXISTS idx_financial_payments_status ON financial.payments(status);
CREATE INDEX IF NOT EXISTS idx_financial_payments_journal ON financial.payments(journal_id);
CREATE INDEX IF NOT EXISTS idx_financial_payments_entry ON financial.payments(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_financial_payments_ref ON financial.payments(ref) WHERE ref IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_financial_payments_posted ON financial.payments(tenant_id, status) WHERE status = 'posted';
CREATE INDEX IF NOT EXISTS idx_financial_payments_inbound ON financial.payments(tenant_id, partner_id, payment_type) WHERE payment_type = 'inbound';
CREATE INDEX IF NOT EXISTS idx_financial_payments_outbound ON financial.payments(tenant_id, partner_id, payment_type) WHERE payment_type = 'outbound';
CREATE INDEX IF NOT EXISTS idx_financial_payments_date_range ON financial.payments(tenant_id, payment_date, status);

-- =====================
-- TABLA: payment_invoice_allocations
-- Aplicacion de pagos a facturas
-- =====================
CREATE TABLE IF NOT EXISTS financial.payment_invoice_allocations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Pago
    payment_id UUID NOT NULL REFERENCES financial.payments(id) ON DELETE CASCADE,

    -- Factura
    invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,

    -- Monto aplicado a esta factura
    amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),

    -- Fecha de aplicacion
    allocation_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Audit columns
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),

    -- Un pago solo puede aplicarse una vez a cada factura
    UNIQUE(payment_id, invoice_id)
);

-- Indices para payment_invoice_allocations
CREATE INDEX IF NOT EXISTS idx_financial_payment_allocations_payment ON financial.payment_invoice_allocations(payment_id);
CREATE INDEX IF NOT EXISTS idx_financial_payment_allocations_invoice ON financial.payment_invoice_allocations(invoice_id);
CREATE INDEX IF NOT EXISTS idx_financial_payment_allocations_date ON financial.payment_invoice_allocations(allocation_date);

-- =====================
-- FUNCION: Actualizar amount_paid en factura
-- =====================
CREATE OR REPLACE FUNCTION financial.update_invoice_amount_paid()
RETURNS TRIGGER AS $$
DECLARE
    v_invoice_id UUID;
    v_total_paid DECIMAL(15, 2);
BEGIN
    -- Determinar la factura afectada
    IF TG_OP = 'DELETE' THEN
        v_invoice_id := OLD.invoice_id;
    ELSE
        v_invoice_id := NEW.invoice_id;
    END IF;

    -- Calcular total pagado para la factura
    SELECT COALESCE(SUM(amount), 0)
    INTO v_total_paid
    FROM financial.payment_invoice_allocations
    WHERE invoice_id = v_invoice_id;

    -- Actualizar factura
    UPDATE financial.invoices
    SET
        amount_paid = v_total_paid,
        status = CASE
            WHEN v_total_paid >= amount_total THEN 'paid'::financial.invoice_status_enum
            WHEN v_total_paid > 0 THEN 'open'::financial.invoice_status_enum
            ELSE status
        END,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = v_invoice_id;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Trigger para actualizar amount_paid automaticamente
DROP TRIGGER IF EXISTS trg_update_invoice_amount_paid ON financial.payment_invoice_allocations;
CREATE TRIGGER trg_update_invoice_amount_paid
    AFTER INSERT OR UPDATE OR DELETE ON financial.payment_invoice_allocations
    FOR EACH ROW
    EXECUTE FUNCTION financial.update_invoice_amount_paid();

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE financial.payments IS 'Pagos contables (cobros y pagos a proveedores)';
COMMENT ON COLUMN financial.payments.payment_type IS 'Tipo: inbound (cobro de cliente), outbound (pago a proveedor)';
COMMENT ON COLUMN financial.payments.payment_method IS 'Metodo: cash, bank_transfer, check, card, other';
COMMENT ON COLUMN financial.payments.amount IS 'Monto del pago (siempre positivo)';
COMMENT ON COLUMN financial.payments.ref IS 'Referencia: numero de cheque, referencia bancaria, etc.';
COMMENT ON COLUMN financial.payments.status IS 'Estado: draft, posted (contabilizado), reconciled, cancelled';
COMMENT ON COLUMN financial.payments.journal_entry_id IS 'Asiento contable generado al publicar el pago';
COMMENT ON COLUMN financial.payments.posted_at IS 'Fecha y hora de publicacion/contabilizacion';

COMMENT ON TABLE financial.payment_invoice_allocations IS 'Aplicacion de pagos a facturas especificas';
COMMENT ON COLUMN financial.payment_invoice_allocations.amount IS 'Monto del pago aplicado a esta factura';
COMMENT ON COLUMN financial.payment_invoice_allocations.allocation_date IS 'Fecha de aplicacion del pago';

COMMENT ON FUNCTION financial.update_invoice_amount_paid() IS 'Actualiza automaticamente amount_paid en facturas cuando se aplican pagos';