diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/bRfq.ts | 2 | ||||
| -rw-r--r-- | db/schema/basicContractDocumnet.ts | 14 | ||||
| -rw-r--r-- | db/schema/contract.ts | 2 | ||||
| -rw-r--r-- | db/schema/index.ts | 6 | ||||
| -rw-r--r-- | db/schema/rfqLast.ts | 83 | ||||
| -rw-r--r-- | db/schema/rfqLastTBE.ts | 659 | ||||
| -rw-r--r-- | db/schema/rfqVendor.ts | 211 |
7 files changed, 882 insertions, 95 deletions
diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts index 18929b77..589b2b0f 100644 --- a/db/schema/bRfq.ts +++ b/db/schema/bRfq.ts @@ -850,7 +850,7 @@ export const bRfqsRelations = relations(bRfqs, ({ one, many }) => ({ })); // vendorResponseHistory 관계 정의 - export const vendorResponseHistoryRelations = relations(vendorResponseHistory, ({ one }) => ({ + export const vendorResponseHistoryRelations_old = relations(vendorResponseHistory, ({ one }) => ({ vendorResponse: one(vendorAttachmentResponses, { fields: [vendorResponseHistory.vendorResponseId], references: [vendorAttachmentResponses.id], diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts index 3a0b84a9..7fc975a0 100644 --- a/db/schema/basicContractDocumnet.ts +++ b/db/schema/basicContractDocumnet.ts @@ -2,6 +2,10 @@ import { pgTable, pgView, text, timestamp, integer, varchar, boolean , unique, d import { vendors } from './vendors'; import { eq, sql } from "drizzle-orm"; import { users } from './users'; +import { biddingCompanies } from './bidding'; +import { rfqLastVendorResponses } from './rfqVendor'; +import { rfqLastDetails } from './rfqLast'; +import { generalContracts } from './generalContract'; export const basicContractTemplates = pgTable('basic_contract_templates', { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), @@ -33,6 +37,10 @@ export const basicContract = pgTable('basic_contract', { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), templateId: integer('template_id').references(() => basicContractTemplates.id), vendorId: integer('vendor_id').references(() => vendors.id), + //biddingCompanies fk 추가, nullable + biddingCompanyId: integer('bidding_company_id').references(() => biddingCompanies.id), + rfqCompanyId: integer('rfq_company_id').references(() => rfqLastDetails.id), + generalContractId: integer('general_contract_id').references(() => generalContracts.id), requestedBy: integer('requested_by').references(() => users.id), // 상태값을 더 세분화 @@ -70,6 +78,12 @@ export const basicContractView = pgView('basic_contract_view').as((qb) => { vendorId: sql<number | null>`${basicContract.vendorId}`.as('vendor_id'), requestedBy: sql<number | null>`${basicContract.requestedBy}`.as('requested_by'), status: sql<string>`${basicContract.status}`.as('basic_contract_status'), + + //견적,입찰, 계약 + rfqCompanyId: sql<number | null>`${basicContract.rfqCompanyId}`.as('rfq_company_id'), + biddingCompanyId: sql<number | null>`${basicContract.biddingCompanyId}`.as('bidding_company_id'), + generalContractId: sql<number | null>`${basicContract.generalContractId}`.as('general_contract_id'), + // 새로 추가된 필드들 deadline: sql<string | null>`${basicContract.deadline}`.as('deadline'), diff --git a/db/schema/contract.ts b/db/schema/contract.ts index 4790d717..34f49c96 100644 --- a/db/schema/contract.ts +++ b/db/schema/contract.ts @@ -21,8 +21,8 @@ export const contracts = pgTable("contracts", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), // 프로젝트와 협력업체 참조 + // .notNull() 제외(0912 구매 프로젝트 id 없는 계약 case 존재-최겸) projectId: integer("project_id") - .notNull() .references(() => projects.id, { onDelete: "cascade" }), vendorId: integer("vendor_id") diff --git a/db/schema/index.ts b/db/schema/index.ts index 1c2d5998..4724c8b5 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -39,6 +39,7 @@ export * from './compliance'; export * from './rfqLast'; export * from './rfqVendor'; export * from './generalContract'; +export * from './rfqLastTBE'; // 부서별 도메인 할당 관리 export * from './departmentDomainAssignments'; @@ -63,4 +64,7 @@ export * from './knox/titles'; // 직급 export * from './knox/approvals'; // Knox 결재 - eVCP 에서 상신한 결재를 저장 // === Risks 스키마 === -export * from './risks/risks';
\ No newline at end of file +export * from './risks/risks'; + +// === S-ERP 스키마 === +export * from './S_ERP/s_erp';
\ No newline at end of file diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts index 56cc0c35..615e57f4 100644 --- a/db/schema/rfqLast.ts +++ b/db/schema/rfqLast.ts @@ -1,4 +1,4 @@ -import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; +import { index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; import { eq, sql, relations } from "drizzle-orm"; import { projects } from "./projects"; import { users } from "./users"; @@ -40,12 +40,10 @@ export const rfqsLast = pgTable( // 아래 컬럼은 대표 자재코드, 대표 자재명으로 사용 itemCode: varchar("item_code", { length: 100 }), itemName: varchar("item_name", { length: 255 }), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>(), // 인터페이스한 값은 dueDate가 없으므로 notNull 제약조건 제거 - - rfqSendDate: date("rfq_send_date", { mode: "date" }) - .$type<Date | null>(), // notNull() 제약조건 제거, null 허용 (ECC에서 수신 후 보내지 않은 RFQ) + dueDate: timestamp("due_date", { withTimezone: true }) + .$type<Date | null>(), + rfqSendDate: timestamp("rfq_send_date", { withTimezone: true }) + .$type<Date | null>(), status: varchar("status", { length: 30 }) .$type<RfqStatus>() @@ -113,23 +111,21 @@ export const rfqLastDetails = pgTable( vendorsId: integer("vendors_id") .references(() => vendors.id, { onDelete: "set null" }), currency: varchar("currency", { length: 10 }).default("USD"), - + // 정규화된 paymentTerms 참조 paymentTermsCode: varchar("payment_terms_code", { length: 50 }) .references(() => paymentTerms.code, { onDelete: "set null" }), - // paymentTerms 필드는 제거 (코드로 조회) - + // 정규화된 incoterms 참조 incotermsCode: varchar("incoterms_code", { length: 20 }) .references(() => incoterms.code, { onDelete: "set null" }), incotermsDetail: varchar("incoterms_detail", { length: 255 }), - - deliveryDate: date("delivery_date", { mode: "date" }) - .$type<Date>(), - + + deliveryDate: timestamp("delivery_date", { withTimezone: true }) + .$type<Date | null>(), + contractDuration: varchar("contract_duration", { length: 255 }), - - + taxCode: varchar("tax_code", { length: 255 }), placeOfShipping: varchar("place_of_shipping", { length: 255 }), placeOfDestination: varchar("place_of_destination", { length: 255 }), @@ -139,25 +135,50 @@ export const rfqLastDetails = pgTable( .notNull() .references(() => users.id, { onDelete: "set null" }), updatedAt: timestamp("updated_at").defaultNow().notNull(), - + shortList: boolean('short_list').notNull().default(false), returnYn: boolean('return_yn').notNull().default(false), returnedAt: timestamp("returned_at").defaultNow(), - - - prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false), + + projectGtcYn: boolean('project_gtc_yn').notNull().default(false), generalGtcYn: boolean('general_gtc_yn').notNull().default(false), ndaYn: boolean('nda_yn').notNull().default(false), agreementYn: boolean('agreement_yn').notNull().default(false), - + gtcType: varchar("gtc_type", { length: 30 }) + .$type<"general" | "project" | "none" | null>() + .default("none"), + materialPriceRelatedYn: boolean("material_price_related_yn").default(false), sparepartYn: boolean("sparepart_yn").default(false), firstYn: boolean("first_yn").default(false), - + firstDescription: text("first_description"), sparepartDescription: text("sparepart_escription"), - + sendVersion: integer("send_version").default(0), + isLatest: boolean("is_latest").notNull().default(true), + + // 이메일 발송 추적 필드 + emailSentAt: timestamp("email_sent_at"), + emailSentTo: text("email_sent_to"), + emailResentCount: integer("email_resent_count").default(0), + lastEmailSentAt: timestamp("last_email_sent_at"), + emailStatus: varchar("email_status", { length: 30 }) + .$type<"pending" | "sent" | "failed" | "bounced" | null>(), + }, + (table) => { + return { + // 복합 유니크 인덱스: rfqsLastId + vendorsId + isLatest(true) + // partial unique index를 사용하여 isLatest=true인 경우에만 유니크 제약 + uniqueLatestVendor: uniqueIndex("unique_latest_vendor") + .on(table.rfqsLastId, table.vendorsId) + .where(sql`${table.isLatest} = true`), + + // 성능을 위한 추가 인덱스들 + rfqIdIndex: index("idx_rfqs_last_id").on(table.rfqsLastId), + vendorIdIndex: index("idx_vendors_id").on(table.vendorsId), + isLatestIndex: index("idx_is_latest").on(table.isLatest), + }; } ); @@ -177,7 +198,7 @@ export const rfqPrItems = pgTable( materialCode: varchar("material_code", { length: 255 }), materialCategory: varchar("material_category", { length: 255 }),//자재그룹코드 - + acc: varchar("acc", { length: 255 }), materialDescription: varchar("material_description", { length: 255 }), @@ -330,7 +351,7 @@ export const rfqsLastView = pgView("rfqs_last_view").as((qb) => { LIMIT 1 )`.as("major_item_materialD_description"), - + majorItemMaterialCategory: sql<string | null>`( SELECT material_category FROM rfq_pr_items @@ -433,10 +454,11 @@ export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => { returnedAt: sql<Date | null>`${rfqDetailsTable.returnedAt}`.as("returned_at"), // GTC/NDA/Agreement 필드 - prjectGtcYn: sql<boolean>`${rfqDetailsTable.prjectGtcYn}`.as("prject_gtc_yn"), + projectGtcYn: sql<boolean>`${rfqDetailsTable.projectGtcYn}`.as("project_gtc_yn"), generalGtcYn: sql<boolean>`${rfqDetailsTable.generalGtcYn}`.as("general_gtc_yn"), ndaYn: sql<boolean>`${rfqDetailsTable.ndaYn}`.as("nda_yn"), agreementYn: sql<boolean>`${rfqDetailsTable.agreementYn}`.as("agreement_yn"), + gtcType: sql<string | null>`${rfqDetailsTable.gtcType}`.as("gtc_type"), // 추가 YN 필드들 materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"), @@ -449,6 +471,15 @@ export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => { firstDescription: sql<string | null>`${rfqDetailsTable.firstDescription}`.as("first_description"), sparepartDescription: sql<string | null>`${rfqDetailsTable.sparepartDescription}`.as("sparepart_description"), + isLatest: sql<boolean>`${rfqDetailsTable.isLatest}`.as("is_latest"), + + sendVersion: sql<string | null>`${rfqDetailsTable.sendVersion}`.as("send_version"), + emailSentAt: sql<string | null>`${rfqDetailsTable.emailSentAt}`.as("email_sent_at"), + emailSentTo: sql<string | null>`${rfqDetailsTable.emailSentTo}`.as("email_sent_to"), + emailResentCount: sql<number | null>`${rfqDetailsTable.emailResentCount}`.as("email_resent_count"), + lastEmailSentAt: sql<string | null>`${rfqDetailsTable.lastEmailSentAt}`.as("last_email_sent_at"), + emailStatus: sql<string | null>`${rfqDetailsTable.emailStatus}`.as("email_status"), + // 업데이트 관련 필드 diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts new file mode 100644 index 00000000..ba7e30b5 --- /dev/null +++ b/db/schema/rfqLastTBE.ts @@ -0,0 +1,659 @@ +import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb, uniqueIndex, index } from "drizzle-orm/pg-core"; +import { eq, sql, relations } from "drizzle-orm"; +import { rfqsLast, rfqLastDetails, rfqLastAttachments, rfqLastAttachmentRevisions } from "./rfqLast"; +import { users } from "./users"; +import { vendors } from "./vendors"; +import { rfqLastVendorAttachments } from "./rfqVendor"; +import { projects } from "./projects"; + +// ========================================== +// 1. TBE 세션 메인 테이블 +// ========================================== +export const rfqLastTbeSessions = pgTable( + "rfq_last_tbe_sessions", + { + id: serial("id").primaryKey(), + + // RFQ 및 벤더 참조 + rfqsLastId: integer("rfqs_last_id") + .notNull() + .references(() => rfqsLast.id, { onDelete: "cascade" }), + rfqLastDetailsId: integer("rfq_last_details_id") + .notNull() + .references(() => rfqLastDetails.id, { onDelete: "cascade" }), + vendorId: integer("vendor_id") + .notNull() + .references(() => vendors.id, { onDelete: "cascade" }), + + // TBE 세션 정보 + sessionCode: varchar("session_code", { length: 50 }).unique(), // TBE-2025-001 + sessionTitle: varchar("session_title", { length: 255 }), + sessionType: varchar("session_type", { length: 50 }) + .$type<"initial" | "revision" | "final">() + .default("initial"), + + // 상태 관리 + status: varchar("status", { length: 30 }) + .$type<"준비중" | "진행중" | "검토중" | "보류" | "완료" | "취소">() + .notNull() + .default("준비중"), + + // 평가 결과 (단순화) + evaluationResult: varchar("evaluation_result", { length: 30 }) + .$type<"pass" | "conditional_pass" | "non_pass" | null>(), + + // 조건부 승인 시 조건 + conditionalRequirements: text("conditional_requirements"), + conditionsFulfilled: boolean("conditions_fulfilled").default(false), + + // 벤더 피드백 및 간단한 Q&A + vendorRemarks: text("vendor_remarks"), // 벤더가 TBE 과정에서 남기는 의견 + vendorQuestionsLog: jsonb("vendor_questions_log").$type<{ + question: string; + answer: string; + askedAt: string; + answeredAt: string; + answeredBy: number; + }[]>(), // 간단한 Q&A 로그 + + // 일정 관리 + plannedStartDate: date("planned_start_date", { mode: "date" }).$type<Date | null>(), + actualStartDate: timestamp("actual_start_date", { withTimezone: true }).$type<Date | null>(), + plannedEndDate: date("planned_end_date", { mode: "date" }).$type<Date | null>(), + actualEndDate: timestamp("actual_end_date", { withTimezone: true }).$type<Date | null>(), + + // 참여자 정보 + leadEvaluatorId: integer("lead_evaluator_id") + .references(() => users.id, { onDelete: "set null" }), + technicalEvaluatorId: integer("technical_evaluator_id") + .references(() => users.id, { onDelete: "set null" }), + commercialEvaluatorId: integer("commercial_evaluator_id") + .references(() => users.id, { onDelete: "set null" }), + + // 평가 요약 + technicalSummary: text("technical_summary"), + commercialSummary: text("commercial_summary"), + overallRemarks: text("overall_remarks"), + + // 감사 필드 + createdBy: integer("created_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + updatedBy: integer("updated_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + + // 최종 승인 + approvedBy: integer("approved_by") + .references(() => users.id, { onDelete: "set null" }), + approvedAt: timestamp("approved_at", { withTimezone: true }).$type<Date | null>(), + approvalRemarks: text("approval_remarks"), + }, + (table) => ({ + // 하나의 RFQ-벤더 조합에 대해 활성 TBE는 하나만 + uniqueActiveTbe: uniqueIndex("unique_active_tbe") + .on(table.rfqsLastId, table.vendorId, table.status) + .where(sql`${table.status} IN ('준비중', '진행중', '검토중', '보류')`), + }) +); + +// ========================================== +// 2. TBE 문서 검토 테이블 +// ========================================== +export const rfqLastTbeDocumentReviews = pgTable( + "rfq_last_tbe_document_reviews", + { + id: serial("id").primaryKey(), + tbeSessionId: integer("tbe_session_id") + .notNull() + .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }), + + // 문서 참조 (구매자 문서 또는 벤더 문서) + documentSource: varchar("document_source", { length: 20 }) + .$type<"buyer" | "vendor">() + .notNull(), + + // 구매자 문서인 경우 + buyerAttachmentId: integer("buyer_attachment_id") + .references(() => rfqLastAttachments.id, { onDelete: "cascade" }), + buyerAttachmentRevisionId: integer("buyer_attachment_revision_id") + .references(() => rfqLastAttachmentRevisions.id, { onDelete: "cascade" }), + + // 벤더 문서인 경우 + vendorAttachmentId: integer("vendor_attachment_id") + .references(() => rfqLastVendorAttachments.id, { onDelete: "cascade" }), + + // 검토 정보 + documentType: varchar("document_type", { length: 50 }), + documentName: varchar("document_name", { length: 255 }), + + reviewStatus: varchar("review_status", { length: 30 }) + .$type<"미검토" | "검토중" | "검토완료" | "재검토필요" | "승인" | "반려">() + .notNull() + .default("미검토"), + + // 검토 결과 + technicalCompliance: boolean("technical_compliance"), + qualityAcceptable: boolean("quality_acceptable"), + requiresRevision: boolean("requires_revision").default(false), + + // 검토 의견 + reviewComments: text("review_comments"), + revisionRequirements: text("revision_requirements"), + + // PDFTron 관련 + hasPdftronComments: boolean("has_pdftron_comments").default(false), + pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }), + pdftronAnnotationCount: integer("pdftron_annotation_count").default(0), + + // 검토자 정보 + reviewedBy: integer("reviewed_by") + .references(() => users.id, { onDelete: "set null" }), + reviewedAt: timestamp("reviewed_at", { withTimezone: true }).$type<Date | null>(), + + // 추가 검토자 (복수 검토자 지원) + additionalReviewers: jsonb("additional_reviewers").$type<number[]>(), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => ({ + tbeSessionIdx: index("idx_tbe_session").on(table.tbeSessionId), + documentSourceIdx: index("idx_document_source").on(table.documentSource), + reviewStatusIdx: index("idx_review_status").on(table.reviewStatus), + }) +); + +// ========================================== +// 3. PDFTron 코멘트 관리 +// ========================================== +export const rfqLastTbePdftronComments = pgTable( + "rfq_last_tbe_pdftron_comments", + { + id: serial("id").primaryKey(), + documentReviewId: integer("document_review_id") + .notNull() + .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "cascade" }), + + // PDFTron 관련 정보 + pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }).notNull(), + pdftronAnnotationId: varchar("pdftron_annotation_id", { length: 255 }).notNull(), + annotationType: varchar("annotation_type", { length: 50 }), // highlight, note, drawing, etc. + + // 위치 정보 + pageNumber: integer("page_number"), + xPosition: numeric("x_position", { precision: 10, scale: 4 }).$type<number>(), + yPosition: numeric("y_position", { precision: 10, scale: 4 }).$type<number>(), + coordinates: jsonb("coordinates"), // 복잡한 도형의 경우 + + // 코멘트 내용 + commentText: text("comment_text"), + commentCategory: varchar("comment_category", { length: 50 }) + .$type<"technical" | "commercial" | "quality" | "compliance" | "general">(), + + severity: varchar("severity", { length: 20 }) + .$type<"minor" | "major" | "critical">() + .default("minor"), + + // 상태 관리 + status: varchar("status", { length: 30 }) + .$type<"open" | "resolved" | "rejected" | "deferred">() + .default("open"), + + // 해결 정보 + resolvedBy: integer("resolved_by") + .references(() => users.id, { onDelete: "set null" }), + resolvedAt: timestamp("resolved_at", { withTimezone: true }).$type<Date | null>(), + resolutionNote: text("resolution_note"), + + // 답변 스레드 + replies: jsonb("replies").$type<{ + userId: number; + userName: string; + message: string; + createdAt: string; + }[]>(), + + // 작성자 정보 + createdBy: integer("created_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + createdByType: varchar("created_by_type", { length: 20 }) + .$type<"buyer" | "vendor">() + .notNull(), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => ({ + documentReviewIdx: index("idx_pdftron_doc_review").on(table.documentReviewId), + statusIdx: index("idx_pdftron_status").on(table.status), + // PDFTron ID들에 대한 유니크 제약 + uniquePdftronAnnotation: uniqueIndex("unique_pdftron_annotation") + .on(table.pdftronDocumentId, table.pdftronAnnotationId), + }) +); + +// ========================================== +// 4. TBE 새로운 벤더 첨부파일 (TBE 중 추가 제출) +// ========================================== +export const rfqLastTbeVendorDocuments = pgTable( + "rfq_last_tbe_vendor_documents", + { + id: serial("id").primaryKey(), + tbeSessionId: integer("tbe_session_id") + .notNull() + .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }), + + // 문서 구분 + documentType: varchar("document_type", { length: 50 }) + .$type<"clarification" | "revised_proposal" | "additional_info" | "certificate" | "test_report" | "drawing">() + .notNull(), + + // 문서 검토 요청에 대한 응답인 경우 + isResponseToReviewId: integer("is_response_to_review_id") + .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "set null" }), + + // 파일 정보 + fileName: varchar("file_name", { length: 255 }).notNull(), + originalFileName: varchar("original_file_name", { length: 255 }).notNull(), + filePath: varchar("file_path", { length: 512 }).notNull(), + fileSize: integer("file_size"), + fileType: varchar("file_type", { length: 100 }), + + // 문서 메타데이터 + documentNo: varchar("document_no", { length: 100 }), + revisionNo: varchar("revision_no", { length: 20 }), + issueDate: date("issue_date", { mode: "date" }).$type<Date | null>(), + + // 설명 + description: text("description"), + submittalRemarks: text("submittal_remarks"), + + // 검토 상태 + reviewRequired: boolean("review_required").default(true), + reviewStatus: varchar("review_status", { length: 30 }) + .$type<"pending" | "reviewing" | "approved" | "rejected">() + .default("pending"), + + // 제출자 정보 + submittedBy: integer("submitted_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + submittedAt: timestamp("submitted_at").defaultNow().notNull(), + + // 검토자 정보 + reviewedBy: integer("reviewed_by") + .references(() => users.id, { onDelete: "set null" }), + reviewedAt: timestamp("reviewed_at", { withTimezone: true }).$type<Date | null>(), + reviewComments: text("review_comments"), + }, + (table) => ({ + tbeSessionIdx: index("idx_tbe_vendor_doc_session").on(table.tbeSessionId), + reviewStatusIdx: index("idx_tbe_vendor_doc_status").on(table.reviewStatus), + }) +); + +// ========================================== +// 5. TBE 이력 관리 +// ========================================== +export const rfqLastTbeHistory = pgTable( + "rfq_last_tbe_history", + { + id: serial("id").primaryKey(), + tbeSessionId: integer("tbe_session_id") + .notNull() + .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }), + + // 액션 타입 + actionType: varchar("action_type", { length: 50 }) + .$type<"status_change" | "evaluation_update" | "document_review" | "approval">() + .notNull(), + + // 상태 변경 + previousStatus: varchar("previous_status", { length: 30 }), + newStatus: varchar("new_status", { length: 30 }), + + // 변경 내용 + changeDescription: text("change_description"), + changeDetails: jsonb("change_details"), + + // 수행자 + performedBy: integer("performed_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + performedByType: varchar("performed_by_type", { length: 20 }) + .$type<"buyer" | "vendor">() + .notNull(), + + performedAt: timestamp("performed_at").defaultNow().notNull(), + + // 추가 컨텍스트 + relatedEntityType: varchar("related_entity_type", { length: 50 }), + relatedEntityId: integer("related_entity_id"), + remarks: text("remarks"), + }, + (table) => ({ + tbeSessionIdx: index("idx_history_tbe_session").on(table.tbeSessionId), + actionTypeIdx: index("idx_history_action_type").on(table.actionType), + performedAtIdx: index("idx_history_performed_at").on(table.performedAt), + }) +); + +// ========================================== +// Views +// ========================================== + +// TBE 세션 종합 뷰 +export const tbeSessionSummaryView = pgView("tbe_session_summary_view").as((qb) => { + const tbeSession = alias(rfqLastTbeSessions, "tbe"); + const rfq = alias(rfqsLast, "rfq"); + const vendor = alias(vendors, "vendor"); + const leadEvaluator = alias(users, "lead_evaluator"); + + return qb + .select({ + // TBE 기본 정보 + tbeId: sql<number>`${tbeSession.id}`.as("tbe_id"), + sessionCode: sql<string>`${tbeSession.sessionCode}`.as("session_code"), + sessionTitle: sql<string>`${tbeSession.sessionTitle}`.as("session_title"), + + // RFQ 정보 + rfqCode: sql<string>`${rfq.rfqCode}`.as("rfq_code"), + + // 벤더 정보 + vendorName: sql<string>`${vendor.vendorName}`.as("vendor_name"), + vendorCode: sql<string>`${vendor.vendorCode}`.as("vendor_code"), + + // 상태 및 결과 + status: sql<string>`${tbeSession.status}`.as("status"), + evaluationResult: sql<string | null>`${tbeSession.evaluationResult}`.as("evaluation_result"), + + // 평가자 + leadEvaluatorName: sql<string | null>`${leadEvaluator.name}`.as("lead_evaluator_name"), + + // 문서 검토 통계 + totalDocuments: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = ${tbeSession.id} + )`.as("total_documents"), + + reviewedDocuments: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = ${tbeSession.id} + AND review_status IN ('검토완료', '승인') + )`.as("reviewed_documents"), + + // PDFTron 코멘트 통계 + totalComments: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_pdftron_comments pc + JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id + WHERE dr.tbe_session_id = ${tbeSession.id} + )`.as("total_comments"), + + unresolvedComments: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_pdftron_comments pc + JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id + WHERE dr.tbe_session_id = ${tbeSession.id} + AND pc.status = 'open' + )`.as("unresolved_comments"), + + // 일정 + actualStartDate: sql<Date | null>`${tbeSession.actualStartDate}`.as("actual_start_date"), + actualEndDate: sql<Date | null>`${tbeSession.actualEndDate}`.as("actual_end_date"), + + // 타임스탬프 + createdAt: sql<Date>`${tbeSession.createdAt}`.as("created_at"), + updatedAt: sql<Date>`${tbeSession.updatedAt}`.as("updated_at"), + }) + .from(tbeSession) + .leftJoin(rfq, eq(tbeSession.rfqsLastId, rfq.id)) + .leftJoin(vendor, eq(tbeSession.vendorId, vendor.id)) + .leftJoin(leadEvaluator, eq(tbeSession.leadEvaluatorId, leadEvaluator.id)); +}); + +// Relations 정의 +export const tbeSessionRelations = relations( + rfqLastTbeSessions, + ({ one, many }) => ({ + rfq: one(rfqsLast, { + fields: [rfqLastTbeSessions.rfqsLastId], + references: [rfqsLast.id], + }), + rfqDetail: one(rfqLastDetails, { + fields: [rfqLastTbeSessions.rfqLastDetailsId], + references: [rfqLastDetails.id], + }), + vendor: one(vendors, { + fields: [rfqLastTbeSessions.vendorId], + references: [vendors.id], + }), + leadEvaluator: one(users, { + fields: [rfqLastTbeSessions.leadEvaluatorId], + references: [users.id], + relationName: "tbeLeadEvaluator", + }), + documentReviews: many(rfqLastTbeDocumentReviews), + vendorDocuments: many(rfqLastTbeVendorDocuments), + history: many(rfqLastTbeHistory), + }) +); + +// Type exports +export type TbeSession = typeof rfqLastTbeSessions.$inferSelect; +export type TbeDocumentReview = typeof rfqLastTbeDocumentReviews.$inferSelect; +export type TbePdftronComment = typeof rfqLastTbePdftronComments.$inferSelect; +export type TbeVendorDocument = typeof rfqLastTbeVendorDocuments.$inferSelect; +export type TbeHistory = typeof rfqLastTbeHistory.$inferSelect; + +// ========================================== +// TBE Last 종합 뷰 +// ========================================== +export const tbeLastView = pgView("tbe_last_view").as((qb) => { + const leadUser = alias(users, "lead_user"); + + return qb + .select({ + // TBE Session 기본 정보 + tbeSessionId: sql<number>`${rfqLastTbeSessions.id}`.as("tbe_session_id"), + sessionCode: sql<string>`${rfqLastTbeSessions.sessionCode}`.as("session_code"), + sessionTitle: sql<string>`${rfqLastTbeSessions.sessionTitle}`.as("session_title"), + sessionType: sql<string>`${rfqLastTbeSessions.sessionType}`.as("session_type"), + sessionStatus: sql<string>`${rfqLastTbeSessions.status}`.as("session_status"), + evaluationResult: sql<string | null>`${rfqLastTbeSessions.evaluationResult}`.as("evaluation_result"), + conditionalRequirements: sql<string | null>`${rfqLastTbeSessions.conditionalRequirements}`.as("conditional_requirements"), + conditionsFulfilled: sql<boolean>`${rfqLastTbeSessions.conditionsFulfilled}`.as("conditions_fulfilled"), + + // RFQ 정보 + rfqId: sql<number>`${rfqsLast.id}`.as("rfq_id"), + rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"), + rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"), + rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"), + series: sql<string | null>`${rfqsLast.series}`.as("series"), + rfqStatus: sql<string>`${rfqsLast.status}`.as("rfq_status"), + rfqDueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("rfq_due_date"), + + // 패키지 정보 + packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"), + packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"), + + // PR 정보 + prNumber: sql<string | null>`${rfqsLast.prNumber}`.as("pr_number"), + prIssueDate: sql<Date | null>`${rfqsLast.prIssueDate}`.as("pr_issue_date"), + + // 프로젝트 정보 + projectId: sql<number | null>`${rfqsLast.projectId}`.as("project_id"), + projectCode: sql<string | null>`${projects.code}`.as("project_code"), + projectName: sql<string | null>`${projects.name}`.as("project_name"), + projectCompany: sql<string | null>`${rfqsLast.projectCompany}`.as("project_company"), + projectFlag: sql<string | null>`${rfqsLast.projectFlag}`.as("project_flag"), + projectSite: sql<string | null>`${rfqsLast.projectSite}`.as("project_site"), + + // 아이템 정보 + itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"), + itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"), + + // 벤더 정보 + vendorId: sql<number>`${vendors.id}`.as("vendor_id"), + vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"), + vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"), + vendorCountry: sql<string | null>`${vendors.country}`.as("vendor_country"), + vendorEmail: sql<string | null>`${vendors.email}`.as("vendor_email"), + vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"), + + // RFQ Detail 정보 + rfqDetailId: sql<number>`${rfqLastDetails.id}`.as("rfq_detail_id"), + shortList: sql<boolean>`${rfqLastDetails.shortList}`.as("short_list"), + returnYn: sql<boolean>`${rfqLastDetails.returnYn}`.as("return_yn"), + + // TBE 일정 + plannedStartDate: sql<Date | null>`${rfqLastTbeSessions.plannedStartDate}`.as("planned_start_date"), + actualStartDate: sql<Date | null>`${rfqLastTbeSessions.actualStartDate}`.as("actual_start_date"), + plannedEndDate: sql<Date | null>`${rfqLastTbeSessions.plannedEndDate}`.as("planned_end_date"), + actualEndDate: sql<Date | null>`${rfqLastTbeSessions.actualEndDate}`.as("actual_end_date"), + + // 평가자 정보 + leadEvaluatorId: sql<number | null>`${rfqLastTbeSessions.leadEvaluatorId}`.as("lead_evaluator_id"), + leadEvaluatorName: sql<string | null>`lead_user.name`.as("lead_evaluator_name"), + + // PR 아이템 수 + prItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + )`.as("pr_items_count"), + + majorItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + )`.as("major_items_count"), + + // 구매자 문서 수 (설계 문서) + buyerDocumentsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = ${rfqLastTbeSessions.id} + AND document_source = 'buyer' + )`.as("buyer_documents_count"), + + // 벤더 문서 수 + vendorDocumentsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_vendor_documents + WHERE tbe_session_id = ${rfqLastTbeSessions.id} + )`.as("vendor_documents_count"), + + // 검토 완료 문서 수 + reviewedDocumentsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = ${rfqLastTbeSessions.id} + AND review_status IN ('검토완료', '승인') + )`.as("reviewed_documents_count"), + + // PDFTron 코멘트 수 + totalCommentsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_pdftron_comments pc + JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id + WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id} + )`.as("total_comments_count"), + + unresolvedCommentsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_tbe_pdftron_comments pc + JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id + WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id} + AND pc.status = 'open' + )`.as("unresolved_comments_count"), + + // 타임스탬프 + createdAt: sql<Date>`${rfqLastTbeSessions.createdAt}`.as("created_at"), + updatedAt: sql<Date>`${rfqLastTbeSessions.updatedAt}`.as("updated_at"), + }) + .from(rfqLastTbeSessions) + .leftJoin(rfqsLast, sql`${rfqLastTbeSessions.rfqsLastId} = ${rfqsLast.id}`) + .leftJoin(rfqLastDetails, sql`${rfqLastTbeSessions.rfqLastDetailsId} = ${rfqLastDetails.id}`) + .leftJoin(vendors, sql`${rfqLastTbeSessions.vendorId} = ${vendors.id}`) + .leftJoin(projects, sql`${rfqsLast.projectId} = ${projects.id}`) + .leftJoin(leadUser, sql`${rfqLastTbeSessions.leadEvaluatorId} = ${leadUser.id}`) +}); + +// ========================================== +// TBE 문서 상세 뷰 (구매자 + 벤더 문서 통합) +// ========================================== +export const tbeDocumentsView = pgView("tbe_documents_view").as((qb) => { + const ba = alias(rfqLastAttachments, "ba"); + const baRev = alias(rfqLastAttachmentRevisions, "ba_rev"); + return qb + .select({ + // 문서 검토 ID + documentReviewId: sql<number | null>`dr.id`.as("document_review_id"), + tbeSessionId: sql<number>`COALESCE(dr.tbe_session_id, vd.tbe_session_id)`.as("tbe_session_id"), + + // 문서 구분 + documentSource: sql<string>` + CASE + WHEN dr.id IS NOT NULL THEN dr.document_source + WHEN vd.id IS NOT NULL THEN 'vendor' + ELSE NULL + END + `.as("document_source"), + + // 문서 정보 + documentId: sql<number>`COALESCE(dr.buyer_attachment_id, vd.id)`.as("document_id"), + documentType: sql<string | null>`COALESCE(dr.document_type, vd.document_type)`.as("document_type"), + documentName: sql<string>`COALESCE(dr.document_name, vd.file_name)`.as("document_name"), + originalFileName: sql<string | null>`COALESCE(ba_rev.original_file_name, vd.original_file_name)`.as("original_file_name"), + filePath: sql<string | null>`COALESCE(ba_rev.file_path, vd.file_path)`.as("file_path"), + fileSize: sql<number | null>`COALESCE(ba_rev.file_size, vd.file_size)`.as("file_size"), + fileType: sql<string | null>`COALESCE(ba_rev.file_type, vd.file_type)`.as("file_type"), + + // 검토 상태 + reviewStatus: sql<string>`COALESCE(dr.review_status, vd.review_status, '미검토')`.as("review_status"), + technicalCompliance: sql<boolean | null>`dr.technical_compliance`.as("technical_compliance"), + qualityAcceptable: sql<boolean | null>`dr.quality_acceptable`.as("quality_acceptable"), + requiresRevision: sql<boolean>`COALESCE(dr.requires_revision, false)`.as("requires_revision"), + + // PDFTron 관련 + hasPdftronComments: sql<boolean>`COALESCE(dr.has_pdftron_comments, false)`.as("has_pdftron_comments"), + pdftronDocumentId: sql<string | null>`dr.pdftron_document_id`.as("pdftron_document_id"), + pdftronAnnotationCount: sql<number>`COALESCE(dr.pdftron_annotation_count, 0)`.as("pdftron_annotation_count"), + + // 검토 정보 + reviewedBy: sql<number | null>`COALESCE(dr.reviewed_by, vd.reviewed_by)`.as("reviewed_by"), + reviewedAt: sql<Date | null>`COALESCE(dr.reviewed_at, vd.reviewed_at)`.as("reviewed_at"), + reviewComments: sql<string | null>`COALESCE(dr.review_comments, vd.review_comments)`.as("review_comments"), + + // 제출 정보 (벤더 문서인 경우) + submittedBy: sql<number | null>`vd.submitted_by`.as("submitted_by"), + submittedAt: sql<Date | null>`vd.submitted_at`.as("submitted_at"), + + // 타임스탬프 + createdAt: sql<Date>`COALESCE(dr.created_at, vd.submitted_at)`.as("created_at"), + updatedAt: sql<Date>`COALESCE(dr.updated_at, vd.submitted_at)`.as("updated_at"), + }) + .from( + sql`( + SELECT * FROM rfq_last_tbe_document_reviews + ) dr + FULL OUTER JOIN ( + SELECT * FROM rfq_last_tbe_vendor_documents + ) vd ON false + ` + ) + .leftJoin(ba, sql`dr.buyer_attachment_id = ${ba.id}`) + .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`); +}); + +// Type exports +export type TbeLastView = typeof tbeLastView.$inferSelect; +export type TbeDocumentsView = typeof tbeDocumentsView.$inferSelect;
\ No newline at end of file diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts index 9d7b5fa8..5752b1c2 100644 --- a/db/schema/rfqVendor.ts +++ b/db/schema/rfqVendor.ts @@ -12,7 +12,7 @@ export const rfqLastVendorResponses = pgTable( "rfq_last_vendor_responses", { id: serial("id").primaryKey(), - + // RFQ 및 벤더 참조 rfqsLastId: integer("rfqs_last_id") .notNull() @@ -23,26 +23,37 @@ export const rfqLastVendorResponses = pgTable( vendorId: integer("vendor_id") .notNull() .references(() => vendors.id, { onDelete: "cascade" }), - + // 응답 버전 관리 responseVersion: integer("response_version").notNull().default(1), isLatest: boolean("is_latest").notNull().default(true), + + + // 참여 여부 관련 필드 (새로 추가) + participationStatus: varchar("participation_status", { length: 20 }) + .$type<"미응답" | "참여" | "불참">() + .notNull() + .default("미응답"), + participationRepliedAt: timestamp("participation_replied_at"), + participationRepliedBy: integer("participation_replied_by") + .references(() => users.id, { onDelete: "set null" }), + nonParticipationReason: text("non_participation_reason"), // 불참 사유 - // 응답 상태 + // 응답 상태 (수정: 참여 결정 후에만 의미 있음) status: varchar("status", { length: 30 }) - .$type<"초대됨" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">() + .$type<"대기중" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">() .notNull() - .default("초대됨"), - + .default("대기중"), + // 제출 정보 submittedAt: timestamp("submitted_at"), submittedBy: integer("submitted_by") .references(() => users.id, { onDelete: "set null" }), - + // 총액 정보 totalAmount: numeric("total_amount", { precision: 15, scale: 2 }).$type<number>(), currency: varchar("currency", { length: 10 }).default("USD"), - + // 벤더 제안 조건 (구매자 제시와 다른 경우) vendorCurrency: varchar("vendor_currency", { length: 10 }), vendorPaymentTermsCode: varchar("vendor_payment_terms_code", { length: 50 }) @@ -55,23 +66,23 @@ export const rfqLastVendorResponses = pgTable( vendorTaxCode: varchar("vendor_tax_code", { length: 255 }), vendorPlaceOfShipping: varchar("vendor_place_of_shipping", { length: 255 }), vendorPlaceOfDestination: varchar("vendor_place_of_destination", { length: 255 }), - + // 초도품관리 응답 vendorFirstYn: boolean("vendor_first_yn"), vendorFirstDescription: text("vendor_first_description"), vendorFirstAcceptance: varchar("vendor_first_acceptance", { length: 50 }) .$type<"수용" | "부분수용" | "거부" | null>(), - + // Spare part 응답 vendorSparepartYn: boolean("vendor_sparepart_yn"), vendorSparepartDescription: text("vendor_sparepart_description"), vendorSparepartAcceptance: varchar("vendor_sparepart_acceptance", { length: 50 }) .$type<"수용" | "부분수용" | "거부" | null>(), - + // 연동제 적용 응답 vendorMaterialPriceRelatedYn: boolean("vendor_material_price_related_yn"), vendorMaterialPriceRelatedReason: text("vendor_material_price_related_reason"), - + // 각 조건별 변경 사유 (구매자 제시와 다른 경우) currencyReason: text("currency_reason"), paymentTermsReason: text("payment_terms_reason"), @@ -79,11 +90,11 @@ export const rfqLastVendorResponses = pgTable( incotermsReason: text("incoterms_reason"), taxReason: text("tax_reason"), shippingReason: text("shipping_reason"), - + // 전체 비고 및 특이사항 generalRemark: text("general_remark"), technicalProposal: text("technical_proposal"), - + // 감사 필드 createdBy: integer("created_by") .notNull() @@ -93,6 +104,8 @@ export const rfqLastVendorResponses = pgTable( .references(() => users.id, { onDelete: "set null" }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), + + } ); @@ -109,18 +122,18 @@ export const rfqLastVendorQuotationItems = pgTable( rfqPrItemId: integer("rfq_pr_item_id") .notNull() .references(() => rfqPrItems.id, { onDelete: "cascade" }), - + // PR 아이템 정보 (캐시) prNo: varchar("pr_no", { length: 50 }), materialCode: varchar("material_code", { length: 255 }), materialDescription: varchar("material_description", { length: 255 }), - + // 견적 정보 quantity: numeric("quantity", { precision: 12, scale: 2 }) .$type<number>() .notNull(), uom: varchar("uom", { length: 50 }), - + // 가격 정보 unitPrice: numeric("unit_price", { precision: 15, scale: 4 }) .$type<number>() @@ -129,28 +142,28 @@ export const rfqLastVendorQuotationItems = pgTable( .$type<number>() .notNull(), currency: varchar("currency", { length: 10 }).default("USD"), - + // 납기 정보 vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(), leadTime: integer("lead_time"), // days - + // 제조사 정보 manufacturer: varchar("manufacturer", { length: 255 }), manufacturerCountry: varchar("manufacturer_country", { length: 100 }), modelNo: varchar("model_no", { length: 255 }), - + // 기술 사양 technicalCompliance: boolean("technical_compliance").default(true), alternativeProposal: text("alternative_proposal"), - + // 할인 정보 discountRate: numeric("discount_rate", { precision: 5, scale: 2 }).$type<number>(), discountAmount: numeric("discount_amount", { precision: 15, scale: 2 }).$type<number>(), - + // 비고 itemRemark: text("item_remark"), deviationReason: text("deviation_reason"), // 요구사항과 다른 경우 사유 - + createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), } @@ -166,27 +179,27 @@ export const rfqLastVendorAttachments = pgTable( vendorResponseId: integer("vendor_response_id") .notNull() .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }), - + // 첨부파일 구분 attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // "견적서", "기술제안서", "인증서", "카탈로그", "도면", "테스트성적서", "기타" - + documentNo: varchar("document_no", { length: 100 }), // 문서번호 - + // 파일 정보 fileName: varchar("file_name", { length: 255 }).notNull(), originalFileName: varchar("original_file_name", { length: 255 }).notNull(), filePath: varchar("file_path", { length: 512 }).notNull(), fileSize: integer("file_size"), fileType: varchar("file_type", { length: 100 }), - + // 파일 설명 description: text("description"), - + // 유효기간 (인증서 등) validFrom: date("valid_from", { mode: "date" }).$type<Date | null>(), validTo: date("valid_to", { mode: "date" }).$type<Date | null>(), - + // 감사 필드 uploadedBy: integer("uploaded_by") .notNull() @@ -205,16 +218,16 @@ export const rfqLastVendorResponseHistory = pgTable( vendorResponseId: integer("vendor_response_id") .notNull() .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }), - + action: varchar("action", { length: 50 }).notNull(), // "생성", "수정", "제출", "철회", "재제출", "승인", "반려" - + previousStatus: varchar("previous_status", { length: 30 }), newStatus: varchar("new_status", { length: 30 }), - + changeDetails: jsonb("change_details"), // 변경된 필드들 changeReason: text("change_reason"), - + performedBy: integer("performed_by") .notNull() .references(() => users.id, { onDelete: "set null" }), @@ -240,25 +253,25 @@ export const vendorResponseSummaryView = pgView("vendor_response_summary_view"). responseId: sql<number>`${responseTable.id}`.as("response_id"), rfqId: sql<number>`${rfqTable.id}`.as("rfq_id"), rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"), - + // 벤더 정보 vendorId: sql<number>`${vendorTable.id}`.as("vendor_id"), vendorName: sql<string>`${vendorTable.vendorName}`.as("vendor_name"), vendorCode: sql<string>`${vendorTable.vendorCode}`.as("vendor_code"), - + // 응답 상태 responseVersion: sql<number>`${responseTable.responseVersion}`.as("response_version"), status: sql<string>`${responseTable.status}`.as("status"), isLatest: sql<boolean>`${responseTable.isLatest}`.as("is_latest"), - + // 제출 정보 submittedAt: sql<Date | null>`${responseTable.submittedAt}`.as("submitted_at"), submittedByName: sql<string | null>`${submittedByUser.name}`.as("submitted_by_name"), - + // 금액 정보 totalAmount: sql<number | null>`${responseTable.totalAmount}`.as("total_amount"), currency: sql<string>`${responseTable.currency}`.as("currency"), - + // 주요 조건 차이 deliveryDateMatch: sql<boolean>` CASE @@ -266,28 +279,28 @@ export const vendorResponseSummaryView = pgView("vendor_response_summary_view"). ELSE false END `.as("delivery_date_match"), - + currencyMatch: sql<boolean>` CASE WHEN ${detailTable.currency} = ${responseTable.vendorCurrency} THEN true ELSE false END `.as("currency_match"), - + // 아이템 수 quotedItemCount: sql<number>`( SELECT COUNT(*) FROM rfq_last_vendor_quotation_items WHERE vendor_response_id = ${responseTable.id} )`.as("quoted_item_count"), - + // 첨부파일 수 attachmentCount: sql<number>`( SELECT COUNT(*) FROM rfq_last_vendor_attachments WHERE vendor_response_id = ${responseTable.id} )`.as("attachment_count"), - + // 타임스탬프 createdAt: sql<Date>`${responseTable.createdAt}`.as("created_at"), updatedAt: sql<Date>`${responseTable.updatedAt}`.as("updated_at"), @@ -311,10 +324,10 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det // 견적 아이템 정보 quotationItemId: sql<number>`${quotationTable.id}`.as("quotation_item_id"), vendorResponseId: sql<number>`${quotationTable.vendorResponseId}`.as("vendor_response_id"), - + // RFQ 정보 rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"), - + // PR 아이템 정보 prNo: sql<string | null>`${prItemTable.prNo}`.as("pr_no"), prItem: sql<string | null>`${prItemTable.prItem}`.as("pr_item"), @@ -322,7 +335,7 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det materialDescription: sql<string | null>`${prItemTable.materialDescription}`.as("material_description"), originalQuantity: sql<number | null>`${prItemTable.quantity}`.as("original_quantity"), originalDeliveryDate: sql<Date | null>`${prItemTable.deliveryDate}`.as("original_delivery_date"), - + // 벤더 견적 정보 quotedQuantity: sql<number>`${quotationTable.quantity}`.as("quoted_quantity"), unitPrice: sql<number>`${quotationTable.unitPrice}`.as("unit_price"), @@ -330,19 +343,19 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det currency: sql<string>`${quotationTable.currency}`.as("currency"), vendorDeliveryDate: sql<Date | null>`${quotationTable.vendorDeliveryDate}`.as("vendor_delivery_date"), leadTime: sql<number | null>`${quotationTable.leadTime}`.as("lead_time"), - + // 제조사 정보 manufacturer: sql<string | null>`${quotationTable.manufacturer}`.as("manufacturer"), modelNo: sql<string | null>`${quotationTable.modelNo}`.as("model_no"), - + // 할인 정보 discountRate: sql<number | null>`${quotationTable.discountRate}`.as("discount_rate"), discountAmount: sql<number | null>`${quotationTable.discountAmount}`.as("discount_amount"), - + // 기술 준수 technicalCompliance: sql<boolean>`${quotationTable.technicalCompliance}`.as("technical_compliance"), alternativeProposal: sql<string | null>`${quotationTable.alternativeProposal}`.as("alternative_proposal"), - + // 비고 itemRemark: sql<string | null>`${quotationTable.itemRemark}`.as("item_remark"), deviationReason: sql<string | null>`${quotationTable.deviationReason}`.as("deviation_reason"), @@ -354,27 +367,93 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det }); // Relations -export const vendorResponseRelations = relations( +export const vendorQuotationItemsRelations = relations( + rfqLastVendorQuotationItems, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorQuotationItems.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + prItem: one(rfqPrItems, { + fields: [rfqLastVendorQuotationItems.rfqPrItemId], + references: [rfqPrItems.id], + }), + }) + ); + + // 첨부파일 테이블의 relation + export const vendorAttachmentsRelations = relations( + rfqLastVendorAttachments, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorAttachments.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + uploadedByUser: one(users, { + fields: [rfqLastVendorAttachments.uploadedBy], + references: [users.id], + }), + }) + ); + + // 이력 테이블의 relation + export const vendorResponseHistoryRelations = relations( + rfqLastVendorResponseHistory, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorResponseHistory.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + performedByUser: one(users, { + fields: [rfqLastVendorResponseHistory.performedBy], + references: [users.id], + }), + }) + ); + + // 메인 응답 테이블의 relation (이미 있지만 완전한 버전) + export const vendorResponseRelations = relations( rfqLastVendorResponses, ({ one, many }) => ({ - rfq: one(rfqsLast, { - fields: [rfqLastVendorResponses.rfqsLastId], - references: [rfqsLast.id], - }), - rfqDetail: one(rfqLastDetails, { - fields: [rfqLastVendorResponses.rfqLastDetailsId], - references: [rfqLastDetails.id], - }), - vendor: one(vendors, { - fields: [rfqLastVendorResponses.vendorId], - references: [vendors.id], - }), - quotationItems: many(rfqLastVendorQuotationItems), - attachments: many(rfqLastVendorAttachments), - history: many(rfqLastVendorResponseHistory), + rfq: one(rfqsLast, { + fields: [rfqLastVendorResponses.rfqsLastId], + references: [rfqsLast.id], + }), + rfqDetail: one(rfqLastDetails, { + fields: [rfqLastVendorResponses.rfqLastDetailsId], + references: [rfqLastDetails.id], + }), + vendor: one(vendors, { + fields: [rfqLastVendorResponses.vendorId], + references: [vendors.id], + }), + submittedByUser: one(users, { + fields: [rfqLastVendorResponses.submittedBy], + references: [users.id], + relationName: "vendorResponseSubmittedBy", + }), + participationRepliedByUser: one(users, { + fields: [rfqLastVendorResponses.participationRepliedBy], + references: [users.id], + relationName: "vendorResponseParticipationRepliedBy", + }), + createdByUser: one(users, { + fields: [rfqLastVendorResponses.createdBy], + references: [users.id], + relationName: "vendorResponseCreatedBy", + }), + updatedByUser: one(users, { + fields: [rfqLastVendorResponses.updatedBy], + references: [users.id], + relationName: "vendorResponseUpdatedBy", + }), + quotationItems: many(rfqLastVendorQuotationItems), + attachments: many(rfqLastVendorAttachments), + history: many(rfqLastVendorResponseHistory), }) -); + ); + // Type exports export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect; export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect; |