`01_stage_sheet.sql`

108 lines
-- Stage the Kravet sheet into dw_unified for diffing against kravet_catalog.
-- Normalized mfr_sku variants and computed cost (MAP/2 + WHLS*Tariff%).

DROP TABLE IF EXISTS kravet_sheet_import_2026_04_20;

CREATE TABLE kravet_sheet_import_2026_04_20 (
  item                      text,
  pattern                   text,
  color                     text,
  brand                     text,
  vert_repeat               text,
  horz_repeat               text,
  repeat_uom                text,
  width                     text,
  width_uom                 text,
  country_of_origin         text,
  whls_price                text,
  unit_of_measure           text,
  content                   text,
  finish                    text,
  clean_code                text,
  durability                text,
  collection                text,
  use                       text,
  type1                     text,
  type2                     text,
  style1                    text,
  style2                    text,
  image_exists              text,
  inventory_available       text,
  image_file_hires          text,
  image_file_lores          text,
  color_1                   text,
  color_2                   text,
  color_3                   text,
  weight                    text,
  weight_uom                text,
  display_status            text,
  new_wholesale_price       text,
  new_price_effective_date  text,
  prop_65                   text,
  ufac                      text,
  direction                 text,
  wallcover_length_yd       text,
  minimum_order_qty         text,
  order_increment_qty       text,
  horizontal_half_drop      text,
  ca_tb117                  text,
  ship_from                 text,
  memo_sample_available     text,
  prop_65_chemical          text,
  prop_65_effect            text,
  qty_on_hand               text,
  lead_time_days            text,
  barcode                   text,
  map                       text,
  memo_sample_qty           text,
  ab_1817                   text,
  notes                     text,
  wallcovering_area         text,
  tariff_pct                text
);

\copy kravet_sheet_import_2026_04_20 FROM '/tmp/kravet_sheet_2026-04-20.csv' WITH (FORMAT csv, HEADER true)

-- Add derived / normalized columns for joining and cost math.
ALTER TABLE kravet_sheet_import_2026_04_20
  ADD COLUMN mfr_sku_raw        text,
  ADD COLUMN mfr_sku_norm       text,
  ADD COLUMN map_num            numeric,
  ADD COLUMN whls_num           numeric,
  ADD COLUMN tariff_pct_num     numeric,
  ADD COLUMN cost_computed      numeric,
  ADD COLUMN retail_computed    numeric,
  ADD COLUMN is_active_in_sheet boolean,
  ADD COLUMN is_in_stock        boolean;

UPDATE kravet_sheet_import_2026_04_20 SET
  mfr_sku_raw = btrim(item),
  -- Sheet uses "031032.01.0" with trailing .0; catalog sometimes stores as "031032.01"
  -- or "031032.01.0" — normalized form strips trailing ".0" and uppercases.
  mfr_sku_norm = regexp_replace(upper(btrim(item)), '\.0$', ''),
  map_num = NULLIF(btrim(map), '')::numeric,
  whls_num = NULLIF(btrim(whls_price), '')::numeric,
  tariff_pct_num = NULLIF(btrim(tariff_pct), '')::numeric,
  is_active_in_sheet = (upper(btrim(COALESCE(display_status, ''))) = 'ACTIVE'),
  is_in_stock = (upper(btrim(COALESCE(inventory_available, ''))) IN ('IN STOCK', 'LIMITED STOCK'));

UPDATE kravet_sheet_import_2026_04_20 SET
  cost_computed   = ROUND((map_num / 2.0) + (whls_num * tariff_pct_num / 100.0), 2),
  retail_computed = map_num
WHERE map_num IS NOT NULL;

CREATE INDEX ON kravet_sheet_import_2026_04_20 (mfr_sku_norm);

-- Sanity summary.
SELECT
  COUNT(*)                                                           AS total_rows,
  COUNT(*) FILTER (WHERE is_active_in_sheet)                         AS active_rows,
  COUNT(*) FILTER (WHERE NOT is_active_in_sheet)                     AS non_active_rows,
  COUNT(DISTINCT display_status)                                     AS distinct_display_status,
  COUNT(*) FILTER (WHERE map_num IS NULL)                            AS missing_map,
  COUNT(*) FILTER (WHERE cost_computed IS NULL)                      AS missing_cost
FROM kravet_sheet_import_2026_04_20;

SELECT display_status, COUNT(*) FROM kravet_sheet_import_2026_04_20
GROUP BY display_status ORDER BY COUNT(*) DESC;