#!/usr/bin/env python3 """ Backfill Opening/Closing Lines from GameOdds to SportsGame Uses GameOdds.openingLineValue and current lines for historical games. Run: python3 scripts/backfill_opening_closing_from_gameodds.py """ import psycopg2 import os from datetime import datetime, timezone # Team name normalization (reuse from etl script) TEAM_ALIASES = { 'nba': { 'atlanta hawks': 'ATL', 'hawks': 'ATL', 'atlanta': 'ATL', 'boston celtics': 'BOS', 'celtics': 'BOS', 'boston': 'BOS', 'brooklyn nets': 'BKN', 'nets': 'BKN', 'brooklyn': 'BKN', 'charlotte hornets': 'CHA', 'hornets': 'CHA', 'charlotte': 'CHA', 'chicago bulls': 'CHI', 'bulls': 'CHI', 'chicago': 'CHI', 'cleveland cavaliers': 'CLE', 'cavaliers': 'CLE', 'cavs': 'CLE', 'cleveland': 'CLE', 'dallas mavericks': 'DAL', 'mavericks': 'DAL', 'mavs': 'DAL', 'dallas': 'DAL', 'denver nuggets': 'DEN', 'nuggets': 'DEN', 'denver': 'DEN', 'detroit pistons': 'DET', 'pistons': 'DET', 'detroit': 'DET', 'golden state warriors': 'GSW', 'warriors': 'GSW', 'golden state': 'GSW', 'houston rockets': 'HOU', 'rockets': 'HOU', 'houston': 'HOU', 'indiana pacers': 'IND', 'pacers': 'IND', 'indiana': 'IND', 'los angeles clippers': 'LAC', 'clippers': 'LAC', 'la clippers': 'LAC', 'los angeles lakers': 'LAL', 'lakers': 'LAL', 'la lakers': 'LAL', 'memphis grizzlies': 'MEM', 'grizzlies': 'MEM', 'memphis': 'MEM', 'miami heat': 'MIA', 'heat': 'MIA', 'miami': 'MIA', 'milwaukee bucks': 'MIL', 'bucks': 'MIL', 'milwaukee': 'MIL', 'minnesota timberwolves': 'MIN', 'timberwolves': 'MIN', 'wolves': 'MIN', 'minnesota': 'MIN', 'new orleans pelicans': 'NOP', 'pelicans': 'NOP', 'new orleans': 'NOP', 'new york knicks': 'NYK', 'knicks': 'NYK', 'new york': 'NYK', 'oklahoma city thunder': 'OKC', 'thunder': 'OKC', 'oklahoma city': 'OKC', 'okc': 'OKC', 'orlando magic': 'ORL', 'magic': 'ORL', 'orlando': 'ORL', 'philadelphia 76ers': 'PHI', '76ers': 'PHI', 'sixers': 'PHI', 'philadelphia': 'PHI', 'phoenix suns': 'PHX', 'suns': 'PHX', 'phoenix': 'PHX', 'portland trail blazers': 'POR', 'trail blazers': 'POR', 'blazers': 'POR', 'portland': 'POR', 'sacramento kings': 'SAC', 'kings': 'SAC', 'sacramento': 'SAC', 'san antonio spurs': 'SAS', 'spurs': 'SAS', 'san antonio': 'SAS', 'toronto raptors': 'TOR', 'raptors': 'TOR', 'toronto': 'TOR', 'utah jazz': 'UTA', 'jazz': 'UTA', 'utah': 'UTA', 'washington wizards': 'WAS', 'wizards': 'WAS', 'washington': 'WAS', }, 'nfl': { 'arizona cardinals': 'ARI', 'cardinals': 'ARI', 'arizona': 'ARI', 'atlanta falcons': 'ATL', 'falcons': 'ATL', 'baltimore ravens': 'BAL', 'ravens': 'BAL', 'baltimore': 'BAL', 'buffalo bills': 'BUF', 'bills': 'BUF', 'buffalo': 'BUF', 'carolina panthers': 'CAR', 'panthers': 'CAR', 'carolina': 'CAR', 'chicago bears': 'CHI', 'bears': 'CHI', 'cincinnati bengals': 'CIN', 'bengals': 'CIN', 'cincinnati': 'CIN', 'cleveland browns': 'CLE', 'browns': 'CLE', 'dallas cowboys': 'DAL', 'cowboys': 'DAL', 'denver broncos': 'DEN', 'broncos': 'DEN', 'detroit lions': 'DET', 'lions': 'DET', 'green bay packers': 'GB', 'packers': 'GB', 'green bay': 'GB', 'houston texans': 'HOU', 'texans': 'HOU', 'indianapolis colts': 'IND', 'colts': 'IND', 'indianapolis': 'IND', 'jacksonville jaguars': 'JAX', 'jaguars': 'JAX', 'jacksonville': 'JAX', 'kansas city chiefs': 'KC', 'chiefs': 'KC', 'kansas city': 'KC', 'las vegas raiders': 'LV', 'raiders': 'LV', 'las vegas': 'LV', 'los angeles chargers': 'LAC', 'chargers': 'LAC', 'los angeles rams': 'LAR', 'rams': 'LAR', 'miami dolphins': 'MIA', 'dolphins': 'MIA', 'minnesota vikings': 'MIN', 'vikings': 'MIN', 'new england patriots': 'NE', 'patriots': 'NE', 'new england': 'NE', 'new orleans saints': 'NO', 'saints': 'NO', 'new york giants': 'NYG', 'giants': 'NYG', 'new york jets': 'NYJ', 'jets': 'NYJ', 'philadelphia eagles': 'PHI', 'eagles': 'PHI', 'pittsburgh steelers': 'PIT', 'steelers': 'PIT', 'pittsburgh': 'PIT', 'san francisco 49ers': 'SF', '49ers': 'SF', 'san francisco': 'SF', 'niners': 'SF', 'seattle seahawks': 'SEA', 'seahawks': 'SEA', 'seattle': 'SEA', 'tampa bay buccaneers': 'TB', 'buccaneers': 'TB', 'bucs': 'TB', 'tampa bay': 'TB', 'tennessee titans': 'TEN', 'titans': 'TEN', 'tennessee': 'TEN', 'washington commanders': 'WAS', 'commanders': 'WAS', }, 'nhl': { 'anaheim ducks': 'ANA', 'ducks': 'ANA', 'anaheim': 'ANA', 'arizona coyotes': 'ARI', 'coyotes': 'ARI', 'boston bruins': 'BOS', 'bruins': 'BOS', 'buffalo sabres': 'BUF', 'sabres': 'BUF', 'calgary flames': 'CGY', 'flames': 'CGY', 'calgary': 'CGY', 'carolina hurricanes': 'CAR', 'hurricanes': 'CAR', 'canes': 'CAR', 'chicago blackhawks': 'CHI', 'blackhawks': 'CHI', 'colorado avalanche': 'COL', 'avalanche': 'COL', 'avs': 'COL', 'colorado': 'COL', 'columbus blue jackets': 'CBJ', 'blue jackets': 'CBJ', 'columbus': 'CBJ', 'dallas stars': 'DAL', 'stars': 'DAL', 'detroit red wings': 'DET', 'red wings': 'DET', 'edmonton oilers': 'EDM', 'oilers': 'EDM', 'edmonton': 'EDM', 'florida panthers': 'FLA', 'panthers': 'FLA', 'florida': 'FLA', 'los angeles kings': 'LAK', 'kings': 'LAK', 'minnesota wild': 'MIN', 'wild': 'MIN', 'montreal canadiens': 'MTL', 'canadiens': 'MTL', 'habs': 'MTL', 'montreal': 'MTL', 'nashville predators': 'NSH', 'predators': 'NSH', 'preds': 'NSH', 'nashville': 'NSH', 'new jersey devils': 'NJD', 'devils': 'NJD', 'new jersey': 'NJD', 'new york islanders': 'NYI', 'islanders': 'NYI', 'new york rangers': 'NYR', 'rangers': 'NYR', 'ottawa senators': 'OTT', 'senators': 'OTT', 'sens': 'OTT', 'ottawa': 'OTT', 'philadelphia flyers': 'PHI', 'flyers': 'PHI', 'pittsburgh penguins': 'PIT', 'penguins': 'PIT', 'pens': 'PIT', 'san jose sharks': 'SJS', 'sharks': 'SJS', 'san jose': 'SJS', 'seattle kraken': 'SEA', 'kraken': 'SEA', 'st. louis blues': 'STL', 'blues': 'STL', 'st louis blues': 'STL', 'saint louis blues': 'STL', 'tampa bay lightning': 'TBL', 'lightning': 'TBL', 'bolts': 'TBL', 'toronto maple leafs': 'TOR', 'maple leafs': 'TOR', 'leafs': 'TOR', 'utah hockey club': 'UTA', 'utah': 'UTA', 'vancouver canucks': 'VAN', 'canucks': 'VAN', 'vancouver': 'VAN', 'vegas golden knights': 'VGK', 'golden knights': 'VGK', 'knights': 'VGK', 'vegas': 'VGK', 'washington capitals': 'WSH', 'capitals': 'WSH', 'caps': 'WSH', 'winnipeg jets': 'WPG', 'jets': 'WPG', 'winnipeg': 'WPG', }, } def normalize_team(team_name, league): if not team_name: return None if len(team_name) <= 4 and team_name.isupper(): return team_name team_lower = team_name.lower().strip() 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_name[: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_backfill(): print("=" * 60) print("BACKFILL: Opening/Closing Lines from GameOdds") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() stats = {'opening': 0, 'closing': 0} # Build SportsGame lookup: (league, date, home_norm, away_norm) -> sg print("\n[1] Building SportsGame lookup index...") cur.execute(''' SELECT id, league, DATE("gameDate"), "homeTeam", "awayTeam", "homeScore", "openingSpreadHome", "closingSpreadHome", "spreadHome" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND "spreadHome" IS NOT NULL ''') sg_lookup = {} for row in cur.fetchall(): sg_id, league, gd, home, away, score, opening, closing, spread = row home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) key = (league, str(gd), home_norm, away_norm) sg_lookup[key] = { 'id': sg_id, 'has_opening': opening is not None, 'has_closing': closing is not None, 'completed': score is not None, 'current_spread': spread, } print(f" Indexed {len(sg_lookup)} SportsGame records with spreads") # Get GameOdds with opening lines print("\n[2] Processing GameOdds with opening line data...") cur.execute(''' SELECT DISTINCT ON (league, "gameId") league, "gameId", DATE("gameDate") as gd, "homeTeam", "awayTeam", "openingLineValue", "openingHomeOdds", "openingAwayOdds", "lineValue", "homeOdds", "awayOdds" FROM "GameOdds" WHERE market = 'spreads' AND "gameDate" IS NOT NULL AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "fetchedAt" DESC ''') gameodds = cur.fetchall() print(f" Found {len(gameodds)} unique games in GameOdds") updates_opening = [] updates_closing = [] for row in gameodds: league, game_id, gd, home, away, open_val, open_home, open_away, curr_val, curr_home, curr_away = 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 = sg_lookup.get(key) if not sg: # Try reverse key2 = (league, str(gd), away_norm, home_norm) sg = sg_lookup.get(key2) if sg: # Swap spread values open_val = -open_val if open_val else None curr_val = -curr_val if curr_val else None if not sg: continue # Set opening line if available and not already set if open_val is not None and not sg['has_opening']: updates_opening.append((open_val, -open_val if open_val else None, sg['id'])) # Set closing line for completed games if not already set if sg['completed'] and not sg['has_closing']: # Use current line as closing line close_val = curr_val if curr_val is not None else open_val if close_val is not None: updates_closing.append((close_val, -close_val if close_val else None, sg['id'])) print(f"\n[3] Applying updates...") # Apply opening line updates if updates_opening: cur.executemany(''' UPDATE "SportsGame" SET "openingSpreadHome" = %s, "openingSpreadAway" = %s, "updatedAt" = NOW() WHERE id = %s ''', updates_opening) stats['opening'] = len(updates_opening) print(f" Set {len(updates_opening)} opening lines from GameOdds") # Apply closing line updates if updates_closing: cur.executemany(''' UPDATE "SportsGame" SET "closingSpreadHome" = %s, "closingSpreadAway" = %s, "updatedAt" = NOW() WHERE id = %s ''', updates_closing) stats['closing'] = len(updates_closing) print(f" Set {len(updates_closing)} closing lines from GameOdds") conn.commit() # Also set closing = current spread for all completed games without closing print("\n[4] Setting closing lines from current spreads for remaining games...") cur.execute(''' UPDATE "SportsGame" SET "closingSpreadHome" = "spreadHome", "closingSpreadAway" = "spreadAway", "closingTotal" = "total", "closingMoneylineHome" = "moneylineHome", "closingMoneylineAway" = "moneylineAway", "updatedAt" = NOW() WHERE "homeScore" IS NOT NULL AND "closingSpreadHome" IS NULL AND "spreadHome" IS NOT NULL AND "gameDate" >= '2024-10-01' ''') stats['closing_from_current'] = cur.rowcount print(f" Set {cur.rowcount} closing lines from current spreads") conn.commit() # Report print("\n[5] Coverage Report:") cur.execute(''' SELECT league, COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL) as completed, COUNT(*) FILTER (WHERE "openingSpreadHome" IS NOT NULL) as has_opening, COUNT(*) FILTER (WHERE "closingSpreadHome" IS NOT NULL) as has_closing FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND league IN ('nba', 'nfl', 'nhl', 'ncaab', 'epl', 'mma') GROUP BY league ORDER BY completed DESC ''') print(f" {'League':<10} {'Completed':>10} {'Opening':>15} {'Closing':>15}") print(f" {'-'*10} {'-'*10} {'-'*15} {'-'*15}") for row in cur.fetchall(): league, completed, opening, closing = row if completed > 0: open_pct = f"{100*opening/completed:.1f}%" close_pct = f"{100*closing/completed:.1f}%" else: open_pct = close_pct = "0%" print(f" {league:<10} {completed:>10} {opening:>10} ({open_pct:>5}) {closing:>10} ({close_pct:>5})") cur.close() conn.close() print("\n" + "=" * 60) print(f"Backfill Complete:") print(f" Opening lines set: {stats['opening']}") print(f" Closing lines set: {stats['closing']}") print(f" Closing from current: {stats.get('closing_from_current', 0)}") print("=" * 60) return stats if __name__ == '__main__': run_backfill()