"""CRUD stores over the SQLite JSON-blob tables.""" from __future__ import annotations import json import sqlite3 import threading from typing import Optional from app.models.assets import Asset from app.models.map_pack import MapPack from app.models.route import TrekkerRoute, utcnow_iso from app.models.trail_guide import TrailGuide class Store: """All-table store. Serializes writes with a lock (SQLite, multi-thread app).""" def __init__(self, conn: sqlite3.Connection): self.conn = conn self.lock = threading.Lock() # -- routes ------------------------------------------------------------ def save_route(self, route: TrekkerRoute) -> TrekkerRoute: route.updated_at = utcnow_iso() with self.lock, self.conn: self.conn.execute( """INSERT INTO routes (id, name, source, data, trail_guide_id, created_at, updated_at) VALUES (?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, source=excluded.source, data=excluded.data, trail_guide_id=excluded.trail_guide_id, updated_at=excluded.updated_at""", (route.id, route.name, route.source, route.model_dump_json(), route.trail_guide_id, route.created_at, route.updated_at), ) return route def get_route(self, route_id: str) -> Optional[TrekkerRoute]: row = self.conn.execute("SELECT data FROM routes WHERE id=?", (route_id,)).fetchone() return TrekkerRoute.model_validate_json(row["data"]) if row else None def list_routes(self) -> list[TrekkerRoute]: rows = self.conn.execute("SELECT data FROM routes ORDER BY created_at DESC").fetchall() return [TrekkerRoute.model_validate_json(r["data"]) for r in rows] def delete_route(self, route_id: str) -> bool: with self.lock, self.conn: cur = self.conn.execute("DELETE FROM routes WHERE id=?", (route_id,)) return cur.rowcount > 0 def link_trail_guide(self, route_id: str, guide_id: Optional[str]) -> Optional[TrekkerRoute]: route = self.get_route(route_id) if route is None: return None if guide_id is not None and self.get_trail_guide(guide_id) is None: raise KeyError(f"trail guide {guide_id} not found") route.trail_guide_id = guide_id return self.save_route(route) # -- trail guides -------------------------------------------------------- def save_trail_guide(self, guide: TrailGuide) -> TrailGuide: guide.updated_at = utcnow_iso() with self.lock, self.conn: self.conn.execute( """INSERT INTO trail_guides (id, name, source_url, pdf_path, data, created_at, updated_at) VALUES (?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, source_url=excluded.source_url, pdf_path=excluded.pdf_path, data=excluded.data, updated_at=excluded.updated_at""", (guide.id, guide.name, guide.source_url, guide.pdf_path, guide.model_dump_json(), guide.created_at, guide.updated_at), ) return guide def get_trail_guide(self, guide_id: str) -> Optional[TrailGuide]: row = self.conn.execute("SELECT data FROM trail_guides WHERE id=?", (guide_id,)).fetchone() return TrailGuide.model_validate_json(row["data"]) if row else None def list_trail_guides(self) -> list[TrailGuide]: rows = self.conn.execute("SELECT data FROM trail_guides ORDER BY created_at DESC").fetchall() return [TrailGuide.model_validate_json(r["data"]) for r in rows] def delete_trail_guide(self, guide_id: str) -> bool: with self.lock, self.conn: cur = self.conn.execute("DELETE FROM trail_guides WHERE id=?", (guide_id,)) return cur.rowcount > 0 # -- assets --------------------------------------------------------------- def save_asset(self, asset: Asset) -> Asset: with self.lock, self.conn: self.conn.execute( """INSERT INTO assets (id, route_id, generator, data, created_at) VALUES (?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET data=excluded.data""", (asset.id, asset.route_id, asset.generator, asset.model_dump_json(), asset.created_at), ) return asset def list_assets(self, route_id: str) -> list[Asset]: rows = self.conn.execute( "SELECT data FROM assets WHERE route_id=? ORDER BY created_at DESC", (route_id,) ).fetchall() return [Asset.model_validate_json(r["data"]) for r in rows] # -- weather cache ---------------------------------------------------------- def save_weather(self, route_id: str, data: dict) -> None: with self.lock, self.conn: self.conn.execute( """INSERT INTO weather_cache (route_id, data, fetched_at) VALUES (?,?,?) ON CONFLICT(route_id) DO UPDATE SET data=excluded.data, fetched_at=excluded.fetched_at""", (route_id, json.dumps(data), utcnow_iso()), ) def get_weather(self, route_id: str) -> Optional[dict]: row = self.conn.execute( "SELECT data, fetched_at FROM weather_cache WHERE route_id=?", (route_id,) ).fetchone() if not row: return None data = json.loads(row["data"]) data["fetched_at"] = row["fetched_at"] return data # -- offline map packs ----------------------------------------------------- def save_map_pack(self, pack: MapPack) -> MapPack: pack.updated_at = utcnow_iso() with self.lock, self.conn: self.conn.execute( """INSERT INTO offline_map_packs (id, name, bbox, min_zoom, max_zoom, layers, status, path, size_bytes, tile_count, data, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, status=excluded.status, path=excluded.path, size_bytes=excluded.size_bytes, tile_count=excluded.tile_count, data=excluded.data, updated_at=excluded.updated_at""", (pack.id, pack.name, json.dumps(pack.bbox), pack.min_zoom, pack.max_zoom, json.dumps(pack.layers), pack.status, pack.path, pack.size_bytes, pack.tile_count, pack.model_dump_json(), pack.created_at, pack.updated_at), ) return pack def get_map_pack(self, pack_id: str) -> Optional[MapPack]: row = self.conn.execute("SELECT data FROM offline_map_packs WHERE id=?", (pack_id,)).fetchone() return MapPack.model_validate_json(row["data"]) if row else None def list_map_packs(self) -> list[MapPack]: rows = self.conn.execute("SELECT data FROM offline_map_packs ORDER BY created_at DESC").fetchall() return [MapPack.model_validate_json(r["data"]) for r in rows] def delete_map_pack(self, pack_id: str) -> bool: with self.lock, self.conn: cur = self.conn.execute("DELETE FROM offline_map_packs WHERE id=?", (pack_id,)) return cur.rowcount > 0