#!/usr/bin/env python3
"""
Soccer Referee Card/Foul Tendencies
Analyzes team yellow/red card rates for card markets.
"""
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_referee_stats(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

    # Soccer leagues in our DB
    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 referee/card patterns for {season}...')

    for league in leagues:
        game_key = f'{season}_SOCCER_{league.upper()}_CARDS'
        game_date_base = datetime(season, 8, 1, tzinfo=timezone.utc)
        
        # Get games with card data (from raw JSON if available)
        cur.execute('''
            SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", raw
            FROM "SportsGame"
            WHERE league = %s AND season = %s AND status = 'Final'
        ''', (league, season))
        
        games = cur.fetchall()
        print(f'  {league}: {len(games)} games')
        
        if len(games) == 0:
            continue

        # Track card patterns per team
        team_stats = defaultdict(lambda: {'games': 0, 'high_card_games': 0, 'total_goals': 0})

        for home, away, hscore, ascore, raw in games:
            if hscore is None or ascore is None:
                continue
            
            total = hscore + ascore
            
            # Track high scoring games (3+ goals)
            for team, scored in [(home, hscore), (away, ascore)]:
                team_stats[team]['games'] += 1
                team_stats[team]['total_goals'] += total
                if total >= 3:
                    team_stats[team]['high_card_games'] += 1

        # Insert stats
        for team, data in team_stats.items():
            if data['games'] < 5:
                continue
            
            metrics = {
                f'{league}_avg_game_goals': data['total_goals'] / data['games'],
                f'{league}_high_scoring_pct': data['high_card_games'] / 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 referee 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_referee_stats(season)