erp-clinicas/database/schemas/04-financial-ext-schema-ddl.sql

-- ============================================================================
-- FINANCIAL EXTENSIONS - FASE 8 ERP-Core
-- ERP Clínicas (Base Genérica)
-- ============================================================================
-- Fecha: 2026-01-04
-- Versión: 1.0
-- ============================================================================

-- Schema
CREATE SCHEMA IF NOT EXISTS financial;

-- ============================================================================
-- ENUMS
-- ============================================================================

DO $$ BEGIN
    CREATE TYPE financial.payment_method_type AS ENUM ('inbound', 'outbound');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE financial.reconcile_model_type AS ENUM (
        'writeoff_button',
        'writeoff_suggestion',
        'invoice_matching'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================================
-- TABLAS
-- ============================================================================

-- Líneas de términos de pago
CREATE TABLE IF NOT EXISTS financial.payment_term_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    payment_term_id UUID,
    value_type VARCHAR(20) NOT NULL DEFAULT 'percent',
    value NUMERIC(10,2) DEFAULT 0,
    days INTEGER DEFAULT 0,
    day_of_month INTEGER,
    applies_to VARCHAR(50), -- 'consulta', 'procedimiento', 'laboratorio', 'farmacia'
    sequence INTEGER DEFAULT 10,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE financial.payment_term_lines IS 'Líneas de términos de pago - FASE 8';
COMMENT ON COLUMN financial.payment_term_lines.applies_to IS 'Tipo de servicio al que aplica';

-- Métodos de pago
CREATE TABLE IF NOT EXISTS financial.payment_methods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    payment_type financial.payment_method_type NOT NULL,
    -- Extensiones clínica
    aplica_seguro BOOLEAN DEFAULT false,
    requiere_factura BOOLEAN DEFAULT false,
    porcentaje_seguro NUMERIC(5,2) DEFAULT 0,
    -- Control
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT uq_payment_methods_tenant_code UNIQUE(tenant_id, code)
);

COMMENT ON TABLE financial.payment_methods IS 'Métodos de pago - FASE 8';
COMMENT ON COLUMN financial.payment_methods.aplica_seguro IS 'Si el método está asociado a pagos de seguro';
COMMENT ON COLUMN financial.payment_methods.porcentaje_seguro IS 'Porcentaje que cubre el seguro';

-- Modelos de conciliación
CREATE TABLE IF NOT EXISTS financial.reconcile_models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    rule_type financial.reconcile_model_type NOT NULL DEFAULT 'writeoff_button',
    auto_reconcile BOOLEAN DEFAULT false,
    match_partner BOOLEAN DEFAULT true,
    match_amount BOOLEAN DEFAULT true,
    tolerance NUMERIC(10,2) DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE financial.reconcile_models IS 'Modelos de conciliación automática - FASE 8';

-- Líneas de modelo de conciliación
CREATE TABLE IF NOT EXISTS financial.reconcile_model_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    model_id UUID NOT NULL REFERENCES financial.reconcile_models(id) ON DELETE CASCADE,
    sequence INTEGER DEFAULT 10,
    account_id UUID,
    amount_type VARCHAR(20) DEFAULT 'percentage',
    amount_value NUMERIC(10,2) DEFAULT 100,
    label VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE financial.reconcile_model_lines IS 'Líneas de modelo de conciliación - FASE 8';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_payment_term_lines_tenant
    ON financial.payment_term_lines(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payment_term_lines_payment_term
    ON financial.payment_term_lines(payment_term_id);

CREATE INDEX IF NOT EXISTS idx_payment_methods_tenant
    ON financial.payment_methods(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payment_methods_code
    ON financial.payment_methods(tenant_id, code);

CREATE INDEX IF NOT EXISTS idx_reconcile_models_tenant
    ON financial.reconcile_models(tenant_id);

CREATE INDEX IF NOT EXISTS idx_reconcile_model_lines_model
    ON financial.reconcile_model_lines(model_id);

-- ============================================================================
-- RLS
-- ============================================================================

ALTER TABLE financial.payment_term_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.payment_methods ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.reconcile_models ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation_payment_term_lines ON financial.payment_term_lines;
CREATE POLICY tenant_isolation_payment_term_lines ON financial.payment_term_lines
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_payment_methods ON financial.payment_methods;
CREATE POLICY tenant_isolation_payment_methods ON financial.payment_methods
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_reconcile_models ON financial.reconcile_models;
CREATE POLICY tenant_isolation_reconcile_models ON financial.reconcile_models
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================================================
-- FIN FINANCIAL EXTENSIONS
-- ============================================================================