`lib/wallpaper.js`

129 lines
// 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;
}

// shopify_products SELECT expression (metafields-first) — reused by both lookups.
// Metafields come in TWO shapes: nested {type,value} objects AND flat scalar strings
// (e.g. Designtex rows store "manufacturer_sku":"8405252" directly). Read both:
// ->'k'->>'value' catches nested, ->>'k' catches flat.
const SP_FIELDS = `json_build_object(
      'mfr', COALESCE(NULLIF(mfr_sku,''),
             metafields->'custom'->'manufacturer_sku'->>'value', metafields->'custom'->>'manufacturer_sku',
             metafields->'global'->'mfr-pattern-number'->>'value', metafields->'global'->>'mfr-pattern-number',
             metafields->'global'->'manufacturer_sku'->>'value', metafields->'global'->>'manufacturer_sku'),
      'supplier', COALESCE(NULLIF(supplier_name,''),
             metafields->'dwc'->'real_vendor'->>'value', metafields->'dwc'->>'real_vendor', vendor),
      'pattern', COALESCE(NULLIF(pattern_name,''),
             metafields->'custom'->'pattern_name'->>'value', metafields->'custom'->>'pattern_name',
             metafields->'global'->'title'->>'value', metafields->'global'->>'pattern_name'),
      'color', COALESCE(metafields->'custom'->'color'->>'value', metafields->'custom'->>'color',
             metafields->'global'->'Color-of-Pattern'->>'value',
             metafields->'dwc'->'color'->>'value', metafields->'dwc'->>'color'),
      'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'global'->>'width',
             metafields->'dwc'->'width'->>'value', metafields->'dwc'->>'width',
             metafields->'specs'->>'width'),
      'canonical', dw_sku)`;

// pull source fields for a dashed dw_sku from dw_unified (metafields-first).
// Tries (1) canonical dw_sku, (2) LEGACY sku/variant_sku match (e.g. DWLK-830880
// -> canonical DWSW-5003662) base-normalized, (3) connie_our_catalog fallback.
function sourceFor(dashSku) {
  const esc = dashSku.replace(/'/g, "''");
  let row = sql(`SELECT ${SP_FIELDS} 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(dw_sku)' };
  row = sql(`SELECT ${SP_FIELDS} FROM shopify_products
    WHERE upper(regexp_replace(COALESCE(NULLIF(sku,''),variant_sku),'-Sample$','','i')) = upper('${esc}') AND mfr_sku<>'' LIMIT 1`);
  if (row && row.mfr) return { ...row, src: 'shopify_products(legacy sku)' };
  // (3) universal vendor_catalog (all scraped vendors, keyed on dw_sku) — the authoritative
  // source for private-label lines absent from shopify_products (e.g. DWBR/Malibu→Brewster).
  // Join dw_sku_registry for a clean supplier name (vendor_code is a slug like 'brewster_york').
  row = sql(`SELECT json_build_object(
      'mfr', NULLIF(vc.mfr_sku,''),
      'supplier', COALESCE(NULLIF(reg.vendor_name,''), NULLIF(vc.original_vendor_name,'')),
      'pattern', NULLIF(vc.pattern_name,''),
      'color', COALESCE(NULLIF(vc.color_name,''), NULLIF(vc.color_primary,'')),
      'width', COALESCE(NULLIF(vc.width,''), NULLIF(vc.width_inches::text,'')))
    FROM vendor_catalog vc LEFT JOIN dw_sku_registry reg ON reg.dw_sku = vc.dw_sku
    WHERE vc.dw_sku ILIKE '${esc}' AND NULLIF(vc.mfr_sku,'') IS NOT NULL LIMIT 1`);
  if (row && row.mfr) return { ...row, src: 'vendor_catalog' };
  // (4) dw_sku_registry — authoritative mfr + vendor_name backstop (no pattern/color/width)
  row = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', NULLIF(vendor_name,''))
    FROM dw_sku_registry WHERE dw_sku ILIKE '${esc}' AND NULLIF(mfr_sku,'') IS NOT NULL LIMIT 1`);
  if (row && row.mfr) return { ...row, src: 'dw_sku_registry' };
  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] || '');
}

// READ-ONLY resolve — what WOULD be written for a combo sku, with NO FileMaker writes.
// Returns { ok, mfr, vid, name, color, width, supplier, jpg, src } or { ok:false, reason }.
// Used by ensureWallpaper (below) and by dry-run/preview callers.
export async function resolveWallpaperSource(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(' - ');
  return { ok: true, mfr: s.mfr, vid, name, color, width, supplier: s.supplier || '', jpg, src: s.src, _p: p };
}

// 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 r = await resolveWallpaperSource(combo);
  if (!r.ok) return r;
  const p = r._p, s = { mfr: r.mfr, supplier: r.supplier };
  const vid = r.vid, name = r.name, color = r.color, width = r.width, jpg = r.jpg;
  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: r.src };
}