import { asc, desc, eq, and } from "drizzle-orm"
import db from "@/db/db"
import { 
  pageInformation, 
  informationAttachments,
  type PageInformation, 
  type NewPageInformation,
  type InformationAttachment,
  type NewInformationAttachment
} from "@/db/schema/information"



// 인포메이션 수정
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
}

// 인포메이션과 첨부파일 함께 조회
export async function getInformationWithAttachments(id: number) {
  const information = await db
    .select()
    .from(pageInformation)
    .where(eq(pageInformation.id, id))
    .limit(1)

  if (!information[0]) return null

  const attachments = await db
    .select()
    .from(informationAttachments)
    .where(eq(informationAttachments.informationId, id))
    .orderBy(asc(informationAttachments.createdAt))

  return {
    ...information[0],
    attachments
  }
}

// 페이지 경로로 인포메이션과 첨부파일 함께 조회
export async function getInformationByPagePathWithAttachments(pagePath: string) {
  const information = await db
    .select()
    .from(pageInformation)
    .where(and(
      eq(pageInformation.pagePath, pagePath),
      eq(pageInformation.isActive, true)
    ))
    .limit(1)

  if (!information[0]) return null

  const attachments = await db
    .select()
    .from(informationAttachments)
    .where(eq(informationAttachments.informationId, information[0].id))
    .orderBy(asc(informationAttachments.createdAt))

  return {
    ...information[0],
    attachments
  }
}

// 첨부파일 추가
export async function addInformationAttachment(data: NewInformationAttachment): Promise<InformationAttachment | null> {
  const result = await db
    .insert(informationAttachments)
    .values(data)
    .returning()

  return result[0] || null
}

// 첨부파일 삭제
export async function deleteInformationAttachment(id: number): Promise<boolean> {
  const result = await db
    .delete(informationAttachments)
    .where(eq(informationAttachments.id, id))
    .returning()

  return result.length > 0
}

// 인포메이션 ID로 모든 첨부파일 조회
export async function getAttachmentsByInformationId(informationId: number): Promise<InformationAttachment[]> {
  return await db
    .select()
    .from(informationAttachments)
    .where(eq(informationAttachments.informationId, informationId))
    .orderBy(asc(informationAttachments.createdAt))
}

// 첨부파일 ID로 조회
export async function getAttachmentById(id: number): Promise<InformationAttachment | null> {
  const result = await db
    .select()
    .from(informationAttachments)
    .where(eq(informationAttachments.id, id))
    .limit(1)

  return result[0] || null
}