`sql/wolf-gordon-pilot.sql`

36 lines
-- TK-10875 bounded recovery pilot — Wolf Gordon cohort (READ-ONLY / DRY-RUN)
--
-- For every ACTIVE Wolf Gordon product that is unbuyable but whose trade cost
-- IS joinable from wolf_gordon_catalog, emit the sellable-variant PLAN.
--
-- Retail = trade_cost / 0.65 / 0.85  (DW standing markup rule).
-- CAVEAT: catalog.price_retail was ALSO computed as trade/0.65/0.85, so
-- computed_retail == catalog_retail is a TAUTOLOGY (internal consistency only) —
-- it is NOT independent price validation. Real validation needs a vendor source.
--
-- WARNING: in the live mirror every WG row that joins here has variant_count=1
-- and a non-sample variant priced far above $4.25 → it is ALREADY sellable
-- (has_product_variant is a STALE flag; the sync job is dead). Do NOT build from
-- this raw join. pilot.mjs applies the stale-flag guard; this SQL is the raw view.
--
-- This SELECT produces a PLAN ONLY. It performs no Shopify or dw_unified write.
SELECT
  wg.id                                   AS product_id,
  wg.sku                                  AS dw_numeric_sku,   -- becomes the Roll variant SKU
  wg.sku || '-Sample'                     AS proposed_sample_sku, -- existing sample preserved
  wg.mfr_sku,
  wg.title,
  wg.has_sample_variant,
  c.pattern_name,
  c.price_trade                           AS trade_cost,
  round(c.price_trade/0.65/0.85, 2)       AS computed_retail,
  c.price_retail                          AS catalog_retail,
  (round(c.price_trade/0.65/0.85,2) = c.price_retail) AS pricing_crosscheck_ok
FROM shopify_products wg
JOIN wolf_gordon_catalog c ON c.dw_sku = wg.sku
WHERE wg.vendor='Wolf Gordon'
  AND lower(wg.status)='active'
  AND (wg.has_product_variant IS DISTINCT FROM true)
  AND c.price_trade > 0
ORDER BY wg.title;