#!/usr/bin/env python3 """ Relink Player Props to Scored SportsGame Records Props may be linked to SportsGame records that have no scores (from odds sources). This script finds the matching scored games and updates the gameId references. Run: python3 scripts/relink_props_to_scored_games.py """ import psycopg2 import os from datetime import datetime, timezone, timedelta from collections import defaultdict # Team name normalization (same as other scripts) 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', 'la clippers': 'LAC', 'clippers': 'LAC', 'los angeles lakers': 'LAL', 'la lakers': 'LAL', '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', 'sixers': 'PHI', '76ers': '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', 'oakland raiders': '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', 'utah hockey club': 'UTA', 'boston bruins': 'BOS', 'bruins': 'BOS', 'buffalo sabres': 'BUF', 'sabres': 'BUF', 'calgary flames': 'CGY', 'flames': 'CGY', 'calgary': 'CGY', 'carolina hurricanes': 'CAR', 'hurricanes': 'CAR', 'chicago blackhawks': 'CHI', 'blackhawks': 'CHI', 'colorado avalanche': 'COL', 'avalanche': 'COL', 'colorado': 'COL', 'avs': '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', 'florida': 'FLA', 'los angeles kings': 'LAK', 'la kings': 'LAK', 'minnesota wild': 'MIN', 'wild': 'MIN', 'montreal canadiens': 'MTL', 'canadiens': 'MTL', 'montreal': 'MTL', 'habs': 'MTL', 'nashville predators': 'NSH', 'predators': 'NSH', 'nashville': 'NSH', 'preds': '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', 'ottawa': 'OTT', 'sens': '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', 'st louis blues': 'STL', 'blues': 'STL', 'st. louis': 'STL', 'tampa bay lightning': 'TBL', 'lightning': 'TBL', 'toronto maple leafs': 'TOR', 'maple leafs': 'TOR', 'leafs': 'TOR', 'vancouver canucks': 'VAN', 'canucks': 'VAN', 'vancouver': 'VAN', 'vegas golden knights': 'VGK', 'golden knights': 'VGK', 'vegas': 'VGK', 'washington capitals': 'WSH', 'capitals': 'WSH', 'caps': 'WSH', 'winnipeg jets': 'WPG', 'jets': 'WPG', 'winnipeg': 'WPG', } def normalize_team(name): """Normalize team name to abbreviation""" if not name: return None name_lower = name.lower().strip() # Direct match if name_lower in TEAM_ALIASES: return TEAM_ALIASES[name_lower] # Already an abbreviation (3 letters) if len(name) <= 4 and name.isupper(): return name return name.upper()[:3] 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_relinkage(): print("=" * 60) print("RELINK PROPS TO SCORED GAMES") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() # Step 1: Find props linked to scoreless games print("\n[1] Finding props linked to scoreless SportsGame records...") cur.execute(''' SELECT DISTINCT ppl."gameId", sg.league, sg."homeTeam", sg."awayTeam", sg."gameDate"::date as game_date, COUNT(*) as prop_count FROM "PlayerPropLine" ppl JOIN "SportsGame" sg ON ppl."gameId" = sg.id WHERE ppl.result IS NULL AND sg."homeScore" IS NULL AND ppl.league IN ('nba', 'nfl', 'nhl') GROUP BY ppl."gameId", sg.league, sg."homeTeam", sg."awayTeam", sg."gameDate"::date ORDER BY sg."gameDate"::date DESC ''') scoreless_games = cur.fetchall() print(f" Found {len(scoreless_games)} scoreless games with props") if not scoreless_games: print(" All props already linked to scored games!") cur.close() conn.close() return {'relinked': 0} # Step 2: Build lookup of scored games by (league, teams, date range) print("\n[2] Building scored games lookup...") cur.execute(''' SELECT id, league, "homeTeam", "awayTeam", "gameDate"::date as game_date, "homeScore", "awayScore" FROM "SportsGame" WHERE "homeScore" IS NOT NULL AND league IN ('nba', 'nfl', 'nhl') ''') # Key: (league, home_abbr, away_abbr, date) -> game_id scored_lookup = defaultdict(list) for row in cur.fetchall(): game_id, league, home, away, game_date, home_score, away_score = row home_abbr = normalize_team(home) away_abbr = normalize_team(away) # Store for date and adjacent dates (timezone handling) for delta in range(-1, 2): # -1, 0, 1 days lookup_date = game_date + timedelta(days=delta) key = (league, home_abbr, away_abbr, str(lookup_date)) scored_lookup[key].append({ 'id': game_id, 'date': game_date, 'home_score': home_score, 'away_score': away_score }) # Also try swapped teams (in case home/away is reversed) key_swapped = (league, away_abbr, home_abbr, str(lookup_date)) scored_lookup[key_swapped].append({ 'id': game_id, 'date': game_date, 'home_score': home_score, 'away_score': away_score, 'swapped': True }) print(f" Loaded {len(scored_lookup)} scored game keys") # Step 3: Match scoreless games to scored equivalents print("\n[3] Matching scoreless to scored games...") relink_map = {} # old_game_id -> new_game_id stats = {'matched': 0, 'no_match': 0, 'props_relinked': 0} for row in scoreless_games: old_game_id, league, home, away, game_date, prop_count = row home_abbr = normalize_team(home) away_abbr = normalize_team(away) key = (league, home_abbr, away_abbr, str(game_date)) matches = scored_lookup.get(key, []) if matches: # Prefer exact date match best_match = min(matches, key=lambda m: abs((m['date'] - game_date).days)) relink_map[old_game_id] = best_match['id'] stats['matched'] += 1 print(f" MATCH: {home_abbr} vs {away_abbr} ({game_date}) -> game_id {best_match['id']} (score: {best_match['home_score']}-{best_match['away_score']})") else: stats['no_match'] += 1 print(f" NO MATCH: {home_abbr} vs {away_abbr} ({game_date}) - {prop_count} props") print(f"\n Matched: {stats['matched']}, No match: {stats['no_match']}") # Step 4: Update prop gameIds if relink_map: print("\n[4] Updating prop gameId references...") for old_id, new_id in relink_map.items(): cur.execute(''' UPDATE "PlayerPropLine" SET "gameId" = %s WHERE "gameId" = %s AND result IS NULL ''', (new_id, old_id)) updated = cur.rowcount stats['props_relinked'] += updated print(f" Updated {updated} props: game_id {old_id} -> {new_id}") conn.commit() print(f"\n Total props relinked: {stats['props_relinked']}") # Step 5: Verify linkage print("\n[5] Verifying linkage results...") cur.execute(''' SELECT COUNT(*) FROM "PlayerPropLine" ppl JOIN "SportsGame" sg ON ppl."gameId" = sg.id WHERE ppl.result IS NULL AND sg."homeScore" IS NOT NULL AND ppl.league IN ('nba', 'nfl', 'nhl') ''') linkable = cur.fetchone()[0] cur.execute(''' SELECT COUNT(*) FROM "PlayerPropLine" ppl JOIN "SportsGame" sg ON ppl."gameId" = sg.id WHERE ppl.result IS NULL AND sg."homeScore" IS NULL AND ppl.league IN ('nba', 'nfl', 'nhl') ''') still_scoreless = cur.fetchone()[0] print(f" Props now linkable to scores: {linkable}") print(f" Props still on scoreless games: {still_scoreless}") # Report print("\n" + "=" * 60) print("RESULTS:") print(f" Games matched: {stats['matched']}") print(f" Games unmatched: {stats['no_match']}") print(f" Props relinked: {stats['props_relinked']}") print("=" * 60) cur.close() conn.close() return stats if __name__ == '__main__': run_relinkage()