"""CRUD de tarjetas de fútbol por voz. Eventos manuales e importados, auditoría y borrado explícito."""
import sqlite3, re, unicodedata
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
from uuid import uuid4
from hermes_football_v3d import DB

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

def find_team(team):
    needle=norm(team)
    with sqlite3.connect('file:'+DB+'?mode=ro',uri=True) as db:
        db.row_factory=sqlite3.Row
        rows=[dict(x) for x in db.execute('SELECT id,home,away,local_day,source FROM events ORDER BY starts_utc DESC LIMIT 500')]
    return [r for r in rows if needle and any(needle in norm(r[k]) for k in ('home','away'))]

def create_card(home,away,start_iso,league='Fútbol'):
    if not all(isinstance(s,str) and 2<=len(s.strip())<=80 for s in (home,away,league)):
        return {'estado':'DATOS_INVALIDOS'}
    try:
        dt=datetime.fromisoformat(start_iso.replace('Z','+00:00'))
        if dt.tzinfo is None: return {'estado':'FALTA_ZONA_HORARIA'}
        dt=dt.astimezone(timezone.utc)
    except (ValueError,AttributeError):return {'estado':'FECHA_INVALIDA'}
    event_id='manual:'+uuid4().hex
    # No se crean enlaces ni se publica programación inventada.
    with sqlite3.connect(DB,timeout=15) as db:
        db.execute('BEGIN IMMEDIATE')
        db.execute('''INSERT INTO events(id,league,league_code,home,away,logo,starts_utc,state,network,source,local_day,updated,away_logo)
                      VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)''',
                   (event_id,league,'manual',home.strip(),away.strip(),'',dt.isoformat(),'pre',
                    'Canal por confirmar','HERMES_MANUAL',dt.astimezone(ZoneInfo('America/Tijuana')).date().isoformat(),datetime.now(timezone.utc).isoformat(),''))
        db.execute('''CREATE TABLE IF NOT EXISTS hermes_card_audit
                   (id INTEGER PRIMARY KEY AUTOINCREMENT,event_id TEXT,action TEXT,created_utc TEXT,note TEXT)''')
        db.execute('INSERT INTO hermes_card_audit(event_id,action,created_utc,note) VALUES(?,?,?,?)',
                   (event_id,'CREATE',datetime.now(timezone.utc).isoformat(),home+' vs '+away))
    with sqlite3.connect(DB) as verify:
        visible=verify.execute('SELECT local_day FROM events WHERE id=?',(event_id,)).fetchone()
    return {'estado':'CREADA' if visible else 'NO_VERIFICADA','id':event_id,'partido':home+' vs '+away,'fecha_panel':visible[0] if visible else None,'refrescar_panel':bool(visible)}

def delete_card(event_id):
    if not isinstance(event_id,str) or not re.fullmatch(r'[a-zA-Z0-9_.:-]{5,120}',event_id):
        return {'estado':'ID_INVALIDO'}
    with sqlite3.connect(DB,timeout=15) as db:
        db.execute('BEGIN IMMEDIATE')
        row=db.execute('SELECT home,away FROM events WHERE id=?',(event_id,)).fetchone()
        if not row:return {'estado':'NO_ENCONTRADA'}
        db.execute('''CREATE TABLE IF NOT EXISTS hermes_card_audit
                   (id INTEGER PRIMARY KEY AUTOINCREMENT,event_id TEXT,action TEXT,created_utc TEXT,note TEXT)''')
        db.execute('CREATE TABLE IF NOT EXISTS hermes_football_exclusions (event_id TEXT PRIMARY KEY, deleted_utc TEXT NOT NULL)')
        db.execute('INSERT OR REPLACE INTO hermes_football_exclusions(event_id,deleted_utc) VALUES (?,?)',(event_id,datetime.now(timezone.utc).isoformat()))
        db.execute('DELETE FROM football_links WHERE event_id=?',(event_id,))
        db.execute('DELETE FROM events WHERE id=?',(event_id,))
        db.execute('INSERT INTO hermes_card_audit(event_id,action,created_utc,note) VALUES(?,?,?,?)',
                   (event_id,'DELETE',datetime.now(timezone.utc).isoformat(),row[0]+' vs '+row[1]))
    with sqlite3.connect(DB) as verify:
        restante=verify.execute('SELECT COUNT(*) FROM events WHERE id=?',(event_id,)).fetchone()[0]
    return {'estado':'BORRADA' if not restante else 'NO_VERIFICADA','id':event_id,'refrescar_panel':not bool(restante)}


def purgar_partidos_concluidos(horas_gracia=3):
    """Borra tarjetas de Fútbol iniciadas hace más de N horas, sea cual sea su estado."""
    from datetime import timedelta
    try: horas=float(horas_gracia)
    except (TypeError,ValueError): return {'estado':'HORAS_INVALIDAS'}
    if not 0 <= horas <= 720: return {'estado':'HORAS_INVALIDAS'}
    limite=datetime.now(timezone.utc)-timedelta(hours=horas)
    eliminados=[]
    with sqlite3.connect(DB,timeout=20) as db:
        db.execute('BEGIN IMMEDIATE')
        db.execute("CREATE TABLE IF NOT EXISTS hermes_card_audit "
                   "(id INTEGER PRIMARY KEY AUTOINCREMENT,event_id TEXT,action TEXT,created_utc TEXT,note TEXT)")
        for eid,inicio in db.execute('SELECT id,starts_utc FROM events').fetchall():
            try:
                fecha=datetime.fromisoformat(str(inicio).replace('Z','+00:00'))
                if fecha.tzinfo is None: fecha=fecha.replace(tzinfo=timezone.utc)
                if fecha.astimezone(timezone.utc)<limite: eliminados.append(eid)
            except (ValueError,TypeError): continue
        db.execute('CREATE TABLE IF NOT EXISTS hermes_football_exclusions (event_id TEXT PRIMARY KEY, deleted_utc TEXT NOT NULL)')
        for eid in eliminados:
            db.execute('INSERT OR REPLACE INTO hermes_football_exclusions(event_id,deleted_utc) VALUES (?,?)',(eid,datetime.now(timezone.utc).isoformat()))
            db.execute('DELETE FROM football_links WHERE event_id=?',(eid,))
            db.execute('DELETE FROM events WHERE id=?',(eid,))
            db.execute('INSERT INTO hermes_card_audit(event_id,action,created_utc,note) VALUES(?,?,?,?)',
                       (eid,'PURGE',datetime.now(timezone.utc).isoformat(),'horas_gracia='+str(horas)))
    with sqlite3.connect(DB) as verify:
        restantes=sum(verify.execute('SELECT COUNT(*) FROM events WHERE id=?',(eid,)).fetchone()[0] for eid in eliminados)
    return {'estado':'PURGA_COMPLETADA' if not restantes else 'PURGA_INCOMPLETA','ok':not bool(restantes),'eliminados':len(eliminados)-restantes,
            'ids_eliminados':eliminados,'refrescar_panel':True}