`corrective-wallquest-cost-TK11410.sql`

41 lines
-- ============================================================================
-- TK-11410 CORRECTIVE — fix WallQuest cost_price contaminated with RETAIL.
-- *** DRAFT — GATED. Do NOT run autonomously. Steve applies via the runbook. ***
--
-- ROOT CAUSE: apply-2026-08-24.sql block 02 set cost_price = wallquest_catalog.our_price
--   (the DW RETAIL/selling price = net_cost/0.65/0.85), not the net cost. That zeroed
--   margin and false-flagged ~1,145 WallQuest products as below cost. ~2,484 rows carry
--   cost_source='wallquest_catalog' with cost_price == retail (our_price/price_dw).
--
-- The forward loader is now FIXED (block 02 reads vc.net_cost), but that UPDATE is
-- guarded by `cost_price IS NULL OR = 0`, so it will NOT overwrite the already-wrong
-- rows. This file overwrites them with the real net cost.
--
-- TARGET: Mac2 dw_unified mirror (host=/tmp). NOTE: shopify_products is Kamatera-
--   canonical, so a corresponding Kamatera write (or a Shopify custom.cost metafield
--   write + resync) is REQUIRED for the fix to persist — that is the customer-facing,
--   split-brain, GATED half. This SQL is the Mac2-mirror half for review.
-- COST: $0 (local psql). Reversible: snapshot the OLD cost_price first (below).
-- ============================================================================

-- 0) SNAPSHOT (reversibility) — save old values before the corrective UPDATE.
CREATE TABLE IF NOT EXISTS tk11410_wq_costprice_backup AS
SELECT sp.id, sp.dw_sku, sp.mfr_sku, sp.cost_price AS old_cost_price, now() AS backed_up_at
FROM shopify_products sp
JOIN wallquest_catalog vc ON vc.mfr_sku = sp.mfr_sku
WHERE sp.cost_source = 'wallquest_catalog'
  AND vc.net_cost > 0
  AND abs(sp.cost_price - vc.our_price) < 0.01;   -- only the retail-contaminated rows

-- 1) CORRECT: overwrite retail-as-cost with the real net cost.
UPDATE shopify_products sp
   SET cost_price = vc.net_cost
  FROM wallquest_catalog vc
 WHERE vc.mfr_sku = sp.mfr_sku
   AND sp.cost_source = 'wallquest_catalog'
   AND vc.net_cost > 0
   AND abs(sp.cost_price - vc.our_price) < 0.01;   -- guard: only touch the contaminated rows

-- UNDO:  UPDATE shopify_products sp SET cost_price = b.old_cost_price
--          FROM tk11410_wq_costprice_backup b WHERE b.id = sp.id;