#!/usr/bin/env python3
"""Create/upgrade emanagement.sqlite: apply schema, seed sites + points
(metadata from labels.js, sid from emdata.js), and backfill readings we
already have (baseline.js = the Aug-2 HAR snapshot; emdata.js = current;
emhistory.json = any accumulated snapshots). Safe to re-run (idempotent)."""
import os, re, json, sqlite3
import db_ingest as ing

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

SITES = [
    ("00_ems", "EMS / system",               ""),
    ("01_ee",  "Environmental Energy s.r.o.", "Kozichovice"),
    ("01_eko", "Environmental Energy s.r.o.", "Kozichovice"),
    ("01_ens", "ENS Power s.r.o.",            "Brno-Hady"),
    ("02_eco", "AgroPellets / UniAgris",      "Smrzice"),
    ("02_eko", "AgroPellets / UniAgris",      "Smrzice"),
    ("03_ens", "ENS Power s.r.o.",            "Brno-Hady"),
    ("03_ene", "ENS Power s.r.o.",            "Brno-Hady"),
    ("04_app", "Appolo BESS s.r.o.",          "Mydlovary"),
    ("04_ene", "Appolo BESS s.r.o.",          "Mydlovary"),
    ("04_ens", "Appolo BESS s.r.o.",          "Mydlovary"),
]

def load_js(fn, must=True):
    p = os.path.join(HERE, fn)
    if not os.path.exists(p):
        if must: raise SystemExit("missing " + fn)
        return None
    t = open(p, encoding="utf-8-sig").read().strip()
    return json.loads(t[t.index("{" if "{" in t[:40] else "["):].rstrip(";").rstrip())

def key_parts(key):
    m = re.match(r"l(\d+)\.ch(\d+)\.r(\d+)", key)
    return (int(m.group(1)), int(m.group(2)), int(m.group(3))) if m else None

def main():
    con = ing.connect(DB)
    con.executescript(open(os.path.join(HERE, "db_schema.sql"), encoding="utf-8").read())

    cur = con.cursor()
    cur.executemany("INSERT OR REPLACE INTO sites(site_code,company,location) VALUES(?,?,?)", SITES)

    labels = load_js("labels.js")                       # {key: {name,unit,mult,dec,typ}}
    # 1) seed points from labels (metadata)
    for key, meta in labels.items():
        lp = key_parts(key)
        if lp: ing.upsert_point(cur, key, *lp, sid=None, meta=meta)
    # 2) add sid from the live snapshot
    emd = load_js("emdata.js")
    for p in emd["points"]:
        lp = key_parts(p["key"])
        if lp: ing.upsert_point(cur, p["key"], *lp, sid=p.get("sid"),
                                meta=labels.get(p["key"]))
    con.commit()

    # 3) backfill readings we already have
    #    baseline.js: {key: {val, dt}}
    base = load_js("baseline.js", must=False) or {}
    nb = 0
    for key, v in base.items():
        lp = key_parts(key)
        if not lp: continue
        pid = ing.upsert_point(cur, key, *lp, meta=labels.get(key))
        cur.execute("INSERT OR IGNORE INTO readings(point_id,ts,raw) VALUES(?,?,?)",
                    (pid, v.get("dt"), float(v["val"])))
        nb += cur.rowcount
    cur.execute("INSERT INTO snapshots(server_date,source,n_new) VALUES(?,?,?)",
                (None, "baseline", nb))

    #    emdata.js current values -> readings at their dt
    ne = 0
    for p in emd["points"]:
        lp = key_parts(p["key"])
        if not lp: continue
        pid = ing.upsert_point(cur, p["key"], *lp, sid=p.get("sid"), meta=labels.get(p["key"]))
        cur.execute("INSERT OR IGNORE INTO readings(point_id,ts,raw) VALUES(?,?,?)",
                    (pid, p.get("dt"), float(p["val"])))
        ne += cur.rowcount
    cur.execute("INSERT INTO snapshots(server_date,source,n_new) VALUES(?,?,?)",
                (emd.get("ts"), "emdata", ne))

    #    emhistory.json: [{ts, v:{key:val}}, ...]  (approximate ts = local fetch time)
    nh = 0
    try:
        hist = json.load(open(os.path.join(HERE, "emhistory.json"), encoding="utf-8"))
    except Exception:
        hist = []
    for snap in hist:
        ts = snap.get("ts")
        for key, val in (snap.get("v") or {}).items():
            lp = key_parts(key)
            if not lp: continue
            pid = ing.upsert_point(cur, key, *lp, meta=labels.get(key))
            cur.execute("INSERT OR IGNORE INTO readings(point_id,ts,raw) VALUES(?,?,?)",
                        (pid, ts, float(val)))
            nh += cur.rowcount
    if hist:
        cur.execute("INSERT INTO snapshots(server_date,source,n_new) VALUES(?,?,?)",
                    (None, "emhistory", nh))
    con.commit()

    # report
    P = con.execute("SELECT COUNT(*) FROM points").fetchone()[0]
    PN = con.execute("SELECT COUNT(*) FROM points WHERE name IS NOT NULL AND name<>''").fetchone()[0]
    R = con.execute("SELECT COUNT(*) FROM readings").fetchone()[0]
    print("DB:", DB)
    print("  points   : %d (%d named)" % (P, PN))
    print("  readings : %d  (baseline %d, emdata %d, emhistory %d)" % (R, nb, ne, nh))
    print("  sites    :", con.execute("SELECT COUNT(*) FROM sites").fetchone()[0])
    con.close()

if __name__ == "__main__":
    main()