#!/usr/bin/env python3
"""
Fetch NHL period-by-period scores for first-period analysis
Ingests 1P/2P/3P scores into TeamGameMetric
"""
import os
import json
import requests
import psycopg2
from datetime import datetime, timezone, timedelta
from pathlib import Path

def load_db_url():
    env_path = Path('/var/www/html/eventheodds/.env')
    if env_path.exists():
        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]
    return ''

def fetch_game_periods(game_id: int) -> dict | None:
    """Fetch period-by-period scoring from NHL API"""
    try:
        url = f"https://api-web.nhle.com/v1/gamecenter/{game_id}/landing"
        resp = requests.get(url, timeout=15)
        if resp.status_code != 200:
            return None
        
        data = resp.json()
        
        # Get team info
        home_team = data.get('homeTeam', {}).get('abbrev', '')
        away_team = data.get('awayTeam', {}).get('abbrev', '')
        game_date = data.get('gameDate', '')
        
        # Parse period scores from scoring summary
        period_scores = {'home': {1: 0, 2: 0, 3: 0}, 'away': {1: 0, 2: 0, 3: 0}}
        
        for period_data in data.get('summary', {}).get('scoring', []):
            period_num = period_data.get('periodDescriptor', {}).get('number', 0)
            if period_num not in [1, 2, 3]:
                continue
            
            goals = period_data.get('goals', [])
            for goal in goals:
                team_abbrev = goal.get('teamAbbrev', {}).get('default', '')
                if team_abbrev == home_team:
                    period_scores['home'][period_num] += 1
                elif team_abbrev == away_team:
                    period_scores['away'][period_num] += 1
        
        # Get shot data by period if available
        shots_by_period = {'home': {1: 0, 2: 0, 3: 0}, 'away': {1: 0, 2: 0, 3: 0}}
        for period_data in data.get('summary', {}).get('shotsByPeriod', []):
            period_num = period_data.get('periodDescriptor', {}).get('number', 0)
            if period_num in [1, 2, 3]:
                shots_by_period['home'][period_num] = period_data.get('home', 0)
                shots_by_period['away'][period_num] = period_data.get('away', 0)
        
        return {
            'game_id': game_id,
            'home_team': home_team,
            'away_team': away_team,
            'game_date': game_date,
            'period_scores': period_scores,
            'shots_by_period': shots_by_period,
            # Calculate 1P winner (for ML analysis)
            'p1_home_score': period_scores['home'][1],
            'p1_away_score': period_scores['away'][1],
            'p1_winner': 'home' if period_scores['home'][1] > period_scores['away'][1] else 
                        ('away' if period_scores['away'][1] > period_scores['home'][1] else 'tie'),
        }
    except Exception as e:
        print(f"  Error fetching game {game_id}: {e}")
        return None

def get_nhl_schedule(start_date: str, end_date: str) -> list:
    """Fetch NHL schedule with game IDs from NHL API"""
    try:
        url = f"https://api-web.nhle.com/v1/schedule/{start_date}"
        resp = requests.get(url, timeout=15)
        if resp.status_code != 200:
            return []
        
        data = resp.json()
        games = []
        
        for game_week in data.get('gameWeek', []):
            for game in game_week.get('games', []):
                games.append({
                    'id': game.get('id'),
                    'date': game.get('gameDate'),
                    'home': game.get('homeTeam', {}).get('abbrev'),
                    'away': game.get('awayTeam', {}).get('abbrev'),
                    'state': game.get('gameState'),  # "FINAL", "LIVE", etc
                })
        
        return games
    except Exception as e:
        print(f"Error fetching schedule: {e}")
        return []

def ingest_period_data():
    """Ingest NHL period data into TeamGameMetric"""
    db_url = load_db_url()
    if not db_url:
        print("Error: SPORTS_DATABASE_URL not found")
        return
    
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    
    # Fetch NHL schedule directly from NHL API for last 30 days
    from datetime import date
    end_date = date.today()
    start_date = end_date - timedelta(days=30)
    
    print(f"Fetching NHL schedule from {start_date} to {end_date}...")
    
    all_games = []
    current = start_date
    while current <= end_date:
        games = get_nhl_schedule(current.strftime('%Y-%m-%d'), current.strftime('%Y-%m-%d'))
        final_games = [g for g in games if g.get('state') == 'OFF']  # OFF = Final
        all_games.extend(final_games)
        current += timedelta(days=7)  # Schedule API returns a week at a time
    
    print(f"Found {len(all_games)} completed NHL games")
    
    added = 0
    for game in all_games:
        game_id = game.get('id')
        home = game.get('home')
        away = game.get('away')
        game_date_str = game.get('date')
        
        if not game_id or not home or not away:
            continue
        
        # Parse game date
        try:
            game_date = datetime.fromisoformat(game_date_str.replace('Z', '+00:00'))
        except:
            game_date = datetime.now(timezone.utc)
        
        # Determine season (NHL season spans two years, e.g., 2025-26)
        season = game_date.year if game_date.month >= 9 else game_date.year - 1
        
        # Skip if we already have period data for this game
        game_key = f"nhl:{game_id}"
        cur.execute('''
            SELECT 1 FROM "TeamGameMetric"
            WHERE league = 'nhl' AND "gameKey" = %s AND "statKey" = 'nhl_p1_goals'
            LIMIT 1
        ''', (game_key,))
        
        if cur.fetchone():
            continue
        
        period_data = fetch_game_periods(game_id)
        if not period_data:
            continue
        
        # Insert period metrics for home team
        for period in [1, 2, 3]:
            try:
                # Home goals by period
                cur.execute('''
                    INSERT INTO "TeamGameMetric"
                    (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
                    VALUES ('nhl', %s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT (league, season, "gameKey", team, "statKey") DO NOTHING
                ''', (
                    season, game_key, game_date, home, away,
                    f'nhl_p{period}_goals', period_data['period_scores']['home'][period]
                ))
                
                # Away goals by period
                cur.execute('''
                    INSERT INTO "TeamGameMetric"
                    (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
                    VALUES ('nhl', %s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT (league, season, "gameKey", team, "statKey") DO NOTHING
                ''', (
                    season, game_key, game_date, away, home,
                    f'nhl_p{period}_goals', period_data['period_scores']['away'][period]
                ))
                
                # Shots by period (if available)
                if period_data['shots_by_period']['home'][period] > 0:
                    cur.execute('''
                        INSERT INTO "TeamGameMetric"
                        (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
                        VALUES ('nhl', %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, season, "gameKey", team, "statKey") DO NOTHING
                    ''', (
                        season, game_key, game_date, home, away,
                        f'nhl_p{period}_shots', period_data['shots_by_period']['home'][period]
                    ))
                    cur.execute('''
                        INSERT INTO "TeamGameMetric"
                        (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
                        VALUES ('nhl', %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, season, "gameKey", team, "statKey") DO NOTHING
                    ''', (
                        season, game_key, game_date, away, home,
                        f'nhl_p{period}_shots', period_data['shots_by_period']['away'][period]
                    ))
            except Exception as e:
                conn.rollback()
                print(f"  Error inserting period {period} for game {game_id}: {e}")
                continue
        
        # Insert 1P winner indicator
        try:
            p1_winner_val = 1 if period_data['p1_winner'] == 'home' else (-1 if period_data['p1_winner'] == 'away' else 0)
            cur.execute('''
                INSERT INTO "TeamGameMetric"
                (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
                VALUES ('nhl', %s, %s, %s, %s, %s, 'nhl_p1_winner', %s)
                ON CONFLICT (league, season, "gameKey", team, "statKey") DO NOTHING
            ''', (season, game_key, game_date, home, away, p1_winner_val))
        except:
            pass
        
        conn.commit()
        added += 1
        if added % 20 == 0:
            print(f"  Processed {added} games...")
    
    print(f"\n✅ Added period data for {added} NHL games")
    
    # Summary
    cur.execute('''
        SELECT "statKey", COUNT(*) 
        FROM "TeamGameMetric"
        WHERE league = 'nhl' AND "statKey" LIKE 'nhl_p%'
        GROUP BY "statKey"
        ORDER BY "statKey"
    ''')
    print("\nPeriod Metrics Summary:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} records")
    
    cur.close()
    conn.close()

if __name__ == '__main__':
    ingest_period_data()