CREATE TABLE "support_messages" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "parent_type" text NOT NULL,
  "parent_id" text NOT NULL,
  "author_type" text NOT NULL,
  "body" text NOT NULL,
  "visibility" text,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "support_state_transitions" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "parent_type" text NOT NULL,
  "parent_id" text NOT NULL,
  "from_status" text NOT NULL,
  "to_status" text NOT NULL,
  "actor_id" text NOT NULL,
  "reason" text,
  "at" timestamp with time zone NOT NULL DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "support_attachments" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "parent_type" text NOT NULL,
  "parent_id" text NOT NULL,
  "object_key" text NOT NULL,
  "file_name" text,
  "mime_type" text,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
--> statement-breakpoint
ALTER TABLE "support_messages"
  ADD COLUMN "tenant_id" uuid REFERENCES "tenants"("id") ON DELETE CASCADE,
  ADD COLUMN "author_id" uuid REFERENCES "users"("id") ON DELETE SET NULL,
  ADD COLUMN "author_name" text,
  ADD COLUMN "source" text NOT NULL DEFAULT 'web',
  ADD COLUMN "deleted_at" timestamp with time zone;
--> statement-breakpoint
ALTER TABLE "support_state_transitions"
  ADD COLUMN "tenant_id" uuid REFERENCES "tenants"("id") ON DELETE CASCADE;
--> statement-breakpoint
ALTER TABLE "support_attachments"
  ADD COLUMN "tenant_id" uuid REFERENCES "tenants"("id") ON DELETE CASCADE,
  ADD COLUMN "message_id" uuid,
  ADD COLUMN "url" text,
  ADD COLUMN "size_bytes" integer;
--> statement-breakpoint
ALTER TABLE "sla_policies"
  ADD COLUMN "scope_key" text,
  ADD COLUMN "target" text,
  ADD COLUMN "due_minutes" integer;
--> statement-breakpoint
CREATE INDEX "idx_support_messages_ticket" ON "support_messages" ("tenant_id", "parent_type", "parent_id", "created_at");
--> statement-breakpoint
CREATE INDEX "idx_support_transitions_ticket" ON "support_state_transitions" ("tenant_id", "parent_type", "parent_id", "at");
--> statement-breakpoint
CREATE INDEX "idx_support_attachments_message" ON "support_attachments" ("tenant_id", "message_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "idx_sla_policies_scope_key" ON "sla_policies" ("scope_key");
--> statement-breakpoint
CREATE INDEX "idx_sla_policies_tenant_priority" ON "sla_policies" ("tenant_id", "priority", "target");
--> statement-breakpoint
INSERT INTO "support_messages" (
  "id",
  "parent_type",
  "parent_id",
  "tenant_id",
  "author_type",
  "author_id",
  "author_name",
  "body",
  "visibility",
  "source",
  "deleted_at",
  "created_at"
)
SELECT
  tm."id",
  'ticket',
  tm."ticket_id"::text,
  tm."tenant_id",
  tm."author_type",
  tm."author_id",
  tm."author_name",
  tm."content",
  tm."source",
  tm."source",
  tm."deleted_at",
  tm."created_at"
FROM "ticket_messages" tm
ON CONFLICT ("id") DO NOTHING;
--> statement-breakpoint
INSERT INTO "support_attachments" (
  "id",
  "parent_type",
  "parent_id",
  "tenant_id",
  "message_id",
  "object_key",
  "file_name",
  "mime_type",
  "url",
  "size_bytes",
  "created_at"
)
SELECT
  tma."id",
  'ticket_message',
  tma."message_id"::text,
  tma."tenant_id",
  tma."message_id",
  tma."r2_key",
  tma."filename",
  tma."mime_type",
  tma."url",
  tma."size_bytes",
  tma."created_at"
FROM "ticket_message_attachments" tma
ON CONFLICT ("id") DO NOTHING;
--> statement-breakpoint
INSERT INTO "support_state_transitions" (
  "parent_type",
  "parent_id",
  "tenant_id",
  "from_status",
  "to_status",
  "actor_id",
  "reason",
  "at"
)
SELECT
  'ticket',
  t."id"::text,
  t."tenant_id",
  'open',
  'resolved',
  COALESCE(t."assignee_id"::text, 'legacy-backfill'),
  'legacy resolved backfill',
  t."resolved_at"
FROM "tickets" t
WHERE t."resolved_at" IS NOT NULL
UNION ALL
SELECT
  'ticket',
  t."id"::text,
  t."tenant_id",
  CASE WHEN t."resolved_at" IS NOT NULL THEN 'resolved' ELSE 'open' END,
  'closed',
  COALESCE(t."assignee_id"::text, 'legacy-backfill'),
  'legacy closed backfill',
  t."closed_at"
FROM "tickets" t
WHERE t."closed_at" IS NOT NULL;
--> statement-breakpoint
UPDATE "sla_policies"
SET
  "scope_key" = "tenant_id"::text || ':' || "priority" || ':resolution',
  "target" = 'resolution',
  "due_minutes" = "resolution_hours" * 60
WHERE "scope_key" IS NULL;
