`03_renorm_and_rediff.sql`

121 lines
-- v3: better normalization — hyphen→dot unifies W-series SKUs across sheet/catalog.
-- Keeps all previous buckets but with correct matching.

-- Rebuild mfr_sku_norm on the sheet.
UPDATE kravet_sheet_import_2026_04_20 SET
  mfr_sku_norm = regexp_replace(
                   regexp_replace(upper(btrim(item)), '-', '.', 'g'),
                   '\.0$', ''
                 );

DROP VIEW IF EXISTS v_sheet_any;
DROP VIEW IF EXISTS v_sheet_wall;
DROP VIEW IF EXISTS v_cat_dwkk;

CREATE TEMP VIEW v_sheet_any AS SELECT * FROM kravet_sheet_import_2026_04_20;

CREATE TEMP VIEW v_sheet_wall AS SELECT * FROM kravet_sheet_import_2026_04_20
WHERE upper(COALESCE(use,'')) LIKE '%WALL%';

CREATE TEMP VIEW v_cat_dwkk AS
SELECT
  id,
  dw_sku,
  regexp_replace(
    regexp_replace(upper(btrim(mfr_sku)), '-', '.', 'g'),
    '\.0$', ''
  ) AS mfr_sku_norm,
  mfr_sku, pattern_name, color_name, brand,
  shopify_product_id, on_shopify,
  price_retail, price_trade, cost_price,
  tariff_pct AS cat_tariff_pct,
  status AS cat_status
FROM kravet_catalog
WHERE dw_sku LIKE 'DWKK-%';

\echo === MATCH TEST (should be higher now) ===
SELECT
  (SELECT COUNT(*) FROM v_cat_dwkk)                                      AS cat_dwkk,
  (SELECT COUNT(*) FROM v_cat_dwkk WHERE shopify_product_id IS NOT NULL) AS cat_live,
  (SELECT COUNT(*) FROM v_cat_dwkk c JOIN v_sheet_any s USING(mfr_sku_norm)) AS cat_matched_any_sheet;

-- Rebuild buckets.
DROP TABLE IF EXISTS kravet_diff_archive_2026_04_20;
CREATE TABLE kravet_diff_archive_2026_04_20 AS
SELECT c.dw_sku, c.mfr_sku, c.pattern_name, c.color_name, c.brand,
       c.shopify_product_id, c.price_retail, c.cost_price,
       'absent_from_full_sheet' AS archive_reason
FROM v_cat_dwkk c
LEFT JOIN v_sheet_any s ON s.mfr_sku_norm = c.mfr_sku_norm
WHERE c.shopify_product_id IS NOT NULL AND s.mfr_sku_norm IS NULL;

\echo === ARCHIVE ===
SELECT COUNT(*) FROM kravet_diff_archive_2026_04_20;
SELECT brand, COUNT(*) FROM kravet_diff_archive_2026_04_20 GROUP BY brand ORDER BY COUNT(*) DESC;

DROP TABLE IF EXISTS kravet_diff_new_2026_04_20;
WITH new_rows AS (
  SELECT s.*,
         row_number() OVER (ORDER BY s.brand NULLS LAST, s.pattern, s.color, s.mfr_sku_norm) AS rn
  FROM v_sheet_wall s
  LEFT JOIN v_cat_dwkk c ON c.mfr_sku_norm = s.mfr_sku_norm
  WHERE c.id IS NULL
    AND s.map_num IS NOT NULL
    AND upper(COALESCE(s.display_status,'')) IN ('ACTIVE','LIMITED STOCK','OUTLET')
)
SELECT
  'DWKK-' || (139999 + rn)::text AS new_dw_sku,
  mfr_sku_raw                    AS mfr_sku,
  pattern                        AS pattern_name,
  color                          AS color_name,
  brand, collection, country_of_origin, width, unit_of_measure, content,
  use, type1, type2, style1, style2,
  image_file_hires, image_file_lores,
  display_status, inventory_available,
  whls_num          AS wholesale,
  map_num           AS map,
  tariff_pct_num    AS tariff_pct,
  cost_computed     AS cost,
  retail_computed   AS shopify_price_map,
  (COALESCE(image_file_hires,'') <> '' OR COALESCE(image_file_lores,'') <> '') AS has_image,
  (COALESCE(width,'') <> '')            AS has_width,
  upper(COALESCE(inventory_available,'')) AS stock_status
INTO kravet_diff_new_2026_04_20
FROM new_rows
ORDER BY rn;

\echo === NEW ===
SELECT
  COUNT(*) AS new_total,
  COUNT(*) FILTER (WHERE has_image AND has_width) AS ready_to_activate,
  COUNT(*) FILTER (WHERE NOT has_image) AS missing_image
FROM kravet_diff_new_2026_04_20;
SELECT brand, COUNT(*) FROM kravet_diff_new_2026_04_20 GROUP BY brand ORDER BY COUNT(*) DESC;

DROP TABLE IF EXISTS kravet_diff_update_2026_04_20;
CREATE TABLE kravet_diff_update_2026_04_20 AS
SELECT c.dw_sku, c.shopify_product_id, c.mfr_sku,
       c.pattern_name, c.color_name, c.brand,
       c.price_retail AS current_retail, s.map_num AS sheet_map,
       c.cost_price AS current_cost, s.cost_computed AS sheet_cost,
       c.cat_tariff_pct AS current_tariff_pct, s.tariff_pct_num AS sheet_tariff_pct,
       s.display_status AS sheet_display_status, s.inventory_available AS sheet_inventory,
       CASE
         WHEN c.price_retail IS DISTINCT FROM s.map_num THEN 'retail_changed'
         WHEN c.cost_price    IS DISTINCT FROM s.cost_computed THEN 'cost_changed'
         WHEN c.cat_tariff_pct IS DISTINCT FROM s.tariff_pct_num THEN 'tariff_changed'
         ELSE 'ok'
       END AS change_flag
FROM v_cat_dwkk c
JOIN v_sheet_any s ON s.mfr_sku_norm = c.mfr_sku_norm
WHERE c.shopify_product_id IS NOT NULL;

\echo === UPDATE ===
SELECT change_flag, COUNT(*) FROM kravet_diff_update_2026_04_20 GROUP BY change_flag ORDER BY COUNT(*) DESC;

\copy (SELECT * FROM kravet_diff_archive_2026_04_20 ORDER BY brand NULLS LAST, pattern_name, color_name) TO '/tmp/kravet_archive_2026-04-20.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT * FROM kravet_diff_new_2026_04_20    ORDER BY brand NULLS LAST, pattern_name, color_name) TO '/tmp/kravet_new_2026-04-20.csv'     WITH (FORMAT csv, HEADER true)
\copy (SELECT * FROM kravet_diff_update_2026_04_20 WHERE change_flag <> 'ok' ORDER BY change_flag, brand) TO '/tmp/kravet_update_2026-04-20.csv' WITH (FORMAT csv, HEADER true)

\echo === CSVs rewritten ===