`fmp-breakdown.mjs`

55 lines
// Hollywood supplier breakdown from the FileMaker master (fmpro-master.csv).
// FMP record (CR-delimited, quoted): [sku_nohyphen, dw_sku, mfr_sku, vid, vendor_name]
// Join FMP.dw_sku == vendor_catalog.mfr_sku → bucket by supplier (VID/vendor).
import fs from 'node:fs';
const FMP = '/Users/macstudio3/Projects/Designer-Wallcoverings/shopify/scripts/fmpro-master.csv';

const raw = fs.readFileSync(FMP, 'utf8');
const recs = raw.split('\r');
const fmp = new Map();
const vidName = {};
const SEP = '","';
for (const r of recs) {
  if (!r) continue;
  const inner = r.replace(/^"/, '').replace(/"$/, '');
  const f = inner.split(SEP);
  if (f.length < 4) continue;
  const dwsku = (f[1] || '').trim().toUpperCase();
  const vid = (f[3] || '').trim().toLowerCase();
  const vendor = (f[4] || '').replace(/"/g, '').trim();
  if (dwsku) fmp.set(dwsku, { vid, vendor });
  // learn vid -> real supplier name from any record where vendor name is present
  if (vid && vendor) { vidName[vid] = vidName[vid] || {}; vidName[vid][vendor] = (vidName[vid][vendor] || 0) + 1; }
}
console.error('FMP map size:', fmp.size);
// best (most common) vendor name per vid
const bestName = {};
for (const [vid, names] of Object.entries(vidName)) bestName[vid] = Object.entries(names).sort((a, b) => b[1] - a[1])[0][0];

const rows = fs.readFileSync('/tmp/hw_mfr.tsv', 'utf8').trim().split('\n').map(l => l.split('\t'));
function supplier(vid, vendor) {
  vid = vid || ''; vendor = (vendor || '').trim();
  if (vid.startsWith('astsand') || /astek/i.test(vendor)) return 'Astek';
  if (vendor) return vendor.replace(/\s+/g, ' ');
  if (vid && bestName[vid]) return bestName[vid].replace(/\s+/g, ' ') + ' [' + vid + ']';
  if (vid) return 'VID:' + vid;
  return '(blank vid)';
}
const agg = {}; let matched = 0;
const vidExamples = {};
for (const [mfr, priced, slug] of rows) {
  const hit = fmp.get((mfr || '').toUpperCase());
  if (hit) matched++;
  const sup = hit ? supplier(hit.vid, hit.vendor) : '(not in FMP master)';
  const a = agg[sup] || (agg[sup] = { total: 0, priced: 0, momSlug: 0 });
  a.total++; a.priced += +priced; a.momSlug += +slug;
  if (hit && !vidExamples[sup]) vidExamples[sup] = `${mfr}→vid:${hit.vid}`;
}
const sorted = Object.entries(agg).sort((a, b) => b[1].total - a[1].total);
console.log(`\nHOLLYWOOD BY SUPPLIER  (matched in FMP: ${matched}/${rows.length})\n`);
console.log('supplier'.padEnd(30) + 'total'.padStart(7) + 'priced'.padStart(8) + 'unpriced'.padStart(9) + 'momSlug'.padStart(8) + '  example');
for (const [s, a] of sorted) {
  console.log(s.slice(0, 29).padEnd(30) + String(a.total).padStart(7) + String(a.priced).padStart(8) + String(a.total - a.priced).padStart(9) + String(a.momSlug).padStart(8) + '  ' + (vidExamples[s] || ''));
}
fs.writeFileSync(new URL('supplier-breakdown.json', import.meta.url), JSON.stringify(sorted.map(([s, a]) => ({ supplier: s, ...a })), null, 2));