`scrapers/apply-vendor-blocklist.js`

80 lines
#!/usr/bin/env node
'use strict';
// Tick 2 correction — Apply the vendor blocklist to existing sod_email_candidates
// rows that previously got auto-promoted to status='is_designer'/'is_firm' but
// are actually vendors. Steve's standing rule: StarsOfDesign surfaces DW CLIENTS
// (designers/firms who BUY from DW), NOT vendors (manufacturers we buy from).
//
// Run modes:
//   node scrapers/apply-vendor-blocklist.js           # apply
//   node scrapers/apply-vendor-blocklist.js --dry     # report only

require('dotenv').config();
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

const DRY = process.argv.includes('--dry');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
  max: 4,
});

const blocklist = JSON.parse(fs.readFileSync(path.join(__dirname, 'vendor-blocklist.json'), 'utf8'));
const VENDOR_DOMAINS = new Set(blocklist.vendor_domains.map(d => d.toLowerCase()));
const DEAD_DOMAINS   = new Set(blocklist.dead_domains.map(d => d.toLowerCase()));

async function main() {
  const r = await pool.query(`
    SELECT id, email, domain, status, signal_score
      FROM sod_email_candidates
     WHERE status IN ('new','is_designer','is_firm')`);
  let toVendor = 0, toDead = 0;
  const previews = [];
  for (const c of r.rows) {
    const dom = (c.domain || c.email.split('@')[1] || '').toLowerCase();
    if (DEAD_DOMAINS.has(dom)) {
      previews.push(`  DEAD   ${c.email}`);
      if (!DRY) await pool.query(
        `UPDATE sod_email_candidates
            SET status='is_noise',
                signal_score=0,
                notes=CONCAT_WS(' || ', NULLIF(notes,''), 'blocklist:dead-domain'::text),
                updated_at=NOW()
          WHERE id=$1`, [c.id]);
      toDead++;
    } else if (VENDOR_DOMAINS.has(dom)) {
      previews.push(`  VENDOR ${c.email}`);
      if (!DRY) await pool.query(
        `UPDATE sod_email_candidates
            SET status='is_vendor',
                notes=CONCAT_WS(' || ', NULLIF(notes,''), 'blocklist:vendor-domain'::text),
                updated_at=NOW()
          WHERE id=$1`, [c.id]);
      toVendor++;
    }
  }
  // Also kill any sod_designers/sod_firms rows whose primary_email is on the blocklist
  let killedDesigners = 0, killedFirms = 0;
  if (!DRY) {
    const matchClause = [...VENDOR_DOMAINS, ...DEAD_DOMAINS].map(d => `'${d}'`).join(',');
    const kd = await pool.query(`
      DELETE FROM sod_designers
       WHERE primary_email IS NOT NULL
         AND split_part(primary_email, '@', 2) IN (${matchClause})
       RETURNING id`);
    killedDesigners = kd.rowCount;
    const kf = await pool.query(`
      DELETE FROM sod_firms
       WHERE primary_email IS NOT NULL
         AND split_part(primary_email, '@', 2) IN (${matchClause})
       RETURNING id`);
    killedFirms = kf.rowCount;
  }

  console.log(previews.slice(0, 40).join('\n') + (previews.length > 40 ? `\n  …+${previews.length - 40} more` : ''));
  console.log(`[blocklist] candidates: vendor=${toVendor} dead=${toDead}  removed: designers=${killedDesigners} firms=${killedFirms}  dry=${DRY}`);
  await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });