Timeline de Ejecución + Auditoría Visual FARO Connect

CREATE OR REPLACE FUNCTION faro.log_execution_event(
  p_company_id       uuid,
  p_entity_type      text,
  p_entity_id        uuid,
  p_event_type       text,
  p_event_family     text,
  p_title            text,
  p_description      text DEFAULT NULL,
  p_actor_user_id    uuid DEFAULT NULL,
  p_actor_type       text DEFAULT 'user',
  p_tension_id       uuid DEFAULT NULL,
  p_action_id        uuid DEFAULT NULL,
  p_evidence_id      uuid DEFAULT NULL,
  p_from_status      text DEFAULT NULL,
  p_to_status        text DEFAULT NULL,
  p_severity         text DEFAULT NULL,
  p_priority         text DEFAULT NULL,
  p_impact_score     numeric DEFAULT NULL,
  p_confidence_delta numeric DEFAULT NULL,
  p_source_module    text DEFAULT NULL,
  p_source_reference text DEFAULT NULL,
  p_payload          jsonb DEFAULT '{}'::jsonb
)
RETURNS uuid AS $$
DECLARE
  v_event_id uuid;
BEGIN
  INSERT INTO faro.execution_events (
    company_id, entity_type, entity_id, event_type, event_family,
    title, description, actor_user_id, actor_type,
    tension_id, action_id, evidence_id,
    from_status, to_status, severity, priority,
    impact_score, confidence_delta,
    source_module, source_reference, payload
  )
  VALUES (
    p_company_id, p_entity_type, p_entity_id, p_event_type, p_event_family,
    p_title, p_description, p_actor_user_id, p_actor_type,
    p_tension_id, p_action_id, p_evidence_id,
    p_from_status, p_to_status, p_severity, p_priority,
    p_impact_score, p_confidence_delta,
    p_source_module, p_source_reference, COALESCE(p_payload, '{}'::jsonb)
  )
  RETURNING execution_event_id INTO v_event_id;

  RETURN v_event_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW faro.v_execution_timeline AS
SELECT
  ee.company_id,
  ee.execution_event_id,
  ee.entity_type,
  ee.entity_id,

  ee.tension_id,
  t.tension_code,
  t.title AS tension_title,
  t.severity AS tension_severity,

  ee.action_id,
  a.action_code,
  a.title AS action_title,
  a.priority AS action_priority,
  a.status AS action_status,

  ee.evidence_id,
  e.evidence_code,
  e.title AS evidence_title,
  e.status AS evidence_status,

  ee.report_id,
  ee.score_snapshot_id,

  ee.event_type,
  ee.event_family,
  ee.actor_type,
  ee.actor_user_id,
  u.full_name AS actor_name,
  u.email     AS actor_email,

  ee.from_status, ee.to_status,
  ee.title, ee.description,
  ee.severity, ee.priority,
  ee.impact_score, ee.confidence_delta,
  ee.source_module, ee.source_reference,
  ee.payload,
  ee.occurred_at, ee.created_at

FROM faro.execution_events ee
LEFT JOIN faro.tensions t  ON t.tension_id = ee.tension_id  AND t.company_id = ee.company_id
LEFT JOIN faro.actions  a  ON a.action_id  = ee.action_id   AND a.company_id = ee.company_id
LEFT JOIN faro.evidence e  ON e.evidence_id= ee.evidence_id AND e.company_id = ee.company_id
LEFT JOIN faro.users    u  ON u.user_id    = ee.actor_user_id;

BEGIN;

-- 1) Tensión detectada por motor evaluador
SELECT faro.log_execution_event(
  '10000000-0000-0000-0000-000000000001', -- company_id
  'tension',
  '22000000-0000-0000-0000-000000000001', -- entity_id
  'tension_detected',
  'detection',
  'Tensión detectada',
  'FARO detectó crecimiento no rentable: ventas suben, margen cae y descuentos aumentan.',
  NULL, 'system',
  '22000000-0000-0000-0000-000000000001', NULL, NULL,
  NULL, 'new',
  'critical', 'critical',
  -8.5000, NULL,
  'evaluation_engine', 'RULE-TNS-001',
  '{"rule_code":"RULE-TNS-001","tension_code":"TNS-001"}'::jsonb
);

-- 2) Acción creada como recomendación
SELECT faro.log_execution_event(
  '10000000-0000-0000-0000-000000000001',
  'action',
  '23000000-0000-0000-0000-000000000001',
  'action_created', 'workflow',
  'Acción creada',
  'FARO creó la acción ACT-COM-001 como recomendación para TNS-001.',
  NULL, 'system',
  '22000000-0000-0000-0000-000000000001',
  '23000000-0000-0000-0000-000000000001',
  NULL,
  NULL, 'new',
  'critical', 'critical',
  NULL, NULL,
  'evaluation_engine', 'ACT-COM-001',
  '{"action_code":"ACT-COM-001","recommended_by":"TNS-001"}'::jsonb
);

-- 3) Acción iniciada por usuario
SELECT faro.log_execution_event(
  '10000000-0000-0000-0000-000000000001',
  'action',
  '23000000-0000-0000-0000-000000000001',
  'action_started', 'workflow',
  'Acción iniciada',
  'El responsable inició la revisión de la política de descuentos.',
  '12000000-0000-0000-0000-000000000002', 'user',
  '22000000-0000-0000-0000-000000000001',
  '23000000-0000-0000-0000-000000000001',
  NULL,
  'new', 'in_progress',
  'critical', 'critical',
  NULL, NULL,
  'action_workflow', NULL,
  '{}'::jsonb
);

-- 4) Evidencia cargada
SELECT faro.log_execution_event(
  '10000000-0000-0000-0000-000000000001',
  'evidence',
  '24000000-0000-0000-0000-000000000001',
  'evidence_uploaded', 'evidence',
  'Evidencia cargada',
  'Se cargó evidencia EVD-007 · Cambio de política comercial.',
  '12000000-0000-0000-0000-000000000002', 'user',
  '22000000-0000-0000-0000-000000000001',
  '23000000-0000-0000-0000-000000000001',
  '24000000-0000-0000-0000-000000000001',
  NULL, 'submitted',
  'critical', 'critical',
  NULL, 1.5000,
  'evidence_upload', 'EVD-007',
  '{"evidence_code":"EVD-007","trust_level":"critical"}'::jsonb
);

COMMIT;

SELECT *
FROM faro.v_execution_timeline
WHERE company_id = $1
  AND action_id  = $2
  AND ($3::timestamptz IS NULL OR occurred_at < $3)
ORDER BY occurred_at DESC
LIMIT 50;