"""MQTT -> SQLite bridge (sessions 7-8).

Nodes publish JSON to   kfs/farm/<node_id>/telemetry   on the local Mosquitto broker.
Run:  python mqtt_ingest.py --broker 192.168.1.10
Needs: pip install paho-mqtt>=2.0
"""
import argparse
import json
import logging
from datetime import datetime

import paho.mqtt.client as mqtt

import db

logging.basicConfig(filename="ingest.log", level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
FIELDS = ["air_temp_c", "air_humidity_pct", "soil_moisture_pct", "light", "battery_v", "rssi"]
LIMITS = {"air_temp_c": (-10, 60), "air_humidity_pct": (0, 100), "soil_moisture_pct": (0, 100)}
stats = {"ok": 0, "bad": 0}


def clean(payload):
    out = {}
    for f in FIELDS:
        v = payload.get(f)
        try:
            v = float(v) if v is not None else None
        except (TypeError, ValueError):
            v = None
        lo, hi = LIMITS.get(f, (-1e9, 1e9))
        out[f] = v if v is not None and lo <= v <= hi else None   # missing / -999 -> NULL
    return out


def on_connect(client, userdata, flags, reason_code, properties=None):
    print("connected:", reason_code)
    client.subscribe("kfs/farm/+/telemetry", qos=1)
    client.subscribe("kfs/farm/+/status", qos=1)


def on_message(client, userdata, msg):
    node_id = msg.topic.split("/")[2]
    try:
        payload = json.loads(msg.payload.decode())
    except ValueError:
        stats["bad"] += 1
        logging.warning("invalid JSON from %s: %r", node_id, msg.payload[:80])
        return
    if msg.topic.endswith("/status"):           # LWT: "offline" published by the broker if the node dies
        logging.info("status %s -> %s", node_id, payload)
        return
    ts = payload.get("ts") or datetime.now().isoformat(timespec="seconds")
    v = clean(payload)
    db.execute("INSERT INTO readings (node_id, ts, " + ", ".join(FIELDS) + ") VALUES (?,?,?,?,?,?,?,?)",
               (node_id, ts, *[v[f] for f in FIELDS]))
    db.execute("INSERT INTO nodes (node_id, name, zone, last_seen) VALUES (?,?,?,?) "
               "ON CONFLICT(node_id) DO UPDATE SET last_seen=excluded.last_seen", (node_id, node_id, "field", ts))
    stats["ok"] += 1
    if stats["ok"] % 20 == 0:
        print(f"stored {stats['ok']} readings ({stats['bad']} rejected)")


if __name__ == "__main__":
    ap = argparse.ArgumentParser()
    ap.add_argument("--broker", default="localhost")
    ap.add_argument("--port", type=int, default=1883)
    ap.add_argument("--user"); ap.add_argument("--password")
    a = ap.parse_args()
    db.init_db()
    client = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2, client_id="kfs-ingest")
    if a.user:
        client.username_pw_set(a.user, a.password)
    client.on_connect, client.on_message = on_connect, on_message
    client.connect(a.broker, a.port, keepalive=60)
    client.loop_forever()