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 java.time.LocalDate

import java.time.LocalDateTime
import com.ffii.core.support.JdbcDao
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
@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
) {
    
    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): List<JoPickOrder> {
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrderId)
        joPickOrders.forEach { 
            it.handledBy = userId
            it.second_qr_scan_status = JoPickOrderStatus.pending // Set initial status
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    
    // ✅ Complete JoPickOrder
    open fun completeJoPickOrdersForPickOrder(pickOrderId: Long): List<JoPickOrder> {
        val joPickOrders = joPickOrderRepository.findByPickOrderId(pickOrderId)
        joPickOrders.forEach { 
            it.second_qr_scan_status = JoPickOrderStatus.completed
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    
    // ✅ Update JoPickOrderRecord status to released
    open fun updateRecordHandledByForPickOrder(pickOrderId: Long, userId: Long): List<JoPickOrderRecord> {
        val joPickOrderRecords = joPickOrderRecordRepository.findByPickOrderId(pickOrderId)
        joPickOrderRecords.forEach { 
            it.handledBy = userId
            it.second_qr_scan_status = JoPickOrderStatus.pending // Set initial status
        }
        return joPickOrderRecordRepository.saveAll(joPickOrderRecords)
    }
    
    // ✅ Complete JoPickOrderRecord
    open fun completeJoPickOrderRecordsForPickOrder(pickOrderId: Long): List<JoPickOrderRecord> {
        val joPickOrderRecords = joPickOrderRecordRepository.findByPickOrderId(pickOrderId)
        joPickOrderRecords.forEach { 
            it.second_qr_scan_status = JoPickOrderStatus.completed
        }
        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> {
        val joPickOrders = itemIds.map { itemId ->
            JoPickOrder(
                pickOrderId = pickOrderId,
                itemId = itemId,
                second_qr_scan_status = JoPickOrderStatus.pending,
                handledBy = null
            )
        }
        return joPickOrderRepository.saveAll(joPickOrders)
    }
    
    // ✅ Create JoPickOrderRecord records for a pick order
    @Transactional
    open fun createJoPickOrderRecordsForPickOrder(pickOrderId: Long, itemIds: List<Long>): List<JoPickOrderRecord> {
        val joPickOrderRecords = itemIds.map { itemId ->
            JoPickOrderRecord(
                pickOrderId = pickOrderId,
                itemId = itemId,
                second_qr_scan_status = JoPickOrderStatus.pending,
                handledBy = null
            )
        }
        return joPickOrderRecordRepository.saveAll(joPickOrderRecords)
    }

    open fun getAllJobOrderLotsWithDetailsHierarchical(userId: Long): Map<String, Any?> {
        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")
            return emptyMap()
        }
        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")
        
        val visiblePickOrders = allAssignedPickOrders.filter { pickOrder ->
            val joPickOrders = findByPickOrderId(pickOrder.id!!)
            joPickOrders.none { it.hide } // Only show hide = false orders
        }
        
        // Filter based on assignment and status
        val filteredPickOrders = if (visiblePickOrders.isNotEmpty()) {
            // Check if there are any RELEASED orders assigned to this user (active work)
            val assignedReleasedOrders = allAssignedPickOrders.filter { 
                it.status == PickOrderStatus.RELEASED && it.assignTo?.id == userId
            }
            
            if (assignedReleasedOrders.isNotEmpty()) {
                // If there are assigned RELEASED orders, show only those
                println("🔍 DEBUG: Found ${assignedReleasedOrders.size} assigned RELEASED 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 mapOf(
                "pickOrder" to null as Any?,
                "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
            )
        }
        
        // ✅ 修复：简化 SQL 查询，移除不存在的字段
        val pickOrderIdsStr = pickOrderIds.joinToString(",")
        
        val sql = """
            SELECT 
                -- Pick Order Information
                po.id as pickOrderId,
                po.code as pickOrderCode,
                po.consoCode as pickOrderConsoCode,
                DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
                po.type as pickOrderType,
                po.status as pickOrderStatus,
                po.assignTo as pickOrderAssignTo,
                
                -- 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
                r.id as routerId,
                r.index as routerIndex,
                r.route as routerRoute,
                r.area 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,
                COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
                ill.inventoryLotId as debugInventoryLotId,
                
                -- Calculate total picked quantity by ALL pick orders for this lot
                COALESCE((
                    SELECT SUM(sol_all.qty) 
                    FROM fpsmsdb.stock_out_line sol_all 
                    WHERE sol_all.inventoryLotLineId = ill.id 
                    AND sol_all.deleted = false
                    AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
                ), 0) as totalPickedByAllPickOrders,
                
                -- Calculate remaining available quantity correctly
                (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
                
                -- Lot availability status
                CASE 
                    WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                    WHEN sol.status = 'rejected' THEN 'rejected'
                    WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                    WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                    ELSE 'available'
                END as lotAvailability,
                
                -- Processing status
                CASE 
                    WHEN sol.status = 'completed' THEN 'completed'
                    WHEN sol.status = 'rejected' THEN 'rejected'
                    WHEN sol.status = 'created' THEN 'pending'
                    ELSE 'pending'
                END as processingStatus,
                
                -- JoPickOrder second scan status
                jpo.second_qr_scan_status as secondQrScanStatus,
                jpo.second_qr_scan_by as secondQrScanBy,
                jpo.second_qr_scan_qty as secondQrScanQty
                
            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.router r ON r.inventoryLotId = ill.id AND r.deleted = false
            LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
            LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
            LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
            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 IN ($pickOrderIdsStr)
            AND pol.deleted = false
            AND po.status IN ('PENDING', 'RELEASED', 'COMPLETED')
            AND po.assignTo = :userId
            AND ill.deleted = false
            AND il.deleted = false
            AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
            ORDER BY 
                CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END,
                COALESCE(r.index, 0) ASC, 
                po.code ASC,
                i.code ASC,
                il.expiryDate ASC, 
                il.lotNo ASC
        """.trimIndent()
        
        println("🔍 Executing SQL for job order hierarchical structure: $sql")
        println("🔍 With parameters: userId = $userId, pickOrderIds = $pickOrderIdsStr")
        
        // ✅ 修复：直接执行 SQL 查询并构建分层结构
        try {
            val results = jdbcDao.queryForList(sql, mapOf("userId" to userId))
            
            if (results.isEmpty()) {
                return mapOf(
                    "pickOrder" to null as Any?,
                    "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
                )
            }
            
            // 构建分层结构
            val pickOrderInfo = results.firstOrNull()?.let { firstRow: Map<String, Any?> ->
                mapOf(
                    "id" to firstRow["pickOrderId"],
                    "code" to firstRow["pickOrderCode"],
                    "consoCode" to firstRow["pickOrderConsoCode"],
                    "targetDate" to firstRow["pickOrderTargetDate"],
                    "type" to firstRow["pickOrderType"],
                    "status" to firstRow["pickOrderStatus"],
                    "assignTo" to firstRow["pickOrderAssignTo"],
                    "jobOrder" to mapOf(
                        "id" to firstRow["jobOrderId"],
                        "code" to firstRow["jobOrderCode"],
                        "name" to "Job Order ${firstRow["jobOrderCode"]}" // ✅ 使用生成的名称
                    )
                )
            }
            
            // 按 pick order line 分组
            val groupedByLine = results.groupBy { row: Map<String, Any?> -> row["pickOrderLineId"] }
            val pickOrderLines = groupedByLine.map { (lineId: Any?, lineRows: List<Map<String, Any?>>) ->
                val firstLineRow = lineRows.first()
                mapOf(
                    "id" to lineId,
                    "itemId" to firstLineRow["itemId"],
                    "itemCode" to firstLineRow["itemCode"],
                    "itemName" to firstLineRow["itemName"],
                    "requiredQty" to firstLineRow["pickOrderLineRequiredQty"],
                    "uomCode" to firstLineRow["uomCode"],
                    "uomDesc" to firstLineRow["uomDesc"],
                    "lots" to lineRows.map { row: Map<String, Any?> ->
                        mapOf(
                            "lotId" to row["lotId"],
                            "lotNo" to row["lotNo"],
                            "expiryDate" to row["expiryDate"],
                            "location" to row["location"],
                            "availableQty" to row["availableQty"],
                            "requiredQty" to row["requiredQty"],
                            "actualPickQty" to row["actualPickQty"],
                            "processingStatus" to row["processingStatus"],
                            "lotAvailability" to row["lotAvailability"],
                            "pickOrderId" to row["pickOrderId"],
                            "pickOrderCode" to row["pickOrderCode"],
                            "pickOrderConsoCode" to row["pickOrderConsoCode"],
                            "pickOrderLineId" to row["pickOrderLineId"],
                            "stockOutLineId" to row["stockOutLineId"],
                            "stockOutLineStatus" to row["stockOutLineStatus"],
                            "routerIndex" to row["routerIndex"],
                            "routerArea" to row["routerArea"],
                            "routerRoute" to row["routerRoute"],
                            "uomShortDesc" to row["uomShortDesc"],
                            "secondQrScanStatus" to row["secondQrScanStatus"],
                            "secondQrScanBy" to row["secondQrScanBy"],
                            "secondQrScanQty" to row["secondQrScanQty"]
                        )
                    }
                )
            }
            
            return mapOf(
                "pickOrder" to pickOrderInfo as Any?,
                "pickOrderLines" to pickOrderLines as Any?
            )
            
        } catch (e: Exception) {
            println("❌ Error executing Job Order SQL: ${e.message}")
            e.printStackTrace()
            return mapOf(
                "pickOrder" to null as Any?,
                "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
            )
        }
    }

    // Get completed job order pick orders (for second tab)
    // ✅ Fix the getCompletedJobOrderLotsHierarchical method
open fun getCompletedJobOrderLotsHierarchical(userId: Long): Map<String, Any?> {
    println("=== Debug: getCompletedJobOrderLotsHierarchical ===")
    println("today: ${LocalDate.now()}")
    println("userId filter: $userId")
    
    // Get all COMPLETED pick orders assigned to the user with joId
    val user = userService.find(userId).orElse(null)
    if (user == null) {
        println("❌ User not found: $userId")
        return emptyMap()
    }
    
    // Get all COMPLETED pick orders assigned to user that have joId
            val completedPickOrders = pickOrderRepository.findAllByAssignToIdAndStatusIn(
                userId,
                listOf(PickOrderStatus.COMPLETED)
    ).filter { it.jobOrder != null } // Only pick orders with joId

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

    val visiblePickOrders = completedPickOrders.filter { pickOrder ->
                    val joPickOrders = findByPickOrderId(pickOrder.id!!)
        joPickOrders.none { it.hide } // Only show hide = false orders
    }
    
    if (visiblePickOrders.isEmpty()) {
        println("🔍 DEBUG: No visible completed job orders found")
        return mapOf(
            "pickOrder" to null as Any?,
            "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
        )
    }
    
    // For completed orders, show the latest one
    val latestCompletedOrder = visiblePickOrders.maxByOrNull { 
        it.completeDate ?: it.modified ?: LocalDateTime.MIN 
    }
    
    if (latestCompletedOrder == null) {
        println("🔍 DEBUG: No latest completed order found")
        return mapOf(
            "pickOrder" to null as Any?,
            "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
        )
    }
    
    println("🔍 DEBUG: Using latest completed order: ${latestCompletedOrder.code}")
    
    // ✅ Use the same SQL query approach as getAllJobOrderLotsWithDetailsHierarchical
    val pickOrderIds = listOf(latestCompletedOrder.id!!)
    val pickOrderIdsStr = pickOrderIds.joinToString(",")
    
    val sql = """
        SELECT 
            -- Pick Order Information
            po.id as pickOrderId,
            po.code as pickOrderCode,
            po.consoCode as pickOrderConsoCode,
            DATE_FORMAT(po.targetDate, '%Y-%m-%d') as pickOrderTargetDate,
            'jo' as pickOrderType,
            po.status as pickOrderStatus,
            po.assignTo as pickOrderAssignTo,
            
            -- 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
            r.id as routerId,
            r.index as routerIndex,
            r.route as routerRoute,
            r.area 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,
            COALESCE(spl.suggestedLotLineId, ill.id) as debugSuggestedLotLineId,
            ill.inventoryLotId as debugInventoryLotId,
            
            -- Calculate total picked quantity by ALL pick orders for this lot
            COALESCE((
                SELECT SUM(sol_all.qty) 
                FROM fpsmsdb.stock_out_line sol_all 
                WHERE sol_all.inventoryLotLineId = ill.id 
                AND sol_all.deleted = false
                AND sol_all.status IN ('pending', 'checked', 'partially_completed', 'completed')
            ), 0) as totalPickedByAllPickOrders,
            
            -- Calculate remaining available quantity correctly
            (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) as remainingAfterAllPickOrders,
            
            -- Lot availability status
            CASE 
                WHEN (il.expiryDate IS NOT NULL AND il.expiryDate < CURDATE()) THEN 'expired'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN (COALESCE(ill.inQty, 0) - COALESCE(ill.outQty, 0) - COALESCE(ill.holdQty, 0)) <= 0 THEN 'insufficient_stock'
                WHEN ill.status = 'unavailable' THEN 'status_unavailable'
                ELSE 'available'
            END as lotAvailability,
            
            -- Processing status
            CASE 
                WHEN sol.status = 'completed' THEN 'completed'
                WHEN sol.status = 'rejected' THEN 'rejected'
                WHEN sol.status = 'created' THEN 'pending'
                ELSE 'pending'
            END as processingStatus,
            
            -- JoPickOrder second scan status
            jpo.second_qr_scan_status as secondQrScanStatus,
            jpo.second_qr_scan_by as secondQrScanBy,
            jpo.second_qr_scan_qty as secondQrScanQty
            
        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.router r ON r.inventoryLotId = ill.id AND r.deleted = false
        LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
        LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
        LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
        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 IN ($pickOrderIdsStr)
        AND pol.deleted = false
        AND po.status = 'COMPLETED'
        AND po.assignTo = :userId
        AND ill.deleted = false
        AND il.deleted = false
        AND (spl.pickOrderLineId IS NOT NULL OR sol.pickOrderLineId IS NOT NULL)
        AND jpo.second_qr_scan_status = 'pending' OR jpo.second_qr_scan_status = 'scanned'
        ORDER BY 
            CASE WHEN sol.status = 'rejected' THEN 0 ELSE 1 END,
            COALESCE(r.index, 0) ASC, 
            po.code ASC,
            i.code ASC,
            il.expiryDate ASC, 
            il.lotNo ASC
    """.trimIndent()
    
    println("🔍 Executing SQL for completed job order hierarchical structure: $sql")
    println("🔍 With parameters: userId = $userId, pickOrderIds = $pickOrderIdsStr")
    
    // ✅ Execute SQL query and build hierarchical structure
    try {
        val results = jdbcDao.queryForList(sql, mapOf("userId" to userId))
        
        if (results.isEmpty()) {
            return mapOf(
                "pickOrder" to null as Any?,
                "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
            )
        }
        
        // Build hierarchical structure
        val pickOrderInfo = results.firstOrNull()?.let { firstRow: Map<String, Any?> ->
                        mapOf(
                "id" to firstRow["pickOrderId"],
                "code" to firstRow["pickOrderCode"],
                "consoCode" to firstRow["pickOrderConsoCode"],
                "targetDate" to firstRow["pickOrderTargetDate"],
                "type" to firstRow["pickOrderType"],
                "status" to firstRow["pickOrderStatus"],
                "assignTo" to firstRow["pickOrderAssignTo"],
                "jobOrder" to mapOf(
                    "id" to firstRow["jobOrderId"],
                    "code" to firstRow["jobOrderCode"],
                    "name" to "Job Order ${firstRow["jobOrderCode"]}"
                )
            )
        }
        
        // Group by pick order line
        val groupedByLine = results.groupBy { row: Map<String, Any?> -> row["pickOrderLineId"] }
        val pickOrderLines = groupedByLine.map { (lineId: Any?, lineRows: List<Map<String, Any?>>) ->
            val firstLineRow = lineRows.first()
            mapOf(
                "id" to lineId,
                "itemId" to firstLineRow["itemId"],
                "itemCode" to firstLineRow["itemCode"],
                "itemName" to firstLineRow["itemName"],
                "requiredQty" to firstLineRow["pickOrderLineRequiredQty"],
                "uomCode" to firstLineRow["uomCode"],
                "uomDesc" to firstLineRow["uomDesc"],
                "lots" to lineRows.map { row: Map<String, Any?> ->
                    mapOf(
                        "lotId" to row["lotId"],
                        "lotNo" to row["lotNo"],
                        "expiryDate" to row["expiryDate"],
                        "location" to row["location"],
                        "availableQty" to row["availableQty"],
                        "requiredQty" to row["requiredQty"],
                        "actualPickQty" to row["actualPickQty"],
                        "processingStatus" to row["processingStatus"],
                        "lotAvailability" to row["lotAvailability"],
                        "pickOrderId" to row["pickOrderId"],
                        "pickOrderCode" to row["pickOrderCode"],
                        "pickOrderConsoCode" to row["pickOrderConsoCode"],
                        "pickOrderLineId" to row["pickOrderLineId"],
                        "stockOutLineId" to row["stockOutLineId"],
                        "stockOutLineStatus" to row["stockOutLineStatus"],
                        "routerIndex" to row["routerIndex"],
                        "routerArea" to row["routerArea"],
                        "routerRoute" to row["routerRoute"],
                        "uomShortDesc" to row["uomShortDesc"],
                        "secondQrScanStatus" to row["secondQrScanStatus"]
                    )
                }
            )
        }
        
        return mapOf(
            "pickOrder" to pickOrderInfo as Any?,
            "pickOrderLines" to pickOrderLines as Any?
        )
        
        } catch (e: Exception) {
        println("❌ Error executing completed Job Order SQL: ${e.message}")
            e.printStackTrace()
        return mapOf(
            "pickOrder" to null as Any?,
            "pickOrderLines" to emptyList<Map<String, Any>>() as Any?
        )
        }
    }

    // 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.second_qr_scan_status == 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,
                        "secondQrScanStatus" to record.second_qr_scan_status,
                        "secondQrScanBy" to record.second_qr_scan_by,
                        "secondQrScanQty" to record.second_qr_scan_qty,
                        "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)
        
        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
        )
    }
    }
    // ✅ Fix the updateSecondQrScanStatus method
open fun updateSecondQrScanStatus(pickOrderId: Long, itemId: Long): MessageResponse {
    try {
        println("=== Debug: updateSecondQrScanStatus ===")
        println("pickOrderId: $pickOrderId, itemId: $itemId")
        
        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.second_qr_scan_status = JoPickOrderStatus.scanned // ✅ Use enum instead of string
        joPickOrderRepository.save(joPickOrderEntity)
        
        return MessageResponse(
            id = null,
            name = null,
            code = "SUCCESS",
            type = "UPDATED",
            message = "Second QR scan status updated to checked",
            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.second_qr_scan_qty = request.qty.toInt()
        
        // ✅ Always set status to completed when submitting quantity
        joPickOrderEntity.second_qr_scan_status = JoPickOrderStatus.completed
        
        joPickOrderRepository.save(joPickOrderEntity)
        
        println("✅ Updated jo_pick_order: status=${joPickOrderEntity.second_qr_scan_status}, qty=${joPickOrderEntity.second_qr_scan_qty}")
        
        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 line details with item information
        val pickOrderLineResults = jdbcDao.queryForList(
            """
            SELECT 
                pol.id as pickOrderLineId,
                i.code as itemCode,
                i.name as itemName,
                pol.qty as requiredQty
            FROM fpsmsdb.pick_order_line pol
            JOIN fpsmsdb.items i ON i.id = pol.itemId
            WHERE pol.poId = :pickOrderId AND pol.itemId = :itemId AND pol.deleted = false
            """,
            mapOf(
                "pickOrderId" to request.pickOrderId,
                "itemId" to request.itemId
            )
        )
        
        if (pickOrderLineResults.isEmpty()) {
            return MessageResponse(
                id = null,
                name = null,
                code = "ERROR",
                type = "NOT_FOUND",
                message = "Pick order line not found for pickOrderId: ${request.pickOrderId}, itemId: ${request.itemId}",
                errorPosition = null
            )
        }
        
        val lineData = pickOrderLineResults.first()
        val pickOrderLineId = (lineData["pickOrderLineId"] as Number).toLong()
        val itemCode = lineData["itemCode"] as String?
        val itemName = lineData["itemName"] as String?
        val requiredQty = lineData["requiredQty"]?.let {
            BigDecimal(it.toString())
        }
        

        val joPickOrderEntity = joPickOrder.get()
        joPickOrderEntity.second_qr_scan_status = JoPickOrderStatus.completed
        joPickOrderEntity.second_qr_scan_qty = request.qty.toInt()
        joPickOrderRepository.save(joPickOrderEntity)
        
        // ✅ Create pick execution issue with complete data
        val pickExecutionIssue = PickExecutionIssue(
            pickOrderId = request.pickOrderId,
            pickOrderCode = "JO-${request.pickOrderId}",
            pickOrderLineId = pickOrderLineId,
            itemId = request.itemId,
            itemCode = itemCode, // ✅ Include item code
            itemDescription = itemName, // ✅ Include item name
            lotId = null,
            lotNo = null,
            storeLocation = null,
            requiredQty = requiredQty, // ✅ Include required quantity
            actualPickQty = request.qty.toBigDecimal(), // ✅ Include actual pick quantity
            missQty = if (request.isMissing) request.qty.toBigDecimal() else BigDecimal.ZERO,
            badItemQty = if (request.isBad) request.qty.toBigDecimal() else BigDecimal.ZERO,
            issueRemark = request.reason,
            pickerName = null,
            handleStatus = HandleStatus.jopending,
            handleDate = null,
            handledBy = null,
            created = LocalDateTime.now(),
            createdBy = request.createdBy.toString(),
            version = 0,
            modified = LocalDateTime.now(),
            modifiedBy = null,
            deleted = false
        )
        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 recording second scan issue: ${e.message}")
        return MessageResponse(
            id = null,
            name = null,
            code = "ERROR",
            type = "EXCEPTION",
            message = "Error recording second scan issue: ${e.message}",
            errorPosition = null
        )
    }
}
open fun getCompletedJobOrderPickOrdersWithCompletedSecondScan(userId: Long): List<Map<String, Any?>> {
    println("=== getCompletedJobOrderPickOrdersWithCompletedSecondScan ===")
    println("userId: $userId")
    
    return try {
        // Get all completed pick orders assigned to user that have joId
        val completedPickOrders = pickOrderRepository.findAllByAssignToIdAndStatusIn(
            userId,
            listOf(PickOrderStatus.COMPLETED)
        ).filter { it.jobOrder != null } // Only pick orders with joId

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

        val completedJobOrderPickOrders = completedPickOrders.mapNotNull { pickOrder ->
            val jobOrder = pickOrder.jobOrder
            if (jobOrder != null) {
                // Check if all items in this pick order have completed second scan
                val joPickOrders = findByPickOrderId(pickOrder.id!!)
                val allSecondScanCompleted = joPickOrders.isNotEmpty() && 
                    joPickOrders.all { it.second_qr_scan_status == 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?.let { 
                            "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                        },
                        "pickOrderStatus" to pickOrder.status,
                        "completedDate" to pickOrder.completeDate?.let { 
                            "${it.year}-${String.format("%02d", it.monthValue)}-${String.format("%02d", it.dayOfMonth)}"
                        },
                        "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.second_qr_scan_status == 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 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,
                
                -- Pick Order Line Information
                pol.id as pickOrderLineId,
                pol.qty as pickOrderLineRequiredQty,
                pol.status as pickOrderLineStatus,
                
                -- Item Information
                i.id as itemId,
                i.code as itemCode,
                i.name as itemName,
                uc.code as uomCode,
                uc.udfudesc as uomDesc,
                uc.udfShortDesc as uomShortDesc,
                
                -- Lot Information
                ill.id as lotId,
                il.lotNo,
                DATE_FORMAT(il.expiryDate, '%Y-%m-%d') as expiryDate,
                w.name as location,
                COALESCE(uc.udfudesc, 'N/A') as stockUnit,   
                
                -- Router Information
                r.id as routerId,
                r.index as routerIndex,
                r.route as routerRoute,
                r.area as routerArea,
                
                -- 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.second_qr_scan_status as secondQrScanStatus,
                jpo.second_qr_scan_by as secondQrScanBy,
                jpo.second_qr_scan_qty as secondQrScanQty
                
            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.router r ON r.inventoryLotId = ill.id AND r.deleted = false
            LEFT JOIN fpsmsdb.inventory_lot il ON il.id = ill.inventoryLotId
            LEFT JOIN fpsmsdb.warehouse w ON w.id = ill.warehouseId
            LEFT JOIN fpsmsdb.stock_out_line sol ON sol.pickOrderLineId = pol.id AND sol.inventoryLotLineId = ill.id AND sol.deleted = false
            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(r.index, 0) 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()
    }
}
}
