← back to Norma

app/api/gmail/sync/route.ts

145 lines

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

/**
 * POST /api/gmail/sync — Sync Gmail messages into gmail_messages table
 * Body: { maxResults?: number, afterDate?: string }
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const gmail = await getGmailClient();
  if (!gmail) {
    return NextResponse.json({ error: 'Gmail not connected' }, { status: 400 });
  }

  const body = await request.json().catch(() => ({}));
  const maxResults = Math.min(body.maxResults || 50, 200);

  // Get or create the mailbox
  const mbRes = await query(
    `SELECT id, sync_cursor FROM mailboxes WHERE email = 'natalia@studentdebtcrisis.org'`,
  );
  if (mbRes.rows.length === 0) {
    return NextResponse.json({ error: 'Mailbox not found' }, { status: 400 });
  }
  const mailboxId = mbRes.rows[0].id;
  const cursor = mbRes.rows[0].sync_cursor;

  // Build Gmail query
  let q = '';
  const afterDate = body.afterDate || cursor;
  if (afterDate) {
    q = `after:${afterDate.replace(/-/g, '/')}`;
  }

  try {
    let fetched = 0;
    let inserted = 0;
    let skipped = 0;
    let pageToken: string | undefined;
    let newestDate: string | null = null;

    // Page through messages
    do {
      const list = await gmail.users.messages.list({
        userId: 'me',
        maxResults: Math.min(maxResults - fetched, 50),
        q: q || undefined,
        pageToken,
      });

      const messageIds = list.data.messages || [];
      if (messageIds.length === 0) break;

      pageToken = list.data.nextPageToken || undefined;

      for (const stub of messageIds) {
        if (fetched >= maxResults) break;
        fetched++;

        try {
          const detail = await gmail.users.messages.get({
            userId: 'me',
            id: stub.id!,
            format: 'full',
          });

          const headers = detail.data.payload?.headers;
          const bodyText = decodeBody(detail.data.payload!);
          const bodyHtml = decodeHtmlBody(detail.data.payload!);
          const dateSent = getHeader(headers, 'Date');
          const toRaw = getHeader(headers, 'To');
          const ccRaw = getHeader(headers, 'Cc');

          // Track newest for cursor
          if (detail.data.internalDate && !newestDate) {
            const ms = parseInt(detail.data.internalDate, 10);
            if (!isNaN(ms)) {
              newestDate = new Date(ms).toISOString().split('T')[0];
            }
          }

          const result = await query(
            `INSERT INTO gmail_messages
               (mailbox_id, gmail_id, thread_id, subject, from_address, to_addresses,
                cc_addresses, date_sent, snippet, body_text, body_html, labels, attachments, is_read)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
             ON CONFLICT (mailbox_id, gmail_id) DO UPDATE SET
               body_text = COALESCE(EXCLUDED.body_text, gmail_messages.body_text),
               body_html = COALESCE(EXCLUDED.body_html, gmail_messages.body_html)
             RETURNING id`,
            [
              mailboxId,
              detail.data.id,
              detail.data.threadId,
              getHeader(headers, 'Subject'),
              getHeader(headers, 'From'),
              toRaw ? toRaw.split(',').map(a => a.trim()) : [],
              ccRaw ? ccRaw.split(',').map(a => a.trim()) : [],
              dateSent ? new Date(dateSent) : null,
              detail.data.snippet,
              bodyText || null,
              bodyHtml || null,
              detail.data.labelIds || [],
              JSON.stringify([]),
              !(detail.data.labelIds || []).includes('UNREAD'),
            ],
          );

          if (result.rowCount && result.rowCount > 0) {
            inserted++;
          } else {
            skipped++;
          }
        } catch (err) {
          console.error(`[gmail-sync] Failed message ${stub.id}:`, (err as Error).message);
        }
      }

      if (fetched >= maxResults) break;
    } while (pageToken);

    // Update cursor
    if (newestDate) {
      await query(
        `UPDATE mailboxes SET last_synced_at = now(), sync_cursor = $2, updated_at = now() WHERE id = $1`,
        [mailboxId, newestDate],
      );
    } else {
      await query(
        `UPDATE mailboxes SET last_synced_at = now(), updated_at = now() WHERE id = $1`,
        [mailboxId],
      );
    }

    return NextResponse.json({ fetched, inserted, skipped, newCursor: newestDate });
  } catch (err) {
    console.error('[gmail-sync] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}