spb/toit-ka Public

1# Author: Simon-Pierre Boucher2# Contact: contact@spboucher.ai3# Project: Toit-Ka4# -----------------------------------------------------------------------------5# web.py : API FastAPI (JSON) + service du frontend + rendu SEO serveur.6#   Toit-Ka lit UNIQUEMENT toitka.db (construite par l'ETL depuis les répliques7#   Lou-Ka / Immo-Ka) — le paramètre `tx` (louer | acheter) filtre l'univers.8# -----------------------------------------------------------------------------9from __future__ import annotations1011import json12import time13from pathlib import Path1415from fastapi import FastAPI, HTTPException, Query, Request16from fastapi.middleware.cors import CORSMiddleware17from fastapi.middleware.gzip import GZipMiddleware18from fastapi.responses import FileResponse19from fastapi.staticfiles import StaticFiles2021from . import auth, db, favorites, seo2223ROOT = Path(__file__).resolve().parent.parent24FRONTEND_DIST = ROOT / "frontend" / "dist"25FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"2627app = FastAPI(title="Toit-Ka API", version="1.0",28              description="Louer ou acheter un toit au Québec — un seul endroit")29app.add_middleware(CORSMiddleware, allow_origins=["*"],30                   allow_methods=["*"], allow_headers=["*"])31app.add_middleware(GZipMiddleware, minimum_size=1024)32app.include_router(auth.router)33app.include_router(favorites.router)3435VISIBLE = "active=1"36TX = ("louer", "acheter")373839def _row_to_dict(row) -> dict:40    d = dict(row)41    d["images"] = json.loads(d.get("images") or "[]")42    return d434445def _filters_sql(tx: str | None, city: str | None, sector: str | None,46                 type_: str | None, source: str | None,47                 price_min: float | None, price_max: float | None,48                 bedrooms_min: int | None, bathrooms_min: int | None,49                 area_min: float | None, pets: str | None,50                 furnished: int | None, q: str | None) -> tuple[str, list]:51    sql, args = f" AND {VISIBLE}", []52    if tx in TX:53        sql += " AND transaction_type=?"; args.append(tx)54    if city:55        sql += " AND city=?"; args.append(city)56    if sector:57        sql += " AND sector LIKE ?"; args.append(f"%{sector}%")58    if type_:59        sql += " AND type=?"; args.append(type_)60    if source:61        sql += " AND source=?"; args.append(source)62    if price_min is not None:63        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)64    if price_max is not None:65        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)66    if bedrooms_min is not None:67        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)68    if bathrooms_min is not None:69        sql += " AND bathrooms IS NOT NULL AND bathrooms>=?"; args.append(bathrooms_min)70    if area_min is not None:71        sql += " AND area_sqft IS NOT NULL AND area_sqft>=?"; args.append(area_min)72    if pets:73        sql += " AND pets IN ('oui','conditions')" if pets == "oui" else " AND pets=?"74        if pets != "oui":75            args.append(pets)76    if furnished is not None:77        sql += " AND furnished=?"; args.append(furnished)78    if q:79        sql += " AND (title LIKE ? OR address LIKE ? OR city LIKE ? OR sector LIKE ? OR mls LIKE ?)"80        args += [f"%{q}%"] * 581    return sql, args828384@app.get("/api/listings")85def list_listings(86    tx: str | None = None,87    city: str | None = None,88    sector: str | None = None,89    type: str | None = None,90    source: str | None = None,91    price_min: float | None = None,92    price_max: float | None = None,93    bedrooms_min: int | None = None,94    bathrooms_min: int | None = None,95    area_min: float | None = None,96    pets: str | None = None,97    furnished: int | None = None,98    q: str | None = None,99    sort: str = "recent",100    limit: int = Query(60, le=2000),101    offset: int = 0,102):103    where, args = _filters_sql(tx, city, sector, type, source, price_min,104                               price_max, bedrooms_min, bathrooms_min,105                               area_min, pets, furnished, q)106    con = db.connect()107    try:108        sql = "SELECT * FROM listings WHERE 1=1" + where109        total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]110        order = {111            "price_asc": " ORDER BY price IS NULL, price ASC",112            "price_desc": " ORDER BY price IS NULL, price DESC",113            "recent": " ORDER BY first_seen DESC",114        }.get(sort, " ORDER BY first_seen DESC")115        rows = [_row_to_dict(r) for r in con.execute(116            sql + order + " LIMIT ? OFFSET ?", args + [limit, offset])]117    finally:118        con.close()119    return {"total": total, "count": len(rows), "listings": rows}120121122@app.get("/api/listings/{uid:path}")123def get_listing(uid: str):124    con = db.connect()125    try:126        row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()127    finally:128        con.close()129    if row is None:130        raise HTTPException(404, "Annonce introuvable")131    return _row_to_dict(row)132133134@app.get("/api/listings.geojson")135def listings_geojson(136    tx: str | None = None,137    city: str | None = None,138    sector: str | None = None,139    type: str | None = None,140    source: str | None = None,141    price_min: float | None = None,142    price_max: float | None = None,143    bedrooms_min: int | None = None,144    bathrooms_min: int | None = None,145    area_min: float | None = None,146    pets: str | None = None,147    furnished: int | None = None,148    q: str | None = None,149    bbox: str | None = None,150    limit: int = Query(3000, le=8000),151):152    """Marqueurs de carte (Ka Maps) — mêmes filtres que /api/listings + bbox."""153    where, args = _filters_sql(tx, city, sector, type, source, price_min,154                               price_max, bedrooms_min, bathrooms_min,155                               area_min, pets, furnished, q)156    sql = ("SELECT uid, transaction_type, title, address, price, price_label,"157           " type, bedrooms, bathrooms, source, city, sector, images, lat, lng"158           " FROM listings WHERE lat IS NOT NULL AND lng IS NOT NULL" + where)159    if bbox:160        try:161            west, south, east, north = (float(v) for v in bbox.split(","))162        except ValueError:163            raise HTTPException(400, "bbox attendu : ouest,sud,est,nord")164        sql += " AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?"165        args += [south, north, west, east]166167    con = db.connect()168    try:169        total_geo = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]170        sql_all = sql.replace(" WHERE lat IS NOT NULL AND lng IS NOT NULL", " WHERE 1=1")171        args_all = list(args)172        if bbox:173            sql_all = sql_all.replace(" AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?", "")174            del args_all[-4:]175        total_all = con.execute(f"SELECT COUNT(*) c FROM ({sql_all})", args_all).fetchone()["c"]176177        features = []178        for r in con.execute(sql + " ORDER BY price IS NULL, price ASC LIMIT ?",179                             args + [limit]):180            images = json.loads(r["images"] or "[]")181            features.append({182                "type": "Feature",183                "geometry": {"type": "Point", "coordinates": [r["lng"], r["lat"]]},184                "properties": {185                    "uid": r["uid"], "tx": r["transaction_type"],186                    "title": None if r["address"] else r["title"],187                    "address": r["address"],188                    "price": r["price"], "price_label": r["price_label"],189                    "type": r["type"], "bedrooms": r["bedrooms"],190                    "bathrooms": r["bathrooms"], "source": r["source"],191                    "city": r["city"], "sector": r["sector"],192                    "image": images[0] if images else None,193                },194            })195    finally:196        con.close()197    return {"type": "FeatureCollection", "features": features,198            "totalGeocoded": total_geo, "totalMatching": total_all}199200201@app.get("/api/facets")202def facets(tx: str | None = None, city: str | None = None):203    """Valeurs distinctes pour les filtres du frontend, par univers (tx)."""204    con = db.connect()205    txw, txa = ("", [])206    if tx in TX:207        txw, txa = " AND transaction_type=?", [tx]208    try:209        sector_sql = (f"SELECT DISTINCT sector FROM listings WHERE {VISIBLE}"210                      " AND sector<>''") + txw211        sector_args = list(txa)212        if city:213            sector_sql += " AND city=?"214            sector_args.append(city)215        out = {216            "cities": [dict(r) for r in con.execute(217                f"SELECT city, COUNT(*) n FROM listings WHERE {VISIBLE}"218                " AND city<>''" + txw + " GROUP BY city ORDER BY n DESC", txa)],219            "sectors": [r["sector"] for r in con.execute(220                sector_sql + " ORDER BY sector", sector_args)],221            "types": [dict(r) for r in con.execute(222                f"SELECT type, COUNT(*) n FROM listings WHERE {VISIBLE}"223                " AND type<>''" + txw + " GROUP BY type ORDER BY n DESC", txa)],224            "sources": [dict(r) for r in con.execute(225                f"SELECT source, COUNT(*) n FROM listings WHERE {VISIBLE}"226                + txw + " GROUP BY source ORDER BY n DESC", txa)],227        }228    finally:229        con.close()230    return out231232233@app.get("/api/stats")234def stats():235    """Statistiques des deux univers (louer / acheter) + top villes."""236    con = db.connect()237    try:238        out: dict = {}239        for tx in TX:240            row = con.execute(241                f"""SELECT COUNT(*) total, COUNT(DISTINCT source) sources,242                           COUNT(DISTINCT NULLIF(city,'')) cities,243                           AVG(price) avg_price, MIN(price) min_price,244                           MAX(price) max_price245                    FROM listings WHERE {VISIBLE} AND transaction_type=?""",246                (tx,)).fetchone()247            top = [dict(r) for r in con.execute(248                f"""SELECT city, COUNT(*) n, ROUND(AVG(price)) avg_price249                    FROM listings WHERE {VISIBLE} AND transaction_type=?250                      AND city<>'' AND price IS NOT NULL251                    GROUP BY city ORDER BY n DESC LIMIT 15""", (tx,))]252            types = [dict(r) for r in con.execute(253                f"""SELECT type, COUNT(*) n, ROUND(AVG(price)) avg_price254                    FROM listings WHERE {VISIBLE} AND transaction_type=?255                      AND type<>'' AND price IS NOT NULL256                    GROUP BY type ORDER BY n DESC LIMIT 15""", (tx,))]257            out[tx] = {**dict(row), "top_cities": top, "top_types": types}258        out["etl"] = [dict(r) for r in con.execute(259            "SELECT ts, origin, found, ok, message FROM etl_log"260            " ORDER BY ts DESC LIMIT 10")]261    finally:262        con.close()263    return out264265266@app.get("/api/health")267def health():268    con = db.connect()269    try:270        n = con.execute(f"SELECT COUNT(*) c FROM listings WHERE {VISIBLE}").fetchone()["c"]271        last = con.execute("SELECT MAX(ts) m FROM etl_log WHERE ok=1").fetchone()["m"]272    finally:273        con.close()274    return {"ok": n > 0, "listings": n, "last_etl": last, "ts": time.time()}275276277@app.get("/api/seo/resolve")278def seo_resolve(tx: str | None = None, ville: str | None = None,279                type: str | None = None):280    """Slug d'URL programmatique -> valeurs exactes (pages /louer /acheter du SPA)."""281    out = seo.resolve_slugs(tx, ville, type)282    if out is None:283        raise HTTPException(404, "Slug inconnu")284    return out285286287# --- Référencement : robots + sitemaps (voir toitka/seo.py) --------------------288@app.get("/robots.txt")289def robots():290    return seo.robots_txt()291292293@app.get("/sitemap.xml")294def sitemap():295    return seo.sitemap_index()296297298@app.get("/sitemaps/{name}")299def sitemap_part(name: str):300    return seo.sitemap_file(name)301302303# --- Frontend statique + SSR SEO ------------------------------------------------304if FRONTEND_DIR.exists():305306    if (FRONTEND_DIR / "assets").is_dir():307        app.mount("/assets", StaticFiles(directory=FRONTEND_DIR / "assets"), name="assets")308309    @app.middleware("http")310    async def _cache_headers(request: Request, call_next):311        resp = await call_next(request)312        if request.url.path.startswith("/assets/"):313            resp.headers["Cache-Control"] = "public, max-age=31536000, immutable"314        elif resp.headers.get("content-type", "").startswith("text/html"):315            resp.headers["Cache-Control"] = "no-cache"316        return resp317318    @app.get("/{full_path:path}")319    def spa(full_path: str, request: Request):320        target = FRONTEND_DIR / full_path321        if full_path and ".." not in full_path and target.is_file():322            return FileResponse(target)323        # rendu SEO serveur : HTML complet par route (meta, JSON-LD, contenu324        # dans #root que React remplace au montage). Ne doit JAMAIS casser325        # l'app -> repli sur l'index brut à la moindre erreur.326        try:327            return seo.render_for_path("/" + full_path, dict(request.query_params))328        except Exception:329            return FileResponse(FRONTEND_DIR / "index.html")330