#!/usr/bin/env python3
"""
NHL Score Effects Analysis
Analyzes trailing/leading shot volume and over/under rates.
"""
import psycopg2
from datetime import datetime, timezone
from collections import defaultdict
import sys


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_score_effects(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_SCORE_EFFECTS'
    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 score effects for {season}...')

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

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

    team_stats = defaultdict(lambda: {
        'high_scoring': 0, 'high_scoring_over': 0,
        'low_scoring': 0, 'low_scoring_over': 0,
        'blowout_wins': 0, 'close_games': 0, 'close_overs': 0
    })

    for home, away, hscore, ascore, total in games:
        if hscore is None or ascore is None:
            continue
        
        game_total = hscore + ascore
        went_over = game_total > total if total else None
        margin = abs(hscore - ascore)
        
        for team, scored, allowed in [(home, hscore, ascore), (away, ascore, hscore)]:
            if game_total >= 6:  # High scoring game
                team_stats[team]['high_scoring'] += 1
                if went_over:
                    team_stats[team]['high_scoring_over'] += 1
            else:
                team_stats[team]['low_scoring'] += 1
                if went_over:
                    team_stats[team]['low_scoring_over'] += 1
            
            if margin <= 1:
                team_stats[team]['close_games'] += 1
                if went_over:
                    team_stats[team]['close_overs'] += 1
            elif scored > allowed and margin >= 3:
                team_stats[team]['blowout_wins'] += 1

    for team, stats in team_stats.items():
        metrics = {}
        
        if stats['high_scoring'] >= 10:
            metrics['nhl_high_scoring_over_rate'] = stats['high_scoring_over'] / stats['high_scoring']
        if stats['low_scoring'] >= 10:
            metrics['nhl_low_scoring_over_rate'] = stats['low_scoring_over'] / stats['low_scoring']
        if stats['close_games'] >= 10:
            metrics['nhl_close_game_over_rate'] = stats['close_overs'] / stats['close_games']
        if stats['blowout_wins'] >= 3:
            metrics['nhl_blowout_win_count'] = stats['blowout_wins']

        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 score effects 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_score_effects(season)