auto-data-snapshot: 2026-09-14T16:46:18 (1 data files) — scripts/merge-v2-REAL-RUN.sql

9853f752689a20de6b01b0983a0ebb5a60edc082 · 2026-09-14 16:48:20 -0700 · auto-commit-fleet
Files touched

A scripts/merge-v2-REAL-RUN.sql
Diff

commit 9853f752689a20de6b01b0983a0ebb5a60edc082
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date:   Mon Sep 14 16:48:20 2026 -0700

    auto-data-snapshot: 2026-09-14T16:46:18 (1 data files) — scripts/merge-v2-REAL-RUN.sql
---
 scripts/merge-v2-REAL-RUN.sql | 94 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 94 insertions(+)

diff --git a/scripts/merge-v2-REAL-RUN.sql b/scripts/merge-v2-REAL-RUN.sql
new file mode 100644
index 0000000..d4ef743
--- /dev/null
+++ b/scripts/merge-v2-REAL-RUN.sql
@@ -0,0 +1,94 @@
+-- TK-10670 REAL-RUN MERGE (in-place on canonical newmor_catalog), keyed by id.
+-- Decisions locked 2026-09-14: #1 NEW dw_sku=NULL (mint later), #2 ABSENT flag-only (no Shopify cascade),
+-- #3 keep bare-color mfr_sku, #4 GO. Snapshot rollback anchor: newmor_catalog_snap_20260909.
+-- DRY by default (ROLLBACK). Real run: psql -v apply=1. Mirrors merge-v2-into-canonical.sql logic exactly.
+\set ON_ERROR_STOP on
+\if :{?apply}
+\else
+  \set apply 0
+\endif
+BEGIN;
+
+-- GUARD: snapshot rollback-anchor must exist and equal 1294, else abort.
+DO $$
+DECLARE snap int;
+BEGIN
+  SELECT count(*) INTO snap FROM newmor_catalog_snap_20260909;
+  IF snap <> 1294 THEN RAISE EXCEPTION 'ABORT: snapshot anchor missing/wrong (% != 1294)', snap; END IF;
+END $$;
+
+-- key mapping for canonical rows (id -> mkey), no column added to the real table
+DROP TABLE IF EXISTS _km;
+CREATE TEMP TABLE _km AS
+SELECT id, CASE WHEN mfr_sku ~ '[0-9]'
+  THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+  ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END AS mkey
+FROM newmor_catalog;
+
+-- v2 keyed + deduped (one row per mkey; prefer most-complete)
+DROP TABLE IF EXISTS _v2;
+CREATE TEMP TABLE _v2 AS
+SELECT * FROM (
+  SELECT *,
+    CASE WHEN mfr_sku ~ '[0-9]' AND code_source='vendor_code'
+      THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+      ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END AS mkey,
+    row_number() OVER (PARTITION BY (CASE WHEN mfr_sku ~ '[0-9]' AND code_source='vendor_code'
+      THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+      ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END)
+      ORDER BY (CASE WHEN image_url<>'' THEN 1 ELSE 0 END + CASE WHEN color_name<>'' THEN 1 ELSE 0 END) DESC) rn
+  FROM newmor_catalog_refresh_20260909_v2
+) q WHERE rn=1;
+
+DROP TABLE IF EXISTS _pre; CREATE TEMP TABLE _pre AS
+  SELECT count(*) n, count(dw_sku) FILTER (WHERE dw_sku<>'') dwsku FROM newmor_catalog;
+
+-- OP1 MATCHED: update raw fields only; discontinued=false
+UPDATE newmor_catalog t SET
+  pattern_name=v.pattern_name, color_name=v.color_name, collection=v.collection,
+  product_type=v.product_type, width=v.width, length=v.length, repeat_v=v.repeat_v,
+  image_url=v.image_url, product_url=v.product_url, in_stock=v.in_stock, material=v.material,
+  match_type=v.match_type, last_scraped=now(), updated_at=now(), discontinued=false
+FROM _v2 v, _km k WHERE k.id=t.id AND k.mkey=v.mkey;
+
+-- OP2 ABSENT: flag discontinued=true; touch nothing else
+UPDATE newmor_catalog t SET discontinued=true, updated_at=now()
+FROM _km k WHERE k.id=t.id AND k.mkey NOT IN (SELECT mkey FROM _v2);
+
+-- fix id sequence (last_value was behind max(id) -> would collide on INSERT)
+SELECT setval('newmor_catalog_id_seq', (SELECT max(id) FROM newmor_catalog));
+
+-- OP3 NEW: insert raw fields; dw_sku NULL (minting stays gated). No mkey column on real table.
+INSERT INTO newmor_catalog (mfr_sku, pattern_name, color_name, collection, product_type, width, length,
+  repeat_v, image_url, product_url, in_stock, material, match_type,
+  last_scraped, created_at, updated_at, discontinued, dw_sku)
+SELECT v.mfr_sku, v.pattern_name, v.color_name, v.collection, v.product_type, v.width, v.length,
+  v.repeat_v, v.image_url, v.product_url, v.in_stock, v.material, v.match_type,
+  now(), now(), now(), false, NULL
+FROM _v2 v WHERE v.mkey NOT IN (SELECT mkey FROM _km);
+
+-- PROOF
+SELECT 'pre_rows' lbl, n::text val FROM _pre
+UNION ALL SELECT 'pre_dwsku',       dwsku::text FROM _pre
+UNION ALL SELECT 'post_rows',       count(*)::text FROM newmor_catalog
+UNION ALL SELECT 'post_dwsku',      (count(dw_sku) FILTER (WHERE dw_sku<>''))::text FROM newmor_catalog
+UNION ALL SELECT 'flagged_discont', (count(*) FILTER (WHERE discontinued))::text FROM newmor_catalog
+UNION ALL SELECT 'new_dwsku_null',  (count(*) FILTER (WHERE dw_sku IS NULL))::text FROM newmor_catalog;
+
+-- ASSERTS: dw_sku preserved, no deletes, post_rows == 1594
+DO $$
+DECLARE pre_dw int; post_dw int; pre_n int; post_n int;
+BEGIN
+  SELECT dwsku,n INTO pre_dw,pre_n FROM _pre;
+  SELECT count(*) FILTER (WHERE dw_sku<>''), count(*) INTO post_dw,post_n FROM newmor_catalog;
+  IF post_dw < pre_dw THEN RAISE EXCEPTION 'ASSERT FAIL: dw_sku dropped % -> %', pre_dw, post_dw; END IF;
+  IF post_n < pre_n THEN RAISE EXCEPTION 'ASSERT FAIL: rows deleted % -> %', pre_n, post_n; END IF;
+  IF post_n <> 1594 THEN RAISE EXCEPTION 'ASSERT FAIL: expected 1594 rows, got %', post_n; END IF;
+  RAISE NOTICE 'ASSERTS PASS: rows %->%  dw_sku %->% (preserved)', pre_n, post_n, pre_dw, post_dw;
+END $$;
+
+\if :apply
+COMMIT;
+\else
+ROLLBACK;
+\endif
← 193bb47 chore: lint, refactor (session close, TK-11424) · back to Newmor Onboard · (newest)