`scripts/priceability.sql`

72 lines
-- Step-0 priceability resolver (DTD verdict B, 2026-06-21). READ-ONLY.
-- Joins each ACTIVE product (live-scanned structure in active_five_field_status)
-- to its REAL cost across verified sources, EXCLUDING product_pricing placeholder
-- stubs (flat $4/$5 sample prices that are NOT roll cost).
--
-- Cost-source priority (first non-null >0 wins):
--   Kravet family:  kravet_dwkk_variant_map.price (MAP-floored) by dw_sku
--                -> product_map.map_price by dw_sku
--                -> kravet_master_price.map_price by mfr_sku
--                -> kravet_catalog (cost_price / MAP) by mfr_sku then dw_sku
--   All others:     vendor catalog price_trade / cost / price_retail (the costExpr
--                   the cadence importer trusts) by mfr_sku then dw_sku, >0 only.
--
-- Output: per-vendor split of (needs roll OR needs sample) into priceable vs hold.

\timing off
\pset pager off

-- Kravet-family vendor names (CLAUDE.md MAP list).
DROP TABLE IF EXISTS _kravet_fam;
CREATE TEMP TABLE _kravet_fam(vendor text);
INSERT INTO _kravet_fam VALUES
 ('Kravet'),('Kravet Couture'),('Kravet Design'),('Kravet Contract'),('Kravet Basics'),
 ('Lee Jofa'),('Lee Jofa Modern'),('Groundworks'),('Brunschwig & Fils'),('Cole & Son'),
 ('GP & J Baker'),('Colefax And Fowler'),('Colefax and Fowler'),('Clarke And Clarke'),
 ('Clarke & Clarke'),('Mulberry'),('Mulberry Home'),('Threads'),('Baker Lifestyle'),
 ('Andrew Martin'),('Nicolette Mayer'),('Aerin'),('Barclay Butera'),('Thom Filicia'),
 ('Gaston Y Daniela'),('Gaston y Daniela'),('Donghia');

-- Per-SKU resolved cost.
DROP TABLE IF EXISTS active_five_field_priceable;
CREATE TABLE active_five_field_priceable AS
WITH s AS (
  SELECT a.*, regexp_replace(a.shopify_id,'\D','','g') AS shop_num,
         (kf.vendor IS NOT NULL) AS is_kravet_fam
  FROM active_five_field_status a
  LEFT JOIN _kravet_fam kf ON kf.vendor = a.vendor
),
-- the mirror carries dw_sku + mfr_sku per product; join structure back to it for keys
keyed AS (
  SELECT DISTINCT ON (s.shopify_id) s.*, sp.dw_sku, sp.mfr_sku
  FROM s
  LEFT JOIN shopify_products sp ON sp.shopify_id = s.shopify_id
  ORDER BY s.shopify_id
)
SELECT
  k.shopify_id, k.vendor, k.is_kravet_fam, k.dw_sku, k.mfr_sku,
  k.has_sample, k.has_product, k.any_zero, k.product_price, k.sample_price,
  -- resolved cost/price (the number a remediation write would use)
  COALESCE(
    -- Kravet family: MAP-floored variant price, then product_map, then master price
    CASE WHEN k.is_kravet_fam THEN NULLIF(vm.price,0) END,
    CASE WHEN k.is_kravet_fam THEN NULLIF(pm.map_price,0) END,
    CASE WHEN k.is_kravet_fam THEN NULLIF(kmp.map_price,0) END,
    CASE WHEN k.is_kravet_fam THEN NULLIF(kc.dw_sell_price,0) END,
    -- generic product_map MAP by dw_sku (covers some non-kravet too)
    NULLIF(pm.map_price,0),
    -- generic product_map MAP by mfr_sku (DTD-C 2026-06-21: covers dw_sku-less
    -- private-label rows like Justin-David whose dw_sku is NULL everywhere)
    NULLIF(pmm.map_price,0)
  ) AS kravet_price,
  vm.cost  AS km_cost,
  pm.map_price AS pm_map,
  kmp.map_price AS master_map
FROM keyed k
LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku = k.dw_sku
LEFT JOIN product_map pm ON pm.dw_sku = k.dw_sku
LEFT JOIN product_map pmm ON pmm.mfr_sku IS NOT NULL
                          AND upper(pmm.mfr_sku) = upper(k.mfr_sku)
LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku) = upper(k.mfr_sku)
LEFT JOIN kravet_catalog kc ON upper(kc.mfr_sku) = upper(k.mfr_sku);