#!/usr/bin/env npx tsx
/**
 * Unified Data Sync Pipeline
 *
 * Orchestrates all data sources and ensures canonical ID linkage.
 * Run daily or on-demand to keep data unified.
 *
 * Usage:
 *   npx tsx scripts/unified-sync.ts --full           # Full sync all sources
 *   npx tsx scripts/unified-sync.ts --league=nba    # Sync specific league
 *   npx tsx scripts/unified-sync.ts --step=rosters  # Run specific step
 */

import { PrismaClient } from '../prisma_sports/generated/sports-client';
import {
  resolveOrCreateCanonicalPlayer,
  resolveOrCreateCanonicalGame,
} from '../src/lib/canonicalResolver';

const prisma = new PrismaClient();
const BATCH_SIZE = 1000;

// ============================================================
// STEP 1: Sync Team Rosters from ESPN
// ============================================================
async function syncRosters(leagues: string[]) {
  console.log('\n=== STEP 1: Syncing Team Rosters ===\n');

  const ESPN_BASE = 'https://site.api.espn.com/apis/site/v2/sports';
  const LEAGUE_CONFIG: Record<string, { sport: string; espnLeague: string }> = {
    nba: { sport: 'basketball', espnLeague: 'nba' },
    nfl: { sport: 'football', espnLeague: 'nfl' },
    nhl: { sport: 'hockey', espnLeague: 'nhl' },
    mlb: { sport: 'baseball', espnLeague: 'mlb' },
  };

  const ESPN_ABBR_MAP: Record<string, Record<string, string>> = {
    nba: { 'GS': 'GSW', 'NO': 'NOP', 'NY': 'NYK', 'SA': 'SAS', 'UTAH': 'UTA', 'WSH': 'WAS' },
    nfl: { 'WSH': 'WAS' },
    nhl: { 'NJ': 'NJD', 'WSH': 'WAS', 'UTAH': 'UTA' },
    mlb: { 'WSH': 'WAS', 'CHW': 'CWS' },
  };

  function normalizePlayerName(name: string): string {
    return name.toLowerCase()
      .normalize('NFD').replace(/[\u0300-\u036f]/g, '')
      .replace(/\s+(jr\.?|sr\.?|iii|ii|iv|v)$/i, '')
      .replace(/\./g, '')
      .replace(/\s+/g, ' ')
      .trim();
  }

  for (const league of leagues) {
    const config = LEAGUE_CONFIG[league];
    if (!config) continue;

    console.log(`Processing ${league.toUpperCase()}...`);

    // Get canonical teams
    const canonicalTeams = await prisma.canonicalTeam.findMany({
      where: { league },
      select: { id: true, abbr: true },
    });
    const teamByAbbr = new Map(canonicalTeams.map(t => [t.abbr, t.id]));

    // Fetch ESPN teams
    const teamsRes = await fetch(`${ESPN_BASE}/${config.sport}/${config.espnLeague}/teams?limit=50`);
    const teamsData = await teamsRes.json();
    const espnTeams = teamsData?.sports?.[0]?.leagues?.[0]?.teams || [];

    let updated = 0;
    for (const t of espnTeams) {
      const espnAbbr = t.team?.abbreviation;
      const mappedAbbr = ESPN_ABBR_MAP[league]?.[espnAbbr] || espnAbbr;
      const teamId = teamByAbbr.get(mappedAbbr);
      if (!teamId) continue;

      // Fetch roster
      const rosterRes = await fetch(`${ESPN_BASE}/${config.sport}/${config.espnLeague}/teams/${t.team.id}/roster`);
      const rosterData = await rosterRes.json();
      const athletes = rosterData?.athletes || [];

      // Handle both flat and grouped formats
      const players: any[] = [];
      for (const item of athletes) {
        if (item?.fullName) players.push(item);
        else if (item?.items) players.push(...item.items);
      }

      for (const player of players) {
        const name = player.fullName || player.displayName;
        if (!name) continue;

        const normalized = normalizePlayerName(name);
        await prisma.canonicalPlayer.updateMany({
          where: { league, normalizedName: normalized, teamId: null },
          data: { teamId, position: player.position?.abbreviation || null },
        });
        updated++;
      }

      await new Promise(r => setTimeout(r, 100)); // Rate limit
    }

    console.log(`  ${league.toUpperCase()}: ${updated} players checked`);
  }
}

// ============================================================
// STEP 2: Link PlayerPropLine to Canonical Players
// ============================================================
async function linkPropsToCanonical(leagues: string[]) {
  console.log('\n=== STEP 2: Linking Props to Canonical Players ===\n');

  for (const league of leagues) {
    console.log(`Processing ${league.toUpperCase()}...`);

    let linked = 0;
    let cursor: bigint | undefined;

    while (true) {
      const batch = await prisma.playerPropLine.findMany({
        where: { league, canonicalPlayerId: null },
        select: { id: true, playerExternalId: true },
        take: BATCH_SIZE,
        ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
        orderBy: { id: 'asc' },
      });

      if (batch.length === 0) break;

      const updates: { id: bigint; canonicalPlayerId: bigint }[] = [];

      for (const prop of batch) {
        try {
          const result = await resolveOrCreateCanonicalPlayer({
            league,
            externalId: prop.playerExternalId,
            source: 'sgo',
          });
          if (result?.id) {
            updates.push({ id: prop.id, canonicalPlayerId: result.id });
          }
        } catch (e) {
          // Skip errors
        }
      }

      if (updates.length > 0) {
        await prisma.$transaction(
          updates.map(u => prisma.playerPropLine.update({
            where: { id: u.id },
            data: { canonicalPlayerId: u.canonicalPlayerId },
          }))
        );
        linked += updates.length;
      }

      cursor = batch[batch.length - 1].id;

      if (linked % 5000 === 0 && linked > 0) {
        console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()} linked so far...`);
      }
    }

    console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()} total linked`);
  }
}

// ============================================================
// STEP 3: Link PlayerGameMetric to Canonical Players
// ============================================================
async function linkMetricsToCanonical(leagues: string[]) {
  console.log('\n=== STEP 3: Linking Metrics to Canonical Players ===\n');

  for (const league of leagues) {
    console.log(`Processing ${league.toUpperCase()}...`);

    let linked = 0;
    let cursor: bigint | undefined;

    while (true) {
      const batch = await prisma.playerGameMetric.findMany({
        where: { league, canonicalPlayerId: null },
        select: { id: true, playerExternalId: true, playerName: true },
        take: BATCH_SIZE,
        ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
        orderBy: { id: 'asc' },
      });

      if (batch.length === 0) break;

      const updates: { id: bigint; canonicalPlayerId: bigint }[] = [];

      for (const metric of batch) {
        try {
          const result = await resolveOrCreateCanonicalPlayer({
            league,
            externalId: metric.playerExternalId,
            name: metric.playerName || undefined,
            source: 'metric',
          });
          if (result?.id) {
            updates.push({ id: metric.id, canonicalPlayerId: result.id });
          }
        } catch (e) {
          // Skip errors
        }
      }

      if (updates.length > 0) {
        await prisma.$transaction(
          updates.map(u => prisma.playerGameMetric.update({
            where: { id: u.id },
            data: { canonicalPlayerId: u.canonicalPlayerId },
          }))
        );
        linked += updates.length;
      }

      cursor = batch[batch.length - 1].id;

      if (linked % 10000 === 0 && linked > 0) {
        console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()} linked so far...`);
      }
    }

    console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()} total linked`);
  }
}

// ============================================================
// STEP 4: Create Missing Canonical Players from SGO IDs
// ============================================================
async function createMissingPlayers(leagues: string[]) {
  console.log('\n=== STEP 4: Creating Missing Canonical Players ===\n');

  function parseSgoPlayerId(sgoId: string): { name: string; league: string } | null {
    const leagueMatch = sgoId.match(/_(NBA|NFL|NHL|MLB|WNBA|NCAAB|NCAAF|MMA|UFC)$/i);
    if (!leagueMatch) return null;
    const league = leagueMatch[1].toLowerCase();
    const withoutLeague = sgoId.slice(0, -leagueMatch[0].length);
    const parts = withoutLeague.split('_');
    const lastPart = parts[parts.length - 1];
    if (!/^\d+$/.test(lastPart)) return null;
    const nameParts = parts.slice(0, -1);
    return { name: nameParts.join(' ').toLowerCase(), league };
  }

  function normalizePlayerName(name: string): string {
    return name.toLowerCase()
      .normalize('NFD').replace(/[\u0300-\u036f]/g, '')
      .replace(/\s+(jr\.?|sr\.?|iii|ii|iv|v)$/i, '')
      .replace(/\./g, '')
      .replace(/\s+/g, ' ')
      .trim();
  }

  for (const league of leagues) {
    // Find SGO-style player IDs without canonical link
    const unlinked = await prisma.playerPropLine.findMany({
      where: {
        league,
        canonicalPlayerId: null,
        playerExternalId: { endsWith: `_${league.toUpperCase()}` },
      },
      select: { playerExternalId: true },
      distinct: ['playerExternalId'],
    });

    let created = 0;
    for (const { playerExternalId } of unlinked) {
      const parsed = parseSgoPlayerId(playerExternalId);
      if (!parsed) continue;

      const normalized = normalizePlayerName(parsed.name);

      // Check if exists
      const exists = await prisma.canonicalPlayer.findUnique({
        where: { league_normalizedName: { league, normalizedName: normalized } },
      });
      if (exists) {
        // Create alias if needed
        const aliasExists = await prisma.playerAlias.findFirst({
          where: { alias: playerExternalId },
        });
        if (!aliasExists) {
          try {
            await prisma.playerAlias.create({
              data: {
                playerId: exists.id,
                source: 'sgo',
                alias: playerExternalId,
                aliasType: 'external_id',
              },
            });
          } catch (e) { /* ignore */ }
        }
        continue;
      }

      // Create new player
      const fullName = parsed.name.split(' ')
        .map(w => w.charAt(0).toUpperCase() + w.slice(1))
        .join(' ');

      try {
        const newPlayer = await prisma.canonicalPlayer.create({
          data: { league, fullName, normalizedName: normalized, sgoId: playerExternalId },
        });
        await prisma.playerAlias.create({
          data: {
            playerId: newPlayer.id,
            source: 'sgo',
            alias: playerExternalId,
            aliasType: 'external_id',
          },
        });
        created++;
      } catch (e) { /* ignore duplicates */ }
    }

    console.log(`  ${league.toUpperCase()}: ${created} new players created`);
  }
}

// ============================================================
// STEP 5: Report Coverage
// ============================================================
async function reportCoverage() {
  console.log('\n=== FINAL COVERAGE REPORT ===\n');

  console.log('PlayerPropLine Coverage:');
  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const total = await prisma.playerPropLine.count({ where: { league } });
    const linked = await prisma.playerPropLine.count({
      where: { league, NOT: { canonicalPlayerId: null } },
    });
    const pct = total > 0 ? ((linked / total) * 100).toFixed(1) : '0.0';
    console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()}/${total.toLocaleString()} (${pct}%)`);
  }

  console.log('\nPlayerGameMetric Coverage:');
  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const total = await prisma.playerGameMetric.count({ where: { league } });
    const linked = await prisma.playerGameMetric.count({
      where: { league, NOT: { canonicalPlayerId: null } },
    });
    const pct = total > 0 ? ((linked / total) * 100).toFixed(1) : '0.0';
    console.log(`  ${league.toUpperCase()}: ${linked.toLocaleString()}/${total.toLocaleString()} (${pct}%)`);
  }

  console.log('\nCanonical Entity Counts:');
  const players = await prisma.canonicalPlayer.count();
  const teams = await prisma.canonicalTeam.count();
  const games = await prisma.canonicalGame.count();
  const aliases = await prisma.playerAlias.count();
  console.log(`  CanonicalPlayer: ${players.toLocaleString()}`);
  console.log(`  CanonicalTeam: ${teams.toLocaleString()}`);
  console.log(`  CanonicalGame: ${games.toLocaleString()}`);
  console.log(`  PlayerAlias: ${aliases.toLocaleString()}`);
}

// ============================================================
// MAIN
// ============================================================
async function main() {
  const args = process.argv.slice(2);
  const leagueArg = args.find(a => a.startsWith('--league='))?.split('=')[1];
  const stepArg = args.find(a => a.startsWith('--step='))?.split('=')[1];
  const isFull = args.includes('--full');

  const leagues = leagueArg ? [leagueArg.toLowerCase()] : ['nba', 'nfl', 'nhl', 'mlb'];

  console.log('='.repeat(60));
  console.log('UNIFIED DATA SYNC PIPELINE');
  console.log('='.repeat(60));
  console.log(`Leagues: ${leagues.join(', ').toUpperCase()}`);
  console.log(`Mode: ${stepArg || (isFull ? 'Full Sync' : 'Quick Sync')}`);

  const startTime = Date.now();

  try {
    if (!stepArg || stepArg === 'rosters' || isFull) {
      await syncRosters(leagues);
    }

    if (!stepArg || stepArg === 'missing' || isFull) {
      await createMissingPlayers(leagues);
    }

    if (!stepArg || stepArg === 'props' || isFull) {
      await linkPropsToCanonical(leagues);
    }

    if (!stepArg || stepArg === 'metrics' || isFull) {
      await linkMetricsToCanonical(leagues);
    }

    await reportCoverage();

    const elapsed = ((Date.now() - startTime) / 1000 / 60).toFixed(1);
    console.log(`\n${'='.repeat(60)}`);
    console.log(`SYNC COMPLETE (${elapsed} minutes)`);
    console.log('='.repeat(60));
  } finally {
    await prisma.$disconnect();
  }
}

main().catch((e) => {
  console.error('Sync failed:', e);
  process.exit(1);
});