#!/usr/bin/env python3
"""
Populate GameScheduleMeta table with derived schedule fields:
- Rest days between games
- Back-to-back / 3-in-4 / 4-in-5 flags
- Games in last 7/14 days
- Travel distance estimates
- Timezone changes

Run: Daily after game schedules are updated
"""
import psycopg2
import os
from datetime import datetime, timezone, timedelta
from collections import defaultdict

# Team timezone mapping (approximate)
TEAM_TIMEZONES = {
    # NBA Eastern
    'ATL': -5, 'BOS': -5, 'BKN': -5, 'CHA': -5, 'CHI': -6, 'CLE': -5,
    'DET': -5, 'IND': -5, 'MIA': -5, 'MIL': -6, 'NYK': -5, 'ORL': -5,
    'PHI': -5, 'TOR': -5, 'WAS': -5,
    # NBA Western
    'DAL': -6, 'DEN': -7, 'GSW': -8, 'HOU': -6, 'LAC': -8, 'LAL': -8,
    'MEM': -6, 'MIN': -6, 'NOP': -6, 'OKC': -6, 'PHX': -7, 'POR': -8,
    'SAC': -8, 'SAS': -6, 'UTA': -7,
    # NHL teams follow similar patterns
    # NFL teams follow similar patterns
}

# Stadium altitudes (feet)
ALTITUDES = {
    'DEN': 5280,  # Denver (NBA Nuggets, NFL Broncos, NHL Avalanche)
    'UTA': 4226,  # Salt Lake City (Jazz)
    'PHX': 1086,  # Phoenix
    'MEX': 7382,  # Mexico City (if games played there)
}


def load_db_url():
    env_path = '/var/www/html/eventheodds/.env'
    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:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def get_team_games(cur, league, season):
    """Get all games for a league/season organized by team"""
    cur.execute('''
        SELECT id, "gameDate", "homeTeam", "awayTeam"
        FROM "SportsGame"
        WHERE league = %s AND season = %s AND "gameDate" IS NOT NULL
        ORDER BY "gameDate"
    ''', (league, season))

    team_games = defaultdict(list)
    for row in cur.fetchall():
        game_id, game_date, home, away = row
        team_games[home].append({'id': game_id, 'date': game_date, 'is_home': True, 'opponent': away})
        team_games[away].append({'id': game_id, 'date': game_date, 'is_home': False, 'opponent': home})

    # Sort each team's games by date
    for team in team_games:
        team_games[team].sort(key=lambda x: x['date'])

    return team_games


def calculate_rest_days(games, idx):
    """Calculate days of rest before this game"""
    if idx == 0:
        return None  # First game of season

    prev_date = games[idx - 1]['date'].date()
    curr_date = games[idx]['date'].date()
    return (curr_date - prev_date).days - 1  # -1 because same day = 0 rest


def calculate_games_in_window(games, idx, days):
    """Count games in last N days (excluding current game)"""
    curr_date = games[idx]['date'].date()
    start_date = curr_date - timedelta(days=days)

    count = 0
    for i in range(idx):
        if games[i]['date'].date() >= start_date:
            count += 1
    return count


def calculate_travel_metrics(games, idx, team):
    """Estimate travel distance and timezone changes"""
    if idx == 0:
        return {'distance': None, 'tz_change': None, 'coast_to_coast': False}

    prev_game = games[idx - 1]
    curr_game = games[idx]

    # Get locations (home team location for home games, opponent for away)
    prev_loc = team if prev_game['is_home'] else prev_game['opponent']
    curr_loc = team if curr_game['is_home'] else curr_game['opponent']

    if prev_loc == curr_loc:
        return {'distance': 0, 'tz_change': 0, 'coast_to_coast': False}

    # Timezone change
    prev_tz = TEAM_TIMEZONES.get(prev_loc, -6)
    curr_tz = TEAM_TIMEZONES.get(curr_loc, -6)
    tz_change = abs(curr_tz - prev_tz)

    # Coast to coast if 3+ hour difference
    coast_to_coast = tz_change >= 3

    # Rough distance estimate based on timezone diff (very approximate)
    # ~800 miles per timezone on average
    distance = tz_change * 800 if tz_change > 0 else 200  # Minimum 200 for any travel

    return {'distance': distance, 'tz_change': tz_change, 'coast_to_coast': coast_to_coast}


def calculate_streak_info(games, idx):
    """Calculate home stand or road trip game number"""
    is_home = games[idx]['is_home']

    streak = 1
    for i in range(idx - 1, -1, -1):
        if games[i]['is_home'] == is_home:
            streak += 1
        else:
            break

    if is_home:
        return {'home_stand': streak, 'road_trip': None}
    else:
        return {'home_stand': None, 'road_trip': streak}


def populate_schedule_meta(league, season):
    """Main function to populate schedule metadata"""
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

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

    print(f"\n{'='*60}")
    print(f"Populating GameScheduleMeta for {league} {season}")
    print(f"{'='*60}")

    team_games = get_team_games(cur, league, season)
    print(f"Found {len(team_games)} teams with games")

    total_inserted = 0
    total_updated = 0

    for team, games in team_games.items():
        for idx, game in enumerate(games):
            rest_days = calculate_rest_days(games, idx)
            is_b2b = rest_days == 0 if rest_days is not None else False

            # Check 3-in-4 and 4-in-5
            games_in_4 = calculate_games_in_window(games, idx, 4) + 1  # +1 for current
            games_in_5 = calculate_games_in_window(games, idx, 5) + 1
            is_3in4 = games_in_4 >= 3
            is_4in5 = games_in_5 >= 4

            games_in_7 = calculate_games_in_window(games, idx, 7)
            games_in_14 = calculate_games_in_window(games, idx, 14)

            travel = calculate_travel_metrics(games, idx, team)
            streak = calculate_streak_info(games, idx)

            # Get altitude
            game_loc = team if game['is_home'] else game['opponent']
            altitude = ALTITUDES.get(game_loc)

            # Calculate altitude change
            if idx > 0:
                prev_loc = team if games[idx-1]['is_home'] else games[idx-1]['opponent']
                prev_alt = ALTITUDES.get(prev_loc, 0)
                curr_alt = altitude or 0
                alt_change = curr_alt - prev_alt if prev_alt or curr_alt else None
            else:
                alt_change = None

            try:
                cur.execute('''
                    INSERT INTO "GameScheduleMeta" (
                        league, "gameId", "gameDate", team,
                        "restDays", "isBackToBack", "isThreeInFour", "isFourInFive",
                        "gamesInLast7", "gamesInLast14",
                        "travelDistance", "timezoneChange", "isCoastToCoast",
                        "isHome", "homeStandGame", "roadTripGame",
                        altitude, "altitudeChange",
                        "createdAt", "updatedAt"
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
                    ON CONFLICT (league, "gameId", team) DO UPDATE SET
                        "restDays" = EXCLUDED."restDays",
                        "isBackToBack" = EXCLUDED."isBackToBack",
                        "isThreeInFour" = EXCLUDED."isThreeInFour",
                        "isFourInFive" = EXCLUDED."isFourInFive",
                        "gamesInLast7" = EXCLUDED."gamesInLast7",
                        "gamesInLast14" = EXCLUDED."gamesInLast14",
                        "travelDistance" = EXCLUDED."travelDistance",
                        "timezoneChange" = EXCLUDED."timezoneChange",
                        "isCoastToCoast" = EXCLUDED."isCoastToCoast",
                        "homeStandGame" = EXCLUDED."homeStandGame",
                        "roadTripGame" = EXCLUDED."roadTripGame",
                        "altitudeChange" = EXCLUDED."altitudeChange",
                        "updatedAt" = NOW()
                    RETURNING (xmax = 0) AS inserted
                ''', (
                    league, game['id'], game['date'], team,
                    rest_days, is_b2b, is_3in4, is_4in5,
                    games_in_7, games_in_14,
                    travel['distance'], travel['tz_change'], travel['coast_to_coast'],
                    game['is_home'], streak['home_stand'], streak['road_trip'],
                    altitude, alt_change
                ))

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

            except Exception as e:
                print(f"  Error for {team} game {game['id']}: {e}")
                conn.rollback()

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

    print(f"\nResults:")
    print(f"  Inserted: {total_inserted}")
    print(f"  Updated:  {total_updated}")

    return {'inserted': total_inserted, 'updated': total_updated}


def main():
    print("=" * 60)
    print("POPULATE GAME SCHEDULE METADATA")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Get all league/season combinations with games
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    cur.execute('''
        SELECT DISTINCT league, season
        FROM "SportsGame"
        WHERE league IN ('nba', 'nfl', 'nhl')
          AND "homeScore" IS NOT NULL
        ORDER BY league, season DESC
    ''')
    leagues = cur.fetchall()
    cur.close()
    conn.close()
    print(f"Found {len(leagues)} league/season combinations to process")

    total_inserted = 0
    total_updated = 0

    for league, season in leagues:
        try:
            result = populate_schedule_meta(league, season)
            total_inserted += result['inserted']
            total_updated += result['updated']
        except Exception as e:
            print(f"Error processing {league} {season}: {e}")

    print(f"\n{'='*60}")
    print(f"TOTAL: {total_inserted} inserted, {total_updated} updated")
    print("=" * 60)


if __name__ == '__main__':
    main()