spb/sorti-ka

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