ScraperControl/scripts/debug/check-enrichment.ts

import { Pool } from 'pg';
import * as dotenv from 'dotenv';
import * as path from 'path';

// Load .env.local first (takes precedence), then .env
dotenv.config({ path: path.resolve(process.cwd(), '.env.local') });
dotenv.config({ path: path.resolve(process.cwd(), '.env') });

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

async function checkEnrichment() {
  try {
    console.log('Connecting to database...');

    // Check total enriched churches
    const totalResult = await pool.query(`
      SELECT
        COUNT(*) as total_enriched,
        COUNT(CASE WHEN updated_at > NOW() - INTERVAL '24 hours' THEN 1 END) as enriched_last_24h,
        MAX(updated_at) as last_enrichment
      FROM churches
      WHERE google_place_id IS NOT NULL;
    `);

    console.log('\n=== Google Enrichment Summary ===');
    console.log(`Total churches with Google Place ID: ${totalResult.rows[0].total_enriched}`);
    console.log(`Enriched in last 24 hours: ${totalResult.rows[0].enriched_last_24h}`);
    console.log(`Last enrichment: ${totalResult.rows[0].last_enrichment}`);

    // Check by country
    const countryResult = await pool.query(`
      SELECT
        country,
        COUNT(*) as enriched_count,
        COUNT(CASE WHEN updated_at > NOW() - INTERVAL '24 hours' THEN 1 END) as enriched_last_24h
      FROM churches
      WHERE google_place_id IS NOT NULL
      GROUP BY country
      ORDER BY enriched_last_24h DESC
      LIMIT 10;
    `);

    console.log('\n=== Top Countries Enriched (Last 24h) ===');
    countryResult.rows.forEach((row) => {
      console.log(`${row.country}: ${row.enriched_last_24h} new / ${row.enriched_count} total`);
    });

    // Check recent enrichments with details
    const recentResult = await pool.query(`
      SELECT
        name,
        city,
        country,
        google_place_id,
        updated_at
      FROM churches
      WHERE google_place_id IS NOT NULL
        AND updated_at > NOW() - INTERVAL '24 hours'
      ORDER BY updated_at DESC
      LIMIT 20;
    `);

    console.log('\n=== Recent Enrichments (Last 24h, sample) ===');
    recentResult.rows.forEach((row) => {
      const timestamp = row.updated_at ? new Date(row.updated_at).toISOString() : 'unknown';
      console.log(`${row.name}, ${row.city}, ${row.country} - ${timestamp}`);
    });

  } catch (error) {
    console.error('Error checking enrichment:', error);
  } finally {
    await pool.end();
  }
}

checkEnrichment();