Resto·Ka — tous les restaurants du Québec, menus complets et prix réels (famille ·Ka)
1# ==============================================================================2# Author: Simon-Pierre Boucher <contact@spboucher.ai>3# File: restoka/db.py4# Desc: Persistance SQLite — upsert des restaurants avec détection de5# changements, menus conservés PAR CONTEXTE de prix (dine-in/takeout/6# delivery, jamais écrasés entre eux), historique des prix par item,7# cycle de vie avec délai de grâce, détection de dérive (connecteur8# cassé), caches détail et géocodage. Calqué sur louka/db.py.9# ==============================================================================10from __future__ import annotations1112import json13import sqlite314import statistics15import time16from pathlib import Path1718from .schema import Restaurant1920DB_PATH = Path(__file__).resolve().parent.parent / "data" / "restoka.db"2122# Nombre d'exécutions consécutives où un resto doit être absent de la source23# avant d'être marqué temporarily_closed (délai de grâce contre les ratés).24MISS_GRACE = 22526# Dérive source : si une source retourne <= DRIFT_RATIO × sa médiane historique27# (médiane >= DRIFT_MIN_BASE restos), on alerte et on suspend les retraits.28DRIFT_RATIO = 0.2529DRIFT_MIN_BASE = 430DRIFT_HISTORY = 53132# Dérive menu : si le nombre d'items d'un resto chute sous MENU_DROP_RATIO ×33# le compte précédent (précédent >= MENU_DROP_MIN items), on conserve l'ancien34# menu et on consigne l'alerte (connecteur probablement cassé — CLAUDE.md §18).35MENU_DROP_RATIO = 0.2536MENU_DROP_MIN = 83738_SCHEMA = """39CREATE TABLE IF NOT EXISTS restaurants (40 uid TEXT PRIMARY KEY, -- source:external_id41 source TEXT NOT NULL,42 external_id TEXT NOT NULL,43 name TEXT,44 chain TEXT,45 cuisines TEXT, -- JSON (taxonomie §6.1)46 establishment_type TEXT,47 price_range TEXT,48 address TEXT,49 city TEXT,50 region TEXT, -- une des 17 régions51 postal_code TEXT,52 lat REAL,53 lng REAL,54 phone TEXT,55 website TEXT,56 url TEXT,57 hours TEXT, -- JSON58 services TEXT, -- JSON59 dietary_options TEXT, -- JSON60 languages TEXT, -- JSON61 images TEXT, -- JSON (logo + photos, URLs absolues)62 status TEXT DEFAULT 'open',63 geocode_failed INTEGER DEFAULT 0,64 content_hash TEXT,65 first_seen REAL,66 last_seen REAL,67 updated_at REAL,68 miss_count INTEGER DEFAULT 0,69 active INTEGER DEFAULT 1,70 dup_of TEXT, -- uid canonique si doublon inter-sources71 dup_sources TEXT -- JSON : autres sources où le resto est publié72);73CREATE INDEX IF NOT EXISTS idx_restaurants_source ON restaurants(source);74CREATE INDEX IF NOT EXISTS idx_restaurants_city ON restaurants(city);75CREATE INDEX IF NOT EXISTS idx_restaurants_region ON restaurants(region);76CREATE INDEX IF NOT EXISTS idx_restaurants_active ON restaurants(active);77CREATE INDEX IF NOT EXISTS idx_restaurants_geo ON restaurants(lat, lng);7879-- Un menu PAR CONTEXTE de prix : un menu dine-in n'est JAMAIS écrasé par un80-- menu delivery (CLAUDE.md §12.2) — on conserve les deux, l'API sert le81-- meilleur contexte disponible (dine-in > takeout > delivery).82CREATE TABLE IF NOT EXISTS menus (83 uid TEXT NOT NULL, -- restaurants.uid84 price_context TEXT NOT NULL, -- dine-in | takeout | delivery85 price_source TEXT NOT NULL, -- source du prix (traçabilité)86 currency TEXT DEFAULT 'CAD',87 captured_at TEXT, -- ISO-8601 (fraîcheur)88 item_count INTEGER,89 sections TEXT, -- JSON : sections -> items -> options90 PRIMARY KEY (uid, price_context)91);9293-- Historique des prix par item (détection des hausses, crédibilité §12.2).94CREATE TABLE IF NOT EXISTS item_price_log (95 uid TEXT NOT NULL,96 price_context TEXT NOT NULL,97 item_key TEXT NOT NULL, -- "section :: item" normalisé98 ts REAL NOT NULL,99 price REAL100);101CREATE INDEX IF NOT EXISTS idx_item_price_log ON item_price_log(uid, item_key);102103CREATE TABLE IF NOT EXISTS sync_log (104 id INTEGER PRIMARY KEY AUTOINCREMENT,105 source TEXT,106 ts REAL,107 found INTEGER,108 added INTEGER,109 updated INTEGER,110 removed INTEGER,111 ok INTEGER,112 message TEXT,113 stats TEXT -- JSON : items totaux, taux sans prix, alertes…114);115116CREATE TABLE IF NOT EXISTS detail_cache (117 source TEXT NOT NULL,118 external_id TEXT NOT NULL,119 key TEXT,120 payload TEXT,121 fetched_at REAL,122 PRIMARY KEY (source, external_id)123);124125CREATE TABLE IF NOT EXISTS geocode_cache (126 address TEXT PRIMARY KEY,127 lat REAL,128 lng REAL,129 provider TEXT,130 failed INTEGER DEFAULT 0,131 ts REAL132);133134-- Comptes membres — connexion « Se connecter avec KA » (hub groupe-ka.com).135CREATE TABLE IF NOT EXISTS users (136 id INTEGER PRIMARY KEY AUTOINCREMENT,137 hub_sub TEXT UNIQUE, -- identifiant stable du hub (« ka:<sub> »)138 ka_id TEXT UNIQUE, -- identifiant membre « ka-0123456789 »139 email TEXT,140 name TEXT,141 picture TEXT,142 created_at REAL,143 last_login REAL144);145146CREATE TABLE IF NOT EXISTS favorites (147 user_id INTEGER NOT NULL,148 uid TEXT NOT NULL, -- restaurants.uid149 ts REAL,150 PRIMARY KEY (user_id, uid)151);152"""153154155# Colonnes ajoutées après la v1 — migration automatique des bases existantes156# (même mécanisme que louka/db.py).157_MIGRATIONS = {158 "restaurants": {159 "images": "TEXT",160 },161}162163164def connect(path: Path | None = None) -> sqlite3.Connection:165 db_path = path or DB_PATH166 db_path.parent.mkdir(parents=True, exist_ok=True)167 con = sqlite3.connect(db_path)168 con.row_factory = sqlite3.Row169 con.executescript(_SCHEMA)170 for table, cols in _MIGRATIONS.items():171 existing = {r["name"] for r in con.execute(f"PRAGMA table_info({table})")}172 for col, decl in cols.items():173 if col not in existing:174 con.execute(f"ALTER TABLE {table} ADD COLUMN {col} {decl}")175 con.commit()176 return con177178179# ---------------------------------------------------------------------------180# Menus & historique des prix181# ---------------------------------------------------------------------------182183def _item_key(section: str, item: str) -> str:184 return f"{(section or '').strip().lower()} :: {(item or '').strip().lower()}"185186187def _menu_items(menu: dict) -> dict[str, float | None]:188 """{item_key: prix} pour la comparaison et l'historique."""189 out: dict[str, float | None] = {}190 for sec in menu.get("sections") or []:191 for it in sec.get("items") or []:192 out[_item_key(sec.get("name"), it.get("name"))] = it.get("price")193 return out194195196def upsert_menu(con: sqlite3.Connection, uid: str, menu: dict,197 now: float) -> dict:198 """Insère/actualise le menu d'un resto POUR SON CONTEXTE de prix.199200 - historise chaque changement de prix d'item (item_price_log) ;201 - garde-fou : chute anormale du nombre d'items -> on conserve l'ancien202 menu et on retourne une alerte (connecteur probablement cassé).203 """204 ctx = menu["price_context"]205 new_items = _menu_items(menu)206 row = con.execute(207 "SELECT sections, item_count FROM menus WHERE uid=? AND price_context=?",208 (uid, ctx)).fetchone()209210 alert = None211 if row is not None:212 old_count = row["item_count"] or 0213 if (old_count >= MENU_DROP_MIN214 and len(new_items) <= MENU_DROP_RATIO * old_count):215 return {"alert": (f"{uid}[{ctx}]: {len(new_items)} item(s) contre "216 f"{old_count} auparavant — ancien menu conservé")}217 # sections est stocké comme liste JSON : reconstruire le dict attendu218 try:219 old_sections = json.loads(row["sections"] or "[]")220 except ValueError:221 old_sections = []222 old_items = _menu_items({"sections": old_sections})223 for key, price in new_items.items():224 if key not in old_items or old_items[key] != price:225 con.execute(226 "INSERT INTO item_price_log (uid, price_context, item_key,"227 " ts, price) VALUES (?,?,?,?,?)",228 (uid, ctx, key, now, price))229 else:230 for key, price in new_items.items():231 if price is not None:232 con.execute(233 "INSERT INTO item_price_log (uid, price_context, item_key,"234 " ts, price) VALUES (?,?,?,?,?)",235 (uid, ctx, key, now, price))236237 con.execute(238 "INSERT INTO menus (uid, price_context, price_source, currency,"239 " captured_at, item_count, sections) VALUES (?,?,?,?,?,?,?)"240 " ON CONFLICT(uid, price_context) DO UPDATE SET"241 " price_source=excluded.price_source, currency=excluded.currency,"242 " captured_at=excluded.captured_at, item_count=excluded.item_count,"243 " sections=excluded.sections",244 (uid, ctx, menu["price_source"], menu.get("currency", "CAD"),245 menu.get("captured_at"), len(new_items),246 json.dumps(menu.get("sections") or [], ensure_ascii=False)))247 return {"alert": alert}248249250# ---------------------------------------------------------------------------251# Synchronisation d'une source252# ---------------------------------------------------------------------------253254def _drift_alert(con: sqlite3.Connection, source: str, found: int) -> str | None:255 hist = con.execute(256 "SELECT found FROM sync_log WHERE source=? AND ok=1"257 " ORDER BY ts DESC LIMIT ?", (source, DRIFT_HISTORY)).fetchall()258 if len(hist) < 3:259 return None260 med_found = statistics.median(r["found"] for r in hist)261 if med_found >= DRIFT_MIN_BASE and found <= DRIFT_RATIO * med_found:262 return (f"dérive: {found} resto(s) trouvé(s) contre une médiane de "263 f"{med_found:.0f} — retraits suspendus, vérifier le connecteur")264 return None265266267def sync_source(con: sqlite3.Connection, source: str,268 restaurants: list[Restaurant]) -> dict:269 """Synchronise les restaurants d'une source.270271 - nouveau resto -> insertion (+ menu, + historique de prix initial)272 - resto modifié -> mise à jour (comparaison de content_hash)273 - resto disparu -> miss_count += 1, puis status=temporarily_closed274 après MISS_GRACE exécutions (délai de grâce, jamais de suppression)275 - dérive détectée -> alerte consignée, retraits suspendus276 """277 now = time.time()278 added = updated = 0279 seen_uids = set()280 menu_alerts: list[str] = []281282 n = len(restaurants)283 total_items = 0284 items_sans_prix = 0285 for r in restaurants:286 if r.menu:287 for sec in r.menu.get("sections") or []:288 for it in sec.get("items") or []:289 total_items += 1290 if it.get("price") is None:291 items_sans_prix += 1292293 alert = _drift_alert(con, source, n)294295 for resto in restaurants:296 seen_uids.add(resto.uid)297 h = resto.content_hash()298 row = con.execute("SELECT content_hash FROM restaurants WHERE uid=?",299 (resto.uid,)).fetchone()300 params = dict(301 uid=resto.uid, source=resto.source, external_id=resto.external_id,302 name=resto.name, chain=resto.chain,303 cuisines=json.dumps(resto.cuisines, ensure_ascii=False),304 establishment_type=resto.establishment_type,305 price_range=resto.price_range, address=resto.address,306 city=resto.city, region=resto.region,307 postal_code=resto.postal_code, lat=resto.lat, lng=resto.lng,308 phone=resto.phone, website=resto.website, url=resto.url,309 hours=json.dumps(resto.hours, ensure_ascii=False),310 services=json.dumps(resto.services, ensure_ascii=False),311 dietary_options=json.dumps(resto.dietary_options, ensure_ascii=False),312 languages=json.dumps(resto.languages, ensure_ascii=False),313 images=json.dumps(resto.images, ensure_ascii=False),314 status=resto.status, content_hash=h, now=now,315 )316 if row is None:317 con.execute(318 """INSERT INTO restaurants (uid, source, external_id, name,319 chain, cuisines, establishment_type, price_range, address,320 city, region, postal_code, lat, lng, phone, website, url,321 hours, services, dietary_options, languages, images,322 status, content_hash, first_seen, last_seen, updated_at,323 miss_count, active)324 VALUES (:uid,:source,:external_id,:name,:chain,:cuisines,325 :establishment_type,:price_range,:address,:city,:region,326 :postal_code,:lat,:lng,:phone,:website,:url,:hours,327 :services,:dietary_options,:languages,:images,:status,328 :content_hash,:now,:now,:now,0,1)""", params)329 added += 1330 elif row["content_hash"] != h:331 # COALESCE : ne jamais écraser des coordonnées géocodées par null332 con.execute(333 """UPDATE restaurants SET name=:name, chain=:chain,334 cuisines=:cuisines, establishment_type=:establishment_type,335 price_range=:price_range, address=:address, city=:city,336 region=:region, postal_code=:postal_code,337 lat=COALESCE(:lat, lat), lng=COALESCE(:lng, lng),338 phone=:phone, website=:website, url=:url, hours=:hours,339 services=:services, dietary_options=:dietary_options,340 languages=:languages, images=:images, status=:status,341 content_hash=:content_hash, last_seen=:now, updated_at=:now,342 miss_count=0, active=1 WHERE uid=:uid""", params)343 updated += 1344 else:345 con.execute(346 "UPDATE restaurants SET last_seen=?, miss_count=0, active=1,"347 " status='open' WHERE uid=?", (now, resto.uid))348349 if resto.menu:350 res = upsert_menu(con, resto.uid, resto.menu, now)351 if res.get("alert"):352 menu_alerts.append(res["alert"])353354 # Restos de cette source qui n'apparaissent plus : délai de grâce, puis355 # status=temporarily_closed (politique de grâce §13 — jamais de suppression).356 removed = missed = 0357 if not alert:358 for r in con.execute(359 "SELECT uid, miss_count FROM restaurants WHERE source=? AND active=1",360 (source,)).fetchall():361 if r["uid"] in seen_uids:362 continue363 missed += 1364 if r["miss_count"] + 1 >= MISS_GRACE:365 con.execute(366 "UPDATE restaurants SET active=0, status='temporarily_closed',"367 " miss_count=?, updated_at=? WHERE uid=?",368 (r["miss_count"] + 1, now, r["uid"]))369 removed += 1370 else:371 con.execute(372 "UPDATE restaurants SET miss_count=miss_count+1 WHERE uid=?",373 (r["uid"],))374375 stats = {376 "menu_items": total_items,377 "null_price_rate": (round(items_sans_prix / total_items, 3)378 if total_items else 0.0),379 "missed": missed,380 }381 if alert:382 stats["alert"] = alert383 if menu_alerts:384 stats["menu_alerts"] = menu_alerts385 con.execute(386 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok,"387 " message, stats) VALUES (?,?,?,?,?,?,1,?,?)",388 (source, now, n, added, updated, removed, alert or "ok",389 json.dumps(stats, ensure_ascii=False)))390 con.commit()391 out = {"source": source, "found": n, "added": added, "updated": updated,392 "removed": removed, "menu_items": total_items}393 if alert:394 out["alert"] = alert395 if menu_alerts:396 out["menu_alerts"] = len(menu_alerts)397 return out398399400def log_failure(con: sqlite3.Connection, source: str, message: str) -> None:401 con.execute(402 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok, message)"403 " VALUES (?,?,0,0,0,0,0,?)", (source, time.time(), message))404 con.commit()405406407# ---------------------------------------------------------------------------408# Cache des pages détail (« détail si nouveau/modifié »)409# ---------------------------------------------------------------------------410411def get_cached_detail(con: sqlite3.Connection, source: str,412 external_id: str, key: str) -> dict | None:413 row = con.execute(414 "SELECT key, payload FROM detail_cache WHERE source=? AND external_id=?",415 (source, external_id)).fetchone()416 if row and row["key"] == key and row["payload"]:417 try:418 return json.loads(row["payload"])419 except ValueError:420 return None421 return None422423424def put_cached_detail(con: sqlite3.Connection, source: str,425 external_id: str, key: str, payload: dict) -> None:426 con.execute(427 "INSERT INTO detail_cache (source, external_id, key, payload, fetched_at)"428 " VALUES (?,?,?,?,?)"429 " ON CONFLICT(source, external_id) DO UPDATE SET"430 " key=excluded.key, payload=excluded.payload, fetched_at=excluded.fetched_at",431 (source, external_id, key, json.dumps(payload, ensure_ascii=False),432 time.time()))433 con.commit()434