import { asc, desc, eq, ilike, and, count, sql } from "drizzle-orm"
import db from "@/db/db"
import { notice, users, type Notice, type NewNotice } from "@/db/schema"

// 최신 패턴: 트랜잭션을 지원하는 공지사항 조회
export async function selectNoticeLists(
  tx: typeof db,
  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({
      id: notice.id,
      pagePath: notice.pagePath,
      title: notice.title,
      content: notice.content,
      authorId: notice.authorId,
      isActive: notice.isActive,
      createdAt: notice.createdAt,
      updatedAt: notice.updatedAt,
      authorName: users.name,
      authorEmail: users.email,
    })
    .from(notice)
    .leftJoin(users, eq(notice.authorId, users.id))
    .where(where)
    .orderBy(...(orderBy ?? [desc(notice.createdAt)]))
    .offset(offset)
    .limit(limit)
}

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

// 기존 패턴 (하위 호환성을 위해 유지)
export async function selectNotice(input: { page: number; per_page: number; sort?: string; pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }) {
  const { page, per_page = 50, sort, pagePath, title, authorId, isActive, from, to } = input

  const conditions = []

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

  if (title) {
    conditions.push(ilike(notice.title, `%${title}%`))
  }

  if (authorId) {
    conditions.push(eq(notice.authorId, authorId))
  }

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

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

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

  const offset = (page - 1) * per_page

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

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

  const data = await db
    .select({
      id: notice.id,
      pagePath: notice.pagePath,
      title: notice.title,
      content: notice.content,
      authorId: notice.authorId,
      isActive: notice.isActive,
      createdAt: notice.createdAt,
      updatedAt: notice.updatedAt,
      authorName: users.name,
      authorEmail: users.email,
    })
    .from(notice)
    .leftJoin(users, eq(notice.authorId, users.id))
    .where(whereClause)
    .orderBy(orderBy)
    .limit(per_page)
    .offset(offset)

  return data
}

// 기존 패턴: 공지사항 총 개수 조회
export async function countNotice(input: { pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }) {
  const { pagePath, title, authorId, isActive, from, to } = input

  const conditions = []

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

  if (title) {
    conditions.push(ilike(notice.title, `%${title}%`))
  }

  if (authorId) {
    conditions.push(eq(notice.authorId, authorId))
  }

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

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

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

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

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

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

// 페이지 경로별 공지사항 조회 (활성화된 것만, 작성자 정보 포함)
export async function getNoticesByPagePath(pagePath: string): Promise<Array<Notice & { authorName: string | null; authorEmail: string | null }>> {
  const result = await db
    .select({
      id: notice.id,
      pagePath: notice.pagePath,
      title: notice.title,
      content: notice.content,
      authorId: notice.authorId,
      isActive: notice.isActive,
      createdAt: notice.createdAt,
      updatedAt: notice.updatedAt,
      authorName: users.name,
      authorEmail: users.email,
    })
    .from(notice)
    .leftJoin(users, eq(notice.authorId, users.id))
    .where(and(
      eq(notice.pagePath, pagePath),
      eq(notice.isActive, true)
    ))
    .orderBy(desc(notice.createdAt))

  return result
}

// 공지사항 생성
export async function insertNotice(data: NewNotice): Promise<Notice> {
  const result = await db
    .insert(notice)
    .values(data)
    .returning()

  return result[0]
}

// 공지사항 수정
export async function updateNotice(id: number, data: Partial<NewNotice>): Promise<Notice | null> {
  const result = await db
    .update(notice)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(notice.id, id))
    .returning()

  return result[0] || null
}

// 공지사항 삭제
export async function deleteNoticeById(id: number): Promise<boolean> {
  const result = await db
    .delete(notice)
    .where(eq(notice.id, id))

  return (result.rowCount ?? 0) > 0
}

// 공지사항 다중 삭제
export async function deleteNoticeByIds(ids: number[]): Promise<number> {
  const result = await db
    .delete(notice)
    .where(sql`${notice.id} = ANY(${ids})`)

  return result.rowCount ?? 0
}

// ID로 공지사항 조회 (작성자 정보 포함)
export async function getNoticeById(id: number): Promise<(Notice & { authorName: string | null; authorEmail: string | null }) | null> {
  const result = await db
    .select({
      id: notice.id,
      pagePath: notice.pagePath,
      title: notice.title,
      content: notice.content,
      authorId: notice.authorId,
      isActive: notice.isActive,
      createdAt: notice.createdAt,
      updatedAt: notice.updatedAt,
      authorName: users.name,
      authorEmail: users.email,
    })
    .from(notice)
    .leftJoin(users, eq(notice.authorId, users.id))
    .where(eq(notice.id, id))
    .limit(1)

  return result[0] || null
}