#!/usr/bin/env python3
"""
Fair Odds + Book Lines Backfill — Extract & Compute for All Leagues
====================================================================
Three-phase approach to populate fairOdds, fairLine, fdLine, dkLine on PlayerPropLine:

Phase 1: Extract from raw JSON (SGO data already has fairOdds, byBookmaker)
Phase 2: Compute no-vig from over/under pairs in PPL
Phase 3: Single-side vig removal using standard margin assumptions

Covers last N days (default 60). Safe to run multiple times (idempotent).

Usage:
  python3 backfill_fair_odds_and_books.py [--days 60] [--league nba] [--batch 5000] [--dry-run]
"""
import os
import sys
import time
import math
import argparse
import psycopg2
import psycopg2.extras
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 helpers ───────────────────────────────────────────────
def american_to_implied(odds):
    """Convert American odds to implied probability (0-1)."""
    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  # even money

def implied_to_american(prob):
    """Convert implied probability (0-1) to American odds."""
    if prob is None or prob <= 0 or prob >= 1:
        return None
    if prob >= 0.5:
        return int(round(-100.0 * prob / (1.0 - prob)))
    else:
        return int(round(100.0 * (1.0 - prob) / prob))

def compute_no_vig_pair(over_odds, under_odds):
    """
    Compute fair (no-vig) odds from an over/under pair using the power method.
    Returns (fair_over_american, fair_under_american).
    """
    p_over = american_to_implied(over_odds)
    p_under = american_to_implied(under_odds)
    if p_over is None or p_under is None:
        return None, None

    total = p_over + p_under
    if total <= 0:
        return None, None

    # Multiplicative vig removal (standard approach)
    fair_over = p_over / total
    fair_under = p_under / total

    return implied_to_american(fair_over), implied_to_american(fair_under)

def estimate_fair_odds_single(odds_american, assumed_vig=0.045):
    """
    Estimate fair odds for a single side assuming standard vig.
    Default 4.5% total vig (~2.25% each side, typical -110/-110).
    """
    if odds_american is None:
        return None
    implied = american_to_implied(odds_american)
    if implied is None:
        return None
    # Remove half the assumed total vig
    fair_prob = implied / (1.0 + assumed_vig / 2.0)
    fair_prob = max(0.01, min(0.99, fair_prob))
    return implied_to_american(fair_prob)


# ─── Phase 1: Extract from raw JSON ──────────────────────────────────
def phase1_extract_from_raw(cur, conn, league_filter, cutoff_date, batch_size, dry_run):
    """Extract fairOdds, fairLine, fdLine, dkLine from raw JSON where available."""
    print("\n" + "=" * 70)
    print("PHASE 1: Extract from raw JSON")
    print("=" * 70)

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

    # Count eligible rows
    cur.execute(f"""
        SELECT COUNT(*) FROM "PlayerPropLine"
        WHERE raw IS NOT NULL
          AND "createdAt" >= %s
          AND (
            ("fairOdds" IS NULL AND raw->'sportsgameodds'->>'fairOdds' IS NOT NULL)
            OR ("fairLine" IS NULL AND raw->'sportsgameodds'->>'fairOverUnder' IS NOT NULL)
            OR ("fdLine" IS NULL AND raw->'sportsgameodds'->'byBookmaker'->'fanduel'->>'overUnder' IS NOT NULL)
            OR ("dkLine" IS NULL AND raw->'sportsgameodds'->'byBookmaker'->'draftkings'->>'overUnder' IS NOT NULL)
          )
          {league_clause}
    """, params)
    total = cur.fetchone()[0]
    print(f"  Eligible rows: {total:,}")

    if total == 0 or dry_run:
        return 0

    # Update fairOdds from raw JSON
    cur.execute(f"""
        UPDATE "PlayerPropLine"
        SET "fairOdds" = (raw->'sportsgameodds'->>'fairOdds')::numeric
        WHERE raw IS NOT NULL
          AND "fairOdds" IS NULL
          AND raw->'sportsgameodds'->>'fairOdds' IS NOT NULL
          AND "createdAt" >= %s
          {league_clause}
    """, params)
    fair_odds_updated = cur.rowcount
    print(f"  fairOdds extracted: {fair_odds_updated:,}")

    # Update fairLine from raw JSON
    cur.execute(f"""
        UPDATE "PlayerPropLine"
        SET "fairLine" = (raw->'sportsgameodds'->>'fairOverUnder')::numeric
        WHERE raw IS NOT NULL
          AND "fairLine" IS NULL
          AND raw->'sportsgameodds'->>'fairOverUnder' IS NOT NULL
          AND "createdAt" >= %s
          {league_clause}
    """, params)
    fair_line_updated = cur.rowcount
    print(f"  fairLine extracted: {fair_line_updated:,}")

    # Update fdLine + fdOverOdds from raw JSON
    cur.execute(f"""
        UPDATE "PlayerPropLine"
        SET
          "fdLine" = (raw->'sportsgameodds'->'byBookmaker'->'fanduel'->>'overUnder')::numeric,
          "fdOverOdds" = (raw->'sportsgameodds'->'byBookmaker'->'fanduel'->>'odds')::numeric
        WHERE raw IS NOT NULL
          AND "fdLine" IS NULL
          AND raw->'sportsgameodds'->'byBookmaker'->'fanduel'->>'overUnder' IS NOT NULL
          AND "createdAt" >= %s
          {league_clause}
    """, params)
    fd_updated = cur.rowcount
    print(f"  fdLine extracted: {fd_updated:,}")

    # Update dkLine + dkOverOdds from raw JSON
    cur.execute(f"""
        UPDATE "PlayerPropLine"
        SET
          "dkLine" = (raw->'sportsgameodds'->'byBookmaker'->'draftkings'->>'overUnder')::numeric,
          "dkOverOdds" = (raw->'sportsgameodds'->'byBookmaker'->'draftkings'->>'odds')::numeric
        WHERE raw IS NOT NULL
          AND "dkLine" IS NULL
          AND raw->'sportsgameodds'->'byBookmaker'->'draftkings'->>'overUnder' IS NOT NULL
          AND "createdAt" >= %s
          {league_clause}
    """, params)
    dk_updated = cur.rowcount
    print(f"  dkLine extracted: {dk_updated:,}")

    conn.commit()
    total_updated = fair_odds_updated + fair_line_updated + fd_updated + dk_updated
    print(f"  Phase 1 total updates: {total_updated:,}")
    return total_updated


# ─── Phase 2: Compute from over/under pairs ──────────────────────────
def phase2_compute_from_pairs(cur, conn, league_filter, cutoff_date, batch_size, dry_run):
    """Compute fair odds from matching over/under pairs in PPL."""
    print("\n" + "=" * 70)
    print("PHASE 2: Compute fair odds from over/under pairs")
    print("=" * 70)

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

    # Find over/under pairs where the over side is missing fairOdds
    # Match by: same player, same game, same propType, same lineValue
    # Over: market IN ('over', 'game_ou_over') or oddId LIKE '%-over'
    # Under: market IN ('under', 'game_ou_under') or oddId LIKE '%-under'
    cur.execute(f"""
        SELECT COUNT(*)
        FROM "PlayerPropLine" o
        JOIN "PlayerPropLine" u
          ON o."playerExternalId" = u."playerExternalId"
          AND o."gameId" = u."gameId"
          AND o."propType" = u."propType"
          AND o."lineValue" = u."lineValue"
          AND o."snapshotAt" = u."snapshotAt"
        WHERE o."fairOdds" IS NULL
          AND o."oddsAmerican" IS NOT NULL
          AND u."oddsAmerican" IS NOT NULL
          AND o.market IN ('over', 'game_ou_over')
          AND u.market IN ('under', 'game_ou_under')
          AND o."createdAt" >= %s
          {league_clause}
    """, params_base)
    total_pairs = cur.fetchone()[0]
    print(f"  Over/under pairs with missing fairOdds: {total_pairs:,}")

    if total_pairs == 0 or dry_run:
        return 0

    # Process in batches
    updated = 0
    offset = 0

    while offset < total_pairs:
        cur.execute(f"""
            SELECT o.id, o."oddsAmerican", u."oddsAmerican", u.id
            FROM "PlayerPropLine" o
            JOIN "PlayerPropLine" u
              ON o."playerExternalId" = u."playerExternalId"
              AND o."gameId" = u."gameId"
              AND o."propType" = u."propType"
              AND o."lineValue" = u."lineValue"
              AND o."snapshotAt" = u."snapshotAt"
            WHERE o."fairOdds" IS NULL
              AND o."oddsAmerican" IS NOT NULL
              AND u."oddsAmerican" IS NOT NULL
              AND o.market IN ('over', 'game_ou_over')
              AND u.market IN ('under', 'game_ou_under')
              AND o."createdAt" >= %s
              {league_clause}
            LIMIT %s
        """, params_base + [batch_size])

        rows = cur.fetchall()
        if not rows:
            break

        batch_updates = []
        for over_id, over_odds, under_odds, under_id in rows:
            fair_over, fair_under = compute_no_vig_pair(over_odds, under_odds)
            if fair_over is not None:
                batch_updates.append((fair_over, over_id))
            if fair_under is not None:
                batch_updates.append((fair_under, under_id))

        if batch_updates:
            psycopg2.extras.execute_batch(
                cur,
                'UPDATE "PlayerPropLine" SET "fairOdds" = %s WHERE id = %s',
                batch_updates
            )
            conn.commit()
            updated += len(batch_updates)

        offset += len(rows)
        print(f"  Processed {offset:,}/{total_pairs:,} pairs, {updated:,} rows updated", flush=True)

    print(f"  Phase 2 total updates: {updated:,}")
    return updated


# ─── Phase 3: Single-side vig estimation ──────────────────────────────
def phase3_single_side_estimation(cur, conn, league_filter, cutoff_date, batch_size, dry_run):
    """Estimate fair odds for remaining rows using standard vig assumption."""
    print("\n" + "=" * 70)
    print("PHASE 3: Single-side vig estimation (fallback)")
    print("=" * 70)

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

    cur.execute(f"""
        SELECT COUNT(*) FROM "PlayerPropLine"
        WHERE "fairOdds" IS NULL
          AND "oddsAmerican" IS NOT NULL
          AND "createdAt" >= %s
          {league_clause}
    """, params)
    remaining = cur.fetchone()[0]
    print(f"  Remaining rows without fairOdds: {remaining:,}")

    if remaining == 0 or dry_run:
        return 0

    updated = 0
    while True:
        cur.execute(f"""
            SELECT id, "oddsAmerican" FROM "PlayerPropLine"
            WHERE "fairOdds" IS NULL
              AND "oddsAmerican" IS NOT NULL
              AND "createdAt" >= %s
              {league_clause}
            LIMIT %s
        """, params + [batch_size])

        rows = cur.fetchall()
        if not rows:
            break

        batch_updates = []
        for row_id, odds_am in rows:
            fair = estimate_fair_odds_single(odds_am)
            if fair is not None:
                batch_updates.append((fair, row_id))

        if batch_updates:
            psycopg2.extras.execute_batch(
                cur,
                'UPDATE "PlayerPropLine" SET "fairOdds" = %s WHERE id = %s',
                batch_updates
            )
            conn.commit()
            updated += len(batch_updates)

        print(f"  Updated {updated:,}/{remaining:,}", flush=True)

    print(f"  Phase 3 total updates: {updated:,}")
    return updated


# ─── Main ─────────────────────────────────────────────────────────────
def main():
    parser = argparse.ArgumentParser(description='Backfill fair odds and book lines')
    parser.add_argument('--days', type=int, default=60, help='Number of days to backfill')
    parser.add_argument('--league', type=str, default=None, help='Filter to specific league')
    parser.add_argument('--batch', type=int, default=5000, help='Batch size')
    parser.add_argument('--dry-run', action='store_true', help='Show counts only')
    parser.add_argument('--phase', type=int, default=0, help='Run specific phase (1/2/3), 0=all')
    args = parser.parse_args()

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

    print("=" * 70)
    print("FAIR ODDS + BOOK LINES BACKFILL")
    print(f"  Days: {args.days}")
    print(f"  Cutoff: {cutoff_date.strftime('%Y-%m-%d %H:%M UTC')}")
    print(f"  League: {args.league or 'ALL'}")
    print(f"  Batch: {args.batch}")
    print(f"  Dry run: {args.dry_run}")
    print("=" * 70)

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

    start = time.time()
    total_updates = 0

    try:
        if args.phase in (0, 1):
            total_updates += phase1_extract_from_raw(
                cur, conn, args.league, cutoff_date, args.batch, args.dry_run)

        if args.phase in (0, 2):
            total_updates += phase2_compute_from_pairs(
                cur, conn, args.league, cutoff_date, args.batch, args.dry_run)

        if args.phase in (0, 3):
            total_updates += phase3_single_side_estimation(
                cur, conn, args.league, cutoff_date, args.batch, 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 — {total_updates:,} total updates in {elapsed:.1f}s")
    print(f"{'=' * 70}")


if __name__ == '__main__':
    main()