add lib/wallpaper.js — importable ensureWallpaper() for pipeline integration (wiring pending)

ea6b0921f9f2d8da75e00694d572a15a77b6b2f9 · 2026-07-10 08:12:20 -0700 · Steve Abrams
Files touched

A lib/wallpaper.js
Diff

commit ea6b0921f9f2d8da75e00694d572a15a77b6b2f9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Jul 10 08:12:20 2026 -0700

    add lib/wallpaper.js — importable ensureWallpaper() for pipeline integration (wiring pending)
---
 lib/wallpaper.js | 80 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 80 insertions(+)

diff --git a/lib/wallpaper.js b/lib/wallpaper.js
new file mode 100644
index 0000000..15445f4
--- /dev/null
+++ b/lib/wallpaper.js
@@ -0,0 +1,80 @@
+// Ensure a complete FileMaker WALLPAPER master record exists for a DW SKU, sourced
+// from dw_unified, so an invoice line's lookup fields (width/name/vid/supplier/mfr#)
+// resolve. Steve's rule (2026-07-10): Mfr Pattern MUST hold a real mfr number and
+// vid MUST be the vendor's code; a SKU with no findable mfr number is FLAGGED, not
+// created. See memory filemaker-wallpaper-record-for-invoice.
+//
+// The invoice's width/supplier/name resolve LIVE once this record exists; VID and
+// Detail 1 Mfr Number are COPY-lookups, so the caller writes them onto the line.
+import { execFileSync } from 'node:child_process';
+import * as fm from '../src/fm-client.js';
+
+const ENTRY = 'Add wallcovering';
+const FULL = '*List Wallpapers - Full View';
+const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
+
+function sql(q) {
+  try { return JSON.parse(execFileSync(PSQL, ['dw_unified', '-tAc', q], { encoding: 'utf8' }).trim() || 'null'); }
+  catch { return null; }
+}
+const firstNum = (s) => { const m = String(s || '').match(/[\d.]+/); return m ? m[0] : ''; };
+const widthClean = (s) => { const n = firstNum(s); return n ? `${n}"` : ''; };
+
+// combo sku (DWKK134979) -> {prefix, num, dashSku}
+function parseCombo(combo) {
+  const m = String(combo || '').match(/^([A-Za-z]+)(\d.*)$/);
+  return m ? { prefix: m[1].toUpperCase(), num: m[2], dashSku: `${m[1].toUpperCase()}-${m[2]}` } : null;
+}
+
+// pull source fields for a dashed dw_sku from dw_unified (metafields-first)
+function sourceFor(dashSku) {
+  const esc = dashSku.replace(/'/g, "''");
+  const row = sql(`SELECT json_build_object(
+      'mfr', COALESCE(NULLIF(mfr_sku,''), metafields->'custom'->'manufacturer_sku'->>'value', metafields->'global'->'mfr-pattern-number'->>'value'),
+      'supplier', COALESCE(NULLIF(supplier_name,''), metafields->'dwc'->'real_vendor'->>'value', vendor),
+      'pattern', COALESCE(NULLIF(pattern_name,''), metafields->'global'->'title'->>'value'),
+      'color', COALESCE(metafields->'custom'->'color'->>'value', metafields->'global'->'Color-of-Pattern'->>'value', metafields->'dwc'->'color'->>'value'),
+      'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'dwc'->'width'->>'value')
+    ) FROM shopify_products
+    WHERE dw_sku ILIKE '${esc}' OR dw_sku ILIKE '${esc}-SAMPLE' OR dw_sku ILIKE '${esc}-Sample'
+    ORDER BY (dw_sku='${esc}') DESC LIMIT 1`);
+  if (row && row.mfr) return { ...row, src: 'shopify_products' };
+  const c = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', vendor, 'color', color_primary)
+    FROM connie_our_catalog WHERE dw_sku ILIKE '${esc}%' LIMIT 1`);
+  return c ? { ...c, src: 'connie_our_catalog' } : null;
+}
+
+// vendor vid code = the most common vid among existing WALLPAPER records of this Series
+const vidCache = {};
+async function vidForSeries(series) {
+  if (series in vidCache) return vidCache[series];
+  const r = await fm.findRecords('WALLPAPER', FULL, [{ Series: series, vid: '*' }], { limit: 25 }).catch(() => ({ records: [] }));
+  const counts = {};
+  for (const rec of r.records) { const v = (rec.fieldData.vid || '').trim(); if (v) counts[v] = (counts[v] || 0) + 1; }
+  return (vidCache[series] = Object.entries(counts).sort((a, b) => b[1] - a[1])[0]?.[0] || '');
+}
+
+// Ensure/complete the WALLPAPER record for a combo sku (DW SKU, no dashes, no -Sample).
+// Returns { ok, mfr, vid, name, color, width, supplier, src } or { ok:false, flagged, reason }.
+export async function ensureWallpaper(combo) {
+  const p = parseCombo(combo);
+  if (!p) return { ok: false, flagged: combo, reason: 'unparseable sku' };
+  const s = sourceFor(p.dashSku);
+  if (!s || !s.mfr) return { ok: false, flagged: combo, reason: `no mfr number (${s ? s.src : 'not in dw_unified'})` };
+  const vid = await vidForSeries(p.prefix);
+  const name = s.pattern || '', color = s.color || '', width = widthClean(s.width);
+  const jpg = [name, color].filter(Boolean).join(' - ');
+  const ex = await fm.findRecords('WALLPAPER', FULL, { 'combo sku': '==' + combo }, { limit: 1 }).catch(() => ({ records: [] }));
+  let id = ex.records[0]?.recordId;
+  if (!id) {
+    const res = await fm.createRecord('WALLPAPER', ENTRY, { 'Mfr Pattern': s.mfr, 'JS Pattern': p.num, Series: p.prefix, Supplier: s.supplier || '', Width: width }, { dryRun: false }).catch((e) => ({ err: e.fmCode }));
+    if (res.err) return { ok: false, flagged: combo, reason: `create failed ${res.err}` };
+    id = res.recordId;
+  } else {
+    for (const [k, v] of [['Mfr Pattern', s.mfr], ['Supplier', s.supplier || ''], ['Width', width]]) { if (v) { try { await fm.updateRecord('WALLPAPER', ENTRY, id, { [k]: v }, { dryRun: false }); } catch {} } }
+  }
+  for (const [k, v] of [['Name of Pattern', name], ['Color of Pattern', color], ['vid', vid], ['JPG Name', jpg]]) {
+    if (v) { try { await fm.updateRecord('WALLPAPER', FULL, id, { [k]: v }, { dryRun: false }); } catch {} }
+  }
+  return { ok: true, mfr: s.mfr, vid, name, color, width, supplier: s.supplier || '', src: s.src };
+}
← 01d9bff add ensure-wallpaper-for-order: auto-complete FileMaker WALL · back to Filemaker Mcp · wallpaper: resolve legacy DWLK sku -> canonical DWSW via sho 0c0536f →