#!/usr/bin/env python3 """ Sync GameOdds → SportsGame Copies best available odds from GameOdds table into SportsGame embedded fields Run: Every 30 minutes (after scrape_odds_api.py) Enhanced with fuzzy matching for college teams. """ import os import psycopg2 from datetime import datetime, timezone, timedelta import re from difflib import SequenceMatcher # Team name mappings: Full name -> common abbreviations/nicknames TEAM_ALIASES = { # NBA 'atlanta hawks': ['atl', 'hawks'], 'boston celtics': ['bos', 'celtics'], 'brooklyn nets': ['bkn', 'nets'], 'charlotte hornets': ['cha', 'hornets'], 'chicago bulls': ['chi', 'bulls'], 'cleveland cavaliers': ['cle', 'cavaliers', 'cavs'], 'dallas mavericks': ['dal', 'mavericks', 'mavs'], 'denver nuggets': ['den', 'nuggets'], 'detroit pistons': ['det', 'pistons'], 'golden state warriors': ['gsw', 'gs', 'warriors'], 'houston rockets': ['hou', 'rockets'], 'indiana pacers': ['ind', 'pacers'], 'los angeles clippers': ['lac', 'clippers'], 'los angeles lakers': ['lal', 'lakers'], 'memphis grizzlies': ['mem', 'grizzlies'], 'miami heat': ['mia', 'heat'], 'milwaukee bucks': ['mil', 'bucks'], 'minnesota timberwolves': ['min', 'timberwolves', 'wolves'], 'new orleans pelicans': ['nop', 'no', 'pelicans'], 'new york knicks': ['nyk', 'ny', 'knicks'], 'oklahoma city thunder': ['okc', 'thunder'], 'orlando magic': ['orl', 'magic'], 'philadelphia 76ers': ['phi', '76ers', 'sixers'], 'phoenix suns': ['phx', 'suns'], 'portland trail blazers': ['por', 'blazers', 'trailblazers'], 'sacramento kings': ['sac', 'kings'], 'san antonio spurs': ['sas', 'sa', 'spurs'], 'toronto raptors': ['tor', 'raptors'], 'utah jazz': ['uta', 'utah', 'jazz'], 'washington wizards': ['was', 'wsh', 'wizards'], # NHL 'anaheim ducks': ['ana', 'ducks'], 'arizona coyotes': ['ari', 'coyotes'], 'boston bruins': ['bos', 'bruins'], 'buffalo sabres': ['buf', 'sabres'], 'calgary flames': ['cgy', 'cal', 'flames'], 'carolina hurricanes': ['car', 'hurricanes', 'canes'], 'chicago blackhawks': ['chi', 'blackhawks', 'hawks'], 'colorado avalanche': ['col', 'avalanche', 'avs'], 'columbus blue jackets': ['cbj', 'bluejackets', 'jackets'], 'dallas stars': ['dal', 'stars'], 'detroit red wings': ['det', 'redwings', 'wings'], 'edmonton oilers': ['edm', 'oilers'], 'florida panthers': ['fla', 'panthers'], 'los angeles kings': ['lak', 'la', 'kings'], 'minnesota wild': ['min', 'wild'], 'montreal canadiens': ['mtl', 'canadiens', 'habs'], 'nashville predators': ['nsh', 'predators', 'preds'], 'new jersey devils': ['njd', 'nj', 'devils'], 'new york islanders': ['nyi', 'islanders', 'isles'], 'new york rangers': ['nyr', 'rangers'], 'ottawa senators': ['ott', 'senators', 'sens'], 'philadelphia flyers': ['phi', 'flyers'], 'pittsburgh penguins': ['pit', 'penguins', 'pens'], 'san jose sharks': ['sjs', 'sj', 'sharks'], 'seattle kraken': ['sea', 'kraken'], 'st louis blues': ['stl', 'blues'], 'st. louis blues': ['stl', 'blues'], 'tampa bay lightning': ['tbl', 'tb', 'lightning', 'bolts'], 'toronto maple leafs': ['tor', 'mapleleafs', 'leafs'], 'vancouver canucks': ['van', 'canucks'], 'vegas golden knights': ['vgk', 'vegas', 'goldenknights', 'knights'], 'washington capitals': ['wsh', 'was', 'capitals', 'caps'], 'winnipeg jets': ['wpg', 'jets'], # NFL 'arizona cardinals': ['ari', 'cardinals', 'cards'], 'atlanta falcons': ['atl', 'falcons'], 'baltimore ravens': ['bal', 'ravens'], 'buffalo bills': ['buf', 'bills'], 'carolina panthers': ['car', 'panthers'], 'chicago bears': ['chi', 'bears'], 'cincinnati bengals': ['cin', 'bengals'], 'cleveland browns': ['cle', 'browns'], 'dallas cowboys': ['dal', 'cowboys'], 'denver broncos': ['den', 'broncos'], 'detroit lions': ['det', 'lions'], 'green bay packers': ['gb', 'packers'], 'houston texans': ['hou', 'texans'], 'indianapolis colts': ['ind', 'colts'], 'jacksonville jaguars': ['jax', 'jaguars', 'jags'], 'kansas city chiefs': ['kc', 'chiefs'], 'las vegas raiders': ['lv', 'raiders'], 'los angeles chargers': ['lac', 'chargers'], 'los angeles rams': ['lar', 'rams'], 'miami dolphins': ['mia', 'dolphins'], 'minnesota vikings': ['min', 'vikings'], 'new england patriots': ['ne', 'patriots', 'pats'], 'new orleans saints': ['no', 'saints'], 'new york giants': ['nyg', 'giants'], 'new york jets': ['nyj', 'jets'], 'philadelphia eagles': ['phi', 'eagles'], 'pittsburgh steelers': ['pit', 'steelers'], 'san francisco 49ers': ['sf', '49ers', 'niners'], 'seattle seahawks': ['sea', 'seahawks'], 'tampa bay buccaneers': ['tb', 'buccaneers', 'bucs'], 'tennessee titans': ['ten', 'titans'], 'washington commanders': ['was', 'wsh', 'commanders'], # EPL 'arsenal': ['ars', 'arsenal'], 'aston villa': ['avl', 'astonvilla', 'villa'], 'bournemouth': ['bou', 'bournemouth'], 'brentford': ['bre', 'brentford'], 'brighton and hove albion': ['bha', 'brighton'], 'burnley': ['bur', 'burnley'], 'chelsea': ['che', 'chelsea'], 'crystal palace': ['cry', 'crystalpalace', 'palace'], 'everton': ['eve', 'everton'], 'fulham': ['ful', 'fulham'], 'leeds united': ['lee', 'leeds'], 'leicester city': ['lei', 'leicester'], 'liverpool': ['liv', 'liverpool'], 'luton town': ['lut', 'luton'], 'manchester city': ['mci', 'mancity', 'city'], 'manchester united': ['mun', 'manutd', 'united'], 'newcastle united': ['new', 'newcastle'], 'nottingham forest': ['nfo', 'nottingham', 'forest'], 'sheffield united': ['shu', 'sheffield'], 'tottenham hotspur': ['tot', 'tottenham', 'spurs'], 'west ham united': ['whu', 'westham'], 'wolverhampton wanderers': ['wol', 'wolves'], # La Liga 'atletico madrid': ['atm', 'atletico'], 'real madrid': ['rma', 'realmadrid'], 'barcelona': ['bar', 'barca', 'barcelona'], 'sevilla': ['sev', 'sevilla'], 'valencia': ['val', 'valencia'], 'real betis': ['bet', 'betis'], 'villarreal': ['vil', 'villarreal'], 'athletic bilbao': ['ath', 'bilbao'], 'real sociedad': ['rso', 'sociedad'], # Serie A 'juventus': ['juv', 'juventus'], 'inter milan': ['int', 'inter'], 'ac milan': ['mil', 'milan', 'acmilan'], 'napoli': ['nap', 'napoli'], 'roma': ['rom', 'roma', 'asroma'], 'lazio': ['laz', 'lazio'], 'atalanta': ['ata', 'atalanta'], 'fiorentina': ['fio', 'fiorentina'], # Bundesliga 'bayern munich': ['bay', 'bayern'], 'borussia dortmund': ['bvb', 'dortmund'], 'rb leipzig': ['rbl', 'leipzig'], 'bayer leverkusen': ['b04', 'leverkusen'], 'eintracht frankfurt': ['sge', 'frankfurt'], } def extract_nickname(full_name): """Extract the team nickname from full name (e.g., 'Texas Tech Red Raiders' -> 'red raiders')""" if not full_name: return '' name = full_name.lower().strip() # Remove common suffixes name = re.sub(r'\s+(st|state|university|college)$', '', name) # Split and get the last 1-2 words as nickname words = name.split() if len(words) >= 2: # Try last 2 words first (e.g., "Red Raiders", "Blue Devils") return ' '.join(words[-2:]) elif len(words) == 1: return words[0] return name def similarity(a, b): """Calculate similarity ratio between two strings""" return SequenceMatcher(None, a.lower(), b.lower()).ratio() def normalize_team_name(name): """Extract normalized team identifier for matching.""" if not name: return '' n = name.lower().strip() # Remove common suffixes n = re.sub(r'\s+(fc|sc|cf|afc|united|city)$', '', n) # Check if it matches a known team full name if n in TEAM_ALIASES: return TEAM_ALIASES[n][0] # Check if it's an abbreviation or nickname (search all aliases) for full_name, aliases in TEAM_ALIASES.items(): if n in aliases: return aliases[0] # Also check last word of full name (nickname) nickname = full_name.split()[-1] if n == nickname: return aliases[0] # If single short string (likely abbreviation), return as-is if len(n) <= 5: return n # Return last word as nickname for unrecognized teams words = n.split() return words[-1] if words else n def fuzzy_match_teams(odds_home, odds_away, sg_home, sg_away, threshold=0.6): """ Fuzzy match teams using multiple strategies: 1. Exact normalized match 2. Nickname matching 3. Substring matching 4. Similarity ratio """ norm_oh = normalize_team_name(odds_home) norm_oa = normalize_team_name(odds_away) norm_sh = normalize_team_name(sg_home) norm_sa = normalize_team_name(sg_away) # Strategy 1: Exact normalized match if (norm_oh == norm_sh and norm_oa == norm_sa): return True, False # match, not swapped if (norm_oh == norm_sa and norm_oa == norm_sh): return True, True # match, swapped # Strategy 2: Nickname matching nick_oh = extract_nickname(odds_home) nick_oa = extract_nickname(odds_away) nick_sh = extract_nickname(sg_home) nick_sa = extract_nickname(sg_away) if nick_oh and nick_sh: if similarity(nick_oh, nick_sh) > threshold and similarity(nick_oa, nick_sa) > threshold: return True, False if similarity(nick_oh, nick_sa) > threshold and similarity(nick_oa, nick_sh) > threshold: return True, True # Strategy 3: Check if abbreviation is in full name oh_lower = odds_home.lower() if odds_home else '' oa_lower = odds_away.lower() if odds_away else '' sh_lower = sg_home.lower() if sg_home else '' sa_lower = sg_away.lower() if sg_away else '' # Check substring matches (abbreviation in full name or vice versa) home_match = (norm_sh in oh_lower or norm_oh in sh_lower or (len(norm_sh) > 2 and norm_sh[:3] in oh_lower[:10])) away_match = (norm_sa in oa_lower or norm_oa in sa_lower or (len(norm_sa) > 2 and norm_sa[:3] in oa_lower[:10])) if home_match and away_match: return True, False # Check swapped home_match_swap = (norm_sh in oa_lower or norm_oh in sa_lower or (len(norm_sh) > 2 and norm_sh[:3] in oa_lower[:10])) away_match_swap = (norm_sa in oh_lower or norm_oa in sh_lower or (len(norm_sa) > 2 and norm_sa[:3] in oh_lower[:10])) if home_match_swap and away_match_swap: return True, True # Strategy 4: Similarity ratio on full names sim_home = similarity(oh_lower, sh_lower) sim_away = similarity(oa_lower, sa_lower) if sim_home > threshold and sim_away > threshold: return True, False sim_home_swap = similarity(oh_lower, sa_lower) sim_away_swap = similarity(oa_lower, sh_lower) if sim_home_swap > threshold and sim_away_swap > threshold: return True, True return False, False def load_db_url(): """Load database URL from environment""" env_paths = [ '/var/www/html/eventheodds/.env', os.path.join(os.path.dirname(__file__), '..', '.env'), ] for env_path in env_paths: 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: continue return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] def sync_odds(conn, lookback_days=14, lookahead_days=14): """ Sync odds from GameOdds to SportsGame for games within date range. Uses intelligent team name matching with fuzzy matching for college teams. """ cur = conn.cursor() stats = {'matched': 0, 'updated': 0, 'errors': 0, 'checked': 0, 'fuzzy_matched': 0} # Get all odds from GameOdds in date range print(f"Loading GameOdds data...") cur.execute(''' SELECT league, DATE("gameDate") as gd, "homeTeam", "awayTeam", "bookmaker", "market", "lineValue", "homeOdds", "awayOdds", "overOdds", "underOdds" FROM "GameOdds" WHERE "gameDate" >= NOW() - INTERVAL '%s days' AND "gameDate" <= NOW() + INTERVAL '%s days' ORDER BY CASE WHEN "bookmaker" = 'pinnacle' THEN 0 ELSE 1 END ''' % (lookback_days, lookahead_days)) odds_data = cur.fetchall() print(f"Loaded {len(odds_data)} odds records") # Group odds by league+date+teams (using normalized names) odds_by_game = {} # Also keep original names for fuzzy matching odds_by_league_date = {} # (league, date) -> list of (home, away, odds_dict) for row in odds_data: league, gd, home, away, book, market, line, h_odds, a_odds, over_o, under_o = row key = (league, str(gd), normalize_team_name(home), normalize_team_name(away)) if key not in odds_by_game: odds_by_game[key] = {'h2h': None, 'spreads': None, 'totals': None, 'home_orig': home, 'away_orig': away} if market == 'h2h' and odds_by_game[key]['h2h'] is None: odds_by_game[key]['h2h'] = (h_odds, a_odds) elif market == 'spreads' and odds_by_game[key]['spreads'] is None: odds_by_game[key]['spreads'] = (line, h_odds, a_odds) elif market == 'totals' and odds_by_game[key]['totals'] is None: odds_by_game[key]['totals'] = line # Store by league+date for fuzzy matching ld_key = (league, str(gd)) if ld_key not in odds_by_league_date: odds_by_league_date[ld_key] = [] # Avoid duplicates entry = (home, away, key) if entry not in [(e[0], e[1], e[2]) for e in odds_by_league_date[ld_key]]: odds_by_league_date[ld_key].append(entry) print(f"Grouped into {len(odds_by_game)} unique games") # Get SportsGame records needing odds print(f"Finding SportsGame records to update...") cur.execute(''' SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= NOW() - INTERVAL '%s days' AND "gameDate" <= NOW() + INTERVAL '%s days' AND ("moneylineHome" IS NULL OR "oddsSource" = 'odds-api') ''' % (lookback_days, lookahead_days)) games = cur.fetchall() print(f"Found {len(games)} games to check") for game in games: game_id, league, gd, home_team, away_team = game stats['checked'] += 1 # Normalize team names and look for match norm_home = normalize_team_name(home_team) norm_away = normalize_team_name(away_team) key = (league, str(gd), norm_home, norm_away) odds = odds_by_game.get(key) swapped = False if not odds: # Try swapped teams key_swapped = (league, str(gd), norm_away, norm_home) odds = odds_by_game.get(key_swapped) if odds: swapped = True # If still no match, try fuzzy matching for college sports if not odds and league in ('ncaab', 'ncaaf'): ld_key = (league, str(gd)) candidates = odds_by_league_date.get(ld_key, []) for cand_home, cand_away, cand_key in candidates: matched, is_swapped = fuzzy_match_teams(cand_home, cand_away, home_team, away_team) if matched: odds = odds_by_game.get(cand_key) swapped = is_swapped stats['fuzzy_matched'] += 1 break if not odds: continue # If swapped, adjust the odds if swapped: if odds.get('h2h'): odds['h2h'] = (odds['h2h'][1], odds['h2h'][0]) if odds.get('spreads'): odds['spreads'] = (-odds['spreads'][0] if odds['spreads'][0] else None, odds['spreads'][2], odds['spreads'][1]) stats['matched'] += 1 # Extract odds values ml_home = ml_away = None if odds.get('h2h'): ml_home, ml_away = odds['h2h'] sp_val = None if odds.get('spreads'): sp_val = odds['spreads'][0] total = odds.get('totals') if ml_home or sp_val or total: try: cur.execute(''' UPDATE "SportsGame" SET "moneylineHome" = COALESCE(%s, "moneylineHome"), "moneylineAway" = COALESCE(%s, "moneylineAway"), "spreadHome" = COALESCE(%s, "spreadHome"), "spreadAway" = COALESCE(%s, "spreadAway"), "total" = COALESCE(%s, "total"), "oddsSource" = 'odds-api', "oddsUpdatedAt" = NOW(), "updatedAt" = NOW() WHERE id = %s ''', ( ml_home, ml_away, sp_val, -sp_val if sp_val else None, total, game_id )) stats['updated'] += 1 except Exception as e: print(f" Error updating game {game_id}: {e}") stats['errors'] += 1 conn.rollback() conn.commit() cur.close() return stats def main(): print("=" * 60) print("SYNC GAMEODDS → SPORTSGAME") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) db_url = load_db_url() if not db_url: print("ERROR: SPORTS_DATABASE_URL not found") return try: conn = psycopg2.connect(db_url) # Run sync with intelligent team matching stats = sync_odds(conn) print(f"\n{'='*60}") print(f"RESULTS:") print(f" Checked: {stats['checked']} games") print(f" Matched: {stats['matched']} games") print(f" Fuzzy Match: {stats['fuzzy_matched']} games") print(f" Updated: {stats['updated']} games") print(f" Errors: {stats['errors']}") print("=" * 60) conn.close() except Exception as e: print(f"ERROR: {e}") import traceback traceback.print_exc() if __name__ == '__main__': main()