diff options
Diffstat (limited to 'db/schema/bidding.ts')
| -rw-r--r-- | db/schema/bidding.ts | 909 |
1 files changed, 909 insertions, 0 deletions
diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts new file mode 100644 index 00000000..96c189c6 --- /dev/null +++ b/db/schema/bidding.ts @@ -0,0 +1,909 @@ +// schema.ts (입찰 시스템 전체 스키마 - 실무 요구사항 최종 반영) + +import { + pgTable, + serial, + varchar, + text, + timestamp, + integer, + decimal, + boolean, + pgEnum, + uuid, + date, pgView +} from 'drizzle-orm/pg-core' +import { Vendor, vendors } from './vendors' +import { relations , sql} from 'drizzle-orm'; +import { projects } from './projects'; + +// 입찰공고문 템플릿 (기존) +export const biddingNoticeTemplate = pgTable('bidding_notice_template', { + id: serial('id').primaryKey(), + type: varchar('type', { length: 50 }).notNull().default('standard'), // 'standard' 고정 + title: varchar('title', { length: 200 }).notNull().default('표준 입찰공고문'), + content: text('content').notNull().default(''), + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +export type BiddingNoticeTemplate = typeof biddingNoticeTemplate.$inferSelect +export type NewBiddingNoticeTemplate = typeof biddingNoticeTemplate.$inferInsert + +// 1. 입찰 상태 enum (실무 워크플로우 반영) +export const biddingStatusEnum = pgEnum('bidding_status', [ + 'bidding_generated', // 입찰생성 + 'request_for_quotation', // 사전견적요청 + 'received_quotation', // 사전견적접수 + 'set_target_price', // 내정가 산정 + 'bidding_opened', // 입찰공고 + 'bidding_closed', // 입찰마감 + 'evaluation_of_bidding', // 입찰평가중 + 'bidding_disposal', // 유찰 + 'vendor_selected' // 업체선정 +]) + +// 2. 계약구분 enum +export const contractTypeEnum = pgEnum('contract_type', [ + 'unit_price', // 단가계약 + 'general', // 일반계약 + 'sale' // 매각계약 +]) + +// 3. 입찰유형 enum +export const biddingTypeEnum = pgEnum('bidding_type', [ + 'equipment', // 기자재 + 'construction', // 공사 + 'service', // 용역 + 'lease', // 임차 + 'steel_stock', // 형강스톡 + 'piping', // 배관 + 'transport', // 운송 + 'waste', // 폐기물 + 'sale' // 매각 +]) + +// 4. 낙찰수 enum +export const awardCountEnum = pgEnum('award_count', [ + 'single', // 단수 + 'multiple' // 복수 +]) + +// 5. 업체 초대/응답 상태 enum +export const invitationStatusEnum = pgEnum('invitation_status', [ + 'pending', // 초대 대기 + 'sent', // 초대 발송 + 'accepted', // 참여 수락 + 'declined', // 참여 거절 + 'submitted' // 견적 제출 완료 +]) + +// 6. 문서 타입 enum +export const documentTypeEnum = pgEnum('document_type', [ + 'notice', // 입찰공고서 + 'specification', // 사양서 + 'specification_meeting', // 사양설명회 + 'contract_draft', // 계약서 초안 + 'company_proposal', // 업체 제안서 + 'financial_doc', // 재무 관련 문서 + 'technical_doc', // 기술 관련 문서 + 'certificate', // 인증서류 + 'pr_document', // PR 문서 + 'spec_document', // SPEC 문서 + 'evaluation_doc', // 평가 관련 문서 + 'bid_attachment', // 입찰 첨부파일 + 'other' // 기타 +]) + +// 7. 수량 단위 enum +export const quantityUnitEnum = pgEnum('quantity_unit', [ + 'ea', // 개 + 'set', // 세트 + 'kg', // 킬로그램 + 'ton', // 톤 + 'm', // 미터 + 'm2', // 제곱미터 + 'm3', // 세제곱미터 + 'lot', // 로트 + 'other' // 기타 +]) + +// 8. 중량 단위 enum +export const weightUnitEnum = pgEnum('weight_unit', [ + 'kg', // 킬로그램 + 'ton', // 톤 + 'lb', // 파운드 + 'g' // 그램 +]) + +// 8. 입찰 메인 테이블 (실무 요구사항 반영) +export const biddings = pgTable('biddings', { + id: serial('id').primaryKey(), + biddingNumber: varchar('bidding_number', { length: 50 }).unique().notNull(), // 입찰 No. + revision: integer('revision').default(0), // Rev. + projectId: integer('project_id').references(() => projects.id), + + // 기본 정보 + projectName: varchar('project_name', { length: 300 }), // 프로젝트명 + itemName: varchar('item_name', { length: 300 }), // 품목명 + title: varchar('title', { length: 300 }).notNull(), // 입찰명 + description: text('description'), + content: text('content'), // 입찰공고 내용 (HTML) - 표준 템플릿에서 수정된 내용 + + // 계약 정보 + contractType: contractTypeEnum('contract_type').notNull(), // 계약구분 + biddingType: biddingTypeEnum('bidding_type').notNull(), // 입찰유형 + awardCount: awardCountEnum('award_count').default('single'), // 낙찰수 + contractPeriod: varchar('contract_period', { length: 100 }), // 계약기간 + + // 일정 관리 + preQuoteDate: date('pre_quote_date'), // 사전견적일 + biddingRegistrationDate: date('bidding_registration_date'), // 입찰등록일 + submissionStartDate: timestamp('submission_start_date'), // 입찰서제출기간 시작 + submissionEndDate: timestamp('submission_end_date'), // 입찰서제출기간 끝 + evaluationDate: timestamp('evaluation_date'), + + // 사양설명회 + hasSpecificationMeeting: boolean('has_specification_meeting').default(false), + + // 예산 및 가격 정보 + currency: varchar('currency', { length: 3 }).default('KRW'), // 통화 + budget: decimal('budget', { precision: 15, scale: 2 }), // 예산 + targetPrice: decimal('target_price', { precision: 15, scale: 2 }), // 내정가 + finalBidPrice: decimal('final_bid_price', { precision: 15, scale: 2 }), // 최종입찰가 + + // PR 정보 + prNumber: varchar('pr_number', { length: 50 }), // PR No. + hasPrDocument: boolean('has_pr_document').default(false), // PR 문서 여부 + + // 상태 및 설정 + status: biddingStatusEnum('status').default('bidding_generated').notNull(), + isPublic: boolean('is_public').default(false), // 공개 입찰 여부 + + // 담당자 정보 + managerName: varchar('manager_name', { length: 100 }), // 입찰담당자 + managerEmail: varchar('manager_email', { length: 100 }), + managerPhone: varchar('manager_phone', { length: 20 }), + + // 메타 정보 + remarks: text('remarks'), // 비고 + createdBy: varchar('created_by', { length: 100 }), // 생성자 + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), // 최종수정일 + updatedBy: varchar('updated_by', { length: 100 }), // 최종수정자 +}) + +// 9. 사양설명회 정보 테이블 +export const specificationMeetings = pgTable('specification_meetings', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + + meetingDate: timestamp('meeting_date').notNull(), + meetingTime: varchar('meeting_time', { length: 20 }), + location: varchar('location', { length: 500 }), + address: text('address'), + contactPerson: varchar('contact_person', { length: 100 }), + contactPhone: varchar('contact_phone', { length: 20 }), + contactEmail: varchar('contact_email', { length: 100 }), + + agenda: text('agenda'), // 회의 안건 + materials: text('materials'), // 준비물 + notes: text('notes'), // 특이사항 + isRequired: boolean('is_required').default(false), // 필수 참석 여부 + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 10. PR 문서 테이블 +export const prDocuments = pgTable('pr_documents', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + + documentName: varchar('document_name', { length: 300 }).notNull(), // 문서명 + fileName: varchar('file_name', { length: 500 }).notNull(), + originalFileName: varchar('original_file_name', { length: 500 }).notNull(), + fileSize: integer('file_size'), // bytes + mimeType: varchar('mime_type', { length: 100 }), + filePath: varchar('file_path', { length: 1000 }).notNull(), // 실제 파일 경로 + + registeredAt: timestamp('registered_at').defaultNow().notNull(), // 등재일 + registeredBy: varchar('registered_by', { length: 100 }).notNull(), // 등재자 + + description: text('description'), + version: varchar('version', { length: 20 }), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 11. PR 아이템 테이블 (상세 응찰 정보) +export const prItemsForBidding = pgTable('pr_items_for_bidding', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + + // 기본 정보 + itemNumber: varchar('item_number', { length: 50 }), // 아이템 번호 + projectInfo: varchar('project_info', { length: 300 }), // 프로젝트 정보 + itemInfo: varchar('item_info', { length: 300 }), // 품목정보 + shi: varchar('shi', { length: 100 }), // SHI + + // 납품 일정 + requestedDeliveryDate: date('requested_delivery_date'), // 납품요청일 + + // 가격 정보 + annualUnitPrice: decimal('annual_unit_price', { precision: 15, scale: 2 }), // 연간단가 + currency: varchar('currency', { length: 3 }).default('KRW'), + + // 수량 및 중량 + quantity: decimal('quantity', { precision: 10, scale: 2 }), // 수량 + quantityUnit: quantityUnitEnum('quantity_unit'), // 수량단위 + totalWeight: decimal('total_weight', { precision: 10, scale: 2 }), // 총 중량 + weightUnit: weightUnitEnum('weight_unit'), // 중량단위 + + // 상세 정보 + materialDescription: text('material_description'), // 자재내역상세 + prNumber: varchar('pr_number', { length: 50 }), // PR번호 + + // SPEC 파일 정보 + hasSpecDocument: boolean('has_spec_document').default(false), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 12. 입찰 조건 테이블 (구매자가 제시하는 조건들) +export const biddingConditions = pgTable('bidding_conditions', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + + // 지급조건 + paymentTerms: text('payment_terms'), // 지급조건 옵션들 (JSON 배열) + + // 세금 + taxConditions: text('tax_conditions'), // Tax 옵션들 (JSON 배열) + + // 계약 및 납기 + contractDeliveryDate: date('contract_delivery_date'), // 계약납기일 + isPriceAdjustmentApplicable: boolean('is_price_adjustment_applicable'), // 연동제적용 여부 + + // 무역조건 + incoterms: text('incoterms'), // Incoterms 옵션들 (JSON 배열) + shippingPort: varchar('shipping_port', { length: 200 }), // 선적지 + destinationPort: varchar('destination_port', { length: 200 }), // 도착지 + + // 기타 + sparePartOptions: text('spare_part_options'), // Spare part 옵션들 (JSON 배열) + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 13. 입찰 참여 업체 테이블2 +export const biddingCompanies = pgTable('bidding_companies', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + companyId: integer('company_id').references(() => vendors.id).notNull(), + + // 초대 및 응답 상태 + invitationStatus: invitationStatusEnum('invitation_status').default('pending').notNull(), + invitedAt: timestamp('invited_at'), + respondedAt: timestamp('responded_at'), + + // 사전견적 정보 + preQuoteAmount: decimal('pre_quote_amount', { precision: 15, scale: 2 }), + preQuoteSubmittedAt: timestamp('pre_quote_submitted_at'), + isPreQuoteSelected: boolean('is_pre_quote_selected').default(false), // 본입찰 대상 선정 여부 + + // 본입찰 정보 + finalQuoteAmount: decimal('final_quote_amount', { precision: 15, scale: 2 }), + finalQuoteSubmittedAt: timestamp('final_quote_submitted_at'), + isWinner: boolean('is_winner').default(false), // 낙찰 여부 + + // 기타 + notes: text('notes'), // 특이사항 + contactPerson: varchar('contact_person', { length: 100 }), // 업체 담당자 + contactEmail: varchar('contact_email', { length: 100 }), + contactPhone: varchar('contact_phone', { length: 20 }), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 14. 업체별 PR 아이템 응찰 정보 +export const companyPrItemBids = pgTable('company_pr_item_bids', { + id: serial('id').primaryKey(), + biddingCompanyId: integer('bidding_company_id').references(() => biddingCompanies.id).notNull(), + prItemId: integer('pr_item_id').references(() => prItemsForBidding.id).notNull(), + + // 업체 응찰 정보 + proposedDeliveryDate: date('proposed_delivery_date'), // 납품예정일 + bidUnitPrice: decimal('bid_unit_price', { precision: 15, scale: 2 }), // 입찰단가 + bidAmount: decimal('bid_amount', { precision: 15, scale: 2 }), // 입찰금액 + currency: varchar('currency', { length: 3 }).default('KRW'), + + // 기술 정보 + technicalSpecification: text('technical_specification'), // 기술 사양 + + // 제출 정보 + isPreQuote: boolean('is_pre_quote').default(false), // 사전견적 여부 + submittedAt: timestamp('submitted_at').defaultNow().notNull(), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 15. 업체별 입찰 조건 응답 +export const companyConditionResponses = pgTable('company_condition_responses', { + id: serial('id').primaryKey(), + biddingCompanyId: integer('bidding_company_id').references(() => biddingCompanies.id).notNull(), + + // 각 조건에 대한 응답 (JSON 형태로 저장) + paymentTermsResponse: varchar('payment_terms_response', { length: 200 }), // 선택된 지급조건 + taxConditionsResponse: varchar('tax_conditions_response', { length: 200 }), // 선택된 Tax 조건 + + // 계약 및 납기 응답 + proposedContractDeliveryDate: date('proposed_contract_delivery_date'), // 제안 계약납기일 + priceAdjustmentResponse: boolean('price_adjustment_response'), // 연동제적용 응답 + + // 무역조건 응답 + incotermsResponse: varchar('incoterms_response', { length: 100 }), // 선택된 Incoterms + proposedShippingPort: varchar('proposed_shipping_port', { length: 200 }), // 제안 선적지 + proposedDestinationPort: varchar('proposed_destination_port', { length: 200 }), // 제안 도착지 + + // 기타 응답 + sparePartResponse: varchar('spare_part_response', { length: 200 }), // Spare part 응답 + + // 추가 제안사항 + additionalProposals: text('additional_proposals'), // 추가 제안사항 + + // 제출 정보 + isPreQuote: boolean('is_pre_quote').default(false), // 사전견적 여부 + submittedAt: timestamp('submitted_at').defaultNow().notNull(), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 16. 입찰 관련 문서 테이블 (강화) +export const biddingDocuments = pgTable('bidding_documents', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + companyId: integer('company_id').references(() => vendors.id), // null이면 발주처 문서 + prItemId: integer('pr_item_id').references(() => prItemsForBidding.id), // SPEC 문서인 경우 연결 + specificationMeetingId: integer('specification_meeting_id').references(() => specificationMeetings.id), // 사양설명회 문서인 경우 연결 + + documentType: documentTypeEnum('document_type').notNull(), + fileName: varchar('file_name', { length: 500 }).notNull(), + originalFileName: varchar('original_file_name', { length: 500 }).notNull(), + fileSize: integer('file_size'), // bytes + mimeType: varchar('mime_type', { length: 100 }), + filePath: varchar('file_path', { length: 1000 }).notNull(), // 실제 파일 경로 + + title: varchar('title', { length: 300 }), + description: text('description'), + + // 접근 권한 + isPublic: boolean('is_public').default(false), // 모든 참여업체 열람 가능 + isRequired: boolean('is_required').default(false), // 필수 제출 문서 + + uploadedBy: varchar('uploaded_by', { length: 100 }), + uploadedAt: timestamp('uploaded_at').defaultNow().notNull(), +}) +// 17. 업체 선정 결과 테이블 (평가 점수 대신 선정 사유) +export const vendorSelectionResults = pgTable('vendor_selection_results', { + id: serial('id').primaryKey(), + biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + selectedCompanyId: integer('selected_company_id').references(() => vendors.id).notNull(), + + // 선정 사유 및 결과 + selectionReason: text('selection_reason').notNull(), // 선정 사유 + evaluationSummary: text('evaluation_summary'), // 평가 요약 + + // 선정 결과 관련 첨부파일들 + hasResultDocuments: boolean('has_result_documents').default(false), + + // 선정 정보 + selectedAt: timestamp('selected_at').defaultNow().notNull(), + selectedBy: varchar('selected_by', { length: 100 }).notNull(), + + // 기타 + remarks: text('remarks'), + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}) + +// 타입 정의 +export type Bidding = typeof biddings.$inferSelect +export type NewBidding = typeof biddings.$inferInsert + +export type SpecificationMeeting = typeof specificationMeetings.$inferSelect +export type NewSpecificationMeeting = typeof specificationMeetings.$inferInsert + +export type PrDocument = typeof prDocuments.$inferSelect +export type NewPrDocument = typeof prDocuments.$inferInsert + +export type PrItem = typeof prItemsForBidding.$inferSelect +export type NewPrItem = typeof prItemsForBidding.$inferInsert + +export type BiddingConditions = typeof biddingConditions.$inferSelect +export type NewBiddingConditions = typeof biddingConditions.$inferInsert + +export type BiddingCompany = typeof biddingCompanies.$inferSelect +export type NewBiddingCompany = typeof biddingCompanies.$inferInsert + +export type CompanyPrItemBid = typeof companyPrItemBids.$inferSelect +export type NewCompanyPrItemBid = typeof companyPrItemBids.$inferInsert + +export type CompanyConditionResponse = typeof companyConditionResponses.$inferSelect +export type NewCompanyConditionResponse = typeof companyConditionResponses.$inferInsert + +export type BiddingDocument = typeof biddingDocuments.$inferSelect +export type NewBiddingDocument = typeof biddingDocuments.$inferInsert + +export type VendorSelectionResult = typeof vendorSelectionResults.$inferSelect +export type NewVendorSelectionResult = typeof vendorSelectionResults.$inferInsert + +// 조인 타입 정의 (자주 사용될 것들) +export type BiddingWithDetails = Bidding & { + specificationMeeting?: SpecificationMeeting + prDocuments: PrDocument[] + prItemsForBidding: (PrItem & { + specDocuments: BiddingDocument[] + })[] + biddingConditions?: BiddingConditions + companies: (BiddingCompany & { + company: Vendor + prItemBids: CompanyPrItemBid[] + conditionResponses: CompanyConditionResponse[] + documents: BiddingDocument[] + })[] + documents: BiddingDocument[] + selectionResult?: VendorSelectionResult +} + +export type BiddingListItem = Bidding & { + participantStats: { + expected: number // 참여예정 + participated: number // 참여 + declined: number // 포기 + } + specificationMeeting?: SpecificationMeeting + prDocuments: PrDocument[] +} + +// 상태별 한글 매핑 +export const biddingStatusLabels = { + bidding_generated: '입찰생성', + request_for_quotation: '사전견적요청', + received_quotation: '사전견적접수', + set_target_price: '내정가 산정', + bidding_opened: '입찰공고', + bidding_closed: '입찰마감', + evaluation_of_bidding: '입찰평가중', + bidding_disposal: '유찰', + vendor_selected: '업체선정' +} as const + +export const contractTypeLabels = { + unit_price: '단가계약', + general: '일반계약', + sale: '매각계약' +} as const + +export const biddingTypeLabels = { + equipment: '기자재', + construction: '공사', + service: '용역', + lease: '임차', + steel_stock: '형강스톡', + piping: '배관', + transport: '운송', + waste: '폐기물', + sale: '매각' +} as const + +export const awardCountLabels = { + single: '단수', + multiple: '복수' +} as const + +export const quantityUnitLabels = { + ea: '개', + set: '세트', + kg: 'kg', + ton: 'ton', + m: 'm', + m2: 'm²', + m3: 'm³', + lot: 'lot', + other: '기타' +} as const + +export const weightUnitLabels = { + kg: 'kg', + ton: 'ton', + 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, + content: biddings.content, + + // ═══════════════════════════════════════════════════════════════ + // 계약 정보 + // ═══════════════════════════════════════════════════════════════ + contractType: biddings.contractType, + biddingType: biddings.biddingType, + awardCount: biddings.awardCount, + contractPeriod: biddings.contractPeriod, + + // ═══════════════════════════════════════════════════════════════ + // 일정 관리 + // ═══════════════════════════════════════════════════════════════ + preQuoteDate: biddings.preQuoteDate, + biddingRegistrationDate: biddings.biddingRegistrationDate, + submissionStartDate: biddings.submissionStartDate, + submissionEndDate: biddings.submissionEndDate, + evaluationDate: biddings.evaluationDate, + + // ═══════════════════════════════════════════════════════════════ + // 회의 및 문서 + // ═══════════════════════════════════════════════════════════════ + 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 |