Python 61.6%
TypeScript 20.9%
CSS 11.4%
JavaScript 5.1%
HTML 1.1%
1# -----------------------------------------------------------------------------2# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# statsdash.py : tableau de bord analytique — construit le JSON du contrat5# commun Groupe KA v2 (/api/stats/dashboard, voir ka/stats/SPEC.md)6# à partir des données réelles (vehicles, price_log, sync_log).7# Sert aussi de source unique aux 5 rapports PDF (autoka/kapdf.py).8#9# Principes :10# - AUCUNE statistique inventée : tout vient de la base SQLite. Un champ sans11# donnée mesurable est simplement omis (le front affiche « Pas encore12# mesuré »).13# - L'« inventaire à la date t » est reconstruit depuis le cycle de vie réel14# des annonces : first_seen (arrivée) et updated_at des annonces15# désactivées (retrait). Les prix/km historiques par jour utilisent le16# dernier prix/km connu de chaque véhicule (approximation documentée).17# - Les séries sont bornées au début réel des données (12 août 2026) :18# avant, rien n'était mesuré — on ne trace pas de faux zéros. De même,19# les deltas vs période précédente ne sont émis que si la référence20# existait déjà (start > début des données).21# - Cache serveur de 5 minutes par période.22# -----------------------------------------------------------------------------23from __future__ import annotations2425import threading26import time27from collections import Counter, defaultdict28from datetime import date, datetime, timedelta29from zoneinfo import ZoneInfo3031from . import db3233TZ = ZoneInfo("America/Toronto")34CACHE_TTL = 300 # 5 minutes3536_cache: dict[tuple, tuple[float, dict]] = {}37_cache_lock = threading.Lock()3839PERIOD_LABELS = {40 "auj": "Aujourd'hui",41 "7j": "7 jours",42 "30j": "30 jours",43 "3m": "3 mois",44 "6m": "6 mois",45 "12m": "12 mois",46 "annee": "Année en cours",47 "tout": "Toute la période",48}49PERIOD_DAYS = {"auj": 1, "7j": 7, "30j": 30, "3m": 90, "6m": 180, "12m": 365}5051# Filtre « type de vendeur » : les annonces de particuliers (Kijiji) portent52# un dealer_name « Particulier (…) » — tout le reste vient de commerces.53PRIV = "dealer_name LIKE 'Particulier%'"545556# --- utilitaires ---------------------------------------------------------------5758def _day_start(d: date) -> float:59 return datetime(d.year, d.month, d.day, tzinfo=TZ).timestamp()606162def _to_date(ts: float) -> date:63 return datetime.fromtimestamp(ts, TZ).date()646566def _fr_int(n) -> str:67 return f"{int(round(n)):,}".replace(",", " ")686970def _fr_money(p) -> str:71 if p is None:72 return "—"73 return _fr_int(p) + " $"747576def _fr_km(k) -> str:77 if k is None:78 return "—"79 return _fr_int(k) + " km"808182def _fr_pct(p) -> str:83 if p is None:84 return "—"85 return ("+" if p >= 0 else "−") + f"{abs(p):.1f}".replace(".", ",") + " %"868788def _delta(cur, prev) -> tuple[float | None, str | None]:89 """Variation en % vs période précédente ; None si non mesurable."""90 if cur is None or prev is None or not prev:91 return None, None92 pct = round(100.0 * (cur - prev) / prev, 1)93 return pct, ("up" if pct >= 0 else "down")949596def _resolve_period(period: str, from_: str | None, to: str | None):97 """(start_ts, end_ts, prev_start_ts, prev_end_ts, label, from_iso, to_iso)."""98 now = datetime.now(TZ)99 today = now.date()100 if from_ and to:101 d0 = date.fromisoformat(from_)102 d1 = date.fromisoformat(to)103 if d1 < d0:104 d0, d1 = d1, d0105 start = _day_start(d0)106 end = min(now.timestamp(), _day_start(d1 + timedelta(days=1)))107 label = f"du {d0.isoformat()} au {d1.isoformat()}"108 elif period == "tout":109 con = db.connect()110 row = con.execute("SELECT MIN(first_seen) m FROM vehicles").fetchone()111 con.close()112 start = row["m"] or now.timestamp()113 end = now.timestamp()114 label = PERIOD_LABELS["tout"]115 elif period == "annee":116 start = _day_start(date(today.year, 1, 1))117 end = now.timestamp()118 label = f"Année {today.year}"119 else:120 days = PERIOD_DAYS.get(period, 30)121 start = _day_start(today - timedelta(days=days - 1))122 end = now.timestamp()123 label = PERIOD_LABELS.get(period, PERIOD_LABELS["30j"])124 span = max(end - start, 1.0)125 return start, end, start - span, start, label, \126 _to_date(start).isoformat(), _to_date(end - 1).isoformat()127128129# --- reconstruction de l'inventaire par jour ------------------------------------130131def _lifecycle_rows(con):132 """(arrivée, retrait|None, prix, km, marque) pour chaque annonce auto."""133 return [134 (r["first_seen"], r["updated_at"] if not r["active"] else None,135 r["price"], r["mileage_km"], r["make"])136 for r in con.execute(137 "SELECT first_seen, active, updated_at, price, mileage_km, make"138 " FROM vehicles WHERE kind='auto' AND first_seen IS NOT NULL")139 ]140141142def _daily_series(rows, start_ts: float, end_ts: float):143 """Par jour : inventaire actif, prix/km moyens de l'inventaire, arrivées,144 retraits.145146 Balayage d'événements (arrivées/retraits triés) — l'inventaire au soir du147 jour J = annonces arrivées avant la fin de J et pas encore retirées.148 """149 if not rows:150 return []151 data_start = min(r[0] for r in rows)152 d = max(_to_date(start_ts), _to_date(data_start))153 d_end = _to_date(end_ts - 1)154 if d > d_end:155 return []156 adds = sorted(rows, key=lambda r: r[0])157 rems = sorted((r for r in rows if r[1] is not None), key=lambda r: r[1])158 new_by_day = Counter(_to_date(r[0]) for r in rows)159 gone_by_day = Counter(_to_date(r[1]) for r in rows if r[1] is not None)160 ai = ri = count = n_price = n_km = 0161 sum_price = sum_km = 0.0162 out = []163 while d <= d_end:164 cutoff = _day_start(d + timedelta(days=1))165 while ai < len(adds) and adds[ai][0] < cutoff:166 count += 1167 if adds[ai][2] is not None:168 sum_price += adds[ai][2]169 n_price += 1170 if adds[ai][3] is not None:171 sum_km += adds[ai][3]172 n_km += 1173 ai += 1174 while ri < len(rems) and rems[ri][1] < cutoff:175 count -= 1176 if rems[ri][2] is not None:177 sum_price -= rems[ri][2]178 n_price -= 1179 if rems[ri][3] is not None:180 sum_km -= rems[ri][3]181 n_km -= 1182 ri += 1183 out.append({184 "t": d.isoformat(),185 "inv": count,186 "avg_price": round(sum_price / n_price) if n_price else None,187 "avg_km": round(sum_km / n_km) if n_km else None,188 "new": new_by_day.get(d, 0),189 "gone": gone_by_day.get(d, 0),190 })191 d += timedelta(days=1)192 return out193194195def _snapshot(con, t: float):196 """Indicateurs de l'inventaire actif reconstitué à l'instant t."""197 return dict(con.execute(198 """SELECT COUNT(*) n, AVG(price) avg_price, AVG(mileage_km) avg_km,199 AVG(year) avg_year,200 COUNT(DISTINCT CASE WHEN NOT {priv} THEN dealer_name END) dealers,201 SUM(CASE WHEN {priv} THEN 1 ELSE 0 END) private,202 COUNT(DISTINCT source) sources203 FROM vehicles WHERE kind='auto' AND first_seen<=?204 AND (active=1 OR updated_at>?)""".format(priv=PRIV),205 (t, t)).fetchone())206207208def _snapshot_counts(con, t: float, expr: str) -> dict[str, int]:209 """Effectifs par catégorie de l'inventaire reconstitué à l'instant t."""210 return {r["lab"]: r["n"] for r in con.execute(211 f"""SELECT {expr} lab, COUNT(*) n FROM vehicles212 WHERE kind='auto' AND first_seen<=? AND (active=1 OR updated_at>?)213 GROUP BY lab""", (t, t))}214215216def _price_drops(con, t0: float, t1: float) -> int:217 """Baisses de prix observées dans price_log entre t0 et t1."""218 return con.execute(219 """WITH pl AS (220 SELECT uid, ts, price,221 LAG(price) OVER (PARTITION BY uid ORDER BY ts) prev_price222 FROM price_log WHERE price IS NOT NULL)223 SELECT COUNT(*) n FROM pl224 WHERE ts>=? AND ts<? AND prev_price IS NOT NULL225 AND prev_price > price""", (t0, t1)).fetchone()["n"]226227228# --- construction du dashboard ---------------------------------------------------229230def _build(period: str, from_: str | None, to: str | None) -> dict:231 start, end, pstart, pend, label, f_iso, t_iso = _resolve_period(period, from_, to)232 con = db.connect()233 now_ts = time.time()234235 # --- KPI : maintenant vs début de période / période précédente -------------236 cur = _snapshot(con, min(end, now_ts))237 # Pas de delta si la période commence avant le début réel des données :238 # l'inventaire de référence n'existait pas encore (rien d'inventé).239 data_start = con.execute(240 "SELECT MIN(first_seen) m FROM vehicles WHERE kind='auto'").fetchone()["m"]241 has_ref = data_start is not None and start > data_start242 if has_ref:243 ref = _snapshot(con, start)244 else:245 ref = {"n": None, "avg_price": None, "avg_km": None, "avg_year": None,246 "dealers": None, "private": None, "sources": None}247248 def _count(sql, args):249 return con.execute(sql, args).fetchone()["n"]250251 new_cur = _count("SELECT COUNT(*) n FROM vehicles WHERE kind='auto'"252 " AND first_seen>=? AND first_seen<?", (start, end))253 new_prev = _count("SELECT COUNT(*) n FROM vehicles WHERE kind='auto'"254 " AND first_seen>=? AND first_seen<?", (pstart, pend))255 gone_cur = _count("SELECT COUNT(*) n FROM vehicles WHERE kind='auto'"256 " AND active=0 AND updated_at>=? AND updated_at<?", (start, end))257 gone_prev = _count("SELECT COUNT(*) n FROM vehicles WHERE kind='auto'"258 " AND active=0 AND updated_at>=? AND updated_at<?", (pstart, pend))259 drops_cur = _price_drops(con, start, end)260 drops_prev = _price_drops(con, pstart, pend) if has_ref else None261262 # --- séries quotidiennes (avant les KPI : sparklines) -----------------------263 rows = _lifecycle_rows(con)264 daily = _daily_series(rows, start, end)265 prev_daily = _daily_series(rows, pstart, pend)266 full_prev = len(prev_daily) == len(daily) and len(daily) > 1267268 def _spark(key):269 pts = [{"t": p["t"], "v": p[key]} for p in daily if p[key] is not None]270 return pts if len(pts) >= 2 else None271272 def _kpi(id_, label_, value, unit="", prev=None, spark=None):273 pct, direction = _delta(value, prev)274 k = {"id": id_, "label": label_, "value": value, "unit": unit,275 "delta_pct": pct, "direction": direction}276 if spark:277 k["spark"] = spark278 return k279280 kpis = [281 _kpi("actifs", "Véhicules actifs", cur["n"], "", ref["n"], _spark("inv")),282 _kpi("nouveaux", "Nouveaux véhicules (période)", new_cur, "",283 new_prev if has_ref else None, _spark("new")),284 _kpi("retires", "Vendus / retirés (période)", gone_cur, "",285 gone_prev if has_ref else None, _spark("gone")),286 _kpi("prix", "Prix moyen (inventaire actif)",287 round(cur["avg_price"]) if cur["avg_price"] else None, "$",288 round(ref["avg_price"]) if ref["avg_price"] else None,289 _spark("avg_price")),290 _kpi("km", "Km moyen (inventaire actif)",291 round(cur["avg_km"]) if cur["avg_km"] else None, "km",292 round(ref["avg_km"]) if ref["avg_km"] else None, _spark("avg_km")),293 # année moyenne : valeur pré-formatée (« 2021,1 » — pas de séparateur294 # de milliers) ; un delta en % n'aurait aucun sens sur un millésime.295 _kpi("annee", "Année-modèle moyenne",296 f"{cur['avg_year']:.1f}".replace(".", ",")297 if cur["avg_year"] else None, ""),298 _kpi("dealers", "Concessionnaires actifs", cur["dealers"], "", ref["dealers"]),299 _kpi("particuliers", "Annonces de particuliers", cur["private"], "",300 ref["private"]),301 _kpi("baisses", "Baisses de prix (période)", drops_cur, "", drops_prev),302 _kpi("sources", "Sources actives", cur["sources"], "", ref["sources"]),303 ]304 kpis = [k for k in kpis if k["value"] is not None]305306 # --- jauges : complétude des fiches de l'inventaire actif -------------------307 cov = con.execute(308 """SELECT COUNT(*) n,309 SUM(CASE WHEN images IS NOT NULL AND images<>'[]'310 AND images<>'' THEN 1 ELSE 0 END) img,311 SUM(CASE WHEN mileage_km IS NOT NULL THEN 1 ELSE 0 END) km,312 SUM(CASE WHEN price IS NOT NULL THEN 1 ELSE 0 END) prix,313 SUM(CASE WHEN vin<>'' THEN 1 ELSE 0 END) vin,314 SUM(CASE WHEN carfax_url<>'' THEN 1 ELSE 0 END) carfax,315 SUM(CASE WHEN lat IS NOT NULL THEN 1 ELSE 0 END) geo316 FROM vehicles WHERE active=1 AND kind='auto'""").fetchone()317 gauges = []318 if cov["n"]:319 def _gauge(id_, label_, num, help_=None):320 g = {"id": id_, "label": label_,321 "value": round(100.0 * num / cov["n"], 1), "max": 100, "unit": "%"}322 if help_:323 g["help"] = help_324 return g325 gauges = [326 _gauge("photos", "Fiches avec photos", cov["img"]),327 _gauge("km", "Kilométrage renseigné", cov["km"]),328 _gauge("prix", "Prix affiché", cov["prix"]),329 _gauge("vin", "NIV (VIN) connu", cov["vin"]),330 _gauge("geo", "Fiches géolocalisées", cov["geo"]),331 _gauge("carfax", "Rapport Carfax lié", cov["carfax"],332 "Part des annonces actives dont la source publie un lien Carfax"),333 ]334335 # --- séries d'évolution ------------------------------------------------------336 series = []337 if daily:338 series.append({339 "id": "inv", "title": "Inventaire actif par jour", "unit": "véhicules",340 "kind": "line", "points": [{"t": p["t"], "v": p["inv"]} for p in daily],341 **({"compare": [{"t": p["t"], "v": p["inv"]} for p in prev_daily]}342 if full_prev else {}),343 })344 series.append({345 "id": "new", "title": "Nouveaux véhicules par jour", "unit": "véhicules",346 "kind": "bar", "points": [{"t": p["t"], "v": p["new"]} for p in daily],347 })348 series.append({349 "id": "gone", "title": "Véhicules vendus / retirés par jour",350 "unit": "véhicules", "kind": "bar",351 "points": [{"t": p["t"], "v": p["gone"]} for p in daily],352 })353 price_pts = [{"t": p["t"], "v": p["avg_price"]} for p in daily354 if p["avg_price"] is not None]355 if price_pts:356 series.append({357 "id": "avg_price", "title": "Prix moyen de l'inventaire par jour",358 "unit": "$", "kind": "area", "points": price_pts,359 })360361 # --- multi-courbes : prix moyen par grande marque ----------------------------362 multiseries = []363 top_makes = [r["make"] for r in con.execute(364 "SELECT make FROM vehicles WHERE active=1 AND kind='auto' AND make<>''"365 " GROUP BY make ORDER BY COUNT(*) DESC LIMIT 4")]366 if top_makes and daily:367 by_make_rows = defaultdict(list)368 for r in rows:369 if r[4] in top_makes:370 by_make_rows[r[4]].append(r)371 make_daily = {m: _daily_series(by_make_rows[m], start, end)372 for m in top_makes}373 # domaine commun : jours où chaque marque a un prix moyen mesuré374 commons = None375 for m in top_makes:376 days_m = {p["t"] for p in make_daily[m] if p["avg_price"] is not None}377 commons = days_m if commons is None else commons & days_m378 commons = commons or set()379 if len(commons) >= 2:380 ms_series = [{381 "label": m,382 "points": [{"t": p["t"], "v": p["avg_price"]}383 for p in make_daily[m] if p["t"] in commons],384 } for m in top_makes]385 multiseries.append({386 "id": "prix_marques",387 "title": "Prix moyen de l'inventaire — top 4 des marques",388 "unit": "$", "series": ms_series,389 })390391 # --- barres empilées : arrivées par source ------------------------------------392 stacked = []393 src_day = defaultdict(Counter) # jour -> source -> n394 src_tot = Counter()395 for r in con.execute(396 "SELECT first_seen, source FROM vehicles WHERE kind='auto'"397 " AND first_seen>=? AND first_seen<?", (start, end)):398 d_ = _to_date(r["first_seen"]).isoformat()399 src_day[d_][r["source"]] += 1400 src_tot[r["source"]] += 1401 if src_tot:402 SRC_LABELS = {"otogo": "Otogo", "kijiji": "Kijiji",403 "autotrader": "AutoTrader", "cargurus": "CarGurus",404 "automobileendirect": "AutomobileEnDirect",405 "hgregoire": "HGrégoire"}406 tops = [s for s, _ in src_tot.most_common(4)]407 keys = [SRC_LABELS.get(s, s) for s in tops]408 others = len(src_tot) > len(tops)409 if others:410 keys.append("Autres")411 pts = []412 for d_ in sorted(src_day):413 vals = [src_day[d_].get(s, 0) for s in tops]414 if others:415 vals.append(sum(src_day[d_].values()) - sum(vals))416 pts.append({"t": d_, "values": vals})417 if len(pts) >= 2:418 stacked.append({"id": "src", "title": "Nouveaux véhicules par source",419 "unit": "véhicules", "keys": keys, "points": pts})420421 # --- répartitions (inventaire actif, deltas vs début de période) --------------422 FUEL_EXPR = ("CASE WHEN fuel IN ('', 'N.D.') THEN 'Non précisé'"423 " ELSE fuel END")424 TRANS_EXPR = ("CASE WHEN transmission IN ('', 'NA', 'N.D.')"425 " THEN 'Non précisé' ELSE transmission END")426 BODY_EXPR = "CASE WHEN body_type='' THEN 'Non précisé' ELSE body_type END"427 SELLER_EXPR = (f"CASE WHEN {PRIV} THEN 'Particuliers'"428 " ELSE 'Concessionnaires' END")429430 def _items(expr, limit=None, where=""):431 cur_rows = con.execute(432 f"SELECT {expr} lab, COUNT(*) n FROM vehicles"433 f" WHERE active=1 AND kind='auto'{where}"434 f" GROUP BY lab ORDER BY n DESC" + (f" LIMIT {limit}" if limit else "")435 ).fetchall()436 prev_counts = _snapshot_counts(con, start, expr) if has_ref else {}437 out = []438 for r in cur_rows:439 pct, _ = _delta(r["n"], prev_counts.get(r["lab"]))440 out.append({"label": r["lab"] or "Non précisé", "value": r["n"],441 **({"delta_pct": pct} if pct is not None else {})})442 return out443444 by_fuel = _items(FUEL_EXPR)445 by_trans = _items(TRANS_EXPR, limit=8)446 by_body = _items(BODY_EXPR, limit=10)447 by_seller = _items(SELLER_EXPR)448 by_make = _items("make", limit=12, where=" AND make<>''")449450 year_rows = con.execute(451 "SELECT year, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"452 " AND year IS NOT NULL GROUP BY year ORDER BY year").fetchall()453 # 11 années récentes + un groupe « antérieures » = 12 barres max, aucune454 # année récente escamotée par la limite d'affichage des graphiques.455 by_year, older = [], 0456 cutoff = max((r["year"] for r in year_rows), default=0) - 10457 for r in year_rows:458 if r["year"] < cutoff:459 older += r["n"]460 else:461 by_year.append({"label": str(r["year"]), "value": r["n"]})462 if older:463 by_year.insert(0, {"label": f"≤ {cutoff - 1}", "value": older})464465 breakdowns = [466 {"id": "make", "title": "Top 12 des marques", "kind": "donut", "items": by_make},467 {"id": "fuel", "title": "Par carburant", "kind": "donut", "items": by_fuel},468 {"id": "seller", "title": "Concessionnaires vs particuliers",469 "kind": "donut", "items": by_seller},470 {"id": "trans", "title": "Par boîte de vitesses", "kind": "bar", "items": by_trans},471 {"id": "body", "title": "Par carrosserie", "kind": "bar", "items": by_body},472 {"id": "year", "title": "Par année-modèle", "kind": "bar", "items": by_year},473 ]474 breakdowns = [b for b in breakdowns if len(b["items"]) > 1]475476 # --- distributions : prix et kilométrage --------------------------------------477 def _histo(col, width, cap, fmt):478 rows_ = con.execute(479 f"SELECT CAST({col}/{width} AS INTEGER) b, COUNT(*) n FROM vehicles"480 f" WHERE active=1 AND kind='auto' AND {col} IS NOT NULL AND {col}>=0"481 " GROUP BY b ORDER BY b").fetchall()482 if not rows_:483 return None484 n_bins = cap // width485 bins = [{"label": fmt(i), "value": 0} for i in range(n_bins)]486 over = {"label": fmt(n_bins), "value": 0}487 for r in rows_:488 if r["b"] < n_bins:489 bins[r["b"]]["value"] += r["n"]490 else:491 over["value"] += r["n"]492 if over["value"]:493 bins.append(over)494 while bins and bins[0]["value"] == 0:495 bins.pop(0)496 return bins497498 def _fmt_price_bin(i):499 lo, hi = i * 5, i * 5 + 5500 return f"{lo}–{hi} k$" if hi <= 100 else "100 k$ +"501502 def _fmt_km_bin(i):503 lo, hi = i * 25, i * 25 + 25504 return f"{lo}–{hi} k km" if hi <= 300 else "300 k km +"505506 distributions = []507 price_bins = _histo("price", 5000, 100000, _fmt_price_bin)508 if price_bins:509 distributions.append({510 "id": "prix", "title": "Distribution des prix (tranches de 5 000 $)",511 "unit": "véhicules", "bins": price_bins})512 km_bins = _histo("mileage_km", 25000, 300000, _fmt_km_bin)513 if km_bins:514 distributions.append({515 "id": "km", "title": "Distribution du kilométrage (tranches de 25 000 km)",516 "unit": "véhicules", "bins": km_bins})517518 # --- répartition géographique ---------------------------------------------------519 geo = {"title": "Par région", "items": _items("region", where=" AND region<>''")}520521 # --- calendrier + activité horaire ----------------------------------------------522 heatmap = {"title": "Nouveaux véhicules par jour",523 "cells": [{"date": p["t"], "value": p["new"]} for p in daily]}524525 hour_counter = Counter()526 for r in con.execute(527 "SELECT first_seen FROM vehicles WHERE kind='auto'"528 " AND first_seen>=? AND first_seen<?", (start, end)):529 dt = datetime.fromtimestamp(r["first_seen"], TZ)530 hour_counter[(dt.weekday(), dt.hour)] += 1531 hourly = None532 if hour_counter:533 hourly = {"title": "Nouveaux véhicules détectés par heure (synchros)",534 "cells": [{"dow": k[0], "hour": k[1], "value": v}535 for k, v in sorted(hour_counter.items())]}536537 # --- tableaux détaillés (inventaire actif) ------------------------------------538 def _table_rows(sql, args=()):539 return [list(r) for r in con.execute(sql, args).fetchall()]540541 make_prev = _snapshot_counts(con, start, "make") if has_ref else {}542543 def _mk_delta(make_, n):544 pct, _ = _delta(n, make_prev.get(make_))545 return _fr_pct(pct)546547 tables = [548 {"id": "makes", "title": "Top marques — volume, prix et km moyens",549 "columns": ["Marque", "Véhicules", "Prix moyen", "Km moyen", "Δ période"],550 "rows": [[m, n, _fr_money(p), _fr_km(k), _mk_delta(m, n)]551 for m, n, p, k in _table_rows(552 "SELECT make, COUNT(*), AVG(price), AVG(mileage_km) FROM vehicles"553 " WHERE active=1 AND kind='auto' AND make<>''"554 " GROUP BY make ORDER BY COUNT(*) DESC LIMIT 25")]},555 {"id": "models", "title": "Top modèles — volume, prix et km moyens",556 "columns": ["Modèle", "Véhicules", "Prix moyen", "Km moyen"],557 "rows": [[m, n, _fr_money(p), _fr_km(k)] for m, n, p, k in _table_rows(558 "SELECT make || ' ' || model, COUNT(*), AVG(price), AVG(mileage_km)"559 " FROM vehicles WHERE active=1 AND kind='auto' AND make<>''"560 " AND model<>'' GROUP BY make, model ORDER BY COUNT(*) DESC LIMIT 25")]},561 {"id": "cities", "title": "Top villes — inventaire, prix et km moyens",562 "columns": ["Ville", "Région", "Véhicules", "Prix moyen", "Km moyen"],563 "rows": [[c, rg or "—", n, _fr_money(p), _fr_km(k)]564 for c, rg, n, p, k in _table_rows(565 "SELECT city, MAX(region), COUNT(*), AVG(price), AVG(mileage_km)"566 " FROM vehicles WHERE active=1 AND kind='auto' AND city<>''"567 " GROUP BY city ORDER BY COUNT(*) DESC LIMIT 25")]},568 {"id": "dealers", "title": "Top concessionnaires — inventaire et prix moyen",569 "columns": ["Concessionnaire", "Région", "Véhicules", "Prix moyen"],570 "rows": [[d, rg or "—", n, _fr_money(p)] for d, rg, n, p in _table_rows(571 "SELECT dealer_name, MAX(region), COUNT(*), AVG(price) FROM vehicles"572 " WHERE active=1 AND kind='auto' AND dealer_name<>''"573 f" AND NOT {PRIV}"574 " GROUP BY dealer_name ORDER BY COUNT(*) DESC LIMIT 25")]},575 ]576577 # sources & fraîcheur : inventaire actif + arrivées de la période + dernière578 # synchro réussie (sync_log = journal réel du pipeline d'ingestion)579 last_sync = {r["source"]: r for r in con.execute(580 """SELECT source, MAX(ts) ts, ok FROM sync_log GROUP BY source""")}581 src_rows = _table_rows(582 """SELECT source, COUNT(*),583 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END)584 FROM vehicles WHERE active=1 AND kind='auto'585 GROUP BY source ORDER BY COUNT(*) DESC LIMIT 25""", (start, end))586 if src_rows:587 rows_out = []588 for s, n, added in src_rows:589 ls = last_sync.get(s)590 when = (datetime.fromtimestamp(ls["ts"], TZ)591 .strftime("%Y-%m-%d %H:%M") if ls else "—")592 ok = ("OK" if ls and ls["ok"] else ("Erreur" if ls else "—"))593 rows_out.append([s, n, added, when, ok])594 tables.append({595 "id": "sources", "title": "Sources — inventaire, ajouts et fraîcheur",596 "columns": ["Source", "Véhicules actifs", "Ajouts (période)",597 "Dernière synchro", "Statut"],598 "rows": rows_out})599600 # --- records & faits marquants -------------------------------------------------601 records = []602 if daily:603 best = max(daily, key=lambda p: p["new"])604 if best["new"]:605 records.append({"label": "Jour record d'arrivées",606 "value": _fr_int(best["new"]) + " véhicules",607 "date": best["t"]})608 fastest = con.execute(609 """SELECT title, year, updated_at - first_seen dur, updated_at610 FROM vehicles WHERE kind='auto' AND active=0611 AND updated_at>=? AND updated_at<? AND updated_at>first_seen612 ORDER BY dur ASC LIMIT 1""", (start, end)).fetchone()613 if fastest:614 days_ = fastest["dur"] / 86400615 dur_txt = (f"{fastest['dur'] / 3600:.0f} h" if days_ < 1616 else f"{days_:.1f} jours".replace(".", ","))617 records.append({"label": f"Vente la plus rapide — {fastest['title']}",618 "value": dur_txt,619 "date": _to_date(fastest["updated_at"]).isoformat()})620 drop = con.execute(621 """WITH pl AS (622 SELECT uid, ts, price,623 LAG(price) OVER (PARTITION BY uid ORDER BY ts) prev_price624 FROM price_log WHERE price IS NOT NULL)625 SELECT v.title, pl.prev_price - pl.price baisse, pl.ts626 FROM pl JOIN vehicles v ON v.uid=pl.uid AND v.kind='auto'627 WHERE pl.ts>=? AND pl.ts<? AND pl.prev_price > pl.price628 ORDER BY baisse DESC LIMIT 1""", (start, end)).fetchone()629 if drop:630 records.append({"label": f"Plus forte baisse de prix — {drop['title']}",631 "value": "−" + _fr_money(drop["baisse"]),632 "date": _to_date(drop["ts"]).isoformat()})633 busiest = con.execute(634 f"""SELECT dealer_name, COUNT(*) n FROM vehicles635 WHERE kind='auto' AND dealer_name<>'' AND NOT {PRIV}636 AND first_seen>=? AND first_seen<?637 GROUP BY dealer_name ORDER BY n DESC LIMIT 1""",638 (start, end)).fetchone()639 if busiest and busiest["n"]:640 records.append({"label": f"Concessionnaire le plus actif — {busiest['dealer_name']}",641 "value": _fr_int(busiest["n"]) + " nouveautés",642 "date": None})643 top_price = con.execute(644 "SELECT title, price FROM vehicles WHERE active=1 AND kind='auto'"645 " AND price IS NOT NULL ORDER BY price DESC LIMIT 1").fetchone()646 if top_price:647 records.append({"label": f"Véhicule le plus cher en vente — {top_price['title']}",648 "value": _fr_money(top_price["price"]), "date": None})649 oldest = con.execute(650 "SELECT title, year FROM vehicles WHERE active=1 AND kind='auto'"651 " AND year IS NOT NULL ORDER BY year ASC LIMIT 1").fetchone()652 if oldest:653 records.append({"label": f"Doyen de l'inventaire — {oldest['title']}",654 "value": f"année {oldest['year']}", "date": None})655 top_km = con.execute(656 "SELECT title, mileage_km FROM vehicles WHERE active=1 AND kind='auto'"657 " AND mileage_km IS NOT NULL ORDER BY mileage_km DESC LIMIT 1").fetchone()658 if top_km:659 records.append({"label": f"Odomètre le plus élevé — {top_km['title']}",660 "value": _fr_km(top_km["mileage_km"]), "date": None})661 if has_ref and make_prev:662 make_cur = {r["lab"]: r["n"] for r in con.execute(663 "SELECT make lab, COUNT(*) n FROM vehicles WHERE active=1"664 " AND kind='auto' AND make<>'' GROUP BY lab HAVING n>=100")}665 gains = [(m, _delta(n, make_prev.get(m))[0]) for m, n in make_cur.items()]666 gains = [(m, p) for m, p in gains if p is not None]667 if gains:668 m, p = max(gains, key=lambda x: x[1])669 if p > 0:670 records.append({671 "label": f"Marque en plus forte hausse — {m}",672 "value": _fr_pct(p) + " d'inventaire", "date": None})673 top_region = con.execute(674 """SELECT region, COUNT(*) n FROM vehicles WHERE kind='auto'675 AND region<>'' AND first_seen>=? AND first_seen<?676 GROUP BY region ORDER BY n DESC LIMIT 1""", (start, end)).fetchone()677 if top_region and top_region["n"]:678 records.append({"label": f"Région la plus active — {top_region['region']}",679 "value": _fr_int(top_region["n"]) + " nouveautés",680 "date": None})681682 con.close()683 out = {684 "updated": datetime.now(TZ).isoformat(timespec="seconds"),685 "period": {"from": f_iso, "to": t_iso, "label": label},686 "kpis": kpis,687 "series": series,688 "breakdowns": breakdowns,689 "geo": geo,690 "heatmap": heatmap,691 "tables": tables,692 "records": records,693 }694 if gauges:695 out["gauges"] = gauges696 if multiseries:697 out["multiseries"] = multiseries698 if stacked:699 out["stacked"] = stacked700 if distributions:701 out["distributions"] = distributions702 if hourly:703 out["hourly"] = hourly704 return out705706707def dashboard(period: str = "30j", from_: str | None = None,708 to: str | None = None) -> dict:709 """Dashboard du contrat SPEC v2 — mis en cache 5 minutes par période."""710 if period not in PERIOD_LABELS and not (from_ and to):711 period = "30j"712 key = (period, from_ or "", to or "")713 now = time.time()714 with _cache_lock:715 hit = _cache.get(key)716 if hit and now - hit[0] < CACHE_TTL:717 return hit[1]718 data = _build(period, from_, to)719 with _cache_lock:720 _cache[key] = (now, data)721 return data722