`scrapers/promote-candidates.js`

135 lines
#!/usr/bin/env node
'use strict';
// Promote high-signal `sod_email_candidates` rows into real `sod_designers`
// (and `sod_firms` when the firm domain is obvious). Idempotent — running
// it twice doesn't double-create. Default signal_score floor: 0.6.
//
// Usage:
//   node scrapers/promote-candidates.js              # promote all >= 0.6
//   node scrapers/promote-candidates.js --min=0.8    # higher bar
//   node scrapers/promote-candidates.js --email=info@nickolsenstyle.com

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

const args = process.argv.slice(2).reduce((m, a) => {
  const [k, v] = a.replace(/^--/, '').split('=');
  m[k] = v ?? true; return m;
}, {});
const MIN_SCORE = parseFloat(args.min || '0.6');
const ONLY_EMAIL = args.email || null;

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

function slugify(s) {
  return String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 100);
}

function guessNameAndFirm(displayName, domain) {
  // "Nick Olsen Style" → name=null, firm="Nick Olsen Style"
  // "John Smith" → name="John Smith", firm=null
  // "John Smith — Smith Design" → name="John Smith", firm="Smith Design"
  if (!displayName) return { person: null, firm: null };
  const split = displayName.split(/[-—|·]/);
  if (split.length >= 2) {
    return { person: split[0].trim(), firm: split.slice(1).join(' ').trim() };
  }
  // Heuristic: if domain matches the display name (vs gmail/hotmail/etc), it's a firm domain
  const personalESPs = ['gmail.com','hotmail.com','yahoo.com','outlook.com','icloud.com','aol.com','me.com'];
  const domHasName = domain && !personalESPs.includes(domain) && displayName.toLowerCase().includes(domain.split('.')[0].slice(0, 4));
  if (domHasName || /\bdesign|\bstudio|\binteriors|\bgroup|\bassociates|\barchitect/i.test(displayName)) {
    return { person: null, firm: displayName };
  }
  return { person: displayName, firm: null };
}

async function main() {
  let params, where;
  if (ONLY_EMAIL) { params = [ONLY_EMAIL]; where = `email = $1`; }
  else {
    // INTENTIONALLY restrictive: only promote rows whose status is the
    // original 'new' lifecycle state. The sig-extract pipeline repurposes
    // `status` for an LLM triage label (is_designer / is_vendor /
    // is_noise), and the LLM is wrong often enough — Shopify/Twitter/TikTok
    // transactional senders, manufacturer reps, and our own outbound have
    // all been mis-labeled is_designer in past runs — that we don't trust
    // it as a promotion signal. To bulk-promote is_designer rows safely,
    // build a separate audit script (curated allow-list, not a block-list)
    // and run it with explicit --email targets.
    params = [MIN_SCORE];
    where = `status='new' AND signal_score >= $1`;
  }
  const r = await pool.query(
    `SELECT * FROM sod_email_candidates WHERE ${where} ORDER BY signal_score DESC NULLS LAST`,
    params
  );
  console.log(`[promote] ${r.rows.length} candidate(s) to promote (min=${MIN_SCORE})`);

  for (const c of r.rows) {
    const { person, firm } = guessNameAndFirm(c.display_name, c.domain);
    let personId = null;
    let firmId = null;

    if (firm) {
      const firmSlug = slugify(firm);
      const f = await pool.query(`
        INSERT INTO sod_firms (slug, name, website, primary_email)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (slug) DO UPDATE SET
          primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email)
        RETURNING id`, [firmSlug, firm, c.domain ? `https://${c.domain}` : null, c.email]);
      firmId = f.rows[0].id;
    }

    if (person) {
      const slug = slugify(person);
      const p = await pool.query(`
        INSERT INTO sod_designers (slug, full_name, primary_email)
        VALUES ($1, $2, $3)
        ON CONFLICT (slug) DO UPDATE SET
          primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email)
        RETURNING id`, [slug, person, c.email]);
      personId = p.rows[0].id;
      if (firmId) {
        await pool.query(`
          INSERT INTO sod_designer_firm (designer_id, firm_id, source)
          VALUES ($1, $2, 'gmail-candidate-promotion')
          ON CONFLICT DO NOTHING`, [personId, firmId]);
      }
    } else if (firm && !person) {
      // Firm-only candidate: also create a placeholder designer = firm name
      // so /designer/:slug works even before we know individual names.
      const slug = slugify(firm);
      const p = await pool.query(`
        INSERT INTO sod_designers (slug, full_name, primary_email)
        VALUES ($1, $2, $3)
        ON CONFLICT (slug) DO UPDATE SET
          primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email)
        RETURNING id`, [slug + '-firm', firm, c.email]);
      personId = p.rows[0].id;
      if (firmId) {
        await pool.query(`
          INSERT INTO sod_designer_firm (designer_id, firm_id, title, source)
          VALUES ($1, $2, 'Firm placeholder', 'gmail-candidate-promotion')
          ON CONFLICT DO NOTHING`, [personId, firmId]);
      }
    }

    await pool.query(`
      UPDATE sod_email_candidates
         SET status = $1::text, promoted_designer_id = $2::bigint, promoted_firm_id = $3::bigint, updated_at = NOW()
       WHERE id = $4::bigint`,
      [firm && !person ? 'is_firm' : 'is_designer', personId, firmId, c.id]);

    console.log(`  ${c.email} → designer=${personId} firm=${firmId} (${person || ''}${person && firm ? ' @ ' : ''}${firm || ''})`);
  }

  console.log(`[promote] done.`);
  await pool.end();
}

main().catch(e => { console.error('fatal:', e); process.exit(1); });