o |j=@sdZddlZddlZddlZejddZejddZejddZgd Z gd Z d gZ d Z d dZ ddZddZddZddZdddZddZdS)u9Shared helpers for the Asset Inventory page. Hierarchy: Site → Container (connex / truck / toolbox / …) → Category → Item. One versioned SQLite DB (connex.db). Categories are per-container and fully custom (container_categories table); items/photos reference a category by name. Photo bytes live on disk under PHOTO_DIR; the DB stores only the filename. Journal mode is DELETE (not WAL): the DB is written directly by a www-data CGI, and the project rule is that any DB touched by a www-data CGI must be DELETE journal to avoid "readonly database" surprises. N CONNEX_DBz/opt/ngon/data/connex.dbCONNEX_PHOTO_DIRz/opt/ngon/data/connex_photos MASTER_CONFIGz#/opt/ngon/config/master_config.json)ITGensPodsGasz Miner Repairz Good Minersz Bad MinersTools)ConnexTruckToolboxTrailerShedz Hearne Officeap CREATE TABLE IF NOT EXISTS meta ( key TEXT PRIMARY KEY, value TEXT ); CREATE TABLE IF NOT EXISTS containers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, site TEXT NOT NULL, type TEXT NOT NULL DEFAULT 'Connex', position REAL DEFAULT 0, created_by TEXT, created_at TEXT ); CREATE TABLE IF NOT EXISTS container_categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, container_id INTEGER NOT NULL REFERENCES containers(id) ON DELETE CASCADE, name TEXT NOT NULL, position REAL DEFAULT 0, UNIQUE(container_id, name) ); CREATE TABLE IF NOT EXISTS items ( id INTEGER PRIMARY KEY AUTOINCREMENT, container_id INTEGER NOT NULL REFERENCES containers(id) ON DELETE CASCADE, category TEXT NOT NULL, description TEXT NOT NULL, quantity INTEGER NOT NULL DEFAULT 0, position REAL DEFAULT 0, created_by TEXT, created_at TEXT ); CREATE TABLE IF NOT EXISTS photos ( id INTEGER PRIMARY KEY AUTOINCREMENT, container_id INTEGER NOT NULL REFERENCES containers(id) ON DELETE CASCADE, category TEXT NOT NULL, filename TEXT NOT NULL, orig_name TEXT, caption TEXT, uploaded_by TEXT, uploaded_at TEXT ); CREATE TABLE IF NOT EXISTS site_order ( site TEXT PRIMARY KEY, position REAL DEFAULT 0 ); CREATE INDEX IF NOT EXISTS idx_items_container ON items(container_id); CREATE INDEX IF NOT EXISTS idx_photos_container ON photos(container_id); CREATE INDEX IF NOT EXISTS idx_cats_container ON container_categories(container_id); cCs|d|fduS)Nz9SELECT 1 FROM sqlite_master WHERE type='table' AND name=?)executefetchone)connnamer&/var/www/html/ngon/connex/connex_db.py _table_existsTs rc st|dr t|dr dS|d|d|d|d|tdd ttD|d }i}|D]\}}||t |q9| D]$\}}t |fd d d D]} |dd}|d||t |fqZqLdS)uNOne-time migration from the original connex-only schema to the container model: connexes → containers (+type='Connex'), items/photos connex_id → container_id, and backfill container_categories from the distinct categories already in use. Runs only when the legacy `connexes` table exists and `containers` does not.connexes containersNz)ALTER TABLE connexes RENAME TO containerszEALTER TABLE containers ADD COLUMN type TEXT NOT NULL DEFAULT 'Connex'z:ALTER TABLE items RENAME COLUMN connex_id TO container_idz:ALTER TABLE photos RENAME COLUMN connex_id TO container_idcSsi|]\}}||qSrr).0irrrr hsz0_migrate_connex_to_container..zXSELECT container_id, category FROM items UNION SELECT container_id, category FROM photoscs|d|fS)Ni)getlower)corderrrpsz._migrate_connex_to_container..)keyizWINSERT OR IGNORE INTO container_categories(container_id, name, position) VALUES (?,?,?))rr executescript_SCHEMA enumerateSUGGESTED_CATEGORIESfetchall setdefaultsetadditemssortedrfloat)rpairsseen container_idcategorycatscatposrrr_migrate_connex_to_containerYs2       r5cCsNtt}tj|_|d|dt||t|d| |S)zOOpen connex.db, migrate/ensure schema + version row, return a Row-factory conn.zPRAGMA journal_mode=DELETEzPRAGMA foreign_keys=ONz>INSERT OR IGNORE INTO meta(key, value) VALUES ('version', '0')) sqlite3connectDBRow row_factoryrr5r#r$commitrrrrget_dbws     r=cCs"|d}|rt|dSdS)Nz*SELECT value FROM meta WHERE key='version'r)rrint)rrowrrr get_versionsr@cCs|dt|S)NzTUPDATE meta SET value = CAST(CAST(value AS INTEGER) + 1 AS TEXT) WHERE key='version')rr@r<rrr bump_versions rArcCs,|d|d||}t|ddS)Nz'SELECT COALESCE(MAX(position), 0) FROM  rg?)rrr-)rtable where_sql where_argsr?rrr next_positions rGcCsvtt}z*tt }t|}Wdn1swY||dp&iWt |St y:Yt |Sw)zSorted site names for the container 'site' picker: master_config sites plus any one-off EXTRA_SITES (e.g. Hearne Office) that aren't real mining sites.Nsites) r) EXTRA_SITESopenrjsonloadupdaterkeys Exceptionr,)namesfcfgrrr site_namess   rS)rBr)__doc__rKosr6environrr8 PHOTO_DIRrr&SUGGESTED_CONTAINER_TYPESrIr$rr5r=r@rArGrSrrrrs$ 3