`scripts/merge-v2-REAL-RUN.sql`

95 lines
-- 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