`02_build_diff.sql`

173 lines
-- Build 3 diff buckets. Writes three CSV files via \copy.
--
-- Scope: Kravet *wallcoverings* only (sheet Use ILIKE '%wall%').
-- Catalog side: kravet_catalog rows with dw_sku LIKE 'DWKK-%'.
-- Match key: normalized mfr_sku (upper, strip trailing ".0").

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

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-%';

-- Diagnostics
\echo === SCOPE COUNTS ===
SELECT
  (SELECT COUNT(*) FROM v_sheet_wall)                                    AS sheet_wallcover_rows,
  (SELECT COUNT(DISTINCT mfr_sku_norm) FROM v_sheet_wall)                AS sheet_distinct,
  (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
-- DWKK products currently live on Shopify whose mfr_sku is NOT in the
-- current Kravet wallcovering sheet. Vendor dropped them.
-- =========================================================================
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_sheet' AS archive_reason
FROM v_cat_dwkk c
LEFT JOIN v_sheet_wall 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 BUCKET ===
SELECT COUNT(*) AS archive_count FROM kravet_diff_archive_2026_04_20;

-- =========================================================================
-- BUCKET 2: NEW (to prepare for push as DWKK drafts)
-- Sheet rows (wallcoverings, Display Status Active or Limited Stock, has MAP)
-- not found in kravet_catalog with a DWKK SKU.
-- Assigns a fresh DWKK-{N} SKU starting at 140000.
-- =========================================================================
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,
  -- Shopify draft gating flags (per CLAUDE.md never-activate-without-width-and-image rule)
  (COALESCE(image_file_hires,'') <> '' OR COALESCE(image_file_lores,'') <> '') AS has_image,
  (COALESCE(width,'') <> '')            AS has_width,
  -- Stock flag for visibility
  upper(COALESCE(inventory_available,'')) AS stock_status
INTO kravet_diff_new_2026_04_20
FROM new_rows
ORDER BY rn;

\echo === NEW BUCKET ===
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,
  COUNT(DISTINCT brand)                                                AS distinct_brands
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 10;

-- =========================================================================
-- BUCKET 3: UPDATE (informational — matched rows, flag price/tariff changes)
-- =========================================================================
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_wall s ON s.mfr_sku_norm = c.mfr_sku_norm
WHERE c.shopify_product_id IS NOT NULL;

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

-- =========================================================================
-- Export CSVs
-- =========================================================================
\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 written to /tmp/ ===