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

import com.ffii.core.response.RecordsRes
import com.ffii.core.support.AbstractBaseEntityService
import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.common.SecurityUtils
import com.ffii.fpsms.modules.jobOrder.entity.JobOrderRepository
import com.ffii.fpsms.modules.master.entity.ItemsRepository
import com.ffii.fpsms.modules.master.entity.UomConversionRepository
import com.ffii.fpsms.modules.master.service.ItemUomService
import com.ffii.fpsms.modules.pickOrder.entity.PickOrder
import com.ffii.fpsms.modules.deliveryOrder.enums.DoPickOrderStatus
import java.time.LocalTime
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderLine
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderLineRepository
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderRepository
import com.ffii.fpsms.modules.pickOrder.entity.projection.PickOrderInfo
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderLineStatus
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderStatus
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderGroup
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderGroupRepository
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.StockOutLIneRepository
import com.ffii.fpsms.modules.stock.entity.StockOutRepository
import com.ffii.fpsms.modules.stock.entity.enum.InventoryLotLineStatus
import com.ffii.fpsms.modules.stock.entity.projection.CurrentInventoryItemInfo
import com.ffii.fpsms.modules.stock.service.InventoryLotLineService
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.StockOutLineStatus
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.IOException
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
import com.ffii.fpsms.modules.pickOrder.entity.projection.PickOrderGroupInfo
import com.ffii.fpsms.modules.deliveryOrder.entity.DeliveryOrderRepository
import com.ffii.fpsms.modules.pickOrder.entity.TruckRepository
import com.ffii.fpsms.modules.pickOrder.entity.RouterRepository
import com.ffii.fpsms.modules.deliveryOrder.service.DoPickOrderService
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrder
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrderRecord
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrderRecordRepository
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrderRepository
import com.ffii.fpsms.modules.pickOrder.web.models.LotSubstitutionConfirmRequest
import com.ffii.fpsms.modules.master.web.models.MessageResponse
@Service    
open class PickOrderService(
    private val jdbcDao: JdbcDao,
    private val pickOrderRepository: PickOrderRepository,
    private val pickOrderLineRepository: PickOrderLineRepository,
    private val suggestedPickLotService: SuggestedPickLotService,
    private val userService: UserService,
    private val stockOutLIneRepository: StockOutLIneRepository,
    private val inventoryLotLineRepository: InventoryLotLineRepository,
    private val pickOrderGroupRepository: PickOrderGroupRepository,
    private val inventoryLotLineService: InventoryLotLineService,
    private val inventoryService: InventoryService,
    private val stockOutRepository: StockOutRepository,
    private val itemsRepository: ItemsRepository,
    private val uomConversionRepository: UomConversionRepository,
    private val jobOrderRepository: JobOrderRepository,
    private val itemUomService: ItemUomService,
    private val deliveryOrderRepository: DeliveryOrderRepository, 
    private val truckRepository: TruckRepository,
    private val doPickOrderService: DoPickOrderService,
    private val routerRepository: RouterRepository,
    private val doPickOrderRecordRepository: DoPickOrderRecordRepository,
    private val doPickOrderRepository: DoPickOrderRepository,
    private val userRepository: UserRepository


    
) : AbstractBaseEntityService<PickOrder, Long, PickOrderRepository>(jdbcDao, pickOrderRepository) {
    open fun create(request: SavePickOrderRequest): MessageResponse {
        val code = assignPickCode()
        val jo = request.joId?.let { jobOrderRepository.findById(it).getOrNull() }
        val deliveryOrder = request.doId?.let { deliveryOrderRepository.findById(it).getOrNull() }
        val pickOrder = PickOrder().apply {
            this.code = code
            this.jobOrder = jo
            this.deliveryOrder = deliveryOrder
            this.targetDate = request.targetDate.atStartOfDay()
            this.type = request.type
            this.status = PickOrderStatus.PENDING
        }
        val savedPickOrder = saveAndFlush(pickOrder)
        val polEntries = request.pickOrderLine.map {
//            val item = itemsRepository.findById(it.itemId).orElseThrow()
            val uom = it.uom?.let { uomCode -> uomConversionRepository.findByCodeAndDeletedFalse(uomCode) }
                ?: it.uomId?.let { uomId -> uomConversionRepository.findById(uomId).orElseThrow() }
            val item = it.itemId?.let { itemId -> itemsRepository.findById(itemId).orElseThrow() }
            PickOrderLine().apply {
                this.pickOrder = savedPickOrder
                this.item = item
                this.qty = it.qty
                this.uom = uom
                this.status = PickOrderLineStatus.PENDING
            }
        }
        pickOrderLineRepository.saveAll(polEntries)
        return MessageResponse(
            id = savedPickOrder.id,
            name = savedPickOrder.code,
            code = savedPickOrder.code,
            type = savedPickOrder.type!!.value,
            message = "success",
            errorPosition = "",
        )
    }

    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 getPickOrdersWithStockBalanceByPage(request: SearchPickOrderRequest): RecordsRes<GetPickOrderInfo> {
        val pageable = PageRequest.of(request.pageNum ?: 0, request.pageSize ?: 10);

        // First get the basic pick order info for pagination
        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 pickOrderIds = records.mapNotNull { it.id }

        if (pickOrderIds.isEmpty()) {
            return RecordsRes(emptyList(), 0)
        }

        // Get full pick orders with relationships
        val fullPickOrders = pickOrderRepository.findAllByIdIn(pickOrderIds)
        val groupsByPickOrderId = pickOrderGroupRepository.findByPickOrderIdInAndDeletedIsFalse(pickOrderIds)
        .groupBy { it.pickOrderId }
        // Get all item IDs
        val itemIds = fullPickOrders
            .flatMap { it.pickOrderLines }
            .mapNotNull { it.item?.id }
            .distinct()

        // Get inventory data
        val today = LocalDate.now()
        val zero = BigDecimal.ZERO

        val inventories = if (itemIds.isNotEmpty()) {
            inventoryLotLineService
                .allInventoryLotLinesByItemIdIn(itemIds)
                .filter { it.status == InventoryLotLineStatus.AVAILABLE.value }
                .filter { (it.inQty ?: zero).minus(it.outQty ?: zero).minus(it.holdQty ?: zero) > zero }
                .filter { it.expiryDate.isAfter(today) || it.expiryDate.isEqual(today) }
                .groupBy { it.item?.id }
        } else {
            emptyMap()
        }

        // Build response (removed suggestions)
        val pickOrderInfos = fullPickOrders.map { po ->
            val pickOrderLineInfos = po.pickOrderLines.map { pol ->
                val inventory = pol.item?.id?.let { inventories[it] }

                GetPickOrderLineInfo(
                    id = pol.id,
                    itemId = pol.item?.id,
                    itemCode = pol.item?.code,
                    itemName = pol.item?.name,
                    availableQty = inventory?.sumOf { i -> (i.availableQty ?: zero) },
                    requiredQty = pol.qty,
                    uomCode = pol.uom?.code,
                    uomDesc = pol.uom?.udfudesc,
                    suggestedList = emptyList() // Empty list since you don't need suggestions
                )
            }
            val groupName = po.id?.let { pickOrderId ->
                groupsByPickOrderId[pickOrderId]?.firstOrNull()?.name
            } ?: "No Group"
            GetPickOrderInfo(
                id = po.id,
                code = po.code,
                consoCode = po.consoCode,
                targetDate = po.targetDate,
                type = po.type?.value,
                status = po.status?.value,
                assignTo = po.assignTo?.id,
                groupName = groupName,
                pickOrderLines = pickOrderLineInfos
            )
        }

        val total = response.totalElements
        return RecordsRes(pickOrderInfos, total.toInt())
    }

    open fun assignPickOrders(pickOrderIds: List<Long>, assignTo: Long): MessageResponse {
        try {
            val pickOrders = pickOrderRepository.findAllById(pickOrderIds)
            val user = userService.find(assignTo).orElse(null) // 获取用户对象

            pickOrders.forEach { pickOrder ->
                pickOrder.assignTo = user  // 使用 User 对象而不是 Long
                pickOrder.status = PickOrderStatus.ASSIGNED  // 使用枚举值
            }

            pickOrderRepository.saveAll(pickOrders)

            return MessageResponse(
                id = null,
                name = "Pick orders assigned successfully",
                code = "SUCCESS",
                type = "pickorder",
                message = "Pick orders assigned successfully",
                errorPosition = null
            )
        } catch (e: Exception) {
            return MessageResponse(
                id = null,
                name = "Failed to assign pick orders",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to assign pick orders: ${e.message}",
                errorPosition = null
            )
        }
    }

    open fun releasePickOrders(pickOrderIds: List<Long>, assignTo: Long): MessageResponse {
        try {
            val pickOrders = pickOrderRepository.findAllById(pickOrderIds)
            val user = userService.find(assignTo).orElse(null) // 获取用户对象

            pickOrders.forEach { pickOrder ->
                pickOrder.assignTo = user  // 使用 User 对象而不是 Long
                pickOrder.status = PickOrderStatus.RELEASED  // 使用枚举值
            }

            pickOrderRepository.saveAll(pickOrders)

            return MessageResponse(
                id = null,
                name = "Pick orders released successfully",
                code = "SUCCESS",
                type = "pickorder",
                message = "Pick orders released successfully",
                errorPosition = null
            )
        } catch (e: Exception) {
            return MessageResponse(
                id = null,
                name = "Failed to release pick orders",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to release pick orders: ${e.message}",
                errorPosition = null
            )
        }
    }


    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 ");
        }
        if (args.containsKey("assignTo")) {
            sql.append(" AND po.assignTo = :assignTo ");
        }
        sql.append(" group by po.consoCode, po.releasedDate, po.status, po.assignTo ")
        return jdbcDao.queryForList(sql.toString(), args);
    }

    open fun assignPickCode(): String {
        val suffixFormat = "%03d"
        val pattern = "yyyyMMdd"
        val formatter = DateTimeFormatter.ofPattern(pattern)

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

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

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

    // 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"
                    + " po.status as poStatus,"
                    + " pol.id, "
                    + " i.name as itemName, "
                    + " pol.qty as qty, "
                    + " max(uc.code) as uom, "
                    + " group_concat(ill.id) as lotLineId, "
                    + " group_concat(w.name) as warehouse, "
                    + " group_concat(il.lotNo) as suggestedLotNo, "
                    + " pol.status "
                    + " 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 po.status, pol.id, i.name, pol.qty, pol.status "
        )
        return jdbcDao.queryForList(sql.toString(), args);
    }

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

        // 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

            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 salesUnit = inventoryLotLine?.inventoryLot?.item?.id?.let { _itemId ->
                        itemUomService.findSalesUnitByItemId(_itemId)
                    }
                    val ratio = BigDecimal.ONE //(salesUnit?.ratioN ?: zero).divide(salesUnit?.ratioD ?: one)

                    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 ?: zero).multiply(ratio),
                        outQty = (inventoryLotLine?.outQty ?: zero).multiply(ratio),
                        holdQty = (inventoryLotLine?.holdQty ?: zero).multiply(ratio),
                        remainingQty = remainingQty.multiply(ratio),
                        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 today = LocalDate.now()
        val zero = BigDecimal.ZERO
        val one = BigDecimal.ONE
        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)
        val inventories = inventoryLotLineService
            .allInventoryLotLinesByItemIdIn(itemIds)
            .filter { it.status == InventoryLotLineStatus.AVAILABLE.value }
            .filter { (it.inQty ?: zero).minus(it.outQty ?: zero).minus(it.holdQty ?: zero) > zero }
            .filter { it.expiryDate.isAfter(today) || it.expiryDate.isEqual(today) }
            .sortedBy { it.expiryDate }
            .groupBy { it.item?.id }

        // Pick Orders
        val releasePickOrderInfos = pos
            .map { po ->
                val releasePickOrderLineInfos = po.pickOrderLines.map { pol ->
//                    if (pol.item?.id != null && pol.item!!.id!! > 0) {
                    val inventory = pol.item?.id?.let { inventories[it] }
                    val itemUom = pol.item?.id?.let { itemUomService.findSalesUnitByItemId(it) }
//                    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,
                        availableQty = inventory?.sumOf { i -> (i.availableQty ?: zero) },
//                        availableQty = inventory?.sumOf { i -> (i.availableQty ?: zero) * (itemUom?.ratioN ?: one) * (itemUom?.ratioD ?: one) },
                        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 }

            val inventory = item.first?.let { inventories[it] }
            val itemUom = item.first?.let { itemUomService.findSalesUnitByItemId(it) }

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

                it.availableQty = inventory?.sumOf { i -> i.availableQty ?: zero }
//                it.availableQty = inventory?.sumOf { i -> (i.availableQty ?: zero) * (itemUom?.ratioN ?: one) * (itemUom?.ratioD ?: one) }

                // return
                it
            }
        }

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

    // TODO:
    open fun getPickOrdersInfo(ids: List<Long>): GetPickOrderInfoResponse {
        val today = LocalDate.now()
        val zero = BigDecimal.ZERO
        val one = BigDecimal.ONE
        val pos = pickOrderRepository.findAllByIdIn(ids)
        println(pos)
        val groupsByPickOrderId = pickOrderGroupRepository.findByPickOrderIdInAndDeletedIsFalse(ids)
        .groupBy { it.pickOrderId }
        
        // 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 = inventoryLotLineService
            .allInventoryLotLinesByItemIdIn(itemIds)
            .filter { it.status == InventoryLotLineStatus.AVAILABLE.value }
            .filter { (it.inQty ?: zero).minus(it.outQty ?: zero).minus(it.holdQty ?: zero) > zero }
            .filter { it.expiryDate.isAfter(today) || it.expiryDate.isEqual(today) }
            .sortedBy { it.expiryDate }
            .groupBy { it.item?.id }
    
        val suggestions =
            suggestedPickLotService.suggestionForPickOrders(SuggestedPickLotForPoRequest(pickOrders = pos))
        
        // ✅ Get stock out line data for each pick order line
        val pickOrderLineIds = pos.flatMap { it.pickOrderLines }.mapNotNull { it.id }
        val stockOutLinesByPickOrderLineId = if (pickOrderLineIds.isNotEmpty()) {
            pickOrderLineIds.associateWith { pickOrderLineId ->
                stockOutLIneRepository.findAllByPickOrderLineIdAndDeletedFalse(pickOrderLineId)
            }
        } else {
            emptyMap()
        }
    
        // ✅ Pre-calculate available quantities for each item ONCE
        val itemAvailableQtyMap = mutableMapOf<Long, BigDecimal>()
        itemIds.forEach { itemId ->
            val inventory = inventories[itemId]
            val totalAvailableQty = inventory?.sumOf { i -> 
                val inQty = i.inQty ?: zero
                val outQty = i.outQty ?: zero
                val holdQty = i.holdQty ?: zero
                inQty.minus(outQty).minus(holdQty)
            } ?: zero
            itemAvailableQtyMap[itemId] = totalAvailableQty
        }
    
        // Pick Orders
        val releasePickOrderLineInfos = pos
            .map { po ->
                val releasePickOrderLineInfos = po.pickOrderLines.map { pol ->
                    val itemId = pol.item?.id
                    val availableQty = itemId?.let { itemAvailableQtyMap[it] } ?: zero
                    
                    // ✅ Move stockOutLines declaration inside the pol loop
                    val stockOutLines = stockOutLinesByPickOrderLineId[pol.id] ?: emptyList()
                    
                    // ✅ Calculate total picked quantity from stock out lines
                    println("=== PICKED QTY DEBUG: Line ${pol.id} ===")
                    println("Stock Out Lines: ${stockOutLines.map { "${it.id}(status=${it.status}, qty=${it.qty})" }}")
                    
                    val totalPickedQty = stockOutLines
                    .sumOf { it.qty ?: zero }
                    
                    println("Total Picked Qty: $totalPickedQty")
                    println("=== END DEBUG ===")
                    
                    // Return
                    GetPickOrderLineInfo(
                        id = pol.id,
                        itemId = pol.item?.id,
                        itemCode = pol.item?.code,
                        itemName = pol.item?.name,
                        availableQty = availableQty, // ✅ Use pre-calculated value
                        requiredQty = pol.qty,
                        uomCode = pol.uom?.code,
                        uomDesc = pol.uom?.udfudesc,
                        suggestedList = suggestions.suggestedList.filter { it.pickOrderLine?.id == pol.id },
                        pickedQty = totalPickedQty
                    )
                }
                val groupName = po.id?.let { pickOrderId ->
                    groupsByPickOrderId[pickOrderId]?.firstOrNull()?.name
                } ?: "No Group"
                // Return
                GetPickOrderInfo(
                    id = po.id,
                    code = po.code, 
                    consoCode = po.consoCode,
                    targetDate = po.targetDate,
                    type = po.type?.value,
                    status = po.status?.value,
                    assignTo = po.assignTo?.id,
                    groupName = groupName,
                    pickOrderLines = releasePickOrderLineInfos
                )
            }
    
        // Items
        val currentInventoryInfos = requiredItems.map { item ->
            val inventory = item.first?.let { inventories[it] }
            val itemUom = item.first?.let { itemUomService.findSalesUnitByItemId(it) }
    
            item.second.let {
                val convertedAvailableQty = inventory?.sumOf { i -> 
                    val baseQty = (i.availableQty ?: zero)
                    baseQty
                }
                
                it.availableQty = convertedAvailableQty
                it
            }
        }
        val consoCode = pos.firstOrNull()?.consoCode
        return GetPickOrderInfoResponse(
            consoCode = consoCode,
            pickOrders = releasePickOrderLineInfos,
            items = currentInventoryInfos,
        )
    }

    open fun getAllPickOrdersInfo(userId: Long? = null): GetPickOrderInfoResponse {
        // 使用现有的查询方法获取所有 Pick Orders，然后在内存中过滤
        val allPickOrders = pickOrderRepository.findAll()
        val releasedPickOrderIds = allPickOrders
            .filter { it.status == PickOrderStatus.RELEASED }
            .filter { userId == null || it.assignTo?.id == userId } // ✅ Fixed: now userId is a parameter
            .map { it.id!! }
    
        println("All released pick order IDs for user $userId: $releasedPickOrderIds")
    
        // 如果没有任何已发布的 Pick Orders，返回空结果
        if (releasedPickOrderIds.isEmpty()) {
            return GetPickOrderInfoResponse(
                consoCode = null,
                pickOrders = emptyList(),
                items = emptyList()
            )
        }
    
        // 重用现有的 getPickOrdersInfo 方法
        return getPickOrdersInfo(releasedPickOrderIds)
    }

    open fun getPickOrderLineLotDetails(pickOrderLineId: Long): List<Map<String, Any>> {
        val today = LocalDate.now()

        println("=== Debug: getPickOrderLineLotDetails ===")
        println("pickOrderLineId: $pickOrderLineId")
        println("today: $today")

        val sql = """
        SELECT 
            ill.id as lotId,
            il.lotNo,
            il.expiryDate,
            w.name as location,
            COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as availableQty,
            COALESCE(spl.qty, 0) as requiredQty, -- ✅ 使用COALESCE处理null值
            COALESCE(ill.inQty, 0) as inQty, 
            COALESCE(ill.outQty, 0) as outQty,
            COALESCE(ill.holdQty, 0) as holdQty,
            COALESCE(sol.qty, 0) as actualPickQty,
            COALESCE(spl.id, 0) as suggestedPickLotId, -- ✅ 使用COALESCE处理null值
            ill.status as lotStatus,
            sol.id as stockOutLineId,
            sol.status as stockOutLineStatus,
            sol.qty as stockOutLineQty,
            COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId, -- ✅ 使用COALESCE处理null值
            ill.inventoryLotId as debugInventoryLotId,
            -- ✅ Calculate total picked quantity by ALL pick orders for this lot
            COALESCE((
                SELECT SUM(sol_all.qty) 
                FROM fpsmsdb.stock_out_line sol_all 
                WHERE sol_all.inventoryLotLineId = ill.id 
                AND sol_all.deleted = false
                AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
            ), 0) as totalPickedByAllPickOrders,
            -- ✅ FIXED: Calculate remaining available quantity correctly
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
            -- ✅ Add detailed debug fields for lotAvailability calculation
            ill.status as debug_ill_status,
            (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) as debug_is_expired,
            sol.status as debug_sol_status,
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as debug_remaining_stock,
            (COALESCE(spl.qty, 0) - COALESCE(sol.qty, 0)) as debug_required_after_picked,
            CASE 
                -- ✅ FIXED: Check if lot is expired
                WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                -- ✅ FIXED: Check if lot has rejected stock out line for this pick order
                WHEN sol.status = 'rejected' THEN 'rejected'
                -- ✅ FIXED: Check if lot is unavailable due to insufficient stock
                WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                -- ✅ FIXED: Check if lot status is unavailable
                WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                -- ✅ Default to available
                ELSE 'available'
            END as lotAvailability
        FROM fpsmsdb.inventory_lot_line ill
        JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
        LEFT JOIN fpsmsdb.item_uom sales_iu ON sales_iu.itemId = il.itemId AND sales_iu.salesUnit = true AND sales_iu.deleted = false
        LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = sales_iu.uomId
        -- ✅ FIXED: Include both suggested lots AND lots with stock out lines for this pick order
        LEFT JOIN fpsmsdb.suggested_pick_lot spl ON spl.suggestedLotLineId = ill.id AND spl.pickOrderLineId = :pickOrderLineId
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = :pickOrderLineId AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
        -- ✅ FIXED: Only include lots that are either suggested OR have stock out lines for this pick order
        WHERE (spl.pickOrderLineId = :pickOrderLineId OR sol.pickOrderLineId = :pickOrderLineId)
        AND(sol.status IS NULL OR sol.status != 'completed')
        AND ill.deleted = false
        AND il.deleted = false
        ORDER BY 
            CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END, -- ✅ Show rejected lots first
            il.expiryDate ASC, 
            il.lotNo ASC
    """.trimIndent()

        println("🔍 Executing SQL for lot details: $sql")
        println("🔍 With parameters: pickOrderLineId = $pickOrderLineId")

        val result = jdbcDao.queryForList(sql, mapOf("pickOrderLineId" to pickOrderLineId))

        // ✅ Add detailed debug output for each lot
        println("=== DETAILED LOT AVAILABILITY DEBUG ===")
        result.forEach { row ->
            val lotId = row["lotId"]
            val lotNo = row["lotNo"]
            val illStatus = row["debug_ill_status"]
            val isExpired = row["debug_is_expired"]
            val solStatus = row["debug_sol_status"]
            val lotAvailability = row["lotAvailability"]
            
            println("--- Lot: $lotNo (ID: $lotId) ---")
            println("  ill.status: $illStatus")
            println("  is_expired: $isExpired")
            println("  sol.status: $solStatus")
            println("  lotAvailability: $lotAvailability")
            
            // ✅ Check each condition step by step
            if (isExpired == true) {
                println("  ❌ FAILED: lot is expired")
            } else if (solStatus == "rejected") {
                println("  ❌ FAILED: sol.status = 'rejected'")
            } else {
                println("  ✅ PASSED: All conditions met, should be 'available'")
            }
            println("  ---")
        }
        println("=== END DETAILED DEBUG ===")

        // ✅ Filter out completed lots
        val filteredResult = result.filter { row ->
            val stockOutLineStatus = row["stockOutLineStatus"] as String?
            val stockOutLineQty = row["stockOutLineQty"] as Number?
            val requiredQty = row["requiredQty"] as Number?
            
            // Show lot if:
            // 1. No stock out line exists, OR
            // 2. Stock out line is not completed, OR  
            // 3. Stock out line qty doesn't equal required qty
            stockOutLineStatus != "completed" || 
            stockOutLineQty?.toDouble() != requiredQty?.toDouble()
        }
        println("Final result count: ${filteredResult.size}")
        filteredResult.forEach { row ->
            println("Final Row: $row")
        }

        return filteredResult
    }

    @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)
        suggestions.suggestedList.forEach {
            println("suggest ${it.suggestedLotLine?.id}")
            println("pick ${it.pickOrderLine?.id}")
        }
        suggestedPickLotService.saveAll(suggestions.suggestedList)

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

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

        logger.info(saveSuggestedPickLots)
        saveSuggestedPickLots.forEach { lot ->
//            val ratio = lot.suggestedLotLine!!.stockUom
            if (lot.suggestedLotLine != null && lot.suggestedLotLine?.id != null && lot.suggestedLotLine!!.id!! > 0) {
                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)
    }

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun releaseAssignedPickOrders(pickOrderIds: List<Long>, assignTo: Long): MessageResponse {
        try {
            val zero = BigDecimal.ZERO
            val releasedBy = SecurityUtils.getUser().getOrNull()
            val user = userService.find(assignTo).orElse(null)
            
            val pickOrders = pickOrderRepository.findAllByIdInAndStatus(pickOrderIds, PickOrderStatus.ASSIGNED)
            
            if (pickOrders.isEmpty()) {
                return MessageResponse(
                    id = null,
                    name = "No assigned pick orders found",
                    code = "ERROR",
                    type = "pickorder",
                    message = "No assigned pick orders found with the provided IDs",
                    errorPosition = null
                )
            }
            
            // ← GENERATE CONSOCODE EARLY AND SAVE IMMEDIATELY
            val newConsoCode = assignConsoCode()
            val currUser = SecurityUtils.getUser().orElseThrow()
            
            // Create and save StockOut immediately to prevent duplicate consoCodes
            val stockOut = StockOut().apply {
                this.type = "job"
                this.consoPickOrderCode = newConsoCode
                this.status = StockOutStatus.PENDING.status
                this.handler = currUser.id
            }
            val savedStockOut = stockOutRepository.saveAndFlush(stockOut)  // ← FLUSH to commit immediately
            
            // Update pick orders
            pickOrders.forEach { pickOrder ->
                pickOrder.apply {
                    this.releasedBy = releasedBy
                    status = PickOrderStatus.RELEASED
                    this.assignTo = user
                    this.consoCode = newConsoCode  // ← Also assign consoCode to pick orders
                }
            }
            
            val suggestions = suggestedPickLotService.suggestionForPickOrders(
                SuggestedPickLotForPoRequest(pickOrders = pickOrders)
            )
            
            val saveSuggestedPickLots = suggestedPickLotService.saveAll(suggestions.suggestedList)

            pickOrderRepository.saveAll(pickOrders)
                
            val inventoryLotLines = inventoryLotLineRepository.findAllByIdIn(
                saveSuggestedPickLots.mapNotNull { it.suggestedLotLine?.id }
            )
            saveSuggestedPickLots.forEach { lot ->
                val lotLineId = lot.suggestedLotLine?.id
                if (lotLineId != null) {
                    val lineIndex = inventoryLotLines.indexOf(lot.suggestedLotLine)
                    if (lineIndex >= 0) {
                        val currentHold = inventoryLotLines[lineIndex].holdQty ?: zero
                        val addHold = lot.qty ?: zero
                        inventoryLotLines[lineIndex].holdQty = currentHold.plus(addHold)
                    }
                }
            }
            logger.info("saveSuggestedPickLots: $saveSuggestedPickLots")
            inventoryLotLineRepository.saveAll(inventoryLotLines)
            var precreated = 0
saveSuggestedPickLots.forEach { lot ->
    val polId = lot.pickOrderLine?.id
    val illId = lot.suggestedLotLine?.id
    if (polId != null && illId != null) {
        try {
            val existingLines = stockOutLIneRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
            if (existingLines.isEmpty()) {
                val pickOrderLine = pickOrderLineRepository.findById(polId).orElse(null)
                val inventoryLotLine = inventoryLotLineRepository.findById(illId).orElse(null)
                
                if (pickOrderLine != null && inventoryLotLine != null) {
                    val line = com.ffii.fpsms.modules.stock.entity.StockOutLine().apply {
                        this.stockOut = savedStockOut
                        this.pickOrderLine = pickOrderLine
                        this.inventoryLotLine = inventoryLotLine
                        this.item = pickOrderLine.item  // ✅ Add the required item field
                        this.status = com.ffii.fpsms.modules.stock.web.model.StockOutLineStatus.PENDING.status
                        this.qty = 0.0
                    }
                    stockOutLIneRepository.save(line)
                    precreated++
                } else {
                    logger.warn("Could not find pickOrderLine (id=$polId) or inventoryLotLine (id=$illId)")
                }
            }
        } catch (e: Exception) {
            logger.warn("Failed to create stock out line for pickOrderLineId=$polId, inventoryLotLineId=$illId: ${e.message}")
        }
    }
}
logger.info("Precreated $precreated stock out lines for suggested lots on release.")
            return MessageResponse(
                id = null,
                name = "Pick orders released successfully with inventory management",
                code = "SUCCESS",
                type = "pickorder",
                message = "Pick orders released successfully with inventory management",
                errorPosition = null,
                entity = mapOf("consoCode" to newConsoCode)  // ← Return the generated consoCode
            )
            
        } catch (e: Exception) {
            return MessageResponse(
                id = null,
                name = "Failed to release pick orders",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to release pick orders: ${e.message}",
                errorPosition = null
            )
        }
    }
    
    @Throws(IOException::class)
    @Transactional
    open fun completeStockOut(consoCode: String): MessageResponse {
        println("=== DEBUG: completeStockOut ===")
        println("consoCode: $consoCode")
        
        val stockOut = stockOutRepository.findByConsoPickOrderCode(consoCode).orElseThrow()
        println("StockOut ID: ${stockOut.id}")
        
        // ✅ FIXED: 直接通过 consoCode 查找相关的 stock out lines，而不依赖 stockOutId 关联
        val stockOutLinesSql = """
            SELECT sol.*
            FROM stock_out_line sol
            JOIN pick_order_line pol ON pol.id = sol.pickOrderLineId
            JOIN pick_order po ON po.id = pol.poId
            WHERE po.consoCode = :consoCode
            AND sol.deleted = false
        """.trimIndent()
        
        val stockOutLinesResult = jdbcDao.queryForList(stockOutLinesSql, mapOf("consoCode" to consoCode))
        println("=== DEBUG: Stock Out Lines for consoCode $consoCode ===")
        stockOutLinesResult.forEach { row ->
            println("StockOutLine ID: ${row["id"]}, stockOutId: ${row["stockOutId"]}, status: ${row["status"]}, qty: ${row["qty"]}, pickOrderLineId: ${row["pickOrderLineId"]}")
        }
        
        // ✅ 将结果转换为 StockOutLine 对象
        val stockOutLineIds = stockOutLinesResult.mapNotNull { row ->
            val id = row["id"]
            println("Raw ID value: $id (type: ${id?.javaClass?.simpleName})")
            val convertedId = when (id) {
                is Number -> id.toLong()
                is String -> id.toLongOrNull()
                else -> null
            }
            println("Converted ID: $convertedId")
            convertedId
        }
        
        val stockOutLines = if (stockOutLineIds.isNotEmpty()) {
            stockOutLIneRepository.findAllById(stockOutLineIds)
        } else {
            emptyList()
        }
        
        println("Total stock out lines for consoCode $consoCode: ${stockOutLines.size}")
        
        stockOutLines.forEach { line ->
            println("Stock out line ${line.id}: status=${line.status}, qty=${line.qty}")
        }
        
        val unfinishedLines = stockOutLines.filter {
            it.status != StockOutLineStatus.COMPLETE.status
                    && it.status != StockOutLineStatus.REJECTED.status
        }
        
        println("Unfinished lines: ${unfinishedLines.size}")
        
        if (unfinishedLines.isEmpty()) {
            stockOut.apply {
                this.status = StockOutStatus.COMPLETE.status
            }
            val savedStockOut = stockOutRepository.saveAndFlush(stockOut)
            
            // ✅ NEW APPROACH: Update through relationship chain
            // 1. Get all pick order lines from completed stock out lines
            val completedPickOrderLineIds = stockOutLines.mapNotNull { it.pickOrderLine?.id }
            println("Completed pick order line IDs: $completedPickOrderLineIds")
            
            // 2. Get all pick order lines and update their status
            if (completedPickOrderLineIds.isNotEmpty()) {
                val pickOrderLines = pickOrderLineRepository.findAllById(completedPickOrderLineIds)
                pickOrderLines.forEach { line ->
                    line.status = PickOrderLineStatus.COMPLETED
                    println("Updated pick order line ${line.id} to COMPLETED")
                }
                pickOrderLineRepository.saveAll(pickOrderLines)
                println("✅ Updated ${pickOrderLines.size} pick order lines to COMPLETED status")
                
                // 3. Get all unique pick order IDs from the lines
                val pickOrderIds = pickOrderLines.mapNotNull { it.pickOrder?.id }.distinct()
                println("Affected pick order IDs: $pickOrderIds")
                
                // 4. Check if each pick order is fully completed
                pickOrderIds.forEach { pickOrderId ->
                    val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
                    if (pickOrder != null) {
                        // Check if all lines of this pick order are completed
                        val allLines = pickOrder.pickOrderLines
                        val completedLines = allLines.filter { it.status == PickOrderLineStatus.COMPLETED }
                        
                        println("Pick order ${pickOrder.code}: ${completedLines.size}/${allLines.size} lines completed")
                        
                        if (completedLines.size == allLines.size && allLines.isNotEmpty()) {
                            // All lines are completed, update pick order status
                            pickOrder.status = PickOrderStatus.COMPLETED
                            pickOrder.completeDate = LocalDateTime.now()
                            pickOrderRepository.save(pickOrder)
                            println("✅ Updated pick order ${pickOrder.code} to COMPLETED status")
                            val removedCount = doPickOrderService.removeDoPickOrdersForPickOrder(pickOrderId)
                            println("✅ Removed $removedCount do_pick_order records for completed pick order ${pickOrderId}")
                            
                            // ✅ Update do_pick_order_record status to completed (don't remove)
                            doPickOrderService.completeDoPickOrderRecordsForPickOrder(pickOrderId)
                            println("✅ Updated do_pick_order_record status to COMPLETED for pick order ${pickOrderId}")
                        }
                    }
                }
                // After updating pick order to COMPLETED status, add:

            }
            
            return MessageResponse(
                id = savedStockOut.id,
                name = savedStockOut.consoPickOrderCode ?: savedStockOut.deliveryOrderCode,
                code = savedStockOut.consoPickOrderCode ?: savedStockOut.deliveryOrderCode,
                type = savedStockOut.type,
                message = "completed",
                errorPosition = null,
            )
        } else {
            println("❌ Still have unfinished lines, cannot complete")
            return MessageResponse(
                id = stockOut.id,
                name = stockOut.consoPickOrderCode ?: stockOut.deliveryOrderCode,
                code = stockOut.consoPickOrderCode ?: stockOut.deliveryOrderCode,
                type = stockOut.type,
                message = "not completed",
                errorPosition = null,
            )
        }
    }

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun checkAndCompletePickOrderByConsoCode(consoCode: String): MessageResponse {
        try {
            println("=== DEBUG: checkAndCompletePickOrderByConsoCode ===")
            println("consoCode: $consoCode")

            val stockOut = stockOutRepository.findByConsoPickOrderCode(consoCode).orElse(null)
            if (stockOut == null) {
                println("❌ No stock_out found for consoCode: $consoCode")
                return MessageResponse(
                    id = null,
                    name = "Stock out not found",
                    code = "ERROR",
                    type = "pickorder",
                    message = "No stock out found for consoCode: $consoCode",
                    errorPosition = null
                )
            }

            val stockOutLinesSql = """
            SELECT sol.*
            FROM stock_out_line sol
            JOIN pick_order_line pol ON pol.id = sol.pickOrderLineId
            JOIN pick_order po ON po.id = pol.poId
            WHERE po.consoCode = :consoCode
            AND sol.deleted = false
        """.trimIndent()
        
        val stockOutLinesResult = jdbcDao.queryForList(stockOutLinesSql, mapOf("consoCode" to consoCode))
        val stockOutLineIds = stockOutLinesResult.mapNotNull { row ->
            when (val id = row["id"]) {
                is Number -> id.toLong()
                is String -> id.toLongOrNull()
                else -> null
            }
        }
        val stockOutLines = if (stockOutLineIds.isNotEmpty()) {
            stockOutLIneRepository.findAllById(stockOutLineIds)
        } else {
            emptyList()
        }
            val unfinishedLines = stockOutLines.filter {
                it.status != StockOutLineStatus.COMPLETE.status
                        && it.status != StockOutLineStatus.REJECTED.status
            }

            println("📊 Stock out lines: ${stockOutLines.size}, Unfinished: ${unfinishedLines.size}")

            if (unfinishedLines.isEmpty()) {
                println("✅ All stock out lines completed, updating pick order statuses...")
                return completeStockOut(consoCode)
            } else {
                println("⏳ Still have ${unfinishedLines.size} unfinished lines")
                return MessageResponse(
                    id = stockOut.id,
                    name = stockOut.consoPickOrderCode ?: stockOut.deliveryOrderCode,
                    code = "NOT_COMPLETED",
                    type = stockOut.type,
                    message = "Pick order not completed yet, ${unfinishedLines.size} lines remaining",
                    errorPosition = null
                )
            }

        } catch (e: Exception) {
            println("❌ Error in checkAndCompletePickOrderByConsoCode: ${e.message}")
            e.printStackTrace()
            return MessageResponse(
                id = null,
                name = "Failed to check pick order completion",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to check pick order completion: ${e.message}",
                errorPosition = null
            )
        }
    }

    
    open fun createGroup(name: String, targetDate: LocalDate, pickOrderId: Long?): PickOrderGroup {
        val group = PickOrderGroup().apply {
            this.name = name
            this.targetDate = targetDate
            this.pickOrderId = pickOrderId
        }
        return pickOrderGroupRepository.save(group)
    }
    
    fun getGroupsByPickOrderId(pickOrderId: Long): List<PickOrderGroup> {
        return pickOrderGroupRepository.findByPickOrderIdAndDeletedIsFalse(pickOrderId)
    }

    fun getAllGroupsFromDatabase(): List<Map<String, Any>> {
        val sql = """
            SELECT id, name, target_date, pick_order_id 
            FROM pick_order_group 
            WHERE deleted = false 
            ORDER BY name ASC
        """.trimIndent()
        
        return jdbcDao.queryForList(sql, emptyMap<String, Any>())
    }

    open fun allPickOrdersGroup(): List<PickOrderGroupInfo> {
        return pickOrderGroupRepository.findAllByDeletedIsFalse()
    }

    open fun getLatestGroupNameAndCreate(): MessageResponse {
        // Use the working repository method like the old /latest endpoint
        val allGroups = allPickOrdersGroup()
        val nextGroupName = if (allGroups.isNotEmpty()) {
            // Sort by numeric part of the name (A001 -> 1, A006 -> 6)
            val latestName = allGroups
                .filter { it.name?.startsWith("A") == true }
                .maxByOrNull { group -> 
                    group.name?.substring(1)?.toIntOrNull() ?: 0 
                }?.name ?: "A000"
            
            // Generate next group name
            val currentNumber = latestName.substring(1).toIntOrNull() ?: 0
            val nextNumber = currentNumber + 1
            "A${nextNumber.toString().padStart(3, '0')}"
        } else {
            "A001"  // If no groups exist, return A001
        }
        
        // Use the new flexible createNewGroups method
        val request = SavePickOrderGroupRequest(
            names = listOf(nextGroupName),  // Create new group with the generated name
            targetDate = null,
            pickOrderId = null
        )
        
        return createNewGroups(request)
    }
    
    fun getLatestGroupName(): String {
        // Use allPickOrdersGroup() instead of the failing repository method
        val allGroups = allPickOrdersGroup()
        return if (allGroups.isNotEmpty()) {
            // Sort by numeric part of the name (A001 -> 1, A006 -> 6)
            val latestName = allGroups
                .filter { it.name?.startsWith("A") == true }
                .maxByOrNull { group -> 
                    group.name?.substring(1)?.toIntOrNull() ?: 0 
                }?.name ?: "A000"
            
            // Generate next group name
            val currentNumber = latestName.substring(1).toIntOrNull() ?: 0
            val nextNumber = currentNumber + 1
            "A${nextNumber.toString().padStart(3, '0')}"
        } else {
            "A001"  // If no groups exist, return A001
        }
    }

    open fun createNewGroups(request: SavePickOrderGroupRequest): MessageResponse {
        val updatedGroups = mutableListOf<PickOrderGroup>()
        val createdGroups = mutableListOf<PickOrderGroup>()
        
        // Case 1: Update existing groups by IDs
        request.groupIds?.forEach { groupId ->
            val group = pickOrderGroupRepository.findById(groupId).orElse(null)
            if (group != null) {
                group.targetDate = request.targetDate
                group.pickOrderId = request.pickOrderId
                val savedGroup = pickOrderGroupRepository.save(group)
                updatedGroups.add(savedGroup)
            }
        }
        
        // Case 2: Create new groups by names
        request.names?.forEach { name ->
            val group = PickOrderGroup().apply {
                this.name = name
                this.targetDate = request.targetDate
                this.pickOrderId = request.pickOrderId
            }
            val savedGroup = pickOrderGroupRepository.save(group)
            createdGroups.add(savedGroup)
        }
        
        val totalGroups = updatedGroups.size + createdGroups.size
        val allGroups = updatedGroups + createdGroups
        
        return MessageResponse(
            id = allGroups.firstOrNull()?.id,
            name = allGroups.map { it.name ?: "" }.joinToString(", "),
            code = "groups_processed",
            type = "pick_order_groups",
            message = "Updated ${updatedGroups.size} groups and created ${createdGroups.size} groups successfully",
            errorPosition = "",
        )
    }

    // ✅ 添加缺失的方法：从 pick order line ID 获取 pick order ID
    private fun getPickOrderIdFromPickOrderLineId(pickOrderLineId: Long): Long? {
        val sql = """
        SELECT po.id as pickOrderId
        FROM fpsmsdb.pick_order_line pol
        JOIN fpsmsdb.pick_order po ON po.id = pol.poId
        WHERE pol.id = :pickOrderLineId
        """.trimIndent()

        val result = jdbcDao.queryForList(sql, mapOf("pickOrderLineId" to pickOrderLineId))

        return if (result.isNotEmpty()) {
            (result[0]["pickOrderId"] as Number).toLong()
        } else {
            null
        }
    }

    // ✅ 添加检查是否需要 resuggest 的方法
    private fun checkIfNeedsResuggest(pickOrderLineId: Long): Boolean {
        println("🔍 checkIfNeedsResuggest called with pickOrderLineId: $pickOrderLineId")
        
        // ✅ 首先执行一个调试查询来查看实际的数值
        val debugSql = """
        SELECT 
            spl.id as suggestedPickLotId,
            spl.qty as suggestedQty,
            ill.inQty,
            ill.outQty,
            ill.holdQty,
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as baseAvailableQty,
            sales_iu.ratioN,
            sales_iu.ratioD,
--            CASE 
--                WHEN sales_iu.ratioN IS NOT NULL AND sales_iu.ratioD IS NOT NULL AND sales_iu.ratioD != 0 
--                THEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) / (sales_iu.ratioN / sales_iu.ratioD)
--                ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
--            END as convertedAvailableQty
            COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0) as convertedAvailableQty
        FROM fpsmsdb.suggested_pick_lot spl
        JOIN fpsmsdb.inventory_lot_line ill ON ill.id = spl.suggestedLotLineId
        JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.item_uom sales_iu ON sales_iu.itemId = il.itemId AND sales_iu.salesUnit = true AND sales_iu.deleted = false
        WHERE spl.pickOrderLineId = :pickOrderLineId
        """.trimIndent()
        
        println("🔍 Debug SQL: $debugSql")
        val debugResult = jdbcDao.queryForList(debugSql, mapOf("pickOrderLineId" to pickOrderLineId))
        println("🔍 Debug result:")
        debugResult.forEach { row ->
            println("  SuggestedPickLotId: ${row["suggestedPickLotId"]}")
            println("  SuggestedQty: ${row["suggestedQty"]}")
            println("  InQty: ${row["inQty"]}")
            println("  OutQty: ${row["outQty"]}")
            println("  HoldQty: ${row["holdQty"]}")
            println("  BaseAvailableQty: ${row["baseAvailableQty"]}")
            println("  RatioN: ${row["ratioN"]}")
            println("  RatioD: ${row["ratioD"]}")
            println("  ConvertedAvailableQty: ${row["convertedAvailableQty"]}")
            // ✅ 修复：正确处理 Number 类型转换
            val convertedAvailableQty = (row["convertedAvailableQty"] as Number).toDouble()
            val suggestedQty = (row["suggestedQty"] as Number).toDouble()
            println("  Is Insufficient: ${convertedAvailableQty < suggestedQty}")
            println("  ---")
        }
        
        val checkSql = """
        SELECT 
            COUNT(*) as totalSuggestions,
            COUNT(CASE WHEN ill.status != 'available' THEN 1 END) as unavailableLots,
            COUNT(CASE WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 1 END) as expiredLots,
            -- ✅ 修复：使用销售单位进行比较
--            COUNT(CASE WHEN 
--                CASE 
--                    WHEN sales_iu.ratioN IS NOT NULL AND sales_iu.ratioD IS NOT NULL AND sales_iu.ratioD != 0 
--                    THEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) / (sales_iu.ratioN / sales_iu.ratioD)
--                    ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
--                END < spl.qty 
--            THEN 1 END) as insufficientStockLots,
            COUNT(CASE WHEN 
                (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) < spl.qty 
            THEN 1 END) as insufficientStockLots,
            COUNT(CASE WHEN sol.status IN ('rejected', 'lot-change', 'determine1') THEN 1 END) as problematicStockOutLines,
            -- ✅ 检查单位不一致问题
            COUNT(CASE WHEN spl.qty > pol.qty * 100 THEN 1 END) as unitMismatchLots
        FROM fpsmsdb.suggested_pick_lot spl
        JOIN fpsmsdb.inventory_lot_line ill ON ill.id = spl.suggestedLotLineId
        JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        JOIN fpsmsdb.pick_order_line pol ON pol.id = spl.pickOrderLineId
        -- ✅ 添加销售单位转换
        LEFT JOIN fpsmsdb.item_uom sales_iu ON sales_iu.itemId = il.itemId AND sales_iu.salesUnit = true AND sales_iu.deleted = false
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.inventoryLotLineId = ill.id 
            AND sol.pickOrderLineId = spl.pickOrderLineId
        WHERE spl.pickOrderLineId = :pickOrderLineId
        """.trimIndent()

        println("🔍 Executing SQL: $checkSql")
        println("🔍 With parameters: pickOrderLineId = $pickOrderLineId")

        val result = jdbcDao.queryForList(checkSql, mapOf("pickOrderLineId" to pickOrderLineId))
        
        println("🔍 SQL result size: ${result.size}")

        if (result.isNotEmpty()) {
            val row = result[0]
            val totalSuggestions = (row["totalSuggestions"] as Number).toInt()
            val unavailableLots = (row["unavailableLots"] as Number).toInt()
            val expiredLots = (row["expiredLots"] as Number).toInt()
            val insufficientStockLots = (row["insufficientStockLots"] as Number).toInt()
            val problematicStockOutLines = (row["problematicStockOutLines"] as Number).toInt()
            val unitMismatchLots = (row["unitMismatchLots"] as Number).toInt()

            println("=== Resuggest Check ===")
            println("Total suggestions: $totalSuggestions")
            println("Unavailable lots: $unavailableLots")
            println("Expired lots: $expiredLots")
            println("Insufficient stock lots: $insufficientStockLots")
            println("Problematic stock out lines: $problematicStockOutLines")
            println("Unit mismatch lots: $unitMismatchLots")

            // 检查是否需要 resuggest 的条件
            val needsResuggest = (
                unavailableLots > 0 || 
                expiredLots > 0 || 
                insufficientStockLots > 0 || 
                problematicStockOutLines > 0 ||
                unitMismatchLots > 0 ||  // ✅ 单位不一致检查
                totalSuggestions == 0  // 没有建议也需要 resuggest
            )

            println("Needs resuggest: $needsResuggest")
            return needsResuggest
        } else {
            println("🔍 No results returned from SQL query")
        }

        return false
    }


    // Add this new method to PickOrderService.kt
@Transactional(rollbackFor = [java.lang.Exception::class])
open fun autoAssignAndReleasePickOrderByStoreAndTicket(storeId: String, ticketNo: String, userId: Long): MessageResponse {
    try {
        println("=== DEBUG: autoAssignAndReleasePickOrderByStoreAndTicket ===")
        println("storeId: $storeId, ticketNo: $ticketNo, userId: $userId")

        val zero = BigDecimal.ZERO
        val releasedBy = SecurityUtils.getUser().getOrNull()
        val user = userService.find(userId).orElse(null)
        
        if (user == null) {
            return MessageResponse(
                id = null, 
                name = "User not found", 
                code = "ERROR", 
                type = "pickorder",
                message = "User with ID $userId not found", 
                errorPosition = null
            )
        }

        // Find the do_pick_order by store_id and ticket_no
        val sql = """
            SELECT DISTINCT dpo.pick_order_id AS pickOrderId
            FROM do_pick_order dpo
            WHERE dpo.deleted = false
              AND dpo.store_id = :storeId
              AND dpo.ticket_no = :ticketNo
              AND dpo.pick_order_id IS NOT NULL
        """.trimIndent()
        
        val idRows = jdbcDao.queryForList(sql, mapOf("storeId" to storeId, "ticketNo" to ticketNo))
        val pickOrderIdsByTicket = idRows.mapNotNull { row ->
            when (val id = row["pickOrderId"]) {
                is Number -> id.toLong()
                is String -> id.toLongOrNull()
                else -> null
            }
        }.toSet()
        
        println("Candidate pickOrderIds by ticket $ticketNo: $pickOrderIdsByTicket")

        if (pickOrderIdsByTicket.isEmpty()) {
            return MessageResponse(
                id = null, 
                name = "No pick orders found", 
                code = "NO_ORDERS", 
                type = "pickorder",
                message = "No pick orders found for store $storeId with ticket $ticketNo", 
                errorPosition = null
            )
        }
        
        // Check if user already has pick orders in progress
        val userExistingOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
            user,
            listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED)
        ).filter { it.deliveryOrder != null }

        if (userExistingOrders.isNotEmpty()) {
            println("🔍 DEBUG: User $userId already has ${userExistingOrders.size} pick orders in progress")
            return MessageResponse(
                id = null, 
                name = "User already has pick orders", 
                code = "USER_BUSY", 
                type = "pickorder",
                message = "User $userId already has ${userExistingOrders.size} pick orders in progress. Cannot assign new orders.", 
                errorPosition = null,
                entity = mapOf(
                    "existingOrders" to userExistingOrders.map { mapOf(
                        "id" to it.id,
                        "code" to it.code,
                        "status" to it.status?.value
                    )}
                )
            )
        }

        // Find available pick orders for the specific ticket
        val availablePickOrders = pickOrderRepository
            .findAll()
            .filter { it.id != null && pickOrderIdsByTicket.contains(it.id!!) }
            .filter { it.deliveryOrder != null }
            .filter { it.assignTo == null }
            .filter { it.status == PickOrderStatus.PENDING || it.status == PickOrderStatus.RELEASED }
            .sortedBy { it.targetDate }
            .take(1) // Take only one pick order

        if (availablePickOrders.isEmpty()) {
            return MessageResponse(
                id = null, 
                name = "No available pick orders", 
                code = "NO_ORDERS", 
                type = "pickorder",
                message = "No unassigned pick orders available for store $storeId with ticket $ticketNo", 
                errorPosition = null
            )
        }

        val selected = availablePickOrders.first()
        val currUser = SecurityUtils.getUser().orElseThrow()

        // If still PENDING, perform full release flow
        if (selected.status == PickOrderStatus.PENDING) {
            val newConsoCode = assignConsoCode()

            val stockOut = StockOut().apply {
                this.type = "job"
                this.consoPickOrderCode = newConsoCode
                this.status = StockOutStatus.PENDING.status
                this.handler = currUser.id
            }
            val savedStockOut = stockOutRepository.saveAndFlush(stockOut)

            selected.apply {
                this.releasedBy = releasedBy
                status = PickOrderStatus.RELEASED
                this.consoCode = newConsoCode
            }

            val suggestions = suggestedPickLotService.suggestionForPickOrders(
                SuggestedPickLotForPoRequest(pickOrders = listOf(selected))
            )
            val saveSuggestedPickLots = suggestedPickLotService.saveAll(suggestions.suggestedList)
            pickOrderRepository.saveAndFlush(selected)

            val inventoryLotLines = inventoryLotLineRepository.findAllByIdIn(
                saveSuggestedPickLots.mapNotNull { it.suggestedLotLine?.id }
            )
            saveSuggestedPickLots.forEach { lot ->
                val lotLineId = lot.suggestedLotLine?.id
                if (lotLineId != null) {
                    val idx = inventoryLotLines.indexOf(lot.suggestedLotLine)
                    if (idx >= 0) {
                        val currentHold = inventoryLotLines[idx].holdQty ?: zero
                        val addHold = lot.qty ?: zero
                        inventoryLotLines[idx].holdQty = currentHold.plus(addHold)
                    }
                }
            }
            inventoryLotLineRepository.saveAll(inventoryLotLines)

            // Pre-create stock out lines
            var precreated = 0
            saveSuggestedPickLots.forEach { lot ->
                val polId = lot.pickOrderLine?.id
                val illId = lot.suggestedLotLine?.id
                if (polId != null && illId != null) {
                    val existing = stockOutLIneRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
                    if (existing.isEmpty()) {
                        val pol = pickOrderLineRepository.findById(polId).orElse(null)
                        val ill = inventoryLotLineRepository.findById(illId).orElse(null)
                        if (pol != null && ill != null) {
                            val line = com.ffii.fpsms.modules.stock.entity.StockOutLine().apply {
                                this.stockOut = savedStockOut
                                this.pickOrderLine = pol
                                this.inventoryLotLine = ill
                                this.item = pol.item
                                this.status = com.ffii.fpsms.modules.stock.web.model.StockOutLineStatus.PENDING.status
                                this.qty = 0.0
                            }
                            stockOutLIneRepository.save(line)
                            precreated++
                        }
                    }
                }
            }
            println("Precreated $precreated stock out lines for ticket $ticketNo")
        }

        // Assign user (both pending->released and already released)
        selected.assignTo = user
        pickOrderRepository.saveAndFlush(selected)
        
        // Update do_pick_order_record entries
        val existingRecords = doPickOrderRecordRepository.findByPickOrderId(selected.id!!)
        println("🔍 DEBUG: Found ${existingRecords.size} existing DoPickOrderRecord entries for pick order ${selected.id}")

        if (existingRecords.isNotEmpty()) {
            existingRecords.forEach { record ->
                record.handledBy = user.id
                record.ticketStatus = DoPickOrderStatus.released
                println("🔍 DEBUG: Updating existing DoPickOrderRecord ID: ${record.id} - handledBy: ${user.id}, status: released")
            }
            doPickOrderRecordRepository.saveAll(existingRecords)
            println("✅ Updated ${existingRecords.size} existing DoPickOrderRecord entries")
        }
        
        doPickOrderService.updateHandledByForPickOrder(selected.id!!, user.id!!)
        println("✅ Updated DoPickOrder handledBy to user $userId for pick order ${selected.id}")
        
        return MessageResponse(
            id = null,
            name = "Pick order assigned by ticket",
            code = "SUCCESS",
            type = "pickorder",
            message = "Assigned to user $userId for store $storeId with ticket $ticketNo",
            errorPosition = null,
            entity = mapOf(
                "pickOrderIds" to listOf(selected.id!!), 
                "storeId" to storeId, 
                "ticketNo" to ticketNo,
                "status" to selected.status?.value
            )
        )
        
    } catch (e: Exception) {
        e.printStackTrace()
        return MessageResponse(
            id = null, 
            name = "Failed to auto-assign by ticket", 
            code = "ERROR", 
            type = "pickorder",
            message = "Failed to auto-assign by ticket: ${e.message}", 
            errorPosition = null
        )
    }
    }

    open fun getPickOrderDetailsOptimized(pickOrderIds: List<Long>): GetPickOrderInfoResponse {
        val today = LocalDate.now()
        val zero = BigDecimal.ZERO
        
        if (pickOrderIds.isEmpty()) {
            return GetPickOrderInfoResponse(
                consoCode = null,
                pickOrders = emptyList(),
                items = emptyList()
            )
        }
        
        val pickOrderIdsStr = pickOrderIds.joinToString(",")
        
        val sql = """
            SELECT 
                -- Pick Order Information
                po.id as pickOrderId,
                po.code as pickOrderCode,
                po.consoCode as pickOrderConsoCode,  -- ✅ 添加 consoCode
                po.targetDate as pickOrderTargetDate,
                po.type as pickOrderType,
                po.status as pickOrderStatus,
                po.assignTo as pickOrderAssignTo,
                
                -- Pick Order Line Information
                pol.id as pickOrderLineId,
                pol.qty as pickOrderLineRequiredQty,
                
                -- Item Information
                i.id as itemId,
                i.code as itemCode,
                i.name as itemName,
                uc.code as uomCode,
                uc.udfudesc as uomDesc,
                
                -- ✅ Calculate total picked quantity from stock_out_line table
                COALESCE((
                    SELECT SUM(sol_picked.qty) 
                    FROM fpsmsdb.stock_out_line sol_picked 
                    WHERE sol_picked.pickOrderLineId = pol.id 
                    AND sol_picked.deleted = false
                    AND sol_picked.status IN ('completed', 'COMPLETE', 'partially_completed','rejected')
                ), 0) as totalPickedQty,
                
                -- ✅ Calculate available quantity from inventory
                COALESCE((
                    SELECT inv.onHandQty - inv.onHoldQty - inv.unavailableQty
                    FROM fpsmsdb.inventory inv
                    WHERE inv.itemId = i.id
                    AND inv.deleted = false
                ), 0) as availableQty
                
            FROM fpsmsdb.pick_order po
            JOIN fpsmsdb.pick_order_line pol ON pol.poId = po.id
            JOIN fpsmsdb.items i ON i.id = pol.itemId
            LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = pol.uomId
            WHERE po.deleted = false 
            AND po.id IN ($pickOrderIdsStr)
            AND pol.deleted = false
            AND po.status = 'RELEASED'
            
            ORDER BY 
                po.code ASC,
                i.code ASC
        """.trimIndent()
        
        println("🔍 Executing optimized SQL: $sql")
        
        val result = jdbcDao.queryForList(sql, emptyMap<String, Any>())
        
        println("✅ Optimized result count: ${result.size}")
        
        // ✅ Transform the flat data into the expected nested structure
        val pickOrdersMap = mutableMapOf<Long, GetPickOrderInfo>()
        var consoCode: String? = null  // ✅ 用于存储 consoCode
        
        result.forEach { row ->
            val pickOrderId = (row["pickOrderId"] as Number).toLong()
            val pickOrderLineId = (row["pickOrderLineId"] as Number).toLong()
            
            // ✅ 获取 consoCode（所有行应该有相同的 consoCode）
            if (consoCode == null) {
                consoCode = row["pickOrderConsoCode"] as String?
            }
            
            // Create or get pick order
            if (!pickOrdersMap.containsKey(pickOrderId)) {
                pickOrdersMap[pickOrderId] = GetPickOrderInfo(
                    id = pickOrderId,
                    code = row["pickOrderCode"] as String,
                    consoCode = row["pickOrderConsoCode"] as String?,
                    targetDate = row["pickOrderTargetDate"] as LocalDateTime?, // ✅ Convert to LocalDateTime
                    type = row["pickOrderType"] as String,
                    status = row["pickOrderStatus"] as String,
                    assignTo = (row["pickOrderAssignTo"] as Number?)?.toLong(),
                    groupName = "No Group",
                    pickOrderLines = mutableListOf()
                )
            }
            
            val pickOrder = pickOrdersMap[pickOrderId]!!
            
            // Create pick order line
            val pickOrderLine = GetPickOrderLineInfo(
                id = pickOrderLineId,
                itemId = (row["itemId"] as Number).toLong(),
                itemCode = row["itemCode"] as String,
                itemName = row["itemName"] as String,
                availableQty = (row["availableQty"] as Number).toDouble().toBigDecimal(), // ✅ Convert to BigDecimal
                requiredQty = (row["pickOrderLineRequiredQty"] as Number).toDouble()
                    .toBigDecimal(), // ✅ Convert to BigDecimal
                uomCode = row["uomCode"] as String?,
                uomDesc = row["uomDesc"] as String?,
                suggestedList = emptyList(),
                pickedQty = (row["totalPickedQty"] as Number).toDouble().toBigDecimal() // ✅ Convert to BigDecimal
            )
            
            // ✅ Fix the add method call
            (pickOrder.pickOrderLines as MutableList).add(pickOrderLine)
        }
        
        return GetPickOrderInfoResponse(
            consoCode = consoCode, // ✅ 使用获取到的 consoCode
            pickOrders = pickOrdersMap.values.toList(),
            items = emptyList()
        )
    }

    open fun getPickOrderDetailsOptimizedByUser(userId: Long): GetPickOrderInfoResponse {
        val today = LocalDate.now()
        val zero = BigDecimal.ZERO
        
        // Get all released pick order IDs for the user
        val allPickOrders = pickOrderRepository.findAll()
        val releasedPickOrderIds = allPickOrders
            .filter { it.status == PickOrderStatus.RELEASED }
            .filter { it.assignTo?.id == userId }
            .map { it.id!! }
        
        if (releasedPickOrderIds.isEmpty()) {
            return GetPickOrderInfoResponse(
                consoCode = null,
                pickOrders = emptyList(),
                items = emptyList()
            )
        }
        
        // Use the existing optimized method
        return getPickOrderDetailsOptimized(releasedPickOrderIds)
    }


// ... existing code ...

    @Transactional(rollbackFor = [java.lang.Exception::class])
    open fun autoAssignAndReleasePickOrder(userId: Long): MessageResponse {
        try {
            println("=== DEBUG: autoAssignAndReleasePickOrder ===")
            println("userId: $userId")

            val zero = BigDecimal.ZERO
            val releasedBy = SecurityUtils.getUser().getOrNull()
            val user = userService.find(userId).orElse(null)

            if (user == null) {
                println("❌ User not found: $userId")
                return MessageResponse(
                    id = null,
                    name = "User not found",
                    code = "ERROR",
                    type = "pickorder",
                    message = "User with ID $userId not found",
                    errorPosition = null
                )
            }

            println("✅ User found: ${user.name} (ID: ${user.id})")

            // Check if user already has pending/released pick orders that are not completed
            val existingPickOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
                user,
                listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED)
            )

            println("📋 Found ${existingPickOrders.size} existing pick orders for user")

            // Filter out completed pick orders by checking stock_out status
            val activePickOrders = existingPickOrders.filter { pickOrder ->
                // ✅ Fix: Store consoCode in local variable to avoid smart cast issues
                val consoCode = pickOrder.consoCode
                if (consoCode == null) {
                    println("🔍 Checking pick order ${pickOrder.code} (consoCode: null)")
                    println("  ⚠️ No consoCode - considering as active")
                    true // No consoCode means not completed
                } else {
                    val stockOut = stockOutRepository.findByConsoPickOrderCode(consoCode).orElse(null)
                    println("�� Checking pick order ${pickOrder.code} (consoCode: $consoCode)")

                    if (stockOut == null) {
                        println("  ⚠️ No stock_out record found - considering as active")
                        true // No stock_out record means not completed
                    } else {
                        println("  📦 Stock_out status: ${stockOut.status}")
                        val isActive = stockOut.status != StockOutStatus.COMPLETE.status
                        println("  ${if (isActive) "🟡 Active" else "✅ Completed"}")
                        isActive
                    }
                }
            }

            println("🎯 Active pick orders: ${activePickOrders.size}")

            if (activePickOrders.isNotEmpty()) {
                // User already has active pick orders, return existing ones
                println("ℹ️ User already has active pick orders")
                return MessageResponse(
                    id = null,
                    name = "User already has active pick orders",
                    code = "EXISTS",
                    type = "pickorder",
                    message = "User already has ${activePickOrders.size} active pick order(s)",
                    errorPosition = null,
                    entity = mapOf(
                        "pickOrderIds" to activePickOrders.map { it.id!! },
                        "hasActiveOrders" to true
                    )
                )
            }

            // Find available pick orders (prioritize those with doId)
            val availablePickOrders = pickOrderRepository.findAllByStatusAndDeletedFalse(PickOrderStatus.PENDING)
                .filter { it.deliveryOrder != null } // Only pick orders with doId (delivery order)
                .sortedBy { it.targetDate } // Sort by target date (earliest first)
                .take(1) // Take only one pick order for now

            println("📋 Available pick orders with doId: ${availablePickOrders.size}")

            if (availablePickOrders.isEmpty()) {
                println("❌ No available pick orders with doId found")
                return MessageResponse(
                    id = null,
                    name = "No available pick orders",
                    code = "NO_ORDERS",
                    type = "pickorder",
                    message = "No pending pick orders with delivery orders available for assignment",
                    errorPosition = null,
                    entity = mapOf("hasActiveOrders" to false)
                )
            }

            println("✅ Found available pick order: ${availablePickOrders.first().code}")

            // ✅ Add the complete assignment logic here
            val availablePickOrder = availablePickOrders.first()

            // Generate consoCode early and save immediately
            val newConsoCode = assignConsoCode()
            val currUser = SecurityUtils.getUser().orElseThrow()

            // Create and save StockOut immediately to prevent duplicate consoCodes
            val stockOut = StockOut().apply {
                this.type = "job"
                this.consoPickOrderCode = newConsoCode
                this.status = StockOutStatus.PENDING.status
                this.handler = currUser.id
            }
            val savedStockOut = stockOutRepository.saveAndFlush(stockOut)

            // Assign and release the pick order
            availablePickOrder.apply {
                this.releasedBy = releasedBy
                status = PickOrderStatus.RELEASED
                this.assignTo = user
                this.consoCode = newConsoCode
            }

            val suggestions = suggestedPickLotService.suggestionForPickOrders(
                SuggestedPickLotForPoRequest(pickOrders = listOf(availablePickOrder))
            )

            val saveSuggestedPickLots = suggestedPickLotService.saveAll(suggestions.suggestedList)

            pickOrderRepository.saveAndFlush(availablePickOrder)

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

            saveSuggestedPickLots.forEach { lot ->
                if (lot.suggestedLotLine != null && lot.suggestedLotLine?.id != null && lot.suggestedLotLine!!.id!! > 0) {
                    val lineIndex = inventoryLotLines.indexOf(lot.suggestedLotLine)
                    if (lineIndex >= 0) {
                        inventoryLotLines[lineIndex].holdQty =
                            (inventoryLotLines[lineIndex].holdQty ?: zero).plus(lot.qty ?: zero)
                    }
                }
            }

            inventoryLotLineRepository.saveAll(inventoryLotLines)

            println("✅ Successfully assigned pick order ${availablePickOrder.code}")

            return MessageResponse(
                id = null,
                name = "Pick order automatically assigned and released",
                code = "SUCCESS",
                type = "pickorder",
                message = "Pick order ${availablePickOrder.code} assigned and released to user ${user.name}",
                errorPosition = null,
                entity = mapOf(
                    "consoCode" to newConsoCode,
                    "pickOrderIds" to listOf(availablePickOrder.id!!),
                    "hasActiveOrders" to false
                )
            )

        } catch (e: Exception) {
            println("❌ Error in autoAssignAndReleasePickOrder: ${e.message}")
            e.printStackTrace()
            return MessageResponse(
                id = null,
                name = "Failed to auto-assign pick order",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to auto-assign pick order: ${e.message}",
                errorPosition = null
            )
        }
    }

    open fun checkPickOrderCompletion(userId: Long): MessageResponse {
        try {
            val user = userService.find(userId).orElse(null)

            if (user == null) {
                return MessageResponse(
                    id = null,
                    name = "User not found",
                    code = "ERROR",
                    type = "pickorder",
                    message = "User with ID $userId not found",
                    errorPosition = null
                )
            }

            // Get all pick orders assigned to this user
            val userPickOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
                user,
                listOf(PickOrderStatus.ASSIGNED, PickOrderStatus.RELEASED)
            )

            val completionStatus = userPickOrders.map { pickOrder ->
                // ✅ Fix: Store consoCode in local variable to avoid smart cast issues
                val consoCode = pickOrder.consoCode
                val stockOut = if (consoCode != null) {
                    stockOutRepository.findByConsoPickOrderCode(consoCode).orElse(null)
                } else {
                    null
                }

                val stockOutLines = if (stockOut != null) {
                    stockOutLIneRepository.findAllByStockOutId(stockOut.id!!)
                } else {
                    emptyList()
                }

                val unfinishedLines = stockOutLines.filter {
                    it.status != StockOutLineStatus.COMPLETE.status && it.status != StockOutLineStatus.REJECTED.status
                }

                mapOf(
                    "pickOrderId" to pickOrder.id,
                    "pickOrderCode" to pickOrder.code,
                    "consoCode" to consoCode,
                    "isCompleted" to (unfinishedLines.isEmpty() && stockOut != null),
                    "stockOutStatus" to (stockOut?.status ?: "not_created"),
                    "totalLines" to stockOutLines.size,
                    "unfinishedLines" to unfinishedLines.size
                )
            }

            val completedOrders = completionStatus.filter { it["isCompleted"] == true }
            val hasCompletedOrders = completedOrders.isNotEmpty()

            return MessageResponse(
                id = null,
                name = "Pick order completion status checked",
                code = "SUCCESS",
                type = "pickorder",
                message = "Found ${completedOrders.size} completed orders out of ${userPickOrders.size} total",
                errorPosition = null,
                entity = mapOf(
                    "hasCompletedOrders" to hasCompletedOrders,
                    "completedOrders" to completedOrders,
                    "allOrders" to completionStatus
                )
            )

        } catch (e: Exception) {
            return MessageResponse(
                id = null,
                name = "Failed to check pick order completion",
                code = "ERROR",
                type = "pickorder",
                message = "Failed to check pick order completion: ${e.message}",
                errorPosition = null
            )
        }
    }
    @Transactional(rollbackFor = [java.lang.Exception::class])
open fun autoAssignAndReleasePickOrderByStore(userId: Long, storeId: String): MessageResponse {
    try {
        println("=== DEBUG: autoAssignAndReleasePickOrderByStore ===")
        println("userId: $userId, storeId: $storeId")

        val zero = BigDecimal.ZERO
        val releasedBy = SecurityUtils.getUser().getOrNull()
        val user = userService.find(userId).orElse(null)
        if (user == null) {
            return MessageResponse(id = null, name = "User not found", code = "ERROR", type = "pickorder",
                message = "User with ID $userId not found", errorPosition = null)
        }

        val sql = """
            SELECT DISTINCT dpo.pick_order_id AS pickOrderId
            FROM do_pick_order dpo
            WHERE dpo.deleted = false
              AND dpo.ticket_status = 'pending'
              AND dpo.store_id = :storeId
              AND dpo.pick_order_id IS NOT NULL
        """.trimIndent()
        val idRows = jdbcDao.queryForList(sql, mapOf("storeId" to storeId))
        val pickOrderIdsByStore = idRows.mapNotNull { row ->
            when (val id = row["pickOrderId"]) {
                is Number -> id.toLong()
                is String -> id.toLongOrNull()
                else -> null
            }
        }.toSet()
        println("Candidate pickOrderIds by store $storeId: $pickOrderIdsByStore")

        if (pickOrderIdsByStore.isEmpty()) {
            return MessageResponse(id = null, name = "No pick orders", code = "NO_ORDERS", type = "pickorder",
                message = "No pending pick orders found for store $storeId", errorPosition = null)
        }
        
        // Add this check after line 2142 (before finding availablePickOrders)
        // ✅ Check if user already has pick orders in progress
        val userExistingOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
            user,
            listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED) // Only active orders, not completed
        ).filter { it.deliveryOrder != null }

        if (userExistingOrders.isNotEmpty()) {
            println("🔍 DEBUG: User $userId already has ${userExistingOrders.size} pick orders in progress:")
            userExistingOrders.forEach { po ->
                println("�� DEBUG: Existing order ${po.id}: code=${po.code}, status=${po.status}")
            }
            return MessageResponse(
                id = null, 
                name = "User already has pick orders", 
                code = "USER_BUSY", 
                type = "pickorder",
                message = "User $userId already has ${userExistingOrders.size} pick orders in progress. Cannot assign new orders.", 
                errorPosition = null,
                entity = mapOf(
                    "existingOrders" to userExistingOrders.map { mapOf(
                        "id" to it.id,
                        "code" to it.code,
                        "status" to it.status?.value
                    )}
                )
            )
        }

        // Continue with existing logic...
        val availablePickOrders = pickOrderRepository
            .findAll()
            .filter { it.id != null && pickOrderIdsByStore.contains(it.id!!) }
            .filter { it.deliveryOrder != null }
            .filter { it.assignTo == null }
            .filter { it.status == PickOrderStatus.PENDING || it.status == PickOrderStatus.RELEASED }
            .sortedBy { it.targetDate }
            .take(1)

        if (availablePickOrders.isEmpty()) {
            return MessageResponse(id = null, name = "No available pick orders", code = "NO_ORDERS", type = "pickorder",
                message = "No unassigned pick orders available for store $storeId", errorPosition = null)
        }

        val selected = availablePickOrders.first()
        val currUser = SecurityUtils.getUser().orElseThrow()

        // If still PENDING, perform full release flow; if already RELEASED, skip creation duplication
        if (selected.status == PickOrderStatus.PENDING) {
            val newConsoCode = assignConsoCode()

            val stockOut = StockOut().apply {
                this.type = "job"
                this.consoPickOrderCode = newConsoCode
                this.status = StockOutStatus.PENDING.status
                this.handler = currUser.id
            }
            val savedStockOut = stockOutRepository.saveAndFlush(stockOut)

            selected.apply {
                this.releasedBy = releasedBy
                status = PickOrderStatus.RELEASED
                this.consoCode = newConsoCode
            }

            val suggestions = suggestedPickLotService.suggestionForPickOrders(
                SuggestedPickLotForPoRequest(pickOrders = listOf(selected))
            )
            val saveSuggestedPickLots = suggestedPickLotService.saveAll(suggestions.suggestedList)
            pickOrderRepository.saveAndFlush(selected)

            val inventoryLotLines = inventoryLotLineRepository.findAllByIdIn(
                saveSuggestedPickLots.mapNotNull { it.suggestedLotLine?.id }
            )
            saveSuggestedPickLots.forEach { lot ->
                val lotLineId = lot.suggestedLotLine?.id
                if (lotLineId != null) {
                    val idx = inventoryLotLines.indexOf(lot.suggestedLotLine)
                    if (idx >= 0) {
                        val currentHold = inventoryLotLines[idx].holdQty ?: zero
                        val addHold = lot.qty ?: zero
                        inventoryLotLines[idx].holdQty = currentHold.plus(addHold)
                    }
                }
            }
            inventoryLotLineRepository.saveAll(inventoryLotLines)

            var precreated = 0
            saveSuggestedPickLots.forEach { lot ->
                val polId = lot.pickOrderLine?.id
                val illId = lot.suggestedLotLine?.id
                if (polId != null && illId != null) {
                    val existing = stockOutLIneRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
                    if (existing.isEmpty()) {
                        val pol = pickOrderLineRepository.findById(polId).orElse(null)
                        val ill = inventoryLotLineRepository.findById(illId).orElse(null)
                        if (pol != null && ill != null) {
                            val line = com.ffii.fpsms.modules.stock.entity.StockOutLine().apply {
                                this.stockOut = savedStockOut
                                this.pickOrderLine = pol
                                this.inventoryLotLine = ill
                                this.item = pol.item
                                this.status = com.ffii.fpsms.modules.stock.web.model.StockOutLineStatus.PENDING.status
                                this.qty = 0.0
                            }
                            stockOutLIneRepository.save(line)
                            precreated++
                        }
                    }
                }
            }
            println("Precreated $precreated stock out lines for store $storeId")
        }

        // Assign user (both pending->released and already released)
        selected.assignTo = user
        pickOrderRepository.saveAndFlush(selected)
        val deliveryOrder = selected.deliveryOrder
        if (deliveryOrder != null) {
            val targetDate = deliveryOrder.estimatedArrivalDate?.toLocalDate() ?: LocalDate.now()
            val datePrefix = targetDate.format(DateTimeFormatter.ofPattern("ddMMyy"))
            
            println("�� DEBUG: Target date: $targetDate, Date prefix: $datePrefix")

            val storeId = if (deliveryOrder.supplier?.code == "P06B") "4/F" else "2/F"
            println("🔍 DEBUG: Determined store ID: $storeId")

            // Get next ticket number for this date and store
            val nextTicketNumber = doPickOrderService.getNextTicketNumber(datePrefix, storeId)
            println("🔍 DEBUG: Next ticket number: $nextTicketNumber")
            
            // ✅ Find truck by shop ID with earliest departure time
            val truck = deliveryOrder.shop?.id?.let { shopId ->
                println("🔍 DEBUG: Looking for truck with shop ID: $shopId")
                val trucks = truckRepository.findByShopIdAndDeletedFalse(shopId)
                println("🔍 DEBUG: Found ${trucks.size} trucks for shop $shopId")
                val selectedTruck = trucks.minByOrNull { it.departureTime ?: LocalTime.MAX }
                println("🔍 DEBUG: Selected truck: ID=${selectedTruck?.id}, DepartureTime=${selectedTruck?.departureTime}")
                selectedTruck
            }
            
            println("🔍 DEBUG: Processing ${deliveryOrder.deliveryOrderLines.size} delivery order lines")
            
           // Replace lines 2253-2275 with this:
// ✅ UPDATE existing do_pick_order_record entries instead of creating new ones
val existingRecords = doPickOrderRecordRepository.findByPickOrderId(selected.id!!)
println("🔍 DEBUG: Found ${existingRecords.size} existing DoPickOrderRecord entries for pick order ${selected.id}")

if (existingRecords.isNotEmpty()) {
    // ✅ Update existing records
    existingRecords.forEach { record ->
        record.handledBy = user.id
        record.ticketStatus = DoPickOrderStatus.released
        println("🔍 DEBUG: Updating existing DoPickOrderRecord ID: ${record.id} - handledBy: ${user.id}, status: released")
    }
    doPickOrderRecordRepository.saveAll(existingRecords)
    println("✅ Updated ${existingRecords.size} existing DoPickOrderRecord entries")
} else {
    // ✅ Only create new records if none exist (fallback)
    println("⚠️ No existing DoPickOrderRecord entries found, creating new ones")
    deliveryOrder.deliveryOrderLines.forEach { line ->
        val storeId = if (deliveryOrder.supplier?.code == "P06B") "4/F" else "2/F"
        println(" DEBUG: Processing line - Item ID: ${line.item?.id}, Store ID: $storeId")
        
        val doPickOrderRecord = DoPickOrderRecord(
            storeId = storeId,
            ticketNo = nextTicketNumber,
            ticketStatus = DoPickOrderStatus.released, // ✅ Set to released
            truckId = truck?.id,
            pickOrderId = selected.id,
            truckDepartureTime = truck?.departureTime,
            itemId = line.item?.id,
            shopId = deliveryOrder.shop?.id,
            shopPoSupplierId = deliveryOrder.shop?.id,
            handledBy = user.id
        )
        
        println(" DEBUG: Creating new DoPickOrderRecord - Store: $storeId, Ticket: $nextTicketNumber, Truck: ${truck?.id}")
        
        val savedDoPickOrderRecord = doPickOrderRecordRepository.save(doPickOrderRecord)
        println("🔍 DEBUG: Saved new DoPickOrderRecord - ID: ${savedDoPickOrderRecord.id}")
    }
}
        }
        doPickOrderService.updateHandledByForPickOrder(selected.id!!, user.id!!)
        println("✅ Updated DoPickOrder handledBy to user $userId for pick order ${selected.id}")
        return MessageResponse(
            id = null,
            name = "Pick order assigned",
            code = "SUCCESS",
            type = "pickorder",
            message = "Assigned to user $userId for store $storeId",
            errorPosition = null,
            entity = mapOf("pickOrderIds" to listOf(selected.id!!), "storeId" to storeId, "status" to selected.status?.value)
        )
    } catch (e: Exception) {
        e.printStackTrace()
        return MessageResponse(
            id = null, name = "Failed to auto-assign by store", code = "ERROR", type = "pickorder",
            message = "Failed to auto-assign by store: ${e.message}", errorPosition = null
        )
    }
}
    open fun getAllPickOrderLotsWithDetailsWithAutoAssign(userId: Long): List<Map<String, Any>> {
        println("=== Debug: getAllPickOrderLotsWithDetailsWithAutoAssign ===")
        println("today: ${LocalDate.now()}")
        println("userId filter: $userId")
        
        // ✅ First attempt auto-assignment for the user
        println("🎯 Attempting auto-assignment for user $userId")
        val assignedPickOrderResponse = autoAssignAndReleasePickOrder(userId)
        
        // Get all pick order IDs assigned to the user (both RELEASED and PENDING with doId)
        val user = userService.find(userId).orElse(null)
        if (user == null) {
            println("❌ User not found: $userId")
            return emptyList()
        }
        
        // Get all pick orders assigned to user with PENDING or RELEASED status that have doId
        val allAssignedPickOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
            user,
            listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED,PickOrderStatus.COMPLETED)
            
        ).filter { it.deliveryOrder != null } // Only pick orders with doId
        println("🔍 DEBUG: All assigned pick orders: ${allAssignedPickOrders.size}")
        val pickOrderIds = allAssignedPickOrders.map { it.id!! }
        println("🔍 DEBUG: All assigned pick orders: ${pickOrderIds.size}")
        println(" Pick order IDs to fetch: $pickOrderIds")
        
        if (pickOrderIds.isEmpty()) {
            return emptyList()
        }
        
        // ✅ Use SQL query approach similar to getPickOrderLineLotDetails for detailed lot information
        val pickOrderIdsStr = pickOrderIds.joinToString(",")
        
        val sql = """
            SELECT 
                -- Pick Order Information
                po.id as pickOrderId,
                po.code as pickOrderCode,
                po.consoCode as pickOrderConsoCode,
                DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
                po.type as pickOrderType,
                po.status as pickOrderStatus,
                po.assignTo as pickOrderAssignTo,
                
                -- Pick Order Line Information
                pol.id as pickOrderLineId,
                pol.qty as pickOrderLineRequiredQty,
                pol.status as pickOrderLineStatus,
                
                -- Item Information
                i.id as itemId,
                i.code as itemCode,
                i.name as itemName,
                uc.code as uomCode,
                uc.udfudesc as uomDesc,
                
                -- Lot Information (similar to lot-details endpoint)
                ill.id as lotId,
                il.lotNo,
                DATE_FORMAT(il.expiryDate, '%Y-%m-%d') as expiryDate,
                w.name as location,
                COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
                
                -- ✅ FIXED: Set quantities to NULL for rejected lots
                CASE 
                    WHEN sol.status = 'rejected' THEN NULL
                    ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
                END as availableQty,
                
                -- Required quantity for this lot
                COALESCE(spl.qty, 0) as requiredQty,
                
                -- Actual picked quantity
                COALESCE(sol.qty, 0) as actualPickQty,
                
                -- Suggested pick lot information
                spl.id as suggestedPickLotId,
                ill.status as lotStatus,
                
                -- Stock out line information
                sol.id as stockOutLineId,
                sol.status as stockOutLineStatus,
                COALESCE(sol.qty, 0) as stockOutLineQty,
                
                -- Additional detailed fields from lot-details
                COALESCE(ill.inQty, 0) as inQty, 
                COALESCE(ill.outQty, 0) as outQty,
                COALESCE(ill.holdQty, 0) as holdQty,
                COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
                ill.inventoryLotId as debugInventoryLotId,
                
                -- Calculate total picked quantity by ALL pick orders for this lot
                COALESCE((
                    SELECT SUM(sol_all.qty) 
                    FROM fpsmsdb.stock_out_line sol_all 
                    WHERE sol_all.inventoryLotLineId = ill.id 
                    AND sol_all.deleted = false
                    AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
                ), 0) as totalPickedByAllPickOrders,
                
                -- Calculate remaining available quantity correctly
                (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
                
                -- Add detailed debug fields for lotAvailability calculation
                ill.status as debug_ill_status,
                (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) as debug_is_expired,
                sol.status as debug_sol_status,
                (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as debug_remaining_stock,
                (COALESCE(spl.qty, 0) - COALESCE(sol.qty, 0)) as debug_required_after_picked,
                
                -- Lot availability status (same logic as lot-details)
                CASE 
                    -- Check if lot is expired
                    WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                    -- Check if lot has rejected stock out line for this pick order
                    WHEN sol.status = 'rejected' THEN 'rejected'
                    -- Check if lot is unavailable due to insufficient stock
                    WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                    -- Check if lot status is unavailable
                    WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                    -- Default to available
                    ELSE 'available'
                END as lotAvailability,
                
                -- Processing status
                CASE 
                    WHEN sol.status = 'completed' THEN 'completed'
                    WHEN sol.status = 'rejected' THEN 'rejected'
                    WHEN sol.status = 'created' THEN 'pending'
                    ELSE 'pending'
                END as processingStatus
                
            FROM fpsmsdb.pick_order po
            JOIN fpsmsdb.pick_order_line pol ON pol.poId = po.id
            JOIN fpsmsdb.items i ON i.id = pol.itemId
            LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = pol.uomId
            LEFT JOIN fpsmsdb.suggested_pick_lot spl ON pol.id = spl.pickOrderLineId
            LEFT JOIN fpsmsdb.inventory_lot_line ill ON spl.suggestedLotLineId = ill.id
            LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
            LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
            LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
            WHERE po.deleted = false 
            AND po.id IN ($pickOrderIdsStr)
            AND pol.deleted = false
            AND po.status IN ('PENDING', 'RELEASED')
            AND po.assignTo = :userId
            AND ill.deleted = false
            AND il.deleted = false
            AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
            ORDER BY 
                CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END, -- Show rejected lots first
                po.code ASC,
                i.code ASC,
                il.expiryDate ASC, 
                il.lotNo ASC
        """.trimIndent()
        
        println("🔍 Executing SQL for all pick order lots with details: $sql")
        println(" With parameters: userId = $userId, pickOrderIds = $pickOrderIdsStr")
        
        val results = jdbcDao.queryForList(sql, mapOf("userId" to userId))
        println("✅ Total result count: ${results.size}")
        
        // Filter out lots with null availableQty (rejected lots)
        val filteredResults = results.filter { row ->
            val availableQty = row["availableQty"]
            availableQty != null
        }
        
        println("✅ Filtered result count: ${filteredResults.size}")
        
        // ✅ Add router information for each lot
        val enrichedResults = filteredResults.map { row ->
            val inventoryLotId = row["debugInventoryLotId"] as? Number
            
            // Get router information for this inventory lot
            val routerInfo = if (inventoryLotId != null) {
                try {
                    val router = routerRepository.findFirstByInventoryLotIdAndDeletedFalse(inventoryLotId.toInt())
                    if (router != null) {
                        mapOf(
                            "routerIndex" to (router.index ?: 0),
                            "routerRoute" to (router.route ?: ""),
                            "routerArea" to (router.area ?: ""),
                            "routerItemCode" to (router.itemCode ?: 0),
                            "routerItemName" to (router.itemName ?: ""),
                            "routerUomId" to (router.uomId ?: 0),
                            "routerNoofCarton" to (router.noofCarton ?: 0)
                        )
                    } else {
                        mapOf(
                            "routerIndex" to 0,
                            "routerRoute" to "",
                            "routerArea" to "",
                            "routerItemCode" to 0,
                            "routerItemName" to "",
                            "routerUomId" to 0,
                            "routerNoofCarton" to 0
                        )
                    }
                } catch (e: Exception) {
                    println("⚠️ Error getting router info for inventoryLotId $inventoryLotId: ${e.message}")
                    mapOf(
                        "routerIndex" to 0,
                        "routerRoute" to "",
                        "routerArea" to "",
                        "routerItemCode" to 0,
                        "routerItemName" to "",
                        "routerUomId" to 0,
                        "routerNoofCarton" to 0
                    )
                }
            } else {
                mapOf(
                    "routerIndex" to 0,
                    "routerRoute" to "",
                    "routerArea" to "",
                    "routerItemCode" to 0,
                    "routerItemName" to "",
                    "routerUomId" to 0,
                    "routerNoofCarton" to 0
                )
            }
            
            // Combine original row with router information
            row.toMutableMap().apply {
                putAll(routerInfo)
            }
        }
        
        return enrichedResults
    }

    open fun getFgPickOrders(): List<Map<String, Any?>> {
        try {
            println("🔍 Starting getFgPickOrders method - using repository")
            
            // Use repository to find pick orders with type 'do'
            val pickOrders = pickOrderRepository.findAll().filter { 
                it.type?.value == "do" && 
                it.status?.value in listOf("assigned", "released", "picking")
            }
            
            println("🔍 Found ${pickOrders.size} FG pick orders using repository")
            
            val results = pickOrders.map { po ->
                val deliveryOrder = po.deliveryOrder
                val shop = deliveryOrder?.shop
                val supplier = deliveryOrder?.supplier
                
                // ✅ Get truck information for this shop - handle Optional properly
                val truckInfo = if (shop?.id != null) {
                    try {
                        jdbcDao.queryForMap("""
                            SELECT truckNo, DepartureTime 
                            FROM truck 
                            WHERE shopId = :shopId AND deleted = false 
                            LIMIT 1
                        """, mapOf("shopId" to shop.id)).orElse(null)
                    } catch (e: Exception) {
                        println("⚠️ No truck found for shop ${shop.id}: ${e.message}")
                        null
                    }
                } else null
                
                mapOf(
                    "pickOrderId" to (po.id ?: 0L),
                    "pickOrderCode" to (po.code ?: ""),
                    "pickOrderConsoCode" to (po.consoCode ?: ""),
                    "pickOrderTargetDate" to (po.targetDate?.toString() ?: ""),
                    "pickOrderStatus" to (po.status?.value ?: ""),
                    "deliveryOrderId" to (deliveryOrder?.id ?: 0L),
                    "deliveryNo" to (deliveryOrder?.code ?: ""),
                    "deliveryDate" to (deliveryOrder?.orderDate?.toString() ?: ""),
                    "shopId" to (shop?.id ?: 0L),
                    "shopCode" to (shop?.code ?: ""),
                    "shopName" to (shop?.name ?: ""),
                    "shopAddress" to buildShopAddress(shop),
                    "shopPoNo" to (supplier?.code ?: ""),
                    "numberOfCartons" to (po.pickOrderLines.size),
                    "truckNo" to (truckInfo?.get("truckNo") ?: ""), // ✅ Add truck number
                    "DepartureTime" to (truckInfo?.get("DepartureTime") ?: ""), // ✅ Add departure time
                    "qrCodeData" to (po.id ?: 0L)
                )
            }
            
            println("✅ FG Pick Orders result count: ${results.size}")
            return results
            
        } catch (e: Exception) {
            println("❌ Error in getFgPickOrders: ${e.message}")
            e.printStackTrace()
            return emptyList()
        }
    }

    open fun getFgPickOrdersByPickOrderId(pickOrderId: Long): List<Map<String, Any?>> {
        try {
            println("🔍 Starting getFgPickOrdersByPickOrderId method with pickOrderId: $pickOrderId")
            
            val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
            
            if (pickOrder == null) {
                println("❌ Pick order not found with ID: $pickOrderId")
                return emptyList()
            }
            if (doPickOrderRepository.findByPickOrderId(pickOrderId).firstOrNull()?.hide == true) {
                println("🔍 Pick order $pickOrderId is hidden, returning empty list")
                return emptyList()
            }
            println("🔍 Found pick order: ${pickOrder.code}, type: ${pickOrder.type?.value}, status: ${pickOrder.status?.value}")
            
            if (pickOrder.type?.value != "do") {
                println("❌ Pick order is not of type 'do': ${pickOrder.type?.value}")
                return emptyList()
            }
            val allowedstatuses= listOf("assigned", "released", "picking"
            //, "completed"
            )
            if (pickOrder.status?.value !in allowedstatuses) {
                println("❌ Pick order status is not in allowed states: ${pickOrder.status?.value}")
                return emptyList()
            }

            val deliveryOrder = pickOrder.deliveryOrder
            val shop = deliveryOrder?.shop
            val supplier = deliveryOrder?.supplier
            
            println(" Delivery order: ${deliveryOrder?.code}, Shop: ${shop?.name}, Supplier: ${supplier?.code}")
            println("🔍 Shop ID: ${shop?.id}")
            
            // ✅ Get truck information using repository with detailed debugging
            val truck = if (shop?.id != null) {
                try {
                    println("🔍 Querying truck repository for shopId: ${shop.id}")
                    
                    // Get all trucks for this shop
                    val trucksForShop = truckRepository.findByShopIdAndDeletedFalse(shop.id)
                    println("🔍 Trucks for shop ${shop.id}: ${trucksForShop.size}")
                    trucksForShop.forEach { t ->
                        println("  - Truck ID: ${t.id}, TruckNo: ${t.truckNo}, ShopId: ${t.shop?.id}, Deleted: ${t.deleted}")
                    }
                    
                    // Take the first truck (or null if none found)
                    val selectedTruck = trucksForShop.firstOrNull()
                    
                    if (selectedTruck != null) {
                        println("✅ Selected truck: ID=${selectedTruck.id}, TruckNo=${selectedTruck.truckNo}, DepartureTime=${selectedTruck.departureTime}")
                    } else {
                        println("❌ No truck found for shopId ${shop.id}")
                    }
                    
                    selectedTruck
                } catch (e: Exception) {
                    println("⚠️ Error querying truck repository for shop ${shop.id}: ${e.message}")
                    e.printStackTrace()
                    null
                }
            } else {
                println("⚠️ Shop ID is null")
                null
            }
            val ticketNo = try {
                val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrderId)
                val ticketNo = doPickOrders.firstOrNull()?.ticketNo ?: ""
                println("🔍 Found ticket number: $ticketNo for pick order $pickOrderId")
                ticketNo
            } catch (e: Exception) {
                println("⚠️ Error getting ticket number for pick order $pickOrderId: ${e.message}")
                ""
            }
            val dpoStoreId = try {
                val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrderId)
                doPickOrders.firstOrNull()?.storeId ?: ""
            } catch (e: Exception) {
                println("⚠️ Error getting storeId for pick order $pickOrderId: ${e.message}")
                ""
            }
            val result = mapOf(
                "pickOrderId" to (pickOrder.id ?: 0L),
                "pickOrderCode" to (pickOrder.code ?: ""),
                "pickOrderConsoCode" to (pickOrder.consoCode ?: ""),
                "pickOrderTargetDate" to (pickOrder.targetDate?.toString() ?: ""),
                "pickOrderStatus" to (pickOrder.status?.value ?: ""),
                "deliveryOrderId" to (deliveryOrder?.id ?: 0L),
                "deliveryNo" to (deliveryOrder?.code ?: ""),
                "deliveryDate" to (deliveryOrder?.orderDate?.toString() ?: ""),
                "shopId" to (shop?.id ?: 0L),
                "shopCode" to (shop?.code ?: ""),
                "shopName" to (shop?.name ?: ""),
                "shopAddress" to buildShopAddress(shop),
                "shopPoNo" to (supplier?.code ?: ""),
                "numberOfCartons" to (pickOrder.pickOrderLines.size),
                "truckNo" to (truck?.truckNo ?: ""), // ✅ Use entity property
                "DepartureTime" to (truck?.departureTime?.toString() ?: ""), 
                "ticketNo" to ticketNo,
                "storeId" to dpoStoreId,
                "qrCodeData" to (pickOrder.id ?: 0L)
            )
            
            println("✅ FG Pick Orders by ID result count: 1")
            return listOf(result)
            
        } catch (e: Exception) {
            println("❌ Error in getFgPickOrdersByPickOrderId: ${e.message}")
            e.printStackTrace()
            return emptyList()
        }
    }
    open fun getnewFgPickOrdersByPickOrderId(pickOrderId: Long): List<Map<String, Any?>> {
        try {
            println("🔍 Starting getnewFgPickOrdersByPickOrderId method with pickOrderId: $pickOrderId")
            
            val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
            
            if (pickOrder == null) {
                println("❌ Pick order not found with ID: $pickOrderId")
                return emptyList()
            }
            if (doPickOrderRepository.findByPickOrderId(pickOrderId).firstOrNull()?.hide == true) {
                println("🔍 Pick order $pickOrderId is hidden, returning empty list")
                return emptyList()
            }
            println("🔍 Found pick order: ${pickOrder.code}, type: ${pickOrder.type?.value}, status: ${pickOrder.status?.value}")
            
            if (pickOrder.type?.value != "do") {
                println("❌ Pick order is not of type 'do': ${pickOrder.type?.value}")
                return emptyList()
            }
            val allowedstatuses= listOf("assigned", "released", "picking","completed"
            //, "completed"
            )
            if (pickOrder.status?.value !in allowedstatuses) {
                println("❌ Pick order status is not in allowed states: ${pickOrder.status?.value}")
                return emptyList()
            }

            val deliveryOrder = pickOrder.deliveryOrder
            val shop = deliveryOrder?.shop
            val supplier = deliveryOrder?.supplier
            
            println(" Delivery order: ${deliveryOrder?.code}, Shop: ${shop?.name}, Supplier: ${supplier?.code}")
            println("🔍 Shop ID: ${shop?.id}")
            
            // ✅ Get truck information using repository with detailed debugging
            val truck = if (shop?.id != null) {
                try {
                    println("🔍 Querying truck repository for shopId: ${shop.id}")
                    
                    // Get all trucks for this shop
                    val trucksForShop = truckRepository.findByShopIdAndDeletedFalse(shop.id)
                    println("🔍 Trucks for shop ${shop.id}: ${trucksForShop.size}")
                    trucksForShop.forEach { t ->
                        println("  - Truck ID: ${t.id}, TruckNo: ${t.truckNo}, ShopId: ${t.shop?.id}, Deleted: ${t.deleted}")
                    }
                    
                    // Take the first truck (or null if none found)
                    val selectedTruck = trucksForShop.firstOrNull()
                    
                    if (selectedTruck != null) {
                        println("✅ Selected truck: ID=${selectedTruck.id}, TruckNo=${selectedTruck.truckNo}, DepartureTime=${selectedTruck.departureTime}")
                    } else {
                        println("❌ No truck found for shopId ${shop.id}")
                    }
                    
                    selectedTruck
                } catch (e: Exception) {
                    println("⚠️ Error querying truck repository for shop ${shop.id}: ${e.message}")
                    e.printStackTrace()
                    null
                }
            } else {
                println("⚠️ Shop ID is null")
                null
            }
            val ticketNo = try {
                val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrderId)
                val ticketNo = doPickOrders.firstOrNull()?.ticketNo ?: ""
                println("🔍 Found ticket number: $ticketNo for pick order $pickOrderId")
                ticketNo
            } catch (e: Exception) {
                println("⚠️ Error getting ticket number for pick order $pickOrderId: ${e.message}")
                ""
            }
            val dpoStoreId = try {
                val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrderId)
                doPickOrders.firstOrNull()?.storeId ?: ""
            } catch (e: Exception) {
                println("⚠️ Error getting storeId for pick order $pickOrderId: ${e.message}")
                ""
            }
            val result = mapOf(
                "pickOrderId" to (pickOrder.id ?: 0L),
                "pickOrderCode" to (pickOrder.code ?: ""),
                "pickOrderConsoCode" to (pickOrder.consoCode ?: ""),
                "pickOrderTargetDate" to (pickOrder.targetDate?.toString() ?: ""),
                "pickOrderStatus" to (pickOrder.status?.value ?: ""),
                "deliveryOrderId" to (deliveryOrder?.id ?: 0L),
                "deliveryNo" to (deliveryOrder?.code ?: ""),
                "deliveryDate" to (deliveryOrder?.orderDate?.toString() ?: ""),
                "shopId" to (shop?.id ?: 0L),
                "shopCode" to (shop?.code ?: ""),
                "shopName" to (shop?.name ?: ""),
                "shopAddress" to buildShopAddress(shop),
                "shopPoNo" to (supplier?.code ?: ""),
                "numberOfCartons" to (pickOrder.pickOrderLines.size),
                "truckNo" to (truck?.truckNo ?: ""), // ✅ Use entity property
                "DepartureTime" to (truck?.departureTime?.toString() ?: ""), 
                "ticketNo" to ticketNo,
                "storeId" to dpoStoreId,
                "qrCodeData" to (pickOrder.id ?: 0L)
            )
            
            println("✅ FG Pick Orders by ID result count: 1")
            return listOf(result)
            
        } catch (e: Exception) {
            println("❌ Error in getFgPickOrdersByPickOrderId: ${e.message}")
            e.printStackTrace()
            return emptyList()
        }
    }
    
    private fun buildShopAddress(shop: com.ffii.fpsms.modules.master.entity.Shop?): String {
        if (shop == null) return ""
        
        val addressParts = mutableListOf<String>()
        
        shop.addr1?.let { if (it.isNotBlank()) addressParts.add(it) }
        shop.addr2?.let { if (it.isNotBlank()) addressParts.add(it) }
        shop.addr3?.let { if (it.isNotBlank()) addressParts.add(it) }
        shop.addr4?.let { if (it.isNotBlank()) addressParts.add(it) }
        shop.district?.let { if (it.isNotBlank()) addressParts.add(it) }
        
        return addressParts.joinToString(", ")
    }

    // Add this new method that doesn't auto-assign
open fun getAllPickOrderLotsWithDetailsWithoutAutoAssign(userId: Long): List<Map<String, Any>> {
    println("=== Debug: getAllPickOrderLotsWithDetailsWithoutAutoAssign ===")
    println("today: ${LocalDate.now()}")
    println("userId filter: $userId")
    
    // Get all pick order IDs assigned to the user (both RELEASED and PENDING with doId)
    val user = userService.find(userId).orElse(null)
    if (user == null) {
        println("❌ User not found: $userId")
        return emptyList()
    }
    
    // Get all pick orders assigned to user with PENDING or RELEASED status that have doId
    val allAssignedPickOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
        user,
        listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED, PickOrderStatus.COMPLETED)
    ).filter { it.deliveryOrder != null } // Only pick orders with doId
    println("🔍 DEBUG: Found ${allAssignedPickOrders.size} pick orders assigned to user $userId")
    allAssignedPickOrders.forEach { po ->
        println("🔍 DEBUG: Pick order ${po.id}: code=${po.code}, status=${po.status}, assignTo=${po.assignTo?.id}, doId=${po.deliveryOrder?.id}")
    }
       // ✅ NEW LOGIC: Filter based on assignment and status
       val filteredPickOrders = if (allAssignedPickOrders.isNotEmpty()) {
        // Check if there are any RELEASED orders assigned to this user (active work)
        val assignedReleasedOrders = allAssignedPickOrders.filter { 
            it.status == PickOrderStatus.RELEASED && it.assignTo?.id == userId
        }
        
        if (assignedReleasedOrders.isNotEmpty()) {
            // ✅ If there are assigned RELEASED orders, show only those
            println("🔍 DEBUG: Found ${assignedReleasedOrders.size} assigned RELEASED orders, showing only those")
            assignedReleasedOrders
        } else {
            // ✅ If no assigned RELEASED orders, show only the latest COMPLETED order
            val completedOrders = allAssignedPickOrders.filter { it.status == PickOrderStatus.COMPLETED }
            if (completedOrders.isNotEmpty()) {
                val latestCompleted = completedOrders.maxByOrNull { it.completeDate ?: it.modified ?: LocalDateTime.MIN }
                println("�� DEBUG: No assigned RELEASED orders, showing latest completed order: ${latestCompleted?.code}")
                listOfNotNull(latestCompleted)
            } else {
                println("🔍 DEBUG: No orders found")
                emptyList()
            }
        }
    } else {
        emptyList()
    }
    
    println("�� DEBUG: After assignment filtering, ${filteredPickOrders.size} pick orders remain")
    filteredPickOrders.forEach { po ->
        println("�� DEBUG: Final pick order ${po.id}: code=${po.code}, status=${po.status}, assignTo=${po.assignTo?.id}, doId=${po.deliveryOrder?.id}")
    }
    
    val pickOrderIds = filteredPickOrders.map { it.id!! }
    println(" Pick order IDs to fetch: $pickOrderIds")
    
    if (pickOrderIds.isEmpty()) {
        return emptyList()
    }
    
    // Use the same SQL query as the auto-assign version but without the auto-assignment
    val pickOrderIdsStr = pickOrderIds.joinToString(",")
    
    val sql = """
        SELECT 
            -- Pick Order Information
            po.id as pickOrderId,
            po.code as pickOrderCode,
            po.consoCode as pickOrderConsoCode,
            DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
            po.type as pickOrderType,
            po.status as pickOrderStatus,
            po.assignTo as pickOrderAssignTo,
            
            -- Pick Order Line Information
            pol.id as pickOrderLineId,
            pol.qty as pickOrderLineRequiredQty,
            pol.status as pickOrderLineStatus,
            
            -- Item Information
            i.id as itemId,
            i.code as itemCode,
            i.name as itemName,
            uc.code as uomCode,
            uc.udfudesc as uomDesc,
            uc.udfShortDesc as uomShortDesc,
            
            -- Lot Information (similar to lot-details endpoint)
            ill.id as lotId,
            il.lotNo,
            DATE_FORMAT(il.expiryDate, '%Y-%m-%d') as expiryDate,
            w.name as location,
            COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
            
            -- Router Information
            r.id as routerId,
            r.index as routerIndex,
            r.route as routerRoute,
            
            -- ✅ FIXED: Set quantities to NULL for rejected lots
            CASE 
                WHEN sol.status = 'rejected' THEN NULL
                ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
            END as availableQty,
            
            -- Required quantity for this lot
            COALESCE(spl.qty, 0) as requiredQty,
            
            -- Actual picked quantity
            COALESCE(sol.qty, 0) as actualPickQty,
            
            -- Suggested pick lot information
            spl.id as suggestedPickLotId,
            ill.status as lotStatus,
            
            -- Stock out line information
            sol.id as stockOutLineId,
            sol.status as stockOutLineStatus,
            COALESCE(sol.qty, 0) as stockOutLineQty,
            
            -- Additional detailed fields from lot-details
            COALESCE(ill.inQty, 0) as inQty, 
            COALESCE(ill.outQty, 0) as outQty,
            COALESCE(ill.holdQty, 0) as holdQty,
            COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
            ill.inventoryLotId as debugInventoryLotId,
            
            -- Calculate total picked quantity by ALL pick orders for this lot
            COALESCE((
                SELECT SUM(sol_all.qty) 
                FROM fpsmsdb.stock_out_line sol_all 
                WHERE sol_all.inventoryLotLineId = ill.id 
                AND sol_all.deleted = false
                AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
            ), 0) as totalPickedByAllPickOrders,
            
            -- Calculate remaining available quantity correctly
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
            
            -- Add detailed debug fields for lotAvailability calculation
            ill.status as debug_ill_status,
            (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) as debug_is_expired,
            sol.status as debug_sol_status,
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as debug_remaining_stock,
            (COALESCE(spl.qty, 0) - COALESCE(sol.qty, 0)) as debug_required_after_picked,
            
            -- Lot availability status (same logic as lot-details)
            CASE 
                -- Check if lot is expired
                WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                -- Check if lot has rejected stock out line for this pick order
                WHEN sol.status = 'rejected' THEN 'rejected'
                -- Check if lot is unavailable due to insufficient stock
                WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                -- Check if lot status is unavailable
                WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                -- Default to available
                ELSE 'available'
            END as lotAvailability,
            
            -- Processing status
            CASE 
                WHEN sol.status = 'completed' THEN 'completed'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN sol.status = 'created' THEN 'pending'
                ELSE 'pending'
            END as processingStatus
            
        FROM fpsmsdb.pick_order po
        JOIN fpsmsdb.pick_order_line pol ON pol.poId = po.id
        JOIN fpsmsdb.items i ON i.id = pol.itemId
        LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = pol.uomId
        LEFT JOIN fpsmsdb.suggested_pick_lot spl ON pol.id = spl.pickOrderLineId

        LEFT JOIN fpsmsdb.inventory_lot_line ill ON spl.suggestedLotLineId = ill.id
                LEFT JOIN fpsmsdb.router r ON r.inventoryLotId = ill.id AND r.deleted = false
        LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
        WHERE po.deleted = false
        AND po.id IN ($pickOrderIdsStr)
        AND pol.deleted = false
        AND po.status IN ('PENDING', 'RELEASED', 'COMPLETED')
        AND po.assignTo = :userId
        AND ill.deleted = false
        AND il.deleted = false
        AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
        ORDER BY 
            CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END, -- Show rejected lots first
            COALESCE(r.index, 0) ASC, 
            po.code ASC,
            i.code ASC,
            il.expiryDate ASC, 
            il.lotNo ASC
    """.trimIndent()
    
    println("🔍 Executing SQL for all pick order lots with details (no auto-assign): $sql")
    println(" With parameters: userId = $userId, pickOrderIds = $pickOrderIdsStr")
    
    val results = jdbcDao.queryForList(sql, mapOf("userId" to userId))
    println("✅ Total result count: ${results.size}")
    
    // Filter out lots with null availableQty (rejected lots)
    val filteredResults = results.filter { row ->
        val availableQty = row["availableQty"]
        availableQty != null
    }
    
    println("✅ Filtered result count: ${filteredResults.size}")
    
    // ✅ Add router information for each lot
    val enrichedResults = filteredResults.map { row ->
        val inventoryLotId = row["debugInventoryLotId"] as? Number
        
        // Get router information for this inventory lot
        val routerInfo = if (inventoryLotId != null) {
            try {
                val router = routerRepository.findFirstByInventoryLotIdAndDeletedFalse(inventoryLotId.toInt())
                if (router != null) {
                    mapOf(
                        "routerIndex" to (router.index ?: 0),
                        "routerRoute" to (router.route ?: ""),
                        "routerArea" to (router.area ?: ""),
                        "routerItemCode" to (router.itemCode ?: 0),
                        "routerItemName" to (router.itemName ?: ""),
                        "routerUomId" to (router.uomId ?: 0),
                        "routerNoofCarton" to (router.noofCarton ?: 0)
                    )
                } else {
                    mapOf(
                        "routerIndex" to 0,
                        "routerRoute" to "",
                        "routerArea" to "",
                        "routerItemCode" to 0,
                        "routerItemName" to "",
                        "routerUomId" to 0,
                        "routerNoofCarton" to 0
                    )
                }
            } catch (e: Exception) {
                println("⚠️ Error getting router info for inventoryLotId $inventoryLotId: ${e.message}")
                mapOf(
                    "routerIndex" to 0,
                    "routerRoute" to "",
                    "routerArea" to "",
                    "routerItemCode" to 0,
                    "routerItemName" to "",
                    "routerUomId" to 0,
                    "routerNoofCarton" to 0
                )
            }
        } else {
            mapOf(
                "routerIndex" to 0,
                "routerRoute" to "",
                "routerArea" to "",
                "routerItemCode" to 0,
                "routerItemName" to "",
                "routerUomId" to 0,
                "routerNoofCarton" to 0
            )
        }
        
        // Combine original row with router information
        row.toMutableMap().apply {
            putAll(routerInfo)
        }
    }
    
    return enrichedResults
}
// ... existing code ...

// Fix the method signature and return types
open fun getAllPickOrderLotsWithDetailsHierarchical(userId: Long): Map<String, Any?> {
    println("=== Debug: getAllPickOrderLotsWithDetailsHierarchical ===")
    println("today: ${LocalDate.now()}")
    println("userId filter: $userId")
    
    // Get all pick order IDs assigned to the user (both RELEASED and PENDING with doId)
    val user = userService.find(userId).orElse(null)
    if (user == null) {
        println("❌ User not found: $userId")
        return emptyMap()
    }
    val statusList = listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED, 
    //PickOrderStatus.COMPLETED
    )
    
    // Get all pick orders assigned to user with PENDING or RELEASED status that have doId
    val allAssignedPickOrders = pickOrderRepository.findAllByAssignToAndStatusIn(
        user,
        statusList
    ).filter { it.deliveryOrder != null } // Only pick orders with doId
    
    println("🔍 DEBUG: Found ${allAssignedPickOrders.size} pick orders assigned to user $userId")
    val visiblePickOrders = allAssignedPickOrders.filter { pickOrder ->
        val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrder.id!!)
        doPickOrders.none { it.hide } // 只显示 hide = false 的订单
    }
    // ✅ NEW LOGIC: Filter based on assignment and status
    val filteredPickOrders = if (visiblePickOrders.isNotEmpty()) {
        // Check if there are any RELEASED orders assigned to this user (active work)
        val assignedReleasedOrders = allAssignedPickOrders.filter { 
            it.status == PickOrderStatus.RELEASED && it.assignTo?.id == userId
        }
        
        if (assignedReleasedOrders.isNotEmpty()) {
            // ✅ If there are assigned RELEASED orders, show only those
            println("🔍 DEBUG: Found ${assignedReleasedOrders.size} assigned RELEASED orders, showing only those")
            assignedReleasedOrders
        } else {
            // ✅ If no assigned RELEASED orders, show only the latest COMPLETED order
            val completedOrders = allAssignedPickOrders.filter { it.status == PickOrderStatus.COMPLETED }
            if (completedOrders.isNotEmpty()) {
                val latestCompleted = completedOrders.maxByOrNull { it.completeDate ?: it.modified ?: LocalDateTime.MIN }
                println(" DEBUG: No assigned RELEASED orders, showing latest completed order: ${latestCompleted?.code}")
                listOfNotNull(latestCompleted)
            } else {
                println("🔍 DEBUG: No orders found")
                emptyList()
            }
        }
    } else {
        emptyList()
    }
    
    val pickOrderIds = filteredPickOrders.map { it.id!! }
    println("🎯 Pick order IDs to fetch: $pickOrderIds")
    
    if (pickOrderIds.isEmpty()) {
        return mapOf(
            "pickOrder" to null as Any?,
            "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
        )
    }
    
    // Use the same SQL query but transform the results into hierarchical structure
    val pickOrderIdsStr = pickOrderIds.joinToString(",")
    
    val sql = """
        SELECT 
            -- Pick Order Information
            po.id as pickOrderId,
            po.code as pickOrderCode,
            po.consoCode as pickOrderConsoCode,
            DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
            po.type as pickOrderType,
            po.status as pickOrderStatus,
            po.assignTo as pickOrderAssignTo,
            
            -- Pick Order Line Information
            pol.id as pickOrderLineId,
            pol.qty as pickOrderLineRequiredQty,
            pol.status as pickOrderLineStatus,
            
            -- Item Information
            i.id as itemId,
            i.code as itemCode,
            i.name as itemName,
            uc.code as uomCode,
            uc.udfudesc as uomDesc,
            uc.udfShortDesc as uomShortDesc,
            
            -- Lot Information
            ill.id as lotId,
            il.lotNo,
            DATE_FORMAT(il.expiryDate, '%Y-%m-%d') as expiryDate,
            w.name as location,
            COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
            
            -- Router Information
            r.id as routerId,
            r.index as routerIndex,
            r.route as routerRoute,
            r.area as routerArea,
            
            -- ✅ FIXED: Set quantities to NULL for rejected lots
            CASE 
                WHEN sol.status = 'rejected' THEN NULL
                ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
            END as availableQty,
            
            -- Required quantity for this lot
            COALESCE(spl.qty, 0) as requiredQty,
            
            -- Actual picked quantity
            COALESCE(sol.qty, 0) as actualPickQty,
            
            -- Suggested pick lot information
            spl.id as suggestedPickLotId,
            ill.status as lotStatus,
            
            -- Stock out line information
            sol.id as stockOutLineId,
            sol.status as stockOutLineStatus,
            COALESCE(sol.qty, 0) as stockOutLineQty,
            
            -- Additional detailed fields
            COALESCE(ill.inQty, 0) as inQty, 
            COALESCE(ill.outQty, 0) as outQty,
            COALESCE(ill.holdQty, 0) as holdQty,
            COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
            ill.inventoryLotId as debugInventoryLotId,
            
            -- Calculate total picked quantity by ALL pick orders for this lot
            COALESCE((
                SELECT SUM(sol_all.qty) 
                FROM fpsmsdb.stock_out_line sol_all 
                WHERE sol_all.inventoryLotLineId = ill.id 
                AND sol_all.deleted = false
                AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
            ), 0) as totalPickedByAllPickOrders,
            
            -- Calculate remaining available quantity correctly
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
            
            -- Lot availability status
            CASE 
                WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                ELSE 'available'
            END as lotAvailability,
            
            -- Processing status
            CASE 
                WHEN sol.status = 'completed' THEN 'completed'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN sol.status = 'created' THEN 'pending'
                ELSE 'pending'
            END as processingStatus
            
        FROM fpsmsdb.pick_order po
        JOIN fpsmsdb.pick_order_line pol ON pol.poId = po.id
        JOIN fpsmsdb.items i ON i.id = pol.itemId
        LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = pol.uomId
        LEFT JOIN fpsmsdb.suggested_pick_lot spl ON pol.id = spl.pickOrderLineId
        LEFT JOIN fpsmsdb.inventory_lot_line ill ON spl.suggestedLotLineId = ill.id
        LEFT JOIN fpsmsdb.router r ON r.inventoryLotId = ill.id AND r.deleted = false
        LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
        WHERE po.deleted = false
        AND po.id IN ($pickOrderIdsStr)
        AND pol.deleted = false
        AND po.status IN ('PENDING', 'RELEASED', 'COMPLETED')
        AND po.assignTo = :userId
        AND ill.deleted = false
        AND il.deleted = false
        AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
        ORDER BY 
            CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END,
            COALESCE(r.index, 0) ASC, 
            po.code ASC,
            i.code ASC,
            il.expiryDate ASC, 
            il.lotNo ASC
    """.trimIndent()
    
    println("�� Executing SQL for hierarchical structure: $sql")
    println("�� With parameters: userId = $userId, pickOrderIds = $pickOrderIdsStr")
    
    val results = jdbcDao.queryForList(sql, mapOf("userId" to userId))
    println("✅ Total result count: ${results.size}")
    
    // Filter out lots with null availableQty (rejected lots)
   // val filteredResults = results.filter { row ->
        //val availableQty = row["availableQty"]
       // availableQty != null
  //  }
  val filteredResults = results
    println("✅ Filtered result count: ${filteredResults.size}")
    
    // ✅ Transform flat results into hierarchical structure
    if (filteredResults.isEmpty()) {
        return mapOf(
            "pickOrder" to null as Any?,
            "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
        )
    }
    
    // Get pick order info from first row (all rows have same pick order info)
    val firstRow = filteredResults.first()
    val pickOrderInfo = mapOf(
        "id" to firstRow["pickOrderId"],
        "code" to firstRow["pickOrderCode"],
        "consoCode" to firstRow["pickOrderConsoCode"],
        "targetDate" to firstRow["pickOrderTargetDate"],
        "type" to firstRow["pickOrderType"],
        "status" to firstRow["pickOrderStatus"],
        "assignTo" to firstRow["pickOrderAssignTo"]
    )
    
    // Group by pick order line ID to create hierarchical structure
    val pickOrderLinesMap = filteredResults
        .groupBy { it["pickOrderLineId"] as Number }
        .map { (pickOrderLineId, lots) ->
            val firstLot = lots.first()
            
            // Item information (same for all lots of this line)
            val itemInfo = mapOf(
                "id" to firstLot["itemId"],
                "code" to firstLot["itemCode"],
                "name" to firstLot["itemName"],
                "uomCode" to firstLot["uomCode"],
                "uomDesc" to firstLot["uomDesc"]
            )
            
            // Transform lots for this pick order line
            val lotsInfo = lots.map { lot ->
                mapOf(
                    "id" to lot["lotId"],
                    "lotNo" to lot["lotNo"],
                    "expiryDate" to lot["expiryDate"],
                    "location" to lot["location"],
                    "stockUnit" to lot["stockUnit"],
                    "availableQty" to lot["availableQty"],
                    "requiredQty" to lot["requiredQty"],
                    "actualPickQty" to lot["actualPickQty"],
                    "inQty" to lot["inQty"],
                    "outQty" to lot["outQty"],
                    "holdQty" to lot["holdQty"],
                    "lotStatus" to lot["lotStatus"],
                    "lotAvailability" to lot["lotAvailability"],
                    "processingStatus" to lot["processingStatus"],
                    "suggestedPickLotId" to lot["suggestedPickLotId"],
                    "stockOutLineId" to lot["stockOutLineId"],
                    "stockOutLineStatus" to lot["stockOutLineStatus"],
                    "stockOutLineQty" to lot["stockOutLineQty"],
                    "router" to mapOf(
                        "id" to lot["routerId"],
                        "index" to lot["routerIndex"],
                        "route" to lot["routerRoute"],
                        "area" to lot["routerArea"],
                        "itemCode" to firstLot["itemId"],
                        "itemName" to firstLot["itemName"],
                        "uomId" to firstLot["uomShortDesc"],
                        "noofCarton" to lot["requiredQty"] // Use required qty as carton count
                    )
                )
            }
            
            // Pick order line with item and lots
            mapOf(
                "id" to pickOrderLineId,
                "requiredQty" to firstLot["pickOrderLineRequiredQty"],
                "status" to firstLot["pickOrderLineStatus"],
                "item" to itemInfo,
                "lots" to lotsInfo
            )
        }
    
    return mapOf(
        "pickOrder" to pickOrderInfo as Any?,
        "pickOrderLines" to pickOrderLinesMap as Any?
    )
}

// Fix the type issues in the getPickOrdersByDateAndStore method
open fun getPickOrdersByDateAndStore(storeId: String): Map<String, Any?> {
    println("=== Debug: getPickOrdersByDateAndStore ===")
    println("storeId: $storeId")
    
    try {
        val today = LocalDate.now()
        println("Today's date: $today")
        
        // SQL query to get pick orders grouped by date and store
        // Get all available orders for this store
        val sql = """
            SELECT 
                dpo.store_id as storeId,
                DATE_FORMAT(dpo.created, '%Y-%m-%d') as orderDate,
                COUNT(DISTINCT po.id) as orderTotal,
                COUNT(DISTINCT CASE WHEN po.status = 'COMPLETED' THEN po.id END) as orderCompleted,
                GROUP_CONCAT(DISTINCT po.id ORDER BY po.id) as pickOrderIds,
                GROUP_CONCAT(DISTINCT po.code ORDER BY po.id SEPARATOR ',') as pickOrderCodes,
                -- Get do_pick_order details
                GROUP_CONCAT(
                    CONCAT(
                        dpo.id, ':', 
                        dpo.ticket_no, ':', 
                        dpo.pick_order_id
                    ) 
                    ORDER BY dpo.id SEPARATOR '|'
                ) as doPickOrderDetails
            FROM do_pick_order dpo
            JOIN pick_order po ON po.id = dpo.pick_order_id
            WHERE dpo.deleted = false
            AND po.deleted = false
            AND dpo.store_id = :storeId
            GROUP BY dpo.store_id, DATE_FORMAT(dpo.created, '%Y-%m-%d')
            ORDER BY orderDate ASC
        """.trimIndent()
        
        println("🔍 Executing SQL: $sql")
        println("🔍 With parameters: storeId = $storeId")
        
        val results = jdbcDao.queryForList(sql, mapOf("storeId" to storeId))
        println("✅ Found ${results.size} records")
        
        if (results.isEmpty()) {
            return mapOf(
                "storeId" to storeId,
                "orders" to emptyList<Map<String, Any?>>()
            )
        }
        
        // Analyze each date's completion status
        val ordersByDate = results.map { row ->
            val orderDate = LocalDate.parse(row["orderDate"] as String)
            val orderTotal = (row["orderTotal"] as Number).toInt()
            val orderCompleted = (row["orderCompleted"] as Number).toInt()
            val isAllCompleted = orderTotal == orderCompleted && orderTotal > 0
            
            mapOf(
                "orderDate" to orderDate,
                "orderTotal" to orderTotal,
                "orderCompleted" to orderCompleted,
                "isAllCompleted" to isAllCompleted,
                "rawData" to row
            )
        }
        
        println("📊 Orders by date analysis:")
        ordersByDate.forEach { order ->
            println("  Date: ${order["orderDate"]}, Total: ${order["orderTotal"]}, Completed: ${order["orderCompleted"]}, AllCompleted: ${order["isAllCompleted"]}")
        }
        
        // Simple logic: Find the first incomplete date and return it + next 2 days
        val ordersToReturn = mutableListOf<Map<String, Any?>>()
        
        // Find the first incomplete order date
        val firstIncompleteDate = ordersByDate
            .firstOrNull { !(it["isAllCompleted"] as Boolean) }
        
        if (firstIncompleteDate != null) {
            val startDate = firstIncompleteDate["orderDate"] as LocalDate
            println("�� First incomplete date: $startDate")
            
            // Get the incomplete date + next 2 days (total 3 days)
            val targetDates = (0..2).map { days -> startDate.plusDays(days.toLong()) }
            println("🎯 Target dates to return: $targetDates")
            
            // Only return dates that actually exist in the database
            ordersToReturn.addAll(
                ordersByDate.filter { order ->
                    val orderDate = order["orderDate"] as LocalDate
                    targetDates.contains(orderDate)
                }.map { it["rawData"] as Map<String, Any?> }
            )
        } else {
            println("🎯 All orders are completed, returning empty list")
        }
        
        // Transform results into the required structure
        val finalOrders = ordersToReturn.map { row ->
            val doPickOrderDetails = (row["doPickOrderDetails"] as String?)?.split("|") ?: emptyList()
            
            val doPickOrderList = doPickOrderDetails.map { detail ->
                val parts = detail.split(":")
                if (parts.size >= 3) {
                    mapOf<String, Any?>(
                        "doPickOrderId" to parts[0].toLongOrNull(),
                        "ticketNo" to parts[1],
                        "pickOrderId" to parts[2].toLongOrNull()
                    )
                } else {
                    mapOf<String, Any?>(
                        "doPickOrderId" to null,
                        "ticketNo" to "",
                        "pickOrderId" to null
                    )
                }
            }
            
            mapOf<String, Any?>(
                "orderTotal" to row["orderTotal"],
                "orderCompleted" to row["orderCompleted"],
                "orderDate" to row["orderDate"],
                "pickOrderIds" to ((row["pickOrderIds"] as String?)?.split(",")?.mapNotNull { it.toLongOrNull() } ?: emptyList<Long>()),
                "pickOrderCodes" to ((row["pickOrderCodes"] as String?)?.split(",") ?: emptyList<String>()),
                "doPickOrderDetails" to doPickOrderList
            )
        }
        
        println("✅ Final orders to return: ${finalOrders.size}")
        finalOrders.forEach { order ->
            println("  📅 Date: ${order["orderDate"]}, Total: ${order["orderTotal"]}, Completed: ${order["orderCompleted"]}")
        }
        
        return mapOf<String, Any?>(
            "storeId" to storeId,
            "orders" to finalOrders
        )
        
        } catch (e: Exception) {
            println("❌ Error in getPickOrdersByDateAndStore: ${e.message}")
            e.printStackTrace()
            return mapOf<String, Any?>(
                "storeId" to storeId,
                "orders" to emptyList<Map<String, Any?>>(),
                "error" to e.message
            )
    }
}
@Transactional(rollbackFor = [java.lang.Exception::class])
open fun confirmLotSubstitution(req: LotSubstitutionConfirmRequest): MessageResponse {
    val zero = BigDecimal.ZERO

    // Validate entities
    val pol = req.pickOrderLineId.let { pickOrderLineRepository.findById(it).orElse(null) }
        ?: return MessageResponse(id = null, name = "Pick order line not found", code = "ERROR", type = "pickorder",
            message = "Pick order line ${req.pickOrderLineId} not found", errorPosition = null)

    val newIll = req.newInventoryLotLineId.let { inventoryLotLineRepository.findById(it).orElse(null) }
        ?: return MessageResponse(id = null, name = "New lot line not found", code = "ERROR", type = "pickorder",
            message = "Inventory lot line ${req.newInventoryLotLineId} not found", errorPosition = null)

    // Item consistency check
    val polItemId = pol.item?.id
    val newItemId = newIll.inventoryLot?.item?.id
    if (polItemId == null || newItemId == null || polItemId != newItemId) {
        return MessageResponse(
            id = null, name = "Item mismatch", code = "ERROR", type = "pickorder",
            message = "New lot line item does not match pick order line item", errorPosition = null
        )
    }

    // 1) Update suggested pick lot (if provided): move holdQty from old ILL to new ILL and re-point the suggestion
    if (req.originalSuggestedPickLotId != null && req.originalSuggestedPickLotId > 0) {
        // Get current suggested ILL id and qty
        val row = jdbcDao.queryForMap("""
            SELECT spl.suggestedLotLineId AS oldIllId, COALESCE(spl.qty,0) AS qty
            FROM suggested_pick_lot spl
            WHERE spl.id = :splId
        """.trimIndent(), mapOf("splId" to req.originalSuggestedPickLotId)).orElse(null)

        if (row != null) {
            val oldIllId = (row["oldIllId"] as Number?)?.toLong()
            val qty = when (val qtyObj = row["qty"]) {
                is BigDecimal -> qtyObj
                is Number -> qtyObj.toDouble().toBigDecimal()
                is String -> qtyObj.toBigDecimalOrNull() ?: zero
                else -> zero
            }

            if (oldIllId != null && oldIllId != req.newInventoryLotLineId) {
                // Decrease hold on old, increase on new
                val oldIll = inventoryLotLineRepository.findById(oldIllId).orElse(null)
                if (oldIll != null) {
                    oldIll.holdQty = (oldIll.holdQty ?: zero).minus(qty).max(zero)
                    inventoryLotLineRepository.save(oldIll)
                }
                val newIllEntity = inventoryLotLineRepository.findById(req.newInventoryLotLineId).orElse(null)
                if (newIllEntity != null) {
                    newIllEntity.holdQty = (newIllEntity.holdQty ?: zero).plus(qty)
                    inventoryLotLineRepository.save(newIllEntity)
                }
            }

            // Re-point suggestion to new ILL
            jdbcDao.executeUpdate("""
                UPDATE suggested_pick_lot 
                SET suggestedLotLineId = :newIllId
                WHERE id = :splId
            """.trimIndent(), mapOf("newIllId" to req.newInventoryLotLineId, "splId" to req.originalSuggestedPickLotId))
        }
    }

    // 2) Update stock out line (if provided): re-point to new ILL; keep qty and status unchanged
    if (req.stockOutLineId != null && req.stockOutLineId > 0) {
        val sol = stockOutLIneRepository.findById(req.stockOutLineId).orElse(null)
        if (sol != null) {
            sol.inventoryLotLine = newIll
            sol.item = pol.item
            stockOutLIneRepository.save(sol)
        }
    }

    return MessageResponse(
        id = null,
        name = "Lot substitution confirmed",
        code = "SUCCESS",
        type = "pickorder",
        message = "Updated suggestion and stock out line to new lot line ${req.newInventoryLotLineId}",
        errorPosition = null
    )
}
open fun updateDoPickOrderHideStatus(pickOrderId: Long, hide: Boolean): MessageResponse {
    return try {
        // ✅ 修复：根据 pickOrderId 查找 do_pick_order 记录
        val doPickOrders = doPickOrderRepository.findByPickOrderId(pickOrderId)
        if (doPickOrders.isEmpty()) {
            MessageResponse(
                id = null,
                name = "Pick order not found",
                code = "ERROR",
                type = "pickorder",
                message = "No do_pick_order found for pickOrderId $pickOrderId",
                errorPosition = "pickOrderId"
            )
        } else {
            // ✅ 更新所有相关的 do_pick_order 记录
            doPickOrders.forEach { doPickOrder ->
                doPickOrder.hide = hide
                doPickOrderRepository.save(doPickOrder)
            }
            
            MessageResponse(
                id = null,
                name = "Hide status updated",
                code = "SUCCESS",
                type = "pickorder",
                message = "Updated hide status for ${doPickOrders.size} do_pick_order records to $hide",
                errorPosition = null
            )
        }
    } catch (e: Exception) {
        println("❌ Error in updateDoPickOrderHideStatus: ${e.message}")
        e.printStackTrace()
        MessageResponse(
            id = null,
            name = "Failed to update hide status",
            code = "ERROR",
            type = "pickorder",
            message = "Failed to update hide status: ${e.message}",
            errorPosition = null
        )
    }
}
open fun getCompletedDoPickOrders(
    userId: Long,
    pickOrderCode: String?,
    shopName: String?,
    deliveryNo: String?,
    ticketNo: String?
): List<Map<String, Any?>> {
    return try {
        println("=== getCompletedDoPickOrders ===")
        println("userId: $userId")
        println("pickOrderCode: $pickOrderCode")
        println("shopName: $shopName")
        println("deliveryNo: $deliveryNo")
        println("ticketNo: $ticketNo")
        
        // ✅ 修复：使用正确的方法获取已完成的 pick orders
        val user = userRepository.findById(userId).orElse(null)
        if (user == null) {
            println("User not found: $userId")
            return emptyList()
        }
        
        // ✅ 修复：使用正确的方法获取已完成的 pick orders
        val completedPickOrders = pickOrderRepository.findAllByAssignToIdAndStatusIn(
            userId, 
            listOf(PickOrderStatus.COMPLETED)
        )
        
        println("Found ${completedPickOrders.size} completed pick orders for user $userId")
        
        val result = mutableListOf<Map<String, Any?>>()
        
        for (pickOrder in completedPickOrders) {
            // 获取该 pick order 的 FG pick orders
            val fgPickOrders = getnewFgPickOrdersByPickOrderId(pickOrder.id!!)
            
            if (fgPickOrders.isNotEmpty()) {
                val firstFgOrder = fgPickOrders[0] as Map<String, Any?> // ✅ 修复：转换为 Map
                
                // ✅ 修复：使用正确的属性名进行搜索过滤，并处理空值
                val matchesSearch = when {
                    pickOrderCode != null && !(pickOrder.code?.contains(pickOrderCode, ignoreCase = true) ?: false) -> false
                    shopName != null && !((firstFgOrder["shopName"] as? String)?.contains(shopName, ignoreCase = true) ?: false) -> false
                    deliveryNo != null && !((firstFgOrder["deliveryNo"] as? String)?.contains(deliveryNo, ignoreCase = true) ?: false) -> false
                    ticketNo != null && !((firstFgOrder["ticketNo"] as? String)?.contains(ticketNo, ignoreCase = true) ?: false) -> false
                    else -> true
                }
                
                if (matchesSearch) {
                    result.add(mapOf(
                        "id" to pickOrder.id,
                        "pickOrderId" to pickOrder.id,
                        "pickOrderCode" to pickOrder.code,
                        "pickOrderConsoCode" to pickOrder.consoCode,
                        "pickOrderStatus" to pickOrder.status.toString(),
                        "deliveryOrderId" to firstFgOrder["deliveryOrderId"],
                        "deliveryNo" to firstFgOrder["deliveryNo"],
                        "deliveryDate" to firstFgOrder["deliveryDate"],
                        "shopId" to firstFgOrder["shopId"],
                        "shopCode" to firstFgOrder["shopCode"],
                        "shopName" to firstFgOrder["shopName"],
                        "shopAddress" to firstFgOrder["shopAddress"],
                        "ticketNo" to firstFgOrder["ticketNo"],
                        "shopPoNo" to firstFgOrder["shopPoNo"],
                        "numberOfCartons" to firstFgOrder["numberOfCartons"],
                        "truckNo" to firstFgOrder["truckNo"],
                        "storeId" to firstFgOrder["storeId"],
                        "completedDate" to (pickOrder.completeDate?.toString() ?: pickOrder.modified?.toString()),
                        "fgPickOrders" to fgPickOrders
                    ))
                }
            }
        }
        
        // 按完成时间倒序排列
        result.sortByDescending { it["completedDate"] as String? }
        
        println("Returning ${result.size} completed DO pick orders")
        result
        
    } catch (e: Exception) {
        println("❌ Error in getCompletedDoPickOrders: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}
// ... existing code ...

open fun getLotDetailsByPickOrderId(pickOrderId: Long): List<Map<String, Any>> {
    println("=== Debug: getLotDetailsByPickOrderId ===")
    println("pickOrderId: $pickOrderId")
    
    // Get the pick order
    val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
    if (pickOrder == null) {
        println("❌ Pick order not found: $pickOrderId")
        return emptyList()
    }
    
    // ✅ 修复：使用正确的方法名
    val pickOrderLines = pickOrderLineRepository.findAllByPickOrderId(pickOrderId)
    if (pickOrderLines.isEmpty()) {
        println("❌ No pick order lines found for pick order: $pickOrderId")
        return emptyList()
    }
    
    // Build the SQL query similar to getAllPickOrderLotsWithDetailsWithoutAutoAssign
    // but filtered by specific pickOrderId
    val sql = """
        SELECT 
            -- Pick Order Information
            po.id as pickOrderId,
            po.code as pickOrderCode,
            po.consoCode as pickOrderConsoCode,
            DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
            po.type as pickOrderType,
            po.status as pickOrderStatus,
            po.assignTo as pickOrderAssignTo,
            
            -- Pick Order Line Information
            pol.id as pickOrderLineId,
            pol.qty as pickOrderLineRequiredQty,
            pol.status as pickOrderLineStatus,
            
            -- Item Information
            i.id as itemId,
            i.code as itemCode,
            i.name as itemName,
            uc.code as uomCode,
            uc.udfudesc as uomDesc,
            uc.udfShortDesc as uomShortDesc,
            
            -- Lot Information
            ill.id as lotId,
            il.lotNo,
            DATE_FORMAT(il.expiryDate, '%Y-%m-%d') as expiryDate,
            w.name as location,
            COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
            
            -- Router Information
            r.id as routerId,
            r.index as routerIndex,
            r.route as routerRoute,
            
            -- Set quantities to NULL for rejected lots
            CASE 
                WHEN sol.status = 'rejected' THEN NULL
                ELSE (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0))
            END as availableQty,
            
            -- Required quantity for this lot
            COALESCE(spl.qty, 0) as requiredQty,
            
            -- Actual picked quantity
            COALESCE(sol.qty, 0) as actualPickQty,
            
            -- Suggested pick lot information
            spl.id as suggestedPickLotId,
            ill.status as lotStatus,
            
            -- Stock out line information
            sol.id as stockOutLineId,
            sol.status as stockOutLineStatus,
            COALESCE(sol.qty, 0) as stockOutLineQty,
            
            -- Additional detailed fields
            COALESCE(ill.inQty, 0) as inQty, 
            COALESCE(ill.outQty, 0) as outQty,
            COALESCE(ill.holdQty, 0) as holdQty,
            COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
            ill.inventoryLotId as debugInventoryLotId,
            
            -- Calculate total picked quantity by ALL pick orders for this lot
            COALESCE((
                SELECT SUM(sol_all.qty) 
                FROM fpsmsdb.stock_out_line sol_all 
                WHERE sol_all.inventoryLotLineId = ill.id 
                AND sol_all.deleted = false
                AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
            ), 0) as totalPickedByAllPickOrders,
            
            -- Calculate remaining available quantity correctly
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
            
            -- Add detailed debug fields for lotAvailability calculation
            ill.status as debug_ill_status,
            (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) as debug_is_expired,
            sol.status as debug_sol_status,
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as debug_remaining_stock,
            (COALESCE(spl.qty, 0) - COALESCE(sol.qty, 0)) as debug_required_after_picked,
            
            -- Lot availability status
            CASE 
                -- Check if lot is expired
                WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                -- Check if lot has rejected stock out line for this pick order
                WHEN sol.status = 'rejected' THEN 'rejected'
                -- Check if lot is unavailable due to insufficient stock
                WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                -- Check if lot status is unavailable
                WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                -- Default to available
                ELSE 'available'
            END as lotAvailability,
            
            -- Processing status
            CASE 
                WHEN sol.status = 'completed' THEN 'completed'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN sol.status = 'created' THEN 'pending'
                ELSE 'pending'
            END as processingStatus
            
        FROM fpsmsdb.pick_order po
        JOIN fpsmsdb.pick_order_line pol ON pol.poId = po.id
        JOIN fpsmsdb.items i ON i.id = pol.itemId
        LEFT JOIN fpsmsdb.uom_conversion uc ON uc.id = pol.uomId
        LEFT JOIN fpsmsdb.suggested_pick_lot spl ON pol.id = spl.pickOrderLineId
        LEFT JOIN fpsmsdb.inventory_lot_line ill ON spl.suggestedLotLineId = ill.id
        LEFT JOIN fpsmsdb.router r ON r.inventoryLotId = ill.id AND r.deleted = false
        LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
        WHERE po.deleted = false
        AND po.id = :pickOrderId
        AND pol.deleted = false
        AND ill.deleted = false
        AND il.deleted = false
        AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
        ORDER BY 
            CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END, -- Show rejected lots first
            COALESCE(r.index, 0) ASC, 
            po.code ASC,
            i.code ASC,
            il.expiryDate ASC, 
            il.lotNo ASC
    """.trimIndent()
    
    // ✅ 使用 jdbcDao 而不是 entityManager
    println("🔍 Executing SQL for lot details by pick order: $sql")
    println("🔍 With parameters: pickOrderId = $pickOrderId")
    
    val results = jdbcDao.queryForList(sql, mapOf("pickOrderId" to pickOrderId))
    println("✅ Total result count: ${results.size}")
    
    return results
}



}
