#!/usr/bin/env python3
"""
Compute Sharp Money Moves from OddsSnapshot
Detects steam moves and sharp action based on line movement velocity.
Enables Q18: Sharp steam moves vs CLV/EV analysis
"""
import psycopg2
from datetime import datetime, timezone, timedelta
from collections import defaultdict

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

    print("=" * 60)
    print("COMPUTE SHARP MONEY MOVES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create SharpMove table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "SharpMove" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" BIGINT,
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            market VARCHAR(50),
            "moveType" VARCHAR(50),
            "lineFrom" FLOAT,
            "lineTo" FLOAT,
            "lineChange" FLOAT,
            "oddsFrom" INT,
            "oddsTo" INT,
            "moveDetectedAt" TIMESTAMP,
            "minutesBetweenSnapshots" FLOAT,
            "moveVelocity" FLOAT,
            "isReverseLine" BOOLEAN DEFAULT false,
            "isSteamMove" BOOLEAN DEFAULT false,
            "bookmaker" VARCHAR(100),
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId", market, "moveDetectedAt")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "SharpMove_game" ON "SharpMove" ("gameId")')
    cur.execute('CREATE INDEX IF NOT EXISTS "SharpMove_steam" ON "SharpMove" ("isSteamMove")')
    cur.execute('CREATE INDEX IF NOT EXISTS "SharpMove_date" ON "SharpMove" ("gameDate" DESC)')

    total_moves = 0

    # Get games with multiple snapshots
    print("\nAnalyzing line movement from OddsSnapshot...")
    cur.execute('''
        SELECT "gameId", league, COUNT(*) as snapshots
        FROM "OddsSnapshot"
        WHERE "gameId" IS NOT NULL
        GROUP BY "gameId", league
        HAVING COUNT(*) >= 2
    ''')
    games_with_snapshots = cur.fetchall()
    print(f"  Found {len(games_with_snapshots)} games with multiple snapshots")

    for game_id, league, _ in games_with_snapshots:
        # Get snapshots for this game ordered by time
        cur.execute('''
            SELECT id, "snapshotAt", "spreadHome", "spreadAway", "total",
                   "moneylineHome", "moneylineAway", bookmaker
            FROM "OddsSnapshot"
            WHERE "gameId" = %s
            ORDER BY "snapshotAt"
        ''', (game_id,))
        snapshots = cur.fetchall()

        if len(snapshots) < 2:
            continue

        # Get game info
        cur.execute('''
            SELECT "gameDate", "homeTeam", "awayTeam"
            FROM "SportsGame"
            WHERE id = %s
        ''', (game_id,))
        game_info = cur.fetchone()
        if not game_info:
            continue
        game_date, home_team, away_team = game_info

        # Compare consecutive snapshots for sharp moves
        for i in range(1, len(snapshots)):
            prev = snapshots[i-1]
            curr = snapshots[i]

            prev_time = prev[1]
            curr_time = curr[1]

            if not prev_time or not curr_time:
                continue

            minutes_diff = (curr_time - prev_time).total_seconds() / 60
            if minutes_diff <= 0:
                continue

            # Check spread movement
            prev_spread = prev[2]
            curr_spread = curr[2]
            if prev_spread is not None and curr_spread is not None:
                spread_change = abs(curr_spread - prev_spread)
                # Steam move: >= 0.5 point move in < 30 minutes
                if spread_change >= 0.5 and minutes_diff <= 30:
                    velocity = spread_change / (minutes_diff / 60)  # points per hour
                    is_steam = spread_change >= 1.0 or (spread_change >= 0.5 and minutes_diff <= 10)

                    cur.execute('''
                        INSERT INTO "SharpMove" (
                            league, "gameId", "gameDate", "homeTeam", "awayTeam",
                            market, "moveType", "lineFrom", "lineTo", "lineChange",
                            "moveDetectedAt", "minutesBetweenSnapshots", "moveVelocity",
                            "isSteamMove", bookmaker
                        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, "gameId", market, "moveDetectedAt") DO NOTHING
                    ''', (
                        league, game_id, game_date, home_team, away_team,
                        'spread', 'spread_move', prev_spread, curr_spread, spread_change,
                        curr_time, minutes_diff, velocity,
                        is_steam, curr[7]
                    ))
                    total_moves += 1

            # Check total movement
            prev_total = prev[4]
            curr_total = curr[4]
            if prev_total is not None and curr_total is not None:
                total_change = abs(curr_total - prev_total)
                if total_change >= 0.5 and minutes_diff <= 30:
                    velocity = total_change / (minutes_diff / 60)
                    is_steam = total_change >= 1.0 or (total_change >= 0.5 and minutes_diff <= 10)

                    cur.execute('''
                        INSERT INTO "SharpMove" (
                            league, "gameId", "gameDate", "homeTeam", "awayTeam",
                            market, "moveType", "lineFrom", "lineTo", "lineChange",
                            "moveDetectedAt", "minutesBetweenSnapshots", "moveVelocity",
                            "isSteamMove", bookmaker
                        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, "gameId", market, "moveDetectedAt") DO NOTHING
                    ''', (
                        league, game_id, game_date, home_team, away_team,
                        'total', 'total_move', prev_total, curr_total, total_change,
                        curr_time, minutes_diff, velocity,
                        is_steam, curr[7]
                    ))
                    total_moves += 1

    conn.commit()

    # Summary
    print("\nSharp Move Summary:")
    cur.execute('''
        SELECT market, "isSteamMove", COUNT(*)
        FROM "SharpMove"
        GROUP BY market, "isSteamMove"
        ORDER BY market, "isSteamMove"
    ''')
    for r in cur.fetchall():
        move_type = "Steam" if r[1] else "Regular"
        print(f"  {r[0]} {move_type}: {r[2]} moves")

    print("\nBy League:")
    cur.execute('''
        SELECT league, COUNT(*), SUM(CASE WHEN "isSteamMove" THEN 1 ELSE 0 END) as steam
        FROM "SharpMove"
        GROUP BY league
        ORDER BY COUNT(*) DESC
    ''')
    for r in cur.fetchall():
        print(f"  {r[0].upper()}: {r[1]} moves ({r[2]} steam)")

    cur.close()
    conn.close()
    print(f"\nTotal sharp moves detected: {total_moves}")
    return total_moves

if __name__ == '__main__':
    compute_sharp_moves()