spb/immo-ka

1# -----------------------------------------------------------------------------2# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# quality.py : contrôle qualité des annonces — score de complétude, contrôles de5#   cohérence immobiliers, seuil de publication (quarantaine sous le seuil).6#7#   `refresh(con)` recalcule pour toutes les annonces actives :8#     - quality_score  : complétude 0-100 (pondération des champs décisifs)9#     - quality_issues : JSON (anomalies détectées, dures ou informatives)10#     - published      : 1 = affichable sur le site, 0 = quarantaine11#   et les champs dérivés dans details : prix_pi2 (prix/superficie) et12#   transaction (vente | location, détectée du libellé de prix).13#14#   Règle de publication : prix plausible + ville + type de bien + au moins une15#   image + contenu exploitable (description ou caractéristiques). Une annonce16#   sous le seuil reste en base (re-synchronisée/enrichie aux prochains cycles)17#   mais n'est pas affichée — elle sort de quarantaine dès qu'elle est complétée.18# -----------------------------------------------------------------------------19from __future__ import annotations2021import json22import os23import re24import sqlite325import time2627from .normalize import strip_accents2829# bornes de plausibilité (marché québécois)30PRICE_SALE_MIN, PRICE_SALE_MAX = 20_000, 80_000_00031PRICE_RENT_MIN, PRICE_RENT_MAX = 300, 25_00032AREA_MIN, AREA_MAX = 120, 25_000            # superficie habitable (pi²)33LOT_MAX = 200_000_000                        # terrain (pi²) — grandes terres34YEAR_MIN = 16003536_RENT_RE = re.compile(r"/\s*mois|par mois|/\s*mth|/\s*month|\bmensuel|\ba louer\b|"37                      r"\blouer\b|\blocation\b|\blease\b|\bfor rent\b")383940def _transaction(price, price_label: str, title: str) -> str:41    """vente | location — détectée du libellé (jamais confondre loyer et prix)."""42    key = strip_accents(f"{price_label} {title}".lower())43    if _RENT_RE.search(key):44        return "location"45    if price is not None and price < PRICE_RENT_MAX:46        # montant de loyer sans mot-clé : trop bas pour une vente au Québec47        return "location"48    return "vente"495051def assess(row: dict) -> tuple[int, list[str], int, dict]:52    """(score 0-100, anomalies, publiable 0/1, champs dérivés) pour une annonce.5354    `row` : dict aux clés des colonnes listings (features/details/images55    peuvent être des chaînes JSON ou déjà décodés)."""5657    def _json(v, default):58        if isinstance(v, (list, dict)):59            return v60        try:61            return json.loads(v) if v else default62        except (TypeError, ValueError):63            return default6465    images = _json(row.get("images"), [])66    features = _json(row.get("features"), [])67    details = _json(row.get("details"), {})68    desc = (row.get("description") or "").strip()69    price = row.get("price")70    issues: list[str] = []71    derived: dict = {}7273    tx = _transaction(price, row.get("price_label") or "", row.get("title") or "")74    derived["transaction"] = tx7576    # -- cohérence : prix plausible pour le type de transaction ----------------77    price_ok = price is not None and price > 078    if price_ok:79        lo, hi = ((PRICE_RENT_MIN, PRICE_RENT_MAX) if tx == "location"80                  else (PRICE_SALE_MIN, PRICE_SALE_MAX))81        if not (lo <= price <= hi):82            issues.append(f"prix_hors_bornes:{price:.0f}$ ({tx})")83            price_ok = False8485    # -- cohérence : superficies plausibles -------------------------------------86    area = row.get("area_sqft")87    area_ok = area is not None and AREA_MIN <= area <= AREA_MAX88    if area is not None and not area_ok:89        issues.append(f"superficie_improbable:{area:.0f}pi2")90    lot = row.get("lot_sqft")91    if lot is not None and not (0 < lot <= LOT_MAX):92        issues.append(f"terrain_improbable:{lot:.0f}pi2")9394    # -- cohérence : pièces / chambres ------------------------------------------95    beds, baths = row.get("bedrooms"), row.get("bathrooms")96    if beds is not None and not (0 <= beds <= 30):97        issues.append(f"chambres_improbables:{beds}")98        beds = None99    if baths is not None and not (0 <= baths <= 20):100        issues.append(f"sdb_improbables:{baths}")101        baths = None102    ptype = (row.get("property_type") or "").strip()103    if beds and ptype == "Condo" and beds > 8:104        issues.append(f"chambres_vs_type:{beds}ch_condo")105106    # -- cohérence : année de construction ---------------------------------------107    year = row.get("year_built")108    current_year = time.gmtime().tm_year109    if year is not None and not (YEAR_MIN <= year <= current_year + 3):110        issues.append(f"annee_invalide:{year}")111112    # -- champ dérivé : prix au pi² (et m²) ---------------------------------------113    if price_ok and area_ok and tx == "vente":114        ppsf = price / area115        derived["prix_pi2"] = round(ppsf)116        derived["prix_m2"] = round(ppsf * 10.7639)117        if not (30 <= ppsf <= 3500):118            issues.append(f"prix_pi2_extreme:{ppsf:.0f}")119120    # -- score de complétude (0-100) ----------------------------------------------121    n_img = len(images)122    has_contact = bool(row.get("broker_name") or row.get("broker_phone"))123    score = 0124    score += 15 if price_ok else 0125    score += 8 if (row.get("city") or "").strip() else 0126    score += 7 if (row.get("address") or "").strip() else 0127    score += 8 if ptype else 0128    score += 10 if n_img >= 1 else 0129    score += 5 if n_img >= 8 else 0130    score += 12 if len(desc) >= 300 else 8 if len(desc) >= 80 else 4 if desc else 0131    score += 6 if beds is not None else 0132    score += 5 if baths is not None else 0133    score += 8 if area_ok else 0134    score += 4 if year is not None else 0135    score += 7 if row.get("lat") is not None else 0136    score += 3 if has_contact else 0137    score += 2 if lot is not None else 0138139    # -- seuil de publication ---------------------------------------------------140    # (une annonce SANS image reste publiable : le frontend applique l'image de141    # secours par type de bien ; le drapeau sans_image la marque à re-vérifier)142    if n_img < 1:143        issues.append("sans_image")144    # vendue/louée à la source : archivée (plus jamais affichée en résultats)145    statut = (row.get("status") or "").strip().lower()146    vendue = statut in ("vendu", "vendue", "loue", "louee", "loué", "louée",147                        "sold", "rented", "retire", "retiré")148    if vendue:149        issues.append(f"statut:{statut}")150    publishable = (151        not vendue152        and price_ok153        and bool((row.get("city") or "").strip())154        and bool(ptype)155        and (len(desc) >= 50 or len(features) >= 3)156    )157    if not publishable:158        why = []159        if not price_ok:160            why.append("prix")161        if not (row.get("city") or "").strip():162            why.append("ville")163        if not ptype:164            why.append("type")165        if len(desc) < 50 and len(features) < 3:166            why.append("contenu")167        issues.append("quarantaine:" + "+".join(why))168169    return score, issues, int(publishable), derived170171172def refresh(con: sqlite3.Connection, sources: list[str] | None = None) -> dict:173    """Recalcule score/anomalies/publication pour les annonces actives.174175    Appelé après chaque synchronisation (ingest.run) — quelques secondes pour176    ~80 k lignes. Retourne un résumé {actives, publiees, quarantaine}."""177    _ensure_columns(con)178    sql = ("SELECT uid, source, title, price, price_label, city, address,"179           " property_type,"180           " bedrooms, bathrooms, area_sqft, lot_sqft, year_built, lat, status,"181           " broker_name, broker_phone, description, features, details, images,"182           " quality_score, quality_issues, published"183           " FROM listings WHERE active=1")184    args: list = []185    if sources:186        sql += f" AND source IN ({','.join('?' * len(sources))})"187        args = list(sources)188    updates = []189    n = pub = 0190    for r in con.execute(sql, args):191        row = dict(r)192        score, issues, publishable, derived = assess(row)193        # PAUSE ONTARIO (2026-08-27) : l'expansion ON (connecteurs RealtyPress,194        # sources rp_*) est suspendue — les annonces restent en base mais sont195        # dépubliées tant que IMMOKA_ONTARIO=1 n'est pas posé196        # (voir docs/ontario-agences-connecteurs.md).197        if (str(row.get("source") or "").startswith("rp_")198                and os.environ.get("IMMOKA_ONTARIO") != "1"):199            publishable = 0200            issues = issues + ["pause-ontario"]201        n += 1202        pub += publishable203        details = {}204        try:205            details = json.loads(row.get("details") or "{}")206        except ValueError:207            pass208        changed_details = any(details.get(k) != v for k, v in derived.items())209        issues_json = json.dumps(issues, ensure_ascii=False) if issues else None210        if (score != row.get("quality_score") or publishable != row.get("published")211                or issues_json != row.get("quality_issues") or changed_details):212            details.update(derived)213            updates.append((score, issues_json, publishable,214                            json.dumps(details, ensure_ascii=False), row["uid"]))215    if updates:216        con.executemany(217            "UPDATE listings SET quality_score=?, quality_issues=?, published=?,"218            " details=? WHERE uid=?", updates)219        con.commit()220    return {"actives": n, "publiees": pub, "quarantaine": n - pub,221            "recalculees": len(updates)}222223224def _ensure_columns(con: sqlite3.Connection) -> None:225    cols = {r["name"] for r in con.execute("PRAGMA table_info(listings)")}226    if "quality_score" not in cols:227        con.execute("ALTER TABLE listings ADD COLUMN quality_score INTEGER")228    if "quality_issues" not in cols:229        con.execute("ALTER TABLE listings ADD COLUMN quality_issues TEXT")230    if "published" not in cols:231        # 1 par défaut : la 1re passe refresh() met la vraie valeur partout232        con.execute("ALTER TABLE listings ADD COLUMN published INTEGER DEFAULT 1")233    con.execute("CREATE INDEX IF NOT EXISTS idx_listings_published"234                " ON listings(published)")235    con.commit()236237238def summary(con: sqlite3.Connection) -> dict:239    """Statistiques qualité pour /api/stats : complétude, quarantaine, anomalies."""240    _ensure_columns(con)241    row = con.execute(242        "SELECT COUNT(*) actives, SUM(published) publiees,"243        " ROUND(AVG(quality_score),1) completude_moyenne"244        " FROM listings WHERE active=1 AND dup_hidden=0").fetchone()245    per_source = [dict(r) for r in con.execute(246        "SELECT source, COUNT(*) n, SUM(published) publiees,"247        " ROUND(AVG(quality_score),1) completude,"248        " SUM(CASE WHEN quality_issues IS NOT NULL THEN 1 ELSE 0 END) anomalies"249        " FROM listings WHERE active=1 AND dup_hidden=0"250        " GROUP BY source ORDER BY n DESC")]251    anomalies: dict[str, int] = {}252    for r in con.execute(253            "SELECT quality_issues FROM listings WHERE active=1 AND dup_hidden=0"254            " AND quality_issues IS NOT NULL"):255        try:256            for issue in json.loads(r["quality_issues"]):257                anomalies[issue.split(":")[0]] = anomalies.get(issue.split(":")[0], 0) + 1258        except ValueError:259            continue260    d = dict(row)261    d["quarantaine"] = (d.get("actives") or 0) - (d.get("publiees") or 0)262    d["anomalies"] = dict(sorted(anomalies.items(), key=lambda kv: -kv[1]))263    d["par_source"] = per_source264    return d265