// lib/vendor-document-list/dolce-upload-service.ts
import db from "@/db/db"
import { documents, revisions, documentAttachments, contracts, projects, vendors, issueStages } from "@/db/schema"
import { eq, and, desc, sql, inArray, min } from "drizzle-orm"
import { v4 as uuidv4 } from "uuid"

export interface DOLCEUploadResult {
  success: boolean
  uploadedDocuments: number
  uploadedFiles: number
  errors?: string[]
  results?: {
    documentResults?: any[]
    fileResults?: any[]
    mappingResults?: any[]
  }
}

interface DOLCEDocument {
  Mode: "ADD" | "MOD"
  Status: string
  RegisterId: number
  ProjectNo: string
  Discipline: string
  DrawingKind: string
  DrawingNo: string
  DrawingName: string
  RegisterGroupId: number
  RegisterSerialNo: number
  RegisterKind: string
  DrawingRevNo: string
  Category: string
  Receiver: string | null
  Manager: string
  RegisterDesc: string
  UploadId?: string
  RegCompanyCode: string
}

interface DOLCEFileMapping {
  CGbn?: string
  Category?: string
  CheckBox: string
  DGbn?: string
  DegreeGbn?: string
  DeptGbn?: string
  Discipline: string
  DrawingKind: string
  DrawingMoveGbn: string
  DrawingName: string
  DrawingNo: string
  DrawingUsage: string
  FileNm: string
  JGbn?: string
  Manager: string
  MappingYN: string
  NewOrNot: string
  ProjectNo: string
  RegisterGroup: number
  RegisterGroupId: number
  RegisterKindCode: string
  RegisterSerialNo: number
  RevNo?: string
  SGbn?: string
  UploadId: string
}

class DOLCEUploadService {
  private readonly BASE_URL = process.env.DOLCE_API_URL || 'http://60.100.99.217:1111'
  private readonly UPLOAD_SERVICE_URL = process.env.DOLCE_UPLOAD_URL || 'http://60.100.99.217:1111/PWPUploadService.ashx'

  /**
   * 메인 업로드 함수: 변경된 문서와 파일을 DOLCE로 업로드
   */
  async uploadToDoLCE(
    contractId: number,
    revisionIds: number[],
    userId: string,
    userName?: string
  ): Promise<DOLCEUploadResult> {
    try {
      console.log(`Starting DOLCE upload for contract ${contractId}, revisions: ${revisionIds.join(', ')}`)

      // 1. 계약 정보 조회 (프로젝트 코드, 벤더 코드 등)
      const contractInfo = await this.getContractInfo(contractId)
      if (!contractInfo) {
        throw new Error(`Contract info not found for ID: ${contractId}`)
      }

      // 2. 업로드할 리비전 정보 조회
      const revisionsToUpload = await this.getRevisionsForUpload(revisionIds)
      if (revisionsToUpload.length === 0) {
        return {
          success: true,
          uploadedDocuments: 0,
          uploadedFiles: 0
        }
      }

      // 3. 각 issueStageId별로 첫 번째 revision 정보를 미리 조회 (Mode 결정용)

      let uploadedDocuments = 0
      let uploadedFiles = 0
      const errors: string[] = []
      const results: any = {
        documentResults: [],
        fileResults: [],
        mappingResults: []
      }

      // 4. 각 리비전별로 처리
      // 4. 각 리비전별로 처리
      for (const revision of revisionsToUpload) {
        try {
          console.log(`Processing revision ${revision.revision} for document ${revision.documentNo}`)

          // 4-1. 파일이 있는 경우 먼저 업로드
          let uploadId: string | undefined
          if (revision.attachments && revision.attachments.length > 0) {
            // ✅ userId를 uploadFiles 메서드에 전달
            const fileUploadResults = await this.uploadFiles(revision.attachments, userId)

            if (fileUploadResults.length > 0) {
              uploadId = fileUploadResults[0].uploadId // 첫 번째 파일의 UploadId 사용
              uploadedFiles += fileUploadResults.length
              results.fileResults.push(...fileUploadResults)
            }
          }

          // 4-2. 문서 정보 업로드
          const dolceDoc = this.transformToDoLCEDocument(
            revision,
            contractInfo,
            uploadId,
            contractInfo.vendorCode,
          )

          const docResult = await this.uploadDocument([dolceDoc], userId)
          if (docResult.success) {
            uploadedDocuments++
            results.documentResults.push(docResult)

            // 4-3. 파일이 있는 경우 매핑 정보 전송
            if (uploadId && revision.attachments && revision.attachments.length > 0) {
              const mappingData = this.transformToFileMapping(
                revision,
                contractInfo,
                uploadId,
                revision.attachments[0].fileName
              )

              const mappingResult = await this.uploadFileMapping([mappingData], userId)
              if (mappingResult.success) {
                results.mappingResults.push(mappingResult)
              } else {
                errors.push(`File mapping failed for ${revision.documentNo}: ${mappingResult.error}`)
              }
            }

            // 4-4. 성공한 리비전의 상태 업데이트
            await this.updateRevisionStatus(revision.id, 'SUBMITTED', uploadId)

          } else {
            errors.push(`Document upload failed for ${revision.documentNo}: ${docResult.error}`)
          }

        } catch (error) {
          const errorMessage = `Failed to process revision ${revision.revision}: ${error instanceof Error ? error.message : 'Unknown error'}`
          errors.push(errorMessage)
          console.error(errorMessage, error)
        }
      }
      return {
        success: errors.length === 0,
        uploadedDocuments,
        uploadedFiles,
        errors: errors.length > 0 ? errors : undefined,
        results
      }

    } catch (error) {
      console.error('DOLCE upload failed:', error)
      throw error
    }
  }

  /**
   * 계약 정보 조회
   */
  private async getContractInfo(contractId: number) {
    const [result] = await db
      .select({
        projectCode: projects.code,
        vendorCode: vendors.vendorCode,
        contractNo: contracts.contractNo
      })
      .from(contracts)
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(eq(contracts.id, contractId))
      .limit(1)

    return result
  }

  /**
   * 각 issueStageId별로 첫 번째 revision 정보를 조회
   */
  private async getFirstRevisionMap(issueStageIds: number[]): Promise<Map<number, string>> {
    const firstRevisions = await db
      .select({
        issueStageId: revisions.issueStageId,
        firstRevision: min(revisions.revision)
      })
      .from(revisions)
      .where(inArray(revisions.issueStageId, issueStageIds))
      .groupBy(revisions.issueStageId)

    const map = new Map<number, string>()
    firstRevisions.forEach(item => {
      if (item.firstRevision) {
        map.set(item.issueStageId, item.firstRevision)
      }
    })

    return map
  }

  /**
   * 업로드할 리비전 정보 조회 (문서 정보 및 첨부파일 포함)
   */
  private async getRevisionsForUpload(revisionIds: number[]) {
    // revisions → issueStages → documents 순서로 join하여 정보 조회
    const revisionResults = await db
      .select({
        // revision 테이블 정보
        id: revisions.id,
        registerId:revisions.registerId, 
        revision: revisions.revision, // revisionNo가 아니라 revision
        revisionStatus: revisions.revisionStatus,
        uploaderId: revisions.uploaderId,
        uploaderName: revisions.uploaderName,
        submittedDate: revisions.submittedDate,
        comment: revisions.comment,
        usage: revisions.comment,

        // ✅ DOLCE 연동 필드들 (새로 추가)
        externalUploadId: revisions.externalUploadId,
        externalRegisterId: revisions.id,
        externalSentAt: revisions.submittedDate,

        // issueStages 테이블 정보
        issueStageId: issueStages.id,
        stageName: issueStages.stageName,
        documentId: issueStages.documentId,

        // documents 테이블 정보 (DOLCE 업로드에 필요한 모든 필드)
        documentNo: documents.docNumber,
        documentName: documents.title,
        drawingKind: documents.drawingKind,
        drawingMoveGbn: documents.drawingMoveGbn,
        discipline: documents.discipline,
        registerGroupId: documents.registerGroupId,

        // DOLCE B4 전용 필드들
        cGbn: documents.cGbn,
        dGbn: documents.dGbn,
        degreeGbn: documents.degreeGbn,
        deptGbn: documents.deptGbn,
        jGbn: documents.jGbn,
        sGbn: documents.sGbn,

        // DOLCE 추가 정보
        manager: documents.manager,
        managerENM: documents.managerENM,
        managerNo: documents.managerNo,
        shiDrawingNo: documents.shiDrawingNo,

        // 외부 시스템 연동 정보
        externalDocumentId: documents.externalDocumentId,
        externalSystemType: documents.externalSystemType,
        externalSyncedAt: documents.externalSyncedAt
      })
      .from(revisions)
      .innerJoin(issueStages, eq(revisions.issueStageId, issueStages.id))
      .innerJoin(documents, eq(issueStages.documentId, documents.id))
      .where(inArray(revisions.id, revisionIds))

    // 각 리비전의 첨부파일 정보도 조회
    const revisionsWithAttachments = []
    for (const revision of revisionResults) {
      const attachments = await db
        .select({
          id: documentAttachments.id,
          uploadId: documentAttachments.uploadId,
          fileId: documentAttachments.fileId,
          fileName: documentAttachments.fileName,
          filePath: documentAttachments.filePath,
          fileType: documentAttachments.fileType,
          fileSize: documentAttachments.fileSize,
          createdAt: documentAttachments.createdAt
        })
        .from(documentAttachments)
        .where(eq(documentAttachments.revisionId, revision.id))

      revisionsWithAttachments.push({
        ...revision,
        attachments
      })
    }

    return revisionsWithAttachments
  }

  /**
   * 파일 업로드 (PWPUploadService.ashx)
   */
  /**
   * 파일 업로드 (PWPUploadService.ashx) - DB 업데이트 포함
   */
  private async uploadFiles(
    attachments: any[],
    userId: string
  ): Promise<Array<{ uploadId: string, fileId: string, filePath: string }>> {
    const uploadResults = []

    for (const attachment of attachments) {
      try {
        // UploadId와 FileId 생성 (UUID 형태)
        const uploadId = uuidv4()
        const fileId = uuidv4()

        // 파일 데이터 읽기 (실제 구현에서는 파일 시스템이나 S3에서 읽어옴)
        const fileBuffer = await this.getFileBuffer(attachment.filePath)

        const uploadUrl = `${this.UPLOAD_SERVICE_URL}?UploadId=${uploadId}&FileId=${fileId}`

        const response = await fetch(uploadUrl, {
          method: 'POST',
          headers: {
            'Content-Type': 'application/octet-stream',
          },
          body: fileBuffer
        })

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

        const dolceFilePath = await response.text() // DOLCE에서 반환하는 파일 경로

        // ✅ 업로드 성공 후 documentAttachments 테이블 업데이트
        await db
          .update(documentAttachments)
          .set({
            uploadId: uploadId,
            fileId: fileId,
            uploadedBy: userId,
            dolceFilePath: dolceFilePath,
            uploadedAt: new Date(),
            updatedAt: new Date()
          })
          .where(eq(documentAttachments.id, attachment.id))

        uploadResults.push({
          uploadId,
          fileId,
          filePath: dolceFilePath
        })

        console.log(`✅ File uploaded successfully: ${attachment.fileName} -> ${dolceFilePath}`)
        console.log(`✅ DB updated for attachment ID: ${attachment.id}`)

      } catch (error) {
        console.error(`❌ File upload failed for ${attachment.fileName}:`, error)
        throw error
      }
    }

    return uploadResults
  }

  /**
   * 문서 정보 업로드 (DetailDwgReceiptMgmtEdit)
   */
  private async uploadDocument(dwgList: DOLCEDocument[], userId: string): Promise<{ success: boolean, error?: string, data?: any }> {
    try {
      const endpoint = `${this.BASE_URL}/Services/VDCSWebService.svc/DetailDwgReceiptMgmtEdit`

      const requestBody = {
        DwgList: dwgList,
        UserID: userId
      }

      console.log('Uploading documents to DOLCE:', JSON.stringify(requestBody, null, 2))

      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(`HTTP ${response.status} - ${errorText}`)
      }

      const result = await response.json()

      return {
        success: true,
        data: result
      }

    } catch (error) {
      return {
        success: false,
        error: error instanceof Error ? error.message : 'Unknown error'
      }
    }
  }

  /**
   * 파일 매핑 정보 업로드 (MatchBatchFileDwgEdit)
   */
  private async uploadFileMapping(mappingList: DOLCEFileMapping[], userId: string): Promise<{ success: boolean, error?: string, data?: any }> {
    try {
      const endpoint = `${this.BASE_URL}/Services/VDCSWebService.svc/MatchBatchFileDwgEdit`

      const requestBody = {
        mappingSaveLists: mappingList,
        UserID: userId
      }

      console.log('Uploading file mapping to DOLCE:', JSON.stringify(requestBody, null, 2))

      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(`HTTP ${response.status} - ${errorText}`)
      }

      const result = await response.json()

      return {
        success: true,
        data: result
      }

    } catch (error) {
      return {
        success: false,
        error: error instanceof Error ? error.message : 'Unknown error'
      }
    }
  }

  /**
   * 리비전 데이터를 DOLCE 문서 형태로 변환 (업데이트된 스키마 사용)
   */
  private transformToDoLCEDocument(
    revision: any,
    contractInfo: any,
    uploadId?: string,
    vendorCode?: string,
  ): DOLCEDocument {
    // Mode 결정: 해당 issueStageId의 첫 번째 revision인지 확인
    let mode: "ADD" | "MOD" = "MOD" // 기본값은 MOD\
    

    if(revision.registerId){
      mode = "MOD"
    } else{
       mode = "ADD"
    }

    // RegisterKind 결정: stageName에 따라 설정
    let registerKind = "APPC" // 기본값

    if (revision.stageName) {
      const stageNameLower = revision.stageName.toLowerCase()

      if (revision.drawingKind === "B4") {
        // B4: 기존 로직
        if (stageNameLower.includes("pre")) {
          registerKind = "RECP"
        } else if (stageNameLower.includes("working")) {
          registerKind = "RECW"
        }
      } else if (revision.drawingKind === "B5") {
        // B5: FMEA 관련
        if (stageNameLower.includes("pre")) {
          registerKind = "FMEA-R1"
        } else if (stageNameLower.includes("working")) {
          registerKind = "FMEA-R2"
        }
      } else if (revision.drawingKind === "B3") {
        // B3: WORK/APPC
        if (stageNameLower.includes("work") && revision.usage.includes('Partial')) {
          registerKind = "WORK-P"
        } else if (stageNameLower.includes("work") && revision.usage.includes('Full')) {
          registerKind = "WORK"
        } else if (stageNameLower.includes("approval") && revision.usage.includes('Partial')) {
          registerKind = "APPC-P"
        }
        else if (stageNameLower.includes("approval") && revision.usage.includes('Full')) {
          registerKind = "APPC"
        }
      }
    }

    const getSerialNumber = (revisionValue: string): number => {
      // 먼저 숫자인지 확인
      const numericValue = parseInt(revisionValue)
      if (!isNaN(numericValue)) {
        return numericValue
      }

      // 문자인 경우 (a=1, b=2, c=3, ...)
      if (typeof revisionValue === 'string' && revisionValue.length === 1) {
        const charCode = revisionValue.toLowerCase().charCodeAt(0)
        if (charCode >= 97 && charCode <= 122) { // a-z
          return charCode - 96 // a=1, b=2, c=3, ...
        }
      }

      // 기본값
      return 1
    }


    return {
      Mode: mode,
      Status: revision.revisionStatus || "Standby",
      RegisterId: revision.externalRegisterId, // 업데이트된 필드 사용
      ProjectNo: contractInfo.projectCode,
      Discipline: revision.discipline,
      DrawingKind: revision.drawingKind,
      DrawingNo: revision.documentNo,
      DrawingName: revision.documentName,
      RegisterGroupId: revision.registerGroupId || 0,
      RegisterSerialNo: getSerialNumber(revision.revision || "1"),
      RegisterKind: registerKind, // stageName에 따라 동적 설정
      DrawingRevNo: revision.revision || "-",
      Category: revision.category || "TS",
      Receiver: null,
      Manager: revision.managerNo || "202206", // 담당자 번호 사용
      RegisterDesc: revision.comment || "System upload",
      UploadId: uploadId,
      RegCompanyCode: vendorCode || "A0005531" // 벤더 코드
    }
  }
  /**
   * 파일 매핑 데이터 변환
   */
  private transformToFileMapping(
    revision: any,
    contractInfo: any,
    uploadId: string,
    fileName: string
  ): DOLCEFileMapping {
    return {
      CGbn: revision.cGbn,
      Category: revision.category,
      CheckBox: "0",
      DGbn: revision.dGbn,
      DegreeGbn: revision.degreeGbn,
      DeptGbn: revision.deptGbn,
      Discipline: revision.discipline || "DL",
      DrawingKind: revision.drawingKind || "B4",
      DrawingMoveGbn: revision.drawingMoveGbn || "도면입수",
      DrawingName: revision.documentName,
      DrawingNo: revision.documentNo,
      DrawingUsage: "입수용",
      FileNm: fileName,
      JGbn: revision.jGbn,
      Manager: revision.managerNo || "970043",
      MappingYN: "Y",
      NewOrNot: "N",
      ProjectNo: contractInfo.projectCode,
      RegisterGroup: 0,
      RegisterGroupId: revision.registerGroupId || 0,
      RegisterKindCode: "RECW",
      RegisterSerialNo: parseInt(revision.revision) || 1,
      RevNo: revision.revision,
      SGbn: revision.sGbn,
      UploadId: uploadId
    }
  }

  /**
   * 파일 버퍼 읽기 (실제 파일 시스템 기반) - 타입 에러 수정
   */
  private async getFileBuffer(filePath: string): Promise<ArrayBuffer> {
    try {
      console.log(`Reading file from path: ${filePath}`)

      if (filePath.startsWith('http')) {
        // URL인 경우 직접 다운로드
        const response = await fetch(filePath)
        if (!response.ok) {
          throw new Error(`Failed to download file: ${response.status}`)
        }
        return await response.arrayBuffer()
      } else {
        // 로컬 파일 경로인 경우
        const fs = await import('fs')
        const path = await import('path')

        let actualFilePath: string

        if (filePath.startsWith('/documents/')) {
          // DB에 저장된 경로 형태: "/documents/[uuid].ext"
          // 실제 파일 시스템 경로로 변환: "public/documents/[uuid].ext"
          actualFilePath = path.join(process.cwd(), 'public', filePath)
        } else if (filePath.startsWith('/')) {
          // 절대 경로인 경우 public 디렉토리 기준으로 변환
          actualFilePath = path.join(process.cwd(), 'public', filePath)
        } else {
          // 상대 경로인 경우 그대로 사용
          actualFilePath = filePath
        }

        // 파일 존재 여부 확인
        if (!fs.existsSync(actualFilePath)) {
          throw new Error(`File not found: ${actualFilePath}`)
        }

        // 파일 읽기
        const fileBuffer = fs.readFileSync(actualFilePath)
        console.log(`✅ File read successfully: ${actualFilePath} (${fileBuffer.length} bytes)`)

        // Buffer를 ArrayBuffer로 변환 (타입 안전성 보장)
        return new ArrayBuffer(fileBuffer.length).slice(0).constructor(fileBuffer)
      }
    } catch (error) {
      console.error(`❌ Failed to read file: ${filePath}`, error)
      throw error
    }
  }

  /**
   * 리비전 상태 업데이트 (업데이트된 스키마 사용)
   */
  private async updateRevisionStatus(revisionId: number, status: string, uploadId?: string) {
    const updateData: any = {
      revisionStatus: status,
      updatedAt: new Date()
    }

    // 업로드 성공 시 관련 날짜 설정
    if (status === 'SUBMITTED') {
      updateData.submittedDate = new Date().toISOString().slice(0, 10)
      //   updateData.externalSentAt = new Date().toISOString().slice(0, 10)
    } else if (status === 'APPROVED') {
      updateData.approvedDate = new Date().toISOString().slice(0, 10)
    }

    // DOLCE 업로드 ID 저장
    if (uploadId) {
      updateData.externalUploadId = uploadId
    }

    await db
      .update(revisions)
      .set(updateData)
      .where(eq(revisions.id, revisionId))

    console.log(`✅ Updated revision ${revisionId} status to ${status}${uploadId ? ` with upload ID: ${uploadId}` : ''}`)
  }

  /**
   * 업로드 가능 여부 확인
   */
  isUploadEnabled(): boolean {
    const enabled = process.env.DOLCE_UPLOAD_ENABLED
    return enabled === 'true' || enabled === '1'
  }
}

export const dolceUploadService = new DOLCEUploadService()

// 편의 함수
export async function uploadRevisionsToDOLCE(
  contractId: number,
  revisionIds: number[],
  userId: string,
  userName?: string
): Promise<DOLCEUploadResult> {
  return dolceUploadService.uploadToDoLCE(contractId, revisionIds, userId, userName)
}