#!/usr/bin/env python3
import os, json, ssl, sqlite3, time
from datetime import datetime
import paho.mqtt.client as mqtt

HOST  = os.environ.get("MQTT_HOST", "localhost")
PORT  = int(os.environ.get("MQTT_PORT", "1883"))
USER  = os.environ.get("MQTT_USER")
PW    = os.environ.get("MQTT_PASS")
TOPIC = os.environ.get("MQTT_TOPIC", "eeg/greenlama/+/live")
DB    = os.environ.get("DB_PATH", "live.db")

db = sqlite3.connect(DB, isolation_level=None, check_same_thread=False)
db.execute("PRAGMA journal_mode=WAL;")
db.executescript("""
CREATE TABLE IF NOT EXISTS live(
  mitglied TEXT PRIMARY KEY,
  t0q      TEXT,
  ts       TEXT,
  e_wh     REAL,
  updated  INTEGER
);
CREATE TABLE IF NOT EXISTS intervalle(
  mitglied   TEXT,
  t0q        TEXT,
  e_wh_final REAL,
  ts_final   TEXT,
  PRIMARY KEY (mitglied, t0q)
);
""")

def floor_q(dt):
    return dt.replace(minute=(dt.minute // 15) * 15, second=0, microsecond=0)

def on_connect(c, u, f, rc, props=None):
    print(f"[ingest] connected rc={rc}", flush=True)
    c.subscribe(TOPIC, qos=1)

def on_message(c, u, msg):
    try:
        d = json.loads(msg.payload.decode("utf-8"))
    except Exception:
        return
    p = msg.topic.split("/")
    if len(p) < 3:
        return
    mid = p[2]
    try:
        e   = float(d["e_wh"])
        t0  = datetime.fromisoformat(d["t0"])
        ts  = d["ts"]
    except Exception:
        return
    t0q = floor_q(t0).isoformat()
    row = db.execute(
        "SELECT t0q, e_wh, ts FROM live WHERE mitglied=?", (mid,)
    ).fetchone()
    # Neues Intervall → altes festschreiben
    if row and row[0] and t0q > row[0]:
        db.execute(
            "INSERT OR REPLACE INTO intervalle VALUES(?,?,?,?)",
            (mid, row[0], row[1], row[2])
        )
    # Ältere/doppelte Nachricht → ignorieren
    if row and row[0] == t0q and ts <= (row[2] or ""):
        return
    db.execute(
        "INSERT OR REPLACE INTO live VALUES(?,?,?,?,?)",
        (mid, t0q, ts, e, int(time.time()))
    )

try:
    cli = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2, client_id="strom-signal-ingest")
except Exception:
    cli = mqtt.Client(client_id="strom-signal-ingest")

if USER:
    cli.username_pw_set(USER, PW)
if PORT == 8883:
    cli.tls_set(cert_reqs=ssl.CERT_REQUIRED)

cli.reconnect_delay_set(min_delay=1, max_delay=30)
cli.on_connect = on_connect
cli.on_message = on_message
cli.connect(HOST, PORT, 60)
cli.loop_forever()