#!/usr/bin/env python3
"""
Compute Soccer Fixture Congestion Impact
Team performance based on days between matches, fixture density
Answers: "Bellingham scoring in fixture congestion?" "Team volatile after UCL?"
Supports: EPL, La Liga, Serie A, Bundesliga, Ligue 1
"""
import psycopg2
from datetime import datetime

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 compute_soccer_fixture_congestion():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create SoccerFixtureCongestion table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "SoccerFixtureCongestion" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            season VARCHAR(20),
            team VARCHAR(100) NOT NULL,
            -- Rest day distribution
            "totalGames" INTEGER DEFAULT 0,
            "gamesShortRest" INTEGER DEFAULT 0,
            "gamesNormalRest" INTEGER DEFAULT 0,
            "gamesLongRest" INTEGER DEFAULT 0,
            -- Performance by rest
            "winPctShortRest" NUMERIC,
            "winPctNormalRest" NUMERIC,
            "winPctLongRest" NUMERIC,
            "goalsForShortRest" NUMERIC,
            "goalsForNormalRest" NUMERIC,
            "goalsForLongRest" NUMERIC,
            -- Fixture density metrics
            "avgRestDays" NUMERIC,
            "gamesIn7Days" NUMERIC,
            "gamesIn14Days" NUMERIC,
            -- Dense schedule performance
            "winPctDenseSchedule" NUMERIC,
            "winPctNormalSchedule" NUMERIC,
            -- Classification
            "congestionImpact" VARCHAR(30),
            "handlesCongetion" BOOLEAN DEFAULT false,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, team, season)
        )
    ''')
    conn.commit()
    print("SoccerFixtureCongestion table created/verified")

    # Compute fixture congestion for each soccer league
    soccer_leagues = ['epl', 'laliga', 'seriea', 'bundesliga', 'ligue1']

    for league in soccer_leagues:
        print(f"\nComputing {league.upper()} fixture congestion...")
        cur.execute('''
            WITH team_games AS (
                -- Get all games with rest days calculated
                SELECT
                    sg.league,
                    sg."gameDate",
                    EXTRACT(YEAR FROM sg."gameDate")::text as season,
                    team,
                    sg."homeScore",
                    sg."awayScore",
                    is_home,
                    CASE WHEN is_home THEN sg."homeScore" ELSE sg."awayScore" END as goals_for,
                    CASE WHEN is_home THEN sg."awayScore" ELSE sg."homeScore" END as goals_against,
                    CASE
                        WHEN is_home AND sg."homeScore" > sg."awayScore" THEN 1
                        WHEN NOT is_home AND sg."awayScore" > sg."homeScore" THEN 1
                        WHEN sg."homeScore" = sg."awayScore" THEN 0.5
                        ELSE 0
                    END as win_points,
                    -- Calculate days since last game
                    LAG(sg."gameDate") OVER (PARTITION BY team ORDER BY sg."gameDate") as prev_game,
                    EXTRACT(EPOCH FROM (sg."gameDate" - LAG(sg."gameDate") OVER (PARTITION BY team ORDER BY sg."gameDate"))) / 86400 as rest_days,
                    -- Games in last 7 days
                    COUNT(*) OVER (PARTITION BY team ORDER BY sg."gameDate" RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) as games_last_7,
                    -- Games in last 14 days
                    COUNT(*) OVER (PARTITION BY team ORDER BY sg."gameDate" RANGE BETWEEN INTERVAL '14 days' PRECEDING AND CURRENT ROW) as games_last_14
                FROM "SportsGame" sg
                CROSS JOIN LATERAL (
                    SELECT sg."homeTeam" as team, true as is_home
                    UNION ALL
                    SELECT sg."awayTeam" as team, false as is_home
                ) t
                WHERE sg.league = %s
                  AND sg."homeScore" IS NOT NULL
            ),
            aggregated AS (
                SELECT
                    league,
                    season,
                    team,
                    COUNT(*) as total_games,
                    -- Short rest (< 4 days)
                    COUNT(CASE WHEN rest_days < 4 THEN 1 END) as games_short,
                    AVG(CASE WHEN rest_days < 4 THEN win_points END) as win_pct_short,
                    AVG(CASE WHEN rest_days < 4 THEN goals_for END) as goals_short,
                    -- Normal rest (4-6 days)
                    COUNT(CASE WHEN rest_days >= 4 AND rest_days <= 6 THEN 1 END) as games_normal,
                    AVG(CASE WHEN rest_days >= 4 AND rest_days <= 6 THEN win_points END) as win_pct_normal,
                    AVG(CASE WHEN rest_days >= 4 AND rest_days <= 6 THEN goals_for END) as goals_normal,
                    -- Long rest (> 6 days)
                    COUNT(CASE WHEN rest_days > 6 THEN 1 END) as games_long,
                    AVG(CASE WHEN rest_days > 6 THEN win_points END) as win_pct_long,
                    AVG(CASE WHEN rest_days > 6 THEN goals_for END) as goals_long,
                    -- Overall metrics
                    AVG(rest_days) as avg_rest,
                    AVG(games_last_7) as avg_games_7,
                    AVG(games_last_14) as avg_games_14,
                    -- Dense schedule (3+ games in 7 days)
                    AVG(CASE WHEN games_last_7 >= 3 THEN win_points END) as win_pct_dense,
                    AVG(CASE WHEN games_last_7 < 3 THEN win_points END) as win_pct_sparse
                FROM team_games
                WHERE rest_days IS NOT NULL
                GROUP BY league, season, team
                HAVING COUNT(*) >= 5
            )
            INSERT INTO "SoccerFixtureCongestion"
            (league, season, team, "totalGames",
             "gamesShortRest", "gamesNormalRest", "gamesLongRest",
             "winPctShortRest", "winPctNormalRest", "winPctLongRest",
             "goalsForShortRest", "goalsForNormalRest", "goalsForLongRest",
             "avgRestDays", "gamesIn7Days", "gamesIn14Days",
             "winPctDenseSchedule", "winPctNormalSchedule",
             "congestionImpact", "handlesCongetion")
            SELECT
                league, season, team, total_games,
                games_short, games_normal, games_long,
                ROUND(win_pct_short::numeric, 3),
                ROUND(win_pct_normal::numeric, 3),
                ROUND(win_pct_long::numeric, 3),
                ROUND(goals_short::numeric, 2),
                ROUND(goals_normal::numeric, 2),
                ROUND(goals_long::numeric, 2),
                ROUND(avg_rest::numeric, 1),
                ROUND(avg_games_7::numeric, 1),
                ROUND(avg_games_14::numeric, 1),
                ROUND(win_pct_dense::numeric, 3),
                ROUND(win_pct_sparse::numeric, 3),
                -- Classification
                CASE
                    WHEN games_short >= 3 AND win_pct_short > win_pct_normal + 0.1 THEN 'THRIVES_CONGESTED'
                    WHEN games_short >= 3 AND win_pct_short < win_pct_normal - 0.15 THEN 'STRUGGLES_CONGESTED'
                    ELSE 'NEUTRAL'
                END,
                games_short >= 3 AND win_pct_short >= win_pct_normal
            FROM aggregated
            ON CONFLICT (league, team, season) DO UPDATE SET
                "totalGames" = EXCLUDED."totalGames",
                "gamesShortRest" = EXCLUDED."gamesShortRest",
                "gamesNormalRest" = EXCLUDED."gamesNormalRest",
                "gamesLongRest" = EXCLUDED."gamesLongRest",
                "winPctShortRest" = EXCLUDED."winPctShortRest",
                "winPctNormalRest" = EXCLUDED."winPctNormalRest",
                "winPctLongRest" = EXCLUDED."winPctLongRest",
                "goalsForShortRest" = EXCLUDED."goalsForShortRest",
                "goalsForNormalRest" = EXCLUDED."goalsForNormalRest",
                "goalsForLongRest" = EXCLUDED."goalsForLongRest",
                "avgRestDays" = EXCLUDED."avgRestDays",
                "gamesIn7Days" = EXCLUDED."gamesIn7Days",
                "gamesIn14Days" = EXCLUDED."gamesIn14Days",
                "winPctDenseSchedule" = EXCLUDED."winPctDenseSchedule",
                "winPctNormalSchedule" = EXCLUDED."winPctNormalSchedule",
                "congestionImpact" = EXCLUDED."congestionImpact",
                "handlesCongetion" = EXCLUDED."handlesCongetion",
                "updatedAt" = NOW()
        ''', (league,))
        count = cur.rowcount
        conn.commit()
        print(f"  {league.upper()}: {count} team records")

    # Summary
    cur.execute('''
        SELECT league, "congestionImpact", COUNT(*),
               ROUND(AVG("winPctShortRest")::numeric, 3) as avg_short,
               ROUND(AVG("winPctNormalRest")::numeric, 3) as avg_normal
        FROM "SoccerFixtureCongestion"
        WHERE "congestionImpact" IS NOT NULL
        GROUP BY league, "congestionImpact"
        ORDER BY league, COUNT(*) DESC
    ''')
    print("\nCongestion Impact Distribution:")
    current_league = None
    for row in cur.fetchall():
        if row[0] != current_league:
            current_league = row[0]
            print(f"\n  {current_league.upper()}:")
        short = row[3] if row[3] else 0
        normal = row[4] if row[4] else 0
        print(f"    {row[1]}: {row[2]} teams (short rest: {short:.1%}, normal: {normal:.1%})")

    cur.execute('SELECT COUNT(*) FROM "SoccerFixtureCongestion"')
    total = cur.fetchone()[0]

    cur.close()
    conn.close()
    print(f"\n✅ Soccer fixture congestion complete: {total} records")

if __name__ == '__main__':
    compute_soccer_fixture_congestion()