#!/usr/bin/env python3
"""
NHL Travel & Rest Analysis
Tracks travel fatigue, timezone effects, and home stand performance.
"""
import psycopg2
from datetime import datetime, timezone
from collections import defaultdict
import sys


TIMEZONES = {
    'ANA': 'PT', 'ARI': 'MT', 'BOS': 'ET', 'BUF': 'ET', 'CGY': 'MT',
    'CAR': 'ET', 'CHI': 'CT', 'COL': 'MT', 'CBJ': 'ET', 'DAL': 'CT',
    'DET': 'ET', 'EDM': 'MT', 'FLA': 'ET', 'LA': 'PT', 'LAK': 'PT',
    'MIN': 'CT', 'MTL': 'ET', 'NSH': 'CT', 'NJ': 'ET', 'NJD': 'ET',
    'NYI': 'ET', 'NYR': 'ET', 'OTT': 'ET', 'PHI': 'ET', 'PIT': 'ET',
    'SJ': 'PT', 'SJS': 'PT', 'SEA': 'PT', 'STL': 'CT', 'TB': 'ET', 'TBL': 'ET',
    'TOR': 'ET', 'VAN': 'PT', 'VGK': 'PT', 'WSH': 'ET', 'WPG': 'CT',
    'UTA': 'MT'
}
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(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}_NHL_TRAVEL'
    game_date_base = datetime(season, 10, 1, tzinfo=timezone.utc)

    sql = '''
        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'Analyzing NHL travel for {season}...')

    cur.execute('''
        SELECT "homeTeam", "awayTeam", "gameDate", "homeScore", "awayScore"
        FROM "SportsGame"
        WHERE league = 'nhl' AND season = %s AND status = 'Final'
        ORDER BY "gameDate"
    ''', (season,))
    
    games = cur.fetchall()
    print(f'  Games: {len(games)}')

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

    team_games = defaultdict(list)
    for home, away, gdate, hscore, ascore in games:
        for team, score, opp_score, is_home, opp in [(home, hscore, ascore, True, away), (away, ascore, hscore, False, home)]:
            team_games[team].append({
                'date': gdate, 'is_home': is_home, 'won': (score or 0) > (opp_score or 0),
                'opponent': opp
            })

    for team, games_list in team_games.items():
        games_sorted = sorted(games_list, key=lambda x: x['date'])
        
        road_trip_games = road_trip_wins = 0
        tz_cross_games = tz_cross_wins = 0
        
        for i, game in enumerate(games_sorted):
            if not game['is_home']:
                # Count consecutive road games
                j = i
                while j >= 0 and not games_sorted[j]['is_home']:
                    j -= 1
                road_streak = i - j
                
                if road_streak >= 3:
                    road_trip_games += 1
                    if game['won']:
                        road_trip_wins += 1

            # Timezone crossing
            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['won']:
                        tz_cross_wins += 1

        metrics = {}
        if road_trip_games >= 5:
            metrics['nhl_road_trip_win_rate'] = road_trip_wins / road_trip_games
        if tz_cross_games >= 5:
            metrics['nhl_tz_cross_win_rate'] = tz_cross_wins / tz_cross_games

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

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ NHL travel 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(season)