1
import { and, asc, count, desc, eq, exists, gt, inArray, sql } from "drizzle-orm";
2
import { ADMIN_TEMP_INBOX_PAGE_SIZE } from "@/shared/contracts";
3
import { createDb, type Database } from "@/worker/db";
4
import { domains, emails, inboxes } from "@/worker/db/schema";
5
import { createLogger } from "@/worker/logger";
6
import { ttlHoursFromInbox } from "./shared";
7
 
8
const logger = createLogger("inbox-service");
9
 
10
export async function getInboxByAddress(env: Env, address: string, db?: Database) {
11
  const database = db ?? createDb(env.DB);
12
  return database.query.inboxes.findFirst({
13
    where: eq(inboxes.fullAddress, address),
14
  });
15
}
16
 
17
export async function listActiveDomains(env: Env, db?: Database) {
18
  const database = db ?? createDb(env.DB);
19
  return database.query.domains.findMany({
20
    where: eq(domains.isActive, true),
21
    orderBy: [asc(domains.domain)],
22
  });
23
}
24
 
25
export async function collectEmailIds(db: Database, inboxIds: string[]) {
26
  if (inboxIds.length === 0) {
27
    return [] as string[];
28
  }
29
 
30
  const emailRows = await db.select({ id: emails.id }).from(emails).where(inArray(emails.inboxId, inboxIds));
31
 
32
  return emailRows.map((row) => row.id);
33
}
34
 
35
export async function listActiveTemporaryInboxesForAdmin(
36
  env: Env,
37
  page: number,
38
  pageSize = ADMIN_TEMP_INBOX_PAGE_SIZE,
39
  db?: Database,
40
  hasEmails?: boolean,
41
) {
42
  const database = db ?? createDb(env.DB);
43
  const currentPage = Number.isFinite(page) && page >= 0 ? page : 0;
44
  const now = new Date();
45
 
46
  const whereCondition = and(
47
    eq(inboxes.isPermanent, false),
48
    gt(inboxes.expiresAt, now),
49
    hasEmails
50
      ? exists(
51
          database
52
            .select({ n: sql`1` })
53
            .from(emails)
54
            .where(eq(emails.inboxId, inboxes.id)),
55
        )
56
      : undefined,
57
  );
58
 
59
  const [items, totalRows] = await Promise.all([
60
    database.query.inboxes.findMany({
61
      where: whereCondition,
62
      orderBy: [desc(inboxes.createdAt)],
63
      limit: pageSize,
64
      offset: currentPage * pageSize,
65
    }),
66
    database.select({ total: count() }).from(inboxes).where(whereCondition),
67
  ]);
68
 
69
  const emailCounts = items.length
70
    ? await database
71
        .select({ inboxId: emails.inboxId, emailCount: count() })
72
        .from(emails)
73
        .where(
74
          inArray(
75
            emails.inboxId,
76
            items.map((item) => item.id),
77
          ),
78
        )
79
        .groupBy(emails.inboxId)
80
    : [];
81
 
82
  const emailCountByInboxId = new Map(emailCounts.map((row) => [row.inboxId, row.emailCount]));
83
 
84
  logger.info("admin_temp_inboxes_listed", "Listed active temporary inboxes for admin", {
85
    page: currentPage,
86
    pageSize,
87
    count: items.length,
88
  });
89
 
90
  return {
91
    page: currentPage,
92
    pageSize,
93
    total: totalRows[0]?.total ?? 0,
94
    items: items.map((item) => ({
95
      address: item.fullAddress,
96
      domain: item.domain,
97
      createdAt: item.createdAt,
98
      expiresAt: item.expiresAt,
99
      ttlHours: ttlHoursFromInbox(item),
100
      emailCount: emailCountByInboxId.get(item.id) ?? 0,
101
    })),
102
  };
103
}