'use server'

import { revalidatePath } from 'next/cache'
import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte, lt } from 'drizzle-orm'
import db from '@/db/db'
import { generalContracts, generalContractItems, generalContractAttachments } from '@/db/schema/generalContract'
import { vendors } from '@/db/schema/vendors'
import { users } from '@/db/schema/users'
import { filterColumns } from '@/lib/filter-columns'
import { saveDRMFile } from '@/lib/file-stroage'
import { decryptWithServerAction } from '@/components/drm/drmUtils'
import { GetGeneralContractsSchema } from './validation'

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.selectionMethod && input.selectionMethod.length > 0) {
      basicConditions.push(
        or(...input.selectionMethod.map(method => eq(generalContracts.selectionMethod, 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 }
    }

    console.log("Total contracts:", total)

    // ✅ 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,
        selectionMethod: generalContracts.selectionMethod,
        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
        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)

    // 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,
      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')
  }
}

// 계약번호 생성 함수
async function generateContractNumber(registeredById: number, contractType: string): Promise<string> {
  // 발주담당자코드 3자리 (사용자 ID를 3자리로 패딩)
  const managerCode = String(registeredById).padStart(3, '0')
  
  // 계약종류 2자리 (영문)
  const typeCode = contractType.substring(0, 2).toUpperCase()
  
  // 일련번호 3자리 (현재 날짜 기준으로 생성)
  const today = new Date()
  
  // 같은 날짜의 계약 개수 조회
  const startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate())
  const endOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1)
  
  const countResult = await db
    .select({ count: count() })
    .from(generalContracts)
    .where(
      and(
        gte(generalContracts.registeredAt, startOfDay),
        lt(generalContracts.registeredAt, endOfDay)
      )
    )
  
  const sequenceNumber = String((countResult[0]?.count || 0) + 1).padStart(3, '0')
  
  return `C${managerCode}${typeCode}${sequenceNumber}`
}

export async function createContract(data: Record<string, unknown>) {
  try {
    // 계약번호 자동 생성
    const contractNumber = data.contractNumber || await generateContractNumber(
      data.registeredById as number, 
      data.type as string
    )
    
    const [newContract] = await db
      .insert(generalContracts)
      .values({
        contractNumber: contractNumber,
        revision: 0,
        status: data.status || 'Draft',
        category: data.category as string,
        type: data.type as string,
        executionMethod: data.executionMethod as string,
        name: data.name as string,
        selectionMethod: data.selectionMethod 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 || {},
        communicationChannels: data.communicationChannels || {},
        locations: data.locations || {},
        fieldServiceRates: data.fieldServiceRates || {},
        offsetDetails: data.offsetDetails || {},
        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()


    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 {
      specificationType,
      specificationManualText,
      unitPriceType,
      warrantyPeriod,
      currency,
      linkedPoNumber,
      linkedBidNumber,
      notes,
      paymentBeforeDelivery,
      paymentDelivery,
      paymentAfterDelivery,
      paymentTerm,
      taxType,
      liquidatedDamages,
      liquidatedDamagesPercent,
      deliveryType,
      deliveryTerm,
      shippingLocation,
      dischargeLocation,
      contractDeliveryDate,
      contractEstablishmentConditions,
      interlockingSystem,
      mandatoryDocuments,
      contractTerminationConditions,
    } = 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 updateData: Record<string, unknown> = {
      specificationType,
      specificationManualText,
      unitPriceType,
      warrantyPeriod, // JSON 필드
      currency,
      linkedPoNumber,
      linkedBidNumber,
      notes,
      paymentBeforeDelivery, // JSON 필드
      paymentDelivery,
      paymentAfterDelivery, // JSON 필드
      paymentTerm,
      taxType,
      liquidatedDamages,
      liquidatedDamagesPercent,
      deliveryType,
      deliveryTerm,
      shippingLocation,
      dischargeLocation,
      contractDeliveryDate,
      contractEstablishmentConditions, // JSON 필드
      interlockingSystem,
      mandatoryDocuments, // JSON 필드
      contractTerminationConditions, // JSON 필드
      contractAmount: calculatedContractAmount,
      lastUpdatedAt: new Date(),
      lastUpdatedById: userId,
    }

    // DB에 업데이트 실행
    const [updatedContract] = await db
      .update(generalContracts)
      .set(updateData)
      .where(eq(generalContracts.id, id))
      .returning()

    revalidatePath('/general-contracts')
    revalidatePath(`/general-contracts/detail/${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()
      .from(generalContractItems)
      .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,
        project: itemData.project as string,
        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({
        project: itemData.project as string,
        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,
            project: item.project as string,
            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,
            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,
        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({
        subcontractChecklist: 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 getCommunicationChannel(contractId: number) {
  try {
    const [contract] = await db
      .select({
        communicationChannels: generalContracts.communicationChannels
      })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (!contract) {
      return null
    }

    return contract.communicationChannels as any
  } catch (error) {
    console.error('Error getting communication channel:', error)
    throw new Error('Failed to get communication channel')
  }
}

export async function updateCommunicationChannel(contractId: number, communicationData: Record<string, unknown>, userId: number) {
  try {
    await db
      .update(generalContracts)
      .set({
        communicationChannels: communicationData,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId
      })
      .where(eq(generalContracts.id, contractId))

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

export async function updateLocation(contractId: number, locationData: Record<string, unknown>, userId: number) {
  try {
    await db
      .update(generalContracts)
      .set({
        locations: locationData,
        lastUpdatedAt: new Date(),
        lastUpdatedById: userId
      })
      .where(eq(generalContracts.id, contractId))

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

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

    if (!contract) {
      return null
    }

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

export async function updateContract(id: number, data: Record<string, unknown>) {
  try {
    // 숫자 필드에서 빈 문자열을 null로 변환
    const cleanedData = { ...data }
    const numericFields = [
      'vendorId', 
      '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
      }
    })
    
    // 숫자 필드들 추가 정리
    numericFields.forEach(field => {
      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) => ({
              project: item.project,
              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)
      .where(eq(vendors.status, 'ACTIVE'))
      .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.filePath) {
      // generalContractAttachments 테이블에 저장
      const [attachment] = await db.insert(generalContractAttachments).values({
        contractId,
        documentName,
        fileName: saveResult.fileName || file.name,
        filePath: saveResult.filePath,
        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: '첨부파일 삭제에 실패했습니다.'
    }
  }
}

// Field Service Rate 관련 서버 액션들
export async function getFieldServiceRate(contractId: number) {
  try {
    const result = await db
      .select({ fieldServiceRates: generalContracts.fieldServiceRates })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (result.length === 0) {
      return null
    }

    return result[0].fieldServiceRates as Record<string, unknown> || null
  } catch (error) {
    console.error('Failed to get field service rate:', error)
    throw new Error('Field Service Rate 데이터를 불러오는데 실패했습니다.')
  }
}

export async function updateFieldServiceRate(
  contractId: number, 
  fieldServiceRateData: Record<string, unknown>, 
  userId: number
) {
  try {
    await db
      .update(generalContracts)
      .set({
        fieldServiceRates: fieldServiceRateData,
        updatedAt: new Date(),
        updatedBy: userId
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath('/evcp/general-contracts')
    return { success: true }
  } catch (error) {
    console.error('Failed to update field service rate:', error)
    throw new Error('Field Service Rate 업데이트에 실패했습니다.')
  }
}

// Offset Details 관련 서버 액션들
export async function getOffsetDetails(contractId: number) {
  try {
    const result = await db
      .select({ offsetDetails: generalContracts.offsetDetails })
      .from(generalContracts)
      .where(eq(generalContracts.id, contractId))
      .limit(1)

    if (result.length === 0) {
      return null
    }

    return result[0].offsetDetails as Record<string, unknown> || null
  } catch (error) {
    console.error('Failed to get offset details:', error)
    throw new Error('회입/상계내역 데이터를 불러오는데 실패했습니다.')
  }
}

export async function updateOffsetDetails(
  contractId: number, 
  offsetDetailsData: Record<string, unknown>, 
  userId: number
) {
  try {
    await db
      .update(generalContracts)
      .set({
        offsetDetails: offsetDetailsData,
        updatedAt: new Date(),
        updatedBy: userId
      })
      .where(eq(generalContracts.id, contractId))

    revalidatePath('/evcp/general-contracts')
    return { success: true }
  } catch (error) {
    console.error('Failed to update offset details:', error)
    throw new Error('회입/상계내역 업데이트에 실패했습니다.')
  }
}