# -----------------------------------------------------------------------------
# Ora-Ka — Moteur de recherche transversal hybride (exact + sémantique)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
#
# Étage 1 : correspondances exactes (LIKE / FTS5) dans les 5 bases
# Étage 2 : découverte sémantique (embeddings OpenAI, oraka.semantic)
# Les deux étages sont fusionnés : l'exact d'abord, le sémantique ensuite.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import re
import sqlite3
from pathlib import Path

from oraka import semantic

ROOT = Path(__file__).resolve().parent.parent
APPS_DIR = ROOT / "apps"

DBS = {
    "immo": APPS_DIR / "immo" / "data" / "immoka.db",
    "lou": APPS_DIR / "lou" / "data" / "louka.db",
    "fabri": APPS_DIR / "fabri" / "data" / "fabrika.db",
    "auto": APPS_DIR / "auto" / "data" / "autoka.db",
    "food": APPS_DIR / "food" / "data" / "foodka.db",
}

META = {
    "immo": {"label": "Propriétés", "color": "#e23744", "more": "/immo/?q="},
    "lou": {"label": "Logements", "color": "#1c5c41", "more": "/lou/?q="},
    "fabri": {"label": "Produits QC", "color": "#c4532e", "more": "/fabri/produits?q="},
    "auto": {"label": "Véhicules", "color": "#ff5a2a", "more": "/auto/?q="},
    "food": {"label": "Épicerie", "color": "#1f7a4d", "more": "/food/?q="},
}

APP_ORDER = ["immo", "lou", "auto", "fabri", "food"]

_FTS_SAFE = re.compile(r"[^0-9A-Za-zÀ-ÖØ-öø-ÿ' -]")
_SEM_FLOOR = 0.32  # similarité minimale pour la découverte sémantique


def _ro(db: Path) -> sqlite3.Connection:
    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True, timeout=5)
    con.row_factory = sqlite3.Row
    return con


def _first_image(raw) -> str | None:
    try:
        imgs = json.loads(raw) if isinstance(raw, str) else raw
        if isinstance(imgs, list) and imgs:
            return str(imgs[0])
    except Exception:
        pass
    return None


def _money(v) -> str | None:
    if v is None:
        return None
    try:
        v = float(v)
    except (TypeError, ValueError):
        return None
    if v == int(v):
        s = f"{int(v):,}".replace(",", " ")
    else:
        s = f"{v:,.2f}".replace(",", " ").replace(".", ",")
    return f"{s} $"


def _fts_query(q: str) -> str:
    words = _FTS_SAFE.sub(" ", q).split()[:8]
    return " ".join(f'"{w}"*' for w in words if w)


# ---------------------------------------------------------------------------
# Mise en forme d'une ligne SQL -> carte de résultat
# ---------------------------------------------------------------------------

def _shape_immo(r) -> dict:
    bits = [b for b in (r["property_type"],
                        f"{int(r['bedrooms'])} ch." if r["bedrooms"] else None,
                        r["city"]) if b]
    return {"uid": r["uid"], "title": r["title"] or r["address"] or "Propriété",
            "sub": " · ".join(bits), "price": r["price_label"] or _money(r["price"]),
            "image": _first_image(r["images"]), "href": f"/immo/propriete/{r['uid']}"}


def _shape_lou(r) -> dict:
    m = _money(r["price"])
    price = r["price_label"] or (f"{m}/mois" if m else None)
    bits = [b for b in (r["unit_type"], r["sector"] or r["city"]) if b]
    return {"uid": r["uid"], "title": r["title"] or r["address"] or "Logement",
            "sub": " · ".join(bits), "price": price,
            "image": _first_image(r["images"]), "href": f"/lou/logement/{r['uid']}"}


def _shape_fabri(r) -> dict:
    return {"uid": r["uid"], "title": r["title"],
            "sub": " · ".join(b for b in (r["store_name"], r["region"]) if b),
            "price": _money(r["price"]), "image": _first_image(r["images"]),
            "href": f"/fabri/produits/{r['uid']}"}


def _shape_auto(r) -> dict:
    km = f"{int(r['mileage_km']):,} km".replace(",", " ") if r["mileage_km"] else None
    bits = [b for b in (km, r["city"] or r["dealer_name"]) if b]
    title = r["title"] or f"{r['year'] or ''} {r['make'] or ''} {r['model'] or ''}".strip()
    return {"uid": r["uid"], "title": title, "sub": " · ".join(bits),
            "price": r["price_label"] or _money(r["price"]),
            "image": _first_image(r["images"]), "href": f"/auto/vehicule/{r['uid']}"}


def _shape_food(r) -> dict:
    bits = [b for b in (r["brand"], r["size_label"],
                        r["source"].replace("_", " ").title() if r["source"] else None) if b]
    price = _money(r["price"])
    if price and r["on_sale"]:
        price += " 🔥"
    return {"uid": r["uid"], "title": r["name"], "sub": " · ".join(bits),
            "price": price, "image": _first_image(r["images"]),
            "href": f"/food/produit/{r['uid']}"}


_FIELDS = {
    "immo": ("listings", "uid, title, address, city, sector, property_type, bedrooms,"
             " bathrooms, price, price_label, images", _shape_immo),
    "lou": ("listings", "uid, title, address, city, sector, unit_type, price,"
            " price_label, images", _shape_lou),
    "auto": ("vehicles", "uid, title, make, model, year, price, price_label,"
             " mileage_km, city, dealer_name, images", _shape_auto),
    "food": ("products", "uid, name, brand, source, size_label, price, on_sale,"
             " images", _shape_food),
}


# ---------------------------------------------------------------------------
# Étage 1 — recherche exacte par univers (LIKE / FTS5)
# ---------------------------------------------------------------------------

_KW_COLS = {
    "immo": ("title", "address", "city", "sector", "mls"),
    "lou": ("title", "address", "sector", "city"),
    "auto": ("title", "make", "model", "dealer_name", "city"),
    "food": ("name", "brand", "category_raw"),
}

_KW_BASE = {
    "immo": "active=1 AND dup_hidden=0 AND price IS NOT NULL",
    "lou": "active=1",
    "auto": "active=1",
    "food": "active=1",
}

_KW_ORDER = {
    "immo": "ORDER BY last_seen DESC",
    "lou": "ORDER BY price IS NULL, last_seen DESC",
    "auto": "ORDER BY price IS NULL, last_seen DESC",
    "food": "ORDER BY price IS NULL, price ASC",
}


def _strip_accents(s: str) -> str:
    import unicodedata
    return "".join(c for c in unicodedata.normalize("NFD", s)
                   if unicodedata.category(c) != "Mn")


_STOP = {"a", "à", "au", "aux", "de", "des", "du", "en", "et", "la", "le", "les",
         "un", "une", "avec", "pour", "près", "pres", "sur", "dans", "d", "l"}


def _tokens(q: str) -> list[str]:
    words = re.split(r"[^0-9A-Za-zÀ-ÖØ-öø-ÿ½'-]+", q)
    out = []
    for w in words:
        w = w.strip("'-")
        if len(w) >= 2 and w.lower() not in _STOP:
            out.append(w)
    return out[:8]


def _kw_search(app: str, q: str, limit: int) -> tuple[int, list[dict]]:
    """Recherche exacte par jetons : chaque mot significatif doit apparaître."""
    toks = _tokens(q)
    if app == "fabri":
        match = _fts_query(q)
        if not match:
            return 0, []
        con = _ro(DBS["fabri"])
        try:
            total = con.execute(
                "SELECT COUNT(*) FROM products_fts f JOIN products p ON p.uid=f.uid"
                " WHERE products_fts MATCH ? AND p.active=1", (match,)).fetchone()[0]
            rows = con.execute(
                "SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,"
                " s.region FROM products_fts f JOIN products p ON p.uid=f.uid"
                " LEFT JOIN stores s ON s.id=p.store_id"
                " WHERE products_fts MATCH ? AND p.active=1 ORDER BY rank LIMIT ?",
                (match, limit)).fetchall()
        finally:
            con.close()
        return total, [_shape_fabri(r) for r in rows]
    if not toks:
        return 0, []
    table, fields, shape = _FIELDS[app]
    cols = _KW_COLS[app]
    clauses, args = [], []
    for t in toks:
        clauses.append("(" + " OR ".join(f"{c} LIKE ?" for c in cols) + ")")
        args.extend([f"%{t}%"] * len(cols))
    where = _KW_BASE[app] + " AND " + " AND ".join(clauses)
    con = _ro(DBS[app])
    try:
        total = con.execute(f"SELECT COUNT(*) FROM {table} WHERE {where}",
                            args).fetchone()[0]
        rows = con.execute(f"SELECT {fields} FROM {table} WHERE {where}"
                           f" {_KW_ORDER[app]} LIMIT ?", args + [limit]).fetchall()
    finally:
        con.close()
    return total, [shape(r) for r in rows]


# ---------------------------------------------------------------------------
# Étage 2 — hydratation des uid retenus par l'index sémantique
# ---------------------------------------------------------------------------

def _hydrate(app: str, uids: list[str]) -> dict[str, dict]:
    if not uids:
        return {}
    marks = ",".join("?" * len(uids))
    con = _ro(DBS[app])
    try:
        if app == "fabri":
            rows = con.execute(
                f"SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,"
                f" s.region FROM products p LEFT JOIN stores s ON s.id=p.store_id"
                f" WHERE p.uid IN ({marks}) AND p.active=1", uids).fetchall()
            return {r["uid"]: _shape_fabri(r) for r in rows}
        table, fields, shape = _FIELDS[app]
        rows = con.execute(f"SELECT {fields} FROM {table}"
                           f" WHERE uid IN ({marks}) AND active=1", uids).fetchall()
        return {r["uid"]: shape(r) for r in rows}
    finally:
        con.close()


# ---------------------------------------------------------------------------
# Fusion hybride
# ---------------------------------------------------------------------------

def _coverage(it: dict, toks_norm: list[str]) -> float:
    """Fraction des jetons de la requête présents dans le titre/sous-titre."""
    if not toks_norm:
        return 0.0
    hay = _strip_accents(f"{it.get('title', '')} {it.get('sub', '')}").lower()
    return sum(1 for t in toks_norm if t in hay) / len(toks_norm)


def search_hits(q: str, scope: str | None = None, limit: int = 60) -> dict:
    q = (q or "").strip()
    apps = [scope] if scope in DBS else APP_ORDER
    apps = [a for a in apps if DBS[a].exists()]
    if not q:
        return {"q": q, "semantic": False, "hits": [], "counts": {},
                "totals": {}, "more": {}}

    toks = _tokens(q)
    toks_norm = [_strip_accents(t).lower() for t in toks]
    q_norm = _strip_accents(q).lower()

    # --- Candidats exacts (tous les jetons requis) ---
    kw_limit = 60 if scope else 30
    totals: dict[str, int] = {}
    cands: dict[tuple[str, str], dict] = {}
    kw_set: set[tuple[str, str]] = set()
    for app in apps:
        try:
            total, items = _kw_search(app, q, kw_limit)
        except Exception:
            total, items = 0, []
        totals[app] = total
        for it in items:
            key = (app, it["uid"])
            it = dict(it)
            it["app"] = app
            cands[key] = it
            kw_set.add(key)

    # --- Candidats sémantiques ---
    sem_used = False
    qv = semantic.query_vector(q) if semantic.available() else None
    sem_scores: dict[tuple[str, str], float] = {}
    if qv is not None:
        per_app = 50 if scope else 30
        sem = semantic.semantic_top(q, apps, per_app=per_app)
        by_app: dict[str, list[str]] = {}
        for app, pairs in sem.items():
            for uid, score in pairs:
                if score >= _SEM_FLOOR:
                    sem_scores[(app, uid)] = score
                    if (app, uid) not in cands:
                        by_app.setdefault(app, []).append(uid)
        for app, uids in by_app.items():
            for uid, it in _hydrate(app, uids).items():
                it = dict(it)
                it["app"] = app
                cands[(app, uid)] = it
        # similarité des hits exacts absents du top sémantique (lookup par uid)
        missing: dict[str, list[str]] = {}
        for (app, uid) in kw_set:
            if (app, uid) not in sem_scores:
                missing.setdefault(app, []).append(uid)
        for app, uids in missing.items():
            for uid, s in semantic.sims_for(app, uids, qv).items():
                sem_scores[(app, uid)] = s
        sem_used = bool(sem_scores)

    if not cands:
        return {"q": q, "semantic": sem_used, "hits": [], "counts": {},
                "totals": totals,
                "more": {a: META[a]["more"] + q.replace(" ", "+") for a in apps},
                "meta": {a: {"label": META[a]["label"], "color": META[a]["color"]}
                         for a in DBS}}

    # --- Affinité d'univers (intention) : meilleur score sémantique par app ---
    aff: dict[str, float] = {}
    for (app, uid), s in sem_scores.items():
        aff[app] = max(aff.get(app, 0.0), s)
    best_aff = max(aff.values()) if aff else 0.0

    # --- Score unifié ---
    scored: list[tuple[float, dict]] = []
    for key, it in cands.items():
        app, uid = key
        sem_s = sem_scores.get(key)
        cov = _coverage(it, toks_norm)
        exact_kw = key in kw_set
        phrase = 1.0 if q_norm and q_norm in _strip_accents(
            str(it.get("title", ""))).lower() else 0.0
        base = sem_s if sem_s is not None else (0.42 if exact_kw else 0.0)
        score = (base
                 + 0.22 * cov
                 + 0.10 * phrase
                 + (0.05 if exact_kw else 0.0)
                 + (0.08 * (aff.get(app, best_aff) - best_aff) if best_aff else 0.0))
        it["match"] = "exact" if exact_kw and cov >= 0.99 else (
            "exact" if exact_kw and sem_s is None else
            ("exact" if exact_kw else "semantique"))
        it["score"] = round(max(score, 0.0), 3)
        scored.append((score, it))

    scored.sort(key=lambda x: -x[0])
    best = scored[0][0]
    hits = [it for s, it in scored if s >= best - 0.22][:limit]

    # Garantie de diversité : tout univers pertinent (à ≤0.30 du meilleur) est
    # représenté dans la première douzaine, même si un univers volumineux domine.
    head_apps = {it["app"] for it in hits[:12]}
    inserts = []
    for app in apps:
        if app in head_apps:
            continue
        cand = next((it for s, it in scored
                     if it["app"] == app and s >= best - 0.30), None)
        if cand is not None:
            inserts.append(cand)
    for i, cand in enumerate(inserts):
        pos = min(6 + i * 3, len(hits))
        if cand in hits:
            hits.remove(cand)
        hits.insert(pos, cand)
    hits = hits[:limit]

    counts: dict[str, int] = {}
    for it in hits:
        counts[it["app"]] = counts.get(it["app"], 0) + 1
    return {
        "q": q,
        "semantic": sem_used,
        "hits": hits,
        "counts": counts,
        "totals": totals,
        "more": {a: META[a]["more"] + q.replace(" ", "+") for a in apps},
        "meta": {a: {"label": META[a]["label"], "color": META[a]["color"]} for a in DBS},
    }