(대표님) 20250617 18시 작업사항

1 files changed, 744 insertions, 87 deletions

diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts
index 115d2c6e..3cd1e98a 100644
--- a/db/schema/bRfq.ts
+++ b/db/schema/bRfq.ts
@@ -1,5 +1,5 @@
 import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core";
-import { eq, sql, and } from "drizzle-orm";
+import { eq, sql, and, relations } from "drizzle-orm";
 import { projects } from "./projects";
 import { users } from "./users";
 import { vendors } from "./vendors";
@@ -94,7 +94,10 @@ export const initialRfq = pgTable("initial_rfq", {
     prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false),
 
     returnRevision: integer("return_revision")
-    .notNull().default(0),
+        .notNull().default(0),
+
+    rfqRevision: integer("rfq_revision")
+        .notNull().default(0),
 
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
@@ -139,17 +142,17 @@ export const finalRfq = pgTable("final_rfq", {
     prjectGtcYn: boolean('prject_gtc_yn').notNull().default(true),
 
     returnRevision: integer("return_revision")
-    .notNull().default(0),
+        .notNull().default(0),
 
     currency: varchar("currency", { length: 10 }).default("KRW"),
 
     paymentTermsCode: varchar("payment_terms_code", { length: 50 })
-    .references(() => paymentTerms.code, { onDelete: "set null" }),
+        .references(() => paymentTerms.code, { onDelete: "set null" }),
 
     taxCode: varchar("tax_code", { length: 255 }).default("VV"),
     deliveryDate: date("delivery_date", { mode: "date" })
-      .$type<Date>()
-      .notNull(),
+        .$type<Date>()
+        .notNull(),
 
     placeOfShipping: varchar("place_of_shipping", { length: 255 }),
     placeOfDestination: varchar("place_of_destination", { length: 255 }),
@@ -167,68 +170,68 @@ export const finalRfq = pgTable("final_rfq", {
 export const bRfqsAttachments = pgTable(
     "b_rfq_attachments",
     {
-      id: serial("id").primaryKey(),
-      attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
-      serialNo: varchar("serial_no", { length: 50 }).notNull(),
-      rfqId: integer("rfq_id")
-        .notNull()
-        .references(() => bRfqs.id),
-      
-      // 현재 리비전 정보 (빠른 접근용)
-      currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"),
-      latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions
-      
-      // 메타 정보
-      description: varchar("description", { length: 500 }),
-      createdBy: integer("created_by")
-        .references(() => users.id, { onDelete: "set null" })
-        .notNull(),
-      createdAt: timestamp("created_at").defaultNow().notNull(),
-      updatedAt: timestamp("updated_at").defaultNow().notNull(),
+        id: serial("id").primaryKey(),
+        attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
+        serialNo: varchar("serial_no", { length: 50 }).notNull(),
+        rfqId: integer("rfq_id")
+            .notNull()
+            .references(() => bRfqs.id),
+
+        // 현재 리비전 정보 (빠른 접근용)
+        currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"),
+        latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions
+
+        // 메타 정보
+        description: varchar("description", { length: 500 }),
+        createdBy: integer("created_by")
+            .references(() => users.id, { onDelete: "set null" })
+            .notNull(),
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
     }
-  )
-  
-  // 리비전 테이블 (모든 파일 버전 관리)
-  export const bRfqAttachmentRevisions = pgTable(
+)
+
+// 리비전 테이블 (모든 파일 버전 관리)
+export const bRfqAttachmentRevisions = pgTable(
     "b_rfq_attachment_revisions",
     {
-      id: serial("id").primaryKey(),
-      attachmentId: integer("attachment_id")
-        .notNull()
-        .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
-        
-      // 리비전 정보
-      revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
-      revisionComment: text("revision_comment"),
-      isLatest: boolean("is_latest").notNull().default(true),
-      
-      // 파일 정보
-      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 }),
-      
-      // 리비전 생성 정보
-      createdBy: integer("created_by")
-        .references(() => users.id, { onDelete: "set null" })
-        .notNull(),
-      createdAt: timestamp("created_at").defaultNow().notNull(),
-    }, 
+        id: serial("id").primaryKey(),
+        attachmentId: integer("attachment_id")
+            .notNull()
+            .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
+
+        // 리비전 정보
+        revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
+        revisionComment: text("revision_comment"),
+        isLatest: boolean("is_latest").notNull().default(true),
+
+        // 파일 정보
+        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 }),
+
+        // 리비전 생성 정보
+        createdBy: integer("created_by")
+            .references(() => users.id, { onDelete: "set null" })
+            .notNull(),
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+    },
     (t) => ({
-      // 첨부파일당 하나의 최신 리비전만 허용
-      latestRevisionIdx: uniqueIndex('latest_revision_idx')
-        .on(t.attachmentId, t.isLatest)
-        .where(eq(t.isLatest, true)),
-      
-      // 첨부파일 + 리비전 번호 유니크
-      attachmentRevisionIdx: uniqueIndex('attachment_revision_idx')
-        .on(t.attachmentId, t.revisionNo),
+        // 첨부파일당 하나의 최신 리비전만 허용
+        latestRevisionIdx: uniqueIndex('latest_revision_idx')
+            .on(t.attachmentId, t.isLatest)
+            .where(eq(t.isLatest, true)),
+
+        // 첨부파일 + 리비전 번호 유니크
+        attachmentRevisionIdx: uniqueIndex('attachment_revision_idx')
+            .on(t.attachmentId, t.revisionNo),
     })
-  )
-  
-  // 첨부파일 + 최신 리비전 뷰
-  export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", {
+)
+
+// 첨부파일 + 최신 리비전 뷰
+export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", {
     // 메인 첨부파일 정보
     attachmentId: integer("attachment_id"),
     attachmentType: varchar("attachment_type", { length: 50 }),
@@ -236,7 +239,7 @@ export const bRfqsAttachments = pgTable(
     rfqId: integer("rfq_id"),
     description: varchar("description", { length: 500 }),
     currentRevision: varchar("current_revision", { length: 10 }),
-    
+
     // 최신 리비전 파일 정보
     revisionId: integer("revision_id"),
     fileName: varchar("file_name", { length: 255 }),
@@ -245,13 +248,13 @@ export const bRfqsAttachments = pgTable(
     fileSize: integer("file_size"),
     fileType: varchar("file_type", { length: 100 }),
     revisionComment: text("revision_comment"),
-    
+
     // 생성/수정 정보
     createdBy: integer("created_by"),
     createdByName: varchar("created_by_name", { length: 255 }),
     createdAt: timestamp("created_at"),
     updatedAt: timestamp("updated_at"),
-  }).as(sql`
+}).as(sql`
     SELECT 
       a.id as attachment_id,
       a.attachment_type,
@@ -279,46 +282,50 @@ export const bRfqsAttachments = pgTable(
 
 // 2. 벤더별 첨부파일 응답 현황 관리
 export const vendorAttachmentResponses = pgTable(
-    "vendor_attachment_responses", 
+    "vendor_attachment_responses",
     {
         id: serial("id").primaryKey(),
         attachmentId: integer("attachment_id")
             .notNull()
             .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
-        
+
         vendorId: integer("vendor_id")
             .notNull()
             .references(() => vendors.id, { onDelete: "cascade" }),
-            
+
         rfqType: varchar("rfq_type", { length: 20 })
             .$type<"INITIAL" | "FINAL">()
             .notNull(), // initial_rfq 또는 final_rfq 구분
-            
+
         rfqRecordId: integer("rfq_record_id").notNull(), // initialRfq.id 또는 finalRfq.id
-        
+
         responseStatus: varchar("response_status", { length: 30 })
             .$type<"NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED">()
             .default("NOT_RESPONDED")
             .notNull(),
-            
+
         currentRevision: varchar("current_revision", { length: 10 }).default("Rev.0"),
         respondedRevision: varchar("responded_revision", { length: 10 }),
-        
+
         responseComment: text("response_comment"),
         vendorComment: text("vendor_comment"),
-        
+
+        revisionRequestComment: text("revision_request_comment"),
+
+
         // 응답 관련 날짜
         requestedAt: timestamp("requested_at").notNull(),
         respondedAt: timestamp("responded_at"),
-        
+        revisionRequestedAt: timestamp("revision_requested_at"),
+
         createdAt: timestamp("created_at").defaultNow().notNull(),
         updatedAt: timestamp("updated_at").defaultNow().notNull(),
-    },(t) => ({
+    }, (t) => ({
         // attachmentId + vendorId + rfqType 유니크
         vendorResponseIdx: uniqueIndex('vendor_response_idx').on(
-          t.attachmentId.asc(),
-          t.vendorId.asc(),
-          t.rfqType.asc(),
+            t.attachmentId.asc(),
+            t.vendorId.asc(),
+            t.rfqType.asc(),
         ),
     }));
 
@@ -330,15 +337,15 @@ export const vendorResponseAttachmentsB = pgTable(
         vendorResponseId: integer("vendor_response_id")
             .notNull()
             .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }),
-            
+
         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: varchar("description", { length: 500 }),
-        
+
         uploadedBy: integer("uploaded_by")
             .references(() => users.id, { onDelete: "set null" }),
         uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
@@ -353,16 +360,16 @@ export const vendorResponseHistory = pgTable(
         vendorResponseId: integer("vendor_response_id")
             .notNull()
             .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }),
-            
+
         action: varchar("action", { length: 50 })
             .$type<"REQUESTED" | "RESPONDED" | "REVISION_REQUESTED" | "REVISED" | "WAIVED">()
             .notNull(),
-            
+
         previousStatus: varchar("previous_status", { length: 30 }),
         newStatus: varchar("new_status", { length: 30 }),
-        
+
         comment: text("comment"),
-        
+
         actionBy: integer("action_by")
             .references(() => users.id, { onDelete: "set null" }),
         actionAt: timestamp("action_at").defaultNow().notNull(),
@@ -434,6 +441,7 @@ export const initialRfqDetailView = pgView("initial_rfq_detail", {
     vendorId: integer("vendor_id"),
     vendorCode: varchar("vendor_code", { length: 50 }),
     vendorName: varchar("vendor_name", { length: 255 }),
+    vendorCategory: varchar("vendor_category", { length: 255 }),
     vendorCountry: varchar("vendor_country", { length: 100 }),
     vendorBusinessSize: varchar("vendor_business_size", { length: 50 }),
     dueDate: date("due_date"),
@@ -445,6 +453,7 @@ export const initialRfqDetailView = pgView("initial_rfq_detail", {
     cpRequestYn: boolean("cp_request_yn"),
     prjectGtcYn: boolean("prject_gtc_yn"),
     returnRevision: integer("return_revision"),
+    rfqRevision: integer("rfq_revision"),
     gtc: varchar("gtc", { length: 255 }),
     gtcValidDate: varchar("gtc_valid_date", { length: 255 }),
     classification: varchar("classification", { length: 255 }),
@@ -463,6 +472,7 @@ export const initialRfqDetailView = pgView("initial_rfq_detail", {
         v.vendor_name,
         v.country as vendor_country,
         v.business_size as vendor_business_size,
+        v.vendor_category as vendor_category,
         ir.due_date,
         ir.valid_date,
         ir.incoterms_code,
@@ -472,6 +482,7 @@ export const initialRfqDetailView = pgView("initial_rfq_detail", {
         ir.cp_request_yn,
         ir.prject_gtc_yn,
         ir.return_revision,
+        ir.rfq_revision,
         ir.gtc,
         ir.gtc_valid_date,
         ir.classification,
@@ -480,7 +491,7 @@ export const initialRfqDetailView = pgView("initial_rfq_detail", {
         ir.updated_at
     FROM b_rfqs br
     JOIN initial_rfq ir ON br.id = ir.rfq_id
-    LEFT JOIN vendors v ON ir.vendor_id = v.id
+    LEFT JOIN vendors_with_types v ON ir.vendor_id = v.id
     LEFT JOIN incoterms inc ON ir.incoterms_code = inc.code
 `);
 
@@ -740,3 +751,649 @@ export type ResponseAttachment = typeof vendorResponseAttachmentsB.$inferSelect;
 export type InitialRfqDetailView = typeof initialRfqDetailView.$inferSelect;
 export type FinalRfqDetailView = typeof finalRfqDetailView.$inferSelect;
 export type RfqDashboardView = typeof rfqDashboardView.$inferSelect;
+
+
+export const bRfqsRelations = relations(bRfqs, ({ one, many }) => ({
+    // 단일 관계
+    project: one(projects, {
+      fields: [bRfqs.projectId],
+      references: [projects.id],
+    }),
+    createdByUser: one(users, {
+      fields: [bRfqs.createdBy],
+      references: [users.id],
+      relationName: "bRfqCreatedBy"
+    }),
+    updatedByUser: one(users, {
+      fields: [bRfqs.updatedBy],
+      references: [users.id],
+      relationName: "bRfqUpdatedBy"
+    }),
+    
+    // 다중 관계
+    attachments: many(bRfqsAttachments),
+    initialRfqs: many(initialRfq),
+    finalRfqs: many(finalRfq),
+  }));
+  
+  // bRfqsAttachments 관계 정의
+  export const bRfqsAttachmentsRelations = relations(bRfqsAttachments, ({ one, many }) => ({
+    // 단일 관계
+    rfq: one(bRfqs, {
+      fields: [bRfqsAttachments.rfqId],
+      references: [bRfqs.id],
+    }),
+    createdByUser: one(users, {
+      fields: [bRfqsAttachments.createdBy],
+      references: [users.id],
+    }),
+    latestRevision: one(bRfqAttachmentRevisions, {
+      fields: [bRfqsAttachments.latestRevisionId],
+      references: [bRfqAttachmentRevisions.id],
+      relationName: "attachmentLatestRevision"
+    }),
+    
+    // 다중 관계
+    revisions: many(bRfqAttachmentRevisions),
+    vendorResponses: many(vendorAttachmentResponses),
+  }));
+  
+  // bRfqAttachmentRevisions 관계 정의
+  export const bRfqAttachmentRevisionsRelations = relations(bRfqAttachmentRevisions, ({ one }) => ({
+    attachment: one(bRfqsAttachments, {
+      fields: [bRfqAttachmentRevisions.attachmentId],
+      references: [bRfqsAttachments.id],
+    }),
+    createdByUser: one(users, {
+      fields: [bRfqAttachmentRevisions.createdBy],
+      references: [users.id],
+    }),
+  }));
+  
+  // vendorAttachmentResponses 관계 정의
+  export const vendorAttachmentResponsesRelations = relations(vendorAttachmentResponses, ({ one, many }) => ({
+    // 단일 관계
+    attachment: one(bRfqsAttachments, {
+      fields: [vendorAttachmentResponses.attachmentId],
+      references: [bRfqsAttachments.id],
+    }),
+    vendor: one(vendors, {
+      fields: [vendorAttachmentResponses.vendorId],
+      references: [vendors.id],
+    }),
+    
+    // 다중 관계
+    responseAttachments: many(vendorResponseAttachmentsB),
+    history: many(vendorResponseHistory),
+  }));
+  
+  // vendorResponseAttachmentsB 관계 정의
+  export const vendorResponseAttachmentsBRelations = relations(vendorResponseAttachmentsB, ({ one }) => ({
+    vendorResponse: one(vendorAttachmentResponses, {
+      fields: [vendorResponseAttachmentsB.vendorResponseId],
+      references: [vendorAttachmentResponses.id],
+    }),
+    uploadedByUser: one(users, {
+      fields: [vendorResponseAttachmentsB.uploadedBy],
+      references: [users.id],
+    }),
+  }));
+  
+  // vendorResponseHistory 관계 정의
+  export const vendorResponseHistoryRelations = relations(vendorResponseHistory, ({ one }) => ({
+    vendorResponse: one(vendorAttachmentResponses, {
+      fields: [vendorResponseHistory.vendorResponseId],
+      references: [vendorAttachmentResponses.id],
+    }),
+    actionByUser: one(users, {
+      fields: [vendorResponseHistory.actionBy],
+      references: [users.id],
+    }),
+  }));
+  
+  // initialRfq 관계 정의
+  export const initialRfqRelations = relations(initialRfq, ({ one }) => ({
+    rfq: one(bRfqs, {
+      fields: [initialRfq.rfqId],
+      references: [bRfqs.id],
+    }),
+    vendor: one(vendors, {
+      fields: [initialRfq.vendorId],
+      references: [vendors.id],
+    }),
+  }));
+  
+  // finalRfq 관계 정의
+  export const finalRfqRelations = relations(finalRfq, ({ one }) => ({
+    rfq: one(bRfqs, {
+      fields: [finalRfq.rfqId],
+      references: [bRfqs.id],
+    }),
+    vendor: one(vendors, {
+      fields: [finalRfq.vendorId],
+      references: [vendors.id],
+    }),
+  }));
+  
+
+
+// 업데이트된 vendorResponseAttachmentsEnhanced 뷰
+export const vendorResponseAttachmentsEnhanced = pgView("vendor_response_attachments_enhanced", {
+    // 벤더 응답 파일 기본 정보
+    responseAttachmentId: integer("response_attachment_id"),
+    vendorResponseId: integer("vendor_response_id"),
+    fileName: varchar("file_name", { length: 255 }),
+    originalFileName: varchar("original_file_name", { length: 255 }),
+    filePath: varchar("file_path", { length: 512 }),
+    fileSize: integer("file_size"),
+    fileType: varchar("file_type", { length: 100 }),
+    description: varchar("description", { length: 500 }),
+    uploadedAt: timestamp("uploaded_at"),
+    
+    // 응답 기본 정보
+    attachmentId: integer("attachment_id"),
+    vendorId: integer("vendor_id"),
+    rfqType: varchar("rfq_type", { length: 20 }),
+    rfqRecordId: integer("rfq_record_id"),
+    responseStatus: varchar("response_status", { length: 30 }),
+    currentRevision: varchar("current_revision", { length: 10 }),
+    respondedRevision: varchar("responded_revision", { length: 10 }),
+    
+    // 코멘트 관련 필드들 (새로 추가된 필드 포함)
+    responseComment: text("response_comment"),
+    vendorComment: text("vendor_comment"),
+    revisionRequestComment: text("revision_request_comment"), // 새로 추가
+    
+    // 날짜 관련 필드들 (새로 추가된 필드 포함)
+    requestedAt: timestamp("requested_at"),
+    respondedAt: timestamp("responded_at"),
+    revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가
+    
+    // 첨부파일 정보
+    attachmentType: varchar("attachment_type", { length: 50 }),
+    serialNo: varchar("serial_no", { length: 50 }),
+    rfqId: integer("rfq_id"),
+    
+    // 벤더 정보
+    vendorCode: varchar("vendor_code", { length: 50 }),
+    vendorName: varchar("vendor_name", { length: 255 }),
+    vendorCountry: varchar("vendor_country", { length: 100 }),
+    
+    // 발주처 현재 리비전 정보
+    latestClientRevisionId: integer("latest_client_revision_id"),
+    latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }),
+    latestClientFileName: varchar("latest_client_file_name", { length: 255 }),
+    
+    // 리비전 비교 정보
+    isVersionMatched: boolean("is_version_matched"),
+    versionLag: integer("version_lag"), // 몇 버전 뒤처져 있는지
+    needsUpdate: boolean("needs_update"),
+    
+    // 응답 파일 순서 (같은 응답에 대한 여러 파일이 있을 경우)
+    fileSequence: integer("file_sequence"),
+    isLatestResponseFile: boolean("is_latest_response_file"),
+    
+}).as(sql`
+    SELECT 
+        vra.id as response_attachment_id,
+        vra.vendor_response_id,
+        vra.file_name,
+        vra.original_file_name,
+        vra.file_path,
+        vra.file_size,
+        vra.file_type,
+        vra.description,
+        vra.uploaded_at,
+        
+        -- 응답 기본 정보
+        var.attachment_id,
+        var.vendor_id,
+        var.rfq_type,
+        var.rfq_record_id,
+        var.response_status,
+        var.current_revision,
+        var.responded_revision,
+        
+        -- 코멘트 (새로 추가된 필드 포함)
+        var.response_comment,
+        var.vendor_comment,
+        var.revision_request_comment,
+        
+        -- 날짜 (새로 추가된 필드 포함)
+        var.requested_at,
+        var.responded_at,
+        var.revision_requested_at,
+        
+        -- 첨부파일 정보
+        ba.attachment_type,
+        ba.serial_no,
+        ba.rfq_id,
+        
+        -- 벤더 정보
+        v.vendor_code,
+        v.vendor_name,
+        v.country as vendor_country,
+        
+        -- 발주처 현재 리비전 정보
+        latest_rev.id as latest_client_revision_id,
+        latest_rev.revision_no as latest_client_revision_no,
+        latest_rev.original_file_name as latest_client_file_name,
+        
+        -- 리비전 비교
+        CASE 
+            WHEN var.responded_revision = ba.current_revision THEN true 
+            ELSE false 
+        END as is_version_matched,
+        
+        -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정)
+        CASE 
+            WHEN var.responded_revision IS NULL THEN NULL
+            WHEN ba.current_revision IS NULL THEN NULL
+            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
+                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
+        END as version_lag,
+        
+        CASE 
+            WHEN var.response_status = 'RESPONDED' 
+                 AND var.responded_revision != ba.current_revision THEN true 
+            ELSE false 
+        END as needs_update,
+        
+        -- 파일 순서
+        ROW_NUMBER() OVER (
+            PARTITION BY var.id 
+            ORDER BY vra.uploaded_at DESC
+        ) as file_sequence,
+        
+        -- 최신 응답 파일 여부
+        CASE 
+            WHEN ROW_NUMBER() OVER (
+                PARTITION BY var.id 
+                ORDER BY vra.uploaded_at DESC
+            ) = 1 THEN true 
+            ELSE false 
+        END as is_latest_response_file
+        
+    FROM vendor_response_attachments_b vra
+    JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id
+    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
+    LEFT JOIN vendors v ON var.vendor_id = v.id
+    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
+`);
+
+// 2. 첨부파일별 리비전 히스토리 전체 뷰
+export const attachmentRevisionHistoryView = pgView("attachment_revision_history", {
+    rfqId: integer("rfq_id"),
+    rfqCode: varchar("rfq_code", { length: 50 }),
+    attachmentId: integer("attachment_id"),
+    attachmentType: varchar("attachment_type", { length: 50 }),
+    serialNo: varchar("serial_no", { length: 50 }),
+    
+    // 발주처 리비전 정보
+    clientRevisionId: integer("client_revision_id"),
+    clientRevisionNo: varchar("client_revision_no", { length: 10 }),
+    clientFileName: varchar("client_file_name", { length: 255 }),
+    clientFilePath: varchar("client_file_path", { length: 512 }),
+    clientFileSize: integer("client_file_size"),
+    clientRevisionComment: text("client_revision_comment"),
+    clientRevisionCreatedAt: timestamp("client_revision_created_at"),
+    isLatestClientRevision: boolean("is_latest_client_revision"),
+    
+    // 이 리비전에 대한 벤더 응답 통계
+    totalVendorResponses: integer("total_vendor_responses"),
+    respondedVendors: integer("responded_vendors"),
+    pendingVendors: integer("pending_vendors"),
+    totalResponseFiles: integer("total_response_files"),
+    
+}).as(sql`
+    SELECT 
+        br.id as rfq_id,
+        br.rfq_code,
+        ba.id as attachment_id,
+        ba.attachment_type,
+        ba.serial_no,
+        
+        -- 발주처 리비전 정보
+        rev.id as client_revision_id,
+        rev.revision_no as client_revision_no,
+        rev.original_file_name as client_file_name,
+        rev.file_size as client_file_size,
+        rev.file_path as client_file_path,
+        rev.revision_comment as client_revision_comment,
+        rev.created_at as client_revision_created_at,
+        rev.is_latest as is_latest_client_revision,
+        
+        -- 벤더 응답 통계
+        COALESCE(response_stats.total_responses, 0) as total_vendor_responses,
+        COALESCE(response_stats.responded_count, 0) as responded_vendors,
+        COALESCE(response_stats.pending_count, 0) as pending_vendors,
+        COALESCE(response_stats.total_files, 0) as total_response_files
+        
+    FROM b_rfqs br
+    JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
+    JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id
+    LEFT JOIN (
+        SELECT 
+            var.attachment_id,
+            COUNT(*) as total_responses,
+            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
+            COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
+            COUNT(vra.id) as total_files
+        FROM vendor_attachment_responses var
+        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
+        GROUP BY var.attachment_id
+    ) response_stats ON ba.id = response_stats.attachment_id
+    
+    ORDER BY ba.id, rev.created_at DESC
+`);
+
+// 3. 벤더별 응답 현황 상세 뷰 (리비전 정보 포함)
+// 업데이트된 vendorResponseDetailView
+export const vendorResponseDetailView = pgView("vendor_response_detail", {
+    // 기본 식별 정보
+    responseId: integer("response_id"),
+    rfqId: integer("rfq_id"),
+    rfqCode: varchar("rfq_code", { length: 50 }),
+    rfqType: varchar("rfq_type", { length: 20 }),
+    rfqRecordId: integer("rfq_record_id"),
+    
+    // 첨부파일 정보
+    attachmentId: integer("attachment_id"),
+    attachmentType: varchar("attachment_type", { length: 50 }),
+    serialNo: varchar("serial_no", { length: 50 }),
+    attachmentDescription: varchar("attachment_description", { length: 500 }),
+    
+    // 벤더 정보
+    vendorId: integer("vendor_id"),
+    vendorCode: varchar("vendor_code", { length: 50 }),
+    vendorName: varchar("vendor_name", { length: 255 }),
+    vendorCountry: varchar("vendor_country", { length: 100 }),
+    
+    // 응답 상태 정보
+    responseStatus: varchar("response_status", { length: 30 }),
+    currentRevision: varchar("current_revision", { length: 10 }),
+    respondedRevision: varchar("responded_revision", { length: 10 }),
+    
+    // 코멘트 관련 필드들 (새로 추가된 필드 포함)
+    responseComment: text("response_comment"),
+    vendorComment: text("vendor_comment"),
+    revisionRequestComment: text("revision_request_comment"), // 새로 추가
+    
+    // 날짜 관련 필드들 (새로 추가된 필드 포함)
+    requestedAt: timestamp("requested_at"),
+    respondedAt: timestamp("responded_at"),
+    revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가
+    
+    // 발주처 최신 리비전 정보
+    latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }),
+    latestClientFileName: varchar("latest_client_file_name", { length: 255 }),
+    latestClientFileSize: integer("latest_client_file_size"),
+    latestClientRevisionComment: text("latest_client_revision_comment"),
+    
+    // 리비전 분석
+    isVersionMatched: boolean("is_version_matched"),
+    versionLag: integer("version_lag"),
+    needsUpdate: boolean("needs_update"),
+    hasMultipleRevisions: boolean("has_multiple_revisions"),
+    
+    // 응답 파일 통계
+    totalResponseFiles: integer("total_response_files"),
+    latestResponseFileName: varchar("latest_response_file_name", { length: 255 }),
+    latestResponseFileSize: integer("latest_response_file_size"),
+    latestResponseUploadedAt: timestamp("latest_response_uploaded_at"),
+    
+    // 효과적인 상태 (UI 표시용)
+    effectiveStatus: varchar("effective_status", { length: 50 }),
+    
+}).as(sql`
+    SELECT 
+        var.id as response_id,
+        ba.rfq_id,
+        br.rfq_code,
+        var.rfq_type,
+        var.rfq_record_id,
+        
+        -- 첨부파일 정보
+        ba.id as attachment_id,
+        ba.attachment_type,
+        ba.serial_no,
+        ba.description as attachment_description,
+        
+        -- 벤더 정보
+        v.id as vendor_id,
+        v.vendor_code,
+        v.vendor_name,
+        v.country as vendor_country,
+        
+        -- 응답 상태
+        var.response_status,
+        var.current_revision,
+        var.responded_revision,
+        
+        -- 코멘트 (새로 추가된 필드 포함)
+        var.response_comment,
+        var.vendor_comment,
+        var.revision_request_comment,
+        
+        -- 날짜 (새로 추가된 필드 포함)
+        var.requested_at,
+        var.responded_at,
+        var.revision_requested_at,
+        
+        -- 발주처 최신 리비전
+        latest_rev.revision_no as latest_client_revision_no,
+        latest_rev.original_file_name as latest_client_file_name,
+        latest_rev.file_size as latest_client_file_size,
+        latest_rev.revision_comment as latest_client_revision_comment,
+        
+        -- 리비전 분석
+        CASE 
+            WHEN var.responded_revision = ba.current_revision THEN true 
+            ELSE false 
+        END as is_version_matched,
+        
+        CASE 
+            WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL
+            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
+                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
+        END as version_lag,
+        
+        CASE 
+            WHEN var.response_status = 'RESPONDED' 
+                 AND var.responded_revision != ba.current_revision THEN true 
+            ELSE false 
+        END as needs_update,
+        
+        CASE 
+            WHEN revision_count.total_revisions > 1 THEN true 
+            ELSE false 
+        END as has_multiple_revisions,
+        
+        -- 응답 파일 정보
+        COALESCE(file_stats.total_files, 0) as total_response_files,
+        file_stats.latest_file_name as latest_response_file_name,
+        file_stats.latest_file_size as latest_response_file_size,
+        file_stats.latest_uploaded_at as latest_response_uploaded_at,
+        
+        -- 효과적인 상태
+        CASE 
+            WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED'
+            WHEN var.response_status = 'WAIVED' THEN 'WAIVED'
+            WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED'
+            WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE'
+            WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH'
+            ELSE var.response_status
+        END as effective_status
+        
+    FROM vendor_attachment_responses var
+    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
+    JOIN b_rfqs br ON ba.rfq_id = br.id
+    LEFT JOIN vendors v ON var.vendor_id = v.id
+    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
+    LEFT JOIN (
+        SELECT 
+            attachment_id,
+            COUNT(*) as total_revisions
+        FROM b_rfq_attachment_revisions
+        GROUP BY attachment_id
+    ) revision_count ON ba.id = revision_count.attachment_id
+    LEFT JOIN (
+        SELECT 
+            vendor_response_id,
+            COUNT(*) as total_files,
+            MAX(original_file_name) as latest_file_name,
+            MAX(file_size) as latest_file_size,
+            MAX(uploaded_at) as latest_uploaded_at
+        FROM vendor_response_attachments_b
+        GROUP BY vendor_response_id
+    ) file_stats ON var.id = file_stats.vendor_response_id
+`);
+
+// 4. RFQ 진행 현황 요약 뷰 (리비전 정보 포함)
+export const rfqProgressSummaryView = pgView("rfq_progress_summary", {
+    rfqId: integer("rfq_id"),
+    rfqCode: varchar("rfq_code", { length: 50 }),
+    rfqStatus: varchar("rfq_status", { length: 30 }),
+    dueDate: date("due_date"),
+    daysToDeadline: integer("days_to_deadline"),
+    
+    // 첨부파일 통계
+    totalAttachments: integer("total_attachments"),
+    attachmentsWithMultipleRevisions: integer("attachments_with_multiple_revisions"),
+    totalClientRevisions: integer("total_client_revisions"),
+    
+    // 응답 통계 (INITIAL)
+    initialVendorCount: integer("initial_vendor_count"),
+    initialTotalResponses: integer("initial_total_responses"),
+    initialRespondedCount: integer("initial_responded_count"),
+    initialUpToDateCount: integer("initial_up_to_date_count"),
+    initialVersionMismatchCount: integer("initial_version_mismatch_count"),
+    initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }),
+    initialVersionMatchRate: numeric("initial_version_match_rate", { precision: 5, scale: 2 }),
+    
+    // 응답 통계 (FINAL)
+    finalVendorCount: integer("final_vendor_count"),
+    finalTotalResponses: integer("final_total_responses"),
+    finalRespondedCount: integer("final_responded_count"),
+    finalUpToDateCount: integer("final_up_to_date_count"),
+    finalVersionMismatchCount: integer("final_version_mismatch_count"),
+    finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }),
+    finalVersionMatchRate: numeric("final_version_match_rate", { precision: 5, scale: 2 }),
+    
+    // 전체 파일 통계
+    totalResponseFiles: integer("total_response_files"),
+    
+}).as(sql`
+    SELECT 
+        br.id as rfq_id,
+        br.rfq_code,
+        br.status as rfq_status,
+        br.due_date,
+        (br.due_date - CURRENT_DATE) as days_to_deadline,
+        
+        -- 첨부파일 통계
+        attachment_stats.total_attachments,
+        attachment_stats.attachments_with_multiple_revisions,
+        attachment_stats.total_client_revisions,
+        
+        -- Initial RFQ 통계
+        COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count,
+        COALESCE(initial_stats.total_responses, 0) as initial_total_responses,
+        COALESCE(initial_stats.responded_count, 0) as initial_responded_count,
+        COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count,
+        COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count,
+        COALESCE(initial_stats.response_rate, 0) as initial_response_rate,
+        COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate,
+        
+        -- Final RFQ 통계
+        COALESCE(final_stats.vendor_count, 0) as final_vendor_count,
+        COALESCE(final_stats.total_responses, 0) as final_total_responses,
+        COALESCE(final_stats.responded_count, 0) as final_responded_count,
+        COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count,
+        COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count,
+        COALESCE(final_stats.response_rate, 0) as final_response_rate,
+        COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate,
+        
+        COALESCE(file_stats.total_files, 0) as total_response_files
+        
+    FROM b_rfqs br
+    LEFT JOIN (
+        SELECT 
+            ba.rfq_id,
+            COUNT(*) as total_attachments,
+            COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions,
+            SUM(rev_count.total_revisions) as total_client_revisions
+        FROM b_rfq_attachments ba
+        LEFT JOIN (
+            SELECT 
+                attachment_id,
+                COUNT(*) as total_revisions
+            FROM b_rfq_attachment_revisions
+            GROUP BY attachment_id
+        ) rev_count ON ba.id = rev_count.attachment_id
+        GROUP BY ba.rfq_id
+    ) attachment_stats ON br.id = attachment_stats.rfq_id
+    
+    LEFT JOIN (
+        SELECT 
+            br.id as rfq_id,
+            COUNT(DISTINCT var.vendor_id) as vendor_count,
+            COUNT(*) as total_responses,
+            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
+            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
+            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
+            ROUND(
+                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
+                NULLIF(COUNT(*), 0), 2
+            ) as response_rate,
+            ROUND(
+                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
+                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
+            ) as version_match_rate
+        FROM b_rfqs br
+        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
+        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
+        WHERE var.rfq_type = 'INITIAL'
+        GROUP BY br.id
+    ) initial_stats ON br.id = initial_stats.rfq_id
+    
+    LEFT JOIN (
+        SELECT 
+            br.id as rfq_id,
+            COUNT(DISTINCT var.vendor_id) as vendor_count,
+            COUNT(*) as total_responses,
+            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
+            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
+            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
+            ROUND(
+                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
+                NULLIF(COUNT(*), 0), 2
+            ) as response_rate,
+            ROUND(
+                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
+                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
+            ) as version_match_rate
+        FROM b_rfqs br
+        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
+        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
+        WHERE var.rfq_type = 'FINAL'
+        GROUP BY br.id
+    ) final_stats ON br.id = final_stats.rfq_id
+    
+    LEFT JOIN (
+        SELECT 
+            br.id as rfq_id,
+            COUNT(vra.id) as total_files
+        FROM b_rfqs br
+        JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
+        JOIN vendor_attachment_responses var ON ba.id = var.attachment_id
+        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
+        GROUP BY br.id
+    ) file_stats ON br.id = file_stats.rfq_id
+`);
+
+// 타입 정의
+export type VendorResponseAttachmentEnhanced = typeof vendorResponseAttachmentsEnhanced.$inferSelect;
+export type AttachmentRevisionHistory = typeof attachmentRevisionHistoryView.$inferSelect;
+export type VendorResponseDetail = typeof vendorResponseDetailView.$inferSelect;
+export type RfqProgressSummary = typeof rfqProgressSummaryView.$inferSelect;
\ No newline at end of file