(대표님) lib 파트 커밋

1 files changed, 782 insertions, 0 deletions

diff --git a/lib/vendor-document-list/enhanced-document-service.ts b/lib/vendor-document-list/enhanced-document-service.ts
new file mode 100644
index 00000000..00f40ea6
--- /dev/null
+++ b/lib/vendor-document-list/enhanced-document-service.ts
@@ -0,0 +1,782 @@
+// enhanced-document-service.ts
+"use server"
+
+import { revalidatePath, unstable_cache } from "next/cache"
+import { and, asc, desc, eq, ilike, or, count, avg } from "drizzle-orm"
+import db from "@/db/db"
+import { documentAttachments, documents, enhancedDocumentsView, issueStages, revisions, type EnhancedDocumentsView } from "@/db/schema/vendorDocu"
+import { filterColumns } from "@/lib/filter-columns"
+import type {
+    CreateDocumentInput,
+    UpdateDocumentInput,
+    CreateStageInput,
+    UpdateStageInput,
+    CreateRevisionInput,
+    UpdateRevisionStatusInput,
+    ApiResponse,
+    StageWithRevisions,
+    FullDocument,
+    DocumentAttachment,
+    Revision
+  } from "@/types/enhanced-documents"
+
+// 스키마 타입 정의
+export interface GetEnhancedDocumentsSchema {
+  page: number
+  perPage: number
+  search?: string
+  filters?: Array<{
+    id: string
+    value: string | string[]
+    operator?: "eq" | "ne" | "like" | "ilike" | "in" | "notin" | "lt" | "lte" | "gt" | "gte"
+  }>
+  joinOperator?: "and" | "or"
+  sort?: Array<{
+    id: keyof EnhancedDocumentsView
+    desc: boolean
+  }>
+}
+
+// Repository 함수들
+export async function selectEnhancedDocuments(
+  tx: any,
+  options: {
+    where?: any
+    orderBy?: any
+    offset?: number
+    limit?: number
+  }
+) {
+  const { where, orderBy, offset, limit } = options
+  
+  let query = tx.select().from(enhancedDocumentsView)
+  
+  if (where) {
+    query = query.where(where)
+  }
+  
+  if (orderBy) {
+    query = query.orderBy(...orderBy)
+  }
+  
+  if (offset !== undefined) {
+    query = query.offset(offset)
+  }
+  
+  if (limit !== undefined) {
+    query = query.limit(limit)
+  }
+  
+  return await query
+}
+
+export async function countEnhancedDocuments(tx: any, where?: any) {
+  let query = tx.select({ count: count() }).from(enhancedDocumentsView)
+  
+  if (where) {
+    query = query.where(where)
+  }
+  
+  const result = await query
+  return result[0]?.count || 0
+}
+
+// 메인 서버 액션
+export async function getEnhancedDocuments(
+  input: GetEnhancedDocumentsSchema, 
+  contractId: number
+) {
+  return unstable_cache(
+    async () => {
+      try {
+        const offset = (input.page - 1) * input.perPage
+
+        // 고급 필터 처리
+        const advancedWhere = filterColumns({
+          table: enhancedDocumentsView,
+          filters: input.filters || [],
+          joinOperator: input.joinOperator || "and",
+        })
+
+        // 전역 검색 처리
+        let globalWhere
+        if (input.search) {
+          const searchTerm = `%${input.search}%`
+          globalWhere = or(
+            ilike(enhancedDocumentsView.title, searchTerm),
+            ilike(enhancedDocumentsView.docNumber, searchTerm),
+            ilike(enhancedDocumentsView.currentStageName, searchTerm),
+            ilike(enhancedDocumentsView.currentStageAssigneeName, searchTerm)
+          )
+        }
+
+        // 최종 WHERE 조건
+        const finalWhere = and(
+          advancedWhere, 
+          globalWhere, 
+          eq(enhancedDocumentsView.contractId, contractId)
+        )
+
+        // 정렬 처리
+        const orderBy = input.sort && input.sort.length > 0
+          ? input.sort.map((item) =>
+              item.desc 
+                ? desc(enhancedDocumentsView[item.id]) 
+                : asc(enhancedDocumentsView[item.id])
+            )
+          : [desc(enhancedDocumentsView.createdAt)]
+
+        // 트랜잭션 실행
+        const { data, total } = await db.transaction(async (tx) => {
+          const data = await selectEnhancedDocuments(tx, {
+            where: finalWhere,
+            orderBy,
+            offset,
+            limit: input.perPage,
+          })
+          
+          const total = await countEnhancedDocuments(tx, finalWhere)
+          
+          return { data, total }
+        })
+
+        const pageCount = Math.ceil(total / input.perPage)
+
+        return { data, pageCount, total }
+      } catch (err) {
+        console.error("Error fetching enhanced documents:", err)
+        return { data: [], pageCount: 0, total: 0 }
+      }
+    },
+    [JSON.stringify(input), String(contractId)],
+    {
+      revalidate: 3600,
+      tags: [`enhanced-documents-${contractId}`],
+    }
+  )()
+}
+
+// 통계 데이터 가져오기
+export async function getDocumentStatistics(contractId: number) {
+  return unstable_cache(
+    async () => {
+      try {
+        const result = await db
+          .select({
+            total: count(),
+            overdue: count(enhancedDocumentsView.isOverdue),
+            dueSoon: count(), // 별도 필터링 필요
+            highPriority: count(),
+            avgProgress: count(), // 별도 계산 필요
+          })
+          .from(enhancedDocumentsView)
+          .where(eq(enhancedDocumentsView.contractId, contractId))
+
+        // 더 정확한 통계를 위한 별도 쿼리들
+        const [overdue, dueSoon, highPriority] = await Promise.all([
+          db
+            .select({ count: count() })
+            .from(enhancedDocumentsView)
+            .where(
+              and(
+                eq(enhancedDocumentsView.contractId, contractId),
+                eq(enhancedDocumentsView.isOverdue, true)
+              )
+            ),
+          db
+            .select({ count: count() })
+            .from(enhancedDocumentsView)
+            .where(
+              and(
+                eq(enhancedDocumentsView.contractId, contractId),
+                eq(enhancedDocumentsView.isOverdue, false),
+                // daysUntilDue <= 3 AND daysUntilDue >= 0 조건 추가 필요
+              )
+            ),
+          db
+            .select({ count: count() })
+            .from(enhancedDocumentsView)
+            .where(
+              and(
+                eq(enhancedDocumentsView.contractId, contractId),
+                eq(enhancedDocumentsView.currentStagePriority, "HIGH")
+              )
+            )
+        ])
+
+        // 평균 진행률 계산
+        const avgProgressResult = await db
+          .select({
+            avgProgress: avg(enhancedDocumentsView.progressPercentage)
+          })
+          .from(enhancedDocumentsView)
+          .where(eq(enhancedDocumentsView.contractId, contractId))
+
+        return {
+          total: result[0]?.total || 0,
+          overdue: overdue[0]?.count || 0,
+          dueSoon: dueSoon[0]?.count || 0,
+          highPriority: highPriority[0]?.count || 0,
+          avgProgress: Math.round(avgProgressResult[0]?.avgProgress || 0),
+        }
+      } catch (err) {
+        console.error("Error fetching document statistics:", err)
+        return {
+          total: 0,
+          overdue: 0,
+          dueSoon: 0,
+          highPriority: 0,
+          avgProgress: 0,
+        }
+      }
+    },
+    [`document-stats-${contractId}`],
+    {
+      revalidate: 1800, // 30분 캐시
+      tags: [`document-stats-${contractId}`],
+    }
+  )()
+}
+
+// 빠른 필터 데이터
+export async function getQuickFilterData(contractId: number) {
+  return unstable_cache(
+    async () => {
+      try {
+        const [all, overdue, dueSoon, inProgress, highPriority] = await Promise.all([
+          countEnhancedDocuments(db, eq(enhancedDocumentsView.contractId, contractId)),
+          countEnhancedDocuments(db, and(
+            eq(enhancedDocumentsView.contractId, contractId),
+            eq(enhancedDocumentsView.isOverdue, true)
+          )),
+          // dueSoon 조건은 SQL에서 직접 처리하거나 별도 뷰 필요
+          countEnhancedDocuments(db, and(
+            eq(enhancedDocumentsView.contractId, contractId),
+            eq(enhancedDocumentsView.currentStageStatus, "IN_PROGRESS")
+          )),
+          countEnhancedDocuments(db, and(
+            eq(enhancedDocumentsView.contractId, contractId),
+            eq(enhancedDocumentsView.currentStageStatus, "IN_PROGRESS")
+          )),
+          countEnhancedDocuments(db, and(
+            eq(enhancedDocumentsView.contractId, contractId),
+            eq(enhancedDocumentsView.currentStagePriority, "HIGH")
+          ))
+        ])
+
+        return {
+          all,
+          overdue,
+          dueSoon: 0, // 별도 계산 필요
+          inProgress,
+          highPriority,
+        }
+      } catch (err) {
+        console.error("Error fetching quick filter data:", err)
+        return {
+          all: 0,
+          overdue: 0,
+          dueSoon: 0,
+          inProgress: 0,
+          highPriority: 0,
+        }
+      }
+    },
+    [`quick-filter-${contractId}`],
+    {
+      revalidate: 1800,
+      tags: [`quick-filter-${contractId}`],
+    }
+  )()
+}
+
+// 단일 문서 상세 정보
+export async function getDocumentDetails(documentId: number) {
+  return unstable_cache(
+    async () => {
+      try {
+        const result = await db
+          .select()
+          .from(enhancedDocumentsView)
+          .where(eq(enhancedDocumentsView.documentId, documentId))
+          .limit(1)
+
+        return result[0] || null
+      } catch (err) {
+        console.error("Error fetching document details:", err)
+        return null
+      }
+    },
+    [`document-details-${documentId}`],
+    {
+      revalidate: 1800,
+      tags: [`document-details-${documentId}`],
+    }
+  )()
+}
+
+
+// 문서 CRUD 작업들
+  export async function createDocument(input: CreateDocumentInput): Promise<ApiResponse<number>> {
+    try {
+      const [newDocument] = await db.insert(documents).values({
+        contractId: input.contractId,
+        docNumber: input.docNumber,
+        title: input.title,
+        pic: input.pic,
+        issuedDate: input.issuedDate,
+      }).returning({ id: documents.id })
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        data: newDocument.id,
+        message: "문서가 성공적으로 생성되었습니다."
+      }
+    } catch (error) {
+      console.error("Error creating document:", error)
+      return {
+        success: false,
+        error: "문서 생성 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function updateDocument(input: UpdateDocumentInput): Promise<ApiResponse<void>> {
+    try {
+      await db.update(documents)
+        .set({
+          ...input,
+          updatedAt: new Date(),
+        })
+        .where(eq(documents.id, input.id))
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: "문서가 성공적으로 업데이트되었습니다."
+      }
+    } catch (error) {
+      console.error("Error updating document:", error)
+      return {
+        success: false,
+        error: "문서 업데이트 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function deleteDocument(id: number): Promise<ApiResponse<void>> {
+    try {
+      await db.delete(documents).where(eq(documents.id, id))
+      
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: "문서가 성공적으로 삭제되었습니다."
+      }
+    } catch (error) {
+      console.error("Error deleting document:", error)
+      return {
+        success: false,
+        error: "문서 삭제 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  // 스테이지 CRUD 작업들
+  export async function createStage(input: CreateStageInput): Promise<ApiResponse<number>> {
+    try {
+      const [newStage] = await db.insert(issueStages).values({
+        documentId: input.documentId,
+        stageName: input.stageName,
+        planDate: input.planDate,
+        stageOrder: input.stageOrder ?? 0,
+        priority: input.priority ?? 'MEDIUM',
+        assigneeId: input.assigneeId,
+        assigneeName: input.assigneeName,
+        description: input.description,
+        reminderDays: input.reminderDays ?? 3,
+      }).returning({ id: issueStages.id })
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        data: newStage.id,
+        message: "스테이지가 성공적으로 생성되었습니다."
+      }
+    } catch (error) {
+      console.error("Error creating stage:", error)
+      return {
+        success: false,
+        error: "스테이지 생성 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function updateStage(input: UpdateStageInput): Promise<ApiResponse<void>> {
+    try {
+      await db.update(issueStages)
+        .set({
+          ...input,
+          updatedAt: new Date(),
+        })
+        .where(eq(issueStages.id, input.id))
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: "스테이지가 성공적으로 업데이트되었습니다."
+      }
+    } catch (error) {
+      console.error("Error updating stage:", error)
+      return {
+        success: false,
+        error: "스테이지 업데이트 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function updateStageStatus(
+    stageId: number, 
+    status: string
+  ): Promise<ApiResponse<void>> {
+    try {
+      const updateData: any = {
+        stageStatus: status,
+        updatedAt: new Date(),
+      }
+  
+      // 상태에 따른 자동 날짜 업데이트
+      if (status === 'COMPLETED' || status === 'APPROVED') {
+        updateData.actualDate = new Date().toISOString().split('T')[0]
+      }
+  
+      await db.update(issueStages)
+        .set(updateData)
+        .where(eq(issueStages.id, stageId))
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: "스테이지 상태가 업데이트되었습니다."
+      }
+    } catch (error) {
+      console.error("Error updating stage status:", error)
+      return {
+        success: false,
+        error: "스테이지 상태 업데이트 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  // 리비전 CRUD 작업들
+  export async function createRevision(input: CreateRevisionInput): Promise<ApiResponse<number>> {
+    try {
+      const result = await db.transaction(async (tx) => {
+        // 리비전 생성
+        const [newRevision] = await tx.insert(revisions).values({
+          issueStageId: input.issueStageId,
+          revision: input.revision,
+          uploaderType: input.uploaderType,
+          uploaderId: input.uploaderId,
+          uploaderName: input.uploaderName,
+          comment: input.comment,
+          submittedDate: new Date().toISOString().split('T')[0],
+        }).returning({ id: revisions.id })
+  
+        // 첨부파일들 생성
+        if (input.attachments && input.attachments.length > 0) {
+          await tx.insert(documentAttachments).values(
+            input.attachments.map(attachment => ({
+              revisionId: newRevision.id,
+              fileName: attachment.fileName,
+              filePath: attachment.filePath,
+              fileType: attachment.fileType,
+              fileSize: attachment.fileSize,
+            }))
+          )
+        }
+  
+        // 스테이지 상태를 SUBMITTED로 업데이트
+        await tx.update(issueStages)
+          .set({
+            stageStatus: 'SUBMITTED',
+            updatedAt: new Date(),
+          })
+          .where(eq(issueStages.id, input.issueStageId))
+  
+        return newRevision.id
+      })
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        data: result,
+        message: "리비전이 성공적으로 업로드되었습니다."
+      }
+    } catch (error) {
+      console.error("Error creating revision:", error)
+      return {
+        success: false,
+        error: "리비전 업로드 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function updateRevisionStatus(input: UpdateRevisionStatusInput): Promise<ApiResponse<void>> {
+    try {
+      const updateData: any = {
+        revisionStatus: input.revisionStatus,
+        reviewerId: input.reviewerId,
+        reviewerName: input.reviewerName,
+        reviewComments: input.reviewComments,
+        updatedAt: new Date(),
+      }
+  
+      // 상태에 따른 자동 날짜 업데이트
+      const today = new Date().toISOString().split('T')[0]
+      if (input.revisionStatus === 'UNDER_REVIEW') {
+        updateData.reviewStartDate = today
+      } else if (input.revisionStatus === 'APPROVED') {
+        updateData.approvedDate = today
+      } else if (input.revisionStatus === 'REJECTED') {
+        updateData.rejectedDate = today
+      }
+  
+      await db.update(revisions)
+        .set(updateData)
+        .where(eq(revisions.id, input.id))
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: "리비전 상태가 업데이트되었습니다."
+      }
+    } catch (error) {
+      console.error("Error updating revision status:", error)
+      return {
+        success: false,
+        error: "리비전 상태 업데이트 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  // 조회 작업들
+  export async function getDocumentWithStages(documentId: number): Promise<ApiResponse<FullDocument>> {
+    try {
+      // 문서 기본 정보
+      const [document] = await db.select()
+        .from(documents)
+        .where(eq(documents.id, documentId))
+  
+      if (!document) {
+        return {
+          success: false,
+          error: "문서를 찾을 수 없습니다."
+        }
+      }
+  
+      // 스테이지와 리비전, 첨부파일 조회
+      const stagesData = await db.select({
+        stage: issueStages,
+        revision: revisions,
+        attachment: documentAttachments,
+      })
+      .from(issueStages)
+      .leftJoin(revisions, eq(issueStages.id, revisions.issueStageId))
+      .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId))
+      .where(eq(issueStages.documentId, documentId))
+      .orderBy(asc(issueStages.stageOrder), desc(revisions.createdAt))
+  
+      // 데이터 구조화
+      const stagesMap = new Map<number, StageWithRevisions>()
+      
+      stagesData.forEach(({ stage, revision, attachment }) => {
+        if (!stagesMap.has(stage.id)) {
+          stagesMap.set(stage.id, {
+            ...stage,
+            revisions: []
+          })
+        }
+  
+        const stageData = stagesMap.get(stage.id)!
+        
+        if (revision) {
+          let revisionData = stageData.revisions.find(r => r.id === revision.id)
+          if (!revisionData) {
+            revisionData = {
+              ...revision,
+              attachments: []
+            }
+            stageData.revisions.push(revisionData)
+          }
+  
+          if (attachment) {
+            revisionData.attachments.push(attachment)
+          }
+        }
+      })
+  
+      const stages = Array.from(stagesMap.values())
+  
+      return {
+        success: true,
+        data: {
+          ...document,
+          stages,
+          currentStage: stages.find(s => s.stageStatus === 'IN_PROGRESS'),
+          latestRevision: stages
+            .flatMap(s => s.revisions)
+            .sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime())[0]
+        }
+      }
+    } catch (error) {
+      console.error("Error getting document with stages:", error)
+      return {
+        success: false,
+        error: "문서 조회 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  // 문서의 스테이지와 리비전만 가져오는 경량화된 함수
+  export async function getDocumentStagesWithRevisions(documentId: number): Promise<ApiResponse<StageWithRevisions[]>> {
+    try {
+      const stagesData = await db.select({
+        stage: issueStages,
+        revision: revisions,
+        attachment: documentAttachments,
+      })
+      .from(issueStages)
+      .leftJoin(revisions, eq(issueStages.id, revisions.issueStageId))
+      .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId))
+      .where(eq(issueStages.documentId, documentId))
+      .orderBy(asc(issueStages.stageOrder), desc(revisions.createdAt))
+
+      console.log(documentId, stagesData)
+  
+      // 데이터 구조화
+      const stagesMap = new Map<number, StageWithRevisions>()
+      
+      stagesData.forEach(({ stage, revision, attachment }) => {
+        if (!stagesMap.has(stage.id)) {
+          stagesMap.set(stage.id, {
+            ...stage,
+            revisions: []
+          })
+        }
+  
+        const stageData = stagesMap.get(stage.id)!
+        
+        if (revision) {
+          let revisionData = stageData.revisions.find(r => r.id === revision.id)
+          if (!revisionData) {
+            revisionData = {
+              ...revision,
+              attachments: []
+            }
+            stageData.revisions.push(revisionData)
+          }
+  
+          if (attachment) {
+            revisionData.attachments.push(attachment)
+          }
+        }
+      })
+  
+      const stages = Array.from(stagesMap.values())
+  
+      return {
+        success: true,
+        data: stages
+      }
+    } catch (error) {
+        console.log(error)
+      console.error("Error getting document stages with revisions:", error)
+      return {
+        success: false,
+        error: "스테이지 조회 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  // 특정 스테이지의 리비전들만 가져오는 함수
+  export async function getStageRevisions(stageId: number): Promise<ApiResponse<Array<Revision & { attachments: DocumentAttachment[] }>>> {
+    try {
+      const revisionsData = await db.select({
+        revision: revisions,
+        attachment: documentAttachments,
+      })
+      .from(revisions)
+      .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId))
+      .where(eq(revisions.issueStageId, stageId))
+      .orderBy(desc(revisions.createdAt))
+
+      console.log(stageId, revisionsData)
+  
+      // 데이터 구조화
+      const revisionsMap = new Map<number, Revision & { attachments: DocumentAttachment[] }>()
+      
+      revisionsData.forEach(({ revision, attachment }) => {
+        if (!revisionsMap.has(revision.id)) {
+          revisionsMap.set(revision.id, {
+            ...revision,
+            attachments: []
+          })
+        }
+  
+        const revisionData = revisionsMap.get(revision.id)!
+        
+        if (attachment) {
+          revisionData.attachments.push(attachment)
+        }
+      })
+  
+      return {
+        success: true,
+        data: Array.from(revisionsMap.values())
+      }
+    } catch (error) {
+      console.error("Error getting stage revisions:", error)
+      return {
+        success: false,
+        error: "리비전 조회 중 오류가 발생했습니다."
+      }
+    }
+  }
+  
+  export async function bulkUpdateStageStatus(
+    stageIds: number[], 
+    status: string
+  ): Promise<ApiResponse<void>> {
+    try {
+      const updateData: any = {
+        stageStatus: status,
+        updatedAt: new Date(),
+      }
+  
+      if (status === 'COMPLETED' || status === 'APPROVED') {
+        updateData.actualDate = new Date().toISOString().split('T')[0]
+      }
+  
+      await db.update(issueStages)
+        .set(updateData)
+        .where(
+          and(
+            ...stageIds.map(id => eq(issueStages.id, id))
+          )
+        )
+  
+      revalidatePath("/documents")
+      return {
+        success: true,
+        message: `${stageIds.length}개 스테이지가 업데이트되었습니다.`
+      }
+    } catch (error) {
+      console.error("Error bulk updating stage status:", error)
+      return {
+        success: false,
+        error: "일괄 상태 업데이트 중 오류가 발생했습니다."
+      }
+    }
+  }
\ No newline at end of file