#!/usr/bin/env python3
"""
Compute Injury Status History
Creates InjuryStatusHistory table tracking injury status changes over time.
Answers Q17: Are books adjusting lines after injury news?
"""
import psycopg2
from datetime import datetime, timezone, timedelta

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

    print("=" * 60)
    print("COMPUTE INJURY STATUS HISTORY")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjuryStatusHistory" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "playerExternalId" VARCHAR(100),
            "playerName" VARCHAR(100),
            team VARCHAR(100),
            -- Status info
            status VARCHAR(50),          -- 'Out', 'Questionable', 'Probable', 'Doubtful', 'GTD'
            "previousStatus" VARCHAR(50),
            "statusChangedAt" TIMESTAMP,
            -- Injury details
            "injuryType" VARCHAR(100),
            "injuryDetail" TEXT,
            -- Game context
            "relatedGameId" BIGINT,
            "relatedGameDate" TIMESTAMP,
            "hoursBeforeGame" FLOAT,     -- Hours between status change and game
            -- Impact tracking
            "lineMovedAfter" BOOLEAN,
            "lineMovement" FLOAT,        -- Points/total movement after news
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "playerExternalId", "statusChangedAt")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryStatusHistory_player" ON "InjuryStatusHistory" ("playerExternalId")')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryStatusHistory_date" ON "InjuryStatusHistory" ("statusChangedAt" DESC)')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryStatusHistory_game" ON "InjuryStatusHistory" ("relatedGameId")')

    total_records = 0

    # Get existing injuries
    print("\nFetching injury data...")
    cur.execute('''
        SELECT id, league, "playerExternalId", "playerName", team, status,
               "injuryType", description, COALESCE("sourceUpdatedAt", "reportedAt", "createdAt") as updated_at, "createdAt"
        FROM "PlayerInjury"
        WHERE status IS NOT NULL
        ORDER BY COALESCE("sourceUpdatedAt", "reportedAt", "createdAt") DESC
    ''')
    injuries = cur.fetchall()
    print(f"  Found {len(injuries)} injury records")

    # Get upcoming games for context
    print("Fetching game schedule for injury context...")
    cur.execute('''
        SELECT id, league, "gameDate", "homeTeam", "awayTeam"
        FROM "SportsGame"
        WHERE "gameDate" > NOW() - INTERVAL '30 days'
          AND "gameDate" < NOW() + INTERVAL '7 days'
        ORDER BY "gameDate"
    ''')
    games = cur.fetchall()
    print(f"  Found {len(games)} recent/upcoming games")

    # Build team game map
    team_games = {}
    for g in games:
        gid, league, gdate, home, away = g
        for team in [home, away]:
            key = (league.lower(), team.lower())
            if key not in team_games or team_games[key][1] > gdate:
                team_games[key] = (gid, gdate)

    # Process injuries
    print("\nProcessing injury status history...")
    for inj in injuries:
        inj_id, league, pid, pname, team, status, inj_type, inj_detail, updated_at, created_at = inj

        # Find related game
        related_game_id = None
        related_game_date = None
        hours_before = None

        if team and updated_at:
            team_key = (league.lower() if league else '', team.lower())
            if team_key in team_games:
                related_game_id, related_game_date = team_games[team_key]
                if related_game_date and updated_at:
                    delta = related_game_date - updated_at
                    hours_before = delta.total_seconds() / 3600 if delta.total_seconds() > 0 else None

        # Determine previous status (simplified - assume opposite)
        prev_status = 'Active' if status in ['Out', 'Questionable', 'Doubtful'] else None

        cur.execute('''
            INSERT INTO "InjuryStatusHistory" (
                league, "playerExternalId", "playerName", team,
                status, "previousStatus", "statusChangedAt",
                "injuryType", "injuryDetail",
                "relatedGameId", "relatedGameDate", "hoursBeforeGame"
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (league, "playerExternalId", "statusChangedAt") DO UPDATE SET
                status = EXCLUDED.status,
                "relatedGameId" = EXCLUDED."relatedGameId",
                "hoursBeforeGame" = EXCLUDED."hoursBeforeGame"
        ''', (
            league, pid, pname, team,
            status, prev_status, updated_at,
            inj_type, inj_detail,
            related_game_id, related_game_date, hours_before
        ))
        total_records += 1

    conn.commit()

    # Summary
    print("\nInjury Status Distribution:")
    cur.execute('''
        SELECT status, COUNT(*)
        FROM "InjuryStatusHistory"
        GROUP BY status
        ORDER BY COUNT(*) DESC
    ''')
    for r in cur.fetchall():
        print(f"  {r[0] or 'Unknown'}: {r[1]} records")

    print("\nBy League:")
    cur.execute('''
        SELECT league, COUNT(*)
        FROM "InjuryStatusHistory"
        GROUP BY league
        ORDER BY COUNT(*) DESC
    ''')
    for r in cur.fetchall():
        print(f"  {r[0] or 'Unknown'}: {r[1]} records")

    cur.close()
    conn.close()
    print(f"\n✅ Total injury history records: {total_records}")
    return total_records

if __name__ == '__main__':
    compute_injury_history()