`scrapers/extract-links-from-signatures.js`

106 lines
#!/usr/bin/env node
'use strict';
// Free cheap pass — regex-scan every sod_email_candidates.last_signature for
// URL patterns and store them in sod_links. The LLM in extract-from-signatures
// often misses URLs (it grabs the obvious linkedin one and skips the firm
// site, or vice versa), so this catches everything that's actually in the
// text. Cheap, deterministic, idempotent.
//
// Patterns captured:
//   linkedin.com/in/<slug>          → kind='linkedin'    (personal)
//   linkedin.com/company/<slug>     → kind='linkedin'    (firm)
//   instagram.com/<slug>            → kind='instagram'
//   <something>.com/.net/.studio    → kind='firm-site'   (if matches the
//                                                          candidate's domain)
//   houzz.com/...                   → kind='houzz'
//   pinterest.com/...               → kind='pinterest'
//
// Linked to sod_designers if a row exists matching primary_email; OR to
// sod_firms via the most-common firm join for that designer. Falls back to
// "orphan" rows (designer_id=NULL, firm_id=NULL) — those get adopted later
// when the designer's profile gets materialized.

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

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

const URL_RE = /https?:\/\/[^\s)\]"'>]+/gi;

function classifyUrl(raw, candidateDomain) {
  // Strip trailing punctuation that sneaks in from sig text
  let u = raw.replace(/[.,;:!?\)\]>'"]+$/, '');
  try { u = new URL(u).toString(); } catch { return null; }
  const lower = u.toLowerCase();
  if (lower.includes('linkedin.com/in/'))         return { url: u, kind: 'linkedin' };
  if (lower.includes('linkedin.com/company/'))    return { url: u, kind: 'linkedin' };
  if (lower.includes('instagram.com/'))           return { url: u, kind: 'instagram' };
  if (lower.includes('houzz.com/'))               return { url: u, kind: 'houzz' };
  if (lower.includes('pinterest.com/'))           return { url: u, kind: 'pinterest' };
  if (lower.includes('facebook.com/'))            return { url: u, kind: 'facebook' };
  if (lower.includes('twitter.com/') || lower.includes('x.com/')) return { url: u, kind: 'twitter' };
  // Firm-site heuristic: if the URL's host matches (or is a subdomain of) the
  // candidate's email domain, count it as the firm site.
  if (candidateDomain) {
    try {
      const host = new URL(u).hostname.replace(/^www\./, '');
      if (host === candidateDomain || host.endsWith('.' + candidateDomain)) {
        return { url: u, kind: 'firm-site' };
      }
    } catch {}
  }
  // Skip generic links (mailing services, calendar, image hosts, etc.) — too
  // noisy to surface on a designer profile without curation.
  return null;
}

async function main() {
  const r = await pool.query(`
    SELECT c.id, c.email, c.domain, c.last_signature, c.notes,
           d.id AS designer_id
      FROM sod_email_candidates c
      LEFT JOIN sod_designers d ON d.primary_email = c.email
     WHERE c.status = 'is_designer'
       AND c.last_signature IS NOT NULL
       AND length(c.last_signature) >= 50`);
  console.log(`[extract-links] scanning ${r.rows.length} signatures for URLs`);

  let inserted = 0, dupes = 0, classified = {};
  for (const row of r.rows) {
    const urls = row.last_signature.match(URL_RE);
    if (!urls) continue;
    for (const raw of new Set(urls)) {
      const c = classifyUrl(raw, row.domain);
      if (!c) continue;
      // Find firm_id via the designer's current firm-join, if any
      let firmId = null;
      if (row.designer_id) {
        const fq = await pool.query(
          `SELECT firm_id FROM sod_designer_firm WHERE designer_id=$1 AND is_current=true LIMIT 1`,
          [row.designer_id]
        );
        firmId = fq.rows[0]?.firm_id || null;
      }
      const ins = await pool.query(`
        INSERT INTO sod_links (designer_id, firm_id, url, kind, added_by)
        VALUES ($1, $2, $3, $4, 'sig-regex-pass')
        ON CONFLICT DO NOTHING
        RETURNING id`,
        [row.designer_id, firmId, c.url, c.kind]
      );
      if (ins.rowCount > 0) {
        inserted++;
        classified[c.kind] = (classified[c.kind] || 0) + 1;
      } else {
        dupes++;
      }
    }
  }
  console.log(`[extract-links] inserted=${inserted} dupes=${dupes} by-kind=${JSON.stringify(classified)}`);
  await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });