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

import com.ffii.core.response.RecordsRes
import com.ffii.core.support.AbstractBaseEntityService
import com.ffii.core.support.JdbcDao
import com.ffii.core.utils.GsonUtils
import com.ffii.fpsms.modules.common.SecurityUtils
import com.ffii.fpsms.modules.jobOrder.service.JobOrderBomMaterialService
import com.ffii.fpsms.modules.jobOrder.service.JobOrderProcessService
import com.ffii.fpsms.modules.jobOrder.service.JobOrderService
import com.ffii.fpsms.modules.jobOrder.web.model.CreateJobOrderBomMaterialRequest
import com.ffii.fpsms.modules.jobOrder.web.model.CreateJobOrderProcessRequest
import com.ffii.fpsms.modules.jobOrder.web.model.CreateJobOrderRequest
import com.ffii.fpsms.modules.master.entity.*
import com.ffii.fpsms.modules.master.entity.projections.*
import com.ffii.fpsms.modules.master.web.models.MessageResponse
import com.ffii.fpsms.modules.master.web.models.ReleaseProdScheduleLineRequest
import com.ffii.fpsms.modules.master.web.models.SearchProdScheduleRequest
import com.ffii.fpsms.modules.settings.service.SettingsService
import com.ffii.fpsms.modules.stock.entity.Inventory
import com.ffii.fpsms.modules.stock.entity.InventoryRepository
import com.ffii.fpsms.modules.stock.service.InventoryService
import com.google.gson.Gson
import com.google.gson.GsonBuilder
import com.google.gson.JsonDeserializationContext
import com.google.gson.JsonDeserializer
import com.google.gson.JsonElement
import com.google.gson.reflect.TypeToken
import org.springframework.data.domain.PageRequest
import org.springframework.scheduling.support.CronTrigger
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import java.lang.reflect.Type
import java.math.BigDecimal
import java.math.RoundingMode
import java.time.DayOfWeek
import java.time.LocalDate
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.concurrent.ScheduledFuture
import kotlin.collections.component1
import kotlin.collections.component2
import kotlin.jvm.optionals.getOrNull
import kotlin.math.ceil

@Service
open class ProductionScheduleService(
    private val jdbcDao: JdbcDao,
    private val itemService: ItemsService,
    private val productionScheduleRepository: ProductionScheduleRepository,
    private val productionScheduleLineRepository: ProductionScheduleLineRepository,
    private val bomMaterialService: BomMaterialService,
    private val bomMaterialRepository: BomMaterialRepository,
    private val jobOrderService: JobOrderService,
    private val jobOrderBomMaterialService: JobOrderBomMaterialService,
    private val bomService: BomService,
    private val jobOrderProcessService: JobOrderProcessService,
    private val inventoryService: InventoryService,
    private val inventoryRepository: InventoryRepository,
    private val itemUomService: ItemUomService,
    private val settingsService: SettingsService,
) : AbstractBaseEntityService<ProductionSchedule, Long, ProductionScheduleRepository>(
    jdbcDao,
    productionScheduleRepository
) {
    // do mapping with projection
    open val formatter: DateTimeFormatter? = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
    val defaultCronExpression = "0 0 2 * * *";

    open fun getLatestScheduleAt(type: String?): LocalDateTime {
        return productionScheduleRepository.getLatestRoughScheduleAt(type)
    }

    open fun getScheduledAtByDate(inputDate: LocalDate): LocalDate {
        val daysToSubtract = when (inputDate.dayOfWeek) {
            DayOfWeek.WEDNESDAY -> 7
            else -> inputDate.dayOfWeek.value + 7 - DayOfWeek.WEDNESDAY.value
        }

        val lastWednesday = inputDate.minusDays(daysToSubtract.toLong())

        return lastWednesday;
    }

    open fun allProdSchedulesByPage(request: SearchProdScheduleRequest): RecordsRes<ProdScheduleInfo> {
        val pageable = PageRequest.of(request.pageNum ?: 0, request.pageSize ?: 10);

        val response = productionScheduleRepository.findProdScheduleInfoByPage(
            scheduleAt = request.scheduleAt,
            schedulePeriod = request.schedulePeriod,
            schedulePeriodTo = request.schedulePeriodTo,
            totalEstProdCount = request.totalEstProdCount,
            types = request.types,
            pageable = pageable
        )

        val records = response.content
        val total = response.totalElements
        return RecordsRes<ProdScheduleInfo>(records, total.toInt());
    }

    open fun allRoughProdSchedulesByPage(request: SearchProdScheduleRequest): RecordsRes<ProdScheduleInfo> {
//        request.apply {
//            types = listOf("rough")
//        }
        return allProdSchedulesByPage(request);
    }

    open fun allDetailedProdSchedulesByPage(request: SearchProdScheduleRequest): RecordsRes<ProdScheduleInfo> {
        val pageable = PageRequest.of(request.pageNum ?: 0, request.pageSize ?: 10);

        val response = productionScheduleRepository.findProdScheduleInfoByProduceAtByPage(
            produceAt = request.produceAt,
            totalEstProdCount = request.totalEstProdCount,
//             types = listOf("detailed", "manual"),
            types = request.types,
            pageable = pageable
        )

        val records = response.content
        val total = response.totalElements
        return RecordsRes<ProdScheduleInfo>(records, total.toInt());
    }

    open fun roughProdScheduleDetail(id: Long): RoughProdScheduleWithLine {
        val zero = BigDecimal.ZERO
        val prodSchedule = productionScheduleRepository.findById(id).getOrNull() ?: throw NoSuchElementException()
        val dayOfWeekValue =
            if (prodSchedule.scheduleAt.dayOfWeek.value >= 7) prodSchedule.scheduleAt.dayOfWeek.value - 7 else prodSchedule.scheduleAt.dayOfWeek.value
        val schedulePeriod = prodSchedule.scheduleAt.plusDays((7 + 4 - dayOfWeekValue).toLong())
        val schedulePeriodTo = prodSchedule.scheduleAt.plusDays((7 + 4 + 6 - dayOfWeekValue).toLong())
        val prodScheduleLines = prodSchedule.productionScheduleLines.filter { !it.deleted }

        // ---------------------------------- By FG ----------------------------------//
        // Production Schedule By Date
        val prodScheduleLineInfosByFg = prodScheduleLines.map { line ->
            val bomMaterials = line.item.id?.let { bomMaterialRepository.findAllByBomItemIdAndDeletedIsFalse(it) }
                ?.map { bm ->
//                    val proportion =
//                        if (line.prodQty > 0 && bm.bom?.outputQty != null && (bm.bom?.outputQty ?: zero) > zero) {
//                            BigDecimal(line.prodQty).divide(bm.bom!!.outputQty, 2, RoundingMode.HALF_UP)
//                        } else {
//                            BigDecimal.ONE
//                        }

                    val proportion = BigDecimal.ONE // BigDecimal(line.prodQty).divide(bm.bom?.outputQty ?: BigDecimal.ONE, 5, RoundingMode.HALF_UP)
                    val demandQty = bm.qty?.times(proportion) ?: zero
                    val saleUnit = bm.item?.id?.let { itemUomService.findSalesUnitByItemId(it) }

                    RoughProdScheduleLineBomMaterialInfoByFg(
                        id = bm.id,
                        code = bm.item?.code,
                        name = bm.item?.name,
                        type = bm.item?.type,
                        availableQty = bm.item?.inventories?.sumOf {
                            (it.onHandQty ?: zero) - (it.onHoldQty ?: zero) - (it.unavailableQty ?: zero)
                        },
                        demandQty = demandQty,
                        uomName = saleUnit?.uom?.udfudesc
                    )
                }

            val saleUnit = line.item.id?.let { itemUomService.findSalesUnitByItemId(it) }
            RoughProdScheduleLineInfoByFg(
                id = line.id,
                code = line.item.code,
                name = line.item.name,
                type = line.item.type,
                availableQty = line.item.inventories.sumOf {
                    (it.onHandQty ?: zero) - (it.onHoldQty ?: zero) - (it.unavailableQty ?: zero)
                },
                prodQty = BigDecimal(line.prodQty),
                lastMonthAvgSales = BigDecimal(line.lastMonthAvgSales),
                estCloseBal = BigDecimal(line.estCloseBal),
                priority = line.itemPriority,
                bomMaterials = bomMaterials,
                assignDate = line.assignDate,
                uomName = saleUnit?.uom?.udfudesc,
            )
        }.filter { !it.bomMaterials.isNullOrEmpty() }

        // Sum of the Production Schedule
        val sumProdScheduleLineInfosByFg = prodScheduleLineInfosByFg
            .groupBy { it.code }
            .map { (code, infos) ->
                val sumBomMaterials = infos
                    .flatMap { it.bomMaterials ?: mutableListOf() }
                    .groupBy { it.code }
                    .map { (code, bm) ->
                        RoughProdScheduleLineBomMaterialInfoByFg(
                            id = if (bm.isNotEmpty()) bm[0].id else null,
                            code = if (bm.isNotEmpty()) bm[0].code else null,
                            name = if (bm.isNotEmpty()) bm[0].name else null,
                            type = if (bm.isNotEmpty()) bm[0].type else null,
                            availableQty = bm.sumOf { it.availableQty ?: zero },
                            demandQty = bm.sumOf { it.demandQty ?: zero },
                            uomName = if (bm.isNotEmpty()) bm[0].uomName else null
                        )
                    }

                RoughProdScheduleLineInfoByFg(
                    id = if (infos.isNotEmpty()) infos[0].assignDate else null,
                    code = if (infos.isNotEmpty()) infos[0].code else null,
                    name = if (infos.isNotEmpty()) infos[0].name else null,
                    type = if (infos.isNotEmpty()) infos[0].type else null,
                    availableQty = infos.sumOf { (it.availableQty ?: zero) },
                    prodQty = infos.sumOf { (it.prodQty ?: zero) },
                    lastMonthAvgSales = infos.sumOf { (it.lastMonthAvgSales ?: zero) },
                    estCloseBal = infos.sumOf { (it.estCloseBal ?: zero) },
                    priority = if (infos.isNotEmpty()) infos[0].priority else null,
                    bomMaterials = sumBomMaterials,
                    assignDate = null,
                    uomName = if (infos.isNotEmpty()) infos[0].uomName else null
                )
            }

        // Production Schedule By Date group by assign date
        val groupedProdScheduleLinesByFg = prodScheduleLineInfosByFg.groupBy { it.assignDate }

        // ---------------------------------- By BoM ----------------------------------//
        // Seven Days of Bom Materials
        val prodScheduleLinesInfoByBom7Days = prodScheduleLines.mapNotNull { line ->
            val bomMaterial = line.item.id?.let { bomMaterialRepository.findAllByBomItemIdAndDeletedIsFalse(it) }

            bomMaterial?.map { bm ->
//                val proportion =
//                    if (line.prodQty > 0 && bm.bom?.outputQty != null && (bm.bom?.outputQty ?: zero) > zero) {
//                        BigDecimal(line.prodQty).divide(bm.bom!!.outputQty, 2, RoundingMode.HALF_UP)
//                    } else {
//                        BigDecimal.ONE
//                    }

                val proportion = BigDecimal.ONE //BigDecimal(line.prodQty).divide(bm.bom?.outputQty ?: BigDecimal.ONE, 5, RoundingMode.HALF_UP)
                val demandQty = bm.qty?.times(proportion) ?: zero
                val saleUnit = bm.item?.id?.let { itemUomService.findSalesUnitByItemId(it) }

                RoughProdScheduleLineInfoByBomByDate(
                    id = bm.item?.id,
                    code = bm.item?.code,
                    name = bm.item?.name,
                    type = bm.item?.type,
                    availableQty = bm.item?.inventories?.sumOf {
                        (it.onHandQty ?: zero) - (it.onHoldQty ?: zero) - (it.unavailableQty ?: zero)
                    } ?: zero,
                    demandQty = demandQty,
                    assignDate = line.assignDate,
                    uomName = saleUnit?.uom?.udfudesc
                )
            }
        }
            .flatten()

//        println(prodScheduleLinesInfoByBom7Days)

        // Production Schedule By Bom By Date
        val prodScheduleLinesInfoByBom = prodScheduleLinesInfoByBom7Days
            .groupBy { Pair(it.assignDate, it.code) }
            .map { (key, line) ->
                RoughProdScheduleLineInfoByBomByDate(
                    id = if (line.isNotEmpty()) line[0].id else null,
                    code = if (line.isNotEmpty()) line[0].code else null,
                    name = if (line.isNotEmpty()) line[0].name else null,
                    type = if (line.isNotEmpty()) line[0].type else null,
                    availableQty = if (line.isNotEmpty()) line[0].availableQty else zero,
                    demandQty = line.sumOf { it.demandQty ?: zero },
                    assignDate = key.first,
                    uomName = if (line.isNotEmpty()) line[0].uomName else null,
                )
            }

        val groupedProdScheduleLinesByBom = prodScheduleLinesInfoByBom.groupBy { it.assignDate }

        // Sum of the Production Schedule By Bom
        val sumProdScheduleLinesInfoByBom = prodScheduleLinesInfoByBom7Days
            .groupBy { it.code }
            .map { (_, line) ->
                RoughProdScheduleLineInfoByBom(
                    id = if (line.isNotEmpty()) line[0].id else null,
                    code = if (line.isNotEmpty()) line[0].code else null,
                    name = if (line.isNotEmpty()) line[0].name else null,
                    type = if (line.isNotEmpty()) line[0].type else null,
                    availableQty = if (line.isNotEmpty()) line[0].availableQty else null,
                    totalDemandQty = line.sumOf { it.demandQty ?: zero },
                    demandQty1 = line.filter { it.assignDate == 1L }.sumOf { it.demandQty ?: zero },
                    demandQty2 = line.filter { it.assignDate == 2L }.sumOf { it.demandQty ?: zero },
                    demandQty3 = line.filter { it.assignDate == 3L }.sumOf { it.demandQty ?: zero },
                    demandQty4 = line.filter { it.assignDate == 4L }.sumOf { it.demandQty ?: zero },
                    demandQty5 = line.filter { it.assignDate == 5L }.sumOf { it.demandQty ?: zero },
                    demandQty6 = line.filter { it.assignDate == 6L }.sumOf { it.demandQty ?: zero },
                    demandQty7 = line.filter { it.assignDate == 7L }.sumOf { it.demandQty ?: zero },
                    uomName = if (line.isNotEmpty()) line[0].uomName else null,
                )
            }



        // ---------------------------------- Response ----------------------------------//
        return RoughProdScheduleWithLine(
            id = prodSchedule.id,
            scheduleAt = prodSchedule.scheduleAt,
            schedulePeriod = schedulePeriod,
            schedulePeriodTo = schedulePeriodTo,
            totalEstProdCount = BigDecimal(prodSchedule.totalEstProdCount),
            totalFGType = prodSchedule.totalFGType,
            type = prodSchedule.type,
            prodScheduleLinesByFg = sumProdScheduleLineInfosByFg,
            prodScheduleLinesByFgByDate = groupedProdScheduleLinesByFg,
            prodScheduleLinesByBom = sumProdScheduleLinesInfoByBom,
            prodScheduleLinesByBomByDate = groupedProdScheduleLinesByBom
        )
    }

    open fun detailedProdScheduleDetail(id: Long): DetailedProdScheduleWithLine {
        val sqlResult = productionScheduleRepository.findDetailedProdScheduleWithLine(id) ?: throw NoSuchElementException()

        val type = object : TypeToken<List<DetailedProdScheduleLineInfo>?>() {}.type
//        val gsonResult: List<DetailedProdScheduleLineInfo>? = gson.fromJson(sqlResult.prodScheduleLines, type)
        val gsonResult: List<DetailedProdScheduleLineInfo>? = GsonUtils.stringToJson(sqlResult.prodScheduleLines, type)


        return DetailedProdScheduleWithLine(
            id = sqlResult.id,
            scheduleAt = sqlResult.scheduleAt,
            totalEstProdCount = sqlResult.totalEstProdCount,
            totalFGType = sqlResult.totalFGType,
            prodScheduleLines = gsonResult
        )
    }

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun saveProdScheduleLine(request: ReleaseProdScheduleLineRequest): MessageResponse {
        val prodScheduleLine = request.id.let { productionScheduleLineRepository.findById(it).getOrNull() } ?: throw NoSuchElementException()
        val prodSchedule = prodScheduleLine.productionSchedule

        // Update Prod Schedule Type
        prodSchedule.apply {
            type = "manual"
        }
        productionScheduleRepository.saveAndFlush(prodSchedule)

        // Update Prod Schedule Line Prod qty
        prodScheduleLine.apply {
            prodQty = request.demandQty.toDouble()
            type = "manual"
        }
        productionScheduleLineRepository.saveAndFlush(prodScheduleLine)

        val bomMaterials = prodScheduleLine.id?.let { productionScheduleLineRepository.getBomMaterials(it) }

        return MessageResponse(
            id = request.id,
            name = null,
            code = null,
            type = null,
            message = "Success",
            entity = mapOf("bomMaterials" to bomMaterials),
            errorPosition = null
        )
    }

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun releaseProdScheduleLine(request: ReleaseProdScheduleLineRequest): MessageResponse {
        val prodScheduleLine = request.id.let { productionScheduleLineRepository.findById(it).getOrNull() } ?: throw NoSuchElementException()
        val bom = prodScheduleLine.item.id?.let { bomService.findByItemId(it) }
        val approver = SecurityUtils.getUser().getOrNull()
        val proportion = BigDecimal.ONE // request.demandQty.divide(bom?.outputQty ?: BigDecimal.ONE, 5, RoundingMode.HALF_UP)
        val isSameQty = request.demandQty.equals(prodScheduleLine.prodQty)

        // Update Prod Schedule Type
        if (!isSameQty) {
            val prodSchedule = prodScheduleLine.productionSchedule
            prodSchedule.apply {
                type = "manual"
            }
            productionScheduleRepository.save(prodSchedule)
        }

        // Update Prod Schedule Line Prod qty
        prodScheduleLine.apply {
            prodQty = request.demandQty.toDouble()
            this.type = if (isSameQty) this.type else "manual"
            approverId = approver?.id
        }
        productionScheduleLineRepository.save(prodScheduleLine)

        // Create Job Order
        val joRequest = CreateJobOrderRequest(
            bomId = bom?.id,
            reqQty = request.demandQty,
            approverId = approver?.id,
            prodScheduleLineId = request.id
        )

        val jo = jobOrderService.createJobOrder(joRequest)

        // Create Job Order Bom Materials
        if (bom?.bomMaterials != null) {
            // Job Order Bom Material
            val jobmRequests = bom.bomMaterials.map { bm ->
                val demandQty = bm.qty?.times(proportion) ?: BigDecimal.ZERO
                val saleUnit = bm.item?.id?.let { itemUomService.findSalesUnitByItemId(it) }
                println("itemId: ${bm.item?.id} | itemNo: ${bm.item?.code} | itemName: ${bm.item?.name} | saleUnit: ${saleUnit?.uom?.udfudesc}")

                val jobm = CreateJobOrderBomMaterialRequest(
                    joId = jo.id,
                    itemId = bm.item?.id,
                    reqQty = bm.qty?.times(proportion) ?: BigDecimal.ZERO,
                    uomId = saleUnit?.uom?.id
                )

                jobm
            }

            if (jobmRequests != null) {
                jobOrderBomMaterialService.createJobOrderBomMaterials(jobmRequests)
            }

            // Inventory
            /* val inventories = bom.bomMaterials.map { bm ->
                val demandQty = bm.qty?.times(proportion) ?: BigDecimal.ZERO

                var inventory = bm.item?.id?.let { inventoryRepository.findByItemId(it).getOrNull() }
                if (inventory != null) {
                    inventory.apply {
                        this.onHoldQty = (this.onHoldQty ?: BigDecimal.ZERO).plus(demandQty)
                    }
                } else {
                    if (bm.item != null) {
                        val itemUom = bm.item?.id?.let { itemUomService.findSalesUnitByItemId(it) }
                        inventory = Inventory().apply {
                            item = bm.item
                            onHandQty = BigDecimal.ZERO
                            unavailableQty = BigDecimal.ZERO
                            this.onHoldQty = demandQty
                            uom = itemUom?.uom
                            status = "unavailable"
                        }
                    }
                }

                inventory
            }.groupBy { it?.item } // Group by item
                .mapNotNull { (item, invList) ->
                    if (invList.isNotEmpty()) {
                        invList[0]?.apply {
                            onHoldQty = invList.sumOf { it?.onHoldQty ?: BigDecimal.ZERO }
                        }
                    } else {
                        null
                    }
                }

            inventoryRepository.saveAllAndFlush(inventories) */
        }

        // Create Job Order Process
        val jopRequests = bom?.bomProcesses?.map { bp ->
            CreateJobOrderProcessRequest(
                joId = jo.id,
                processId = bp.process?.id,
                seqNo = bp.seqNo,
            )
        }

        if (jopRequests != null) {
            jobOrderProcessService.createJobOrderProcesses(jopRequests)
        }

        // Get Latest Data
//        val bomMaterials = prodScheduleLine.id?.let { productionScheduleLineRepository.getBomMaterials(it) }
        val latestDetail = prodScheduleLine.productionSchedule.id?.let { detailedProdScheduleDetail(it) }

        return MessageResponse(
            id = request.id,
            name = null,
            code = jo.code,
            type = null,
            message = "Success",
            entity = mapOf("prodScheduleLines" to latestDetail?.prodScheduleLines),
            errorPosition = null
        )
    }

    //====================細排相關 START====================//

    open fun getDailyProductionCount(assignDate: Int, selectedDate: LocalDateTime): Int {

        val args = mapOf(
            "assignDate" to assignDate,
            "selectedDate" to selectedDate.format(formatter)
        )

        val sql = StringBuilder(
            " SELECT "
                    + " SUM(psl.prodQty) AS dailyProdCount "
                    + " FROM production_schedule ps "
                    + " LEFT JOIN production_schedule_line psl ON psl.prodScheduleId = ps.id "
                    + " WHERE ps.deleted = FALSE "
                    + " AND psl.deleted = FALSE "
                    + " AND DATE(ps.scheduleAt) LIKE DATE(:selectedDate) "
                    + " AND psl.assignDate = :assignDate "
                    + " GROUP BY ps.id "
                    + " Limit 1"
        );
        return jdbcDao.queryForInt(sql.toString(), args);
    }

    open fun generateDetailedScheduleByDay(assignDate: Int, selectedDate: LocalDateTime, produceAt: LocalDateTime) {
        val detailedScheduleOutputList = ArrayList<ProductionScheduleRecord>()

        // check the produce date have manual changes.
        val manualChange = productionScheduleRepository.findByTypeAndProduceAtAndDeletedIsFalse("detailed", produceAt)
        if (manualChange != null) {
            return;
        }

        //increasement available
        var idleProductionCount = 22000.0 - getDailyProductionCount(assignDate, selectedDate);

        println("idleProductionCount - " + idleProductionCount);

        val args = mapOf(
            "assignDate" to assignDate,
            "selectedDate" to selectedDate.format(formatter)
        )

        val scheduledList: List<ProductionScheduleRecord> = getProductionScheduleRecord(args)

        //用缺口決定生產順序
        val productionPriorityMap: HashMap<ProductionScheduleRecord, Double> = HashMap();
        //TODO: update to use SQL get shop order record for detailed scheduling (real prodQty and openBal)
        for (record in scheduledList) {
            println("Object - " + record.toString());
            val tempRecord = record;
            tempRecord.prodQty = tempRecord.prodQty * 2;
            val difference =
                -(tempRecord.targetMinStock + tempRecord.prodQty - tempRecord.estCloseBal) /*TODO: this should be real close bal*/;
            productionPriorityMap.put(tempRecord, difference)
        }

        //sort by difference
        val sortedEntries = productionPriorityMap.entries.sortedBy { it.value }

        var accProdCount = 0.0;
        var fgCount = 0L;

        for ((updatedScheduleRecord, totalDifference) in sortedEntries) {
            //match id with rough schedule record to create new record
            val targetRecord = scheduledList.find { it.item.id == updatedScheduleRecord.item.id }
            val prodDifference = updatedScheduleRecord.prodQty - (targetRecord?.prodQty ?: 0.0)

            if (idleProductionCount - prodDifference >= 0) {
                //have enough quoter for adjustment
                idleProductionCount -= prodDifference;
                detailedScheduleOutputList.add(updatedScheduleRecord)
                accProdCount += updatedScheduleRecord.prodQty
                fgCount++
            } else {
                println("[INFO] item " + updatedScheduleRecord.name + " have bee skipped");
            }
        }

        // Sort detailedScheduleOutputList by item priority
        val sortedOutputList = detailedScheduleOutputList.sortedBy { it.weightingRef }.toMutableList()

        // Update itemPriority in the sorted list
        var tempPriority = 1L
        sortedOutputList.forEach { record ->
            record.itemPriority = tempPriority
            tempPriority++
        }

        saveDetailedScheduleOutput(sortedOutputList, accProdCount, fgCount, produceAt)
    }

    open fun saveDetailedScheduleOutput(
        sortedEntries: List<ProductionScheduleRecord>,
        accProdCount: Double,
        fgCount: Long,
        produceAt: LocalDateTime,
    ) {
        val tempObj = ProductionSchedule()
        tempObj.id = -1;
        tempObj.scheduleAt = LocalDateTime.now()
        tempObj.produceAt = produceAt;
        tempObj.totalFGType = fgCount;
        tempObj.totalEstProdCount = accProdCount;
        tempObj.type = "detailed"
        tempObj.id = saveProductionScheduleToDatabase(tempObj);

        for (detailedScheduleRecord in sortedEntries) {
            saveDetailedScheduleLineToDatabase(tempObj.id ?: -1, detailedScheduleRecord)
        }
    }

    private fun saveDetailedScheduleLineToDatabase(parentId: Long, detailedScheduleObj: ProductionScheduleRecord) {
        try {
            val prodSchedule = productionScheduleRepository.findById(parentId).get()
            val item = detailedScheduleObj.item.id?.let { itemService.findById(it) } ?: Items()
            var savedItem = ProductionScheduleLine()
            savedItem.id = -1;
//            savedItem.prodScheduleId = parentId;
            savedItem.productionSchedule = prodSchedule;
            savedItem.item = item;
            savedItem.lastMonthAvgSales = detailedScheduleObj.lastMonthAvgSales ?: 0.0;
            savedItem.refScheduleId = detailedScheduleObj.id;
            savedItem.approverId = null;
            savedItem.estCloseBal = detailedScheduleObj.estCloseBal;
            savedItem.prodQty = detailedScheduleObj.prodQty
            savedItem.type = "detailed"
            savedItem.assignDate = detailedScheduleObj.assignDate;
            savedItem.weightingRef = detailedScheduleObj.weightingRef
            savedItem.itemPriority = detailedScheduleObj.itemPriority
            productionScheduleLineRepository.saveAndFlush(savedItem)

        } catch (e: Exception) {
            throw RuntimeException("Error saving production schedule line: ${e.message}", e)
        }
    }
    //====================細排相關 END====================//

    open class ProductionScheduleRecord : ProductionScheduleLine() {
        //SQL record in general with item name
        open var name: String = "" //item name
        open var isDark: Number = 0
        open var isFloat: Number = 0
        open var isDense: Number = 0
        open var targetMinStock: Double = 0.0;

        override fun toString(): String {
            return "ProductionScheduleLine(prodScheduleId=${productionSchedule.id}," +
                    " itemId=${item.id}, lastMonthAvgSales=$lastMonthAvgSales," +
                    " prodQty=$prodQty, estCloseBal=$estCloseBal, type='$type'," +
                    " approverId=$approverId, refScheduleId=$refScheduleId," +
                    " assignDate=$assignDate, itemPriority=$itemPriority)" +
                    " productionScheduleRecord(name='$name', targetMinStock=$targetMinStock)" +
                    " isDark=$isDark, isFloat=$isFloat, isDense=$isDense, weightingRef=$weightingRef"
        }
    }

    open fun getProductionScheduleRecord(args: Map<String, Any>): List<ProductionScheduleRecord> {

        val sql = StringBuilder(
            " SELECT "
                    + " psl.id, "
                    + " psl.itemPriority, "
                    + " psl.itemId, "
                    + " i.name, "
                    + " COALESCE(b.isDark, 0) AS isDark, "
                    + " COALESCE(b.isFloat, 0) AS isFloat, "
                    + " COALESCE(b.isDense, 0) AS isDense, "
                    + " psl.lastMonthAvgSales, "
                    + " psl.lastMonthAvgSales*2 AS targetMinStock, "
                    + " psl.prodQty, "
                    + " psl.estCloseBal, "
                    + " psl.`type`, "
                    + " psl.assignDate "
                    + " FROM production_schedule ps "
                    + " LEFT JOIN production_schedule_line psl ON psl.prodScheduleId = ps.id "
                    + " LEFT JOIN items i ON psl.itemId = i.id "
                    + " LEFT JOIN bom b ON i.id = b.itemId "
                    + " WHERE ps.deleted = FALSE "
                    + " AND psl.deleted = FALSE "
                    + " AND ps.`type` = 'rough' "
        );

        if (args.containsKey("selectedDate")) {
            sql.append(" AND DATE(ps.scheduleAt) LIKE DATE(:selectedDate) AND ps.id = (select max(ps1.id) from production_schedule ps1 where DATE(ps1.scheduleAt) LIKE DATE(:selectedDate) AND ps1.`type` = 'rough') ");
        }
        if (args.containsKey("name")) {
            sql.append(" AND i.name LIKE :name ");
        }
        if (args.containsKey("assignDate")) {
            sql.append(" AND psl.assignDate = :assignDate ");
        }
        if (args.containsKey("id")) {
            sql.append(" AND i.id = :id ");
        }

        sql.append(" ORDER BY psl.assignDate, psl.itemPriority ASC ");

        print(sql)
        val resultList = jdbcDao.queryForList(sql.toString(), args);
        print(resultList)

        //TODO: From Global config
        val DARK_MAX_VALUE = 1;
        val FLOAT_MAX_VALUE = 5;
        val DENSE_MAX_VALUE = 5;
        val DARK_WEIGHTING = 0.34;
        val FLOAT_WEIGHTING = 0.33;
        val DENSE_WEIGHTING = 0.33;

        // Convert the list of maps to a list of ProductionScheduleRecord
        return resultList.map { row ->
            ProductionScheduleRecord().apply {
                this.id = row["id"].toString().toLong()
                this.itemPriority = row["itemPriority"].toString().toLong()
                this.item.id = row["itemId"].toString().toLong()
                this.name = row["name"] as String
                this.lastMonthAvgSales = row["lastMonthAvgSales"].toString().toDouble()
                this.targetMinStock = row["targetMinStock"].toString().toDouble() // Ensure correct type
                this.prodQty = row["prodQty"].toString().toDouble()
                this.estCloseBal = row["estCloseBal"].toString().toDouble()
                this.type = row["type"] as String
                this.assignDate = row["assignDate"].toString().toLong()

                this.isDark = row["isDark"].toString().toInt()
                this.isDense = row["isDense"].toString().toInt()
                this.isFloat = row["isFloat"].toString().toInt()

                val isDarkValue = row["isDark"].toString().toInt()
                val isDenseValue = row["isDense"].toString().toInt()
                val isFloatValue = row["isFloat"].toString().toInt()

                this.weightingRef =
                    if (isDarkValue > 0) {
                        (DARK_MAX_VALUE + 1 - isDarkValue) / DARK_MAX_VALUE.toDouble() * DARK_WEIGHTING
                    } else {
                        0.0
                    } +
                            if (isDenseValue > 0) {
                                (DENSE_MAX_VALUE + 1 - isDenseValue) / DENSE_MAX_VALUE.toDouble() * DENSE_WEIGHTING
                            } else {
                                0.0
                            } +
                            if (isFloatValue > 0) {
                                (FLOAT_MAX_VALUE + 1 - isFloatValue) / FLOAT_MAX_VALUE.toDouble() * FLOAT_WEIGHTING
                            } else {
                                0.0
                            }
            }
        }
    }

    //====================粗排相關 START====================//
    open fun convertToFinishedGoodList(): ArrayList<FinishedGood> {
        val roughScheduleList = itemService.getRoughScheduleList() // Retrieve the list from your existing method
        val finishedGoodList = ArrayList<FinishedGood>()

        for (item in roughScheduleList) {
            val finishedGood = FinishedGood(
                id = item["itemId"].toString().toLong(), // Cast to Long
                name = item["itemName"] as String?, // Cast to String
                receipeId = 0, // Assuming itemNo is a Long
                openBalance = 0.0, // Set default value or calculate if needed
                lastMonthAvgSalesCount = item["lastMonthAvgSalesCount"].toString().toDouble(), // Cast to Double
                fgProductionLimit = item["fgProductionLimit"].toString().toDouble() // Default production limit
            )
            finishedGoodList.add(finishedGood)
        }

        return finishedGoodList
    }

    open fun generateRoughScheduleByWeek(fgList: ArrayList<FinishedGood>): HashMap<RoughScheduleObj, Double> {
        val roughScheduleOutput: HashMap<RoughScheduleObj, Double> = HashMap();

        var itemCount = 1
        for (fg: FinishedGood in fgList) {
            val roughScheduleRecord: RoughScheduleObj = roughScheduleByItem(fg);
            itemCount++
            roughScheduleOutput.put(roughScheduleRecord, roughScheduleRecord.totalDifference);
        }

        //完成初步fg粗排後檢查調整
        for (i in 0 until 12) {
            var accProductionCount = 0L;

            // Sort by totalDifference before processing
            val sortedEntries = roughScheduleOutput.entries.sortedBy { it.value }

            //原排產
            var itemCount = 1L;
            for ((roughScheduleRecord, totalDifference) in sortedEntries) {
                //記錄粗排優先順序(以最少庫存缺口最大優先)
                roughScheduleRecord.itemPriority[i] = itemCount;
                itemCount++;

                if (accProductionCount + ceil(roughScheduleRecord.productionSchedule[i]).toLong() <= 22000) { //沒有超過每日產量
                    accProductionCount += ceil(roughScheduleRecord.productionSchedule[i]).toLong() //分開日子算

                    roughScheduleRecord.totalDifference += roughScheduleRecord.productionSchedule[i]
                    roughScheduleOutput[roughScheduleRecord] = roughScheduleRecord.totalDifference

                } else {//超過每日產量
                    var maxExtraProduction = Math.max((22000L - accProductionCount), 0L);
                    roughScheduleRecord.totalForgoneCount =
                        roughScheduleRecord.totalForgoneCount + roughScheduleRecord.productionSchedule[i] - maxExtraProduction;
                    roughScheduleRecord.productionSchedule[i] = maxExtraProduction.toDouble();
                    accProductionCount += maxExtraProduction;

                    //update close balance
                    roughScheduleRecord.closeBalance[i] =
                        roughScheduleRecord.closeBalance[i] - roughScheduleRecord.productionSchedule[i] + maxExtraProduction.toDouble();
                    for (j in i + 1 until 12) {
                        roughScheduleRecord.closeBalance[j] =
                            roughScheduleRecord.closeBalance[j] - roughScheduleRecord.productionSchedule[i] + maxExtraProduction.toDouble();
                    }

                    // Update totalDifference in roughScheduleRecord
                    roughScheduleRecord.totalDifference += (roughScheduleRecord.productionSchedule[i] - maxExtraProduction.toDouble());
                    roughScheduleOutput[roughScheduleRecord] = roughScheduleRecord.totalDifference
                }

            }

            //解決拖欠的數量
            for ((roughScheduleRecord, totalDifference) in sortedEntries) {
                if (accProductionCount + ceil(roughScheduleRecord.productionSchedule[i]).toLong() <= 22000) { //沒有超過每日產量
                    if (roughScheduleRecord.totalForgoneCount > 0.0) {
                        if (accProductionCount + ceil(roughScheduleRecord.totalForgoneCount).toLong() <= 22000) {
                            //可以全做
                            roughScheduleRecord.productionSchedule[i] =
                                roughScheduleRecord.productionSchedule[i] + ceil(roughScheduleRecord.totalForgoneCount).toLong();

                            accProductionCount += ceil(roughScheduleRecord.totalForgoneCount).toLong();

                            roughScheduleRecord.totalDifference += roughScheduleRecord.totalForgoneCount;
                            roughScheduleOutput[roughScheduleRecord] = roughScheduleRecord.totalDifference

                            //update close balance
                            for (j in i until 12) {
                                roughScheduleRecord.closeBalance[j] =
                                    roughScheduleRecord.closeBalance[j] + roughScheduleRecord.totalForgoneCount;
                            }
                            roughScheduleRecord.totalForgoneCount = 0.0;
                        } else {
                            //只能做部分
                            var maxExtraProduction = Math.max((22000L - accProductionCount), 0L);
                            roughScheduleRecord.totalForgoneCount =
                                Math.max((roughScheduleRecord.totalForgoneCount - maxExtraProduction), 0.0);

                            roughScheduleRecord.productionSchedule[i] =
                                roughScheduleRecord.productionSchedule[i] + maxExtraProduction;
                            accProductionCount += maxExtraProduction;
                            roughScheduleRecord.totalDifference += maxExtraProduction;

                            //update close balance
                            for (j in i until 12) {
                                roughScheduleRecord.closeBalance[j] =
                                    roughScheduleRecord.closeBalance[j] + maxExtraProduction;
                            }
                        }
                    }

                }
            }

            //debug use
            for ((roughScheduleRecord, totalDifference) in sortedEntries) {
                println("[RUN" + i + "][index:" + totalDifference + "] - " + roughScheduleRecord.fgDetail?.name + " - " + roughScheduleRecord.productionSchedule[i]);
            }
            println("Total Production Count $accProductionCount");
            println("========================================");

        }

        return roughScheduleOutput
    }

    open fun roughScheduleByItem(fg: FinishedGood): RoughScheduleObj {
        val minStockCount: Double = fg.lastMonthAvgSalesCount * 2 //TODO: change to multiple by user config setting
        val stockDifference: Double = minStockCount - fg.openBalance
        val productionSchedule: Array<Double> = Array<Double>(12, { 0.0 })

        //首日粗排產量 —＞ 取結果，或是fg本身生產上限,並不可小於0
        productionSchedule[0] = Math.max(
            ceil(
                Math.min(
                    (stockDifference + fg.lastMonthAvgSalesCount),
                    fg.fgProductionLimit
                )
            ),
            0.0
        )

        val closeBalance: Array<Double> = Array<Double>(12, { 0.0 })
        closeBalance[0] = /*Math.floor(*/fg.openBalance + productionSchedule[0] - fg.lastMonthAvgSalesCount/*)*/;

        for (i in 1 until 12) {
            //最少庫存-closeBalance 有可能小於0，所以必須要確保輸出>=0
            productionSchedule[i] = Math.max(
                ceil(
                    Math.min(
                        (minStockCount - closeBalance[i - 1] + fg.lastMonthAvgSalesCount),
                        fg.fgProductionLimit
                    )
                ),
                0.0
            )
            closeBalance[i] = /*Math.floor(*/
                closeBalance[i - 1] + productionSchedule[i] - fg.lastMonthAvgSalesCount/*)*/;
        }

        var totalProductionCount: Double = 0.0

        for (i in 5 until 12) {
            totalProductionCount = totalProductionCount + productionSchedule[i];
        }

        val totalDifference: Double = -(totalProductionCount - fg.openBalance);


        return RoughScheduleObj(
            fg,
            productionSchedule,
            closeBalance,
            totalDifference,
            totalProductionCount,
            0.0
        );
    }

    open fun saveRoughScheduleOutput(
        sortedEntries: List<Map.Entry<RoughScheduleObj, Double>>,
        accProdCount: Double,
        fgCount: Long
    ) {
        val tempObj = ProductionSchedule()
        tempObj.id = -1;
        tempObj.scheduleAt = LocalDateTime.now()
        tempObj.totalFGType = fgCount;
        tempObj.totalEstProdCount = accProdCount;
        tempObj.type = "rough"
        tempObj.id = saveProductionScheduleToDatabase(tempObj);

        for ((roughScheduleRecord, totalDifference) in sortedEntries) {
            saveRoughScheduleLineToDatabase(tempObj.id ?: -1, roughScheduleRecord)
        }
    }

    private fun saveProductionScheduleToDatabase(newRecord: ProductionSchedule): Long? {
        try {
            val savedItem = productionScheduleRepository.saveAndFlush(newRecord)
            return savedItem.id
        } catch (e: Exception) {
            throw RuntimeException("Error saving production schedule: ${e.message}", e)
        }
    }

    private fun saveRoughScheduleLineToDatabase(parentId: Long, roughScheduleObj: RoughScheduleObj) {
        for (i in 5 until 12) {
            try {
                val prodSchedule = productionScheduleRepository.findById(parentId).get()
                val item = roughScheduleObj.fgDetail?.id?.let { itemService.findById(it) }
                var savedItem = ProductionScheduleLine()
                savedItem.id = -1;
//                savedItem.prodScheduleId = parentId;
                savedItem.productionSchedule = prodSchedule;
                if (item != null) {
                    savedItem.item = item
                };
                savedItem.lastMonthAvgSales = roughScheduleObj.fgDetail?.lastMonthAvgSalesCount ?: 0.0;
                savedItem.refScheduleId = null;
                savedItem.approverId = null;
                savedItem.estCloseBal = roughScheduleObj.closeBalance[i]
                savedItem.prodQty = roughScheduleObj.productionSchedule[i]
                savedItem.type = "rough"
                savedItem.assignDate = i - 4L;
                savedItem.itemPriority = roughScheduleObj.itemPriority[i]
                productionScheduleLineRepository.saveAndFlush(savedItem)

            } catch (e: Exception) {
                throw RuntimeException("Error saving production schedule line: ${e.message}", e)
            }
        }

    }
    //====================粗排相關 END====================//

    open class FinishedGood {
        //var fgDetail: FinishedGood? = null
        var id: Long? = null
        var name: String? = null
        var receipeId: Long? = null
        var openBalance: Double = 0.0
        var lastMonthAvgSalesCount: Double = 0.0
        var fgProductionLimit: Double = 22000.0

        constructor(
            id: Long?,
            name: String?,
            receipeId: Long?,
            openBalance: Double,
            lastMonthAvgSalesCount: Double,
            fgProductionLimit: Double
        ) {
            this.id = id
            this.name = name
            this.receipeId = receipeId
            this.openBalance = openBalance
            this.lastMonthAvgSalesCount = lastMonthAvgSalesCount
            this.fgProductionLimit = fgProductionLimit
        }
    }

    open class RoughScheduleObj {
        var fgDetail: FinishedGood? = null
        var productionSchedule: Array<Double> = Array<Double>(12, { 0.0 })
        var closeBalance: Array<Double> = Array(12) { 0.0 };
        var itemPriority: Array<Long> = Array(12) { 0 };
        var totalDifference: Double = 0.0
        var totalProductionCount: Double = 0.0
        var totalForgoneCount: Double = 0.0


        constructor(
            fgDetail: FinishedGood?,
            productionSchedule: Array<Double>,
            closeBalance: Array<Double>,
            totalDifference: Double,
            totalProductionCount: Double,
            totalForgoneCount: Double
        ) {
            this.fgDetail = fgDetail
            this.productionSchedule = productionSchedule
            this.closeBalance = closeBalance
            this.totalDifference = totalDifference
            this.totalProductionCount = totalProductionCount
            this.totalForgoneCount = totalForgoneCount
        }

        override fun toString(): String {
            return "RoughScheduleObj(fgDetail=$fgDetail, productionSchedule=${productionSchedule.contentToString()}, closeBalance=${closeBalance.contentToString()}, totalDifference=$totalDifference, totalProductionCount=$totalProductionCount, totalForgoneCount=$totalForgoneCount)"
        }

    }
}