#!/usr/bin/env python3
"""Phase 2 — backfill qualité (2026-08-19).

Applique aux données DÉJÀ en base les règles désormais appliquées à
l'ingestion (schema.py) :

1. Produits :
   - prix placeholders (999 999 $…) -> price=NULL + price_on_request=1 ;
   - produits sans prix -> price_on_request=1 ;
   - prix > 100 000 $ -> quarantine:prix_hors_bornes ;
   - titre vide -> quarantine:titre_vide ;
   - cartes-cadeaux / ateliers-cours / abonnements / billets / produits test
     -> excluded:<raison> (marqués, jamais supprimés, réintégrables).
2. Boutiques :
   - renommage des noms génériques d'annuaire (« Créateurs », « Complices »…)
     et des groupes dupliqués via og:site_name du cache d'enrichissement
     (data/enrich_cache/<dom>.json) — registre + DB + réindexation FTS ;
   - store_kind (fabricant / revendeur / collectif / hors_mission) :
     * revendeur : origin_evidence du registre contenant « revend » +
       liste curée (marques tierces dominantes constatées en base) ;
     * collectif : boutiques multi-créateurs connues ;
     * hors_mission : transport/billetterie (produits exclus en masse).
   - postal_prefix recopié du registre vers la DB (upsert le maintient ensuite).

Idempotent ; usage : .venv/bin/python scripts/backfill_quality.py [--dry-run]
"""
import argparse
import json
import os
import re
import sys

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, ROOT)

from fabrika import db as fdb  # noqa: E402
from fabrika.schema import classify_listing, is_placeholder_price, PRICE_SANE_MAX  # noqa: E402

REG_PATH = os.path.join(ROOT, "data", "stores.json")
ENRICH_CACHE = os.path.join(ROOT, "data", "enrich_cache")

# Noms de boutiques génériques hérités des rubriques d'annuaires
BAD_SITE_NAMES = {"your site title", "site title", "home", "home page",
                  "accueil", "untitled", "my site", "mon site"}

GENERIC_NAMES = {"créateurs", "createurs", "producteurs/transformateurs",
                 "producteurs", "transformateurs", "complices", "accueil",
                 "home", "boutique", "produits", "shop", "menu", "à propos"}

# revendeurs constatés (marques tierces dominantes dans le catalogue :
# Tissot, BIBS, Rieker, Natural Factors, Pacsafe… — voir 05-ENRICHISSEMENT-RAPPORT)
CURATED_REVENDEUR = {
    "lamaisondubleuet.com", "mondeavie.ca", "lesptitsmosus.com",
    "grenierboutique.ca", "yellowshoes.com", "bijouteriejodoin.com",
    "eugeneallard.com", "remorquetrailer.com", "doggoboutique.ca",
    "boiteavins.com",
}
# boutiques multi-créateurs (vendor = fabricant réel)
CURATED_COLLECTIF = {"paperole.com", "signelocal.com", "wachiya.com",
                     "galerieiris.com"}
# hors mission fabricant (transport, activités, billetterie)
CURATED_HORS_MISSION = {"traversiers.com", "rtcbq.com", "raftingmomentum.com"}


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--dry-run", action="store_true")
    args = ap.parse_args()

    con = fdb.connect()

    # ------------------------------------------------------------------ produits
    stats = {"placeholder": 0, "on_request": 0, "quarantine_prix": 0,
             "quarantine_titre": 0}
    excl = {}
    updates = []
    for r in con.execute("SELECT uid, title, product_type, price, price_max, "
                         "details, listing_status, price_on_request FROM products"):
        uid, title, ptype, price = r["uid"], r["title"] or "", r["product_type"] or "", r["price"]
        new_price, new_pmax = price, r["price_max"]
        details = r["details"]
        on_req = 0
        if new_price is not None and new_price <= 0:      # 0 $ n'est pas un prix
            new_price = None
        if new_pmax is not None and new_pmax <= 0:
            new_pmax = None
        if is_placeholder_price(new_price) or is_placeholder_price(new_pmax):
            try:
                d = json.loads(details) if details else {}
            except Exception:
                d = {}
            d["price_placeholder"] = new_price or new_pmax
            details = json.dumps(d, ensure_ascii=False)
            new_price = new_pmax = None
            stats["placeholder"] += 1
        if new_price is None:
            on_req = 1
        status = classify_listing(title, ptype)
        if status == "published":
            if not title.strip():
                status = "quarantine:titre_vide"
                stats["quarantine_titre"] += 1
            elif new_price is not None and new_price > PRICE_SANE_MAX:
                status = "quarantine:prix_hors_bornes"
                stats["quarantine_prix"] += 1
        else:
            excl[status] = excl.get(status, 0) + 1
        if on_req:
            stats["on_request"] += 1
        if (status != (r["listing_status"] or "published")
                or on_req != (r["price_on_request"] or 0)
                or new_price != price):
            updates.append((new_price, new_pmax, details, status, on_req, uid))

    print(f"[produits] placeholders neutralisés : {stats['placeholder']} | "
          f"sur devis (price_on_request) : {stats['on_request']} | "
          f"quarantaine prix : {stats['quarantine_prix']} | "
          f"quarantaine titre : {stats['quarantine_titre']}")
    print(f"[produits] exclusions : {json.dumps(excl, ensure_ascii=False)}")
    print(f"[produits] lignes à modifier : {len(updates)}")
    if not args.dry_run:
        con.executemany("UPDATE products SET price=?, price_max=?, details=?, "
                        "listing_status=?, price_on_request=? WHERE uid=?", updates)
        con.commit()

    # ------------------------------------------------- boutiques : renommage
    reg = json.load(open(REG_PATH))
    by_id = {s["id"]: s for s in reg["stores"]}
    from collections import Counter
    name_counts = Counter((s.get("name") or "").strip().lower() for s in reg["stores"])
    renamed = []
    for s in reg["stores"]:
        cur = (s.get("name") or "").strip()
        low = cur.lower()
        generic = low in GENERIC_NAMES or (name_counts[low] >= 3 and len(low) < 40)
        if not generic:
            continue
        cpath = os.path.join(ENRICH_CACHE, s["id"] + ".json")
        if not os.path.exists(cpath):
            continue
        try:
            site_name = (json.load(open(cpath)).get("site_name") or "").strip()
        except Exception:
            continue
        import html as _html
        site_name = re.sub(r"\s+", " ", _html.unescape(site_name)).strip()[:80]
        if site_name.lower() in BAD_SITE_NAMES:
            continue
        if (site_name and site_name.lower() not in GENERIC_NAMES
                and site_name.lower() != low and len(site_name) >= 3):
            renamed.append((s["id"], cur, site_name))
            s["name"] = site_name
    print(f"[boutiques] renommées via og:site_name : {len(renamed)}")
    for sid, old, new in renamed[:15]:
        print(f"   {sid}: «{old}» -> «{new}»")

    # ------------------------------------------------- boutiques : store_kind
    kinds = {}
    for s in reg["stores"]:
        sid = s["id"]
        ev = (s.get("origin_evidence") or "").lower()
        kind = ""
        if sid in CURATED_HORS_MISSION:
            kind = "hors_mission"
        elif sid in CURATED_COLLECTIF:
            kind = "collectif"
        elif sid in CURATED_REVENDEUR or "revend" in ev:
            kind = "revendeur"
        if kind:
            s["store_kind"] = kind
            kinds[kind] = kinds.get(kind, 0) + 1
    print(f"[boutiques] store_kind : {json.dumps(kinds, ensure_ascii=False)}")

    if args.dry_run:
        print("[dry-run] aucun écrit")
        return

    json.dump(reg, open(REG_PATH, "w"), ensure_ascii=False, indent=1)

    # DB : noms, store_kind, postal_prefix, phone
    for sid, _, new in renamed:
        con.execute("UPDATE stores SET name=? WHERE id=?", (new, sid))
    for s in reg["stores"]:
        con.execute("UPDATE stores SET store_kind=COALESCE(NULLIF(?,''), store_kind), "
                    "postal_prefix=COALESCE(NULLIF(?,''), postal_prefix), "
                    "phone=COALESCE(NULLIF(?,''), phone) WHERE id=?",
                    (s.get("store_kind") or "", s.get("postal_prefix") or "",
                     s.get("phone") or "", s["id"]))

    # produits des boutiques hors mission -> exclus en masse
    for sid in CURATED_HORS_MISSION:
        cur = con.execute("UPDATE products SET listing_status='excluded:hors_mission' "
                          "WHERE store_id=? AND listing_status='published'", (sid,))
        if cur.rowcount:
            print(f"[hors-mission] {sid}: {cur.rowcount} produits exclus")

    # réindexation FTS (store_name) des boutiques renommées avec produits
    n_fts = 0
    for sid, _, new in renamed:
        rows = con.execute("SELECT uid, title, description, tags, vendor FROM products "
                           "WHERE store_id=? AND active=1", (sid,)).fetchall()
        if not rows:
            continue
        uids = [r["uid"] for r in rows]
        for i in range(0, len(uids), 500):
            chunk = uids[i:i + 500]
            con.execute("DELETE FROM products_fts WHERE uid IN (%s)"
                        % ",".join("?" * len(chunk)), chunk)
        con.executemany(
            "INSERT INTO products_fts (uid, title, description, tags, vendor, store_name) "
            "VALUES (?,?,?,?,?,?)",
            [(r["uid"], r["title"], r["description"],
              " ".join(json.loads(r["tags"] or "[]")), r["vendor"], new) for r in rows])
        n_fts += len(rows)
    print(f"[fts] {n_fts} produits réindexés (nouveau nom de boutique)")

    # product_count recalculé (publiés seulement)
    con.execute("UPDATE stores SET product_count=(SELECT COUNT(*) FROM products "
                "WHERE store_id=stores.id AND active=1 AND listing_status='published')")
    con.commit()
    con.close()
    print("[backfill] terminé")


if __name__ == "__main__":
    main()