#!/usr/bin/env python3
"""
Compute Opponent Style Classification
Classify teams by playing style: pace, 3PT shooting, size, defensive scheme
Answers: "Jokić vs small-ball?" "Trae vs switching defense?" "Team vs elite defense?"
Supports: NBA, NFL
"""
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_opponent_style():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create TeamPlayStyle table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "TeamPlayStyle" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            season VARCHAR(20),
            team VARCHAR(100) NOT NULL,
            -- Pace metrics
            "avgPace" NUMERIC,
            "paceRank" INTEGER,
            "paceStyle" VARCHAR(20),
            -- Shooting style
            "fg3aPerGame" NUMERIC,
            "fg3Pct" NUMERIC,
            "threePointRank" INTEGER,
            "shootingStyle" VARCHAR(20),
            -- Size/rebounding
            "rebPerGame" NUMERIC,
            "rebRank" INTEGER,
            -- Defensive metrics
            "defRating" NUMERIC,
            "defRank" INTEGER,
            "defStyle" VARCHAR(20),
            -- Offensive metrics
            "offRating" NUMERIC,
            "offRank" INTEGER,
            "offStyle" VARCHAR(20),
            -- Overall classification
            "primaryStyle" VARCHAR(30),
            "secondaryStyle" VARCHAR(30),
            "isSmallBall" BOOLEAN DEFAULT false,
            "isSwitchingDefense" BOOLEAN DEFAULT false,
            "isEliteDefense" BOOLEAN DEFAULT false,
            "isEliteOffense" BOOLEAN DEFAULT false,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, team, season)
        )
    ''')
    conn.commit()
    print("TeamPlayStyle table created/verified")

    # NBA team style classification
    print("\nComputing NBA team play styles...")
    cur.execute('''
        WITH team_stats AS (
            SELECT
                tgm.league,
                tgm.team,
                EXTRACT(YEAR FROM tgm."gameDate")::text as season,
                -- Aggregate stats
                AVG(CASE WHEN tgm."statKey" = 'nba_game_pts' THEN tgm.value END) as ppg,
                AVG(CASE WHEN tgm."statKey" = 'nba_game_fg3a' THEN tgm.value END) as fg3a,
                AVG(CASE WHEN tgm."statKey" = 'nba_game_fg3m' THEN tgm.value END) as fg3m,
                AVG(CASE WHEN tgm."statKey" = 'nba_game_fga' THEN tgm.value END) as fga,
                AVG(CASE WHEN tgm."statKey" = 'nba_game_reb' THEN tgm.value END) as rpg,
                AVG(CASE WHEN tgm."statKey" = 'nba_game_ast' THEN tgm.value END) as apg,
                AVG(CASE WHEN tgm."statKey" = 'nba_team_ppg' THEN tgm.value END) as team_ppg,
                AVG(CASE WHEN tgm."statKey" = 'nba_team_3pt_pct' THEN tgm.value END) as fg3_pct,
                COUNT(DISTINCT tgm."gameDate") as games
            FROM "TeamGameMetric" tgm
            WHERE tgm.league = 'nba'
              AND tgm."statKey" IN ('nba_game_pts', 'nba_game_fg3a', 'nba_game_fg3m',
                                    'nba_game_fga', 'nba_game_reb', 'nba_game_ast',
                                    'nba_team_ppg', 'nba_team_3pt_pct')
            GROUP BY tgm.league, tgm.team, EXTRACT(YEAR FROM tgm."gameDate")
            HAVING COUNT(DISTINCT tgm."gameDate") >= 5
        ),
        ranked_stats AS (
            SELECT
                *,
                -- Calculate pace proxy (FGA + assists approximates possessions)
                (COALESCE(fga, 0) + COALESCE(apg, 0) * 0.5) as pace_proxy,
                -- 3PT rate
                CASE WHEN fga > 0 THEN fg3a / fga ELSE 0 END as three_rate,
                -- Rankings within season
                RANK() OVER (PARTITION BY season ORDER BY COALESCE(fga, 0) + COALESCE(apg, 0) * 0.5 DESC) as pace_rank,
                RANK() OVER (PARTITION BY season ORDER BY fg3a DESC NULLS LAST) as fg3_rank,
                RANK() OVER (PARTITION BY season ORDER BY rpg DESC NULLS LAST) as reb_rank,
                RANK() OVER (PARTITION BY season ORDER BY ppg DESC NULLS LAST) as off_rank,
                -- Defensive rank (lower PPG allowed = better)
                RANK() OVER (PARTITION BY season ORDER BY ppg ASC NULLS LAST) as def_rank,
                COUNT(*) OVER (PARTITION BY season) as total_teams
            FROM team_stats
        )
        INSERT INTO "TeamPlayStyle"
        (league, season, team, "avgPace", "paceRank", "paceStyle",
         "fg3aPerGame", "fg3Pct", "threePointRank", "shootingStyle",
         "rebPerGame", "rebRank", "defRating", "defRank", "defStyle",
         "offRating", "offRank", "offStyle",
         "primaryStyle", "secondaryStyle",
         "isSmallBall", "isSwitchingDefense", "isEliteDefense", "isEliteOffense")
        SELECT
            league, season, team,
            ROUND(pace_proxy::numeric, 1),
            pace_rank,
            CASE
                WHEN pace_rank <= total_teams * 0.25 THEN 'FAST'
                WHEN pace_rank >= total_teams * 0.75 THEN 'SLOW'
                ELSE 'MODERATE'
            END,
            ROUND(fg3a::numeric, 1),
            ROUND(fg3_pct::numeric, 3),
            fg3_rank,
            CASE
                WHEN fg3_rank <= total_teams * 0.25 THEN 'THREE_HEAVY'
                WHEN fg3_rank >= total_teams * 0.75 THEN 'PAINT_FOCUSED'
                ELSE 'BALANCED'
            END,
            ROUND(rpg::numeric, 1),
            reb_rank,
            ROUND(ppg::numeric, 1),
            def_rank,
            CASE
                WHEN def_rank <= total_teams * 0.2 THEN 'ELITE_DEFENSE'
                WHEN def_rank <= total_teams * 0.4 THEN 'GOOD_DEFENSE'
                WHEN def_rank >= total_teams * 0.8 THEN 'POOR_DEFENSE'
                ELSE 'AVERAGE_DEFENSE'
            END,
            ROUND(ppg::numeric, 1),
            off_rank,
            CASE
                WHEN off_rank <= total_teams * 0.2 THEN 'ELITE_OFFENSE'
                WHEN off_rank <= total_teams * 0.4 THEN 'GOOD_OFFENSE'
                WHEN off_rank >= total_teams * 0.8 THEN 'POOR_OFFENSE'
                ELSE 'AVERAGE_OFFENSE'
            END,
            -- Primary style (most distinctive trait)
            CASE
                WHEN pace_rank <= total_teams * 0.2 AND fg3_rank <= total_teams * 0.3 THEN 'FAST_THREE_HEAVY'
                WHEN pace_rank <= total_teams * 0.25 THEN 'RUN_AND_GUN'
                WHEN fg3_rank <= total_teams * 0.2 THEN 'PERIMETER_ORIENTED'
                WHEN reb_rank <= total_teams * 0.2 THEN 'PHYSICAL_INSIDE'
                WHEN def_rank <= total_teams * 0.2 THEN 'DEFENSE_FIRST'
                ELSE 'BALANCED'
            END,
            -- Secondary style
            CASE
                WHEN def_rank <= total_teams * 0.3 AND off_rank <= total_teams * 0.3 THEN 'TWO_WAY'
                WHEN def_rank >= total_teams * 0.7 THEN 'OFFENSE_ONLY'
                WHEN off_rank >= total_teams * 0.7 THEN 'DEFENSE_ONLY'
                ELSE NULL
            END,
            -- Boolean flags
            fg3_rank <= total_teams * 0.3 AND reb_rank >= total_teams * 0.6,  -- Small ball
            fg3_rank <= total_teams * 0.4 AND def_rank <= total_teams * 0.5,  -- Switching D proxy
            def_rank <= total_teams * 0.2,  -- Elite defense
            off_rank <= total_teams * 0.2   -- Elite offense
        FROM ranked_stats
        ON CONFLICT (league, team, season) DO UPDATE SET
            "avgPace" = EXCLUDED."avgPace",
            "paceRank" = EXCLUDED."paceRank",
            "paceStyle" = EXCLUDED."paceStyle",
            "fg3aPerGame" = EXCLUDED."fg3aPerGame",
            "fg3Pct" = EXCLUDED."fg3Pct",
            "threePointRank" = EXCLUDED."threePointRank",
            "shootingStyle" = EXCLUDED."shootingStyle",
            "rebPerGame" = EXCLUDED."rebPerGame",
            "rebRank" = EXCLUDED."rebRank",
            "defRating" = EXCLUDED."defRating",
            "defRank" = EXCLUDED."defRank",
            "defStyle" = EXCLUDED."defStyle",
            "offRating" = EXCLUDED."offRating",
            "offRank" = EXCLUDED."offRank",
            "offStyle" = EXCLUDED."offStyle",
            "primaryStyle" = EXCLUDED."primaryStyle",
            "secondaryStyle" = EXCLUDED."secondaryStyle",
            "isSmallBall" = EXCLUDED."isSmallBall",
            "isSwitchingDefense" = EXCLUDED."isSwitchingDefense",
            "isEliteDefense" = EXCLUDED."isEliteDefense",
            "isEliteOffense" = EXCLUDED."isEliteOffense",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA: {nba_count} team style records")

    # Summary
    cur.execute('''
        SELECT "primaryStyle", COUNT(*),
               ROUND(AVG("avgPace")::numeric, 1) as avg_pace,
               ROUND(AVG("fg3aPerGame")::numeric, 1) as avg_3pa
        FROM "TeamPlayStyle"
        WHERE "primaryStyle" IS NOT NULL
        GROUP BY "primaryStyle"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nPrimary Style Distribution:")
    for row in cur.fetchall():
        pace = row[2] if row[2] else 0
        fg3a = row[3] if row[3] else 0
        print(f"  {row[0]}: {row[1]} teams (avg pace: {pace:.1f}, avg 3PA: {fg3a:.1f})")

    # Show elite teams
    cur.execute('''
        SELECT team, season, "primaryStyle", "isEliteDefense", "isEliteOffense", "isSmallBall"
        FROM "TeamPlayStyle"
        WHERE "isEliteDefense" = true OR "isEliteOffense" = true
        ORDER BY season DESC, team
        LIMIT 15
    ''')
    results = cur.fetchall()
    if results:
        print("\nElite Teams:")
        for row in results:
            flags = []
            if row[3]: flags.append("Elite D")
            if row[4]: flags.append("Elite O")
            if row[5]: flags.append("Small-Ball")
            print(f"  {row[0]} ({row[1]}): {row[2]} - {', '.join(flags)}")

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

    cur.close()
    conn.close()
    print(f"\n✅ Team play style complete: {total} records")

if __name__ == '__main__':
    compute_opponent_style()