`src/jobs/normalize_categories.ts`

73 lines
/**
 * Backfill / fix category_tag on magazine_features using a deterministic
 * pitch_type → category_tag mapping (instead of trusting qwen3's pick).
 * Runs idempotently; only updates rows where the deterministic value differs.
 *
 *   $ npx tsx src/jobs/normalize_categories.ts          # apply to all
 *   $ npx tsx src/jobs/normalize_categories.ts --dry-run
 */
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const DRY = process.argv.includes('--dry-run');

const MAP: Record<string, string> = {
  'restaurant':         'restaurant',
  'food-service':       'restaurant',
  'cafe':               'restaurant',
  'bar':                'restaurant',
  'salon-spa':          'salon',
  'beauty-salon':       'salon',
  'beauty-spa':         'beauty',
  'nail-salon':         'beauty',
  'medical-clinic':     'medical',
  'dental-office':      'medical',
  'chiropractic':       'medical',
  'therapy':            'medical',
  'fitness-studio':     'fitness',
  'gym':                'fitness',
  'yoga':               'fitness',
  'law-office':         'professional',
  'accountant':         'professional',
  'cpa':                'professional',
  'real-estate':        'real-estate',
  'real-estate-agent':  'real-estate',
  'realty':             'real-estate',
  'retail-storefront':  'shop',
  'boutique':           'shop',
  'gift-shop':          'shop',
  'office-tenant':      'professional',
  'building-neighbor':  'professional',
  'hospitality':        'hospitality',
  'hotel':              'hospitality',
  'automotive':         'automotive',
  'auto-repair':        'automotive',
  'car-wash':           'automotive',
  'beauty':             'beauty',
};

async function main() {
  const r = await query(`
    SELECT mf.id, mf.category_tag, p.pitch_type
    FROM magazine_features mf
    JOIN pitches p ON p.business_id = mf.business_id
    WHERE p.pitch_type IS NOT NULL
  `);
  let toUpdate = 0;
  for (const row of r.rows) {
    const expected = MAP[row.pitch_type];
    if (!expected) continue;
    if (row.category_tag === expected) continue;
    if (DRY) {
      console.log(`  [dry] ${row.id} ${row.pitch_type} : ${row.category_tag} → ${expected}`);
    } else {
      await query(`UPDATE magazine_features SET category_tag = $1 WHERE id = $2`, [expected, row.id]);
    }
    toUpdate++;
  }
  console.log(`[normalize_categories] ${DRY ? 'would update' : 'updated'} ${toUpdate} of ${r.rowCount} features`);
  await pool.end();
}

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