email/sidecar/routes.ts

import { mkdir } from 'node:fs/promises';
import { join } from 'node:path';
import nodemailer from 'nodemailer';
import type { EmailMessage } from '../db/types';
import { createRouter } from '@@/create-router';
import * as errors from '@@/custom-errors';
import { getEmailAttachmentCacheDir } from '@@/data-path';
import { getEmailAccounts } from '../db/queries';
import { openEmailDb, openUserEmailDb, rowToSummary, getSyncMeta, searchEmails } from './store';
import { accountsRouter } from './accounts';
import { openHolding, openScope, queryAcross, resolveMailScope } from './mail-scope';

export const emailRouter = createRouter();

emailRouter.route('/accounts', accountsRouter);

// ── Sending (SMTP) — sends as the connected account using its app password ──
/**
 * The mailbox a message goes out from.
 *
 * `accountParam` is the account id the composer names. It matters most for a REPLY: mail arrives on one
 * account, and answering it from another is not a cosmetic slip — it leaks which addresses belong to the
 * same person, and the thread breaks for the recipient. `emails.email_account` records where each message
 * landed, so the composer can name the right one and this can honour it.
 *
 * `all` is not a sender. It falls through to the default, because "send from every account" is not a thing
 * anybody means — and a unified compose with no account chosen has to pick one rather than refuse.
 */
async function getSmtpTransport(
  userId: number,
  accountParam?: string,
): Promise<{ transport: ReturnType<typeof nodemailer.createTransport>; from: string }> {
  const accounts = await getEmailAccounts(userId);
  const asked =
    accountParam && accountParam !== 'all' && Number.isFinite(Number(accountParam))
      ? accounts.find((a) => a.id === Number(accountParam))
      : undefined;
  const acct = asked ?? accounts.find((a) => a.enabled) ?? accounts[0];
  if (!acct) throw errors.BAD_REQUEST('No email account configured');
  if (accountParam && accountParam !== 'all' && !asked) {
    // Naming an account that is not yours must not quietly send as you. Refusing is the only safe answer.
    throw errors.BAD_REQUEST('That mail account does not exist on this login');
  }
  const pass = (acct.credentials as Record<string, unknown> | null)?.password as string | undefined;
  if (!pass) throw errors.BAD_REQUEST('This account has no SMTP password — sending needs an app-password account');
  // Gmail: smtp.gmail.com:465 (SSL). Derive from the IMAP host for other providers.
  const host = acct.provider === 'gmail' ? 'smtp.gmail.com' : acct.imapHost.replace(/^imap\./, 'smtp.');
  const transport = nodemailer.createTransport({ host, port: 465, secure: true, auth: { user: acct.email, pass } });
  return { transport, from: acct.email };
}

// POST /send — compose/reply (multipart; `files` are attachments). Gmail auto-files the sent copy in
// "Sent", so IMAP sync picks it up.
emailRouter.post('/send', async (ctx) => {
  const user = ctx.get('user');
  const form = await ctx.req.parseBody({ all: true });
  const str = (v: unknown) => (typeof v === 'string' ? v : '');
  const to = str(form.to).trim();
  if (!to) throw errors.BAD_REQUEST('At least one recipient is required');

  const toFiles = (raw: unknown) =>
    (Array.isArray(raw) ? raw : raw ? [raw] : []).filter((f): f is File => f instanceof File);
  const attachments = await Promise.all(
    toFiles(form.files).map(async (f) => ({
      filename: f.name,
      content: Buffer.from(await f.arrayBuffer()),
      contentType: f.type || undefined,
    })),
  );
  // Inline images: cid `inline-<i>` matches the `<img src="cid:inline-i">` the composer put in the html.
  const inline = await Promise.all(
    toFiles(form.inline).map(async (f, i) => ({
      filename: f.name,
      content: Buffer.from(await f.arrayBuffer()),
      contentType: f.type || undefined,
      cid: `inline-${i}`,
      contentDisposition: 'inline' as const,
    })),
  );
  const allAttachments = [...attachments, ...inline];

  const html = str(form.html).trim();
  const inReplyTo = str(form.inReplyTo).trim();
  // The composer names the account; a reply names the one the original arrived on.
  const { transport, from } = await getSmtpTransport(
    user.id,
    typeof form.account === 'string' ? form.account : ctx.req.query('account'),
  );
  await transport.sendMail({
    from,
    to,
    cc: str(form.cc).trim() || undefined,
    bcc: str(form.bcc).trim() || undefined,
    subject: str(form.subject).trim() || '(no subject)',
    text: str(form.body),
    ...(html ? { html } : {}),
    ...(allAttachments.length ? { attachments: allAttachments } : {}),
    ...(inReplyTo ? { headers: { 'In-Reply-To': inReplyTo, References: inReplyTo } } : {}),
  });
  return ctx.json({ ok: true });
});

// GET /contacts?q= — address autocomplete from people you've received mail from, ranked by frequency.
emailRouter.get('/contacts', async (ctx) => {
  const user = ctx.get('user');
  const like = `%${(ctx.req.query('q') ?? '').trim().toLowerCase()}%`;
  const db = await openUserEmailDb(user.email, user.id, ctx.req.query('account'));
  if (!db) return ctx.json([]);
  try {
    const rows = db
      .query(
        `SELECT lower(from_address) AS address, from_name AS name, count(*) AS c
         FROM emails
         WHERE from_address IS NOT NULL AND from_address != '' AND deleted = 0
           AND (lower(from_address) LIKE ? OR lower(from_name) LIKE ?)
         GROUP BY lower(from_address)
         ORDER BY c DESC LIMIT 10`,
      )
      .all(like, like) as Array<{ address: string; name: string | null }>;
    return ctx.json(rows.map((r) => ({ address: r.address, name: r.name || '' })));
  } finally {
    db.close();
  }
});

// ── Real-time: per-user SSE stream of email events (fed by the IMAP IDLE watcher) ──
const emailSseClients = new Map<string, Set<ReadableStreamDefaultController<Uint8Array>>>();

// Called from the sidecar-message handler when the IDLE watcher saves new mail for a user.
export function broadcastEmailNew(userEmail: string): void {
  const set = emailSseClients.get(userEmail);
  if (!set || set.size === 0) return;
  const payload = new TextEncoder().encode(`data: ${JSON.stringify({ type: 'new-mail' })}\n\n`);
  for (const ctrl of set) {
    try {
      ctrl.enqueue(payload);
    } catch {
      /* dead controller — cleaned up on cancel */
    }
  }
}

// EventSource endpoint (auth via ?token= handled by userMiddleware). The frontend opens this only
// while on /email, so events stop the moment you navigate away.
emailRouter.get('/events', (ctx) => {
  const email = ctx.get('user').email;
  const enc = new TextEncoder();
  let controllerRef: ReadableStreamDefaultController<Uint8Array>;
  let ping: ReturnType<typeof setInterval>;
  const stream = new ReadableStream<Uint8Array>({
    start(controller) {
      controllerRef = controller;
      let set = emailSseClients.get(email);
      if (!set) {
        set = new Set();
        emailSseClients.set(email, set);
      }
      set.add(controller);
      controller.enqueue(enc.encode('retry: 3000\n\n'));
      ping = setInterval(() => {
        try {
          controller.enqueue(enc.encode(': ping\n\n'));
        } catch {
          /* closed */
        }
      }, 25_000);
    },
    cancel() {
      clearInterval(ping);
      const set = emailSseClients.get(email);
      set?.delete(controllerRef);
      if (set && set.size === 0) emailSseClients.delete(email);
    },
  });
  return new Response(stream, {
    headers: { 'Content-Type': 'text/event-stream', 'Cache-Control': 'no-cache', Connection: 'keep-alive' },
  });
});

// Full-text search across all mail (subject / sender / recipients / snippet / body), newest first.
emailRouter.get('/search', async (ctx) => {
  const user = ctx.get('user');
  const q = (ctx.req.query('q') ?? '').trim();
  const page = Math.max(1, Number(ctx.req.query('page') ?? '1') || 1);
  const limit = Math.min(100, Math.max(1, Number(ctx.req.query('limit') ?? '50') || 50));
  const offset = (page - 1) * limit;
  if (!q) return ctx.json({ messages: [], total: 0 });

  const scope = await resolveMailScope(user.id, ctx.req.query('account'));
  const opened = openScope(user.email, scope);
  if (!opened.length) return ctx.json({ messages: [], total: 0 });
  try {
    // `searchEmails` is reused verbatim, per mailbox. It has to be: `emails_fts` is a per-database FTS5
    // virtual table and `MATCH` cannot be aimed across an ATTACH boundary, so a unified search is a merge
    // of local searches rather than one query. The upside is that the operator parser, the OR branching
    // and every FTS detail stay in one place and are not reimplemented for the unified case.
    let total = 0;
    const merged = queryAcross(
      opened,
      (db, take) => {
        const { rows, total: t } = searchEmails(db, q, take, 0);
        total += t;
        return rows as unknown as Array<Record<string, unknown>>;
      },
      { sortBy: (row) => String(row.date ?? ''), limit, offset },
    );
    const messages = merged.map((row) => ({
      ...rowToSummary(row),
      account: row.accountEmail,
      accountId: row.accountId,
    }));
    return ctx.json({ messages, total });
  } finally {
    for (const { db } of opened) db.close();
  }
});

// `folder` is request input and was being interpolated straight into the SQL. It is bound now — the
// fragment and its parameters travel together because each call site builds several statements from the
// same fragment and has to spread the params in the right order.
type FolderFilter = { where: string; params: string[] };
const folderFilter = (folder: string): FolderFilter =>
  folder === 'all'
    ? { where: 'deleted = 0', params: [] }
    : { where: 'deleted = 0 AND labels LIKE ?', params: [`%${folder}%`] };

emailRouter.get('/messages', async (ctx) => {
  const user = ctx.get('user');
  // `|| n` also catches NaN from a non-numeric query param, which used to reach the bindings as NaN.
  const page = Math.max(Number(ctx.req.query('page') ?? '1') || 1, 1);
  const limit = Math.max(Number(ctx.req.query('limit') ?? '50') || 50, 1);
  const folder = ctx.req.query('folder') ?? 'inbox';
  const offset = (page - 1) * limit;

  const { where: folderWhere, params: folderParams } = folderFilter(folder);

  const scope = await resolveMailScope(user.id, ctx.req.query('account'));
  const opened = openScope(user.email, scope);
  if (!opened.length) return ctx.json({ messages: [], total: 0 });
  try {
    // One row per conversation: the latest message in each thread within this folder, plus the
    // thread's message count and how many are unread. COALESCE guards any un-backfilled rows.
    //
    // Unchanged from the single-account version, and run per mailbox rather than rewritten to span them.
    // Threading is per account by definition — a conversation does not continue across two mailboxes — so
    // collapsing threads locally and merging the collapsed rows is both cheaper and more correct than any
    // cross-database window function would be.
    const listOne = (db: (typeof opened)[number]['db'], take: number) =>
      db
        .query(
          `SELECT * FROM (
             SELECT e.*,
               COUNT(*) OVER (PARTITION BY COALESCE(thread_id, id)) AS thread_count,
               SUM(CASE WHEN read = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY COALESCE(thread_id, id)) AS thread_unread,
               ROW_NUMBER() OVER (PARTITION BY COALESCE(thread_id, id) ORDER BY date DESC, id DESC) AS rn
             FROM emails e
             WHERE ${folderWhere}
           ) WHERE rn = 1
           ORDER BY date DESC LIMIT ?`,
        )
        .all(...folderParams, take) as Record<string, unknown>[];

    const countOne = (db: (typeof opened)[number]['db']) =>
      (
        db
          .query(`SELECT COUNT(DISTINCT COALESCE(thread_id, id)) as total FROM emails WHERE ${folderWhere}`)
          .get(...folderParams) as { total: number }
      ).total;

    const merged = queryAcross(opened, listOne, {
      sortBy: (row) => String(row.date ?? ''),
      limit,
      offset,
    });
    const total = opened.reduce((sum, entry) => sum + countOne(entry.db), 0);

    // `account` rides on every row so a unified list can label who it arrived as, and so a reply can go
    // out from the right mailbox without a second lookup.
    const messages = merged.map((row) => ({
      ...rowToSummary(row),
      account: row.accountEmail,
      accountId: row.accountId,
    }));
    return ctx.json({ messages, total });
  } finally {
    for (const { db } of opened) db.close();
  }
});

function buildMessage(db: ReturnType<typeof openEmailDb>, row: Record<string, unknown>): EmailMessage {
  const attachmentRows = db
    .query('SELECT * FROM attachments WHERE email_id = ? ORDER BY idx')
    .all(row.id as string) as Array<Record<string, unknown>>;
  const from = row.from_name ? `${row.from_name} <${row.from_address}>` : (row.from_address as string);
  return {
    id: row.id as string,
    from,
    to: row.to_address as string,
    cc: (row.cc as string) ?? undefined,
    subject: row.subject as string,
    date: row.date as string,
    snippet: row.snippet as string,
    html: (row.html as string) ?? undefined,
    text: (row.text_body as string) ?? undefined,
    attachments: attachmentRows.map((a) => ({
      filename: a.filename as string,
      size: a.size as number,
      contentType: a.content_type as string,
    })),
    ...(row.attachment_count ? { attachmentCount: row.attachment_count as number } : {}),
    ...(row.read ? { read: true } : {}),
    ...(row.labels ? { labels: (row.labels as string).split(',') } : {}),
  };
}

emailRouter.get('/messages/:id', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.text('Not found', 404);
  try {
    const row = db.query('SELECT * FROM emails WHERE id = ? AND deleted = 0').get(id) as Record<string, unknown> | null;
    if (!row) return ctx.text('Not found', 404);
    return ctx.json(buildMessage(db, row));
  } finally {
    db.close();
  }
});

// GET /thread/:id — the full conversation containing message :id, oldest message first.
emailRouter.get('/thread/:id', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.text('Not found', 404);
  try {
    const head = db.query('SELECT thread_id, subject FROM emails WHERE id = ? AND deleted = 0').get(id) as {
      thread_id: string | null;
      subject: string;
    } | null;
    if (!head) return ctx.text('Not found', 404);

    const threadKey = head.thread_id ?? id;
    const rows = db
      .query('SELECT * FROM emails WHERE COALESCE(thread_id, id) = ? AND deleted = 0 ORDER BY date ASC')
      .all(threadKey) as Record<string, unknown>[];
    const messages = rows.map((row) => buildMessage(db, row));
    return ctx.json({ id, subject: head.subject, messages });
  } finally {
    db.close();
  }
});

// PATCH /thread/:id/read — mark every message in the conversation as read.
emailRouter.patch('/thread/:id/read', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.text('Not found', 404);
  try {
    const head = db.query('SELECT thread_id FROM emails WHERE id = ?').get(id) as { thread_id: string | null } | null;
    if (!head) return ctx.text('Not found', 404);
    const threadKey = head.thread_id ?? id;
    db.run('UPDATE emails SET read = 1 WHERE COALESCE(thread_id, id) = ? AND read = 0', [threadKey]);
    return ctx.json({ ok: true });
  } finally {
    db.close();
  }
});

emailRouter.post('/messages/:id/attachments/:index/extract', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');
  const index = Number(ctx.req.param('index'));

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.text('Attachment not found', 404);
  try {
    const row = db.query('SELECT filename, content FROM attachments WHERE email_id = ? AND idx = ?').get(id, index) as {
      filename: string;
      content: string | null;
    } | null;
    if (!row || !row.content) return ctx.text('Attachment not found', 404);

    const fileName = row.filename ?? 'unknown';
    const attachDir = getEmailAttachmentCacheDir(user.email);
    const destPath = join(attachDir, fileName);

    const destFile = Bun.file(destPath);
    if (!(await destFile.exists())) {
      await mkdir(attachDir, { recursive: true });
      const binary = Buffer.from(row.content, 'base64');
      await Bun.write(destPath, binary);
    }

    return ctx.json({ filePath: `email_accounts/attachment_cache/${fileName}`, fileName, root: 'user-data' });
  } catch {
    return ctx.text('Failed to extract attachment', 500);
  } finally {
    db.close();
  }
});

emailRouter.patch('/messages/:id/read', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.json({ ok: true });
  try {
    db.run('UPDATE emails SET read = 1 WHERE id = ? AND read = 0', [id]);
    return ctx.json({ ok: true });
  } finally {
    db.close();
  }
});

emailRouter.delete('/messages/:id', async (ctx) => {
  const user = ctx.get('user');
  const id = ctx.req.param('id');

  // Which mailbox holds it. Addressed by id, so the account cannot be assumed: a unified list returns ids
  // from every mailbox, and opening the first would 404 on mail that is plainly on screen.
  const holder = await openHolding(user.email, user.id, ctx.req.query('account'), id);
  const db = holder;
  if (!db) return ctx.text('Not found', 404);
  try {
    const result = db.run('UPDATE emails SET deleted = 1 WHERE id = ? AND deleted = 0', [id]);
    if (result.changes === 0) return ctx.text('Not found', 404);
    return ctx.json({ ok: true });
  } finally {
    db.close();
  }
});

emailRouter.get('/sync-status', async (ctx) => {
  const user = ctx.get('user');

  const scope = await resolveMailScope(user.id, ctx.req.query('account'));
  const opened = openScope(user.email, scope);
  if (!opened.length) return ctx.json({ lastSyncAt: null });
  try {
    // Across mailboxes the honest answer is the OLDEST, not the newest. "Synced as of" is a claim about
    // everything on screen, and it stops being true at the laggard — reporting the freshest would say the
    // unified inbox is current while one account has not been reached for a day.
    const stamps = opened.map(({ db }) => getSyncMeta(db, 'last_sync_at')).filter((v): v is string => !!v);
    const lastSyncAt = stamps.length === opened.length && stamps.length ? stamps.sort()[0] : null;
    return ctx.json({ lastSyncAt });
  } finally {
    for (const { db } of opened) db.close();
  }
});

emailRouter.get('/stats', async (ctx) => {
  const user = ctx.get('user');
  const folder = ctx.req.query('folder') ?? 'inbox';

  const { where: folderWhere, params: folderParams } = folderFilter(folder);

  const db = await openUserEmailDb(user.email, user.id, ctx.req.query('account'));
  if (!db) return ctx.json({ total: 0, byDomain: [], bySender: [] });
  try {
    const total = (
      db.query(`SELECT COUNT(*) as count FROM emails WHERE ${folderWhere}`).get(...folderParams) as { count: number }
    ).count;
    const byDomain = db
      .query(
        `SELECT from_domain, COUNT(*) as count FROM emails WHERE ${folderWhere} GROUP BY from_domain ORDER BY count DESC LIMIT 20`,
      )
      .all(...folderParams) as Array<{ from_domain: string; count: number }>;
    const bySender = db
      .query(
        `SELECT from_address, from_name, COUNT(*) as count FROM emails WHERE ${folderWhere} GROUP BY from_address ORDER BY count DESC LIMIT 20`,
      )
      .all(...folderParams) as Array<{ from_address: string; from_name: string; count: number }>;

    return ctx.json({ total, byDomain, bySender });
  } finally {
    db.close();
  }
});

emailRouter.get('/labels', async (ctx) => {
  const user = ctx.get('user');

  const db = await openUserEmailDb(user.email, user.id, ctx.req.query('account'));
  if (!db) return ctx.json({ labels: [] });
  try {
    const rows = db.query('SELECT labels FROM emails WHERE deleted = 0 AND labels IS NOT NULL').all() as Array<{
      labels: string;
    }>;

    const counts = new Map<string, number>();
    for (const row of rows) {
      for (const label of row.labels.split(',')) {
        const trimmed = label.trim().toLowerCase();
        if (trimmed) counts.set(trimmed, (counts.get(trimmed) ?? 0) + 1);
      }
    }

    const labels = [...counts.entries()].map(([label, count]) => ({ label, count })).sort((a, b) => b.count - a.count);

    return ctx.json({ labels });
  } finally {
    db.close();
  }
});