from fastapi import APIRouter, Depends, Query
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from core.database import get_session

router = APIRouter()

SWITCHES = {
    "sg-01": [
        {"switch_id": "sw-core-01", "name": "Core Switch — Hall A", "model": "Cisco Catalyst C9300-48P", "room_id": "hall-a", "rack_id": "SG1A01.01", "port_count": 48, "role": "core"},
        {"switch_id": "sw-core-02", "name": "Core Switch — Hall B", "model": "Arista 7050CX3-32S",       "room_id": "hall-b", "rack_id": "SG1B01.01", "port_count": 32, "role": "core"},
        {"switch_id": "sw-edge-01", "name": "Edge / Uplink Switch",  "model": "Juniper EX4300-48T",      "room_id": "hall-a", "rack_id": "SG1A01.05", "port_count": 48, "role": "edge"},
    ]
}

NET_FIELD_MAP = {
    "net_uptime_s":      "uptime_s",
    "net_active_ports":  "active_ports",
    "net_bw_in_mbps":    "bandwidth_in_mbps",
    "net_bw_out_mbps":   "bandwidth_out_mbps",
    "net_cpu_pct":       "cpu_pct",
    "net_mem_pct":       "mem_pct",
    "net_temp_c":        "temperature_c",
    "net_pkt_loss_pct":  "packet_loss_pct",
}

STATE_MAP = {0.0: "up", 1.0: "degraded", 2.0: "down"}


@router.get("/status")
async def network_status(
    site_id: str = Query(...),
    session: AsyncSession = Depends(get_session),
):
    """Latest reading for each network switch."""
    types_sql = ", ".join(f"'{t}'" for t in [*NET_FIELD_MAP.keys(), "net_state"])
    result = await session.execute(text(f"""
        SELECT DISTINCT ON (sensor_id)
            sensor_id, sensor_type, value
        FROM readings
        WHERE site_id     = :site_id
          AND sensor_type IN ({types_sql})
          AND recorded_at > NOW() - INTERVAL '5 minutes'
        ORDER BY sensor_id, recorded_at DESC
    """), {"site_id": site_id})

    sw_data: dict[str, dict] = {}
    for row in result.mappings().all():
        parts = row["sensor_id"].split("/")
        if len(parts) < 3:
            continue
        sw_id = parts[2]
        if sw_id not in sw_data:
            sw_data[sw_id] = {}
        field = NET_FIELD_MAP.get(row["sensor_type"])
        if field:
            sw_data[sw_id][field] = round(float(row["value"]), 2)
        elif row["sensor_type"] == "net_state":
            v = round(float(row["value"]))
            sw_data[sw_id]["state"] = STATE_MAP.get(v, "unknown")

    out = []
    for sw_cfg in SWITCHES.get(site_id, []):
        sw_id = sw_cfg["switch_id"]
        d = {**sw_cfg, **sw_data.get(sw_id, {})}
        if "state" not in d:
            d["state"] = "unknown"
        out.append(d)
    return out