12 Evidencias Canónicas FARO Connect MVP

-- ============================================================
-- FARO-SQL-006 · V030__create_evidence_definitions.sql
-- Catálogo canónico de evidencias FARO MVP
-- Engine: PostgreSQL 15+
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.evidence_definitions (
  evidence_definition_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  evidence_code text NOT NULL,
  name text NOT NULL,
  short_name text NULL,

  evidence_family text NOT NULL,
  evidence_type text NOT NULL CHECK (
    evidence_type IN (
      'document',
      'screenshot',
      'approval',
      'comment',
      'order',
      'external_proof',
      'policy_change',
      'kpi_snapshot',
      'manual_closure',
      'meeting_record',
      'contact_record',
      'executive_validation',
      'system_log',
      'other'
    )
  ),

  description text NOT NULL,
  executive_purpose text NOT NULL,

  allowed_submission_modes text[] NOT NULL DEFAULT ARRAY[]::text[],
  allowed_file_types text[] NOT NULL DEFAULT ARRAY[]::text[],
  required_metadata_keys text[] NOT NULL DEFAULT ARRAY[]::text[],

  can_be_user_submitted boolean NOT NULL DEFAULT true,
  can_be_system_generated boolean NOT NULL DEFAULT false,
  requires_review boolean NOT NULL DEFAULT true,
  can_close_action boolean NOT NULL DEFAULT false,

  default_reviewer_role text NULL,
  minimum_reviewer_role text NULL,

  trust_level text NOT NULL CHECK (
    trust_level IN ('low', 'medium', 'high', 'critical')
  ),

  confidence_weight numeric(9,4) NOT NULL DEFAULT 1.0000,
  score_confidence_impact numeric(9,4) NOT NULL DEFAULT 0.0000,

  retention_policy text NOT NULL DEFAULT 'standard',
  audit_level text NOT NULL CHECK (
    audit_level IN ('basic', 'standard', 'strict', 'critical')
  ) DEFAULT 'standard',

  related_action_families text[] NOT NULL DEFAULT ARRAY[]::text[],
  related_tension_codes text[] NOT NULL DEFAULT ARRAY[]::text[],
  related_action_codes text[] NOT NULL DEFAULT ARRAY[]::text[],

  validation_rules jsonb NOT NULL DEFAULT '{}'::jsonb,

  status text NOT NULL DEFAULT 'active' CHECK (
    status IN ('active', 'inactive', 'archived')
  ),

  version integer NOT NULL DEFAULT 1,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_evidence_definitions_code_version UNIQUE (evidence_code, version)
);

-- Un solo activo por código
CREATE UNIQUE INDEX IF NOT EXISTS uq_evidence_definitions_one_active_per_code
ON faro.evidence_definitions (evidence_code)
WHERE status = 'active';

-- Índices de búsqueda y filtrado
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_code        ON faro.evidence_definitions (evidence_code);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_family      ON faro.evidence_definitions (evidence_family);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_type        ON faro.evidence_definitions (evidence_type);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_status      ON faro.evidence_definitions (status);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_trust_level ON faro.evidence_definitions (trust_level);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_can_close   ON faro.evidence_definitions (can_close_action);

-- GIN para búsqueda en arrays
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_related_actions  ON faro.evidence_definitions USING gin (related_action_codes);
CREATE INDEX IF NOT EXISTS idx_evidence_definitions_related_tensions ON faro.evidence_definitions USING gin (related_tension_codes);

COMMENT ON TABLE faro.evidence_definitions IS
'Catálogo canónico de evidencias FARO. Define las pruebas aceptadas para validar acciones, cierres, auditoría y confianza de ejecución.';

-- ============================================================
-- FARO-SQL-006 · V031__seed_evidence_definitions_mvp.sql
-- Seed Catálogo Canónico de Evidencias MVP FARO
-- Version: v1.0
-- ============================================================

BEGIN;

INSERT INTO faro.evidence_definitions (
  evidence_code,
  name,
  short_name,
  evidence_family,
  evidence_type,
  description,
  executive_purpose,
  allowed_submission_modes,
  allowed_file_types,
  required_metadata_keys,
  can_be_user_submitted,
  can_be_system_generated,
  requires_review,
  can_close_action,
  default_reviewer_role,
  minimum_reviewer_role,
  trust_level,
  confidence_weight,
  score_confidence_impact,
  retention_policy,
  audit_level,
  related_action_families,
  related_tension_codes,
  related_action_codes,
  validation_rules,
  status,
  version,
  metadata
)
VALUES

-- ============================================================
-- EVD-001
-- ============================================================

(
  'EVD-001',
  'Documento cargado',
  'Documento',
  'documentary',
  'document',
  'Archivo cargado como respaldo de una acción, decisión, política, análisis o entrega.',
  'Permitir que el cierre de una acción quede respaldado por un documento verificable.',
  ARRAY['upload','manual'],
  ARRAY['pdf','docx','xlsx','csv','png','jpg'],
  ARRAY['file_name','submitted_by','submitted_at'],
  true,
  false,
  true,
  true,
  'general_manager',
  'area_manager',
  'medium',
  1.0000,
  1.0000,
  'standard_5_years',
  'standard',
  ARRAY['commercial','finance','stock','purchasing','operations','data_quality','direction'],
  ARRAY[]::text[],
  ARRAY[]::text[],
  '{
    "max_file_size_mb": 25,
    "requires_file_hash": true,
    "requires_storage_uri": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-002
-- ============================================================

(
  'EVD-002',
  'Captura de sistema',
  'Captura sistema',
  'system_proof',
  'screenshot',
  'Captura o exportación visual de un sistema interno o externo que respalda una acción o dato.',
  'Respaldar visualmente cambios, estados, cargas, stock, reportes, fuentes o configuraciones.',
  ARRAY['upload','manual','system_capture'],
  ARRAY['png','jpg','jpeg','pdf'],
  ARRAY['source_system','captured_at','submitted_by'],
  true,
  true,
  true,
  true,
  'area_manager',
  'area_manager',
  'medium',
  1.0000,
  1.0000,
  'standard_5_years',
  'standard',
  ARRAY['stock','data_quality','operations'],
  ARRAY['TNS-006','TNS-027','TNS-028'],
  ARRAY['ACT-STK-001','ACT-STK-002','ACT-DQ-001','ACT-DQ-002'],
  '{
    "requires_source_system": true,
    "requires_capture_date": true,
    "accepted_sources": ["erp", "crm", "pos", "bank", "spreadsheet", "faro", "external_system"]
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-003
-- ============================================================

(
  'EVD-003',
  'Registro de aprobación',
  'Aprobación',
  'approval',
  'approval',
  'Registro formal de aprobación realizada por responsable autorizado.',
  'Asegurar que una decisión o cambio crítico fue aprobado por el nivel correspondiente.',
  ARRAY['workflow','system'],
  ARRAY[]::text[],
  ARRAY['approved_by','approved_at','approval_scope'],
  false,
  true,
  false,
  true,
  'general_manager',
  'area_manager',
  'high',
  1.3000,
  1.5000,
  'strict_7_years',
  'strict',
  ARRAY['commercial','finance','stock','purchasing','direction'],
  ARRAY[]::text[],
  ARRAY['ACT-DIR-003'],
  '{
    "requires_approver_user_id": true,
    "requires_role_validation": true,
    "requires_timestamp": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-004
-- ============================================================

(
  'EVD-004',
  'Comentario validado',
  'Comentario validado',
  'workflow',
  'comment',
  'Comentario operativo o ejecutivo validado por un responsable autorizado.',
  'Permitir trazabilidad mínima cuando la evidencia es una explicación, gestión o justificación revisada.',
  ARRAY['manual','workflow'],
  ARRAY[]::text[],
  ARRAY['comment','submitted_by','validated_by','validated_at'],
  true,
  true,
  true,
  false,
  'general_manager',
  'area_manager',
  'low',
  0.7000,
  0.5000,
  'standard_5_years',
  'basic',
  ARRAY['operations','data_quality','direction'],
  ARRAY['TNS-009','TNS-010','TNS-027','TNS-028','TNS-029'],
  ARRAY['ACT-OPS-001','ACT-OPS-002','ACT-OPS-003','ACT-DQ-001','ACT-DQ-002'],
  '{
    "requires_comment_min_length": 20,
    "requires_validator": true,
    "cannot_close_critical_action_alone": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-005
-- ============================================================

(
  'EVD-005',
  'Orden emitida',
  'Orden emitida',
  'operational_order',
  'order',
  'Orden de compra, transferencia, reposición, despacho o instrucción operativa emitida.',
  'Probar que una acción operativa dejó de ser intención y pasó a ejecución concreta.',
  ARRAY['upload','system','manual'],
  ARRAY['pdf','xlsx','csv','png','jpg'],
  ARRAY['order_number','order_date','responsible_user_id'],
  true,
  true,
  true,
  true,
  'area_manager',
  'area_manager',
  'high',
  1.2500,
  1.5000,
  'standard_5_years',
  'standard',
  ARRAY['stock','purchasing','operations'],
  ARRAY['TNS-006','TNS-008','TNS-021'],
  ARRAY['ACT-STK-001','ACT-STK-002'],
  '{
    "requires_order_number": true,
    "requires_order_date": true,
    "requires_related_product_or_supplier": false
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-006
-- ============================================================

(
  'EVD-006',
  'Comprobante externo',
  'Comprobante externo',
  'external_proof',
  'external_proof',
  'Comprobante emitido por tercero: pago, transferencia, recibo, factura, nota, acuerdo o constancia externa.',
  'Respaldar hechos que no dependen solo del registro interno de la empresa.',
  ARRAY['upload','manual','integration'],
  ARRAY['pdf','png','jpg','jpeg','xlsx'],
  ARRAY['external_party','document_date','submitted_by'],
  true,
  true,
  true,
  true,
  'finance_manager',
  'area_manager',
  'high',
  1.4000,
  2.0000,
  'strict_7_years',
  'strict',
  ARRAY['finance','purchasing','commercial'],
  ARRAY['TNS-004','TNS-005'],
  ARRAY['ACT-FIN-001'],
  '{
    "requires_external_party": true,
    "requires_document_date": true,
    "requires_file_hash": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-007
-- ============================================================

(
  'EVD-007',
  'Cambio de política',
  'Cambio política',
  'policy',
  'policy_change',
  'Documento, registro o aprobación que acredita un cambio de política comercial, financiera, operativa o de datos.',
  'Probar que una tensión estructural fue abordada con una regla de gobierno y no solo con una acción aislada.',
  ARRAY['upload','workflow','system'],
  ARRAY['pdf','docx','xlsx','png','jpg'],
  ARRAY['policy_name','approved_by','effective_from'],
  true,
  true,
  true,
  true,
  'general_manager',
  'general_manager',
  'critical',
  1.6000,
  3.0000,
  'strict_7_years',
  'critical',
  ARRAY['commercial','finance','stock','purchasing','direction'],
  ARRAY['TNS-001','TNS-002','TNS-015','TNS-016'],
  ARRAY['ACT-COM-001','ACT-COM-003','ACT-PUR-003','ACT-DIR-003'],
  '{
    "requires_policy_name": true,
    "requires_approval": true,
    "requires_effective_from": true,
    "requires_scope": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-008
-- ============================================================

(
  'EVD-008',
  'KPI posterior',
  'KPI posterior',
  'measurement',
  'kpi_snapshot',
  'KPI posterior a la acción que permite medir si la intervención generó impacto.',
  'Cerrar el ciclo dato → acción → resultado, midiendo si la acción funcionó.',
  ARRAY['system','integration'],
  ARRAY[]::text[],
  ARRAY['kpi_code','period_start','period_end','calculated_at'],
  false,
  true,
  true,
  false,
  'general_manager',
  'area_manager',
  'high',
  1.5000,
  2.5000,
  'standard_5_years',
  'strict',
  ARRAY['commercial','finance','stock','operations','data_quality','direction'],
  ARRAY[]::text[],
  ARRAY[]::text[],
  '{
    "requires_kpi_snapshot_id": true,
    "requires_comparison_period": true,
    "cannot_be_manually_uploaded": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-009
-- ============================================================

(
  'EVD-009',
  'Cierre manual justificado',
  'Cierre justificado',
  'manual_closure',
  'manual_closure',
  'Justificación formal para cerrar una acción cuando no existe evidencia documental completa.',
  'Permitir cierres excepcionales sin romper trazabilidad, pero marcando menor confianza.',
  ARRAY['workflow','manual'],
  ARRAY[]::text[],
  ARRAY['justification','submitted_by','approved_by','reason_code'],
  true,
  true,
  true,
  true,
  'director',
  'general_manager',
  'medium',
  0.8000,
  0.5000,
  'strict_7_years',
  'strict',
  ARRAY['operations','direction'],
  ARRAY[]::text[],
  ARRAY[]::text[],
  '{
    "requires_justification_min_length": 50,
    "requires_approval": true,
    "cannot_close_recurrent_critical_tension_alone": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true, "exceptional": true}'::jsonb
),

-- ============================================================
-- EVD-010
-- ============================================================

(
  'EVD-010',
  'Acta, minuta o reporte ejecutivo',
  'Acta/reporte',
  'executive_record',
  'meeting_record',
  'Acta, minuta, reporte ejecutivo o documento de reunión donde se registra análisis, decisión o seguimiento.',
  'Dar respaldo formal a decisiones ejecutivas, comités, revisiones y planes de acción.',
  ARRAY['upload','system','workflow'],
  ARRAY['pdf','docx','xlsx','png','jpg'],
  ARRAY['meeting_date','participants','submitted_by'],
  true,
  true,
  true,
  true,
  'general_manager',
  'area_manager',
  'high',
  1.3000,
  2.0000,
  'standard_5_years',
  'standard',
  ARRAY['commercial','finance','stock','purchasing','operations','direction'],
  ARRAY['TNS-009','TNS-026','TNS-030'],
  ARRAY['ACT-COM-004','ACT-COM-005','ACT-FIN-003','ACT-DIR-001','ACT-DIR-002'],
  '{
    "requires_meeting_date": false,
    "requires_participants": false,
    "requires_summary": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-011
-- ============================================================

(
  'EVD-011',
  'Cliente/proveedor contactado',
  'Contacto externo',
  'external_contact',
  'contact_record',
  'Registro de contacto con cliente, proveedor, referente externo o contraparte relevante.',
  'Probar gestión activa frente a mora, proveedor crítico, negociación o reclamo.',
  ARRAY['manual','workflow','integration'],
  ARRAY['pdf','png','jpg','eml','msg'],
  ARRAY['external_party','contact_date','contact_result','submitted_by'],
  true,
  true,
  true,
  true,
  'area_manager',
  'area_manager',
  'medium',
  1.1000,
  1.5000,
  'standard_5_years',
  'standard',
  ARRAY['finance','purchasing','commercial'],
  ARRAY['TNS-004','TNS-005','TNS-020','TNS-023'],
  ARRAY['ACT-FIN-001','ACT-FIN-006','ACT-PUR-002'],
  '{
    "requires_external_party": true,
    "requires_contact_date": true,
    "requires_contact_result": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
),

-- ============================================================
-- EVD-012
-- ============================================================

(
  'EVD-012',
  'Validación de dirección',
  'Validación dirección',
  'executive_validation',
  'executive_validation',
  'Validación formal de dirección sobre una acción, política, cierre, excepción o decisión crítica.',
  'Asegurar que decisiones de alto impacto queden aprobadas por el nivel correcto.',
  ARRAY['workflow','system'],
  ARRAY[]::text[],
  ARRAY['validated_by','validated_at','validation_scope'],
  false,
  true,
  false,
  true,
  'director',
  'general_manager',
  'critical',
  1.8000,
  3.0000,
  'strict_7_years',
  'critical',
  ARRAY['commercial','finance','stock','purchasing','operations','data_quality','direction'],
  ARRAY['TNS-001','TNS-004','TNS-009','TNS-010','TNS-019','TNS-026','TNS-030'],
  ARRAY['ACT-COM-001','ACT-COM-002','ACT-FIN-005','ACT-DIR-001','ACT-DIR-002','ACT-DIR-003'],
  '{
    "requires_validated_by": true,
    "requires_role_validation": true,
    "requires_timestamp": true,
    "cannot_be_uploaded_as_file_only": true
  }'::jsonb,
  'active',
  1,
  '{"canonical": true, "mvp": true}'::jsonb
)

ON CONFLICT (evidence_code, version)
DO UPDATE SET
  name = EXCLUDED.name,
  short_name = EXCLUDED.short_name,
  evidence_family = EXCLUDED.evidence_family,
  evidence_type = EXCLUDED.evidence_type,
  description = EXCLUDED.description,
  executive_purpose = EXCLUDED.executive_purpose,
  allowed_submission_modes = EXCLUDED.allowed_submission_modes,
  allowed_file_types = EXCLUDED.allowed_file_types,
  required_metadata_keys = EXCLUDED.required_metadata_keys,
  can_be_user_submitted = EXCLUDED.can_be_user_submitted,
  can_be_system_generated = EXCLUDED.can_be_system_generated,
  requires_review = EXCLUDED.requires_review,
  can_close_action = EXCLUDED.can_close_action,
  default_reviewer_role = EXCLUDED.default_reviewer_role,
  minimum_reviewer_role = EXCLUDED.minimum_reviewer_role,
  trust_level = EXCLUDED.trust_level,
  confidence_weight = EXCLUDED.confidence_weight,
  score_confidence_impact = EXCLUDED.score_confidence_impact,
  retention_policy = EXCLUDED.retention_policy,
  audit_level = EXCLUDED.audit_level,
  related_action_families = EXCLUDED.related_action_families,
  related_tension_codes = EXCLUDED.related_tension_codes,
  related_action_codes = EXCLUDED.related_action_codes,
  validation_rules = EXCLUDED.validation_rules,
  status = EXCLUDED.status,
  metadata = EXCLUDED.metadata,
  updated_at = now();

COMMIT;

-- Refuerzo de faro.evidence con campos canónicos
ALTER TABLE faro.evidence
ADD COLUMN IF NOT EXISTS evidence_definition_id uuid NULL,
ADD COLUMN IF NOT EXISTS validation_score numeric(9,4) NULL,
ADD COLUMN IF NOT EXISTS reviewed_status text NULL CHECK (
  reviewed_status IS NULL OR reviewed_status IN ('pending','approved','rejected','needs_more_info')
),
ADD COLUMN IF NOT EXISTS reviewed_reason text NULL;

-- Backfill de referencias en datos existentes
UPDATE faro.evidence e
SET evidence_definition_id = ed.evidence_definition_id
FROM faro.evidence_definitions ed
WHERE ed.evidence_code = e.evidence_code
  AND ed.status = 'active'
  AND e.evidence_definition_id IS NULL;

CREATE OR REPLACE FUNCTION faro.set_evidence_definition_id()
RETURNS trigger AS $$
BEGIN
  IF NEW.evidence_code IS NOT NULL AND NEW.evidence_definition_id IS NULL THEN
    SELECT evidence_definition_id
    INTO NEW.evidence_definition_id
    FROM faro.evidence_definitions
    WHERE evidence_code = NEW.evidence_code
      AND status = 'active'
    LIMIT 1;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_set_evidence_definition_id ON faro.evidence;

CREATE TRIGGER trg_set_evidence_definition_id
BEFORE INSERT OR UPDATE OF evidence_code
ON faro.evidence
FOR EACH ROW
EXECUTE FUNCTION faro.set_evidence_definition_id();

CREATE OR REPLACE VIEW faro.v_action_required_evidence AS
SELECT
  a.company_id,
  a.action_id,
  a.action_code,
  a.title AS action_title,
  ad.name AS action_definition_name,
  required_evidence.evidence_code,
  ed.name AS evidence_name,
  ed.evidence_type,
  ed.trust_level,
  ed.can_close_action,
  ed.requires_review
FROM faro.actions a
LEFT JOIN faro.action_definitions ad
  ON ad.action_code = a.action_code
 AND ad.status = 'active'
CROSS JOIN LATERAL unnest(
  COALESCE(
    ARRAY(
      SELECT jsonb_array_elements_text(a.payload->'evidence_required_codes')
    ),
    ad.evidence_required_codes,
    ARRAY[]::text[]
  )
) AS required_evidence(evidence_code)
LEFT JOIN faro.evidence_definitions ed
  ON ed.evidence_code = required_evidence.evidence_code
 AND ed.status = 'active';

CREATE OR REPLACE VIEW faro.v_action_evidence_status AS
WITH required AS (
  SELECT
    a.company_id,
    a.action_id,
    a.action_code,
    a.title AS action_title,
    required_evidence.evidence_code
  FROM faro.actions a
  LEFT JOIN faro.action_definitions ad
    ON ad.action_code = a.action_code
   AND ad.status = 'active'
  CROSS JOIN LATERAL unnest(
    COALESCE(ad.evidence_required_codes, ARRAY[]::text[])
  ) AS required_evidence(evidence_code)
),
submitted AS (
  SELECT
    e.company_id,
    e.action_id,
    e.evidence_code,
    COUNT(*) FILTER (WHERE e.status IN ('submitted','approved')) AS submitted_count,
    COUNT(*) FILTER (WHERE e.status = 'approved') AS approved_count
  FROM faro.evidence e
  GROUP BY e.company_id, e.action_id, e.evidence_code
)
SELECT
  r.company_id,
  r.action_id,
  r.action_code,
  r.action_title,
  r.evidence_code,
  ed.name AS evidence_name,
  ed.trust_level,
  COALESCE(s.submitted_count, 0) AS submitted_count,
  COALESCE(s.approved_count, 0) AS approved_count,
  CASE
    WHEN COALESCE(s.approved_count, 0) > 0 THEN 'approved'
    WHEN COALESCE(s.submitted_count, 0) > 0 THEN 'submitted'
    ELSE 'missing'
  END AS evidence_status
FROM required r
LEFT JOIN submitted s
  ON s.company_id = r.company_id
 AND s.action_id = r.action_id
 AND s.evidence_code = r.evidence_code
LEFT JOIN faro.evidence_definitions ed
  ON ed.evidence_code = r.evidence_code
 AND ed.status = 'active';