spb/fabri-ka

1#!/usr/bin/env python32"""Phase 2 — mesure « APRÈS » : mêmes requêtes que 03-BASELINE.md (2026-08-19).34Sort un bloc texte prêt à coller dans 05-ENRICHISSEMENT-RAPPORT.md.5Usage : .venv/bin/python scripts/phase2_stats.py6"""7import json8import os9import sys1011ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))12sys.path.insert(0, ROOT)13from fabrika import db as fdb  # noqa: E4021415con = fdb.connect()16one = lambda sql, a=(): con.execute(sql, a).fetchone()[0]17rows = lambda sql, a=(): [tuple(r) for r in con.execute(sql, a)]1819ACT = "active=1"20PUB = "active=1 AND listing_status='published'"2122print("## Produits")23print("total en base :", one("SELECT COUNT(*) FROM products"))24print("actifs :", one(f"SELECT COUNT(*) FROM products WHERE {ACT}"))25print("publiés :", one(f"SELECT COUNT(*) FROM products WHERE {PUB}"))26print("par listing_status :", rows("SELECT listing_status, COUNT(*) FROM products "27                                   f"WHERE {ACT} GROUP BY listing_status ORDER BY 2 DESC"))28print("sur devis (price_on_request, actifs) :",29      one(f"SELECT COUNT(*) FROM products WHERE {ACT} AND price_on_request=1"))30print("devises :", rows(f"SELECT currency, COUNT(*) FROM products WHERE {ACT} "31                        "GROUP BY currency ORDER BY 2 DESC"))3233n_pub = one(f"SELECT COUNT(*) FROM products WHERE {PUB}")34def pct(n): return f"{n} ({100.0*n/n_pub:.1f} %)"35print("\n## Complétude par champ (produits publiés)")36print("prix (>0) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>0")))37print("≥1 image :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND images<>'[]' AND images IS NOT NULL")))38print("description non vide :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND description<>''")))39print("details :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND details IS NOT NULL AND details<>'{{}}'")))40print("product_type :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND product_type<>''")))41print("vendor :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND vendor<>''")))42print("multi-images (≥2) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND json_array_length(images)>=2")))43print("plafond images atteint (=15) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND json_array_length(images)=15")))44print("anciennes troncatures possibles (desc 590-600) :",45      pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description) BETWEEN 590 AND 600")))46print("desc ≥ 590 car. :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description)>=590")))47print("desc tronquée au nouveau cap (≥4990) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description)>=4990")))48print("longueur moyenne description :", round(one(f"SELECT AVG(length(description)) FROM products WHERE {PUB} AND description<>''") or 0), "car.")49print("prix >10k / >100k (publiés) :",50      one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>10000"), "/",51      one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>100000"))52print("prix max publié :", one(f"SELECT MAX(price) FROM products WHERE {PUB}"))5354print("\n## Catégories (publiés)")55print(rows(f"SELECT category, COUNT(*) FROM products WHERE {PUB} GROUP BY category ORDER BY 2 DESC"))5657print("\n## Boutiques")58print("registre :", one("SELECT COUNT(*) FROM stores"))59print("avec ≥1 produit publié :", one("SELECT COUNT(*) FROM stores WHERE product_count>0"))60print("city :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(city,'')<>''"))61print("lat/lng :", one("SELECT COUNT(*) FROM stores WHERE lat IS NOT NULL AND lng IS NOT NULL"))62print("email :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(email,'')<>''"))63print("phone :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(phone,'')<>''"))64print("shipping_info :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(shipping_info,'')<>''"))65print("store_kind :", rows("SELECT store_kind, COUNT(*) FROM stores WHERE COALESCE(store_kind,'')<>'' GROUP BY store_kind"))66print("categories boutique :", one("SELECT COUNT(*) FROM stores WHERE categories IS NOT NULL AND categories<>'[]'"))67print("logo :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(logo_url,'')<>''"))68print("description_meta :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(description_meta,'')<>''"))69print("région :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(region,'')<>''"))7071reg = json.load(open(os.path.join(ROOT, "data", "stores.json")))["stores"]72print("\n## Registre stores.json")73print("boutiques :", len(reg))74print("enabled :", sum(1 for s in reg if s.get("enabled")))75print("avec endpoint :", sum(1 for s in reg if s.get("catalog_endpoint")))76print("jamais re-sondées (sans last_reprobe, sans endpoint) :",77      sum(1 for s in reg if not s.get("catalog_endpoint") and not s.get("last_reprobe")))78print("geo (lat/lng) :", sum(1 for s in reg if s.get("geo")))79print("store_kind :", sum(1 for s in reg if s.get("store_kind")))8081print("\n## Détails (échantillon 20 000 publiés)")82import random83det_rows = [r[0] for r in con.execute(84    f"SELECT details FROM products WHERE {PUB} AND details IS NOT NULL LIMIT 20000")]85keys = {}86for d in det_rows:87    try:88        for k in json.loads(d):89            keys[k] = keys.get(k, 0) + 190    except Exception:91        pass92print({k: v for k, v in sorted(keys.items(), key=lambda x: -x[1])[:14]})9394print("\n## Santé sync (24 h)")95import time96t0 = time.time() - 8640097print("syncs/erreurs 24 h :",98      one("SELECT COUNT(*) FROM sync_log WHERE ts>?", (t0,)),99      one("SELECT COUNT(*) FROM sync_log WHERE ts>? AND status='error'", (t0,)))100con.close()101