-- AI Ticket Analyzer — Phase 2.7 cost-guard audit log.
-- See docs/ticket-analyzer-phase2-spec.md → Section D.8.

CREATE TABLE IF NOT EXISTS analyzer_cost_audit (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           TEXT REFERENCES "user"(id) ON DELETE SET NULL,
  action            TEXT NOT NULL,
                      -- e.g. 'aggregate_report' | 'analyze_ticket'
  estimated_cost    NUMERIC(10,4) NOT NULL,
  daily_spend_before NUMERIC(10,4) NOT NULL,
  decision          TEXT NOT NULL
                      CHECK (decision IN ('approved','requires_confirmation','blocked','overridden')),
  decision_reason   TEXT,
  context           JSONB,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_analyzer_cost_audit_user_date
  ON analyzer_cost_audit (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_analyzer_cost_audit_decision
  ON analyzer_cost_audit (decision, created_at DESC);

COMMENT ON TABLE analyzer_cost_audit IS
  'Audit log of cost-guard decisions for analyzer LLM operations. One row per gated request '
  '(approved/requires_confirmation/blocked/overridden), recording the inputs that drove the decision.';