path: root/lib/general-contracts/service.ts

'use server'

import { revalidatePath } from 'next/cache'
import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte, lt, like, sql } from 'drizzle-orm'
import db from '@/db/db'
import path from 'path'
import { promises as fs } from 'fs'
import { generalContracts, generalContractItems, generalContractAttachments } from '@/db/schema/generalContract'
import { contracts, contractItems, contractEnvelopes, contractSigners } from '@/db/schema/contract'
import { basicContract, basicContractTemplates } from '@/db/schema/basicContractDocumnet'
import { generalContractTemplates } from '@/db/schema'
import { vendors } from '@/db/schema/vendors'
import { users, roles, userRoles } from '@/db/schema/users'
import { projects } from '@/db/schema/projects'
import { items } from '@/db/schema/items'
import { filterColumns } from '@/lib/filter-columns'
import { saveDRMFile } from '@/lib/file-stroage'
import { decryptWithServerAction } from '@/components/drm/drmUtils'
import { saveBuffer } from '@/lib/file-stroage'
import { v4 as uuidv4 } from 'uuid'
import { GetGeneralContractsSchema } from './validation'
import { sendEmail } from '../mail/sendEmail'

export async function getGeneralContracts(input: GetGeneralContractsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage

    console.log(input.filters)
    console.log(input.sort)

    // ✅ 1) 고급 필터 조건
    let advancedWhere: SQL<unknown> | undefined = undefined
    if (input.filters && input.filters.length > 0) {
      advancedWhere = filterColumns({
        table: generalContracts,
        filters: input.filters as any,
        joinOperator: input.joinOperator || 'and',
      })
    }

    // ✅ 2) 기본 필터 조건들
    const basicConditions: SQL<unknown>[] = []

    if (input.contractNumber) {
      basicConditions.push(ilike(generalContracts.contractNumber, `%${input.contractNumber}%`))
    }

    if (input.name) {
      basicConditions.push(ilike(generalContracts.name, `%${input.name}%`))
    }

    if (input.status && input.status.length > 0) {
      basicConditions.push(
        or(...input.status.map(status => eq(generalContracts.status, status)))!
      )
    }

    if (input.category && input.category.length > 0) {
      basicConditions.push(
        or(...input.category.map(category => eq(generalContracts.category, category)))!
      )
    }

    if (input.type && input.type.length > 0) {
      basicConditions.push(
        or(...input.type.map(type => eq(generalContracts.type, type)))!
      )
    }

    if (input.executionMethod && input.executionMethod.length > 0) {
      basicConditions.push(
        or(...input.executionMethod.map(method => eq(generalContracts.executionMethod, method)))!
      )
    }

    if (input.contractSourceType && input.contractSourceType.length > 0) {
      basicConditions.push(
        or(...input.contractSourceType.map(method => eq(generalContracts.contractSourceType, method)))!
      )
    }

    if (input.vendorId && input.vendorId > 0) {
      basicConditions.push(eq(generalContracts.vendorId, input.vendorId))
    }

    if (input.managerName) {
      basicConditions.push(ilike(users.name, `%${input.managerName}%`))
    }

    // 날짜 필터들
    if (input.registeredAtFrom) {
      basicConditions.push(gte(generalContracts.registeredAt, new Date(input.registeredAtFrom)))
    }
    if (input.registeredAtTo) {
      basicConditions.push(lte(generalContracts.registeredAt, new Date(input.registeredAtTo)))
    }

    if (input.signedAtFrom) {
      basicConditions.push(gte(generalContracts.signedAt, new Date(input.signedAtFrom)))
    }
    if (input.signedAtTo) {
      basicConditions.push(lte(generalContracts.signedAt, new Date(input.signedAtTo)))
    }

    if (input.startDateFrom) {
      basicConditions.push(gte(generalContracts.startDate, new Date(input.startDateFrom)))
    }
    if (input.startDateTo) {
      basicConditions.push(lte(generalContracts.startDate, new Date(input.startDateTo)))
    }

    if (input.endDateFrom) {
      basicConditions.push(gte(generalContracts.endDate, new Date(input.endDateFrom)))
    }
    if (input.endDateTo) {
      basicConditions.push(lte(generalContracts.endDate, new Date(input.endDateTo)))
    }

    // 금액 필터들
    if (input.contractAmountMin) {
      basicConditions.push(gte(generalContracts.contractAmount, parseFloat(input.contractAmountMin)))
    }
    if (input.contractAmountMax) {
      basicConditions.push(lte(generalContracts.contractAmount, parseFloat(input.contractAmountMax)))
    }

    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

    // ✅ 3) 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined
    if (input.search) {
      const s = `%${input.search}%`
      const searchConditions = [
        ilike(generalContracts.contractNumber, s),
        ilike(generalContracts.name, s),
        ilike(generalContracts.notes, s),
        ilike(vendors.vendorName, s),
        ilike(users.name, s),
        ilike(generalContracts.linkedPoNumber, s),
        ilike(generalContracts.linkedRfqOrItb, s),
        ilike(generalContracts.linkedBidNumber, s),
      ]
      globalWhere = or(...searchConditions)
    }

    // ✅ 4) 최종 WHERE 조건
    const whereConditions: SQL<unknown>[] = []
    if (advancedWhere) whereConditions.push(advancedWhere)
    if (basicWhere) whereConditions.push(basicWhere)
    if (globalWhere) whereConditions.push(globalWhere)

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined

    // ✅ 5) 전체 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(generalContracts)
      .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id))
      .leftJoin(users, eq(generalContracts.registeredById, users.id))
      .where(finalWhere)

    const total = totalResult[0]?.count || 0

    if (total === 0) {
      return { data: [], pageCount: 0, total: 0 }
    }


    // ✅ 6) 정렬 및 페이징
    const orderByColumns: any[] = []
    
    for (const sort of input.sort) {
      const column = sort.id
      
      // generalContracts 테이블의 컬럼들
      if (column in generalContracts) {
        const contractColumn = generalContracts[column as keyof typeof generalContracts]
        orderByColumns.push(sort.desc ? desc(contractColumn) : asc(contractColumn))
      }
      // vendors 테이블의 컬럼들
      else if (column === 'vendorName' || column === 'vendorCode') {
        const vendorColumn = vendors[column as keyof typeof vendors]
        orderByColumns.push(sort.desc ? desc(vendorColumn) : asc(vendorColumn))
      }
      // users 테이블의 컬럼들
      else if (column === 'managerName' || column === 'lastUpdatedByName') {
        const userColumn = users.name
        orderByColumns.push(sort.desc ? desc(userColumn) : asc(userColumn))
      }
    }

    if (orderByColumns.length === 0) {
      orderByColumns.push(desc(generalContracts.registeredAt))
    }

    // ✅ 7) 메인 쿼리
    const data = await db
      .select({
        id: generalContracts.id,
        contractNumber: generalContracts.contractNumber,
        revision: generalContracts.revision,
        status: generalContracts.status,
        category: generalContracts.category,
        type: generalContracts.type,
        executionMethod: generalContracts.executionMethod,
        name: generalContracts.name,
        contractSourceType: generalContracts.contractSourceType,
        startDate: generalContracts.startDate,
        endDate: generalContracts.endDate,
        validityEndDate: generalContracts.validityEndDate,
        contractScope: generalContracts.contractScope,
        specificationType: generalContracts.specificationType,
        specificationManualText: generalContracts.specificationManualText,
        contractAmount: generalContracts.contractAmount,
        totalAmount: generalContracts.totalAmount,
        currency: generalContracts.currency,
        registeredAt: generalContracts.registeredAt,
        signedAt: generalContracts.signedAt,
        linkedRfqOrItb: generalContracts.linkedRfqOrItb,
        linkedPoNumber: generalContracts.linkedPoNumber,
        linkedBidNumber: generalContracts.linkedBidNumber,
        lastUpdatedAt: generalContracts.lastUpdatedAt,
        notes: generalContracts.notes,
        // Vendor info
        vendorId: generalContracts.vendorId,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        // User info
        managerName: users.name,
        lastUpdatedByName: users.name,
      })
      .from(generalContracts)
      .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id))
      .leftJoin(users, eq(generalContracts.registeredById, users.id))
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset)

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

    return { data, pageCount, total }

  } catch (err) {
    console.error("Error in getGeneralContracts:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

export async function getContractById(id: number) {
  try {
    // ID 유효성 검사
    if (!id || isNaN(id) || id <= 0) {
      throw new Error('Invalid contract ID')
    }

    const contract = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, id))
      .limit(1)

    if (!contract.length) {
      throw new Error('Contract not found')
    }

    // Get contract items
    const items = await db
      .select()
      .from(generalContractItems)
      .where(eq(generalContractItems.contractId, id))

    // Get contract attachments
    const attachments = await db
      .select()
      .from(generalContractAttachments)
      .where(eq(generalContractAttachments.contractId, id))

    // Get vendor info
    const vendor = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, contract[0].vendorId))
      .limit(1)
    
    // vendor의 country 정보 가져오기 (없으면 기본값 'KR')
    const vendorCountry = vendor[0]?.country || 'KR'

    // Get manager info
    const manager = await db
      .select()
      .from(users)
      .where(eq(users.id, contract[0].registeredById))
      .limit(1)

    return {
      ...contract[0],
      contractItems: items,
      attachments,
      vendor: vendor[0] || null,
      vendorCode: vendor[0]?.vendorCode || null,
      vendorName: vendor[0]?.vendorName || null,
      vendorCountry: vendorCountry,
      manager: manager[0] || null
    }
  } catch (error) {
    console.error('Error fetching contract by ID:', error)
    throw new Error('Failed to fetch contract')
  }
}

export async function getContractBasicInfo(id: number) {
  try {
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, id))
      .limit(1)

    if (!contract) {
      return null
    }

    // JSON 필드를 문자열에서 객체로 변환하여 클라이언트에서 사용하기 쉽게 만듭니다.
    // Drizzle ORM이 JSONB 타입을 처리하지만, 명확성을 위해 명시적으로 파싱하는 것이 좋습니다.
    const parsedContract = {
      ...contract,
      warrantyPeriod: contract.warrantyPeriod as any,
      paymentBeforeDelivery: contract.paymentBeforeDelivery as any,
      paymentAfterDelivery: contract.paymentAfterDelivery as any,
      contractEstablishmentConditions: contract.contractEstablishmentConditions as any,
      mandatoryDocuments: contract.mandatoryDocuments as any,
      contractTerminationConditions: contract.contractTerminationConditions as any,
    }

    // 품목정보 총합 계산 로직 (기존 코드와 동일)
    const contractItems = await db
      .select()
      .from(generalContractItems)
      .where(eq(generalContractItems.contractId, id))

    let calculatedContractAmount = null
    if (contractItems && contractItems.length > 0) {
      calculatedContractAmount = contractItems.reduce((sum, item) => {
        const amount = parseFloat(item.contractAmount || '0')
        return sum + amount
      }, 0)
    }

    return {
      ...parsedContract,
      contractAmount: calculatedContractAmount,
    }

  } catch (error) {
    console.error('Error getting contract basic info:', error)
    throw new Error('Failed to fetch contract basic info')
  }
}

export async function createContract(data: Record<string, unknown>) {
  try {
    // 계약번호 자동 생성
    // TODO: 구매 발주담당자 코드 필요 - 파라미터 추가
    const rawUserId = data.registeredById
    const userId = (rawUserId && !isNaN(Number(rawUserId))) ? String(rawUserId) : undefined
    const contractNumber = await generateContractNumber(
      userId,
      data.type as string
    )
    
    const [newContract] = await db
      .insert(generalContracts)
      .values({
        contractNumber: contractNumber,
        revision: 0,
        // contractSourceType: data.contractSourceType || 'manual',
        status: data.status || 'Draft',
        category: data.category as string,
        type: data.type as string,
        executionMethod: data.executionMethod as string,
        name: data.name as string,
        vendorId: data.vendorId as number,
        startDate: data.startDate as string,
        endDate: data.endDate as string,
        validityEndDate: data.validityEndDate as string,
        linkedRfqOrItb: data.linkedRfqOrItb as string,
        linkedPoNumber: data.linkedPoNumber as string,
        linkedBidNumber: data.linkedBidNumber as string,
        contractScope: data.contractScope as string,
        warrantyPeriod: data.warrantyPeriod || {},
        specificationType: data.specificationType as string,
        specificationManualText: data.specificationManualText as string,
        unitPriceType: data.unitPriceType as string,
        contractAmount: data.contractAmount as number,
        currency: data.currency as string,
        paymentBeforeDelivery: data.paymentBeforeDelivery || {},
        paymentDelivery: data.paymentDelivery as string,
        paymentAfterDelivery: data.paymentAfterDelivery || {},
        paymentTerm: data.paymentTerm as string,
        taxType: data.taxType as string,
        liquidatedDamages: data.liquidatedDamages as number,
        liquidatedDamagesPercent: data.liquidatedDamagesPercent as number,
        deliveryType: data.deliveryType as string,
        deliveryTerm: data.deliveryTerm as string,
        shippingLocation: data.shippingLocation as string,
        dischargeLocation: data.dischargeLocation as string,
        contractDeliveryDate: data.contractDeliveryDate as string,
        contractEstablishmentConditions: data.contractEstablishmentConditions || {},
        interlockingSystem: data.interlockingSystem as string,
        mandatoryDocuments: data.mandatoryDocuments || {},
        contractTerminationConditions: data.contractTerminationConditions || {},
        terms: data.terms || {},
        complianceChecklist: data.complianceChecklist || {},
        totalAmount: data.totalAmount as number,
        availableBudget: data.availableBudget as number,
        registeredById: data.registeredById as number,
        lastUpdatedById: data.lastUpdatedById as number,
        notes: data.notes as string,
      })
      .returning()
      console.log(newContract,"newContract")


    revalidatePath('/general-contracts')
    return newContract
  } catch (error) {
    console.error('Error creating contract:', error)
    throw new Error('Failed to create contract')
  }
}

export async function updateContractBasicInfo(id: number, data: Record<string, unknown>, userId: number) {
  try {
    // 업데이트할 데이터 정리
    // 클라이언트에서 전송된 formData를 그대로 사용합니다.
    const {
      contractScope,
      specificationType,
      specificationManualText,
      unitPriceType,
      warrantyPeriod,
      currency,
      linkedPoNumber,
      linkedBidNumber,
      notes,
      paymentBeforeDelivery,
      paymentDelivery,
      paymentAfterDelivery,
      paymentTerm,
      taxType,
      liquidatedDamages,
      liquidatedDamagesPercent,
      deliveryType,
      deliveryTerm,
      shippingLocation,
      dischargeLocation,
      contractDeliveryDate,
      contractEstablishmentConditions,
      interlockingSystem,
      mandatoryDocuments,
      contractTerminationConditions,
      externalYardEntry,
      contractAmountReason,
    } = data

    // 계약금액 자동 집계 로직
    const contractItems = await db
      .select()
      .from(generalContractItems)
      .where(eq(generalContractItems.contractId, id))

    let calculatedContractAmount: number | null = null
    if (contractItems && contractItems.length > 0) {
      calculatedContractAmount = contractItems.reduce((sum, item) => {
        const amount = parseFloat(item.contractAmount || '0')
        return sum + amount
      }, 0)
    }

    // 데이터 타입 변환 및 검증
    const convertToNumberOrNull = (value: unknown): number | null => {
      if (value === null || value === undefined || value === '' || value === 'false') {
        return null
      }
      const num = typeof value === 'string' ? parseFloat(value) : Number(value)
      return isNaN(num) ? null : num
    }

    // 날짜 필드에서 빈 문자열을 null로 변환
    const convertEmptyStringToNull = (value: unknown): string | null => {
      return (value === '' || value === undefined) ? null : value as string
    }

    // 업데이트할 데이터 객체 생성
    const updateData: Record<string, unknown> = {
      contractScope,
      specificationType,
      specificationManualText,
      unitPriceType,
      warrantyPeriod, // JSON 필드
      currency,
      linkedPoNumber,
      linkedBidNumber,
      notes,
      paymentBeforeDelivery, // JSON 필드
      paymentDelivery,
      paymentAfterDelivery, // JSON 필드
      paymentTerm,
      taxType,
      liquidatedDamages: convertToNumberOrNull(liquidatedDamages),
      liquidatedDamagesPercent: convertToNumberOrNull(liquidatedDamagesPercent),
      deliveryType,
      deliveryTerm,
      shippingLocation,
      dischargeLocation,
      contractDeliveryDate: convertEmptyStringToNull(contractDeliveryDate),
      contractEstablishmentConditions, // JSON 필드
      interlockingSystem,
      mandatoryDocuments, // JSON 필드
      contractTerminationConditions, // JSON 필드
      externalYardEntry,
      contractAmountReason: convertEmptyStringToNull(contractAmountReason),
      contractAmount: calculatedContractAmount || 0,
      lastUpdatedAt: new Date(),
      lastUpdatedById: userId,
    }
    console.log(updateData.paymentDelivery,"updateData.paymentDelivery")
    // DB에 업데이트 실행
    const [updatedContract] = await db
      .update(generalContracts)
      .set(updateData)
      .where(eq(generalContracts.id, id))
      .returning()


    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/${id}`)
    return updatedContract
  } catch (error) {
    console.error('Error updating contract basic info:', error)
    throw new Error('Failed to update contract basic info')
  }
}

// 품목정보 조회
export async function getContractItems(contractId: number) {
  try {
    const items = await db
      .select({
        id: generalContractItems.id,
        contractId: generalContractItems.contractId,
        projectId: generalContractItems.projectId,
        itemCode: generalContractItems.itemCode,
        itemInfo: generalContractItems.itemInfo,
        specification: generalContractItems.specification,
        quantity: generalContractItems.quantity,
        quantityUnit: generalContractItems.quantityUnit,
        totalWeight: generalContractItems.totalWeight,
        weightUnit: generalContractItems.weightUnit,
        contractDeliveryDate: generalContractItems.contractDeliveryDate,
        contractUnitPrice: generalContractItems.contractUnitPrice,
        contractAmount: generalContractItems.contractAmount,
        contractCurrency: generalContractItems.contractCurrency,
        createdAt: generalContractItems.createdAt,
        updatedAt: generalContractItems.updatedAt,
        projectName: projects.name,
        projectCode: projects.code,
      })
      .from(generalContractItems)
      .leftJoin(projects, eq(generalContractItems.projectId, projects.id))
      .where(eq(generalContractItems.contractId, contractId))
      .orderBy(asc(generalContractItems.id))

    return items
  } catch (error) {
    console.error('Error getting contract items:', error)
    throw new Error('Failed to get contract items')
  }
}

// 품목정보 생성
export async function createContractItem(contractId: number, itemData: Record<string, unknown>) {
  try {
    const [newItem] = await db
      .insert(generalContractItems)
      .values({
        contractId,
        projectId: itemData.projectId ? (itemData.projectId as number) : null,
        itemCode: itemData.itemCode as string,
        itemInfo: itemData.itemInfo as string,
        specification: itemData.specification as string,
        quantity: itemData.quantity as number,
        quantityUnit: itemData.quantityUnit as string,
        contractDeliveryDate: itemData.contractDeliveryDate as string,
        contractUnitPrice: itemData.contractUnitPrice as number,
        contractAmount: itemData.contractAmount as number,
        contractCurrency: itemData.contractCurrency as string,
      })
      .returning()

    // 계약금액 자동 업데이트
    await updateContractAmount(contractId)

    revalidatePath('/general-contracts')
    return newItem
  } catch (error) {
    console.error('Error creating contract item:', error)
    throw new Error('Failed to create contract item')
  }
}

// 품목정보 업데이트
export async function updateContractItem(itemId: number, itemData: Record<string, unknown>) {
  try {
    const [updatedItem] = await db
      .update(generalContractItems)
      .set({
        projectId: itemData.projectId ? (itemData.projectId as number) : null,
        itemCode: itemData.itemCode as string,
        itemInfo: itemData.itemInfo as string,
        specification: itemData.specification as string,
        quantity: itemData.quantity as number,
        quantityUnit: itemData.quantityUnit as string,
        contractDeliveryDate: itemData.contractDeliveryDate as string,
        contractUnitPrice: itemData.contractUnitPrice as number,
        contractAmount: itemData.contractAmount as number,
        contractCurrency: itemData.contractCurrency as string,
        updatedAt: new Date()
      })
      .where(eq(generalContractItems.id, itemId))
      .returning()

    // 계약금액 자동 업데이트
    await updateContractAmount(updatedItem.contractId)

    revalidatePath('/general-contracts')
    return updatedItem
  } catch (error) {
    console.error('Error updating contract item:', error)
    throw new Error('Failed to update contract item')
  }
}

// 품목정보 삭제
export async function deleteContractItem(itemId: number) {
  try {
    // 삭제 전 계약 ID 조회
    const [item] = await db
      .select({ contractId: generalContractItems.contractId })
      .from(generalContractItems)
      .where(eq(generalContractItems.id, itemId))
      .limit(1)

    if (!item) {
      throw new Error('Contract item not found')
    }

    await db
      .delete(generalContractItems)
      .where(eq(generalContractItems.id, itemId))

    // 계약금액 자동 업데이트
    await updateContractAmount(item.contractId)

    revalidatePath('/general-contracts')
    return { success: true }
  } catch (error) {
    console.error('Error deleting contract item:', error)
    throw new Error('Failed to delete contract item')
  }
}

// 품목정보 일괄 업데이트 (기존 함수 개선)
export async function updateContractItems(contractId: number, items: Record<string, unknown>[]) {
  try {
    // 기존 품목 삭제
    await db
      .delete(generalContractItems)
      .where(eq(generalContractItems.contractId, contractId))

    // 새 품목 추가
    if (items && items.length > 0) {
      await db
        .insert(generalContractItems)
        .values(
          items.map((item: Record<string, unknown>) => ({
            contractId,
            projectId: item.projectId ? (item.projectId as number) : null,
            itemCode: item.itemCode as string,
            itemInfo: item.itemInfo as string,
            specification: item.specification as string,
            quantity: item.quantity as number,
            quantityUnit: item.quantityUnit as string,
            totalWeight: item.totalWeight as number,
            weightUnit: item.weightUnit as string,
            contractDeliveryDate: item.contractDeliveryDate as string,
            contractUnitPrice: item.contractUnitPrice as number,
            contractAmount: item.contractAmount as number,
            contractCurrency: item.contractCurrency as string,
          }))
        )
    }

    // 계약금액 자동 업데이트
    await updateContractAmount(contractId)

    revalidatePath('/general-contracts')
    return { success: true }
  } catch (error) {
    console.error('Error updating contract items:', error)
    throw new Error('Failed to update contract items')
  }
}

// 계약금액 자동 업데이트 헬퍼 함수
async function updateContractAmount(contractId: number) {
  try {
    const items = await db
      .select({ contractAmount: generalContractItems.contractAmount })
      .from(generalContractItems)
      .where(eq(generalContractItems.contractId, contractId))

    let calculatedContractAmount: number | null = null
    if (items && items.length > 0) {
      calculatedContractAmount = items.reduce((sum, item) => {
        const amount = parseFloat(String(item.contractAmount || '0'))
        return sum + amount
      }, 0)
    }

    // 계약 테이블의 contractAmount 업데이트
    await db
      .update(generalContracts)
      .set({
        contractAmount: calculatedContractAmount || 0,
        lastUpdatedAt: new Date()
      })
      .where(eq(generalContracts.id, contractId))
  } catch (error) {
    console.error('Error updating contract amount:', error)
    throw new Error('Failed to update contract amount')
  }
}

export async function updateSubcontractChecklist(contractId: number, checklistData: Record<string, unknown>) {
  try {
    await db
      .update(generalContracts)
      .set({
        complianceChecklist: checklistData,
        lastUpdatedAt: new Date()
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath('/general-contracts')
    return { success: true }
  } catch (error) {
    console.error('Error updating subcontract checklist:', error)
    throw new Error('Failed to update subcontract checklist')
  }
}

export async function getSubcontractChecklist(contractId: number) {
  try {
    const result = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (result.length === 0) {
      return { success: false, error: '계약을 찾을 수 없습니다.' }
    }

    const contract = result[0]
    const checklistData = contract.complianceChecklist as any

    return {
      success: true,
      enabled: !!checklistData,
      data: checklistData || {}
    }
  } catch (error) {
    console.error('Error getting subcontract checklist:', error)
    return { success: false, error: '하도급 체크리스트 조회에 실패했습니다.' }
  }
}

export async function getBasicInfo(contractId: number) {
  try {
    const result = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (result.length === 0) {
      return { success: false, error: '계약을 찾을 수 없습니다.' }
    }

    const contract = result[0]
    return {
      success: true,
      enabled: true, // basic-info는 항상 활성화
      data: {
        // 기본 정보
        contractNumber: contract.contractNumber,
        contractName: contract.name,
        vendorId: contract.vendorId,
        vendorName: contract.vendorName,
        projectName: contract.projectName,
        contractType: contract.type,
        contractStatus: contract.status,
        startDate: contract.startDate,
        endDate: contract.endDate,
        contractAmount: contract.contractAmount,
        currency: contract.currency,
        description: contract.description,
        specificationType: contract.specificationType,
        specificationManualText: contract.specificationManualText,
        unitPriceType: contract.unitPriceType,
        warrantyPeriod: contract.warrantyPeriod,
        linkedPoNumber: contract.linkedPoNumber,
        linkedBidNumber: contract.linkedBidNumber,
        notes: contract.notes,
        
        // 지급/인도 조건
        paymentBeforeDelivery: contract.paymentBeforeDelivery,
        paymentDelivery: contract.paymentDelivery,
        paymentAfterDelivery: contract.paymentAfterDelivery,
        paymentTerm: contract.paymentTerm,
        taxType: contract.taxType,
        liquidatedDamages: contract.liquidatedDamages,
        liquidatedDamagesPercent: contract.liquidatedDamagesPercent,
        deliveryType: contract.deliveryType,
        deliveryTerm: contract.deliveryTerm,
        shippingLocation: contract.shippingLocation,
        dischargeLocation: contract.dischargeLocation,
        contractDeliveryDate: contract.contractDeliveryDate,
        
        // 추가 조건
        contractEstablishmentConditions: contract.contractEstablishmentConditions,
        interlockingSystem: contract.interlockingSystem,
        mandatoryDocuments: contract.mandatoryDocuments,
        contractTerminationConditions: contract.contractTerminationConditions,
        externalYardEntry: contract.externalYardEntry || 'N'
      }
    }
  } catch (error) {
    console.error('Error getting basic info:', error)
    return { success: false, error: '기본 정보 조회에 실패했습니다.' }
  }
}

export async function updateContract(id: number, data: Record<string, unknown>) {
  try {
    // 숫자 필드에서 빈 문자열을 null로 변환
    const cleanedData = { ...data }
    const numericFields = [
      'vendorId',
      'projectId',
      'warrantyPeriodValue',
      'warrantyPeriodMax',
      'contractAmount',
      'totalAmount',
      'availableBudget',
      'liquidatedDamages',
      'liquidatedDamagesPercent',
      'lastUpdatedById'
    ]
    
    // 모든 필드에서 빈 문자열, undefined, 빈 객체 등을 정리
    Object.keys(cleanedData).forEach(key => {
      const value = cleanedData[key]
      
      // 빈 문자열을 null로 변환
      if (value === '') {
        cleanedData[key] = null
      }
      
      // 빈 객체를 null로 변환
      if (value && typeof value === 'object' && Object.keys(value).length === 0) {
        cleanedData[key] = null
      }
    })
    
    // 숫자 필드들 추가 정리 (vendorId는 NOT NULL이므로 null로 설정하지 않음)
    numericFields.forEach(field => {
      if (field === 'vendorId') {
        // vendorId는 필수 필드이므로 null로 설정하지 않음
        if (cleanedData[field] === '' || cleanedData[field] === undefined || cleanedData[field] === 0) {
          // 유효하지 않은 값이면 에러 발생
          throw new Error('Vendor ID is required and cannot be null')
        }
      } else {
        // 다른 숫자 필드들은 빈 값이면 null로 설정
        if (cleanedData[field] === '' || cleanedData[field] === undefined || cleanedData[field] === 0) {
          cleanedData[field] = null
        }
      }
    })

    const [updatedContract] = await db
      .update(generalContracts)
      .set({
        ...cleanedData,
        lastUpdatedAt: new Date(),
        revision: (cleanedData.revision as number) ? (cleanedData.revision as number) + 1 : 0,
      })
      .where(eq(generalContracts.id, id))
      .returning()

    // Update contract items if provided
    if (data.contractItems && Array.isArray(data.contractItems)) {
      // Delete existing items
      await db
        .delete(generalContractItems)
        .where(eq(generalContractItems.contractId, id))

      // Insert new items
      if (data.contractItems.length > 0) {
        await db
          .insert(generalContractItems)
          .values(
            data.contractItems.map((item: any) => ({
              projectId: item.projectId ? (item.projectId as number) : null,
              itemCode: item.itemCode,
              itemInfo: item.itemInfo,
              specification: item.specification,
              quantity: item.quantity,
              quantityUnit: item.quantityUnit,
              contractDeliveryDate: item.contractDeliveryDate,
              contractUnitPrice: item.contractUnitPrice,
              contractAmount: item.contractAmount,
              contractCurrency: item.contractCurrency,
              contractId: id,
            }))
          )
      }
    }

    // Update attachments if provided
    if (data.attachments && Array.isArray(data.attachments)) {
      // Delete existing attachments
      await db
        .delete(generalContractAttachments)
        .where(eq(generalContractAttachments.contractId, id))

      // Insert new attachments
      if (data.attachments.length > 0) {
        await db
          .insert(generalContractAttachments)
          .values(
            data.attachments.map((attachment: any) => ({
              ...attachment,
              contractId: id,
            }))
          )
      }
    }

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${id}`)
    return updatedContract
  } catch (error) {
    console.error('Error updating contract:', error)
    throw new Error('Failed to update contract')
  }
}

export async function deleteContract(id: number) {
  try {
    // 현재 계약 정보 조회
    await db
      .select({ revision: generalContracts.revision })
      .from(generalContracts)
      .where(eq(generalContracts.id, id))
      .limit(1)

    // 계약폐기: status를 'Contract Delete'로 변경
    const [updatedContract] = await db
      .update(generalContracts)
      .set({
        status: 'Contract Delete',
        lastUpdatedAt: new Date(),
        // revision: (currentContract[0]?.revision || 0) + 1 // 계약 파기 시 리비전 증가? 확인 필요
      })
      .where(eq(generalContracts.id, id))
      .returning()

    revalidatePath('/general-contracts')
    return { success: true, contract: updatedContract }
  } catch (error) {
    console.error('Error deleting contract:', error)
    throw new Error('Failed to delete contract')
  }
}

// 상태별 개수 집계
export async function getGeneralContractStatusCounts() {
  try {
    const counts = await db
      .select({
        status: generalContracts.status,
        count: count(),
      })
      .from(generalContracts)
      .groupBy(generalContracts.status)

    return counts.reduce((acc, { status, count }) => {
      acc[status] = count
      return acc
    }, {} as Record<string, number>)
  } catch (error) {
    console.error('Failed to get contract status counts:', error)
    return {}
  }
}

// 계약구분별 개수 집계
export async function getGeneralContractCategoryCounts() {
  try {
    const counts = await db
      .select({
        category: generalContracts.category,
        count: count(),
      })
      .from(generalContracts)
      .groupBy(generalContracts.category)

    return counts.reduce((acc, { category, count }) => {
      acc[category] = count
      return acc
    }, {} as Record<string, number>)
  } catch (error) {
    console.error('Failed to get contract category counts:', error)
    return {}
  }
}

export async function getVendors() {
  try {
    const vendorList = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
      })
      .from(vendors)
      .orderBy(asc(vendors.vendorName))

    return vendorList
  } catch (error) {
    console.error('Error fetching vendors:', error)
    throw new Error('Failed to fetch vendors')
  }
}

// 첨부파일 업로드
export async function uploadContractAttachment(contractId: number, file: File, userId: string, documentName: string = '사양 및 공급범위') {
  try {
    // userId를 숫자로 변환
    const userIdNumber = parseInt(userId)
    if (isNaN(userIdNumber)) {
      throw new Error('Invalid user ID')
    }

    const saveResult = await saveDRMFile(
      file,
      decryptWithServerAction,
      `general-contracts/${contractId}/attachments`,
      userId,
    )

    if (saveResult.success && saveResult.publicPath) {
      // generalContractAttachments 테이블에 저장
      const [attachment] = await db.insert(generalContractAttachments).values({
        contractId,
        documentName,
        fileName: saveResult.fileName || file.name,
        filePath: saveResult.publicPath,
        uploadedById: userIdNumber,
        uploadedAt: new Date(),
      }).returning()

      return {
        success: true,
        message: '파일이 성공적으로 업로드되었습니다.',
        attachment
      }
    } else {
      return {
        success: false,
        error: saveResult.error || '파일 저장에 실패했습니다.'
      }
    }
  } catch (error) {
    console.error('Failed to upload contract attachment:', error)
    return {
      success: false,
      error: '파일 업로드에 실패했습니다.'
    }
  }
}

// 첨부파일 목록 조회
export async function getContractAttachments(contractId: number) {
  try {
    const attachments = await db
      .select()
      .from(generalContractAttachments)
      .where(eq(generalContractAttachments.contractId, contractId))
      .orderBy(desc(generalContractAttachments.uploadedAt))

    return attachments
  } catch (error) {
    console.error('Failed to get contract attachments:', error)
    return []
  }
}

// 첨부파일 다운로드
export async function getContractAttachmentForDownload(attachmentId: number, contractId: number) {
  try {
    const attachments = await db
      .select()
      .from(generalContractAttachments)
      .where(and(
        eq(generalContractAttachments.id, attachmentId),
        eq(generalContractAttachments.contractId, contractId)
      ))
      .limit(1)

    if (attachments.length === 0) {
      return {
        success: false,
        error: '첨부파일을 찾을 수 없습니다.'
      }
    }

    return {
      success: true,
      attachment: attachments[0]
    }
  } catch (error) {
    console.error('Failed to get contract attachment for download:', error)
    return {
      success: false,
      error: '첨부파일 다운로드 준비에 실패했습니다.'
    }
  }
}

// 첨부파일 삭제
export async function deleteContractAttachment(attachmentId: number, contractId: number) {
  try {
    const attachments = await db
      .select()
      .from(generalContractAttachments)
      .where(and(
        eq(generalContractAttachments.id, attachmentId),
        eq(generalContractAttachments.contractId, contractId)
      ))
      .limit(1)

    if (attachments.length === 0) {
      return {
        success: false,
        error: '첨부파일을 찾을 수 없습니다.'
      }
    }

    // 데이터베이스에서 삭제
    await db
      .delete(generalContractAttachments)
      .where(eq(generalContractAttachments.id, attachmentId))

    return {
      success: true,
      message: '첨부파일이 삭제되었습니다.'
    }
  } catch (error) {
    console.error('Failed to delete contract attachment:', error)
    return {
      success: false,
      error: '첨부파일 삭제에 실패했습니다.'
    }
  }
}

// 계약승인요청용 파일 업로드 (DRM 사용)
export async function uploadContractApprovalFile(contractId: number, file: File, userId: string) {
  try {
    const userIdNumber = parseInt(userId)
    if (isNaN(userIdNumber)) {
      throw new Error('Invalid user ID')
    }

    const saveResult = await saveDRMFile(
      file,
      decryptWithServerAction,
      `general-contracts/${contractId}/approval-documents`,
      userId,
    )

    if (saveResult.success && saveResult.publicPath) {
      return {
        success: true,
        message: '파일이 성공적으로 업로드되었습니다.',
        filePath: saveResult.publicPath,
        fileName: saveResult.fileName || file.name
      }
    } else {
      return {
        success: false,
        error: saveResult.error || '파일 저장에 실패했습니다.'
      }
    }
  } catch (error) {
    console.error('Failed to upload contract approval file:', error)
    return {
      success: false,
      error: '파일 업로드에 실패했습니다.'
    }
  }
}



// 계약승인요청 전송
export async function sendContractApprovalRequest(
  contractSummary: any,
  pdfBuffer: Uint8Array,
  documentType: string,
  userId: string,
  generatedBasicContracts?: Array<{ key: string; buffer: number[]; fileName: string }>
) {
  try {
    // contracts 테이블에 새 계약 생성 (generalContracts에서 contracts로 복사)
    const contractData = await mapContractSummaryToDb(contractSummary)
    
    const [newContract] = await db.insert(contracts).values({
      ...contractData,
      contractNo: contractData.contractNo || `GC-${Date.now()}`, // contractNumber 대신 contractNo 사용
    }).returning()
    
    const contractId = newContract.id

  //   const items: {
  //     id: number;
  //     createdAt: Date;
  //     updatedAt: Date;
  //     contractId: number;
  //     itemCode: string | null;
  //     quantity: string | null;
  //     contractAmount: string | null;
  //     contractCurrency: string | null;
  //     contractDeliveryDate: string | null;
  //     specification: string | null;
  //     itemInfo: string | null;
  //     quantityUnit: string | null;
  //     totalWeight: string | null;
  //     weightUnit: string | null;
  //     contractUnitPrice: string | null;
  // }[]

    // contractItems 테이블에 품목 정보 저장 (general-contract-items가 있을 때만)
    if (contractSummary.items && contractSummary.items.length > 0) {
      const projectNo = contractSummary.basicInfo?.projectCode || contractSummary.basicInfo?.projectId?.toString() || 'NULL'

      for (const item of contractSummary.items) {
        let itemId: number

        // 1. items 테이블에서 itemCode로 기존 아이템 검색
        if (item.itemCode) {
          // const existingItem = await db
          //   .select({ id: items.id })
          //   .from(items)
          //   .where(and(
          //     eq(items.itemCode, item.itemCode),
          //     eq(items.ProjectNo, projectNo)
          //   ))
          //   .limit(1)
          const existingItem = await db
            .select({ id: items.id })
            .from(items)
            .where(
              eq(items.itemCode, item.itemCode)
            )
            .limit(1)

          if (existingItem.length > 0) {
            // 기존 아이템이 있으면 해당 ID 사용
            itemId = existingItem[0].id
          } else {
            // 기존 아이템이 없으면 새로 생성
            const newItem = await db.insert(items).values({
              ProjectNo: projectNo,
              itemCode: item.itemCode,
              itemName: item.itemInfo || item.description || item.itemCode,
              packageCode: item.itemCode,
              description: item.specification || item.description || '',
              unitOfMeasure: item.quantityUnit || 'EA',
              createdAt: new Date(),
              updatedAt: new Date(),
            }).returning({ id: items.id })

            itemId = newItem[0].id
          }
        

        // 2. contractItems에 저장
        await db.insert(contractItems).values({
          contractId,
          itemId: itemId,
          description: item.itemInfo || item.description || '',
          quantity: Math.floor(Number(item.quantity) || 1), // 정수로 변환
          unitPrice: item.contractUnitPrice || item.unitPrice || 0,
          taxRate: item.taxRate || 0,
          taxAmount: item.taxAmount || 0,
          totalLineAmount: item.contractAmount || item.totalLineAmount || 0,
          remark: item.remark || '',
        })
      }else{
        //아이템코드가 없으니 pass
        continue
      }
      }
    }

    // PDF 버퍼를 saveBuffer 함수로 저장
    const fileId = uuidv4()
    const fileName = `${fileId}.pdf`
    
    // PDF 버퍼를 Buffer로 변환
    let bufferData: Buffer
    if (Buffer.isBuffer(pdfBuffer)) {
      bufferData = pdfBuffer
    } else if (pdfBuffer instanceof ArrayBuffer) {
      bufferData = Buffer.from(pdfBuffer)
    } else if (pdfBuffer instanceof Uint8Array) {
      bufferData = Buffer.from(pdfBuffer)
    } else {
      bufferData = Buffer.from(pdfBuffer as any)
    }

    // saveBuffer 함수를 사용해서 파일 저장
    const saveResult = await saveBuffer({
      buffer: bufferData,
      fileName: fileName,
      directory: "generalContracts",
      originalName: `contract_${contractId}_${documentType}_${fileId}.pdf`,
      userId: userId
    })
    
    if (!saveResult.success) {
      throw new Error(saveResult.error || 'PDF 파일 저장에 실패했습니다.')
    }
    
    const finalFileName = saveResult.fileName || fileName
    const finalFilePath = saveResult.publicPath 
      ? saveResult.publicPath.replace('/api/files/', '') 
      : `/generalContracts/${fileName}`

    // contractEnvelopes 테이블에 서명할 PDF 파일 정보 저장
    const [newEnvelope] = await db.insert(contractEnvelopes).values({
      contractId: contractId,
      envelopeId: `envelope_${contractId}_${Date.now()}`,
      documentId: `document_${contractId}_${Date.now()}`,
      envelopeStatus: 'PENDING',
      fileName: finalFileName,
      filePath: finalFilePath,
    }).returning()

    // contractSigners 테이블에 벤더 서명자 정보 저장
    const vendorEmail = contractSummary.basicInfo?.vendorEmail || 'vendor@example.com'
    const vendorName = contractSummary.basicInfo?.vendorName || '벤더'
    
    await db.insert(contractSigners).values({
      envelopeId: newEnvelope.id,
      signerType: 'VENDOR',
      signerEmail: vendorEmail,
      signerName: vendorName,
      signerPosition: '대표자',
      signerStatus: 'PENDING',
    })

    // 사외업체 야드투입이 'Y'인 경우 안전담당자 자동 지정
    if (contractSummary.basicInfo?.externalYardEntry === 'Y') {
      try {
        // 안전담당자 역할을 가진 사용자 조회 (역할명에 '안전' 또는 'safety' 포함)
        const safetyManagers = await db
          .select({
            id: users.id,
            name: users.name,
            email: users.email,
          })
          .from(users)
          .innerJoin(userRoles, eq(users.id, userRoles.userId))
          .innerJoin(roles, eq(userRoles.roleId, roles.id))
          .where(
            and(
              or(
                like(roles.name, '%안전%'),
                like(roles.name, '%safety%'),
                like(roles.name, '%Safety%')
              ),
              eq(users.isActive, true)
            )
          )
          .limit(1)

        // 첫 번째 안전담당자를 자동 추가
        if (safetyManagers.length > 0) {
          const safetyManager = safetyManagers[0]
          await db.insert(contractSigners).values({
            envelopeId: newEnvelope.id,
            signerType: 'SAFETY_MANAGER',
            signerEmail: safetyManager.email || '',
            signerName: safetyManager.name || '안전담당자',
            signerPosition: '안전담당자',
            signerStatus: 'PENDING',
          })
        }
      } catch (error) {
        console.error('Error adding safety manager:', error)
        // 안전담당자 추가 실패해도 계약 승인 요청은 계속 진행
      }
    }

    // generalContractAttachments에 contractId 업데이트 (일반계약의 첨부파일들을 PO 계약과 연결)
    const generalContractId = contractSummary.basicInfo?.id || contractSummary.id
    if (generalContractId) {
      await db.update(generalContractAttachments)
        .set({ poContractId: contractId })
        .where(eq(generalContractAttachments.contractId, generalContractId))
    }

    // 기본계약 처리 (클라이언트에서 생성된 PDF 사용 또는 자동 생성)
    await processGeneratedBasicContracts(contractSummary, contractId, userId, generatedBasicContracts)

    try {
    sendEmail({
      to: contractSummary.basicInfo.vendorEmail,
      subject: `계약승인요청`,
      template: "contract-approval-request",
      context: {
        contractId: contractId,
        loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/po`,
        language: "ko",
      },
    })
    // 계약 상태 업데이트
    await db.update(generalContracts)
    .set({
      status: 'Contract Accept Request',
      lastUpdatedAt: new Date()
    })
    .where(eq(generalContracts.id, generalContractId))

  } catch (error) {
    console.error('계약승인요청 전송 오류:', error)

  }


  revalidatePath('/evcp/general-contracts')
  revalidatePath('/evcp/general-contracts/detail')
  revalidatePath('/evcp/general-contracts/detail/contract-approval-request-dialog')

  return {
    success: true,
    message: '계약승인요청이 성공적으로 전송되었습니다.',
    pdfPath: saveResult.publicPath
  }

  } catch (error: any) {
    console.error('계약승인요청 전송 오류:', error)

    // 중복 계약 번호 오류 처리
    if (error.message && error.message.includes('duplicate key value violates unique constraint')) {
      return {
        success: false,
        error: '이미 존재하는 계약번호입니다. 다른 계약번호를 사용해주세요.'
      }
    }

    // 다른 데이터베이스 오류 처리
    if (error.code === '23505') { // PostgreSQL unique constraint violation
      return {
        success: false,
        error: '중복된 데이터가 존재합니다. 입력값을 확인해주세요.'
      }
    }

    return {
      success: false,
      error: `계약승인요청 전송 중 오류가 발생했습니다: ${error.message}`
    }
  }
}

// 클라이언트에서 생성된 기본계약 처리 (RFQ-Last 방식)
async function processGeneratedBasicContracts(
  contractSummary: any,
  contractId: number,
  userId: string,
  generatedBasicContracts: Array<{ key: string; buffer: number[]; fileName: string }>
): Promise<void> {
  try {
    const userIdNumber = parseInt(userId)
    if (isNaN(userIdNumber)) {
      throw new Error('Invalid user ID')
    }

    console.log(`${generatedBasicContracts.length}개의 클라이언트 생성 기본계약을 처리합니다.`)

    // 기본계약 디렉토리 생성 (RFQ-Last 방식)
    const nasPath = process.env.NAS_PATH || "/evcp_nas"
    const isProduction = process.env.NODE_ENV === "production"
    const baseDir = isProduction ? nasPath : path.join(process.cwd(), "public")
    const contractsDir = path.join(baseDir, "basicContracts")
    await fs.mkdir(contractsDir, { recursive: true })

    for (const contractData of generatedBasicContracts) {
      try {
        console.log(contractSummary.basicInfo?.vendorId || 'unknown', contractData.buffer.length)

        // PDF 버퍼를 Buffer로 변환 및 파일 저장
        const pdfBuffer = Buffer.from(contractData.buffer)
        const fileName = contractData.fileName
        const filePath = path.join(contractsDir, fileName)

        await fs.writeFile(filePath, pdfBuffer)

        // key에서 템플릿 정보 추출 (vendorId_type_templateName 형식)
        const keyParts = contractData.key.split('_')
        const vendorId = parseInt(keyParts[0])
        const contractType = keyParts[1]
        const templateName = keyParts.slice(2).join('_')

        // 템플릿 조회
        const template = await getTemplateByName(templateName)

        console.log("템플릿", templateName, template)

        if (template) {
          // 웹 접근 경로 설정 (RFQ-Last 방식)
          let filePublicPath: string
          if (isProduction) {
            filePublicPath = `/api/files/basicContracts/${fileName}`
          } else {
            filePublicPath = `/basicContracts/${fileName}`
          }

          // basicContract 테이블에 저장
          const deadline = new Date()
          deadline.setDate(deadline.getDate() + 10) // 10일 후 마감

          await db.insert(basicContract).values({
            templateId: template.id,
            vendorId: vendorId,
            requestedBy: userIdNumber,
            generalContractId: contractSummary.basicInfo?.id || contractSummary.id,
            fileName: fileName,
            filePath: filePublicPath,
            deadline: deadline.toISOString().split('T')[0], // YYYY-MM-DD 형식으로
            status: 'PENDING'
          })

          console.log(`클라이언트 생성 기본계약 저장 완료:${contractData.fileName}`)
        } else {
          console.error(`템플릿을 찾을 수 없음: ${templateName}`)
        }

      } catch (error) {
        console.error(`기본계약 처리 실패 (${contractData.fileName}):`, error)
        // 개별 계약서 처리 실패는 전체 프로세스를 중단하지 않음
      }
    }

  } catch (error) {
    console.error('클라이언트 생성 기본계약 처리 중 오류:', error)
    // 기본계약 생성 실패는 계약 승인 요청 전체를 실패시키지 않음
  }
}

// 템플릿명으로 템플릿 조회 (RFQ-Last 방식)
async function getTemplateByName(templateName: string) {
  const [template] = await db
    .select()
    .from(basicContractTemplates)
    .where(
      and(
        ilike(basicContractTemplates.templateName, `%${templateName}%`),
        eq(basicContractTemplates.status, "ACTIVE")
      )
    )
    .limit(1)

  return template
}

async function mapContractSummaryToDb(contractSummary: any) {
  const basicInfo = contractSummary.basicInfo || {}
  
  // 계약번호 생성
  const contractNumber = await generateContractNumber(
    basicInfo.userId,
    basicInfo.contractType || basicInfo.type || 'UP'
  )
  
  return {
    // 기본 정보
    projectId: basicInfo.projectId || null, // 기본값 설정
    vendorId: basicInfo.vendorId,
    contractNo: contractNumber,
    contractName: basicInfo.contractName || '계약승인요청',
    status: 'PENDING_APPROVAL',
    
    // 계약 기간
    startDate: basicInfo.startDate || new Date().toISOString().split('T')[0],
    endDate: basicInfo.endDate || new Date().toISOString().split('T')[0],
    
    // 지급/인도 조건
    paymentTerms: basicInfo.paymentTerm || '',
    deliveryTerms: basicInfo.deliveryTerm || '',
    deliveryDate: basicInfo.contractDeliveryDate || basicInfo.deliveryDate || new Date().toISOString().split('T')[0],
    shippmentPlace: basicInfo.shippingLocation || basicInfo.shippmentPlace || '',
    deliveryLocation: basicInfo.dischargeLocation || basicInfo.deliveryLocation || '',
    
    // 금액 정보
    budgetAmount: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0),
    budgetCurrency: basicInfo.currency || basicInfo.contractCurrency || 'USD',
    totalAmountKrw: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0),
    currency: basicInfo.currency || basicInfo.contractCurrency || 'USD',
    totalAmount: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0),
    
    // // SAP ECC 관련 필드들
    // poVersion: basicInfo.revision || 1,
    // purchaseDocType: basicInfo.type || 'UP',
    // purchaseOrg: basicInfo.purchaseOrg || '',
    // purchaseGroup: basicInfo.purchaseGroup || '',
    // exchangeRate: Number(basicInfo.exchangeRate || 1),
    
    // // 계약/보증 관련
    // contractGuaranteeCode: basicInfo.contractGuaranteeCode || '',
    // defectGuaranteeCode: basicInfo.defectGuaranteeCode || '',
    // guaranteePeriodCode: basicInfo.guaranteePeriodCode || '',
    // advancePaymentYn: basicInfo.advancePaymentYn || 'N',
    
    // // 전자계약/승인 관련
    // electronicContractYn: basicInfo.electronicContractYn || 'Y',
    // electronicApprovalDate: basicInfo.electronicApprovalDate || null,
    // electronicApprovalTime: basicInfo.electronicApprovalTime || '',
    // ownerApprovalYn: basicInfo.ownerApprovalYn || 'N',
    
    // // 기타
    // plannedInOutFlag: basicInfo.plannedInOutFlag || 'I',
    // settlementStandard: basicInfo.settlementStandard || 'A',
    // weightSettlementFlag: basicInfo.weightSettlementFlag || 'N',
    
    // 연동제 관련
    priceIndexYn: basicInfo.priceIndexYn || 'N',
    writtenContractNo: basicInfo.contractNumber || '',
    contractVersion: basicInfo.revision || 1,
    
    // // 부분 납품/결제
    // partialShippingAllowed: basicInfo.partialShippingAllowed || false,
    // partialPaymentAllowed: basicInfo.partialPaymentAllowed || false,
    
    // 메모
    remarks: basicInfo.notes || basicInfo.remarks || '',
    
    // 버전 관리
    version: basicInfo.revision || 1,
    
    // 타임스탬프 (contracts 테이블 스키마에 맞게)
    createdAt: new Date(),
    updatedAt: new Date()
  }
}


// 계약번호 생성 함수
// 임치계약 정보 조회
export async function getStorageInfo(contractId: number) {
  try {
    const contract = await db
      .select({ terms: generalContracts.terms })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract.length || !contract[0].terms) {
      return []
    }

    const terms = contract[0].terms as any
    return terms.storageInfo || []
  } catch (error) {
    console.error('Error getting storage info:', error)
    throw new Error('Failed to get storage info')
  }
}

// 임치계약 정보 저장
export async function saveStorageInfo(contractId: number, items: Array<{ poNumber: string; hullNumber: string; remainingAmount: number }>, userId: number) {
  try {
    const contract = await db
      .select({ terms: generalContracts.terms })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract.length) {
      throw new Error('Contract not found')
    }

    const currentTerms = (contract[0].terms || {}) as any
    const updatedTerms = {
      ...currentTerms,
      storageInfo: items
    }

    await db
      .update(generalContracts)
      .set({
        terms: updatedTerms,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId,
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath(`/general-contracts/detail/${contractId}`)
  } catch (error) {
    console.error('Error saving storage info:', error)
    throw new Error('Failed to save storage info')
  }
}

// 야드투입 정보 조회
export async function getYardEntryInfo(contractId: number) {
  try {
    const contract = await db
      .select({ terms: generalContracts.terms })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract.length || !contract[0].terms) {
      return null
    }

    const terms = contract[0].terms as any
    return terms.yardEntryInfo || null
  } catch (error) {
    console.error('Error getting yard entry info:', error)
    throw new Error('Failed to get yard entry info')
  }
}

// 야드투입 정보 저장
export async function saveYardEntryInfo(contractId: number, data: { projectId: number | null; projectCode: string; projectName: string; managerName: string; managerDepartment: string; rehandlingContractor: string }, userId: number) {
  try {
    const contract = await db
      .select({ terms: generalContracts.terms })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract.length) {
      throw new Error('Contract not found')
    }

    const currentTerms = (contract[0].terms || {}) as any
    const updatedTerms = {
      ...currentTerms,
      yardEntryInfo: data
    }

    await db
      .update(generalContracts)
      .set({
        terms: updatedTerms,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId,
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath(`/general-contracts/detail/${contractId}`)
  } catch (error) {
    console.error('Error saving yard entry info:', error)
    throw new Error('Failed to save yard entry info')
  }
}

// 계약 문서 댓글 저장
export async function saveContractAttachmentComment(attachmentId: number, contractId: number, commentType: 'shi' | 'vendor', comment: string, userId: number) {
  try {
    const updateData: Record<string, unknown> = {}
    if (commentType === 'shi') {
      updateData.shiComment = comment
    } else {
      updateData.vendorComment = comment
    }

    await db
      .update(generalContractAttachments)
      .set(updateData)
      .where(eq(generalContractAttachments.id, attachmentId))

    revalidatePath(`/general-contracts/detail/${contractId}`)
  } catch (error) {
    console.error('Error saving attachment comment:', error)
    throw new Error('Failed to save attachment comment')
  }
}

export async function generateContractNumber(
  userId?: string,
  contractType: string
): Promise<string> {
  try {
    // 계약종류 매핑 (2자리) - GENERAL_CONTRACT_TYPES 상수 사용
    const contractTypeMap: Record<string, string> = {
      'UP': 'UP',   // 자재단가계약
      'LE': 'LE',   // 임대차계약
      'IL': 'IL',   // 개별운송계약
      'AL': 'AL',   // 연간운송계약
      'OS': 'OS',   // 외주용역계약
      'OW': 'OW',   // 도급계약
      'LO': 'LO',   // LOI (의향서)
      'FA': 'FA',   // FA (Frame Agreement)
      'SC': 'SC',   // 납품합의계약 (Supply Contract)
      'OF': 'OF',   // 클레임상계계약 (Offset Agreement)
      'AW': 'AW',   // 사전작업합의 (Advanced Work)
      'AD': 'AD',   // 사전납품합의 (Advanced Delivery)
      'SG': 'SG',   // 임치(물품보관)계약
      'SR': 'SR',   // 폐기물매각계약 (Scrap)
      'SP': 'SP'    // S-PEpC
    }
    
    const typeCode = contractTypeMap[contractType] || 'XX' // 기본값
    // user 테이블의 user.userCode가 있으면 발주담당자 코드로 사용
    // userId가 주어졌을 때 user.userCode를 조회, 없으면 '000' 사용
    let purchaseManagerCode = '000';
    if (userId) {
      const user = await db
        .select({ userCode: users.userCode })
        .from(users)
        .where(eq(users.id, parseInt(userId || '0')))
        .limit(1);
      if (user[0]?.userCode && user[0].userCode.length >= 3) {
        purchaseManagerCode = user[0].userCode.substring(0, 3).toUpperCase();
      }
    }
    let managerCode: string
    if (purchaseManagerCode && purchaseManagerCode.length >= 3) {
      // 발주담당자 코드가 있으면 3자리 사용
      managerCode = purchaseManagerCode.substring(0, 3).toUpperCase()
    } else {
      // 발주담당자 코드가 없으면 일련번호로 대체 (001부터 시작)
      const currentYear = new Date().getFullYear()
      const prefix = `C${typeCode}${currentYear.toString().slice(-2)}`
      
      // 해당 패턴으로 시작하는 계약번호 중 가장 큰 일련번호 찾기
      const existingContracts = await db
        .select({ contractNumber: generalContracts.contractNumber })
        .from(generalContracts)
        .where(like(generalContracts.contractNumber, `${prefix}%`))
        .orderBy(desc(generalContracts.contractNumber))
        .limit(1)
      
      let sequenceNumber = 1
      if (existingContracts.length > 0) {
        const lastContractNumber = existingContracts[0].contractNumber
        const lastSequenceStr = lastContractNumber.slice(-3)

        // contractNumber에서 숫자만 추출하여 sequence 찾기
        const numericParts = lastContractNumber.match(/\d+/g)
        if (numericParts && numericParts.length > 0) {
          // 마지막 숫자 부분을 시퀀스로 사용 (일반적으로 마지막 3자리)
          const potentialSequence = numericParts[numericParts.length - 1]
          const lastSequence = parseInt(potentialSequence)

          if (!isNaN(lastSequence)) {
            sequenceNumber = lastSequence + 1
          }
        }
        // 숫자를 찾지 못했거나 파싱 실패 시 sequenceNumber = 1 유지
      }
      
      // 일련번호를 3자리로 포맷팅
      managerCode = sequenceNumber.toString().padStart(3, '0')
    }
    
    // 일련번호 생성 (3자리)
    const currentYear = new Date().getFullYear()
    const prefix = `C${managerCode}${typeCode}${currentYear.toString().slice(-2)}`
    
    // 해당 패턴으로 시작하는 계약번호 중 가장 큰 일련번호 찾기
    const existingContracts = await db
      .select({ contractNumber: generalContracts.contractNumber })
      .from(generalContracts)
      .where(like(generalContracts.contractNumber, `${prefix}%`))
      .orderBy(desc(generalContracts.contractNumber))
      .limit(1)
    
    let sequenceNumber = 1
    if (existingContracts.length > 0) {
      const lastContractNumber = existingContracts[0].contractNumber

      // contractNumber에서 숫자만 추출하여 sequence 찾기
      const numericParts = lastContractNumber.match(/\d+/g)
      if (numericParts && numericParts.length > 0) {
        // 마지막 숫자 부분을 시퀀스로 사용
        const potentialSequence = numericParts[numericParts.length - 1]
        const lastSequence = parseInt(potentialSequence)

        if (!isNaN(lastSequence)) {
          sequenceNumber = lastSequence + 1
        }
      }
      // 숫자를 찾지 못했거나 파싱 실패 시 sequenceNumber = 1 유지
    }
    
    // 최종 계약번호 생성: C + 발주담당자코드(3자리) + 계약종류(2자리) + 연도(2자리) + 일련번호(3자리)
    const finalSequence = sequenceNumber.toString().padStart(3, '0')
    const contractNumber = `C${managerCode}${typeCode}${currentYear.toString().slice(-2)}${finalSequence}`
    
    return contractNumber

  } catch (error) {
    console.error('계약번호 생성 오류:', error)
    throw new Error('계약번호 생성에 실패했습니다.')
  }
}

// 프로젝트 목록 조회 (코드와 이름만 반환)
export async function getProjects() {
  try {
    const projectList = await db
      .select({
        id: projects.id,
        code: projects.code,
        name: projects.name,
      })
      .from(projects)
      .orderBy(asc(projects.name))

    return projectList
  } catch (error) {
    console.error('프로젝트 목록 조회 오류:', error)
    throw new Error('프로젝트 목록을 불러오는데 실패했습니다')
  }
}

// ═══════════════════════════════════════════════════════════════
// 협력업체 전용 조건검토 조회 함수
// ═══════════════════════════════════════════════════════════════

// 협력업체 전용 조건검토 계약 조회
export async function getVendorContractReviews(
  vendorId: number,
  page: number = 1,
  perPage: number = 10,
  search?: string
) {
  try {
    const offset = (page - 1) * perPage

    // 조건검토 관련 상태들
    const reviewStatuses = ['Request to Review', 'Vendor Replied Review', 'SHI Confirmed Review']

    // 기본 조건: vendorId와 status 필터
    const conditions: SQL<unknown>[] = [
      eq(generalContracts.vendorId, vendorId),
      or(...reviewStatuses.map(status => eq(generalContracts.status, status)))!
    ]

    // 검색 조건 추가
    if (search) {
      const searchPattern = `%${search}%`
      conditions.push(
        or(
          ilike(generalContracts.contractNumber, searchPattern),
          ilike(generalContracts.name, searchPattern),
          ilike(generalContracts.notes, searchPattern)
        )!
      )
    }

    const whereCondition = and(...conditions)

    // 전체 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(generalContracts)
      .where(whereCondition)

    const total = totalResult[0]?.count || 0

    if (total === 0) {
      return { data: [], pageCount: 0, total: 0 }
    }

    // 데이터 조회
    const data = await db
      .select({
        id: generalContracts.id,
        contractNumber: generalContracts.contractNumber,
        revision: generalContracts.revision,
        status: generalContracts.status,
        category: generalContracts.category,
        type: generalContracts.type,
        executionMethod: generalContracts.executionMethod,
        name: generalContracts.name,
        contractSourceType: generalContracts.contractSourceType,
        startDate: generalContracts.startDate,
        endDate: generalContracts.endDate,
        validityEndDate: generalContracts.validityEndDate,
        contractScope: generalContracts.contractScope,
        specificationType: generalContracts.specificationType,
        specificationManualText: generalContracts.specificationManualText,
        contractAmount: generalContracts.contractAmount,
        totalAmount: generalContracts.totalAmount,
        currency: generalContracts.currency,
        registeredAt: generalContracts.registeredAt,
        signedAt: generalContracts.signedAt,
        linkedRfqOrItb: generalContracts.linkedRfqOrItb,
        linkedPoNumber: generalContracts.linkedPoNumber,
        linkedBidNumber: generalContracts.linkedBidNumber,
        lastUpdatedAt: generalContracts.lastUpdatedAt,
        notes: generalContracts.notes,
        vendorId: generalContracts.vendorId,
        registeredById: generalContracts.registeredById,
        lastUpdatedById: generalContracts.lastUpdatedById,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        managerName: users.name,
      })
      .from(generalContracts)
      .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id))
      .leftJoin(users, eq(generalContracts.registeredById, users.id))
      .where(whereCondition)
      .orderBy(desc(generalContracts.registeredAt))
      .limit(perPage)
      .offset(offset)

    const pageCount = Math.ceil(total / perPage)

    // 날짜 변환 헬퍼 함수
    const formatDate = (date: unknown): string => {
      if (!date) return ''
      if (date instanceof Date) {
        return date.toISOString()
      }
      if (typeof date === 'string') {
        return date
      }
      return String(date)
    }

    return {
      data: data.map((row) => ({
        id: row.id,
        contractNumber: row.contractNumber || '',
        revision: row.revision || 0,
        status: row.status || '',
        category: row.category || '',
        type: row.type || '',
        executionMethod: row.executionMethod || '',
        name: row.name || '',
        contractSourceType: row.contractSourceType || '',
        startDate: formatDate(row.startDate),
        endDate: formatDate(row.endDate),
        validityEndDate: formatDate(row.validityEndDate),
        contractScope: row.contractScope || '',
        specificationType: row.specificationType || '',
        specificationManualText: row.specificationManualText || '',
        contractAmount: row.contractAmount ? row.contractAmount.toString() : '',
        totalAmount: row.totalAmount ? row.totalAmount.toString() : '',
        currency: row.currency || '',
        registeredAt: formatDate(row.registeredAt),
        signedAt: formatDate(row.signedAt),
        linkedRfqOrItb: row.linkedRfqOrItb || '',
        linkedPoNumber: row.linkedPoNumber || '',
        linkedBidNumber: row.linkedBidNumber || '',
        lastUpdatedAt: formatDate(row.lastUpdatedAt),
        notes: row.notes || '',
        vendorId: row.vendorId || 0,
        registeredById: row.registeredById || 0,
        lastUpdatedById: row.lastUpdatedById || 0,
        vendorName: row.vendorName || '',
        vendorCode: row.vendorCode || '',
        managerName: row.managerName || '',
      })),
      pageCount,
      total,
    }
    console.log(data, "data")
  } catch (error) {
    console.error('Error fetching vendor contract reviews:', error)
    return { data: [], pageCount: 0, total: 0 }
  }
}

// ═══════════════════════════════════════════════════════════════
// 조건검토 의견 관련 함수들
// ═══════════════════════════════════════════════════════════════

// 협력업체 조건검토 의견 저장
export async function saveVendorComment(
  contractId: number,
  vendorComment: string,
  vendorId: number
) {
  try {
    // 계약 정보 조회 및 권한 확인
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    if (contract.vendorId !== vendorId) {
      throw new Error('이 계약에 대한 접근 권한이 없습니다.')
    }

    // generalContracts 테이블에 vendorComment 저장
    await db
      .update(generalContracts)
      .set({
        vendorComment: vendorComment,
        lastUpdatedAt: new Date(),
        lastUpdatedById: contract.lastUpdatedById, // 기존 수정자 유지
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath(`/partners/general-contract-review/${contractId}`)
    revalidatePath(`/general-contracts/detail/${contractId}`)

    return { success: true, message: '협력업체 의견이 저장되었습니다.' }
  } catch (error) {
    console.error('협력업체 의견 저장 오류:', error)
    throw error
  }
}

// 협력업체 조건검토 의견 조회
export async function getVendorComment(contractId: number, vendorId?: number) {
  try {
    const conditions = [eq(generalContracts.id, contractId)]
    
    if (vendorId) {
      conditions.push(eq(generalContracts.vendorId, vendorId))
    }

    const [contract] = await db
      .select({
        vendorComment: generalContracts.vendorComment,
      })
      .from(generalContracts)
      .where(and(...conditions))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    return {
      success: true,
      vendorComment: contract.vendorComment || '',
    }
  } catch (error) {
    console.error('협력업체 의견 조회 오류:', error)
    throw error
  }
}

// 당사 조건검토 의견 저장
export async function saveShiComment(
  contractId: number,
  shiComment: string,
  userId: number
) {
  try {
    // 계약 정보 조회
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    // generalContracts 테이블에 shiComment 저장
    await db
      .update(generalContracts)
      .set({
        shiComment: shiComment,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId,
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath(`/general-contracts/detail/${contractId}`)
    revalidatePath(`/partners/general-contract-review/${contractId}`)

    return { success: true, message: '당사 의견이 저장되었습니다.' }
  } catch (error) {
    console.error('당사 의견 저장 오류:', error)
    throw error
  }
}

// 당사 조건검토 의견 조회
export async function getShiComment(contractId: number) {
  try {
    const [contract] = await db
      .select({
        shiComment: generalContracts.shiComment,
      })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    return {
      success: true,
      shiComment: contract.shiComment || '',
    }
  } catch (error) {
    console.error('당사 의견 조회 오류:', error)
    throw error
  }
}

// 조건검토 의견 모두 조회 (vendorComment + shiComment)
export async function getContractReviewComments(contractId: number, vendorId?: number) {
  try {
    const conditions = [eq(generalContracts.id, contractId)]
    
    if (vendorId) {
      conditions.push(eq(generalContracts.vendorId, vendorId))
    }

    const [contract] = await db
      .select({
        vendorComment: generalContracts.vendorComment,
        shiComment: generalContracts.shiComment,
      })
      .from(generalContracts)
      .where(and(...conditions))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    return {
      success: true,
      vendorComment: contract.vendorComment || '',
      shiComment: contract.shiComment || '',
    }
  } catch (error) {
    console.error('조건검토 의견 조회 오류:', error)
    throw error
  }
}

// ═══════════════════════════════════════════════════════════════
// 조건검토요청 관련 함수들
// ═══════════════════════════════════════════════════════════════

// 조건검토용 파일 업로드
export async function uploadContractReviewFile(contractId: number, file: File, userId: string) {
  try {
    const userIdNumber = parseInt(userId)
    if (isNaN(userIdNumber)) {
      throw new Error('Invalid user ID')
    }

    const saveResult = await saveDRMFile(
      file,
      decryptWithServerAction,
      `general-contracts/${contractId}/review-documents`,
      userId,
    )

    if (saveResult.success && saveResult.publicPath) {
      return {
        success: true,
        message: '파일이 성공적으로 업로드되었습니다.',
        filePath: saveResult.publicPath,
        fileName: saveResult.fileName || file.name
      }
    } else {
      return {
        success: false,
        error: saveResult.error || '파일 저장에 실패했습니다.'
      }
    }
  } catch (error) {
    console.error('Failed to upload contract review file:', error)
    return {
      success: false,
      error: '파일 업로드에 실패했습니다.'
    }
  }
}

// 조건검토요청 전송 (PDF 포함)
export async function sendContractReviewRequest(
  contractSummary: any,
  pdfBuffer: Uint8Array,
  contractId: number,
  userId: string
) {
  try {
    const userIdNumber = parseInt(userId)
    if (isNaN(userIdNumber)) {
      throw new Error('Invalid user ID')
    }

    // 계약 정보 조회
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    // PDF 버퍼를 saveBuffer 함수로 저장
    const fileId = uuidv4()
    const fileName = `contract_review_${fileId}.pdf`
    
    // PDF 버퍼를 Buffer로 변환
    let bufferData: Buffer
    if (Buffer.isBuffer(pdfBuffer)) {
      bufferData = pdfBuffer
    } else if (pdfBuffer instanceof ArrayBuffer) {
      bufferData = Buffer.from(pdfBuffer)
    } else if (pdfBuffer instanceof Uint8Array) {
      bufferData = Buffer.from(pdfBuffer)
    } else {
      bufferData = Buffer.from(pdfBuffer as any)
    }

    // saveBuffer 함수를 사용해서 파일 저장
    const saveResult = await saveBuffer({
      buffer: bufferData,
      fileName: fileName,
      directory: "generalContracts",
      originalName: `contract_review_${contractId}_${fileId}.pdf`,
      userId: userId
    })
    
    if (!saveResult.success) {
      throw new Error(saveResult.error || 'PDF 파일 저장에 실패했습니다.')
    }
    
    const finalFileName = saveResult.fileName || fileName
    const finalFilePath = saveResult.publicPath 
      ? saveResult.publicPath.replace('/api/files/', '') 
      : `/generalContracts/${fileName}`

    // generalContractAttachments 테이블에 계약서 초안 PDF 저장
    await db.insert(generalContractAttachments).values({
      contractId: contractId,
      documentName: '계약서 초안',
      fileName: finalFileName,
      filePath: finalFilePath,
      uploadedById: userIdNumber,
      uploadedAt: new Date(),
    })

    // 계약 상태를 'Request to Review'로 변경
    await db
      .update(generalContracts)
      .set({
        status: 'Request to Review',
        lastUpdatedAt: new Date(),
        lastUpdatedById: userIdNumber,
      })
      .where(eq(generalContracts.id, contractId))

    // 협력업체 정보 조회
    const [vendor] = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, contract.vendorId))
      .limit(1)

    // 협력업체 담당자에게 검토 요청 이메일 발송
    if (vendor?.vendorEmail) {
      try {
        await sendEmail({
          to: vendor.vendorEmail,
          subject: `[SHI] 일반계약 조건검토 요청 - ${contract.contractNumber}`,
          template: 'contract-review-request',
          context: {
            contractId: contractId,
            contractNumber: contract.contractNumber,
            contractName: contract.name,
            loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/general-contract-review/${contractId}`,
            language: 'ko',
          },
        })
      } catch (emailError) {
        console.error('이메일 발송 실패:', emailError)
        // 이메일 발송 실패해도 계약 상태 변경은 유지
      }
    }

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${contractId}`)

    return { success: true, message: '조건검토요청이 성공적으로 전송되었습니다.' }
  } catch (error: any) {
    console.error('조건검토요청 전송 오류:', error)
    return {
      success: false,
      error: error.message || '조건검토요청 전송에 실패했습니다.'
    }
  }
}

// 조건검토요청 전송 (기존 함수 - 하위 호환성 유지)
export async function requestContractReview(contractId: number, userId: number) {
  try {
    // 계약 정보 조회
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    // 계약 상태를 'Request to Review'로 변경
    await db
      .update(generalContracts)
      .set({
        status: 'Request to Review',
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId,
      })
      .where(eq(generalContracts.id, contractId))

    // 협력업체 정보 조회
    const [vendor] = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, contract.vendorId))
      .limit(1)

    // 협력업체 담당자에게 검토 요청 이메일 발송
    if (vendor?.vendorEmail) {
      try {
        await sendEmail({
          to: vendor.vendorEmail,
          subject: `[SHI] 일반계약 조건검토 요청 - ${contract.contractNumber}`,
          template: 'contract-review-request',
          context: {
            contractId: contractId,
            contractNumber: contract.contractNumber,
            contractName: contract.name,
            loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/general-contract-review/${contractId}`,
            language: 'ko',
          },
        })
      } catch (emailError) {
        console.error('이메일 발송 실패:', emailError)
        // 이메일 발송 실패해도 계약 상태 변경은 유지
      }
    }

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${contractId}`)

    return { success: true, message: '조건검토요청이 성공적으로 전송되었습니다.' }
  } catch (error) {
    console.error('조건검토요청 전송 오류:', error)
    throw new Error('조건검토요청 전송에 실패했습니다.')
  }
}

// 협력업체용 계약 정보 조회 (검토용 최소 정보)
export async function getContractForVendorReview(contractId: number, vendorId?: number) {
  try {
    const contract = await db
      .select({
        id: generalContracts.id,
        contractNumber: generalContracts.contractNumber,
        revision: generalContracts.revision,
        name: generalContracts.name,
        status: generalContracts.status,
        type: generalContracts.type,
        category: generalContracts.category,
        vendorId: generalContracts.vendorId,
        contractAmount: generalContracts.contractAmount,
        currency: generalContracts.currency,
        startDate: generalContracts.startDate,
        endDate: generalContracts.endDate,
        specificationType: generalContracts.specificationType,
        specificationManualText: generalContracts.specificationManualText,
        contractScope: generalContracts.contractScope,
        notes: generalContracts.notes,
        vendorComment: generalContracts.vendorComment,
        shiComment: generalContracts.shiComment,
      })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract.length) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    // 권한 확인: vendorId가 제공된 경우 해당 협력업체의 계약인지 확인
    if (vendorId && contract[0].vendorId !== vendorId) {
      throw new Error('이 계약에 대한 접근 권한이 없습니다.')
    }

    // 품목 정보 조회
    const contractItems = await db
      .select()
      .from(generalContractItems)
      .where(eq(generalContractItems.contractId, contractId))

    // 첨부파일 조회
    const attachments = await db
      .select()
      .from(generalContractAttachments)
      .where(eq(generalContractAttachments.contractId, contractId))

    // 협력업체 정보 조회
    const [vendor] = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, contract[0].vendorId))
      .limit(1)

    return {
      ...contract[0],
      contractItems,
      attachments,
      vendor: vendor || null,
    }
  } catch (error) {
    console.error('협력업체용 계약 정보 조회 오류:', error)
    throw error
  }
}

// 협력업체 의견 회신
export async function vendorReplyToContractReview(
  contractId: number,
  vendorComment: string,
  vendorId: number
) {
  try {
    // 계약 정보 조회 및 권한 확인
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    if (contract.vendorId !== vendorId) {
      throw new Error('이 계약에 대한 접근 권한이 없습니다.')
    }

    // 계약 상태 확인
    if (contract.status !== 'Request to Review') {
      throw new Error('조건검토요청 상태가 아닙니다.')
    }

    // 계약 상태를 'Vendor Replied Review'로 변경하고 vendorComment 저장
    await db
      .update(generalContracts)
      .set({
        status: 'Vendor Replied Review',
        vendorComment: vendorComment,
        lastUpdatedAt: new Date(),
      })
      .where(eq(generalContracts.id, contractId))

    // 당사 구매 담당자에게 회신 알림 이메일 발송
    const [manager] = await db
      .select()
      .from(users)
      .where(eq(users.id, contract.registeredById))
      .limit(1)

    if (manager?.email) {
      try {
        await sendEmail({
          to: manager.email,
          subject: `[SHI] 협력업체 조건검토 회신 - ${contract.contractNumber}`,
          template: 'vendor-review-reply',
          context: {
            contractId: contractId,
            contractNumber: contract.contractNumber,
            contractName: contract.name,
            vendorName: contract.vendorName || '협력업체',
            loginUrl: `${process.env.NEXT_PUBLIC_URL}/evcp/general-contracts/detail/${contractId}`,
            language: 'ko',
          },
        })
      } catch (emailError) {
        console.error('이메일 발송 실패:', emailError)
      }
    }

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${contractId}`)

    return { success: true, message: '의견이 성공적으로 회신되었습니다.' }
  } catch (error) {
    console.error('협력업체 의견 회신 오류:', error)
    throw error
  }
}

// 협력업체 의견 임시 저장
export async function saveVendorCommentDraft(
  contractId: number,
  vendorComment: string,
  vendorId: number
) {
  try {
    // 계약 정보 조회 및 권한 확인
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    if (contract.vendorId !== vendorId) {
      throw new Error('이 계약에 대한 접근 권한이 없습니다.')
    }

    // 협력업체 의견을 임시 저장 (generalContracts 테이블의 vendorComment에 저장, 상태는 변경하지 않음)
    await db
      .update(generalContracts)
      .set({
        vendorComment: vendorComment,
        lastUpdatedAt: new Date(),
      })
      .where(eq(generalContracts.id, contractId))

    return { success: true, message: '의견이 임시 저장되었습니다.' }
  } catch (error) {
    console.error('협력업체 의견 임시 저장 오류:', error)
    throw error
  }
}

// 당사 검토 확정
export async function confirmContractReview(
  contractId: number,
  shiComment: string,
  userId: number
) {
  try {
    // 계약 정보 조회
    const [contract] = await db
      .select()
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      throw new Error('계약을 찾을 수 없습니다.')
    }

    // 계약 상태 확인
    if (contract.status !== 'Vendor Replied Review') {
      throw new Error('협력업체 회신 상태가 아닙니다.')
    }

    // 계약 상태를 'SHI Confirmed Review'로 변경하고 shiComment 저장
    await db
      .update(generalContracts)
      .set({
        status: 'SHI Confirmed Review',
        shiComment: shiComment,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId,
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${contractId}`)

    return { success: true, message: '검토가 확정되었습니다.' }
  } catch (error) {
    console.error('당사 검토 확정 오류:', error)
    throw error
  }
}

// 계약 유형에 맞는 최신 템플릿 조회
export async function getContractTemplateByContractType(contractType: string) {
  try {
    // 1. 정확한 타입 매칭 시도
    const templates = await db
      .select()
      .from(generalContractTemplates)
      .where(
        and(
          eq(generalContractTemplates.contractTemplateType, contractType),
          eq(generalContractTemplates.status, 'ACTIVE')
        )
      )
      .orderBy(desc(generalContractTemplates.revision)) // 최신 리비전 우선
      .limit(1)

    if (templates.length > 0) {
      return { success: true, template: templates[0] }
    }

    // 2. 매칭되는 템플릿이 없을 경우 (필요 시 로직 추가)
    return { success: false, error: '해당 계약 유형에 맞는 템플릿을 찾을 수 없습니다.' }
  } catch (error) {
    console.error('템플릿 조회 오류:', error)
    return { success: false, error: '템플릿 조회 중 오류가 발생했습니다.' }
  }
}