# Funciones SQL iniciales — Motor MVP FARO Connect

Este paquete está pensado para correr sobre el **modelo SQL inicial** que armamos antes. La lógica respeta la cadena central del producto: **dato → RAW/Staging → normalización → facts → KPIs → señales → alertas → tensiones → diagnóstico → recomendaciones → acciones → evidencia → FARO Score → reporte ejecutivo**. Esa es la columna vertebral del MVP FARO Connect. 

La idea es no construir una “mega función mágica”. Eso sería cómodo hoy y un dolor de cabeza mañana. Conviene armar funciones chicas, auditables y reutilizables.

---

# 1. Orden recomendado de ejecución

```sql
-- 1. Helpers generales
-- 2. Seed de definiciones MVP
-- 3. Validación de staging
-- 4. Normalización / maestros
-- 5. Construcción de facts
-- 6. Cálculo de KPIs
-- 7. Evaluación de señales
-- 8. Generación de alertas
-- 9. Evaluación de tensiones
-- 10. Diagnóstico y recomendaciones
-- 11. Acciones sugeridas
-- 12. FARO Score
-- 13. Reporte ejecutivo
-- 14. Orquestador general del ciclo MVP
```

---

# 2. Helpers generales

## 2.1. Normalizar texto

```sql
CREATE OR REPLACE FUNCTION faro.fn_norm_text(p_text TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT NULLIF(
        UPPER(
            BTRIM(
                REGEXP_REPLACE(COALESCE(p_text, ''), '\s+', ' ', 'g')
            )
        ),
        ''
    );
$$;
```

---

## 2.2. Normalizar código

Si viene código, usa código.
Si no viene código, genera uno desde el nombre.

```sql
CREATE OR REPLACE FUNCTION faro.fn_norm_code(p_code TEXT, p_name TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT COALESCE(
        faro.fn_norm_text(p_code),
        LEFT(MD5(COALESCE(faro.fn_norm_text(p_name), 'SIN_NOMBRE')), 16)
    );
$$;
```

---

## 2.3. Primer día del mes

```sql
CREATE OR REPLACE FUNCTION faro.fn_month_start(p_date DATE)
RETURNS DATE
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT DATE_TRUNC('month', p_date)::DATE;
$$;
```

---

# 3. Seed de definiciones MVP

Esta función carga las señales y alertas mínimas del MVP.

```sql
CREATE OR REPLACE FUNCTION faro.fn_seed_mvp_definitions()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO faro.signal_definitions (
        code,
        name,
        description,
        area,
        related_kpi_code,
        logic_description,
        threshold_config,
        severity
    )
    VALUES
    (
        'SIG-001',
        'Ventas suben',
        'Detecta crecimiento relevante de ventas.',
        'Comercial',
        'KPI-002',
        'Variación de ventas mayor a 10%.',
        '{"operator": ">", "value": 10}'::jsonb,
        'MEDIUM'
    ),
    (
        'SIG-002',
        'Margen cae',
        'Detecta caída relevante del margen bruto.',
        'Comercial / Finanzas',
        'KPI-003',
        'Margen bruto cae más de 3 puntos porcentuales.',
        '{"operator": "decrease_points", "value": 3}'::jsonb,
        'HIGH'
    ),
    (
        'SIG-003',
        'Descuento sube',
        'Detecta aumento de descuentos comerciales.',
        'Comercial',
        'KPI-004',
        'Descuento promedio sube más de 3 puntos porcentuales.',
        '{"operator": "increase_points", "value": 3}'::jsonb,
        'HIGH'
    ),
    (
        'SIG-004',
        'Cobranza empeora',
        'Detecta deterioro de los días de cobranza.',
        'Finanzas',
        'KPI-005',
        'DSO sube más de 7 días.',
        '{"operator": "increase_days", "value": 7}'::jsonb,
        'HIGH'
    ),
    (
        'SIG-005',
        'Stock crítico',
        'Detecta productos bajo mínimo de stock.',
        'Stock',
        'KPI-006',
        'Cantidad de productos bajo mínimo mayor a cero.',
        '{"operator": ">", "value": 0}'::jsonb,
        'HIGH'
    ),
    (
        'SIG-006',
        'Ejecución atrasada',
        'Detecta exceso de acciones vencidas.',
        'Ejecución',
        'KPI-007',
        'Acciones vencidas superiores al 20%.',
        '{"operator": ">", "value": 20}'::jsonb,
        'MEDIUM'
    ),
    (
        'SIG-007',
        'Datos poco confiables',
        'Detecta baja calidad de datos.',
        'Sistemas / Data',
        'KPI-008',
        'Calidad de datos inferior al 85%.',
        '{"operator": "<", "value": 85}'::jsonb,
        'HIGH'
    )
    ON CONFLICT (code) DO UPDATE
    SET
        name = EXCLUDED.name,
        description = EXCLUDED.description,
        threshold_config = EXCLUDED.threshold_config,
        severity = EXCLUDED.severity;

    INSERT INTO faro.alert_definitions (
        code,
        name,
        description,
        area,
        severity,
        related_signal_codes,
        related_kpi_codes
    )
    VALUES
    (
        'ALT-001',
        'Ventas crecen con margen en caída',
        'La empresa vende más, pero pierde rentabilidad.',
        'Comercial / Finanzas',
        'HIGH',
        ARRAY['SIG-001', 'SIG-002'],
        ARRAY['KPI-002', 'KPI-003']
    ),
    (
        'ALT-002',
        'Descuento comercial fuera de rango',
        'Los descuentos aumentan por encima del umbral aceptable.',
        'Comercial',
        'HIGH',
        ARRAY['SIG-003'],
        ARRAY['KPI-004']
    ),
    (
        'ALT-003',
        'Cobranza deteriorada',
        'Los días de cobranza aumentan y comprometen caja.',
        'Finanzas',
        'HIGH',
        ARRAY['SIG-004'],
        ARRAY['KPI-005']
    ),
    (
        'ALT-004',
        'Stock crítico en productos tractores',
        'Hay productos con stock bajo o crítico.',
        'Stock',
        'HIGH',
        ARRAY['SIG-005'],
        ARRAY['KPI-006']
    ),
    (
        'ALT-005',
        'Acciones vencidas relevantes',
        'La ejecución muestra atrasos que afectan el seguimiento.',
        'Ejecución',
        'MEDIUM',
        ARRAY['SIG-006'],
        ARRAY['KPI-007']
    ),
    (
        'ALT-006',
        'Calidad de datos insuficiente',
        'La información no tiene calidad suficiente para decidir con confianza.',
        'Sistemas / Data',
        'HIGH',
        ARRAY['SIG-007'],
        ARRAY['KPI-008']
    )
    ON CONFLICT (code) DO UPDATE
    SET
        name = EXCLUDED.name,
        description = EXCLUDED.description,
        severity = EXCLUDED.severity,
        related_signal_codes = EXCLUDED.related_signal_codes,
        related_kpi_codes = EXCLUDED.related_kpi_codes;
END;
$$;
```

---

# 4. Validación de staging

Esta función valida una carga ya pasada a staging.

**Supuesto:** el backend ya leyó el Excel/CSV, guardó RAW y cargó staging.
Este SQL valida si esos datos son utilizables.

```sql
CREATE OR REPLACE FUNCTION faro.fn_validate_mvp_import(
    p_company_id UUID,
    p_raw_import_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_domain faro.data_domain;
    v_total INTEGER := 0;
    v_valid INTEGER := 0;
    v_error INTEGER := 0;
    v_quality NUMERIC(5,2) := 0;
BEGIN
    SELECT data_domain
    INTO v_domain
    FROM faro.raw_imports
    WHERE id = p_raw_import_id
      AND company_id = p_company_id;

    IF v_domain IS NULL THEN
        RAISE EXCEPTION 'Importación no encontrada para company_id % y raw_import_id %', p_company_id, p_raw_import_id;
    END IF;

    IF v_domain = 'SALES' THEN

        WITH evaluated AS (
            SELECT
                id,
                (
                    CASE WHEN sale_date IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'sale_date', 'error', 'Fecha de venta faltante o inválida'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN COALESCE(product_code, product_name) IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'product', 'error', 'Producto faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN net_amount IS NULL AND gross_amount IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'amount', 'error', 'Importe de venta faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN quantity IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'quantity', 'error', 'Cantidad faltante'))
                        ELSE '[]'::jsonb END
                ) AS errors
            FROM faro.staging_sales
            WHERE company_id = p_company_id
              AND raw_import_id = p_raw_import_id
        )
        UPDATE faro.staging_sales ss
        SET
            validation_errors = e.errors,
            validation_status = CASE
                WHEN jsonb_array_length(e.errors) = 0 THEN 'VALID'
                ELSE 'ERROR'
            END
        FROM evaluated e
        WHERE ss.id = e.id;

        SELECT
            COUNT(*),
            COUNT(*) FILTER (WHERE validation_status = 'VALID'),
            COUNT(*) FILTER (WHERE validation_status = 'ERROR')
        INTO v_total, v_valid, v_error
        FROM faro.staging_sales
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

    ELSIF v_domain = 'STOCK' THEN

        WITH evaluated AS (
            SELECT
                id,
                (
                    CASE WHEN snapshot_date IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'snapshot_date', 'error', 'Fecha de stock faltante o inválida'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN COALESCE(product_code, product_name) IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'product', 'error', 'Producto faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN stock_available IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'stock_available', 'error', 'Stock disponible faltante'))
                        ELSE '[]'::jsonb END
                ) AS errors
            FROM faro.staging_stock
            WHERE company_id = p_company_id
              AND raw_import_id = p_raw_import_id
        )
        UPDATE faro.staging_stock ss
        SET
            validation_errors = e.errors,
            validation_status = CASE
                WHEN jsonb_array_length(e.errors) = 0 THEN 'VALID'
                ELSE 'ERROR'
            END
        FROM evaluated e
        WHERE ss.id = e.id;

        SELECT
            COUNT(*),
            COUNT(*) FILTER (WHERE validation_status = 'VALID'),
            COUNT(*) FILTER (WHERE validation_status = 'ERROR')
        INTO v_total, v_valid, v_error
        FROM faro.staging_stock
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

    ELSIF v_domain = 'RECEIVABLES' THEN

        WITH evaluated AS (
            SELECT
                id,
                (
                    CASE WHEN COALESCE(customer_code, customer_name) IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'customer', 'error', 'Cliente faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN invoice_number IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'invoice_number', 'error', 'Número de factura faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN due_date IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'due_date', 'error', 'Fecha de vencimiento faltante'))
                        ELSE '[]'::jsonb END
                    ||
                    CASE WHEN original_amount IS NULL
                        THEN jsonb_build_array(jsonb_build_object('field', 'original_amount', 'error', 'Importe original faltante'))
                        ELSE '[]'::jsonb END
                ) AS errors
            FROM faro.staging_receivables
            WHERE company_id = p_company_id
              AND raw_import_id = p_raw_import_id
        )
        UPDATE faro.staging_receivables sr
        SET
            validation_errors = e.errors,
            validation_status = CASE
                WHEN jsonb_array_length(e.errors) = 0 THEN 'VALID'
                ELSE 'ERROR'
            END
        FROM evaluated e
        WHERE sr.id = e.id;

        SELECT
            COUNT(*),
            COUNT(*) FILTER (WHERE validation_status = 'VALID'),
            COUNT(*) FILTER (WHERE validation_status = 'ERROR')
        INTO v_total, v_valid, v_error
        FROM faro.staging_receivables
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

    ELSE
        RAISE EXCEPTION 'Dominio no soportado por MVP: %', v_domain;
    END IF;

    v_quality := CASE
        WHEN v_total = 0 THEN 0
        ELSE ROUND((v_valid::NUMERIC / v_total::NUMERIC) * 100, 2)
    END;

    UPDATE faro.raw_imports
    SET
        total_rows = v_total,
        valid_rows_count = v_valid,
        rejected_rows_count = v_error,
        observed_rows_count = 0,
        data_quality_score = v_quality,
        status = CASE
            WHEN v_error = 0 THEN 'VALIDATED'::faro.import_status
            ELSE 'STAGING_READY'::faro.import_status
        END,
        updated_at = now()
    WHERE id = p_raw_import_id
      AND company_id = p_company_id;

    RETURN jsonb_build_object(
        'raw_import_id', p_raw_import_id,
        'domain', v_domain,
        'total_rows', v_total,
        'valid_rows', v_valid,
        'error_rows', v_error,
        'data_quality_score', v_quality
    );
END;
$$;
```

---

# 5. Normalización y creación de maestros

Esta función toma staging válido y crea/actualiza:

* sucursales,
* productos,
* clientes,
* vendedores.

```sql
CREATE OR REPLACE FUNCTION faro.fn_rebuild_masters_from_import(
    p_company_id UUID,
    p_raw_import_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_branches INTEGER := 0;
    v_products INTEGER := 0;
    v_customers INTEGER := 0;
    v_salespeople INTEGER := 0;
BEGIN
    -- Sucursales desde ventas
    INSERT INTO faro.branches (
        company_id,
        code,
        name,
        city,
        province
    )
    SELECT DISTINCT
        p_company_id,
        faro.fn_norm_code(branch_code, branch_name),
        COALESCE(NULLIF(branch_name, ''), NULLIF(branch_code, ''), 'Sucursal sin nombre'),
        NULL,
        NULL
    FROM faro.staging_sales
    WHERE company_id = p_company_id
      AND raw_import_id = p_raw_import_id
      AND validation_status = 'VALID'
      AND COALESCE(branch_code, branch_name) IS NOT NULL
    ON CONFLICT (company_id, code) DO UPDATE
    SET
        name = EXCLUDED.name,
        updated_at = now();

    GET DIAGNOSTICS v_branches = ROW_COUNT;

    -- Sucursales desde stock
    INSERT INTO faro.branches (
        company_id,
        code,
        name
    )
    SELECT DISTINCT
        p_company_id,
        faro.fn_norm_code(branch_code, branch_name),
        COALESCE(NULLIF(branch_name, ''), NULLIF(branch_code, ''), 'Sucursal sin nombre')
    FROM faro.staging_stock
    WHERE company_id = p_company_id
      AND raw_import_id = p_raw_import_id
      AND validation_status = 'VALID'
      AND COALESCE(branch_code, branch_name) IS NOT NULL
    ON CONFLICT (company_id, code) DO UPDATE
    SET
        name = EXCLUDED.name,
        updated_at = now();

    -- Productos desde ventas y stock
    WITH src_products AS (
        SELECT DISTINCT
            product_code,
            product_name
        FROM faro.staging_sales
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id
          AND validation_status = 'VALID'
          AND COALESCE(product_code, product_name) IS NOT NULL

        UNION

        SELECT DISTINCT
            product_code,
            product_name
        FROM faro.staging_stock
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id
          AND validation_status = 'VALID'
          AND COALESCE(product_code, product_name) IS NOT NULL
    )
    INSERT INTO faro.master_products (
        company_id,
        source_code,
        normalized_code,
        name,
        normalized_name
    )
    SELECT
        p_company_id,
        product_code,
        faro.fn_norm_code(product_code, product_name),
        COALESCE(NULLIF(product_name, ''), NULLIF(product_code, ''), 'Producto sin nombre'),
        faro.fn_norm_text(COALESCE(product_name, product_code))
    FROM src_products
    ON CONFLICT (company_id, normalized_code) DO UPDATE
    SET
        name = EXCLUDED.name,
        normalized_name = EXCLUDED.normalized_name,
        updated_at = now();

    GET DIAGNOSTICS v_products = ROW_COUNT;

    -- Clientes desde ventas y cobranza
    WITH src_customers AS (
        SELECT DISTINCT
            customer_code,
            customer_name
        FROM faro.staging_sales
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id
          AND validation_status = 'VALID'
          AND COALESCE(customer_code, customer_name) IS NOT NULL

        UNION

        SELECT DISTINCT
            customer_code,
            customer_name
        FROM faro.staging_receivables
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id
          AND validation_status = 'VALID'
          AND COALESCE(customer_code, customer_name) IS NOT NULL
    )
    INSERT INTO faro.master_customers (
        company_id,
        source_code,
        normalized_code,
        name,
        normalized_name
    )
    SELECT
        p_company_id,
        customer_code,
        faro.fn_norm_code(customer_code, customer_name),
        COALESCE(NULLIF(customer_name, ''), NULLIF(customer_code, ''), 'Cliente sin nombre'),
        faro.fn_norm_text(COALESCE(customer_name, customer_code))
    FROM src_customers
    ON CONFLICT (company_id, normalized_code) DO UPDATE
    SET
        name = EXCLUDED.name,
        normalized_name = EXCLUDED.normalized_name,
        updated_at = now();

    GET DIAGNOSTICS v_customers = ROW_COUNT;

    -- Vendedores desde ventas
    INSERT INTO faro.master_salespeople (
        company_id,
        branch_id,
        source_name,
        normalized_name
    )
    SELECT DISTINCT
        p_company_id,
        b.id,
        ss.salesperson_name,
        faro.fn_norm_text(ss.salesperson_name)
    FROM faro.staging_sales ss
    LEFT JOIN faro.branches b
        ON b.company_id = ss.company_id
       AND b.code = faro.fn_norm_code(ss.branch_code, ss.branch_name)
    WHERE ss.company_id = p_company_id
      AND ss.raw_import_id = p_raw_import_id
      AND ss.validation_status = 'VALID'
      AND ss.salesperson_name IS NOT NULL
    ON CONFLICT (company_id, normalized_name) DO UPDATE
    SET
        branch_id = COALESCE(EXCLUDED.branch_id, faro.master_salespeople.branch_id),
        updated_at = now();

    GET DIAGNOSTICS v_salespeople = ROW_COUNT;

    UPDATE faro.raw_imports
    SET
        status = 'NORMALIZED',
        updated_at = now()
    WHERE company_id = p_company_id
      AND id = p_raw_import_id;

    RETURN jsonb_build_object(
        'raw_import_id', p_raw_import_id,
        'branches_touched', v_branches,
        'products_touched', v_products,
        'customers_touched', v_customers,
        'salespeople_touched', v_salespeople
    );
END;
$$;
```

---

# 6. Construcción de facts

Esta función lleva staging validado al modelo ejecutivo.

```sql
CREATE OR REPLACE FUNCTION faro.fn_build_facts_from_import(
    p_company_id UUID,
    p_raw_import_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_domain faro.data_domain;
    v_inserted INTEGER := 0;
BEGIN
    SELECT data_domain
    INTO v_domain
    FROM faro.raw_imports
    WHERE id = p_raw_import_id
      AND company_id = p_company_id;

    IF v_domain = 'SALES' THEN

        DELETE FROM faro.fact_sales
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

        INSERT INTO faro.fact_sales (
            company_id,
            raw_import_id,
            staging_sale_id,
            sale_date,
            period_month,
            branch_id,
            customer_id,
            product_id,
            salesperson_id,
            invoice_number,
            quantity,
            gross_amount,
            discount_amount,
            net_amount,
            cost_amount,
            currency
        )
        SELECT
            ss.company_id,
            ss.raw_import_id,
            ss.id,
            ss.sale_date,
            faro.fn_month_start(ss.sale_date),
            b.id,
            c.id,
            p.id,
            sp.id,
            ss.invoice_number,
            COALESCE(ss.quantity, 0),
            COALESCE(ss.gross_amount, ss.net_amount + COALESCE(ss.discount_amount, 0), 0),
            COALESCE(ss.discount_amount, 0),
            COALESCE(ss.net_amount, COALESCE(ss.gross_amount, 0) - COALESCE(ss.discount_amount, 0)),
            COALESCE(ss.cost_amount, 0),
            COALESCE(ss.currency, 'ARS')
        FROM faro.staging_sales ss
        LEFT JOIN faro.branches b
            ON b.company_id = ss.company_id
           AND b.code = faro.fn_norm_code(ss.branch_code, ss.branch_name)
        LEFT JOIN faro.master_customers c
            ON c.company_id = ss.company_id
           AND c.normalized_code = faro.fn_norm_code(ss.customer_code, ss.customer_name)
        LEFT JOIN faro.master_products p
            ON p.company_id = ss.company_id
           AND p.normalized_code = faro.fn_norm_code(ss.product_code, ss.product_name)
        LEFT JOIN faro.master_salespeople sp
            ON sp.company_id = ss.company_id
           AND sp.normalized_name = faro.fn_norm_text(ss.salesperson_name)
        WHERE ss.company_id = p_company_id
          AND ss.raw_import_id = p_raw_import_id
          AND ss.validation_status = 'VALID';

        GET DIAGNOSTICS v_inserted = ROW_COUNT;

    ELSIF v_domain = 'STOCK' THEN

        DELETE FROM faro.fact_stock_snapshot
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

        INSERT INTO faro.fact_stock_snapshot (
            company_id,
            raw_import_id,
            staging_stock_id,
            snapshot_date,
            period_month,
            branch_id,
            product_id,
            stock_available,
            stock_reserved,
            stock_minimum,
            stock_maximum,
            unit_cost,
            stock_value
        )
        SELECT
            ss.company_id,
            ss.raw_import_id,
            ss.id,
            ss.snapshot_date,
            faro.fn_month_start(ss.snapshot_date),
            b.id,
            p.id,
            COALESCE(ss.stock_available, 0),
            COALESCE(ss.stock_reserved, 0),
            ss.stock_minimum,
            ss.stock_maximum,
            ss.unit_cost,
            COALESCE(ss.stock_available, 0) * COALESCE(ss.unit_cost, 0)
        FROM faro.staging_stock ss
        LEFT JOIN faro.branches b
            ON b.company_id = ss.company_id
           AND b.code = faro.fn_norm_code(ss.branch_code, ss.branch_name)
        LEFT JOIN faro.master_products p
            ON p.company_id = ss.company_id
           AND p.normalized_code = faro.fn_norm_code(ss.product_code, ss.product_name)
        WHERE ss.company_id = p_company_id
          AND ss.raw_import_id = p_raw_import_id
          AND ss.validation_status = 'VALID';

        GET DIAGNOSTICS v_inserted = ROW_COUNT;

    ELSIF v_domain = 'RECEIVABLES' THEN

        DELETE FROM faro.fact_receivables
        WHERE company_id = p_company_id
          AND raw_import_id = p_raw_import_id;

        INSERT INTO faro.fact_receivables (
            company_id,
            raw_import_id,
            staging_receivable_id,
            customer_id,
            invoice_number,
            issue_date,
            due_date,
            payment_date,
            period_month,
            original_amount,
            paid_amount,
            outstanding_amount,
            status
        )
        SELECT
            sr.company_id,
            sr.raw_import_id,
            sr.id,
            c.id,
            sr.invoice_number,
            sr.issue_date,
            sr.due_date,
            sr.payment_date,
            faro.fn_month_start(COALESCE(sr.issue_date, sr.due_date, CURRENT_DATE)),
            COALESCE(sr.original_amount, 0),
            COALESCE(sr.paid_amount, 0),
            COALESCE(sr.outstanding_amount, COALESCE(sr.original_amount, 0) - COALESCE(sr.paid_amount, 0)),
            CASE
                WHEN COALESCE(sr.outstanding_amount, COALESCE(sr.original_amount, 0) - COALESCE(sr.paid_amount, 0)) <= 0 THEN 'PAID'
                WHEN sr.due_date < CURRENT_DATE THEN 'OVERDUE'
                ELSE 'OPEN'
            END
        FROM faro.staging_receivables sr
        LEFT JOIN faro.master_customers c
            ON c.company_id = sr.company_id
           AND c.normalized_code = faro.fn_norm_code(sr.customer_code, sr.customer_name)
        WHERE sr.company_id = p_company_id
          AND sr.raw_import_id = p_raw_import_id
          AND sr.validation_status = 'VALID';

        GET DIAGNOSTICS v_inserted = ROW_COUNT;

    ELSE
        RAISE EXCEPTION 'Dominio no soportado para facts: %', v_domain;
    END IF;

    RETURN jsonb_build_object(
        'raw_import_id', p_raw_import_id,
        'domain', v_domain,
        'inserted_facts', v_inserted
    );
END;
$$;
```

---

# 7. Guardar resultado de KPI

Función auxiliar para insertar o reemplazar un KPI calculado.

```sql
CREATE OR REPLACE FUNCTION faro.fn_store_kpi_result(
    p_company_id UUID,
    p_kpi_code TEXT,
    p_period_start DATE,
    p_period_end DATE,
    p_branch_id UUID,
    p_value NUMERIC,
    p_previous_value NUMERIC,
    p_status TEXT,
    p_confidence_score NUMERIC,
    p_metadata JSONB DEFAULT '{}'::jsonb
)
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
    v_kpi_definition_id UUID;
    v_result_id UUID;
BEGIN
    SELECT id
    INTO v_kpi_definition_id
    FROM faro.kpi_definitions
    WHERE code = p_kpi_code
      AND is_active = TRUE;

    IF v_kpi_definition_id IS NULL THEN
        RAISE EXCEPTION 'KPI no definido: %', p_kpi_code;
    END IF;

    DELETE FROM faro.kpi_results
    WHERE company_id = p_company_id
      AND kpi_definition_id = v_kpi_definition_id
      AND period_start = p_period_start
      AND period_end = p_period_end
      AND branch_id IS NOT DISTINCT FROM p_branch_id;

    INSERT INTO faro.kpi_results (
        company_id,
        kpi_definition_id,
        period_start,
        period_end,
        branch_id,
        value,
        previous_value,
        variation_absolute,
        variation_percentage,
        status,
        confidence_score,
        calculation_metadata
    )
    VALUES (
        p_company_id,
        v_kpi_definition_id,
        p_period_start,
        p_period_end,
        p_branch_id,
        p_value,
        p_previous_value,
        CASE
            WHEN p_previous_value IS NULL THEN NULL
            ELSE p_value - p_previous_value
        END,
        CASE
            WHEN p_previous_value IS NULL OR p_previous_value = 0 THEN NULL
            ELSE ROUND(((p_value - p_previous_value) / p_previous_value) * 100, 4)
        END,
        p_status,
        p_confidence_score,
        p_metadata
    )
    RETURNING id INTO v_result_id;

    RETURN v_result_id;
END;
$$;
```

---

# 8. Cálculo de KPIs MVP

Calcula los KPIs centrales del MVP.

```sql
CREATE OR REPLACE FUNCTION faro.fn_calculate_kpis_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_days INTEGER;
    v_prev_start DATE;
    v_prev_end DATE;

    v_sales NUMERIC := 0;
    v_prev_sales NUMERIC := 0;
    v_sales_growth NUMERIC := 0;

    v_margin_pct NUMERIC := 0;
    v_prev_margin_pct NUMERIC := 0;

    v_discount_pct NUMERIC := 0;
    v_prev_discount_pct NUMERIC := 0;

    v_dso NUMERIC := 0;
    v_prev_dso NUMERIC := 0;
    v_outstanding NUMERIC := 0;
    v_prev_outstanding NUMERIC := 0;
    v_avg_daily_sales NUMERIC := 0;
    v_prev_avg_daily_sales NUMERIC := 0;

    v_critical_stock NUMERIC := 0;
    v_prev_critical_stock NUMERIC := 0;

    v_open_actions NUMERIC := 0;
    v_overdue_actions NUMERIC := 0;
    v_overdue_pct NUMERIC := 0;
    v_prev_overdue_pct NUMERIC := 0;

    v_data_quality NUMERIC := 100;
    v_prev_data_quality NUMERIC := 100;
BEGIN
    v_days := (p_period_end - p_period_start) + 1;
    v_prev_end := p_period_start - 1;
    v_prev_start := v_prev_end - (v_days - 1);

    -- KPI-001 Ventas netas
    SELECT COALESCE(SUM(net_amount), 0)
    INTO v_sales
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN p_period_start AND p_period_end;

    SELECT COALESCE(SUM(net_amount), 0)
    INTO v_prev_sales
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN v_prev_start AND v_prev_end;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-001',
        p_period_start,
        p_period_end,
        NULL,
        v_sales,
        v_prev_sales,
        'CALCULATED',
        95,
        jsonb_build_object('source', 'fact_sales')
    );

    -- KPI-002 Variación ventas %
    v_sales_growth := CASE
        WHEN v_prev_sales = 0 THEN NULL
        ELSE ROUND(((v_sales - v_prev_sales) / v_prev_sales) * 100, 4)
    END;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-002',
        p_period_start,
        p_period_end,
        NULL,
        COALESCE(v_sales_growth, 0),
        NULL,
        CASE
            WHEN COALESCE(v_sales_growth, 0) >= 10 THEN 'GROWTH'
            WHEN COALESCE(v_sales_growth, 0) < 0 THEN 'DROP'
            ELSE 'STABLE'
        END,
        90,
        jsonb_build_object(
            'current_sales', v_sales,
            'previous_sales', v_prev_sales
        )
    );

    -- KPI-003 Margen bruto %
    SELECT
        CASE
            WHEN COALESCE(SUM(net_amount), 0) = 0 THEN 0
            ELSE ROUND((SUM(margin_amount) / SUM(net_amount)) * 100, 4)
        END
    INTO v_margin_pct
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN p_period_start AND p_period_end;

    SELECT
        CASE
            WHEN COALESCE(SUM(net_amount), 0) = 0 THEN 0
            ELSE ROUND((SUM(margin_amount) / SUM(net_amount)) * 100, 4)
        END
    INTO v_prev_margin_pct
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN v_prev_start AND v_prev_end;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-003',
        p_period_start,
        p_period_end,
        NULL,
        v_margin_pct,
        v_prev_margin_pct,
        CASE
            WHEN v_margin_pct < v_prev_margin_pct - 3 THEN 'RISK'
            ELSE 'OK'
        END,
        90,
        jsonb_build_object('source', 'fact_sales')
    );

    -- KPI-004 Descuento promedio %
    SELECT
        CASE
            WHEN COALESCE(SUM(gross_amount), 0) = 0 THEN 0
            ELSE ROUND((SUM(discount_amount) / SUM(gross_amount)) * 100, 4)
        END
    INTO v_discount_pct
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN p_period_start AND p_period_end;

    SELECT
        CASE
            WHEN COALESCE(SUM(gross_amount), 0) = 0 THEN 0
            ELSE ROUND((SUM(discount_amount) / SUM(gross_amount)) * 100, 4)
        END
    INTO v_prev_discount_pct
    FROM faro.fact_sales
    WHERE company_id = p_company_id
      AND sale_date BETWEEN v_prev_start AND v_prev_end;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-004',
        p_period_start,
        p_period_end,
        NULL,
        v_discount_pct,
        v_prev_discount_pct,
        CASE
            WHEN v_discount_pct > v_prev_discount_pct + 3 THEN 'RISK'
            ELSE 'OK'
        END,
        90,
        jsonb_build_object('source', 'fact_sales')
    );

    -- KPI-005 Días de cobranza / DSO básico
    SELECT COALESCE(SUM(outstanding_amount), 0)
    INTO v_outstanding
    FROM faro.fact_receivables
    WHERE company_id = p_company_id
      AND period_month <= faro.fn_month_start(p_period_end);

    v_avg_daily_sales := CASE
        WHEN v_days = 0 THEN 0
        ELSE v_sales / v_days
    END;

    v_dso := CASE
        WHEN v_avg_daily_sales = 0 THEN 0
        ELSE ROUND(v_outstanding / v_avg_daily_sales, 2)
    END;

    SELECT COALESCE(SUM(outstanding_amount), 0)
    INTO v_prev_outstanding
    FROM faro.fact_receivables
    WHERE company_id = p_company_id
      AND period_month <= faro.fn_month_start(v_prev_end);

    v_prev_avg_daily_sales := CASE
        WHEN v_days = 0 THEN 0
        ELSE v_prev_sales / v_days
    END;

    v_prev_dso := CASE
        WHEN v_prev_avg_daily_sales = 0 THEN 0
        ELSE ROUND(v_prev_outstanding / v_prev_avg_daily_sales, 2)
    END;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-005',
        p_period_start,
        p_period_end,
        NULL,
        v_dso,
        v_prev_dso,
        CASE
            WHEN v_dso > v_prev_dso + 7 THEN 'RISK'
            ELSE 'OK'
        END,
        85,
        jsonb_build_object(
            'outstanding_amount', v_outstanding,
            'avg_daily_sales', v_avg_daily_sales
        )
    );

    -- KPI-006 Productos bajo stock mínimo
    SELECT COALESCE(COUNT(DISTINCT product_id), 0)
    INTO v_critical_stock
    FROM faro.fact_stock_snapshot
    WHERE company_id = p_company_id
      AND snapshot_date BETWEEN p_period_start AND p_period_end
      AND is_critical = TRUE;

    SELECT COALESCE(COUNT(DISTINCT product_id), 0)
    INTO v_prev_critical_stock
    FROM faro.fact_stock_snapshot
    WHERE company_id = p_company_id
      AND snapshot_date BETWEEN v_prev_start AND v_prev_end
      AND is_critical = TRUE;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-006',
        p_period_start,
        p_period_end,
        NULL,
        v_critical_stock,
        v_prev_critical_stock,
        CASE
            WHEN v_critical_stock > 0 THEN 'RISK'
            ELSE 'OK'
        END,
        90,
        jsonb_build_object('source', 'fact_stock_snapshot')
    );

    -- KPI-007 Acciones vencidas %
    SELECT COALESCE(COUNT(*), 0)
    INTO v_open_actions
    FROM faro.actions
    WHERE company_id = p_company_id
      AND created_at::DATE <= p_period_end
      AND status NOT IN ('CLOSED', 'CANCELLED');

    SELECT COALESCE(COUNT(*), 0)
    INTO v_overdue_actions
    FROM faro.actions
    WHERE company_id = p_company_id
      AND due_date < p_period_end
      AND status NOT IN ('CLOSED', 'CANCELLED');

    v_overdue_pct := CASE
        WHEN v_open_actions = 0 THEN 0
        ELSE ROUND((v_overdue_actions / v_open_actions) * 100, 4)
    END;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-007',
        p_period_start,
        p_period_end,
        NULL,
        v_overdue_pct,
        v_prev_overdue_pct,
        CASE
            WHEN v_overdue_pct > 20 THEN 'RISK'
            ELSE 'OK'
        END,
        95,
        jsonb_build_object(
            'open_actions', v_open_actions,
            'overdue_actions', v_overdue_actions
        )
    );

    -- KPI-008 Calidad de datos %
    SELECT COALESCE(ROUND(AVG(data_quality_score), 2), 100)
    INTO v_data_quality
    FROM faro.raw_imports
    WHERE company_id = p_company_id
      AND created_at::DATE BETWEEN p_period_start AND p_period_end
      AND data_quality_score IS NOT NULL;

    SELECT COALESCE(ROUND(AVG(data_quality_score), 2), 100)
    INTO v_prev_data_quality
    FROM faro.raw_imports
    WHERE company_id = p_company_id
      AND created_at::DATE BETWEEN v_prev_start AND v_prev_end
      AND data_quality_score IS NOT NULL;

    PERFORM faro.fn_store_kpi_result(
        p_company_id,
        'KPI-008',
        p_period_start,
        p_period_end,
        NULL,
        v_data_quality,
        v_prev_data_quality,
        CASE
            WHEN v_data_quality < 85 THEN 'RISK'
            ELSE 'OK'
        END,
        95,
        jsonb_build_object('source', 'raw_imports')
    );

    RETURN jsonb_build_object(
        'company_id', p_company_id,
        'period_start', p_period_start,
        'period_end', p_period_end,
        'kpis_calculated', ARRAY[
            'KPI-001',
            'KPI-002',
            'KPI-003',
            'KPI-004',
            'KPI-005',
            'KPI-006',
            'KPI-007',
            'KPI-008'
        ]
    );
END;
$$;
```

---

# 9. Helper para leer KPIs

```sql
CREATE OR REPLACE FUNCTION faro.fn_get_kpi_value(
    p_company_id UUID,
    p_kpi_code TEXT,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT kr.value
    FROM faro.kpi_results kr
    JOIN faro.kpi_definitions kd ON kd.id = kr.kpi_definition_id
    WHERE kr.company_id = p_company_id
      AND kd.code = p_kpi_code
      AND kr.period_start = p_period_start
      AND kr.period_end = p_period_end
    ORDER BY kr.created_at DESC
    LIMIT 1;
$$;
```

```sql
CREATE OR REPLACE FUNCTION faro.fn_get_kpi_variation_absolute(
    p_company_id UUID,
    p_kpi_code TEXT,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT kr.variation_absolute
    FROM faro.kpi_results kr
    JOIN faro.kpi_definitions kd ON kd.id = kr.kpi_definition_id
    WHERE kr.company_id = p_company_id
      AND kd.code = p_kpi_code
      AND kr.period_start = p_period_start
      AND kr.period_end = p_period_end
    ORDER BY kr.created_at DESC
    LIMIT 1;
$$;
```

---

# 10. Guardar señal

```sql
CREATE OR REPLACE FUNCTION faro.fn_store_signal_result(
    p_company_id UUID,
    p_signal_code TEXT,
    p_kpi_code TEXT,
    p_period_start DATE,
    p_period_end DATE,
    p_is_triggered BOOLEAN,
    p_severity faro.severity_level,
    p_message TEXT,
    p_payload JSONB DEFAULT '{}'::jsonb
)
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
    v_signal_definition_id UUID;
    v_kpi_result_id UUID;
    v_result_id UUID;
BEGIN
    SELECT id
    INTO v_signal_definition_id
    FROM faro.signal_definitions
    WHERE code = p_signal_code
      AND is_active = TRUE;

    IF v_signal_definition_id IS NULL THEN
        RAISE EXCEPTION 'Señal no definida: %', p_signal_code;
    END IF;

    SELECT kr.id
    INTO v_kpi_result_id
    FROM faro.kpi_results kr
    JOIN faro.kpi_definitions kd ON kd.id = kr.kpi_definition_id
    WHERE kr.company_id = p_company_id
      AND kd.code = p_kpi_code
      AND kr.period_start = p_period_start
      AND kr.period_end = p_period_end
    ORDER BY kr.created_at DESC
    LIMIT 1;

    DELETE FROM faro.signal_results
    WHERE company_id = p_company_id
      AND signal_definition_id = v_signal_definition_id
      AND period_start = p_period_start
      AND period_end = p_period_end;

    INSERT INTO faro.signal_results (
        company_id,
        signal_definition_id,
        kpi_result_id,
        period_start,
        period_end,
        is_triggered,
        severity,
        message,
        evidence_payload
    )
    VALUES (
        p_company_id,
        v_signal_definition_id,
        v_kpi_result_id,
        p_period_start,
        p_period_end,
        p_is_triggered,
        p_severity,
        p_message,
        p_payload
    )
    RETURNING id INTO v_result_id;

    RETURN v_result_id;
END;
$$;
```

---

# 11. Evaluación de señales MVP

```sql
CREATE OR REPLACE FUNCTION faro.fn_evaluate_signals_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_sales_growth NUMERIC := 0;
    v_margin_change NUMERIC := 0;
    v_discount_change NUMERIC := 0;
    v_dso_change NUMERIC := 0;
    v_critical_stock NUMERIC := 0;
    v_overdue_actions_pct NUMERIC := 0;
    v_data_quality NUMERIC := 100;

    v_triggered_count INTEGER := 0;
BEGIN
    v_sales_growth := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-002', p_period_start, p_period_end), 0);
    v_margin_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-003', p_period_start, p_period_end), 0);
    v_discount_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-004', p_period_start, p_period_end), 0);
    v_dso_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-005', p_period_start, p_period_end), 0);
    v_critical_stock := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-006', p_period_start, p_period_end), 0);
    v_overdue_actions_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-007', p_period_start, p_period_end), 0);
    v_data_quality := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-008', p_period_start, p_period_end), 100);

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-001',
        'KPI-002',
        p_period_start,
        p_period_end,
        v_sales_growth > 10,
        'MEDIUM',
        'Las ventas muestran crecimiento relevante.',
        jsonb_build_object('sales_growth_pct', v_sales_growth)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-002',
        'KPI-003',
        p_period_start,
        p_period_end,
        v_margin_change < -3,
        'HIGH',
        'El margen bruto cae por encima del umbral permitido.',
        jsonb_build_object('margin_change_points', v_margin_change)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-003',
        'KPI-004',
        p_period_start,
        p_period_end,
        v_discount_change > 3,
        'HIGH',
        'Los descuentos comerciales aumentan por encima del umbral permitido.',
        jsonb_build_object('discount_change_points', v_discount_change)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-004',
        'KPI-005',
        p_period_start,
        p_period_end,
        v_dso_change > 7,
        'HIGH',
        'Los días de cobranza empeoran.',
        jsonb_build_object('dso_change_days', v_dso_change)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-005',
        'KPI-006',
        p_period_start,
        p_period_end,
        v_critical_stock > 0,
        'HIGH',
        'Existen productos bajo stock mínimo.',
        jsonb_build_object('critical_stock_count', v_critical_stock)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-006',
        'KPI-007',
        p_period_start,
        p_period_end,
        v_overdue_actions_pct > 20,
        'MEDIUM',
        'Las acciones vencidas superan el umbral permitido.',
        jsonb_build_object('overdue_actions_pct', v_overdue_actions_pct)
    );

    PERFORM faro.fn_store_signal_result(
        p_company_id,
        'SIG-007',
        'KPI-008',
        p_period_start,
        p_period_end,
        v_data_quality < 85,
        'HIGH',
        'La calidad de datos es insuficiente para decidir con plena confianza.',
        jsonb_build_object('data_quality_score', v_data_quality)
    );

    SELECT COUNT(*)
    INTO v_triggered_count
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sr.is_triggered = TRUE;

    RETURN jsonb_build_object(
        'signals_evaluated', 7,
        'signals_triggered', v_triggered_count
    );
END;
$$;
```

---

# 12. Guardar alerta

```sql
CREATE OR REPLACE FUNCTION faro.fn_store_alert_result(
    p_company_id UUID,
    p_alert_code TEXT,
    p_period_start DATE,
    p_period_end DATE,
    p_title TEXT,
    p_message TEXT,
    p_area TEXT,
    p_severity faro.severity_level,
    p_payload JSONB DEFAULT '{}'::jsonb
)
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
    v_alert_definition_id UUID;
    v_result_id UUID;
BEGIN
    SELECT id
    INTO v_alert_definition_id
    FROM faro.alert_definitions
    WHERE code = p_alert_code
      AND is_active = TRUE;

    IF v_alert_definition_id IS NULL THEN
        RAISE EXCEPTION 'Alerta no definida: %', p_alert_code;
    END IF;

    DELETE FROM faro.alert_results
    WHERE company_id = p_company_id
      AND alert_definition_id = v_alert_definition_id
      AND period_start = p_period_start
      AND period_end = p_period_end;

    INSERT INTO faro.alert_results (
        company_id,
        alert_definition_id,
        period_start,
        period_end,
        severity,
        title,
        message,
        area,
        status,
        source_payload
    )
    VALUES (
        p_company_id,
        v_alert_definition_id,
        p_period_start,
        p_period_end,
        p_severity,
        p_title,
        p_message,
        p_area,
        'OPEN',
        p_payload
    )
    RETURNING id INTO v_result_id;

    RETURN v_result_id;
END;
$$;
```

---

# 13. Evaluación de alertas MVP

```sql
CREATE OR REPLACE FUNCTION faro.fn_evaluate_alerts_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_sig_sales_growth BOOLEAN := FALSE;
    v_sig_margin_drop BOOLEAN := FALSE;
    v_sig_discount_up BOOLEAN := FALSE;
    v_sig_dso_up BOOLEAN := FALSE;
    v_sig_stock_critical BOOLEAN := FALSE;
    v_sig_execution_delay BOOLEAN := FALSE;
    v_sig_data_quality BOOLEAN := FALSE;

    v_alerts_created INTEGER := 0;
BEGIN
    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_sales_growth
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-001';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_margin_drop
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-002';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_discount_up
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-003';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_dso_up
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-004';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_stock_critical
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-005';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_execution_delay
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-006';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_data_quality
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-007';

    IF v_sig_sales_growth AND v_sig_margin_drop THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-001',
            p_period_start,
            p_period_end,
            'Ventas crecen con margen en caída',
            'La empresa aumenta ventas, pero el margen se deteriora. Esto requiere revisión comercial y financiera.',
            'Comercial / Finanzas',
            'HIGH',
            jsonb_build_object('signals', ARRAY['SIG-001', 'SIG-002'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    IF v_sig_discount_up THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-002',
            p_period_start,
            p_period_end,
            'Descuento comercial fuera de rango',
            'Los descuentos aumentaron por encima del umbral aceptable.',
            'Comercial',
            'HIGH',
            jsonb_build_object('signals', ARRAY['SIG-003'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    IF v_sig_dso_up THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-003',
            p_period_start,
            p_period_end,
            'Cobranza deteriorada',
            'Los días de cobranza empeoraron y pueden afectar la caja.',
            'Finanzas',
            'HIGH',
            jsonb_build_object('signals', ARRAY['SIG-004'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    IF v_sig_stock_critical THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-004',
            p_period_start,
            p_period_end,
            'Stock crítico en productos tractores',
            'Hay productos bajo mínimo de stock. Riesgo comercial y operativo.',
            'Stock',
            'HIGH',
            jsonb_build_object('signals', ARRAY['SIG-005'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    IF v_sig_execution_delay THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-005',
            p_period_start,
            p_period_end,
            'Acciones vencidas relevantes',
            'La ejecución muestra atrasos que afectan el cierre de decisiones.',
            'Ejecución',
            'MEDIUM',
            jsonb_build_object('signals', ARRAY['SIG-006'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    IF v_sig_data_quality THEN
        PERFORM faro.fn_store_alert_result(
            p_company_id,
            'ALT-006',
            p_period_start,
            p_period_end,
            'Calidad de datos insuficiente',
            'La calidad de datos está por debajo del umbral mínimo para decidir con confianza.',
            'Sistemas / Data',
            'HIGH',
            jsonb_build_object('signals', ARRAY['SIG-007'])
        );
        v_alerts_created := v_alerts_created + 1;
    END IF;

    RETURN jsonb_build_object(
        'alerts_created', v_alerts_created
    );
END;
$$;
```

---

# 14. Evaluación de tensión MVP — Crecimiento no rentable

Esta es la tensión demo central del MVP.

```sql
CREATE OR REPLACE FUNCTION faro.fn_evaluate_tensions_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_tension_definition_id UUID;

    v_sig_sales_growth BOOLEAN := FALSE;
    v_sig_margin_drop BOOLEAN := FALSE;
    v_sig_discount_up BOOLEAN := FALSE;
    v_sig_dso_up BOOLEAN := FALSE;
    v_sig_stock_critical BOOLEAN := FALSE;

    v_core_count INTEGER := 0;
    v_optional_count INTEGER := 0;
    v_confidence NUMERIC(5,2) := 0;
    v_data_quality NUMERIC := 100;
    v_created BOOLEAN := FALSE;
BEGIN
    SELECT id
    INTO v_tension_definition_id
    FROM faro.tension_definitions
    WHERE code = 'TNS-001'
      AND is_active = TRUE;

    IF v_tension_definition_id IS NULL THEN
        RAISE EXCEPTION 'Tensión TNS-001 no definida';
    END IF;

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_sales_growth
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-001';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_margin_drop
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-002';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_discount_up
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-003';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_dso_up
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-004';

    SELECT COALESCE(MAX(sr.is_triggered::INT), 0)::BOOLEAN
    INTO v_sig_stock_critical
    FROM faro.signal_results sr
    JOIN faro.signal_definitions sd ON sd.id = sr.signal_definition_id
    WHERE sr.company_id = p_company_id
      AND sr.period_start = p_period_start
      AND sr.period_end = p_period_end
      AND sd.code = 'SIG-005';

    v_core_count :=
        CASE WHEN v_sig_sales_growth THEN 1 ELSE 0 END +
        CASE WHEN v_sig_margin_drop THEN 1 ELSE 0 END +
        CASE WHEN v_sig_discount_up THEN 1 ELSE 0 END;

    v_optional_count :=
        CASE WHEN v_sig_dso_up THEN 1 ELSE 0 END +
        CASE WHEN v_sig_stock_critical THEN 1 ELSE 0 END;

    v_data_quality := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-008', p_period_start, p_period_end), 100);

    v_confidence := LEAST(
        100,
        ROUND(
            (v_core_count * 25)
            + (v_optional_count * 10)
            + (v_data_quality * 0.15),
            2
        )
    );

    DELETE FROM faro.tension_results
    WHERE company_id = p_company_id
      AND tension_definition_id = v_tension_definition_id
      AND period_start = p_period_start
      AND period_end = p_period_end;

    IF v_core_count >= 3 THEN
        INSERT INTO faro.tension_results (
            company_id,
            tension_definition_id,
            period_start,
            period_end,
            priority,
            severity,
            confidence_score,
            title,
            executive_summary,
            source_payload,
            status
        )
        VALUES (
            p_company_id,
            v_tension_definition_id,
            p_period_start,
            p_period_end,
            'HIGH',
            CASE
                WHEN v_optional_count >= 2 THEN 'CRITICAL'
                ELSE 'HIGH'
            END,
            v_confidence,
            'Crecimiento no rentable',
            'La empresa está creciendo en ventas, pero deteriora margen y descuentos. La situación requiere revisión comercial, financiera y operativa.',
            jsonb_build_object(
                'sales_growth_signal', v_sig_sales_growth,
                'margin_drop_signal', v_sig_margin_drop,
                'discount_up_signal', v_sig_discount_up,
                'dso_up_signal', v_sig_dso_up,
                'stock_critical_signal', v_sig_stock_critical,
                'core_count', v_core_count,
                'optional_count', v_optional_count,
                'data_quality', v_data_quality
            ),
            'ACTIVE'
        );

        v_created := TRUE;
    END IF;

    RETURN jsonb_build_object(
        'tension_code', 'TNS-001',
        'created', v_created,
        'core_count', v_core_count,
        'optional_count', v_optional_count,
        'confidence', v_confidence
    );
END;
$$;
```

---

# 15. Diagnóstico y recomendaciones

```sql
CREATE OR REPLACE FUNCTION faro.fn_generate_diagnosis_and_recommendations_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_tension_result_id UUID;
    v_diagnosis_id UUID;

    v_sales_growth NUMERIC := 0;
    v_margin_pct NUMERIC := 0;
    v_margin_change NUMERIC := 0;
    v_discount_pct NUMERIC := 0;
    v_discount_change NUMERIC := 0;
    v_dso NUMERIC := 0;
    v_dso_change NUMERIC := 0;
    v_critical_stock NUMERIC := 0;

    v_recommendations_created INTEGER := 0;
BEGIN
    SELECT tr.id
    INTO v_tension_result_id
    FROM faro.tension_results tr
    JOIN faro.tension_definitions td ON td.id = tr.tension_definition_id
    WHERE tr.company_id = p_company_id
      AND tr.period_start = p_period_start
      AND tr.period_end = p_period_end
      AND td.code = 'TNS-001'
      AND tr.status = 'ACTIVE'
    ORDER BY tr.created_at DESC
    LIMIT 1;

    IF v_tension_result_id IS NULL THEN
        RETURN jsonb_build_object(
            'diagnosis_created', false,
            'reason', 'No hay tensión activa TNS-001'
        );
    END IF;

    v_sales_growth := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-002', p_period_start, p_period_end), 0);
    v_margin_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-003', p_period_start, p_period_end), 0);
    v_margin_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-003', p_period_start, p_period_end), 0);
    v_discount_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-004', p_period_start, p_period_end), 0);
    v_discount_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-004', p_period_start, p_period_end), 0);
    v_dso := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-005', p_period_start, p_period_end), 0);
    v_dso_change := COALESCE(faro.fn_get_kpi_variation_absolute(p_company_id, 'KPI-005', p_period_start, p_period_end), 0);
    v_critical_stock := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-006', p_period_start, p_period_end), 0);

    DELETE FROM faro.diagnosis_results
    WHERE company_id = p_company_id
      AND tension_result_id = v_tension_result_id
      AND period_start = p_period_start
      AND period_end = p_period_end;

    INSERT INTO faro.diagnosis_results (
        company_id,
        tension_result_id,
        period_start,
        period_end,
        title,
        diagnosis_text,
        root_cause_hypothesis,
        confidence_score,
        priority,
        positive_drivers,
        negative_drivers,
        recommended_focus
    )
    VALUES (
        p_company_id,
        v_tension_result_id,
        p_period_start,
        p_period_end,
        'Diagnóstico ejecutivo: crecimiento no rentable',
        'La empresa está creciendo en ventas, pero deteriora margen y caja. El crecimiento parece estar impulsado por mayores descuentos, con riesgo adicional por cobranza y stock crítico.',
        'La política comercial estaría priorizando volumen por encima de rentabilidad, con controles insuficientes sobre descuentos, cobranza y disponibilidad de productos tractores.',
        87,
        'HIGH',
        jsonb_build_array(
            jsonb_build_object('driver', 'Ventas en crecimiento', 'value', v_sales_growth)
        ),
        jsonb_build_array(
            jsonb_build_object('driver', 'Caída de margen', 'value', v_margin_change),
            jsonb_build_object('driver', 'Aumento de descuentos', 'value', v_discount_change),
            jsonb_build_object('driver', 'Deterioro de cobranza', 'value', v_dso_change),
            jsonb_build_object('driver', 'Stock crítico', 'value', v_critical_stock)
        ),
        'Auditar descuentos, priorizar cobranza, revisar política de comisiones y validar stock crítico.'
    )
    RETURNING id INTO v_diagnosis_id;

    DELETE FROM faro.recommendation_results
    WHERE company_id = p_company_id
      AND diagnosis_result_id = v_diagnosis_id;

    INSERT INTO faro.recommendation_results (
        company_id,
        recommendation_definition_id,
        diagnosis_result_id,
        tension_result_id,
        title,
        recommendation_text,
        priority,
        expected_impact,
        can_create_action,
        status
    )
    SELECT
        p_company_id,
        rd.id,
        v_diagnosis_id,
        v_tension_result_id,
        rd.name,
        rd.description,
        CASE
            WHEN rd.code IN ('REC-001', 'REC-002', 'REC-003') THEN 'HIGH'::faro.priority_level
            ELSE 'MEDIUM'::faro.priority_level
        END,
        rd.expected_impact,
        TRUE,
        'SUGGESTED'
    FROM faro.recommendation_definitions rd
    WHERE rd.code IN ('REC-001', 'REC-002', 'REC-003', 'REC-004')
      AND rd.is_active = TRUE;

    GET DIAGNOSTICS v_recommendations_created = ROW_COUNT;

    RETURN jsonb_build_object(
        'diagnosis_created', true,
        'diagnosis_id', v_diagnosis_id,
        'recommendations_created', v_recommendations_created
    );
END;
$$;
```

---

# 16. Crear acciones desde recomendaciones

Esta función convierte recomendaciones en acciones ejecutivas.

```sql
CREATE OR REPLACE FUNCTION faro.fn_create_actions_from_recommendations_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE,
    p_created_by UUID DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    v_responsible_user_id UUID;
    v_due_date DATE;
    v_actions_created INTEGER := 0;
BEGIN
    FOR r IN
        SELECT
            rr.id AS recommendation_result_id,
            rr.diagnosis_result_id,
            rr.tension_result_id,
            rr.title,
            rr.recommendation_text,
            rr.priority,
            rd.code AS recommendation_code,
            rd.area
        FROM faro.recommendation_results rr
        LEFT JOIN faro.recommendation_definitions rd ON rd.id = rr.recommendation_definition_id
        WHERE rr.company_id = p_company_id
          AND rr.status = 'SUGGESTED'
          AND rr.can_create_action = TRUE
          AND NOT EXISTS (
              SELECT 1
              FROM faro.actions a
              WHERE a.company_id = p_company_id
                AND a.recommendation_result_id = rr.id
          )
    LOOP
        v_responsible_user_id := NULL;

        SELECT id
        INTO v_responsible_user_id
        FROM faro.users
        WHERE company_id = p_company_id
          AND is_active = TRUE
          AND (
              area ILIKE '%' || SPLIT_PART(COALESCE(r.area, ''), ' ', 1) || '%'
              OR position ILIKE '%Gerente%'
              OR position ILIKE '%Responsable%'
          )
        ORDER BY
            CASE
                WHEN area ILIKE '%' || SPLIT_PART(COALESCE(r.area, ''), ' ', 1) || '%' THEN 1
                ELSE 2
            END
        LIMIT 1;

        v_due_date := CASE
            WHEN r.recommendation_code = 'REC-003' THEN CURRENT_DATE + 2
            WHEN r.recommendation_code IN ('REC-001', 'REC-002') THEN CURRENT_DATE + 3
            ELSE CURRENT_DATE + 7
        END;

        INSERT INTO faro.actions (
            company_id,
            recommendation_result_id,
            diagnosis_result_id,
            tension_result_id,
            title,
            description,
            area,
            priority,
            status,
            responsible_user_id,
            due_date,
            expected_impact,
            measurement_method,
            evidence_required,
            created_by
        )
        VALUES (
            p_company_id,
            r.recommendation_result_id,
            r.diagnosis_result_id,
            r.tension_result_id,
            r.title,
            r.recommendation_text,
            r.area,
            r.priority,
            'PENDING',
            v_responsible_user_id,
            v_due_date,
            'Impactar positivamente en margen, caja, stock o ejecución según recomendación.',
            'Medición posterior mediante KPIs vinculados a la tensión original.',
            TRUE,
            p_created_by
        );

        UPDATE faro.recommendation_results
        SET status = 'CONVERTED_TO_ACTION'
        WHERE id = r.recommendation_result_id;

        v_actions_created := v_actions_created + 1;
    END LOOP;

    RETURN jsonb_build_object(
        'actions_created', v_actions_created
    );
END;
$$;
```

---

# 17. Calcular FARO Score MVP

Fórmula base:

```text
FARO Score =
Comercial × 25%
+ Financiero × 25%
+ Stock × 20%
+ Ejecución × 20%
+ Calidad de datos × 10%
```

```sql
CREATE OR REPLACE FUNCTION faro.fn_calculate_faro_score_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_sales_growth NUMERIC := 0;
    v_margin_pct NUMERIC := 0;
    v_discount_pct NUMERIC := 0;
    v_dso NUMERIC := 0;
    v_critical_stock NUMERIC := 0;
    v_overdue_pct NUMERIC := 0;
    v_data_quality NUMERIC := 100;

    v_commercial_score NUMERIC := 0;
    v_financial_score NUMERIC := 0;
    v_stock_score NUMERIC := 0;
    v_execution_score NUMERIC := 0;
    v_data_score NUMERIC := 0;
    v_final_score NUMERIC := 0;
    v_previous_score NUMERIC;
    v_score_result_id UUID;
BEGIN
    v_sales_growth := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-002', p_period_start, p_period_end), 0);
    v_margin_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-003', p_period_start, p_period_end), 0);
    v_discount_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-004', p_period_start, p_period_end), 0);
    v_dso := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-005', p_period_start, p_period_end), 0);
    v_critical_stock := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-006', p_period_start, p_period_end), 0);
    v_overdue_pct := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-007', p_period_start, p_period_end), 0);
    v_data_quality := COALESCE(faro.fn_get_kpi_value(p_company_id, 'KPI-008', p_period_start, p_period_end), 100);

    -- Comercial: premia crecimiento, castiga bajo margen y descuento alto.
    v_commercial_score := LEAST(
        100,
        GREATEST(
            0,
            70
            + (v_sales_growth * 0.5)
            + ((v_margin_pct - 25) * 2)
            - (GREATEST(v_discount_pct - 8, 0) * 2)
        )
    );

    -- Financiero: castiga DSO alto.
    v_financial_score := LEAST(
        100,
        GREATEST(
            0,
            85 - (GREATEST(v_dso - 30, 0) * 2)
        )
    );

    -- Stock: castiga productos críticos.
    v_stock_score := LEAST(
        100,
        GREATEST(
            0,
            100 - LEAST(40, v_critical_stock)
        )
    );

    -- Ejecución: castiga acciones vencidas.
    v_execution_score := LEAST(
        100,
        GREATEST(
            0,
            100 - (v_overdue_pct * 1.5)
        )
    );

    -- Calidad de datos: entra directo, limitado entre 0 y 100.
    v_data_score := LEAST(100, GREATEST(0, v_data_quality));

    v_final_score := ROUND(
        (v_commercial_score * 0.25)
        + (v_financial_score * 0.25)
        + (v_stock_score * 0.20)
        + (v_execution_score * 0.20)
        + (v_data_score * 0.10),
        2
    );

    SELECT score_value
    INTO v_previous_score
    FROM faro.score_results
    WHERE company_id = p_company_id
      AND period_end < p_period_start
      AND formula_version = 'MVP-1'
    ORDER BY period_end DESC
    LIMIT 1;

    DELETE FROM faro.score_results
    WHERE company_id = p_company_id
      AND period_start = p_period_start
      AND period_end = p_period_end
      AND formula_version = 'MVP-1';

    INSERT INTO faro.score_results (
        company_id,
        period_start,
        period_end,
        score_value,
        previous_score_value,
        variation,
        confidence_score,
        recommended_focus,
        explanation,
        formula_version
    )
    VALUES (
        p_company_id,
        p_period_start,
        p_period_end,
        v_final_score,
        v_previous_score,
        CASE
            WHEN v_previous_score IS NULL THEN NULL
            ELSE v_final_score - v_previous_score
        END,
        v_data_score,
        CASE
            WHEN v_commercial_score < 70 THEN 'Revisar rentabilidad comercial y descuentos.'
            WHEN v_financial_score < 70 THEN 'Priorizar cobranza y caja.'
            WHEN v_stock_score < 70 THEN 'Validar stock crítico.'
            WHEN v_execution_score < 70 THEN 'Acelerar cierre de acciones vencidas.'
            ELSE 'Mantener foco y seguimiento ejecutivo.'
        END,
        'FARO Score calculado por componentes MVP: comercial, financiero, stock, ejecución y calidad de datos.',
        'MVP-1'
    )
    RETURNING id INTO v_score_result_id;

    INSERT INTO faro.score_components (
        company_id,
        score_result_id,
        component_code,
        component_name,
        weight,
        value,
        weighted_value,
        source_payload
    )
    VALUES
    (
        p_company_id,
        v_score_result_id,
        'COMERCIAL',
        'Comercial',
        25,
        ROUND(v_commercial_score, 2),
        ROUND(v_commercial_score * 0.25, 2),
        jsonb_build_object(
            'sales_growth', v_sales_growth,
            'margin_pct', v_margin_pct,
            'discount_pct', v_discount_pct
        )
    ),
    (
        p_company_id,
        v_score_result_id,
        'FINANCIERO',
        'Financiero',
        25,
        ROUND(v_financial_score, 2),
        ROUND(v_financial_score * 0.25, 2),
        jsonb_build_object('dso', v_dso)
    ),
    (
        p_company_id,
        v_score_result_id,
        'STOCK',
        'Stock',
        20,
        ROUND(v_stock_score, 2),
        ROUND(v_stock_score * 0.20, 2),
        jsonb_build_object('critical_stock', v_critical_stock)
    ),
    (
        p_company_id,
        v_score_result_id,
        'EJECUCION',
        'Ejecución',
        20,
        ROUND(v_execution_score, 2),
        ROUND(v_execution_score * 0.20, 2),
        jsonb_build_object('overdue_actions_pct', v_overdue_pct)
    ),
    (
        p_company_id,
        v_score_result_id,
        'CALIDAD_DATOS',
        'Calidad de datos',
        10,
        ROUND(v_data_score, 2),
        ROUND(v_data_score * 0.10, 2),
        jsonb_build_object('data_quality', v_data_quality)
    );

    -- Drivers negativos
    IF v_margin_pct < 25 THEN
        INSERT INTO faro.score_drivers (
            company_id,
            score_result_id,
            driver_type,
            title,
            description,
            impact_value,
            source_type
        )
        VALUES (
            p_company_id,
            v_score_result_id,
            'NEGATIVE',
            'Margen inferior al objetivo',
            'El margen bruto está por debajo del nivel de referencia.',
            v_margin_pct,
            'KPI-003'
        );
    END IF;

    IF v_discount_pct > 8 THEN
        INSERT INTO faro.score_drivers (
            company_id,
            score_result_id,
            driver_type,
            title,
            description,
            impact_value,
            source_type
        )
        VALUES (
            p_company_id,
            v_score_result_id,
            'NEGATIVE',
            'Descuento comercial elevado',
            'El descuento promedio supera el umbral esperado.',
            v_discount_pct,
            'KPI-004'
        );
    END IF;

    IF v_dso > 30 THEN
        INSERT INTO faro.score_drivers (
            company_id,
            score_result_id,
            driver_type,
            title,
            description,
            impact_value,
            source_type
        )
        VALUES (
            p_company_id,
            v_score_result_id,
            'NEGATIVE',
            'Días de cobranza elevados',
            'El DSO afecta la caja y el riesgo financiero.',
            v_dso,
            'KPI-005'
        );
    END IF;

    -- Drivers positivos
    IF v_sales_growth > 0 THEN
        INSERT INTO faro.score_drivers (
            company_id,
            score_result_id,
            driver_type,
            title,
            description,
            impact_value,
            source_type
        )
        VALUES (
            p_company_id,
            v_score_result_id,
            'POSITIVE',
            'Ventas en crecimiento',
            'Las ventas muestran evolución positiva.',
            v_sales_growth,
            'KPI-002'
        );
    END IF;

    RETURN jsonb_build_object(
        'score_result_id', v_score_result_id,
        'score_value', v_final_score,
        'commercial_score', ROUND(v_commercial_score, 2),
        'financial_score', ROUND(v_financial_score, 2),
        'stock_score', ROUND(v_stock_score, 2),
        'execution_score', ROUND(v_execution_score, 2),
        'data_score', ROUND(v_data_score, 2)
    );
END;
$$;
```

---

# 18. Generar reporte ejecutivo MVP

```sql
CREATE OR REPLACE FUNCTION faro.fn_generate_executive_report_mvp(
    p_company_id UUID,
    p_period_start DATE,
    p_period_end DATE,
    p_generated_by UUID DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_score_result_id UUID;
    v_score NUMERIC;
    v_main_tension TEXT;
    v_diagnosis TEXT;
    v_recommended_focus TEXT;
    v_report_id UUID;
BEGIN
    SELECT
        id,
        score_value,
        recommended_focus
    INTO
        v_score_result_id,
        v_score,
        v_recommended_focus
    FROM faro.score_results
    WHERE company_id = p_company_id
      AND period_start = p_period_start
      AND period_end = p_period_end
      AND formula_version = 'MVP-1'
    ORDER BY created_at DESC
    LIMIT 1;

    SELECT tr.title
    INTO v_main_tension
    FROM faro.tension_results tr
    WHERE tr.company_id = p_company_id
      AND tr.period_start = p_period_start
      AND tr.period_end = p_period_end
      AND tr.status = 'ACTIVE'
    ORDER BY tr.created_at DESC
    LIMIT 1;

    SELECT dr.diagnosis_text
    INTO v_diagnosis
    FROM faro.diagnosis_results dr
    WHERE dr.company_id = p_company_id
      AND dr.period_start = p_period_start
      AND dr.period_end = p_period_end
    ORDER BY dr.created_at DESC
    LIMIT 1;

    SELECT id
    INTO v_report_id
    FROM faro.executive_reports
    WHERE company_id = p_company_id
      AND period_start = p_period_start
      AND period_end = p_period_end
    ORDER BY created_at DESC
    LIMIT 1;

    IF v_report_id IS NULL THEN
        INSERT INTO faro.executive_reports (
            company_id,
            score_result_id,
            title,
            period_start,
            period_end,
            status,
            executive_summary,
            main_tension,
            recommended_focus,
            generated_by
        )
        VALUES (
            p_company_id,
            v_score_result_id,
            'Reporte ejecutivo FARO — ' || p_period_start || ' a ' || p_period_end,
            p_period_start,
            p_period_end,
            'GENERATED',
            COALESCE(v_diagnosis, 'No se detectaron tensiones críticas en el período.'),
            v_main_tension,
            v_recommended_focus,
            p_generated_by
        )
        RETURNING id INTO v_report_id;
    ELSE
        UPDATE faro.executive_reports
        SET
            score_result_id = v_score_result_id,
            status = 'GENERATED',
            executive_summary = COALESCE(v_diagnosis, 'No se detectaron tensiones críticas en el período.'),
            main_tension = v_main_tension,
            recommended_focus = v_recommended_focus,
            generated_by = p_generated_by,
            updated_at = now()
        WHERE id = v_report_id;

        DELETE FROM faro.report_sections
        WHERE executive_report_id = v_report_id;
    END IF;

    INSERT INTO faro.report_sections (
        company_id,
        executive_report_id,
        section_order,
        section_code,
        title,
        content,
        payload
    )
    VALUES
    (
        p_company_id,
        v_report_id,
        1,
        'SCORE',
        'FARO Score',
        'Score ejecutivo del período.',
        jsonb_build_object('score', v_score)
    ),
    (
        p_company_id,
        v_report_id,
        2,
        'MAIN_TENSION',
        'Tensión principal',
        COALESCE(v_main_tension, 'Sin tensión crítica principal.'),
        '{}'::jsonb
    ),
    (
        p_company_id,
        v_report_id,
        3,
        'DIAGNOSIS',
        'Diagnóstico ejecutivo',
        COALESCE(v_diagnosis, 'Sin diagnóstico crítico generado.'),
        '{}'::jsonb
    ),
    (
        p_company_id,
        v_report_id,
        4,
        'ALERTS',
        'Alertas críticas',
        'Alertas abiertas del período.',
        (
            SELECT COALESCE(jsonb_agg(
                jsonb_build_object(
                    'title', ar.title,
                    'severity', ar.severity,
                    'area', ar.area,
                    'message', ar.message
                )
            ), '[]'::jsonb)
            FROM faro.alert_results ar
            WHERE ar.company_id = p_company_id
              AND ar.period_start = p_period_start
              AND ar.period_end = p_period_end
        )
    ),
    (
        p_company_id,
        v_report_id,
        5,
        'ACTIONS',
        'Acciones ejecutivas',
        'Acciones abiertas o vencidas relacionadas al período.',
        (
            SELECT COALESCE(jsonb_agg(
                jsonb_build_object(
                    'title', a.title,
                    'area', a.area,
                    'priority', a.priority,
                    'status', a.status,
                    'due_date', a.due_date
                )
            ), '[]'::jsonb)
            FROM faro.actions a
            WHERE a.company_id = p_company_id
              AND a.created_at::DATE <= p_period_end
              AND a.status NOT IN ('CLOSED', 'CANCELLED')
        )
    ),
    (
        p_company_id,
        v_report_id,
        6,
        'FOCUS',
        'Foco recomendado',
        COALESCE(v_recommended_focus, 'Mantener seguimiento ejecutivo.'),
        '{}'::jsonb
    );

    RETURN jsonb_build_object(
        'executive_report_id', v_report_id,
        'score', v_score,
        'main_tension', v_main_tension,
        'recommended_focus', v_recommended_focus
    );
END;
$$;
```

---

# 19. Orquestador general del ciclo MVP

Esta función ejecuta todo el ciclo sobre una carga.

```sql
CREATE OR REPLACE FUNCTION faro.fn_run_mvp_cycle(
    p_company_id UUID,
    p_raw_import_id UUID,
    p_period_start DATE,
    p_period_end DATE,
    p_user_id UUID DEFAULT NULL,
    p_auto_create_actions BOOLEAN DEFAULT TRUE
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_validation JSONB;
    v_masters JSONB;
    v_facts JSONB;
    v_kpis JSONB;
    v_signals JSONB;
    v_alerts JSONB;
    v_tensions JSONB;
    v_diagnosis JSONB;
    v_actions JSONB := '{}'::jsonb;
    v_score JSONB;
    v_report JSONB;
BEGIN
    -- 1. Definiciones base
    PERFORM faro.fn_seed_mvp_definitions();

    -- 2. Validación
    v_validation := faro.fn_validate_mvp_import(
        p_company_id,
        p_raw_import_id
    );

    -- 3. Maestros
    v_masters := faro.fn_rebuild_masters_from_import(
        p_company_id,
        p_raw_import_id
    );

    -- 4. Facts
    v_facts := faro.fn_build_facts_from_import(
        p_company_id,
        p_raw_import_id
    );

    -- 5. KPIs
    v_kpis := faro.fn_calculate_kpis_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 6. Señales
    v_signals := faro.fn_evaluate_signals_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 7. Alertas
    v_alerts := faro.fn_evaluate_alerts_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 8. Tensiones
    v_tensions := faro.fn_evaluate_tensions_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 9. Diagnóstico y recomendaciones
    v_diagnosis := faro.fn_generate_diagnosis_and_recommendations_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 10. Acciones opcionales
    IF p_auto_create_actions THEN
        v_actions := faro.fn_create_actions_from_recommendations_mvp(
            p_company_id,
            p_period_start,
            p_period_end,
            p_user_id
        );
    END IF;

    -- 11. Score
    v_score := faro.fn_calculate_faro_score_mvp(
        p_company_id,
        p_period_start,
        p_period_end
    );

    -- 12. Reporte
    v_report := faro.fn_generate_executive_report_mvp(
        p_company_id,
        p_period_start,
        p_period_end,
        p_user_id
    );

    -- 13. Auditoría
    INSERT INTO faro.audit_logs (
        company_id,
        user_id,
        entity_type,
        entity_id,
        action,
        metadata
    )
    VALUES (
        p_company_id,
        p_user_id,
        'MVP_CYCLE',
        p_raw_import_id,
        'RUN_MVP_CYCLE',
        jsonb_build_object(
            'period_start', p_period_start,
            'period_end', p_period_end,
            'validation', v_validation,
            'masters', v_masters,
            'facts', v_facts,
            'kpis', v_kpis,
            'signals', v_signals,
            'alerts', v_alerts,
            'tensions', v_tensions,
            'diagnosis', v_diagnosis,
            'actions', v_actions,
            'score', v_score,
            'report', v_report
        )
    );

    RETURN jsonb_build_object(
        'status', 'OK',
        'company_id', p_company_id,
        'raw_import_id', p_raw_import_id,
        'period_start', p_period_start,
        'period_end', p_period_end,
        'validation', v_validation,
        'masters', v_masters,
        'facts', v_facts,
        'kpis', v_kpis,
        'signals', v_signals,
        'alerts', v_alerts,
        'tensions', v_tensions,
        'diagnosis', v_diagnosis,
        'actions', v_actions,
        'score', v_score,
        'report', v_report
    );
END;
$$;
```

---

# 20. Ejemplo de ejecución

```sql
SELECT faro.fn_run_mvp_cycle(
    '00000000-0000-0000-0000-000000000001'::uuid,
    '00000000-0000-0000-0000-000000000999'::uuid,
    '2026-05-01'::date,
    '2026-05-31'::date,
    '00000000-0000-0000-0000-000000000111'::uuid,
    TRUE
);
```

---

# 21. Qué hace realmente este paquete

Con estas funciones, FARO ya puede hacer el ciclo MVP:

```text
Carga validada
→ maestros actualizados
→ facts generados
→ KPIs calculados
→ señales evaluadas
→ alertas creadas
→ tensión detectada
→ diagnóstico generado
→ recomendaciones creadas
→ acciones asignadas
→ Score calculado
→ reporte ejecutivo generado
→ auditoría registrada
```

Esto ya no es un dashboard.
Esto ya empieza a ser **motor de dirección**.

---

# 22. Ajuste técnico importante

En el modelo anterior, `kpi_results` tenía un `UNIQUE` con `branch_id`, pero en PostgreSQL los `NULL` no chocan entre sí. Por eso usé `DELETE + INSERT` en `fn_store_kpi_result`.

Más adelante conviene reemplazar eso por un índice único con `COALESCE`.

```sql
CREATE UNIQUE INDEX IF NOT EXISTS ux_kpi_results_company_kpi_period_branch
ON faro.kpi_results (
    company_id,
    kpi_definition_id,
    period_start,
    period_end,
    COALESCE(branch_id, '00000000-0000-0000-0000-000000000000'::uuid)
);
```

Esto es detalle técnico, pero detalle que evita futuros fantasmas. Y en sistemas de dirección, los fantasmas salen caros.