spb/ora-ka Public

1# -----------------------------------------------------------------------------2# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# web.py : API FastAPI (JSON) + service du frontend statique (frontend/)5# -----------------------------------------------------------------------------6from __future__ import annotations78import json9import threading10from pathlib import Path1112from fastapi import BackgroundTasks, FastAPI, HTTPException, Query13from fastapi.middleware.cors import CORSMiddleware14from fastapi.middleware.gzip import GZipMiddleware15from fastapi.responses import FileResponse16from fastapi.staticfiles import StaticFiles1718from . import db, ingest1920ROOT = Path(__file__).resolve().parent.parent21SOURCES_PATH = ROOT / "data" / "sources.json"22# Frontend : build Vite (React) si présent, sinon la page statique de secours.23FRONTEND_DIST = ROOT / "frontend" / "dist"24FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"2526app = FastAPI(title="Immo-Ka API", version="0.1",27              description="Agrégateur de maisons à vendre — province de Québec")28app.add_middleware(CORSMiddleware, allow_origins=["*"],29                   allow_methods=["*"], allow_headers=["*"])30# le GeoJSON complet pèse ~20 Mo — la compression est vitale pour la carte31app.add_middleware(GZipMiddleware, minimum_size=1024)3233_sync_lock = threading.Lock()3435# Déduplication de la famille RE/MAX : le flux central (remax_quebec) et les36# ~42 connecteurs de sous-agences (remax_ag_*) décrivent les MÊMES inscriptions,37# identifiées de façon unique par leur numéro Centris (= external_id). On masque38# donc toute fiche de sous-agence dès qu'une fiche de plus haute priorité existe39# (le central enrichi d'abord, puis la sous-agence au plus petit uid). Les fiches40# du central et des autres agences ne sont jamais masquées. → aucun double-comptage,41# et les sous-agences prennent le relais automatiquement si le central disparaît.42# Déduplication PRÉ-CALCULÉE : la colonne `dup_hidden` (remplie par43# db.refresh_dedup après chaque sync) marque les doublons de sous-agences. La44# lecture est ainsi instantanée (index) au lieu d'un sous-select corrélé par45# ligne (~300 s sur 75 k lignes). Voir db.refresh_dedup pour la règle.46# + `price IS NOT NULL` : on masque partout les annonces « Prix sur demande »47# (sans prix) — grille, carte, stats, sources. La fiche détail par uid reste48# accessible en direct.49DEDUP_CLAUSE = " AND dup_hidden=0 AND price IS NOT NULL"505152def _row_to_dict(row) -> dict:53    d = dict(row)54    d["features"] = json.loads(d.get("features") or "[]")55    d["images"] = json.loads(d.get("images") or "[]")56    d["details"] = json.loads(d.get("details") or "{}")57    if d.get("vraiprix"):58        try:59            d["vraiprix"] = json.loads(d["vraiprix"]) or None60        except (ValueError, TypeError):61            d["vraiprix"] = None62    return d636465@app.get("/api/listings")66def list_listings(67    city: str | None = None,68    sector: str | None = None,69    region: str | None = None,70    property_type: str | None = None,71    source: str | None = None,72    price_max: float | None = None,73    price_min: float | None = None,74    bedrooms_min: int | None = None,75    bathrooms_min: int | None = None,76    area_min: float | None = None,       # superficie habitable minimale (pi²)77    q: str | None = None,78    active: int = 1,79    sort: str = "price_asc",             # price_asc | price_desc | recent80    limit: int = Query(500, le=2000),81    offset: int = 0,82):83    con = db.connect()84    sql = "SELECT * FROM listings WHERE 1=1"85    args: list = []86    if active in (0, 1):87        sql += " AND active=?"; args.append(active)88    if city:89        sql += " AND city=?"; args.append(city)90    if sector:91        sql += " AND sector LIKE ?"; args.append(f"%{sector}%")92    if region:93        sql += " AND region=?"; args.append(region)94    if property_type:95        sql += " AND property_type=?"; args.append(property_type)96    if source:97        sql += " AND source=?"; args.append(source)98    if price_max is not None:99        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)100    if price_min is not None:101        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)102    if bedrooms_min is not None:103        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)104    if bathrooms_min is not None:105        sql += " AND bathrooms IS NOT NULL AND bathrooms>=?"; args.append(bathrooms_min)106    if area_min is not None:107        sql += " AND area_sqft IS NOT NULL AND area_sqft>=?"; args.append(area_min)108    if q:109        sql += " AND (title LIKE ? OR address LIKE ? OR city LIKE ? OR mls LIKE ?)"110        args += [f"%{q}%"] * 4111    sql += DEDUP_CLAUSE112    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]113    order = {114        "price_asc": " ORDER BY price IS NULL, price ASC",115        "price_desc": " ORDER BY price IS NULL, price DESC",116        "recent": " ORDER BY first_seen DESC",117    }.get(sort, " ORDER BY price IS NULL, price ASC")118    sql += order + " LIMIT ? OFFSET ?"119    args += [limit, offset]120    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]121    con.close()122    return {"total": total, "count": len(rows), "listings": rows}123124125@app.get("/api/listings/{uid}")126def get_listing(uid: str):127    con = db.connect()128    row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()129    d = None130    if row is not None:131        d = _row_to_dict(row)132        # historique de prix (baisses/hausses du prix demandé)133        d["price_history"] = [dict(r) for r in con.execute(134            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",135            (uid,)).fetchall()]136        # commodités de proximité (cache par immeuble, voir immoka/poi.py)137        if d.get("lat") is not None and d.get("lng") is not None:138            key = f"{round(d['lat'], 4)},{round(d['lng'], 4)}"139            poi_row = con.execute(140                "SELECT pois FROM poi_cache WHERE coord_key=?", (key,)).fetchone()141            d["poi"] = json.loads(poi_row["pois"]) if poi_row else []142        else:143            d["poi"] = []144        # statistiques de quartier (recensement, proximité, chaleur, criminalité)145        from . import quartier146        dauid = d.get("dauid")147        d["quartier"] = quartier.fiche_quartier(148            d.get("lat"), d.get("lng"), d.get("city") or "",149            dauid if dauid and dauid != "hors-zone" else None)150    con.close()151    if d is None:152        raise HTTPException(404, "Propriété introuvable")153    return d154155156@app.get("/api/listings.geojson")157def listings_geojson(158    city: str | None = None,159    property_type: str | None = None,160    source: str | None = None,161    price_max: float | None = None,162    price_min: float | None = None,163    bedrooms_min: int | None = None,164):165    """Propriétés géolocalisées (marqueurs de carte, champs allégés)."""166    con = db.connect()167    sql = ("SELECT uid, title, address, price, price_label, property_type,"168           " bedrooms, bathrooms, source, city, sector, images, lat, lng, vraiprix"169           " FROM listings WHERE active=1 AND lat IS NOT NULL AND lng IS NOT NULL")170    args: list = []171    if city:172        sql += " AND city=?"; args.append(city)173    if property_type:174        sql += " AND property_type=?"; args.append(property_type)175    if source:176        sql += " AND source=?"; args.append(source)177    if price_max is not None:178        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)179    if price_min is not None:180        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)181    if bedrooms_min is not None:182        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)183    sql += DEDUP_CLAUSE184    features = []185    for r in con.execute(sql, args).fetchall():186        images = json.loads(r["images"] or "[]")187        try:188            vp_val = (json.loads(r["vraiprix"]) or {}).get("value")189        except (TypeError, ValueError):190            vp_val = None191        features.append({192            "type": "Feature",193            "geometry": {"type": "Point", "coordinates": [r["lng"], r["lat"]]},194            "properties": {195                "uid": r["uid"],196                "title": None if r["address"] else r["title"],197                "address": r["address"],198                "price": r["price"], "price_label": r["price_label"],199                "property_type": r["property_type"], "bedrooms": r["bedrooms"],200                "bathrooms": r["bathrooms"], "source": r["source"],201                "city": r["city"], "sector": r["sector"],202                "image": images[0] if images else None,203                "vp": vp_val,204            },205        })206    con.close()207    return {"type": "FeatureCollection", "features": features}208209210@app.get("/api/facets")211def facets(city: str | None = None):212    """Valeurs distinctes pour construire les filtres du frontend."""213    con = db.connect()214    sector_sql = "SELECT DISTINCT sector FROM listings WHERE active=1 AND sector<>''"215    sector_args: list = []216    if city:217        sector_sql += " AND city=?"218        sector_args.append(city)219    out = {220        "cities": [r["city"] for r in con.execute(221            "SELECT DISTINCT city FROM listings WHERE active=1 AND city<>'' ORDER BY city")],222        "sectors": [r["sector"] for r in con.execute(223            sector_sql + " ORDER BY sector", sector_args)],224        "property_types": [r["property_type"] for r in con.execute(225            "SELECT DISTINCT property_type FROM listings WHERE active=1"226            " AND property_type<>'' ORDER BY property_type")],227        "sources": [dict(r) for r in con.execute(228            "SELECT source, COUNT(*) n FROM listings WHERE active=1"229            + DEDUP_CLAUSE + " GROUP BY source ORDER BY n DESC")],230    }231    con.close()232    return out233234235@app.get("/api/sources")236def sources():237    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]238    con = db.connect()239    counts = {r["source"]: r["n"] for r in con.execute(240        "SELECT source, COUNT(*) n FROM listings WHERE active=1 GROUP BY source")}241    last = {r["source"]: r["ts"] for r in con.execute(242        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}243    con.close()244    for s in registry:245        s["active_listings"] = counts.get(s["id"], 0)246        s["last_sync"] = last.get(s["id"])247    return {"sources": registry}248249250# Rattachement d'une source à sa bannière (franchise) pour le regroupement.251_FRANCHISES = [252    ("RE/MAX", lambda s: s == "remax_quebec" or s.startswith("remax_ag_")),253    ("Via Capitale", lambda s: s == "via_capitale" or s.startswith("via_ag_")),254    ("Century 21", lambda s: s == "century21" or s.startswith("c21_ag_")),255    ("Royal LePage", lambda s: s == "royal_lepage"),256    ("Groupe Sutton", lambda s: s == "sutton"),257    ("Keller Williams", lambda s: s.startswith("kw_")),258    ("DuProprio", lambda s: s == "duproprio"),259]260261262def _franchise_of(source: str, source_names: dict) -> str:263    for name, match in _FRANCHISES:264        if match(source):265            return name266    return source_names.get(source, source)   # agence indépendante = elle-même267268269@app.get("/api/agencies")270def agencies():271    """Arbre bannière → sous-agences (bureaux) avec le nombre d'inscriptions.272273    Alimente la page « Sources » de l'app : chaque bannière est éclatée par274    sous-agence via le champ `agency` (bureau). Dédupliqué (n° Centris)."""275    con = db.connect()276    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]277    source_names = {s["id"]: s["name"] for s in registry}278    rows = con.execute(279        "SELECT source, COALESCE(NULLIF(agency,''), '') agency, COUNT(*) n"280        " FROM listings WHERE active=1" + DEDUP_CLAUSE +281        " GROUP BY source, agency").fetchall()282    con.close()283    tree: dict[str, dict] = {}284    for r in rows:285        fr = _franchise_of(r["source"], source_names)286        node = tree.setdefault(fr, {"franchise": fr, "total": 0, "agencies": {}})287        node["total"] += r["n"]288        # nom de sous-agence : le bureau (agency) sinon le nom de la source289        label = r["agency"] or source_names.get(r["source"], r["source"])290        a = node["agencies"].setdefault(label, {"name": label, "count": 0,291                                                 "sources": set()})292        a["count"] += r["n"]293        a["sources"].add(r["source"])294    out = []295    for node in tree.values():296        ags = sorted(node["agencies"].values(), key=lambda x: -x["count"])297        for a in ags:298            a["sources"] = sorted(a["sources"])299        out.append({"franchise": node["franchise"], "total": node["total"],300                    "sub_agencies": len(ags), "agencies": ags})301    out.sort(key=lambda x: -x["total"])302    return {"franchises": out}303304305@app.get("/api/stats")306def stats():307    con = db.connect()308    row = con.execute(309        """SELECT COUNT(*) total,310                  COUNT(DISTINCT source) sources,311                  COUNT(DISTINCT city) cities,312                  AVG(price) avg_price,313                  MIN(price) min_price,314                  MAX(price) max_price315           FROM listings WHERE active=1""" + DEDUP_CLAUSE).fetchone()316    log = [dict(r) for r in con.execute(317        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]318319    # --- écart prix demandé vs estimation Vrai-Prix, par bannière -----------320    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]321    source_names = {s["id"]: s["name"] for s in registry}322    deltas: dict[str, list[float]] = {}323    for r in con.execute(324            "SELECT source, price,"325            " CAST(json_extract(vraiprix, '$.value') AS REAL) vp"326            " FROM listings WHERE active=1" + DEDUP_CLAUSE +327            " AND vraiprix LIKE '%estimation%'"):328        if not r["vp"] or r["vp"] <= 0:329            continue330        d = (r["price"] - r["vp"]) / r["vp"] * 100.0331        if -80.0 <= d <= 300.0:   # coupe les aberrations (terrains, données sales)332            deltas.setdefault(_franchise_of(r["source"], source_names), []).append(d)333    con.close()334335    def _agg(name: str, ds: list[float]) -> dict:336        ds = sorted(ds)337        n = len(ds)338        med = ds[n // 2] if n % 2 else (ds[n // 2 - 1] + ds[n // 2]) / 2339        return {340            "banniere": name, "n": n,341            "median_delta_pct": round(med, 1),342            "p25": round(ds[n // 4], 1), "p75": round(ds[(3 * n) // 4], 1),343            "pct_sur10": round(100 * sum(1 for d in ds if d > 10) / n, 1),344            "pct_juste": round(100 * sum(1 for d in ds if -5 <= d <= 10) / n, 1),345            "pct_sous5": round(100 * sum(1 for d in ds if d < -5) / n, 1),346        }347348    tous = [d for ds in deltas.values() for d in ds]349    vraiprix = {350        "ensemble": _agg("Toutes bannières", tous) if tous else None,351        "bannieres": sorted(352            (_agg(k, v) for k, v in deltas.items() if len(v) >= 30),353            key=lambda x: -x["median_delta_pct"]),354    }355    return {**dict(row), "vraiprix": vraiprix, "recent_syncs": log}356357358@app.post("/api/sync")359def trigger_sync(background: BackgroundTasks, source: str | None = None):360    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""361    def _job():362        with _sync_lock:363            ingest.run([source] if source else None)364    background.add_task(_job)365    return {"status": "démarré", "source": source or "toutes"}366367368# --- Frontend statique -------------------------------------------------------369if FRONTEND_DIR.exists():370371    if (FRONTEND_DIR / "assets").is_dir():372        app.mount("/assets", StaticFiles(directory=FRONTEND_DIR / "assets"), name="assets")373374    @app.get("/{full_path:path}")375    def spa(full_path: str):376        target = FRONTEND_DIR / full_path377        if full_path and target.is_file():378            return FileResponse(target)379        return FileResponse(FRONTEND_DIR / "index.html")380