(대표님) 입찰, EDP 변경사항 대응, spreadJS 오류 수정, 벤더실사 수정

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