-- Capture push-only Neon drift: users/payment/credit wallet columns.

ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "email_canonical_index" text;
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "notif_prefs" jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TYPE "public"."llm_job_type" ADD VALUE IF NOT EXISTS 'TRANSLATION';
ALTER TYPE "public"."support_message_author_type" ADD VALUE IF NOT EXISTS 'admin' AFTER 'system';
ALTER TYPE "public"."referral_status" ADD VALUE IF NOT EXISTS 'quarantined' AFTER 'rejected';

ALTER TABLE "vendors" ADD COLUMN IF NOT EXISTS "flag_reason" text;
ALTER TABLE "vendors" ADD COLUMN IF NOT EXISTS "reject_reason" text;
ALTER TABLE "vendors" ADD COLUMN IF NOT EXISTS "llm_decision" text;

ALTER TABLE "deal_variant_axes" ADD COLUMN IF NOT EXISTS "is_visual_axis" boolean DEFAULT false NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS "deal_variant_axes_deal_visual_uniq"
  ON "deal_variant_axes" ("deal_id")
  WHERE "is_visual_axis" = true;

ALTER TABLE "deal_images" ADD COLUMN IF NOT EXISTS "sku_id" uuid REFERENCES "public"."deal_skus"("id") ON DELETE cascade;
CREATE INDEX IF NOT EXISTS "deal_images_sku_idx" ON "deal_images" ("sku_id");
CREATE UNIQUE INDEX IF NOT EXISTS "deal_images_sku_primary_uniq"
  ON "deal_images" ("sku_id")
  WHERE "is_primary" = true AND "sku_id" IS NOT NULL;

ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "prompt_tokens" integer;
ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "completion_tokens" integer;
ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "total_tokens" integer;
ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "cost_usd" numeric(10, 6);
ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "model_name" text;

CREATE TABLE IF NOT EXISTS "llm_queues" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "chain" jsonb NOT NULL,
  "prompt" text DEFAULT '' NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_by" uuid REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action
);

CREATE TABLE IF NOT EXISTS "agent_definitions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "slug" text NOT NULL,
  "name" text NOT NULL,
  "description" text DEFAULT '' NOT NULL,
  "system_prompt_template" text NOT NULL,
  "knowledge_adapter_ids" text[] DEFAULT '{}'::text[] NOT NULL,
  "escalation_rule_ids" text[] DEFAULT '{}'::text[] NOT NULL,
  "available_decisions" text[] DEFAULT '{}'::text[] NOT NULL,
  "llm_chain" jsonb NOT NULL,
  "sla_ai_hours" integer DEFAULT 4 NOT NULL,
  "sla_human_hours" integer DEFAULT 24 NOT NULL,
  "max_cost_cents" integer DEFAULT 10 NOT NULL,
  "max_tool_calls" integer DEFAULT 12 NOT NULL,
  "ai_confidence_threshold" numeric(4, 3) DEFAULT '0.700' NOT NULL,
  "auto_refund_cap_cents" integer DEFAULT 0 NOT NULL,
  "is_active" boolean DEFAULT true NOT NULL,
  "version" integer DEFAULT 1 NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "agent_definitions_slug_unique" UNIQUE("slug")
);

CREATE TABLE IF NOT EXISTS "agent_definition_versions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "definition_id" uuid NOT NULL REFERENCES "public"."agent_definitions"("id") ON DELETE no action ON UPDATE no action,
  "version" integer NOT NULL,
  "snapshot" jsonb NOT NULL,
  "changed_by" uuid REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "agent_def_versions_def_ver_idx"
  ON "agent_definition_versions" ("definition_id","version");

DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name = 'vector') THEN
    CREATE EXTENSION IF NOT EXISTS vector;
    CREATE TABLE IF NOT EXISTS "support_kb_embeddings" (
      "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
      "article_id" uuid NOT NULL REFERENCES "public"."support_kb_articles"("id") ON DELETE cascade,
      "chunk_index" integer DEFAULT 0 NOT NULL,
      "chunk_text" text NOT NULL,
      "embedding" vector(1024) NOT NULL,
      "created_at" timestamp with time zone DEFAULT now() NOT NULL
    );
  ELSE
    CREATE TABLE IF NOT EXISTS "support_kb_embeddings" (
      "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
      "article_id" uuid NOT NULL REFERENCES "public"."support_kb_articles"("id") ON DELETE cascade,
      "chunk_index" integer DEFAULT 0 NOT NULL,
      "chunk_text" text NOT NULL,
      "embedding" text NOT NULL,
      "created_at" timestamp with time zone DEFAULT now() NOT NULL
    );
  END IF;
END $$;
CREATE UNIQUE INDEX IF NOT EXISTS "support_kb_embeddings_article_chunk_idx"
  ON "support_kb_embeddings" ("article_id","chunk_index");

ALTER TABLE "support_tickets" ADD COLUMN IF NOT EXISTS "related_order_line_id" uuid REFERENCES "public"."order_line"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "support_tickets" ADD COLUMN IF NOT EXISTS "agent_definition_id" uuid REFERENCES "public"."agent_definitions"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "transaction_cases" ADD COLUMN IF NOT EXISTS "agent_definition_id" uuid REFERENCES "public"."agent_definitions"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "ai_interventions" ADD COLUMN IF NOT EXISTS "agent_def_version" integer;

CREATE TABLE IF NOT EXISTS "live_notifications" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL REFERENCES "public"."users"("id") ON DELETE cascade,
  "event" text NOT NULL,
  "tier" text NOT NULL,
  "title_he" text NOT NULL,
  "title_en" text NOT NULL,
  "body_he" text,
  "body_en" text,
  "link" text,
  "payload" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "read_at" timestamp with time zone,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "expires_at" timestamp with time zone DEFAULT now() + interval '30 days' NOT NULL
);
CREATE INDEX IF NOT EXISTS "live_notifications_user_unread_idx" ON "live_notifications" ("user_id","created_at");
CREATE INDEX IF NOT EXISTS "live_notifications_expires_idx" ON "live_notifications" ("expires_at");

CREATE TABLE IF NOT EXISTS "admin_assignments" (
  "scope" text NOT NULL,
  "entity_id" uuid NOT NULL,
  "admin_user_id" uuid NOT NULL REFERENCES "public"."users"("id") ON DELETE cascade,
  "assigned_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "admin_assignments_scope_entity_id_pk" PRIMARY KEY ("scope","entity_id")
);
CREATE INDEX IF NOT EXISTS "admin_assignments_admin_idx" ON "admin_assignments" ("admin_user_id");

CREATE TABLE IF NOT EXISTS "platform_image_limits" (
  "id" integer PRIMARY KEY DEFAULT 1 NOT NULL,
  "max_images_per_deal" integer DEFAULT 10 NOT NULL,
  "max_images_per_sku" integer DEFAULT 10 NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_by_user_id" uuid REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action,
  CONSTRAINT "platform_image_limits_singleton" CHECK ("id" = 1)
);

CREATE TABLE IF NOT EXISTS "stock_watchlist" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL REFERENCES "public"."users"("id") ON DELETE cascade,
  "deal_id" uuid NOT NULL REFERENCES "public"."deals"("id") ON DELETE cascade,
  "sku_id" uuid REFERENCES "public"."deal_skus"("id") ON DELETE cascade,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "notified_at" timestamp with time zone,
  "expires_at" timestamp with time zone NOT NULL
);
CREATE INDEX IF NOT EXISTS "stock_watchlist_deal_pending_idx" ON "stock_watchlist" ("deal_id");

CREATE TABLE IF NOT EXISTS "alert_channels" (
  "id" text PRIMARY KEY NOT NULL,
  "kind" text NOT NULL,
  "label" text NOT NULL,
  "enabled" boolean DEFAULT false NOT NULL,
  "min_severity" text DEFAULT 'page' NOT NULL,
  "config" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "secret_enc" bytea,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "monitor_checks" (
  "key" text PRIMARY KEY NOT NULL,
  "enabled" boolean DEFAULT true NOT NULL,
  "config" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "severity_floor" text,
  "interval_hours" integer,
  "include_in_digest" boolean DEFAULT true NOT NULL,
  "last_run_at" timestamp with time zone,
  "last_severity" text,
  "last_detail" text,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "cron_runs" (
  "cron_key" text PRIMARY KEY NOT NULL,
  "last_success_at" timestamp with time zone,
  "last_error_at" timestamp with time zone,
  "last_error" text,
  "expected_interval_minutes" integer DEFAULT 60 NOT NULL
);

CREATE TABLE IF NOT EXISTS "dlq_events" (
  "queue_key" text PRIMARY KEY NOT NULL,
  "dead_count" bigint DEFAULT 0 NOT NULL,
  "last_dead_at" timestamp with time zone,
  "last_reason" text,
  "acknowledged_count" bigint DEFAULT 0 NOT NULL
);

CREATE TABLE IF NOT EXISTS "marketing_trigger_state" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL REFERENCES "public"."users"("id") ON DELETE cascade,
  "trigger_key" text NOT NULL,
  "fired_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "mts_user_trigger_uidx"
  ON "marketing_trigger_state" ("user_id","trigger_key");

CREATE TABLE IF NOT EXISTS "sync_cursors" (
  "key" text PRIMARY KEY NOT NULL,
  "value" text NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE "payment_methods" ADD COLUMN IF NOT EXISTS "card_fingerprint" text;

ALTER TABLE "credit_ledger" ADD COLUMN IF NOT EXISTS "withdrawable_at" timestamp with time zone;

ALTER TABLE "wallet_balances" ADD COLUMN IF NOT EXISTS "withdrawable_agorot" bigint DEFAULT 0 NOT NULL;
