Carl Robinson: material-based SKU prefixes (DWGRS/PWV/CORK/MIC…) matching Daisy Bennett; DB+drafts remapped, Shopify variant-SKU updater

bccb98f81561204a03a735202ef3260855da2bbc · 2026-07-06 11:41:28 -0700 · steve@designerwallcoverings.com
Files touched

A scripts/wallquest-refresh/remap-skus-carl-robinson.cjs
A scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
Diff

commit bccb98f81561204a03a735202ef3260855da2bbc
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Mon Jul 6 11:41:28 2026 -0700

    Carl Robinson: material-based SKU prefixes (DWGRS/PWV/CORK/MIC…) matching Daisy Bennett; DB+drafts remapped, Shopify variant-SKU updater
---
 .../wallquest-refresh/remap-skus-carl-robinson.cjs | 42 +++++++++++++++++
 .../update-shopify-skus-carl-robinson.cjs          | 54 ++++++++++++++++++++++
 2 files changed, 96 insertions(+)

diff --git a/scripts/wallquest-refresh/remap-skus-carl-robinson.cjs b/scripts/wallquest-refresh/remap-skus-carl-robinson.cjs
new file mode 100644
index 00000000..056f30ba
--- /dev/null
+++ b/scripts/wallquest-refresh/remap-skus-carl-robinson.cjs
@@ -0,0 +1,42 @@
+// Re-assign Carl Robinson dw_sku with MATERIAL-BASED prefixes (Steve's directive 2026-07-06),
+// matching the Daisy Bennett scheme (grasscloth→DWGRS, paperweave→DWPWV, cork→DWCORK, mica→DWMIC…).
+// Beach-city DISPLAY names are unchanged — only the DW SKU code changes. Numeric tail = mfr CL number.
+// Updates: enriched+normalized JSON (in place), carl_robinson_catalog.dw_sku, and writes an
+// old→new map (+ shopify_product_id) for the gated Shopify variant-SKU update.
+const fs = require('fs');
+const { Client } = require('pg');
+const CONN = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
+const ENR = '/tmp/wq-carl-robinson-enriched.json';
+const NORM = '/tmp/wq-carl-robinson-normalized.json';
+const MAPOUT = '/tmp/cr-sku-remap.json';
+
+const CODE = { Grasscloth:'GRS', Paperweave:'PWV', Raffia:'RAF', Sisal:'SIS', Cork:'CORK', Mica:'MIC',
+  Netting:'NET', Bamboo:'BAM', Fiber:'FIB', Jute:'JUT', Nonwoven:'NWV', Silk:'SLK', 'Wood Veneer':'WDV' };
+const materialOf = p => (p || '').replace(/^white naturals\s*/i, '').trim() || 'Naturals';
+const newSku = (r) => { const mat = materialOf(r.pattern); const code = CODE[mat] || 'NAT';
+  const tail = (String(r.mfr_sku).match(/\d+/) || String(r.dw_sku).match(/\d+/) || ['0'])[0]; return { code, sku: `DW${code}-${tail}` }; };
+
+(async () => {
+  const enr = JSON.parse(fs.readFileSync(ENR, 'utf8'));
+  const map = [];       // {old, new, mfr}
+  const dist = {};
+  for (const r of enr) {
+    const old = r.dw_sku; const { code, sku } = newSku(r);
+    if (old !== sku) map.push({ old, new: sku, mfr: r.mfr_sku });
+    r.dw_sku = sku; dist[code] = (dist[code] || 0) + 1;
+  }
+  fs.writeFileSync(ENR, JSON.stringify(enr, null, 2));
+  if (fs.existsSync(NORM)) { const n = JSON.parse(fs.readFileSync(NORM, 'utf8')); for (const r of n) r.dw_sku = newSku(r).sku; fs.writeFileSync(NORM, JSON.stringify(n, null, 2)); }
+
+  // DB: update catalog dw_sku (keyed on mfr_sku) + pull shopify_product_id for the map
+  const db = new Client({ connectionString: CONN }); await db.connect();
+  for (const r of enr) await db.query('UPDATE carl_robinson_catalog SET dw_sku=$1, updated_at=now() WHERE mfr_sku=$2', [r.dw_sku, r.mfr_sku]);
+  for (const m of map) { const q = await db.query('SELECT shopify_product_id FROM dw_sku_registry WHERE dw_sku=$1', [m.old]); m.shopify_product_id = q.rows[0]?.shopify_product_id || null; }
+  await db.end();
+  fs.writeFileSync(MAPOUT, JSON.stringify(map, null, 2));
+
+  console.log('material-based SKU distribution:');
+  Object.entries(dist).sort((a, b) => b[1] - a[1]).forEach(([c, v]) => console.log(`  DW${c}-  ×${v}`));
+  console.log(`total=${Object.values(dist).reduce((a, b) => a + b, 0)} | remapped=${map.length} | with shopify id=${map.filter(m => m.shopify_product_id).length}`);
+  console.log(`map → ${MAPOUT}`);
+})().catch(e => { console.error('ERROR', e.message); process.exit(1); });
diff --git a/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs b/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
new file mode 100644
index 00000000..b0809821
--- /dev/null
+++ b/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
@@ -0,0 +1,54 @@
+// Update the 67 live Carl Robinson Shopify products' variant SKUs DWCR-*  →  material-based
+// (DWGRS-/DWPWV-/DWCORK-/DWMIC-…), and re-key dw_sku_registry. Beach-city titles unchanged.
+// Reads /tmp/cr-sku-remap.json (old,new,shopify_product_id). Env: SHOPIFY_ADMIN_TOKEN, DATABASE_URL.
+const fs = require('fs');
+const https = require('https');
+const { Client } = require('pg');
+
+const STORE = 'designer-laboratory-sandbox.myshopify.com';
+const API = '2024-10';
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
+const CONN = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
+const map = JSON.parse(fs.readFileSync('/tmp/cr-sku-remap.json', 'utf8')).filter(m => m.shopify_product_id);
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+function rest(method, path, body) {
+  return new Promise((resolve, reject) => {
+    const data = body ? JSON.stringify(body) : null;
+    const req = https.request({ hostname: STORE, path: `/admin/api/${API}${path}`, method,
+      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', ...(data ? { 'Content-Length': Buffer.byteLength(data) } : {}) }, timeout: 40000 },
+      res => { let d = ''; res.on('data', c => d += c); res.on('end', () => { try { resolve({ status: res.statusCode, j: d ? JSON.parse(d) : {} }); } catch (e) { resolve({ status: res.statusCode, raw: d }); } }); });
+    req.on('error', reject); req.on('timeout', () => { req.destroy(); reject(new Error('timeout')); });
+    if (data) req.write(data); req.end();
+  });
+}
+
+(async () => {
+  const db = new Client({ connectionString: CONN }); await db.connect();
+  let ok = 0, err = 0;
+  for (const m of map) {
+    try {
+      const g = await rest('GET', `/products/${m.shopify_product_id}.json?fields=id,variants`);
+      const variants = g.j?.product?.variants || [];
+      let changed = 0;
+      for (const v of variants) {
+        let nsku = null;
+        if (v.sku === m.old) nsku = m.new;
+        else if (v.sku === `${m.old}-Sample`) nsku = `${m.new}-Sample`;
+        if (nsku && nsku !== v.sku) {
+          const u = await rest('PUT', `/variants/${v.id}.json`, { variant: { id: v.id, sku: nsku } });
+          if (u.status >= 200 && u.status < 300) changed++;
+          else console.log(`  ✗ variant ${v.id} ${v.sku}→${nsku} HTTP ${u.status}`);
+          await sleep(550);
+        }
+      }
+      // re-key registry old → new
+      await db.query('UPDATE dw_sku_registry SET dw_sku=$1, updated_at=now() WHERE dw_sku=$2', [m.new, m.old]);
+      ok++; console.log(`✓ ${m.old} → ${m.new} (${changed} variant sku${changed===1?'':'s'}) [${ok}/${map.length}]`);
+    } catch (e) { err++; console.log(`✗ ${m.old} ${String(e).slice(0, 90)}`); }
+    await sleep(300);
+  }
+  console.log(`\nSKU UPDATE DONE: products=${ok} errors=${err} (variant SKUs → material-based; registry re-keyed)`);
+  await db.end();
+})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
← 4511065a Daisy Bennett: material-based SKU prefixes (DWGRS/PWV/RAF/SI · back to Designer Wallcoverings · wallquest grasscloth/veneers: login fix + Phillipe Romano re 41f0ede8 →