path: root/lib/bidding/actions.ts

"use server"

import db from "@/db/db"
import { eq, and, sql } from "drizzle-orm"
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import {
  biddings,
  biddingCompanies,
  prItemsForBidding,
  companyPrItemBids,
  vendors,
  generalContracts,
  generalContractItems,
  biddingConditions,
  biddingDocuments,
  users
} from "@/db/schema"
import { createPurchaseOrder } from "@/lib/soap/ecc/send/create-po-bidding"
import { getCurrentSAPDate } from "@/lib/soap/utils"
import { generateContractNumber } from "@/lib/general-contracts/service"
import { saveFile } from "@/lib/file-stroage"

// TO Contract
export async function transmitToContract(biddingId: number, userId: number) {
  try {
    // 1. 입찰 정보 조회 (단순 쿼리)
    const bidding = await db.select()
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

    if (!bidding || bidding.length === 0) {
      throw new Error("입찰 정보를 찾을 수 없습니다.")
    }

    const biddingData = bidding[0]

    // 2. 입찰 조건 정보 조회
    const biddingConditionData = await db.select()
      .from(biddingConditions)
      .where(eq(biddingConditions.biddingId, biddingId))
      .limit(1)

    const biddingCondition = biddingConditionData.length > 0 ? biddingConditionData[0] : null

    // 3. 낙찰된 업체들 조회 (biddingCompanies.id 포함)
    const winnerCompaniesData = await db.select({
      id: biddingCompanies.id,
      companyId: biddingCompanies.companyId,
      finalQuoteAmount: biddingCompanies.finalQuoteAmount,
      awardRatio: biddingCompanies.awardRatio,
      vendorCode: vendors.vendorCode,
      vendorName: vendors.vendorName
    })
    .from(biddingCompanies)
    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
    .where(
      and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isWinner, true)
      )
    )

    // 상태 검증
    if (biddingData.status !== 'vendor_selected') {
      throw new Error("업체 선정이 완료되지 않은 입찰입니다.")
    }

    // 낙찰된 업체 검증
    if (winnerCompaniesData.length === 0) {
      throw new Error("낙찰된 업체가 없습니다.")
    }

    // 일반/매각 입찰의 경우 비율 합계 100% 검증
    const contractType = biddingData.contractType
    if (contractType === 'general' || contractType === 'sale') {
      const totalRatio = winnerCompaniesData.reduce((sum, company) =>
        sum + (Number(company.awardRatio) || 0), 0)

      if (totalRatio !== 100) {
        throw new Error(`일반/매각 입찰의 경우 비율 합계가 100%여야 합니다. 현재 합계: ${totalRatio}%`)
      }
    }

    for (const winnerCompany of winnerCompaniesData) {
      // winnerCompany에서 직접 정보 사용
      const awardRatio = (Number(winnerCompany.awardRatio) || 100) / 100
      const biddingCompanyId = winnerCompany.id

      // 현재 winnerCompany의 입찰 데이터 조회
      const companyBids = await db.select({
        prItemId: companyPrItemBids.prItemId,
        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,
        bidUnitPrice: companyPrItemBids.bidUnitPrice,
        bidAmount: companyPrItemBids.bidAmount,
        currency: companyPrItemBids.currency,
        // PR 아이템 정보도 함께 조회
        projectId: prItemsForBidding.projectId,
        materialGroupNumber: prItemsForBidding.materialGroupNumber,
        materialGroupInfo: prItemsForBidding.materialGroupInfo,
        materialInfo: prItemsForBidding.materialInfo,
        specification: prItemsForBidding.specification,
        quantity: prItemsForBidding.quantity,
        quantityUnit: prItemsForBidding.quantityUnit,
      })
      .from(companyPrItemBids)
      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))
      .where(eq(companyPrItemBids.biddingCompanyId, biddingCompanyId))

      // 발주비율에 따른 최종 계약금액 계산
      let totalContractAmount = 0
      if (companyBids.length > 0) {
        for (const bid of companyBids) {
          const originalQuantity = Number(bid.quantity) || 0
          const bidUnitPrice = Number(bid.bidUnitPrice) || 0
          const finalQuantity = originalQuantity * awardRatio
          const finalAmount = finalQuantity * bidUnitPrice
          totalContractAmount += finalAmount
        }
      }

      // 계약 번호 자동 생성 (실제 규칙에 맞게)
      const safeUserId = userId ? String(userId) : '0';
      const contractNumber = await generateContractNumber(safeUserId, biddingData.contractType)
      console.log('Generated contractNumber:', contractNumber)

      // 연동제 여부 변환 (boolean -> Y/N)
      const interlockingSystem = biddingCondition?.isPriceAdjustmentApplicable 
        ? 'Y' 
        : (biddingCondition?.isPriceAdjustmentApplicable === false ? 'N' : null)

      // general-contract 생성 (발주비율 계산된 최종 금액 사용)
      const contractResult = await db.insert(generalContracts).values({
        contractNumber,
        revision: 0,
        contractSourceType: 'bid', // 입찰에서 생성됨
        status: 'Draft',
        category: biddingData.contractType || 'general',
        name: biddingData.title,
        vendorId: winnerCompany.companyId,
        linkedBidNumber: biddingData.biddingNumber,
        contractAmount: !isNaN(totalContractAmount) ? String(totalContractAmount) : null, // 발주비율 계산된 최종 금액 사용
        startDate: biddingData.contractStartDate || null,
        endDate: biddingData.contractEndDate || null,
        currency: biddingData.currency || 'KRW',
        // 계약 조건 정보 추가
        paymentTerm: biddingCondition?.paymentTerms || null,
        paymentDelivery: biddingCondition?.paymentTerms || null, // 지급조건 (납품 지급조건)
        taxType: biddingCondition?.taxConditions || 'V0',
        deliveryTerm: biddingCondition?.incoterms || 'FOB',
        shippingLocation: biddingCondition?.shippingPort || null,
        dischargeLocation: biddingCondition?.destinationPort || null,
        contractDeliveryDate: biddingCondition?.contractDeliveryDate || null, // 계약납기일
        interlockingSystem: interlockingSystem, // 연동제 여부
        registeredById: userId,
        lastUpdatedById: userId,
      }).returning({ id: generalContracts.id })
      console.log('contractResult', contractResult)
      const contractId = contractResult[0].id

      // 현재 winnerCompany의 품목정보 생성 (발주비율 적용)
      if (companyBids.length > 0) {
        console.log(`Creating ${companyBids.length} contract items for winner company ${winnerCompany.companyId} with award ratio ${awardRatio}`)
        for (const bid of companyBids) {
          // 발주비율에 따른 최종 수량 계산 (중량 제외)
          const originalQuantity = Number(bid.quantity) || 0
          const bidUnitPrice = Number(bid.bidUnitPrice) || 0

          const finalQuantity = originalQuantity * awardRatio
          const finalAmount = finalQuantity * bidUnitPrice

          await db.insert(generalContractItems).values({
            contractId: contractId,
            projectId: bid.projectId,
            itemCode: bid.materialGroupNumber || '',
            itemInfo: bid.materialGroupInfo || '',
            specification: bid.specification || '',
            quantity: !isNaN(finalQuantity) ? String(finalQuantity) : null,
            quantityUnit: bid.quantityUnit || '',
            totalWeight: null, // 중량 정보 제외
            weightUnit: '', // 중량 단위 제외
            contractDeliveryDate: bid.proposedDeliveryDate || null,
            contractUnitPrice: !isNaN(bidUnitPrice) ? String(bidUnitPrice) : null,
            contractAmount: !isNaN(finalAmount) ? String(finalAmount) : null,
            contractCurrency: bid.currency || biddingData.currency || 'KRW',
          })
        }
        console.log(`Created ${companyBids.length} contract items for winner company ${winnerCompany.companyId}`)
      } else {
        console.log(`No bid data found for winner company ${winnerCompany.companyId}`)
      }
    }

    return { success: true, message: `${winnerCompaniesData.length}개의 계약서가 생성되었습니다.` }

  } catch (error) {
    console.error('TO Contract 실패:', error)
    throw new Error(error instanceof Error ? error.message : '계약서 생성에 실패했습니다.')
  }
}

// TO PO
export async function transmitToPO(biddingId: number) {
  try {
    // 1. 입찰 정보 조회
    const biddingData = await db.select()
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

    if (!biddingData || biddingData.length === 0) {
      throw new Error("입찰 정보를 찾을 수 없습니다.")
    }

    const bidding = biddingData[0]

    if (bidding.status !== 'vendor_selected') {
      throw new Error("업체 선정이 완료되지 않은 입찰입니다.")
    }

    // 2. 입찰 조건 정보 조회
    const biddingConditionData = await db.select()
      .from(biddingConditions)
      .where(eq(biddingConditions.biddingId, biddingId))
      .limit(1)

    const biddingCondition = biddingConditionData.length > 0 ? biddingConditionData[0] : null

    // 3. 낙찰된 업체들 조회 (발주비율 포함)
    const winnerCompaniesRaw = await db.select({
      id: biddingCompanies.id,
      companyId: biddingCompanies.companyId,
      finalQuoteAmount: biddingCompanies.finalQuoteAmount,
      awardRatio: biddingCompanies.awardRatio,
      vendorCode: vendors.vendorCode,
      vendorName: vendors.vendorName
    })
    .from(biddingCompanies)
    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
    .where(
      and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isWinner, true)
      )
    )

    if (winnerCompaniesRaw.length === 0) {
      throw new Error("낙찰된 업체가 없습니다.")
    }

    // 일반/매각 입찰의 경우 비율 합계 100% 검증
    const contractType = bidding.contractType
    if (contractType === 'general' || contractType === 'sale') {
      const totalRatio = winnerCompaniesRaw.reduce((sum, company) =>
        sum + (Number(company.awardRatio) || 0), 0)

      if (totalRatio !== 100) {
        throw new Error(`일반/매각 입찰의 경우 비율 합계가 100%여야 합니다. 현재 합계: ${totalRatio}%`)
      }
    }

    // 4. 낙찰된 업체들의 입찰 데이터 조회 (발주비율 적용)
    type POItem = {
      prItemId: number
      proposedDeliveryDate: string | null
      bidUnitPrice: string | null
      bidAmount: string | null
      currency: string | null
      itemNumber: string | null
      itemInfo: string | null
      materialDescription: string | null
      quantity: string | null
      quantityUnit: string | null
      finalQuantity: number
      finalAmount: number
      awardRatio: number
      vendorCode: string | null
      vendorName: string | null
      companyId: number
    }
    const poItems: POItem[] = []
    for (const winner of winnerCompaniesRaw) {
      const awardRatio = (Number(winner.awardRatio) || 100) / 100

      const companyBids = await db.select({
        prItemId: companyPrItemBids.prItemId,
        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,
        bidUnitPrice: companyPrItemBids.bidUnitPrice,
        bidAmount: companyPrItemBids.bidAmount,
        currency: companyPrItemBids.currency,
        // PR 아이템 정보
        itemNumber: prItemsForBidding.itemNumber,
        itemInfo: prItemsForBidding.itemInfo,
        materialDescription: prItemsForBidding.specification,
        quantity: prItemsForBidding.quantity,
        quantityUnit: prItemsForBidding.quantityUnit,
      })
      .from(companyPrItemBids)
      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))
      .where(eq(companyPrItemBids.biddingCompanyId, winner.id))

      // 발주비율 적용하여 PO 아이템 생성 (중량 제외)
      for (const bid of companyBids) {
        const originalQuantity = Number(bid.quantity) || 0
        const bidUnitPrice = Number(bid.bidUnitPrice) || 0

        const finalQuantity = originalQuantity * awardRatio
        const finalAmount = finalQuantity * bidUnitPrice

        poItems.push({
          ...bid,
          finalQuantity,
          finalAmount,
          awardRatio,
          vendorCode: winner.vendorCode,
          vendorName: winner.vendorName,
          companyId: winner.companyId,
        } as POItem)
      }
    }

    // 5. PO 데이터 구성 (bidding condition 정보와 발주비율 적용된 데이터 사용)
    const poData = {
      T_Bidding_HEADER: winnerCompaniesRaw.map((company) => ({
        ANFNR: bidding.biddingNumber,
        LIFNR: company.vendorCode || `VENDOR${company.companyId}`,
        ZPROC_IND: 'A', // 구매 처리 상태
        ANGNR: bidding.biddingNumber,
        WAERS: bidding.currency || 'KRW',
        ZTERM: biddingCondition?.paymentTerms || '0001', // 지급조건
        INCO1: biddingCondition?.incoterms || 'FOB', // Incoterms
        INCO2: biddingCondition?.destinationPort || biddingCondition?.shippingPort || 'Seoul, Korea',
        MWSKZ: biddingCondition?.taxConditions || 'V0', // 세금 코드
        LANDS: 'KR',
        ZRCV_DT: getCurrentSAPDate(),
        ZATTEN_IND: 'Y',
        IHRAN: getCurrentSAPDate(),
        TEXT: `PO from Bidding: ${bidding.title}`,
      })),
      T_Bidding_ITEM: poItems.map((item, index) => ({
        ANFNR: bidding.biddingNumber,
        ANFPS: (index + 1).toString().padStart(5, '0'),
        LIFNR: item.vendorCode || `VENDOR${item.companyId}`,
        NETPR: item.bidUnitPrice?.toString() || '0',
        PEINH: '1',
        BPRME: item.quantityUnit || 'EA',
        NETWR: item.finalAmount?.toString() || '0',
        BRTWR: (Number(item.finalAmount || 0) * 1.1).toString(), // 10% 부가세 가정
        LFDAT: item.proposedDeliveryDate ? new Date(item.proposedDeliveryDate).toISOString().split('T')[0] : getCurrentSAPDate(),
      })),
      T_PR_RETURN: [{
        ANFNR: bidding.biddingNumber,
        ANFPS: '00001',
        EBELN: `PR${bidding.biddingNumber}`,
        EBELP: '00001',
        MSGTY: 'S',
        MSGTXT: 'Success'
      }]
    }

    // 3. SAP으로 PO 전송
    console.log('SAP으로 PO 전송할 poData', poData)
    const result = await createPurchaseOrder(poData)

    if (!result.success) {
      throw new Error(result.message)
    }

    return { success: true, message: result.message }

  } catch (error) {
    console.error('TO PO 실패:', error)
    throw new Error(error instanceof Error ? error.message : 'PO 전송에 실패했습니다.')
  }
}

// 낙찰된 업체들의 상세 정보 조회 (발주비율에 따른 계산 포함)
export async function getWinnerDetails(biddingId: number) {
  try {
    // 1. 입찰 정보 조회 (contractType 포함)
    const biddingInfo = await db.select({
      contractType: biddings.contractType,
    })
    .from(biddings)
    .where(eq(biddings.id, biddingId))
    .limit(1)

    if (!biddingInfo || biddingInfo.length === 0) {
      return { success: false, error: '입찰 정보를 찾을 수 없습니다.' }
    }

    // 2. 낙찰된 업체들 조회
    const winnerCompanies = await db.select({
      id: biddingCompanies.id,
      companyId: biddingCompanies.companyId,
      finalQuoteAmount: biddingCompanies.finalQuoteAmount,
      awardRatio: biddingCompanies.awardRatio,
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      // contractType은 biddingInfo에서 가져옴
    })
    .from(biddingCompanies)
    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
    .where(
      and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isWinner, true)
      )
    )

    if (winnerCompanies.length === 0) {
      return { success: false, error: '낙찰된 업체가 없습니다.' }
    }

    // 일반/매각 입찰의 경우 비율 합계 100% 검증
    const contractType = biddingInfo[0].contractType
    if (contractType === 'general' || contractType === 'sale') {
      const totalRatio = winnerCompanies.reduce((sum, company) =>
        sum + (Number(company.awardRatio) || 0), 0)

      if (totalRatio !== 100) {
        return { success: false, error: `일반/매각 입찰의 경우 비율 합계가 100%여야 합니다. 현재 합계: ${totalRatio}%` }
      }
    }

    // 2. 각 낙찰 업체의 입찰 품목 정보 조회
    const winnerDetails = []

    for (const winner of winnerCompanies) {
      // 업체의 입찰 품목 정보 조회
      const companyBids = await db.select({
        prItemId: companyPrItemBids.prItemId,
        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,
        bidUnitPrice: companyPrItemBids.bidUnitPrice,
        bidAmount: companyPrItemBids.bidAmount,
        currency: companyPrItemBids.currency,
        // PR 아이템 정보
        itemNumber: prItemsForBidding.itemNumber,
        itemInfo: prItemsForBidding.itemInfo,
        materialDescription: prItemsForBidding.specification,
        quantity: prItemsForBidding.quantity,
        quantityUnit: prItemsForBidding.quantityUnit,
      })
      .from(companyPrItemBids)
      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))
      .where(eq(companyPrItemBids.biddingCompanyId, winner.id))

      // 발주비율에 따른 계산 (백분율을 실제 비율로 변환, 중량 제외)
      const awardRatio = (Number(winner.awardRatio) || 100) / 100
      const calculatedItems = companyBids.map(bid => {
        const originalQuantity = Number(bid.quantity) || 0
        const bidUnitPrice = Number(bid.bidUnitPrice) || 0

        // 발주비율에 따른 최종 수량 계산
        const finalQuantity = originalQuantity * awardRatio
        const finalWeight = 0 // 중량 제외
        const finalAmount = finalQuantity * bidUnitPrice

        return {
          ...bid,
          finalQuantity,
          finalWeight,
          finalAmount,
          awardRatio,
        }
      })

      // 업체 총 견적가 계산
      const totalFinalAmount = calculatedItems.reduce((sum, item) => sum + item.finalAmount, 0)

      winnerDetails.push({
        ...winner,
        items: calculatedItems,
        totalFinalAmount,
        awardRatio: Number(winner.awardRatio) || 1,
        contractType: contractType,
      })
    }

    return {
      success: true,
      data: winnerDetails
    }

  } catch (error) {
    console.error('Winner details 조회 실패:', error)
    return {
      success: false,
      error: '낙찰 업체 상세 정보 조회에 실패했습니다.'
    }
  }
}

// 폐찰하기 액션
export async function bidClosureAction(
  biddingId: number,
  formData: {
    description: string
    files: File[]
  },
  userId: string | undefined
) {
  if (!userId) {
    return {
      success: false,
      error: '사용자 정보가 필요합니다.'
    }
  }

  try {
    const userName = await getUserNameById(userId)

    return await db.transaction(async (tx) => {
      // 1. 입찰 정보 확인
      const [existingBidding] = await tx
        .select()
        .from(biddings)
        .where(eq(biddings.id, biddingId))
        .limit(1)

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

      // 2. 유찰 상태인지 확인
      if (existingBidding.status !== 'bidding_disposal') {
        return {
          success: false,
          error: '유찰 상태인 입찰만 폐찰할 수 있습니다.'
        }
      }

      // 3. 입찰 상태를 폐찰로 변경하고 설명 저장
      await tx
        .update(biddings)
        .set({
          status: 'bid_closure',
          description: formData.description,
          updatedAt: new Date(),
          updatedBy: userName,
        })
        .where(eq(biddings.id, biddingId))

      // 4. 첨부파일들 저장 (evaluation_doc로 저장)
      if (formData.files && formData.files.length > 0) {
        for (const file of formData.files) {
          try {
            const saveResult = await saveFile({
              file,
              directory: `biddings/${biddingId}/closure-documents`,
              originalName: file.name,
              userId
            })

            if (saveResult.success) {
              await tx.insert(biddingDocuments).values({
                biddingId,
                documentType: 'evaluation_doc',
                fileName: saveResult.fileName!,
                originalFileName: saveResult.originalName!,
                fileSize: saveResult.fileSize!,
                mimeType: file.type,
                filePath: saveResult.publicPath!,
                title: `폐찰 문서 - ${file.name}`,
                description: formData.description,
                isPublic: false,
                isRequired: false,
                uploadedBy: userName,
              })
            } else {
              console.error(`Failed to save closure file: ${file.name}`, saveResult.error)
            }
          } catch (error) {
            console.error(`Error saving closure file: ${file.name}`, error)
          }
        }
      }

      return {
        success: true,
        message: '폐찰이 완료되었습니다.'
      }
    })

  } catch (error) {
    console.error('폐찰 실패:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '폐찰 중 오류가 발생했습니다.'
    }
  }
}

// 유찰취소 액션
export async function cancelDisposalAction(
  biddingId: number,
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)

    return await db.transaction(async (tx) => {
      // 1. 입찰 정보 확인
      const [existingBidding] = await tx
        .select()
        .from(biddings)
        .where(eq(biddings.id, biddingId))
        .limit(1)

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

      // 2. 유찰 또는 폐찰 상태인지 확인
      if (existingBidding.status !== 'bidding_disposal' && existingBidding.status !== 'bid_closure') {
        return {
          success: false,
          error: '유찰 또는 폐찰 상태인 입찰만 취소할 수 있습니다.'
        }
      }

      // 3. 입찰 상태를 입찰평가중으로 변경
      await tx
        .update(biddings)
        .set({
          status: 'evaluation_of_bidding',
          updatedAt: new Date(),
          updatedBy: userName,
        })
        .where(eq(biddings.id, biddingId))
        
      return {
        success: true,
        message: '유찰 취소가 완료되었습니다.'
      }
    })

  } catch (error) {
    console.error('유찰취소 실패:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '유찰취소 중 오류가 발생했습니다.'
    }
  }
}

// 사용자 이름 조회 헬퍼 함수
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
  } catch (error) {
    console.error('Failed to get user name:', error)
    return userId
  }
}

// 개찰 액션 (조기개찰 포함)
export async function openBiddingAction(biddingId: number) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.name) {
      return { success: false, message: '인증이 필요합니다.' }
    }

    const userName = session.user.name

    return await db.transaction(async (tx) => {
      // 1. 입찰 정보 확인
      const [bidding] = await tx
        .select({
          id: biddings.id,
          status: biddings.status,
          submissionEndDate: biddings.submissionEndDate,
          title: biddings.title
        })
        .from(biddings)
        .where(eq(biddings.id, biddingId))
        .limit(1)

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

      const now = new Date()
      const submissionEndDate = bidding.submissionEndDate ? new Date(bidding.submissionEndDate) : null
      const isDeadlinePassed = submissionEndDate && now > submissionEndDate

      // 2. 개찰 가능 여부 확인
      if (!isDeadlinePassed) {
        // 마감일이 지나지 않았으면 조기개찰 조건 확인
        // 조기개찰 조건: 모든 대상 업체가 응찰(최종제출)했거나 포기했는지 확인 (미제출 0)

        const [stats] = await tx
          .select({
            participantExpected: sql<number>`COUNT(*)`.as('participant_expected'),
            participantFinalSubmitted: sql<number>`COUNT(CASE WHEN invitation_status = 'bidding_submitted' THEN 1 END)`.as('participant_final_submitted'),
            participantDeclined: sql<number>`COUNT(CASE WHEN invitation_status IN ('bidding_declined', 'bidding_cancelled') THEN 1 END)`.as('participant_declined'),
          })
          .from(biddingCompanies)
          .where(eq(biddingCompanies.biddingId, biddingId))

        const participantExpected = Number(stats.participantExpected) || 0
        const participantFinalSubmitted = Number(stats.participantFinalSubmitted) || 0
        const participantDeclined = Number(stats.participantDeclined) || 0

        // 조건: 전체 대상 = 최종제출 + 포기
        if (participantExpected !== participantFinalSubmitted + participantDeclined) {
            const pending = participantExpected - (participantFinalSubmitted + participantDeclined);
            return { 
                success: false, 
                message: `입찰서 제출기간이 종료되지 않았으며, 최종제출하지 않은 업체가 ${pending}곳 있어 조기개찰할 수 없습니다.` 
            }
        }
      }

      // 3. 입찰평가중 상태로 변경
      await tx
        .update(biddings)
        .set({
          status: 'evaluation_of_bidding',
          openedAt: new Date(),
          openedBy: userName,
          updatedAt: new Date(),
          updatedBy: userName,
        })
        .where(eq(biddings.id, biddingId))

      return { success: true, message: isDeadlinePassed ? '개찰이 완료되었습니다.' : '조기개찰이 완료되었습니다.' }
    })

  } catch (error) {
    console.error('개찰 실패:', error)
    return {
      success: false,
      message: error instanceof Error ? error.message : '개찰 중 오류가 발생했습니다.'
    }
  }
}