"""Hermes Football V3: almacenamiento aislado de asignaciones, Python 3.8.
No descarga streams ni altera las otras ligas. La publicación requiere aprobación explícita.
"""
import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from urllib.parse import urlsplit

DB = '/var/lib/hermes-nexus/football.db'

def connect(db_path=DB):
    db = sqlite3.connect(db_path, timeout=20)
    db.row_factory = sqlite3.Row
    db.execute('PRAGMA busy_timeout=20000')
    return db

def init(db_path=DB):
    with connect(db_path) as db:
        db.execute('''CREATE TABLE IF NOT EXISTS football_links (
            event_id TEXT PRIMARY KEY,
            automatic_url TEXT NOT NULL DEFAULT '',
            manual_url TEXT NOT NULL DEFAULT '',
            provider TEXT NOT NULL DEFAULT '',
            epg_channel TEXT NOT NULL DEFAULT '',
            evidence TEXT NOT NULL DEFAULT '',
            updated_utc TEXT NOT NULL DEFAULT '',
            FOREIGN KEY(event_id) REFERENCES events(id))''')
        db.execute('''CREATE TABLE IF NOT EXISTS football_link_audit (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id TEXT NOT NULL,
            action TEXT NOT NULL,
            timestamp_utc TEXT NOT NULL,
            note TEXT NOT NULL DEFAULT '')''')

def _validate_url(value):
    if not value:
        return ''
    if len(value) > 4096 or any(c in value for c in '\r\n\x00'):
        raise ValueError('URL inválida')
    parsed = urlsplit(value)
    if parsed.scheme not in ('http', 'https') or not parsed.hostname or parsed.username or parsed.password:
        raise ValueError('Se requiere URL HTTP(S) sin credenciales incrustadas')
    return value

def _exists(db, event_id):
    if not db.execute('SELECT 1 FROM events WHERE id=?', (event_id,)).fetchone():
        raise ValueError('El evento no existe')

def _stamp():
    return datetime.now(timezone.utc).isoformat()

def set_manual(event_id, url, db_path=DB):
    url = _validate_url(url.strip())
    init(db_path)
    with connect(db_path) as db:
        _exists(db, event_id)
        db.execute('''INSERT INTO football_links(event_id,manual_url,updated_utc)
          VALUES(?,?,?) ON CONFLICT(event_id) DO UPDATE SET
          manual_url=excluded.manual_url,updated_utc=excluded.updated_utc''', (event_id,url,_stamp()))
        db.execute('INSERT INTO football_link_audit(event_id,action,timestamp_utc) VALUES(?,?,?)',
                   (event_id, 'MANUAL_SET' if url else 'MANUAL_CLEAR', _stamp()))
    return {'event_id':event_id,'manual':bool(url),'saved':True}

def set_automatic(event_id, url, epg_channel, evidence, db_path=DB):
    url = _validate_url(url.strip())
    if not url: raise ValueError('URL automática vacía')
    if not isinstance(evidence, dict) or evidence.get('estado') != 'CATALOGO_UNICO':
        raise ValueError('La coincidencia no está verificada en catálogo')
    if evidence.get('epg_canal') != epg_channel or abs(float(evidence.get('diferencia_minutos',999)))>60:
        raise ValueError('Evidencia de EPG u horario insuficiente')
    init(db_path)
    with connect(db_path) as db:
        _exists(db,event_id)
        row=db.execute('SELECT manual_url FROM football_links WHERE event_id=?',(event_id,)).fetchone()
        if row and row['manual_url']:
            return {'event_id':event_id,'saved':False,'reason':'MANUAL_PRIORITY'}
        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''',
          (event_id,url,'sling',epg_channel,json.dumps(evidence,ensure_ascii=False),_stamp()))
        db.execute('INSERT INTO football_link_audit(event_id,action,timestamp_utc,note) VALUES(?,?,?,?)',
                   (event_id,'AUTO_SET',_stamp(),epg_channel))
    return {'event_id':event_id,'saved':True}

def get_links(event_id, db_path=DB):
    init(db_path)
    with connect(db_path) as db:
        _exists(db,event_id)
        row=db.execute('SELECT * FROM football_links WHERE event_id=?',(event_id,)).fetchone()
        if not row:return {'event_id':event_id,'assigned':False,'source':'none'}
        url=row['manual_url'] or row['automatic_url']
        return {'event_id':event_id,'assigned':bool(url),
                'source':'manual' if row['manual_url'] else 'sling' if row['automatic_url'] else 'none',
                'epg_channel':row['epg_channel']}

def get_target(event_id, db_path=DB):
    """Uso exclusivo del backend autenticado: nunca devolver este valor al cliente sin control de acceso."""
    init(db_path)
    with connect(db_path) as db:
        _exists(db,event_id)
        row=db.execute('SELECT manual_url,automatic_url FROM football_links WHERE event_id=?',(event_id,)).fetchone()
        return (row['manual_url'] or row['automatic_url']) if row else ''

if __name__=='__main__':
    import argparse
    p=argparse.ArgumentParser()
    p.add_argument('--init',action='store_true')
    p.add_argument('--db',default=DB)
    a=p.parse_args()
    if not a.init:p.error('Usa --init para crear tablas (tras respaldo)')
    init(a.db)
    print('Tablas football_links y football_link_audit listas; eventos intactos.')