← back to Norma

app/api/email-analyzer/suggestions/route.ts

170 lines

/**
 * GET /api/email-analyzer/suggestions?keywords=a,b,c&limit=5
 * Auto-suggests references for the Email Analyzer by searching across:
 *   - gmail_messages (synced inbox)
 *   - news_items (ingested news)
 *   - x_posts (journalists/leaders tweets — filtered by journalists.twitter_handle)
 *
 * Drive is not yet integrated — returns an empty drive bucket so the UI
 * can display "Drive not connected" without blowing up.
 */

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { searchDrive } from '@/lib/drive';

interface Suggestion {
  id: string;
  source: 'gmail' | 'news' | 'leader' | 'drive';
  title: string;
  snippet: string;
  url?: string;
  meta?: string;
  published_at?: string;
}

function parseKeywords(raw: string | null): string[] {
  if (!raw) return [];
  return raw
    .split(/[,\s]+/)
    .map((k) => k.trim())
    .filter((k) => k.length >= 3)
    .slice(0, 8);
}

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

  try {
    const { searchParams } = new URL(request.url);
    const keywords = parseKeywords(searchParams.get('keywords'));
    const limit = Math.min(parseInt(searchParams.get('limit') || '5', 10) || 5, 20);

    if (keywords.length === 0) {
      return NextResponse.json({
        gmail: [], news: [], leaders: [], drive: [],
        driveStatus: 'not_connected',
      });
    }

    const ilikeClauses = keywords.map((_, i) => `$${i + 1}`);
    const ilikeValues = keywords.map((k) => `%${k}%`);

    /* ── Gmail search ──────────────────────────────────────────────── */
    const gmailSql = `
      SELECT id::text, subject, snippet, date_sent, from_address
      FROM gmail_messages
      WHERE ${ilikeClauses.map((p) => `(subject ILIKE ${p} OR snippet ILIKE ${p} OR body_text ILIKE ${p})`).join(' OR ')}
      ORDER BY date_sent DESC NULLS LAST
      LIMIT ${limit}
    `;

    /* ── News search ───────────────────────────────────────────────── */
    const newsSql = `
      SELECT id::text, headline, summary, url, outlet, published_at
      FROM news_items
      WHERE ${ilikeClauses.map((p) => `(headline ILIKE ${p} OR summary ILIKE ${p})`).join(' OR ')}
      ORDER BY relevance_score DESC NULLS LAST, published_at DESC NULLS LAST
      LIMIT ${limit}
    `;

    /* ── Leader tweets search ──────────────────────────────────────── */
    const leadersSql = `
      SELECT x.id::text, x.content, x.author_name, x.author_handle, x.posted_at, j.beat
      FROM x_posts x
      LEFT JOIN journalists j
        ON lower(j.twitter_handle) = lower(x.author_handle)
      WHERE ${ilikeClauses.map((p) => `x.content ILIKE ${p}`).join(' OR ')}
      ORDER BY x.posted_at DESC NULLS LAST
      LIMIT ${limit}
    `;

    async function safe(sql: string): Promise<Array<Record<string, unknown>>> {
      try {
        const r = await query(sql, ilikeValues);
        return r.rows as Array<Record<string, unknown>>;
      } catch (e) {
        console.error('[suggestions] query error:', (e as Error).message);
        return [];
      }
    }

    const [gmailRows, newsRows, leaderRows, driveFiles, driveStatus] = await Promise.all([
      safe(gmailSql),
      safe(newsSql),
      safe(leadersSql),
      searchDrive(keywords, limit).catch((e) => {
        if ((e as Error).message === 'drive_scope_missing') return [];
        console.error('[suggestions] drive', (e as Error).message);
        return [];
      }),
      (async () => {
        try {
          const files = await searchDrive([], 1);
          return files !== null ? 'connected' : 'not_connected';
        } catch (e) {
          if ((e as Error).message === 'drive_scope_missing') return 'scope_missing';
          return 'not_connected';
        }
      })(),
    ]);

    const gmail: Suggestion[] = gmailRows.map((r) => ({
      id: String(r.id),
      source: 'gmail',
      title: (r.subject as string) || '(no subject)',
      snippet: ((r.snippet as string) || '').slice(0, 240),
      meta: r.from_address as string | undefined,
      published_at: r.date_sent as string | undefined,
    }));

    const news: Suggestion[] = newsRows.map((r) => ({
      id: String(r.id),
      source: 'news',
      title: (r.headline as string) || '(untitled)',
      snippet: ((r.summary as string) || '').slice(0, 240),
      url: r.url as string | undefined,
      meta: r.outlet as string | undefined,
      published_at: r.published_at as string | undefined,
    }));

    const leaders: Suggestion[] = leaderRows.map((r) => {
      const handle = r.author_handle as string | undefined;
      const name = r.author_name as string | undefined;
      return {
        id: String(r.id),
        source: 'leader',
        title: name ? `${name} (@${handle})` : `@${handle || 'unknown'}`,
        snippet: ((r.content as string) || '').slice(0, 240),
        url: handle ? `https://x.com/${handle}` : undefined,
        meta: (r.beat as string) || undefined,
        published_at: r.posted_at as string | undefined,
      };
    });

    const drive: Suggestion[] = (driveFiles || []).map((f) => ({
      id: f.id,
      source: 'drive',
      title: f.name,
      snippet: f.owners.length ? `Owner: ${f.owners.join(', ')}` : '',
      url: f.webViewLink,
      meta: f.mimeType.replace('application/vnd.google-apps.', ''),
      published_at: f.modifiedTime,
    }));

    return NextResponse.json({
      gmail,
      news,
      leaders,
      drive,
      driveStatus,
      keywords,
    });
  } catch (err) {
    console.error('[email-analyzer/suggestions] error:', (err as Error).message);
    return NextResponse.json({ error: 'Suggestions failed' }, { status: 500 });
  }
}