`load-catalog.py`

49 lines
#!/usr/bin/env python3
"""Load scraped vahallan.com products into dw_unified.vahallan_catalog (local mirror, host=/tmp).
Assigns DWYG- DW SKUs (registry id 393: start 292000, x10). Existing-on-Shopify handles are
loaded but marked already_on_shopify so the importer skips them."""
import json, subprocess

EXISTING = {'unearthed-saluda','unearthed-roanoke','unearthed-woodburn','rhythm-fluent'}
prods = [json.loads(l) for l in open('data/vahallan-products.jsonl')]
prods.sort(key=lambda p: p['handle'])

ddl = """
CREATE TABLE IF NOT EXISTS vahallan_catalog (
  id serial PRIMARY KEY,
  mfr_sku text UNIQUE NOT NULL,
  handle text UNIQUE NOT NULL,
  pattern text, colorway text, title text,
  description_html text, tags text, image_url text, product_url text,
  dw_sku text UNIQUE, already_on_shopify boolean DEFAULT false,
  shopify_product_id bigint, imported_at timestamptz,
  created_at timestamptz DEFAULT now()
);
"""
rows=[]; n=292000
for p in prods:
    handle=p['handle']; title=p['title'].strip()
    words=title.split()
    pattern=words[0] if words else handle
    colorway=' '.join(words[1:]) or None
    mfr=handle.upper()
    tags=','.join(sorted({t.strip() for t in p.get('tags',[]) if t.strip()}))
    img=(p.get('images') or [{}])[0].get('src')
    on_shop = handle in EXISTING
    dw=f'DWYG-{n}'; n+=10
    rows.append((mfr,handle,pattern,colorway,title,p.get('body_html',''),tags,img,
                 f'https://vahallan.com/products/{handle}',dw,on_shop))

copy_lines=[]
for r in rows:
    copy_lines.append('\t'.join(
        (str(x).replace('\\','\\\\').replace('\t',' ').replace('\n','\\n').replace('\r','') if x is not None else '\\N')
        for x in r))
sql = ddl + """
BEGIN;
DELETE FROM vahallan_catalog;
COPY vahallan_catalog (mfr_sku,handle,pattern,colorway,title,description_html,tags,image_url,product_url,dw_sku,already_on_shopify) FROM STDIN;
""" + '\n'.join(copy_lines) + "\n\\.\nCOMMIT;\nSELECT count(*), count(*) FILTER (WHERE already_on_shopify) FROM vahallan_catalog;"
r=subprocess.run(['psql','-h','/tmp','-d','dw_unified','-v','ON_ERROR_STOP=1'],input=sql,capture_output=True,text=True)
print(r.stdout[-500:]); print(r.stderr[-500:] if r.returncode else 'OK')