"""
audit_service.py — Registro de auditoría de seguridad (MySQL, best-effort).

Fire-and-forget: nunca bloquea ni propaga excepciones — un fallo de auditoría
no debe romper la operación auditada.

Convención de `action` : "<categoría>.<evento>", ej. "auth.login",
"photo.rgpd_delete", "consent.given", "family.import", "sale.create".

Niveles de `severity`:
  info     — operaciones normales (login correcto, venta, importación)
  warning  — intentos fallidos, ediciones, bloqueos temporales
  critical — borrado permanente de fotos, fuerza bruta bloqueada

Multi-colegio: cada evento puede llevar `school_id`; el director solo ve los
de su colegio, el operador ve los del colegio seleccionado (o todos).
"""
import json
import logging
from typing import Optional

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

log = logging.getLogger("meeconic.audit")


def init_audit_db() -> None:
    with get_db() as conn:
        conn.execute(f"""
            CREATE TABLE IF NOT EXISTS audit_logs (
                id            BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                created_at    VARCHAR(40)  NOT NULL,
                user_id       VARCHAR(100),
                ip_address    VARCHAR(64),
                action        VARCHAR(80)  NOT NULL,
                resource_type VARCHAR(40),
                resource_id   VARCHAR(100),
                details_json  MEDIUMTEXT,
                severity      VARCHAR(20)  NOT NULL DEFAULT 'info',
                school_id     VARCHAR(64),
                KEY idx_audit_created (created_at),
                KEY idx_audit_action (action),
                KEY idx_audit_severity (severity),
                KEY idx_audit_school (school_id)
            ) {TABLE_OPTIONS}
        """)
        conn.commit()
    log.info("audit_logs table ready")


def log_event(
    action: str,
    *,
    user_id: Optional[str] = None,
    ip_address: Optional[str] = None,
    resource_type: Optional[str] = None,
    resource_id: Optional[str] = None,
    details: Optional[dict] = None,
    severity: str = "info",
    school_id: Optional[str] = None,
) -> None:
    """Persiste un événement d'audit. Ne lève jamais d'exception."""
    try:
        with get_db() as conn:
            conn.execute(
                """INSERT INTO audit_logs
                   (created_at, user_id, ip_address, action, resource_type, resource_id, details_json, severity, school_id)
                   VALUES (?,?,?,?,?,?,?,?,?)""",
                (
                    utcnow_iso(), user_id, ip_address, action, resource_type, resource_id,
                    json.dumps(details, ensure_ascii=False) if details else None,
                    severity, school_id,
                ),
            )
            conn.commit()
    except Exception as e:
        log.warning("Audit logging failed [%s]: %s", action, e)


def _row_to_dict(r) -> dict:
    d = dict(r)
    details_json = d.pop("details_json", None)
    d["details"] = json.loads(details_json) if details_json else None
    return d


def _where(action, severity, user_id, date_from, date_to, school_id) -> tuple[str, list]:
    q = " WHERE 1=1"
    params: list = []
    if action:
        q += " AND action LIKE ?"; params.append(f"{action}%")
    if severity:
        q += " AND severity = ?"; params.append(severity)
    if user_id:
        q += " AND user_id = ?"; params.append(user_id)
    if date_from:
        q += " AND created_at >= ?"; params.append(date_from)
    if date_to:
        q += " AND created_at <= ?"; params.append(date_to + "T23:59:59")
    if school_id:
        q += " AND school_id = ?"; params.append(school_id)
    return q, params


def list_events(
    *,
    action: Optional[str] = None,
    severity: Optional[str] = None,
    user_id: Optional[str] = None,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None,
    school_id: Optional[str] = None,
    limit: int = 200,
    offset: int = 0,
) -> list[dict]:
    where, params = _where(action, severity, user_id, date_from, date_to, school_id)
    q = "SELECT * FROM audit_logs" + where + " ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?"
    with get_db() as conn:
        rows = conn.execute(q, params + [limit, offset]).fetchall()
    return [_row_to_dict(r) for r in rows]


def count_events(
    *,
    action: Optional[str] = None,
    severity: Optional[str] = None,
    user_id: Optional[str] = None,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None,
    school_id: Optional[str] = None,
) -> int:
    where, params = _where(action, severity, user_id, date_from, date_to, school_id)
    with get_db() as conn:
        return conn.execute("SELECT COUNT(*) AS n FROM audit_logs" + where, params).fetchone()["n"]