5jv$HdZddlZddlZddlZddlZddlmZddlmZeejjdee jjdz Z ejZdZdZd Zd Zd Zdd Zd ZdZddZdZdZdZdZddZdZy)u thingpark_store.py — SQLite store for LoRaWAN uplinks from ThingPark (DVI-1136). Durable store for telemetry pushed by the Actility ThingPark Enterprise Application Server (Generic HTTPS connector → POST /thingpark/ingest). Live LoRaWAN telemetry is push-only, so Togen receives uplinks here; the Auditor table, and later the Equipment/Oversight layers, read them back — degrading to the last stored snapshot when ThingPark is unreachable (same contract as scada_store.py). Flask-independent (mirrors scada_store.py / scada_report.py) so app.py, any future scheduler, and tests can all import it. The DB lives alongside the app's other state files (thingpark.db next to thingpark_config.json). Schema ------ uplinks(dev_eui, ts, fcnt_up, ...) — one row per received uplink. Idempotent ingest via UNIQUE(dev_eui, fcnt_up, ts): ThingPark retries failed POSTs and a device's frame counter is unique within a session, so the same uplink is stored once. Decoded payload (ThingPark X driver JSON) is kept verbatim in ``decoded`` for flexible surfacing before we pin per-model columns. meta(key, value) — ingest health bookkeeping for UI surfacing (why data stopped, mirroring the SCADA collector-health pattern). N)datetime)PathTHINGPARK_DB_FILEz thingpark.dbctjttd}|j d|j d|S)N)timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000)sqlite3connectstrrexecuteconns &/var/www/html/togen/thingpark_store.py_connectr)s7 ??3012 >DLL*+LL,- Kct5t5}|jd|jd|jd|jdddddddy#1swYxYw#1swYyxYw)zECreate tables/indexes if missing. Idempotent; call at import/startup.aCREATE TABLE IF NOT EXISTS uplinks ( id INTEGER PRIMARY KEY AUTOINCREMENT, dev_eui TEXT NOT NULL, ts TEXT NOT NULL, fcnt_up INTEGER, fport INTEGER, payload_hex TEXT, rssi REAL, snr REAL, battery REAL, lat REAL, lng REAL, decoded TEXT, source TEXT NOT NULL DEFAULT 'thingpark', created_at TEXT NOT NULL DEFAULT (datetime('now')), UNIQUE(dev_eui, fcnt_up, ts) )zHCREATE INDEX IF NOT EXISTS idx_tp_uplinks_dev_ts ON uplinks(dev_eui, ts)z;CREATE INDEX IF NOT EXISTS idx_tp_uplinks_ts ON uplinks(ts)zBCREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT)N) _write_lockrr r s rinit_thingpark_dbr0s RhjRD   $  ' ( I K P R1RRRRRRs# A4AA(A4(A1 -A44A=c|xsdj}|s|S tj|jdd}|j dS#t $r|cYSwxYw)a+Normalize an uplink timestamp to sortable "YYYY-MM-DD HH:MM:SS" text. ThingPark stamps uplinks with ISO-8601 (``Time``/``time``, often with a timezone offset). Unparseable input is returned trimmed (stored verbatim, sorts best-effort) so we never drop an uplink over a stamp quirk. Zz+00:00%Y-%m-%d %H:%M:%S)stripr fromisoformatreplacestrftime ValueError)rawsdts r normalize_tsr!Nsb A   # #AIIc8$< ={{.// s5A AAcr tt|jS#ttf$rYywxYwN)floatr r TypeErrorrvs r _as_floatr(_s3SV\\^$$ z "s !$66c ttt|jS#tt f$rYywxYwr#)intr$r rr%rr&s r_as_intr+fs85Q()) z "s *-??c|g}|xsgD]}t|tst|jdxsdj }t t|jdxsd}|r|sm|jd}t|tt frtj|d}n | t|}|j|dd|t|jd t|jd |jd +t|jd j dd ndt|jd t|jdt|jdt|jdt|jd||f |syt5t5}|j}|jd||j|z cdddcdddS#1swYnxYwdddy#1swYyxYw)a+Insert uplink dicts idempotently; return the number of NEW rows stored. Each row needs at least ``dev_eui`` and ``ts``. Numeric fields are coerced (None when absent/unparseable); ``decoded`` is JSON-serialized if given a dict/list. Duplicates on (dev_eui, fcnt_up, ts) are ignored. dev_euirtsdecoded),:) separatorsN fcnt_upfport payload_hexirssisnrbatterylatlngrzINSERT OR IGNORE INTO uplinks (dev_eui, ts, fcnt_up, fport, payload_hex, rssi, snr, battery, lat, lng, decoded, source) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) isinstancedictr getrr!listjsondumpsappendr+r(rr total_changes executemany) rowssourcepreparedrr-r.r/rbefores rinsert_uplinksrJmsH ZR7!T" aeeI&,"-335 #aeeDk/R0 1b %% " gd| ,jjZ@G  'lG CRL"gaeeI&67w9Pm$0}% & , , .t 46: aeeFm $ie &= aeeI& '155<)@ aeeEl #Wf 6 77&  +hj+D##  ;=E  F !!F*+++++++s$ H2-H H2H& "H22H;c |\ }}}}}}}}} } } d} | r tj| } ||||||||| | | d S#ttf$r| } Y!wxYw)N) r- last_seenr4r5r6r7r8r9r:r;r/)r@loadsrr%) rowr-r.r4r5r6r7r8r9r:r;r/decs r_row_to_devicerPs|Wb'5+tS'#w C **W%C{D3ss I& C s7A  A ct5}|jdj}dddDcgc] }t|}}t d|Dd}||dS#1swY:xYwcc}w)aMost recent stored uplink per device: {"time": , "devices": [{dev_eui, last_seen, rssi, snr, battery, lat, lng, decoded, ...}]}. Per-device ts lets the Auditor table age each tag independently (a dead tag must show stale even while its neighbors keep reporting).a~SELECT u.dev_eui, u.ts, u.fcnt_up, u.fport, u.payload_hex, u.rssi, u.snr, u.battery, u.lat, u.lng, u.decoded FROM uplinks u WHERE u.id = (SELECT u2.id FROM uplinks u2 WHERE u2.dev_eui = u.dev_eui ORDER BY u2.ts DESC, u2.id DESC LIMIT 1) ORDER BY u.dev_euiNc3&K|] }|d yw)rLN).0ds r z"latest_snapshot..s2Q!K.2s)default)timedevices)rr fetchallrPmax)rrErHrYlatests rlatest_snapshotr]sz /t|| #$%-HJ /+//Q~a /G/ 2'2D AFw ////0s A$A0$A-ct5}|jd||fj}dddDcgc] }t|c}S#1swY"xYwcc}w)zBRecent uplinks for one device, newest first (detail/history view).zSELECT dev_eui, ts, fcnt_up, fport, payload_hex, rssi, snr, battery, lat, lng, decoded FROM uplinks WHERE dev_eui = ? ORDER BY ts DESC, id DESC LIMIT ?N)rr rZrP)r-limitrrErHs rdevice_uplinksr`sj Pt|| 25Actjjd}td||r1td|tddtd|td|y tdt |xsdtd |y ) z?Persist the outcome of one ingest POST for UI health surfacing.ringest_last_runingest_last_okingest_last_errorringest_last_receivedingest_last_insertedz unknown erroringest_last_error_atN)rnowrrkr )okerrorreceivedinsertedrus r record_ingestrzss ,,. ! !"5 6C $ !3'$b)'2'2$c%*B?&CD'-rctdtdtdxsdtdttdS)z'Ingest health summary for API payloads.rorprqNrt)last_runlast_ok last_error last_error_atuplinksrY)rmrdrgrSrr ingest_healthrsA./,-23;t!"89>>  r) thingpark)i)Nrr)__doc__r@osr threadingrpathlibrenvironr>__file__resolveparentrLockrrrr!r(r+rJrPr]r`rdrgrkrmrzrrSrrrs2 JJNN&>))+22^CEF inn R<"$+N  0(-J I8# . r