spb/immo-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.responses import FileResponse15from fastapi.staticfiles import StaticFiles1617from . import db, ingest1819ROOT = Path(__file__).resolve().parent.parent20SOURCES_PATH = ROOT / "data" / "sources.json"21# Frontend : build Vite (React) si présent, sinon la page statique de secours.22FRONTEND_DIST = ROOT / "frontend" / "dist"23FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"2425app = FastAPI(title="Immo-Ka API", version="0.1",26              description="Agrégateur de maisons à vendre — province de Québec")27app.add_middleware(CORSMiddleware, allow_origins=["*"],28                   allow_methods=["*"], allow_headers=["*"])2930_sync_lock = threading.Lock()3132# Déduplication de la famille RE/MAX : le flux central (remax_quebec) et les33# ~42 connecteurs de sous-agences (remax_ag_*) décrivent les MÊMES inscriptions,34# identifiées de façon unique par leur numéro Centris (= external_id). On masque35# donc toute fiche de sous-agence dès qu'une fiche de plus haute priorité existe36# (le central enrichi d'abord, puis la sous-agence au plus petit uid). Les fiches37# du central et des autres agences ne sont jamais masquées. → aucun double-comptage,38# et les sous-agences prennent le relais automatiquement si le central disparaît.39DEDUP_CLAUSE = (40    " AND NOT EXISTS (SELECT 1 FROM listings d WHERE d.active=1"41    "   AND d.external_id = listings.external_id AND d.uid <> listings.uid"42    "   AND (listings.source LIKE '%\\_ag\\_%' ESCAPE '\\')"43    "   AND ((d.source NOT LIKE '%\\_ag\\_%' ESCAPE '\\')"44    "        OR (d.source LIKE '%\\_ag\\_%' ESCAPE '\\' AND d.uid < listings.uid)))"45)464748def _row_to_dict(row) -> dict:49    d = dict(row)50    d["features"] = json.loads(d.get("features") or "[]")51    d["images"] = json.loads(d.get("images") or "[]")52    d["details"] = json.loads(d.get("details") or "{}")53    return d545556@app.get("/api/listings")57def list_listings(58    city: str | None = None,59    sector: str | None = None,60    region: str | None = None,61    property_type: str | None = None,62    source: str | None = None,63    price_max: float | None = None,64    price_min: float | None = None,65    bedrooms_min: int | None = None,66    bathrooms_min: int | None = None,67    area_min: float | None = None,       # superficie habitable minimale (pi²)68    q: str | None = None,69    active: int = 1,70    sort: str = "price_asc",             # price_asc | price_desc | recent71    limit: int = Query(500, le=2000),72    offset: int = 0,73):74    con = db.connect()75    sql = "SELECT * FROM listings WHERE 1=1"76    args: list = []77    if active in (0, 1):78        sql += " AND active=?"; args.append(active)79    if city:80        sql += " AND city=?"; args.append(city)81    if sector:82        sql += " AND sector LIKE ?"; args.append(f"%{sector}%")83    if region:84        sql += " AND region=?"; args.append(region)85    if property_type:86        sql += " AND property_type=?"; args.append(property_type)87    if source:88        sql += " AND source=?"; args.append(source)89    if price_max is not None:90        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)91    if price_min is not None:92        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)93    if bedrooms_min is not None:94        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)95    if bathrooms_min is not None:96        sql += " AND bathrooms IS NOT NULL AND bathrooms>=?"; args.append(bathrooms_min)97    if area_min is not None:98        sql += " AND area_sqft IS NOT NULL AND area_sqft>=?"; args.append(area_min)99    if q:100        sql += " AND (title LIKE ? OR address LIKE ? OR city LIKE ? OR mls LIKE ?)"101        args += [f"%{q}%"] * 4102    sql += DEDUP_CLAUSE103    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]104    order = {105        "price_asc": " ORDER BY price IS NULL, price ASC",106        "price_desc": " ORDER BY price IS NULL, price DESC",107        "recent": " ORDER BY first_seen DESC",108    }.get(sort, " ORDER BY price IS NULL, price ASC")109    sql += order + " LIMIT ? OFFSET ?"110    args += [limit, offset]111    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]112    con.close()113    return {"total": total, "count": len(rows), "listings": rows}114115116@app.get("/api/listings/{uid}")117def get_listing(uid: str):118    con = db.connect()119    row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()120    d = None121    if row is not None:122        d = _row_to_dict(row)123        # historique de prix (baisses/hausses du prix demandé)124        d["price_history"] = [dict(r) for r in con.execute(125            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",126            (uid,)).fetchall()]127    con.close()128    if d is None:129        raise HTTPException(404, "Propriété introuvable")130    return d131132133@app.get("/api/listings.geojson")134def listings_geojson(135    city: str | None = None,136    property_type: str | None = None,137    source: str | None = None,138    price_max: float | None = None,139    price_min: float | None = None,140    bedrooms_min: int | None = None,141):142    """Propriétés géolocalisées (marqueurs de carte, champs allégés)."""143    con = db.connect()144    sql = ("SELECT uid, title, address, price, price_label, property_type,"145           " bedrooms, bathrooms, source, city, sector, images, lat, lng"146           " FROM listings WHERE active=1 AND lat IS NOT NULL AND lng IS NOT NULL")147    args: list = []148    if city:149        sql += " AND city=?"; args.append(city)150    if property_type:151        sql += " AND property_type=?"; args.append(property_type)152    if source:153        sql += " AND source=?"; args.append(source)154    if price_max is not None:155        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)156    if price_min is not None:157        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)158    if bedrooms_min is not None:159        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)160    sql += DEDUP_CLAUSE161    features = []162    for r in con.execute(sql, args).fetchall():163        images = json.loads(r["images"] or "[]")164        features.append({165            "type": "Feature",166            "geometry": {"type": "Point", "coordinates": [r["lng"], r["lat"]]},167            "properties": {168                "uid": r["uid"], "title": r["title"], "address": r["address"],169                "price": r["price"], "price_label": r["price_label"],170                "property_type": r["property_type"], "bedrooms": r["bedrooms"],171                "bathrooms": r["bathrooms"], "source": r["source"],172                "city": r["city"], "sector": r["sector"],173                "image": images[0] if images else None,174            },175        })176    con.close()177    return {"type": "FeatureCollection", "features": features}178179180@app.get("/api/facets")181def facets(city: str | None = None):182    """Valeurs distinctes pour construire les filtres du frontend."""183    con = db.connect()184    sector_sql = "SELECT DISTINCT sector FROM listings WHERE active=1 AND sector<>''"185    sector_args: list = []186    if city:187        sector_sql += " AND city=?"188        sector_args.append(city)189    out = {190        "cities": [r["city"] for r in con.execute(191            "SELECT DISTINCT city FROM listings WHERE active=1 AND city<>'' ORDER BY city")],192        "sectors": [r["sector"] for r in con.execute(193            sector_sql + " ORDER BY sector", sector_args)],194        "property_types": [r["property_type"] for r in con.execute(195            "SELECT DISTINCT property_type FROM listings WHERE active=1"196            " AND property_type<>'' ORDER BY property_type")],197        "sources": [dict(r) for r in con.execute(198            "SELECT source, COUNT(*) n FROM listings WHERE active=1"199            + DEDUP_CLAUSE + " GROUP BY source ORDER BY n DESC")],200    }201    con.close()202    return out203204205@app.get("/api/sources")206def sources():207    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]208    con = db.connect()209    counts = {r["source"]: r["n"] for r in con.execute(210        "SELECT source, COUNT(*) n FROM listings WHERE active=1 GROUP BY source")}211    last = {r["source"]: r["ts"] for r in con.execute(212        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}213    con.close()214    for s in registry:215        s["active_listings"] = counts.get(s["id"], 0)216        s["last_sync"] = last.get(s["id"])217    return {"sources": registry}218219220@app.get("/api/stats")221def stats():222    con = db.connect()223    row = con.execute(224        """SELECT COUNT(*) total,225                  COUNT(DISTINCT source) sources,226                  COUNT(DISTINCT city) cities,227                  AVG(price) avg_price,228                  MIN(price) min_price,229                  MAX(price) max_price230           FROM listings WHERE active=1""" + DEDUP_CLAUSE).fetchone()231    log = [dict(r) for r in con.execute(232        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]233    con.close()234    return {**dict(row), "recent_syncs": log}235236237@app.post("/api/sync")238def trigger_sync(background: BackgroundTasks, source: str | None = None):239    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""240    def _job():241        with _sync_lock:242            ingest.run([source] if source else None)243    background.add_task(_job)244    return {"status": "démarré", "source": source or "toutes"}245246247# --- Frontend statique -------------------------------------------------------248if FRONTEND_DIR.exists():249250    if (FRONTEND_DIR / "assets").is_dir():251        app.mount("/assets", StaticFiles(directory=FRONTEND_DIR / "assets"), name="assets")252253    @app.get("/{full_path:path}")254    def spa(full_path: str):255        target = FRONTEND_DIR / full_path256        if full_path and target.is_file():257            return FileResponse(target)258        return FileResponse(FRONTEND_DIR / "index.html")259