'use server'

import db from '@/db/db'
import {
    biddings,
    biddingNoticeTemplate,
    projects,
    biddingDocuments,
    prItemsForBidding,
    specificationMeetings,
    prDocuments,
    biddingConditions,
    users,
    basicContractTemplates,
    vendorsWithTypesView,
    biddingCompanies,
    biddingCompaniesContacts,
    vendorContacts,
    vendors
} from '@/db/schema'
import {
    eq,
    desc,
    asc,
    and,
    or,
    count,
    sql,
    ilike,
    gte,
    lte,
    SQL,
    like,
    notInArray,
    inArray,
    isNull
} from 'drizzle-orm'
import { revalidatePath } from 'next/cache'
import { filterColumns } from '@/lib/filter-columns'
import { GetBiddingsSchema, CreateBiddingSchema } from './validation'



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

    return user[0]?.userCode || null
  } catch (error) {
    console.error('Failed to get user code by email:', error)
    return null
  }
}
import { saveFile } from '../file-stroage'

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


export async function getBiddingNoticeTemplates() {
    try {
        const result = await db
            .select()
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.isTemplate, true))
            .orderBy(desc(biddingNoticeTemplate.updatedAt))

        // 타입별로 그룹화하여 반환
        const templates = result.reduce((acc, template) => {
            acc[template.type] = template
            return acc
        }, {} as Record<string, typeof result[0]>)

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

export async function getBiddingNoticeTemplate(type?: string) {
    try {
        let query = db
            .select()
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.isTemplate, true))
            .orderBy(desc(biddingNoticeTemplate.updatedAt))

        if (type) {
            query = query.where(eq(biddingNoticeTemplate.type, type))
        }

        const result = await query.limit(1)

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

export async function getBiddingNotice(biddingId: number) {
    try {
        const result = await db
            .select({
                id: biddingNoticeTemplate.id,
                biddingId: biddingNoticeTemplate.biddingId,
                title: biddingNoticeTemplate.title,
                content: biddingNoticeTemplate.content,
                isTemplate: biddingNoticeTemplate.isTemplate,
                createdAt: biddingNoticeTemplate.createdAt,
                updatedAt: biddingNoticeTemplate.updatedAt,
            })
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.biddingId, biddingId))
            .limit(1)

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

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

        // 기존 입찰공고 확인
        const existing = await db
            .select()
            .from(biddingNoticeTemplate)
            .where(eq(biddingNoticeTemplate.biddingId, biddingId))
            .limit(1)

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

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

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

        // 기존 동일 타입의 템플릿 확인
        const existing = await db
            .select()
            .from(biddingNoticeTemplate)
            .where(and(
                eq(biddingNoticeTemplate.isTemplate, true),
                eq(biddingNoticeTemplate.type, type)
            ))
            .limit(1)

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

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


export async function getBiddings(input: GetBiddingsSchema) {
    try {
        const offset = (input.page - 1) * input.perPage

        console.log(input.filters)
        console.log(input.sort)

        // ✅ 1) 고급 필터 조건
        let advancedWhere: SQL<unknown> | undefined = undefined
        if (input.filters && input.filters.length > 0) {
            advancedWhere = filterColumns({
                table: biddings,
                filters: input.filters,
                joinOperator: input.joinOperator || 'and',
            })
        }

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

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

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

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

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

        if (input.purchasingOrganization) {
            basicConditions.push(ilike(biddings.purchasingOrganization, `%${input.purchasingOrganization}%`))
        }

        // 담당자 필터 (bidPicId 또는 supplyPicId로 검색)
        if (input.managerName) {
            // managerName으로 검색 시 bidPic 또는 supplyPic의 이름으로 검색
            basicConditions.push(
                or(
                    ilike(biddings.bidPicName, `%${input.managerName}%`),
                    ilike(biddings.supplyPicName, `%${input.managerName}%`)
                )!
            )
        }

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

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

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

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

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

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

        const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

        // ✅ 3) 글로벌 검색 조건
        let globalWhere: SQL<unknown> | undefined = undefined
        if (input.search) {
            const s = `%${input.search}%`
            const searchConditions = [
                ilike(biddings.biddingNumber, s),
                ilike(biddings.title, s),
                ilike(biddings.projectName, s),
                ilike(biddings.itemName, s),
                ilike(biddings.purchasingOrganization, s),
                ilike(biddings.bidPicName, s),
                ilike(biddings.supplyPicName, s),
                ilike(biddings.prNumber, s),
                ilike(biddings.remarks, s),
            ]
            globalWhere = or(...searchConditions)
        }

        // ✅ 4) 최종 WHERE 조건
        const whereConditions: SQL<unknown>[] = []
        if (advancedWhere) whereConditions.push(advancedWhere)
        if (basicWhere) whereConditions.push(basicWhere)
        if (globalWhere) whereConditions.push(globalWhere)

        const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined

        // ✅ 5) 전체 개수 조회
        const totalResult = await db
            .select({ count: count() })
            .from(biddings)
            .where(finalWhere)

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

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

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

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

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

        // ✅ 7) 메인 쿼리 - 이제 조인이 필요함!
        const data = await db
            .select({
                // 기본 입찰 정보
                id: biddings.id,
                biddingNumber: biddings.biddingNumber,
                originalBiddingNumber: biddings.originalBiddingNumber,
                revision: biddings.revision,
                projectName: biddings.projectName,
                itemName: biddings.itemName,
                title: biddings.title,
                description: biddings.description,
                biddingSourceType: biddings.biddingSourceType,
                isUrgent: biddings.isUrgent,

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

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

                // 회의 및 문서
                hasSpecificationMeeting: biddings.hasSpecificationMeeting,
                hasPrDocument: biddings.hasPrDocument,
                prNumber: biddings.prNumber,

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

                // 상태 및 담당자
                status: biddings.status,
                isPublic: biddings.isPublic,
                purchasingOrganization: biddings.purchasingOrganization,
                bidPicId: biddings.bidPicId,
                bidPicName: biddings.bidPicName,
                supplyPicId: biddings.supplyPicId,
                supplyPicName: biddings.supplyPicName,

                // 메타 정보
                remarks: biddings.remarks,
                createdBy: biddings.createdBy,
                createdAt: biddings.createdAt,
                updatedAt: biddings.updatedAt,
                updatedBy: biddings.updatedBy,

                // 사양설명회 상세 정보
                hasSpecificationMeetingDetails: sql<boolean>`${specificationMeetings.id} IS NOT NULL`.as('has_specification_meeting_details'),
                meetingDate: specificationMeetings.meetingDate,
                meetingLocation: specificationMeetings.location,
                meetingContactPerson: specificationMeetings.contactPerson,
                meetingIsRequired: specificationMeetings.isRequired,

                // PR 문서 집계
                prDocumentCount: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM pr_documents
                    WHERE bidding_id = ${biddings.id}
                  ), 0)
                `.as('pr_document_count'),

                prDocumentNames: sql<string[]>`
                  (
                    SELECT array_agg(document_name ORDER BY registered_at DESC)
                    FROM pr_documents
                    WHERE bidding_id = ${biddings.id}
                    LIMIT 5
                  )
                `.as('pr_document_names'),

                // 참여 현황 집계 (전체)
                participantExpected: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                  ), 0)
                `.as('participant_expected'),

                // === 사전견적 참여 현황 ===
                preQuotePending: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status IN ('pending', 'pre_quote_sent')
                  ), 0)
                `.as('pre_quote_pending'),

                preQuoteAccepted: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'pre_quote_accepted'
                  ), 0)
                `.as('pre_quote_accepted'),

                preQuoteDeclined: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'pre_quote_declined'
                  ), 0)
                `.as('pre_quote_declined'),

                preQuoteSubmitted: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'pre_quote_submitted'
                  ), 0)
                `.as('pre_quote_submitted'),

                // === 입찰 참여 현황 ===
                biddingPending: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'bidding_sent'
                  ), 0)
                `.as('bidding_pending'),

                biddingAccepted: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'bidding_accepted'
                  ), 0)
                `.as('bidding_accepted'),

                biddingDeclined: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'bidding_declined'
                  ), 0)
                `.as('bidding_declined'),

                biddingCancelled: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'bidding_cancelled'
                  ), 0)
                `.as('bidding_cancelled'),

                biddingSubmitted: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'bidding_submitted'
                  ), 0)
                `.as('bidding_submitted'),

                // === 호환성을 위한 기존 컬럼 (사전견적 기준) ===
                participantParticipated: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status = 'pre_quote_submitted'
                  ), 0)
                `.as('participant_participated'),

                participantDeclined: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status IN ('pre_quote_declined', 'bidding_declined')
                  ), 0)
                `.as('participant_declined'),

                participantPending: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status IN ('pending', 'pre_quote_sent', 'bidding_sent')
                  ), 0)
                `.as('participant_pending'),

                participantAccepted: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND invitation_status IN ('pre_quote_accepted', 'bidding_accepted')
                  ), 0)
                `.as('participant_accepted'),

                // 참여율 계산 (입찰 기준 - 응찰 완료 / 전체)
                participationRate: sql<number>`
                  CASE
                    WHEN (
                      SELECT count(*)
                      FROM bidding_companies
                      WHERE bidding_id = ${biddings.id}
                    ) > 0
                    THEN ROUND(
                      (
                        SELECT count(*)::decimal
                        FROM bidding_companies
                        WHERE bidding_id = ${biddings.id}
                        AND invitation_status = 'bidding_submitted'
                      ) / (
                        SELECT count(*)::decimal
                        FROM bidding_companies
                        WHERE bidding_id = ${biddings.id}
                      ) * 100, 1
                    )
                    ELSE 0
                  END
                `.as('participation_rate'),

                // 견적 금액 통계
                avgPreQuoteAmount: sql<number>`
                  (
                    SELECT AVG(pre_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND pre_quote_amount IS NOT NULL
                  )
                `.as('avg_pre_quote_amount'),

                minPreQuoteAmount: sql<number>`
                  (
                    SELECT MIN(pre_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND pre_quote_amount IS NOT NULL
                  )
                `.as('min_pre_quote_amount'),

                maxPreQuoteAmount: sql<number>`
                  (
                    SELECT MAX(pre_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND pre_quote_amount IS NOT NULL
                  )
                `.as('max_pre_quote_amount'),

                avgFinalQuoteAmount: sql<number>`
                  (
                    SELECT AVG(final_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND final_quote_amount IS NOT NULL
                  )
                `.as('avg_final_quote_amount'),

                minFinalQuoteAmount: sql<number>`
                  (
                    SELECT MIN(final_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND final_quote_amount IS NOT NULL
                  )
                `.as('min_final_quote_amount'),

                maxFinalQuoteAmount: sql<number>`
                  (
                    SELECT MAX(final_quote_amount)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND final_quote_amount IS NOT NULL
                  )
                `.as('max_final_quote_amount'),

                // 선정 및 낙찰 정보
                selectedForFinalBidCount: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND is_pre_quote_selected = true
                  ), 0)
                `.as('selected_for_final_bid_count'),

                winnerCount: sql<number>`
                  COALESCE((
                    SELECT count(*)
                    FROM bidding_companies
                    WHERE bidding_id = ${biddings.id}
                    AND is_winner = true
                  ), 0)
                `.as('winner_count'),

                winnerCompanyNames: sql<string[]>`
                  (
                    SELECT array_agg(v.vendor_name ORDER BY v.vendor_name)
                    FROM bidding_companies bc
                    JOIN vendors v ON bc.company_id = v.id
                    WHERE bc.bidding_id = ${biddings.id}
                    AND bc.is_winner = true
                  )
                `.as('winner_company_names'),

                // 일정 상태 계산
                submissionStatus: sql<string>`
                  CASE
                    WHEN ${biddings.submissionStartDate} IS NULL OR ${biddings.submissionEndDate} IS NULL
                    THEN 'not_scheduled'
                    WHEN NOW() < ${biddings.submissionStartDate}
                    THEN 'scheduled'
                    WHEN NOW() BETWEEN ${biddings.submissionStartDate} AND ${biddings.submissionEndDate}
                    THEN 'active'
                    WHEN NOW() > ${biddings.submissionEndDate}
                    THEN 'closed'
                    ELSE 'unknown'
                  END
                `.as('submission_status'),

                // 마감까지 남은 일수
                daysUntilDeadline: sql<number>`
                  CASE
                    WHEN ${biddings.submissionEndDate} IS NOT NULL
                    AND NOW() < ${biddings.submissionEndDate}
                    THEN EXTRACT(DAYS FROM (${biddings.submissionEndDate} - NOW()))::integer
                    ELSE NULL
                  END
                `.as('days_until_deadline'),

                // 시작까지 남은 일수
                daysUntilStart: sql<number>`
                  CASE
                    WHEN ${biddings.submissionStartDate} IS NOT NULL
                    AND NOW() < ${biddings.submissionStartDate}
                    THEN EXTRACT(DAYS FROM (${biddings.submissionStartDate} - NOW()))::integer
                    ELSE NULL
                  END
                `.as('days_until_start'),

                // 예산 대비 최저 견적 비율
                budgetEfficiencyRate: sql<number>`
                  CASE
                    WHEN ${biddings.budget} IS NOT NULL AND ${biddings.budget} > 0
                    AND (
                      SELECT MIN(final_quote_amount)
                      FROM bidding_companies
                      WHERE bidding_id = ${biddings.id}
                      AND final_quote_amount IS NOT NULL
                    ) IS NOT NULL
                    THEN ROUND(
                      (
                        SELECT MIN(final_quote_amount)
                        FROM bidding_companies
                        WHERE bidding_id = ${biddings.id}
                        AND final_quote_amount IS NOT NULL
                      ) / ${biddings.budget} * 100, 1
                    )
                    ELSE NULL
                  END
                `.as('budget_efficiency_rate'),

                // 내정가 대비 최저 견적 비율
                targetPriceEfficiencyRate: sql<number>`
                  CASE
                    WHEN ${biddings.targetPrice} IS NOT NULL AND ${biddings.targetPrice} > 0
                    AND (
                      SELECT MIN(final_quote_amount)
                      FROM bidding_companies
                      WHERE bidding_id = ${biddings.id}
                      AND final_quote_amount IS NOT NULL
                    ) IS NOT NULL
                    THEN ROUND(
                      (
                        SELECT MIN(final_quote_amount)
                        FROM bidding_companies
                        WHERE bidding_id = ${biddings.id}
                        AND final_quote_amount IS NOT NULL
                      ) / ${biddings.targetPrice} * 100, 1
                    )
                    ELSE NULL
                  END
                `.as('target_price_efficiency_rate'),

                // 입찰 진행 단계 점수 (0-100)
                progressScore: sql<number>`
                  CASE ${biddings.status}
                    WHEN 'bidding_generated' THEN 10
                    WHEN 'request_for_quotation' THEN 20
                    WHEN 'received_quotation' THEN 40
                    WHEN 'set_target_price' THEN 60
                    WHEN 'bidding_opened' THEN 70
                    WHEN 'bidding_closed' THEN 80
                    WHEN 'evaluation_of_bidding' THEN 90
                    WHEN 'vendor_selected' THEN 100
                    WHEN 'bidding_disposal' THEN 0
                    ELSE 0
                  END
                `.as('progress_score'),

                // 마지막 활동일 (가장 최근 업체 응답일)
                lastActivityDate: sql<Date>`
                  GREATEST(
                    ${biddings.updatedAt},
                    COALESCE((
                      SELECT MAX(updated_at)
                      FROM bidding_companies
                      WHERE bidding_id = ${biddings.id}
                    ), ${biddings.updatedAt})
                  )
                `.as('last_activity_date'),
            })
            .from(biddings)
            .leftJoin(
                specificationMeetings,
                sql`${biddings.id} = ${specificationMeetings.biddingId}`
            )
            .where(finalWhere)
            .orderBy(...orderByColumns)
            .limit(input.perPage)
            .offset(offset)

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

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

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

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


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

  // 첨부파일들 (선택사항)
  attachments?: File[]
  vendorAttachments?: File[]

  // noticeType 필드 명시적 추가 (CreateBiddingSchema에 포함되어 있지만 타입 추론 문제 해결)
  noticeType: 'standard' | 'facility' | 'unit_price'

  // PR 아이템들 (선택사항)
  prItems?: Array<{
    id: string
    prNumber: string
    projectId?: number
    projectInfo?: string
    shi?: string
    quantity: string
    quantityUnit: string
    totalWeight: string
    weightUnit: string
    materialGroupNumber: string
    materialGroupInfo: string
    materialNumber?: string
    materialInfo?: string
    materialDescription: string
    hasSpecDocument: boolean
    requestedDeliveryDate: string
    specFiles: File[]
    isRepresentative: boolean

    // 가격 정보
    annualUnitPrice?: string
    currency?: string

    // 단위 정보
    priceUnit?: string
    purchaseUnit?: string
    materialWeight?: string

    // WBS 정보
    wbsCode?: string
    wbsName?: string

    // Cost Center 정보
    costCenterCode?: string
    costCenterName?: string

    // GL Account 정보
    glAccountCode?: string
    glAccountName?: string

    // 내정 정보
    targetUnitPrice?: string
    targetAmount?: string
    targetCurrency?: string

    // 예산 정보
    budgetAmount?: string
    budgetCurrency?: string

    // 실적 정보
    actualAmount?: string
    actualCurrency?: string
  }>

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

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

export interface UpdateBiddingInput extends UpdateBiddingSchema {
  id: number
}

// 4자리 시퀀스 생성 (0001 -> 0009 -> 000A -> 000Z -> 0011 -> ...)
function generateNextSequence(currentMax: string | null): string {
  if (!currentMax) {
    return '0001'; // 첫 번째 시퀀스
  }

  // 36진수로 변환 (0-9, A-Z)
  const chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

  // 4자리 시퀀스를 36진수로 해석
  let value = 0;
  for (let i = 0; i < 4; i++) {
    const charIndex = chars.indexOf(currentMax[i]);
    if (charIndex === -1) return '0001'; // 잘못된 문자면 초기화
    value = value * 36 + charIndex;
  }

  // 1 증가
  value += 1;

  // 다시 4자리 36진수로 변환
  let result = '';
  for (let i = 0; i < 4; i++) {
    const remainder = value % 36;
    result = chars[remainder] + result;
    value = Math.floor(value / 36);
  }

  // 4자리가 되도록 앞에 0 채우기
  return result.padStart(4, '0');
}

// 입찰 참여 현황 카운트 계산 함수
export async function getParticipantCountsForBidding(biddingId: number) {
  try {
    // 전체 참여자 수 (예상 참여자)
    const expectedResult = await db
      .select({ count: count() })
      .from(biddingCompanies)
      .where(eq(biddingCompanies.biddingId, biddingId))

    const expected = expectedResult[0]?.count || 0

    // 참여 완료 수
    const participatedResult = await db
      .select({ count: count() })
      .from(biddingCompanies)
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        eq(biddingCompanies.invitationStatus, 'bidding_submitted')
      ))

    const participated = participatedResult[0]?.count || 0

    // 거부/취소 수
    const declinedResult = await db
      .select({ count: count() })
      .from(biddingCompanies)
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        or(
          eq(biddingCompanies.invitationStatus, 'bidding_declined'),
          eq(biddingCompanies.invitationStatus, 'bidding_cancelled')
        )
      ))

    const declined = declinedResult[0]?.count || 0

    // 대기중 수
    const pendingResult = await db
      .select({ count: count() })
      .from(biddingCompanies)
      .where(and(
        eq(biddingCompanies.biddingId, biddingId),
        or(
          eq(biddingCompanies.invitationStatus, 'pending'),
          eq(biddingCompanies.invitationStatus, 'bidding_sent'),
          eq(biddingCompanies.invitationStatus, 'bidding_accepted')
        )
      ))

    const pending = pendingResult[0]?.count || 0

    return {
      participantExpected: expected,
      participantParticipated: participated,
      participantDeclined: declined,
      participantPending: pending
    }
  } catch (error) {
    console.error('Error in getParticipantCountsForBidding:', error)
    return {
      participantExpected: 0,
      participantParticipated: 0,
      participantDeclined: 0,
      participantPending: 0
    }
  }
}

// 자동 입찰번호 생성
export async function generateBiddingNumber(
  contractType: string,
  userId?: string,
  tx?: any,
  maxRetries: number = 5
): Promise<string> {
  // 계약 타입별 접두사 설정
  const typePrefix = {
    'general': 'E', // 일반계약
    'unit_price': 'F', // 단가계약
    'sale': 'G', // 매각계약
  };

  const prefix = typePrefix[contractType as keyof typeof typePrefix] || 'E';

  // user 테이블의 user.userCode가 있으면 발주담당자 코드로 사용
  // userId가 주어졌을 때 user.userCode를 조회, 없으면 '000' 사용
  let purchaseManagerCode = '000';
  if (userId) {
    const user = await db
      .select({ userCode: users.userCode })
      .from(users)
      .where(eq(users.id, parseInt(userId)))
      .limit(1);
    if (user[0]?.userCode && user[0].userCode.length >= 3) {
      purchaseManagerCode = user[0].userCode.substring(0, 3).toUpperCase();
    }
  }
  const managerCode = (purchaseManagerCode && purchaseManagerCode.length >= 3)
    ? purchaseManagerCode.substring(0, 3).toUpperCase()
    : '000';

  // 현재 년도 2자리
  const currentYear = new Date().getFullYear().toString().slice(-2);

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

  for (let attempt = 0; attempt < maxRetries; attempt++) {
    // 현재 최대 시퀀스 조회 (년도별로, -01 제외하고 앞부분만)
    const prefixLength = yearPrefix.length + 4;
    const result = await dbInstance
      .select({
        maxNumber: sql<string>`MAX(LEFT(${biddings.biddingNumber}, ${prefixLength}))`
      })
      .from(biddings)
      .where(like(biddings.biddingNumber, `${yearPrefix}%`));

    const nextSequence = generateNextSequence(result[0]?.maxNumber?.slice(-4) || null);

    const biddingNumber = `${yearPrefix}${nextSequence}-01`;

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

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

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

  throw new Error(`Failed to generate unique bidding number after ${maxRetries} attempts`);
}
// 입찰 생성
export async function createBidding(input: CreateBiddingInput, userId: string) {
    try {
      const userName = await getUserNameById(userId)
      return await db.transaction(async (tx) => {
        // 자동 입찰번호 생성
        const biddingNumber = await generateBiddingNumber(input.contractType, userId, tx)
  
        // 프로젝트 정보 조회 (PR 아이템에서 설정됨)
        let projectName = input.projectName
  
        // 표준 공고문 템플릿 가져오기 (noticeType별)
        let standardContent = ''
        if (!input.content) {
          try {
            const template = await tx
              .select({ content: biddingNoticeTemplate.content })
              .from(biddingNoticeTemplate)
              .where(
                and(
                  eq(biddingNoticeTemplate.isTemplate, true),
                  eq(biddingNoticeTemplate.type, input.noticeType || 'standard')
                )
              )
              .orderBy(desc(biddingNoticeTemplate.updatedAt))
              .limit(1)

            if (template.length > 0) {
              standardContent = template[0].content
            }
          } catch (error) {
            console.warn('Failed to load standard template:', error)
          }
        }
  
        // 날짜 변환 함수
        const parseDate = (dateStr?: string) => {
          if (!dateStr) return null
          try {
            return new Date(dateStr)
          } catch {
            return null
          }
        }
  
        // 1. 입찰 생성
        const [newBidding] = await tx
          .insert(biddings)
          .values({
            biddingNumber,
            originalBiddingNumber: null, // 원입찰번호는 초기 생성이므로 아직 없음
            revision: input.revision || 0,
            
            // 프로젝트 정보 (PR 아이템에서 설정됨)
            projectName,
            
            itemName: input.itemName,
            title: input.title,
            description: input.description,
            
            contractType: input.contractType,
            noticeType: input.noticeType || 'standard',
            biddingType: input.biddingType,
            awardCount: input.awardCount,
            contractStartDate: input.contractStartDate ? parseDate(input.contractStartDate) : new Date(),
            contractEndDate: input.contractEndDate ? parseDate(input.contractEndDate) : (() => {
              const startDate = input.contractStartDate ? new Date(input.contractStartDate) : new Date()
              const endDate = new Date(startDate)
              endDate.setFullYear(endDate.getFullYear() + 1) // 1년 후
              return endDate
            })(),
            
            // 자동 등록일 설정
            biddingRegistrationDate: new Date(),
            submissionStartDate: parseDate(input.submissionStartDate),
            submissionEndDate: parseDate(input.submissionEndDate),
            evaluationDate: parseDate(input.evaluationDate),
            
            hasSpecificationMeeting: input.hasSpecificationMeeting || false,
            hasPrDocument: input.hasPrDocument || false,
            prNumber: input.prNumber,
            
            currency: input.currency,
            budget: input.budget ? parseFloat(input.budget) : null,
            targetPrice: input.targetPrice ? parseFloat(input.targetPrice) : null,
            finalBidPrice: input.finalBidPrice ? parseFloat(input.finalBidPrice) : null,
            
            status: input.status || 'bidding_generated',
            // biddingSourceType: input.biddingSourceType || 'manual',
            isPublic: input.isPublic || false,
            isUrgent: input.isUrgent || false,

            // 구매조직
            purchasingOrganization: input.purchasingOrganization,

            // 담당자 정보 (user FK)
            bidPicId: input.bidPicId ? parseInt(input.bidPicId.toString()) : null,
            bidPicName: input.bidPicName || null,
            bidPicCode: input.bidPicCode || null,
            supplyPicId: input.supplyPicId ? parseInt(input.supplyPicId.toString()) : null,
            supplyPicName: input.supplyPicName || null,
            supplyPicCode: input.supplyPicCode || null,
            
            remarks: input.remarks,
            createdBy: userName,
            updatedBy: userName,
          })
          .returning({ id: biddings.id })
  
        const biddingId = newBidding.id
  
        // 2. 입찰공고 생성 (템플릿에서 복제)
        await tx.insert(biddingNoticeTemplate).values({
          biddingId,
          title: input.title + ' 입찰공고',
          content: input.content || standardContent,
          isTemplate: false,
        })

        // 3. 사양설명회 정보 저장 (있는 경우)
        if (input.specificationMeeting) {
          const [newSpecMeeting] = await tx
            .insert(specificationMeetings)
            .values({
              biddingId,
              meetingDate: new Date(input.specificationMeeting.meetingDate),
              meetingTime: input.specificationMeeting.meetingTime,
              location: input.specificationMeeting.location,
              address: input.specificationMeeting.address,
              contactPerson: input.specificationMeeting.contactPerson,
              contactPhone: input.specificationMeeting.contactPhone,
              contactEmail: input.specificationMeeting.contactEmail,
              agenda: input.specificationMeeting.agenda,
              materials: input.specificationMeeting.materials,
              notes: input.specificationMeeting.notes,
              isRequired: input.specificationMeeting.isRequired,
            })
            .returning({ id: specificationMeetings.id })
  
          // 2-1. 사양설명회 첨부파일 저장
          if (input.specificationMeeting.meetingFiles && input.specificationMeeting.meetingFiles.length > 0) {
            for (const file of input.specificationMeeting.meetingFiles) {
              try {
                const saveResult = await saveFile({
                  file,
                  directory: `biddings/${biddingId}/specification-meeting`,
                  originalName: file.name,
                  userId
                })
  
                if (saveResult.success) {
                  await tx.insert(biddingDocuments).values({
                    biddingId,
                    specificationMeetingId: newSpecMeeting.id,
                    documentType: 'specification_meeting',
                    fileName: saveResult.fileName!,
                    originalFileName: saveResult.originalName!,
                    fileSize: saveResult.fileSize!,
                    mimeType: file.type,
                    filePath: saveResult.publicPath!,
                    // publicPath: saveResult.publicPath,
                    title: `사양설명회 - ${file.name}`,
                    isPublic: false,
                    isRequired: false,
                    uploadedBy: userName,
                  })
                } else {
                  console.error(`Failed to save specification meeting file: ${file.name}`, saveResult.error)
                  // 파일 저장 실패해도 전체 트랜잭션은 계속 진행
                }
              } catch (error) {
                console.error(`Error saving specification meeting file: ${file.name}`, error)
              }
            }
          }
        }
  
        // 3. 입찰 조건 저장 (있는 경우)
        if (input.biddingConditions) {
          try {
            await tx.insert(biddingConditions).values({
              biddingId,
              paymentTerms: input.biddingConditions.paymentTerms,
              taxConditions: input.biddingConditions.taxConditions,
              incoterms: input.biddingConditions.incoterms,
              incotermsOption: input.biddingConditions.incotermsOption,
              contractDeliveryDate: input.biddingConditions.contractDeliveryDate || null,
              shippingPort: input.biddingConditions.shippingPort || null,
              destinationPort: input.biddingConditions.destinationPort || null,
              isPriceAdjustmentApplicable: input.biddingConditions.isPriceAdjustmentApplicable,
              sparePartOptions: input.biddingConditions.sparePartOptions,
            })
          } catch (error) {
            console.error('Error saving bidding conditions:', error)
            // 입찰 조건 저장 실패해도 전체 트랜잭션은 계속 진행
          }
        }

        // 4. PR 아이템들 저장 (있는 경우)
        if (input.prItems && input.prItems.length > 0) {
          for (const prItem of input.prItems) {
            // PR 아이템 저장
            const [newPrItem] = await tx.insert(prItemsForBidding).values({
              biddingId,
              projectId: prItem.projectId, // 프로젝트 ID 추가
              projectInfo: prItem.projectInfo || '', // 프로젝트 정보 (기존 호환성 유지)
              shi: prItem.shi || '', // SHI 정보
              requestedDeliveryDate: prItem.requestedDeliveryDate ? new Date(prItem.requestedDeliveryDate) : null,

              // 자재 그룹 정보 (필수)
              materialGroupNumber: prItem.materialGroupNumber,
              materialGroupInfo: prItem.materialGroupInfo,

              // 자재 정보
              materialNumber: prItem.materialNumber || null,
              materialInfo: prItem.materialInfo || null,

              // 가격 정보
              annualUnitPrice: prItem.annualUnitPrice ? parseFloat(prItem.annualUnitPrice) : null,
              currency: prItem.currency || 'KRW',

              // 수량 및 중량
              quantity: prItem.quantity ? parseFloat(prItem.quantity) : null,
              quantityUnit: prItem.quantityUnit as any,
              totalWeight: prItem.totalWeight ? parseFloat(prItem.totalWeight) : null,
              weightUnit: prItem.weightUnit as any,
              
              // 단위 정보
              priceUnit: prItem.priceUnit || null,
              purchaseUnit: prItem.purchaseUnit || null,
              materialWeight: prItem.materialWeight ? parseFloat(prItem.materialWeight) : null,

              // WBS 정보
              wbsCode: prItem.wbsCode || null,
              wbsName: prItem.wbsName || null,

              // Cost Center 정보
              costCenterCode: prItem.costCenterCode || null,
              costCenterName: prItem.costCenterName || null,

              // GL Account 정보
              glAccountCode: prItem.glAccountCode || null,
              glAccountName: prItem.glAccountName || null,

              // 내정 정보
              targetUnitPrice: prItem.targetUnitPrice ? parseFloat(prItem.targetUnitPrice) : null,
              targetAmount: prItem.targetAmount ? parseFloat(prItem.targetAmount) : null,
              targetCurrency: prItem.targetCurrency || 'KRW',

              // 예산 정보
              budgetAmount: prItem.budgetAmount ? parseFloat(prItem.budgetAmount) : null,
              budgetCurrency: prItem.budgetCurrency || 'KRW',

              // 실적 정보
              actualAmount: prItem.actualAmount ? parseFloat(prItem.actualAmount) : null,
              actualCurrency: prItem.actualCurrency || 'KRW',

              // 상세 정보
              materialDescription: prItem.materialDescription || '',
              prNumber: prItem.prNumber,
              hasSpecDocument: prItem.specFiles.length > 0,
              isRepresentative: prItem.isRepresentative,
            }).returning({ id: prItemsForBidding.id })
  
            // 3-1. 스펙 파일들 저장 (있는 경우)
            if (prItem.specFiles.length > 0) {
              for (let fileIndex = 0; fileIndex < prItem.specFiles.length; fileIndex++) {
                const file = prItem.specFiles[fileIndex]
                try {
                  const saveResult = await saveFile({
                    file,
                    directory: `biddings/${biddingId}/pr-items/${newPrItem.id}/specs`,
                    originalName: file.name,
                    userId
                  })
  
                  if (saveResult.success) {
                    await tx.insert(biddingDocuments).values({
                      biddingId,
                      prItemId: newPrItem.id,
                      documentType: 'spec_document',
                      fileName: saveResult.fileName!,
                      originalFileName: saveResult.originalName!,
                      fileSize: saveResult.fileSize!,
                      mimeType: file.type,
                      filePath: saveResult.publicPath!,
                      // publicPath: saveResult.publicPath,
                      title: `${prItem.materialGroupInfo || prItem.materialGroupNumber} 스펙 - ${file.name}`,
                      description: `PR ${prItem.prNumber}의 스펙 문서`,
                      isPublic: false,
                      isRequired: false,
                      uploadedBy: userName,
                    })
                  } else {
                    console.error(`Failed to save spec file: ${file.name}`, saveResult.error)
                    // 파일 저장 실패해도 전체 트랜잭션은 계속 진행
                  }
                } catch (error) {
                  console.error(`Error saving spec file: ${file.name}`, error)
                }
              }
            }
          }
        }

        // 4. 첨부파일들 저장 (있는 경우)
        if (input.attachments && input.attachments.length > 0) {
          for (const file of input.attachments) {
            try {
              const saveResult = await saveFile({
                file,
                directory: `biddings/${biddingId}/attachments/shi`,
                originalName: file.name,
                userId
              })

              if (saveResult.success) {
                await tx.insert(biddingDocuments).values({
                  biddingId,
                  documentType: 'evaluation_doc', // SHI용 문서 타입
                  fileName: saveResult.fileName!,
                  originalFileName: saveResult.originalName!,
                  fileSize: saveResult.fileSize!,
                  mimeType: file.type,
                  filePath: saveResult.publicPath!,
                  title: `SHI용 첨부파일 - ${file.name}`,
                  description: 'SHI용 첨부파일',
                  isPublic: true, // 발주처 문서이므로 공개
                  isRequired: false,
                  uploadedBy: userName,
                })
              } else {
                console.error(`Failed to save SHI attachment file: ${file.name}`, saveResult.error)
              }
            } catch (error) {
              console.error(`Error saving SHI attachment file: ${file.name}`, error)
            }
          }
        }

        // Vendor 첨부파일들 저장 (있는 경우)
        if (input.vendorAttachments && input.vendorAttachments.length > 0) {
          for (const file of input.vendorAttachments) {
            try {
              const saveResult = await saveFile({
                file,
                directory: `biddings/${biddingId}/attachments/vendor`,
                originalName: file.name,
                userId
              })

              if (saveResult.success) {
                await tx.insert(biddingDocuments).values({
                  biddingId,
                  documentType: 'company_proposal', // 협력업체용 문서 타입
                  fileName: saveResult.fileName!,
                  originalFileName: saveResult.originalName!,
                  fileSize: saveResult.fileSize!,
                  mimeType: file.type,
                  filePath: saveResult.publicPath!,
                  title: `협력업체용 첨부파일 - ${file.name}`,
                  description: '협력업체용 첨부파일',
                  isPublic: true, // 발주처 문서이므로 공개
                  isRequired: false,
                  uploadedBy: userName,
                })
              } else {
                console.error(`Failed to save vendor attachment file: ${file.name}`, saveResult.error)
              }
            } catch (error) {
              console.error(`Error saving vendor attachment file: ${file.name}`, error)
            }
          }
        }

        // 캐시 무효화
        revalidatePath('/evcp/bid')

        return {
          success: true,
          message: '입찰이 성공적으로 생성되었습니다.',
          data: { id: biddingId, biddingNumber }
        }
      })
    } catch (error) {
      console.error('Error creating bidding:', error)
      return {
        success: false,
        error: error instanceof Error ? error.message : '입찰 생성 중 오류가 발생했습니다.'
      }
    }
  }
// 입찰 수정
export async function updateBidding(input: UpdateBiddingInput, userId: string) {
  try {
    const userName = await getUserNameById(userId)
    // 존재 여부 확인
    const existing = await db
      .select({ id: biddings.id })
      .from(biddings)
      .where(eq(biddings.id, input.id))
      .limit(1)

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

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

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

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

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

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

    // 구매조직
    if (input.purchasingOrganization !== undefined) updateData.purchasingOrganization = input.purchasingOrganization

    // 담당자 정보 (user FK)
    if (input.bidPicId !== undefined) updateData.bidPicId = input.bidPicId
    if (input.bidPicName !== undefined) updateData.bidPicName = input.bidPicName
    if (input.supplyPicId !== undefined) updateData.supplyPicId = input.supplyPicId
    if (input.supplyPicName !== undefined) updateData.supplyPicName = input.supplyPicName
    
    if (input.remarks !== undefined) updateData.remarks = input.remarks

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

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

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

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

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

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

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

    revalidatePath('/admin/biddings')

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

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

// 단일 입찰 조회
export async function getBiddingById(id: number) {
  try {
    // ID 유효성 검증
    if (!id || isNaN(id) || id <= 0) {
      console.warn('Invalid bidding ID provided to getBiddingById:', id)
      return null
    }

    const bidding = await db
      .select()
      .from(biddings)
      .where(eq(biddings.id, id))
      .limit(1)

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

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

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

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

// PR 상세 정보 타입
export interface PRDetails {
    documents: Array<{
        id: number
        documentName: string
        fileName: string
        originalFileName: string
        fileSize: number
        filePath: string
        registeredAt: string
        registeredBy: string
        version?: string | null
        description?: string | null
        createdAt: string
        updatedAt: string
    }>
    items: Array<{
        id: number
        itemNumber: string | null
        prNumber: string | null
        projectInfo: string | null
        shi: string | null
        // 자재 그룹 정보
        materialGroupNumber: string | null
        materialGroupInfo: string | null
        // 자재 정보
        materialNumber: string | null
        materialInfo: string | null
        // 품목 정보
        itemInfo: string | null
        // 수량 및 중량
        quantity: number | null
        quantityUnit: string | null
        totalWeight: number | null
        weightUnit: string | null
        // 가격 정보
        annualUnitPrice: number | null
        currency: string | null
        // 단위 정보
        priceUnit: string | null
        purchaseUnit: string | null
        materialWeight: number | null
        // WBS 정보
        wbsCode: string | null
        wbsName: string | null
        // Cost Center 정보
        costCenterCode: string | null
        costCenterName: string | null
        // GL Account 정보
        glAccountCode: string | null
        glAccountName: string | null
        // 내정 정보
        targetUnitPrice: number | null
        targetAmount: number | null
        targetCurrency: string | null
        // 예산 정보
        budgetAmount: number | null
        budgetCurrency: string | null
        // 실적 정보
        actualAmount: number | null
        actualCurrency: string | null
        // 납품 일정
        requestedDeliveryDate: string | null
        // SPEC 문서
        hasSpecDocument: boolean
        createdAt: string
        updatedAt: string
        specDocuments: Array<{
            id: number
            fileName: string
            originalFileName: string
            fileSize: number
            filePath: string
            uploadedAt: string
            title: string | null
        }>
    }>
}

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

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

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

        const meetingData = meeting[0]

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

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

        return {
            success: true,
            data: result
        }

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

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

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

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

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

              // 5. 데이터 직렬화 (모든 필드 포함)
              return {
                  id: item.id,
                  itemNumber: item.itemNumber,
                  prNumber: item.prNumber,
                  projectInfo: item.projectInfo,
                  shi: item.shi,
                  // 자재 그룹 정보
                  materialGroupNumber: item.materialGroupNumber,
                  materialGroupInfo: item.materialGroupInfo,
                  // 자재 정보
                  materialNumber: item.materialNumber,
                  materialInfo: item.materialInfo,
                  // 품목 정보
                  itemInfo: item.itemInfo,
                  // 수량 및 중량
                  quantity: item.quantity ? Number(item.quantity) : null,
                  quantityUnit: item.quantityUnit,
                  totalWeight: item.totalWeight ? Number(item.totalWeight) : null,
                  weightUnit: item.weightUnit,
                  // 가격 정보
                  annualUnitPrice: item.annualUnitPrice ? Number(item.annualUnitPrice) : null,
                  currency: item.currency,
                  // 단위 정보
                  priceUnit: item.priceUnit,
                  purchaseUnit: item.purchaseUnit,
                  materialWeight: item.materialWeight ? Number(item.materialWeight) : null,
                  // WBS 정보
                  wbsCode: item.wbsCode,
                  wbsName: item.wbsName,
                  // Cost Center 정보
                  costCenterCode: item.costCenterCode,
                  costCenterName: item.costCenterName,
                  // GL Account 정보
                  glAccountCode: item.glAccountCode,
                  glAccountName: item.glAccountName,
                  // 내정 정보
                  targetUnitPrice: item.targetUnitPrice ? Number(item.targetUnitPrice) : null,
                  targetAmount: item.targetAmount ? Number(item.targetAmount) : null,
                  targetCurrency: item.targetCurrency,
                  // 예산 정보
                  budgetAmount: item.budgetAmount ? Number(item.budgetAmount) : null,
                  budgetCurrency: item.budgetCurrency,
                  // 실적 정보
                  actualAmount: item.actualAmount ? Number(item.actualAmount) : null,
                  actualCurrency: item.actualCurrency,
                  // 납품 일정
                  requestedDeliveryDate: item.requestedDeliveryDate || null,
                  // 기타
                  hasSpecDocument: item.hasSpecDocument || false,
                  createdAt: item.createdAt?.toISOString() || '',
                  updatedAt: item.updatedAt?.toISOString() || '',
                  specDocuments: specDocuments.map(doc => ({
                      id: doc.id,
                      fileName: doc.fileName,
                      originalFileName: doc.originalFileName,
                      fileSize: doc.fileSize || 0,
                      filePath: doc.filePath,
                      uploadedAt: doc.uploadedAt?.toISOString() || '',
                      title: doc.title,
                  }))
              }
          })
      )

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

      return {
          success: true,
          data: result
      }

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

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

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

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

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

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

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

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

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

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

// 입찰 조건 업데이트
// === 입찰 관리 서버 액션들 ===

// 입찰 기본 정보 업데이트 (관리 페이지용)
export async function updateBiddingBasicInfo(
  biddingId: number,
  updates: {
    title?: string
    description?: string
    content?: string
    noticeType?: string
    contractType?: string
    biddingType?: string
    biddingTypeCustom?: string
    awardCount?: string
    budget?: string
    finalBidPrice?: string
    targetPrice?: string
    prNumber?: string
    contractStartDate?: string
    contractEndDate?: string
    submissionStartDate?: string
    submissionEndDate?: string
    evaluationDate?: string
    hasSpecificationMeeting?: boolean
    hasPrDocument?: boolean
    currency?: string
    purchasingOrganization?: string
    bidPicName?: string
    bidPicCode?: string
    supplyPicName?: string
    supplyPicCode?: string
    requesterName?: string
    remarks?: string
  },
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)

    // 존재 여부 확인
    const existing = await db
      .select({ id: biddings.id })
      .from(biddings)
      .where(eq(biddings.id, biddingId))
      .limit(1)

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

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

    // 숫자 문자열을 숫자로 변환 (빈 문자열은 null)
    const parseNumeric = (value?: string): number | null | undefined => {
      if (value === undefined) return undefined
      if (value === '' || value === null) return null
      const parsed = parseFloat(value)
      return isNaN(parsed) ? null : parsed
    }

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

    // 정의된 필드들만 업데이트
    if (updates.title !== undefined) updateData.title = updates.title
    if (updates.description !== undefined) updateData.description = updates.description
    if (updates.content !== undefined) updateData.content = updates.content
    if (updates.noticeType !== undefined) updateData.noticeType = updates.noticeType
    if (updates.contractType !== undefined) updateData.contractType = updates.contractType
    if (updates.biddingType !== undefined) updateData.biddingType = updates.biddingType
    if (updates.biddingTypeCustom !== undefined) updateData.biddingTypeCustom = updates.biddingTypeCustom
    if (updates.awardCount !== undefined) updateData.awardCount = updates.awardCount
    if (updates.budget !== undefined) updateData.budget = parseNumeric(updates.budget)
    if (updates.finalBidPrice !== undefined) updateData.finalBidPrice = parseNumeric(updates.finalBidPrice)
    if (updates.targetPrice !== undefined) updateData.targetPrice = parseNumeric(updates.targetPrice)
    if (updates.prNumber !== undefined) updateData.prNumber = updates.prNumber
    if (updates.contractStartDate !== undefined) updateData.contractStartDate = parseDate(updates.contractStartDate)
    if (updates.contractEndDate !== undefined) updateData.contractEndDate = parseDate(updates.contractEndDate)
    if (updates.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(updates.submissionStartDate)
    if (updates.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(updates.submissionEndDate)
    if (updates.evaluationDate !== undefined) updateData.evaluationDate = parseDate(updates.evaluationDate)
    if (updates.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = updates.hasSpecificationMeeting
    if (updates.hasPrDocument !== undefined) updateData.hasPrDocument = updates.hasPrDocument
    if (updates.currency !== undefined) updateData.currency = updates.currency
    if (updates.purchasingOrganization !== undefined) updateData.purchasingOrganization = updates.purchasingOrganization
    if (updates.bidPicName !== undefined) updateData.bidPicName = updates.bidPicName
    if (updates.bidPicCode !== undefined) updateData.bidPicCode = updates.bidPicCode
    if (updates.supplyPicName !== undefined) updateData.supplyPicName = updates.supplyPicName
    if (updates.supplyPicCode !== undefined) updateData.supplyPicCode = updates.supplyPicCode
    if (updates.requesterName !== undefined) updateData.requesterName = updates.requesterName
    if (updates.remarks !== undefined) updateData.remarks = updates.remarks

    // 데이터베이스 업데이트
    await db
      .update(biddings)
      .set(updateData)
      .where(eq(biddings.id, biddingId))

    revalidatePath(`/evcp/bid/${biddingId}`)
    revalidatePath(`/evcp/bid/${biddingId}/info`)

    return {
      success: true,
      message: '입찰 기본 정보가 성공적으로 업데이트되었습니다.'
    }
  } catch (error) {
    console.error('Failed to update bidding basic info:', error)
    return {
      success: false,
      error: '입찰 기본 정보 업데이트에 실패했습니다.'
    }
  }
}

// 입찰 일정 업데이트
export async function updateBiddingSchedule(
  biddingId: number,
  schedule: {
    submissionStartDate?: string
    submissionEndDate?: string
    remarks?: string
    isUrgent?: boolean
    hasSpecificationMeeting?: boolean
  },
  userId: string,
  specificationMeeting?: {
    meetingDate: string
    meetingTime: string
    location: string
    address: string
    contactPerson: string
    contactPhone: string
    contactEmail: string
    agenda: string
    materials: string
    notes: string
    isRequired: boolean
  }
) {
  try {
    const userName = await getUserNameById(userId)

    // 날짜 문자열을 Date 객체로 수동 변환
    const parseDate = (dateStr?: string) => {
      if (!dateStr) return undefined
      // 'YYYY-MM-DDTHH:mm' 또는 'YYYY-MM-DD HH:mm' 등을 허용
      // 잘못된 포맷이면 undefined 반환
      const m = dateStr.match(
        /^(\d{4})-(\d{2})-(\d{2})[T ]?(\d{2}):(\d{2})(?::(\d{2}))?$/
      )
      if (!m) return undefined
      const year = parseInt(m[1], 10)
      const month = parseInt(m[2], 10) - 1 // JS month는 0부터
      const day = parseInt(m[3], 10)
      const hour = parseInt(m[4], 10)
      const min = parseInt(m[5], 10)
      const sec = m[6] ? parseInt(m[6], 10) : 0
      return new Date(Date.UTC(year, month, day, hour, min, sec))
    }

    return await db.transaction(async (tx) => {
      const updateData: any = {
        updatedAt: new Date(),
        updatedBy: userName,
      }

      if (schedule.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(schedule.submissionStartDate) || null
      if (schedule.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(schedule.submissionEndDate) || null
      if (schedule.remarks !== undefined) updateData.remarks = schedule.remarks
      if (schedule.isUrgent !== undefined) updateData.isUrgent = schedule.isUrgent
      if (schedule.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = schedule.hasSpecificationMeeting

      await tx
        .update(biddings)
        .set(updateData)
        .where(eq(biddings.id, biddingId))

      // 사양설명회 정보 저장/업데이트
      if (schedule.hasSpecificationMeeting && specificationMeeting) {
        // 기존 사양설명회 정보 확인
        const existingMeeting = await tx
          .select()
          .from(specificationMeetings)
          .where(eq(specificationMeetings.biddingId, biddingId))
          .limit(1)

        if (existingMeeting.length > 0) {
          // 기존 정보 업데이트
          await tx
            .update(specificationMeetings)
            .set({
              meetingDate: parseDate(specificationMeeting.meetingDate) || null,
              meetingTime: specificationMeeting.meetingTime || null,
              location: specificationMeeting.location,
              address: specificationMeeting.address || null,
              contactPerson: specificationMeeting.contactPerson,
              contactPhone: specificationMeeting.contactPhone || null,
              contactEmail: specificationMeeting.contactEmail || null,
              agenda: specificationMeeting.agenda || null,
              materials: specificationMeeting.materials || null,
              notes: specificationMeeting.notes || null,
              isRequired: specificationMeeting.isRequired || false,
              updatedAt: new Date(),
            })
            .where(eq(specificationMeetings.id, existingMeeting[0].id))
        } else {
          // 새로 생성
          await tx
            .insert(specificationMeetings)
            .values({
              biddingId,
              meetingDate: parseDate(specificationMeeting.meetingDate),
              meetingTime: specificationMeeting.meetingTime || null,
              location: specificationMeeting.location,
              address: specificationMeeting.address || null,
              contactPerson: specificationMeeting.contactPerson,
              contactPhone: specificationMeeting.contactPhone || null,
              contactEmail: specificationMeeting.contactEmail || null,
              agenda: specificationMeeting.agenda || null,
              materials: specificationMeeting.materials || null,
              notes: specificationMeeting.notes || null,
              isRequired: specificationMeeting.isRequired || false,
            })
        }
      } else if (!schedule.hasSpecificationMeeting) {
        // 사양설명회 실시 여부가 false로 변경된 경우, 관련 정보 삭제
        await tx
          .delete(specificationMeetings)
          .where(eq(specificationMeetings.biddingId, biddingId))
      }

      revalidatePath(`/evcp/bid/${biddingId}`)
      revalidatePath(`/evcp/bid/${biddingId}/schedule`)

      return {
        success: true,
        message: '입찰 일정이 성공적으로 업데이트되었습니다.'
      }
    })
  } catch (error) {
    console.error('Failed to update bidding schedule:', error)
    return {
      success: false,
      error: '입찰 일정 업데이트에 실패했습니다.'
    }
  }
}

// 입찰 품목 관리 액션들
export async function getBiddingItems(biddingId: number) {
  try {
    // PR 아이템 조회 (실제로는 prItemsForBidding 테이블에서 조회)
    const items = await db
      .select({
        id: prItemsForBidding.id,
        itemName: prItemsForBidding.itemName,
        description: prItemsForBidding.description,
        quantity: prItemsForBidding.quantity,
        unit: prItemsForBidding.unit,
        unitPrice: prItemsForBidding.unitPrice,
        totalPrice: prItemsForBidding.totalPrice,
        currency: prItemsForBidding.currency,
      })
      .from(prItemsForBidding)
      .where(eq(prItemsForBidding.biddingId, biddingId))
      .orderBy(prItemsForBidding.id)

    return {
      success: true,
      data: items
    }
  } catch (error) {
    console.error('Failed to get bidding items:', error)
    return {
      success: false,
      error: '품목 정보를 불러오는데 실패했습니다.'
    }
  }
}

export async function updateBiddingItem(
  itemId: number,
  updates: {
    itemName?: string
    description?: string
    quantity?: number
    unit?: string
    unitPrice?: number
    currency?: string
  }
) {
  try {
    const updateData: any = {
      updatedAt: new Date(),
    }

    if (updates.itemName !== undefined) updateData.itemName = updates.itemName
    if (updates.description !== undefined) updateData.description = updates.description
    if (updates.quantity !== undefined) updateData.quantity = updates.quantity
    if (updates.unit !== undefined) updateData.unit = updates.unit
    if (updates.unitPrice !== undefined) updateData.unitPrice = updates.unitPrice
    if (updates.currency !== undefined) updateData.currency = updates.currency

    // 총액 자동 계산
    if (updates.quantity !== undefined || updates.unitPrice !== undefined) {
      const item = await db
        .select({ quantity: prItemsForBidding.quantity, unitPrice: prItemsForBidding.unitPrice })
        .from(prItemsForBidding)
        .where(eq(prItemsForBidding.id, itemId))
        .limit(1)

      if (item.length > 0) {
        const quantity = updates.quantity ?? item[0].quantity ?? 0
        const unitPrice = updates.unitPrice ?? item[0].unitPrice ?? 0
        updateData.totalPrice = quantity * unitPrice
      }
    }

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

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

export async function addBiddingItem(
  biddingId: number,
  item: {
    itemName: string
    description?: string
    quantity?: number
    unit?: string
    unitPrice?: number
    currency?: string
  }
) {
  try {
    const totalPrice = (item.quantity || 0) * (item.unitPrice || 0)

    await db.insert(prItemsForBidding).values({
      biddingId,
      itemName: item.itemName,
      description: item.description,
      quantity: item.quantity || 0,
      unit: item.unit,
      unitPrice: item.unitPrice || 0,
      totalPrice,
      currency: item.currency || 'KRW',
    })

    return {
      success: true,
      message: '품목이 성공적으로 추가되었습니다.'
    }
  } catch (error) {
    console.error('Failed to add bidding item:', error)
    return {
      success: false,
      error: '품목 추가에 실패했습니다.'
    }
  }
}

export async function removeBiddingItem(itemId: number) {
  try {
    await db
      .delete(prItemsForBidding)
      .where(eq(prItemsForBidding.id, itemId))

    return {
      success: true,
      message: '품목이 성공적으로 삭제되었습니다.'
    }
  } catch (error) {
    console.error('Failed to remove bidding item:', error)
    return {
      success: false,
      error: '품목 삭제에 실패했습니다.'
    }
  }
}

// 입찰의 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,
        budget: totalBudgetAmount,
        finalBidPrice: totalActualAmount,
        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 addPRItemForBidding(
  biddingId: number,
  item: {
    projectId?: number | null
    projectInfo?: string | null
    shi?: string | null
    materialGroupNumber?: string | null
    materialGroupInfo?: string | null
    materialNumber?: string | null
    materialInfo?: string | null
    quantity?: string | null
    quantityUnit?: string | null
    totalWeight?: string | null
    weightUnit?: string | null
    priceUnit?: string | null
    purchaseUnit?: string | null
    materialWeight?: string | null
    wbsCode?: string | null
    wbsName?: string | null
    costCenterCode?: string | null
    costCenterName?: string | null
    glAccountCode?: string | null
    glAccountName?: string | null
    targetUnitPrice?: string | null
    targetAmount?: string | null
    targetCurrency?: string | null
    budgetAmount?: string | null
    budgetCurrency?: string | null
    actualAmount?: string | null
    actualCurrency?: string | null
    requestedDeliveryDate?: string | null
    prNumber?: string | null
    currency?: string | null
    annualUnitPrice?: string | null
    hasSpecDocument?: boolean
  }
) {
  try {
    const result = await db.insert(prItemsForBidding).values({
      biddingId,
      projectId: item.projectId || null,
      projectInfo: item.projectInfo || null,
      shi: item.shi || null,
      materialGroupNumber: item.materialGroupNumber || null,
      materialGroupInfo: item.materialGroupInfo || null,
      materialNumber: item.materialNumber || null,
      materialInfo: item.materialInfo || null,
      quantity: item.quantity ? parseFloat(item.quantity) : null,
      quantityUnit: item.quantityUnit || null,
      totalWeight: item.totalWeight ? parseFloat(item.totalWeight) : null,
      weightUnit: item.weightUnit || null,
      priceUnit: item.priceUnit || null,
      purchaseUnit: item.purchaseUnit || null,
      materialWeight: item.materialWeight ? parseFloat(item.materialWeight) : null,
      wbsCode: item.wbsCode || null,
      wbsName: item.wbsName || null,
      costCenterCode: item.costCenterCode || null,
      costCenterName: item.costCenterName || null,
      glAccountCode: item.glAccountCode || null,
      glAccountName: item.glAccountName || null,
      targetUnitPrice: item.targetUnitPrice ? parseFloat(item.targetUnitPrice) : null,
      targetAmount: item.targetAmount ? parseFloat(item.targetAmount) : null,
      targetCurrency: item.targetCurrency || 'KRW',
      budgetAmount: item.budgetAmount ? parseFloat(item.budgetAmount) : null,
      budgetCurrency: item.budgetCurrency || 'KRW',
      actualAmount: item.actualAmount ? parseFloat(item.actualAmount) : null,
      actualCurrency: item.actualCurrency || 'KRW',
      requestedDeliveryDate: item.requestedDeliveryDate ? new Date(item.requestedDeliveryDate) : null,
      prNumber: item.prNumber || null,
      currency: item.currency || 'KRW',
      annualUnitPrice: item.annualUnitPrice ? parseFloat(item.annualUnitPrice) : null,
      hasSpecDocument: item.hasSpecDocument || false,
    }).returning()

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

    revalidatePath(`/evcp/bid/${biddingId}/info`)
    revalidatePath(`/evcp/bid/${biddingId}`)

    return {
      success: true,
      data: result[0],
      message: '품목이 성공적으로 추가되었습니다.'
    }
  } catch (error) {
    console.error('Failed to add PR item:', error)
    return {
      success: false,
      error: '품목 추가에 실패했습니다.'
    }
  }
}

// 입찰 업체 관리 액션들
export async function getBiddingVendors(biddingId: number) {
  try {
    const vendorsData = await db
      .select({
        id: biddingCompanies.id,
        companyId: biddingCompanies.companyId, // 벤더 ID 추가
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        contactPerson: biddingCompanies.contactPerson,
        contactEmail: biddingCompanies.contactEmail,
        contactPhone: biddingCompanies.contactPhone,
        quotationAmount: biddingCompanies.finalQuoteAmount,
        currency: sql<string>`'KRW'`,
        invitationStatus: biddingCompanies.invitationStatus,
        isPriceAdjustmentApplicableQuestion: biddingCompanies.isPriceAdjustmentApplicableQuestion,
        businessSize: vendors.businessSize,
      })
      .from(biddingCompanies)
      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))
      .where(eq(biddingCompanies.biddingId, biddingId))
      .orderBy(biddingCompanies.id)

    return {
      success: true,
      data: vendorsData
    }
  } catch (error) {
    console.error('Failed to get bidding vendors:', error)
    return {
      success: false,
      error: '업체 정보를 불러오는데 실패했습니다.'
    }
  }
}

export async function updateBiddingCompanyPriceAdjustmentQuestion(
  biddingCompanyId: number,
  isPriceAdjustmentApplicableQuestion: boolean
) {
  try {
    await db
      .update(biddingCompanies)
      .set({
        isPriceAdjustmentApplicableQuestion,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))

    return {
      success: true,
      message: '연동제 적용요건 문의 여부가 성공적으로 업데이트되었습니다.'
    }
  } catch (error) {
    console.error('Failed to update price adjustment question:', error)
    return {
      success: false,
      error: '연동제 적용요건 문의 여부 업데이트에 실패했습니다.'
    }
  }
}

export async function updateVendorContact(
  biddingCompanyId: number,
  contact: {
    contactPerson?: string
    contactEmail?: string
    contactPhone?: string
  }
) {
  try {
    // biddingCompanies 테이블에 연락처 정보가 직접 저장되어 있으므로 직접 업데이트
    await db
      .update(biddingCompanies)
      .set({
        contactPerson: contact.contactPerson,
        contactEmail: contact.contactEmail,
        contactPhone: contact.contactPhone,
        updatedAt: new Date(),
      })
      .where(eq(biddingCompanies.id, biddingCompanyId))

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

// 입찰 참여 업체 담당자 관리 함수들
export async function getBiddingCompanyContacts(biddingId: number, vendorId: number) {
  try {
    const contacts = await db
      .select({
        id: biddingCompaniesContacts.id,
        biddingId: biddingCompaniesContacts.biddingId,
        vendorId: biddingCompaniesContacts.vendorId,
        contactName: biddingCompaniesContacts.contactName,
        contactEmail: biddingCompaniesContacts.contactEmail,
        contactNumber: biddingCompaniesContacts.contactNumber,
        createdAt: biddingCompaniesContacts.createdAt,
        updatedAt: biddingCompaniesContacts.updatedAt,
      })
      .from(biddingCompaniesContacts)
      .where(
        and(
          eq(biddingCompaniesContacts.biddingId, biddingId),
          eq(biddingCompaniesContacts.vendorId, vendorId)
        )
      )
      .orderBy(asc(biddingCompaniesContacts.contactName))

    return {
      success: true,
      data: contacts
    }
  } catch (error) {
    console.error('Failed to get bidding company contacts:', error)
    return {
      success: false,
      error: '담당자 목록을 불러오는데 실패했습니다.'
    }
  }
}

export async function createBiddingCompanyContact(
  biddingId: number,
  vendorId: number,
  contact: {
    contactName: string
    contactEmail: string
    contactNumber?: string
  }
) {
  try {
    const [newContact] = await db
      .insert(biddingCompaniesContacts)
      .values({
        biddingId,
        vendorId,
        contactName: contact.contactName,
        contactEmail: contact.contactEmail,
        contactNumber: contact.contactNumber || null,
      })
      .returning()

    return {
      success: true,
      data: newContact,
      message: '담당자가 성공적으로 추가되었습니다.'
    }
  } catch (error) {
    console.error('Failed to create bidding company contact:', error)
    return {
      success: false,
      error: '담당자 추가에 실패했습니다.'
    }
  }
}

export async function deleteBiddingCompanyContact(contactId: number) {
  try {
    await db
      .delete(biddingCompaniesContacts)
      .where(eq(biddingCompaniesContacts.id, contactId))

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

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

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

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

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

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

// 사전견적용 일반견적 생성 액션
export async function createPreQuoteRfqAction(input: {
  biddingId: number
  rfqType: string
  rfqTitle: string
  dueDate: Date
  picUserId: number
  projectId?: number
  remark?: string
  items: Array<{
    itemCode: string
    itemName: string
    materialCode?: string
    materialName?: string
    quantity: number
    uom: string
    remark?: string
  }>
  biddingConditions?: {
    paymentTerms?: string | null
    taxConditions?: string | null
    incoterms?: string | null
    incotermsOption?: string | null
    contractDeliveryDate?: string | null
    shippingPort?: string | null
    destinationPort?: string | null
    isPriceAdjustmentApplicable?: boolean | null
    sparePartOptions?: string | null
  }
  createdBy: number
  updatedBy: number
}) {
  try {
    // 일반견적 생성 서버 액션 및 필요한 스키마 import
    const { createGeneralRfqAction } = await import('@/lib/rfq-last/service')
    const { rfqLastDetails, rfqLastVendorResponses, rfqLastVendorResponseHistory } = await import('@/db/schema')
    
    // 일반견적 생성
    const result = await createGeneralRfqAction({
      rfqType: input.rfqType,
      rfqTitle: input.rfqTitle,
      dueDate: input.dueDate,
      picUserId: input.picUserId,
      projectId: input.projectId,
      remark: input.remark || '',
      items: input.items.map(item => ({
        itemCode: item.itemCode,
        itemName: item.itemName,
        quantity: item.quantity,
        uom: item.uom,
        remark: item.remark,
        materialCode: item.materialCode,
        materialName: item.materialName,
      })),
      createdBy: input.createdBy,
      updatedBy: input.updatedBy,
    })

    if (!result.success || !result.data) {
      return {
        success: false,
        error: result.error || '사전견적용 일반견적 생성에 실패했습니다',
      }
    }

    const rfqId = result.data.id
    const conditions = input.biddingConditions

    // 입찰 조건을 RFQ 조건으로 매핑
    const mapBiddingConditionsToRfqConditions = () => {
      if (!conditions) {
        return {
          currency: 'KRW',
          paymentTermsCode: undefined,
          incotermsCode: undefined,
          incotermsDetail: undefined,
          deliveryDate: undefined,
          taxCode: undefined,
          placeOfShipping: undefined,
          placeOfDestination: undefined,
          materialPriceRelatedYn: false,
          sparepartYn: false,
          sparepartDescription: undefined,
        }
      }

      // contractDeliveryDate 문자열을 Date로 변환 (timestamp 타입용)
      let deliveryDate: Date | undefined = undefined
      if (conditions.contractDeliveryDate) {
        try {
          const date = new Date(conditions.contractDeliveryDate)
          if (!isNaN(date.getTime())) {
            deliveryDate = date
          }
        } catch (error) {
          console.warn('Failed to parse contractDeliveryDate:', error)
        }
      }

      return {
        currency: 'KRW', // 기본값
        paymentTermsCode: conditions.paymentTerms || undefined,
        incotermsCode: conditions.incoterms || undefined,
        incotermsDetail: conditions.incotermsOption || undefined,
        deliveryDate: deliveryDate, // timestamp 타입 (rfqLastDetails용)
        vendorDeliveryDate: deliveryDate, // date 타입 (rfqLastVendorResponses용)
        taxCode: conditions.taxConditions || undefined,
        placeOfShipping: conditions.shippingPort || undefined,
        placeOfDestination: conditions.destinationPort || undefined,
        materialPriceRelatedYn: conditions.isPriceAdjustmentApplicable ?? false,
        sparepartYn: !!conditions.sparePartOptions, // sparePartOptions가 있으면 true
        sparepartDescription: conditions.sparePartOptions || undefined,
      }
    }

    const rfqConditions = mapBiddingConditionsToRfqConditions()

    // 입찰에 참여한 업체 목록 조회
    const vendorsResult = await getBiddingVendors(input.biddingId)
    if (!vendorsResult.success || !vendorsResult.data || vendorsResult.data.length === 0) {
      return {
        success: true,
        message: '사전견적용 일반견적이 생성되었습니다. (참여 업체 없음)',
        data: {
          rfqCode: result.data.rfqCode,
          rfqId: result.data.id,
        },
      }
    }

    // 각 업체에 대해 rfqLastDetails와 rfqLastVendorResponses 생성
    await db.transaction(async (tx) => {
      for (const vendor of vendorsResult.data) {
        if (!vendor.companyId) continue

        // 1. rfqLastDetails 생성 (구매자 제시 조건)
        const [rfqDetail] = await tx
          .insert(rfqLastDetails)
          .values({
            rfqsLastId: rfqId,
            vendorsId: vendor.companyId,
            currency: rfqConditions.currency,
            paymentTermsCode: rfqConditions.paymentTermsCode || null,
            incotermsCode: rfqConditions.incotermsCode || null,
            incotermsDetail: rfqConditions.incotermsDetail || null,
            deliveryDate: rfqConditions.deliveryDate || null,
            taxCode: rfqConditions.taxCode || null,
            placeOfShipping: rfqConditions.placeOfShipping || null,
            placeOfDestination: rfqConditions.placeOfDestination || null,
            materialPriceRelatedYn: rfqConditions.materialPriceRelatedYn,
            sparepartYn: rfqConditions.sparepartYn,
            sparepartDescription: rfqConditions.sparepartDescription || null,
            updatedBy: input.updatedBy,
            createdBy: input.createdBy,
            isLatest: true,
          })
          .returning()

        // 2. rfqLastVendorResponses 생성 (초기 응답 레코드)
        const [vendorResponse] = await tx
          .insert(rfqLastVendorResponses)
          .values({
            rfqsLastId: rfqId,
            rfqLastDetailsId: rfqDetail.id,
            vendorId: vendor.companyId,
            status: '대기중',
            responseVersion: 1,
            isLatest: true,
            participationStatus: '미응답',
            currency: rfqConditions.currency,
            // 구매자 제시 조건을 벤더 제안 조건의 초기값으로 복사
            vendorCurrency: rfqConditions.currency,
            vendorPaymentTermsCode: rfqConditions.paymentTermsCode || null,
            vendorIncotermsCode: rfqConditions.incotermsCode || null,
            vendorIncotermsDetail: rfqConditions.incotermsDetail || null,
            vendorDeliveryDate: rfqConditions.vendorDeliveryDate || null,
            vendorTaxCode: rfqConditions.taxCode || null,
            vendorPlaceOfShipping: rfqConditions.placeOfShipping || null,
            vendorPlaceOfDestination: rfqConditions.placeOfDestination || null,
            vendorMaterialPriceRelatedYn: rfqConditions.materialPriceRelatedYn,
            vendorSparepartYn: rfqConditions.sparepartYn,
            vendorSparepartDescription: rfqConditions.sparepartDescription || null,
            createdBy: input.createdBy,
            updatedBy: input.updatedBy,
          })
          .returning()

        // 3. 이력 기록
        await tx
          .insert(rfqLastVendorResponseHistory)
          .values({
            vendorResponseId: vendorResponse.id,
            action: '생성',
            newStatus: '대기중',
            changeDetails: {
              action: '사전견적용 일반견적 생성',
              biddingId: input.biddingId,
              conditions: rfqConditions,
            },
            performedBy: input.createdBy,
          })
      }
    })

    return {
      success: true,
      message: `사전견적용 일반견적이 성공적으로 생성되었습니다. (${vendorsResult.data.length}개 업체 추가)`,
      data: {
        rfqCode: result.data.rfqCode,
        rfqId: result.data.id,
      },
    }
  } catch (error) {
    console.error('Failed to create pre-quote RFQ:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '사전견적용 일반견적 생성에 실패했습니다',
    }
  }
}

// 일반견적 RFQ 코드 미리보기 (rfq-last/service에서 재사용)
export async function previewGeneralRfqCode(picUserId: number): Promise<string> {
  try {
    const { previewGeneralRfqCode: previewCode } = await import('@/lib/rfq-last/service')
    return await previewCode(picUserId)
  } catch (error) {
    console.error('Failed to preview general RFQ code:', error)
    return 'F???00001'
  }
}

// 내정가 산정 기준 업데이트
export async function updateTargetPriceCalculationCriteria(
  biddingId: number,
  criteria: string,
  userId: string
) {
  try {
    const userName = await getUserNameById(userId)
    
    await db
      .update(biddings)
      .set({
        targetPriceCalculationCriteria: criteria.trim() || null,
        updatedAt: new Date(),
        updatedBy: userName,
      })
      .where(eq(biddings.id, biddingId))

    revalidatePath(`/evcp/bid/${biddingId}`)
    revalidatePath(`/evcp/bid/${biddingId}/items`)

    return {
      success: true,
      message: '내정가 산정 기준이 성공적으로 저장되었습니다.',
    }
  } catch (error) {
    console.error('Failed to update target price calculation criteria:', error)
    return {
      success: false,
      error: '내정가 산정 기준 저장에 실패했습니다.',
    }
  }
}

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

    return {
      templates
    };

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

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

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

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

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

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


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

// 선택된 vendor들의 businessSize 정보를 가져오는 함수
export async function getVendorsBusinessSize(vendorIds: number[]) {
  try {
    if (vendorIds.length === 0) {
      return {};
    }

    const result = await db
      .select({
        id: vendors.id,
        businessSize: vendors.businessSize,
      })
      .from(vendors)
      .where(inArray(vendors.id, vendorIds));

    // Map 형태로 변환하여 반환
    const businessSizeMap: Record<number, string | null> = {};
    result.forEach(vendor => {
      businessSizeMap[vendor.id] = vendor.businessSize;
    });

    return businessSizeMap;
  } catch (error) {
    console.error('Error getting vendors business size:', error);
    return {};
  }
}

// 차수증가 또는 재입찰 함수
export async function increaseRoundOrRebid(biddingId: number, userId: string | undefined, type: 'round_increase' | 'rebidding') {
  if (!userId) {
    return {
      success: false,
      error: '사용자 정보가 필요합니다.',
    }
  }

  try {
    const userName = await getUserNameById(userId)
    
    return await db.transaction(async (tx) => {
      // 1. 기존 입찰 정보 조회
      const [existingBidding] = await tx
        .select()
        .from(biddings)
        .where(eq(biddings.id, biddingId))
        .limit(1)
      
      if (!existingBidding) {
        return {
          success: false,
          error: '입찰 정보를 찾을 수 없습니다.'
        }
      }

      // 2. 입찰번호 생성 (타입에 따라 다르게 처리)
      let newBiddingNumber: string

      if (type === 'rebidding') {
        // 재입찰: 완전히 새로운 입찰번호 생성
        newBiddingNumber = await generateBiddingNumber(existingBidding.contractType, userId, tx)
      } else {
        // 차수증가: 기존 입찰번호에서 차수 증가
        const currentBiddingNumber = existingBidding.biddingNumber

        // 현재 입찰번호에서 차수 추출 (예: E00025-02 -> 02)
        const match = currentBiddingNumber.match(/-(\d+)$/)
        let currentRound = match ? parseInt(match[1]) : 1

        if (currentRound >= 3) {
          // -03 이상이면 새로운 번호 생성
          newBiddingNumber = await generateBiddingNumber(existingBidding.contractType, userId, tx)
        } else {
          // -02까지는 차수만 증가
          const baseNumber = currentBiddingNumber.split('-')[0]
          newBiddingNumber = `${baseNumber}-${String(currentRound + 1).padStart(2, '0')}`
        }
      }
      //원입찰번호는 -0n 제외하고 저장
      const originalBiddingNumber = existingBidding.biddingNumber.split('-')[0]

      // 3. 새로운 입찰 생성 (기존 정보 복제)
      const [newBidding] = await tx
        .insert(biddings)
        .values({
          biddingNumber: newBiddingNumber,
          originalBiddingNumber: originalBiddingNumber, // 원입찰번호 설정
          revision: 0,
          biddingSourceType: existingBidding.biddingSourceType,
          
          // 기본 정보 복제
          projectName: existingBidding.projectName,
          itemName: existingBidding.itemName,
          title: existingBidding.title,
          description: existingBidding.description,
          
          // 계약 정보 복제
          contractType: existingBidding.contractType,
          biddingType: existingBidding.biddingType,
          awardCount: existingBidding.awardCount,
          contractStartDate: existingBidding.contractStartDate,
          contractEndDate: existingBidding.contractEndDate,
          
          // 일정은 초기화 (새로 설정해야 함)
          preQuoteDate: null,
          biddingRegistrationDate: new Date(),
          submissionStartDate: null,
          submissionEndDate: null,
          evaluationDate: null,
          
          // 사양설명회
          hasSpecificationMeeting: existingBidding.hasSpecificationMeeting,
          
          // 예산 및 가격 정보 복제
          currency: existingBidding.currency,
          budget: existingBidding.budget,
          targetPrice: existingBidding.targetPrice,
          targetPriceCalculationCriteria: existingBidding.targetPriceCalculationCriteria,
          finalBidPrice: null, // 최종입찰가는 초기화
          
          // PR 정보 복제
          prNumber: existingBidding.prNumber,
          hasPrDocument: existingBidding.hasPrDocument,
          
          // 상태는 입찰생성으로 초기화
          status: 'bidding_generated',
          isPublic: existingBidding.isPublic,
          isUrgent: existingBidding.isUrgent,
          
          // 구매조직
          purchasingOrganization: existingBidding.purchasingOrganization,
          
          // 담당자 정보 복제
          bidPicId: existingBidding.bidPicId,
          bidPicName: existingBidding.bidPicName,
          bidPicCode: existingBidding.bidPicCode,
          supplyPicId: existingBidding.supplyPicId,
          supplyPicName: existingBidding.supplyPicName,
          supplyPicCode: existingBidding.supplyPicCode,
          
          remarks: `${type === 'round_increase' ? '차수증가' : '재입찰'}`,
          createdBy: userName,
          updatedBy: userName,
          ANFNR: existingBidding.ANFNR,
        })
        .returning()

      // 4. 입찰 조건 복제
      const [existingConditions] = await tx
        .select()
        .from(biddingConditions)
        .where(eq(biddingConditions.biddingId, biddingId))
        .limit(1)
      
      if (existingConditions) {
        await tx
          .insert(biddingConditions)
          .values({
            biddingId: newBidding.id,
            paymentTerms: existingConditions.paymentTerms,
            taxConditions: existingConditions.taxConditions,
            incoterms: existingConditions.incoterms,
            incotermsOption: existingConditions.incotermsOption,
            contractDeliveryDate: existingConditions.contractDeliveryDate,
            shippingPort: existingConditions.shippingPort,
            destinationPort: existingConditions.destinationPort,
            isPriceAdjustmentApplicable: existingConditions.isPriceAdjustmentApplicable,
            sparePartOptions: existingConditions.sparePartOptions,
          })
      }

      // 5. PR 아이템 복제
      const existingPrItems = await tx
        .select()
        .from(prItemsForBidding)
        .where(eq(prItemsForBidding.biddingId, biddingId))
      
      if (existingPrItems.length > 0) {
        await tx
          .insert(prItemsForBidding)
          .values(
            existingPrItems.map((item) => ({
              biddingId: newBidding.id,
              
              // 기본 정보
              itemNumber: item.itemNumber,
              projectId: item.projectId,
              projectInfo: item.projectInfo,
              itemInfo: item.itemInfo,
              shi: item.shi,
              prNumber: item.prNumber,
              
              // 자재 그룹 정보
              materialGroupNumber: item.materialGroupNumber,
              materialGroupInfo: item.materialGroupInfo,
              
              // 자재 정보
              materialNumber: item.materialNumber,
              materialInfo: item.materialInfo,
              
              // 납품 일정
              requestedDeliveryDate: item.requestedDeliveryDate,
              
              // 가격 정보
              annualUnitPrice: item.annualUnitPrice,
              currency: item.currency,
              
              // 수량 및 중량
              quantity: item.quantity,
              quantityUnit: item.quantityUnit,
              totalWeight: item.totalWeight,
              weightUnit: item.weightUnit,
              
              // 단위 정보
              priceUnit: item.priceUnit,
              purchaseUnit: item.purchaseUnit,
              materialWeight: item.materialWeight,
              
              // WBS 정보
              wbsCode: item.wbsCode,
              wbsName: item.wbsName,
              
              // Cost Center 정보
              costCenterCode: item.costCenterCode,
              costCenterName: item.costCenterName,
              
              // GL Account 정보
              glAccountCode: item.glAccountCode,
              glAccountName: item.glAccountName,
              
              // 내정가 정보
              targetUnitPrice: item.targetUnitPrice,
              targetAmount: item.targetAmount,
              targetCurrency: item.targetCurrency,
              
              // 예산 정보
              budgetAmount: item.budgetAmount,
              budgetCurrency: item.budgetCurrency,
              
              // 실적 정보
              actualAmount: item.actualAmount,
              actualCurrency: item.actualCurrency,
              
              // SPEC 문서 여부
              hasSpecDocument: item.hasSpecDocument,
              
              createdAt: new Date(),
              updatedAt: new Date(),
            }))
          )
      }

      // 6. 벤더 복제 (제출 정보는 초기화)
      const existingCompanies = await tx
        .select()
        .from(biddingCompanies)
        .where(eq(biddingCompanies.biddingId, biddingId))
      
      if (existingCompanies.length > 0) {
        await tx
          .insert(biddingCompanies)
          .values(
            existingCompanies.map((company) => ({
              biddingId: newBidding.id,
              companyId: company.companyId,
              invitedAt: new Date(),
              invitationStatus: 'pending' as const, // 초대 대기 상태로 초기화
              // 제출 정보는 초기화
              submittedAt: null,
              quotationPrice: null,
              quotationCurrency: null,
              quotationValidityDays: null,
              deliveryDate: null,
              remarks: null,
            }))
          )
      }

      // 7. 사양설명회 정보 복제 (있는 경우)
      if (existingBidding.hasSpecificationMeeting) {
        const [existingMeeting] = await tx
          .select()
          .from(specificationMeetings)
          .where(eq(specificationMeetings.biddingId, biddingId))
          .limit(1)

        if (existingMeeting) {
          await tx
            .insert(specificationMeetings)
            .values({
              biddingId: newBidding.id,
              meetingDate: existingMeeting.meetingDate,
              meetingTime: existingMeeting.meetingTime,
              location: existingMeeting.location,
              address: existingMeeting.address,
              contactPerson: existingMeeting.contactPerson,
              contactPhone: existingMeeting.contactPhone,
              contactEmail: existingMeeting.contactEmail,
              agenda: existingMeeting.agenda,
              materials: existingMeeting.materials,
              notes: existingMeeting.notes,
              isRequired: existingMeeting.isRequired,
            })
        }
      }

      // 9. 기존 입찰 상태 변경 (타입에 따라 다르게 설정)
      await tx
        .update(biddings)
        .set({
          status: type === 'round_increase' ? 'round_increase' : 'rebidding',
          updatedBy: userName,
          updatedAt: new Date(),
        })
        .where(eq(biddings.id, biddingId))

      // 10. 입찰공고문 정보 복제 (있는 경우)
      const [existingNotice] = await tx
        .select()
        .from(biddingNoticeTemplate)
        .where(eq(biddingNoticeTemplate.biddingId, biddingId))
        .limit(1)

      if (existingNotice) {
        await tx
          .insert(biddingNoticeTemplate)
          .values({
            biddingId: newBidding.id,
            title: existingNotice.title,
            content: existingNotice.content,
          })
      }

      // 11. 첨부파일 복제 (SHI용 및 협력업체용 첨부파일만)
      const existingDocuments = await tx
        .select()
        .from(biddingDocuments)
        .where(and(
          eq(biddingDocuments.biddingId, biddingId),
          // PR 아이템에 연결된 첨부파일은 제외 (SHI용과 협력업체용만 복제)
          isNull(biddingDocuments.prItemId),
          // SHI용(evaluation_doc) 또는 협력업체용(company_proposal) 문서만 복제
          or(
            eq(biddingDocuments.documentType, 'evaluation_doc'),
            eq(biddingDocuments.documentType, 'company_proposal')
          )
        ))

      if (existingDocuments.length > 0) {
        for (const doc of existingDocuments) {
          try {
            // 기존 파일을 Buffer로 읽어서 File 객체 생성
            const { readFileSync, existsSync } = await import('fs')
            const { join } = await import('path')

            const oldFilePath = doc.filePath.startsWith('/uploads/')
              ? join(process.cwd(), 'public', doc.filePath)
              : doc.filePath

            if (!existsSync(oldFilePath)) {
              console.warn(`원본 파일이 존재하지 않음: ${oldFilePath}`)
              continue
            }

            // 파일 내용을 읽어서 Buffer 생성
            const fileBuffer = readFileSync(oldFilePath)

            // Buffer를 File 객체로 변환 (브라우저 File API 시뮬레이션)
            const file = new File([fileBuffer], doc.fileName, {
              type: doc.mimeType || 'application/octet-stream'
            })

            // saveFile을 사용하여 새 파일 저장
            const saveResult = await saveFile({
              file,
              directory: `biddings/${newBidding.id}/attachments/${doc.documentType === 'evaluation_doc' ? 'shi' : 'vendor'}`,
              originalName: `copied_${Date.now()}_${doc.fileName}`,
              userId: userName
            })

            if (saveResult.success) {
              // 새 첨부파일 레코드 삽입
              await tx.insert(biddingDocuments).values({
                biddingId: newBidding.id,
                companyId: doc.companyId,
                prItemId: null as any,
                specificationMeetingId: null,
                documentType: doc.documentType,
                fileName: saveResult.fileName!,
                originalFileName: doc.originalFileName,
                fileSize: saveResult.fileSize!,
                mimeType: doc.mimeType,
                filePath: saveResult.publicPath!,
                title: `${doc.documentType === 'evaluation_doc' ? 'SHI용' : '협력업체용'} 첨부파일 복제 - ${doc.originalFileName}`,
                description: doc.description,
                isPublic: doc.isPublic,
                isRequired: doc.isRequired,
                uploadedBy: userName,
                uploadedAt: new Date(),
              })
            } else {
              console.error(`첨부파일 저장 실패 (${doc.fileName}):`, saveResult.error)
            }

          } catch (error) {
            console.error(`첨부파일 복제 실패 (${doc.fileName}):`, error)
          }
        }
      }

      revalidatePath('/bidding')
      revalidatePath(`/bidding/${biddingId}`) // 기존 입찰 페이지도 갱신
      revalidatePath(`/bidding/${newBidding.id}`)

      return {
        success: true,
        message: `${type === 'round_increase' ? '차수증가' : '재입찰'}가 완료되었습니다.`,
        biddingId: newBidding.id,
        biddingNumber: newBiddingNumber
      }
    })
  } catch (error) {
    console.error('차수증가/재입찰 실패:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '차수증가/재입찰 중 오류가 발생했습니다.'
    }
  }
}

/**
 * 벤더의 담당자 목록 조회
 */
export async function getVendorContactsByVendorId(vendorId: number) {
  try {
    const contacts = await db
      .select({
        id: vendorContacts.id,
        vendorId: vendorContacts.vendorId,
        contactName: vendorContacts.contactName,
        contactPosition: vendorContacts.contactPosition,
        contactDepartment: vendorContacts.contactDepartment,
        contactTask: vendorContacts.contactTask,
        contactEmail: vendorContacts.contactEmail,
        contactPhone: vendorContacts.contactPhone,
        isPrimary: vendorContacts.isPrimary,
        createdAt: vendorContacts.createdAt,
        updatedAt: vendorContacts.updatedAt,
      })
      .from(vendorContacts)
      .where(eq(vendorContacts.vendorId, vendorId))
      .orderBy(vendorContacts.isPrimary, vendorContacts.contactName)

    return {
      success: true,
      data: contacts
    }
  } catch (error) {
    console.error('Failed to get vendor contacts:', error)
    return {
      success: false,
      error: error instanceof Error ? error.message : '담당자 목록 조회에 실패했습니다.'
    }
  }
}

// ═══════════════════════════════════════════════════════════════
// bid-receive 페이지용 함수들
// ═══════════════════════════════════════════════════════════════

// bid-receive: 입찰서접수및마감 페이지용 입찰 목록 조회
export async function getBiddingsForReceive(input: GetBiddingsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 기본 필터 조건들 (입찰서접수및마감에 적합한 상태만)
    const basicConditions: SQL<unknown>[] = []

    // 입찰서 접수 및 마감과 관련된 상태만 필터링
    // 'received_quotation', 'bidding_opened', 'bidding_closed' 상태만 조회
    basicConditions.push(
      or(
        eq(biddings.status, 'received_quotation'),
        eq(biddings.status, 'bidding_opened'),
        eq(biddings.status, 'bidding_closed'),
        eq(biddings.status, 'evaluation_of_bidding'),
      )!
    )

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

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

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

    if (input.prNumber) {
      basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`))
    }

    if (input.managerName) {
      basicConditions.push(
        or(
          ilike(biddings.bidPicName, `%${input.managerName}%`),
          ilike(biddings.supplyPicName, `%${input.managerName}%`)
        )!
      )
    }

    // 날짜 필터들
    if (input.submissionDateFrom) {
      basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom)))
    }
    if (input.submissionDateTo) {
      basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo)))
    }

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

    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

    // 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined
    if (input.search) {
      const s = `%${input.search}%`
      const searchConditions = [
        ilike(biddings.biddingNumber, s),
        ilike(biddings.title, s),
        ilike(biddings.projectName, s),
        ilike(biddings.prNumber, s),
        ilike(biddings.bidPicName, s),
        ilike(biddings.supplyPicName, s),
      ]
      globalWhere = or(...searchConditions)
    }

    const whereConditions: SQL<unknown>[] = []
    if (basicWhere) whereConditions.push(basicWhere)
    if (globalWhere) whereConditions.push(globalWhere)

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined

    // 전체 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(biddings)
      .where(finalWhere)

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

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

    // 정렬
    const orderByColumns = input.sort.map((sort) => {
      const column = sort.id as keyof typeof biddings.$inferSelect
      return sort.desc ? desc(biddings[column]) : asc(biddings[column])
    })

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

    // bid-receive 페이지용 데이터 조회 (참여 현황 제외하고 기본 정보만)
    const biddingData = await db
      .select({
        // 기본 입찰 정보
        id: biddings.id,
        biddingNumber: biddings.biddingNumber,
        originalBiddingNumber: biddings.originalBiddingNumber,
        title: biddings.title,
        status: biddings.status,
        contractType: biddings.contractType,
        prNumber: biddings.prNumber,
        submissionStartDate: biddings.submissionStartDate,
        submissionEndDate: biddings.submissionEndDate,
        bidPicName: biddings.bidPicName,
        supplyPicName: biddings.supplyPicName,
        createdBy: biddings.createdBy,
        createdAt: biddings.createdAt,
        updatedAt: biddings.updatedAt,

        // 개찰 정보
        openedAt: biddings.openedAt,
        openedBy: biddings.openedBy,
      })
      .from(biddings)
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset)

    // 각 입찰에 대한 참여 현황 카운트 계산
    const data = await Promise.all(
      biddingData.map(async (bidding) => {
        const participantCounts = await getParticipantCountsForBidding(bidding.id)
        return {
          ...bidding,
          ...participantCounts
        }
      })
    )

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

    return { data, pageCount, total }

  } catch (err) {
    console.error("Error in getBiddingsForReceive:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

// ═══════════════════════════════════════════════════════════════
// bid-selection 페이지용 함수들
// ═══════════════════════════════════════════════════════════════

// bid-selection: 개찰 이후 입찰가 및 정보 확인 페이지용 입찰 목록 조회
export async function getBiddingsForSelection(input: GetBiddingsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 기본 필터 조건들 (개찰 이후 상태만)
    const basicConditions: SQL<unknown>[] = []

    // 개찰 이후 상태만 필터링
    // 'bidding_opened', 'bidding_closed', 'evaluation_of_bidding', 'vendor_selected' 상태만 조회
    basicConditions.push(
      or(
        eq(biddings.status, 'bidding_opened'),
        eq(biddings.status, 'bidding_closed'),
        eq(biddings.status, 'evaluation_of_bidding'),
        eq(biddings.status, 'vendor_selected')
      )!
    )

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

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

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

    if (input.prNumber) {
      basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`))
    }

    if (input.managerName) {
      basicConditions.push(
        or(
          ilike(biddings.bidPicName, `%${input.managerName}%`),
          ilike(biddings.supplyPicName, `%${input.managerName}%`)
        )!
      )
    }

    // 날짜 필터들
    if (input.submissionDateFrom) {
      basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom)))
    }
    if (input.submissionDateTo) {
      basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo)))
    }

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

    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

    // 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined
    if (input.search) {
      const s = `%${input.search}%`
      const searchConditions = [
        ilike(biddings.biddingNumber, s),
        ilike(biddings.title, s),
        ilike(biddings.projectName, s),
        ilike(biddings.prNumber, s),
        ilike(biddings.bidPicName, s),
        ilike(biddings.supplyPicName, s),
      ]
      globalWhere = or(...searchConditions)
    }

    const whereConditions: SQL<unknown>[] = []
    if (basicWhere) whereConditions.push(basicWhere)
    if (globalWhere) whereConditions.push(globalWhere)

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined

    // 전체 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(biddings)
      .where(finalWhere)

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

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

    // 정렬
    const orderByColumns = input.sort.map((sort) => {
      const column = sort.id as keyof typeof biddings.$inferSelect
      return sort.desc ? desc(biddings[column]) : asc(biddings[column])
    })

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

    // bid-selection 페이지용 데이터 조회
    const data = await db
      .select({
        // 기본 입찰 정보
        id: biddings.id,
        biddingNumber: biddings.biddingNumber,
        originalBiddingNumber: biddings.originalBiddingNumber,
        title: biddings.title,
        status: biddings.status,
        contractType: biddings.contractType,
        prNumber: biddings.prNumber,
        submissionStartDate: biddings.submissionStartDate,
        submissionEndDate: biddings.submissionEndDate,
        bidPicName: biddings.bidPicName,
        supplyPicName: biddings.supplyPicName,
        createdBy: biddings.createdBy,
        createdAt: biddings.createdAt,
        updatedAt: biddings.updatedAt,
      })
      .from(biddings)
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset)

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

    return { data, pageCount, total }

  } catch (err) {
    console.error("Error in getBiddingsForSelection:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

// ═══════════════════════════════════════════════════════════════
// bid-failure 페이지용 함수들
// ═══════════════════════════════════════════════════════════════

// bid-failure: 유찰된 입찰 확인 페이지용 입찰 목록 조회
export async function getBiddingsForFailure(input: GetBiddingsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage

    // 기본 필터 조건들 (유찰된 입찰만)
    const basicConditions: SQL<unknown>[] = []

    // 유찰된 상태만 필터링, 폐찰된 상태도 포함
    basicConditions.push(
      or(
        eq(biddings.status, 'bidding_disposal'),
        eq(biddings.status, 'approval_pending'),
        eq(biddings.status, 'bid_closure')
      )!
    )

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

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

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

    if (input.prNumber) {
      basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`))
    }

    if (input.managerName) {
      basicConditions.push(
        or(
          ilike(biddings.bidPicName, `%${input.managerName}%`),
          ilike(biddings.supplyPicName, `%${input.managerName}%`)
        )!
      )
    }

    // 날짜 필터들
    if (input.createdAtFrom) {
      basicConditions.push(gte(biddings.createdAt, new Date(input.createdAtFrom)))
    }
    if (input.createdAtTo) {
      basicConditions.push(lte(biddings.createdAt, new Date(input.createdAtTo)))
    }

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

    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

    // 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined
    if (input.search) {
      const s = `%${input.search}%`
      const searchConditions = [
        ilike(biddings.biddingNumber, s),
        ilike(biddings.title, s),
        ilike(biddings.projectName, s),
        ilike(biddings.prNumber, s),
        ilike(biddings.bidPicName, s),
        ilike(biddings.supplyPicName, s),
      ]
      globalWhere = or(...searchConditions)
    }

    const whereConditions: SQL<unknown>[] = []
    if (basicWhere) whereConditions.push(basicWhere)
    if (globalWhere) whereConditions.push(globalWhere)

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined

    // 전체 개수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(biddings)
      .where(finalWhere)

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

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

    // 정렬
    const orderByColumns = input.sort.map((sort) => {
      const column = sort.id as keyof typeof biddings.$inferSelect
      return sort.desc ? desc(biddings[column]) : asc(biddings[column])
    })

    if (orderByColumns.length === 0) {
      orderByColumns.push(desc(biddings.updatedAt)) // 유찰된 최신순
    }

    // bid-failure 페이지용 데이터 조회 (폐찰 문서 정보 포함)
    const rawData = await db
      .select({
        // 기본 입찰 정보
        id: biddings.id,
        biddingNumber: biddings.biddingNumber,
        originalBiddingNumber: biddings.originalBiddingNumber,
        title: biddings.title,
        status: biddings.status,
        contractType: biddings.contractType,
        prNumber: biddings.prNumber,

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

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

        // 담당자 정보
        bidPicName: biddings.bidPicName,
        supplyPicName: biddings.supplyPicName,

        // 유찰 정보 (업데이트 일시를 유찰일로 사용)
        disposalDate: biddings.updatedAt, // 유찰일
        disposalUpdatedAt: biddings.updatedAt, // 폐찰수정일
        disposalUpdatedBy: biddings.updatedBy, // 폐찰수정자

        // 폐찰 정보
        closureReason: biddings.description, // 폐찰사유

        // 폐찰 문서 정보
        documentId: biddingDocuments.id,
        documentFileName: biddingDocuments.fileName,
        documentOriginalFileName: biddingDocuments.originalFileName,
        documentFilePath: biddingDocuments.filePath,

        // 기타 정보
        createdBy: biddings.createdBy,
        createdAt: biddings.createdAt,
        updatedAt: biddings.updatedAt,
        updatedBy: biddings.updatedBy,
      })
      .from(biddings)
      .leftJoin(biddingDocuments, and(
        eq(biddingDocuments.biddingId, biddings.id),
        eq(biddingDocuments.documentType, 'evaluation_doc'), // 폐찰 문서
        eq(biddingDocuments.isPublic, false) // 폐찰 문서는 비공개
      ))
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset)

    // 데이터를 그룹화하여 폐찰 문서들을 배열로 묶기
    const groupedData = rawData.reduce((acc, item) => {
      const existing = acc.find(b => b.id === item.id)
      if (existing) {
        // 이미 존재하는 입찰이면 문서 추가
        if (item.documentId) {
          existing.closureDocuments.push({
            id: item.documentId,
            fileName: item.documentFileName!,
            originalFileName: item.documentOriginalFileName!,
            filePath: item.documentFilePath!
          })
        }
      } else {
        // 새로운 입찰 추가
        acc.push({
          id: item.id,
          biddingNumber: item.biddingNumber,
          originalBiddingNumber: item.originalBiddingNumber,
          title: item.title,
          status: item.status,
          contractType: item.contractType,
          prNumber: item.prNumber,
          targetPrice: item.targetPrice,
          currency: item.currency,
          biddingRegistrationDate: item.biddingRegistrationDate,
          submissionStartDate: item.submissionStartDate,
          submissionEndDate: item.submissionEndDate,
          bidPicName: item.bidPicName,
          supplyPicName: item.supplyPicName,
          disposalDate: item.disposalDate,
          disposalUpdatedAt: item.disposalUpdatedAt,
          disposalUpdatedBy: item.disposalUpdatedBy,
          closureReason: item.closureReason,
          closureDocuments: item.documentId ? [{
            id: item.documentId,
            fileName: item.documentFileName!,
            originalFileName: item.documentOriginalFileName!,
            filePath: item.documentFilePath!
          }] : [],
          createdBy: item.createdBy,
          createdAt: item.createdAt,
          updatedAt: item.updatedAt,
          updatedBy: item.updatedBy,
        })
      }
      return acc
    }, [] as any[])

    const data = groupedData

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

    return { data, pageCount, total }

  } catch (err) {
    console.error("Error in getBiddingsForFailure:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}