"""Hermes V5: title/description EPG search, options and explicit selection."""
import os,re,sqlite3,unicodedata,json,hashlib,secrets
from pathlib import Path
from datetime import datetime,timezone
import pytz
from hermes_football_v3d import DB
from hermes_football_links_v3 import get_target, _validate_url, init
from hermes_football_v3b_aprobar import check_once

EPG=os.environ.get('HERMES_EPG_DB','/var/lib/hermes-nexus/epg.db')
STATE=Path('/var/lib/hermes-nexus/taller/opciones_v5')
TZ=pytz.timezone('America/Tijuana')
STOP={'fc','cf','club','de','del','la','el','los','las','atletico','football','futbol','the','ac','sc'}

def norm(s):
    s=''.join(c for c in unicodedata.normalize('NFKD',str(s or '').casefold()) if not unicodedata.combining(c))
    return re.sub(r'[^a-z0-9]+',' ',s).strip()

def tokens(s):return set(norm(s).split())-STOP

def score(team, text):
    a=tokens(team); b=tokens(text)
    if not a or not b:return 0
    if norm(team) in norm(text):return 1.0
    if len(a)>1 and len(a&b)>=1 and len(a&b)/len(a)>=0.5:return 0.75
    return len(a&b)/len(a)

def _read_db(path):return sqlite3.connect('file:'+str(path)+'?mode=ro',uri=True)

def _session_key(user,session):
    return hashlib.sha256((str(user)+'|'+str(session)).encode()).hexdigest()[:32]

def _path(user,session):return STATE/(_session_key(user,session)+'.json')

def _save(user,session,data):
    STATE.mkdir(parents=True,exist_ok=True,mode=0o700)
    os.chmod(STATE,0o700)
    p=_path(user,session); tmp=p.with_name('.'+secrets.token_hex(8)+'.tmp')
    with open(tmp,'x',encoding='utf8') as f:json.dump(data,f,ensure_ascii=False)
    os.chmod(tmp,0o600);os.replace(tmp,p)

def _load(user,session):
    p=_path(user,session)
    if not p.exists():return None
    data=json.loads(p.read_text(encoding='utf8'))
    if (datetime.now(timezone.utc).timestamp()-data.get('created',0))>3600:
        p.unlink(missing_ok=True);return None
    return data

def _catalog():
    # Same authenticated O11 catalog used by Hermes V3D. No credential logging.
    from hermes_football_v3d import _playlist
    return _playlist()

def _catalog_key(channel):
    from hermes_football_link_manager_v2_1 import epg_num
    return epg_num(channel)

def _epg_candidates(event, max_candidates=12):
    with _read_db(EPG) as db:
        db.row_factory=sqlite3.Row
        cols={x[1] for x in db.execute('PRAGMA table_info(epg_programas)')}
        desc=next((c for c in ('descripcion','description','desc','sinopsis','summary') if c in cols),None)
        if not {'canal','titulo','inicio'}.issubset(cols):
            return [],'EPG_SIN_COLUMNAS_REQUERIDAS'
        fields='canal,titulo,inicio'+(','+desc if desc else '')
        # Limit by day, NOT by arbitrary 500 rows (bug previously found).
        start=datetime.fromisoformat(event['starts_utc'].replace('Z','+00:00'))
        dates={(start.date()).strftime('%Y%m%d'),start.astimezone(TZ).date().strftime('%Y%m%d')}
        rows=[]
        for d in dates:
            rows.extend(db.execute('SELECT '+fields+' FROM epg_programas WHERE inicio LIKE ?',(d+'%',)).fetchall())
    found=[]; seen=set()
    for row in rows:
        channel,title,raw=row['canal'],row['titulo'],row['inicio']
        description=row[desc] if desc else ''
        body=str(title or '')+' '+str(description or '')
        home=score(event['home'],body);away=score(event['away'],body)
        if min(home,away)<0.55:continue
        # EPG time parsing accepts XMLTV compact and ISO timestamps.
        try:
            from hermes_football_v3d import epg_date
            diff=abs((epg_date(raw)-start).total_seconds()/60)
        except Exception:
            continue
        if diff>180:continue
        key=(channel,raw)
        if key in seen:continue
        seen.add(key)
        found.append({'canal':channel,'titulo':str(title or '')[:130],
                      'descripcion':str(description or '')[:200],
                      'diferencia_minutos':round(diff,1),
                      'puntuacion':round(min(home,away),2),
                      'key':_catalog_key(channel)})
    found.sort(key=lambda x:(-x['puntuacion'],x['diferencia_minutos']))
    return found[:max_candidates],('DESCRIPCION_DISPONIBLE' if desc else 'EPG_SIN_DESCRIPCION')

def _event(term):
    with _read_db(DB) as db:
        db.row_factory=sqlite3.Row
        rows=[dict(r) for r in db.execute('SELECT id,home,away,local_day,starts_utc FROM events ORDER BY starts_utc DESC LIMIT 1000')]
    found=[e for e in rows if score(term,e['home'])>=0.7 or score(term,e['away'])>=0.7]
    today=datetime.now(TZ).date().isoformat()
    todays=[e for e in found if e['local_day']==today]
    if len(todays)==1:return todays[0],None
    if len(found)==1:return found[0],None
    if not found:return None,{'estado':'SIN_PARTIDO'}
    return None,{'estado':'VARIOS_PARTIDOS','partidos':[{'id':e['id'],'partido':e['home']+' contra '+e['away'],'fecha':e['local_day']} for e in (todays or found)[:8]]}

def buscar_opciones(equipo,user,session):
    event,error=_event(equipo)
    if error:return error
    rows,epg_info=_epg_candidates(event)
    if not rows:return {'estado':'SIN_COINCIDENCIA_EPG','partido':event['home']+' contra '+event['away'],'detalle':epg_info}
    catalog=_catalog()
    options=[]
    for row in rows:
        entries=catalog.get(row['key'],[]) if row['key'] else []
        for entry in entries:
            if not isinstance(entry,dict) or not entry.get('url'):continue
            options.append({'nombre':str(entry.get('nombre','Canal'))[:100],
                            'canal':row['canal'],'titulo':row['titulo'],
                            'descripcion':row['descripcion'],'url':entry['url'],
                            'diferencia_minutos':row['diferencia_minutos']})
    if not options:return {'estado':'SIN_CANAL_EN_CATALOGO','partido':event['home']+' contra '+event['away'],'epg':len(rows)}
    options=options[:8]
    _save(user,session,{'created':datetime.now(timezone.utc).timestamp(),
          'event_id':event['id'],'partido':event['home']+' contra '+event['away'],'opciones':options})
    return {'estado':'ELEGIR_FUENTE','partido':event['home']+' contra '+event['away'],
            'opciones':[{'numero':i+1,'canal':o['nombre'],'titulo':o['titulo'],
                         'descripcion':o['descripcion'],'diferencia_minutos':o['diferencia_minutos']} for i,o in enumerate(options)],
            'detalle':epg_info}

def elegir_opcion(numero,user,session):
    data=_load(user,session)
    if not data:return {'estado':'SIN_SELECCION_PENDIENTE'}
    if not isinstance(numero,int) or not 1<=numero<=len(data['opciones']):return {'estado':'OPCION_INVALIDA'}
    option=data['opciones'][numero-1]
    evidence={'estado':'SELECCION_EXPLICITA_USUARIO','epg_canal':option['canal'],
              'diferencia_minutos':option['diferencia_minutos'],
              'nota':'Elegido por el usuario tras búsqueda EPG por título/descripción'}
    url=_validate_url(option['url'])
    init()
    with sqlite3.connect(DB,timeout=20) as db:
        db.execute('BEGIN IMMEDIATE')
        if not db.execute('SELECT 1 FROM events WHERE id=?',(data['event_id'],)).fetchone():
            return {'estado':'EVENTO_NO_ENCONTRADO'}
        old=db.execute('SELECT manual_url FROM football_links WHERE event_id=?',(data['event_id'],)).fetchone()
        if old and old[0]:
            return {'estado':'ENLACE_MANUAL_PROTEGIDO'}
        now=datetime.now(timezone.utc).isoformat()
        db.execute("INSERT INTO football_links(event_id,automatic_url,provider,epg_channel,evidence,updated_utc) "
           "VALUES(?,?,?,?,?,?) ON CONFLICT(event_id) DO UPDATE SET "
           "automatic_url=excluded.automatic_url,provider=excluded.provider, "
           "epg_channel=excluded.epg_channel,evidence=excluded.evidence,updated_utc=excluded.updated_utc",
           (data['event_id'],url,'sling',option['canal'],json.dumps(evidence,ensure_ascii=False),now))
        db.execute('INSERT INTO football_link_audit(event_id,action,timestamp_utc,note) VALUES(?,?,?,?)',
           (data['event_id'],'USER_SELECTED_V5',now,option['canal']))
        db.commit()
    confirmed=get_target(data['event_id'])==option['url']
    status=check_once(option['url']) if confirmed else 'NO_COMPROBADO'
    if confirmed:_path(user,session).unlink(missing_ok=True)
    return {'estado':'ASIGNADO' if confirmed else 'GUARDADO_NO_VERIFICADO',
            'partido':data['partido'],'canal':option['nombre'],
            'guardado_verificado':confirmed,'verificacion_http':status,
            'refrescar_panel':bool(confirmed)}