import { asc, desc, eq, ilike, and, count, sql } from "drizzle-orm"
import db from "@/db/db"
import { pageInformation, type PageInformation, type NewPageInformation } from "@/db/schema/information"
import type { GetInformationSchema } from "./validations"
import { PgTransaction } from "drizzle-orm/pg-core"

// 최신 패턴: 트랜잭션을 지원하는 인포메이션 조회
export async function selectInformationLists(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: ReturnType<typeof and>
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]
    offset?: number
    limit?: number
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params

  return tx
    .select()
    .from(pageInformation)
    .where(where)
    .orderBy(...(orderBy ?? [desc(pageInformation.createdAt)]))
    .offset(offset)
    .limit(limit)
}

// 최신 패턴: 트랜잭션을 지원하는 카운트 조회
export async function countInformationLists(
  tx: PgTransaction<any, any, any>,
  where?: ReturnType<typeof and>
) {
  const res = await tx
    .select({ count: count() })
    .from(pageInformation)
    .where(where)
  
  return res[0]?.count ?? 0
}

// 기존 패턴 (하위 호환성을 위해 유지)
export async function selectInformation(input: GetInformationSchema) {
  const { page, per_page = 50, sort, pagePath, isActive, from, to } = input

  const conditions = []

  if (pagePath) {
    conditions.push(ilike(pageInformation.pagePath, `%${pagePath}%`))
  }

  if (isActive !== null && isActive !== undefined) {
    conditions.push(eq(pageInformation.isActive, isActive))
  }

  if (from) {
    conditions.push(sql`${pageInformation.createdAt} >= ${from}`)
  }

  if (to) {
    conditions.push(sql`${pageInformation.createdAt} <= ${to}`)
  }

  const offset = (page - 1) * per_page

  // 정렬 설정
  let orderBy = desc(pageInformation.createdAt);
  
  if (sort && Array.isArray(sort) && sort.length > 0) {
    const sortItem = sort[0];
    if (sortItem.id === "createdAt") {
      orderBy = sortItem.desc ? desc(pageInformation.createdAt) : asc(pageInformation.createdAt);
    }
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined

  const data = await db
    .select()
    .from(pageInformation)
    .where(whereClause)
    .orderBy(orderBy)
    .limit(per_page)
    .offset(offset)

  return data
}

// 기존 패턴: 인포메이션 총 개수 조회
export async function countInformation(input: GetInformationSchema) {
  const { pagePath, isActive, from, to } = input

  const conditions = []

  if (pagePath) {
    conditions.push(ilike(pageInformation.pagePath, `%${pagePath}%`))
  }

  if (isActive !== null && isActive !== undefined) {
    conditions.push(eq(pageInformation.isActive, isActive))
  }

  if (from) {
    conditions.push(sql`${pageInformation.createdAt} >= ${from}`)
  }

  if (to) {
    conditions.push(sql`${pageInformation.createdAt} <= ${to}`)
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined

  const result = await db
    .select({ count: count() })
    .from(pageInformation)
    .where(whereClause)

  return result[0]?.count ?? 0
}

// 페이지 경로별 인포메이션 조회 (활성화된 것만)
export async function getInformationByPagePath(pagePath: string): Promise<PageInformation | null> {
  const result = await db
    .select()
    .from(pageInformation)
    .where(and(
      eq(pageInformation.pagePath, pagePath),
      eq(pageInformation.isActive, true)
    ))
    .limit(1)

  return result[0] || null
}

// 인포메이션 수정
export async function updateInformation(id: number, data: Partial<NewPageInformation>): Promise<PageInformation | null> {
  const result = await db
    .update(pageInformation)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(pageInformation.id, id))
    .returning()

  return result[0] || null
}

// ID로 인포메이션 조회
export async function getInformationById(id: number): Promise<PageInformation | null> {
  const result = await db
    .select()
    .from(pageInformation)
    .where(eq(pageInformation.id, id))
    .limit(1)

  return result[0] || null
}