#!/usr/bin/env python3 """ Link all orphaned records to SportsGame using team name matching. Tables: OddsSnapshot, GameHalfLine, BettingSplits, SharpMove Run: python3 scripts/link_tables_to_sportsgame.py """ import psycopg2 import os from datetime import datetime, timezone # Team name normalization (comprehensive) TEAM_ALIASES = { 'nba': { 'atlanta hawks': 'ATL', 'hawks': 'ATL', 'atlanta': 'ATL', 'atl': 'ATL', 'boston celtics': 'BOS', 'celtics': 'BOS', 'boston': 'BOS', 'bos': 'BOS', 'brooklyn nets': 'BKN', 'nets': 'BKN', 'brooklyn': 'BKN', 'bkn': 'BKN', 'charlotte hornets': 'CHA', 'hornets': 'CHA', 'charlotte': 'CHA', 'cha': 'CHA', 'chicago bulls': 'CHI', 'bulls': 'CHI', 'chicago': 'CHI', 'chi': 'CHI', 'cleveland cavaliers': 'CLE', 'cavaliers': 'CLE', 'cavs': 'CLE', 'cleveland': 'CLE', 'cle': 'CLE', 'dallas mavericks': 'DAL', 'mavericks': 'DAL', 'mavs': 'DAL', 'dallas': 'DAL', 'dal': 'DAL', 'denver nuggets': 'DEN', 'nuggets': 'DEN', 'denver': 'DEN', 'den': 'DEN', 'detroit pistons': 'DET', 'pistons': 'DET', 'detroit': 'DET', 'det': 'DET', 'golden state warriors': 'GSW', 'warriors': 'GSW', 'golden state': 'GSW', 'gsw': 'GSW', 'gs': 'GSW', 'houston rockets': 'HOU', 'rockets': 'HOU', 'houston': 'HOU', 'hou': 'HOU', 'indiana pacers': 'IND', 'pacers': 'IND', 'indiana': 'IND', 'ind': 'IND', 'los angeles clippers': 'LAC', 'clippers': 'LAC', 'la clippers': 'LAC', 'lac': 'LAC', 'los angeles lakers': 'LAL', 'lakers': 'LAL', 'la lakers': 'LAL', 'lal': 'LAL', 'memphis grizzlies': 'MEM', 'grizzlies': 'MEM', 'memphis': 'MEM', 'mem': 'MEM', 'miami heat': 'MIA', 'heat': 'MIA', 'miami': 'MIA', 'mia': 'MIA', 'milwaukee bucks': 'MIL', 'bucks': 'MIL', 'milwaukee': 'MIL', 'mil': 'MIL', 'minnesota timberwolves': 'MIN', 'timberwolves': 'MIN', 'wolves': 'MIN', 'minnesota': 'MIN', 'min': 'MIN', 'new orleans pelicans': 'NOP', 'pelicans': 'NOP', 'new orleans': 'NOP', 'nop': 'NOP', 'no': 'NOP', 'new york knicks': 'NYK', 'knicks': 'NYK', 'new york': 'NYK', 'nyk': 'NYK', 'ny': 'NYK', 'oklahoma city thunder': 'OKC', 'thunder': 'OKC', 'oklahoma city': 'OKC', 'okc': 'OKC', 'orlando magic': 'ORL', 'magic': 'ORL', 'orlando': 'ORL', 'orl': 'ORL', 'philadelphia 76ers': 'PHI', '76ers': 'PHI', 'sixers': 'PHI', 'philadelphia': 'PHI', 'phi': 'PHI', 'phoenix suns': 'PHX', 'suns': 'PHX', 'phoenix': 'PHX', 'phx': 'PHX', 'pho': 'PHX', 'portland trail blazers': 'POR', 'trail blazers': 'POR', 'blazers': 'POR', 'portland': 'POR', 'por': 'POR', 'sacramento kings': 'SAC', 'kings': 'SAC', 'sacramento': 'SAC', 'sac': 'SAC', 'san antonio spurs': 'SAS', 'spurs': 'SAS', 'san antonio': 'SAS', 'sas': 'SAS', 'sa': 'SAS', 'toronto raptors': 'TOR', 'raptors': 'TOR', 'toronto': 'TOR', 'tor': 'TOR', 'utah jazz': 'UTA', 'jazz': 'UTA', 'utah': 'UTA', 'uta': 'UTA', 'washington wizards': 'WAS', 'wizards': 'WAS', 'washington': 'WAS', 'was': 'WAS', 'wsh': 'WAS', }, 'nfl': { 'arizona cardinals': 'ARI', 'cardinals': 'ARI', 'arizona': 'ARI', 'ari': 'ARI', 'atlanta falcons': 'ATL', 'falcons': 'ATL', 'atl': 'ATL', 'baltimore ravens': 'BAL', 'ravens': 'BAL', 'baltimore': 'BAL', 'bal': 'BAL', 'buffalo bills': 'BUF', 'bills': 'BUF', 'buffalo': 'BUF', 'buf': 'BUF', 'carolina panthers': 'CAR', 'panthers': 'CAR', 'carolina': 'CAR', 'car': 'CAR', 'chicago bears': 'CHI', 'bears': 'CHI', 'chi': 'CHI', 'cincinnati bengals': 'CIN', 'bengals': 'CIN', 'cincinnati': 'CIN', 'cin': 'CIN', 'cleveland browns': 'CLE', 'browns': 'CLE', 'cle': 'CLE', 'dallas cowboys': 'DAL', 'cowboys': 'DAL', 'dal': 'DAL', 'denver broncos': 'DEN', 'broncos': 'DEN', 'den': 'DEN', 'detroit lions': 'DET', 'lions': 'DET', 'det': 'DET', 'green bay packers': 'GB', 'packers': 'GB', 'green bay': 'GB', 'gb': 'GB', 'houston texans': 'HOU', 'texans': 'HOU', 'hou': 'HOU', 'indianapolis colts': 'IND', 'colts': 'IND', 'indianapolis': 'IND', 'ind': 'IND', 'jacksonville jaguars': 'JAX', 'jaguars': 'JAX', 'jacksonville': 'JAX', 'jax': 'JAX', 'kansas city chiefs': 'KC', 'chiefs': 'KC', 'kansas city': 'KC', 'kc': 'KC', 'las vegas raiders': 'LV', 'raiders': 'LV', 'las vegas': 'LV', 'lv': 'LV', 'los angeles chargers': 'LAC', 'chargers': 'LAC', 'lac': 'LAC', 'los angeles rams': 'LAR', 'rams': 'LAR', 'lar': 'LAR', 'la': 'LAR', 'miami dolphins': 'MIA', 'dolphins': 'MIA', 'mia': 'MIA', 'minnesota vikings': 'MIN', 'vikings': 'MIN', 'min': 'MIN', 'new england patriots': 'NE', 'patriots': 'NE', 'new england': 'NE', 'ne': 'NE', 'new orleans saints': 'NO', 'saints': 'NO', 'no': 'NO', 'new york giants': 'NYG', 'giants': 'NYG', 'nyg': 'NYG', 'new york jets': 'NYJ', 'jets': 'NYJ', 'nyj': 'NYJ', 'philadelphia eagles': 'PHI', 'eagles': 'PHI', 'phi': 'PHI', 'pittsburgh steelers': 'PIT', 'steelers': 'PIT', 'pittsburgh': 'PIT', 'pit': 'PIT', 'san francisco 49ers': 'SF', '49ers': 'SF', 'san francisco': 'SF', 'sf': 'SF', 'niners': 'SF', 'seattle seahawks': 'SEA', 'seahawks': 'SEA', 'seattle': 'SEA', 'sea': 'SEA', 'tampa bay buccaneers': 'TB', 'buccaneers': 'TB', 'bucs': 'TB', 'tampa bay': 'TB', 'tb': 'TB', 'tennessee titans': 'TEN', 'titans': 'TEN', 'tennessee': 'TEN', 'ten': 'TEN', 'washington commanders': 'WAS', 'commanders': 'WAS', 'was': 'WAS', 'wsh': 'WAS', }, 'nhl': { 'anaheim ducks': 'ANA', 'ducks': 'ANA', 'anaheim': 'ANA', 'ana': 'ANA', 'arizona coyotes': 'ARI', 'coyotes': 'ARI', 'ari': 'ARI', 'boston bruins': 'BOS', 'bruins': 'BOS', 'bos': 'BOS', 'buffalo sabres': 'BUF', 'sabres': 'BUF', 'buf': 'BUF', 'calgary flames': 'CGY', 'flames': 'CGY', 'calgary': 'CGY', 'cgy': 'CGY', 'carolina hurricanes': 'CAR', 'hurricanes': 'CAR', 'canes': 'CAR', 'car': 'CAR', 'chicago blackhawks': 'CHI', 'blackhawks': 'CHI', 'chi': 'CHI', 'colorado avalanche': 'COL', 'avalanche': 'COL', 'avs': 'COL', 'colorado': 'COL', 'col': 'COL', 'columbus blue jackets': 'CBJ', 'blue jackets': 'CBJ', 'columbus': 'CBJ', 'cbj': 'CBJ', 'dallas stars': 'DAL', 'stars': 'DAL', 'dal': 'DAL', 'detroit red wings': 'DET', 'red wings': 'DET', 'det': 'DET', 'edmonton oilers': 'EDM', 'oilers': 'EDM', 'edmonton': 'EDM', 'edm': 'EDM', 'florida panthers': 'FLA', 'panthers': 'FLA', 'florida': 'FLA', 'fla': 'FLA', 'los angeles kings': 'LAK', 'kings': 'LAK', 'lak': 'LAK', 'la': 'LAK', 'minnesota wild': 'MIN', 'wild': 'MIN', 'min': 'MIN', 'montreal canadiens': 'MTL', 'canadiens': 'MTL', 'habs': 'MTL', 'montreal': 'MTL', 'mtl': 'MTL', 'nashville predators': 'NSH', 'predators': 'NSH', 'preds': 'NSH', 'nashville': 'NSH', 'nsh': 'NSH', 'new jersey devils': 'NJD', 'devils': 'NJD', 'new jersey': 'NJD', 'njd': 'NJD', 'new york islanders': 'NYI', 'islanders': 'NYI', 'nyi': 'NYI', 'new york rangers': 'NYR', 'rangers': 'NYR', 'nyr': 'NYR', 'ottawa senators': 'OTT', 'senators': 'OTT', 'sens': 'OTT', 'ottawa': 'OTT', 'ott': 'OTT', 'philadelphia flyers': 'PHI', 'flyers': 'PHI', 'phi': 'PHI', 'pittsburgh penguins': 'PIT', 'penguins': 'PIT', 'pens': 'PIT', 'pit': 'PIT', 'san jose sharks': 'SJS', 'sharks': 'SJS', 'san jose': 'SJS', 'sjs': 'SJS', 'sj': 'SJS', 'seattle kraken': 'SEA', 'kraken': 'SEA', 'sea': 'SEA', 'st. louis blues': 'STL', 'blues': 'STL', 'st louis blues': 'STL', 'stl': 'STL', 'tampa bay lightning': 'TBL', 'lightning': 'TBL', 'bolts': 'TBL', 'tbl': 'TBL', 'tb': 'TBL', 'toronto maple leafs': 'TOR', 'maple leafs': 'TOR', 'leafs': 'TOR', 'tor': 'TOR', 'utah hockey club': 'UTA', 'utah': 'UTA', 'uta': 'UTA', 'vancouver canucks': 'VAN', 'canucks': 'VAN', 'vancouver': 'VAN', 'van': 'VAN', 'vegas golden knights': 'VGK', 'golden knights': 'VGK', 'knights': 'VGK', 'vegas': 'VGK', 'vgk': 'VGK', 'washington capitals': 'WSH', 'capitals': 'WSH', 'caps': 'WSH', 'wsh': 'WSH', 'winnipeg jets': 'WPG', 'jets': 'WPG', 'winnipeg': 'WPG', 'wpg': 'WPG', }, 'epl': { 'arsenal': 'ARS', 'ars': 'ARS', 'aston villa': 'AVL', 'villa': 'AVL', 'avl': 'AVL', 'bournemouth': 'BOU', 'bou': 'BOU', 'brentford': 'BRE', 'bre': 'BRE', 'brighton': 'BHA', 'brighton and hove albion': 'BHA', 'bha': 'BHA', 'chelsea': 'CHE', 'che': 'CHE', 'crystal palace': 'CRY', 'palace': 'CRY', 'cry': 'CRY', 'everton': 'EVE', 'eve': 'EVE', 'fulham': 'FUL', 'ful': 'FUL', 'ipswich': 'IPS', 'ipswich town': 'IPS', 'ips': 'IPS', 'leicester': 'LEI', 'leicester city': 'LEI', 'lei': 'LEI', 'liverpool': 'LIV', 'liv': 'LIV', 'manchester city': 'MCI', 'man city': 'MCI', 'mci': 'MCI', 'manchester united': 'MUN', 'man united': 'MUN', 'man utd': 'MUN', 'mun': 'MUN', 'newcastle': 'NEW', 'newcastle united': 'NEW', 'new': 'NEW', 'nottingham forest': 'NFO', 'forest': 'NFO', 'nfo': 'NFO', 'southampton': 'SOU', 'sou': 'SOU', 'tottenham': 'TOT', 'tottenham hotspur': 'TOT', 'spurs': 'TOT', 'tot': 'TOT', 'west ham': 'WHU', 'west ham united': 'WHU', 'whu': 'WHU', 'wolves': 'WOL', 'wolverhampton': 'WOL', 'wolverhampton wanderers': 'WOL', 'wol': 'WOL', }, 'ucl': { 'ajax': 'AJA', 'barcelona': 'BAR', 'bayern munich': 'BAY', 'bayern': 'BAY', 'chelsea': 'CHE', 'dortmund': 'DOR', 'borussia dortmund': 'DOR', 'inter milan': 'INT', 'inter': 'INT', 'juventus': 'JUV', 'liverpool': 'LIV', 'manchester city': 'MCI', 'man city': 'MCI', 'psg': 'PSG', 'paris saint-germain': 'PSG', 'paris': 'PSG', 'real madrid': 'RMA', 'madrid': 'RMA', 'slavia praha': 'SLA', 'slavia prague': 'SLA', 'villarreal': 'VIL', 'pafos fc': 'PAF', 'pafos': 'PAF', }, } def normalize_team(team_name, league): """Normalize team name to standard abbreviation""" if not team_name: return None team_str = str(team_name).strip() if len(team_str) <= 4 and team_str.isupper(): return team_str team_lower = team_str.lower() league_aliases = TEAM_ALIASES.get(league, {}) if team_lower in league_aliases: return league_aliases[team_lower] for alias, abbrev in league_aliases.items(): if alias in team_lower or team_lower in alias: return abbrev return team_str[:3].upper() def load_db_url(): env_path = '/var/www/html/eventheodds/.env' try: with open(env_path, 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().strip('"').split('?')[0] except FileNotFoundError: pass return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] def build_sportsgame_lookup(cur): """Build lookup: (league, date, home_norm, away_norm) -> id""" cur.execute(''' SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' ''') lookup = {} for sg_id, league, gd, home, away in cur.fetchall(): home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) if home_norm and away_norm: key1 = (league, str(gd), home_norm, away_norm) key2 = (league, str(gd), away_norm, home_norm) lookup[key1] = sg_id lookup[key2] = sg_id return lookup def link_table(cur, conn, table_name, sg_lookup, stats): """Link records in a table to SportsGame""" # Get unlinked records cur.execute(f''' SELECT id, league, "homeTeam", "awayTeam", "gameDate" FROM "{table_name}" WHERE "gameId" IS NULL AND "homeTeam" IS NOT NULL AND "awayTeam" IS NOT NULL ''') unlinked = cur.fetchall() linked = 0 for rec_id, league, home, away, game_date in unlinked: if not game_date: continue home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) date_str = str(game_date.date()) if hasattr(game_date, 'date') else str(game_date)[:10] key = (league, date_str, home_norm, away_norm) sg_id = sg_lookup.get(key) if sg_id: cur.execute(f''' UPDATE "{table_name}" SET "gameId" = %s WHERE id = %s ''', (sg_id, rec_id)) linked += 1 conn.commit() stats[table_name] = linked return linked def run_linkage(): print("=" * 60) print("LINK TABLES TO SPORTSGAME") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() # Build lookup print("\n[1] Building SportsGame lookup...") sg_lookup = build_sportsgame_lookup(cur) print(f" Indexed {len(sg_lookup)} game entries") stats = {} # Link each table tables = ['OddsSnapshot', 'GameHalfLine', 'BettingSplits', 'SharpMove'] for table in tables: print(f"\n[{tables.index(table)+2}] Linking {table}...") try: linked = link_table(cur, conn, table, sg_lookup, stats) print(f" Linked {linked} records") except Exception as e: print(f" Error: {e}") conn.rollback() # Report final status print("\n" + "=" * 60) print("LINKAGE REPORT:") print("=" * 60) cur.execute(''' SELECT 'OddsSnapshot' as tbl, COUNT(*) as total, COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) as linked FROM "OddsSnapshot" UNION ALL SELECT 'GameHalfLine', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "GameHalfLine" UNION ALL SELECT 'BettingSplits', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "BettingSplits" UNION ALL SELECT 'SharpMove', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "SharpMove" ''') print(f"{'Table':<20} {'Total':>10} {'Linked':>10} {'%':>8}") print("-" * 50) for row in cur.fetchall(): tbl, total, linked = row pct = f"{100*linked/total:.1f}%" if total > 0 else "0%" print(f"{tbl:<20} {total:>10} {linked:>10} {pct:>8}") cur.close() conn.close() print("\n" + "=" * 60) print(f"Linkage complete: {sum(stats.values())} records linked") print("=" * 60) return stats if __name__ == '__main__': run_linkage()