Resolve staged price-sheet costs into shopify_products.cost (1,177 active Schumacher+Ralph Lauren, unit-aware, tagged & reversible)

89650361bb02f57805e8bb03178657b2635bd22f · 2026-06-15 10:33:42 -0700 · Steve Abrams

Files touched

A scripts/price-sheets/resolve-staging-to-cost.sql

Diff

commit 89650361bb02f57805e8bb03178657b2635bd22f
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 15 10:33:42 2026 -0700

    Resolve staged price-sheet costs into shopify_products.cost (1,177 active Schumacher+Ralph Lauren, unit-aware, tagged & reversible)
---
 scripts/price-sheets/resolve-staging-to-cost.sql | 33 ++++++++++++++++++++++++
 1 file changed, 33 insertions(+)

diff --git a/scripts/price-sheets/resolve-staging-to-cost.sql b/scripts/price-sheets/resolve-staging-to-cost.sql
new file mode 100644
index 0000000..ab667c4
--- /dev/null
+++ b/scripts/price-sheets/resolve-staging-to-cost.sql
@@ -0,0 +1,33 @@
+-- resolve-staging-to-cost.sql — write wholesale cost from vendor_price_sheets staging
+-- into dw_unified.shopify_products.cost for ACTIVE, currently-uncosted Schumacher +
+-- Ralph Lauren products. Carries the sheet's unit_of_measure into cost_unit_of_measure
+-- (the YARD/ROLL/PANEL lesson: cost is per-unit; pricing must respect that unit).
+--
+-- Run 2026-06-15. Tagged cost_source='pricesheet-2026-06-15' for full reversibility.
+-- READ→COST only. Computing retail/price from this cost is a SEPARATE gated step that
+-- must be unit-aware (per-yard fabric cost must NOT become a per-roll price).
+--
+-- REVERT:  UPDATE shopify_products SET cost=NULL, cost_unit_of_measure=NULL, cost_source=NULL
+--          WHERE cost_source='pricesheet-2026-06-15';
+
+BEGIN;
+ALTER TABLE shopify_products ADD COLUMN IF NOT EXISTS cost_unit_of_measure text;
+ALTER TABLE shopify_products ADD COLUMN IF NOT EXISTS cost_source text;
+
+CREATE TEMP TABLE stg_resolved AS
+SELECT mfr_sku_norm, max(wholesale) AS wholesale, max(unit_of_measure) AS unit
+FROM vendor_price_sheets
+WHERE (vendor ILIKE 'Schumacher%' OR vendor ILIKE 'Ralph%')
+  AND mfr_sku_norm <> '' AND wholesale > 0
+GROUP BY mfr_sku_norm;   -- verified: 0 price-conflicts, 0 unit-conflicts per norm
+
+UPDATE shopify_products p
+   SET cost = s.wholesale,
+       cost_unit_of_measure = s.unit,
+       cost_source = 'pricesheet-2026-06-15'
+  FROM stg_resolved s
+ WHERE regexp_replace(lower(coalesce(p.mfr_sku,'')),'[^a-z0-9]','','g') = s.mfr_sku_norm
+   AND upper(p.status)='ACTIVE'
+   AND (p.cost IS NULL OR p.cost = 0)
+   AND (p.vendor ILIKE 'Schumacher%' OR p.vendor ILIKE 'Ralph%');
+COMMIT;

← 1856ee2 Ingest GDrive vendor price sheets into dw_unified.vendor_pri · back to Dw Yolo Loop · Add Schumacher roll-price pusher + propagate price-sheet cos 1193fa7 →