#!/usr/bin/env python3
"""
Compute News-Line Move Correlation
Links injury/news events to line movements
Fixes: Q24 "Did this line move without obvious news?"
"""
import psycopg2
from datetime import datetime, 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_news_correlation():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create LineMoveCause table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "LineMoveCause" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT,
            "gameDate" DATE,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            "moveDetectedAt" TIMESTAMP WITH TIME ZONE,
            "market" VARCHAR(30),
            "lineFrom" NUMERIC,
            "lineTo" NUMERIC,
            "lineChange" NUMERIC,
            "moveType" VARCHAR(30),
            -- Correlation fields
            "hasCorrelatedNews" BOOLEAN DEFAULT FALSE,
            "newsType" VARCHAR(50),
            "newsPlayer" VARCHAR(200),
            "newsTeam" VARCHAR(100),
            "newsTimestamp" TIMESTAMP WITH TIME ZONE,
            "minutesBeforeMove" INTEGER,
            "moveCause" VARCHAR(50),
            "moveExplanation" TEXT,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId", "moveDetectedAt", "market")
        )
    ''')
    conn.commit()
    print("LineMoveCause table created/verified")

    # Correlate sharp moves with injury news
    print("\nCorrelating line moves with injury news...")
    cur.execute('''
        WITH moves_with_news AS (
            SELECT
                sm.league,
                sm."gameId",
                sm."gameDate",
                sm."homeTeam",
                sm."awayTeam",
                sm."moveDetectedAt",
                sm.market,
                sm."lineFrom",
                sm."lineTo",
                sm."lineChange",
                sm."moveType",
                -- Find closest injury news within 24 hours before move
                pi.id as injury_id,
                pi."playerName",
                pi.team as injury_team,
                pi.status as injury_status,
                pi."updatedAt" as news_timestamp,
                EXTRACT(EPOCH FROM (sm."moveDetectedAt" - pi."updatedAt")) / 60 as minutes_diff
            FROM "SharpMove" sm
            LEFT JOIN "PlayerInjury" pi ON
                pi.league = sm.league
                AND (pi.team = sm."homeTeam" OR pi.team = sm."awayTeam")
                AND pi."updatedAt" < sm."moveDetectedAt"
                AND pi."updatedAt" > sm."moveDetectedAt" - INTERVAL '24 hours'
        ),
        ranked_news AS (
            SELECT *,
                ROW_NUMBER() OVER (
                    PARTITION BY league, "gameId", "moveDetectedAt", market
                    ORDER BY minutes_diff ASC
                ) as news_rank
            FROM moves_with_news
        )
        INSERT INTO "LineMoveCause"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", "moveDetectedAt",
         market, "lineFrom", "lineTo", "lineChange", "moveType",
         "hasCorrelatedNews", "newsType", "newsPlayer", "newsTeam", "newsTimestamp",
         "minutesBeforeMove", "moveCause", "moveExplanation")
        SELECT
            league,
            "gameId",
            "gameDate",
            "homeTeam",
            "awayTeam",
            "moveDetectedAt",
            market,
            "lineFrom",
            "lineTo",
            "lineChange",
            "moveType",
            -- Correlation
            injury_id IS NOT NULL,
            CASE WHEN injury_id IS NOT NULL THEN 'INJURY' END,
            "playerName",
            injury_team,
            news_timestamp,
            ROUND(minutes_diff)::int,
            CASE
                WHEN injury_id IS NOT NULL AND minutes_diff < 60 THEN 'NEWS_DRIVEN'
                WHEN injury_id IS NOT NULL AND minutes_diff < 360 THEN 'LIKELY_NEWS'
                WHEN "moveType" = 'steam' THEN 'SHARP_ACTION'
                WHEN "moveType" = 'reverse' THEN 'REVERSE_LINE'
                ELSE 'UNEXPLAINED'
            END,
            CASE
                WHEN injury_id IS NOT NULL THEN
                    "playerName" || ' (' || injury_team || ') - ' || injury_status
                WHEN "moveType" = 'steam' THEN 'Sharp money detected'
                WHEN "moveType" = 'reverse' THEN 'Reverse line movement'
                ELSE 'No obvious news - possible sharp action or insider info'
            END
        FROM ranked_news
        WHERE news_rank = 1 OR injury_id IS NULL
        ON CONFLICT (league, "gameId", "moveDetectedAt", "market") DO UPDATE SET
            "hasCorrelatedNews" = EXCLUDED."hasCorrelatedNews",
            "newsType" = EXCLUDED."newsType",
            "newsPlayer" = EXCLUDED."newsPlayer",
            "newsTeam" = EXCLUDED."newsTeam",
            "newsTimestamp" = EXCLUDED."newsTimestamp",
            "minutesBeforeMove" = EXCLUDED."minutesBeforeMove",
            "moveCause" = EXCLUDED."moveCause",
            "moveExplanation" = EXCLUDED."moveExplanation",
            "updatedAt" = NOW()
    ''')
    move_count = cur.rowcount
    conn.commit()
    print(f"  Line moves analyzed: {move_count}")

    # Summary
    cur.execute('''
        SELECT "moveCause", COUNT(*), ROUND(AVG(ABS("lineChange"))::numeric, 2)
        FROM "LineMoveCause"
        GROUP BY "moveCause"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nLine Move Causes:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} moves (avg {row[2]} pts)")

    # Show examples
    cur.execute('''
        SELECT league, "homeTeam", "awayTeam", "lineChange", "moveCause", "moveExplanation"
        FROM "LineMoveCause"
        WHERE "hasCorrelatedNews" = true
        ORDER BY "moveDetectedAt" DESC
        LIMIT 5
    ''')
    results = cur.fetchall()
    if results:
        print("\nRecent News-Driven Moves:")
        for row in results:
            print(f"  {row[0].upper()}: {row[2]} @ {row[1]} ({row[3]:+.1f}) - {row[4]}")
            print(f"    → {row[5][:60]}...")

    cur.close()
    conn.close()
    print(f"\n✅ News-line correlation complete: {move_count} moves analyzed")

if __name__ == '__main__':
    compute_news_correlation()