#!/usr/bin/env npx tsx
/**
 * Comprehensive Sports Data Cleanup
 *
 * Fixes:
 * 1. Remove games where teams don't belong to that league
 * 2. Normalize team names (abbreviations → canonical)
 * 3. Fix case inconsistencies
 * 4. Remove All-Star and exhibition games
 */
require('dotenv').config({ path: '.env.local' });
require('dotenv').config({ path: '.env' });

import { getSportsDb, isSportsDbEnabled } from '../lib/sportsDb';

// Canonical team lists per league
const VALID_TEAMS: Record<string, string[]> = {
  nba: [
    'Atlanta Hawks', 'Boston Celtics', 'Brooklyn Nets', 'Charlotte Hornets',
    'Chicago Bulls', 'Cleveland Cavaliers', 'Dallas Mavericks', 'Denver Nuggets',
    'Detroit Pistons', 'Golden State Warriors', 'Houston Rockets', 'Indiana Pacers',
    'Los Angeles Clippers', 'Los Angeles Lakers', 'Memphis Grizzlies', 'Miami Heat',
    'Milwaukee Bucks', 'Minnesota Timberwolves', 'New Orleans Pelicans', 'New York Knicks',
    'Oklahoma City Thunder', 'Orlando Magic', 'Philadelphia 76ers', 'Phoenix Suns',
    'Portland Trail Blazers', 'Sacramento Kings', 'San Antonio Spurs', 'Toronto Raptors',
    'Utah Jazz', 'Washington Wizards',
  ],
  nfl: [
    'Arizona Cardinals', 'Atlanta Falcons', 'Baltimore Ravens', 'Buffalo Bills',
    'Carolina Panthers', 'Chicago Bears', 'Cincinnati Bengals', 'Cleveland Browns',
    'Dallas Cowboys', 'Denver Broncos', 'Detroit Lions', 'Green Bay Packers',
    'Houston Texans', 'Indianapolis Colts', 'Jacksonville Jaguars', 'Kansas City Chiefs',
    'Las Vegas Raiders', 'Los Angeles Chargers', 'Los Angeles Rams', 'Miami Dolphins',
    'Minnesota Vikings', 'New England Patriots', 'New Orleans Saints', 'New York Giants',
    'New York Jets', 'Philadelphia Eagles', 'Pittsburgh Steelers', 'San Francisco 49ers',
    'Seattle Seahawks', 'Tampa Bay Buccaneers', 'Tennessee Titans', 'Washington Commanders',
  ],
  nhl: [
    'Anaheim Ducks', 'Arizona Coyotes', 'Boston Bruins', 'Buffalo Sabres',
    'Calgary Flames', 'Carolina Hurricanes', 'Chicago Blackhawks', 'Colorado Avalanche',
    'Columbus Blue Jackets', 'Dallas Stars', 'Detroit Red Wings', 'Edmonton Oilers',
    'Florida Panthers', 'Los Angeles Kings', 'Minnesota Wild', 'Montreal Canadiens',
    'Nashville Predators', 'New Jersey Devils', 'New York Islanders', 'New York Rangers',
    'Ottawa Senators', 'Philadelphia Flyers', 'Pittsburgh Penguins', 'San Jose Sharks',
    'Seattle Kraken', 'St. Louis Blues', 'Tampa Bay Lightning', 'Toronto Maple Leafs',
    'Vancouver Canucks', 'Vegas Golden Knights', 'Washington Capitals', 'Winnipeg Jets',
    // Utah Hockey Club (new team 2024)
    'Utah Hockey Club',
  ],
  mlb: [
    'Arizona Diamondbacks', 'Atlanta Braves', 'Baltimore Orioles', 'Boston Red Sox',
    'Chicago Cubs', 'Chicago White Sox', 'Cincinnati Reds', 'Cleveland Guardians',
    'Colorado Rockies', 'Detroit Tigers', 'Houston Astros', 'Kansas City Royals',
    'Los Angeles Angels', 'Los Angeles Dodgers', 'Miami Marlins', 'Milwaukee Brewers',
    'Minnesota Twins', 'New York Mets', 'New York Yankees', 'Oakland Athletics',
    'Philadelphia Phillies', 'Pittsburgh Pirates', 'San Diego Padres', 'San Francisco Giants',
    'Seattle Mariners', 'St. Louis Cardinals', 'Tampa Bay Rays', 'Texas Rangers',
    'Toronto Blue Jays', 'Washington Nationals',
  ],
};

// Team name normalization mappings (case-insensitive)
const TEAM_ALIASES: Record<string, Record<string, string>> = {
  nba: {
    'lal': 'Los Angeles Lakers', 'lakers': 'Los Angeles Lakers',
    'bos': 'Boston Celtics', 'celtics': 'Boston Celtics',
    'gsw': 'Golden State Warriors', 'warriors': 'Golden State Warriors', 'golden state': 'Golden State Warriors',
    'bkn': 'Brooklyn Nets', 'nets': 'Brooklyn Nets',
    'nyk': 'New York Knicks', 'knicks': 'New York Knicks',
    'mia': 'Miami Heat', 'heat': 'Miami Heat',
    'chi': 'Chicago Bulls', 'bulls': 'Chicago Bulls',
    'cle': 'Cleveland Cavaliers', 'cavaliers': 'Cleveland Cavaliers', 'cavs': 'Cleveland Cavaliers',
    'dal': 'Dallas Mavericks', 'mavericks': 'Dallas Mavericks', 'mavs': 'Dallas Mavericks',
    'den': 'Denver Nuggets', 'nuggets': 'Denver Nuggets',
    'phi': 'Philadelphia 76ers', '76ers': 'Philadelphia 76ers', 'sixers': 'Philadelphia 76ers',
    'phx': 'Phoenix Suns', 'suns': 'Phoenix Suns',
    'mil': 'Milwaukee Bucks', 'bucks': 'Milwaukee Bucks',
    'min': 'Minnesota Timberwolves', 'timberwolves': 'Minnesota Timberwolves', 'wolves': 'Minnesota Timberwolves',
    'okc': 'Oklahoma City Thunder', 'thunder': 'Oklahoma City Thunder',
    'sac': 'Sacramento Kings', 'kings': 'Sacramento Kings',
    'lac': 'Los Angeles Clippers', 'clippers': 'Los Angeles Clippers',
    'tor': 'Toronto Raptors', 'raptors': 'Toronto Raptors',
    'ind': 'Indiana Pacers', 'pacers': 'Indiana Pacers',
    'atl': 'Atlanta Hawks', 'hawks': 'Atlanta Hawks',
    'hou': 'Houston Rockets', 'rockets': 'Houston Rockets',
    'mem': 'Memphis Grizzlies', 'grizzlies': 'Memphis Grizzlies',
    'nop': 'New Orleans Pelicans', 'pelicans': 'New Orleans Pelicans',
    'orl': 'Orlando Magic', 'magic': 'Orlando Magic',
    'por': 'Portland Trail Blazers', 'trail blazers': 'Portland Trail Blazers', 'blazers': 'Portland Trail Blazers',
    'sas': 'San Antonio Spurs', 'spurs': 'San Antonio Spurs',
    'uta': 'Utah Jazz', 'jazz': 'Utah Jazz',
    'was': 'Washington Wizards', 'wizards': 'Washington Wizards',
    'cha': 'Charlotte Hornets', 'hornets': 'Charlotte Hornets',
    'det': 'Detroit Pistons', 'pistons': 'Detroit Pistons',
  },
  nhl: {
    'ana': 'Anaheim Ducks', 'ducks': 'Anaheim Ducks', 'anaheim mighty ducks': 'Anaheim Ducks',
    'ari': 'Arizona Coyotes', 'coyotes': 'Arizona Coyotes',
    'bos': 'Boston Bruins', 'bruins': 'Boston Bruins',
    'buf': 'Buffalo Sabres', 'sabres': 'Buffalo Sabres',
    'cgy': 'Calgary Flames', 'flames': 'Calgary Flames',
    'car': 'Carolina Hurricanes', 'hurricanes': 'Carolina Hurricanes',
    'chi': 'Chicago Blackhawks', 'blackhawks': 'Chicago Blackhawks',
    'col': 'Colorado Avalanche', 'avalanche': 'Colorado Avalanche',
    'cbj': 'Columbus Blue Jackets', 'blue jackets': 'Columbus Blue Jackets',
    'dal': 'Dallas Stars', 'stars': 'Dallas Stars',
    'det': 'Detroit Red Wings', 'red wings': 'Detroit Red Wings',
    'edm': 'Edmonton Oilers', 'oilers': 'Edmonton Oilers',
    'fla': 'Florida Panthers', 'panthers': 'Florida Panthers',
    'la': 'Los Angeles Kings', 'lak': 'Los Angeles Kings', 'kings': 'Los Angeles Kings',
    'min': 'Minnesota Wild', 'wild': 'Minnesota Wild',
    'mtl': 'Montreal Canadiens', 'canadiens': 'Montreal Canadiens', 'montreal canadiens': 'Montreal Canadiens', 'montréal canadiens': 'Montreal Canadiens',
    'nsh': 'Nashville Predators', 'predators': 'Nashville Predators',
    'nj': 'New Jersey Devils', 'njd': 'New Jersey Devils', 'devils': 'New Jersey Devils',
    'nyi': 'New York Islanders', 'islanders': 'New York Islanders',
    'nyr': 'New York Rangers', 'rangers': 'New York Rangers',
    'ott': 'Ottawa Senators', 'senators': 'Ottawa Senators',
    'phi': 'Philadelphia Flyers', 'flyers': 'Philadelphia Flyers',
    'pit': 'Pittsburgh Penguins', 'penguins': 'Pittsburgh Penguins',
    'sj': 'San Jose Sharks', 'sjs': 'San Jose Sharks', 'sharks': 'San Jose Sharks',
    'sea': 'Seattle Kraken', 'kraken': 'Seattle Kraken',
    'stl': 'St. Louis Blues', 'blues': 'St. Louis Blues', 'st louis blues': 'St. Louis Blues',
    'tb': 'Tampa Bay Lightning', 'tbl': 'Tampa Bay Lightning', 'lightning': 'Tampa Bay Lightning',
    'tor': 'Toronto Maple Leafs', 'maple leafs': 'Toronto Maple Leafs',
    'van': 'Vancouver Canucks', 'canucks': 'Vancouver Canucks',
    'vgk': 'Vegas Golden Knights', 'golden knights': 'Vegas Golden Knights',
    'wsh': 'Washington Capitals', 'capitals': 'Washington Capitals',
    'wpg': 'Winnipeg Jets', 'jets': 'Winnipeg Jets',
  },
  mlb: {
    'ari': 'Arizona Diamondbacks', 'diamondbacks': 'Arizona Diamondbacks', 'd-backs': 'Arizona Diamondbacks',
    'atl': 'Atlanta Braves', 'braves': 'Atlanta Braves',
    'bal': 'Baltimore Orioles', 'orioles': 'Baltimore Orioles',
    'bos': 'Boston Red Sox', 'red sox': 'Boston Red Sox',
    'chc': 'Chicago Cubs', 'cubs': 'Chicago Cubs',
    'chw': 'Chicago White Sox', 'cws': 'Chicago White Sox', 'white sox': 'Chicago White Sox',
    'cin': 'Cincinnati Reds', 'reds': 'Cincinnati Reds',
    'cle': 'Cleveland Guardians', 'guardians': 'Cleveland Guardians', 'indians': 'Cleveland Guardians',
    'col': 'Colorado Rockies', 'rockies': 'Colorado Rockies',
    'det': 'Detroit Tigers', 'tigers': 'Detroit Tigers',
    'hou': 'Houston Astros', 'astros': 'Houston Astros',
    'kc': 'Kansas City Royals', 'royals': 'Kansas City Royals',
    'laa': 'Los Angeles Angels', 'angels': 'Los Angeles Angels',
    'lad': 'Los Angeles Dodgers', 'dodgers': 'Los Angeles Dodgers',
    'mia': 'Miami Marlins', 'marlins': 'Miami Marlins',
    'mil': 'Milwaukee Brewers', 'brewers': 'Milwaukee Brewers',
    'min': 'Minnesota Twins', 'twins': 'Minnesota Twins',
    'nym': 'New York Mets', 'mets': 'New York Mets',
    'nyy': 'New York Yankees', 'yankees': 'New York Yankees',
    'oak': 'Oakland Athletics', 'athletics': 'Oakland Athletics', "a's": 'Oakland Athletics',
    'phi': 'Philadelphia Phillies', 'phillies': 'Philadelphia Phillies',
    'pit': 'Pittsburgh Pirates', 'pirates': 'Pittsburgh Pirates',
    'sd': 'San Diego Padres', 'padres': 'San Diego Padres',
    'sf': 'San Francisco Giants', 'giants': 'San Francisco Giants',
    'sea': 'Seattle Mariners', 'mariners': 'Seattle Mariners',
    'stl': 'St. Louis Cardinals', 'cardinals': 'St. Louis Cardinals',
    'tb': 'Tampa Bay Rays', 'rays': 'Tampa Bay Rays',
    'tex': 'Texas Rangers', 'rangers': 'Texas Rangers',
    'tor': 'Toronto Blue Jays', 'blue jays': 'Toronto Blue Jays',
    'wsh': 'Washington Nationals', 'nationals': 'Washington Nationals',
  },
  nfl: {
    'ari': 'Arizona Cardinals', 'cardinals': 'Arizona Cardinals',
    'atl': 'Atlanta Falcons', 'falcons': 'Atlanta Falcons',
    'bal': 'Baltimore Ravens', 'ravens': 'Baltimore Ravens',
    'buf': 'Buffalo Bills', 'bills': 'Buffalo Bills',
    'car': 'Carolina Panthers', 'panthers': 'Carolina Panthers',
    'chi': 'Chicago Bears', 'bears': 'Chicago Bears',
    'cin': 'Cincinnati Bengals', 'bengals': 'Cincinnati Bengals',
    'cle': 'Cleveland Browns', 'browns': 'Cleveland Browns',
    'dal': 'Dallas Cowboys', 'cowboys': 'Dallas Cowboys',
    'den': 'Denver Broncos', 'broncos': 'Denver Broncos',
    'det': 'Detroit Lions', 'lions': 'Detroit Lions',
    'gb': 'Green Bay Packers', 'packers': 'Green Bay Packers',
    'hou': 'Houston Texans', 'texans': 'Houston Texans',
    'ind': 'Indianapolis Colts', 'colts': 'Indianapolis Colts',
    'jax': 'Jacksonville Jaguars', 'jaguars': 'Jacksonville Jaguars',
    'kc': 'Kansas City Chiefs', 'chiefs': 'Kansas City Chiefs',
    'lv': 'Las Vegas Raiders', 'raiders': 'Las Vegas Raiders',
    'lac': 'Los Angeles Chargers', 'chargers': 'Los Angeles Chargers',
    'lar': 'Los Angeles Rams', 'rams': 'Los Angeles Rams',
    'mia': 'Miami Dolphins', 'dolphins': 'Miami Dolphins',
    'min': 'Minnesota Vikings', 'vikings': 'Minnesota Vikings',
    'ne': 'New England Patriots', 'patriots': 'New England Patriots',
    'no': 'New Orleans Saints', 'saints': 'New Orleans Saints',
    'nyg': 'New York Giants', 'giants': 'New York Giants',
    'nyj': 'New York Jets', 'jets': 'New York Jets',
    'phi': 'Philadelphia Eagles', 'eagles': 'Philadelphia Eagles',
    'pit': 'Pittsburgh Steelers', 'steelers': 'Pittsburgh Steelers',
    'sf': 'San Francisco 49ers', '49ers': 'San Francisco 49ers', 'niners': 'San Francisco 49ers',
    'sea': 'Seattle Seahawks', 'seahawks': 'Seattle Seahawks',
    'tb': 'Tampa Bay Buccaneers', 'buccaneers': 'Tampa Bay Buccaneers', 'bucs': 'Tampa Bay Buccaneers',
    'ten': 'Tennessee Titans', 'titans': 'Tennessee Titans',
    'was': 'Washington Commanders', 'commanders': 'Washington Commanders',
  },
};

// Create lowercase lookup sets for validation
const VALID_TEAMS_LOWER: Record<string, Set<string>> = {};
for (const [league, teams] of Object.entries(VALID_TEAMS)) {
  VALID_TEAMS_LOWER[league] = new Set(teams.map(t => t.toLowerCase()));
}

function normalizeTeamName(name: string, league: string): string | null {
  if (!name) return null;
  const lower = name.toLowerCase().trim();

  // Check if already canonical
  if (VALID_TEAMS_LOWER[league]?.has(lower)) {
    return VALID_TEAMS[league].find(t => t.toLowerCase() === lower) || null;
  }

  // Check aliases
  const aliases = TEAM_ALIASES[league];
  if (aliases && aliases[lower]) {
    return aliases[lower];
  }

  // Not a valid team for this league
  return null;
}

async function cleanupLeague(league: string) {
  const prisma = getSportsDb();

  console.log(`\n${'='.repeat(60)}`);
  console.log(`📊 Cleaning ${league.toUpperCase()}`);
  console.log('='.repeat(60));

  // Step 1: Get all unique team names
  const homeTeams = await prisma.sportsGame.findMany({
    where: { league },
    distinct: ['homeTeam'],
    select: { homeTeam: true },
  });
  const awayTeams = await prisma.sportsGame.findMany({
    where: { league },
    distinct: ['awayTeam'],
    select: { awayTeam: true },
  });
  const allTeams = new Set([
    ...homeTeams.map((g: any) => g.homeTeam),
    ...awayTeams.map((g: any) => g.awayTeam),
  ]);

  console.log(`  Found ${allTeams.size} unique team names`);

  // Step 2: Categorize teams
  const validTeams: string[] = [];
  const normalizableTeams: Map<string, string> = new Map(); // old -> new
  const invalidTeams: string[] = [];

  for (const team of allTeams) {
    const normalized = normalizeTeamName(team, league);
    if (normalized) {
      if (normalized === team) {
        validTeams.push(team);
      } else {
        normalizableTeams.set(team, normalized);
      }
    } else {
      invalidTeams.push(team);
    }
  }

  console.log(`  ✅ Valid: ${validTeams.length}`);
  console.log(`  🔄 Normalizable: ${normalizableTeams.size}`);
  console.log(`  ❌ Invalid (wrong sport/exhibition): ${invalidTeams.length}`);

  if (invalidTeams.length > 0) {
    console.log(`     Invalid teams: ${invalidTeams.slice(0, 10).join(', ')}${invalidTeams.length > 10 ? '...' : ''}`);
  }

  // Step 3: Delete games with invalid teams
  if (invalidTeams.length > 0) {
    console.log(`\n  Deleting games with invalid teams...`);
    let deleted = 0;
    for (const team of invalidTeams) {
      try {
        const count = await prisma.sportsGame.deleteMany({
          where: {
            league,
            OR: [
              { homeTeam: team },
              { awayTeam: team },
            ],
          },
        });
        deleted += count.count;
      } catch (e) {
        // ignore
      }
    }
    console.log(`  🗑️  Deleted ${deleted} games with invalid teams`);
  }

  // Step 4: Normalize team names
  if (normalizableTeams.size > 0) {
    console.log(`\n  Normalizing team names...`);
    let updated = 0;
    for (const [oldName, newName] of normalizableTeams) {
      try {
        const homeResult = await prisma.sportsGame.updateMany({
          where: { league, homeTeam: oldName },
          data: { homeTeam: newName },
        });
        const awayResult = await prisma.sportsGame.updateMany({
          where: { league, awayTeam: oldName },
          data: { awayTeam: newName },
        });
        updated += homeResult.count + awayResult.count;
      } catch (e) {
        // ignore
      }
    }
    console.log(`  ✏️  Updated ${updated} records`);
  }

  // Step 5: Final count
  const finalCount = await prisma.sportsGame.count({ where: { league } });
  const finalTeams = await prisma.sportsGame.findMany({
    where: { league },
    distinct: ['homeTeam'],
    select: { homeTeam: true },
  });

  console.log(`\n  📈 Final: ${finalCount.toLocaleString()} games, ${finalTeams.length} unique teams`);
}

async function main() {
  if (!isSportsDbEnabled()) {
    console.error('❌ SportsDB not enabled');
    process.exit(1);
  }

  console.log('🧹 Sports Data Cleanup Tool');
  console.log('='.repeat(60));

  const leagues = ['nba', 'nfl', 'nhl', 'mlb'];

  for (const league of leagues) {
    await cleanupLeague(league);
  }

  console.log('\n' + '='.repeat(60));
  console.log('✅ Cleanup complete!');
}

main().catch(console.error);