Food-Ka — agrégateur de produits d'épicerie du Québec — www.food-ka.com
Python 53.9%
TypeScript 24%
CSS 14.9%
JavaScript 5.8%
HTML 1.4%
1# -----------------------------------------------------------------------------2# Food-Ka — Agrégateur de produits d'épicerie (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# statsdash.py : tableau de bord analytique — source unique de5# GET /api/stats/dashboard (contrat commun ka-ui/stats/SPEC.md v2) et des6# 5 rapports PDF Groupe-KA (GET /api/stats/report, moteur foodka/kapdf.py).7# Tout est calculé sur les données réelles : products (catalogue vivant),8# price_log (historique des relevés de prix), sync_log (journal des syncs).9# Rien d'inventé : une mesure indisponible = champ omis (section masquée).10# Cache serveur : 5 minutes par période demandée.11# -----------------------------------------------------------------------------12from __future__ import annotations1314import json15import statistics16import threading17import time18from datetime import date, datetime, timedelta19from pathlib import Path20from zoneinfo import ZoneInfo2122from . import db2324TZ = ZoneInfo("America/Toronto")25CACHE_TTL = 300 # secondes (SPEC : >= 5 min par période)2627_cache: dict[tuple, tuple[float, dict]] = {}28_cache_lock = threading.Lock()2930# période -> (libellé, nombre de jours) ; « annee » et « tout » sont calculés31_PERIOD_DAYS = {32 "auj": ("Aujourd'hui", 1),33 "7j": ("7 jours", 7),34 "30j": ("30 jours", 30),35 "3m": ("3 mois", 91),36 "6m": ("6 mois", 182),37 "12m": ("12 mois", 365),38}3940_PRICE_SANE = "price IS NOT NULL AND price > 0 AND price <= 2000"4142# Noms d'affichage des bannières (registre data/sources.json)43_SOURCES_PATH = Path(__file__).resolve().parent.parent / "data" / "sources.json"444546def _source_registry() -> list[dict]:47 try:48 return json.loads(_SOURCES_PATH.read_text(encoding="utf-8"))["sources"]49 except Exception:50 return []515253def _fmt_money(v: float | None) -> str:54 if v is None:55 return "—"56 return f"{v:,.2f}".replace(",", " ").replace(".", ",") + " $"575859def _fmt_int(n: int) -> str:60 return f"{n:,}".replace(",", " ")616263def _parse_date(s: str | None) -> date | None:64 if not s:65 return None66 try:67 return date.fromisoformat(s[:10])68 except ValueError:69 return None707172def _day_start_ts(d: date) -> float:73 return datetime(d.year, d.month, d.day, tzinfo=TZ).timestamp()747576def _resolve_period(con, period: str, from_s: str | None, to_s: str | None):77 """Retourne (from_date, to_date, label, period_id) — bornes inclusives."""78 today = datetime.now(TZ).date()79 f, t = _parse_date(from_s), _parse_date(to_s)80 if f and t:81 if t < f:82 f, t = t, f83 return f, min(t, today), f"du {f.isoformat()} au {t.isoformat()}", "perso"84 if period == "annee":85 return date(today.year, 1, 1), today, "Année en cours", period86 if period == "tout":87 row = con.execute("SELECT MIN(first_seen) m FROM products").fetchone()88 start = (datetime.fromtimestamp(row["m"], TZ).date()89 if row and row["m"] else today)90 return start, today, "Toute la période", period91 label, days = _PERIOD_DAYS.get(period, _PERIOD_DAYS["30j"])92 if period not in _PERIOD_DAYS:93 label, period = _PERIOD_DAYS["30j"][0], "30j"94 return today - timedelta(days=days - 1), today, label, period959697def _delta_pct(cur: float | None, prev: float | None) -> float | None:98 if cur is None or prev is None or prev == 0:99 return None100 return round(100 * (cur - prev) / prev, 1)101102103def _days_range(f: date, t: date) -> list[date]:104 n = (t - f).days + 1105 step = max(1, -(-n // 200)) # au plus ~200 points de série106 days = [f + timedelta(days=i) for i in range(0, n, step)]107 if days[-1] != t:108 days.append(t)109 return days110111112def _spark(points: list[dict], n: int = 30) -> list[dict]:113 """Sous-échantillonne une série pour la sparkline d'un KPI (≤ n points)."""114 if len(points) <= n:115 return points116 step = len(points) / (n - 1)117 out = [points[int(i * step)] for i in range(n - 1)]118 out.append(points[-1])119 return out120121122# ---------------------------------------------------------------------------123# Calcul principal124# ---------------------------------------------------------------------------125126def _tracked_at(con, ts: float) -> int:127 """Produits suivis à l'instant ts (reconstruit via first_seen/last_seen)."""128 return con.execute(129 "SELECT COUNT(*) c FROM products"130 " WHERE first_seen IS NOT NULL AND first_seen <= ?"131 " AND (active = 1 OR last_seen >= ?)", (ts, ts)).fetchone()["c"]132133134# variations de prix (LAG par produit sur price_log), bornées par ts — colonnes :135# uid, ts, price, prev, name, source, category136_MOVES_SQL = """WITH x AS (137 SELECT uid, ts, price,138 LAG(price) OVER (PARTITION BY uid ORDER BY ts) prev139 FROM price_log)140 SELECT x.uid, x.ts, x.price, x.prev, p.name, p.source, p.category141 FROM x JOIN products p ON p.uid = x.uid142 WHERE x.ts >= ? AND x.ts < ?143 AND x.price IS NOT NULL AND x.prev IS NOT NULL144 AND x.prev > 0 AND x.price > 0 AND x.price <> x.prev145 AND x.price <= 2000 AND x.prev <= 2000"""146147148def _compute(period: str, from_s: str | None, to_s: str | None) -> dict:149 con = db.connect()150 registry = _source_registry()151 names = {s["id"]: s.get("name") or s["id"] for s in registry}152 label_of = lambda src: names.get(src, src) # noqa: E731153154 f_date, t_date, label, period_id = _resolve_period(con, period, from_s, to_s)155 start = _day_start_ts(f_date)156 end = _day_start_ts(t_date + timedelta(days=1))157 now_ts = time.time()158 end_eff = min(end, now_ts) # fin effective (la période inclut souvent « maintenant »)159 span = end - start160 prev_start, prev_end = start - span, start161162 # ---- historique des relevés de prix (price_log) --------------------------163 first_log = con.execute("SELECT MIN(ts) m FROM price_log").fetchone()["m"]164 has_history = first_log is not None165 has_prev_history = bool(has_history and first_log < prev_end)166167 releves_cur = con.execute(168 "SELECT COUNT(*) c FROM price_log WHERE ts >= ? AND ts < ?",169 (start, end)).fetchone()["c"]170 releves_prev = con.execute(171 "SELECT COUNT(*) c FROM price_log WHERE ts >= ? AND ts < ?",172 (prev_start, prev_end)).fetchone()["c"]173174 # variations de prix détectées dans la période175 moves = con.execute(_MOVES_SQL, (start, end)).fetchall()176 drops = [m for m in moves if m["price"] < m["prev"]]177 hikes = [m for m in moves if m["price"] > m["prev"]]178 amp = [abs(m["price"] - m["prev"]) / m["prev"] for m in moves]179 amp_avg_pct = round(100 * statistics.mean(amp), 1) if amp else None180181 drops_prev = hikes_prev = None182 if has_prev_history:183 mv_prev = con.execute(184 f"SELECT SUM(price < prev) d, SUM(price > prev) h FROM ({_MOVES_SQL})",185 (prev_start, prev_end)).fetchone()186 drops_prev, hikes_prev = mv_prev["d"] or 0, mv_prev["h"] or 0187188 drops_by_day: dict[str, int] = {}189 hikes_by_day: dict[str, int] = {}190 for m in drops:191 d = datetime.fromtimestamp(m["ts"], TZ).date().isoformat()192 drops_by_day[d] = drops_by_day.get(d, 0) + 1193 for m in hikes:194 d = datetime.fromtimestamp(m["ts"], TZ).date().isoformat()195 hikes_by_day[d] = hikes_by_day.get(d, 0) + 1196 top_drops = sorted(197 ({"name": m["name"] or "", "source": m["source"],198 "old": m["prev"], "new": m["price"], "ts": m["ts"],199 "pct": round(100 * (m["prev"] - m["price"]) / m["prev"], 1)}200 for m in drops), key=lambda d: -d["pct"])201 top_hikes = sorted(202 ({"name": m["name"] or "", "source": m["source"],203 "old": m["prev"], "new": m["price"], "ts": m["ts"],204 "pct": round(100 * (m["price"] - m["prev"]) / m["prev"], 1)}205 for m in hikes), key=lambda d: -d["pct"])206207 # ---- agrégats du catalogue --------------------------------------------------208 g = con.execute(209 f"""SELECT COUNT(*) total, SUM(on_sale) on_sale,210 COUNT(DISTINCT source) sources,211 COUNT(DISTINCT category) categories,212 AVG(CASE WHEN {_PRICE_SANE} THEN price END) avg_price213 FROM products WHERE active=1""").fetchone()214215 tracked_now = _tracked_at(con, end_eff)216 tracked_prev = _tracked_at(con, start) if start > (first_log or 0) - 1 else None217218 src_cur = con.execute(219 "SELECT COUNT(DISTINCT source) c FROM sync_log WHERE ok=1 AND ts >= ? AND ts < ?",220 (start, end)).fetchone()["c"]221 src_prev = con.execute(222 "SELECT COUNT(DISTINCT source) c FROM sync_log WHERE ok=1 AND ts >= ? AND ts < ?",223 (prev_start, prev_end)).fetchone()["c"]224225 avg_obs_cur = con.execute(226 "SELECT AVG(price) a FROM price_log WHERE ts >= ? AND ts < ?"227 " AND price > 0 AND price <= 2000", (start, end)).fetchone()["a"]228 avg_obs_prev = con.execute(229 "SELECT AVG(price) a FROM price_log WHERE ts >= ? AND ts < ?"230 " AND price > 0 AND price <= 2000", (prev_start, prev_end)).fetchone()["a"]231232 new_cur = con.execute(233 "SELECT COUNT(*) c FROM products WHERE first_seen >= ? AND first_seen < ?",234 (start, end)).fetchone()["c"]235 new_prev = con.execute(236 "SELECT COUNT(*) c FROM products WHERE first_seen >= ? AND first_seen < ?",237 (prev_start, prev_end)).fetchone()["c"]238239 # rabais moyen affiché (soldes actifs avec prix régulier connu)240 rabais_avg = con.execute(241 f"""SELECT AVG(100.0 * (regular_price - price) / regular_price) a242 FROM products WHERE active=1 AND on_sale=1 AND {_PRICE_SANE}243 AND regular_price IS NOT NULL AND regular_price > price""").fetchone()["a"]244245 # ---- séries par jour --------------------------------------------------------246 data_start = (datetime.fromtimestamp(first_log, TZ).date()247 if has_history else t_date)248 serie_from = max(f_date, data_start)249 days = _days_range(serie_from, t_date)250 iso = [d.isoformat() for d in days]251252 pts_tracked = [{"t": d.isoformat(),253 "v": _tracked_at(con, min(_day_start_ts(d + timedelta(days=1)), now_ts))}254 for d in days]255256 per_day = {r["d"]: r["c"] for r in con.execute(257 "SELECT date(ts,'unixepoch','localtime') d, COUNT(*) c FROM price_log"258 " WHERE ts >= ? AND ts < ? GROUP BY d", (start, end))}259 pts_releves = [{"t": d, "v": per_day.get(d, 0)} for d in iso]260261 avg_day = {r["d"]: round(r["a"], 2) for r in con.execute(262 "SELECT date(ts,'unixepoch','localtime') d, AVG(price) a FROM price_log"263 " WHERE ts >= ? AND ts < ? AND price > 0 AND price <= 2000"264 " GROUP BY d", (start, end)) if r["a"] is not None}265 pts_avg = [{"t": d, "v": avg_day[d]} for d in iso if d in avg_day]266267 # comparaison N-1 de l'indice de prix moyen (si historique antérieur)268 cmp_avg: list[dict] = []269 if has_prev_history:270 cmp_avg = [{"t": r["d"], "v": round(r["a"], 2)} for r in con.execute(271 "SELECT date(ts,'unixepoch','localtime') d, AVG(price) a FROM price_log"272 " WHERE ts >= ? AND ts < ? AND price > 0 AND price <= 2000"273 " GROUP BY d ORDER BY d", (prev_start, prev_end)) if r["a"] is not None]274275 new_day = {r["d"]: r["c"] for r in con.execute(276 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) c FROM products"277 " WHERE first_seen >= ? AND first_seen < ? GROUP BY d", (start, end))}278 pts_new = [{"t": d, "v": new_day.get(d, 0)} for d in iso]279280 pts_drops = [{"t": d, "v": drops_by_day.get(d, 0)} for d in iso]281282 # ---- KPI (≥ 8, deltas honnêtes, sparklines quand une série existe) ----------283 def _kpi(id_, lbl, value, unit="", delta=None, spark=None):284 d = {"id": id_, "label": lbl, "value": value, "unit": unit}285 if delta is not None:286 d["delta_pct"] = delta287 d["direction"] = "up" if delta >= 0 else "down"288 else:289 d["delta_pct"] = None290 if spark and len(spark) >= 2:291 d["spark"] = _spark(spark)292 return d293294 kpis = [295 _kpi("suivis", "Produits suivis (actifs)", g["total"], "",296 _delta_pct(tracked_now, tracked_prev), spark=pts_tracked),297 _kpi("releves", "Relevés de prix (période)", releves_cur, "",298 _delta_pct(releves_cur, releves_prev), spark=pts_releves),299 _kpi("baisses", "Baisses de prix détectées", len(drops), "",300 _delta_pct(len(drops), drops_prev), spark=pts_drops),301 _kpi("hausses", "Hausses de prix détectées", len(hikes), "",302 _delta_pct(len(hikes), hikes_prev)),303 _kpi("soldes", "Soldes actifs", g["on_sale"] or 0, ""),304 *([_kpi("rabais_moyen", "Rabais moyen affiché",305 round(rabais_avg, 1), "%")] if rabais_avg else []),306 _kpi("prix_moyen", "Prix moyen (produits actifs)",307 round(g["avg_price"], 2) if g["avg_price"] else 0, "$",308 _delta_pct(avg_obs_cur, avg_obs_prev), spark=pts_avg),309 _kpi("bannieres", "Bannières connectées", g["sources"], "",310 _delta_pct(src_cur, src_prev)),311 _kpi("nouveaux", "Nouveaux produits (période)", new_cur, "",312 _delta_pct(new_cur, new_prev) if new_prev else None, spark=pts_new),313 _kpi("categories", "Catégories", g["categories"], ""),314 ]315316 # ---- jauges (taux & couvertures mesurés sur le catalogue réel) ---------------317 active_total = g["total"] or 0318 gauges = []319 if active_total:320 fresh7 = con.execute(321 "SELECT COUNT(*) c FROM products WHERE active=1 AND last_seen >= ?",322 (now_ts - 7 * 86400,)).fetchone()["c"]323 with_img = con.execute(324 "SELECT COUNT(*) c FROM products WHERE active=1"325 " AND images IS NOT NULL AND images <> '' AND images <> '[]'").fetchone()["c"]326 with_up = con.execute(327 "SELECT COUNT(*) c FROM products WHERE active=1"328 " AND unit_price IS NOT NULL").fetchone()["c"]329 gauges = [330 {"id": "fraicheur", "label": "Produits vus il y a moins de 7 jours",331 "value": round(100 * fresh7 / active_total, 1), "max": 100, "unit": "%"},332 {"id": "images", "label": "Produits avec image",333 "value": round(100 * with_img / active_total, 1), "max": 100, "unit": "%"},334 {"id": "prix_unitaire", "label": "Produits avec prix unitaire comparable",335 "value": round(100 * with_up / active_total, 1), "max": 100, "unit": "%"},336 ]337 if registry:338 gauges.append({"id": "couverture",339 "label": "Bannières du registre avec produits actifs",340 "value": g["sources"], "max": len(registry), "unit": ""})341342 # ---- séries -------------------------------------------------------------------343 series = []344 if has_history and len(pts_releves) >= 2:345 series.append({"id": "releves", "title": "Relevés de prix par jour",346 "unit": "relevés", "kind": "bar", "points": pts_releves})347 if len(pts_tracked) >= 2:348 series.append({"id": "suivis", "title": "Produits suivis par jour",349 "unit": "produits", "kind": "line", "points": pts_tracked})350 if has_history and len(pts_avg) >= 2:351 s_avg = {"id": "prix_moyen", "title": "Indice de prix moyen relevé par jour",352 "unit": "$", "kind": "area", "points": pts_avg}353 if len(cmp_avg) >= 2:354 s_avg["compare"] = cmp_avg355 series.append(s_avg)356 if has_history and len(pts_drops) >= 2 and drops:357 series.append({"id": "baisses", "title": "Baisses de prix détectées par jour",358 "unit": "baisses", "kind": "line", "points": pts_drops})359 if len(pts_new) >= 2 and any(p["v"] for p in pts_new):360 series.append({"id": "nouveautes", "title": "Nouveaux produits par jour",361 "unit": "produits", "kind": "bar", "points": pts_new})362363 # ---- multi-courbes : prix moyen relevé par jour, top bannières (≤ 4) ----------364 multiseries = []365 if has_history:366 rows = con.execute(367 """SELECT p.source s, date(l.ts,'unixepoch','localtime') d,368 AVG(l.price) a, COUNT(*) c369 FROM price_log l JOIN products p ON p.uid = l.uid370 WHERE l.ts >= ? AND l.ts < ? AND l.price > 0 AND l.price <= 2000371 GROUP BY s, d""", (start, end)).fetchall()372 by_src_day: dict[str, dict[str, float]] = {}373 src_obs: dict[str, int] = {}374 for r in rows:375 by_src_day.setdefault(r["s"], {})[r["d"]] = round(r["a"], 2)376 src_obs[r["s"]] = src_obs.get(r["s"], 0) + r["c"]377 # jusqu'à 4 bannières très actives partageant assez de jours communs378 chosen: list[str] = []379 common: set[str] = set()380 for s in sorted(src_obs, key=lambda s: -src_obs[s]):381 days_s = set(by_src_day[s])382 cand = (common & days_s) if chosen else days_s383 if len(cand) >= 3 and len(chosen) < 4:384 chosen.append(s)385 common = cand386 if len(chosen) >= 2 and len(common) >= 3:387 axis = sorted(common)388 multiseries.append({389 "id": "prix_bannieres",390 "title": "Prix moyen relevé par jour — bannières les plus actives",391 "unit": "$",392 "series": [{"label": label_of(s),393 "points": [{"t": d, "v": by_src_day[s][d]} for d in axis]}394 for s in chosen]})395396 # ---- barres empilées ------------------------------------------------------------397 stacked = []398 if moves and len(iso) >= 2:399 stacked.append({400 "id": "variations",401 "title": "Variations de prix par jour — baisses vs hausses",402 "unit": "variations", "keys": ["Baisses", "Hausses"],403 "points": [{"t": d, "values": [drops_by_day.get(d, 0),404 hikes_by_day.get(d, 0)]} for d in iso]})405 adds = con.execute(406 """SELECT source s, date(first_seen,'unixepoch','localtime') d, COUNT(*) c407 FROM products WHERE first_seen >= ? AND first_seen < ?408 GROUP BY s, d""", (start, end)).fetchall()409 if adds and len(iso) >= 2:410 add_tot: dict[str, int] = {}411 add_day: dict[tuple[str, str], int] = {}412 for r in adds:413 add_tot[r["s"]] = add_tot.get(r["s"], 0) + r["c"]414 add_day[(r["s"], r["d"])] = r["c"]415 top_src = sorted(add_tot, key=lambda s: -add_tot[s])[:5]416 others = [s for s in add_tot if s not in top_src]417 keys = [label_of(s) for s in top_src] + (["Autres"] if others else [])418 pts_st = []419 for d in iso:420 vals = [add_day.get((s, d), 0) for s in top_src]421 if others:422 vals.append(sum(add_day.get((s, d), 0) for s in others))423 pts_st.append({"t": d, "values": vals})424 if any(sum(p["values"]) for p in pts_st):425 stacked.append({"id": "ajouts",426 "title": "Nouveaux produits par jour et par bannière",427 "unit": "produits", "keys": keys, "points": pts_st})428429 # ---- répartitions ----------------------------------------------------------------430 by_src = con.execute(431 "SELECT source, COUNT(*) n, SUM(on_sale) sales FROM products WHERE active=1"432 " GROUP BY source ORDER BY n DESC").fetchall()433 donut_items = [{"label": label_of(r["source"]), "value": r["n"]}434 for r in by_src[:8]] # top 8 (limite du donut ka-ui/kapdf)435 by_cat = con.execute(436 "SELECT category, COUNT(*) n FROM products WHERE active=1 AND category<>''"437 " GROUP BY category ORDER BY n DESC").fetchall()438 breakdowns = [439 {"id": "bannieres", "title": "Produits actifs par bannière", "kind": "donut",440 "items": donut_items},441 {"id": "categories", "title": "Top catégories (produits actifs)", "kind": "bars",442 "items": [{"label": r["category"], "value": r["n"]} for r in by_cat[:14]]},443 ]444 sale_src = [{"label": label_of(r["source"]), "value": r["sales"] or 0}445 for r in sorted(by_src, key=lambda r: -(r["sales"] or 0))446 if (r["sales"] or 0) > 0][:14]447 if sale_src:448 breakdowns.append({"id": "soldes_bannieres",449 "title": "Soldes actifs par bannière", "kind": "bars",450 "items": sale_src})451 if drops:452 d_src_cur: dict[str, int] = {}453 for m in drops:454 d_src_cur[m["source"]] = d_src_cur.get(m["source"], 0) + 1455 d_src_prev: dict[str, int] = {}456 if has_prev_history:457 d_src_prev = {r["source"]: r["c"] for r in con.execute(458 f"""SELECT source, COUNT(*) c FROM ({_MOVES_SQL})459 WHERE price < prev GROUP BY source""", (prev_start, prev_end))}460 breakdowns.append({461 "id": "baisses_bannieres",462 "title": "Baisses de prix détectées par bannière (période)", "kind": "bars",463 "items": [{"label": label_of(s), "value": n,464 "delta_pct": _delta_pct(n, d_src_prev.get(s))}465 for s, n in sorted(d_src_cur.items(), key=lambda kv: -kv[1])[:12]]})466467 # ---- distributions (histogrammes) --------------------------------------------------468 distributions = []469 rabais_rows = con.execute(470 f"""SELECT 100.0 * (regular_price - price) / regular_price pct471 FROM products WHERE active=1 AND on_sale=1 AND {_PRICE_SANE}472 AND regular_price IS NOT NULL AND regular_price > price""").fetchall()473 if rabais_rows:474 edges = [(0, 10), (10, 20), (20, 30), (30, 40), (40, 50), (50, 101)]475 bins = [{"label": ("50 % +" if lo == 50 else f"{lo}-{hi} %"),476 "value": sum(1 for r in rabais_rows if lo <= r["pct"] < hi)}477 for lo, hi in edges]478 distributions.append({"id": "rabais",479 "title": "Distribution des rabais affichés (soldes actifs)",480 "unit": "produits", "bins": bins})481 price_rows = con.execute(482 f"SELECT price FROM products WHERE active=1 AND {_PRICE_SANE}").fetchall()483 if price_rows:484 edges_p = [(0, 2, "0-2 $"), (2, 5, "2-5 $"), (5, 10, "5-10 $"),485 (10, 20, "10-20 $"), (20, 50, "20-50 $"),486 (50, 100, "50-100 $"), (100, 2001, "100 $ +")]487 bins_p = [{"label": lb,488 "value": sum(1 for r in price_rows if lo <= r["price"] < hi)}489 for lo, hi, lb in edges_p]490 distributions.append({"id": "prix",491 "title": "Distribution des prix (produits actifs)",492 "unit": "produits", "bins": bins_p})493494 # ---- heatmap calendrier : relevés de prix par jour ---------------------------------495 heatmap = None496 if has_history:497 hm_start = max(start, now_ts - 183 * 86400)498 cells = [{"date": r["d"], "value": r["c"]} for r in con.execute(499 "SELECT date(ts,'unixepoch','localtime') d, COUNT(*) c FROM price_log"500 " WHERE ts >= ? AND ts < ? GROUP BY d ORDER BY d", (hm_start, end))]501 if cells:502 heatmap = {"title": "Relevés de prix par jour", "cells": cells}503504 # ---- heatmap horaire 7×24 : relevés par jour de semaine × heure --------------------505 hourly = None506 if has_history:507 hcells = [{"dow": (r["w"] + 6) % 7, "hour": r["h"], "value": r["c"]}508 for r in con.execute(509 """SELECT CAST(strftime('%w', ts,'unixepoch','localtime') AS INT) w,510 CAST(strftime('%H', ts,'unixepoch','localtime') AS INT) h,511 COUNT(*) c512 FROM price_log WHERE ts >= ? AND ts < ?513 GROUP BY w, h""", (start, end))]514 if hcells:515 hourly = {"title": "Relevés de prix par jour de semaine et heure",516 "cells": hcells}517518 # ---- tableaux -----------------------------------------------------------------------519 def _cut(s: str, n: int = 26) -> str:520 s = (s or "").strip()521 return s if len(s) <= n else s[: n - 1] + "…"522523 sale_rows = [524 [_cut(r["name"]), label_of(r["source"]), _fmt_money(r["price"]),525 _fmt_money(r["regular_price"]),526 f"−{round(100 * (r['regular_price'] - r['price']) / r['regular_price'])} %"]527 for r in con.execute(528 f"""SELECT name, source, price, regular_price FROM products529 WHERE active=1 AND on_sale=1 AND regular_price IS NOT NULL530 AND {_PRICE_SANE} AND regular_price > price531 ORDER BY (regular_price - price) / regular_price DESC LIMIT 100""")]532533 # prix moyens par catégorie + delta des prix relevés (période vs précédente)534 cat_prices: dict[str, list[float]] = {}535 cat_sales: dict[str, int] = {}536 for r in con.execute(537 f"""SELECT category, price, on_sale FROM products538 WHERE active=1 AND category<>'' AND {_PRICE_SANE}"""):539 cat_prices.setdefault(r["category"], []).append(r["price"])540 cat_sales[r["category"]] = cat_sales.get(r["category"], 0) + (r["on_sale"] or 0)541 cat_obs_cur = {r["cat"]: r["a"] for r in con.execute(542 """SELECT p.category cat, AVG(l.price) a543 FROM price_log l JOIN products p ON p.uid = l.uid544 WHERE l.ts >= ? AND l.ts < ? AND l.price > 0 AND l.price <= 2000545 AND p.category <> '' GROUP BY cat""", (start, end))}546 cat_obs_prev: dict[str, float] = {}547 if has_prev_history:548 cat_obs_prev = {r["cat"]: r["a"] for r in con.execute(549 """SELECT p.category cat, AVG(l.price) a550 FROM price_log l JOIN products p ON p.uid = l.uid551 WHERE l.ts >= ? AND l.ts < ? AND l.price > 0 AND l.price <= 2000552 AND p.category <> '' GROUP BY cat""", (prev_start, prev_end))}553554 def _fmt_delta(cat: str) -> str:555 dp = _delta_pct(cat_obs_cur.get(cat), cat_obs_prev.get(cat))556 if dp is None:557 return "—"558 return f"{'+' if dp >= 0 else '−'}{str(abs(dp)).replace('.', ',')} %"559560 cat_rows = sorted(561 ([cat, len(v), _fmt_money(round(statistics.mean(v), 2)),562 _fmt_money(round(statistics.median(v), 2)), cat_sales.get(cat, 0),563 _fmt_delta(cat)]564 for cat, v in cat_prices.items()), key=lambda r: -r[1])565566 # bannières : produits, soldes, relevés (période), fraîcheur de synchro567 rel_src = {r["s"]: r["c"] for r in con.execute(568 """SELECT p.source s, COUNT(*) c FROM price_log l569 JOIN products p ON p.uid = l.uid570 WHERE l.ts >= ? AND l.ts < ? GROUP BY s""", (start, end))}571 last_sync = {r["source"]: r["ts"] for r in con.execute(572 "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}573 src_price = {r["source"]: r["a"] for r in con.execute(574 f"""SELECT source, AVG(price) a FROM products575 WHERE active=1 AND {_PRICE_SANE} GROUP BY source""")}576 src_rows = [577 [label_of(r["source"]), r["n"], r["sales"] or 0,578 _fmt_money(round(src_price[r["source"]], 2))579 if src_price.get(r["source"]) else "—",580 _fmt_int(rel_src.get(r["source"], 0)),581 (datetime.fromtimestamp(last_sync[r["source"]], TZ).strftime("%Y-%m-%d %H:%M")582 if last_sync.get(r["source"]) else "—")]583 for r in by_src]584585 tables = [586 {"id": "top_soldes", "title": "Top produits en solde (rabais les plus forts)",587 "columns": ["Produit", "Bannière", "Prix", "Prix rég.", "Rabais"],588 "rows": sale_rows},589 {"id": "prix_categories", "title": "Prix moyens par catégorie",590 "columns": ["Catégorie", "Produits", "Prix moyen", "Prix médian",591 "En solde", "Δ prix relevés"],592 "rows": cat_rows},593 {"id": "bannieres", "title": "Bannières — produits, soldes & fraîcheur",594 "columns": ["Bannière", "Produits actifs", "En solde", "Prix moyen",595 "Relevés (période)", "Dernière synchro"],596 "rows": src_rows},597 ]598 if top_drops:599 tables.insert(1, {600 "id": "baisses", "title": "Top baisses de prix détectées (période)",601 "columns": ["Produit", "Bannière", "Avant", "Après", "Baisse"],602 "rows": [[_cut(d["name"]), label_of(d["source"]), _fmt_money(d["old"]),603 _fmt_money(d["new"]), f"−{d['pct']} %".replace(".", ",")]604 for d in top_drops[:100]]})605 if top_hikes:606 tables.insert(2 if top_drops else 1, {607 "id": "hausses", "title": "Top hausses de prix détectées (période)",608 "columns": ["Produit", "Bannière", "Avant", "Après", "Hausse"],609 "rows": [[_cut(d["name"]), label_of(d["source"]), _fmt_money(d["old"]),610 _fmt_money(d["new"]), f"+{d['pct']} %".replace(".", ",")]611 for d in top_hikes[:100]]})612613 # ---- records & faits marquants ------------------------------------------------------614 records = []615 best_sale = con.execute(616 f"""SELECT name, source, price, regular_price,617 (regular_price - price) / regular_price pct618 FROM products WHERE active=1 AND on_sale=1 AND {_PRICE_SANE}619 AND regular_price IS NOT NULL AND regular_price > price620 ORDER BY pct DESC LIMIT 1""").fetchone()621 if best_sale:622 records.append({623 "label": "Record de promo — plus gros rabais affiché",624 "value": f"−{round(100 * best_sale['pct'])} % · {_cut(best_sale['name'], 34)} "625 f"({label_of(best_sale['source'])})"})626 if top_drops:627 d0 = top_drops[0]628 records.append({629 "label": "Plus forte baisse détectée (période)",630 "value": f"−{str(d0['pct']).replace('.', ',')} % · {_cut(d0['name'], 34)} "631 f"({_fmt_money(d0['old'])} → {_fmt_money(d0['new'])})",632 "date": datetime.fromtimestamp(d0["ts"], TZ).date().isoformat()})633 if top_hikes:634 h0 = top_hikes[0]635 records.append({636 "label": "Plus forte hausse détectée (période)",637 "value": f"+{str(h0['pct']).replace('.', ',')} % · {_cut(h0['name'], 34)} "638 f"({_fmt_money(h0['old'])} → {_fmt_money(h0['new'])})",639 "date": datetime.fromtimestamp(h0["ts"], TZ).date().isoformat()})640 if has_history and per_day:641 rec_day = max(per_day.items(), key=lambda kv: kv[1])642 records.append({"label": "Jour record de relevés de prix",643 "value": _fmt_int(rec_day[1]) + " relevés",644 "date": rec_day[0]})645 if new_day:646 rec_new = max(new_day.items(), key=lambda kv: kv[1])647 records.append({"label": "Jour record de nouveaux produits",648 "value": _fmt_int(rec_new[1]) + " produits",649 "date": rec_new[0]})650 if moves:651 records.append({652 "label": "Variations de prix détectées (période)",653 "value": f"{_fmt_int(len(drops))} baisses · {_fmt_int(len(hikes))} hausses"})654 if amp_avg_pct is not None:655 records.append({"label": "Amplitude moyenne des variations de prix",656 "value": f"±{amp_avg_pct} %".replace(".", ",")})657 if rel_src:658 top_rel = max(rel_src.items(), key=lambda kv: kv[1])659 records.append({"label": "Bannière la plus relevée (période)",660 "value": f"{label_of(top_rel[0])} · "661 f"{_fmt_int(top_rel[1])} relevés"})662 if hourly and hourly["cells"]:663 dows = ["lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"]664 peak = max(hourly["cells"], key=lambda c: c["value"])665 records.append({"label": "Heure de pointe des relevés",666 "value": f"{dows[peak['dow']]} {peak['hour']} h · "667 f"{_fmt_int(peak['value'])} relevés"})668 cat_sale_share = [(cat, 100 * cat_sales.get(cat, 0) / len(v))669 for cat, v in cat_prices.items()670 if len(v) >= 50 and cat_sales.get(cat, 0) > 0]671 if cat_sale_share:672 top_cat = max(cat_sale_share, key=lambda kv: kv[1])673 records.append({"label": "Catégorie la plus en solde",674 "value": (f"{top_cat[0]} · {round(top_cat[1], 1)} % des produits"675 ).replace(".", ",")})676 cat_deltas = [(cat, _delta_pct(cat_obs_cur.get(cat), cat_obs_prev.get(cat)))677 for cat in cat_obs_cur if cat_obs_prev.get(cat)]678 cat_deltas = [(c, d) for c, d in cat_deltas if d is not None]679 if cat_deltas:680 up_cat = max(cat_deltas, key=lambda kv: kv[1])681 dn_cat = min(cat_deltas, key=lambda kv: kv[1])682 if up_cat[1] > 0:683 records.append({"label": "Catégorie en plus forte hausse (prix relevés)",684 "value": f"{up_cat[0]} · +{str(up_cat[1]).replace('.', ',')} %"})685 if dn_cat[1] < 0:686 records.append({"label": "Catégorie en plus forte baisse (prix relevés)",687 "value": f"{dn_cat[0]} · −{str(abs(dn_cat[1])).replace('.', ',')} %"})688689 con.close()690 return {691 "updated": datetime.now(TZ).isoformat(timespec="seconds"),692 "period": {"from": f_date.isoformat(), "to": t_date.isoformat(),693 "label": label, "id": period_id},694 "kpis": kpis,695 **({"gauges": gauges} if gauges else {}),696 "series": series,697 **({"multiseries": multiseries} if multiseries else {}),698 **({"stacked": stacked} if stacked else {}),699 "breakdowns": breakdowns,700 **({"distributions": distributions} if distributions else {}),701 **({"heatmap": heatmap} if heatmap else {}),702 **({"hourly": hourly} if hourly else {}),703 "tables": tables,704 "records": records,705 }706707708def dashboard(period: str = "30j", from_s: str | None = None,709 to_s: str | None = None) -> dict:710 """Tableau de bord (contrat SPEC.md v2) — mis en cache 5 minutes par période."""711 key = (period, from_s or "", to_s or "")712 now = time.time()713 with _cache_lock:714 hit = _cache.get(key)715 if hit and now - hit[0] < CACHE_TTL:716 return hit[1]717 data = _compute(period, from_s, to_s)718 with _cache_lock:719 _cache[key] = (now, data)720 if len(_cache) > 64: # borne de sécurité (plages personnalisées)721 oldest = min(_cache, key=lambda k: _cache[k][0])722 _cache.pop(oldest, None)723 return data724