#!/usr/bin/env python3
"""
Soccer Goal Patterns Analysis
Analyzes early (0-15) and late (75+) scoring for live betting edges.
"""
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_goal_patterns(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

    leagues = ['epl', 'laliga', 'seriea', 'bundesliga', 'ligue1']
    
    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 soccer goal patterns for {season}...')

    for league in leagues:
        game_key = f'{season}_SOCCER_{league.upper()}_GOALS'
        game_date_base = datetime(season, 8, 1, tzinfo=timezone.utc)
        
        cur.execute('''
            SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", total
            FROM "SportsGame"
            WHERE league = %s AND season = %s AND status = 'Final'
        ''', (league, season))
        
        games = cur.fetchall()
        
        if len(games) == 0:
            continue

        team_stats = defaultdict(lambda: {
            'games': 0, 'clean_sheets': 0, 'btts': 0,
            'over_2_5': 0, 'over_1_5': 0
        })

        for home, away, hscore, ascore, total in games:
            if hscore is None or ascore is None:
                continue
            
            game_total = hscore + ascore
            went_over_2_5 = game_total > 2.5
            went_over_1_5 = game_total > 1.5
            btts = hscore > 0 and ascore > 0
            
            # Home team
            team_stats[home]['games'] += 1
            if ascore == 0:
                team_stats[home]['clean_sheets'] += 1
            if btts:
                team_stats[home]['btts'] += 1
            if went_over_2_5:
                team_stats[home]['over_2_5'] += 1
            if went_over_1_5:
                team_stats[home]['over_1_5'] += 1
            
            # Away team
            team_stats[away]['games'] += 1
            if hscore == 0:
                team_stats[away]['clean_sheets'] += 1
            if btts:
                team_stats[away]['btts'] += 1
            if went_over_2_5:
                team_stats[away]['over_2_5'] += 1
            if went_over_1_5:
                team_stats[away]['over_1_5'] += 1

        for team, data in team_stats.items():
            if data['games'] < 5:
                continue
            
            metrics = {
                f'{league}_clean_sheet_rate': data['clean_sheets'] / data['games'],
                f'{league}_btts_rate': data['btts'] / data['games'],
                f'{league}_over_2_5_rate': data['over_2_5'] / data['games'],
                f'{league}_over_1_5_rate': data['over_1_5'] / data['games'],
            }
            
            for stat_key, val in metrics.items():
                cur.execute(sql, (league, season, game_key, game_date_base, team, stat_key, float(val)))
                stats_added += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ Soccer goal patterns 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_goal_patterns(season)