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, inArray, sql, ne } from "drizzle-orm"
import db from "@/db/db"
import { StageDocumentsView, documentAttachments, documentStagesOnlyView, documents, enhancedDocumentsView, issueStages, revisions, simplifiedDocumentsView, 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"
import { GetVendorShipDcoumentsSchema } from "./validations"
import { contracts, users, vendors } from "@/db/schema"
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { countDocumentStagesOnly, selectDocumentStagesOnly } from "./repository"
import { saveFile } from "../file-stroage"
import { syncService } from "./sync-service"

// 스키마 타입 정의
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
  }>
}

export interface GetDocumentsSchema {
  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 StageDocumentsView
    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 getEnhancedDocumentsShip(
  input: GetVendorShipDcoumentsSchema,
) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 고급 필터 처리
    const advancedWhere = filterColumns({
      table: simplifiedDocumentsView,
      filters: input.filters || [],
      joinOperator: input.joinOperator || "and",
    })

    // 전역 검색 처리
    let globalWhere
    if (input.search) {
      const searchTerm = `%${input.search}%`
      globalWhere = or(
        ilike(simplifiedDocumentsView.title, searchTerm),
        ilike(simplifiedDocumentsView.docNumber, searchTerm),
        ilike(simplifiedDocumentsView.projectCode, searchTerm),
        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
      )
    }

    // 최종 WHERE 조건
    const finalWhere = and(
      advancedWhere,
      globalWhere,
    )

    // 정렬 처리
    const orderBy = input.sort && input.sort.length > 0
      ? input.sort.map((item) =>
          item.desc
            ? desc(simplifiedDocumentsView[item.id])
            : asc(simplifiedDocumentsView[item.id])
        )
      : [desc(simplifiedDocumentsView.createdAt)]

    // 트랜잭션 실행
    const { data, total } = await db.transaction(async (tx) => {
      // 데이터 조회
      const data = await tx
        .select()
        .from(simplifiedDocumentsView)
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset)

      // 총 개수 조회
      const [{ total }] = await tx
        .select({ 
          total: count() 
        })
        .from(simplifiedDocumentsView)
        .where(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 }
  }
}

// contractId로 필터링이 필요한 경우를 위한 추가 함수
export async function getEnhancedDocumentsShipByContract(
  input: GetVendorShipDcoumentsSchema,
  contractId: number
) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 고급 필터 처리
    const advancedWhere = filterColumns({
      table: simplifiedDocumentsView,
      filters: input.filters || [],
      joinOperator: input.joinOperator || "and",
    })

    // 전역 검색 처리
    let globalWhere
    if (input.search) {
      const searchTerm = `%${input.search}%`
      globalWhere = or(
        ilike(simplifiedDocumentsView.title, searchTerm),
        ilike(simplifiedDocumentsView.docNumber, searchTerm),
        ilike(simplifiedDocumentsView.projectCode, searchTerm),
        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
      )
    }

    // 최종 WHERE 조건 (contractId 포함)
    const finalWhere = and(
      eq(simplifiedDocumentsView.contractId, contractId),
      advancedWhere,
      globalWhere,
    )

    // 정렬 처리
    const orderBy = input.sort && input.sort.length > 0
      ? input.sort.map((item) =>
          item.desc
            ? desc(simplifiedDocumentsView[item.id])
            : asc(simplifiedDocumentsView[item.id])
        )
      : [desc(simplifiedDocumentsView.createdAt)]

    // 트랜잭션 실행
    const { data, total } = await db.transaction(async (tx) => {
      // 데이터 조회
      const data = await tx
        .select()
        .from(simplifiedDocumentsView)
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset)

      // 총 개수 조회
      const [{ total }] = await tx
        .select({ 
          total: count() 
        })
        .from(simplifiedDocumentsView)
        .where(finalWhere)

      return { data, total }
    })

    const pageCount = Math.ceil(total / input.perPage)

    return { data, pageCount, total }
  } catch (err) {
    console.error("Error fetching enhanced documents by contract:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

// drawingKind별로 문서를 조회하는 함수
export async function getEnhancedDocumentsShipByDrawingKind(
  input: GetVendorShipDcoumentsSchema,
  drawingKind: 'B3' | 'B4' | 'B5'
) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 고급 필터 처리
    const advancedWhere = filterColumns({
      table: simplifiedDocumentsView,
      filters: input.filters || [],
      joinOperator: input.joinOperator || "and",
    })

    // 전역 검색 처리
    let globalWhere
    if (input.search) {
      const searchTerm = `%${input.search}%`
      globalWhere = or(
        ilike(simplifiedDocumentsView.title, searchTerm),
        ilike(simplifiedDocumentsView.docNumber, searchTerm),
        ilike(simplifiedDocumentsView.projectCode, searchTerm),
        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
      )
    }

    // 최종 WHERE 조건 (drawingKind 포함)
    const finalWhere = and(
      eq(simplifiedDocumentsView.drawingKind, drawingKind),
      advancedWhere,
      globalWhere,
    )

    // 정렬 처리
    const orderBy = input.sort && input.sort.length > 0
      ? input.sort.map((item) =>
          item.desc
            ? desc(simplifiedDocumentsView[item.id])
            : asc(simplifiedDocumentsView[item.id])
        )
      : [desc(simplifiedDocumentsView.createdAt)]

    // 트랜잭션 실행
    const { data, total } = await db.transaction(async (tx) => {
      // 데이터 조회
      const data = await tx
        .select()
        .from(simplifiedDocumentsView)
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset)

      // 총 개수 조회
      const [{ total }] = await tx
        .select({ 
          total: count() 
        })
        .from(simplifiedDocumentsView)
        .where(finalWhere)

      return { data, total }
    })

    const pageCount = Math.ceil(total / input.perPage)

    return { data, pageCount, total }
  } catch (err) {
    console.error(`Error fetching enhanced documents for drawing kind ${drawingKind}:`, err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

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


  export async function getUserVendorDocuments(
    userId: number,
    input: GetVendorShipDcoumentsSchema
  ) {
    try {

      const session = await getServerSession(authOptions)
      if (!session?.user?.id) {
        throw new Error("인증이 필요합니다.")
      }
  
      const companyId = session?.user?.companyId;

      const offset = (input.page - 1) * input.perPage
  
      if (!companyId) {
        return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
      }
     
      // 3. 고급 필터 처리
      const advancedWhere = filterColumns({
        table: simplifiedDocumentsView,
        filters: input.filters || [],
        joinOperator: input.joinOperator || "and",
      })
  
      // 4. 전역 검색 처리
      let globalWhere
      if (input.search) {
        const searchTerm = `%${input.search}%`
        globalWhere = or(
          ilike(simplifiedDocumentsView.title, searchTerm),
          ilike(simplifiedDocumentsView.docNumber, searchTerm),
          ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
        )
      }

      // 4.5. B4 필터 처리 (drawingMoveGbn 기반)
      let b4FilterWhere
      if (input.b4FilterType && input.b4FilterType !== 'all') {
        if (input.b4FilterType === 'gtt_deliverable') {
          b4FilterWhere = eq(simplifiedDocumentsView.drawingMoveGbn, '도면입수')
        } else if (input.b4FilterType === 'shi_input') {
          b4FilterWhere = eq(simplifiedDocumentsView.drawingMoveGbn, '도면제출')
        }
      }

      // 4.6. Project Code 필터 처리
      let projectCodeWhere
      if (input.projectCode && input.projectCode !== 'all') {
        projectCodeWhere = eq(simplifiedDocumentsView.projectCode, input.projectCode)
      }
  
      // 5. 최종 WHERE 조건 (계약 ID들로 필터링)
      const finalWhere = and(
        eq(simplifiedDocumentsView.vendorId, Number(companyId)),
        advancedWhere,
        globalWhere,
        b4FilterWhere,
        projectCodeWhere,
      )
  
      // 6. 정렬 처리
      const orderBy = input.sort && input.sort.length > 0
        ? input.sort.map((item) =>
            item.desc
              ? desc(simplifiedDocumentsView[item.id])
              : asc(simplifiedDocumentsView[item.id])
          )
        : [desc(simplifiedDocumentsView.createdAt)]
  
      // 7. 트랜잭션 실행
      const { data, total, drawingKind, vendorInfo } = await db.transaction(async (tx) => {
        // 데이터 조회
        const data = await tx
          .select()
          .from(simplifiedDocumentsView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .limit(input.perPage)
          .offset(offset)
  
        // 총 개수 조회
        const [{ total }] = await tx
          .select({ 
            total: count() 
          })
          .from(simplifiedDocumentsView)
          .where(finalWhere)
  
        // DrawingKind 분석 (첫 번째 문서의 drawingKind 사용)
        const drawingKind = data.length > 0 ? data[0].drawingKind : null
  
        // 벤더 정보 조회
        const [vendorInfo] = await tx
          .select({
            vendorName: vendors.vendorName,
            vendorCode: vendors.vendorCode,
          })
          .from(vendors)
          .where(eq(vendors.id, companyId))
          .limit(1)
  
        return { data, total, drawingKind, vendorInfo }
      })
  
      const pageCount = Math.ceil(total / input.perPage)
  
      return { 
        data, 
        pageCount, 
        total, 
        drawingKind: drawingKind as 'B3' | 'B4' | 'B5' | null,
        vendorInfo: vendorInfo || null
      }
    } catch (err) {
      console.error("Error fetching user vendor documents:", err)
      return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
    }
  }
  
  /**
   * DrawingKind별 문서 통계 조회
   */
  export async function getUserVendorDocumentStats(userId: number) {
    try {

      const session = await getServerSession(authOptions)
      if (!session?.user?.id) {
        throw new Error("인증이 필요합니다.")
      }
  
      const companyId = session?.user?.companyId;
  
    if (!companyId) {
      return { 
        stats: {}, 
        totalDocuments: 0, 
        primaryDrawingKind: null, 
        b4Stats: { gttDeliverableCount: 0, shiInputCount: 0 },
        projectCodeStats: []
      }
    }
    
    // DrawingKind별 통계 조회 (B4의 경우 drawingMoveGbn도 함께 조회, projectCode도 추가)
    const documents = await db
      .select({
        drawingKind: simplifiedDocumentsView.drawingKind,
        drawingMoveGbn: simplifiedDocumentsView.drawingMoveGbn,
        projectCode: simplifiedDocumentsView.projectCode,
        projectId: simplifiedDocumentsView.projectId,
      })
      .from(simplifiedDocumentsView)
      .where(eq(simplifiedDocumentsView.vendorId, Number(companyId)))

    // 통계 계산
    const stats = documents.reduce((acc, doc) => {
      if (doc.drawingKind) {
        acc[doc.drawingKind] = (acc[doc.drawingKind] || 0) + 1
      }
      return acc
    }, {} as Record<string, number>)

    // B4 세부 통계 계산 (GTT Deliverable vs SHI Input)
    const b4Stats = {
      gttDeliverableCount: documents.filter(doc =>
        doc.drawingKind === 'B4' && doc.drawingMoveGbn === '도면입수'
      ).length,
      shiInputCount: documents.filter(doc =>
        doc.drawingKind === 'B4' && doc.drawingMoveGbn === '도면제출'
      ).length,
    }

    // 🔥 프로젝트별 B4 통계 계산 (전체 개수를 기반으로)
    const projectB4StatsMap = new Map<string, { gttDeliverableCount: number, shiInputCount: number }>()
    documents.forEach(doc => {
      if (doc.drawingKind === 'B4') {
        const code = doc.projectCode || 'Unknown'
        const current = projectB4StatsMap.get(code) || { gttDeliverableCount: 0, shiInputCount: 0 }
        if (doc.drawingMoveGbn === '도면입수') {
          current.gttDeliverableCount++
        } else if (doc.drawingMoveGbn === '도면제출') {
          current.shiInputCount++
        }
        projectB4StatsMap.set(code, current)
      }
    })

    // 프로젝트별 B4 통계를 배열로 변환
    const projectB4Stats = Array.from(projectB4StatsMap.entries())
      .sort((a, b) => a[0].localeCompare(b[0]))
      .map(([code, stats]) => ({ code, ...stats }))

    // 🔥 프로젝트 코드별 통계 계산
    const projectCodeMap = new Map<string, { count: number, projectId: number | null }>()
    documents.forEach(doc => {
      const code = doc.projectCode || 'Unknown'
      const current = projectCodeMap.get(code)
      if (current) {
        current.count++
      } else {
        projectCodeMap.set(code, { count: 1, projectId: doc.projectId })
      }
    })
    
    // 정렬된 배열로 변환 (프로젝트 코드 알파벳순)
    const projectCodeStats = Array.from(projectCodeMap.entries())
      .sort((a, b) => a[0].localeCompare(b[0]))
      .map(([code, { count, projectId }]) => ({ code, count, projectId }))

    // 가장 많은 DrawingKind 찾기
    const primaryDrawingKind = Object.entries(stats)
      .sort(([,a], [,b]) => b - a)[0]?.[0] as 'B3' | 'B4' | 'B5' | undefined

    return {
      stats,
      totalDocuments: documents.length,
      primaryDrawingKind: primaryDrawingKind || null,
      b4Stats, // B4 세부 통계 추가
      projectCodeStats, // 🔥 프로젝트 코드 통계 추가
      projectB4Stats, // 🔥 프로젝트별 B4 통계 추가
    }
    } catch (err) {
      console.error("Error fetching user vendor document stats:", err)
      return {
        stats: {},
        totalDocuments: 0,
        primaryDrawingKind: null,
        b4Stats: { gttDeliverableCount: 0, shiInputCount: 0 },
        projectCodeStats: [],
        projectB4Stats: []
      }
    }
  }

  export async function getUserVendorDocumentsAll(
    userId: number,
    input: GetVendorShipDcoumentsSchema
  ) {
    try {

      const session = await getServerSession(authOptions)
      if (!session?.user?.id) {
        throw new Error("인증이 필요합니다.")
      }
  


      const offset = (input.page - 1) * input.perPage
  

     
      // 3. 고급 필터 처리
      const advancedWhere = filterColumns({
        table: simplifiedDocumentsView,
        filters: input.filters || [],
        joinOperator: input.joinOperator || "and",
      })
  
      // 4. 전역 검색 처리
      let globalWhere
      if (input.search) {
        const searchTerm = `%${input.search}%`
        globalWhere = or(
          ilike(simplifiedDocumentsView.title, searchTerm),
          ilike(simplifiedDocumentsView.docNumber, searchTerm),
          ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
        )
      }
  
      // 5. 최종 WHERE 조건 (계약 ID들로 필터링)
      const finalWhere = and(
        advancedWhere,
        globalWhere,
      )
  
      // 6. 정렬 처리
      const orderBy = input.sort && input.sort.length > 0
        ? input.sort.map((item) =>
            item.desc
              ? desc(simplifiedDocumentsView[item.id])
              : asc(simplifiedDocumentsView[item.id])
          )
        : [desc(simplifiedDocumentsView.createdAt)]
  
      // 7. 트랜잭션 실행
      const { data, total, drawingKind, vendorInfo } = await db.transaction(async (tx) => {
        // 데이터 조회
        const data = await tx
          .select()
          .from(simplifiedDocumentsView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .limit(input.perPage)
          .offset(offset)
  
        // 총 개수 조회
        const [{ total }] = await tx
          .select({ 
            total: count() 
          })
          .from(simplifiedDocumentsView)
          .where(finalWhere)
  
        // DrawingKind 분석 (첫 번째 문서의 drawingKind 사용)
        const drawingKind = data.length > 0 ? data[0].drawingKind : null
  
        // 벤더 정보 조회
       
  
        return { data, total, drawingKind }
      })
  
      const pageCount = Math.ceil(total / input.perPage)
  
      return { 
        data, 
        pageCount, 
        total, 
        drawingKind: drawingKind as 'B3' | 'B4' | 'B5' | null,
      }
    } catch (err) {
      console.error("Error fetching user vendor documents:", err)
      return { data: [], pageCount: 0, total: 0, drawingKind: null }
    }
  }
  
  /**
   * DrawingKind별 문서 통계 조회
   */
  export async function getUserVendorDocumentStatsAll(userId: number) {
    try {

      const session = await getServerSession(authOptions)
      if (!session?.user?.id) {
        throw new Error("인증이 필요합니다.")
      }
  
 
      // DrawingKind별 통계 조회
      const documents = await db
        .select({
          drawingKind: simplifiedDocumentsView.drawingKind,
        })
        .from(simplifiedDocumentsView)
  
      // 통계 계산
      const stats = documents.reduce((acc, doc) => {
        if (doc.drawingKind) {
          acc[doc.drawingKind] = (acc[doc.drawingKind] || 0) + 1
        }
        return acc
      }, {} as Record<string, number>)
  
      // 가장 많은 DrawingKind 찾기
      const primaryDrawingKind = Object.entries(stats)
        .sort(([,a], [,b]) => b - a)[0]?.[0] as 'B3' | 'B4' | 'B5' | undefined
  
      return {
        stats,
        totalDocuments: documents.length,
        primaryDrawingKind: primaryDrawingKind || null
      }
    } catch (err) {
      console.error("Error fetching user vendor document stats:", err)
      return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
    }
  }


  export interface UpdateRevisionInput {
    revisionId: number
    revision: string // ✅ revision 필드 추가
    comment?: string | null
    usage: string
    usageType?: string | null
  }
  
  export interface UpdateRevisionResult {
    success: boolean
    message?: string
    error?: string
    updatedRevision?: any
  }
  
  export async function updateRevisionAction(
    input: UpdateRevisionInput
  ): Promise<UpdateRevisionResult> {
    try {
      const { revisionId, revision, comment, usage, usageType } = input
  
      // 1. 리비전 존재 여부 확인
      const existingRevision = await db
        .select()
        .from(revisions)
        .where(eq(revisions.id, revisionId))
        .limit(1)
  
      if (!existingRevision || existingRevision.length === 0) {
        return {
          success: false,
          error: "Revision not found"
        }
      }
  
      // 2. 동일한 revision 번호가 같은 문서에 이미 존재하는지 확인 (자기 자신 제외)
      const duplicateRevision = await db
        .select()
        .from(revisions)
        .innerJoin(issueStages, eq(revisions.issueStageId, issueStages.id))
        .where(
          and(
            eq(revisions.revision, revision.trim()),
            eq(issueStages.documentId, existingRevision[0].issueStageId), // 같은 문서 내에서
            ne(revisions.id, revisionId) // 자기 자신 제외
          )
        )
        .limit(1)
  
      if (duplicateRevision && duplicateRevision.length > 0) {
        return {
          success: false,
          error: `Revision "${revision.trim()}" already exists in this document`
        }
      }
  
      // 3. 첨부파일이 처리된 상태인지 확인 (수정 가능 여부 체크)
      const attachments = await db
        .select()
        .from(documentAttachments)
        .where(eq(documentAttachments.revisionId, revisionId))
  
      const hasProcessedFiles = attachments.some(att => 
        att.dolceFilePath && att.dolceFilePath.trim() !== ''
      )
  
      if (hasProcessedFiles) {
        return {
          success: false,
          error: "Cannot edit revision with processed files"
        }
      }
  
      // 4. 리비전 업데이트
      const [updatedRevision] = await db
        .update(revisions)
        .set({
          revision: revision.trim(), // ✅ revision 필드 업데이트 추가
          comment: comment?.trim() || null,
          usage: usage.trim(),
          usageType: usageType?.trim() || null,
          updatedAt: new Date(),
        })
        .where(eq(revisions.id, revisionId))
        .returning()
  
      revalidatePath("/partners/document-list-ship") // ✅ 경로 오타 수정
  
      return {
        success: true,
        message: `Revision ${revision.trim()} updated successfully`, // ✅ 새 revision 값 사용
        updatedRevision
      }
  
    } catch (error) {
      console.error("❌ Revision update server action error:", error)
  
      return {
        success: false,
        error: error instanceof Error ? error.message : "Failed to update revision"
      }
    }
  }
  // 삭제 서버 액션도 함께 만들어드릴게요
  export interface DeleteRevisionInput {
    revisionId: number
  }
  
  export interface DeleteRevisionResult {
    success: boolean
    message?: string
    error?: string
    deletedRevisionId?: number
    deletedAttachmentsCount?: number
  }
  
  export async function deleteRevisionAction(
    input: DeleteRevisionInput
  ): Promise<DeleteRevisionResult> {
    try {
      const { revisionId } = input
  
      // 1. 리비전과 첨부파일 정보 조회
      const revision = await db
        .select()
        .from(revisions)
        .where(eq(revisions.id, revisionId))
        .limit(1)
  
      if (!revision || revision.length === 0) {
        return {
          success: false,
          error: "Revision not found"
        }
      }
  
  
      // 5. 리비전 삭제
      await db
        .delete(revisions)
        .where(eq(revisions.id, revisionId))
  
      // 6. 캐시 재검증
      revalidatePath("/parnters/document-list-ship")
  
      return {
        success: true,
        message: `Revision ${revision[0].revision} deleted successfully`,
        deletedRevisionId: revisionId,
        deletedAttachmentsCount: 0 // revisionAttachments.length
      }
  
    } catch (error) {
      console.error("❌ Revision delete server action error:", error)
      
      return {
        success: false,
        error: error instanceof Error ? error.message : "Failed to delete revision"
      }
    }
  }


  interface UploadResult {
    docNumber: string
    revision: string
    success: boolean
    message?: string
    error?: string
  }
  
  interface BulkUploadResult {
    success: boolean
    successCount?: number
    failCount?: number
    results?: UploadResult[]
    error?: string
  }
  
  export async function bulkUploadB4Documents(formData: FormData): Promise<BulkUploadResult> {
    try {
      const session = await getServerSession(authOptions)
      if (!session?.user) {
        return { success: false, error: "인증이 필요합니다" }
      }
  
      const projectId = formData.get("projectId") as string
      const fileCount = parseInt(formData.get("fileCount") as string)
  
      if (!projectId) {
        return { success: false, error: "프로젝트를 선택해주세요" }
      }

      // vendorId 가져오기
      const vendorId = session.user.companyId ? Number(session.user.companyId) : 0
      const userId = session.user.id ? parseInt(session.user.id) : 0
      const userName = session.user.name || "System"
  
      const results: UploadResult[] = []
      let successCount = 0
      let failCount = 0
  
      // 문서번호별로 그룹화
      const fileGroups = new Map<string, Array<{
        file: File
        revision: string
        index: number
      }>>()
  
      // 파일들을 문서번호별로 그룹화
      for (let i = 0; i < fileCount; i++) {
        const file = formData.get(`file_${i}`) as File
        const docNumber = formData.get(`docNumber_${i}`) as string
        const revision = formData.get(`revision_${i}`) as string
  
        if (!file || !docNumber) continue
  
        if (!fileGroups.has(docNumber)) {
          fileGroups.set(docNumber, [])
        }
  
        fileGroups.get(docNumber)!.push({
          file,
          revision: revision || "00",
          index: i
        })
      }
  
      // 각 문서번호 그룹 처리
      for (const [docNumber, files] of fileGroups.entries()) {
        try {
          // 문서가 존재하는지 확인
          const existingDoc = await db.query.documents.findFirst({
            where: and(
              eq(documents.docNumber, docNumber),
              eq(documents.projectId, parseInt(projectId))
            )
          })
  
          if (!existingDoc) {
            // 문서가 없으면 모든 파일 스킵
            for (const fileInfo of files) {
              results.push({
                docNumber,
                revision: fileInfo.revision,
                success: false,
                error: `문서번호 ${docNumber}가 존재하지 않습니다`
              })
              failCount++
            }
            continue
          }
  
          // 기존 스테이지 조회
          const existingStages = await db.query.issueStages.findMany({
            where: eq(issueStages.documentId, existingDoc.id)
          })
  
          const preStage = existingStages.find(s => s.stageName === 'GTT → SHI (For Pre.DWG)')
          const workStage = existingStages.find(s => s.stageName === 'GTT → SHI (For Work.DWG)')
  
          // 파일별 처리 (첫 번째 리비전은 Pre.DWG, 나머지는 Work.DWG)
          for (let fileIndex = 0; fileIndex < files.length; fileIndex++) {
            const fileInfo = files[fileIndex]
            let targetStageId: number
  
            try {
              // 스테이지 결정 및 생성
              if (fileIndex === 0) {
                // 첫 번째 리비전 - Pre.DWG 스테이지
                if (preStage) {
                  targetStageId = preStage.id
                } else {
                  // Pre.DWG 스테이지 생성
                  const [newStage] = await db.insert(issueStages).values({
                    documentId: existingDoc.id,
                    stageName: 'GTT → SHI (For Pre.DWG)',
                    stageOrder: 1,
                    stageStatus: 'PLANNED',
                  }).returning()
                  targetStageId = newStage.id
                }
              } else {
                // 나머지 리비전 - Work.DWG 스테이지
                if (workStage) {
                  targetStageId = workStage.id
                } else {
                  // Work.DWG 스테이지 생성
                  const [newStage] = await db.insert(issueStages).values({
                    documentId: existingDoc.id,
                    stageName: 'GTT → SHI (For Work.DWG)',
                    stageOrder: 2,
                    stageStatus: 'PLANNED',
                  }).returning()
                  targetStageId = newStage.id
                }
              }
  
              // 같은 리비전이 이미 있는지 확인
              const existingRevision = await db.query.revisions.findFirst({
                where: and(
                  eq(revisions.issueStageId, targetStageId),
                  eq(revisions.revision, fileInfo.revision)
                )
              })
  
              let revisionId: number
  
              if (existingRevision) {
                // 기존 리비전 사용
                revisionId = existingRevision.id
              } else {
                // 새 리비전 생성
                const [newRevision] = await db.insert(revisions).values({
                  issueStageId: targetStageId,
                  revision: fileInfo.revision,
                  uploaderType: "vendor",
                  uploaderName: session.user.name || "System",
                  uploadedAt: new Date().toISOString().split('T')[0],
                  submittedDate: new Date().toISOString().split('T')[0],
                  revisionStatus: 'SUBMITTED',
                }).returning()
                revisionId = newRevision.id

                // ✅ change_logs에 revision 생성 기록
                if (vendorId > 0) {
                  try {
                    await syncService.logChange(
                      vendorId,
                      'revision',
                      revisionId,
                      'CREATE',
                      {
                        issueStageId: targetStageId,
                        revision: fileInfo.revision,
                        uploaderType: "vendor",
                        uploaderName: session.user.name || "System",
                        revisionStatus: 'SUBMITTED',
                      },
                      null,
                      userId,
                      userName,
                      ['DOLCE']
                    )
                  } catch (logError) {
                    console.error('Failed to log revision change:', logError)
                    // 로그 실패는 업로드 실패로 처리하지 않음
                  }
                }
              }
  
              // 파일 저장
              const saveResult = await saveFile({
                file: fileInfo.file,
                directory: `documents/${existingDoc.id}/revisions/${revisionId}`,
                originalName: fileInfo.file.name,
                userId: session.user.id
              })
  
              if (!saveResult.success) {
                throw new Error(saveResult.error || "파일 저장 실패")
              }
  
              // 첨부파일 정보 저장
              const [newAttachment] = await db.insert(documentAttachments).values({
                revisionId,
                fileName: fileInfo.file.name,
                filePath: saveResult.publicPath!,
                fileType: fileInfo.file.type,
                fileSize: fileInfo.file.size,
              }).returning()

              // ✅ change_logs에 attachment 생성 기록
              if (vendorId > 0 && newAttachment?.id) {
                try {
                  await syncService.logChange(
                    vendorId,
                    'attachment',
                    newAttachment.id,
                    'CREATE',
                    {
                      revisionId,
                      fileName: fileInfo.file.name,
                      filePath: saveResult.publicPath!,
                      fileType: fileInfo.file.type,
                      fileSize: fileInfo.file.size,
                    },
                    null,
                    userId,
                    userName,
                    ['DOLCE']
                  )
                } catch (logError) {
                  console.error('Failed to log attachment change:', logError)
                  // 로그 실패는 업로드 실패로 처리하지 않음
                }
              }
  
              results.push({
                docNumber,
                revision: fileInfo.revision,
                success: true,
                message: `${fileIndex === 0 ? 'Pre.DWG' : 'Work.DWG'} 스테이지에 업로드 완료`
              })
              successCount++
  
            } catch (fileError) {
              results.push({
                docNumber,
                revision: fileInfo.revision,
                success: false,
                error: fileError instanceof Error ? fileError.message : "파일 처리 실패"
              })
              failCount++
            }
          }
  
        } catch (docError) {
          // 문서 그룹 전체 에러
          for (const fileInfo of files) {
            results.push({
              docNumber,
              revision: fileInfo.revision,
              success: false,
              error: docError instanceof Error ? docError.message : "문서 처리 실패"
            })
            failCount++
          }
        }
      }
  
      revalidatePath('/documents')
      
      return {
        success: true,
        successCount,
        failCount,
        results
      }
  
    } catch (error) {
      console.error("Bulk upload error:", error)
      return {
        success: false,
        error: error instanceof Error ? error.message : "업로드 중 오류가 발생했습니다"
      }
    }
  }