Repository Public
1# -----------------------------------------------------------------------------2# Sorti-Ka — Agrégateur de sorties & événements (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# stats.py : tableau de bord analytique /api/stats/dashboard (contrat ka-stats5# SPEC.md) — agrégations SQL réelles sur events/sync_log, AUCUNE6# statistique inventée, cache mémoire 5 min par période.7# -----------------------------------------------------------------------------8from __future__ import annotations910import json11import time12from datetime import date, datetime, timedelta13from zoneinfo import ZoneInfo1415from . import db1617TZ = ZoneInfo("America/Toronto")18CACHE_TTL = 300 # ≥ 5 min (SPEC)19_cache: dict[tuple, tuple[float, dict]] = {}2021HEATMAP_DAYS = 182 # 26 semaines de calendrier des dates d'événements22ONGOING_DAYS = 30 # série « se déroulant par jour » (30 prochains jours)23BIGDAYS_HORIZON = 90 # tableau « prochains gros jours »2425# Libellés humains (mêmes que le frontend — taxonomie sortika/normalize.py)26CAT_LABELS = {27 "festival": "Festivals", "musique": "Musique", "arts-scene": "Arts de la scène",28 "exposition-musee": "Expos & musées", "cinema": "Cinéma", "sport": "Sport",29 "plein-air": "Plein air", "famille": "Famille", "gastronomie": "Gastronomie",30 "marche-foire": "Marchés & foires", "conference": "Conférences & ateliers",31 "communautaire": "Communautaire", "patrimoine": "Patrimoine", "autre": "Autre",32}33SRC_LABELS = {34 "sitq": "SIT Québec (MTO)", "montreal": "Ville de Montréal",35 "laval": "Ville de Laval", "lepointdevente": "Le point de vente",36 "evenko": "evenko", "atuvu": "Atuvu.ca", "lavitrine": "La Vitrine",37 "sherbrooke": "Ville de Sherbrooke",38}3940PERIOD_DAYS = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}41PERIOD_LABELS = {42 "auj": "Aujourd'hui", "7j": "7 jours", "30j": "30 jours", "3m": "3 mois",43 "6m": "6 mois", "12m": "12 mois", "annee": "Année en cours",44 "tout": "Toute la période",45}464748def _parse_iso(s: str) -> date | None:49 try:50 return date.fromisoformat((s or "").strip())51 except ValueError:52 return None535455def resolve_period(con, period: str, dfrom: str = "", dto: str = "") -> tuple[date, date, str]:56 """(from, to, label) — plage personnalisée > période nommée > 30 j."""57 today = datetime.now(TZ).date()58 f, t = _parse_iso(dfrom), _parse_iso(dto)59 if f and t:60 if f > t:61 f, t = t, f62 return f, t, f"{f.isoformat()} → {t.isoformat()}"63 if period == "auj":64 return today, today, PERIOD_LABELS["auj"]65 if period == "annee":66 return date(today.year, 1, 1), today, PERIOD_LABELS["annee"]67 if period == "tout":68 row = con.execute(69 "SELECT date(MIN(first_seen),'unixepoch','localtime') FROM events"70 ).fetchone()71 start = _parse_iso(row[0] or "") or today72 return start, today, PERIOD_LABELS["tout"]73 days = PERIOD_DAYS.get(period, 30)74 return today - timedelta(days=days - 1), today, PERIOD_LABELS.get(period, "30 jours")757677def _pct(cur: float, prev: float) -> float | None:78 if prev <= 0:79 return None80 return round(100.0 * (cur - prev) / prev, 1)818283def _kpi(id, label, value, unit="", delta_pct=None) -> dict:84 d = {"id": id, "label": label, "value": value, "unit": unit,85 "delta_pct": delta_pct}86 if delta_pct is not None:87 d["direction"] = "up" if delta_pct >= 0 else "down"88 return d899091def _added_by_day(con, d0: date, d1: date) -> dict[str, int]:92 """Événements découverts (first_seen) par jour local, entre d0 et d1."""93 return {r[0]: r[1] for r in con.execute(94 "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) "95 "FROM events WHERE d >= ? AND d <= ? GROUP BY d",96 (d0.isoformat(), d1.isoformat()))}979899def _ongoing_counts(con, start: date, days: int) -> list[int]:100 """Nb d'événements actifs SE DÉROULANT chaque jour (tableau de différences :101 +1 au début effectif, -1 au lendemain de la fin — O(événements + jours))."""102 diff = [0] * (days + 1)103 horizon = start + timedelta(days=days - 1)104 for r in con.execute(105 "SELECT start_date, COALESCE(end_date, start_date) AS e FROM events "106 "WHERE active=1 AND start_date IS NOT NULL "107 "AND start_date <= ? AND COALESCE(end_date, start_date) >= ?",108 (horizon.isoformat(), start.isoformat())):109 s = max(_parse_iso(r["start_date"]) or start, start)110 e = min(_parse_iso(r["e"]) or s, horizon)111 if e < s:112 continue113 diff[(s - start).days] += 1114 diff[(e - start).days + 1] -= 1115 out, acc = [], 0116 for i in range(days):117 acc += diff[i]118 out.append(acc)119 return out120121122def _upcoming_where() -> str:123 return ("active=1 AND (end_date >= :today OR "124 "(end_date IS NULL AND start_date >= :today))")125126127def dashboard(period: str = "30j", dfrom: str = "", dto: str = "") -> dict:128 key = (period, dfrom, dto)129 hit = _cache.get(key)130 if hit and time.time() - hit[0] < CACHE_TTL:131 return hit[1]132133 con = db.connect()134 now = datetime.now(TZ)135 today = now.date()136 p_from, p_to, p_label = resolve_period(con, period, dfrom, dto)137 length = (p_to - p_from).days + 1138 prev_to = p_from - timedelta(days=1)139 prev_from = prev_to - timedelta(days=length - 1)140 tp = {"today": today.isoformat()}141142 # ---------- KPI ----------143 active = con.execute("SELECT COUNT(*) FROM events WHERE active=1").fetchone()[0]144 upcoming = con.execute(145 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()}", tp).fetchone()[0]146 free_up = con.execute(147 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} AND is_free=1",148 tp).fetchone()[0]149 cities = con.execute(150 "SELECT COUNT(DISTINCT city) FROM events WHERE active=1 AND city != ''"151 ).fetchone()[0]152153 added_map = _added_by_day(con, prev_from, p_to)154 added_cur = sum(v for d, v in added_map.items()155 if p_from.isoformat() <= d <= p_to.isoformat())156 added_prev = sum(v for d, v in added_map.items()157 if prev_from.isoformat() <= d <= prev_to.isoformat())158159 # « actifs » au début de la période, reconstruit depuis les journaux réels :160 # actifs_avant = actifs_maintenant − ajoutés depuis (toujours actifs)161 # + désactivés depuis (updated_at du passage active=0),162 # seulement s'ils existaient déjà avant la période163 added_active = con.execute(164 "SELECT COUNT(*) FROM events WHERE active=1 AND "165 "date(first_seen,'unixepoch','localtime') >= ?",166 (p_from.isoformat(),)).fetchone()[0]167 deactivated = con.execute(168 "SELECT COUNT(*) FROM events WHERE active=0 AND "169 "date(updated_at,'unixepoch','localtime') >= ? AND "170 "date(first_seen,'unixepoch','localtime') < ?",171 (p_from.isoformat(), p_from.isoformat())).fetchone()[0]172 prev_active = active - added_active + deactivated173174 kpis = [175 _kpi("actifs", "Événements actifs", active, "",176 _pct(active, prev_active)),177 _kpi("avenir", "Événements à venir", upcoming),178 _kpi("gratuits", "Gratuits à venir", free_up),179 _kpi("villes", "Villes couvertes", cities),180 _kpi("ajouts", f"Ajoutés sur la période ({p_label})", added_cur, "",181 _pct(added_cur, added_prev)),182 ]183 prices = con.execute(184 f"SELECT COUNT(*), AVG(price_min) FROM events WHERE {_upcoming_where()} "185 "AND is_free=0 AND price_min IS NOT NULL AND price_min > 0",186 tp).fetchone()187 if prices[0] >= 10: # champ prix exploitable seulement si assez de relevés188 kpis.append(_kpi("prix", f"Prix moyen des billets ({prices[0]} relevés)",189 round(prices[1], 2), "$"))190191 # ---------- séries ----------192 series = []193 pts = [{"t": (p_from + timedelta(days=i)).isoformat(),194 "v": added_map.get((p_from + timedelta(days=i)).isoformat(), 0)}195 for i in range(length)]196 s_added = {"id": "ajouts", "title": "Événements ajoutés par jour",197 "unit": "événements", "kind": "line", "points": pts}198 if period not in ("tout", "annee") and not (dfrom and dto):199 s_added["compare"] = [200 {"t": (prev_from + timedelta(days=i)).isoformat(),201 "v": added_map.get((prev_from + timedelta(days=i)).isoformat(), 0)}202 for i in range(length)]203 series.append(s_added)204205 horizon_counts = _ongoing_counts(con, today, HEATMAP_DAYS)206 series.append({207 "id": "en_cours", "title":208 f"Événements se déroulant par jour ({ONGOING_DAYS} prochains jours)",209 "unit": "événements", "kind": "line",210 "points": [{"t": (today + timedelta(days=i)).isoformat(),211 "v": horizon_counts[i]} for i in range(ONGOING_DAYS)]})212213 # ---------- répartitions ----------214 cat_counts: dict[str, int] = {}215 for r in con.execute(216 f"SELECT categories FROM events WHERE {_upcoming_where()}", tp):217 for c in json.loads(r["categories"] or "[]"):218 cat_counts[c] = cat_counts.get(c, 0) + 1219 top_cats = sorted(cat_counts.items(), key=lambda kv: -kv[1])[:8]220 breakdowns = [221 {"id": "categories", "title": "Événements à venir par catégorie (top 8)",222 "kind": "donut",223 "items": [{"label": CAT_LABELS.get(c, c), "value": n}224 for c, n in top_cats]},225 {"id": "gratuite", "title": "Gratuits vs payants (à venir)", "kind": "bar",226 "items": [227 {"label": "Gratuits", "value": free_up},228 {"label": "Payants", "value": con.execute(229 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} "230 "AND is_free=0", tp).fetchone()[0]},231 {"label": "Prix non précisé", "value": con.execute(232 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} "233 "AND is_free IS NULL", tp).fetchone()[0]},234 ]},235 {"id": "sources", "title": "Événements actifs par source", "kind": "bar",236 "items": [{"label": SRC_LABELS.get(r["source"], r["source"]),237 "value": r["n"]}238 for r in con.execute(239 "SELECT source, COUNT(*) AS n FROM events WHERE active=1 "240 "GROUP BY source ORDER BY n DESC")]},241 ]242243 # ---------- géographie ----------244 geo = {"title": "Événements à venir par région", "items": [245 {"label": r["region"] or "Non rattachée", "value": r["n"]}246 for r in con.execute(247 f"SELECT region, COUNT(*) AS n FROM events WHERE {_upcoming_where()} "248 "GROUP BY region ORDER BY n DESC LIMIT 17", tp)]}249250 # ---------- heatmap : calendrier des dates d'événements (26 semaines) ----251 heatmap = {"title": "Calendrier des événements — jours les plus chargés",252 "cells": [{"date": (today + timedelta(days=i)).isoformat(),253 "value": horizon_counts[i]}254 for i in range(HEATMAP_DAYS)]}255256 # ---------- tableaux ----------257 city_rows = [[r["city"], r["n"], r["g"],258 f"{100.0 * r['n'] / upcoming:.1f} %".replace(".", ",")]259 for r in con.execute(260 f"SELECT city, COUNT(*) AS n, "261 f"SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) AS g "262 f"FROM events WHERE {_upcoming_where()} AND city != '' "263 "GROUP BY city ORDER BY n DESC LIMIT 40", tp)]264 venue_rows = [[r["venue"], r["city"] or "—", r["n"]]265 for r in con.execute(266 f"SELECT venue, city, COUNT(*) AS n FROM events "267 f"WHERE {_upcoming_where()} AND venue != '' "268 "GROUP BY venue, city ORDER BY n DESC LIMIT 40", tp)]269 starts_by_day = {r[0]: r[1] for r in con.execute(270 "SELECT start_date, COUNT(*) FROM events WHERE active=1 "271 "AND start_date >= ? GROUP BY start_date", (today.isoformat(),))}272 big_days = sorted(273 ((today + timedelta(days=i), horizon_counts[i])274 for i in range(min(BIGDAYS_HORIZON, HEATMAP_DAYS))),275 key=lambda dv: -dv[1])[:15]276 bigday_rows = [[d.isoformat(), n, starts_by_day.get(d.isoformat(), 0)]277 for d, n in big_days]278 tables = [279 {"id": "villes", "title": "Top villes (événements à venir)",280 "columns": ["Ville", "À venir", "Gratuits", "Part"], "rows": city_rows},281 {"id": "lieux", "title": "Top lieux d'événements (à venir)",282 "columns": ["Lieu", "Ville", "À venir"], "rows": venue_rows},283 {"id": "gros_jours",284 "title": f"Prochains gros jours ({BIGDAYS_HORIZON} jours)",285 "columns": ["Date", "Événements ce jour-là", "Débutent ce jour-là"],286 "rows": bigday_rows},287 ]288289 # ---------- records ----------290 records = []291 if horizon_counts:292 i_max = max(range(HEATMAP_DAYS), key=lambda i: horizon_counts[i])293 records.append({"label": "Jour le plus chargé (26 prochaines semaines)",294 "value": f"{horizon_counts[i_max]:,} événements".replace(",", " "),295 "date": (today + timedelta(days=i_max)).isoformat()})296 add_rec = con.execute(297 "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) AS n "298 "FROM events GROUP BY d ORDER BY n DESC LIMIT 1").fetchone()299 if add_rec:300 records.append({"label": "Plus grosse journée d'ajouts",301 "value": f"{add_rec['n']:,} événements".replace(",", " "),302 "date": add_rec["d"]})303 if city_rows:304 records.append({"label": "Ville la plus active (à venir)",305 "value": f"{city_rows[0][0]} — "306 f"{city_rows[0][1]:,} événements".replace(",", " ")})307 if top_cats:308 records.append({"label": "Catégorie dominante (à venir)",309 "value": f"{CAT_LABELS.get(top_cats[0][0], top_cats[0][0])}"310 f" — {top_cats[0][1]:,} événements".replace(",", " ")})311 longest = con.execute(312 f"SELECT title, start_date, end_date, "313 f"julianday(end_date) - julianday(start_date) + 1 AS j "314 f"FROM events WHERE {_upcoming_where()} AND end_date > start_date "315 "ORDER BY j DESC LIMIT 1", tp).fetchone()316 if longest:317 records.append({"label": "Plus longue affiche à venir — "318 f"{longest['title'][:36]}",319 "value": f"{int(longest['j']):,} jours".replace(",", " "),320 "date": longest["start_date"]})321 con.close()322323 payload = {324 "updated": now.isoformat(timespec="seconds"),325 "period": {"from": p_from.isoformat(), "to": p_to.isoformat(),326 "label": p_label},327 "kpis": kpis, "series": series, "breakdowns": breakdowns,328 "geo": geo, "heatmap": heatmap, "tables": tables, "records": records,329 }330 _cache[key] = (time.time(), payload)331 return payload332