5jAdZddlZddlZddlZddlZddlZddlmZddlmZeejjdee jjdz ZejZdZdZd Zd d Zd Zd!d Zd"dZd#dZdZdZd!dZ d$dZd%dZdZd&dZd'dZ ddd ddddddZ!dZ"dZ#dZ$d(dZ%ey))ureports_store.py — SQLite store for Report Builder templates (DVI-1255). Part of the DVI-1252 Report Builder. A *report template* is a reusable, versioned definition — a set of dashboard-shaped tiles (the chart / report_table tiles registered by P1, DVI-1254) plus a report-level date-range parameter and an optional recipient list — that P3 will render into a viewable report and (later) snapshot to history. Flask-independent (mirrors scada_store.py / idencia_store.py / assets_store.py) so app.py, the togen-worker, and tests can all import it. The DB lives alongside the app's other state files (reports.db next to dashboards.json). Decision D7-A: this lives in a standalone module (NOT inline in app.py) with a DVI-848 dual-dir `update.sh` copy step, so the `sys.path` shadow gotcha can never serve a stale copy. Schema ------ templates(id, name, owner, definition, version, created_at, updated_at, deleted) — one row per template. `definition` is the JSON authoring payload (tiles + date_range + recipients). `version` bumps on every content save; soft-delete via `deleted` keeps ids stable for any P3 run rows that reference them. template_versions(id, template_id, version, definition, created_at, created_by) — append-only history of every saved version, so a template's edit trail (and a future "restore this version") survives overwrites. runs / snapshots — created empty now so P3 (Report Viewer + history + snapshots) is additive with no schema migration; no write API here yet. N)datetime)PathREPORTS_DB_FILEz reports.dbctjttd}|j d|j dtj |_|S)N)timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000)sqlite3connectstrrexecuteRow row_factoryconns $/var/www/html/togen/reports_store.py_connectr/sB ??3/        ' (     : ;CC;C;C;C;C;C;s# B8B B,B8,B5 1B88CcHtjjdS)Nz%Y-%m-%d %H:%M:%S)rnowstrftimerr_nowrs <<> " "#6 77rc |y|d|d|d|d|d|dt|dd}|r tj|d |d <|S|S#tjtf$r i|d <Y|SwxYw) Nidnameownerversion created_at updated_atdeleted)rrrr r!r"r# definition)booljsonloadsJSONDecodeError TypeError)rowwith_definitionouts r_row_to_templater-s {$iF Wy>,','I' C # $ 3|+< =C  J3J$$i0 # "C  J #sAA0/A0c dtjjddz}t}t j |xsi}t 5t5}|jd|||xsd|||f|jd||||xsdfddddddt|S#1swYxYw#1swYt|SxYw)z FHJ  ; &&& f"&,3v;Di.  !jj!1r2G)nq(G LL27GS#6 8 LL-gwU[b9  ; LLF3$ &/&&4  5&&&4  s. C2%C&C2BC& C2&C/ +C22Dct5t5}|jdt|f}|jdkDcdddcdddS#1swYnxYwdddy#1swYyxYw)ztSoft-delete (keeps id stable for any P3 run rows). Returns True if a live template was found and marked deleted.zEUPDATE templates SET deleted=1, updated_at=? WHERE id=? AND deleted=0rN)rrr rrowcount)r7rcurs rdelete_templaterQsf  hj Dll "fc],||a        s! A%+A A%A A%%A.ct5}|jd|fj}dddDcgc]}|d|d|ddc}S#1swY&xYwcc}w)z:Version history (metadata) for one template, newest first.zgSELECT version, created_at, created_by FROM template_versions WHERE template_id=? ORDER BY version DESCNr r! created_by)r r!rS)rr rC)r7rrFrGs rtemplate_versionsrTs| Lt|| 8:=AAI L 6: ;01) AlO\? , ;; LL ;s"AAAcd}|s|dz }t5}|j|jdcdddS#1swYyxYw)Nz#SELECT COUNT(*) AS n FROM templatesz WHERE deleted=0nrr r;)r<r=rs rtemplate_countrXsJ-A   /t||A'')#.///s "AA okc dtjjddz}t}t j d|xsii}t j |xsi} t 5t5} | jd|||||xsd||xsdf| jd|| fddddddt|S#1swYxYw#1swYt|SxYw) zEPersist a run + its view-model snapshot. Returns the full run record.run_N date_rangezINSERT INTO runs (id, template_id, template_version, ran_at, ran_by, params, status) VALUES (?, ?, ?, ?, ?, ?, ?)r1rYzDINSERT INTO snapshots (run_id, tile_id, payload) VALUES (?, NULL, ?)) r2r3r4rr&r5rrr get_run) template_idtemplate_versionran_by view_modelr]statusridrparamsr8rs r create_runrfs 4::<##CR( (C &C ZZz'7R8 9Fjj)r*G  hj D  /+/flF ^t    R 'N    3<    3<s$) C46C*CC CC$c |y |drtj|dni}|d|d|d|d|d|d|j dxsid }|r||d <|S#tjtf$ri}YUwxYw) Nrerr_r`ran_atrarcr])rr_r`rhrarcr]rb)r&r'r(r)get)r*with_view_modelrbrer,s r _row_to_runrk#s {.1(mCM*$i=) 23h-h-h-jj.4" C&L J  ) ,sAA:9A:ctt5}|jd|fj}| dddy|jd|fj}dddd} tj|d}td|S#1swY4xYw#tj t f$rd}Y7wxYw)z9Full run record including its stored view-model, or None.zSELECT * FROM runs WHERE id=?NzDSELECT payload FROM snapshots WHERE run_id=? ORDER BY id ASC LIMIT 1r8T)rjrb)rr r;r&r'r(r)rk)rdrr*snapvms rr^r^8s *tll:SFCLLN ;**|| v'xz * B  DO,B sDR @@**$$i0 B s"%B !B 'B BB76B7c. tdtt|d}t 5}|j d||fj}dddDcgc] }t|c}S#ttf$rd}YawxYw#1swY9xYwcc}w)zTEKR$3G I J}}       s# B)A/B B)B B))B2c |y |drtj|dng}|d|d|d|d|dt |d||d|d |d xsd d S#tjtf$rg}YRwxYw) Nrrr_rr|r}r~rrcrr1) rr_rr|r}r~rrrcr)r&r'r(r)r%)r*recipss r_row_to_schedule_runrxs {25l2CC -.$i=) O Oh-I','h-W#    ) ,sAA76A7ct5}|jd||fj}ddd|duS#1swYduSxYw)uTrue if a scheduled run already landed for this template on run_date (the per-period fire guard). A prior error also counts — a failed fire is retried on the next due day, not re-hammered every minute.zFSELECT 1 FROM schedule_runs WHERE template_id=? AND run_date=? LIMIT 1NrW)r_rrr*s rschedule_ran_onrsX ;tll #X.008  ; d? ; d?s #:Act5}|jd|fj}dddt|S#1swYtSxYw)z9Most-recent scheduled-run record for a template, or None.zHSELECT * FROM schedule_runs WHERE template_id=? ORDER BY id DESC LIMIT 1N)rr r;r)r_rr*s rlast_schedule_runrsX Ctll ')488@  C  $$ C  $$s "AAc. tdtt|d}t 5}|j d||fj}dddDcgc] }t|c}S#ttf$rd}YawxYw#1swY9xYwcc}w)zHScheduled-run history (newest first) for the Report Builder health view.rJrp2zHSELECT * FROM schedule_runs WHERE template_id=? ORDER BY id DESC LIMIT ?N) rrrsrtr)rurr rCrrvs rlist_schedule_runsrsAs3u:s+, It|| ')4e(<>>Fhj I.2 2  # 22 z "II 3ry)T)F)NF)NNr1)NrY)FN)rq)N)r)&__doc__r&osr threadingr2rpathlibrenvironri__file__resolveparentrLockrrrrr-r9r6rHrMrQrTrXrfrkr^rxr{rrrrrrrrrs<  JJNN$>))+22\ACD inn E;P8,(!F$B ;/"(,**A$ *5 <@ %$1#2  *% 3r