229 lines
8.6 KiB
Python
229 lines
8.6 KiB
Python
from datetime import datetime, timezone, timedelta
|
|
from fastapi import APIRouter, Depends, Query
|
|
from sqlalchemy import text
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
from core.database import get_session
|
|
|
|
router = APIRouter()
|
|
|
|
|
|
@router.get("/latest")
|
|
async def get_latest_readings(
|
|
site_id: str = Query(...),
|
|
session: AsyncSession = Depends(get_session),
|
|
):
|
|
"""Most recent reading per sensor for a site (last 10 minutes)."""
|
|
result = await session.execute(text("""
|
|
SELECT DISTINCT ON (sensor_id)
|
|
sensor_id, sensor_type, site_id, room_id, rack_id, value, unit, recorded_at
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND recorded_at > NOW() - INTERVAL '10 minutes'
|
|
ORDER BY sensor_id, recorded_at DESC
|
|
"""), {"site_id": site_id})
|
|
return [dict(r) for r in result.mappings().all()]
|
|
|
|
|
|
@router.get("/kpis")
|
|
async def get_site_kpis(
|
|
site_id: str = Query(...),
|
|
session: AsyncSession = Depends(get_session),
|
|
):
|
|
"""Aggregate KPIs for the overview dashboard."""
|
|
power = await session.execute(text("""
|
|
SELECT COALESCE(SUM(value), 0) AS total_power_kw
|
|
FROM (
|
|
SELECT DISTINCT ON (sensor_id) sensor_id, value
|
|
FROM readings
|
|
WHERE site_id = :site_id AND sensor_type = 'power_kw'
|
|
AND recorded_at > NOW() - INTERVAL '5 minutes'
|
|
ORDER BY sensor_id, recorded_at DESC
|
|
) latest
|
|
"""), {"site_id": site_id})
|
|
|
|
temp = await session.execute(text("""
|
|
SELECT COALESCE(AVG(value), 0) AS avg_temp
|
|
FROM (
|
|
SELECT DISTINCT ON (sensor_id) sensor_id, value
|
|
FROM readings
|
|
WHERE site_id = :site_id AND sensor_type = 'temperature'
|
|
AND recorded_at > NOW() - INTERVAL '5 minutes'
|
|
ORDER BY sensor_id, recorded_at DESC
|
|
) latest
|
|
"""), {"site_id": site_id})
|
|
|
|
alarms = await session.execute(text("""
|
|
SELECT COUNT(*) AS alarm_count
|
|
FROM alarms
|
|
WHERE site_id = :site_id AND state = 'active'
|
|
"""), {"site_id": site_id})
|
|
|
|
total_kw = float(power.mappings().one()["total_power_kw"])
|
|
avg_temp = float(temp.mappings().one()["avg_temp"])
|
|
alarm_cnt = int(alarms.mappings().one()["alarm_count"])
|
|
pue = round(total_kw / (total_kw * 0.87), 2) if total_kw > 0 else 0.0
|
|
|
|
return {
|
|
"total_power_kw": round(total_kw, 1),
|
|
"pue": pue,
|
|
"avg_temperature": round(avg_temp, 1),
|
|
"active_alarms": alarm_cnt,
|
|
}
|
|
|
|
|
|
@router.get("/site-power-history")
|
|
async def get_site_power_history(
|
|
site_id: str = Query(...),
|
|
hours: int = Query(1, ge=1, le=24),
|
|
session: AsyncSession = Depends(get_session),
|
|
):
|
|
"""Total power (kW) bucketed by 5 minutes — for the power trend chart."""
|
|
from_time = datetime.now(timezone.utc) - timedelta(hours=hours)
|
|
try:
|
|
result = await session.execute(text("""
|
|
SELECT bucket, ROUND(SUM(avg_per_sensor)::numeric, 1) AS total_kw
|
|
FROM (
|
|
SELECT
|
|
time_bucket('5 minutes', recorded_at) AS bucket,
|
|
sensor_id,
|
|
AVG(value) AS avg_per_sensor
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'power_kw'
|
|
AND recorded_at > :from_time
|
|
GROUP BY bucket, sensor_id
|
|
) per_sensor
|
|
GROUP BY bucket
|
|
ORDER BY bucket ASC
|
|
"""), {"site_id": site_id, "from_time": from_time})
|
|
except Exception:
|
|
result = await session.execute(text("""
|
|
SELECT bucket, ROUND(SUM(avg_per_sensor)::numeric, 1) AS total_kw
|
|
FROM (
|
|
SELECT
|
|
date_trunc('minute', recorded_at) AS bucket,
|
|
sensor_id,
|
|
AVG(value) AS avg_per_sensor
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'power_kw'
|
|
AND recorded_at > :from_time
|
|
GROUP BY bucket, sensor_id
|
|
) per_sensor
|
|
GROUP BY bucket
|
|
ORDER BY bucket ASC
|
|
"""), {"site_id": site_id, "from_time": from_time})
|
|
return [dict(r) for r in result.mappings().all()]
|
|
|
|
|
|
@router.get("/room-temp-history")
|
|
async def get_room_temp_history(
|
|
site_id: str = Query(...),
|
|
hours: int = Query(1, ge=1, le=24),
|
|
session: AsyncSession = Depends(get_session),
|
|
):
|
|
"""Average temperature per room bucketed by 5 minutes — for the temp trend chart."""
|
|
from_time = datetime.now(timezone.utc) - timedelta(hours=hours)
|
|
try:
|
|
result = await session.execute(text("""
|
|
SELECT bucket, room_id, ROUND(AVG(avg_per_rack)::numeric, 2) AS avg_temp
|
|
FROM (
|
|
SELECT
|
|
time_bucket('5 minutes', recorded_at) AS bucket,
|
|
sensor_id, room_id,
|
|
AVG(value) AS avg_per_rack
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'temperature'
|
|
AND room_id IS NOT NULL
|
|
AND recorded_at > :from_time
|
|
GROUP BY bucket, sensor_id, room_id
|
|
) per_rack
|
|
GROUP BY bucket, room_id
|
|
ORDER BY bucket ASC
|
|
"""), {"site_id": site_id, "from_time": from_time})
|
|
except Exception:
|
|
result = await session.execute(text("""
|
|
SELECT bucket, room_id, ROUND(AVG(avg_per_rack)::numeric, 2) AS avg_temp
|
|
FROM (
|
|
SELECT
|
|
date_trunc('minute', recorded_at) AS bucket,
|
|
sensor_id, room_id,
|
|
AVG(value) AS avg_per_rack
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'temperature'
|
|
AND room_id IS NOT NULL
|
|
AND recorded_at > :from_time
|
|
GROUP BY bucket, sensor_id, room_id
|
|
) per_rack
|
|
GROUP BY bucket, room_id
|
|
ORDER BY bucket ASC
|
|
"""), {"site_id": site_id, "from_time": from_time})
|
|
return [dict(r) for r in result.mappings().all()]
|
|
|
|
|
|
@router.get("/room-status")
|
|
async def get_room_status(
|
|
site_id: str = Query(...),
|
|
session: AsyncSession = Depends(get_session),
|
|
):
|
|
"""Current per-room summary: avg temp, total power, rack count, alarm count."""
|
|
temp = await session.execute(text("""
|
|
SELECT room_id, ROUND(AVG(value)::numeric, 1) AS avg_temp
|
|
FROM (
|
|
SELECT DISTINCT ON (sensor_id) sensor_id, room_id, value
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'temperature'
|
|
AND room_id IS NOT NULL
|
|
AND recorded_at > NOW() - INTERVAL '10 minutes'
|
|
ORDER BY sensor_id, recorded_at DESC
|
|
) latest
|
|
GROUP BY room_id
|
|
"""), {"site_id": site_id})
|
|
|
|
power = await session.execute(text("""
|
|
SELECT room_id, ROUND(SUM(value)::numeric, 1) AS total_kw
|
|
FROM (
|
|
SELECT DISTINCT ON (sensor_id) sensor_id, room_id, value
|
|
FROM readings
|
|
WHERE site_id = :site_id
|
|
AND sensor_type = 'power_kw'
|
|
AND room_id IS NOT NULL
|
|
AND recorded_at > NOW() - INTERVAL '10 minutes'
|
|
ORDER BY sensor_id, recorded_at DESC
|
|
) latest
|
|
GROUP BY room_id
|
|
"""), {"site_id": site_id})
|
|
|
|
alarm_counts = await session.execute(text("""
|
|
SELECT room_id, COUNT(*) AS alarm_count, MAX(severity) AS worst_severity
|
|
FROM alarms
|
|
WHERE site_id = :site_id AND state = 'active' AND room_id IS NOT NULL
|
|
GROUP BY room_id
|
|
"""), {"site_id": site_id})
|
|
|
|
temp_map = {r["room_id"]: float(r["avg_temp"]) for r in temp.mappings().all()}
|
|
power_map = {r["room_id"]: float(r["total_kw"]) for r in power.mappings().all()}
|
|
alarm_map = {r["room_id"]: (int(r["alarm_count"]), r["worst_severity"])
|
|
for r in alarm_counts.mappings().all()}
|
|
|
|
rooms = sorted(set(list(temp_map.keys()) + list(power_map.keys())))
|
|
result = []
|
|
for room_id in rooms:
|
|
avg_temp = temp_map.get(room_id, 0.0)
|
|
alarm_cnt, ws = alarm_map.get(room_id, (0, None))
|
|
status = "ok"
|
|
if ws == "critical" or avg_temp >= 30:
|
|
status = "critical"
|
|
elif ws == "warning" or avg_temp >= 26:
|
|
status = "warning"
|
|
result.append({
|
|
"room_id": room_id,
|
|
"avg_temp": avg_temp,
|
|
"total_kw": power_map.get(room_id, 0.0),
|
|
"alarm_count": alarm_cnt,
|
|
"status": status,
|
|
})
|
|
return result
|