// 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, desc, sql } 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"

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
}

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
}

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
}

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

  /**
   * DOLCE 시스템에서 문서 목록 가져오기
   */
  async importFromExternalSystem(
    projectId: number,
    sourceSystem: string = 'DOLCE'
  ): Promise<ImportResult> {
    try {
      console.log(`Starting import from ${sourceSystem} for contract ${projectId}`)

      // 1. 계약 정보를 통해 프로젝트 코드와 벤더 코드 조회
      const contractInfo = await this.getContractInfoById(projectId)
      if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
        throw new Error(`Project code or vendor code not found for contract ${projectId}`)
      }

      // 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)
          console.log(`Fetched ${documents.length} documents for ${drawingKind}`)
        } catch (error) {
          console.warn(`Failed to fetch ${drawingKind} documents:`, error)
          // 개별 drawingKind 실패는 전체 실패로 처리하지 않음
        }
      }

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

      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 {
          const result = await this.syncSingleDocument(projectId, 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, 
              sourceSystem
            )
            newRevisionsCount += revisionResult.newCount
            updatedRevisionsCount += revisionResult.updatedCount

            // 5. 파일 첨부 동기화 처리 (Category가 FS인 것만)
            const attachmentResult = await this.syncDocumentAttachments(
              dolceDoc,
              sourceSystem
            )
            newAttachmentsCount += attachmentResult.newCount
            updatedAttachmentsCount += attachmentResult.updatedCount
            downloadedFilesCount += attachmentResult.downloadedCount

          } catch (revisionError) {
            console.warn(`Failed to sync revisions for ${dolceDoc.DrawingNo}:`, revisionError)
            // revisions 동기화 실패는 에러 로그만 남기고 계속 진행
          }

        } catch (error) {
          errors.push(`Document ${dolceDoc.DrawingNo}: ${error instanceof Error ? error.message : 'Unknown error'}`)
          skippedCount++
        }
      }

      console.log(`Import completed: ${newCount} new, ${updatedCount} updated, ${skippedCount} skipped`)
      console.log(`Revisions: ${newRevisionsCount} new, ${updatedRevisionsCount} updated`)
      console.log(`Attachments: ${newAttachmentsCount} new, ${updatedAttachmentsCount} updated, ${downloadedFilesCount} downloaded`)

      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) {
      console.error('Import failed:', error)
      throw error
    }
  }

  /**
   * 계약 ID로 프로젝트 코드와 벤더 코드 조회
   */
  private async getContractInfoById(projectId: number): Promise<{
    projectCode: string;
    vendorCode: string;
  } | null> {
    const [result] = await db
      .select({
        projectCode: projects.code,
        vendorCode: vendors.vendorCode
      })
      .from(contracts)
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(eq(contracts.projectId, projectId))
      .limit(1)

    return result?.projectCode && result?.vendorCode
      ? { 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
    }

    console.log(`Fetching file info from DOLCE: ${uploadId}`)

    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 FileInfo API failed: HTTP ${response.status} - ${errorText}`)
      }

      const data = await response.json()

      // DOLCE FileInfo API 응답 구조에 맞게 처리
      if (data.FileInfoListResult) {
        const files = data.FileInfoListResult as DOLCEFileInfo[]
        console.log(`Found ${files.length} files for uploadId: ${uploadId}`)
        return files
      } else {
        console.warn(`Unexpected DOLCE FileInfo response structure:`, data)
        return []
      }

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

  /**
   * DES 암호화 (C# DESCryptoServiceProvider 호환)
   */
  private encryptDES(text: string): string {
    try {
      const cipher = crypto.createCipher('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}`
      
      console.log(`Downloading file: ${fileName} with key: ${encryptedKey.substring(0, 20)}...`)

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

      if (!response.ok) {
        throw new Error(`File download failed: HTTP ${response.status}`)
      }

      const buffer = Buffer.from(await response.arrayBuffer())
      console.log(`Downloaded ${buffer.length} bytes for ${fileName}`)
      
      return buffer

    } catch (error) {
      console.error(`Failed to download file ${fileName}:`, 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)
      
      console.log(`Saved file: ${originalFileName} as ${fileName}`)
      
      return {
        fileName: originalFileName,
        filePath: relativePath,
        fileSize: buffer.length
      }

    } catch (error) {
      console.error(`Failed to save file ${originalFileName}:`, error)
      throw error
    }
  }

  /**
   * 단일 문서 동기화
   */
  private async syncSingleDocument(
    projectId: number,
    dolceDoc: DOLCEDocument,
    sourceSystem: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
    // 기존 문서 조회 (문서 번호로)
    const existingDoc = await db
      .select()
      .from(documents)
      .where(and(
        eq(documents.projectId, projectId),
        eq(documents.docNumber, dolceDoc.DrawingNo)
      ))
      .limit(1)

    // DOLCE 문서를 DB 스키마에 맞게 변환
    const documentData = {
      projectId,
      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) {
        await db
          .update(documents)
          .set(documentData)
          .where(eq(documents.id, existing.id))

        console.log(`Updated document: ${dolceDoc.DrawingNo}`)
        return 'UPDATED'
      } else {
        return 'SKIPPED'
      }
    } else {
      // 새 문서 생성
      const [newDoc] = await db
        .insert(documents)
        .values({
          ...documentData,
          createdAt: new Date()
        })
        .returning({ id: documents.id })

      console.log(`Created new document: ${dolceDoc.DrawingNo}`)
      return 'NEW'
    }
  }

  /**
   * 문서의 revisions 동기화
   */
  private async syncDocumentRevisions(
    projectId: number,
    dolceDoc: DOLCEDocument,
    sourceSystem: string
  ): 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)
        ))
        .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))

      let newCount = 0
      let updatedCount = 0

      // 3. 각 상세 데이터에 대해 revision 동기화
      for (const detailDoc of detailDocs) {
        try {
          // RegisterGroupId로 해당하는 issueStage 찾기
          const matchingStage = issueStagesList.find(stage => {
            // RegisterGroupId와 매칭하는 로직 (추후 개선 필요)
            return stage.id // 임시로 첫 번째 stage 사용
          })

          if (!matchingStage) {
            console.warn(`No matching issue stage found for RegisterGroupId: ${detailDoc.RegisterGroupId}`)
            continue
          }

          const result = await this.syncSingleRevision(matchingStage.id, detailDoc, sourceSystem)
          if (result === 'NEW') {
            newCount++
          } else if (result === 'UPDATED') {
            updatedCount++
          }

        } catch (error) {
          console.error(`Failed to sync revision ${detailDoc.RegisterId}:`, error)
        }
      }

      return { newCount, updatedCount }

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

  /**
   * 문서의 첨부파일 동기화 (Category가 FS인 것만)
   */
  private async syncDocumentAttachments(
    dolceDoc: DOLCEDocument,
    sourceSystem: string
  ): Promise<{ newCount: number; updatedCount: number; downloadedCount: number }> {
    try {
      // 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) {
        console.log(`No FS category documents found for ${dolceDoc.DrawingNo}`)
        return { newCount: 0, updatedCount: 0, downloadedCount: 0 }
      }

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

      // 3. 각 FS 문서에 대해 파일 첨부 동기화
      for (const detailDoc of fsDetailDocs) {
        try {
          if (!detailDoc.UploadId || detailDoc.UploadId.trim() === '') {
            console.log(`No UploadId for ${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) {
            console.warn(`No revision found for RegisterId: ${detailDoc.RegisterId}`)
            continue
          }

          const revisionId = revisionRecord[0].id

          // 5. 파일 정보 조회
          const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId)

          for (const fileInfo of fileInfos) {
            if (fileInfo.UseYn !== 'Y') {
              console.log(`Skipping inactive file: ${fileInfo.FileName}`)
              continue
            }

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

            if (result === 'NEW') {
              newCount++
              downloadedCount++
            } else if (result === 'UPDATED') {
              updatedCount++
            }
          }

        } catch (error) {
          console.error(`Failed to sync attachments for ${detailDoc.RegisterId}:`, error)
        }
      }

      return { newCount, updatedCount, downloadedCount }

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

  /**
   * 단일 첨부파일 동기화
   */
  private async syncSingleAttachment(
    revisionId: number,
    fileInfo: DOLCEFileInfo,
    userId: string,
    sourceSystem: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
    try {
      // 기존 첨부파일 조회 (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) {
        // 이미 존재하는 파일인 경우, 필요시 업데이트 로직 추가
        console.log(`File already exists: ${fileInfo.FileName}`)
        return 'SKIPPED'
      }

      // 파일 다운로드
      console.log(`Downloading file: ${fileInfo.FileName}`)
      const fileBuffer = await this.downloadFileFromDOLCE(
        fileInfo.FileId,
        userId,
        fileInfo.FileName
      )

      // 로컬 파일 시스템에 저장
      const savedFile = await this.saveFileToLocal(fileBuffer, fileInfo.FileName)

      // DB에 첨부파일 정보 저장
      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()
      }

      await db
        .insert(documentAttachments)
        .values(attachmentData)

      console.log(`Created new attachment: ${fileInfo.FileName}`)
      return 'NEW'

    } catch (error) {
      console.error(`Failed to sync attachment ${fileInfo.FileName}:`, error)
      throw error
    }
  }

  /**
   * 단일 revision 동기화
   */
  private async syncSingleRevision(
    issueStageId: number,
    detailDoc: DOLCEDetailDocument,
    sourceSystem: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
    
    // 🆕 여러 조건으로 기존 revision 조회
    let existingRevision = null
  
    // 1차: registerId로 조회 (가장 정확한 매칭)
    if (detailDoc.RegisterId) {
      const results = await db
        .select()
        .from(revisions)
        .where(and(
          eq(revisions.issueStageId, issueStageId),
          eq(revisions.registerId, detailDoc.RegisterId)
        ))
        .limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0]
        console.log(`Found revision by registerId: ${detailDoc.RegisterId}`)
      }
    }
  
    // 2차: externalUploadId로 조회 (업로드했던 revision 매칭)
    if (!existingRevision && detailDoc.UploadId) {
      const results = await db
        .select()
        .from(revisions)
        .where(and(
          eq(revisions.issueStageId, issueStageId),
          eq(revisions.externalUploadId, detailDoc.UploadId)
        ))
        .limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0]
        console.log(`Found revision by externalUploadId: ${detailDoc.UploadId}`)
      }
    }
  
    // 3차: DrawingRevNo로 조회 (같은 issueStage 내에서 revision 번호 매칭)
    if (!existingRevision && detailDoc.DrawingRevNo) {
      const results = await db
        .select()
        .from(revisions)
        .where(and(
          eq(revisions.issueStageId, issueStageId),
          eq(revisions.revision, detailDoc.DrawingRevNo)
        ))
        .limit(1)
      
      if (results.length > 0) {
        existingRevision = results[0]
        console.log(`Found revision by DrawingRevNo: ${detailDoc.DrawingRevNo}`)
      }
    }
  
    // Category에 따른 uploaderType 매핑
    const uploaderType = this.mapCategoryToUploaderType(detailDoc.Category)
    
    // RegisterKind에 따른 usage, usageType 매핑
    const { usage, usageType } = this.mapRegisterKindToUsage(detailDoc.RegisterKind)
  
    // DOLCE 상세 데이터를 revisions 스키마에 맞게 변환
    const revisionData = {
      issueStageId,
      revision: detailDoc.DrawingRevNo,
      uploaderType,
      uploaderName: detailDoc.CreateUserNM,
      usage,
      usageType,
      revisionStatus: detailDoc.Status,
      externalUploadId: detailDoc.UploadId,
      registerId: detailDoc.RegisterId, // 🆕 항상 최신 registerId로 업데이트
      comment: detailDoc.RegisterDesc,
      submittedDate: this.convertDolceDateToDate(detailDoc.CreateDt),
      updatedAt: new Date()
    }
  
    if (existingRevision) {
      // 업데이트 필요 여부 확인
      const hasChanges =
        existingRevision.revision !== revisionData.revision ||
        existingRevision.revisionStatus !== revisionData.revisionStatus ||
        existingRevision.uploaderName !== revisionData.uploaderName ||
        existingRevision.registerId !== revisionData.registerId // 🆕 registerId 변경 확인
  
      if (hasChanges) {
        await db
          .update(revisions)
          .set(revisionData)
          .where(eq(revisions.id, 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
        }
      
      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)
        ))
        .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 작업 도면 단계'
        })
      }

      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)
        ))
        .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)
        ))
        .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,
  sourceSystem: string = 'DOLCE'
): Promise<ImportStatus> {
  try {
    // 마지막 가져오기 시간 조회
    const [lastImport] = await db
      .select({
        lastSynced: sql<string>`MAX(${documents.externalSyncedAt})`
      })
      .from(documents)
      .where(and(
        eq(documents.projectId, projectId),
        eq(documents.externalSystemType, sourceSystem)
      ))

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

    // 🔥 계약 정보가 없으면 기본 상태 반환 (에러 throw 하지 않음)
    if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
      console.warn(`Project code or vendor code not found for contract ${projectId}`)
      return {
        lastImportAt: lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : undefined,
        availableDocuments: 0,
        newDocuments: 0,
        updatedDocuments: 0,
        availableRevisions: 0,
        newRevisions: 0,
        updatedRevisions: 0,
        availableAttachments: 0,
        newAttachments: 0,
        updatedAttachments: 0,
        importEnabled: false, // 🔥 계약 정보가 없으면 import 비활성화
        error: `Contract ${projectId}에 대한 프로젝트 코드 또는 벤더 코드를 찾을 수 없습니다.` // 🔥 에러 메시지 추가
      }
    }

    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

    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),
                eq(documents.docNumber, externalDoc.DrawingNo)
              ))
              .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) {
                const existingRevision = await db
                  .select({ id: revisions.id })
                  .from(revisions)
                  .where(eq(revisions.registerId, detailDoc.RegisterId))
                  .limit(1)

                if (existingRevision.length === 0) {
                  newRevisions++
                } else {
                  updatedRevisions++
                }

                // FS Category 문서의 첨부파일 확인
                if (detailDoc.Category === 'FS' && detailDoc.UploadId) {
                  try {
                    const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId)
                    availableAttachments += fileInfos.filter(f => f.UseYn === 'Y').length

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

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

                      if (existingAttachment.length === 0) {
                        newAttachments++
                      } else {
                        updatedAttachments++
                      }
                    }
                  } 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 호출 실패 시에도 기본값 반환
    }

    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'
  }
}

export const importService = new ImportService()