package com.ffii.fpsms.modules.master.service

import com.ffii.fpsms.modules.master.entity.*
import com.ffii.fpsms.modules.master.web.models.*
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.springframework.core.io.ClassPathResource
import org.springframework.core.io.Resource
import org.springframework.core.io.support.PathMatchingResourcePatternResolver
import org.springframework.http.ResponseEntity
import org.springframework.stereotype.Service
import java.io.ByteArrayOutputStream
import java.nio.file.Path
import java.nio.file.Paths
import java.nio.file.Files;
import java.nio.file.StandardCopyOption
import com.ffii.fpsms.modules.master.entity.EquipmentDetailRepository


@Service
open class BomService(
    private val bomRepository: BomRepository,
    private val bomMaterialRepository: BomMaterialRepository,
    private val bomProcessRepository: BomProcessRepository,
    private val bomProcessMaterialRepository: BomProcessMaterialRepository,
    private val itemsService: ItemsService,
    private val itemsRepository: ItemsRepository,
    private val uomConversionService: UomConversionService,
    private val uomConversionRepository: UomConversionRepository,
    private val equipmentRepository: EquipmentRepository,
    private val processRepository: ProcessRepository,
    private val equipmentDetailRepository: EquipmentDetailRepository,
) {
    open fun findAll(): List<Bom> {
        return bomRepository.findAll()
    }

    open fun findById(id: Long): Bom? {
        return bomRepository.findByIdAndDeletedIsFalse(id)
    }

    open fun findByM18Id(m18Id: Long): Bom? {
        return bomRepository.findByM18IdAndDeletedIsFalse(m18Id)
    }

    open fun findByItemId(itemId: Long): Bom? {
        return bomRepository.findByItemIdAndDeletedIsFalse(itemId)
    }

    open fun saveBom(request: SaveBomRequest): SaveBomResponse {

        val item = request.code.let { itemsService.findByM18BomCode(it) } ?: request.itemId?.let { itemsService.findById(it) }

        val uom = request.m18UomId?.let { uomConversionService.findByM18Id(it) }
            ?: request.uomId?.let { uomConversionService.findById(it) }

        val bom = request.m18Id?.let { findByM18Id(it) }
            ?: request.id?.let { findById(it) }
            ?: Bom()

        bom.apply {
            this.item = item
            code = request.code
            name = request.name
            description = request.description
            outputQty = request.outputQty
            outputQtyUom = request.outputQtyUom
            yield = request.yield
            this.uom = uom
            m18Id = request.m18Id ?: m18Id
            m18LastModifyDate = request.m18LastModifyDate ?: m18LastModifyDate
        }

        val response = bomRepository.saveAndFlush(bom).let {
            SaveBomResponse(
                id = it.id,
                code = it.code,
                name = it.name,
                description = it.description,
                outputQty = it.outputQty,
                outputQtyUom = it.outputQtyUom,
                yield = it.yield,
                uomName = uom?.udfudesc,
            )
        }

        return response
    }

    //////// -------------------------------- for excel import ------------------------------- /////////
    private fun saveBomEntity(req: ImportBomRequest): Bom {
        val item =  itemsRepository.findByCodeAndDeletedFalse(req.code) ?: itemsRepository.findByNameAndDeletedFalse(req.name)
        val uom = if (req.uomId != null) uomConversionRepository.findById(req.uomId!!).orElseThrow() else null
        val bom = bomRepository.findByCodeAndDeletedIsFalse(req.code) ?: Bom()
        bom.apply {
            this.isDark = req.isDark
            this.isFloat = req.isFloat
            this.isDense = req.isDense
            this.code = req.code
            this.scrapRate = req.scrapRate
            this.timeSequence = req.timeSequence
            this.complexity = req.complexity
            this.allergicSubstances = req.allergicSubstances
            this.name = req.name
            this.description = req.description
            this.item = item
            this.outputQty = req.outputQty
            this.outputQtyUom = req.outputQtyUom
            this.yield = req.yield
            this.uom = uom
//            this.excelUom = req.excelUom
        }
        val savedBom = bomRepository.saveAndFlush(bom)
//        println("saved: ${savedBom.id}")
        return savedBom
    }

    private fun saveBomMaterial(req: ImportBomMatRequest): BomMaterial {
//        val uom = uomConversionRepository.findByCodeAndDeletedFalse()
//        println("printing")
//        println(req)
//        println(req.item)
        val bomMaterial = req.bom?.id?.let { bId -> req.item?.id?.let { iId -> bomMaterialRepository.findByBomIdAndItemId(bId, iId) } } ?: BomMaterial()
        bomMaterial.apply {
            this.item = req.item
            this.itemName = req.item!!.name
            this.isConsumable = req.isConsumable
            this.qty = req.qty
            this.salesUnit = req.salesUnit
            this.salesUnitCode = req.salesUnitCode
            this.uom = req.uom
            this.uomName = req.uomName
            this.bom = req.bom
        }
        return bomMaterialRepository.saveAndFlush(bomMaterial)
    }
    private fun saveBomProcess(req: ImportBomProcessRequest): BomProcess {
        val bomProcess = req.bom!!.id?.let { id -> req.seqNo?.let { seqNo -> bomProcessRepository.findBySeqNoAndBomIdAndDeletedIsFalse(seqNo.toInt(), id) } } ?: BomProcess()
        bomProcess.apply {
            this.process = req.process
            this.equipment = req.equipment
            this.description = req.description
            this.seqNo = req.seqNo
            this.durationInMinute = req.durationInMinute
            this.prepTimeInMinute = req.prepTimeInMinute
            this.postProdTimeInMinute = req.postProdTimeInMinute
            this.bom = req.bom
        }
        return bomProcessRepository.saveAndFlush(bomProcess)
    }
    fun saveBomProcessMaterial(req: ImportBomProcessMaterialRequest): BomProcessMaterial {
        val bomProcessMaterial = req.bomProcess?.id?.let { pid -> req.bomMaterial?.id?.let { mid -> bomProcessMaterialRepository.findByBomProcessIdAndBomMaterialId(pid, mid)} } ?: BomProcessMaterial()
        bomProcessMaterial.apply {
            this.bomProcess = req.bomProcess
            this.bomMaterial = req.bomMaterial
        }
        return bomProcessMaterialRepository.saveAndFlush(bomProcessMaterial)
    }
    private fun importExcelBomMaterial(bom: Bom, sheet: Sheet) {
        var bomProcessMatRequest = ImportBomProcessMaterialRequest()
        var startRowIndex = 10
        val endRowIndex = 30
        var startColumnIndex = 0
        val endColumnIndex = 10
        while (startRowIndex < endRowIndex) {
            val tempRow = sheet.getRow(startRowIndex)
            val tempCell = tempRow.getCell(startColumnIndex)
            if (tempCell != null && tempCell.cellType == CellType.STRING && tempCell.stringCellValue.trim() == "材料編號") {
                println("last: $startRowIndex")
                startRowIndex++
                break
            }
            startRowIndex++
        }
        var bomMatRequest = ImportBomMatRequest(
            bom = bom
        )
        println("starting new loop")
        while (startRowIndex != endRowIndex || startColumnIndex != endColumnIndex) {
            val tempRow = sheet.getRow(startRowIndex)
            val tempCell = tempRow.getCell(startColumnIndex)
            if (startColumnIndex == 0 && (tempCell == null || tempCell.cellType == CellType.BLANK)) {
                break
            } else {
                try {
                    when (startColumnIndex) {
                        0 -> {
//                            println("rowIndex: $startRowIndex")
                            val nameRow = sheet.getRow(startRowIndex)
                            val nameCell = nameRow.getCell(1)
                            println(tempCell.stringCellValue.trim())
                            val item = itemsRepository.findByCodeAndDeletedFalse(tempCell.stringCellValue.trim())
                                ?: itemsRepository.findByNameAndDeletedFalse(nameCell.stringCellValue.trim())
//                            println("getting item.....:")
//                            println(item)
                            bomMatRequest.item = item
                        }
                        2-> {
                            bomMatRequest.qty = tempCell.numericCellValue.toBigDecimal()
                        }
                        3 -> {
                            val uom = uomConversionRepository.findByCodeAndDeletedFalse(tempCell.stringCellValue.trim())
                            bomMatRequest.uom = uom
                            bomMatRequest.uomName = uom?.udfudesc
                        }
                        /* 
                        6 -> {
                            bomMatRequest.qty = tempCell.numericCellValue.toBigDecimal()
                        }
                        */
                        7 -> {
                            val salesUnit = uomConversionRepository.findByCodeAndDeletedFalse(tempCell.stringCellValue.trim())
                            bomMatRequest.salesUnit = salesUnit
                            bomMatRequest.salesUnitCode = salesUnit?.udfudesc
                        }
                         
                      
                        /* 
                        2 -> {
                            val salesUnit = uomConversionRepository.findByCodeAndDeletedFalse(tempCell.stringCellValue.trim())
                            bomMatRequest.salesUnit = salesUnit
                        }*/

                        10 -> {
                            println("seqNo: ${tempCell.numericCellValue.toInt()}")
                            println("bomId: ${bom.id!!}")
                            val bomProcess = bomProcessRepository.findBySeqNoAndBomIdAndDeletedIsFalse(
                                seqNo = tempCell.numericCellValue.toInt(),
                                bomId = bom.id!!
                            )!! // if null = bugged
                            bomProcessMatRequest.bomProcess = bomProcess
                        }
                    }

                    println("startRowIndex: $startRowIndex")
                    println("endRowIndex: $endRowIndex")

//                    println("first condition: ${startColumnIndex < endColumnIndex}")
//                    println("second condition: ${startRowIndex < endRowIndex}")
                    if (startColumnIndex < endColumnIndex) {
                        startColumnIndex++
                    } else if (startRowIndex < endRowIndex) {
                        startRowIndex++
                        // do save
                        println("req:")
                        println(bomMatRequest)
                        val bomMaterial = saveBomMaterial(bomMatRequest)
                        bomProcessMatRequest.bomMaterial = bomMaterial
                        val bomProcessMaterial = saveBomProcessMaterial(bomProcessMatRequest)
                        // clean up
                        startColumnIndex = 0
                        bomMatRequest = ImportBomMatRequest(
                            bom = bom
                        )
                        println("saved: $bomMatRequest")
                    }
                } catch(e: Error) {
                    println("DEBUG ERROR:")
                    println(e)
                }
            }
        }
    }

    private fun importExcelBomBasicInfo(sheet: Sheet): Bom {
        var request = ImportBomRequest(
            code = "",
            name = "",
            description = "",
        )
        var startRowIndex = 0
        val endRowIndex = 10
        var startColumnIndex = 0
        val endColumnIndex = 9
        while (startRowIndex != endRowIndex || startColumnIndex != endColumnIndex) {
            val tempRow = sheet.getRow(startRowIndex)
            val tempCell = tempRow.getCell(startColumnIndex)
            if (tempCell != null && tempCell.cellType == CellType.STRING) {
                val tempCellVal = tempCell.stringCellValue.trim()
                // --------------------- assigning value that is under a header to request ------------------------ //
                val topTargetValueRow = sheet.getRow(startRowIndex + 1)
                val topTargetValueCell = topTargetValueRow.getCell(startColumnIndex)
                when (tempCellVal) {
                   "編號" -> {
                       request.code = topTargetValueCell.stringCellValue.trim()
                   }
                   "產品名稱" ->  request.name = topTargetValueCell.stringCellValue.trim()
                   "種類" ->  request.description = topTargetValueCell.stringCellValue.trim()
                   "份量 (Qty)" ->  request.outputQty = topTargetValueCell.numericCellValue.toBigDecimal()
                   "單位" ->  request.outputQtyUom = topTargetValueCell.stringCellValue.trim()
                }
                // ----------------------------------------------------------------------------------------------- //
                // --------------------- assigning value that is next to a header to request ------------------------ //
                fun calculateColourScore(value: String): Int {
                    var score = -1
                    when (value) {
                        "淺" -> score = 0
                        "深" -> score = 1
                    }
                    return score
                }
                fun calculateFloatScore(value: String): Int {
                    var score = -1
                    when (value) {
                        "沉" -> score = 0
                        "浮" -> score = 1
                    }
                    return score
                }

                fun calculateAllergicSubstancesScore(value: String): Int {
                    var score = -1
                    when (value) {
                        "N/A" -> score = 0
                        "0" -> score = 0
                        "Yes" -> score = 5
                        "1" -> score = 5
                        " " -> score = 0
                        "" -> score = 0
                    }
                    return score
                }
                val leftTargetValueRow = sheet.getRow(startRowIndex)
                val leftTargetValueCell = leftTargetValueRow.getCell(startColumnIndex + 1)

                fun getCellValueAsString(cell: org.apache.poi.ss.usermodel.Cell?): String {
                    return when {
                        cell == null -> ""
                        cell.cellType == CellType.STRING -> cell.stringCellValue.trim()
                        cell.cellType == CellType.NUMERIC -> cell.numericCellValue.toString().trim()
                        cell.cellType == CellType.BLANK -> ""
                        else -> ""
                    }
                }

                when {
                        //use fix row column by index not by search value contain later
                    tempCellVal.contains("深淺") ->  request.isDark = calculateColourScore(getCellValueAsString(leftTargetValueCell))
                    tempCellVal.contains("浮沉") ->  request.isFloat = calculateFloatScore(getCellValueAsString(leftTargetValueCell))
                    tempCellVal.contains("過敏原 （如有）") ->  request.allergicSubstances = calculateAllergicSubstancesScore(getCellValueAsString(leftTargetValueCell))
                    tempCellVal.contains("濃淡") ->  request.isDense = if (leftTargetValueCell?.cellType == CellType.NUMERIC)
                        leftTargetValueCell.numericCellValue.toInt()
                    else 0
                    tempCellVal.contains("損耗率") ->  request.scrapRate = if (leftTargetValueCell?.cellType == CellType.NUMERIC)
                        leftTargetValueCell.numericCellValue.toInt()
                    else 0
                    tempCellVal.contains("生產先後次序") ->  request.timeSequence = if (leftTargetValueCell?.cellType == CellType.NUMERIC)
                        leftTargetValueCell.numericCellValue.toInt()
                    else 0
                    tempCellVal.contains("複雜度") ->  request.complexity = if (leftTargetValueCell?.cellType == CellType.NUMERIC)
                        leftTargetValueCell.numericCellValue.toInt()
                    else 0
                }
            }
        
            if (startRowIndex < endRowIndex) {
                startRowIndex++
            } else if (startColumnIndex < endColumnIndex) {
                startColumnIndex++
                startRowIndex = 0
            }
        }
        return saveBomEntity(request)
    }

    private fun generateNextTMPEquipmentCode(): String {
        // 查询所有未删除的 EquipmentDetail
        val allEquipmentDetails = equipmentDetailRepository.findAllByDeletedFalse()
        
        // 过滤出以 "TMP" 开头的 equipmentCode，并提取数字部分
        val tmpNumbers = allEquipmentDetails
            .mapNotNull { it.equipmentCode }
            .filter { it.startsWith("TMP") }
            .mapNotNull { code ->
                // 提取 TMP 后面的数字部分（例如：TMP01 -> 1, TMP02 -> 2）
                val numberPart = code.substring(3).trim()
                numberPart.toIntOrNull()
            }
        
        // 找到最大编号，如果没有则从 0 开始
        val maxNumber = tmpNumbers.maxOrNull() ?: 0
        
        // 递增并格式化为两位数（TMP01, TMP02, ...）
        val nextNumber = maxNumber + 1
        return "TMP${nextNumber.toString().padStart(2, '0')}"
    }
    private fun bomGetOrCreateEquipment(equipmentName: String): Equipment {
        var equipment = equipmentRepository.findByNameAndDeletedIsFalse(equipmentName)
            ?: equipmentRepository.findByCodeAndDeletedIsFalse(equipmentName)
    
        var equipment_detail = equipmentDetailRepository.findByNameAndDeletedIsFalse(equipmentName)
        if (equipment == null) {
            // 解析 XXX-YYY 格式
            val parts = equipmentName.split("-")
            val firstPart = if (parts.size > 0) parts[0] else equipmentName  // XXX
            val secondPart = if (parts.size > 1) parts[1] else ""   // YYY
            
            // 创建 equipment
            equipment = Equipment().apply {
                this.name = equipmentName  // 完整值 XXX-YYY
                this.code = secondPart
                this.description = firstPart  // XXX 写入 description
            }
            equipment = equipmentRepository.saveAndFlush(equipment)
            
            // 创建 equipment_detail
            if (equipment_detail == null) {
                val nextEquipmentCode = generateNextTMPEquipmentCode()
                equipment_detail = EquipmentDetail().apply {
                    this.name = "1號"  // 默认值
                    this.description = equipmentName  // equipmentName (完整值 XXX-YYY)
                    this.code = "${this.description}-$name" 
                    this.equipmentCode=nextEquipmentCode
                }
                equipment_detail = equipmentDetailRepository.saveAndFlush(equipment_detail)
            }
        }
        return equipment!!
    }

    private fun bomGetOrCreateProcess(name: String): Process {
        var process = processRepository.findByNameAndDeletedIsFalse(name)
            ?: processRepository.findByCodeAndDeletedIsFalse(name)
        if (process == null) {
            val truncatedName = name.take(30)
            process = Process().apply {
                this.name = truncatedName
                this.code = truncatedName
                this.description = name
            }
            process = processRepository.saveAndFlush(process)
        }
        return process!!
    }

    private fun extractDurationStringToMinutes(str: String): Int {
        val regex = """(\d+)(\D+)""".toRegex()
        val matchResult = regex.find(str)
//        fun returnMultiplier(unit: String): {
//
//        }
        val (number, unit) = matchResult?.let {
            val num1 = it.groupValues[1].toInt()
            val unit1 = it.groupValues[2]
//            val num2 = it.groupValues[3].toInt()
//            val unit2 = it.groupValues[4]
            Pair(num1, unit1)
        }!!
        var multiplier = 1
        when {
            unit.contains("min") -> {
                multiplier = 1
            }
            unit.contains("hr") -> {
                multiplier = 60
            }
        }
        return number * multiplier
    }
    private fun importExcelBomProcess(bom: Bom, sheet: Sheet) {
        var startRowIndex = 30
        var endRowIndex = 70
        var startColumnIndex = 0
        val endColumnIndex = 11
        while (startRowIndex < endRowIndex) {
            val tempRow = sheet.getRow(startRowIndex) ?: throw Exception()
            val tempCell = tempRow.getCell(startColumnIndex)
            if (tempCell != null && tempCell.cellType == CellType.STRING && tempCell.stringCellValue.trim() == "工序") {
                startRowIndex += 2 // skip column header
//                println("last: $startRowIndex")
                break
            }
            startRowIndex++
        }
        var searchRowIndex = startRowIndex
        val maxSearchRow = 50 
        while (searchRowIndex < maxSearchRow) {
            val tempRow = sheet.getRow(searchRowIndex)
            val tempCell = tempRow?.getCell(0)
            if (tempCell == null || tempCell.cellType == CellType.BLANK) {
                endRowIndex = searchRowIndex
                break
            }
            searchRowIndex++
        }
        var bomProcessRequest = ImportBomProcessRequest(
            bom = bom
        )
        while (startRowIndex != endRowIndex || startColumnIndex != endColumnIndex) {
            val tempRow = sheet.getRow(startRowIndex)
            val tempCell = tempRow.getCell(startColumnIndex)
//            val checkCell = tempRow.getCell(0)
            if (startColumnIndex == 0 && (tempCell == null || tempCell.cellType == CellType.BLANK)) {
//                println("hi")
                break
            } else {
//                println(startRowIndex)
//                println(startColumnIndex)
//                println(tempCell.cellType)
//                println(tempCell.toString())
                try {
                    when (startColumnIndex) {
                        0 -> {
//                            println("startRowIndex: $startRowIndex")
                            bomProcessRequest.seqNo = tempCell.numericCellValue.toLong()
//                            println("seqNo: ${tempCell.numericCellValue.toLong()}")
//                            println("bomProcessRequest: $bomProcessRequest")
                        }

                        1 -> {
                            val processName = tempCell.stringCellValue.trim()
                            val process = bomGetOrCreateProcess(processName)
                            bomProcessRequest.process = process
//                            println("process created")
                        }
                        2 -> {
                            val description = tempCell.stringCellValue.trim()
                            bomProcessRequest.description = description
                        }
                        3 -> {
                            val equipmentName = tempCell.stringCellValue.trim()
                            if (equipmentName != "不適用") {
                                val equipment = bomGetOrCreateEquipment(equipmentName)
//                                println("equipment created")
                                bomProcessRequest.equipment = equipment
                            }
                        }
                        5 -> { //duration
                            when (tempCell.cellType) {
                                CellType.NUMERIC -> {
                                    bomProcessRequest.durationInMinute = tempCell.numericCellValue.toInt()
                                }
                                CellType.STRING -> {
                                    val durationString = tempCell.stringCellValue.trim()
                                    bomProcessRequest.durationInMinute = extractDurationStringToMinutes(durationString)
                                }

                                else -> {}
                            }

                        }
                        /*
                        5 -> { //processOutputQty
                            val processOutputQty = tempCell.stringCellValue.trim()
                            bomProcessRequest.processOutputQty = processOutputQty

                        }
                        6 -> { //processOutputUnit
                            val processOutputUnit = tempCell.stringCellValue.trim()
                            bomProcessRequest.processOutputUnit = processOutputUnit

                        }
                        7 -> { //byProductName
                            val byProductQty = tempCell.stringCellValue.trim()
                            bomProcessRequest.byProductQty = byProductQty
                        }
                        8 -> { //byProductQty
                            val byProductUnit = tempCell.stringCellValue.trim()
                            bomProcessRequest.byProductUnit = byProductUnit
                        }

                        9 -> { //byProductUnit
                            val byProductUnit = tempCell.stringCellValue.trim()
                            bomProcessRequest.byProductUnit = byProductUnit
                        }
                    */
                        9 -> { //scrapRate
                           // val scrapRate = tempCell.stringCellValue.trim()
                           // bomProcessRequest.scrapRate = scrapRate
                        }
                        10 -> { //prepTimeInMinute
                            when (tempCell.cellType) {
                                CellType.NUMERIC -> {
                                    bomProcessRequest.prepTimeInMinute = tempCell.numericCellValue.toInt()
                                }
                                CellType.STRING -> {
                                    val prepTimeInMinuteString = tempCell.stringCellValue.trim()
                                    bomProcessRequest.prepTimeInMinute = extractDurationStringToMinutes(prepTimeInMinuteString)
                                }

                                else -> {}
                            }
                        }
                        11 -> { //postProdTimeInMinute
                            when (tempCell.cellType) {
                                CellType.NUMERIC -> {
                                    bomProcessRequest.postProdTimeInMinute = tempCell.numericCellValue.toInt()
                                }
                                CellType.STRING -> {
                                    val postProdTimeInMinuteString = tempCell.stringCellValue.trim()
                                    bomProcessRequest.postProdTimeInMinute = extractDurationStringToMinutes(postProdTimeInMinuteString)
                                }
                                else -> {}
                            }
                        }
                    }
//                    println("req:")
//                    println(bomProcessRequest)
                    // moving the loop
                    if (startColumnIndex < endColumnIndex) {
//                        println("case1")
                        startColumnIndex++
//                        println("next col startColumnIndex: $startColumnIndex")
                    } else if (startRowIndex < endRowIndex) {
//                        println("case2")
                        // when doesn't meet first condition, falls to this and go new row
                        startRowIndex++
//                        println("next row startRowIndex: $startRowIndex")
                        // do save
                        saveBomProcess(bomProcessRequest)
                        // clean up
                        startColumnIndex = 0
                        bomProcessRequest = ImportBomProcessRequest(
                            bom = bom
                        )
                    }
                } catch (e: Error) {
//                    throw e
                }
            }
        }
    }
    open fun exportProblematicBOM() {
        val resolver = PathMatchingResourcePatternResolver()
        val successExcel = resolver.getResources("file:C:/Users/2Fi/Desktop/fail/*.xlsx")
        val failedExcels = resolver.getResources("file:C:/Users/2Fi/Desktop/fail/*.xlsx")



    }
    open fun importBOM(): ByteArray {
//        val folderPath = "bomImport"
//        val folder = File(folderPath)
        val resolver = PathMatchingResourcePatternResolver()
//        val excels = resolver.getResources("bomImport/*.xlsx")
        //val excels = resolver.getResources("file:C:/Users/Kelvin YAU/Downloads/bom/*.xlsx")
        val excels = resolver.getResources("file:C:/Users/Kelvin YAU/Downloads/datasetup/bom/*.xlsx")
       // val excels = resolver.getResources("file:C:/Users/kw093/Downloads/bom/bom/*.xlsx")
//        val excels = resolver.getResources("file:C:/Users/2Fi/Desktop/Third Wave of BOM Excel/*.xlsx")
        println("size: ${excels.size}")
        val logExcel = ClassPathResource("excelTemplate/bom_excel_issue_log.xlsx")
        val templateInputStream = logExcel.inputStream
        val workbook: Workbook = XSSFWorkbook(templateInputStream)
        val logSheet: Sheet = workbook.getSheetAt(0)
        var rowIndex = 0

            /////// for outputing issue log ////////////
//        excels.forEachIndexed { index, resource ->
////            get sheet
//            println(resource.filename)
//            val templateInputStream1 = resource.inputStream
//            val workbook1: Workbook = XSSFWorkbook(templateInputStream1)
//            val sheet: Sheet = workbook1.getSheetAt(0)
//            println("sheetName")
//            println(sheet.sheetName)
//            val successPath: Path = Paths.get("C:/Users/2Fi/Desktop/success/${resource.filename}")
//            val failPath: Path = Paths.get("C:/Users/2Fi/Desktop/fail/${resource.filename}")
//            val bom = try {
//                importExcelBomBasicInfo(sheet) // Updating BOM table
//            } catch (e: Exception) {
//                println("Error in importExcelBomBasicInfo: ${e.message}")
//                rowIndex++
//                val row = logSheet.createRow(rowIndex)
//                row.createCell(0).setCellValue(resource.filename)
//                row.createCell(1).setCellValue(false)
//                row.createCell(2).setCellValue(e.message)
//                Files.copy(templateInputStream, failPath, StandardCopyOption.REPLACE_EXISTING)
//                throw
//                return@forEachIndexed
////                throw e // Rethrow to handle in the outer try-catch
//            }
//            try {
//                importExcelBomProcess(bom, sheet)
//            } catch (e: Exception) {
//                println("Error in importExcelBomProcess: ${e.message}")
//                rowIndex++
//                val row = logSheet.createRow(rowIndex)
//                row.createCell(0).setCellValue(resource.filename)
//                row.createCell(1).setCellValue(false)
//                row.createCell(2).setCellValue(e.message)
//                Files.copy(templateInputStream, failPath, StandardCopyOption.REPLACE_EXISTING)
//                throw
//                return@forEachIndexed
////                throw e // Rethrow to handle in the outer try-catch
//            }
//            try {
//                importExcelBomMaterial(bom, sheet)
//            } catch (e: Exception) {
//                println("Error in importExcelBomMaterial: ${e.message}")
//                rowIndex++
//                val row = logSheet.createRow(rowIndex)
//                row.createCell(0).setCellValue(resource.filename)
//                row.createCell(1).setCellValue(false)
//                row.createCell(2).setCellValue(e.message)
//                Files.copy(templateInputStream, failPath, StandardCopyOption.REPLACE_EXISTING)
//                throw
//                return@forEachIndexed
////                throw e // Rethrow to handle in the outer try-catch
//            }
//            rowIndex++
//            val row = logSheet.createRow(rowIndex)
//            row.createCell(0).setCellValue(resource.filename)
//            row.createCell(1).setCellValue(true)
//            Files.copy(templateInputStream, successPath, StandardCopyOption.REPLACE_EXISTING)
//        }
//            val outputStream = ByteArrayOutputStream()
//            workbook.write(outputStream)
//            workbook.close()
//            return outputStream.toByteArray()
        ////////////////////////////////////////////////////////////////////////////////////////////
        for (resource in excels) {
            //get sheet
            println(resource.filename)
            val templateInputStream2 = resource.inputStream
            val workbook2: Workbook = XSSFWorkbook(templateInputStream2)
            val sheet: Sheet = workbook2.getSheetAt(0)
            val bom = importExcelBomBasicInfo(sheet) // updating bom table
            //  import bom process / create new process
            importExcelBomProcess(bom, sheet)
            // import bom material
            importExcelBomMaterial(bom, sheet)

//            break
        }
            val outputStream = ByteArrayOutputStream()
            workbook.write(outputStream)
            workbook.close()
            return outputStream.toByteArray()
    }
}