erp-core-database-v2/ddl/57-financial-bank-reconciliation.sql

-- =============================================================
-- ARCHIVO: 57-financial-bank-reconciliation.sql
-- DESCRIPCION: Conciliacion bancaria - extractos, lineas y reglas de match
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-20
-- DEPENDE DE: 50-financial-schema.sql, 51-financial-accounts.sql, 53-financial-entries.sql
-- =============================================================

-- =====================
-- TIPO ENUMERADO: Estado de extracto bancario
-- =====================
DO $$ BEGIN
    CREATE TYPE financial.bank_statement_status_enum AS ENUM (
        'draft',       -- Borrador
        'reconciling', -- En proceso de conciliacion
        'reconciled'   -- Conciliado
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- =====================
-- TIPO ENUMERADO: Tipo de regla de match
-- =====================
DO $$ BEGIN
    CREATE TYPE financial.reconciliation_match_type_enum AS ENUM (
        'exact_amount',      -- Monto exacto
        'reference_contains', -- Referencia contiene texto
        'partner_name'       -- Nombre de partner
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- =====================
-- TABLA: bank_statements
-- Extractos bancarios importados
-- =====================
CREATE TABLE IF NOT EXISTS financial.bank_statements (
    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,

    -- Cuenta bancaria asociada (referencia a cuenta contable tipo banco)
    bank_account_id UUID REFERENCES financial.accounts(id) ON DELETE RESTRICT,

    -- Datos del extracto
    statement_date DATE NOT NULL,
    opening_balance DECIMAL(15, 2) NOT NULL DEFAULT 0,
    closing_balance DECIMAL(15, 2) NOT NULL DEFAULT 0,

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

    -- Importacion
    imported_at TIMESTAMPTZ,
    imported_by UUID REFERENCES auth.users(id),

    -- Conciliacion
    reconciled_at TIMESTAMPTZ,
    reconciled_by UUID REFERENCES auth.users(id),

    -- 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)
);

-- Indices para bank_statements
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_tenant ON financial.bank_statements(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_company ON financial.bank_statements(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_bank_account ON financial.bank_statements(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_date ON financial.bank_statements(statement_date);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_status ON financial.bank_statements(status);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_tenant_date ON financial.bank_statements(tenant_id, statement_date DESC);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_draft ON financial.bank_statements(tenant_id, status) WHERE status = 'draft';
CREATE INDEX IF NOT EXISTS idx_financial_bank_statements_reconciling ON financial.bank_statements(tenant_id, status) WHERE status = 'reconciling';

-- =====================
-- TABLA: bank_statement_lines
-- Lineas de extracto bancario (movimientos)
-- =====================
CREATE TABLE IF NOT EXISTS financial.bank_statement_lines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Relacion con extracto (cascade delete)
    statement_id UUID NOT NULL REFERENCES financial.bank_statements(id) ON DELETE CASCADE,

    -- Multi-tenant (denormalizado para queries rapidas)
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Datos del movimiento
    transaction_date DATE NOT NULL,
    value_date DATE,
    description VARCHAR(500),
    reference VARCHAR(100),
    amount DECIMAL(15, 2) NOT NULL,  -- Positivo = deposito, Negativo = retiro

    -- Estado de conciliacion
    is_reconciled BOOLEAN DEFAULT false,
    reconciled_entry_id UUID REFERENCES financial.journal_entry_lines(id) ON DELETE SET NULL,
    reconciled_at TIMESTAMPTZ,
    reconciled_by UUID REFERENCES auth.users(id),

    -- Partner detectado (automatico o manual)
    partner_id UUID,  -- FK a partners si existe

    -- Notas adicionales
    notes TEXT,

    -- Audit columns
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indices para bank_statement_lines
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_statement ON financial.bank_statement_lines(statement_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_tenant ON financial.bank_statement_lines(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_date ON financial.bank_statement_lines(transaction_date);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_reconciled ON financial.bank_statement_lines(is_reconciled);
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_entry ON financial.bank_statement_lines(reconciled_entry_id) WHERE reconciled_entry_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_partner ON financial.bank_statement_lines(partner_id) WHERE partner_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_reference ON financial.bank_statement_lines(reference) WHERE reference IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_unreconciled ON financial.bank_statement_lines(tenant_id, statement_id) WHERE is_reconciled = false;
CREATE INDEX IF NOT EXISTS idx_financial_bank_statement_lines_amount ON financial.bank_statement_lines(amount);

-- =====================
-- TABLA: bank_reconciliation_rules
-- Reglas de conciliacion automatica
-- =====================
CREATE TABLE IF NOT EXISTS financial.bank_reconciliation_rules (
    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,

    -- Identificacion de la regla
    name VARCHAR(255) NOT NULL,

    -- Tipo y valor del match
    match_type financial.reconciliation_match_type_enum NOT NULL,
    match_value VARCHAR(255) NOT NULL,  -- Valor a buscar segun el tipo

    -- Cuenta destino para auto-crear asiento
    auto_account_id UUID REFERENCES financial.accounts(id) ON DELETE SET NULL,

    -- Estado y prioridad
    is_active BOOLEAN DEFAULT true,
    priority INTEGER DEFAULT 0,  -- Mayor prioridad = se evalua primero

    -- 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)
);

-- Indices para bank_reconciliation_rules
CREATE INDEX IF NOT EXISTS idx_financial_bank_reconciliation_rules_tenant ON financial.bank_reconciliation_rules(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_reconciliation_rules_company ON financial.bank_reconciliation_rules(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_bank_reconciliation_rules_active ON financial.bank_reconciliation_rules(tenant_id, is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_financial_bank_reconciliation_rules_priority ON financial.bank_reconciliation_rules(tenant_id, priority DESC);
CREATE INDEX IF NOT EXISTS idx_financial_bank_reconciliation_rules_match_type ON financial.bank_reconciliation_rules(match_type);

-- =====================
-- FUNCION: Calcular balance calculado del extracto
-- Verifica que opening_balance + sum(lines) = closing_balance
-- =====================
CREATE OR REPLACE FUNCTION financial.check_statement_balance(p_statement_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    v_opening_balance DECIMAL(15, 2);
    v_closing_balance DECIMAL(15, 2);
    v_lines_total DECIMAL(15, 2);
    v_calculated_closing DECIMAL(15, 2);
BEGIN
    -- Obtener balances del extracto
    SELECT opening_balance, closing_balance
    INTO v_opening_balance, v_closing_balance
    FROM financial.bank_statements
    WHERE id = p_statement_id;

    -- Sumar todas las lineas
    SELECT COALESCE(SUM(amount), 0)
    INTO v_lines_total
    FROM financial.bank_statement_lines
    WHERE statement_id = p_statement_id;

    -- Calcular balance de cierre esperado
    v_calculated_closing := v_opening_balance + v_lines_total;

    -- Retornar si coincide (con tolerancia de 0.01)
    RETURN ABS(v_calculated_closing - v_closing_balance) < 0.01;
END;
$$ LANGUAGE plpgsql;

-- =====================
-- FUNCION: Obtener progreso de conciliacion
-- Retorna porcentaje de lineas conciliadas
-- =====================
CREATE OR REPLACE FUNCTION financial.get_reconciliation_progress(p_statement_id UUID)
RETURNS NUMERIC AS $$
DECLARE
    v_total_lines INTEGER;
    v_reconciled_lines INTEGER;
BEGIN
    SELECT COUNT(*), COUNT(*) FILTER (WHERE is_reconciled = true)
    INTO v_total_lines, v_reconciled_lines
    FROM financial.bank_statement_lines
    WHERE statement_id = p_statement_id;

    IF v_total_lines = 0 THEN
        RETURN 100;
    END IF;

    RETURN ROUND((v_reconciled_lines::NUMERIC / v_total_lines::NUMERIC) * 100, 2);
END;
$$ LANGUAGE plpgsql;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TYPE financial.bank_statement_status_enum IS 'Estado del extracto bancario: borrador, en conciliacion, conciliado';
COMMENT ON TYPE financial.reconciliation_match_type_enum IS 'Tipo de regla de match: monto exacto, referencia contiene, nombre de partner';

COMMENT ON TABLE financial.bank_statements IS 'Extractos bancarios importados para conciliacion';
COMMENT ON COLUMN financial.bank_statements.bank_account_id IS 'Cuenta contable tipo banco asociada';
COMMENT ON COLUMN financial.bank_statements.statement_date IS 'Fecha del extracto bancario';
COMMENT ON COLUMN financial.bank_statements.opening_balance IS 'Saldo inicial del extracto';
COMMENT ON COLUMN financial.bank_statements.closing_balance IS 'Saldo final del extracto';
COMMENT ON COLUMN financial.bank_statements.status IS 'Estado: draft, reconciling, reconciled';
COMMENT ON COLUMN financial.bank_statements.imported_at IS 'Fecha y hora de importacion';
COMMENT ON COLUMN financial.bank_statements.reconciled_at IS 'Fecha y hora de cierre de conciliacion';

COMMENT ON TABLE financial.bank_statement_lines IS 'Lineas/movimientos del extracto bancario';
COMMENT ON COLUMN financial.bank_statement_lines.transaction_date IS 'Fecha de la transaccion';
COMMENT ON COLUMN financial.bank_statement_lines.value_date IS 'Fecha valor (cuando aplica el movimiento)';
COMMENT ON COLUMN financial.bank_statement_lines.amount IS 'Monto del movimiento (positivo=deposito, negativo=retiro)';
COMMENT ON COLUMN financial.bank_statement_lines.is_reconciled IS 'Indica si la linea ha sido conciliada';
COMMENT ON COLUMN financial.bank_statement_lines.reconciled_entry_id IS 'Linea de asiento contable con la que se concilio';
COMMENT ON COLUMN financial.bank_statement_lines.partner_id IS 'Partner detectado o asignado manualmente';

COMMENT ON TABLE financial.bank_reconciliation_rules IS 'Reglas para conciliacion automatica de movimientos';
COMMENT ON COLUMN financial.bank_reconciliation_rules.match_type IS 'Tipo de coincidencia: exact_amount, reference_contains, partner_name';
COMMENT ON COLUMN financial.bank_reconciliation_rules.match_value IS 'Valor a buscar segun el tipo de match';
COMMENT ON COLUMN financial.bank_reconciliation_rules.auto_account_id IS 'Cuenta contable para auto-generar asiento';
COMMENT ON COLUMN financial.bank_reconciliation_rules.priority IS 'Prioridad de evaluacion (mayor = primero)';

COMMENT ON FUNCTION financial.check_statement_balance(UUID) IS 'Verifica que el balance calculado coincida con opening + lines = closing';
COMMENT ON FUNCTION financial.get_reconciliation_progress(UUID) IS 'Retorna porcentaje de lineas conciliadas (0-100)';