#!/usr/bin/env python3
"""
Compute Injury Severity Impact
Correlation between injury status and team performance
Answers: "Cavs ATS with Mitchell minor injury?" "Team performance when star questionable?"
Supports: NBA, NFL, NHL
"""
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_injury_severity_impact():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create InjurySeverityImpact table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjurySeverityImpact" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            season VARCHAR(20),
            team VARCHAR(100) NOT NULL,
            -- Overall injury context
            "totalGames" INTEGER DEFAULT 0,
            "gamesWithStarOut" INTEGER DEFAULT 0,
            "gamesWithStarQuestionable" INTEGER DEFAULT 0,
            "gamesFullHealth" INTEGER DEFAULT 0,
            -- Performance by injury status
            "winPctFullHealth" NUMERIC,
            "winPctStarOut" NUMERIC,
            "winPctStarQuestionable" NUMERIC,
            -- Line movement correlation
            "avgLineMovementInjury" NUMERIC,
            "avgLineMovementNoInjury" NUMERIC,
            -- Star player impact
            "starPlayerImpact" NUMERIC,
            "teamDepthRating" VARCHAR(20),
            -- Classification
            "injuryImpactType" VARCHAR(30),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, team, season)
        )
    ''')
    conn.commit()
    print("InjurySeverityImpact table created/verified")

    # Compute injury impact for NBA
    print("\nComputing NBA injury severity impact...")
    cur.execute('''
        WITH injury_games AS (
            SELECT
                ish.league,
                ish.team,
                ish."relatedGameDate"::date as game_date,
                ish."playerName",
                ish.status,
                ish."lineMovement",
                -- Determine severity
                CASE
                    WHEN ish.status IN ('Out', 'OUT', 'Ruled Out') THEN 'OUT'
                    WHEN ish.status IN ('Questionable', 'QUESTIONABLE', 'Game-Time Decision', 'GTD') THEN 'QUESTIONABLE'
                    WHEN ish.status IN ('Doubtful', 'DOUBTFUL') THEN 'DOUBTFUL'
                    WHEN ish.status IN ('Probable', 'PROBABLE', 'Available') THEN 'PROBABLE'
                    ELSE 'UNKNOWN'
                END as injury_severity
            FROM "InjuryStatusHistory" ish
            WHERE ish.league = 'nba'
              AND ish."relatedGameDate" IS NOT NULL
        ),
        game_injury_context AS (
            SELECT
                sg.league,
                sg."gameDate"::date as game_date,
                t.team,
                EXTRACT(YEAR FROM sg."gameDate")::text as season,
                -- Win result
                CASE
                    WHEN t.is_home AND sg."homeScore" > sg."awayScore" THEN 1
                    WHEN NOT t.is_home AND sg."awayScore" > sg."homeScore" THEN 1
                    ELSE 0
                END as won,
                -- Check for any OUT injuries for this team
                (SELECT COUNT(*) FROM injury_games ig
                 WHERE ig.team = t.team AND ig.game_date = sg."gameDate"::date
                   AND ig.injury_severity = 'OUT') as players_out,
                -- Check for any questionable
                (SELECT COUNT(*) FROM injury_games ig
                 WHERE ig.team = t.team AND ig.game_date = sg."gameDate"::date
                   AND ig.injury_severity = 'QUESTIONABLE') as players_questionable,
                -- Average line movement
                (SELECT AVG(ig."lineMovement") FROM injury_games ig
                 WHERE ig.team = t.team AND ig.game_date = sg."gameDate"::date
                   AND ig."lineMovement" IS NOT NULL) as avg_line_move
            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 = 'nba'
              AND sg."homeScore" IS NOT NULL
        ),
        aggregated AS (
            SELECT
                league,
                season,
                team,
                COUNT(*) as total_games,
                -- Games by injury context
                COUNT(CASE WHEN players_out > 0 THEN 1 END) as games_star_out,
                COUNT(CASE WHEN players_questionable > 0 AND players_out = 0 THEN 1 END) as games_questionable,
                COUNT(CASE WHEN players_out = 0 AND players_questionable = 0 THEN 1 END) as games_healthy,
                -- Win rates
                AVG(CASE WHEN players_out = 0 AND players_questionable = 0 THEN won END) as win_pct_healthy,
                AVG(CASE WHEN players_out > 0 THEN won END) as win_pct_out,
                AVG(CASE WHEN players_questionable > 0 AND players_out = 0 THEN won END) as win_pct_questionable,
                -- Line movements
                AVG(CASE WHEN players_out > 0 OR players_questionable > 0 THEN avg_line_move END) as avg_move_injury,
                AVG(CASE WHEN players_out = 0 AND players_questionable = 0 THEN avg_line_move END) as avg_move_healthy
            FROM game_injury_context
            GROUP BY league, season, team
            HAVING COUNT(*) >= 5
        )
        INSERT INTO "InjurySeverityImpact"
        (league, season, team, "totalGames",
         "gamesWithStarOut", "gamesWithStarQuestionable", "gamesFullHealth",
         "winPctFullHealth", "winPctStarOut", "winPctStarQuestionable",
         "avgLineMovementInjury", "avgLineMovementNoInjury",
         "starPlayerImpact", "teamDepthRating", "injuryImpactType")
        SELECT
            league, season, team, total_games,
            games_star_out, games_questionable, games_healthy,
            ROUND(win_pct_healthy::numeric, 3),
            ROUND(win_pct_out::numeric, 3),
            ROUND(win_pct_questionable::numeric, 3),
            ROUND(avg_move_injury::numeric, 2),
            ROUND(avg_move_healthy::numeric, 2),
            -- Star player impact (difference in win rate healthy vs injured)
            ROUND((COALESCE(win_pct_healthy, 0) - COALESCE(win_pct_out, 0))::numeric, 3),
            -- Team depth rating
            CASE
                WHEN games_star_out >= 3 AND win_pct_out >= 0.45 THEN 'DEEP_ROSTER'
                WHEN games_star_out >= 3 AND win_pct_out < 0.35 THEN 'STAR_DEPENDENT'
                ELSE 'AVERAGE_DEPTH'
            END,
            -- Injury impact classification
            CASE
                WHEN games_star_out >= 3 AND (win_pct_healthy - win_pct_out) > 0.25 THEN 'HIGH_INJURY_IMPACT'
                WHEN games_star_out >= 3 AND (win_pct_healthy - win_pct_out) < 0.1 THEN 'RESILIENT'
                WHEN games_questionable >= 3 AND (win_pct_healthy - win_pct_questionable) > 0.15 THEN 'QUESTIONABLE_MATTERS'
                ELSE 'NORMAL_IMPACT'
            END
        FROM aggregated
        ON CONFLICT (league, team, season) DO UPDATE SET
            "totalGames" = EXCLUDED."totalGames",
            "gamesWithStarOut" = EXCLUDED."gamesWithStarOut",
            "gamesWithStarQuestionable" = EXCLUDED."gamesWithStarQuestionable",
            "gamesFullHealth" = EXCLUDED."gamesFullHealth",
            "winPctFullHealth" = EXCLUDED."winPctFullHealth",
            "winPctStarOut" = EXCLUDED."winPctStarOut",
            "winPctStarQuestionable" = EXCLUDED."winPctStarQuestionable",
            "avgLineMovementInjury" = EXCLUDED."avgLineMovementInjury",
            "avgLineMovementNoInjury" = EXCLUDED."avgLineMovementNoInjury",
            "starPlayerImpact" = EXCLUDED."starPlayerImpact",
            "teamDepthRating" = EXCLUDED."teamDepthRating",
            "injuryImpactType" = EXCLUDED."injuryImpactType",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA: {nba_count} team injury impact records")

    # Summary
    cur.execute('''
        SELECT "injuryImpactType", COUNT(*),
               ROUND(AVG("starPlayerImpact")::numeric, 3) as avg_impact,
               ROUND(AVG("gamesWithStarOut")::numeric, 0) as avg_games_out
        FROM "InjurySeverityImpact"
        WHERE "injuryImpactType" IS NOT NULL
        GROUP BY "injuryImpactType"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nInjury Impact Distribution:")
    for row in cur.fetchall():
        impact = row[2] if row[2] else 0
        games = row[3] if row[3] else 0
        print(f"  {row[0]}: {row[1]} teams (avg impact: {impact:+.3f}, avg games injured: {games:.0f})")

    # Show high-impact teams
    cur.execute('''
        SELECT team, season, "starPlayerImpact", "teamDepthRating",
               "winPctFullHealth", "winPctStarOut"
        FROM "InjurySeverityImpact"
        WHERE "injuryImpactType" = 'HIGH_INJURY_IMPACT'
          AND "gamesWithStarOut" >= 3
        ORDER BY "starPlayerImpact" DESC
        LIMIT 10
    ''')
    results = cur.fetchall()
    if results:
        print("\nHigh Injury Impact Teams:")
        for row in results:
            healthy = row[4] * 100 if row[4] else 0
            injured = row[5] * 100 if row[5] else 0
            print(f"  {row[0]} ({row[1]}): {healthy:.0f}% healthy vs {injured:.0f}% injured (diff: {row[2]:+.3f})")

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

    cur.close()
    conn.close()
    print(f"\n✅ Injury severity impact complete: {total} records")

if __name__ == '__main__':
    compute_injury_severity_impact()