"""
Unified Odds Ingestion Helper

This module provides functions to write odds data to the new unified schema
(BookmakerOdds, OddsHistoryV2, OddsIngestion tables).

Used by existing cron scripts to ensure data flows to both legacy and unified tables.
"""
import os
import sys
import psycopg2
from datetime import datetime, timezone
from typing import Optional, Dict, List, Any
sys.path.insert(0, os.path.join(os.path.dirname(os.path.abspath(__file__)), '..'))
from team_names import ABBREV_TO_FULL, normalize_to_full


# SportsGame is not globally consistent across leagues. For NBA specifically,
# downstream SportsClaw/DIGIMON logic expects abbreviations, not full names.
SPORTSGAME_CODE_LEAGUES = {'nba'}


def get_db_connection(db_url: str):
    """Create database connection from URL."""
    return psycopg2.connect(db_url)


def log_ingestion_start(
    conn,
    source: str,
    league: str,
    run_type: str = 'scheduled',
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
) -> int:
    """
    Log the start of an ingestion run.
    Returns the ingestion run ID.
    """
    cur = conn.cursor()
    cur.execute('''
        INSERT INTO "OddsIngestion" (
            source, league, "runType", "startDate", "endDate",
            "startedAt", status
        ) VALUES (%s, %s, %s, %s, %s, NOW(), 'running')
        RETURNING id
    ''', (source, league, run_type, start_date, end_date))
    ingestion_id = cur.fetchone()[0]
    conn.commit()
    cur.close()
    return ingestion_id


def log_ingestion_complete(
    conn,
    ingestion_id: int,
    games_processed: int = 0,
    bookmakers_seen: int = 0,
    odds_created: int = 0,
    odds_updated: int = 0,
    snapshots_created: int = 0,
    errors_count: int = 0,
    errors: Optional[List[str]] = None,
    metadata: Optional[Dict] = None,
    status: str = 'completed',
):
    """Log the completion of an ingestion run."""
    cur = conn.cursor()
    cur.execute('''
        UPDATE "OddsIngestion"
        SET "completedAt" = NOW(),
            "durationMs" = EXTRACT(EPOCH FROM (NOW() - "startedAt")) * 1000,
            "gamesProcessed" = %s,
            "bookmakersSeen" = %s,
            "oddsCreated" = %s,
            "oddsUpdated" = %s,
            "snapshotsCreated" = %s,
            "errorsCount" = %s,
            errors = %s::jsonb,
            metadata = %s::jsonb,
            status = %s
        WHERE id = %s
    ''', (
        games_processed, bookmakers_seen, odds_created, odds_updated,
        snapshots_created, errors_count,
        psycopg2.extras.Json(errors) if errors else None,
        psycopg2.extras.Json(metadata) if metadata else None,
        status, ingestion_id
    ))
    conn.commit()
    cur.close()


def upsert_bookmaker_odds(
    conn,
    game_id: int,
    league: str,
    game_date: datetime,
    home_team: str,
    away_team: str,
    bookmaker: str,
    market: str,
    line_value: Optional[float],
    home_odds: Optional[int],
    away_odds: Optional[int],
    source: str = 'the-odds-api',
    is_opening: bool = False,
) -> tuple:
    """
    Upsert a bookmaker odds record.
    Returns (created: bool, updated: bool).
    """
    cur = conn.cursor()

    # Normalize line_value for unique constraint
    line_value_norm = line_value if line_value is not None else 0.0

    # Check if record exists
    cur.execute('''
        SELECT id, "openingLineValue", "openingHomeOdds"
        FROM "BookmakerOdds"
        WHERE "gameId" = %s AND bookmaker = %s AND market = %s AND COALESCE("lineValue", 0) = %s
    ''', (game_id, bookmaker, market, line_value_norm))

    existing = cur.fetchone()

    if existing:
        existing_id, existing_opening_line, existing_opening_odds = existing

        # Calculate line movement
        line_movement = None
        if line_value is not None and existing_opening_line is not None:
            line_movement = line_value - float(existing_opening_line)

        odds_movement = None
        if home_odds is not None and existing_opening_odds is not None:
            odds_movement = home_odds - existing_opening_odds

        # Update
        cur.execute('''
            UPDATE "BookmakerOdds"
            SET "homeOdds" = COALESCE(%s, "homeOdds"),
                "awayOdds" = COALESCE(%s, "awayOdds"),
                "lineValue" = COALESCE(%s, "lineValue"),
                "lineMovement" = %s,
                "oddsMovement" = %s,
                "fetchedAt" = NOW(),
                "updatedAt" = NOW()
            WHERE id = %s
        ''', (home_odds, away_odds, line_value, line_movement, odds_movement, existing_id))
        conn.commit()
        cur.close()
        return (False, True)
    else:
        # Insert new record
        cur.execute('''
            INSERT INTO "BookmakerOdds" (
                "gameId", league, "gameDate", "homeTeam", "awayTeam",
                bookmaker, market, "lineValue", "homeOdds", "awayOdds",
                "openingLineValue", "openingHomeOdds", "openingAwayOdds", "openingAt",
                source, "fetchedAt"
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s, NOW()
            )
        ''', (
            game_id, league, game_date, home_team, away_team,
            bookmaker, market, line_value, home_odds, away_odds,
            # Opening values are the same as current on first insert
            line_value, home_odds, away_odds, datetime.now(timezone.utc),
            source
        ))
        conn.commit()
        cur.close()
        return (True, False)


def insert_odds_history(
    conn,
    game_id: int,
    league: str,
    game_date: datetime,
    bookmaker: str,
    market: str,
    line_value: Optional[float],
    home_odds: Optional[int],
    away_odds: Optional[int],
    hours_to_game: Optional[float] = None,
    snapshot_type: str = 'periodic',
    source: str = 'the-odds-api',
    bookmaker_odds_id: Optional[int] = None,
) -> bool:
    """
    Insert an odds history snapshot.
    Returns True if inserted, False if duplicate.
    """
    cur = conn.cursor()

    # Calculate hours to game
    if hours_to_game is None and game_date:
        now = datetime.now(timezone.utc)
        if game_date.tzinfo is None:
            game_date = game_date.replace(tzinfo=timezone.utc)
        hours_to_game = (game_date - now).total_seconds() / 3600

    try:
        cur.execute('''
            INSERT INTO "OddsHistoryV2" (
                "bookmakerOddsId", "gameId", league, "gameDate",
                bookmaker, market, "lineValue", "homeOdds", "awayOdds",
                "snapshotAt", "hoursToGame", "snapshotType", source
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s,
                NOW(), %s, %s, %s
            )
        ''', (
            bookmaker_odds_id, game_id, league, game_date,
            bookmaker, market, line_value if line_value is not None else 0,
            home_odds, away_odds,
            hours_to_game, snapshot_type, source
        ))
        conn.commit()
        cur.close()
        return True
    except psycopg2.IntegrityError:
        conn.rollback()
        cur.close()
        return False


def _to_sportsgame_team_value(team_name: str, league: str) -> str:
    """Convert incoming team text to the canonical SportsGame storage form."""
    full_name = normalize_to_full(team_name, league) or team_name
    if league not in SPORTSGAME_CODE_LEAGUES:
        return full_name

    league_map = ABBREV_TO_FULL.get(league, {})
    for abbrev, mapped_full in league_map.items():
        if mapped_full == full_name:
            return abbrev
    return full_name


def _sportsgame_lookup_candidates(team_name: str, league: str) -> List[str]:
    """
    Build lookup candidates that can match both legacy full-name rows and the
    corrected canonical storage form for the league.
    """
    candidates: List[str] = []
    if not team_name:
        return candidates

    raw = team_name.strip()
    full_name = normalize_to_full(raw, league) or raw
    stored_value = _to_sportsgame_team_value(raw, league)

    for value in (raw, full_name, stored_value):
        if value and value not in candidates:
            candidates.append(value)
    return candidates


def _refresh_sports_game_identity(
    conn,
    game_id: int,
    home_team: str,
    away_team: str,
    external_game_id: Optional[str] = None,
) -> None:
    """
    Normalize an existing SportsGame row in place when we match it through a
    legacy identity (full names, missing externalGameId, etc.).
    """
    cur = conn.cursor()
    cur.execute(
        '''
        UPDATE "SportsGame"
        SET "homeTeam" = %s,
            "awayTeam" = %s,
            "externalGameId" = COALESCE("SportsGame"."externalGameId", %s),
            "updatedAt" = NOW()
        WHERE id = %s
          AND (
            COALESCE("homeTeam", '') <> %s OR
            COALESCE("awayTeam", '') <> %s OR
            (%s IS NOT NULL AND "externalGameId" IS NULL)
          )
        ''',
        (
            home_team,
            away_team,
            external_game_id,
            game_id,
            home_team,
            away_team,
            external_game_id,
        ),
    )
    if cur.rowcount:
        conn.commit()
    cur.close()


def get_or_create_sports_game(
    conn,
    league: str,
    game_date: datetime,
    home_team: str,
    away_team: str,
    external_game_id: Optional[str] = None,
) -> Optional[int]:
    """
    Find or create a SportsGame record.
    Returns the game ID.
    Uses the league's canonical SportsGame storage format.
    """
    league = league.lower()
    home_lookup = _sportsgame_lookup_candidates(home_team, league)
    away_lookup = _sportsgame_lookup_candidates(away_team, league)
    home_team = _to_sportsgame_team_value(home_team, league)
    away_team = _to_sportsgame_team_value(away_team, league)

    cur = conn.cursor()

    # Try to find by external ID first
    if external_game_id:
        cur.execute('''
            SELECT id FROM "SportsGame"
            WHERE "externalGameId" = %s
        ''', (external_game_id,))
        result = cur.fetchone()
        if result:
            _refresh_sports_game_identity(conn, result[0], home_team, away_team, external_game_id)
            cur.close()
            return result[0]

    # Try to find by teams and date
    day_start = game_date.replace(hour=0, minute=0, second=0, microsecond=0)
    day_end = day_start.replace(hour=23, minute=59, second=59)

    cur.execute('''
        SELECT id FROM "SportsGame"
        WHERE league = %s
          AND "homeTeam" = ANY(%s)
          AND "awayTeam" = ANY(%s)
          AND "gameDate" BETWEEN %s AND %s
        ORDER BY
          CASE WHEN "homeTeam" = %s AND "awayTeam" = %s THEN 0 ELSE 1 END,
          CASE WHEN "externalGameId" IS NULL THEN 1 ELSE 0 END,
          id DESC
    ''', (league, home_lookup, away_lookup, day_start, day_end, home_team, away_team))

    result = cur.fetchone()
    if result:
        _refresh_sports_game_identity(conn, result[0], home_team, away_team, external_game_id)
        cur.close()
        return result[0]

    # Create new game — use ON CONFLICT to handle races with other ingest processes
    season = game_date.year
    try:
        cur.execute('''
            INSERT INTO "SportsGame" (
                league, season, "gameDate", "homeTeam", "awayTeam", "externalGameId",
                "createdAt", "updatedAt"
            ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
            ON CONFLICT (league, season, "gameDate", "homeTeam", "awayTeam") DO UPDATE SET
                "externalGameId" = COALESCE("SportsGame"."externalGameId", EXCLUDED."externalGameId"),
                "updatedAt" = NOW()
            RETURNING id
        ''', (league, season, game_date, home_team, away_team, external_game_id))

        game_id = cur.fetchone()[0]
        conn.commit()
        cur.close()
        return game_id
    except psycopg2.IntegrityError:
        # Another unique constraint conflict (e.g., externalGameId) — rollback and re-lookup
        conn.rollback()
        cur.close()
        cur = conn.cursor()
        cur.execute('''
            SELECT id FROM "SportsGame"
            WHERE league = %s
              AND "homeTeam" = %s
              AND "awayTeam" = %s
              AND "gameDate" BETWEEN %s AND %s
        ''', (league, home_team, away_team, day_start, day_end))
        result = cur.fetchone()
        cur.close()
        return result[0] if result else None


def process_odds_for_game(
    conn,
    league: str,
    game_date: datetime,
    home_team: str,
    away_team: str,
    bookmaker_odds: List[Dict[str, Any]],
    external_game_id: Optional[str] = None,
    source: str = 'the-odds-api',
    create_snapshots: bool = True,
) -> Dict[str, int]:
    """
    Process all bookmaker odds for a single game.

    bookmaker_odds should be a list of dicts with:
    - bookmaker: str
    - markets: dict with keys like 'h2h', 'spreads', 'totals'
      each containing 'home_odds', 'away_odds', 'line' (for spreads/totals)

    Returns dict with 'created', 'updated', 'snapshots'.
    """
    stats = {'created': 0, 'updated': 0, 'snapshots': 0}

    # Get or create the game
    game_id = get_or_create_sports_game(
        conn, league, game_date, home_team, away_team, external_game_id
    )
    if not game_id:
        return stats

    for book_data in bookmaker_odds:
        bookmaker = book_data.get('bookmaker', 'unknown')
        markets = book_data.get('markets', {})

        for market_key, market_data in markets.items():
            # Normalize market name
            if market_key == 'h2h':
                market = 'moneyline'
            elif market_key == 'spreads':
                market = 'spread'
            elif market_key == 'totals':
                market = 'total'
            else:
                market = market_key

            line_value = market_data.get('line')
            home_odds = market_data.get('home_odds')
            away_odds = market_data.get('away_odds')

            # Skip if no odds data
            if home_odds is None and away_odds is None:
                continue

            # Upsert to BookmakerOdds
            created, updated = upsert_bookmaker_odds(
                conn, game_id, league, game_date, home_team, away_team,
                bookmaker, market, line_value, home_odds, away_odds, source
            )

            if created:
                stats['created'] += 1
            if updated:
                stats['updated'] += 1

            # Create snapshot if requested
            if create_snapshots:
                if insert_odds_history(
                    conn, game_id, league, game_date,
                    bookmaker, market, line_value, home_odds, away_odds,
                    source=source
                ):
                    stats['snapshots'] += 1

    return stats


# Psycopg2 extras for JSON
try:
    import psycopg2.extras
except ImportError:
    pass