'use server'

import db  from "@/db/db"
import { 
  gtcClauses, 
  gtcClausesTreeView,
  gtcVendorClausesView,
  gtcClausesWithVendorView,
  type GtcClause, 
  type GtcClauseTreeView,
  type NewGtcClause, 
  gtcNegotiationHistory,
  gtcVendorClauses,
  gtcVendorDocuments,
  GtcVendorClause
} from "@/db/schema/gtc"
import { users } from "@/db/schema/users"
import { and, asc, count, desc, eq, ilike, or, sql, gt, lt, inArray, like  } from "drizzle-orm"
import { unstable_cache } from "next/cache"
import { filterColumns } from "@/lib/filter-columns"
import type {
  GetGtcClausesSchema,
  CreateGtcClauseSchema,
  UpdateGtcClauseSchema,
  ReorderGtcClausesSchema,
  BulkUpdateGtcClausesSchema,
  GenerateVariableNamesSchema,
  GetGtcVendorClausesSchema,
  UpdateVendorGtcClauseSchema,
} from "@/lib/gtc-contract/gtc-clauses/validations"
import { decryptWithServerAction } from "@/components/drm/drmUtils"
import { saveDRMFile } from "@/lib/file-stroage"
import { vendors } from "@/db/schema"

interface ClauseImage {
  id: string
  url: string
  fileName: string
  size: number
  savedName?: string
  mimeType?: string
  width?: number
  height?: number
  hash?: string
}

/**
 * GTC 조항 목록 조회 (계층구조)
 */
export async function getGtcClauses(input: GetGtcClausesSchema & { documentId: number }) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage

        // 문서 ID 필터 (필수)
        const documentWhere = eq(gtcClausesTreeView.documentId, input.documentId)

        // 고급 필터
        const advancedWhere = filterColumns({
          table: gtcClausesTreeView,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // 전역 검색
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            ilike(gtcClausesTreeView.itemNumber, s),
            ilike(gtcClausesTreeView.subtitle, s),
            ilike(gtcClausesTreeView.content, s),
            ilike(gtcClausesTreeView.category, s)
          )
        }

        // 카테고리 필터
        let categoryWhere
        if (input.category) {
          categoryWhere = ilike(gtcClausesTreeView.category, `%${input.category}%`)
        }

        // 뎁스 필터
        let depthWhere
        if (input.depth > 0) {
          depthWhere = eq(gtcClausesTreeView.depth, input.depth)
        }

        // 부모 필터
        let parentWhere
        if (input.parentId > 0) {
          parentWhere = eq(gtcClausesTreeView.parentId, input.parentId)
        }

        // 최종 where 조건
        const finalWhere = and(
          documentWhere,
          advancedWhere,
          globalWhere,
          categoryWhere,
          depthWhere,
          parentWhere,
          eq(gtcClausesTreeView.isActive, true)
        )

        // 정렬
        const orderBy =
        input.sort.length > 0
          ? input.sort.map((item) => {
              const column = gtcClausesTreeView[item.id as keyof typeof gtcClausesTreeView]
              const result = item.desc ? desc(column) : asc(column)
              return result
            })
          : [asc(gtcClausesTreeView.itemNumber)]
      

        // 데이터 조회
        const { data, total } = await db.transaction(async (tx) => {
          const data = await tx
            .select()
            .from(gtcClausesTreeView)
            .where(finalWhere)
            .orderBy(...orderBy)
            .limit(input.perPage)
            .offset(offset)

          const total = await countGtcClauses(tx, finalWhere)
          return { data, total }
        })

        const pageCount = Math.ceil(total / input.perPage)
        return { data, pageCount }
      } catch (err) {
        console.error("Error fetching GTC clauses:", err)
        return { data: [], pageCount: 0 }
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: [`gtc-clauses-${input.documentId}`],
    }
  )()
}

/**
 * 계층구조 트리 형태로 GTC 조항 조회
 */
export async function getGtcClausesTree(documentId: number) {
  return unstable_cache(
    async () => {
      try {
        const allClauses = await db
          .select()
          .from(gtcClausesTreeView)
          .where(
            and(
              eq(gtcClausesTreeView.documentId, documentId),
              eq(gtcClausesTreeView.isActive, true)
            )
          )
          .orderBy(asc(gtcClausesTreeView.sortOrder), asc(gtcClausesTreeView.depth))

        // 계층구조로 변환
        return buildClausesTree(allClauses)
      } catch (err) {
        console.error("Error fetching GTC clauses tree:", err)
        return []
      }
    },
    [`gtc-clauses-tree-${documentId}`],
    {
      revalidate: 3600,
      tags: [`gtc-clauses-${documentId}`],
    }
  )()
}

/**
 * 단일 GTC 조항 조회
 */
export async function getGtcClauseById(id: number) {
  return unstable_cache(
    async () => {
      try {
        const [clause] = await db
          .select()
          .from(gtcClausesTreeView)
          .where(eq(gtcClausesTreeView.id, id))
          .limit(1)

        return clause || null
      } catch (err) {
        console.error("Error fetching GTC clause:", err)
        return null
      }
    },
    [`gtc-clause-${id}`],
    {
      revalidate: 3600,
      tags: [`gtc-clause-${id}`],
    }
  )()
}

// helper: 이미지 저장
async function saveClauseImagesFromFormData(
  files: File[],
  documentId: number,
  userId: number
) {
  const saved: ClauseImage[] = []

  for (const file of files) {
    // DRM 복호화 + 저장
    const result = await saveDRMFile(file, decryptWithServerAction, `gtc/${documentId}`, String(userId))
    if (!result.success || !result.publicPath) {
      throw new Error(result.error || "이미지 저장 실패")
    }

    // (선택) 해상도 파악
    let width: number | undefined
    let height: number | undefined
    try {
      // 이미지일 때만
      if (file.type.startsWith("image/")) {
        // 서버에서 해상도를 바로 읽으려면 sharp 등 라이브러리를 쓰세요.
        // 여기서는 비용을 줄이기 위해 생략 (원하면 sharp로 추후 보강)
      }
    } catch { /* no-op */ }

    saved.push({
      id: uuid(),
      url: result.publicPath,        // <- 뷰어/Docx에서 그대로 fetch 가능한 경로
      fileName: file.name,
      savedName: result.fileName,
      mimeType: file.type,
      size: result.fileSize ?? file.size,
      width,
      height,
      hash: undefined,
    })
  }

  return saved
}

// ✅ 새 서버액션: 파일 포함 생성
export async function createGtcClauseWithUploads(formData: FormData) {
  try {
    const documentId = Number(formData.get("documentId"))
    const parentId = formData.get("parentId") ? Number(formData.get("parentId")) : null
    const itemNumber = String(formData.get("itemNumber") || "")
    const category = (formData.get("category") as string) || ""
    const subtitle = String(formData.get("subtitle") || "")
    const content = (formData.get("content") as string) || ""
    const sortOrder = Number(formData.get("sortOrder") || 0)
    const editReason = (formData.get("editReason") as string) || ""
    const createdById = Number(formData.get("createdById"))

    // 첨부 파일들
    const files: File[] = []
    for (const [key, value] of formData.entries()) {
      if (key.startsWith("images[") && value instanceof File && value.size > 0) {
        files.push(value)
      }
    }

    // depth 계산
    let depth = 0
    if (parentId) {
      const parent = await db.query.gtcClauses.findFirst({ where: eq(gtcClauses.id, parentId) })
      if (parent) depth = (parent.depth ?? 0) + 1
    }

    console.log(files.length, "파일 확인")
    // 파일 저장
    const images = files.length > 0
      ? await saveClauseImagesFromFormData(files, documentId, createdById)
      : []

    const newClause: NewGtcClause = {
      documentId,
      parentId,
      itemNumber,
      category,
      subtitle,
      content: content.trim() || null,
      images: images.length ? images : null,
      sortOrder: sortOrder.toString(),
      depth,
      createdById,
      updatedById: createdById,
      editReason,
    }

    const [result] = await db.insert(gtcClauses).values(newClause).returning()
    await revalidateGtcClausesCaches(documentId)

    return { data: result, error: null }
  } catch (error) {
    console.error("Error createGtcClauseWithUploads:", error)
    return { data: null, error: "조항 생성 중 오류가 발생했습니다." }
  }
}




function buildClauseDir(documentId: number) {
  const now = new Date()
  const y = now.getFullYear()
  const m = String(now.getMonth() + 1).padStart(2, "0")
  return `gtc/${documentId}/${y}/${m}`
}

async function saveUploads(files: File[], documentId: number, userId: number) {
  const dir = buildClauseDir(documentId)
  const saved: ClauseImage[] = []
  for (const file of files) {
    if (!file || file.size === 0) continue
    const r = await saveDRMFile(file, decryptWithServerAction, dir, String(userId))
    if (!r.success || !r.publicPath) {
      throw new Error(r.error || "파일 저장 실패")
    }
    saved.push({
      id: uuid(),
      url: r.publicPath,
      fileName: file.name,
      savedName: r.fileName,
      mimeType: file.type,
      size: r.fileSize ?? file.size,
    })
  }
  return saved
}


export async function updateGtcClauseWithUploads(formData: FormData) {
  try {
    const id = Number(formData.get("id"))
    const documentId = Number(formData.get("documentId"))
    const itemNumber = (formData.get("itemNumber") as string) || ""
    const category = (formData.get("category") as string) || ""
    const subtitle = (formData.get("subtitle") as string) || ""
    const content = (formData.get("content") as string) || ""
    const sortOrderStr = (formData.get("sortOrder") as string) || ""
    const isActive = String(formData.get("isActive")).toLowerCase() !== "false"
    const editReason = (formData.get("editReason") as string) || ""
    const updatedById = Number(formData.get("updatedById"))

    // 기존 조항 조회
    const existing = await db.query.gtcClauses.findFirst({ where: eq(gtcClauses.id, id) })
    if (!existing) return { data: null, error: "조항을 찾을 수 없습니다." }

    // 삭제될 이미지 목록
    let removedImageIds: string[] = []
    try {
      const raw = (formData.get("removedImageIds") as string) || "[]"
      removedImageIds = JSON.parse(raw)
    } catch {
      removedImageIds = []
    }

    // 신규 파일 수집
    const uploadFiles: File[] = []
    for (const [key, value] of formData.entries()) {
      if (key.startsWith("images[") && value instanceof File && value.size > 0) {
        uploadFiles.push(value)
      }
    }

    // 기존 이미지
    const existingImages: ClauseImage[] = Array.isArray(existing.images) ? (existing.images as any) : (existing.images ? JSON.parse(JSON.stringify(existing.images)) : [])

    // 삭제 반영
    const kept = existingImages.filter(img => !removedImageIds.includes(img.id))

    // 신규 저장
    const newImages = uploadFiles.length ? await saveUploads(uploadFiles, documentId, updatedById) : []

    const nextImages = [...kept, ...newImages]
    const updateData: Partial<GtcClause> = {
      itemNumber,
      category,
      subtitle,
      content: content.trim() ? content.trim() : null,
      sortOrder: sortOrderStr ? String(sortOrderStr) : existing.sortOrder,
      isActive,
      images: nextImages.length ? nextImages : null,
      updatedById,
      updatedAt: new Date(),
      editReason,
    }

    // itemNumber 변경 시 fullPath 갱신 (간단 규칙)
    if (itemNumber && itemNumber !== existing.itemNumber) {
      updateData.fullPath = itemNumber
    }

    const [result] = await db.update(gtcClauses).set(updateData).where(eq(gtcClauses.id, id)).returning()

    await revalidateGtcClausesCaches(existing.documentId)

    return { data: result, error: null }
  } catch (error) {
    console.error("Error updateGtcClauseWithUploads:", error)
    return { data: null, error: "조항 수정 중 오류가 발생했습니다." }
  }
}

/**
 * GTC 조항 생성
 */
export async function createGtcClause(
  input: CreateGtcClauseSchema & { createdById: number }
) {
  try {
    let depth = 0
    
    if (input.parentId) {
      const parent = await db.query.gtcClauses.findFirst({
        where: eq(gtcClauses.id, input.parentId),
      })
      if (parent) {
        depth = parent.depth + 1
      }
    }

    const newClause: NewGtcClause = {
      documentId: input.documentId,
      parentId: input.parentId,
      itemNumber: input.itemNumber,
      category: input.category,
      subtitle: input.subtitle,
      content: input.content?.trim() || null,
      images: input.images || null, // ✅ 이미지 데이터 저장
      sortOrder: input.sortOrder.toString(),
      depth,
      createdById: input.createdById,
      updatedById: input.createdById,
      editReason: input.editReason,
    }

    const [result] = await db.insert(gtcClauses).values(newClause).returning()
    await revalidateGtcClausesCaches(input.documentId)

    return { data: result, error: null }
  } catch (error) {
    console.error("Error creating GTC clause:", error)
    return { data: null, error: "조항 생성 중 오류가 발생했습니다." }
  }
}

export async function createVendorGtcClause(
  input: CreateVendorGtcClauseSchema & { createdById: number }
) {
  try {
    // Calculate depth if parent is specified
    let depth = 0
    
    if (input.parentId) {
      const parent = await db.query.gtcVendorClauses.findFirst({
        where: eq(gtcVendorClauses.id, input.parentId),
      })
      if (parent) {
        depth = parent.depth + 1
      }
    }
    
    // Create the new vendor clause
    const newVendorClause = {
      vendorDocumentId: input.vendorDocumentId,
      baseClauseId: input.baseClauseId,
      parentId: input.parentId,
      
      // Modified fields
      modifiedItemNumber: input.isNumberModified ? input.modifiedItemNumber : null,
      modifiedCategory: input.isCategoryModified ? input.modifiedCategory : null,
      modifiedSubtitle: input.isSubtitleModified ? input.modifiedSubtitle : null,
      modifiedContent: input.isContentModified ? input.modifiedContent?.trim() : null,
      
      // Modification flags
      isNumberModified: input.isNumberModified,
      isCategoryModified: input.isCategoryModified,
      isSubtitleModified: input.isSubtitleModified,
      isContentModified: input.isContentModified,
      
      // Additional fields
      sortOrder: input.sortOrder.toString(),
      depth,
      reviewStatus: input.reviewStatus,
      negotiationNote: input.negotiationNote || null,
      isExcluded: input.isExcluded,
      
      // Audit fields
      createdById: input.createdById,
      updatedById: input.createdById,
      editReason: input.editReason,
      images: input.images || null,
      isActive: true,
    }
    
    const [result] = await db.insert(gtcVendorClauses).values(newVendorClause).returning()
    
    // Create negotiation history entry
    if (input.negotiationNote) {
      await db.insert(gtcNegotiationHistory).values({
        vendorClauseId: result.id,
        action: "created",
        comment: input.negotiationNote,
        actorName: input.actorName, // You'll need to pass this from the form
        actorEmail: input.actorEmail, // You'll need to pass this from the form
        previousStatus: null,
        newStatus: input.reviewStatus,
        createdById: input.createdById,
      })
    }
    
    // Revalidate cache
    // await revalidatePath(`/evcp/gtc/${input.documentId}?vendorId=${input.vendorId}`)
    
    return { data: result, error: null }
  } catch (error) {
    console.error("Error creating vendor GTC clause:", error)
    return { data: null, error: "벤더 조항 생성 중 오류가 발생했습니다." }
  }
}

/**
 * GTC 조항 수정
 */
export async function updateGtcClause(
  id: number, 
  input: UpdateGtcClauseSchema & { updatedById: number }
) {
  try {
    const updateData: Partial<GtcClause> = {
      updatedById: input.updatedById,
      updatedAt: new Date(),
      images: input.images || null, // ✅ 이미지 데이터 저장
      editReason: input.editReason,
    }

    // 선택적 업데이트 필드들
    if (input.itemNumber !== undefined) updateData.itemNumber = input.itemNumber
    if (input.category !== undefined) updateData.category = input.category
    if (input.subtitle !== undefined) updateData.subtitle = input.subtitle
    if (input.content !== undefined) {
      // 빈 문자열은 null로 저장
      updateData.content = input.content && input.content.trim() ? input.content.trim() : null
    }
    if (input.sortOrder !== undefined) updateData.sortOrder = input.sortOrder.toString()
    if (input.isActive !== undefined) updateData.isActive = input.isActive

    // fullPath 재계산 (itemNumber가 변경된 경우)
    if (input.itemNumber) {
     
          updateData.fullPath = input.itemNumber

    }

    const [result] = await db
      .update(gtcClauses)
      .set(updateData)
      .where(eq(gtcClauses.id, id))
      .returning()

    // 캐시 무효화
    const clause = await db.query.gtcClauses.findFirst({
      where: eq(gtcClauses.id, id),
    })
    if (clause) {
      await revalidateGtcClausesCaches(clause.documentId)
    }

    return { data: result, error: null }
  } catch (error) {
    console.error("Error updating GTC clause:", error)
    return { data: null, error: "조항 수정 중 오류가 발생했습니다." }
  }
}

/**
 * GTC 조항 삭제 (소프트 삭제)
 */
/**
 * GTC 조항 복수 삭제 (실제 삭제)
 */
export async function deleteGtcClauses(ids: number[], deletedById: number) {
  try {
    // 삭제 실행하고 documentId 반환받기
    const deletedClauses = await db
      .delete(gtcClauses)
      .where(inArray(gtcClauses.id, ids))
      .returning()

    if (deletedClauses.length === 0) {
      return { 
        data: null, 
        error: "삭제할 조항을 찾을 수 없습니다.",
        deletedCount: 0 
      }
    }

    // documentId는 하나일 것이므로 첫 번째 것 사용
    const documentId = deletedClauses[0].documentId
    
    // 캐시 무효화
    await revalidateGtcClausesCaches(documentId)

    return {
      data: deletedClauses,
      error: null,
      deletedCount: deletedClauses.length
    }
  } catch (error) {
    console.error("Error deleting GTC clauses:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "조항 삭제 중 오류가 발생했습니다.",
      deletedCount: 0
    }
  }
}


/**
 * GTC 조항 단일 삭제 (실제 삭제)
 */
export async function deleteGtcClause(id: number, deletedById: number) {
  const result = await deleteGtcClauses([id], deletedById)
  return {
    data: result.data?.[0] || null,
    error: result.error
  }
}

/**
 * 조항과 모든 하위 조항을 안전하게 삭제
 * (삭제 전 하위 조항 수 확인 포함)
 */
export async function deleteGtcClauseWithChildren(id: number, deletedById: number) {
  try {
    // 1. 먼저 해당 조항과 하위 조항 정보 조회
    const clause = await db.query.gtcClauses.findFirst({
      where: eq(gtcClauses.id, id),
      columns: { id: true, itemNumber: true, subtitle: true, documentId: true }
    })

    if (!clause) {
      return { data: null, error: "조항을 찾을 수 없습니다." }
    }

    // 2. 하위 조항들 조회
    const children = await db.query.gtcClauses.findMany({
      where: like(gtcClauses.fullPath, `${clause.itemNumber}.%`),
      columns: { id: true, itemNumber: true, subtitle: true }
    })

    // 3. 모든 조항 삭제 (본인 + 하위 조항들)
    const allIds = [clause.id, ...children.map(c => c.id)]
    const result = await deleteGtcClauses(allIds, deletedById)

    return {
      ...result,
      deletedClause: clause,
      deletedChildren: children,
      totalDeleted: allIds.length
    }
  } catch (error) {
    console.error("Error deleting GTC clause with children:", error)
    return { data: null, error: "조항 삭제 중 오류가 발생했습니다." }
  }
}

/**
 * GTC 조항 순서 변경
 */
export async function reorderGtcClauses(input: ReorderGtcClausesSchema & { updatedById: number }) {
  try {
    await db.transaction(async (tx) => {
      for (const clause of input.clauses) {
        await tx
          .update(gtcClauses)
          .set({
            sortOrder: clause.sortOrder.toString(),
            parentId: clause.parentId,
            depth: clause.depth,
            fullPath: clause.fullPath,
            updatedById: input.updatedById,
            updatedAt: new Date(),
            editReason: input.editReason || "조항 순서 변경",
          })
          .where(eq(gtcClauses.id, clause.id))
      }
    })

    // 캐시 무효화 (첫 번째 조항의 documentId 사용)
    if (input.clauses.length > 0) {
      const firstClause = await db.query.gtcClauses.findFirst({
        where: eq(gtcClauses.id, input.clauses[0].id),
      })
      if (firstClause) {
        await revalidateGtcClausesCaches(firstClause.documentId)
      }
    }

    return { error: null }
  } catch (error) {
    console.error("Error reordering GTC clauses:", error)
    return { error: "조항 순서 변경 중 오류가 발생했습니다." }
  }
}

/**
 * 벌크 업데이트
 */
export async function bulkUpdateGtcClauses(input: BulkUpdateGtcClausesSchema & { updatedById: number }) {
  try {
    const updateData: Partial<GtcClause> = {
      updatedById: input.updatedById,
      updatedAt: new Date(),
      editReason: input.editReason,
    }

    if (input.updates.category !== undefined) updateData.category = input.updates.category
    if (input.updates.isActive !== undefined) updateData.isActive = input.updates.isActive

    await db.transaction(async (tx) => {
      for (const clauseId of input.clauseIds) {
        await tx
          .update(gtcClauses)
          .set(updateData)
          .where(eq(gtcClauses.id, clauseId))
      }
    })

    // 캐시 무효화
    const firstClause = await db.query.gtcClauses.findFirst({
      where: eq(gtcClauses.id, input.clauseIds[0]),
    })
    if (firstClause) {
      await revalidateGtcClausesCaches(firstClause.documentId)
    }

    return { error: null }
  } catch (error) {
    console.error("Error bulk updating GTC clauses:", error)
    return { error: "조항 일괄 수정 중 오류가 발생했습니다." }
  }
}

/**
 * PDFTron 변수명 자동 생성
 */
export async function generateVariableNames(input: GenerateVariableNamesSchema & { updatedById: number }) {
  try {
    const clauses = await db
      .select()
      .from(gtcClauses)
      .where(
        and(
          eq(gtcClauses.documentId, input.documentId),
          eq(gtcClauses.isActive, true)
        )
      )

    await db.transaction(async (tx) => {
      for (const clause of clauses) {
        const basePrefix = input.includeVendorCode && input.vendorCode
          ? `${input.vendorCode}_${input.prefix}`
          : input.prefix

        const pathPrefix = clause.fullPath?.replace(/\./g, "_") || clause.itemNumber.replace(/\./g, "_")
        const varPrefix = `${basePrefix}_${pathPrefix}`

        await tx
          .update(gtcClauses)
          .set({
            numberVariableName: `${varPrefix}_NUMBER`,
            subtitleVariableName: `${varPrefix}_SUBTITLE`,
            contentVariableName: `${varPrefix}_CONTENT`,
            updatedById: input.updatedById,
            updatedAt: new Date(),
            editReason: "PDFTron 변수명 자동 생성",
          })
          .where(eq(gtcClauses.id, clause.id))
      }
    })

    await revalidateGtcClausesCaches(input.documentId)

    return { error: null }
  } catch (error) {
    console.error("Error generating variable names:", error)
    return { error: "변수명 생성 중 오류가 발생했습니다." }
  }
}

/**
 * 사용자 목록 조회 (필터용)
 */
export async function getUsersForFilter() {
  return unstable_cache(
    async () => {
      try {
        return await db
          .select({
            id: users.id,
            name: users.name,
          })
          .from(users)
          .where(eq(users.isActive, true))
          .orderBy(asc(users.name))
      } catch (err) {
        console.error("Error fetching users for filter:", err)
        return []
      }
    },
    ["users-for-filter"],
    {
      revalidate: 3600,
      tags: ["users"],
    }
  )()
}

// ===== 유틸리티 함수들 =====

async function countGtcClauses(tx: any, where: any) {
  const [{ count: total }] = await tx
    .select({ count: count() })
    .from(gtcClausesTreeView)
    .where(where)
  return total
}

async function countGtcVendorClauses(tx: any, where: any) {
  const [{ count: total }] = await tx
    .select({ count: count() })
    .from(gtcClausesWithVendorView)
    .where(where)
  return total
}


function buildClausesTree(clauses: GtcClauseTreeView[]): GtcClauseTreeView[] {
  const clauseMap = new Map<number, GtcClauseTreeView & { children: GtcClauseTreeView[] }>()
  const rootClauses: (GtcClauseTreeView & { children: GtcClauseTreeView[] })[] = []

  // 맵 생성
  clauses.forEach(clause => {
    clauseMap.set(clause.id, { ...clause, children: [] })
  })

  // 트리 구조 생성
  clauses.forEach(clause => {
    const clauseWithChildren = clauseMap.get(clause.id)!
    
    if (clause.parentId) {
      const parent = clauseMap.get(clause.parentId)
      if (parent) {
        parent.children.push(clauseWithChildren)
      }
    } else {
      rootClauses.push(clauseWithChildren)
    }
  })

  return rootClauses
}

async function revalidateGtcClausesCaches(documentId: number) {
  const { revalidateTag } = await import("next/cache")
  revalidateTag(`gtc-clauses-${documentId}`)
  revalidateTag(`gtc-clauses-tree-${documentId}`)
  revalidateTag( "basicContractView-vendor")
 
}

/**
 * 조항을 위로 이동
 */
export async function moveGtcClauseUp(clauseId: number, updatedById: number) {
  try {
    return await db.transaction(async (tx) => {
      // 현재 조항 정보 조회
      const [currentClause] = await tx
        .select()
        .from(gtcClauses)
        .where(eq(gtcClauses.id, clauseId))
        .limit(1)

      if (!currentClause) {
        return { error: "조항을 찾을 수 없습니다." }
      }

      // 같은 부모 아래에서 현재 조항보다 sortOrder가 작은 조항 중 가장 큰 것 찾기
      const whereCondition = currentClause.parentId 
        ? and(
            eq(gtcClauses.parentId, currentClause.parentId),
            sql`${gtcClauses.sortOrder} < ${currentClause.sortOrder}`,
            eq(gtcClauses.documentId, currentClause.documentId),
            eq(gtcClauses.isActive, true)
          )
        : and(
            sql`${gtcClauses.parentId} IS NULL`,
            sql`${gtcClauses.sortOrder} < ${currentClause.sortOrder}`,
            eq(gtcClauses.documentId, currentClause.documentId),
            eq(gtcClauses.isActive, true)
          )

      const [prevClause] = await tx
        .select()
        .from(gtcClauses)
        .where(whereCondition)
        .orderBy(desc(gtcClauses.sortOrder))
        .limit(1)

      if (!prevClause) {
        return { error: "이미 첫 번째 위치입니다." }
      }

      // sortOrder 교환
      const tempOrder = currentClause.sortOrder
      
      await tx
        .update(gtcClauses)
        .set({
          sortOrder: prevClause.sortOrder,
          updatedById,
          updatedAt: new Date(),
          editReason: "조항 순서 이동 (위로)"
        })
        .where(eq(gtcClauses.id, currentClause.id))

      await tx
        .update(gtcClauses)
        .set({
          sortOrder: tempOrder,
          updatedById,
          updatedAt: new Date(),
          editReason: "조항 순서 이동 (아래로)"
        })
        .where(eq(gtcClauses.id, prevClause.id))

      await revalidateGtcClausesCaches(currentClause.documentId)
      
      return { error: null }
    })
  } catch (error) {
    console.error("Error moving clause up:", error)
    return { error: "조항 이동 중 오류가 발생했습니다." }
  }
}

/**
 * 조항을 아래로 이동
 */
export async function moveGtcClauseDown(clauseId: number, updatedById: number) {
  try {
    return await db.transaction(async (tx) => {
      // 현재 조항 정보 조회
      const [currentClause] = await tx
        .select()
        .from(gtcClauses)
        .where(eq(gtcClauses.id, clauseId))
        .limit(1)

      if (!currentClause) {
        return { error: "조항을 찾을 수 없습니다." }
      }

      // 같은 부모 아래에서 현재 조항보다 sortOrder가 큰 조항 중 가장 작은 것 찾기
      const whereCondition = currentClause.parentId 
        ? and(
            eq(gtcClauses.parentId, currentClause.parentId),
            sql`${gtcClauses.sortOrder} > ${currentClause.sortOrder}`,
            eq(gtcClauses.documentId, currentClause.documentId),
            eq(gtcClauses.isActive, true)
          )
        : and(
            sql`${gtcClauses.parentId} IS NULL`,
            sql`${gtcClauses.sortOrder} > ${currentClause.sortOrder}`,
            eq(gtcClauses.documentId, currentClause.documentId),
            eq(gtcClauses.isActive, true)
          )

      const [nextClause] = await tx
        .select()
        .from(gtcClauses)
        .where(whereCondition)
        .orderBy(asc(gtcClauses.sortOrder))
        .limit(1)

      if (!nextClause) {
        return { error: "이미 마지막 위치입니다." }
      }

      // sortOrder 교환
      const tempOrder = currentClause.sortOrder
      
      await tx
        .update(gtcClauses)
        .set({
          sortOrder: nextClause.sortOrder,
          updatedById,
          updatedAt: new Date(),
          editReason: "조항 순서 이동 (아래로)"
        })
        .where(eq(gtcClauses.id, currentClause.id))

      await tx
        .update(gtcClauses)
        .set({
          sortOrder: tempOrder,
          updatedById,
          updatedAt: new Date(),
          editReason: "조항 순서 이동 (위로)"
        })
        .where(eq(gtcClauses.id, nextClause.id))

      await revalidateGtcClausesCaches(currentClause.documentId)
      
      return { error: null }
    })
  } catch (error) {
    console.error("Error moving clause down:", error)
    return { error: "조항 이동 중 오류가 발생했습니다." }
  }
}


// 벤더별 조항 정보를 조회하는 함수 추가
export async function getVendorClausesForDocument({
  documentId,
  vendorId,
}: {
  documentId: number
  vendorId?: number
}) {
  try {
    // vendorId가 없으면 빈 객체 반환
    if (!vendorId) {
      return {}
    }

    // 1. 해당 문서와 벤더에 대한 벤더 문서 찾기
    const vendorDocument = await db
      .select()
      .from(gtcVendorDocuments)
      .where(
        and(
          eq(gtcVendorDocuments.baseDocumentId, documentId),
          eq(gtcVendorDocuments.vendorId, vendorId),
          eq(gtcVendorDocuments.isActive, true)
        )
      )
      .limit(1)

    if (!vendorDocument[0]) {
      return {}
    }

    // 2. 벤더 조항들 조회 (협의 이력 포함)
    const vendorClauses = await db
      .select({
        // 벤더 조항 정보
        id: gtcVendorClauses.id,
        baseClauseId: gtcVendorClauses.baseClauseId,
        vendorDocumentId: gtcVendorClauses.vendorDocumentId,
        
        // 수정된 내용
        modifiedItemNumber: gtcVendorClauses.modifiedItemNumber,
        modifiedCategory: gtcVendorClauses.modifiedCategory,
        modifiedSubtitle: gtcVendorClauses.modifiedSubtitle,
        modifiedContent: gtcVendorClauses.modifiedContent,
        
        // 수정 플래그
        isNumberModified: gtcVendorClauses.isNumberModified,
        isCategoryModified: gtcVendorClauses.isCategoryModified,
        isSubtitleModified: gtcVendorClauses.isSubtitleModified,
        isContentModified: gtcVendorClauses.isContentModified,
        
        // 협의 상태
        reviewStatus: gtcVendorClauses.reviewStatus,
        negotiationNote: gtcVendorClauses.negotiationNote,
        isExcluded: gtcVendorClauses.isExcluded,
        
        // 날짜
        createdAt: gtcVendorClauses.createdAt,
        updatedAt: gtcVendorClauses.updatedAt,
      })
      .from(gtcVendorClauses)
      .where(
        and(
          eq(gtcVendorClauses.vendorDocumentId, vendorDocument[0].id),
          eq(gtcVendorClauses.isActive, true)
        )
      )

    // 3. 각 벤더 조항에 대한 협의 이력 조회
    const clauseIds = vendorClauses.map(c => c.id)
    const negotiationHistories = clauseIds.length > 0
      ? await db
          .select({
            vendorClauseId: gtcNegotiationHistory.vendorClauseId,
            action: gtcNegotiationHistory.action,
            previousStatus: gtcNegotiationHistory.previousStatus,
            newStatus: gtcNegotiationHistory.newStatus,
            comment: gtcNegotiationHistory.comment,
            actorName: gtcNegotiationHistory.actorName,
            actorEmail: gtcNegotiationHistory.actorEmail,
            createdAt: gtcNegotiationHistory.createdAt,
          })
          .from(gtcNegotiationHistory)
          .where(inArray(gtcNegotiationHistory.vendorClauseId, clauseIds))
          .orderBy(desc(gtcNegotiationHistory.createdAt))
      : []

    // 4. baseClauseId를 키로 하는 맵 생성
    const vendorClauseMap = new Map()
    
    vendorClauses.forEach(vc => {
      // 해당 조항의 협의 이력 필터링
      const history = negotiationHistories
        .filter(h => h.vendorClauseId === vc.id)
        .map(h => ({
          action: h.action,
          comment: h.comment,
          actorName: h.actorName,
          actorEmail: h.actorEmail,
          createdAt: h.createdAt,
          previousStatus: h.previousStatus,
          newStatus: h.newStatus,
        }))

      // 가장 최근 코멘트 찾기
      const latestComment = history.find(h => h.comment)?.comment || null
      
      vendorClauseMap.set(vc.baseClauseId, {
        ...vc,
        negotiationHistory: history,
        latestComment,
        hasModifications: 
          vc.isNumberModified || 
          vc.isCategoryModified || 
          vc.isSubtitleModified || 
          vc.isContentModified,
      })
    })

    return {
      vendorDocument: vendorDocument[0],
      vendorClauseMap,
      totalModified: vendorClauses.filter(vc => 
        vc.isNumberModified || 
        vc.isCategoryModified || 
        vc.isSubtitleModified || 
        vc.isContentModified
      ).length,
      totalExcluded: vendorClauses.filter(vc => vc.isExcluded).length,
    }
  } catch (error) {
    console.error("Failed to fetch vendor clauses:", error)
    return {}
  }
}

// service.ts에 추가
/**
 * 벤더별 GTC 조항 수정
 */
export async function updateVendorGtcClause(
  input: UpdateVendorGtcClauseSchema & { 
    baseClauseId: number
    documentId: number
    vendorId: number
    updatedById: number 
    images?: any[]
  }
) {
  try {
    // 1. 먼저 벤더 문서 찾기 또는 생성
    let vendorDocument = await db
      .select()
      .from(gtcVendorDocuments)
      .where(
        and(
          eq(gtcVendorDocuments.baseDocumentId, input.documentId),
          eq(gtcVendorDocuments.vendorId, input.vendorId),
          eq(gtcVendorDocuments.isActive, true)
        )
      )
      .limit(1)

      console.log(vendorDocument,"vendorDocument", input.vendorId, input.documentId)

    // 벤더 문서가 없으면 생성
    if (!vendorDocument[0]) {
      const vendor = await db
        .select()
        .from(vendors)
        .where(eq(vendors.id, input.vendorId))
        .limit(1)

      if (!vendor[0]) {
        return { data: null, error: "벤더 정보를 찾을 수 없습니다." }
      }

      [vendorDocument[0]] = await db
        .insert(gtcVendorDocuments)
        .values({
          baseDocumentId: input.documentId,
          vendorId: input.vendorId,
          name: `${vendor[0].vendorName} GTC Agreement`,
          description: `GTC negotiation with ${vendor[0].vendorName}`,
          version: "1.0",
          reviewStatus: "draft",
          createdById: input.updatedById,
          updatedById: input.updatedById,
        })
        .returning()
    }

    // 2. 벤더 조항 찾기 또는 생성
    let vendorClause = await db
      .select()
      .from(gtcVendorClauses)
      .where(
        and(
          eq(gtcVendorClauses.vendorDocumentId, vendorDocument[0].id),
          eq(gtcVendorClauses.baseClauseId, input.baseClauseId)
        )
      )
      .limit(1)

    const updateData: Partial<GtcVendorClause> = {
      updatedById: input.updatedById,
      updatedAt: new Date(),
      
      // 수정 플래그
      isNumberModified: input.isNumberModified,
      isCategoryModified: input.isCategoryModified,
      isSubtitleModified: input.isSubtitleModified,
      isContentModified: input.isContentModified,
      
      // 협의 정보
      reviewStatus: input.reviewStatus,
      negotiationNote: input.negotiationNote,
      isExcluded: input.isExcluded,
    }

    // 수정된 내용만 업데이트
    if (input.isNumberModified) {
      updateData.modifiedItemNumber = input.modifiedItemNumber
    } else {
      updateData.modifiedItemNumber = null
    }

    if (input.isCategoryModified) {
      updateData.modifiedCategory = input.modifiedCategory
    } else {
      updateData.modifiedCategory = null
    }

    if (input.isSubtitleModified) {
      updateData.modifiedSubtitle = input.modifiedSubtitle
    } else {
      updateData.modifiedSubtitle = null
    }

    if (input.isContentModified) {
      updateData.modifiedContent = input.modifiedContent
    } else {
      updateData.modifiedContent = null
    }

    console.log("updateData",updateData)

    let result
    if (vendorClause[0]) {
      // 업데이트
      [result] = await db
        .update(gtcVendorClauses)
        .set(updateData)
        .where(eq(gtcVendorClauses.id, vendorClause[0].id))
        .returning()
    } else {
      // 새로 생성
      const baseClause = await db
        .select()
        .from(gtcClauses)
        .where(eq(gtcClauses.id, input.baseClauseId))
        .limit(1)

      if (!baseClause[0]) {
        return { data: null, error: "기본 조항을 찾을 수 없습니다." }
      }

      [result] = await db
        .insert(gtcVendorClauses)
        .values({
          vendorDocumentId: vendorDocument[0].id,
          baseClauseId: input.baseClauseId,
          parentId: baseClause[0].parentId,
          sortOrder: baseClause[0].sortOrder,
          depth: baseClause[0].depth,
          fullPath: baseClause[0].fullPath,
          createdById: input.updatedById,
          ...updateData,
        })
        .returning()
    }

    // 3. 협의 이력 추가
    if (input.negotiationNote) {
      await db.insert(gtcNegotiationHistory).values({
        vendorClauseId: result.id,
        action: vendorClause[0] ? "modified" : "created",
        previousStatus: vendorClause[0]?.reviewStatus || null,
        newStatus: input.reviewStatus,
        comment: input.negotiationNote,
        actorType: "internal",
        actorId: input.updatedById,
        changedFields: {
          isNumberModified: input.isNumberModified,
          isCategoryModified: input.isCategoryModified,
          isSubtitleModified: input.isSubtitleModified,
          isContentModified: input.isContentModified,
        },
      })
    }

    // 캐시 무효화
    await revalidateGtcClausesCaches(input.documentId)

    return { data: result, error: null }
  } catch (error) {
    console.error("Error updating vendor GTC clause:", error)
    return { data: null, error: "벤더 조항 수정 중 오류가 발생했습니다." }
  }
}