← back to Crezana Scraper

Auto-assign collection-encoded DWCZ-<coll>-NNN to new rows (per-collection continue-from-max)

9917ba61c2b916db9e510a5ff546897c7bcf374e · 2026-07-14 08:11:32 -0700 · Steve Abrams

Files touched

• M scrape.mjs

Diff

commit 9917ba61c2b916db9e510a5ff546897c7bcf374e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 14 08:11:32 2026 -0700

    Auto-assign collection-encoded DWCZ-<coll>-NNN to new rows (per-collection continue-from-max)
---
 scrape.mjs | 28 ++++++++++++++++++----------
 1 file changed, 18 insertions(+), 10 deletions(-)

diff --git a/scrape.mjs b/scrape.mjs
index 0b23c19..7e331bc 100644
--- a/scrape.mjs
+++ b/scrape.mjs
@@ -136,23 +136,31 @@ async function main() {
     if (r.rows[0].inserted) ins++; else upd++;
   }
 
-  // Assign DW series SKUs (DWCZ-) to any rows still missing one — continue from the
-  // current max so the series never regresses to NULL and never collides. Idempotent:
-  // a normal re-run touches 0 rows since every existing row already carries a dw_sku.
+  // Assign collection-encoded DW series SKUs (DWCZ-<CODE>-NNN) to any rows still
+  // missing one — NNN continues from that collection's current max, so the series
+  // never regresses to NULL and never collides. Idempotent: a normal re-run touches
+  // 0 rows since every existing row already carries a dw_sku.
   const dw = await client.query(`
-    WITH mx AS (
-      SELECT COALESCE(MAX((regexp_replace(dw_sku,'^DWCZ-',''))::int), 890999) AS n
-      FROM crezana_catalog WHERE dw_sku ~ '^DWCZ-[0-9]+$'
+    WITH code(collection, c) AS (VALUES
+      ('Home','HOM'),('Printed','PRT'),('Textures','TEX'),
+      ('Embroideries','EMB'),('Embroideries II','EM2'),
+      ('Madagascars','MAD'),('Grommets & Studs','GRO')),
+    mx AS (
+      SELECT collection, MAX((regexp_replace(dw_sku,'^DWCZ-[A-Z0-9]{3}-',''))::int) AS n
+      FROM crezana_catalog WHERE dw_sku ~ '^DWCZ-[A-Z0-9]{3}-[0-9]+$' GROUP BY collection
     ),
     todo AS (
-      SELECT id, (SELECT n FROM mx) + row_number() OVER (ORDER BY id) AS seq
-      FROM crezana_catalog WHERE dw_sku IS NULL
+      SELECT cc.id, k.c AS code,
+             COALESCE((SELECT n FROM mx WHERE mx.collection = cc.collection), 0)
+             + row_number() OVER (PARTITION BY cc.collection ORDER BY cc.id) AS seq
+      FROM crezana_catalog cc JOIN code k ON k.collection = cc.collection
+      WHERE cc.dw_sku IS NULL
     )
     UPDATE crezana_catalog c
-    SET dw_sku = 'DWCZ-' || todo.seq, updated_at = now()
+    SET dw_sku = 'DWCZ-' || todo.code || '-' || lpad(todo.seq::text, 3, '0'), updated_at = now()
     FROM todo WHERE c.id = todo.id
     RETURNING c.id`);
-  if (dw.rowCount) console.log(`Assigned ${dw.rowCount} new DWCZ- series SKU(s).`);
+  if (dw.rowCount) console.log(`Assigned ${dw.rowCount} new DWCZ-<coll>-NNN series SKU(s).`);
 
   await client.end();
   console.log(`Upserted: ${ins} inserted, ${upd} updated. Cost: $0 (local)`);

← f721c89 Auto-assign DWCZ- series SKU to new rows on scrape (idempote  ·  back to Crezana Scraper  ·  Scraper auto-assign back to DWCZ-###### 6-digit (never colle 2098191 →