brackt/scripts/backfill-major-linking.ts

/**
 * Backfill: unify existing majors for "score once, fan out everywhere".
 *
 * Existing data predates the primary-event model and the completion fan-out, so
 * it's in three inconsistent states. This script reconciles them idempotently:
 *
 *   1. Link orphaned qualifying events (tournament_id IS NULL) to a canonical
 *      tournament via extractTournamentIdentity + upsertTournament. Same-major
 *      windows collapse onto one canonical row (unique sportId+name+year).
 *   2. Designate a primary scoring_event per BRACKET major (tennis/CS2) — the
 *      window holding the actual bracket/stage/results work. Golf is left with no
 *      primary (it's scored on the canonical tournament page).
 *   3. Backfill canonical tournament_results from the chosen source window's
 *      event_results (placement → canonical participant), and set tournament
 *      status from whether that window is complete.
 *   4. Reconcile every sibling window via syncTournamentResults, then recompute
 *      each sports_season's majors_completed from its completed qualifying events.
 *
 * Safe to re-run. Validate on a DB snapshot first. Reads DATABASE_URL.
 *
 *   npx tsx scripts/backfill-major-linking.ts          # apply
 *   npx tsx scripts/backfill-major-linking.ts --dry    # report only
 */

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { and, eq, isNull } from "drizzle-orm";
import * as schema from "../database/schema.js";
import { DatabaseContext, database } from "../database/context.js";
import { extractTournamentIdentity } from "../app/lib/tournament-identity.js";
import { isBracketMajor } from "../app/lib/event-utils.js";
import { upsertTournament, updateTournamentStatus } from "../app/models/tournament.js";
import { upsertTournamentResult } from "../app/models/tournament-result.js";
import { getEventResults } from "../app/models/event-result.js";
import { findPlayoffMatchesByEventId } from "../app/models/playoff-match.js";
import { getCs2StageResultsMapForEvent } from "../app/models/cs2-major-stage.js";
import { syncTournamentResults, syncMajorFromPrimaryEvent } from "../app/services/sync-tournament-results.js";

const DRY = process.argv.includes("--dry");
const log = (...a: unknown[]) => console.log(...a);

async function run() {
  const db = database();

  // ── Step 1: link orphaned qualifying events ───────────────────────────────
  const orphans = await db.query.scoringEvents.findMany({
    where: and(
      eq(schema.scoringEvents.isQualifyingEvent, true),
      isNull(schema.scoringEvents.tournamentId)
    ),
    with: { sportsSeason: true },
  });
  log(`\n[1] Orphaned qualifying events (no tournament link): ${orphans.length}`);
  for (const ev of orphans) {
    try {
      const identity = extractTournamentIdentity({
        name: ev.name,
        eventDate: ev.eventDate,
        eventType: ev.eventType,
      });
      log(`  • "${ev.name}" → ${identity.name} ${identity.year}`);
      if (!DRY) {
        const tournament = await upsertTournament({
          sportId: ev.sportsSeason.sportId,
          name: identity.name,
          year: identity.year,
          startsAt: ev.eventStartsAt ?? null,
        });
        await db
          .update(schema.scoringEvents)
          .set({ tournamentId: tournament.id, updatedAt: new Date() })
          .where(eq(schema.scoringEvents.id, ev.id));
      }
    } catch (e) {
      log(`  ! could not parse "${ev.name}" — link by hand: ${(e as Error).message}`);
    }
  }

  // ── Gather all tournaments that now have linked events ─────────────────────
  const linkedEvents = await db.query.scoringEvents.findMany({
    where: eq(schema.scoringEvents.isQualifyingEvent, true),
    with: { sportsSeason: { with: { sport: true } } },
  });
  const byTournament = new Map<string, typeof linkedEvents>();
  for (const ev of linkedEvents) {
    if (!ev.tournamentId) continue;
    const arr = byTournament.get(ev.tournamentId) ?? [];
    arr.push(ev);
    byTournament.set(ev.tournamentId, arr);
  }
  log(`\n[2-4] Tournaments with linked events: ${byTournament.size}`);

  // How "scored" a window is, used to pick the source/primary window.
  async function scoredWeight(eventId: string): Promise<number> {
    const [results, matches, stages] = await Promise.all([
      getEventResults(eventId),
      findPlayoffMatchesByEventId(eventId),
      getCs2StageResultsMapForEvent(eventId),
    ]);
    const placed = results.filter((r) => r.placement !== null).length;
    return placed + matches.length + stages.size;
  }

  const touchedSportsSeasons = new Set<string>();

  for (const [tournamentId, events] of byTournament) {
    const simulatorType = events[0]?.sportsSeason?.sport?.simulatorType ?? null;
    const bracketMajor = isBracketMajor(simulatorType);

    // Choose the source window: most-scored, tie-break earliest created.
    const weighted = await Promise.all(
      events.map(async (e) => ({ e, w: await scoredWeight(e.id) }))
    );
    weighted.sort(
      (a, b) =>
        b.w - a.w ||
        a.e.createdAt.getTime() - b.e.createdAt.getTime()
    );
    const source = weighted[0]?.e;
    if (!source) continue;

    const tName = `${source.name} [${bracketMajor ? simulatorType : "golf/placement"}]`;
    log(`\n  ${tName}: ${events.length} window(s), source=${source.id} (weight ${weighted[0].w})`);

    // Step 2: designate primary for bracket majors only.
    if (bracketMajor && !DRY) {
      for (const { e } of weighted) {
        const shouldBePrimary = e.id === source.id;
        if (e.isPrimary !== shouldBePrimary) {
          await db
            .update(schema.scoringEvents)
            .set({ isPrimary: shouldBePrimary, updatedAt: new Date() })
            .where(eq(schema.scoringEvents.id, e.id));
        }
      }
      log(`    primary → ${source.id}`);
    }

    // Steps 3+4: backfill canonical results and reconcile siblings.
    const isComplete = source.isComplete;
    if (bracketMajor) {
      // Bracket majors go through the exact live path: promote the primary's
      // derived results to canonical (deleting any stale rows) and fan out to
      // siblings, skipping the primary which is scored in place.
      if (!DRY) {
        const report = await syncMajorFromPrimaryEvent(source.id, {
          markComplete: isComplete,
        });
        await updateTournamentStatus(
          tournamentId,
          report.windowsFailed === 0 && isComplete ? "completed" : "in_progress"
        );
        log(
          `    synced via primary: ${report.windowsSynced} ok, ${report.windowsFailed} failed`
        );
      } else {
        log(`    (dry) would sync via primary ${source.id}`);
      }
    } else {
      // Golf/placement: promote the source window's placements to canonical,
      // then fan to every window (no primary to skip).
      const sourceResults = await getEventResults(source.id);
      let promoted = 0;
      for (const r of sourceResults) {
        if (r.placement === null || r.notParticipating) continue;
        const canonical = r.seasonParticipant?.participantId;
        if (!canonical) continue;
        if (!DRY) {
          await upsertTournamentResult({
            tournamentId,
            participantId: canonical,
            placement: r.placement,
            rawScore: r.rawScore,
          });
        }
        promoted += 1;
      }
      log(`    canonical results promoted: ${promoted}`);
      if (!DRY && promoted > 0) {
        const report = await syncTournamentResults(tournamentId, {
          markComplete: isComplete,
        });
        await updateTournamentStatus(
          tournamentId,
          report.windowsFailed === 0 && isComplete ? "completed" : "in_progress"
        );
        log(`    synced: ${report.windowsSynced} ok, ${report.windowsFailed} failed`);
      }
    }

    for (const e of events) touchedSportsSeasons.add(e.sportsSeasonId);
  }

  // ── Recompute majors_completed from completed qualifying events ────────────
  log(`\n[5] Recomputing majors_completed for ${touchedSportsSeasons.size} sports season(s)`);
  for (const sportsSeasonId of touchedSportsSeasons) {
    const completed = await db.query.scoringEvents.findMany({
      where: and(
        eq(schema.scoringEvents.sportsSeasonId, sportsSeasonId),
        eq(schema.scoringEvents.isQualifyingEvent, true),
        eq(schema.scoringEvents.isComplete, true)
      ),
    });
    if (!DRY) {
      await db
        .update(schema.sportsSeasons)
        .set({ majorsCompleted: completed.length, updatedAt: new Date() })
        .where(eq(schema.sportsSeasons.id, sportsSeasonId));
    }
    log(`  • ${sportsSeasonId}: majorsCompleted = ${completed.length}`);
  }

  log(`\nDone${DRY ? " (dry run — no writes)" : ""}.`);
}

async function main() {
  const dbUrl = process.env.DATABASE_URL;
  if (!dbUrl) {
    console.error("ERROR: DATABASE_URL is required");
    process.exit(1);
  }
  const client = postgres(dbUrl, { max: 1 });
  const db = drizzle(client, { schema });
  try {
    await DatabaseContext.run(db, run);
  } finally {
    await client.end();
  }
}

main().catch((e) => {
  console.error(e);
  process.exit(1);
});