#!/usr/bin/env python3 """ Fetch weather data for upcoming outdoor games Uses OpenWeatherMap for forecasts, WeatherAPI.com as fallback """ import os import json import requests import psycopg2 from datetime import datetime, timezone, timedelta from pathlib import Path # Load environment def load_env(): env = {} env_path = Path('/var/www/html/eventheodds/.env') if env_path.exists(): with open(env_path, 'r') as f: for line in f: if '=' in line and not line.startswith('#'): key, val = line.strip().split('=', 1) env[key] = val return env ENV = load_env() OPENWEATHERMAP_KEY = ENV.get('OPENWEATHERMAP_API_KEY', '') WEATHERAPI_KEY = ENV.get('WEATHERAPI_KEY', '') DB_URL = ENV.get('SPORTS_DATABASE_URL', '').split('?')[0] # Load stadium data SCRIPT_DIR = Path(__file__).parent STADIUM_DATA = {} stadium_file = SCRIPT_DIR / 'stadium_data.json' if stadium_file.exists(): with open(stadium_file, 'r') as f: STADIUM_DATA = json.load(f) def get_stadium_for_team(team: str, league: str) -> dict | None: """Find stadium for a team""" league_stadiums = STADIUM_DATA.get(league, []) for stadium in league_stadiums: if team.upper() in [t.upper() for t in stadium.get('teams', [])]: return stadium # Try partial match for stadium in league_stadiums: for t in stadium.get('teams', []): if team.upper() in t.upper() or t.upper() in team.upper(): return stadium return None def fetch_openweathermap(lat: float, lon: float, game_time: datetime) -> dict | None: """Fetch forecast from OpenWeatherMap""" if not OPENWEATHERMAP_KEY: return None try: # Use 3-hour forecast API url = f"https://api.openweathermap.org/data/2.5/forecast?lat={lat}&lon={lon}&appid={OPENWEATHERMAP_KEY}&units=imperial" resp = requests.get(url, timeout=15) if resp.status_code != 200: print(f" OpenWeatherMap error: {resp.status_code}") return None data = resp.json() # Find forecast closest to game time best_forecast = None min_diff = float('inf') for forecast in data.get('list', []): fc_time = datetime.fromtimestamp(forecast['dt'], tz=timezone.utc) diff = abs((fc_time - game_time).total_seconds()) if diff < min_diff: min_diff = diff best_forecast = forecast if not best_forecast: return None main = best_forecast.get('main', {}) wind = best_forecast.get('wind', {}) weather = best_forecast.get('weather', [{}])[0] # Convert wind degrees to cardinal direction deg = wind.get('deg', 0) directions = ['N', 'NNE', 'NE', 'ENE', 'E', 'ESE', 'SE', 'SSE', 'S', 'SSW', 'SW', 'WSW', 'W', 'WNW', 'NW', 'NNW'] wind_dir = directions[int((deg + 11.25) / 22.5) % 16] return { 'temperature': main.get('temp'), 'feelsLike': main.get('feels_like'), 'humidity': main.get('humidity'), 'windSpeed': wind.get('speed'), 'windDegree': deg, 'windDirection': wind_dir, 'condition': weather.get('main', 'Unknown'), 'description': weather.get('description', ''), 'precipChance': int(best_forecast.get('pop', 0) * 100), 'visibility': best_forecast.get('visibility', 10000) / 1609.34, # meters to miles 'source': 'openweathermap', 'forecastTime': datetime.fromtimestamp(best_forecast['dt'], tz=timezone.utc), 'raw': best_forecast } except Exception as e: print(f" OpenWeatherMap fetch error: {e}") return None def fetch_weatherapi(lat: float, lon: float, game_time: datetime) -> dict | None: """Fetch forecast from WeatherAPI.com (fallback)""" if not WEATHERAPI_KEY: return None try: # Format date for API date_str = game_time.strftime('%Y-%m-%d') url = f"http://api.weatherapi.com/v1/forecast.json?key={WEATHERAPI_KEY}&q={lat},{lon}&dt={date_str}&hour={game_time.hour}" resp = requests.get(url, timeout=15) if resp.status_code != 200: print(f" WeatherAPI error: {resp.status_code}") return None data = resp.json() # Get the hourly forecast closest to game time forecast_day = data.get('forecast', {}).get('forecastday', [{}])[0] hours = forecast_day.get('hour', []) best_hour = None min_diff = float('inf') for hour in hours: hour_time = datetime.fromtimestamp(hour['time_epoch'], tz=timezone.utc) diff = abs((hour_time - game_time).total_seconds()) if diff < min_diff: min_diff = diff best_hour = hour if not best_hour: return None return { 'temperature': best_hour.get('temp_f'), 'feelsLike': best_hour.get('feelslike_f'), 'humidity': best_hour.get('humidity'), 'windSpeed': best_hour.get('wind_mph'), 'windDegree': best_hour.get('wind_degree'), 'windDirection': best_hour.get('wind_dir'), 'condition': best_hour.get('condition', {}).get('text', 'Unknown'), 'precipChance': best_hour.get('chance_of_rain', 0), 'precipitation': best_hour.get('precip_in', 0), 'visibility': best_hour.get('vis_miles'), 'source': 'weatherapi', 'forecastTime': datetime.fromtimestamp(best_hour['time_epoch'], tz=timezone.utc), 'raw': best_hour } except Exception as e: print(f" WeatherAPI fetch error: {e}") return None def calculate_betting_impact(weather: dict, league: str) -> dict: """Calculate betting impact based on weather conditions""" wind_speed = weather.get('windSpeed') or 0 temp = weather.get('temperature') or 70 precip_chance = weather.get('precipChance') or 0 condition = (weather.get('condition') or '').lower() # Wind impact (affects passing, kicking, home runs) wind_impact = 'NONE' if wind_speed >= 20: wind_impact = 'HIGH' elif wind_speed >= 12: wind_impact = 'MEDIUM' elif wind_speed >= 8: wind_impact = 'LOW' # Temperature impact temp_impact = 'NORMAL' if temp <= 32: temp_impact = 'COLD' elif temp >= 90: temp_impact = 'HOT' # Precipitation impact precip_impact = 'DRY' if precip_chance >= 50 or 'rain' in condition or 'snow' in condition: precip_impact = 'WET' # Overall impact overall_impact = 'MINIMAL' if wind_impact == 'HIGH' or temp_impact == 'COLD' or precip_impact == 'WET': overall_impact = 'SIGNIFICANT' elif wind_impact == 'MEDIUM' or precip_chance >= 30: overall_impact = 'MODERATE' # Calculate prop adjustments passing_adjust = 0 kicking_adjust = 0 homerun_adjust = 0 if league == 'nfl': # Wind affects passing if wind_speed >= 20: passing_adjust = -15 kicking_adjust = -20 elif wind_speed >= 15: passing_adjust = -10 kicking_adjust = -15 elif wind_speed >= 10: passing_adjust = -5 kicking_adjust = -8 # Cold affects everything if temp <= 32: passing_adjust -= 5 kicking_adjust -= 10 # Rain/snow if precip_impact == 'WET': passing_adjust -= 10 kicking_adjust -= 5 elif league == 'mlb': # Wind out = more home runs, wind in = fewer wind_dir = weather.get('windDirection', '') if wind_speed >= 10: if wind_dir in ['N', 'NE', 'NW']: # Wind blowing out homerun_adjust = 10 + (wind_speed - 10) elif wind_dir in ['S', 'SE', 'SW']: # Wind blowing in homerun_adjust = -10 - (wind_speed - 10) # Cold = ball doesn't carry if temp <= 50: homerun_adjust -= 5 elif temp >= 85: homerun_adjust += 5 return { 'windImpact': wind_impact, 'tempImpact': temp_impact, 'precipImpact': precip_impact, 'overallImpact': overall_impact, 'passingAdjust': passing_adjust, 'kickingAdjust': kicking_adjust, 'homeRunAdjust': homerun_adjust } def ingest_weather_for_games(): """Fetch weather for upcoming games""" if not DB_URL: print("Error: SPORTS_DATABASE_URL not found") return conn = psycopg2.connect(DB_URL) cur = conn.cursor() # Get upcoming outdoor games (next 5 days) now = datetime.now(timezone.utc) end_date = now + timedelta(days=5) print(f"Fetching weather for games from {now.date()} to {end_date.date()}") # Query upcoming games from both GameOdds and SportsGame # SportsGame uses abbreviations (DEN, BUF), GameOdds uses full names cur.execute(''' SELECT DISTINCT league, "homeTeam", "awayTeam", "gameDate", "externalGameId" as game_id FROM "SportsGame" WHERE "gameDate" >= %s AND "gameDate" <= %s AND league IN ('nfl', 'mlb', 'epl') UNION SELECT DISTINCT league, "homeTeam", "awayTeam", "gameDate", "gameId" as game_id FROM "GameOdds" WHERE "gameDate" >= %s AND "gameDate" <= %s AND league IN ('nfl', 'mlb', 'soccer_epl') ORDER BY "gameDate" ''', (now, end_date, now, end_date)) games = cur.fetchall() print(f"Found {len(games)} upcoming games") weather_added = 0 weather_skipped = 0 for league, home_team, away_team, game_date, game_id in games: # Find stadium stadium = get_stadium_for_team(home_team, league) if not stadium: print(f" No stadium found for {home_team} ({league})") weather_skipped += 1 continue if stadium.get('indoor', False): print(f" {home_team} plays indoors - skipping weather") weather_skipped += 1 continue lat = stadium.get('lat') lon = stadium.get('lon') if not lat or not lon: print(f" No coordinates for {stadium.get('name')}") weather_skipped += 1 continue # Fetch weather print(f" Fetching weather for {away_team} @ {home_team} on {game_date}") weather = fetch_openweathermap(lat, lon, game_date) if not weather: weather = fetch_weatherapi(lat, lon, game_date) if not weather: print(f" Failed to fetch weather") weather_skipped += 1 continue # Calculate betting impact impact = calculate_betting_impact(weather, league) # Generate game ID if not present if not game_id: game_id = f"{league}_{game_date.strftime('%Y%m%d')}_{home_team}_{away_team}" # Insert/update weather try: cur.execute(''' INSERT INTO "GameWeather" (league, "gameId", "gameDate", "homeTeam", "awayTeam", "stadiumName", latitude, longitude, "isIndoor", temperature, "feelsLike", humidity, "windSpeed", "windDirection", "windDegree", precipitation, "precipChance", condition, visibility, "windImpact", "tempImpact", "precipImpact", "overallImpact", "passingAdjust", "kickingAdjust", "homeRunAdjust", source, "forecastTime", raw, "updatedAt") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (league, "gameId", "forecastTime") DO UPDATE SET temperature = EXCLUDED.temperature, "feelsLike" = EXCLUDED."feelsLike", humidity = EXCLUDED.humidity, "windSpeed" = EXCLUDED."windSpeed", "windDirection" = EXCLUDED."windDirection", "windDegree" = EXCLUDED."windDegree", "precipChance" = EXCLUDED."precipChance", condition = EXCLUDED.condition, "windImpact" = EXCLUDED."windImpact", "tempImpact" = EXCLUDED."tempImpact", "precipImpact" = EXCLUDED."precipImpact", "overallImpact" = EXCLUDED."overallImpact", "passingAdjust" = EXCLUDED."passingAdjust", "kickingAdjust" = EXCLUDED."kickingAdjust", "homeRunAdjust" = EXCLUDED."homeRunAdjust", raw = EXCLUDED.raw, "updatedAt" = NOW() ''', ( league, game_id, game_date, home_team, away_team, stadium.get('name'), lat, lon, False, weather.get('temperature'), weather.get('feelsLike'), weather.get('humidity'), weather.get('windSpeed'), weather.get('windDirection'), weather.get('windDegree'), weather.get('precipitation'), weather.get('precipChance'), weather.get('condition'), weather.get('visibility'), impact['windImpact'], impact['tempImpact'], impact['precipImpact'], impact['overallImpact'], impact['passingAdjust'], impact['kickingAdjust'], impact['homeRunAdjust'], weather.get('source'), weather.get('forecastTime'), json.dumps(weather.get('raw')) )) conn.commit() weather_added += 1 print(f" ✓ {weather.get('temperature')}°F, Wind {weather.get('windSpeed')}mph {weather.get('windDirection')}, {impact['overallImpact']} impact") except Exception as e: conn.rollback() print(f" Error inserting weather: {e}") print(f"\n✅ Weather ingestion complete: {weather_added} added, {weather_skipped} skipped") # Summary cur.execute(''' SELECT league, "overallImpact", COUNT(*) FROM "GameWeather" WHERE "gameDate" >= NOW() GROUP BY league, "overallImpact" ORDER BY league, "overallImpact" ''') print("\nWeather Impact Summary:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]} = {row[2]} games") cur.close() conn.close() if __name__ == '__main__': ingest_weather_for_games()