#!/usr/bin/env python3 """ Capture Odds Snapshots Periodically captures odds for upcoming games to track line movements. Stores snapshots in OddsSnapshot table for CLV analysis. Run: Every 2-4 hours via cron - Captures "periodic" snapshots for line movement - Marks first capture as "opening" - Captures "closing" snapshot at game time - Links snapshots to SportsGame via team name matching - Updates SportsGame opening/closing lines directly """ import psycopg2 import os from datetime import datetime, timezone, timedelta # Team name normalization for matching OddsSnapshot to SportsGame 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', '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): """Normalize team name to standard abbreviation for matching""" 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 build_sportsgame_lookup(cur): """Build index of SportsGame by (league, date, home, away) -> id""" cur.execute(''' SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= NOW() - INTERVAL '1 day' AND "gameDate" < NOW() + INTERVAL '14 days' ''') lookup = {} for sg_id, league, gd, home, away in cur.fetchall(): home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) key = (league, str(gd), home_norm, away_norm) lookup[key] = sg_id # Also index reverse in case home/away are swapped key2 = (league, str(gd), away_norm, home_norm) lookup[key2] = sg_id return lookup def capture_snapshots(): """Capture current odds as snapshots with team-based game matching""" db_url = load_db_url() if not db_url: raise RuntimeError('SPORTS_DATABASE_URL not set') conn = psycopg2.connect(db_url) cur = conn.cursor() now = datetime.now(timezone.utc) print("=" * 60) print("CAPTURE ODDS SNAPSHOTS") print(f"Time: {now.isoformat()}") print("=" * 60) # Build SportsGame lookup for team-based matching sg_lookup = build_sportsgame_lookup(cur) print(f"Built SportsGame lookup with {len(sg_lookup)} entries") # Get upcoming games within next 7 days that have odds cur.execute(''' SELECT DISTINCT g.league, g."gameId", g."gameDate", g."homeTeam", g."awayTeam" FROM "GameOdds" g WHERE g."gameDate" IS NOT NULL AND g."gameDate" > NOW() AND g."gameDate" < NOW() + INTERVAL '7 days' ''') upcoming_games = cur.fetchall() print(f"Found {len(upcoming_games)} upcoming games with odds") snapshots_created = 0 openings_marked = 0 closings_captured = 0 opening_lines_set = 0 for league, game_id, game_date, home, away in upcoming_games: hours_to_game = (game_date - now).total_seconds() / 3600 if game_date else None # Determine snapshot type if hours_to_game is not None and hours_to_game <= 0.5: snapshot_type = 'closing' closings_captured += 1 else: # Check if this is the first snapshot for this game cur.execute(''' SELECT COUNT(*) FROM "OddsSnapshot" WHERE league = %s AND "externalGameId" = %s ''', (league, game_id)) existing = cur.fetchone()[0] if existing == 0: snapshot_type = 'opening' openings_marked += 1 else: snapshot_type = 'periodic' # Get best spread odds cur.execute(''' SELECT "lineValue", "homeOdds", "awayOdds", bookmaker, source FROM "GameOdds" WHERE league = %s AND "gameId" = %s AND market = 'spreads' ORDER BY "isBestLine" DESC NULLS LAST, "fetchedAt" DESC LIMIT 1 ''', (league, game_id)) spread = cur.fetchone() # Get best moneyline odds cur.execute(''' SELECT "homeOdds", "awayOdds", bookmaker, source FROM "GameOdds" WHERE league = %s AND "gameId" = %s AND market = 'h2h' ORDER BY "isBestLine" DESC NULLS LAST, "fetchedAt" DESC LIMIT 1 ''', (league, game_id)) ml = cur.fetchone() # Get best total odds cur.execute(''' SELECT "lineValue", "overOdds", "underOdds", bookmaker, source FROM "GameOdds" WHERE league = %s AND "gameId" = %s AND market = 'totals' ORDER BY "isBestLine" DESC NULLS LAST, "fetchedAt" DESC LIMIT 1 ''', (league, game_id)) total = cur.fetchone() if not spread and not ml and not total: continue # Find SportsGame ID using team-based matching sg_id = None if home and away and game_date: home_norm = normalize_team(home, league) away_norm = normalize_team(away, league) date_str = str(game_date.date()) key = (league, date_str, home_norm, away_norm) sg_id = sg_lookup.get(key) # Insert snapshot try: cur.execute(''' INSERT INTO "OddsSnapshot" ( league, "gameId", "externalGameId", "gameDate", "homeTeam", "awayTeam", "snapshotType", "snapshotAt", "hoursToGame", "moneylineHome", "moneylineAway", "spreadHome", "spreadAway", "spreadHomeOdds", "spreadAwayOdds", total, "totalOverOdds", "totalUnderOdds", source, bookmaker, "createdAt" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (league, "externalGameId", "snapshotAt", source, bookmaker) DO NOTHING ''', ( league, sg_id, game_id, game_date, home, away, snapshot_type, now, hours_to_game, ml[0] if ml else None, ml[1] if ml else None, spread[0] if spread else None, -spread[0] if spread and spread[0] else None, spread[1] if spread else None, spread[2] if spread else None, total[0] if total else None, total[1] if total else None, total[2] if total else None, spread[4] if spread else (ml[3] if ml else (total[4] if total else None)), spread[3] if spread else (ml[2] if ml else (total[3] if total else None)) )) snapshots_created += 1 # If this is an opening snapshot, also update SportsGame opening lines if snapshot_type == 'opening' and sg_id and spread: cur.execute(''' UPDATE "SportsGame" SET "openingSpreadHome" = %s, "openingSpreadAway" = %s, "openingMoneylineHome" = %s, "openingMoneylineAway" = %s, "openingTotal" = %s, "openingCapturedAt" = %s, "updatedAt" = NOW() WHERE id = %s AND "openingSpreadHome" IS NULL ''', ( spread[0], -spread[0] if spread[0] else None, ml[0] if ml else None, ml[1] if ml else None, total[0] if total else None, now, sg_id )) if cur.rowcount > 0: opening_lines_set += 1 except Exception as e: print(f" Error for {league} {game_id}: {e}") conn.rollback() # Update SportsGame closing lines for games starting soon (within 30 min) # Use team-based matching instead of externalGameId cur.execute(''' WITH closing_odds AS ( SELECT DISTINCT ON (go.league, go."homeTeam", DATE(go."gameDate")) go.league, go."homeTeam", go."awayTeam", DATE(go."gameDate") as game_date, go."lineValue" as spread, go_ml."homeOdds" as ml_home, go_ml."awayOdds" as ml_away, go_t."lineValue" as total FROM "GameOdds" go LEFT JOIN "GameOdds" go_ml ON go_ml.league = go.league AND go_ml."gameId" = go."gameId" AND go_ml.market = 'h2h' LEFT JOIN "GameOdds" go_t ON go_t.league = go.league AND go_t."gameId" = go."gameId" AND go_t.market = 'totals' WHERE go.market = 'spreads' AND go."gameDate" > NOW() - INTERVAL '30 minutes' AND go."gameDate" < NOW() + INTERVAL '30 minutes' ORDER BY go.league, go."homeTeam", DATE(go."gameDate"), go."fetchedAt" DESC ) UPDATE "SportsGame" sg SET "closingSpreadHome" = COALESCE(co.spread, sg."closingSpreadHome"), "closingSpreadAway" = COALESCE(-co.spread, sg."closingSpreadAway"), "closingMoneylineHome" = COALESCE(co.ml_home, sg."closingMoneylineHome"), "closingMoneylineAway" = COALESCE(co.ml_away, sg."closingMoneylineAway"), "closingTotal" = COALESCE(co.total, sg."closingTotal"), "closingCapturedAt" = NOW(), "updatedAt" = NOW() FROM closing_odds co WHERE sg.league = co.league AND DATE(sg."gameDate") = co.game_date AND sg."closingSpreadHome" IS NULL ''') closing_updates = cur.rowcount conn.commit() cur.close() conn.close() print(f"\nResults:") print(f" Snapshots created: {snapshots_created}") print(f" Opening snapshots: {openings_marked}") print(f" Opening lines set on SportsGame: {opening_lines_set}") print(f" Closing snapshots: {closings_captured}") print(f" SportsGame closing updates: {closing_updates}") print("=" * 60) return { 'snapshots': snapshots_created, 'openings': openings_marked, 'opening_lines_set': opening_lines_set, 'closings': closings_captured, 'closing_updates': closing_updates } def main(): try: result = capture_snapshots() print(f"\nCapture complete: {result['snapshots']} snapshots created") except Exception as e: print(f"ERROR: {e}") import traceback traceback.print_exc() if __name__ == '__main__': main()