`db/stage-sangetsu-all.js`

63 lines
#!/usr/bin/env node
/**
 * Bulk-stage the FULL Sangetsu line into dw_unified.sangetsu_catalog (PostgreSQL-first,
 * on_shopify=false — no Shopify publish). Reads staging/sangetsu-staging.jsonl, explodes
 * each pattern to its colorway SKUs (same as the viewer), and upserts with us_distributor
 * derived from the source host (Goodrich). Chunked multi-row upsert inside one transaction.
 *
 * Local mirror is safe + reversible (TRUNCATE sangetsu_catalog). Canonical = gated.
 *   DATABASE_URL=postgresql:///dw_unified node db/stage-sangetsu-all.js
 */
const fs = require('fs'), path = require('path');
const { Client } = require('pg');

const F = path.join(__dirname, '..', 'staging', 'sangetsu-staging.jsonl');
const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified';
const CHUNK = 500;

const distOf = (url) => /sangetsu-goodrich\.co\.th/.test(url || '') ? 'Goodrich (Thailand)'
  : /sangetsu-goodrich\.vn/.test(url || '') ? 'Goodrich (Vietnam)'
  : /goodrichglobal\.com/.test(url || '') ? 'Goodrich (Singapore)' : 'Goodrich';

(async () => {
  const patterns = fs.readFileSync(F, 'utf8').trim().split('\n').filter(Boolean).map((l) => JSON.parse(l));
  // flatten pattern → colorway SKU rows (mirror the viewer's Sangetsu builder).
  // A colorway SKU can appear under >1 pattern in the feed → dedupe globally (first wins)
  // so no single upsert statement carries the same (vendor_code, mfr_sku) twice.
  const recs = [], seen = new Set();
  let dupes = 0;
  for (const r of patterns) {
    const imgs = r.sku_images || {}, us = distOf(r.source_url);
    for (const sku of (r.colorway_skus || [])) {
      if (seen.has(sku)) { dupes++; continue; }
      seen.add(sku);
      recs.push([ 'sangetsu', sku, r.pattern || sku, r.source_url || null, imgs[sku] || null, us ]);
    }
  }
  if (dupes) console.log(`skipped ${dupes} duplicate SKU(s) across patterns`);
  const db = new Client({ connectionString: DSN });
  await db.connect();
  await db.query('BEGIN');
  let n = 0;
  for (let i = 0; i < recs.length; i += CHUNK) {
    const slice = recs.slice(i, i + CHUNK);
    const vals = [], params = [];
    slice.forEach((row, j) => {
      const b = j * 6;
      vals.push(`($${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},false)`);
      params.push(...row);
    });
    await db.query(
      `insert into sangetsu_catalog (vendor_code,mfr_sku,pattern_name,product_url,image_url,us_distributor,on_shopify)
       values ${vals.join(',')}
       on conflict (vendor_code,mfr_sku) do update set
         pattern_name=excluded.pattern_name, product_url=excluded.product_url,
         image_url=excluded.image_url, us_distributor=excluded.us_distributor, updated_at=now()`,
      params);
    n += slice.length;
  }
  await db.query('COMMIT');
  await db.end();
  console.log(`staged ${n} Sangetsu colorway SKUs from ${patterns.length} patterns → sangetsu_catalog`);
})().catch((e) => { console.error(e); process.exit(1); });