'use server'

import db from '@/db/db'
import { biddingCompanies, companyConditionResponses, biddings, prItemsForBidding, biddingDocuments, companyPrItemBids, priceAdjustmentForms } from '@/db/schema/bidding'
import { vendors } from '@/db/schema/vendors'
import { users } from '@/db/schema'
import { sendEmail } from '@/lib/mail/sendEmail'
import { eq, inArray, and } from 'drizzle-orm'
import { saveFile } from '@/lib/file-stroage'
import { downloadFile } from '@/lib/file-download'
import { revalidateTag, revalidatePath } from 'next/cache'

// 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를 그대로 반환
  }
}

interface CreateBiddingCompanyInput {
  biddingId: number
  companyId: number
  contactPerson?: string
  contactEmail?: string
  contactPhone?: string
  notes?: string
}

interface UpdateBiddingCompanyInput {
  contactPerson?: string
  contactEmail?: string
  contactPhone?: string
  preQuoteAmount?: number
  notes?: string
  invitationStatus?: 'pending' | 'accepted' | 'declined'
  isPreQuoteSelected?: boolean
  isAttendingMeeting?: boolean
}

interface PrItemQuotation {
  prItemId: number
  bidUnitPrice: number
  bidAmount: number
  proposedDeliveryDate?: string
  technicalSpecification?: string
}

interface PreQuoteDocumentUpload {
  fileName: string
  originalFileName: string
  fileSize: number
  mimeType: string
  filePath: string
}

// 사전견적용 업체 추가 - biddingCompanies와 company_condition_responses 레코드 생성
export async function createBiddingCompany(input: CreateBiddingCompanyInput) {
    try {
      const result = await db.transaction(async (tx) => {
            // 1. biddingCompanies 레코드 생성
        const biddingCompanyResult = await tx.insert(biddingCompanies).values({
          biddingId: input.biddingId,
        companyId: input.companyId,
        invitationStatus: 'pending', // 초기 상태: 입찰생성
        invitedAt: new Date(),
          contactPerson: input.contactPerson,
          contactEmail: input.contactEmail,
          contactPhone: input.contactPhone,
        notes: input.notes,
        }).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,
        // 나머지 필드들은 null로 시작 (벤더가 나중에 응답)
      })

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

// 사전견적용 업체 정보 업데이트
export async function updateBiddingCompany(id: number, input: UpdateBiddingCompanyInput) {
  try {
    const updateData: any = {
      updatedAt: new Date()
    }
    
        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.preQuoteAmount !== undefined) updateData.preQuoteAmount = input.preQuoteAmount
    if (input.notes !== undefined) updateData.notes = input.notes
    if (input.invitationStatus !== undefined) {
      updateData.invitationStatus = input.invitationStatus
      if (input.invitationStatus !== 'pending') {
        updateData.respondedAt = new Date()
      }
    }
    if (input.isPreQuoteSelected !== undefined) updateData.isPreQuoteSelected = input.isPreQuoteSelected
    if (input.isAttendingMeeting !== undefined) updateData.isAttendingMeeting = input.isAttendingMeeting

    await db.update(biddingCompanies)
            .set(updateData)
            .where(eq(biddingCompanies.id, id))

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

// 본입찰 등록 상태 업데이트 (복수 업체 선택 가능)
export async function updatePreQuoteSelection(companyIds: number[], isSelected: boolean) {
  try {
    // 업체들의 입찰 ID 조회 (캐시 무효화를 위해)
    const companies = await db
      .select({ biddingId: biddingCompanies.biddingId })
      .from(biddingCompanies)
      .where(inArray(biddingCompanies.id, companyIds))
      .limit(1)

    await db.update(biddingCompanies)
      .set({
        isPreQuoteSelected: isSelected,
        invitationStatus: 'pending', // 초기 상태: 입찰생성
        updatedAt: new Date()
      })
      .where(inArray(biddingCompanies.id, companyIds))

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

    const message = isSelected 
      ? `${companyIds.length}개 업체가 본입찰 대상으로 선정되었습니다.`
      : `${companyIds.length}개 업체의 본입찰 선정이 취소되었습니다.`

    return {
      success: true,
      message
    }
  } catch (error) {
    console.error('Failed to update pre-quote selection:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '본입찰 선정 상태 업데이트에 실패했습니다.' 
    }
  }
}

// 사전견적용 업체 삭제
export async function deleteBiddingCompany(id: number) {
  try {
    await db.transaction(async (tx) => {
      // 1. 먼저 관련된 조건 응답들 삭제
      await tx.delete(companyConditionResponses)
        .where(eq(companyConditionResponses.biddingCompanyId, id))
      
      // 2. biddingCompanies 레코드 삭제
      await tx.delete(biddingCompanies)
        .where(eq(biddingCompanies.id, id))
      })
  
      return {
        success: true,
      message: '업체가 성공적으로 삭제되었습니다.' 
    }
  } catch (error) {
    console.error('Failed to delete bidding company:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '업체 삭제에 실패했습니다.' 
    }
  }
}

// 특정 입찰의 참여 업체 목록 조회 (company_condition_responses와 vendors 조인)
export async function getBiddingCompanies(biddingId: number) {
  try {
    const companies = await db
      .select({
        // bidding_companies 필드들
        id: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        companyId: biddingCompanies.companyId,
        invitationStatus: biddingCompanies.invitationStatus,
        invitedAt: biddingCompanies.invitedAt,
        respondedAt: biddingCompanies.respondedAt,
        preQuoteAmount: biddingCompanies.preQuoteAmount,
        preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt,
        preQuoteDeadline: biddingCompanies.preQuoteDeadline,
        isPreQuoteSelected: biddingCompanies.isPreQuoteSelected,
        isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated,
        isAttendingMeeting: biddingCompanies.isAttendingMeeting,
        notes: biddingCompanies.notes,
        contactPerson: biddingCompanies.contactPerson,
        contactEmail: biddingCompanies.contactEmail,
        contactPhone: biddingCompanies.contactPhone,
        createdAt: biddingCompanies.createdAt,
        updatedAt: biddingCompanies.updatedAt,
        
        // vendors 테이블에서 업체 정보
        companyName: vendors.vendorName,
        companyCode: vendors.vendorCode,
        
        // company_condition_responses 필드들
        paymentTermsResponse: companyConditionResponses.paymentTermsResponse,
        taxConditionsResponse: companyConditionResponses.taxConditionsResponse,
        proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate,
        priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse,
        isInitialResponse: companyConditionResponses.isInitialResponse,
        incotermsResponse: companyConditionResponses.incotermsResponse,
        proposedShippingPort: companyConditionResponses.proposedShippingPort,
        proposedDestinationPort: companyConditionResponses.proposedDestinationPort,
        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))

    return {
      success: true,
      data: companies
    }
  } catch (error) {
    console.error('Failed to get bidding companies:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '업체 목록 조회에 실패했습니다.' 
    }
  }
}

// 선택된 업체들에게 사전견적 초대 발송
export async function sendPreQuoteInvitations(companyIds: number[], preQuoteDeadline?: Date | string) {
  try {
    if (companyIds.length === 0) {
      return {
        success: false,
        error: '선택된 업체가 없습니다.'
      }
    }

    // 선택된 업체들의 정보와 입찰 정보 조회
    const companiesInfo = await db
      .select({
        biddingCompanyId: biddingCompanies.id,
        companyId: biddingCompanies.companyId,
        biddingId: biddingCompanies.biddingId,
        companyName: vendors.vendorName,
        companyEmail: vendors.email,
        // 입찰 정보
        biddingNumber: biddings.biddingNumber,
        revision: biddings.revision,
        projectName: biddings.projectName,
        biddingTitle: biddings.title,
        itemName: biddings.itemName,
        preQuoteDate: biddings.preQuoteDate,
        budget: biddings.budget,
        currency: biddings.currency,
        managerName: biddings.managerName,
        managerEmail: biddings.managerEmail,
        managerPhone: biddings.managerPhone,
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .leftJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
      .where(inArray(biddingCompanies.id, companyIds))

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

    await db.transaction(async (tx) => {
      // 선택된 업체들의 상태를 '사전견적요청(초대발송)'으로 변경
      for (const id of companyIds) {
        await tx.update(biddingCompanies)
          .set({
            invitationStatus: 'sent', // 사전견적 초대 발송 상태
            invitedAt: new Date(),
            preQuoteDeadline: preQuoteDeadline ? new Date(preQuoteDeadline) : null,
            updatedAt: new Date()
          })
          .where(eq(biddingCompanies.id, id))
      }
    })

    // 각 업체별로 이메일 발송
    for (const company of companiesInfo) {
      if (company.companyEmail) {
        try {
          await sendEmail({
            to: company.companyEmail,
            template: 'pre-quote-invitation',
            context: {
              companyName: company.companyName,
              biddingNumber: company.biddingNumber,
              revision: company.revision,
              projectName: company.projectName,
              biddingTitle: company.biddingTitle,
              itemName: company.itemName,
              preQuoteDate: company.preQuoteDate ? new Date(company.preQuoteDate).toLocaleDateString() : null,
              budget: company.budget ? company.budget.toLocaleString() : null,
              currency: company.currency,
              managerName: company.managerName,
              managerEmail: company.managerEmail,
              managerPhone: company.managerPhone,
              loginUrl: `${process.env.NEXT_PUBLIC_APP_URL}/partners/bid/${company.biddingId}/pre-quote`,
              currentYear: new Date().getFullYear(),
              language: 'ko'
            }
          })
        } catch (emailError) {
          console.error(`Failed to send email to ${company.companyEmail}:`, emailError)
          // 이메일 발송 실패해도 전체 프로세스는 계속 진행
        }
      }
    }
          // 3. 입찰 상태를 사전견적 요청으로 변경 (bidding_generated 상태에서만)
          for (const company of companiesInfo) {
            await db.transaction(async (tx) => {
            await tx
              .update(biddings)
              .set({
                status: 'request_for_quotation',
                updatedAt: new Date()
              })
              .where(and(
                eq(biddings.id, company.biddingId),
                eq(biddings.status, 'bidding_generated')
              ))
            })
          }
    return {
      success: true,
      message: `${companyIds.length}개 업체에 사전견적 초대를 발송했습니다.`
      }
    } catch (error) {
    console.error('Failed to send pre-quote invitations:', error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : '초대 발송에 실패했습니다.' 
    }
  }
}

// Partners에서 특정 업체의 입찰 정보 조회 (사전견적 단계)
export async function getBiddingCompaniesForPartners(biddingId: number, companyId: number) {
  try {
    // 1. 먼저 입찰 기본 정보를 가져옴
    const biddingResult = 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,
      })
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

    const biddingData = biddingResult[0]

    // 2. 해당 업체의 biddingCompanies 정보 조회
    const companyResult = await db
      .select({
        biddingCompanyId: biddingCompanies.id,
        biddingId: biddingCompanies.biddingId,
        invitationStatus: biddingCompanies.invitationStatus,
        preQuoteAmount: biddingCompanies.preQuoteAmount,
        preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt,
        preQuoteDeadline: biddingCompanies.preQuoteDeadline,
        isPreQuoteSelected: biddingCompanies.isPreQuoteSelected,
        isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated,
        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,
        isInitialResponse: companyConditionResponses.isInitialResponse,
        additionalProposals: companyConditionResponses.additionalProposals,
      })
      .from(biddingCompanies)
      .leftJoin(
        companyConditionResponses, 
        eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId)
      )
      .where(
        and(
          eq(biddingCompanies.biddingId, biddingId),
          eq(biddingCompanies.companyId, companyId)
        )
      )
      .limit(1)

    // 3. 결과 조합
    if (companyResult.length === 0) {
      // 아직 초대되지 않은 상태
      return {
        ...biddingData,
        biddingCompanyId: null,
        biddingId: biddingData.id,
        invitationStatus: null,
        preQuoteAmount: null,
        preQuoteSubmittedAt: null,
        preQuoteDeadline: null,
        isPreQuoteSelected: false,
        isPreQuoteParticipated: null,
        isAttendingMeeting: null,
        paymentTermsResponse: null,
        taxConditionsResponse: null,
        incotermsResponse: null,
        proposedContractDeliveryDate: null,
        proposedShippingPort: null,
        proposedDestinationPort: null,
        priceAdjustmentResponse: null,
        sparePartResponse: null,
        isInitialResponse: null,
        additionalProposals: null,
      }
    }

    const companyData = companyResult[0]

    return {
      ...biddingData,
      ...companyData,
      biddingId: biddingData.id, // bidding ID 보장
    }
  } catch (error) {
    console.error('Failed to get bidding companies for partners:', error)
    throw error
  }
}

// Partners에서 사전견적 응답 제출
export async function submitPreQuoteResponse(
  biddingCompanyId: number,
  responseData: {
    preQuoteAmount?: number // 품목별 계산에서 자동으로 계산되므로 optional
    prItemQuotations?: PrItemQuotation[] // 품목별 견적 정보 추가
    paymentTermsResponse?: string
    taxConditionsResponse?: string
    incotermsResponse?: string
    proposedContractDeliveryDate?: string
    proposedShippingPort?: string
    proposedDestinationPort?: string
    priceAdjustmentResponse?: boolean
    isInitialResponse?: boolean
    sparePartResponse?: string
    additionalProposals?: string
    priceAdjustmentForm?: any
  },
  userId: string
) {
  try {
    let finalAmount = responseData.preQuoteAmount || 0
    
    await db.transaction(async (tx) => {
      // 1. 품목별 견적 정보 최종 저장 (사전견적 제출)
      if (responseData.prItemQuotations && responseData.prItemQuotations.length > 0) {
        // 기존 사전견적 품목 삭제 후 새로 생성
        await tx.delete(companyPrItemBids)
          .where(
            and(
              eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
              eq(companyPrItemBids.isPreQuote, true)
            )
          )

        // 품목별 견적 최종 저장
        for (const item of responseData.prItemQuotations) {
          await tx.insert(companyPrItemBids)
            .values({
              biddingCompanyId,
              prItemId: item.prItemId,
              bidUnitPrice: item.bidUnitPrice.toString(),
              bidAmount: item.bidAmount.toString(),
              proposedDeliveryDate: item.proposedDeliveryDate || null,
              technicalSpecification: item.technicalSpecification || null,
              currency: 'KRW',
              isPreQuote: true,
              submittedAt: new Date(),
              createdAt: new Date(),
              updatedAt: new Date()
            })
        }

        // 총 금액 다시 계산
        finalAmount = responseData.prItemQuotations.reduce((sum, item) => sum + item.bidAmount, 0)
      }

      // 2. biddingCompanies 업데이트 (사전견적 금액, 제출 시간, 상태 변경)
      await tx.update(biddingCompanies)
        .set({
          preQuoteAmount: finalAmount.toString(),
          preQuoteSubmittedAt: new Date(),
          invitationStatus: 'submitted', // 사전견적 제출 완료 상태로 변경
          updatedAt: new Date()
        })
        .where(eq(biddingCompanies.id, biddingCompanyId))

      // 3. company_condition_responses 업데이트
      const finalConditionResult = await tx.update(companyConditionResponses)
        .set({
          paymentTermsResponse: responseData.paymentTermsResponse,
          taxConditionsResponse: responseData.taxConditionsResponse,
          incotermsResponse: responseData.incotermsResponse,
          proposedContractDeliveryDate: responseData.proposedContractDeliveryDate,
          proposedShippingPort: responseData.proposedShippingPort,
          proposedDestinationPort: responseData.proposedDestinationPort,
          priceAdjustmentResponse: responseData.priceAdjustmentResponse,
          isInitialResponse: responseData.isInitialResponse,
          sparePartResponse: responseData.sparePartResponse,
          additionalProposals: responseData.additionalProposals,
          updatedAt: new Date()
        })
        .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId))
        .returning()

      // 4. 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우)
      if (responseData.priceAdjustmentResponse && responseData.priceAdjustmentForm && finalConditionResult.length > 0) {
        const companyConditionResponseId = finalConditionResult[0].id
        
        const priceAdjustmentData = {
          companyConditionResponsesId: companyConditionResponseId,
          itemName: responseData.priceAdjustmentForm.itemName,
          adjustmentReflectionPoint: responseData.priceAdjustmentForm.adjustmentReflectionPoint,
          majorApplicableRawMaterial: responseData.priceAdjustmentForm.majorApplicableRawMaterial,
          adjustmentFormula: responseData.priceAdjustmentForm.adjustmentFormula,
          rawMaterialPriceIndex: responseData.priceAdjustmentForm.rawMaterialPriceIndex,
          referenceDate: responseData.priceAdjustmentForm.referenceDate as string || null,
          comparisonDate: responseData.priceAdjustmentForm.comparisonDate as string || null,
          adjustmentRatio: responseData.priceAdjustmentForm.adjustmentRatio || null,
          notes: responseData.priceAdjustmentForm.notes,
          adjustmentConditions: responseData.priceAdjustmentForm.adjustmentConditions,
          majorNonApplicableRawMaterial: responseData.priceAdjustmentForm.majorNonApplicableRawMaterial,
          adjustmentPeriod: responseData.priceAdjustmentForm.adjustmentPeriod,
          contractorWriter: responseData.priceAdjustmentForm.contractorWriter,
          adjustmentDate: responseData.priceAdjustmentForm.adjustmentDate as string || null,
          nonApplicableReason: responseData.priceAdjustmentForm.nonApplicableReason,
        } as any

        // 기존 연동제 정보가 있는지 확인
        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)
        }
      }

      // 5. 입찰 상태를 사전견적 접수로 변경 (request_for_quotation 상태에서만)
      // 또한 사전견적 접수일 업데이트
      const biddingCompany = await tx
        .select({ biddingId: biddingCompanies.biddingId })
        .from(biddingCompanies)
        .where(eq(biddingCompanies.id, biddingCompanyId))
        .limit(1)

      if (biddingCompany.length > 0) {
        await tx
          .update(biddings)
          .set({
            status: 'received_quotation',
            preQuoteDate: new Date().toISOString().split('T')[0], // 사전견적 접수일 업데이트
            updatedAt: new Date()
          })
          .where(and(
            eq(biddings.id, biddingCompany[0].biddingId),
            eq(biddings.status, 'request_for_quotation')
          ))
      }
    })

    return {
      success: true,
      message: '사전견적이 성공적으로 제출되었습니다.'
    }
    } catch (error) {
    console.error('Failed to submit pre-quote response:', error)
    return {
      success: false,
            error: error instanceof Error ? error.message : '사전견적 제출에 실패했습니다.'
    }
  }
}

// Partners에서 사전견적 참여 의사 결정 (수락/거절)
export async function respondToPreQuoteInvitation(
  biddingCompanyId: number,
  response: 'accepted' | 'declined'
) {
  try {
    await db.update(biddingCompanies)
      .set({
        invitationStatus: response, // accepted 또는 declined
        respondedAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))

    const message = response === 'accepted' ? 
      '사전견적 참여를 수락했습니다.' : 
      '사전견적 참여를 거절했습니다.'

    return {
      success: true,
      message
    }
  } catch (error) {
    console.error('Failed to respond to pre-quote invitation:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '응답 처리에 실패했습니다.'
    }
  }
}

// 벤더에서 사전견적 참여 여부 결정 (isPreQuoteSelected, isPreQuoteParticipated 사용)
export async function setPreQuoteParticipation(
  biddingCompanyId: number,
  isParticipating: boolean
) {
  try {
    await db.update(biddingCompanies)
      .set({
        isPreQuoteParticipated: isParticipating,
        isPreQuoteSelected: isParticipating,
        respondedAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))

    const message = isParticipating ? 
      '사전견적 참여를 확정했습니다. 이제 견적서를 작성하실 수 있습니다.' : 
      '사전견적 참여를 거절했습니다.'

    return {
      success: true,
      message
    }
  } catch (error) {
    console.error('Failed to set pre-quote participation:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '참여 의사 처리에 실패했습니다.'
    }
  }
}

// PR 아이템 조회 (입찰에 포함된 품목들)
export async function getPrItemsForBidding(biddingId: number) {
  try {
    const prItems = await db
      .select({
        id: prItemsForBidding.id,
        itemNumber: prItemsForBidding.itemNumber,
        prNumber: prItemsForBidding.prNumber,
        itemInfo: prItemsForBidding.itemInfo,
        materialDescription: prItemsForBidding.materialDescription,
        quantity: prItemsForBidding.quantity,
        quantityUnit: prItemsForBidding.quantityUnit,
        totalWeight: prItemsForBidding.totalWeight,
        weightUnit: prItemsForBidding.weightUnit,
        currency: prItemsForBidding.currency,
        requestedDeliveryDate: prItemsForBidding.requestedDeliveryDate,
        hasSpecDocument: prItemsForBidding.hasSpecDocument
      })
      .from(prItemsForBidding)
      .where(eq(prItemsForBidding.biddingId, biddingId))

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

// SPEC 문서 조회 (PR 아이템에 연결된 문서들)
export async function getSpecDocumentsForPrItem(prItemId: number) {
  try {
    
    const specDocs = await db
      .select({
        id: biddingDocuments.id,
        fileName: biddingDocuments.fileName,
        originalFileName: biddingDocuments.originalFileName,
        fileSize: biddingDocuments.fileSize,
        filePath: biddingDocuments.filePath,
        title: biddingDocuments.title,
        description: biddingDocuments.description,
        uploadedAt: biddingDocuments.uploadedAt
      })
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.prItemId, prItemId),
          eq(biddingDocuments.documentType, 'spec_document')
        )
      )

    return specDocs
  } catch (error) {
    console.error('Failed to get spec documents for PR item:', error)
    return []
  }
}

// 사전견적 임시저장
export async function savePreQuoteDraft(
  biddingCompanyId: number,
  responseData: {
    prItemQuotations?: PrItemQuotation[]
    paymentTermsResponse?: string
    taxConditionsResponse?: string
    incotermsResponse?: string
    proposedContractDeliveryDate?: string
    proposedShippingPort?: string
    proposedDestinationPort?: string
    priceAdjustmentResponse?: boolean
    isInitialResponse?: boolean
    sparePartResponse?: string
    additionalProposals?: string
    priceAdjustmentForm?: any
  },
  userId: string
) {
  try {
    let totalAmount = 0
    
    await db.transaction(async (tx) => {
      // 품목별 견적 정보 저장
      if (responseData.prItemQuotations && responseData.prItemQuotations.length > 0) {
        // 기존 사전견적 품목 삭제 (임시저장 시 덮어쓰기)
        await tx.delete(companyPrItemBids)
          .where(
            and(
              eq(companyPrItemBids.biddingCompanyId, biddingCompanyId),
              eq(companyPrItemBids.isPreQuote, true)
            )
          )

        // 새로운 품목별 견적 저장
        for (const item of responseData.prItemQuotations) {
          await tx.insert(companyPrItemBids)
            .values({
              biddingCompanyId,
              prItemId: item.prItemId,
              bidUnitPrice: item.bidUnitPrice.toString(),
              bidAmount: item.bidAmount.toString(),
              proposedDeliveryDate: item.proposedDeliveryDate || null,
              technicalSpecification: item.technicalSpecification || null,
              currency: 'KRW',
              isPreQuote: true, // 사전견적 표시
              submittedAt: new Date(),
              createdAt: new Date(),
              updatedAt: new Date()
            })
        }

        // 총 금액 계산
        totalAmount = responseData.prItemQuotations.reduce((sum, item) => sum + item.bidAmount, 0)
        
        // biddingCompanies에 총 금액 임시 저장 (status는 변경하지 않음)
        await tx.update(biddingCompanies)
          .set({
            preQuoteAmount: totalAmount.toString(),
            updatedAt: new Date()
          })
          .where(eq(biddingCompanies.id, biddingCompanyId))
      }

      // company_condition_responses 업데이트 (임시저장)
      const conditionResult = await tx.update(companyConditionResponses)
        .set({
          paymentTermsResponse: responseData.paymentTermsResponse || null,
          taxConditionsResponse: responseData.taxConditionsResponse || null,
          incotermsResponse: responseData.incotermsResponse || null,
          proposedContractDeliveryDate: responseData.proposedContractDeliveryDate || null,
          proposedShippingPort: responseData.proposedShippingPort || null,
          proposedDestinationPort: responseData.proposedDestinationPort || null,
          priceAdjustmentResponse: responseData.priceAdjustmentResponse || null,
          isInitialResponse: responseData.isInitialResponse || null,
          sparePartResponse: responseData.sparePartResponse || null,
          additionalProposals: responseData.additionalProposals || null,
          updatedAt: new Date()
        })
        .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId))
        .returning()

      // 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우)
      if (responseData.priceAdjustmentResponse && responseData.priceAdjustmentForm && conditionResult.length > 0) {
        const companyConditionResponseId = conditionResult[0].id
        
        const priceAdjustmentData = {
          companyConditionResponsesId: companyConditionResponseId,
          itemName: responseData.priceAdjustmentForm.itemName,
          adjustmentReflectionPoint: responseData.priceAdjustmentForm.adjustmentReflectionPoint,
          majorApplicableRawMaterial: responseData.priceAdjustmentForm.majorApplicableRawMaterial,
          adjustmentFormula: responseData.priceAdjustmentForm.adjustmentFormula,
          rawMaterialPriceIndex: responseData.priceAdjustmentForm.rawMaterialPriceIndex,
          referenceDate: responseData.priceAdjustmentForm.referenceDate as string || null,
          comparisonDate: responseData.priceAdjustmentForm.comparisonDate as string || null,
          adjustmentRatio: responseData.priceAdjustmentForm.adjustmentRatio || null,
          notes: responseData.priceAdjustmentForm.notes,
          adjustmentConditions: responseData.priceAdjustmentForm.adjustmentConditions,
          majorNonApplicableRawMaterial: responseData.priceAdjustmentForm.majorNonApplicableRawMaterial,
          adjustmentPeriod: responseData.priceAdjustmentForm.adjustmentPeriod,
          contractorWriter: responseData.priceAdjustmentForm.contractorWriter,
          adjustmentDate: responseData.priceAdjustmentForm.adjustmentDate as string || null,
          nonApplicableReason: responseData.priceAdjustmentForm.nonApplicableReason,
        } as any

        // 기존 연동제 정보가 있는지 확인
        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)
        }
      }
    })

    return {
      success: true,
      message: '임시저장이 완료되었습니다.',
      totalAmount
    }
  } catch (error) {
    console.error('Failed to save pre-quote draft:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '임시저장에 실패했습니다.'
    }
  }
}

// 견적 문서 업로드
export async function uploadPreQuoteDocument(
  biddingId: number,
  companyId: number,
  file: File,
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)
    // 파일 저장
    const saveResult = await saveFile({
      file,
      directory: `bidding/${biddingId}/quotations`,
      originalName: file.name,
      userId
    })

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

    // 데이터베이스에 문서 정보 저장
    const result = await db.insert(biddingDocuments)
      .values({
        biddingId,
        companyId,
        documentType: 'other', // 견적서 타입
        fileName: saveResult.fileName!,
        originalFileName: file.name,
        fileSize: file.size,
        mimeType: file.type,
        filePath: saveResult.publicPath!, // publicPath 사용 (웹 접근 가능한 경로)
        title: `견적서 - ${file.name}`,
        description: '협력업체 제출 견적서',
        isPublic: false,
        isRequired: false,
        uploadedBy: userName,
        uploadedAt: new Date()
      })
      .returning()

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

// 업로드된 견적 문서 목록 조회
export async function getPreQuoteDocuments(biddingId: number, companyId: number) {
  try {
    const documents = await db
      .select({
        id: biddingDocuments.id,
        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.companyId, companyId),
        )
      )

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

// 저장된 품목별 견적 조회 (임시저장/기존 데이터 불러오기용)
export async function getSavedPrItemQuotations(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, true)
        )
      )

    // 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 saved PR item quotations:', error)
    return []
    }
  }

// 견적 문서 정보 조회 (다운로드용)
export async function getPreQuoteDocumentForDownload(
  documentId: number,
  biddingId: number,
  companyId: number
) {
  try {
    const document = await db
      .select({
        fileName: biddingDocuments.fileName,
        originalFileName: biddingDocuments.originalFileName,
        filePath: biddingDocuments.filePath
      })
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.id, documentId),
          eq(biddingDocuments.biddingId, biddingId),
          eq(biddingDocuments.companyId, companyId),
          eq(biddingDocuments.documentType, 'other')
        )
      )
      .limit(1)

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

    return {
      success: true,
      document: document[0]
    }
  } catch (error) {
    console.error('Failed to get pre-quote document:', error)
    return {
      success: false,
      error: '문서 정보 조회에 실패했습니다.'
    }
  }
}

// 견적 문서 삭제
export async function deletePreQuoteDocument(
  documentId: number,
  biddingId: number,
  companyId: number,
  userId: string
) {
  try {
    // 문서 존재 여부 및 권한 확인
    const document = await db
      .select({
        id: biddingDocuments.id,
        fileName: biddingDocuments.fileName,
        filePath: biddingDocuments.filePath,
        uploadedBy: biddingDocuments.uploadedBy
      })
      .from(biddingDocuments)
      .where(
        and(
          eq(biddingDocuments.id, documentId),
          eq(biddingDocuments.biddingId, biddingId),
          eq(biddingDocuments.companyId, companyId),
          eq(biddingDocuments.documentType, 'other')
        )
      )
      .limit(1)

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

    const doc = document[0]

    // 데이터베이스에서 문서 정보 삭제
    await db
      .delete(biddingDocuments)
      .where(eq(biddingDocuments.id, documentId))

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