Alertas y Notificaciones FARO Connect MVP

CREATE TABLE IF NOT EXISTS faro.notifications (
  notification_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NOT NULL,

  user_id uuid NULL,
  role_code text NULL,

  channel text NOT NULL CHECK (
    channel IN ('in_app', 'email', 'whatsapp', 'slack', 'teams')
  ),

  notification_type text NOT NULL CHECK (
    notification_type IN (
      'alert',
      'reminder',
      'escalation',
      'approval_request',
      'evidence_review',
      'score_warning',
      'digest',
      'system'
    )
  ),

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

  title text NOT NULL,
  body text NOT NULL,
  action_label text NULL,
  action_url text NULL,

  entity_type text NULL CHECK (
    entity_type IS NULL OR entity_type IN (
      'tension', 'action', 'evidence', 'score', 'report', 'system'
    )
  ),

  entity_id uuid NULL,

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

  template_code text NULL,
  template_version integer NOT NULL DEFAULT 1,

  status text NOT NULL DEFAULT 'pending' CHECK (
    status IN (
      'pending', 'queued', 'sent', 'delivered',
      'read', 'failed', 'cancelled', 'suppressed'
    )
  ),

  provider text NULL,
  provider_message_id text NULL,
  provider_response jsonb NOT NULL DEFAULT '{}'::jsonb,

  dedupe_key text NULL,

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

  -- IA audit trail (D5)
  ai_used boolean NOT NULL DEFAULT false,
  ai_model text NULL,
  ai_slots_filled text[] NOT NULL DEFAULT ARRAY[]::text[],
  ai_call_id uuid NULL,
  ai_latency_ms integer NULL,

  scheduled_at timestamptz NOT NULL DEFAULT now(),
  queued_at timestamptz NULL,
  sent_at timestamptz NULL,
  delivered_at timestamptz NULL,
  read_at timestamptz NULL,
  failed_at timestamptz NULL,
  failure_reason text NULL,

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

CREATE INDEX IF NOT EXISTS idx_notifications_company_user_status
ON faro.notifications(company_id, user_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_notifications_company_role_status
ON faro.notifications(company_id, role_code, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_notifications_entity
ON faro.notifications(company_id, entity_type, entity_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_notifications_dedupe
ON faro.notifications(company_id, dedupe_key);

CREATE INDEX IF NOT EXISTS idx_notifications_pending
ON faro.notifications(status, scheduled_at)
WHERE status IN ('pending', 'queued');

CREATE INDEX IF NOT EXISTS idx_notifications_ai_audit
ON faro.notifications(company_id, ai_used, created_at DESC)
WHERE ai_used = true;

CREATE TABLE IF NOT EXISTS faro.notification_templates (
  notification_template_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  template_code text NOT NULL,
  version integer NOT NULL DEFAULT 1,

  name text NOT NULL,
  description text NOT NULL,

  channel text NOT NULL CHECK (
    channel IN ('in_app', 'email', 'whatsapp', 'slack', 'teams')
  ),

  notification_type text NOT NULL CHECK (
    notification_type IN (
      'alert', 'reminder', 'escalation', 'approval_request',
      'evidence_review', 'score_warning', 'digest', 'system'
    )
  ),

  subject_template text NULL,
  title_template text NOT NULL,
  body_template text NOT NULL,
  action_label_template text NULL,
  action_url_template text NULL,

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

  required_payload_keys text[] NOT NULL DEFAULT ARRAY[]::text[],

  -- IA controlada (D5)
  ai_enabled boolean NOT NULL DEFAULT false,
  ai_slots text[] NOT NULL DEFAULT ARRAY[]::text[],

  is_active boolean NOT NULL DEFAULT true,

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

  UNIQUE(template_code, version, channel)
);

CREATE TABLE IF NOT EXISTS faro.notification_preferences (
  notification_preference_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NOT NULL,
  user_id uuid NOT NULL,

  channel text NOT NULL CHECK (
    channel IN ('in_app', 'email', 'whatsapp', 'slack', 'teams')
  ),

  notification_type text NOT NULL,

  enabled boolean NOT NULL DEFAULT true,

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

  digest_enabled boolean NOT NULL DEFAULT false,
  digest_frequency text NULL CHECK (
    digest_frequency IS NULL OR digest_frequency IN ('daily', 'weekly')
  ),

  quiet_hours_start time NULL,
  quiet_hours_end time NULL,

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

  UNIQUE(company_id, user_id, channel, notification_type)
);

-- Regla MVP simple sin lookup en preferences:
--   critical/high → siempre se notifica;
--   medium       → según canal;
--   low          → solo in-app.

async function fillAiSlots(params: {
  templateCode: string;
  context: { tension?: Tension; action?: Action; evidence?: Evidence };
  fallback: Record<string, string>;
}): Promise<{ slots: Record<string, string>; aiUsed: boolean; callId?: string }> {
  try {
    const response = await aiGateway.complete({
      template_code: params.templateCode,
      mode: "slot_fill",
      slots: ["subject_intro", "body_summary", "recommendation_text"],
      context: params.context,
      max_tokens: 240,
      timeout_ms: 500,
      guardrails: {
        forbid_digits: true,
        forbid_dates: true,
        forbid_urls: true,
        forbid_codes: true,
        max_length: 200,
        language: "es"
      }
    });

    if (response.guardrails_violated) {
      throw new Error("AI_GUARDRAIL_VIOLATION");
    }

    return { slots: response.slots, aiUsed: true, callId: response.call_id };
  } catch (error) {
    // Fallback determinístico
    return { slots: params.fallback, aiUsed: false };
  }
}