path: root/lib/bidding/service.ts

'use server'

import db from '@/db/db'
import {
    biddings,
    biddingListView,
    biddingNoticeTemplate,
    projects,
    biddingDocuments,
    prItemsForBidding,
    specificationMeetings,
    prDocuments,
    biddingConditions,
    users,
    basicContractTemplates,
    vendorsWithTypesView,
    biddingCompanies
} from '@/db/schema'
import {
    eq,
    desc,
    asc,
    and,
    or,
    count,
    sql,
    ilike,
    gte,
    lte,
    SQL,
    like,
    notInArray
} from 'drizzle-orm'

// 사용자 이메일로 사용자 코드 조회
export async function getUserCodeByEmail(email: string): Promise<string | null> {
  try {
    const user = await db
      .select({ userCode: users.userCode })
      .from(users)
      .where(and(eq(users.email, email), eq(users.isActive, true)))
      .limit(1)

    return user[0]?.userCode || null
  } catch (error) {
    console.error('Failed to get user code by email:', error)
    return null
  }
}
import { revalidatePath } from 'next/cache'
import { filterColumns } from '@/lib/filter-columns'
import { CreateBiddingSchema, GetBiddingsSchema, UpdateBiddingSchema } from './validation'
import { saveFile } from '../file-stroage'

// userId를 user.name으로 변환하는 유틸리티 함수
async function getUserNameById(userId: string): Promise<string> {
  try {
    const user = await db
      .select({ name: users.name })
      .from(users)
      .where(eq(users.id, parseInt(userId)))
      .limit(1)
    
    return user[0]?.name || userId // user.name이 없으면 userId를 그대로 반환
  } catch (error) {
    console.error('Failed to get user name:', error)
    return userId // 에러 시 userId를 그대로 반환
  }
}


export async function getBiddingNoticeTemplate() {
    try {
        const result = await db
            .select()
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.type, 'standard'))
            .limit(1)

        return result[0] || null
    } catch (error) {
        console.error('Failed to get bidding notice template:', error)
        throw new Error('입찰공고문 템플릿을 불러오는데 실패했습니다.')
    }
}

export async function saveBiddingNoticeTemplate(formData: {
    title: string
    content: string
}) {
    try {
        const { title, content } = formData

        // 기존 템플릿 확인
        const existing = await db
            .select()
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.type, 'standard'))
            .limit(1)

        if (existing.length > 0) {
            // 업데이트
            await db
                .update(biddingNoticeTemplate)
                .set({
                    title,
                    content,
                    updatedAt: new Date(),
                })
                .where(eq(biddingNoticeTemplate.type, 'standard'))
        } else {
            // 새로 생성
            await db.insert(biddingNoticeTemplate).values({
                type: 'standard',
                title,
                content,
            })
        }

        revalidatePath('/admin/bidding-notice')
        return { success: true, message: '입찰공고문 템플릿이 저장되었습니다.' }
    } catch (error) {
        console.error('Failed to save bidding notice template:', error)
        throw new Error('입찰공고문 템플릿 저장에 실패했습니다.')
    }
}


export async function getBiddings(input: GetBiddingsSchema) {
    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: biddingListView,
                filters: input.filters,
                joinOperator: input.joinOperator || 'and',
            })
        }

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

        if (input.biddingNumber) {
            basicConditions.push(ilike(biddingListView.biddingNumber, `%${input.biddingNumber}%`))
        }

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

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

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

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

        // 날짜 필터들
        if (input.preQuoteDateFrom) {
            basicConditions.push(gte(biddingListView.preQuoteDate, input.preQuoteDateFrom))
        }
        if (input.preQuoteDateTo) {
            basicConditions.push(lte(biddingListView.preQuoteDate, input.preQuoteDateTo))
        }

        if (input.submissionDateFrom) {
            basicConditions.push(gte(biddingListView.submissionStartDate, new Date(input.submissionDateFrom)))
        }
        if (input.submissionDateTo) {
            basicConditions.push(lte(biddingListView.submissionEndDate, new Date(input.submissionDateTo)))
        }

        if (input.createdAtFrom) {
            basicConditions.push(gte(biddingListView.createdAt, new Date(input.createdAtFrom)))
        }
        if (input.createdAtTo) {
            basicConditions.push(lte(biddingListView.createdAt, new Date(input.createdAtTo)))
        }

        // 가격 범위 필터
        if (input.budgetMin) {
            basicConditions.push(gte(biddingListView.budget, input.budgetMin))
        }
        if (input.budgetMax) {
            basicConditions.push(lte(biddingListView.budget, input.budgetMax))
        }

        // Boolean 필터
        if (input.hasSpecificationMeeting === "true") {
            basicConditions.push(eq(biddingListView.hasSpecificationMeeting, true))
        } else if (input.hasSpecificationMeeting === "false") {
            basicConditions.push(eq(biddingListView.hasSpecificationMeeting, false))
        }

        if (input.hasPrDocument === "true") {
            basicConditions.push(eq(biddingListView.hasPrDocument, true))
        } else if (input.hasPrDocument === "false") {
            basicConditions.push(eq(biddingListView.hasPrDocument, false))
        }

        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(biddingListView.biddingNumber, s),
                ilike(biddingListView.title, s),
                ilike(biddingListView.projectName, s),
                ilike(biddingListView.itemName, s),
                ilike(biddingListView.managerName, s),
                ilike(biddingListView.prNumber, s),
                ilike(biddingListView.remarks, 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(biddingListView)
            .where(finalWhere)

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

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

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

        // ✅ 6) 정렬 및 페이징
        const orderByColumns = input.sort.map((sort) => {
            const column = sort.id as keyof typeof biddingListView.$inferSelect
            return sort.desc ? desc(biddingListView[column]) : asc(biddingListView[column])
        })

        if (orderByColumns.length === 0) {
            orderByColumns.push(desc(biddingListView.createdAt))
        }

        // ✅ 7) 메인 쿼리 - 매우 간단해짐!
        const data = await db
            .select()
            .from(biddingListView)
            .where(finalWhere)
            .orderBy(...orderByColumns)
            .limit(input.perPage)
            .offset(offset)

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

        // ✅ 8) 포맷팅 불필요 - 뷰에서 이미 완성된 데이터!
        return { data, pageCount, total }

    } catch (err) {
        console.error("Error in getBiddings:", err)
        return { data: [], pageCount: 0, total: 0 }
    }
}
// 상태별 개수 집계
export async function getBiddingStatusCounts() {
    try {
        const counts = await db
            .select({
                status: biddings.status,
                count: count(),
            })
            .from(biddings)
            .groupBy(biddings.status)

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

// 입찰유형별 개수 집계
export async function getBiddingTypeCounts() {
    try {
        const counts = await db
            .select({
                biddingType: biddings.biddingType,
                count: count(),
            })
            .from(biddings)
            .groupBy(biddings.biddingType)

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

// 담당자별 개수 집계
export async function getBiddingManagerCounts() {
    try {
        const counts = await db
            .select({
                managerName: biddings.managerName,
                count: count(),
            })
            .from(biddings)
            .where(sql`${biddings.managerName} IS NOT NULL AND ${biddings.managerName} != ''`)
            .groupBy(biddings.managerName)

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

// 월별 입찰 생성 통계
export async function getBiddingMonthlyStats(year: number = new Date().getFullYear()) {
    try {
        const stats = await db
            .select({
                month: sql<number>`EXTRACT(MONTH FROM ${biddings.createdAt})`.as('month'),
                count: count(),
            })
            .from(biddings)
            .where(sql`EXTRACT(YEAR FROM ${biddings.createdAt}) = ${year}`)
            .groupBy(sql`EXTRACT(MONTH FROM ${biddings.createdAt})`)
            .orderBy(sql`EXTRACT(MONTH FROM ${biddings.createdAt})`)

        // 1-12월 전체 배열 생성 (없는 월은 0으로)
        const monthlyData = Array.from({ length: 12 }, (_, i) => {
            const month = i + 1
            const found = stats.find(stat => stat.month === month)
            return {
                month,
                count: found?.count || 0,
            }
        })

        return monthlyData
    } catch (error) {
        console.error('Failed to get bidding monthly stats:', error)
        return []
    }
}

export interface CreateBiddingInput extends CreateBiddingSchema {
  // 사양설명회 정보 (선택사항)
  specificationMeeting?: {
    meetingDate: string
    meetingTime: string
    location: string
    address: string
    contactPerson: string
    contactPhone: string
    contactEmail: string
    agenda: string
    materials: string
    notes: string
    isRequired: boolean
    meetingFiles: File[]
  } | null

  // PR 아이템들 (선택사항)
  prItems?: Array<{
    id: string
    prNumber: string
    itemCode: string
    itemInfo: string
    quantity: string
    quantityUnit: string
    totalWeight: string
    weightUnit: string
    materialDescription: string
    hasSpecDocument: boolean
    requestedDeliveryDate: string
    specFiles: File[]
    isRepresentative: boolean
  }>

  // 입찰 조건 (선택사항)
  biddingConditions?: {
    paymentTerms: string
    taxConditions: string
    incoterms: string
    contractDeliveryDate: string
    shippingPort: string
    destinationPort: string
    isPriceAdjustmentApplicable: boolean
    sparePartOptions: string
  }

  // 계약 기간 정보
  contractStartDate?: string
  contractEndDate?: string
}

export interface UpdateBiddingInput extends UpdateBiddingSchema {
  id: number
}

// 자동 입찰번호 생성
export async function generateBiddingNumber(
  userId?: string,
  tx?: any,
  maxRetries: number = 5
): Promise<string> {
  // 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)))
      .limit(1);
    if (user[0]?.userCode && user[0].userCode.length >= 3) {
      purchaseManagerCode = user[0].userCode.substring(0, 3).toUpperCase();
    }
  }
  const managerCode = (purchaseManagerCode && purchaseManagerCode.length >= 3)
    ? purchaseManagerCode.substring(0, 3).toUpperCase()
    : '000';

  const dbInstance = tx || db;
  const prefix = `B${managerCode}`;

  for (let attempt = 0; attempt < maxRetries; attempt++) {
    // 현재 최대 일련번호 조회
    const result = await dbInstance
      .select({ 
        maxNumber: sql<string>`MAX(${biddings.biddingNumber})` 
      })
      .from(biddings)
      .where(like(biddings.biddingNumber, `${prefix}%`));

    let sequence = 1;
    if (result[0]?.maxNumber) {
      const lastSequence = parseInt(result[0].maxNumber.slice(-5));
      if (!isNaN(lastSequence)) {
        sequence = lastSequence + 1;
      }
    }

    const biddingNumber = `${prefix}${sequence.toString().padStart(5, '0')}`;

    // 중복 확인
    const existing = await dbInstance
      .select({ id: biddings.id })
      .from(biddings)
      .where(eq(biddings.biddingNumber, biddingNumber))
      .limit(1);

    if (existing.length === 0) {
      return biddingNumber;
    }

    // 중복이 발견되면 잠시 대기 후 재시도 (동시성 문제 방지)
    await new Promise(resolve => setTimeout(resolve, 10 + Math.random() * 20));
  }

  throw new Error(`Failed to generate unique bidding number after ${maxRetries} attempts`);
}
// 입찰 생성
export async function createBidding(input: CreateBiddingInput, userId: string) {
    try {
      const userName = await getUserNameById(userId)
      return await db.transaction(async (tx) => {
        // 자동 입찰번호 생성
        const biddingNumber = await generateBiddingNumber(userId)
  
        // 프로젝트 정보 조회
        let projectName = input.projectName
        if (input.projectId) {
          const project = await tx
            .select({ code: projects.code, name: projects.name })
            .from(projects)
            .where(eq(projects.id, input.projectId))
            .limit(1)
          
          if (project.length > 0) {
            projectName = `${project[0].code} (${project[0].name})`
          }
        }
  
        // 표준 공고문 템플릿 가져오기
        let standardContent = ''
        if (!input.content) {
          try {
            const template = await tx
              .select({ content: biddingNoticeTemplate.content })
              .from(biddingNoticeTemplate)
              .where(eq(biddingNoticeTemplate.type, 'standard'))
              .limit(1)
            
            if (template.length > 0) {
              standardContent = template[0].content
            }
          } catch (error) {
            console.warn('Failed to load standard template:', error)
          }
        }
  
        // 날짜 변환 함수
        const parseDate = (dateStr?: string) => {
          if (!dateStr) return null
          try {
            return new Date(dateStr)
          } catch {
            return null
          }
        }
  
        // 1. 입찰 생성
        const [newBidding] = await tx
          .insert(biddings)
          .values({
            biddingNumber,
            revision: input.revision || 0,
            
            // 프로젝트 정보
            projectId: input.projectId,
            projectName,
            
            itemName: input.itemName,
            title: input.title,
            description: input.description,
            content: input.content || standardContent,
            
            contractType: input.contractType,
            biddingType: input.biddingType,
            awardCount: input.awardCount,
            contractStartDate: input.contractStartDate ? parseDate(input.contractStartDate) : null,
            contractEndDate: input.contractEndDate ? parseDate(input.contractEndDate) : null,
            
            // 자동 등록일 설정
            biddingRegistrationDate: new Date(),
            submissionStartDate: parseDate(input.submissionStartDate),
            submissionEndDate: parseDate(input.submissionEndDate),
            evaluationDate: parseDate(input.evaluationDate),
            
            hasSpecificationMeeting: input.hasSpecificationMeeting || false,
            hasPrDocument: input.hasPrDocument || false,
            prNumber: input.prNumber,
            
            currency: input.currency,
            budget: input.budget ? parseFloat(input.budget) : null,
            targetPrice: input.targetPrice ? parseFloat(input.targetPrice) : null,
            finalBidPrice: input.finalBidPrice ? parseFloat(input.finalBidPrice) : null,
            
            status: input.status || 'bidding_generated',
            // biddingSourceType: input.biddingSourceType || 'manual',
            isPublic: input.isPublic || false,
            isUrgent: input.isUrgent || false,
            managerName: input.managerName,
            managerEmail: input.managerEmail,
            managerPhone: input.managerPhone,
            
            remarks: input.remarks,
            createdBy: userName,
            updatedBy: userName,
          })
          .returning({ id: biddings.id })
  
        const biddingId = newBidding.id
  
        // 2. 사양설명회 정보 저장 (있는 경우)
        if (input.specificationMeeting) {
          const [newSpecMeeting] = await tx
            .insert(specificationMeetings)
            .values({
              biddingId,
              meetingDate: new Date(input.specificationMeeting.meetingDate),
              meetingTime: input.specificationMeeting.meetingTime,
              location: input.specificationMeeting.location,
              address: input.specificationMeeting.address,
              contactPerson: input.specificationMeeting.contactPerson,
              contactPhone: input.specificationMeeting.contactPhone,
              contactEmail: input.specificationMeeting.contactEmail,
              agenda: input.specificationMeeting.agenda,
              materials: input.specificationMeeting.materials,
              notes: input.specificationMeeting.notes,
              isRequired: input.specificationMeeting.isRequired,
            })
            .returning({ id: specificationMeetings.id })
  
          // 2-1. 사양설명회 첨부파일 저장
          if (input.specificationMeeting.meetingFiles && input.specificationMeeting.meetingFiles.length > 0) {
            for (const file of input.specificationMeeting.meetingFiles) {
              try {
                const saveResult = await saveFile({
                  file,
                  directory: `biddings/${biddingId}/specification-meeting`,
                  originalName: file.name,
                  userId
                })
  
                if (saveResult.success) {
                  await tx.insert(biddingDocuments).values({
                    biddingId,
                    specificationMeetingId: newSpecMeeting.id,
                    documentType: 'specification_meeting',
                    fileName: saveResult.fileName!,
                    originalFileName: saveResult.originalName!,
                    fileSize: saveResult.fileSize!,
                    mimeType: file.type,
                    filePath: saveResult.publicPath!,
                    // publicPath: saveResult.publicPath,
                    title: `사양설명회 - ${file.name}`,
                    isPublic: false,
                    isRequired: false,
                    uploadedBy: userName,
                  })
                } else {
                  console.error(`Failed to save specification meeting file: ${file.name}`, saveResult.error)
                  // 파일 저장 실패해도 전체 트랜잭션은 계속 진행
                }
              } catch (error) {
                console.error(`Error saving specification meeting file: ${file.name}`, error)
              }
            }
          }
        }
  
        // 3. 입찰 조건 저장 (있는 경우)
        if (input.biddingConditions) {
          try {
            await tx.insert(biddingConditions).values({
              biddingId,
              paymentTerms: input.biddingConditions.paymentTerms,
              taxConditions: input.biddingConditions.taxConditions,
              incoterms: input.biddingConditions.incoterms,
              contractDeliveryDate: input.biddingConditions.contractDeliveryDate || null,
              shippingPort: input.biddingConditions.shippingPort,
              destinationPort: input.biddingConditions.destinationPort,
              isPriceAdjustmentApplicable: input.biddingConditions.isPriceAdjustmentApplicable,
              sparePartOptions: input.biddingConditions.sparePartOptions,
            })
          } catch (error) {
            console.error('Error saving bidding conditions:', error)
            // 입찰 조건 저장 실패해도 전체 트랜잭션은 계속 진행
          }
        }

        // 4. PR 아이템들 저장 (있는 경우)
        if (input.prItems && input.prItems.length > 0) {
          for (const prItem of input.prItems) {
            // PR 아이템 저장
            const [newPrItem] = await tx.insert(prItemsForBidding).values({
              biddingId,
              itemNumber: prItem.itemCode, // itemCode를 itemNumber로 매핑
              projectInfo: '', // 필요시 추가
              itemInfo: prItem.itemInfo,
              shi: '', // 필요시 추가
              requestedDeliveryDate: prItem.requestedDeliveryDate ? new Date(prItem.requestedDeliveryDate) : null,
              annualUnitPrice: null, // 필요시 추가
              currency: 'KRW', // 기본값 또는 입력받은 값
              quantity: prItem.quantity ? parseFloat(prItem.quantity) : null,
              quantityUnit: prItem.quantityUnit as any, // enum 타입에 맞게
              totalWeight: prItem.totalWeight ? parseFloat(prItem.totalWeight) : null,
              weightUnit: prItem.weightUnit as any, // enum 타입에 맞게
              materialDescription: '', // 필요시 추가
              prNumber: prItem.prNumber,
              hasSpecDocument: prItem.specFiles.length > 0,
              isRepresentative: prItem.isRepresentative,
            }).returning({ id: prItemsForBidding.id })
  
            // 3-1. 스펙 파일들 저장 (있는 경우)
            if (prItem.specFiles.length > 0) {
              for (let fileIndex = 0; fileIndex < prItem.specFiles.length; fileIndex++) {
                const file = prItem.specFiles[fileIndex]
                try {
                  const saveResult = await saveFile({
                    file,
                    directory: `biddings/${biddingId}/pr-items/${newPrItem.id}/specs`,
                    originalName: file.name,
                    userId
                  })
  
                  if (saveResult.success) {
                    await tx.insert(biddingDocuments).values({
                      biddingId,
                      prItemId: newPrItem.id,
                      documentType: 'spec_document',
                      fileName: saveResult.fileName!,
                      originalFileName: saveResult.originalName!,
                      fileSize: saveResult.fileSize!,
                      mimeType: file.type,
                      filePath: saveResult.publicPath!,
                      // publicPath: saveResult.publicPath,
                      title: `${prItem.itemInfo || prItem.itemCode} 스펙 - ${file.name}`,
                      description: `PR ${prItem.prNumber}의 스펙 문서`,
                      isPublic: false,
                      isRequired: false,
                      uploadedBy: userName,
                    })
                  } else {
                    console.error(`Failed to save spec file: ${file.name}`, saveResult.error)
                    // 파일 저장 실패해도 전체 트랜잭션은 계속 진행
                  }
                } catch (error) {
                  console.error(`Error saving spec file: ${file.name}`, error)
                }
              }
            }
          }
        }
  
        // 캐시 무효화
        revalidatePath('/evcp/bid')
  
        return {
          success: true,
          message: '입찰이 성공적으로 생성되었습니다.',
          data: { id: biddingId, biddingNumber }
        }
      })
    } catch (error) {
      console.error('Error creating bidding:', error)
      return {
        success: false,
        error: error instanceof Error ? error.message : '입찰 생성 중 오류가 발생했습니다.'
      }
    }
  }
// 입찰 수정
export async function updateBidding(input: UpdateBiddingInput, userId: string) {
  try {
    const userName = await getUserNameById(userId)
    // 존재 여부 확인
    const existing = await db
      .select({ id: biddings.id })
      .from(biddings)
      .where(eq(biddings.id, input.id))
      .limit(1)

    if (existing.length === 0) {
      return {
        success: false,
        error: '존재하지 않는 입찰입니다.'
      }
    }

    // 입찰번호 중복 체크 (다른 레코드에서)
    if (input.biddingNumber) {
      const duplicate = await db
        .select({ id: biddings.id })
        .from(biddings)
        .where(eq(biddings.biddingNumber, input.biddingNumber))
        .limit(1)

      if (duplicate.length > 0 && duplicate[0].id !== input.id) {
        return {
          success: false,
          error: '이미 존재하는 입찰번호입니다.'
        }
      }
    }

    // 날짜 문자열을 Date 객체로 변환
    const parseDate = (dateStr?: string) => {
      if (!dateStr) return undefined
      try {
        return new Date(dateStr)
      } catch {
        return undefined
      }
    }

    // 업데이트할 데이터 준비
    const updateData: any = {
      updatedAt: new Date(),
      updatedBy: userName,
    }

    // 정의된 필드들만 업데이트
    if (input.biddingNumber !== undefined) updateData.biddingNumber = input.biddingNumber
    if (input.revision !== undefined) updateData.revision = input.revision
    if (input.projectName !== undefined) updateData.projectName = input.projectName
    if (input.itemName !== undefined) updateData.itemName = input.itemName
    if (input.title !== undefined) updateData.title = input.title
    if (input.description !== undefined) updateData.description = input.description
    if (input.content !== undefined) updateData.content = input.content
    
    if (input.contractType !== undefined) updateData.contractType = input.contractType
    if (input.biddingType !== undefined) updateData.biddingType = input.biddingType
    if (input.awardCount !== undefined) updateData.awardCount = input.awardCount
    if (input.contractStartDate !== undefined) updateData.contractStartDate = parseDate(input.contractStartDate)
    if (input.contractEndDate !== undefined) updateData.contractEndDate = parseDate(input.contractEndDate)
    
    if (input.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(input.submissionStartDate)
    if (input.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(input.submissionEndDate)
    if (input.evaluationDate !== undefined) updateData.evaluationDate = parseDate(input.evaluationDate)
    
    if (input.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = input.hasSpecificationMeeting
    if (input.hasPrDocument !== undefined) updateData.hasPrDocument = input.hasPrDocument
    if (input.prNumber !== undefined) updateData.prNumber = input.prNumber
    
    if (input.currency !== undefined) updateData.currency = input.currency
    if (input.budget !== undefined) updateData.budget = input.budget ? parseFloat(input.budget) : null
    if (input.targetPrice !== undefined) updateData.targetPrice = input.targetPrice ? parseFloat(input.targetPrice) : null
    if (input.finalBidPrice !== undefined) updateData.finalBidPrice = input.finalBidPrice ? parseFloat(input.finalBidPrice) : null
    
    if (input.status !== undefined) updateData.status = input.status
    if (input.isPublic !== undefined) updateData.isPublic = input.isPublic
    if (input.isUrgent !== undefined) updateData.isUrgent = input.isUrgent
    if (input.managerName !== undefined) updateData.managerName = input.managerName
    if (input.managerEmail !== undefined) updateData.managerEmail = input.managerEmail
    if (input.managerPhone !== undefined) updateData.managerPhone = input.managerPhone
    
    if (input.remarks !== undefined) updateData.remarks = input.remarks

    // 입찰 수정
    await db
      .update(biddings)
      .set(updateData)
      .where(eq(biddings.id, input.id))

    revalidatePath('/admin/biddings')
    revalidatePath(`/admin/biddings/${input.id}`)

    return {
      success: true,
      message: '입찰이 성공적으로 수정되었습니다.'
    }

  } catch (error) {
    console.error('Error updating bidding:', error)
    return {
      success: false,
      error: '입찰 수정 중 오류가 발생했습니다.'
    }
  }
}

// 입찰 삭제
export async function deleteBidding(id: number) {
  try {
    const existing = await db
      .select({ id: biddings.id })
      .from(biddings)
      .where(eq(biddings.id, id))
      .limit(1)

    if (existing.length === 0) {
      return {
        success: false,
        error: '존재하지 않는 입찰입니다.'
      }
    }

    await db
      .delete(biddings)
      .where(eq(biddings.id, id))

    revalidatePath('/admin/biddings')

    return {
      success: true,
      message: '입찰이 성공적으로 삭제되었습니다.'
    }

  } catch (error) {
    console.error('Error deleting bidding:', error)
    return {
      success: false,
      error: '입찰 삭제 중 오류가 발생했습니다.'
    }
  }
}

// 단일 입찰 조회
export async function getBiddingById(id: number) {
  try {
    const bidding = await db
      .select()
      .from(biddings)
      .where(eq(biddings.id, id))
      .limit(1)

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

    return bidding[0]
  } catch (error) {
    console.error('Error getting bidding:', error)
    return null
  }
}

// 공통 결과 타입
interface ActionResult<T> {
    success: boolean
    data?: T
    error?: string
}

// 사양설명회 상세 정보 타입
export interface SpecificationMeetingDetails {
    id: number
    biddingId: number
    meetingDate: string
    meetingTime?: string | null
    location: string
    address?: string | null
    contactPerson: string
    contactPhone?: string | null
    contactEmail?: string | null
    agenda?: string | null
    materials?: string | null
    notes?: string | null
    isRequired: boolean
    createdAt: string
    updatedAt: string
    documents: Array<{
        id: number
        fileName: string
        originalFileName: string
        fileSize: number
        filePath: string
        title?: string | null
        uploadedAt: string
        uploadedBy?: string | null
    }>
}

// PR 상세 정보 타입
export interface PRDetails {
    documents: Array<{
        id: number
        documentName: string
        fileName: string
        originalFileName: string
        fileSize: number
        filePath: string
        registeredAt: string
        registeredBy: string
        version?: string | null
        description?: string | null
        createdAt: string
        updatedAt: string
    }>
    items: Array<{
        id: number
        itemNumber?: string | null
        itemInfo: string
        quantity?: number | null
        quantityUnit?: string | null
        requestedDeliveryDate?: string | null
        prNumber?: string | null
        annualUnitPrice?: number | null
        currency: string
        totalWeight?: number | null
        weightUnit?: string | null
        materialDescription?: string | null
        hasSpecDocument: boolean
        createdAt: string
        updatedAt: string
        specDocuments: Array<{
            id: number
            fileName: string
            originalFileName: string
            fileSize: number
            filePath: string
            uploadedAt: string
            title?: string | null
        }>
    }>
}

/**
 * 사양설명회 상세 정보 조회 서버 액션
 */
export async function getSpecificationMeetingDetailsAction(
    biddingId: number
): Promise<ActionResult<SpecificationMeetingDetails>> {
    try {
        // 1. 입력 검증
        if (!biddingId || isNaN(biddingId) || biddingId <= 0) {
            return { 
                success: false, 
                error: "유효하지 않은 입찰 ID입니다" 
            }
        }

        // 2. 사양설명회 기본 정보 조회
        const meeting = await db
            .select()
            .from(specificationMeetings)
            .where(eq(specificationMeetings.biddingId, biddingId))
            .limit(1)

        if (meeting.length === 0) {
            return { 
                success: false, 
                error: "사양설명회 정보를 찾을 수 없습니다" 
            }
        }

        const meetingData = meeting[0]

        // 3. 관련 문서들 조회
        const documents = await db
            .select({
                id: biddingDocuments.id,
                fileName: biddingDocuments.fileName,
                originalFileName: biddingDocuments.originalFileName,
                fileSize: biddingDocuments.fileSize,
                filePath: biddingDocuments.filePath,
                title: biddingDocuments.title,
                uploadedAt: biddingDocuments.uploadedAt,
                uploadedBy: biddingDocuments.uploadedBy,
            })
            .from(biddingDocuments)
            .where(
                and(
                    eq(biddingDocuments.biddingId, biddingId),
                    eq(biddingDocuments.documentType, 'specification_meeting'),
                    eq(biddingDocuments.specificationMeetingId, meetingData.id)
                )
            )

        // 4. 데이터 직렬화 (Date 객체를 문자열로 변환)
        const result: SpecificationMeetingDetails = {
            id: meetingData.id,
            biddingId: meetingData.biddingId,
            meetingDate: meetingData.meetingDate?.toISOString() || '',
            meetingTime: meetingData.meetingTime,
            location: meetingData.location || '',
            address: meetingData.address,
            contactPerson: meetingData.contactPerson || '',
            contactPhone: meetingData.contactPhone,
            contactEmail: meetingData.contactEmail,
            agenda: meetingData.agenda,
            materials: meetingData.materials,
            notes: meetingData.notes,
            isRequired: meetingData.isRequired || false,
            createdAt: meetingData.createdAt?.toISOString() || '',
            updatedAt: meetingData.updatedAt?.toISOString() || '',
            documents: documents.map(doc => ({
                id: doc.id,
                fileName: doc.fileName,
                originalFileName: doc.originalFileName,
                fileSize: doc.fileSize || 0,
                filePath: doc.filePath,
                title: doc.title,
                uploadedAt: doc.uploadedAt?.toISOString() || '',
                uploadedBy: doc.uploadedBy,
            }))
        }

        return {
            success: true,
            data: result
        }

    } catch (error) {
        console.error("사양설명회 상세 정보 조회 실패:", error)
        return { 
            success: false, 
            error: "사양설명회 정보 조회 중 오류가 발생했습니다" 
        }
    }
}

/**
 * PR 상세 정보 조회 서버 액션
 */
export async function getPRDetailsAction(
  biddingId: number
): Promise<ActionResult<PRDetails>> {
  try {
      // 1. 입력 검증
      if (!biddingId || isNaN(biddingId) || biddingId <= 0) {
          return { 
              success: false, 
              error: "유효하지 않은 입찰 ID입니다" 
          }
      }

      // 2. PR 문서들 조회
      const documents = await db
          .select({
              id: prDocuments.id,
              documentName: prDocuments.documentName,
              fileName: prDocuments.fileName,
              originalFileName: prDocuments.originalFileName,
              fileSize: prDocuments.fileSize,
              filePath: prDocuments.filePath,
              registeredAt: prDocuments.registeredAt,
              registeredBy: prDocuments.registeredBy,
              version: prDocuments.version,
              description: prDocuments.description,
              createdAt: prDocuments.createdAt,
              updatedAt: prDocuments.updatedAt,
          })
          .from(prDocuments)
          .where(eq(prDocuments.biddingId, biddingId))

      // 3. PR 아이템들 조회
      const items = await db
          .select()
          .from(prItemsForBidding)
          .where(eq(prItemsForBidding.biddingId, biddingId))

      // 4. 각 아이템별 스펙 문서들 조회
      const itemsWithDocs = await Promise.all(
          items.map(async (item) => {
              const specDocuments = await db
                  .select({
                      id: biddingDocuments.id,
                      fileName: biddingDocuments.fileName,
                      originalFileName: biddingDocuments.originalFileName,
                      fileSize: biddingDocuments.fileSize,
                      filePath: biddingDocuments.filePath,
                      uploadedAt: biddingDocuments.uploadedAt,
                      title: biddingDocuments.title,
                  })
                  .from(biddingDocuments)
                  .where(
                      and(
                          eq(biddingDocuments.biddingId, biddingId),
                          eq(biddingDocuments.documentType, 'spec_document'),
                          eq(biddingDocuments.prItemId, item.id)
                      )
                  )

              // 5. 데이터 직렬화
              return {
                  id: item.id,
                  itemNumber: item.itemNumber,
                  itemInfo: item.itemInfo,
                  quantity: item.quantity ? Number(item.quantity) : null,
                  quantityUnit: item.quantityUnit,
                  requestedDeliveryDate: item.requestedDeliveryDate || null,
                  prNumber: item.prNumber,
                  annualUnitPrice: item.annualUnitPrice ? Number(item.annualUnitPrice) : null,
                  currency: item.currency,
                  totalWeight: item.totalWeight ? Number(item.totalWeight) : null,
                  weightUnit: item.weightUnit,
                  materialDescription: item.materialDescription,
                  hasSpecDocument: item.hasSpecDocument,
                  createdAt: item.createdAt?.toISOString() || '',
                  updatedAt: item.updatedAt?.toISOString() || '',
                  specDocuments: specDocuments.map(doc => ({
                      id: doc.id,
                      fileName: doc.fileName,
                      originalFileName: doc.originalFileName,
                      fileSize: doc.fileSize || 0,
                      filePath: doc.filePath,
                      uploadedAt: doc.uploadedAt?.toISOString() || '',
                      title: doc.title,
                  }))
              }
          })
      )

      const result: PRDetails = {
          documents: documents.map(doc => ({
              id: doc.id,
              documentName: doc.documentName,
              fileName: doc.fileName,
              originalFileName: doc.originalFileName,
              fileSize: doc.fileSize || 0,
              filePath: doc.filePath,
              registeredAt: doc.registeredAt?.toISOString() || '',
              registeredBy: doc.registeredBy,
              version: doc.version,
              description: doc.description,
              createdAt: doc.createdAt?.toISOString() || '',
              updatedAt: doc.updatedAt?.toISOString() || '',
          })),
          items: itemsWithDocs as any
      }

      return {
          success: true,
          data: result
      }

  } catch (error) {
      console.error("PR 상세 정보 조회 실패:", error)
      return { 
          success: false, 
          error: "PR 정보 조회 중 오류가 발생했습니다" 
      }
  }
}

/**
 * 입찰 기본 정보 조회 서버 액션 (선택사항)
 */
export async function getBiddingBasicInfoAction(
    biddingId: number
): Promise<ActionResult<{
    id: number
    title: string
    hasSpecificationMeeting: boolean
    hasPrDocument: boolean
}>> {
    try {
        if (!biddingId || isNaN(biddingId) || biddingId <= 0) {
            return { 
                success: false, 
                error: "유효하지 않은 입찰 ID입니다" 
            }
        }

        // 간단한 입찰 정보만 조회 (성능 최적화)
        const bidding = await db.query.biddings.findFirst({
            where: (biddings, { eq }) => eq(biddings.id, biddingId),
            columns: {
                id: true,
                title: true,
                hasSpecificationMeeting: true,
                hasPrDocument: true,
            }
        })

        if (!bidding) {
            return {
                success: false,
                error: "입찰 정보를 찾을 수 없습니다"
            }
        }

        return {
            success: true,
            data: bidding as any
        }

    } catch (error) {
        console.error("입찰 기본 정보 조회 실패:", error)
        return { 
            success: false, 
            error: "입찰 기본 정보 조회 중 오류가 발생했습니다" 
        }
    }
}

// 입찰 조건 조회
export async function getBiddingConditions(biddingId: number) {
  try {
    // biddingId가 유효하지 않은 경우 early return
    if (!biddingId || isNaN(biddingId) || biddingId <= 0) {
      console.warn('Invalid biddingId provided to getBiddingConditions:', biddingId)
      return null
    }

    const conditions = await db
      .select()
      .from(biddingConditions)
      .where(eq(biddingConditions.biddingId, biddingId))
      .limit(1)

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

    return conditions[0]
  } catch (error) {
    console.error('Error fetching bidding conditions:', error)
    return null
  }
}

// 입찰 조건 업데이트
export async function updateBiddingConditions(
  biddingId: number,
  updates: {
    paymentTerms?: string
    taxConditions?: string
    incoterms?: string
    contractDeliveryDate?: string
    shippingPort?: string
    destinationPort?: string
    isPriceAdjustmentApplicable?: boolean
    sparePartOptions?: string
  }
) {
  try {
    return await db.transaction(async (tx) => {
      // 기존 조건 확인
      const existing = await tx
        .select()
        .from(biddingConditions)
        .where(eq(biddingConditions.biddingId, biddingId))
        .limit(1)

      const updateData = {
        paymentTerms: updates.paymentTerms,
        taxConditions: updates.taxConditions,
        incoterms: updates.incoterms,
        contractDeliveryDate: updates.contractDeliveryDate || null,
        shippingPort: updates.shippingPort,
        destinationPort: updates.destinationPort,
        isPriceAdjustmentApplicable: updates.isPriceAdjustmentApplicable,
        sparePartOptions: updates.sparePartOptions,
        updatedAt: new Date(),
      }

      if (existing.length > 0) {
        // 업데이트
        await tx
          .update(biddingConditions)
          .set(updateData)
          .where(eq(biddingConditions.biddingId, biddingId))
      } else {
        // 새로 생성
        await tx.insert(biddingConditions).values({
          biddingId,
          ...updateData as any,
        })
      }

      // 캐시 무효화
      revalidatePath(`/evcp/bid/${biddingId}`)

      return {
        success: true,
        message: '입찰 조건이 성공적으로 업데이트되었습니다.'
      }
    })
  } catch (error) {
    console.error('Error updating bidding conditions:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '입찰 조건 업데이트 중 오류가 발생했습니다.'
    }
  }
}

// 활성 템플릿 조회 서버 액션
export async function getActiveContractTemplates() {
  try {
    // 활성 상태의 템플릿들 조회
    const templates = await db
      .select({
        id: basicContractTemplates.id,
        templateName: basicContractTemplates.templateName,
        revision: basicContractTemplates.revision,
        status: basicContractTemplates.status,
        filePath: basicContractTemplates.filePath,
        validityPeriod: basicContractTemplates.validityPeriod,
        legalReviewRequired: basicContractTemplates.legalReviewRequired,
        createdAt: basicContractTemplates.createdAt,
      })
      .from(basicContractTemplates)
      .where(eq(basicContractTemplates.status, 'ACTIVE'))
      .orderBy(basicContractTemplates.templateName);

    return {
      templates
    };

  } catch (error) {
    console.error('활성 템플릿 조회 실패:', error);
    throw new Error('템플릿 조회에 실패했습니다.');
  }
}

// 입찰에 참여하지 않은 벤더만 검색 (중복 방지)
export async function searchVendorsForBidding(searchTerm: string = "", biddingId: number) {
  try {
    let whereCondition;

    if (searchTerm.trim()) {
      const s = `%${searchTerm.trim()}%`;
      whereCondition = or(
        ilike(vendorsWithTypesView.vendorName, s),
        ilike(vendorsWithTypesView.vendorCode, s)
      );
    }

    // 이미 해당 입찰에 참여중인 벤더 ID들을 가져옴
    const participatingVendorIds = await db
      .select({ companyId: biddingCompanies.companyId })
      .from(biddingCompanies)
      .where(eq(biddingCompanies.biddingId, biddingId));

    const excludedIds = participatingVendorIds.map(p => p.companyId);

    const result = await db
      .select({
        id: vendorsWithTypesView.id,
        vendorName: vendorsWithTypesView.vendorName,
        vendorCode: vendorsWithTypesView.vendorCode,
        status: vendorsWithTypesView.status,
        country: vendorsWithTypesView.country,
      })
      .from(vendorsWithTypesView)
      .where(
        and(
          whereCondition,
          // 이미 참여중인 벤더 제외
          excludedIds.length > 0 ? notInArray(vendorsWithTypesView.id, excludedIds) : undefined,
          // ACTIVE 상태인 벤더만 검색
          // eq(vendorsWithTypesView.status, "ACTIVE"),
        )
      )
      .orderBy(asc(vendorsWithTypesView.vendorName));
      

    return result;
  } catch (error) {
    console.error('Error searching vendors for bidding:', error)
    return []
  }
}