`build-grs-map.mjs`

99 lines
// Build a clean GRS dw_sku -> real mfr code map from the FM dump.
// Read-only. Produces a reviewed fill map + a junk/needs-manual list.
import fs from 'fs';
const SRC = process.argv[2];
const OUT = process.env.HOME + '/Desktop/GRS_mfr_fill_map_' +
  new Date().toISOString().slice(0, 10).replace(/-/g, '') + '.csv';

// --- tiny CSV parser (quoted) ---
function parseCSV(txt) {
  const rows = []; let i = 0, f = '', row = [], q = false;
  while (i < txt.length) {
    const c = txt[i];
    if (q) { if (c === '"') { if (txt[i + 1] === '"') { f += '"'; i++; } else q = false; } else f += c; }
    else { if (c === '"') q = true; else if (c === ',') { row.push(f); f = ''; } else if (c === '\n') { row.push(f); rows.push(row); row = []; f = ''; } else if (c !== '\r') f += c; }
    i++;
  }
  if (f.length || row.length) { row.push(f); rows.push(row); }
  return rows;
}

const raw = parseCSV(fs.readFileSync(SRC, 'utf8'));
const hdr = raw.shift();
const idx = Object.fromEntries(hdr.map((h, i) => [h, i]));

// Extract a real mfr code token from a free-text Mfr Pattern.
// Returns {code, kind} where kind = REAL | PLACEHOLDER | JUNK | BLANK
function classify(mfrRaw, jsPattern) {
  const s = (mfrRaw || '').trim();
  if (!s) return { code: '', kind: 'BLANK' };
  const up = s.toUpperCase();
  if (/DO NOT SELL|CONFUSING|TEST RECORD|DELETE|DISCONTINUE|SEE |NEED SAMPLE/i.test(s)) return { code: '', kind: 'JUNK' };
  // grab leading code-like token: letters then digits (e.g. SC5823, GT3940, SUA211, A4, BL72600)
  const m = up.match(/\b([A-Z]{1,4}\d{1,6}[A-Z]?)\b/);
  if (m) {
    const code = m[1];
    if (code === String(jsPattern || '').toUpperCase()) return { code: '', kind: 'PLACEHOLDER' };
    return { code, kind: 'REAL' };
  }
  // pure-number mfr that differs from js pattern -> possible real numeric code, flag as REVIEW
  const n = up.match(/^(\d{2,6})\b/);
  if (n) {
    if (n[1] === String(jsPattern || '')) return { code: '', kind: 'PLACEHOLDER' };
    return { code: n[1], kind: 'REVIEW_NUM' };
  }
  return { code: '', kind: 'JUNK' };
}

const norm = s => (s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
// group all FM records by dw base sku
const byBase = new Map();
for (const r of raw) {
  if (!r.length || !r[idx.combo_sku]) continue;
  const combo = norm(r[idx.combo_sku]);              // GRS43023
  if (!combo.startsWith('GRS')) continue;
  const base = 'GRS-' + combo.slice(3);              // GRS-43023
  const rec = {
    base, mfrRaw: r[idx.mfr_pattern], js: r[idx.js_pattern],
    supplier: r[idx.supplier], net: r[idx.net_price],
    jpg: r[idx.jpg_name], recId: r[idx.fm_record_id]
  };
  rec.cls = classify(rec.mfrRaw, rec.js);
  if (!byBase.has(base)) byBase.set(base, []);
  byBase.get(base).push(rec);
}

// pick best record per base: prefer REAL > REVIEW_NUM > PLACEHOLDER > JUNK > BLANK
const rank = { REAL: 5, REVIEW_NUM: 4, PLACEHOLDER: 2, JUNK: 1, BLANK: 0 };
const out = [];
const counts = {};
for (const [base, recs] of byBase) {
  recs.sort((a, b) => rank[b.cls.kind] - rank[a.cls.kind]);
  const best = recs[0];
  const status = best.cls.kind;
  counts[status] = (counts[status] || 0) + 1;
  // pick a supplier from any record that has a non-DW one
  const supp = (recs.find(r => r.supplier && !/designer wallcoverings/i.test(r.supplier))?.supplier)
    || best.supplier || '';
  out.push({
    dw_sku: base, clean_mfr: best.cls.code, status,
    mfr_raw: (best.mfrRaw || '').trim(), supplier: supp,
    net: best.net || '', dup_records: recs.length
  });
}
out.sort((a, b) => a.dw_sku.localeCompare(b.dw_sku, undefined, { numeric: true }));

const csvq = s => '"' + String(s ?? '').replace(/"/g, '""') + '"';
const cols = ['dw_sku', 'clean_mfr', 'status', 'mfr_raw', 'supplier', 'net', 'dup_records'];
fs.writeFileSync(OUT, cols.join(',') + '\n' + out.map(o => cols.map(c => csvq(o[c])).join(',')).join('\n') + '\n');

console.log('== GRS mfr map built ==');
console.log('unique GRS base skus in FM:', out.length);
console.log('status counts:', JSON.stringify(counts, null, 0));
console.log('REAL codes recovered:', out.filter(o => o.status === 'REAL').length);
console.log('out ->', OUT);
console.log('\n-- GRS-43023 check --');
console.log(JSON.stringify(out.find(o => o.dw_sku === 'GRS-43023')));
console.log('\n-- sample REAL codes --');
for (const o of out.filter(o => o.status === 'REAL').slice(0, 12)) console.log(`${o.dw_sku} -> ${o.clean_mfr}   [${o.mfr_raw}]`);