`scripts/load_pg.sh`

95 lines
#!/bin/zsh
# Load Sister Parish normalized data into dw_unified via local socket (peer auth).
set -euo pipefail
ROOT="$(cd "$(dirname "$0")/.." && pwd)"
cd "$ROOT"

psql dw_unified <<'SQL'
CREATE TABLE IF NOT EXISTS sisterparish_catalog (
  sp_product_id BIGINT PRIMARY KEY,
  handle TEXT NOT NULL,
  title TEXT NOT NULL,
  vendor TEXT NOT NULL DEFAULT 'Sister Parish',
  product_type TEXT,
  tags TEXT[],
  body_html TEXT,
  source_url TEXT,
  primary_image TEXT,
  images JSONB,
  variants JSONB,
  has_sample BOOLEAN,
  has_bolt BOOLEAN,
  has_image BOOLEAN,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sp_handle ON sisterparish_catalog(handle);
CREATE INDEX IF NOT EXISTS idx_sp_type   ON sisterparish_catalog(product_type);
SQL

# Stream normalized.json rows into a TEMP staging table, then upsert.
node -e '
const fs = require("fs");
const rows = JSON.parse(fs.readFileSync("output/normalized.json","utf8"));
process.stdout.write("BEGIN;\n");
process.stdout.write("CREATE TEMP TABLE sp_stage (j JSONB);\n");
for (const r of rows) {
  const esc = JSON.stringify(r).replace(/\x27/g,"\x27\x27");
  process.stdout.write(`INSERT INTO sp_stage(j) VALUES (\x27${esc}\x27::jsonb);\n`);
}
process.stdout.write(`
INSERT INTO sisterparish_catalog
  (sp_product_id, handle, title, vendor, product_type, tags, body_html, source_url,
   primary_image, images, variants, has_sample, has_bolt, has_image)
SELECT
  (j->>\x27sp_product_id\x27)::bigint,
  j->>\x27handle\x27,
  j->>\x27title\x27,
  COALESCE(j->>\x27vendor\x27, \x27Sister Parish\x27),
  j->>\x27product_type\x27,
  ARRAY(SELECT jsonb_array_elements_text(j->\x27tags\x27)),
  j->>\x27body_html\x27,
  j->>\x27url\x27,
  j->>\x27primary_image\x27,
  j->\x27images\x27,
  j->\x27variants\x27,
  (j->>\x27has_sample\x27)::bool,
  (j->>\x27has_bolt\x27)::bool,
  (j->>\x27has_image\x27)::bool
FROM sp_stage
ON CONFLICT (sp_product_id) DO UPDATE SET
  title=EXCLUDED.title,
  tags=EXCLUDED.tags,
  body_html=EXCLUDED.body_html,
  primary_image=EXCLUDED.primary_image,
  images=EXCLUDED.images,
  variants=EXCLUDED.variants,
  has_sample=EXCLUDED.has_sample,
  has_bolt=EXCLUDED.has_bolt,
  has_image=EXCLUDED.has_image,
  updated_at=NOW();
COMMIT;
\\echo
\\echo === counts ===
SELECT COUNT(*) AS row_count FROM sisterparish_catalog;
\\echo
\\echo === price tier (vendor retail, first variant) ===
SELECT
  CASE
    WHEN (variants->0->>\x27vendor_retail\x27)::numeric < 100 THEN \x27A: <\$100\x27
    WHEN (variants->0->>\x27vendor_retail\x27)::numeric < 300 THEN \x27B: \$100-\$299\x27
    WHEN (variants->0->>\x27vendor_retail\x27)::numeric < 500 THEN \x27C: \$300-\$499\x27
    WHEN (variants->0->>\x27vendor_retail\x27)::numeric < 700 THEN \x27D: \$500-\$699\x27
    ELSE \x27E: \$700+\x27
  END AS tier,
  COUNT(*) AS n
FROM sisterparish_catalog GROUP BY 1 ORDER BY 1;
\\echo
\\echo === DW markup spot-check (5 random) ===
SELECT title,
       (variants->0->>\x27vendor_retail\x27)::numeric AS vendor_retail,
       (variants->0->>\x27dw_retail\x27)::numeric AS dw_retail
FROM sisterparish_catalog ORDER BY random() LIMIT 5;
`);
' | psql dw_unified