path: root/lib/vendor-document-list/import-service.ts

// lib/vendor-document-list/import-service.ts - DOLCE API 연동 버전 (파일 다운로드 포함)

import db from "@/db/db"
import { documents, issueStages, contracts, projects, vendors, revisions, documentAttachments } from "@/db/schema"
import { eq, and, sql, asc } from "drizzle-orm"
import { writeFile, mkdir } from "fs/promises"
import { join } from "path"
import { v4 as uuidv4 } from "uuid"
import { extname } from "path"
import * as crypto from "crypto"
import { debugError, debugWarn, debugSuccess, debugProcess } from "@/lib/debug-utils"
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"

export interface ImportResult {
  success: boolean
  newCount: number
  updatedCount: number
  skippedCount: number
  newRevisionsCount: number
  updatedRevisionsCount: number
  newAttachmentsCount: number
  updatedAttachmentsCount: number
  downloadedFilesCount: number
  errors?: string[]
  message?: string
}

export interface ImportStatus {
  lastImportAt?: string
  availableDocuments: number
  newDocuments: number
  updatedDocuments: number
  availableRevisions: number
  newRevisions: number
  updatedRevisions: number
  availableAttachments: number
  newAttachments: number
  updatedAttachments: number
  importEnabled: boolean
  error?: string
}

interface DOLCEDocument {
  CGbn?: string
  CreateDt: string
  CreateUserENM: string
  CreateUserId: string
  CreateUserNo: string
  DGbn?: string
  DegreeGbn?: string
  DeptGbn?: string
  Discipline: string
  DrawingKind: string // B3, B4, B5
  DrawingMoveGbn: string
  DrawingName: string
  DrawingNo: string
  GTTInput_PlanDate?: string
  GTTInput_ResultDate?: string
  AppDwg_PlanDate?: string
  AppDwg_ResultDate?: string
  WorDwg_PlanDate?: string
  WorDwg_ResultDate?: string
  GTTPreDwg_PlanDate?: string
  GTTPreDwg_ResultDate?: string
  GTTWorkingDwg_PlanDate?: string
  GTTWorkingDwg_ResultDate?: string
  FMEAFirst_PlanDate?: string
  FMEAFirst_ResultDate?: string
  FMEASecond_PlanDate?: string
  FMEASecond_ResultDate?: string
  JGbn?: string
  Manager: string
  ManagerENM: string
  ManagerNo: string
  ProjectNo: string
  RegisterGroup: number
  RegisterGroupId: number
  SGbn?: string
  SHIDrawingNo?: string
}

interface DOLCEDetailDocument {
  Status: string
  Category: string // TS, FS
  CategoryNM: string
  CategoryENM: string
  RegisterId: string
  ProjectNo: string
  DrawingNo: string
  RegisterGroupId: number
  RegisterGroup: number
  DrawingName: string
  RegisterSerialNoMax: number
  RegisterSerialNo: number
  DrawingUsage: string
  DrawingUsageNM: string
  DrawingUsageENM: string
  RegisterKind: string
  RegisterKindNM: string
  RegisterKindENM: string
  DrawingRevNo: string
  RegisterDesc: string
  UploadId: string
  ManagerNM: string
  Manager: string
  UseYn: string
  RegCompanyCode: string
  RegCompanyNM: string
  RegCompanyENM: string
  CreateUserENM: string
  CreateUserNM: string
  CreateUserId: string
  CreateDt: string
  ModifyUserId: string
  ModifyDt: string
  Discipline: string
  DrawingKind: string
  DrawingMoveGbn: string
  SHIDrawingNo: string
  Receiver: string
  SHINote: string
  OFDC_NO: string | null // OFDC Number for document identification
}

interface DOLCEFileInfo {
  FileId: string
  UploadId: string
  FileSeq: number
  FileServerId: string
  FileTitle: string
  FileDescription: string
  FileName: string
  FileRelativePath: string
  FileSize: number
  FileCreateDT: string
  FileWriteDT: string
  OwnerUserId: string
  UseYn: string
}

/**
 * Revision 매칭 결과 타입
 */
interface RevisionMatchResult {
  id: number
  issueStageId: number
  revision: string
  uploaderType: string
  uploaderId: number | null
  uploaderName: string | null
  usage: string | null
  usageType: string | null
  revisionStatus: string
  comment: string | null
  externalUploadId: string | null
  registerId: string | null
  serialNo: string | null
  registerSerialNoMax: string | null
  createdAt: Date
  updatedAt: Date
}

/**
 * 공통 Revision 매칭 함수
 * DOLCE DetailDocument와 로컬 DB를 비교하여 기존 revision을 찾음
 * 
 * 매칭 우선순위:
 * 1. registerId + OFDC_NO (OFDC_NO가 있으면 함께 사용)
 * 2. DrawingRevNo + serialNo + OFDC_NO (OFDC_NO가 있는 경우)
 * 3. DrawingRevNo + serialNo (OFDC_NO 없는 경우 fallback)
 */
export async function findMatchingRevision(
  projectId: number,
  docNumber: string,
  detailDoc: DOLCEDetailDocument,
  issueStageId?: number
): Promise<RevisionMatchResult | null> {
  let existingRevision: RevisionMatchResult | null = null

  // 1차: registerId + OFDC_NO로 조회 (OFDC_NO가 있으면 함께 사용)
  if (detailDoc.RegisterId) {
    const conditions = [eq(revisions.registerId, detailDoc.RegisterId)]
    
    // OFDC_NO가 있으면 추가 조건으로 사용
    if (detailDoc.OFDC_NO) {
      conditions.push(eq(revisions.ofdcNo, detailDoc.OFDC_NO))
    }
    
    if (issueStageId) {
      conditions.unshift(eq(revisions.issueStageId, issueStageId))
    }
    
    const results = await db.select().from(revisions).where(and(...conditions)).limit(1)
    
    if (results.length > 0) {
      existingRevision = results[0] as RevisionMatchResult
      console.log(`✅ Found revision by registerId${detailDoc.OFDC_NO ? '+OFDC_NO' : ''}: ${detailDoc.RegisterId}${detailDoc.OFDC_NO ? `/${detailDoc.OFDC_NO}` : ''} → local ID: ${existingRevision.id}`)
      return existingRevision
    } else {
      console.log(`❌ NOT found by registerId${detailDoc.OFDC_NO ? '+OFDC_NO' : ''}: ${detailDoc.RegisterId}${detailDoc.OFDC_NO ? `/${detailDoc.OFDC_NO}` : ''}`)
    }
  }

  // 2차: DrawingRevNo + serialNo + OFDC_NO로 조회 (OFDC_NO가 있는 경우만)
  if (!existingRevision && detailDoc.DrawingRevNo && detailDoc.RegisterSerialNo && detailDoc.OFDC_NO) {
    if (issueStageId) {
      const results = await db.select().from(revisions).where(
        and(
          eq(revisions.issueStageId, issueStageId),
          eq(revisions.revision, detailDoc.DrawingRevNo),
          eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)),
          eq(revisions.ofdcNo, detailDoc.OFDC_NO)
        )
      ).limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0] as RevisionMatchResult
        console.log(`✅ Found revision by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO} → local ID: ${existingRevision.id}`)
        return existingRevision
      }
    } else {
      const results = await db.select({
        id: revisions.id,
        issueStageId: revisions.issueStageId,
        revision: revisions.revision,
        uploaderType: revisions.uploaderType,
        uploaderId: revisions.uploaderId,
        uploaderName: revisions.uploaderName,
        usage: revisions.usage,
        usageType: revisions.usageType,
        revisionStatus: revisions.revisionStatus,
        comment: revisions.comment,
        externalUploadId: revisions.externalUploadId,
        registerId: revisions.registerId,
        serialNo: revisions.serialNo,
        registerSerialNoMax: revisions.registerSerialNoMax,
        createdAt: revisions.createdAt,
        updatedAt: revisions.updatedAt,
      })
        .from(revisions)
        .innerJoin(issueStages, eq(issueStages.id, revisions.issueStageId))
        .innerJoin(documents, eq(documents.id, issueStages.documentId))
        .where(
          and(
            eq(documents.projectId, projectId),
            eq(documents.docNumber, docNumber),
            eq(revisions.revision, detailDoc.DrawingRevNo),
            eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)),
            eq(revisions.ofdcNo, detailDoc.OFDC_NO)
          )
        )
        .limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0] as RevisionMatchResult
        console.log(`✅ Found revision by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO} → local ID: ${existingRevision.id}`)
        return existingRevision
      }
    }
    console.log(`❌ NOT found by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO}`)
  }

  // 3차: DrawingRevNo + serialNo로 조회 (OFDC_NO가 없는 경우 fallback)
  if (!existingRevision && detailDoc.DrawingRevNo && detailDoc.RegisterSerialNo) {
    if (issueStageId) {
      const results = await db.select().from(revisions).where(
        and(
          eq(revisions.issueStageId, issueStageId),
          eq(revisions.revision, detailDoc.DrawingRevNo),
          eq(revisions.serialNo, String(detailDoc.RegisterSerialNo))
        )
      ).limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0] as RevisionMatchResult
        console.log(`✅ Found revision by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo} → local ID: ${existingRevision.id}`)
        return existingRevision
      }
    } else {
      const results = await db.select({
        id: revisions.id,
        issueStageId: revisions.issueStageId,
        revision: revisions.revision,
        uploaderType: revisions.uploaderType,
        uploaderId: revisions.uploaderId,
        uploaderName: revisions.uploaderName,
        usage: revisions.usage,
        usageType: revisions.usageType,
        revisionStatus: revisions.revisionStatus,
        comment: revisions.comment,
        externalUploadId: revisions.externalUploadId,
        registerId: revisions.registerId,
        serialNo: revisions.serialNo,
        registerSerialNoMax: revisions.registerSerialNoMax,
        createdAt: revisions.createdAt,
        updatedAt: revisions.updatedAt,
      })
        .from(revisions)
        .innerJoin(issueStages, eq(issueStages.id, revisions.issueStageId))
        .innerJoin(documents, eq(documents.id, issueStages.documentId))
        .where(
          and(
            eq(documents.projectId, projectId),
            eq(documents.docNumber, docNumber),
            eq(revisions.revision, detailDoc.DrawingRevNo),
            eq(revisions.serialNo, String(detailDoc.RegisterSerialNo))
          )
        )
        .limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0] as RevisionMatchResult
        console.log(`✅ Found revision by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo} → local ID: ${existingRevision.id}`)
        return existingRevision
      }
    }
    console.log(`❌ NOT found by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}`)
  }
  
  // 최종 결과 로그
  if (!existingRevision) {
    console.log(`🆕 No matching revision found for RegisterId: ${detailDoc.RegisterId} (${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO || 'N/A'})`)
  }

  return existingRevision
}

class ImportService {
  private readonly DES_KEY = Buffer.from("4fkkdijg", "ascii")

  /**
   * DOLCE 시스템에서 문서 목록 가져오기
   */
  async importFromExternalSystem(
    projectId: number, // ✅ projectId
    sourceSystem: string = 'DOLCE'
  ): Promise<ImportResult> {
    try {
      console.log('\n')
      console.log('🚀'.repeat(40))
      console.log('🚀 importFromExternalSystem 호출됨!')
      console.log('🚀'.repeat(40))
      debugProcess(`DOLCE 가져오기 시작`, { projectId, sourceSystem })

      // 🔥 세션을 한 번만 가져와서 재사용
      const session = await getServerSession(authOptions)
      if (!session?.user?.companyId) {
        debugError(`세션 없음 - 인증 필요`, { projectId })
        throw new Error("인증이 필요합니다.")
      }
      const vendorId = Number(session.user.companyId)
      debugProcess(`세션 조회 완료`, { vendorId, userId: session.user.id })

      // 1. 계약 정보를 통해 프로젝트 코드와 벤더 코드 조회
      const contractInfo = await this.getContractInfoByProjectId(projectId, vendorId)
      if (!contractInfo?.projectCode || !contractInfo?.vendorCode || !contractInfo?.contractId) {
        debugError(`계약 정보 없음`, { projectId, vendorId })
        throw new Error(`Contract info not found for project ${projectId}`)
      }

      const contractId = contractInfo.contractId // contract.id를 가져옴
      
      debugProcess(`계약 정보 조회 완료`, { 
        contractId,
        projectId, 
        projectCode: contractInfo.projectCode, 
        vendorCode: contractInfo.vendorCode 
      })

      // 2. 각 drawingKind별로 데이터 조회
      const allDocuments: DOLCEDocument[] = []
      const drawingKinds = ['B3', 'B4', 'B5']

      for (const drawingKind of drawingKinds) {
        try {
          const documents = await this.fetchFromDOLCE(
            contractInfo.projectCode,
            contractInfo.vendorCode,
            drawingKind
          )
          allDocuments.push(...documents)
          debugSuccess(`${drawingKind} 문서 조회 완료`, { 
            drawingKind, 
            documentCount: documents.length 
          })
        } catch (error) {
          debugWarn(`${drawingKind} 문서 조회 실패`, { drawingKind, error })
          // 개별 drawingKind 실패는 전체 실패로 처리하지 않음
        }
      }

      if (allDocuments.length === 0) {
        debugProcess(`가져올 문서 없음`, { contractId, projectId })
        return {
          success: true,
          newCount: 0,
          updatedCount: 0,
          skippedCount: 0,
          newRevisionsCount: 0,
          updatedRevisionsCount: 0,
          newAttachmentsCount: 0,
          updatedAttachmentsCount: 0,
          downloadedFilesCount: 0,
          message: '가져올 새로운 데이터가 없습니다.'
        }
      }

      debugProcess(`전체 문서 수`, { 
        contractId,
        projectId, 
        totalDocuments: allDocuments.length,
        byDrawingKind: {
          B3: allDocuments.filter(d => d.DrawingKind === 'B3').length,
          B4: allDocuments.filter(d => d.DrawingKind === 'B4').length,
          B5: allDocuments.filter(d => d.DrawingKind === 'B5').length
        }
      })

      let newCount = 0
      let updatedCount = 0
      let skippedCount = 0
      let newRevisionsCount = 0
      let updatedRevisionsCount = 0
      let newAttachmentsCount = 0
      let updatedAttachmentsCount = 0
      let downloadedFilesCount = 0
      const errors: string[] = []

      // 3. 각 문서 동기화 처리
      for (const dolceDoc of allDocuments) {
        try {
          debugProcess(`문서 동기화 시작`, { 
            drawingNo: dolceDoc.DrawingNo, 
            drawingKind: dolceDoc.DrawingKind 
          })

          const result = await this.syncSingleDocument(contractId, projectId, vendorId, dolceDoc, sourceSystem)

          if (result === 'NEW') {
            newCount++
            // B4 문서의 경우 이슈 스테이지 자동 생성
            if (dolceDoc.DrawingKind === 'B4') {
              await this.createIssueStagesForB4Document(dolceDoc.DrawingNo, projectId, dolceDoc)
            }
            if (dolceDoc.DrawingKind === 'B3') {
              await this.createIssueStagesForB3Document(dolceDoc.DrawingNo, projectId, dolceDoc)
            }
            if (dolceDoc.DrawingKind === 'B5') {
              await this.createIssueStagesForB5Document(dolceDoc.DrawingNo, projectId, dolceDoc)
            }
          } else if (result === 'UPDATED') {
            updatedCount++
          } else {
            skippedCount++
          }

          // 4. revisions 동기화 처리
          try {
            const revisionResult = await this.syncDocumentRevisions(
              projectId, 
              dolceDoc
            )
            newRevisionsCount += revisionResult.newCount
            updatedRevisionsCount += revisionResult.updatedCount

            // 5. 파일 첨부 동기화 처리 (Category가 FS인 것만)
            console.log(`📎 첨부파일 동기화 시도: ${dolceDoc.DrawingNo} [${dolceDoc.Discipline}]`)
            const attachmentResult = await this.syncDocumentAttachments(
              dolceDoc
            )
            console.log(`📎 첨부파일 동기화 결과:`, {
              drawingNo: dolceDoc.DrawingNo,
              discipline: dolceDoc.Discipline,
              new: attachmentResult.newCount,
              updated: attachmentResult.updatedCount,
              downloaded: attachmentResult.downloadedCount
            })
            newAttachmentsCount += attachmentResult.newCount
            updatedAttachmentsCount += attachmentResult.updatedCount
            downloadedFilesCount += attachmentResult.downloadedCount

          } catch (revisionError) {
            debugWarn(`리비전 동기화 실패`, { 
              drawingNo: dolceDoc.DrawingNo, 
              error: revisionError 
            })
            // revisions 동기화 실패는 에러 로그만 남기고 계속 진행
          }

        } catch (error) {
          const errorMessage = error instanceof Error ? error.message : 'Unknown error'
          const errorStack = error instanceof Error ? error.stack : undefined
          
          debugError(`❌ 문서 동기화 실패`, { 
            drawingNo: dolceDoc.DrawingNo,
            drawingKind: dolceDoc.DrawingKind,
            discipline: dolceDoc.Discipline,
            errorMessage,
            errorStack
          })
          
          console.error(`❌ 문서 동기화 실패 상세:`, {
            문서번호: dolceDoc.DrawingNo,
            문서종류: dolceDoc.DrawingKind,
            discipline: dolceDoc.Discipline,
            에러메시지: errorMessage,
            스택: errorStack
          })
          
          errors.push(`Document ${dolceDoc.DrawingNo}: ${errorMessage}`)
          skippedCount++
        }
      }

      debugSuccess(`DOLCE 가져오기 완료`, {
        contractId,
        projectId,
        newCount,
        updatedCount,
        skippedCount,
        newRevisionsCount,
        updatedRevisionsCount,
        newAttachmentsCount,
        updatedAttachmentsCount,
        downloadedFilesCount,
        errorCount: errors.length
      })

      return {
        success: errors.length === 0,
        newCount,
        updatedCount,
        skippedCount,
        newRevisionsCount,
        updatedRevisionsCount,
        newAttachmentsCount,
        updatedAttachmentsCount,
        downloadedFilesCount,
        errors: errors.length > 0 ? errors : undefined,
        message: `가져오기 완료: 신규 ${newCount}건, 업데이트 ${updatedCount}건, 리비전 신규 ${newRevisionsCount}건, 리비전 업데이트 ${updatedRevisionsCount}건, 파일 다운로드 ${downloadedFilesCount}건`
      }

    } catch (error) {
      debugError(`DOLCE 가져오기 실패`, { projectId, error })
      throw error
    }
  }

  /**
   * 프로젝트 ID로 계약 정보 조회
   */
  private async getContractInfoByProjectId(projectId: number, vendorId: number): Promise<{
    contractId: number;  // 🔥 contract.id 반환
    projectCode: string;
    vendorCode: string;
  } | null> {

    const [result] = await db
      .select({
        contractId: contracts.id,  // 🔥 contract.id 가져오기
        projectCode: projects.code,
        vendorCode: vendors.vendorCode
      })
      .from(contracts)
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(and(
        eq(contracts.projectId, projectId), // ✅ projects.id로 조회
        eq(contracts.vendorId, vendorId)
      ))
      .limit(1)

    return result?.projectCode && result?.vendorCode
      ? { 
          contractId: result.contractId,  // 🔥 contract.id 반환
          projectCode: result.projectCode, 
          vendorCode: result.vendorCode 
        }
      : null
  }

  /**
   * DOLCE API에서 문서 목록 데이터 조회
   */
  private async fetchFromDOLCE(
    projectCode: string,
    vendorCode: string,
    drawingKind: string
  ): Promise<DOLCEDocument[]> {
    const endpoint = process.env.DOLCE_DOC_LIST_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/DwgReceiptMgmt'

    const requestBody = {
      project: projectCode,
      drawingKind: drawingKind, // B3, B4, B5
      drawingMoveGbn: "",
      drawingNo: "",
      drawingName: "",
      drawingVendor: vendorCode
    }

    console.log(`Fetching from DOLCE: ${projectCode} - ${drawingKind} = ${vendorCode}`)

    try {
      const response = await fetch(endpoint, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(requestBody)
      })

      if (!response.ok) {
        const errorText = await response.text()
        throw new Error(`DOLCE API failed: HTTP ${response.status} - ${errorText}`)
      }

      const data = await response.json()

      // DOLCE API 응답 구조에 맞게 처리
      if (data.DwgReceiptMgmtResult) {
        const result = data.DwgReceiptMgmtResult

        // drawingKind에 따라 적절한 배열에서 데이터 추출
        let documents: DOLCEDocument[] = []

        switch (drawingKind) {
          case 'B3':
            documents = result.VendorDwgList || []
            break
          case 'B4':
            documents = result.GTTDwgList || []
            break
          case 'B5':
            documents = result.FMEADwgList || []
            break
          default:
            console.warn(`Unknown drawingKind: ${drawingKind}`)
            documents = []
        }

        console.log(`Found ${documents.length} documents for ${drawingKind}`)
        return documents as DOLCEDocument[]

      } else {
        console.warn(`Unexpected DOLCE response structure:`, data)
        return []
      }

    } catch (error) {
      console.error(`DOLCE API call failed for ${projectCode}/${drawingKind}:`, error)
      throw error
    }
  }

  /**
   * DOLCE API에서 문서 상세 정보 조회 (revisions 데이터)
   */
  private async fetchDetailFromDOLCE(
    projectCode: string,
    drawingNo: string,
    discipline: string,
    drawingKind: string
  ): Promise<DOLCEDetailDocument[]> {
    const endpoint = process.env.DOLCE_DOC_DETAIL_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/DetailDwgReceiptMgmt'

    const requestBody = {
      project: projectCode,
      drawingNo: drawingNo,
      discipline: discipline,
      drawingKind: drawingKind
    }

    console.log(`Fetching detail from DOLCE: ${projectCode} - ${drawingNo}`)

    try {
      const response = await fetch(endpoint, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(requestBody)
      })

      if (!response.ok) {
        const errorText = await response.text()
        throw new Error(`DOLCE Detail API failed: HTTP ${response.status} - ${errorText}`)
      }

      const data = await response.json()

      // DOLCE Detail API 응답 구조에 맞게 처리
      if (data.DetailDwgReceiptMgmtResult) {
        const documents = data.DetailDwgReceiptMgmtResult as DOLCEDetailDocument[]
        console.log(`Found ${documents.length} detail records for ${drawingNo}`)
        return documents
      } else {
        console.warn(`Unexpected DOLCE Detail response structure:`, data)
        return []
      }

    } catch (error) {
      console.error(`DOLCE Detail API call failed for ${drawingNo}:`, error)
      throw error
    }
  }

  /**
   * DOLCE API에서 파일 정보 조회
   */
  private async fetchFileInfoFromDOLCE(uploadId: string): Promise<DOLCEFileInfo[]> {
    const endpoint = process.env.DOLCE_FILE_INFO_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/FileInfoList'

    const requestBody = {
      uploadId: uploadId
    }

    debugProcess(`DOLCE 파일 정보 조회 시작`, { uploadId, endpoint })

    try {
      const response = await fetch(endpoint, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(requestBody)
      })

      if (!response.ok) {
        const errorText = await response.text()
        debugError(`DOLCE FileInfo API 실패`, { uploadId, status: response.status, error: errorText })
        throw new Error(`DOLCE FileInfo API failed: HTTP ${response.status} - ${errorText}`)
      }

      const data = await response.json()

      // DOLCE FileInfo API 응답 구조에 맞게 처리
      if (data.FileInfoListResult) {
        const files = data.FileInfoListResult as DOLCEFileInfo[]
        const activeFiles = files.filter(f => f.UseYn === 'True')
        debugSuccess(`DOLCE 파일 정보 조회 완료`, { 
          uploadId, 
          totalFiles: files.length, 
          activeFiles: activeFiles.length 
        })
        return files
      } else {
        debugWarn(`예상치 못한 DOLCE FileInfo 응답 구조`, { uploadId, data })
        return []
      }

    } catch (error) {
      debugError(`DOLCE FileInfo API 호출 실패`, { uploadId, error })
      throw error
    }
  }

  /**
   * DES 암호화 (C# DESCryptoServiceProvider 호환)
   */
  private encryptDES(text: string): string {
    try {
      const cipher = crypto.createCipheriv('des-ecb', this.DES_KEY, '')
      cipher.setAutoPadding(true)
      let encrypted = cipher.update(text, 'utf8', 'base64')
      encrypted += cipher.final('base64')
      // + 문자를 |||로 치환
      return encrypted.replace(/\+/g, '|||')
    } catch (error) {
      console.error('DES encryption failed:', error)
      throw error
    }
  }

  /**
   * DOLCE에서 파일 다운로드
   */
  private async downloadFileFromDOLCE(
    fileId: string,
    userId: string,
    fileName: string
  ): Promise<Buffer> {
    try {
      // 암호화 문자열 생성: FileId↔UserId↔FileName
      const encryptString = `${fileId}↔${userId}↔${fileName}`
      const encryptedKey = this.encryptDES(encryptString)
      
      const downloadUrl = `${process.env.DOLCE_DOWNLOAD_URL}?key=${encryptedKey}` ||`http://60.100.99.217:1111/Download.aspx?key=${encryptedKey}`
      
      debugProcess(`DOLCE 파일 다운로드 시작`, {
        fileName,
        fileId,
        userId,
        encryptedKey,
        downloadUrl
      })

      const response = await fetch(downloadUrl, {
        method: 'GET',
        headers: {
          'User-Agent': 'DOLCE-Integration-Service'
        }
      })

      if (!response.ok) {
        debugError(`DOLCE 다운로드 실패`, {
          fileName,
          status: response.status,
          url: downloadUrl
        })
        throw new Error(`File download failed: HTTP ${response.status}`)
      }

      const buffer = Buffer.from(await response.arrayBuffer())
      debugSuccess(`DOLCE 파일 다운로드 완료`, {
        fileName,
        fileSize: buffer.length,
        fileId
      })
      
      return buffer

    } catch (error) {
      debugError(`DOLCE 파일 다운로드 실패`, { fileName, fileId, error })
      throw error
    }
  }

  /**
   * 로컬 파일 시스템에 파일 저장
   */
  private async saveFileToLocal(
    buffer: Buffer, 
    originalFileName: string
  ): Promise<{ fileName: string; filePath: string; fileSize: number }> {
    try {
      const baseDir = join(process.cwd(), "public", "documents")
      
      // 디렉토리가 없으면 생성
      await mkdir(baseDir, { recursive: true })
      
      const ext = extname(originalFileName)
      const fileName = uuidv4() + ext
      const fullPath = join(baseDir, fileName)
      const relativePath = "/documents/" + fileName
      
      await writeFile(fullPath, buffer)
      
      debugSuccess(`로컬 파일 저장 완료`, {
        originalFileName,
        savedFileName: fileName,
        filePath: relativePath,
        fileSize: buffer.length
      })
      
      return {
        fileName: originalFileName,
        filePath: relativePath,
        fileSize: buffer.length
      }

    } catch (error) {
      debugError(`로컬 파일 저장 실패`, { originalFileName, error })
      throw error
    }
  }

  /**
   * 단일 문서 동기화
   */
  private async syncSingleDocument(
    contractId: number, // 🔥 contractId 추가
    projectId: number,
    vendorId: number,
    dolceDoc: DOLCEDocument,
    sourceSystem: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {

    debugProcess(`📄 문서 동기화 처리 중`, {
      contractId,
      projectId,
      vendorId,
      drawingNo: dolceDoc.DrawingNo,
      drawingKind: dolceDoc.DrawingKind,
      discipline: dolceDoc.Discipline
    })

    // 기존 문서 조회 (문서 번호로)
    // ✅ projectId + externalDocumentId + discipline로 조회 (유니크 인덱스와 일치)
    const existingDoc = await db
      .select()
      .from(documents)
      .where(and(
        eq(documents.projectId, projectId),
        eq(documents.externalDocumentId, dolceDoc.DrawingNo), // externalDocumentId 사용
        eq(documents.discipline, dolceDoc.Discipline),
        eq(documents.externalSystemType, sourceSystem)
      ))
      .limit(1)

    debugProcess(`🔍 기존 문서 조회 결과`, {
      projectId,
      contractId,
      drawingNo: dolceDoc.DrawingNo,
      externalDocumentId: dolceDoc.DrawingNo,
      found: existingDoc.length > 0,
      existingId: existingDoc.length > 0 ? existingDoc[0].id : null
    })

    // DOLCE 문서를 DB 스키마에 맞게 변환
    const documentData = {
      contractId, // 🔥 contractId 추가 - 유니크 인덱스에 필수!
      projectId,
      vendorId,
      docNumber: dolceDoc.DrawingNo,
      title: dolceDoc.DrawingName,
      status: 'ACTIVE',

      // DOLCE 전용 필드들
      drawingKind: dolceDoc.DrawingKind,
      drawingMoveGbn: dolceDoc.DrawingMoveGbn,
      discipline: dolceDoc.Discipline,

      // 외부 시스템 정보
      externalDocumentId: dolceDoc.DrawingNo, // DOLCE에서는 DrawingNo가 ID 역할
      externalSystemType: sourceSystem,
      externalSyncedAt: new Date(),

      // B4 전용 필드들
      cGbn: dolceDoc.CGbn,
      dGbn: dolceDoc.DGbn,
      degreeGbn: dolceDoc.DegreeGbn,
      deptGbn: dolceDoc.DeptGbn,
      jGbn: dolceDoc.JGbn,
      sGbn: dolceDoc.SGbn,

      // 추가 정보
      shiDrawingNo: dolceDoc.SHIDrawingNo,
      manager: dolceDoc.Manager,
      managerENM: dolceDoc.ManagerENM,
      managerNo: dolceDoc.ManagerNo,
      registerGroup: dolceDoc.RegisterGroup,
      registerGroupId: dolceDoc.RegisterGroupId,

      // 생성자 정보
      createUserNo: dolceDoc.CreateUserNo,
      createUserId: dolceDoc.CreateUserId,
      createUserENM: dolceDoc.CreateUserENM,

      updatedAt: new Date()
    }

    if (existingDoc.length > 0) {
      // 업데이트 필요 여부 확인
      const existing = existingDoc[0]
      const hasChanges =
        existing.title !== documentData.title ||
        existing.drawingMoveGbn !== documentData.drawingMoveGbn ||
        existing.manager !== documentData.manager

      if (hasChanges) {
        debugProcess(`🔄 문서 업데이트 시작`, {
          drawingNo: dolceDoc.DrawingNo,
          existingId: existing.id,
          changes: {
            title: existing.title !== documentData.title,
            drawingMoveGbn: existing.drawingMoveGbn !== documentData.drawingMoveGbn,
            manager: existing.manager !== documentData.manager
          }
        })

        await db
          .update(documents)
          .set(documentData)
          .where(eq(documents.id, existing.id))

        debugSuccess(`✅ 문서 업데이트 완료`, {
          drawingNo: dolceDoc.DrawingNo,
          documentId: existing.id
        })
        return 'UPDATED'
      } else {
        debugProcess(`⏭️ 문서 변경사항 없음 - 스킵`, {
          drawingNo: dolceDoc.DrawingNo,
          documentId: existing.id
        })
        return 'SKIPPED'
      }
    } else {
      // 새 문서 생성
      debugProcess(`➕ 새 문서 생성 시작`, {
        drawingNo: dolceDoc.DrawingNo,
        drawingKind: dolceDoc.DrawingKind,
        title: dolceDoc.DrawingName
      })

      const [newDoc] = await db
        .insert(documents)
        .values({
          ...documentData,
          createdAt: new Date()
        })
        .returning({ id: documents.id })

      debugSuccess(`✅ 새 문서 생성 완료`, {
        drawingNo: dolceDoc.DrawingNo,
        documentId: newDoc.id,
        drawingKind: dolceDoc.DrawingKind
      })
      return 'NEW'
    }
  }

  /**
   * 문서의 revisions 동기화
   */
  private async syncDocumentRevisions(
    projectId: number,
    dolceDoc: DOLCEDocument
  ): Promise<{ newCount: number; updatedCount: number }> {
    try {
      // 1. 상세 정보 조회
      const detailDocs = await this.fetchDetailFromDOLCE(
        dolceDoc.ProjectNo,
        dolceDoc.DrawingNo,
        dolceDoc.Discipline,
        dolceDoc.DrawingKind
      )

      if (detailDocs.length === 0) {
        console.log(`No detail data found for ${dolceDoc.DrawingNo}`)
        return { newCount: 0, updatedCount: 0 }
      }

      // 2. 해당 문서의 issueStages 조회
      const documentRecord = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.projectId, projectId),
          eq(documents.docNumber, dolceDoc.DrawingNo),
          eq(documents.discipline, dolceDoc.Discipline),
        ))
        .limit(1)

      if (documentRecord.length === 0) {
        throw new Error(`Document not found: ${dolceDoc.DrawingNo}`)
      }

      const documentId = documentRecord[0].id

      const issueStagesList = await db
        .select()
        .from(issueStages)
        .where(eq(issueStages.documentId, documentId))
        .orderBy(asc(issueStages.stageOrder)) // 순서대로 정렬

      console.log(`📋 Issue Stages 목록:`, {
        drawingNo: dolceDoc.DrawingNo,
        documentId,
        totalStages: issueStagesList.length,
        stages: issueStagesList.map(s => ({
          id: s.id,
          name: s.stageName,
          order: s.stageOrder,
          status: s.stageStatus
        }))
      })

      let newCount = 0
      let updatedCount = 0

      // 3. 각 상세 데이터에 대해 revision 동기화
      for (const detailDoc of detailDocs) {
        try {
          console.log(`🔄 Revision 동기화 시도:`, {
            drawingNo: dolceDoc.DrawingNo,
            discipline: dolceDoc.Discipline,
            registerId: detailDoc.RegisterId,
            drawingRevNo: detailDoc.DrawingRevNo,
            registerSerialNo: detailDoc.RegisterSerialNo,
            registerGroupId: detailDoc.RegisterGroupId,
            registerGroup: detailDoc.RegisterGroup,
            category: detailDoc.Category,
            drawingUsage: detailDoc.DrawingUsage,
            registerKind: detailDoc.RegisterKind
          })
          
          // issueStage 매칭 로직 (여러 fallback)
          let matchingStage = null
          
          // 1. RegisterGroupId가 유효한 경우 (> 0) ID로 매칭
          if (detailDoc.RegisterGroupId > 0) {
            matchingStage = issueStagesList.find(stage => stage.id === detailDoc.RegisterGroupId)
            if (matchingStage) {
              console.log(`✅ Stage 매칭 (RegisterGroupId):`, {
                registerId: detailDoc.RegisterId,
                stageId: matchingStage.id,
                stageName: matchingStage.stageName,
                method: 'RegisterGroupId'
              })
            }
          }
          
          // 2. stageName으로 매칭 시도 (DrawingUsage 기반)
          if (!matchingStage && detailDoc.DrawingUsage) {
            const usageKeywords: Record<string, string[]> = {
              'SUB': ['제출', 'submission', 'submit', 'SUB'],
              'WOR': ['작업', 'work', 'working', 'WOR'],
              'REV': ['검토', 'review', 'REV'],
              'APP': ['승인', 'approval', 'approve', 'APP']
            }
            
            const keywords = usageKeywords[detailDoc.DrawingUsage] || []
            matchingStage = issueStagesList.find(stage => 
              keywords.some(keyword => 
                stage.stageName?.toLowerCase().includes(keyword.toLowerCase())
              )
            )
            
            if (matchingStage) {
              console.log(`✅ Stage 매칭 (DrawingUsage):`, {
                registerId: detailDoc.RegisterId,
                stageId: matchingStage.id,
                stageName: matchingStage.stageName,
                drawingUsage: detailDoc.DrawingUsage,
                method: 'DrawingUsage keyword'
              })
            }
          }
          
          // 3. Category로 매칭 시도
          if (!matchingStage && detailDoc.Category) {
            const categoryKeywords: Record<string, string[]> = {
              'FS': ['발신', 'from shi', 'outgoing'],
              'TS': ['수신', 'to shi', 'incoming']
            }
            
            const keywords = categoryKeywords[detailDoc.Category] || []
            matchingStage = issueStagesList.find(stage => 
              keywords.some(keyword => 
                stage.stageName?.toLowerCase().includes(keyword.toLowerCase())
              )
            )
            
            if (matchingStage) {
              console.log(`✅ Stage 매칭 (Category):`, {
                registerId: detailDoc.RegisterId,
                stageId: matchingStage.id,
                stageName: matchingStage.stageName,
                category: detailDoc.Category,
                method: 'Category keyword'
              })
            }
          }
          
          // 4. Fallback: stageOrder가 가장 낮은 것 (첫 번째 단계)
          if (!matchingStage && issueStagesList.length > 0) {
            matchingStage = issueStagesList[0]
            console.warn(`⚠️ Stage 매칭 실패 - Fallback 사용:`, {
              registerId: detailDoc.RegisterId,
              stageId: matchingStage.id,
              stageName: matchingStage.stageName,
              method: 'fallback (first stage)'
            })
          }

          if (!matchingStage) {
            console.warn(`⚠️ Issue Stage 없음 - Revision 생성 불가:`, {
              drawingNo: dolceDoc.DrawingNo,
              registerId: detailDoc.RegisterId,
              registerGroupId: detailDoc.RegisterGroupId,
              availableStages: issueStagesList.length
            })
            continue
          }

          const result = await this.syncSingleRevision(
            matchingStage.id, 
            detailDoc,
            projectId,
            dolceDoc.DrawingNo
          )
          
          console.log(`✅ Revision 동기화 완료:`, {
            registerId: detailDoc.RegisterId,
            result
          })
          
          if (result === 'NEW') {
            newCount++
          } else if (result === 'UPDATED') {
            updatedCount++
          }

        } catch (error) {
          console.error(`❌ Revision 동기화 실패:`, {
            drawingNo: dolceDoc.DrawingNo,
            registerId: detailDoc.RegisterId,
            error: error instanceof Error ? error.message : String(error),
            stack: error instanceof Error ? error.stack : undefined
          })
        }
      }

      return { newCount, updatedCount }

    } catch (error) {
      console.error(`Failed to sync revisions for ${dolceDoc.DrawingNo}:`, error)
      throw error
    }
  }

  /**
   * 문서의 첨부파일 동기화 (Category가 FS인 것만)
   */
  private async syncDocumentAttachments(
    dolceDoc: DOLCEDocument
  ): Promise<{ newCount: number; updatedCount: number; downloadedCount: number }> {
    try {
      debugProcess(`문서 첨부파일 동기화 시작`, {
        drawingNo: dolceDoc.DrawingNo,
        drawingKind: dolceDoc.DrawingKind,
        discipline: dolceDoc.Discipline
      })

      // 1. 상세 정보 조회
      const detailDocs = await this.fetchDetailFromDOLCE(
        dolceDoc.ProjectNo,
        dolceDoc.DrawingNo,
        dolceDoc.Discipline,
        dolceDoc.DrawingKind
      )

      // 2. Category가 'FS'인 것만 필터링
      const fsDetailDocs = detailDocs.filter(doc => doc.Category === 'FS')

      if (fsDetailDocs.length === 0) {
        debugProcess(`FS 카테고리 문서 없음`, { 
          drawingNo: dolceDoc.DrawingNo,
          discipline: dolceDoc.Discipline 
        })
        return { newCount: 0, updatedCount: 0, downloadedCount: 0 }
      }

              debugProcess(`FS 문서 발견`, { 
          drawingNo: dolceDoc.DrawingNo,
          discipline: dolceDoc.Discipline,
          totalDetails: detailDocs.length,
          fsDetails: fsDetailDocs.length 
        })

      let newCount = 0
      let updatedCount = 0
      let downloadedCount = 0

      // 3. 각 FS 문서에 대해 파일 첨부 동기화
      for (const detailDoc of fsDetailDocs) {
        try {
          if (!detailDoc.UploadId || detailDoc.UploadId.trim() === '') {
            debugProcess(`UploadId 없음`, { registerId: detailDoc.RegisterId })
            continue
          }

          // 4. 해당 revision 조회
          const revisionRecord = await db
            .select({ id: revisions.id })
            .from(revisions)
            .where(eq(revisions.registerId, detailDoc.RegisterId))
            .limit(1)

          if (revisionRecord.length === 0) {
            debugWarn(`⚠️ Revision 없음 - 첨부파일 처리 건너뜀`, { 
              drawingNo: dolceDoc.DrawingNo,
              discipline: dolceDoc.Discipline,
              registerId: detailDoc.RegisterId,
              uploadId: detailDoc.UploadId,
              drawingRevNo: detailDoc.DrawingRevNo,
              registerSerialNo: detailDoc.RegisterSerialNo,
              message: 'Revision이 DB에 없습니다. syncDocumentRevisions에서 생성 실패했을 가능성이 있습니다.'
            })
            
            // 🔍 디버깅: 파일 정보가 있는지 확인
            try {
              const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId)
              if (fileInfos.length > 0) {
                console.warn(`⚠️ Orphan 파일 발견 (Revision 없음):`, {
                  drawingNo: dolceDoc.DrawingNo,
                  discipline: dolceDoc.Discipline,
                  registerId: detailDoc.RegisterId,
                  uploadId: detailDoc.UploadId,
                  fileCount: fileInfos.length,
                  files: fileInfos.map(f => ({
                    fileId: f.FileId,
                    fileName: f.FileName,
                    fileSize: f.FileSize
                  })),
                  message: 'API에는 파일이 있지만 Revision이 없어서 처리할 수 없습니다.'
                })
              }
            } catch (error) {
              console.error(`파일 정보 조회 실패 (Revision 없음):`, error)
            }
            
            continue
          }

          const revisionId = revisionRecord[0].id

          // 5. 파일 정보 조회
          const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId)
          console.log(`📂 파일 정보 조회 완료:`, {
            drawingNo: dolceDoc.DrawingNo,
            discipline: dolceDoc.Discipline,
            uploadId: detailDoc.UploadId,
            totalFiles: fileInfos.length,
            activeFiles: fileInfos.filter(f => f.UseYn === 'True').length,
            files: fileInfos.map(f => ({
              fileName: f.FileName,
              fileSize: f.FileSize,
              fileId: f.FileId,
              useYn: f.UseYn
            }))
          })

          for (const fileInfo of fileInfos) {
            console.log(`🔍 파일 처리 시작:`, {
              drawingNo: dolceDoc.DrawingNo,
              discipline: dolceDoc.Discipline,
              fileName: fileInfo.FileName,
              fileId: fileInfo.FileId,
              useYn: fileInfo.UseYn,
              revisionId
            })
            
            if (fileInfo.UseYn !== 'True') {
              debugProcess(`비활성 파일 스킵`, { fileName: fileInfo.FileName })
              continue
            }

            try {
              const result = await this.syncSingleAttachment(
                revisionId,
                fileInfo,
                detailDoc.CreateUserId
              )

              if (result === 'NEW') {
                newCount++
                downloadedCount++
              } else if (result === 'UPDATED') {
                updatedCount++
              }
            } catch (attachmentError) {
              debugError(`⚠️ 개별 첨부파일 동기화 실패 (계속 진행)`, {
                drawingNo: dolceDoc.DrawingNo,
                discipline: dolceDoc.Discipline,
                fileName: fileInfo.FileName,
                fileId: fileInfo.FileId,
                revisionId,
                registerId: detailDoc.RegisterId,
                error: attachmentError,
                errorMessage: attachmentError instanceof Error ? attachmentError.message : String(attachmentError)
              })
              // 개별 첨부파일 실패는 전체 프로세스를 중단하지 않음
              continue
            }
          }

        } catch (error) {
          debugError(`첨부파일 동기화 실패`, { 
            drawingNo: dolceDoc.DrawingNo,
            discipline: dolceDoc.Discipline,
            registerId: detailDoc.RegisterId, 
            error 
          })
        }
      }

      debugSuccess(`문서 첨부파일 동기화 완료`, {
        drawingNo: dolceDoc.DrawingNo,
        discipline: dolceDoc.Discipline,
        newCount,
        updatedCount,
        downloadedCount
      })

      return { newCount, updatedCount, downloadedCount }

    } catch (error) {
      debugError(`문서 첨부파일 동기화 실패`, { 
        drawingNo: dolceDoc.DrawingNo,
        discipline: dolceDoc.Discipline,
        error 
      })
      throw error
    }
  }

  /**
   * 단일 첨부파일 동기화
   */
  private async syncSingleAttachment(
    revisionId: number,
    fileInfo: DOLCEFileInfo,
    userId: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
    try {
      debugProcess(`단일 첨부파일 동기화 시작`, {
        fileName: fileInfo.FileName,
        fileId: fileInfo.FileId,
        revisionId,
        userId
      })

      // 기존 첨부파일 조회 (FileId로)
      const existingAttachment = await db
        .select()
        .from(documentAttachments)
        .where(and(
          eq(documentAttachments.revisionId, revisionId),
          eq(documentAttachments.fileId, fileInfo.FileId)
        ))
        .limit(1)

      if (existingAttachment.length > 0) {
        // ✅ 변경사항 체크 (fileName, fileSize)
      const existing = existingAttachment[0]
      
      // 타입 안전 비교 (fileName은 문자열, fileSize는 숫자로 변환)
      const fileNameMatch = existing.fileName === fileInfo.FileName
      const fileSizeMatch = Number(existing.fileSize) === Number(fileInfo.FileSize)
      
      debugProcess(`첨부파일 비교`, {
        fileId: fileInfo.FileId,
        revisionId,
        fileNameMatch,
        fileSizeMatch,
        existing: { 
          fileName: existing.fileName, 
          fileSize: existing.fileSize,
          fileNameType: typeof existing.fileName,
          fileSizeType: typeof existing.fileSize
        },
        dolce: { 
          fileName: fileInfo.FileName, 
          fileSize: fileInfo.FileSize,
          fileNameType: typeof fileInfo.FileName,
          fileSizeType: typeof fileInfo.FileSize
        }
      })
      
      const hasChanges = !fileNameMatch || !fileSizeMatch

        if (hasChanges) {
          // 변경사항이 있으면 업데이트
          debugProcess(`파일 정보 변경 감지 - 업데이트`, {
            fileName: fileInfo.FileName,
            fileId: fileInfo.FileId,
            changes: {
              fileName: !fileNameMatch,
              fileSize: !fileSizeMatch
            }
          })

          await db
            .update(documentAttachments)
            .set({
              fileName: fileInfo.FileName,
              fileSize: fileInfo.FileSize,
              updatedAt: new Date()
            })
            .where(eq(documentAttachments.id, existing.id))

          debugSuccess(`첨부파일 정보 업데이트 완료`, {
            fileName: fileInfo.FileName,
            fileId: fileInfo.FileId
          })
          return 'UPDATED'
        }

        // 변경사항 없으면 SKIPPED
        debugProcess(`파일 이미 존재 - 변경사항 없음`, { 
          fileName: fileInfo.FileName, 
          fileId: fileInfo.FileId 
        })
        return 'SKIPPED'
      }

      // 파일 다운로드
      debugProcess(`📥 [1/3] 파일 다운로드 시작`, { 
        fileName: fileInfo.FileName, 
        fileId: fileInfo.FileId,
        revisionId 
      })
      
      let fileBuffer: Buffer
      try {
        fileBuffer = await this.downloadFileFromDOLCE(
          fileInfo.FileId,
          userId,
          fileInfo.FileName
        )
        debugSuccess(`✅ [1/3] 파일 다운로드 완료`, { 
          fileName: fileInfo.FileName,
          bufferSize: fileBuffer.length 
        })
      } catch (downloadError) {
        debugError(`❌ [1/3] 파일 다운로드 실패`, { 
          fileName: fileInfo.FileName,
          fileId: fileInfo.FileId,
          error: downloadError
        })
        throw downloadError
      }

      // 로컬 파일 시스템에 저장
      debugProcess(`💾 [2/3] 로컬 저장 시작`, { fileName: fileInfo.FileName })
      
      let savedFile: { filePath: string; fileSize: number }
      try {
        savedFile = await this.saveFileToLocal(fileBuffer, fileInfo.FileName)
        debugSuccess(`✅ [2/3] 로컬 저장 완료`, { 
          fileName: fileInfo.FileName,
          filePath: savedFile.filePath,
          fileSize: savedFile.fileSize
        })
      } catch (saveError) {
        debugError(`❌ [2/3] 로컬 저장 실패`, { 
          fileName: fileInfo.FileName,
          error: saveError
        })
        throw saveError
      }

      // DB에 첨부파일 정보 저장
      debugProcess(`💿 [3/3] DB Insert 시작`, { 
        fileName: fileInfo.FileName,
        revisionId,
        fileId: fileInfo.FileId
      })
      
      const attachmentData = {
        revisionId,
        fileName: fileInfo.FileName,
        filePath: savedFile.filePath,
        fileType: extname(fileInfo.FileName).slice(1).toLowerCase() || undefined,
        fileSize: fileInfo.FileSize,
        uploadId: fileInfo.UploadId,
        fileId: fileInfo.FileId,
        uploadedBy: userId,
        dolceFilePath: fileInfo.FileRelativePath,
        uploadedAt: this.convertDolceDateToDate(fileInfo.FileCreateDT),
        createdAt: new Date(),
        updatedAt: new Date()
      }

      debugProcess(`💿 [3/3] DB Insert 데이터`, attachmentData)

      try {
        const insertResult = await db
          .insert(documentAttachments)
          .values(attachmentData)
          .returning({ id: documentAttachments.id })
        
        debugSuccess(`✅ [3/3] DB Insert 완료`, {
          fileName: fileInfo.FileName,
          fileId: fileInfo.FileId,
          insertedId: insertResult[0]?.id,
          filePath: savedFile.filePath,
          fileSize: savedFile.fileSize
        })
      } catch (insertError) {
        debugError(`❌ [3/3] DB Insert 실패`, { 
          fileName: fileInfo.FileName,
          fileId: fileInfo.FileId,
          revisionId,
          error: insertError,
          errorMessage: insertError instanceof Error ? insertError.message : String(insertError),
          errorStack: insertError instanceof Error ? insertError.stack : undefined
        })
        throw insertError
      }

      debugSuccess(`🎉 새 첨부파일 생성 완료 (전체 프로세스)`, {
        fileName: fileInfo.FileName,
        fileId: fileInfo.FileId,
        filePath: savedFile.filePath,
        fileSize: savedFile.fileSize
      })
      return 'NEW'

    } catch (error) {
      debugError(`단일 첨부파일 동기화 실패`, { 
        fileName: fileInfo.FileName, 
        fileId: fileInfo.FileId, 
        error 
      })
      throw error
    }
  }

  /**
   * 단일 revision 동기화
   */
  private async syncSingleRevision(
    issueStageId: number,
    detailDoc: DOLCEDetailDocument,
    projectId: number,
    docNumber: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {

    console.log(detailDoc,"detailDoc")
    
    // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함)
    const existingRevision = await findMatchingRevision(
      projectId,
      docNumber,
      detailDoc,
      issueStageId
    )
  
    // Category에 따른 uploaderType 매핑
    const uploaderType = this.mapCategoryToUploaderType(detailDoc.Category)
    
    // RegisterKind에 따른 usage, usageType 매핑
    const { usage, usageType } = this.mapRegisterKindToUsage(detailDoc.RegisterKind)
  
    // DOLCE 상세 데이터를 revisions 스키마에 맞게 변환
    const submittedDate = this.convertDolceDateToDate(detailDoc.CreateDt)
    
    const revisionData = {
      serialNo: String(detailDoc.RegisterSerialNo),
      issueStageId,
      revision: detailDoc.DrawingRevNo,
      uploaderType,
      registerSerialNoMax: String(detailDoc.RegisterSerialNoMax),
      // uploaderName: detailDoc.CreateUserNM,
      usage,
      usageType,
      revisionStatus: detailDoc.Status,
      externalUploadId: detailDoc.UploadId,
      registerId: detailDoc.RegisterId, // 🆕 항상 최신 registerId로 업데이트
      ofdcNo: detailDoc.OFDC_NO, // 🆕 OFDC Number 추가
      comment: detailDoc.SHINote,
      submittedDate: submittedDate ? submittedDate.toISOString().split('T')[0] : null, // Date를 YYYY-MM-DD string으로 변환
      updatedAt: new Date()
    }
  
    if (existingRevision) {
      // 업데이트 필요 여부 확인 - getImportStatus와 동일한 필드 체크
      const hasChanges =
        existingRevision.comment !== revisionData.comment ||
        existingRevision.revisionStatus !== revisionData.revisionStatus

      if (hasChanges) {
        await db
          .update(revisions)
          .set(revisionData)
          .where(eq(revisions.id, existingRevision.id))

        console.log(`Updated revision: ${detailDoc.RegisterId} (local ID: ${existingRevision.id})`)
        return 'UPDATED'
      } else {
        return 'SKIPPED'
      }
    } else {
      // 새 revision 생성
      await db
        .insert(revisions)
        .values({
          ...revisionData,
          createdAt: new Date()
        })
  
      console.log(`Created new revision: ${detailDoc.RegisterId}`)
      return 'NEW'
    }
  }
  /**
   * Category를 uploaderType으로 매핑
   */
  private mapCategoryToUploaderType(category: string): string {
    switch (category) {
      case 'TS':
        return 'vendor'
      case 'FS':
        return 'shi'
      default:
        return 'vendor' // 기본값
    }
  }

  /**
   * RegisterKind를 usage/usageType으로 매핑
   */
  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
        }

      // SUB(제출용) - 도면제출 SHI >> GTT
      case 'GSUB':
        return {
          usage: 'SUB',
          usageType: null
        }
      
      default:
        console.warn(`Unknown RegisterKind: ${registerKind}`)
        return {
          usage: registerKind,
          usageType: 'DEFAULT'
        }
    }
  }

  /**
   * Status를 revisionStatus로 매핑
   */
  private mapStatusToRevisionStatus(status: string): string {
    // TODO: DOLCE의 Status 값에 맞게 매핑 로직 구현
    // 현재는 기본 매핑만 제공
    switch (status?.toUpperCase()) {
      case 'SUBMITTED':
        return 'SUBMITTED'
      case 'APPROVED':
        return 'APPROVED'
      case 'REJECTED':
        return 'REJECTED'
      default:
        return 'SUBMITTED' // 기본값
    }
  }

  private convertDolceDateToDate(dolceDate: string | undefined | null): Date | null {
    if (!dolceDate || dolceDate.trim() === '') {
      return null
    }
    
    // "20250204" 형태의 문자열을 "2025-02-04" 형태로 변환
    if (dolceDate.length === 8 && /^\d{8}$/.test(dolceDate)) {
      const year = dolceDate.substring(0, 4)
      const month = dolceDate.substring(4, 6)
      const day = dolceDate.substring(6, 8)
      
      try {
        const date = new Date(`${year}-${month}-${day}`)
        // 유효한 날짜인지 확인
        if (isNaN(date.getTime())) {
          console.warn(`Invalid date format: ${dolceDate}`)
          return null
        }
        return date
      } catch (error) {
        console.warn(`Failed to parse date: ${dolceDate}`, error)
        return null
      }
    }
    
    console.warn(`Unexpected date format: ${dolceDate}`)
    return null
  }

  /**
   * B4 문서용 이슈 스테이지 자동 생성
   */
  private async createIssueStagesForB4Document(
    drawingNo: string,
    projectId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.projectId, projectId),
          eq(documents.docNumber, drawingNo),
          eq(documents.discipline, dolceDoc.Discipline)
        ))
        .limit(1)

      if (!document) {
        throw new Error(`Document not found: ${drawingNo}`)
      }

      const documentId = document.id

      // 기존 이슈 스테이지 확인
      const existingStages = await db
        .select()
        .from(issueStages)
        .where(eq(issueStages.documentId, documentId))

      const existingStageNames = existingStages.map(stage => stage.stageName)

      // For Pre 스테이지 생성 (GTTPreDwg)
      if (!existingStageNames.includes('For Pre')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'GTT → SHI (For Pre.DWG)',
          planDate: this.convertDolceDateToDate(dolceDoc.GTTPreDwg_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.GTTPreDwg_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 1,
          priority: 'MEDIUM',
          reminderDays: 3,
          description: 'GTT 예비 도면 단계'
        })
      }

      // For Working 스테이지 생성 (GTTWorkingDwg)
      if (!existingStageNames.includes('For Work')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'GTT → SHI (For Work.DWG)',
          planDate: this.convertDolceDateToDate(dolceDoc.GTTWorkingDwg_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.GTTWorkingDwg_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 2,
          description: 'GTT 작업 도면 단계'
        })
      }

      // SHI → GTT 스테이지 생성 (GTTInput)
      if (!existingStageNames.includes('SHI → GTT')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'SHI → GTT',
          planDate: this.convertDolceDateToDate(dolceDoc.GTTInput_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.GTTInput_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 3,
          priority: 'MEDIUM',
          reminderDays: 3,
          description: 'SHI 제출 문서 단계'
        })
      }

      console.log(`Created issue stages for B4 document: ${drawingNo}`)

    } catch (error) {
      console.error(`Failed to create issue stages for ${drawingNo}:`, error)
    }
  }

  private async createIssueStagesForB3Document(
    drawingNo: string,
    projectId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.projectId, projectId),
          eq(documents.docNumber, drawingNo),
          eq(documents.discipline, dolceDoc.Discipline)
        ))
        .limit(1)

      if (!document) {
        throw new Error(`Document not found: ${drawingNo}`)
      }

      const documentId = document.id

      // 기존 이슈 스테이지 확인
      const existingStages = await db
        .select()
        .from(issueStages)
        .where(eq(issueStages.documentId, documentId))

      const existingStageNames = existingStages.map(stage => stage.stageName)

      // Approval 스테이지 생성
      if (!existingStageNames.includes('Approval')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'Vendor → SHI (For Approval)',
          planDate: this.convertDolceDateToDate(dolceDoc.AppDwg_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.AppDwg_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 1,
          description: 'Vendor 승인 도면 단계'
        })
      }

      // Working 스테이지 생성
      if (!existingStageNames.includes('Working')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'Vendor → SHI (For Working)',
          planDate: this.convertDolceDateToDate(dolceDoc.WorDwg_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.WorDwg_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 2,
          description: 'Vendor 작업 도면 단계'
        })
      }

      console.log(`Created issue stages for B3 document: ${drawingNo}`)

    } catch (error) {
      console.error(`Failed to create issue stages for ${drawingNo}:`, error)
    }
  }

  private async createIssueStagesForB5Document(
    drawingNo: string,
    projectId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.projectId, projectId),
          eq(documents.docNumber, drawingNo),
          eq(documents.discipline, dolceDoc.Discipline)
        ))
        .limit(1)

      if (!document) {
        throw new Error(`Document not found: ${drawingNo}`)
      }

      const documentId = document.id

      // 기존 이슈 스테이지 확인
      const existingStages = await db
        .select()
        .from(issueStages)
        .where(eq(issueStages.documentId, documentId))

      const existingStageNames = existingStages.map(stage => stage.stageName)

      // Approval 스테이지 생성
      if (!existingStageNames.includes('Approval')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'Vendor → SHI (For Approval)',
          planDate: this.convertDolceDateToDate(dolceDoc.FMEAFirst_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.FMEAFirst_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 1,
          description: 'FMEA 예비 도면 단계'
        })
      }

      // Working 스테이지 생성
      if (!existingStageNames.includes('Working')) {
        await db.insert(issueStages).values({
          documentId: documentId,
          stageName: 'Vendor → SHI (For Working)',
          planDate: this.convertDolceDateToDate(dolceDoc.FMEASecond_PlanDate),
          actualDate: this.convertDolceDateToDate(dolceDoc.FMEASecond_ResultDate),
          stageStatus: 'PLANNED',
          stageOrder: 2,
          description: 'FMEA 작업 도면 단계'
        })
      }

      console.log(`Created issue stages for B5 document: ${drawingNo}`)

    } catch (error) {
      console.error(`Failed to create issue stages for ${drawingNo}:`, error)
    }
  }

  /**
   * 가져오기 상태 조회
   */
 /**
 * 가져오기 상태 조회 - 에러 시 안전한 기본값 반환
 */
async getImportStatus(
  projectId: number, // ✅ projectId
  sourceSystem: string = 'DOLCE'
): Promise<ImportStatus> {
  try {
    // 세션 조회
    const session = await getServerSession(authOptions)
    if (!session?.user?.companyId) {
      console.warn(`Session not found for import status check`)
      return {
        lastImportAt: undefined,
        availableDocuments: 0,
        newDocuments: 0,
        updatedDocuments: 0,
        availableRevisions: 0,
        newRevisions: 0,
        updatedRevisions: 0,
        availableAttachments: 0,
        newAttachments: 0,
        updatedAttachments: 0,
        importEnabled: false,
        error: '세션이 없습니다. 다시 로그인해주세요.'
      }
    }
    const vendorId = Number(session.user.companyId)

    // 프로젝트 코드와 벤더 코드 조회
    const contractInfo = await this.getContractInfoByProjectId(projectId, vendorId)

    // 🔥 계약 정보가 없으면 기본 상태 반환 (에러 throw 하지 않음)
    if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
      console.warn(`Contract not found for project ${projectId}`)
      return {
        lastImportAt: undefined,
        availableDocuments: 0,
        newDocuments: 0,
        updatedDocuments: 0,
        availableRevisions: 0,
        newRevisions: 0,
        updatedRevisions: 0,
        availableAttachments: 0,
        newAttachments: 0,
        updatedAttachments: 0,
        importEnabled: false,
        error: `Project ${projectId}에 대한 계약 정보를 찾을 수 없습니다.`
      }
    }
    
    const contractId = contractInfo.contractId // 🔥 contract.id 추출

    // 마지막 가져오기 시간 조회
    const [lastImport] = await db
      .select({
        lastSynced: sql<string>`MAX(${documents.externalSyncedAt})`
      })
      .from(documents)
      .where(and(
        eq(documents.contractId, contractId), // ✅ contractId로 조회
        eq(documents.externalSystemType, sourceSystem)
      ))

    let availableDocuments = 0
    let newDocuments = 0
    let updatedDocuments = 0
    let availableRevisions = 0
    let newRevisions = 0
    let updatedRevisions = 0
    let availableAttachments = 0
    let newAttachments = 0
    let updatedAttachments = 0
    
    // 🔍 디버깅용: 새로운 attachment 상세 정보 수집
    const newAttachmentDetails: Array<{
      fileId: string
      fileName: string
      fileSize: number
      revisionId: number
      documentNo: string
      discipline: string
      revision: string
    }> = []
    
    const updatedAttachmentDetails: Array<{
      fileId: string
      fileName: string
      fileSize: number
      revisionId: number
      documentNo: string
      discipline: string
      revision: string
      changes: { fileName: boolean; fileSize: boolean }
    }> = []

    try {
      // 각 drawingKind별로 확인
      const drawingKinds = ['B3', 'B4', 'B5']

      for (const drawingKind of drawingKinds) {
        try {
          const externalDocs = await this.fetchFromDOLCE(
            contractInfo.projectCode,
            contractInfo.vendorCode,
            drawingKind
          )
          availableDocuments += externalDocs.length

          // 신규/업데이트 문서 수 계산
          for (const externalDoc of externalDocs) {
              const existing = await db
              .select({ id: documents.id, updatedAt: documents.updatedAt })
              .from(documents)
              .where(and(
                eq(documents.projectId, projectId), // ✅ projectId로 조회
                eq(documents.externalDocumentId, externalDoc.DrawingNo), // externalDocumentId 사용
                eq(documents.discipline, externalDoc.Discipline),
                eq(documents.externalSystemType, sourceSystem)
              ))
              .limit(1)

            if (existing.length === 0) {
              newDocuments++
            } else {
              // DOLCE의 CreateDt와 로컬 updatedAt 비교
              if (externalDoc.CreateDt && existing[0].updatedAt) {
                const externalModified = new Date(externalDoc.CreateDt)
                const localModified = new Date(existing[0].updatedAt)
                if (externalModified > localModified) {
                  updatedDocuments++
                }
              }
            }

            // revisions 및 attachments 상태도 확인
            try {
              const detailDocs = await this.fetchDetailFromDOLCE(
                externalDoc.ProjectNo,
                externalDoc.DrawingNo,
                externalDoc.Discipline,
                externalDoc.DrawingKind
              )
              availableRevisions += detailDocs.length

              for (const detailDoc of detailDocs) {
                // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함)
                const existingRevision = await findMatchingRevision(
                  projectId,
                  externalDoc.DrawingNo,
                  detailDoc
                )

                if (!existingRevision) {
                  // revision이 존재하지 않음 -> 신규
                  newRevisions++
                } else {
                  // 2. revision이 존재하면 변경사항이 있는지 체크
                  const hasChanges = 
                    existingRevision.comment !== detailDoc.SHINote ||
                    existingRevision.revisionStatus !== detailDoc.Status

                  if (hasChanges) {
                    // 변경사항이 있음 -> 업데이트 대상
                    updatedRevisions++
                  }
                  // 변경사항이 없으면 카운트하지 않음
                }


                // FS Category 문서의 첨부파일 확인
                if (detailDoc.Category === 'FS' && detailDoc.UploadId) {
                  try {
                    console.log(`🔍 [getImportStatus] FileInfoList 조회 시작:`, {
                      drawingNo: externalDoc.DrawingNo,
                      discipline: externalDoc.Discipline,
                      registerId: detailDoc.RegisterId,
                      uploadId: detailDoc.UploadId
                    })
                    
                    const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId)
                    
                    console.log(`🔍 [getImportStatus] FileInfoList 조회 결과:`, {
                      drawingNo: externalDoc.DrawingNo,
                      discipline: externalDoc.Discipline,
                      uploadId: detailDoc.UploadId,
                      totalFiles: fileInfos.length,
                      files: fileInfos.map(f => ({
                        fileId: f.FileId,
                        fileName: f.FileName,
                        fileSize: f.FileSize,
                        useYn: f.UseYn
                      }))
                    })
                    
                    availableAttachments += fileInfos.filter(f => f.UseYn === 'True').length

                    for (const fileInfo of fileInfos) {
                      if (fileInfo.UseYn !== 'True') continue

                      // 1. 먼저 해당 revision의 attachment가 존재하는지 확인
                      // ✅ revisionId를 찾기 위해 먼저 revision 조회
                      if (!existingRevision) {
                        // ⚠️ revision이 없으면 orphan attachment (처리 불가)
                        console.warn(`⚠️ [getImportStatus] Orphan 파일 - Revision 없음:`, {
                          drawingNo: externalDoc.DrawingNo,
                          discipline: externalDoc.Discipline,
                          registerId: detailDoc.RegisterId,
                          uploadId: detailDoc.UploadId,
                          fileId: fileInfo.FileId,
                          fileName: fileInfo.FileName,
                          fileSize: fileInfo.FileSize,
                          reason: 'Revision not found in DB - cannot process this file'
                        })
                        // ❌ 신규로 카운트하지 않음 (처리할 수 없으므로)
                        continue
                      }

                      const existingAttachment = await db
                        .select({ 
                          id: documentAttachments.id,
                          fileName: documentAttachments.fileName,
                          fileSize: documentAttachments.fileSize
                        })
                        .from(documentAttachments)
                        .where(and(
                          eq(documentAttachments.revisionId, existingRevision.id),
                          eq(documentAttachments.fileId, fileInfo.FileId)
                        ))
                        .limit(1)

                      if (existingAttachment.length === 0) {
                        // attachment가 존재하지 않음 -> 신규
                        newAttachments++
                        console.log(`✨ [getImportStatus] 신규 Attachment 감지:`, {
                          drawingNo: externalDoc.DrawingNo,
                          discipline: externalDoc.Discipline,
                          registerId: detailDoc.RegisterId,
                          uploadId: detailDoc.UploadId,
                          fileId: fileInfo.FileId,
                          fileName: fileInfo.FileName,
                          fileSize: fileInfo.FileSize,
                          revisionId: existingRevision.id
                        })
                        newAttachmentDetails.push({
                          fileId: fileInfo.FileId,
                          fileName: fileInfo.FileName,
                          fileSize: fileInfo.FileSize,
                          revisionId: existingRevision.id,
                          documentNo: externalDoc.DrawingNo,
                          discipline: externalDoc.Discipline,
                          revision: detailDoc.DrawingRevNo
                        })
                      } else {
                        // 2. attachment가 존재하면 변경사항이 있는지 체크
                        const existing = existingAttachment[0]
                        
                        // 타입 안전 비교 (fileName은 문자열, fileSize는 숫자로 변환)
                        const fileNameMatch = existing.fileName === fileInfo.FileName
                        const fileSizeMatch = Number(existing.fileSize) === Number(fileInfo.FileSize)
                        
                        if (!fileNameMatch || !fileSizeMatch) {
                          console.log(`🔍 Attachment difference detected:`, {
                            fileId: fileInfo.FileId,
                            revisionId: existingRevision.id,
                            fileNameMatch,
                            fileSizeMatch,
                            existing: { fileName: existing.fileName, fileSize: existing.fileSize, fileSizeType: typeof existing.fileSize },
                            dolce: { fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, fileSizeType: typeof fileInfo.FileSize }
                          })
                        }
                        
                        const hasChanges = !fileNameMatch || !fileSizeMatch

                        if (hasChanges) {
                          // 변경사항이 있음 -> 업데이트 대상
                          updatedAttachments++
                          updatedAttachmentDetails.push({
                            fileId: fileInfo.FileId,
                            fileName: fileInfo.FileName,
                            fileSize: fileInfo.FileSize,
                            revisionId: existingRevision.id,
                            documentNo: externalDoc.DrawingNo,
                            discipline: externalDoc.Discipline,
                            revision: detailDoc.DrawingRevNo,
                            changes: { fileName: !fileNameMatch, fileSize: !fileSizeMatch }
                          })
                        }
                        // ✅ fileId가 같고 fileName, fileSize도 같으면 변경사항 없음
                      }
                    }
                  } catch (error) {
                    console.warn(`Failed to check files for ${detailDoc.UploadId}:`, error)
                  }
                }
              }
            } catch (error) {
              console.warn(`Failed to check revisions for ${externalDoc.DrawingNo}:`, error)
            }
          }
        } catch (error) {
          console.warn(`Failed to check ${drawingKind} for status:`, error)
        }
      }
    } catch (error) {
      console.warn(`Failed to fetch external data for status: ${error}`)
      // 🔥 외부 API 호출 실패 시에도 기본값 반환
    }

    // 🔍 최종 diff 요약 출력
    console.log('\n========================================')
    console.log('📊 DOLCE 동기화 상태 검사 완료')
    console.log('========================================')
    console.log(`프로젝트 ID: ${projectId}`)
    console.log(`마지막 동기화: ${lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : '없음'}`)
    console.log('\n📄 Documents:')
    console.log(`  - 총 개수: ${availableDocuments}`)
    console.log(`  - 신규: ${newDocuments}`)
    console.log(`  - 업데이트: ${updatedDocuments}`)
    console.log('\n📝 Revisions:')
    console.log(`  - 총 개수: ${availableRevisions}`)
    console.log(`  - 신규: ${newRevisions}`)
    console.log(`  - 업데이트: ${updatedRevisions}`)
    console.log('\n📎 Attachments:')
    console.log(`  - 총 개수: ${availableAttachments}`)
    console.log(`  - 신규: ${newAttachments}`)
    console.log(`  - 업데이트: ${updatedAttachments}`)
    
    if (newAttachmentDetails.length > 0) {
      console.log('\n🆕 신규 Attachments 상세:')
      newAttachmentDetails.forEach((att, idx) => {
        console.log(`  ${idx + 1}. FileID: ${att.fileId}`)
        console.log(`     - Document: ${att.documentNo} [${att.discipline}] (Rev: ${att.revision})`)
        console.log(`     - FileName: ${att.fileName}`)
        console.log(`     - FileSize: ${att.fileSize}`)
        console.log(`     - RevisionID: ${att.revisionId}`)
      })
    }
    
    if (updatedAttachmentDetails.length > 0) {
      console.log('\n🔄 업데이트 Attachments 상세:')
      updatedAttachmentDetails.forEach((att, idx) => {
        console.log(`  ${idx + 1}. FileID: ${att.fileId}`)
        console.log(`     - Document: ${att.documentNo} [${att.discipline}] (Rev: ${att.revision})`)
        console.log(`     - FileName: ${att.fileName}`)
        console.log(`     - FileSize: ${att.fileSize}`)
        console.log(`     - RevisionID: ${att.revisionId}`)
        console.log(`     - Changes: fileName=${att.changes.fileName}, fileSize=${att.changes.fileSize}`)
      })
    }
    
    console.log('========================================\n')

    return {
      lastImportAt: lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : undefined,
      availableDocuments,
      newDocuments,
      updatedDocuments,
      availableRevisions,
      newRevisions,
      updatedRevisions,
      availableAttachments,
      newAttachments,
      updatedAttachments,
      importEnabled: this.isImportEnabled(sourceSystem)
    }

  } catch (error) {
    // 🔥 최종적으로 모든 에러를 catch하여 안전한 기본값 반환
    console.error('Failed to get import status:', error)
    return {
      lastImportAt: undefined,
      availableDocuments: 0,
      newDocuments: 0,
      updatedDocuments: 0,
      availableRevisions: 0,
      newRevisions: 0,
      updatedRevisions: 0,
      availableAttachments: 0,
      newAttachments: 0,
      updatedAttachments: 0,
      importEnabled: false,
      error: error instanceof Error ? error.message : 'Unknown error occurred'
    }
  }
}

  /**
   * 가져오기 활성화 여부 확인
   */
  private isImportEnabled(sourceSystem: string): boolean {
    const upperSystem = sourceSystem.toUpperCase()
    const enabled = process.env[`IMPORT_${upperSystem}_ENABLED`]
    return enabled === 'true' || enabled === '1'
  }

  /**
   * DOLCE 업로드 확인 테스트 (업로드 후 파일이 DOLCE에 존재하는지 확인)
   */
  async testDOLCEFileDownload(
    fileId: string,
    userId: string,
    fileName: string
  ): Promise<{ success: boolean; downloadUrl?: string; error?: string }> {
    try {
      // 암호화 문자열 생성: FileId↔UserId↔FileName
      const encryptString = `${fileId}↔${userId}↔${fileName}`
      const encryptedKey = this.encryptDES(encryptString)
      
      const downloadUrl = `${process.env.DOLCE_DOWNLOAD_URL}?key=${encryptedKey}` || `http://60.100.99.217:1111/Download.aspx?key=${encryptedKey}`
      
      console.log(`🧪 DOLCE 파일 다운로드 테스트:`)
      console.log(`   파일명: ${fileName}`)
      console.log(`   FileId: ${fileId}`)
      console.log(`   UserId: ${userId}`)
      console.log(`   암호화 키: ${encryptedKey}`)
      console.log(`   다운로드 URL: ${downloadUrl}`)

      const response = await fetch(downloadUrl, {
        method: 'GET',
        headers: {
          'User-Agent': 'DOLCE-Integration-Service'
        }
      })

      if (!response.ok) {
        console.error(`❌ DOLCE 파일 다운로드 테스트 실패: HTTP ${response.status}`)
        return {
          success: false,
          downloadUrl,
          error: `HTTP ${response.status}`
        }
      }

      const buffer = Buffer.from(await response.arrayBuffer())
      console.log(`✅ DOLCE 파일 다운로드 테스트 성공: ${fileName} (${buffer.length} bytes)`)
      
      return {
        success: true,
        downloadUrl
      }

    } catch (error) {
      console.error(`❌ DOLCE 파일 다운로드 테스트 실패: ${fileName}`, error)
      return {
        success: false,
        error: error instanceof Error ? error.message : 'Unknown error'
      }
    }
  }
}

export const importService = new ImportService()