(대표님, 최겸) 이메일 템플릿, 벤더데이터 변경사항 대응, 기술영업 변경요구사항 구현

10 files changed, 393 insertions, 151 deletions

diff --git a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-hull/page.tsx b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-hull/page.tsx
index ce7bac9a..97e53567 100644
--- a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-hull/page.tsx
+++ b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-hull/page.tsx
@@ -35,7 +35,7 @@ export default async function HullRfqPage(props: HullRfqPageProps) {
           <div>
             <div className="flex items-center gap-2">
               <h2 className="text-2xl font-bold tracking-tight">
-                기술영업-해양 Hull RFQ
+                기술영업-해양 Hull Budgetary RFQ
               </h2>
               <InformationButton pagePath="evcp/budgetary-tech-sales-hull" />
             </div>
diff --git a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-ship/page.tsx b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-ship/page.tsx
index b2132cac..779b9ac9 100644
--- a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-ship/page.tsx
+++ b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-ship/page.tsx
@@ -35,7 +35,7 @@ export default async function RfqPage(props: RfqPageProps) {
           <div>
             <div className="flex items-center gap-2">
               <h2 className="text-2xl font-bold tracking-tight">
-                기술영업-조선 RFQ
+                기술영업-조선 Budgetary RFQ
               </h2>
               <InformationButton pagePath="evcp/budgetary-tech-sales-ship" />
             </div>
diff --git a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-top/page.tsx b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-top/page.tsx
index 37b75d22..5c96c85d 100644
--- a/app/[lng]/evcp/(evcp)/budgetary-tech-sales-top/page.tsx
+++ b/app/[lng]/evcp/(evcp)/budgetary-tech-sales-top/page.tsx
@@ -35,7 +35,7 @@ export default async function HullRfqPage(props: HullRfqPageProps) {
           <div>
             <div className="flex items-center gap-2">
               <h2 className="text-2xl font-bold tracking-tight">
-                기술영업-해양 TOP RFQ
+                기술영업-해양 TOP Budgetary RFQ
               </h2>
               <InformationButton pagePath="evcp/budgetary-tech-sales-top" />
             </div>
diff --git a/app/[lng]/evcp/(evcp)/contact-possible-items/page.tsx b/app/[lng]/evcp/(evcp)/contact-possible-items/page.tsx
index 9fda681e..5bc36790 100644
--- a/app/[lng]/evcp/(evcp)/contact-possible-items/page.tsx
+++ b/app/[lng]/evcp/(evcp)/contact-possible-items/page.tsx
@@ -25,11 +25,11 @@ export default async function ContactPossibleItemsPage({
         <div className="flex items-center justify-between space-y-2">

           <div>

           <h2 className="text-2xl font-bold tracking-tight">

-            담당자별 아이템 관리

+            담당자별 자재 관리

           </h2>

-          <p className="text-muted-foreground">

-            기술영업 담당자별 가능 아이템을 관리합니다.

-          </p>

+          {/* <p className="text-muted-foreground">

+            기술영업 담당자별 자재를 관리합니다.

+          </p> */}

         </div>

       </div>

       </div>

diff --git a/app/[lng]/evcp/(evcp)/email-template/[slug]/page.tsx b/app/[lng]/evcp/(evcp)/email-template/[slug]/page.tsx
index 713c2b6d..2654489f 100644
--- a/app/[lng]/evcp/(evcp)/email-template/[slug]/page.tsx
+++ b/app/[lng]/evcp/(evcp)/email-template/[slug]/page.tsx
@@ -6,13 +6,14 @@ import { getTemplateAction } from "@/lib/email-template/service"
 import { TemplateEditor } from "@/lib/email-template/editor/template-editor"

 

 interface TemplateDetailPageProps {

-  params: {

+  params: Promise<{

     slug: string

-  }

+  }>

 }

 

 export async function generateMetadata({ params }: TemplateDetailPageProps): Promise<Metadata> {

-  const result = await getTemplateAction(params.slug)

+  const { slug } = await params

+  const result = await getTemplateAction(slug)

   

   if (!result.success || !result.data) {

     return {

@@ -27,7 +28,8 @@ export async function generateMetadata({ params }: TemplateDetailPageProps): Pro
 }

 

 export default async function TemplateDetailPage({ params }: TemplateDetailPageProps) {

-  const result = await getTemplateAction(params.slug)

+  const { slug } = await params

+  const result = await getTemplateAction(slug)

 

   if (!result.success || !result.data) {

     notFound()

@@ -36,7 +38,7 @@ export default async function TemplateDetailPage({ params }: TemplateDetailPageP
   return (

     <div className="flex flex-1 flex-col">

       <TemplateEditor

-        templateSlug={params.slug}

+        templateSlug={slug}

         initialTemplate={result.data}

       />

     </div>

diff --git a/app/[lng]/procurement/(procurement)/email-template/[name]/page.tsx b/app/[lng]/procurement/(procurement)/email-template/[name]/page.tsx
deleted file mode 100644
index cccc10fc..00000000
--- a/app/[lng]/procurement/(procurement)/email-template/[name]/page.tsx
+++ /dev/null
@@ -1,26 +0,0 @@
-import { getTemplateAction } from '@/lib/mail/service';

-import MailTemplateEditorClient from '@/components/mail/mail-template-editor-client';

-

-interface EditMailTemplatePageProps {

-  params: {

-    name: string;

-    lng: string;

-  };

-}

-

-export default async function EditMailTemplatePage({ params }: EditMailTemplatePageProps) {

-  const { name: templateName } = await params;

-  

-  // 서버에서 초기 템플릿 데이터 가져오기

-  const result = await getTemplateAction(templateName);

-  const initialTemplate = result.success ? result.data : null;

-

-  return (

-    <div className="container mx-auto p-6">

-      <MailTemplateEditorClient 

-        templateName={templateName}

-        initialTemplate={initialTemplate}

-      />

-    </div>

-  );

-}

diff --git a/app/[lng]/procurement/(procurement)/email-template/page.tsx b/app/[lng]/procurement/(procurement)/email-template/page.tsx
deleted file mode 100644
index 7c1156ee..00000000
--- a/app/[lng]/procurement/(procurement)/email-template/page.tsx
+++ /dev/null
@@ -1,19 +0,0 @@
-import { getTemplatesAction } from '@/lib/mail/service';

-import MailTemplatesClient from '@/components/mail/mail-templates-client';

-

-export default async function MailTemplatesPage() {

-  // 서버에서 초기 데이터 가져오기

-  const result = await getTemplatesAction();

-  const initialData = result.success ? result.data : [];

-

-  return (

-    <div className="container mx-auto p-6">

-      <div className="mb-8">

-        <h1 className="text-3xl font-bold text-gray-900 mb-2">메일 템플릿 관리</h1>

-        {/* <p className="text-gray-600">이메일 템플릿을 관리할 수 있습니다.</p> */}

-      </div>

-

-      <MailTemplatesClient initialData={initialData} />

-    </div>

-  );

-}

diff --git a/app/api/revision-upload-ship/route.ts b/app/api/revision-upload-ship/route.ts
index 549d15bd..38762e5d 100644
--- a/app/api/revision-upload-ship/route.ts
+++ b/app/api/revision-upload-ship/route.ts
@@ -1,8 +1,4 @@
 import { NextRequest, NextResponse } from "next/server"
-import { writeFile } from "fs/promises"
-import { join } from "path"
-import { v4 as uuidv4 } from "uuid"
-import path from "path"
 import { revalidateTag } from "next/cache"
 
 import db from "@/db/db"
@@ -14,6 +10,9 @@ import {
 } from "@/db/schema/vendorDocu"
 import { and, eq } from "drizzle-orm"
 
+/* 보안 강화된 파일 저장 유틸리티 */
+import { saveFile, SaveFileResult } from "@/lib/file-stroage"
+
 /* change log 유틸 */
 import {
   logRevisionChange,
@@ -42,13 +41,16 @@ export async function POST(request: NextRequest) {
     if (!attachmentFiles.length)
       return NextResponse.json({ error: "No files provided" }, { status: 400 })
 
+    // 기본 파일 크기 검증 (보안 함수에서도 검증하지만 조기 체크)
     const MAX = 50 * 1024 * 1024 // 50MB (다이얼로그 제한과 맞춤)
-    for (const f of attachmentFiles)
-      if (f.size > MAX)
+    for (const f of attachmentFiles) {
+      if (f.size > MAX) {
         return NextResponse.json(
           { error: `${f.name} exceeds 50MB limit` },
           { status: 400 }
         )
+      }
+    }
 
     /* ------- 계약 ID 확보 ------- */
     const [docInfo] = await db
@@ -93,7 +95,7 @@ export async function POST(request: NextRequest) {
 
     /* ------- 트랜잭션 ------- */
     const result = await db.transaction(async (tx) => {
-      /* Revision 생성 */
+      /* Revision 생성/업데이트 */
       const today = new Date().toISOString().slice(0, 10)
       
       // 동일한 revision이 이미 있는지 확인 (usage, usageType도 포함)
@@ -187,34 +189,49 @@ export async function POST(request: NextRequest) {
         )
       }
 
-      /* 첨부파일 처리 */
+      /* ------- 보안 강화된 첨부파일 처리 ------- */
       const uploadedFiles: any[] = []
-      const baseDir = join(process.cwd(), "public", "documents")
+      const securityFailures: string[] = []
 
       for (const file of attachmentFiles) {
-        const ext   = path.extname(file.name)
-        const fname = uuidv4() + ext
-        const dest  = join(baseDir, fname)
+        console.log(`🔐 보안 검증 시작: ${file.name}`)
+        
+        // 보안 강화된 파일 저장
+        const saveResult: SaveFileResult = await saveFile({
+          file,
+          directory: "documents", // 문서 전용 디렉토리
+          originalName: file.name,
+          userId: uploaderName || "anonymous", // 업로더 정보 로깅용
+        })
+
+        if (!saveResult.success) {
+          console.error(`❌ 파일 보안 검증 실패: ${file.name} - ${saveResult.error}`)
+          securityFailures.push(`${file.name}: ${saveResult.error}`)
+          continue // 실패한 파일은 건너뛰고 계속 진행
+        }
 
-        await writeFile(dest, Buffer.from(await file.arrayBuffer()))
+        console.log(`✅ 파일 보안 검증 통과: ${file.name}`)
+        console.log(`📁 저장된 경로: ${saveResult.publicPath}`)
 
+        // DB에 첨부파일 정보 저장
         const [att] = await tx.insert(documentAttachments)
           .values({
             revisionId,
-            fileName: file.name,
-            filePath: "/documents/" + fname,
-            fileSize: file.size,
-            fileType: ext.slice(1).toLowerCase() || undefined,
+            fileName: saveResult.originalName!, // 원본 파일명
+            filePath: saveResult.publicPath!,   // 웹 접근 경로
+            fileSize: saveResult.fileSize!,
+            fileType: saveResult.fileName!.split('.').pop()?.toLowerCase() || undefined,
             updatedAt: new Date(),
           })
           .returning()
 
         uploadedFiles.push({
           id: att.id,
-          fileName: file.name,
-          fileSize: file.size,
-          filePath: att.filePath,
-          fileType: ext.slice(1).toLowerCase() || null, // ✅ 추가
+          fileName: saveResult.originalName,
+          fileSize: saveResult.fileSize,
+          filePath: saveResult.publicPath,
+          fileType: saveResult.fileName!.split('.').pop()?.toLowerCase() || null,
+          securityChecks: saveResult.securityChecks, // 보안 검증 결과
         })
 
         // change_logs: attachment CREATE
@@ -230,6 +247,16 @@ export async function POST(request: NextRequest) {
         )
       }
 
+      // 보안 검증 실패한 파일이 있으면 경고 반환
+      if (securityFailures.length > 0) {
+        console.warn(`⚠️ 일부 파일의 보안 검증 실패:`, securityFailures)
+        
+        // 모든 파일이 실패한 경우 에러 반환
+        if (uploadedFiles.length === 0) {
+          throw new Error(`모든 파일의 보안 검증이 실패했습니다: ${securityFailures.join(', ')}`)
+        }
+      }
+
       /* documents.updatedAt 업데이트 */
       await tx.update(documents)
         .set({ updatedAt: new Date() })
@@ -237,38 +264,45 @@ export async function POST(request: NextRequest) {
 
       return { 
         revisionId, 
-        issueStageId, // ✅ 추가
+        issueStageId,
         stage, 
         revision, 
         uploadedFiles, 
         contractId: docInfo.contractId,
         usage,
-        usageType 
+        usageType,
+        securityFailures // 보안 실패 정보 포함
       }
     })
 
     // 캐시 무효화
     try {
       revalidateTag(`sync-status-${result.contractId}`)
-      
       console.log(`✅ Cache invalidated for contract ${result.contractId}`)
     } catch (cacheError) {
       console.warn('⚠️ Cache invalidation failed:', cacheError)
     }
 
+    // 응답 메시지 구성
+    let message = `리비전 ${result.revision}이 성공적으로 업로드되었습니다`
+    if (result.securityFailures.length > 0) {
+      message += ` (일부 파일 보안 검증 실패: ${result.securityFailures.length}개)`
+    }
+
     return NextResponse.json({
       success: true,
-      message: `리비전 ${result.revision}이 성공적으로 업로드되었습니다`,
+      message,
       data: {
         revisionId: result.revisionId,
-        issueStageId: issueStageId, // ✅ 추가
+        issueStageId: result.issueStageId,
         stage: result.stage,
         revision: result.revision,
         usage: result.usage,
         usageType: result.usageType,
-        uploaderName: uploaderName, // ✅ 추가
+        uploaderName: uploaderName,
         uploadedFiles: result.uploadedFiles,
-        filesCount: result.uploadedFiles.length
+        filesCount: result.uploadedFiles.length,
+        securityFailures: result.securityFailures, // 클라이언트에 보안 실패 정보 전달
       },
     })
   } catch (e) {
diff --git a/app/api/revision-upload/route.ts b/app/api/revision-upload/route.ts
index 1a9666a7..b171b89a 100644
--- a/app/api/revision-upload/route.ts
+++ b/app/api/revision-upload/route.ts
@@ -1,9 +1,5 @@
 import { NextRequest, NextResponse } from "next/server"
-import { writeFile } from "fs/promises"
-import { join } from "path"
-import { v4 as uuidv4 } from "uuid"
-import path from "path"
-import { revalidateTag } from "next/cache" // ✅ 추가
+import { revalidateTag } from "next/cache"
 
 import db from "@/db/db"
 import {
@@ -14,7 +10,10 @@ import {
 } from "@/db/schema/vendorDocu"
 import { and, eq } from "drizzle-orm"
 
-/* ① change log 유틸 */
+/* 보안 강화된 파일 저장 유틸리티 */
+import { saveFile, SaveFileResult } from "@/lib/file-stroage"
+
+/* change log 유틸 */
 import {
   logRevisionChange,
   logAttachmentChange,
@@ -29,8 +28,8 @@ export async function POST(request: NextRequest) {
     const revision         = formData.get("revision")     as string | null
     const docId            = Number(formData.get("documentId"))
     const uploaderName     = formData.get("uploaderName") as string | null
-    const usage          = formData.get("usage")      as string | null
-    const usageType          = formData.get("usageType")      as string | null
+    const usage            = formData.get("usage")        as string | null
+    const usageType        = formData.get("usageType")    as string | null
     const comment          = formData.get("comment")      as string | null
     const mode             = (formData.get("mode") || "new") as string // 'new'|'append'
     const targetSystem     = (formData.get("targetSystem") as string | null) ?? "DOLCE"
@@ -44,24 +43,31 @@ export async function POST(request: NextRequest) {
     if (!attachmentFiles.length)
       return NextResponse.json({ error: "No files provided" }, { status: 400 })
 
+    // 기본 파일 크기 검증 (보안 함수에서도 검증하지만 조기 체크)
     const MAX = 3 * 1024 * 1024 * 1024 // 3 GB
-    for (const f of attachmentFiles)
-      if (f.size > MAX)
+    for (const f of attachmentFiles) {
+      if (f.size > MAX) {
         return NextResponse.json(
           { error: `${f.name} > 3 GB` },
           { status: 400 }
         )
+      }
+    }
 
     /* ------- 계약 ID 확보 ------- */
-    const [{ contractId }] = await db
+    const [docInfo] = await db
       .select({ contractId: documents.contractId })
       .from(documents)
       .where(eq(documents.id, docId))
       .limit(1)
 
+    if (!docInfo) {
+      return NextResponse.json({ error: "Document not found" }, { status: 404 })
+    }
+
     /* ------- 트랜잭션 ------- */
     const result = await db.transaction(async (tx) => {
-      /* 1) Stage */
+      /* 1) Stage 생성/조회 */
       let issueStageId: number
       const [stageRow] = await tx
         .select({ id: issueStages.id })
@@ -77,9 +83,11 @@ export async function POST(request: NextRequest) {
           .values({ documentId: docId, stageName: stage, updatedAt: new Date() })
           .returning({ id: issueStages.id })
         issueStageId = s.id
-      } else issueStageId = stageRow.id
+      } else {
+        issueStageId = stageRow.id
+      }
 
-      /* 2) Revision */
+      /* 2) Revision 생성/업데이트 */
       const today = new Date().toISOString().slice(0, 10)
       let revisionId: number
       const [revRow] = await tx
@@ -93,23 +101,30 @@ export async function POST(request: NextRequest) {
 
       if (!revRow || mode === "new") {
         /* --- CREATE --- */
+        const revisionData: any = {
+          issueStageId,
+          revision,
+          uploaderType: "vendor",
+          uploaderName: uploaderName ?? undefined,
+          revisionStatus: "UPLOADED",
+          uploadedAt: today,
+          comment: comment ?? undefined,
+          updatedAt: new Date(),
+        }
+
+        // usage와 usageType이 있으면 추가
+        if (usage) revisionData.usage = usage
+        if (usageType) revisionData.usageType = usageType
+
         const [newRev] = await tx.insert(revisions)
-          .values({
-            issueStageId,
-            revision,
-            uploaderType: "vendor",
-            uploaderName: uploaderName ?? undefined,
-            revisionStatus: "UPLOADED",
-            uploadedAt: today,
-            comment: comment ?? undefined,
-            updatedAt: new Date(),
-          })
+          .values(revisionData)
           .returning()
+        
         revisionId = newRev.id
 
         // change_logs: CREATE
         await logRevisionChange(
-          contractId,
+          docInfo.contractId,
           revisionId,
           "CREATE",
           newRev,
@@ -120,12 +135,18 @@ export async function POST(request: NextRequest) {
         )
       } else {
         /* --- UPDATE --- */
+        const updateData: any = {
+          uploaderName: uploaderName ?? revRow.uploaderName,
+          comment: comment ?? revRow.comment,
+          updatedAt: new Date(),
+        }
+
+        // usage와 usageType이 있으면 업데이트
+        if (usage) updateData.usage = usage
+        if (usageType) updateData.usageType = usageType
+
         await tx.update(revisions)
-          .set({
-            uploaderName: uploaderName ?? revRow.uploaderName,
-            comment: comment ?? revRow.comment,
-            updatedAt: new Date(),
-          })
+          .set(updateData)
           .where(eq(revisions.id, revRow.id))
 
         const [updated] = await tx
@@ -136,7 +157,7 @@ export async function POST(request: NextRequest) {
         revisionId = revRow.id
 
         await logRevisionChange(
-          contractId,
+          docInfo.contractId,
           revisionId,
           "UPDATE",
           updated,
@@ -147,38 +168,54 @@ export async function POST(request: NextRequest) {
         )
       }
 
-      /* 3) Attachments */
+      /* ------- 보안 강화된 첨부파일 처리 ------- */
       const uploadedFiles: any[] = []
-      const baseDir = join(process.cwd(), "public", "documents")
+      const securityFailures: string[] = []
 
       for (const file of attachmentFiles) {
-        const ext   = path.extname(file.name)
-        const fname = uuidv4() + ext
-        const dest  = join(baseDir, fname)
+        console.log(`🔐 보안 검증 시작: ${file.name}`)
+        
+        // 보안 강화된 파일 저장
+        const saveResult: SaveFileResult = await saveFile({
+          file,
+          directory: "documents", // 문서 전용 디렉토리
+          originalName: file.name,
+          userId: uploaderName || "anonymous", // 업로더 정보 로깅용
+        })
+
+        if (!saveResult.success) {
+          console.error(`❌ 파일 보안 검증 실패: ${file.name} - ${saveResult.error}`)
+          securityFailures.push(`${file.name}: ${saveResult.error}`)
+          continue // 실패한 파일은 건너뛰고 계속 진행
+        }
 
-        await writeFile(dest, Buffer.from(await file.arrayBuffer()))
+        console.log(`✅ 파일 보안 검증 통과: ${file.name}`)
+        console.log(`📁 저장된 경로: ${saveResult.publicPath}`)
 
+        // DB에 첨부파일 정보 저장
         const [att] = await tx.insert(documentAttachments)
           .values({
             revisionId,
-            fileName: file.name,
-            filePath: "/documents/" + fname,
-            fileSize: file.size,
-            fileType: ext.slice(1).toLowerCase() || undefined,
+            fileName: saveResult.originalName!, // 원본 파일명
+            filePath: saveResult.publicPath!,   // 웹 접근 경로
+            fileSize: saveResult.fileSize!,
+            fileType: saveResult.fileName!.split('.').pop()?.toLowerCase() || undefined,
             updatedAt: new Date(),
           })
           .returning()
 
         uploadedFiles.push({
           id: att.id,
-          fileName: file.name,
-          fileSize: file.size,
-          filePath: att.filePath,
+          fileName: saveResult.originalName,
+          fileSize: saveResult.fileSize,
+          filePath: saveResult.publicPath,
+          fileType: saveResult.fileName!.split('.').pop()?.toLowerCase() || null,
+          securityChecks: saveResult.securityChecks, // 보안 검증 결과
         })
 
         // change_logs: attachment CREATE
         await logAttachmentChange(
-          contractId,
+          docInfo.contractId,
           att.id,
           "CREATE",
           att,
@@ -189,15 +226,35 @@ export async function POST(request: NextRequest) {
         )
       }
 
-      /* 4) documents.updatedAt */
+      // 보안 검증 실패한 파일이 있으면 경고 반환
+      if (securityFailures.length > 0) {
+        console.warn(`⚠️ 일부 파일의 보안 검증 실패:`, securityFailures)
+        
+        // 모든 파일이 실패한 경우 에러 반환
+        if (uploadedFiles.length === 0) {
+          throw new Error(`모든 파일의 보안 검증이 실패했습니다: ${securityFailures.join(', ')}`)
+        }
+      }
+
+      /* 4) documents.updatedAt 업데이트 */
       await tx.update(documents)
         .set({ updatedAt: new Date() })
         .where(eq(documents.id, docId))
 
-      return { revisionId, stage, revision, uploadedFiles, mode, contractId }
+      return { 
+        revisionId, 
+        stage, 
+        revision, 
+        uploadedFiles, 
+        mode, 
+        contractId: docInfo.contractId,
+        usage,
+        usageType,
+        securityFailures // 보안 실패 정보 포함
+      }
     })
 
-    // ✅ 캐시 무효화 - 트랜잭션 완료 후에 실행
+    // 캐시 무효화 - 트랜잭션 완료 후에 실행
     try {
       // enhanced documents 캐시 무효화
       revalidateTag(`enhanced-documents-${result.contractId}`)
@@ -211,10 +268,28 @@ export async function POST(request: NextRequest) {
       // 캐시 무효화 실패해도 업로드는 성공으로 처리
     }
 
+    // 응답 메시지 구성
+    let message = `${result.uploadedFiles.length}개 파일 업로드 완료`
+    if (result.securityFailures.length > 0) {
+      message += ` (일부 파일 보안 검증 실패: ${result.securityFailures.length}개)`
+    }
+
     return NextResponse.json({
       success: true,
-      message: `${result.uploadedFiles.length}개 파일 업로드 완료`,
-      data: result,
+      message,
+      data: {
+        revisionId: result.revisionId,
+        stage: result.stage,
+        revision: result.revision,
+        mode: result.mode,
+        usage: result.usage,
+        usageType: result.usageType,
+        uploaderName: uploaderName,
+        uploadedFiles: result.uploadedFiles,
+        filesCount: result.uploadedFiles.length,
+        securityFailures: result.securityFailures, // 클라이언트에 보안 실패 정보 전달
+        contractId: result.contractId,
+      },
     })
   } catch (e) {
     console.error("revision-upload error:", e)
diff --git a/app/api/sync/import/status/route.ts b/app/api/sync/import/status/route.ts
index c5b4b0bd..8b6144d6 100644
--- a/app/api/sync/import/status/route.ts
+++ b/app/api/sync/import/status/route.ts
@@ -1,8 +1,19 @@
-// app/api/sync/import/status/route.ts  
+// app/api/sync/status/route.ts
 import { NextRequest, NextResponse } from "next/server"
-import { importService } from "@/lib/vendor-document-list/import-service"
 import { getServerSession } from "next-auth"
 import { authOptions } from "@/app/api/auth/[...nextauth]/route"
+import db from "@/db/db"
+import { documents, revisions, documentAttachments, contracts, projects, vendors } from "@/db/schema"
+import { eq, and, sql, desc } from "drizzle-orm"
+
+interface SyncStatus {
+  syncEnabled: boolean
+  pendingChanges: number
+  syncedChanges: number
+  failedChanges: number
+  lastSyncAt?: string
+  error?: string
+}
 
 export async function GET(request: NextRequest) {
   try {
@@ -13,7 +24,7 @@ export async function GET(request: NextRequest) {
 
     const { searchParams } = new URL(request.url)
     const contractId = searchParams.get('contractId')
-    const sourceSystem = searchParams.get('sourceSystem') || 'DOLCE'
+    const targetSystem = searchParams.get('targetSystem') || 'SHI'
 
     if (!contractId) {
       return NextResponse.json(
@@ -22,20 +33,185 @@ export async function GET(request: NextRequest) {
       )
     }
 
-    const status = await importService.getImportStatus(
-      Number(contractId),
-      sourceSystem
-    )
+    // 🔥 안전하게 동기화 상태 조회
+    const syncStatus = await getSyncStatusSafely(Number(contractId), targetSystem)
+    
+    return NextResponse.json(syncStatus)
+    
+  } catch (error) {
+    console.error('Unexpected error in sync status API:', error)
+    
+    // 🔥 에러 시에도 200으로 응답하고 error 필드 포함
+    return NextResponse.json({
+      syncEnabled: false,
+      pendingChanges: 0,
+      syncedChanges: 0,
+      failedChanges: 0,
+      lastSyncAt: undefined,
+      error: '시스템 오류가 발생했습니다. 잠시 후 다시 시도해주세요.'
+    }, { status: 200 })
+  }
+}
+
+async function getSyncStatusSafely(contractId: number, targetSystem: string): Promise<SyncStatus> {
+  try {
+    // 1. 계약 정보 확인
+    const contractInfo = await db
+      .select({
+        projectCode: projects.code,
+        vendorCode: vendors.vendorCode,
+        contractStatus: contracts.status
+      })
+      .from(contracts)
+      .innerJoin(projects, eq(contracts.projectId, projects.id))
+      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
+      .where(eq(contracts.id, contractId))
+      .limit(1)
+
+    // 계약 정보가 없는 경우
+    if (!contractInfo || contractInfo.length === 0) {
+      return {
+        syncEnabled: false,
+        pendingChanges: 0,
+        syncedChanges: 0,
+        failedChanges: 0,
+        error: `계약 ${contractId}를 찾을 수 없습니다.`
+      }
+    }
+
+    const contract = contractInfo[0]
+
+    // 프로젝트 코드나 벤더 코드가 없는 경우
+    if (!contract.projectCode || !contract.vendorCode) {
+      return {
+        syncEnabled: false,
+        pendingChanges: 0,
+        syncedChanges: 0,
+        failedChanges: 0,
+        error: `계약 ${contractId}에 프로젝트 코드 또는 벤더 코드가 설정되지 않았습니다.`
+      }
+    }
+
+    // 2. 마지막 동기화 시간 조회
+    const [lastSync] = await db
+      .select({
+        lastSyncAt: sql<string>`MAX(${documents.externalSyncedAt})`
+      })
+      .from(documents)
+      .where(and(
+        eq(documents.contractId, contractId),
+        eq(documents.externalSystemType, targetSystem)
+      ))
+
+    // 3. 문서별 변경사항 분석
+    const documentStats = await db
+      .select({
+        id: documents.id,
+        docNumber: documents.docNumber,
+        updatedAt: documents.updatedAt,
+        externalSyncedAt: documents.externalSyncedAt,
+        syncStatus: documents.syncStatus
+      })
+      .from(documents)
+      .where(eq(documents.contractId, contractId))
+
+    let pendingChanges = 0
+    let syncedChanges = 0
+    let failedChanges = 0
+
+    // 각 문서의 동기화 상태 분석
+    for (const doc of documentStats) {
+      // 문서 자체가 변경되었는지 확인
+      const docNeedsSync = !doc.externalSyncedAt || 
+        (doc.updatedAt && doc.externalSyncedAt && doc.updatedAt > doc.externalSyncedAt)
+
+      if (docNeedsSync) {
+        if (doc.syncStatus === 'FAILED') {
+          failedChanges++
+        } else if (doc.syncStatus === 'SYNCED') {
+          syncedChanges++
+        } else {
+          pendingChanges++
+        }
+      }
+
+      // 해당 문서의 리비전 변경사항 확인
+      const revisionStats = await db
+        .select({
+          updatedAt: revisions.updatedAt,
+          externalSyncedAt: revisions.externalSyncedAt,
+          syncStatus: revisions.syncStatus
+        })
+        .from(revisions)
+        .innerJoin(documents, eq(revisions.documentId, documents.id))
+        .where(eq(documents.id, doc.id))
+
+      for (const revision of revisionStats) {
+        const revisionNeedsSync = !revision.externalSyncedAt || 
+          (revision.updatedAt && revision.externalSyncedAt && revision.updatedAt > revision.externalSyncedAt)
+
+        if (revisionNeedsSync) {
+          if (revision.syncStatus === 'FAILED') {
+            failedChanges++
+          } else if (revision.syncStatus === 'SYNCED') {
+            syncedChanges++
+          } else {
+            pendingChanges++
+          }
+        }
+      }
+
+      // 첨부파일 변경사항 확인
+      const attachmentStats = await db
+        .select({
+          updatedAt: documentAttachments.updatedAt,
+          externalSyncedAt: documentAttachments.externalSyncedAt,
+          syncStatus: documentAttachments.syncStatus
+        })
+        .from(documentAttachments)
+        .innerJoin(revisions, eq(documentAttachments.revisionId, revisions.id))
+        .innerJoin(documents, eq(revisions.documentId, documents.id))
+        .where(eq(documents.id, doc.id))
+
+      for (const attachment of attachmentStats) {
+        const attachmentNeedsSync = !attachment.externalSyncedAt || 
+          (attachment.updatedAt && attachment.externalSyncedAt && attachment.updatedAt > attachment.externalSyncedAt)
+
+        if (attachmentNeedsSync) {
+          if (attachment.syncStatus === 'FAILED') {
+            failedChanges++
+          } else if (attachment.syncStatus === 'SYNCED') {
+            syncedChanges++
+          } else {
+            pendingChanges++
+          }
+        }
+      }
+    }
+
+    // 4. 동기화 활성화 여부 확인
+    const syncEnabled = contract.contractStatus === 'ACTIVE' && 
+      Boolean(contract.projectCode) && 
+      Boolean(contract.vendorCode) &&
+      process.env[`SYNC_${targetSystem.toUpperCase()}_ENABLED`] === 'true'
+
+    return {
+      syncEnabled,
+      pendingChanges,
+      syncedChanges,
+      failedChanges,
+      lastSyncAt: lastSync?.lastSyncAt ? new Date(lastSync.lastSyncAt).toISOString() : undefined
+    }
 
-    return NextResponse.json(status)
   } catch (error) {
-    console.error('Failed to get import status:', error)
-    return NextResponse.json(
-      {
-        error: 'Failed to get import status',
-        message: error instanceof Error ? error.message : 'Unknown error'
-      },
-      { status: 500 }
-    )
+    console.error(`Failed to get sync status for contract ${contractId}:`, error)
+    
+    return {
+      syncEnabled: false,
+      pendingChanges: 0,
+      syncedChanges: 0,
+      failedChanges: 0,
+      error: error instanceof Error ? error.message : '동기화 상태를 확인할 수 없습니다.'
+    }
   }
 }
\ No newline at end of file