Modelo SQL del MVP

CREATE TABLE raw_imports (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id      UUID NOT NULL REFERENCES companies(id),
  domain          TEXT NOT NULL -- 'sales' | 'stock' | 'receivables',
  filename        TEXT NOT NULL,
  file_hash       TEXT NOT NULL -- SHA-256 del archivo completo,
  status          TEXT NOT NULL -- received | parsed | validated | failed,
  rows_total      INTEGER,
  rows_ok         INTEGER,
  rows_error      INTEGER,
  uploaded_by     UUID REFERENCES users(id),
  uploaded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  parsed_at       TIMESTAMPTZ,
  metadata        JSONB -- locale, charset, separador, etc.
);

CREATE TABLE raw_rows (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  raw_import_id   UUID NOT NULL REFERENCES raw_imports(id),
  row_index       INTEGER NOT NULL -- línea en el archivo original,
  row_hash        TEXT NOT NULL -- SHA-256 del row completo,
  payload         JSONB NOT NULL -- el row tal cual entró, sin transformar,
  ingested_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_raw_rows_import ON raw_rows(raw_import_id);
CREATE INDEX idx_raw_imports_company ON raw_imports(company_id, uploaded_at);

CREATE TABLE staging_sales (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  raw_row_id       UUID NOT NULL REFERENCES raw_rows(id) -- LINAJE,
  company_id       UUID NOT NULL,
  factura_id       TEXT NOT NULL,
  fecha            DATE NOT NULL,
  cliente_id       TEXT NOT NULL,
  producto_id      TEXT NOT NULL,
  sucursal_id      TEXT NOT NULL,
  vendedor_id      TEXT,
  cantidad         NUMERIC(14,4) NOT NULL CHECK (cantidad > 0),
  precio_unitario  NUMERIC(14,4) NOT NULL CHECK (precio_unitario >= 0),
  descuento_pct    NUMERIC(5,2) DEFAULT 0,
  importe_bruto    NUMERIC(14,4) NOT NULL,
  importe_neto     NUMERIC(14,4) NOT NULL,
  costo_unitario   NUMERIC(14,4),
  moneda           CHAR(3) DEFAULT 'ARS',
  canal            TEXT,
  condicion_pago   TEXT,
  validations      JSONB -- {"unique_id":"ok","date_format":"ok","bruto_neto_match":"warning"},
  loaded_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (company_id, factura_id) -- idempotencia
);

CREATE TABLE fact_sales (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id      UUID NOT NULL,
  period          DATE NOT NULL -- truncado a día/mes según granularidad,
  factura_id      TEXT NOT NULL,
  fecha           DATE NOT NULL,
  cliente_id      UUID REFERENCES master_customers(id),
  producto_id     UUID REFERENCES master_products(id),
  sucursal_id     UUID REFERENCES branches(id),
  cantidad        NUMERIC(14,4),
  importe_neto    NUMERIC(14,4),
  costo_total     NUMERIC(14,4) -- calculado,
  margen_pct      NUMERIC(5,2) -- calculado,
  staging_id      UUID REFERENCES staging_sales(id) -- LINAJE
) PARTITION BY RANGE (period);

CREATE INDEX idx_fact_sales_company_period ON fact_sales(company_id, period);

CREATE TABLE kpi_catalog (
  codigo            TEXT PRIMARY KEY -- 'KPI-001' .. 'KPI-400',
  nombre            TEXT NOT NULL,
  area              TEXT,
  modulo            TEXT,
  formula_sql       TEXT NOT NULL -- query parametrizada,
  polaridad         TEXT NOT NULL -- 'positiva' | 'negativa' | 'neutral',
  umbral_verde      NUMERIC,
  umbral_ambar      NUMERIC,
  umbral_rojo       NUMERIC,
  dim_score         TEXT -- 'DIM-01' .. 'DIM-10',
  peso_score        NUMERIC(3,1),
  frecuencia        TEXT,
  responsable_rol   TEXT,
  mvp               BOOLEAN DEFAULT false,
  version           TEXT NOT NULL DEFAULT 'v1.0'
);

CREATE TABLE kpi_calculations (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id    UUID NOT NULL,
  kpi_codigo    TEXT NOT NULL REFERENCES kpi_catalog(codigo),
  period        DATE NOT NULL,
  valor         NUMERIC(18,4) NOT NULL,
  banda         TEXT -- 'verde' | 'ambar' | 'rojo',
  confianza     NUMERIC(3,2) -- 0..1, baja si faltan datos,
  computed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  formula_version TEXT -- snapshot de la fórmula usada,
  UNIQUE (company_id, kpi_codigo, period)
);

CREATE TABLE tension_catalog (
  codigo              TEXT PRIMARY KEY -- 'TNS-001' .. 'TNS-300',
  nombre              TEXT NOT NULL,
  area                TEXT,
  tipo                TEXT,
  definicion_ejecutiva TEXT,
  logica_dsl          TEXT NOT NULL -- pseudocódigo evaluable,
  logica_sql          TEXT -- compilada,
  kpis_origen         TEXT[] -- ['KPI-001','KPI-002'],
  umbral_ambar        TEXT,
  umbral_rojo         TEXT,
  severidad           SMALLINT CHECK (severidad BETWEEN 1 AND 5),
  peso_score          NUMERIC(3,1),
  ind_aplicable       TEXT[] -- ['IND-01','IND-02'] o ['IND-ALL'],
  accion_canonica     TEXT REFERENCES action_catalog(codigo),
  mvp                 BOOLEAN DEFAULT false,
  version             TEXT NOT NULL DEFAULT 'v1.0'
);

CREATE TABLE tension_detections (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id      UUID NOT NULL,
  tension_codigo  TEXT NOT NULL REFERENCES tension_catalog(codigo),
  period          DATE NOT NULL,
  banda           TEXT -- 'ambar' | 'rojo',
  kpis_evidence   JSONB -- {"KPI-001":{"valor":1.12,"delta":"+12%"},...},
  detected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_at     TIMESTAMPTZ,
  action_id       UUID REFERENCES action_instances(id),
  UNIQUE (company_id, tension_codigo, period)
);

CREATE TABLE action_instances (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id          UUID NOT NULL,
  action_codigo       TEXT NOT NULL REFERENCES action_catalog(codigo),
  tension_detection_id UUID REFERENCES tension_detections(id),
  responsable_id      UUID REFERENCES users(id),
  accountable_id      UUID REFERENCES users(id),
  estado              TEXT NOT NULL -- Nueva|En análisis|En ejecución|En verificación|Cerrada,
  prioridad           TEXT -- P1|P2|P3|P4,
  vence_at            TIMESTAMPTZ NOT NULL,
  iniciada_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  cerrada_at          TIMESTAMPTZ,
  evidencia_id        UUID REFERENCES evidence_files(id) -- OBLIGATORIO al cerrar,
  score_impact_actual NUMERIC(4,1) -- medido post-cierre
);

CREATE TABLE action_workflow_log (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  action_id       UUID NOT NULL REFERENCES action_instances(id),
  estado_from     TEXT,
  estado_to       TEXT NOT NULL,
  changed_by      UUID REFERENCES users(id),
  changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  comentario      TEXT
);

CREATE TABLE faro_score_snapshots (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id      UUID NOT NULL,
  period          DATE NOT NULL,
  score_total     NUMERIC(5,2) NOT NULL -- 0..100,
  nivel           SMALLINT NOT NULL -- 1..5,
  banda           TEXT NOT NULL -- 'Reactiva'..'Dirigida por sistema',
  drivers         JSONB -- top movimientos vs período anterior,
  computed_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (company_id, period)
);

CREATE TABLE faro_score_components (
  snapshot_id     UUID NOT NULL REFERENCES faro_score_snapshots(id),
  dim_codigo      TEXT NOT NULL -- 'DIM-01'..'DIM-10',
  valor           NUMERIC(5,2) NOT NULL,
  peso            NUMERIC(4,3) NOT NULL -- 0..1,
  contribucion    NUMERIC(5,2) -- valor × peso,
  kpis_input      JSONB -- {"KPI-001":74.5,...},
  PRIMARY KEY (snapshot_id, dim_codigo)
);

SELECT faro.fn_run_mvp_cycle(
    p_company_id => '00000000-0000-0000-0000-000000000001'::uuid,
    p_period     => '2026-05-01'::date,
    p_seed_mvp   => true,           -- carga definiciones base si faltan
    p_recompute_score => true
);
-- Devuelve: jsonb con counts por capa, errores y duración por paso