japan: bulk-stage full Sangetsu line into sangetsu_catalog (13,067 SKUs, us_distributor=Goodrich, on_shopify=false; dedupe cross-pattern SKUs)

efb64720a6523f8da18b2d73d9fedaa4cc01576b · 2026-07-06 13:57:52 -0700 · Steve

Files touched

A db/stage-sangetsu-all.js

Diff

commit efb64720a6523f8da18b2d73d9fedaa4cc01576b
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 13:57:52 2026 -0700

    japan: bulk-stage full Sangetsu line into sangetsu_catalog (13,067 SKUs, us_distributor=Goodrich, on_shopify=false; dedupe cross-pattern SKUs)
---
 db/stage-sangetsu-all.js | 62 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 62 insertions(+)

diff --git a/db/stage-sangetsu-all.js b/db/stage-sangetsu-all.js
new file mode 100644
index 0000000..d763e08
--- /dev/null
+++ b/db/stage-sangetsu-all.js
@@ -0,0 +1,62 @@
+#!/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); });

← af6d4f1 japan: create per-vendor *_catalog tables (promote-queue tar · back to Japan Enrich · japan: deploy artifacts for Kamatera go-live (nginx vhost ja 4ebbbd5 →