(대표님) 변경사항 20250707 10시 43분 - unstaged 변경사항 추가

1 files changed, 118 insertions, 0 deletions

diff --git a/lib/login-session/service.ts b/lib/login-session/service.ts
new file mode 100644
index 00000000..4fa35376
--- /dev/null
+++ b/lib/login-session/service.ts
@@ -0,0 +1,118 @@
+import db from "@/db/db"
+import { loginSessions, users } from "@/db/schema"
+import { and, or, ilike, eq, desc, asc, count, sql } from "drizzle-orm"
+import { filterColumns } from "@/lib/filter-columns";
+import type { GetLoginSessionsSchema, ExtendedLoginSession } from "./validation"
+
+export async function getLoginSessions(input: GetLoginSessionsSchema) {
+  try {
+    const offset = (input.page - 1) * input.perPage;
+    const advancedTable = true;
+
+    // 고급 필터 처리
+    const advancedWhere = filterColumns({
+      table: loginSessions,
+      filters: input.filters,
+      joinOperator: input.joinOperator,
+    });
+
+    // 전역 검색
+    let globalWhere;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(users.email, s),
+        ilike(users.name, s),
+        ilike(loginSessions.authMethod, s),
+        ilike(loginSessions.ipAddress, s)
+      );
+    }
+
+    // 조건 결합
+    const conditions = [];
+    if (advancedWhere) conditions.push(advancedWhere);
+    if (globalWhere) conditions.push(globalWhere);
+
+    let finalWhere;
+    if (conditions.length > 0) {
+      finalWhere = conditions.length > 1 ? and(...conditions) : conditions[0];
+    }
+
+    // 정렬 처리
+    const orderBy =
+      input.sort.length > 0
+        ? input.sort.map((item) => {
+            // 사용자 관련 필드 정렬
+            if (item.id === 'userEmail') {
+              return item.desc ? desc(users.email) : asc(users.email);
+            } else if (item.id === 'userName') {
+              return item.desc ? desc(users.name) : asc(users.name);
+            } else {
+              // 세션 필드 정렬
+              return item.desc
+                ? desc(loginSessions[item.id as keyof typeof loginSessions.$inferSelect])
+                : asc(loginSessions[item.id as keyof typeof loginSessions.$inferSelect]);
+            }
+          })
+        : [desc(loginSessions.loginAt)];
+
+    // 데이터 조회
+    const data = await db
+      .select({
+        id: loginSessions.id,
+        userId: loginSessions.userId,
+        loginAt: loginSessions.loginAt,
+        logoutAt: loginSessions.logoutAt,
+        lastActivityAt: loginSessions.lastActivityAt,
+        ipAddress: loginSessions.ipAddress,
+        userAgent: loginSessions.userAgent,
+        authMethod: loginSessions.authMethod,
+        isActive: loginSessions.isActive,
+        sessionExpiredAt: loginSessions.sessionExpiredAt,
+        createdAt: loginSessions.createdAt,
+        userEmail: users.email,
+        userName: users.name,
+        // 세션 지속 시간 계산 (분 단위)
+        sessionDuration: sql<number>`
+          CASE 
+            WHEN ${loginSessions.logoutAt} IS NOT NULL THEN
+              EXTRACT(EPOCH FROM (${loginSessions.logoutAt} - ${loginSessions.loginAt})) / 60
+            WHEN ${loginSessions.isActive} = true THEN
+              EXTRACT(EPOCH FROM (${loginSessions.lastActivityAt} - ${loginSessions.loginAt})) / 60
+            ELSE NULL
+          END
+        `,
+        // 현재 활성 여부
+        isCurrentlyActive: sql<boolean>`
+          CASE 
+            WHEN ${loginSessions.isActive} = true 
+              AND (${loginSessions.sessionExpiredAt} IS NULL 
+                   OR ${loginSessions.sessionExpiredAt} > NOW()) 
+            THEN true
+            ELSE false
+          END
+        `
+      })
+      .from(loginSessions)
+      .innerJoin(users, eq(loginSessions.userId, users.id))
+      .where(finalWhere)
+      .orderBy(...orderBy)
+      .limit(input.perPage)
+      .offset(offset);
+
+    // 총 개수 조회
+    const totalResult = await db
+      .select({ count: count() })
+      .from(loginSessions)
+      .innerJoin(users, eq(loginSessions.userId, users.id))
+      .where(finalWhere);
+
+    const total = totalResult[0]?.count || 0;
+    const pageCount = Math.ceil(total / input.perPage);
+
+    return { data: data as ExtendedLoginSession[], pageCount };
+  } catch (err) {
+    console.error("Failed to fetch login sessions:", err);
+    return { data: [], pageCount: 0 };
+  }
+}
\ No newline at end of file