path: root/lib/bidding/detail/service.ts

'use server'

import db from '@/db/db'
import { biddings, prItemsForBidding, biddingDocuments, biddingCompanies, vendors, companyPrItemBids, companyConditionResponses, vendorSelectionResults, BiddingListItem, biddingConditions } from '@/db/schema'
import { eq, and, sql, desc, ne } from 'drizzle-orm'
import { revalidatePath } from 'next/cache'

// 데이터 조회 함수들
export interface BiddingDetailData {
  bidding: Awaited<ReturnType<typeof getBiddingById>>
  quotationDetails: QuotationDetails | null
  quotationVendors: QuotationVendor[]
  biddingCompanies: Awaited<ReturnType<typeof getBiddingCompaniesData>>
  prItems: Awaited<ReturnType<typeof getPRItemsForBidding>>
}

// getBiddingById 함수 임포트 (기존 함수 재사용)
import { getBiddingById, getPRDetailsAction } from '@/lib/bidding/service'

// Promise.all을 사용하여 모든 데이터를 병렬로 조회
export async function getBiddingDetailData(biddingId: number): Promise<BiddingDetailData> {
  const [
    bidding,
    quotationDetails,
    quotationVendors,
    biddingCompanies,
    prItems
  ] = await Promise.all([
    getBiddingById(biddingId),
    getQuotationDetails(biddingId),
    getQuotationVendors(biddingId),
    getBiddingCompaniesData(biddingId),
    getPRItemsForBidding(biddingId)
  ])

  return {
    bidding,
    quotationDetails,
    quotationVendors,
    biddingCompanies,
    prItems
  }
}
export interface QuotationDetails {
  biddingId: number
  estimatedPrice: number // 예상액
  lowestQuote: number // 최저견적가
  averageQuote: number // 평균견적가
  targetPrice: number // 내정가
  quotationCount: number // 견적 수
  lastUpdated: string // 최종 업데이트일
}

export interface QuotationVendor {
  id: number
  biddingId: number
  vendorId: number
  vendorName: string
  vendorCode: string
  contactPerson: string
  contactEmail: string
  contactPhone: string
  quotationAmount: number // 견적금액
  currency: string
  submissionDate: string // 제출일
  isWinner: boolean // 낙찰여부
  awardRatio: number // 발주비율
  status: 'pending' | 'submitted' | 'selected' | 'rejected'
  // companyConditionResponses에서 가져온 입찰 조건들
  paymentTermsResponse?: string // 지급조건 응답
  taxConditionsResponse?: string // 세금조건 응답
  incotermsResponse?: string // 운송조건 응답
  proposedContractDeliveryDate?: string // 제안 계약납기일
  proposedShippingPort?: string // 제안 선적지
  proposedDestinationPort?: string // 제안 도착지
  priceAdjustmentResponse?: boolean // 연동제 적용 응답
  sparePartResponse?: string // 스페어파트 응답
  additionalProposals?: string // 추가 제안사항
  documents: Array<{
    id: number
    fileName: string
    originalFileName: string
    filePath: string
    uploadedAt: string
  }>
}

// 견적 시스템에서 내정가 및 관련 정보를 가져오는 함수
export async function getQuotationDetails(biddingId: number): Promise<QuotationDetails | null> {
  try {
    // bidding_companies 테이블에서 견적 데이터를 집계
    const quotationStats = await db
      .select({
        biddingId: biddingCompanies.biddingId,
        estimatedPrice: sql<number>`AVG(${biddingCompanies.finalQuoteAmount})`.as('estimated_price'),
        lowestQuote: sql<number>`MIN(${biddingCompanies.finalQuoteAmount})`.as('lowest_quote'),
        averageQuote: sql<number>`AVG(${biddingCompanies.finalQuoteAmount})`.as('average_quote'),
        targetPrice: sql<number>`AVG(${biddings.targetPrice})`.as('target_price'),
        quotationCount: sql<number>`COUNT(*)`.as('quotation_count'),
        lastUpdated: sql<string>`MAX(${biddingCompanies.updatedAt})`.as('last_updated')
      })
      .from(biddingCompanies)
      .leftJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        sql`${biddingCompanies.finalQuoteAmount} IS NOT NULL`
      ))
      .groupBy(biddingCompanies.biddingId)
      .limit(1)

    if (quotationStats.length === 0) {
      return {
        biddingId,
        estimatedPrice: 0,
        lowestQuote: 0,
        averageQuote: 0,
        targetPrice: 0,
        quotationCount: 0,
        lastUpdated: new Date().toISOString()
      }
    }

    const stat = quotationStats[0]

    return {
      biddingId,
      estimatedPrice: Number(stat.estimatedPrice) || 0,
      lowestQuote: Number(stat.lowestQuote) || 0,
      averageQuote: Number(stat.averageQuote) || 0,
      targetPrice: Number(stat.targetPrice) || 0,
      quotationCount: Number(stat.quotationCount) || 0,
      lastUpdated: stat.lastUpdated || new Date().toISOString()
    }
  } catch (error) {
    console.error('Failed to get quotation details:', error)
    return null
  }
}

// bidding_companies 테이블을 메인으로 vendors 테이블을 조인하여 협력업체 정보 조회
export async function getBiddingCompaniesData(biddingId: number) {
  try {
    const companies = await db
      .select({
        id: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        companyId: biddingCompanies.companyId,
        companyName: vendors.vendorName,
        companyCode: vendors.vendorCode,
        invitationStatus: biddingCompanies.invitationStatus,
        invitedAt: biddingCompanies.invitedAt,
        respondedAt: biddingCompanies.respondedAt,
        preQuoteAmount: biddingCompanies.preQuoteAmount,
        preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt,
        isPreQuoteSelected: biddingCompanies.isPreQuoteSelected,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt,
        isWinner: biddingCompanies.isWinner,
        notes: biddingCompanies.notes,
        contactPerson: biddingCompanies.contactPerson,
        contactEmail: biddingCompanies.contactEmail,
        contactPhone: biddingCompanies.contactPhone,
        createdAt: biddingCompanies.createdAt,
        updatedAt: biddingCompanies.updatedAt
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(eq(biddingCompanies.biddingId, biddingId))
      .orderBy(desc(biddingCompanies.finalQuoteAmount))

    return companies
  } catch (error) {
    console.error('Failed to get bidding companies data:', error)
    return []
  }
}

// prItemsForBidding 테이블에서 품목 정보 조회
export async function getPRItemsForBidding(biddingId: number) {
  try {
    const items = await db
      .select()
      .from(prItemsForBidding)
      .where(eq(prItemsForBidding.biddingId, biddingId))
      .orderBy(prItemsForBidding.id)

    return items
  } catch (error) {
    console.error('Failed to get PR items for bidding:', error)
    return []
  }
}

// 견적 시스템에서 협력업체 정보를 가져오는 함수
export async function getQuotationVendors(biddingId: number): Promise<QuotationVendor[]> {
  try {
    // bidding_companies 테이블을 메인으로 vendors, company_condition_responses를 조인하여 협력업체 정보 조회
    const vendorsData = await db
      .select({
        id: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        vendorId: biddingCompanies.companyId,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        contactPerson: biddingCompanies.contactPerson,
        contactEmail: biddingCompanies.contactEmail,
        contactPhone: biddingCompanies.contactPhone,
        quotationAmount: biddingCompanies.finalQuoteAmount,
        currency: sql<string>`'KRW'` as currency,
        submissionDate: biddingCompanies.finalQuoteSubmittedAt,
        isWinner: biddingCompanies.isWinner,
        awardRatio: sql<number>`CASE WHEN ${biddingCompanies.isWinner} THEN 100 ELSE 0 END`,
        status: sql<string>`CASE
          WHEN ${biddingCompanies.isWinner} THEN 'selected'
          WHEN ${biddingCompanies.finalQuoteSubmittedAt} IS NOT NULL THEN 'submitted'
          WHEN ${biddingCompanies.respondedAt} IS NOT NULL THEN 'submitted'
          ELSE 'pending'
        END`,
        // companyConditionResponses에서 입찰 조건들
        paymentTermsResponse: companyConditionResponses.paymentTermsResponse,
        taxConditionsResponse: companyConditionResponses.taxConditionsResponse,
        incotermsResponse: companyConditionResponses.incotermsResponse,
        proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate,
        proposedShippingPort: companyConditionResponses.proposedShippingPort,
        proposedDestinationPort: companyConditionResponses.proposedDestinationPort,
        priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse,
        sparePartResponse: companyConditionResponses.sparePartResponse,
        additionalProposals: companyConditionResponses.additionalProposals,
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .leftJoin(companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId))
      .where(eq(biddingCompanies.biddingId, biddingId))
      .orderBy(desc(biddingCompanies.finalQuoteAmount))

    return vendorsData.map(vendor => ({
      id: vendor.id,
      biddingId: vendor.biddingId,
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName || `Vendor ${vendor.vendorId}`,
      vendorCode: vendor.vendorCode || '',
      contactPerson: vendor.contactPerson || '',
      contactEmail: vendor.contactEmail || '',
      contactPhone: vendor.contactPhone || '',
      quotationAmount: Number(vendor.quotationAmount) || 0,
      currency: vendor.currency,
      submissionDate: vendor.submissionDate ? vendor.submissionDate.toISOString().split('T')[0] : '',
      isWinner: vendor.isWinner || false,
      awardRatio: vendor.awardRatio || 0,
      status: vendor.status as 'pending' | 'submitted' | 'selected' | 'rejected',
      // companyConditionResponses에서 입찰 조건들
      paymentTermsResponse: vendor.paymentTermsResponse || '',
      taxConditionsResponse: vendor.taxConditionsResponse || '',
      incotermsResponse: vendor.incotermsResponse || '',
      proposedContractDeliveryDate: vendor.proposedContractDeliveryDate ? (typeof vendor.proposedContractDeliveryDate === 'string' ? vendor.proposedContractDeliveryDate : vendor.proposedContractDeliveryDate.toISOString().split('T')[0]) : undefined,
      proposedShippingPort: vendor.proposedShippingPort || '',
      proposedDestinationPort: vendor.proposedDestinationPort || '',
      priceAdjustmentResponse: vendor.priceAdjustmentResponse || false,
      sparePartResponse: vendor.sparePartResponse || '',
      additionalProposals: vendor.additionalProposals || '',
      documents: [] // TODO: 문서 정보 조회 로직 추가
    }))
  } catch (error) {
    console.error('Failed to get quotation vendors:', error)
    return []
  }
}

// 내정가 수동 업데이트 (실제 저장)
export async function updateTargetPrice(
  biddingId: number,
  targetPrice: number,
  targetPriceCalculationCriteria: string,
  userId: string
) {
  try {
    await db
      .update(biddings)
      .set({
        targetPrice: targetPrice.toString(),
        targetPriceCalculationCriteria: targetPriceCalculationCriteria,
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '내정가가 성공적으로 업데이트되었습니다.' }
  } catch (error) {
    console.error('Failed to update target price:', error)
    return { success: false, error: '내정가 업데이트에 실패했습니다.' }
  }
}

// 협력업체 정보 저장 - biddingCompanies와 companyConditionResponses 테이블에 레코드 생성
export async function createQuotationVendor(input: any, userId: string) {
  try {
    const result = await db.transaction(async (tx) => {
      // 1. biddingCompanies에 레코드 생성
      const biddingCompanyResult = await tx.insert(biddingCompanies).values({
        biddingId: input.biddingId,
        companyId: input.vendorId,
        quotationAmount: input.quotationAmount,
        currency: input.currency,
        status: input.status,
        awardRatio: input.awardRatio,
        isWinner: false,
        contactPerson: input.contactPerson,
        contactEmail: input.contactEmail,
        contactPhone: input.contactPhone,
        submissionDate: new Date(),
        createdBy: userId,
        updatedBy: userId,
      }).returning({ id: biddingCompanies.id })

      if (biddingCompanyResult.length === 0) {
        throw new Error('협력업체 정보 저장에 실패했습니다.')
      }

      const biddingCompanyId = biddingCompanyResult[0].id

      // 2. companyConditionResponses에 입찰 조건 생성
      await tx.insert(companyConditionResponses).values({
        biddingCompanyId: biddingCompanyId,
        paymentTermsResponse: input.paymentTermsResponse || '',
        taxConditionsResponse: input.taxConditionsResponse || '',
        proposedContractDeliveryDate: input.proposedContractDeliveryDate ? new Date(input.proposedContractDeliveryDate) : null,
        priceAdjustmentResponse: input.priceAdjustmentResponse || false,
        incotermsResponse: input.incotermsResponse || '',
        proposedShippingPort: input.proposedShippingPort || '',
        proposedDestinationPort: input.proposedDestinationPort || '',
        sparePartResponse: input.sparePartResponse || '',
        additionalProposals: input.additionalProposals || '',
        isPreQuote: false,
        submittedAt: new Date(),
        createdAt: new Date(),
        updatedAt: new Date(),
      })

      return biddingCompanyId
    })

    revalidatePath(`/evcp/bid/[id]`)
    return {
      success: true,
      message: '협력업체 정보가 성공적으로 저장되었습니다.',
      data: { id: result }
    }
  } catch (error) {
    console.error('Failed to create quotation vendor:', error)
    return { success: false, error: '협력업체 정보 저장에 실패했습니다.' }
  }
}

// 협력업체 정보 업데이트
export async function updateQuotationVendor(id: number, input: any, userId: string) {
  try {
    const result = await db.transaction(async (tx) => {
      // 1. biddingCompanies 테이블 업데이트
      const updateData: any = {}
      if (input.quotationAmount !== undefined) updateData.finalQuoteAmount = input.quotationAmount
      if (input.contactPerson !== undefined) updateData.contactPerson = input.contactPerson
      if (input.contactEmail !== undefined) updateData.contactEmail = input.contactEmail
      if (input.contactPhone !== undefined) updateData.contactPhone = input.contactPhone
      if (input.awardRatio !== undefined) updateData.awardRatio = input.awardRatio
      if (input.status !== undefined) updateData.status = input.status
      updateData.updatedBy = userId
      updateData.updatedAt = new Date()

      if (Object.keys(updateData).length > 0) {
        await tx.update(biddingCompanies)
          .set(updateData)
          .where(eq(biddingCompanies.id, id))
      }

      // 2. companyConditionResponses 테이블 업데이트 (입찰 조건들)
      if (input.paymentTermsResponse !== undefined ||
          input.taxConditionsResponse !== undefined ||
          input.incotermsResponse !== undefined ||
          input.proposedContractDeliveryDate !== undefined ||
          input.proposedShippingPort !== undefined ||
          input.proposedDestinationPort !== undefined ||
          input.priceAdjustmentResponse !== undefined ||
          input.sparePartResponse !== undefined ||
          input.additionalProposals !== undefined) {

        const conditionsUpdateData: any = {}
        if (input.paymentTermsResponse !== undefined) conditionsUpdateData.paymentTermsResponse = input.paymentTermsResponse
        if (input.taxConditionsResponse !== undefined) conditionsUpdateData.taxConditionsResponse = input.taxConditionsResponse
        if (input.incotermsResponse !== undefined) conditionsUpdateData.incotermsResponse = input.incotermsResponse
        if (input.proposedContractDeliveryDate !== undefined) conditionsUpdateData.proposedContractDeliveryDate = input.proposedContractDeliveryDate ? new Date(input.proposedContractDeliveryDate) : null
        if (input.proposedShippingPort !== undefined) conditionsUpdateData.proposedShippingPort = input.proposedShippingPort
        if (input.proposedDestinationPort !== undefined) conditionsUpdateData.proposedDestinationPort = input.proposedDestinationPort
        if (input.priceAdjustmentResponse !== undefined) conditionsUpdateData.priceAdjustmentResponse = input.priceAdjustmentResponse
        if (input.sparePartResponse !== undefined) conditionsUpdateData.sparePartResponse = input.sparePartResponse
        if (input.additionalProposals !== undefined) conditionsUpdateData.additionalProposals = input.additionalProposals
        conditionsUpdateData.updatedAt = new Date()

        await tx.update(companyConditionResponses)
          .set(conditionsUpdateData)
          .where(eq(companyConditionResponses.biddingCompanyId, id))
      }

      return true
    })

    revalidatePath(`/evcp/bid/[id]`)
    return {
      success: true,
      message: '협력업체 정보가 성공적으로 업데이트되었습니다.',
    }
  } catch (error) {
    console.error('Failed to update quotation vendor:', error)
    return { success: false, error: '협력업체 정보 업데이트에 실패했습니다.' }
  }
}

// 협력업체 정보 삭제
export async function deleteQuotationVendor(id: number) {
  try {
    // TODO: 실제로는 견적 시스템의 테이블에서 삭제
    console.log(`[TODO] 견적 시스템에서 협력업체 정보 ${id} 삭제 예정`)

    // 임시로 성공 응답
    return { success: true, message: '협력업체 정보가 성공적으로 삭제되었습니다.' }
  } catch (error) {
    console.error('Failed to delete quotation vendor:', error)
    return { success: false, error: '협력업체 정보 삭제에 실패했습니다.' }
  }
}

// 낙찰 처리
export async function selectWinner(biddingId: number, vendorId: number, awardRatio: number, userId: string) {
  try {
    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 기존 낙찰자 초기화
      await tx
        .update(biddingCompanies)
        .set({
          isWinner: false,
          updatedAt: new Date()
        })
        .where(eq(biddingCompanies.biddingId, biddingId))

      // 새로운 낙찰자 설정
      const biddingCompany = await tx
        .select()
        .from(biddingCompanies)
        .where(and(
          eq(biddingCompanies.biddingId, biddingId),
          eq(biddingCompanies.companyId, vendorId)
        ))
        .limit(1)

      if (biddingCompany.length > 0) {
        await tx
          .update(biddingCompanies)
          .set({
            isWinner: true,
            updatedAt: new Date()
          })
          .where(eq(biddingCompanies.id, biddingCompany[0].id))
      }

      // biddings 테이블의 상태 업데이트
      await tx
        .update(biddings)
        .set({
          status: 'vendor_selected',
          finalBidPrice: undefined, // TODO: 낙찰가 설정 로직 추가
          updatedAt: new Date()
        })
        .where(eq(biddings.id, biddingId))
    })

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '낙찰 처리가 완료되었습니다.' }
  } catch (error) {
    console.error('Failed to select winner:', error)
    return { success: false, error: '낙찰 처리에 실패했습니다.' }
  }
}

// 유찰 처리
export async function markAsDisposal(biddingId: number, userId: string) {
  try {
    await db
      .update(biddings)
      .set({
        status: 'bidding_disposal',
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '유찰 처리가 완료되었습니다.' }
  } catch (error) {
    console.error('Failed to mark as disposal:', error)
    return { success: false, error: '유찰 처리에 실패했습니다.' }
  }
}

// 입찰 등록 (상태 변경)
export async function registerBidding(biddingId: number, userId: string) {
  try {
    await db
      .update(biddings)
      .set({
        status: 'bidding_opened',
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))
      //todo 입찰 등록하면 bidding_companies  invitationStatus를 sent로 변경!
      await db
        .update(biddingCompanies)
        .set({
          invitationStatus: 'sent',
          updatedAt: new Date()
        })
        .where(eq(biddingCompanies.biddingId, biddingId))

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '입찰이 성공적으로 등록되었습니다.' }
  } catch (error) {
    console.error('Failed to register bidding:', error)
    return { success: false, error: '입찰 등록에 실패했습니다.' }
  }
}

// 재입찰 생성
export async function createRebidding(originalBiddingId: number, userId: string) {
  try {
    // 원본 입찰 정보 조회
    const originalBidding = await db
      .select()
      .from(biddings)
      .where(eq(biddings.id, originalBiddingId))
      .limit(1)

    if (originalBidding.length === 0) {
      return { success: false, error: '원본 입찰을 찾을 수 없습니다.' }
    }

    const original = originalBidding[0]

    // 재입찰용 데이터 준비
    const rebiddingData = {
      ...original,
      id: undefined,
      biddingNumber: `${original.biddingNumber}-R${(original.revision || 0) + 1}`,
      revision: (original.revision || 0) + 1,
      status: 'bidding_generated' as const,
      createdAt: new Date(),
      updatedAt: new Date()
    }

    // 새로운 입찰 생성
    const [newBidding] = await db
      .insert(biddings)
      .values(rebiddingData)
      .returning({ id: biddings.id, biddingNumber: biddings.biddingNumber })

    revalidatePath('/evcp/bid')
    revalidatePath(`/evcp/bid/${newBidding.id}`)

    return {
      success: true,
      message: '재입찰이 성공적으로 생성되었습니다.',
      data: newBidding
    }
  } catch (error) {
    console.error('Failed to create rebidding:', error)
    return { success: false, error: '재입찰 생성에 실패했습니다.' }
  }
}

// 업체 선정 사유 업데이트
export async function updateVendorSelectionReason(biddingId: number, selectedCompanyId: number, selectionReason: string, userId: string) {
  try {
    // vendorSelectionResults 테이블에 삽입 또는 업데이트
    await db
      .insert(vendorSelectionResults)
      .values({
        biddingId,
        selectedCompanyId,
        selectionReason,
        selectedBy: userId,
        selectedAt: new Date(),
        createdAt: new Date(),
        updatedAt: new Date()
      })
      .onConflictDoUpdate({
        target: [vendorSelectionResults.biddingId],
        set: {
          selectedCompanyId,
          selectionReason,
          selectedBy: userId,
          selectedAt: new Date(),
          updatedAt: new Date()
        }
      })

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '업체 선정 사유가 성공적으로 업데이트되었습니다.' }
  } catch (error) {
    console.error('Failed to update vendor selection reason:', error)
    return { success: false, error: '업체 선정 사유 업데이트에 실패했습니다.' }
  }
}

// PR 품목 정보 업데이트
export async function updatePrItem(prItemId: number, input: Partial<typeof prItemsForBidding.$inferSelect>, userId: string) {
  try {
    await db
      .update(prItemsForBidding)
      .set({
        ...input,
        updatedAt: new Date()
      })
      .where(eq(prItemsForBidding.id, prItemId))

    revalidatePath(`/evcp/bid/${input.biddingId}`)
    return { success: true, message: '품목 정보가 성공적으로 업데이트되었습니다.' }
  } catch (error) {
    console.error('Failed to update PR item:', error)
    return { success: false, error: '품목 정보 업데이트에 실패했습니다.' }
  }
}

// 입찰에 협력업체 추가
export async function addVendorToBidding(biddingId: number, companyId: number, userId: string) {
  try {
    // 이미 추가된 업체인지 확인
    const existing = await db
      .select()
      .from(biddingCompanies)
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.companyId, companyId)
      ))
      .limit(1)

    if (existing.length > 0) {
      return { success: false, error: '이미 추가된 협력업체입니다.' }
    }

    // 새로운 협력업체 추가
    await db
      .insert(biddingCompanies)
      .values({
        biddingId,
        companyId,
        invitationStatus: 'pending',
        invitedAt: new Date(),
        createdAt: new Date(),
        updatedAt: new Date()
      })

    revalidatePath(`/evcp/bid/${biddingId}`)
    return { success: true, message: '협력업체가 성공적으로 추가되었습니다.' }
  } catch (error) {
    console.error('Failed to add vendor to bidding:', error)
    return { success: false, error: '협력업체 추가에 실패했습니다.' }
  }
}

// =================================================
// 협력업체 페이지용 함수들 (Partners)
// =================================================

// 협력업체용 입찰 목록 조회 (bidding_companies 기준)
export interface PartnersBiddingListItem {
  // bidding_companies 정보
  id: number
  biddingCompanyId: number
  invitationStatus: string
  respondedAt: string | null
  finalQuoteAmount: number | null
  finalQuoteSubmittedAt: string | null
  isWinner: boolean | null
  isAttendingMeeting: boolean | null
  notes: string | null
  createdAt: Date
  updatedAt: Date
  // updatedBy: string | null

  // biddings 정보
  biddingId: number
  biddingNumber: string
  revision: number
  projectName: string
  itemName: string
  title: string
  contractType: string
  biddingType: string
  contractPeriod: string | null
  submissionStartDate: Date | null
  submissionEndDate: Date | null
  status: string
  managerName: string | null
  managerEmail: string | null
  managerPhone: string | null
  currency: string
  budget: number | null

  // 계산된 필드
  responseDeadline: Date | null // 참여회신 마감일 (submissionStartDate 전 3일)
  submissionDate: Date | null // 입찰제출일 (submissionEndDate)
}

export async function getBiddingListForPartners(companyId: number): Promise<PartnersBiddingListItem[]> {
  try {
    const result = await db
      .select({
        // bidding_companies 정보
        id: biddingCompanies.id,
        biddingCompanyId: biddingCompanies.id, // 동일
        invitationStatus: biddingCompanies.invitationStatus,
        respondedAt: biddingCompanies.respondedAt,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt,
        isWinner: biddingCompanies.isWinner,
        isAttendingMeeting: biddingCompanies.isAttendingMeeting,
        notes: biddingCompanies.notes,
        createdAt: biddingCompanies.createdAt,
        updatedAt: biddingCompanies.updatedAt,
        // updatedBy: biddingCompanies.updatedBy, // 이 필드가 존재하지 않음

        // biddings 정보
        biddingId: biddings.id,
        biddingNumber: biddings.biddingNumber,
        revision: biddings.revision,
        projectName: biddings.projectName,
        itemName: biddings.itemName,
        title: biddings.title,
        contractType: biddings.contractType,
        biddingType: biddings.biddingType,
        contractPeriod: biddings.contractPeriod,
        submissionStartDate: biddings.submissionStartDate,
        submissionEndDate: biddings.submissionEndDate,
        status: biddings.status,
        managerName: biddings.managerName,
        managerEmail: biddings.managerEmail,
        managerPhone: biddings.managerPhone,
        currency: biddings.currency,
        budget: biddings.budget,
      })
      .from(biddingCompanies)
      .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
      .where(and(
        eq(biddingCompanies.companyId, companyId),
        ne(biddingCompanies.invitationStatus, 'pending') // 초대 대기 상태 제외
      ))
      .orderBy(desc(biddingCompanies.createdAt))
    
    console.log(result, "result")

    // 계산된 필드 추가
    const resultWithCalculatedFields = result.map(item => ({
      ...item,
      respondedAt: item.respondedAt ? item.respondedAt.toISOString() : null,
      finalQuoteAmount: item.finalQuoteAmount ? Number(item.finalQuoteAmount) : null, // string을 number로 변환
      responseDeadline: item.submissionStartDate
        ? new Date(item.submissionStartDate.getTime() - 3 * 24 * 60 * 60 * 1000) // 3일 전
        : null,
      submissionDate: item.submissionEndDate,
    }))

    return resultWithCalculatedFields
  } catch (error) {
    console.error('Failed to get bidding list for partners:', error)
    return []
  }
}

// 협력업체용 입찰 상세 정보 조회
export async function getBiddingDetailsForPartners(biddingId: number, companyId: number) {
  try {
    const result = await db
      .select({
        // 입찰 기본 정보
        id: biddings.id,
        biddingNumber: biddings.biddingNumber,
        revision: biddings.revision,
        projectName: biddings.projectName,
        itemName: biddings.itemName,
        title: biddings.title,
        description: biddings.description,
        content: biddings.content,

        // 계약 정보
        contractType: biddings.contractType,
        biddingType: biddings.biddingType,
        awardCount: biddings.awardCount,
        contractPeriod: biddings.contractPeriod,

        // 일정 정보
        preQuoteDate: biddings.preQuoteDate,
        biddingRegistrationDate: biddings.biddingRegistrationDate,
        submissionStartDate: biddings.submissionStartDate,
        submissionEndDate: biddings.submissionEndDate,
        evaluationDate: biddings.evaluationDate,

        // 가격 정보
        currency: biddings.currency,
        budget: biddings.budget,
        targetPrice: biddings.targetPrice,

        // 상태 및 담당자
        status: biddings.status,
        managerName: biddings.managerName,
        managerEmail: biddings.managerEmail,
        managerPhone: biddings.managerPhone,

        // 협력업체 특정 정보
        biddingCompanyId: biddingCompanies.id,
        invitationStatus: biddingCompanies.invitationStatus,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt,
        isWinner: biddingCompanies.isWinner,
        isAttendingMeeting: biddingCompanies.isAttendingMeeting,

        // 응답한 조건들 (company_condition_responses) - 제시된 조건과 응답 모두 여기서 관리
        paymentTermsResponse: companyConditionResponses.paymentTermsResponse,
        taxConditionsResponse: companyConditionResponses.taxConditionsResponse,
        incotermsResponse: companyConditionResponses.incotermsResponse,
        proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate,
        proposedShippingPort: companyConditionResponses.proposedShippingPort,
        proposedDestinationPort: companyConditionResponses.proposedDestinationPort,
        priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse,
        sparePartResponse: companyConditionResponses.sparePartResponse,
        additionalProposals: companyConditionResponses.additionalProposals,
        responseSubmittedAt: companyConditionResponses.submittedAt,
      })
      .from(biddings)
      .innerJoin(biddingCompanies, eq(biddings.id, biddingCompanies.biddingId))
      .leftJoin(companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId))
      .where(and(
        eq(biddings.id, biddingId),
        eq(biddingCompanies.companyId, companyId)
      ))
      .limit(1)

    return result[0] || null
  } catch (error) {
    console.error('Failed to get bidding details for partners:', error)
    return null
  }
}

// 협력업체 응찰 제출
export async function submitPartnerResponse(
  biddingCompanyId: number,
  response: {
    paymentTermsResponse?: string
    taxConditionsResponse?: string
    incotermsResponse?: string
    proposedContractDeliveryDate?: string
    proposedShippingPort?: string
    proposedDestinationPort?: string
    priceAdjustmentResponse?: boolean
    sparePartResponse?: string
    additionalProposals?: string
    finalQuoteAmount?: number
  },
  userId: string
) {
  try {
    const result = await db.transaction(async (tx) => {
      // 1. company_condition_responses 테이블에 응답 저장/업데이트
      const responseData = {
        paymentTermsResponse: response.paymentTermsResponse,
        taxConditionsResponse: response.taxConditionsResponse,
        incotermsResponse: response.incotermsResponse,
        proposedContractDeliveryDate: response.proposedContractDeliveryDate ? response.proposedContractDeliveryDate : null, // Date 대신 string 사용
        proposedShippingPort: response.proposedShippingPort,
        proposedDestinationPort: response.proposedDestinationPort,
        priceAdjustmentResponse: response.priceAdjustmentResponse,
        sparePartResponse: response.sparePartResponse,
        additionalProposals: response.additionalProposals,
        submittedAt: new Date(),
        updatedAt: new Date(),
      }

      // 기존 응답이 있는지 확인
      const existingResponse = await tx
        .select()
        .from(companyConditionResponses)
        .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId))
        .limit(1)

      if (existingResponse.length > 0) {
        // 업데이트
        await tx
          .update(companyConditionResponses)
          .set(responseData)
          .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId))
      } else {
        // 새로 생성
        await tx
          .insert(companyConditionResponses)
          .values({
            biddingCompanyId,
            ...responseData,
          })
      }

      // 2. biddingCompanies 테이블에 견적 금액과 상태 업데이트
      const companyUpdateData: any = {
        respondedAt: new Date(),
        updatedAt: new Date(),
        // updatedBy: userId, // 이 필드가 존재하지 않음
      }

      if (response.finalQuoteAmount !== undefined) {
        companyUpdateData.finalQuoteAmount = response.finalQuoteAmount
        companyUpdateData.finalQuoteSubmittedAt = new Date()
        companyUpdateData.invitationStatus = 'submitted'
      }

      await tx
        .update(biddingCompanies)
        .set(companyUpdateData)
        .where(eq(biddingCompanies.id, biddingCompanyId))

      return true
    })

    revalidatePath('/partners/bid/[id]')
    return {
      success: true,
      message: '응찰이 성공적으로 제출되었습니다.',
    }
  } catch (error) {
    console.error('Failed to submit partner response:', error)
    return { success: false, error: '응찰 제출에 실패했습니다.' }
  }
}

// 사양설명회 정보 조회 (협력업체용)
export async function getSpecificationMeetingForPartners(biddingId: number) {
  try {
    // bidding_documents에서 사양설명회 관련 문서 조회
    const documents = await db
      .select({
        id: biddingDocuments.id,
        fileName: biddingDocuments.fileName,
        originalFileName: biddingDocuments.originalFileName,
        filePath: biddingDocuments.filePath,
        fileSize: biddingDocuments.fileSize,
        title: biddingDocuments.title,
      })
      .from(biddingDocuments)
      .where(and(
        eq(biddingDocuments.biddingId, biddingId),
        eq(biddingDocuments.documentType, 'specification_meeting')
      ))

    // biddings 테이블에서 사양설명회 기본 정보 조회
    const bidding = await db
      .select({
        id: biddings.id,
        title: biddings.title,
        biddingNumber: biddings.biddingNumber,
        preQuoteDate: biddings.preQuoteDate,
        biddingRegistrationDate: biddings.biddingRegistrationDate,
        managerName: biddings.managerName,
        managerEmail: biddings.managerEmail,
        managerPhone: biddings.managerPhone,
      })
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

    return {
      success: true,
      data: {
        ...bidding[0],
        documents,
        meetingDate: bidding[0].preQuoteDate ? bidding[0].preQuoteDate.toISOString().split('T')[0] : null,
        contactPerson: bidding[0].managerName,
        contactEmail: bidding[0].managerEmail,
        contactPhone: bidding[0].managerPhone,
      }
    }
  } catch (error) {
    console.error('Failed to get specification meeting info:', error)
    return { success: false, error: '사양설명회 정보 조회에 실패했습니다.' }
  }
}

// 사양설명회 참석 여부 업데이트 (상세 정보 포함)
export async function updatePartnerAttendance(
  biddingCompanyId: number,
  attendanceData: {
    isAttending: boolean
    attendeeCount?: number
    representativeName?: string
    representativePhone?: string
  },
  userId: string
) {
  try {
    const result = await db.transaction(async (tx) => {
      // biddingCompanies 테이블 업데이트 (참석여부만 저장)
      await tx
        .update(biddingCompanies)
        .set({
          isAttendingMeeting: attendanceData.isAttending,
          updatedAt: new Date(),
        })
        .where(eq(biddingCompanies.id, biddingCompanyId))

      // 참석하는 경우, 사양설명회 담당자에게 이메일 발송을 위한 정보 반환
      if (attendanceData.isAttending) {
        const biddingInfo = await tx
          .select({
            biddingId: biddingCompanies.biddingId,
            companyId: biddingCompanies.companyId,
            managerEmail: biddings.managerEmail,
            managerName: biddings.managerName,
            title: biddings.title,
            biddingNumber: biddings.biddingNumber,
          })
          .from(biddingCompanies)
          .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
          .where(eq(biddingCompanies.id, biddingCompanyId))
          .limit(1)

        if (biddingInfo.length > 0) {
          // 협력업체 정보 조회
          const companyInfo = await tx
            .select({
              vendorName: vendors.vendorName,
            })
            .from(vendors)
            .where(eq(vendors.id, biddingInfo[0].companyId))
            .limit(1)

          const companyName = companyInfo.length > 0 ? companyInfo[0].vendorName : '알 수 없음'

          // 메일 발송 (템플릿 사용)
          try {
            const { sendEmail } = await import('@/lib/mail/sendEmail')
            
            await sendEmail({
              to: biddingInfo[0].managerEmail,
              template: 'specification-meeting-attendance',
              context: {
                biddingNumber: biddingInfo[0].biddingNumber,
                title: biddingInfo[0].title,
                companyName: companyName,
                attendeeCount: attendanceData.attendeeCount,
                representativeName: attendanceData.representativeName,
                representativePhone: attendanceData.representativePhone,
                managerName: biddingInfo[0].managerName,
                managerEmail: biddingInfo[0].managerEmail,
                currentYear: new Date().getFullYear(),
                language: 'ko'
              }
            })
            
            console.log(`사양설명회 참석 알림 메일 발송 완료: ${biddingInfo[0].managerEmail}`)
          } catch (emailError) {
            console.error('메일 발송 실패:', emailError)
            // 메일 발송 실패해도 참석 여부 업데이트는 성공으로 처리
          }
          
          return {
            ...biddingInfo[0],
            companyName,
            attendeeCount: attendanceData.attendeeCount,
            representativeName: attendanceData.representativeName,
            representativePhone: attendanceData.representativePhone
          }
        }
      }

      return null
    })

    revalidatePath('/partners/bid/[id]')
    return {
      success: true,
      message: `사양설명회 ${attendanceData.isAttending ? '참석' : '불참'}으로 설정되었습니다.`,
      data: result
    }
  } catch (error) {
    console.error('Failed to update partner attendance:', error)
    return { success: false, error: '참석 여부 업데이트에 실패했습니다.' }
  }
}