path: root/lib/vendor-document-list/plant/shi-buyer-system-api.ts

// app/lib/shi-buyer-system-api.ts
import db from "@/db/db"
import { stageDocuments, stageIssueStages, contracts, vendors, projects, stageSubmissions, stageSubmissionAttachments } from "@/db/schema"
import { eq, and, sql, ne } from "drizzle-orm"
import fs from 'fs/promises'
import path from 'path'

interface ShiDocumentInfo {
  PROJ_NO: string
  SHI_DOC_NO: string
  CATEGORY: string
  RESPONSIBLE_CD: string
  RESPONSIBLE: string
  VNDR_CD: string
  VNDR_NM: string
  DSN_SKL: string
  MIFP_CD: string
  MIFP_NM: string
  CG_EMPNO1: string
  CG_EMPNM1: string
  OWN_DOC_NO: string
  DSC: string
  DOC_CLASS: string
  COMMENT: string
  STATUS: string
  CRTER: string
  CRTE_DTM: string
  CHGR: string
  CHG_DTM: string
}

interface ShiScheduleInfo {
  PROJ_NO: string
  SHI_DOC_NO: string
  DDPKIND: string
  SCHEDULE_TYPE: string
  BASELINE1: string | null
  REVISED1: string | null
  FORECAST1: string | null
  ACTUAL1: string | null
  BASELINE2: string | null
  REVISED2: string | null
  FORECAST2: string | null
  ACTUAL2: string | null
  CRTER: string
  CRTE_DTM: string
  CHGR: string
  CHG_DTM: string
}

// SHI API 응답 타입
interface ShiDocumentResponse {
  PROJ_NO: string
  SHI_DOC_NO: string
  STATUS: string
  COMMENT: string | null
  CATEGORY?: string
  RESPONSIBLE_CD?: string
  RESPONSIBLE?: string
  VNDR_CD?: string
  VNDR_NM?: string
  DSN_SKL?: string
  MIFP_CD?: string
  MIFP_NM?: string
  CG_EMPNO1?: string
  CG_EMPNM1?: string
  OWN_DOC_NO?: string
  DSC?: string
  DOC_CLASS?: string
  CRTER?: string
  CRTE_DTM?: string
  CHGR?: string
  CHG_DTM?: string
}

interface ShiApiResponse {
  GetDwgInfoResult: ShiDocumentResponse[]
}

// InBox 파일 정보 인터페이스 추가
interface InBoxFileInfo {
  PROJ_NO: string
  SHI_DOC_NO: string
  STAGE_NAME: string
  REVISION_NO: string
  VNDR_CD: string
  VNDR_NM: string
  FILE_NAME: string
  FILE_SIZE: number
  CONTENT_TYPE: string
  UPLOAD_DATE: string
  UPLOADED_BY: string
  STATUS: string
  COMMENT: string
}

// SaveInBoxList API 응답 인터페이스
interface SaveInBoxListResponse {
  SaveInBoxListResult: {
    success: boolean
    message: string
    processedCount?: number
    files?: Array<{
      fileName: string
      networkPath: string
      status: string
    }>
  }
}

export class ShiBuyerSystemAPI {
  private baseUrl = process.env.SWP_BASE_URL || 'http://60.100.99.217/DDP/Services/VNDRService.svc'
  private ddcUrl = process.env.DDC_BASE_URL || 'http://60.100.99.217/DDC/Services/WebService.svc'
  private localStoragePath = process.env.NAS_PATH || './uploads'

  async sendToSHI(contractId: number) {
    try {
      // 1. 전송할 문서 조회
      const documents = await this.getDocumentsToSend(contractId)
      
      if (documents.length === 0) {
        return { success: false, message: "전송할 문서가 없습니다." }
      }

      // 2. 도서 정보 전송
      await this.sendDocumentInfo(documents)
      
      // 3. 스케줄 정보 전송
      await this.sendScheduleInfo(documents)
      
      // 4. 동기화 상태 업데이트
      await this.updateSyncStatus(documents.map(d => d.documentId))
      
      return { 
        success: true, 
        message: `${documents.length}개 문서가 성공적으로 전송되었습니다.`,
        count: documents.length
      }
    } catch (error) {
      console.error("SHI 전송 오류:", error)
      
      // 에러 시 동기화 상태 업데이트
      await this.updateSyncError(
        contractId, 
        error instanceof Error ? error.message : "알 수 없는 오류"
      )
      
      throw error
    }
  }

  private async getDocumentsToSend(contractId: number) {
    const result = await db
      .select({
        documentId: stageDocuments.id,
        docNumber: stageDocuments.docNumber,
        vendorDocNumber: stageDocuments.vendorDocNumber,
        title: stageDocuments.title,
        status: stageDocuments.status,
        projectCode: sql<string>`(SELECT code FROM projects WHERE id = ${stageDocuments.projectId})`,
        vendorCode: sql<string>`(SELECT vendor_code FROM vendors WHERE id = ${stageDocuments.vendorId})`,
        vendorName: sql<string>`(SELECT vendor_name FROM vendors WHERE id = ${stageDocuments.vendorId})`,
        stages: sql<any[]>`
          COALESCE(
            (SELECT json_agg(row_to_json(s.*))
             FROM stage_issue_stages s
             WHERE s.document_id = ${stageDocuments.id}
             ORDER BY s.stage_order),
            '[]'::json
          )
        `
      })
      .from(stageDocuments)
      .where(
        and(
          eq(stageDocuments.contractId, contractId),
          eq(stageDocuments.status, 'ACTIVE'),
          ne(stageDocuments.buyerSystemStatus, "승인(DC)")
        )
      )
    
    return result
  }

  private async sendDocumentInfo(documents: any[]) {
    const shiDocuments: ShiDocumentInfo[] = documents.map(doc => ({
      PROJ_NO: doc.projectCode,
      SHI_DOC_NO: doc.docNumber,
      CATEGORY: "SHIP",
      RESPONSIBLE_CD: "EVCP",
      RESPONSIBLE: "eVCP System",
      VNDR_CD: doc.vendorCode || "",
      VNDR_NM: doc.vendorName || "",
      DSN_SKL: "B3",
      MIFP_CD: "",
      MIFP_NM: "",
      CG_EMPNO1: "",
      CG_EMPNM1: "",
      OWN_DOC_NO: doc.vendorDocNumber || doc.docNumber,
      DSC: doc.title,
      DOC_CLASS: "B3",
      COMMENT: "",
      STATUS: "ACTIVE",
      CRTER: "EVCP_SYSTEM",
      CRTE_DTM: new Date().toISOString(),
      CHGR: "EVCP_SYSTEM",
      CHG_DTM: new Date().toISOString()
    }))

    const response = await fetch(`${this.baseUrl}/SetDwgInfo`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(shiDocuments)
    })

    if (!response.ok) {
      const errorText = await response.text()
      throw new Error(`도서 정보 전송 실패: ${response.statusText} - ${errorText}`)
    }

    return response.json()
  }

  private async sendScheduleInfo(documents: any[]) {
    const schedules: ShiScheduleInfo[] = []
    
    for (const doc of documents) {
      for (const stage of doc.stages) {
        if (stage.plan_date) {
          schedules.push({
            PROJ_NO: doc.projectCode,
            SHI_DOC_NO: doc.docNumber,
            DDPKIND: "V",
            SCHEDULE_TYPE: stage.stage_name,
            BASELINE1: stage.plan_date ? new Date(stage.plan_date).toISOString() : null,
            REVISED1: null,
            FORECAST1: null,
            ACTUAL1: stage.actual_date ? new Date(stage.actual_date).toISOString() : null,
            BASELINE2: null,
            REVISED2: null,
            FORECAST2: null,
            ACTUAL2: null,
            CRTER: "EVCP_SYSTEM",
            CRTE_DTM: new Date().toISOString(),
            CHGR: "EVCP_SYSTEM",
            CHG_DTM: new Date().toISOString()
          })
        }
      }
    }

    if (schedules.length === 0) {
      console.log("전송할 스케줄 정보가 없습니다.")
      return
    }

    const response = await fetch(`${this.baseUrl}/SetScheduleInfo`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(schedules)
    })

    if (!response.ok) {
      const errorText = await response.text()
      throw new Error(`스케줄 정보 전송 실패: ${response.statusText} - ${errorText}`)
    }

    return response.json()
  }

  private async updateSyncStatus(documentIds: number[]) {
    if (documentIds.length === 0) return

    await db
      .update(stageDocuments)
      .set({
        syncStatus: 'synced',
        lastSyncedAt: new Date(),
        syncError: null,
        syncVersion: sql`sync_version + 1`,
        lastModifiedBy: 'EVCP'
      })
      .where(sql`id = ANY(${documentIds})`)
  }

  private async updateSyncError(contractId: number, errorMessage: string) {
    await db
      .update(stageDocuments)
      .set({
        syncStatus: 'error',
        syncError: errorMessage,
        lastModifiedBy: 'EVCP'
      })
      .where(
        and(
          eq(stageDocuments.contractId, contractId),
          eq(stageDocuments.status, 'ACTIVE')
        )
      )
  }
  
  async pullDocumentStatus(contractId: number) {
    try {
      const contract = await db.query.contracts.findFirst({
        where: eq(contracts.id, contractId),
      });

      if (!contract) {
        throw new Error(`계약을 찾을 수 없습니다: ${contractId}`)
      }

      const project = await db.query.projects.findFirst({
        where: eq(projects.id, contract.projectId),
      });

      if (!project) {
        throw new Error(`프로젝트를 찾을 수 없습니다: ${contract.projectId}`)
      }

      const vendor = await db.query.vendors.findFirst({
        where: eq(vendors.id, contract.vendorId),
      });

      if (!vendor) {
        throw new Error(`벤더를 찾을 수 없습니다: ${contract.vendorId}`)
      }

      const shiDocuments = await this.fetchDocumentsFromSHI(project.code, {
        VNDR_CD: vendor.vendorCode
      })

      if (!shiDocuments || shiDocuments.length === 0) {
        return { 
          success: true, 
          message: "동기화할 문서가 없습니다.",
          updatedCount: 0,
          documents: []
        }
      }

      const updateResults = await this.updateLocalDocuments(project.code, shiDocuments)

      return {
        success: true,
        message: `${updateResults.updatedCount}개 문서의 상태가 업데이트되었습니다.`,
        updatedCount: updateResults.updatedCount,
        newCount: updateResults.newCount,
        documents: updateResults.documents
      }
    } catch (error) {
      console.error("문서 상태 풀링 오류:", error)
      throw error
    }
  }

  private async fetchDocumentsFromSHI(
    projectCode: string, 
    filters?: {
      SHI_DOC_NO?: string
      CATEGORY?: string
      VNDR_CD?: string
      RESPONSIBLE_CD?: string
      STATUS?: string
      DOC_CLASS?: string
      CRTE_DTM_FROM?: string
      CRTE_DTM_TO?: string
      CHG_DTM_FROM?: string
      CHG_DTM_TO?: string
    }
  ): Promise<ShiDocumentResponse[]> {
    const params = new URLSearchParams({ PROJ_NO: projectCode })
    
    if (filters) {
      Object.entries(filters).forEach(([key, value]) => {
        if (value) params.append(key, value)
      })
    }

    const url = `${this.baseUrl}/GetDwgInfo?${params.toString()}`
    
    const response = await fetch(url, {
      method: 'GET',
      headers: {
        'Accept': 'application/json'
      }
    })

    if (!response.ok) {
      throw new Error(`문서 조회 실패: ${response.statusText}`)
    }

    const data: ShiApiResponse = await response.json()
    
    return data.GetDwgInfoResult || []
  }

  private async updateLocalDocuments(
    projectCode: string, 
    shiDocuments: ShiDocumentResponse[]
  ) {
    let updatedCount = 0
    let newCount = 0
    const updatedDocuments: any[] = []

    const project = await db.query.projects.findFirst({
      where: eq(projects.code, projectCode)
    })

    if (!project) {
      throw new Error(`프로젝트를 찾을 수 없습니다: ${projectCode}`)
    }

    for (const shiDoc of shiDocuments) {
      const localDoc = await db.query.stageDocuments.findFirst({
        where: and(
          eq(stageDocuments.projectId, project.id),
          eq(stageDocuments.docNumber, shiDoc.SHI_DOC_NO)
        )
      })

      if (localDoc) {
        if (
          localDoc.buyerSystemStatus !== shiDoc.STATUS ||
          localDoc.buyerSystemComment !== shiDoc.COMMENT
        ) {
          await db
            .update(stageDocuments)
            .set({
              buyerSystemStatus: shiDoc.STATUS,
              buyerSystemComment: shiDoc.COMMENT,
              lastSyncedAt: new Date(),
              syncStatus: 'synced',
              syncError: null,
              lastModifiedBy: 'BUYER_SYSTEM',
              syncVersion: sql`sync_version + 1`
            })
            .where(eq(stageDocuments.id, localDoc.id))

          updatedCount++
          updatedDocuments.push({
            docNumber: shiDoc.SHI_DOC_NO,
            title: shiDoc.DSC || localDoc.title,
            status: shiDoc.STATUS,
            comment: shiDoc.COMMENT,
            action: 'updated'
          })
        }
      } else {
        console.log(`SHI에만 존재하는 문서: ${shiDoc.SHI_DOC_NO}`)
        newCount++
        updatedDocuments.push({
          docNumber: shiDoc.SHI_DOC_NO,
          title: shiDoc.DSC || 'N/A',
          status: shiDoc.STATUS,
          comment: shiDoc.COMMENT,
          action: 'new_in_shi'
        })
      }
    }

    return {
      updatedCount,
      newCount,
      documents: updatedDocuments
    }
  }

  async getSyncStatus(contractId: number) {
    const documents = await db
      .select({
        docNumber: stageDocuments.docNumber,
        title: stageDocuments.title,
        syncStatus: stageDocuments.syncStatus,
        lastSyncedAt: stageDocuments.lastSyncedAt,
        syncError: stageDocuments.syncError,
        buyerSystemStatus: stageDocuments.buyerSystemStatus,
        buyerSystemComment: stageDocuments.buyerSystemComment
      })
      .from(stageDocuments)
      .where(eq(stageDocuments.contractId, contractId))
    
    return documents
  }

  /**
   * 스테이지 제출 건들의 파일을 SHI 구매자 시스템으로 동기화
   * @param submissionIds 제출 ID 배열
   */
  async syncSubmissionsToSHI(submissionIds: number[]) {
    const results = {
      totalCount: submissionIds.length,
      successCount: 0,
      failedCount: 0,
      details: [] as any[]
    }

    for (const submissionId of submissionIds) {
      try {
        const result = await this.syncSingleSubmission(submissionId)
        if (result.success) {
          results.successCount++
        } else {
          results.failedCount++
        }
        results.details.push(result)
      } catch (error) {
        results.failedCount++
        results.details.push({
          submissionId,
          success: false,
          error: error instanceof Error ? error.message : "Unknown error"
        })
      }
    }

    return results
  }

  /**
   * 단일 제출 건 동기화
   */
  private async syncSingleSubmission(submissionId: number) {
    try {
      // 1. 제출 정보 조회 (프로젝트, 문서, 스테이지, 파일 정보 포함)
      const submissionInfo = await this.getSubmissionFullInfo(submissionId)
      
      if (!submissionInfo) {
        throw new Error(`제출 정보를 찾을 수 없습니다: ${submissionId}`)
      }

      // 2. 동기화 시작 상태 업데이트
      await this.updateSubmissionSyncStatus(submissionId, 'syncing')

      // 3. 첨부파일들과 실제 파일 내용을 준비
      const filesWithContent = await this.prepareFilesWithContent(submissionInfo)
      
      if (filesWithContent.length === 0) {
        await this.updateSubmissionSyncStatus(submissionId, 'synced', '전송할 파일이 없습니다')
        return {
          submissionId,
          success: true,
          message: "전송할 파일이 없습니다"
        }
      }

      // 4. SaveInBoxList API 호출하여 네트워크 경로 받기
      const response = await this.sendToInBox(filesWithContent)

      // 5. 응답받은 네트워크 경로에 파일 저장
      if (response.SaveInBoxListResult.success && response.SaveInBoxListResult.files) {
        await this.saveFilesToNetworkPaths(filesWithContent, response.SaveInBoxListResult.files)
        
        // 6. 동기화 결과 업데이트
        await this.updateSubmissionSyncStatus(submissionId, 'synced', null, {
          syncedFilesCount: filesWithContent.length,
          buyerSystemStatus: 'SYNCED'
        })

        // 개별 파일 상태 업데이트
        await this.updateAttachmentsSyncStatus(
          submissionInfo.attachments.map(a => a.id),
          'synced'
        )

        return {
          submissionId,
          success: true,
          message: response.SaveInBoxListResult.message,
          syncedFiles: filesWithContent.length
        }
      } else {
        throw new Error(response.SaveInBoxListResult.message)
      }
    } catch (error) {
      await this.updateSubmissionSyncStatus(
        submissionId, 
        'failed', 
        error instanceof Error ? error.message : '알 수 없는 오류'
      )
      
      throw error
    }
  }

  /**
   * 제출 정보 조회 (관련 정보 포함)
   */
  private async getSubmissionFullInfo(submissionId: number) {
    const result = await db
      .select({
        submission: stageSubmissions,
        stage: stageIssueStages,
        document: stageDocuments,
        project: projects,
        vendor: vendors
      })
      .from(stageSubmissions)
      .innerJoin(stageIssueStages, eq(stageSubmissions.stageId, stageIssueStages.id))
      .innerJoin(stageDocuments, eq(stageSubmissions.documentId, stageDocuments.id))
      .innerJoin(projects, eq(stageDocuments.projectId, projects.id))
      .leftJoin(vendors, eq(stageDocuments.vendorId, vendors.id))
      .where(eq(stageSubmissions.id, submissionId))
      .limit(1)

    if (result.length === 0) return null

    // 첨부파일 조회 - 파일 경로 포함
    const attachments = await db
      .select()
      .from(stageSubmissionAttachments)
      .where(
        and(
          eq(stageSubmissionAttachments.submissionId, submissionId),
          eq(stageSubmissionAttachments.status, 'ACTIVE')
        )
      )

    return {
      ...result[0],
      attachments
    }
  }

  /**
   * 파일 내용과 함께 InBox 파일 정보 준비
   */
  private async prepareFilesWithContent(submissionInfo: any): Promise<Array<InBoxFileInfo & { fileBuffer: Buffer, attachment: any }>> {
    const filesWithContent: Array<InBoxFileInfo & { fileBuffer: Buffer, attachment: any }> = []
    
    for (const attachment of submissionInfo.attachments) {
      try {
        // 파일 경로 결정 (storagePath 또는 storageUrl 사용)
        const filePath = attachment.storagePath || attachment.storageUrl
        
        if (!filePath) {
          console.warn(`첨부파일 ${attachment.id}의 경로를 찾을 수 없습니다.`)
          continue
        }

        // 전체 경로 생성
        const fullPath = path.isAbsolute(filePath) 
          ? filePath 
          : path.join(this.localStoragePath, filePath)

        // 파일 읽기
        const fileBuffer = await fs.readFile(fullPath)
        
        // 파일 정보 생성
        const fileInfo: InBoxFileInfo & { fileBuffer: Buffer, attachment: any } = {
          PROJ_NO: submissionInfo.project.code,
          SHI_DOC_NO: submissionInfo.document.docNumber,
          STAGE_NAME: submissionInfo.stage.stageName,
          REVISION_NO: String(submissionInfo.submission.revisionNumber),
          VNDR_CD: submissionInfo.vendor?.vendorCode || '',
          VNDR_NM: submissionInfo.vendor?.vendorName || '',
          FILE_NAME: attachment.fileName,
          FILE_SIZE: fileBuffer.length, // 실제 파일 크기 사용
          CONTENT_TYPE: attachment.mimeType || 'application/octet-stream',
          UPLOAD_DATE: new Date().toISOString(),
          UPLOADED_BY: submissionInfo.submission.submittedBy,
          STATUS: 'PENDING',
          COMMENT: `Revision ${submissionInfo.submission.revisionNumber} - ${submissionInfo.stage.stageName}`,
          fileBuffer: fileBuffer,
          attachment: attachment
        }
        
        filesWithContent.push(fileInfo)
      } catch (error) {
        console.error(`파일 읽기 실패: ${attachment.fileName}`, error)
        // 파일 읽기 실패 시 계속 진행
        continue
      }
    }

    return filesWithContent
  }

  /**
   * SaveInBoxList API 호출 (파일 메타데이터만 전송)
   */
  private async sendToInBox(files: Array<InBoxFileInfo & { fileBuffer: Buffer }>): Promise<SaveInBoxListResponse> {
    // fileBuffer를 제외한 메타데이터만 전송
    const fileMetadata = files.map(({ fileBuffer, attachment, ...metadata }) => metadata)
    
    const request = { files: fileMetadata }
    
    const response = await fetch(`${this.ddcUrl}/SaveInBoxList`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Accept': 'application/json'
      },
      body: JSON.stringify(request)
    })

    if (!response.ok) {
      const errorText = await response.text()
      throw new Error(`InBox 전송 실패: ${response.statusText} - ${errorText}`)
    }

    const data = await response.json()
    
    // 응답 구조 확인 및 처리
    if (!data.SaveInBoxListResult) {
      return {
        SaveInBoxListResult: {
          success: true,
          message: "전송 완료",
          processedCount: files.length,
          files: files.map(f => ({
            fileName: f.FILE_NAME,
            networkPath: `\\\\network\\share\\${f.PROJ_NO}\\${f.SHI_DOC_NO}\\${f.FILE_NAME}`,
            status: 'READY'
          }))
        }
      }
    }
    
    return data
  }

  /**
   * 네트워크 경로에 파일 저장
   */
  private async saveFilesToNetworkPaths(
    filesWithContent: Array<InBoxFileInfo & { fileBuffer: Buffer, attachment: any }>,
    networkPathInfo: Array<{ fileName: string, networkPath: string, status: string }>
  ) {
    for (const fileInfo of filesWithContent) {
      const pathInfo = networkPathInfo.find(p => p.fileName === fileInfo.FILE_NAME)
      
      if (!pathInfo || !pathInfo.networkPath) {
        console.error(`네트워크 경로를 찾을 수 없습니다: ${fileInfo.FILE_NAME}`)
        continue
      }

      try {
        // 네트워크 경로에 파일 저장
        // Windows 네트워크 경로인 경우 처리
        let targetPath = pathInfo.networkPath
        
        // Windows 네트워크 경로를 Node.js가 이해할 수 있는 형식으로 변환
        if (process.platform === 'win32' && targetPath.startsWith('\\\\')) {
          // 그대로 사용
        } else if (process.platform !== 'win32' && targetPath.startsWith('\\\\')) {
          // Linux/Mac에서는 SMB 마운트 경로로 변환 필요
          // 예: \\\\server\\share -> /mnt/server/share
          targetPath = targetPath.replace(/\\\\/g, '/mnt/').replace(/\\/g, '/')
        }

        // 디렉토리 생성 (없는 경우)
        const targetDir = path.dirname(targetPath)
        await fs.mkdir(targetDir, { recursive: true })
        
        // 파일 저장
        await fs.writeFile(targetPath, fileInfo.fileBuffer)
        
        console.log(`파일 저장 완료: ${fileInfo.FILE_NAME} -> ${targetPath}`)
        
        // DB에 네트워크 경로 업데이트
        await db
          .update(stageSubmissionAttachments)
          .set({
            buyerSystemUrl: pathInfo.networkPath,
            buyerSystemStatus: 'UPLOADED',
            lastModifiedBy: 'EVCP'
          })
          .where(eq(stageSubmissionAttachments.id, fileInfo.attachment.id))
          
      } catch (error) {
        console.error(`파일 저장 실패: ${fileInfo.FILE_NAME}`, error)
        // 개별 파일 실패는 전체 프로세스를 중단하지 않음
      }
    }
  }

  /**
   * 제출 동기화 상태 업데이트
   */
  private async updateSubmissionSyncStatus(
    submissionId: number,
    status: string,
    error?: string | null,
    additionalData?: any
  ) {
    const updateData: any = {
      syncStatus: status,
      lastSyncedAt: new Date(),
      syncError: error,
      lastModifiedBy: 'EVCP',
      ...additionalData
    }

    if (status === 'failed') {
      updateData.syncRetryCount = sql`sync_retry_count + 1`
      updateData.nextRetryAt = new Date(Date.now() + 30 * 60 * 1000) // 30분 후 재시도
    }

    await db
      .update(stageSubmissions)
      .set(updateData)
      .where(eq(stageSubmissions.id, submissionId))
  }

  /**
   * 첨부파일 동기화 상태 업데이트
   */
  private async updateAttachmentsSyncStatus(
    attachmentIds: number[],
    status: string
  ) {
    if (attachmentIds.length === 0) return

    await db
      .update(stageSubmissionAttachments)
      .set({
        syncStatus: status,
        syncCompletedAt: status === 'synced' ? new Date() : null,
        buyerSystemStatus: status === 'synced' ? 'UPLOADED' : 'PENDING',
        lastModifiedBy: 'EVCP'
      })
      .where(sql`id = ANY(${attachmentIds})`)
  }

  /**
   * 동기화 재시도 (실패한 건들)
   */
  async retrySyncFailedSubmissions(contractId?: number) {
    const conditions = [
      eq(stageSubmissions.syncStatus, 'failed'),
      sql`next_retry_at <= NOW()`
    ]
    
    if (contractId) {
      const documentIds = await db
        .select({ id: stageDocuments.id })
        .from(stageDocuments)
        .where(eq(stageDocuments.contractId, contractId))
      
      if (documentIds.length > 0) {
        conditions.push(
          sql`document_id = ANY(${documentIds.map(d => d.id)})`
        )
      }
    }

    const failedSubmissions = await db
      .select({ id: stageSubmissions.id })
      .from(stageSubmissions)
      .where(and(...conditions))
      .limit(10) // 한 번에 최대 10개씩 재시도

    if (failedSubmissions.length === 0) {
      return { 
        success: true, 
        message: "재시도할 제출 건이 없습니다.",
        retryCount: 0
      }
    }

    const submissionIds = failedSubmissions.map(s => s.id)
    const results = await this.syncSubmissionsToSHI(submissionIds)

    return {
      success: true,
      message: `${results.successCount}/${results.totalCount}개 제출 건 재시도 완료`,
      ...results
    }
  }
}