spb/ora-ka Public

1# -----------------------------------------------------------------------------2# Ora-Ka — Moteur de recherche transversal hybride (exact + sémantique)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4#5# Étage 1 : correspondances exactes (LIKE / FTS5) dans les 5 bases6# Étage 2 : découverte sémantique (embeddings OpenAI, oraka.semantic)7# Les deux étages sont fusionnés : l'exact d'abord, le sémantique ensuite.8# -----------------------------------------------------------------------------9from __future__ import annotations1011import json12import re13import sqlite314from pathlib import Path1516from oraka import semantic1718ROOT = Path(__file__).resolve().parent.parent19APPS_DIR = ROOT / "apps"2021DBS = {22    "immo": APPS_DIR / "immo" / "data" / "immoka.db",23    "lou": APPS_DIR / "lou" / "data" / "louka.db",24    "fabri": APPS_DIR / "fabri" / "data" / "fabrika.db",25    "auto": APPS_DIR / "auto" / "data" / "autoka.db",26    "food": APPS_DIR / "food" / "data" / "foodka.db",27}2829META = {30    "immo": {"label": "Propriétés", "color": "#e23744", "more": "/immo/?q="},31    "lou": {"label": "Logements", "color": "#1c5c41", "more": "/lou/?q="},32    "fabri": {"label": "Produits QC", "color": "#c4532e", "more": "/fabri/produits?q="},33    "auto": {"label": "Véhicules", "color": "#ff5a2a", "more": "/auto/?q="},34    "food": {"label": "Épicerie", "color": "#1f7a4d", "more": "/food/?q="},35}3637APP_ORDER = ["immo", "lou", "auto", "fabri", "food"]3839_FTS_SAFE = re.compile(r"[^0-9A-Za-zÀ-ÖØ-öø-ÿ' -]")40_SEM_FLOOR = 0.32  # similarité minimale pour la découverte sémantique414243def _ro(db: Path) -> sqlite3.Connection:44    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True, timeout=5)45    con.row_factory = sqlite3.Row46    return con474849def _first_image(raw) -> str | None:50    try:51        imgs = json.loads(raw) if isinstance(raw, str) else raw52        if isinstance(imgs, list) and imgs:53            return str(imgs[0])54    except Exception:55        pass56    return None575859def _money(v) -> str | None:60    if v is None:61        return None62    try:63        v = float(v)64    except (TypeError, ValueError):65        return None66    if v == int(v):67        s = f"{int(v):,}".replace(",", " ")68    else:69        s = f"{v:,.2f}".replace(",", " ").replace(".", ",")70    return f"{s} $"717273def _fts_query(q: str) -> str:74    words = _FTS_SAFE.sub(" ", q).split()[:8]75    return " ".join(f'"{w}"*' for w in words if w)767778# ---------------------------------------------------------------------------79# Mise en forme d'une ligne SQL -> carte de résultat80# ---------------------------------------------------------------------------8182def _shape_immo(r) -> dict:83    bits = [b for b in (r["property_type"],84                        f"{int(r['bedrooms'])} ch." if r["bedrooms"] else None,85                        r["city"]) if b]86    return {"uid": r["uid"], "title": r["title"] or r["address"] or "Propriété",87            "sub": " · ".join(bits), "price": r["price_label"] or _money(r["price"]),88            "image": _first_image(r["images"]), "href": f"/immo/propriete/{r['uid']}"}899091def _shape_lou(r) -> dict:92    m = _money(r["price"])93    price = r["price_label"] or (f"{m}/mois" if m else None)94    bits = [b for b in (r["unit_type"], r["sector"] or r["city"]) if b]95    return {"uid": r["uid"], "title": r["title"] or r["address"] or "Logement",96            "sub": " · ".join(bits), "price": price,97            "image": _first_image(r["images"]), "href": f"/lou/logement/{r['uid']}"}9899100def _shape_fabri(r) -> dict:101    return {"uid": r["uid"], "title": r["title"],102            "sub": " · ".join(b for b in (r["store_name"], r["region"]) if b),103            "price": _money(r["price"]), "image": _first_image(r["images"]),104            "href": f"/fabri/produits/{r['uid']}"}105106107def _shape_auto(r) -> dict:108    km = f"{int(r['mileage_km']):,} km".replace(",", " ") if r["mileage_km"] else None109    bits = [b for b in (km, r["city"] or r["dealer_name"]) if b]110    title = r["title"] or f"{r['year'] or ''} {r['make'] or ''} {r['model'] or ''}".strip()111    return {"uid": r["uid"], "title": title, "sub": " · ".join(bits),112            "price": r["price_label"] or _money(r["price"]),113            "image": _first_image(r["images"]), "href": f"/auto/vehicule/{r['uid']}"}114115116def _shape_food(r) -> dict:117    bits = [b for b in (r["brand"], r["size_label"],118                        r["source"].replace("_", " ").title() if r["source"] else None) if b]119    price = _money(r["price"])120    if price and r["on_sale"]:121        price += " 🔥"122    return {"uid": r["uid"], "title": r["name"], "sub": " · ".join(bits),123            "price": price, "image": _first_image(r["images"]),124            "href": f"/food/produit/{r['uid']}"}125126127_FIELDS = {128    "immo": ("listings", "uid, title, address, city, sector, property_type, bedrooms,"129             " bathrooms, price, price_label, images", _shape_immo),130    "lou": ("listings", "uid, title, address, city, sector, unit_type, price,"131            " price_label, images", _shape_lou),132    "auto": ("vehicles", "uid, title, make, model, year, price, price_label,"133             " mileage_km, city, dealer_name, images", _shape_auto),134    "food": ("products", "uid, name, brand, source, size_label, price, on_sale,"135             " images", _shape_food),136}137138139# ---------------------------------------------------------------------------140# Étage 1 — recherche exacte par univers (LIKE / FTS5)141# ---------------------------------------------------------------------------142143_KW_COLS = {144    "immo": ("title", "address", "city", "sector", "mls"),145    "lou": ("title", "address", "sector", "city"),146    "auto": ("title", "make", "model", "dealer_name", "city"),147    "food": ("name", "brand", "category_raw"),148}149150_KW_BASE = {151    "immo": "active=1 AND dup_hidden=0 AND price IS NOT NULL",152    "lou": "active=1",153    "auto": "active=1",154    "food": "active=1",155}156157_KW_ORDER = {158    "immo": "ORDER BY last_seen DESC",159    "lou": "ORDER BY price IS NULL, last_seen DESC",160    "auto": "ORDER BY price IS NULL, last_seen DESC",161    "food": "ORDER BY price IS NULL, price ASC",162}163164165def _strip_accents(s: str) -> str:166    import unicodedata167    return "".join(c for c in unicodedata.normalize("NFD", s)168                   if unicodedata.category(c) != "Mn")169170171_STOP = {"a", "à", "au", "aux", "de", "des", "du", "en", "et", "la", "le", "les",172         "un", "une", "avec", "pour", "près", "pres", "sur", "dans", "d", "l"}173174175def _tokens(q: str) -> list[str]:176    words = re.split(r"[^0-9A-Za-zÀ-ÖØ-öø-ÿ½'-]+", q)177    out = []178    for w in words:179        w = w.strip("'-")180        if len(w) >= 2 and w.lower() not in _STOP:181            out.append(w)182    return out[:8]183184185def _kw_search(app: str, q: str, limit: int) -> tuple[int, list[dict]]:186    """Recherche exacte par jetons : chaque mot significatif doit apparaître."""187    toks = _tokens(q)188    if app == "fabri":189        match = _fts_query(q)190        if not match:191            return 0, []192        con = _ro(DBS["fabri"])193        try:194            total = con.execute(195                "SELECT COUNT(*) FROM products_fts f JOIN products p ON p.uid=f.uid"196                " WHERE products_fts MATCH ? AND p.active=1", (match,)).fetchone()[0]197            rows = con.execute(198                "SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,"199                " s.region FROM products_fts f JOIN products p ON p.uid=f.uid"200                " LEFT JOIN stores s ON s.id=p.store_id"201                " WHERE products_fts MATCH ? AND p.active=1 ORDER BY rank LIMIT ?",202                (match, limit)).fetchall()203        finally:204            con.close()205        return total, [_shape_fabri(r) for r in rows]206    if not toks:207        return 0, []208    table, fields, shape = _FIELDS[app]209    cols = _KW_COLS[app]210    clauses, args = [], []211    for t in toks:212        clauses.append("(" + " OR ".join(f"{c} LIKE ?" for c in cols) + ")")213        args.extend([f"%{t}%"] * len(cols))214    where = _KW_BASE[app] + " AND " + " AND ".join(clauses)215    con = _ro(DBS[app])216    try:217        total = con.execute(f"SELECT COUNT(*) FROM {table} WHERE {where}",218                            args).fetchone()[0]219        rows = con.execute(f"SELECT {fields} FROM {table} WHERE {where}"220                           f" {_KW_ORDER[app]} LIMIT ?", args + [limit]).fetchall()221    finally:222        con.close()223    return total, [shape(r) for r in rows]224225226# ---------------------------------------------------------------------------227# Étage 2 — hydratation des uid retenus par l'index sémantique228# ---------------------------------------------------------------------------229230def _hydrate(app: str, uids: list[str]) -> dict[str, dict]:231    if not uids:232        return {}233    marks = ",".join("?" * len(uids))234    con = _ro(DBS[app])235    try:236        if app == "fabri":237            rows = con.execute(238                f"SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,"239                f" s.region FROM products p LEFT JOIN stores s ON s.id=p.store_id"240                f" WHERE p.uid IN ({marks}) AND p.active=1", uids).fetchall()241            return {r["uid"]: _shape_fabri(r) for r in rows}242        table, fields, shape = _FIELDS[app]243        rows = con.execute(f"SELECT {fields} FROM {table}"244                           f" WHERE uid IN ({marks}) AND active=1", uids).fetchall()245        return {r["uid"]: shape(r) for r in rows}246    finally:247        con.close()248249250# ---------------------------------------------------------------------------251# Fusion hybride252# ---------------------------------------------------------------------------253254def _coverage(it: dict, toks_norm: list[str]) -> float:255    """Fraction des jetons de la requête présents dans le titre/sous-titre."""256    if not toks_norm:257        return 0.0258    hay = _strip_accents(f"{it.get('title', '')} {it.get('sub', '')}").lower()259    return sum(1 for t in toks_norm if t in hay) / len(toks_norm)260261262def search_hits(q: str, scope: str | None = None, limit: int = 60) -> dict:263    q = (q or "").strip()264    apps = [scope] if scope in DBS else APP_ORDER265    apps = [a for a in apps if DBS[a].exists()]266    if not q:267        return {"q": q, "semantic": False, "hits": [], "counts": {},268                "totals": {}, "more": {}}269270    toks = _tokens(q)271    toks_norm = [_strip_accents(t).lower() for t in toks]272    q_norm = _strip_accents(q).lower()273274    # --- Candidats exacts (tous les jetons requis) ---275    kw_limit = 60 if scope else 30276    totals: dict[str, int] = {}277    cands: dict[tuple[str, str], dict] = {}278    kw_set: set[tuple[str, str]] = set()279    for app in apps:280        try:281            total, items = _kw_search(app, q, kw_limit)282        except Exception:283            total, items = 0, []284        totals[app] = total285        for it in items:286            key = (app, it["uid"])287            it = dict(it)288            it["app"] = app289            cands[key] = it290            kw_set.add(key)291292    # --- Candidats sémantiques ---293    sem_used = False294    qv = semantic.query_vector(q) if semantic.available() else None295    sem_scores: dict[tuple[str, str], float] = {}296    if qv is not None:297        per_app = 50 if scope else 30298        sem = semantic.semantic_top(q, apps, per_app=per_app)299        by_app: dict[str, list[str]] = {}300        for app, pairs in sem.items():301            for uid, score in pairs:302                if score >= _SEM_FLOOR:303                    sem_scores[(app, uid)] = score304                    if (app, uid) not in cands:305                        by_app.setdefault(app, []).append(uid)306        for app, uids in by_app.items():307            for uid, it in _hydrate(app, uids).items():308                it = dict(it)309                it["app"] = app310                cands[(app, uid)] = it311        # similarité des hits exacts absents du top sémantique (lookup par uid)312        missing: dict[str, list[str]] = {}313        for (app, uid) in kw_set:314            if (app, uid) not in sem_scores:315                missing.setdefault(app, []).append(uid)316        for app, uids in missing.items():317            for uid, s in semantic.sims_for(app, uids, qv).items():318                sem_scores[(app, uid)] = s319        sem_used = bool(sem_scores)320321    if not cands:322        return {"q": q, "semantic": sem_used, "hits": [], "counts": {},323                "totals": totals,324                "more": {a: META[a]["more"] + q.replace(" ", "+") for a in apps},325                "meta": {a: {"label": META[a]["label"], "color": META[a]["color"]}326                         for a in DBS}}327328    # --- Affinité d'univers (intention) : meilleur score sémantique par app ---329    aff: dict[str, float] = {}330    for (app, uid), s in sem_scores.items():331        aff[app] = max(aff.get(app, 0.0), s)332    best_aff = max(aff.values()) if aff else 0.0333334    # --- Score unifié ---335    scored: list[tuple[float, dict]] = []336    for key, it in cands.items():337        app, uid = key338        sem_s = sem_scores.get(key)339        cov = _coverage(it, toks_norm)340        exact_kw = key in kw_set341        phrase = 1.0 if q_norm and q_norm in _strip_accents(342            str(it.get("title", ""))).lower() else 0.0343        base = sem_s if sem_s is not None else (0.42 if exact_kw else 0.0)344        score = (base345                 + 0.22 * cov346                 + 0.10 * phrase347                 + (0.05 if exact_kw else 0.0)348                 + (0.08 * (aff.get(app, best_aff) - best_aff) if best_aff else 0.0))349        it["match"] = "exact" if exact_kw and cov >= 0.99 else (350            "exact" if exact_kw and sem_s is None else351            ("exact" if exact_kw else "semantique"))352        it["score"] = round(max(score, 0.0), 3)353        scored.append((score, it))354355    scored.sort(key=lambda x: -x[0])356    best = scored[0][0]357    hits = [it for s, it in scored if s >= best - 0.22][:limit]358359    # Garantie de diversité : tout univers pertinent (à ≤0.30 du meilleur) est360    # représenté dans la première douzaine, même si un univers volumineux domine.361    head_apps = {it["app"] for it in hits[:12]}362    inserts = []363    for app in apps:364        if app in head_apps:365            continue366        cand = next((it for s, it in scored367                     if it["app"] == app and s >= best - 0.30), None)368        if cand is not None:369            inserts.append(cand)370    for i, cand in enumerate(inserts):371        pos = min(6 + i * 3, len(hits))372        if cand in hits:373            hits.remove(cand)374        hits.insert(pos, cand)375    hits = hits[:limit]376377    counts: dict[str, int] = {}378    for it in hits:379        counts[it["app"]] = counts.get(it["app"], 0) + 1380    return {381        "q": q,382        "semantic": sem_used,383        "hits": hits,384        "counts": counts,385        "totals": totals,386        "more": {a: META[a]["more"] + q.replace(" ", "+") for a in apps},387        "meta": {a: {"label": META[a]["label"], "color": META[a]["color"]} for a in DBS},388    }389