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

import com.ffii.core.support.AbstractBaseEntityService
import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.master.entity.*
import com.ffii.fpsms.modules.master.web.models.MessageResponse
import org.springframework.stereotype.Service
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*
import kotlin.collections.component1
import kotlin.collections.component2
import kotlin.math.ceil

@Service
open class ProductionScheduleService(
    private val jdbcDao: JdbcDao,
    private val itemService: ItemsService,
    private val productionScheduleRepository: ProductionScheduleRepository,
    private val productionScheduleLineRepository: ProductionScheduleLineRepository,
): AbstractBaseEntityService<ProductionSchedule, Long, ProductionScheduleRepository>(jdbcDao, productionScheduleRepository)  {
    // do mapping with projection
    open val formatter: DateTimeFormatter? = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")

    //====================細排相關 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 "
        );
        return jdbcDao.queryForInt(sql.toString(), args);
    }

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

        //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.itemId == updatedScheduleRecord.itemId }
            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)
    }

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

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


    private fun saveDetailedScheduleLineToDatabase(parentId: Long, detailedScheduleObj: ProductionScheduleRecord) {
        try {
            var savedItem = ProductionScheduleLine()
            savedItem.id = -1;
            savedItem.prodScheduleId = parentId;
            savedItem.itemId = detailedScheduleObj.itemId ?: -1;
            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=$prodScheduleId," +
                    " itemId=$itemId, 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 "
        );

        if(args.containsKey("selectedDate")){
            sql.append(" AND DATE(ps.scheduleAt) LIKE DATE(:selectedDate) ");
        }
        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 ");

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

        //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.itemId = 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.id = saveProductionScheduleToDatabase(tempObj);
        tempObj.type = "rough"

        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 {
                var savedItem = ProductionScheduleLine()
                savedItem.id = -1;
                savedItem.prodScheduleId = parentId;
                savedItem.itemId = roughScheduleObj.fgDetail?.id ?: -1;
                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)"
        }

    }

}