#!/usr/bin/env python3
"""Export research deliverables from the pipeline outputs.

Produces in deliverables/:
  quebec_stores.csv / quebec_stores.json   (deduplicated verified dataset)
  unverified_candidates.csv                (dead/unreachable/name-only candidates)
  sources.csv                              (all sources used)
"""
import csv
import json
import os
from datetime import date

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DEL = os.path.join(ROOT, "deliverables")
os.makedirs(DEL, exist_ok=True)

FIELDS = ["business_name", "domain", "website_url", "store_url", "city",
          "administrative_region", "postal_code_prefix", "latitude", "longitude",
          "primary_category", "secondary_categories", "business_type",
          "origin_classification", "origin_confidence", "origin_evidence",
          "products_summary", "example_products", "ecommerce", "ecommerce_platform",
          "direct_checkout", "ships_within_quebec", "ships_within_canada",
          "pickup_available", "language", "instagram", "facebook",
          "discovery_source", "discovery_source_url", "verification_source",
          "verification_date", "status"]


def store_row(s, product_info):
    insta = next((x for x in s.get("socials", []) if "instagram" in x), "")
    fb = next((x for x in s.get("socials", []) if "facebook" in x), "")
    pinfo = product_info.get(s["id"], {})
    return {
        "business_name": s["name"],
        "domain": s["id"],
        "website_url": s["url"],
        "store_url": s["url"] + (s.get("catalog_endpoint") or ""),
        "city": s.get("city") or "",
        "administrative_region": s.get("region") or "",
        "postal_code_prefix": s.get("postal_prefix") or "",
        "latitude": None, "longitude": None,
        "primary_category": (s.get("categories") or [""])[0],
        "secondary_categories": json.dumps(s.get("categories", [])[1:], ensure_ascii=False),
        "business_type": "producteur/fabricant" if s["origin_class"] == "A"
                          else ("détaillant" if s["origin_class"] == "C" else ""),
        "origin_classification": s["origin_class"],
        "origin_confidence": s["origin_confidence"],
        "origin_evidence": s["origin_evidence"],
        "products_summary": pinfo.get("summary", ""),
        "example_products": json.dumps(pinfo.get("examples", []), ensure_ascii=False),
        "ecommerce": s.get("ecommerce", False),
        "ecommerce_platform": s.get("platform") or "",
        "direct_checkout": bool(s.get("catalog_endpoint")) or s.get("ecommerce", False),
        "ships_within_quebec": None, "ships_within_canada": None, "pickup_available": None,
        "language": json.dumps([s["language"]] if s.get("language") else []),
        "instagram": insta, "facebook": fb,
        "discovery_source": ";".join(s.get("discovery_sources", [])),
        "discovery_source_url": (s.get("discovery_source_urls") or [""])[0],
        "verification_source": "fabri-ka verify pipeline (fetch homepage + catalog probe)",
        "verification_date": s.get("verification_date", ""),
        "status": s.get("status", "probable"),
    }


def main():
    reg = json.load(open(os.path.join(ROOT, "data", "stores.json")))["stores"]

    # infos produits depuis la BD (si présente)
    product_info = {}
    dbp = os.path.join(ROOT, "data", "fabrika.db")
    if os.path.exists(dbp):
        import sqlite3
        con = sqlite3.connect(dbp)
        for sid, n in con.execute("SELECT store_id, COUNT(*) FROM products WHERE active=1 GROUP BY store_id"):
            product_info[sid] = {"summary": f"{n} produits en ligne agrégés", "examples": []}
        for sid in list(product_info):
            ex = [r[0] for r in con.execute(
                "SELECT title FROM products WHERE store_id=? AND active=1 LIMIT 3", (sid,))]
            product_info[sid]["examples"] = ex
        con.close()

    rows = [store_row(s, product_info) for s in reg]
    with open(os.path.join(DEL, "quebec_stores.csv"), "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=FIELDS)
        w.writeheader()
        w.writerows(rows)
    with open(os.path.join(DEL, "quebec_stores.json"), "w") as f:
        json.dump({"generated": str(date.today()), "count": len(rows), "stores": rows},
                  f, ensure_ascii=False, indent=1)
    print(f"quebec_stores: {len(rows)} rows")

    # --- unverified candidates: candidats non actifs + vendeurs nom-seulement
    cands = {}
    with open(os.path.join(ROOT, "data", "enriched", "candidates.jsonl")) as f:
        for line in f:
            c = json.loads(line)
            cands[c["domain"]] = c
    vers = {}
    with open(os.path.join(ROOT, "data", "enriched", "verified.jsonl")) as f:
        for line in f:
            v = json.loads(line)
            vers[v["domain"]] = v
    unv = []
    for dom, c in sorted(cands.items()):
        v = vers.get(dom, {})
        if v.get("active"):
            continue
        unv.append({"business_name": (c.get("names") or [dom])[0], "domain": dom,
                    "reason": "site inaccessible ou hors-ligne lors de la vérification",
                    "http_status": v.get("status"),
                    "discovery_source": ";".join(c.get("sources", [])),
                    "discovery_source_url": (c.get("source_pages") or [""])[0]})
    sl = os.path.join(ROOT, "data", "raw", "signelocal_vendors.json")
    if os.path.exists(sl):
        for vend in json.load(open(sl)):
            unv.append({"business_name": vend["vendor"], "domain": "",
                        "reason": "marque Signé Local — site web propre à identifier",
                        "http_status": None, "discovery_source": "signelocal",
                        "discovery_source_url": "https://www.signelocal.com"})
    with open(os.path.join(DEL, "unverified_candidates.csv"), "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=["business_name", "domain", "reason",
                                          "http_status", "discovery_source", "discovery_source_url"])
        w.writeheader()
        w.writerows(unv)
    print(f"unverified_candidates: {len(unv)} rows")


if __name__ == "__main__":
    main()