path: root/lib/vendor-document-list/enhanced-document-service.ts

// 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: "일괄 상태 업데이트 중 오류가 발생했습니다."
      }
    }
  }