← back to Kravet Sheet Sync 2026 04 20
04_persist_all_fields.sql
243 lines
-- Persist every field from the Kravet master sheet into dw_unified so all
-- values are available to flow into Shopify metafields later.
--
-- Strategy:
-- 1) Keep the staging table (`kravet_sheet_import_2026_04_20`) as the raw CSV mirror.
-- 2) Create a durable supplementary table `kravet_sheet_fields` keyed by
-- normalized mfr_sku that holds ALL 54 sheet fields. This is source of
-- truth for "what Kravet said about this SKU as of 2026-04-20".
-- 3) Upsert overlapping spec fields into kravet_catalog columns that already
-- exist (width, repeat_v/h, origin, composition, finish, collection,
-- price_trade, price_retail, price_trade_new, price_effective_date,
-- tariff_pct, unit_of_measure, leadtime, status, roll_length, application).
-- 4) Do NOT touch Shopify. Metafield push is a separate pass.
-- =========================================================================
-- STEP 1: Create durable sheet-fields table.
-- Re-runnable: drops and rebuilds from current staging.
-- =========================================================================
DROP TABLE IF EXISTS kravet_sheet_fields CASCADE;
CREATE TABLE kravet_sheet_fields (
mfr_sku_norm text PRIMARY KEY,
mfr_sku_raw text NOT NULL,
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 numeric,
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 numeric,
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 numeric,
memo_sample_qty text,
ab_1817 text,
notes text,
wallcovering_area text,
tariff_pct numeric,
cost_computed numeric,
retail_computed numeric,
sheet_snapshot_date date NOT NULL DEFAULT CURRENT_DATE,
last_synced_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON kravet_sheet_fields (mfr_sku_raw);
CREATE INDEX ON kravet_sheet_fields (brand);
CREATE INDEX ON kravet_sheet_fields (use);
CREATE INDEX ON kravet_sheet_fields (display_status);
CREATE INDEX ON kravet_sheet_fields (inventory_available);
-- Populate — deduplicate on normalized SKU keeping the row with highest MAP
-- (a safety heuristic; in practice the 44,572 rows have 44,524 distinct norms).
INSERT INTO kravet_sheet_fields (
mfr_sku_norm, mfr_sku_raw, item, pattern, color, brand,
vert_repeat, horz_repeat, repeat_uom, width, width_uom, country_of_origin,
whls_price, unit_of_measure, content, finish, clean_code, durability, collection, use,
type1, type2, style1, style2, image_exists, inventory_available,
image_file_hires, image_file_lores, color_1, color_2, color_3, weight, weight_uom,
display_status, new_wholesale_price, new_price_effective_date, prop_65, ufac, direction,
wallcover_length_yd, minimum_order_qty, order_increment_qty, horizontal_half_drop,
ca_tb117, ship_from, memo_sample_available, prop_65_chemical, prop_65_effect,
qty_on_hand, lead_time_days, barcode, map, memo_sample_qty, ab_1817, notes,
wallcovering_area, tariff_pct, cost_computed, retail_computed
)
SELECT DISTINCT ON (mfr_sku_norm)
mfr_sku_norm, mfr_sku_raw, item, pattern, color, brand,
vert_repeat, horz_repeat, repeat_uom, width, width_uom, country_of_origin,
whls_num, unit_of_measure, content, finish, clean_code, durability, collection, use,
type1, type2, style1, style2, image_exists, inventory_available,
image_file_hires, image_file_lores, color_1, color_2, color_3, weight, weight_uom,
display_status, NULLIF(btrim(new_wholesale_price),'')::numeric, new_price_effective_date,
prop_65, ufac, direction,
wallcover_length_yd, minimum_order_qty, order_increment_qty, horizontal_half_drop,
ca_tb117, ship_from, memo_sample_available, prop_65_chemical, prop_65_effect,
qty_on_hand, lead_time_days, barcode, map_num, memo_sample_qty, ab_1817, notes,
wallcovering_area, tariff_pct_num, cost_computed, retail_computed
FROM kravet_sheet_import_2026_04_20
WHERE mfr_sku_norm IS NOT NULL AND mfr_sku_norm <> ''
ORDER BY mfr_sku_norm, map_num DESC NULLS LAST;
\echo === kravet_sheet_fields populated ===
SELECT COUNT(*) AS rows_stored, COUNT(DISTINCT brand) AS distinct_brands FROM kravet_sheet_fields;
-- =========================================================================
-- STEP 2: Upsert overlapping fields into kravet_catalog where DWKK rows exist.
-- Only touches rows where dw_sku LIKE 'DWKK-%'. Writes ONLY spec/price fields,
-- never mfr_sku/dw_sku/shopify_product_id.
-- =========================================================================
-- Add tracking columns if missing (additive — no renames or drops).
ALTER TABLE kravet_catalog
ADD COLUMN IF NOT EXISTS sheet_last_synced_at timestamptz,
ADD COLUMN IF NOT EXISTS sheet_display_status text,
ADD COLUMN IF NOT EXISTS sheet_inventory_status text;
-- Counter for reporting
CREATE TEMP TABLE _cat_sync_counter (updated_count int);
WITH matched AS (
SELECT
c.id AS cat_id,
c.dw_sku,
c.price_trade AS cat_price_trade,
c.price_retail AS cat_price_retail,
c.width AS cat_width,
c.repeat_v AS cat_repeat_v,
c.repeat_h AS cat_repeat_h,
c.origin AS cat_origin,
c.composition AS cat_composition,
c.finish AS cat_finish,
c.collection AS cat_collection,
c.tariff_pct AS cat_tariff_pct,
c.price_trade_new AS cat_price_trade_new,
c.price_effective_date AS cat_price_effective_date,
c.unit_of_measure AS cat_unit_of_measure,
c.application AS cat_application,
c.roll_length AS cat_roll_length,
s.*
FROM kravet_catalog c
JOIN kravet_sheet_fields s
ON s.mfr_sku_norm = regexp_replace(
regexp_replace(upper(btrim(c.mfr_sku)), '-', '.', 'g'),
'\.0$', ''
)
WHERE c.dw_sku LIKE 'DWKK-%'
),
upd AS (
UPDATE kravet_catalog c
SET width = COALESCE(NULLIF(btrim(m.width),''), c.width),
repeat_v = COALESCE(NULLIF(btrim(m.vert_repeat),''), c.repeat_v),
repeat_h = COALESCE(NULLIF(btrim(m.horz_repeat),''), c.repeat_h),
origin = COALESCE(NULLIF(btrim(m.country_of_origin),''), c.origin),
composition = COALESCE(NULLIF(btrim(m.content),''), c.composition),
finish = COALESCE(NULLIF(btrim(m.finish),''), c.finish),
collection = COALESCE(NULLIF(btrim(m.collection),''), c.collection),
unit_of_measure = COALESCE(NULLIF(btrim(m.unit_of_measure),''), c.unit_of_measure),
application = COALESCE(NULLIF(btrim(m.use),''), c.application),
roll_length = COALESCE(NULLIF(btrim(m.wallcover_length_yd),''), c.roll_length),
price_trade = COALESCE(m.whls_price, c.price_trade),
price_retail = COALESCE(m.map, c.price_retail),
price_trade_new = COALESCE(m.new_wholesale_price, c.price_trade_new),
price_effective_date = COALESCE(NULLIF(btrim(m.new_price_effective_date),''), c.price_effective_date),
tariff_pct = COALESCE(m.tariff_pct, c.tariff_pct),
cost_price = COALESCE(m.cost_computed, c.cost_price),
sheet_display_status = m.display_status,
sheet_inventory_status = m.inventory_available,
sheet_last_synced_at = now()
FROM matched m
WHERE c.id = m.cat_id
RETURNING c.id
)
INSERT INTO _cat_sync_counter SELECT COUNT(*) FROM upd;
\echo === kravet_catalog rows updated with sheet fields ===
SELECT updated_count FROM _cat_sync_counter;
-- =========================================================================
-- STEP 3: Verification summary
-- =========================================================================
\echo === Sanity: sheet_fields vs catalog coverage ===
SELECT
(SELECT COUNT(*) FROM kravet_sheet_fields) AS sheet_fields_rows,
(SELECT COUNT(*) FROM kravet_catalog WHERE dw_sku LIKE 'DWKK-%') AS catalog_dwkk,
(SELECT COUNT(*) FROM kravet_catalog WHERE dw_sku LIKE 'DWKK-%'
AND sheet_last_synced_at IS NOT NULL) AS catalog_sheet_synced,
(SELECT COUNT(*) FROM kravet_catalog c
WHERE c.dw_sku LIKE 'DWKK-%' AND c.shopify_product_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM kravet_sheet_fields s
WHERE s.mfr_sku_norm = regexp_replace(
regexp_replace(upper(btrim(c.mfr_sku)),'-','.','g'),
'\.0$',''))
) AS live_catalog_without_sheet;
\echo === Field fill rates on kravet_sheet_fields (for Shopify metafield push planning) ===
SELECT
COUNT(*) FILTER (WHERE prop_65 IS NOT NULL AND prop_65 <> '') AS has_prop65,
COUNT(*) FILTER (WHERE prop_65_chemical IS NOT NULL AND prop_65_chemical <> '')
AS has_prop65_chemical,
COUNT(*) FILTER (WHERE ca_tb117 IS NOT NULL AND ca_tb117 <> '') AS has_ca_tb117,
COUNT(*) FILTER (WHERE ab_1817 IS NOT NULL AND ab_1817 <> '') AS has_ab_1817,
COUNT(*) FILTER (WHERE ufac IS NOT NULL AND ufac <> '') AS has_ufac,
COUNT(*) FILTER (WHERE direction IS NOT NULL AND direction <> '') AS has_direction,
COUNT(*) FILTER (WHERE memo_sample_available IS NOT NULL
AND memo_sample_available <> '') AS has_memo_sample,
COUNT(*) FILTER (WHERE minimum_order_qty IS NOT NULL
AND minimum_order_qty <> '') AS has_min_order,
COUNT(*) FILTER (WHERE horizontal_half_drop IS NOT NULL
AND horizontal_half_drop <> '') AS has_half_drop,
COUNT(*) FILTER (WHERE barcode IS NOT NULL AND barcode <> '') AS has_barcode,
COUNT(*) FILTER (WHERE clean_code IS NOT NULL AND clean_code <> '') AS has_clean_code,
COUNT(*) FILTER (WHERE durability IS NOT NULL AND durability <> '') AS has_durability,
COUNT(*) FILTER (WHERE weight IS NOT NULL AND weight <> '') AS has_weight,
COUNT(*) FILTER (WHERE lead_time_days IS NOT NULL AND lead_time_days <> '')
AS has_lead_time,
COUNT(*) FILTER (WHERE ship_from IS NOT NULL AND ship_from <> '') AS has_ship_from,
COUNT(*) FILTER (WHERE qty_on_hand IS NOT NULL AND qty_on_hand <> '') AS has_qoh,
COUNT(*) FILTER (WHERE memo_sample_qty IS NOT NULL AND memo_sample_qty <> '')
AS has_memo_qoh,
COUNT(*) FILTER (WHERE notes IS NOT NULL AND notes <> '') AS has_notes,
COUNT(*) FILTER (WHERE wallcovering_area IS NOT NULL
AND wallcovering_area <> '') AS has_wallcover_area
FROM kravet_sheet_fields;