// enhanced-document-service.ts
"use server"

import { revalidatePath, unstable_cache } from "next/cache"
import { and, asc, desc, eq, ilike, or, count, avg, inArray } from "drizzle-orm"
import db from "@/db/db"
import { documentAttachments, 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"

// 스키마 타입 정의
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 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 offset = (input.page - 1) * input.perPage
  
      // 1. 사용자의 벤더(회사) ID 조회
      const [user] = await db
        .select({ companyId: users.companyId })
        .from(users)
        .where(eq(users.id, userId))
        .limit(1)
  
      if (!user?.companyId) {
        return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
      }
  
      // 2. 해당 벤더의 모든 계약 ID들 조회
      const vendorContracts = await db
        .select({ id: contracts.id })
        .from(contracts)
        .where(eq(contracts.vendorId, user.companyId))
  
      const contractIds = vendorContracts.map(c => c.id)
  
      if (contractIds.length === 0) {
        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),
        )
      }
  
      // 5. 최종 WHERE 조건 (계약 ID들로 필터링)
      const finalWhere = and(
        inArray(simplifiedDocumentsView.contractId, contractIds),
        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
  
        // 벤더 정보 조회
        const [vendorInfo] = await tx
          .select({
            vendorName: vendors.vendorName,
            vendorCode: vendors.vendorCode,
          })
          .from(contracts)
          .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
          .where(eq(contracts.id, contractIds[0]))
          .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 {
      // 사용자의 벤더 ID 조회
      const [user] = await db
        .select({ companyId: users.companyId })
        .from(users)
        .where(eq(users.id, userId))
        .limit(1)
  
      if (!user?.companyId) {
        return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
      }
  
      // 해당 벤더의 계약 ID들 조회
      const vendorContracts = await db
        .select({ id: contracts.id })
        .from(contracts)
        .where(eq(contracts.vendorId, user.companyId))
  
      const contractIds = vendorContracts.map(c => c.id)
  
      if (contractIds.length === 0) {
        return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
      }
  
      // DrawingKind별 통계 조회
      const documents = await db
        .select({
          drawingKind: simplifiedDocumentsView.drawingKind,
          contractId: simplifiedDocumentsView.contractId,
        })
        .from(simplifiedDocumentsView)
        .where(inArray(simplifiedDocumentsView.contractId, contractIds))
  
      // 통계 계산
      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 }
    }
  }