"""SQLite schema and connection management. JSON-blob storage per PLAN.md 1d.""" from __future__ import annotations import sqlite3 from pathlib import Path SCHEMA = """ CREATE TABLE IF NOT EXISTS routes ( id TEXT PRIMARY KEY, name TEXT NOT NULL, source TEXT NOT NULL, data JSON NOT NULL, trail_guide_id TEXT REFERENCES trail_guides(id) ON DELETE SET NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS trail_guides ( id TEXT PRIMARY KEY, name TEXT NOT NULL, source_url TEXT, pdf_path TEXT, data JSON NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS assets ( id TEXT PRIMARY KEY, route_id TEXT NOT NULL REFERENCES routes(id) ON DELETE CASCADE, generator TEXT NOT NULL, data JSON NOT NULL, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS weather_cache ( route_id TEXT PRIMARY KEY REFERENCES routes(id) ON DELETE CASCADE, data JSON NOT NULL, fetched_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS offline_map_packs ( id TEXT PRIMARY KEY, name TEXT NOT NULL, bbox JSON NOT NULL, min_zoom INTEGER NOT NULL, max_zoom INTEGER NOT NULL, layers JSON NOT NULL, status TEXT NOT NULL, path TEXT, size_bytes INTEGER DEFAULT 0, tile_count INTEGER DEFAULT 0, data JSON NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); """ def connect(db_path: str) -> sqlite3.Connection: Path(db_path).parent.mkdir(parents=True, exist_ok=True) conn = sqlite3.connect(db_path, check_same_thread=False) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") conn.executescript(SCHEMA) return conn