-- 0101_fraud_events_open_uq
-- resolved_at-aware idempotency for fraud_events (Finding 2). fraud_events is an admin work queue;
-- uniqueness applies only to OPEN (unresolved) rows on the coarse tuple
-- (user_id, referral_id, decision_point, adapter). Retries + multiple unresolved purchases collapse to
-- one open queue item; a recurrence after an admin sets resolved_at inserts a fresh row.
-- referral_id is nullable and Postgres unique indexes treat NULLs as distinct, so two partial unique
-- indexes split on referral_id; each also carries AND resolved_at IS NULL.
-- Pre-dedupe DELETE intentionally omitted: live fraud_events verified 0 rows on 2026-06-15.
-- Applied to live via direct run_sql_transaction (journal broken — see project_drizzle_journal_rebaseline),
-- so NOT CONCURRENTLY (must be transaction-safe). No ALTER TABLE — no column added.

CREATE TABLE IF NOT EXISTS "fraud_events" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "referral_id" uuid,
  "decision_point" text NOT NULL,
  "adapter" text NOT NULL,
  "action" text NOT NULL,
  "codes" text[] DEFAULT '{}'::text[] NOT NULL,
  "detail" jsonb,
  "resolved_by" uuid,
  "resolved_at" timestamp with time zone,
  "ts" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "fraud_events_user_id_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action,
  CONSTRAINT "fraud_events_referral_id_referrals_id_fk"
    FOREIGN KEY ("referral_id") REFERENCES "public"."referrals"("id") ON DELETE no action ON UPDATE no action,
  CONSTRAINT "fraud_events_resolved_by_users_id_fk"
    FOREIGN KEY ("resolved_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action
);

CREATE INDEX IF NOT EXISTS fraud_events_user_ts_idx ON fraud_events (user_id, ts DESC);
CREATE INDEX IF NOT EXISTS fraud_events_resolved_ts_idx ON fraud_events (resolved_at, ts DESC);
CREATE INDEX IF NOT EXISTS fraud_events_referral_idx ON fraud_events (referral_id) WHERE referral_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS fraud_events_open_uq
  ON fraud_events (user_id, referral_id, decision_point, adapter)
  WHERE referral_id IS NOT NULL AND resolved_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS fraud_events_open_noref_uq
  ON fraud_events (user_id, decision_point, adapter)
  WHERE referral_id IS NULL AND resolved_at IS NULL;
