#!/usr/bin/env python3
"""
NHL Extra Advanced Stats - MoneyPuck + NHL API
Fetches Individual xG, PDO, PP%/PK%, Goalie stats
"""
import requests
import psycopg2
from datetime import datetime, timezone

MONEYPUCK_BASE = 'https://moneypuck.com/moneypuck/playerData/seasonSummary'
NHL_API_BASE = 'https://api.nhle.com/stats/rest/en'

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 fetch_moneypuck_skaters(season='2024'):
    """Fetch skater stats from MoneyPuck"""
    url = f'{MONEYPUCK_BASE}/{season}/regular/skaters.csv'
    
    print(f'Fetching MoneyPuck skater stats for {season}...')
    try:
        resp = requests.get(url, timeout=60)
        if resp.status_code != 200:
            print(f'  Status: {resp.status_code}')
            return []
        
        lines = resp.text.strip().split('\n')
        if len(lines) < 2:
            return []
        
        headers = [h.strip().lower() for h in lines[0].split(',')]
        data = []
        for line in lines[1:]:
            values = line.split(',')
            if len(values) == len(headers):
                row = dict(zip(headers, values))
                # Only include 'all' situation or entries without situation
                situation = row.get('situation', 'all')
                if situation == 'all' or situation == '':
                    data.append(row)
        
        print(f'  Found {len(data)} skaters (all situations)')
        return data
    except Exception as e:
        print(f'Error: {e}')
        return []

def fetch_moneypuck_goalies(season='2024'):
    """Fetch goalie stats from MoneyPuck - only 'all' situation"""
    url = f'{MONEYPUCK_BASE}/{season}/regular/goalies.csv'
    
    print(f'Fetching MoneyPuck goalie stats for {season}...')
    try:
        resp = requests.get(url, timeout=60)
        if resp.status_code != 200:
            print(f'  Status: {resp.status_code}')
            return []
        
        lines = resp.text.strip().split('\n')
        if len(lines) < 2:
            return []
        
        headers = [h.strip().lower() for h in lines[0].split(',')]
        data = []
        for line in lines[1:]:
            values = line.split(',')
            if len(values) == len(headers):
                row = dict(zip(headers, values))
                # Only include 'all' situation for overall stats
                situation = row.get('situation', '')
                if situation == 'all':
                    data.append(row)
        
        print(f'  Found {len(data)} goalies (all situations)')
        return data
    except Exception as e:
        print(f'Error: {e}')
        return []

def safe_float(val):
    try:
        if val is None or val == '' or val == 'NA':
            return None
        return float(val)
    except:
        return None

def ingest_nhl_extra(season='2024'):
    """Main ingest function"""
    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
    
    season_year = int(season)
    game_key = f'{season}_NHL_SEASON'
    game_date = datetime(season_year, 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
    '''
    
    sql_player = '''
        INSERT INTO "PlayerGameMetric"
        (league, season, "gameKey", "gameDate", "playerExternalId", "playerName", position, team, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", "playerExternalId", "statKey")
        DO UPDATE SET value = EXCLUDED.value, position = EXCLUDED.position
    '''
    
    # 1. MoneyPuck Skater Stats
    skaters = fetch_moneypuck_skaters(season)
    skater_count = 0
    
    for row in skaters:
        player_id = row.get('playerid', row.get('name', ''))
        player_name = row.get('name', '')
        team = row.get('team', '')
        position = row.get('position', '')
        
        if not player_name:
            continue
        
        metrics = [
            ('nhl_ixg', row.get('i_xgoals')),
            ('nhl_ixg_per60', row.get('i_xgoals_per60')),
            ('nhl_goals', row.get('i_goals')),
            ('nhl_goals_per60', row.get('i_goals_per60')),
            ('nhl_assists', row.get('i_assists')),
            ('nhl_points', row.get('i_points')),
            ('nhl_shots', row.get('i_shots')),
            ('nhl_shots_per60', row.get('i_shots_per60')),
            ('nhl_shooting_pct', row.get('i_sh_percentage')),
            ('nhl_on_ice_xgf', row.get('onicegoalsfor_xgoals')),
            ('nhl_on_ice_xga', row.get('onicegoalsagainst_xgoals')),
            ('nhl_on_ice_gf', row.get('onicegoalsfor')),
            ('nhl_on_ice_ga', row.get('onicegoalsagainst')),
            ('nhl_cf', row.get('icorsif')),
            ('nhl_ca', row.get('icorsia')),
            ('nhl_cf_pct', row.get('icorsif_pct')),
            ('nhl_ff', row.get('ifenwickf')),
            ('nhl_ff_pct', row.get('ifenwickf_pct')),
            ('nhl_toi', row.get('icetime')),
            ('nhl_games', row.get('games_played')),
            ('nhl_penalties', row.get('i_penalties')),
            ('nhl_penalties_drawn', row.get('i_penaltiesdrawn')),
        ]
        
        for stat_key, val in metrics:
            fval = safe_float(val)
            if fval is not None:
                try:
                    cur.execute(sql_player, (
                        'nhl', season_year, game_key, game_date,
                        str(player_id), player_name, position, team,
                        stat_key, fval
                    ))
                    stats_added += 1
                    skater_count += 1
                except:
                    conn.rollback()
    
    conn.commit()
    print(f'  Skater stats added: {skater_count}')
    
    # 2. MoneyPuck Goalie Stats - using 'all' situation only
    goalies = fetch_moneypuck_goalies(season)
    goalie_count = 0
    
    for row in goalies:
        player_id = row.get('playerid', row.get('name', ''))
        player_name = row.get('name', '')
        team = row.get('team', '')
        position = 'G'
        
        if not player_name:
            continue
        
        # Get raw values for calculations
        games = safe_float(row.get('games_played')) or 0
        icetime = safe_float(row.get('icetime')) or 0
        goals_against = safe_float(row.get('goals')) or 0
        xgoals = safe_float(row.get('xgoals')) or 0
        shots_on_goal = safe_float(row.get('ongoal')) or 0
        
        # Low/Medium/High danger breakdown
        ld_shots = safe_float(row.get('lowdangershots')) or 0
        md_shots = safe_float(row.get('mediumdangershots')) or 0
        hd_shots = safe_float(row.get('highdangershots')) or 0
        ld_goals = safe_float(row.get('lowdangergoals')) or 0
        md_goals = safe_float(row.get('mediumdangergoals')) or 0
        hd_goals = safe_float(row.get('highdangergoals')) or 0
        
        # Calculate derived stats
        saves = shots_on_goal - goals_against
        save_pct = (saves / shots_on_goal * 100) if shots_on_goal > 0 else None
        gaa = (goals_against / (icetime / 3600)) if icetime > 0 else None  # icetime is in seconds
        gsax = xgoals - goals_against  # Goals Saved Above Expected (positive is good)
        
        # High danger save %
        hd_sv_pct = ((hd_shots - hd_goals) / hd_shots * 100) if hd_shots > 0 else None
        md_sv_pct = ((md_shots - md_goals) / md_shots * 100) if md_shots > 0 else None
        ld_sv_pct = ((ld_shots - ld_goals) / ld_shots * 100) if ld_shots > 0 else None
        
        metrics = [
            ('nhl_g_sv_pct', save_pct),
            ('nhl_g_gaa', gaa),
            ('nhl_g_gsax', gsax),
            ('nhl_g_xgoals_against', xgoals),
            ('nhl_g_goals_against', goals_against),
            ('nhl_g_shots_against', shots_on_goal),
            ('nhl_g_saves', saves),
            ('nhl_g_high_danger_sv_pct', hd_sv_pct),
            ('nhl_g_medium_danger_sv_pct', md_sv_pct),
            ('nhl_g_low_danger_sv_pct', ld_sv_pct),
            ('nhl_g_hd_shots', hd_shots),
            ('nhl_g_hd_goals', hd_goals),
            ('nhl_g_games', games),
            ('nhl_g_toi', icetime),
        ]
        
        for stat_key, val in metrics:
            fval = safe_float(val) if not isinstance(val, float) else val
            if fval is not None:
                try:
                    cur.execute(sql_player, (
                        'nhl', season_year, game_key, game_date,
                        str(player_id), player_name, position, team,
                        stat_key, fval
                    ))
                    stats_added += 1
                    goalie_count += 1
                except:
                    conn.rollback()
    
    conn.commit()
    print(f'  Goalie stats added: {goalie_count}')
    
    # 3. Team-level stats
    team_stats = {}
    for row in skaters:
        team = row.get('team', '')
        if not team:
            continue
        
        if team not in team_stats:
            team_stats[team] = {'gf': 0, 'ga': 0, 'xgf': 0, 'xga': 0}
        
        gf = safe_float(row.get('onicegoalsfor')) or 0
        ga = safe_float(row.get('onicegoalsagainst')) or 0
        xgf = safe_float(row.get('onicegoalsfor_xgoals')) or 0
        xga = safe_float(row.get('onicegoalsagainst_xgoals')) or 0
        
        team_stats[team]['gf'] = max(team_stats[team]['gf'], gf)
        team_stats[team]['ga'] = max(team_stats[team]['ga'], ga)
        team_stats[team]['xgf'] = max(team_stats[team]['xgf'], xgf)
        team_stats[team]['xga'] = max(team_stats[team]['xga'], xga)
    
    team_count = 0
    for team, stats in team_stats.items():
        gf = stats['gf']
        ga = stats['ga']
        xgf = stats['xgf']
        xga = stats['xga']
        
        metrics = [
            ('nhl_team_gf', gf),
            ('nhl_team_ga', ga),
            ('nhl_team_xgf', xgf),
            ('nhl_team_xga', xga),
            ('nhl_team_xg_diff', xgf - xga),
            ('nhl_team_goal_diff', gf - ga),
        ]
        
        if xgf > 0:
            metrics.append(('nhl_team_goals_vs_xg', (gf - xgf) / xgf * 100))
        
        for stat_key, val in metrics:
            if val is not None:
                try:
                    cur.execute(sql_team, ('nhl', season_year, game_key, game_date, team, stat_key, float(val)))
                    stats_added += 1
                    team_count += 1
                except:
                    conn.rollback()
    
    conn.commit()
    print(f'  Team stats added: {team_count}')
    
    cur.close()
    conn.close()
    
    print(f'')
    print(f'✅ NHL Extra Stats ingested: {stats_added} total metrics')
    return {'success': True, 'stats_added': stats_added}

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