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/stats.py4# Desc: Tableau de bord analytique — contrat commun Groupe KA (ka-ui/stats/5# SPEC.md §2). Agrège la DB réelle (restaurants, menus, item_price_log,6# sync_log) : KPI avec deltas, séries quotidiennes, répartitions,7# géographie, heatmap, tableaux et records. AUCUNE stat inventée :8# une mesure indisponible est simplement absente du JSON.9# Cache mémoire 5 min par période.10# ==============================================================================11from __future__ import annotations1213import json14import statistics15import threading16import time17from collections import Counter18from datetime import date, datetime, timedelta19from zoneinfo import ZoneInfo2021from . import db2223TZ = ZoneInfo("America/Toronto")24CACHE_TTL = 300 # ≥ 5 min (SPEC.md §2)2526_cache: dict[tuple, tuple[float, dict]] = {}27_cache_lock = threading.Lock()2829# libellés FR compacts pour cuisines/types (sous-ensemble de api.ts)30_CUISINE_LABELS = {31 "cafe-dessert": "Café & desserts", "autre": "Autre", "burgers": "Burgers",32 "pizza": "Pizza", "fast-food": "Restauration rapide", "poulet": "Poulet",33 "quebecois": "Québécois", "sushi-japonais": "Sushi & japonais",34 "italien": "Italien", "bbq-grillades": "BBQ & grillades",35 "chinois": "Chinois", "dejeuner-brunch": "Déjeuner & brunch",36 "mexicain": "Mexicain", "libanais-moyen-orient": "Libanais & M-O",37 "thai": "Thaï", "indien": "Indien", "grec": "Grec",38 "vietnamien": "Vietnamien", "coreen": "Coréen", "francais": "Français",39 "fruits-de-mer": "Fruits de mer", "vegetarien": "Végétarien",40}41_TYPE_LABELS = {42 "restaurant": "Restaurant", "fast-food": "Restauration rapide",43 "cafe": "Café", "bar": "Bar", "boulangerie-patisserie":44 "Boulangerie-pâtisserie", "casse-croute": "Casse-croûte",45 "traiteur": "Traiteur", "creme-glacee": "Crème glacée",46}47_PRICE_BUCKETS = [48 ("moins de 10 $", 0, 10), ("10 à 15 $", 10, 15), ("15 à 20 $", 15, 20),49 ("20 à 30 $", 20, 30), ("30 à 50 $", 30, 50), ("50 $ et plus", 50, 1e9),50]5152PERIOD_LABELS = {53 "auj": "Aujourd'hui", "7j": "7 jours", "30j": "30 jours",54 "3m": "3 mois", "6m": "6 mois", "12m": "12 mois",55 "annee": "Année en cours", "tout": "Toute la période",56}575859def _day(ts: float) -> str:60 return datetime.fromtimestamp(ts, TZ).strftime("%Y-%m-%d")616263def _epoch(d: date, end: bool = False) -> float:64 dt = datetime(d.year, d.month, d.day, tzinfo=TZ)65 if end:66 dt += timedelta(days=1)67 return dt.timestamp()686970def _resolve_period(con, period: str, dfrom: str | None,71 dto: str | None) -> tuple[date, date, str]:72 """(from, to, label) — bornes inclusives en dates locales."""73 today = datetime.now(TZ).date()74 if dfrom and dto:75 try:76 f = date.fromisoformat(dfrom)77 t = date.fromisoformat(dto)78 if f <= t:79 return f, t, f"du {f} au {t}"80 except ValueError:81 pass82 days = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}83 if period == "auj":84 return today, today, PERIOD_LABELS["auj"]85 if period in days:86 return today - timedelta(days=days[period] - 1), today, \87 PERIOD_LABELS[period]88 if period == "annee":89 return date(today.year, 1, 1), today, f"Année {today.year}"90 # tout : depuis la première fiche référencée91 row = con.execute("SELECT MIN(first_seen) m FROM restaurants").fetchone()92 start = date.fromtimestamp(row["m"]) if row and row["m"] else today93 return start, today, PERIOD_LABELS["tout"]949596def _delta(cur: float, prev: float) -> float | None:97 if not prev:98 return None99 return round(100.0 * (cur - prev) / prev, 1)100101102def _kpi(id_, label, value, unit="", delta_pct=None, positive_is_up=True):103 k = {"id": id_, "label": label, "value": value, "unit": unit,104 "delta_pct": delta_pct}105 if delta_pct is not None:106 up = delta_pct >= 0 if positive_is_up else delta_pct < 0107 k["direction"] = "up" if up else "down"108 return k109110111def _daily(rows: list, f: date, t: date, cumulative: bool = False,112 base: int = 0) -> list[dict]:113 """Série quotidienne bouchée à zéro sur [f, t] à partir de {jour: n}."""114 by_day = dict(rows)115 n_days = (t - f).days + 1116 pts, acc = [], base117 step = max(1, n_days // 366) # plafonne le nombre de points118 d = f119 while d <= t:120 v = 0121 for k in range(step):122 v += by_day.get((d + timedelta(days=k)).isoformat(), 0)123 acc += v124 pts.append({"t": d.isoformat(), "v": acc if cumulative else v})125 d += timedelta(days=step)126 if pts and pts[-1]["t"] != t.isoformat():127 pts.append({"t": t.isoformat(), "v": acc if cumulative else 0})128 return pts129130131def _iter_menu_items(con):132 for r in con.execute(133 "SELECT m.sections, r.chain, r.name FROM menus m"134 " JOIN restaurants r ON r.uid=m.uid"135 " WHERE r.active=1 AND r.dup_of IS NULL"):136 try:137 sections = json.loads(r["sections"] or "[]")138 except ValueError:139 continue140 for sec in sections:141 for it in sec.get("items") or []:142 yield r, it143144145def _build(period: str, dfrom: str | None, dto: str | None) -> dict:146 con = db.connect()147 try:148 f, t, label = _resolve_period(con, period, dfrom, dto)149 f_ts, t_ts = _epoch(f), _epoch(t, end=True)150 span = (t - f).days + 1151 pf, pt = f - timedelta(days=span), f - timedelta(days=1)152 pf_ts, pt_ts = _epoch(pf), _epoch(pt, end=True)153154 A = "active=1 AND dup_of IS NULL" # restos comptés partout155156 # ------------------------------------------------------------ KPI ---157 total = con.execute(158 f"SELECT COUNT(*) n FROM restaurants WHERE {A}").fetchone()["n"]159 # proxy de stock par first_seen (croissance sur la période)160 stock_end = con.execute(161 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND first_seen<?",162 (t_ts,)).fetchone()["n"]163 stock_start = con.execute(164 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND first_seen<?",165 (f_ts,)).fetchone()["n"]166167 with_menu = con.execute(168 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND EXISTS"169 " (SELECT 1 FROM menus m WHERE m.uid=restaurants.uid)"170 ).fetchone()["n"]171172 items = con.execute(173 "SELECT COALESCE(SUM(m.item_count),0) n FROM menus m"174 " JOIN restaurants r ON r.uid=m.uid"175 " WHERE r.active=1 AND r.dup_of IS NULL").fetchone()["n"]176177 chains = con.execute(178 f"SELECT COUNT(DISTINCT chain) n FROM restaurants WHERE {A}"179 " AND chain IS NOT NULL").fetchone()["n"]180181 new_cur = con.execute(182 f"SELECT COUNT(*) n FROM restaurants WHERE {A}"183 " AND first_seen>=? AND first_seen<?", (f_ts, t_ts)).fetchone()["n"]184 new_prev = con.execute(185 f"SELECT COUNT(*) n FROM restaurants WHERE {A}"186 " AND first_seen>=? AND first_seen<?", (pf_ts, pt_ts)).fetchone()["n"]187188 closed_cur = con.execute(189 "SELECT COUNT(*) n FROM restaurants WHERE dup_of IS NULL"190 " AND status IN ('temporarily_closed','closed')"191 " AND updated_at>=? AND updated_at<?", (f_ts, t_ts)).fetchone()["n"]192 closed_prev = con.execute(193 "SELECT COUNT(*) n FROM restaurants WHERE dup_of IS NULL"194 " AND status IN ('temporarily_closed','closed')"195 " AND updated_at>=? AND updated_at<?", (pf_ts, pt_ts)).fetchone()["n"]196197 kpis = [198 _kpi("total", "Restaurants référencés", total,199 delta_pct=_delta(stock_end, stock_start)),200 _kpi("with_menu", "Restos avec menu & prix", with_menu),201 _kpi("items", "Plats & prix suivis", items),202 _kpi("chains", "Chaînes suivies", chains),203 _kpi("new", "Nouveaux référencés (période)", new_cur,204 delta_pct=_delta(new_cur, new_prev)),205 _kpi("closed", "Fermetures détectées (période)", closed_cur,206 delta_pct=_delta(closed_cur, closed_prev),207 positive_is_up=False),208 ]209210 # --------------------------------------------------------- séries ---211 new_by_day = [(r["d"], r["n"]) for r in con.execute(212 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"213 f" FROM restaurants WHERE {A} AND first_seen>=? AND first_seen<?"214 " GROUP BY d", (f_ts, t_ts))]215 # plats suivis : 1re apparition de chaque item dans l'historique216 items_first = [(r["d"], r["n"]) for r in con.execute(217 "SELECT date(m0,'unixepoch','localtime') d, COUNT(*) n FROM"218 " (SELECT MIN(ts) m0 FROM item_price_log"219 " GROUP BY uid, price_context, item_key)"220 " WHERE m0>=? AND m0<? GROUP BY d", (f_ts, t_ts))]221 items_base = con.execute(222 "SELECT COUNT(*) n FROM (SELECT MIN(ts) m0 FROM item_price_log"223 " GROUP BY uid, price_context, item_key) WHERE m0<?",224 (f_ts,)).fetchone()["n"]225226 series = [227 {"id": "new_restos", "title": "Nouveaux restos référencés par jour",228 "unit": "restos", "kind": "line",229 "points": _daily(new_by_day, f, t)},230 {"id": "items_cum", "title": "Plats & prix suivis (cumul)",231 "unit": "plats", "kind": "line",232 "points": _daily(items_first, f, t, cumulative=True,233 base=items_base)},234 ]235236 # ---------------------------------------------- répartitions (stock) ---237 cuisine_counts: Counter = Counter()238 for r in con.execute(239 f"SELECT cuisines FROM restaurants WHERE {A}"):240 for c in json.loads(r["cuisines"] or "[]"):241 cuisine_counts[c] += 1242 top_cuisines = [243 {"label": _CUISINE_LABELS.get(c, c.capitalize()), "value": n}244 for c, n in cuisine_counts.most_common(8)]245246 price_counts = Counter()247 max_item = (None, 0.0) # (desc, prix) — record réel248 all_prices: list[float] = []249 for r, it in _iter_menu_items(con):250 p = it.get("price")251 if not isinstance(p, (int, float)) or p <= 0:252 continue253 all_prices.append(float(p))254 for lbl, lo, hi in _PRICE_BUCKETS:255 if lo <= p < hi:256 price_counts[lbl] += 1257 break258 if p > max_item[1]:259 max_item = (f"{it.get('name')} — {r['chain'] or r['name']}", p)260 price_items = [{"label": lbl, "value": price_counts[lbl]}261 for lbl, _, _ in _PRICE_BUCKETS if price_counts[lbl]]262263 types = [{"label": _TYPE_LABELS.get(r["t"], r["t"] or "Autre"),264 "value": r["n"]} for r in con.execute(265 f"SELECT establishment_type t, COUNT(*) n FROM restaurants"266 f" WHERE {A} AND establishment_type<>'' GROUP BY t"267 " ORDER BY n DESC")]268269 breakdowns = [270 {"id": "cuisines", "title": "Restos par type de cuisine (top 8)",271 "kind": "donut", "items": top_cuisines},272 {"id": "prix", "title": "Plats par gamme de prix",273 "kind": "bar", "items": price_items},274 {"id": "types", "title": "Par type d'établissement",275 "kind": "bar", "items": types},276 ]277278 # -------------------------------------------------------------- géo ---279 geo = {"title": "Restos par région", "items": [280 {"label": r["region"], "value": r["n"]} for r in con.execute(281 f"SELECT region, COUNT(*) n FROM restaurants WHERE {A}"282 " AND region<>'' GROUP BY region ORDER BY n DESC")]}283284 # ---------------------------------------------------------- heatmap ---285 heat = [{"date": r["d"], "value": r["n"]} for r in con.execute(286 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"287 f" FROM restaurants WHERE {A} GROUP BY d ORDER BY d")]288 heatmap = {"title": "Nouveaux restos référencés", "cells": heat}289290 # --------------------------------------------------------- tableaux ---291 top_cities = [[r["city"], r["n"], r["wm"],292 f"{100.0 * r['n'] / total:.1f} %".replace(".", ",")]293 for r in con.execute(294 f"SELECT city, COUNT(*) n, SUM(EXISTS (SELECT 1 FROM menus m"295 f" WHERE m.uid=restaurants.uid)) wm FROM restaurants WHERE {A}"296 " AND city<>'' GROUP BY city ORDER BY n DESC LIMIT 25")]297298 # top chaînes : succursales, plats du menu le plus complet, prix moyen299 chain_rows: dict[str, dict] = {}300 for r in con.execute(301 f"SELECT chain, COUNT(*) n FROM restaurants WHERE {A}"302 " AND chain IS NOT NULL GROUP BY chain"):303 chain_rows[r["chain"]] = {"locs": r["n"], "items": 0, "prices": []}304 for r in con.execute(305 "SELECT r.chain, m.item_count, m.sections FROM menus m"306 " JOIN restaurants r ON r.uid=m.uid"307 " WHERE r.active=1 AND r.dup_of IS NULL"308 " AND r.chain IS NOT NULL"):309 cr = chain_rows.get(r["chain"])310 if cr is None or (r["item_count"] or 0) <= cr["items"]:311 continue312 cr["items"] = r["item_count"] or 0313 try:314 secs = json.loads(r["sections"] or "[]")315 except ValueError:316 continue317 cr["prices"] = [it["price"] for s in secs318 for it in s.get("items") or []319 if isinstance(it.get("price"), (int, float))320 and it["price"] > 0]321 top_chains = []322 for name, cr in sorted(chain_rows.items(),323 key=lambda kv: -kv[1]["locs"])[:25]:324 avg = (f"{statistics.mean(cr['prices']):.2f} $".replace(".", ",")325 if cr["prices"] else "—")326 top_chains.append([name, cr["locs"], cr["items"] or "—", avg])327328 newest = [[r["name"], r["city"] or "—", r["region"] or "—",329 _day(r["first_seen"])] for r in con.execute(330 f"SELECT name, city, region, first_seen FROM restaurants"331 f" WHERE {A} AND first_seen>=? AND first_seen<?"332 " ORDER BY first_seen DESC LIMIT 100", (f_ts, t_ts))]333334 syncs = [[r["source"], datetime.fromtimestamp(r["ts"], TZ)335 .strftime("%Y-%m-%d %H:%M"), r["found"], r["added"],336 r["updated"], r["removed"],337 "ok" if r["ok"] and r["message"] == "ok"338 else ("alerte" if r["ok"] else "échec")]339 for r in con.execute(340 "SELECT source, ts, found, added, updated, removed, ok, message"341 " FROM sync_log WHERE ts>=? AND ts<? ORDER BY ts DESC LIMIT 50",342 (f_ts, t_ts))]343344 tables = [345 {"id": "villes", "title": "Top villes",346 "columns": ["Ville", "Restos", "Avec menu", "Part"],347 "rows": top_cities},348 {"id": "chaines", "title": "Top chaînes",349 "columns": ["Chaîne", "Succursales", "Plats au menu",350 "Prix moyen"],351 "rows": top_chains},352 ]353 if newest:354 tables.append(355 {"id": "nouveaux", "title": "Nouveaux restos de la période"356 " (100 plus récents)",357 "columns": ["Restaurant", "Ville", "Région", "Ajouté le"],358 "rows": newest})359 if syncs:360 tables.append(361 {"id": "syncs", "title": "Journal des synchronisations",362 "columns": ["Source", "Quand", "Trouvés", "Ajoutés",363 "Mis à jour", "Retirés", "État"],364 "rows": syncs})365366 # ---------------------------------------------------------- records ---367 records = []368 rec_day = con.execute(369 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"370 f" FROM restaurants WHERE {A} GROUP BY d ORDER BY n DESC LIMIT 1"371 ).fetchone()372 if rec_day:373 records.append({"label": "Jour record de référencement",374 "value": f"{rec_day['n']:,} restos".replace(",", " "),375 "date": rec_day["d"]})376 big_chain = max(chain_rows.items(), key=lambda kv: kv[1]["items"],377 default=None)378 if big_chain and big_chain[1]["items"]:379 records.append({"label": "Chaîne au menu le plus étoffé",380 "value": f"{big_chain[0]} — "381 f"{big_chain[1]['items']} plats"})382 if top_cities:383 records.append({"label": "Ville la plus couverte",384 "value": f"{top_cities[0][0]} — "385 f"{top_cities[0][1]:,} restos".replace(",", " ")})386 if geo["items"]:387 g0 = geo["items"][0]388 records.append({"label": "Région la plus couverte",389 "value": f"{g0['label']} — "390 f"{g0['value']:,} restos".replace(",", " ")})391 if all_prices:392 med = statistics.median(all_prices)393 records.append({"label": "Prix médian d'un plat suivi",394 "value": f"{med:.2f} $".replace(".", ",")})395 if max_item[0]:396 records.append({"label": "Plat le plus cher observé",397 "value": f"{max_item[0][:44]} — "398 f"{max_item[1]:.2f} $".replace(".", ",")})399 rec_price_day = con.execute(400 "SELECT date(ts,'unixepoch','localtime') d, COUNT(*) n"401 " FROM item_price_log GROUP BY d ORDER BY n DESC LIMIT 1"402 ).fetchone()403 if rec_price_day:404 records.append({"label": "Jour record de relevés de prix",405 "value": f"{rec_price_day['n']:,} relevés"406 .replace(",", " "),407 "date": rec_price_day["d"]})408409 return {410 "updated": datetime.now(TZ).isoformat(timespec="seconds"),411 "period": {"from": f.isoformat(), "to": t.isoformat(),412 "label": label},413 "kpis": kpis,414 "series": series,415 "breakdowns": breakdowns,416 "geo": geo,417 "heatmap": heatmap,418 "tables": tables,419 "records": records,420 }421 finally:422 con.close()423424425def dashboard(period: str = "30j", dfrom: str | None = None,426 dto: str | None = None) -> dict:427 key = (period, dfrom or "", dto or "")428 now = time.time()429 with _cache_lock:430 hit = _cache.get(key)431 if hit and now - hit[0] < CACHE_TTL:432 return hit[1]433 data = _build(period, dfrom, dto)434 with _cache_lock:435 _cache[key] = (time.time(), data)436 return data437