#!/usr/bin/env python3 """ Full Historical Backfill Engine for The Odds API v4 5-tier backfill covering 60 days (Dec 22, 2025 -> Feb 20, 2026) across ALL available markets and sports. Separate from existing historical_backfill_theoddsapi.py (untouched). Tiers: 1. Core markets (h2h, spreads, totals) for all sports ~22K credits 2. Historical player props (per-event) ~176K credits 3. Historical period markets (quarters, halves, periods) ~29K credits 4. Historical alt lines (alternate_spreads, alternate_totals) ~58K credits 5. Extended sports (soccer, MMA, AFL, NRL, WNBA) ~36K credits Usage: python historical_backfill_full_theoddsapi.py --tier 1 --dry-run python historical_backfill_full_theoddsapi.py --tier 1 --from 2025-12-22 --to 2026-02-20 python historical_backfill_full_theoddsapi.py --tier 2 --leagues nba,nfl python historical_backfill_full_theoddsapi.py --tier all --batch 5 python historical_backfill_full_theoddsapi.py --status """ import argparse import requests import psycopg2 import psycopg2.extras import json import os import sys import time from datetime import datetime, timezone, timedelta from typing import Optional, Dict, List, Tuple sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) from team_names import normalize_to_full sys.path.insert(0, os.path.dirname(__file__)) try: from lib.unified_odds_ingestion import ( log_ingestion_start, log_ingestion_complete, get_or_create_sports_game, upsert_bookmaker_odds, insert_odds_history, ) UNIFIED_AVAILABLE = True except ImportError: UNIFIED_AVAILABLE = False BASE_URL = 'https://api.the-odds-api.com/v4' PROGRESS_FILE = '/var/www/html/eventheodds/data/backfill_progress.json' CREDIT_SAFETY_MARGIN = 50000 # Stop with 50K credits remaining # Core sport keys (Tiers 1-4) CORE_SPORTS = { 'nba': 'basketball_nba', 'nfl': 'americanfootball_nfl', 'nhl': 'icehockey_nhl', 'mlb': 'baseball_mlb', 'ncaab': 'basketball_ncaab', 'ncaaf': 'americanfootball_ncaaf', } # Extended sport keys (Tier 5) EXTENDED_SPORTS = { 'wnba': 'basketball_wnba', 'epl': 'soccer_epl', 'bundesliga': 'soccer_germany_bundesliga', 'seriea': 'soccer_italy_serie_a', 'laliga': 'soccer_spain_la_liga', 'ligue1': 'soccer_france_ligue_one', 'ucl': 'soccer_uefa_champions_league', 'uel': 'soccer_uefa_europa_league', 'mls': 'soccer_usa_mls', 'mma': 'mma_mixed_martial_arts', 'afl': 'aussierules_afl', 'nrl': 'rugbyleague_nrl', } CORE_MARKETS = ['h2h', 'spreads', 'totals'] # Prop markets per sport PROP_MARKETS = { 'nba': [ 'player_points', 'player_rebounds', 'player_assists', 'player_threes', 'player_points_rebounds_assists', 'player_double_double', 'player_blocks', 'player_steals', 'player_turnovers', 'player_points_rebounds', 'player_points_assists', 'player_rebounds_assists', ], 'nfl': [ 'player_pass_tds', 'player_pass_yds', 'player_rush_yds', 'player_reception_yds', 'player_receptions', 'player_anytime_td', 'player_pass_attempts', 'player_pass_completions', 'player_pass_interceptions', 'player_rush_attempts', 'player_rush_tds', 'player_reception_tds', ], 'nhl': [ 'player_goals', 'player_shots_on_goal', 'player_goal_scorer_anytime', 'player_points', 'player_assists', 'player_total_saves', ], 'mlb': [ 'batter_home_runs', 'pitcher_strikeouts', 'batter_hits_runs_rbis', 'batter_hits', 'batter_total_bases', 'batter_rbis', 'batter_strikeouts', ], 'ncaab': ['player_points', 'player_rebounds', 'player_assists'], 'ncaaf': ['player_pass_tds', 'player_rush_yds', 'player_pass_yds'], } # Period markets per sport PERIOD_MARKETS = { 'nba': [ 'h2h_q1', 'spreads_q1', 'totals_q1', 'h2h_q2', 'spreads_q2', 'totals_q2', 'h2h_q3', 'spreads_q3', 'totals_q3', 'h2h_q4', 'spreads_q4', 'totals_q4', 'h2h_h1', 'spreads_h1', 'totals_h1', 'h2h_h2', 'spreads_h2', 'totals_h2', ], 'nfl': [ 'h2h_q1', 'spreads_q1', 'totals_q1', 'h2h_h1', 'spreads_h1', 'totals_h1', 'h2h_h2', 'spreads_h2', 'totals_h2', ], 'nhl': [ 'h2h_p1', 'spreads_p1', 'totals_p1', 'h2h_p2', 'spreads_p2', 'totals_p2', 'h2h_p3', 'spreads_p3', 'totals_p3', ], 'mlb': [ 'h2h_1st_5_innings', 'spreads_1st_5_innings', 'totals_1st_5_innings', ], } def load_config() -> Tuple[str, str]: """Load database URL and API key from .env""" env_path = '/var/www/html/eventheodds/.env' db_url = None api_key = None try: with open(env_path, 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): db_url = line.split('=', 1)[1].strip().strip('"').split('?')[0] elif line.startswith('THE_ODDS_API_KEY='): api_key = line.split('=', 1)[1].strip().strip('"') except FileNotFoundError: pass db_url = db_url or os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] api_key = api_key or os.environ.get('THE_ODDS_API_KEY') return db_url, api_key def load_progress() -> dict: """Load checkpoint progress""" try: with open(PROGRESS_FILE, 'r') as f: return json.load(f) except (FileNotFoundError, json.JSONDecodeError): return {} def save_progress(progress: dict): """Save checkpoint progress""" os.makedirs(os.path.dirname(PROGRESS_FILE), exist_ok=True) with open(PROGRESS_FILE, 'w') as f: json.dump(progress, f, indent=2, default=str) def get_progress_key(tier: int, league: str, date_str: str) -> str: return f"tier{tier}:{league}:{date_str}" class HistoricalFetcher: def __init__(self, api_key: str): self.api_key = api_key self.session = requests.Session() self.quota_used = 0 self.quota_remaining = None self.requests_made = 0 def _request(self, endpoint: str, params: dict = None) -> Optional[dict]: """Make API request with rate limiting and quota tracking""" if params is None: params = {} params['apiKey'] = self.api_key url = f"{BASE_URL}{endpoint}" try: resp = self.session.get(url, params=params, timeout=60) self.quota_used = int(resp.headers.get('x-requests-used', 0)) self.quota_remaining = int(resp.headers.get('x-requests-remaining', 0)) self.requests_made += 1 if resp.status_code == 401: print(f"ERROR: Invalid API key") return None if resp.status_code == 422: return None # No data if resp.status_code == 429: print(f"ERROR: Quota exceeded (used: {self.quota_used})") return None if resp.status_code != 200: print(f"ERROR: API returned {resp.status_code}") return None return resp.json() except Exception as e: print(f"ERROR: Request failed: {e}") return None def check_budget(self) -> bool: """Check if we have enough credits to continue""" if self.quota_remaining is not None and self.quota_remaining < CREDIT_SAFETY_MARGIN: print(f"\n*** CREDIT SAFETY STOP: {self.quota_remaining} remaining " f"(margin: {CREDIT_SAFETY_MARGIN}) ***") return False return True def get_historical_odds(self, sport_key: str, date_iso: str, markets: List[str] = None) -> Optional[dict]: """Get historical sport-level odds""" if markets is None: markets = CORE_MARKETS params = { 'date': date_iso, 'regions': 'us', 'markets': ','.join(markets), 'oddsFormat': 'american' } return self._request(f'/historical/sports/{sport_key}/odds', params) def get_historical_events(self, sport_key: str, date_iso: str) -> Optional[dict]: """Get historical events list (1 credit)""" params = {'date': date_iso} return self._request(f'/historical/sports/{sport_key}/events', params) def get_historical_event_odds(self, sport_key: str, event_id: str, date_iso: str, markets: List[str]) -> Optional[dict]: """Get historical event-level odds (for props)""" params = { 'date': date_iso, 'regions': 'us', 'markets': ','.join(markets), 'oddsFormat': 'american' } return self._request( f'/historical/sports/{sport_key}/events/{event_id}/odds', params ) def ensure_tables(conn): """Ensure required tables exist. PeriodOdds, GameOddsAltLine, GameOdds already exist with their own schemas. Only create PropSnapshot if needed. """ cur = conn.cursor() # PropSnapshot — may not exist yet cur.execute(''' CREATE TABLE IF NOT EXISTS "PropSnapshot" ( id SERIAL PRIMARY KEY, league VARCHAR(20), "theoddsapiEventId" VARCHAR(100), "gameDate" TIMESTAMP, "homeTeam" VARCHAR(100), "awayTeam" VARCHAR(100), bookmaker VARCHAR(50), "marketType" VARCHAR(50), "playerName" VARCHAR(100), "propLine" DOUBLE PRECISION, "overPrice" INTEGER, "underPrice" INTEGER, "capturedAt" TIMESTAMP DEFAULT NOW(), source VARCHAR(50) DEFAULT 'theoddsapi', "createdAt" TIMESTAMP DEFAULT NOW() ) ''') cur.execute(''' CREATE INDEX IF NOT EXISTS idx_prop_snapshot_event ON "PropSnapshot"(league, "theoddsapiEventId", "playerName", "capturedAt") ''') # Add bookmaker/source columns to PeriodOdds if missing for col, default in [('bookmaker', None), ('source', "'theoddsapi'")]: cur.execute(""" SELECT column_name FROM information_schema.columns WHERE table_name = 'PeriodOdds' AND column_name = %s """, (col,)) if not cur.fetchone(): try: if default: cur.execute(f'ALTER TABLE "PeriodOdds" ADD COLUMN {col} TEXT DEFAULT {default}') else: cur.execute(f'ALTER TABLE "PeriodOdds" ADD COLUMN {col} TEXT') except: conn.rollback() conn.commit() cur.close() def extract_consensus_line(event: dict) -> dict: """Extract consensus line from bookmakers (weighted average of top books)""" spreads = [] totals = [] ml_home = [] ml_away = [] preferred_books = ['fanduel', 'draftkings', 'betmgm', 'caesars'] for book in event.get('bookmakers', []): book_key = book.get('key', '') weight = 2 if book_key in preferred_books else 1 for market in book.get('markets', []): market_key = market.get('key') if market_key == 'spreads': for o in market.get('outcomes', []): if o.get('name') == event.get('home_team') and o.get('point') is not None: spreads.extend([o['point']] * weight) elif market_key == 'totals': for o in market.get('outcomes', []): if o.get('name') == 'Over' and o.get('point') is not None: totals.extend([o['point']] * weight) elif market_key == 'h2h': for o in market.get('outcomes', []): if o.get('name') == event.get('home_team') and o.get('price') is not None: ml_home.extend([o['price']] * weight) elif o.get('name') == event.get('away_team') and o.get('price') is not None: ml_away.extend([o['price']] * weight) result = {} if spreads: result['spread_home'] = round(sum(spreads) / len(spreads) * 2) / 2 if totals: result['total'] = round(sum(totals) / len(totals) * 2) / 2 if ml_home: result['ml_home'] = int(sum(ml_home) / len(ml_home)) if ml_away: result['ml_away'] = int(sum(ml_away) / len(ml_away)) return result def generate_dates(from_date: str, to_date: str) -> List[str]: """Generate list of date strings""" start = datetime.strptime(from_date, '%Y-%m-%d') end = datetime.strptime(to_date, '%Y-%m-%d') dates = [] current = start while current <= end: dates.append(current.strftime('%Y-%m-%d')) current += timedelta(days=1) return dates # ============================================================================ # TIER 1: Core Markets # ============================================================================ def tier1_core_markets(fetcher: HistoricalFetcher, conn, leagues: List[str], from_date: str, to_date: str, dry_run: bool = False): """Backfill core markets (h2h, spreads, totals) for all sports""" print(f"\n{'='*60}") print(f"TIER 1: CORE MARKETS BACKFILL") print(f"Date range: {from_date} to {to_date}") print(f"Leagues: {', '.join(leagues)}") print(f"{'='*60}") progress = load_progress() cur = conn.cursor() total_updated = 0 total_snapshots = 0 dates = generate_dates(from_date, to_date) for league in leagues: sport_key = {**CORE_SPORTS, **EXTENDED_SPORTS}.get(league) if not sport_key: continue print(f"\n--- {league.upper()} ---") for date_str in dates: pkey = get_progress_key(1, league, date_str) if progress.get(pkey) == 'done': continue if not fetcher.check_budget(): save_progress(progress) return total_updated # Opening snapshot (~6am UTC) opening_ts = f"{date_str}T06:00:00Z" # Closing snapshot (~11pm UTC) closing_ts = f"{date_str}T23:00:00Z" for snapshot_type, ts in [('opening', opening_ts), ('closing', closing_ts)]: data = fetcher.get_historical_odds(sport_key, ts) time.sleep(1) if not data or not data.get('data'): continue timestamp = data.get('timestamp', ts) for event in data['data']: home_raw = event.get('home_team', '') away_raw = event.get('away_team', '') home = normalize_to_full(home_raw, league) away = normalize_to_full(away_raw, league) event_id = event.get('id') commence = event.get('commence_time') commence_dt = None if commence: try: commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00')) except: pass consensus = extract_consensus_line(event) if not consensus: continue if dry_run: print(f" [DRY] {date_str} {snapshot_type}: {away} @ {home} | " f"spread={consensus.get('spread_home')} total={consensus.get('total')}") total_updated += 1 continue # Update SportsGame opening/closing if snapshot_type == 'opening': updates = [] values = [] if consensus.get('spread_home') is not None: updates.append('"openingSpreadHome" = COALESCE("openingSpreadHome", %s)') values.append(consensus['spread_home']) updates.append('"openingSpreadAway" = COALESCE("openingSpreadAway", %s)') values.append(-consensus['spread_home']) if consensus.get('total') is not None: updates.append('"openingTotal" = COALESCE("openingTotal", %s)') values.append(consensus['total']) if consensus.get('ml_home') is not None: updates.append('"openingMoneylineHome" = COALESCE("openingMoneylineHome", %s)') values.append(consensus['ml_home']) if consensus.get('ml_away') is not None: updates.append('"openingMoneylineAway" = COALESCE("openingMoneylineAway", %s)') values.append(consensus['ml_away']) if updates: updates.append('"openingCapturedAt" = COALESCE("openingCapturedAt", %s)') values.append(timestamp) else: updates = [] values = [] if consensus.get('spread_home') is not None: updates.append('"closingSpreadHome" = COALESCE("closingSpreadHome", %s)') values.append(consensus['spread_home']) updates.append('"closingSpreadAway" = COALESCE("closingSpreadAway", %s)') values.append(-consensus['spread_home']) if consensus.get('total') is not None: updates.append('"closingTotal" = COALESCE("closingTotal", %s)') values.append(consensus['total']) if consensus.get('ml_home') is not None: updates.append('"closingMoneylineHome" = COALESCE("closingMoneylineHome", %s)') values.append(consensus['ml_home']) if consensus.get('ml_away') is not None: updates.append('"closingMoneylineAway" = COALESCE("closingMoneylineAway", %s)') values.append(consensus['ml_away']) if updates: updates.append('"closingCapturedAt" = COALESCE("closingCapturedAt", %s)') values.append(timestamp) if updates: # Find matching game if commence_dt: day_start = commence_dt.replace(hour=0, minute=0, second=0) day_end = commence_dt.replace(hour=23, minute=59, second=59) values.extend([league, home, away, day_start, day_end]) cur.execute(f''' UPDATE "SportsGame" SET {', '.join(updates)}, "updatedAt" = NOW() WHERE league = %s AND "homeTeam" = %s AND "awayTeam" = %s AND "gameDate" BETWEEN %s AND %s ''', values) if cur.rowcount > 0: total_updated += cur.rowcount # Store per-bookmaker snapshots in OddsSnapshot for book in event.get('bookmakers', []): book_name = book.get('key', 'unknown') ml_h = ml_a = sp_h = sp_a = sp_h_o = sp_a_o = None tot = over_o = under_o = None for market in book.get('markets', []): mk = market.get('key') for o in market.get('outcomes', []): if mk == 'h2h': if o.get('name') == home_raw: ml_h = o.get('price') elif o.get('name') == away_raw: ml_a = o.get('price') elif mk == 'spreads': if o.get('name') == home_raw: sp_h = o.get('point') sp_h_o = o.get('price') elif o.get('name') == away_raw: sp_a = o.get('point') sp_a_o = o.get('price') elif mk == 'totals': if o.get('name') == 'Over': tot = o.get('point') over_o = o.get('price') elif o.get('name') == 'Under': under_o = o.get('price') cur.execute(''' INSERT INTO "OddsSnapshot" ( league, "theoddsapiEventId", "externalGameId", "gameDate", "homeTeam", "awayTeam", bookmaker, "snapshotType", "snapshotAt", "moneylineHome", "moneylineAway", "spreadHome", "spreadAway", "spreadHomeOdds", "spreadAwayOdds", total, "totalOverOdds", "totalUnderOdds", source ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill' ) ON CONFLICT (league, "externalGameId", "snapshotAt", source, bookmaker) DO NOTHING ''', ( league, event_id, event_id, commence_dt, home, away, book_name, snapshot_type, timestamp, ml_h, ml_a, sp_h, sp_a, sp_h_o, sp_a_o, tot, over_o, under_o )) total_snapshots += 1 if not dry_run: conn.commit() progress[pkey] = 'done' if dates.index(date_str) % 5 == 0: if not dry_run: save_progress(progress) print(f" {league.upper()} {date_str}: {total_updated} games updated, " f"{total_snapshots} snapshots, quota={fetcher.quota_remaining}") if not dry_run: save_progress(progress) print(f"\nTier 1 complete: {total_updated} games updated, {total_snapshots} snapshots") return total_updated # ============================================================================ # TIER 2: Historical Props # ============================================================================ def tier2_historical_props(fetcher: HistoricalFetcher, conn, leagues: List[str], from_date: str, to_date: str, dry_run: bool = False): """Backfill historical player props""" print(f"\n{'='*60}") print(f"TIER 2: HISTORICAL PROPS BACKFILL") print(f"Date range: {from_date} to {to_date}") print(f"{'='*60}") progress = load_progress() cur = conn.cursor() total_props = 0 dates = generate_dates(from_date, to_date) for league in leagues: sport_key = CORE_SPORTS.get(league) props = PROP_MARKETS.get(league) if not sport_key or not props: continue print(f"\n--- {league.upper()} ({len(props)} prop markets) ---") for date_str in dates: pkey = get_progress_key(2, league, date_str) if progress.get(pkey) == 'done': continue if not fetcher.check_budget(): save_progress(progress) return total_props # Get events for this date (1 credit) events_ts = f"{date_str}T18:00:00Z" events_data = fetcher.get_historical_events(sport_key, events_ts) time.sleep(1) if not events_data or not events_data.get('data'): progress[pkey] = 'done' continue events = events_data['data'] print(f" {league.upper()} {date_str}: {len(events)} events") for event in events: event_id = event.get('id') home = normalize_to_full(event.get('home_team', ''), league) away = normalize_to_full(event.get('away_team', ''), league) commence = event.get('commence_time') commence_dt = None if commence: try: commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00')) except: pass if not fetcher.check_budget(): save_progress(progress) return total_props # Fetch props in batches of 8 for batch_start in range(0, len(props), 8): batch = props[batch_start:batch_start + 8] # Use closing time (~2h before game) prop_ts = events_ts # Use same day snapshot if commence_dt: prop_dt = commence_dt - timedelta(hours=2) prop_ts = prop_dt.strftime('%Y-%m-%dT%H:%M:%SZ') event_odds = fetcher.get_historical_event_odds( sport_key, event_id, prop_ts, batch ) time.sleep(1) if not event_odds or not event_odds.get('data'): continue event_data = event_odds['data'] for book in event_data.get('bookmakers', []): book_name = book.get('key', 'unknown') for market in book.get('markets', []): market_key = market.get('key', '') for outcome in market.get('outcomes', []): player_name = outcome.get('description', outcome.get('name', '')) prop_line = outcome.get('point') price = outcome.get('price') outcome_type = outcome.get('name', '').lower() if prop_line is None: continue over_price = price if outcome_type == 'over' else None under_price = price if outcome_type == 'under' else None if dry_run: total_props += 1 continue cur.execute(''' INSERT INTO "PropSnapshot" ( league, "theoddsapiEventId", "gameDate", "homeTeam", "awayTeam", bookmaker, "marketType", "playerName", "propLine", "overPrice", "underPrice", "capturedAt", source ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill') ON CONFLICT DO NOTHING ''', ( league, event_id, commence_dt, home, away, book_name, market_key, player_name, prop_line, over_price, under_price, prop_ts )) total_props += 1 if not dry_run: conn.commit() progress[pkey] = 'done' if dates.index(date_str) % 3 == 0: if not dry_run: save_progress(progress) print(f" Props so far: {total_props}, quota={fetcher.quota_remaining}") if not dry_run: save_progress(progress) print(f"\nTier 2 complete: {total_props} prop records") return total_props # ============================================================================ # TIER 3: Historical Period Markets # ============================================================================ def tier3_historical_periods(fetcher: HistoricalFetcher, conn, leagues: List[str], from_date: str, to_date: str, dry_run: bool = False): """Backfill historical period markets""" print(f"\n{'='*60}") print(f"TIER 3: HISTORICAL PERIOD MARKETS BACKFILL") print(f"Date range: {from_date} to {to_date}") print(f"{'='*60}") progress = load_progress() cur = conn.cursor() total = 0 dates = generate_dates(from_date, to_date) for league in leagues: sport_key = CORE_SPORTS.get(league) period_mkts = PERIOD_MARKETS.get(league) if not sport_key or not period_mkts: continue print(f"\n--- {league.upper()} ({len(period_mkts)} period markets) ---") for date_str in dates: pkey = get_progress_key(3, league, date_str) if progress.get(pkey) == 'done': continue if not fetcher.check_budget(): save_progress(progress) return total # Fetch period markets in batches of 8 for batch_start in range(0, len(period_mkts), 8): batch = period_mkts[batch_start:batch_start + 8] ts = f"{date_str}T20:00:00Z" data = fetcher.get_historical_odds(sport_key, ts, markets=batch) time.sleep(1) if not data or not data.get('data'): continue for event in data['data']: event_id = event.get('id') home = normalize_to_full(event.get('home_team', ''), league) away = normalize_to_full(event.get('away_team', ''), league) commence = event.get('commence_time') commence_dt = None if commence: try: commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00')) except: pass for book in event.get('bookmakers', []): book_name = book.get('key', 'unknown') for market in book.get('markets', []): market_key = market.get('key', '') # Parse period from market key if '_1st_' in market_key: parts = market_key.split('_', 1) m_type, period = parts[0], parts[1] else: parts = market_key.rsplit('_', 1) m_type = parts[0] if len(parts) == 2 else market_key period = parts[1] if len(parts) == 2 else 'full' # Accumulate spread/total/ml for this period from outcomes sp_home = sp_away = tot_val = ml_home = ml_away = None for o in market.get('outcomes', []): name = o.get('name', '') line_value = o.get('point') price = o.get('price') if price is None: continue if m_type in ('spreads', 'spread'): if name == event.get('home_team'): sp_home = line_value sp_away = -line_value if line_value is not None else None elif m_type in ('totals', 'total'): if name == 'Over': tot_val = line_value elif m_type in ('h2h', 'moneyline'): if name == event.get('home_team'): ml_home = price elif name == event.get('away_team'): ml_away = price if sp_home is None and tot_val is None and ml_home is None and ml_away is None: continue if dry_run: total += 1 continue # PeriodOdds actual schema cur.execute(''' INSERT INTO "PeriodOdds" ( league, "gameId", "gameDate", "homeTeam", "awayTeam", period, "spreadHome", "spreadAway", total, "moneylineHome", "moneylineAway", "snapshotType", "snapshotAt", bookmaker, source ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'historical', %s, %s, 'theoddsapi_backfill') ON CONFLICT ("league", "gameId", "period", "snapshotType") DO UPDATE SET "spreadHome" = COALESCE(EXCLUDED."spreadHome", "PeriodOdds"."spreadHome"), "spreadAway" = COALESCE(EXCLUDED."spreadAway", "PeriodOdds"."spreadAway"), total = COALESCE(EXCLUDED.total, "PeriodOdds".total), "moneylineHome" = COALESCE(EXCLUDED."moneylineHome", "PeriodOdds"."moneylineHome"), "moneylineAway" = COALESCE(EXCLUDED."moneylineAway", "PeriodOdds"."moneylineAway"), "snapshotAt" = EXCLUDED."snapshotAt" ''', ( league, event_id, commence_dt, home, away, period, sp_home, sp_away, tot_val, ml_home, ml_away, ts, book_name )) total += 1 if not dry_run: conn.commit() progress[pkey] = 'done' if dates.index(date_str) % 5 == 0: if not dry_run: save_progress(progress) if not dry_run: save_progress(progress) print(f"\nTier 3 complete: {total} period records") return total # ============================================================================ # TIER 4: Historical Alt Lines # ============================================================================ def tier4_historical_alt_lines(fetcher: HistoricalFetcher, conn, leagues: List[str], from_date: str, to_date: str, dry_run: bool = False): """Backfill historical alternate spreads and totals""" print(f"\n{'='*60}") print(f"TIER 4: HISTORICAL ALT LINES BACKFILL") print(f"Date range: {from_date} to {to_date}") print(f"{'='*60}") progress = load_progress() cur = conn.cursor() total = 0 dates = generate_dates(from_date, to_date) alt_markets = ['alternate_spreads', 'alternate_totals'] for league in leagues: sport_key = CORE_SPORTS.get(league) if not sport_key: continue print(f"\n--- {league.upper()} ---") for date_str in dates: pkey = get_progress_key(4, league, date_str) if progress.get(pkey) == 'done': continue if not fetcher.check_budget(): save_progress(progress) return total # Get events first (1 credit) events_ts = f"{date_str}T18:00:00Z" events_data = fetcher.get_historical_events(sport_key, events_ts) time.sleep(1) if not events_data or not events_data.get('data'): progress[pkey] = 'done' continue events = events_data['data'] for event in events: event_id = event.get('id') home = normalize_to_full(event.get('home_team', ''), league) away = normalize_to_full(event.get('away_team', ''), league) commence = event.get('commence_time') commence_dt = None if commence: try: commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00')) except: pass if not fetcher.check_budget(): save_progress(progress) return total # Fetch alt lines for this event alt_ts = events_ts if commence_dt: alt_dt = commence_dt - timedelta(hours=2) alt_ts = alt_dt.strftime('%Y-%m-%dT%H:%M:%SZ') event_odds = fetcher.get_historical_event_odds( sport_key, event_id, alt_ts, alt_markets ) time.sleep(1) if not event_odds or not event_odds.get('data'): continue event_data = event_odds['data'] for book in event_data.get('bookmakers', []): book_name = book.get('key', 'unknown') for market in book.get('markets', []): market_key = market.get('key', '') alt_type = 'alt_spread' if 'spreads' in market_key else 'alt_total' for o in market.get('outcomes', []): line_value = o.get('point') price = o.get('price') name = o.get('name', '') if line_value is None or price is None: continue if alt_type == 'alt_total': over_odds = price if name == 'Over' else None under_odds = price if name == 'Under' else None else: over_odds = price if name == event.get('home_team') else None under_odds = price if name == event.get('away_team') else None if dry_run: total += 1 continue # GameOddsAltLine actual schema team_label = name if name in (event.get('home_team'), event.get('away_team')) else name opp_label = event.get('away_team') if name == event.get('home_team') else event.get('home_team') cur.execute(''' INSERT INTO "GameOddsAltLine" ( league, "gameId", "gameDate", "homeTeam", "awayTeam", team, opponent, market, bookmaker, "lineValue", "oddsAmerican", "fetchedAt", source ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill') ON CONFLICT ("league", "gameId", "bookmaker", "market", "team", "lineValue") DO UPDATE SET "oddsAmerican" = EXCLUDED."oddsAmerican", "fetchedAt" = EXCLUDED."fetchedAt" ''', ( league, event_id, commence_dt, home, away, team_label, opp_label, alt_type, book_name, line_value, price, alt_ts )) total += 1 if not dry_run: conn.commit() progress[pkey] = 'done' if dates.index(date_str) % 5 == 0: if not dry_run: save_progress(progress) print(f" {league.upper()} {date_str}: {total} alt line records, " f"quota={fetcher.quota_remaining}") if not dry_run: save_progress(progress) print(f"\nTier 4 complete: {total} alt line records") return total # ============================================================================ # TIER 5: Extended Sports # ============================================================================ def tier5_extended_sports(fetcher: HistoricalFetcher, conn, from_date: str, to_date: str, dry_run: bool = False): """Backfill core markets for extended sports (soccer, MMA, AFL, NRL, WNBA)""" print(f"\n{'='*60}") print(f"TIER 5: EXTENDED SPORTS BACKFILL") print(f"Date range: {from_date} to {to_date}") print(f"{'='*60}") # Reuse tier1 logic for extended sports extended_leagues = list(EXTENDED_SPORTS.keys()) return tier1_core_markets(fetcher, conn, extended_leagues, from_date, to_date, dry_run) # ============================================================================ # STATUS & MAIN # ============================================================================ def show_status(): """Show backfill progress status""" progress = load_progress() if not progress: print("No backfill progress found.") return # Count by tier tier_counts = {} for key, status in progress.items(): tier = key.split(':')[0] if tier not in tier_counts: tier_counts[tier] = {'done': 0, 'pending': 0} if status == 'done': tier_counts[tier]['done'] += 1 else: tier_counts[tier]['pending'] += 1 print(f"\n{'='*60}") print("BACKFILL PROGRESS") print(f"{'='*60}") for tier, counts in sorted(tier_counts.items()): total = counts['done'] + counts['pending'] pct = counts['done'] / total * 100 if total > 0 else 0 print(f" {tier}: {counts['done']}/{total} ({pct:.1f}%) complete") print(f"\nTotal entries: {len(progress)}") print(f"Progress file: {PROGRESS_FILE}") print(f"{'='*60}") def main(): parser = argparse.ArgumentParser(description='Full Historical Backfill Engine') parser.add_argument('--tier', type=str, default='1', help='Tier to run: 1|2|3|4|5|all') parser.add_argument('--from', dest='from_date', type=str, default='2025-12-22', help='Start date (YYYY-MM-DD)') parser.add_argument('--to', dest='to_date', type=str, default='2026-02-20', help='End date (YYYY-MM-DD)') parser.add_argument('--leagues', type=str, default='nba,nfl,nhl,mlb,ncaab,ncaaf', help='Comma-separated leagues') parser.add_argument('--batch', type=int, default=60, help='Days per batch') parser.add_argument('--dry-run', action='store_true', help='Preview without writing') parser.add_argument('--status', action='store_true', help='Show progress status') parser.add_argument('--reset', action='store_true', help='Reset progress for a tier') args = parser.parse_args() if args.status: show_status() return 0 if args.reset: progress = load_progress() prefix = f"tier{args.tier}:" removed = 0 keys_to_remove = [k for k in progress if k.startswith(prefix)] for k in keys_to_remove: del progress[k] removed += 1 save_progress(progress) print(f"Reset {removed} progress entries for tier {args.tier}") return 0 db_url, api_key = load_config() if not api_key: print("ERROR: THE_ODDS_API_KEY not configured") return 1 if not db_url: print("ERROR: SPORTS_DATABASE_URL not configured") return 1 fetcher = HistoricalFetcher(api_key) conn = psycopg2.connect(db_url) ensure_tables(conn) leagues = [l.strip() for l in args.leagues.split(',')] tiers = args.tier.split(',') if args.tier != 'all' else ['1', '2', '3', '4', '5'] # Log ingestion ingestion_id = None if UNIFIED_AVAILABLE and not args.dry_run: try: ingestion_id = log_ingestion_start( conn, 'theoddsapi-backfill', 'multi', 'backfill', datetime.strptime(args.from_date, '%Y-%m-%d'), datetime.strptime(args.to_date, '%Y-%m-%d') ) except Exception as e: print(f"Warning: Could not log ingestion: {e}") total = 0 print(f"\n{'#'*60}") print(f"FULL HISTORICAL BACKFILL") print(f"Tiers: {', '.join(tiers)}") print(f"Range: {args.from_date} to {args.to_date}") print(f"Leagues: {', '.join(leagues)}") print(f"Dry run: {args.dry_run}") print(f"{'#'*60}") # Check current quota fetcher._request('/sports/') print(f"Starting quota: {fetcher.quota_used} used, {fetcher.quota_remaining} remaining") try: for tier in tiers: if tier == '1': total += tier1_core_markets( fetcher, conn, leagues, args.from_date, args.to_date, args.dry_run) elif tier == '2': total += tier2_historical_props( fetcher, conn, leagues, args.from_date, args.to_date, args.dry_run) elif tier == '3': total += tier3_historical_periods( fetcher, conn, leagues, args.from_date, args.to_date, args.dry_run) elif tier == '4': total += tier4_historical_alt_lines( fetcher, conn, leagues, args.from_date, args.to_date, args.dry_run) elif tier == '5': total += tier5_extended_sports( fetcher, conn, args.from_date, args.to_date, args.dry_run) if not fetcher.check_budget(): break finally: if UNIFIED_AVAILABLE and ingestion_id and not args.dry_run: try: log_ingestion_complete( conn, ingestion_id, odds_created=total, metadata={'tiers': tiers, 'from': args.from_date, 'to': args.to_date}, status='completed' ) except Exception as e: print(f"Warning: Could not log completion: {e}") print(f"\n{'#'*60}") print(f"BACKFILL SUMMARY") print(f" Total records: {total}") print(f" API requests: {fetcher.requests_made}") print(f" Quota used: {fetcher.quota_used}") print(f" Quota remaining: {fetcher.quota_remaining}") if args.dry_run: print(f" MODE: DRY RUN (no data written)") print(f"{'#'*60}") conn.close() return 0 if __name__ == '__main__': exit(main())