Merge remote-tracking branch 'origin/dujinkim' into master_homemaster

1 files changed, 543 insertions, 415 deletions

diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts
index 74603df0..18699633 100644
--- a/db/schema/bidding.ts
+++ b/db/schema/bidding.ts
@@ -10,19 +10,21 @@ import {
   decimal, 
   boolean,
   pgEnum,
-  uuid,
-  date, pgView
+  date, 
 } from 'drizzle-orm/pg-core'
 import { Vendor, vendors } from './vendors'
-import { relations , sql} from 'drizzle-orm';
 import { projects } from './projects';
+import { users } from './users';
+
 
 // 입찰공고문 템플릿 (기존)
 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('표준 입찰공고문'),
+  biddingId: integer('bidding_id').references(() => biddings.id), // 연결된 입찰 ID (null이면 템플릿)
+  type: varchar('type', { length: 50 }).notNull().default('standard'), // 입찰공고 타입
+  title: varchar('title', { length: 200 }).notNull().default('입찰공고문'),
   content: text('content').notNull().default(''),
+  isTemplate: boolean('is_template').default(false).notNull(), // 템플릿 여부
   createdAt: timestamp('created_at').defaultNow().notNull(),
   updatedAt: timestamp('updated_at').defaultNow().notNull(),
 })
@@ -40,7 +42,13 @@ export const biddingStatusEnum = pgEnum('bidding_status', [
   'bidding_closed',           // 입찰마감
   'evaluation_of_bidding',    // 입찰평가중
   'bidding_disposal',         // 유찰
-  'vendor_selected'           // 업체선정
+  'vendor_selected',          // 업체선정
+  'bid_opening',              // 개찰
+  'early_bid_opening',        // 조기개찰
+  'rebidding',                // 재입찰
+  'disposal_cancelled',       // 유찰취소
+  'bid_closure',              // 폐찰
+  'round_increase'            // 차수증가
 ])
 
 // 2. 계약구분 enum 
@@ -61,7 +69,6 @@ export const biddingTypeEnum = pgEnum('bidding_type', [
   'transport',       // 운송
   'waste',           // 폐기물
   'sale',            // 매각
-  'steel',           // 강재
   'other'            // 기타(직접입력)
 ])
 
@@ -73,15 +80,32 @@ export const awardCountEnum = pgEnum('award_count', [
 
 // 5. 업체 초대/응답 상태 enum
 export const invitationStatusEnum = pgEnum('invitation_status', [
-  'pending',    // 초대 대기
-  'sent',       // 초대 발송
-  'bidding_invited',   // 입찰 초대
-  'accepted',   // 참여 수락
-  'declined',   // 참여 거절
-  'submitted',   // 견적 제출 완료
-  'bidding_submitted'   // 입찰 제출 완료
+  'pending',              // 초대 대기
+  'pre_quote_sent',       // 사전견적 초대 발송
+  'pre_quote_accepted',   // 사전견적 참여
+  'pre_quote_declined',   // 사전견적 미참여
+  'pre_quote_submitted',  // 사전견적제출완료
+  'bidding_sent',         // 입찰 초대 발송
+  'bidding_accepted',     // 입찰 참여
+  'bidding_declined',     // 입찰 미참여
+  'bidding_cancelled',    // 응찰 취소
+  'bidding_submitted'     // 응찰 완료
 ])
 
+// invitationStatus 라벨 맵핑
+export const invitationStatusLabels: Record<string, string> = {
+  pending: '초대 대기',
+  pre_quote_sent: '사전견적 초대 발송',
+  pre_quote_accepted: '사전견적 참여',
+  pre_quote_declined: '사전견적 미참여',
+  pre_quote_submitted: '사전견적제출완료',
+  bidding_sent: '입찰 초대 발송',
+  bidding_accepted: '입찰 참여',
+  bidding_declined: '입찰 미참여',
+  bidding_cancelled: '응찰 취소',
+  bidding_submitted: '응찰 완료'
+}
+
 // 6. 문서 타입 enum
 export const documentTypeEnum = pgEnum('document_type', [
   'notice',           // 입찰공고서
@@ -124,8 +148,8 @@ export const weightUnitEnum = pgEnum('weight_unit', [
 export const biddings = pgTable('biddings', {
   id: serial('id').primaryKey(),
   biddingNumber: varchar('bidding_number', { length: 50 }).unique().notNull(), // 입찰 No.
+  originalBiddingNumber: varchar('original_bidding_number', { length: 50 }), // 원입찰번호
   revision: integer('revision').default(0), // Rev.
-  projectId: integer('project_id').references(() => projects.id),
   //견적에서 넘어온 레코드인지, 자체생산인지, 디폴트는 자체생산, notnull
   biddingSourceType: varchar('bidding_source_type', { length: 20 }).notNull().default('manual'),
   // 기본 정보
@@ -133,16 +157,16 @@ export const biddings = pgTable('biddings', {
   itemName: varchar('item_name', { length: 300 }), // 품목명
   title: varchar('title', { length: 300 }).notNull(), // 입찰명
   description: text('description'),
-  content: text('content'), // 입찰공고 내용 (HTML) - 표준 템플릿에서 수정된 내용
+  // 입찰공고 내용은 별도 biddingNotices 테이블에서 관리됨
   
   // 계약 정보
   contractType: contractTypeEnum('contract_type').notNull(), // 계약구분
   biddingType: biddingTypeEnum('bidding_type').notNull(), // 입찰유형
   awardCount: awardCountEnum('award_count').default('single'), // 낙찰수
   // contractPeriod: varchar('contract_period', { length: 100 }), // 계약기간
-  //시작일
-  contractStartDate: date('contract_start_date'),
-  //종료일
+  //시작일 (기본값: 현재 날짜)
+  contractStartDate: date('contract_start_date').defaultNow(),
+  //종료일 (기본값: 시작일로부터 1년 후)
   contractEndDate: date('contract_end_date'),
   
   // 일정 관리
@@ -170,11 +194,19 @@ export const biddings = pgTable('biddings', {
   status: biddingStatusEnum('status').default('bidding_generated').notNull(),
   isPublic: boolean('is_public').default(false), // 공개 입찰 여부
   isUrgent: boolean('is_urgent').default(false), // 긴급여부
-  
-  // 담당자 정보
-  managerName: varchar('manager_name', { length: 100 }), // 입찰담당자
-  managerEmail: varchar('manager_email', { length: 100 }),
-  managerPhone: varchar('manager_phone', { length: 20 }),
+
+  // 구매조직 정보
+  purchasingOrganization: varchar('purchasing_organization', { length: 100 }), // 구매조직
+
+  // 담당자 정보 (개선된 구조)
+  bidPicId: integer('bid_pic_id').references(() => users.id), // 입찰담당자 ID
+  bidPicName: varchar('bid_pic_name', { length: 100 }), // 입찰담당자 이름
+  bidPicCode: varchar('bid_pic_code', { length: 50 }), // 입찰담당자 코드
+  supplyPicId: integer('supply_pic_id').references(() => users.id), // 조달담당자 ID
+  supplyPicName: varchar('supply_pic_name', { length: 100 }), // 조달담당자 이름
+  supplyPicCode: varchar('supply_pic_code', { length: 50 }), // 조달담당자 코드
+
+  // 기존 담당자 정보 제거됨 (user FK로 대체)
   
   // 메타 정보
   remarks: text('remarks'), // 비고
@@ -236,9 +268,16 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', {
   
   // 기본 정보
   itemNumber: varchar('item_number', { length: 50 }), // 아이템 번호
-  projectInfo: varchar('project_info', { length: 300 }), // 프로젝트 정보
+  projectId: integer('project_id').references(() => projects.id), // 프로젝트 ID (새로 추가)
+  projectInfo: varchar('project_info', { length: 300 }), // 프로젝트 정보 (기존, 추후 제거 가능)
   itemInfo: varchar('item_info', { length: 300 }), // 품목정보
   shi: varchar('shi', { length: 100 }), // SHI
+  // 자재 그룹 정보 (새로 추가)
+  materialGroupNumber: varchar('material_group_number', { length: 100 }), // 자재그룹번호
+  materialGroupInfo: varchar('material_group_info', { length: 300 }), // 자재그룹정보
+  // 자재 정보 (새로 추가)
+  materialNumber: varchar('material_number', { length: 100 }), // 자재번호
+  materialInfo: varchar('material_info', { length: 500 }), // 자재정보
   
   // 납품 일정
   requestedDeliveryDate: date('requested_delivery_date'), // 납품요청일
@@ -249,12 +288,41 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', {
   
   // 수량 및 중량
   quantity: decimal('quantity', { precision: 10, scale: 2 }), // 수량
-  quantityUnit: varchar('quantity_unit', { length: 50 }),  // 수량단위
+  quantityUnit: varchar('quantity_unit', { length: 50 }),  // 수량단위 (구매단위)
   totalWeight: decimal('total_weight', { precision: 10, scale: 2 }), // 총 중량
-  weightUnit: varchar('weight_unit', { length: 50 }),  // 중량단위
+  weightUnit: varchar('weight_unit', { length: 50 }),  // 중량단위 (자재순중량)
+  
+  // 가격 단위 추가
+  priceUnit: varchar('price_unit', { length: 50 }), // 가격단위
+  purchaseUnit: varchar('purchase_unit', { length: 50 }), // 구매단위
+  materialWeight: decimal('material_weight', { precision: 10, scale: 2 }), // 자재순중량
+
+  // WBS 정보
+  wbsCode: varchar('wbs_code', { length: 100 }), // WBS 코드
+  wbsName: varchar('wbs_name', { length: 300 }), // WBS 명칭
+
+  // Cost Center 정보
+  costCenterCode: varchar('cost_center_code', { length: 100 }), // 코스트센터 코드
+  costCenterName: varchar('cost_center_name', { length: 300 }), // 코스트센터 명칭
+
+  // GL Account 정보
+  glAccountCode: varchar('gl_account_code', { length: 100 }), // GL 계정 코드
+  glAccountName: varchar('gl_account_name', { length: 300 }), // GL 계정 명칭
+
+  // 내정 정보 (새로 추가)
+  targetUnitPrice: decimal('target_unit_price', { precision: 15, scale: 2 }), // 내정단가
+  targetAmount: decimal('target_amount', { precision: 15, scale: 2 }), // 내정금액
+  targetCurrency: varchar('target_currency', { length: 3 }).default('KRW'), // 내정통화
+
+  // 예산 정보 (새로 추가)
+  budgetAmount: decimal('budget_amount', { precision: 15, scale: 2 }), // 예산금액
+  budgetCurrency: varchar('budget_currency', { length: 3 }).default('KRW'), // 예산통화
+
+  // 실적 정보 (새로 추가)
+  actualAmount: decimal('actual_amount', { precision: 15, scale: 2 }), // 실적금액
+  actualCurrency: varchar('actual_currency', { length: 3 }).default('KRW'), // 실적통화
   
-  // 상세 정보
-  materialDescription: text('material_description'), // 자재내역상세
+
   prNumber: varchar('pr_number', { length: 50 }), // PR번호
   
   // SPEC 파일 정보
@@ -280,7 +348,8 @@ export const biddingConditions = pgTable('bidding_conditions', {
   isPriceAdjustmentApplicable: boolean('is_price_adjustment_applicable'), // 연동제적용 여부
   
   // 무역조건
-  incoterms: text('incoterms'), // Incoterms 옵션들 
+  incoterms: text('incoterms'), // Incoterms 옵션들
+  incotermsOption: text('incoterms_option'), // Incoterms 옵션 (추가)
   shippingPort: varchar('shipping_port', { length: 200 }), // 선적지
   destinationPort: varchar('destination_port', { length: 200 }), // 하역지
   
@@ -298,7 +367,7 @@ export const biddingCompanies = pgTable('bidding_companies', {
   companyId: integer('company_id').references(() => vendors.id).notNull(),
   
   // 초대 및 응답 상태
-  invitationStatus: invitationStatusEnum('invitation_status').default('pending').notNull(),
+  invitationStatus: invitationStatusEnum('invitation_status').notNull(),
   invitedAt: timestamp('invited_at'),
   respondedAt: timestamp('responded_at'),
   
@@ -314,9 +383,12 @@ export const biddingCompanies = pgTable('bidding_companies', {
   isBiddingParticipated: boolean('is_bidding_participated'),//본입찰 참여 여부
   finalQuoteAmount: decimal('final_quote_amount', { precision: 15, scale: 2 }),
   finalQuoteSubmittedAt: timestamp('final_quote_submitted_at'),
+  isFinalSubmission: boolean('is_final_submission').default(false), // 최종제출 여부
   isWinner: boolean('is_winner'), // 낙찰 여부
   isAttendingMeeting: boolean('is_attending_meeting'), // 사양설명회 참석 여부
   awardRatio: decimal('award_ratio', { precision: 5, scale: 2 }), // 발주비율
+  //연동제 적용요건 문의 여부
+  isPriceAdjustmentApplicableQuestion: boolean('is_price_adjustment_applicable_question').default(false), // 연동제 적용요건 문의 여부
 
   // 기타
   notes: text('notes'), // 특이사항
@@ -328,6 +400,18 @@ export const biddingCompanies = pgTable('bidding_companies', {
   updatedAt: timestamp('updated_at').defaultNow().notNull(),
 })
 
+// 13-1. 입찰 참여 업체 담당자 테이블
+export const biddingCompaniesContacts = pgTable('bidding_companies_contacts', {
+  id: serial('id').primaryKey(),
+  biddingId: integer('bidding_id').references(() => biddings.id).notNull(),
+  vendorId: integer('vendor_id').references(() => vendors.id).notNull(),
+  contactName: varchar('contact_name', { length: 255 }).notNull(),
+  contactEmail: varchar('contact_email', { length: 255 }).notNull(),
+  contactNumber: varchar('contact_number', { length: 50 }),
+  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(),
@@ -491,7 +575,15 @@ export const priceAdjustmentForms = pgTable('price_adjustment_forms', {
 });
 
 // 타입 정의
-export type Bidding = typeof biddings.$inferSelect
+export type Bidding = typeof biddings.$inferSelect & {
+  // 추가 필드들 (쿼리 결과에 포함되므로 타입에도 추가)
+  purchasingOrganization?: string | null
+  bidPicId?: number | null
+  bidPicName?: string | null
+  supplyPicId?: number | null
+  supplyPicName?: string | null
+}
+
 export type NewBidding = typeof biddings.$inferInsert
 
 export type SpecificationMeeting = typeof specificationMeetings.$inferSelect
@@ -509,6 +601,9 @@ export type NewBiddingConditions = typeof biddingConditions.$inferInsert
 export type BiddingCompany = typeof biddingCompanies.$inferSelect
 export type NewBiddingCompany = typeof biddingCompanies.$inferInsert
 
+export type BiddingCompanyContact = typeof biddingCompaniesContacts.$inferSelect
+export type NewBiddingCompanyContact = typeof biddingCompaniesContacts.$inferInsert
+
 export type CompanyPrItemBid = typeof companyPrItemBids.$inferSelect
 export type NewCompanyPrItemBid = typeof companyPrItemBids.$inferInsert
 
@@ -543,6 +638,29 @@ export type BiddingWithDetails = Bidding & {
 }
 
 export type BiddingListItem = Bidding & {
+  // 전체 참여 현황
+  participantExpected: number // 초대업체 수
+  participationRate: number // 참여율 (입찰 기준)
+  
+  // 사전견적 참여 현황
+  preQuotePending: number // 사전견적 초대 대기/발송
+  preQuoteAccepted: number // 사전견적 참여
+  preQuoteDeclined: number // 사전견적 미참여
+  preQuoteSubmitted: number // 사전견적 제출완료
+  
+  // 입찰 참여 현황
+  biddingPending: number // 입찰 초대 발송
+  biddingAccepted: number // 입찰 참여
+  biddingDeclined: number // 입찰 미참여
+  biddingCancelled: number // 응찰 취소
+  biddingSubmitted: number // 응찰 완료
+  
+  // 호환성을 위한 기존 필드 (deprecated)
+  participantParticipated: number // 참여
+  participantDeclined: number // 포기
+  participantPending: number // 대기
+  participantAccepted: number // 수락
+  
   participantStats: {
     expected: number    // 참여예정
     participated: number // 참여
@@ -562,7 +680,13 @@ export const biddingStatusLabels = {
   bidding_closed: '입찰마감',
   evaluation_of_bidding: '입찰평가중',
   bidding_disposal: '유찰',
-  vendor_selected: '업체선정'
+  vendor_selected: '업체선정',
+  bid_opening: '개찰',
+  early_bid_opening: '조기개찰',
+  rebidding: '재입찰',
+  disposal_cancelled: '유찰취소',
+  bid_closure: '폐찰',
+  round_increase: '차수증가'
 } as const
 
 export const contractTypeLabels = {
@@ -571,6 +695,12 @@ export const contractTypeLabels = {
   sale: '매각계약'
 } as const
 
+export const biddingNoticeTypeLabels = {
+  standard: '표준',
+  facility: '시설재',
+  unit_price: '단가계약'
+} as const
+
 export const biddingTypeLabels = {
   equipment: '기자재',
   construction: '공사',
@@ -581,7 +711,6 @@ export const biddingTypeLabels = {
   transport: '운송',
   waste: '폐기물',
   sale: '매각',
-  steel: '강재',
   other: '기타(직접입력)'
 } as const
 
@@ -610,384 +739,383 @@ export const weightUnitLabels = {
 } 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,
-      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
+// 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