(대표님) 작업사항 - rfqLast, tbeLast, pdfTron, userAuth

2 files changed, 340 insertions, 95 deletions

diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts
index ba7e30b5..1efb43bb 100644
--- a/db/schema/rfqLastTBE.ts
+++ b/db/schema/rfqLastTBE.ts
@@ -40,7 +40,7 @@ export const rfqLastTbeSessions = pgTable(
 
         // 평가 결과 (단순화)
         evaluationResult: varchar("evaluation_result", { length: 30 })
-            .$type<"pass" | "conditional_pass" | "non_pass" | null>(),
+            .$type<"Acceptable" | "Acceptable with Comment" | "Not Acceptable" | null>(),
 
         // 조건부 승인 시 조건
         conditionalRequirements: text("conditional_requirements"),
@@ -123,7 +123,7 @@ export const rfqLastTbeDocumentReviews = pgTable(
 
         // 벤더 문서인 경우
         vendorAttachmentId: integer("vendor_attachment_id")
-            .references(() => rfqLastVendorAttachments.id, { onDelete: "cascade" }),
+            .references(() => rfqLastTbeVendorDocuments.id, { onDelete: "cascade" }),
 
         // 검토 정보
         documentType: varchar("document_type", { length: 50 }),
@@ -169,6 +169,7 @@ export const rfqLastTbeDocumentReviews = pgTable(
 // ==========================================
 // 3. PDFTron 코멘트 관리
 // ==========================================
+// 수정된 스키마 (버전 관리 제거)
 export const rfqLastTbePdftronComments = pgTable(
     "rfq_last_tbe_pdftron_comments",
     {
@@ -177,65 +178,80 @@ export const rfqLastTbePdftronComments = pgTable(
             .notNull()
             .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "cascade" }),
 
-        // PDFTron 관련 정보
+        // 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;
-        }[]>(),
+        // XFDF XML 전체 저장 (모든 annotation 포함)
+        xfdfString: text("xfdf_string").notNull(),
+
+        // 파싱된 annotation 데이터 (검색/필터링용)
+        annotationData: jsonb("annotation_data").$type<{
+            annotations: {
+                id: string;
+                type: string;
+                page: number;
+                author: string;
+                subject: string;
+                contents?: string;
+                color?: string;
+                opacity?: number;
+                createdDate: string;
+                modifiedDate?: string;
+                customData?: {
+                    category?: "technical" | "commercial" | "quality" | "compliance" | "general";
+                    severity?: "minor" | "major" | "critical";
+                    status?: "open" | "resolved" | "rejected" | "deferred";
+                    createdBy?: number;
+                    createdByType?: "buyer" | "vendor";
+                    resolvedBy?: number;
+                    resolvedAt?: string;
+                    resolutionNote?: string;
+                };
+                replies?: {
+                    author: string;
+                    contents: string;
+                    createdDate: string;
+                }[];
+                coords?: number[]; // 좌표 데이터
+            }[];
+        }>(),
+
+        // 요약 정보 (빠른 조회용)
+        commentSummary: jsonb("comment_summary").$type<{
+            total: number;
+            open: number;
+            resolved: number;
+            rejected: number;
+            deferred: number;
+            byCategory: Record<string, number>;
+            bySeverity: Record<string, number>;
+            byAuthor: Record<string, number>;
+        }>(),
 
         // 작성자 정보
         createdBy: integer("created_by")
             .notNull()
-            .references(() => users.id, { onDelete: "set null" }),
+            .references(() => users.id, { onDelete: "restrict" }),
         createdByType: varchar("created_by_type", { length: 20 })
             .$type<"buyer" | "vendor">()
             .notNull(),
 
+        // 마지막 수정자
+        lastModifiedBy: integer("last_modified_by")
+            .references(() => users.id, { onDelete: "set null" }),
+
         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),
-    })
-);
+        documentIdIdx: index("idx_pdftron_doc_id").on(table.pdftronDocumentId),
 
+        // documentReviewId와 pdftronDocumentId 조합 유니크
+        uniqueDocument: uniqueIndex("unique_document")
+            .on(table.documentReviewId, table.pdftronDocumentId),
+    })
+)
 // ==========================================
 // 4. TBE 새로운 벤더 첨부파일 (TBE 중 추가 제출)
 // ==========================================
@@ -478,6 +494,9 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => {
             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"),
+            picName: sql<string>`${rfqsLast.picName}`.as("pic_name"),
+            EngPicName: sql<string>`${rfqsLast.EngPicName}`.as("eng_pic_name"),
+
 
             // 패키지 정보
             packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
@@ -591,68 +610,74 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => {
 // TBE 문서 상세 뷰 (구매자 + 벤더 문서 통합)
 // ==========================================
 export const tbeDocumentsView = pgView("tbe_documents_view").as((qb) => {
-    const ba = alias(rfqLastAttachments, "ba");
-    const baRev = alias(rfqLastAttachmentRevisions, "ba_rev");
+    const dr = alias(rfqLastTbeDocumentReviews, "dr")
+    const ba = alias(rfqLastAttachments, "ba")
+    const baRev = alias(rfqLastAttachmentRevisions, "ba_rev")
+    const vd = alias(rfqLastTbeVendorDocuments, "vd")
+
     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"),
+            // 기본키/세션
+            documentReviewId: sql<number>`dr.id`.as("document_review_id"),
+            tbeSessionId: sql<number>`dr.tbe_session_id`.as("tbe_session_id"),
+
+            // 소스
+            documentSource: sql<"buyer" | "vendor">`dr.document_source`.as("document_source"),
+
+            // 문서 식별자: buyer면 buyerAttachmentId, vendor면 vendorAttachmentId
+            documentId: sql<number | null>`
+          CASE 
+            WHEN dr.document_source = 'buyer' THEN dr.buyer_attachment_id
+            WHEN dr.document_source = 'vendor' THEN dr.vendor_attachment_id
+            ELSE NULL
+          END
+        `.as("document_id"),
+
+            // 표시 정보
+            documentType: sql<string | null>`dr.document_type`.as("document_type"),
+            documentName: sql<string | null>`dr.document_name`.as("document_name"),
+
+            // 파일 메타: buyer면 ba_rev.*, vendor면 vd.*
             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"),
+            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"),
+            // 리뷰 상태/정보 (dr 기준)
+            reviewStatus: sql<string>`dr.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 관련
+            // 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"),
+            // 검토자/타임스탬프
+            reviewedBy: sql<number | null>`dr.reviewed_by`.as("reviewed_by"),
+            reviewedAt: sql<Date | null>`dr.reviewed_at`.as("reviewed_at"),
+            reviewComments: sql<string | null>`dr.review_comments`.as("review_comments"),
+
+            // 제출 정보(벤더 문서일 때 vd의 제출 정보 노출, 아니면 null)
+            submittedBy: sql<number | null>`
+          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_by ELSE NULL END
+        `.as("submitted_by"),
+            submittedAt: sql<Date | null>`
+          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_at ELSE NULL END
+        `.as("submitted_at"),
+
+            // 생성/업데이트 시각: 리뷰기준
+            createdAt: sql<Date>`dr.created_at`.as("created_at"),
+            updatedAt: sql<Date>`dr.updated_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
-    `
-        )
+        .from(dr)
+        // buyer: 리뷰가 가리키는 첨부 개정에 조인
         .leftJoin(ba, sql`dr.buyer_attachment_id = ${ba.id}`)
-        .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`);
-});
+        .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`)
+        // vendor: 리뷰가 가리키는 벤더 문서에 조인
+        .leftJoin(vd, sql`dr.vendor_attachment_id = ${vd.id}`)
+})
 
 // Type exports
 export type TbeLastView = typeof tbeLastView.$inferSelect;
diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts
index 5752b1c2..0ddf109b 100644
--- a/db/schema/rfqVendor.ts
+++ b/db/schema/rfqVendor.ts
@@ -1,9 +1,10 @@
 import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb } from "drizzle-orm/pg-core";
-import { eq, sql, relations } from "drizzle-orm";
+import { eq, sql, relations,and } from "drizzle-orm";
 import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast";
 import { users } from "./users";
 import { vendors } from "./vendors";
 import { incoterms, paymentTerms } from "./procurementRFQ";
+import { projects } from "./projects";
 
 // ==========================================
 // 1. 벤더 응답 메인 테이블 (견적서 헤더)
@@ -458,4 +459,223 @@ export const vendorQuotationItemsRelations = relations(
 export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect;
 export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect;
 export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect;
-export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;
\ No newline at end of file
+export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;
+
+
+// vendorQuotationView - 벤더별 견적 현황을 보여주는 통합 뷰
+export const vendorQuotationView = pgView("vendor_quotation_view").as((qb) => {
+  const createdByUser = alias(users, "created_by_user");
+  const updatedByUser = alias(users, "updated_by_user");
+  const sentByUser = alias(users, "sent_by_user");
+  const picUser = alias(users, "pic_user");
+
+  return qb
+      .select({
+          // ===== RFQ 기본 정보 (rfqsLastView에서 가져온 필드들) =====
+          id: sql<number>`${rfqsLast.id}`.as("id"),
+          rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"),
+          series: sql<string | null>`${rfqsLast.series}`.as("series"),
+          rfqSealedYn: sql<boolean | null>`${rfqsLast.rfqSealedYn}`.as("rfq_sealed_yn"),
+          
+          // RFQ 타입 정보
+          rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"),
+          rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"),
+          
+          // ITB 관련 필드
+          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"),
+          smCode: sql<string | null>`${rfqsLast.smCode}`.as("sm_code"),
+          
+          // RFQ 추가 필드
+          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"),
+          
+          // 아이템 정보
+          itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"),
+          itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"),
+          
+          // 패키지 정보
+          packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
+          packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"),
+          
+          engPicName: sql<string | null>`${rfqsLast.EngPicName}`.as("eng_pic_name"),
+          
+          // 상태와 날짜
+          status: sql<string>`${rfqsLast.status}`.as("status"),
+          rfqSendDate: sql<Date | null>`${rfqsLast.rfqSendDate}`.as("rfq_send_date"),
+          dueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("due_date"),
+          
+          // PIC 정보
+          picId: sql<number | null>`${rfqsLast.pic}`.as("pic_id"),
+          picCode: sql<string | null>`${rfqsLast.picCode}`.as("pic_code"),
+          picName: sql<string | null>`${rfqsLast.picName}`.as("pic_name"),
+          picUserName: sql<string | null>`${picUser.name}`.as("pic_user_name"),
+          
+          // 감사 정보
+          createdBy: sql<number>`${rfqsLast.createdBy}`.as("created_by"),
+          createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"),
+          createdAt: sql<Date>`${rfqsLast.createdAt}`.as("created_at"),
+          sentBy: sql<number | null>`${rfqsLast.sentBy}`.as("sent_by"),
+          sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"),
+          updatedBy: sql<number>`${rfqsLast.updatedBy}`.as("updated_by"),
+          updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+          updatedAt: sql<Date>`${rfqsLast.updatedAt}`.as("updated_at"),
+          remark: sql<string | null>`${rfqsLast.remark}`.as("remark"),
+
+          // ===== 벤더별 정보 =====
+          vendorId: sql<number | null>`${vendors.id}`.as("vendor_id"),
+          vendorName: sql<string | null>`${vendors.vendorName}`.as("vendor_name"),
+          vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+          
+          // rfqLastDetails 정보
+          rfqLastDetailsId: sql<number | null>`${rfqLastDetails.id}`.as("rfq_last_details_id"),
+          emailSentAt: sql<Date | null>`${rfqLastDetails.emailSentAt}`.as("email_sent_at"),
+          emailStatus: sql<string | null>`${rfqLastDetails.emailStatus}`.as("email_status"),
+          shortList: sql<boolean>`${rfqLastDetails.shortList}`.as("short_list"),
+          
+          // ===== 벤더 응답 정보 (rfqLastVendorResponses) =====
+          vendorResponseId: sql<number | null>`${rfqLastVendorResponses.id}`.as("vendor_response_id"),
+          
+          // 참여 상태
+          participationStatus: sql<string | null>`${rfqLastVendorResponses.participationStatus}`.as("participation_status"),
+          participationRepliedAt: sql<Date | null>`${rfqLastVendorResponses.participationRepliedAt}`.as("participation_replied_at"),
+          nonParticipationReason: sql<string | null>`${rfqLastVendorResponses.nonParticipationReason}`.as("non_participation_reason"),
+          
+          // 응답 상태
+          responseStatus: sql<string | null>`${rfqLastVendorResponses.status}`.as("response_status"),
+          responseVersion: sql<number | null>`${rfqLastVendorResponses.responseVersion}`.as("response_version"),
+          submittedAt: sql<Date | null>`${rfqLastVendorResponses.submittedAt}`.as("submitted_at"),
+          
+          // 금액 정보
+          totalAmount: sql<number | null>`${rfqLastVendorResponses.totalAmount}`.as("total_amount"),
+          vendorCurrency: sql<string | null>`${rfqLastVendorResponses.vendorCurrency}`.as("vendor_currency"),
+          
+          // 벤더 제안 조건
+          vendorPaymentTermsCode: sql<string | null>`${rfqLastVendorResponses.vendorPaymentTermsCode}`.as("vendor_payment_terms_code"),
+          vendorIncotermsCode: sql<string | null>`${rfqLastVendorResponses.vendorIncotermsCode}`.as("vendor_incoterms_code"),
+          vendorDeliveryDate: sql<Date | null>`${rfqLastVendorResponses.vendorDeliveryDate}`.as("vendor_delivery_date"),
+          
+          // ===== 계산된 필드 - displayStatus =====
+          displayStatus: sql<string | null>`
+              CASE 
+                  WHEN ${rfqLastVendorResponses.participationStatus} = '불참' THEN '불참'
+                  WHEN ${rfqLastVendorResponses.participationStatus} = '참여' THEN 
+                      COALESCE(${rfqLastVendorResponses.status}, '작성중')
+                  WHEN ${rfqLastVendorResponses.participationStatus} = '미응답' OR ${rfqLastVendorResponses.participationStatus} IS NULL THEN
+                      CASE
+                          WHEN ${rfqLastDetails.emailSentAt} IS NOT NULL THEN '미응답'
+                          ELSE NULL
+                      END
+                  ELSE '미응답'
+              END
+          `.as("display_status"),
+
+          // ===== 집계 정보 (RFQ 레벨) =====
+          vendorCount: sql<number>`(
+              SELECT COUNT(*)
+              FROM rfq_last_details d
+              WHERE d.rfqs_last_id = ${rfqsLast.id}
+              AND d.is_latest = true
+          )`.as("vendor_count"),
+          
+          shortListedVendorCount: sql<number>`(
+              SELECT COUNT(*)
+              FROM rfq_last_details d
+              WHERE d.rfqs_last_id = ${rfqsLast.id}
+              AND d.short_list = true
+              AND d.is_latest = true
+          )`.as("short_listed_vendor_count"),
+          
+          quotationReceivedCount: sql<number>`(
+              SELECT COUNT(DISTINCT r.vendor_id)
+              FROM rfq_last_vendor_responses r
+              WHERE r.rfqs_last_id = ${rfqsLast.id}
+              AND r.submitted_at IS NOT NULL
+              AND r.is_latest = true
+          )`.as("quotation_received_count"),
+          
+          earliestQuotationSubmittedAt: sql<Date | null>`(
+              SELECT MIN(r.submitted_at)
+              FROM rfq_last_vendor_responses r
+              WHERE r.rfqs_last_id = ${rfqsLast.id}
+              AND r.submitted_at IS NOT NULL
+              AND r.is_latest = true
+          )`.as("earliest_quotation_submitted_at"),
+
+          // PR Items 관련 정보
+          majorItemMaterialCode: sql<string | null>`(
+              SELECT material_code 
+              FROM rfq_pr_items 
+              WHERE rfqs_last_id = ${rfqsLast.id}
+              AND major_yn = true
+              LIMIT 1
+          )`.as("major_item_material_code"),
+
+          majorItemMaterialDescription: sql<string | null>`(
+              SELECT material_description 
+              FROM rfq_pr_items 
+              WHERE rfqs_last_id = ${rfqsLast.id}
+              AND major_yn = true
+              LIMIT 1
+          )`.as("major_item_material_description"),
+
+          majorItemMaterialCategory: sql<string | null>`(
+              SELECT material_category 
+              FROM rfq_pr_items 
+              WHERE rfqs_last_id = ${rfqsLast.id}
+              AND major_yn = true
+              LIMIT 1
+          )`.as("major_item_material_category"),
+
+          majorItemPrNo: sql<string | null>`(
+              SELECT pr_no 
+              FROM rfq_pr_items 
+              WHERE rfqs_last_id = ${rfqsLast.id}
+              AND major_yn = true
+              LIMIT 1
+          )`.as("major_item_pr_no"),
+
+          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")
+      })
+      .from(rfqsLast)
+      .innerJoin(rfqLastDetails, 
+          and(
+              eq(rfqLastDetails.rfqsLastId, rfqsLast.id),
+              eq(rfqLastDetails.isLatest, true)
+          )
+      )
+      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
+      .leftJoin(rfqLastVendorResponses,
+          and(
+              eq(rfqLastVendorResponses.rfqsLastId, rfqsLast.id),
+              eq(rfqLastVendorResponses.vendorId, vendors.id),
+              eq(rfqLastVendorResponses.isLatest, true)
+          )
+      )
+      .leftJoin(projects, eq(rfqsLast.projectId, projects.id))
+      .leftJoin(createdByUser, eq(rfqsLast.createdBy, createdByUser.id))
+      .leftJoin(updatedByUser, eq(rfqsLast.updatedBy, updatedByUser.id))
+      .leftJoin(sentByUser, eq(rfqsLast.sentBy, sentByUser.id))
+      .leftJoin(picUser, eq(rfqsLast.pic, picUser.id));
+});
+
+// Type export
+export type VendorQuotationView = typeof vendorQuotationView.$inferSelect;
+