CREATE TYPE "public"."ai_intervention_decision" AS ENUM('auto', 'escalate', 'ask', 'propose');--> statement-breakpoint
CREATE TYPE "public"."case_offer_by" AS ENUM('vendor', 'ai', 'human');--> statement-breakpoint
CREATE TYPE "public"."case_offer_outcome" AS ENUM('refund_full', 'refund_partial', 'replacement', 'deny');--> statement-breakpoint
CREATE TYPE "public"."case_offer_status" AS ENUM('pending', 'accepted', 'rejected', 'superseded', 'auto_executed');--> statement-breakpoint
CREATE TYPE "public"."case_resolution_executor" AS ENUM('vendor_auto', 'customer_accept', 'ai_auto', 'human');--> statement-breakpoint
CREATE TYPE "public"."support_attachment_abuse_status" AS ENUM('pending', 'clean', 'flagged', 'rejected');--> statement-breakpoint
CREATE TYPE "public"."support_case_category" AS ENUM('item_not_as_described', 'no_show_vendor', 'quality_issue', 'cannot_redeem', 'cancellation_request', 'fraud', 'safety', 'other');--> statement-breakpoint
CREATE TYPE "public"."support_case_status" AS ENUM('opened', 'vendor_review', 'vendor_offered', 'escalated', 'ai_handling', 'awaiting_info', 'human_review', 'resolved', 'closed', 'reopened');--> statement-breakpoint
CREATE TYPE "public"."support_kb_category" AS ENUM('faq', 'policy', 'refund_rules', 'redemption_rules', 'internal');--> statement-breakpoint
CREATE TYPE "public"."support_kb_visibility" AS ENUM('public', 'internal');--> statement-breakpoint
CREATE TYPE "public"."support_message_author_type" AS ENUM('customer', 'vendor', 'ai', 'human_agent', 'system');--> statement-breakpoint
CREATE TYPE "public"."support_message_visibility" AS ENUM('public', 'vendor_internal', 'site_internal');--> statement-breakpoint
CREATE TYPE "public"."support_opener_role" AS ENUM('customer', 'vendor');--> statement-breakpoint
CREATE TYPE "public"."support_parent_type" AS ENUM('ticket', 'case');--> statement-breakpoint
CREATE TYPE "public"."support_priority" AS ENUM('low', 'normal', 'high', 'urgent');--> statement-breakpoint
CREATE TYPE "public"."support_ticket_category" AS ENUM('account', 'billing', 'bug', 'how_to', 'complaint', 'other');--> statement-breakpoint
CREATE TYPE "public"."support_ticket_status" AS ENUM('open', 'ai_handling', 'awaiting_user', 'escalated', 'awaiting_agent', 'human_handling', 'resolved', 'closed', 'reopened');--> statement-breakpoint
CREATE TABLE "ai_interventions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"parent_type" "support_parent_type" NOT NULL,
	"parent_id" uuid NOT NULL,
	"agent_name" text NOT NULL,
	"tool_name" text NOT NULL,
	"input_encrypted" text,
	"output_encrypted" text,
	"confidence" numeric(4, 3),
	"decision" "ai_intervention_decision" NOT NULL,
	"tokens_in" integer,
	"tokens_out" integer,
	"latency_ms" integer,
	"cost_usd" numeric(10, 6),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "case_offers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"case_id" uuid NOT NULL,
	"offered_by" "case_offer_by" NOT NULL,
	"outcome" "case_offer_outcome" NOT NULL,
	"amount_cents" integer,
	"reason" text,
	"status" "case_offer_status" DEFAULT 'pending' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"decided_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "case_resolutions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"case_id" uuid NOT NULL,
	"outcome" "case_offer_outcome" NOT NULL,
	"amount_cents" integer,
	"executed_by" "case_resolution_executor" NOT NULL,
	"sumit_refund_id" text,
	"executed_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "case_resolutions_case_id_unique" UNIQUE("case_id")
);
--> statement-breakpoint
CREATE TABLE "support_attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"parent_type" "support_parent_type" NOT NULL,
	"parent_id" uuid NOT NULL,
	"message_id" uuid,
	"cf_image_id" text NOT NULL,
	"variants" jsonb NOT NULL,
	"mime" text NOT NULL,
	"size_bytes" integer NOT NULL,
	"uploader_id" uuid NOT NULL,
	"uploader_role" "support_opener_role" NOT NULL,
	"abuse_status" "support_attachment_abuse_status" DEFAULT 'pending' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "support_kb_articles" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"locale" text NOT NULL,
	"category" "support_kb_category" NOT NULL,
	"title" text NOT NULL,
	"body_md" text NOT NULL,
	"tags" text[] DEFAULT '{}' NOT NULL,
	"visibility" "support_kb_visibility" DEFAULT 'public' NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_by" uuid
);
--> statement-breakpoint
CREATE TABLE "support_messages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"parent_type" "support_parent_type" NOT NULL,
	"parent_id" uuid NOT NULL,
	"author_type" "support_message_author_type" NOT NULL,
	"author_id" uuid,
	"visibility" "support_message_visibility" DEFAULT 'public' NOT NULL,
	"body_encrypted" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"edited_at" timestamp with time zone,
	"deleted_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "support_state_transitions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"parent_type" "support_parent_type" NOT NULL,
	"parent_id" uuid NOT NULL,
	"from_state" text NOT NULL,
	"to_state" text NOT NULL,
	"actor_type" text NOT NULL,
	"actor_id" uuid,
	"reason" text,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "support_tickets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"opener_id" uuid NOT NULL,
	"opener_role" "support_opener_role" NOT NULL,
	"category" "support_ticket_category" NOT NULL,
	"status" "support_ticket_status" DEFAULT 'open' NOT NULL,
	"priority" "support_priority" DEFAULT 'normal' NOT NULL,
	"ai_confidence" numeric(4, 3),
	"assigned_agent_id" uuid,
	"related_purchase_id" uuid,
	"reopen_count" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"resolved_at" timestamp with time zone,
	"closed_at" timestamp with time zone,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL
);
--> statement-breakpoint
CREATE TABLE "transaction_cases" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"purchase_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"vendor_id" uuid NOT NULL,
	"category" "support_case_category" NOT NULL,
	"status" "support_case_status" DEFAULT 'opened' NOT NULL,
	"escape_used" boolean DEFAULT false NOT NULL,
	"vendor_window_hours" integer NOT NULL,
	"vendor_window_expires_at" timestamp with time zone,
	"sla_ai_due_at" timestamp with time zone,
	"sla_human_due_at" timestamp with time zone,
	"autoclose_at" timestamp with time zone,
	"ai_confidence" numeric(4, 3),
	"assigned_agent_id" uuid,
	"reopen_count" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"resolved_at" timestamp with time zone,
	"closed_at" timestamp with time zone,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL
);
--> statement-breakpoint
ALTER TABLE "case_offers" ADD CONSTRAINT "case_offers_case_id_transaction_cases_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."transaction_cases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "case_resolutions" ADD CONSTRAINT "case_resolutions_case_id_transaction_cases_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."transaction_cases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_attachments" ADD CONSTRAINT "support_attachments_message_id_support_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."support_messages"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_attachments" ADD CONSTRAINT "support_attachments_uploader_id_users_id_fk" FOREIGN KEY ("uploader_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_kb_articles" ADD CONSTRAINT "support_kb_articles_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_tickets" ADD CONSTRAINT "support_tickets_opener_id_users_id_fk" FOREIGN KEY ("opener_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_tickets" ADD CONSTRAINT "support_tickets_assigned_agent_id_users_id_fk" FOREIGN KEY ("assigned_agent_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_tickets" ADD CONSTRAINT "support_tickets_related_purchase_id_purchases_id_fk" FOREIGN KEY ("related_purchase_id") REFERENCES "public"."purchases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "transaction_cases" ADD CONSTRAINT "transaction_cases_purchase_id_purchases_id_fk" FOREIGN KEY ("purchase_id") REFERENCES "public"."purchases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "transaction_cases" ADD CONSTRAINT "transaction_cases_customer_id_users_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "transaction_cases" ADD CONSTRAINT "transaction_cases_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "transaction_cases" ADD CONSTRAINT "transaction_cases_assigned_agent_id_users_id_fk" FOREIGN KEY ("assigned_agent_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "ai_interventions_parent_idx" ON "ai_interventions" USING btree ("parent_type","parent_id","created_at");--> statement-breakpoint
CREATE INDEX "case_offers_case_status_idx" ON "case_offers" USING btree ("case_id","status");--> statement-breakpoint
CREATE INDEX "support_attachments_parent_idx" ON "support_attachments" USING btree ("parent_type","parent_id");--> statement-breakpoint
CREATE UNIQUE INDEX "support_kb_slug_locale_idx" ON "support_kb_articles" USING btree ("slug","locale");--> statement-breakpoint
CREATE INDEX "support_messages_parent_idx" ON "support_messages" USING btree ("parent_type","parent_id","created_at");--> statement-breakpoint
CREATE INDEX "support_state_transitions_parent_idx" ON "support_state_transitions" USING btree ("parent_type","parent_id","created_at");--> statement-breakpoint
CREATE INDEX "support_tickets_opener_status_idx" ON "support_tickets" USING btree ("opener_id","status");--> statement-breakpoint
CREATE INDEX "support_tickets_status_updated_idx" ON "support_tickets" USING btree ("status","updated_at");--> statement-breakpoint
CREATE INDEX "tx_cases_status_vendor_window_idx" ON "transaction_cases" USING btree ("status","vendor_window_expires_at");--> statement-breakpoint
CREATE INDEX "tx_cases_status_sla_ai_idx" ON "transaction_cases" USING btree ("status","sla_ai_due_at");--> statement-breakpoint
CREATE INDEX "tx_cases_status_sla_human_idx" ON "transaction_cases" USING btree ("status","sla_human_due_at");--> statement-breakpoint
CREATE INDEX "tx_cases_status_autoclose_idx" ON "transaction_cases" USING btree ("status","autoclose_at");--> statement-breakpoint
CREATE UNIQUE INDEX "tx_cases_purchase_open_uniq" ON "transaction_cases" USING btree ("purchase_id") WHERE status NOT IN ('resolved','closed');
CREATE INDEX IF NOT EXISTS support_kb_articles_tags_gin_idx ON support_kb_articles USING gin (tags);
