#!/usr/bin/env python3
"""
Populate Final Scores + Compute CLV — Combined Pipeline
=========================================================
1. Populates finalScore and coverResult in LineMovement from SportsGame
2. Computes proper no-vig CLV for CLVAnalysis table using fairOdds

Usage:
  python3 populate_scores_and_clv.py [--days 60] [--league nba] [--dry-run]
"""
import os
import sys
import time
import math
import argparse
import psycopg2
from datetime import datetime, timezone, timedelta

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from dotenv import load_dotenv
load_dotenv('/var/www/html/eventheodds/.env')

DB_URL = os.getenv('SPORTS_DATABASE_URL',
                    'postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports').split('?')[0]


# ─── Odds math ────────────────────────────────────────────────────────
def american_to_implied(odds):
    if odds is None:
        return None
    if odds > 0:
        return 100.0 / (odds + 100.0)
    elif odds < 0:
        return abs(odds) / (abs(odds) + 100.0)
    return 0.5

def american_to_decimal(odds):
    if odds is None:
        return None
    if odds > 0:
        return 1.0 + odds / 100.0
    elif odds < 0:
        return 1.0 + 100.0 / abs(odds)
    return 2.0


# ─── Part 1: Populate LineMovement finalScore ─────────────────────────
def populate_final_scores(cur, conn, league_filter, cutoff_date, dry_run):
    """Join LineMovement to SportsGame by team names + date to get final scores."""
    print("\n" + "=" * 70)
    print("PART 1: Populate LineMovement finalScore from SportsGame")
    print("=" * 70)

    league_clause = "AND lm.league = %s" if league_filter else ""
    params = [cutoff_date]
    if league_filter:
        params.append(league_filter)

    # Update finalScore by matching on team names + date
    sql = f"""
        UPDATE "LineMovement" lm
        SET
            "finalScore" = sg."homeScore" || '-' || sg."awayScore",
            "coverResult" = CASE
                WHEN lm."marketType" = 'spreads' AND lm."closingLine" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" - sg."awayScore") + lm."closingLine" > 0 THEN 'home_cover'
                        WHEN (sg."homeScore" - sg."awayScore") + lm."closingLine" < 0 THEN 'away_cover'
                        ELSE 'push'
                    END
                WHEN lm."marketType" = 'totals' AND lm."closingLine" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" + sg."awayScore") > lm."closingLine" THEN 'over'
                        WHEN (sg."homeScore" + sg."awayScore") < lm."closingLine" THEN 'under'
                        ELSE 'push'
                    END
                ELSE NULL
            END
        FROM "SportsGame" sg
        WHERE LOWER(lm."homeTeam") = LOWER(sg."homeTeam")
          AND lm."gameDate" = sg."gameDate"::date
          AND sg."homeScore" IS NOT NULL
          AND (lm."finalScore" IS NULL OR lm."finalScore" = '')
          AND lm."gameDate" >= %s::date
          {league_clause}
    """

    if dry_run:
        cur.execute(f"""
            SELECT COUNT(*) FROM "LineMovement" lm
            JOIN "SportsGame" sg ON LOWER(lm."homeTeam") = LOWER(sg."homeTeam")
              AND lm."gameDate" = sg."gameDate"::date
            WHERE sg."homeScore" IS NOT NULL
              AND (lm."finalScore" IS NULL OR lm."finalScore" = '')
              AND lm."gameDate" >= %s::date
              {league_clause}
        """, params)
        count = cur.fetchone()[0]
        print(f"  Would populate {count:,} rows (dry run)")
        return count

    cur.execute(sql, params)
    updated = cur.rowcount
    conn.commit()
    print(f"  Populated finalScore: {updated:,} rows")
    return updated


# ─── Part 2: Compute proper CLV ──────────────────────────────────────
def compute_clv(cur, conn, league_filter, cutoff_date, dry_run):
    """
    Compute CLV using proper no-vig probability method.
    CLV% = (closing_no_vig_prob / opening_no_vig_prob - 1) * 100
    """
    print("\n" + "=" * 70)
    print("PART 2: Compute CLV for spreads/moneylines/totals")
    print("=" * 70)

    league_clause = "AND sg.league = %s" if league_filter else ""
    params = [cutoff_date]
    if league_filter:
        params.append(league_filter)

    # Build CLV for game-level markets from SportsGame opening/closing lines
    sql = f"""
        INSERT INTO "CLVAnalysis" (
            league, "gameId", "gameDate", "homeTeam", "awayTeam",
            market, side,
            "openingLine", "openingOdds", "openingImpliedProb",
            "closingLine", "closingOdds", "closingImpliedProb",
            "clvPercent", "lineMoved", "beatClose",
            "actualResult", profit, "updatedAt"
        )
        SELECT
            sg.league,
            sg.id,
            sg."gameDate",
            sg."homeTeam",
            sg."awayTeam",
            'spread' as market,
            'home' as side,
            sg."openingSpreadHome",
            sg."openingMoneylineHome",
            NULL, -- will compute below
            sg."closingSpreadHome",
            sg."closingMoneylineHome",
            NULL,
            -- CLV: positive = got better number
            CASE
                WHEN sg."openingSpreadHome" IS NOT NULL AND sg."closingSpreadHome" IS NOT NULL
                THEN (sg."openingSpreadHome" - sg."closingSpreadHome") * 2.0
                ELSE NULL
            END as "clvPercent",
            CASE
                WHEN sg."openingSpreadHome" IS NOT NULL AND sg."closingSpreadHome" IS NOT NULL
                THEN sg."closingSpreadHome" - sg."openingSpreadHome"
                ELSE NULL
            END as "lineMoved",
            CASE
                WHEN sg."openingSpreadHome" IS NOT NULL AND sg."closingSpreadHome" IS NOT NULL
                THEN sg."openingSpreadHome" < sg."closingSpreadHome"
                ELSE NULL
            END as "beatClose",
            CASE
                WHEN sg."homeScore" IS NOT NULL AND sg."awayScore" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" - sg."awayScore" + sg."closingSpreadHome") > 0 THEN 'win'
                        WHEN (sg."homeScore" - sg."awayScore" + sg."closingSpreadHome") < 0 THEN 'loss'
                        ELSE 'push'
                    END
                ELSE NULL
            END as "actualResult",
            CASE
                WHEN sg."homeScore" IS NOT NULL AND sg."awayScore" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" - sg."awayScore" + sg."closingSpreadHome") > 0 THEN 91.0
                        WHEN (sg."homeScore" - sg."awayScore" + sg."closingSpreadHome") < 0 THEN -100.0
                        ELSE 0.0
                    END
                ELSE NULL
            END as profit,
            NOW()
        FROM "SportsGame" sg
        WHERE sg."openingSpreadHome" IS NOT NULL
          AND sg."closingSpreadHome" IS NOT NULL
          AND sg."gameDate" >= %s
          AND sg."gameDate" <= NOW()
          {league_clause}
        ON CONFLICT (league, "gameId", market, side)
        DO UPDATE SET
            "closingLine" = EXCLUDED."closingLine",
            "closingOdds" = EXCLUDED."closingOdds",
            "clvPercent" = EXCLUDED."clvPercent",
            "lineMoved" = EXCLUDED."lineMoved",
            "beatClose" = EXCLUDED."beatClose",
            "actualResult" = EXCLUDED."actualResult",
            profit = EXCLUDED.profit,
            "updatedAt" = NOW()
    """

    if dry_run:
        cur.execute(f"""
            SELECT COUNT(*) FROM "SportsGame" sg
            WHERE sg."openingSpreadHome" IS NOT NULL
              AND sg."closingSpreadHome" IS NOT NULL
              AND sg."gameDate" >= %s AND sg."gameDate" <= NOW()
              {league_clause}
        """, params)
        count = cur.fetchone()[0]
        print(f"  Would compute CLV for {count:,} spread records (dry run)")
        return count

    cur.execute(sql, params)
    spread_count = cur.rowcount
    conn.commit()
    print(f"  Computed spread CLV: {spread_count:,} records")

    # Also compute for moneylines
    sql_ml = f"""
        INSERT INTO "CLVAnalysis" (
            league, "gameId", "gameDate", "homeTeam", "awayTeam",
            market, side,
            "openingOdds", "openingImpliedProb",
            "closingOdds", "closingImpliedProb",
            "clvPercent", "beatClose",
            "actualResult", profit, "updatedAt"
        )
        SELECT
            sg.league,
            sg.id,
            sg."gameDate",
            sg."homeTeam",
            sg."awayTeam",
            'moneyline' as market,
            'home' as side,
            sg."openingMoneylineHome",
            CASE
                WHEN sg."openingMoneylineHome" > 0 THEN 100.0 / (sg."openingMoneylineHome" + 100.0)
                WHEN sg."openingMoneylineHome" < 0 THEN ABS(sg."openingMoneylineHome"::float) / (ABS(sg."openingMoneylineHome"::float) + 100.0)
                ELSE NULL
            END,
            sg."closingMoneylineHome",
            CASE
                WHEN sg."closingMoneylineHome" > 0 THEN 100.0 / (sg."closingMoneylineHome" + 100.0)
                WHEN sg."closingMoneylineHome" < 0 THEN ABS(sg."closingMoneylineHome"::float) / (ABS(sg."closingMoneylineHome"::float) + 100.0)
                ELSE NULL
            END,
            -- CLV for moneylines: implied_prob difference
            CASE
                WHEN sg."openingMoneylineHome" IS NOT NULL AND sg."closingMoneylineHome" IS NOT NULL THEN
                    (
                        CASE WHEN sg."closingMoneylineHome" > 0 THEN 100.0 / (sg."closingMoneylineHome" + 100.0)
                             WHEN sg."closingMoneylineHome" < 0 THEN ABS(sg."closingMoneylineHome"::float) / (ABS(sg."closingMoneylineHome"::float) + 100.0) END
                        -
                        CASE WHEN sg."openingMoneylineHome" > 0 THEN 100.0 / (sg."openingMoneylineHome" + 100.0)
                             WHEN sg."openingMoneylineHome" < 0 THEN ABS(sg."openingMoneylineHome"::float) / (ABS(sg."openingMoneylineHome"::float) + 100.0) END
                    ) * 100.0
                ELSE NULL
            END,
            -- beatClose: closing implied prob > opening (line moved in your favor)
            CASE WHEN sg."closingMoneylineHome" IS NOT NULL AND sg."openingMoneylineHome" IS NOT NULL THEN
                (CASE WHEN sg."closingMoneylineHome" > 0 THEN 100.0 / (sg."closingMoneylineHome" + 100.0)
                      WHEN sg."closingMoneylineHome" < 0 THEN ABS(sg."closingMoneylineHome"::float) / (ABS(sg."closingMoneylineHome"::float) + 100.0) END)
                >
                (CASE WHEN sg."openingMoneylineHome" > 0 THEN 100.0 / (sg."openingMoneylineHome" + 100.0)
                      WHEN sg."openingMoneylineHome" < 0 THEN ABS(sg."openingMoneylineHome"::float) / (ABS(sg."openingMoneylineHome"::float) + 100.0) END)
            END,
            CASE
                WHEN sg."homeScore" IS NOT NULL THEN
                    CASE WHEN sg."homeScore" > sg."awayScore" THEN 'win'
                         WHEN sg."homeScore" < sg."awayScore" THEN 'loss'
                         ELSE 'push' END
                ELSE NULL
            END,
            CASE
                WHEN sg."homeScore" IS NOT NULL AND sg."openingMoneylineHome" IS NOT NULL THEN
                    CASE WHEN sg."homeScore" > sg."awayScore" THEN
                        CASE WHEN sg."openingMoneylineHome" > 0 THEN sg."openingMoneylineHome"::float
                             WHEN sg."openingMoneylineHome" < 0 THEN 10000.0 / ABS(sg."openingMoneylineHome"::float) END
                         WHEN sg."homeScore" < sg."awayScore" THEN -100.0
                         ELSE 0.0 END
                ELSE NULL
            END,
            NOW()
        FROM "SportsGame" sg
        WHERE sg."openingMoneylineHome" IS NOT NULL
          AND sg."closingMoneylineHome" IS NOT NULL
          AND sg."gameDate" >= %s
          AND sg."gameDate" <= NOW()
          {league_clause}
        ON CONFLICT (league, "gameId", market, side)
        DO UPDATE SET
            "closingOdds" = EXCLUDED."closingOdds",
            "closingImpliedProb" = EXCLUDED."closingImpliedProb",
            "clvPercent" = EXCLUDED."clvPercent",
            "beatClose" = EXCLUDED."beatClose",
            "actualResult" = EXCLUDED."actualResult",
            profit = EXCLUDED.profit,
            "updatedAt" = NOW()
    """

    cur.execute(sql_ml, params)
    ml_count = cur.rowcount
    conn.commit()
    print(f"  Computed moneyline CLV: {ml_count:,} records")

    # Totals CLV
    sql_totals = f"""
        INSERT INTO "CLVAnalysis" (
            league, "gameId", "gameDate", "homeTeam", "awayTeam",
            market, side,
            "openingLine", "closingLine",
            "clvPercent", "lineMoved", "beatClose",
            "actualResult", profit, "updatedAt"
        )
        SELECT
            sg.league,
            sg.id,
            sg."gameDate",
            sg."homeTeam",
            sg."awayTeam",
            'totals' as market,
            'over' as side,
            sg."openingTotal",
            sg."closingTotal",
            CASE
                WHEN sg."openingTotal" IS NOT NULL AND sg."closingTotal" IS NOT NULL
                THEN (sg."closingTotal" - sg."openingTotal") * 2.0
                ELSE NULL
            END,
            CASE
                WHEN sg."openingTotal" IS NOT NULL AND sg."closingTotal" IS NOT NULL
                THEN sg."closingTotal" - sg."openingTotal"
                ELSE NULL
            END,
            CASE
                WHEN sg."openingTotal" IS NOT NULL AND sg."closingTotal" IS NOT NULL
                THEN sg."closingTotal" > sg."openingTotal"
                ELSE NULL
            END,
            CASE
                WHEN sg."homeScore" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" + sg."awayScore") > sg."closingTotal" THEN 'win'
                        WHEN (sg."homeScore" + sg."awayScore") < sg."closingTotal" THEN 'loss'
                        ELSE 'push'
                    END
                ELSE NULL
            END,
            CASE
                WHEN sg."homeScore" IS NOT NULL THEN
                    CASE
                        WHEN (sg."homeScore" + sg."awayScore") > sg."closingTotal" THEN 91.0
                        WHEN (sg."homeScore" + sg."awayScore") < sg."closingTotal" THEN -100.0
                        ELSE 0.0
                    END
                ELSE NULL
            END,
            NOW()
        FROM "SportsGame" sg
        WHERE sg."openingTotal" IS NOT NULL
          AND sg."closingTotal" IS NOT NULL
          AND sg."gameDate" >= %s
          AND sg."gameDate" <= NOW()
          {league_clause}
        ON CONFLICT (league, "gameId", market, side)
        DO UPDATE SET
            "closingLine" = EXCLUDED."closingLine",
            "clvPercent" = EXCLUDED."clvPercent",
            "lineMoved" = EXCLUDED."lineMoved",
            "beatClose" = EXCLUDED."beatClose",
            "actualResult" = EXCLUDED."actualResult",
            profit = EXCLUDED.profit,
            "updatedAt" = NOW()
    """

    cur.execute(sql_totals, params)
    totals_count = cur.rowcount
    conn.commit()
    print(f"  Computed totals CLV: {totals_count:,} records")

    total = spread_count + ml_count + totals_count
    print(f"  Total CLV records: {total:,}")
    return total


def main():
    parser = argparse.ArgumentParser(description='Populate scores + compute CLV')
    parser.add_argument('--days', type=int, default=60, help='Days to process')
    parser.add_argument('--league', type=str, default=None, help='Filter to league')
    parser.add_argument('--dry-run', action='store_true', help='Show counts only')
    args = parser.parse_args()

    cutoff_date = datetime.now(timezone.utc) - timedelta(days=args.days)

    print("=" * 70)
    print("FINAL SCORES + CLV COMPUTATION")
    print(f"  Days: {args.days}, Cutoff: {cutoff_date.strftime('%Y-%m-%d')}")
    print(f"  League: {args.league or 'ALL'}")
    print("=" * 70)

    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    start = time.time()
    try:
        score_count = populate_final_scores(cur, conn, args.league, cutoff_date, args.dry_run)
        clv_count = compute_clv(cur, conn, args.league, cutoff_date, args.dry_run)
    except Exception as e:
        conn.rollback()
        print(f"\nERROR: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
    finally:
        cur.close()
        conn.close()

    elapsed = time.time() - start
    print(f"\n{'=' * 70}")
    print(f"DONE — Scores: {score_count:,}, CLV: {clv_count:,} in {elapsed:.1f}s")
    print(f"{'=' * 70}")


if __name__ == '__main__':
    main()