leads4less/db/scripts/check-postal-status.ts

import { getDbPool } from '../../server/src/db/pool.js';
import { createScriptLogger } from './postal-logging.js';

async function run() {
  const logger = createScriptLogger('postal-status');
  const pool = getDbPool();

  try {
    const postalAreasByCountry = await pool.query<{ country_code: string; area_count: string }>(`
      select country_code, count(*)::text as area_count
      from public.postal_areas
      group by country_code
      order by country_code asc
    `);

    const neighborCountsByCountry = await pool.query<{ country_code: string; neighbor_count: string }>(`
      select area.country_code, count(*)::text as neighbor_count
      from public.postal_area_neighbors link
      join public.postal_areas area on area.id = link.postal_area_id
      group by area.country_code
      order by area.country_code asc
    `);

    const totalAreas = await pool.query<{ count: string }>('select count(*)::text as count from public.postal_areas');
    const totalNeighbors = await pool.query<{ count: string }>('select count(*)::text as count from public.postal_area_neighbors');

    logger.info(`Postal areas loaded: ${totalAreas.rows[0]?.count ?? '0'}`);
    postalAreasByCountry.rows.forEach((row) => {
      logger.info(`  ${row.country_code}: ${row.area_count} postal areas`);
    });

    logger.info(`Postal neighbor links built: ${totalNeighbors.rows[0]?.count ?? '0'}`);
    neighborCountsByCountry.rows.forEach((row) => {
      logger.info(`  ${row.country_code}: ${row.neighbor_count} adjacency links`);
    });
  } finally {
    await pool.end();
  }
}

await run();