`scripts/linkedin-openclaw.js`

115 lines
// Targeted LinkedIn via openclaw ($0 — real logged-in Chrome, NOT bot-blocked like curl).
// For the site-less entities that actually MATTER — the named crew of a real deal — drive a
// real-browser Google search "<name> <city> CA linkedin", read the direct linkedin.com/
// (company|in) hrefs off the rendered page, accept one only if a name token (>=4) matches the
// slug, and write it (linkedin_source='openclaw'). This is on-demand + per-entity, not a bulk
// grind — that's the point: you need LinkedIn for the crew on the deal in front of you, not
// all 145k licensees. We never request linkedin.com directly; Google renders the result links.
//
// Usage:
//   node scripts/linkedin-openclaw.js --apn=<APN>              # every crew member of a deal
//   node scripts/linkedin-openclaw.js --license=<CSLB#>        # one licensed entity
//   node scripts/linkedin-openclaw.js --name="ACME" --city="Los Angeles"   # ad-hoc (no write)
//   node scripts/linkedin-openclaw.js --top=<N>                # crews of the top-N current deals
import { execSync } from 'child_process';
import { q, pool } from '../src/db.js';
import { getCrew } from './permit-crew.js';

const kv = Object.fromEntries(process.argv.slice(2).filter(a => a.includes('=')).map(a => a.slice(2).split('=')));
const sleep = ms => new Promise(r => setTimeout(r, ms));
function oc(args) { return execSync(`openclaw browser ${args}`, { encoding: 'utf8', timeout: 45000, stdio: ['ignore', 'pipe', 'ignore'] }); }

let tab = process.env.OPENCLAW_TAB || null;
function ensureTab() { if (tab) return; const out = oc('open "about:blank" --timeout 20000'); tab = (out.match(/id:\s*([A-F0-9]+)/i) || [])[1]; }
function unwrap(s) {                                        // openclaw evaluate returns a JSON-encoded string
  s = (s || '').trim().split('\n').filter(Boolean).pop() || '[]';
  try { let v = JSON.parse(s); if (typeof v === 'string') v = JSON.parse(v); return Array.isArray(v) ? v : []; } catch { return []; }
}
const norm = u => u.split('#')[0].split('?')[0]                    // drop fragment (#:~:text=…) AND query
  .replace(/^https?:\/\/[a-z]{2,3}\.linkedin/i, 'https://www.linkedin').replace(/\/$/, '');
// generic words that must NEVER be the sole basis for a match (too common → false positives)
const GENERIC = new Set(('inc llc corp co ltd company the and of dba construction builders building build ' +
  'remodeling remodel design designs development dev group services service enterprises realty real estate ' +
  'properties property associates custom quality pacific coast best pro elite premier american america california ' +
  'socal west east north south general home homes new star royal first prime advanced modern classic superior ' +
  'professional solutions contracting contractor engineering engineers architects landscape landscaping electric ' +
  'plumbing roofing painting concrete masonry').split(' '));
function nameTokens(name) {
  const clean = String(name || '').toLowerCase().replace(/[^a-z0-9 ]/g, ' ').split(/\s+/).filter(Boolean);
  const big = clean.filter(t => t.length >= 3 && !GENERIC.has(t));  // distinctive tokens
  const acr = clean.filter(t => t.length === 1).join('');           // spaced initials "M D Q" -> "mdq"
  return { big, acr: acr.length >= 2 ? acr : null };
}
function slugMatches(name, url) {
  const path = url.replace(/^https?:\/\/[^/]+/i, '');             // slug from PATH only, never domain/fragment
  const slug = path.toLowerCase().replace(/[^a-z0-9]/g, '');
  const { big, acr } = nameTokens(name);
  if (big.some(t => slug.includes(t))) return true;                 // a distinctive word matches
  if (acr && slug.includes(acr)) return true;                       // the initials-acronym matches
  return false;
}
const EXTRACT_FN = `() => JSON.stringify([...new Set([...document.querySelectorAll('a')].map(a=>a.href).filter(h=>/linkedin\\.com\\/(company|in|pub|school)\\//i.test(h)))])`;
async function searchLinkedin(name, city) {
  const url = 'https://www.google.com/search?q=' + encodeURIComponent(`${name} ${city || ''} CA linkedin`);
  let t;
  try { t = (oc(`open ${JSON.stringify(url)} --timeout 30000`).match(/id:\s*([A-F0-9]+)/i) || [])[1]; } catch { return null; }
  if (!t) return null;
  let arr = [];
  try {
    for (let i = 0; i < 4 && !arr.length; i++) {                     // retry until result anchors render
      await sleep(1500);
      try { arr = unwrap(oc(`evaluate --target-id ${t} --fn ${JSON.stringify(EXTRACT_FN)}`)); } catch { arr = []; }
    }
  } finally { try { oc(`close --target-id ${t}`); } catch {} }       // don't leak tabs
  const cands = [...new Set(arr.map(norm))]
    .filter(u => !/\/pub\/dir\//i.test(u) && slugMatches(name, u));   // reject people-directory pages
  return cands.find(u => /\/company\//i.test(u)) || cands[0] || null; // prefer the company page
}
async function writeLic(lic, link) {
  await q(`UPDATE cslb_raw SET linkedin=$2, linkedin_source='openclaw', contacts_enriched_at=COALESCE(contacts_enriched_at,now()) WHERE "LicenseNo"=$1`, [lic, link]);
}
async function doEntity(row) {
  if (row.linkedin) { console.log(`  ✓ ${row.BusinessName.slice(0, 30).padEnd(30)} already has ${row.linkedin} (${row.linkedin_source || '?'})`); return 0; }
  const link = await searchLinkedin(row.BusinessName, row.City);
  if (link) { await writeLic(row.LicenseNo, link); console.log(`  → ${row.BusinessName.slice(0, 30).padEnd(30)} ${link}`); return 1; }
  console.log(`  · ${row.BusinessName.slice(0, 30).padEnd(30)} (no confident match)`); return 0;
}
async function rowByLic(lic) {
  return (await q(`SELECT "LicenseNo","BusinessName","City",linkedin,linkedin_source FROM cslb_raw WHERE "LicenseNo"=$1`, [lic])).rows[0];
}

async function main() {
  let found = 0;
  if (kv.name) {
    const link = await searchLinkedin(kv.name, kv.city || "Los Angeles"); if(link) found++;
    console.log(link ? `→ ${kv.name}: ${link}` : `· ${kv.name}: no confident match`);
  } else if (kv.license) {
    const r = await rowByLic(kv.license.replace(/[^0-9]/g, ''));
    if (!r) { console.error('no CSLB row for license ' + kv.license); process.exit(1); }
    found += await doEntity(r);
  } else if (kv.apn || kv.top) {
    const apns = kv.apn ? [kv.apn.replace(/[^0-9]/g, '')]
      : (await q(`SELECT DISTINCT ON (apn) apn FROM la_building_permits_raw
           WHERE dataset_id='pi9x-tg5x' AND status_desc='Issued' AND permit_type='Bldg-New'
             AND issue_date > now()-interval '120 days' AND apn IS NOT NULL AND valuation>=1000000
           ORDER BY apn, valuation DESC`)).rows.slice(0, Number(kv.top)).map(r => r.apn);
    for (const apn of apns) {
      console.log(`\n=== DEAL APN ${apn} — naming crew LinkedIn ===`);
      const crew = await getCrew(apn);                 // uses cached LADBS detail where available
      const seen = new Set();
      for (const c of crew.crew) {
        if (!c.lic || seen.has(c.lic)) continue; seen.add(c.lic);
        const r = await rowByLic(c.lic);
        if (r) found += await doEntity(r); else console.log(`  · ${c.contractor} (lic ${c.lic}) not in CSLB`);
        await sleep(1500);
      }
    }
  } else { console.error('usage: --apn=<APN> | --license=<#> | --name="X" --city="Y" | --top=<N>'); process.exit(1); }
  console.log(`\nDone — ${found} LinkedIn profile(s) found. $0 (openclaw real browser + local).`);
}
export { searchLinkedin, writeLic };
// run the CLI only when invoked directly (not when imported by property.js)
if (process.argv[1] && /linkedin-openclaw\.js$/.test(process.argv[1])) {
  main().catch(e => { console.error('linkedin-openclaw error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
}