"""Herramientas de inspección de fútbol, exclusivamente lectura.
No descarga playlists, no publica enlaces ni devuelve secretos a Gemini.
Python 3.8.
"""
import json
import sqlite3
from pathlib import Path

EPG = Path("/var/lib/hermes-nexus/epg.db")
FOOTBALL = Path("/var/lib/hermes-nexus/football.db")
JSON_FILES = (
    Path("/var/www/html/NFL_ST/hermes_lmp_epg_live.json"),
    Path("/var/www/html/NFL_ST/hermes_lmp_epg_cache.json"),
)
MAX_BYTES = 5 * 1024 * 1024
SAFE_KEYS = {"title","titulo","name","nombre","team","equipo","home","away",
             "match","partido","start","starts","date","fecha","time","hora",
             "channel","canal","competition","league","liga","description",
             "descripcion","shortname","displayname"}

def _term(termino):
    if not isinstance(termino, str) or not 2 <= len(termino.strip()) <= 100:
        raise ValueError("Término de búsqueda entre 2 y 100 caracteres")
    return termino.strip().casefold()

def _limit(limite):
    return max(1, min(int(limite), 25))

def explorar_epg(termino, limite=15):
    term = _term(termino)
    limite = _limit(limite)
    if not EPG.is_file():
        return {"error":"EPG_DB_NO_DISPONIBLE"}
    db = sqlite3.connect("file:{}?mode=ro".format(EPG), uri=True, timeout=5)
    try:
        rows = db.execute("""
            SELECT fuente, canal, titulo, inicio, fin, descripcion
            FROM epg_programas
            WHERE titulo LIKE ? OR descripcion LIKE ? OR canal LIKE ?
            ORDER BY inicio DESC LIMIT ?
        """, ("%" + term + "%", "%" + term + "%", "%" + term + "%", limite)).fetchall()
        return {"termino":termino,"total_mostrados":len(rows),"solo_lectura":True,
                "programas":[{"fuente":r[0],"canal":r[1],"titulo":r[2],
                             "inicio":r[3],"fin":r[4],
                             "descripcion":(r[5] or "")[:180]} for r in rows]}
    finally:
        db.close()

def examinar_json(termino, limite=15):
    term = _term(termino)
    limite = _limit(limite)
    matches = []
    archivos = []
    for path in JSON_FILES:
        if not path.is_file() or path.stat().st_size > MAX_BYTES:
            continue
        archivos.append(path.name)
        try:
            obj = json.loads(path.read_text(encoding="utf-8"))
        except (ValueError, OSError, UnicodeError):
            continue
        stack = [(obj,0)]
        seen = 0
        while stack and len(matches) < limite and seen < 30000:
            item, depth = stack.pop()
            seen += 1
            if depth > 12:
                continue
            if isinstance(item, list):
                stack.extend((x, depth+1) for x in item[:2000])
            elif isinstance(item, dict):
                safe = {}
                for k,v in item.items():
                    key = str(k).lower().replace("_","")
                    if key in SAFE_KEYS and isinstance(v,(str,int,float)):
                        value = str(v)[:180]
                        if "://" not in value and "@" not in value:
                            safe[str(k)]=value
                if safe and any(term in v.casefold() for v in safe.values()):
                    matches.append({"archivo":path.name,"campos":safe})
                for v in item.values():
                    if isinstance(v,(dict,list)):
                        stack.append((v,depth+1))
    return {"termino":termino,"archivos_revisados":archivos,
            "coincidencias":matches,"solo_lectura":True,
            "nota":"Solo JSON de lista permitida; no se leen archivos arbitrarios."}

def revisar_tarjetas():
    if not FOOTBALL.is_file():
        return {"error":"FOOTBALL_DB_NO_DISPONIBLE"}
    db = sqlite3.connect("file:{}?mode=ro".format(FOOTBALL), uri=True, timeout=5)
    try:
        rows = db.execute("""
            SELECT e.id,e.league,e.home,e.away,e.starts_utc,
                   COALESCE(l.provider,''),COALESCE(l.epg_channel,''),
                   CASE WHEN COALESCE(l.manual_url,'')<>'' THEN 1 ELSE 0 END,
                   CASE WHEN COALESCE(l.automatic_url,'')<>'' THEN 1 ELSE 0 END
            FROM events e LEFT JOIN football_links l ON l.event_id=e.id
            ORDER BY e.starts_utc DESC LIMIT 35
        """).fetchall()
        return {"eventos":[{"id":r[0],"liga":r[1],"local":r[2],"visitante":r[3],
                            "inicio_utc":r[4],"proveedor":r[5],"canal_epg":r[6],
                            "manual":bool(r[7]),"automatico":bool(r[8])} for r in rows],
                "solo_lectura":True}
    finally:
        db.close()