#!/usr/bin/env python3
"""
Compute CLV (Closing Line Value) to Profit Correlation
Creates CLVAnalysis table linking opening odds, closing odds, and actual results.
Answers Q19: "Does CLV correlate with actual profit?"
"""
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 american_to_decimal(odds):
    """Convert American odds to decimal."""
    if odds is None:
        return None
    if odds > 0:
        return 1 + (odds / 100)
    else:
        return 1 + (100 / abs(odds))

def implied_prob(decimal_odds):
    """Convert decimal odds to implied probability."""
    if decimal_odds is None or decimal_odds <= 1:
        return None
    return 1 / decimal_odds

def compute_clv_profit():
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("COMPUTE CLV-TO-PROFIT ANALYSIS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create CLV analysis table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "CLVAnalysis" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" BIGINT,
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            market VARCHAR(20),  -- 'spread', 'total', 'ml'
            side VARCHAR(20),    -- 'home', 'away', 'over', 'under'
            -- Opening line
            "openingLine" FLOAT,
            "openingOdds" INT,
            "openingImpliedProb" FLOAT,
            -- Closing line
            "closingLine" FLOAT,
            "closingOdds" INT,
            "closingImpliedProb" FLOAT,
            -- CLV metrics
            "clvPercent" FLOAT,  -- (closing_prob - opening_prob) / opening_prob * 100
            "lineMoved" FLOAT,   -- closing_line - opening_line
            "beatClose" BOOLEAN, -- Did opening beat closing?
            -- Result
            "actualResult" VARCHAR(20),  -- 'win', 'loss', 'push'
            "profit" FLOAT,      -- Profit on $100 bet at opening odds
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId", market, side)
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "CLVAnalysis_clv" ON "CLVAnalysis" ("clvPercent")')
    cur.execute('CREATE INDEX IF NOT EXISTS "CLVAnalysis_league" ON "CLVAnalysis" (league, "gameDate")')

    total_records = 0

    # Get games with opening and closing lines from SportsGame
    cur.execute('''
        SELECT id, league, "gameDate", "homeTeam", "awayTeam",
               "closingSpreadHome", "moneylineHome", "moneylineAway",
               "closingTotal",
               "openingSpreadHome", "openingTotal",
               "homeScore", "awayScore"
        FROM "SportsGame"
        WHERE season >= 2024
          AND "homeScore" IS NOT NULL
          AND ("openingSpreadHome" IS NOT NULL OR "openingTotal" IS NOT NULL)
        ORDER BY "gameDate"
    ''')

    games = cur.fetchall()
    print(f"Found {len(games)} games with opening/closing lines")

    for g in games:
        (game_id, league, gdate, home, away, spread_close, ml_home, ml_away,
         total_close, spread_open, total_open, hscore, ascore) = g

        if spread_open and spread_close:
            # Spread CLV - Home side
            line_moved = spread_close - spread_open
            # Positive line move = line moved against home = got better number
            beat_close = line_moved < 0  # Got home at better number

            # Determine result
            margin = hscore - ascore
            actual_margin_vs_spread = margin + spread_open
            if actual_margin_vs_spread > 0:
                result = 'win'
                profit = 91  # Assume standard -110 odds
            elif actual_margin_vs_spread < 0:
                result = 'loss'
                profit = -100
            else:
                result = 'push'
                profit = 0

            # Calculate CLV as line movement value
            clv_pct = -line_moved * 2  # ~2% per half point of CLV

            cur.execute('''
                INSERT INTO "CLVAnalysis" (
                    league, "gameId", "gameDate", "homeTeam", "awayTeam",
                    market, side, "openingLine", "closingLine", "lineMoved",
                    "beatClose", "actualResult", profit, "clvPercent"
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (league, "gameId", market, side) DO UPDATE SET
                    "closingLine" = EXCLUDED."closingLine",
                    "lineMoved" = EXCLUDED."lineMoved",
                    "actualResult" = EXCLUDED."actualResult",
                    profit = EXCLUDED.profit,
                    "updatedAt" = NOW()
            ''', (league, game_id, gdate, home, away, 'spread', 'home',
                  spread_open, spread_close, line_moved, beat_close, result, profit, clv_pct))
            total_records += 1

        if total_open and total_close:
            # Total CLV - Over side
            line_moved = total_close - total_open
            beat_close = line_moved > 0  # Got over at lower number

            total_score = hscore + ascore
            if total_score > total_open:
                result = 'win'
                profit = 91  # Assume standard -110 odds
            elif total_score < total_open:
                result = 'loss'
                profit = -100
            else:
                result = 'push'
                profit = 0

            clv_pct = line_moved * 2

            cur.execute('''
                INSERT INTO "CLVAnalysis" (
                    league, "gameId", "gameDate", "homeTeam", "awayTeam",
                    market, side, "openingLine", "closingLine", "lineMoved",
                    "beatClose", "actualResult", profit, "clvPercent"
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (league, "gameId", market, side) DO UPDATE SET
                    "closingLine" = EXCLUDED."closingLine",
                    "lineMoved" = EXCLUDED."lineMoved",
                    "actualResult" = EXCLUDED."actualResult",
                    profit = EXCLUDED.profit,
                    "updatedAt" = NOW()
            ''', (league, game_id, gdate, home, away, 'total', 'over',
                  total_open, total_close, line_moved, beat_close, result, profit, clv_pct))
            total_records += 1

    conn.commit()

    # Generate summary stats
    print("\nCLV-to-Profit Summary:")
    cur.execute('''
        SELECT
            CASE WHEN "beatClose" THEN 'Beat Close' ELSE 'Did Not Beat' END as clv_status,
            COUNT(*) as bets,
            SUM(profit) as total_profit,
            AVG(profit) as avg_profit,
            SUM(CASE WHEN "actualResult" = 'win' THEN 1 ELSE 0 END) as wins
        FROM "CLVAnalysis"
        GROUP BY "beatClose"
    ''')
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} bets, ${row[2]:.0f} profit, {row[3]:.1f} avg, {row[4]} wins")

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

if __name__ == '__main__':
    compute_clv_profit()