#!/usr/bin/env python3 """ Populate Soccer Period Scoring Fetches half-by-half scoring data from football-data.org API and stores in TeamGameMetric. """ import requests import psycopg2 import json import time import os from datetime import datetime, timezone, timedelta def load_api_key(): with open('/var/www/html/eventheodds/.env', 'r') as f: for line in f: if line.startswith('FOOTBALL_DATA_API_KEY='): return line.split('=', 1)[1].strip() return os.environ.get('FOOTBALL_DATA_API_KEY', '') HEADERS = { 'User-Agent': 'Mozilla/5.0 (compatible; EvenTheOdds/1.0)', 'Accept': 'application/json', 'X-Auth-Token': load_api_key(), } # Football-data.org league codes FD_LEAGUES = { 'PL': 'eng.1', # Premier League 'PD': 'esp.1', # La Liga 'BL1': 'ger.1', # Bundesliga 'SA': 'ita.1', # Serie A 'FL1': 'fra.1', # Ligue 1 } 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 fetch_fd_matches(league_code, date_from, date_to): """Fetch matches from football-data.org API for a date range.""" url = f"https://api.football-data.org/v4/competitions/{league_code}/matches" params = {'dateFrom': date_from, 'dateTo': date_to, 'status': 'FINISHED'} try: resp = requests.get(url, headers=HEADERS, params=params, timeout=30) if resp.status_code == 200: return resp.json().get('matches', []) elif resp.status_code == 429: print(f" Rate limited, waiting...") time.sleep(60) return fetch_fd_matches(league_code, date_from, date_to) else: print(f" Error {resp.status_code}: {resp.text[:100]}") except Exception as e: print(f" Error fetching {league_code}: {e}") return [] def extract_half_scores(match): """Extract first half and second half scores from football-data.org match.""" score = match.get('score', {}) full_time = score.get('fullTime', {}) half_time = score.get('halfTime', {}) home_team = match.get('homeTeam', {}).get('name', '') away_team = match.get('awayTeam', {}).get('name', '') home_total = full_time.get('home') away_total = full_time.get('away') home_1h = half_time.get('home') away_1h = half_time.get('away') # Calculate 2nd half as total - 1st half home_2h = None away_2h = None if home_total is not None and home_1h is not None: home_2h = home_total - home_1h if away_total is not None and away_1h is not None: away_2h = away_total - away_1h return { 'home_team': home_team, 'away_team': away_team, 'home_1h': home_1h, 'home_2h': home_2h, 'away_1h': away_1h, 'away_2h': away_2h, 'home_total': home_total, 'away_total': away_total, 'date': match.get('utcDate'), } def populate_soccer_periods(days_back=90): """Populate period scoring data for recent soccer games.""" db_url = load_db_url() if not db_url: raise RuntimeError('SPORTS_DATABASE_URL not set') conn = psycopg2.connect(db_url) cur = conn.cursor() print("=" * 60) print("POPULATE SOCCER PERIOD SCORING (football-data.org)") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) total_metrics = 0 # Calculate date range date_to = datetime.now(timezone.utc) date_from = date_to - timedelta(days=days_back) date_from_str = date_from.strftime('%Y-%m-%d') date_to_str = date_to.strftime('%Y-%m-%d') for fd_code, our_code in FD_LEAGUES.items(): print(f"\n[{fd_code}] Fetching matches from {date_from_str} to {date_to_str}...") matches = fetch_fd_matches(fd_code, date_from_str, date_to_str) print(f" Found {len(matches)} finished matches") matches_with_halves = 0 for match in matches: scores = extract_half_scores(match) if scores['home_1h'] is None and scores['away_1h'] is None: continue matches_with_halves += 1 game_date = scores['date'] dt = datetime.fromisoformat(game_date.replace('Z', '+00:00')) if game_date else datetime.now(timezone.utc) season = dt.year if dt.month >= 8 else dt.year - 1 game_key = f"{our_code}_{dt.strftime('%Y%m%d')}_{scores['home_team'][:10]}_{scores['away_team'][:10]}" # Insert metrics for home team metrics = [ ('soccer_1h_goals_for', scores['home_1h']), ('soccer_2h_goals_for', scores['home_2h']), ('soccer_1h_goals_against', scores['away_1h']), ('soccer_2h_goals_against', scores['away_2h']), ] for stat_key, value in metrics: if value is not None: cur.execute(''' INSERT INTO "TeamGameMetric" (league, season, "gameKey", "gameDate", team, "statKey", value) VALUES (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, season, "gameKey", team, "statKey") DO UPDATE SET value = EXCLUDED.value ''', (our_code, season, game_key, dt, scores['home_team'], stat_key, float(value))) total_metrics += 1 # Insert metrics for away team (swap for/against) metrics_away = [ ('soccer_1h_goals_for', scores['away_1h']), ('soccer_2h_goals_for', scores['away_2h']), ('soccer_1h_goals_against', scores['home_1h']), ('soccer_2h_goals_against', scores['home_2h']), ] for stat_key, value in metrics_away: if value is not None: cur.execute(''' INSERT INTO "TeamGameMetric" (league, season, "gameKey", "gameDate", team, "statKey", value) VALUES (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, season, "gameKey", team, "statKey") DO UPDATE SET value = EXCLUDED.value ''', (our_code, season, game_key, dt, scores['away_team'], stat_key, float(value))) total_metrics += 1 conn.commit() print(f" Processed {matches_with_halves} matches with half-time data") time.sleep(6) # Rate limit: 10 requests per minute cur.close() conn.close() print(f"\n✅ Total soccer period metrics: {total_metrics}") return total_metrics if __name__ == '__main__': import sys days = int(sys.argv[1]) if len(sys.argv) > 1 else 90 populate_soccer_periods(days)