`data/d3b-held-insert.sql`

78 lines
-- TK-11331 D3b-heldreview — stage the 79 held extend/disambiguate colorways.
-- All held/verdict logic recomputed in SQL (mirrors the node classifier):
--   norm(x)  = lower(btrim(regexp_replace(x,'\s+',' ','g')))
--   fcolor(x)= regexp_replace(replace(norm(x),'gray','grey'),'[^a-z0-9]','','g')   -- Gray/Grey fold
-- HELD = in-scope feed row whose norm(pattern) IS staged, norm(pair) NOT staged, fcolor NOT staged.
-- Stage under canon_raw (the staged pattern spelling) => DISAMBIGUATE folds whitespace-variant
-- collision patterns (Rectangular *9/16 ) into the existing group; == feed spelling for clean STAGE-NEW.
-- dw_sku omitted (NULL — assign-sku FROZEN). cost=list*0.80, hw_price(sell)=list*1.448.
-- Guarded by ON CONFLICT (pattern_name,color_name) DO NOTHING. RETURNING prints inserted ids for restore.
\set ON_ERROR_STOP on
CREATE TEMP TABLE feed_tmp (
  alt_product_description text, number text, preferred_color_number text, list_price text,
  is_roll_price text, uom text, base_width text, category_name text, pattern_name text,
  preferred_color_name text, product_line_code text
);
\copy feed_tmp FROM 'data/momentum_feed_full.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true, QUOTE E'\x01')

WITH staged AS (
  SELECT pattern_name,
         lower(btrim(regexp_replace(pattern_name,'\s+',' ','g'))) AS np,
         lower(btrim(regexp_replace(color_name,'\s+',' ','g'))) AS nc,
         regexp_replace(replace(lower(btrim(regexp_replace(color_name,'\s+',' ','g'))),'gray','grey'),'[^a-z0-9]','','g') AS fc
  FROM momentum_colorways
),
staged_pairs  AS (SELECT DISTINCT np, nc FROM staged),
staged_fcolor AS (SELECT DISTINCT np, fc FROM staged),
canon_rank AS (
  SELECT np, pattern_name,
         row_number() OVER (PARTITION BY np ORDER BY count(*) DESC, length(pattern_name) ASC) rn
  FROM staged GROUP BY np, pattern_name
),
canon1 AS (SELECT np, pattern_name AS canon_raw FROM canon_rank WHERE rn=1),
feed_norm AS (
  SELECT number, preferred_color_number, list_price, uom, base_width, category_name,
         pattern_name, preferred_color_name, product_line_code,
         lower(btrim(regexp_replace(pattern_name,'\s+',' ','g'))) AS np,
         lower(btrim(regexp_replace(preferred_color_name,'\s+',' ','g'))) AS nc,
         regexp_replace(replace(lower(btrim(regexp_replace(preferred_color_name,'\s+',' ','g'))),'gray','grey'),'[^a-z0-9]','','g') AS fc,
         row_number() OVER (PARTITION BY pattern_name, preferred_color_name ORDER BY number) AS dedup_rn
  FROM feed_tmp
  WHERE category_name IN ('Wallcovering','Acoustic')
    AND btrim(pattern_name) <> '' AND btrim(preferred_color_name) <> ''
),
feed_dedup AS (SELECT * FROM feed_norm WHERE dedup_rn = 1),
held AS (
  SELECT f.*, c.canon_raw
  FROM feed_dedup f
  JOIN canon1 c ON c.np = f.np                                   -- pattern already staged (norm)
  LEFT JOIN staged_pairs  sp ON sp.np = f.np AND sp.nc = f.nc
  LEFT JOIN staged_fcolor sx ON sx.np = f.np AND sx.fc = f.fc
  WHERE sp.np IS NULL                                            -- norm pair NOT staged
    AND sx.np IS NULL                                            -- fuzzy color NOT staged (drop Gray/Grey)
)
INSERT INTO momentum_colorways
  (pattern_name,color_name,color_number,momentum_sku,image_url,list_price,width,category,
   product_line,collection_name,uom,price_unit,cost,hw_price,sub_category,tags,created_at,updated_at)
SELECT
  canon_raw,
  preferred_color_name,
  nullif(preferred_color_number,''),
  nullif(number,''),
  NULL,
  nullif(list_price,'')::numeric,
  nullif(btrim(base_width),''),
  category_name,
  nullif(btrim(product_line_code),''),
  NULL,
  nullif(btrim(uom),''),
  nullif(btrim(uom),''),
  round(nullif(list_price,'')::numeric * 0.80, 2),
  round(nullif(list_price,'')::numeric * 1.448, 2),
  CASE WHEN category_name = 'Acoustic' THEN 'Acoustic Panel' END,
  CASE WHEN btrim(pattern_name) IN ('Hula WC','Sea Line','Palomar','Oakstone') THEN '["settlement-review"]' END,
  now(), now()
FROM held
ON CONFLICT (pattern_name,color_name) DO NOTHING
RETURNING id, pattern_name, color_name;