`scraper-utils.js`

64 lines
// ============================================================================
// Minimal Mac2-local scraper utils for the Wolf Gordon crawl.
// Self-contained (only `pg`, no browser) — scrape-wolfgordon.js is pure HTTP.
// Targets the LOCAL dw_unified over the unix socket (peer auth as the OS user),
// the same DB the existing 5,377 wolf_gordon_catalog rows live in.
// Prod (Kamatera) sync is a separate, Steve-gated step — NOT this script.
// ============================================================================

const { Pool } = require('pg');

// Unix-socket / peer auth — mirrors `psql -d dw_unified` working on Mac2.
// Override with DATABASE_URL if ever needed.
const pool = process.env.DATABASE_URL
  ? new Pool({ connectionString: process.env.DATABASE_URL, max: 3 })
  : new Pool({ host: '/tmp', database: 'dw_unified', max: 3 });

async function upsertProduct(table, product) {
  if (!product.mfr_sku) return null;

  const fields = Object.keys(product).filter(k => product[k] !== undefined && product[k] !== null);
  const values = fields.map(k => product[k]);
  const placeholders = fields.map((_, i) => `$${i + 1}`);
  const updates = fields.filter(f => f !== 'mfr_sku').map(f => `${f} = EXCLUDED.${f}`);

  const sql = `
    INSERT INTO ${table} (${fields.join(', ')})
    VALUES (${placeholders.join(', ')})
    ON CONFLICT (mfr_sku) DO UPDATE SET
      ${updates.join(', ')},
      updated_at = NOW(),
      last_scraped = NOW()
    RETURNING id
  `;

  try {
    const res = await pool.query(sql, values);
    return res.rows[0]?.id;
  } catch (err) {
    if (err.message.includes('duplicate key')) return null;
    console.error(`  DB error for SKU ${product.mfr_sku}: ${err.message}`);
    return null;
  }
}

function wait(base = 300, jitter = 150) {
  const ms = base + Math.floor(Math.random() * jitter);
  return new Promise(r => setTimeout(r, ms));
}

async function getCatalogCount(table) {
  try {
    const res = await pool.query(`SELECT COUNT(*)::int AS c FROM ${table}`);
    return res.rows[0].c;
  } catch {
    return 0;
  }
}

async function closePool() {
  await pool.end();
}

module.exports = { pool, upsertProduct, wait, getCatalogCount, closePool };