`scripts/build-data.py`

135 lines
#!/usr/bin/env python3
"""Build data/vendors.json for all.designerwallcoverings.com.

Sources (local dw_unified mirror, read-only):
  vendor_registry   — every vendor id (the "all vendor ids" requirement)
  shopify_products  — live ACTIVE counts + sample images per front-facing vendor

Front-facing rules enforced here:
  - display name = private_label_name when is_private_label, else vendor_name
  - parenthetical source notes stripped ("York (Jeffrey Stevens source)" -> handled
    by private_label_name; leftover "(...)" stripped defensively)
  - BANNED upstream names never ship: Brewster, York, WallQuest, NewWall,
    Command54, Justin David, Nicolette Mayer, Seabrook, Chesapeake, NextWall,
    Desima, Carlsten. A registry row whose display name still contains one is
    dropped (its products already surface under the private label).
"""
import json, re, subprocess, sys, os

DB = "postgresql:///dw_unified?host=/tmp"
OUT = os.path.join(os.path.dirname(__file__), "..", "data", "vendors.json")

BANNED = re.compile(
    r"brewster|york|wallquest|wall\s*quest|newwall|new\s*wall\b|command\s*54|"
    r"justin\s*david|nicolette\s*mayer|seabrook|chesapeake|nextwall|desima|carlsten",
    re.I,
)

def q(sql):
    r = subprocess.run(["psql", DB, "-tA", "-c", sql], capture_output=True, text=True)
    if r.returncode != 0:
        sys.exit(f"psql failed: {r.stderr}")
    return r.stdout.strip()

# 1. Live store: per-vendor active count + 4 newest sample images
live = json.loads(q("""
SELECT COALESCE(json_agg(t), '[]') FROM (
  SELECT vendor,
         count(*) AS active_count,
         (SELECT json_agg(u) FROM (
            SELECT image_url AS u FROM shopify_products sp2
            WHERE sp2.vendor = sp.vendor AND upper(sp2.status)='ACTIVE'
              AND sp2.image_url IS NOT NULL AND sp2.image_url <> ''
            ORDER BY sp2.created_at_shopify DESC NULLS LAST LIMIT 4
          ) s) AS images,
         max(created_at_shopify) AS newest
  FROM shopify_products sp
  WHERE upper(status)='ACTIVE' AND vendor IS NOT NULL AND vendor <> ''
  GROUP BY vendor
) t;"""))

# 2. Registry: every vendor id
reg = json.loads(q("""
SELECT COALESCE(json_agg(t), '[]') FROM (
  SELECT id, vendor_name, vendor_code, is_private_label, private_label_name,
         is_active, shopify_count, total_products
  FROM vendor_registry
) t;"""))

def display_name(row):
    name = (row.get("private_label_name") or "").strip() if row.get("is_private_label") else ""
    name = name or (row.get("vendor_name") or "").strip()
    name = re.sub(r"\s*\(.*?\)\s*", " ", name).strip()  # strip "(... source)" notes
    return name

live_by_name = { (v["vendor"] or "").strip().lower(): v for v in live }

vendors = {}
def upsert(name, **kw):
    key = name.strip().lower()
    # front-facing: never carry vendor's own website — links go into the DW store only
    v = vendors.setdefault(key, {
        "name": name.strip(), "registry_ids": [], "active_count": 0,
        "images": [], "is_private_label": False,
    })
    for ids in kw.pop("registry_ids", []):
        if ids not in v["registry_ids"]:
            v["registry_ids"].append(ids)
    for k, val in kw.items():
        if k == "active_count":
            v[k] = max(v[k], val or 0)
        elif k == "images":
            v[k] = v[k] or (val or [])
        elif val and not v.get(k):
            v[k] = val
    return v

# INTERNAL lines — deliberately NEVER front-facing (config/internal-lines.json). Their vendor
# names are dropped from this front-facing vendor directory, same as BANNED. (Steve 2026-07-09)
INTERNAL_VENDORS = {"fromental", "gracie", "zuber", "crezana design"}
for _p in (os.path.join(os.path.dirname(__file__), "..", "..", "Designer-Wallcoverings", "shopify", "config", "internal-lines.json"),
           "/root/Projects/Designer-Wallcoverings/shopify/config/internal-lines.json"):
    try:
        with open(_p) as _f:
            _j = json.load(_f)
        if isinstance(_j.get("vendors"), list) and _j["vendors"]:
            INTERNAL_VENDORS = {str(x).strip().lower() for x in _j["vendors"]}
        break
    except Exception:
        pass

dropped = []
for row in reg:
    name = display_name(row)
    if not name or BANNED.search(name) or name.strip().lower() in INTERNAL_VENDORS:
        dropped.append(row.get("vendor_name"))
        continue
    lv = live_by_name.get(name.lower())
    upsert(name,
           registry_ids=[row["id"]],
           active_count=(lv or {}).get("active_count", 0),
           images=(lv or {}).get("images"),
           is_private_label=bool(row.get("is_private_label")))

# Live store vendors with no registry row still belong in a front-facing directory
for lname, lv in live_by_name.items():
    if BANNED.search(lv["vendor"] or "") or (lv["vendor"] or "").strip().lower() in INTERNAL_VENDORS:
        dropped.append(lv["vendor"])
        continue
    if lname not in vendors:
        upsert(lv["vendor"], registry_ids=[], active_count=lv["active_count"], images=lv["images"])

out = sorted(vendors.values(), key=lambda v: (-v["active_count"], v["name"].lower()))
for v in out:
    v["shop_url"] = ("https://www.designerwallcoverings.com/collections/vendors?q="
                     + v["name"].replace(" ", "+"))

os.makedirs(os.path.dirname(OUT), exist_ok=True)
with open(OUT, "w") as f:
    json.dump({"generated_note": "built from dw_unified mirror", "vendors": out}, f)

n_live = sum(1 for v in out if v["active_count"] > 0)
print(f"vendors={len(out)} with_live_products={n_live} dropped_banned={len(dropped)}")
for d in dropped[:15]:
    print("  dropped:", d)