`scripts/refresh-catalog.py`

74 lines
#!/usr/bin/env python3
"""
refresh-catalog.py — export Fentucci ACTIVE products from dw_unified to products.json
Run on Kamatera: python3 /root/Projects/Designer-Wallcoverings/DW-Websites/Fentucci/scripts/refresh-catalog.py
"""
import subprocess, json, sys, os

# Natural-materials filter (Steve 2026-06-24: "only show natural materials").
# Allow-list on title: genuine natural-fiber substrates only (grasscloth, sisal,
# linen, cork, raffia, silk, mica, stringcloth, etc.), with a hard exclude for
# vinyl / faux / metallic / mural / printed-paper finishes. This deliberately
# drops the ~267 vinyl/faux/metallic/printed-mural items and apparel leakage
# (espadrilles, bomber jackets) so fentucci.com shows the natural line only.
NATURAL_INCLUDE = (
    r"grasscloth|sisal|jute|hemp|linen|silk|cork|raffia|abaca|seagrass|"
    r"sea grass|arrowroot|paperweave|paper weave|paper-weave|mica|wool|"
    r"bamboo|reed|rattan|hessian|burlap|stringcloth|string cloth|coir"
)
# Hard exclude: synthetic/coated finishes, AND printed-pattern fabrics that
# merely sit on a natural fiber (Steve 2026-06-24: "only real natural materials"
# — the raw natural-fiber/mineral surfaces, not printed Country florals/toiles/
# plaids/botanicals/stripes or Chinoiserie). Mica (mineral) and bamboo (plant)
# are kept as real naturals; only the printed designs are dropped.
NATURAL_EXCLUDE = (
    r"vinyl|faux|mural|mylar| suede|polyester|acrylic|non-woven|metallic|"
    r"country |chinoiserie|toile|floral|plaid|botanical"
)

SQL = """SELECT row_to_json(p) FROM (
  SELECT id, title, vendor, handle, dw_sku, mfr_sku,
         price::numeric as price, image_url, tags,
         pattern_name, product_type
  FROM shopify_products
  WHERE vendor = 'Fentucci' AND status = 'ACTIVE'
    AND lower(title) ~ '{inc}'
    AND lower(title) !~ '{exc}'
  ORDER BY title
) p""".format(inc=NATURAL_INCLUDE, exc=NATURAL_EXCLUDE)

# Kamatera connects as postgres via sudo; Mac2's local dw_unified mirror takes
# a plain psql. Try plain first (works on Mac2), fall back to sudo (Kamatera).
def run_psql(argv_prefix):
    return subprocess.run(
        argv_prefix + ['psql', '-d', 'dw_unified', '-t', '-A', '-c', SQL],
        capture_output=True, text=True
    )

result = run_psql([])
if result.returncode != 0:
    result = run_psql(['sudo', '-u', 'postgres'])

if result.returncode != 0:
    print(f"[catalog] psql error: {result.stderr}", file=sys.stderr)
    sys.exit(1)

rows = []
skipped = 0
for line in result.stdout.splitlines():
    line = line.strip()
    if line:
        try:
            rows.append(json.loads(line))
        except Exception as e:
            skipped += 1
            print(f"[catalog] skip line: {e}", file=sys.stderr)

outpath = os.path.join(os.path.dirname(__file__), '..', 'data', 'products.json')
outpath = os.path.abspath(outpath)

with open(outpath, 'w') as f:
    json.dump(rows, f)

print(f"[catalog] wrote {len(rows)} products to {outpath}" + (f" ({skipped} skipped)" if skipped else ""))