Trouve-KA — moteur de recherche web indépendant, Québec-first. Crawler distribué, index OpenSearch, ranking bilingue, galerie d'images. En prod : www.trouve-ka.com
Python 51.9%
TypeScript 32.7%
JavaScript 4.8%
CSS 4.1%
Shell 2.9%
HTML 1.8%
SQL 1.5%
1# Trouve-KA — module Stats : dashboard analytique + rapport PDF Groupe-KA2# Author: Simon-Pierre Boucher3# Contact: contact@spboucher.ai45"""Statistiques du moteur (contrat ka-ui/stats/SPEC.md v2).67- `dashboard()` agrège Postgres (documents, crawl_attempts, frontier_items,8 domains, search_queries) en JSON conforme au contrat /api/stats/dashboard :9 KPI (+sparklines), jauges, séries, multi-séries, empilées, répartitions,10 distributions, heatmap calendrier, heatmap horaire 7×24, tableaux, records.11- Cache mémoire par période (>= 5 min) : les tables sont volumineuses12 (crawl_attempts ~5 M lignes), on ne re-agrège jamais à chaque appel.13 Toutes les requêtes sont bornées sur des colonnes indexées (fetched_at,14 first_indexed_at, created_at) — jamais de scan complet par requête HTTP.15 Les agrégats globaux lourds (profil documents, profondeurs frontier)16 vivent dans un cache dédié (10 min à 1 h).17- AUCUNE donnée inventée : une section sans données est simplement absente18 (le front affiche « Pas encore mesuré »).19- Purge opportuniste des journaux de recherche (> 90 jours), 1×/jour max.20"""2122from __future__ import annotations2324import asyncio25import time26from datetime import datetime, timedelta27from zoneinfo import ZoneInfo2829from trouveka.logging import get_logger3031log = get_logger("stats")3233TZ = ZoneInfo("America/Toronto")3435SITE = {36 "wordmark": "Trouve·Ka",37 "domain": "www.trouve-ka.com",38 "accent": "#1c7ed6",39 "tagline": "Le moteur de recherche du Groupe KA",40}4142PERIOD_LABELS = {43 "auj": "aujourd'hui",44 "7j": "7 jours",45 "30j": "30 jours",46 "3m": "3 mois",47 "6m": "6 mois",48 "12m": "12 mois",49 "annee": "année en cours",50 "tout": "toute la période",51}5253OUTCOME_LABELS = {54 "indexed": "Indexée",55 "duplicate": "Doublon",56 "error": "Erreur",57 "not_quebec": "Hors périmètre",58 "robots_blocked": "Bloquée (robots.txt)",59 "redirect": "Redirection",60 "unchanged": "Inchangée",61}6263FRONTIER_LABELS = {64 "pending": "En attente",65 "in_progress": "En cours",66 "done": "Complétée",67 "failed": "Échouée",68 "blocked": "Bloquée",69}7071LANG_LABELS = {"fr": "Français", "en": "Anglais", "?": "Non détectée"}7273HTTP_CLASSES = ["2xx", "3xx", "4xx", "5xx", "Sans réponse"]7475# Cache mémoire : clé période → (expiration monotonic, payload)76_cache: dict[str, tuple[float, dict]] = {}77_global_cache: dict[str, tuple[float, object]] = {}78_build_lock = asyncio.Lock()79_last_purge = 0.08081CACHE_TTL = 300 # 5 min par période82CACHE_TTL_TOUT = 900 # « tout » agrège 5 M lignes : 15 min83GLOBAL_TTL = 600 # heatmap / profil documents / records globaux84DEEP_TTL = 3600 # profondeurs frontier (scan 3,7 M lignes) : 1 h858687def _fr_int(n: int | float) -> str:88 return f"{int(n):,}".replace(",", " ")899091def _delta(cur: float | None, prev: float | None) -> float | None:92 """Variation % vs période précédente. Pas de période de référence → None."""93 if cur is None or prev is None or prev == 0:94 return None # pas de période de référence mesurée → pas de faux delta95 return round(100.0 * (cur - prev) / prev, 1)969798def _spark(points: list[dict], max_pts: int = 40) -> list[dict] | None:99 """Sous-échantillonne une série pour la sparkline d'un KPI (≥ 2 points)."""100 pts = [p for p in points if isinstance(p.get("v"), (int, float))]101 if len(pts) < 2:102 return None103 if len(pts) <= max_pts:104 return pts105 step = len(pts) / max_pts106 out = [pts[int(i * step)] for i in range(max_pts)]107 if out[-1] is not pts[-1]:108 out.append(pts[-1])109 return out110111112def _mb(nbytes: int) -> tuple[float, str]:113 """Octets → (valeur, unité) lisibles (Mo / Go / To)."""114 if nbytes >= 1e12:115 return round(nbytes / 1e12, 2), "To"116 if nbytes >= 1e9:117 return round(nbytes / 1e9, 2), "Go"118 return round(nbytes / 1e6, 1), "Mo"119120121def resolve_period(122 period: str, from_s: str | None, to_s: str | None, dataset_start: datetime123) -> tuple[datetime, datetime, str, datetime | None, datetime | None]:124 """→ (start, end, label, prev_start, prev_end) en heure de l'Est."""125 now = datetime.now(TZ)126 if from_s and to_s:127 start = datetime.strptime(from_s, "%Y-%m-%d").replace(tzinfo=TZ)128 end = datetime.strptime(to_s, "%Y-%m-%d").replace(tzinfo=TZ) + timedelta(days=1)129 end = min(end, now)130 label = f"du {from_s} au {to_s}"131 elif period == "auj":132 start = now.replace(hour=0, minute=0, second=0, microsecond=0)133 end, label = now, PERIOD_LABELS["auj"]134 elif period == "annee":135 start = now.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)136 end, label = now, PERIOD_LABELS["annee"]137 elif period == "tout":138 start = dataset_start.astimezone(TZ)139 end, label = now, PERIOD_LABELS["tout"]140 else:141 days = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}.get(period, 30)142 start = now - timedelta(days=days)143 end, label = now, PERIOD_LABELS.get(period, PERIOD_LABELS["30j"])144 if start >= end:145 start = end - timedelta(days=1)146 if period == "tout" and not (from_s and to_s):147 return start, end, label, None, None148 span = end - start149 return start, end, label, start - span, start150151152def _bucket_grid(start: datetime, end: datetime, bucket: str) -> tuple[list[datetime], list[str]]:153 """Clés (datetime naïfs, heure locale — même forme que date_trunc AT TIME154 ZONE) et libellés d'axe pour une plage, trous compris."""155 keys: list[datetime] = []156 labels: list[str] = []157 if bucket == "hour":158 cur = start.replace(minute=0, second=0, microsecond=0)159 while cur < end:160 k = cur.astimezone(TZ).replace(tzinfo=None, minute=0, second=0, microsecond=0)161 keys.append(k)162 labels.append(k.strftime("%Hh"))163 cur += timedelta(hours=1)164 else:165 cur_d = start.astimezone(TZ).date()166 end_d = end.astimezone(TZ).date()167 while cur_d <= end_d:168 keys.append(datetime(cur_d.year, cur_d.month, cur_d.day))169 labels.append(cur_d.isoformat())170 cur_d += timedelta(days=1)171 return keys, labels172173174async def _dataset_start(db) -> datetime:175 """Première trace du crawler (min borné par index) — cache 1 h."""176 hit = _global_cache.get("dataset_start")177 if hit and hit[0] > time.monotonic():178 return hit[1] # type: ignore[return-value]179 row = await db.pool.fetchrow("SELECT min(fetched_at) AS t FROM crawl_attempts")180 start = row["t"] or datetime.now(TZ) - timedelta(days=1)181 _global_cache["dataset_start"] = (time.monotonic() + 3600, start)182 return start183184185async def _bucket_counts(186 db, table: str, ts_col: str, start: datetime, end: datetime,187 bucket: str, extra_where: str = ""188) -> list[dict]:189 """Comptes par jour (ou heure) en heure locale, trous remplis de zéros.190191 Toujours borné sur la colonne temporelle indexée — jamais de scan complet.192 """193 unit = "hour" if bucket == "hour" else "day"194 rows = await db.pool.fetch(195 f"""196 SELECT date_trunc('{unit}', {ts_col} AT TIME ZONE 'America/Toronto') AS b,197 count(*)::int AS n198 FROM {table}199 WHERE {ts_col} >= $1 AND {ts_col} < $2 {extra_where}200 GROUP BY 1 ORDER BY 1201 """,202 start, end,203 )204 got = {r["b"]: r["n"] for r in rows}205 keys, labels = _bucket_grid(start, end, bucket)206 return [{"t": lab, "v": got.get(k, 0)} for k, lab in zip(keys, labels)]207208209async def _heatmap_cells(db) -> list[dict]:210 """Indexations/jour sur 182 jours — partagé entre périodes (cache 10 min)."""211 hit = _global_cache.get("heatmap")212 if hit and hit[0] > time.monotonic():213 return hit[1] # type: ignore[return-value]214 since = datetime.now(TZ) - timedelta(days=182)215 rows = await db.pool.fetch(216 """217 SELECT (first_indexed_at AT TIME ZONE 'America/Toronto')::date AS d, count(*)::int AS n218 FROM documents WHERE first_indexed_at >= $1219 GROUP BY 1 ORDER BY 1220 """,221 since,222 )223 cells = [{"date": r["d"].isoformat(), "value": r["n"]} for r in rows]224 _global_cache["heatmap"] = (time.monotonic() + GLOBAL_TTL, cells)225 return cells226227228async def _doc_profile(db) -> dict:229 """Profil global de l'index (1 seul passage sur documents, cache 10 min) :230 total, fraîcheur < 30 j, langues, score Québec moyen."""231 hit = _global_cache.get("doc_profile")232 if hit and hit[0] > time.monotonic():233 return hit[1] # type: ignore[return-value]234 fresh_since = datetime.now(TZ) - timedelta(days=30)235 rows = await db.pool.fetch(236 """237 SELECT coalesce(nullif(language, ''), '?') AS lang,238 count(*)::int AS n,239 count(*) FILTER (WHERE last_indexed_at >= $1)::int AS fresh,240 sum(page_quebec_score)::float AS qs_sum241 FROM documents GROUP BY 1242 """,243 fresh_since,244 )245 total = sum(r["n"] for r in rows)246 prof = {247 "total": total,248 "fresh30": sum(r["fresh"] for r in rows),249 "avg_qs": round(sum(r["qs_sum"] or 0 for r in rows) / total, 2) if total else None,250 "langs": sorted(251 ({"label": LANG_LABELS.get(r["lang"], r["lang"]), "value": r["n"]} for r in rows),252 key=lambda x: -x["value"],253 ),254 }255 _global_cache["doc_profile"] = (time.monotonic() + GLOBAL_TTL, prof)256 return prof257258259async def _frontier_depths(db) -> tuple[list[dict], dict | None]:260 """Distribution des profondeurs + domaine le plus profond (scans lourds261 sur frontier_items ~3,7 M lignes → cache 1 h)."""262 hit = _global_cache.get("frontier_depths")263 if hit and hit[0] > time.monotonic():264 return hit[1] # type: ignore[return-value]265 rows = await db.pool.fetch(266 "SELECT least(depth, 10)::int AS d, count(*)::int AS n FROM frontier_items GROUP BY 1 ORDER BY 1"267 )268 bins = [{"label": ("10+" if r["d"] >= 10 else str(r["d"])), "value": r["n"]} for r in rows]269 deep = await db.pool.fetchrow(270 """271 SELECT d.domain, f.depth272 FROM frontier_items f273 JOIN urls u ON u.id = f.url_id274 JOIN domains d ON d.id = u.domain_id275 ORDER BY f.depth DESC LIMIT 1276 """277 )278 deepest = {"domain": deep["domain"], "depth": deep["depth"]} if deep else None279 val = (bins, deepest)280 _global_cache["frontier_depths"] = (time.monotonic() + DEEP_TTL, val)281 return val282283284async def _tld_breakdown(db) -> list[dict]:285 """Pages indexées par TLD (table domains, petite — cache 10 min)."""286 hit = _global_cache.get("tld")287 if hit and hit[0] > time.monotonic():288 return hit[1] # type: ignore[return-value]289 rows = await db.pool.fetch(290 """291 SELECT CASE WHEN domain LIKE '%.qc.ca' THEN 'qc.ca'292 ELSE substring(domain from '[^.]+$') END AS tld,293 sum(page_count)::int AS n294 FROM domains WHERE page_count > 0295 GROUP BY 1 ORDER BY n DESC LIMIT 9296 """297 )298 items = [{"label": "." + r["tld"], "value": r["n"]} for r in rows if r["tld"]]299 _global_cache["tld"] = (time.monotonic() + GLOBAL_TTL, items)300 return items301302303async def _crawl_hour_record(db) -> dict | None:304 """Heure record de crawl sur 30 jours (plage indexée — cache 10 min)."""305 hit = _global_cache.get("hour_record")306 if hit and hit[0] > time.monotonic():307 return hit[1] # type: ignore[return-value]308 since = datetime.now(TZ) - timedelta(days=30)309 row = await db.pool.fetchrow(310 """311 SELECT date_trunc('hour', fetched_at AT TIME ZONE 'America/Toronto') AS h, count(*)::int AS n312 FROM crawl_attempts WHERE fetched_at >= $1313 GROUP BY 1 ORDER BY n DESC LIMIT 1314 """,315 since,316 )317 val = {"hour": row["h"], "n": row["n"]} if row else None318 _global_cache["hour_record"] = (time.monotonic() + GLOBAL_TTL, val)319 return val320321322async def _purge_old_queries(db) -> None:323 """Rétention 90 jours des journaux de recherche — au plus 1×/jour."""324 global _last_purge325 if time.monotonic() - _last_purge < 86400 and _last_purge:326 return327 _last_purge = time.monotonic()328 try:329 res = await db.pool.execute(330 "DELETE FROM search_queries WHERE created_at < now() - interval '90 days'"331 )332 log.info("purge search_queries (90 j)", extra={"ctx": {"result": res}})333 except Exception:334 log.exception("purge search_queries échouée")335336337async def dashboard(db, period: str = "30j", from_s: str | None = None, to_s: str | None = None) -> dict:338 key = f"{period}|{from_s or ''}|{to_s or ''}"339 hit = _cache.get(key)340 if hit and hit[0] > time.monotonic():341 return hit[1]342 async with _build_lock:343 hit = _cache.get(key)344 if hit and hit[0] > time.monotonic():345 return hit[1]346 data = await _build(db, period, from_s, to_s)347 ttl = CACHE_TTL_TOUT if period == "tout" else CACHE_TTL348 _cache[key] = (time.monotonic() + ttl, data)349 return data350351352async def _build(db, period: str, from_s: str | None, to_s: str | None) -> dict:353 now = datetime.now(TZ)354 ds_start = await _dataset_start(db)355 start, end, label, prev_start, prev_end = resolve_period(period, from_s, to_s, ds_start)356 bucket = "hour" if (end - start) <= timedelta(hours=36) else "day"357 unit_sql = "hour" if bucket == "hour" else "day"358 unit_label = "par heure" if bucket == "hour" else "par jour"359 hours = max((end - start).total_seconds() / 3600.0, 0.01)360 keys, klabels = _bucket_grid(start, end, bucket)361362 await _purge_old_queries(db)363364 # ---- Crawl : un passage borné, ventilé par (bucket, outcome) ----------365 oc_rows = await db.pool.fetch(366 f"""367 SELECT date_trunc('{unit_sql}', fetched_at AT TIME ZONE 'America/Toronto') AS b,368 outcome, count(*)::int AS n,369 coalesce(sum(bytes), 0)::bigint AS bytes,370 max(bytes)::int AS bytes_max,371 sum(duration_ms)::bigint AS dur_sum,372 max(duration_ms)::int AS dur_max373 FROM crawl_attempts374 WHERE fetched_at >= $1 AND fetched_at < $2375 GROUP BY 1, 2376 """,377 start, end,378 )379 oc_map: dict[tuple, int] = {}380 outcome_totals: dict[str, int] = {}381 attempts_cur = errors_cur = indexed_ok_cur = 0382 bytes_sum = dur_sum = 0383 bytes_max = dur_max = 0384 for r in oc_rows:385 oc_map[(r["b"], r["outcome"])] = r["n"]386 outcome_totals[r["outcome"]] = outcome_totals.get(r["outcome"], 0) + r["n"]387 attempts_cur += r["n"]388 bytes_sum += r["bytes"]389 dur_sum += r["dur_sum"] or 0390 bytes_max = max(bytes_max, r["bytes_max"] or 0)391 dur_max = max(dur_max, r["dur_max"] or 0)392 errors_cur = outcome_totals.get("error", 0)393 indexed_ok_cur = outcome_totals.get("indexed", 0)394 crawl_pts = [{"t": lab, "v": sum(oc_map.get((k, o), 0) for o in outcome_totals)}395 for k, lab in zip(keys, klabels)]396 err_pts = [{"t": lab, "v": oc_map.get((k, "error"), 0)} for k, lab in zip(keys, klabels)]397 idx_ok_pts = [{"t": lab, "v": oc_map.get((k, "indexed"), 0)} for k, lab in zip(keys, klabels)]398399 # ---- Crawl : classes de statut HTTP par bucket (empilées) -------------400 st_rows = await db.pool.fetch(401 f"""402 SELECT date_trunc('{unit_sql}', fetched_at AT TIME ZONE 'America/Toronto') AS b,403 CASE WHEN status_code BETWEEN 200 AND 299 THEN '2xx'404 WHEN status_code BETWEEN 300 AND 399 THEN '3xx'405 WHEN status_code BETWEEN 400 AND 499 THEN '4xx'406 WHEN status_code >= 500 THEN '5xx'407 ELSE 'Sans réponse' END AS cls,408 count(*)::int AS n409 FROM crawl_attempts410 WHERE fetched_at >= $1 AND fetched_at < $2411 GROUP BY 1, 2412 """,413 start, end,414 )415 st_map = {(r["b"], r["cls"]): r["n"] for r in st_rows}416 st_totals = {c: sum(n for (b, cls), n in st_map.items() if cls == c) for c in HTTP_CLASSES}417 st_keys = [c for c in HTTP_CLASSES if st_totals.get(c)]418 stacked_pts = [{"t": lab, "values": [st_map.get((k, c), 0) for c in st_keys]}419 for k, lab in zip(keys, klabels)]420421 # ---- Période précédente (bornée) ---------------------------------------422 prev_oc: dict[str, int] = {}423 attempts_prev = errors_prev = indexed_prev = searches_prev = bytes_prev = None424 if prev_start is not None:425 prows = await db.pool.fetch(426 """427 SELECT outcome, count(*)::int AS n, coalesce(sum(bytes), 0)::bigint AS bytes428 FROM crawl_attempts WHERE fetched_at >= $1 AND fetched_at < $2429 GROUP BY 1430 """,431 prev_start, prev_end,432 )433 prev_oc = {r["outcome"]: r["n"] for r in prows}434 attempts_prev = sum(prev_oc.values())435 errors_prev = prev_oc.get("error", 0)436 bytes_prev = sum(r["bytes"] for r in prows)437 prev = await db.pool.fetchrow(438 """439 SELECT440 (SELECT count(*)::int FROM documents441 WHERE first_indexed_at >= $1 AND first_indexed_at < $2) AS indexed_prev,442 (SELECT count(*)::int FROM search_queries443 WHERE created_at >= $1 AND created_at < $2) AS searches_prev444 """,445 prev_start, prev_end,446 )447 indexed_prev = prev["indexed_prev"]448 searches_prev = prev["searches_prev"]449450 # ---- Index / domaines / frontier (compteurs indexés) -------------------451 cur = await db.pool.fetchrow(452 """453 SELECT454 (SELECT count(*)::int FROM domains WHERE page_count > 0) AS domains_count,455 (SELECT count(*)::int FROM documents456 WHERE first_indexed_at >= $1 AND first_indexed_at < $2) AS indexed_cur,457 (SELECT count(*)::int FROM documents WHERE first_indexed_at < $1) AS docs_before,458 (SELECT count(*)::int FROM frontier_items WHERE status = 'pending') AS frontier_pending459 """,460 start, end,461 )462 prof = await _doc_profile(db)463 docs_total = prof["total"]464 indexed_cur = cur["indexed_cur"]465466 # ---- Recherches ---------------------------------------------------------467 sq = await db.pool.fetchrow(468 """469 SELECT count(*)::int AS n,470 round(avg((NOT zero_result)::int) * 100, 1)::float AS found_rate,471 round(avg(took_ms))::int AS took_avg472 FROM search_queries WHERE created_at >= $1 AND created_at < $2473 """,474 start, end,475 )476477 # ---- Séries -------------------------------------------------------------478 per_bucket = await _bucket_counts(db, "documents", "first_indexed_at", start, end, bucket)479 running = cur["docs_before"]480 cum_points = []481 for p in per_bucket:482 running += p["v"]483 cum_points.append({"t": p["t"], "v": running})484 idx_compare = None485 if prev_start is not None and bucket == "day":486 idx_compare = await _bucket_counts(487 db, "documents", "first_indexed_at", prev_start, prev_end, bucket488 )489490 growth = round(100.0 * indexed_cur / cur["docs_before"], 1) if cur["docs_before"] else None491 vol_val, vol_unit = _mb(bytes_sum)492493 kpis = [494 {"id": "pages", "label": "Pages indexées (total)", "value": docs_total,495 "delta_pct": growth, "direction": "up", "spark": _spark(cum_points)},496 {"id": "domaines", "label": "Sites du Groupe KA", "value": cur["domains_count"]},497 {"id": "crawlees", "label": f"Pages crawlées — {label}", "value": attempts_cur,498 "delta_pct": _delta(attempts_cur, attempts_prev), "spark": _spark(crawl_pts)},499 {"id": "rythme", "label": "Rythme de crawl", "value": round(attempts_cur / hours, 1),500 "unit": "pages/h", "delta_pct": _delta(attempts_cur, attempts_prev)},501 {"id": "indexees", "label": f"Pages indexées — {label}", "value": indexed_cur,502 "delta_pct": _delta(indexed_cur, indexed_prev), "spark": _spark(per_bucket)},503 {"id": "erreurs", "label": f"Erreurs de crawl — {label}", "value": errors_cur,504 "delta_pct": _delta(errors_cur, errors_prev), "spark": _spark(err_pts)},505 {"id": "frontier", "label": "Frontier en attente", "value": cur["frontier_pending"]},506 {"id": "recherches", "label": f"Recherches — {label}", "value": sq["n"],507 "delta_pct": _delta(sq["n"], searches_prev)},508 ]509 if bytes_sum:510 kpis.append({"id": "volume", "label": f"Volume téléchargé — {label}", "value": vol_val,511 "unit": vol_unit, "delta_pct": _delta(bytes_sum, bytes_prev)})512 if dur_sum and attempts_cur:513 kpis.append({"id": "duree", "label": "Durée moyenne d'une tentative",514 "value": int(round(dur_sum / attempts_cur)), "unit": "ms"})515516 # ---- Jauges -------------------------------------------------------------517 gauges = []518 if attempts_cur:519 gauges.append({"id": "succes", "label": "Crawl sans erreur",520 "value": round(100.0 * (attempts_cur - errors_cur) / attempts_cur, 1),521 "max": 100, "unit": "%",522 "help": "Part des tentatives de crawl qui ne finissent pas en erreur"})523 gauges.append({"id": "convert", "label": "Crawl menant à l'index",524 "value": round(100.0 * indexed_ok_cur / attempts_cur, 1),525 "max": 100, "unit": "%",526 "help": "Part des tentatives dont l'issue est « indexée »"})527 if docs_total:528 gauges.append({"id": "frais", "label": "Index frais (< 30 jours)",529 "value": round(100.0 * prof["fresh30"] / docs_total, 1),530 "max": 100, "unit": "%",531 "help": "Pages re-visitées par le crawler dans les 30 derniers jours"})532 if sq["n"] and sq["found_rate"] is not None:533 gauges.append({"id": "taux", "label": "Recherches avec résultats",534 "value": sq["found_rate"], "max": 100, "unit": "%"})535536 series = [537 {"id": "cumul", "title": "Pages indexées — cumul", "unit": "pages", "kind": "area",538 "points": cum_points},539 {"id": "crawlees", "title": f"Pages crawlées {unit_label}", "unit": "pages", "kind": "bar",540 "points": crawl_pts},541 {"id": "indexees", "title": f"Pages indexées {unit_label}", "unit": "pages", "kind": "line",542 "points": per_bucket, **({"compare": idx_compare} if idx_compare else {})},543 {"id": "erreurs", "title": f"Erreurs de crawl {unit_label}", "unit": "erreurs", "kind": "line",544 "points": err_pts},545 ]546 if sq["n"]:547 sq_bucket = await _bucket_counts(db, "search_queries", "created_at", start, end, bucket)548 series.append({"id": "recherches", "title": f"Recherches {unit_label}", "unit": "recherches",549 "kind": "bar", "points": sq_bucket})550551 multiseries = [{552 "id": "crawlmix", "title": f"Crawl {unit_label} : tentatives, indexées, erreurs",553 "unit": "pages",554 "series": [555 {"label": "Crawlées", "points": crawl_pts},556 {"label": "Indexées", "points": idx_ok_pts},557 {"label": "Erreurs", "points": err_pts},558 ],559 }]560561 stacked = []562 if st_keys:563 stacked.append({"id": "http", "title": f"Crawl {unit_label} par classe de statut HTTP",564 "unit": "pages", "keys": st_keys, "points": stacked_pts})565566 # ---- Répartitions -------------------------------------------------------567 frontier = await db.pool.fetch(568 "SELECT status, count(*)::int AS n FROM frontier_items GROUP BY status ORDER BY n DESC"569 )570 top_domains = await db.pool.fetch(571 """572 SELECT domain, page_count, url_count, round(quebec_score::numeric, 2)::float AS qs,573 last_crawled_at574 FROM domains WHERE page_count > 0575 ORDER BY page_count DESC LIMIT 100576 """577 )578 tld_items = await _tld_breakdown(db)579 breakdowns = [580 {"id": "outcomes", "title": f"Résultats de crawl — {label}", "kind": "donut",581 "items": [{"label": OUTCOME_LABELS.get(o, o), "value": n,582 **({"delta_pct": _delta(n, prev_oc.get(o))} if prev_oc else {})}583 for o, n in sorted(outcome_totals.items(), key=lambda kv: -kv[1])]},584 {"id": "http", "title": f"Crawl par classe de statut HTTP — {label}", "kind": "bars",585 "items": [{"label": c, "value": st_totals[c]} for c in st_keys]},586 {"id": "topdom", "title": "Pages indexées par site du Groupe KA (tout l'index)", "kind": "bars",587 "items": [{"label": r["domain"], "value": r["page_count"]} for r in top_domains[:14]]},588 {"id": "frontier", "title": "File frontier par statut", "kind": "donut",589 "items": [{"label": FRONTIER_LABELS.get(r["status"], r["status"]), "value": r["n"]}590 for r in frontier]},591 ]592 if tld_items:593 breakdowns.insert(2, {"id": "tld", "title": "Pages indexées par TLD", "kind": "donut",594 "items": tld_items})595 if prof["langs"]:596 breakdowns.append({"id": "langues", "title": "Langue des pages indexées", "kind": "donut",597 "items": prof["langs"][:8]})598599 # ---- Distributions ------------------------------------------------------600 distributions = []601 size_rows = await db.pool.fetch(602 """603 SELECT width_bucket(bytes, 0, 512000, 16) AS wb, count(*)::int AS n604 FROM crawl_attempts605 WHERE fetched_at >= $1 AND fetched_at < $2 AND bytes IS NOT NULL AND bytes > 0606 GROUP BY 1 ORDER BY 1607 """,608 start, end,609 )610 if size_rows:611 bins = []612 got = {r["wb"]: r["n"] for r in size_rows}613 for i in range(1, 17):614 bins.append({"label": f"{(i - 1) * 32}-{i * 32} Ko", "value": got.get(i, 0)})615 over = got.get(17, 0)616 if over:617 bins.append({"label": "> 512 Ko", "value": over})618 distributions.append({"id": "taille", "title": f"Distribution de la taille des pages — {label}",619 "unit": "pages", "bins": bins})620 dur_rows = await db.pool.fetch(621 """622 SELECT width_bucket(duration_ms, 0, 10000, 10) AS wb, count(*)::int AS n623 FROM crawl_attempts624 WHERE fetched_at >= $1 AND fetched_at < $2 AND duration_ms IS NOT NULL625 GROUP BY 1 ORDER BY 1626 """,627 start, end,628 )629 if dur_rows:630 got = {r["wb"]: r["n"] for r in dur_rows}631 bins = [{"label": f"{i - 1}-{i} s", "value": got.get(i, 0)} for i in range(1, 11)]632 over = got.get(11, 0)633 if over:634 bins.append({"label": "> 10 s", "value": over})635 distributions.append({"id": "duree", "title": f"Distribution de la durée des requêtes — {label}",636 "unit": "pages", "bins": bins})637 depth_bins, deepest = await _frontier_depths(db)638 if depth_bins:639 distributions.append({"id": "profondeur", "title": "Profondeur des URLs de la frontier",640 "unit": "URLs", "bins": depth_bins})641642 # ---- Heatmap horaire 7×24 (bornée à 56 jours max) -----------------------643 hh_start = max(start, end - timedelta(days=56))644 hh_rows = await db.pool.fetch(645 """646 SELECT (extract(isodow FROM fetched_at AT TIME ZONE 'America/Toronto')::int - 1) AS dow,647 extract(hour FROM fetched_at AT TIME ZONE 'America/Toronto')::int AS hr,648 count(*)::int AS n649 FROM crawl_attempts WHERE fetched_at >= $1 AND fetched_at < $2650 GROUP BY 1, 2651 """,652 hh_start, end,653 )654 hourly = None655 if hh_rows:656 hourly = {"title": "Pages crawlées par jour × heure",657 "cells": [{"dow": r["dow"], "hour": r["hr"], "value": r["n"]} for r in hh_rows]}658659 # ---- Tableaux -----------------------------------------------------------660 tables = [661 {"id": "topdom", "title": "Sites du Groupe KA (tout l'index)",662 "columns": ["Site", "Pages indexées", "URLs connues", "Dernier crawl"],663 "rows": [[r["domain"], r["page_count"], r["url_count"],664 r["last_crawled_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M")665 if r["last_crawled_at"] else "—"] for r in top_domains]},666 ]667 top_q = []668 if sq["n"]:669 top_q = await db.pool.fetch(670 """671 SELECT lower(query) AS q, count(*)::int AS n,672 round(avg(results_total))::int AS avg_res, max(created_at) AS last_at673 FROM search_queries WHERE created_at >= $1 AND created_at < $2674 GROUP BY 1 ORDER BY n DESC, last_at DESC LIMIT 50675 """,676 start, end,677 )678 tables.append({679 "id": "topq", "title": f"Top requêtes de recherche — {label}",680 "columns": ["Requête", "Recherches", "Résultats moy.", "Dernière fois"],681 "rows": [[r["q"], r["n"], r["avg_res"],682 r["last_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M")] for r in top_q],683 })684 zero_q = await db.pool.fetch(685 """686 SELECT lower(query) AS q, count(*)::int AS n, max(created_at) AS last_at687 FROM search_queries688 WHERE zero_result AND created_at >= $1 AND created_at < $2689 GROUP BY 1 ORDER BY n DESC, last_at DESC LIMIT 30690 """,691 start, end,692 )693 if zero_q:694 tables.append({695 "id": "zeroq", "title": f"Requêtes sans résultat — {label}",696 "columns": ["Requête", "Tentatives", "Dernière fois"],697 "rows": [[r["q"], r["n"],698 r["last_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M")] for r in zero_q],699 })700 err_dom = await db.pool.fetch(701 """702 SELECT d.domain, count(*)::int AS n, max(a.fetched_at) AS last_at703 FROM crawl_attempts a704 JOIN urls u ON u.id = a.url_id705 JOIN domains d ON d.id = u.domain_id706 WHERE a.outcome = 'error' AND a.fetched_at >= $1 AND a.fetched_at < $2707 GROUP BY 1 ORDER BY n DESC LIMIT 30708 """,709 start, end,710 )711 if err_dom:712 tables.append({713 "id": "errdom", "title": f"Erreurs de crawl par domaine — {label}",714 "columns": ["Domaine", "Erreurs", "Dernière erreur"],715 "rows": [[r["domain"], r["n"],716 r["last_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M")] for r in err_dom],717 })718 err_types = await db.pool.fetch(719 """720 SELECT coalesce(error_code, 'inconnu') AS code, count(*)::int AS n, max(fetched_at) AS last_at721 FROM crawl_attempts722 WHERE outcome = 'error' AND fetched_at >= $1 AND fetched_at < $2723 GROUP BY 1 ORDER BY n DESC724 """,725 start, end,726 )727 if err_types:728 tables.append({729 "id": "errtypes", "title": f"Erreurs de crawl par type — {label}",730 "columns": ["Type d'erreur", "Occurrences", "Dernière occurrence"],731 "rows": [[r["code"], r["n"],732 r["last_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M")] for r in err_types],733 })734 last_errors = await db.pool.fetch(735 """736 SELECT a.fetched_at, coalesce(a.error_code, 'inconnu') AS code, u.url737 FROM crawl_attempts a JOIN urls u ON u.id = a.url_id738 WHERE a.outcome = 'error' AND a.fetched_at >= $1 AND a.fetched_at < $2739 ORDER BY a.fetched_at DESC LIMIT 25740 """,741 start, end,742 )743 if last_errors:744 tables.append({745 "id": "lasterr", "title": "Dernières erreurs de crawl",746 "columns": ["Quand", "Type", "URL"],747 "rows": [[r["fetched_at"].astimezone(TZ).strftime("%Y-%m-%d %H:%M"),748 r["code"], r["url"][:120]] for r in last_errors],749 })750751 # ---- Heatmap calendrier & records (réels, jamais inventés) --------------752 cells = await _heatmap_cells(db)753 records = []754 if cells:755 best = max(cells, key=lambda c: c["value"])756 records.append({"label": "Jour record d'indexation",757 "value": f"{_fr_int(best['value'])} pages", "date": best["date"]})758 hr_rec = await _crawl_hour_record(db)759 if hr_rec:760 records.append({"label": "Heure record de crawl (30 derniers jours)",761 "value": f"{_fr_int(hr_rec['n'])} pages",762 "date": hr_rec["hour"].strftime("%Y-%m-%d %Hh")})763 if top_domains:764 records.append({"label": "Site le plus fourni",765 "value": f"{top_domains[0]['domain']} — {_fr_int(top_domains[0]['page_count'])} pages"})766 if deepest:767 records.append({"label": "Site exploré le plus profondément",768 "value": f"{deepest['domain']} (profondeur {deepest['depth']})"})769 if bytes_max:770 bm_val, bm_unit = _mb(bytes_max)771 records.append({"label": f"Page la plus lourde téléchargée — {label}",772 "value": f"{str(bm_val).replace('.', ',')} {bm_unit}"})773 if dur_max:774 records.append({"label": f"Plus longue tentative de crawl — {label}",775 "value": f"{str(round(dur_max / 1000, 1)).replace('.', ',')} s"})776 if prof["avg_qs"] is not None:777 records.append({"label": "Score Québec moyen des pages indexées",778 "value": str(prof["avg_qs"]).replace(".", ",")})779 if top_q:780 records.append({"label": f"Requête la plus fréquente — {label}",781 "value": f"« {top_q[0]['q'][:40]} » — {_fr_int(top_q[0]['n'])} fois"})782 if sq["n"] and sq["took_avg"] is not None:783 records.append({"label": f"Temps de réponse moyen des recherches — {label}",784 "value": f"{_fr_int(sq['took_avg'])} ms"})785 if sq["n"]:786 best_sq = await db.pool.fetchrow(787 """788 SELECT (created_at AT TIME ZONE 'America/Toronto')::date AS d, count(*)::int AS n789 FROM search_queries GROUP BY 1 ORDER BY n DESC LIMIT 1790 """791 )792 if best_sq:793 records.append({"label": "Jour record de recherches",794 "value": f"{_fr_int(best_sq['n'])} recherches",795 "date": best_sq["d"].isoformat()})796797 out = {798 "updated": now.isoformat(),799 "period": {"from": start.astimezone(TZ).date().isoformat(),800 "to": end.astimezone(TZ).date().isoformat(), "label": label},801 "kpis": kpis,802 "series": series,803 "multiseries": multiseries,804 "breakdowns": breakdowns,805 "heatmap": {"title": "Indexations par jour (26 dernières semaines)", "cells": cells},806 "tables": tables,807 "records": records,808 }809 if gauges:810 out["gauges"] = gauges811 if stacked:812 out["stacked"] = stacked813 if distributions:814 out["distributions"] = distributions815 if hourly:816 out["hourly"] = hourly817 return out818