import db from "@/db/db"
import { loginSessions, pageVisits, users } from "@/db/schema"
import { and, or, ilike, eq, desc, asc, count, sql, isNull } from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns";
import type { GetPageVisitsSchema, ExtendedPageVisit } from "./validation"

export async function getPageVisits(input: GetPageVisitsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;
    const advancedTable = true;

    // 고급 필터 처리
    const advancedWhere = filterColumns({
      table: pageVisits,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 전역 검색
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(pageVisits.route, s),
        ilike(pageVisits.pageTitle, s),
        ilike(pageVisits.referrer, s),
        ilike(pageVisits.deviceType, s),
        ilike(pageVisits.browserName, s),
        ilike(pageVisits.osName, s),
        ilike(users.email, s),
        ilike(users.name, 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(pageVisits[item.id as keyof typeof pageVisits.$inferSelect])
                : asc(pageVisits[item.id as keyof typeof pageVisits.$inferSelect]);
            }
          })
        : [desc(pageVisits.visitedAt)];

    // 데이터 조회
    const data = await db
      .select({
        id: pageVisits.id,
        userId: pageVisits.userId,
        sessionId: pageVisits.sessionId,
        route: pageVisits.route,
        pageTitle: pageVisits.pageTitle,
        referrer: pageVisits.referrer,
        ipAddress: pageVisits.ipAddress,
        userAgent: pageVisits.userAgent,
        visitedAt: pageVisits.visitedAt,
        duration: pageVisits.duration,
        queryParams: pageVisits.queryParams,
        deviceType: pageVisits.deviceType,
        browserName: pageVisits.browserName,
        osName: pageVisits.osName,
        userEmail: users.email,
        userName: users.name,
        // 지속 시간 포맷팅
        durationFormatted: sql<string>`
          CASE 
            WHEN ${pageVisits.duration} IS NULL THEN NULL
            WHEN ${pageVisits.duration} < 60 THEN CONCAT(${pageVisits.duration}, '초')
            WHEN ${pageVisits.duration} < 3600 THEN CONCAT(FLOOR(${pageVisits.duration} / 60), '분 ', ${pageVisits.duration} % 60, '초')
            ELSE CONCAT(FLOOR(${pageVisits.duration} / 3600), '시간 ', FLOOR((${pageVisits.duration} % 3600) / 60), '분')
          END
        `,
        // 장기 체류 여부 (5분 이상)
        isLongVisit: sql<boolean>`${pageVisits.duration} >= 300`
      })
      .from(pageVisits)
      .leftJoin(users, eq(pageVisits.userId, users.id))
      .where(finalWhere)
      .orderBy(...orderBy)
      .limit(input.perPage)
      .offset(offset);

    // 총 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(pageVisits)
      .leftJoin(users, eq(pageVisits.userId, users.id))
      .where(finalWhere);

    const total = totalResult[0]?.count || 0;
    const pageCount = Math.ceil(total / input.perPage);

    return { data: data as ExtendedPageVisit[], pageCount };
  } catch (err) {
    console.error("Failed to fetch page visits:", err);
    return { data: [], pageCount: 0 };
  }
}


export async function getUserActivitySummary(userId: string, startDate: Date, endDate: Date) {
  try {
    // 페이지 방문 통계
    const pageStats = await db
      .select({
        route: pageVisits.route,
        visitCount: count(),
        totalDuration: sql<number>`SUM(${pageVisits.duration})`,
        avgDuration: sql<number>`AVG(${pageVisits.duration})`,
      })
      .from(pageVisits)
      .where(
        and(
          eq(pageVisits.userId, userId),
          between(pageVisits.visitedAt, startDate, endDate)
        )
      )
      .groupBy(pageVisits.route)
      .orderBy(desc(count()));

    // 세션 통계
    const sessionStats = await db
      .select({
        sessionCount: count(),
        totalSessionTime: sql<number>`
          SUM(EXTRACT(EPOCH FROM (
            COALESCE(${loginSessions.logoutAt}, ${loginSessions.lastActivityAt}) 
            - ${loginSessions.loginAt}
          )) / 60)
        `,
      })
      .from(loginSessions)
      .where(
        and(
          eq(loginSessions.userId, userId),
          between(loginSessions.loginAt, startDate, endDate)
        )
      );

    return {
      pageStats,
      sessionStats: sessionStats[0] || { sessionCount: 0, totalSessionTime: 0 },
    };
  } catch (error) {
    console.error("Failed to get user activity summary:", error);
    return {
      pageStats: [],
      sessionStats: { sessionCount: 0, totalSessionTime: 0 },
    };
  }
}