diff options
Diffstat (limited to 'db/schema/rfqLastTBE.ts')
| -rw-r--r-- | db/schema/rfqLastTBE.ts | 659 |
1 files changed, 659 insertions, 0 deletions
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 |