Roundtable-plugins/snmp/routes.py

# plugins/snmp/routes.py
from __future__ import annotations
from datetime import datetime, timedelta, timezone

from quart import Blueprint, current_app, render_template, request
from sqlalchemy import func, select

from fabledscryer.auth.middleware import require_role
from fabledscryer.models.users import UserRole
from fabledscryer.models.metrics import PluginMetric

snmp_bp = Blueprint("snmp", __name__, template_folder="templates")


async def _latest_readings(db, device_names: list[str]) -> dict[str, dict[str, float]]:
    """Return {device_name: {label: latest_value}} for all configured devices."""
    if not device_names:
        return {}

    # Latest value per (resource_name, metric_name) pair
    subq = (
        select(
            PluginMetric.resource_name,
            PluginMetric.metric_name,
            func.max(PluginMetric.recorded_at).label("latest_at"),
        )
        .where(PluginMetric.source_module == "snmp")
        .where(PluginMetric.resource_name.in_(device_names))
        .group_by(PluginMetric.resource_name, PluginMetric.metric_name)
        .subquery()
    )
    result = await db.execute(
        select(PluginMetric).join(
            subq,
            (PluginMetric.resource_name == subq.c.resource_name)
            & (PluginMetric.metric_name == subq.c.metric_name)
            & (PluginMetric.recorded_at == subq.c.latest_at),
        ).where(PluginMetric.source_module == "snmp")
    )
    rows = result.scalars().all()

    out: dict[str, dict[str, float]] = {}
    for row in rows:
        out.setdefault(row.resource_name, {})[row.metric_name] = row.value
    return out


async def _history(db, device_name: str, hours: int = 24) -> dict[str, list]:
    """Return {label: [(recorded_at, value), ...]} for a single device."""
    since = datetime.now(timezone.utc) - timedelta(hours=hours)
    result = await db.execute(
        select(PluginMetric)
        .where(
            PluginMetric.source_module == "snmp",
            PluginMetric.resource_name == device_name,
            PluginMetric.recorded_at >= since,
        )
        .order_by(PluginMetric.recorded_at)
    )
    rows = result.scalars().all()

    out: dict[str, list] = {}
    for row in rows:
        out.setdefault(row.metric_name, []).append(
            (row.recorded_at.isoformat(), row.value)
        )
    return out


@snmp_bp.get("/")
@require_role(UserRole.viewer)
async def index():
    devices_cfg: list[dict] = current_app.config["PLUGINS"]["snmp"].get("devices", [])
    device_names = [d.get("name") or d.get("host", "?") for d in devices_cfg]

    async with current_app.db_sessionmaker() as db:
        latest = await _latest_readings(db, device_names)

    # Merge config + latest readings for the template
    devices = []
    for cfg in devices_cfg:
        name = cfg.get("name") or cfg.get("host", "?")
        devices.append({
            "name": name,
            "host": cfg.get("host", ""),
            "oids": cfg.get("oids", []),
            "readings": latest.get(name, {}),
        })

    poll_interval = current_app.config.get("MONITORS_POLL_INTERVAL", 60)
    return await render_template("snmp/index.html",
                                 devices=devices,
                                 poll_interval=poll_interval)


@snmp_bp.get("/widget")
@require_role(UserRole.viewer)
async def widget():
    devices_cfg: list[dict] = current_app.config["PLUGINS"]["snmp"].get("devices", [])
    device_names = [d.get("name") or d.get("host", "?") for d in devices_cfg]

    async with current_app.db_sessionmaker() as db:
        latest = await _latest_readings(db, device_names[:10])

    devices = []
    for cfg in devices_cfg[:10]:
        name = cfg.get("name") or cfg.get("host", "?")
        devices.append({
            "name": name,
            "oids": cfg.get("oids", []),
            "readings": latest.get(name, {}),
        })

    return await render_template("snmp/widget.html",
                                 devices=devices,
                                 total=len(devices_cfg))


@snmp_bp.get("/device/<device_name>")
@require_role(UserRole.viewer)
async def device_detail(device_name: str):
    devices_cfg: list[dict] = current_app.config["PLUGINS"]["snmp"].get("devices", [])
    device_cfg = next(
        (d for d in devices_cfg if (d.get("name") or d.get("host")) == device_name),
        None,
    )
    if device_cfg is None:
        from quart import abort
        abort(404)

    hours = int(request.args.get("hours", 24))
    async with current_app.db_sessionmaker() as db:
        hist = await _history(db, device_name, hours=hours)

    import json
    history_json = json.dumps(hist)
    oid_labels = [o.get("label") or o.get("oid") for o in device_cfg.get("oids", [])]

    return await render_template(
        "snmp/device.html",
        device=device_cfg,
        device_name=device_name,
        oid_labels=oid_labels,
        history_json=history_json,
        hours=hours,
    )