Resto·Ka — tous les restaurants du Québec, menus complets et prix réels (famille ·Ka)
Python 69.3%
TypeScript 16.7%
CSS 7.9%
JavaScript 4.7%
HTML 1.4%
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# Garde « bootstrap » (source trop jeune pour une médiane fiable) : dès qu'il33# existe AU MOINS un run ok, si le run courant retourne moins de34# BOOTSTRAP_RATIO × le MAXIMUM historique, on suspend les retraits. Protège35# contre un run partiel (miroirs Overpass en 504…) qui, avec <3 runs36# d'historique, passait sous le radar de la garde médiane (incident du run37# OSM #2 : 6 213 trouvés vs 13 531 -> 7 354 fausses quasi-fermetures).38BOOTSTRAP_RATIO = 0.603940# Dérive menu : si le nombre d'items d'un resto chute sous MENU_DROP_RATIO ×41# le compte précédent (précédent >= MENU_DROP_MIN items), on conserve l'ancien42# menu et on consigne l'alerte (connecteur probablement cassé — CLAUDE.md §18).43MENU_DROP_RATIO = 0.2544MENU_DROP_MIN = 84546_SCHEMA = """47CREATE TABLE IF NOT EXISTS restaurants (48 uid TEXT PRIMARY KEY, -- source:external_id49 source TEXT NOT NULL,50 external_id TEXT NOT NULL,51 name TEXT,52 chain TEXT,53 cuisines TEXT, -- JSON (taxonomie §6.1)54 establishment_type TEXT,55 price_range TEXT,56 address TEXT,57 city TEXT,58 region TEXT, -- une des 17 régions59 postal_code TEXT,60 lat REAL,61 lng REAL,62 phone TEXT,63 website TEXT,64 url TEXT,65 hours TEXT, -- JSON66 services TEXT, -- JSON67 dietary_options TEXT, -- JSON68 languages TEXT, -- JSON69 images TEXT, -- JSON (logo + photos, URLs absolues)70 status TEXT DEFAULT 'open',71 geocode_failed INTEGER DEFAULT 0,72 content_hash TEXT,73 first_seen REAL,74 last_seen REAL,75 updated_at REAL,76 miss_count INTEGER DEFAULT 0,77 active INTEGER DEFAULT 1,78 dup_of TEXT, -- uid canonique si doublon inter-sources79 dup_sources TEXT -- JSON : autres sources où le resto est publié80);81CREATE INDEX IF NOT EXISTS idx_restaurants_source ON restaurants(source);82CREATE INDEX IF NOT EXISTS idx_restaurants_city ON restaurants(city);83CREATE INDEX IF NOT EXISTS idx_restaurants_region ON restaurants(region);84CREATE INDEX IF NOT EXISTS idx_restaurants_active ON restaurants(active);85CREATE INDEX IF NOT EXISTS idx_restaurants_geo ON restaurants(lat, lng);8687-- Un menu PAR CONTEXTE de prix : un menu dine-in n'est JAMAIS écrasé par un88-- menu delivery (CLAUDE.md §12.2) — on conserve les deux, l'API sert le89-- meilleur contexte disponible (dine-in > takeout > delivery).90CREATE TABLE IF NOT EXISTS menus (91 uid TEXT NOT NULL, -- restaurants.uid92 price_context TEXT NOT NULL, -- dine-in | takeout | delivery93 price_source TEXT NOT NULL, -- source du prix (traçabilité)94 currency TEXT DEFAULT 'CAD',95 captured_at TEXT, -- ISO-8601 (fraîcheur)96 item_count INTEGER,97 sections TEXT, -- JSON : sections -> items -> options98 PRIMARY KEY (uid, price_context)99);100101-- Historique des prix par item (détection des hausses, crédibilité §12.2).102CREATE TABLE IF NOT EXISTS item_price_log (103 uid TEXT NOT NULL,104 price_context TEXT NOT NULL,105 item_key TEXT NOT NULL, -- "section :: item" normalisé106 ts REAL NOT NULL,107 price REAL108);109CREATE INDEX IF NOT EXISTS idx_item_price_log ON item_price_log(uid, item_key);110111CREATE TABLE IF NOT EXISTS sync_log (112 id INTEGER PRIMARY KEY AUTOINCREMENT,113 source TEXT,114 ts REAL,115 found INTEGER,116 added INTEGER,117 updated INTEGER,118 removed INTEGER,119 ok INTEGER,120 message TEXT,121 stats TEXT -- JSON : items totaux, taux sans prix, alertes…122);123124CREATE TABLE IF NOT EXISTS detail_cache (125 source TEXT NOT NULL,126 external_id TEXT NOT NULL,127 key TEXT,128 payload TEXT,129 fetched_at REAL,130 PRIMARY KEY (source, external_id)131);132133CREATE TABLE IF NOT EXISTS geocode_cache (134 address TEXT PRIMARY KEY,135 lat REAL,136 lng REAL,137 provider TEXT,138 failed INTEGER DEFAULT 0,139 ts REAL140);141142-- Comptes membres — connexion « Se connecter avec KA » (hub groupe-ka.com).143CREATE TABLE IF NOT EXISTS users (144 id INTEGER PRIMARY KEY AUTOINCREMENT,145 hub_sub TEXT UNIQUE, -- identifiant stable du hub (« ka:<sub> »)146 ka_id TEXT UNIQUE, -- identifiant membre « ka-0123456789 »147 email TEXT,148 name TEXT,149 picture TEXT,150 created_at REAL,151 last_login REAL152);153154CREATE TABLE IF NOT EXISTS favorites (155 user_id INTEGER NOT NULL,156 uid TEXT NOT NULL, -- restaurants.uid157 ts REAL,158 PRIMARY KEY (user_id, uid)159);160161-- Inspections alimentaires MAPAQ (« Condamnations des établissements162-- alimentaires », Données Québec, licence CC-BY 4.0). Table LIÉE : le163-- croisement conservateur avec restaurants remplit `uid` (sinon NULL).164CREATE TABLE IF NOT EXISTS inspections (165 id INTEGER PRIMARY KEY AUTOINCREMENT,166 row_hash TEXT UNIQUE, -- anti-doublon au ré-import167 exploitant TEXT, -- Nom_exploitant (entité légale)168 etablissement TEXT, -- Raison_sociale (nom commercial)169 description TEXT, -- Description_infraction170 adresse TEXT, -- Adresse_lieu_infraction (brute)171 postal_code TEXT, -- extrait de l'adresse (A1A1A1)172 type_etablissement TEXT, -- Type_etablissement MAPAQ173 categorie TEXT, -- regroupement (RESTAURATION, LAIT…)174 date_infraction TEXT, -- ISO-8601 (date seulement)175 date_jugement TEXT,176 date_publication TEXT,177 montant_amende REAL,178 loi TEXT,179 motif TEXT, -- SOC_NOM_ARTCL_INFRC (INSALUBRITE…)180 uid TEXT, -- restaurants.uid si croisement réussi181 matched_by TEXT -- nom+ville | adresse+nom (traçabilité)182);183CREATE INDEX IF NOT EXISTS idx_inspections_uid ON inspections(uid);184"""185186187# Colonnes ajoutées après la v1 — migration automatique des bases existantes188# (même mécanisme que louka/db.py).189_MIGRATIONS = {190 "restaurants": {191 "images": "TEXT",192 # enrichissements hors connecteurs (réservation, Yelp, MAPAQ…) —193 # JAMAIS touché par sync_source : survit aux re-crawls des sources194 "details": "TEXT",195 },196}197198199def connect(path: Path | None = None) -> sqlite3.Connection:200 db_path = path or DB_PATH201 db_path.parent.mkdir(parents=True, exist_ok=True)202 con = sqlite3.connect(db_path)203 con.row_factory = sqlite3.Row204 con.executescript(_SCHEMA)205 for table, cols in _MIGRATIONS.items():206 existing = {r["name"] for r in con.execute(f"PRAGMA table_info({table})")}207 for col, decl in cols.items():208 if col not in existing:209 con.execute(f"ALTER TABLE {table} ADD COLUMN {col} {decl}")210 con.commit()211 return con212213214# ---------------------------------------------------------------------------215# Menus & historique des prix216# ---------------------------------------------------------------------------217218def _item_key(section: str, item: str) -> str:219 return f"{(section or '').strip().lower()} :: {(item or '').strip().lower()}"220221222def _menu_items(menu: dict) -> dict[str, float | None]:223 """{item_key: prix} pour la comparaison et l'historique."""224 out: dict[str, float | None] = {}225 for sec in menu.get("sections") or []:226 for it in sec.get("items") or []:227 out[_item_key(sec.get("name"), it.get("name"))] = it.get("price")228 return out229230231def upsert_menu(con: sqlite3.Connection, uid: str, menu: dict,232 now: float) -> dict:233 """Insère/actualise le menu d'un resto POUR SON CONTEXTE de prix.234235 - historise chaque changement de prix d'item (item_price_log) ;236 - garde-fou : chute anormale du nombre d'items -> on conserve l'ancien237 menu et on retourne une alerte (connecteur probablement cassé).238 """239 ctx = menu["price_context"]240 new_items = _menu_items(menu)241 row = con.execute(242 "SELECT sections, item_count FROM menus WHERE uid=? AND price_context=?",243 (uid, ctx)).fetchone()244245 alert = None246 if row is not None:247 old_count = row["item_count"] or 0248 if (old_count >= MENU_DROP_MIN249 and len(new_items) <= MENU_DROP_RATIO * old_count):250 return {"alert": (f"{uid}[{ctx}]: {len(new_items)} item(s) contre "251 f"{old_count} auparavant — ancien menu conservé")}252 # sections est stocké comme liste JSON : reconstruire le dict attendu253 try:254 old_sections = json.loads(row["sections"] or "[]")255 except ValueError:256 old_sections = []257 old_items = _menu_items({"sections": old_sections})258 for key, price in new_items.items():259 if key not in old_items or old_items[key] != price:260 con.execute(261 "INSERT INTO item_price_log (uid, price_context, item_key,"262 " ts, price) VALUES (?,?,?,?,?)",263 (uid, ctx, key, now, price))264 else:265 for key, price in new_items.items():266 if price is not None:267 con.execute(268 "INSERT INTO item_price_log (uid, price_context, item_key,"269 " ts, price) VALUES (?,?,?,?,?)",270 (uid, ctx, key, now, price))271272 con.execute(273 "INSERT INTO menus (uid, price_context, price_source, currency,"274 " captured_at, item_count, sections) VALUES (?,?,?,?,?,?,?)"275 " ON CONFLICT(uid, price_context) DO UPDATE SET"276 " price_source=excluded.price_source, currency=excluded.currency,"277 " captured_at=excluded.captured_at, item_count=excluded.item_count,"278 " sections=excluded.sections",279 (uid, ctx, menu["price_source"], menu.get("currency", "CAD"),280 menu.get("captured_at"), len(new_items),281 json.dumps(menu.get("sections") or [], ensure_ascii=False)))282 return {"alert": alert}283284285# ---------------------------------------------------------------------------286# Synchronisation d'une source287# ---------------------------------------------------------------------------288289def _drift_alert(con: sqlite3.Connection, source: str, found: int) -> str | None:290 hist = con.execute(291 "SELECT found FROM sync_log WHERE source=? AND ok=1"292 " ORDER BY ts DESC LIMIT ?", (source, DRIFT_HISTORY)).fetchall()293 if not hist:294 return None295 # Garde bootstrap : active dès le 2e run, MÊME sans 3 runs d'historique.296 max_found = max(r["found"] for r in hist)297 if max_found >= DRIFT_MIN_BASE and found < BOOTSTRAP_RATIO * max_found:298 return (f"dérive: {found} resto(s) trouvé(s) contre un maximum "299 f"historique de {max_found} — retraits suspendus, "300 "vérifier le connecteur")301 if len(hist) < 3:302 return None303 med_found = statistics.median(r["found"] for r in hist)304 if med_found >= DRIFT_MIN_BASE and found <= DRIFT_RATIO * med_found:305 return (f"dérive: {found} resto(s) trouvé(s) contre une médiane de "306 f"{med_found:.0f} — retraits suspendus, vérifier le connecteur")307 return None308309310def sync_source(con: sqlite3.Connection, source: str,311 restaurants: list[Restaurant],312 partial: bool = False) -> dict:313 """Synchronise les restaurants d'une source.314315 - nouveau resto -> insertion (+ menu, + historique de prix initial)316 - resto modifié -> mise à jour (comparaison de content_hash)317 - resto disparu -> miss_count += 1, puis status=temporarily_closed318 après MISS_GRACE exécutions (délai de grâce, jamais de suppression)319 - dérive détectée -> alerte consignée, retraits suspendus320 - `partial=True` -> le connecteur signale un run incomplet (un321 sous-ensemble de requêtes a échoué) : retraits suspendus d'office322 """323 now = time.time()324 added = updated = 0325 seen_uids = set()326 menu_alerts: list[str] = []327328 n = len(restaurants)329 total_items = 0330 items_sans_prix = 0331 for r in restaurants:332 if r.menu:333 for sec in r.menu.get("sections") or []:334 for it in sec.get("items") or []:335 total_items += 1336 if it.get("price") is None:337 items_sans_prix += 1338339 alert = _drift_alert(con, source, n)340 if partial and not alert:341 alert = ("run partiel signalé par le connecteur — "342 "retraits suspendus")343344 for resto in restaurants:345 seen_uids.add(resto.uid)346 h = resto.content_hash()347 row = con.execute("SELECT content_hash FROM restaurants WHERE uid=?",348 (resto.uid,)).fetchone()349 params = dict(350 uid=resto.uid, source=resto.source, external_id=resto.external_id,351 name=resto.name, chain=resto.chain,352 cuisines=json.dumps(resto.cuisines, ensure_ascii=False),353 establishment_type=resto.establishment_type,354 price_range=resto.price_range, address=resto.address,355 city=resto.city, region=resto.region,356 postal_code=resto.postal_code, lat=resto.lat, lng=resto.lng,357 phone=resto.phone, website=resto.website, url=resto.url,358 hours=json.dumps(resto.hours, ensure_ascii=False),359 services=json.dumps(resto.services, ensure_ascii=False),360 dietary_options=json.dumps(resto.dietary_options, ensure_ascii=False),361 languages=json.dumps(resto.languages, ensure_ascii=False),362 images=json.dumps(resto.images, ensure_ascii=False),363 status=resto.status, content_hash=h, now=now,364 )365 if row is None:366 con.execute(367 """INSERT INTO restaurants (uid, source, external_id, name,368 chain, cuisines, establishment_type, price_range, address,369 city, region, postal_code, lat, lng, phone, website, url,370 hours, services, dietary_options, languages, images,371 status, content_hash, first_seen, last_seen, updated_at,372 miss_count, active)373 VALUES (:uid,:source,:external_id,:name,:chain,:cuisines,374 :establishment_type,:price_range,:address,:city,:region,375 :postal_code,:lat,:lng,:phone,:website,:url,:hours,376 :services,:dietary_options,:languages,:images,:status,377 :content_hash,:now,:now,:now,0,1)""", params)378 added += 1379 elif row["content_hash"] != h:380 # COALESCE : ne jamais écraser des coordonnées géocodées par null381 con.execute(382 """UPDATE restaurants SET name=:name, chain=:chain,383 cuisines=:cuisines, establishment_type=:establishment_type,384 price_range=:price_range, address=:address, city=:city,385 region=:region, postal_code=:postal_code,386 lat=COALESCE(:lat, lat), lng=COALESCE(:lng, lng),387 phone=:phone, website=:website, url=:url, hours=:hours,388 services=:services, dietary_options=:dietary_options,389 languages=:languages, images=:images, status=:status,390 content_hash=:content_hash, last_seen=:now, updated_at=:now,391 miss_count=0, active=1 WHERE uid=:uid""", params)392 updated += 1393 else:394 con.execute(395 "UPDATE restaurants SET last_seen=?, miss_count=0, active=1,"396 " status='open' WHERE uid=?", (now, resto.uid))397398 if resto.menu:399 res = upsert_menu(con, resto.uid, resto.menu, now)400 if res.get("alert"):401 menu_alerts.append(res["alert"])402403 # Restos de cette source qui n'apparaissent plus : délai de grâce, puis404 # status=temporarily_closed (politique de grâce §13 — jamais de suppression).405 removed = missed = 0406 if not alert:407 for r in con.execute(408 "SELECT uid, miss_count FROM restaurants WHERE source=? AND active=1",409 (source,)).fetchall():410 if r["uid"] in seen_uids:411 continue412 missed += 1413 if r["miss_count"] + 1 >= MISS_GRACE:414 con.execute(415 "UPDATE restaurants SET active=0, status='temporarily_closed',"416 " miss_count=?, updated_at=? WHERE uid=?",417 (r["miss_count"] + 1, now, r["uid"]))418 removed += 1419 else:420 con.execute(421 "UPDATE restaurants SET miss_count=miss_count+1 WHERE uid=?",422 (r["uid"],))423424 stats = {425 "menu_items": total_items,426 "null_price_rate": (round(items_sans_prix / total_items, 3)427 if total_items else 0.0),428 "missed": missed,429 }430 if alert:431 stats["alert"] = alert432 if menu_alerts:433 stats["menu_alerts"] = menu_alerts434 con.execute(435 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok,"436 " message, stats) VALUES (?,?,?,?,?,?,1,?,?)",437 (source, now, n, added, updated, removed, alert or "ok",438 json.dumps(stats, ensure_ascii=False)))439 con.commit()440 out = {"source": source, "found": n, "added": added, "updated": updated,441 "removed": removed, "menu_items": total_items}442 if alert:443 out["alert"] = alert444 if menu_alerts:445 out["menu_alerts"] = len(menu_alerts)446 return out447448449def log_failure(con: sqlite3.Connection, source: str, message: str) -> None:450 con.execute(451 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok, message)"452 " VALUES (?,?,0,0,0,0,0,?)", (source, time.time(), message))453 con.commit()454455456# ---------------------------------------------------------------------------457# Enrichissements hors connecteurs (colonne `details` + COALESCE conservateur)458# ---------------------------------------------------------------------------459460def merge_details(con: sqlite3.Connection, uid: str, patch: dict) -> bool:461 """Fusionne `patch` dans la colonne JSON `details` du resto (additif :462 les clés du patch écrasent seulement leurs propres clés). Retourne False463 si le resto n'existe pas (encore)."""464 row = con.execute("SELECT details FROM restaurants WHERE uid=?",465 (uid,)).fetchone()466 if row is None:467 return False468 try:469 details = json.loads(row["details"] or "{}")470 except ValueError:471 details = {}472 details.update({k: v for k, v in patch.items() if v not in (None, "", {})})473 con.execute("UPDATE restaurants SET details=? WHERE uid=?",474 (json.dumps(details, ensure_ascii=False), uid))475 return True476477478def enrich_contact(con: sqlite3.Connection, uid: str, phone: str = "",479 hours: dict | None = None, details: dict | None = None) -> None:480 """Enrichissement CONSERVATEUR d'une fiche : ne remplit que les trous.481482 - phone : seulement si la colonne est vide (COALESCE) ;483 - hours : fusion — n'ajoute que les clés absentes (le connecteur d'origine484 garde la priorité) ;485 - details : fusion via merge_details (reservation_url, yelp, mapaq…).486 """487 row = con.execute("SELECT phone, hours FROM restaurants WHERE uid=?",488 (uid,)).fetchone()489 if row is None:490 return491 if phone and not (row["phone"] or "").strip():492 con.execute("UPDATE restaurants SET phone=? WHERE uid=?", (phone, uid))493 if hours:494 try:495 cur = json.loads(row["hours"] or "{}")496 except ValueError:497 cur = {}498 add = {k: v for k, v in hours.items() if k not in cur}499 if add:500 cur.update(add)501 con.execute("UPDATE restaurants SET hours=? WHERE uid=?",502 (json.dumps(cur, ensure_ascii=False), uid))503 if details:504 merge_details(con, uid, details)505506507# ---------------------------------------------------------------------------508# Cache des pages détail (« détail si nouveau/modifié »)509# ---------------------------------------------------------------------------510511def get_cached_detail(con: sqlite3.Connection, source: str,512 external_id: str, key: str) -> dict | None:513 row = con.execute(514 "SELECT key, payload FROM detail_cache WHERE source=? AND external_id=?",515 (source, external_id)).fetchone()516 if row and row["key"] == key and row["payload"]:517 try:518 return json.loads(row["payload"])519 except ValueError:520 return None521 return None522523524def put_cached_detail(con: sqlite3.Connection, source: str,525 external_id: str, key: str, payload: dict) -> None:526 con.execute(527 "INSERT INTO detail_cache (source, external_id, key, payload, fetched_at)"528 " VALUES (?,?,?,?,?)"529 " ON CONFLICT(source, external_id) DO UPDATE SET"530 " key=excluded.key, payload=excluded.payload, fetched_at=excluded.fetched_at",531 (source, external_id, key, json.dumps(payload, ensure_ascii=False),532 time.time()))533 con.commit()534