`scripts/load_pg.js`

82 lines
#!/usr/bin/env node
require('dotenv').config({ path: require('path').join(__dirname,'..','.env') });
const fs = require('fs');
const path = require('path');
const { Client } = require('pg');

const data = JSON.parse(fs.readFileSync(path.join(__dirname,'..','output','normalized.json'),'utf8'));

(async () => {
  const c = new Client({ connectionString: process.env.DATABASE_URL });
  await c.connect();

  await c.query(`
    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);
  `);

  let inserted = 0, updated = 0;
  for (const p of data) {
    const r = await c.query(
      `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)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
       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()
       RETURNING (xmax = 0) AS inserted`,
      [p.sp_product_id, p.handle, p.title, p.vendor, p.product_type, p.tags, p.body_html, p.url, p.primary_image, JSON.stringify(p.images), JSON.stringify(p.variants), p.has_sample, p.has_bolt, p.has_image]
    );
    if (r.rows[0].inserted) inserted++; else updated++;
  }

  console.log(`Loaded into dw_unified.sisterparish_catalog: ${inserted} inserted, ${updated} updated`);
  const cnt = await c.query('SELECT COUNT(*) FROM sisterparish_catalog');
  console.log(`Total rows: ${cnt.rows[0].count}`);

  // Summary by price tier
  const tiers = await c.query(`
    SELECT
      CASE
        WHEN (variants->0->>'vendor_retail')::numeric < 100 THEN 'A: <$100'
        WHEN (variants->0->>'vendor_retail')::numeric < 300 THEN 'B: $100-$299'
        WHEN (variants->0->>'vendor_retail')::numeric < 500 THEN 'C: $300-$499'
        WHEN (variants->0->>'vendor_retail')::numeric < 700 THEN 'D: $500-$699'
        ELSE 'E: $700+'
      END AS tier,
      COUNT(*) AS n
    FROM sisterparish_catalog
    GROUP BY 1 ORDER BY 1;
  `);
  console.log('\nPrice tiers (vendor retail, first variant):');
  for (const row of tiers.rows) console.log(`  ${row.tier}: ${row.n}`);

  await c.end();
})().catch(e=>{ console.error(e); process.exit(1); });