`audit_phase1.js`

357 lines
#!/usr/bin/env node
/**
 * PHASE 1 — AUDIT (NO WRITES).
 *
 * Pulls all candidate products (DWBR + CCA + TRF + GGA + other DWB*) plus all
 * existing DWJS (for series classification), extracts mfr_sku metafield + tags,
 * classifies each as York/Brewster/Unknown, and proposes new DWJS-##### numbers
 * starting at 90000.
 *
 * Output:
 *  - mapping_audit.csv   — reviewable mapping for user
 *  - audit_summary.txt   — counts, flags, sample rows
 *  - audit_raw.json      — full structured dump for phase 3 ingestion
 *
 * IMPORTANT: emits CSV and JSON only. No Shopify writes.
 */

const https = require('https');
const fs = require('fs');
const path = require('path');

const STORE = 'designer-laboratory-sandbox.myshopify.com';
const TOKEN = (process.env.SHOPIFY_ADMIN_TOKEN || '');
const API = '/admin/api/2024-10/graphql.json';
const OUT = __dirname;
const NEW_SKU_START = 90000;

// Which prefixes are candidates to renumber
const LEGACY_FILTER = '(sku:DWBR* OR sku:CCA* OR sku:TRF* OR sku:GGA*)';
// For classification, also pull existing DWJS so we can confirm series tagging
const DWJS_FILTER = 'sku:DWJS*';

function gql(body, retry = 0) {
  return new Promise((resolve, reject) => {
    const data = JSON.stringify(body);
    const req = https.request(
      { hostname: STORE, path: API, method: 'POST',
        headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) } },
      res => {
        let c = '';
        res.on('data', d => (c += d));
        res.on('end', async () => {
          try {
            const j = JSON.parse(c);
            const tri = j?.extensions?.cost?.throttleStatus;
            if (j.errors && /throttle/i.test(JSON.stringify(j.errors)) && retry < 6) {
              await new Promise(r => setTimeout(r, 2000 * (retry + 1)));
              return resolve(gql(body, retry + 1));
            }
            if (tri && tri.currentlyAvailable < 200) await new Promise(r => setTimeout(r, 1000));
            resolve(j);
          } catch (e) {
            if (retry < 4) setTimeout(() => resolve(gql(body, retry + 1)), 2000 * (retry + 1));
            else resolve({ error: c.slice(0, 500) });
          }
        });
      }
    );
    req.on('error', err => {
      if (retry < 4) setTimeout(() => resolve(gql(body, retry + 1)), 2000 * (retry + 1));
      else reject(err);
    });
    req.setTimeout(60000, () => {
      req.destroy();
      if (retry < 4) resolve(gql(body, retry + 1));
      else reject(new Error('timeout'));
    });
    req.write(data);
    req.end();
  });
}

async function runBulk(filter) {
  // wait for any prior bulk op to clear
  for (let i = 0; i < 20; i++) {
    const r = await gql({ query: `{ currentBulkOperation { id status } }` });
    const st = r?.data?.currentBulkOperation?.status;
    if (!st || ['COMPLETED', 'FAILED', 'CANCELED', 'EXPIRED'].includes(st)) break;
    await new Promise(r => setTimeout(r, 3000));
  }
  const q = `
    {
      products(query: "${filter}") {
        edges {
          node {
            id
            title
            vendor
            productType
            status
            tags
            metafield(namespace: "custom", key: "manufacturer_sku") { value }
            mfr_global: metafield(namespace: "global", key: "manufacturer_sku") { value }
            coll_custom: metafield(namespace: "custom", key: "collection_name") { value }
            coll_global: metafield(namespace: "global", key: "Collection") { value }
            variants {
              edges {
                node { id sku title price }
              }
            }
          }
        }
      }
    }`;
  const r = await gql({
    query: `mutation {
      bulkOperationRunQuery(query: """${q}""") {
        bulkOperation { id status }
        userErrors { field message }
      }
    }`,
  });
  const errs = r?.data?.bulkOperationRunQuery?.userErrors || [];
  if (errs.length) {
    console.error('bulk start errors:', errs);
    // wait and retry
    await new Promise(r => setTimeout(r, 8000));
    return runBulk(filter);
  }
  // poll
  for (;;) {
    await new Promise(r => setTimeout(r, 4000));
    const p = await gql({ query: `{ currentBulkOperation { id status objectCount url errorCode } }` });
    const op = p?.data?.currentBulkOperation;
    if (!op) continue;
    if (op.status === 'COMPLETED') {
      console.log(`  bulk completed: ${op.objectCount} objects`);
      return op.url;
    }
    if (['FAILED', 'CANCELED', 'EXPIRED'].includes(op.status)) {
      throw new Error(`bulk ${op.status} ${op.errorCode || ''}`);
    }
  }
}

function dl(url, file) {
  return new Promise((resolve, reject) => {
    const out = fs.createWriteStream(file);
    https.get(url, res => { res.pipe(out); out.on('finish', () => out.close(() => resolve(file))); }).on('error', reject);
  });
}

function parseJsonl(file) {
  const products = new Map();
  const lines = fs.readFileSync(file, 'utf8').split('\n').filter(Boolean);
  for (const line of lines) {
    const o = JSON.parse(line);
    if (o.id && o.id.includes('/Product/')) {
      products.set(o.id, {
        id: o.id,
        title: o.title,
        vendor: o.vendor,
        type: o.productType,
        status: o.status,
        tags: o.tags || [],
        mfr_sku: o?.metafield?.value || o?.mfr_global?.value || '',
        current_collection: o?.coll_custom?.value || o?.coll_global?.value || '',
        variants: [],
      });
    } else if (o.id && o.id.includes('/ProductVariant/') && o.__parentId) {
      const p = products.get(o.__parentId);
      if (p) p.variants.push({ id: o.id, sku: o.sku || '', title: o.title || '', price: parseFloat(o.price || '0') });
    }
  }
  return [...products.values()];
}

function classify(p) {
  // Return {series: 'York'|'Brewster'|'Unknown', reason}
  const tagsLc = p.tags.map(t => t.toLowerCase());
  const hasTag = t => tagsLc.some(x => x === t.toLowerCase() || x.includes(t.toLowerCase()));
  const vendorLc = (p.vendor || '').toLowerCase();
  const bolt = p.variants.find(v => !/sample/i.test((v.sku || '') + (v.title || '')));
  const sku = (bolt ? bolt.sku : (p.variants[0] || {}).sku) || '';
  const prefix = sku.split('-')[0] || '';

  // Strong signals
  if (vendorLc === 'york' || vendorLc === 'york contract') return { series: 'York', reason: `vendor=${p.vendor}` };
  if (hasTag('york-contract') || hasTag('york contract')) return { series: 'York', reason: 'tag=York Contract' };
  if (hasTag('restoration elements') || hasTag('serene') || hasTag('hytex acoustics') ||
      hasTag('candice olson') || hasTag('ashford') || hasTag('magnolia') || hasTag('stacy garcia'))
    return { series: 'York', reason: 'tag=York sub-series' };

  if (hasTag('brewster')) return { series: 'Brewster', reason: 'tag:Brewster' };
  if (hasTag('brewster essentials')) return { series: 'Brewster', reason: 'tag=Brewster Essentials' };
  if (prefix === 'DWBR') return { series: 'Brewster', reason: 'sku prefix=DWBR' };
  if (prefix === 'CCA') return { series: 'Brewster', reason: 'CCA legacy (LA Walls → Brewster)' };

  if (prefix === 'TRF') return { series: 'Unknown', reason: 'TRF legacy — manual review' };
  if (prefix === 'GGA') return { series: 'Unknown', reason: 'GGA legacy — manual review' };

  return { series: 'Unknown', reason: 'no signal' };
}

function buildFlags(p) {
  const flags = [];
  if (!p.mfr_sku) flags.push('MISSING_MFR_SKU');
  const hasSample = p.variants.some(v => /sample/i.test((v.sku || '') + (v.title || '')));
  const hasBolt = p.variants.some(v => !/sample/i.test((v.sku || '') + (v.title || '')));
  if (!hasSample) flags.push('NO_SAMPLE_VARIANT');
  if (!hasBolt) flags.push('NO_BOLT_VARIANT');
  return flags;
}

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

(async () => {
  console.log('=== PHASE 1 AUDIT (NO WRITES) ===');
  console.log(`target prefix: DWJS`);
  console.log(`new-number start: ${NEW_SKU_START}`);
  console.log(`legacy filter: ${LEGACY_FILTER}`);

  console.log('\n[1/2] bulk export: legacy prefixes');
  const urlLegacy = await runBulk(LEGACY_FILTER);
  const fileLegacy = path.join(OUT, 'raw_legacy.jsonl');
  await dl(urlLegacy, fileLegacy);
  const legacy = parseJsonl(fileLegacy);
  console.log(`parsed ${legacy.length} legacy products`);

  console.log('\n[2/2] bulk export: existing DWJS (for series classification audit)');
  const urlDwjs = await runBulk(DWJS_FILTER);
  const fileDwjs = path.join(OUT, 'raw_dwjs.jsonl');
  await dl(urlDwjs, fileDwjs);
  const dwjs = parseJsonl(fileDwjs);
  console.log(`parsed ${dwjs.length} DWJS products`);

  // Build the mapping CSV
  let counter = NEW_SKU_START;
  const rows = [];
  // track duplicate mfr_sku across set
  const mfrMap = {};
  for (const p of [...legacy, ...dwjs]) {
    if (p.mfr_sku) mfrMap[p.mfr_sku] = (mfrMap[p.mfr_sku] || 0) + 1;
  }

  for (const p of legacy) {
    const { series, reason } = classify(p);
    const flags = buildFlags(p);
    if (p.mfr_sku && mfrMap[p.mfr_sku] > 1) flags.push('DUPLICATE_MFR_IN_SET');
    if (series === 'Unknown') flags.push('UNKNOWN_SERIES');
    const newBase = counter++;
    for (const v of p.variants) {
      const oldSku = v.sku || '';
      const isSample = /sample/i.test(oldSku + v.title);
      const newSku = `DWJS-${newBase}${isSample ? '-Sample' : ''}`;
      rows.push({
        current_sku: oldSku,
        new_sku: newSku,
        product_id: p.id,
        variant_id: v.id,
        product_status: p.status,
        vendor: p.vendor,
        title: p.title,
        mfr_sku: p.mfr_sku,
        series_guess: series,
        series_reason: reason,
        current_collection: p.current_collection,
        price: v.price,
        flags: flags.join('|'),
        sample_or_bolt: isSample ? 'sample' : 'bolt',
      });
    }
  }

  // Also emit DWJS products (no SKU rename, but annotate series for tagging in phase 3)
  for (const p of dwjs) {
    const { series, reason } = classify(p);
    const flags = buildFlags(p);
    if (series === 'Unknown') flags.push('UNKNOWN_SERIES');
    for (const v of p.variants) {
      rows.push({
        current_sku: v.sku || '',
        new_sku: v.sku || '', // NO RENAME for DWJS
        product_id: p.id,
        variant_id: v.id,
        product_status: p.status,
        vendor: p.vendor,
        title: p.title,
        mfr_sku: p.mfr_sku,
        series_guess: series,
        series_reason: reason,
        current_collection: p.current_collection,
        price: v.price,
        flags: flags.join('|') + '|EXISTING_DWJS_NO_RENAME',
        sample_or_bolt: /sample/i.test((v.sku || '') + v.title) ? 'sample' : 'bolt',
      });
    }
  }

  // Write CSV
  const cols = ['current_sku','new_sku','product_id','variant_id','product_status','vendor','title','mfr_sku','series_guess','series_reason','current_collection','price','sample_or_bolt','flags'];
  const csv = [cols.join(',')].concat(rows.map(r => cols.map(c => csvEscape(r[c])).join(','))).join('\n');
  fs.writeFileSync(path.join(OUT, 'mapping_audit.csv'), csv);

  // Write raw JSON (the phase-3 input)
  fs.writeFileSync(path.join(OUT, 'audit_raw.json'), JSON.stringify(rows, null, 2));

  // Write summary
  const bySeries = {}, byPrefix = {}, byStatus = {}, byFlag = {};
  for (const r of rows) {
    bySeries[r.series_guess] = (bySeries[r.series_guess]||0)+1;
    const pref = (r.current_sku.split('-')[0] || '(empty)');
    byPrefix[pref] = (byPrefix[pref]||0)+1;
    byStatus[r.product_status] = (byStatus[r.product_status]||0)+1;
    for (const f of (r.flags || '').split('|').filter(Boolean)) byFlag[f] = (byFlag[f]||0)+1;
  }

  const missingMfr = rows.filter(r => r.flags.includes('MISSING_MFR_SKU') && !r.flags.includes('EXISTING_DWJS_NO_RENAME'));
  const unknownSeries = rows.filter(r => r.flags.includes('UNKNOWN_SERIES'));

  const summary = [
    '=== PHASE 1 AUDIT SUMMARY ===',
    `Timestamp: ${new Date().toISOString()}`,
    `Total variants in audit: ${rows.length}`,
    `Unique products in audit: ${new Set(rows.map(r => r.product_id)).size}`,
    '',
    '--- by prefix (current_sku) ---',
    ...Object.entries(byPrefix).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(6)}  ${k}`),
    '',
    '--- by status ---',
    ...Object.entries(byStatus).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(6)}  ${k}`),
    '',
    '--- by series_guess ---',
    ...Object.entries(bySeries).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(6)}  ${k}`),
    '',
    '--- flags ---',
    ...Object.entries(byFlag).sort((a,b)=>b[1]-a[1]).map(([k,v])=>`  ${v.toString().padStart(6)}  ${k}`),
    '',
    `Missing mfr_sku (legacy only, non-DWJS): ${missingMfr.length}`,
    `Unknown series (needs human review): ${unknownSeries.length}`,
    '',
    '--- sample UNKNOWN_SERIES rows (first 10) ---',
    ...unknownSeries.slice(0,10).map(r => `  ${r.current_sku.padEnd(24)} ${r.vendor.padEnd(22)} ${r.title.slice(0,55)}`),
    '',
    '--- sample LEGACY rows by prefix (first 5 each) ---',
    ...['DWBR','CCA','TRF','GGA'].flatMap(pref => {
      const sub = rows.filter(r => r.current_sku.startsWith(pref + '-') && r.sample_or_bolt === 'bolt').slice(0,5);
      return [`[${pref}]`, ...sub.map(r => `  ${r.current_sku.padEnd(24)} mfr=${(r.mfr_sku||'').padEnd(14)} ${r.series_guess.padEnd(9)} ${r.title.slice(0,50)}`)];
    }),
    '',
    `Renumber range used: DWJS-${NEW_SKU_START} .. DWJS-${counter-1}`,
    '',
  ].join('\n');

  fs.writeFileSync(path.join(OUT, 'audit_summary.txt'), summary);
  console.log('\n' + summary);
  console.log(`\nFiles written to ${OUT}:`);
  console.log('  mapping_audit.csv   — reviewable CSV');
  console.log('  audit_summary.txt   — counts + samples');
  console.log('  audit_raw.json      — input for phase 3');
})().catch(e => { console.error('FATAL', e); process.exit(1); });