/**
 * Data Normalizer - Canonical mappings for teams, stats, and players
 * Solves inconsistency issues across all leagues
 */

// ============================================================
// TEAM NAME NORMALIZATION
// ============================================================

interface TeamCanonical {
  canonical: string;      // "Los Angeles Lakers"
  abbrev: string;         // "LAL"
  aliases: string[];      // ["LAKERS", "Lakers", "LA Lakers", "L.A. Lakers"]
}

const NBA_TEAMS: TeamCanonical[] = [
  { canonical: 'Los Angeles Lakers', abbrev: 'LAL', aliases: ['LAKERS', 'Lakers', 'LA Lakers', 'L.A. Lakers'] },
  { canonical: 'Boston Celtics', abbrev: 'BOS', aliases: ['CELTICS', 'Celtics'] },
  { canonical: 'Golden State Warriors', abbrev: 'GSW', aliases: ['WARRIORS', 'Warriors', 'Golden State', 'GS Warriors'] },
  { canonical: 'Brooklyn Nets', abbrev: 'BKN', aliases: ['NETS', 'Nets', 'Brooklyn'] },
  { canonical: 'New York Knicks', abbrev: 'NYK', aliases: ['KNICKS', 'Knicks', 'NY Knicks'] },
  { canonical: 'Miami Heat', abbrev: 'MIA', aliases: ['HEAT', 'Heat'] },
  { canonical: 'Chicago Bulls', abbrev: 'CHI', aliases: ['BULLS', 'Bulls'] },
  { canonical: 'Cleveland Cavaliers', abbrev: 'CLE', aliases: ['CAVALIERS', 'Cavaliers', 'Cavs', 'CAVS'] },
  { canonical: 'Dallas Mavericks', abbrev: 'DAL', aliases: ['MAVERICKS', 'Mavericks', 'Mavs', 'MAVS'] },
  { canonical: 'Denver Nuggets', abbrev: 'DEN', aliases: ['NUGGETS', 'Nuggets'] },
  { canonical: 'Philadelphia 76ers', abbrev: 'PHI', aliases: ['76ERS', '76ers', 'Sixers', 'SIXERS'] },
  { canonical: 'Phoenix Suns', abbrev: 'PHX', aliases: ['SUNS', 'Suns'] },
  { canonical: 'Milwaukee Bucks', abbrev: 'MIL', aliases: ['BUCKS', 'Bucks'] },
  { canonical: 'Minnesota Timberwolves', abbrev: 'MIN', aliases: ['TIMBERWOLVES', 'Timberwolves', 'Wolves', 'T-Wolves'] },
  { canonical: 'Oklahoma City Thunder', abbrev: 'OKC', aliases: ['THUNDER', 'Thunder', 'OKC Thunder'] },
  { canonical: 'Sacramento Kings', abbrev: 'SAC', aliases: ['KINGS', 'Kings'] },
  { canonical: 'Los Angeles Clippers', abbrev: 'LAC', aliases: ['CLIPPERS', 'Clippers', 'LA Clippers'] },
  { canonical: 'Toronto Raptors', abbrev: 'TOR', aliases: ['RAPTORS', 'Raptors'] },
  { canonical: 'Indiana Pacers', abbrev: 'IND', aliases: ['PACERS', 'Pacers'] },
  { canonical: 'Atlanta Hawks', abbrev: 'ATL', aliases: ['HAWKS', 'Hawks'] },
  { canonical: 'Houston Rockets', abbrev: 'HOU', aliases: ['ROCKETS', 'Rockets'] },
  { canonical: 'Memphis Grizzlies', abbrev: 'MEM', aliases: ['GRIZZLIES', 'Grizzlies', 'Grizz'] },
  { canonical: 'New Orleans Pelicans', abbrev: 'NOP', aliases: ['PELICANS', 'Pelicans', 'Pels'] },
  { canonical: 'Orlando Magic', abbrev: 'ORL', aliases: ['MAGIC', 'Magic'] },
  { canonical: 'Portland Trail Blazers', abbrev: 'POR', aliases: ['TRAIL BLAZERS', 'Trail Blazers', 'Blazers', 'BLAZERS'] },
  { canonical: 'San Antonio Spurs', abbrev: 'SAS', aliases: ['SPURS', 'Spurs'] },
  { canonical: 'Utah Jazz', abbrev: 'UTA', aliases: ['JAZZ', 'Jazz'] },
  { canonical: 'Washington Wizards', abbrev: 'WAS', aliases: ['WIZARDS', 'Wizards'] },
  { canonical: 'Charlotte Hornets', abbrev: 'CHA', aliases: ['HORNETS', 'Hornets'] },
  { canonical: 'Detroit Pistons', abbrev: 'DET', aliases: ['PISTONS', 'Pistons'] },
];

const NFL_TEAMS: TeamCanonical[] = [
  { canonical: 'Kansas City Chiefs', abbrev: 'KC', aliases: ['CHIEFS', 'Chiefs', 'Kansas City'] },
  { canonical: 'Philadelphia Eagles', abbrev: 'PHI', aliases: ['EAGLES', 'Eagles', 'Philly'] },
  { canonical: 'San Francisco 49ers', abbrev: 'SF', aliases: ['49ERS', '49ers', 'Niners', 'NINERS'] },
  { canonical: 'Dallas Cowboys', abbrev: 'DAL', aliases: ['COWBOYS', 'Cowboys'] },
  { canonical: 'Buffalo Bills', abbrev: 'BUF', aliases: ['BILLS', 'Bills'] },
  { canonical: 'Miami Dolphins', abbrev: 'MIA', aliases: ['DOLPHINS', 'Dolphins'] },
  { canonical: 'Detroit Lions', abbrev: 'DET', aliases: ['LIONS', 'Lions'] },
  { canonical: 'Baltimore Ravens', abbrev: 'BAL', aliases: ['RAVENS', 'Ravens'] },
  { canonical: 'Cincinnati Bengals', abbrev: 'CIN', aliases: ['BENGALS', 'Bengals'] },
  { canonical: 'New York Jets', abbrev: 'NYJ', aliases: ['JETS', 'Jets', 'NY Jets'] },
  { canonical: 'New York Giants', abbrev: 'NYG', aliases: ['GIANTS', 'Giants', 'NY Giants'] },
  { canonical: 'Green Bay Packers', abbrev: 'GB', aliases: ['PACKERS', 'Packers'] },
  { canonical: 'Los Angeles Rams', abbrev: 'LAR', aliases: ['RAMS', 'Rams', 'LA Rams'] },
  { canonical: 'Los Angeles Chargers', abbrev: 'LAC', aliases: ['CHARGERS', 'Chargers', 'LA Chargers'] },
  { canonical: 'Las Vegas Raiders', abbrev: 'LV', aliases: ['RAIDERS', 'Raiders'] },
  { canonical: 'Denver Broncos', abbrev: 'DEN', aliases: ['BRONCOS', 'Broncos'] },
  { canonical: 'Seattle Seahawks', abbrev: 'SEA', aliases: ['SEAHAWKS', 'Seahawks'] },
  { canonical: 'Arizona Cardinals', abbrev: 'ARI', aliases: ['CARDINALS', 'Cardinals'] },
  { canonical: 'Minnesota Vikings', abbrev: 'MIN', aliases: ['VIKINGS', 'Vikings'] },
  { canonical: 'Chicago Bears', abbrev: 'CHI', aliases: ['BEARS', 'Bears'] },
  { canonical: 'New England Patriots', abbrev: 'NE', aliases: ['PATRIOTS', 'Patriots', 'Pats'] },
  { canonical: 'Pittsburgh Steelers', abbrev: 'PIT', aliases: ['STEELERS', 'Steelers'] },
  { canonical: 'Cleveland Browns', abbrev: 'CLE', aliases: ['BROWNS', 'Browns'] },
  { canonical: 'Indianapolis Colts', abbrev: 'IND', aliases: ['COLTS', 'Colts'] },
  { canonical: 'Jacksonville Jaguars', abbrev: 'JAX', aliases: ['JAGUARS', 'Jaguars', 'Jags'] },
  { canonical: 'Tennessee Titans', abbrev: 'TEN', aliases: ['TITANS', 'Titans'] },
  { canonical: 'Houston Texans', abbrev: 'HOU', aliases: ['TEXANS', 'Texans'] },
  { canonical: 'Atlanta Falcons', abbrev: 'ATL', aliases: ['FALCONS', 'Falcons'] },
  { canonical: 'Carolina Panthers', abbrev: 'CAR', aliases: ['PANTHERS', 'Panthers'] },
  { canonical: 'New Orleans Saints', abbrev: 'NO', aliases: ['SAINTS', 'Saints'] },
  { canonical: 'Tampa Bay Buccaneers', abbrev: 'TB', aliases: ['BUCCANEERS', 'Buccaneers', 'Bucs'] },
  { canonical: 'Washington Commanders', abbrev: 'WAS', aliases: ['COMMANDERS', 'Commanders'] },
];

// Build lookup maps
const TEAM_MAPS: Record<string, Map<string, TeamCanonical>> = {};

function buildTeamMap(teams: TeamCanonical[]): Map<string, TeamCanonical> {
  const map = new Map<string, TeamCanonical>();
  for (const team of teams) {
    // Add canonical name
    map.set(team.canonical.toLowerCase(), team);
    map.set(team.abbrev.toLowerCase(), team);
    // Add all aliases
    for (const alias of team.aliases) {
      map.set(alias.toLowerCase(), team);
    }
  }
  return map;
}

TEAM_MAPS['nba'] = buildTeamMap(NBA_TEAMS);
TEAM_MAPS['nfl'] = buildTeamMap(NFL_TEAMS);

// ============================================================
// STAT KEY NORMALIZATION
// ============================================================

interface StatMapping {
  canonical: string;      // Standard key we use
  aliases: string[];      // All variations
}

const STAT_MAPPINGS: Record<string, StatMapping[]> = {
  nba: [
    { canonical: 'points', aliases: ['pts', 'PTS', 'Points', 'POINTS', 'espn_pts', 'nba_pts'] },
    { canonical: 'rebounds', aliases: ['reb', 'REB', 'Rebounds', 'REBOUNDS', 'espn_reb', 'totalRebounds'] },
    { canonical: 'assists', aliases: ['ast', 'AST', 'Assists', 'ASSISTS', 'espn_ast'] },
    { canonical: 'threePointersMade', aliases: ['threes', '3PM', '3pm', 'fg3m', 'FG3M', 'nba_fg3m', 'threesMade'] },
    { canonical: 'steals', aliases: ['stl', 'STL', 'Steals', 'STEALS', 'espn_stl'] },
    { canonical: 'blocks', aliases: ['blk', 'BLK', 'Blocks', 'BLOCKS', 'espn_blk'] },
    { canonical: 'turnovers', aliases: ['to', 'TO', 'Turnovers', 'TURNOVERS', 'espn_to'] },
    { canonical: 'minutes', aliases: ['min', 'MIN', 'Minutes', 'MINUTES', 'espn_min', 'nba_min'] },
    { canonical: 'fieldGoalsMade', aliases: ['fgm', 'FGM', 'nba_fgm'] },
    { canonical: 'freeThrowsMade', aliases: ['ftm', 'FTM', 'nba_ftm'] },
  ],
  nfl: [
    { canonical: 'passingYards', aliases: ['pass_yds', 'PASS_YDS', 'passYds', 'passing_yards'] },
    { canonical: 'passingTouchdowns', aliases: ['pass_td', 'PASS_TD', 'passTD', 'passing_touchdowns'] },
    { canonical: 'rushingYards', aliases: ['rush_yds', 'RUSH_YDS', 'rushYds', 'rushing_yards'] },
    { canonical: 'rushingTouchdowns', aliases: ['rush_td', 'RUSH_TD', 'rushTD', 'rushing_touchdowns'] },
    { canonical: 'receivingYards', aliases: ['rec_yds', 'REC_YDS', 'recYds', 'receiving_yards'] },
    { canonical: 'receptions', aliases: ['rec', 'REC', 'catches', 'CATCHES'] },
    { canonical: 'interceptions', aliases: ['int', 'INT', 'ints', 'INTS'] },
    { canonical: 'completions', aliases: ['comp', 'COMP', 'pass_comp'] },
    { canonical: 'attempts', aliases: ['att', 'ATT', 'pass_att', 'rush_att'] },
  ],
  nhl: [
    { canonical: 'goals', aliases: ['g', 'G', 'Goals', 'GOALS'] },
    { canonical: 'assists', aliases: ['a', 'A', 'Assists', 'ASSISTS'] },
    { canonical: 'points', aliases: ['pts', 'PTS', 'Points', 'POINTS'] },
    { canonical: 'shots', aliases: ['sog', 'SOG', 'shotsOnGoal', 'Shots'] },
    { canonical: 'saves', aliases: ['sv', 'SV', 'Saves', 'SAVES'] },
    { canonical: 'goalsAgainst', aliases: ['ga', 'GA', 'goals_against'] },
  ],
  mlb: [
    { canonical: 'hits', aliases: ['h', 'H', 'Hits', 'HITS'] },
    { canonical: 'runs', aliases: ['r', 'R', 'Runs', 'RUNS'] },
    { canonical: 'rbi', aliases: ['RBI', 'rbis', 'RBIS', 'runsBattedIn'] },
    { canonical: 'homeRuns', aliases: ['hr', 'HR', 'homers', 'HOMERS'] },
    { canonical: 'strikeouts', aliases: ['so', 'SO', 'k', 'K', 'Ks'] },
    { canonical: 'walks', aliases: ['bb', 'BB', 'baseOnBalls'] },
    { canonical: 'stolenBases', aliases: ['sb', 'SB', 'steals'] },
    { canonical: 'innings', aliases: ['ip', 'IP', 'inningsPitched'] },
    { canonical: 'earnedRuns', aliases: ['er', 'ER', 'earned_runs'] },
  ],
};

// Build stat lookup maps
const STAT_MAPS: Record<string, Map<string, string>> = {};

for (const [league, mappings] of Object.entries(STAT_MAPPINGS)) {
  const map = new Map<string, string>();
  for (const stat of mappings) {
    map.set(stat.canonical.toLowerCase(), stat.canonical);
    for (const alias of stat.aliases) {
      map.set(alias.toLowerCase(), stat.canonical);
    }
  }
  STAT_MAPS[league] = map;
}

// ============================================================
// EXPORTED FUNCTIONS
// ============================================================

/**
 * Normalize a team name to its canonical form
 */
export function normalizeTeamName(name: string, league: string): string {
  const map = TEAM_MAPS[league.toLowerCase()];
  if (!map) return name;
  
  const team = map.get(name.toLowerCase());
  return team?.canonical || name;
}

/**
 * Get team abbreviation
 */
export function getTeamAbbrev(name: string, league: string): string | null {
  const map = TEAM_MAPS[league.toLowerCase()];
  if (!map) return null;
  
  const team = map.get(name.toLowerCase());
  return team?.abbrev || null;
}

/**
 * Normalize a stat key to its canonical form
 */
export function normalizeStatKey(key: string, league: string): string {
  const map = STAT_MAPS[league.toLowerCase()];
  if (!map) return key;
  
  return map.get(key.toLowerCase()) || key;
}

/**
 * Build a query condition that matches all team name variations
 */
export function buildTeamQuery(teamName: string, league: string): string[] {
  const map = TEAM_MAPS[league.toLowerCase()];
  if (!map) return [teamName];
  
  const team = map.get(teamName.toLowerCase());
  if (!team) return [teamName];
  
  // Return canonical + all aliases for OR query
  return [team.canonical, ...team.aliases];
}

/**
 * Build a query condition that matches all stat key variations
 */
export function buildStatQuery(statKey: string, league: string): string[] {
  const mappings = STAT_MAPPINGS[league.toLowerCase()];
  if (!mappings) return [statKey];
  
  const stat = mappings.find(m => 
    m.canonical.toLowerCase() === statKey.toLowerCase() ||
    m.aliases.some(a => a.toLowerCase() === statKey.toLowerCase())
  );
  
  if (!stat) return [statKey];
  return [stat.canonical, ...stat.aliases];
}

// ============================================================
// DATA MIGRATION HELPERS
// ============================================================

/**
 * Generate SQL to normalize team names in sportsGame table
 */
export function generateTeamNormalizationSQL(league: string): string[] {
  const teams = league === 'nba' ? NBA_TEAMS : NFL_TEAMS;
  const sqls: string[] = [];
  
  for (const team of teams) {
    for (const alias of team.aliases) {
      sqls.push(`UPDATE "SportsGame" SET "homeTeam" = '${team.canonical}' WHERE "homeTeam" = '${alias}' AND "league" = '${league}';`);
      sqls.push(`UPDATE "SportsGame" SET "awayTeam" = '${team.canonical}' WHERE "awayTeam" = '${alias}' AND "league" = '${league}';`);
    }
  }
  
  return sqls;
}

export { NBA_TEAMS, NFL_TEAMS, STAT_MAPPINGS };