package com.ffii.fpsms.modules.jobOrder.service

import com.ffii.fpsms.modules.jobOrder.entity.JoPickOrder
import com.ffii.fpsms.modules.jobOrder.entity.JoPickOrderRepository
import com.ffii.fpsms.modules.jobOrder.entity.JoPickOrderRecord
import com.ffii.fpsms.modules.jobOrder.entity.JoPickOrderRecordRepository
import com.ffii.fpsms.modules.jobOrder.enums.JoPickOrderStatus
import com.ffii.fpsms.modules.master.web.models.MessageResponse
import com.ffii.fpsms.modules.pickOrder.service.PickOrderService
import com.ffii.fpsms.modules.user.service.UserService
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderRepository
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderStatus
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional

import com.ffii.fpsms.modules.stock.entity.enum.InventoryLotLineStatus
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderLineStatus
import java.time.LocalDateTime
import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.jobOrder.web.model.JobOrderListForPrintQrCodeResponse
import com.ffii.fpsms.modules.jobOrder.web.model.SecondScanSubmitRequest
import java.util.Optional
import com.ffii.fpsms.modules.jobOrder.web.model.SecondScanIssueRequest
import com.ffii.fpsms.modules.pickOrder.entity.PickExecutionIssue
import com.ffii.fpsms.modules.pickOrder.entity.PickExecutionIssueRepository
import java.math.BigDecimal
import com.ffii.fpsms.modules.pickOrder.entity.HandleStatus
import com.ffii.fpsms.modules.pickOrder.entity.IssueCategory
import com.ffii.fpsms.modules.pickOrder.entity.PickOrderLineRepository
import com.ffii.fpsms.modules.stock.entity.SuggestPickLotRepository
import com.ffii.fpsms.modules.stock.entity.InventoryLotLineRepository
import com.ffii.fpsms.modules.stock.entity.InventoryLotRepository
import com.ffii.fpsms.modules.stock.entity.StockOutLIneRepository
import com.ffii.fpsms.modules.jobOrder.web.model.AllJoPickOrderResponse
import com.ffii.fpsms.modules.jobOrder.entity.JobOrderRepository
import com.ffii.fpsms.modules.jobOrder.entity.JobTypeRepository
import com.ffii.fpsms.modules.master.entity.ItemsRepository
import com.ffii.fpsms.modules.productProcess.entity.ProductProcessRepository
import com.ffii.fpsms.modules.jobOrder.enums.JobOrderStatus
import com.ffii.fpsms.modules.productProcess.entity.ProductProcessLineRepository
import com.ffii.fpsms.modules.stock.entity.StockOutRepository
import com.ffii.fpsms.modules.jobOrder.entity.JobOrderProcessRepository
import com.ffii.fpsms.modules.jobOrder.web.model.JobOrderLotsHierarchicalResponse
import com.ffii.fpsms.modules.jobOrder.web.model.PickOrderInfoResponse
import com.ffii.fpsms.modules.jobOrder.web.model.JobOrderBasicInfoResponse
import com.ffii.fpsms.modules.jobOrder.web.model.PickOrderLineWithLotsResponse
import com.ffii.fpsms.modules.jobOrder.web.model.LotDetailResponse

import com.ffii.fpsms.modules.stock.entity.enum.StockInLineStatus
import com.ffii.fpsms.modules.stock.entity.StockInLineRepository
import java.time.LocalDate
import com.ffii.fpsms.modules.jobOrder.web.model.MaterialPickStatusItem
@Service
open class JoPickOrderService(
    private val joPickOrderRepository: JoPickOrderRepository,
    private val joPickOrderRecordRepository: JoPickOrderRecordRepository,
    private val pickOrderService: PickOrderService,
    private val userService: UserService,
    private val pickOrderRepository: PickOrderRepository,
    private val jdbcDao: JdbcDao,
    private val pickExecutionIssueRepository: PickExecutionIssueRepository,
    private val pickOrderLineRepository: PickOrderLineRepository,
    private val suggestPickLotRepository: SuggestPickLotRepository,
    private val inventoryLotLineRepository: InventoryLotLineRepository,
    private val inventoryLotRepository: InventoryLotRepository,
    private val stockOutLineRepository: StockOutLIneRepository,
    private val jobOrderRepository: JobOrderRepository,
    private val jobTypeRepository: JobTypeRepository,
    private val itemsRepository: ItemsRepository,
    private val productProcessRepository: ProductProcessRepository,
    private val productProcessLineRepository: ProductProcessLineRepository,
    private val stockOutRepository: StockOutRepository,
    private val jobOrderProcessRepository: JobOrderProcessRepository,
    private val stockInLineRepository: StockInLineRepository

) {
    
    open fun save(record: JoPickOrder): JoPickOrder {
        return joPickOrderRepository.save(record)
    }
    
    open fun saveRecord(record: JoPickOrderRecord): JoPickOrderRecord {
        return joPickOrderRecordRepository.save(record)
    }
    
    //  Update JoPickOrder status to released
    open fun updateHandledByForPickOrder(pickOrderId: Long, userId: Long?, matchStatus: String?): List<JoPickOrder> {
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrderId)

        // Get pick order details to populate new fields
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        
        joPickOrders.forEach { 
            it.handledBy = userId
            if (matchStatus != null) {               // 只在有传入状态时才修改
                it.matchStatus = JoPickOrderStatus.valueOf(matchStatus)
            }// Set initial status
            
            // Set ticket release time (similar to pick order released date)
            it.ticketReleaseTime = LocalDateTime.now()
            
            // Populate additional fields from pick order
            pickOrder?.let { po ->
                it.pickOrderCode = po.code
                it.targetDate = po.targetDate
                it.jobOrderId = po.jobOrder?.id
                it.jobOrderCode = po.jobOrder?.code
            }
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    //  Complete JoPickOrder
    open fun completeJoPickOrdersForPickOrder(pickOrderId: Long): List<JoPickOrder> {
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrderId)
        joPickOrders.forEach { 
            it.matchStatus = JoPickOrderStatus.completed
            it.ticketCompleteTime = LocalDateTime.now() // Set complete time
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    
    //  Update JoPickOrderRecord status to released
    open fun updateRecordHandledByForPickOrder(pickOrderId: Long, userId: Long?): List<JoPickOrderRecord> {
        val joPickOrderRecords = joPickOrderRecordRepository.findByPickOrderId(pickOrderId)

        // Get pick order details to populate nw fields
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        
        joPickOrderRecords.forEach { 
            it.handledBy = userId
            it.matchStatus = JoPickOrderStatus.pending // Set initial status
            
            // Set ticket release time
            it.ticketReleaseTime = LocalDateTime.now()
            
            // Populate additional fields from pick order
            pickOrder?.let { po ->
                it.pickOrderCode = po.code
                it.targetDate = po.targetDate
                it.jobOrderId = po.jobOrder?.id
                it.jobOrderCode = po.jobOrder?.code
            }
        }
        return joPickOrderRecordRepository.saveAll(joPickOrderRecords)
    }
    
    //  Complete JoPickOrderRecord
    open fun completeJoPickOrderRecordsForPickOrder(pickOrderId: Long): List<JoPickOrderRecord> {
        val joPickOrderRecords = joPickOrderRecordRepository.findByPickOrderId(pickOrderId)
        joPickOrderRecords.forEach { 
            it.matchStatus = JoPickOrderStatus.completed
            it.ticketCompleteTime = LocalDateTime.now() // Set complete time
        }
        return joPickOrderRecordRepository.saveAll(joPickOrderRecords)
    }
    
    //  Find JoPickOrder records by pick order ID
    open fun findByPickOrderId(pickOrderId: Long): List<JoPickOrder> {
        return joPickOrderRepository.findByPickOrderId(pickOrderId)
    }
    
    //  Find JoPickOrderRecord records by pick order ID
    open fun findRecordsByPickOrderId(pickOrderId: Long): List<JoPickOrderRecord> {
        return joPickOrderRecordRepository.findByPickOrderId(pickOrderId)
    }
    
    //  Create JoPickOrder records for a pick order
    @Transactional
    open fun createJoPickOrdersForPickOrder(pickOrderId: Long, itemIds: List<Long>): List<JoPickOrder> {
        // Get pick order and item details
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        
        val joPickOrders = itemIds.map { itemId ->
            // Get item code from database - 修复：使用 queryForList
            val itemCodeResult = jdbcDao.queryForList(
                "SELECT code FROM fpsmsdb.items WHERE id = :itemId",
                mapOf("itemId" to itemId)
            )
            val itemCode = if (itemCodeResult.isNotEmpty()) {
                itemCodeResult.first()["code"] as String?
            } else {
                null
            }
            
            JoPickOrder(
                pickOrderId = pickOrderId,
                itemId = itemId,
                matchStatus = JoPickOrderStatus.pending,
                handledBy = null,
                jobOrderId = pickOrder?.jobOrder?.id,
                jobOrderCode = pickOrder?.jobOrder?.code,
                targetDate = pickOrder?.targetDate,
                itemCode = itemCode,
                pickOrderCode = pickOrder?.code
            )
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    //  Create JoPickOrderRecord records for a pick order
    @Transactional
    open fun createJoPickOrderRecordsForPickOrder(pickOrderId: Long, itemIds: List<Long>): List<JoPickOrderRecord> {
        // Get pick order and item details
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        
        val joPickOrderRecords = itemIds.map { itemId ->
            // Get item code from database - 修复：使用 queryForList
            val itemCodeResult = jdbcDao.queryForList(
                "SELECT code FROM fpsmsdb.items WHERE id = :itemId",
                mapOf("itemId" to itemId)
            )
            val itemCode = if (itemCodeResult.isNotEmpty()) {
                itemCodeResult.first()["code"] as String?
            } else {
                null
            }
            
            JoPickOrderRecord(
                pickOrderId = pickOrderId,
                itemId = itemId,
                matchStatus = JoPickOrderStatus.pending,
                handledBy = null,
                jobOrderId = pickOrder?.jobOrder?.id,
                jobOrderCode = pickOrder?.jobOrder?.code,
                targetDate = pickOrder?.targetDate,
                itemCode = itemCode,
                pickOrderCode = pickOrder?.code,

            )
        }
        return joPickOrderRecordRepository.saveAll(joPickOrderRecords)
    }
    open fun getAllJobOrderLotsWithDetailsHierarchical(userId: Long): JobOrderLotsHierarchicalResponse {
        println("=== Debug: getAllJobOrderLotsWithDetailsHierarchical ===")
        println("today: ${LocalDate.now()}")
        println("userId filter: $userId")
        
        // Get all pick order IDs assigned to the user (both RELEASED and PENDING with joId)
        val user = userService.find(userId).orElse(null)
        if (user == null) {
            println("❌ User not found: $userId")
            // ✅ 修复：返回 JobOrderLotsHierarchicalResponse
            return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = null,
                    code = null,
                    consoCode = null,
                    targetDate = null,
                    type = null,
                    status = null,
                    assignTo = null,
                    jobOrder = JobOrderBasicInfoResponse(0, "", "")
                ),
                pickOrderLines = emptyList()
            )
        }
        val statusList = listOf(PickOrderStatus.PENDING, PickOrderStatus.RELEASED)

        // Get all pick orders assigned to user with PENDING or RELEASED status that have joId
        val allAssignedPickOrders = pickOrderRepository.findAllByAssignToIdAndStatusIn(
            userId,
            statusList
        ).filter { it.jobOrder != null } // Only pick orders with joId

        println("    DEBUG: Found ${allAssignedPickOrders.size} job order pick orders assigned to user $userId")

        // 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 job 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 job orders, showing latest completed order: ${latestCompleted?.code}")
                    listOfNotNull(latestCompleted)
                } else {
                    println("    DEBUG: No job orders found")
                    emptyList()
                }
            }
        } else {
            emptyList()
        }

        val pickOrderIds = filteredPickOrders.map { it.id!! }
        println("   Job Order Pick order IDs to fetch: $pickOrderIds")

        if (pickOrderIds.isEmpty()) {
            return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = null,
                    code = null,
                    consoCode = null,
                    targetDate = null,
                    type = null,
                    status = null,
                    assignTo = null,
                    jobOrder = JobOrderBasicInfoResponse(0, "", "")
                ),
                pickOrderLines = emptyList()
            )
        }

        // 使用 Repository 获取数据
        try {
            val pickOrders = pickOrderRepository.findAllByIdIn(pickOrderIds)
                .filter { it.deleted == false && it.assignTo?.id == userId }

            if (pickOrders.isEmpty()) {
                return JobOrderLotsHierarchicalResponse(
                    pickOrder = PickOrderInfoResponse(
                        id = null,
                        code = null,
                        consoCode = null,
                        targetDate = null,
                        type = null,
                        status = null,
                        assignTo = null,
                        jobOrder = JobOrderBasicInfoResponse(0, "", "")
                    ),
                    pickOrderLines = emptyList()
                )
            }

            val pickOrder = pickOrders.first() // 取第一个（应该只有一个）
            val jobOrder = pickOrder.jobOrder ?: return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = null,
                    code = null,
                    consoCode = null,
                    targetDate = null,
                    type = null,
                    status = null,
                    assignTo = null,
                    jobOrder = JobOrderBasicInfoResponse(0, "", "")
                ),
                pickOrderLines = emptyList()
            )
            // 获取 pick order lines
            val pickOrderLines = pickOrderLineRepository.findAllByPickOrderId(pickOrder.id!!)
                .filter { it.deleted == false }

            // 获取所有 pick order line IDs
            val pickOrderLineIds = pickOrderLines.map { it.id!! }

            // 获取 suggested pick lots
            val suggestedPickLots = if (pickOrderLineIds.isNotEmpty()) {
                suggestPickLotRepository.findAllByPickOrderLineIdIn(pickOrderLineIds)
                    .filter { it.deleted == false }
            } else {
                emptyList()
            }

            // 获取所有 inventory lot line IDs
            val inventoryLotLineIds = suggestedPickLots.mapNotNull { it.suggestedLotLine?.id }

            // 获取 inventory lot lines
            val inventoryLotLines = if (inventoryLotLineIds.isNotEmpty()) {
                inventoryLotLineRepository.findAllByIdIn(inventoryLotLineIds)
                    .filter { it.deleted == false }
            } else {
                emptyList()
            }

            // 获取 inventory lots
            val inventoryLotIds = inventoryLotLines.mapNotNull { it.inventoryLot?.id }.distinct()
            val inventoryLots = if (inventoryLotIds.isNotEmpty()) {
                inventoryLotRepository.findAllByIdIn(inventoryLotIds)
                    .filter { it.deleted == false }
            } else {
                emptyList()
            }

            // 获取 stock out lines
            val stockOutLines = if (pickOrderLineIds.isNotEmpty() && inventoryLotLineIds.isNotEmpty()) {
                pickOrderLineIds.flatMap { polId ->
                    inventoryLotLineIds.flatMap { illId ->
                        stockOutLineRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
                    }
                }
            } else {
                emptyList()
            }
            // 取得所有 stock out line（含無 lot 情況）
            val stockOutLinesByPickOrderLine = pickOrderLineIds.associateWith { polId ->
                stockOutLineRepository.findAllByPickOrderLineIdAndDeletedFalse(polId)
            }
            // 获取 jo_pick_order 记录
            val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrder.id!!)

            // 构建 pick order info
            val pickOrderInfo = mapOf(
                "id" to pickOrder.id,
                "code" to pickOrder.code,
                "consoCode" to pickOrder.consoCode,
                "targetDate" to pickOrder.targetDate?.let {
                    "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                },
                "type" to pickOrder.type?.value,
                "status" to pickOrder.status?.value,
                "assignTo" to pickOrder.assignTo?.id,
                "jobOrder" to mapOf(
                    "id" to jobOrder.id,
                    "code" to jobOrder.code,
                    "name" to "Job Order ${jobOrder.code}"
                )
            )

            // 构建 pick order lines with lots
            val pickOrderLinesResult = pickOrderLines.map { pol ->
                val item = pol.item
                val uom = pol.uom
                val lineId = pol.id!!
                val lineSuggestedLots = suggestedPickLots.filter { it.pickOrderLine?.id == pol.id }
                val jpo = joPickOrders.firstOrNull { it.itemId == item?.id }
                println("jpo: $jpo")
                val user = userService.find(jpo?.handledBy?:0L)
                println("user: $user")
                val handlerName = jpo?.handledBy?.let { uid ->
                    userService.find(uid).orElse(null)?.name
                }
                println("handlerName: $handlerName")
                // 构建 lots 数据
                val lots = lineSuggestedLots.mapNotNull { spl ->
                    val ill = spl.suggestedLotLine
                    if (ill == null || ill.deleted == true) return@mapNotNull null

                    val il = ill.inventoryLot
                    if (il == null || il.deleted == true) return@mapNotNull null

                    val warehouse = ill.warehouse
                    val sol = stockOutLines.firstOrNull {
                        it.pickOrderLine?.id == pol.id && it.inventoryLotLine?.id == ill.id
                    }
                    val jpo = joPickOrders.firstOrNull { it.itemId == item?.id }
                    val handlerName = jpo?.handledBy?.let { uid ->
                        userService.find(uid).orElse(null)?.name
                    }
                    println("handlerName: $handlerName")
                    val availableQty = if (sol?.status == "rejected") {
                        null
                    } else {
                        (ill.inQty ?: BigDecimal.ZERO) - (ill.outQty ?: BigDecimal.ZERO) - (ill.holdQty
                            ?: BigDecimal.ZERO)
                    }

                    val lotAvailability = when {
                        il.expiryDate != null && il.expiryDate!!.isBefore(LocalDate.now()) -> "expired"
                        sol?.status == "rejected" -> "rejected"
                        availableQty != null && availableQty <= BigDecimal.ZERO -> "insufficient_stock"
                        ill.status == InventoryLotLineStatus.UNAVAILABLE -> "status_unavailable"
                        else -> "available"
                    }

                    val processingStatus = when (sol?.status) {
                        "completed" -> "completed"
                        "rejected" -> "rejected"
                        "created" -> "pending"
                        else -> "pending"
                    }

                    LotDetailResponse(
                        lotId = ill.id,
                        lotNo = il.lotNo,
                        expiryDate = il.expiryDate?.let {
                            "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                        },
                        location = warehouse?.code,
                        availableQty = availableQty?.toDouble(),
                        requiredQty = spl.qty?.toDouble() ?: 0.0,
                        actualPickQty = sol?.qty ?: 0.0,
                        processingStatus = processingStatus,
                        lotAvailability = lotAvailability,
                        pickOrderId = pickOrder.id,
                        pickOrderCode = pickOrder.code,
                        pickOrderConsoCode = pickOrder.consoCode,
                        pickOrderLineId = pol.id,
                        stockOutLineId = sol?.id,
                        suggestedPickLotId = spl.id,
                        stockOutLineQty = sol?.qty ?: 0.0,
                        stockOutLineStatus = sol?.status,
                        routerIndex = warehouse?.order?.toString(),
                        routerArea = warehouse?.code,
                        routerRoute = warehouse?.code,
                        uomShortDesc = uom?.udfShortDesc,
                        matchStatus = jpo?.matchStatus?.value,
                        matchBy = jpo?.matchBy,
                        matchQty = jpo?.matchQty?.toDouble(),
                        stockInLineId = null,
                    )
                }

                PickOrderLineWithLotsResponse(
                    id = pol.id!!,
                    itemId = item?.id,
                    itemCode = item?.code,
                    itemName = item?.name,
                    requiredQty = pol.qty?.toDouble(),
                    uomCode = uom?.code,
                    uomDesc = uom?.udfudesc,
                    status = pol.status?.value,
                    lots = lots,
                    handler = handlerName
                )
            }

            // ✅ 修复第469行：返回 JobOrderLotsHierarchicalResponse
            return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = pickOrder.id,
                    code = pickOrder.code,
                    consoCode = pickOrder.consoCode,
                    targetDate = pickOrder.targetDate?.let {
                        "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                    },
                    type = pickOrder.type?.value,
                    status = pickOrder.status?.value,
                    assignTo = pickOrder.assignTo?.id,
                    jobOrder = JobOrderBasicInfoResponse(
                        id = jobOrder.id!!,
                        code = jobOrder.code ?: "", // ✅ 修复第482行：处理空值
                        name = "Job Order ${jobOrder.code ?: ""}"
                    )
                ),
                pickOrderLines = pickOrderLinesResult // ✅ 修复第486行：使用转换后的结果
            )

        } catch (e: Exception) {
            println("❌ Error executing Job Order hierarchical query: ${e.message}")
            e.printStackTrace()
            // ✅ 修复 catch 块：返回 JobOrderLotsHierarchicalResponse 而不是 Map
            return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = null,
                    code = null,
                    consoCode = null,
                    targetDate = null,
                    type = null,
                    status = null,
                    assignTo = null,
                    jobOrder = JobOrderBasicInfoResponse(0, "", "")
                ),
                pickOrderLines = emptyList()
            )
        }
    }
    // Get completed job order pick orders (for second tab)
    //  Fix the getCompletedJobOrderLotsHierarchical method
    open fun getCompletedJobOrderLotsHierarchical(userId: Long): Map<String, Any?> {
        println("=== Debug: getCompletedJobOrderLotsHierarchical (repo version) ===")
        println("today: ${LocalDate.now()}")
        println("userId filter: $userId")
    
        val user = userService.find(userId).orElse(null)
        if (user == null) {
            println("❌ User not found: $userId")
            return mapOf("pickOrder" to null, "pickOrderLines" to emptyList<Map<String, Any?>>())
        }
    
        // 1. 找出当前用户所有 COMPLETED 的 JO pick order
        val completedPickOrders = pickOrderRepository
            .findAllByAssignToIdAndStatusIn(userId, listOf(PickOrderStatus.COMPLETED))
            .filter { it.jobOrder != null }
    
        println("    DEBUG: Found ${completedPickOrders.size} completed job order pick orders assigned to user $userId")
    
        if (completedPickOrders.isEmpty()) {
            return mapOf("pickOrder" to null, "pickOrderLines" to emptyList<Map<String, Any?>>())
        }
    
        // 2. 取最新的一张
        val latestCompletedOrder = completedPickOrders.maxByOrNull {
            it.completeDate ?: it.modified ?: LocalDateTime.MIN
        } ?: return mapOf("pickOrder" to null, "pickOrderLines" to emptyList<Map<String, Any?>>())
    
        println("    DEBUG: Using latest completed order: ${latestCompletedOrder.code}")
    
        val pickOrder = latestCompletedOrder
        val jobOrder = pickOrder.jobOrder!!
    
        // 3. 组装 pickOrder 信息
        val pickOrderInfo = mapOf(
            "id" to pickOrder.id,
            "code" to pickOrder.code,
            "consoCode" to pickOrder.consoCode,
            "targetDate" to pickOrder.targetDate?.let {
                "${it.year}-${"%02d".format(it.monthValue)}-${"%02d".format(it.dayOfMonth)}"
            },
            "type" to "jo",
            "status" to pickOrder.status?.value,
            "assignTo" to pickOrder.assignTo?.id,
            "jobOrder" to mapOf(
                "id" to jobOrder.id,
                "code" to jobOrder.code,
                "name" to "Job Order ${jobOrder.code}"
            )
        )
    
        // 4. 找出所有 pick order line
        val pickOrderLines = pickOrderLineRepository
            .findAllByPickOrderId(pickOrder.id!!)
            .filter { it.deleted == false }
    
        if (pickOrderLines.isEmpty()) {
            return mapOf("pickOrder" to pickOrderInfo, "pickOrderLines" to emptyList<Map<String, Any?>>())
        }
    
        val pickOrderLineIds = pickOrderLines.mapNotNull { it.id }
    
        // 5. 建议拣货 lot
        val suggestedPickLots = if (pickOrderLineIds.isNotEmpty()) {
            suggestPickLotRepository
                .findAllByPickOrderLineIdIn(pickOrderLineIds)
                .filter { it.deleted == false }
        } else emptyList()
    
        val inventoryLotLineIds = suggestedPickLots.mapNotNull { it.suggestedLotLine?.id }
    
        val inventoryLotLines = if (inventoryLotLineIds.isNotEmpty()) {
            inventoryLotLineRepository
                .findAllByIdIn(inventoryLotLineIds)
                .filter { it.deleted == false }
        } else emptyList()
    
        val inventoryLotIds = inventoryLotLines.mapNotNull { it.inventoryLot?.id }.distinct()
        val inventoryLots = if (inventoryLotIds.isNotEmpty()) {
            inventoryLotRepository
                .findAllByIdIn(inventoryLotIds)
                .filter { it.deleted == false }
        } else emptyList()
    
        // 6. stock_out_line（含所有状态）
        val stockOutLines = if (pickOrderLineIds.isNotEmpty() && inventoryLotLineIds.isNotEmpty()) {
            pickOrderLineIds.flatMap { polId ->
                inventoryLotLineIds.flatMap { illId ->
                    stockOutLineRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
                }
            }
        } else emptyList()
    
        // 7. 对应的 jo_pick_order（用于 match_* 和 handler）
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrder.id!!)
    
        // 8. 组装每一行 + lots
        val pickOrderLinesResult = pickOrderLines.map { pol ->
            val item = pol.item
            val uom = pol.uom
            val lineSuggestedLots = suggestedPickLots.filter { it.pickOrderLine?.id == pol.id }
    
            val jpo = joPickOrders.firstOrNull { it.itemId == item?.id }
            val handlerName = jpo?.matchBy?.let { uid ->
                userService.find(uid).orElse(null)?.name
            }
    
            val lots = lineSuggestedLots.mapNotNull { spl ->
                val ill = spl.suggestedLotLine ?: return@mapNotNull null
                if (ill.deleted == true) return@mapNotNull null
    
                val il = ill.inventoryLot ?: return@mapNotNull null
                if (il.deleted == true) return@mapNotNull null
    
                val warehouse = ill.warehouse
                val sol = stockOutLines.firstOrNull {
                    it.pickOrderLine?.id == pol.id && it.inventoryLotLine?.id == ill.id
                }
    
                val availableQty = if (sol?.status == "rejected") {
                    null
                } else {
                    (ill.inQty ?: BigDecimal.ZERO) -
                        (ill.outQty ?: BigDecimal.ZERO) -
                        (ill.holdQty ?: BigDecimal.ZERO)
                }
    
                val lotAvailability = when {
                    il.expiryDate != null && il.expiryDate!!.isBefore(LocalDate.now()) -> "expired"
                    sol?.status == "rejected" -> "rejected"
                    availableQty != null && availableQty <= BigDecimal.ZERO -> "insufficient_stock"
                    ill.status == InventoryLotLineStatus.UNAVAILABLE -> "status_unavailable"
                    else -> "available"
                }
    
                val processingStatus = when (sol?.status) {
                    "completed" -> "completed"
                    "rejected" -> "rejected"
                    "created" -> "pending"
                    else -> "pending"
                }
    
                mapOf(
                    "lotId" to ill.id,
                    "lotNo" to il.lotNo,
                    "expiryDate" to il.expiryDate?.let {
                        "${it.year}-${"%02d".format(it.monthValue)}-${"%02d".format(it.dayOfMonth)}"
                    },
                    "location" to warehouse?.code,
                    "availableQty" to availableQty?.toDouble(),
                    "requiredQty" to (spl.qty?.toDouble() ?: 0.0),
                    "actualPickQty" to (sol?.qty ?: 0.0),
                    "processingStatus" to processingStatus,
                    "lotAvailability" to lotAvailability,
                    "pickOrderId" to pickOrder.id,
                    "pickOrderCode" to pickOrder.code,
                    "pickOrderConsoCode" to pickOrder.consoCode,
                    "pickOrderLineId" to pol.id,
                    "stockOutLineId" to sol?.id,
                    "stockOutLineStatus" to sol?.status,
                    "stockOutLineQty" to (sol?.qty ?: 0.0),
                    "routerIndex" to warehouse?.order?.toIntOrNull(),
                    "routerArea" to warehouse?.code,
                    "routerRoute" to warehouse?.code,
                    "uomShortDesc" to uom?.udfShortDesc,
                    "matchStatus" to jpo?.matchStatus?.value,
                    "matchBy" to jpo?.matchBy,
                    "matchQty" to jpo?.matchQty,
                    "handler" to handlerName
                )
            }
    
            mapOf(
                "id" to pol.id,
                "itemId" to item?.id,
                "itemCode" to item?.code,
                "itemName" to item?.name,
                "requiredQty" to pol.qty?.toDouble(),
                "uomCode" to uom?.code,
                "uomDesc" to uom?.udfudesc,
                "handler" to handlerName,
                "lots" to lots
            )
        }
    
        return mapOf(
            "pickOrder" to pickOrderInfo,
            "pickOrderLines" to pickOrderLinesResult
        )
    }

    // Get completed job order pick order records (for third tab)
    open fun getCompletedJobOrderPickOrderRecords(userId: Long): List<Map<String, Any?>> {
        println("=== getCompletedJobOrderPickOrderRecords ===")
        println("userId: $userId")
        
        return try {
            // Get all completed JoPickOrderRecord records for the user
            val allRecords = joPickOrderRecordRepository.findAll()
            val completedRecords = allRecords.filter { 
                it.matchStatus == JoPickOrderStatus.completed &&
                it.handledBy == userId
            }

            println("Found ${completedRecords.size} completed job order pick order records for user $userId")

            val recordDetails = completedRecords.mapNotNull { record ->
                // Get pick order details
                val pickOrder = pickOrderRepository.findById(record.pickOrderId!!).orElse(null)
                if (pickOrder != null && pickOrder.jobOrder != null) {
                    val jobOrder = pickOrder.jobOrder
                    mapOf(
                        "recordId" to record.id,
                        "pickOrderId" to pickOrder.id,
                        "pickOrderCode" to pickOrder.code,
                        "jobOrderId" to jobOrder?.id,
                        "jobOrderCode" to jobOrder?.code,
                        "jobOrderName" to jobOrder?.bom?.name,
                        "itemId" to record.itemId,
                        "match_status" to record.matchStatus,
                        "match_by" to record.matchBy,
                        "match_qty" to record.matchQty,
                        "handledBy" to record.handledBy,
                        "created" to record.created,
                        "completeDate" to pickOrder.completeDate
                    )
                } else {
                    println("❌ Record ${record.id} has no valid pick order or job order, skipping.")
                    null
                }
            }

            println("Returning ${recordDetails.size} completed job order pick order records")
            recordDetails
        } catch (e: Exception) {
            println("❌ Error in getCompletedJobOrderPickOrderRecords: ${e.message}")
            e.printStackTrace()
            emptyList()
        }
    }

  // 修复 getUnassignedJobOrderPickOrders 方法
open fun getUnassignedJobOrderPickOrders(): List<Map<String, Any?>> {
    println("=== getUnassignedJobOrderPickOrders ===")
    
    return try {
        //  修复：使用正确的 repository 方法
        val unassignedPickOrders = pickOrderRepository.findAllByStatusAndAssignToIsNullAndDeletedFalse(
            com.ffii.fpsms.modules.pickOrder.enums.PickOrderStatus.RELEASED
        ).filter { pickOrder -> 
            pickOrder.jobOrder != null // Only job order pick orders
        }

        println("Found ${unassignedPickOrders.size} unassigned job order pick orders")

        val jobOrderPickOrders = unassignedPickOrders.mapNotNull { pickOrder ->
            val jobOrder = pickOrder.jobOrder
            if (jobOrder != null) {
                mapOf(
                    "pickOrderId" to pickOrder.id,
                    "pickOrderCode" to pickOrder.code,
                    "pickOrderConsoCode" to pickOrder.consoCode,
                    "pickOrderTargetDate" to pickOrder.targetDate,
                    "pickOrderStatus" to pickOrder.status,
                    "jobOrderId" to jobOrder.id,
                    "jobOrderCode" to jobOrder.code,
                    "jobOrderName" to jobOrder.bom?.name,
                    "reqQty" to jobOrder.reqQty,
                    "uom" to jobOrder.bom?.uom?.code,
                    "planStart" to jobOrder.planStart,
                    "planEnd" to jobOrder.planEnd
                )
            } else {
                println("❌ Pick order ${pickOrder.id} has no job order, skipping.")
                null
            }
        }

        println("Returning ${jobOrderPickOrders.size} unassigned job order pick orders")
        jobOrderPickOrders
    } catch (e: Exception) {
        println("❌ Error in getUnassignedJobOrderPickOrders: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}

//  修复：assignJobOrderPickOrderToUser 方法中的自引用问题
@Transactional
open fun assignJobOrderPickOrderToUser(pickOrderId: Long, userId: Long): MessageResponse {
    println("=== assignJobOrderPickOrderToUser ===")
    println("pickOrderId: $pickOrderId, userId: $userId")
    
    return try {
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        if (pickOrder == null) {
        return MessageResponse(
            id = null,
            code = null,
            name = null,
            type = null,
                message = "Pick order not found",
                errorPosition = null
        )
    }
    
        if (pickOrder.assignTo != null) {
        return MessageResponse(
            id = null,
            code = null,
            name = null,
            type = null,
                message = "Pick order is already assigned",
                errorPosition = null
            )
        }
        
        // Assign pick order to user
        pickOrder.assignTo = userService.find(userId).orElse(null)
        pickOrderRepository.save(pickOrder)
        
        //  修复：使用 this 而不是 joPickOrderService
        this.updateHandledByForPickOrder(pickOrderId, userId, null)
        this.updateRecordHandledByForPickOrder(pickOrderId, userId)
        MessageResponse(
            id = pickOrder.id,
            code = pickOrder.code,
            name = pickOrder.jobOrder?.bom?.name,
            type = null,
            message = "Successfully assigned",
            errorPosition = null
        )
    } catch (e: Exception) {
        println("❌ Error in assignJobOrderPickOrderToUser: ${e.message}")
        e.printStackTrace()
        MessageResponse(
            id = null,
            code = null,
            name = null,
            type = null,
            message = "Error occurred: ${e.message}",
            errorPosition = null
        )
    }
    }
    open fun unAssignJobOrderPickOrderToUser(pickOrderId: Long): MessageResponse {
        println("=== unAssignJobOrderPickOrderToUser ===")
        println("pickOrderId: $pickOrderId")
        
        return try {
            val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
            if (pickOrder == null) {
                return MessageResponse(
                    id = null,
                    code = null,
                    name = null,
                    type = null,
                    message = "Pick order not found",
                    errorPosition = null
                )
            }
            pickOrder.assignTo = null
            pickOrderRepository.save(pickOrder)
            this.updateHandledByForPickOrder(pickOrderId, null,null)
            this.updateRecordHandledByForPickOrder(pickOrderId, null)
            MessageResponse(
                id = pickOrder.id,
                code = pickOrder.code,
                name = pickOrder.jobOrder?.bom?.name,
                type = null,
                message = "Successfully unassigned",
                errorPosition = null
            )
        } catch (e: Exception) {
            println("❌ Error in unAssignJobOrderPickOrderToUser: ${e.message}")
            e.printStackTrace()
            MessageResponse(
                id = null,
                code = null,
                name = null,
                type = null,
                message = "Error occurred: ${e.message}",
                errorPosition = null
            )
        }
    }
    //  Fix the updateMatchStatus method
    open fun updateMatchStatus(pickOrderId: Long, itemId: Long, userId: Long, qty: Int): MessageResponse {
        try {
            println("=== Debug: updateMatchStatus ===")
            println("pickOrderId: $pickOrderId, itemId: $itemId, userId: $userId, qty: $qty")
            
            val joPickOrder = joPickOrderRepository.findByPickOrderIdAndItemId(pickOrderId, itemId)
            
            if (joPickOrder.isEmpty) {
                return MessageResponse(
                    id = null,
                    name = null,
                    code = "ERROR",
                    type = "NOT_FOUND",
                    message = "Job Order Pick Order not found for pickOrderId: $pickOrderId, itemId: $itemId",
                    errorPosition = null
                )
            }
            
            val joPickOrderEntity = joPickOrder.get()
            
            //  设置扫描状态和相关字段
            joPickOrderEntity.matchStatus = JoPickOrderStatus.scanned
            joPickOrderEntity.matchBy = userId
            joPickOrderEntity.matchQty = qty  //  使用传递的 qty
            joPickOrderRepository.save(joPickOrderEntity)
            
            //  同时更新 jo_pick_order_record
            val joPickOrderRecord = joPickOrderRecordRepository.findByPickOrderIdAndItemId(pickOrderId, itemId)
            if (joPickOrderRecord.isPresent) {
                val recordEntity = joPickOrderRecord.get()
                recordEntity.matchStatus = JoPickOrderStatus.scanned
                recordEntity.matchBy = userId
                recordEntity.matchQty = qty  //  使用相同的 qty
                joPickOrderRecordRepository.save(recordEntity)
            }
            
            println(" Updated match status: pickOrderId=$pickOrderId, itemId=$itemId, matchQty=$qty")
            
            return MessageResponse(
                id = null,
                name = null,
                code = "SUCCESS",
                type = "UPDATED",
                message = "Second QR scan status updated to scanned with quantity $qty",
                errorPosition = null
            )
            
        } catch (e: Exception) {
            println("❌ Error updating second QR scan status: ${e.message}")
            return MessageResponse(
                id = null,
                name = null,
                code = "ERROR",
                type = "EXCEPTION",
                message = "Error updating second QR scan status: ${e.message}",
                errorPosition = null
            )
        }
    }
open fun submitSecondScanQty(request: SecondScanSubmitRequest): MessageResponse {
    try {
        println("=== Debug: submitSecondScanQty ===")
        println("pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}, qty: ${request.qty}")
        
        val joPickOrder = joPickOrderRepository.findByPickOrderIdAndItemId(request.pickOrderId, request.itemId)
        
        if (joPickOrder.isEmpty) {
            return MessageResponse(
                id = null,
                name = null,
                code = "ERROR",
                type = "NOT_FOUND",
                message = "Job Order Pick Order not found for pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}",
                errorPosition = null
            )
        }
        
        val joPickOrderEntity = joPickOrder.get()
        joPickOrderEntity.matchQty = request.qty.toInt()
        
        //  Set status to completed when submitting quantity
        joPickOrderEntity.matchStatus = JoPickOrderStatus.completed
        
        //  添加：如果 ticketCompleteTime 还没设置，现在设置（通常已经在拣货完成时设置了）
        if (joPickOrderEntity.ticketCompleteTime == null) {
            joPickOrderEntity.ticketCompleteTime = LocalDateTime.now()
        }
        
        joPickOrderRepository.save(joPickOrderEntity)
        
        println(" Updated jo_pick_order: status=${joPickOrderEntity.matchStatus}, qty=${joPickOrderEntity.matchQty}, completeTime=${joPickOrderEntity.ticketCompleteTime}")
        
        return MessageResponse(
            id = null,
            name = null,
            code = "SUCCESS",
            type = "UPDATED",
            message = "Second scan quantity submitted successfully",
            errorPosition = null
        )
        
    } catch (e: Exception) {
        println("❌ Error submitting second scan quantity: ${e.message}")
        return MessageResponse(
            id = null,
            name = null,
            code = "ERROR",
            type = "EXCEPTION",
            message = "Error submitting second scan quantity: ${e.message}",
            errorPosition = null
        )
    }
}


//  Fix the recordSecondScanIssue method
open fun recordSecondScanIssue(request: SecondScanIssueRequest): MessageResponse {
    try {
        println("=== Debug: recordSecondScanIssue ===")
        println("pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}")
        
        val joPickOrder = joPickOrderRepository.findByPickOrderIdAndItemId(request.pickOrderId, request.itemId)
        
        if (joPickOrder.isEmpty) {
            return MessageResponse(
                id = null,
                name = null,
                code = "ERROR",
                type = "NOT_FOUND",
                message = "Job Order Pick Order not found for pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}",
                errorPosition = null
            )
        }
        
        //  Get pick order and pick order line details with item and lot information
        val pickOrderDetails = jdbcDao.queryForList(
            """
            SELECT 
                po.code as pickOrderCode,
                DATE_FORMAT(po.created, '%Y-%m-%d') as pickOrderCreateDate,
                pol.id as pickOrderLineId,
                i.code as itemCode,
                i.name as itemName,
                pol.qty as requiredQty,
                u.name as pickerName
            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.user u ON u.id = :createdBy
            WHERE po.id = :pickOrderId 
            AND pol.itemId = :itemId 
            AND pol.deleted = false
            AND po.deleted = false
            """,
            mapOf(
                "pickOrderId" to request.pickOrderId,
                "itemId" to request.itemId,
                "createdBy" to request.createdBy
            )
        )
        
        //  修复：使用正确的变量名 pickOrderDetails
        if (pickOrderDetails.isEmpty()) {
            return MessageResponse(
                id = null,
                name = null,
                code = "ERROR",
                type = "NOT_FOUND",
                message = "Pick order not found for pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}",
                errorPosition = null
            )
        }
        
        //  修复：使用正确的变量名 pickOrderDetails
        val orderData = pickOrderDetails.first()
        val pickOrderCode = orderData["pickOrderCode"] as String?
        val pickOrderCreateDate = orderData["pickOrderCreateDate"]?.let {
            LocalDate.parse(it.toString())
        }
        val pickOrderLineId = (orderData["pickOrderLineId"] as Number).toLong()
        val itemCode = orderData["itemCode"] as String?
        val itemName = orderData["itemName"] as String?
        val pickerName = orderData["pickerName"] as String?
        val requiredQty = orderData["requiredQty"]?.let {
            BigDecimal(it.toString())
        }
        
        //  获取 suggested lot 信息（用于 lot_id, lot_no, store_location）
        val lotResults = jdbcDao.queryForList(
            """
            SELECT 
                ill.id as lotId,
                il.lotNo as lotNo,
                w.name as storeLocation
            FROM fpsmsdb.suggested_pick_lot spl
            JOIN fpsmsdb.inventory_lot_line ill ON spl.suggestedLotLineId = ill.id
            JOIN fpsmsdb.inventory_lot il ON ill.inventoryLotId = il.id
            LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
            WHERE spl.pickOrderLineId = :pickOrderLineId
            AND spl.deleted = false
            AND ill.deleted = false
            LIMIT 1
            """,
            mapOf("pickOrderLineId" to pickOrderLineId)
        )
        
        val lotId = if (lotResults.isNotEmpty()) {
            (lotResults.first()["lotId"] as Number?)?.toLong()
        } else null
        
        val lotNo = if (lotResults.isNotEmpty()) {
            lotResults.first()["lotNo"] as String?
        } else null
        
        val storeLocation = if (lotResults.isNotEmpty()) {
            lotResults.first()["storeLocation"] as String?
        } else null

        val joPickOrderEntity = joPickOrder.get()
        joPickOrderEntity.matchStatus = JoPickOrderStatus.completed
        joPickOrderEntity.matchQty = request.qty.toInt()

        if (joPickOrderEntity.ticketCompleteTime == null) {
            joPickOrderEntity.ticketCompleteTime = LocalDateTime.now()
        }

        joPickOrderRepository.save(joPickOrderEntity)
        
        //  生成 issueNo
        val issueNo = generateIssueNoForJo()
        
        //  Create pick execution issue with complete data
        //  Create pick execution issue with complete data
        val pickExecutionIssue = PickExecutionIssue(
            id = null,  //  1
            pickOrderId = request.pickOrderId,  //  2
            pickOrderCode = pickOrderCode ?: "PI-${request.pickOrderId}",  //  3
            pickOrderCreateDate = pickOrderCreateDate,  //  4
            pickExecutionDate = LocalDate.now(),  //  5
            pickOrderLineId = pickOrderLineId,  //  6
            issueNo = issueNo,  //  7
            issueCategory = IssueCategory.match_issue,  //  8 - 因为这是 JO 的 match 问题
            itemId = request.itemId,  //  9
            itemCode = itemCode,  //  10
            itemDescription = itemName,  //  11
            lotId = lotId,  //  12
            lotNo = lotNo,  //  13
            storeLocation = storeLocation,  //  14
            requiredQty = requiredQty,  //  15
            actualPickQty = request.qty.toBigDecimal(),  //  16
            missQty = request.missQty.toBigDecimal(),  //  17
            badItemQty = request.badItemQty.toBigDecimal(),  //  18
            issueRemark = request.reason,  //  19
            pickerName = pickerName,  //  20
            handleStatus = HandleStatus.pending,  //  21
            handleDate = null,  //  22
            handledBy = null,  //  23
            created = LocalDateTime.now(),  //  24
            createdBy = request.createdBy.toString(),  //  25
            version = 0,  //  26
            modified = LocalDateTime.now(),  //  27
            modifiedBy = null,  //  28
            deleted = false  //  29
        )
        pickExecutionIssueRepository.save(pickExecutionIssue)
        
        return MessageResponse(
            id = null,
            name = null,
            code = "SUCCESS",
            type = "CREATED",
            message = "Second scan issue recorded successfully",
            errorPosition = null
        )
    } catch (e: Exception) {
        println("=== ERROR in recordSecondScanIssue ===")
        e.printStackTrace()
        return MessageResponse(
            id = null,
            name = null,
            code = "ERROR",
            type = "EXCEPTION",
            message = "Error: ${e.message}",
            errorPosition = null
        )
    }
}
private fun generateIssueNoForJo(): String {
    val now = LocalDateTime.now()
    val yearMonth = now.format(java.time.format.DateTimeFormatter.ofPattern("yyMM"))
    
    // 查询当月最新的 issueNo
    val latestIssueNo = pickExecutionIssueRepository.findLatestIssueNoByYearMonth(yearMonth)
    
    // 计算下一个序列号
    val nextSequence = if (latestIssueNo != null) {
        val parts = latestIssueNo.split("-")
        if (parts.size == 3) {
            val currentSequence = parts[2].toIntOrNull() ?: 0
            currentSequence + 1
        } else {
            1
        }
    } else {
        1
    }
    
    return "SKO-${yearMonth}-${nextSequence.toString().padStart(3, '0')}"
}
open fun getCompletedJobOrderPickOrdersWithCompletedSecondScan(): List<Map<String, Any?>> {
    println("=== getCompletedJobOrderPickOrdersWithCompletedSecondScan ===")

    return try {
        // 1. 先找出所有 COMPLETED 的 JO pick order（不再依赖 assignTo / userId）
        val completedPickOrders = pickOrderRepository
            .findAllByStatusIn(listOf(PickOrderStatus.COMPLETED))
            .filter { it.jobOrder != null }  // 只要有 jobOrder 的

        println("Found ${completedPickOrders.size} completed job order pick orders (all users)")

        // 2. 只保留「所有 item 的 second scan 都完成」的 pick order
        val completedJobOrderPickOrders = completedPickOrders.mapNotNull { pickOrder ->
            val jobOrder = pickOrder.jobOrder
            if (jobOrder != null) {
                val joPickOrders = findByPickOrderId(pickOrder.id!!)
                val allSecondScanCompleted = joPickOrders.isNotEmpty() &&
                        joPickOrders.all { it.matchStatus == JoPickOrderStatus.completed }

                if (allSecondScanCompleted) {
                    mapOf(
                        "id" to pickOrder.id,
                        "pickOrderId" to pickOrder.id,
                        "pickOrderCode" to pickOrder.code,
                        "pickOrderConsoCode" to pickOrder.consoCode,
                        "pickOrderTargetDate" to pickOrder.targetDate,
                        "pickOrderStatus" to pickOrder.status,
                        // ✅ 完成时间改用 jobOrder.planEnd
                        "completedDate" to jobOrder.planEnd,
                        "jobOrderId" to jobOrder.id,
                        "jobOrderCode" to jobOrder.code,
                        "jobOrderName" to jobOrder.bom?.name,
                        "reqQty" to jobOrder.reqQty,
                        "uom" to jobOrder.bom?.uom?.code,
                        "planStart" to jobOrder.planStart,
                        "planEnd" to jobOrder.planEnd,
                        "secondScanCompleted" to true,
                        "totalItems" to joPickOrders.size,
                        "completedItems" to joPickOrders.count { it.matchStatus == JoPickOrderStatus.completed }
                    )
                } else {
                    println("❌ Pick order ${pickOrder.id} has incomplete second scan, skipping.")
                    null
                }
            } else {
                println("❌ Pick order ${pickOrder.id} has no job order, skipping.")
                null
            }
        }

        println("Returning ${completedJobOrderPickOrders.size} completed job order pick orders with completed second scan")
        completedJobOrderPickOrders
    } catch (e: Exception) {
        println("❌ Error in getCompletedJobOrderPickOrdersWithCompletedSecondScan: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}
open fun getJobOrderListForPrintQrCode(date: LocalDate): List<JobOrderListForPrintQrCodeResponse> {
    println("=== getJobOrderListForPrintQrCode ===")
    println("Filtering by receiptDate: $date")
    return try {
        // 1. 根据 receiptDate 日期过滤 StockInLine（在数据库层面过滤）
        val allStockInLines = stockInLineRepository.findByReceiptDateAndDeletedFalse(date)
        println("📦 Found ${allStockInLines.size} StockInLines with receiptDate = $date")
        println("📦 All StockInLines: ${allStockInLines.map { it.id }}, ${allStockInLines.map { it.jobOrder?.id }}")
        println("📦 All StockInLines: ${allStockInLines.map { it.status }}, ${allStockInLines.map { it.jobOrder?.bom?.description }}")
        
        // 2. 进一步过滤：status = "received" 或 "completed"，且 BOM type = "WIP" 或 "FG"
        val stockInLines = allStockInLines.filter {
            it.jobOrder != null
            //it.status == "received" &&  // 只显示 QC 完成但尚未 putaway 的
          //  it.jobOrder?.bom?.description in listOf("WIP", "FG")
        }
        println("✅ After filtering: ${stockInLines.size} StockInLines")

        // 3. 获取相关的 JobOrder IDs
        val jobOrderIds = stockInLines.mapNotNull { it.jobOrder?.id }.distinct()
        println("📋 JobOrder IDs to check: $jobOrderIds")

        // 4. 批量加载 JobOrders（只加载需要的，且状态为 COMPLETED）
        val jobOrders = if (jobOrderIds.isNotEmpty()) {
            jobOrderRepository.findAllById(jobOrderIds)
                .filter { it.status == JobOrderStatus.COMPLETED || it.status == JobOrderStatus.STORING }
        } else {
            emptyList()
        }
        println("✅ Completed JobOrders: ${jobOrders.map { "${it.id}(${it.status})" }}")

        // 5. 构建响应
        val result = mutableListOf<JobOrderListForPrintQrCodeResponse>()

        for (stockInLine in stockInLines) {
            val jobOrder = jobOrders.find { it.id == stockInLine.jobOrder?.id }
            if (jobOrder != null && jobOrder.bom?.description in listOf("WIP", "FG")) {
                // Check if this job order is already in the result (to avoid duplicates)
                val existing = result.find {
                    it.stockInLineId == stockInLine.id
                }

                if (existing == null) {
                    val response = JobOrderListForPrintQrCodeResponse(
                        id = stockInLine.id!!,
                        code = jobOrder.code ?: "JO-${jobOrder.id}",
                        name = jobOrder.bom?.name ?: "",
                        reqQty = jobOrder.reqQty ?: BigDecimal.ZERO,
                        stockInLineId = stockInLine.id!!,
                        stockInLineQty = stockInLine.acceptedQty?.toDouble() ?: 0.0,
                        stockInLineStatus = stockInLine.status ?: "received",
                        finihedTime = jobOrder.planEnd ?: jobOrder.modified ?: LocalDateTime.now()
                    )
                    result.add(response)
                    println("✅ Added StockInLine ${stockInLine.id} (JobOrder ${jobOrder.id}, BOM: ${jobOrder.bom?.description})")
                }
            } else {
                println("❌ StockInLine ${stockInLine.id}: JobOrder ${stockInLine.jobOrder?.id} not found or BOM type not WIP/FG")
            }
        }
        
        println("📊 Final result: ${result.size} items")
        result
    } catch (e: Exception) {
        println("❌ Error in getJobOrderListForPrintQrCode: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}
open fun getCompletedJobOrderPickOrderLotDetails(pickOrderId: Long): List<Map<String, Any?>> {
    println("=== getCompletedJobOrderPickOrderLotDetails ===")
    println("pickOrderId: $pickOrderId")
    
    return try {
        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.status as pickOrderStatus,
                
                -- Job Order Information
                jo.id as jobOrderId,
                jo.code as jobOrderCode,
                DATE_FORMAT(jo.planStart, '%Y/%m/%d') as jobOrderPlanStart,
                jo.reqQty as jobOrderReqQty,
                
                -- Finished Goods Information  
                fg.code as fgCode,
                fg.name as fgName,
                
                b.outputQtyUom as unit,
                b.itemId as bomItemId,
                iu.uomId as bomItemUomId,
                
                uc_bom.udfudesc as uomConversionDesc,
                
                -- 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
                w.id as routerId,
                w.order as routerIndex,
                w.code as routerRoute,
                w.code as routerArea,
                
                -- 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,
                
                -- 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,
                
                -- JoPickOrder second scan status
                jpo.match_status as match_status,
                jpo.match_by as match_by,
                jpo.match_qty as match_qty
                
            FROM fpsmsdb.pick_order po
            JOIN fpsmsdb.job_order jo ON jo.id = po.joId
            LEFT JOIN fpsmsdb.bom b ON b.id = jo.bomId          
            LEFT JOIN fpsmsdb.items fg ON fg.id = b.itemId
            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
            LEFT JOIN fpsmsdb.jo_pick_order jpo ON jpo.pick_order_id = po.id AND jpo.item_id = pol.itemId
            LEFT JOIN fpsmsdb.item_uom iu ON iu.itemId = b.itemId AND iu.stockUnit = 1
            LEFT JOIN fpsmsdb.uom_conversion uc_fg ON uc_fg.id = iu.uomId
            LEFT JOIN fpsmsdb.uom_conversion uc_bom ON uc_bom.id = iu.uomId
            WHERE po.deleted = false
            AND po.id = :pickOrderId
            AND pol.deleted = false
            AND po.status = 'COMPLETED'
            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(w.`order`, 999999) ASC, 
                po.code ASC,
                i.code ASC,
                il.expiryDate ASC, 
                il.lotNo ASC
        """.trimIndent()
        
        println("    Executing SQL for Job Order pick order lot details: $sql")
        println("    With parameters: pickOrderId = $pickOrderId")
        
        val results = jdbcDao.queryForList(sql, mapOf("pickOrderId" to pickOrderId))
        
        println("Found ${results.size} lot details for pick order $pickOrderId")
        results
    } catch (e: Exception) {
        println("❌ Error in getCompletedJobOrderPickOrderLotDetails: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}
// ... existing code ...

open fun getCompletedJobOrderPickOrders(): List<Map<String, Any?>> {
    println("=== getCompletedJobOrderPickOrders (no user filter) ===")

    return try {
        // 1. 找出所有 COMPLETED 状态、且有关联 JobOrder 的 pick order（不再按 assignTo 过滤）
        val allCompletedPickOrders = pickOrderRepository
            .findAllByStatusIn(listOf(PickOrderStatus.COMPLETED))
            .filter { it.jobOrder != null }

        println("Found ${allCompletedPickOrders.size} completed job order pick orders")

        // 2. 组装返回数据
        val completedJobOrderPickOrders = allCompletedPickOrders.mapNotNull { pickOrder ->
            val jobOrder = pickOrder.jobOrder
            if (jobOrder != null) {
                // 对应的 JoPickOrder 记录（用于 second scan 状态统计）
                val joPickOrders = findByPickOrderId(pickOrder.id!!)
                println("Pick Order ${pickOrder.code}: joPickOrders.size=${joPickOrders.size}")

                val secondScanCompleted = joPickOrders.isNotEmpty() &&
                        joPickOrders.all { it.matchStatus == JoPickOrderStatus.completed }

                mapOf(
                    "id" to pickOrder.id,
                    "pickOrderId" to pickOrder.id,
                    "pickOrderCode" to pickOrder.code,
                    "pickOrderConsoCode" to pickOrder.consoCode,
                    "pickOrderTargetDate" to pickOrder.targetDate?.let {
                        "${it.year}-${"%02d".format(it.monthValue)}-${"%02d".format(it.dayOfMonth)}"
                    },
                    "pickOrderStatus" to pickOrder.status,
                    // 完成时间：按你的需求用 jobOrder.planEnd
                    "completedDate" to jobOrder.planEnd,
                    "jobOrderId" to jobOrder.id,
                    "jobOrderCode" to jobOrder.code,
                    "jobOrderName" to jobOrder.bom?.name,
                    "reqQty" to jobOrder.reqQty,
                    "uom" to jobOrder.bom?.uom?.code,
                    "planStart" to jobOrder.planStart,
                    "planEnd" to jobOrder.planEnd,
                    "secondScanCompleted" to secondScanCompleted,
                    "totalItems" to joPickOrders.size,
                    "completedItems" to joPickOrders.count { it.matchStatus == JoPickOrderStatus.completed }
                )
            } else {
                println("❌ Pick order ${pickOrder.id} has no job order, skipping.")
                null
            }
        }

        println("Returning ${completedJobOrderPickOrders.size} completed job order pick orders")
        completedJobOrderPickOrders
    } catch (e: Exception) {
        println("❌ Error in getCompletedJobOrderPickOrders: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}

// ... rest of the code ...

open fun getCompletedJobOrderPickOrderLotDetailsForCompletedPick(pickOrderId: Long): List<Map<String, Any?>> {
    println("=== getCompletedJobOrderPickOrderLotDetailsForCompletedPick ===")
    println("pickOrderId: $pickOrderId")
    
    return try {
        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.status as pickOrderStatus,
                
                -- Job Order Information
                jo.id as jobOrderId,
                jo.code as jobOrderCode,
                
                -- 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
                w.id as routerId,
                w.order as routerIndex,
                w.code as routerRoute,
                w.code as routerArea,
                
                -- 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,
                
                -- 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,
                
                -- JoPickOrder second scan status
                jpo.match_status as match_status,
                jpo.match_by as match_by,
                jpo.match_qty as match_qty
                
            FROM fpsmsdb.pick_order po
            JOIN fpsmsdb.job_order jo ON jo.id = po.joId
            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
            LEFT JOIN fpsmsdb.jo_pick_order jpo ON jpo.pick_order_id = po.id AND jpo.item_id = pol.itemId

            WHERE po.deleted = false
            AND po.id = :pickOrderId
            AND pol.deleted = false
            AND po.status = 'COMPLETED'
            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(w.`order`, 999999) ASC, 
                po.code ASC,
                i.code ASC,
                il.expiryDate ASC, 
                il.lotNo ASC
        """.trimIndent()
        
        println("    Executing SQL for completed pick order lot details: $sql")
        println("    With parameters: pickOrderId = $pickOrderId")
        
        val results = jdbcDao.queryForList(sql, mapOf("pickOrderId" to pickOrderId))
        
        println("Found ${results.size} lot details for completed pick order $pickOrderId")
        results
    } catch (e: Exception) {
        println("❌ Error in getCompletedJobOrderPickOrderLotDetailsForCompletedPick: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}


open fun getAllJoPickOrders(): List<AllJoPickOrderResponse> {
    println("=== getAllJoPickOrders ===")
    
    return try {
        val releasedPickOrders = pickOrderRepository.findAllByStatusAndDeletedFalse(
            PickOrderStatus.RELEASED
        ).filter { pickOrder -> 
            pickOrder.jobOrder != null
        }

        println("Found ${releasedPickOrders.size} released job order pick orders")

        val jobOrderPickOrders = releasedPickOrders.mapNotNull { pickOrder ->
            println("Processing pick order: ${pickOrder.id}, code: ${pickOrder.code}")
            val jobOrder = pickOrder.jobOrder
            if (jobOrder == null) {
                println("❌ Pick order ${pickOrder.id} has no job order")
                return@mapNotNull null
            }
            
            println("Job order found: ${jobOrder.id}, code: ${jobOrder.code}")
            
            val bom = jobOrder.bom

            
            println("BOM found: ${bom?.id}")
            
            val item = bom?.item
            if (item == null) {
                println("❌ BOM ${bom?.id} has no item")
                return@mapNotNull null
            }
            
            println("Item found: ${item.id}, name: ${item.name}")
            
            val uom = bom.outputQtyUom
            if (uom == null) {
                println("❌ BOM ${bom.id} has no uom")
                return@mapNotNull null
            }
            
           // println("UOM found: ${uom.id}, code: ${uom.code}")
            
            val pickOrderLines = pickOrderLineRepository.findAllByPickOrderId(pickOrder.id ?: return@mapNotNull null)
            val finishedLines = pickOrderLines.count { it.status == PickOrderLineStatus.COMPLETED }
            val jobOrderType = jobOrder.jobTypeId?.let { jobTypeRepository.findById(it).orElse(null) }

            println("✅ Building response for pick order ${pickOrder.id}")

            AllJoPickOrderResponse(
                id = pickOrder.id ?: 0L,
                pickOrderId = pickOrder.id,
                pickOrderCode = pickOrder.code,
                jobOrderId = jobOrder.id,
                jobOrderCode = jobOrder.code,
                jobOrderTypeId = jobOrder.jobTypeId,
                jobOrderType = jobOrderType?.name,
                itemId = item.id ?: 0L,
                itemName = item.name ?: "",
                reqQty = jobOrder.reqQty ?: BigDecimal.ZERO,
                //uomId = bom.outputQtyUom?.id : 0L,
                uomId = 0,
                uomName = bom?.outputQtyUom?: "",
                jobOrderStatus = jobOrder.status?.value ?: "",
                finishedPickOLineCount = finishedLines
            )
        }

        println("Returning ${jobOrderPickOrders.size} released job order pick orders")
        jobOrderPickOrders
    } catch (e: Exception) {
        println("❌ Error in getAllJoPickOrders: ${e.message}")
        e.printStackTrace()
        emptyList()
    }
}
open fun getJobOrderLotsHierarchicalByPickOrderId(pickOrderId: Long): JobOrderLotsHierarchicalResponse {
    println("=== getJobOrderLotsHierarchicalByPickOrderId ===")
    println("pickOrderId: $pickOrderId")
    
    return try {
        val pickOrder = pickOrderRepository.findById(pickOrderId).orElse(null)
        if (pickOrder == null || pickOrder.deleted == true) {
            println("❌ Pick order $pickOrderId not found or deleted")
            return JobOrderLotsHierarchicalResponse(
                pickOrder = PickOrderInfoResponse(
                    id = null,
                    code = null,
                    consoCode = null,
                    targetDate = null,
                    type = null,
                    status = null,
                    assignTo = null,
                    jobOrder = JobOrderBasicInfoResponse(0, "", "")
                ),
                pickOrderLines = emptyList()
            )
        }
        
        val jobOrder = pickOrder.jobOrder ?: return JobOrderLotsHierarchicalResponse(
            pickOrder = PickOrderInfoResponse(
                id = null,
                code = null,
                consoCode = null,
                targetDate = null,
                type = null,
                status = null,
                assignTo = null,
                jobOrder = JobOrderBasicInfoResponse(0, "", "")
            ),
            pickOrderLines = emptyList()
        )
        
        // ✅ 添加数据获取逻辑（从原始 Map 版本复制）
        // 获取 pick order lines
        val pickOrderLines = pickOrderLineRepository.findAllByPickOrderId(pickOrder.id!!)
            .filter { it.deleted == false }
        
        // 获取所有 pick order line IDs
        val pickOrderLineIds = pickOrderLines.map { it.id!! }
        
        // 获取 suggested pick lots
        val suggestedPickLots = if (pickOrderLineIds.isNotEmpty()) {
            suggestPickLotRepository.findAllByPickOrderLineIdIn(pickOrderLineIds)
                .filter { it.deleted == false }
        } else {
            emptyList()
        }
        
        // 获取所有 inventory lot line IDs
        val inventoryLotLineIds = suggestedPickLots.mapNotNull { it.suggestedLotLine?.id }
        
        // 获取 inventory lot lines
        val inventoryLotLines = if (inventoryLotLineIds.isNotEmpty()) {
            inventoryLotLineRepository.findAllByIdIn(inventoryLotLineIds)
                .filter { it.deleted == false }
        } else {
            emptyList()
        }
        
        // 获取 inventory lots
        val inventoryLotIds = inventoryLotLines.mapNotNull { it.inventoryLot?.id }.distinct()
        val inventoryLots = if (inventoryLotIds.isNotEmpty()) {
            inventoryLotRepository.findAllByIdIn(inventoryLotIds)
                .filter { it.deleted == false }
        } else {
            emptyList()
        }
        
        // 获取 stock out lines
        val stockOutLines = if (pickOrderLineIds.isNotEmpty() && inventoryLotLineIds.isNotEmpty()) {
            pickOrderLineIds.flatMap { polId ->
                inventoryLotLineIds.flatMap { illId ->
                    stockOutLineRepository.findByPickOrderLineIdAndInventoryLotLineIdAndDeletedFalse(polId, illId)
                }
            }
        } else {
            emptyList()
        }
        
        // 取得所有 stock out line（含無 lot 情況）
        val stockOutLinesByPickOrderLine = pickOrderLineIds.associateWith { polId ->
            stockOutLineRepository.findAllByPickOrderLineIdAndDeletedFalse(polId)
        }
        
        // 获取 stock in lines 通过 inventoryLotLineId（用于填充 stockInLineId）
        val stockInLinesByInventoryLotLineId = if (inventoryLotLineIds.isNotEmpty()) {
            inventoryLotLineIds.associateWith { illId ->
                stockInLineRepository.findStockInLineInfoByInventoryLotLineId(illId).orElse(null)?.id
            }.filterValues { it != null }
        } else {
            emptyMap()
        }
        
        // 获取 jo_pick_order 记录
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrder.id!!)
        
        // 构建 pick order info
        val pickOrderInfo = PickOrderInfoResponse(
            id = pickOrder.id,
            code = pickOrder.code,
            consoCode = pickOrder.consoCode,
            targetDate = pickOrder.targetDate?.let { 
                "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
            },
            type = pickOrder.type?.value,
            status = pickOrder.status?.value,
            assignTo = pickOrder.assignTo?.id,
            jobOrder = JobOrderBasicInfoResponse(
                id = jobOrder.id!!,
                code = jobOrder.code ?: "",
                name = "Job Order ${jobOrder.code}"
            )
        )
        
        // 构建 pick order lines with lots
        val pickOrderLinesResult = pickOrderLines.map { pol ->
            val item = pol.item
            val uom = pol.uom
            val lineId = pol.id!!
            val lineSuggestedLots = suggestedPickLots.filter { it.pickOrderLine?.id == pol.id }
            val jpo = joPickOrders.firstOrNull { it.itemId == item?.id }
            val handlerName = jpo?.handledBy?.let { uid ->
                userService.find(uid).orElse(null)?.name
            }
            // 构建 lots 数据
            val lots = lineSuggestedLots.mapNotNull { spl ->
                val ill = spl.suggestedLotLine
                if (ill == null || ill.deleted == true) return@mapNotNull null
                
                val il = ill.inventoryLot
                if (il == null || il.deleted == true) return@mapNotNull null
                
                val warehouse = ill.warehouse
                val sol = stockOutLines.firstOrNull { 
                    it.pickOrderLine?.id == pol.id && it.inventoryLotLine?.id == ill.id 
                }
                val jpo = joPickOrders.firstOrNull { it.itemId == item?.id }
                val handlerName = jpo?.handledBy?.let { uid ->
                    userService.find(uid).orElse(null)?.name
                }
                println("handlerName: $handlerName")
                val availableQty = if (sol?.status == "rejected") {
                    null
                } else {
                    (ill.inQty ?: BigDecimal.ZERO) - (ill.outQty ?: BigDecimal.ZERO) - (ill.holdQty ?: BigDecimal.ZERO)
                }
                
                val lotAvailability = when {
                    il.expiryDate != null && il.expiryDate!!.isBefore(LocalDate.now()) -> "expired"
                    sol?.status == "rejected" -> "rejected"
                    availableQty != null && availableQty <= BigDecimal.ZERO -> "insufficient_stock"
                    ill.status == InventoryLotLineStatus.UNAVAILABLE -> "status_unavailable"
                    else -> "available"
                }
                
                val processingStatus = when (sol?.status) {
                    "completed" -> "completed"
                    "rejected" -> "rejected"
                    "created" -> "pending"
                    else -> "pending"
                }
                
                // 获取 stockInLineId
                val stockInLineId = ill.id?.let { illId ->
                    stockInLinesByInventoryLotLineId[illId]
                }
                
                LotDetailResponse(
                    lotId = ill.id,
                    lotNo = il.lotNo,
                    expiryDate = il.expiryDate?.let { 
                        "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                    },
                    location = warehouse?.code,
                    availableQty = availableQty?.toDouble(),
                    requiredQty = spl.qty?.toDouble() ?: 0.0,
                    actualPickQty = sol?.qty ?: 0.0,
                    processingStatus = processingStatus,
                    lotAvailability = lotAvailability,
                    pickOrderId = pickOrder.id,
                    pickOrderCode = pickOrder.code,
                    pickOrderConsoCode = pickOrder.consoCode,
                    pickOrderLineId = pol.id,
                    stockOutLineId = sol?.id,
                    stockInLineId = stockInLineId,
                    suggestedPickLotId = spl.id,
                    stockOutLineQty = sol?.qty ?: 0.0,
                    stockOutLineStatus = sol?.status,
                    routerIndex = warehouse?.order?.toString(),
                    routerArea = warehouse?.code,
                    routerRoute = warehouse?.code,
                    uomShortDesc = uom?.udfShortDesc,
                    matchStatus = jpo?.matchStatus?.value,
                    matchBy = jpo?.matchBy,
                    matchQty = jpo?.matchQty?.toDouble()
                )
            }
            
            PickOrderLineWithLotsResponse(
                id = pol.id!!,
                itemId = item?.id,
                itemCode = item?.code,
                itemName = item?.name,
                requiredQty = pol.qty?.toDouble(),
                uomCode = uom?.code,
                uomDesc = uom?.udfudesc,
                status = pol.status?.value,
                lots = lots,
                handler=handlerName
            )
        }
        
        return JobOrderLotsHierarchicalResponse(
            pickOrder = pickOrderInfo,
            pickOrderLines = pickOrderLinesResult
        )
        
    } catch (e: Exception) {
        println("❌ Error in getJobOrderLotsHierarchicalByPickOrderId: ${e.message}")
        e.printStackTrace()
        return JobOrderLotsHierarchicalResponse(
            pickOrder = PickOrderInfoResponse(
                id = null,
                code = null,
                consoCode = null,
                targetDate = null,
                type = null,
                status = null,
                assignTo = null,
                jobOrder = JobOrderBasicInfoResponse(0, "", "")
            ),
            pickOrderLines = emptyList()
        )
    }
}

open fun updateHandledByForItem(pickOrderId: Long, itemId: Long, userId: Long): MessageResponse {
    val joPickOrderOpt = joPickOrderRepository.findByPickOrderIdAndItemId(pickOrderId, itemId)
    
    if (joPickOrderOpt.isEmpty) {
        println("⚠️ JoPickOrder not found for pickOrderId: $pickOrderId, itemId: $itemId")
        return MessageResponse(
            id = 0,
            code = "404",
            name = "Not Found",
            type = "error",
            message = "JoPickOrder not found",
            errorPosition = "operator"
        )
    }

    val joPickOrder = joPickOrderOpt.get()
    if (userId != null && joPickOrder.handledBy != null) {
        val existingOperatorId = joPickOrder.handledBy
        val newOperatorId = userId
        
        // 如果不是同一个用户，拒绝更新
        if (existingOperatorId != null && existingOperatorId != newOperatorId) {
            return MessageResponse(
                id = joPickOrder.id,
                code = "409",
                name = "Conflict",
                type = "error",
                message = "This pick order is already assigned to another user",
                errorPosition = "operator"
            )
        }
    }
    joPickOrder.handledBy = userId
    joPickOrderRepository.save(joPickOrder)
    // Don't update other fields - only handledBy
    
    return MessageResponse(
        id = joPickOrder.id,
       code = null,
        name = null,
        type = null,
        message = "Pick order handled by updated",
        errorPosition = null
    )
}

open fun updateRecordHandledByForItem(pickOrderId: Long, itemId: Long, userId: Long): MessageResponse {
    val joPickOrderRecordOpt = joPickOrderRecordRepository.findByPickOrderIdAndItemId(pickOrderId, itemId)
    
    if (joPickOrderRecordOpt.isEmpty) {
        println("⚠️ JoPickOrderRecord not found for pickOrderId: $pickOrderId, itemId: $itemId")
        return MessageResponse(
            id = 0,
            code = "404",
            name = "Not Found",
            type = "error",
            message = "JoPickOrderRecord not found",
            errorPosition = "operator"
        )
    }

    val joPickOrderRecord = joPickOrderRecordOpt.get()
    if (userId != null && joPickOrderRecord.handledBy != null) {
        val existingOperatorId = joPickOrderRecord.handledBy
        val newOperatorId = userId
        
        // 如果不是同一个用户，拒绝更新
        if (existingOperatorId != null && existingOperatorId != newOperatorId) {
            return MessageResponse(
                id = joPickOrderRecord.id,
                code = "409",
                name = "Conflict",
                type = "error",
                message = "This pick order is already assigned to another user",
                errorPosition = "operator"
            )
        }
    }
    joPickOrderRecord.handledBy = userId
    joPickOrderRecordRepository.save(joPickOrderRecord)
    // Don't update other fields - only handledBy
    
    return MessageResponse(
        id = joPickOrderRecord.id,
        code = null,
        name = null,
        type = null,
        message = "Pick order record handled by updated",
        errorPosition = null
    )
}
@Transactional(rollbackFor = [Exception::class])
open fun deleteJoPickOrderJobOrderProductProcessPickOrder(jobOrderId: Long): MessageResponse {
    println("=== deleteJoPickOrderJobOrderProductProcessPickOrder ===")
    println("jobOrderId: $jobOrderId")

    // 1. 只允许删除 PLANNING 的工单
    val jobOrder = jobOrderRepository.findById(jobOrderId)
        .orElseThrow { NoSuchElementException("JobOrder $jobOrderId not found") }

    if (jobOrder.status != JobOrderStatus.PLANNING) {
        return null as MessageResponse;
    }

    // 2. Product Process & Product Process Line
    val productProcesses = productProcessRepository.findByJobOrder_Id(jobOrderId)
    val productProcessIds = productProcesses.mapNotNull { it.id }

    if (productProcessIds.isNotEmpty()) {
        val productProcessLines =
            productProcessLineRepository.findByProductProcess_IdIn(productProcessIds)
        if (productProcessLines.isNotEmpty()) {
            productProcessLineRepository.deleteAll(productProcessLines)
        }
        productProcessRepository.deleteAll(productProcesses)
    }

    // 3. PickOrder / PickOrderLine / JoPickOrder / JoPickOrderRecord
    val pickOrders = pickOrderRepository.findAllByJobOrder_Id(jobOrderId)
    if (pickOrders.isNotEmpty()) {
        val pickOrderIds = pickOrders.mapNotNull { it.id }

        // 3.1 所有 pick order line
        val pickOrderLines = pickOrderIds.flatMap { poId ->
            pickOrderLineRepository.findAllByPickOrderId(poId)
        }
        val pickOrderLineIds = pickOrderLines.mapNotNull { it.id }

        // 3.2 jo_pick_order & jo_pick_order_record
        pickOrderIds.forEach { poId ->
            val joPickOrders = joPickOrderRepository.findByPickOrderId(poId)
            if (joPickOrders.isNotEmpty()) {
                joPickOrderRepository.deleteAll(joPickOrders)
            }
            val joPickOrderRecords = joPickOrderRecordRepository.findByPickOrderId(poId)
            if (joPickOrderRecords.isNotEmpty()) {
                joPickOrderRecordRepository.deleteAll(joPickOrderRecords)
            }
        }

        // 4. SuggestedPickLot & 释放 inventoryLotLine.holdQty
        if (pickOrderLineIds.isNotEmpty()) {
            val suggestedPickLots =
                suggestPickLotRepository.findAllByPickOrderLineIdIn(pickOrderLineIds)
            if (suggestedPickLots.isNotEmpty()) {
                val lotLineIds = suggestedPickLots.mapNotNull { it.suggestedLotLine?.id }.distinct()
                if (lotLineIds.isNotEmpty()) {
                    val lotLines = inventoryLotLineRepository.findAllByIdIn(lotLineIds)
                    // 把当初 hold 住的 qty 减回去（releaseJobOrder 里是直接 + qty，这里反向 - qty）
                    lotLines.forEach { ill ->
                        val totalQtyToRelease = suggestedPickLots
                            .filter { it.suggestedLotLine?.id == ill.id }
                            .mapNotNull { it.qty }
                            .fold(BigDecimal.ZERO) { acc, q -> acc + q }
                        if (totalQtyToRelease > BigDecimal.ZERO) {
                            val current = ill.holdQty ?: BigDecimal.ZERO
                            ill.holdQty = current - totalQtyToRelease
                        }
                    }
                    inventoryLotLineRepository.saveAll(lotLines)
                }
                suggestPickLotRepository.deleteAll(suggestedPickLots)
            }
        }

        // 5. StockOut & StockOutLine（按 consoPickOrderCode 关联）
        pickOrders
            .mapNotNull { it.consoCode }
            .mapNotNull { stockOutRepository.findByConsoPickOrderCode(it).orElse(null) }
            .forEach { stockOut ->
                val lines = stockOutLineRepository.findAllByStockOutId(stockOut.id!!)
                if (lines.isNotEmpty()) {
                    stockOutLineRepository.deleteAll(lines)
                }
                stockOutRepository.delete(stockOut)
            }

        // 6. 最后删 PickOrderLine & PickOrder
        if (pickOrderLines.isNotEmpty()) {
            pickOrderLineRepository.deleteAll(pickOrderLines)
        }
        pickOrderRepository.deleteAll(pickOrders)
    }
    val jobOrderProcesses = jobOrderProcessRepository.findAllByJo_Id(jobOrderId)
    if (jobOrderProcesses.isNotEmpty()) {
        jobOrderProcessRepository.deleteAll(jobOrderProcesses)
    }
    // 7. 最后删除 JobOrder 本身
    jobOrderRepository.delete(jobOrder)

    return MessageResponse(
        id = jobOrder.id,
        code = jobOrder.code,
        name = jobOrder.bom?.name,
        type = null,
        message = "Job Order deleted",
        errorPosition = null
    )
}
open fun getMaterialPickStatus(): List<MaterialPickStatusItem> {
    val joPickOrders = joPickOrderRepository.findAll()
    
    return joPickOrders
        .filter { joPickOrder ->
            val jobOrder = joPickOrder.jobOrderId?.let { 
                jobOrderRepository.findById(it).orElse(null) 
            }
            jobOrder?.status != JobOrderStatus.COMPLETED
        }
        .map { joPickOrder ->
        // Get related entities for additional data
        val jobOrder = joPickOrder.jobOrderId?.let { 
            jobOrderRepository.findById(it).orElse(null) 
        }
        val item = joPickOrder.itemId?.let { 
            itemsRepository.findById(it).orElse(null) 
        }
        val pickOrder = joPickOrder.pickOrderId?.let {
            pickOrderRepository.findById(it).orElse(null)
        }
        
        // Get stock out lines for this pick order and item to get start/end times
        // First get all pick order lines for this pick order
        val pickOrderLines = pickOrder?.let { po ->
            pickOrderLineRepository.findByPickOrderId(po.id!!)
        } ?: emptyList()
        
        // Then get stock out lines for each pick order line that matches the item
        val stockOutLines = pickOrderLines
            .filter { it.item?.id == joPickOrder.itemId }
            .flatMap { pol ->
                stockOutLineRepository.findAllByPickOrderLineIdAndDeletedFalse(pol.id!!)
                    .mapNotNull { solInfo ->
                        // Convert StockOutLineInfo to StockOutLine entity to access startTime/endTime
                        // We need to fetch the actual entity
                        stockOutLineRepository.findById(solInfo.id).orElse(null)
                    }
            }
        
        // Get earliest startTime and latest endTime from stock out lines
        val pickStartTime = stockOutLines
            .mapNotNull { it.startTime }
            .minOrNull()
        
        val pickEndTime = stockOutLines
            .mapNotNull { it.endTime }
            .maxOrNull()
        
        // Count items to pick from pick order lines
        val numberOfItemsToPick = pickOrder?.let { po ->
            pickOrderLineRepository.findByPickOrderId(po.id!!).size
        } ?: 0
        
        // Count items with issues from pick execution issues
        val numberOfItemsWithIssue = joPickOrder.id?.let { joPickOrderId ->
            pickExecutionIssueRepository.findByPickOrderIdAndDeletedFalse(joPickOrder.pickOrderId ?: 0L)
                .filter { it.joPickOrderId == joPickOrderId }
                .size
        } ?: 0
        
        // Get job order quantity and UOM - access via bom
        val jobOrderQty = jobOrder?.reqQty?.toDouble()
        val uom = jobOrder?.bom?.uom?.code
        
        // Get item name
        val itemName = item?.name
        
        // Determine pick status - prioritize pickOrder.status over matchStatus
        val pickStatus = when {
            pickOrder?.status != null -> pickOrder.status!!.value
            joPickOrder.matchStatus != null -> joPickOrder.matchStatus!!.value
            else -> null
        }
        
        MaterialPickStatusItem(
            id = joPickOrder.id ?: 0L,
            pickOrderId = joPickOrder.pickOrderId,
            pickOrderCode = joPickOrder.pickOrderCode,
            jobOrderId = joPickOrder.jobOrderId,
            jobOrderCode = joPickOrder.jobOrderCode,
            itemId = joPickOrder.itemId,
            itemCode = joPickOrder.itemCode,
            itemName = itemName,
            jobOrderQty = jobOrderQty,
            uom = uom,
            pickStartTime = pickStartTime,
            pickEndTime = pickEndTime,
            numberOfItemsToPick = numberOfItemsToPick,
            numberOfItemsWithIssue = numberOfItemsWithIssue,
            pickStatus = pickStatus
        )
    }
}
}