`app/api/globe/countries/route.ts`

162 lines
/**
 * GET /api/globe/countries
 * Returns all country profiles with activity scores for globe dot placement.
 *
 * Response:
 *   { countries: CountryRow[] }
 *
 * CountryRow:
 *   code, name, region, lat, lng, value, article_count, org_count,
 *   sentiment_avg, todays_headlines
 */

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { COUNTRY_CENTROIDS } from '@/components/globe/globe-utils';

export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    // Try to query country_profiles table if it exists
    let rows: Array<Record<string, unknown>> = [];

    try {
      const result = await query(`
        SELECT
          cp.country_code,
          cp.country_name,
          cp.region,
          cp.latitude,
          cp.longitude,
          cp.article_count,
          cp.org_count,
          cp.sentiment_avg,
          COALESCE(
            (SELECT COUNT(*)::int
             FROM newspaper_frontpages np
             WHERE np.country = cp.country_code
               AND np.scraped_date = CURRENT_DATE),
            0
          ) AS todays_headlines
        FROM country_profiles cp
        ORDER BY cp.article_count DESC
        LIMIT 200
      `);
      rows = result.rows;
    } catch {
      // country_profiles table may not exist yet — build from other tables
    }

    // If we got rows from country_profiles, return them enriched
    if (rows.length > 0) {
      const countries = rows.map(r => {
        const code = String(r.country_code || '');
        const centroid = COUNTRY_CENTROIDS[code] || [0, 0];
        const lat = typeof r.latitude === 'number' ? r.latitude : centroid[0];
        const lng = typeof r.longitude === 'number' ? r.longitude : centroid[1];

        return {
          code,
          name: String(r.country_name || code),
          region: r.region ? String(r.region) : null,
          lat,
          lng,
          article_count: Number(r.article_count) || 0,
          org_count: Number(r.org_count) || 0,
          sentiment_avg: r.sentiment_avg != null ? Number(r.sentiment_avg) : null,
          todays_headlines: Number(r.todays_headlines) || 0,
          value: (Number(r.article_count) || 0) + (Number(r.org_count) || 0),
        };
      });

      return NextResponse.json({ countries });
    }

    // Fallback: build from organizations table country field + statements
    const orgResult = await query(`
      SELECT
        UPPER(TRIM(o.country)) AS country_code,
        COUNT(DISTINCT o.id)::int AS org_count
      FROM organizations o
      WHERE o.country IS NOT NULL AND o.country != ''
      GROUP BY UPPER(TRIM(o.country))
      HAVING COUNT(DISTINCT o.id) > 0
      ORDER BY org_count DESC
      LIMIT 100
    `).catch(() => ({ rows: [] as Array<Record<string, unknown>> }));

    // Build country list from orgs + centroids
    const countryMap = new Map<string, {
      code: string;
      org_count: number;
      article_count: number;
      sentiment_avg: number | null;
    }>();

    for (const r of orgResult.rows) {
      const code = String(r.country_code || '');
      if (!code || !COUNTRY_CENTROIDS[code]) continue;
      countryMap.set(code, {
        code,
        org_count: Number(r.org_count) || 0,
        article_count: 0,
        sentiment_avg: null,
      });
    }

    // Merge statement country data if available
    await query(`
      SELECT
        UPPER(TRIM(country)) AS country_code,
        COUNT(*)::int AS statement_count,
        AVG(sentiment_score)::float AS sentiment_avg
      FROM nonprofit_statements
      WHERE country IS NOT NULL AND country != ''
      GROUP BY UPPER(TRIM(country))
    `).then(res => {
      for (const r of res.rows) {
        const code = String(r.country_code || '');
        if (!code) continue;
        const existing = countryMap.get(code);
        if (existing) {
          existing.article_count = Number(r.statement_count) || 0;
          existing.sentiment_avg = r.sentiment_avg != null ? Number(r.sentiment_avg) : null;
        } else if (COUNTRY_CENTROIDS[code]) {
          countryMap.set(code, {
            code,
            org_count: 0,
            article_count: Number(r.statement_count) || 0,
            sentiment_avg: r.sentiment_avg != null ? Number(r.sentiment_avg) : null,
          });
        }
      }
    }).catch(() => { /* statements table may not exist */ });

    // Build response from centroid map — always include all known centroids
    // with at least a stub entry so the globe always has data
    const countries = Object.entries(COUNTRY_CENTROIDS).map(([code, [lat, lng]]) => {
      const data = countryMap.get(code);
      return {
        code,
        name: code, // will be enriched by name lookup if available
        region: null,
        lat,
        lng,
        article_count: data?.article_count || 0,
        org_count: data?.org_count || 0,
        sentiment_avg: data?.sentiment_avg ?? null,
        todays_headlines: 0,
        value: (data?.article_count || 0) + (data?.org_count || 0),
      };
    }).filter(c => c.value > 0 || c.code === 'US');

    return NextResponse.json({ countries });
  } catch (err) {
    console.error('[api/globe/countries] error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch country data' }, { status: 500 });
  }
}