`scripts/aggregate-ratings.js`

72 lines
#!/usr/bin/env node
/**
 * Recompute aggregated_ratings from reviews. Run nightly via launchd.
 *
 *   - Excludes suppressed_by_target=true and hidden_by_admin=true.
 *   - Includes reviews where go_live_at <= now() OR go_live_at IS NULL (seeded).
 *   - Counts user reviews + seeded reviews separately.
 */
const { pool, query } = require('../agents/shared/db');

async function main() {
  const t0 = Date.now();
  console.log('[agg] recomputing aggregated_ratings…');

  // Wipe + rebuild — small enough table that a full recompute is fine.
  await query(`TRUNCATE aggregated_ratings RESTART IDENTITY`);

  // Per-professional aggregation.
  await query(`
    INSERT INTO aggregated_ratings (target_professional_id,
                                    service_avg, price_avg, quality_avg, overall_avg,
                                    n_reviews, n_user_reviews, n_seeded_reviews)
    SELECT target_professional_id,
           ROUND(AVG(service_score)::numeric, 2),
           ROUND(AVG(price_score)::numeric, 2),
           ROUND(AVG(quality_score)::numeric, 2),
           ROUND(AVG(overall_score)::numeric, 2),
           COUNT(*),
           COUNT(*) FILTER (WHERE source = 'user'),
           COUNT(*) FILTER (WHERE source <> 'user')
      FROM reviews
     WHERE target_professional_id IS NOT NULL
       AND suppressed_by_target = false
       AND hidden_by_admin = false
       AND (go_live_at IS NULL OR go_live_at <= now())
     GROUP BY target_professional_id
  `);

  // Per-organization aggregation.
  await query(`
    INSERT INTO aggregated_ratings (target_organization_id,
                                    service_avg, price_avg, quality_avg, overall_avg,
                                    n_reviews, n_user_reviews, n_seeded_reviews)
    SELECT target_organization_id,
           ROUND(AVG(service_score)::numeric, 2),
           ROUND(AVG(price_score)::numeric, 2),
           ROUND(AVG(quality_score)::numeric, 2),
           ROUND(AVG(overall_score)::numeric, 2),
           COUNT(*),
           COUNT(*) FILTER (WHERE source = 'user'),
           COUNT(*) FILTER (WHERE source <> 'user')
      FROM reviews
     WHERE target_organization_id IS NOT NULL
       AND suppressed_by_target = false
       AND hidden_by_admin = false
       AND (go_live_at IS NULL OR go_live_at <= now())
     GROUP BY target_organization_id
  `);

  const stats = (await query(`
    SELECT COUNT(*) AS rows,
           SUM(n_reviews)::int AS total_reviews,
           SUM(n_user_reviews)::int AS user_reviews,
           SUM(n_seeded_reviews)::int AS seeded_reviews
      FROM aggregated_ratings`)).rows[0];

  console.log(`[agg] done in ${(Date.now() - t0) / 1000}s. rows=${stats.rows} total_reviews=${stats.total_reviews} user=${stats.user_reviews} seeded=${stats.seeded_reviews}`);
  await pool.end();
}

main().catch(async e => { console.error(e); try { await pool.end(); } catch (_) {}; process.exit(1); });