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.support.PathMatchingResourcePatternResolver
import org.springframework.stereotype.Service
import java.io.File


@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,
) {
    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 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 = Bom().apply {
            this.isDark = req.isDark
            this.isFloat = req.isFloat
            this.isDense = req.isDense
            this.code = req.code
            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()
        val 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.salesUnit?.code
            this.uom = req.uom
            this.uomName = req.uomName
            this.bom = req.bom

        }
        return bomMaterialRepository.saveAndFlush(bomMaterial)
    }
    private fun saveBomProcess(req: ImportBomProcessRequest): BomProcess {
        val 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 = 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 -> {
                            bomMatRequest.uomName = tempCell.stringCellValue.trim()
                        }
                        7 -> {
                            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 = 8
        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
                }
                val leftTargetValueRow = sheet.getRow(startRowIndex)
                val leftTargetValueCell = leftTargetValueRow.getCell(startColumnIndex + 1)
                when {
                    tempCellVal.contains("深淺") ->  request.isDark = calculateColourScore(leftTargetValueCell.stringCellValue.trim())
                    tempCellVal.contains("浮沉") ->  request.isFloat = calculateFloatScore(leftTargetValueCell.stringCellValue.trim())
                    tempCellVal.contains("濃淡") ->  request.isDense = 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 bomGetOrCreateEquipment(name: String): Equipment {
        var equipment = equipmentRepository.findByNameAndDeletedIsFalse(name)
            ?: equipmentRepository.findByCodeAndDeletedIsFalse(name)
        if (equipment == null) {
            equipment = Equipment().apply {
                this.name = name
                this.code = name
                this.description = name
            }
            equipment = equipmentRepository.saveAndFlush(equipment)
        }
        return equipment!!
    }
    private fun bomGetOrCreateProcess(name: String): Process {
        var process = processRepository.findByNameAndDeletedIsFalse(name)
            ?: processRepository.findByCodeAndDeletedIsFalse(name)
        if (process == null) {
            process = Process().apply {
                this.name = name
                this.code = name
                this.description = name
            }
            process = processRepository.saveAndFlush(process)
        }
        return process!!
    }

    private fun extractDurationStringToMinutes(str: String): Int {
        val regex = """(\d+)(\D+)(\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
        val endRowIndex = 70
        var startColumnIndex = 0
        val endColumnIndex = 11
        while (startRowIndex < endRowIndex) {
            val tempRow = sheet.getRow(startRowIndex)
            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 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(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 equipmentName = tempCell.stringCellValue.trim()
                            if (equipmentName != "不適用") {
                                val equipment = bomGetOrCreateEquipment(equipmentName)
//                                println("equipment created")
                                bomProcessRequest.equipment = equipment
                            }
                        }
                        2 -> {
                            val processName = tempCell.stringCellValue.trim()
                            val process = bomGetOrCreateProcess(processName)
                            bomProcessRequest.process = process
//                            println("process created")
                        }
                        3 -> {
                            val description = tempCell.stringCellValue.trim()
                            bomProcessRequest.description = description
                        }
                        6 -> { //duration
                            when (tempCell.cellType) {
                                CellType.NUMERIC -> {
                                    bomProcessRequest.durationInMinute = tempCell.numericCellValue.toInt()
                                }
                                CellType.STRING -> {
                                    val durationString = tempCell.stringCellValue.trim()
                                    bomProcessRequest.durationInMinute = extractDurationStringToMinutes(durationString)
                                }

                                else -> {}
                            }

                        }
                        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 importBOM() {
//        val folderPath = "bomImport"
//        val folder = File(folderPath)
        val resolver = PathMatchingResourcePatternResolver()
        val excels = resolver.getResources("bomImport/*.xlsx")

        for (resource in excels) {
            //get sheet
            println(resource.filename)
            val templateInputStream = resource.inputStream
            val workbook: Workbook = XSSFWorkbook(templateInputStream)
            val sheet: Sheet = workbook.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
        }
    }
}