email/db/queries.ts

import { eq, and, inArray } from 'drizzle-orm';
import { db } from 'officerdb/db';
import { encryptSecret, decryptSecret } from 'officerdb/crypto';
import { emailAccounts } from './schema';
import type { EmailAccount, EmailAccountCreate, EmailAccountSelect } from './types';

/*
  The encryption boundary is this file, and nothing above it.

  `email_accounts.credentials` holds an IMAP app password or an OAuth pair. In the platform it was plaintext
  jsonb — the SMTP transport read the password straight out of the row — and the extraction was the moment to
  stop that, because the platform now has a secret store with a key per purpose and a plugin is expected to
  use it rather than invent its own.

  `'email'` is this plugin's purpose. `getKey` creates it on first use, so a core install carries no email
  key until this plugin asks for one, and a leaked key opens this column rather than every plugin's.

  Encrypt on the way in, decrypt on the way out, in one place: callers keep the object they always had, and
  a ciphertext string cannot escape into the rest of the plugin by accident. There is deliberately NO
  plaintext fallback on read — a value that does not verify is an error, not something to shrug at.
*/
const PURPOSE = 'email';

const seal = (credentials: Record<string, unknown>): string => encryptSecret(PURPOSE, JSON.stringify(credentials));

const open = (row: EmailAccountSelect): EmailAccount => ({
  ...row,
  credentials: JSON.parse(decryptSecret(PURPOSE, row.credentials)) as Record<string, unknown>,
});

export async function getEmailAccounts(userId: number): Promise<EmailAccount[]> {
  const rows = await db.select().from(emailAccounts).where(eq(emailAccounts.userId, userId));
  return rows.map(open);
}

export async function getEmailAccount(id: number): Promise<EmailAccount | undefined> {
  const [row] = await db.select().from(emailAccounts).where(eq(emailAccounts.id, id));
  return row ? open(row) : undefined;
}

export async function createEmailAccount(params: EmailAccountCreate): Promise<EmailAccount> {
  const [row] = await db
    .insert(emailAccounts)
    .values({ ...params, credentials: seal(params.credentials) })
    .returning();
  return open(row!);
}

export async function deleteEmailAccount(id: number, userId: number): Promise<boolean> {
  const [row] = await db
    .delete(emailAccounts)
    .where(and(eq(emailAccounts.id, id), eq(emailAccounts.userId, userId)))
    .returning({ id: emailAccounts.id });
  return !!row;
}

export async function updateEmailAccountStatus(id: number, status: string): Promise<void> {
  await db.update(emailAccounts).set({ status }).where(eq(emailAccounts.id, id));
}

export async function updateEmailAccountSyncMeta(id: number, syncMeta: Record<string, unknown>): Promise<void> {
  await db.update(emailAccounts).set({ syncMeta }).where(eq(emailAccounts.id, id));
}

/** Every account due for a background sync, across all users — the cron and IDLE watchers' entry point. */
export async function getAllSyncedAccounts(): Promise<EmailAccount[]> {
  const rows = await db
    .select()
    .from(emailAccounts)
    .where(and(eq(emailAccounts.status, 'synced'), eq(emailAccounts.enabled, true)));
  return rows.map(open);
}

/**
 * Clear a `syncing` or `queued` status left behind by a process that died mid-sync.
 *
 * `getAllSyncedAccounts` only picks up `status = 'synced'`, so an account stranded at either — the
 * resync route sets `syncing` before awaiting and only clears it after; a first sync sets `queued`
 * before handing off to the in-process job runner — silently drops out of both the cron and the IDLE
 * watcher forever. A fresh process can't have either genuinely in flight (the job runner is in-memory,
 * not persisted), so on startup that state only ever means "orphaned by the last restart." `performResync`
 * — what the cron and IDLE watcher call — already handles an account with no prior sync state by doing a
 * full fetch, so resetting to `synced` rather than replaying the dedicated first-sync job is safe.
 */
export async function resetStuckSyncingAccounts(): Promise<void> {
  await db
    .update(emailAccounts)
    .set({ status: 'synced' })
    .where(inArray(emailAccounts.status, ['syncing', 'queued']));
}

/** Replace an account's stored secret — used when an OAuth token is refreshed. */
export async function updateEmailAccountCredentials(id: number, credentials: Record<string, unknown>): Promise<void> {
  await db.update(emailAccounts).set({ credentials: seal(credentials) }).where(eq(emailAccounts.id, id));
}