`assign-skus.js`

93 lines
#!/usr/bin/env node
// ============================================================================
// Assign sequential DW SKUs to wolf_gordon_catalog rows that have none.
//
// HARD RULE (Steve 2026-06-19): every DW SKU is DW<XX>-<sequential number> for
// that vendor series. WG series = DWWG-, range start 530000. NEVER embed the
// manufacturer code. REUSE an existing dw_sku wherever one is set (never
// renumber). Assign the next sequential number ONLY to rows with dw_sku IS NULL.
//
// This is a SAFE, LOCAL, REVERSIBLE write to wolf_gordon_catalog.dw_sku only.
// Nothing is written to shopify_products or to live Shopify.
//
// Usage:
//   node assign-skus.js            # dry run (prints plan, writes nothing)
//   node assign-skus.js --apply    # performs the UPDATE inside a transaction
// ============================================================================

const { pool } = require('./scraper-utils');
const APPLY = process.argv.includes('--apply');

async function main() {
  // Next sequential number = max existing DWWG-<n> + 1.
  const { rows: mx } = await pool.query(`
    SELECT COALESCE(MAX((regexp_replace(dw_sku,'^DWWG-',''))::int), 530000) AS maxseq
    FROM wolf_gordon_catalog WHERE dw_sku ~ '^DWWG-[0-9]+$'
  `);
  let next = mx[0].maxseq + 1;

  // Rows needing a SKU, deterministic order (id ASC) so re-runs are stable.
  const { rows } = await pool.query(`
    SELECT id, mfr_sku, pattern_name, color_name
    FROM wolf_gordon_catalog
    WHERE dw_sku IS NULL
    ORDER BY id ASC
  `);

  console.log('='.repeat(70));
  console.log(`  WG SKU ASSIGNMENT  (${APPLY ? 'APPLY' : 'DRY RUN'})`);
  console.log('='.repeat(70));
  console.log(`  Rows needing a dw_sku: ${rows.length}`);
  console.log(`  Next sequential start: DWWG-${next}`);
  if (rows.length === 0) { console.log('  Nothing to do.'); await pool.end(); return; }

  const plan = rows.map(r => ({ id: r.id, dw_sku: `DWWG-${next++}`, r }));
  console.log(`  Range to assign:       ${plan[0].dw_sku} .. ${plan[plan.length-1].dw_sku}`);
  console.log('-'.repeat(70));
  for (const p of plan.slice(0, 8)) {
    console.log(`    id=${String(p.id).padEnd(6)} ${p.dw_sku}  <- ${p.r.mfr_sku} | ${p.r.pattern_name||'(no pat)'} | ${p.r.color_name||'(no color)'}`);
  }
  if (plan.length > 8) console.log(`    ... and ${plan.length - 8} more`);
  console.log('-'.repeat(70));

  if (!APPLY) {
    console.log('  DRY RUN — no writes. Re-run with --apply to commit.');
    await pool.end();
    return;
  }

  // Apply inside a transaction. Guard: each UPDATE only fires if dw_sku is
  // still NULL (idempotent against a concurrent crawl insert).
  const client = await pool.connect();
  let updated = 0;
  try {
    await client.query('BEGIN');
    for (const p of plan) {
      const res = await client.query(
        `UPDATE wolf_gordon_catalog SET dw_sku = $1, updated_at = NOW()
         WHERE id = $2 AND dw_sku IS NULL`,
        [p.dw_sku, p.id]
      );
      updated += res.rowCount;
    }
    // Safety: no duplicate DWWG sequential numbers exist after the write.
    const dup = await client.query(`
      SELECT dw_sku, count(*) c FROM wolf_gordon_catalog
      WHERE dw_sku ~ '^DWWG-[0-9]+$' GROUP BY dw_sku HAVING count(*) > 1`);
    if (dup.rows.length) {
      throw new Error(`ABORT: duplicate dw_sku detected: ${dup.rows.map(d=>d.dw_sku).join(', ')}`);
    }
    await client.query('COMMIT');
    console.log(`  COMMITTED. Rows updated: ${updated}`);
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('  ROLLED BACK:', e.message);
    process.exitCode = 1;
  } finally {
    client.release();
    await pool.end();
  }
}

main().catch(e => { console.error(e); process.exit(1); });