CREATE TABLE IF NOT EXISTS "e2e_factory_commands" (
  "run_id" uuid NOT NULL,
  "request_id" uuid NOT NULL,
  "body_hash" text NOT NULL,
  "operation" text NOT NULL,
  "result" jsonb,
  "completed_at" timestamp with time zone,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "e2e_factory_commands_pk" PRIMARY KEY ("run_id", "request_id")
);

CREATE INDEX IF NOT EXISTS "e2e_factory_commands_created_at_idx"
  ON "e2e_factory_commands" ("created_at");

CREATE TABLE IF NOT EXISTS "e2e_factory_entities" (
  "run_id" uuid NOT NULL,
  "kind" text NOT NULL,
  "entity_id" uuid NOT NULL,
  "operation" text NOT NULL,
  "dependency_order" integer NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "deleted_at" timestamp with time zone,
  CONSTRAINT "e2e_factory_entities_pk" PRIMARY KEY ("run_id", "kind", "entity_id")
);

CREATE UNIQUE INDEX IF NOT EXISTS "e2e_factory_entities_active_kind_entity_uq"
  ON "e2e_factory_entities" ("kind", "entity_id") WHERE "deleted_at" IS NULL;

CREATE INDEX IF NOT EXISTS "e2e_factory_entities_run_order_idx"
  ON "e2e_factory_entities" ("run_id", "dependency_order");

CREATE TABLE IF NOT EXISTS "e2e_factory_runs" (
  "run_id" uuid PRIMARY KEY NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "touched_at" timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "e2e_run_id" uuid;
ALTER TABLE "llm_jobs" ADD COLUMN IF NOT EXISTS "queue_chain_override" jsonb;

DO $$ BEGIN
  ALTER TABLE "llm_jobs" ADD CONSTRAINT "llm_jobs_e2e_run_id_e2e_factory_runs_run_id_fk"
    FOREIGN KEY ("e2e_run_id") REFERENCES "public"."e2e_factory_runs"("run_id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

CREATE INDEX IF NOT EXISTS "llm_jobs_e2e_run_idx" ON "llm_jobs" ("e2e_run_id", "status");
