-- Capture push-only Neon drift: fraud_events base table (0101 indexes assumed table existed).

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
);

DO $$ BEGIN
  ALTER TABLE "fraud_events" ADD CONSTRAINT "fraud_events_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  ALTER TABLE "fraud_events" ADD CONSTRAINT "fraud_events_referral_id_referrals_id_fk" FOREIGN KEY ("referral_id") REFERENCES "public"."referrals"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  ALTER TABLE "fraud_events" ADD CONSTRAINT "fraud_events_resolved_by_users_id_fk" FOREIGN KEY ("resolved_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

CREATE INDEX IF NOT EXISTS "fraud_events_user_ts_idx" ON "fraud_events" USING btree ("user_id", "ts" DESC);

CREATE INDEX IF NOT EXISTS "fraud_events_resolved_ts_idx" ON "fraud_events" USING btree ("resolved_at", "ts" DESC);

CREATE INDEX IF NOT EXISTS "fraud_events_referral_idx" ON "fraud_events" USING btree ("referral_id") WHERE "referral_id" IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS "fraud_events_open_uq" ON "fraud_events" USING btree ("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" USING btree ("user_id", "decision_point", "adapter") WHERE "referral_id" IS NULL AND "resolved_at" IS NULL;