1 files changed, 68 insertions, 0 deletions
diff --git a/lib/email-log/service.ts b/lib/email-log/service.ts
new file mode 100644
index 00000000..7eea6869
--- /dev/null
+++ b/lib/email-log/service.ts
@@ -0,0 +1,68 @@
+"use server";
+
+import db from "@/db/db";
+import { emailLogs } from "@/db/schema/emailLogs";
+import { and, asc, count, desc, ilike, or } from "drizzle-orm";
+import { type GetEmailLogSchema } from "./validations";
+import { getValidFilters } from "@/lib/data-table";
+
+export async function getEmailLogList(input: GetEmailLogSchema) {
+  const offset = (input.page - 1) * input.perPage;
+
+  const advancedWhere = getValidFilters(input.filters) // placeholder for future advanced filter handling
+
+  let globalWhere;
+  if (input.search) {
+    const s = `%${input.search}%`;
+    globalWhere = or(
+      ilike(emailLogs.from, s),
+      ilike(emailLogs.to, s),
+      ilike(emailLogs.cc, s),
+      ilike(emailLogs.subject, s),
+    );
+  }
+
+  const conditions = [] as any[];
+  if (advancedWhere && (advancedWhere as any).length !== 0) conditions.push(advancedWhere);
+  if (globalWhere) conditions.push(globalWhere);
+
+  let where: any;
+  if (conditions.length > 0) {
+    where = conditions.length > 1 ? and(...conditions) : conditions[0];
+  }
+
+  let orderBy;
+  try {
+    orderBy = input.sort.length > 0
+      ? input.sort
+          .map((item) => {
+            if (!item || !item.id || typeof item.id !== "string" || !(item.id in emailLogs)) return null;
+            const col = emailLogs[item.id as keyof typeof emailLogs];
+            return item.desc ? desc(col as any) : asc(col as any);
+          })
+          .filter((v): v is Exclude<typeof v, null> => v !== null)
+      : [desc(emailLogs.createdAt)];
+  } catch {
+    orderBy = [desc(emailLogs.createdAt)];
+  }
+
+  const data = await db
+    .select()
+    .from(emailLogs)
+    .where(where)
+    .orderBy(...orderBy as any)
+    .limit(input.perPage)
+    .offset(offset);
+
+  const totalResult = await db
+    .select({ count: count() })
+    .from(emailLogs)
+    .where(where);
+
+  const total = totalResult[0]?.count ?? 0;
+  const pageCount = Math.ceil(total / input.perPage);
+
+  return { data, pageCount };
+}
+
+