#!/usr/bin/env python3
"""
Soccer Team Stats via ESPN API (EPL, La Liga, Serie A, Bundesliga, Ligue 1)
"""
import requests
import psycopg2
from datetime import datetime, timezone
import sys

LEAGUES = {
    'eng.1': 'epl',      # Premier League
    'esp.1': 'laliga',   # La Liga
    'ita.1': 'seriea',   # Serie A
    'ger.1': 'bundesliga', # Bundesliga
    'fra.1': 'ligue1'    # Ligue 1
}


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 get_espn_stats(league_code, team_id):
    url = f'https://site.api.espn.com/apis/site/v2/sports/soccer/{league_code}/teams/{team_id}/statistics'
    try:
        resp = requests.get(url, timeout=10)
        if resp.ok:
            return resp.json()
    except:
        pass
    return None


def parse_stats(data):
    if not data or 'results' not in data:
        return {}
    stats = {}
    try:
        categories = data['results']['stats'].get('categories', [])
        for cat in categories:
            for stat in cat.get('stats', []):
                stats[stat.get('name', '')] = stat.get('value')
    except:
        pass
    return stats


def ingest_league(league_code, league_name, season, cur, sql):
    stats_added = 0
    game_key = f'{season}_SOCCER_{league_name.upper()}'
    game_date = datetime(season, 8, 1, tzinfo=timezone.utc)
    
    try:
        teams_resp = requests.get(f'https://site.api.espn.com/apis/site/v2/sports/soccer/{league_code}/teams', timeout=10)
        if not teams_resp.ok:
            print(f'  Failed to get {league_name} teams')
            return 0
        
        teams = teams_resp.json()['sports'][0]['leagues'][0]['teams']
        
        for team_data in teams:
            team = team_data['team']
            abbr = team.get('abbreviation', team['displayName'][:3].upper())
            team_id = team['id']
            
            data = get_espn_stats(league_code, team_id)
            stats = parse_stats(data)
            if not stats:
                continue
            
            metrics = {
                f'{league_name}_goals_pg': stats.get('goalsScoredPerMatch'),
                f'{league_name}_conceded_pg': stats.get('goalsConcededPerMatch'),
                f'{league_name}_shots_pg': stats.get('shotsPerMatch'),
                f'{league_name}_possession': stats.get('possessionPct'),
                f'{league_name}_pass_accuracy': stats.get('passCompletionPct'),
                f'{league_name}_fouls_pg': stats.get('foulsPerMatch'),
                f'{league_name}_yellows_pg': stats.get('yellowCardsPerMatch'),
                f'{league_name}_corners_pg': stats.get('cornersPerMatch'),
            }
            
            for stat_key, val in metrics.items():
                if val is not None:
                    cur.execute(sql, (league_name, season, game_key, game_date, abbr, stat_key, float(val)))
                    stats_added += 1
    except Exception as e:
        print(f'  Error processing {league_name}: {e}')
    
    return stats_added


def ingest_soccer_teams(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()
    total_stats = 0

    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'Fetching soccer team stats for {season}...')
    
    for league_code, league_name in LEAGUES.items():
        stats = ingest_league(league_code, league_name, season, cur, sql)
        print(f'  {league_name}: {stats} stats')
        total_stats += stats

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ Soccer team stats ingested: {total_stats}')
    return {'success': True, 'stats_added': total_stats}


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