'use server'

import db from '@/db/db'
import { biddings, prItemsForBidding, biddingDocuments, biddingCompanies, vendors, companyPrItemBids, companyConditionResponses, vendorSelectionResults, priceAdjustmentForms, users, vendorContacts } from '@/db/schema'
import { specificationMeetings, biddingCompaniesContacts } from '@/db/schema/bidding'
import { eq, and, sql, desc, ne, asc } from 'drizzle-orm'
import { revalidatePath, revalidateTag } from 'next/cache'
import { unstable_cache } from "@/lib/unstable-cache";
import { sendEmail } from '@/lib/mail/sendEmail'
import { saveFile } from '@/lib/file-stroage'
import { sendBiddingNoticeSms } from '@/lib/users/auth/passwordUtil'
import { debugLog, debugError, debugSuccess } from '@/lib/debug-utils'

// 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 interface BiddingDetailData {
  bidding: Awaited<ReturnType<typeof getBiddingById>>
  quotationDetails: QuotationDetails | null
  quotationVendors: QuotationVendor[]
  prItems: Awaited<ReturnType<typeof getPRItemsForBidding>>
}

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

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

      return {
        bidding,
        quotationDetails,
        quotationVendors,
        prItems
      }
    },
    [`bidding-detail-data-${biddingId}`],
    {
      tags: [`bidding-${biddingId}`, 'bidding-detail', 'quotation-vendors', 'pr-items']
    }
  )()
}
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
  vendorEmail?: string // 벤더의 기본 이메일
  contactPerson: string
  contactEmail: string
  contactPhone: string
  quotationAmount: number // 견적금액
  currency: string
  submissionDate: string // 제출일
  isWinner: boolean | null // 낙찰여부 (null: 미정, true: 낙찰, false: 탈락)
  awardRatio: number | null // 발주비율
  isBiddingParticipated: boolean | null // 본입찰 참여여부
  invitationStatus: 'pending' | 'pre_quote_sent' | 'pre_quote_accepted' | 'pre_quote_declined' | 'pre_quote_submitted' | 'bidding_sent' | 'bidding_accepted' | 'bidding_declined' | 'bidding_cancelled' | 'bidding_submitted'
  documents: Array<{
    id: number
    fileName: string
    originalFileName: string
    filePath: string
    uploadedAt: string
  }>
}

// 견적 시스템에서 내정가 및 관련 정보를 가져오는 함수 (캐시 적용)
export async function getQuotationDetails(biddingId: number): Promise<QuotationDetails | null> {
  return unstable_cache(
    async () => {
  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
  }
    },
    [`quotation-details-${biddingId}`],
    {
      tags: [`bidding-${biddingId}`, 'quotation-details']
    }
  )()
}

// 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(
        and(
          eq(biddingCompanies.biddingId, biddingId),
          eq(biddingCompanies.isPreQuoteSelected, true)
        )
      )
      .orderBy(desc(biddingCompanies.finalQuoteAmount))
     console.log(companies)

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

// 입찰 접수 화면용: 모든 초대된 협력사 조회 (필터링 없음, contact 정보 포함)
export async function getAllBiddingCompanies(biddingId: number) {
  try {
    // 1. 기본 협력사 정보 조회
    const companies = await db
      .select({
        id: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        companyId: biddingCompanies.companyId,
        vendorName: vendors.vendorName,
        vendorCode: 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,
        createdAt: biddingCompanies.createdAt,
        updatedAt: biddingCompanies.updatedAt
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(eq(biddingCompanies.biddingId, biddingId))
      .orderBy(biddingCompanies.invitedAt)

    // 2. 각 협력사의 첫 번째 contact 정보 조회
    const companiesWithContacts = await Promise.all(
      companies.map(async (company) => {
        if (!company.companyId) {
          return {
            ...company,
            contactPerson: null,
            contactEmail: null,
            contactPhone: null
          }
        }

        // biddingCompaniesContacts에서 첫 번째 contact 조회
        const [firstContact] = await db
          .select({
            contactName: biddingCompaniesContacts.contactName,
            contactEmail: biddingCompaniesContacts.contactEmail,
            contactNumber: biddingCompaniesContacts.contactNumber,
          })
          .from(biddingCompaniesContacts)
          .where(
            and(
              eq(biddingCompaniesContacts.biddingId, biddingId),
              eq(biddingCompaniesContacts.vendorId, company.companyId)
            )
          )
          .orderBy(asc(biddingCompaniesContacts.id))
          .limit(1)

        return {
          ...company,
          contactPerson: firstContact?.contactName || null,
          contactEmail: firstContact?.contactEmail || null,
          contactPhone: firstContact?.contactNumber || null
        }
      })
    )

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

// prItemsForBidding 테이블에서 품목 정보 조회 (캐시 미적용, always fresh)
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[]> {
  return unstable_cache(
    async () => {
  try {
    // bidding_companies 테이블을 메인으로 vendors를 조인하여 협력업체 정보 조회
    const vendorsData = await db
      .select({
        id: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        vendorId: biddingCompanies.companyId,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        vendorEmail: vendors.email, // 벤더의 기본 이메일
        contactPerson: biddingCompanies.contactPerson,
        contactEmail: biddingCompanies.contactEmail,
        contactPhone: biddingCompanies.contactPhone,
        quotationAmount: biddingCompanies.finalQuoteAmount,
        currency: sql<string>`'KRW'`,
        submissionDate: biddingCompanies.finalQuoteSubmittedAt,
        isWinner: biddingCompanies.isWinner,
        // awardRatio: sql<number>`CASE WHEN ${biddingCompanies.isWinner} THEN 100 ELSE 0 END`,
        awardRatio: biddingCompanies.awardRatio,
        isBiddingParticipated: biddingCompanies.isBiddingParticipated,
        invitationStatus: biddingCompanies.invitationStatus,
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isPreQuoteSelected, true) // 본입찰 선정된 업체만 조회
      ))
      .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 || '',
      vendorEmail: vendor.vendorEmail || '', // 벤더의 기본 이메일
      contactPerson: vendor.contactPerson || '',
      contactEmail: vendor.contactEmail || '',
      contactPhone: vendor.contactPhone || '',
      quotationAmount: Number(vendor.quotationAmount) || 0,
      currency: vendor.currency,
      submissionDate: vendor.submissionDate ? (vendor.submissionDate instanceof Date ? vendor.submissionDate.toISOString().split('T')[0] : String(vendor.submissionDate).split('T')[0]) : '',
      isWinner: vendor.isWinner,
      awardRatio: vendor.awardRatio ? Number(vendor.awardRatio) : null,
      isBiddingParticipated: vendor.isBiddingParticipated,
      invitationStatus: vendor.invitationStatus,
      documents: [], // 빈 배열로 초기화
    }))
  } catch (error) {
    console.error('Failed to get quotation vendors:', error)
    return []
  }
    },
    [`quotation-vendors-${biddingId}`],
    {
      tags: [`bidding-${biddingId}`, 'quotation-vendors']
    }
  )()
}

// 사전견적 데이터 조회 (내정가 산정용)
export async function getPreQuoteData(biddingId: number) {
  try {
    const preQuotes = await db
      .select({
        id: biddingCompanies.id,
        companyId: biddingCompanies.companyId,
        vendorName: vendors.vendorName,
        preQuoteAmount: biddingCompanies.preQuoteAmount,
        submittedAt: biddingCompanies.preQuoteSubmittedAt,
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        sql`${biddingCompanies.preQuoteAmount} IS NOT NULL AND ${biddingCompanies.preQuoteAmount} > 0`
      ))
      .orderBy(biddingCompanies.preQuoteAmount)

    if (preQuotes.length === 0) {
      return {
        quotes: [],
        lowestQuote: null,
        highestQuote: null,
        averageQuote: null,
        quotationCount: 0
      }
    }

    const amounts = preQuotes
      .map(q => Number(q.preQuoteAmount))
      .filter(amount => !isNaN(amount) && amount > 0)
    
    console.log('Pre-quote amounts:', amounts)
    
    if (amounts.length === 0) {
      return {
        quotes: preQuotes,
        lowestQuote: null,
        highestQuote: null,
        averageQuote: null,
        quotationCount: 0
      }
    }
    
    const lowestQuote = Math.min(...amounts)
    const highestQuote = Math.max(...amounts)
    const averageQuote = amounts.reduce((sum, amount) => sum + amount, 0) / amounts.length

    console.log('Calculated quotes:', { lowestQuote, highestQuote, averageQuote })

    return {
      quotes: preQuotes,
      lowestQuote,
      highestQuote,
      averageQuote,
      quotationCount: amounts.length
    }
  } catch (error) {
    console.error('Failed to get pre-quote data:', error)
    return {
      quotes: [],
      lowestQuote: null,
      highestQuote: null,
      averageQuote: null,
      quotationCount: 0
    }
  }
}

// 입찰유형별 내정가 자동 산정 로직
export async function calculateTargetPrice(
  biddingType: string,
  budget: number | null,
  lowestQuote: number | null,
  highestQuote: number | null
): Promise<{ targetPrice: number; criteria: string }> {
  const results: Array<{ price: number; description: string }> = []
  
  // 입찰유형별 로직
  switch (biddingType) {
    case 'equipment':
    case 'construction':
    case 'service':
    case 'lease':
    case 'steel_stock':
    case 'piping': {
      // 예산가 85%, 최저견적가 85% 중 최저가 (직전실적가 95% 제외)
      if (budget) {
        results.push({ price: budget * 0.85, description: '예산가 85%' })
      }
      if (lowestQuote) {
        results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' })
      }
      break
    }
    case 'transport': {
      // 예산가 85%, 최저견적가 85% 중 최저가 (직전실적가 95% 제외)
      // 만약 예산이 없을 경우 최저견적가의 70%
      if (budget) {
        results.push({ price: budget * 0.85, description: '예산가 85%' })
      }
      if (lowestQuote) {
        if (budget) {
          results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' })
        } else {
          results.push({ price: lowestQuote * 0.70, description: '최저견적가 70% (예산 없음)' })
        }
      }
      break
    }
    case 'waste': {
      // 예산가 85%, 최저견적가 70% 중 최저가 (직전실적가 95% 제외)
      if (budget) {
        results.push({ price: budget * 0.85, description: '예산가 85%' })
      }
      if (lowestQuote) {
        results.push({ price: lowestQuote * 0.70, description: '최저견적가 70%' })
      }
      break
    }
    case 'sale': {
      // 최고견적가 130% (직전실적가 105% 제외)
      if (highestQuote) {
        results.push({ price: highestQuote * 1.30, description: '최고견적가 130%' })
      }
      break
    }
    default: {
      // 기본: 최저견적가 85%
      if (lowestQuote) {
        results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' })
      }
      break
    }
  }

  if (results.length === 0) {
    return {
      targetPrice: 0,
      criteria: '산정 가능한 데이터가 없습니다.'
    }
  }

  // 매각의 경우 최고가, 나머지는 최저가
  const prices = results.map(r => r.price).filter(p => !isNaN(p) && isFinite(p))
  
  if (prices.length === 0) {
    return {
      targetPrice: 0,
      criteria: '유효한 가격 데이터가 없습니다.'
    }
  }
  
  const targetPrice = biddingType === 'sale' 
    ? Math.max(...prices)
    : Math.min(...prices)

  if (!isFinite(targetPrice) || isNaN(targetPrice)) {
    return {
      targetPrice: 0,
      criteria: '내정가 계산 오류가 발생했습니다.'
    }
  }

  const selectedResult = results.find(r => r.price === targetPrice)
  const criteria = `입찰유형: ${biddingType} - ${selectedResult?.description || ''}로 산정`

  return {
    targetPrice: Math.round(targetPrice),
    criteria
  }
}

// 내정가 자동 산정 및 업데이트
export async function calculateAndUpdateTargetPrice(
  biddingId: number
) {
  try {

    // 입찰 정보 조회
    const bidding = await getBiddingById(biddingId)
    if (!bidding) {
      return { success: false, error: '입찰 정보를 찾을 수 없습니다.' }
    }

    // 사전견적 데이터 조회
    const preQuoteData = await getPreQuoteData(biddingId)
    
    if (preQuoteData.quotationCount === 0) {
      return { success: false, error: '사전견적 데이터가 없습니다.' }
    }

    // 내정가 산정
    console.log('Bidding data for calculation:', {
      biddingType: bidding.biddingType,
      budget: bidding.budget,
      preQuoteData
    })
    
    const { targetPrice, criteria } = await calculateTargetPrice(
      bidding.biddingType || '',
      bidding.budget ? Number(bidding.budget) : null,
      preQuoteData.lowestQuote,
      preQuoteData.highestQuote
    )

    console.log('Calculated target price:', { targetPrice, criteria })

    if (!targetPrice || targetPrice <= 0 || isNaN(targetPrice)) {
      return { success: false, error: `내정가 산정에 실패했습니다. (계산된 값: ${targetPrice})` }
    }

    // 내정가 업데이트
    const updateResult = await updateTargetPrice(biddingId, targetPrice, criteria)
    
    if (updateResult.success) {
      // // 내정가 산정 후 입찰 상태를 set_target_price로 변경 (received_quotation 상태에서만)
      // await db
      //   .update(biddings)
      //   .set({
      //     status: 'set_target_price',
      //     updatedAt: new Date()
      //   })
      //   .where(and(
      //     eq(biddings.id, biddingId)
      //   ))
      
      // 캐시 무효화
      revalidateTag(`bidding-${biddingId}`)
      
      return {
        success: true,
        message: '내정가가 자동으로 산정되었습니다.',
        data: {
          targetPrice,
          criteria,
          preQuoteData
        }
      }
    } else {
      return updateResult
    }
  } catch (error) {
    console.error('Failed to calculate and update target price:', error)
    return { success: false, error: '내정가 자동 산정에 실패했습니다.' }
  }
}

// 내정가 수동 업데이트 (실제 저장)
export async function updateTargetPrice(
  biddingId: number,
  targetPrice: number,
  targetPriceCalculationCriteria: string,
) {
  try {
    // 입력값 검증
    if (!targetPrice || targetPrice <= 0 || isNaN(targetPrice)) {
      return { success: false, error: `유효하지 않은 내정가입니다: ${targetPrice}` }
    }
    
    console.log('Updating target price:', { biddingId, targetPrice, targetPriceCalculationCriteria })
    
    await db
      .update(biddings)
      .set({
        targetPrice: Math.round(targetPrice).toString(),
        targetPriceCalculationCriteria: targetPriceCalculationCriteria,
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))

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

// 본입찰용 업체 수정 (간소화 버전 - 발주비율만 UI에서 수정 가능, 견적금액/통화는 기존값 유지)
export async function updateBiddingDetailVendor(
  biddingCompanyId: number,
  quotationAmount: number, // 기존값 유지용
  currency: string, // 기존값 유지용
  awardRatio: number, // UI에서 수정 가능
) {
  try {
    const result = await db.update(biddingCompanies)
      .set({
        finalQuoteAmount: quotationAmount.toString(),
        awardRatio: awardRatio.toString(),
        isWinner: awardRatio > 0,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))
      .returning({ biddingId: biddingCompanies.biddingId })

    // 캐시 무효화
    if (result.length > 0) {
      const biddingId = result[0].biddingId
      revalidateTag(`bidding-${biddingId}`)
      revalidateTag('quotation-vendors')
      revalidateTag('quotation-details')
      revalidatePath(`/evcp/bid/${biddingId}`)
    }

    return {
      success: true,
      message: '업체 정보가 성공적으로 수정되었습니다.',
    }
  } catch (error) {
    console.error('Failed to update bidding detail vendor:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '업체 정보 수정에 실패했습니다.' 
    }
  }
}

// 발주비율 취소 (발주비율을 null로 리셋하고 낙찰 상태 해제)
export async function cancelAwardRatio(biddingCompanyId: number) {
  try {
    const result = await db.update(biddingCompanies)
      .set({
        awardRatio: null,
        isWinner: false,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))
      .returning({ biddingId: biddingCompanies.biddingId })

    // 캐시 무효화
    if (result.length > 0) {
      const biddingId = result[0].biddingId
      revalidateTag(`bidding-${biddingId}`)
      revalidateTag('quotation-vendors')
      revalidateTag('quotation-details')
      revalidatePath(`/evcp/bid/${biddingId}`)
    }

    return {
      success: true,
      message: '발주비율이 성공적으로 취소되었습니다.',
    }
  } catch (error) {
    console.error('Failed to cancel award ratio:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '발주비율 취소에 실패했습니다.'
    }
  }
}

// 본입찰용 업체 추가
export async function createBiddingDetailVendor(
  biddingId: number,
  vendorId: number,
  isPriceAdjustmentApplicableQuestion?: boolean
) {
  try {
    const result = await db.transaction(async (tx) => {
      // 1. biddingCompanies 레코드 생성 (본입찰 선정 기본값 true)
      const biddingCompanyResult = await tx.insert(biddingCompanies).values({
        biddingId: biddingId,
        companyId: vendorId,
        invitationStatus: 'pending', // 초대 대기
        isPreQuoteSelected: true, // 본입찰 등록 기본값
        isWinner: null, // 미정 상태로 초기화 0916
        isPriceAdjustmentApplicableQuestion: isPriceAdjustmentApplicableQuestion ?? false,
        createdAt: new Date(),
        updatedAt: new Date(),
      }).returning({ id: biddingCompanies.id })

      if (biddingCompanyResult.length === 0) {
        throw new Error('업체 추가에 실패했습니다.')
      }

      const biddingCompanyId = biddingCompanyResult[0].id

      // 2. company_condition_responses 레코드 생성 (기본값)
      await tx.insert(companyConditionResponses).values({
        biddingCompanyId: biddingCompanyId,
        submittedAt: new Date(),
        createdAt: new Date(),
        updatedAt: new Date(),
      })

      return biddingCompanyId
    })

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('quotation-vendors')
    revalidateTag('quotation-details')
    revalidatePath(`/evcp/bid/${biddingId}`)

    return {
      success: true,
      message: '업체가 성공적으로 추가되었습니다.',
      data: { id: result }
    }
  } catch (error) {
    console.error('Failed to create bidding detail vendor:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '업체 추가에 실패했습니다.' 
    }
  }
}

// 유찰 처리
export async function markAsDisposal(biddingId: number, userId: string) {
  try {
    // 입찰 정보 조회
    const biddingInfo = await db
      .select()
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

    const bidding = biddingInfo[0]

    // 입찰 참여 업체들 조회
    const participantCompanies = await db
      .select({
        companyId: biddingCompanies.companyId,
        companyName: vendors.vendorName,
        contactEmail: vendors.email
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isBiddingParticipated, true)
      ))
    const userName = await getUserNameById(userId)

    // 입찰 상태를 유찰로 변경
    await db
      .update(biddings)
      .set({
        status: 'bidding_disposal',
        updatedBy: userName,
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))

    // 참여 업체들에게 유찰 안내 메일 발송
    for (const company of participantCompanies) {
      if (company.contactEmail) {
        try {
          await sendEmail({
            to: company.contactEmail,
            template: 'bidding-disposal',
            context: {
              companyName: company.companyName,
              biddingNumber: bidding.biddingNumber,
              title: bidding.title,
              projectName: bidding.projectName,
              itemName: bidding.itemName,
              biddingType: bidding.biddingType,
              processedDate: new Date().toLocaleDateString('ko-KR'),
              bidPicName: bidding.bidPicName,
              supplyPicName: bidding.supplyPicName,
              language: 'ko'
            }
          })
        } catch (emailError) {
          console.error(`Failed to send disposal email to ${company.contactEmail}:`, emailError)
        }
      }
    }

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('quotation-vendors')
    revalidateTag('quotation-details')
    revalidatePath(`/evcp/bid/${biddingId}`)
    
    return { 
      success: true, 
      message: `유찰 처리가 완료되었습니다. ${participantCompanies.length}개 업체에 안내 메일을 발송했습니다.` 
    }
  } catch (error) {
    console.error('Failed to mark as disposal:', error)
    return { success: false, error: '유찰 처리에 실패했습니다.' }
  }
}

// 입찰 등록 ( 본입찰 초대 발송)
export async function registerBidding(biddingId: number, userId: string) {
  debugLog('registerBidding started', { biddingId, userId })
  try {
    // 본입찰에서 개별적으로 추가한 업체들 조회
    const selectedCompanies = await db
      .select({
        companyId: biddingCompanies.companyId,
        companyName: vendors.vendorName,
        contactEmail: vendors.email
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(
        eq(biddingCompanies.biddingId, biddingId)
      )

    debugLog('registerBidding: selectedCompanies fetched', { count: selectedCompanies.length, selectedCompanies })

    // 입찰 정보 조회
    const biddingInfo = await db
      .select()
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

    if (biddingInfo.length === 0) {
      debugError('registerBidding: Bidding info not found', { biddingId })
      return { success: false, error: '입찰 정보를 찾을 수 없습니다.' }
    }

    const bidding = biddingInfo[0]
    debugLog('registerBidding: bidding info fetched', { bidding })

    const userName = await getUserNameById(userId)
    debugLog('registerBidding: userName fetched', { userName })

    await db.transaction(async (tx) => {
      debugLog('registerBidding: Transaction started')
      // 1. 입찰 상태를 오픈으로 변경
      await tx
        .update(biddings)
        .set({
          status: 'bidding_opened',
          updatedBy: userName,
          updatedAt: new Date()
        })
        .where(eq(biddings.id, biddingId))

      // 2. 선정된 업체들의 입찰 초대 여부를 true로 변경하고 초대 상태 업데이트
      for (const company of selectedCompanies) {
        await tx
          .update(biddingCompanies)
          .set({
            isBiddingInvited: true,
            invitationStatus: 'bidding_sent', // 입찰 초대 발송
            updatedAt: new Date()
          })
          .where(and(
            eq(biddingCompanies.biddingId, biddingId),
            eq(biddingCompanies.companyId, company.companyId)
          ))
      }
      debugLog('registerBidding: Transaction completed (status updated, companies invited)')
    })

    // 3. 선정된 업체들에게 본입찰 초대 메일 발송
    debugLog('registerBidding: Sending emails...')
    for (const company of selectedCompanies) {
      // 벤더 메인 이메일로 발송
      if (company.contactEmail) {
        try {
          await sendEmail({
            to: company.contactEmail,
            template: 'bidding-invitation', // 새로운 본입찰 초대 템플릿 필요
            context: {
              companyName: company.companyName,
              biddingNumber: bidding.biddingNumber,
              title: bidding.title,
              projectName: bidding.projectName,
              itemName: bidding.itemName,
              biddingType: bidding.biddingType,
              submissionStartDate: bidding.submissionStartDate,
              submissionEndDate: bidding.submissionEndDate,
              biddingUrl: `${process.env.NEXT_PUBLIC_BASE_URL}/partners/bid/${biddingId}`,
              bidPicName: bidding.bidPicName,
              supplyPicName: bidding.supplyPicName,
              language: 'ko'
            }
          })
          debugLog(`registerBidding: Email sent to ${company.contactEmail}`)
        } catch (emailError) {
          debugError(`Failed to send bidding invitation email to ${company.contactEmail}:`, emailError)
        }
      }

      // 추가 담당자들에게도 이메일 발송
      try {
        const contactInfos = await db
          .select({
            contactName: biddingCompaniesContacts.contactName,
            contactEmail: biddingCompaniesContacts.contactEmail
          })
          .from(biddingCompaniesContacts)
          .where(and(
            eq(biddingCompaniesContacts.biddingId, biddingId),
            eq(biddingCompaniesContacts.vendorId, company.companyId)
          ));

        for (const contact of contactInfos) {
          // 벤더 메인 이메일과 중복되지 않는 경우에만 발송
          if (contact.contactEmail && contact.contactEmail !== company.contactEmail) {
            try {
              await sendEmail({
                to: contact.contactEmail,
                template: 'bidding-invitation',
                context: {
                  companyName: company.companyName,
                  biddingNumber: bidding.biddingNumber,
                  title: bidding.title,
                  projectName: bidding.projectName,
                  itemName: bidding.itemName,
                  biddingType: bidding.biddingType,
                  submissionStartDate: bidding.submissionStartDate,
                  submissionEndDate: bidding.submissionEndDate,
                  biddingUrl: `${process.env.NEXT_PUBLIC_BASE_URL}/partners/bid/${biddingId}`,
                  bidPicName: bidding.bidPicName,
                  supplyPicName: bidding.supplyPicName,
                  language: 'ko'
                }
              })
              debugLog(`registerBidding: Email sent to contact ${contact.contactEmail}`)
            } catch (emailError) {
              debugError(`Failed to send bidding invitation email to contact ${contact.contactEmail}:`, emailError)
            }
          }
        }
      } catch (contactError) {
        debugError('Failed to fetch contact emails:', contactError)
      }
    }
    // 4. 입찰 공고 SMS 알림 전송
    debugLog('registerBidding: Sending SMS...')
    for (const company of selectedCompanies) {
      // biddingCompaniesContacts에서 모든 연락처 전화번호 조회
      const contactInfos = await db
        .select({
          contactNumber: biddingCompaniesContacts.contactNumber
        })
        .from(biddingCompaniesContacts)
        .where(and(
          eq(biddingCompaniesContacts.biddingId, biddingId),
          eq(biddingCompaniesContacts.vendorId, company.companyId)
        ));

      // 각 연락처에 SMS 전송
      for (const contactInfo of contactInfos) {
        const contactPhone = contactInfo.contactNumber;

        if (contactPhone) {
          try {
            const smsResult = await sendBiddingNoticeSms(contactPhone, bidding.title);
            if (smsResult.success) {
              debugLog(`입찰 공고 SMS 전송 성공: ${contactPhone} - ${bidding.title}`);
            } else {
              debugError(`입찰 공고 SMS 전송 실패: ${contactPhone} - ${smsResult.error}`);
            }
          } catch (smsError) {
            debugError(`Failed to send bidding notice SMS to ${contactPhone}:`, smsError)
          }
        }
      }
    }
    // 캐시 무효화 (API를 통한 방식)
    const { revalidateViaCronJob } = await import('@/lib/revalidation-utils');
    await revalidateViaCronJob({
      tags: [`bidding-${biddingId}`, 'bidding-detail', 'quotation-vendors', 'quotation-details', 'pr-items']
    });
    
    debugSuccess(`registerBidding: Success. Invited ${selectedCompanies.length} companies.`)
    return { 
      success: true, 
      message: `입찰이 성공적으로 등록되었습니다. ${selectedCompanies.length}개 업체에 초대 메일을 발송했습니다.` 
    }
  } catch (error) {
    debugError('Failed to register bidding:', error)
    return { success: false, error: `입찰 등록에 실패했습니다. ${error}` }
  }
}


// 업체 선정 사유 업데이트
export async function updateVendorSelectionReason(biddingId: number, selectedCompanyId: number, selectionReason: string, userId: string) {
  debugLog('updateVendorSelectionReason started', { biddingId, selectedCompanyId, selectionReason, userId })
  try {
    const userName = await getUserNameById(userId)
    debugLog('updateVendorSelectionReason: userName fetched', { userName })

    // vendorSelectionResults 테이블에 삽입 또는 업데이트
    await db
      .insert(vendorSelectionResults)
      .values({
        biddingId,
        selectedCompanyId,
        selectionReason,
        selectedBy: userName,
        selectedAt: new Date(),
        createdAt: new Date(),
        updatedAt: new Date()
      })
      .onConflictDoUpdate({
        target: [vendorSelectionResults.biddingId],
        set: {
          selectedCompanyId,
          selectionReason,
          selectedBy: userName,
          selectedAt: new Date(),
          updatedAt: new Date()
        }
      })

    debugLog('updateVendorSelectionReason: DB updated')

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('quotation-vendors')
    revalidatePath(`/evcp/bid/${biddingId}`)
    
    debugSuccess('updateVendorSelectionReason: Success')
    return { success: true, message: '업체 선정 사유가 성공적으로 업데이트되었습니다.' }
  } catch (error) {
    debugError('Failed to update vendor selection reason:', error)
    return { success: false, error: '업체 선정 사유 업데이트에 실패했습니다.' }
  }
}

// 낙찰용 문서 업로드
export async function uploadAwardDocument(biddingId: number, file: File, userId: string) {
  try {
    const userName = await getUserNameById(userId)
    const saveResult = await saveFile({
      file,
      directory: `biddings/${biddingId}/award`,
      userId: userId
    })

    if (saveResult.success && saveResult.filePath) {
      // biddingDocuments 테이블에 저장
      const [document] = await db.insert(biddingDocuments).values({
        biddingId,
        fileName: saveResult.fileName || file.name,
        originalFileName: file.name,
        filePath: saveResult.filePath,
        fileSize: file.size,
        documentType: 'other',
        title: '낙찰 관련 문서',
        description: '낙찰 관련 첨부파일',
        uploadedBy: userName,
        uploadedAt: new Date(),
        // createdAt, updatedAt 필드가 스키마에 없으므로 제거
      }).returning()

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

// 낙찰용 문서 목록 조회
export async function getAwardDocuments(biddingId: number) {
  try {
    const documents = await db
      .select()
      .from(biddingDocuments)
      .where(and(
        eq(biddingDocuments.biddingId, biddingId),
        eq(biddingDocuments.documentType, 'other')
      ))
      .orderBy(desc(biddingDocuments.uploadedAt))

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

// 낙찰용 문서 다운로드
export async function getAwardDocumentForDownload(documentId: number, biddingId: number) {
  try {
    const documents = await db
      .select()
      .from(biddingDocuments)
      .where(and(
        eq(biddingDocuments.id, documentId),
        eq(biddingDocuments.biddingId, biddingId),
        eq(biddingDocuments.documentType, 'other')
      ))
      .limit(1)

    if (documents.length === 0) {
      return {
        success: false,
        error: '문서를 찾을 수 없습니다.'
      }
    }

    return {
      success: true,
      document: documents[0]
    }
  } catch (error) {
    console.error('Failed to get award document for download:', error)
    return {
      success: false,
      error: '문서 다운로드 준비에 실패했습니다.'
    }
  }
}

// 낙찰용 문서 삭제
export async function deleteAwardDocument(documentId: number, biddingId: number, userId: string) {
  try {
    const userName = await getUserNameById(userId)
    // 문서 정보 조회
    const documents = await db
      .select()
      .from(biddingDocuments)
      .where(and(
        eq(biddingDocuments.id, documentId),
        eq(biddingDocuments.biddingId, biddingId),
        eq(biddingDocuments.documentType, 'other'),
        eq(biddingDocuments.uploadedBy, userName)
      ))
      .limit(1)

    if (documents.length === 0) {
      return {
        success: false,
        error: '삭제할 수 있는 문서가 없습니다.'
      }
    }

    // DB에서 삭제
    await db
      .delete(biddingDocuments)
      .where(eq(biddingDocuments.id, documentId))

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)

    return {
      success: true,
      message: '문서가 성공적으로 삭제되었습니다.'
    }
  } catch (error) {
    console.error('Failed to delete award document:', error)
    return {
      success: false,
      error: '문서 삭제에 실패했습니다.'
    }
  }
}

// 낙찰 처리 (발주비율과 함께)
export async function awardBidding(biddingId: number, selectionReason: string, userId: string) {
  try {
    const userName = await getUserNameById(userId)

    // 입찰 정보 조회 (contractType 포함)
    const biddingInfo = await db
      .select({
        contractType: biddings.contractType,
        status: biddings.status
      })
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

    const bidding = biddingInfo[0]

    // 낙찰된 업체들 조회 (isWinner가 true인 업체들)
    const awardedCompanies = await db
      .select({
        companyId: biddingCompanies.companyId,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        awardRatio: biddingCompanies.awardRatio
      })
      .from(biddingCompanies)
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isWinner, true)
      ))

    if (awardedCompanies.length === 0) {
      return { success: false, error: '낙찰된 업체가 없습니다. 먼저 발주비율을 산정해주세요.' }
    }

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

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

    // 최종입찰가 계산 (낙찰된 업체의 견적금액 * 발주비율의 합)
    let finalBidPrice = 0
    for (const company of awardedCompanies) {
      const quoteAmount = parseFloat(company.finalQuoteAmount?.toString() || '0')
      const ratio = parseFloat(company.awardRatio?.toString() || '0') / 100
      finalBidPrice += quoteAmount * ratio
    }

    await db.transaction(async (tx) => {
      // 1. 입찰 상태를 낙찰로 변경하고 최종입찰가 업데이트
      await tx
        .update(biddings)
        .set({
          status: 'vendor_selected',
          finalBidPrice: finalBidPrice.toString(),
          updatedAt: new Date()
        })
        .where(eq(biddings.id, biddingId))

      // 2. 선정 사유 저장 (첫 번째 낙찰 업체 기준으로 저장)
      const firstAwardedCompany = awardedCompanies[0]
      
      // 기존 선정 결과 확인
      const existingResult = await tx
        .select()
        .from(vendorSelectionResults)
        .where(eq(vendorSelectionResults.biddingId, biddingId))
        .limit(1)

      if (existingResult.length > 0) {
        // 업데이트
        await tx
          .update(vendorSelectionResults)
          .set({
            selectedCompanyId: firstAwardedCompany.companyId,
            selectionReason,
            selectedBy: userName,
            selectedAt: new Date(),
            updatedAt: new Date()
          })
          .where(eq(vendorSelectionResults.biddingId, biddingId))
      } else {
        // 삽입
        await tx
          .insert(vendorSelectionResults)
          .values({
            biddingId,
            selectedCompanyId: firstAwardedCompany.companyId,
            selectionReason,
            selectedBy: userName,
            selectedAt: new Date(),
            createdAt: new Date(),
            updatedAt: new Date()
          })
      }

    })

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('quotation-vendors')
    revalidateTag('quotation-details')
    revalidatePath(`/evcp/bid/${biddingId}`)

    return {
      success: true,
      message: `낙찰 처리가 완료되었습니다. 최종입찰가: ${finalBidPrice.toLocaleString()}원`
    }
  } catch (error) {
    console.error('Failed to award bidding:', error)
    return { success: false, error: '낙찰 처리에 실패했습니다.' }
  }
}

// 낙찰된 업체 정보 조회
export async function getAwardedCompanies(biddingId: number) {
  try {
    const awardedCompanies = await db
      .select({
        companyId: biddingCompanies.companyId,
        companyName: vendors.vendorName,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        awardRatio: biddingCompanies.awardRatio
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.isWinner, true)
      ))

    return awardedCompanies.map(company => ({
      companyId: company.companyId,
      companyName: company.companyName,
      finalQuoteAmount: parseFloat(company.finalQuoteAmount?.toString() || '0'),
      awardRatio: parseFloat(company.awardRatio?.toString() || '0')
    }))
  } catch (error) {
    console.error('Failed to get awarded companies:', error)
    return []
  }
}

// 입찰의 PR 아이템 금액 합산하여 bidding 업데이트
async function updateBiddingAmounts(biddingId: number) {
  try {
    // 해당 bidding의 모든 PR 아이템들의 금액 합계 계산
    const amounts = await db
      .select({
        totalTargetAmount: sql<number>`COALESCE(SUM(${prItemsForBidding.targetAmount}), 0)`,
        totalBudgetAmount: sql<number>`COALESCE(SUM(${prItemsForBidding.budgetAmount}), 0)`,
        totalActualAmount: sql<number>`COALESCE(SUM(${prItemsForBidding.actualAmount}), 0)`
      })
      .from(prItemsForBidding)
      .where(eq(prItemsForBidding.biddingId, biddingId))

    const { totalTargetAmount, totalBudgetAmount, totalActualAmount } = amounts[0]

    // bidding 테이블 업데이트
    await db
      .update(biddings)
      .set({
        targetPrice: totalTargetAmount.toString(),
        budget: totalBudgetAmount.toString(),
        finalBidPrice: totalActualAmount.toString(),
        updatedAt: new Date()
      })
      .where(eq(biddings.id, biddingId))

    console.log(`Bidding ${biddingId} amounts updated: target=${totalTargetAmount}, budget=${totalBudgetAmount}, actual=${totalActualAmount}`)
  } catch (error) {
    console.error('Failed to update bidding amounts:', error)
    throw error
  }
}

// PR 품목 정보 업데이트
export async function updatePrItem(prItemId: number, input: Partial<typeof prItemsForBidding.$inferSelect>, userId: string) {
  try {
    // 업데이트 전 biddingId 확인
    const prItem = await db
      .select({ biddingId: prItemsForBidding.biddingId })
      .from(prItemsForBidding)
      .where(eq(prItemsForBidding.id, prItemId))
      .limit(1)

    if (!prItem[0]?.biddingId) {
      throw new Error('PR item not found or biddingId is missing')
    }

    const biddingId = prItem[0].biddingId

    await db
      .update(prItemsForBidding)
      .set({
        ...input,
        updatedAt: new Date()
      })
      .where(eq(prItemsForBidding.id, prItemId))

    // PR 아이템 금액 합산하여 bidding 업데이트
    await updateBiddingAmounts(biddingId)

    // 프로젝트 정보 업데이트
    await updateBiddingProjectInfo(biddingId)

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('pr-items')
    revalidatePath(`/evcp/bid/${biddingId}`)

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

// 입찰 참여여부 업데이트
export async function updateBiddingParticipation(
  biddingCompanyId: number,
  participated: boolean,
  userId: string
) {
  try {
    const result = await db.update(biddingCompanies)
      .set({
        isBiddingParticipated: participated,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))
      .returning({ biddingId: biddingCompanies.biddingId })

    // 캐시 무효화
    if (result.length > 0) {
      const biddingId = result[0].biddingId
      revalidateTag(`bidding-${biddingId}`)
      revalidateTag('quotation-vendors')
      revalidatePath(`/evcp/bid/${biddingId}`)
    }

    return {
      success: true,
      message: `입찰 참여상태가 ${participated ? '응찰' :'응찰포기'}로 업데이트되었습니다.`,
    }
  } catch (error) {
    console.error('Failed to update bidding participation:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '입찰 참여상태 업데이트에 실패했습니다.' 
    }
  }
}

// =================================================
// 품목별 입찰 관련 함수들 (본입찰용)
// =================================================

// 품목별 입찰 임시 저장 (본입찰용)
export async function saveBiddingDraft(
  biddingCompanyId: number,
  prItemQuotations: Array<{
    prItemId: number
    bidUnitPrice: number
    bidAmount: number
    proposedDeliveryDate?: string
    technicalSpecification?: string
  }>,
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)
    let totalAmount = 0
    
    await db.transaction(async (tx) => {
      // 품목별 입찰 Upsert 방식으로 저장
      for (const item of prItemQuotations) {
        // 기존 데이터 확인
        const existingItem = await tx
          .select()
          .from(companyPrItemBids)
          .where(
            and(
              eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
              eq(companyPrItemBids.prItemId, item.prItemId),
            )
          )
          .limit(1)

        const itemData = {
          bidUnitPrice: item.bidUnitPrice.toString(),
          bidAmount: item.bidAmount.toString(),
          proposedDeliveryDate: item.proposedDeliveryDate,
          technicalSpecification: item.technicalSpecification,
          currency: 'KRW',
          updatedAt: new Date()
        }

        if (existingItem.length > 0) {
          // 업데이트
          await tx
            .update(companyPrItemBids)
            .set(itemData)
            .where(
              and(
                eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
                eq(companyPrItemBids.prItemId, item.prItemId),
                eq(companyPrItemBids.isPreQuote, false)
              )
            )
        } else {
          // 새로 생성
          await tx.insert(companyPrItemBids)
            .values({
              biddingCompanyId,
              prItemId: item.prItemId,
              isPreQuote: false, // 본입찰 데이터
              createdAt: new Date(),
              ...itemData
            })
        }

        totalAmount += item.bidAmount
      }
    })

    // 캐시 무효화
    revalidateTag(`bidding-${biddingCompanyId}`)
    revalidateTag('quotation-vendors')

    return {
      success: true,
      message: '품목별 입찰이 임시 저장되었습니다.',
      totalAmount
    }
  } catch (error) {
    console.error('Failed to save bidding draft:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '임시 저장에 실패했습니다.'
    }
  }
}

// 본입찰용 품목별 견적 조회 (협력업체용)
export async function getPartnerBiddingItemQuotations(biddingCompanyId: number) {
  try {
    const savedQuotations = await db
      .select({
        prItemId: companyPrItemBids.prItemId,
        bidUnitPrice: companyPrItemBids.bidUnitPrice,
        bidAmount: companyPrItemBids.bidAmount,
        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,
        technicalSpecification: companyPrItemBids.technicalSpecification,
        currency: companyPrItemBids.currency
      })
      .from(companyPrItemBids)
      .where(
        and(
          eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
          eq(companyPrItemBids.isPreQuote, false) // 본입찰 데이터
        )
      )

    // Decimal 타입을 number로 변환
    return savedQuotations.map(item => ({
      prItemId: item.prItemId,
      bidUnitPrice: parseFloat(item.bidUnitPrice || '0'),
      bidAmount: parseFloat(item.bidAmount || '0'),
      proposedDeliveryDate: item.proposedDeliveryDate,
      technicalSpecification: item.technicalSpecification,
      currency: item.currency
    }))
  } catch (error) {
    console.error('Failed to get partner bidding item quotations:', error)
    return []
  }
}

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

// 협력업체용 입찰 참여여부 업데이트
export async function updatePartnerBiddingParticipation(
  biddingCompanyId: number,
  participated: boolean,
  userId: string
) {
  try {
    const result = await db.update(biddingCompanies)
      .set({
        isBiddingParticipated: participated,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))
      .returning({ biddingId: biddingCompanies.biddingId })

    // 캐시 무효화
    if (result.length > 0) {
      const biddingId = result[0].biddingId
      revalidateTag(`bidding-${biddingId}`)
      revalidateTag('quotation-vendors')
      revalidateTag(`partners-bidding-${biddingId}`)
      revalidatePath(`/partners/bid/${biddingId}`)
    }

    return {
      success: true,
      message: `입찰 참여상태가 ${participated ? '응찰' : '응찰포기'}로 업데이트되었습니다.`,
    }
  } catch (error) {
    console.error('Failed to update partner bidding participation:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '입찰 참여상태 업데이트에 실패했습니다.' 
    }
  }
}

// 협력업체용 입찰 목록 조회 (bidding_companies 기준)
export interface PartnersBiddingListItem {
  // bidding_companies 정보
  id: number
  biddingCompanyId: number
  invitationStatus: string
  respondedAt: string | null
  finalQuoteAmount: number | null
  finalQuoteSubmittedAt: string | null
  isFinalSubmission: boolean | null
  isWinner: boolean | null
  isAttendingMeeting: boolean | null
  isPreQuoteSelected: boolean | null
  isPreQuoteParticipated: boolean | null
  isBiddingParticipated: boolean | null
  preQuoteDeadline: Date | null
  isBiddingInvited: boolean | null
  notes: string | null
  createdAt: Date
  updatedAt: Date
  // updatedBy: string | null
  hasSpecificationMeeting: boolean | null
  // biddings 정보
  biddingId: number
  biddingNumber: string
  originalBiddingNumber: string | null // 원입찰번호
  revision: number | null
  projectName: string
  itemName: string
  title: string
  contractType: string
  biddingType: string
  preQuoteDate: Date | null
  contractStartDate: Date | null
  contractEndDate: Date | null
  submissionStartDate: Date | null
  submissionEndDate: Date | null
  status: string
  // 입찰담당자
  bidPicName: string | null
  // 조달담당자
  supplyPicName: string | null
  currency: string
  budget: number | null
  isUrgent: boolean | null // 긴급여부

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

// 협력업체용 입찰 목록 조회 (bidding_companies 기준)
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,
        isFinalSubmission: biddingCompanies.isFinalSubmission,
        isWinner: biddingCompanies.isWinner,
        isAttendingMeeting: biddingCompanies.isAttendingMeeting,
        isPreQuoteSelected: biddingCompanies.isPreQuoteSelected,
        isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated,
        isBiddingParticipated: biddingCompanies.isBiddingParticipated,
        preQuoteDeadline: biddingCompanies.preQuoteDeadline,
        isBiddingInvited: biddingCompanies.isBiddingInvited,
        notes: biddingCompanies.notes,
        createdAt: biddingCompanies.createdAt,
        updatedAt: biddingCompanies.updatedAt,
        // updatedBy: biddingCompanies.updatedBy, // 이 필드가 존재하지 않음

        // biddings 정보
        biddingId: biddings.id,
        biddingNumber: biddings.biddingNumber,
        originalBiddingNumber: biddings.originalBiddingNumber, // 원입찰번호
        revision: biddings.revision,
        projectName: biddings.projectName,
        itemName: biddings.itemName,
        title: biddings.title,
        contractType: biddings.contractType,
        biddingType: biddings.biddingType,
        preQuoteDate: biddings.preQuoteDate,
        contractStartDate: biddings.contractStartDate,
        contractEndDate: biddings.contractEndDate,
        submissionStartDate: biddings.submissionStartDate,
        submissionEndDate: biddings.submissionEndDate,
        status: biddings.status,
        // 기존 담당자 필드 (하위호환성 유지)

        // 입찰담당자
        bidPicName: biddings.bidPicName,
        // 조달담당자
        supplyPicName: biddings.supplyPicName,
        currency: biddings.currency,
        budget: biddings.budget,
        isUrgent: biddings.isUrgent,
        hasSpecificationMeeting: biddings.hasSpecificationMeeting,
      })
      .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 instanceof Date ? item.respondedAt.toISOString() : item.respondedAt.toString()) : null,
      finalQuoteAmount: item.finalQuoteAmount ? Number(item.finalQuoteAmount) : null, // string을 number로 변환
      finalQuoteSubmittedAt: item.finalQuoteSubmittedAt ? (item.finalQuoteSubmittedAt instanceof Date ? item.finalQuoteSubmittedAt.toISOString() : item.finalQuoteSubmittedAt.toString()) : null,
      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,
        biddingId: biddings.id, // partners-bidding-detail.tsx에서 필요한 필드
        biddingNumber: biddings.biddingNumber,
        revision: biddings.revision,
        projectName: biddings.projectName,
        itemName: biddings.itemName,
        title: biddings.title,
        description: biddings.description,

        // 계약 정보
        contractType: biddings.contractType,
        biddingType: biddings.biddingType,
        awardCount: biddings.awardCount,
        preQuoteDate: biddings.preQuoteDate,
        contractStartDate: biddings.contractStartDate,
        contractEndDate: biddings.contractEndDate,

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

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

        // 상태 및 담당자
        status: biddings.status,
        isUrgent: biddings.isUrgent,
        bidPicName: biddings.bidPicName,
        bidPicPhone: users.phone,
        supplyPicName: biddings.supplyPicName,

        // 협력업체 특정 정보
        biddingCompanyId: biddingCompanies.id,
        invitationStatus: biddingCompanies.invitationStatus,
        finalQuoteAmount: biddingCompanies.finalQuoteAmount,
        finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt,
        isFinalSubmission: biddingCompanies.isFinalSubmission,
        isWinner: biddingCompanies.isWinner,
        isAttendingMeeting: biddingCompanies.isAttendingMeeting,
        isPreQuoteSelected: biddingCompanies.isPreQuoteSelected,
        isBiddingParticipated: biddingCompanies.isBiddingParticipated,
        isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated,
        isPriceAdjustmentApplicableQuestion: biddingCompanies.isPriceAdjustmentApplicableQuestion,
        hasSpecificationMeeting: biddings.hasSpecificationMeeting,
        // 응답한 조건들 (company_condition_responses) - 제시된 조건과 응답 모두 여기서 관리
        paymentTermsResponse: companyConditionResponses.paymentTermsResponse,
        taxConditionsResponse: companyConditionResponses.taxConditionsResponse,
        incotermsResponse: companyConditionResponses.incotermsResponse,
        proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate,
        proposedShippingPort: companyConditionResponses.proposedShippingPort,
        proposedDestinationPort: companyConditionResponses.proposedDestinationPort,
        priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse,
        isInitialResponse: companyConditionResponses.isInitialResponse,
        sparePartResponse: companyConditionResponses.sparePartResponse,
        additionalProposals: companyConditionResponses.additionalProposals,
        responseSubmittedAt: companyConditionResponses.submittedAt,
      })
      .from(biddings)
      .leftJoin(users, eq(biddings.bidPicId, users.id))
      .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
    isInitialResponse?: boolean
    sparePartResponse?: string
    additionalProposals?: string
    finalQuoteAmount?: number
    isFinalSubmission?: boolean // 최종제출 여부 추가
    prItemQuotations?: Array<{
      prItemId: number
      bidUnitPrice: number
      bidAmount: number
      proposedDeliveryDate?: string
      technicalSpecification?: string
    }>
    priceAdjustmentForm?: {
      itemName?: string
      adjustmentReflectionPoint?: string
      majorApplicableRawMaterial?: string
      adjustmentFormula?: string
      rawMaterialPriceIndex?: string
      referenceDate?: string
      comparisonDate?: string
      adjustmentRatio?: number
      notes?: string
      adjustmentConditions?: string
      majorNonApplicableRawMaterial?: string
      adjustmentPeriod?: string
      contractorWriter?: string
      adjustmentDate?: string
      nonApplicableReason?: string
    }
  },
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)
    const result = await db.transaction(async (tx) => {
      // 0. 품목별 견적 정보 최종 저장 (본입찰 제출) - Upsert 방식
      if (response.prItemQuotations && response.prItemQuotations.length > 0) {
        for (const item of response.prItemQuotations) {
          // 기존 데이터 확인
          const existingItem = await tx
            .select()
            .from(companyPrItemBids)
            .where(
              and(
                eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
                eq(companyPrItemBids.prItemId, item.prItemId),
              )
            )
            .limit(1)

          const itemData = {
            bidUnitPrice: item.bidUnitPrice.toString(),
            bidAmount: item.bidAmount.toString(),
            proposedDeliveryDate: item.proposedDeliveryDate || null,
            technicalSpecification: item.technicalSpecification || null,
            currency: 'KRW',
            submittedAt: new Date(),
            updatedAt: new Date()
          }

          if (existingItem.length > 0) {
            // 업데이트
            await tx
              .update(companyPrItemBids)
              .set(itemData)
              .where(
                and(
                  eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
                  eq(companyPrItemBids.prItemId, item.prItemId),
                  eq(companyPrItemBids.isPreQuote, false)
                )
              )
          } else {
            // 새로 생성
            await tx.insert(companyPrItemBids)
              .values({
                biddingCompanyId,
                prItemId: item.prItemId,
                isPreQuote: false, // 본입찰 데이터
                createdAt: new Date(),
                ...itemData
              })
          }
        }
      }

      // 3. 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우)
      // if (response.priceAdjustmentResponse && response.priceAdjustmentForm) {
      //   const priceAdjustmentData = {
      //     companyConditionResponsesId: companyConditionResponseId,
      //     itemName: response.priceAdjustmentForm.itemName,
      //     adjustmentReflectionPoint: response.priceAdjustmentForm.adjustmentReflectionPoint,
      //     majorApplicableRawMaterial: response.priceAdjustmentForm.majorApplicableRawMaterial,
      //     adjustmentFormula: response.priceAdjustmentForm.adjustmentFormula,
      //     rawMaterialPriceIndex: response.priceAdjustmentForm.rawMaterialPriceIndex,
      //     referenceDate: response.priceAdjustmentForm.referenceDate || null,
      //     comparisonDate: response.priceAdjustmentForm.comparisonDate || null,
      //     adjustmentRatio: response.priceAdjustmentForm.adjustmentRatio,
      //     notes: response.priceAdjustmentForm.notes,
      //     adjustmentConditions: response.priceAdjustmentForm.adjustmentConditions,
      //     majorNonApplicableRawMaterial: response.priceAdjustmentForm.majorNonApplicableRawMaterial,
      //     adjustmentPeriod: response.priceAdjustmentForm.adjustmentPeriod,
      //     contractorWriter: response.priceAdjustmentForm.contractorWriter,
      //     adjustmentDate: response.priceAdjustmentForm.adjustmentDate || null,
      //     nonApplicableReason: response.priceAdjustmentForm.nonApplicableReason,
      //   }
      //
      //   // 기존 연동제 정보가 있는지 확인
      //   const existingPriceAdjustment = await tx
      //     .select()
      //     .from(priceAdjustmentForms)
      //     .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId))
      //     .limit(1)
      //
      //   if (existingPriceAdjustment.length > 0) {
      //     // 업데이트
      //     await tx
      //       .update(priceAdjustmentForms)
      //       .set(priceAdjustmentData)
      //       .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId))
      //   } else {
      //     // 새로 생성
      //     await tx.insert(priceAdjustmentForms).values(priceAdjustmentData)
      //   }
      // }

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

      if (response.finalQuoteAmount !== undefined) {
        companyUpdateData.finalQuoteAmount = response.finalQuoteAmount
        companyUpdateData.finalQuoteSubmittedAt = new Date()

        // isFinalSubmission에 따라 상태 및 플래그 설정
        if (response.isFinalSubmission) {
          companyUpdateData.isFinalSubmission = true
          companyUpdateData.invitationStatus = 'bidding_submitted' // 응찰 완료
        } else {
          companyUpdateData.isFinalSubmission = false
          // 임시저장: invitationStatus는 변경하지 않음 (bidding_accepted 유지)
        }

        // // 스냅샷은 임시저장/최종제출 관계없이 항상 생성
        // if (response.prItemQuotations && response.prItemQuotations.length > 0) {
        //   // 기존 스냅샷 조회
        //   const existingCompany = await tx
        //     .select({ quotationSnapshots: biddingCompanies.quotationSnapshots })
        //     .from(biddingCompanies)
        //     .where(eq(biddingCompanies.id, biddingCompanyId))
        //     .limit(1)

        //   const existingSnapshots = existingCompany[0]?.quotationSnapshots as any[] || []

        //   // 새로운 스냅샷 생성
        //   const newSnapshot = {
        //     id: Date.now().toString(), // 고유 ID
        //     round: existingSnapshots.length + 1, // 차수
        //     submittedAt: new Date().toISOString(),
        //     totalAmount: response.finalQuoteAmount,
        //     currency: 'KRW',
        //     isFinalSubmission: !!response.isFinalSubmission,
        //     items: response.prItemQuotations.map(item => ({
        //       prItemId: item.prItemId,
        //       bidUnitPrice: item.bidUnitPrice,
        //       bidAmount: item.bidAmount,
        //       proposedDeliveryDate: item.proposedDeliveryDate,
        //       technicalSpecification: item.technicalSpecification
        //     }))
        //   }

        //   // 스냅샷 배열에 추가
        //   companyUpdateData.quotationSnapshots = [...existingSnapshots, newSnapshot]
        // }
      }

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

      // biddingId 조회
      const biddingCompanyInfo = await tx
        .select({ biddingId: biddingCompanies.biddingId })
        .from(biddingCompanies)
        .where(eq(biddingCompanies.id, biddingCompanyId))
        .limit(1)

      const biddingId = biddingCompanyInfo[0]?.biddingId

      // 최종제출인 경우, 입찰 상태를 평가중으로 변경 (bidding_opened 상태에서만)
      if (biddingId && response.finalQuoteAmount !== undefined && response.isFinalSubmission) {
        await tx
          .update(biddings)
          .set({
            status: 'evaluation_of_bidding',
            updatedAt: new Date()
          })
          .where(and(
            eq(biddings.id, biddingId),
            eq(biddings.status, 'bidding_opened')
          ))
      }

      return biddingId
    })

    // 캐시 무효화
    if (result) {
      revalidateTag(`bidding-${result}`)
      revalidateTag('quotation-vendors')
      revalidateTag('quotation-details')
    }
    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 {
    // specification_meetings 테이블에서 사양설명회 정보 조회
    const specMeeting = await db
      .select({
        id: specificationMeetings.id,
        meetingDate: specificationMeetings.meetingDate,
        meetingTime: specificationMeetings.meetingTime,
        location: specificationMeetings.location,
        address: specificationMeetings.address,
        contactPerson: specificationMeetings.contactPerson,
        contactPhone: specificationMeetings.contactPhone,
        contactEmail: specificationMeetings.contactEmail,
        agenda: specificationMeetings.agenda,
        materials: specificationMeetings.materials,
        notes: specificationMeetings.notes,
        isRequired: specificationMeetings.isRequired,
      })
      .from(specificationMeetings)
      .where(eq(specificationMeetings.biddingId, biddingId))
      .limit(1)

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

    // 기본 입찰 정보도 가져오기 (제목, 입찰번호 등)
    const bidding = await db
      .select({
        id: biddings.id,
        title: biddings.title,
        biddingNumber: biddings.biddingNumber,
      })
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

    // 사양설명회 정보가 없는 경우
    if (specMeeting.length === 0) {
      return {
        success: true,
        data: {
          ...bidding[0],
          documents,
          meetingDate: null,
          meetingTime: null,
          location: null,
          address: null,
          contactPerson: null,
          contactPhone: null,
          contactEmail: null,
          agenda: null,
          materials: null,
          notes: null,
          isRequired: false,
        }
      }
    }

    return {
      success: true,
      data: {
        ...bidding[0],
        documents,
        meetingDate: specMeeting[0].meetingDate ? (specMeeting[0].meetingDate instanceof Date ? specMeeting[0].meetingDate.toISOString().split('T')[0] : specMeeting[0].meetingDate.toString().split('T')[0]) : null,
        meetingTime: specMeeting[0].meetingTime,
        location: specMeeting[0].location,
        address: specMeeting[0].address,
        contactPerson: specMeeting[0].contactPerson,
        contactPhone: specMeeting[0].contactPhone,
        contactEmail: specMeeting[0].contactEmail,
        agenda: specMeeting[0].agenda,
        materials: specMeeting[0].materials,
        notes: specMeeting[0].notes,
        isRequired: specMeeting[0].isRequired,
      }
    }
  } 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
  }
) {
  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))

      // 참석하는 경우, 사양설명회 담당자(contactEmail)에 이메일 발송을 위한 정보 반환
      if (attendanceData.isAttending) {
        // 입찰 + 사양설명회 + 업체 정보 불러오기
        const biddingInfo = await tx
          .select({
            biddingId: biddingCompanies.biddingId,
            companyId: biddingCompanies.companyId,
            bidPicName: biddings.bidPicName,
            supplyPicName: biddings.supplyPicName,
            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 : '알 수 없음'

          // 사양설명회 상세 정보(담당자 email 포함)
          const specificationMeetingInfo = await tx
            .select({
              contactEmail: specificationMeetings.contactEmail,
              meetingDate: specificationMeetings.meetingDate,
              meetingTime: specificationMeetings.meetingTime,
              location: specificationMeetings.location,
            })
            .from(specificationMeetings)
            .where(eq(specificationMeetings.biddingId, biddingInfo[0].biddingId))
            .limit(1)

          const contactEmail = specificationMeetingInfo.length > 0 ? specificationMeetingInfo[0].contactEmail : null

          // 메일 발송 (템플릿 사용)
          if (contactEmail) {
            try {
              const { sendEmail } = await import('@/lib/mail/sendEmail')

              await sendEmail({
                to: contactEmail,
                template: 'specification-meeting-attendance',
                context: {
                  biddingNumber: biddingInfo[0].biddingNumber,
                  title: biddingInfo[0].title,
                  companyName: companyName,
                  attendeeCount: attendanceData.attendeeCount,
                  representativeName: attendanceData.representativeName,
                  representativePhone: attendanceData.representativePhone,
                  bidPicName: biddingInfo[0].bidPicName,
                  supplyPicName: biddingInfo[0].supplyPicName,
                  meetingDate: specificationMeetingInfo[0]?.meetingDate,
                  meetingTime: specificationMeetingInfo[0]?.meetingTime,
                  location: specificationMeetingInfo[0]?.location,
                  contactEmail: contactEmail,
                  currentYear: new Date().getFullYear(),
                  language: 'ko'
                }
              })

              console.log(`사양설명회 참석 알림 메일 발송 완료: ${contactEmail}`)
            } catch (emailError) {
              console.error('메일 발송 실패:', emailError)
              // 메일 발송 실패해도 참석 여부 업데이트는 성공으로 처리
            }
          } else {
            console.warn('사양설명회 담당자 이메일이 없습니다.')
          }

          // 캐시 무효화
          revalidateTag(`bidding-${biddingInfo[0].biddingId}`)
          revalidateTag('quotation-vendors')

          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: '참석 여부 업데이트에 실패했습니다.' }
  }
}

// 연동제 정보 조회
export async function getPriceAdjustmentForm(companyConditionResponseId: number) {
  try {
    const priceAdjustment = await db
      .select()
      .from(priceAdjustmentForms)
      .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId))
      .limit(1)

    return priceAdjustment[0] || null
  } catch (error) {
    console.error('Failed to get price adjustment form:', error)
    return null
  }
}

// 입찰업체 ID로 연동제 정보 조회
export async function getPriceAdjustmentFormByBiddingCompanyId(biddingCompanyId: number) {
  try {
    const result = await db
      .select({
        priceAdjustmentForm: priceAdjustmentForms,
        companyConditionResponse: companyConditionResponses,
      })
      .from(companyConditionResponses)
      .leftJoin(priceAdjustmentForms, eq(companyConditionResponses.id, priceAdjustmentForms.companyConditionResponsesId))
      .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId))
      .limit(1)

    return result[0]?.priceAdjustmentForm || null
  } catch (error) {
    console.error('Failed to get price adjustment form by bidding company id:', error)
    return null
  }
}

// =================================================
// 입찰 문서 관리 함수들 (발주처 문서용)
// =================================================

// 입찰 문서 업로드 (발주처 문서용 - companyId: null)
export async function uploadBiddingDocument(
  biddingId: number,
  file: File,
  documentType: string,
  title: string,
  description: string,
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)

    // 파일 저장
    const saveResult = await saveFile({
      file,
      directory: `bidding/${biddingId}/documents`,
      originalName: file.name,
      userId
    })

    if (!saveResult.success) {
      return {
        success: false,
        error: saveResult.error || '파일 저장에 실패했습니다.'
      }
    }

    // 데이터베이스에 문서 정보 저장 (companyId는 null로 설정)
    const result = await db.insert(biddingDocuments)
      .values({
        biddingId,
        companyId: null, // 발주처 문서
        documentType: documentType as any,
        fileName: saveResult.fileName!,
        originalFileName: file.name,
        fileSize: file.size,
        mimeType: file.type,
        filePath: saveResult.publicPath!, // publicPath 사용 (웹 접근 가능한 경로)
        title,
        description,
        isPublic: true, // 발주처 문서는 기본적으로 공개
        isRequired: false,
        uploadedBy: userName,
        uploadedAt: new Date()
      })
      .returning()

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('bidding-documents')

    return {
      success: true,
      message: '문서가 성공적으로 업로드되었습니다.',
      documentId: result[0].id
    }
  } catch (error) {
    console.error('Failed to upload bidding document:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '문서 업로드에 실패했습니다.'
    }
  }
}

// 업로드된 입찰 문서 목록 조회 (발주처 문서용)
export async function getBiddingDocuments(biddingId: number) {
  try {
    const documents = await db
      .select({
        id: biddingDocuments.id,
        biddingId: biddingDocuments.biddingId,
        companyId: biddingDocuments.companyId,
        documentType: biddingDocuments.documentType,
        fileName: biddingDocuments.fileName,
        originalFileName: biddingDocuments.originalFileName,
        fileSize: biddingDocuments.fileSize,
        filePath: biddingDocuments.filePath,
        title: biddingDocuments.title,
        description: biddingDocuments.description,
        uploadedAt: biddingDocuments.uploadedAt,
        uploadedBy: biddingDocuments.uploadedBy
      })
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.biddingId, biddingId),
          sql`${biddingDocuments.companyId} IS NULL` // 발주처 문서만
        )
      )
      .orderBy(desc(biddingDocuments.uploadedAt))

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

// 입찰 문서 다운로드용 정보 조회
export async function getBiddingDocumentForDownload(documentId: number, biddingId: number) {
  try {
    const documents = await db
      .select()
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.id, documentId),
          eq(biddingDocuments.biddingId, biddingId),
          sql`${biddingDocuments.companyId} IS NULL` // 발주처 문서만
        )
      )
      .limit(1)

    if (documents.length === 0) {
      return {
        success: false,
        error: '문서를 찾을 수 없습니다.'
      }
    }

    return {
      success: true,
      document: documents[0]
    }
  } catch (error) {
    console.error('Failed to get bidding document for download:', error)
    return {
      success: false,
      error: '문서 다운로드 준비에 실패했습니다.'
    }
  }
}

// 입찰 문서 삭제 (발주처 문서용)
export async function deleteBiddingDocument(documentId: number, biddingId: number, userId: string) {
  try {
    const userName = await getUserNameById(userId)

    // 문서 정보 조회 (업로더 확인)
    const documents = await db
      .select()
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.id, documentId),
          eq(biddingDocuments.biddingId, biddingId),
          sql`${biddingDocuments.companyId} IS NULL`, // 발주처 문서만
          eq(biddingDocuments.uploadedBy, userName)
        )
      )
      .limit(1)

    if (documents.length === 0) {
      return {
        success: false,
        error: '삭제할 수 있는 문서가 없습니다.'
      }
    }

    // DB에서 삭제
    await db
      .delete(biddingDocuments)
      .where(eq(biddingDocuments.id, documentId))

    // 캐시 무효화
    revalidateTag(`bidding-${biddingId}`)
    revalidateTag('bidding-documents')

    return {
      success: true,
      message: '문서가 성공적으로 삭제되었습니다.'
    }
  } catch (error) {
    console.error('Failed to delete bidding document:', error)
    return {
      success: false,
      error: '문서 삭제에 실패했습니다.'
    }
  }
}

// 협력업체용 발주처 문서 조회 (협력업체용 첨부파일만)
export async function getBiddingDocumentsForPartners(biddingId: number) {
  try {
    const documents = await db
      .select({
        id: biddingDocuments.id,
        biddingId: biddingDocuments.biddingId,
        companyId: biddingDocuments.companyId,
        documentType: biddingDocuments.documentType,
        fileName: biddingDocuments.fileName,
        originalFileName: biddingDocuments.originalFileName,
        fileSize: biddingDocuments.fileSize,
        filePath: biddingDocuments.filePath,
        title: biddingDocuments.title,
        description: biddingDocuments.description,
        uploadedAt: biddingDocuments.uploadedAt,
        uploadedBy: biddingDocuments.uploadedBy
      })
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.biddingId, biddingId),
          eq(biddingDocuments.documentType, 'company_proposal'), // 협력업체용 첨부파일만
          sql`${biddingDocuments.companyId} IS NULL`, // 발주처 문서만
          eq(biddingDocuments.isPublic, true) // 공개 문서만
        )
      )
      .orderBy(desc(biddingDocuments.uploadedAt))

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

// =================================================
// 입찰가 비교 분석 함수들
// =================================================

// 벤더별 입찰가 정보 조회 (캐시 적용)
export async function getVendorPricesForBidding(biddingId: number) {
  return unstable_cache(
    async () => {
      try {
        // 각 회사의 입찰가 정보를 조회 - 본입찰 참여 업체들
        const vendorPrices = await db
          .select({
            companyId: biddingCompanies.companyId,
            companyName: vendors.vendorName,
            biddingCompanyId: biddingCompanies.id,
            currency: sql<string>`'KRW'`, // 기본값 KRW
            finalQuoteAmount: biddingCompanies.finalQuoteAmount,
            isBiddingParticipated: biddingCompanies.isBiddingParticipated,
          })
          .from(biddingCompanies)
          .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
          .where(and(
            eq(biddingCompanies.biddingId, biddingId),
            eq(biddingCompanies.isBiddingParticipated, true), // 본입찰 참여 업체만
            sql`${biddingCompanies.finalQuoteAmount} IS NOT NULL` // 입찰가를 제출한 업체만
          ))

        console.log(`Found ${vendorPrices.length} vendors for bidding ${biddingId}`)

        const result: any[] = []

        for (const vendor of vendorPrices) {
          try {
            // 해당 회사의 품목별 입찰가 조회 (본입찰 데이터)
            const itemPrices = await db
              .select({
                prItemId: companyPrItemBids.prItemId,
                itemName: prItemsForBidding.itemInfo, // itemInfo 사용
                itemNumber: prItemsForBidding.itemNumber, // itemNumber도 포함
                quantity: prItemsForBidding.quantity,
                quantityUnit: prItemsForBidding.quantityUnit,
                weight: prItemsForBidding.totalWeight, // totalWeight 사용
                weightUnit: prItemsForBidding.weightUnit,
                unitPrice: companyPrItemBids.bidUnitPrice,
                amount: companyPrItemBids.bidAmount,
                proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,
              })
              .from(companyPrItemBids)
              .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))
              .where(and(
                eq(companyPrItemBids.biddingCompanyId, vendor.biddingCompanyId),
                eq(companyPrItemBids.isPreQuote, false) // 본입찰 데이터만
              ))
              .orderBy(prItemsForBidding.id)

            console.log(`Vendor ${vendor.companyName}: Found ${itemPrices.length} item prices`)

            // 총 금액은 biddingCompanies.finalQuoteAmount 사용
            const totalAmount = parseFloat(vendor.finalQuoteAmount || '0')

            result.push({
              companyId: vendor.companyId,
              companyName: vendor.companyName || `Vendor ${vendor.companyId}`,
              biddingCompanyId: vendor.biddingCompanyId,
              totalAmount,
              currency: vendor.currency,
              itemPrices: itemPrices.map(item => ({
                prItemId: item.prItemId,
                itemName: item.itemName || item.itemNumber || `Item ${item.prItemId}`,
                quantity: parseFloat(item.quantity || '0'),
                quantityUnit: item.quantityUnit || 'ea',
                weight: item.weight ? parseFloat(item.weight) : null,
                weightUnit: item.weightUnit,
                unitPrice: parseFloat(item.unitPrice || '0'),
                amount: parseFloat(item.amount || '0'),
                proposedDeliveryDate: item.proposedDeliveryDate ?
                  (typeof item.proposedDeliveryDate === 'string'
                    ? item.proposedDeliveryDate
                    : item.proposedDeliveryDate.toISOString().split('T')[0])
                  : null,
              }))
            })
          } catch (vendorError) {
            console.error(`Error processing vendor ${vendor.companyId}:`, vendorError)
            // 벤더 처리 중 에러가 발생해도 다른 벤더들은 계속 처리
          }
        }

        return result
      } catch (error) {
        console.error('Failed to get vendor prices for bidding:', error)
        return []
      }
    },
    [`bidding-vendor-prices-${biddingId}`],
    {
      tags: [`bidding-${biddingId}`, 'quotation-vendors', 'pr-items']
    }
  )()
}

// 사양설명회 참여 여부 업데이트
export async function setSpecificationMeetingParticipation(biddingCompanyId: number, participated: boolean) {
  try {
    const result = await db.update(biddingCompanies)
      .set({
        isAttendingMeeting: participated,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))
      .returning({ biddingId: biddingCompanies.biddingId })

    if (result.length > 0) {
      const biddingId = result[0].biddingId
      revalidateTag(`bidding-${biddingId}`)
      revalidateTag('quotation-vendors')
      revalidatePath(`/partners/bid/${biddingId}`)
    }

    return {
      success: true,
      message: `사양설명회 참여상태가 ${participated ? '참여' : '불참'}로 업데이트되었습니다.`,
    }
  } catch (error) {
    console.error('Failed to update specification meeting participation:', error)
    return { success: false, error: '사양설명회 참여상태 업데이트에 실패했습니다.' }
  }
}