package com.ffii.fpsms.modules.pickOrder.service

import com.ffii.core.response.RecordsRes
import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.common.SecurityUtils
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderRepository
import com.ffii.fpsms.modules.pickOrder.entity.projection.PickOrderInfo
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderStatus
import com.ffii.fpsms.modules.pickOrder.web.models.*
import com.ffii.fpsms.modules.stock.entity.InventoryLotLineRepository
import com.ffii.fpsms.modules.stock.entity.StockOut
import com.ffii.fpsms.modules.stock.entity.StockOutRepository
import com.ffii.fpsms.modules.stock.entity.projection.CurrentInventoryItemInfo
import com.ffii.fpsms.modules.stock.service.InventoryService
import com.ffii.fpsms.modules.stock.service.StockOutLineService
import com.ffii.fpsms.modules.stock.service.SuggestedPickLotService
import com.ffii.fpsms.modules.stock.web.model.StockOutStatus
import com.ffii.fpsms.modules.stock.web.model.SuggestedPickLotForPoRequest
import com.ffii.fpsms.modules.user.entity.UserRepository
import com.ffii.fpsms.modules.user.service.UserService
import org.springframework.data.domain.PageRequest
import org.springframework.http.HttpStatus
import org.springframework.http.ResponseEntity
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import java.io.Serializable
import java.math.BigDecimal
import java.time.LocalDate
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import kotlin.jvm.optionals.getOrNull

@Service
open class PickOrderService(
    private val jdbcDao: JdbcDao,
    val pickOrderRepository: PickOrderRepository,
    val stockOutLineService: StockOutLineService,
    val suggestedPickLotService: SuggestedPickLotService,
    val userService: UserService,
    private val inventoryLotLineRepository: InventoryLotLineRepository,
    val inventoryService: InventoryService,
    private val stockOutRepository: StockOutRepository
) {
    open fun localDateTimeParse(dateTime: String?, pattern: String? = "YYYY-MM-DD hh:mm:ss"): LocalDateTime? {
        try {
            val formatter = DateTimeFormatter.ofPattern(pattern!!)
            return LocalDateTime.parse(dateTime, formatter)
        } catch (e: Exception) {
            return null
        }
    }

    open fun allPickOrders(): List<PickOrderInfo> {
        return pickOrderRepository.findPickOrderInfoByDeletedIsFalse()
    }

    open fun allPickOrdersByPage(request: SearchPickOrderRequest): RecordsRes<PickOrderInfo> {
        val pageable = PageRequest.of(request.pageNum ?: 0, request.pageSize ?: 10);

        val response = pickOrderRepository.findPickOrderInfoByConditionsAndPageable(
            code = request.code ?: "all",
            targetDateFrom = localDateTimeParse(request.targetDateFrom),
            targetDateTo = localDateTimeParse(request.targetDateTo),
            type = request.type ?: "all",
            status = request.status ?: "all",
            itemName = request.itemName ?: "all",
            pageable = pageable
        )

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

    open fun getConsoPickOrderList(args: MutableMap<String, Any>): List<Map<String, Any>> {
        val sql = StringBuilder( "select"
            + " po.consoCode, "
            + " po.releasedDate, "
            + " po.status, "
            + " po.assignTo "
            + " from pick_order po "
            + " where po.deleted = false "
            + " and po.consoCode is not null "
        )
        if (args.containsKey("consoCode")){
            sql.append(" AND po.consoCode like :consoCode ");
        }
        if (args.containsKey("status")){
            sql.append(" AND po.status = :status ");
        }
        sql.append(" group by po.consoCode, po.releasedDate, po.status, po.assignTo ")
        return jdbcDao.queryForList(sql.toString(), args);
    }
    // Consolidating Pick Orders
    open fun assignConsoCode(): String {
        val suffixFormat = "%03d"
        val pattern = "yyyyMMdd"
        val formatter = DateTimeFormatter.ofPattern(pattern)

        val prefix = "PICK"
        val midfix = LocalDate.now().format(formatter)
        val suffix = String.format(suffixFormat, 1)

        val latestConsoCode = pickOrderRepository.findLatestConsoCodeByPrefix("${prefix}-${midfix}")
        if (latestConsoCode != null) {
            val splitLatestConsoCode = latestConsoCode.split("-")
            if (splitLatestConsoCode.size > 2) {
                val latestNo = splitLatestConsoCode[2].toInt()
                return listOf<String>(prefix, midfix, String.format(suffixFormat, latestNo + 1)).joinToString("-")
            }
        }

        return listOf<String>(prefix, midfix, suffix).joinToString("-")
    }

    open fun consoPickOrders(request: ConsoPickOrderRequest): List<PickOrderInfo> {
        val newConsoCode = assignConsoCode()
        val pickOrders = pickOrderRepository.findAllByIdInAndStatus(request.ids, PickOrderStatus.PENDING)
        pickOrders.forEach {
            it.consoCode = newConsoCode
            it.status = PickOrderStatus.CONSOLIDATED
        }
        println(newConsoCode)
        println(pickOrders)

        val savedPickOrders = pickOrderRepository.saveAll(pickOrders)
        val savedPickOrderInfos = savedPickOrders.map { po -> po.id as Serializable }
            .let { pickOrderRepository.findPickOrderInfoByIdIn(it) }

        return savedPickOrderInfos
    }

    open fun deconsoPickOrders(request: ConsoPickOrderRequest): List<PickOrderInfo> {
        val pickOrders = pickOrderRepository.findAllByIdInAndStatus(request.ids, PickOrderStatus.CONSOLIDATED)
        pickOrders.forEach {
            it.consoCode = null
            it.status = PickOrderStatus.PENDING
        }

        val updatedPickOrders = pickOrderRepository.saveAll(pickOrders)
        val updatedPickOrderInfos = updatedPickOrders.map { po -> po.id as Serializable }
            .let { pickOrderRepository.findPickOrderInfoByIdIn(it) }

        return updatedPickOrderInfos
    }

    open fun getPickOrderLine(args: MutableMap<String, Any>): List<Map<String, Any>> {
        if (!args.containsKey("consoCode") || args["consoCode"] == null) {
            throw IllegalArgumentException("consoCode must not be null")
        }
        val sql = StringBuilder("select"
                + " pol.id, "
                + " i.name as itemName, "
                + " pol.qty as qty, "
                + " max(uc.code) as uom, "
                + " group_concat(JSON_ARRAY(w.name)) as warehouse, "
                + " group_concat(JSON_ARRAY(il.lotNo)) as suggestedLotNo "
                + " from pick_order po "
                + " left join pick_order_line pol on pol.poId = po.id "
                + " left join items i on i.id = pol.itemId "
                + " left join suggested_pick_lot spl on spl.pickOrderLineId = pol.id "
                + " left join inventory_lot_line ill on ill.id = spl.suggestedLotLineId "
                + " left join warehouse w on w.id = ill.warehouseId "
                + " left join inventory_lot il on il.id = ill.inventoryLotId "
                + " left join uom_conversion uc on uc.id = pol.uomId "
                + " where po.deleted = false "
                + " and po.consoCode = :consoCode "
                + " group by pol.id, i.name, pol.qty "
        )
        return jdbcDao.queryForList(sql.toString(), args);
    }

    // TODO: Add actual pick lots
    open fun consoPickOrderDetail(consoCode: String): ConsoPickOrderResponse {
        val zero = BigDecimal.ZERO

        // pick orders
        val pos = pickOrderRepository.findAllByConsoCode(consoCode)

        // Suggestions for Pick Order
//        val suggestions =
//            suggestedPickLotService.suggestionForPickOrders(SuggestedPickLotForPoRequest(pickOrders = pos))
//        val suggestedList = suggestions.suggestedList

        // Mapping: PickOrder -> PickOrderInConso
        val finalPos = pos.map { po ->
            val pols = po.pickOrderLines
            // Suggestions for Pick Order Line
            // val suggestions = suggestedPickLotService.suggestionForPickOrderLines(pols)

            // Pick Order Lines
            // Mapping: PickOrderLine -> PickOrderLineInConso
            val finalPols = pols.map { pol ->
                val item = pol.item
                val uom = pol.uom

                // Check If already have suggestion
                val suggestedPickLots = pol.suggestedPickLots
//                if (suggestedPickLots.isEmpty()) {
//                    suggestedPickLots = suggestedList.filter { it.pickOrderLine?.id == pol.id }.toMutableList()
//                }

                // Mapping: SuggestedPickLot -> SuggestPickLotInConso
                val finalSuggestedPickLots = suggestedPickLots.map {
                    val inventoryLotLine = it.suggestedLotLine
                    val remainingQty = (inventoryLotLine?.inQty ?: zero)
                        .minus(inventoryLotLine?.outQty ?: zero)
                        .minus(inventoryLotLine?.holdQty ?: zero)
                    val stockUom = inventoryLotLine?.stockUom?.uom

                    // Mapping: InventoryLotLine -> InventoryLotLineInConso
                    val finalInventoryLotLine = InventoryLotLineInConso(
                        id = inventoryLotLine?.id,
                        lotNo = inventoryLotLine?.inventoryLot?.lotNo,
                        inQty = inventoryLotLine?.inQty,
                        outQty = inventoryLotLine?.outQty,
                        holdQty = inventoryLotLine?.holdQty,
                        remainingQty = remainingQty,
                        stockUom = IdCodeDesc(stockUom?.id, stockUom?.code, stockUom?.udfudesc),
                        status = inventoryLotLine?.status?.value,
                        remarks = inventoryLotLine?.remarks
                    )

                    // Return
                    SuggestedPickLotInConso(
                        id = it.id,
                        type = it.type?.value,
                        qty = it.qty,
                        inventoryLotLine = finalInventoryLotLine,
                        pickSuggested = it.pickSuggested
                    )
                }

                // Actual Pick Lots
//                val actualPickLots = pol.stockOutLines
//                val finalActualPickLots = actualPickLots.map {
//                    ActualPickLotInConso(
//
//                    )
//                }

                // Return
                PickOrderLineInConso(
                    id = pol.id,
                    item = IdCodeName(item?.id, item?.code, item?.name),
                    qty = pol.qty,
                    uom = IdCodeDesc(uom?.id, uom?.code, uom?.udfudesc),
                    suggestedPickLots = finalSuggestedPickLots,
                    status = pol.status?.value,
                    actualPickLots = mutableListOf()
                )
            }

            val releasedBy = po.releasedBy
            val assignTo = po.assignTo

            // Return
            PickOrderInConso(
                id = po.id,
                code = po.code,
                targetDate = po.targetDate,
                completeDate = po.completeDate,
                releasedDate = po.releasedDate,
                releasedBy = IdName(releasedBy?.id, releasedBy?.name),
                assignTo = IdName(assignTo?.id, assignTo?.name),
                pickOrderLines = finalPols,
                type = po.type?.value,
                status = po.status?.value,
            )
        }

        // Items
        val finalItems = finalPos
            .flatMap { it.pickOrderLines }
            .groupBy { it.item.id }
            .map { (_itemId, _line) ->
                val itemSuggestions = _line
                    .flatMap { it.suggestedPickLots }
                    .groupBy { it.inventoryLotLine.id to it.pickSuggested }
                    .map { (_key, _lot) ->
                        SuggestedPickLotInConso(
                            id = _key.first,
                            type = _lot.first().type,
                            inventoryLotLine = _lot.first().inventoryLotLine,
                            qty = _lot.fold(zero) { sum, item -> sum + (item.qty ?: zero) },
                            pickSuggested = _key.second
                        )
                    }

                ItemInConso(
                    id = _line.first().item.id,
                    code = _line.first().item.code,
                    name = _line.first().item.name,
                    qty = _line.fold(zero) { sum, item -> sum + (item.qty ?: zero) },
                    suggestPickLots = itemSuggestions,
                    actualPickLots = mutableListOf()
                )
            }

        val response = ConsoPickOrderResponse(
            consoCode = consoCode,
            pickOrders = finalPos,
            items = finalItems
        )

        return response
    }

    open fun releaseConsoPickOrderInfo(consoCode: String): ReleasePickOrderInfoResponse {
        val zero = BigDecimal.ZERO
        val pos = pickOrderRepository.findAllByConsoCodeAndStatus(consoCode, PickOrderStatus.CONSOLIDATED)
        println(pos)
        // Get Inventory Data
        val requiredItems = pos
            .flatMap { it.pickOrderLines }
            .groupBy { it.item?.id }
            .map { (key, value) ->
                key to object : CurrentInventoryItemInfo {
                    override val id: Long? = value[0].item?.id
                    override val code: String? = value[0].item?.code
                    override val name: String? = value[0].item?.name
                    override val uomDesc: String? = value[0].uom?.udfudesc
                    override var availableQty: BigDecimal? = zero
                    override val requiredQty: BigDecimal = value.sumOf { it.qty ?: zero }

                }
            } // itemId - requiredQty

        val itemIds = requiredItems.mapNotNull { it.first }
//        val inventories = inventoryLotLineRepository.findCurrentInventoryByItems(itemIds)
        val inventories = inventoryService.allInventoriesByItemIds(itemIds)

        // Pick Orders
        val releasePickOrderInfos = pos
            .map { po ->
                val releasePickOrderLineInfos = po.pickOrderLines.map { pol ->
                    val inventory = inventories.find { it.itemId == pol.item?.id }

                    // Return
                    ReleasePickOrderLineInfo(
                        id = pol.id,
                        itemId = pol.item?.id,
                        itemCode = pol.item?.code,
                        itemName = pol.item?.name,
                        availableQty = inventory?.availableQty,
                        requiredQty = pol.qty,
                        uomCode = pol.uom?.code,
                        uomDesc = pol.uom?.udfudesc,
                    )
                }

                // Return
                ReleasePickOrderInfo(
                    id = po.id,
                    code = po.code,
                    targetDate = po.targetDate,
                    type = po.type?.value,
                    status = po.status?.value,
                    pickOrderLines = releasePickOrderLineInfos
                )
            }

        // Items
        val currentInventoryInfos = requiredItems.map { item ->
            val inventory = inventories.find { it.itemId == item.first }

            item.second.let {
                it.availableQty = inventory?.availableQty

                // return
                it
            }
        }

        return ReleasePickOrderInfoResponse(
            consoCode = consoCode,
            pickOrders = releasePickOrderInfos,
            items = currentInventoryInfos,
        )
    }

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun releaseConsoPickOrderAction(request: ReleaseConsoPickOrderRequest): ReleasePickOrderInfoResponse {
        val zero = BigDecimal.ZERO
        val releasedBy = SecurityUtils.getUser().getOrNull()
        val assignTo = request.assignTo?.let { userService.find(it) }?.getOrNull()
        val pos = pickOrderRepository.findAllByConsoCodeAndStatus(request.consoCode, PickOrderStatus.CONSOLIDATED)
        pos.forEach {
            it.apply {
                this.releasedBy = releasedBy
                status = PickOrderStatus.RELEASED
                this.assignTo = assignTo
            }
        }

        val suggestions =
            suggestedPickLotService.suggestionForPickOrders(SuggestedPickLotForPoRequest(pickOrders = pos))
        val currUser = SecurityUtils.getUser().orElseThrow()
        val stockOut = StockOut().apply {
            this.type = "job"
            this.consoPickOrderCode = request.consoCode
            this.status = StockOutStatus.PENDING.status
            this.handler = currUser.id
        }
        stockOutRepository.save(stockOut)
        suggestedPickLotService.saveAll(suggestions.suggestedList)

        val saveSuggestedPickLots = suggestedPickLotService.saveAll(suggestions.suggestedList)
        pickOrderRepository.saveAll(pos)

        val inventoryLotLines = inventoryLotLineRepository.findAllByIdIn(saveSuggestedPickLots.mapNotNull { it.suggestedLotLine?.id })

        saveSuggestedPickLots.forEach { lot ->
            val lineIndex = inventoryLotLines.indexOf(lot.suggestedLotLine)
            inventoryLotLines[lineIndex].holdQty = (inventoryLotLines[lineIndex].holdQty ?: zero).plus(lot.qty ?: zero)
        }
//        inventoryLotLines.forEach {  line ->
//            line.holdQty = (line.holdQty ?: zero)?.plus(saveSuggestedPickLots.find { it.suggestedLotLine == line }?.qty ?: zero)
//        }

        inventoryLotLineRepository.saveAll(inventoryLotLines)

        return releaseConsoPickOrderInfo(request.consoCode)
    }
}