"""Admin visibility service for Publishing Integrity.

Provides query functions used by the admin API endpoints.
"""

import json
import logging
from typing import Dict, Any, List, Optional
from datetime import datetime

import psycopg2.extras

from .repositories import get_connection, PropEligibilityRepo, AuditLogRepo
from .freshness import check_freshness

log = logging.getLogger("pub-integrity")


def get_game_integrity_status(game_id: str) -> Dict[str, Any]:
    """Get full integrity status for a specific game."""
    conn = get_connection()
    try:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        # Get game info
        cur.execute("""
            SELECT event_id, league, home_team, away_team, starts_at, status
            FROM rm_events WHERE event_id = %s
        """, (game_id,))
        game = cur.fetchone()

        # Get all prop eligibilities for this game
        eligibilities = PropEligibilityRepo.get_by_game(conn, game_id)

        # Get recent audit log entries
        cur.execute("""
            SELECT * FROM pi_publish_audit_log
            WHERE game_id = %s ORDER BY acted_at DESC LIMIT 50
        """, (game_id,))
        audit_entries = cur.fetchall()

        cur.close()

        # Build summary
        states = {}
        for e in eligibilities:
            vs = e.get("validation_state", "UNKNOWN")
            states[vs] = states.get(vs, 0) + 1

        return {
            "game": dict(game) if game else None,
            "summary": {
                "total_props": len(eligibilities),
                "states": states,
                "publishable": sum(1 for e in eligibilities if e.get("publish_allowed")),
                "suppressed": sum(1 for e in eligibilities if not e.get("publish_allowed")),
            },
            "eligibilities": [dict(e) for e in eligibilities],
            "recent_audit": [dict(a) for a in audit_entries],
        }
    finally:
        conn.close()


def get_player_integrity_status(player_id: str) -> Dict[str, Any]:
    """Get integrity status across all games for a player."""
    conn = get_connection()
    try:
        eligibilities = PropEligibilityRepo.get_by_player(conn, player_id)
        return {
            "player_id": player_id,
            "total_records": len(eligibilities),
            "records": [dict(e) for e in eligibilities],
        }
    finally:
        conn.close()


def get_suppressions(league: Optional[str] = None, limit: int = 100) -> Dict[str, Any]:
    """Get all current suppressions, optionally filtered by league."""
    conn = get_connection()
    try:
        suppressions = PropEligibilityRepo.get_suppressions(conn, league, limit)
        return {
            "total": len(suppressions),
            "league_filter": league,
            "suppressions": [dict(s) for s in suppressions],
        }
    finally:
        conn.close()


def get_audit_log(limit: int = 200) -> Dict[str, Any]:
    """Get recent audit log entries."""
    conn = get_connection()
    try:
        entries = AuditLogRepo.get_recent(conn, limit)
        return {
            "total": len(entries),
            "entries": [dict(e) for e in entries],
        }
    finally:
        conn.close()


def get_freshness_report(league: str, game_id: str) -> Dict[str, Any]:
    """Get freshness status for a specific game's prop validations."""
    conn = get_connection()
    try:
        eligibilities = PropEligibilityRepo.get_by_game(conn, game_id)
        if not eligibilities:
            return {"game_id": game_id, "status": "no_data"}

        # Use the most recent timestamps across all props
        latest_roster = None
        latest_avail = None
        latest_lineup = None

        for e in eligibilities:
            rv = e.get("roster_verified_at")
            av = e.get("availability_verified_at")
            lv = e.get("lineup_verified_at")
            if rv and (latest_roster is None or rv > latest_roster):
                latest_roster = rv
            if av and (latest_avail is None or av > latest_avail):
                latest_avail = av
            if lv and (latest_lineup is None or lv > latest_lineup):
                latest_lineup = lv

        freshness = check_freshness(league, latest_roster, latest_avail, latest_lineup)
        return {
            "game_id": game_id,
            "league": league,
            "freshness": freshness,
            "prop_count": len(eligibilities),
        }
    finally:
        conn.close()