`step5-mirror-sync.sh`

57 lines
#!/usr/bin/env bash
# STEP 5 — sync the dw_unified.shopify_products MIRROR to the new DWPF-600xxx numbers.
# Run ONLY after the live Shopify bulk (step 4) has fully completed, to avoid live/mirror drift.
# Reversible: takes a backup table before writing; whole thing is one transaction.
set -euo pipefail
export PGURL="postgresql:///dw_unified?host=/tmp"
BAK="_bak_sp_pf_sku_sync_20260713"

echo "=== PRE-CHECK: bulk must be done (expect 0 old-block DWPF still live on Shopify side is separate; here we sync the mirror) ==="
psql "$PGURL" -tA -c "SELECT 'mirror DWPF old-block rows='||count(*) FROM shopify_products WHERE regexp_replace(sku,'-Sample\$','','i') ~ '^DWPF-(15|20|1[0-9])';"

echo "=== Running backup + update + verify in ONE transaction ==="
psql "$PGURL" -v ON_ERROR_STOP=1 <<SQL
BEGIN;

-- 1. Backup the exact rows we will touch (id + both sku columns) for rollback.
DROP TABLE IF EXISTS ${BAK};
CREATE TABLE ${BAK} AS
SELECT id, sku AS old_sku, variant_sku AS old_variant_sku
FROM shopify_products
WHERE sku ILIKE 'DWPF-%'
  AND regexp_replace(sku,'-Sample\$','','i') IN (SELECT old_base FROM pf_sku_renumber_map);

-- 2. Update sku + variant_sku via the deterministic 1:1 map, preserving any suffix.
UPDATE shopify_products sp
SET sku = m.new_base || substring(sp.sku from length(m.old_base)+1),
    variant_sku = CASE
        WHEN sp.variant_sku ILIKE 'DWPF-%'
         AND regexp_replace(sp.variant_sku,'-Sample\$','','i') = m.old_base
        THEN m.new_base || substring(sp.variant_sku from length(m.old_base)+1)
        ELSE sp.variant_sku END
FROM pf_sku_renumber_map m
WHERE sp.sku ILIKE 'DWPF-%'
  AND regexp_replace(sp.sku,'-Sample\$','','i') = m.old_base;

-- 3. Verify inside the txn: 0 old-block left, all DWPF now 6-digit.
SELECT 'after_update' AS phase,
       count(*) FILTER (WHERE regexp_replace(sku,'-Sample\$','','i') ~ '^DWPF-6[0-9]{5}\$')  AS now_6digit,
       count(*) FILTER (WHERE regexp_replace(sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$') AS not_6digit_should_be_0
FROM shopify_products WHERE sku ILIKE 'DWPF-%';

-- Guard: abort if any DWPF row is not in the new 6-digit block.
DO \$\$
DECLARE bad int;
BEGIN
  SELECT count(*) INTO bad FROM shopify_products
   WHERE sku ILIKE 'DWPF-%' AND regexp_replace(sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$';
  IF bad > 0 THEN RAISE EXCEPTION 'ABORT: % DWPF mirror rows not in 600xxx block', bad; END IF;
END \$\$;

COMMIT;
SQL

echo "=== POST: variant_sku sanity ==="
psql "$PGURL" -tA -c "SELECT 'variant_sku still old-block='||count(*) FROM shopify_products WHERE variant_sku ILIKE 'DWPF-%' AND regexp_replace(variant_sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$';"
echo "=== DONE. Rollback if needed: UPDATE shopify_products sp SET sku=b.old_sku, variant_sku=b.old_variant_sku FROM ${BAK} b WHERE sp.id=b.id; ==="