#!/usr/bin/env python3
"""
Compute Opponent Ratings at Game Time
Adds opponent win% and strength to game/player analysis
Fixes: Q2, Q11, Q15 - opponent quality splits
"""
import psycopg2

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

    # Create OpponentRating table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "OpponentRating" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT,
            "gameDate" DATE,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            "homeWinPctAtGame" NUMERIC,
            "awayWinPctAtGame" NUMERIC,
            "homeStrength" VARCHAR(20),
            "awayStrength" VARCHAR(20),
            "matchupType" VARCHAR(30),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId")
        )
    ''')
    conn.commit()
    print("OpponentRating table created/verified")

    # Compute opponent ratings for each game
    print("\nComputing opponent ratings...")
    cur.execute('''
        WITH team_records AS (
            SELECT 
                league,
                "homeTeam" as team,
                "gameDate",
                SUM(CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END) 
                    OVER (PARTITION BY league, "homeTeam" ORDER BY "gameDate" 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as wins,
                COUNT(*) OVER (PARTITION BY league, "homeTeam" ORDER BY "gameDate" 
                              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as games
            FROM "SportsGame"
            WHERE "homeScore" IS NOT NULL
            UNION ALL
            SELECT 
                league,
                "awayTeam" as team,
                "gameDate",
                SUM(CASE WHEN "awayScore" > "homeScore" THEN 1 ELSE 0 END) 
                    OVER (PARTITION BY league, "awayTeam" ORDER BY "gameDate" 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as wins,
                COUNT(*) OVER (PARTITION BY league, "awayTeam" ORDER BY "gameDate" 
                              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as games
            FROM "SportsGame"
            WHERE "homeScore" IS NOT NULL
        ),
        team_winpct AS (
            SELECT league, team, "gameDate",
                   ROUND((wins::numeric / NULLIF(games, 0))::numeric, 3) as win_pct
            FROM team_records
            WHERE games >= 5
        )
        INSERT INTO "OpponentRating"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", 
         "homeWinPctAtGame", "awayWinPctAtGame", "homeStrength", "awayStrength", "matchupType")
        SELECT DISTINCT ON (sg.league, sg.id)
            sg.league,
            sg.id::text,
            sg."gameDate",
            sg."homeTeam",
            sg."awayTeam",
            hw.win_pct,
            aw.win_pct,
            CASE 
                WHEN hw.win_pct >= 0.6 THEN 'ELITE'
                WHEN hw.win_pct >= 0.5 THEN 'GOOD'
                WHEN hw.win_pct >= 0.4 THEN 'AVERAGE'
                ELSE 'WEAK'
            END,
            CASE 
                WHEN aw.win_pct >= 0.6 THEN 'ELITE'
                WHEN aw.win_pct >= 0.5 THEN 'GOOD'
                WHEN aw.win_pct >= 0.4 THEN 'AVERAGE'
                ELSE 'WEAK'
            END,
            CASE
                WHEN hw.win_pct >= 0.6 AND aw.win_pct >= 0.6 THEN 'ELITE_VS_ELITE'
                WHEN hw.win_pct >= 0.6 OR aw.win_pct >= 0.6 THEN 'ELITE_VS_OTHER'
                WHEN hw.win_pct < 0.4 AND aw.win_pct < 0.4 THEN 'WEAK_VS_WEAK'
                ELSE 'COMPETITIVE'
            END
        FROM "SportsGame" sg
        LEFT JOIN team_winpct hw ON sg.league = hw.league 
            AND sg."homeTeam" = hw.team AND sg."gameDate" = hw."gameDate"
        LEFT JOIN team_winpct aw ON sg.league = aw.league 
            AND sg."awayTeam" = aw.team AND sg."gameDate" = aw."gameDate"
        WHERE sg."gameDate" > '2024-01-01'
          AND (hw.win_pct IS NOT NULL OR aw.win_pct IS NOT NULL)
        ORDER BY sg.league, sg.id, sg."gameDate" DESC
        ON CONFLICT (league, "gameId") DO UPDATE SET
            "homeWinPctAtGame" = EXCLUDED."homeWinPctAtGame",
            "awayWinPctAtGame" = EXCLUDED."awayWinPctAtGame",
            "homeStrength" = EXCLUDED."homeStrength",
            "awayStrength" = EXCLUDED."awayStrength",
            "matchupType" = EXCLUDED."matchupType",
            "updatedAt" = NOW()
    ''')
    count = cur.rowcount
    conn.commit()
    print(f"  Opponent ratings computed: {count}")

    # Summary
    cur.execute('''
        SELECT league, "matchupType", COUNT(*)
        FROM "OpponentRating"
        GROUP BY league, "matchupType"
        ORDER BY league, COUNT(*) DESC
    ''')
    print("\nMatchup Type Summary:")
    for row in cur.fetchall():
        print(f"  {row[0].upper()} {row[1]}: {row[2]}")

    cur.close()
    conn.close()
    print(f"\n✅ Opponent ratings complete: {count} games")
    return count

if __name__ == '__main__':
    compute_opponent_ratings()