-- CreateEnum
CREATE TYPE "ProgressReportDeadlineSlot" AS ENUM ('FIRST', 'SECOND', 'FINAL');

-- Step 1: add the column as nullable so existing rows survive the backfill.
ALTER TABLE "progress_report_deadlines" ADD COLUMN "slot" "ProgressReportDeadlineSlot";

-- Step 2: refuse to continue if any report has more than three active deadlines.
-- There is no fourth slot, so assigning those rows would need a human decision.
-- The migration reports the affected progress report IDs instead of guessing.
DO $$
DECLARE bad_reports TEXT;
BEGIN
  SELECT string_agg(progress_report_id::text, ', ' ORDER BY progress_report_id)
    INTO bad_reports
    FROM (
      SELECT progress_report_id
        FROM "progress_report_deadlines"
       WHERE deleted_at IS NULL
       GROUP BY progress_report_id
      HAVING count(*) > 3
    ) AS overflow;

  IF bad_reports IS NOT NULL THEN
    RAISE EXCEPTION
      'Cannot assign deadline slots: progress reports (%) have more than three active deadlines. Resolve them by hand first.',
      bad_reports;
  END IF;
END $$;

-- Step 3: refuse to continue if a soft-deleted row would collide with an
-- active row once slots are assigned. The unique constraint below covers the
-- whole table, so every row still occupies its slot even when soft-deleted.
DO $$
DECLARE bad_reports TEXT;
BEGIN
  SELECT string_agg(progress_report_id::text, ', ' ORDER BY progress_report_id)
    INTO bad_reports
    FROM (
      SELECT progress_report_id
        FROM "progress_report_deadlines"
       GROUP BY progress_report_id
      HAVING count(*) > 3
    ) AS overflow;

  IF bad_reports IS NOT NULL THEN
    RAISE EXCEPTION
      'Cannot assign deadline slots: progress reports (%) have more than three deadline rows in total (including soft-deleted ones). Resolve them by hand first.',
      bad_reports;
  END IF;
END $$;

-- Step 4: the earliest deadline becomes FIRST, then SECOND, then FINAL.
-- Active rows are ranked before soft-deleted ones so a live deadline always
-- wins the earlier slot.
UPDATE "progress_report_deadlines" AS d
   SET "slot" = (
         CASE ranked.position
           WHEN 1 THEN 'FIRST'
           WHEN 2 THEN 'SECOND'
           ELSE 'FINAL'
         END
       )::"ProgressReportDeadlineSlot"
  FROM (
    SELECT id,
           row_number() OVER (
             PARTITION BY progress_report_id
             ORDER BY (deleted_at IS NOT NULL), deadline, id
           ) AS position
      FROM "progress_report_deadlines"
  ) AS ranked
 WHERE d.id = ranked.id;

-- AlterTable
ALTER TABLE "progress_report_deadlines" ALTER COLUMN "slot" SET NOT NULL;

-- CreateIndex
CREATE INDEX "progress_report_deadlines_slot_idx" ON "progress_report_deadlines"("slot");

-- CreateIndex
CREATE UNIQUE INDEX "progress_report_deadlines_progress_report_id_slot_key" ON "progress_report_deadlines"("progress_report_id", "slot");

-- Step 5: one progress report per semester of a year. This fails loudly if
-- duplicates already exist, which is the correct outcome.
CREATE UNIQUE INDEX "progress_reports_year_semester_key" ON "progress_reports"("year", "semester");

-- DropForeignKey
ALTER TABLE "progress_report_deadlines" DROP CONSTRAINT "progress_report_deadlines_progress_report_id_fkey";

-- AddForeignKey
ALTER TABLE "progress_report_deadlines" ADD CONSTRAINT "progress_report_deadlines_progress_report_id_fkey" FOREIGN KEY ("progress_report_id") REFERENCES "progress_reports"("id") ON DELETE CASCADE ON UPDATE CASCADE;
