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

// 페이지 경로별 공지사항 조회 (활성화된 것만, 작성자 정보 포함, 유효기간 내 공지사항만)
export async function getNoticesByPagePath(pagePath: string): Promise<Array<Notice & { authorName: string | null; authorEmail: string | null }>> {
  const currentTime = new Date()

  const result = await db
    .select({
      id: notice.id,
      pagePath: notice.pagePath,
      title: notice.title,
      content: notice.content,
      authorId: notice.authorId,
      isActive: notice.isActive,
      isPopup: notice.isPopup,
      startAt: notice.startAt,
      endAt: notice.endAt,
      dontShowDuration: notice.dontShowDuration,
      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),
      // // 유효기간 필터링: startAt과 endAt이 모두 null이거나 현재 시간이 범위 내에 있어야 함
      // sql`(${notice.startAt} IS NULL OR ${notice.startAt} <= ${currentTime})`,
      // sql`(${notice.endAt} IS NULL OR ${notice.endAt} >= ${currentTime})`
    ))
    .orderBy(desc(notice.createdAt))

  return result
}

// 공지사항 생성
export async function insertNotice(data: NewNotice): Promise<Notice> {
  const result = await db
    .insert(notice)
    .values({
      ...data,
      createdAt: new Date(),
      updatedAt: new Date()
    })
    .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,
      isPopup: notice.isPopup,
      startAt: notice.startAt,
      endAt: notice.endAt,
      dontShowDuration: notice.dontShowDuration,
      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
}