Python 68.8%
TypeScript 18.6%
CSS 8.7%
JavaScript 3.3%
HTML 0.6%
1# -----------------------------------------------------------------------------2# Rent-Ka — Rental listings aggregator (Canada, outside Québec)3# Author: Simon-Pierre Boucher — contact@spboucher.ai4# statsdash.py : tableau de bord statistique — contrat commun Groupe KA v25# (voir frontend/src/ka/stats/SPEC.md). Construit le JSON du dashboard à6# partir de requêtes SQL agrégées (listings, sync_log, price_log) avec un7# cache mémoire de 5 minutes par clé de période. AUCUNE stat inventée :8# une section sans donnée réelle est simplement absente du JSON.9# v2 (2026-08-19) : sparklines KPI, jauges de couverture, série des loyers10# (area), multi-courbes loyer médian par taille, barres empilées par source,11# distribution des loyers, heatmap horaire 7×24, tableaux « loyers par12# taille » et « baisses de loyer », records enrichis.13# -----------------------------------------------------------------------------14from __future__ import annotations1516import json17import threading18import time19from datetime import date, datetime, timedelta20from pathlib import Path21from zoneinfo import ZoneInfo2223from . import db2425TZ = ZoneInfo("America/Toronto")26ROOT = Path(__file__).resolve().parent.parent27SOURCES_PATH = ROOT / "data" / "sources.json"2829CACHE_TTL = 300 # secondes30_cache: dict[str, tuple[float, dict]] = {}31_cache_lock = threading.Lock()3233# bornes de plausibilité des loyers résidentiels : au-delà, presque toujours34# des erreurs de lecture à la source — exclues des agrégats de prix.35PRIX_MIN, PRIX_MAX = 300, 150003637PERIODS = {38 "auj": ("Aujourd'hui", 0),39 "7j": ("7 jours", 6),40 "30j": ("30 jours", 29),41 "3m": ("3 mois", 89),42 "6m": ("6 mois", 179),43 "12m": ("12 mois", 364),44}4546DOW_FR = ["lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"]474849# ---------------------------------------------------------------- utilitaires50def _day_start_ts(d: date) -> float:51 return datetime(d.year, d.month, d.day, tzinfo=TZ).timestamp()525354def _coverage(con) -> tuple[date | None, date | None]:55 """Première et dernière date observées dans la base (first/last_seen)."""56 row = con.execute(57 "SELECT MIN(first_seen) a, MAX(last_seen) b FROM listings"58 " WHERE dup_of IS NULL"59 ).fetchone()60 if row["a"] is None:61 return None, None62 return (datetime.fromtimestamp(row["a"], TZ).date(),63 datetime.fromtimestamp(row["b"], TZ).date())646566def resolve_period(period: str, from_: str | None, to_: str | None,67 cov_min: date, today: date) -> tuple[date, date, str]:68 """Bornes [from, to] (dates locales incluses) + libellé humain."""69 if from_ and to_:70 try:71 a = date.fromisoformat(from_)72 b = date.fromisoformat(to_)73 if a > b:74 a, b = b, a75 return max(a, cov_min), min(b, today), f"{a} → {b}"76 except ValueError:77 pass78 if period == "tout":79 return cov_min, today, "Toute la période"80 if period == "annee":81 return max(date(today.year, 1, 1), cov_min), today, "Année en cours"82 label, back = PERIODS.get(period, PERIODS["30j"])83 return max(today - timedelta(days=back), cov_min), today, label848586def _pct(cur: float, prev: float) -> float | None:87 if not prev:88 return None89 return round(100.0 * (cur - prev) / prev, 1)909192def _days(a: date, b: date) -> list[date]:93 return [a + timedelta(days=i) for i in range((b - a).days + 1)]949596def _fr_int(n: float) -> str:97 return f"{int(round(n)):,}".replace(",", " ")9899100def _fr_money(n: float) -> str:101 return _fr_int(n) + " $"102103104def _spark(pts: list[dict], cap: int = 30) -> list[dict] | None:105 """Sous-échantillonne une série pour la sparkline d'un KPI (≤ cap points)."""106 if len(pts) < 2:107 return None108 if len(pts) <= cap:109 return pts110 step = max(1, len(pts) // cap)111 out = pts[::step]112 if out[-1] is not pts[-1]:113 out.append(pts[-1])114 return out115116117def _source_names() -> dict[str, str]:118 try:119 reg = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]120 return {s["id"]: s.get("name") or s["id"] for s in reg}121 except (OSError, ValueError, KeyError):122 return {}123124125def _pctile(sorted_vals: list[float], frac: float) -> float | None:126 if not sorted_vals:127 return None128 i = min(len(sorted_vals) - 1, int(len(sorted_vals) * frac))129 return sorted_vals[i]130131132# ------------------------------------------------------------------- dashboard133def compute(period: str = "30j", from_: str | None = None,134 to_: str | None = None) -> dict:135 key = f"{period}|{from_ or ''}|{to_ or ''}"136 now = time.time()137 with _cache_lock:138 hit = _cache.get(key)139 if hit and hit[0] > now:140 return hit[1]141 data = _compute(period, from_, to_)142 with _cache_lock:143 _cache[key] = (now + CACHE_TTL, data)144 return data145146147def _compute(period: str, from_: str | None, to_: str | None) -> dict:148 con = db.connect()149 try:150 return _build(con, period, from_, to_)151 finally:152 con.close()153154155# requête réutilisée : premier loyer observé de chaque annonce (journal de156# prix) — c'est le loyer demandé à l'entrée sur le marché, borné plausible.157_FIRST_PRICE_CTE = """158 WITH fp AS (159 SELECT p.uid uid, p.ts ts, p.price price160 FROM price_log p161 JOIN (SELECT uid, MIN(ts) t0 FROM price_log162 WHERE price IS NOT NULL GROUP BY uid) f163 ON f.uid = p.uid AND p.ts = f.t0164 WHERE p.price BETWEEN ? AND ?165 )166"""167168169def _build(con, period: str, from_: str | None, to_: str | None) -> dict:170 today = datetime.now(TZ).date()171 cov_min, _cov_max = _coverage(con)172 if cov_min is None: # base vide173 return {"updated": datetime.now(TZ).isoformat(),174 "period": {"from": None, "to": None, "label": "—"},175 "kpis": [], "series": [], "breakdowns": [], "tables": [],176 "records": []}177178 d_from, d_to, label = resolve_period(period, from_, to_, cov_min, today)179 ts_from = _day_start_ts(d_from)180 ts_to = _day_start_ts(d_to + timedelta(days=1)) # borne exclusive181182 # ---- histogrammes journaliers (2 balayages agrégés, réutilisés partout)183 starts = {r["d"]: r["n"] for r in con.execute(184 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"185 " FROM listings WHERE dup_of IS NULL GROUP BY d")}186 ends = {r["d"]: r["n"] for r in con.execute(187 "SELECT date(last_seen,'unixepoch','localtime') d, COUNT(*) n"188 " FROM listings WHERE dup_of IS NULL AND active=0 GROUP BY d")}189190 def actives_at(d: date) -> int:191 """Reconstruction : cum(first_seen<=d) − cum(retraits<=d−1)."""192 iso = d.isoformat()193 prev = (d - timedelta(days=1)).isoformat()194 s = sum(n for dd, n in starts.items() if dd <= iso)195 e = sum(n for dd, n in ends.items() if dd <= prev)196 return s - e197198 # ---- KPI ------------------------------------------------------------199 actives_now = con.execute(200 "SELECT COUNT(*) n FROM listings WHERE active=1 AND dup_of IS NULL"201 ).fetchone()["n"]202 new_in = sum(n for d, n in starts.items()203 if d_from.isoformat() <= d <= d_to.isoformat())204 removed_in = sum(n for d, n in ends.items()205 if d_from.isoformat() <= d <= d_to.isoformat())206207 # période précédente de même longueur (uniquement si couverte par la base)208 span = (d_to - d_from).days + 1209 p_from, p_to = d_from - timedelta(days=span), d_from - timedelta(days=1)210 prev_ok = p_from >= cov_min211 pts_prev_from = _day_start_ts(p_from)212 pts_prev_to = _day_start_ts(p_to + timedelta(days=1))213 new_prev = removed_prev = None214 if prev_ok:215 new_prev = sum(n for d, n in starts.items()216 if p_from.isoformat() <= d <= p_to.isoformat())217 removed_prev = sum(n for d, n in ends.items()218 if p_from.isoformat() <= d <= p_to.isoformat())219220 actives_prev = actives_at(p_to) if d_from - timedelta(days=1) >= cov_min else None221222 row = con.execute(223 "SELECT AVG(price) avg_p, COUNT(price) n_p FROM listings"224 " WHERE active=1 AND dup_of IS NULL AND price BETWEEN ? AND ?",225 (PRIX_MIN, PRIX_MAX)).fetchone()226 avg_price, n_priced = row["avg_p"], row["n_p"]227 median_price = None228 if n_priced:229 median_price = con.execute(230 "SELECT price FROM listings WHERE active=1 AND dup_of IS NULL"231 " AND price BETWEEN ? AND ? ORDER BY price LIMIT 1 OFFSET ?",232 (PRIX_MIN, PRIX_MAX, n_priced // 2)).fetchone()["price"]233234 connectors = con.execute(235 "SELECT COUNT(DISTINCT source) n FROM sync_log"236 " WHERE ok=1 AND ts>=? AND ts<?", (ts_from, ts_to)).fetchone()["n"]237 cities_n = con.execute(238 "SELECT COUNT(DISTINCT city) n FROM listings"239 " WHERE active=1 AND dup_of IS NULL AND city<>''").fetchone()["n"]240241 # séries journalières (réutilisées : séries, sparklines)242 days = _days(d_from, d_to)243 pts_act = [{"t": d.isoformat(), "v": actives_at(d)} for d in days]244 pts_new = [{"t": d.isoformat(), "v": starts.get(d.isoformat(), 0)}245 for d in days]246 pts_rem = [{"t": d.isoformat(), "v": ends.get(d.isoformat(), 0)}247 for d in days]248249 # loyer moyen des nouvelles annonces par jour (premier prix observé)250 fp_daily = con.execute(251 _FIRST_PRICE_CTE +252 "SELECT date(ts,'unixepoch','localtime') d, AVG(price) a, COUNT(*) n"253 " FROM fp WHERE ts>=? AND ts<? GROUP BY d ORDER BY d",254 (PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchall()255 pts_rent = [{"t": r["d"], "v": round(r["a"])} for r in fp_daily256 if r["n"] >= 5]257258 kpis = [259 {"id": "actives", "label": "Annonces actives", "value": actives_now,260 "delta_pct": _pct(actives_now, actives_prev) if actives_prev else None,261 "direction": "up" if (actives_prev and actives_now >= actives_prev) else262 ("down" if actives_prev else None),263 "spark": _spark(pts_act)},264 {"id": "nouvelles", "label": "Nouvelles annonces (période)",265 "value": new_in,266 "delta_pct": _pct(new_in, new_prev) if prev_ok else None,267 "direction": ("up" if new_in >= (new_prev or 0) else "down") if prev_ok else None,268 "spark": _spark(pts_new)},269 {"id": "retirees", "label": "Annonces retirées (période)",270 "value": removed_in,271 "delta_pct": _pct(removed_in, removed_prev) if prev_ok else None,272 "direction": ("down" if removed_in >= (removed_prev or 0) else "up") if prev_ok else None,273 "spark": _spark(pts_rem)},274 ]275 if avg_price:276 kpis.append({"id": "loyer_moyen", "label": "Loyer moyen (actives)",277 "value": round(avg_price), "unit": "$",278 "spark": _spark(pts_rent)})279 if median_price:280 kpis.append({"id": "loyer_median", "label": "Loyer médian (actives)",281 "value": round(median_price), "unit": "$"})282 # qualité des données (quality.py) : quarantaine283 qual = con.execute(284 "SELECT ROUND(AVG(completeness),1) c, SUM(published=0) q"285 " FROM listings WHERE active=1 AND dup_of IS NULL").fetchone()286 if qual["c"] is not None:287 kpis.append({"id": "quarantaine",288 "label": "Annonces en quarantaine (qualité)",289 "value": qual["q"] or 0})290 # juste valeur (fair value) : répartition des classifications291 fv_ok = True292 try:293 fv = con.execute(294 """SELECT SUM(f.verdict='sous') sous, SUM(f.verdict='marche') marche,295 SUM(f.verdict='sur') sur, COUNT(*) n,296 ROUND(AVG(f.deviation)*100, 1) dev297 FROM fairvalue f JOIN listings l USING (uid)298 WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL299 AND f.verdict IS NOT NULL""").fetchone()300 except Exception:301 fv_ok = False302 fv = None303 if fv_ok and fv and fv["n"]:304 kpis.append({"id": "sous_marche", "label": "Annonces sous le marché",305 "value": fv["sous"] or 0})306 kpis.append({"id": "connecteurs", "label": "Connecteurs actifs (période)",307 "value": connectors})308 kpis.append({"id": "villes", "label": "Villes couvertes", "value": cities_n})309 kpis = [{k: v for k, v in kpi.items() if v is not None} for kpi in kpis]310311 # ---- jauges de couverture / complétude ---------------------------------312 cov = con.execute(313 """SELECT COUNT(*) n,314 SUM(lat IS NOT NULL AND lng IS NOT NULL) geo,315 SUM(price BETWEEN ? AND ?) prix,316 SUM(images IS NOT NULL AND images NOT IN ('', '[]')) img,317 SUM(published=1) pub318 FROM listings WHERE active=1 AND dup_of IS NULL""",319 (PRIX_MIN, PRIX_MAX)).fetchone()320 gauges = []321 if cov["n"]:322 n_act = cov["n"]323324 def _gauge(gid, lab, num, help_=None):325 g = {"id": gid, "label": lab,326 "value": round(100.0 * (num or 0) / n_act, 1),327 "max": 100, "unit": "%"}328 if help_:329 g["help"] = help_330 return g331332 gauges = [333 _gauge("geo", "Annonces géolocalisées", cov["geo"]),334 _gauge("prix", "Annonces avec loyer affiché", cov["prix"]),335 _gauge("photos", "Annonces avec photos", cov["img"]),336 _gauge("publiees", "Annonces publiées (qualité OK)", cov["pub"],337 "Le reste est en quarantaine qualité"),338 ]339 if qual["c"] is not None:340 gauges.append({"id": "completude",341 "label": "Complétude moyenne des fiches",342 "value": qual["c"], "max": 100, "unit": "%"})343344 # ---- séries temporelles ----------------------------------------------345 series = []346 if len(days) >= 2:347 s_act = {"id": "actives_jour", "title": "Annonces actives par jour",348 "unit": "annonces", "kind": "line", "points": pts_act}349 s_new = {"id": "nouvelles_jour", "title": "Nouvelles annonces par jour",350 "unit": "annonces", "kind": "bar", "points": pts_new}351 s_rem = {"id": "retraits_jour", "title": "Annonces retirées par jour",352 "unit": "annonces", "kind": "line", "points": pts_rem}353 if prev_ok:354 pdays = _days(p_from, p_to)355 s_act["compare"] = [{"t": d.isoformat(), "v": actives_at(d)}356 for d in pdays]357 s_rem["compare"] = [{"t": d.isoformat(),358 "v": ends.get(d.isoformat(), 0)}359 for d in pdays]360 series = [s_act, s_new, s_rem]361 if len(pts_rent) >= 2:362 series.append({"id": "loyer_entree_jour",363 "title": "Loyer moyen des nouvelles annonces par jour",364 "unit": "$", "kind": "area", "points": pts_rent})365366 # ---- multi-courbes : loyer médian d'entrée par taille -------------------367 multiseries = []368 if len(days) >= 2:369 top_types = [r["t"] for r in con.execute(370 _FIRST_PRICE_CTE +371 """SELECT l.unit_type t, COUNT(*) n FROM fp372 JOIN listings l ON l.uid=fp.uid AND l.dup_of IS NULL373 WHERE fp.ts>=? AND fp.ts<? AND l.unit_type<>''374 GROUP BY t ORDER BY n DESC LIMIT 4""",375 (PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchall()]376 if top_types:377 ph = ",".join("?" * len(top_types))378 rows = con.execute(379 _FIRST_PRICE_CTE +380 f"""SELECT date(fp.ts,'unixepoch','localtime') d,381 l.unit_type t, fp.price p FROM fp382 JOIN listings l ON l.uid=fp.uid AND l.dup_of IS NULL383 WHERE fp.ts>=? AND fp.ts<? AND l.unit_type IN ({ph})""",384 (PRIX_MIN, PRIX_MAX, ts_from, ts_to, *top_types)).fetchall()385 per: dict[str, dict[str, list[float]]] = {t: {} for t in top_types}386 for r in rows:387 per[r["t"]].setdefault(r["d"], []).append(r["p"])388 # axe commun : jours où chaque taille a au moins 3 observations389 common = [d.isoformat() for d in days390 if all(len(per[t].get(d.isoformat(), [])) >= 3391 for t in top_types)]392 if len(common) >= 2:393 ms_series = []394 for t in top_types:395 pts = []396 for d in common:397 vals = sorted(per[t][d])398 pts.append({"t": d, "v": round(vals[len(vals) // 2])})399 ms_series.append({"label": t, "points": pts})400 multiseries.append({401 "id": "loyer_taille",402 "title": "Loyer médian des nouvelles annonces par taille",403 "unit": "$", "series": ms_series})404405 # ---- barres empilées : ajouts par jour et par source --------------------406 names = _source_names()407 stacked = []408 if len(days) >= 2:409 src_tot = con.execute(410 """SELECT source s, COUNT(*) n FROM listings411 WHERE dup_of IS NULL AND first_seen>=? AND first_seen<?412 GROUP BY s ORDER BY n DESC""", (ts_from, ts_to)).fetchall()413 if src_tot:414 top_src = [r["s"] for r in src_tot[:3]]415 others = [r["s"] for r in src_tot[3:]]416 daily_src: dict[str, dict[str, int]] = {}417 for r in con.execute(418 """SELECT date(first_seen,'unixepoch','localtime') d,419 source s, COUNT(*) n FROM listings420 WHERE dup_of IS NULL AND first_seen>=? AND first_seen<?421 GROUP BY d, s""", (ts_from, ts_to)):422 daily_src.setdefault(r["d"], {})[r["s"]] = r["n"]423 keys = [names.get(s, s) for s in top_src] + (424 ["Autres"] if others else [])425 pts = []426 for d in days:427 by = daily_src.get(d.isoformat(), {})428 vals = [by.get(s, 0) for s in top_src]429 if others:430 vals.append(sum(by.get(s, 0) for s in others))431 pts.append({"t": d.isoformat(), "values": vals})432 if any(sum(p["values"]) for p in pts):433 stacked.append({"id": "ajouts_source",434 "title": "Nouvelles annonces par jour et par source",435 "unit": "ajouts", "keys": keys, "points": pts})436437 # ---- répartitions ------------------------------------------------------438 types = [{"label": r["t"] or "Non précisé", "value": r["n"]}439 for r in con.execute(440 "SELECT unit_type t, COUNT(*) n FROM listings"441 " WHERE active=1 AND dup_of IS NULL GROUP BY unit_type"442 " ORDER BY n DESC LIMIT 8")]443 by_source = [{"label": names.get(r["s"], r["s"]), "value": r["n"]}444 for r in con.execute(445 "SELECT source s, COUNT(*) n FROM listings"446 " WHERE active=1 AND dup_of IS NULL GROUP BY source"447 " ORDER BY n DESC LIMIT 12")]448 breakdowns = []449 if fv_ok and fv and fv["n"]:450 breakdowns.append({"id": "fairvalue",451 "title": "Position des loyers vs juste valeur estimée",452 "kind": "donut", "items": [453 {"label": "Sous le marché", "value": fv["sous"] or 0},454 {"label": "Dans le marché", "value": fv["marche"] or 0},455 {"label": "Au-dessus du marché", "value": fv["sur"] or 0}]})456 if types:457 breakdowns.append({"id": "types", "title": "Annonces actives par taille",458 "kind": "donut", "items": types})459 # nouvelles annonces par taille (période) — deltas vs période précédente460 new_types = con.execute(461 """SELECT COALESCE(NULLIF(unit_type,''),'Non précisé') t, COUNT(*) n462 FROM listings WHERE dup_of IS NULL AND first_seen>=? AND first_seen<?463 GROUP BY t ORDER BY n DESC LIMIT 8""", (ts_from, ts_to)).fetchall()464 if new_types:465 prev_types = {}466 if prev_ok:467 prev_types = {r["t"]: r["n"] for r in con.execute(468 """SELECT COALESCE(NULLIF(unit_type,''),'Non précisé') t,469 COUNT(*) n FROM listings470 WHERE dup_of IS NULL AND first_seen>=? AND first_seen<?471 GROUP BY t""", (pts_prev_from, pts_prev_to))}472 items = []473 for r in new_types:474 it = {"label": r["t"], "value": r["n"]}475 if prev_ok:476 it["delta_pct"] = _pct(r["n"], prev_types.get(r["t"], 0))477 items.append(it)478 breakdowns.append({"id": "nouvelles_taille",479 "title": "Nouvelles annonces par taille (période)",480 "kind": "bars", "items": items})481 if by_source:482 breakdowns.append({"id": "sources", "title": "Top sources (annonces actives)",483 "kind": "bars", "items": by_source})484485 # ---- distribution des loyers (annonces actives, tranches de 200 $) ------486 distributions = []487 bins_rows = con.execute(488 """SELECT CAST(price/200 AS INT) b, COUNT(*) n FROM listings489 WHERE active=1 AND dup_of IS NULL AND price BETWEEN ? AND ?490 GROUP BY b ORDER BY b""", (PRIX_MIN, PRIX_MAX)).fetchall()491 if bins_rows:492 LO, HI = 2, 16 # < 400 $ … 3 200 $ et +493 agg: dict[int, int] = {}494 for r in bins_rows:495 b = min(max(r["b"], LO - 1), HI)496 agg[b] = agg.get(b, 0) + r["n"]497 bins = []498 for b in sorted(agg):499 if b == LO - 1:500 lab = f"< {LO * 200} $"501 elif b == HI:502 lab = f"{_fr_int(HI * 200)} $ +"503 else:504 lab = f"{_fr_int(b * 200)}–{_fr_int((b + 1) * 200 - 1)} $"505 bins.append({"label": lab, "value": agg[b]})506 distributions.append({"id": "loyers",507 "title": "Distribution des loyers (annonces actives)",508 "unit": "annonces", "bins": bins})509510 # ---- géographie --------------------------------------------------------511 geo_items = [{"label": r["c"], "value": r["n"]} for r in con.execute(512 "SELECT city c, COUNT(*) n FROM listings"513 " WHERE active=1 AND dup_of IS NULL AND city<>''"514 " GROUP BY city ORDER BY n DESC LIMIT 14")]515 geo = ({"title": "Top villes (annonces actives)", "items": geo_items}516 if geo_items else None)517518 # ---- calendrier de chaleur : 26 dernières semaines d'ajouts --------------519 heat_from = max(cov_min, today - timedelta(days=181))520 heat_cells = [{"date": d, "value": n} for d, n in sorted(starts.items())521 if d >= heat_from.isoformat()]522 heatmap = ({"title": "Nouvelles annonces par jour (26 dernières semaines)",523 "cells": heat_cells} if heat_cells else None)524525 # ---- heatmap horaire 7×24 : heure d'observation des ajouts ---------------526 hourly = None527 hour_rows = con.execute(528 """SELECT CAST(strftime('%w', first_seen,'unixepoch','localtime') AS INT) w,529 CAST(strftime('%H', first_seen,'unixepoch','localtime') AS INT) h,530 COUNT(*) n531 FROM listings WHERE dup_of IS NULL AND first_seen>=? AND first_seen<?532 GROUP BY w, h""", (ts_from, ts_to)).fetchall()533 if hour_rows:534 cells = [{"dow": (r["w"] + 6) % 7, "hour": r["h"], "value": r["n"]}535 for r in hour_rows] # %w : 0=dim → contrat : 0=lun536 hourly = {"title": "Ajouts d'annonces par heure (période)",537 "cells": cells}538539 # ---- tableaux ----------------------------------------------------------540 tables = []541 top_villes = con.execute(542 """SELECT city, COUNT(*) n, AVG(CASE WHEN price BETWEEN ? AND ? THEN price END) avg_p,543 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END) new_n544 FROM listings WHERE active=1 AND dup_of IS NULL AND city<>''545 GROUP BY city ORDER BY n DESC LIMIT 50""",546 (PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchall()547 removed_by_city = {r["city"]: r["n"] for r in con.execute(548 """SELECT city, COUNT(*) n FROM listings549 WHERE active=0 AND dup_of IS NULL AND city<>''550 AND last_seen>=? AND last_seen<? GROUP BY city""",551 (ts_from, ts_to)).fetchall()}552553 def _city_median(city: str) -> float | None:554 r = con.execute(555 "SELECT COUNT(*) n FROM listings WHERE active=1 AND dup_of IS NULL"556 " AND city=? AND price BETWEEN ? AND ?",557 (city, PRIX_MIN, PRIX_MAX)).fetchone()558 if not r["n"]:559 return None560 return con.execute(561 "SELECT price FROM listings WHERE active=1 AND dup_of IS NULL"562 " AND city=? AND price BETWEEN ? AND ? ORDER BY price"563 " LIMIT 1 OFFSET ?",564 (city, PRIX_MIN, PRIX_MAX, r["n"] // 2)).fetchone()["price"]565566 city_medians: dict[str, float] = {}567 if top_villes:568 rows = []569 for r in top_villes:570 med = _city_median(r["city"])571 if med is not None:572 city_medians[r["city"]] = med573 net = r["new_n"] - removed_by_city.get(r["city"], 0)574 rows.append([575 r["city"], r["n"],576 _fr_money(r["avg_p"]) if r["avg_p"] else "—",577 _fr_money(med) if med is not None else "—",578 r["new_n"], f"{'+' if net >= 0 else ''}{net}"])579 tables.append({"id": "top_villes", "title": "Top villes",580 "columns": ["Ville", "Annonces actives", "Loyer moyen",581 "Loyer médian", "Nouvelles (période)",582 "Δ net (période)"],583 "rows": rows})584585 # loyers par taille de logement (moyenne, médiane, P10–P90)586 type_rows = con.execute(587 """SELECT COALESCE(NULLIF(unit_type,''),'Non précisé') t, COUNT(*) n,588 AVG(price) avg_p FROM listings589 WHERE active=1 AND dup_of IS NULL AND price BETWEEN ? AND ?590 GROUP BY t ORDER BY n DESC LIMIT 12""",591 (PRIX_MIN, PRIX_MAX)).fetchall()592 if type_rows:593 rows = []594 for r in type_rows:595 prices = sorted(x["price"] for x in con.execute(596 """SELECT price FROM listings WHERE active=1 AND dup_of IS NULL597 AND COALESCE(NULLIF(unit_type,''),'Non précisé')=?598 AND price BETWEEN ? AND ?""",599 (r["t"], PRIX_MIN, PRIX_MAX)))600 med = _pctile(prices, 0.5)601 p10 = _pctile(prices, 0.10)602 p90 = _pctile(prices, 0.90)603 rows.append([604 r["t"], r["n"], _fr_money(r["avg_p"]),605 _fr_money(med) if med is not None else "—",606 f"{_fr_money(p10)} – {_fr_money(p90)}"607 if p10 is not None and p90 is not None else "—"])608 tables.append({"id": "loyers_taille",609 "title": "Loyers par taille de logement (actives)",610 "columns": ["Taille", "Annonces avec loyer",611 "Loyer moyen", "Loyer médian",612 "Fourchette P10–P90"],613 "rows": rows})614615 top_srcs = con.execute(616 """SELECT source s, COUNT(*) n, AVG(CASE WHEN price BETWEEN ? AND ? THEN price END) avg_p,617 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END) new_n,618 ROUND(AVG(completeness),0) comp, SUM(published=0) quar619 FROM listings WHERE active=1 AND dup_of IS NULL620 GROUP BY source ORDER BY n DESC LIMIT 50""",621 (PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchall()622 last_sync = {r["source"]: r["ts"] for r in con.execute(623 "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}624 if top_srcs:625 rows = []626 for r in top_srcs:627 ls = last_sync.get(r["s"])628 rows.append([629 names.get(r["s"], r["s"]), r["n"],630 _fr_money(r["avg_p"]) if r["avg_p"] else "—",631 r["new_n"],632 f"{r['comp']:.0f} %" if r["comp"] is not None else "—",633 r["quar"] or 0,634 datetime.fromtimestamp(ls, TZ).strftime("%Y-%m-%d %H:%M") if ls else "—"])635 tables.append({"id": "top_gestionnaires",636 "title": "Top gestionnaires & sources",637 "columns": ["Gestionnaire / source", "Annonces actives",638 "Loyer moyen", "Nouvelles (période)",639 "Complétude", "Quarantaine",640 "Dernière synchro"],641 "rows": rows})642643 # écart moyen au marché par ville (villes à volume suffisant)644 if fv_ok:645 fv_villes = con.execute(646 """SELECT l.city, COUNT(*) n, ROUND(AVG(f.deviation)*100,1) dev,647 SUM(f.verdict='sous') sous648 FROM fairvalue f JOIN listings l USING (uid)649 WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL650 AND f.verdict IS NOT NULL AND l.city<>''651 GROUP BY l.city HAVING n>=50 ORDER BY n DESC LIMIT 25""").fetchall()652 if fv_villes:653 tables.append({654 "id": "fv_villes", "title": "Écart au marché par ville (fair value)",655 "columns": ["Ville", "Annonces évaluées", "Écart moyen",656 "Sous le marché"],657 "rows": [[r["city"], r["n"],658 f"{'+' if r['dev'] >= 0 else ''}{r['dev']} %",659 r["sous"] or 0] for r in fv_villes]})660661 # baisses de loyer observées dans la période (journal de prix, plausibles)662 drops = con.execute(663 """SELECT a.uid uid, MAX(a.price-b.price) dp, a.price p0, b.price p1,664 b.ts ts1665 FROM price_log a JOIN price_log b666 ON a.uid=b.uid AND b.ts>a.ts667 AND a.price BETWEEN ? AND ? AND b.price BETWEEN ? AND ?668 AND b.price<a.price AND b.price>=a.price*0.5669 WHERE a.ts>=? AND b.ts<?670 GROUP BY a.uid ORDER BY dp DESC LIMIT 15""",671 (PRIX_MIN, PRIX_MAX, PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchall()672 if drops:673 rows = []674 for r in drops:675 li = con.execute(676 "SELECT city, unit_type FROM listings WHERE uid=?",677 (r["uid"],)).fetchone()678 what = " · ".join(x for x in [li["unit_type"], li["city"]]679 if x) if li else r["uid"]680 rows.append([681 what or r["uid"], _fr_money(r["p0"]), _fr_money(r["p1"]),682 f"−{_fr_money(r['dp'])}",683 f"{round(100.0 * r['dp'] / r['p0'], 1)} %",684 datetime.fromtimestamp(r["ts1"], TZ).date().isoformat()])685 tables.append({"id": "baisses_loyer",686 "title": "Plus fortes baisses de loyer (période)",687 "columns": ["Logement", "Ancien loyer", "Nouveau loyer",688 "Baisse", "Baisse %", "Observée le"],689 "rows": rows})690691 # ---- records & faits marquants -----------------------------------------692 records = []693 in_period = {d: n for d, n in starts.items()694 if d_from.isoformat() <= d <= d_to.isoformat()}695 if in_period:696 best = max(in_period, key=in_period.get)697 records.append({"label": "Jour record d'ajouts",698 "value": _fr_int(in_period[best]) + " annonces",699 "date": best})700 rem_period = {d: n for d, n in ends.items()701 if d_from.isoformat() <= d <= d_to.isoformat()}702 if rem_period:703 worst = max(rem_period, key=rem_period.get)704 records.append({"label": "Jour record de retraits",705 "value": _fr_int(rem_period[worst]) + " annonces",706 "date": worst})707 if top_villes:708 ville = max(top_villes, key=lambda r: r["new_n"])709 if ville["new_n"]:710 records.append({"label": "Ville la plus dynamique (nouvelles annonces)",711 "value": f"{ville['city']} — {ville['new_n']}"})712 # villes la plus chère / la plus abordable (loyer médian, volume suffisant)713 big_meds = {c: m for c, m in city_medians.items()714 if next((r["n"] for r in top_villes if r["city"] == c), 0) >= 100}715 if big_meds:716 chere = max(big_meds, key=big_meds.get)717 abordable = min(big_meds, key=big_meds.get)718 records.append({"label": "Ville la plus chère (loyer médian, ≥ 100 annonces)",719 "value": f"{chere} — {_fr_money(big_meds[chere])}"})720 if abordable != chere:721 records.append({"label": "Ville la plus abordable (loyer médian, ≥ 100 annonces)",722 "value": f"{abordable} — {_fr_money(big_meds[abordable])}"})723 if types and types[0]["label"] != "Non précisé":724 records.append({"label": "Taille la plus offerte",725 "value": f"{types[0]['label']} — "726 f"{_fr_int(types[0]['value'])} annonces"})727 # plus forte baisse de loyer observée dans la période (journal de prix)728 if drops:729 d0 = drops[0]730 li = con.execute("SELECT city, unit_type FROM listings WHERE uid=?",731 (d0["uid"],)).fetchone()732 where = " · ".join(x for x in [li["unit_type"], li["city"]] if x) if li else ""733 records.append({734 "label": "Plus forte baisse de loyer observée"735 + (f" ({where})" if where else ""),736 "value": f"−{_fr_money(d0['dp'])}"737 f" ({_fr_money(d0['p0'])} → {_fr_money(d0['p1'])})",738 "date": datetime.fromtimestamp(d0["ts1"], TZ).date().isoformat()})739 # plus forte hausse de loyer observée (plausible : ≤ ×2)740 hike = con.execute(741 """SELECT a.uid uid, MAX(b.price-a.price) dp, a.price p0, b.price p1,742 b.ts ts1743 FROM price_log a JOIN price_log b744 ON a.uid=b.uid AND b.ts>a.ts745 AND a.price BETWEEN ? AND ? AND b.price BETWEEN ? AND ?746 AND b.price>a.price AND b.price<=a.price*2747 WHERE a.ts>=? AND b.ts<? LIMIT 1""",748 (PRIX_MIN, PRIX_MAX, PRIX_MIN, PRIX_MAX, ts_from, ts_to)).fetchone()749 if hike and hike["dp"]:750 li = con.execute("SELECT city, unit_type FROM listings WHERE uid=?",751 (hike["uid"],)).fetchone()752 where = " · ".join(x for x in [li["unit_type"], li["city"]] if x) if li else ""753 records.append({754 "label": "Plus forte hausse de loyer observée"755 + (f" ({where})" if where else ""),756 "value": f"+{_fr_money(hike['dp'])}"757 f" ({_fr_money(hike['p0'])} → {_fr_money(hike['p1'])})",758 "date": datetime.fromtimestamp(hike["ts1"], TZ).date().isoformat()})759 if hourly:760 peak = max(hourly["cells"], key=lambda c: c["value"])761 records.append({"label": "Heure de pointe des ajouts (période)",762 "value": f"{DOW_FR[peak['dow']]} {peak['hour']} h — "763 f"{_fr_int(peak['value'])} annonces"})764 if top_srcs:765 src = max(top_srcs, key=lambda r: r["new_n"])766 if src["new_n"]:767 records.append({"label": "Source la plus active (nouvelles annonces)",768 "value": f"{names.get(src['s'], src['s'])} — {src['new_n']}"})769770 out = {771 "updated": datetime.now(TZ).isoformat(),772 "period": {"from": d_from.isoformat(), "to": d_to.isoformat(),773 "label": label},774 "kpis": kpis,775 "series": series,776 "breakdowns": breakdowns,777 "tables": tables,778 "records": records,779 }780 if gauges:781 out["gauges"] = gauges782 if multiseries:783 out["multiseries"] = multiseries784 if stacked:785 out["stacked"] = stacked786 if distributions:787 out["distributions"] = distributions788 if geo:789 out["geo"] = geo790 if heatmap:791 out["heatmap"] = heatmap792 if hourly:793 out["hourly"] = hourly794 try:795 from . import statsfiche796 pnls = statsfiche.panels(con)797 if pnls:798 out["panels"] = pnls799 except Exception:800 pass801 return out802