import json
import os
import time
import uuid
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Callable

import redis
from sqlalchemy import text

from app.core.config import get_settings
from app.core.db import db_session
from app.services.observability import set_component, set_pending
from app.tasks.celery_app import celery_app


APP_VERSION = "1.20.0"
HEARTBEAT_KEY = "documenta:h12:worker-scheduler"


def _control(code: str, category: str, component: str, callback: Callable[[], tuple[str, str, dict[str, Any] | None]]) -> dict[str, Any]:
    started = time.monotonic()
    try:
        status, detail, evidence = callback()
    except Exception as exc:
        status, detail, evidence = "ERROR", f"{type(exc).__name__}: control no disponible", None
    return {
        "codigo": code,
        "categoria": category,
        "componente": component,
        "estado": status,
        "detalle": detail[:700],
        "evidencia": evidence,
        "duracion_ms": max(0, round((time.monotonic() - started) * 1000)),
    }


def _database_check() -> tuple[str, str, dict[str, Any]]:
    with db_session() as db:
        row = db.execute(text("""
          SELECT DATABASE() base,
                 (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE()) tablas,
                 (SELECT COUNT(*) FROM permisos WHERE codigo='operacion.monitoreo') permiso_h12
        """)).mappings().one()
    ok = int(row["tablas"]) > 0 and int(row["permiso_h12"]) == 1
    return ("OK" if ok else "ERROR", "Base de datos operativa y esquema H12 disponible." if ok else "El esquema H12 está incompleto.", dict(row))


def _redis_client():
    return redis.Redis.from_url(get_settings().redis_url, socket_connect_timeout=2, socket_timeout=2)


def _redis_check() -> tuple[str, str, dict[str, Any]]:
    client = _redis_client()
    ok = bool(client.ping())
    return ("OK" if ok else "ERROR", "Redis respondió correctamente." if ok else "Redis no respondió.", None)


def _heartbeat_check() -> tuple[str, str, dict[str, Any] | None]:
    raw = _redis_client().get(HEARTBEAT_KEY)
    if not raw:
        return "ERROR", "No existe heartbeat reciente del ciclo worker/scheduler.", None
    parsed = datetime.fromisoformat(raw.decode().replace("Z", "+00:00"))
    age = max(0, int((datetime.now(timezone.utc) - parsed.astimezone(timezone.utc)).total_seconds()))
    if age > 180:
        return "ERROR", f"El heartbeat worker/scheduler tiene {age} segundos.", {"antiguedad_segundos": age}
    if age > 120:
        return "ADVERTENCIA", f"El heartbeat worker/scheduler tiene {age} segundos.", {"antiguedad_segundos": age}
    return "OK", f"Worker y scheduler confirmaron actividad hace {age} segundos.", {"antiguedad_segundos": age}


def _worker_check() -> tuple[str, str, dict[str, Any] | None]:
    replies = celery_app.control.inspect(timeout=1.5).ping() or {}
    workers = sorted(replies)
    return ("OK" if workers else "ERROR",
            f"{len(workers)} worker(s) respondieron al ping." if workers else "Ningún worker respondió al ping.",
            {"workers": workers} if workers else None)


def _storage_check() -> tuple[str, str, dict[str, Any]]:
    path = Path(get_settings().storage_path)
    readable = path.is_dir() and os.access(path, os.R_OK | os.X_OK)
    writable = path.is_dir() and os.access(path, os.W_OK | os.X_OK)
    status = "OK" if readable and writable else "ERROR"
    return status, "Almacenamiento privado legible y escribible." if status == "OK" else "El almacenamiento privado no posee acceso completo.", {"legible": readable, "escribible": writable}


def _backup_check() -> tuple[str, str, dict[str, Any] | None]:
    marker = Path(get_settings().storage_path) / "operations" / "last-backup.json"
    if not marker.is_file():
        return "ADVERTENCIA", "Todavía no existe evidencia H12 de respaldo validado.", None
    try:
        payload = json.loads(marker.read_text(encoding="utf-8"))
        created = datetime.fromisoformat(str(payload["created_at"]).replace("Z", "+00:00"))
        age_hours = max(0, round((datetime.now(timezone.utc) - created.astimezone(timezone.utc)).total_seconds() / 3600, 1))
    except (OSError, ValueError, KeyError, json.JSONDecodeError):
        return "ERROR", "La evidencia del último respaldo no es válida.", None
    status = "OK" if age_hours <= 24 else "ADVERTENCIA" if age_hours <= 72 else "ERROR"
    return status, f"Último respaldo validado hace {age_hours} horas.", {"antiguedad_horas": age_hours, "restore_tested": bool(payload.get("restore_tested"))}


def _queue_check() -> tuple[str, str, dict[str, Any]]:
    with db_session() as db:
        row = db.execute(text("""
          SELECT
            (SELECT COUNT(*) FROM eventos_outbox WHERE procesado_en IS NULL) outbox_pendiente,
            (SELECT COUNT(*) FROM eventos_outbox WHERE procesado_en IS NULL AND intentos>0) outbox_error,
            (SELECT COUNT(*) FROM organizacion_webhook_entregas WHERE estado IN ('PENDIENTE','ERROR')) webhooks_pendientes,
            (SELECT COUNT(*) FROM organizacion_webhook_entregas WHERE estado='AGOTADO') webhooks_agotados
        """)).mappings().one()
    evidence = {key: int(value or 0) for key, value in row.items()}
    set_pending("outbox", evidence["outbox_pendiente"])
    set_pending("webhooks", evidence["webhooks_pendientes"])
    critical = evidence["webhooks_agotados"] > 0 or evidence["outbox_error"] > 25
    warning = evidence["outbox_error"] > 0 or evidence["webhooks_pendientes"] > 25
    status = "ERROR" if critical else "ADVERTENCIA" if warning else "OK"
    return status, "Colas dentro de umbrales operativos." if status == "OK" else "Existen entregas pendientes o con error que requieren revisión.", evidence


def _connector_check() -> tuple[str, str, dict[str, Any]]:
    with db_session() as db:
        rows = db.execute(text("""
          SELECT pi.codigo proveedor,COUNT(*) intentos,
                 SUM(ei.codigo='EXITOSA') exitosos,
                 SUM(ei.codigo IN ('FALLIDA','REINTENTABLE','DESCONOCIDA')) fallidos
          FROM intentos_integracion ii
          JOIN proveedores_integracion pi ON pi.id=ii.proveedor_integracion_id
          JOIN estados_integracion ei ON ei.id=ii.estado_integracion_id
          WHERE ii.iniciado_en>DATE_SUB(UTC_TIMESTAMP(6),INTERVAL 24 HOUR)
          GROUP BY pi.id,pi.codigo ORDER BY pi.codigo
        """)).mappings().all()
        verification = db.execute(text("""
          SELECT COUNT(*) total,SUM(resultado='ERROR') errores
          FROM verificacion_intentos WHERE creado_en>DATE_SUB(UTC_TIMESTAMP(6),INTERVAL 24 HOUR)
        """)).mappings().one()
    items = [{key: int(value or 0) if key != "proveedor" else value for key, value in row.items()} for row in rows]
    failed = sum(item["fallidos"] for item in items)
    total = sum(item["intentos"] for item in items)
    ratio = failed / total if total else 0
    status = "ERROR" if total >= 5 and ratio >= .5 else "ADVERTENCIA" if failed else "OK"
    return status, "Integraciones sin fallas recientes." if status == "OK" else "Se detectaron fallas de integración durante las últimas 24 horas.", {
        "proveedores": items,
        "verificacion": {key: int(value or 0) for key, value in verification.items()},
    }


def collect_controls() -> list[dict[str, Any]]:
    controls = [
        _control("DB_SCHEMA", "PLATAFORMA", "MariaDB", _database_check),
        _control("REDIS_PING", "PLATAFORMA", "Redis", _redis_check),
        _control("WORKER_PING", "PROCESAMIENTO", "Worker", _worker_check),
        _control("SCHEDULER_HEARTBEAT", "PROCESAMIENTO", "Worker y scheduler", _heartbeat_check),
        _control("PRIVATE_STORAGE", "DOCUMENTOS", "Storage privado", _storage_check),
        _control("QUEUES", "PROCESAMIENTO", "Outbox y webhooks", _queue_check),
        _control("CONNECTORS_24H", "INTEGRACIONES", "Conectores", _connector_check),
        _control("BACKUP_RECENCY", "RECUPERACION", "Respaldos", _backup_check),
    ]
    for item in controls:
        set_component(item["codigo"].lower(), item["estado"] == "OK")
    return controls


def _global_status(controls: list[dict[str, Any]]) -> str:
    if any(item["estado"] == "ERROR" for item in controls):
        return "FALLIDA"
    if any(item["estado"] == "ADVERTENCIA" for item in controls):
        return "ADVERTENCIA"
    return "APROBADA"


def run_operational_checks(actor_user_id: int | None, run_type: str = "PANEL") -> dict[str, Any]:
    public_uuid = uuid.uuid4()
    with db_session() as db:
        execution_id = db.execute(text("""
          INSERT INTO operacion_ejecuciones(uuid,tipo,version_aplicacion,actor_usuario_id)
          VALUES (:uuid,:type,:version,:actor)
        """), {"uuid": public_uuid.bytes, "type": run_type, "version": APP_VERSION, "actor": actor_user_id}).lastrowid
    controls = collect_controls()
    status = _global_status(controls)
    counts = {name: sum(item["estado"] == state for item in controls) for name, state in (("ok", "OK"), ("warnings", "ADVERTENCIA"), ("errors", "ERROR"))}
    summary = f"{counts['ok']} controles OK, {counts['warnings']} advertencia(s), {counts['errors']} error(es)."
    with db_session() as db:
        for item in controls:
            db.execute(text("""
              INSERT INTO operacion_controles
                (ejecucion_id,codigo,categoria,componente,estado,duracion_ms,detalle,evidencia_json)
              VALUES (:run,:code,:category,:component,:status,:duration,:detail,:evidence)
            """), {"run": execution_id, "code": item["codigo"], "category": item["categoria"],
                    "component": item["componente"], "status": item["estado"],
                    "duration": item["duracion_ms"], "detail": item["detalle"],
                    "evidence": json.dumps(item["evidencia"], ensure_ascii=False, default=str) if item["evidencia"] is not None else None})
            alert_code = f"H12_{item['codigo']}"
            if item["estado"] == "OK":
                db.execute(text("""
                  UPDATE operacion_alertas SET estado='RESUELTA',resuelta_en=UTC_TIMESTAMP(6),
                    ultima_ejecucion_id=:run WHERE codigo=:code AND estado='ABIERTA'
                """), {"run": execution_id, "code": alert_code})
            else:
                severity = "CRITICA" if item["estado"] == "ERROR" else "ADVERTENCIA"
                db.execute(text("""
                  INSERT INTO operacion_alertas
                    (codigo,componente,severidad,estado,resumen,ultima_ejecucion_id)
                  VALUES (:code,:component,:severity,'ABIERTA',:summary,:run)
                  ON DUPLICATE KEY UPDATE componente=VALUES(componente),severidad=VALUES(severidad),
                    estado='ABIERTA',resumen=VALUES(resumen),ocurrencias=ocurrencias+1,
                    ultima_deteccion_en=UTC_TIMESTAMP(6),resuelta_en=NULL,ultima_ejecucion_id=VALUES(ultima_ejecucion_id)
                """), {"code": alert_code, "component": item["componente"], "severity": severity,
                        "summary": item["detalle"], "run": execution_id})
        db.execute(text("""
          UPDATE operacion_ejecuciones SET estado=:status,resumen=:summary,
            controles_total=:total,controles_ok=:ok,controles_advertencia=:warnings,
            controles_error=:errors,finalizada_en=UTC_TIMESTAMP(6) WHERE id=:id
        """), {"status": status, "summary": summary, "total": len(controls), "ok": counts["ok"],
                "warnings": counts["warnings"], "errors": counts["errors"], "id": execution_id})
    return {"uuid": str(public_uuid), "estado": status, "resumen": summary, "controles": controls}


def readiness() -> tuple[bool, dict[str, Any]]:
    checks = []
    for name, callback in (("database", _database_check), ("redis", _redis_check), ("storage", _storage_check)):
        result = _control(name.upper(), "READINESS", name, callback)
        checks.append({"componente": name, "estado": result["estado"], "duracion_ms": result["duracion_ms"]})
    return all(item["estado"] == "OK" for item in checks), {"version": APP_VERSION, "controles": checks}


def operations_overview() -> dict[str, Any]:
    controls = collect_controls()
    with db_session() as db:
        runs = db.execute(text("""
          SELECT LOWER(CONCAT(SUBSTR(HEX(uuid),1,8),'-',SUBSTR(HEX(uuid),9,4),'-',
                 SUBSTR(HEX(uuid),13,4),'-',SUBSTR(HEX(uuid),17,4),'-',SUBSTR(HEX(uuid),21))) uuid,
                 tipo,version_aplicacion,estado,resumen,controles_total,controles_ok,
                 controles_advertencia,controles_error,iniciada_en,finalizada_en
          FROM operacion_ejecuciones ORDER BY id DESC LIMIT 30
        """)).mappings().all()
        alerts = db.execute(text("""
          SELECT codigo,componente,severidad,estado,resumen,ocurrencias,
                 primera_deteccion_en,ultima_deteccion_en,resuelta_en
          FROM operacion_alertas ORDER BY (estado='ABIERTA') DESC,ultima_deteccion_en DESC LIMIT 50
        """)).mappings().all()
    return {
        "version": APP_VERSION,
        "estado": _global_status(controls),
        "generado_en": datetime.now(timezone.utc),
        "controles": controls,
        "alertas": [dict(row) for row in alerts],
        "ejecuciones": [dict(row) for row in runs],
        "dominios": [
            {"host": "documenta.cl", "rol": "Portal público"},
            {"host": "tramites.documenta.cl", "rol": "Trámites y pagos"},
            {"host": "personas.documenta.cl", "rol": "Portal personas"},
            {"host": "pro.documenta.cl", "rol": "Portal empresas"},
            {"host": "notaria.documenta.cl", "rol": "Workspace notarías"},
            {"host": "cbr.documenta.cl", "rol": "Workspace conservadores"},
            {"host": "archivero.documenta.cl", "rol": "Workspace archiveros"},
        ],
    }