-- Lab workflow refactor: remove task assignment/priority, group tasks by prosthesis,
-- add importance flag, status timeline, and per-case comments.
-- Local dev data only: existing tasks are truncated and regenerated on next dispatch/send.

-- 1. Clear existing task data (task shape changes: tooth -> teeth[]).
TRUNCATE TABLE "lab_case_tasks" CASCADE;

-- 2. Drop assignment / priority machinery.
ALTER TABLE "lab_case_tasks" DROP CONSTRAINT IF EXISTS "lab_case_tasks_assigneeUserId_fkey";
DROP INDEX IF EXISTS "lab_case_tasks_assigneeUserId_priority_createdAt_idx";
DROP INDEX IF EXISTS "lab_case_tasks_assignedAt_labCaseId_priority_idx";
DROP INDEX IF EXISTS "lab_case_tasks_labCaseId_treatmentDetailId_tooth_stepOrder_key";

ALTER TABLE "lab_case_tasks"
  DROP COLUMN IF EXISTS "assigneeUserId",
  DROP COLUMN IF EXISTS "assignedAt",
  DROP COLUMN IF EXISTS "priority",
  DROP COLUMN IF EXISTS "tooth";

-- 3. Rebuild LabTaskStatus enum without PENDING.
ALTER TABLE "lab_case_tasks" ALTER COLUMN "status" DROP DEFAULT;
ALTER TYPE "LabTaskStatus" RENAME TO "LabTaskStatus_old";
CREATE TYPE "LabTaskStatus" AS ENUM ('IN_PROGRESS', 'COMPLETED');
ALTER TABLE "lab_case_tasks"
  ALTER COLUMN "status" TYPE "LabTaskStatus" USING ("status"::text::"LabTaskStatus");
ALTER TABLE "lab_case_tasks" ALTER COLUMN "status" SET DEFAULT 'IN_PROGRESS';
DROP TYPE "LabTaskStatus_old";

-- 4. New task columns.
ALTER TABLE "lab_case_tasks" ADD COLUMN "teeth" JSONB NOT NULL DEFAULT '[]';
ALTER TABLE "lab_case_tasks" ALTER COLUMN "teeth" DROP DEFAULT;
ALTER TABLE "lab_case_tasks" ADD COLUMN "isImportant" BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE "lab_case_tasks" ADD COLUMN "lastStatusChangedByUserId" TEXT;
ALTER TABLE "lab_case_tasks" ADD COLUMN "lastStatusChangedAt" TIMESTAMP(3);

-- 5. New unique + indexes.
CREATE UNIQUE INDEX "lab_case_tasks_labCaseId_treatmentDetailId_prosthesisTypeCode_stepOrder_key"
  ON "lab_case_tasks"("labCaseId", "treatmentDetailId", "prosthesisTypeCode", "stepOrder");
CREATE INDEX "lab_case_tasks_labCaseId_isImportant_idx"
  ON "lab_case_tasks"("labCaseId", "isImportant");

ALTER TABLE "lab_case_tasks" ADD CONSTRAINT "lab_case_tasks_lastStatusChangedByUserId_fkey"
  FOREIGN KEY ("lastStatusChangedByUserId") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- 6. Task status timeline.
CREATE TABLE "lab_case_task_status_events" (
  "id" TEXT NOT NULL,
  "taskId" TEXT NOT NULL,
  "fromStatus" "LabTaskStatus",
  "toStatus" "LabTaskStatus" NOT NULL,
  "changedByUserId" TEXT,
  "changedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "lab_case_task_status_events_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "lab_case_task_status_events_taskId_changedAt_idx"
  ON "lab_case_task_status_events"("taskId", "changedAt");
ALTER TABLE "lab_case_task_status_events" ADD CONSTRAINT "lab_case_task_status_events_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "lab_case_tasks"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "lab_case_task_status_events" ADD CONSTRAINT "lab_case_task_status_events_changedByUserId_fkey"
  FOREIGN KEY ("changedByUserId") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- 7. Per-case comments.
CREATE TYPE "LabCaseCommentSide" AS ENUM ('LAB', 'CLINIC');
CREATE TABLE "lab_case_comments" (
  "id" TEXT NOT NULL,
  "labCaseId" TEXT NOT NULL,
  "authorUserId" TEXT,
  "authorOrganizationId" TEXT,
  "authorSide" "LabCaseCommentSide" NOT NULL,
  "body" TEXT NOT NULL,
  "visibleToClinic" BOOLEAN NOT NULL DEFAULT false,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "lab_case_comments_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "lab_case_comments_labCaseId_createdAt_idx"
  ON "lab_case_comments"("labCaseId", "createdAt");
ALTER TABLE "lab_case_comments" ADD CONSTRAINT "lab_case_comments_labCaseId_fkey"
  FOREIGN KEY ("labCaseId") REFERENCES "lab_cases"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "lab_case_comments" ADD CONSTRAINT "lab_case_comments_authorUserId_fkey"
  FOREIGN KEY ("authorUserId") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "lab_case_comments" ADD CONSTRAINT "lab_case_comments_authorOrganizationId_fkey"
  FOREIGN KEY ("authorOrganizationId") REFERENCES "organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;
