#!/usr/bin/env python3
"""
Calculate Estimated Half-Lines from Full-Game Lines
Workaround while DraftKings scraping is geo-blocked.

Uses historical correlations to estimate 1H/2H spreads and totals.
Less accurate than actual lines but enables analysis.

Run: Daily after odds sync
"""
import psycopg2
import os
from datetime import datetime, timezone


# Historical correlation factors (derived from market analysis)
# These represent typical 1H line as fraction of full game line
HALF_LINE_FACTORS = {
    'nba': {
        '1h': {'spread': 0.52, 'total': 0.50},  # NBA 1H slightly favors home
        '2h': {'spread': 0.48, 'total': 0.50},
    },
    'nfl': {
        '1h': {'spread': 0.48, 'total': 0.47},  # NFL 1H more variable
        '2h': {'spread': 0.52, 'total': 0.53},
    },
    'nhl': {
        'p1': {'spread': 0.33, 'total': 0.33},  # Hockey periods roughly equal
        'p2': {'spread': 0.33, 'total': 0.33},
        'p3': {'spread': 0.34, 'total': 0.34},
    },
    'ncaab': {
        '1h': {'spread': 0.50, 'total': 0.48},  # College hoops
        '2h': {'spread': 0.50, 'total': 0.52},
    },
    'ncaaf': {
        '1h': {'spread': 0.45, 'total': 0.45},  # College football
        '2h': {'spread': 0.55, 'total': 0.55},
    },
}


def load_db_url():
    env_path = '/var/www/html/eventheodds/.env'
    try:
        with open(env_path, 'r') as f:
            for line in f:
                if line.startswith('SPORTS_DATABASE_URL='):
                    return line.split('=', 1)[1].strip().split('?')[0]
    except:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def ensure_table(cur):
    """Ensure GameHalfLine table exists"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "GameHalfLine" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(50) NOT NULL,
            "gameId" BIGINT,
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            period VARCHAR(10) NOT NULL,
            market VARCHAR(50) NOT NULL,
            side VARCHAR(50),
            "lineValue" FLOAT,
            "bookOdds" INT,
            bookmaker VARCHAR(50) DEFAULT 'estimated',
            "isEstimated" BOOLEAN DEFAULT TRUE,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            "updatedAt" TIMESTAMP DEFAULT NOW()
        )
    ''')

    # Add isEstimated column if it doesn't exist
    cur.execute('''
        DO $$ BEGIN
            ALTER TABLE "GameHalfLine" ADD COLUMN "isEstimated" BOOLEAN DEFAULT FALSE;
        EXCEPTION
            WHEN duplicate_column THEN NULL;
        END $$;
    ''')


def calculate_half_lines(conn):
    """Calculate estimated half-lines from full-game lines"""
    cur = conn.cursor()

    print("=" * 60)
    print("CALCULATE ESTIMATED HALF-LINES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    ensure_table(cur)
    conn.commit()

    total_created = 0

    # Process each league
    for league in ['nba', 'nfl', 'nhl', 'ncaab', 'ncaaf']:
        factors = HALF_LINE_FACTORS.get(league, HALF_LINE_FACTORS.get('nba'))

        # Get upcoming games with spreads and totals
        cur.execute('''
            SELECT id, "gameDate", "homeTeam", "awayTeam",
                   "spreadHome", total
            FROM "SportsGame"
            WHERE league = %s
              AND "gameDate" > NOW()
              AND "gameDate" < NOW() + INTERVAL '7 days'
              AND ("spreadHome" IS NOT NULL OR total IS NOT NULL)
            ORDER BY "gameDate"
        ''', (league,))

        games = cur.fetchall()
        print(f"\n{league.upper()}: {len(games)} upcoming games with lines")

        created = 0
        for game in games:
            game_id, game_date, home, away, spread_home, total = game
            spread = spread_home  # Use home spread as the reference

            for period, period_factors in factors.items():
                # Calculate estimated spread
                if spread is not None:
                    half_spread = round(spread * period_factors['spread'], 1)

                    # Insert home spread
                    try:
                        cur.execute('''
                            INSERT INTO "GameHalfLine"
                            (league, "gameId", "gameDate", "homeTeam", "awayTeam",
                             period, market, side, "lineValue", "bookOdds",
                             bookmaker, "isEstimated")
                            VALUES (%s, %s, %s, %s, %s, %s, 'spread', 'home', %s, -110,
                                    'estimated', TRUE)
                            ON CONFLICT DO NOTHING
                        ''', (league, game_id, game_date, home, away, period, half_spread))

                        # Insert away spread (opposite)
                        cur.execute('''
                            INSERT INTO "GameHalfLine"
                            (league, "gameId", "gameDate", "homeTeam", "awayTeam",
                             period, market, side, "lineValue", "bookOdds",
                             bookmaker, "isEstimated")
                            VALUES (%s, %s, %s, %s, %s, %s, 'spread', 'away', %s, -110,
                                    'estimated', TRUE)
                            ON CONFLICT DO NOTHING
                        ''', (league, game_id, game_date, home, away, period, -half_spread))
                        created += 2
                    except:
                        pass

                # Calculate estimated total
                if total is not None:
                    half_total = round(total * period_factors['total'], 1)

                    try:
                        # Insert over
                        cur.execute('''
                            INSERT INTO "GameHalfLine"
                            (league, "gameId", "gameDate", "homeTeam", "awayTeam",
                             period, market, side, "lineValue", "bookOdds",
                             bookmaker, "isEstimated")
                            VALUES (%s, %s, %s, %s, %s, %s, 'total', 'over', %s, -110,
                                    'estimated', TRUE)
                            ON CONFLICT DO NOTHING
                        ''', (league, game_id, game_date, home, away, period, half_total))

                        # Insert under
                        cur.execute('''
                            INSERT INTO "GameHalfLine"
                            (league, "gameId", "gameDate", "homeTeam", "awayTeam",
                             period, market, side, "lineValue", "bookOdds",
                             bookmaker, "isEstimated")
                            VALUES (%s, %s, %s, %s, %s, %s, 'total', 'under', %s, -110,
                                    'estimated', TRUE)
                            ON CONFLICT DO NOTHING
                        ''', (league, game_id, game_date, home, away, period, half_total))
                        created += 2
                    except:
                        pass

        conn.commit()
        print(f"  Created {created} estimated half-lines")
        total_created += created

    # Summary
    cur.execute('''
        SELECT league, period, market, COUNT(*),
               COUNT(*) FILTER (WHERE "isEstimated" = TRUE) as estimated
        FROM "GameHalfLine"
        GROUP BY league, period, market
        ORDER BY league, period, market
    ''')

    print("\nHalf-Line Coverage:")
    for row in cur.fetchall():
        print(f"  {row[0]} {row[1]} {row[2]}: {row[3]} ({row[4]} estimated)")

    cur.close()

    print("\n" + "=" * 60)
    print(f"TOTAL: {total_created} estimated half-lines created")
    print("=" * 60)

    return total_created


def main():
    db_url = load_db_url()
    if not db_url:
        print("ERROR: No database URL")
        return

    conn = psycopg2.connect(db_url)
    calculate_half_lines(conn)
    conn.close()


if __name__ == '__main__':
    main()