#!/usr/bin/env python3 """ Sync odds from GameOdds to SportsGame using team name matching. Fixes games that have odds in GameOdds but not in SportsGame. Run: python3 scripts/sync_gameodds_to_sportsgame.py """ import psycopg2 import os from datetime import datetime, timezone # Reuse team normalization from linkage script 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', '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', 'jac': 'JAX', 'kansas city chiefs': 'KC', 'chiefs': 'KC', 'kansas city': 'KC', 'kc': 'KC', 'las vegas raiders': 'LV', 'raiders': 'LV', 'las vegas': 'LV', 'lv': 'LV', 'lvr': 'LV', 'los angeles chargers': 'LAC', 'chargers': 'LAC', 'lac': 'LAC', 'los angeles rams': 'LAR', 'rams': 'LAR', 'lar': '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', 'new orleans': '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', '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', 'nj': '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', }, } def normalize_team(team_name, league): 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 run_sync(): print("=" * 60) print("SYNC GAMEODDS 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 SportsGame lookup: (league, date, home, away) -> id print("\n[1] Building SportsGame lookup...") cur.execute(''' SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND "spreadHome" IS NULL ''') sg_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: key = (league, str(gd), home_norm, away_norm) sg_lookup[key] = sg_id print(f" Found {len(sg_lookup)} SportsGame records missing spreads") # Get best odds from GameOdds per game print("\n[2] Getting best odds from GameOdds...") cur.execute(''' WITH best_spreads AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", DATE("gameDate") as gd, "homeTeam", "awayTeam", "lineValue" as spread, "homeOdds" as spread_home_odds, "awayOdds" as spread_away_odds FROM "GameOdds" WHERE market = 'spreads' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ), best_ml AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", "homeOdds" as ml_home, "awayOdds" as ml_away FROM "GameOdds" WHERE market = 'h2h' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ), best_total AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", "lineValue" as total, "overOdds", "underOdds" FROM "GameOdds" WHERE market = 'totals' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ) SELECT s.league, s."gameId", s.gd, s."homeTeam", s."awayTeam", s.spread, m.ml_home, m.ml_away, t.total FROM best_spreads s LEFT JOIN best_ml m ON s.league = m.league AND s."gameId" = m."gameId" LEFT JOIN best_total t ON s.league = t.league AND s."gameId" = t."gameId" ''') gameodds = cur.fetchall() print(f" Found {len(gameodds)} games with odds in GameOdds") # Match and update print("\n[3] Syncing odds to SportsGame...") updates = [] for row in gameodds: league, game_id, gd, home, away, spread, ml_home, ml_away, total = row if not home or not away: continue home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) key = (league, str(gd), home_norm, away_norm) sg_id = sg_lookup.get(key) if sg_id and spread is not None: updates.append(( spread, -spread if spread else None, ml_home, ml_away, total, 'odds-api-sync', sg_id )) if updates: cur.executemany(''' UPDATE "SportsGame" SET "spreadHome" = %s, "spreadAway" = %s, "moneylineHome" = %s, "moneylineAway" = %s, "total" = %s, "oddsSource" = %s, "updatedAt" = NOW() WHERE id = %s ''', updates) print(f" Updated {len(updates)} SportsGame records with odds") else: print(" No updates needed") conn.commit() # Report final coverage print("\n[4] Coverage Report:") cur.execute(''' SELECT league, COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL) as completed, COUNT(*) FILTER (WHERE "spreadHome" IS NOT NULL) as has_spread, ROUND(100.0 * COUNT(*) FILTER (WHERE "spreadHome" IS NOT NULL) / NULLIF(COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL), 0), 1) as pct FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND league IN ('nba', 'nfl', 'nhl', 'ncaab', 'ncaaf', 'epl', 'mlb') GROUP BY league ORDER BY completed DESC ''') print(f" {'League':<10} {'Completed':>10} {'Has Spread':>12} {'%':>8}") print(f" {'-'*10} {'-'*10} {'-'*12} {'-'*8}") for row in cur.fetchall(): league, completed, has_spread, pct = row print(f" {league:<10} {completed:>10} {has_spread:>12} {pct or 0:>7.1f}%") cur.close() conn.close() print("\n" + "=" * 60) print(f"Sync complete: {len(updates)} games updated") print("=" * 60) return len(updates) if __name__ == '__main__': run_sync()