`scripts/populate-catalog.js`

81 lines
#!/usr/bin/env node
/**
 * Stage-2 one-shot: populate dw_unified.microsite_products for 1960swallpaper.
 * Pulls genuine 1960s-era wallcoverings from the DW master catalog
 * (shopify_products) and inserts them so the site's niche filter has stock.
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require(path.join(__dirname, '..', '..', '_shared', 'node_modules', 'pg'));
const { classifyAesthetic } = require(path.join(__dirname, '..', '..', '_shared', 'microsite-aesthetic.js'));

const siteCfg = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'site.config.json'), 'utf8'));
const SITE_SLUG = siteCfg.slug;
const RAILS = Array.isArray(siteCfg.rails) ? siteCfg.rails : [];
const CAP = 150;

const pool = new Pool({ host: process.env.PGHOST || '/tmp', database: 'dw_unified' });

(async () => {
  // Strongest 1960s signal first (rank 0), mid-century modern last (rank 1),
  // so a 150-cap leans toward the most on-theme stock.
  const { rows } = await pool.query(`
    SELECT DISTINCT ON (coalesce(sku, handle))
           sku, handle, title, vendor, product_type, image_url, tags,
           retail_price, price,
           CASE WHEN title ~* '1960|\\mpop art\\M|\\mop art\\M|psychedelic|space.?age|\\matomic\\M'
                 OR tags ~* '1960|pop art|op art|psychedelic|space.?age|atomic'
                THEN 0 ELSE 1 END AS rank
      FROM shopify_products
     WHERE status = 'ACTIVE' AND image_url IS NOT NULL AND image_url <> ''
       AND product_type ILIKE '%Wallcovering%'
       AND title !~* 'lamp|rug|pillow|throw|tripod|frame|mirror|vase|candle|sculpture|figurine'
       AND (title ~* '1960|\\mpop art\\M|\\mop art\\M|psychedelic|space.?age|\\matomic\\M'
            OR tags ~* '1960|pop art|op art|psychedelic|space.?age|atomic|mid.century modern')
       AND lower(title || ' ' || coalesce(tags,'')) ~ 'mod|op art|op-art|pop|geometric|psychedelic|1960'
       AND lower(title || ' ' || coalesce(tags,'')) !~ 'solid|blank|plain'
     ORDER BY coalesce(sku, handle)
  `);

  // Re-sort by rank (strongest signal first), stable tiebreak on sku/handle,
  // then trim to cap — deterministic so re-runs pick the same 150.
  rows.sort((a, b) => a.rank - b.rank
    || String(a.sku || a.handle).localeCompare(String(b.sku || b.handle)));
  const picked = rows.slice(0, CAP);

  // Idempotent: drop any prior catalog-sourced rows for this site so a re-run
  // with a changed query/cap doesn't leave stale extras behind.
  await pool.query(
    "DELETE FROM microsite_products WHERE site_slug=$1 AND source='catalog'",
    [SITE_SLUG]);

  let inserted = 0;
  for (let i = 0; i < picked.length; i++) {
    const p = picked[i];
    const tags = typeof p.tags === 'string'
      ? p.tags.split(',').map(t => t.trim()).filter(Boolean)
      : (Array.isArray(p.tags) ? p.tags : []);
    const aesthetic = classifyAesthetic(tags, RAILS);
    const skuKey = p.sku || p.handle;
    await pool.query(`
      INSERT INTO microsite_products
        (site_slug, sku, source, title, vendor, product_type, handle,
         image_url, tags, aesthetic, max_price, product_url, sort_order)
      VALUES ($1,$2,'catalog',$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
      ON CONFLICT (site_slug, sku) DO UPDATE SET
        title=EXCLUDED.title, vendor=EXCLUDED.vendor, product_type=EXCLUDED.product_type,
        handle=EXCLUDED.handle, image_url=EXCLUDED.image_url, tags=EXCLUDED.tags,
        aesthetic=EXCLUDED.aesthetic, max_price=EXCLUDED.max_price,
        product_url=EXCLUDED.product_url, visible=true, updated_at=now()
    `, [SITE_SLUG, skuKey, p.title, p.vendor, p.product_type, p.handle,
        p.image_url, tags, aesthetic, p.retail_price || p.price || null,
        p.handle ? `https://designerwallcoverings.com/products/${p.handle}` : null, i]);
    inserted++;
  }

  const { rows: cnt } = await pool.query(
    'SELECT count(*) FROM microsite_products WHERE site_slug=$1', [SITE_SLUG]);
  console.log(`candidates=${rows.length} picked=${picked.length} inserted=${inserted} total_now=${cnt[0].count}`);
  await pool.end();
})().catch(e => { console.error(e); process.exit(1); });