`app/api/gmail/messages/route.ts`

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

/**
 * GET /api/gmail/messages
 *
 * List gmail messages with filtering, sorting, and pagination.
 *
 * Query params:
 *   ?sort=date|priority|from   (default: date)
 *   ?sort_dir=asc|desc         (default: desc)
 *   ?priority=high|medium|low  (filter by computed priority)
 *   ?topic=                    (filter by label/topic tag)
 *   ?from=                     (filter by sender email)
 *   ?q=                        (full-text search in subject + body_text)
 *   ?is_read=true|false
 *   ?limit=50&offset=0
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const sp = request.nextUrl.searchParams;
  const sort = sp.get('sort') || 'date';
  const sortDir = sp.get('sort_dir') === 'asc' ? 'ASC' : 'DESC';
  const priority = sp.get('priority');
  const topic = sp.get('topic');
  const from = sp.get('from');
  const q = sp.get('q');
  const isRead = sp.get('is_read');
  const limit = Math.min(parseInt(sp.get('limit') || '50', 10), 200);
  const offset = parseInt(sp.get('offset') || '0', 10);

  // Build the priority CASE expression
  const priorityExpr = `
    CASE
      WHEN labels @> ARRAY['IMPORTANT']
        OR from_address ILIKE '%media%'
        OR from_address ILIKE '%press%'
        OR from_address ILIKE '%journalist%'
        OR from_address ILIKE '%reporter%'
        OR from_address ILIKE '%congress%'
        OR from_address ILIKE '%senate%'
        OR from_address ILIKE '%house.gov%'
      THEN 'high'
      WHEN labels @> ARRAY['CATEGORY_PROMOTIONS']
        OR from_address ILIKE '%noreply%'
        OR from_address ILIKE '%newsletter%'
      THEN 'low'
      ELSE 'medium'
    END
  `;

  const conditions: string[] = [];
  const params: unknown[] = [];
  let idx = 1;

  // Mailbox scoping:
  //   - Admin defaults to the UNION of every connected mailbox in the system
  //     (the "main account sees everything" pattern Steve asked for).
  //   - Staff / intern / pulse default to their OWN mailbox only.
  //   - ?mailbox=<id>  → specific override (anyone can pin a single mailbox)
  //   - ?mailbox=own   → force own-mailbox view even when admin
  //   - ?mailbox=all   → admin-only escape hatch (same as default for admin)
  const explicitMailbox = sp.get('mailbox');
  if (explicitMailbox === 'all' && auth.role === 'admin') {
    // intentional no-op — admin opted into the full union
  } else if (!explicitMailbox && auth.role === 'admin') {
    // Admin default: every connected mailbox
    const mbs = await query<{ id: string }>(
      `SELECT id FROM mailboxes WHERE is_active = true AND oauth_tokens IS NOT NULL`,
    );
    if (mbs.rows.length > 0) {
      const placeholders = mbs.rows.map(() => `$${idx++}`).join(',');
      conditions.push(`mailbox_id IN (${placeholders})`);
      mbs.rows.forEach((r) => params.push(r.id));
    } else {
      // No connected mailboxes yet → empty list, surface needs_connect for the UI
      return NextResponse.json({
        messages:      [],
        total:         0,
        limit,
        offset,
        needs_connect: true,
        admin_view:    true,
      });
    }
  } else if (explicitMailbox && explicitMailbox !== 'own') {
    conditions.push(`mailbox_id = $${idx++}`);
    params.push(explicitMailbox);
  } else {
    // Resolve the user's email — tier_credentials has no email column, so the
    // username IS the email when it's email-shaped.
    let userEmail: string | null = null;
    if (auth.username.includes('@')) {
      userEmail = auth.username;
    }
    if (userEmail) {
      const mb = await query<{ id: string; oauth_tokens: unknown; needs_reauth: boolean | null }>(
        `SELECT id, oauth_tokens, needs_reauth
           FROM mailboxes
          WHERE LOWER(email) = LOWER($1) AND is_active = true
          ORDER BY (oauth_tokens IS NOT NULL) DESC
          LIMIT 1`,
        [userEmail],
      );
      if (mb.rows.length === 0) {
        // No mailbox row at all → tell the UI to show Connect Gmail.
        return NextResponse.json({
          messages:      [],
          total:         0,
          limit,
          offset,
          mailbox_email: userEmail,
          needs_connect: true,
        });
      }
      const row = mb.rows[0];
      const connected = !!row.oauth_tokens && !row.needs_reauth;
      if (!connected) {
        // Row exists but never OAuth'd (or revoked) → same outcome.
        return NextResponse.json({
          messages:      [],
          total:         0,
          limit,
          offset,
          mailbox_email: userEmail,
          needs_connect: true,
          needs_reauth:  !!row.needs_reauth,
        });
      }
      conditions.push(`mailbox_id = $${idx++}`);
      params.push(row.id);
    } else {
      // No identifiable email at all — empty list (no leakage from other users).
      return NextResponse.json({ messages: [], total: 0, limit, offset, needs_connect: true });
    }
  }

  // Exclude trashed messages
  conditions.push(`NOT (labels @> ARRAY['TRASH'])`);

  // Date range filter
  const dateRange = sp.get('date_range');
  if (dateRange === 'today') {
    conditions.push(`date_sent >= CURRENT_DATE`);
  } else if (dateRange === 'week') {
    conditions.push(`date_sent >= CURRENT_DATE - INTERVAL '7 days'`);
  } else if (dateRange === 'month') {
    conditions.push(`date_sent >= CURRENT_DATE - INTERVAL '30 days'`);
  } else if (dateRange === '3months') {
    conditions.push(`date_sent >= CURRENT_DATE - INTERVAL '90 days'`);
  }

  if (priority) {
    conditions.push(`(${priorityExpr}) = $${idx++}`);
    params.push(priority);
  }

  if (topic) {
    conditions.push(`$${idx++} = ANY(labels)`);
    params.push(topic);
  }

  if (from) {
    conditions.push(`from_address ILIKE $${idx++}`);
    params.push(`%${from}%`);
  }

  if (q) {
    conditions.push(`(
      to_tsvector('english', COALESCE(subject, '')) @@ plainto_tsquery('english', $${idx})
      OR body_text ILIKE $${idx + 1}
    )`);
    params.push(q, `%${q}%`);
    idx += 2;
  }

  if (isRead === 'true') {
    conditions.push(`is_read = true`);
  } else if (isRead === 'false') {
    conditions.push(`is_read = false`);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // Determine ORDER BY
  let orderBy: string;
  switch (sort) {
    case 'priority':
      orderBy = `(${priorityExpr}) ${sortDir}, date_sent DESC`;
      break;
    case 'from':
      orderBy = `from_address ${sortDir}, date_sent DESC`;
      break;
    default:
      orderBy = `date_sent ${sortDir}`;
  }

  // Get total count
  const countRes = await query(
    `SELECT COUNT(*) AS total FROM gmail_messages ${where}`,
    params,
  );
  const total = parseInt(countRes.rows[0]?.total || '0', 10);

  // Get messages (exclude body_html from list for performance)
  const messagesRes = await query(
    `SELECT
       id, gmail_id, thread_id, subject, from_address,
       to_addresses, cc_addresses, date_sent, snippet,
       LEFT(body_text, 200) AS body_preview,
       labels, is_read, attachments, created_at,
       assigned_user_id, assigned_at,
       (${priorityExpr}) AS priority
     FROM gmail_messages
     ${where}
     ORDER BY ${orderBy}
     LIMIT $${idx++} OFFSET $${idx++}`,
    [...params, limit, offset],
  );

  // Hydrate assigned-user info + read-by list for the returned page of messages.
  // Done in a second pass so the main query keeps its existing shape + index plan.
  const messageIds: string[] = messagesRes.rows.map((r: { id: string }) => r.id);
  let assigneesById: Record<string, { id: string; username: string; name: string } | undefined> = {};
  let readsByMessageId: Record<string, Array<{ user_id: string; username: string; name: string; read_at: string }>> = {};
  if (messageIds.length > 0) {
    const assigneeIds = messagesRes.rows
      .map((r: { assigned_user_id: string | null }) => r.assigned_user_id)
      .filter((v: string | null): v is string => !!v);
    if (assigneeIds.length > 0) {
      const aRes = await query<{ id: string; username: string; name: string }>(
        `SELECT id, username, COALESCE(display_name, username) AS name
           FROM tier_credentials WHERE id = ANY($1::uuid[])`,
        [assigneeIds],
      );
      assigneesById = Object.fromEntries(aRes.rows.map(r => [r.id, r]));
    }
    const rRes = await query<{
      message_id: string;
      user_id: string;
      username: string;
      name: string;
      read_at: string;
    }>(
      `SELECT r.message_id, r.user_id, tc.username,
              COALESCE(tc.display_name, tc.username) AS name, r.read_at
         FROM gmail_message_reads r
         JOIN tier_credentials tc ON tc.id = r.user_id
        WHERE r.message_id = ANY($1::uuid[])
        ORDER BY r.read_at ASC`,
      [messageIds],
    );
    for (const row of rRes.rows) {
      (readsByMessageId[row.message_id] ||= []).push({
        user_id: row.user_id,
        username: row.username,
        name: row.name,
        read_at: row.read_at,
      });
    }
  }
  for (const m of messagesRes.rows) {
    const a = m.assigned_user_id ? assigneesById[m.assigned_user_id] : undefined;
    m.assigned_username = a?.username || null;
    m.assigned_name     = a?.name || null;
    m.read_by           = readsByMessageId[m.id] || [];
  }

  // Get unique labels/topics for filter dropdown — scoped to the same mailbox
  // we filtered messages by (if any). Pulled out of the first WHERE param.
  const mailboxClauseIdx = params.findIndex((_, i) => conditions[i]?.startsWith('mailbox_id = '));
  const mbId = mailboxClauseIdx >= 0 ? (params[mailboxClauseIdx] as string) : null;
  const topicsRes = await query(
    `SELECT DISTINCT unnest(labels) AS topic
     FROM gmail_messages
     WHERE NOT (labels @> ARRAY['TRASH'])${mbId ? ` AND mailbox_id = $1` : ''}
     ORDER BY topic`,
    mbId ? [mbId] : [],
  );

  // Get unique senders for "From" dropdown (natalia only)
  const sendersRes = await query(
    `SELECT DISTINCT from_address
     FROM gmail_messages
     WHERE from_address IS NOT NULL AND from_address != ''${mbId ? ` AND mailbox_id = $1` : ''}
     ORDER BY from_address
     LIMIT 100`,
    mbId ? [mbId] : [],
  );

  return NextResponse.json({
    messages: messagesRes.rows,
    total,
    topics: topicsRes.rows.map((r) => (r as { topic: string }).topic),
    senders: sendersRes.rows.map((r) => (r as { from_address: string }).from_address),
  });
}