← back to Mdc Onboard
import-drafts.py
208 lines
#!/usr/bin/env python3
"""
MDC → Phillipe Romano (Type II contract vinyl) DRAFT onboard.
Creates Shopify DRAFT products for dw_unified.mdc_catalog rows not yet on Shopify,
following the proven vahallan-onboard (quote-only) + knoll-onboard (private-label)
pattern. PRIVATE LABEL — "MDC" / mdcwall.com / the real book & designer names are
NEVER customer-facing; the only real datum pushed to Shopify is the opaque mfr SKU
code, stored in hidden custom+dwc manufacturer_sku metafields (per task spec).
Per product (task contract):
* title = dw_catalog.dw_title (already title-cased "... | Phillipe Romano",
guaranteed to contain no "Wallpaper"/"Unknown" — re-guarded here)
* status = draft (activation DRAFT->ACTIVE is a SEPARATE Steve-gated step
through settlement + the 5-field gate + publish-ex-Google)
* Sample var = {DW_SKU}-Sample @ $4.25, inventory_management=null (no inventory)
* quote-only => tags 'quotes' + 'contact-for-price' (no sellable/priced variant)
* metafields = custom.manufacturer_sku, dwc.manufacturer_sku (both = mfr_sku),
global.Brand=Phillipe Romano, global.width (when known),
global.unit_of_measure, custom.color/real_color_name
* >=1 image = image_url (every row has one; re-guarded)
BUDGET_CAT=backlog: this is a STANDALONE importer. It does NOT touch the shared
upload/variant-create budget (budget.cjs) and does NOT auto-activate — the drafts
land as backlog, to be drip-activated later under the activation-lane cap. Nothing
here consumes the "upload" pool.
PostgreSQL-first write-back: the moment a product is created, its shopify_product_id
+ on_shopify=true is written back to mdc_catalog (the local dw_unified mirror,
host=/tmp) BEFORE moving on — so the run is fully resumable and never double-creates.
Cost: $0 (local) — plain urllib POSTs to the Shopify Admin API (unmetered) + local
PG. No browser, no Gemini, no Claude API.
Usage:
python3 import-drafts.py # DRY-RUN: print first 3 payloads, create nothing
python3 import-drafts.py --limit 3 --commit # create 3 real drafts (test batch)
python3 import-drafts.py --commit # create ALL remaining (~45 min)
"""
import json, subprocess, sys, time, urllib.request, urllib.error, re, os
SHOP = 'designer-laboratory-sandbox.myshopify.com' # LIVE DW store (legacy misnomer)
API = f'https://{SHOP}/admin/api/2024-10'
TOKEN = next(l.split('=', 1)[1].strip()
for l in open('/Users/macstudio3/Projects/secrets-manager/.env')
if l.startswith('SHOPIFY_ADMIN_TOKEN='))
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'out')
os.makedirs(OUT, exist_ok=True)
COMMIT = '--commit' in sys.argv
LIMIT = int(sys.argv[sys.argv.index('--limit') + 1]) if '--limit' in sys.argv else 10**9
# Distinctive private-label brand coinages + designer names that must NEVER be
# customer-facing. Generic English words that happen to be book slugs (e.g. "vital",
# "encore", "restoration") are deliberately NOT here — they are not distinctive marks.
HARD_LEAK = re.compile(
r'\b(mdc|mdcwall|vycon|genon|bolta|lentex|nvolve|candice\s+olson|'
r'thom\s+filicia|jonathan\s+mark|taniya\s+nayak)\b', re.I)
TITLE_BAN = re.compile(r'\b(wallpaper|unknown)\b', re.I)
def psql(sql):
r = subprocess.run(['psql', '-h', '/tmp', '-d', 'dw_unified', '-v', 'ON_ERROR_STOP=1',
'-tA', '-c', sql], capture_output=True, text=True)
if r.returncode:
sys.exit('psql error: ' + r.stderr[:400])
return r.stdout
def api(method, path, body):
req = urllib.request.Request(API + path, method=method,
data=json.dumps(body).encode(),
headers={'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json'})
for _ in range(5):
try:
return json.loads(urllib.request.urlopen(req, timeout=45).read())
except urllib.error.HTTPError as e:
if e.code == 429:
time.sleep(float(e.headers.get('Retry-After', '3')) + 1); continue
raise
raise RuntimeError('429 retries exhausted')
def spec_row(label, val):
return f'<tr><td><strong>{label}</strong></td><td>{val}</td></tr>' if val and str(val).strip() else ''
def build_body(r):
coll = r['private_label_collection'] or ''
pat = r['pattern_name'] or r['dw_sku']
intro = (f'<p>{pat} from the Phillipe Romano {coll} collection — a '
f'high-performance Type II commercial wallcovering. Priced per single roll; '
f'request a memo sample to see the material in hand, or contact us for a quote.</p>')
table = '<table>' + ''.join([
spec_row('Pattern', pat),
spec_row('Color', r['color_name']),
spec_row('Material', r['material']),
spec_row('Width', r['width']),
spec_row('Backing', r['backing']),
spec_row('Weight', r['weight']),
spec_row('Fire Rating', r['fire_rating']),
spec_row('Pattern Match', r['pattern_match']),
spec_row('Certifications', r['certifications']),
spec_row('Manufacturer SKU', r['mfr_sku']),
]) + '</table>'
return intro + table
def build_payload(r):
title = (r['dw_title'] or '').strip()
coll = r['private_label_collection'] or ''
pat = r['pattern_name'] or ''
color = r['color_name'] or ''
dw = r['dw_sku']
tags = sorted({t for t in [
'Phillipe Romano', 'Wallcovering', 'Commercial', 'Type II', coll,
pat, 'quotes', 'contact-for-price', 'display_variant',
] if t and t.strip()})
mfs = [
{'namespace': 'custom', 'key': 'manufacturer_sku', 'type': 'single_line_text_field', 'value': r['mfr_sku']},
{'namespace': 'dwc', 'key': 'manufacturer_sku', 'type': 'single_line_text_field', 'value': r['mfr_sku']},
{'namespace': 'global', 'key': 'Brand', 'type': 'single_line_text_field', 'value': 'Phillipe Romano'},
{'namespace': 'global', 'key': 'unit_of_measure', 'type': 'single_line_text_field', 'value': 'Priced Per Single Roll'},
]
if color:
mfs.append({'namespace': 'custom', 'key': 'color', 'type': 'single_line_text_field', 'value': color})
mfs.append({'namespace': 'custom', 'key': 'real_color_name', 'type': 'single_line_text_field', 'value': color})
if r['width'] and r['width'].strip():
mfs.append({'namespace': 'global', 'key': 'width', 'type': 'single_line_text_field', 'value': r['width'].strip()})
return {'product': {
'title': title,
'body_html': build_body(r),
'vendor': 'Phillipe Romano',
'product_type': 'Wallcovering',
'status': 'draft',
'tags': ', '.join(tags),
'options': [{'name': 'Size', 'values': ['Sample']}],
'variants': [{'option1': 'Sample', 'price': '4.25', 'sku': f'{dw}-Sample',
'inventory_management': None, 'requires_shipping': True,
'taxable': True, 'grams': 0}],
'images': [{'src': r['image_url'], 'alt': title}],
'metafields': mfs,
}}
COLS = ['id', 'mfr_sku', 'dw_sku', 'dw_title', 'pattern_name', 'color_name',
'private_label_collection', 'width', 'backing', 'weight', 'material',
'fire_rating', 'pattern_match', 'certifications', 'image_url']
def fetch_rows():
sel = ('SELECT coalesce(json_agg(json_build_object(' +
','.join(f"'{c}',{c}" for c in COLS) +
") ORDER BY dw_sku),'[]') FROM mdc_catalog "
'WHERE NOT on_shopify AND shopify_product_id IS NULL')
return json.loads(psql(sel))
def main():
rows = fetch_rows()
print(f'candidates (not yet on Shopify): {len(rows)} | mode={"COMMIT" if COMMIT else "DRY-RUN"} | limit={LIMIT if LIMIT < 10**9 else "all"}', flush=True)
created = errs = skipped = 0
todo = rows[:LIMIT]
with (open(os.path.join(OUT, 'leak-skipped.jsonl'), 'a') as leak_log,
open(os.path.join(OUT, 'created.jsonl'), 'a') as created_log):
for r in todo:
title = (r['dw_title'] or '').strip()
# ---- hard guards (task: never Wallpaper/Unknown; >=1 image; no coinage leak)
composite = f"{title} {r['pattern_name']} {r['color_name']} {r['material']}"
reason = None
if not title: reason = 'empty-title'
elif TITLE_BAN.search(title): reason = 'title-has-wallpaper-or-unknown'
elif not (r['image_url'] or '').strip(): reason = 'no-image'
elif HARD_LEAK.search(composite): reason = 'private-label-coinage-leak'
if reason:
skipped += 1
leak_log.write(json.dumps({'dw_sku': r['dw_sku'], 'reason': reason, 'title': title}) + '\n')
print(f' SKIP {r["dw_sku"]}: {reason}', flush=True)
continue
payload = build_payload(r)
if not COMMIT:
if created < 3:
print(json.dumps(payload, indent=2)[:1400])
created += 1
continue
try:
resp = api('POST', '/products.json', payload)
pid = resp['product']['id']
# PostgreSQL-FIRST write-back (before anything else) — resumability contract
psql(f"UPDATE mdc_catalog SET shopify_product_id='{pid}', on_shopify=true "
f"WHERE id={r['id']}")
created_log.write(json.dumps({'dw_sku': r['dw_sku'], 'pid': pid, 'title': title}) + '\n')
created += 1
if created % 25 == 0:
print(f' {created}/{len(todo)} created (err {errs}, skip {skipped})', flush=True)
time.sleep(0.55) # ~1.8/s, under Shopify REST 2/s
except Exception as e:
errs += 1
detail = e.read()[:200] if isinstance(e, urllib.error.HTTPError) else str(e)
print(f' ERR {r["dw_sku"]}: {detail}', flush=True)
time.sleep(2)
print(f'DONE mode={"COMMIT" if COMMIT else "DRY-RUN"} created={created} errors={errs} skipped={skipped}', flush=True)
if __name__ == '__main__':
main()