(대표님) dolce 수정, spreadjs 수정 등

6 files changed, 433 insertions, 103 deletions

diff --git a/lib/vendor-document-list/dolce-upload-service.ts b/lib/vendor-document-list/dolce-upload-service.ts
index 85085d80..7717877b 100644
--- a/lib/vendor-document-list/dolce-upload-service.ts
+++ b/lib/vendor-document-list/dolce-upload-service.ts
@@ -293,6 +293,8 @@ class DOLCEUploadService {
         externalRegisterId: revisions.id,
         externalSentAt: revisions.submittedDate,
 
+        serialNo: revisions.serialNo,
+
         // issueStages 테이블 정보
         issueStageId: issueStages.id,
         stageName: issueStages.stageName,
@@ -644,40 +646,53 @@ class DOLCEUploadService {
 
     if (revision.usage && revision.usage !== 'DEFAULT') {
       switch (revision.usage) {
+
         case "APPROVAL":
-          if (revision.usageType === "Full") {
-            registerKind = "APPR"
-          } else if (revision.usageType === "Partial") {
-            registerKind = "APPR-P"
-          } else {
-            registerKind = "APPR" // 기본값
+          if (revision.drawingKind === "B3") {
+            if (revision.usageType === "Full") {
+              registerKind = "APPR"
+            } else if (revision.usageType === "Partial") {
+              registerKind = "APPR-P"
+            } else {
+              registerKind = "APPR" // 기본값
+            }
           }
           break
 
         case "WORKING":
-          if (revision.usageType === "Full") {
-            registerKind = "WORK"
-          } else if (revision.usageType === "Partial") {
-            registerKind = "WORK-P"
-          } else {
-            registerKind = "WORK" // 기본값
+          if (revision.drawingKind === "B3") {
+            if (revision.usageType === "Full") {
+              registerKind = "WORK"
+            } else if (revision.usageType === "Partial") {
+              registerKind = "WORK-P"
+            } else {
+              registerKind = "WORK" // 기본값
+            }
           }
           break
 
         case "The 1st":
-          registerKind = "FMEA-1"
+          if (revision.drawingKind === "B5") {
+            registerKind = "FMEA-1"
+          }
           break
 
         case "The 2nd":
-          registerKind = "FMEA-2"
+          if (revision.drawingKind === "B5") {
+            registerKind = "FMEA-2"
+          }
           break
 
         case "Pre":
-          registerKind = "RECP"
+          if (revision.drawingKind === "B3") {
+            registerKind = "RECP"
+          }
           break
 
         case "Working":
-          registerKind = "RECW"
+          if (revision.drawingKind === "B3") {
+            registerKind = "RECW"
+          }
           break
 
         case "Mark-Up":
@@ -742,7 +757,7 @@ class DOLCEUploadService {
       DrawingNo: revision.documentNo,
       DrawingName: revision.documentName,
       RegisterGroupId: revision.registerGroupId || 0,
-      RegisterSerialNo: getSerialNumber(revision.revision || "1"),
+      RegisterSerialNo: revision.serialNo || getSerialNumber(revision.revision || "1"),
       RegisterKind: registerKind, // usage/usageType에 따라 동적 설정
       DrawingRevNo: revision.revision || "-",
       Category: revision.category || "TS",
diff --git a/lib/vendor-document-list/import-service.ts b/lib/vendor-document-list/import-service.ts
index 13c51824..fb4db85e 100644
--- a/lib/vendor-document-list/import-service.ts
+++ b/lib/vendor-document-list/import-service.ts
@@ -1082,13 +1082,14 @@ class ImportService {
       issueStageId,
       revision: detailDoc.DrawingRevNo,
       uploaderType,
-      uploaderName: detailDoc.CreateUserNM,
+      registerSerialNoMax:detailDoc.RegisterSerialNoMax,
+      // uploaderName: detailDoc.CreateUserNM,
       usage,
       usageType,
       revisionStatus: detailDoc.Status,
       externalUploadId: detailDoc.UploadId,
       registerId: detailDoc.RegisterId, // 🆕 항상 최신 registerId로 업데이트
-      comment: detailDoc.RegisterDesc,
+      comment: detailDoc.SHINote,
       submittedDate: this.convertDolceDateToDate(detailDoc.CreateDt),
       updatedAt: new Date()
     }
@@ -1098,7 +1099,8 @@ class ImportService {
       const hasChanges =
         existingRevision.revision !== revisionData.revision ||
         existingRevision.revisionStatus !== revisionData.revisionStatus ||
-        existingRevision.uploaderName !== revisionData.uploaderName ||
+        existingRevision.registerSerialNoMax !== revisionData.registerSerialNoMax ||
+        // existingRevision.uploaderName !== revisionData.uploaderName ||
         existingRevision.serialNo !== revisionData.serialNo ||
         existingRevision.registerId !== revisionData.registerId // 🆕 registerId 변경 확인
   
diff --git a/lib/vendor-document-list/plant/document-stages-columns.tsx b/lib/vendor-document-list/plant/document-stages-columns.tsx
index d71ecc0f..9a53b55b 100644
--- a/lib/vendor-document-list/plant/document-stages-columns.tsx
+++ b/lib/vendor-document-list/plant/document-stages-columns.tsx
@@ -347,6 +347,26 @@ export function getDocumentStagesColumns({
       },
     },
 
+    {
+      accessorKey: "buyerSystemComment",
+      header: ({ column }) => (
+        <DataTableColumnHeaderSimple column={column} title="Document Comment" />
+      ),
+      cell: ({ row }) => {
+        const doc = row.original
+
+        return (
+          <div className="flex items-center gap-2">
+               {doc.buyerSystemComment}
+          </div>
+        )
+      },
+      size: 180,
+      enableResizing: true,
+      meta: {
+        excelHeader: "Document Comment"
+      },
+    },
     // {
     //   accessorKey: "buyerSystemStatus",
     //   header: ({ column }) => (
diff --git a/lib/vendor-document-list/ship/enhanced-documents-table.tsx b/lib/vendor-document-list/ship/enhanced-documents-table.tsx
index 24ab42fb..cae0fe06 100644
--- a/lib/vendor-document-list/ship/enhanced-documents-table.tsx
+++ b/lib/vendor-document-list/ship/enhanced-documents-table.tsx
@@ -1,4 +1,4 @@
-// simplified-documents-table.tsx - 최적화된 버전
+// simplified-documents-table.tsx - Project Code 필터 기능 추가
 "use client"
 
 import React from "react"
@@ -13,7 +13,8 @@ import { getUserVendorDocuments, getUserVendorDocumentStats } from "@/lib/vendor
 import { DataTableAdvancedToolbar } from "@/components/data-table/data-table-advanced-toolbar"
 import { toast } from "sonner"
 import { Badge } from "@/components/ui/badge"
-import { FileText } from "lucide-react"
+import { Button } from "@/components/ui/button"
+import { FileText, FileInput, FileOutput, FolderOpen, Building2 } from "lucide-react"
 
 import { Label } from "@/components/ui/label"
 import { DataTable } from "@/components/data-table/data-table"
@@ -21,6 +22,15 @@ import { SimplifiedDocumentsView } from "@/db/schema"
 import { getSimplifiedDocumentColumns } from "./enhanced-doc-table-columns"
 import { EnhancedDocTableToolbarActions } from "./enhanced-doc-table-toolbar-actions"
 
+// 🔥 Project Code 필터를 위한 Select 컴포넌트 import 추가
+import {
+  Select,
+  SelectContent,
+  SelectItem,
+  SelectTrigger,
+  SelectValue,
+} from "@/components/ui/select"
+
 // DrawingKind별 설명 매핑
 const DRAWING_KIND_INFO = {
   B3: {
@@ -60,17 +70,61 @@ export function SimplifiedDocumentsTable({
   const { data, pageCount, total, drawingKind, vendorInfo } = React.useMemo(() => documentResult, [documentResult])
   const { stats, totalDocuments, primaryDrawingKind } = React.useMemo(() => statsResult, [statsResult])
 
+  // 🔥 B4 필터 상태 추가
+  const [b4FilterType, setB4FilterType] = React.useState<'all' | 'gtt_deliverable' | 'shi_input'>('all')
+
+  // 🔥 Project Code 필터 상태 추가
+  const [selectedProjectCode, setSelectedProjectCode] = React.useState<string>('all')
+
+  // 🔥 고유한 Project Code 목록 추출 및 카운트 메모이제이션
+  const projectCodeStats = React.useMemo(() => {
+    const projectMap = new Map<string, number>()
+    
+    data.forEach(doc => {
+      const projectCode = doc.projectCode || 'Unknown'
+      projectMap.set(projectCode, (projectMap.get(projectCode) || 0) + 1)
+    })
+
+    // 정렬된 배열로 변환 (프로젝트 코드 알파벳순)
+    return Array.from(projectMap.entries())
+      .sort((a, b) => a[0].localeCompare(b[0]))
+      .map(([code, count]) => ({ code, count }))
+  }, [data])
+
   // 🔥 데이터 로드 콜백을 useCallback으로 최적화
   const handleDataLoaded = React.useCallback((loadedData: SimplifiedDocumentsView[]) => {
     onDataLoaded?.(loadedData)
   }, [onDataLoaded])
 
-  // 🔥 데이터가 로드되면 콜백 호출 (의존성 최적화)
+  // 🔥 B4 및 Project Code 필터링된 데이터 메모이제이션
+  const filteredData = React.useMemo(() => {
+    let result = data
+
+    // B4 필터 적용
+    if (b4FilterType !== 'all') {
+      if (b4FilterType === 'gtt_deliverable') {
+        result = result.filter(doc => doc.drawingMoveGbn === '도면입수')
+      } else if (b4FilterType === 'shi_input') {
+        result = result.filter(doc => doc.drawingMoveGbn === '도면제출')
+      }
+    }
+
+    // Project Code 필터 적용
+    if (selectedProjectCode !== 'all') {
+      result = result.filter(doc => 
+        (doc.projectCode || 'Unknown') === selectedProjectCode
+      )
+    }
+
+    return result
+  }, [data, b4FilterType, selectedProjectCode])
+
+  // 🔥 데이터가 로드되면 콜백 호출 (필터링된 데이터 사용)
   React.useEffect(() => {
-    if (data && handleDataLoaded) {
-      handleDataLoaded(data)
+    if (filteredData && handleDataLoaded) {
+      handleDataLoaded(filteredData)
     }
-  }, [data, handleDataLoaded])
+  }, [filteredData, handleDataLoaded])
 
   // 🔥 상태들을 안정적으로 관리
   const [rowAction, setRowAction] = React.useState<DataTableRowAction<SimplifiedDocumentsView> | null>(null)
@@ -81,7 +135,7 @@ export function SimplifiedDocumentsTable({
     () => getSimplifiedDocumentColumns({ 
       setRowAction,
     }),
-    [] // setRowAction은 항상 동일한 함수이므로 의존성에서 제외
+    []
   )
 
   // 🔥 필터 필드들을 메모이제이션
@@ -238,7 +292,7 @@ export function SimplifiedDocumentsTable({
   const getRowId = React.useCallback((originalRow: SimplifiedDocumentsView) => String(originalRow.documentId), [])
 
   const { table } = useDataTable({
-    data,
+    data: filteredData,
     columns,
     pageCount,
     enablePinning: true,
@@ -260,6 +314,21 @@ export function SimplifiedDocumentsTable({
     return activeDrawingKind ? DRAWING_KIND_INFO[activeDrawingKind] : null
   }, [activeDrawingKind])
 
+  // 🔥 B4 문서 통계 계산
+  const b4Stats = React.useMemo(() => {
+    if (!hasB4Documents) return null
+    
+    const gttDeliverableCount = data.filter(doc => 
+      doc.drawingKind === 'B4' && doc.drawingMoveGbn === '도면입수'
+    ).length
+    
+    const shiInputCount = data.filter(doc => 
+      doc.drawingKind === 'B4' && doc.drawingMoveGbn === '도면제출'
+    ).length
+    
+    return { gttDeliverableCount, shiInputCount }
+  }, [data, hasB4Documents])
+
   return (
     <div className="w-full space-y-4">
       {/* DrawingKind 정보 간단 표시 */}
@@ -270,12 +339,107 @@ export function SimplifiedDocumentsTable({
           </div>
           <div className="flex items-center gap-2">
             <Badge variant="outline">
-              {total} documents
+              {filteredData.length} documents
             </Badge>
           </div>
         </div>
       )}
 
+      {/* 🔥 필터 섹션 - Project Code 필터와 B4 필터를 함께 배치 */}
+      <div className="space-y-3">
+        {/* Project Code 필터 드롭다운 */}
+        <div className="flex items-center gap-3 p-4 bg-muted/30 rounded-lg">
+          <div className="flex items-center gap-2">
+            <Building2 className="h-4 w-4 text-muted-foreground" />
+            <Label className="text-sm font-medium">Project:</Label>
+          </div>
+          <Select
+            value={selectedProjectCode}
+            onValueChange={setSelectedProjectCode}
+          >
+            <SelectTrigger className="w-[200px]">
+              <SelectValue placeholder="Select a project" />
+            </SelectTrigger>
+            <SelectContent>
+              <SelectItem value="all">
+                <div className="flex items-center justify-between w-full">
+                  <span>All Projects</span>
+                  <Badge variant="secondary" className="ml-2">
+                    {data.length}
+                  </Badge>
+                </div>
+              </SelectItem>
+              {projectCodeStats.map(({ code, count }) => (
+                <SelectItem key={code} value={code}>
+                  <div className="flex items-center justify-between w-full">
+                    <span className="font-mono">{code}</span>
+                    <Badge variant="secondary" className="ml-2">
+                      {count}
+                    </Badge>
+                  </div>
+                </SelectItem>
+              ))}
+            </SelectContent>
+          </Select>
+          
+          {selectedProjectCode !== 'all' && (
+            <Button
+              variant="ghost"
+              size="sm"
+              onClick={() => setSelectedProjectCode('all')}
+              className="h-8"
+            >
+              Clear filter
+            </Button>
+          )}
+        </div>
+
+        {/* B4 필터 버튼 - 기존 코드 유지 */}
+        {hasB4Documents && b4Stats && (
+          <div className="flex items-center gap-2 p-4 bg-muted/50 rounded-lg">
+            <Label className="text-sm font-medium">Document Type:</Label>
+            <div className="flex gap-2">
+              <Button
+                variant={b4FilterType === 'all' ? 'default' : 'outline'}
+                size="sm"
+                onClick={() => setB4FilterType('all')}
+                className="gap-2"
+              >
+                <FileText className="h-4 w-4" />
+                All
+                <Badge variant="secondary" className="ml-1">
+                  {b4Stats.gttDeliverableCount + b4Stats.shiInputCount}
+                </Badge>
+              </Button>
+              <Button
+                variant={b4FilterType === 'gtt_deliverable' ? 'default' : 'outline'}
+                size="sm"
+                onClick={() => setB4FilterType('gtt_deliverable')}
+                className="gap-2"
+              >
+                <FileInput className="h-4 w-4" />
+                GTT Deliverable
+                <Badge variant="secondary" className="ml-1">
+                  {b4Stats.gttDeliverableCount}
+                </Badge>
+              </Button>
+              <Button
+                variant={b4FilterType === 'shi_input' ? 'default' : 'outline'}
+                size="sm"
+                onClick={() => setB4FilterType('shi_input')}
+                className="gap-2"
+              >
+                <FileOutput className="h-4 w-4" />
+                SHI Input Document
+                <Badge variant="secondary" className="ml-1">
+                  {b4Stats.shiInputCount}
+                </Badge>
+              </Button>
+            </div>
+          </div>
+        )}
+      </div>
+
       {/* 테이블 */}
       <div className="overflow-x-auto">
         <DataTable table={table} compact>
@@ -287,7 +451,7 @@ export function SimplifiedDocumentsTable({
             <EnhancedDocTableToolbarActions
               table={table}
               projectType="ship"
-              b4={hasB4Documents}
+              b4={hasB4Documents && b4FilterType === 'gtt_deliverable'}
             />
           </DataTableAdvancedToolbar>
         </DataTable>
diff --git a/lib/vendor-document-list/ship/send-to-shi-button.tsx b/lib/vendor-document-list/ship/send-to-shi-button.tsx
index 52874702..7bb85710 100644
--- a/lib/vendor-document-list/ship/send-to-shi-button.tsx
+++ b/lib/vendor-document-list/ship/send-to-shi-button.tsx
@@ -325,21 +325,123 @@ export function SendToSHIButton({
               <div className="space-y-3">
                 <Separator />
 
+                {/* 전체 통계 */}
                 <div className="grid grid-cols-3 gap-4 text-sm">
                   <div className="text-center">
                     <div className="text-muted-foreground">{t('shiSync.labels.pending')}</div>
-                    <div className="font-medium text-orange-600">{t('shiSync.labels.itemCount', { count: totalStats.totalPending })}</div>
+                    <div className="font-medium text-orange-600">
+                      {t('shiSync.labels.itemCount', { count: totalStats.totalPending })}
+                    </div>
                   </div>
                   <div className="text-center">
                     <div className="text-muted-foreground">{t('shiSync.labels.synced')}</div>
-                    <div className="font-medium text-emerald-600 dark:text-emerald-400">{t('shiSync.labels.itemCount', { count: totalStats.totalSynced })}</div>
+                    <div className="font-medium text-emerald-600 dark:text-emerald-400">
+                      {t('shiSync.labels.itemCount', { count: totalStats.totalSynced })}
+                    </div>
                   </div>
                   <div className="text-center">
                     <div className="text-muted-foreground">{t('shiSync.labels.failed')}</div>
-                    <div className="font-medium text-destructive">{t('shiSync.labels.itemCount', { count: totalStats.totalFailed })}</div>
+                    <div className="font-medium text-destructive">
+                      {t('shiSync.labels.itemCount', { count: totalStats.totalFailed })}
+                    </div>
                   </div>
                 </div>
 
+                {/* EntityType별 상세 통계 추가 */}
+                {totalStats.entityTypeDetailsTotals && (
+                  <>
+                    <Separator className="my-2" />
+                    <div className="space-y-2">
+                      <div className="text-sm font-medium flex items-center gap-2">
+                        {t('shiSync.labels.detailsByType')}
+                        <Badge variant="outline" className="text-xs">
+                          {t('shiSync.labels.experimental')}
+                        </Badge>
+                      </div>
+
+                      <div className="space-y-1 text-xs">
+                        {/* Document 통계 */}
+                        {totalStats.entityTypeDetailsTotals.document && (
+                          <div className="flex items-center justify-between p-2 rounded bg-muted/50">
+                            <span className="font-medium">
+                              {t('shiSync.labels.documents')}
+                            </span>
+                            <div className="flex gap-3 text-xs">
+                              {totalStats.entityTypeDetailsTotals.document.pending > 0 && (
+                                <span className="text-orange-600">
+                                  {totalStats.entityTypeDetailsTotals.document.pending} {t('shiSync.labels.pendingShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.document.synced > 0 && (
+                                <span className="text-emerald-600">
+                                  {totalStats.entityTypeDetailsTotals.document.synced} {t('shiSync.labels.syncedShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.document.failed > 0 && (
+                                <span className="text-destructive">
+                                  {totalStats.entityTypeDetailsTotals.document.failed} {t('shiSync.labels.failedShort')}
+                                </span>
+                              )}
+                            </div>
+                          </div>
+                        )}
+
+                        {/* Revision 통계 */}
+                        {totalStats.entityTypeDetailsTotals.revision && (
+                          <div className="flex items-center justify-between p-2 rounded bg-muted/50">
+                            <span className="font-medium">
+                              {t('shiSync.labels.revisions')}
+                            </span>
+                            <div className="flex gap-3 text-xs">
+                              {totalStats.entityTypeDetailsTotals.revision.pending > 0 && (
+                                <span className="text-orange-600">
+                                  {totalStats.entityTypeDetailsTotals.revision.pending} {t('shiSync.labels.pendingShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.revision.synced > 0 && (
+                                <span className="text-emerald-600">
+                                  {totalStats.entityTypeDetailsTotals.revision.synced} {t('shiSync.labels.syncedShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.revision.failed > 0 && (
+                                <span className="text-destructive">
+                                  {totalStats.entityTypeDetailsTotals.revision.failed} {t('shiSync.labels.failedShort')}
+                                </span>
+                              )}
+                            </div>
+                          </div>
+                        )}
+
+                        {/* Attachment 통계 */}
+                        {totalStats.entityTypeDetailsTotals.attachment && (
+                          <div className="flex items-center justify-between p-2 rounded bg-muted/50">
+                            <span className="font-medium">
+                              {t('shiSync.labels.attachments')}
+                            </span>
+                            <div className="flex gap-3 text-xs">
+                              {totalStats.entityTypeDetailsTotals.attachment.pending > 0 && (
+                                <span className="text-orange-600">
+                                  {totalStats.entityTypeDetailsTotals.attachment.pending} {t('shiSync.labels.pendingShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.attachment.synced > 0 && (
+                                <span className="text-emerald-600">
+                                  {totalStats.entityTypeDetailsTotals.attachment.synced} {t('shiSync.labels.syncedShort')}
+                                </span>
+                              )}
+                              {totalStats.entityTypeDetailsTotals.attachment.failed > 0 && (
+                                <span className="text-destructive">
+                                  {totalStats.entityTypeDetailsTotals.attachment.failed} {t('shiSync.labels.failedShort')}
+                                </span>
+                              )}
+                            </div>
+                          </div>
+                        )}
+                      </div>
+                    </div>
+                  </>
+                )}
+
                 {/* 계약별 상세 상태 */}
                 {contractStatuses.length > 1 && (
                   <div className="space-y-2">
diff --git a/lib/vendor-document-list/sync-service.ts b/lib/vendor-document-list/sync-service.ts
index cdbf489f..c3ddfcca 100644
--- a/lib/vendor-document-list/sync-service.ts
+++ b/lib/vendor-document-list/sync-service.ts
@@ -101,7 +101,7 @@ class SyncService {
    * 동기화할 변경사항 조회 (증분)
    */
   async getPendingChanges(
-    vendorId: number,
+    userId: number,
     targetSystem: string = 'DOLCE',
     limit?: number
   ): Promise<ChangeLog[]> {
@@ -109,7 +109,7 @@ class SyncService {
       .select()
       .from(changeLogs)
       .where(and(
-        eq(changeLogs.vendorId, vendorId),
+        eq(changeLogs.userId, userId),
         eq(changeLogs.isSynced, false),
         lt(changeLogs.syncAttempts, 3),
         sql`(${changeLogs.targetSystems} IS NULL OR ${changeLogs.targetSystems} @> ${JSON.stringify([targetSystem])})`
@@ -176,10 +176,11 @@ class SyncService {
       }
 
       const vendorId = Number(session.user.companyId)
+      const userId = Number(session.user.id)
 
 
       // 2. 대기 중인 변경사항 조회 (전체)
-      const pendingChanges = await this.getPendingChanges(vendorId, targetSystem)
+      const pendingChanges = await this.getPendingChanges(userId, targetSystem)
 
       if (pendingChanges.length === 0) {
         return {
@@ -457,79 +458,105 @@ class SyncService {
       .where(inArray(changeLogs.id, changeIds))
   }
 
-  /**
-   * 동기화 상태 조회
-   */
-  async getSyncStatus(projectId: number, targetSystem: string = 'DOLCE') {
-    try {
+/**
+ * 동기화 상태 조회 - entityType별 상세 통계 포함
+ */
+async getSyncStatus(projectId: number, targetSystem: string = 'DOLCE') {
+  try {
+    const session = await getServerSession(authOptions)
+    if (!session?.user?.companyId) {
+      throw new Error("인증이 필요합니다.")
+    }
 
-      const session = await getServerSession(authOptions)
-      if (!session?.user?.companyId) {
-        throw new Error("인증이 필요합니다.")
+    const vendorId = Number(session.user.companyId)
+    const userId = Number(session.user.id)
+
+    // 기본 조건
+    const baseConditions = and(
+      eq(changeLogs.userId, userId),
+      sql`(${changeLogs.targetSystems} IS NULL OR ${changeLogs.targetSystems} @> ${JSON.stringify([targetSystem])})`
+    )
+
+    // entityType별 통계를 위한 쿼리
+    const entityStats = await db
+      .select({
+        entityType: changeLogs.entityType,
+        pendingCount: sql<number>`COUNT(*) FILTER (WHERE ${changeLogs.isSynced} = false AND ${changeLogs.syncAttempts} < 3)`,
+        syncedCount: sql<number>`COUNT(*) FILTER (WHERE ${changeLogs.isSynced} = true)`,
+        failedCount: sql<number>`COUNT(*) FILTER (WHERE ${changeLogs.isSynced} = false AND ${changeLogs.syncAttempts} >= 3)`,
+        totalCount: sql<number>`COUNT(*)`
+      })
+      .from(changeLogs)
+      .where(baseConditions)
+      .groupBy(changeLogs.entityType)
+
+    // 전체 통계 계산
+    const totals = entityStats.reduce((acc, stat) => ({
+      pendingChanges: acc.pendingChanges + Number(stat.pendingCount),
+      syncedChanges: acc.syncedChanges + Number(stat.syncedCount),
+      failedChanges: acc.failedChanges + Number(stat.failedCount),
+      totalChanges: acc.totalChanges + Number(stat.totalCount)
+    }), {
+      pendingChanges: 0,
+      syncedChanges: 0,
+      failedChanges: 0,
+      totalChanges: 0
+    })
+
+    // entityType별 상세 정보 구성
+    const entityTypeDetails = {
+      document: {
+        pending: 0,
+        synced: 0,
+        failed: 0,
+        total: 0
+      },
+      revision: {
+        pending: 0,
+        synced: 0,
+        failed: 0,
+        total: 0
+      },
+      attachment: {
+        pending: 0,
+        synced: 0,
+        failed: 0,
+        total: 0
       }
-  
-      const vendorId = Number(session.user.companyId)
-
-      
-      // 대기 중인 변경사항 수 조회
-      const pendingCount = await db.$count(
-        changeLogs,
-        and(
-          eq(changeLogs.vendorId, vendorId),
-          eq(changeLogs.isSynced, false),
-          lt(changeLogs.syncAttempts, 3),
-          sql`(${changeLogs.targetSystems} IS NULL OR ${changeLogs.targetSystems} @> ${JSON.stringify([targetSystem])})`
-        )
-      )
-
-      // 동기화된 변경사항 수 조회
-      const syncedCount = await db.$count(
-        changeLogs,
-        and(
-          eq(changeLogs.vendorId, vendorId),
-          eq(changeLogs.isSynced, true),
-          sql`(${changeLogs.targetSystems} IS NULL OR ${changeLogs.targetSystems} @> ${JSON.stringify([targetSystem])})`
-        )
-      )
-
-      // 실패한 변경사항 수 조회
-      const failedCount = await db.$count(
-        changeLogs,
-        and(
-          eq(changeLogs.vendorId, vendorId),
-          eq(changeLogs.isSynced, false),
-          sql`${changeLogs.syncAttempts} >= 3`,
-          sql`(${changeLogs.targetSystems} IS NULL OR ${changeLogs.targetSystems} @> ${JSON.stringify([targetSystem])})`
-        )
-      )
-
-      // 마지막 성공한 배치 조회
-      const [lastSuccessfulBatch] = await db
-        .select()
-        .from(syncBatches)
-        .where(and(
-          eq(syncBatches.vendorId, vendorId),
-          eq(syncBatches.targetSystem, targetSystem),
-          eq(syncBatches.status, 'SUCCESS')
-        ))
-        .orderBy(desc(syncBatches.completedAt))
-        .limit(1)
+    }
 
-      return {
-        vendorId,
-        targetSystem,
-        totalChanges: pendingCount + syncedCount + failedCount,
-        pendingChanges: pendingCount,
-        syncedChanges: syncedCount,
-        failedChanges: failedCount,
-        lastSyncAt: lastSuccessfulBatch?.completedAt?.toISOString() || null,
-        syncEnabled: this.isSyncEnabled(targetSystem)
+    // 통계 데이터를 entityTypeDetails에 매핑
+    entityStats.forEach(stat => {
+      const entityType = stat.entityType as 'document' | 'revision' | 'attachment'
+      if (entityTypeDetails[entityType]) {
+        entityTypeDetails[entityType] = {
+          pending: Number(stat.pendingCount),
+          synced: Number(stat.syncedCount),
+          failed: Number(stat.failedCount),
+          total: Number(stat.totalCount)
+        }
       }
-    } catch (error) {
-      console.error('Failed to get sync status:', error)
-      throw error
+    })
+
+ 
+    return {
+      projectId, 
+      vendorId,
+      targetSystem,
+      ...totals,
+      entityTypeDetails, // entityType별 상세 통계
+      syncEnabled: this.isSyncEnabled(targetSystem),
+      // 추가 메타데이터
+      hasPendingChanges: totals.pendingChanges > 0,
+      hasFailedChanges: totals.failedChanges > 0,
+      syncHealthy: totals.failedChanges === 0 && totals.pendingChanges < 100,
+      requiresSync: totals.pendingChanges > 0 
     }
+  } catch (error) {
+    console.error('Failed to get sync status:', error)
+    throw error
   }
+}
 
   /**
    * 최근 동기화 배치 목록 조회