#!/usr/bin/env npx tsx
/**
 * Normalize All Leagues - Apply data normalization across NBA, NFL, NHL, MLB
 * 
 * Algorithm:
 * 1. Deduplicate games (keep canonical names, delete abbreviated versions)
 * 2. Normalize team names (abbreviations → full names)
 * 3. Normalize stat keys (aliases → canonical)
 * 
 * Usage:
 *   npx tsx src/scripts/normalizeAllLeagues.ts --league=nba
 *   npx tsx src/scripts/normalizeAllLeagues.ts --all
 */

require('dotenv').config({ path: '.env.local' });
require('dotenv').config({ path: '.env' });

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

// Team mappings by league: [abbreviation, canonical name]
const TEAM_MAPS: Record<string, [string, string][]> = {
  nba: [
    ['LAL', 'Los Angeles Lakers'], ['BOS', 'Boston Celtics'], ['GSW', 'Golden State Warriors'],
    ['BKN', 'Brooklyn Nets'], ['NYK', 'New York Knicks'], ['MIA', 'Miami Heat'],
    ['CHI', 'Chicago Bulls'], ['CLE', 'Cleveland Cavaliers'], ['DAL', 'Dallas Mavericks'],
    ['DEN', 'Denver Nuggets'], ['PHI', 'Philadelphia 76ers'], ['PHX', 'Phoenix Suns'],
    ['MIL', 'Milwaukee Bucks'], ['MIN', 'Minnesota Timberwolves'], ['OKC', 'Oklahoma City Thunder'],
    ['SAC', 'Sacramento Kings'], ['LAC', 'Los Angeles Clippers'], ['TOR', 'Toronto Raptors'],
    ['IND', 'Indiana Pacers'], ['ATL', 'Atlanta Hawks'], ['HOU', 'Houston Rockets'],
    ['MEM', 'Memphis Grizzlies'], ['NOP', 'New Orleans Pelicans'], ['ORL', 'Orlando Magic'],
    ['POR', 'Portland Trail Blazers'], ['SAS', 'San Antonio Spurs'], ['UTA', 'Utah Jazz'],
    ['WAS', 'Washington Wizards'], ['CHA', 'Charlotte Hornets'], ['DET', 'Detroit Pistons'],
  ],
  nfl: [
    ['KC', 'Kansas City Chiefs'], ['PHI', 'Philadelphia Eagles'], ['SF', 'San Francisco 49ers'],
    ['DAL', 'Dallas Cowboys'], ['BUF', 'Buffalo Bills'], ['MIA', 'Miami Dolphins'],
    ['DET', 'Detroit Lions'], ['BAL', 'Baltimore Ravens'], ['CIN', 'Cincinnati Bengals'],
    ['NYJ', 'New York Jets'], ['NYG', 'New York Giants'], ['GB', 'Green Bay Packers'],
    ['LAR', 'Los Angeles Rams'], ['LAC', 'Los Angeles Chargers'], ['LV', 'Las Vegas Raiders'],
    ['DEN', 'Denver Broncos'], ['SEA', 'Seattle Seahawks'], ['ARI', 'Arizona Cardinals'],
    ['MIN', 'Minnesota Vikings'], ['CHI', 'Chicago Bears'], ['NE', 'New England Patriots'],
    ['PIT', 'Pittsburgh Steelers'], ['CLE', 'Cleveland Browns'], ['IND', 'Indianapolis Colts'],
    ['JAX', 'Jacksonville Jaguars'], ['TEN', 'Tennessee Titans'], ['HOU', 'Houston Texans'],
    ['ATL', 'Atlanta Falcons'], ['CAR', 'Carolina Panthers'], ['NO', 'New Orleans Saints'],
    ['TB', 'Tampa Bay Buccaneers'], ['WAS', 'Washington Commanders'],
  ],
  nhl: [
    ['ANA', 'Anaheim Ducks'], ['ARI', 'Arizona Coyotes'], ['BOS', 'Boston Bruins'],
    ['BUF', 'Buffalo Sabres'], ['CGY', 'Calgary Flames'], ['CAR', 'Carolina Hurricanes'],
    ['CHI', 'Chicago Blackhawks'], ['COL', 'Colorado Avalanche'], ['CBJ', 'Columbus Blue Jackets'],
    ['DAL', 'Dallas Stars'], ['DET', 'Detroit Red Wings'], ['EDM', 'Edmonton Oilers'],
    ['FLA', 'Florida Panthers'], ['LA', 'Los Angeles Kings'], ['MIN', 'Minnesota Wild'],
    ['MTL', 'Montreal Canadiens'], ['NSH', 'Nashville Predators'], ['NJ', 'New Jersey Devils'],
    ['NYI', 'New York Islanders'], ['NYR', 'New York Rangers'], ['OTT', 'Ottawa Senators'],
    ['PHI', 'Philadelphia Flyers'], ['PIT', 'Pittsburgh Penguins'], ['SJ', 'San Jose Sharks'],
    ['SEA', 'Seattle Kraken'], ['STL', 'St. Louis Blues'], ['TB', 'Tampa Bay Lightning'],
    ['TOR', 'Toronto Maple Leafs'], ['VAN', 'Vancouver Canucks'], ['VGK', 'Vegas Golden Knights'],
    ['WSH', 'Washington Capitals'], ['WPG', 'Winnipeg Jets'],
  ],
  mlb: [
    ['ARI', 'Arizona Diamondbacks'], ['ATL', 'Atlanta Braves'], ['BAL', 'Baltimore Orioles'],
    ['BOS', 'Boston Red Sox'], ['CHC', 'Chicago Cubs'], ['CWS', 'Chicago White Sox'],
    ['CIN', 'Cincinnati Reds'], ['CLE', 'Cleveland Guardians'], ['COL', 'Colorado Rockies'],
    ['DET', 'Detroit Tigers'], ['HOU', 'Houston Astros'], ['KC', 'Kansas City Royals'],
    ['LAA', 'Los Angeles Angels'], ['LAD', 'Los Angeles Dodgers'], ['MIA', 'Miami Marlins'],
    ['MIL', 'Milwaukee Brewers'], ['MIN', 'Minnesota Twins'], ['NYM', 'New York Mets'],
    ['NYY', 'New York Yankees'], ['OAK', 'Oakland Athletics'], ['PHI', 'Philadelphia Phillies'],
    ['PIT', 'Pittsburgh Pirates'], ['SD', 'San Diego Padres'], ['SF', 'San Francisco Giants'],
    ['SEA', 'Seattle Mariners'], ['STL', 'St. Louis Cardinals'], ['TB', 'Tampa Bay Rays'],
    ['TEX', 'Texas Rangers'], ['TOR', 'Toronto Blue Jays'], ['WSH', 'Washington Nationals'],
  ],
};

// Stat key normalization by league
const STAT_MAPS: Record<string, [string, string][]> = {
  nba: [
    ['threes', 'threePointersMade'],
    ['fg3m', 'threePointersMade'],
    ['3PM', 'threePointersMade'],
  ],
  nfl: [],
  nhl: [],
  mlb: [],
};

async function normalizeLeague(league: string) {
  const prisma = getSportsDb();
  const teamMap = TEAM_MAPS[league] || [];
  const statMap = STAT_MAPS[league] || [];
  
  console.log(`\n📊 Normalizing ${league.toUpperCase()}...`);
  
  // Get abbreviations list for dedup query
  const abbrevs = teamMap.map(([a]) => a);
  const abbrevsStr = abbrevs.map(a => `'${a}'`).join(',');
  
  // Step 1: Deduplicate
  console.log('  Step 1: Deduplicating games...');
  try {
    const deleted = await prisma.$executeRawUnsafe(`
      DELETE FROM "SportsGame" g1
      WHERE g1.league = '${league}'
      AND EXISTS (
        SELECT 1 FROM "SportsGame" g2
        WHERE g2.league = g1.league
        AND g2.season = g1.season
        AND g2."gameDate" = g1."gameDate"
        AND g2.id != g1.id
        AND (
          (g1."homeTeam" IN (${abbrevsStr}) AND g2."homeTeam" NOT IN (${abbrevsStr}))
          OR (g1."awayTeam" IN (${abbrevsStr}) AND g2."awayTeam" NOT IN (${abbrevsStr}))
        )
      )
    `);
    console.log(`    Removed ${deleted} duplicates`);
  } catch (e: any) {
    console.log(`    Dedup error: ${e.message.slice(0, 50)}`);
  }
  
  // Step 2: Normalize team names
  console.log('  Step 2: Normalizing team names...');
  let teamUpdates = 0;
  for (const [abbrev, canonical] of teamMap) {
    try {
      const home = await prisma.$executeRawUnsafe(
        `UPDATE "SportsGame" SET "homeTeam" = $1 WHERE "homeTeam" = $2 AND league = $3`,
        canonical, abbrev, league
      );
      const away = await prisma.$executeRawUnsafe(
        `UPDATE "SportsGame" SET "awayTeam" = $1 WHERE "awayTeam" = $2 AND league = $3`,
        canonical, abbrev, league
      );
      teamUpdates += home + away;
    } catch {}
  }
  console.log(`    Updated ${teamUpdates} team records`);
  
  // Step 3: Normalize stat keys
  if (statMap.length > 0) {
    console.log('  Step 3: Normalizing stat keys...');
    let statUpdates = 0;
    for (const [alias, canonical] of statMap) {
      try {
        const count = await prisma.$executeRawUnsafe(
          `UPDATE "PlayerGameMetric" SET "statKey" = $1 WHERE "statKey" = $2 AND league = $3`,
          canonical, alias, league
        );
        statUpdates += count;
      } catch {}
    }
    console.log(`    Updated ${statUpdates} stat records`);
  }
  
  // Summary
  const teams = await prisma.sportsGame.findMany({
    where: { league },
    distinct: ['homeTeam'],
    select: { homeTeam: true }
  });
  console.log(`  ✅ ${teams.length} unique team names`);
}

async function main() {
  const args = process.argv.slice(2);
  const allLeagues = args.includes('--all');
  const leagueArg = args.find(a => a.startsWith('--league='));
  const league = leagueArg?.split('=')[1]?.toLowerCase();

  console.log('🔧 Multi-League Data Normalization');
  console.log('='.repeat(50));

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

  const leagues = allLeagues ? ['nba', 'nfl', 'nhl', 'mlb'] : (league ? [league] : []);

  if (leagues.length === 0) {
    console.log('\nUsage:');
    console.log('  npx tsx src/scripts/normalizeAllLeagues.ts --league=nba');
    console.log('  npx tsx src/scripts/normalizeAllLeagues.ts --all');
    process.exit(0);
  }

  for (const l of leagues) {
    await normalizeLeague(l);
  }

  console.log('\n✅ Done!');
}

main().catch(console.error);