(최겸) 구매 입찰 수정

1 files changed, 4 insertions, 386 deletions

diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts
index d657086f..3db9108d 100644
--- a/db/schema/bidding.ts
+++ b/db/schema/bidding.ts
@@ -74,7 +74,7 @@ export const biddingTypeEnum = pgEnum('bidding_type', [
   'other'            // 기타(직접입력)
 ])
 
-// 4. 낙찰수 enum
+// 4. 낙찰업체 수 enum
 export const awardCountEnum = pgEnum('award_count', [
   'single',    // 단수
   'multiple'   // 복수
@@ -114,13 +114,13 @@ export const documentTypeEnum = pgEnum('document_type', [
   'specification',    // 사양서
   'specification_meeting',    // 사양설명회
   'contract_draft',   // 계약서 초안
-  'company_proposal', // 업체 제안서
+  'company_proposal', // 협력업체용 첨부파일
   'financial_doc',    // 재무 관련 문서
   'technical_doc',    // 기술 관련 문서
   'certificate',      // 인증서류
   'pr_document',      // PR 문서
   'spec_document',    // SPEC 문서
-  'evaluation_doc',   // 평가 관련 문서
+  'evaluation_doc',   // SHI용 첨부파일 (평가 관련 문서)
   'bid_attachment',   // 입찰 첨부파일
   'selection_result', // 선정결과 첨부파일
   'other'            // 기타
@@ -166,7 +166,7 @@ export const biddings = pgTable('biddings', {
   contractType: contractTypeEnum('contract_type').notNull(), // 계약구분
   noticeType: varchar('notice_type', { length: 50 }).default('standard'), // 입찰공고 타입
   biddingType: biddingTypeEnum('bidding_type').notNull(), // 입찰유형
-  awardCount: awardCountEnum('award_count').default('single'), // 낙찰수
+  awardCount: awardCountEnum('award_count').default('single'), // 낙찰업체 수
   // contractPeriod: varchar('contract_period', { length: 100 }), // 계약기간
   //시작일 (기본값: 현재 날짜)
   contractStartDate: date('contract_start_date').defaultNow(),
@@ -752,385 +752,3 @@ export const weightUnitLabels = {
   lb: 'lb',
   g: 'g'
 } as const
-
-
-// export const biddingListView = pgView('bidding_list_view').as((qb) =>
-//   qb
-//     .select({
-//       // ═══════════════════════════════════════════════════════════════
-//       // 기본 입찰 정보
-//       // ═══════════════════════════════════════════════════════════════
-//       id: biddings.id,
-//       biddingNumber: biddings.biddingNumber,
-//       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,
-//       managerName: biddings.managerName,
-//       managerEmail: biddings.managerEmail,
-//       managerPhone: biddings.managerPhone,
-      
-//       // ═══════════════════════════════════════════════════════════════
-//       // 메타 정보
-//       // ═══════════════════════════════════════════════════════════════
-//       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'),
-      
-//       // PR 문서 목록 (최대 5개 문서명)
-//       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'),
-      
-//       participantParticipated: sql<number>`
-//         COALESCE((
-//           SELECT count(*) 
-//           FROM bidding_companies 
-//           WHERE bidding_id = ${biddings.id} 
-//           AND invitation_status = 'submitted'
-//         ), 0)
-//       `.as('participant_participated'),
-      
-//       participantDeclined: sql<number>`
-//         COALESCE((
-//           SELECT count(*) 
-//           FROM bidding_companies 
-//           WHERE bidding_id = ${biddings.id} 
-//           AND invitation_status = 'declined'
-//         ), 0)
-//       `.as('participant_declined'),
-      
-//       participantPending: sql<number>`
-//         COALESCE((
-//           SELECT count(*) 
-//           FROM bidding_companies 
-//           WHERE bidding_id = ${biddings.id} 
-//           AND invitation_status IN ('pending', 'sent')
-//         ), 0)
-//       `.as('participant_pending'),
-      
-//       participantAccepted: sql<number>`
-//         COALESCE((
-//           SELECT count(*) 
-//           FROM bidding_companies 
-//           WHERE bidding_id = ${biddings.id} 
-//           AND invitation_status = '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 = '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}`
-//     )
-// )
-
-// export type BiddingListView = typeof biddingListView.$inferSelect