# ----------------------------------------------------------------------------- # Lou-Ka — Agrégateur de logements à louer (province de Québec) # Auteur : Simon-Pierre Boucher — contact@spboucher.ai # marketstats.py : agrégats du marché partagés par /api/stats/detailed (page # Statistiques) et par le rapport PDF (pdfgen.rapport_pdf) — une seule # source de vérité pour tous les chiffres. # ----------------------------------------------------------------------------- from __future__ import annotations import json import time from . import db # Régions administratives simplifiées (villes réellement présentes en base) REGIONS: list[tuple[str, set[str]]] = [ ("Québec métro", {"Québec", "Lévis", "Saint-Augustin-de-Desmaures", "L'Ancienne-Lorette", "Pont-Rouge", "Shannon", "Sainte-Brigitte-de-Laval", "Saint-Raphaël", "La Malbaie"}), ("Outaouais", {"Gatineau", "Chelsea", "Thurso", "Perkins", "Maniwaki", "Val-des-Monts"}), ("Estrie / Montérégie-Est", {"Sherbrooke", "Magog", "Orford", "East Angus", "Waterville", "Granby", "Waterloo", "Bromont", "Cowansville", "Richmond"}), ("Mauricie / Centre-du-Québec", {"Trois-Rivières", "Bécancour", "Shawinigan", "Drummondville", "Victoriaville", "Nicolet", "Notre-Dame-du-Bon-Conseil", "Wickham", "Saint-Léonard-d'Aston", "Louiseville", "Saint-Narcisse", "Saint-Nicéphore"}), ("Lanaudière / Laurentides", {"Joliette", "Saint-Jérôme", "Berthierville", "Saint-Ambroise-de-Kildare", "Charlemagne", "Saint-Gabriel-de-Brandon", "Lachute", "Brownsburg-Chatham", "Saint-Charles-Borromée", "Mirabel", "Sainte-Agathe-des-Monts", "Sainte-Thérèse", "Blainville", "Notre-Dame-des-Prairies"}), ("Bas-Saint-Laurent / Gaspésie", {"Rimouski", "Rivière-du-Loup", "Matane", "Saint-Ulric", "Amqui", "Le Bic", "New Richmond", "Carleton-sur-Mer", "Gaspé", "Pointe-au-Père"}), ("Saguenay–Lac-Saint-Jean", {"Saguenay", "Alma", "Chicoutimi", "Jonquière", "Chambord", "La Baie", "Laterrière"}), ("Abitibi-Témiscamingue", {"Rouyn-Noranda", "Val-d'Or", "Amos", "Malartic"}), ("Côte-Nord", {"Sept-Îles", "Port-Cartier", "Baie-Comeau", "Forestville"}), ("Chaudière-Appalaches", {"Saint-Georges", "Sainte-Marie", "Thetford Mines", "Montmagny", "Vallée-Jonction", "Scott", "Saint-Isidore", "La Guadeloupe", "Saint-Joseph-de-Beauce"}), ] def region_for(city: str) -> str: for nom, villes in REGIONS: if city in villes: return nom return "Grand Montréal & environs" def _median(v: list) -> float | None: n = len(v) if n == 0: return None return v[n // 2] if n % 2 else (v[n // 2 - 1] + v[n // 2]) / 2 def compute() -> dict: """Tous les agrégats du marché sur les annonces actives.""" con = db.connect() rows = con.execute( """SELECT uid, city, source, price, unit_type, area_sqft, furnished, pets, availability_date, lat, details FROM listings WHERE active=1""").fetchall() now = time.time() total = len(rows) prix = sorted(r["price"] for r in rows if r["price"] and 300 <= r["price"] <= 10000) # -- groupes simples ------------------------------------------------------ def grouper(cle_fn): g: dict[str, dict] = {} for r in rows: k = cle_fn(r) if not k: continue d = g.setdefault(k, {"count": 0, "prix": [], "sources": set()}) d["count"] += 1 d["sources"].add(r["source"]) if r["price"] and 300 <= r["price"] <= 10000: d["prix"].append(r["price"]) out = [] for k, d in sorted(g.items(), key=lambda kv: -kv[1]["count"]): p = d["prix"] out.append({"key": k, "count": d["count"], "sources": len(d["sources"]), "avg_price": round(sum(p) / len(p)) if p else None, "min_price": min(p) if p else None}) return out by_type = grouper(lambda r: r["unit_type"]) by_city = grouper(lambda r: r["city"]) by_source = grouper(lambda r: r["source"]) by_region = grouper(lambda r: region_for(r["city"] or "")) # -- histogramme des loyers ------------------------------------------------ lo, hi, step = 400, 3200, 200 hist = [{"lo": a, "hi": a + step, "count": 0} for a in range(lo, hi, step)] under = over = 0 for p in prix: if p < lo: under += 1 elif p >= hi: over += 1 else: hist[int((p - lo) // step)]["count"] += 1 if under: hist.insert(0, {"lo": 0, "hi": lo, "count": under}) if over: hist.append({"lo": hi, "hi": None, "count": over}) # -- offre : inclusions, animaux, meublé, dispo, superficie ---------------- def pct(n, d): return round(100 * n / d, 1) if d else None inc_counts = {"heating": 0, "electricity": 0, "hot_water": 0, "internet": 0} ac = parking = balcon = 0 with_details = 0 for r in rows: try: det = json.loads(r["details"] or "{}") except ValueError: det = {} if det: with_details += 1 inc = det.get("inclusions") or {} for k in inc_counts: if inc.get(k): inc_counts[k] += 1 if det.get("ac"): ac += 1 if (det.get("parking") or {}).get("available"): parking += 1 if det.get("balcony"): balcon += 1 pets_vals = [r["pets"] for r in rows if r["pets"]] furn = sum(1 for r in rows if r["furnished"]) dispo_now = sum(1 for r in rows if r["availability_date"] == "now") dispo_date = sum(1 for r in rows if r["availability_date"] and r["availability_date"] != "now") aires = [r["area_sqft"] for r in rows if r["area_sqft"]] # prix au pi² par taille (annonces ayant les deux) pi2: dict[str, list] = {} for r in rows: if (r["price"] and r["area_sqft"] and r["unit_type"] and 300 <= r["price"] <= 10000 and r["area_sqft"] >= 200): pi2.setdefault(r["unit_type"], []).append(r["price"] / r["area_sqft"]) prix_pi2 = [{"key": k, "count": len(v), "val": round(sum(v) / len(v), 2)} for k, v in sorted(pi2.items(), key=lambda kv: -len(kv[1])) if len(v) >= 8][:8] offre = { "furnished_pct": pct(furn, total), "pets_oui_pct": pct(sum(1 for p in pets_vals if p in ("oui", "conditions")), len(pets_vals)), "pets_connu": len(pets_vals), "chauffage_pct": pct(inc_counts["heating"], total), "electricite_pct": pct(inc_counts["electricity"], total), "eau_chaude_pct": pct(inc_counts["hot_water"], total), "internet_pct": pct(inc_counts["internet"], total), "clim_pct": pct(ac, total), "stationnement_pct": pct(parking, total), "balcon_pct": pct(balcon, total), "dispo_now": dispo_now, "dispo_date": dispo_date, "dispo_inconnue": total - dispo_now - dispo_date, "superficie_moyenne": round(sum(aires) / len(aires)) if aires else None, "superficie_connue": len(aires), "prix_pi2": prix_pi2, } # -- baisses de prix récentes (30 jours) ----------------------------------- baisses = [] for r in con.execute( """SELECT p1.uid, l.title, l.city, l.price, p1.price nouveau, p1.ts FROM price_log p1 JOIN listings l ON l.uid = p1.uid AND l.active=1 WHERE p1.ts > ? AND p1.price IS NOT NULL ORDER BY p1.ts DESC LIMIT 400""", (now - 30 * 86400,)).fetchall(): prev = con.execute( "SELECT price FROM price_log WHERE uid=? AND ts?", (now - 86400,)).fetchone()["c"] alertes = [dict(r) for r in con.execute( """SELECT source, message, ts FROM sync_log WHERE ts > ? AND message NOT IN ('ok') ORDER BY ts DESC LIMIT 10""", (now - 86400,)).fetchall()] gps_pct = con.execute( "SELECT ROUND(100.0*SUM(lat IS NOT NULL)/COUNT(*),1) p" " FROM listings WHERE active=1").fetchone()["p"] out = { "totals": { "total": total, "with_price": len(prix), "avg": round(sum(prix) / len(prix)) if prix else None, "median": round(_median(prix)) if prix else None, "min": prix[0] if prix else None, "max": prix[-1] if prix else None, "sources": len(by_source), "cities": len(by_city), "regions": len([r for r in by_region if r["count"] > 0]), "gps_pct": gps_pct, "superficie_moyenne": offre["superficie_moyenne"], "dispo_now": dispo_now, }, "histogram": hist, "by_type": by_type, "by_city": by_city, "by_source": by_source, "by_region": by_region, "offre": offre, "baisses": baisses, "sante": {"sources_sync_24h": sync24, "alertes_24h": alertes}, } con.close() return out