Workflow oficial tensión → acción → evidencia → cierre

-- tensions.status (10 valores)
CHECK (status IN (
  'new', 'in_review', 'in_execution', 'blocked', 'monitoring',
  'closed', 'rejected', 'expired', 'cancelled', 'archived'
))

-- actions.status (12 valores)
CHECK (status IN (
  'new', 'in_progress', 'waiting_evidence', 'in_review', 'approved',
  'closed', 'blocked', 'rejected', 'expired', 'cancelled',
  'reopened', 'archived'
))

-- evidence.status (5 valores)
CHECK (status IN (
  'submitted', 'approved', 'rejected', 'needs_more_info', 'archived'
))

CREATE OR REPLACE FUNCTION faro.transition_action_status(
  p_company_id uuid,
  p_action_id uuid,
  p_new_status text,
  p_user_id uuid,
  p_reason text DEFAULT NULL
)
RETURNS boolean AS $$
DECLARE
  v_old_status text;
  v_allowed boolean;
BEGIN
  SELECT status INTO v_old_status
  FROM faro.actions
  WHERE company_id = p_company_id AND action_id = p_action_id
  FOR UPDATE;

  -- consulta tabla canónica faro.action_status_transitions
  SELECT EXISTS (
    SELECT 1 FROM faro.action_status_transitions
    WHERE from_status = v_old_status AND to_status = p_new_status
  ) INTO v_allowed;

  IF NOT v_allowed THEN
    RAISE EXCEPTION 'INVALID_TRANSITION: % -> %', v_old_status, p_new_status;
  END IF;

  UPDATE faro.actions
  SET status = p_new_status, updated_at = now()
  WHERE company_id = p_company_id AND action_id = p_action_id;

  PERFORM faro.log_execution_event(
    p_company_id, 'action', p_action_id,
    'action_status_changed', 'workflow',
    'Transición de estado', p_reason,
    p_user_id, 'user', NULL, p_action_id, NULL, NULL,
    p_new_status, v_old_status, NULL, NULL, NULL,
    'transition_function', NULL, '{}'::jsonb
  );

  RETURN true;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION faro.calculate_due_date(
  p_priority text,
  p_action_type text,
  p_default_sla_days integer
)
RETURNS date AS $$
DECLARE
  v_days integer;
BEGIN
  v_days := CASE p_priority
    WHEN 'critical' THEN 3
    WHEN 'high'     THEN 7
    WHEN 'medium'   THEN 14
    WHEN 'low'      THEN 30
    ELSE COALESCE(p_default_sla_days, 7)
  END;

  RETURN CURRENT_DATE + (v_days || ' days')::interval;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Una acción está vencida si:
SELECT
  action_code,
  due_date,
  status,
  CASE
    WHEN status IN ('closed', 'cancelled', 'rejected', 'archived') THEN false
    WHEN due_date IS NULL THEN false
    WHEN due_date < CURRENT_DATE THEN true
    ELSE false
  END AS is_overdue,

  CASE
    WHEN due_date < CURRENT_DATE
     AND status NOT IN ('closed', 'cancelled', 'rejected', 'archived')
    THEN (CURRENT_DATE - due_date)
    ELSE 0
  END AS overdue_days
FROM faro.actions
WHERE company_id = 'EMPRESA-DEMO-CUYO-SA'::uuid;

CREATE TABLE IF NOT EXISTS faro.escalations (
  escalation_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL,
  entity_type text NOT NULL CHECK (entity_type IN ('tension', 'action', 'evidence')),
  entity_id uuid NOT NULL,
  tension_id uuid NULL,
  action_id uuid NULL,
  evidence_id uuid NULL,

  escalation_level text NOT NULL CHECK (
    escalation_level IN ('L1', 'L2', 'L3', 'L4', 'L5')
  ),

  reason_code text NOT NULL,         -- 'SLA_EXPIRED', 'RESPONSIBLE_MISSING', etc.
  reason_description text NOT NULL,

  from_user_id uuid NULL,
  to_user_id uuid NULL,
  to_role text NULL,

  status text NOT NULL DEFAULT 'open' CHECK (
    status IN ('open', 'acknowledged', 'resolved', 'dismissed')
  ),

  due_at timestamptz NULL,
  acknowledged_at timestamptz NULL,
  resolved_at timestamptz NULL,

  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Índice anti-duplicación: una sola escalation 'open' por nivel + entidad
CREATE UNIQUE INDEX idx_escalations_no_dup_open
ON faro.escalations(company_id, entity_id, escalation_level)
WHERE status = 'open';

CREATE OR REPLACE FUNCTION faro.can_close_action(
  p_company_id uuid,
  p_action_id uuid,
  p_user_id uuid
)
RETURNS TABLE(can_close boolean, blocking_reasons text[]) AS $$
DECLARE
  v_reasons text[] := ARRAY[]::text[];
  v_action RECORD;
  v_evidence_pending integer;
  v_open_escalations integer;
BEGIN
  SELECT * INTO v_action
  FROM faro.actions
  WHERE company_id = p_company_id AND action_id = p_action_id;

  IF v_action.status IN ('blocked', 'cancelled', 'rejected') THEN
    v_reasons := array_append(v_reasons, 'La acción está ' || v_action.status);
  END IF;

  IF v_action.closure_criteria IS NULL OR v_action.closure_criteria = '' THEN
    v_reasons := array_append(v_reasons, 'Falta closure_criteria');
  END IF;

  SELECT COUNT(*) INTO v_evidence_pending
  FROM faro.evidence
  WHERE action_id = p_action_id AND status <> 'approved';

  IF v_evidence_pending > 0 THEN
    v_reasons := array_append(v_reasons, 'Hay evidencia no aprobada (EVIDENCE_NOT_APPROVED)');
  END IF;

  SELECT COUNT(*) INTO v_open_escalations
  FROM faro.escalations
  WHERE action_id = p_action_id AND status = 'open';

  IF v_open_escalations > 0 THEN
    v_reasons := array_append(v_reasons, 'Hay escalamientos sin resolver');
  END IF;

  RETURN QUERY SELECT (cardinality(v_reasons) = 0), v_reasons;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE IF NOT EXISTS faro.execution_events (
  event_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL,

  entity_type text NOT NULL CHECK (
    entity_type IN ('tension', 'action', 'evidence')
  ),
  entity_id uuid NOT NULL,

  event_type text NOT NULL CHECK (event_type IN (
    -- Lifecycle (12)
    'tension_detected', 'tension_assigned', 'tension_started',
    'tension_closed', 'tension_reopened', 'tension_rejected',
    'action_created', 'action_assigned', 'action_started',
    'action_closed', 'action_reopened', 'action_cancelled',

    -- Workflow (8)
    'action_status_changed', 'tension_status_changed',
    'action_blocked', 'action_unblocked',
    'action_regularized', 'tension_to_monitoring',
    'responsible_assigned', 'responsible_missing',

    -- Escalation (6)
    'tension_escalated', 'action_escalated', 'evidence_escalated',
    'escalation_acknowledged', 'escalation_resolved', 'escalation_dismissed',

    -- Evidence (5)
    'evidence_submitted', 'evidence_approved', 'evidence_rejected',
    'evidence_needs_more_info', 'evidence_archived',

    -- Score (5)
    'score_recovery_enabled', 'score_recovered', 'score_recovery_blocked',
    'score_penalized', 'score_confidence_updated',

    -- Deadline (4)
    'action_expired', 'tension_expired',
    'sla_warning', 'sla_critical'
  )),

  category text NOT NULL CHECK (category IN (
    'lifecycle', 'workflow', 'escalation',
    'evidence', 'score', 'deadline'
  )),

  title text NOT NULL,
  description text NULL,

  actor_user_id uuid NULL,
  actor_kind text NOT NULL CHECK (
    actor_kind IN ('user', 'system', 'integration')
  ),

  tension_id uuid NULL,
  action_id uuid NULL,
  evidence_id uuid NULL,
  escalation_id uuid NULL,

  new_status text NULL,
  old_status text NULL,
  severity text NULL,
  priority text NULL,
  impact_score numeric NULL,

  source text NOT NULL,             -- 'workflow_engine', 'manual_ui', 'api', etc.
  source_reason text NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_events_company_entity
ON faro.execution_events(company_id, entity_type, entity_id, created_at DESC);

CREATE INDEX idx_events_company_action_timeline
ON faro.execution_events(company_id, action_id, created_at DESC)
WHERE action_id IS NOT NULL;

CREATE INDEX idx_events_company_tension_timeline
ON faro.execution_events(company_id, tension_id, created_at DESC)
WHERE tension_id IS NOT NULL;

CREATE OR REPLACE VIEW faro.v_action_events AS
SELECT
  e.event_id,
  e.company_id,
  e.action_id,
  e.tension_id,
  e.event_type,
  e.category,
  e.title,
  e.description,
  e.actor_user_id,
  u.full_name AS actor_name,
  e.actor_kind,
  e.new_status,
  e.old_status,
  e.severity,
  e.priority,
  e.payload,
  e.created_at
FROM faro.execution_events e
LEFT JOIN faro.users u ON u.user_id = e.actor_user_id
WHERE e.entity_type = 'action'
   OR e.action_id IS NOT NULL;