/**
 * Roster Reconciler — ESPN is the source of truth.
 *
 * Pulls ESPN rosters for major leagues, fuzzy-matches against SGO player data,
 * and corrects names/teams/missing players. Every correction is logged.
 *
 * Usage:
 *   npx tsx src/workers/roster-reconciler.ts                    # all US leagues
 *   npx tsx src/workers/roster-reconciler.ts --league nba       # single league
 *   npx tsx src/workers/roster-reconciler.ts --all              # US + soccer
 *   npx tsx src/workers/roster-reconciler.ts --dry-run          # preview only
 *
 * Cron: 3:00 AM ET daily (before seed-all-sports at 5:30 AM)
 */

import 'dotenv/config';
import pool from '../db';
import fs from 'fs';
import path from 'path';
import {
  ESPN_LEAGUES,
  fetchLeagueRoster,
  fetchLeagueInjuries,
  type ESPNPlayer,
  type ESPNLeagueRoster,
  type ESPNInjury,
  type ESPNLeagueInjuries,
} from '../services/espn-roster';
import {
  normalizeName,
  stripSuffix,
  buildLookupMaps,
  matchPlayer,
  type MatchResult,
  type LookupMaps,
} from '../services/player-normalizer';

// ---------------------------------------------------------------------------
// Config
// ---------------------------------------------------------------------------

const PLAYERS_JSON = '/home/administrator/rainmaker_players.json';
const LOG_DIR = path.join(__dirname, '../../logs');

const US_LEAGUES = ['nba', 'ncaab', 'nfl', 'mlb', 'nhl', 'wnba'];
const SOCCER_LEAGUES = ['epl', 'la_liga', 'bundesliga', 'serie_a', 'ligue_1'];

// Parse CLI args
const args = process.argv.slice(2);
const DRY_RUN = args.includes('--dry-run');
const SINGLE_LEAGUE = args.find((a, i) => args[i - 1] === '--league') ?? null;
const ALL_LEAGUES = args.includes('--all');

function getLeaguesToProcess(): string[] {
  if (SINGLE_LEAGUE) return [SINGLE_LEAGUE];
  if (ALL_LEAGUES) return [...US_LEAGUES, ...SOCCER_LEAGUES];
  return US_LEAGUES;
}

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

interface Correction {
  league: string;
  correction_type: 'name_mismatch' | 'team_mismatch' | 'missing_from_sgo' | 'cut_from_roster';
  player_name_sgo: string | null;
  player_name_espn: string | null;
  player_id_sgo: string | null;
  player_id_espn: string | null;
  team_sgo: string | null;
  team_espn: string | null;
  match_tier: number | null;
  match_confidence: number | null;
  details: Record<string, any>;
}

interface LeagueStats {
  league: string;
  espnPlayers: number;
  sgoPlayers: number;
  matched: number;
  nameCorrections: number;
  teamCorrections: number;
  missing: number;
  cut: number;
}

// ---------------------------------------------------------------------------
// SGO JSON helpers
// ---------------------------------------------------------------------------

interface SgoPlayerJson {
  playerID: string;
  name: string;
  firstName?: string;
  lastName?: string;
  shortName?: string;
  teamID: string;
  teamName: string;
  position?: string;
  number?: string;
}

interface SgoLeagueJson {
  leagueID: string;
  sportID: string;
  type: string;
  teamCount: number;
  playerCount: number;
  teams: any[];
  players: SgoPlayerJson[];
}

function loadSgoData(): Record<string, SgoLeagueJson> {
  const raw = JSON.parse(fs.readFileSync(PLAYERS_JSON, 'utf8'));
  return raw.leagues;
}

function saveSgoData(leagues: Record<string, SgoLeagueJson>): void {
  const raw = JSON.parse(fs.readFileSync(PLAYERS_JSON, 'utf8'));
  raw.leagues = leagues;
  raw.lastReconciled = new Date().toISOString();
  const tmpPath = PLAYERS_JSON + '.tmp';
  fs.writeFileSync(tmpPath, JSON.stringify(raw, null, 2));
  fs.renameSync(tmpPath, PLAYERS_JSON);
}

// ---------------------------------------------------------------------------
// Team matching bridge
// ---------------------------------------------------------------------------

/**
 * Build a bidirectional mapping between ESPN team abbreviations and SGO teamIDs.
 * Uses normalized team names as the bridge when abbreviations don't match.
 */
function buildTeamBridge(
  espnRoster: ESPNLeagueRoster,
  sgoLeague: SgoLeagueJson,
): { espnToSgoTeam: Map<string, string>; sgoToEspnTeam: Map<string, string> } {
  const espnToSgoTeam = new Map<string, string>();
  const sgoToEspnTeam = new Map<string, string>();

  // Build normalized name → SGO teamID map
  const sgoTeamByNorm = new Map<string, { teamID: string; teamName: string }>();
  for (const t of sgoLeague.teams) {
    const norm = normalizeName(t.fullName || t.teamID);
    sgoTeamByNorm.set(norm, { teamID: t.teamID, teamName: t.fullName || t.teamID });
    // Also index by short/medium names
    if (t.shortName) sgoTeamByNorm.set(normalizeName(t.shortName), { teamID: t.teamID, teamName: t.fullName || t.teamID });
    if (t.mediumName) sgoTeamByNorm.set(normalizeName(t.mediumName), { teamID: t.teamID, teamName: t.fullName || t.teamID });
  }

  // Also index SGO players by team for fallback matching
  const sgoTeamsByPlayerTeamName = new Map<string, string>();
  for (const p of sgoLeague.players || []) {
    if (p.teamName && p.teamID) {
      sgoTeamsByPlayerTeamName.set(normalizeName(p.teamName), p.teamID);
    }
  }

  for (const espnTeam of espnRoster.teams) {
    const espnNorm = normalizeName(espnTeam.name);
    const espnShort = normalizeName(espnTeam.short);

    // Try exact name match
    let match = sgoTeamByNorm.get(espnNorm);
    // Try short name match
    if (!match) match = sgoTeamByNorm.get(espnShort);
    // Try partial: see if any SGO team name contains ESPN team name or vice versa
    if (!match) {
      for (const [sgoNorm, sgoTeam] of sgoTeamByNorm.entries()) {
        if (sgoNorm.includes(espnNorm) || espnNorm.includes(sgoNorm)) {
          match = sgoTeam;
          break;
        }
      }
    }
    // Try player team name fallback
    if (!match) {
      const fallback = sgoTeamsByPlayerTeamName.get(espnNorm);
      if (fallback) match = { teamID: fallback, teamName: espnTeam.name };
    }

    if (match) {
      espnToSgoTeam.set(espnTeam.short, match.teamID);
      sgoToEspnTeam.set(match.teamID, espnTeam.short);
    }
  }

  return { espnToSgoTeam, sgoToEspnTeam };
}

// ---------------------------------------------------------------------------
// Core reconciliation for one league
// ---------------------------------------------------------------------------

function reconcileLeague(
  leagueKey: string,
  espnRoster: ESPNLeagueRoster,
  sgoLeague: SgoLeagueJson,
): { corrections: Correction[]; stats: LeagueStats } {
  const corrections: Correction[] = [];
  const sgoPlayers = sgoLeague.players || [];
  const espnPlayers = espnRoster.players;

  const { espnToSgoTeam, sgoToEspnTeam } = buildTeamBridge(espnRoster, sgoLeague);

  console.log(`[RECON] ${leagueKey.toUpperCase()}: Team bridge mapped ${espnToSgoTeam.size}/${espnRoster.teams.length} ESPN teams to SGO`);

  // Group SGO players by SGO teamID for per-team matching
  const sgoByTeam = new Map<string, SgoPlayerJson[]>();
  for (const p of sgoPlayers) {
    const list = sgoByTeam.get(p.teamID) || [];
    list.push(p);
    sgoByTeam.set(p.teamID, list);
  }

  // Build global lookup maps for league-wide fallback matching
  const globalMaps = buildLookupMaps(sgoPlayers.map(p => ({ playerID: p.playerID, name: p.name })));

  // Track which SGO players got matched
  const matchedSgoIDs = new Set<string>();

  let nameCorrections = 0;
  let teamCorrections = 0;
  let missing = 0;

  // For each ESPN player, try to find their SGO match
  for (const espnPlayer of espnPlayers) {
    const sgoTeamID = espnToSgoTeam.get(espnPlayer.teamShort);

    let match: MatchResult | null = null;

    // Try team-scoped match first (more accurate)
    if (sgoTeamID) {
      const teamPlayers = sgoByTeam.get(sgoTeamID) || [];
      const teamMaps = buildLookupMaps(teamPlayers.map(p => ({ playerID: p.playerID, name: p.name })));
      match = matchPlayer(espnPlayer.name, teamPlayers.map(p => ({ playerID: p.playerID, name: p.name })), teamMaps);
    }

    // Fallback: league-wide match (catches traded players)
    if (!match) {
      match = matchPlayer(
        espnPlayer.name,
        sgoPlayers.map(p => ({ playerID: p.playerID, name: p.name })),
        globalMaps,
      );
    }

    if (match) {
      matchedSgoIDs.add(match.sgoPlayerID);

      // Check for name mismatch
      const espnNorm = normalizeName(espnPlayer.name);
      const sgoNorm = normalizeName(match.sgoName);
      if (espnNorm !== sgoNorm) {
        nameCorrections++;
        corrections.push({
          league: leagueKey,
          correction_type: 'name_mismatch',
          player_name_sgo: match.sgoName,
          player_name_espn: espnPlayer.name,
          player_id_sgo: match.sgoPlayerID,
          player_id_espn: espnPlayer.espnId,
          team_sgo: null,
          team_espn: espnPlayer.teamShort,
          match_tier: match.tier,
          match_confidence: match.confidence,
          details: { reason: `Tier ${match.tier} match: SGO "${match.sgoName}" → ESPN "${espnPlayer.name}"` },
        });
      }

      // Check for team mismatch
      const matchedSgoPlayer = sgoPlayers.find(p => p.playerID === match!.sgoPlayerID);
      if (matchedSgoPlayer && sgoTeamID && matchedSgoPlayer.teamID !== sgoTeamID) {
        teamCorrections++;
        corrections.push({
          league: leagueKey,
          correction_type: 'team_mismatch',
          player_name_sgo: match.sgoName,
          player_name_espn: espnPlayer.name,
          player_id_sgo: match.sgoPlayerID,
          player_id_espn: espnPlayer.espnId,
          team_sgo: matchedSgoPlayer.teamName,
          team_espn: `${espnPlayer.teamName} (${espnPlayer.teamShort})`,
          match_tier: match.tier,
          match_confidence: match.confidence,
          details: { reason: `Player moved: SGO has on "${matchedSgoPlayer.teamName}" but ESPN has on "${espnPlayer.teamName}"` },
        });
      }
    } else {
      // Missing from SGO entirely
      missing++;
      corrections.push({
        league: leagueKey,
        correction_type: 'missing_from_sgo',
        player_name_sgo: null,
        player_name_espn: espnPlayer.name,
        player_id_sgo: null,
        player_id_espn: espnPlayer.espnId,
        team_sgo: null,
        team_espn: `${espnPlayer.teamName} (${espnPlayer.teamShort})`,
        match_tier: null,
        match_confidence: null,
        details: { position: espnPlayer.position },
      });
    }
  }

  // Find SGO players on real ESPN teams who weren't matched (potentially cut)
  let cut = 0;
  const espnTeamShorts = new Set(espnRoster.teams.map(t => t.short));
  for (const sgoPlayer of sgoPlayers) {
    if (matchedSgoIDs.has(sgoPlayer.playerID)) continue;
    const espnShort = sgoToEspnTeam.get(sgoPlayer.teamID);
    if (espnShort && espnTeamShorts.has(espnShort)) {
      cut++;
      corrections.push({
        league: leagueKey,
        correction_type: 'cut_from_roster',
        player_name_sgo: sgoPlayer.name,
        player_name_espn: null,
        player_id_sgo: sgoPlayer.playerID,
        player_id_espn: null,
        team_sgo: sgoPlayer.teamName,
        team_espn: espnShort,
        match_tier: null,
        match_confidence: null,
        details: { reason: `In SGO but not on ESPN ${espnShort} roster` },
      });
    }
  }

  const stats: LeagueStats = {
    league: leagueKey,
    espnPlayers: espnPlayers.length,
    sgoPlayers: sgoPlayers.length,
    matched: matchedSgoIDs.size,
    nameCorrections,
    teamCorrections,
    missing,
    cut,
  };

  return { corrections, stats };
}

// ---------------------------------------------------------------------------
// Apply corrections to the JSON + database
// ---------------------------------------------------------------------------

async function applyCorrections(
  corrections: Correction[],
  sgoData: Record<string, SgoLeagueJson>,
): Promise<number> {
  let applied = 0;

  // Group corrections by league for efficient processing
  const byLeague = new Map<string, Correction[]>();
  for (const c of corrections) {
    const list = byLeague.get(c.league) || [];
    list.push(c);
    byLeague.set(c.league, list);
  }

  for (const [leagueKey, leagueCorrections] of byLeague.entries()) {
    const league = sgoData[leagueKey];
    if (!league || !league.players) continue;

    // Build playerID index for fast lookup
    const playerIdx = new Map<string, number>();
    for (let i = 0; i < league.players.length; i++) {
      playerIdx.set(league.players[i].playerID, i);
    }

    for (const c of leagueCorrections) {
      if (c.correction_type === 'name_mismatch' && c.player_id_sgo && c.player_name_espn) {
        const idx = playerIdx.get(c.player_id_sgo);
        if (idx !== undefined) {
          league.players[idx].name = c.player_name_espn;
          applied++;
        }
      }

      if (c.correction_type === 'team_mismatch' && c.player_id_sgo && c.team_espn) {
        // Team corrections are more complex — just log for now, don't move players between teams
        // as this could break SGO teamID linkage
      }

      if (c.correction_type === 'missing_from_sgo' && c.player_name_espn && c.player_id_espn) {
        // Add ESPN player to the SGO list with a synthetic entry
        league.players.push({
          playerID: `ESPN_${c.player_id_espn}_${leagueKey.toUpperCase()}`,
          name: c.player_name_espn,
          teamID: '',
          teamName: c.team_espn || '',
          position: c.details?.position || '',
        });
        league.playerCount = league.players.length;
        applied++;
      }
    }
  }

  // Update rm_forecast_precomputed player names for name mismatches
  const nameFixBatch: { oldName: string; newName: string; league: string }[] = [];
  for (const c of corrections) {
    if (c.correction_type === 'name_mismatch' && c.player_name_sgo && c.player_name_espn) {
      if (normalizeName(c.player_name_sgo) !== normalizeName(c.player_name_espn)) {
        nameFixBatch.push({
          oldName: c.player_name_sgo,
          newName: c.player_name_espn,
          league: c.league,
        });
      }
    }
  }

  if (nameFixBatch.length > 0) {
    console.log(`[RECON] Updating ${nameFixBatch.length} player names in rm_forecast_precomputed...`);
    for (const fix of nameFixBatch) {
      try {
        const res = await pool.query(
          `UPDATE rm_forecast_precomputed SET player_name = $1 WHERE player_name = $2 AND league = $3 AND forecast_type = 'PLAYER_PROP'`,
          [fix.newName, fix.oldName, fix.league],
        );
        if (res.rowCount && res.rowCount > 0) {
          console.log(`  Fixed: "${fix.oldName}" → "${fix.newName}" (${fix.league}, ${res.rowCount} rows)`);
        }
      } catch (err: any) {
        console.log(`  WARN: Failed to update "${fix.oldName}": ${err.message}`);
      }
    }
  }

  return applied;
}

// ---------------------------------------------------------------------------
// Database logging
// ---------------------------------------------------------------------------

async function createRun(leagues: string[]): Promise<string> {
  const res = await pool.query(
    `INSERT INTO rm_roster_reconciliation_runs (status, leagues_processed) VALUES ('RUNNING', $1) RETURNING id`,
    [leagues],
  );
  return res.rows[0].id;
}

async function completeRun(
  runId: string,
  allStats: LeagueStats[],
  totalApplied: number,
  startTime: number,
  error?: string,
): Promise<void> {
  const duration = Date.now() - startTime;
  const totals = allStats.reduce(
    (acc, s) => ({
      espn: acc.espn + s.espnPlayers,
      sgo: acc.sgo + s.sgoPlayers,
      matched: acc.matched + s.matched,
      names: acc.names + s.nameCorrections,
      teams: acc.teams + s.teamCorrections,
      missing: acc.missing + s.missing,
      cut: acc.cut + s.cut,
    }),
    { espn: 0, sgo: 0, matched: 0, names: 0, teams: 0, missing: 0, cut: 0 },
  );

  await pool.query(
    `UPDATE rm_roster_reconciliation_runs SET
      status = $1, completed_at = NOW(), duration_ms = $2,
      total_espn_players = $3, total_sgo_players = $4, total_matched = $5,
      total_name_corrections = $6, total_team_corrections = $7,
      total_missing = $8, total_cut = $9, total_applied = $10,
      error_message = $11
    WHERE id = $12`,
    [
      error ? 'FAILED' : 'COMPLETED', duration,
      totals.espn, totals.sgo, totals.matched,
      totals.names, totals.teams, totals.missing, totals.cut,
      totalApplied, error || null, runId,
    ],
  );
}

async function logCorrections(runId: string, corrections: Correction[]): Promise<void> {
  if (corrections.length === 0) return;

  // Batch insert in chunks of 100
  const CHUNK = 100;
  for (let i = 0; i < corrections.length; i += CHUNK) {
    const chunk = corrections.slice(i, i + CHUNK);
    const values: any[] = [];
    const placeholders: string[] = [];
    let n = 1;

    for (const c of chunk) {
      placeholders.push(
        `($${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++})`,
      );
      values.push(
        runId, c.league, c.correction_type,
        c.player_name_sgo, c.player_name_espn,
        c.player_id_sgo, c.player_id_espn,
        c.team_sgo, c.team_espn,
        c.match_tier, c.match_confidence,
        JSON.stringify(c.details),
      );
    }

    await pool.query(
      `INSERT INTO rm_roster_reconciliation (run_id, league, correction_type, player_name_sgo, player_name_espn, player_id_sgo, player_id_espn, team_sgo, team_espn, match_tier, match_confidence, details)
       VALUES ${placeholders.join(', ')}`,
      values,
    );
  }
}

// ---------------------------------------------------------------------------
// ORB Reporting — pipe results into LobsterBoard Data Research Agent
// ---------------------------------------------------------------------------

const ORB_AGENT_ID = 'data-research';

async function reportToOrb(
  runId: string,
  allStats: LeagueStats[],
  allInjuryStats: { league: string; espnInjuries: number; dbInjuries: number; matched: number; nameFixes: number; teamFills: number; statusUpdates: number; newInjuries: number; staleCleared: number }[],
  corrections: Correction[],
  totalApplied: number,
  durationMs: number,
): Promise<void> {
  try {
    const rosterTotals = allStats.reduce(
      (a, s) => ({
        espn: a.espn + s.espnPlayers, sgo: a.sgo + s.sgoPlayers,
        matched: a.matched + s.matched, names: a.names + s.nameCorrections,
        teams: a.teams + s.teamCorrections, missing: a.missing + s.missing, cut: a.cut + s.cut,
      }),
      { espn: 0, sgo: 0, matched: 0, names: 0, teams: 0, missing: 0, cut: 0 },
    );

    const injTotals = allInjuryStats.reduce(
      (a, s) => ({
        espn: a.espn + s.espnInjuries, db: a.db + s.dbInjuries, matched: a.matched + s.matched,
        names: a.names + s.nameFixes, teams: a.teams + s.teamFills, status: a.status + s.statusUpdates,
        newInj: a.newInj + s.newInjuries, stale: a.stale + s.staleCleared,
      }),
      { espn: 0, db: 0, matched: 0, names: 0, teams: 0, status: 0, newInj: 0, stale: 0 },
    );

    const totalScanned = rosterTotals.espn + injTotals.espn;
    const totalNew = rosterTotals.missing + injTotals.newInj;
    const totalInserted = totalApplied + injTotals.newInj;
    const flaggedForReview = corrections.filter(c => c.correction_type === 'team_mismatch').length;

    // 1. Insert reconciliation run
    const orbRun = await pool.query(
      `INSERT INTO orb.reconciliation_runs
        (run_date, run_status, total_entities_scanned, new_entities_found, new_entities_inserted, flagged_for_review, notes)
       VALUES (CURRENT_DATE, 'completed', $1, $2, $3, $4, $5)
       RETURNING id`,
      [
        totalScanned,
        totalNew,
        totalInserted,
        flaggedForReview,
        `Roster reconciler run ${runId}: ${rosterTotals.matched}/${rosterTotals.espn} roster matched, ${rosterTotals.names} name fixes, ${injTotals.newInj} new injuries, ${injTotals.stale} stale cleared. Duration: ${(durationMs / 1000).toFixed(1)}s`,
      ],
    );
    const orbRunId = orbRun.rows[0].id;

    // 2. Insert findings (sample up to 200 most interesting corrections)
    const interestingCorrections = corrections
      .filter(c => c.correction_type !== 'cut_from_roster') // skip cuts — too noisy
      .slice(0, 200);

    if (interestingCorrections.length > 0) {
      const CHUNK = 50;
      for (let i = 0; i < interestingCorrections.length; i += CHUNK) {
        const chunk = interestingCorrections.slice(i, i + CHUNK);
        const values: any[] = [];
        const placeholders: string[] = [];
        let n = 1;

        for (const c of chunk) {
          placeholders.push(
            `($${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++}, $${n++})`,
          );
          values.push(
            orbRunId,
            c.player_name_espn || c.player_name_sgo || 'unknown',
            `${c.league}_${c.correction_type}`,
            c.player_id_sgo || c.player_id_espn || null,
            c.match_confidence || 1.0,
            c.correction_type === 'missing_from_sgo' ? 'inserted_new' : 'corrected',
            c.correction_type === 'missing_from_sgo',
            c.player_id_sgo || c.player_id_espn || null,
          );
        }

        await pool.query(
          `INSERT INTO orb.reconciliation_findings
            (reconciliation_run_id, discovered_name, discovered_type, suggested_parent_id,
             classification_confidence, action_taken, created_new_slot, final_entity_id)
           VALUES ${placeholders.join(', ')}`,
          values,
        );
      }
    }

    // 3. Log activity
    await pool.query(
      `INSERT INTO orb.activity_log
        (agent_id, action, details, output_data, duration_ms, status)
       VALUES ($1, $2, $3, $4, $5, $6)`,
      [
        ORB_AGENT_ID,
        'roster_injury_reconciliation',
        JSON.stringify({
          rm_run_id: runId,
          orb_run_id: orbRunId,
          leagues: allStats.map(s => s.league),
          roster: rosterTotals,
          injuries: injTotals,
        }),
        JSON.stringify({
          corrections_total: corrections.length,
          corrections_applied: totalApplied,
          injury_actions: injTotals.newInj + injTotals.stale + injTotals.names + injTotals.teams + injTotals.status,
        }),
        durationMs,
        'success',
      ],
    );

    console.log(`\n[ORB] Reported to LobsterBoard: orb.reconciliation_runs #${orbRunId}, ${interestingCorrections.length} findings, activity logged under agent '${ORB_AGENT_ID}'`);

  } catch (err: any) {
    console.log(`[ORB] WARNING: Failed to report to ORB tables: ${err.message}`);
    // Non-fatal — reconciler still did its job
  }
}

// ---------------------------------------------------------------------------
// Main
// ---------------------------------------------------------------------------

async function main() {
  const startTime = Date.now();
  const leagues = getLeaguesToProcess();

  console.log('='.repeat(70));
  console.log(`ROSTER RECONCILER — ESPN Source of Truth`);
  console.log(`Leagues: ${leagues.join(', ')}${DRY_RUN ? '  [DRY RUN]' : ''}`);
  console.log('='.repeat(70));

  const runId = await createRun(leagues);
  console.log(`Run ID: ${runId}\n`);

  // Load SGO data
  const sgoData = loadSgoData();

  const allCorrections: Correction[] = [];
  const allStats: LeagueStats[] = [];

  for (const leagueKey of leagues) {
    if (!ESPN_LEAGUES[leagueKey]) {
      console.log(`[SKIP] ${leagueKey} — no ESPN mapping\n`);
      continue;
    }
    if (!sgoData[leagueKey]) {
      console.log(`[SKIP] ${leagueKey} — not in SGO data\n`);
      continue;
    }

    console.log(`\n${'─'.repeat(70)}`);
    console.log(`  ${leagueKey.toUpperCase()}`);
    console.log(`${'─'.repeat(70)}`);

    try {
      // Fetch ESPN roster
      const espnRoster = await fetchLeagueRoster(leagueKey);

      // Reconcile
      const { corrections, stats } = reconcileLeague(leagueKey, espnRoster, sgoData[leagueKey]);
      allCorrections.push(...corrections);
      allStats.push(stats);

      const matchPct = stats.espnPlayers > 0
        ? ((stats.matched / stats.espnPlayers) * 100).toFixed(1)
        : '0';

      console.log(`\n[RECON] ${leagueKey.toUpperCase()} Results:`);
      console.log(`  ESPN: ${stats.espnPlayers}  SGO: ${stats.sgoPlayers}  Matched: ${stats.matched} (${matchPct}%)`);
      console.log(`  Name fixes: ${stats.nameCorrections}  Team fixes: ${stats.teamCorrections}  Missing: ${stats.missing}  Cut: ${stats.cut}`);

      if (stats.nameCorrections > 0) {
        const nameFixes = corrections.filter(c => c.correction_type === 'name_mismatch').slice(0, 10);
        console.log(`\n  Sample name corrections:`);
        for (const c of nameFixes) {
          console.log(`    "${c.player_name_sgo}" → "${c.player_name_espn}" (tier ${c.match_tier})`);
        }
      }

      if (stats.missing > 0) {
        const missingList = corrections.filter(c => c.correction_type === 'missing_from_sgo').slice(0, 10);
        console.log(`\n  Sample missing from SGO:`);
        for (const c of missingList) {
          console.log(`    ${c.player_name_espn} (${c.team_espn})`);
        }
      }

    } catch (err: any) {
      console.log(`[ERROR] ${leagueKey}: ${err.message}`);
      allStats.push({
        league: leagueKey,
        espnPlayers: 0,
        sgoPlayers: sgoData[leagueKey]?.players?.length || 0,
        matched: 0,
        nameCorrections: 0,
        teamCorrections: 0,
        missing: 0,
        cut: 0,
      });
    }

    // Rate limit between leagues
    await new Promise(r => setTimeout(r, 300));
  }

  // Apply corrections (unless dry run)
  let totalApplied = 0;
  if (!DRY_RUN && allCorrections.length > 0) {
    console.log(`\n${'─'.repeat(70)}`);
    console.log('  APPLYING CORRECTIONS');
    console.log(`${'─'.repeat(70)}`);

    totalApplied = await applyCorrections(allCorrections, sgoData);
    saveSgoData(sgoData);
    console.log(`[RECON] Updated ${PLAYERS_JSON}`);
    console.log(`[RECON] Applied ${totalApplied} corrections to JSON + database`);
  } else if (DRY_RUN) {
    console.log(`\n[DRY RUN] Would apply ${allCorrections.length} corrections. No changes made.`);
  }

  // =========================================================================
  // PHASE 2: INJURY RECONCILIATION
  // =========================================================================

  console.log(`\n${'='.repeat(70)}`);
  console.log('PHASE 2: INJURY RECONCILIATION');
  console.log('='.repeat(70));

  interface InjuryStats {
    league: string;
    espnInjuries: number;
    dbInjuries: number;
    matched: number;
    nameFixes: number;
    teamFills: number;
    statusUpdates: number;
    newInjuries: number;
    staleCleared: number;
  }

  const allInjuryStats: InjuryStats[] = [];
  let totalInjuryActions = 0;

  for (const leagueKey of leagues) {
    if (!ESPN_LEAGUES[leagueKey]) continue;

    console.log(`\n[INJ] ─── ${leagueKey.toUpperCase()} ───`);

    try {
      const espnInj = await fetchLeagueInjuries(leagueKey);

      // Load current DB injuries for this league
      const dbRes = await pool.query(
        `SELECT id, "playerName", team, status, "injuryType", source, "playerExternalId"
         FROM "PlayerInjury"
         WHERE league = $1 AND status NOT IN ('Active')`,
        [leagueKey],
      );
      const dbInjuries = dbRes.rows;

      // Build canonical name lookup from our reconciled roster
      const sgoLeague = sgoData[leagueKey];
      const rosterNames = new Map<string, string>(); // normalized → canonical display
      if (sgoLeague?.players) {
        for (const p of sgoLeague.players) {
          const norm = normalizeName(p.name);
          rosterNames.set(norm, p.name);
          rosterNames.set(stripSuffix(norm), p.name);
        }
      }

      // Build normalized ESPN injury map: normName → ESPNInjury
      const espnByNorm = new Map<string, ESPNInjury>();
      for (const inj of espnInj.injuries) {
        espnByNorm.set(normalizeName(inj.playerName), inj);
        espnByNorm.set(stripSuffix(normalizeName(inj.playerName)), inj);
      }

      let matched = 0, nameFixes = 0, teamFills = 0, statusUpdates = 0, staleCleared = 0;

      // Reconcile existing DB injuries against ESPN
      for (const dbInj of dbInjuries) {
        const dbNorm = normalizeName(dbInj.playerName);
        const dbSuff = stripSuffix(dbNorm);

        // Match to ESPN injury
        const espnMatch = espnByNorm.get(dbNorm) || espnByNorm.get(dbSuff);

        if (espnMatch) {
          matched++;

          // Resolve canonical name
          const canonical = rosterNames.get(normalizeName(espnMatch.playerName))
            || rosterNames.get(stripSuffix(normalizeName(espnMatch.playerName)))
            || espnMatch.playerName;

          const updates: string[] = [];
          const params: any[] = [];
          let paramIdx = 1;

          // Fix player name to canonical
          if (dbInj.playerName !== canonical) {
            updates.push(`"playerName" = $${paramIdx++}`);
            params.push(canonical);
            nameFixes++;
          }

          // Fill missing team from ESPN
          if (!dbInj.team && espnMatch.teamShort) {
            updates.push(`team = $${paramIdx++}`);
            params.push(espnMatch.teamShort);
            teamFills++;
          }

          // Update status if ESPN has a different one
          const espnStatus = espnMatch.status;
          if (espnStatus && dbInj.status !== espnStatus) {
            updates.push(`status = $${paramIdx++}`);
            params.push(espnStatus);
            statusUpdates++;
          }

          if (updates.length > 0 && !DRY_RUN) {
            updates.push(`"updatedAt" = NOW()`);
            params.push(dbInj.id);
            await pool.query(
              `UPDATE "PlayerInjury" SET ${updates.join(', ')} WHERE id = $${paramIdx}`,
              params,
            );
            totalInjuryActions++;
          }
        }
      }

      // Find ESPN injuries not in DB → insert new ones
      const dbNormSet = new Set(dbInjuries.map(d => normalizeName(d.playerName)));
      const dbSuffSet = new Set(dbInjuries.map(d => stripSuffix(normalizeName(d.playerName))));
      // Also check Active entries
      const activeRes = await pool.query(
        `SELECT "playerName" FROM "PlayerInjury" WHERE league = $1 AND status = 'Active'`,
        [leagueKey],
      );
      for (const r of activeRes.rows) {
        dbNormSet.add(normalizeName(r.playerName));
        dbSuffSet.add(stripSuffix(normalizeName(r.playerName)));
      }

      let newInjuries = 0;
      for (const espnInj2 of espnInj.injuries) {
        const norm = normalizeName(espnInj2.playerName);
        const suff = stripSuffix(norm);
        if (dbNormSet.has(norm) || dbNormSet.has(suff) || dbSuffSet.has(norm) || dbSuffSet.has(suff)) continue;

        // Resolve to canonical name
        const canonical = rosterNames.get(norm) || rosterNames.get(suff) || espnInj2.playerName;

        newInjuries++;
        if (!DRY_RUN) {
          await pool.query(
            `INSERT INTO "PlayerInjury"
              (league, "playerExternalId", "playerName", team, status, "injuryType", description, source, "sourceUpdatedAt", raw, "updatedAt")
             VALUES ($1, $2, $3, $4, $5, $6, $7, 'espn_reconciler', $8, $9, NOW())
             ON CONFLICT (league, "playerExternalId", source) DO UPDATE SET
              "playerName" = EXCLUDED."playerName",
              team = EXCLUDED.team,
              status = EXCLUDED.status,
              "injuryType" = EXCLUDED."injuryType",
              description = EXCLUDED.description,
              "sourceUpdatedAt" = EXCLUDED."sourceUpdatedAt",
              raw = EXCLUDED.raw,
              "updatedAt" = NOW()`,
            [
              leagueKey,
              `espn_recon_${espnInj2.espnId || canonical}`,
              canonical,
              espnInj2.teamShort || null,
              espnInj2.status,
              espnInj2.injuryType || null,
              espnInj2.shortComment || null,
              espnInj2.date ? new Date(espnInj2.date) : new Date(),
              JSON.stringify({ espnId: espnInj2.espnId, athleteId: espnInj2.athleteId, longComment: espnInj2.longComment }),
            ],
          );
          totalInjuryActions++;
        }
      }

      // Check DB injuries no longer in ESPN → mark as stale/Active (recovered)
      for (const dbInj of dbInjuries) {
        const dbNorm = normalizeName(dbInj.playerName);
        const dbSuff = stripSuffix(dbNorm);
        if (!espnByNorm.has(dbNorm) && !espnByNorm.has(dbSuff)) {
          // Player not on ESPN injury list anymore → likely recovered
          if (dbInj.source === 'espn_reconciler' || dbInj.source === 'espn') {
            staleCleared++;
            if (!DRY_RUN) {
              await pool.query(
                `UPDATE "PlayerInjury" SET status = 'Active', "updatedAt" = NOW() WHERE id = $1`,
                [dbInj.id],
              );
              totalInjuryActions++;
            }
          }
        }
      }

      const injStats: InjuryStats = {
        league: leagueKey,
        espnInjuries: espnInj.injuries.length,
        dbInjuries: dbInjuries.length,
        matched,
        nameFixes,
        teamFills,
        statusUpdates,
        newInjuries,
        staleCleared,
      };
      allInjuryStats.push(injStats);

      console.log(`[INJ] ${leagueKey.toUpperCase()}: ESPN=${espnInj.injuries.length} DB=${dbInjuries.length} Matched=${matched}`);
      console.log(`  Name fixes: ${nameFixes}  Team fills: ${teamFills}  Status updates: ${statusUpdates}  New: ${newInjuries}  Cleared: ${staleCleared}`);

      if (newInjuries > 0) {
        const newList = espnInj.injuries
          .filter(e => {
            const n = normalizeName(e.playerName);
            const s = stripSuffix(n);
            return !dbNormSet.has(n) && !dbNormSet.has(s) && !dbSuffSet.has(n) && !dbSuffSet.has(s);
          })
          .slice(0, 5);
        for (const e of newList) {
          console.log(`    + ${e.playerName} (${e.teamShort}) — ${e.status}: ${(e.shortComment || '').slice(0, 80)}`);
        }
        if (newInjuries > 5) console.log(`    ... and ${newInjuries - 5} more`);
      }

    } catch (err: any) {
      console.log(`[INJ-ERROR] ${leagueKey}: ${err.message}`);
      allInjuryStats.push({
        league: leagueKey, espnInjuries: 0, dbInjuries: 0, matched: 0,
        nameFixes: 0, teamFills: 0, statusUpdates: 0, newInjuries: 0, staleCleared: 0,
      });
    }

    await new Promise(r => setTimeout(r, 200));
  }

  // Update run record with injury stats
  const injTotals = allInjuryStats.reduce(
    (a, s) => ({
      espn: a.espn + s.espnInjuries, db: a.db + s.dbInjuries, matched: a.matched + s.matched,
      names: a.names + s.nameFixes, teams: a.teams + s.teamFills, status: a.status + s.statusUpdates,
      newInj: a.newInj + s.newInjuries, stale: a.stale + s.staleCleared,
    }),
    { espn: 0, db: 0, matched: 0, names: 0, teams: 0, status: 0, newInj: 0, stale: 0 },
  );

  await pool.query(
    `UPDATE rm_roster_reconciliation_runs SET
      total_injury_espn = $1, total_injury_db = $2, total_injury_matched = $3,
      total_injury_name_fixes = $4, total_injury_team_fills = $5,
      total_injury_status_updates = $6, total_injury_new = $7, total_injury_stale = $8
    WHERE id = $9`,
    [injTotals.espn, injTotals.db, injTotals.matched, injTotals.names, injTotals.teams, injTotals.status, injTotals.newInj, injTotals.stale, runId],
  );

  // Log to database
  await logCorrections(runId, allCorrections);
  await completeRun(runId, allStats, totalApplied, startTime);

  // Report to ORB (LobsterBoard Data Research Agent)
  await reportToOrb(runId, allStats, allInjuryStats, allCorrections, totalApplied, Date.now() - startTime);

  // Write report JSON
  if (!fs.existsSync(LOG_DIR)) fs.mkdirSync(LOG_DIR, { recursive: true });
  const dateStr = new Date().toISOString().slice(0, 10);
  const reportPath = path.join(LOG_DIR, `roster-reconciliation-${dateStr}.json`);
  const report = {
    runId,
    date: dateStr,
    dryRun: DRY_RUN,
    duration: `${((Date.now() - startTime) / 1000).toFixed(1)}s`,
    stats: allStats,
    injuries: {
      stats: allInjuryStats,
      totals: injTotals,
      actions: totalInjuryActions,
    },
    corrections: {
      total: allCorrections.length,
      applied: totalApplied,
      byType: {
        name_mismatch: allCorrections.filter(c => c.correction_type === 'name_mismatch').length,
        team_mismatch: allCorrections.filter(c => c.correction_type === 'team_mismatch').length,
        missing_from_sgo: allCorrections.filter(c => c.correction_type === 'missing_from_sgo').length,
        cut_from_roster: allCorrections.filter(c => c.correction_type === 'cut_from_roster').length,
      },
      details: allCorrections,
    },
  };
  fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));

  // Final summary
  console.log(`\n${'='.repeat(70)}`);
  console.log('RECONCILIATION SUMMARY');
  console.log('='.repeat(70));
  console.log(`${'League'.padEnd(15)} ${'ESPN'.padStart(6)} ${'SGO'.padStart(6)} ${'Match'.padStart(6)} ${'%'.padStart(7)} ${'Names'.padStart(6)} ${'Teams'.padStart(6)} ${'Miss'.padStart(6)} ${'Cut'.padStart(6)}`);
  console.log('─'.repeat(70));
  for (const s of allStats) {
    const pct = s.espnPlayers > 0 ? ((s.matched / s.espnPlayers) * 100).toFixed(1) + '%' : 'N/A';
    console.log(
      `${s.league.toUpperCase().padEnd(15)} ${String(s.espnPlayers).padStart(6)} ${String(s.sgoPlayers).padStart(6)} ${String(s.matched).padStart(6)} ${pct.padStart(7)} ${String(s.nameCorrections).padStart(6)} ${String(s.teamCorrections).padStart(6)} ${String(s.missing).padStart(6)} ${String(s.cut).padStart(6)}`,
    );
  }
  console.log('─'.repeat(70));

  const totals = allStats.reduce(
    (a, s) => ({
      espn: a.espn + s.espnPlayers,
      sgo: a.sgo + s.sgoPlayers,
      matched: a.matched + s.matched,
      names: a.names + s.nameCorrections,
      teams: a.teams + s.teamCorrections,
      missing: a.missing + s.missing,
      cut: a.cut + s.cut,
    }),
    { espn: 0, sgo: 0, matched: 0, names: 0, teams: 0, missing: 0, cut: 0 },
  );
  const totalPct = totals.espn > 0 ? ((totals.matched / totals.espn) * 100).toFixed(1) + '%' : 'N/A';
  console.log(
    `${'TOTAL'.padEnd(15)} ${String(totals.espn).padStart(6)} ${String(totals.sgo).padStart(6)} ${String(totals.matched).padStart(6)} ${totalPct.padStart(7)} ${String(totals.names).padStart(6)} ${String(totals.teams).padStart(6)} ${String(totals.missing).padStart(6)} ${String(totals.cut).padStart(6)}`,
  );

  console.log(`\nRoster: ${allCorrections.length} corrections, ${totalApplied} applied`);

  // Injury summary
  if (allInjuryStats.length > 0) {
    console.log(`\n${'='.repeat(70)}`);
    console.log('INJURY RECONCILIATION SUMMARY');
    console.log('='.repeat(70));
    console.log(`${'League'.padEnd(15)} ${'ESPN'.padStart(6)} ${'DB'.padStart(6)} ${'Match'.padStart(6)} ${'Names'.padStart(6)} ${'Teams'.padStart(6)} ${'Status'.padStart(7)} ${'New'.padStart(5)} ${'Clear'.padStart(6)}`);
    console.log('─'.repeat(70));
    for (const s of allInjuryStats) {
      console.log(
        `${s.league.toUpperCase().padEnd(15)} ${String(s.espnInjuries).padStart(6)} ${String(s.dbInjuries).padStart(6)} ${String(s.matched).padStart(6)} ${String(s.nameFixes).padStart(6)} ${String(s.teamFills).padStart(6)} ${String(s.statusUpdates).padStart(7)} ${String(s.newInjuries).padStart(5)} ${String(s.staleCleared).padStart(6)}`,
      );
    }
    console.log('─'.repeat(70));
    console.log(
      `${'TOTAL'.padEnd(15)} ${String(injTotals.espn).padStart(6)} ${String(injTotals.db).padStart(6)} ${String(injTotals.matched).padStart(6)} ${String(injTotals.names).padStart(6)} ${String(injTotals.teams).padStart(6)} ${String(injTotals.status).padStart(7)} ${String(injTotals.newInj).padStart(5)} ${String(injTotals.stale).padStart(6)}`,
    );
    console.log(`\nInjury actions: ${totalInjuryActions} applied${DRY_RUN ? ' (dry run — 0 actual)' : ''}`);
  }

  console.log(`\nReport: ${reportPath}`);
  console.log(`Duration: ${((Date.now() - startTime) / 1000).toFixed(1)}s`);

  await pool.end();
}

main().catch(async (err) => {
  console.error('FATAL:', err);
  try { await pool.end(); } catch {}
  process.exit(1);
});