← back to Reid Witlin Onboarding
build_batch2.py
235 lines
#!/usr/bin/env python3
"""
Reid Witlin (rwltd.com) onboarding batch v2 — CORRECTED scope, post 2026-09-01 re-scrape.
Supersedes build_batch.py (which targeted the stale 1,071-row/DWDQ-prefix plan,
now quarantined — see _superseded/reid-witlin-rwltd-onboarding-2026-07-30-*.md).
Two genuine candidate pools, both confirmed still-live on rwltd.com by the
2026-09-01 re-scrape, neither overlapping the 190 already-live products:
Pool A ("reconfirmed-old", 779 rows) — original Feb-2026 full-detail scrape,
already carries a minted DWKR-19xxxx dw_sku, full spec_* fields, town
mapping, and gallery_images. Reuses build_batch.py's proven per-colorway
fuzzy image-match (these already have real dw_sku — DO NOT re-mint).
Pool B ("new-gap", 382 rows) — colorways first seen in the 2026-09-01
re-scrape, feed-only data (title/handle/price/one image, no spec_* or
town). No dw_sku yet -> mint continuing the live DWKR- band from 191394.
Uses the row's own image_url directly (already the vendor's per-colorway
primary image — no fuzzy matching needed).
OUTPUT ONLY — writes local CSVs + summary.json. Does NOT touch Shopify or
write anything to dw_unified. The live create is a separate, Steve-gated step
(create2.py, DRY_RUN default).
"""
import json, csv, re, subprocess, os
OUT = os.path.dirname(os.path.abspath(__file__))
VENDOR = "Architectural Fabrics"
SAMPLE_PRICE = "4.25"
DWKR_NEXT_START = 191394 # confirmed max minted DWKR- number today is 191393
def psql_json(sql):
wrapped = f"SELECT COALESCE(json_agg(t),'[]') FROM ({sql}) t;"
r = subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-tA", "-c", wrapped],
capture_output=True, text=True)
if r.returncode != 0:
raise SystemExit(f"psql failed: {r.stderr}")
return json.loads(r.stdout.strip() or "[]")
def slugify(s):
return re.sub(r'-+', '-', re.sub(r'[^a-z0-9]+', '-', (s or '').lower())).strip('-')
def colorway_of(mfr_sku, pattern_name):
pat = slugify(pattern_name)
slug = slugify(mfr_sku)
if pat and slug.startswith(pat + '-'):
return slug[len(pat) + 1:]
return slug.rsplit('-', 1)[-1]
def recover_image(colorway, gallery):
if not gallery:
return None, "none"
cw = colorway.replace('-', '')
is_numeric = bool(cw) and cw.isdigit()
best = None
for url in gallery:
if not isinstance(url, str) or not url.startswith('http'):
continue
fname = url.split('/')[-1].split('?')[0].lower()
stem = re.sub(r'\.(jpg|jpeg|png|webp|gif)$', '', fname)
stem_alnum = re.sub(r'[^a-z0-9]', '', stem)
if is_numeric:
if re.search(r'(?<!\d)' + re.escape(cw) + r'(?!\d)', stem):
return url, "exact"
continue
if cw and cw in stem_alnum:
return url, "exact"
if cw and len(cw) >= 4 and len(stem_alnum) >= 3 and (
stem_alnum.startswith(cw[:4]) or cw.startswith(stem_alnum[:4])):
best = best or (url, "prefix")
return best if best else (None, "none")
def build_tags(row, colorway, town):
tags = {VENDOR, "quotes", "Commercial", "Showroom Line", "display_variant", "Fabric"}
if town:
tags.add(town)
tags.add(f"{town} Collection")
cwt = colorway.replace('-', ' ').title().strip()
if cwt:
tags.add(cwt)
origin = (row.get("spec_origin") or "").strip()
if origin:
tags.add(f"Made in {origin}")
st = (row.get("spec_type") or "").lower()
if "wallcovering" in st or "vinyl" in st:
tags.add("Commercial Wallcovering")
for key in ("ai_tags", "ai_styles"):
v = row.get(key)
if isinstance(v, list):
for t in v:
if isinstance(t, str) and t.strip():
tags.add(t.strip())
return sorted(tags)
def product_type(row):
st = (row.get("spec_type") or "").lower()
return "Wallcovering" if ("wallcovering" in st or "vinyl" in st) else "Fabric"
def rec_common(mfr, dw_sku, pat, town, colorway_disp, product_type_, img, conf, row):
title = f"{town} {colorway_disp}".strip() if town else f"{pat} {colorway_disp}".strip()
handle = slugify(f"{town or pat} {colorway_disp} architectural-fabrics")
return {
"sku": dw_sku, "title": title, "handle": handle, "vendor": VENDOR,
"product_type": product_type_, "price": SAMPLE_PRICE, "mfr_sku": mfr,
"pattern": pat, "town": town, "colorway": colorway_disp,
"image_url": img or "", "image_confidence": conf,
"width": row.get("spec_actual_width") or row.get("spec_width") or "",
"content": row.get("spec_content") or "",
"origin": row.get("spec_origin") or "",
"fire_rating": row.get("spec_flamecode") or "",
"finish": row.get("spec_finish") or "",
"abrasion": row.get("spec_abrasion") or "",
"care": row.get("spec_cleaning") or "",
"repeat": row.get("spec_repeat") or "",
"style": ", ".join(row["ai_styles"]) if isinstance(row.get("ai_styles"), list) else "",
"tags": " | ".join(build_tags(row, colorway_disp.lower().replace(' ', '-'), town)),
# FIX (TK-11256, 2026-09-04): rwltd_catalog.description/ai_description were being
# queried nowhere and dropped on the floor here, so create2.py never had a body_html
# to send -- every product this pipeline created shipped with NO description (the
# dw-five-field-canary cadence-regression FAIL on the 1005-item 2026-09-03 batch).
# Prefer human-authored description, fall back to ai_description. May still be empty
# for rows where the scraper never captured either -- that's a data gap, not this bug.
"description": (row.get("description") or row.get("ai_description") or "").strip(),
"pool": "A-reconfirmed-old" if row.get("_pool") == "A" else "B-new-gap",
}
def main():
ready, held = [], []
# Pool A: reconfirmed-old, already has dw_sku minted -- reuse it verbatim.
pool_a = psql_json("""
SELECT mfr_sku, dw_sku, pattern_name, color_name, virginia_town, spec_type,
spec_width, spec_actual_width, spec_content, spec_repeat, spec_flamecode,
spec_origin, spec_cleaning, spec_finish, spec_abrasion,
gallery_images, ai_tags, ai_styles, description, ai_description
FROM rwltd_catalog
WHERE dw_sku IS NOT NULL AND dw_sku <> ''
AND (shopify_product_id IS NULL OR shopify_product_id='')
AND (excluded IS NULL OR excluded=false)
AND updated_at::date >= '2026-09-01'
ORDER BY mfr_sku
""")
seen = set()
for row in pool_a:
row["_pool"] = "A"
mfr = row["mfr_sku"]
if mfr in seen:
continue
seen.add(mfr)
pat = row.get("pattern_name") or ""
town = (row.get("virginia_town") or "").strip()
cw = colorway_of(mfr, pat)
gallery = row.get("gallery_images") or []
img, conf = recover_image(cw, gallery)
rec = rec_common(mfr, row["dw_sku"], pat, town, cw.replace('-', ' ').title(),
product_type(row), img, conf, row)
(ready if img and conf in ("exact", "prefix") else held).append(rec)
# Pool B: new-gap, no dw_sku yet -- mint continuing the live DWKR- band.
pool_b = psql_json("""
SELECT mfr_sku, pattern_name, color_name, virginia_town, spec_type,
spec_width, spec_actual_width, spec_content, spec_repeat, spec_flamecode,
spec_origin, spec_cleaning, spec_finish, spec_abrasion,
gallery_images, ai_tags, ai_styles, image_url, description, ai_description
FROM rwltd_catalog
WHERE (dw_sku IS NULL OR dw_sku='')
AND (shopify_product_id IS NULL OR shopify_product_id='')
AND (excluded IS NULL OR excluded=false)
AND created_at::date >= '2026-09-01'
ORDER BY mfr_sku
""")
n = DWKR_NEXT_START
for row in pool_b:
row["_pool"] = "B"
mfr = row["mfr_sku"]
if mfr in seen:
continue
seen.add(mfr)
pat = row.get("pattern_name") or ""
town = (row.get("virginia_town") or "").strip() # empty for pool B, expected
cw = colorway_of(mfr, pat)
img = row.get("image_url") or ""
conf = "feed-direct" if img else "none"
dw_sku = f"DWKR-{n}"
n += 1
rec = rec_common(mfr, dw_sku, pat, town, cw.replace('-', ' ').title(),
product_type(row), img, conf, row)
(ready if img else held).append(rec)
# Collision guard: any image reused across >1 product -> demote all to held.
from collections import Counter
img_counts = Counter(r["image_url"] for r in ready if r["image_url"])
collided = {u for u, c in img_counts.items() if c > 1}
if collided:
keep, demoted = [], []
for r in ready:
if r["image_url"] in collided:
r["image_confidence"] = "collision-held"
r["image_url"] = ""
demoted.append(r)
else:
keep.append(r)
ready = keep
held.extend(demoted)
def write_csv(path, recs):
if not recs:
open(path, "w").close(); return
cols = list(recs[0].keys())
with open(path, "w", newline="") as f:
w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(recs)
write_csv(os.path.join(OUT, "targets_ready_v2.csv"), ready)
write_csv(os.path.join(OUT, "targets_image_held_v2.csv"), held)
by_pool = Counter(r["pool"] for r in ready)
summary = {
"pool_a_reconfirmed_old_total": len(pool_a),
"pool_b_new_gap_total": len(pool_b),
"ready_to_onboard": len(ready),
" ready_pool_a": by_pool.get("A-reconfirmed-old", 0),
" ready_pool_b": by_pool.get("B-new-gap", 0),
"image_held_for_eyeball": len(held),
"dw_sku_band_minted_for_pool_b": f"DWKR-{DWKR_NEXT_START}..DWKR-{n-1}" if pool_b else None,
"vendor": VENDOR,
"price": f"${SAMPLE_PRICE} sample-only (quote-only, 'quotes' tag)",
"distinct_patterns": len(set(r["pattern"] for r in ready + held)),
}
json.dump(summary, open(os.path.join(OUT, "summary_v2.json"), "w"), indent=2)
print(json.dumps(summary, indent=2))
if __name__ == "__main__":
main()