`phase2_prepare.js`

155 lines
#!/usr/bin/env node
/**
 * PHASE 2 — FINAL MAPPING (NO WRITES).
 *
 * Inputs:
 *   audit_raw.json     — Phase 1 audit output (every legacy variant + existing DWJS)
 *   mfr_resolved.json  — legacy SKU → resolved mfr (from 13MB fmpro-master CSV)
 *
 * Outputs:
 *   mapping_final.csv  — reviewable, includes real mfr + resolved series
 *   mapping_final.json — phase 3 input
 *   summary_phase2.txt — counts, flags
 *
 * Classification rules (authoritative):
 *   vendor_code YOR (York)     → Series: York
 *   vendor_code BRE (Brewster) → Series: Brewster
 *   prefix DWBR                → Series: Brewster (confirmed)
 *   existing DWJS: leave series assignment for Phase 3 decision
 *
 * Renumber range: DWJS-90000 + counter (one per legacy product).
 */
const fs = require('fs');
const path = require('path');
const OUT = __dirname;
const NEW_SKU_START = 90000;

const audit = JSON.parse(fs.readFileSync(path.join(OUT, 'audit_raw.json'), 'utf8'));
const resolved = JSON.parse(fs.readFileSync(path.join(OUT, 'mfr_resolved.json'), 'utf8'));

// Group audit rows by product_id so we assign a single new DWJS number per product
const byProduct = new Map();
for (const r of audit) {
  if (!byProduct.has(r.product_id)) byProduct.set(r.product_id, []);
  byProduct.get(r.product_id).push(r);
}

// We only renumber legacy (non-DWJS) products. Existing DWJS keeps its number.
let counter = NEW_SKU_START;
const productAssignments = new Map(); // product_id → new base number

for (const [pid, variants] of byProduct) {
  const any = variants[0];
  if (any.flags && any.flags.includes('EXISTING_DWJS_NO_RENAME')) continue;
  productAssignments.set(pid, counter++);
}
console.log(`legacy products requiring renumber: ${productAssignments.size} (DWJS-${NEW_SKU_START}..DWJS-${counter-1})`);

// Build final rows
const cols = ['product_id','variant_id','current_sku','new_sku','sample_or_bolt','product_status','vendor','title',
              'current_mfr_in_shopify','resolved_mfr','resolved_vendor_code','mfr_source','series_final','series_reason','price','flags'];
const lines = [cols.join(',')];
const jsonRows = [];

function csvEsc(s) { if (s==null) return ''; const str=String(s); return /[",\n]/.test(str) ? '"'+str.replace(/"/g,'""')+'"' : str; }

function classifyFinal(row, res) {
  if (res && res.resolved_vendor_code) {
    const v = res.resolved_vendor_code.toUpperCase();
    if (v === 'YOR') return { series: 'York', reason: '13MB-csv vendor_code=YOR' };
    if (v === 'BRE') return { series: 'Brewster', reason: '13MB-csv vendor_code=BRE' };
  }
  // fall back to the Phase 1 series_guess
  if (row.series_guess === 'York' || row.series_guess === 'Brewster') {
    return { series: row.series_guess, reason: row.series_reason };
  }
  return { series: 'Unknown', reason: 'no vendor_code + unclassified' };
}

const outSummary = { legacyByPrefix:{}, bySeriesFinal:{}, byStatus:{}, unresolvedMfr: [], flagCounts: {} };

for (const [pid, variants] of byProduct) {
  const isLegacy = productAssignments.has(pid);
  const newBase = productAssignments.get(pid);
  const any = variants[0];
  // resolved data is keyed by hyphenless dw sku (per-product, bolt)
  const boltSku = (variants.find(v => v.sample_or_bolt === 'bolt') || any).current_sku;
  const key = boltSku.replace('-sample','').replace('-Sample','').replace('-','');
  const res = resolved[key] || null;

  for (const v of variants) {
    const { series, reason } = classifyFinal(v, res);
    const newSku = isLegacy
      ? `DWJS-${newBase}${/sample/i.test(v.current_sku+v.title)?'-Sample':''}`
      : v.current_sku;
    const resolvedMfr = res?.resolved_mfr || v.mfr_sku || '';
    const mfrSource = res?.source || (v.mfr_sku ? 'shopify-only' : 'NONE');
    const flags = (v.flags || '').split('|').filter(Boolean);
    if (isLegacy && !resolvedMfr) flags.push('NO_RESOLVED_MFR');
    if (series === 'Unknown') flags.push('UNKNOWN_SERIES');

    const row = {
      product_id: v.product_id,
      variant_id: v.variant_id,
      current_sku: v.current_sku,
      new_sku: newSku,
      sample_or_bolt: v.sample_or_bolt,
      product_status: v.product_status,
      vendor: v.vendor,
      title: v.title,
      current_mfr_in_shopify: v.mfr_sku || '',
      resolved_mfr: resolvedMfr,
      resolved_vendor_code: res?.resolved_vendor_code || '',
      mfr_source: mfrSource,
      series_final: series,
      series_reason: reason,
      price: v.price,
      flags: flags.join('|'),
    };
    jsonRows.push(row);
    lines.push(cols.map(c => csvEsc(row[c])).join(','));

    // summaries
    const prefix = v.current_sku.split('-')[0] || '';
    if (isLegacy) outSummary.legacyByPrefix[prefix] = (outSummary.legacyByPrefix[prefix]||0)+1;
    outSummary.bySeriesFinal[series] = (outSummary.bySeriesFinal[series]||0)+1;
    outSummary.byStatus[v.product_status] = (outSummary.byStatus[v.product_status]||0)+1;
    for (const f of flags) outSummary.flagCounts[f] = (outSummary.flagCounts[f]||0)+1;
    if (isLegacy && !resolvedMfr) outSummary.unresolvedMfr.push({sku: v.current_sku, title: v.title});
  }
}

fs.writeFileSync(path.join(OUT, 'mapping_final.csv'), lines.join('\n'));
fs.writeFileSync(path.join(OUT, 'mapping_final.json'), JSON.stringify(jsonRows, null, 2));

const summary = [
  '=== PHASE 2 FINAL MAPPING SUMMARY ===',
  `Generated: ${new Date().toISOString()}`,
  `Total variants: ${jsonRows.length}`,
  `Unique products: ${byProduct.size}`,
  `Legacy products renumbered: ${productAssignments.size}`,
  `Range: DWJS-${NEW_SKU_START}..DWJS-${counter-1}`,
  '',
  '--- Legacy variants by prefix ---',
  ...Object.entries(outSummary.legacyByPrefix).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(5)}  ${k}`),
  '',
  '--- Series final (all variants) ---',
  ...Object.entries(outSummary.bySeriesFinal).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(5)}  ${k}`),
  '',
  '--- Status ---',
  ...Object.entries(outSummary.byStatus).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(5)}  ${k}`),
  '',
  '--- Flags ---',
  ...Object.entries(outSummary.flagCounts).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(5)}  ${k}`),
  '',
  `Unresolved mfr (legacy only): ${outSummary.unresolvedMfr.length}`,
  ...outSummary.unresolvedMfr.slice(0, 20).map(u => `  ${u.sku.padEnd(20)} ${u.title.slice(0,55)}`),
].join('\n');

fs.writeFileSync(path.join(OUT, 'summary_phase2.txt'), summary);
console.log('\n' + summary);
console.log('\nFiles written:');
console.log('  mapping_final.csv   — reviewable CSV');
console.log('  mapping_final.json  — Phase 3 input');
console.log('  summary_phase2.txt  — counts');