-- Stop before adding the unique index if duplicate assignments exist.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM "progress_report_ministries"
    GROUP BY "progress_report_id", "ministry_id"
    HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Duplicate progress report Ministry assignments exist';
  END IF;
END $$;

-- Rename the misspelled attachment column without losing data.
ALTER TABLE "progress_report_ministries"
  RENAME COLUMN "attachement" TO "attachment";

-- Replace the old workflow enum with the final workflow values.
ALTER TABLE "progress_report_ministries"
  ALTER COLUMN "status" DROP DEFAULT;

ALTER TYPE "ProgressReportMinistryStatus"
  RENAME TO "ProgressReportMinistryStatus_old";

CREATE TYPE "ProgressReportMinistryStatus" AS ENUM (
  'DRAFT',
  'SHARED_WITH_PSWG',
  'PSWG_REVIEWED',
  'SUBMITTED',
  'CDC_UNDER_REVIEW',
  'COMPLETED'
);

ALTER TABLE "progress_report_ministries"
  ALTER COLUMN "status" TYPE "ProgressReportMinistryStatus"
  USING (
    CASE "status"::TEXT
      WHEN 'SHARED' THEN 'SHARED_WITH_PSWG'
      WHEN 'REVIEWING' THEN 'CDC_UNDER_REVIEW'
      WHEN 'REVIEWED' THEN 'COMPLETED'
      WHEN 'UPDATING' THEN 'PSWG_REVIEWED'
      WHEN 'SUBMITTED' THEN 'SUBMITTED'
      ELSE 'DRAFT'
    END
  )::"ProgressReportMinistryStatus";

DROP TYPE "ProgressReportMinistryStatus_old";

ALTER TABLE "progress_report_ministries"
  ALTER COLUMN "status" SET DEFAULT 'DRAFT';

-- Apply the final nullable fields and defaults.
ALTER TABLE "progress_report_ministries"
  DROP CONSTRAINT "progress_report_ministries_user_id_fkey",
  ALTER COLUMN "issues" SET DEFAULT 0,
  ALTER COLUMN "user_id" DROP NOT NULL,
  ADD COLUMN "submitted_at" TIMESTAMP(3);

-- Add final indexes.
CREATE UNIQUE INDEX "progress_report_ministries_progress_report_id_ministry_id_key"
  ON "progress_report_ministries"("progress_report_id", "ministry_id");

CREATE INDEX "progress_report_ministries_submitted_at_idx"
  ON "progress_report_ministries"("submitted_at");

-- Add final foreign keys owned by the Progress Report Ministry table.
ALTER TABLE "progress_report_ministries"
  ADD CONSTRAINT "progress_report_ministries_progress_report_id_fkey"
  FOREIGN KEY ("progress_report_id") REFERENCES "progress_reports"("id")
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "progress_report_ministries"
  ADD CONSTRAINT "progress_report_ministries_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;
