Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 98.9%
Python 0.6%
1# -----------------------------------------------------------------------------2# Lou-Ka — Location court terme3# web.py : routeur API /api/ct/* (liste, geojson, détail, facettes, stats)4# — inclus par louka/web.py, entièrement séparé du long terme.5# -----------------------------------------------------------------------------6from __future__ import annotations78import json9import math10import threading11from pathlib import Path1213from fastapi import APIRouter, BackgroundTasks, HTTPException, Query1415from . import db1617router = APIRouter(prefix="/api/ct", tags=["court-terme"])1819SOURCES_CT_PATH = (Path(__file__).resolve().parent.parent.parent20 / "data" / "sources_ct.json")21_sync_lock = threading.Lock()2223_SORTS = {24 "recent": " ORDER BY first_seen DESC, price_night IS NULL, price_night ASC",25 "prix": " ORDER BY price_night IS NULL, price_night ASC",26 "prix_desc": " ORDER BY price_night IS NULL, price_night DESC",27 "note": " ORDER BY rating IS NULL, rating DESC, reviews DESC",28}293031def _row_to_dict(row) -> dict:32 d = dict(row)33 d["amenities"] = json.loads(d.get("amenities") or "[]")34 d["images"] = json.loads(d.get("images") or "[]")35 d["details"] = json.loads(d.get("details") or "{}")36 return d373839def _apply_filters(sql: str, args: list,40 region: str | None, city: str | None, type_: str | None,41 source: str | None, price_min: float | None,42 price_max: float | None, capacity_min: float | None,43 bedrooms_min: float | None, pets: str | None,44 spa: int | None, waterfront: int | None,45 q: str | None) -> str:46 if region:47 sql += " AND region=?"; args.append(region)48 if city:49 sql += " AND city LIKE ?"; args.append(f"%{city}%")50 if type_:51 sql += " AND property_type=?"; args.append(type_)52 if source:53 sql += " AND source=?"; args.append(source)54 if price_min is not None:55 sql += " AND price_night IS NOT NULL AND price_night>=?"56 args.append(price_min)57 if price_max is not None:58 sql += " AND price_night IS NOT NULL AND price_night<=?"59 args.append(price_max)60 if capacity_min is not None:61 sql += " AND capacity IS NOT NULL AND capacity>=?"62 args.append(capacity_min)63 if bedrooms_min is not None:64 sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"65 args.append(bedrooms_min)66 if pets == "oui":67 sql += " AND pets IN ('oui','conditions')"68 if spa == 1:69 sql += " AND json_extract(details,'$.spa')=1"70 if waterfront == 1:71 sql += " AND json_extract(details,'$.waterfront')=1"72 if q:73 sql += " AND (title LIKE ? OR city LIKE ? OR region LIKE ?)"74 args += [f"%{q}%"] * 375 return sql767778@router.get("/listings")79def ct_listings(80 region: str | None = None,81 city: str | None = None,82 type: str | None = None,83 source: str | None = None,84 price_min: float | None = None,85 price_max: float | None = None,86 capacity_min: float | None = None,87 bedrooms_min: float | None = None,88 pets: str | None = None,89 spa: int | None = None,90 waterfront: int | None = None,91 q: str | None = None,92 sort: str = "recent",93 limit: int = Query(24, le=200),94 offset: int = 0,95):96 if sort not in _SORTS:97 raise HTTPException(400, f"sort inconnu : {sort}")98 con = db.connect()99 sql = "SELECT * FROM st_listings WHERE active=1"100 args: list = []101 sql = _apply_filters(sql, args, region, city, type, source, price_min,102 price_max, capacity_min, bedrooms_min, pets, spa,103 waterfront, q)104 total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]105 sql += _SORTS[sort] + " LIMIT ? OFFSET ?"106 args += [limit, offset]107 rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]108 con.close()109 return {"total": total, "count": len(rows), "listings": rows}110111112@router.get("/listings.geojson")113def ct_geojson(114 region: str | None = None,115 city: str | None = None,116 type: str | None = None,117 source: str | None = None,118 price_min: float | None = None,119 price_max: float | None = None,120 capacity_min: float | None = None,121 bedrooms_min: float | None = None,122 pets: str | None = None,123 spa: int | None = None,124 waterfront: int | None = None,125 q: str | None = None,126 bbox: str | None = None,127 limit: int = Query(4000, le=10000),128):129 con = db.connect()130 sql = ("SELECT uid, title, property_type, city, region, price_night,"131 " price_label, capacity, bedrooms, rating, source, images, lat, lng"132 " FROM st_listings WHERE active=1"133 " AND lat IS NOT NULL AND lng IS NOT NULL")134 args: list = []135 if bbox:136 try:137 west, south, east, north = (float(v) for v in bbox.split(","))138 except ValueError:139 raise HTTPException(400, "bbox attendu : ouest,sud,est,nord")140 sql += " AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?"141 args += [south, north, west, east]142 sql = _apply_filters(sql, args, region, city, type, source, price_min,143 price_max, capacity_min, bedrooms_min, pets, spa,144 waterfront, q)145 total_geo = con.execute(f"SELECT COUNT(*) c FROM ({sql})",146 args).fetchone()["c"]147 sql += " ORDER BY first_seen DESC LIMIT ?"148 args.append(limit)149 features = []150 for r in con.execute(sql, args).fetchall():151 images = json.loads(r["images"] or "[]")152 features.append({153 "type": "Feature",154 "geometry": {"type": "Point",155 "coordinates": [r["lng"], r["lat"]]},156 "properties": {157 "uid": r["uid"], "title": r["title"],158 "property_type": r["property_type"], "city": r["city"],159 "region": r["region"], "price_night": r["price_night"],160 "price_label": r["price_label"], "capacity": r["capacity"],161 "bedrooms": r["bedrooms"], "rating": r["rating"],162 "source": r["source"],163 "image": images[0] if images else None,164 },165 })166 con.close()167 return {"type": "FeatureCollection", "features": features,168 "totalGeocoded": total_geo}169170171@router.get("/facets")172def ct_facets():173 con = db.connect()174 out = {175 "regions": [dict(r) for r in con.execute(176 "SELECT region, COUNT(*) n FROM st_listings WHERE active=1"177 " AND region<>'' GROUP BY region ORDER BY n DESC")],178 "types": [dict(r) for r in con.execute(179 "SELECT property_type AS type, COUNT(*) n FROM st_listings"180 " WHERE active=1 AND property_type<>''"181 " GROUP BY property_type ORDER BY n DESC")],182 "sources": [dict(r) for r in con.execute(183 "SELECT source, COUNT(*) n FROM st_listings WHERE active=1"184 " GROUP BY source ORDER BY n DESC")],185 }186 con.close()187 return out188189190@router.get("/stats")191def ct_stats():192 con = db.connect()193 row = con.execute(194 """SELECT COUNT(*) total, COUNT(DISTINCT source) sources,195 COUNT(DISTINCT region) regions,196 AVG(price_night) avg_night,197 SUM(CASE WHEN lat IS NOT NULL THEN 1 ELSE 0 END) geocoded198 FROM st_listings WHERE active=1""").fetchone()199 log = [dict(r) for r in con.execute(200 "SELECT * FROM st_sync_log ORDER BY ts DESC LIMIT 20")]201 con.close()202 return {**dict(row), "recent_syncs": log}203204205@router.get("/sources")206def ct_sources():207 from .connectors import ST_CONNECTORS208 try:209 registry = json.loads(210 SOURCES_CT_PATH.read_text(encoding="utf-8"))["sources"]211 except (OSError, ValueError, KeyError):212 registry = []213 con = db.connect()214 counts = {r["source"]: r["n"] for r in con.execute(215 "SELECT source, COUNT(*) n FROM st_listings WHERE active=1"216 " GROUP BY source")}217 last = {r["source"]: r["ts"] for r in con.execute(218 "SELECT source, MAX(ts) ts FROM st_sync_log WHERE ok=1"219 " GROUP BY source")}220 con.close()221 for s in registry:222 s["connector"] = s["id"] in ST_CONNECTORS223 s["active_listings"] = counts.get(s["id"], 0)224 s["last_sync"] = last.get(s["id"])225 return {"sources": registry}226227228def _percentile(sorted_vals: list[float], q: float) -> float:229 if not sorted_vals:230 return 0.0231 pos = (len(sorted_vals) - 1) * q232 lo, hi = int(pos), min(int(pos) + 1, len(sorted_vals) - 1)233 return sorted_vals[lo] + (sorted_vals[hi] - sorted_vals[lo]) * (pos - lo)234235236def _haversine_km(lat1, lng1, lat2, lng2) -> float:237 rl1, rl2 = math.radians(lat1), math.radians(lat2)238 dlat, dlng = rl2 - rl1, math.radians(lng2 - lng1)239 a = (math.sin(dlat / 2) ** 2240 + math.cos(rl1) * math.cos(rl2) * math.sin(dlng / 2) ** 2)241 return 6371.0 * 2 * math.asin(math.sqrt(a))242243244@router.get("/listings/{uid}/context")245def ct_listing_context(uid: str):246 """Contexte d'une fiche : analyse du prix/nuit vs segment comparable247 (région → + type → + capacité) et hébergements similaires à proximité."""248 con = db.connect()249 row = con.execute("SELECT * FROM st_listings WHERE uid=?",250 (uid,)).fetchone()251 if row is None:252 con.close()253 raise HTTPException(404, "Hébergement introuvable")254 l = dict(row)255256 # --- analyse de prix : segment le plus précis avec ≥ 12 comparables ------257 price_block = None258 if l["price_night"] is not None and l["region"]:259 candidates: list[tuple[str, str, list]] = []260 base_sql = (" AND region=?")261 base_args: list = [l["region"]]262 if l["property_type"] and l["capacity"]:263 candidates.append((264 f"{l['property_type']} · {int(l['capacity'])}±2 pers. · {l['region']}",265 base_sql + " AND property_type=? AND capacity BETWEEN ? AND ?",266 base_args + [l["property_type"], l["capacity"] - 2,267 l["capacity"] + 2]))268 if l["property_type"]:269 candidates.append((270 f"{l['property_type']} · {l['region']}",271 base_sql + " AND property_type=?",272 base_args + [l["property_type"]]))273 candidates.append((l["region"], base_sql, base_args))274275 for label, extra, args in candidates:276 vals = [r["p"] for r in con.execute(277 "SELECT price_night p FROM st_listings WHERE active=1"278 " AND price_night IS NOT NULL AND uid<>?" + extra279 + " ORDER BY price_night", [uid] + args)]280 if len(vals) < 12:281 continue282 med = _percentile(vals, 0.5)283 deviation = (l["price_night"] - med) / med if med else None284 verdict = None285 if deviation is not None:286 verdict = ("sous" if deviation <= -0.15287 else "dans" if deviation < 0.12 else "dessus")288 # histogramme 12 classes entre p5 et p95 (queues écrasées)289 lo, hi = _percentile(vals, 0.05), _percentile(vals, 0.95)290 bins = []291 if hi > lo:292 step = (hi - lo) / 12293 edges = [lo + i * step for i in range(13)]294 counts = [0] * 12295 for v in vals:296 i = min(11, max(0, int((v - lo) / step)))297 counts[i] += 1298 bins = [{"x0": round(edges[i]), "x1": round(edges[i + 1]),299 "n": counts[i]} for i in range(12)]300 rank = sum(1 for v in vals if v <= l["price_night"])301 price_block = {302 "segment": label, "n": len(vals),303 "median": round(med), "p25": round(_percentile(vals, 0.25)),304 "p75": round(_percentile(vals, 0.75)),305 "deviation": round(deviation, 3) if deviation is not None else None,306 "verdict": verdict,307 "percentile": round(100 * rank / len(vals)),308 "histogram": bins,309 }310 break311312 # --- hébergements similaires ---------------------------------------------313 similar: list[dict] = []314 if l["lat"] is not None and l["lng"] is not None:315 dlat = 0.45 # ≈ 50 km316 dlng = dlat / max(0.2, math.cos(math.radians(l["lat"])))317 rows = con.execute(318 "SELECT * FROM st_listings WHERE active=1 AND uid<>?"319 " AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?"320 " AND images IS NOT NULL AND images<>'[]' LIMIT 400",321 (uid, l["lat"] - dlat, l["lat"] + dlat,322 l["lng"] - dlng, l["lng"] + dlng)).fetchall()323 scored = []324 for r in rows:325 km = _haversine_km(l["lat"], l["lng"], r["lat"], r["lng"])326 if km > 50:327 continue328 same_type = (l["property_type"]329 and r["property_type"] == l["property_type"])330 scored.append((0 if same_type else 1, km, r))331 scored.sort(key=lambda t: (t[0], t[1]))332 for _, km, r in scored[:8]:333 d = _row_to_dict(r)334 d["distance_km"] = round(km, 1)335 similar.append(d)336 if not similar and l["region"]:337 sql = ("SELECT * FROM st_listings WHERE active=1 AND uid<>?"338 " AND region=? AND images IS NOT NULL AND images<>'[]'")339 args = [uid, l["region"]]340 if l["property_type"]:341 sql += " AND property_type=?"342 args.append(l["property_type"])343 sql += " ORDER BY rating IS NULL, rating DESC, first_seen DESC LIMIT 8"344 similar = [_row_to_dict(r) for r in con.execute(sql, args)]345346 con.close()347 return {"price": price_block, "similar": similar}348349350@router.get("/listings/{uid}")351def ct_listing(uid: str):352 con = db.connect()353 row = con.execute("SELECT * FROM st_listings WHERE uid=?",354 (uid,)).fetchone()355 con.close()356 if row is None:357 raise HTTPException(404, "Hébergement introuvable")358 return _row_to_dict(row)359360361@router.post("/sync")362def ct_trigger_sync(background: BackgroundTasks, source: str | None = None):363 from . import ingest364 def _job():365 with _sync_lock:366 ingest.run([source] if source else None)367 background.add_task(_job)368 return {"status": "démarré", "source": source or "toutes"}369