import { createRequire } from "node:module";
import { basename, dirname, join } from "node:path";
import type {
  CalendarSnapshot,
  CoachSummary,
  ConferenceSummary,
  GamePlayerStatsSummary,
  GameSummary,
  GameTeamStatsSummary,
  ImportProbeResult,
  PlayerSummary,
  SourcePointer,
  StatPayload,
  TeamSummary,
  UserTeamSummary
} from "../../shared/src";

const require = createRequire(import.meta.url);
const { FranchiseFile } = require("madden-franchise");

const TABLE_UIDS = {
  franchise: 2226370608,
  seasonInfo: 3123991521,
  team: 3359508968,
  conference: 3820706130,
  coach: 1860529246,
  player: 1612938518,
  seasonGame: 4049338978
} as const;

const PLAYER_GAME_STAT_TABLE_UIDS = new Set<number>([
  3937354187, // GameOffensiveStats
  3388716768, // GameOffensiveKPReturnStats
  698792022, // GameDefensiveStats
  300209847, // GameDefensiveKPReturnStats
  2067273102, // GameOLineStats
  589639074 // GameKickingStats
]);

const TEAM_STAT_SOURCE_FIELDS = new Set(["AwayTeamStatCache", "HomeTeamStatCache"]);
const PLAYER_STAT_LINK_FIELDS = new Set(["SeasonGame", "OpposingTeam"]);

type FranchiseLike = {
  gameYear?: number;
  gameType?: string;
  expectedSchemaVersion?: {
    major?: number;
    minor?: number;
    gameYear?: number;
  };
  tables: TableLike[];
  getTableByUniqueId: (id: number) => TableLike | undefined;
  getTableById: (id: number) => TableLike | undefined;
};

type TableLike = {
  name: string;
  header: {
    uniqueId: number;
    tableId: number;
    recordCapacity: number;
  };
  offsetTable: Array<{
    name: string;
    type: string;
    isReference?: boolean;
  }>;
  records: RecordLike[];
  readRecords: (fields?: string[]) => Promise<TableLike>;
};

type RecordLike = {
  index: number;
  isEmpty: boolean;
  arraySize?: number;
  getValueByKey?: (key: string) => unknown;
  getFieldByKey?: (key: string) => unknown;
  [key: string]: unknown;
};

type ParseProbeOptions = {
  sourceHash: string;
  archivePath: string;
  openedAt?: string;
};

type ParsedTeams = {
  teams: TeamSummary[];
  teamsByRow: Map<number, TeamSummary>;
  teamsByTeamIndex: Map<number, TeamSummary>;
  rosterRefsByTeamRow: Map<number, string>;
};

type ParsedPlayers = {
  players: PlayerSummary[];
  playersByRow: Map<number, PlayerSummary>;
  gameStatsRefByPlayerRow: Map<number, string>;
};

type ParsedGames = {
  games: GameSummary[];
  teamStatRefsByGameRow: Map<
    number,
    {
      away: string | null;
      home: string | null;
    }
  >;
};

export async function parseSaveProbe(
  savePath: string,
  options: ParseProbeOptions
): Promise<ImportProbeResult> {
  const diagnostics: string[] = [];
  const franchise = await openSaveReadOnly(savePath);

  const franchiseTable = await readTable(
    franchise,
    TABLE_UIDS.franchise,
    ["LeagueID"],
    diagnostics
  );
  const leagueId = franchiseTable
    ? safeNumber(franchiseTable.records[0], "LeagueID")
    : null;

  const calendar = await parseCalendar(franchise, diagnostics);
  const parsedTeams = await parseTeams(franchise, diagnostics);
  const conferences = await parseConferences(
    franchise,
    parsedTeams.teamsByRow,
    diagnostics
  );
  const coaches = await parseCoaches(
    franchise,
    parsedTeams.teamsByTeamIndex,
    diagnostics
  );
  const userTeam = parseUserTeam(coaches, parsedTeams.teamsByRow, diagnostics);
  const parsedPlayers = await parsePlayers(franchise, parsedTeams, diagnostics);
  const parsedGames = await parseGames(
    franchise,
    parsedTeams.teamsByRow,
    userTeam?.team,
    diagnostics
  );
  const teamGameStats = await parseTeamGameStats(
    franchise,
    parsedGames.games,
    parsedGames.teamStatRefsByGameRow,
    diagnostics
  );
  const playerGameStats = await parsePlayerGameStats(
    franchise,
    parsedPlayers,
    parsedGames.games,
    diagnostics
  );

  const dynastyId =
    leagueId !== null
      ? `league-${leagueId}`
      : calendar?.seasonYear !== null && calendar?.seasonYear !== undefined
        ? `unknown-${calendar.seasonYear}`
        : null;

  return {
    sourceFileName: basename(savePath),
    sourceHash: options.sourceHash,
    archivePath: options.archivePath,
    openedAt: options.openedAt ?? new Date().toISOString(),
    dynastyId,
    schema: {
      gameYear: franchise.expectedSchemaVersion?.gameYear ?? franchise.gameYear ?? null,
      gameType: franchise.gameType ?? null,
      major: franchise.expectedSchemaVersion?.major ?? null,
      minor: franchise.expectedSchemaVersion?.minor ?? null
    },
    tableCount: franchise.tables.length,
    calendar,
    userTeam,
    conferences,
    coaches,
    teams: parsedTeams.teams,
    players: parsedPlayers.players,
    games: parsedGames.games,
    teamGameStats,
    playerGameStats,
    validation: validateLsuAuburn(parsedGames.games, teamGameStats, playerGameStats),
    diagnostics
  };
}

async function openSaveReadOnly(savePath: string): Promise<FranchiseLike> {
  const packageRoot = dirname(require.resolve("madden-franchise/package.json"));
  const schemaDirectory = join(packageRoot, "data", "schemas", "27");
  const schemaPath = join(schemaDirectory, "C27_468_2.gz");

  return await new Promise<FranchiseLike>((resolve, reject) => {
    const file = new FranchiseFile(savePath, {
      autoParse: true,
      saveOnChange: false,
      schemaDirectory,
      schemaOverride: {
        major: 468,
        minor: 2,
        gameYear: 27,
        path: schemaPath
      },
      gameTypeOverride: "college",
      gameYearOverride: 27
    });

    file.on("ready", () => resolve(file));
    file.on("error", (error: Error) => reject(error));
  });
}

async function parseCalendar(
  franchise: FranchiseLike,
  diagnostics: string[]
): Promise<CalendarSnapshot | null> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.seasonInfo,
    [
      "CurrentStage",
      "CurrentWeekType",
      "CurrentWeek",
      "CurrentSeasonYear",
      "CurrentYear",
      "CurrentOffseasonStage",
      "NumberOffseasonAdvances"
    ],
    diagnostics
  );
  const record = table?.records[0];
  if (!record) return null;

  return {
    stage: safeString(record, "CurrentStage"),
    weekType: safeString(record, "CurrentWeekType"),
    week: safeNumber(record, "CurrentWeek"),
    seasonYear: safeNumber(record, "CurrentSeasonYear"),
    dynastyYear: safeNumber(record, "CurrentYear"),
    offseasonStage: safeNumber(record, "CurrentOffseasonStage"),
    offseasonAdvances: safeNumber(record, "NumberOffseasonAdvances")
  };
}

async function parseTeams(
  franchise: FranchiseLike,
  diagnostics: string[]
): Promise<ParsedTeams> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.team,
    [
      "TeamIndex",
      "DisplayName",
      "LongName",
      "ShortName",
      "NickName",
      "TeamRank",
      "MediaPoll_CurrentRank",
      "CoachesPoll_CurrentRank",
      "CFPPoll_CurrentRank",
      "TeamPrestige",
      "PrestigeRank",
      "Roster",
      "Rival1TeamRef",
      "Rival2TeamRef",
      "Rival3TeamRef",
      "TEAM_BACKGROUNDCOLORR",
      "TEAM_BACKGROUNDCOLORG",
      "TEAM_BACKGROUNDCOLORB",
      "TEAM_BACKGROUNDCOLORR2",
      "TEAM_BACKGROUNDCOLORG2",
      "TEAM_BACKGROUNDCOLORB2"
    ],
    diagnostics
  );

  const teams: TeamSummary[] = [];
  const teamsByRow = new Map<number, TeamSummary>();
  const teamsByTeamIndex = new Map<number, TeamSummary>();
  const rosterRefsByTeamRow = new Map<number, string>();
  if (!table) return { teams, teamsByRow, teamsByTeamIndex, rosterRefsByTeamRow };

  for (const record of table.records) {
    if (record.isEmpty) continue;

    const displayName =
      safeString(record, "DisplayName") ??
      safeString(record, "LongName") ??
      safeString(record, "ShortName") ??
      `Team row ${record.index}`;
    const rivalTeamRows = [
      teamRowFromRef(safeString(record, "Rival1TeamRef"), table.header.tableId),
      teamRowFromRef(safeString(record, "Rival2TeamRef"), table.header.tableId),
      teamRowFromRef(safeString(record, "Rival3TeamRef"), table.header.tableId)
    ].filter((row): row is number => row !== null);

    const team: TeamSummary = {
      row: record.index,
      teamIndex: safeNumber(record, "TeamIndex"),
      displayName,
      shortName: safeString(record, "ShortName"),
      nickName: safeString(record, "NickName"),
      conferenceRow: null,
      conferenceName: null,
      rank: zeroAsNull(safeNumber(record, "TeamRank")),
      mediaRank: zeroAsNull(safeNumber(record, "MediaPoll_CurrentRank")),
      coachesRank: zeroAsNull(safeNumber(record, "CoachesPoll_CurrentRank")),
      cfpRank: zeroAsNull(safeNumber(record, "CFPPoll_CurrentRank")),
      prestige: safeNumber(record, "TeamPrestige"),
      prestigeRank: zeroAsNull(safeNumber(record, "PrestigeRank")),
      rivalTeamRows,
      colors: {
        primary: readRgb(record, "", false),
        secondary: readRgb(record, "2", false)
      },
      source: sourcePointer(table, record)
    };

    const rosterRef = safeString(record, "Roster");
    if (rosterRef) rosterRefsByTeamRow.set(team.row, rosterRef);

    teams.push(team);
    teamsByRow.set(team.row, team);
    if (team.teamIndex !== null && !teamsByTeamIndex.has(team.teamIndex)) {
      teamsByTeamIndex.set(team.teamIndex, team);
    }
  }

  return { teams, teamsByRow, teamsByTeamIndex, rosterRefsByTeamRow };
}

async function parseConferences(
  franchise: FranchiseLike,
  teamsByRow: Map<number, TeamSummary>,
  diagnostics: string[]
): Promise<ConferenceSummary[]> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.conference,
    ["Name", "ConferenceEnum", "TeamSlots"],
    diagnostics
  );
  if (!table) return [];

  const teamTableId = franchise.getTableByUniqueId(TABLE_UIDS.team)?.header.tableId;
  const conferences: ConferenceSummary[] = [];

  for (const record of table.records) {
    if (record.isEmpty) continue;

    const enumName = safeString(record, "ConferenceEnum");
    const name =
      safeString(record, "Name") ?? enumName ?? `Conference row ${record.index}`;
    const teamRows = await rowsFromArrayReference(
      franchise,
      safeString(record, "TeamSlots"),
      teamTableId,
      diagnostics
    );

    for (const teamRow of teamRows) {
      const team = teamsByRow.get(teamRow);
      if (!team) continue;
      team.conferenceRow = record.index;
      team.conferenceName = name;
    }

    conferences.push({
      row: record.index,
      name,
      enumName,
      teamRows,
      source: sourcePointer(table, record)
    });
  }

  conferences.sort((a, b) => a.name.localeCompare(b.name) || a.row - b.row);
  return conferences;
}

async function parseCoaches(
  franchise: FranchiseLike,
  teamsByTeamIndex: Map<number, TeamSummary>,
  diagnostics: string[]
): Promise<CoachSummary[]> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.coach,
    [
      "Name",
      "FirstName",
      "LastName",
      "Position",
      "TeamIndex",
      "IsUserControlled",
      "Level",
      "CoachPrestige",
      "CoachPrestigeScore",
      "ContractStatus",
      "CurrentJobSecurityStatus"
    ],
    diagnostics
  );
  if (!table) return [];

  const coaches: CoachSummary[] = [];
  for (const record of table.records) {
    if (record.isEmpty) continue;

    const teamIndex = safeNumber(record, "TeamIndex");
    const teamRow =
      teamIndex !== null ? teamsByTeamIndex.get(teamIndex)?.row ?? null : null;

    coaches.push({
      row: record.index,
      name: safeString(record, "Name"),
      firstName: safeString(record, "FirstName"),
      lastName: safeString(record, "LastName"),
      role: safeString(record, "Position"),
      teamIndex,
      teamRow,
      level: safeNumber(record, "Level"),
      prestige: safeString(record, "CoachPrestige"),
      prestigeScore: safeNumber(record, "CoachPrestigeScore"),
      contractStatus: safeString(record, "ContractStatus"),
      jobSecurity: safeString(record, "CurrentJobSecurityStatus"),
      isUserControlled: safeBoolean(record, "IsUserControlled") === true,
      source: sourcePointer(table, record)
    });
  }

  return coaches.sort((a, b) => a.row - b.row);
}

function parseUserTeam(
  coaches: CoachSummary[],
  teamsByRow: Map<number, TeamSummary>,
  diagnostics: string[]
): UserTeamSummary | null {
  const userCoach = coaches.find((coach) => coach.isUserControlled);
  if (!userCoach) {
    diagnostics.push("No Coach row had IsUserControlled=true.");
    return { team: null, coach: null };
  }

  return {
    coach: userCoach,
    team: userCoach.teamRow !== null ? teamsByRow.get(userCoach.teamRow) ?? null : null
  };
}

async function parsePlayers(
  franchise: FranchiseLike,
  parsedTeams: ParsedTeams,
  diagnostics: string[]
): Promise<ParsedPlayers> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.player,
    [
      "FirstName",
      "LastName",
      "Position",
      "JerseyNum",
      "SchoolYear",
      "OverallRating",
      "TeamIndex",
      "GameStats"
    ],
    diagnostics
  );
  if (!table) {
    return {
      players: [],
      playersByRow: new Map(),
      gameStatsRefByPlayerRow: new Map()
    };
  }

  const rosterTeamByPlayerRow = await parseRosterMembership(
    franchise,
    parsedTeams.rosterRefsByTeamRow,
    table.header.tableId,
    diagnostics
  );
  const players: PlayerSummary[] = [];
  const playersByRow = new Map<number, PlayerSummary>();
  const gameStatsRefByPlayerRow = new Map<number, string>();

  for (const record of table.records) {
    if (record.isEmpty) continue;

    const teamIndex = safeNumber(record, "TeamIndex");
    const rosterTeamRow = rosterTeamByPlayerRow.get(record.index) ?? null;
    const teamRow =
      rosterTeamRow ??
      (teamIndex !== null && teamIndex !== 255
        ? parsedTeams.teamsByTeamIndex.get(teamIndex)?.row ?? null
        : null);

    if (teamRow === null && teamIndex === 255) continue;

    const firstName = safeString(record, "FirstName");
    const lastName = safeString(record, "LastName");
    const displayName =
      [firstName, lastName].filter(Boolean).join(" ") || `Player row ${record.index}`;
    const player: PlayerSummary = {
      row: record.index,
      firstName,
      lastName,
      displayName,
      position: safeString(record, "Position"),
      jerseyNumber: safeNumber(record, "JerseyNum"),
      schoolYear: safeString(record, "SchoolYear"),
      overall: safeNumber(record, "OverallRating"),
      teamIndex,
      teamRow,
      rosterTeamRow,
      source: sourcePointer(table, record)
    };

    const gameStatsRef = safeString(record, "GameStats");
    if (gameStatsRef) gameStatsRefByPlayerRow.set(player.row, gameStatsRef);

    players.push(player);
    playersByRow.set(player.row, player);
  }

  players.sort((a, b) => {
    return (
      nullLast(a.teamRow, b.teamRow) ||
      String(a.position ?? "").localeCompare(String(b.position ?? "")) ||
      a.displayName.localeCompare(b.displayName) ||
      a.row - b.row
    );
  });

  return { players, playersByRow, gameStatsRefByPlayerRow };
}

async function parseRosterMembership(
  franchise: FranchiseLike,
  rosterRefsByTeamRow: Map<number, string>,
  playerTableId: number,
  diagnostics: string[]
): Promise<Map<number, number>> {
  const rosterTeamByPlayerRow = new Map<number, number>();
  const arrayTables = new Map<number, TableLike>();

  for (const [teamRow, rosterRef] of rosterRefsByTeamRow) {
    const decoded = decodeReference(rosterRef);
    if (!decoded) continue;

    let arrayTable = arrayTables.get(decoded.tableId);
    if (!arrayTable) {
      arrayTable = franchise.getTableById(decoded.tableId);
      if (!arrayTable) {
        diagnostics.push(
          `Team row ${teamRow} Roster ref points to missing tableId ${decoded.tableId}.`
        );
        continue;
      }
      await arrayTable.readRecords();
      arrayTables.set(decoded.tableId, arrayTable);
    }

    const arrayRecord = arrayTable.records[decoded.row];
    if (!arrayRecord || arrayRecord.isEmpty) continue;
    for (const field of arrayFields(arrayTable, arrayRecord)) {
      const playerRef = decodeReference(safeString(arrayRecord, field.name));
      if (!playerRef || playerRef.tableId !== playerTableId) continue;
      if (!rosterTeamByPlayerRow.has(playerRef.row)) {
        rosterTeamByPlayerRow.set(playerRef.row, teamRow);
      }
    }
  }

  return rosterTeamByPlayerRow;
}

async function parseGames(
  franchise: FranchiseLike,
  teamsByRow: Map<number, TeamSummary>,
  userTeam: TeamSummary | null | undefined,
  diagnostics: string[]
): Promise<ParsedGames> {
  const table = await readTable(
    franchise,
    TABLE_UIDS.seasonGame,
    [
      "SeasonYear",
      "SeasonWeek",
      "SeasonWeekType",
      "SeasonGameNum",
      "GameStatus",
      "HomeTeam",
      "AwayTeam",
      "HomeTeamStatCache",
      "AwayTeamStatCache",
      "HomeScore",
      "AwayScore",
      "HomeScoreQuarter1",
      "HomeScoreQuarter2",
      "HomeScoreQuarter3",
      "HomeScoreQuarter4",
      "AwayScoreQuarter1",
      "AwayScoreQuarter2",
      "AwayScoreQuarter3",
      "AwayScoreQuarter4",
      "HomeScoreOT",
      "AwayScoreOT",
      "IsSimmed",
      "IsGameOfTheWeek",
      "Attendance",
      "Weather"
    ],
    diagnostics
  );
  if (!table) return { games: [], teamStatRefsByGameRow: new Map() };

  const teamTableId = franchise.getTableByUniqueId(TABLE_UIDS.team)?.header.tableId;
  const allGames: GameSummary[] = [];
  const teamStatRefsByGameRow = new Map<
    number,
    {
      away: string | null;
      home: string | null;
    }
  >();

  for (const record of table.records) {
    if (record.isEmpty) continue;

    const homeTeam = teamFromRef(
      safeString(record, "HomeTeam"),
      teamTableId,
      teamsByRow
    );
    const awayTeam = teamFromRef(
      safeString(record, "AwayTeam"),
      teamTableId,
      teamsByRow
    );

    if (!homeTeam && !awayTeam) continue;

    const majorGameReasons = majorReasons(homeTeam, awayTeam, record);
    allGames.push({
      row: record.index,
      seasonYear: safeNumber(record, "SeasonYear"),
      week: safeNumber(record, "SeasonWeek"),
      weekType: safeString(record, "SeasonWeekType"),
      gameNumber: safeNumber(record, "SeasonGameNum"),
      status: safeString(record, "GameStatus"),
      homeTeam,
      awayTeam,
      homeScore: safeNumber(record, "HomeScore"),
      awayScore: safeNumber(record, "AwayScore"),
      quarterScores: {
        home: [
          safeNumber(record, "HomeScoreQuarter1"),
          safeNumber(record, "HomeScoreQuarter2"),
          safeNumber(record, "HomeScoreQuarter3"),
          safeNumber(record, "HomeScoreQuarter4")
        ],
        away: [
          safeNumber(record, "AwayScoreQuarter1"),
          safeNumber(record, "AwayScoreQuarter2"),
          safeNumber(record, "AwayScoreQuarter3"),
          safeNumber(record, "AwayScoreQuarter4")
        ],
        homeOvertime: safeNumber(record, "HomeScoreOT"),
        awayOvertime: safeNumber(record, "AwayScoreOT")
      },
      isSimmed: safeBoolean(record, "IsSimmed"),
      isGameOfTheWeek: safeBoolean(record, "IsGameOfTheWeek"),
      isMajorGame: majorGameReasons.length > 0,
      majorGameReasons,
      attendance: safeNumber(record, "Attendance"),
      weather: safeString(record, "Weather"),
      source: sourcePointer(table, record)
    });

    teamStatRefsByGameRow.set(record.index, {
      away: safeString(record, "AwayTeamStatCache"),
      home: safeString(record, "HomeTeamStatCache")
    });
  }

  allGames.sort((a, b) => {
    const userGameSort =
      userTeam && isUserGame(a, userTeam) !== isUserGame(b, userTeam)
        ? isUserGame(a, userTeam)
          ? -1
          : 1
        : 0;

    return (
      nullLast(a.seasonYear, b.seasonYear) ||
      nullLast(a.week, b.week) ||
      String(a.weekType ?? "").localeCompare(String(b.weekType ?? "")) ||
      nullLast(a.gameNumber, b.gameNumber) ||
      userGameSort ||
      a.row - b.row
    );
  });

  return { games: allGames, teamStatRefsByGameRow };
}

async function parseTeamGameStats(
  franchise: FranchiseLike,
  games: GameSummary[],
  refsByGameRow: Map<number, { away: string | null; home: string | null }>,
  diagnostics: string[]
): Promise<GameTeamStatsSummary[]> {
  const tableCache = new Map<number, TableLike>();
  const stats: GameTeamStatsSummary[] = [];

  for (const game of games) {
    const refs = refsByGameRow.get(game.row);
    if (!refs) continue;

    for (const side of ["away", "home"] as const) {
      const decoded = decodeReference(refs[side]);
      if (!decoded) continue;

      let table = tableCache.get(decoded.tableId);
      if (!table) {
        table = franchise.getTableById(decoded.tableId);
        if (!table) {
          diagnostics.push(
            `SeasonGame row ${game.row} ${side} team stats point to missing tableId ${decoded.tableId}.`
          );
          continue;
        }
        await table.readRecords();
        tableCache.set(decoded.tableId, table);
      }

      const record = table.records[decoded.row];
      if (!record || record.isEmpty) continue;

      stats.push({
        gameRow: game.row,
        teamRow: side === "home" ? game.homeTeam?.row ?? null : game.awayTeam?.row ?? null,
        side,
        stats: statPayload(table, record, {
          includeZero: true,
          skipFields: TEAM_STAT_SOURCE_FIELDS
        }),
        source: sourcePointer(table, record)
      });
    }
  }

  return stats;
}

async function parsePlayerGameStats(
  franchise: FranchiseLike,
  parsedPlayers: ParsedPlayers,
  games: GameSummary[],
  diagnostics: string[]
): Promise<GamePlayerStatsSummary[]> {
  const seasonGameTableId =
    franchise.getTableByUniqueId(TABLE_UIDS.seasonGame)?.header.tableId;
  const teamTableId = franchise.getTableByUniqueId(TABLE_UIDS.team)?.header.tableId;
  const knownGameRows = new Set(games.map((game) => game.row));
  const arrayTableCache = new Map<number, TableLike>();
  const statTableCache = new Map<number, TableLike>();
  const seen = new Set<string>();
  const output: GamePlayerStatsSummary[] = [];

  for (const player of parsedPlayers.players) {
    const gameStatsRef = parsedPlayers.gameStatsRefByPlayerRow.get(player.row);
    const decodedGameStats = decodeReference(gameStatsRef ?? null);
    if (!decodedGameStats) continue;

    let arrayTable = arrayTableCache.get(decodedGameStats.tableId);
    if (!arrayTable) {
      arrayTable = franchise.getTableById(decodedGameStats.tableId);
      if (!arrayTable) {
        diagnostics.push(
          `Player row ${player.row} GameStats ref points to missing tableId ${decodedGameStats.tableId}.`
        );
        continue;
      }
      await arrayTable.readRecords();
      arrayTableCache.set(decodedGameStats.tableId, arrayTable);
    }

    const arrayRecord = arrayTable.records[decodedGameStats.row];
    if (!arrayRecord || arrayRecord.isEmpty) continue;

    for (const field of arrayFields(arrayTable, arrayRecord)) {
      const statRef = decodeReference(safeString(arrayRecord, field.name));
      if (!statRef) continue;

      let statTable = statTableCache.get(statRef.tableId);
      if (!statTable) {
        statTable = franchise.getTableById(statRef.tableId);
        if (!statTable || !PLAYER_GAME_STAT_TABLE_UIDS.has(statTable.header.uniqueId)) {
          continue;
        }
        await statTable.readRecords();
        statTableCache.set(statRef.tableId, statTable);
      }

      if (!PLAYER_GAME_STAT_TABLE_UIDS.has(statTable.header.uniqueId)) continue;
      const statRecord = statTable.records[statRef.row];
      if (!statRecord || statRecord.isEmpty) continue;

      const gameRef = decodeReference(safeString(statRecord, "SeasonGame"));
      if (
        !gameRef ||
        gameRef.tableId !== seasonGameTableId ||
        !knownGameRows.has(gameRef.row)
      ) {
        continue;
      }

      const key = `${player.row}:${statTable.header.uniqueId}:${statRef.row}`;
      if (seen.has(key)) continue;
      seen.add(key);

      const opponentRef = decodeReference(safeString(statRecord, "OpposingTeam"));
      output.push({
        gameRow: gameRef.row,
        playerRow: player.row,
        playerName: player.displayName,
        teamRow: player.teamRow,
        opponentTeamRow:
          opponentRef && opponentRef.tableId === teamTableId ? opponentRef.row : null,
        statGroup: statGroup(statTable.name),
        stats: statPayload(statTable, statRecord, {
          includeZero: false,
          skipFields: PLAYER_STAT_LINK_FIELDS
        }),
        source: sourcePointer(statTable, statRecord)
      });
    }
  }

  output.sort((a, b) => {
    return (
      a.gameRow - b.gameRow ||
      nullLast(a.teamRow, b.teamRow) ||
      a.playerName.localeCompare(b.playerName) ||
      a.statGroup.localeCompare(b.statGroup) ||
      a.source.row - b.source.row
    );
  });
  return output;
}

async function readTable(
  franchise: FranchiseLike,
  uniqueId: number,
  fields: string[],
  diagnostics: string[]
): Promise<TableLike | null> {
  const table = franchise.getTableByUniqueId(uniqueId);
  if (!table) {
    diagnostics.push(`Missing table with uniqueId ${uniqueId}.`);
    return null;
  }

  await table.readRecords(fields);
  return table;
}

async function rowsFromArrayReference(
  franchise: FranchiseLike,
  reference: string | null,
  expectedTableId: number | undefined,
  diagnostics: string[]
): Promise<number[]> {
  const decoded = decodeReference(reference);
  if (!decoded) return [];

  const table = franchise.getTableById(decoded.tableId);
  if (!table) {
    diagnostics.push(`Array reference points to missing tableId ${decoded.tableId}.`);
    return [];
  }

  await table.readRecords();
  const record = table.records[decoded.row];
  if (!record || record.isEmpty) return [];

  const rows: number[] = [];
  for (const field of arrayFields(table, record)) {
    const row = teamRowFromRef(safeString(record, field.name), expectedTableId);
    if (row !== null) rows.push(row);
  }
  return [...new Set(rows)];
}

function sourcePointer(table: TableLike, record: RecordLike): SourcePointer {
  return {
    table: table.name,
    uniqueId: table.header.uniqueId,
    tableId: table.header.tableId,
    row: record.index
  };
}

function teamFromRef(
  reference: string | null,
  expectedTableId: number | undefined,
  teamsByRow: Map<number, TeamSummary>
): TeamSummary | null {
  const row = teamRowFromRef(reference, expectedTableId);
  if (row === null) return null;
  return teamsByRow.get(row) ?? null;
}

function teamRowFromRef(
  reference: string | null,
  expectedTableId: number | undefined
): number | null {
  const decoded = decodeReference(reference);
  if (!decoded) return null;
  if (expectedTableId !== undefined && decoded.tableId !== expectedTableId) return null;
  return decoded.row;
}

function decodeReference(
  value: string | null
): { tableId: number; row: number } | null {
  if (!value || value.length !== 32 || !/^[01]{32}$/.test(value)) return null;
  if (!value.includes("1")) return null;
  return {
    tableId: Number.parseInt(value.slice(0, 15), 2),
    row: Number.parseInt(value.slice(15), 2)
  };
}

function arrayFields(table: TableLike, record: RecordLike): TableLike["offsetTable"] {
  const size =
    typeof record.arraySize === "number" && record.arraySize > 0
      ? record.arraySize
      : table.offsetTable.length;
  return table.offsetTable.slice(0, size);
}

function safeValue(record: RecordLike, key: string): unknown {
  try {
    if (typeof record.getValueByKey === "function") {
      const methodValue = record.getValueByKey(key);
      if (methodValue !== null && methodValue !== undefined) return methodValue;
    }
    return record[key];
  } catch {
    return null;
  }
}

function safeString(record: RecordLike, key: string): string | null {
  const value = safeValue(record, key);
  if (typeof value !== "string") return null;
  const trimmed = value.trim();
  return trimmed.length > 0 ? trimmed : null;
}

function safeNumber(record: RecordLike, key: string): number | null {
  const value = safeValue(record, key);
  if (typeof value === "number" && Number.isFinite(value)) return value;
  if (typeof value === "string" && value.trim() !== "") {
    const parsed = Number(value);
    return Number.isFinite(parsed) ? parsed : null;
  }
  return null;
}

function safeBoolean(record: RecordLike, key: string): boolean | null {
  const value = safeValue(record, key);
  return typeof value === "boolean" ? value : null;
}

function statPayload(
  table: TableLike,
  record: RecordLike,
  options: {
    includeZero: boolean;
    skipFields: Set<string>;
  }
): StatPayload {
  const payload: StatPayload = {};
  for (const field of table.offsetTable) {
    if (options.skipFields.has(field.name) || field.isReference) continue;
    const value = safeValue(record, field.name);
    if (!isJsonScalar(value)) continue;
    if (!options.includeZero && isEmptyStatValue(value)) continue;
    payload[field.name] = value;
  }
  return payload;
}

function isJsonScalar(
  value: unknown
): value is string | number | boolean | null {
  return (
    value === null ||
    typeof value === "string" ||
    typeof value === "number" ||
    typeof value === "boolean"
  );
}

function isEmptyStatValue(value: string | number | boolean | null): boolean {
  return (
    value === null ||
    value === 0 ||
    value === false ||
    value === "" ||
    value === "Invalid" ||
    value === "Invalid_"
  );
}

function majorReasons(
  homeTeam: TeamSummary | null,
  awayTeam: TeamSummary | null,
  record: RecordLike
): string[] {
  const reasons: string[] = [];
  const homeRank = bestRank(homeTeam);
  const awayRank = bestRank(awayTeam);

  if (
    homeRank !== null &&
    awayRank !== null &&
    homeRank <= 25 &&
    awayRank <= 25
  ) {
    reasons.push("ranked_matchup");
  }

  if (homeTeam && awayTeam && isRivalry(homeTeam, awayTeam)) {
    reasons.push("rivalry");
  }

  if (safeBoolean(record, "IsGameOfTheWeek") === true) {
    reasons.push("game_of_the_week");
  }

  return reasons;
}

function bestRank(team: TeamSummary | null): number | null {
  if (!team) return null;
  const ranks = [team.rank, team.mediaRank, team.coachesRank, team.cfpRank].filter(
    (rank): rank is number => rank !== null
  );
  return ranks.length ? Math.min(...ranks) : null;
}

function isRivalry(homeTeam: TeamSummary, awayTeam: TeamSummary): boolean {
  return (
    homeTeam.rivalTeamRows.includes(awayTeam.row) ||
    awayTeam.rivalTeamRows.includes(homeTeam.row)
  );
}

function isUserGame(game: GameSummary, userTeam: TeamSummary): boolean {
  return game.homeTeam?.row === userTeam.row || game.awayTeam?.row === userTeam.row;
}

function validateLsuAuburn(
  games: GameSummary[],
  teamGameStats: GameTeamStatsSummary[],
  playerGameStats: GamePlayerStatsSummary[]
): ImportProbeResult["validation"] {
  const game =
    games.find(
      (candidate) =>
        normalizeName(candidate.awayTeam?.displayName) === "auburn" &&
        normalizeName(candidate.homeTeam?.displayName) === "lsu"
    ) ?? null;

  if (!game) {
    return {
      lsuAuburn: {
        found: false,
        gameRow: null,
        scoreMatched: false,
        houstonAndersonMatched: false,
        julianReeseMatched: false,
        notes: ["Auburn at LSU was not found in SeasonGame rows."]
      }
    };
  }

  const scoreMatched = game.awayScore === 0 && game.homeScore === 66;
  const houstonAndersonMatched = playerGameStats.some(
    (stat) =>
      stat.gameRow === game.row &&
      normalizeName(stat.playerName) === "houston anderson" &&
      stat.stats.PASSYARDS === 508 &&
      stat.stats.PASSTDS === 7
  );
  const julianReeseMatched = playerGameStats.some(
    (stat) =>
      stat.gameRow === game.row &&
      normalizeName(stat.playerName) === "julian reese ii" &&
      stat.stats.RECEIVECATCHES === 9 &&
      stat.stats.RECEIVEYARDS === 283 &&
      stat.stats.RECEIVETDS === 4
  );
  const lsuTeamStats = teamGameStats.find(
    (stat) => stat.gameRow === game.row && stat.teamRow === game.homeTeam?.row
  );
  const notes = [
    `SeasonGame row ${game.row}: ${game.awayTeam?.displayName ?? "Away"} ${game.awayScore ?? "?"}, ${game.homeTeam?.displayName ?? "Home"} ${game.homeScore ?? "?"}.`
  ];

  if (lsuTeamStats) {
    notes.push(
      `LSU team stats: OFFPASSYARDS=${lsuTeamStats.stats.OFFPASSYARDS ?? 0}, OFFRUSHYARDS=${lsuTeamStats.stats.OFFRUSHYARDS ?? 0}, OFFYARDS=${lsuTeamStats.stats.OFFYARDS ?? 0}, TOTALYARDS=${lsuTeamStats.stats.TOTALYARDS ?? 0}, PASSTDS=${lsuTeamStats.stats.PASSTDS ?? 0}.`
    );
  }

  return {
    lsuAuburn: {
      found: true,
      gameRow: game.row,
      scoreMatched,
      houstonAndersonMatched,
      julianReeseMatched,
      notes
    }
  };
}

function statGroup(tableName: string): string {
  switch (tableName) {
    case "GameOffensiveStats":
      return "offense";
    case "GameOffensiveKPReturnStats":
      return "offense_return";
    case "GameDefensiveStats":
      return "defense";
    case "GameDefensiveKPReturnStats":
      return "defense_return";
    case "GameOLineStats":
      return "offensive_line";
    case "GameKickingStats":
      return "kicking_punting";
    default:
      return tableName;
  }
}

function normalizeName(value: string | null | undefined): string {
  return String(value ?? "")
    .trim()
    .toLowerCase();
}

function zeroAsNull(value: number | null): number | null {
  return value === 0 ? null : value;
}

function readRgb(record: RecordLike, suffix: string, requireAll: boolean): string | null {
  const r = safeNumber(record, `TEAM_BACKGROUNDCOLORR${suffix}`);
  const g = safeNumber(record, `TEAM_BACKGROUNDCOLORG${suffix}`);
  const b = safeNumber(record, `TEAM_BACKGROUNDCOLORB${suffix}`);
  if (r === null || g === null || b === null) return null;
  if (requireAll && r === 0 && g === 0 && b === 0) return null;
  return `#${toHex(r)}${toHex(g)}${toHex(b)}`;
}

function toHex(value: number): string {
  return Math.max(0, Math.min(255, value)).toString(16).padStart(2, "0");
}

function nullLast(a: number | null, b: number | null): number {
  if (a === null && b === null) return 0;
  if (a === null) return 1;
  if (b === null) return -1;
  return a - b;
}