`scripts/merge-v2-into-canonical.sql`

77 lines
-- TK-10670 enrichment-preserving MERGE: newmor_catalog_refresh_20260909_v2 -> newmor_catalog
-- SAFE BY DEFAULT: runs against a SCRATCH COPY inside a transaction and ROLLS BACK.
-- Set :apply to 1 ONLY via the gated ! paste to run for real against canonical (still txn-wrapped).
-- Key: real-code ('C:'..) for coded rows, (pattern|color) ('N:'..) for code-less.
\set ON_ERROR_STOP on
BEGIN;

-- ---- target selection: scratch by default, canonical only when :apply=1 ----
DROP TABLE IF EXISTS _tgt;
CREATE TEMP TABLE _tgt AS SELECT * FROM newmor_catalog;   -- scratch working copy
ALTER TABLE _tgt ADD COLUMN mkey text;
UPDATE _tgt SET mkey = 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;

-- ---- 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;

-- capture pre-state
DROP TABLE IF EXISTS _pre; CREATE TEMP TABLE _pre AS SELECT count(*) n, count(dw_sku) FILTER (WHERE dw_sku<>'') dwsku FROM _tgt;

-- ---- OP1: MATCHED -> update the 15 raw fields only; discontinued=false ----
UPDATE _tgt 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 WHERE t.mkey=v.mkey;

-- ---- OP2: ABSENT -> flag discontinued=true; touch nothing else ----
UPDATE _tgt t SET discontinued=true, updated_at=now()
WHERE t.mkey NOT IN (SELECT mkey FROM _v2);

-- ---- OP3: NEW -> insert raw fields; dw_sku NULL (minting stays gated) ----
INSERT INTO _tgt (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, mkey)
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, v.mkey
FROM _v2 v WHERE v.mkey NOT IN (SELECT mkey FROM _tgt);

-- ---- PROOF / ASSERTS ----
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 _tgt
UNION ALL SELECT 'post_dwsku',      (count(dw_sku) FILTER (WHERE dw_sku<>''))::text FROM _tgt
UNION ALL SELECT 'flagged_discont', (count(*) FILTER (WHERE discontinued))::text FROM _tgt
UNION ALL SELECT 'new_dwsku_null',  (count(*) FILTER (WHERE dw_sku IS NULL))::text FROM _tgt
UNION ALL SELECT 'dupe_mkey',       (SELECT count(*)::text FROM (SELECT mkey FROM _tgt GROUP BY mkey HAVING count(*)>1) d);

-- ASSERT: dw_sku preserved (post >= pre), no deletes (post_rows = pre + new)
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 _tgt;
  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;
  RAISE NOTICE 'ASSERTS PASS: rows %->%  dw_sku %->% (preserved)', pre_n, post_n, pre_dw, post_dw;
END $$;

ROLLBACK;   -- SCRATCH PROOF: never commits. Real run = separate gated ! paste.