# Modelo SQL inicial — FARO Connect MVP

Este modelo está pensado para **PostgreSQL**, con arquitectura **monolito modular primero**, multiempresa desde el día uno y trazabilidad completa desde **RAW → Staging → Normalización → Maestros → Facts → KPIs → Alertas → Tensiones → Diagnóstico → Acciones → Evidencia → FARO Score**. Es exactamente la columna vertebral que necesita el MVP para probar que FARO no es un dashboard, sino un sistema de dirección ejecutiva accionable. 

---

# 1. Principios del modelo

## Decisiones técnicas base

| Decisión     | Criterio                                                                       |
| ------------ | ------------------------------------------------------------------------------ |
| Base         | PostgreSQL                                                                     |
| IDs          | UUID                                                                           |
| Multiempresa | `company_id` en toda tabla operativa                                           |
| Trazabilidad | RAW nunca se modifica                                                          |
| IA           | Se audita por `ai_requests` y `ai_responses`                                   |
| Score        | Guardado por período, componente y drivers                                     |
| Acciones     | Siempre con responsable, vencimiento y evidencia                               |
| Auditoría    | Tabla transversal `audit_logs`                                                 |
| MVP          | Ventas, stock, cobranza, KPIs, alertas, tensiones, acciones, evidencia y Score |

---

# 2. Estructura general de tablas

```text
CORE
companies
users
roles
permissions
role_permissions
user_roles
branches

INGESTA / RAW
raw_imports
raw_import_files
raw_rows
raw_errors

STAGING
staging_sales
staging_stock
staging_receivables

MAESTROS
master_products
master_customers
master_salespeople
master_product_categories

FACTS
fact_sales
fact_stock_snapshot
fact_receivables

MOTOR FARO
kpi_definitions
kpi_results
signal_definitions
signal_results
rule_definitions
alert_definitions
alert_results
tension_definitions
tension_results
diagnosis_results
recommendation_definitions
recommendation_results

EJECUCIÓN
actions
action_comments
action_evidence
action_status_history
raci_assignments
workflow_events

SCORE / REPORTES
score_results
score_components
score_drivers
executive_reports
report_sections

IA / AUDITORÍA
ai_requests
ai_responses
audit_logs
```

---

# 3. SQL inicial completo

## 3.1. Extensiones, schema y enums

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS faro;

SET search_path TO faro, public;

DO $$
BEGIN
    CREATE TYPE import_status AS ENUM (
        'RECEIVED',
        'RAW_STORED',
        'STAGING_READY',
        'VALIDATED',
        'NORMALIZED',
        'ERROR'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE source_type AS ENUM (
        'EXCEL',
        'CSV',
        'ERP',
        'CRM',
        'POS',
        'BANK',
        'API',
        'WHATSAPP',
        'DOCUMENT',
        'MANUAL'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE data_domain AS ENUM (
        'SALES',
        'STOCK',
        'RECEIVABLES',
        'PURCHASES',
        'FINANCE',
        'HR',
        'OPERATIONS',
        'GENERAL'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE severity_level AS ENUM (
        'LOW',
        'MEDIUM',
        'HIGH',
        'CRITICAL'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE priority_level AS ENUM (
        'LOW',
        'MEDIUM',
        'HIGH',
        'CRITICAL'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE action_status AS ENUM (
        'PENDING',
        'IN_PROGRESS',
        'BLOCKED',
        'OBSERVED',
        'VALIDATED',
        'CLOSED',
        'OVERDUE',
        'CANCELLED'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE evidence_type AS ENUM (
        'FILE',
        'COMMENT',
        'LINK',
        'SCREENSHOT',
        'VALUE_UPDATE',
        'DOCUMENT'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    CREATE TYPE report_status AS ENUM (
        'DRAFT',
        'GENERATED',
        'APPROVED',
        'ARCHIVED'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
```

---

# 4. CORE — Empresas, usuarios, roles y sucursales

## 4.1. Empresas

```sql
CREATE TABLE IF NOT EXISTS companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    legal_name TEXT,
    tax_id TEXT,
    industry TEXT,
    country TEXT DEFAULT 'Argentina',
    status TEXT NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 4.2. Usuarios

```sql
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL,
    password_hash TEXT,
    position TEXT,
    area TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT users_company_email_unique UNIQUE (company_id, email)
);
```

---

## 4.3. Roles

```sql
CREATE TABLE IF NOT EXISTS roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
    code TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    is_system_role BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT roles_company_code_unique UNIQUE (company_id, code)
);
```

---

## 4.4. Permisos

```sql
CREATE TABLE IF NOT EXISTS permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    module TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 4.5. Permisos por rol

```sql
CREATE TABLE IF NOT EXISTS role_permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT role_permissions_unique UNIQUE (role_id, permission_id)
);
```

---

## 4.6. Roles por usuario

```sql
CREATE TABLE IF NOT EXISTS user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT user_roles_unique UNIQUE (company_id, user_id, role_id)
);
```

---

## 4.7. Sucursales

```sql
CREATE TABLE IF NOT EXISTS branches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    code TEXT,
    name TEXT NOT NULL,
    city TEXT,
    province TEXT,
    country TEXT DEFAULT 'Argentina',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT branches_company_code_unique UNIQUE (company_id, code)
);
```

---

# 5. INGESTA / RAW

Acá está el seguro del sistema.
**RAW no se toca, no se corrige, no se interpreta. Se guarda como llegó.**

---

## 5.1. Importaciones

```sql
CREATE TABLE IF NOT EXISTS raw_imports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    uploaded_by UUID REFERENCES users(id),
    source_type source_type NOT NULL DEFAULT 'EXCEL',
    data_domain data_domain NOT NULL,
    original_file_name TEXT NOT NULL,
    file_size_bytes BIGINT,
    status import_status NOT NULL DEFAULT 'RECEIVED',
    total_rows INTEGER DEFAULT 0,
    raw_rows_count INTEGER DEFAULT 0,
    valid_rows_count INTEGER DEFAULT 0,
    rejected_rows_count INTEGER DEFAULT 0,
    observed_rows_count INTEGER DEFAULT 0,
    data_quality_score NUMERIC(5,2),
    period_from DATE,
    period_to DATE,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    error_summary JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 5.2. Archivos originales

```sql
CREATE TABLE IF NOT EXISTS raw_import_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    storage_provider TEXT,
    storage_path TEXT NOT NULL,
    file_hash TEXT,
    mime_type TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 5.3. Filas RAW

```sql
CREATE TABLE IF NOT EXISTS raw_rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    source_row_number INTEGER NOT NULL,
    raw_payload JSONB NOT NULL,
    row_hash TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT raw_rows_unique_source_row UNIQUE (raw_import_id, source_row_number)
);
```

---

## 5.4. Errores RAW / Staging

```sql
CREATE TABLE IF NOT EXISTS raw_errors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    raw_row_id UUID REFERENCES raw_rows(id) ON DELETE CASCADE,
    error_code TEXT NOT NULL,
    error_message TEXT NOT NULL,
    field_name TEXT,
    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 6. STAGING

Staging ya empieza a ordenar, pero todavía no es dato ejecutivo final.

---

## 6.1. Staging de ventas

```sql
CREATE TABLE IF NOT EXISTS staging_sales (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    raw_row_id UUID REFERENCES raw_rows(id) ON DELETE SET NULL,

    sale_date DATE,
    invoice_number TEXT,
    customer_code TEXT,
    customer_name TEXT,
    product_code TEXT,
    product_name TEXT,
    salesperson_name TEXT,
    branch_code TEXT,
    branch_name TEXT,

    quantity NUMERIC(18,4),
    gross_amount NUMERIC(18,2),
    discount_amount NUMERIC(18,2) DEFAULT 0,
    net_amount NUMERIC(18,2),
    cost_amount NUMERIC(18,2),
    currency TEXT DEFAULT 'ARS',

    validation_status TEXT NOT NULL DEFAULT 'PENDING',
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 6.2. Staging de stock

```sql
CREATE TABLE IF NOT EXISTS staging_stock (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    raw_row_id UUID REFERENCES raw_rows(id) ON DELETE SET NULL,

    snapshot_date DATE,
    product_code TEXT,
    product_name TEXT,
    branch_code TEXT,
    branch_name TEXT,

    stock_available NUMERIC(18,4),
    stock_reserved NUMERIC(18,4) DEFAULT 0,
    stock_minimum NUMERIC(18,4),
    stock_maximum NUMERIC(18,4),
    unit_cost NUMERIC(18,2),
    currency TEXT DEFAULT 'ARS',

    validation_status TEXT NOT NULL DEFAULT 'PENDING',
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 6.3. Staging de cobranza

```sql
CREATE TABLE IF NOT EXISTS staging_receivables (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID NOT NULL REFERENCES raw_imports(id) ON DELETE CASCADE,
    raw_row_id UUID REFERENCES raw_rows(id) ON DELETE SET NULL,

    customer_code TEXT,
    customer_name TEXT,
    invoice_number TEXT,
    issue_date DATE,
    due_date DATE,
    payment_date DATE,

    original_amount NUMERIC(18,2),
    paid_amount NUMERIC(18,2) DEFAULT 0,
    outstanding_amount NUMERIC(18,2),
    currency TEXT DEFAULT 'ARS',
    receivable_status TEXT,

    validation_status TEXT NOT NULL DEFAULT 'PENDING',
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 7. MAESTROS

Los maestros son la diferencia entre “planilla cargada” y “sistema serio”. Sin maestros, FARO se vuelve una licuadora de Excel.

---

## 7.1. Categorías de productos

```sql
CREATE TABLE IF NOT EXISTS master_product_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    code TEXT,
    name TEXT NOT NULL,
    parent_id UUID REFERENCES master_product_categories(id),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT product_categories_unique UNIQUE (company_id, code)
);
```

---

## 7.2. Productos

```sql
CREATE TABLE IF NOT EXISTS master_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    category_id UUID REFERENCES master_product_categories(id),
    source_code TEXT,
    normalized_code TEXT,
    name TEXT NOT NULL,
    normalized_name TEXT,
    unit TEXT,
    is_tractor_product BOOLEAN NOT NULL DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT products_company_normalized_code_unique UNIQUE (company_id, normalized_code)
);
```

---

## 7.3. Clientes

```sql
CREATE TABLE IF NOT EXISTS master_customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    source_code TEXT,
    normalized_code TEXT,
    name TEXT NOT NULL,
    normalized_name TEXT,
    tax_id TEXT,
    segment TEXT,
    credit_limit NUMERIC(18,2),
    payment_terms_days INTEGER,
    risk_level TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT customers_company_normalized_code_unique UNIQUE (company_id, normalized_code)
);
```

---

## 7.4. Vendedores

```sql
CREATE TABLE IF NOT EXISTS master_salespeople (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    branch_id UUID REFERENCES branches(id),
    user_id UUID REFERENCES users(id),
    source_name TEXT,
    normalized_name TEXT NOT NULL,
    email TEXT,
    commission_scheme TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT salespeople_company_name_unique UNIQUE (company_id, normalized_name)
);
```

---

# 8. FACTS — Modelo ejecutivo

Estas tablas son las que FARO usa para calcular KPIs.
No debería calcularse desde RAW directamente, salvo para auditoría.

---

## 8.1. Ventas

```sql
CREATE TABLE IF NOT EXISTS fact_sales (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID REFERENCES raw_imports(id),
    staging_sale_id UUID REFERENCES staging_sales(id),

    sale_date DATE NOT NULL,
    period_month DATE NOT NULL,

    branch_id UUID REFERENCES branches(id),
    customer_id UUID REFERENCES master_customers(id),
    product_id UUID REFERENCES master_products(id),
    salesperson_id UUID REFERENCES master_salespeople(id),

    invoice_number TEXT,
    quantity NUMERIC(18,4) NOT NULL DEFAULT 0,
    gross_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    discount_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    net_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    cost_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    margin_amount NUMERIC(18,2) GENERATED ALWAYS AS (net_amount - cost_amount) STORED,
    currency TEXT DEFAULT 'ARS',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 8.2. Stock

```sql
CREATE TABLE IF NOT EXISTS fact_stock_snapshot (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID REFERENCES raw_imports(id),
    staging_stock_id UUID REFERENCES staging_stock(id),

    snapshot_date DATE NOT NULL,
    period_month DATE NOT NULL,

    branch_id UUID REFERENCES branches(id),
    product_id UUID REFERENCES master_products(id),

    stock_available NUMERIC(18,4) NOT NULL DEFAULT 0,
    stock_reserved NUMERIC(18,4) NOT NULL DEFAULT 0,
    stock_minimum NUMERIC(18,4),
    stock_maximum NUMERIC(18,4),
    unit_cost NUMERIC(18,2),
    stock_value NUMERIC(18,2),
    is_critical BOOLEAN GENERATED ALWAYS AS (
        stock_minimum IS NOT NULL AND stock_available <= stock_minimum
    ) STORED,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 8.3. Cobranza

```sql
CREATE TABLE IF NOT EXISTS fact_receivables (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    raw_import_id UUID REFERENCES raw_imports(id),
    staging_receivable_id UUID REFERENCES staging_receivables(id),

    customer_id UUID REFERENCES master_customers(id),
    invoice_number TEXT,

    issue_date DATE,
    due_date DATE,
    payment_date DATE,
    period_month DATE NOT NULL,

    original_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    paid_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    outstanding_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    days_overdue INTEGER GENERATED ALWAYS AS (
        CASE 
            WHEN payment_date IS NULL AND due_date IS NOT NULL THEN GREATEST((CURRENT_DATE - due_date), 0)
            WHEN payment_date IS NOT NULL AND due_date IS NOT NULL THEN GREATEST((payment_date - due_date), 0)
            ELSE 0
        END
    ) STORED,
    status TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 9. MOTOR FARO — KPIs, señales, reglas, alertas y tensiones

---

## 9.1. Definición de KPIs

```sql
CREATE TABLE IF NOT EXISTS kpi_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    area TEXT NOT NULL,
    module TEXT NOT NULL,
    industry_scope TEXT DEFAULT 'MULTIINDUSTRIA',
    description TEXT,
    formula_text TEXT,
    formula_sql TEXT,
    data_sources TEXT[],
    frequency TEXT DEFAULT 'WEEKLY',
    owner_role TEXT,
    threshold_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    score_component TEXT,
    complexity_level TEXT DEFAULT 'BASIC',
    faro_version TEXT DEFAULT 'CORE',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.2. Resultados de KPIs

```sql
CREATE TABLE IF NOT EXISTS kpi_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    kpi_definition_id UUID NOT NULL REFERENCES kpi_definitions(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    branch_id UUID REFERENCES branches(id),

    value NUMERIC(18,4),
    previous_value NUMERIC(18,4),
    variation_absolute NUMERIC(18,4),
    variation_percentage NUMERIC(18,4),

    status TEXT,
    confidence_score NUMERIC(5,2),
    calculation_metadata JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT kpi_results_unique UNIQUE (
        company_id,
        kpi_definition_id,
        period_start,
        period_end,
        branch_id
    )
);
```

---

## 9.3. Señales

```sql
CREATE TABLE IF NOT EXISTS signal_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    area TEXT,
    related_kpi_code TEXT,
    logic_description TEXT,
    threshold_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

```sql
CREATE TABLE IF NOT EXISTS signal_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    signal_definition_id UUID NOT NULL REFERENCES signal_definitions(id),
    kpi_result_id UUID REFERENCES kpi_results(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,

    is_triggered BOOLEAN NOT NULL DEFAULT FALSE,
    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    message TEXT,
    evidence_payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.4. Reglas

```sql
CREATE TABLE IF NOT EXISTS rule_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    rule_type TEXT NOT NULL,
    condition_payload JSONB NOT NULL,
    output_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    priority priority_level NOT NULL DEFAULT 'MEDIUM',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.5. Alertas

```sql
CREATE TABLE IF NOT EXISTS alert_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    area TEXT,
    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    related_signal_codes TEXT[],
    related_kpi_codes TEXT[],
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

```sql
CREATE TABLE IF NOT EXISTS alert_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    alert_definition_id UUID NOT NULL REFERENCES alert_definitions(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,

    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    area TEXT,
    status TEXT NOT NULL DEFAULT 'OPEN',
    source_payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    reviewed_by UUID REFERENCES users(id),
    reviewed_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.6. Tensiones

```sql
CREATE TABLE IF NOT EXISTS tension_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    areas_involved TEXT[],
    description TEXT,
    symptoms TEXT[],
    detection_logic JSONB NOT NULL DEFAULT '{}'::jsonb,
    related_kpi_codes TEXT[],
    related_signal_codes TEXT[],
    related_alert_codes TEXT[],
    typical_priority priority_level NOT NULL DEFAULT 'MEDIUM',
    industry_scope TEXT DEFAULT 'MULTIINDUSTRIA',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

```sql
CREATE TABLE IF NOT EXISTS tension_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    tension_definition_id UUID NOT NULL REFERENCES tension_definitions(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,

    priority priority_level NOT NULL DEFAULT 'MEDIUM',
    severity severity_level NOT NULL DEFAULT 'MEDIUM',
    confidence_score NUMERIC(5,2),
    title TEXT NOT NULL,
    executive_summary TEXT,
    source_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    status TEXT NOT NULL DEFAULT 'ACTIVE',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.7. Diagnósticos

```sql
CREATE TABLE IF NOT EXISTS diagnosis_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    tension_result_id UUID REFERENCES tension_results(id) ON DELETE SET NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,

    title TEXT NOT NULL,
    diagnosis_text TEXT NOT NULL,
    root_cause_hypothesis TEXT,
    confidence_score NUMERIC(5,2),
    priority priority_level NOT NULL DEFAULT 'MEDIUM',

    positive_drivers JSONB NOT NULL DEFAULT '[]'::jsonb,
    negative_drivers JSONB NOT NULL DEFAULT '[]'::jsonb,
    recommended_focus TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 9.8. Recomendaciones

```sql
CREATE TABLE IF NOT EXISTS recommendation_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    related_tension_codes TEXT[],
    area TEXT,
    expected_impact TEXT,
    complexity_level TEXT DEFAULT 'BASIC',
    faro_version TEXT DEFAULT 'CORE',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

```sql
CREATE TABLE IF NOT EXISTS recommendation_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    recommendation_definition_id UUID REFERENCES recommendation_definitions(id),
    diagnosis_result_id UUID REFERENCES diagnosis_results(id) ON DELETE SET NULL,
    tension_result_id UUID REFERENCES tension_results(id) ON DELETE SET NULL,

    title TEXT NOT NULL,
    recommendation_text TEXT NOT NULL,
    priority priority_level NOT NULL DEFAULT 'MEDIUM',
    expected_impact TEXT,
    can_create_action BOOLEAN NOT NULL DEFAULT TRUE,
    status TEXT NOT NULL DEFAULT 'SUGGESTED',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 10. EJECUCIÓN — Acciones, workflow, RACI y evidencia

---

## 10.1. Acciones

```sql
CREATE TABLE IF NOT EXISTS actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    recommendation_result_id UUID REFERENCES recommendation_results(id) ON DELETE SET NULL,
    diagnosis_result_id UUID REFERENCES diagnosis_results(id) ON DELETE SET NULL,
    tension_result_id UUID REFERENCES tension_results(id) ON DELETE SET NULL,

    title TEXT NOT NULL,
    description TEXT,
    area TEXT,
    priority priority_level NOT NULL DEFAULT 'MEDIUM',
    status action_status NOT NULL DEFAULT 'PENDING',

    responsible_user_id UUID REFERENCES users(id),
    approver_user_id UUID REFERENCES users(id),

    due_date DATE NOT NULL,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    closed_at TIMESTAMPTZ,

    expected_impact TEXT,
    measurement_method TEXT,
    evidence_required BOOLEAN NOT NULL DEFAULT TRUE,

    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 10.2. Comentarios de acciones

```sql
CREATE TABLE IF NOT EXISTS action_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    action_id UUID NOT NULL REFERENCES actions(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id),
    comment TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 10.3. Evidencia

```sql
CREATE TABLE IF NOT EXISTS action_evidence (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    action_id UUID NOT NULL REFERENCES actions(id) ON DELETE CASCADE,

    evidence_type evidence_type NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    storage_path TEXT,
    external_url TEXT,
    value_payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    uploaded_by UUID REFERENCES users(id),
    validated_by UUID REFERENCES users(id),
    validated_at TIMESTAMPTZ,
    validation_status TEXT NOT NULL DEFAULT 'PENDING',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 10.4. Historial de estados

```sql
CREATE TABLE IF NOT EXISTS action_status_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    action_id UUID NOT NULL REFERENCES actions(id) ON DELETE CASCADE,

    old_status action_status,
    new_status action_status NOT NULL,
    changed_by UUID REFERENCES users(id),
    change_reason TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 10.5. RACI

```sql
CREATE TABLE IF NOT EXISTS raci_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    action_id UUID NOT NULL REFERENCES actions(id) ON DELETE CASCADE,

    user_id UUID REFERENCES users(id),
    role_type TEXT NOT NULL CHECK (role_type IN ('RESPONSIBLE', 'ACCOUNTABLE', 'CONSULTED', 'INFORMED')),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT raci_unique UNIQUE (action_id, user_id, role_type)
);
```

---

## 10.6. Eventos de workflow

```sql
CREATE TABLE IF NOT EXISTS workflow_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    action_id UUID REFERENCES actions(id) ON DELETE CASCADE,

    event_type TEXT NOT NULL,
    event_title TEXT NOT NULL,
    event_description TEXT,
    triggered_by UUID REFERENCES users(id),
    event_payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 11. FARO SCORE

El Score no es promedio simple. En MVP usamos fórmula inicial, pero dejamos preparada la estructura para componentes, drivers y explicación.

---

## 11.1. Resultado general del Score

```sql
CREATE TABLE IF NOT EXISTS score_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,

    score_value NUMERIC(5,2) NOT NULL CHECK (score_value >= 0 AND score_value <= 100),
    previous_score_value NUMERIC(5,2),
    variation NUMERIC(5,2),

    confidence_score NUMERIC(5,2),
    recommended_focus TEXT,
    explanation TEXT,
    formula_version TEXT NOT NULL DEFAULT 'MVP-1',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT score_results_unique UNIQUE (company_id, period_start, period_end, formula_version)
);
```

---

## 11.2. Componentes del Score

```sql
CREATE TABLE IF NOT EXISTS score_components (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    score_result_id UUID NOT NULL REFERENCES score_results(id) ON DELETE CASCADE,

    component_code TEXT NOT NULL,
    component_name TEXT NOT NULL,
    weight NUMERIC(5,2) NOT NULL,
    value NUMERIC(5,2) NOT NULL CHECK (value >= 0 AND value <= 100),
    weighted_value NUMERIC(5,2) NOT NULL,

    source_payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 11.3. Drivers del Score

```sql
CREATE TABLE IF NOT EXISTS score_drivers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    score_result_id UUID NOT NULL REFERENCES score_results(id) ON DELETE CASCADE,

    driver_type TEXT NOT NULL CHECK (driver_type IN ('POSITIVE', 'NEGATIVE')),
    title TEXT NOT NULL,
    description TEXT,
    impact_value NUMERIC(8,2),
    source_type TEXT,
    source_id UUID,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 12. REPORTES EJECUTIVOS

---

## 12.1. Reportes

```sql
CREATE TABLE IF NOT EXISTS executive_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    score_result_id UUID REFERENCES score_results(id),

    title TEXT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status report_status NOT NULL DEFAULT 'DRAFT',

    executive_summary TEXT,
    main_tension TEXT,
    recommended_focus TEXT,

    generated_by UUID REFERENCES users(id),
    approved_by UUID REFERENCES users(id),
    approved_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 12.2. Secciones del reporte

```sql
CREATE TABLE IF NOT EXISTS report_sections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    executive_report_id UUID NOT NULL REFERENCES executive_reports(id) ON DELETE CASCADE,

    section_order INTEGER NOT NULL,
    section_code TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT report_section_unique UNIQUE (executive_report_id, section_code)
);
```

---

# 13. IA CONTROLADA

La IA no calcula.
La IA no inventa.
La IA redacta sobre datos estructurados.

---

## 13.1. Requests IA

```sql
CREATE TABLE IF NOT EXISTS ai_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id),

    use_case TEXT NOT NULL,
    model_provider TEXT,
    model_name TEXT,

    input_payload JSONB NOT NULL,
    allowed_output JSONB NOT NULL DEFAULT '[]'::jsonb,
    forbidden_output JSONB NOT NULL DEFAULT '[]'::jsonb,

    status TEXT NOT NULL DEFAULT 'PENDING',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 13.2. Responses IA

```sql
CREATE TABLE IF NOT EXISTS ai_responses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    ai_request_id UUID NOT NULL REFERENCES ai_requests(id) ON DELETE CASCADE,

    output_payload JSONB NOT NULL,
    output_text TEXT,
    validation_status TEXT NOT NULL DEFAULT 'PENDING',
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 14. AUDITORÍA

Auditoría básica desde el día uno.
Sin auditoría, el sistema termina siendo una discusión de pasillo con base de datos.

---

```sql
CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id),

    entity_type TEXT NOT NULL,
    entity_id UUID,
    action TEXT NOT NULL,

    old_values JSONB,
    new_values JSONB,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,

    ip_address TEXT,
    user_agent TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

# 15. Índices recomendados

```sql
CREATE INDEX IF NOT EXISTS idx_users_company_id ON users(company_id);
CREATE INDEX IF NOT EXISTS idx_branches_company_id ON branches(company_id);

CREATE INDEX IF NOT EXISTS idx_raw_imports_company_status ON raw_imports(company_id, status);
CREATE INDEX IF NOT EXISTS idx_raw_imports_company_domain ON raw_imports(company_id, data_domain);
CREATE INDEX IF NOT EXISTS idx_raw_rows_import_id ON raw_rows(raw_import_id);
CREATE INDEX IF NOT EXISTS idx_raw_rows_payload_gin ON raw_rows USING GIN (raw_payload);

CREATE INDEX IF NOT EXISTS idx_staging_sales_company_import ON staging_sales(company_id, raw_import_id);
CREATE INDEX IF NOT EXISTS idx_staging_stock_company_import ON staging_stock(company_id, raw_import_id);
CREATE INDEX IF NOT EXISTS idx_staging_receivables_company_import ON staging_receivables(company_id, raw_import_id);

CREATE INDEX IF NOT EXISTS idx_products_company ON master_products(company_id);
CREATE INDEX IF NOT EXISTS idx_customers_company ON master_customers(company_id);
CREATE INDEX IF NOT EXISTS idx_salespeople_company ON master_salespeople(company_id);

CREATE INDEX IF NOT EXISTS idx_fact_sales_company_period ON fact_sales(company_id, period_month);
CREATE INDEX IF NOT EXISTS idx_fact_sales_company_branch ON fact_sales(company_id, branch_id);
CREATE INDEX IF NOT EXISTS idx_fact_sales_company_product ON fact_sales(company_id, product_id);
CREATE INDEX IF NOT EXISTS idx_fact_sales_company_salesperson ON fact_sales(company_id, salesperson_id);

CREATE INDEX IF NOT EXISTS idx_fact_stock_company_period ON fact_stock_snapshot(company_id, period_month);
CREATE INDEX IF NOT EXISTS idx_fact_stock_company_product ON fact_stock_snapshot(company_id, product_id);
CREATE INDEX IF NOT EXISTS idx_fact_stock_critical ON fact_stock_snapshot(company_id, is_critical);

CREATE INDEX IF NOT EXISTS idx_fact_receivables_company_period ON fact_receivables(company_id, period_month);
CREATE INDEX IF NOT EXISTS idx_fact_receivables_customer ON fact_receivables(company_id, customer_id);

CREATE INDEX IF NOT EXISTS idx_kpi_results_company_period ON kpi_results(company_id, period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_signal_results_company_period ON signal_results(company_id, period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_alert_results_company_status ON alert_results(company_id, status);
CREATE INDEX IF NOT EXISTS idx_tension_results_company_status ON tension_results(company_id, status);

CREATE INDEX IF NOT EXISTS idx_actions_company_status ON actions(company_id, status);
CREATE INDEX IF NOT EXISTS idx_actions_responsible ON actions(company_id, responsible_user_id);
CREATE INDEX IF NOT EXISTS idx_actions_due_date ON actions(company_id, due_date);
CREATE INDEX IF NOT EXISTS idx_action_evidence_action ON action_evidence(action_id);

CREATE INDEX IF NOT EXISTS idx_score_results_company_period ON score_results(company_id, period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_reports_company_period ON executive_reports(company_id, period_start, period_end);

CREATE INDEX IF NOT EXISTS idx_audit_logs_company_entity ON audit_logs(company_id, entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_ai_requests_company_use_case ON ai_requests(company_id, use_case);
```

---

# 16. Vistas útiles para el MVP

## 16.1. Vista de acciones vencidas

```sql
CREATE OR REPLACE VIEW vw_overdue_actions AS
SELECT
    a.company_id,
    a.id AS action_id,
    a.title,
    a.area,
    a.priority,
    a.status,
    a.due_date,
    u.full_name AS responsible_name,
    a.created_at
FROM actions a
LEFT JOIN users u ON u.id = a.responsible_user_id
WHERE a.status NOT IN ('CLOSED', 'CANCELLED')
  AND a.due_date < CURRENT_DATE;
```

---

## 16.2. Vista de stock crítico

```sql
CREATE OR REPLACE VIEW vw_critical_stock AS
SELECT
    fs.company_id,
    fs.snapshot_date,
    fs.period_month,
    b.name AS branch_name,
    p.name AS product_name,
    p.normalized_code AS product_code,
    fs.stock_available,
    fs.stock_minimum,
    fs.is_critical
FROM fact_stock_snapshot fs
LEFT JOIN branches b ON b.id = fs.branch_id
LEFT JOIN master_products p ON p.id = fs.product_id
WHERE fs.is_critical = TRUE;
```

---

## 16.3. Vista de resumen comercial

```sql
CREATE OR REPLACE VIEW vw_sales_summary_monthly AS
SELECT
    company_id,
    period_month,
    SUM(gross_amount) AS gross_sales,
    SUM(discount_amount) AS total_discounts,
    SUM(net_amount) AS net_sales,
    SUM(cost_amount) AS total_cost,
    SUM(margin_amount) AS gross_margin,
    CASE 
        WHEN SUM(net_amount) = 0 THEN 0
        ELSE ROUND((SUM(margin_amount) / SUM(net_amount)) * 100, 2)
    END AS gross_margin_pct,
    CASE 
        WHEN SUM(gross_amount) = 0 THEN 0
        ELSE ROUND((SUM(discount_amount) / SUM(gross_amount)) * 100, 2)
    END AS discount_pct
FROM fact_sales
GROUP BY company_id, period_month;
```

---

# 17. Seed inicial de KPIs MVP

Esto no son los 400 KPIs.
Esto es lo mínimo para que el MVP respire.

```sql
INSERT INTO kpi_definitions (
    code,
    name,
    area,
    module,
    description,
    formula_text,
    data_sources,
    frequency,
    owner_role,
    score_component,
    complexity_level,
    faro_version
)
VALUES
('KPI-001', 'Ventas netas', 'Comercial', 'Ventas',
 'Mide el total de ventas netas descontando descuentos y devoluciones.',
 'Ventas netas = ventas brutas - descuentos - devoluciones',
 ARRAY['fact_sales'], 'WEEKLY', 'Gerente Comercial', 'COMERCIAL', 'BASIC', 'CORE'),

('KPI-002', 'Variación de ventas %', 'Comercial', 'Ventas',
 'Mide la variación porcentual de ventas contra el período anterior.',
 'Variación % = (ventas actuales - ventas anteriores) / ventas anteriores',
 ARRAY['fact_sales'], 'WEEKLY', 'Gerente Comercial', 'COMERCIAL', 'BASIC', 'CORE'),

('KPI-003', 'Margen bruto %', 'Comercial / Finanzas', 'Ventas',
 'Mide la rentabilidad bruta de las ventas.',
 'Margen bruto % = margen bruto / ventas netas',
 ARRAY['fact_sales'], 'WEEKLY', 'Gerente Comercial', 'COMERCIAL', 'BASIC', 'CORE'),

('KPI-004', 'Descuento promedio %', 'Comercial', 'Ventas',
 'Mide el peso de los descuentos sobre las ventas brutas.',
 'Descuento % = descuentos / ventas brutas',
 ARRAY['fact_sales'], 'WEEKLY', 'Gerente Comercial', 'COMERCIAL', 'BASIC', 'CORE'),

('KPI-005', 'Días de cobranza', 'Finanzas', 'Cobranza',
 'Mide el plazo promedio de recuperación de cuentas por cobrar.',
 'DSO = cuentas por cobrar / ventas diarias promedio',
 ARRAY['fact_receivables', 'fact_sales'], 'WEEKLY', 'Gerente Financiero', 'FINANCIERO', 'BASIC', 'CORE'),

('KPI-006', 'Productos bajo stock mínimo', 'Stock', 'Inventario',
 'Mide la cantidad de productos con stock disponible menor o igual al mínimo.',
 'Cantidad de productos donde stock disponible <= stock mínimo',
 ARRAY['fact_stock_snapshot'], 'DAILY', 'Encargado de Stock', 'STOCK', 'BASIC', 'CORE'),

('KPI-007', 'Acciones vencidas %', 'Ejecución', 'Workflow',
 'Mide el porcentaje de acciones vencidas sobre acciones abiertas.',
 'Acciones vencidas % = acciones vencidas / acciones abiertas',
 ARRAY['actions'], 'WEEKLY', 'Gerencia General', 'EJECUCION', 'BASIC', 'CORE'),

('KPI-008', 'Calidad de datos %', 'Sistemas / Data', 'Data Quality',
 'Mide el porcentaje de registros válidos sobre el total procesado.',
 'Calidad de datos % = registros válidos / registros totales',
 ARRAY['raw_imports'], 'WEEKLY', 'Analista de Datos', 'CALIDAD_DATOS', 'BASIC', 'CORE')
ON CONFLICT (code) DO NOTHING;
```

---

# 18. Seed inicial de tensión demo

```sql
INSERT INTO tension_definitions (
    code,
    name,
    areas_involved,
    description,
    symptoms,
    detection_logic,
    related_kpi_codes,
    related_signal_codes,
    typical_priority,
    industry_scope
)
VALUES (
    'TNS-001',
    'Crecimiento no rentable',
    ARRAY['Comercial', 'Finanzas', 'Stock', 'Dirección'],
    'La empresa aumenta ventas, pero deteriora margen, descuentos y/o cobranza.',
    ARRAY[
        'Ventas suben',
        'Margen baja',
        'Descuentos suben',
        'Cobranza empeora',
        'Stock crítico en productos tractores'
    ],
    '{
        "conditions": [
            {"kpi": "KPI-002", "operator": ">", "value": 10},
            {"kpi": "KPI-003", "operator": "decrease_points", "value": 3},
            {"kpi": "KPI-004", "operator": "increase_points", "value": 3}
        ],
        "optional_conditions": [
            {"kpi": "KPI-005", "operator": "increase_days", "value": 7},
            {"kpi": "KPI-006", "operator": ">", "value": 0}
        ],
        "minimum_required_conditions": 3
    }'::jsonb,
    ARRAY['KPI-002', 'KPI-003', 'KPI-004', 'KPI-005', 'KPI-006'],
    ARRAY['SIG-001', 'SIG-002', 'SIG-003', 'SIG-004', 'SIG-005'],
    'HIGH',
    'Comercial / Retail / Insumos / Mayorista / Distribución'
)
ON CONFLICT (code) DO NOTHING;
```

---

# 19. Seed inicial de acciones demo

```sql
INSERT INTO recommendation_definitions (
    code,
    name,
    description,
    related_tension_codes,
    area,
    expected_impact,
    complexity_level,
    faro_version
)
VALUES
('REC-001', 'Auditar descuentos comerciales',
 'Revisar descuentos por vendedor, producto, cliente y sucursal para detectar pérdida de margen.',
 ARRAY['TNS-001'], 'Comercial', 'Mejora de margen y control comercial', 'BASIC', 'CORE'),

('REC-002', 'Priorizar cobranza vencida',
 'Ordenar clientes con deuda vencida y activar gestión prioritaria de cobro.',
 ARRAY['TNS-001'], 'Finanzas', 'Mejora de caja y reducción de riesgo financiero', 'BASIC', 'CORE'),

('REC-003', 'Validar stock crítico',
 'Revisar productos tractores con stock bajo para evitar quiebres de venta.',
 ARRAY['TNS-001'], 'Stock', 'Reducción de riesgo operativo y pérdida de ventas', 'BASIC', 'CORE'),

('REC-004', 'Revisar esquema de comisión',
 'Simular comisión basada en margen y cobranza, no solamente en venta bruta.',
 ARRAY['TNS-001'], 'Dirección / RRHH / Comercial', 'Mejora de incentivos y rentabilidad', 'MEDIUM', 'PRO')
ON CONFLICT (code) DO NOTHING;
```

---

# 20. Qué falta después de este modelo

Este modelo ya permite construir el MVP, pero después hay que completar tres piezas:

## 20.1. Funciones de cálculo

Ejemplos:

* `calculate_kpi_results(company_id, period_start, period_end)`
* `evaluate_signals(company_id, period_start, period_end)`
* `evaluate_tensions(company_id, period_start, period_end)`
* `calculate_faro_score(company_id, period_start, period_end)`
* `generate_executive_report(company_id, period_start, period_end)`

---

## 20.2. Capa API FastAPI

Endpoints mínimos:

```text
/auth/login
/companies
/users
/imports/upload
/imports/{id}
/imports/{id}/raw
/imports/{id}/validate
/imports/{id}/normalize
/kpis/results
/alerts
/tensions
/diagnosis
/recommendations
/actions
/actions/{id}/evidence
/score
/reports
/ai/explain
```

---

## 20.3. Motor de reglas

Inicialmente puede ser simple:

```text
KPI calculado
→ señal evaluada
→ alerta creada
→ tensión evaluada
→ diagnóstico generado
→ recomendación sugerida
→ acción creada
→ evidencia exigida
→ Score recalculado
```

Nada de magia.
Primero reglas claras. Después sofisticación.

---

# 21. Recomendación ejecutiva

Para el MVP, yo no agregaría más tablas todavía.

Este modelo ya cubre:

1. Empresas y usuarios.
2. Carga Excel/CSV.
3. RAW.
4. Staging.
5. Normalización.
6. Maestros.
7. Facts.
8. KPIs.
9. Señales.
10. Alertas.
11. Tensiones.
12. Diagnóstico.
13. Recomendaciones.
14. Acciones.
15. RACI.
16. Workflow.
17. Evidencia.
18. Score.
19. Reporte.
20. IA controlada.
21. Auditoría.

La próxima pieza lógica sería armar:

**las funciones SQL / pseudocódigo del motor MVP**, especialmente:

```text
calculate_kpis()
evaluate_tensions()
generate_diagnosis()
create_recommended_actions()
calculate_faro_score()
```

Ahí FARO deja de ser estructura y empieza a comportarse como producto.