#!/usr/bin/env python3
"""
Compute Injury-to-Line-Movement Correlation
Links injury announcements to subsequent line movement.
Enables Q15: Are injury designations being priced correctly?
"""
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_line_impact():
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("COMPUTE INJURY-TO-LINE-MOVEMENT CORRELATION")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create InjuryLineImpact table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjuryLineImpact" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "injuryHistoryId" BIGINT,
            "playerName" VARCHAR(100),
            team VARCHAR(100),
            status VARCHAR(50),
            "statusChangedAt" TIMESTAMP,
            "gameId" BIGINT,
            "gameDate" TIMESTAMP,
            "hoursBeforeGame" FLOAT,
            -- Line before injury news
            "spreadBefore" FLOAT,
            "totalBefore" FLOAT,
            -- Line after injury news (closest snapshot)
            "spreadAfter" FLOAT,
            "totalAfter" FLOAT,
            -- Movement
            "spreadMovement" FLOAT,
            "totalMovement" FLOAT,
            "lineMovedFavorably" BOOLEAN,
            -- Timing
            "minutesToLineMove" FLOAT,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "injuryHistoryId", "gameId")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryLineImpact_game" ON "InjuryLineImpact" ("gameId")')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryLineImpact_player" ON "InjuryLineImpact" ("playerName")')

    total_impacts = 0
    updates_to_history = 0

    # Get injury records with related games
    print("\nFetching injury records with game context...")
    cur.execute('''
        SELECT ih.id, ih.league, ih."playerName", ih.team, ih.status,
               ih."statusChangedAt", ih."relatedGameId", ih."relatedGameDate"
        FROM "InjuryStatusHistory" ih
        WHERE ih."relatedGameId" IS NOT NULL
          AND ih."statusChangedAt" IS NOT NULL
        ORDER BY ih."statusChangedAt" DESC
    ''')
    injuries = cur.fetchall()
    print(f"  Found {len(injuries)} injuries with game context")

    for inj in injuries:
        inj_id, league, player, team, status, status_time, game_id, game_date = inj

        if not status_time or not game_id:
            continue

        # Find odds snapshots before and after injury news
        # Before: latest snapshot before injury time
        cur.execute('''
            SELECT "spreadHome", total, "snapshotAt"
            FROM "OddsSnapshot"
            WHERE "gameId" = %s AND "snapshotAt" < %s
            ORDER BY "snapshotAt" DESC
            LIMIT 1
        ''', (game_id, status_time))
        before = cur.fetchone()

        # After: earliest snapshot after injury time
        cur.execute('''
            SELECT "spreadHome", total, "snapshotAt"
            FROM "OddsSnapshot"
            WHERE "gameId" = %s AND "snapshotAt" > %s
            ORDER BY "snapshotAt" ASC
            LIMIT 1
        ''', (game_id, status_time))
        after = cur.fetchone()

        if not before and not after:
            # Try to get from SportsGame opening/closing
            cur.execute('''
                SELECT "openingSpreadHome", "openingTotal", "closingSpreadHome", "closingTotal"
                FROM "SportsGame"
                WHERE id = %s
            ''', (game_id,))
            game_lines = cur.fetchone()
            if game_lines and game_lines[0] is not None and game_lines[2] is not None:
                spread_before = game_lines[0]
                total_before = game_lines[1]
                spread_after = game_lines[2]
                total_after = game_lines[3]
                spread_move = (spread_after - spread_before) if spread_before and spread_after else None
                total_move = (total_after - total_before) if total_before and total_after else None
                minutes_to_move = None
            else:
                continue
        else:
            spread_before = before[0] if before else None
            total_before = before[1] if before else None
            spread_after = after[0] if after else None
            total_after = after[1] if after else None

            spread_move = (spread_after - spread_before) if spread_before and spread_after else None
            total_move = (total_after - total_before) if total_before and total_after else None

            if after and before:
                minutes_to_move = (after[2] - before[2]).total_seconds() / 60
            else:
                minutes_to_move = None

        # Calculate hours before game
        hours_before = None
        if game_date and status_time:
            delta = game_date - status_time
            if delta.total_seconds() > 0:
                hours_before = delta.total_seconds() / 3600

        # Determine if line moved favorably (toward the injury team losing)
        line_moved_favorably = None
        if spread_move is not None:
            # If key player out, spread should move against their team
            # Positive movement = line moved in favor of betting against injured team
            line_moved_favorably = abs(spread_move) >= 0.5

        cur.execute('''
            INSERT INTO "InjuryLineImpact" (
                league, "injuryHistoryId", "playerName", team, status,
                "statusChangedAt", "gameId", "gameDate", "hoursBeforeGame",
                "spreadBefore", "totalBefore", "spreadAfter", "totalAfter",
                "spreadMovement", "totalMovement", "lineMovedFavorably", "minutesToLineMove"
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (league, "injuryHistoryId", "gameId") DO UPDATE SET
                "spreadMovement" = EXCLUDED."spreadMovement",
                "totalMovement" = EXCLUDED."totalMovement",
                "lineMovedFavorably" = EXCLUDED."lineMovedFavorably"
        ''', (
            league, inj_id, player, team, status,
            status_time, game_id, game_date, hours_before,
            spread_before, total_before, spread_after, total_after,
            spread_move, total_move, line_moved_favorably, minutes_to_move
        ))
        total_impacts += 1

        # Update InjuryStatusHistory with line movement
        if spread_move is not None or total_move is not None:
            total_movement = abs(spread_move or 0) + abs(total_move or 0)
            cur.execute('''
                UPDATE "InjuryStatusHistory"
                SET "lineMovedAfter" = %s, "lineMovement" = %s
                WHERE id = %s
            ''', (total_movement > 0.5, total_movement, inj_id))
            updates_to_history += 1

    conn.commit()

    # Summary
    print("\nInjury Line Impact Summary:")
    cur.execute('''
        SELECT status, COUNT(*),
               AVG(ABS("spreadMovement")) as avg_spread_move,
               AVG(ABS("totalMovement")) as avg_total_move
        FROM "InjuryLineImpact"
        WHERE "spreadMovement" IS NOT NULL
        GROUP BY status
        ORDER BY COUNT(*) DESC
    ''')
    for r in cur.fetchall():
        spread_avg = r[2] if r[2] else 0
        total_avg = r[3] if r[3] else 0
        print(f"  {r[0]}: {r[1]} records, Avg spread move: {spread_avg:.2f}, Total move: {total_avg:.2f}")

    print(f"\nBy Timing (Hours Before Game):")
    cur.execute('''
        SELECT
            CASE
                WHEN "hoursBeforeGame" <= 2 THEN 'Late (0-2h)'
                WHEN "hoursBeforeGame" <= 24 THEN 'Game Day (2-24h)'
                WHEN "hoursBeforeGame" <= 48 THEN 'Day Before (24-48h)'
                ELSE 'Early (48h+)'
            END as timing,
            COUNT(*),
            AVG(ABS("spreadMovement")) as avg_move
        FROM "InjuryLineImpact"
        WHERE "hoursBeforeGame" IS NOT NULL AND "spreadMovement" IS NOT NULL
        GROUP BY 1
        ORDER BY 1
    ''')
    for r in cur.fetchall():
        avg = r[2] if r[2] else 0
        print(f"  {r[0]}: {r[1]} records, Avg spread move: {avg:.2f}")

    cur.close()
    conn.close()
    print(f"\nTotal injury-line impacts: {total_impacts}")
    print(f"Updated InjuryStatusHistory records: {updates_to_history}")
    return total_impacts

if __name__ == '__main__':
    compute_injury_line_impact()