1 files changed, 138 insertions, 31 deletions

diff --git a/lib/vendor-document-list/import-service.ts b/lib/vendor-document-list/import-service.ts
index 9a4e44db..bc384ea2 100644
--- a/lib/vendor-document-list/import-service.ts
+++ b/lib/vendor-document-list/import-service.ts
@@ -890,22 +890,67 @@ class ImportService {
     detailDoc: DOLCEDetailDocument,
     sourceSystem: string
   ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
-    // 기존 revision 조회 (registerId로)
-    const existingRevision = await db
-      .select()
-      .from(revisions)
-      .where(and(
-        eq(revisions.issueStageId, issueStageId),
-        eq(revisions.registerId, detailDoc.RegisterId)
-      ))
-      .limit(1)
-
+    
+    // 🆕 여러 조건으로 기존 revision 조회
+    let existingRevision = null
+  
+    // 1차: registerId로 조회 (가장 정확한 매칭)
+    if (detailDoc.RegisterId) {
+      const results = await db
+        .select()
+        .from(revisions)
+        .where(and(
+          eq(revisions.issueStageId, issueStageId),
+          eq(revisions.registerId, detailDoc.RegisterId)
+        ))
+        .limit(1)
+      
+      if (results.length > 0) {
+        existingRevision = results[0]
+        console.log(`Found revision by registerId: ${detailDoc.RegisterId}`)
+      }
+    }
+  
+    // 2차: externalUploadId로 조회 (업로드했던 revision 매칭)
+    if (!existingRevision && detailDoc.UploadId) {
+      const results = await db
+        .select()
+        .from(revisions)
+        .where(and(
+          eq(revisions.issueStageId, issueStageId),
+          eq(revisions.externalUploadId, detailDoc.UploadId)
+        ))
+        .limit(1)
+      
+      if (results.length > 0) {
+        existingRevision = results[0]
+        console.log(`Found revision by externalUploadId: ${detailDoc.UploadId}`)
+      }
+    }
+  
+    // 3차: DrawingRevNo로 조회 (같은 issueStage 내에서 revision 번호 매칭)
+    if (!existingRevision && detailDoc.DrawingRevNo) {
+      const results = await db
+        .select()
+        .from(revisions)
+        .where(and(
+          eq(revisions.issueStageId, issueStageId),
+          eq(revisions.revision, detailDoc.DrawingRevNo)
+        ))
+        .limit(1)
+      
+      if (results.length > 0) {
+        existingRevision = results[0]
+        console.log(`Found revision by DrawingRevNo: ${detailDoc.DrawingRevNo}`)
+      }
+    }
+  
     // Category에 따른 uploaderType 매핑
     const uploaderType = this.mapCategoryToUploaderType(detailDoc.Category)
     
-    // RegisterKind에 따른 usage, usageType 매핑 (기본 로직, 추후 개선)
+    // RegisterKind에 따른 usage, usageType 매핑
     const { usage, usageType } = this.mapRegisterKindToUsage(detailDoc.RegisterKind)
-
+  
     // DOLCE 상세 데이터를 revisions 스키마에 맞게 변환
     const revisionData = {
       issueStageId,
@@ -916,27 +961,27 @@ class ImportService {
       usageType,
       revisionStatus: detailDoc.Status,
       externalUploadId: detailDoc.UploadId,
-      registerId: detailDoc.RegisterId,
+      registerId: detailDoc.RegisterId, // 🆕 항상 최신 registerId로 업데이트
       comment: detailDoc.RegisterDesc,
       submittedDate: this.convertDolceDateToDate(detailDoc.CreateDt),
       updatedAt: new Date()
     }
-
-    if (existingRevision.length > 0) {
+  
+    if (existingRevision) {
       // 업데이트 필요 여부 확인
-      const existing = existingRevision[0]
       const hasChanges =
-        existing.revision !== revisionData.revision ||
-        existing.revisionStatus !== revisionData.revisionStatus ||
-        existing.uploaderName !== revisionData.uploaderName
-
+        existingRevision.revision !== revisionData.revision ||
+        existingRevision.revisionStatus !== revisionData.revisionStatus ||
+        existingRevision.uploaderName !== revisionData.uploaderName ||
+        existingRevision.registerId !== revisionData.registerId // 🆕 registerId 변경 확인
+  
       if (hasChanges) {
         await db
           .update(revisions)
           .set(revisionData)
-          .where(eq(revisions.id, existing.id))
-
-        console.log(`Updated revision: ${detailDoc.RegisterId}`)
+          .where(eq(revisions.id, existingRevision.id))
+  
+        console.log(`Updated revision: ${detailDoc.RegisterId} (local ID: ${existingRevision.id})`)
         return 'UPDATED'
       } else {
         return 'SKIPPED'
@@ -949,12 +994,11 @@ class ImportService {
           ...revisionData,
           createdAt: new Date()
         })
-
+  
       console.log(`Created new revision: ${detailDoc.RegisterId}`)
       return 'NEW'
     }
   }
-
   /**
    * Category를 uploaderType으로 매핑
    */
@@ -972,12 +1016,75 @@ class ImportService {
   /**
    * RegisterKind를 usage/usageType으로 매핑
    */
-  private mapRegisterKindToUsage(registerKind: string): { usage: string; usageType: string } {
-    // TODO: 추후 비즈니스 로직에 맞게 구현
-    // 현재는 기본 매핑만 제공
-    return {
-      usage: registerKind || 'DEFAULT',
-      usageType: registerKind || 'DEFAULT'
+  private mapRegisterKindToUsage(registerKind: string): { usage: string; usageType: string | null } {
+    if (!registerKind) {
+      return {
+        usage: 'DEFAULT',
+        usageType: 'DEFAULT'
+      }
+    }
+  
+    switch (registerKind.toUpperCase()) {
+      case 'APPR':
+        return {
+          usage: 'APPROVAL',
+          usageType: 'Full'
+        }
+      
+      case 'APPR-P':
+        return {
+          usage: 'APPROVAL',
+          usageType: 'Partial'
+        }
+      
+      case 'WORK':
+        return {
+          usage: 'WORKING',
+          usageType: 'Full'
+        }
+      
+      case 'WORK-P':
+        return {
+          usage: 'WORKING',
+          usageType: 'Partial'
+        }
+      
+      case 'FMEA-1':
+        return {
+          usage: 'The 1st',
+          usageType: null
+        }
+      
+      case 'FMEA-2':
+        return {
+          usage: 'The 2nd',
+          usageType: null
+        }
+      
+      case 'RECP':
+        return {
+          usage: 'Pre',
+          usageType: null
+        }
+      
+      case 'RECW':
+        return {
+          usage: 'Working',
+          usageType: null
+        }
+      
+      case 'CMTM':
+        return {
+          usage: 'Mark-Up',
+          usageType: null
+        }
+      
+      default:
+        console.warn(`Unknown RegisterKind: ${registerKind}`)
+        return {
+          usage: registerKind,
+          usageType: 'DEFAULT'
+        }
     }
   }