spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# statsextra.py : panneaux « données du territoire » de l'onglet Statistiques5#   Trois blocs indépendants du marché des annonces, calculés sur nos jeux de6#   données ouverts et mis en cache :7#     · Registre des loyers (loyers réellement déclarés, data/rdl.db) ;8#     · Population & territoire (recensement des secteurs couverts,9#       quartier.db.da_stats joint aux annonces par aire de diffusion) ;10#     · Prix de l'essence (stations gazquebec.ca, data/gaz.db).11#   Chaque panneau réutilise les rendus existants du kit stats (kpis, donut/12#   barres, histogramme, tableau) — aucune donnée inventée : un bloc absent si13#   sa source ne répond pas.14# -----------------------------------------------------------------------------15from __future__ import annotations1617import sqlite318import time19from pathlib import Path20from statistics import median2122ROOT = Path(__file__).resolve().parent.parent23DATA = ROOT / "data"24_CACHE: dict[str, tuple[float, list]] = {}25_TTL = 1800262728def _ro(name: str) -> sqlite3.Connection | None:29    p = DATA / name30    if not p.exists():31        return None32    con = sqlite3.connect(f"file:{p}?mode=ro", uri=True)33    con.row_factory = sqlite3.Row34    return con353637def _money(v) -> str:38    return f"{round(v):,}".replace(",", " ") + " $" if v is not None else "—"394041def _q(vals: list[float], f: float) -> float:42    s = sorted(vals)43    if not s:44        return 045    i = f * (len(s) - 1)46    lo = int(i)47    hi = min(lo + 1, len(s) - 1)48    return s[lo] + (s[hi] - s[lo]) * (i - lo)495051# --- Panneau 1 : Registre des loyers -----------------------------------------52def _panel_rdl() -> dict | None:53    con = _ro("rdl.db")54    if con is None:55        return None56    try:57        rows = con.execute(58            "SELECT price, rooms, year, city FROM rdl_housings "59            "WHERE price > 100 AND price < 20000").fetchall()60    except sqlite3.Error:61        con.close()62        return None63    con.close()64    if len(rows) < 100:65        return None66    prices = [r["price"] for r in rows]67    recent = [r["price"] for r in rows if (r["year"] or 0) >= 2023]68    villes = {r["city"] for r in rows if r["city"]}6970    kpis = [71        {"id": "rdl_n", "label": "Loyers déclarés", "value": len(rows)},72        {"id": "rdl_med", "label": "Loyer médian déclaré",73         "value": round(median(prices)), "unit": "$"},74        {"id": "rdl_rec", "label": "Médiane depuis 2023",75         "value": round(median(recent)) if recent else None, "unit": "$"},76        {"id": "rdl_villes", "label": "Villes représentées", "value": len(villes)},77    ]7879    # médiane par nombre de chambres (barres)80    byr: dict[int, list[float]] = {}81    for r in rows:82        if r["rooms"] is not None and 0 <= r["rooms"] <= 6:83            byr.setdefault(r["rooms"], []).append(r["price"])84    bars = [{"label": f"{k} ch.", "value": round(median(v))}85            for k, v in sorted(byr.items()) if len(v) >= 10]8687    # histogramme des loyers déclarés (récents de préférence)88    base = recent if len(recent) >= 500 else prices89    lo, hi = round(_q(base, 0.02)), round(_q(base, 0.98))90    step = max(50, round((hi - lo) / 14 / 50) * 50)91    bins = []92    x = lo93    while x < hi:94        n = sum(1 for p in base if x <= p < x + step)95        bins.append({"label": f"{x}", "value": n})96        x += step9798    # top villes par nombre de déclarations99    vcount: dict[str, list[float]] = {}100    for r in rows:101        if r["city"]:102            vcount.setdefault(r["city"], []).append(r["price"])103    top = sorted(vcount.items(), key=lambda kv: -len(kv[1]))[:15]104    table = {"id": "rdl_villes_t",105             "title": "Loyers déclarés par ville (top 15)",106             "columns": ["Ville", "Déclarations", "Loyer médian"],107             "rows": [[v, len(p), _money(median(p))] for v, p in top]}108109    return {110        "id": "registre_loyers",111        "title": "Registre des loyers — loyers réellement payés",112        "subtitle": "Loyers déclarés volontairement par des locataires au "113                    "Registre des loyers (Vivre en ville). Données citoyennes, "114                    "indicatives.",115        "kpis": [k for k in kpis if k.get("value") is not None],116        "breakdowns": [{"id": "rdl_rooms", "title": "Loyer médian déclaré par "117                        "nombre de chambres", "kind": "bar", "items": bars}]118                      if bars else [],119        "distributions": [{"id": "rdl_hist",120                           "title": "Distribution des loyers déclarés"121                           + (" (depuis 2023)" if base is recent else ""),122                           "unit": "$", "bins": bins}] if len(bins) >= 4 else [],123        "tables": [table],124    }125126127# --- Panneau 2 : Population & territoire --------------------------------------128def _active_where(con: sqlite3.Connection) -> str:129    """Filtre « annonces actives » adapté au schéma (lou-ka a dup_of, pas immo)."""130    cols = {r[1] for r in con.execute("PRAGMA table_info(listings)")}131    w = "active=1" if "active" in cols else "1=1"132    if "dup_of" in cols:133        w += " AND dup_of IS NULL"134    return w135136137def _panel_population(con: sqlite3.Connection) -> dict | None:138    qc = _ro("quartier.db")139    if qc is None:140        return None141    aw = _active_where(con)142    try:143        # aires de diffusion couvertes par au moins une annonce active144        dauids = [r[0] for r in con.execute(145            f"SELECT DISTINCT dauid FROM listings WHERE {aw} "146            "AND dauid IS NOT NULL AND dauid<>''")]147    except sqlite3.Error:148        dauids = []149    if len(dauids) < 20:150        qc.close()151        return None152    # charger le recensement des DA couvertes153    rows = []154    CH = 400155    for i in range(0, len(dauids), CH):156        chunk = dauids[i:i + CH]157        ph = ",".join("?" * len(chunk))158        rows += qc.execute(159            f"SELECT * FROM da_stats WHERE dauid IN ({ph})", chunk).fetchall()160    qc.close()161    if not rows:162        return None163164    def med(col):165        v = [r[col] for r in rows if r[col] is not None]166        return median(v) if v else None167168    pop_tot = sum(r["population"] or 0 for r in rows)169    kpis = [170        {"id": "pop_tot", "label": "Population des secteurs couverts",171         "value": round(pop_tot)},172        {"id": "pop_rev", "label": "Revenu médian des ménages (médiane)",173         "value": round(med("revenu_median")) if med("revenu_median") else None,174         "unit": "$"},175        {"id": "pop_loc", "label": "Part de locataires (médiane)",176         "value": round(med("pct_locataires")) if med("pct_locataires") is not None else None,177         "unit": "%"},178        {"id": "pop_age", "label": "Âge médian (médiane des secteurs)",179         "value": round(med("age_median")) if med("age_median") else None,180         "unit": "ans"},181        {"id": "pop_univ", "label": "Diplôme universitaire (médiane)",182         "value": round(med("pct_univ")) if med("pct_univ") is not None else None,183         "unit": "%"},184    ]185186    # répartition de la population par quintile de défavorisation matérielle187    defav = _ro("quartier.db")188    donut_items = []189    try:190        dmap = {r["dauid"]: r["quintile_materiel"] for r in defav.execute(191            "SELECT dauid, quintile_materiel FROM da_defav")}192        buckets: dict[int, float] = {}193        for r in rows:194            qv = dmap.get(r["dauid"])195            if qv:196                buckets[qv] = buckets.get(qv, 0) + (r["population"] or 0)197        labels = {1: "Très favorisé", 2: "Favorisé", 3: "Moyen",198                  4: "Défavorisé", 5: "Très défavorisé"}199        donut_items = [{"label": labels.get(k, str(k)), "value": round(v)}200                       for k, v in sorted(buckets.items())]201    except sqlite3.Error:202        pass203    finally:204        if defav:205            defav.close()206207    # tableau par ville : population, revenu médian, % locataires208    qc2 = _ro("quartier.db")209    table = None210    try:211        # villes avec le plus d'annonces actives212        top_cities = [r["city"] for r in con.execute(213            f"SELECT city, COUNT(*) n FROM listings WHERE {aw} "214            "AND city<>'' GROUP BY city ORDER BY n DESC LIMIT 15")]215        st = {r["dauid"]: r for r in qc2.execute("SELECT * FROM da_stats")}216        trows = []217        for city in top_cities:218            das = [r["dauid"] for r in con.execute(219                f"SELECT DISTINCT dauid FROM listings WHERE {aw} "220                "AND city=? AND dauid IS NOT NULL", (city,))]221            recs = [st[d] for d in das if d in st]222            if len(recs) < 3:223                continue224            pop = sum(x["population"] or 0 for x in recs)225            rev = [x["revenu_median"] for x in recs if x["revenu_median"]]226            loc = [x["pct_locataires"] for x in recs227                   if x["pct_locataires"] is not None]228            trows.append([city, f"{round(pop):,}".replace(",", " "),229                          _money(median(rev)) if rev else "—",230                          f"{round(median(loc))} %" if loc else "—"])231        if trows:232            table = {"id": "pop_villes",233                     "title": "Profil des secteurs couverts par ville",234                     "columns": ["Ville", "Population", "Revenu médian",235                                 "Locataires"],236                     "rows": trows}237    except sqlite3.Error:238        pass239    finally:240        qc2.close()241242    return {243        "id": "population",244        "title": "Population & territoire",245        "subtitle": "Portrait sociodémographique des secteurs (aires de "246                    "diffusion) où se trouvent les annonces — recensement.",247        "kpis": [k for k in kpis if k.get("value") is not None],248        "breakdowns": [{"id": "pop_defav",249                        "title": "Population par niveau de défavorisation "250                        "matérielle", "kind": "donut", "items": donut_items}]251                      if donut_items else [],252        "tables": [table] if table else [],253    }254255256# --- Panneau 3 : Prix de l'essence -------------------------------------------257def _panel_gaz() -> dict | None:258    try:259        from . import gaz260        gcon = gaz._connect()261        row = gcon.execute("SELECT v FROM meta WHERE k='maj'").fetchone()262        if row is None or time.time() - float(row["v"]) > gaz.TTL:263            try:264                gaz.refresh(gcon)265            except Exception:266                pass267        rows = gcon.execute(268            "SELECT region, prix_regulier, prix_super, prix_diesel "269            "FROM gaz_stations WHERE prix_regulier > 50").fetchall()270        gcon.close()271    except Exception:272        return None273    if len(rows) < 30:274        return None275    reg = [r["prix_regulier"] for r in rows]276    sup = [r["prix_super"] for r in rows if r["prix_super"]]277    die = [r["prix_diesel"] for r in rows if r["prix_diesel"]]278279    def c(v):280        return round(v, 1) if v is not None else None281282    kpis = [283        {"id": "gaz_n", "label": "Stations suivies", "value": len(rows)},284        {"id": "gaz_reg", "label": "Régulier médian",285         "value": c(median(reg)), "unit": "¢/L"},286        {"id": "gaz_min", "label": "Meilleur prix (régulier)",287         "value": c(min(reg)), "unit": "¢/L"},288        {"id": "gaz_sup", "label": "Super médian",289         "value": c(median(sup)) if sup else None, "unit": "¢/L"},290        {"id": "gaz_die", "label": "Diesel médian",291         "value": c(median(die)) if die else None, "unit": "¢/L"},292    ]293294    # prix régulier médian par région295    byreg: dict[str, list[float]] = {}296    for r in rows:297        if r["region"]:298            byreg.setdefault(r["region"], []).append(r["prix_regulier"])299    bars = sorted(300        ([{"label": k, "value": round(median(v), 1)}301          for k, v in byreg.items() if len(v) >= 5]),302        key=lambda x: x["value"])303    # tableau régions304    treg = sorted(([k, len(v), round(median(v), 1)]305                   for k, v in byreg.items() if len(v) >= 5),306                  key=lambda x: x[2])307    table = {"id": "gaz_regions",308             "title": "Prix de l'essence régulière par région",309             "columns": ["Région", "Stations", "Régulier médian (¢/L)"],310             "rows": [[r[0], r[1], f"{r[2]:.1f}"] for r in treg]}311312    return {313        "id": "essence",314        "title": "Prix de l'essence au Québec",315        "subtitle": "Prix courants des stations-service (gazquebec.ca), toutes "316                    "régions confondues.",317        "kpis": [k for k in kpis if k.get("value") is not None],318        "breakdowns": [{"id": "gaz_reg_bar",319                        "title": "Essence régulière — médiane par région (¢/L)",320                        "kind": "bar", "items": bars}] if bars else [],321        "tables": [table],322    }323324325def panels(con: sqlite3.Connection) -> list[dict]:326    """Retourne les panneaux « territoire » (cache 30 min). `con` = base des327    annonces (pour la jointure recensement via l'aire de diffusion)."""328    hit = _CACHE.get("panels")329    if hit and time.time() - hit[0] < _TTL:330        return hit[1]331    out = []332    for fn in (_panel_rdl, lambda: _panel_population(con), _panel_gaz):333        try:334            p = fn()335            if p and (p.get("kpis") or p.get("tables") or p.get("breakdowns")):336                out.append(p)337        except Exception:338            continue339    _CACHE["panels"] = (time.time(), out)340    return out341