← back to Norma

app/api/journalists/directory/route.ts

135 lines

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

// ─── GET /api/journalists/directory ──────────────────────────────────────────
// Returns reporters with associated LinkedIn contacts and suggestion scores.

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 category = searchParams.get('category') || '';

    // Fetch all journalists with article stats
    const journalists = await query(
      `SELECT j.*,
              COUNT(ja.id) as article_count,
              MAX(ja.published_date) as latest_article,
              MIN(ja.published_date) as first_article,
              array_agg(DISTINCT unnest_topic) FILTER (WHERE unnest_topic IS NOT NULL) as all_topics
       FROM journalists j
       LEFT JOIN journalist_articles ja ON ja.journalist_id = j.id
       LEFT JOIN LATERAL unnest(ja.topics) AS unnest_topic ON true
       WHERE j.is_active = true
       GROUP BY j.id
       ORDER BY COUNT(ja.id) DESC, j.created_at DESC`,
    );

    // Fetch contacts who might be connected to journalists (mentioned in articles)
    const contactLinks = await query(
      `SELECT DISTINCT ON (c.id, j.journalist_id)
              c.id as contact_id, c.first_name, c.last_name, c.email,
              c.company, c.position, c.headline, c.linkedin_url,
              c.location, c.profile_photo_url as photo_url, c.skills,
              j.journalist_id
       FROM contacts c
       JOIN (
         SELECT journalist_id, unnest(mentioned_people) as person
         FROM journalist_articles
       ) j ON LOWER(c.first_name || ' ' || c.last_name) = LOWER(j.person)
          OR (LENGTH(c.last_name) > 2 AND LOWER(j.person) LIKE '%' || LOWER(c.last_name) || '%')
       LIMIT 100`,
    );

    // Build a map of journalist → linked contacts
    const journalistContacts = new Map<string, Array<Record<string, unknown>>>();
    for (const link of contactLinks.rows) {
      const jId = link.journalist_id as string;
      if (!journalistContacts.has(jId)) journalistContacts.set(jId, []);
      journalistContacts.get(jId)!.push({
        id: link.contact_id,
        first_name: link.first_name,
        last_name: link.last_name,
        email: link.email,
        company: link.company,
        position: link.position,
        headline: link.headline,
        linkedin_url: link.linkedin_url,
        location: link.location,
        photo_url: link.photo_url,
        skills: link.skills,
      });
    }

    // Suggested reporters: those covering student debt topics
    const suggestedTopics = [
      'student debt', 'student loan', 'loan forgiveness', 'SAVE plan',
      'income-driven repayment', 'higher education', 'tuition',
      'borrower defense', 'Pell Grant', 'FAFSA',
    ];

    const directory = (journalists.rows as Array<Record<string, unknown>>).map((j) => {
      const topics = (j.all_topics as string[]) || [];
      const topicLower = topics.map(t => (t || '').toLowerCase());

      // Calculate relevance score based on topic overlap
      let relevanceScore = 0;
      for (const st of suggestedTopics) {
        if (topicLower.some(t => t.includes(st) || st.includes(t))) {
          relevanceScore += 1;
        }
      }

      // Boost for recent activity
      if (j.latest_article) {
        const daysSince = (Date.now() - new Date(j.latest_article as string).getTime()) / 86400000;
        if (daysSince < 30) relevanceScore += 2;
        else if (daysSince < 90) relevanceScore += 1;
      }

      const interacted = journalistContacts.has(j.id as string);

      return {
        ...j,
        linked_contacts: journalistContacts.get(j.id as string) || [],
        relevance_score: relevanceScore,
        interacted,
        status: interacted ? 'connected' : relevanceScore >= 3 ? 'recommended' : 'potential',
      };
    });

    // Filter by category if provided
    let filtered = directory;
    if (category === 'connected') {
      filtered = directory.filter(d => d.interacted);
    } else if (category === 'recommended') {
      filtered = directory.filter(d => !d.interacted && d.relevance_score >= 3);
    } else if (category === 'potential') {
      filtered = directory.filter(d => !d.interacted && d.relevance_score < 3);
    }

    // Sort: connected first, then by relevance score
    filtered.sort((a, b) => {
      if (a.interacted && !b.interacted) return -1;
      if (!a.interacted && b.interacted) return 1;
      return (b.relevance_score as number) - (a.relevance_score as number);
    });

    return NextResponse.json({
      reporters: filtered,
      stats: {
        total: directory.length,
        connected: directory.filter(d => d.interacted).length,
        recommended: directory.filter(d => !d.interacted && d.relevance_score >= 3).length,
        potential: directory.filter(d => !d.interacted && d.relevance_score < 3).length,
      },
    });
  } catch (err) {
    console.error('[journalists/directory] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}