#!/usr/bin/env python3
"""
NBA Travel Density & Circadian Analysis
Computes 3-in-4, 4-in-5, altitude impact, timezone crossing effects from SportsDB.
"""
import psycopg2
from datetime import datetime, timezone, timedelta
from collections import defaultdict
import sys

HIGH_ALTITUDE = {'DEN', 'UTA', 'SLC'}
TIMEZONES = {
    'BOS': 'ET', 'BKN': 'ET', 'NYK': 'ET', 'PHI': 'ET', 'TOR': 'ET',
    'CLE': 'ET', 'DET': 'ET', 'IND': 'ET', 'MIA': 'ET', 'ORL': 'ET',
    'ATL': 'ET', 'CHA': 'ET', 'WAS': 'ET', 'CHI': 'CT', 'MIL': 'CT',
    'MIN': 'CT', 'OKC': 'CT', 'MEM': 'CT', 'NOP': 'CT', 'SAS': 'CT',
    'HOU': 'CT', 'DAL': 'CT', 'DEN': 'MT', 'UTA': 'MT', 'PHX': 'MT',
    'LAL': 'PT', 'LAC': 'PT', 'GSW': 'PT', 'SAC': 'PT', 'POR': 'PT'
}
TZ_ORDER = {'ET': 0, 'CT': 1, 'MT': 2, 'PT': 3}


def load_db_url():
    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 ingest_travel_metrics(season=2024):
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

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

    game_key = f'{season}_NBA_TRAVEL'
    game_date_base = datetime(season, 10, 1, tzinfo=timezone.utc)

    sql_team = '''
        INSERT INTO "TeamGameMetric"
        (league, season, "gameKey", "gameDate", team, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", team, "statKey")
        DO UPDATE SET value = EXCLUDED.value
    '''

    print(f'Fetching NBA games for {season}...')

    # Get all games - using correct column names
    cur.execute('''
        SELECT "homeTeam", "awayTeam", "gameDate", "homeScore", "awayScore", "spreadHome"
        FROM "SportsGame"
        WHERE league = 'nba' AND season = %s AND status = 'Final'
        ORDER BY "gameDate"
    ''', (season,))
    
    games = cur.fetchall()
    print(f'  Games: {len(games)}')

    if len(games) == 0:
        print('  No games found')
        conn.close()
        return {'success': True, 'stats_added': 0}

    # Build team schedules
    team_games = defaultdict(list)
    for home, away, gdate, hscore, ascore, spread_home in games:
        margin = (hscore or 0) - (ascore or 0)
        home_covered = None
        if spread_home is not None and hscore is not None and ascore is not None:
            home_covered = margin > -spread_home
        
        team_games[home].append({
            'date': gdate, 'is_home': True, 'opponent': away, 
            'covered': home_covered
        })
        team_games[away].append({
            'date': gdate, 'is_home': False, 'opponent': home,
            'covered': not home_covered if home_covered is not None else None
        })

    # Analyze each team
    for team, games_list in team_games.items():
        games_sorted = sorted(games_list, key=lambda x: x['date'])
        
        b2b_games = b2b_covers = 0
        b2b_road_games = b2b_road_covers = 0
        three_in_four = three_in_four_covers = 0
        altitude_games = altitude_covers = 0
        tz_cross_games = tz_cross_covers = 0

        for i, game in enumerate(games_sorted):
            if i > 0:
                prev_date = games_sorted[i-1]['date']
                curr_date = game['date']
                if hasattr(prev_date, 'date'):
                    prev_date = prev_date.date()
                if hasattr(curr_date, 'date'):
                    curr_date = curr_date.date()
                
                days_between = (curr_date - prev_date).days if prev_date and curr_date else 999
                
                if days_between == 1:
                    b2b_games += 1
                    if game['covered']:
                        b2b_covers += 1
                    if not game['is_home']:
                        b2b_road_games += 1
                        if game['covered']:
                            b2b_road_covers += 1

            if i >= 2:
                dates = [g['date'] for g in games_sorted[max(0, i-2):i+1]]
                dates = [d.date() if hasattr(d, 'date') else d for d in dates]
                if len(dates) == 3 and all(dates):
                    span = (dates[-1] - dates[0]).days
                    if span <= 3:
                        three_in_four += 1
                        if game['covered']:
                            three_in_four_covers += 1

            if not game['is_home'] and game['opponent'] in HIGH_ALTITUDE:
                altitude_games += 1
                if game['covered']:
                    altitude_covers += 1

            if i > 0:
                prev_loc = games_sorted[i-1]['opponent'] if not games_sorted[i-1]['is_home'] else team
                curr_loc = game['opponent'] if not game['is_home'] else team
                
                prev_tz = TIMEZONES.get(prev_loc, 'ET')
                curr_tz = TIMEZONES.get(curr_loc, 'ET')
                tz_diff = abs(TZ_ORDER.get(prev_tz, 0) - TZ_ORDER.get(curr_tz, 0))
                
                if tz_diff >= 2:
                    tz_cross_games += 1
                    if game['covered']:
                        tz_cross_covers += 1

        metrics = {}
        if b2b_games >= 3:
            metrics['nba_b2b_cover_rate'] = b2b_covers / b2b_games
            metrics['nba_b2b_games'] = b2b_games
        if b2b_road_games >= 2:
            metrics['nba_b2b_road_cover_rate'] = b2b_road_covers / b2b_road_games
        if three_in_four >= 2:
            metrics['nba_3in4_cover_rate'] = three_in_four_covers / three_in_four
            metrics['nba_3in4_games'] = three_in_four
        if altitude_games >= 2:
            metrics['nba_altitude_cover_rate'] = altitude_covers / altitude_games
        if tz_cross_games >= 2:
            metrics['nba_tz_cross_cover_rate'] = tz_cross_covers / tz_cross_games

        for stat_key, val in metrics.items():
            cur.execute(sql_team, ('nba', season, game_key, game_date_base, team, stat_key, float(val)))
            stats_added += 1

    conn.commit()
    cur.close()
    conn.close()

    print(f'✅ NBA travel metrics ingested: {stats_added}')
    return {'success': True, 'stats_added': stats_added}


if __name__ == '__main__':
    season = int(sys.argv[1]) if len(sys.argv) > 1 else 2024
    ingest_travel_metrics(season)