`scripts/import_social_handles.cjs`

80 lines
/**
 * Import Instagram + TikTok handles from ~/Projects/_ventura-recon/social-results.json
 * into ventura_corridor.business_contacts.
 *
 * Matches by (name + zip) → businesses.id. Insert one contact row per platform.
 * Idempotent via the dedupe_key generated column on business_contacts.
 *
 * No LLM. Pure data import.
 */
const fs = require('fs');
const path = require('path');
const os = require('os');
const { Client } = require('pg');

const SRC = path.join(os.homedir(), 'Projects/_ventura-recon/social-results.json');
const DATABASE_URL =
  process.env.DATABASE_URL ||
  'postgresql:///ventura_corridor?host=/tmp';

(async () => {
  const rows = JSON.parse(fs.readFileSync(SRC, 'utf8'));
  console.log(`[social] ${rows.length} rows to consider`);

  const client = new Client({ connectionString: DATABASE_URL });
  await client.connect();

  let matched = 0, inserted = 0, skipped_nohandle = 0, skipped_nomatch = 0;
  for (const r of rows) {
    if (!r.business_name) continue;
    if (!r.instagram_handle && !r.tiktok_handle) {
      skipped_nohandle++;
      continue;
    }
    // Match by raw business_name + zip (BTRC stored zip is the 5-digit prefix)
    const zip = String(r.zip || '').slice(0, 5);
    const m = await client.query(
      `SELECT id FROM businesses
        WHERE on_corridor
          AND source = 'la_btrc'
          AND zip = $1
          AND (
            UPPER(raw->>'business_name') = UPPER($2)
            OR UPPER(raw->>'dba_name') = UPPER($2)
            OR UPPER(name) = UPPER($2)
          )
        LIMIT 1`,
      [zip, r.business_name]
    );
    if (m.rowCount === 0) { skipped_nomatch++; continue; }
    matched++;
    const businessId = m.rows[0].id;

    if (r.instagram_handle && r.instagram_url) {
      await client.query(
        `INSERT INTO business_contacts (
           business_id, role, person_name, source, source_url, confidence, notes
         ) VALUES ($1, 'social_handle', $2, 'ventura_recon_brave', $3, $4, 'instagram')
         ON CONFLICT DO NOTHING`,
        [businessId, '@' + r.instagram_handle, r.instagram_url, r.confidence === 'high' ? 0.9 : 0.7]
      );
      inserted++;
    }
    if (r.tiktok_handle && r.tiktok_url) {
      await client.query(
        `INSERT INTO business_contacts (
           business_id, role, person_name, source, source_url, confidence, notes
         ) VALUES ($1, 'social_handle', $2, 'ventura_recon_brave', $3, $4, 'tiktok')
         ON CONFLICT DO NOTHING`,
        [businessId, '@' + r.tiktok_handle, r.tiktok_url, r.confidence === 'high' ? 0.9 : 0.7]
      );
      inserted++;
    }
  }

  console.log(
    `[social] matched=${matched} inserted=${inserted} skipped_nohandle=${skipped_nohandle} skipped_nomatch=${skipped_nomatch}`
  );
  await client.end();
})().catch((e) => { console.error(e); process.exit(1); });