erp-core-database-v2/ddl/51-financial-accounts.sql

-- =============================================================
-- ARCHIVO: 51-financial-accounts.sql
-- DESCRIPCION: Plan de cuentas, tipos de cuenta y mapeos
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-20
-- DEPENDE DE: 50-financial-schema.sql
-- =============================================================

-- =====================
-- TABLA: account_types
-- Catalogo de tipos de cuenta contable
-- =====================
CREATE TABLE IF NOT EXISTS financial.account_types (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Identificacion
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,

    -- Clasificacion
    account_type financial.account_type_enum NOT NULL,

    -- Descripcion
    description TEXT,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indices para account_types
CREATE INDEX IF NOT EXISTS idx_financial_account_types_code ON financial.account_types(code);
CREATE INDEX IF NOT EXISTS idx_financial_account_types_type ON financial.account_types(account_type);

-- =====================
-- TABLA: accounts
-- Plan de cuentas contables
-- =====================
CREATE TABLE IF NOT EXISTS financial.accounts (
    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,  -- FK a company si existe multi-company

    -- Identificacion
    code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,

    -- Clasificacion
    account_type_id UUID NOT NULL REFERENCES financial.account_types(id) ON DELETE RESTRICT,

    -- Jerarquia (cuenta padre para estructura arborea)
    parent_id UUID REFERENCES financial.accounts(id) ON DELETE SET NULL,

    -- Moneda preferida
    currency_id UUID,  -- FK a catalogo de monedas si existe

    -- Configuracion
    is_reconcilable BOOLEAN DEFAULT FALSE,  -- Permite conciliacion bancaria
    is_deprecated BOOLEAN DEFAULT FALSE,     -- Cuenta obsoleta (no usar en nuevos movimientos)

    -- 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),
    deleted_at TIMESTAMPTZ,

    -- Constraint de unicidad por tenant
    UNIQUE(tenant_id, code)
);

-- Indices para accounts
CREATE INDEX IF NOT EXISTS idx_financial_accounts_tenant ON financial.accounts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_accounts_company ON financial.accounts(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_accounts_code ON financial.accounts(code);
CREATE INDEX IF NOT EXISTS idx_financial_accounts_type ON financial.accounts(account_type_id);
CREATE INDEX IF NOT EXISTS idx_financial_accounts_parent ON financial.accounts(parent_id);
CREATE INDEX IF NOT EXISTS idx_financial_accounts_active ON financial.accounts(tenant_id) WHERE deleted_at IS NULL AND is_deprecated = FALSE;
CREATE INDEX IF NOT EXISTS idx_financial_accounts_reconcilable ON financial.accounts(tenant_id, is_reconcilable) WHERE is_reconcilable = TRUE;

-- =====================
-- TABLA: account_mappings
-- Mapeos de cuentas para automatizaciones
-- Ej: cuenta_ingreso_default, cuenta_iva_16, cuenta_banco_principal
-- =====================
CREATE TABLE IF NOT EXISTS financial.account_mappings (
    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 del mapeo
    mapping_type VARCHAR(50) NOT NULL,  -- Ej: default_income, default_expense, vat_16, bank_main

    -- Cuenta mapeada
    account_id UUID NOT NULL REFERENCES financial.accounts(id) ON DELETE CASCADE,

    -- Descripcion
    description TEXT,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

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

    -- Un solo mapeo activo por tipo por tenant/company
    UNIQUE(tenant_id, company_id, mapping_type)
);

-- Indices para account_mappings
CREATE INDEX IF NOT EXISTS idx_financial_account_mappings_tenant ON financial.account_mappings(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_account_mappings_company ON financial.account_mappings(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_account_mappings_type ON financial.account_mappings(mapping_type);
CREATE INDEX IF NOT EXISTS idx_financial_account_mappings_account ON financial.account_mappings(account_id);
CREATE INDEX IF NOT EXISTS idx_financial_account_mappings_active ON financial.account_mappings(tenant_id, is_active) WHERE is_active = TRUE;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE financial.account_types IS 'Catalogo de tipos de cuenta contable (activo, pasivo, capital, ingreso, gasto)';
COMMENT ON COLUMN financial.account_types.code IS 'Codigo unico del tipo (ej: ASSET_CURRENT, LIABILITY_LONG)';
COMMENT ON COLUMN financial.account_types.account_type IS 'Clasificacion principal: asset, liability, equity, income, expense';

COMMENT ON TABLE financial.accounts IS 'Plan de cuentas contables con estructura jerarquica';
COMMENT ON COLUMN financial.accounts.code IS 'Codigo de cuenta (ej: 1100, 1100.01)';
COMMENT ON COLUMN financial.accounts.parent_id IS 'Referencia a cuenta padre para estructura de arbol';
COMMENT ON COLUMN financial.accounts.is_reconcilable IS 'TRUE si la cuenta permite conciliacion bancaria';
COMMENT ON COLUMN financial.accounts.is_deprecated IS 'TRUE si la cuenta esta obsoleta (no usar en nuevos movimientos)';

COMMENT ON TABLE financial.account_mappings IS 'Mapeos de cuentas para automatizaciones contables';
COMMENT ON COLUMN financial.account_mappings.mapping_type IS 'Tipo de mapeo (ej: default_income, vat_16, bank_main)';
COMMENT ON COLUMN financial.account_mappings.account_id IS 'Cuenta contable asociada al mapeo';