`app/api/alerts/route.ts`

279 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';

/**
 * Unified alert item returned to the client.
 */
interface AlertItem {
  id: string;
  type: 'news' | 'grant' | 'petition' | 'congress' | 'donation' | 'community' | 'organization';
  title: string;
  summary: string;
  date: string;
  source: string;
}

const VALID_CATEGORIES = [
  'news_media',
  'grants',
  'petitions',
  'congress',
  'donations',
  'community',
  'organizations',
] as const;

type Category = (typeof VALID_CATEGORIES)[number];

/**
 * GET /api/alerts?categories=news_media,grants,petitions&limit=20&keywords=student+debt,loan+forgiveness
 *
 * Aggregates recent items from each enabled category into a unified alert feed.
 * When `keywords` is provided, text-based categories (news, grants, petitions, community)
 * are filtered to only return items matching those keywords — this gives each org
 * a different, relevant feed.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

  try {
    const { searchParams } = new URL(request.url);

    // Parse categories — default to all if omitted
    const categoriesParam = searchParams.get('categories');
    const requestedCategories: Category[] = categoriesParam
      ? (categoriesParam.split(',').filter((c) => VALID_CATEGORIES.includes(c as Category)) as Category[])
      : [...VALID_CATEGORIES];

    // Org-specific keywords for filtering (comma-separated)
    const keywordsParam = searchParams.get('keywords');
    const keywords: string[] = keywordsParam
      ? keywordsParam.split(',').map((k) => k.trim()).filter(Boolean)
      : [];

    // Per-category limit (how many recent items to pull from each table)
    let perCategory = parseInt(searchParams.get('per') || '5', 10);
    if (isNaN(perCategory) || perCategory < 1) perCategory = 5;
    if (perCategory > 20) perCategory = 20;

    // Overall limit on the merged result
    let limit = parseInt(searchParams.get('limit') || '20', 10);
    if (isNaN(limit) || limit < 1) limit = 20;
    if (limit > 100) limit = 100;

    // Fetch each category concurrently; failed queries return empty arrays
    const fetchers: Promise<AlertItem[]>[] = requestedCategories.map((cat) =>
      fetchCategory(cat, perCategory, keywords, orgId),
    );

    const results = await Promise.all(fetchers);
    const merged: AlertItem[] = results
      .flat()
      .sort((a, b) => new Date(b.date).getTime() - new Date(a.date).getTime())
      .slice(0, limit);

    return NextResponse.json({ alerts: merged, total: merged.length });
  } catch (err) {
    console.error('[api/alerts] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch alerts' }, { status: 500 });
  }
}

/* ─── Per-category fetchers ────────────────────────────────────────────────── */

async function fetchCategory(category: Category, limit: number, keywords: string[], orgId: string | null): Promise<AlertItem[]> {
  try {
    switch (category) {
      case 'news_media':
        return await fetchNews(limit, keywords, orgId);
      case 'grants':
        return await fetchGrants(limit, keywords, orgId);
      case 'petitions':
        return await fetchPetitions(limit, keywords, orgId);
      case 'congress':
        return await fetchCongress(limit);
      case 'donations':
        return await fetchDonations(limit, orgId);
      case 'community':
        return await fetchCommunity(limit, keywords);
      case 'organizations':
        return await fetchOrganizations(limit);
      default:
        return [];
    }
  } catch (err) {
    console.warn(`[api/alerts] Failed to fetch ${category}:`, (err as Error).message);
    return [];
  }
}

/** Build a WHERE clause fragment for keyword text search */
function keywordFilter(keywords: string[], columns: string[], paramOffset: number): { clause: string; params: string[]; nextIdx: number } {
  if (keywords.length === 0) return { clause: '', params: [], nextIdx: paramOffset };
  // Build: (col1 || ' ' || col2) ILIKE ANY(ARRAY[$2, $3, ...])
  const colExpr = columns.map(c => `COALESCE(${c},'')`).join(" || ' ' || ");
  const placeholders = keywords.map((_, i) => `$${paramOffset + i}`);
  const patterns = keywords.map((k) => `%${k}%`);
  return {
    clause: `AND (${colExpr}) ILIKE ANY(ARRAY[${placeholders.join(',')}])`,
    params: patterns,
    nextIdx: paramOffset + keywords.length,
  };
}

function truncate(text: string | null | undefined, max = 120): string {
  if (!text) return '';
  if (text.length <= max) return text;
  return text.slice(0, max).trimEnd() + '...';
}

async function fetchNews(limit: number, keywords: string[] = [], orgId: string | null = null): Promise<AlertItem[]> {
  const orgClause = orgId ? `AND org_id = $2` : '';
  const orgParams = orgId ? [orgId] : [];
  const kw = keywordFilter(keywords, ['headline', 'summary'], orgId ? 3 : 2);
  const result = await query(
    `SELECT id, headline, summary, outlet, published_at
     FROM news_items
     WHERE 1=1 ${orgClause} ${kw.clause}
     ORDER BY published_at DESC NULLS LAST
     LIMIT $1`,
    [limit, ...orgParams, ...kw.params],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'news' as const,
    title: r.headline,
    summary: truncate(r.summary),
    date: r.published_at?.toISOString?.() ?? r.published_at ?? new Date().toISOString(),
    source: r.outlet || 'News',
  }));
}

async function fetchGrants(limit: number, keywords: string[] = [], orgId: string | null = null): Promise<AlertItem[]> {
  const orgClause = orgId ? `AND org_id = $2` : '';
  const orgParams = orgId ? [orgId] : [];
  const kw = keywordFilter(keywords, ['title', 'description'], orgId ? 3 : 2);
  const result = await query(
    `SELECT id, title, description, deadline, created_at
     FROM grants
     WHERE 1=1 ${orgClause} ${kw.clause}
     ORDER BY created_at DESC
     LIMIT $1`,
    [limit, ...orgParams, ...kw.params],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'grant' as const,
    title: r.title,
    summary: truncate(r.description),
    date: r.created_at?.toISOString?.() ?? r.created_at ?? new Date().toISOString(),
    source: 'Grants',
  }));
}

async function fetchPetitions(limit: number, keywords: string[] = [], orgId: string | null = null): Promise<AlertItem[]> {
  const orgClause = orgId ? `AND org_id = $2` : '';
  const orgParams = orgId ? [orgId] : [];
  const kw = keywordFilter(keywords, ['title', 'description'], orgId ? 3 : 2);
  const result = await query(
    `SELECT id, title, description, signature_count, created_at
     FROM petitions
     WHERE 1=1 ${orgClause} ${kw.clause}
     ORDER BY created_at DESC
     LIMIT $1`,
    [limit, ...orgParams, ...kw.params],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'petition' as const,
    title: r.title,
    summary: truncate(r.description) || (r.signature_count ? `${r.signature_count.toLocaleString()} signatures` : ''),
    date: r.created_at?.toISOString?.() ?? r.created_at ?? new Date().toISOString(),
    source: 'Petitions',
  }));
}

async function fetchCongress(limit: number): Promise<AlertItem[]> {
  const result = await query(
    `SELECT id, representative_name, representative_party, state_abbr, district_number, updated_at
     FROM congressional_districts
     WHERE representative_name IS NOT NULL
     ORDER BY updated_at DESC NULLS LAST
     LIMIT $1`,
    [limit],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'congress' as const,
    title: `${r.representative_name} (${r.representative_party || '?'}-${r.state_abbr})`,
    summary: `District ${r.district_number}, ${r.state_abbr}`,
    date: r.updated_at?.toISOString?.() ?? r.updated_at ?? new Date().toISOString(),
    source: 'Congress',
  }));
}

async function fetchDonations(limit: number, orgId: string | null = null): Promise<AlertItem[]> {
  const orgClause = orgId ? `AND org_id = $1` : '';
  const orgParams = orgId ? [orgId] : [];
  const limitIdx = orgId ? 2 : 1;
  const result = await query(
    `SELECT id, amount, source, campaign_name, donated_at
     FROM donations
     WHERE 1=1 ${orgClause}
     ORDER BY donated_at DESC
     LIMIT $${limitIdx}`,
    [...orgParams, limit],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'donation' as const,
    title: `$${Number(r.amount).toLocaleString()} donation${r.campaign_name ? ` — ${r.campaign_name}` : ''}`,
    summary: `Source: ${r.source}`,
    date: r.donated_at?.toISOString?.() ?? r.donated_at ?? new Date().toISOString(),
    source: 'Donations',
  }));
}

async function fetchCommunity(limit: number, keywords: string[] = []): Promise<AlertItem[]> {
  const kw = keywordFilter(keywords, ['title', 'body'], 2);
  const result = await query(
    `SELECT id, title, body, platform, posted_at, created_at
     FROM community_posts
     WHERE 1=1 ${kw.clause}
     ORDER BY COALESCE(posted_at, created_at) DESC
     LIMIT $1`,
    [limit, ...kw.params],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'community' as const,
    title: r.title || 'Community post',
    summary: truncate(r.body),
    date: (r.posted_at ?? r.created_at)?.toISOString?.() ?? new Date().toISOString(),
    source: r.platform || 'Community',
  }));
}

async function fetchOrganizations(limit: number): Promise<AlertItem[]> {
  const result = await query(
    `SELECT id, name, mission, city, state, created_at
     FROM organizations
     ORDER BY created_at DESC
     LIMIT $1`,
    [limit],
  );
  return result.rows.map((r) => ({
    id: r.id,
    type: 'organization' as const,
    title: r.name,
    summary: truncate(r.mission) || [r.city, r.state].filter(Boolean).join(', ') || '',
    date: r.created_at?.toISOString?.() ?? r.created_at ?? new Date().toISOString(),
    source: 'Organizations',
  }));
}