`build.sql`

95 lines
\set ON_ERROR_STOP on
BEGIN;

-- staging: column ORDER must match sync.mjs CSV column order (position-matched by \copy)
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)

-- guard: refuse to proceed on a truncated/empty pull (protects the live tables)
DO $$
DECLARE n int; BEGIN
  SELECT count(*) INTO n FROM fm_wallpaper_stage;
  IF n < 250000 THEN RAISE EXCEPTION 'stage row count % below safety floor 250000 - aborting', n; END IF;
END $$;

-- ===== canonical current mirror: one row per normalized combo sku, most-populated wins =====
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),'')                        AS series,
  nullif(js_pattern,'')                           AS js_pattern,
  nullif(split_part(mfr_pattern,' -- ',1),'')     AS mfr_pattern,
  nullif(supplier,'')                             AS supplier,
  nullif(jpg_name,'')                             AS jpg_name,
  nullif(width,'') AS fm_width, nullif(border,'') AS fm_border, nullif(rpt,'') AS fm_repeat,
  nullif(five_ten,'') AS five_ten, nullif(account,'') AS fm_account, nullif(line,'') AS fm_line,
  nullif(minimum,'') AS fm_minimum, nullif(date_line,'') AS date_line,
  nullif(retail,'') AS retail, nullif(cost,'') AS cost, nullif(net,'') AS net,
  record_id, now() AS refreshed_at
FROM (SELECT *, upper(regexp_replace(combo_sku,'[^A-Za-z0-9]','','g')) AS 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,
  (nullif(jpg_name,'')    IS NOT NULL)::int DESC;

DROP TABLE IF EXISTS fm_wallpaper_live;
ALTER TABLE fm_wallpaper_live_new RENAME TO fm_wallpaper_live;
CREATE INDEX fm_wallpaper_live_combo_idx ON fm_wallpaper_live(combo_sku);

-- ===== safe fill-only merge into fmpro (NEVER nulls vendor-join cols phone/email/vendor_code/account) =====
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);

-- ===== same into master_fmpro (id is serial+PK, omit it) =====
UPDATE master_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 master_fmpro (sku, mfr_sku, sku_prefix, image_filename, vendor_name, added_date)
SELECT 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 master_fmpro f WHERE upper(f.sku) = l.combo_sku);

-- ===== queue this run's NEW skus for Steve's approval (canary) — pending; never re-queues a decided sku =====
INSERT INTO fmpro_wallpaper_review (sku, review_status, batch_date, vendor_name, mfr_sku, sku_prefix, image_filename)
SELECT DISTINCT ON (sku) sku, 'pending', CURRENT_DATE,
       nullif(vendor_name,''), nullif(mfr_sku,''), nullif(sku_prefix,''), nullif(image_filename,'')
FROM master_fmpro
WHERE added_date::date = CURRENT_DATE AND nullif(sku,'') IS NOT NULL
ORDER BY sku, (nullif(vendor_name,'') IS NOT NULL) DESC
ON CONFLICT (sku) DO NOTHING;

-- backfill thumbnails/titles for still-pending review rows from the live Shopify catalog
UPDATE fmpro_wallpaper_review r SET image_url=s.image_url, title=s.title
FROM (
  SELECT substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+') core,
         max(image_url) image_url, min(title) title
  FROM shopify_products WHERE status='ACTIVE' AND coalesce(variant_sku,sku) IS NOT NULL GROUP BY 1
) s
WHERE r.review_status='pending' AND r.image_url IS NULL AND upper(r.sku)=s.core;

SELECT
  (SELECT count(*) FROM fm_wallpaper_live) AS live_distinct_combosku,
  (SELECT count(*) FROM fmpro)             AS fmpro_rows,
  (SELECT count(*) FROM master_fmpro)      AS master_fmpro_rows;

COMMIT;