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/stats.py4# Desc: Tableau de bord analytique — contrat commun Groupe KA v2 (ka-ui/5# stats/SPEC.md §2). Agrège la DB réelle (restaurants, menus,6# item_price_log, sync_log, details RACJ/Yelp/MAPAQ) : KPI avec deltas7# et sparklines, jauges de complétude, séries quotidiennes, multi-8# courbes (prix moyen par ville), barres empilées (ajouts par source),9# répartitions, distributions, géographie, heatmaps calendrier +10# horaire, tableaux et records. AUCUNE stat inventée : une mesure11# indisponible est simplement absente du JSON.12# Cache mémoire 5 min par période.13# ==============================================================================14from __future__ import annotations1516import json17import statistics18import threading19import time20from collections import Counter21from datetime import date, datetime, timedelta22from zoneinfo import ZoneInfo2324from . import db2526TZ = ZoneInfo("America/Toronto")27CACHE_TTL = 300 # ≥ 5 min (SPEC.md §2)2829_cache: dict[tuple, tuple[float, dict]] = {}30_cache_lock = threading.Lock()3132# libellés FR compacts pour cuisines/types (sous-ensemble de api.ts)33_CUISINE_LABELS = {34 "cafe-dessert": "Café & desserts", "autre": "Autre", "burgers": "Burgers",35 "pizza": "Pizza", "fast-food": "Restauration rapide", "poulet": "Poulet",36 "quebecois": "Québécois", "sushi-japonais": "Sushi & japonais",37 "italien": "Italien", "bbq-grillades": "BBQ & grillades",38 "chinois": "Chinois", "dejeuner-brunch": "Déjeuner & brunch",39 "mexicain": "Mexicain", "libanais-moyen-orient": "Libanais & M-O",40 "thai": "Thaï", "indien": "Indien", "grec": "Grec",41 "vietnamien": "Vietnamien", "coreen": "Coréen", "francais": "Français",42 "fruits-de-mer": "Fruits de mer", "vegetarien": "Végétarien",43}44_TYPE_LABELS = {45 "restaurant": "Restaurant", "fast-food": "Restauration rapide",46 "cafe": "Café", "bar": "Bar", "boulangerie-patisserie":47 "Boulangerie-pâtisserie", "casse-croute": "Casse-croûte",48 "traiteur": "Traiteur", "creme-glacee": "Crème glacée",49}50_CTX_LABELS = {"dine-in": "En salle", "takeout": "Pour emporter",51 "delivery": "Livraison"}52_PRICE_BINS = [53 ("< 5 $", 0, 5), ("5-10 $", 5, 10), ("10-15 $", 10, 15),54 ("15-20 $", 15, 20), ("20-25 $", 20, 25), ("25-30 $", 25, 30),55 ("30-40 $", 30, 40), ("40-50 $", 40, 50), ("50 $ +", 50, 1e9),56]57_CAP_BINS = [58 ("1-50", 1, 50), ("50-100", 50, 100), ("100-200", 100, 200),59 ("200-300", 200, 300), ("300-500", 300, 500), ("500 +", 500, 1e9),60]61_YELP_BINS = [62 ("< 3", 0, 3), ("3 à 3,5", 3, 3.5), ("3,5 à 4", 3.5, 4),63 ("4 à 4,5", 4, 4.5), ("4,5 à 5", 4.5, 5.01),64]6566PERIOD_LABELS = {67 "auj": "Aujourd'hui", "7j": "7 jours", "30j": "30 jours",68 "3m": "3 mois", "6m": "6 mois", "12m": "12 mois",69 "annee": "Année en cours", "tout": "Toute la période",70}717273def _day(ts: float) -> str:74 return datetime.fromtimestamp(ts, TZ).strftime("%Y-%m-%d")757677def _epoch(d: date, end: bool = False) -> float:78 dt = datetime(d.year, d.month, d.day, tzinfo=TZ)79 if end:80 dt += timedelta(days=1)81 return dt.timestamp()828384def _resolve_period(con, period: str, dfrom: str | None,85 dto: str | None) -> tuple[date, date, str]:86 """(from, to, label) — bornes inclusives en dates locales."""87 today = datetime.now(TZ).date()88 if dfrom and dto:89 try:90 f = date.fromisoformat(dfrom)91 t = date.fromisoformat(dto)92 if f <= t:93 return f, t, f"du {f} au {t}"94 except ValueError:95 pass96 days = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}97 if period == "auj":98 return today, today, PERIOD_LABELS["auj"]99 if period in days:100 return today - timedelta(days=days[period] - 1), today, \101 PERIOD_LABELS[period]102 if period == "annee":103 return date(today.year, 1, 1), today, f"Année {today.year}"104 # tout : depuis la première fiche référencée105 row = con.execute("SELECT MIN(first_seen) m FROM restaurants").fetchone()106 start = date.fromtimestamp(row["m"]) if row and row["m"] else today107 return start, today, PERIOD_LABELS["tout"]108109110def _delta(cur: float, prev: float) -> float | None:111 if not prev:112 return None113 return round(100.0 * (cur - prev) / prev, 1)114115116def _kpi(id_, label, value, unit="", delta_pct=None, positive_is_up=True,117 spark=None):118 k = {"id": id_, "label": label, "value": value, "unit": unit,119 "delta_pct": delta_pct}120 if delta_pct is not None:121 up = delta_pct >= 0 if positive_is_up else delta_pct < 0122 k["direction"] = "up" if up else "down"123 if spark and len(spark) >= 2:124 k["spark"] = spark125 return k126127128def _spark(pts: list[dict], n: int = 30) -> list[dict]:129 """Sous-échantillonne une série quotidienne pour la sparkline (≤ n pts)."""130 if len(pts) <= n:131 return pts132 step = max(1, len(pts) // n)133 out = pts[::step]134 if out[-1] is not pts[-1]:135 out.append(pts[-1])136 return out137138139def _pct(part: int, total: int) -> float:140 return round(100.0 * part / total, 1) if total else 0.0141142143def _fr(n: float, dec: int = 2) -> str:144 return f"{n:.{dec}f}".replace(".", ",")145146147def _daily(rows: list, f: date, t: date, cumulative: bool = False,148 base: int = 0) -> list[dict]:149 """Série quotidienne bouchée à zéro sur [f, t] à partir de {jour: n}."""150 by_day = dict(rows)151 n_days = (t - f).days + 1152 pts, acc = [], base153 step = max(1, n_days // 366) # plafonne le nombre de points154 d = f155 while d <= t:156 v = 0157 for k in range(step):158 v += by_day.get((d + timedelta(days=k)).isoformat(), 0)159 acc += v160 pts.append({"t": d.isoformat(), "v": acc if cumulative else v})161 d += timedelta(days=step)162 if pts and pts[-1]["t"] != t.isoformat():163 pts.append({"t": t.isoformat(), "v": acc if cumulative else 0})164 return pts165166167def _iter_menu_items(con):168 for r in con.execute(169 "SELECT m.sections, r.chain, r.name FROM menus m"170 " JOIN restaurants r ON r.uid=m.uid"171 " WHERE r.active=1 AND r.dup_of IS NULL"):172 try:173 sections = json.loads(r["sections"] or "[]")174 except ValueError:175 continue176 for sec in sections:177 for it in sec.get("items") or []:178 yield r, it179180181def _bin_counts(values: list[float], bins) -> list[dict]:182 counts = Counter()183 for v in values:184 for lbl, lo, hi in bins:185 if lo <= v < hi:186 counts[lbl] += 1187 break188 return [{"label": lbl, "value": counts[lbl]}189 for lbl, _, _ in bins if counts[lbl]]190191192def _build(period: str, dfrom: str | None, dto: str | None) -> dict:193 con = db.connect()194 try:195 f, t, label = _resolve_period(con, period, dfrom, dto)196 f_ts, t_ts = _epoch(f), _epoch(t, end=True)197 span = (t - f).days + 1198 pf, pt = f - timedelta(days=span), f - timedelta(days=1)199 pf_ts, pt_ts = _epoch(pf), _epoch(pt, end=True)200201 A = "active=1 AND dup_of IS NULL" # restos comptés partout202203 # ------------------------------------------------------------ KPI ---204 total = con.execute(205 f"SELECT COUNT(*) n FROM restaurants WHERE {A}").fetchone()["n"]206 # proxy de stock par first_seen (croissance sur la période)207 stock_end = con.execute(208 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND first_seen<?",209 (t_ts,)).fetchone()["n"]210 stock_start = con.execute(211 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND first_seen<?",212 (f_ts,)).fetchone()["n"]213214 with_menu = con.execute(215 f"SELECT COUNT(*) n FROM restaurants WHERE {A} AND EXISTS"216 " (SELECT 1 FROM menus m WHERE m.uid=restaurants.uid)"217 ).fetchone()["n"]218219 items = con.execute(220 "SELECT COALESCE(SUM(m.item_count),0) n FROM menus m"221 " JOIN restaurants r ON r.uid=m.uid"222 " WHERE r.active=1 AND r.dup_of IS NULL").fetchone()["n"]223224 chains = con.execute(225 f"SELECT COUNT(DISTINCT chain) n FROM restaurants WHERE {A}"226 " AND chain IS NOT NULL").fetchone()["n"]227 cities = con.execute(228 f"SELECT COUNT(DISTINCT city) n FROM restaurants WHERE {A}"229 " AND city<>''").fetchone()["n"]230231 new_cur = con.execute(232 f"SELECT COUNT(*) n FROM restaurants WHERE {A}"233 " AND first_seen>=? AND first_seen<?", (f_ts, t_ts)).fetchone()["n"]234 new_prev = con.execute(235 f"SELECT COUNT(*) n FROM restaurants WHERE {A}"236 " AND first_seen>=? AND first_seen<?", (pf_ts, pt_ts)).fetchone()["n"]237238 closed_cur = con.execute(239 "SELECT COUNT(*) n FROM restaurants WHERE dup_of IS NULL"240 " AND status IN ('temporarily_closed','closed')"241 " AND updated_at>=? AND updated_at<?", (f_ts, t_ts)).fetchone()["n"]242 closed_prev = con.execute(243 "SELECT COUNT(*) n FROM restaurants WHERE dup_of IS NULL"244 " AND status IN ('temporarily_closed','closed')"245 " AND updated_at>=? AND updated_at<?", (pf_ts, pt_ts)).fetchone()["n"]246247 releves_cur = con.execute(248 "SELECT COUNT(*) n FROM item_price_log WHERE ts>=? AND ts<?",249 (f_ts, t_ts)).fetchone()["n"]250 releves_prev = con.execute(251 "SELECT COUNT(*) n FROM item_price_log WHERE ts>=? AND ts<?",252 (pf_ts, pt_ts)).fetchone()["n"]253254 # -------------------------------------------- complétude des fiches ---255 comp = con.execute(256 f"""SELECT257 SUM(lat IS NOT NULL AND lng IS NOT NULL) geo,258 SUM(phone<>'') tel,259 SUM(website<>'') web,260 SUM(hours IS NOT NULL AND hours<>'' AND hours<>'{{}}') hrs,261 SUM(price_range<>'') pr,262 SUM(images IS NOT NULL AND images<>'' AND images<>'[]') img263 FROM restaurants WHERE {A}""").fetchone()264265 # ------------------------- enrichissements (details : RACJ/Yelp/MAPAQ)266 permis_n = 0267 caps: list[float] = []268 max_cap: tuple[str | None, float] = (None, 0.0)269 yelp_ratings: list[float] = []270 best_yelp: tuple[str | None, float, int] = (None, 0.0, 0)271 mapaq_n = 0272 for r in con.execute(273 f"SELECT name, city, details FROM restaurants WHERE {A}"274 " AND details IS NOT NULL AND details<>''"):275 try:276 det = json.loads(r["details"])277 except ValueError:278 continue279 pa = det.get("permis_alcool")280 if pa:281 permis_n += 1282 cap = pa.get("capacite")283 if isinstance(cap, (int, float)) and cap > 0:284 caps.append(float(cap))285 if cap > max_cap[1]:286 max_cap = (f"{r['name']} ({r['city']})", float(cap))287 y = det.get("yelp")288 if y and isinstance(y.get("rating"), (int, float)):289 yelp_ratings.append(float(y["rating"]))290 rc = int(y.get("review_count") or 0)291 if (y["rating"], rc) > (best_yelp[1], best_yelp[2]):292 best_yelp = (f"{r['name']} ({r['city']})",293 float(y["rating"]), rc)294 if det.get("mapaq"):295 mapaq_n += 1296297 # -------------------------------------------- séries quotidiennes ---298 new_by_day = [(r["d"], r["n"]) for r in con.execute(299 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"300 f" FROM restaurants WHERE {A} AND first_seen>=? AND first_seen<?"301 " GROUP BY d", (f_ts, t_ts))]302 # plats suivis : 1re apparition de chaque item dans l'historique303 items_first = [(r["d"], r["n"]) for r in con.execute(304 "SELECT date(m0,'unixepoch','localtime') d, COUNT(*) n FROM"305 " (SELECT MIN(ts) m0 FROM item_price_log"306 " GROUP BY uid, price_context, item_key)"307 " WHERE m0>=? AND m0<? GROUP BY d", (f_ts, t_ts))]308 items_base = con.execute(309 "SELECT COUNT(*) n FROM (SELECT MIN(ts) m0 FROM item_price_log"310 " GROUP BY uid, price_context, item_key) WHERE m0<?",311 (f_ts,)).fetchone()["n"]312 releves_by_day = [(r["d"], r["n"]) for r in con.execute(313 "SELECT date(ts,'unixepoch','localtime') d, COUNT(*) n"314 " FROM item_price_log WHERE ts>=? AND ts<? GROUP BY d",315 (f_ts, t_ts))]316 updated_by_day = [(r["d"], r["n"]) for r in con.execute(317 "SELECT date(updated_at,'unixepoch','localtime') d, COUNT(*) n"318 f" FROM restaurants WHERE {A} AND updated_at>=? AND updated_at<?"319 " GROUP BY d", (f_ts, t_ts))]320321 stock_pts = _daily(new_by_day, f, t, cumulative=True,322 base=stock_start)323 new_pts = _daily(new_by_day, f, t)324 items_pts = _daily(items_first, f, t, cumulative=True,325 base=items_base)326 releves_pts = _daily(releves_by_day, f, t)327 updated_pts = _daily(updated_by_day, f, t)328329 kpis = [330 _kpi("total", "Restaurants référencés", total,331 delta_pct=_delta(stock_end, stock_start),332 spark=_spark(stock_pts)),333 _kpi("with_menu", "Restos avec menu & prix", with_menu),334 _kpi("items", "Plats & prix suivis", items,335 spark=_spark(items_pts)),336 _kpi("releves", "Relevés de prix (période)", releves_cur,337 delta_pct=_delta(releves_cur, releves_prev),338 spark=_spark(releves_pts)),339 _kpi("chains", "Chaînes suivies", chains),340 _kpi("cities", "Villes couvertes", cities),341 _kpi("new", "Nouveaux référencés (période)", new_cur,342 delta_pct=_delta(new_cur, new_prev),343 spark=_spark(new_pts)),344 _kpi("closed", "Fermetures détectées (période)", closed_cur,345 delta_pct=_delta(closed_cur, closed_prev),346 positive_is_up=False),347 _kpi("permis", "Permis d'alcool reliés (RACJ)", permis_n),348 _kpi("yelp", "Notes Yelp reliées", len(yelp_ratings)),349 ]350351 # ----------------------------------------------------------- jauges ---352 gauges = [353 {"id": "geo", "label": "Restos géolocalisés",354 "value": _pct(comp["geo"], total), "max": 100, "unit": "%"},355 {"id": "menu", "label": "Restos avec menu & prix",356 "value": _pct(with_menu, total), "max": 100, "unit": "%"},357 {"id": "tel", "label": "Fiches avec téléphone",358 "value": _pct(comp["tel"], total), "max": 100, "unit": "%"},359 {"id": "hours", "label": "Horaires structurés",360 "value": _pct(comp["hrs"], total), "max": 100, "unit": "%"},361 {"id": "web", "label": "Fiches avec site web",362 "value": _pct(comp["web"], total), "max": 100, "unit": "%"},363 {"id": "pr", "label": "Fourchette de prix estimée",364 "value": _pct(comp["pr"], total), "max": 100, "unit": "%"},365 ]366367 # ----------------------------------------------------------- séries ---368 series = [369 {"id": "new_restos", "title": "Nouveaux restos référencés par jour",370 "unit": "restos", "kind": "line", "points": new_pts},371 {"id": "items_cum", "title": "Plats & prix suivis (cumul)",372 "unit": "plats", "kind": "area", "points": items_pts},373 {"id": "releves", "title": "Relevés de prix par jour",374 "unit": "relevés", "kind": "bar", "points": releves_pts},375 {"id": "updated", "title": "Fiches mises à jour par jour",376 "unit": "fiches", "kind": "bar", "points": updated_pts},377 ]378379 # -------------------------------- multi-courbes : prix moyen / ville ---380 # jours de la période où des relevés existent (grille commune)381 grid_days = [r["d"] for r in con.execute(382 "SELECT DISTINCT date(ts,'unixepoch','localtime') d"383 " FROM item_price_log WHERE ts>=? AND ts<? AND price>0"384 " ORDER BY d", (f_ts, t_ts))]385 multiseries = []386 if len(grid_days) >= 2:387 city_day: dict[str, dict[str, float]] = {}388 city_tot: Counter = Counter()389 for r in con.execute(390 "SELECT re.city c, date(l.ts,'unixepoch','localtime') d,"391 " AVG(l.price) a, COUNT(*) n FROM item_price_log l"392 " JOIN restaurants re ON re.uid=l.uid"393 " WHERE l.ts>=? AND l.ts<? AND l.price>0 AND re.city<>''"394 " GROUP BY c, d", (f_ts, t_ts)):395 city_day.setdefault(r["c"], {})[r["d"]] = round(r["a"], 2)396 city_tot[r["c"]] += r["n"]397 chosen = []398 for city, _n in city_tot.most_common():399 if all(d in city_day[city] for d in grid_days):400 chosen.append(city)401 if len(chosen) == 4:402 break403 if len(chosen) >= 2:404 multiseries.append({405 "id": "prix_villes",406 "title": "Prix moyen d'un plat relevé — grandes villes",407 "unit": "$",408 "series": [{"label": c, "points": [409 {"t": d, "v": city_day[c][d]} for d in grid_days]}410 for c in chosen]})411412 # ------------------------------- empilé : éléments ajoutés / source ---413 src_added: dict[str, list] = {}414 for r in con.execute(415 "SELECT source s, date(ts,'unixepoch','localtime') d,"416 " SUM(added) n FROM sync_log WHERE ts>=? AND ts<?"417 " GROUP BY s, d", (f_ts, t_ts)):418 src_added.setdefault(r["s"], []).append((r["d"], r["n"] or 0))419 stacked = []420 if src_added:421 keys = sorted(src_added,422 key=lambda s: -sum(n for _, n in src_added[s]))[:6]423 per_key = {k: _daily(src_added[k], f, t) for k in keys}424 grid = per_key[keys[0]]425 stacked.append({426 "id": "ajouts_sources",427 "title": "Éléments ajoutés par source (journal de sync)",428 "unit": "ajouts", "keys": keys,429 "points": [{"t": grid[i]["t"],430 "values": [per_key[k][i]["v"] for k in keys]}431 for i in range(len(grid))]})432433 # ---------------------------------------------- répartitions (stock) ---434 cuisine_counts: Counter = Counter()435 for r in con.execute(436 f"SELECT cuisines FROM restaurants WHERE {A}"):437 for c in json.loads(r["cuisines"] or "[]"):438 cuisine_counts[c] += 1439 top_cuisines = [440 {"label": _CUISINE_LABELS.get(c, c.capitalize()), "value": n}441 for c, n in cuisine_counts.most_common(8)]442443 max_item = (None, 0.0) # (desc, prix) — record réel444 all_prices: list[float] = []445 for r, it in _iter_menu_items(con):446 p = it.get("price")447 if not isinstance(p, (int, float)) or p <= 0:448 continue449 all_prices.append(float(p))450 if p > max_item[1]:451 max_item = (f"{it.get('name')} — {r['chain'] or r['name']}", p)452453 types = [{"label": _TYPE_LABELS.get(r["t"], r["t"] or "Autre"),454 "value": r["n"]} for r in con.execute(455 f"SELECT establishment_type t, COUNT(*) n FROM restaurants"456 f" WHERE {A} AND establishment_type<>'' GROUP BY t"457 " ORDER BY n DESC")]458459 ctx_items = [{"label": _CTX_LABELS.get(r["c"], r["c"]), "value": r["n"]}460 for r in con.execute(461 "SELECT m.price_context c, COUNT(*) n FROM menus m"462 " JOIN restaurants r ON r.uid=m.uid"463 " WHERE r.active=1 AND r.dup_of IS NULL"464 " GROUP BY c ORDER BY n DESC")]465466 gamme_items = [{"label": r["g"], "value": r["n"]} for r in con.execute(467 f"SELECT price_range g, COUNT(*) n FROM restaurants WHERE {A}"468 " AND price_range<>'' GROUP BY g ORDER BY LENGTH(g)")]469470 breakdowns = [471 {"id": "cuisines", "title": "Restos par type de cuisine (top 8)",472 "kind": "donut", "items": top_cuisines},473 {"id": "contextes", "title": "Menus par contexte de prix",474 "kind": "donut", "items": ctx_items},475 {"id": "types", "title": "Par type d'établissement",476 "kind": "bar", "items": types},477 ]478 if gamme_items:479 breakdowns.append(480 {"id": "gammes",481 "title": "Restos par fourchette de prix estimée",482 "kind": "bar", "items": gamme_items})483484 # ---------------------------------------------------- distributions ---485 distributions = []486 if all_prices:487 distributions.append(488 {"id": "prix_plats", "title": "Distribution des prix de plats",489 "unit": "plats", "bins": _bin_counts(all_prices, _PRICE_BINS)})490 if caps:491 distributions.append(492 {"id": "capacites",493 "title": "Capacité des salles (permis d'alcool RACJ)",494 "unit": "restos", "bins": _bin_counts(caps, _CAP_BINS)})495 if yelp_ratings:496 distributions.append(497 {"id": "notes_yelp", "title": "Distribution des notes Yelp",498 "unit": "restos",499 "bins": _bin_counts(yelp_ratings, _YELP_BINS)})500501 # -------------------------------------------------------------- géo ---502 geo = {"title": "Restos par région", "items": [503 {"label": r["region"], "value": r["n"]} for r in con.execute(504 f"SELECT region, COUNT(*) n FROM restaurants WHERE {A}"505 " AND region<>'' GROUP BY region ORDER BY n DESC")]}506507 # ---------------------------------------------------------- heatmap ---508 heat = [{"date": r["d"], "value": r["n"]} for r in con.execute(509 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"510 f" FROM restaurants WHERE {A} GROUP BY d ORDER BY d")]511 heatmap = {"title": "Nouveaux restos référencés", "cells": heat}512513 # ------------------------------------------- heatmap horaire (7×24) ---514 hourly_cells = [515 {"dow": r["w"], "hour": r["h"], "value": r["n"]}516 for r in con.execute(517 "SELECT (CAST(strftime('%w',ts,'unixepoch','localtime')"518 " AS INTEGER)+6)%7 w,"519 " CAST(strftime('%H',ts,'unixepoch','localtime') AS INTEGER) h,"520 " COUNT(*) n FROM item_price_log WHERE ts>=? AND ts<?"521 " GROUP BY w, h", (f_ts, t_ts))]522 hourly = ({"title": "Relevés de prix par heure", "cells": hourly_cells}523 if hourly_cells else None)524525 # --------------------------------------------------------- tableaux ---526 top_cities = [[r["city"], r["n"], r["wm"],527 f"{100.0 * r['n'] / total:.1f} %".replace(".", ",")]528 for r in con.execute(529 f"SELECT city, COUNT(*) n, SUM(EXISTS (SELECT 1 FROM menus m"530 f" WHERE m.uid=restaurants.uid)) wm FROM restaurants WHERE {A}"531 " AND city<>'' GROUP BY city ORDER BY n DESC LIMIT 25")]532533 # top chaînes : succursales, plats du menu le plus complet, prix moyen534 chain_rows: dict[str, dict] = {}535 for r in con.execute(536 f"SELECT chain, COUNT(*) n FROM restaurants WHERE {A}"537 " AND chain IS NOT NULL GROUP BY chain"):538 chain_rows[r["chain"]] = {"locs": r["n"], "items": 0, "prices": []}539 for r in con.execute(540 "SELECT r.chain, m.item_count, m.sections FROM menus m"541 " JOIN restaurants r ON r.uid=m.uid"542 " WHERE r.active=1 AND r.dup_of IS NULL"543 " AND r.chain IS NOT NULL"):544 cr = chain_rows.get(r["chain"])545 if cr is None or (r["item_count"] or 0) <= cr["items"]:546 continue547 cr["items"] = r["item_count"] or 0548 try:549 secs = json.loads(r["sections"] or "[]")550 except ValueError:551 continue552 cr["prices"] = [it["price"] for s in secs553 for it in s.get("items") or []554 if isinstance(it.get("price"), (int, float))555 and it["price"] > 0]556 top_chains = []557 for name, cr in sorted(chain_rows.items(),558 key=lambda kv: -kv[1]["locs"])[:25]:559 avg = (f"{statistics.mean(cr['prices']):.2f} $".replace(".", ",")560 if cr["prices"] else "—")561 top_chains.append([name, cr["locs"], cr["items"] or "—", avg])562563 # top établissements par nombre de plats au menu564 seen_uids: set[str] = set()565 top_places = []566 for r in con.execute(567 "SELECT r.uid, r.name, r.city, m.price_context c,"568 " m.item_count ic, m.sections FROM menus m"569 " JOIN restaurants r ON r.uid=m.uid"570 " WHERE r.active=1 AND r.dup_of IS NULL"571 " ORDER BY m.item_count DESC LIMIT 60"):572 if r["uid"] in seen_uids:573 continue574 seen_uids.add(r["uid"])575 prices = []576 try:577 secs = json.loads(r["sections"] or "[]")578 prices = [it["price"] for s in secs579 for it in s.get("items") or []580 if isinstance(it.get("price"), (int, float))581 and it["price"] > 0]582 except ValueError:583 pass584 avg = (f"{statistics.mean(prices):.2f} $".replace(".", ",")585 if prices else "—")586 top_places.append([r["name"], r["city"] or "—",587 _CTX_LABELS.get(r["c"], r["c"]),588 r["ic"] or 0, avg])589 if len(top_places) == 25:590 break591592 # sources & connecteurs : couverture + dernier sync593 src_restos = {r["source"]: (r["n"], r["wm"]) for r in con.execute(594 f"SELECT source, COUNT(*) n, SUM(EXISTS (SELECT 1 FROM menus m"595 f" WHERE m.uid=restaurants.uid)) wm FROM restaurants WHERE {A}"596 " GROUP BY source")}597 src_last: dict[str, dict] = {}598 for r in con.execute(599 "SELECT source, ts, added, ok, message FROM sync_log"600 " ORDER BY ts"):601 src_last[r["source"]] = dict(r)602 src_added_period = {r["source"]: r["n"] or 0 for r in con.execute(603 "SELECT source, SUM(added) n FROM sync_log"604 " WHERE ts>=? AND ts<? GROUP BY source", (f_ts, t_ts))}605 sources_rows = []606 for s in sorted(set(src_restos) | set(src_last),607 key=lambda s: -(src_restos.get(s, (0, 0))[0])):608 n, wm = src_restos.get(s, (0, 0))609 last = src_last.get(s)610 when = (datetime.fromtimestamp(last["ts"], TZ)611 .strftime("%Y-%m-%d %H:%M") if last else "—")612 state = ("—" if not last else613 "ok" if last["ok"] and last["message"] == "ok"614 else ("alerte" if last["ok"] else "échec"))615 sources_rows.append([s, n, wm or 0,616 src_added_period.get(s, 0), when, state])617618 newest = [[r["name"], r["city"] or "—", r["region"] or "—",619 _day(r["first_seen"])] for r in con.execute(620 f"SELECT name, city, region, first_seen FROM restaurants"621 f" WHERE {A} AND first_seen>=? AND first_seen<?"622 " ORDER BY first_seen DESC LIMIT 100", (f_ts, t_ts))]623624 syncs = [[r["source"], datetime.fromtimestamp(r["ts"], TZ)625 .strftime("%Y-%m-%d %H:%M"), r["found"], r["added"],626 r["updated"], r["removed"],627 "ok" if r["ok"] and r["message"] == "ok"628 else ("alerte" if r["ok"] else "échec")]629 for r in con.execute(630 "SELECT source, ts, found, added, updated, removed, ok, message"631 " FROM sync_log WHERE ts>=? AND ts<? ORDER BY ts DESC LIMIT 50",632 (f_ts, t_ts))]633634 tables = [635 {"id": "villes", "title": "Top villes",636 "columns": ["Ville", "Restos", "Avec menu", "Part"],637 "rows": top_cities},638 {"id": "chaines", "title": "Top chaînes",639 "columns": ["Chaîne", "Succursales", "Plats au menu",640 "Prix moyen"],641 "rows": top_chains},642 ]643 if top_places:644 tables.append(645 {"id": "etablissements",646 "title": "Top établissements par plats au menu",647 "columns": ["Restaurant", "Ville", "Contexte", "Plats",648 "Prix moyen"],649 "rows": top_places})650 if sources_rows:651 tables.append(652 {"id": "sources", "title": "Sources & connecteurs",653 "columns": ["Source", "Restos actifs", "Avec menu",654 "Ajoutés (période)", "Dernier sync", "État"],655 "rows": sources_rows})656 if newest:657 tables.append(658 {"id": "nouveaux", "title": "Nouveaux restos de la période"659 " (100 plus récents)",660 "columns": ["Restaurant", "Ville", "Région", "Ajouté le"],661 "rows": newest})662 if syncs:663 tables.append(664 {"id": "syncs", "title": "Journal des synchronisations",665 "columns": ["Source", "Quand", "Trouvés", "Ajoutés",666 "Mis à jour", "Retirés", "État"],667 "rows": syncs})668669 # ---------------------------------------------------------- records ---670 records = []671 rec_day = con.execute(672 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"673 f" FROM restaurants WHERE {A} GROUP BY d ORDER BY n DESC LIMIT 1"674 ).fetchone()675 if rec_day:676 records.append({"label": "Jour record de référencement",677 "value": f"{rec_day['n']:,} restos".replace(",", " "),678 "date": rec_day["d"]})679 big_menu = con.execute(680 "SELECT r.name, r.city, m.item_count ic FROM menus m"681 " JOIN restaurants r ON r.uid=m.uid"682 " WHERE r.active=1 AND r.dup_of IS NULL"683 " ORDER BY m.item_count DESC LIMIT 1").fetchone()684 if big_menu and big_menu["ic"]:685 records.append({"label": "Menu le plus étoffé",686 "value": f"{big_menu['name']} ({big_menu['city']})"687 f" — {big_menu['ic']} plats"})688 big_chain = max(chain_rows.items(), key=lambda kv: kv[1]["items"],689 default=None)690 if big_chain and big_chain[1]["items"]:691 records.append({"label": "Chaîne au menu le plus étoffé",692 "value": f"{big_chain[0]} — "693 f"{big_chain[1]['items']} plats"})694 if top_cities:695 records.append({"label": "Ville la plus couverte",696 "value": f"{top_cities[0][0]} — "697 f"{top_cities[0][1]:,} restos".replace(",", " ")})698 if geo["items"]:699 g0 = geo["items"][0]700 records.append({"label": "Région la plus couverte",701 "value": f"{g0['label']} — "702 f"{g0['value']:,} restos".replace(",", " ")})703 if all_prices:704 med = statistics.median(all_prices)705 records.append({"label": "Prix médian d'un plat suivi",706 "value": f"{med:.2f} $".replace(".", ",")})707 if max_item[0]:708 records.append({"label": "Plat le plus cher observé",709 "value": f"{max_item[0][:44]} — "710 f"{max_item[1]:.2f} $".replace(".", ",")})711 rec_price_day = con.execute(712 "SELECT date(ts,'unixepoch','localtime') d, COUNT(*) n"713 " FROM item_price_log GROUP BY d ORDER BY n DESC LIMIT 1"714 ).fetchone()715 if rec_price_day:716 records.append({"label": "Jour record de relevés de prix",717 "value": f"{rec_price_day['n']:,} relevés"718 .replace(",", " "),719 "date": rec_price_day["d"]})720 if max_cap[0]:721 records.append({"label": "Plus grande salle (permis RACJ)",722 "value": f"{max_cap[0][:44]} — "723 f"{int(max_cap[1])} places"})724 if best_yelp[0]:725 records.append({"label": "Meilleure note Yelp reliée",726 "value": f"{best_yelp[0][:40]} — "727 f"{_fr(best_yelp[1], 1)}/5"728 f" ({best_yelp[2]} avis)"})729 if src_added:730 bs_name, bs_rows = max(src_added.items(),731 key=lambda kv: sum(n for _, n in kv[1]))732 bs_tot = sum(n for _, n in bs_rows)733 if bs_tot:734 records.append(735 {"label": "Source la plus productive (période)",736 "value": f"{bs_name} — {bs_tot:,} ajouts"737 .replace(",", " ")})738 if mapaq_n:739 records.append({"label": "Restos avec dossier MAPAQ relié",740 "value": f"{mapaq_n} établissements"})741742 out = {743 "updated": datetime.now(TZ).isoformat(timespec="seconds"),744 "period": {"from": f.isoformat(), "to": t.isoformat(),745 "label": label},746 "kpis": kpis,747 "gauges": gauges,748 "series": series,749 "breakdowns": breakdowns,750 "geo": geo,751 "heatmap": heatmap,752 "tables": tables,753 "records": records,754 }755 if multiseries:756 out["multiseries"] = multiseries757 if stacked:758 out["stacked"] = stacked759 if distributions:760 out["distributions"] = distributions761 if hourly:762 out["hourly"] = hourly763 return out764 finally:765 con.close()766767768def dashboard(period: str = "30j", dfrom: str | None = None,769 dto: str | None = None) -> dict:770 key = (period, dfrom or "", dto or "")771 now = time.time()772 with _cache_lock:773 hit = _cache.get(key)774 if hit and now - hit[0] < CACHE_TTL:775 return hit[1]776 data = _build(period, dfrom, dto)777 with _cache_lock:778 _cache[key] = (time.time(), data)779 return data780