#!/usr/bin/env python3
"""Phase 2 — mesure « APRÈS » : mêmes requêtes que 03-BASELINE.md (2026-08-19).

Sort un bloc texte prêt à coller dans 05-ENRICHISSEMENT-RAPPORT.md.
Usage : .venv/bin/python scripts/phase2_stats.py
"""
import json
import os
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

con = fdb.connect()
one = lambda sql, a=(): con.execute(sql, a).fetchone()[0]
rows = lambda sql, a=(): [tuple(r) for r in con.execute(sql, a)]

ACT = "active=1"
PUB = "active=1 AND listing_status='published'"

print("## Produits")
print("total en base :", one("SELECT COUNT(*) FROM products"))
print("actifs :", one(f"SELECT COUNT(*) FROM products WHERE {ACT}"))
print("publiés :", one(f"SELECT COUNT(*) FROM products WHERE {PUB}"))
print("par listing_status :", rows("SELECT listing_status, COUNT(*) FROM products "
                                   f"WHERE {ACT} GROUP BY listing_status ORDER BY 2 DESC"))
print("sur devis (price_on_request, actifs) :",
      one(f"SELECT COUNT(*) FROM products WHERE {ACT} AND price_on_request=1"))
print("devises :", rows(f"SELECT currency, COUNT(*) FROM products WHERE {ACT} "
                        "GROUP BY currency ORDER BY 2 DESC"))

n_pub = one(f"SELECT COUNT(*) FROM products WHERE {PUB}")
def pct(n): return f"{n} ({100.0*n/n_pub:.1f} %)"
print("\n## Complétude par champ (produits publiés)")
print("prix (>0) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>0")))
print("≥1 image :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND images<>'[]' AND images IS NOT NULL")))
print("description non vide :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND description<>''")))
print("details :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND details IS NOT NULL AND details<>'{{}}'")))
print("product_type :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND product_type<>''")))
print("vendor :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND vendor<>''")))
print("multi-images (≥2) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND json_array_length(images)>=2")))
print("plafond images atteint (=15) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND json_array_length(images)=15")))
print("anciennes troncatures possibles (desc 590-600) :",
      pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description) BETWEEN 590 AND 600")))
print("desc ≥ 590 car. :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description)>=590")))
print("desc tronquée au nouveau cap (≥4990) :", pct(one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND length(description)>=4990")))
print("longueur moyenne description :", round(one(f"SELECT AVG(length(description)) FROM products WHERE {PUB} AND description<>''") or 0), "car.")
print("prix >10k / >100k (publiés) :",
      one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>10000"), "/",
      one(f"SELECT COUNT(*) FROM products WHERE {PUB} AND price>100000"))
print("prix max publié :", one(f"SELECT MAX(price) FROM products WHERE {PUB}"))

print("\n## Catégories (publiés)")
print(rows(f"SELECT category, COUNT(*) FROM products WHERE {PUB} GROUP BY category ORDER BY 2 DESC"))

print("\n## Boutiques")
print("registre :", one("SELECT COUNT(*) FROM stores"))
print("avec ≥1 produit publié :", one("SELECT COUNT(*) FROM stores WHERE product_count>0"))
print("city :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(city,'')<>''"))
print("lat/lng :", one("SELECT COUNT(*) FROM stores WHERE lat IS NOT NULL AND lng IS NOT NULL"))
print("email :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(email,'')<>''"))
print("phone :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(phone,'')<>''"))
print("shipping_info :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(shipping_info,'')<>''"))
print("store_kind :", rows("SELECT store_kind, COUNT(*) FROM stores WHERE COALESCE(store_kind,'')<>'' GROUP BY store_kind"))
print("categories boutique :", one("SELECT COUNT(*) FROM stores WHERE categories IS NOT NULL AND categories<>'[]'"))
print("logo :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(logo_url,'')<>''"))
print("description_meta :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(description_meta,'')<>''"))
print("région :", one("SELECT COUNT(*) FROM stores WHERE COALESCE(region,'')<>''"))

reg = json.load(open(os.path.join(ROOT, "data", "stores.json")))["stores"]
print("\n## Registre stores.json")
print("boutiques :", len(reg))
print("enabled :", sum(1 for s in reg if s.get("enabled")))
print("avec endpoint :", sum(1 for s in reg if s.get("catalog_endpoint")))
print("jamais re-sondées (sans last_reprobe, sans endpoint) :",
      sum(1 for s in reg if not s.get("catalog_endpoint") and not s.get("last_reprobe")))
print("geo (lat/lng) :", sum(1 for s in reg if s.get("geo")))
print("store_kind :", sum(1 for s in reg if s.get("store_kind")))

print("\n## Détails (échantillon 20 000 publiés)")
import random
det_rows = [r[0] for r in con.execute(
    f"SELECT details FROM products WHERE {PUB} AND details IS NOT NULL LIMIT 20000")]
keys = {}
for d in det_rows:
    try:
        for k in json.loads(d):
            keys[k] = keys.get(k, 0) + 1
    except Exception:
        pass
print({k: v for k, v in sorted(keys.items(), key=lambda x: -x[1])[:14]})

print("\n## Santé sync (24 h)")
import time
t0 = time.time() - 86400
print("syncs/erreurs 24 h :",
      one("SELECT COUNT(*) FROM sync_log WHERE ts>?", (t0,)),
      one("SELECT COUNT(*) FROM sync_log WHERE ts>? AND status='error'", (t0,)))
con.close()