"""Live network status board in the terminal (session 8).

python network_status.py            # refresh every 10 s
Shows, per node: last seen, readings in the last hour, expected vs received (delivery %), last values,
plus how many values were rejected as sensor errors (stored as NULL).
"""
import os
import sqlite3
import sys
import time
from datetime import datetime, timedelta

import config

EXPECTED_PER_HOUR = int(os.environ.get("SF_EXPECTED_PER_HOUR", "60"))   # node sends every 60 s


def board():
    con = sqlite3.connect(config.DB_PATH)
    con.row_factory = sqlite3.Row
    hour_ago = (datetime.now() - timedelta(hours=1)).isoformat(timespec="seconds")
    rows = con.execute("""
        SELECT n.node_id, n.last_seen,
               (SELECT COUNT(*) FROM readings r WHERE r.node_id = n.node_id AND r.ts >= ?) AS last_hour,
               (SELECT COUNT(*) FROM readings r WHERE r.node_id = n.node_id AND r.ts >= ?
                       AND (r.air_temp_c IS NULL OR r.soil_moisture_pct IS NULL)) AS with_nulls,
               (SELECT soil_moisture_pct FROM readings r WHERE r.node_id = n.node_id ORDER BY ts DESC LIMIT 1) AS soil,
               (SELECT air_temp_c FROM readings r WHERE r.node_id = n.node_id ORDER BY ts DESC LIMIT 1) AS temp
        FROM nodes n ORDER BY n.node_id""", (hour_ago, hour_ago)).fetchall()
    con.close()
    print(f"\nKFS Smart Farm - network status  {datetime.now():%Y-%m-%d %H:%M:%S}")
    print(f"{'node':15s} {'state':8s} {'last seen':>10s} {'1h msgs':>8s} {'delivery':>9s} {'nulls':>6s} {'soil%':>6s} {'temp':>6s}")
    for r in rows:
        if r["last_seen"]:
            age = (datetime.now() - datetime.fromisoformat(r["last_seen"])).total_seconds()
            seen = f"{int(age // 60)}m{int(age % 60):02d}s"
            state = "ONLINE" if age < config.NODE_OFFLINE_MIN * 60 else "OFFLINE"
        else:
            seen, state = "never", "UNKNOWN"
        pdr = min(100, r["last_hour"] / EXPECTED_PER_HOUR * 100)
        print(f"{r['node_id']:15s} {state:8s} {seen:>10s} {r['last_hour']:>8d} {pdr:>8.0f}% {r['with_nulls']:>6d} "
              f"{(r['soil'] if r['soil'] is not None else float('nan')):>6.1f} {(r['temp'] if r['temp'] is not None else float('nan')):>6.1f}")


if __name__ == "__main__":
    while True:
        board()
        if "--once" in sys.argv:
            break
        time.sleep(10)