`build_dryrun.sql`

53 lines
\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE fm_wallpaper_stage (
  combo_sku text, series text, js_pattern text, mfr_pattern text, supplier text,
  jpg_name text, width text, border text, rpt text, five_ten text, account text,
  line text, minimum text, date_line text, page text, retail text, cost text, net text,
  record_id text
) ON COMMIT DROP;
\copy fm_wallpaper_stage FROM '__CSV__' WITH (FORMAT csv, HEADER true)
DO $$ DECLARE n int; BEGIN SELECT count(*) INTO n FROM fm_wallpaper_stage;
  IF n < 250000 THEN RAISE EXCEPTION 'stage row count % below floor', n; END IF; END $$;

CREATE TEMP TABLE _pre AS SELECT
  (SELECT count(*) FROM fmpro) rows,
  (SELECT count(*) FILTER (WHERE nullif(phone,'') IS NOT NULL) FROM fmpro) phone,
  (SELECT count(*) FILTER (WHERE nullif(email_1,'') IS NOT NULL) FROM fmpro) email,
  (SELECT count(*) FILTER (WHERE nullif(vendor_code,'') IS NOT NULL) FROM fmpro) vcode,
  (SELECT count(*) FILTER (WHERE nullif(account_num,'') IS NOT NULL) FROM fmpro) acct;

DROP TABLE IF EXISTS fm_wallpaper_live_new;
CREATE TABLE fm_wallpaper_live_new AS
SELECT DISTINCT ON (norm) norm AS combo_sku,
  nullif(upper(series),'') series, nullif(js_pattern,'') js_pattern,
  nullif(split_part(mfr_pattern,' -- ',1),'') mfr_pattern, nullif(supplier,'') supplier,
  nullif(jpg_name,'') jpg_name, record_id, now() refreshed_at
FROM (SELECT *, upper(regexp_replace(combo_sku,'[^A-Za-z0-9]','','g')) norm FROM fm_wallpaper_stage) s
WHERE norm ~ '^[A-Z]+[0-9]+'
ORDER BY norm, (nullif(mfr_pattern,'') IS NOT NULL)::int DESC, (nullif(supplier,'') IS NOT NULL)::int DESC;
DROP TABLE IF EXISTS fm_wallpaper_live;
ALTER TABLE fm_wallpaper_live_new RENAME TO fm_wallpaper_live;

UPDATE fmpro f SET mfr_sku=COALESCE(NULLIF(f.mfr_sku,''),l.mfr_pattern),
  sku_prefix=COALESCE(NULLIF(f.sku_prefix,''),l.series),
  image_filename=COALESCE(NULLIF(f.image_filename,''),l.jpg_name)
FROM fm_wallpaper_live l WHERE upper(f.sku)=l.combo_sku;
INSERT INTO fmpro (id, sku, mfr_sku, sku_prefix, image_filename, vendor_name, added_date)
SELECT (SELECT COALESCE(max(id),0) FROM fmpro)+row_number() OVER (),
  l.combo_sku,l.mfr_pattern,l.series,l.jpg_name,l.supplier,CURRENT_DATE
FROM fm_wallpaper_live l WHERE NOT EXISTS (SELECT 1 FROM fmpro f WHERE upper(f.sku)=l.combo_sku);

\echo '=== PRE (fmpro) ==='
SELECT * FROM _pre;
\echo '=== POST (fmpro) — phone/email/vcode/acct must NOT drop ==='
SELECT (SELECT count(*) FROM fmpro) rows,
  count(*) FILTER (WHERE nullif(phone,'') IS NOT NULL) phone,
  count(*) FILTER (WHERE nullif(email_1,'') IS NOT NULL) email,
  count(*) FILTER (WHERE nullif(vendor_code,'') IS NOT NULL) vcode,
  count(*) FILTER (WHERE nullif(account_num,'') IS NOT NULL) acct FROM fmpro;
\echo '=== deltas ==='
SELECT (SELECT count(*) FROM fmpro)-(SELECT rows FROM _pre) AS fmpro_new_rows,
       (SELECT count(*) FROM fm_wallpaper_live) AS live_distinct;
ROLLBACK;