-- Create the meeting summary status lookup table.
CREATE TABLE "meeting_summary_statuses" (
    "id" SERIAL NOT NULL,
    "code" VARCHAR(50) NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "deleted_at" TIMESTAMP(3),

    CONSTRAINT "meeting_summary_statuses_pkey" PRIMARY KEY ("id")
);

-- Unique code + lookup indexes (match the Prisma @unique / @@index).
CREATE UNIQUE INDEX "meeting_summary_statuses_code_key" ON "meeting_summary_statuses"("code");
CREATE INDEX "meeting_summary_statuses_code_idx" ON "meeting_summary_statuses"("code");
CREATE INDEX "meeting_summary_statuses_deleted_at_idx" ON "meeting_summary_statuses"("deleted_at");

-- Seed the four canonical statuses with fixed ids.
INSERT INTO "meeting_summary_statuses" ("id", "code", "name", "updated_at") VALUES
  (1, 'DRAFT', 'Draft', CURRENT_TIMESTAMP),
  (2, 'REVIEWED', 'Reviewed', CURRENT_TIMESTAMP),
  (3, 'SUBMITTED', 'Submitted', CURRENT_TIMESTAMP),
  (4, 'COMPLETED', 'Completed', CURRENT_TIMESTAMP);

-- Move the serial sequence past the fixed ids so future inserts don't collide.
SELECT setval('meeting_summary_statuses_id_seq', 4, true);

-- Add the FK column as NULLable first so we can backfill existing rows.
ALTER TABLE "meeting_summaries" ADD COLUMN "meeting_summary_status_id" INTEGER;

-- Backfill from the legacy free-text status (unknown values default to Draft).
UPDATE "meeting_summaries" SET "meeting_summary_status_id" =
  CASE "status"
    WHEN 'Drafted' THEN 1
    WHEN 'Review' THEN 2
    WHEN 'Submitted' THEN 3
    WHEN 'Completed' THEN 4
    ELSE 1
  END;

-- Lock the column down and wire up the foreign key + index.
ALTER TABLE "meeting_summaries" ALTER COLUMN "meeting_summary_status_id" SET NOT NULL;
CREATE INDEX "meeting_summaries_meeting_summary_status_id_idx" ON "meeting_summaries"("meeting_summary_status_id");
ALTER TABLE "meeting_summaries" ADD CONSTRAINT "meeting_summaries_meeting_summary_status_id_fkey" FOREIGN KEY ("meeting_summary_status_id") REFERENCES "meeting_summary_statuses"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- Drop the legacy string status column and its index.
DROP INDEX "meeting_summaries_status_idx";
ALTER TABLE "meeting_summaries" DROP COLUMN "status";

-- Rename the shared_with_pswg flag to share.
ALTER TABLE "meeting_summaries" RENAME COLUMN "shared_with_pswg" TO "share";
ALTER INDEX "meeting_summaries_shared_with_pswg_idx" RENAME TO "meeting_summaries_share_idx";
