`02_build_diff_v2.sql`

163 lines
-- v2: match against FULL sheet (not only wallcoverings) to avoid false
-- archives for catalog rows whose sheet counterpart is now classified as
-- fabric/multipurpose.

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(upper(btrim(mfr_sku)), '\.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 === SCOPE ===
SELECT
  (SELECT COUNT(*) FROM v_sheet_any)                                      AS sheet_total,
  (SELECT COUNT(*) FROM v_sheet_wall)                                     AS sheet_wallcover,
  (SELECT COUNT(*) FROM v_cat_dwkk)                                       AS catalog_dwkk,
  (SELECT COUNT(*) FROM v_cat_dwkk WHERE shopify_product_id IS NOT NULL)  AS catalog_live_shopify;

-- =========================================================================
-- BUCKET 1: ARCHIVE (catalog live on Shopify, absent from FULL sheet)
-- =========================================================================
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(*) AS archive_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 LIMIT 15;

-- =========================================================================
-- BUCKET 2: NEW (sheet wallcoverings not yet in catalog)
-- =========================================================================
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,
  COUNT(*) FILTER (WHERE NOT has_width)                                AS missing_width
FROM kravet_diff_new_2026_04_20;
SELECT brand, COUNT(*) FROM kravet_diff_new_2026_04_20
GROUP BY brand ORDER BY COUNT(*) DESC LIMIT 15;

-- =========================================================================
-- BUCKET 3: UPDATE
-- =========================================================================
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;

-- Export CSVs (overwrite)
\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 ===