5j^dZddlZddlZddlZddlZddlmZddlmZeejjdee jjdz Z ejZdZdZd Zd Zd Zd Zd ZdZedfdZeddfdZdZdZdZddZdZey)u6 powerbi_store.py — SQLite cache + snapshot store for Power BI data (DVI-1243). Togen reads the Power BI REST API live-first (org service principal, client-credentials) and falls back to this local store so dashboard tiles and Auditor reports never go blank when Power BI is slow, throttled, or down — the same board-approved hybrid design as the Idencia service (D4 mirrors DVI-1226 D2). The background vocab sync (org scope) and every successful live query (any scope) write through here. Flask-independent (mirrors idencia_store.py / scada_store.py) so app.py, the togen-worker sync loop, and tests can all import it. The DB lives alongside the app's other state files (powerbi.db next to powerbi_config.json). Schema ------ cache(scope, cache_key, payload, fetched_at) — last-good JSON result per (connection scope, query key). Scope is "org" today (DVI-1243 D1-A: org service principal only); "user:" is reserved for the deferred per-user delegated-access follow-up (D1-B) so cache entries stay scope-keyed from day one. UPSERT on (scope, cache_key); this is a latest-value store, not history. snapshots(run_ts, scope, kind, payload) — append-only rows written by each sync run (workspace/report/dataset inventory counts, dataset refresh outcomes), the over-time data source for Auditor Reporting (P3). Kept separate from cache so trends survive cache overwrites. meta(key, value) — sync health bookkeeping (last run, error, counts) for the Admin → Services → Power BI pane, mirroring the SCADA collector-health pattern. N)datetime)PathPOWERBI_DB_FILEz powerbi.dborgctjttd}|j d|j d|S)N)timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000)sqlite3connectstrrexecuteconns $/var/www/html/togen/powerbi_store.py_connectr1s6 ??3/ " "#6 77rc^dt|xsdjjzS)zScope key for a personal connection ("user:", lowercased). Reserved for the D1-B per-user delegated-access follow-up.zuser:)r striplower)emails r user_scoper Ys* S"%++-335 55rc t5t5}|jd||tj|t fddddddy#1swYxYw#1swYyxYw)zYStore the last-good payload for (scope, key). payload is any JSON-serializable value.zINSERT INTO cache (scope, cache_key, payload, fetched_at) VALUES (?, ?, ?, ?) ON CONFLICT(scope, cache_key) DO UPDATE SET payload=excluded.payload, fetched_at=excluded.fetched_atN)rrr jsondumpsr)scope cache_keypayloadrs r cache_putr'asa =hj=D  ? Itzz'2DF ;  =======s" A!2AA!A A!!A*ct5}|jd||fj}dddsy tj|d|ddS#1swY+xYw#tj t f$rYywxYw)z|Return {"payload": ..., "fetched_at": "..."} or None when never cached. Corrupt rows read as a miss rather than raising.zCSELECT payload, fetched_at FROM cache WHERE scope=? AND cache_key=?Nr)r& fetched_atrr fetchoner"loadsJSONDecodeError TypeError)r$r%rrows r cache_getr1ns +tll - I  !)  + ::c!f-SVDD++  ) ,s#AA$A!$B?Bct5}|jd|fj}dddDcgc] }|d|ddc}S#1swY"xYwcc}w)zDList cached keys for a scope with their freshness (admin surfacing).zHSELECT cache_key, fetched_at FROM cache WHERE scope=? ORDER BY cache_keyNrr))r%r*)rr fetchall)r$rrowsrs r cache_keysr6~sf 8t|| "$)8--5XZ 8>B B!A$ad 3 BB 88 Cs"AAAc t5t5}|jd|xs t||t j |fddddddy#1swYxYw#1swYyxYw)z4Append one snapshot row (payload JSON-serializable).zHINSERT INTO snapshots (run_ts, scope, kind, payload) VALUES (?, ?, ?, ?)N)rrr rr"r#)kindr&r$run_tsrs r add_snapshotr:sk BhjBD  #  tvudDJJw,? @ BBBBBBBs" A%6AA%A" A%%A.c tdtt|xsdd}d}||g}|r|dz }|j||dz }|j|t 5}|j ||j }dddg}tD]-\} } |j| tj| d/|S#1swYHxYw#tjtf$rYYwxYw) u{Snapshot rows for one kind, oldest→newest (chart-ready). ``since`` is an inclusive "YYYY-MM-DD HH:MM:SS" lower bound.r)r;iz>SELECT run_ts, payload FROM snapshots WHERE kind=? AND scope=?z AND run_ts>=?z& ORDER BY run_ts DESC, id DESC LIMIT ?N)r9r&) maxminintappendrr r3reversedr"r-r.r/) r8r$sincelimitqargsrr4outr9r&s r snapshotsrGs 3s5  JJ&TZZ5HI J J00 $$i0   s"!C 'C CC21C2ct5}|jdjdcdddS#1swYyxYw)NzSELECT COUNT(*) FROM snapshotsr)rr r,rs rsnapshot_countrIs= Lt||<=FFHKLLLs "7Ac t5t5}|jd|tj|fddddddy#1swYxYw#1swYyxYw)Nz_INSERT INTO meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value=excluded.value)rrr r"r#)keyvaluers rset_metarMsX &hj&D  C $**U# $ &&&&&&&s! A(A A A AA ct5}|jd|fj}dddsy tj|dS#1swY%xYw#tj t f$rYywxYw)Nz"SELECT value FROM meta WHERE key=?rr+)rKrr0s rget_metarOs Etll 03&::B(* E zz#a&!! EE  ) ,s"AAAA98A9c|t|xsd|xsid}|rt|dd|d<td||dk(r td|yy) zCRecord one sync run's outcome for the admin pane. status: ok|error.r)statusatactorcountsNr;error last_syncok last_sync_ok)rr rM)rQrSrUrTrecs r record_syncrZsT46EKR\r #C 5z$3'G  [# ~%rc0tdtddS)zC{"last": ..., "last_ok": ...} for the Admin pane (None when never).rVrX)lastlast_ok)rOrrr sync_healthr^s[)h~6N OOr)rNN)__doc__r"osr threadingrpathlibrenvironget__file__resolveparentrLockr ORG_SCOPErrrr r'r1r6r:rGrIrMrOrZr^rrrrjs> JJNN$>))+22\ACD inn  :86 =  C'0B$4s.L& &P r