#!/usr/bin/env python3
"""
Q3: Compute injury timing impact on betting lines
Correlates injury announcements with line movements
"""
import psycopg2
from datetime import datetime, timezone

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_timing():
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    # Create InjuryLineImpact table (detailed per-injury analysis)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjuryLineImpact" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT NOT NULL,
            "gameDate" DATE,
            team VARCHAR(100),
            "playerName" VARCHAR(200),
            "injuryStatus" VARCHAR(50),
            "injuryReportedAt" TIMESTAMP WITH TIME ZONE,
            "hoursBeforeGame" NUMERIC,
            "lineBeforeInjury" NUMERIC,
            "lineAfterInjury" NUMERIC,
            "lineImpact" NUMERIC,
            "impactDirection" VARCHAR(20),
            "playerImportance" VARCHAR(20),
            "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId", "playerName")
        )
    ''')

    # Create InjuryTimingPatterns table (aggregate patterns)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjuryTimingPatterns" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "injuryStatus" VARCHAR(50) NOT NULL,
            "timingBucket" VARCHAR(20) NOT NULL,
            "sampleSize" INTEGER,
            "avgLineImpact" NUMERIC,
            "maxLineImpact" NUMERIC,
            "bettingWindow" TEXT,
            "recommendation" TEXT,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "injuryStatus", "timingBucket")
        )
    ''')

    conn.commit()
    print("Tables created/verified")

    # Check if we have injury data with timestamps
    cur.execute('''
        SELECT COUNT(*) FROM "PlayerInjury"
        WHERE "updatedAt" IS NOT NULL
    ''')
    injury_count = cur.fetchone()[0]

    print(f"Found {injury_count} injuries with timestamps")

    # Get injuries and match with game odds
    cur.execute('''
        INSERT INTO "InjuryLineImpact"
        (league, "gameId", "gameDate", team, "playerName", "injuryStatus",
         "injuryReportedAt", "hoursBeforeGame", "playerImportance")
        SELECT DISTINCT ON (i.league, gsm."gameId", i."playerName")
            i.league,
            gsm."gameId"::text,
            gsm."gameDate",
            i.team,
            i."playerName",
            i.status,
            COALESCE(i."reportedAt", i."updatedAt") as reported_at,
            EXTRACT(EPOCH FROM (gsm."gameDate"::timestamp - COALESCE(i."reportedAt", i."updatedAt"))) / 3600 as hours_before,
            CASE
                WHEN i.description ILIKE '%star%' OR i.description ILIKE '%all-star%' THEN 'star'
                WHEN i.description ILIKE '%starter%' OR i.description ILIKE '%key%' THEN 'starter'
                ELSE 'rotation'
            END as importance
        FROM "PlayerInjury" i
        JOIN "GameScheduleMeta" gsm ON i.league = gsm.league
            AND i.team = gsm.team
            AND gsm."gameDate" >= COALESCE(i."reportedAt", i."updatedAt")::date
            AND gsm."gameDate" <= COALESCE(i."reportedAt", i."updatedAt")::date + INTERVAL '7 days'
        WHERE COALESCE(i."reportedAt", i."updatedAt") IS NOT NULL
        ORDER BY i.league, gsm."gameId", i."playerName", gsm."gameDate"
        ON CONFLICT (league, "gameId", "playerName") DO UPDATE SET
            "injuryStatus" = EXCLUDED."injuryStatus",
            "injuryReportedAt" = EXCLUDED."injuryReportedAt",
            "hoursBeforeGame" = EXCLUDED."hoursBeforeGame"
    ''')
    injury_records = cur.rowcount
    conn.commit()
    print(f"InjuryLineImpact populated: {injury_records} injury-game matches")

    # Match with line movements
    cur.execute('''
        UPDATE "InjuryLineImpact" ili
        SET "lineBeforeInjury" = before_odds.line_value,
            "lineAfterInjury" = after_odds.line_value
        FROM (
            SELECT DISTINCT ON (go.league, go."gameId")
                go.league, go."gameId", go."lineValue" as line_value
            FROM "GameOdds" go
            JOIN "InjuryLineImpact" ili2 ON go.league = ili2.league
                AND go."gameId" = ili2."gameId"
            WHERE go."fetchedAt" < ili2."injuryReportedAt"
              AND go.market = 'spreads'
            ORDER BY go.league, go."gameId", go."fetchedAt" DESC
        ) before_odds,
        (
            SELECT DISTINCT ON (go.league, go."gameId")
                go.league, go."gameId", go."lineValue" as line_value
            FROM "GameOdds" go
            JOIN "InjuryLineImpact" ili2 ON go.league = ili2.league
                AND go."gameId" = ili2."gameId"
            WHERE go."fetchedAt" > ili2."injuryReportedAt"
              AND go.market = 'spreads'
            ORDER BY go.league, go."gameId", go."fetchedAt" ASC
        ) after_odds
        WHERE ili.league = before_odds.league
          AND ili."gameId" = before_odds."gameId"
          AND ili.league = after_odds.league
          AND ili."gameId" = after_odds."gameId"
    ''')
    conn.commit()

    # Calculate line impact
    cur.execute('''
        UPDATE "InjuryLineImpact"
        SET "lineImpact" = "lineAfterInjury" - "lineBeforeInjury",
            "impactDirection" = CASE
                WHEN "lineAfterInjury" - "lineBeforeInjury" > 0.5 THEN 'moved_against'
                WHEN "lineAfterInjury" - "lineBeforeInjury" < -0.5 THEN 'moved_for'
                ELSE 'minimal'
            END
        WHERE "lineBeforeInjury" IS NOT NULL AND "lineAfterInjury" IS NOT NULL
    ''')
    conn.commit()

    # Generate timing patterns
    cur.execute('''
        INSERT INTO "InjuryTimingPatterns"
        (league, "injuryStatus", "timingBucket", "sampleSize", "avgLineImpact",
         "maxLineImpact", "bettingWindow", recommendation)
        SELECT
            league,
            "injuryStatus",
            CASE
                WHEN "hoursBeforeGame" > 24 THEN 'early'
                WHEN "hoursBeforeGame" > 6 THEN 'midday'
                WHEN "hoursBeforeGame" > 2 THEN 'pregame'
                ELSE 'late'
            END as timing_bucket,
            COUNT(*) as sample_size,
            ROUND(AVG("lineImpact")::numeric, 2) as avg_impact,
            ROUND(MAX(ABS("lineImpact"))::numeric, 2) as max_impact,
            CASE
                WHEN AVG("lineImpact") > 1 THEN '0-30 min after announcement'
                WHEN AVG("lineImpact") > 0.5 THEN '30-60 min after announcement'
                ELSE 'No clear window'
            END as betting_window,
            CASE
                WHEN AVG("lineImpact") > 1.5 THEN 'BET_IMMEDIATELY: Large line moves expected'
                WHEN AVG("lineImpact") > 0.5 THEN 'ACT_QUICKLY: Moderate moves expected'
                ELSE 'MONITOR: Lines typically stable'
            END as recommendation
        FROM "InjuryLineImpact"
        WHERE "lineImpact" IS NOT NULL
        GROUP BY league, "injuryStatus",
            CASE
                WHEN "hoursBeforeGame" > 24 THEN 'early'
                WHEN "hoursBeforeGame" > 6 THEN 'midday'
                WHEN "hoursBeforeGame" > 2 THEN 'pregame'
                ELSE 'late'
            END
        HAVING COUNT(*) >= 3
        ON CONFLICT (league, "injuryStatus", "timingBucket") DO UPDATE SET
            "sampleSize" = EXCLUDED."sampleSize",
            "avgLineImpact" = EXCLUDED."avgLineImpact",
            "maxLineImpact" = EXCLUDED."maxLineImpact",
            "bettingWindow" = EXCLUDED."bettingWindow",
            recommendation = EXCLUDED.recommendation,
            "updatedAt" = NOW()
    ''')
    pattern_records = cur.rowcount
    conn.commit()

    print(f"InjuryTimingPatterns computed: {pattern_records} patterns")

    # Print summary
    cur.execute('''
        SELECT "injuryStatus", COUNT(*), ROUND(AVG("avgLineImpact")::numeric, 2)
        FROM "InjuryTimingPatterns"
        GROUP BY "injuryStatus"
        ORDER BY AVG("avgLineImpact") DESC
    ''')
    print("\nInjury Impact by Status:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} patterns, avg impact: {row[2]} pts")

    cur.close()
    conn.close()

    print(f"\n✅ Injury timing analysis complete: {injury_records} injuries, {pattern_records} patterns")
    return {'injury_records': injury_records, 'pattern_records': pattern_records}

if __name__ == '__main__':
    compute_injury_timing()