#!/usr/bin/env python3 """ Populate GameScheduleMeta with rest/travel metrics for each team's games. Calculates: restDays, isBackToBack, isThreeInFour, isFourInFive, gamesInLast7/14, travelDistance, timezoneChange, isCoastToCoast """ import psycopg2 from datetime import datetime, timezone, timedelta from collections import defaultdict import math import sys # Team locations (lat, lon) for distance calculation TEAM_COORDS = { # NBA 'ATL': (33.757, -84.396), 'BOS': (42.366, -71.062), 'BKN': (40.683, -73.975), 'CHA': (35.225, -80.839), 'CHI': (41.881, -87.674), 'CLE': (41.496, -81.688), 'DAL': (32.790, -96.810), 'DEN': (39.749, -105.008), 'DET': (42.341, -83.055), 'GSW': (37.768, -122.388), 'HOU': (29.751, -95.362), 'IND': (39.764, -86.155), 'LAC': (34.043, -118.267), 'LAL': (34.043, -118.267), 'MEM': (35.138, -90.051), 'MIA': (25.781, -80.188), 'MIL': (43.045, -87.917), 'MIN': (44.979, -93.276), 'NOP': (29.949, -90.082), 'NYK': (40.751, -73.994), 'OKC': (35.463, -97.515), 'ORL': (28.539, -81.384), 'PHI': (39.901, -75.172), 'PHX': (33.446, -112.071), 'POR': (45.532, -122.667), 'SAC': (38.580, -121.500), 'SAS': (29.427, -98.438), 'TOR': (43.643, -79.379), 'UTA': (40.768, -111.901), 'WAS': (38.898, -77.021), # NHL 'ANA': (33.808, -117.877), 'ARI': (33.532, -112.261), 'BUF': (42.875, -78.876), 'CGY': (51.037, -114.052), 'CAR': (35.803, -78.722), 'CBJ': (39.969, -83.006), 'COL': (39.749, -105.008), 'EDM': (53.547, -113.498), 'FLA': (26.158, -80.326), 'NSH': (36.159, -86.779), 'NJD': (40.733, -74.171), 'NYI': (40.724, -73.590), 'NYR': (40.751, -73.994), 'OTT': (45.297, -75.927), 'PIT': (40.439, -79.989), 'SEA': (47.622, -122.354), 'STL': (38.627, -90.203), 'TBL': (27.943, -82.452), 'VAN': (49.278, -123.109), 'VGK': (36.103, -115.178), 'WPG': (49.893, -97.144), 'WSH': (38.898, -77.021), # NFL (using city centers) 'ARI': (33.4484, -112.0740), 'ATL': (33.7490, -84.3880), 'BAL': (39.2904, -76.6122), 'BUF': (42.8864, -78.8784), 'CAR': (35.2271, -80.8431), 'CHI': (41.8781, -87.6298), 'CIN': (39.1031, -84.5120), 'CLE': (41.4993, -81.6944), 'DAL': (32.7767, -96.7970), 'DEN': (39.7392, -104.9903), 'DET': (42.3314, -83.0458), 'GB': (44.5013, -88.0622), 'HOU': (29.7604, -95.3698), 'IND': (39.7684, -86.1581), 'JAX': (30.3322, -81.6557), 'KC': (39.0997, -94.5786), 'LV': (36.1699, -115.1398), 'LAC': (34.0522, -118.2437), 'LAR': (34.0522, -118.2437), 'MIA': (25.7617, -80.1918), 'MIN': (44.9778, -93.2650), 'NE': (42.3601, -71.0589), 'NO': (29.9511, -90.0715), 'NYG': (40.8128, -74.0742), 'NYJ': (40.8128, -74.0742), 'PHI': (39.9526, -75.1652), 'PIT': (40.4406, -79.9959), 'SF': (37.7749, -122.4194), 'SEA': (47.6062, -122.3321), 'TB': (27.9506, -82.4572), 'TEN': (36.1627, -86.7816), 'WAS': (38.9072, -77.0369), } # Timezone mappings TIMEZONES = { # Eastern 'ATL': -5, 'BOS': -5, 'BKN': -5, 'CHA': -5, 'CLE': -5, 'DET': -5, 'IND': -5, 'MIA': -5, 'NYK': -5, 'ORL': -5, 'PHI': -5, 'TOR': -5, 'WAS': -5, 'BUF': -5, 'CAR': -5, 'CBJ': -5, 'FLA': -5, 'NJD': -5, 'NYI': -5, 'NYR': -5, 'OTT': -5, 'PIT': -5, 'TBL': -5, 'WSH': -5, 'BAL': -5, 'CIN': -5, 'JAX': -5, 'NE': -5, 'NYG': -5, 'NYJ': -5, 'TB': -5, # Central 'CHI': -6, 'DAL': -6, 'HOU': -6, 'MEM': -6, 'MIL': -6, 'MIN': -6, 'NOP': -6, 'OKC': -6, 'SAS': -6, 'NSH': -6, 'STL': -6, 'WPG': -6, 'GB': -6, 'KC': -6, 'NO': -6, 'TEN': -6, # Mountain 'DEN': -7, 'PHX': -7, 'UTA': -7, 'ARI': -7, 'CGY': -7, 'COL': -7, 'EDM': -7, # Pacific 'GSW': -8, 'LAC': -8, 'LAL': -8, 'POR': -8, 'SAC': -8, 'ANA': -8, 'SEA': -8, 'VAN': -8, 'VGK': -8, 'LV': -8, 'LAR': -8, 'SF': -8, } def haversine_miles(lat1, lon1, lat2, lon2): """Calculate distance between two points in miles.""" R = 3959 # Earth's radius in miles lat1, lon1, lat2, lon2 = map(math.radians, [lat1, lon1, lat2, lon2]) dlat = lat2 - lat1 dlon = lon2 - lon1 a = math.sin(dlat/2)**2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon/2)**2 return R * 2 * math.asin(math.sqrt(a)) def get_team_abbrev(team_name): """Extract team abbreviation from full name.""" abbrev_map = { 'HAWKS': 'ATL', 'CELTICS': 'BOS', 'NETS': 'BKN', 'HORNETS': 'CHA', 'BULLS': 'CHI', 'CAVALIERS': 'CLE', 'MAVERICKS': 'DAL', 'NUGGETS': 'DEN', 'PISTONS': 'DET', 'WARRIORS': 'GSW', 'ROCKETS': 'HOU', 'PACERS': 'IND', 'CLIPPERS': 'LAC', 'LAKERS': 'LAL', 'GRIZZLIES': 'MEM', 'HEAT': 'MIA', 'BUCKS': 'MIL', 'TIMBERWOLVES': 'MIN', 'PELICANS': 'NOP', 'KNICKS': 'NYK', 'THUNDER': 'OKC', 'MAGIC': 'ORL', '76ERS': 'PHI', 'SUNS': 'PHX', 'TRAIL BLAZERS': 'POR', 'BLAZERS': 'POR', 'KINGS': 'SAC', 'SPURS': 'SAS', 'RAPTORS': 'TOR', 'JAZZ': 'UTA', 'WIZARDS': 'WAS', # NHL 'DUCKS': 'ANA', 'COYOTES': 'ARI', 'BRUINS': 'BOS', 'SABRES': 'BUF', 'FLAMES': 'CGY', 'HURRICANES': 'CAR', 'BLACKHAWKS': 'CHI', 'AVALANCHE': 'COL', 'BLUE JACKETS': 'CBJ', 'STARS': 'DAL', 'RED WINGS': 'DET', 'OILERS': 'EDM', 'PANTHERS': 'FLA', 'KINGS': 'LAK', 'WILD': 'MIN', 'CANADIENS': 'MTL', 'PREDATORS': 'NSH', 'DEVILS': 'NJD', 'ISLANDERS': 'NYI', 'RANGERS': 'NYR', 'SENATORS': 'OTT', 'FLYERS': 'PHI', 'PENGUINS': 'PIT', 'SHARKS': 'SJS', 'KRAKEN': 'SEA', 'BLUES': 'STL', 'LIGHTNING': 'TBL', 'MAPLE LEAFS': 'TOR', 'CANUCKS': 'VAN', 'GOLDEN KNIGHTS': 'VGK', 'CAPITALS': 'WSH', 'JETS': 'WPG', } upper = team_name.upper() # Try direct match first if upper in TEAM_COORDS: return upper # Try name lookup for name, abbr in abbrev_map.items(): if name in upper: return abbr # Try first 3 chars return upper[:3] def load_db_url(): with open('/var/www/html/eventheodds/.env', 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] return '' def populate_rest_travel(leagues=None, seasons=None): """Populate GameScheduleMeta with rest/travel metrics.""" db_url = load_db_url() if not db_url: raise RuntimeError('SPORTS_DATABASE_URL not set') if leagues is None: leagues = ['nba', 'nhl', 'nfl'] if seasons is None: seasons = [2024, 2025, 2026] conn = psycopg2.connect(db_url) cur = conn.cursor() total_inserted = 0 for league in leagues: for season in seasons: print(f'\n[{league.upper()}] Processing season {season}...') # Fetch all games for this league/season cur.execute(''' SELECT id, "homeTeam", "awayTeam", "gameDate" FROM "SportsGame" WHERE league = %s AND season = %s AND "gameDate" IS NOT NULL ORDER BY "gameDate" ''', (league, season)) games = cur.fetchall() if not games: print(f' No games found') continue print(f' Found {len(games)} games') # Build team schedules team_games = defaultdict(list) for game_id, home, away, gdate in games: team_games[home].append({'id': game_id, 'date': gdate, 'is_home': True, 'opponent': away, 'location': home}) team_games[away].append({'id': game_id, 'date': gdate, 'is_home': False, 'opponent': home, 'location': home}) # Process each team for team, team_schedule in team_games.items(): team_schedule.sort(key=lambda x: x['date']) team_abbr = get_team_abbrev(team) for i, game in enumerate(team_schedule): game_date = game['date'] if hasattr(game_date, 'date'): game_date_only = game_date.date() else: game_date_only = game_date # Calculate rest days rest_days = None if i > 0: prev_date = team_schedule[i-1]['date'] if hasattr(prev_date, 'date'): prev_date = prev_date.date() if prev_date and game_date_only: rest_days = (game_date_only - prev_date).days - 1 is_b2b = rest_days == 0 if rest_days is not None else False # 3-in-4 check is_3in4 = False if i >= 2: dates = [g['date'] for g in team_schedule[max(0, i-2):i+1]] dates = [d.date() if hasattr(d, 'date') else d for d in dates] if len(dates) == 3 and all(dates): span = (dates[-1] - dates[0]).days is_3in4 = span <= 3 # 4-in-5 check is_4in5 = False if i >= 3: dates = [g['date'] for g in team_schedule[max(0, i-3):i+1]] dates = [d.date() if hasattr(d, 'date') else d for d in dates] if len(dates) == 4 and all(dates): span = (dates[-1] - dates[0]).days is_4in5 = span <= 4 # Games in last 7/14 days games_in_7 = sum(1 for g in team_schedule[:i+1] if (game_date_only - (g['date'].date() if hasattr(g['date'], 'date') else g['date'])).days <= 7) games_in_14 = sum(1 for g in team_schedule[:i+1] if (game_date_only - (g['date'].date() if hasattr(g['date'], 'date') else g['date'])).days <= 14) # Travel metrics travel_distance = None timezone_change = None is_coast_to_coast = False if i > 0: prev_loc = get_team_abbrev(team_schedule[i-1]['location']) curr_loc = get_team_abbrev(game['location']) if prev_loc in TEAM_COORDS and curr_loc in TEAM_COORDS: lat1, lon1 = TEAM_COORDS[prev_loc] lat2, lon2 = TEAM_COORDS[curr_loc] travel_distance = haversine_miles(lat1, lon1, lat2, lon2) is_coast_to_coast = travel_distance > 2000 prev_tz = TIMEZONES.get(prev_loc, -5) curr_tz = TIMEZONES.get(curr_loc, -5) timezone_change = abs(curr_tz - prev_tz) # Insert into GameScheduleMeta is_home = game['is_home'] cur.execute(''' INSERT INTO "GameScheduleMeta" ( league, "gameId", "gameDate", team, "isHome", "restDays", "isBackToBack", "isThreeInFour", "isFourInFive", "gamesInLast7", "gamesInLast14", "travelDistance", "timezoneChange", "isCoastToCoast", "updatedAt" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (league, "gameId", team) DO UPDATE SET "gameDate" = EXCLUDED."gameDate", "isHome" = EXCLUDED."isHome", "restDays" = EXCLUDED."restDays", "isBackToBack" = EXCLUDED."isBackToBack", "isThreeInFour" = EXCLUDED."isThreeInFour", "isFourInFive" = EXCLUDED."isFourInFive", "gamesInLast7" = EXCLUDED."gamesInLast7", "gamesInLast14" = EXCLUDED."gamesInLast14", "travelDistance" = EXCLUDED."travelDistance", "timezoneChange" = EXCLUDED."timezoneChange", "isCoastToCoast" = EXCLUDED."isCoastToCoast", "updatedAt" = NOW() ''', ( league, game['id'], game['date'], team, is_home, rest_days, is_b2b, is_3in4, is_4in5, games_in_7, games_in_14, travel_distance, timezone_change, is_coast_to_coast )) total_inserted += 1 conn.commit() print(f' Processed {len(team_games)} teams') cur.close() conn.close() print(f'\n✅ Total GameScheduleMeta records: {total_inserted}') return total_inserted if __name__ == '__main__': leagues = sys.argv[1].split(',') if len(sys.argv) > 1 else None seasons = [int(s) for s in sys.argv[2].split(',')] if len(sys.argv) > 2 else None populate_rest_travel(leagues, seasons)