/**
 * team-abbreviations.ts
 *
 * Canonical team abbreviation map — sportsbook-standard callsigns.
 * RULE: All team display in graded forecasts uses these abbreviations.
 * Player names remain full-spelled.
 *
 * Architecture:
 *   1. DB cache (loaded once from rm_events.home_short / away_short) — covers ALL teams
 *   2. Static TEAM_ABBR map — fallback for pro leagues + soccer
 *   3. Last-word nickname lookup — final fallback
 *
 * Format: Full team name → 2-4 letter abbreviation
 * Matching is case-insensitive and checks both full name and nickname (last word).
 */

import pool from '../db';

const TEAM_ABBR: Record<string, string> = {
  // ─── NBA ───
  'Atlanta Hawks': 'ATL', 'Boston Celtics': 'BOS', 'Brooklyn Nets': 'BKN',
  'Charlotte Hornets': 'CHA', 'Chicago Bulls': 'CHI', 'Cleveland Cavaliers': 'CLE',
  'Dallas Mavericks': 'DAL', 'Denver Nuggets': 'DEN', 'Detroit Pistons': 'DET',
  'Golden State Warriors': 'GSW', 'Houston Rockets': 'HOU', 'Indiana Pacers': 'IND',
  'LA Clippers': 'LAC', 'Los Angeles Clippers': 'LAC',
  'Los Angeles Lakers': 'LAL', 'LA Lakers': 'LAL',
  'Memphis Grizzlies': 'MEM', 'Miami Heat': 'MIA', 'Milwaukee Bucks': 'MIL',
  'Minnesota Timberwolves': 'MIN', 'New Orleans Pelicans': 'NOP',
  'New York Knicks': 'NYK', 'Oklahoma City Thunder': 'OKC',
  'Orlando Magic': 'ORL', 'Philadelphia 76ers': 'PHI',
  'Phoenix Suns': 'PHX', 'Portland Trail Blazers': 'POR',
  'Sacramento Kings': 'SAC', 'San Antonio Spurs': 'SAS',
  'Toronto Raptors': 'TOR', 'Utah Jazz': 'UTA', 'Washington Wizards': 'WAS',

  // ─── NFL ───
  'Arizona Cardinals': 'ARI', 'Atlanta Falcons': 'ATL', 'Baltimore Ravens': 'BAL',
  'Buffalo Bills': 'BUF', 'Carolina Panthers': 'CAR', 'Chicago Bears': 'CHI',
  'Cincinnati Bengals': 'CIN', 'Cleveland Browns': 'CLE', 'Dallas Cowboys': 'DAL',
  'Denver Broncos': 'DEN', 'Detroit Lions': 'DET', 'Green Bay Packers': 'GB',
  'Houston Texans': 'HOU', 'Indianapolis Colts': 'IND',
  'Jacksonville Jaguars': 'JAX', 'Kansas City Chiefs': 'KC',
  'Las Vegas Raiders': 'LV', 'Los Angeles Chargers': 'LAC',
  'Los Angeles Rams': 'LAR', 'Miami Dolphins': 'MIA',
  'Minnesota Vikings': 'MIN', 'New England Patriots': 'NE',
  'New Orleans Saints': 'NO', 'New York Giants': 'NYG', 'New York Jets': 'NYJ',
  'Philadelphia Eagles': 'PHI', 'Pittsburgh Steelers': 'PIT',
  'San Francisco 49ers': 'SF', 'Seattle Seahawks': 'SEA',
  'Tampa Bay Buccaneers': 'TB', 'Tennessee Titans': 'TEN',
  'Washington Commanders': 'WAS',

  // ─── NHL (sportsbook-standard codes from events feed) ───
  'Anaheim Ducks': 'ANA', 'Arizona Coyotes': 'ARI', 'Boston Bruins': 'BOS',
  'Buffalo Sabres': 'BUF', 'Calgary Flames': 'CGY', 'Carolina Hurricanes': 'CAR',
  'Chicago Blackhawks': 'CHI', 'Colorado Avalanche': 'COL',
  'Columbus Blue Jackets': 'CBJ', 'Dallas Stars': 'DAL',
  'Detroit Red Wings': 'DET', 'Edmonton Oilers': 'EDM',
  'Florida Panthers': 'FLA', 'Los Angeles Kings': 'LA',
  'Minnesota Wild': 'MIN', 'Montreal Canadiens': 'MTL', 'Montréal Canadiens': 'MTL',
  'Nashville Predators': 'NSH', 'New Jersey Devils': 'NJ',
  'New York Islanders': 'NYI', 'New York Rangers': 'NYR',
  'Ottawa Senators': 'OTT', 'Philadelphia Flyers': 'PHI',
  'Pittsburgh Penguins': 'PIT', 'San Jose Sharks': 'SJ',
  'Seattle Kraken': 'SEA', 'St. Louis Blues': 'STL',
  'Tampa Bay Lightning': 'TB', 'Toronto Maple Leafs': 'TOR',
  'Utah Hockey Club': 'UTA', 'Utah Mammoth': 'UTA',
  'Vancouver Canucks': 'VAN', 'Vegas Golden Knights': 'VGK',
  'Washington Capitals': 'WSH', 'Winnipeg Jets': 'WPG',

  // ─── MLB ───
  'Arizona Diamondbacks': 'ARI', 'Atlanta Braves': 'ATL',
  'Baltimore Orioles': 'BAL', 'Boston Red Sox': 'BOS',
  'Chicago Cubs': 'CHC', 'Chicago White Sox': 'CWS',
  'Cincinnati Reds': 'CIN', 'Cleveland Guardians': 'CLE',
  'Colorado Rockies': 'COL', 'Detroit Tigers': 'DET',
  'Houston Astros': 'HOU', 'Kansas City Royals': 'KC',
  'Los Angeles Angels': 'LAA', 'Los Angeles Dodgers': 'LAD',
  'Miami Marlins': 'MIA', 'Milwaukee Brewers': 'MIL',
  'Minnesota Twins': 'MIN', 'New York Mets': 'NYM', 'New York Yankees': 'NYY',
  'Athletics': 'OAK', 'Oakland Athletics': 'OAK',
  'Philadelphia Phillies': 'PHI', 'Pittsburgh Pirates': 'PIT',
  'San Diego Padres': 'SD', 'San Francisco Giants': 'SF',
  'Seattle Mariners': 'SEA', 'St. Louis Cardinals': 'STL',
  'Tampa Bay Rays': 'TB', 'Texas Rangers': 'TEX',
  'Toronto Blue Jays': 'TOR', 'Washington Nationals': 'WSH',

  // ─── EPL ───
  'Arsenal': 'ARS', 'Aston Villa': 'AVL', 'Bournemouth': 'BOU',
  'Brentford': 'BRE', 'Brighton': 'BHA', 'Brighton and Hove Albion': 'BHA',
  'Chelsea': 'CHE', 'Crystal Palace': 'CRY', 'Everton': 'EVE',
  'Fulham': 'FUL', 'Ipswich Town': 'IPS', 'Leicester City': 'LEI',
  'Liverpool': 'LIV', 'Manchester City': 'MCI', 'Manchester United': 'MUN',
  'Newcastle United': 'NEW', 'Nottingham Forest': 'NFO',
  'Southampton': 'SOU', 'Tottenham': 'TOT', 'Tottenham Hotspur': 'TOT',
  'West Ham': 'WHU', 'West Ham United': 'WHU', 'Wolverhampton': 'WOL',
  'Wolverhampton Wanderers': 'WOL', 'Wolves': 'WOL',

  // ─── La Liga ───
  'Athletic Bilbao': 'ATH', 'Atletico Madrid': 'ATM', 'Barcelona': 'BAR',
  'Celta Vigo': 'CEL', 'Espanyol': 'ESP', 'Getafe': 'GET',
  'Girona': 'GIR', 'Las Palmas': 'LPA', 'Leganes': 'LEG',
  'Mallorca': 'MLL', 'Osasuna': 'OSA', 'Rayo Vallecano': 'RAY',
  'Real Betis': 'BET', 'Real Madrid': 'RMA', 'Real Sociedad': 'RSO',
  'Real Valladolid': 'VLL', 'Sevilla': 'SEV', 'Valencia': 'VAL',
  'Villarreal': 'VIL', 'Alavés': 'ALA', 'Real Oviedo': 'OVI',

  // ─── Serie A ───
  'AC Milan': 'MIL', 'Atalanta': 'ATA', 'Bologna': 'BOL',
  'Cagliari': 'CAG', 'Como': 'COM', 'Cremonese': 'CRE',
  'Empoli': 'EMP', 'Fiorentina': 'FIO', 'Genoa': 'GEN',
  'Hellas Verona': 'VER', 'Inter': 'INT', 'Inter Milan': 'INT',
  'Juventus': 'JUV', 'Lazio': 'LAZ', 'Lecce': 'LEC',
  'Monza': 'MON', 'Napoli': 'NAP', 'Parma': 'PAR',
  'Roma': 'ROM', 'Salernitana': 'SAL', 'Sampdoria': 'SAM',
  'Sassuolo': 'SAS', 'Torino': 'TOR', 'Udinese': 'UDI', 'Venezia': 'VEN',

  // ─── Bundesliga ───
  'Augsburg': 'AUG', 'Bayer Leverkusen': 'LEV', 'Bayern Munich': 'BAY',
  'Borussia Dortmund': 'BVB', 'Dortmund': 'BVB',
  'Borussia Monchengladbach': 'BMG', 'Eintracht Frankfurt': 'SGE', 'Frankfurt': 'SGE',
  'Freiburg': 'FRE', 'Heidenheim': 'HDH', 'Hoffenheim': 'HOF',
  'Holstein Kiel': 'KIE', 'Mainz': 'MAI', 'RB Leipzig': 'RBL', 'Leipzig': 'RBL',
  'St. Pauli': 'STP', 'Stuttgart': 'VFB', 'Union Berlin': 'UNB', 'Berlin': 'UNB',
  'Werder Bremen': 'SVW', 'Bremen': 'SVW', 'Wolfsburg': 'WOB',

  // ─── Ligue 1 ───
  'Angers': 'ANG', 'Auxerre': 'AUX', 'Brest': 'BRE', 'Brestois': 'BRE',
  'Le Havre': 'HAV', 'Havre': 'HAV', 'Lens': 'LEN', 'RC Lens': 'LEN',
  'Lille': 'LIL', 'Lorient': 'LOR', 'Lyon': 'LYO', 'Olympique Lyonnais': 'LYO', 'Lyonnais': 'LYO',
  'Marseille': 'MAR', 'Olympique Marseille': 'MAR',
  'Metz': 'MET', 'Monaco': 'MON', 'Montpellier': 'MTP',
  'Nantes': 'NAN', 'Nice': 'NIC', 'PSG': 'PSG', 'Paris Saint-Germain': 'PSG',
  'Reims': 'REI', 'Rennes': 'REN', 'Saint-Etienne': 'STE',
  'Strasbourg': 'STR', 'Toulouse': 'TFC',
};

const TEAM_NAME_CHAR_GROUPS = [
  { chars: 'àáâãäåāăą', replacement: 'a' },
  { chars: 'çćč', replacement: 'c' },
  { chars: 'ď', replacement: 'd' },
  { chars: 'èéêëēėęě', replacement: 'e' },
  { chars: 'ìíîïīį', replacement: 'i' },
  { chars: 'ñń', replacement: 'n' },
  { chars: 'òóôõöøōő', replacement: 'o' },
  { chars: 'ùúûüūůű', replacement: 'u' },
  { chars: 'ýÿ', replacement: 'y' },
  { chars: 'śşš', replacement: 's' },
  { chars: 'ł', replacement: 'l' },
  { chars: 'žźż', replacement: 'z' },
  { chars: 'æ', replacement: 'a' },
  { chars: 'œ', replacement: 'o' },
  { chars: 'ß', replacement: 's' },
] as const;

const TEAM_NAME_CHAR_REPLACEMENTS = new Map<string, string>();
for (const group of TEAM_NAME_CHAR_GROUPS) {
  for (const char of group.chars) {
    TEAM_NAME_CHAR_REPLACEMENTS.set(char, group.replacement);
  }
}

const TEAM_NAME_CHAR_PATTERN = new RegExp(
  `[${TEAM_NAME_CHAR_GROUPS.map((group) => group.chars).join('')}]`,
  'g',
);

function cleanTeamName(teamName: string | null | undefined): string {
  return String(teamName || '').replace(/\s+/g, ' ').trim();
}

function transliterateTeamName(teamName: string): string {
  return teamName.replace(TEAM_NAME_CHAR_PATTERN, (char) => TEAM_NAME_CHAR_REPLACEMENTS.get(char) || char);
}

export function normalizeTeamNameKey(teamName: string | null | undefined): string {
  return transliterateTeamName(cleanTeamName(teamName).toLowerCase())
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '');
}

const TEAM_NAME_SQL_COLUMN_PATTERN = /^(?:(?:[a-z_][a-z0-9_]*|"(?:[A-Za-z_][A-Za-z0-9_]*)")\.)?(?:"(?:homeTeam|awayTeam)"|home_team|away_team)$/;

export function teamNameKeySql(column: string): string {
  if (!TEAM_NAME_SQL_COLUMN_PATTERN.test(column)) {
    throw new Error(`Unsafe teamNameKeySql column: ${column}`);
  }

  let normalized = `lower(coalesce(${column}, ''))`;

  for (const group of TEAM_NAME_CHAR_GROUPS) {
    normalized = `regexp_replace(${normalized}, '[${group.chars}]', '${group.replacement}', 'g')`;
  }

  return `regexp_replace(${normalized}, '[^a-z0-9]+', '', 'g')`;
}

const STATIC_CANONICAL_BY_KEY = Object.keys(TEAM_ABBR).reduce<Record<string, string>>((acc, teamName) => {
  const key = normalizeTeamNameKey(teamName);
  if (key && !acc[key]) acc[key] = teamName;
  return acc;
}, {});

export function canonicalizeTeamName(teamName: string | null | undefined): string {
  const cleaned = cleanTeamName(teamName);
  if (!cleaned) return '';
  return STATIC_CANONICAL_BY_KEY[normalizeTeamNameKey(cleaned)] || cleaned;
}

// ─── DB-backed cache (auto-populates from rm_events) ───
// This covers ALL teams across ALL leagues (especially NCAAB's 350+ schools)
let dbCache: Map<string, string> | null = null;
let dbCacheLoading = false;
let dbCacheLower: Map<string, string> | null = null;
let dbCacheNormalized: Map<string, string> | null = null;

async function loadDbCache(): Promise<void> {
  if (dbCache || dbCacheLoading) return;
  dbCacheLoading = true;
  try {
    const result = await pool.query(`
      SELECT DISTINCT team_name, team_short FROM (
        SELECT home_team as team_name, home_short as team_short
        FROM rm_events WHERE home_short IS NOT NULL AND home_short != ''
        UNION
        SELECT away_team, away_short
        FROM rm_events WHERE away_short IS NOT NULL AND away_short != ''
      ) t
    `);
    const rows = Array.isArray(result?.rows) ? result.rows : [];
    dbCache = new Map();
    dbCacheLower = new Map();
    dbCacheNormalized = new Map();
    for (const row of rows) {
      if (row.team_name && row.team_short) {
        const canonicalTeamName = canonicalizeTeamName(row.team_name);
        dbCache.set(canonicalTeamName, row.team_short);
        dbCacheLower.set(canonicalTeamName.toLowerCase(), row.team_short);
        const normalizedKey = normalizeTeamNameKey(canonicalTeamName);
        if (normalizedKey) dbCacheNormalized.set(normalizedKey, row.team_short);
      }
    }
    console.log(`[team-abbr] Loaded ${dbCache.size} team abbreviations from rm_events`);
  } catch (err) {
    console.warn('[team-abbr] Failed to load DB cache, using static map only:', (err as Error).message);
    dbCache = new Map();
    dbCacheLower = new Map();
    dbCacheNormalized = new Map();
  }
  dbCacheLoading = false;
}

// Build reverse lookup by nickname (last word of team name) — static map only
const NICKNAME_ABBR: Record<string, string> = {};
for (const [fullName, abbr] of Object.entries(TEAM_ABBR)) {
  const nickname = fullName.split(' ').pop()?.toLowerCase() || '';
  if (nickname.length > 2 && !NICKNAME_ABBR[nickname]) {
    NICKNAME_ABBR[nickname] = abbr;
  }
}

/**
 * Get the sportsbook abbreviation for a team name.
 * Lookup order: DB cache → static map → nickname → fallback
 */
export function getTeamAbbr(teamName: string | null): string {
  if (!teamName) return '???';
  const canonicalTeamName = canonicalizeTeamName(teamName);

  if (!dbCache && !dbCacheLoading) {
    void loadDbCache();
  }

  // 1. DB cache exact match (covers NCAAB + all leagues)
  if (dbCache) {
    const cached = dbCache.get(canonicalTeamName);
    if (cached) return cached;
    // Case-insensitive DB lookup
    const cachedLower = dbCacheLower?.get(canonicalTeamName.toLowerCase());
    if (cachedLower) return cachedLower;
    const cachedNormalized = dbCacheNormalized?.get(normalizeTeamNameKey(canonicalTeamName));
    if (cachedNormalized) return cachedNormalized;
  }

  // 2. Static map exact match
  if (TEAM_ABBR[canonicalTeamName]) return TEAM_ABBR[canonicalTeamName];

  // 3. Case-insensitive static match
  const lower = canonicalTeamName.toLowerCase();
  for (const [name, abbr] of Object.entries(TEAM_ABBR)) {
    if (name.toLowerCase() === lower) return abbr;
  }

  const normalizedKey = normalizeTeamNameKey(canonicalTeamName);
  const staticCanonical = STATIC_CANONICAL_BY_KEY[normalizedKey];
  if (staticCanonical && TEAM_ABBR[staticCanonical]) return TEAM_ABBR[staticCanonical];

  // 4. Nickname match (last word)
  const nickname = canonicalTeamName.trim().split(/\s+/).pop()?.toLowerCase() || '';
  if (NICKNAME_ABBR[nickname]) return NICKNAME_ABBR[nickname];

  // 5. Fallback: first 4 chars of first word, uppercase (city/school rather than mascot)
  const firstWord = canonicalTeamName.trim().split(/\s+/)[0] || '';
  return firstWord.slice(0, 4).toUpperCase();
}

/**
 * Format a matchup as abbreviations: "BOS vs LAL"
 */
export function formatMatchupAbbr(homeTeam: string | null, awayTeam: string | null): string {
  return `${getTeamAbbr(homeTeam)} vs ${getTeamAbbr(awayTeam)}`;
}

/**
 * Format final score with abbreviations: "BOS 131 - LAL 111"
 */
export function formatScoreAbbr(
  homeTeam: string | null, awayTeam: string | null,
  homeScore: number | null, awayScore: number | null,
  league: string
): string | null {
  if (homeScore == null || awayScore == null || !homeTeam || !awayTeam) return null;
  // Always show the score — transparency requirement. Scores are validated by score-validator.ts.
  return `${getTeamAbbr(homeTeam)} ${homeScore} - ${getTeamAbbr(awayTeam)} ${awayScore}`;
}

/**
 * Force-refresh the DB cache (call after data ingestion if needed)
 */
export async function refreshTeamAbbrCache(): Promise<void> {
  dbCache = null;
  dbCacheLower = null;
  dbCacheNormalized = null;
  dbCacheLoading = false;
  await loadDbCache();
}