3 files changed, 648 insertions, 0 deletions
diff --git a/lib/notice/repository.ts b/lib/notice/repository.ts
new file mode 100644
index 00000000..84e64f00
--- /dev/null
+++ b/lib/notice/repository.ts
@@ -0,0 +1,244 @@
+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
+} 
+\ No newline at end of file
diff --git a/lib/notice/service.ts b/lib/notice/service.ts
new file mode 100644
index 00000000..24b03fe9
--- /dev/null
+++ b/lib/notice/service.ts
@@ -0,0 +1,324 @@
+"use server"
+
+import { revalidateTag, unstable_noStore } from "next/cache"
+import { getErrorMessage } from "@/lib/handle-error"
+import { unstable_cache } from "@/lib/unstable-cache"
+import { filterColumns } from "@/lib/filter-columns"
+import { asc, desc, ilike, and, or, eq } from "drizzle-orm"
+import db from "@/db/db"
+import { notice, pageInformation } from "@/db/schema"
+
+import type { 
+  CreateNoticeSchema, 
+  UpdateNoticeSchema, 
+  GetNoticeSchema
+} from "./validations"
+
+import {
+  selectNotice,
+  countNotice,
+  getNoticesByPagePath,
+  insertNotice,
+  updateNotice,
+  deleteNoticeById,
+  deleteNoticeByIds,
+  getNoticeById,
+  selectNoticeLists,
+  countNoticeLists
+} from "./repository"
+
+import type { Notice } from "@/db/schema/notice"
+
+export async function getNoticeLists(input: GetNoticeSchema) {
+  return unstable_cache(
+    async () => {
+      try {
+        // 고급 검색 로직
+        const { page, perPage, search, filters, joinOperator, pagePath, title, content, authorId, isActive } = input
+
+        // 기본 검색 조건들
+        const conditions = []
+
+        // 검색어가 있으면 여러 필드에서 검색
+        if (search && search.trim()) {
+          const searchConditions = [
+            ilike(notice.pagePath, `%${search}%`),
+            ilike(notice.title, `%${search}%`),
+            ilike(notice.content, `%${search}%`)
+          ]
+          conditions.push(or(...searchConditions))
+        }
+
+        // 개별 필드 조건들
+        if (pagePath && pagePath.trim()) {
+          conditions.push(ilike(notice.pagePath, `%${pagePath}%`))
+        }
+
+        if (title && title.trim()) {
+          conditions.push(ilike(notice.title, `%${title}%`))
+        }
+
+        if (content && content.trim()) {
+          conditions.push(ilike(notice.content, `%${content}%`))
+        }
+
+        if (authorId !== null && authorId !== undefined) {
+          conditions.push(eq(notice.authorId, authorId))
+        }
+
+        if (isActive !== null && isActive !== undefined) {
+          conditions.push(eq(notice.isActive, isActive))
+        }
+        // 고급 필터 처리
+        if (filters && filters.length > 0) {
+          const advancedConditions = filters.map(() => 
+            filterColumns({ 
+              table: notice, 
+              filters: filters,
+              joinOperator: joinOperator,
+            })
+          )
+
+          if (advancedConditions.length > 0) {
+            if (joinOperator === "or") {
+              conditions.push(or(...advancedConditions))
+            } else {
+              conditions.push(and(...advancedConditions))
+            }
+          }
+        }
+
+        // 전체 WHERE 조건 조합
+        const finalWhere = conditions.length > 0 
+          ? (joinOperator === "or" ? or(...conditions) : and(...conditions))
+          : undefined
+
+        // 페이지네이션
+        const offset = (page - 1) * perPage
+
+        // 정렬 처리
+        const orderBy = input.sort.length > 0
+          ? input.sort.map((item) => {
+              if (item.id === "createdAt") {
+                return item.desc ? desc(notice.createdAt) : asc(notice.createdAt)
+              } else if (item.id === "updatedAt") {
+                return item.desc ? desc(notice.updatedAt) : asc(notice.updatedAt)
+              } else if (item.id === "pagePath") {
+                return item.desc ? desc(notice.pagePath) : asc(notice.pagePath)
+              } else if (item.id === "title") {
+                return item.desc ? desc(notice.title) : asc(notice.title)
+              } else if (item.id === "authorId") {
+                return item.desc ? desc(notice.authorId) : asc(notice.authorId)
+              } else if (item.id === "isActive") {
+                return item.desc ? desc(notice.isActive) : asc(notice.isActive)
+              } else {
+                return desc(notice.createdAt) // 기본값
+              }
+            })
+          : [desc(notice.createdAt)]
+
+        // 트랜잭션 내부에서 Repository 호출
+        const { data, total } = await db.transaction(async (tx) => {
+          const data = await selectNoticeLists(tx, {
+            where: finalWhere,
+            orderBy,
+            offset,
+            limit: input.perPage,
+          })
+
+          const total = await countNoticeLists(tx, finalWhere)
+          return { data, total }
+        })
+
+        const pageCount = Math.ceil(total / input.perPage)
+
+        return { data, pageCount, total }
+      } catch (err) {
+        console.error("Failed to get notice lists:", err)
+        // 에러 발생 시 기본값 반환
+        return { data: [], pageCount: 0, total: 0 }
+      }
+    },
+    [JSON.stringify(input)],
+    {
+      revalidate: 3600,
+      tags: ["notice-lists"],
+    }
+  )()
+}
+
+// 기존 패턴 (하위 호환성을 위해 유지)  
+export async function getNoticeList(input: Partial<{ page: number; per_page: number; sort?: string; pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }> & { page: number; per_page: number }) {
+  unstable_noStore()
+  
+  try {
+    const [data, total] = await Promise.all([
+      selectNotice(input),
+      countNotice(input)
+    ])
+
+    const pageCount = Math.ceil(total / input.per_page)
+
+    return {
+      data,
+      pageCount,
+      total
+    }
+  } catch (error) {
+    console.error("Failed to get notice list:", error)
+    throw new Error(getErrorMessage(error))
+  }
+}
+
+// 페이지별 공지사항 조회 (일반 사용자용)
+export async function getPageNotices(pagePath: string): Promise<Array<Notice & { authorName: string | null; authorEmail: string | null }>> {
+  try {
+    return await getNoticesByPagePath(pagePath)
+  } catch (error) {
+    console.error(`Failed to get notices for page ${pagePath}:`, error)
+    return []
+  }
+}
+
+// 캐시된 페이지별 공지사항 조회
+export const getCachedPageNotices = unstable_cache(
+  async (pagePath: string) => getPageNotices(pagePath),
+  ["page-notices"],
+  {
+    tags: ["page-notices"],
+    revalidate: 3600, // 1시간 캐시
+  }
+)
+
+// 공지사항 생성
+export async function createNotice(input: CreateNoticeSchema) {
+  try {
+    const result = await insertNotice(input)
+    
+    revalidateTag("page-notices")
+    revalidateTag("notice-lists")
+    
+    return {
+      success: true,
+      data: result,
+      message: "공지사항이 성공적으로 생성되었습니다."
+    }
+  } catch (error) {
+    console.error("Failed to create notice:", error)
+    return {
+      success: false,
+      message: getErrorMessage(error)
+    }
+  }
+}
+
+// 공지사항 수정
+export async function updateNoticeData(input: UpdateNoticeSchema) {
+  try {
+    const { id, ...updateData } = input
+    const result = await updateNotice(id, updateData)
+    
+    if (!result) {
+      return {
+        success: false,
+        message: "공지사항을 찾을 수 없거나 수정에 실패했습니다."
+      }
+    }
+    
+    revalidateTag("page-notices")
+    revalidateTag("notice-lists")
+    
+    return {
+      success: true,
+      message: "공지사항이 성공적으로 수정되었습니다."
+    }
+  } catch (error) {
+    console.error("Failed to update notice:", error)
+    return {
+      success: false,
+      message: getErrorMessage(error)
+    }
+  }
+}
+
+// 공지사항 삭제
+export async function deleteNotice(id: number) {
+  try {
+    const success = await deleteNoticeById(id)
+    
+    if (!success) {
+      return {
+        success: false,
+        message: "공지사항을 찾을 수 없거나 삭제에 실패했습니다."
+      }
+    }
+    
+    revalidateTag("page-notices")
+    revalidateTag("notice-lists")
+    
+    return {
+      success: true,
+      message: "공지사항이 성공적으로 삭제되었습니다."
+    }
+  } catch (error) {
+    console.error("Failed to delete notice:", error)
+    return {
+      success: false,
+      message: getErrorMessage(error)
+    }
+  }
+}
+
+// 공지사항 다중 삭제
+export async function deleteMultipleNotices(ids: number[]) {
+  try {
+    const deletedCount = await deleteNoticeByIds(ids)
+    
+    revalidateTag("page-notices")
+    revalidateTag("notice-lists")
+    
+    return {
+      success: true,
+      deletedCount,
+      message: `${deletedCount}개의 공지사항이 성공적으로 삭제되었습니다.`
+    }
+  } catch (error) {
+    console.error("Failed to delete multiple notices:", error)
+    return {
+      success: false,
+      message: getErrorMessage(error)
+    }
+  }
+}
+
+// ID로 공지사항 조회
+export async function getNoticeDetail(id: number): Promise<(Notice & { authorName: string | null; authorEmail: string | null }) | null> {
+  try {
+    return await getNoticeById(id)
+  } catch (error) {
+    console.error(`Failed to get notice detail for id ${id}:`, error)
+    return null
+  }
+}
+
+// pagePath 목록 조회 (정보 시스템에서 사용)
+export async function getPagePathList(): Promise<Array<{ pagePath: string; pageName: string }>> {
+  try {
+    const result = await db
+      .selectDistinct({
+        pagePath: pageInformation.pagePath,
+        pageName: pageInformation.pageName
+      })
+      .from(pageInformation)
+      .where(eq(pageInformation.isActive, true))
+      .orderBy(asc(pageInformation.pagePath))
+
+    return result.map(item => ({
+      pagePath: item.pagePath,
+      pageName: item.pageName || item.pagePath
+    }))
+  } catch (error) {
+    console.error("Failed to get page path list:", error)
+    return []
+  }
+} 
+\ No newline at end of file
diff --git a/lib/notice/validations.ts b/lib/notice/validations.ts
new file mode 100644
index 00000000..05e84af9
--- /dev/null
+++ b/lib/notice/validations.ts
@@ -0,0 +1,80 @@
+import { z } from "zod"
+import {
+  createSearchParamsCache,
+  parseAsArrayOf,
+  parseAsInteger,
+  parseAsString,
+  parseAsStringEnum,
+  parseAsBoolean,
+} from "nuqs/server"
+import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers"
+import { Notice } from "@/db/schema/notice"
+
+// 공지사항 생성 스키마
+export const createNoticeSchema = z.object({
+  pagePath: z.string().min(1, "페이지 경로를 입력해주세요"),
+  title: z.string().min(1, "제목을 입력해주세요"),
+  content: z.string().min(1, "내용을 입력해주세요"),
+  authorId: z.number().min(1, "작성자를 선택해주세요"),
+  isActive: z.boolean().default(true),
+})
+
+// 공지사항 수정 스키마
+export const updateNoticeSchema = z.object({
+  id: z.number(),
+  pagePath: z.string().min(1, "페이지 경로를 입력해주세요"),
+  title: z.string().min(1, "제목을 입력해주세요"),
+  content: z.string().min(1, "내용을 입력해주세요"),
+  isActive: z.boolean().default(true),
+})
+
+// 현대적인 검색 파라미터 캐시
+export const searchParamsNoticeCache = createSearchParamsCache({
+  flags: parseAsArrayOf(z.enum(["advancedTable", "floatingBar"])).withDefault([]),
+  page: parseAsInteger.withDefault(1),
+  perPage: parseAsInteger.withDefault(10),
+  sort: getSortingStateParser<Notice>().withDefault([
+    { id: "createdAt", desc: true },
+  ]),
+
+  // 기본 검색 필드들
+  pagePath: parseAsString.withDefault(""),
+  title: parseAsString.withDefault(""),
+  content: parseAsString.withDefault(""),
+  authorId: parseAsInteger,
+  isActive: parseAsBoolean,
+  
+  // 고급 필터
+  filters: getFiltersStateParser().withDefault([]),
+  joinOperator: parseAsStringEnum(["and", "or"]).withDefault("and"),
+  search: parseAsString.withDefault(""),
+  
+  // 날짜 범위
+  from: parseAsString.withDefault(""),
+  to: parseAsString.withDefault(""),
+})
+
+// 타입 추출
+export type CreateNoticeSchema = z.infer<typeof createNoticeSchema>
+export type UpdateNoticeSchema = z.infer<typeof updateNoticeSchema>
+export type GetNoticeSchema = Awaited<ReturnType<typeof searchParamsNoticeCache.parse>>
+
+// 기존 스키마 (하위 호환성을 위해 유지)
+export const getNoticeSchema = z.object({
+  page: z.coerce.number().default(1),
+  per_page: z.coerce.number().default(10),
+  sort: z.string().optional(),
+  pagePath: z.string().optional(),
+  title: z.string().optional(),
+  authorId: z.coerce.number().optional(),
+  isActive: z.coerce.boolean().optional(),
+  from: z.string().optional(),
+  to: z.string().optional(),
+})
+
+// 페이지 경로별 공지사항 조회 스키마
+export const getPageNoticeSchema = z.object({
+  pagePath: z.string().min(1, "페이지 경로를 입력해주세요"),
+})
+
+export type GetPageNoticeSchema = z.infer<typeof getPageNoticeSchema> 
+\ No newline at end of file