(최겸) 구매 입찰 수정

1 files changed, 74 insertions, 409 deletions

diff --git a/lib/bidding/service.ts b/lib/bidding/service.ts
index 521f4c33..489268c6 100644
--- a/lib/bidding/service.ts
+++ b/lib/bidding/service.ts
@@ -33,7 +33,8 @@ import {
     like,
     notInArray,
     inArray,
-    isNull
+    isNull,
+    isNotNull
 } from 'drizzle-orm'
 import { revalidatePath } from 'next/cache'
 import { filterColumns } from '@/lib/filter-columns'
@@ -390,429 +391,36 @@ export async function getBiddings(input: GetBiddingsSchema) {
                 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)
@@ -995,8 +603,8 @@ export async function getParticipantCountsForBidding(biddingId: number) {
 
     const expected = expectedResult[0]?.count || 0
 
-    // 참여 완료 수
-    const participatedResult = await db
+    // 최종 제출 완료 수 (Strict)
+    const finalSubmittedResult = await db
       .select({ count: count() })
       .from(biddingCompanies)
       .where(and(
@@ -1004,6 +612,23 @@ export async function getParticipantCountsForBidding(biddingId: number) {
         eq(biddingCompanies.invitationStatus, 'bidding_submitted')
       ))
 
+    const finalSubmitted = finalSubmittedResult[0]?.count || 0
+
+    // 참여 완료 수 (Broad: 최종제출 OR (참여수락 AND 견적제출))
+    const participatedResult = await db
+      .select({ count: count() })
+      .from(biddingCompanies)
+      .where(and(
+        eq(biddingCompanies.biddingId, biddingId),
+        or(
+          eq(biddingCompanies.invitationStatus, 'bidding_submitted'),
+          and(
+            eq(biddingCompanies.invitationStatus, 'bidding_accepted'),
+            isNotNull(biddingCompanies.finalQuoteAmount)
+          )
+        )
+      ))
+
     const participated = participatedResult[0]?.count || 0
 
     // 거부/취소 수
@@ -1020,7 +645,8 @@ export async function getParticipantCountsForBidding(biddingId: number) {
 
     const declined = declinedResult[0]?.count || 0
 
-    // 대기중 수
+    // 대기중 수 (Expected - Participated - Declined)
+    // 또는: Pending OR Sent OR (Accepted AND Quote IS NULL)
     const pendingResult = await db
       .select({ count: count() })
       .from(biddingCompanies)
@@ -1029,7 +655,10 @@ export async function getParticipantCountsForBidding(biddingId: number) {
         or(
           eq(biddingCompanies.invitationStatus, 'pending'),
           eq(biddingCompanies.invitationStatus, 'bidding_sent'),
-          eq(biddingCompanies.invitationStatus, 'bidding_accepted')
+          and(
+            eq(biddingCompanies.invitationStatus, 'bidding_accepted'),
+            isNull(biddingCompanies.finalQuoteAmount)
+          )
         )
       ))
 
@@ -1039,7 +668,8 @@ export async function getParticipantCountsForBidding(biddingId: number) {
       participantExpected: expected,
       participantParticipated: participated,
       participantDeclined: declined,
-      participantPending: pending
+      participantPending: pending,
+      participantFinalSubmitted: finalSubmitted
     }
   } catch (error) {
     console.error('Error in getParticipantCountsForBidding:', error)
@@ -1047,7 +677,8 @@ export async function getParticipantCountsForBidding(biddingId: number) {
       participantExpected: 0,
       participantParticipated: 0,
       participantDeclined: 0,
-      participantPending: 0
+      participantPending: 0,
+      participantFinalSubmitted: 0
     }
   }
 }
@@ -1614,8 +1245,8 @@ export async function updateBidding(input: UpdateBiddingInput, userId: string) {
       .set(updateData)
       .where(eq(biddings.id, input.id))
 
-    revalidatePath('/admin/biddings')
-    revalidatePath(`/admin/biddings/${input.id}`)
+    revalidatePath('/evcp/bid')
+    revalidatePath(`/evcp/bid/${input.id}/info`)
 
     return {
       success: true,
@@ -1651,7 +1282,7 @@ export async function deleteBidding(id: number) {
       .delete(biddings)
       .where(eq(biddings.id, id))
 
-    revalidatePath('/admin/biddings')
+    revalidatePath('/evcp/bid')
 
     return {
       success: true,
@@ -3539,14 +3170,48 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u
               invitedAt: new Date(),
               invitationStatus: 'pending' as const, // 초대 대기 상태로 초기화
               // 제출 정보는 초기화
-              submittedAt: null,
-              quotationPrice: null,
-              quotationCurrency: null,
-              quotationValidityDays: null,
-              deliveryDate: null,
-              remarks: null,
+              finalQuoteAmount: null,
+              finalQuoteSubmittedAt: null,
+              isFinalSubmission: false,
+              isWinner: null,
+              awardRatio: null,
+              respondedAt: null,
+              
+              // 연락처 정보 복제
+              contactPerson: company.contactPerson,
+              contactEmail: company.contactEmail,
+              contactPhone: company.contactPhone,
+              
+              // 본입찰 대상 선정 여부 복제 (중요: 차수증가 시에도 대상 업체 유지)
+              isPreQuoteSelected: company.isPreQuoteSelected,
+              // 본입찰 참여 여부 초기화 (다시 참여해야 함)
+              isBiddingParticipated: null,
+              // 본입찰 초대 여부 초기화 (다시 초대해야 함)
+              isBiddingInvited: false,
+              
+              notes: company.notes,
             }))
           )
+          
+        // 6-1. 벤더 담당자 복제 (추가)
+        const existingContacts = await tx
+          .select()
+          .from(biddingCompaniesContacts)
+          .where(eq(biddingCompaniesContacts.biddingId, biddingId))
+
+        if (existingContacts.length > 0) {
+          await tx
+            .insert(biddingCompaniesContacts)
+            .values(
+              existingContacts.map((contact) => ({
+                biddingId: newBidding.id,
+                vendorId: contact.vendorId,
+                contactName: contact.contactName,
+                contactEmail: contact.contactEmail,
+                contactNumber: contact.contactNumber,
+              }))
+            )
+        }
       }
 
       // 7. 사양설명회 정보 복제 (있는 경우)