80 lines
3.1 KiB
MySQL
80 lines
3.1 KiB
MySQL
|
-- Round finalization fields
|
||
|
|
ALTER TABLE "Round" ADD COLUMN "gracePeriodEndsAt" TIMESTAMP(3);
|
||
|
|
ALTER TABLE "Round" ADD COLUMN "finalizedAt" TIMESTAMP(3);
|
||
|
|
ALTER TABLE "Round" ADD COLUMN "finalizedBy" TEXT;
|
||
|
|
|
||
|
|
-- ProjectRoundState proposed outcome for finalization pool
|
||
|
|
ALTER TABLE "ProjectRoundState" ADD COLUMN "proposedOutcome" "ProjectRoundStateValue";
|
||
|
|
|
||
|
|
-- Mark already-closed rounds as pre-finalized IF their projects were already
|
||
|
|
-- advanced to the IMMEDIATELY NEXT round (sortOrder = current + 1).
|
||
|
|
-- We check the next sequential round only, not any subsequent round, because
|
||
|
|
-- projects can appear in non-adjacent rounds (e.g. special award tracks) without
|
||
|
|
-- implying the current round was finalized.
|
||
|
|
UPDATE "Round" r
|
||
|
|
SET "finalizedAt" = NOW(), "finalizedBy" = 'system-migration'
|
||
|
|
WHERE r.status IN ('ROUND_CLOSED', 'ROUND_ARCHIVED')
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM "Round" next_r
|
||
|
|
JOIN "ProjectRoundState" next_prs ON next_prs."roundId" = next_r.id
|
||
|
|
JOIN "ProjectRoundState" cur_prs ON cur_prs."roundId" = r.id
|
||
|
|
AND cur_prs."projectId" = next_prs."projectId"
|
||
|
|
WHERE next_r."competitionId" = r."competitionId"
|
||
|
|
AND next_r."sortOrder" = r."sortOrder" + 1
|
||
|
|
LIMIT 1
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ─── Backfill terminal states for already-finalized rounds ───────────────────
|
||
|
|
-- These rounds were finalized manually before this system existed.
|
||
|
|
-- Set ProjectRoundState to accurate terminal states so the data matches reality.
|
||
|
|
-- All updates are guarded by current state + round type to avoid touching anything unexpected.
|
||
|
|
|
||
|
|
-- R0 (INTAKE, closed): All 214 projects completed intake successfully → PASSED
|
||
|
|
-- Guard: only touch COMPLETED states in closed INTAKE rounds marked as finalized
|
||
|
|
UPDATE "ProjectRoundState" prs
|
||
|
|
SET state = 'PASSED', "proposedOutcome" = 'PASSED'
|
||
|
|
FROM "Round" r
|
||
|
|
WHERE prs."roundId" = r.id
|
||
|
|
AND r."roundType" = 'INTAKE'
|
||
|
|
AND r.status = 'ROUND_CLOSED'
|
||
|
|
AND r."finalizedAt" IS NOT NULL
|
||
|
|
AND prs.state = 'COMPLETED';
|
||
|
|
|
||
|
|
-- R1 (FILTERING, closed): Set states based on FilteringResult outcomes
|
||
|
|
-- Projects that passed filtering → PASSED
|
||
|
|
UPDATE "ProjectRoundState" prs
|
||
|
|
SET state = 'PASSED', "proposedOutcome" = 'PASSED'
|
||
|
|
FROM "Round" r
|
||
|
|
WHERE prs."roundId" = r.id
|
||
|
|
AND r."roundType" = 'FILTERING'
|
||
|
|
AND r.status = 'ROUND_CLOSED'
|
||
|
|
AND r."finalizedAt" IS NOT NULL
|
||
|
|
AND prs.state = 'PENDING'
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 1 FROM "FilteringResult" fr
|
||
|
|
WHERE fr."projectId" = prs."projectId"
|
||
|
|
AND (
|
||
|
|
fr."finalOutcome" = 'PASSED'
|
||
|
|
OR (fr."finalOutcome" IS NULL AND fr.outcome IN ('PASSED', 'FLAGGED'))
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Projects that were filtered out → REJECTED
|
||
|
|
UPDATE "ProjectRoundState" prs
|
||
|
|
SET state = 'REJECTED', "proposedOutcome" = 'REJECTED'
|
||
|
|
FROM "Round" r
|
||
|
|
WHERE prs."roundId" = r.id
|
||
|
|
AND r."roundType" = 'FILTERING'
|
||
|
|
AND r.status = 'ROUND_CLOSED'
|
||
|
|
AND r."finalizedAt" IS NOT NULL
|
||
|
|
AND prs.state = 'PENDING'
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 1 FROM "FilteringResult" fr
|
||
|
|
WHERE fr."projectId" = prs."projectId"
|
||
|
|
AND (
|
||
|
|
fr."finalOutcome" = 'FILTERED_OUT'
|
||
|
|
OR (fr."finalOutcome" IS NULL AND fr.outcome = 'FILTERED_OUT')
|
||
|
|
)
|
||
|
|
);
|