"""
photographer_service.py — fotógrafos de un colegio y su "QR pegajoso".

Igual que en hec-photo-app, cada fotógrafo se identifica por el PREFIJO del nombre de archivo de su
cámara (p. ej. `DSC` → "xavier", `CAR` → "carlos"). Eso permite que dos fotógrafos trabajen a la vez
sobre el mismo FTP sin pisarse: cada uno tiene SU último QR escaneado ("sticky"), así que las fotos de
uno no acaban en la familia que está fotografiando el otro.

Sin fotógrafos configurados el organizer usa uno implícito ("photographer") que acepta cualquier prefijo:
una instalación con un solo fotógrafo funciona sin configurar nada.
"""
import logging
import re
from typing import Optional

from core.db import get_db, TABLE_OPTIONS
from core.timeutil import utcnow_iso

logger = logging.getLogger(__name__)

IMPLICIT_PHOTOGRAPHER = "photographer"
_PREFIX_RE = re.compile(r"^[A-Z0-9_]{1,12}$")
# El nombre es el de una CARPETA (photos/{colegio}/{nombre}/…): minúsculas, sin separadores.
_NAME_RE = re.compile(r"^[a-z0-9][a-z0-9_-]{0,39}$")


def init_photographer_table() -> None:
    with get_db() as conn:
        conn.execute(f"""
            CREATE TABLE IF NOT EXISTS photographers (
                school_id  VARCHAR(64) NOT NULL,
                prefix     VARCHAR(16) NOT NULL,
                name       VARCHAR(64) NOT NULL,
                last_qr    VARCHAR(64),
                created_at VARCHAR(40) NOT NULL,
                PRIMARY KEY (school_id, prefix)
            ) {TABLE_OPTIONS}""")
        conn.commit()


def get_photographer_map(school_id: str) -> dict[str, str]:
    with get_db() as conn:
        rows = conn.execute("SELECT prefix, name FROM photographers WHERE school_id=?", (school_id,)).fetchall()
    return {r["prefix"]: r["name"] for r in rows}


def list_photographers(school_id: str) -> list[dict]:
    with get_db() as conn:
        rows = conn.execute("SELECT prefix, name, last_qr FROM photographers WHERE school_id=? ORDER BY name, prefix",
                            (school_id,)).fetchall()
    return [dict(r) for r in rows]


def update_photographer_map(school_id: str, new_map: dict[str, str]) -> dict[str, str]:
    """Sustituye el mapa del colegio (prefijo → nombre). Valida antes de tocar nada y conserva el
    último QR de los fotógrafos que siguen existiendo. ValueError si algún dato no es válido."""
    normalized: dict[str, str] = {}
    for k, v in new_map.items():
        prefix, name = str(k).strip().upper(), str(v).strip().lower()
        if not prefix and not name:
            continue
        if not _PREFIX_RE.match(prefix):
            raise ValueError(f"Invalid prefix: {k!r} (1-12 letters, digits or _)")
        if not _NAME_RE.match(name):
            raise ValueError(f"Invalid name: {v!r} (lowercase, digits, - or _; it is the folder name)")
        if prefix in normalized:
            raise ValueError(f"Prefijo repetido: {prefix}")
        normalized[prefix] = name
    now = utcnow_iso()
    with get_db() as conn:
        previous = {r["name"]: r["last_qr"] for r in conn.execute(
            "SELECT name, last_qr FROM photographers WHERE school_id=? AND last_qr IS NOT NULL", (school_id,))}
        conn.execute("DELETE FROM photographers WHERE school_id=?", (school_id,))
        for prefix, name in normalized.items():
            conn.execute("INSERT INTO photographers (school_id, prefix, name, last_qr, created_at) VALUES (?,?,?,?,?)",
                         (school_id, prefix, name, previous.get(name), now))
        conn.commit()
    logger.info("[%s] Mapa de fotógrafos actualizado: %s", school_id, normalized)
    return normalized


def resolve_photographer(school_id: str, filename: str) -> Optional[str]:
    """Fotógrafo de un archivo por el prefijo de su nombre (el más largo gana). None = prefijo desconocido.
    Sin fotógrafos configurados, uno implícito acepta todo."""
    pmap = get_photographer_map(school_id)
    if not pmap:
        return IMPLICIT_PHOTOGRAPHER
    upper = filename.upper()
    for prefix in sorted(pmap, key=len, reverse=True):
        if upper.startswith(prefix):
            return pmap[prefix]
    logger.warning("[%s] Prefijo no reconocido para %r (prefijos: %s)", school_id, filename, sorted(pmap))
    return None


def get_sticky_qr(school_id: str, name: str) -> str:
    with get_db() as conn:
        if name == IMPLICIT_PHOTOGRAPHER and not get_photographer_map(school_id):
            row = conn.execute("SELECT value FROM app_kv WHERE key=?", (f"sticky:{school_id}:{name}",)).fetchone()
            return (row["value"] if row else None) or "_no_qr"
        row = conn.execute("SELECT last_qr FROM photographers WHERE school_id=? AND name=? AND last_qr IS NOT NULL LIMIT 1",
                           (school_id, name)).fetchone()
    return row["last_qr"] if row else "_no_qr"


def set_sticky_qr(school_id: str, name: str, code: str) -> None:
    """Persiste el último QR para TODOS los prefijos de ese fotógrafo (sobrevive a reinicios)."""
    with get_db() as conn:
        if name == IMPLICIT_PHOTOGRAPHER and not get_photographer_map(school_id):
            conn.execute("INSERT OR REPLACE INTO app_kv (key, value) VALUES (?,?)", (f"sticky:{school_id}:{name}", code))
        else:
            conn.execute("UPDATE photographers SET last_qr=? WHERE school_id=? AND name=?", (code, school_id, name))
        conn.commit()