path: root/db/schema/bidding.ts

// 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',            // 매각
  'steel',           // 강재
  'other'            // 기타(직접입력)
])

// 4. 낙찰수 enum
export const awardCountEnum = pgEnum('award_count', [
  'single',    // 단수
  'multiple'   // 복수
])

// 5. 업체 초대/응답 상태 enum
export const invitationStatusEnum = pgEnum('invitation_status', [
  'pending',    // 초대 대기
  'sent',       // 초대 발송
  'bidding_invited',   // 입찰 초대
  'accepted',   // 참여 수락
  'declined',   // 참여 거절
  'submitted',   // 견적 제출 완료
  'bidding_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),
  //견적에서 넘어온 레코드인지, 자체생산인지, 디폴트는 자체생산, notnull
  biddingSourceType: varchar('bidding_source_type', { length: 20 }).notNull().default('manual'),
  // 기본 정보
  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 }), // 계약기간
  //시작일
  contractStartDate: date('contract_start_date'),
  //종료일
  contractEndDate: date('contract_end_date'),
  
  // 일정 관리
  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 }), // 내정가
  targetPriceCalculationCriteria: text('target_price_calculation_criteria'), // 내정가 산정 기준
  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), // 공개 입찰 여부
  isUrgent: boolean('is_urgent').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 }), // 최종수정자
  ANFNR: varchar({length: 50}).unique(), // 원본 ANFNR 추적을 위한 Bidding/RFQ Number (ECC), onConflict target이므로 unique 처리
})

// 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: varchar('quantity_unit', { length: 50 }),  // 수량단위
  totalWeight: decimal('total_weight', { precision: 10, scale: 2 }), // 총 중량
  weightUnit: varchar('weight_unit', { length: 50 }),  // 중량단위
  
  // 상세 정보
  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. 입찰 조건 테이블 (SHI 구매자가 제시하는 조건들)
export const biddingConditions = pgTable('bidding_conditions', {
  id: serial('id').primaryKey(),
  biddingId: integer('bidding_id').references(() => biddings.id).notNull(),
  
  // 지급조건
  paymentTerms: text('payment_terms'), // 지급조건 옵션들 
  
  // 세금
  taxConditions: text('tax_conditions'), // Tax 옵션들
  
  // 계약 및 납기
  contractDeliveryDate: date('contract_delivery_date'), // 계약납기일
  isPriceAdjustmentApplicable: boolean('is_price_adjustment_applicable'), // 연동제적용 여부
  
  // 무역조건
  incoterms: text('incoterms'), // Incoterms 옵션들 
  shippingPort: varchar('shipping_port', { length: 200 }), // 선적지
  destinationPort: varchar('destination_port', { length: 200 }), // 하역지
  
  // 기타
  sparePartOptions: text('spare_part_options'), // Spare part 옵션들
  
  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'),
  preQuoteDeadline: timestamp('pre_quote_deadline'), // 사전견적 마감일
  isPreQuoteSelected: boolean('is_pre_quote_selected').default(false), // 본입찰 대상 선정 여부
  isPreQuoteParticipated: boolean('is_pre_quote_participated'), // 사전견적 참여 여부
  
  // 본입찰 정보  
  isBiddingInvited: boolean('is_bidding_invited').default(false), // 본입찰 초대 여부
  isBiddingParticipated: boolean('is_bidding_participated'),//본입찰 참여 여부
  finalQuoteAmount: decimal('final_quote_amount', { precision: 15, scale: 2 }),
  finalQuoteSubmittedAt: timestamp('final_quote_submitted_at'),
  isWinner: boolean('is_winner'), // 낙찰 여부
  isAttendingMeeting: boolean('is_attending_meeting'), // 사양설명회 참석 여부
  awardRatio: decimal('award_ratio', { precision: 5, scale: 2 }), // 발주비율

  // 기타
  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'), // 연동제적용 응답
  isInitialResponse: boolean('is_initial_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(),
})

// 19. 하도급대금 등 연동표 테이블
export const priceAdjustmentForms = pgTable('price_adjustment_forms', {
  id: serial('id').primaryKey(),
  
  // companyConditionResponses 테이블과 외래 키로 연결
  companyConditionResponsesId: integer('company_condition_responses_id')
    .notNull()
    .references(() => companyConditionResponses.id),
  
  // 품목등의 명칭
  itemName: varchar('item_name', { length: 255 }),

  // 조정대금 반영시점
  adjustmentReflectionPoint: varchar('adjustment_reflection_point', { length: 255 }),
  
  // 연동대상 주요 원재료
  majorApplicableRawMaterial: text('major_applicable_raw_material'),
  
  // 하도급대금등 연동 산식
  adjustmentFormula: text('adjustment_formula'),

  // 원재료 가격 기준지표
  rawMaterialPriceIndex: text('raw_material_price_index'),
  
  // 기준시점 및 비교시점
  referenceDate: date('reference_date'), // 기준시점
  comparisonDate: date('comparison_date'), // 비교시점

  // 연동 비율
  adjustmentRatio: decimal('adjustment_ratio', { precision: 5, scale: 2 }), // 소수점 2자리까지
  
  // 기타 사항
  notes: text('notes'),

  // 조정요건
  adjustmentConditions: text('adjustment_conditions'),
  
  // 연동 미적용 주요 원재료
  majorNonApplicableRawMaterial: text('major_non_applicable_raw_material'),
  
  // 조정주기
  adjustmentPeriod: varchar('adjustment_period', { length: 100 }),

  // 수탁기업(협력사) 작성자
  contractorWriter: varchar('contractor_writer', { length: 100 }),

  // 조정일
  adjustmentDate: date('adjustment_date'),
  
  // 연동 미적용 사유
  nonApplicableReason: text('non_applicable_reason'),
  
  // 메타 정보
  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 PriceAdjustmentForm = typeof priceAdjustmentForms.$inferSelect
export type NewPriceAdjustmentForm = typeof priceAdjustmentForms.$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: '매각',
  steel: '강재',
  other: '기타(직접입력)'
} 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,
      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