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

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

-- Step 2: refuse to continue if any report has more than two meeting rows.
-- Assigning slots there would need a human decision, so we stop instead of guessing.
DO $$
DECLARE bad_reports TEXT;
BEGIN
  SELECT string_agg(progress_report_id::text, ', ')
    INTO bad_reports
    FROM (
      SELECT progress_report_id
        FROM "progress_report_meetings"
       GROUP BY progress_report_id
      HAVING count(*) > 2
    ) AS overflow;

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

-- Step 3: earliest meeting becomes FIRST, the next becomes SECOND.
-- Active rows are ranked before soft-deleted ones so a live meeting always wins FIRST.
UPDATE "progress_report_meetings" AS m
   SET "slot" = CASE ranked.position WHEN 1 THEN 'FIRST' ELSE 'SECOND' END::"ProgressReportMeetingSlot"
  FROM (
    SELECT id,
           row_number() OVER (
             PARTITION BY progress_report_id
             ORDER BY (deleted_at IS NOT NULL), meeting_date, start_time, created_at
           ) AS position
      FROM "progress_report_meetings"
  ) AS ranked
 WHERE m.id = ranked.id;

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

-- CreateIndex
CREATE INDEX "progress_report_meetings_slot_idx" ON "progress_report_meetings"("slot");

-- CreateIndex
CREATE UNIQUE INDEX "progress_report_meetings_progress_report_id_slot_key" ON "progress_report_meetings"("progress_report_id", "slot");

-- DropForeignKey
ALTER TABLE "progress_report_meetings" DROP CONSTRAINT "progress_report_meetings_progress_report_id_fkey";

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