spb/fabri-ka

1#!/usr/bin/env python32"""Phase 2 — backfill qualité (2026-08-19).34Applique aux données DÉJÀ en base les règles désormais appliquées à5l'ingestion (schema.py) :671. Produits :8   - prix placeholders (999 999 $…) -> price=NULL + price_on_request=1 ;9   - produits sans prix -> price_on_request=1 ;10   - prix > 100 000 $ -> quarantine:prix_hors_bornes ;11   - titre vide -> quarantine:titre_vide ;12   - cartes-cadeaux / ateliers-cours / abonnements / billets / produits test13     -> excluded:<raison> (marqués, jamais supprimés, réintégrables).142. Boutiques :15   - renommage des noms génériques d'annuaire (« Créateurs », « Complices »…)16     et des groupes dupliqués via og:site_name du cache d'enrichissement17     (data/enrich_cache/<dom>.json) — registre + DB + réindexation FTS ;18   - store_kind (fabricant / revendeur / collectif / hors_mission) :19     * revendeur : origin_evidence du registre contenant « revend » +20       liste curée (marques tierces dominantes constatées en base) ;21     * collectif : boutiques multi-créateurs connues ;22     * hors_mission : transport/billetterie (produits exclus en masse).23   - postal_prefix recopié du registre vers la DB (upsert le maintient ensuite).2425Idempotent ; usage : .venv/bin/python scripts/backfill_quality.py [--dry-run]26"""27import argparse28import json29import os30import re31import sys3233ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))34sys.path.insert(0, ROOT)3536from fabrika import db as fdb  # noqa: E40237from fabrika.schema import classify_listing, is_placeholder_price, PRICE_SANE_MAX  # noqa: E4023839REG_PATH = os.path.join(ROOT, "data", "stores.json")40ENRICH_CACHE = os.path.join(ROOT, "data", "enrich_cache")4142# Noms de boutiques génériques hérités des rubriques d'annuaires43BAD_SITE_NAMES = {"your site title", "site title", "home", "home page",44                  "accueil", "untitled", "my site", "mon site"}4546GENERIC_NAMES = {"créateurs", "createurs", "producteurs/transformateurs",47                 "producteurs", "transformateurs", "complices", "accueil",48                 "home", "boutique", "produits", "shop", "menu", "à propos"}4950# revendeurs constatés (marques tierces dominantes dans le catalogue :51# Tissot, BIBS, Rieker, Natural Factors, Pacsafe… — voir 05-ENRICHISSEMENT-RAPPORT)52CURATED_REVENDEUR = {53    "lamaisondubleuet.com", "mondeavie.ca", "lesptitsmosus.com",54    "grenierboutique.ca", "yellowshoes.com", "bijouteriejodoin.com",55    "eugeneallard.com", "remorquetrailer.com", "doggoboutique.ca",56    "boiteavins.com",57}58# boutiques multi-créateurs (vendor = fabricant réel)59CURATED_COLLECTIF = {"paperole.com", "signelocal.com", "wachiya.com",60                     "galerieiris.com"}61# hors mission fabricant (transport, activités, billetterie)62CURATED_HORS_MISSION = {"traversiers.com", "rtcbq.com", "raftingmomentum.com"}636465def main():66    ap = argparse.ArgumentParser()67    ap.add_argument("--dry-run", action="store_true")68    args = ap.parse_args()6970    con = fdb.connect()7172    # ------------------------------------------------------------------ produits73    stats = {"placeholder": 0, "on_request": 0, "quarantine_prix": 0,74             "quarantine_titre": 0}75    excl = {}76    updates = []77    for r in con.execute("SELECT uid, title, product_type, price, price_max, "78                         "details, listing_status, price_on_request FROM products"):79        uid, title, ptype, price = r["uid"], r["title"] or "", r["product_type"] or "", r["price"]80        new_price, new_pmax = price, r["price_max"]81        details = r["details"]82        on_req = 083        if new_price is not None and new_price <= 0:      # 0 $ n'est pas un prix84            new_price = None85        if new_pmax is not None and new_pmax <= 0:86            new_pmax = None87        if is_placeholder_price(new_price) or is_placeholder_price(new_pmax):88            try:89                d = json.loads(details) if details else {}90            except Exception:91                d = {}92            d["price_placeholder"] = new_price or new_pmax93            details = json.dumps(d, ensure_ascii=False)94            new_price = new_pmax = None95            stats["placeholder"] += 196        if new_price is None:97            on_req = 198        status = classify_listing(title, ptype)99        if status == "published":100            if not title.strip():101                status = "quarantine:titre_vide"102                stats["quarantine_titre"] += 1103            elif new_price is not None and new_price > PRICE_SANE_MAX:104                status = "quarantine:prix_hors_bornes"105                stats["quarantine_prix"] += 1106        else:107            excl[status] = excl.get(status, 0) + 1108        if on_req:109            stats["on_request"] += 1110        if (status != (r["listing_status"] or "published")111                or on_req != (r["price_on_request"] or 0)112                or new_price != price):113            updates.append((new_price, new_pmax, details, status, on_req, uid))114115    print(f"[produits] placeholders neutralisés : {stats['placeholder']} | "116          f"sur devis (price_on_request) : {stats['on_request']} | "117          f"quarantaine prix : {stats['quarantine_prix']} | "118          f"quarantaine titre : {stats['quarantine_titre']}")119    print(f"[produits] exclusions : {json.dumps(excl, ensure_ascii=False)}")120    print(f"[produits] lignes à modifier : {len(updates)}")121    if not args.dry_run:122        con.executemany("UPDATE products SET price=?, price_max=?, details=?, "123                        "listing_status=?, price_on_request=? WHERE uid=?", updates)124        con.commit()125126    # ------------------------------------------------- boutiques : renommage127    reg = json.load(open(REG_PATH))128    by_id = {s["id"]: s for s in reg["stores"]}129    from collections import Counter130    name_counts = Counter((s.get("name") or "").strip().lower() for s in reg["stores"])131    renamed = []132    for s in reg["stores"]:133        cur = (s.get("name") or "").strip()134        low = cur.lower()135        generic = low in GENERIC_NAMES or (name_counts[low] >= 3 and len(low) < 40)136        if not generic:137            continue138        cpath = os.path.join(ENRICH_CACHE, s["id"] + ".json")139        if not os.path.exists(cpath):140            continue141        try:142            site_name = (json.load(open(cpath)).get("site_name") or "").strip()143        except Exception:144            continue145        import html as _html146        site_name = re.sub(r"\s+", " ", _html.unescape(site_name)).strip()[:80]147        if site_name.lower() in BAD_SITE_NAMES:148            continue149        if (site_name and site_name.lower() not in GENERIC_NAMES150                and site_name.lower() != low and len(site_name) >= 3):151            renamed.append((s["id"], cur, site_name))152            s["name"] = site_name153    print(f"[boutiques] renommées via og:site_name : {len(renamed)}")154    for sid, old, new in renamed[:15]:155        print(f"   {sid}: «{old}» -> «{new}»")156157    # ------------------------------------------------- boutiques : store_kind158    kinds = {}159    for s in reg["stores"]:160        sid = s["id"]161        ev = (s.get("origin_evidence") or "").lower()162        kind = ""163        if sid in CURATED_HORS_MISSION:164            kind = "hors_mission"165        elif sid in CURATED_COLLECTIF:166            kind = "collectif"167        elif sid in CURATED_REVENDEUR or "revend" in ev:168            kind = "revendeur"169        if kind:170            s["store_kind"] = kind171            kinds[kind] = kinds.get(kind, 0) + 1172    print(f"[boutiques] store_kind : {json.dumps(kinds, ensure_ascii=False)}")173174    if args.dry_run:175        print("[dry-run] aucun écrit")176        return177178    json.dump(reg, open(REG_PATH, "w"), ensure_ascii=False, indent=1)179180    # DB : noms, store_kind, postal_prefix, phone181    for sid, _, new in renamed:182        con.execute("UPDATE stores SET name=? WHERE id=?", (new, sid))183    for s in reg["stores"]:184        con.execute("UPDATE stores SET store_kind=COALESCE(NULLIF(?,''), store_kind), "185                    "postal_prefix=COALESCE(NULLIF(?,''), postal_prefix), "186                    "phone=COALESCE(NULLIF(?,''), phone) WHERE id=?",187                    (s.get("store_kind") or "", s.get("postal_prefix") or "",188                     s.get("phone") or "", s["id"]))189190    # produits des boutiques hors mission -> exclus en masse191    for sid in CURATED_HORS_MISSION:192        cur = con.execute("UPDATE products SET listing_status='excluded:hors_mission' "193                          "WHERE store_id=? AND listing_status='published'", (sid,))194        if cur.rowcount:195            print(f"[hors-mission] {sid}: {cur.rowcount} produits exclus")196197    # réindexation FTS (store_name) des boutiques renommées avec produits198    n_fts = 0199    for sid, _, new in renamed:200        rows = con.execute("SELECT uid, title, description, tags, vendor FROM products "201                           "WHERE store_id=? AND active=1", (sid,)).fetchall()202        if not rows:203            continue204        uids = [r["uid"] for r in rows]205        for i in range(0, len(uids), 500):206            chunk = uids[i:i + 500]207            con.execute("DELETE FROM products_fts WHERE uid IN (%s)"208                        % ",".join("?" * len(chunk)), chunk)209        con.executemany(210            "INSERT INTO products_fts (uid, title, description, tags, vendor, store_name) "211            "VALUES (?,?,?,?,?,?)",212            [(r["uid"], r["title"], r["description"],213              " ".join(json.loads(r["tags"] or "[]")), r["vendor"], new) for r in rows])214        n_fts += len(rows)215    print(f"[fts] {n_fts} produits réindexés (nouveau nom de boutique)")216217    # product_count recalculé (publiés seulement)218    con.execute("UPDATE stores SET product_count=(SELECT COUNT(*) FROM products "219                "WHERE store_id=stores.id AND active=1 AND listing_status='published')")220    con.commit()221    con.close()222    print("[backfill] terminé")223224225if __name__ == "__main__":226    main()227