CREATE TABLE "translation_jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"deal_id" uuid NOT NULL,
	"target_locale" text NOT NULL,
	"status" text DEFAULT 'PENDING' NOT NULL,
	"attempt" integer DEFAULT 0 NOT NULL,
	"last_error" text,
	"cost_usd" numeric(10, 6),
	"input_tokens" integer,
	"output_tokens" integer,
	"force_fresh" boolean DEFAULT false NOT NULL,
	"scheduled_at" timestamp with time zone,
	"started_at" timestamp with time zone,
	"finished_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "translation_memory" (
	"source_hash" text NOT NULL,
	"source_locale" text NOT NULL,
	"target_locale" text NOT NULL,
	"source_text" text NOT NULL,
	"translated_text" text NOT NULL,
	"model_id" text NOT NULL,
	"quality_score" integer,
	"usage_count" integer DEFAULT 1 NOT NULL,
	"last_used_at" timestamp with time zone DEFAULT now() NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "translation_memory_source_hash_source_locale_target_locale_pk" PRIMARY KEY("source_hash","source_locale","target_locale")
);
--> statement-breakpoint
ALTER TABLE "translation_jobs" ADD CONSTRAINT "translation_jobs_deal_id_deals_id_fk" FOREIGN KEY ("deal_id") REFERENCES "public"."deals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "translation_jobs" ADD CONSTRAINT "translation_jobs_target_locale_languages_code_fk" FOREIGN KEY ("target_locale") REFERENCES "public"."languages"("code") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "translation_memory" ADD CONSTRAINT "translation_memory_source_locale_languages_code_fk" FOREIGN KEY ("source_locale") REFERENCES "public"."languages"("code") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "translation_memory" ADD CONSTRAINT "translation_memory_target_locale_languages_code_fk" FOREIGN KEY ("target_locale") REFERENCES "public"."languages"("code") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "translation_jobs_deal_idx" ON "translation_jobs" USING btree ("deal_id");--> statement-breakpoint
CREATE INDEX "translation_jobs_status_idx" ON "translation_jobs" USING btree ("status");--> statement-breakpoint
CREATE INDEX "translation_jobs_scheduled_at_idx" ON "translation_jobs" USING btree ("scheduled_at");--> statement-breakpoint
CREATE UNIQUE INDEX "translation_jobs_deal_locale_active_idx" ON "translation_jobs" USING btree ("deal_id","target_locale") WHERE "translation_jobs"."status" IN ('PENDING','RUNNING','PENDING_BUDGET');--> statement-breakpoint
CREATE INDEX "tm_last_used_idx" ON "translation_memory" USING btree ("last_used_at");--> statement-breakpoint
CREATE INDEX "tm_locale_pair_idx" ON "translation_memory" USING btree ("source_locale","target_locale");