-- Brief.followUpAt (informational-only follow-up date, nullable)
ALTER TABLE "Brief" ADD COLUMN "followUpAt" TIMESTAMP(3);

-- BriefMeeting: one row per alignment meeting (repeatable, was 1:1 before)
CREATE TABLE "BriefMeeting" (
    "id" TEXT NOT NULL,
    "briefId" TEXT NOT NULL,
    "date" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

ALTER TABLE "BriefMeeting" ADD CONSTRAINT "BriefMeeting_briefId_fkey" FOREIGN KEY ("briefId") REFERENCES "Brief"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- BriefParticipant moves from Brief-level to BriefMeeting-level. Add the new
-- FK column nullable first so we can backfill before enforcing NOT NULL.
ALTER TABLE "BriefParticipant" ADD COLUMN "briefMeetingId" TEXT;

-- Backfill: one BriefMeeting per existing Brief, dated from the diagnostic's
-- old phase1MeetingAt (preserves the real meeting date for every existing
-- tenant's brief instead of losing it).
INSERT INTO "BriefMeeting" ("id", "briefId", "date")
SELECT gen_random_uuid(), "b"."id", "d"."phase1MeetingAt"
FROM "Brief" "b"
JOIN "Diagnostic" "d" ON "d"."id" = "b"."diagnosticId";

-- Re-point existing participants at the newly created meeting for their brief.
UPDATE "BriefParticipant" "bp"
SET "briefMeetingId" = "bm"."id"
FROM "BriefMeeting" "bm"
WHERE "bm"."briefId" = "bp"."briefId";

-- Now safe to enforce NOT NULL and drop the old direct-to-Brief relation.
ALTER TABLE "BriefParticipant" ALTER COLUMN "briefMeetingId" SET NOT NULL;
ALTER TABLE "BriefParticipant" DROP CONSTRAINT "BriefParticipant_briefId_fkey";
ALTER TABLE "BriefParticipant" DROP COLUMN "briefId";
ALTER TABLE "BriefParticipant" ADD CONSTRAINT "BriefParticipant_briefMeetingId_fkey" FOREIGN KEY ("briefMeetingId") REFERENCES "BriefMeeting"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Diagnostic.phase1MeetingAt is superseded by BriefMeeting.date (the
-- "Reunión Fase 1" timeline milestone is now derived from the earliest
-- meeting instead of this separate field). Safe to drop: every existing
-- non-null value was just copied into a BriefMeeting row above.
ALTER TABLE "Diagnostic" DROP COLUMN "phase1MeetingAt";