#!/usr/bin/env python3 """Shared 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. """ import json import os import sqlite3 DB = os.environ.get('CONNEX_DB', '/opt/ngon/data/connex.db') PHOTO_DIR = os.environ.get('CONNEX_PHOTO_DIR', '/opt/ngon/data/connex_photos') MASTER_CONFIG = os.environ.get('MASTER_CONFIG', '/opt/ngon/config/master_config.json') # Suggested category names offered as quick-picks when adding a category to a container. # Categories are otherwise fully custom (per container) — this list is just seeds/hints. SUGGESTED_CATEGORIES = ['IT', 'Gens', 'Pods', 'Gas', 'Miner Repair', 'Good Miners', 'Bad Miners', 'Tools'] # Suggested container types (free-text field; any custom type is allowed). SUGGESTED_CONTAINER_TYPES = ['Connex', 'Truck', 'Toolbox', 'Trailer', 'Shed'] # One-off asset locations that aren't real mining sites in master_config (so we don't # pollute the fleet-wide site list). Add a name here to make it selectable for a container. EXTRA_SITES = ['Hearne Office'] _SCHEMA = """ 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); """ def _table_exists(conn, name): return conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (name,)).fetchone() is not None def _migrate_connex_to_container(conn): """One-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.""" if not (_table_exists(conn, 'connexes') and not _table_exists(conn, 'containers')): return conn.execute("ALTER TABLE connexes RENAME TO containers") conn.execute("ALTER TABLE containers ADD COLUMN type TEXT NOT NULL DEFAULT 'Connex'") conn.execute("ALTER TABLE items RENAME COLUMN connex_id TO container_id") conn.execute("ALTER TABLE photos RENAME COLUMN connex_id TO container_id") # create the new tables/indexes (container_categories etc.) before backfilling conn.executescript(_SCHEMA) # backfill per-container categories from existing item + photo category usage, # ordered by the suggested order first, then alphabetically. order = {name: i for i, name in enumerate(SUGGESTED_CATEGORIES)} pairs = conn.execute( "SELECT container_id, category FROM items " "UNION SELECT container_id, category FROM photos").fetchall() seen = {} for container_id, category in pairs: seen.setdefault(container_id, set()).add(category) for container_id, cats in seen.items(): for cat in sorted(cats, key=lambda c: (order.get(c, 999), c.lower())): pos = order.get(cat, 900) + 1 conn.execute( "INSERT OR IGNORE INTO container_categories(container_id, name, position) VALUES (?,?,?)", (container_id, cat, float(pos))) def get_db(): """Open connex.db, migrate/ensure schema + version row, return a Row-factory conn.""" conn = sqlite3.connect(DB) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=DELETE") conn.execute("PRAGMA foreign_keys=ON") _migrate_connex_to_container(conn) conn.executescript(_SCHEMA) conn.execute("INSERT OR IGNORE INTO meta(key, value) VALUES ('version', '0')") conn.commit() return conn def get_version(conn): row = conn.execute("SELECT value FROM meta WHERE key='version'").fetchone() return int(row[0]) if row else 0 def bump_version(conn): conn.execute("UPDATE meta SET value = CAST(CAST(value AS INTEGER) + 1 AS TEXT) WHERE key='version'") return get_version(conn) def next_position(conn, table, where_sql="", where_args=()): row = conn.execute( f"SELECT COALESCE(MAX(position), 0) FROM {table} {where_sql}", where_args ).fetchone() return float(row[0]) + 1.0 def site_names(): """Sorted 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.""" names = set(EXTRA_SITES) try: with open(MASTER_CONFIG) as f: cfg = json.load(f) names.update((cfg.get('sites') or {}).keys()) except Exception: pass return sorted(names)