spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# building.py : « Passeport de l'immeuble » — entité persistante regroupant les5#               annonces (actives ET historiques) d'un même bâtiment.6#7# Clé d'immeuble : la clé d'adresse civique de dedup._parse_address8# (« civique|rue|ville », préfixe « adr: ») — la même logique éprouvée que la9# déduplication inter-sources ; repli « geo:coord_key » (~11 m) quand l'adresse10# n'a pas de numéro civique fiable mais que l'annonce est géocodée.11#12# Tout indicateur est calculé UNIQUEMENT sur ce que Lou-Ka observe réellement13# (annonces collectées par les crawls). Un indicateur sans échantillon14# suffisant est omis ou marqué « donnees_insuffisantes » — jamais inventé.15# -----------------------------------------------------------------------------16from __future__ import annotations1718import json19import statistics20import time2122from . import db23from .dedup import CLASSIFIEDS, PORTALS, _parse_address2425VERSION = "immeuble-1.0"2627# seuils du score de rotation (annonces des 12 derniers mois / unités estimées)28ROTATION = [(0.5, "faible"), (1.0, "normale"), (2.0, "élevée")]29ROTATION_MAX = "très élevée"30MIN_OBS_DAYS = 180      # fenêtre d'observation minimale pour classer la rotation31MIN_ADS_ROTATION = 3    # nb d'annonces minimal pour classer la rotation32MIN_ADS_PRESSURE = 3    # nb minimal par fenêtre pour la pression sur les loyers333435def building_key(address: str | None, city: str | None,36                 coord_key: str | None) -> tuple[str | None, str | None]:37    """(clé d'immeuble, numéro d'unité) — adresse d'abord, géo en repli."""38    akey, unit = _parse_address(address or "", city or "")39    if akey:40        return f"adr:{akey}", unit41    if coord_key:42        return f"geo:{coord_key}", unit43    return None, unit444546def _median(vals: list[float]) -> float | None:47    vals = [v for v in vals if v]48    return round(statistics.median(vals)) if vals else None495051def _stats_for(members: list[dict], now: float) -> dict:52    """Statistiques d'un immeuble à partir de ses annonces (obs. Lou-Ka)."""53    d30, d90, d365, d730 = (now - 30 * 86400, now - 90 * 86400,54                            now - 365 * 86400, now - 730 * 86400)55    actives = [m for m in members if m["active"]]56    first_obs = min(m["first_seen"] for m in members if m["first_seen"])57    last_obs = max(m["last_seen"] for m in members if m["last_seen"])58    obs_days = max(1, round((now - first_obs) / 86400))5960    units = {m["unit"] for m in members if m["unit"]}61    # unités estimées : numéros d'app distincts observés, plancher = annonces62    # actives en ce moment (elles existent forcément toutes en même temps)63    unites_estimees = max(len(units), len(actives), 1)6465    n_12m = sum(1 for m in members if (m["first_seen"] or 0) >= d365)6667    # loyers médians (annonces actives)68    med = _median([m["price"] for m in actives])69    par_cc: dict[str, int] = {}70    by_cc: dict[str, list[float]] = {}71    for m in actives:72        if m["price"] and m["bedrooms"] is not None:73            by_cc.setdefault(str(int(m["bedrooms"])), []).append(m["price"])74    for cc, vals in sorted(by_cc.items()):75        v = _median(vals)76        if v and len(vals) >= 2:77            par_cc[cc] = v78    vals_pi2 = [m["price"] / m["area"] for m in actives79                if m["price"] and m["area"] and m["area"] > 100]80    pi2 = round(statistics.median(vals_pi2), 2) if vals_pi2 else None8182    # pression sur les loyers : médiane des PREMIERS prix observés des annonces83    # apparues dans les 12 derniers mois vs celles des 12 mois précédents84    win_new = [m["first_price"] for m in members85               if m["first_price"] and (m["first_seen"] or 0) >= d365]86    win_old = [m["first_price"] for m in members87               if m["first_price"] and d730 <= (m["first_seen"] or 0) < d365]88    pression = None89    if len(win_new) >= MIN_ADS_PRESSURE and len(win_old) >= MIN_ADS_PRESSURE:90        m_new, m_old = statistics.median(win_new), statistics.median(win_old)91        if m_old > 0:92            pression = {93                "variation_12m": round((m_new - m_old) / m_old, 4),94                "mediane_12m": round(m_new), "mediane_12_24m": round(m_old),95                "n_12m": len(win_new), "n_12_24m": len(win_old),96                "statut": "calculated",97            }9899    # score de rotation : annonces des 12 derniers mois / unités estimées100    rotation: dict = {"statut": "donnees_insuffisantes"}101    if obs_days >= MIN_OBS_DAYS and len(members) >= MIN_ADS_ROTATION:102        ratio = n_12m / unites_estimees103        classe = ROTATION_MAX104        for seuil, nom in ROTATION:105            if ratio < seuil:106                classe = nom107                break108        rotation = {109            "classe": classe, "ratio": round(ratio, 2),110            "annonces_12m": n_12m, "unites_estimees": unites_estimees,111            "observation_jours": obs_days, "statut": "calculated",112            "methode": ("annonces observées les 12 derniers mois ÷ unités "113                        "estimées (numéros d'app distincts, plancher = annonces "114                        "actives simultanées). Mesure uniquement ce que Lou-Ka "115                        "observe — pas un taux de roulement démographique."),116        }117118    sources: dict[str, int] = {}119    for m in members:120        sources[m["source"]] = sources.get(m["source"], 0) + 1121    gestionnaires = sorted(s for s in sources122                           if s not in PORTALS and s not in CLASSIFIEDS)123124    return {125        "annonces_total": len(members),126        "annonces_actives": len(actives),127        "annonces_30j": sum(1 for m in members if (m["first_seen"] or 0) >= d30),128        "annonces_90j": sum(1 for m in members if (m["first_seen"] or 0) >= d90),129        "annonces_12m": n_12m,130        "unites_identifiees": len(units),131        "unites_estimees": unites_estimees,132        "loyer_median": med,133        "loyer_median_par_cc": par_cc or None,134        "pi2_median": pi2,135        "pression_loyers": pression,136        "rotation": rotation,137        "gestionnaires": gestionnaires,138        "sources": sources,139        "premiere_observation": first_obs,140        "derniere_observation": last_obs,141        "unites_actives": sorted(142            [{"uid": m["uid"], "unit_type": m["unit_type"],143              "price": m["price"], "bedrooms": m["bedrooms"]}144             for m in actives if not m["dup_of"]],145            key=lambda x: (x["price"] is None, x["price"] or 0))[:24],146    }147148149def rollup(con=None) -> dict:150    """Recalcule building_key de toutes les annonces + la table buildings.151152    Idempotent, recalcul complet (même philosophie que dedup.run) : les153    annonces INACTIVES participent — l'historique de l'immeuble est justement154    ce qui fait la valeur du passeport.155    """156    own = con is None157    if own:158        con = db.connect()159    now = time.time()160    rows = con.execute(161        "SELECT uid, source, address, city, coord_key, unit_type, bedrooms,"162        " price, area_sqft, first_seen, last_seen, active, dup_of, building_key"163        " FROM listings").fetchall()164165    # premier prix observé par annonce (pression sur les loyers)166    firstp = {r["uid"]: r["p"] for r in con.execute(167        "SELECT uid, (SELECT price FROM price_log p2 WHERE p2.uid=p1.uid"168        "  AND p2.price IS NOT NULL ORDER BY ts LIMIT 1) p"169        " FROM (SELECT DISTINCT uid FROM price_log) p1")}170171    groups: dict[str, list[dict]] = {}172    key_updates: list[tuple[str | None, str]] = []173    for r in rows:174        bkey, unit = building_key(r["address"], r["city"], r["coord_key"])175        if bkey != r["building_key"]:176            key_updates.append((bkey, r["uid"]))177        if not bkey:178            continue179        groups.setdefault(bkey, []).append({180            "uid": r["uid"], "source": r["source"], "unit": unit,181            "unit_type": r["unit_type"], "bedrooms": r["bedrooms"],182            "price": r["price"], "area": r["area_sqft"],183            "first_seen": r["first_seen"], "last_seen": r["last_seen"],184            "active": r["active"], "dup_of": r["dup_of"],185            "first_price": firstp.get(r["uid"]) or r["price"],186            "address": r["address"], "city": r["city"],187        })188    if key_updates:189        con.executemany("UPDATE listings SET building_key=? WHERE uid=?",190                        key_updates)191192    upserts = []193    for bkey, members in groups.items():194        # doublons inter-sources : un même logement publié sur 3 plateformes ne195        # compte qu'une fois dans les statistiques de l'immeuble196        uniq = [m for m in members if not m["dup_of"]]197        if not uniq:198            continue199        stats = _stats_for(uniq, now)200        # adresse d'affichage : la plus fréquente parmi les membres actifs201        addrs = [m["address"] for m in uniq if m["address"]]202        addr = max(set(addrs), key=addrs.count) if addrs else None203        cities = [m["city"] for m in uniq if m["city"]]204        city = max(set(cities), key=cities.count) if cities else None205        # position : coord de n'importe quel membre géocodé (via coord_key)206        upserts.append((bkey, addr, city,207                        json.dumps(stats, ensure_ascii=False), VERSION, now))208    con.executemany(209        "INSERT INTO buildings (bkey, address, city, stats, version, computed_at)"210        " VALUES (?,?,?,?,?,?)"211        " ON CONFLICT(bkey) DO UPDATE SET address=excluded.address,"212        " city=excluded.city, stats=excluded.stats, version=excluded.version,"213        " computed_at=excluded.computed_at", upserts)214    con.commit()215    out = {"buildings": len(upserts), "listings_scanned": len(rows),216           "keys_updated": len(key_updates)}217    if own:218        con.close()219    return out220221222def fiche(bkey: str, con=None) -> dict | None:223    """Passeport d'un immeuble pour l'API (stats précalculées par rollup)."""224    own = con is None225    if own:226        con = db.connect()227    row = con.execute(228        "SELECT bkey, address, city, stats, version, computed_at FROM buildings"229        " WHERE bkey=?", (bkey,)).fetchone()230    if own and row is None:231        con.close()232        return None233    if row is None:234        return None235    out = {"bkey": row["bkey"], "address": row["address"], "city": row["city"],236           "version": row["version"], "computed_at": row["computed_at"],237           **json.loads(row["stats"] or "{}")}238    if own:239        con.close()240    return out241242243if __name__ == "__main__":244    print(rollup())245