#!/usr/bin/env python3
"""Ingest getData JSON into emanagement.sqlite.

Reusable by:
  * db_build.py         (initial build / re-seed)
  * fetch_emanagement.bat  ->  python db_ingest.py _raw.json bat
  * serve.py            (imports ingest_json() and logs every realtime poll)

Design: upsert the point (dimension) if unseen, then INSERT OR IGNORE one
reading per (point, server-measurement-time). Dedup is automatic.
"""
import sqlite3, json, os, re, sys

HERE = os.path.dirname(os.path.abspath(__file__))
DB   = os.path.join(HERE, "emanagement.sqlite")

def connect(db=DB):
    # check_same_thread=False so serve.py's worker threads can log via the
    # single shared connection (writes are serialized by serve.py's lock).
    con = sqlite3.connect(db, timeout=15, check_same_thread=False)
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA foreign_keys=ON")
    return con

def parse_name(name):
    """'01_ee_KGJ1_vykon_procenta' -> ('01_ee','KGJ1','vykon_procenta')."""
    if not name:
        return (None, None, None)
    parts = name.split("_")
    if parts[0].isdigit() and len(parts) >= 3:
        return ("%s_%s" % (parts[0], parts[1]), parts[2], "_".join(parts[3:]) or None)
    return (None, parts[0], "_".join(parts[1:]) or None)

def upsert_point(cur, key, layer, channel, register, sid=None, meta=None):
    """Ensure a points row exists; fill sid/metadata when available. Returns point_id."""
    site = device = metric = None
    name = unit = typ = None
    mult = 1.0; dec = 0
    if meta:
        name = meta.get("name") or None
        unit = meta.get("unit") or None
        typ  = meta.get("typ") or None
        try: mult = float(meta.get("mult", 1) or 1)
        except (TypeError, ValueError): mult = 1.0
        try: dec = int(meta.get("dec", 0) or 0)
        except (TypeError, ValueError): dec = 0
        site, device, metric = parse_name(name)
    zdroj = "%d;%d;%d;0" % (layer, channel, register)
    cur.execute(
        """INSERT INTO points(key,layer,channel,register,zdroj,sid,name,unit,mult,decimals,typ,site_code,device,metric)
           VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
           ON CONFLICT(key) DO UPDATE SET
             sid       = COALESCE(points.sid, excluded.sid),
             name      = COALESCE(points.name, excluded.name),
             unit      = COALESCE(NULLIF(points.unit,''), excluded.unit),
             mult      = CASE WHEN points.mult=1 AND excluded.mult<>1 THEN excluded.mult ELSE points.mult END,
             decimals  = CASE WHEN points.decimals=0 AND excluded.decimals<>0 THEN excluded.decimals ELSE points.decimals END,
             typ       = COALESCE(points.typ, excluded.typ),
             site_code = COALESCE(points.site_code, excluded.site_code),
             device    = COALESCE(points.device, excluded.device),
             metric    = COALESCE(points.metric, excluded.metric)""",
        (key, layer, channel, register, zdroj, sid, name, unit, mult, dec, typ, site, device, metric))
    row = cur.execute("SELECT point_id FROM points WHERE key=?", (key,)).fetchone()
    return row[0]

def ingest_data(con, data, server_date=None, source="import", labels=None):
    """data = {'l1': {...}, 'l2': {...}} nested getData structure. Returns #new readings."""
    cur = con.cursor()
    new = 0
    for L, chs in (data or {}).items():
        if not L.startswith("l"):
            continue
        layer = int(L[1:])
        for ch, body in chs.items():
            if not ch.startswith("ch"):
                continue
            channel = int(ch[2:])
            sid = body.get("sid")
            for rk, rv in body.items():
                if not rk.startswith("r"):
                    continue
                register = int(rk[1:])
                c0 = (rv or {}).get("c0") or {}
                if "val" not in c0:
                    continue
                key = "l%d.ch%d.r%d" % (layer, channel, register)
                meta = labels.get(key) if labels else None
                pid = upsert_point(cur, key, layer, channel, register, sid, meta)
                cur.execute("INSERT OR IGNORE INTO readings(point_id,ts,raw) VALUES(?,?,?)",
                            (pid, c0.get("dt"), float(c0["val"])))
                new += cur.rowcount
    cur.execute("INSERT INTO snapshots(server_date,source,n_new) VALUES(?,?,?)",
                (server_date, source, new))
    con.commit()
    return new

def ingest_json(con, text, source="import", labels=None):
    """text = full getData response JSON string."""
    d = json.loads(text)
    r = d["returns"][0]["r"]
    return ingest_data(con, r.get("data", {}), r.get("date"), source, labels)

def _load_labels():
    p = os.path.join(HERE, "labels.js")
    if not os.path.exists(p):
        return {}
    t = open(p, encoding="utf-8-sig").read().strip()
    return json.loads(t[t.index("{"):t.rindex("}") + 1])

if __name__ == "__main__":
    raw = sys.argv[1] if len(sys.argv) > 1 else os.path.join(HERE, "_raw.json")
    source = sys.argv[2] if len(sys.argv) > 2 else "bat"
    if not os.path.exists(raw):
        print("no file:", raw); sys.exit(1)
    con = connect()
    n = ingest_json(con, open(raw, encoding="utf-8").read(), source, _load_labels())
    total = con.execute("SELECT COUNT(*) FROM readings").fetchone()[0]
    print("ingested %d new reading(s) [source=%s]; total readings now %d" % (n, source, total))
    con.close()