// @/lib/esg-check-list/excel-actions.ts
"use server"

import db  from "@/db/db"
import { 
  esgEvaluations, 
  esgEvaluationItems, 
  esgAnswerOptions 
} from "@/db/schema"
import { eq, inArray } from "drizzle-orm"
import type { ParsedExcelData } from "./excel-utils"

export interface ImportOptions {
  skipDuplicates?: boolean // 중복 시 스킵
  updateExisting?: boolean // 기존 데이터 업데이트
}

export interface ImportResult {
  success: boolean
  message: string
  details: {
    evaluationsCreated: number
    evaluationsUpdated: number
    evaluationsSkipped: number
    itemsCreated: number
    optionsCreated: number
    errors: string[]
  }
}

export async function importEsgDataFromExcel(
  data: ParsedExcelData,
  options: ImportOptions = {}
): Promise<ImportResult> {
  const result: ImportResult = {
    success: false,
    message: '',
    details: {
      evaluationsCreated: 0,
      evaluationsUpdated: 0,
      evaluationsSkipped: 0,
      itemsCreated: 0,
      optionsCreated: 0,
      errors: []
    }
  }

  try {
    await db.transaction(async (tx) => {
      // 1. 기존 평가표 확인
      const existingSerials = data.evaluations.map(e => e.serialNumber)
      const existingEvaluations = await tx
        .select()
        .from(esgEvaluations)
        .where(inArray(esgEvaluations.serialNumber, existingSerials))

      const existingSerialNumbers = existingEvaluations.map(e => e.serialNumber)

      // 2. 평가표 처리
      for (const evaluation of data.evaluations) {
        const exists = existingSerialNumbers.includes(evaluation.serialNumber)

        if (exists) {
          if (options.skipDuplicates) {
            result.details.evaluationsSkipped++
            continue
          } else if (options.updateExisting) {
            // 기존 데이터 업데이트
            await tx
              .update(esgEvaluations)
              .set({
                category: evaluation.category,
                inspectionItem: evaluation.inspectionItem,
                updatedAt: new Date(),
              })
              .where(eq(esgEvaluations.serialNumber, evaluation.serialNumber))

            // 기존 평가항목과 답변옵션 삭제
            const existingEvaluation = existingEvaluations.find(e => e.serialNumber === evaluation.serialNumber)
            if (existingEvaluation) {
              await tx
                .delete(esgEvaluationItems)
                .where(eq(esgEvaluationItems.esgEvaluationId, existingEvaluation.id))
              // 답변옵션은 CASCADE DELETE로 자동 삭제됨
            }

            result.details.evaluationsUpdated++
          } else {
            result.details.errors.push(`시리얼번호 '${evaluation.serialNumber}'가 이미 존재합니다.`)
            continue
          }
        } else {
          // 새 평가표 생성
          await tx
            .insert(esgEvaluations)
            .values({
              serialNumber: evaluation.serialNumber,
              category: evaluation.category,
              inspectionItem: evaluation.inspectionItem,
            })

          result.details.evaluationsCreated++
        }
      }

      // 3. 최신 평가표 목록 다시 조회 (새로 생성되거나 업데이트된 것들 포함)
      const currentEvaluations = await tx
        .select()
        .from(esgEvaluations)
        .where(inArray(esgEvaluations.serialNumber, existingSerials))

      const evaluationMap = new Map(
        currentEvaluations.map(e => [e.serialNumber, e.id])
      )

      // 4. 평가항목 처리
      const itemsToInsert = []
      for (const item of data.evaluationItems) {
        const evaluationId = evaluationMap.get(item.serialNumber)
        
        if (!evaluationId) {
          result.details.errors.push(
            `평가항목의 시리얼번호 '${item.serialNumber}'에 해당하는 평가표를 찾을 수 없습니다.`
          )
          continue
        }

        itemsToInsert.push({
          esgEvaluationId: evaluationId,
          evaluationItem: item.evaluationItem,
          evaluationItemDescription: item.evaluationItemDescription,
          orderIndex: item.orderIndex,
        })
      }

      if (itemsToInsert.length > 0) {
        const insertedItems = await tx
          .insert(esgEvaluationItems)
          .values(itemsToInsert)
          .returning()

        result.details.itemsCreated = insertedItems.length

        // 5. 답변옵션 처리
        const itemMap = new Map()
        for (const insertedItem of insertedItems) {
          const originalItem = itemsToInsert.find(
            item => item.esgEvaluationId === insertedItem.esgEvaluationId && 
                    item.evaluationItem === insertedItem.evaluationItem
          )
          if (originalItem) {
            const evaluation = currentEvaluations.find(e => e.id === originalItem.esgEvaluationId)
            if (evaluation) {
              const key = `${evaluation.serialNumber}:${originalItem.evaluationItem}`
              itemMap.set(key, insertedItem.id)
            }
          }
        }

        const optionsToInsert = []
        for (const option of data.answerOptions) {
          const key = `${option.serialNumber}:${option.evaluationItem}`
          const itemId = itemMap.get(key)
          
          if (!itemId) {
            result.details.errors.push(
              `답변옵션의 평가항목 '${option.evaluationItem}'을 찾을 수 없습니다.`
            )
            continue
          }

          optionsToInsert.push({
            esgEvaluationItemId: itemId,
            answerText: option.answerText,
            score: option.score.toString(),
            orderIndex: option.orderIndex,
          })
        }

        if (optionsToInsert.length > 0) {
          const insertedOptions = await tx
            .insert(esgAnswerOptions)
            .values(optionsToInsert)
            .returning()

          result.details.optionsCreated = insertedOptions.length
        }
      }
    })

    // 결과 메시지 생성
    const { details } = result
    const totalProcessed = details.evaluationsCreated + details.evaluationsUpdated + details.evaluationsSkipped

    if (details.errors.length === 0) {
      result.success = true
      result.message = `성공적으로 처리되었습니다. 평가표 ${totalProcessed}개 (생성: ${details.evaluationsCreated}, 업데이트: ${details.evaluationsUpdated}, 스킵: ${details.evaluationsSkipped}), 평가항목 ${details.itemsCreated}개, 답변옵션 ${details.optionsCreated}개`
    } else if (details.evaluationsCreated > 0 || details.evaluationsUpdated > 0) {
      result.success = true
      result.message = `부분적으로 성공했습니다. ${details.errors.length}개의 오류가 있었습니다.`
    } else {
      result.success = false
      result.message = `임포트에 실패했습니다. ${details.errors.length}개의 오류가 발생했습니다.`
    }


    return result

  } catch (error) {
    console.error('Excel import error:', error)
    
    result.success = false
    result.message = error instanceof Error ? error.message : '알 수 없는 오류가 발생했습니다.'
    result.details.errors.push(result.message)

    return result
  }
}

// 중복 확인 함수
export async function checkDuplicateSerials(serialNumbers: string[]) {
  try {
    const existing = await db
      .select({ serialNumber: esgEvaluations.serialNumber })
      .from(esgEvaluations)
      .where(inArray(esgEvaluations.serialNumber, serialNumbers))

    return existing.map(e => e.serialNumber)
  } catch (error) {
    console.error('Error checking duplicates:', error)
    return []
  }
}