Agrégateur de produits québécois — www.fabri-ka.com
Python 38.4%
HTML 30.3%
TypeScript 17.2%
CSS 11%
JavaScript 3.2%
1# -----------------------------------------------------------------------------2# Fabri-Ka — Agrégateur de produits québécois3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# statsdash.py : tableau de bord /api/stats/dashboard (contrat ka-stats SPEC v2)5# -----------------------------------------------------------------------------6"""Toutes les valeurs viennent de la base réelle (products / stores / sync_log) :7- volumes et nouveautés par jour via `first_seen` (epoch) ;8- prix moyen/médian sur les produits actifs avec prix (> 0, plafond 500 000 $9 pour la moyenne, même garde-fou que /api/stats/extended) ;10- deltas des KPI de stock = état à la fin vs état au DÉBUT de la période11 (reconstruit avec first_seen) ; delta des nouveautés = fenêtre précédente12 de même longueur ; deltas des répartitions = même reconstruction.13- jauges = couvertures mesurées (prix, image, dispo, géoloc, boutiques) ;14- activité horaire = heures réelles de détection (first_seen) ;15- sources & fraîcheur = journal sync_log.16Aucun chiffre inventé : indisponible => champ omis / None (le front masque).17Cache mémoire 5 min par période."""18from __future__ import annotations1920import sqlite321import time22from datetime import date, datetime, timedelta23from zoneinfo import ZoneInfo2425from . import db26from .schema import CATEGORIES2728TZ = ZoneInfo("America/Toronto")29PRICE_CAP = 500_000 # prix aberrants exclus de la moyenne30CACHE_TTL = 300 # ≥ 5 min (SPEC)3132PLATFORM_LABELS = {33 "shopify": "Shopify", "woocommerce": "WooCommerce", "wix": "Wix",34 "squarespace": "Squarespace", "lightspeed": "Lightspeed",35 "prestashop": "PrestaShop", "snipcart": "Snipcart",36 "wordpress": "WordPress", "generic": "Site générique", "": "Inconnue",37}3839ORIGIN_LABELS = {40 "A": "A — Fabriqué au Québec", "B": "B — Conçu au Québec",41 "C": "C — Détaillant québécois", "D": "D — Mixte", "E": "E — À vérifier",42}4344BUCKETS = [45 ("0-10", "Moins de 10 $"), ("10-25", "10 – 25 $"), ("25-50", "25 – 50 $"),46 ("50-100", "50 – 100 $"), ("100-250", "100 – 250 $"),47 ("250-1000", "250 – 1 000 $"), ("1000+", "1 000 $ et plus"),48]4950SIZE_BUCKETS = [51 ("1-10", "1 – 10"), ("11-50", "11 – 50"), ("51-200", "51 – 200"),52 ("201-500", "201 – 500"), ("501-1000", "501 – 1 000"), ("1000+", "> 1 000"),53]545556def _cat_label(key: str | None) -> str:57 return CATEGORIES.get(key or "", (key or "Autre", []))[0]585960def _price(v) -> str:61 if v is None:62 return "—"63 return f"{v:,.2f}".replace(",", " ").replace(".", ",") + " $"646566def _int(v) -> str:67 return f"{int(v):,}".replace(",", " ")686970def _delta(cur, prev) -> float | None:71 """% de variation, None si la base de comparaison est vide (rien d'inventé)."""72 if prev is None or cur is None or prev <= 0:73 return None74 return round(100.0 * (cur - prev) / prev, 1)757677def _ts(d: date) -> float:78 return datetime(d.year, d.month, d.day, tzinfo=TZ).timestamp()798081def _spark(points: list[dict], n: int = 50) -> list[dict] | None:82 """Sous-échantillonne une série pour la sparkline d'un KPI (≥ 2 points)."""83 if len(points) < 2:84 return None85 if len(points) <= n:86 return points87 step = (len(points) - 1) / (n - 1)88 return [points[round(i * step)] for i in range(n)]899091def _bounds(con: sqlite3.Connection, period: str, from_: str | None,92 to: str | None) -> tuple[date, date, str]:93 today = datetime.now(TZ).date()94 if from_ and to:95 start, end = date.fromisoformat(from_), date.fromisoformat(to)96 if end < start:97 start, end = end, start98 return start, min(end, today), f"du {start} au {min(end, today)}"99 days = {"7j": (7, "7 jours"), "30j": (30, "30 jours"), "3m": (90, "3 mois"),100 "6m": (180, "6 mois"), "12m": (365, "12 mois")}101 if period == "auj":102 return today, today, "aujourd'hui"103 if period == "annee":104 return date(today.year, 1, 1), today, f"année {today.year}"105 if period == "tout":106 row = con.execute("SELECT MIN(first_seen) FROM products").fetchone()107 start = (datetime.fromtimestamp(row[0], TZ).date()108 if row and row[0] else today)109 return start, today, "toute la période"110 n, label = days.get(period, days["30j"])111 return today - timedelta(days=n - 1), today, label112113114def _daily(con, t0: float, t1: float) -> dict[str, int]:115 """Nouveaux produits par jour (date locale) dans [t0, t1)."""116 rows = con.execute(117 """SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) AS n118 FROM products WHERE first_seen>=? AND first_seen<? GROUP BY d""",119 (t0, t1)).fetchall()120 return {r[0]: r[1] for r in rows}121122123def _median(con, extra_where: str = "", args: tuple = ()) -> float | None:124 n = con.execute(125 f"SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND price>0{extra_where}",126 args).fetchone()[0]127 if not n:128 return None129 row = con.execute(130 f"""SELECT price FROM products WHERE active=1 AND listing_status='published' AND price>0{extra_where}131 ORDER BY price LIMIT 1 OFFSET ?""", args + (n // 2,)).fetchone()132 return round(row[0], 2) if row else None133134135def _build(period: str, from_: str | None, to: str | None) -> dict:136 con = db.connect()137 try:138 start, end, label = _bounds(con, period, from_, to)139 ndays = (end - start).days + 1140 t0, t1 = _ts(start), _ts(end + timedelta(days=1))141 p_start, p_end = start - timedelta(days=ndays), start - timedelta(days=1)142 pt0, pt1 = _ts(p_start), _ts(p_end + timedelta(days=1))143 one = lambda sql, a=(): con.execute(sql, a).fetchone()[0] # noqa: E731144 days = [start + timedelta(days=i) for i in range(min(ndays, 400))]145 iso_days = [d.isoformat() for d in days]146147 # ----- KPI : état courant vs état au début de la période -------------148 total = one("SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published'")149 total_t0 = one("SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND first_seen<?", (t0,))150 stores_live = one("SELECT COUNT(*) FROM stores WHERE product_count>0")151 stores_t0 = one("""SELECT COUNT(DISTINCT store_id) FROM products152 WHERE active=1 AND listing_status='published' AND first_seen<?""", (t0,))153 stores_reg = one("SELECT COUNT(*) FROM stores")154 new_cur = one("SELECT COUNT(*) FROM products WHERE first_seen>=? AND first_seen<?", (t0, t1))155 new_prev = one("SELECT COUNT(*) FROM products WHERE first_seen>=? AND first_seen<?", (pt0, pt1))156 avg_now = one("""SELECT ROUND(AVG(price),2) FROM products157 WHERE active=1 AND listing_status='published' AND price>0 AND price<=?""", (PRICE_CAP,))158 avg_t0 = one("""SELECT ROUND(AVG(price),2) FROM products159 WHERE active=1 AND listing_status='published' AND price>0 AND price<=? AND first_seen<?""",160 (PRICE_CAP, t0))161 med_now = _median(con)162 med_t0 = _median(con, " AND first_seen<?", (t0,))163 cats = one("SELECT COUNT(DISTINCT category) FROM products WHERE active=1 AND listing_status='published' AND category<>''")164 cats_t0 = one("""SELECT COUNT(DISTINCT category) FROM products165 WHERE active=1 AND listing_status='published' AND category<>'' AND first_seen<?""", (t0,))166 regions = one("""SELECT COUNT(DISTINCT region) FROM stores167 WHERE region<>'' AND product_count>0""")168 regions_t0 = one("""SELECT COUNT(DISTINCT s.region) FROM stores s169 JOIN products p ON p.store_id=s.id170 WHERE s.region<>'' AND p.active=1 AND p.listing_status='published' AND p.first_seen<?""", (t0,))171 img_where = " AND images IS NOT NULL AND images<>'' AND images<>'[]'"172 with_img = one(f"SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published'{img_where}")173 with_img_t0 = one(f"""SELECT COUNT(*) FROM products174 WHERE active=1 AND listing_status='published' AND first_seen<?{img_where}""", (t0,))175176 # ----- séries quotidiennes (aussi utilisées comme sparklines) --------177 cur_daily = _daily(con, t0, t1)178 prev_daily = _daily(con, pt0, pt1)179 new_points = [{"t": d, "v": cur_daily.get(d, 0)} for d in iso_days]180 compare = None181 if sum(prev_daily.values()):182 pdays = [(p_start + timedelta(days=i)).isoformat()183 for i in range(min(ndays, 400))]184 compare = [{"t": d, "v": prev_daily.get(d, 0)} for d in pdays]185 base = one("SELECT COUNT(*) FROM products WHERE first_seen<?", (t0,))186 cum_points, acc = [], base187 for pt in new_points:188 acc += pt["v"]189 cum_points.append({"t": pt["t"], "v": acc})190191 # prix moyen cumulatif du catalogue détecté (reconstruit via first_seen)192 pr = con.execute("""SELECT date(first_seen,'unixepoch','localtime') AS d,193 SUM(price), COUNT(*) FROM products194 WHERE price>0 AND price<=? GROUP BY d""",195 (PRICE_CAP,)).fetchall()196 psum = pcnt = 0.0197 by_day = {}198 for d, s, n in pr:199 by_day[d] = (s, n)200 start_iso = start.isoformat()201 for d, (s, n) in by_day.items():202 if d < start_iso:203 psum += s204 pcnt += n205 avg_points = []206 for d in iso_days:207 s, n = by_day.get(d, (0.0, 0))208 psum += s209 pcnt += n210 if pcnt:211 avg_points.append({"t": d, "v": round(psum / pcnt, 2)})212213 # boutiques cumulées (première détection d'un produit par boutique)214 st_first = con.execute("""SELECT date(first_seen,'unixepoch','localtime') AS d,215 COUNT(*) FROM (216 SELECT store_id, MIN(first_seen) AS first_seen217 FROM products GROUP BY store_id) GROUP BY d""").fetchall()218 st_by_day = {r[0]: r[1] for r in st_first}219 st_acc = sum(v for d, v in st_by_day.items() if d < start_iso)220 st_points = []221 for d in iso_days:222 st_acc += st_by_day.get(d, 0)223 st_points.append({"t": d, "v": st_acc})224225 def kpi(id_, lab, value, prev=None, unit="", spark=None):226 d = _delta(value, prev)227 out = {"id": id_, "label": lab, "value": value, "unit": unit,228 "delta_pct": d,229 "direction": None if d is None else ("up" if d >= 0 else "down")}230 sp = _spark(spark or [])231 if sp:232 out["spark"] = sp233 return out234235 kpis = [236 kpi("produits", "Produits actifs au catalogue", total, total_t0,237 spark=cum_points),238 kpi("boutiques", "Boutiques en ligne avec produits", stores_live,239 stores_t0, spark=st_points),240 kpi("nouveautes", f"Nouveaux produits ({label})", new_cur, new_prev,241 spark=new_points),242 kpi("prix_moyen", "Prix moyen (produits actifs)", avg_now, avg_t0,243 unit="$", spark=avg_points),244 kpi("prix_median", "Prix médian (produits actifs)", med_now, med_t0,245 unit="$"),246 kpi("avec_image", "Produits actifs avec image", with_img, with_img_t0),247 kpi("categories", "Catégories couvertes", cats, cats_t0),248 kpi("regions", "Régions avec boutiques actives", regions, regions_t0),249 kpi("registre", "Boutiques au registre", stores_reg),250 ]251252 # ----- jauges : couvertures mesurées ----------------------------------253 with_price = one("SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND price>0")254 avail = one("SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND available=1")255 live_geo = one("""SELECT COUNT(*) FROM stores256 WHERE product_count>0 AND region<>'' AND region IS NOT NULL""")257 pct = lambda a, b: round(100.0 * a / b, 1) if b else None # noqa: E731258 gauges = []259 for gid, glabel, val, help_ in [260 ("prix", "Produits avec prix affiché", pct(with_price, total),261 "part des produits actifs dont le prix est connu"),262 ("image", "Produits avec image", pct(with_img, total),263 "part des produits actifs avec au moins une image"),264 ("dispo", "Produits en stock", pct(avail, total),265 "part des produits actifs marqués disponibles à la source"),266 ("boutiques_actives", "Boutiques du registre avec produits",267 pct(stores_live, stores_reg),268 "boutiques dont le catalogue public est agrégé"),269 ("geoloc", "Boutiques actives géolocalisées", pct(live_geo, stores_live),270 "boutiques actives avec région administrative connue"),271 ]:272 if val is not None:273 gauges.append({"id": gid, "label": glabel, "value": val,274 "max": 100, "unit": "%", "help": help_})275276 # ----- séries ----------------------------------------------------------277 series = [278 {"id": "nouveautes_jour", "title": "Nouveaux produits détectés par jour",279 "unit": "produits", "kind": "line" if compare else "bar",280 "points": new_points, **({"compare": compare} if compare else {})},281 {"id": "cumul", "title": "Produits détectés — cumul du catalogue",282 "unit": "produits", "kind": "area", "points": cum_points},283 ]284 if len(avg_points) > 1:285 series.append(286 {"id": "prix_moyen", "title": "Prix moyen du catalogue détecté (cumul)",287 "unit": "$", "kind": "line", "points": avg_points})288289 # ----- multi-courbes : nouveautés des 4 premières catégories ----------290 top4 = [r[0] for r in con.execute(291 """SELECT category FROM products WHERE first_seen>=? AND first_seen<?292 AND category<>'' GROUP BY category ORDER BY COUNT(*) DESC LIMIT 4""",293 (t0, t1)).fetchall()]294 multiseries = []295 if top4 and ndays > 1:296 qmarks = ",".join("?" * len(top4))297 rows = con.execute(298 f"""SELECT category, date(first_seen,'unixepoch','localtime') AS d,299 COUNT(*) FROM products300 WHERE first_seen>=? AND first_seen<? AND category IN ({qmarks})301 GROUP BY category, d""", (t0, t1, *top4)).fetchall()302 grid: dict[str, dict[str, int]] = {c: {} for c in top4}303 for c, d, n in rows:304 grid[c][d] = n305 multiseries.append({306 "id": "cats_nouveautes",307 "title": "Nouveaux produits par jour — top catégories",308 "unit": "produits",309 "series": [{"label": _cat_label(c),310 "points": [{"t": d, "v": grid[c].get(d, 0)}311 for d in iso_days]} for c in top4]})312313 # ----- barres empilées : ajouts par plateforme e-commerce -------------314 plat_rows = con.execute(315 """SELECT COALESCE(s.platform,''), date(p.first_seen,'unixepoch','localtime') AS d,316 COUNT(*) FROM products p JOIN stores s ON s.id=p.store_id317 WHERE p.first_seen>=? AND p.first_seen<? GROUP BY 1, d""",318 (t0, t1)).fetchall()319 stacked = []320 if plat_rows and ndays > 1:321 tot_by_plat: dict[str, int] = {}322 for pl, d, n in plat_rows:323 tot_by_plat[pl] = tot_by_plat.get(pl, 0) + n324 top_pl = sorted(tot_by_plat, key=tot_by_plat.get, reverse=True)[:5]325 others = [pl for pl in tot_by_plat if pl not in top_pl]326 keys = [PLATFORM_LABELS.get(pl, pl or "Inconnue") for pl in top_pl]327 if others:328 keys.append("Autres")329 pgrid: dict[str, dict[str, int]] = {}330 for pl, d, n in plat_rows:331 k = (PLATFORM_LABELS.get(pl, pl or "Inconnue")332 if pl in top_pl else "Autres")333 pgrid.setdefault(d, {})334 pgrid[d][k] = pgrid[d].get(k, 0) + n335 stacked.append({336 "id": "ajouts_plateforme",337 "title": "Nouveaux produits par jour, par plateforme e-commerce",338 "unit": "produits", "keys": keys,339 "points": [{"t": d, "values": [pgrid.get(d, {}).get(k, 0)340 for k in keys]} for d in iso_days]})341342 # ----- répartitions ---------------------------------------------------343 plat = con.execute("""SELECT platform, COUNT(*) FROM stores344 WHERE product_count>0 GROUP BY platform345 ORDER BY 2 DESC""").fetchall()346 top_cats = con.execute("""SELECT category, COUNT(*) FROM products347 WHERE active=1 AND listing_status='published' GROUP BY category348 ORDER BY 2 DESC LIMIT 12""").fetchall()349 cats_t0_rows = dict(con.execute(350 """SELECT category, COUNT(*) FROM products351 WHERE active=1 AND listing_status='published' AND first_seen<? GROUP BY category""", (t0,)).fetchall())352 # donut catégories : top 7 + « Autres »353 donut_cats = [{"label": _cat_label(c), "value": n} for c, n in top_cats[:7]]354 rest = total - sum(n for _, n in top_cats[:7])355 if rest > 0:356 donut_cats.append({"label": "Autres", "value": rest})357 origin_rows = con.execute(358 """SELECT COALESCE(s.origin_class,''), COUNT(p.uid) FROM stores s359 JOIN products p ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'360 GROUP BY 1 ORDER BY 2 DESC""").fetchall()361 region_now = con.execute(362 """SELECT s.region, COUNT(p.uid) FROM stores s363 JOIN products p ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'364 WHERE s.region<>'' GROUP BY s.region ORDER BY 2 DESC""").fetchall()365 region_t0 = dict(con.execute(366 """SELECT s.region, COUNT(p.uid) FROM stores s367 JOIN products p ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'368 AND p.first_seen<?369 WHERE s.region<>'' GROUP BY s.region""", (t0,)).fetchall())370 breakdowns = [371 {"id": "categories", "title": "Produits actifs par catégorie",372 "kind": "donut", "items": donut_cats},373 {"id": "plateformes", "title": "Boutiques par plateforme e-commerce",374 "kind": "donut",375 "items": [{"label": PLATFORM_LABELS.get(p or "", p or "Inconnue"),376 "value": n} for p, n in plat]},377 {"id": "regions_delta", "title": "Produits actifs par région (Δ période)",378 "kind": "bars",379 "items": [{"label": r, "value": n,380 "delta_pct": _delta(n, region_t0.get(r))}381 for r, n in region_now[:14]]},382 {"id": "origine", "title": "Produits par classe d'origine (A–E)",383 "kind": "bars",384 "items": [{"label": ORIGIN_LABELS.get(o, o or "Non classée"),385 "value": n} for o, n in origin_rows]},386 ]387388 # ----- distributions --------------------------------------------------389 buckets = dict(con.execute("""SELECT CASE390 WHEN price < 10 THEN '0-10' WHEN price < 25 THEN '10-25'391 WHEN price < 50 THEN '25-50' WHEN price < 100 THEN '50-100'392 WHEN price < 250 THEN '100-250' WHEN price < 1000 THEN '250-1000'393 ELSE '1000+' END AS b, COUNT(*) FROM products394 WHERE active=1 AND listing_status='published' AND price>0 GROUP BY b""").fetchall())395 sizes = dict(con.execute("""SELECT CASE396 WHEN product_count <= 10 THEN '1-10'397 WHEN product_count <= 50 THEN '11-50'398 WHEN product_count <= 200 THEN '51-200'399 WHEN product_count <= 500 THEN '201-500'400 WHEN product_count <= 1000 THEN '501-1000'401 ELSE '1000+' END AS b, COUNT(*) FROM stores402 WHERE product_count>0 GROUP BY b""").fetchall())403 distributions = [404 {"id": "prix", "title": "Distribution des prix (produits actifs)",405 "unit": "produits",406 "bins": [{"label": lab, "value": buckets[k]}407 for k, lab in BUCKETS if buckets.get(k)]},408 {"id": "taille_boutiques",409 "title": "Boutiques par taille de catalogue (nb de produits)",410 "unit": "boutiques",411 "bins": [{"label": lab, "value": sizes[k]}412 for k, lab in SIZE_BUCKETS if sizes.get(k)]},413 ]414415 geo = {"title": "Produits actifs par région",416 "items": [{"label": r, "value": n} for r, n in region_now]}417418 # ----- heatmap : nouveautés/jour sur 26 semaines ----------------------419 h_start = datetime.now(TZ).date() - timedelta(days=181)420 hm = _daily(con, _ts(h_start), _ts(datetime.now(TZ).date() + timedelta(days=1)))421 heatmap = {"title": "Nouveaux produits par jour",422 "cells": [{"date": d, "value": v} for d, v in sorted(hm.items())]}423424 # ----- heatmap horaire 7×24 : heures réelles de détection -------------425 hr = con.execute(426 """SELECT strftime('%w', first_seen,'unixepoch','localtime') AS w,427 strftime('%H', first_seen,'unixepoch','localtime') AS h,428 COUNT(*) FROM products429 WHERE first_seen>=? AND first_seen<? GROUP BY w, h""",430 (t0, t1)).fetchall()431 hourly = None432 if hr:433 hourly = {"title": "Produits détectés par heure (période)",434 "cells": [{"dow": (int(w) + 6) % 7, "hour": int(h), "value": n}435 for w, h, n in hr]}436437 # ----- tableaux --------------------------------------------------------438 # une seule passe agrégée sur les produits des 50 boutiques (les439 # sous-requêtes corrélées balayaient les 3 900+ boutiques : > 2 min440 # sur la DB de 1,9 Go)441 shop_rows = con.execute("""442 WITH top AS (SELECT id, name, region, product_count FROM stores443 WHERE product_count>0444 ORDER BY product_count DESC LIMIT 50)445 SELECT t.name, t.region, t.product_count,446 COALESCE(a.nouv, 0) AS nouv, a.pavg447 FROM top t LEFT JOIN (448 SELECT store_id,449 SUM(CASE WHEN first_seen>=? AND first_seen<?450 THEN 1 ELSE 0 END) AS nouv,451 ROUND(AVG(CASE WHEN active=1452 AND listing_status='published'453 AND price>0 AND price<=?454 THEN price END), 2) AS pavg455 FROM products WHERE store_id IN (SELECT id FROM top)456 GROUP BY store_id) a ON a.store_id=t.id457 ORDER BY t.product_count DESC""", (t0, t1, PRICE_CAP)).fetchall()458 cat_rows = con.execute("""459 SELECT category, COUNT(*) AS n, COUNT(DISTINCT store_id) AS st,460 ROUND(AVG(CASE WHEN price>0 AND price<=? THEN price END),2)461 FROM products WHERE active=1 AND listing_status='published' GROUP BY category462 ORDER BY n DESC""", (PRICE_CAP,)).fetchall()463 region_tbl = con.execute("""464 SELECT s.region, COUNT(DISTINCT s.id) AS st, COUNT(p.uid) AS n,465 ROUND(AVG(CASE WHEN p.price>0 AND p.price<=? THEN p.price END),2),466 SUM(CASE WHEN p.first_seen>=? AND p.first_seen<? THEN 1 ELSE 0 END)467 FROM stores s JOIN products p ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'468 WHERE s.region<>'' GROUP BY s.region ORDER BY n DESC""",469 (PRICE_CAP, t0, t1)).fetchall()470 nouv_rows = con.execute("""471 SELECT p.title, s.name, p.category, p.price,472 date(p.first_seen,'unixepoch','localtime')473 FROM products p JOIN stores s ON s.id=p.store_id474 WHERE p.first_seen>=? AND p.first_seen<?475 ORDER BY p.first_seen DESC LIMIT 50""", (t0, t1)).fetchall()476 sync_rows = con.execute("""477 SELECT datetime(ts,'unixepoch','localtime'), store_id, found, added,478 updated, removed, status479 FROM sync_log WHERE ts>=? AND ts<? ORDER BY ts DESC LIMIT 50""",480 (t0, t1)).fetchall()481 tables = [482 {"id": "top_boutiques", "title": "Top boutiques",483 "columns": ["Boutique", "Région", "Produits", "Nouveautés (période)", "Prix moyen"],484 "rows": [[name or "—", reg or "—", n, nouv, _price(pavg)]485 for name, reg, n, nouv, pavg in shop_rows]},486 {"id": "categories", "title": "Catégories en détail",487 "columns": ["Catégorie", "Produits", "Boutiques", "Prix moyen", "Prix médian"],488 "rows": [[_cat_label(c), n, st, _price(pavg),489 _price(_median(con, " AND category=?", (c,)))]490 for c, n, st, pavg in cat_rows]},491 {"id": "regions", "title": "Régions en détail",492 "columns": ["Région", "Boutiques", "Produits", "Prix moyen", "Nouveautés (période)"],493 "rows": [[r, st, n, _price(pavg), nouv]494 for r, st, n, pavg, nouv in region_tbl]},495 ]496 if nouv_rows:497 tables.append(498 {"id": "nouveautes", "title": "Nouveautés récentes (période)",499 "columns": ["Produit", "Boutique", "Catégorie", "Prix", "Détecté le"],500 "rows": [[(t or "—")[:60], (s or "—")[:36], _cat_label(c),501 _price(p if p and p > 0 else None), d]502 for t, s, c, p, d in nouv_rows]})503 if sync_rows:504 tables.append(505 {"id": "syncs", "title": "Sources & fraîcheur — dernières synchronisations",506 "columns": ["Date", "Boutique", "Trouvés", "Ajoutés", "Mis à jour",507 "Retirés", "Statut"],508 "rows": [[d, sid, f or 0, a or 0, u or 0, rm or 0, st or "—"]509 for d, sid, f, a, u, rm, st in sync_rows]})510511 # ----- records & faits marquants --------------------------------------512 records = []513 if cur_daily:514 day, v = max(cur_daily.items(), key=lambda kv: kv[1])515 records.append({"label": "Jour record de nouveautés (période)",516 "value": _int(v) + " produits", "date": day})517 all_time = con.execute(518 """SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) AS n519 FROM products GROUP BY d ORDER BY n DESC LIMIT 1""").fetchone()520 if all_time:521 records.append({"label": "Jour record de nouveautés (depuis le début)",522 "value": _int(all_time[1]) + " produits",523 "date": all_time[0]})524 top_shop = con.execute("""SELECT s.name, COUNT(*) FROM products p525 JOIN stores s ON s.id=p.store_id WHERE p.first_seen>=? AND p.first_seen<?526 GROUP BY p.store_id ORDER BY 2 DESC LIMIT 1""", (t0, t1)).fetchone()527 if top_shop:528 records.append({"label": f"Boutique la plus prolifique (période) — {(top_shop[0] or '')[:40]}",529 "value": _int(top_shop[1]) + " nouveautés"})530 big_shop = con.execute("""SELECT name, product_count FROM stores531 ORDER BY product_count DESC LIMIT 1""").fetchone()532 if big_shop and big_shop[1]:533 records.append({"label": f"Boutique la mieux garnie — {(big_shop[0] or '')[:40]}",534 "value": _int(big_shop[1]) + " produits"})535 top_region = con.execute("""SELECT s.region, COUNT(*) FROM products p536 JOIN stores s ON s.id=p.store_id WHERE s.region<>''537 AND p.first_seen>=? AND p.first_seen<? GROUP BY s.region538 ORDER BY 2 DESC LIMIT 1""", (t0, t1)).fetchone()539 if top_region:540 records.append({"label": "Région la plus active (période)",541 "value": f"{top_region[0]} — " + _int(top_region[1]) + " nouveautés"})542 if top_cats:543 records.append({"label": "Catégorie la plus fournie",544 "value": f"{_cat_label(top_cats[0][0])} — "545 + _int(top_cats[0][1]) + " produits"})546 rich_cat = con.execute("""SELECT category,547 ROUND(AVG(CASE WHEN price>0 AND price<=? THEN price END),2) AS a548 FROM products WHERE active=1 AND listing_status='published' GROUP BY category549 HAVING COUNT(*)>=100 AND a IS NOT NULL550 ORDER BY a DESC LIMIT 1""", (PRICE_CAP,)).fetchone()551 if rich_cat:552 records.append({"label": f"Catégorie au prix moyen le plus élevé — {_cat_label(rich_cat[0])}",553 "value": _price(rich_cat[1])})554 if plat:555 records.append({"label": "Plateforme e-commerce dominante",556 "value": f"{PLATFORM_LABELS.get(plat[0][0] or '', plat[0][0] or 'Inconnue')} — "557 + _int(plat[0][1]) + " boutiques"})558 dear = con.execute("""SELECT p.title, p.price, s.name FROM products p559 JOIN stores s ON s.id=p.store_id WHERE p.active=1 AND p.listing_status='published' AND p.price>0560 AND p.price<=? ORDER BY p.price DESC LIMIT 1""", (PRICE_CAP,)).fetchone()561 if dear:562 records.append({"label": f"Produit le plus cher au catalogue — {(dear[0] or '')[:34]} ({dear[2]})",563 "value": _price(dear[1])})564 best_sync = con.execute("""SELECT store_id, added,565 date(ts,'unixepoch','localtime') FROM sync_log566 WHERE ts>=? AND ts<? AND added>0567 ORDER BY added DESC LIMIT 1""", (t0, t1)).fetchone()568 if best_sync:569 records.append({"label": f"Synchronisation la plus fructueuse (période) — {best_sync[0]}",570 "value": _int(best_sync[1]) + " ajouts",571 "date": best_sync[2]})572573 out = {574 "updated": datetime.now(TZ).isoformat(timespec="seconds"),575 "period": {"from": start.isoformat(), "to": end.isoformat(), "label": label},576 "kpis": kpis, "gauges": gauges, "series": series,577 "breakdowns": breakdowns, "distributions": distributions,578 "geo": geo, "heatmap": heatmap, "tables": tables, "records": records,579 }580 if multiseries:581 out["multiseries"] = multiseries582 if stacked:583 out["stacked"] = stacked584 if hourly:585 out["hourly"] = hourly586 return out587 finally:588 con.close()589590591_cache: dict[tuple, tuple[float, dict]] = {}592593594def dashboard(period: str = "30j", from_: str | None = None,595 to: str | None = None) -> dict:596 key = (period, from_ or "", to or "")597 hit = _cache.get(key)598 if hit and time.time() - hit[0] < CACHE_TTL:599 return hit[1]600 data = _build(period, from_, to)601 if len(_cache) > 64:602 _cache.clear()603 _cache[key] = (time.time(), data)604 return data605