// lib/vendor-document-list/import-service.ts - DOLCE API 연동 버전

import db from "@/db/db"
import { documents, issueStages, contracts, projects, vendors } from "@/db/schema"
import { eq, and, desc, sql } from "drizzle-orm"

export interface ImportResult {
  success: boolean
  newCount: number
  updatedCount: number
  skippedCount: number
  errors?: string[]
  message?: string
}

export interface ImportStatus {
  lastImportAt?: string
  availableDocuments: number
  newDocuments: number
  updatedDocuments: 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
}

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

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

      // 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,
          message: '가져올 새로운 데이터가 없습니다.'
        }
      }

      let newCount = 0
      let updatedCount = 0
      let skippedCount = 0
      const errors: string[] = []

      // 3. 각 문서 동기화 처리
      for (const dolceDoc of allDocuments) {
        try {
          const result = await this.syncSingleDocument(contractId, dolceDoc, sourceSystem)

          if (result === 'NEW') {
            newCount++
            // B4 문서의 경우 이슈 스테이지 자동 생성
            if (dolceDoc.DrawingKind === 'B4') {
              await this.createIssueStagesForB4Document(dolceDoc.DrawingNo, contractId, dolceDoc)
            }

            if (dolceDoc.DrawingKind === 'B3') {
              await this.createIssueStagesForB3Document(dolceDoc.DrawingNo, contractId, dolceDoc)
            }


            if (dolceDoc.DrawingKind === 'B5') {
              await this.createIssueStagesForB5Document(dolceDoc.DrawingNo, contractId, dolceDoc)
            }
          } else if (result === 'UPDATED') {
            updatedCount++
          } else {
            skippedCount++
          }

        } 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`)

      return {
        success: errors.length === 0,
        newCount,
        updatedCount,
        skippedCount,
        errors: errors.length > 0 ? errors : undefined,
        message: `가져오기 완료: 신규 ${newCount}건, 업데이트 ${updatedCount}건`
      }

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

  /**
   * 계약 ID로 프로젝트 코드와 벤더 코드 조회
   */
  private async getContractInfoById(contractId: 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.id, contractId))
      .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',
          // DOLCE API에 특별한 인증이 필요하다면 여기에 추가
        },
        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} in ${drawingKind === 'B3' ? 'VendorDwgList' : drawingKind === 'B4' ? 'GTTDwgList' : 'FMEADwgList'}`)
        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
    }
  }
  /**
   * 단일 문서 동기화
   */
  private async syncSingleDocument(
    contractId: number,
    dolceDoc: DOLCEDocument,
    sourceSystem: string
  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
    // 기존 문서 조회 (문서 번호로)
    const existingDoc = await db
      .select()
      .from(documents)
      .where(and(
        eq(documents.contractId, contractId),
        eq(documents.docNumber, dolceDoc.DrawingNo)
      ))
      .limit(1)

    // DOLCE 문서를 DB 스키마에 맞게 변환
    const documentData = {
      contractId,
      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'
    }
  }

  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,
    contractId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.contractId, contractId),
          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,
    contractId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.contractId, contractId),
          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('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 승인 도면 단계'
        })
      }

      // For Working 스테이지 생성 (GTTWorkingDwg)
      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 B4 document: ${drawingNo}`)

    } catch (error) {
      console.error(`Failed to create issue stages for ${drawingNo}:`, error)
      // 이슈 스테이지 생성 실패는 전체 문서 생성을 막지 않음
    }
  }

  private async createIssueStagesForB5Document(
    drawingNo: string,
    contractId: number,
    dolceDoc: DOLCEDocument
  ): Promise<void> {
    try {
      // 문서 ID 조회
      const [document] = await db
        .select({ id: documents.id })
        .from(documents)
        .where(and(
          eq(documents.contractId, contractId),
          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('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 예비 도면 단계'
        })
      }

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

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

    } catch (error) {
      console.error(`Failed to create issue stages for ${drawingNo}:`, error)
      // 이슈 스테이지 생성 실패는 전체 문서 생성을 막지 않음
    }
  }


  /**
   * 가져오기 상태 조회
   */
  async getImportStatus(
    contractId: number,
    sourceSystem: string = 'DOLCE'
  ): Promise<ImportStatus> {
    try {
      // 마지막 가져오기 시간 조회
      const [lastImport] = await db
        .select({
          lastSynced: sql<string>`MAX(${documents.externalSyncedAt})`
        })
        .from(documents)
        .where(and(
          eq(documents.contractId, contractId),
          eq(documents.externalSystemType, sourceSystem)
        ))

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

      console.log(contractInfo, "contractInfo")

      if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
        throw new Error(`Project code or vendor code not found for contract ${contractId}`)
      }

      let availableDocuments = 0
      let newDocuments = 0
      let updatedDocuments = 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.contractId, contractId),
                  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++
                  }
                }
              }
            }
          } catch (error) {
            console.warn(`Failed to check ${drawingKind} for status:`, error)
          }
        }
      } catch (error) {
        console.warn(`Failed to fetch external data for status: ${error}`)
      }

      return {
        lastImportAt: lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : undefined,
        availableDocuments,
        newDocuments,
        updatedDocuments,
        importEnabled: this.isImportEnabled(sourceSystem)
      }

    } catch (error) {
      console.error('Failed to get import status:', error)
      throw error
    }
  }

  /**
   * 가져오기 활성화 여부 확인
   */
  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()