`scrapers/seed-top-firms.js`

88 lines
#!/usr/bin/env node
'use strict';
// Seed the top US architecture + interior design firms as sod_firms anchors
// for the StarsOfDesign directory. Deterministic — no scraping. Source data
// lives in data/top-us-firms.json (public facts: name, city, founded year,
// website, public LinkedIn company page slug). Logos stay made-with-ai
// placeholders until the firm claims its profile.
//
// Idempotent. Re-running updates existing rows but never duplicates.

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

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);
}

async function upsertFirm(f, kind) {
  const slug = slugify(f.name);
  const r = await pool.query(`
    INSERT INTO sod_firms
      (slug, name, city, state_or_region, founded_year, size_band, website, bio, styles, logo_source)
    VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,'made-with-ai')
    ON CONFLICT (slug) DO UPDATE SET
      name            = EXCLUDED.name,
      city            = COALESCE(sod_firms.city, EXCLUDED.city),
      state_or_region = COALESCE(sod_firms.state_or_region, EXCLUDED.state_or_region),
      founded_year    = COALESCE(sod_firms.founded_year, EXCLUDED.founded_year),
      size_band       = COALESCE(sod_firms.size_band, EXCLUDED.size_band),
      website         = COALESCE(sod_firms.website, EXCLUDED.website),
      styles          = COALESCE(sod_firms.styles, EXCLUDED.styles),
      updated_at      = NOW()
    RETURNING id, (xmax = 0) AS inserted`,
    [slug, f.name, f.city, f.state, f.founded, f.size_band, f.website,
     `Top US ${kind} firm. ${f.styles ? 'Known for ' + f.styles.join(' / ') + '.' : ''}`.trim(),
     f.styles || []]);
  const firmId = r.rows[0].id;
  const inserted = r.rows[0].inserted;
  if (f.linkedin_company) {
    await pool.query(`
      INSERT INTO sod_links (firm_id, url, kind, added_by)
      VALUES ($1, $2, 'linkedin', 'seed-top-firms')
      ON CONFLICT DO NOTHING`,
      [firmId, `https://www.linkedin.com/company/${f.linkedin_company}/`]);
  }
  if (f.website) {
    await pool.query(`
      INSERT INTO sod_links (firm_id, url, kind, added_by)
      VALUES ($1, $2, 'firm-site', 'seed-top-firms')
      ON CONFLICT DO NOTHING`,
      [firmId, f.website]);
  }
  return inserted;
}

async function main() {
  const ir = await pool.query(
    `INSERT INTO sod_ingest_runs (run_kind, source, status) VALUES ('seed-top-firms', 'data/top-us-firms.json', 'running') RETURNING id`
  );
  const runId = ir.rows[0].id;
  const data = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'top-us-firms.json'), 'utf8'));
  let archCreated = 0, archUpdated = 0;
  for (const f of data.architecture_firms) {
    if (await upsertFirm(f, 'architecture')) archCreated++; else archUpdated++;
  }
  let intCreated = 0, intUpdated = 0;
  for (const f of data.interior_design_firms) {
    if (await upsertFirm(f, 'interior design')) intCreated++; else intUpdated++;
  }
  await pool.query(
    `UPDATE sod_ingest_runs SET status='ok', finished_at=NOW(), records_in=$1, records_out=$2, notes=$3 WHERE id=$4`,
    [data.architecture_firms.length + data.interior_design_firms.length,
     archCreated + intCreated,
     `arch:+${archCreated}/~${archUpdated}  interior:+${intCreated}/~${intUpdated}`,
     runId]
  );
  console.log(`[seed-top-firms] arch:+${archCreated}/~${archUpdated}  interior:+${intCreated}/~${intUpdated}`);
  await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });