`app/api/journalists/route.ts`

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

/* ═══════════════════════════════════════════════════════════════════════════
   GET /api/journalists — list journalists with stats
   Query: ?q=search&outlet=&year=2024&tag=&sort=articles|name|recent
   ═══════════════════════════════════════════════════════════════════════════ */

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

  try {
    const { searchParams } = new URL(req.url);
    const q = searchParams.get('q') || '';
    const outlet = searchParams.get('outlet') || '';
    const tag = searchParams.get('tag') || '';
    const year = searchParams.get('year') || '';
    const state = searchParams.get('state') || '';
    const sort = searchParams.get('sort') || 'articles';

    // Base query: journalists with article counts, year range, and org counts
    let sql = `
      SELECT j.*,
        COUNT(DISTINCT a.id) AS article_count,
        COUNT(DISTINCT o.id) AS org_count,
        MIN(a.published_date) AS first_article,
        MAX(a.published_date) AS latest_article,
        COALESCE(
          json_agg(DISTINCT jsonb_build_object('year', EXTRACT(YEAR FROM a.published_date)::int, 'count', 1))
            FILTER (WHERE a.id IS NOT NULL),
          '[]'
        ) AS year_data_raw
      FROM journalists j
      LEFT JOIN journalist_articles a ON a.journalist_id = j.id
    `;

    const conditions: string[] = [];
    const params: (string | number)[] = [];
    let idx = 1;

    if (year) {
      conditions.push(`EXTRACT(YEAR FROM a.published_date) = $${idx++}`);
      params.push(Number(year));
    }

    sql += ` LEFT JOIN journalist_orgs o ON o.journalist_id = j.id`;

    if (q) {
      conditions.push(`(j.name ILIKE $${idx} OR j.outlet ILIKE $${idx} OR j.beat ILIKE $${idx})`);
      params.push(`%${q}%`);
      idx++;
    }
    if (outlet) {
      conditions.push(`j.outlet = $${idx++}`);
      params.push(outlet);
    }
    if (tag) {
      conditions.push(`$${idx++} = ANY(j.tags)`);
      params.push(tag);
    }
    if (state) {
      conditions.push(`j.state = $${idx++}`);
      params.push(state);
    }

    if (conditions.length) sql += ` WHERE ${conditions.join(' AND ')}`;

    sql += ` GROUP BY j.id`;

    const orderMap: Record<string, string> = {
      articles: 'article_count DESC',
      name: 'j.name ASC',
      recent: 'latest_article DESC NULLS LAST',
      outlet: 'j.outlet ASC, j.name ASC',
    };
    sql += ` ORDER BY ${orderMap[sort] || orderMap.articles}`;

    const { rows: journalists } = await query(sql, params);

    // Compute year breakdowns in a single batch query (avoids N+1)
    const journalistIds = journalists.map((j: any) => j.id);
    const yearBreakdownMap = new Map<string, { year: number; count: number }[]>();

    if (journalistIds.length > 0) {
      const { rows: yearRows } = await query(
        `SELECT journalist_id, EXTRACT(YEAR FROM published_date)::int AS year, COUNT(*)::int AS count
         FROM journalist_articles
         WHERE journalist_id = ANY($1)
         GROUP BY journalist_id, year
         ORDER BY journalist_id, year`,
        [journalistIds]
      );
      for (const row of yearRows) {
        const key = String(row.journalist_id);
        if (!yearBreakdownMap.has(key)) yearBreakdownMap.set(key, []);
        yearBreakdownMap.get(key)!.push({ year: row.year, count: row.count });
      }
    }

    for (const j of journalists) {
      j.year_breakdown = yearBreakdownMap.get(String(j.id)) || [];
      delete j.year_data_raw;
    }

    // Get all outlets for filter
    const { rows: outlets } = await query(
      `SELECT DISTINCT outlet, COUNT(*)::int AS count FROM journalists GROUP BY outlet ORDER BY count DESC`
    );

    // Get all tags across journalists
    const { rows: tagRows } = await query(
      `SELECT UNNEST(tags) AS tag, COUNT(*)::int AS count FROM journalists GROUP BY tag ORDER BY count DESC LIMIT 30`
    );

    // Get all states for filter
    const { rows: stateRows } = await query(
      `SELECT state, COUNT(*)::int AS count FROM journalists WHERE state IS NOT NULL GROUP BY state ORDER BY state`
    );

    // Get year range
    const { rows: yearRange } = await query(
      `SELECT EXTRACT(YEAR FROM MIN(published_date))::int AS min_year,
              EXTRACT(YEAR FROM MAX(published_date))::int AS max_year
       FROM journalist_articles`
    );

    return NextResponse.json({
      journalists,
      outlets,
      tags: tagRows,
      states: stateRows,
      yearRange: yearRange[0] || { min_year: 2021, max_year: 2026 },
      total: journalists.length,
    });
  } catch (err: any) {
    console.error('Journalists GET error:', err.message);
    return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
  }
}

/* ═══════════════════════════════════════════════════════════════════════════
   POST /api/journalists — add a new journalist
   ═══════════════════════════════════════════════════════════════════════════ */

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

  try {
    const body = await req.json();
    const {
      name, outlet, beat, bio, twitter_handle, email, tags, linkedin_url,
      website, instagram_handle, facebook_url, youtube_url, substack_url,
      medium_url, podcast_url, rss_feed,
    } = body;

    if (!name || !outlet) {
      return NextResponse.json({ error: 'Name and outlet are required' }, { status: 400 });
    }

    const { rows } = await query(
      `INSERT INTO journalists (name, outlet, beat, bio, twitter_handle, email, tags, added_by,
        linkedin_url, website, instagram_handle, facebook_url, youtube_url, substack_url, medium_url, podcast_url, rss_feed)
       VALUES ($1, $2, $3, $4, $5, $6, $7, 'user', $8, $9, $10, $11, $12, $13, $14, $15, $16)
       RETURNING *`,
      [name, outlet, beat || 'General', bio || '', twitter_handle || '', email || '', tags || [],
       linkedin_url || null, website || null, instagram_handle || null, facebook_url || null,
       youtube_url || null, substack_url || null, medium_url || null, podcast_url || null, rss_feed || null]
    );

    return NextResponse.json({ journalist: rows[0] }, { status: 201 });
  } catch (err: any) {
    console.error('Journalists POST error:', err.message);
    return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
  }
}