#!/usr/bin/env python3
"""Backfill deep history into emanagement.sqlite from the server's own store.

For every point that has live data, calls smallGraph.php `gVals` (which returns
raw [ts_ms, value] pairs) and inserts them as readings. Timestamps are converted
so they align exactly with getData-sourced rows (dedup is automatic).

Usage:  python db_backfill_gvals.py [hours]     (default 24; server caps ~3190 pts)
"""
import urllib.request, urllib.parse, http.cookiejar, json, os, sys, datetime
import db_ingest as ing

HERE = os.path.dirname(os.path.abspath(__file__))
BASE = "https://agregator.e-management.cz/lookdet"
UA   = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"
HOURS = int(sys.argv[1]) if len(sys.argv) > 1 else 24

def login():
    ls = [l.strip() for l in open(os.path.join(HERE, "login.txt"), encoding="utf-8").read().splitlines()
          if l.strip() and not l.strip().startswith("#")]
    if len(ls) < 2 or ls[1].startswith("<"):
        raise SystemExit("Fill login.txt with your password first.")
    cj = http.cookiejar.CookieJar()
    op = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
    op.addheaders = [("User-Agent", UA)]
    op.open(BASE + "/common/login/", timeout=15).read()
    op.open(BASE + "/common/login/index.php",
            data=urllib.parse.urlencode({"control": "login", "logLogin": ls[0], "logPassword": ls[1]}).encode(),
            timeout=15).read()
    if "login" in op.open(BASE + "/zalozky/", timeout=15).geturl():
        raise SystemExit("Login failed - check login.txt.")
    return op

def gvals(op, zdroj, hours):
    payload = [{"n": "gVals", "p": [[zdroj], "0", 0, hours, 5000]}]
    body = "funcs=" + urllib.parse.quote_plus(json.dumps(payload)) + "&timeStamp=1786223729849"
    req = urllib.request.Request(BASE + "/common/visual/php/smallGraph.php", data=body.encode(), method="POST")
    req.add_header("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8")
    req.add_header("X-Requested-With", "XMLHttpRequest")
    req.add_header("Referer", BASE + "/zalozky/")
    req.add_header("User-Agent", UA)
    d = json.loads(op.open(req, timeout=30).read())
    return d["returns"][0]["r"][0] or []

def ts_iso(ms):
    return datetime.datetime.fromtimestamp(ms / 1000, datetime.timezone.utc).strftime("%Y-%m-%d %H:%M:%S")

def main():
    con = ing.connect()
    # points that have appeared in live data (they have gVals history)
    pts = con.execute("""
        SELECT DISTINCT p.point_id, p.key, p.zdroj
        FROM points p JOIN readings r USING(point_id)
        WHERE p.zdroj IS NOT NULL
        ORDER BY p.key""").fetchall()
    print("Logging in...")
    op = login()
    print("Backfilling %d points x %dh ...\n" % (len(pts), HOURS))
    total = 0; empty = 0
    cur = con.cursor()
    for i, (pid, key, zdroj) in enumerate(pts, 1):
        try:
            ser = gvals(op, zdroj, HOURS)
        except Exception as e:
            print("  %-16s ERROR %s" % (key, e)); continue
        n = 0
        for pt in ser:
            if not pt or pt[1] is None:
                continue
            cur.execute("INSERT OR IGNORE INTO readings(point_id,ts,raw) VALUES(?,?,?)",
                        (pid, ts_iso(pt[0]), float(pt[1])))
            n += cur.rowcount
        total += n
        if not ser:
            empty += 1
        if n:
            print("  [%3d/%d] %-18s +%d" % (i, len(pts), key, n))
        if i % 20 == 0:
            con.commit()
    cur.execute("INSERT INTO snapshots(server_date,source,n_new) VALUES(?,?,?)", (None, "gvals", total))
    con.commit()
    grand = con.execute("SELECT COUNT(*) FROM readings").fetchone()[0]
    span = con.execute("SELECT MIN(ts), MAX(ts) FROM readings").fetchone()
    print("\nDone. +%d new readings (%d points returned no history)." % (total, empty))
    print("Total readings: %d   span: %s -> %s" % (grand, span[0], span[1]))
    con.close()

if __name__ == "__main__":
    main()