spb/fabri-ka Public

1#!/usr/bin/env python32"""Export research deliverables from the pipeline outputs.34Produces in deliverables/:5  quebec_stores.csv / quebec_stores.json   (deduplicated verified dataset)6  unverified_candidates.csv                (dead/unreachable/name-only candidates)7  sources.csv                              (all sources used)8"""9import csv10import json11import os12from datetime import date1314ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))15DEL = os.path.join(ROOT, "deliverables")16os.makedirs(DEL, exist_ok=True)1718FIELDS = ["business_name", "domain", "website_url", "store_url", "city",19          "administrative_region", "postal_code_prefix", "latitude", "longitude",20          "primary_category", "secondary_categories", "business_type",21          "origin_classification", "origin_confidence", "origin_evidence",22          "products_summary", "example_products", "ecommerce", "ecommerce_platform",23          "direct_checkout", "ships_within_quebec", "ships_within_canada",24          "pickup_available", "language", "instagram", "facebook",25          "discovery_source", "discovery_source_url", "verification_source",26          "verification_date", "status"]272829def store_row(s, product_info):30    insta = next((x for x in s.get("socials", []) if "instagram" in x), "")31    fb = next((x for x in s.get("socials", []) if "facebook" in x), "")32    pinfo = product_info.get(s["id"], {})33    return {34        "business_name": s["name"],35        "domain": s["id"],36        "website_url": s["url"],37        "store_url": s["url"] + (s.get("catalog_endpoint") or ""),38        "city": s.get("city") or "",39        "administrative_region": s.get("region") or "",40        "postal_code_prefix": s.get("postal_prefix") or "",41        "latitude": None, "longitude": None,42        "primary_category": (s.get("categories") or [""])[0],43        "secondary_categories": json.dumps(s.get("categories", [])[1:], ensure_ascii=False),44        "business_type": "producteur/fabricant" if s["origin_class"] == "A"45                          else ("détaillant" if s["origin_class"] == "C" else ""),46        "origin_classification": s["origin_class"],47        "origin_confidence": s["origin_confidence"],48        "origin_evidence": s["origin_evidence"],49        "products_summary": pinfo.get("summary", ""),50        "example_products": json.dumps(pinfo.get("examples", []), ensure_ascii=False),51        "ecommerce": s.get("ecommerce", False),52        "ecommerce_platform": s.get("platform") or "",53        "direct_checkout": bool(s.get("catalog_endpoint")) or s.get("ecommerce", False),54        "ships_within_quebec": None, "ships_within_canada": None, "pickup_available": None,55        "language": json.dumps([s["language"]] if s.get("language") else []),56        "instagram": insta, "facebook": fb,57        "discovery_source": ";".join(s.get("discovery_sources", [])),58        "discovery_source_url": (s.get("discovery_source_urls") or [""])[0],59        "verification_source": "fabri-ka verify pipeline (fetch homepage + catalog probe)",60        "verification_date": s.get("verification_date", ""),61        "status": s.get("status", "probable"),62    }636465def main():66    reg = json.load(open(os.path.join(ROOT, "data", "stores.json")))["stores"]6768    # infos produits depuis la BD (si présente)69    product_info = {}70    dbp = os.path.join(ROOT, "data", "fabrika.db")71    if os.path.exists(dbp):72        import sqlite373        con = sqlite3.connect(dbp)74        for sid, n in con.execute("SELECT store_id, COUNT(*) FROM products WHERE active=1 GROUP BY store_id"):75            product_info[sid] = {"summary": f"{n} produits en ligne agrégés", "examples": []}76        for sid in list(product_info):77            ex = [r[0] for r in con.execute(78                "SELECT title FROM products WHERE store_id=? AND active=1 LIMIT 3", (sid,))]79            product_info[sid]["examples"] = ex80        con.close()8182    rows = [store_row(s, product_info) for s in reg]83    with open(os.path.join(DEL, "quebec_stores.csv"), "w", newline="") as f:84        w = csv.DictWriter(f, fieldnames=FIELDS)85        w.writeheader()86        w.writerows(rows)87    with open(os.path.join(DEL, "quebec_stores.json"), "w") as f:88        json.dump({"generated": str(date.today()), "count": len(rows), "stores": rows},89                  f, ensure_ascii=False, indent=1)90    print(f"quebec_stores: {len(rows)} rows")9192    # --- unverified candidates: candidats non actifs + vendeurs nom-seulement93    cands = {}94    with open(os.path.join(ROOT, "data", "enriched", "candidates.jsonl")) as f:95        for line in f:96            c = json.loads(line)97            cands[c["domain"]] = c98    vers = {}99    with open(os.path.join(ROOT, "data", "enriched", "verified.jsonl")) as f:100        for line in f:101            v = json.loads(line)102            vers[v["domain"]] = v103    unv = []104    for dom, c in sorted(cands.items()):105        v = vers.get(dom, {})106        if v.get("active"):107            continue108        unv.append({"business_name": (c.get("names") or [dom])[0], "domain": dom,109                    "reason": "site inaccessible ou hors-ligne lors de la vérification",110                    "http_status": v.get("status"),111                    "discovery_source": ";".join(c.get("sources", [])),112                    "discovery_source_url": (c.get("source_pages") or [""])[0]})113    sl = os.path.join(ROOT, "data", "raw", "signelocal_vendors.json")114    if os.path.exists(sl):115        for vend in json.load(open(sl)):116            unv.append({"business_name": vend["vendor"], "domain": "",117                        "reason": "marque Signé Local — site web propre à identifier",118                        "http_status": None, "discovery_source": "signelocal",119                        "discovery_source_url": "https://www.signelocal.com"})120    with open(os.path.join(DEL, "unverified_candidates.csv"), "w", newline="") as f:121        w = csv.DictWriter(f, fieldnames=["business_name", "domain", "reason",122                                          "http_status", "discovery_source", "discovery_source_url"])123        w.writeheader()124        w.writerows(unv)125    print(f"unverified_candidates: {len(unv)} rows")126127128if __name__ == "__main__":129    main()130