-- 033_crm_alert_audit.sql
-- AWS-style CRM alert lifecycle tracking.
--
-- Active alerts stay on the Alerts dashboard. When the underlying condition
-- disappears, the alert transitions to recovered and remains here for audit.

CREATE TABLE IF NOT EXISTS crm_alert_events (
  alert_key             TEXT PRIMARY KEY,
  category              TEXT NOT NULL,
  severity              TEXT NOT NULL CHECK (severity IN ('critical', 'warning', 'info')),
  entity_id             TEXT,
  title                 TEXT NOT NULL,
  summary               TEXT,
  status                TEXT NOT NULL CHECK (status IN ('active', 'recovered')),
  first_seen_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  recovered_at          TIMESTAMPTZ,
  recovery_notified_at  TIMESTAMPTZ,
  open_count            INTEGER NOT NULL DEFAULT 1,
  row_snapshot          JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS crm_alert_event_history (
  id            BIGSERIAL PRIMARY KEY,
  alert_key     TEXT NOT NULL REFERENCES crm_alert_events(alert_key) ON DELETE CASCADE,
  category      TEXT NOT NULL,
  from_status   TEXT,
  to_status     TEXT NOT NULL,
  summary       TEXT NOT NULL,
  metadata      JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_crm_alert_events_status_category
  ON crm_alert_events (status, category, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_crm_alert_events_recovered
  ON crm_alert_events (recovered_at DESC)
  WHERE status = 'recovered';

CREATE INDEX IF NOT EXISTS idx_crm_alert_event_history_key_time
  ON crm_alert_event_history (alert_key, created_at DESC);

