#!/usr/bin/env python3 """ The Odds API - Alternate Spreads Ingestion Fetches alternate spread lines (±X) and stores them for ROI analysis. """ import json import os import time from datetime import datetime, timezone from typing import Dict, List, Optional import psycopg2 import requests ODDS_API_BASE = "https://api.the-odds-api.com/v4" SPORTS = { "nba": "basketball_nba", "nfl": "americanfootball_nfl", "nhl": "icehockey_nhl", "mlb": "baseball_mlb", "ncaab": "basketball_ncaab", "ncaaf": "americanfootball_ncaaf", } NBA_TEAM_MAP = { "atlanta hawks": "ATL", "boston celtics": "BOS", "brooklyn nets": "BKN", "charlotte hornets": "CHA", "chicago bulls": "CHI", "cleveland cavaliers": "CLE", "dallas mavericks": "DAL", "denver nuggets": "DEN", "detroit pistons": "DET", "golden state warriors": "GSW", "houston rockets": "HOU", "indiana pacers": "IND", "los angeles clippers": "LAC", "los angeles lakers": "LAL", "memphis grizzlies": "MEM", "miami heat": "MIA", "milwaukee bucks": "MIL", "minnesota timberwolves": "MIN", "new orleans pelicans": "NOP", "new york knicks": "NYK", "oklahoma city thunder": "OKC", "orlando magic": "ORL", "philadelphia 76ers": "PHI", "phoenix suns": "PHX", "portland trail blazers": "POR", "sacramento kings": "SAC", "san antonio spurs": "SAS", "toronto raptors": "TOR", "utah jazz": "UTA", "washington wizards": "WAS", } def load_db_url() -> str: 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 load_api_key() -> Optional[str]: key = os.environ.get("THE_ODDS_API_KEY") if key: return key with open("/var/www/html/eventheodds/.env", "r") as f: for line in f: if line.startswith("THE_ODDS_API_KEY="): return line.split("=", 1)[1].strip() return None def normalize_team(name: str, league: str) -> str: if not name: return "" lower = name.lower().strip() if league == "nba": return NBA_TEAM_MAP.get(lower, name.upper()) return name.upper() def fetch_events(sport_key: str, api_key: str) -> List[Dict]: url = f"{ODDS_API_BASE}/sports/{sport_key}/events" params = {"apiKey": api_key} try: resp = requests.get(url, params=params, timeout=30) if resp.status_code == 200: return resp.json() except Exception: pass return [] def fetch_alt_spreads(sport_key: str, event_id: str, api_key: str) -> Optional[Dict]: url = f"{ODDS_API_BASE}/sports/{sport_key}/events/{event_id}/odds" params = { "apiKey": api_key, "regions": "us", "markets": "alternate_spreads", "oddsFormat": "american", } try: resp = requests.get(url, params=params, timeout=30) if resp.status_code != 200: return None return resp.json() except Exception: return None def safe_float(val): try: if val is None: return None return float(val) except Exception: return None def safe_int(val): try: if val is None: return None return int(float(str(val).replace("+", ""))) except Exception: return None def ingest_alt_spreads(leagues=None): api_key = load_api_key() if not api_key: print("No THE_ODDS_API_KEY found") return {"success": False, "error": "No API key"} db_url = load_db_url() if not db_url: raise RuntimeError("SPORTS_DATABASE_URL not set") conn = psycopg2.connect(db_url) cur = conn.cursor() cur.execute( """ CREATE TABLE IF NOT EXISTS "GameOddsAltLine" ( id BIGSERIAL PRIMARY KEY, league TEXT NOT NULL, "gameId" TEXT NOT NULL, "gameDate" TIMESTAMPTZ, "homeTeam" TEXT, "awayTeam" TEXT, "team" TEXT, "opponent" TEXT, "bookmaker" TEXT, "market" TEXT NOT NULL, "lineValue" FLOAT, "oddsAmerican" INT, "fetchedAt" TIMESTAMPTZ DEFAULT NOW(), "source" TEXT DEFAULT 'the-odds-api', raw JSONB, UNIQUE(league, "gameId", "bookmaker", "market", "team", "lineValue") ) """ ) conn.commit() sql = """ INSERT INTO "GameOddsAltLine" (league, "gameId", "gameDate", "homeTeam", "awayTeam", "team", "opponent", "bookmaker", "market", "lineValue", "oddsAmerican", "source", raw) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, "gameId", "bookmaker", "market", "team", "lineValue") DO UPDATE SET "oddsAmerican" = EXCLUDED."oddsAmerican", "fetchedAt" = NOW(), raw = EXCLUDED.raw """ target_leagues = leagues or list(SPORTS.keys()) total_added = 0 for league in target_leagues: sport_key = SPORTS.get(league) if not sport_key: continue events = fetch_events(sport_key, api_key) if not events: print(f"[{league}] No events found") continue league_added = 0 for event in events: event_id = event.get("id") if not event_id: continue alt_data = fetch_alt_spreads(sport_key, event_id, api_key) if not alt_data: continue home_team_raw = alt_data.get("home_team", "") away_team_raw = alt_data.get("away_team", "") home_team = normalize_team(home_team_raw, league) away_team = normalize_team(away_team_raw, league) commence_time = alt_data.get("commence_time", "") try: game_date = datetime.fromisoformat(commence_time.replace("Z", "+00:00")) except Exception: game_date = datetime.now(timezone.utc) for bookmaker in alt_data.get("bookmakers", []): book_key = bookmaker.get("key", "") for market in bookmaker.get("markets", []): if market.get("key") != "alternate_spreads": continue for outcome in market.get("outcomes", []): team_name = outcome.get("name", "") line_value = safe_float(outcome.get("point")) odds = safe_int(outcome.get("price")) if line_value is None or odds is None: continue team = normalize_team(team_name, league) opponent = away_team if team == home_team else home_team try: cur.execute( sql, ( league, event_id, game_date, home_team, away_team, team, opponent, book_key, "alternate_spreads", line_value, odds, "the-odds-api", json.dumps({"event": alt_data, "market": market, "outcome": outcome}), ), ) total_added += 1 league_added += 1 except Exception: conn.rollback() conn.commit() time.sleep(0.3) print(f"[{league.upper()}] Alt spreads added: {league_added}") cur.close() conn.close() print(f"✅ Alt spread lines ingested: {total_added}") return {"success": True, "stats_added": total_added} if __name__ == "__main__": import sys leagues = sys.argv[1].split(",") if len(sys.argv) > 1 else None ingest_alt_spreads(leagues)