#!/usr/bin/env python3
"""
Ingest Upcoming Game Schedules
Creates SportsGame records for upcoming games from The Odds API.
This ensures we have games to match odds against.

Run: Every 4 hours
"""
import requests
import psycopg2
import os
import time
import argparse
from datetime import datetime, timezone, timedelta

# Rate limit delay between API calls (seconds) to avoid 429 errors
API_RATE_LIMIT_DELAY = 1.5

ODDS_API_BASE = 'https://api.the-odds-api.com/v4'

# Sport key mapping (same as scrape_odds_api.py)
SPORTS = {
    'nba': 'basketball_nba',
    'nfl': 'americanfootball_nfl',
    'mlb': 'baseball_mlb',
    'nhl': 'icehockey_nhl',
    'ncaaf': 'americanfootball_ncaaf',
    'ncaab': 'basketball_ncaab',
    'mls': 'soccer_usa_mls',
    'mma': 'mma_mixed_martial_arts',
    'epl': 'soccer_epl',
    'laliga': 'soccer_spain_la_liga',
    'seriea': 'soccer_italy_serie_a',
    'bundesliga': 'soccer_germany_bundesliga',
    'ligue1': 'soccer_france_ligue_one',
    'ucl': 'soccer_uefa_champs_league',
    'uel': 'soccer_uefa_europa_league',
}

# Priority leagues to always check (includes European soccer)
PRIORITY_LEAGUES = ['nba', 'nfl', 'nhl', 'mlb', 'ncaab', 'ncaaf', 'epl', 'laliga', 'seriea', 'bundesliga', 'ligue1', 'mma']


def load_db_url():
    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 load_api_key():
    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('THE_ODDS_API_KEY='):
                        return line.split('=', 1)[1].strip()
        except FileNotFoundError:
            continue
    return None


def fetch_upcoming_events(sport_key):
    """Fetch upcoming events from The Odds API (events only, no odds to save quota)"""
    api_key = load_api_key()
    if not api_key:
        print('No API key found')
        return []

    # Use the events endpoint (cheaper than odds endpoint)
    url = f'{ODDS_API_BASE}/sports/{sport_key}/events'
    params = {
        'apiKey': api_key,
        'dateFormat': 'iso',
    }

    try:
        resp = requests.get(url, params=params, timeout=30)
        if resp.status_code == 401:
            print('  Invalid API key')
            return []
        if resp.status_code == 404:
            print(f'  No events for {sport_key}')
            return []
        if resp.status_code != 200:
            print(f'  Status: {resp.status_code}')
            return []

        data = resp.json()
        print(f'  Found {len(data)} upcoming events')
        return data
    except Exception as e:
        print(f'  Error: {e}')
        return []


def normalize_team_abbrev(team_name, league):
    """Create a reasonable abbreviation from team name"""
    if not team_name:
        return ''

    # Common team name to abbreviation mappings
    team_abbrevs = {
        # NBA
        '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',
        # NHL
        'anaheim ducks': 'ANA', 'arizona coyotes': 'ARI', 'boston bruins': 'BOS',
        'buffalo sabres': 'BUF', 'calgary flames': 'CGY', 'carolina hurricanes': 'CAR',
        'chicago blackhawks': 'CHI', 'colorado avalanche': 'COL', 'columbus blue jackets': 'CBJ',
        'dallas stars': 'DAL', 'detroit red wings': 'DET', 'edmonton oilers': 'EDM',
        'florida panthers': 'FLA', 'los angeles kings': 'LAK', 'minnesota wild': 'MIN',
        'montreal canadiens': 'MTL', 'nashville predators': 'NSH', 'new jersey devils': 'NJD',
        'new york islanders': 'NYI', 'new york rangers': 'NYR', 'ottawa senators': 'OTT',
        'philadelphia flyers': 'PHI', 'pittsburgh penguins': 'PIT', 'san jose sharks': 'SJS',
        'seattle kraken': 'SEA', 'st louis blues': 'STL', 'st. louis blues': 'STL',
        'tampa bay lightning': 'TBL', 'toronto maple leafs': 'TOR', 'vancouver canucks': 'VAN',
        'vegas golden knights': 'VGK', 'washington capitals': 'WSH', 'winnipeg jets': 'WPG',
        # NFL
        'arizona cardinals': 'ARI', 'atlanta falcons': 'ATL', 'baltimore ravens': 'BAL',
        'buffalo bills': 'BUF', 'carolina panthers': 'CAR', 'chicago bears': 'CHI',
        'cincinnati bengals': 'CIN', 'cleveland browns': 'CLE', 'dallas cowboys': 'DAL',
        'denver broncos': 'DEN', 'detroit lions': 'DET', 'green bay packers': 'GB',
        'houston texans': 'HOU', 'indianapolis colts': 'IND', 'jacksonville jaguars': 'JAX',
        'kansas city chiefs': 'KC', 'las vegas raiders': 'LV', 'los angeles chargers': 'LAC',
        'los angeles rams': 'LAR', 'miami dolphins': 'MIA', 'minnesota vikings': 'MIN',
        'new england patriots': 'NE', 'new orleans saints': 'NO', 'new york giants': 'NYG',
        'new york jets': 'NYJ', 'philadelphia eagles': 'PHI', 'pittsburgh steelers': 'PIT',
        'san francisco 49ers': 'SF', 'seattle seahawks': 'SEA', 'tampa bay buccaneers': 'TB',
        'tennessee titans': 'TEN', 'washington commanders': 'WAS',
    }

    name_lower = team_name.lower().strip()
    if name_lower in team_abbrevs:
        return team_abbrevs[name_lower]

    # For unknown teams (college, etc.), create abbreviation from first letters
    words = team_name.replace('-', ' ').split()
    if len(words) >= 2:
        # Use first 2-3 letters of each significant word
        abbrev = ''.join(w[0].upper() for w in words if len(w) > 2)[:5]
        return abbrev if abbrev else words[0][:6].upper()
    else:
        return words[0][:6].upper() if words else 'UNK'


def ingest_schedules(leagues=None):
    """Ingest upcoming game schedules into SportsGame table"""
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    # Ensure unique constraint exists on externalGameId
    try:
        cur.execute('''
            CREATE UNIQUE INDEX IF NOT EXISTS "SportsGame_league_externalGameId_idx"
            ON "SportsGame" (league, "externalGameId")
            WHERE "externalGameId" IS NOT NULL
        ''')
        conn.commit()
    except Exception as e:
        print(f"Note: Index may already exist: {e}")
        conn.rollback()

    target_leagues = leagues or PRIORITY_LEAGUES
    total_added = 0
    total_updated = 0

    for league in target_leagues:
        sport_key = SPORTS.get(league)
        if not sport_key:
            print(f'Unknown league: {league}')
            continue

        print(f'\nFetching {league.upper()} schedule...')
        events = fetch_upcoming_events(sport_key)

        for event in events:
            event_id = event.get('id', '')
            commence_time = event.get('commence_time', '')
            home_team = event.get('home_team', '')
            away_team = event.get('away_team', '')

            if not home_team or not away_team:
                continue

            try:
                game_date = datetime.fromisoformat(commence_time.replace('Z', '+00:00'))
            except:
                game_date = datetime.now(timezone.utc)

            # Normalize team names for SportsGame table
            home_abbrev = normalize_team_abbrev(home_team, league)
            away_abbrev = normalize_team_abbrev(away_team, league)

            # Get current season year
            season_year = game_date.year if game_date.month >= 8 else game_date.year - 1

            try:
                # Try insert, on conflict update the game date if changed
                cur.execute('''
                    INSERT INTO "SportsGame" (
                        league, season, "externalGameId", "gameDate", "homeTeam", "awayTeam",
                        status, "createdAt", "updatedAt"
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, 'scheduled', NOW(), NOW())
                    ON CONFLICT (league, "externalGameId")
                    DO UPDATE SET
                        "gameDate" = EXCLUDED."gameDate",
                        "homeTeam" = EXCLUDED."homeTeam",
                        "awayTeam" = EXCLUDED."awayTeam",
                        "updatedAt" = NOW()
                    RETURNING (xmax = 0) AS inserted
                ''', (league, season_year, event_id, game_date, home_abbrev, away_abbrev))

                result = cur.fetchone()
                if result and result[0]:
                    total_added += 1
                else:
                    total_updated += 1

            except Exception as e:
                print(f'  Error inserting game: {e}')
                conn.rollback()

        conn.commit()
        print(f'  {league.upper()}: {len(events)} events processed')

        # Rate limit to avoid 429 errors from The Odds API
        if league != target_leagues[-1]:
            time.sleep(API_RATE_LIMIT_DELAY)

    cur.close()
    conn.close()

    return {'added': total_added, 'updated': total_updated}


def main():
    parser = argparse.ArgumentParser(description='Ingest upcoming game schedules from The Odds API')
    parser.add_argument('--leagues', type=str, help='Comma-separated list of leagues (e.g., nba,nfl,ncaab)')
    args = parser.parse_args()

    leagues = None
    if args.leagues:
        leagues = [l.strip().lower() for l in args.leagues.split(',')]

    print("=" * 60)
    print("INGEST UPCOMING GAME SCHEDULES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    try:
        result = ingest_schedules(leagues=leagues)
        print(f"\n{'='*60}")
        print(f"RESULTS:")
        print(f"  New games added:    {result['added']}")
        print(f"  Existing updated:   {result['updated']}")
        print("=" * 60)
    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    main()