#!/usr/bin/env python3
"""
Q2: Compute line timing analysis - overnight vs same-day line moves
Analyzes whether overnight lines or same-day lines offer better CLV
Uses fetchedAt timestamps from GameOdds
"""
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_line_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 LineTimingAnalysis table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "LineTimingAnalysis" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT NOT NULL,
            "gameDate" DATE NOT NULL,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            bookmaker VARCHAR(100),
            market VARCHAR(50),
            "timingBucket" VARCHAR(20) NOT NULL,
            "hoursBeforeGame" NUMERIC,
            "lineValue" NUMERIC,
            "homeOdds" INTEGER,
            "awayOdds" INTEGER,
            "openingLine" NUMERIC,
            "closingLine" NUMERIC,
            "lineMove" NUMERIC,
            "clvDirection" VARCHAR(10),
            "fetchedAt" TIMESTAMP WITH TIME ZONE,
            "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId", bookmaker, market, "timingBucket")
        )
    ''')

    # Create LineTimingSummary table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "LineTimingSummary" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            market VARCHAR(50) NOT NULL,
            "timingBucket" VARCHAR(20) NOT NULL,
            "sampleSize" INTEGER,
            "avgLineMove" NUMERIC,
            "avgClvGained" NUMERIC,
            "clvWinRate" NUMERIC,
            "stdDev" NUMERIC,
            "recommendation" TEXT,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, market, "timingBucket")
        )
    ''')

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

    # Analyze line timing from GameOdds
    # Bucket: overnight (18-24h before), morning (6-18h), pregame (2-6h), close (0-2h)
    cur.execute('''
        WITH game_times AS (
            SELECT DISTINCT league, "gameId", "gameDate",
                   "homeTeam", "awayTeam"
            FROM "GameOdds"
            WHERE "gameDate" IS NOT NULL
        ),
        odds_with_timing AS (
            SELECT
                go.league,
                go."gameId",
                go."gameDate",
                go."homeTeam",
                go."awayTeam",
                go.bookmaker,
                go.market,
                go."lineValue",
                go."homeOdds",
                go."awayOdds",
                go."openingLineValue",
                go."fetchedAt",
                EXTRACT(EPOCH FROM (go."gameDate"::timestamp - go."fetchedAt")) / 3600 as hours_before,
                CASE
                    WHEN EXTRACT(EPOCH FROM (go."gameDate"::timestamp - go."fetchedAt")) / 3600 > 18 THEN 'overnight'
                    WHEN EXTRACT(EPOCH FROM (go."gameDate"::timestamp - go."fetchedAt")) / 3600 > 6 THEN 'morning'
                    WHEN EXTRACT(EPOCH FROM (go."gameDate"::timestamp - go."fetchedAt")) / 3600 > 2 THEN 'pregame'
                    ELSE 'close'
                END as timing_bucket
            FROM "GameOdds" go
            WHERE go."fetchedAt" IS NOT NULL
              AND go."gameDate" IS NOT NULL
              AND go."lineValue" IS NOT NULL
        )
        INSERT INTO "LineTimingAnalysis"
        (league, "gameId", "gameDate", "homeTeam", "awayTeam", bookmaker, market,
         "timingBucket", "hoursBeforeGame", "lineValue", "homeOdds", "awayOdds",
         "openingLine", "fetchedAt")
        SELECT DISTINCT ON (league, "gameId", bookmaker, market, timing_bucket)
            league,
            "gameId",
            "gameDate",
            "homeTeam",
            "awayTeam",
            bookmaker,
            market,
            timing_bucket,
            ROUND(hours_before::numeric, 1),
            "lineValue",
            "homeOdds",
            "awayOdds",
            "openingLineValue",
            "fetchedAt"
        FROM odds_with_timing
        WHERE hours_before > 0 AND hours_before < 48
        ORDER BY league, "gameId", bookmaker, market, timing_bucket, "fetchedAt" DESC
        ON CONFLICT (league, "gameId", bookmaker, market, "timingBucket") DO UPDATE SET
            "lineValue" = EXCLUDED."lineValue",
            "homeOdds" = EXCLUDED."homeOdds",
            "awayOdds" = EXCLUDED."awayOdds",
            "hoursBeforeGame" = EXCLUDED."hoursBeforeGame",
            "fetchedAt" = EXCLUDED."fetchedAt"
    ''')

    timing_records = cur.rowcount
    conn.commit()
    print(f"LineTimingAnalysis populated: {timing_records} records")

    # Compute closing lines for each game
    cur.execute('''
        UPDATE "LineTimingAnalysis" lta
        SET "closingLine" = closing.line_value
        FROM (
            SELECT DISTINCT ON (league, "gameId", bookmaker, market)
                league, "gameId", bookmaker, market, "lineValue" as line_value
            FROM "LineTimingAnalysis"
            WHERE "timingBucket" = 'close'
            ORDER BY league, "gameId", bookmaker, market
        ) closing
        WHERE lta.league = closing.league
          AND lta."gameId" = closing."gameId"
          AND lta.bookmaker = closing.bookmaker
          AND lta.market = closing.market
    ''')
    conn.commit()

    # Calculate line moves
    cur.execute('''
        UPDATE "LineTimingAnalysis"
        SET "lineMove" = "closingLine" - "lineValue"
        WHERE "closingLine" IS NOT NULL AND "lineValue" IS NOT NULL
    ''')
    conn.commit()

    # Compute CLV direction (positive = line moved in your favor if you bet early)
    cur.execute('''
        UPDATE "LineTimingAnalysis"
        SET "clvDirection" = CASE
            WHEN "lineMove" > 0.5 THEN 'positive'
            WHEN "lineMove" < -0.5 THEN 'negative'
            ELSE 'neutral'
        END
        WHERE "lineMove" IS NOT NULL
    ''')
    conn.commit()

    # Generate summary by timing bucket
    cur.execute('''
        INSERT INTO "LineTimingSummary"
        (league, market, "timingBucket", "sampleSize", "avgLineMove", "avgClvGained",
         "clvWinRate", "stdDev", recommendation)
        SELECT
            league,
            market,
            "timingBucket",
            COUNT(*) as sample_size,
            ROUND(AVG("lineMove")::numeric, 2) as avg_line_move,
            ROUND(AVG(CASE WHEN "lineMove" > 0 THEN "lineMove" ELSE 0 END)::numeric, 2) as avg_clv_gained,
            ROUND(100.0 * COUNT(CASE WHEN "clvDirection" = 'positive' THEN 1 END) / NULLIF(COUNT(*), 0), 1) as clv_win_rate,
            ROUND(STDDEV("lineMove")::numeric, 2) as std_dev,
            CASE
                WHEN AVG("lineMove") > 0.3 THEN 'BET_EARLY: Lines move against early bettors'
                WHEN AVG("lineMove") < -0.3 THEN 'WAIT: Lines move in favor of late bettors'
                ELSE 'NEUTRAL: No significant timing edge'
            END as recommendation
        FROM "LineTimingAnalysis"
        WHERE "lineMove" IS NOT NULL
        GROUP BY league, market, "timingBucket"
        HAVING COUNT(*) >= 10
        ON CONFLICT (league, market, "timingBucket") DO UPDATE SET
            "sampleSize" = EXCLUDED."sampleSize",
            "avgLineMove" = EXCLUDED."avgLineMove",
            "avgClvGained" = EXCLUDED."avgClvGained",
            "clvWinRate" = EXCLUDED."clvWinRate",
            "stdDev" = EXCLUDED."stdDev",
            recommendation = EXCLUDED.recommendation,
            "updatedAt" = NOW()
    ''')

    summary_records = cur.rowcount
    conn.commit()
    print(f"LineTimingSummary populated: {summary_records} records")

    # Print summary
    cur.execute('''
        SELECT "timingBucket", COUNT(*), ROUND(AVG("avgLineMove")::numeric, 2), ROUND(AVG("clvWinRate")::numeric, 1)
        FROM "LineTimingSummary"
        GROUP BY "timingBucket"
        ORDER BY "timingBucket"
    ''')
    print("\nTiming Summary:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} markets, avg move: {row[2]}, CLV rate: {row[3]}%")

    cur.close()
    conn.close()

    print(f"\n✅ Line timing analysis complete: {timing_records} records, {summary_records} summaries")
    return {'timing_records': timing_records, 'summary_records': summary_records}

if __name__ == '__main__':
    compute_line_timing()