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 };
  }
}