← back to Dw Discovery
build-data.py
207 lines
#!/usr/bin/env python3
"""
build-data.py — READ-ONLY adapter: dw_unified -> DWDiscovery product shape.
Picks ONE real, well-populated DW collection (Thibaut "TEXTURE RESOURCE 9",
149 SKUs, 100% color+hex+width+image+price coverage in thibaut_catalog) and
maps the real catalog fields into the module's product shape, writing
data/products.real.json + data/config.real.json.
NEVER writes to dw_unified. SELECT only. $0 (local PG read).
Run: python3 build-data.py
"""
import json, re, os, sys
import psycopg2
import psycopg2.extras
VENDOR_REAL = "Thibaut" # real vendor, kept for SEO (NOT a banned private-label)
COLLECTION = "TEXTURE RESOURCE 9"
OUT_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data")
# ---- REAL color-bucket derivation, from the actual color_name distribution ----
# Map every distinct color word seen in this collection to one display bucket.
# Order matters: first keyword hit wins (so "Powder Blue" -> Blue, "Forest Green" -> Green).
BUCKET_RULES = [
("Blue", ["blue", "navy", "spa", "peacock", "robin", "sky", "seamist", "dusk", "fog", "mineral"]),
("Green", ["green", "sage", "olive", "palmetto", "pine", "forest", "citron", "pale green"]),
("Neutral",["beige", "cream", "off white", "white", "ivory", "taupe", "sand", "tan", "wheat",
"straw", "pearl", "blush", "lavender", "fog"]),
("Brown", ["brown", "bark", "caramel", "saddle", "whiskey", "terracotta", "straw", "dark gold",
"wheat", "tan"]),
("Grey", ["grey", "gray", "silver", "pewter", "charcoal", "fog"]),
("Black", ["black"]),
("Gold", ["gold"]),
]
def color_bucket(name: str) -> str:
n = (name or "").strip().lower()
if not n:
return "Neutral"
for bucket, kws in BUCKET_RULES:
for kw in kws:
if kw in n:
return bucket
return "Neutral"
def title_case(s: str) -> str:
"""Title Case a color/pattern name; preserve apostrophes (Robin's Egg)."""
s = (s or "").strip()
if not s:
return s
# collapse whitespace, keep small words natural-ish but Title Case per DW rule
return " ".join(w[:1].upper() + w[1:].lower() if w else w for w in s.split(" "))
def parse_repeat(raw: str) -> float:
""" '21 (53.34 cm)' -> 21.0 ; '0' -> 0.0 ; '' -> 0.0 (uses inches, the leading number) """
if not raw:
return 0.0
m = re.match(r"\s*([0-9]+(?:\.[0-9]+)?)", str(raw))
return float(m.group(1)) if m else 0.0
def first_style(ai_styles) -> str:
"""ai_styles is a JSON array text like ["Contemporary","Coastal"] -> 'Contemporary'."""
if not ai_styles:
return "Texture"
try:
arr = ai_styles if isinstance(ai_styles, list) else json.loads(ai_styles)
if arr:
return title_case(str(arr[0]))
except Exception:
pass
return "Texture"
def main():
conn = psycopg2.connect(dbname="dw_unified")
conn.set_session(readonly=True, autocommit=True) # hard read-only guard
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("""
SELECT dw_sku, mfr_sku, pattern_name, color_name,
color_hex, dominant_color_hex,
width_inches, pattern_repeat, repeat_v,
product_type, material, collection,
COALESCE(dw_retail_price, our_price, retail_price) AS price,
is_new, discontinued, crawled_at, ai_styles, image_url
FROM thibaut_catalog
WHERE collection = %s
ORDER BY crawled_at DESC
""", (COLLECTION,))
rows = cur.fetchall()
now = None
# use the newest crawl as "now" reference so createdDaysAgo is relative + stable
import datetime
ref = max((r["crawled_at"] for r in rows if r["crawled_at"]), default=datetime.datetime.utcnow())
products = []
buckets_seen = {}
skipped = 0
for r in rows:
# DW rule: never publish with banned word / never use Unknown; fall back sku->color->skip
pattern = title_case(r["pattern_name"]) or ""
color = title_case(r["color_name"]) or ""
price = float(r["price"]) if r["price"] is not None else None
img = (r["image_url"] or "").strip()
if price is None or price <= 0 or not img:
skipped += 1
continue # honor activation gate: no price / no image -> not import-ready
# Title format: "Pattern Name Real Color Name | Brand" (color from data, never AI)
if pattern and color:
title = f"{pattern} {color} | {VENDOR_REAL}"
elif pattern:
title = f"{pattern} {r['mfr_sku'] or ''}".strip() + f" | {VENDOR_REAL}"
else:
title = f"{r['mfr_sku'] or color or r['dw_sku']} | {VENDOR_REAL}"
hex_ = (r["color_hex"] or r["dominant_color_hex"] or "#cccccc")
bucket = color_bucket(r["color_name"])
buckets_seen[bucket] = buckets_seen.get(bucket, 0) + 1
width = float(r["width_inches"]) if r["width_inches"] is not None else 0.0
repeat = parse_repeat(r["pattern_repeat"] or r["repeat_v"])
days_ago = 0
if r["crawled_at"]:
days_ago = max(0, (ref - r["crawled_at"]).days)
products.append({
"id": r["dw_sku"],
"sku": r["dw_sku"],
"mfrSku": r["mfr_sku"],
"title": title,
"vendor": VENDOR_REAL,
"category": "Wallcovering", # real product_type collapses Commercial/Wallcovering
"subtype": title_case(r["product_type"]) or "Wallcovering",
"style": first_style(r["ai_styles"]),
"colorName": color or (r["mfr_sku"] or ""),
"colorBucket": bucket,
"hex": hex_,
"width_in": round(width, 1),
"repeat_in": round(repeat, 1),
"price": round(price, 2),
"inStock": (not r["discontinued"]),
"featured": bool(r["is_new"]), # real "New Arrival" signal = featured
"createdDaysAgo": days_ago,
"image": img,
"collection": r["collection"],
"material": r["material"],
})
os.makedirs(OUT_DIR, exist_ok=True)
with open(os.path.join(OUT_DIR, "products.real.json"), "w") as f:
json.dump(products, f, indent=1)
# ---- derive the REAL config for this collection ----
# merge map: collapse raw color-name variants into the derived buckets (display alias),
# + vendor spelling aliases. These are REAL values present in the data.
color_alias = {}
cur.execute("""SELECT DISTINCT color_name FROM thibaut_catalog
WHERE collection=%s AND color_name<>''""", (COLLECTION,))
for (cn,) in [(x["color_name"],) for x in cur.fetchall()]:
color_alias[title_case(cn)] = color_bucket(cn)
config = {
"merge": {
# collapse raw color names -> bucket (used by the swatch facet via colorBucket field,
# but we expose the raw->bucket map for documentation/UI merge-value tooling)
"colorBucket": {}, # colorBucket is already canonical in the data
"vendor": {
"Thibaut Inc": "Thibaut",
"Thibaut Wallcovering": "Thibaut"
}
},
"tree": [
{"label": "Catalog", "field": "category",
"children": [{"label": "Type", "field": "subtype"}]},
{"label": "Style", "field": "style"},
{"label": "Vendor", "field": "vendor"}
],
"ranges": [
{"label": "Price", "field": "price", "prefix": "$", "step": 1},
{"label": "Width", "field": "width_in", "suffix": "\"", "step": 1},
{"label": "Repeat", "field": "repeat_in", "suffix": "\"", "step": 0.5}
],
"swatchField": "colorBucket",
"_meta": {
"collection": COLLECTION,
"vendor": VENDOR_REAL,
"rawColorNames": len(color_alias),
"colorNameToBucket": color_alias,
"buckets": buckets_seen,
"products": len(products),
"skippedNoPriceOrImage": skipped
}
}
with open(os.path.join(OUT_DIR, "config.real.json"), "w") as f:
json.dump(config, f, indent=1)
cur.close(); conn.close()
print(f"[build-data] collection={COLLECTION!r} vendor={VENDOR_REAL}")
print(f"[build-data] wrote {len(products)} products (skipped {skipped} no-price/no-image)")
print(f"[build-data] color buckets: {buckets_seen}")
print(f"[build-data] -> data/products.real.json + data/config.real.json")
if __name__ == "__main__":
main()