`app/api/gmail/sync-status/route.ts`

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

/**
 * GET /api/gmail/sync-status
 *
 * Returns sync status information for the dashboard widget:
 * - Last sync time
 * - Messages synced since last sync
 * - Sync health indicator (green/yellow/red)
 * - Recent sync log entries
 * - needs_reauth flag
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    // Get primary mailbox status
    const mbRes = await query(
      `SELECT m.id, m.email, m.last_synced_at, m.needs_reauth, m.is_active,
              (SELECT count(*) FROM gmail_messages WHERE mailbox_id = m.id) AS total_messages,
              (SELECT count(*) FROM gmail_messages WHERE mailbox_id = m.id AND created_at > COALESCE(m.last_synced_at - INTERVAL '30 minutes', m.last_synced_at)) AS recent_messages
       FROM mailboxes m
       WHERE m.email = 'natalia@studentdebtcrisis.org'`,
    );

    if (mbRes.rows.length === 0) {
      return NextResponse.json({
        connected: false,
        lastSyncedAt: null,
        health: 'red',
        totalMessages: 0,
        recentMessages: 0,
        needsReauth: false,
        recentLogs: [],
      });
    }

    const mailbox = mbRes.rows[0];
    const lastSynced = mailbox.last_synced_at;

    // Compute health indicator
    let health: 'green' | 'yellow' | 'red' = 'red';
    if (lastSynced) {
      const minutesAgo = (Date.now() - new Date(lastSynced).getTime()) / 60000;
      if (minutesAgo <= 30) {
        health = 'green';
      } else if (minutesAgo <= 60) {
        health = 'yellow';
      } else {
        health = 'red';
      }
    }

    // Get recent sync logs
    const logsRes = await query(
      `SELECT id, started_at, completed_at, messages_synced, error, status
       FROM gmail_sync_log
       WHERE mailbox_id = $1
       ORDER BY started_at DESC
       LIMIT 5`,
      [mailbox.id],
    );

    // Messages since last sync
    let messagesSinceLastSync = 0;
    if (lastSynced) {
      const sinceRes = await query(
        `SELECT count(*) AS cnt FROM gmail_messages
         WHERE mailbox_id = $1 AND created_at >= $2`,
        [mailbox.id, lastSynced],
      );
      messagesSinceLastSync = parseInt(sinceRes.rows[0]?.cnt || '0', 10);
    }

    return NextResponse.json({
      connected: true,
      lastSyncedAt: lastSynced,
      health,
      totalMessages: parseInt(mailbox.total_messages, 10) || 0,
      messagesSinceLastSync,
      needsReauth: mailbox.needs_reauth || false,
      isActive: mailbox.is_active,
      recentLogs: logsRes.rows,
    });
  } catch (err) {
    console.error('[gmail/sync-status] Error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Failed to get sync status' },
      { status: 500 },
    );
  }
}