`scripts/kravet-master-2026/load.sh`

41 lines
#!/usr/bin/env bash
# Load the authoritative Jan-2026 Kravet price list into dw_unified (Kamatera canonical)
# as kravet_master_price_2026, keyed by Item SKU. Additive reference table, re-runnable.
# Source: GDrive "Kravet Price list 1-20-26.xlsx" (8,191 rows; NEW MAP = NEW WHLS x 1.5 verified).
# Build the CSV first via build_csv.py, then run this. Read-only w.r.t. the storefront.
set -euo pipefail
CSV="${1:-/tmp/kravet_master_2026.csv}"
REMOTE_CSV="/tmp/kravet_master_2026.csv"
SSH="my-server"
PSQL="psql -U dw_admin -d dw_unified -h 127.0.0.1"

[ -f "$CSV" ] || { echo "missing $CSV — run build_csv.py first"; exit 1; }
echo "scp $CSV -> $SSH:$REMOTE_CSV"
scp -q "$CSV" "$SSH:$REMOTE_CSV"

ssh "$SSH" "$PSQL -v ON_ERROR_STOP=1 <<'SQL'
DROP TABLE IF EXISTS kravet_master_price_2026;
CREATE TABLE kravet_master_price_2026 (
  item            text PRIMARY KEY,
  brand           text,
  cur_whls        numeric,
  cur_map         numeric,
  uom             text,
  collection      text,
  use_category    text,
  new_whls        numeric,
  new_map         numeric,
  effective_date  date,
  tariff_pct      numeric,
  loaded_at       timestamptz DEFAULT now(),
  source_file     text DEFAULT 'Kravet Price list 1-20-26.xlsx'
);
\copy kravet_master_price_2026 (item,brand,cur_whls,cur_map,uom,collection,use_category,new_whls,new_map,effective_date,tariff_pct) FROM '$REMOTE_CSV' WITH (FORMAT csv, HEADER true)
CREATE INDEX idx_kmp26_item_upper ON kravet_master_price_2026 (upper(item));
CREATE INDEX idx_kmp26_brand      ON kravet_master_price_2026 (upper(brand));
CREATE INDEX idx_kmp26_uom        ON kravet_master_price_2026 (uom);
ANALYZE kravet_master_price_2026;
SQL"
echo "== verify =="
ssh "$SSH" "$PSQL -tA -F'|' -c \"select count(*) rows, count(distinct item) items, count(*) filter (where uom='ROLL') roll, count(*) filter (where new_map>0) has_newmap from kravet_master_price_2026;\""