# ----------------------------------------------------------------------------- # Sorti-Ka — Agrégateur de sorties & événements (province de Québec) # Auteur : Simon-Pierre Boucher — contact@spboucher.ai # stats.py : tableau de bord analytique /api/stats/dashboard (contrat ka-stats # SPEC.md v2) — agrégations SQL réelles sur events/sync_log, AUCUNE # statistique inventée, cache mémoire 5 min par période. # v2 : sparklines KPI, jauges, multi-courbes (top catégories), # barres empilées (sources, gratuits vs payants), distributions # (prix, durée), heatmap horaire 7×24, deltas de répartitions, # 5 tableaux, ~10 records. # ----------------------------------------------------------------------------- from __future__ import annotations import json import time from datetime import date, datetime, timedelta from zoneinfo import ZoneInfo from . import db TZ = ZoneInfo("America/Toronto") CACHE_TTL = 300 # ≥ 5 min (SPEC) _cache: dict[tuple, tuple[float, dict]] = {} HEATMAP_DAYS = 182 # 26 semaines de calendrier des dates d'événements ONGOING_DAYS = 30 # série « se déroulant par jour » (30 prochains jours) BIGDAYS_HORIZON = 90 # tableau « prochains gros jours » WEEKLY_OVER = 62 # au-delà de N jours, empilées/multi-courbes par semaine # Libellés humains (mêmes que le frontend — taxonomie sortika/normalize.py) CAT_LABELS = { "festival": "Festivals", "musique": "Musique", "arts-scene": "Arts de la scène", "exposition-musee": "Expos & musées", "cinema": "Cinéma", "sport": "Sport", "plein-air": "Plein air", "famille": "Famille", "gastronomie": "Gastronomie", "marche-foire": "Marchés & foires", "conference": "Conférences & ateliers", "communautaire": "Communautaire", "patrimoine": "Patrimoine", "autre": "Autre", } SRC_LABELS = { "sitq": "SIT Québec (MTO)", "montreal": "Ville de Montréal", "laval": "Ville de Laval", "lepointdevente": "Le point de vente", "evenko": "evenko", "atuvu": "Atuvu.ca", "lavitrine": "La Vitrine", "sherbrooke": "Ville de Sherbrooke", "eventbrite": "Eventbrite", "ticketmaster": "Ticketmaster", "bandsintown": "Bandsintown", "brossard": "Ville de Brossard", "longueuil": "Ville de Longueuil", "cantonsdelest": "Cantons-de-l'Est (ATR)", } PERIOD_DAYS = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365} PERIOD_LABELS = { "auj": "Aujourd'hui", "7j": "7 jours", "30j": "30 jours", "3m": "3 mois", "6m": "6 mois", "12m": "12 mois", "annee": "Année en cours", "tout": "Toute la période", } PRICE_BINS = [(0, 10), (10, 20), (20, 30), (30, 50), (50, 75), (75, 100), (100, 150), (150, None)] DURATION_BINS = [("1 jour", 1, 1), ("2-3 jours", 2, 3), ("4-7 jours", 4, 7), ("1-2 semaines", 8, 14), ("2-4 semaines", 15, 28), ("1-3 mois", 29, 92), ("3 mois +", 93, None)] def _parse_iso(s: str) -> date | None: try: return date.fromisoformat((s or "").strip()) except ValueError: return None def resolve_period(con, period: str, dfrom: str = "", dto: str = "") -> tuple[date, date, str]: """(from, to, label) — plage personnalisée > période nommée > 30 j.""" today = datetime.now(TZ).date() f, t = _parse_iso(dfrom), _parse_iso(dto) if f and t: if f > t: f, t = t, f return f, t, f"{f.isoformat()} → {t.isoformat()}" if period == "auj": return today, today, PERIOD_LABELS["auj"] if period == "annee": return date(today.year, 1, 1), today, PERIOD_LABELS["annee"] if period == "tout": row = con.execute( "SELECT date(MIN(first_seen),'unixepoch','localtime') FROM events" ).fetchone() start = _parse_iso(row[0] or "") or today return start, today, PERIOD_LABELS["tout"] days = PERIOD_DAYS.get(period, 30) return today - timedelta(days=days - 1), today, PERIOD_LABELS.get(period, "30 jours") def _pct(cur: float, prev: float) -> float | None: if prev <= 0: return None return round(100.0 * (cur - prev) / prev, 1) def _kpi(id, label, value, unit="", delta_pct=None, spark=None) -> dict: d = {"id": id, "label": label, "value": value, "unit": unit, "delta_pct": delta_pct} if delta_pct is not None: d["direction"] = "up" if delta_pct >= 0 else "down" if spark: d["spark"] = spark return d def _added_by_day(con, d0: date, d1: date) -> dict[str, int]: """Événements découverts (first_seen) par jour local, entre d0 et d1.""" return {r[0]: r[1] for r in con.execute( "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) " "FROM events WHERE d >= ? AND d <= ? GROUP BY d", (d0.isoformat(), d1.isoformat()))} def _ongoing_counts(con, start: date, days: int) -> list[int]: """Nb d'événements actifs SE DÉROULANT chaque jour (tableau de différences : +1 au début effectif, -1 au lendemain de la fin — O(événements + jours)).""" diff = [0] * (days + 1) horizon = start + timedelta(days=days - 1) for r in con.execute( "SELECT start_date, COALESCE(end_date, start_date) AS e FROM events " "WHERE active=1 AND quarantine IS NULL AND start_date IS NOT NULL " "AND start_date <= ? AND COALESCE(end_date, start_date) >= ?", (horizon.isoformat(), start.isoformat())): s = max(_parse_iso(r["start_date"]) or start, start) e = min(_parse_iso(r["e"]) or s, horizon) if e < s: continue diff[(s - start).days] += 1 diff[(e - start).days + 1] -= 1 out, acc = [], 0 for i in range(days): acc += diff[i] out.append(acc) return out def _upcoming_where() -> str: return ("active=1 AND quarantine IS NULL AND (end_date >= :today OR " "(end_date IS NULL AND start_date >= :today))") def _buckets(p_from: date, p_to: date) -> tuple[list[date], bool]: """Bornes des seaux temporels (jour, ou lundi de semaine si la période dépasse WEEKLY_OVER jours) couvrant [p_from, p_to].""" length = (p_to - p_from).days + 1 weekly = length > WEEKLY_OVER if not weekly: return [p_from + timedelta(days=i) for i in range(length)], False start = p_from - timedelta(days=p_from.weekday()) # lundi out, d = [], start while d <= p_to: out.append(d) d += timedelta(days=7) return out, True def _bucket_of(d: date, weekly: bool) -> date: return d - timedelta(days=d.weekday()) if weekly else d def dashboard(period: str = "30j", dfrom: str = "", dto: str = "") -> dict: key = (period, dfrom, dto) hit = _cache.get(key) if hit and time.time() - hit[0] < CACHE_TTL: return hit[1] con = db.connect() now = datetime.now(TZ) today = now.date() p_from, p_to, p_label = resolve_period(con, period, dfrom, dto) length = (p_to - p_from).days + 1 prev_to = p_from - timedelta(days=1) prev_from = prev_to - timedelta(days=length - 1) tp = {"today": today.isoformat()} # ---------- KPI ---------- active = con.execute("SELECT COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL").fetchone()[0] upcoming = con.execute( f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()}", tp).fetchone()[0] free_up = con.execute( f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} AND is_free=1", tp).fetchone()[0] paid_up = con.execute( f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} AND is_free=0", tp).fetchone()[0] unknown_price_up = upcoming - free_up - paid_up past = con.execute( "SELECT COUNT(*) FROM events WHERE start_date IS NOT NULL " "AND COALESCE(end_date, start_date) < :today", tp).fetchone()[0] cities = con.execute( "SELECT COUNT(DISTINCT city) FROM events WHERE active=1 AND quarantine IS NULL AND city != ''" ).fetchone()[0] venues_up = con.execute( f"SELECT COUNT(DISTINCT venue) FROM events WHERE {_upcoming_where()} " "AND venue != ''", tp).fetchone()[0] n_sources = con.execute( "SELECT COUNT(DISTINCT source) FROM events WHERE active=1 AND quarantine IS NULL").fetchone()[0] added_map = _added_by_day(con, prev_from, p_to) added_cur = sum(v for d, v in added_map.items() if p_from.isoformat() <= d <= p_to.isoformat()) added_prev = sum(v for d, v in added_map.items() if prev_from.isoformat() <= d <= prev_to.isoformat()) # « actifs » au début de la période, reconstruit depuis les journaux réels : # actifs_avant = actifs_maintenant − ajoutés depuis (toujours actifs) # + désactivés depuis (updated_at du passage active=0), # seulement s'ils existaient déjà avant la période added_active = con.execute( "SELECT COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL AND " "date(first_seen,'unixepoch','localtime') >= ?", (p_from.isoformat(),)).fetchone()[0] deactivated = con.execute( "SELECT COUNT(*) FROM events WHERE active=0 AND " "date(updated_at,'unixepoch','localtime') >= ? AND " "date(first_seen,'unixepoch','localtime') < ?", (p_from.isoformat(), p_from.isoformat())).fetchone()[0] prev_active = active - added_active + deactivated # sparklines : ajouts/jour (période) et « se déroulant » (30 prochains j.) horizon_counts = _ongoing_counts(con, today, HEATMAP_DAYS) spark_added = [{"t": (p_from + timedelta(days=i)).isoformat(), "v": added_map.get((p_from + timedelta(days=i)).isoformat(), 0)} for i in range(length)] spark_up = [{"t": (today + timedelta(days=i)).isoformat(), "v": horizon_counts[i]} for i in range(ONGOING_DAYS)] kpis = [ _kpi("actifs", "Événements actifs", active, "", _pct(active, prev_active)), _kpi("avenir", "Événements à venir", upcoming, "", None, spark_up), _kpi("ajouts", f"Ajoutés sur la période ({p_label})", added_cur, "", _pct(added_cur, added_prev), spark_added), _kpi("passes", "Événements passés (archives)", past), _kpi("gratuits", "Gratuits à venir", free_up), ] if free_up + paid_up > 0: kpis.append(_kpi( "part_gratuits", f"Part de gratuits (sur {free_up + paid_up} avec prix connu)", round(100.0 * free_up / (free_up + paid_up), 1), "%")) kpis += [ _kpi("villes", "Villes couvertes", cities), _kpi("lieux", "Lieux référencés (à venir)", venues_up), _kpi("sources_n", "Sources branchées", n_sources), ] prices = con.execute( f"SELECT COUNT(*), AVG(price_min) FROM events WHERE {_upcoming_where()} " "AND is_free=0 AND price_min IS NOT NULL AND price_min > 0", tp).fetchone() if prices[0] >= 10: # champ prix exploitable seulement si assez de relevés kpis.append(_kpi("prix", f"Prix moyen des billets ({prices[0]} relevés)", round(prices[1], 2), "$")) # ---------- jauges (complétude des fiches à venir) ---------- gauges = [] if upcoming: def _cov(id_, label, where_extra): n = con.execute( f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} " f"AND {where_extra}", tp).fetchone()[0] gauges.append({"id": id_, "label": label, "value": round(100.0 * n / upcoming, 1), "max": 100, "unit": "%"}) _cov("geo", "Événements géolocalisés", "lat IS NOT NULL AND lng IS NOT NULL") _cov("prixinfo", "Info de prix connue", "is_free IS NOT NULL") _cov("image", "Fiches avec image", "image != ''") _cov("heure", "Heure de début connue", "start_time IS NOT NULL AND start_time != ''") # ---------- séries ---------- series = [] s_added = {"id": "ajouts", "title": "Événements ajoutés par jour", "unit": "événements", "kind": "line", "points": spark_added} if period not in ("tout", "annee") and not (dfrom and dto): s_added["compare"] = [ {"t": (prev_from + timedelta(days=i)).isoformat(), "v": added_map.get((prev_from + timedelta(days=i)).isoformat(), 0)} for i in range(length)] series.append(s_added) series.append({ "id": "en_cours", "title": f"Événements se déroulant par jour ({ONGOING_DAYS} prochains jours)", "unit": "événements", "kind": "line", "points": spark_up}) starts_by_day = {r[0]: r[1] for r in con.execute( "SELECT start_date, COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL " "AND start_date >= ? GROUP BY start_date", (today.isoformat(),))} series.append({ "id": "debuts", "title": f"Événements débutant par jour ({ONGOING_DAYS} prochains jours)", "unit": "événements", "kind": "bar", "points": [{"t": (today + timedelta(days=i)).isoformat(), "v": starts_by_day.get((today + timedelta(days=i)).isoformat(), 0)} for i in range(ONGOING_DAYS)]}) # ---------- multi-courbes : ajouts par top catégories (≤ 4) ---------- buckets, weekly = _buckets(p_from, p_to) b_index = {b.isoformat(): i for i, b in enumerate(buckets)} cat_added: dict[str, list[int]] = {} cat_tot: dict[str, int] = {} src_added: dict[str, list[int]] = {} for r in con.execute( "SELECT date(first_seen,'unixepoch','localtime') AS d, categories, " "source FROM events WHERE d >= ? AND d <= ?", (p_from.isoformat(), p_to.isoformat())): d = _parse_iso(r["d"]) if not d: continue bi = b_index.get(_bucket_of(d, weekly).isoformat()) if bi is None: continue for c in json.loads(r["categories"] or "[]"): cat_added.setdefault(c, [0] * len(buckets))[bi] += 1 cat_tot[c] = cat_tot.get(c, 0) + 1 src_added.setdefault(r["source"], [0] * len(buckets))[bi] += 1 gran = "semaine" if weekly else "jour" multiseries = [] top4 = [c for c, _ in sorted(cat_tot.items(), key=lambda kv: -kv[1])[:4]] if top4 and added_cur: multiseries.append({ "id": "cat_ajouts", "title": f"Ajouts par {gran} — top {len(top4)} catégories", "unit": "événements", "series": [{"label": CAT_LABELS.get(c, c), "points": [{"t": buckets[i].isoformat(), "v": cat_added[c][i]} for i in range(len(buckets))]} for c in top4]}) # ---------- barres empilées ---------- stacked = [] if src_added and added_cur: top_src = [s for s, _ in sorted( ((s, sum(v)) for s, v in src_added.items()), key=lambda kv: -kv[1])[:5]] others = [s for s in src_added if s not in top_src] keys = [SRC_LABELS.get(s, s) for s in top_src] + ( ["Autres"] if others else []) pts = [] for i in range(len(buckets)): vals = [src_added[s][i] for s in top_src] if others: vals.append(sum(src_added[s][i] for s in others)) pts.append({"t": buckets[i].isoformat(), "values": vals}) stacked.append({"id": "src_ajouts", "title": f"Ajouts par {gran} et par source", "unit": "ajouts", "keys": keys, "points": pts}) # gratuits vs payants par semaine de tenue (12 prochaines semaines) monday = today - timedelta(days=today.weekday()) wk_free, wk_paid, wk_unk = [0] * 12, [0] * 12, [0] * 12 for r in con.execute( f"SELECT start_date, is_free FROM events WHERE {_upcoming_where()} " "AND start_date >= :today", tp): d = _parse_iso(r["start_date"]) if not d: continue wi = ((d - timedelta(days=d.weekday())) - monday).days // 7 if 0 <= wi < 12: if r["is_free"] == 1: wk_free[wi] += 1 elif r["is_free"] == 0: wk_paid[wi] += 1 else: wk_unk[wi] += 1 if sum(wk_free) + sum(wk_paid) + sum(wk_unk) > 0: stacked.append({ "id": "gratuite_temps", "title": "Gratuits vs payants — débuts par semaine (12 prochaines)", "unit": "événements", "keys": ["Gratuits", "Payants", "Prix non précisé"], "points": [{"t": (monday + timedelta(days=7 * i)).isoformat(), "values": [wk_free[i], wk_paid[i], wk_unk[i]]} for i in range(12)]}) # ---------- répartitions ---------- cat_counts: dict[str, int] = {} for r in con.execute( f"SELECT categories FROM events WHERE {_upcoming_where()}", tp): for c in json.loads(r["categories"] or "[]"): cat_counts[c] = cat_counts.get(c, 0) + 1 top_cats = sorted(cat_counts.items(), key=lambda kv: -kv[1])[:8] # ajouts par région (période vs précédente) — deltas honnêtes reg_cur: dict[str, int] = {} reg_prev: dict[str, int] = {} for r in con.execute( "SELECT region, date(first_seen,'unixepoch','localtime') AS d, " "COUNT(*) AS n FROM events WHERE d >= ? AND d <= ? " "GROUP BY region, d", (prev_from.isoformat(), p_to.isoformat())): lbl = r["region"] or "Non rattachée" if r["d"] >= p_from.isoformat(): reg_cur[lbl] = reg_cur.get(lbl, 0) + r["n"] else: reg_prev[lbl] = reg_prev.get(lbl, 0) + r["n"] breakdowns = [ {"id": "categories", "title": "Événements à venir par catégorie (top 8)", "kind": "donut", "items": [{"label": CAT_LABELS.get(c, c), "value": n} for c, n in top_cats]}, {"id": "gratuite", "title": "Gratuits vs payants (à venir)", "kind": "bar", "items": [ {"label": "Gratuits", "value": free_up}, {"label": "Payants", "value": paid_up}, {"label": "Prix non précisé", "value": unknown_price_up}, ]}, {"id": "sources", "title": "Événements actifs par source", "kind": "bar", "items": [{"label": SRC_LABELS.get(r["source"], r["source"]), "value": r["n"]} for r in con.execute( "SELECT source, COUNT(*) AS n FROM events WHERE active=1 AND quarantine IS NULL " "GROUP BY source ORDER BY n DESC")]}, ] if reg_cur: breakdowns.append({ "id": "reg_ajouts", "title": f"Ajouts par région ({p_label}) — Δ vs période précédente", "kind": "bar", "items": [{"label": lbl, "value": n, "delta_pct": _pct(n, reg_prev.get(lbl, 0))} for lbl, n in sorted(reg_cur.items(), key=lambda kv: -kv[1])[:12]]}) # ---------- distributions ---------- distributions = [] price_vals = [r[0] for r in con.execute( f"SELECT price_min FROM events WHERE {_upcoming_where()} " "AND is_free=0 AND price_min IS NOT NULL AND price_min > 0", tp)] if len(price_vals) >= 10: bins = [] for lo, hi in PRICE_BINS: n = sum(1 for v in price_vals if v >= lo and (hi is None or v < hi)) bins.append({"label": f"{lo}-{hi} $" if hi else f"{lo} $ +", "value": n}) distributions.append({ "id": "prix", "title": f"Distribution des prix d'entrée ({len(price_vals)} relevés, à venir)", "unit": "événements", "bins": bins}) dur_bins = [0] * len(DURATION_BINS) n_dur = 0 for r in con.execute( f"SELECT julianday(COALESCE(end_date,start_date)) - " f"julianday(start_date) + 1 AS j FROM events " f"WHERE {_upcoming_where()} AND start_date IS NOT NULL", tp): j = int(r["j"] or 1) n_dur += 1 for i, (_, lo, hi) in enumerate(DURATION_BINS): if j >= lo and (hi is None or j <= hi): dur_bins[i] += 1 break if n_dur: distributions.append({ "id": "duree", "title": "Durée des événements à venir", "unit": "événements", "bins": [{"label": DURATION_BINS[i][0], "value": dur_bins[i]} for i in range(len(DURATION_BINS))]}) # ---------- géographie ---------- geo = {"title": "Événements à venir par région", "items": [ {"label": r["region"] or "Non rattachée", "value": r["n"]} for r in con.execute( f"SELECT region, COUNT(*) AS n FROM events WHERE {_upcoming_where()} " "GROUP BY region ORDER BY n DESC LIMIT 17", tp)]} # ---------- heatmap : calendrier des dates d'événements (26 semaines) ---- heatmap = {"title": "Calendrier des événements — jours les plus chargés", "cells": [{"date": (today + timedelta(days=i)).isoformat(), "value": horizon_counts[i]} for i in range(HEATMAP_DAYS)]} # ---------- heatmap horaire 7×24 : heure de début des événements à venir -- hourly = None hh: dict[tuple[int, int], int] = {} n_hourly = 0 for r in con.execute( f"SELECT start_date, start_time FROM events WHERE {_upcoming_where()} " "AND start_time IS NOT NULL AND start_time != '' " "AND start_date IS NOT NULL", tp): d = _parse_iso(r["start_date"]) try: h = int(str(r["start_time"])[:2]) except ValueError: continue if d is None or not 0 <= h <= 23: continue hh[(d.weekday(), h)] = hh.get((d.weekday(), h), 0) + 1 n_hourly += 1 if n_hourly >= 20: hourly = {"title": f"Débuts d'événements par jour et heure " f"({n_hourly} événements à venir avec heure)", "cells": [{"dow": k[0], "hour": k[1], "value": v} for k, v in sorted(hh.items())]} # ---------- tableaux ---------- city_rows = [[r["city"], r["n"], r["g"], f"{100.0 * r['n'] / upcoming:.1f} %".replace(".", ",")] for r in con.execute( f"SELECT city, COUNT(*) AS n, " f"SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) AS g " f"FROM events WHERE {_upcoming_where()} AND city != '' " "GROUP BY city ORDER BY n DESC LIMIT 40", tp)] venue_rows = [[r["venue"], r["city"] or "—", r["n"]] for r in con.execute( f"SELECT venue, city, COUNT(*) AS n FROM events " f"WHERE {_upcoming_where()} AND venue != '' " "GROUP BY venue, city ORDER BY n DESC LIMIT 40", tp)] big_days = sorted( ((today + timedelta(days=i), horizon_counts[i]) for i in range(min(BIGDAYS_HORIZON, HEATMAP_DAYS))), key=lambda dv: -dv[1])[:15] bigday_rows = [[d.isoformat(), n, starts_by_day.get(d.isoformat(), 0)] for d, n in big_days] org_rows = [[r["organizer"], r["n"], r["g"], r["c"]] for r in con.execute( f"SELECT organizer, COUNT(*) AS n, " f"SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) AS g, " f"COUNT(DISTINCT city) AS c FROM events " f"WHERE {_upcoming_where()} AND organizer != '' " "GROUP BY organizer ORDER BY n DESC LIMIT 40", tp)] # sources & connecteurs : actifs, ajoutés (période), dernière synchro OK last_sync = {r["source"]: r["ts"] for r in con.execute( "SELECT source, MAX(ts) AS ts FROM sync_log WHERE error IS NULL " "GROUP BY source")} src_rows = [] for r in con.execute( "SELECT source, COUNT(*) AS n FROM events WHERE active=1 AND quarantine IS NULL " "GROUP BY source ORDER BY n DESC"): s = r["source"] ts = last_sync.get(s) sync_txt = (datetime.fromtimestamp(ts, TZ).strftime("%Y-%m-%d %H:%M") if ts else "—") src_rows.append([SRC_LABELS.get(s, s), r["n"], sum(src_added.get(s, [])), sync_txt]) tables = [ {"id": "villes", "title": "Top villes (événements à venir)", "columns": ["Ville", "À venir", "Gratuits", "Part"], "rows": city_rows}, {"id": "lieux", "title": "Top lieux d'événements (à venir)", "columns": ["Lieu", "Ville", "À venir"], "rows": venue_rows}, {"id": "gros_jours", "title": f"Prochains gros jours ({BIGDAYS_HORIZON} jours)", "columns": ["Date", "Événements ce jour-là", "Débutent ce jour-là"], "rows": bigday_rows}, {"id": "organisateurs", "title": "Top organisateurs (à venir)", "columns": ["Organisateur", "À venir", "Gratuits", "Villes"], "rows": org_rows}, {"id": "sources_sync", "title": "Sources & connecteurs", "columns": ["Source", "Événements actifs", f"Ajoutés ({p_label})", "Dernière synchro"], "rows": src_rows}, ] # ---------- records ---------- records = [] if horizon_counts: i_max = max(range(HEATMAP_DAYS), key=lambda i: horizon_counts[i]) records.append({"label": "Jour le plus chargé (26 prochaines semaines)", "value": f"{horizon_counts[i_max]:,} événements".replace(",", " "), "date": (today + timedelta(days=i_max)).isoformat()}) best_w, best_wi = -1, 0 for i in range(HEATMAP_DAYS - 6): s = sum(horizon_counts[i:i + 7]) if s > best_w: best_w, best_wi = s, i records.append({"label": "Semaine la plus chargée (à venir)", "value": f"≈ {round(best_w / 7):,} événements/jour".replace(",", " "), "date": (today + timedelta(days=best_wi)).isoformat()}) add_rec = con.execute( "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) AS n " "FROM events GROUP BY d ORDER BY n DESC LIMIT 1").fetchone() if add_rec: records.append({"label": "Plus grosse journée d'ajouts", "value": f"{add_rec['n']:,} événements".replace(",", " "), "date": add_rec["d"]}) if city_rows: records.append({"label": "Ville la plus active (à venir)", "value": f"{city_rows[0][0]} — " f"{city_rows[0][1]:,} événements".replace(",", " ")}) if venue_rows: records.append({"label": "Lieu le plus actif (à venir)", "value": f"{venue_rows[0][0]} — " f"{venue_rows[0][2]:,} événements".replace(",", " ")}) if geo["items"]: g0 = geo["items"][0] records.append({"label": "Région la plus animée (à venir)", "value": f"{g0['label']} — " f"{g0['value']:,} événements".replace(",", " ")}) if top_cats: records.append({"label": "Catégorie dominante (à venir)", "value": f"{CAT_LABELS.get(top_cats[0][0], top_cats[0][0])}" f" — {top_cats[0][1]:,} événements".replace(",", " ")}) if hh: (dow, hr), n_pk = max(hh.items(), key=lambda kv: kv[1]) days_fr = ["lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"] records.append({"label": "Créneau de début le plus fréquent (à venir)", "value": f"{days_fr[dow]} {hr} h — " f"{n_pk:,} événements".replace(",", " ")}) if src_added and added_cur: s_top = max(src_added.items(), key=lambda kv: sum(kv[1])) records.append({"label": f"Source la plus productive ({p_label})", "value": f"{SRC_LABELS.get(s_top[0], s_top[0])} — " f"{sum(s_top[1]):,} ajouts".replace(",", " ")}) longest = con.execute( f"SELECT title, start_date, end_date, " f"julianday(end_date) - julianday(start_date) + 1 AS j " f"FROM events WHERE {_upcoming_where()} AND end_date > start_date " "ORDER BY j DESC LIMIT 1", tp).fetchone() if longest: records.append({"label": "Plus longue affiche à venir — " f"{longest['title'][:36]}", "value": f"{int(longest['j']):,} jours".replace(",", " "), "date": longest["start_date"]}) con.close() payload = { "updated": now.isoformat(timespec="seconds"), "period": {"from": p_from.isoformat(), "to": p_to.isoformat(), "label": p_label}, "kpis": kpis, "gauges": gauges, "series": series, "multiseries": multiseries, "stacked": stacked, "breakdowns": breakdowns, "distributions": distributions, "geo": geo, "heatmap": heatmap, "tables": tables, "records": records, } if hourly: payload["hourly"] = hourly _cache[key] = (time.time(), payload) return payload