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

1 files changed, 1464 insertions, 94 deletions

diff --git a/lib/b-rfq/service.ts b/lib/b-rfq/service.ts
index 0dc61832..c5398e6c 100644
--- a/lib/b-rfq/service.ts
+++ b/lib/b-rfq/service.ts
@@ -1,16 +1,24 @@
 'use server'
 
-import { revalidateTag, unstable_cache } from "next/cache"
-import { count, desc, asc, and, or, gte, lte, ilike, eq, inArray, sql } from "drizzle-orm"
+import { revalidateTag, unstable_cache ,unstable_noStore} from "next/cache"
+import {count, desc, asc, and, or, gte, lte, ilike, eq, inArray, sql } from "drizzle-orm"
 import { filterColumns } from "@/lib/filter-columns"
 import db from "@/db/db"
-import { Incoterm, RfqDashboardView, Vendor, bRfqAttachmentRevisions, bRfqs, bRfqsAttachments, incoterms, initialRfq, initialRfqDetailView, projects, users, vendorAttachmentResponses, vendors } from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정
+import {   vendorResponseDetailView, 
+  attachmentRevisionHistoryView,
+  rfqProgressSummaryView,
+  vendorResponseAttachmentsEnhanced ,Incoterm, RfqDashboardView, Vendor, VendorAttachmentResponse, bRfqAttachmentRevisions, bRfqs, bRfqsAttachments, incoterms, initialRfq, initialRfqDetailView, projects, users, vendorAttachmentResponses, vendors, 
+  vendorResponseAttachmentsB} from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정
 import { rfqDashboardView } from "@/db/schema" // 뷰 import
 import type { SQL } from "drizzle-orm"
-import { AttachmentRecord, CreateRfqInput, DeleteAttachmentsInput, GetInitialRfqDetailSchema, GetRFQDashboardSchema, GetRfqAttachmentsSchema, attachmentRecordSchema, createRfqServerSchema, deleteAttachmentsSchema } from "./validations"
+import { AttachmentRecord, BulkEmailInput, CreateRfqInput, DeleteAttachmentsInput, GetInitialRfqDetailSchema, GetRFQDashboardSchema, GetRfqAttachmentsSchema, GetVendorResponsesSchema, RemoveInitialRfqsSchema, RequestRevisionResult, ResponseStatus, UpdateInitialRfqSchema, VendorRfqResponseSummary, attachmentRecordSchema, bulkEmailSchema, createRfqServerSchema, deleteAttachmentsSchema, removeInitialRfqsSchema, requestRevisionSchema, updateInitialRfqSchema } from "./validations"
 import { getServerSession } from "next-auth/next"
 import { authOptions } from "@/app/api/auth/[...nextauth]/route"
 import { unlink } from "fs/promises"
+import { getErrorMessage } from "../handle-error"
+import { AddInitialRfqFormData } from "./initial/add-initial-rfq-dialog"
+import { sendEmail } from "../mail/sendEmail"
+import { RfqType } from "../rfqs/validations"
 
 const tag = {
   initialRfqDetail:"initial-rfq",
@@ -25,8 +33,7 @@ const tag = {
 } as const;
 
 export async function getRFQDashboard(input: GetRFQDashboardSchema) {
-  return unstable_cache(
-    async () => {
+
       try {
         const offset = (input.page - 1) * input.perPage;
 
@@ -140,11 +147,7 @@ export async function getRFQDashboard(input: GetRFQDashboardSchema) {
         console.error("Error in getRFQDashboard:", err);
         return { data: [], pageCount: 0, total: 0 };
       }
-    },
-    [JSON.stringify(input)],
-    { revalidate: 3600, tags: [tag.rfqDashboard] },
-
-  )();
+   
 }
 
 // 헬퍼 함수들
@@ -192,6 +195,7 @@ async function generateNextSerial(picCode: string): Promise<string> {
     return "00001" // 기본값
   }
 }
+
 export async function createRfqAction(input: CreateRfqInput) {
   try {
     // 입력 데이터 검증
@@ -308,8 +312,6 @@ export async function getRfqAttachments(
   input: GetRfqAttachmentsSchema,
   rfqId: number
 ) {
-  return unstable_cache(
-    async () => {
       try {
         const offset = (input.page - 1) * input.perPage
 
@@ -445,10 +447,7 @@ export async function getRfqAttachments(
         console.error("getRfqAttachments error:", err)
         return { data: [], pageCount: 0 }
       }
-    },
-    [JSON.stringify(input), `${rfqId}`],
-    { revalidate: 300, tags: [tag.rfqAttachments(rfqId)] },
-  )()
+  
 }
 
 // 첨부파일별 벤더 응답 통계 조회
@@ -495,51 +494,79 @@ export async function getVendorResponsesForAttachment(
   attachmentId: number,
   rfqType: 'INITIAL' | 'FINAL' = 'INITIAL'
 ) {
-  return unstable_cache(
-    async () => {
-      try {
-        const responses = await db
-          .select({
-            id: vendorAttachmentResponses.id,
-            attachmentId: vendorAttachmentResponses.attachmentId,
-            vendorId: vendorAttachmentResponses.vendorId,
-            vendorCode: vendors.vendorCode,
-            vendorName: vendors.vendorName,
-            vendorCountry: vendors.country,
-            rfqType: vendorAttachmentResponses.rfqType,
-            rfqRecordId: vendorAttachmentResponses.rfqRecordId,
-            responseStatus: vendorAttachmentResponses.responseStatus,
-            currentRevision: vendorAttachmentResponses.currentRevision,
-            respondedRevision: vendorAttachmentResponses.respondedRevision,
-            responseComment: vendorAttachmentResponses.responseComment,
-            vendorComment: vendorAttachmentResponses.vendorComment,
-            requestedAt: vendorAttachmentResponses.requestedAt,
-            respondedAt: vendorAttachmentResponses.respondedAt,
-            updatedAt: vendorAttachmentResponses.updatedAt,
-          })
-          .from(vendorAttachmentResponses)
-          .leftJoin(vendors, eq(vendorAttachmentResponses.vendorId, vendors.id))
-          .where(
-            and(
-              eq(vendorAttachmentResponses.attachmentId, attachmentId),
-              eq(vendorAttachmentResponses.rfqType, rfqType)
-            )
-          )
-          .orderBy(vendors.vendorName)
-
-        return responses
-      } catch (err) {
-        console.error("getVendorResponsesForAttachment error:", err)
-        return []
-      }
-    },
-    [`${attachmentId}`, rfqType],
-    { revalidate: 180, tags: [tag.vendorResponses(attachmentId, rfqType)] },
+  try {
+    // 1. 기본 벤더 응답 정보 가져오기
+    const responses = await db
+      .select({
+        id: vendorAttachmentResponses.id,
+        attachmentId: vendorAttachmentResponses.attachmentId,
+        vendorId: vendorAttachmentResponses.vendorId,
+        vendorCode: vendors.vendorCode,
+        vendorName: vendors.vendorName,
+        vendorCountry: vendors.country,
+        rfqType: vendorAttachmentResponses.rfqType,
+        rfqRecordId: vendorAttachmentResponses.rfqRecordId,
+        responseStatus: vendorAttachmentResponses.responseStatus,
+        currentRevision: vendorAttachmentResponses.currentRevision,
+        respondedRevision: vendorAttachmentResponses.respondedRevision,
+        responseComment: vendorAttachmentResponses.responseComment,
+        vendorComment: vendorAttachmentResponses.vendorComment,
+        // 새로 추가된 필드들
+        revisionRequestComment: vendorAttachmentResponses.revisionRequestComment,
+        revisionRequestedAt: vendorAttachmentResponses.revisionRequestedAt,
+        requestedAt: vendorAttachmentResponses.requestedAt,
+        respondedAt: vendorAttachmentResponses.respondedAt,
+        updatedAt: vendorAttachmentResponses.updatedAt,
+      })
+      .from(vendorAttachmentResponses)
+      .leftJoin(vendors, eq(vendorAttachmentResponses.vendorId, vendors.id))
+      .where(
+        and(
+          eq(vendorAttachmentResponses.attachmentId, attachmentId),
+          eq(vendorAttachmentResponses.rfqType, rfqType)
+        )
+      )
+      .orderBy(vendors.vendorName);
+
+    // 2. 각 응답에 대한 파일 정보 가져오기
+    const responseIds = responses.map(r => r.id);
+        
+    let responseFiles: any[] = [];
+    if (responseIds.length > 0) {
+      responseFiles = await db
+        .select({
+          id: vendorResponseAttachmentsB.id,
+          vendorResponseId: vendorResponseAttachmentsB.vendorResponseId,
+          fileName: vendorResponseAttachmentsB.fileName,
+          originalFileName: vendorResponseAttachmentsB.originalFileName,
+          filePath: vendorResponseAttachmentsB.filePath,
+          fileSize: vendorResponseAttachmentsB.fileSize,
+          fileType: vendorResponseAttachmentsB.fileType,
+          description: vendorResponseAttachmentsB.description,
+          uploadedAt: vendorResponseAttachmentsB.uploadedAt,
+        })
+        .from(vendorResponseAttachmentsB)
+        .where(inArray(vendorResponseAttachmentsB.vendorResponseId, responseIds))
+        .orderBy(desc(vendorResponseAttachmentsB.uploadedAt));
+    }
 
-  )()
+    // 3. 응답에 파일 정보 병합
+    const enhancedResponses = responses.map(response => ({
+      ...response,
+      files: responseFiles.filter(file => file.vendorResponseId === response.id),
+      totalFiles: responseFiles.filter(file => file.vendorResponseId === response.id).length,
+      latestFile: responseFiles
+        .filter(file => file.vendorResponseId === response.id)
+        .sort((a, b) => new Date(b.uploadedAt).getTime() - new Date(a.uploadedAt).getTime())[0] || null,
+    }));
+
+    return enhancedResponses;
+  } catch (err) {
+    console.error("getVendorResponsesForAttachment error:", err);
+    return [];
+  }
 }
 
-
 export async function confirmDocuments(rfqId: number) {
   try {
     const session = await getServerSession(authOptions)
@@ -650,10 +677,7 @@ export async function requestTbe(rfqId: number, attachmentIds?: number[]) {
       }
     })
 
-    // 캐시 무효화
-    revalidateTag(`rfq-${rfqId}`)
-    revalidateTag(`rfq-attachments-${rfqId}`)
-
+  
     const attachmentCount = targetAttachments.length
     const attachmentList = targetAttachments
       .map(a => `${a.serialNo} (${a.currentRevision})`)
@@ -738,7 +762,7 @@ export async function addRfqAttachmentRecord(record: AttachmentRecord) {
           filePath: validatedRecord.filePath,
           fileSize: validatedRecord.fileSize,
           fileType: validatedRecord.fileType,
-          revisionComment: validatedRecord.revisionComment || "초기 업로드",
+          revisionComment: validatedRecord.revisionComment,
           isLatest: true,
           createdBy: userId,
         })
@@ -756,11 +780,7 @@ export async function addRfqAttachmentRecord(record: AttachmentRecord) {
       return { attachment, revision }
     })
 
-    revalidateTag(tag.rfq(validatedRecord.rfqId));
-    revalidateTag(tag.rfqDashboard);
-    revalidateTag(tag.rfqAttachments(validatedRecord.rfqId));
-
-    return {
+     return {
       success: true,
       message: `파일이 성공적으로 등록되었습니다. (시리얼: ${result.attachment.serialNo}, 리비전: Rev.0)`,
       attachment: result.attachment,
@@ -868,13 +888,7 @@ export async function addRevisionToAttachment(
       return inserted;
     });
 
-    // ────────────────────────────────────────────────────────────────────────────
-    // 6. 캐시 무효화 (rfqId 기준으로 수정)
-    // ────────────────────────────────────────────────────────────────────────────
-    revalidateTag(tag.rfq(rfqId));
-    revalidateTag(tag.rfqDashboard);
-    revalidateTag(tag.rfqAttachments(rfqId));
-    revalidateTag(tag.attachmentRevisions(attachmentId));
+   
 
     return {
       success: true,
@@ -892,8 +906,7 @@ export async function addRevisionToAttachment(
 
 // 특정 첨부파일의 모든 리비전 조회
 export async function getAttachmentRevisions(attachmentId: number) {
-  return unstable_cache(
-    async () => {
+
       try {
         const revisions = await db
           .select({
@@ -927,11 +940,6 @@ export async function getAttachmentRevisions(attachmentId: number) {
           revisions: [],
         }
       }
-    },
-    [`${attachmentId}`],
-    { revalidate: 180, tags: [tag.attachmentRevisions(attachmentId)] },
-
-  )()
 }
 
 
@@ -999,11 +1007,7 @@ export async function deleteRfqAttachments(input: DeleteAttachmentsInput) {
       }
     })
 
-    // 캐시 무효화
-    result.rfqIds.forEach(rfqId => {
-      revalidateTag(`rfq-attachments-${rfqId}`)
-    })
-
+  
     return {
       success: true,
       message: `${result.deletedCount}개의 첨부파일이 삭제되었습니다.`,
@@ -1025,8 +1029,7 @@ export async function deleteRfqAttachments(input: DeleteAttachmentsInput) {
 //Initial RFQ
 
 export async function getInitialRfqDetail(input: GetInitialRfqDetailSchema, rfqId?: number) {
-  return unstable_cache(
-    async () => {
+
       try {
         const offset = (input.page - 1) * input.perPage;
 
@@ -1112,6 +1115,7 @@ export async function getInitialRfqDetail(input: GetInitialRfqDetailSchema, rfqI
           return { data: [], pageCount: 0, total: 0 };
         }
 
+        console.log(totalResult);
         console.log(total);
 
         // 7) 정렬 및 페이징 처리된 데이터 조회
@@ -1139,10 +1143,6 @@ export async function getInitialRfqDetail(input: GetInitialRfqDetailSchema, rfqI
         console.error("Error in getInitialRfqDetail:", err);
         return { data: [], pageCount: 0, total: 0 };
       }
-    },
-    [JSON.stringify(input)],
-    { revalidate: 3600, tags: [tag.initialRfqDetail] },
-  )();
 }
 
 export async function getVendorsForSelection() {
@@ -1152,6 +1152,7 @@ export async function getVendorsForSelection() {
         id: vendors.id,
         vendorName: vendors.vendorName,
         vendorCode: vendors.vendorCode,
+        taxId: vendors.taxId,
         country: vendors.country,
         status: vendors.status,
       })
@@ -1179,6 +1180,8 @@ export async function getVendorsForSelection() {
 
 export async function addInitialRfqRecord(data: AddInitialRfqFormData & { rfqId: number }) {
   try {
+    console.log('Incoming data:', data);
+    
     const [newRecord] = await db
       .insert(initialRfq)
       .values({
@@ -1231,4 +1234,1371 @@ export async function getIncotermsForSelection() {
     console.error("Error fetching incoterms:", error)
     throw new Error("Failed to fetch incoterms")
   }
+}
+
+export async function removeInitialRfqs(input: RemoveInitialRfqsSchema) {
+  unstable_noStore ()
+  try {
+    const { ids } = removeInitialRfqsSchema.parse(input)
+
+    await db.transaction(async (tx) => {
+      await tx.delete(initialRfq).where(inArray(initialRfq.id, ids))
+    })
+
+    revalidateTag(tag.initialRfqDetail)
+
+    return {
+      data: null,
+      error: null,
+    }
+  } catch (err) {
+    return {
+      data: null,
+      error: getErrorMessage(err),
+    }
+  }
+}
+
+interface ModifyInitialRfqInput extends UpdateInitialRfqSchema {
+  id: number
+}
+
+export async function modifyInitialRfq(input: ModifyInitialRfqInput) {
+  unstable_noStore ()
+  try {
+    const { id, ...updateData } = input
+    
+    // validation
+    updateInitialRfqSchema.parse(updateData)
+
+    await db.transaction(async (tx) => {
+      const existingRfq = await tx
+        .select()
+        .from(initialRfq)
+        .where(eq(initialRfq.id, id))
+        .limit(1)
+
+      if (existingRfq.length === 0) {
+        throw new Error("초기 RFQ를 찾을 수 없습니다.")
+      }
+
+      await tx
+        .update(initialRfq)
+        .set({
+          ...updateData,
+          // Convert empty strings to null for optional fields
+          incotermsCode: updateData.incotermsCode || null,
+          gtc: updateData.gtc || null,
+          gtcValidDate: updateData.gtcValidDate || null,
+          classification: updateData.classification || null,
+          sparepart: updateData.sparepart || null,
+          validDate: updateData.validDate || null,
+          updatedAt: new Date(),
+        })
+        .where(eq(initialRfq.id, id))
+    })
+
+    revalidateTag(tag.initialRfqDetail)
+
+    return {
+      data: null,
+      error: null,
+    }
+  } catch (err) {
+    return {
+      data: null,
+      error: getErrorMessage(err),
+    }
+  }
+}
+
+
+
+
+// 이메일 발송용 데이터 타입
+interface EmailData {
+  rfqCode: string
+  projectName: string
+  projectCompany: string
+  projectFlag: string
+  projectSite: string
+  classification: string
+  incotermsCode: string
+  incotermsDescription: string
+  dueDate: string
+  validDate: string
+  sparepart: string
+  vendorName: string
+  picName: string
+  picEmail: string
+  warrantyPeriod: string
+  packageName: string
+  rfqRevision: number
+  emailType: string
+}
+
+export async function sendBulkInitialRfqEmails(input: BulkEmailInput) {
+  unstable_noStore()
+  try {
+
+    const session = await getServerSession(authOptions)
+    if (!session?.user?.id) {
+      throw new Error("인증이 필요합니다.")
+    }
+
+    const { initialRfqIds, language } = bulkEmailSchema.parse(input)
+
+    // 1. 선택된 초기 RFQ들의 상세 정보 조회
+    const initialRfqDetails = await db
+      .select({
+        // initialRfqDetailView 필드들을 명시적으로 선택
+        rfqId: initialRfqDetailView.rfqId,
+        rfqCode: initialRfqDetailView.rfqCode,
+        rfqStatus: initialRfqDetailView.rfqStatus,
+        initialRfqId: initialRfqDetailView.initialRfqId,
+        initialRfqStatus: initialRfqDetailView.initialRfqStatus,
+        vendorId: initialRfqDetailView.vendorId,
+        vendorCode: initialRfqDetailView.vendorCode,
+        vendorName: initialRfqDetailView.vendorName,
+        vendorCategory: initialRfqDetailView.vendorCategory,
+        vendorCountry: initialRfqDetailView.vendorCountry,
+        vendorBusinessSize: initialRfqDetailView.vendorBusinessSize,
+        dueDate: initialRfqDetailView.dueDate,
+        validDate: initialRfqDetailView.validDate,
+        incotermsCode: initialRfqDetailView.incotermsCode,
+        incotermsDescription: initialRfqDetailView.incotermsDescription,
+        shortList: initialRfqDetailView.shortList,
+        returnYn: initialRfqDetailView.returnYn,
+        cpRequestYn: initialRfqDetailView.cpRequestYn,
+        prjectGtcYn: initialRfqDetailView.prjectGtcYn,
+        returnRevision: initialRfqDetailView.returnRevision,
+        rfqRevision: initialRfqDetailView.rfqRevision,
+        gtc: initialRfqDetailView.gtc,
+        gtcValidDate: initialRfqDetailView.gtcValidDate,
+        classification: initialRfqDetailView.classification,
+        sparepart: initialRfqDetailView.sparepart,
+        createdAt: initialRfqDetailView.createdAt,
+        updatedAt: initialRfqDetailView.updatedAt,
+        // bRfqs에서 추가로 필요한 필드들
+        picName: bRfqs.picName,
+        picCode: bRfqs.picCode,
+        packageName: bRfqs.packageName,
+        packageNo: bRfqs.packageNo,
+        projectCompany: bRfqs.projectCompany,
+        projectFlag: bRfqs.projectFlag,
+        projectSite: bRfqs.projectSite,
+      })
+      .from(initialRfqDetailView)
+      .leftJoin(bRfqs, eq(initialRfqDetailView.rfqId, bRfqs.id))
+      .where(inArray(initialRfqDetailView.initialRfqId, initialRfqIds))
+
+    if (initialRfqDetails.length === 0) {
+      return {
+        success: false,
+        message: "선택된 초기 RFQ를 찾을 수 없습니다.",
+      }
+    }
+
+    // 2. 각 RFQ에 대한 첨부파일 조회
+    const rfqIds = [...new Set(initialRfqDetails.map(rfq => rfq.rfqId))].filter((id): id is number => id !== null)
+    const attachments = await db
+      .select()
+      .from(bRfqsAttachments)
+      .where(inArray(bRfqsAttachments.rfqId, rfqIds))
+
+    // 3. 벤더 이메일 정보 조회 (모든 이메일 주소 포함)
+    const vendorIds = [...new Set(initialRfqDetails.map(rfq => rfq.vendorId))].filter((id): id is number => id !== null)
+    const vendorsWithAllEmails = await db
+      .select({
+        id: vendors.id,
+        vendorName: vendors.vendorName,
+        email: vendors.email,
+        representativeEmail: vendors.representativeEmail,
+        // 연락처 이메일들을 JSON 배열로 집계
+        contactEmails: sql<string[]>`
+          COALESCE(
+            (SELECT json_agg(contact_email)
+             FROM vendor_contacts 
+             WHERE vendor_id = ${vendors.id} 
+             AND contact_email IS NOT NULL
+             AND contact_email != ''
+            ), 
+            '[]'::json
+          )
+        `.as("contact_emails")
+      })
+      .from(vendors)
+      .where(inArray(vendors.id, vendorIds))
+
+    // 각 벤더의 모든 유효한 이메일 주소를 정리하는 함수
+    function getAllVendorEmails(vendor: typeof vendorsWithAllEmails[0]): string[] {
+      const emails: string[] = []
+      
+      // 벤더 기본 이메일
+      if (vendor.email) {
+        emails.push(vendor.email)
+      }
+      
+      // 대표자 이메일
+      if (vendor.representativeEmail && vendor.representativeEmail !== vendor.email) {
+        emails.push(vendor.representativeEmail)
+      }
+      
+      // 연락처 이메일들
+      if (vendor.contactEmails && Array.isArray(vendor.contactEmails)) {
+        vendor.contactEmails.forEach(contactEmail => {
+          if (contactEmail && !emails.includes(contactEmail)) {
+            emails.push(contactEmail)
+          }
+        })
+      }
+      
+      return emails.filter(email => email && email.trim() !== '')
+    }
+
+    const results = []
+    const errors = []
+
+    // 4. 각 초기 RFQ에 대해 처리
+    for (const rfqDetail of initialRfqDetails) {
+      try {
+        // vendorId null 체크
+        if (!rfqDetail.vendorId) {
+          errors.push(`벤더 ID가 없습니다: RFQ ID ${rfqDetail.initialRfqId}`)
+          continue
+        }
+
+        // 해당 RFQ의 첨부파일들
+        const rfqAttachments = attachments.filter(att => att.rfqId === rfqDetail.rfqId)
+        
+        // 벤더 정보
+        const vendor = vendorsWithAllEmails.find(v => v.id === rfqDetail.vendorId)
+        if (!vendor) {
+          errors.push(`벤더 정보를 찾을 수 없습니다: RFQ ID ${rfqDetail.initialRfqId}`)
+          continue
+        }
+
+        // 해당 벤더의 모든 이메일 주소 수집
+        const vendorEmails = getAllVendorEmails(vendor)
+        
+        if (vendorEmails.length === 0) {
+          errors.push(`벤더 이메일 주소가 없습니다: ${vendor.vendorName}`)
+          continue
+        }
+
+        // 5. 기존 vendorAttachmentResponses 조회하여 리비전 상태 확인
+        const currentRfqRevision = rfqDetail.rfqRevision || 0
+        let emailType: "NEW" | "RESEND" | "REVISION" = "NEW"
+        let revisionToUse = currentRfqRevision
+
+        // 첫 번째 첨부파일을 기준으로 기존 응답 조회 (리비전 상태 확인용)
+        if (rfqAttachments.length > 0 &&  rfqDetail.initialRfqId) {
+          const existingResponses = await db
+            .select()
+            .from(vendorAttachmentResponses)
+            .where(
+              and(
+                eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId),
+                eq(vendorAttachmentResponses.rfqType, "INITIAL"),
+                eq(vendorAttachmentResponses.rfqRecordId, rfqDetail.initialRfqId)
+              )
+            )
+
+          if (existingResponses.length > 0) {
+            // 기존 응답이 있음
+            const existingRevision = parseInt(existingResponses[0].currentRevision?.replace("Rev.", "") || "0")
+            
+            if (currentRfqRevision > existingRevision) {
+              // RFQ 리비전이 올라감 → 리비전 업데이트
+              emailType = "REVISION"
+              revisionToUse = currentRfqRevision
+            } else {
+              // 동일하거나 낮음 → 재전송
+              emailType = "RESEND"
+              revisionToUse = existingRevision
+            }
+          } else {
+            // 기존 응답이 없음 → 신규 전송
+            emailType = "NEW"
+            revisionToUse = currentRfqRevision
+          }
+        }
+
+        // 6. vendorAttachmentResponses 레코드 생성/업데이트
+        for (const attachment of rfqAttachments) {
+          const existingResponse = await db
+            .select()
+            .from(vendorAttachmentResponses)
+            .where(
+              and(
+                eq(vendorAttachmentResponses.attachmentId, attachment.id),
+                eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId),
+                eq(vendorAttachmentResponses.rfqType, "INITIAL")
+              )
+            )
+            .limit(1)
+
+          if (existingResponse.length === 0) {
+            // 새 응답 레코드 생성
+            await db.insert(vendorAttachmentResponses).values({
+              attachmentId: attachment.id,
+              vendorId: rfqDetail.vendorId,
+              rfqType: "INITIAL",
+              rfqRecordId: rfqDetail.initialRfqId,
+              responseStatus: "NOT_RESPONDED",
+              currentRevision: `Rev.${revisionToUse}`,
+              requestedAt: new Date(),
+            })
+          } else {
+            // 기존 레코드 업데이트
+            await db
+              .update(vendorAttachmentResponses)
+              .set({
+                currentRevision: `Rev.${revisionToUse}`,
+                requestedAt: new Date(),
+                // 리비전 업데이트인 경우 응답 상태 초기화
+                responseStatus: emailType === "REVISION" ? "NOT_RESPONDED" : existingResponse[0].responseStatus,
+              })
+              .where(eq(vendorAttachmentResponses.id, existingResponse[0].id))
+          }
+          
+        }
+
+        const formatDateSafely = (date: Date | string | null | undefined): string => {
+          if (!date) return ""
+          try {
+            // Date 객체로 변환하고 포맷팅
+            const dateObj = new Date(date)
+            // 유효한 날짜인지 확인
+            if (isNaN(dateObj.getTime())) return ""
+            
+            return dateObj.toLocaleDateString('en-US', { 
+              year: 'numeric', 
+              month: '2-digit', 
+              day: '2-digit' 
+            })
+          } catch (error) {
+            console.error("Date formatting error:", error)
+            return ""
+          }
+        }
+
+        // 7. 이메일 발송
+        const emailData: EmailData = {
+          name:vendor.vendorName,
+          rfqCode: rfqDetail.rfqCode || "",
+          projectName: rfqDetail.rfqCode || "", // 실제 프로젝트명이 있다면 사용
+          projectCompany: rfqDetail.projectCompany || "",
+          projectFlag: rfqDetail.projectFlag || "",
+          projectSite: rfqDetail.projectSite || "",
+          classification: rfqDetail.classification || "ABS",
+          incotermsCode: rfqDetail.incotermsCode || "FOB",
+          incotermsDescription: rfqDetail.incotermsDescription || "FOB Finland Port",
+          dueDate: rfqDetail.dueDate ? formatDateSafely(rfqDetail.dueDate) : "",
+          validDate: rfqDetail.validDate ?formatDateSafely(rfqDetail.validDate) : "",
+          sparepart: rfqDetail.sparepart || "One(1) year operational spare parts",
+          vendorName: vendor.vendorName,
+          picName: session.user.name || rfqDetail.picName || "Procurement Manager",
+          picEmail: session.user.email || "procurement@samsung.com",
+          warrantyPeriod: "Refer to commercial package attached",
+          packageName: rfqDetail.packageName || "",
+          rfqRevision: revisionToUse, // 리비전 정보 추가
+          emailType: emailType, // 이메일 타입 추가
+        }
+
+        // 이메일 제목 생성 (타입에 따라 다르게)
+        let emailSubject = ""
+        const revisionText = revisionToUse > 0 ? ` Rev.${revisionToUse}` : ""
+        
+        switch (emailType) {
+          case "NEW":
+            emailSubject = `[SHI RFQ] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
+            break
+          case "RESEND":
+            emailSubject = `[SHI RFQ - RESEND] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
+            break
+          case "REVISION":
+            emailSubject = `[SHI RFQ - REVISED] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
+            break
+        }
+
+        // nodemailer로 모든 이메일 주소에 한번에 발송
+        await sendEmail({
+          // from: session.user.email || undefined,
+          to: vendorEmails.join(", "), // 콤마+공백으로 구분
+          subject: emailSubject,
+          template: "initial-rfq-invitation", // hbs 템플릿 파일명
+          context: {
+            ...emailData,
+            language,
+          }
+        })
+
+        // 8. 초기 RFQ 상태 업데이트 (리비전은 변경하지 않음 - 이미 DB에 저장된 값 사용)
+        if(rfqDetail.initialRfqId && rfqDetail.rfqId){
+          // Promise.all로 두 테이블 동시 업데이트
+          await Promise.all([
+            // initialRfq 테이블 업데이트
+            db
+              .update(initialRfq)
+              .set({
+                initialRfqStatus: "Init. RFQ Sent",
+                updatedAt: new Date(),
+              })
+              .where(eq(initialRfq.id, rfqDetail.initialRfqId)),
+            
+            // bRfqs 테이블 status도 함께 업데이트
+            db
+              .update(bRfqs)
+              .set({
+                status: "Init. RFQ Sent",
+                // updatedBy: session.user.id,
+                updatedAt: new Date(),
+              })
+              .where(eq(bRfqs.id, rfqDetail.rfqId))
+          ]);
+        }
+
+        results.push({
+          initialRfqId: rfqDetail.initialRfqId,
+          vendorName: vendor.vendorName,
+          vendorEmails: vendorEmails, // 발송된 모든 이메일 주소 기록
+          emailCount: vendorEmails.length,
+          emailType: emailType,
+          rfqRevision: revisionToUse,
+          success: true,
+        })
+
+      } catch (error) {
+        console.error(`Error processing RFQ ${rfqDetail.initialRfqId}:`, error)
+        errors.push(`RFQ ${rfqDetail.initialRfqId} 처리 중 오류: ${getErrorMessage(error)}`)
+      }
+    }
+
+    // 9. 페이지 새로고침
+    revalidateTag(tag.initialRfqDetail)
+    revalidateTag(tag.rfqDashboard) // 📋 RFQ 대시보드도 새로고침
+
+
+    return {
+      success: true,
+      message: `${results.length}개의 RFQ 이메일이 발송되었습니다.`,
+      results,
+      errors: errors.length > 0 ? errors : undefined,
+    }
+
+  } catch (err) {
+    console.error("Bulk email error:", err)
+    return {
+      success: false,
+      message: getErrorMessage(err),
+    }
+  }
+}
+
+// 개별 RFQ 이메일 재발송
+export async function resendInitialRfqEmail(initialRfqId: number) {
+  unstable_noStore()
+  try {
+    const result = await sendBulkInitialRfqEmails({
+      initialRfqIds: [initialRfqId],
+      language: "en",
+    })
+
+    return result
+  } catch (err) {
+    return {
+      success: false,
+      message: getErrorMessage(err),
+    }
+  }
+}
+
+export type VendorResponseDetail = VendorAttachmentResponse & {
+  attachment: {
+    id: number;
+    attachmentType: string;
+    serialNo: string;
+    description: string | null;
+    currentRevision: string;
+  };
+  vendor: {
+    id: number;
+    vendorCode: string;
+    vendorName: string;
+    country: string | null;
+    businessSize: string | null;
+  };
+  rfq: {
+    id: number;
+    rfqCode: string | null;
+    description: string | null;
+    status: string;
+    dueDate: Date;
+  };
+};
+
+export async function getVendorRfqResponses(input: GetVendorResponsesSchema, vendorId?: string, rfqId?: string) {
+      try {
+        // 페이지네이션 설정
+        const page = input.page || 1;
+        const perPage = input.perPage || 10;
+        const offset = (page - 1) * perPage;
+
+        // 기본 조건
+        let whereConditions = [];
+
+        // 벤더 ID 조건
+        if (vendorId) {
+          whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId)));
+        }
+
+        // RFQ 타입 조건
+        // if (input.rfqType !== "ALL") {
+        //   whereConditions.push(eq(vendorAttachmentResponses.rfqType, input.rfqType as RfqType));
+        // }
+
+        // 날짜 범위 조건
+        if (input.from && input.to) {
+          whereConditions.push(
+            and(
+              gte(vendorAttachmentResponses.requestedAt, new Date(input.from)),
+              lte(vendorAttachmentResponses.requestedAt, new Date(input.to))
+            )
+          );
+        }
+
+        const baseWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+
+        // 그룹핑된 응답 요약 데이터 조회
+        const groupedResponses = await db
+          .select({
+            vendorId: vendorAttachmentResponses.vendorId,
+            rfqRecordId: vendorAttachmentResponses.rfqRecordId,
+            rfqType: vendorAttachmentResponses.rfqType,
+            
+            // 통계 계산 (조건부 COUNT 수정)
+            totalAttachments: count(),
+            respondedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
+            pendingCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`,
+            revisionRequestedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`,
+            waivedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
+            
+            // 날짜 정보
+            requestedAt: sql<Date>`MIN(${vendorAttachmentResponses.requestedAt})`,
+            lastRespondedAt: sql<Date | null>`MAX(${vendorAttachmentResponses.respondedAt})`,
+            
+            // 코멘트 여부
+            hasComments: sql<boolean>`BOOL_OR(${vendorAttachmentResponses.responseComment} IS NOT NULL OR ${vendorAttachmentResponses.vendorComment} IS NOT NULL)`,
+          })
+          .from(vendorAttachmentResponses)
+          .where(baseWhere)
+          .groupBy(
+            vendorAttachmentResponses.vendorId,
+            vendorAttachmentResponses.rfqRecordId,
+            vendorAttachmentResponses.rfqType
+          )
+          .orderBy(desc(sql`MIN(${vendorAttachmentResponses.requestedAt})`))
+          .offset(offset)
+          .limit(perPage);
+
+        // 벤더 정보와 RFQ 정보를 별도로 조회
+        const vendorIds = [...new Set(groupedResponses.map(r => r.vendorId))];
+        const rfqRecordIds = [...new Set(groupedResponses.map(r => r.rfqRecordId))];
+
+        // 벤더 정보 조회
+        const vendorsData = await db.query.vendors.findMany({
+          where: or(...vendorIds.map(id => eq(vendors.id, id))),
+          columns: {
+            id: true,
+            vendorCode: true,
+            vendorName: true,
+            country: true,
+            businessSize: true,
+          }
+        });
+
+        // RFQ 정보 조회 (초기 RFQ와 최종 RFQ 모두)
+        const [initialRfqs] = await Promise.all([
+          db.query.initialRfq.findMany({
+            where: or(...rfqRecordIds.map(id => eq(initialRfq.id, id))),
+            with: {
+              rfq: {
+                columns: {
+                  id: true,
+                  rfqCode: true,
+                  description: true,
+                  status: true,
+                  dueDate: true,
+                }
+              }
+            }
+          })
+
+        ]);
+
+        // 데이터 조합 및 변환
+        const transformedResponses: VendorRfqResponseSummary[] = groupedResponses.map(response => {
+          const vendor = vendorsData.find(v => v.id === response.vendorId);
+          
+          let rfqInfo = null;
+          if (response.rfqType === "INITIAL") {
+            const initialRfq = initialRfqs.find(r => r.id === response.rfqRecordId);
+            rfqInfo = initialRfq?.rfq || null;
+          }
+
+          // 응답률 계산
+          const responseRate = Number(response.totalAttachments) > 0 
+            ? Math.round((Number(response.respondedCount) / Number(response.totalAttachments)) * 100)
+            : 0;
+
+          // 완료율 계산 (응답완료 + 포기)
+          const completionRate = Number(response.totalAttachments) > 0
+            ? Math.round(((Number(response.respondedCount) + Number(response.waivedCount)) / Number(response.totalAttachments)) * 100)
+            : 0;
+
+          // 전체 상태 결정
+          let overallStatus: ResponseStatus = "NOT_RESPONDED";
+          if (Number(response.revisionRequestedCount) > 0) {
+            overallStatus = "REVISION_REQUESTED";
+          } else if (completionRate === 100) {
+            overallStatus = Number(response.waivedCount) === Number(response.totalAttachments) ? "WAIVED" : "RESPONDED";
+          } else if (Number(response.respondedCount) > 0) {
+            overallStatus = "RESPONDED"; // 부분 응답
+          }
+
+          return {
+            id: `${response.vendorId}-${response.rfqRecordId}-${response.rfqType}`,
+            vendorId: response.vendorId,
+            rfqRecordId: response.rfqRecordId,
+            rfqType: response.rfqType,
+            rfq: rfqInfo,
+            vendor: vendor || null,
+            totalAttachments: Number(response.totalAttachments),
+            respondedCount: Number(response.respondedCount),
+            pendingCount: Number(response.pendingCount),
+            revisionRequestedCount: Number(response.revisionRequestedCount),
+            waivedCount: Number(response.waivedCount),
+            responseRate,
+            completionRate,
+            overallStatus,
+            requestedAt: response.requestedAt,
+            lastRespondedAt: response.lastRespondedAt,
+            hasComments: response.hasComments,
+          };
+        });
+
+        // 전체 개수 조회 (그룹핑 기준) - PostgreSQL 호환 방식
+        const totalCountResult = await db
+          .select({
+            totalCount: sql<number>`COUNT(DISTINCT (${vendorAttachmentResponses.vendorId}, ${vendorAttachmentResponses.rfqRecordId}, ${vendorAttachmentResponses.rfqType}))`
+          })
+          .from(vendorAttachmentResponses)
+          .where(baseWhere);
+
+        const totalCount = Number(totalCountResult[0].totalCount);
+        const pageCount = Math.ceil(totalCount / perPage);
+
+        return {
+          data: transformedResponses,
+          pageCount,
+          totalCount
+        };
+
+      } catch (err) {
+        console.error("getVendorRfqResponses 에러:", err);
+        return { data: [], pageCount: 0, totalCount: 0 };
+      }
+}
+/**
+ * 특정 RFQ의 첨부파일별 응답 상세 조회 (상세 페이지용)
+ */
+export async function getRfqAttachmentResponses(vendorId: string, rfqRecordId: string) {
+      try {
+        // 해당 RFQ의 모든 첨부파일 응답 조회
+        const responses = await db.query.vendorAttachmentResponses.findMany({
+          where: and(
+            eq(vendorAttachmentResponses.vendorId, Number(vendorId)),
+            eq(vendorAttachmentResponses.rfqRecordId, Number(rfqRecordId)),
+                 ),
+          with: {
+            attachment: {
+              with: {
+                rfq: {
+                  columns: {
+                    id: true,
+                    rfqCode: true,
+                    description: true,
+                    status: true,
+                    dueDate: true,
+                    // 추가 정보
+                    picCode: true,
+                    picName: true,
+                    EngPicName: true,
+                    packageNo: true,
+                    packageName: true,
+                    projectId: true,
+                    projectCompany: true,
+                    projectFlag: true,
+                    projectSite: true,
+                    remark: true,
+                  },
+                  with: {
+                    project: {
+                      columns: {
+                        id: true,
+                        code: true,
+                        name: true,
+                        type: true,
+                      }
+                    }
+                  }
+                }
+              }
+            },
+            vendor: {
+              columns: {
+                id: true,
+                vendorCode: true,
+                vendorName: true,
+                country: true,
+                businessSize: true,
+              }
+            },
+            responseAttachments: true,
+          },
+          orderBy: [asc(vendorAttachmentResponses.attachmentId)]
+        });
+
+        return {
+          data: responses,
+          rfqInfo: responses[0]?.attachment?.rfq || null,
+          vendorInfo: responses[0]?.vendor || null,
+        };
+
+      } catch (err) {
+        console.error("getRfqAttachmentResponses 에러:", err);
+        return { data: [], rfqInfo: null, vendorInfo: null };
+      }
+}
+
+export async function getVendorResponseStatusCounts(vendorId?: string, rfqId?: string, rfqType?: RfqType) {
+      try {
+        const initial: Record<ResponseStatus, number> = {
+          NOT_RESPONDED: 0,
+          RESPONDED: 0,
+          REVISION_REQUESTED: 0,
+          WAIVED: 0,
+        };
+
+        // 조건 설정
+        let whereConditions = [];
+
+        // 벤더 ID 조건
+        if (vendorId) {
+          whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId)));
+        }
+
+        // RFQ ID 조건
+        if (rfqId) {
+          const attachmentIds = await db
+            .select({ id: bRfqsAttachments.id })
+            .from(bRfqsAttachments)
+            .where(eq(bRfqsAttachments.rfqId, Number(rfqId)));
+          
+          if (attachmentIds.length > 0) {
+            whereConditions.push(
+              or(...attachmentIds.map(att => eq(vendorAttachmentResponses.attachmentId, att.id)))
+            );
+          }
+        }
+
+        // RFQ 타입 조건
+        if (rfqType) {
+          whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType));
+        }
+
+        const whereCondition = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+
+        // 상태별 그룹핑 쿼리
+        const rows = await db
+          .select({
+            status: vendorAttachmentResponses.responseStatus,
+            count: count(),
+          })
+          .from(vendorAttachmentResponses)
+          .where(whereCondition)
+          .groupBy(vendorAttachmentResponses.responseStatus);
+
+        // 결과 처리
+        const result = rows.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => {
+          if (status) {
+            acc[status as ResponseStatus] = Number(count);
+          }
+          return acc;
+        }, initial);
+
+        return result;
+      } catch (err) {
+        console.error("getVendorResponseStatusCounts 에러:", err);
+        return {} as Record<ResponseStatus, number>;
+      }
+}
+
+/**
+ * RFQ별 벤더 응답 요약 조회
+ */
+export async function getRfqResponseSummary(rfqId: string, rfqType?: RfqType) {
+
+      try {
+        // RFQ의 첨부파일 목록 조회 (relations 사용)
+        const attachments = await db.query.bRfqsAttachments.findMany({
+          where: eq(bRfqsAttachments.rfqId, Number(rfqId)),
+          columns: {
+            id: true,
+            attachmentType: true,
+            serialNo: true,
+            description: true,
+          }
+        });
+
+        if (attachments.length === 0) {
+          return {
+            totalAttachments: 0,
+            totalVendors: 0,
+            responseRate: 0,
+            completionRate: 0,
+            statusCounts: {} as Record<ResponseStatus, number>
+          };
+        }
+
+        // 조건 설정
+        let whereConditions = [
+          or(...attachments.map(att => eq(vendorAttachmentResponses.attachmentId, att.id)))
+        ];
+
+        if (rfqType) {
+          whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType));
+        }
+
+        const whereCondition = and(...whereConditions);
+
+        // 벤더 수 및 응답 통계 조회
+        const [vendorStats, statusCounts] = await Promise.all([
+          // 전체 벤더 수 및 응답 벤더 수 (조건부 COUNT 수정)
+          db
+            .select({
+              totalVendors: count(),
+              respondedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
+              completedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' OR ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
+            })
+            .from(vendorAttachmentResponses)
+            .where(whereCondition),
+
+          // 상태별 개수
+          db
+            .select({
+              status: vendorAttachmentResponses.responseStatus,
+              count: count(),
+            })
+            .from(vendorAttachmentResponses)
+            .where(whereCondition)
+            .groupBy(vendorAttachmentResponses.responseStatus)
+        ]);
+
+        const stats = vendorStats[0];
+        const statusCountsMap = statusCounts.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => {
+          if (status) {
+            acc[status as ResponseStatus] = Number(count);
+          }
+          return acc;
+        }, {
+          NOT_RESPONDED: 0,
+          RESPONDED: 0,
+          REVISION_REQUESTED: 0,
+          WAIVED: 0,
+        });
+
+        const responseRate = stats.totalVendors > 0 
+          ? Math.round((Number(stats.respondedVendors) / Number(stats.totalVendors)) * 100) 
+          : 0;
+        
+        const completionRate = stats.totalVendors > 0 
+          ? Math.round((Number(stats.completedVendors) / Number(stats.totalVendors)) * 100) 
+          : 0;
+
+        return {
+          totalAttachments: attachments.length,
+          totalVendors: Number(stats.totalVendors),
+          responseRate,
+          completionRate,
+          statusCounts: statusCountsMap
+        };
+
+      } catch (err) {
+        console.error("getRfqResponseSummary 에러:", err);
+        return {
+          totalAttachments: 0,
+          totalVendors: 0,
+          responseRate: 0,
+          completionRate: 0,
+          statusCounts: {} as Record<ResponseStatus, number>
+        };
+      }
+}
+
+/**
+ * 벤더별 응답 진행률 조회
+ */
+export async function getVendorResponseProgress(vendorId: string) {
+
+      try {
+        let whereConditions = [eq(vendorAttachmentResponses.vendorId, Number(vendorId))];
+
+         const whereCondition = and(...whereConditions);
+
+         const progress = await db
+         .select({
+           totalRequests: count(),
+           responded: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
+           pending: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`,
+           revisionRequested: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`,
+           waived: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
+         })
+         .from(vendorAttachmentResponses)
+         .where(whereCondition);
+          console.log(progress,"progress")
+
+        const stats = progress[0];
+        const responseRate = Number(stats.totalRequests) > 0 
+          ? Math.round((Number(stats.responded) / Number(stats.totalRequests)) * 100) 
+          : 0;
+
+        const completionRate = Number(stats.totalRequests) > 0 
+          ? Math.round(((Number(stats.responded) + Number(stats.waived)) / Number(stats.totalRequests)) * 100) 
+          : 0;
+
+        return {
+          totalRequests: Number(stats.totalRequests),
+          responded: Number(stats.responded),
+          pending: Number(stats.pending),
+          revisionRequested: Number(stats.revisionRequested),
+          waived: Number(stats.waived),
+          responseRate,
+          completionRate,
+        };
+
+      } catch (err) {
+        console.error("getVendorResponseProgress 에러:", err);
+        return {
+          totalRequests: 0,
+          responded: 0,
+          pending: 0,
+          revisionRequested: 0,
+          waived: 0,
+          responseRate: 0,
+          completionRate: 0,
+        };
+      }
+}
+
+
+export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, rfqRecordId: string) {
+  try {
+    // 1. 벤더 응답 상세 정보 조회 (뷰 사용)
+    const responses = await db
+      .select()
+      .from(vendorResponseDetailView)
+      .where(
+        and(
+          eq(vendorResponseDetailView.vendorId, Number(vendorId)),
+          eq(vendorResponseDetailView.rfqRecordId, Number(rfqRecordId))
+        )
+      )
+      .orderBy(asc(vendorResponseDetailView.attachmentId));
+
+    // 2. RFQ 진행 현황 요약 조회
+    const progressSummaryResult = await db
+      .select()
+      .from(rfqProgressSummaryView)
+      .where(eq(rfqProgressSummaryView.rfqId, responses[0]?.rfqId || 0))
+      .limit(1);
+    
+    const progressSummary = progressSummaryResult[0] || null;
+
+    // 3. 각 응답의 첨부파일 리비전 히스토리 조회
+    const attachmentHistories = await Promise.all(
+      responses.map(async (response) => {
+        const history = await db
+          .select()
+          .from(attachmentRevisionHistoryView)
+          .where(eq(attachmentRevisionHistoryView.attachmentId, response.attachmentId))
+          .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt));
+        
+        return {
+          attachmentId: response.attachmentId,
+          revisions: history
+        };
+      })
+    );
+
+    // 4. 벤더 응답 파일들 조회 (향상된 정보 포함)
+    const responseFiles = await Promise.all(
+      responses.map(async (response) => {
+        const files = await db
+          .select()
+          .from(vendorResponseAttachmentsEnhanced)
+          .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, response.responseId))
+          .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt));
+        
+        return {
+          responseId: response.responseId,
+          files: files
+        };
+      })
+    );
+
+    // 5. 데이터 변환 및 통합
+    const enhancedResponses = responses.map(response => {
+      const attachmentHistory = attachmentHistories.find(h => h.attachmentId === response.attachmentId);
+      const responseFileData = responseFiles.find(f => f.responseId === response.responseId);
+      
+      return {
+        ...response,
+        // 첨부파일 정보에 리비전 히스토리 추가
+        attachment: {
+          id: response.attachmentId,
+          attachmentType: response.attachmentType,
+          serialNo: response.serialNo,
+          description: response.attachmentDescription,
+          currentRevision: response.currentRevision,
+          // 모든 리비전 정보
+          revisions: attachmentHistory?.revisions?.map(rev => ({
+            id: rev.clientRevisionId,
+            revisionNo: rev.clientRevisionNo,
+            fileName: rev.clientFileName,
+            originalFileName: rev.clientFileName,
+            filePath: rev.clientFilePath, // 파일 경로 추가
+            fileSize: rev.clientFileSize,
+            revisionComment: rev.clientRevisionComment,
+            createdAt: rev.clientRevisionCreatedAt?.toISOString() || new Date().toISOString(),
+            isLatest: rev.isLatestClientRevision
+          })) || []
+        },
+        // 벤더 응답 파일들
+        responseAttachments: responseFileData?.files?.map(file => ({
+          id: file.responseAttachmentId,
+          fileName: file.fileName,
+          originalFileName: file.originalFileName,
+          filePath: file.filePath,
+          fileSize: file.fileSize,
+          description: file.description,
+          uploadedAt: file.uploadedAt?.toISOString() || new Date().toISOString(),
+          isLatestResponseFile: file.isLatestResponseFile,
+          fileSequence: file.fileSequence
+        })) || [],
+        // 리비전 분석 정보
+        isVersionMatched: response.isVersionMatched,
+        versionLag: response.versionLag,
+        needsUpdate: response.needsUpdate,
+        hasMultipleRevisions: response.hasMultipleRevisions,
+        
+        // 새로 추가된 필드들
+        revisionRequestComment: response.revisionRequestComment,
+        revisionRequestedAt: response.revisionRequestedAt?.toISOString() || null,
+      };
+    });
+
+    // RFQ 기본 정보 (첫 번째 응답에서 추출)
+    const rfqInfo = responses[0] ? {
+      id: responses[0].rfqId,
+      rfqCode: responses[0].rfqCode,
+      // 추가 정보는 기존 방식대로 별도 조회 필요
+      description: "",
+      dueDate: progressSummary?.dueDate || new Date(),
+      status: progressSummary?.rfqStatus || "DRAFT",
+      // ... 기타 필요한 정보들
+    } : null;
+
+    // 벤더 정보
+    const vendorInfo = responses[0] ? {
+      id: responses[0].vendorId,
+      vendorCode: responses[0].vendorCode,
+      vendorName: responses[0].vendorName,
+      country: responses[0].vendorCountry,
+    } : null;
+
+    // 통계 정보 계산
+    const calculateStats = (responses: typeof enhancedResponses) => {
+      const total = responses.length;
+      const responded = responses.filter(r => r.responseStatus === "RESPONDED").length;
+      const pending = responses.filter(r => r.responseStatus === "NOT_RESPONDED").length;
+      const revisionRequested = responses.filter(r => r.responseStatus === "REVISION_REQUESTED").length;
+      const waived = responses.filter(r => r.responseStatus === "WAIVED").length;
+      const versionMismatch = responses.filter(r => r.effectiveStatus === "VERSION_MISMATCH").length;
+      const upToDate = responses.filter(r => r.effectiveStatus === "UP_TO_DATE").length;
+
+      return {
+        total,
+        responded,
+        pending,
+        revisionRequested,
+        waived,
+        versionMismatch,
+        upToDate,
+        responseRate: total > 0 ? Math.round((responded / total) * 100) : 0,
+        completionRate: total > 0 ? Math.round(((responded + waived) / total) * 100) : 0,
+        versionMatchRate: responded > 0 ? Math.round((upToDate / responded) * 100) : 100
+      };
+    };
+
+    const statistics = calculateStats(enhancedResponses);
+
+    return {
+      data: enhancedResponses,
+      rfqInfo,
+      vendorInfo,
+      statistics,
+      progressSummary: progressSummary ? {
+        totalAttachments: progressSummary.totalAttachments,
+        attachmentsWithMultipleRevisions: progressSummary.attachmentsWithMultipleRevisions,
+        totalClientRevisions: progressSummary.totalClientRevisions,
+        totalResponseFiles: progressSummary.totalResponseFiles,
+        daysToDeadline: progressSummary.daysToDeadline
+      } : null
+    };
+
+  } catch (err) {
+    console.error("getRfqAttachmentResponsesWithRevisions 에러:", err);
+    return { 
+      data: [], 
+      rfqInfo: null, 
+      vendorInfo: null, 
+      statistics: {
+        total: 0,
+        responded: 0,
+        pending: 0,
+        revisionRequested: 0,
+        waived: 0,
+        versionMismatch: 0,
+        upToDate: 0,
+        responseRate: 0,
+        completionRate: 0,
+        versionMatchRate: 100
+      },
+      progressSummary: null
+    };
+  }
+}
+
+// 첨부파일 리비전 히스토리 조회
+export async function getAttachmentRevisionHistory(attachmentId: number) {
+
+      try {
+        const history = await db
+          .select()
+          .from(attachmentRevisionHistoryView)
+          .where(eq(attachmentRevisionHistoryView.attachmentId, attachmentId))
+          .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt));
+
+        return history;
+      } catch (err) {
+        console.error("getAttachmentRevisionHistory 에러:", err);
+        return [];
+      }
+  }
+
+// RFQ 전체 진행 현황 조회
+export async function getRfqProgressSummary(rfqId: number) {
+      try {
+        const summaryResult = await db
+          .select()
+          .from(rfqProgressSummaryView)
+          .where(eq(rfqProgressSummaryView.rfqId, rfqId))
+          .limit(1);
+
+        return summaryResult[0] || null;
+      } catch (err) {
+        console.error("getRfqProgressSummary 에러:", err);
+        return null;
+      }
+}
+
+// 벤더 응답 파일 상세 조회 (향상된 정보 포함)
+export async function getVendorResponseFiles(vendorResponseId: number) {
+      try {
+        const files = await db
+          .select()
+          .from(vendorResponseAttachmentsEnhanced)
+          .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, vendorResponseId))
+          .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt));
+
+        return files;
+      } catch (err) {
+        console.error("getVendorResponseFiles 에러:", err);
+        return [];
+      }
+  }
+
+
+// 타입 정의 확장
+export type EnhancedVendorResponse = {
+  // 기본 응답 정보
+  responseId: number;
+  rfqId: number;
+  rfqCode: string;
+  rfqType: "INITIAL" | "FINAL";
+  rfqRecordId: number;
+  
+  // 첨부파일 정보
+  attachmentId: number;
+  attachmentType: string;
+  serialNo: string;
+  attachmentDescription?: string;
+  
+  // 벤더 정보
+  vendorId: number;
+  vendorCode: string;
+  vendorName: string;
+  vendorCountry: string;
+  
+  // 응답 상태
+  responseStatus: "NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED";
+  currentRevision: string;
+  respondedRevision?: string;
+  effectiveStatus: string;
+  
+  // 코멘트 관련 필드들 (새로 추가된 필드 포함)
+  responseComment?: string;      // 벤더가 응답할 때 작성하는 코멘트
+  vendorComment?: string;        // 벤더 내부 메모
+  revisionRequestComment?: string; // 발주처가 수정 요청할 때 작성하는 사유 (새로 추가)
+  
+  // 날짜 관련 필드들 (새로 추가된 필드 포함)
+  requestedAt: string;
+  respondedAt?: string;
+  revisionRequestedAt?: string;  // 수정 요청 날짜 (새로 추가)
+  
+  // 발주처 최신 리비전 정보
+  latestClientRevisionNo?: string;
+  latestClientFileName?: string;
+  latestClientFileSize?: number;
+  latestClientRevisionComment?: string;
+  
+  // 리비전 분석
+  isVersionMatched: boolean;
+  versionLag?: number;
+  needsUpdate: boolean;
+  hasMultipleRevisions: boolean;
+  
+  // 응답 파일 통계
+  totalResponseFiles: number;
+  latestResponseFileName?: string;
+  latestResponseFileSize?: number;
+  latestResponseUploadedAt?: string;
+  
+  // 첨부파일 정보 (리비전 히스토리 포함)
+  attachment: {
+    id: number;
+    attachmentType: string;
+    serialNo: string;
+    description?: string;
+    currentRevision: string;
+    revisions: Array<{
+      id: number;
+      revisionNo: string;
+      fileName: string;
+      originalFileName: string;
+      filePath?: string;
+      fileSize?: number;
+      revisionComment?: string;
+      createdAt: string;
+      isLatest: boolean;
+    }>;
+  };
+  
+  // 벤더 응답 파일들
+  responseAttachments: Array<{
+    id: number;
+    fileName: string;
+    originalFileName: string;
+    filePath: string;
+    fileSize?: number;
+    description?: string;
+    uploadedAt: string;
+    isLatestResponseFile: boolean;
+    fileSequence: number;
+  }>;
+};
+
+
+export async function requestRevision(
+  responseId: number,
+  revisionReason: string
+): Promise<RequestRevisionResult> {
+  try {
+    // 입력값 검증
+    const validatedData = requestRevisionSchema.parse({
+      responseId,
+      revisionReason,
+    });
+
+    // 현재 응답 정보 조회
+    const existingResponse = await db
+      .select()
+      .from(vendorAttachmentResponses)
+      .where(eq(vendorAttachmentResponses.id, validatedData.responseId))
+      .limit(1);
+
+    if (existingResponse.length === 0) {
+      return {
+        success: false,
+        message: "해당 응답을 찾을 수 없습니다",
+        error: "NOT_FOUND",
+      };
+    }
+
+    const response = existingResponse[0];
+
+    // 응답 상태 확인 (이미 응답되었거나 포기된 상태에서만 수정 요청 가능)
+    if (response.responseStatus !== "RESPONDED") {
+      return {
+        success: false,
+        message: "응답된 상태의 항목에서만 수정을 요청할 수 있습니다",
+        error: "INVALID_STATUS",
+      };
+    }
+
+    // 응답 상태를 REVISION_REQUESTED로 업데이트
+    const updateResult = await db
+      .update(vendorAttachmentResponses)
+      .set({
+        responseStatus: "REVISION_REQUESTED",
+        revisionRequestComment: validatedData.revisionReason, // 새로운 필드에 저장
+        revisionRequestedAt: new Date(), // 수정 요청 시간 저장
+        updatedAt: new Date(),
+      })
+      .where(eq(vendorAttachmentResponses.id, validatedData.responseId))
+      .returning();
+
+    if (updateResult.length === 0) {
+      return {
+        success: false,
+        message: "수정 요청 업데이트에 실패했습니다",
+        error: "UPDATE_FAILED",
+      };
+    }
+
+    return {
+      success: true,
+      message: "수정 요청이 성공적으로 전송되었습니다",
+    };
+
+  } catch (error) {
+    console.error("Request revision server action error:", error);
+       return {
+      success: false,
+      message: "내부 서버 오류가 발생했습니다",
+      error: "INTERNAL_ERROR",
+    };
+  }
 }
\ No newline at end of file