#!/usr/bin/env python3 """ Fetch Referee Assignments from ESPN Gets referee/official data for NBA, NFL, NHL games. ESPN API: /sports/{sport}/{league}/summary?event={id} Officials are in header.competitions[0].officials Run: Daily after games complete """ import requests import psycopg2 import os import time import argparse from datetime import datetime, timezone, timedelta ESPN_BASE = 'https://site.api.espn.com/apis/site/v2/sports' REQUEST_DELAY = 0.5 LEAGUE_CONFIG = { 'nba': {'sport': 'basketball', 'espn_league': 'nba'}, 'nfl': {'sport': 'football', 'espn_league': 'nfl'}, 'nhl': {'sport': 'hockey', 'espn_league': 'nhl'}, 'ncaab': {'sport': 'basketball', 'espn_league': 'mens-college-basketball'}, 'ncaaf': {'sport': 'football', 'espn_league': 'college-football'}, } 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().split('?')[0] except FileNotFoundError: pass return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] def ensure_referee_table(cur): """Create GameReferee table if needed""" cur.execute(''' CREATE TABLE IF NOT EXISTS "GameReferee" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(50) NOT NULL, "gameId" BIGINT NOT NULL, "gameDate" TIMESTAMP, "refereeName" VARCHAR(200) NOT NULL, "refereeRole" VARCHAR(50), "refereeId" VARCHAR(100), "createdAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, "gameId", "refereeName") ) ''') cur.execute('CREATE INDEX IF NOT EXISTS "GameReferee_league_ref_idx" ON "GameReferee" (league, "refereeName")') cur.execute('CREATE INDEX IF NOT EXISTS "GameReferee_gameId_idx" ON "GameReferee" ("gameId")') def get_espn_events_for_date(sport, espn_league, date_str): """Get all ESPN event IDs for a date""" url = f'{ESPN_BASE}/{sport}/{espn_league}/scoreboard?dates={date_str}' if espn_league in ['mens-college-basketball', 'college-football']: url += '&groups=50&limit=300' try: resp = requests.get(url, timeout=15) if resp.status_code != 200: return [] data = resp.json() events = [] for event in data.get('events', []): event_id = event.get('id') competitions = event.get('competitions', []) if not competitions: continue comp = competitions[0] competitors = comp.get('competitors', []) home_team = None away_team = None for competitor in competitors: team = competitor.get('team', {}) abbrev = team.get('abbreviation', '').upper() if competitor.get('homeAway') == 'home': home_team = abbrev else: away_team = abbrev if event_id and home_team and away_team: events.append({ 'id': event_id, 'home': home_team, 'away': away_team }) return events except Exception as e: return [] def fetch_officials(sport, espn_league, event_id): """Fetch officials from ESPN summary endpoint""" url = f'{ESPN_BASE}/{sport}/{espn_league}/summary?event={event_id}' try: resp = requests.get(url, timeout=15) if resp.status_code != 200: return [] data = resp.json() officials = [] # Try header.competitions[0].officials header = data.get('header', {}) competitions = header.get('competitions', []) if competitions: for official in competitions[0].get('officials', []): name = official.get('displayName') or official.get('fullName') position = official.get('position', {}) role = position.get('displayName') if isinstance(position, dict) else position ref_id = official.get('id') if name: officials.append({ 'name': name.strip(), 'role': role, 'id': str(ref_id) if ref_id else None }) # Also try gameInfo.officials game_info = data.get('gameInfo', {}) for official in game_info.get('officials', []): name = official.get('displayName') or official.get('fullName') if name and name not in [o['name'] for o in officials]: officials.append({ 'name': name.strip(), 'role': official.get('position', {}).get('displayName'), 'id': str(official.get('id')) if official.get('id') else None }) return officials except Exception as e: return [] def fetch_referees_for_league(league, days_back=30): """Fetch referee data for a league""" if league not in LEAGUE_CONFIG: print(f"Unknown league: {league}") return {'inserted': 0, 'games': 0} config = LEAGUE_CONFIG[league] db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() ensure_referee_table(cur) conn.commit() # Get dates to process end_date = datetime.now(timezone.utc).date() start_date = end_date - timedelta(days=days_back) print(f"\n{league.upper()}: Fetching referees from {start_date} to {end_date}") total_inserted = 0 total_games = 0 current_date = start_date while current_date <= end_date: date_str = current_date.strftime('%Y%m%d') events = get_espn_events_for_date(config['sport'], config['espn_league'], date_str) for event in events: time.sleep(REQUEST_DELAY) officials = fetch_officials(config['sport'], config['espn_league'], event['id']) if not officials: continue # Find matching game in SportsGame cur.execute(''' SELECT id, "gameDate" FROM "SportsGame" WHERE league = %s AND "homeTeam" = %s AND "awayTeam" = %s AND "gameDate"::date = %s LIMIT 1 ''', (league, event['home'], event['away'], current_date)) game = cur.fetchone() if not game: continue game_id, game_date = game total_games += 1 for official in officials: try: cur.execute(''' INSERT INTO "GameReferee" (league, "gameId", "gameDate", "refereeName", "refereeRole", "refereeId") VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (league, "gameId", "refereeName") DO NOTHING ''', (league, game_id, game_date, official['name'], official['role'], official['id'])) if cur.rowcount > 0: total_inserted += 1 except Exception as e: pass current_date += timedelta(days=1) if (current_date - start_date).days % 7 == 0: print(f" {league}: Processed through {current_date}, {total_games} games, {total_inserted} refs") conn.commit() conn.commit() cur.close() conn.close() print(f" {league}: Total {total_inserted} referees from {total_games} games") return {'inserted': total_inserted, 'games': total_games} def main(): parser = argparse.ArgumentParser(description='Fetch referee data from ESPN') parser.add_argument('--leagues', type=str, default='nba,nfl,nhl', help='Comma-separated leagues') parser.add_argument('--days', type=int, default=60, help='Days to look back') args = parser.parse_args() print("=" * 60) print("FETCH ESPN REFEREE DATA") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) leagues = [l.strip().lower() for l in args.leagues.split(',')] total_inserted = 0 total_games = 0 for league in leagues: result = fetch_referees_for_league(league, days_back=args.days) total_inserted += result['inserted'] total_games += result['games'] print(f"\n{'='*60}") print(f"TOTAL: {total_inserted} referees from {total_games} games") print("=" * 60) if __name__ == '__main__': main()