Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 98.9%
Python 0.6%
1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# managers.py : « Qui gère ce logement? » — fiche du gestionnaire/propriétaire.5#6# 1) Amorçage : les gestionnaires sont les sources DIRECTES de Lou-Ka7# (data/sources.json, hors portails et petites annonces) qui ont au moins8# une annonce collectée. Aucun gestionnaire n'est inventé.9# 2) Résolution Google Maps (SerpApi, serveur seulement) : la fiche Google10# n'est associée QUE si la confiance est suffisante (similarité de nom,11# domaine du site web, ville) — jamais « le premier résultat » aveuglément.12# Signaux et confiance stockés (match_confidence, match_signals).13# 3) Avis Google : synchronisation paginée (next_page_token, hl=fr, plus14# récents d'abord), dédupliqués par (manager_id, review_id Google) avec15# repli sur un hash de contenu. Les avis vivent dans NOTRE base : les pages16# ne déclenchent jamais d'appel SerpApi.17# 4) Analyse : statistiques réelles (distribution, récence, tendance — pas18# seulement la moyenne Google) + thèmes par lexique français, sentiment19# dérivé de la note, sévérité et confiance. Statut « inferred » assumé.20# -----------------------------------------------------------------------------21from __future__ import annotations2223import difflib24import hashlib25import json26import re27import time28import unicodedata29from datetime import datetime, timezone3031from . import db, serpapi_client32from .dedup import CLASSIFIEDS, PORTALS3334VERSION = "gestionnaire-1.0"35SEUIL_CONFIANCE = 0.60 # sous ce seuil : fiche Google NON associée36MAX_PAGES = 10 # garde-fou pagination avis (par synchronisation)37SYNC_TTL = 7 * 86400 # re-synchroniser les avis au plus aux 7 jours38RESOLVE_RETRY = 30 * 86400 # re-tenter une résolution échouée après 30 jours3940# suffixes/génériques retirés pour comparer les noms d'entreprises41_LEGAL = re.compile(42 r"\b(inc|ltee|ltée|ltd|senc|s\.e\.n\.c|enr|corp|corporation|groupe|group|"43 r"gestion|gestions|immobilier|immobiliere|immobilière|immeubles?|"44 r"appartements?|apartments?|properties|proprietes|propriétés|residences?|"45 r"résidences?|habitations?|siege|social|bureau|head|office|"46 r"les|le|la|de|du|des|et|and)\b")4748# types Google d'une fiche d'ENTREPRISE (vs fiche d'un immeuble précis)49_CORP_TYPES = re.compile(50 r"agence immobili|gestion immobili|property management|real estate|"51 r"societe de gestion|immobilier", re.I)525354def _norm_name(s: str | None) -> str:55 s = unicodedata.normalize("NFKD", (s or "").lower())56 s = "".join(c for c in s if not unicodedata.combining(c))57 s = re.sub(r"[^a-z0-9 ]+", " ", s)58 s = _LEGAL.sub(" ", s)59 return " ".join(s.split())606162def _domain(url: str | None) -> str | None:63 if not url:64 return None65 m = re.search(r"^(?:https?://)?(?:www\.)?([^/:?#]+)", url.strip().lower())66 return m.group(1) if m else None676869# --- 1) amorçage depuis sources.json ----------------------------------------7071def seed(con=None) -> dict:72 """Crée/actualise un gestionnaire par source directe ayant des annonces."""73 own = con is None74 if own:75 con = db.connect()76 try:77 sources = json.loads((db.DB_PATH.parent / "sources.json")78 .read_text("utf-8"))["sources"]79 with_ads = {r["source"] for r in con.execute(80 "SELECT DISTINCT source FROM listings")}81 n = 082 for s in sources:83 sid = s.get("id")84 if not sid or sid in PORTALS or sid in CLASSIFIEDS:85 continue86 if sid not in with_ads and s.get("connector") not in with_ads:87 continue88 con.execute(89 "INSERT INTO managers (source_id, name, website, aliases)"90 " VALUES (?,?,?,?)"91 " ON CONFLICT(source_id) DO UPDATE SET name=excluded.name,"92 " website=excluded.website",93 (sid, s.get("name"), s.get("url"),94 json.dumps({"region": s.get("region"),95 "connector": s.get("connector")},96 ensure_ascii=False)))97 n += 198 con.commit()99 return {"managers": n}100 finally:101 if own:102 con.close()103104105# --- 2) résolution Google Maps (jamais « le premier résultat ») --------------106107def _score_candidate(mgr: dict, cand: dict) -> tuple[float, dict]:108 signals: dict = {}109 sim = difflib.SequenceMatcher(110 None, _norm_name(mgr["name"]), _norm_name(cand.get("title"))).ratio()111 signals["similarite_nom"] = round(sim, 3)112 conf = 0.6 * sim113 d_mgr, d_cand = _domain(mgr.get("website")), _domain(cand.get("website"))114 if d_mgr and d_cand and d_mgr == d_cand:115 conf += 0.3116 signals["meme_domaine_web"] = d_mgr117 region = None118 try:119 region = (json.loads(mgr.get("aliases") or "{}") or {}).get("region")120 except (ValueError, TypeError):121 pass122 addr = (cand.get("address") or "").lower()123 if region and _norm_name(region) and _norm_name(region) in _norm_name(addr):124 conf += 0.1125 signals["ville_correspondante"] = region126 if _CORP_TYPES.search(cand.get("type") or ""):127 signals["fiche_entreprise"] = cand.get("type")128 signals["type_fiche"] = cand.get("type")129 signals["nombre_avis"] = cand.get("reviews")130 return min(1.0, conf), signals131132133def resolve(manager_id: int, con=None) -> dict:134 """Associe (ou refuse d'associer) la fiche Google Maps du gestionnaire."""135 own = con is None136 if own:137 con = db.connect()138 try:139 mgr = con.execute("SELECT * FROM managers WHERE id=?",140 (manager_id,)).fetchone()141 if mgr is None:142 return {"ok": False, "raison": "gestionnaire inconnu"}143 mgr = dict(mgr)144 region = ""145 try:146 region = (json.loads(mgr.get("aliases") or "{}") or {}).get(147 "region") or ""148 except (ValueError, TypeError):149 pass150 q = f"{mgr['name']} {region} Québec".strip()151 data = serpapi_client.google_maps(q)152 cands = data.get("local_results") or []153 if not cands and data.get("place_results"):154 cands = [data["place_results"]]155 for c in cands:156 # SerpApi renvoie parfois `type` en liste (multi-catégories)157 if isinstance(c.get("type"), list):158 c["type"] = ", ".join(str(t) for t in c["type"])159160 scored = [( *_score_candidate(mgr, c), c) for c in cands[:8]]161 best, best_conf, best_sig = None, 0.0, {}162 if scored:163 top = max(s[0] for s in scored)164 # une chaîne a souvent une fiche Google PAR IMMEUBLE en plus du165 # siège : parmi les candidats plausibles (confiance proche du166 # meilleur ET au-dessus du seuil), préférer la fiche d'entreprise167 # puis la plus représentative (le plus d'avis)168 near = [s for s in scored169 if s[0] >= max(SEUIL_CONFIANCE, top - 0.15)]170 if near:171 best_conf, best_sig, best = max(172 near, key=lambda s: (bool(_CORP_TYPES.search(173 s[2].get("type") or "")),174 s[2].get("reviews") or 0, s[0]))175 else:176 best_conf, best_sig, best = max(scored, key=lambda s: s[0])177 now = time.time()178 if best is None or best_conf < SEUIL_CONFIANCE:179 con.execute(180 "UPDATE managers SET resolve_failed=1, resolved_at=?,"181 " match_confidence=?, match_signals=? WHERE id=?",182 (now, round(best_conf, 3) if best else None,183 json.dumps({"refus": "confiance insuffisante",184 "candidats": len(cands), **best_sig},185 ensure_ascii=False), manager_id))186 con.commit()187 return {"ok": False, "raison": "confiance insuffisante",188 "confiance": round(best_conf, 3)}189 con.execute(190 "UPDATE managers SET gmaps_place_id=?, gmaps_data_id=?,"191 " gmaps_name=?, gmaps_address=?, gmaps_rating=?, gmaps_reviews=?,"192 " match_confidence=?, match_signals=?, resolved_at=?,"193 " resolve_failed=0, phone=COALESCE(phone, ?) WHERE id=?",194 (best.get("place_id"), best.get("data_id"), best.get("title"),195 best.get("address"), best.get("rating"), best.get("reviews"),196 round(best_conf, 3), json.dumps(best_sig, ensure_ascii=False),197 now, best.get("phone"), manager_id))198 con.commit()199 return {"ok": True, "gmaps_name": best.get("title"),200 "confiance": round(best_conf, 3)}201 finally:202 if own:203 con.close()204205206# --- 3) synchronisation des avis (paginée, dédupliquée, en base) -------------207208def _content_hash(author: str | None, rating, text: str | None) -> str:209 raw = f"{author or ''}|{rating}|{(text or '')[:200]}"210 return hashlib.sha1(raw.encode("utf-8")).hexdigest()211212213def sync_reviews(manager_id: int, con=None, max_pages: int = MAX_PAGES) -> dict:214 """Rapatrie les avis Google (les plus récents d'abord) dans NOTRE base."""215 own = con is None216 if own:217 con = db.connect()218 try:219 mgr = con.execute("SELECT * FROM managers WHERE id=?",220 (manager_id,)).fetchone()221 if mgr is None or not mgr["gmaps_data_id"]:222 return {"ok": False, "raison": "fiche Google non associée"}223 known = {r["external_review_id"] for r in con.execute(224 "SELECT external_review_id FROM manager_reviews WHERE manager_id=?",225 (manager_id,))}226 known_hash = {r["content_hash"] for r in con.execute(227 "SELECT content_hash FROM manager_reviews WHERE manager_id=?"228 " AND external_review_id IS NULL", (manager_id,))}229230 token, new, pages = None, 0, 0231 while pages < max_pages:232 data = serpapi_client.google_maps_reviews(233 mgr["gmaps_data_id"], next_page_token=token)234 reviews = data.get("reviews") or []235 pages += 1236 page_new = 0237 now = time.time()238 for rv in reviews:239 ext = rv.get("review_id")240 text = rv.get("snippet") or rv.get("extracted_snippet",241 {}).get("original")242 user = rv.get("user") or {}243 chash = _content_hash(user.get("name"), rv.get("rating"), text)244 if (ext and ext in known) or (not ext and chash in known_hash):245 continue246 resp = rv.get("response") or {}247 analysis = _analyze_review(rv.get("rating"), text)248 con.execute(249 "INSERT INTO manager_reviews (manager_id,"250 " external_review_id, source, rating, text, published_at,"251 " relative_date_raw, author_name, author_review_count,"252 " owner_response, owner_response_date, source_url,"253 " fetched_at, content_hash, analysis) VALUES"254 " (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"255 " ON CONFLICT(manager_id, external_review_id) DO NOTHING",256 (manager_id, ext, "google_maps", rv.get("rating"), text,257 rv.get("iso_date"), rv.get("date"), user.get("name"),258 user.get("reviews"), resp.get("snippet"),259 resp.get("iso_date") or resp.get("date"), rv.get("link"),260 now, chash, json.dumps(analysis, ensure_ascii=False)))261 if ext:262 known.add(ext)263 else:264 known_hash.add(chash)265 page_new += 1266 new += page_new267 token = (data.get("serpapi_pagination") or {}).get(268 "next_page_token")269 # tri « plus récents d'abord » : une page entièrement connue270 # signifie qu'on a rejoint l'historique déjà synchronisé271 if not token or (reviews and page_new == 0):272 break273 stats = _aggregate(con, manager_id)274 con.execute("UPDATE managers SET review_stats=?, last_synced_at=?"275 " WHERE id=?",276 (json.dumps(stats, ensure_ascii=False), time.time(),277 manager_id))278 con.commit()279 return {"ok": True, "nouveaux": new, "pages": pages,280 "total": stats.get("n")}281 finally:282 if own:283 con.close()284285286# --- 4) analyse : thèmes (lexique fr), sentiment, sévérité, agrégats ---------287288TOPICS = {289 "entretien": ["entretien", "entreten", "maintenance", "neglige", "négligé",290 "delabre", "délabré", "moisissure"],291 "réparations": ["reparation", "réparation", "reparer", "réparer", "bris",292 "brise", "brisé", "fuite", "degat", "dégât", "plomberie"],293 "communication": ["communication", "repond", "répond", "reponse",294 "réponse", "rappel", "courriel", "joindre",295 "injoignable", "appel", "message"],296 "personnel": ["personnel", "employe", "employé", "concierge", "equipe",297 "équipe", "gerant", "gérant", "agent", "proprietaire",298 "propriétaire"],299 "propreté": ["propre", "proprete", "propreté", "sale", "salete", "saleté",300 "malpropre"],301 "chauffage": ["chauffage", "chauffe", "chauffé", "froid", "temperature",302 "température"],303 "eau chaude": ["eau chaude"],304 "vermine": ["punaise", "coquerelle", "souris", "rat ", "rats", "insecte",305 "vermine", "fourmi", "extermina"],306 "bruit": ["bruit", "bruyant", "tapage", "insonoris"],307 "sécurité": ["securite", "sécurité", "securitaire", "sécuritaire", "vol",308 "intrusion", "serrure", "camera", "caméra"],309 "ascenseur": ["ascenseur"],310 "stationnement": ["stationnement", "parking", "deneigement",311 "déneigement"],312 "dépôt et remboursement": ["depot", "dépôt", "rembours", "caution"],313 "augmentation de loyer": ["augmentation", "hausse"],314 "bail et location": ["bail", "signature", "visite", "location"],315 "service client": ["service", "clientele", "clientèle", "professionnel",316 "courtois", "arrogant", "impoli", "respect"],317}318_GRAVES = {"vermine", "sécurité", "chauffage", "eau chaude"}319320321def _analyze_review(rating, text: str | None) -> dict:322 t = (text or "").lower()323 topics = sorted({name for name, kws in TOPICS.items()324 if any(k in t for k in kws)})325 if rating is None:326 sentiment = "inconnu"327 elif rating <= 2:328 sentiment = "négatif"329 elif rating >= 4:330 sentiment = "positif"331 else:332 sentiment = "neutre"333 severity = "faible"334 if sentiment == "négatif":335 severity = "élevée" if any(x in _GRAVES for x in topics) else "moyenne"336 if len(t) >= 80 and topics:337 confidence = 0.7338 elif t:339 confidence = 0.4340 else:341 confidence = 0.2342 return {"topics": topics, "sentiment": sentiment, "severite": severity,343 "confiance": confidence, "statut": "inferred",344 "methode": "lexique de thèmes fr + sentiment dérivé de la note"}345346347def _aggregate(con, manager_id: int) -> dict:348 rows = con.execute(349 "SELECT rating, published_at, owner_response, analysis"350 " FROM manager_reviews WHERE manager_id=?", (manager_id,)).fetchall()351 rated = [r for r in rows if r["rating"] is not None]352 out: dict = {"n": len(rows), "version": VERSION, "statut": "calculated",353 "methode": ("statistiques calculées sur les avis Google "354 "synchronisés en base (pas seulement la moyenne "355 "affichée par Google) — l'échantillon peut être "356 "plus petit que le total annoncé par Google, il "357 "s'accumule à chaque synchronisation ; thèmes "358 "par lexique — inférence, pas une lecture "359 "humaine")}360 if not rated:361 return out362 out["moyenne"] = round(sum(r["rating"] for r in rated) / len(rated), 2)363 dist = {str(i): 0 for i in range(1, 6)}364 for r in rated:365 k = str(int(round(r["rating"])))366 if k in dist:367 dist[k] += 1368 out["distribution"] = dist369 out["pct_negatif"] = round(100 * (dist["1"] + dist["2"]) / len(rated))370 out["pct_positif"] = round(100 * (dist["4"] + dist["5"]) / len(rated))371 out["avec_reponse_proprietaire"] = sum(372 1 for r in rows if r["owner_response"])373374 cutoff = datetime.now(timezone.utc).timestamp() - 365 * 86400375 rec, old = [], []376 for r in rated:377 ts = _iso_ts(r["published_at"])378 (rec if ts and ts >= cutoff else old).append(r["rating"])379 if rec:380 out["moyenne_12m"] = round(sum(rec) / len(rec), 2)381 out["n_12m"] = len(rec)382 if len(rec) >= 5 and len(old) >= 5:383 delta = out["moyenne_12m"] - sum(old) / len(old)384 out["tendance"] = ("en amélioration" if delta >= 0.3 else385 "en dégradation" if delta <= -0.3 else "stable")386 out["tendance_delta"] = round(delta, 2)387388 themes: dict[str, dict] = {}389 for r in rows:390 try:391 a = json.loads(r["analysis"] or "{}")392 except (ValueError, TypeError):393 continue394 for tp in a.get("topics") or []:395 d = themes.setdefault(tp, {"mentions": 0, "negatif": 0,396 "positif": 0})397 d["mentions"] += 1398 if a.get("sentiment") == "négatif":399 d["negatif"] += 1400 elif a.get("sentiment") == "positif":401 d["positif"] += 1402 out["themes"] = dict(sorted(themes.items(),403 key=lambda kv: -kv[1]["mentions"]))404 out["plaintes_frequentes"] = [405 k for k, v in sorted(themes.items(), key=lambda kv: -kv[1]["negatif"])406 if v["negatif"] >= 2][:5]407 return out408409410def _iso_ts(iso: str | None) -> float | None:411 if not iso:412 return None413 try:414 return datetime.fromisoformat(iso.replace("Z", "+00:00")).timestamp()415 except ValueError:416 return None417418419# --- fiche API + boucle de fond ----------------------------------------------420421def fiche(source_id: str, con=None) -> dict | None:422 """Fiche gestionnaire pour l'API — tout vient de NOTRE base (0 SerpApi)."""423 own = con is None424 if own:425 con = db.connect()426 try:427 mgr = con.execute("SELECT * FROM managers WHERE source_id=?",428 (source_id,)).fetchone()429 if mgr is None:430 return None431 m = dict(mgr)432 n_active = con.execute(433 "SELECT COUNT(*) n FROM listings WHERE source=? AND active=1"434 " AND published=1 AND dup_of IS NULL", (source_id,)).fetchone()["n"]435 out = {436 "source_id": m["source_id"], "nom": m["name"],437 "site_web": m["website"], "telephone": m["phone"],438 "annonces_actives": n_active, "version": VERSION,439 }440 if m["gmaps_place_id"] and not m["resolve_failed"]:441 out["google_maps"] = {442 "nom": m["gmaps_name"], "adresse": m["gmaps_address"],443 "note": m["gmaps_rating"], "nombre_avis": m["gmaps_reviews"],444 "place_id": m["gmaps_place_id"],445 "confiance_association": m["match_confidence"],446 "signaux": json.loads(m["match_signals"] or "{}"),447 "statut": "inferred",448 "methode": ("fiche associée par similarité de nom, domaine "449 "web et ville — association refusée sous "450 f"{SEUIL_CONFIANCE:.2f} de confiance"),451 }452 try:453 out["avis"] = json.loads(m["review_stats"] or "null")454 except (ValueError, TypeError):455 out["avis"] = None456 out["avis_recents"] = [457 {"note": r["rating"], "texte": r["text"],458 "date": r["published_at"] or r["relative_date_raw"],459 "auteur": r["author_name"],460 "reponse_proprietaire": bool(r["owner_response"]),461 "analyse": json.loads(r["analysis"] or "{}"),462 "statut": "observed"}463 for r in con.execute(464 "SELECT rating, text, published_at, relative_date_raw,"465 " author_name, owner_response, analysis"466 " FROM manager_reviews WHERE manager_id=?"467 " ORDER BY published_at DESC, fetched_at DESC LIMIT 10",468 (m["id"],))]469 out["derniere_synchro"] = m["last_synced_at"]470 elif m["resolved_at"]:471 out["google_maps"] = {472 "statut": "non_associe",473 "note_methode": ("aucune fiche Google Maps n'a pu être "474 "associée avec une confiance suffisante — "475 "Lou-Ka préfère ne rien afficher plutôt que "476 "d'attribuer les avis d'une autre entreprise"),477 }478 return out479 finally:480 if own:481 con.close()482483484def precompute(budget_s: float = 120.0, con=None) -> dict:485 """Boucle de fond : amorce, résout et synchronise dans un budget temps."""486 if not serpapi_client.available():487 return {"skipped": "SERPAPI_API_KEY absente"}488 own = con is None489 if own:490 con = db.connect()491 t0 = time.time()492 resolved = synced = errors = 0493 try:494 seed(con)495 now = time.time()496 # 1) résolutions manquantes (ou échecs anciens à retenter)497 for r in con.execute(498 "SELECT id FROM managers WHERE resolved_at IS NULL"499 " OR (resolve_failed=1 AND resolved_at < ?)"500 " ORDER BY resolved_at IS NOT NULL, id",501 (now - RESOLVE_RETRY,)).fetchall():502 if time.time() - t0 > budget_s:503 break504 try:505 if resolve(r["id"], con).get("ok"):506 resolved += 1507 except serpapi_client.SerpApiError:508 errors += 1509 break # panne API : ne pas insister ce tour-ci510 # 2) avis périmés (les jamais-synchronisés d'abord)511 for r in con.execute(512 "SELECT id FROM managers WHERE gmaps_data_id IS NOT NULL"513 " AND resolve_failed=0 AND (last_synced_at IS NULL"514 " OR last_synced_at < ?)"515 " ORDER BY last_synced_at IS NOT NULL, last_synced_at",516 (now - SYNC_TTL,)).fetchall():517 if time.time() - t0 > budget_s:518 break519 try:520 if sync_reviews(r["id"], con).get("ok"):521 synced += 1522 except serpapi_client.SerpApiError:523 errors += 1524 break525 return {"resolved": resolved, "synced": synced, "errors": errors,526 "elapsed_s": round(time.time() - t0, 1)}527 finally:528 if own:529 con.close()530531532if __name__ == "__main__":533 print(precompute())534