// StockTakeRecordService.kt
package com.ffii.fpsms.modules.stock.service

import com.ffii.fpsms.modules.master.entity.WarehouseRepository
import com.ffii.fpsms.modules.stock.entity.InventoryLotLineRepository
import com.ffii.fpsms.modules.stock.entity.StockTakeRepository
import com.ffii.fpsms.modules.stock.entity.StockTakeRecordRepository
import com.ffii.fpsms.modules.stock.entity.StockTakeRecord
import com.ffii.fpsms.modules.stock.enums.StockTakeStatus
import com.ffii.fpsms.modules.stock.web.model.*
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.stereotype.Service
import java.time.LocalDateTime
import java.math.BigDecimal
import com.ffii.fpsms.modules.user.entity.UserRepository
import org.springframework.data.domain.PageRequest
import com.ffii.core.response.RecordsRes
import java.time.LocalDate
import com.ffii.fpsms.modules.stock.service.InventoryLotLineService
import com.ffii.fpsms.modules.stock.entity.StockTakeLine
import com.ffii.fpsms.modules.stock.entity.StockTakeLineRepository
import com.ffii.fpsms.modules.stock.enums.StockTakeLineStatus
import com.ffii.fpsms.modules.stock.entity.StockOut
import com.ffii.fpsms.modules.stock.entity.StockOutRepository
import com.ffii.fpsms.modules.stock.entity.StockOutLine
import com.ffii.fpsms.modules.stock.entity.StockOutLIneRepository
import com.ffii.fpsms.modules.stock.entity.StockIn
import com.ffii.fpsms.modules.stock.entity.StockInRepository
import com.ffii.fpsms.modules.stock.entity.StockInLine
import com.ffii.fpsms.modules.stock.entity.StockInLineRepository
import com.ffii.fpsms.modules.stock.entity.InventoryLot
import com.ffii.fpsms.modules.stock.entity.InventoryLotRepository
import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import org.hibernate.Session
import org.hibernate.jdbc.Work
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import com.ffii.fpsms.modules.stock.entity.StockLedgerRepository
import com.ffii.fpsms.modules.stock.entity.InventoryRepository
import com.ffii.fpsms.modules.stock.entity.StockLedger
@Service
class StockTakeRecordService(
    val stockTakeRepository: StockTakeRepository,
    val warehouseRepository: WarehouseRepository,
    val inventoryLotLineRepository: InventoryLotLineRepository,
    val stockTakeRecordRepository: StockTakeRecordRepository,
    val userRepository: UserRepository,
    val stockTakeLineRepository: StockTakeLineRepository,
    val inventoryLotLineService: InventoryLotLineService,
    val stockOutRepository: StockOutRepository,
    val stockOutLineRepository: StockOutLIneRepository,
    val stockInRepository: StockInRepository,
    val stockInLineRepository: StockInLineRepository,
    val inventoryLotRepository: InventoryLotRepository,
    val stockLedgerRepository: StockLedgerRepository,
    val inventoryRepository: InventoryRepository

) {
    private val logger: Logger = LoggerFactory.getLogger(StockTakeRecordService::class.java)

    open fun AllPickedStockTakeList(): List<AllPickedStockTakeListReponse> {
        // 1. 获取所有不同的 stockTakeSection（从 warehouse 表）
        val allWarehouses = warehouseRepository.findAllByDeletedIsFalse()
        val distinctSections = allWarehouses
            .mapNotNull { it.stockTakeSection }
            .distinct()
            .filter { !it.isBlank() }
        
        if (distinctSections.isEmpty()) {
            return emptyList()
        }
        
    
        val warehousesBySection = allWarehouses
            .filter { it.stockTakeSection != null && !it.stockTakeSection!!.isBlank() }
            .groupBy { it.stockTakeSection!! }
  
        val allStockTakes = stockTakeRepository.findAll()
            .filter { !it.deleted }
            .groupBy { it.stockTakeSection }
        

        val allStockTakeRecords = stockTakeRecordRepository.findAll()
            .filter { !it.deleted }
        

        val recordsByWarehouseId = allStockTakeRecords
            .filter { it.warehouse?.id != null }
            .groupBy { it.warehouse!!.id }
        
        val result = mutableListOf<AllPickedStockTakeListReponse>()
        var idCounter = 1L
        
        // 3. 为每个 stockTakeSection 创建一个卡片
        distinctSections.forEach { stockTakeSection ->
            // 4. 获取该 section 下的所有 warehouse
            val warehouses = warehousesBySection[stockTakeSection] ?: emptyList()
            val warehouseIds = warehouses.mapNotNull { it.id }
            
            if (warehouseIds.isEmpty()) {
                return@forEach
            }
            
            // 5. 获取该 section 相关的所有 stock_take 记录
            val stockTakesForSection = allStockTakes[stockTakeSection] ?: emptyList()
            
            // 6. 获取 lastStockTakeDate：从 completed 状态的记录中，按 actualEnd 排序，取最新的
            val completedStockTakes = stockTakesForSection
                .filter { it.status == StockTakeStatus.COMPLETED && it.actualEnd != null }
                .sortedByDescending { it.actualEnd }
            
            val lastStockTakeDate = completedStockTakes.firstOrNull()?.actualEnd
            
       
            val latestStockTake = stockTakesForSection
                .maxByOrNull { it.actualStart ?: it.planStart ?: LocalDateTime.MIN }
            
            val status = if (latestStockTake != null) {
                latestStockTake.status?.value ?: "pending"
            } else {
                "no_cycle"
            }
            val stockTakerName = if (latestStockTake != null) {
                // 从该 stock take 的 records 中获取最新的 stockTakerName
                val recordsForStockTake = allStockTakeRecords
                    .filter { 
                        it.stockTake?.id == latestStockTake.id &&
                        it.stockTakeSection == stockTakeSection &&
                        it.stockTakerName != null
                    }
                    .sortedByDescending { it.stockTakeStartTime ?: LocalDateTime.MIN }
                
                recordsForStockTake.firstOrNull()?.stockTakerName
            } else {
                null
            }
            val approverName = if (latestStockTake != null) {
                // 从该 stock take 的 records 中获取最新的 approverName
                val recordsForStockTake = allStockTakeRecords
                    .filter { 
                        it.stockTake?.id == latestStockTake.id &&
                        it.stockTakeSection == stockTakeSection &&
                        it.approverName != null
                    }
                    .sortedByDescending { it.stockTakeStartTime ?: LocalDateTime.MIN }
                
                recordsForStockTake.firstOrNull()?.approverName
            } else {
                null
            }
            // 9. 计算 TotalItemNumber：获取该 section 下所有 InventoryLotLine，按 item 分组，计算不同的 item 数量
            val totalItemNumber = inventoryLotLineRepository.countDistinctItemsByWarehouseIds(warehouseIds).toInt()
            val totalInventoryLotNumber = inventoryLotLineRepository.countAllByWarehouseIds(warehouseIds).toInt()
            // 8. 使用 stockTakeSection 作为 stockTakeSession
            val reStockTakeTrueFalse = if (latestStockTake != null) {
                // 检查该 stock take 下该 section 的记录中是否有 notMatch 状态
                val hasNotMatch = allStockTakeRecords.any {
                    !it.deleted &&
                    it.stockTake?.id == latestStockTake.id &&
                    it.stockTakeSection == stockTakeSection &&
                    it.status == "notMatch"
                }
                hasNotMatch
            } else {
                false
            }
            result.add(
                AllPickedStockTakeListReponse(
                    id = idCounter++,
                    stockTakeSession = stockTakeSection,
                    lastStockTakeDate = latestStockTake?.actualStart?.toLocalDate(),
                    status = status?:"",
                    currentStockTakeItemNumber = 0, 
                    totalInventoryLotNumber = totalInventoryLotNumber,   
                    stockTakeId = latestStockTake?.id ?: 0,
                    stockTakerName = stockTakerName,
                    TotalItemNumber = totalItemNumber,
                    approverName = approverName,
                    startTime = latestStockTake?.actualStart,
                    endTime = latestStockTake?.actualEnd,
                    ReStockTakeTrueFalse = reStockTakeTrueFalse

                )
            )
        }
        
        return result.sortedBy { it.stockTakeSession }
    }
    open fun AllApproverStockTakeList(): List<AllPickedStockTakeListReponse> {
        // 1. 获取所有不同的 stockTakeSection（从 warehouse 表）
        val allWarehouses = warehouseRepository.findAllByDeletedIsFalse()
        val distinctSections = allWarehouses
            .mapNotNull { it.stockTakeSection }
            .distinct()
            .filter { !it.isBlank() }
        
        if (distinctSections.isEmpty()) {
            return emptyList()
        }
        
        // 2. 批量获取所有相关的数据（优化性能 - 只查询一次）
        // 2.1 按 section 分组 warehouse
        val warehousesBySection = allWarehouses
            .filter { it.stockTakeSection != null && !it.stockTakeSection!!.isBlank() }
            .groupBy { it.stockTakeSection!! }
        
        // 2.2 批量获取所有 stock_take 记录（只获取一次），按 stockTakeSection 分组
        val allStockTakes = stockTakeRepository.findAll()
            .filter { !it.deleted }
            .groupBy { it.stockTakeSection }
        
        // 2.3 批量获取所有 stocktakeRecord（只获取一次）
        val allStockTakeRecords = stockTakeRecordRepository.findAll()
            .filter { !it.deleted }
        
        // 2.4 按 warehouseId 分组 stocktakeRecord，便于快速查找
        val recordsByWarehouseId = allStockTakeRecords
            .filter { it.warehouse?.id != null }
            .groupBy { it.warehouse!!.id }
        
        val result = mutableListOf<AllPickedStockTakeListReponse>()
        var idCounter = 1L
        
        // 3. 为每个 stockTakeSection 创建一个卡片
        distinctSections.forEach { stockTakeSection ->
            // 4. 获取该 section 下的所有 warehouse
            val warehouses = warehousesBySection[stockTakeSection] ?: emptyList()
            val warehouseIds = warehouses.mapNotNull { it.id }
            
            if (warehouseIds.isEmpty()) {
                return@forEach
            }
            
            // 5. 获取该 section 相关的所有 stock_take 记录
            val stockTakesForSection = allStockTakes[stockTakeSection] ?: emptyList()
            
            // 6. 获取 lastStockTakeDate：从 completed 状态的记录中，按 actualEnd 排序，取最新的
            val completedStockTakes = stockTakesForSection
                .filter { it.status == StockTakeStatus.COMPLETED && it.actualEnd != null }
                .sortedByDescending { it.actualEnd }
            
            val lastStockTakeDate = completedStockTakes.firstOrNull()?.actualEnd?.toLocalDate()
            
            // 7. 获取 status：获取最新的 stock_take 记录（按 actualStart 或 planStart 排序）
            val latestStockTake = stockTakesForSection
                .maxByOrNull { it.actualStart ?: it.planStart ?: LocalDateTime.MIN }
            
            // 8. 确定 status：只有 APPROVING 或 COMPLETED 状态才输出，其他为 null
            val status = if (latestStockTake != null) {
                val stockTakeStatus = latestStockTake.status
                // 只有 APPROVING 或 COMPLETED 状态才输出值，其他返回 null
                if (stockTakeStatus == StockTakeStatus.APPROVING || stockTakeStatus == StockTakeStatus.COMPLETED) {
                    stockTakeStatus.value
                } else {
                    null
                }
            } else {
                null
            }
            val stockTakerName = if (latestStockTake != null) {
                // 从该 stock take 的 records 中获取最新的 stockTakerName
                val recordsForStockTake = allStockTakeRecords
                    .filter { 
                        it.stockTake?.id == latestStockTake.id &&
                        it.stockTakeSection == stockTakeSection &&
                        it.stockTakerName != null
                    }
                    .sortedByDescending { it.stockTakeStartTime ?: LocalDateTime.MIN }
                
                recordsForStockTake.firstOrNull()?.stockTakerName
            } else {
                null
            }
            val approverName = if (latestStockTake != null) {
                // 从该 stock take 的 records 中获取最新的 approverName
                val recordsForStockTake = allStockTakeRecords
                    .filter { 
                        it.stockTake?.id == latestStockTake.id &&
                        it.stockTakeSection == stockTakeSection &&
                        it.approverName != null
                    }
                    .sortedByDescending { it.stockTakeStartTime ?: LocalDateTime.MIN }
                
                recordsForStockTake.firstOrNull()?.approverName
            } else {
                null
            }
            val reStockTakeTrueFalse = if (latestStockTake != null) {
                // 检查该 stock take 下该 section 的记录中是否有 notMatch 状态
                val hasNotMatch = allStockTakeRecords.any {
                    !it.deleted &&
                    it.stockTake?.id == latestStockTake.id &&
                    it.stockTakeSection == stockTakeSection &&
                    it.status == "notMatch"
                }
                hasNotMatch
            } else {
                false
            }
            // 9. 计算 TotalItemNumber：获取该 section 下所有 InventoryLotLine，按 item 分组，计算不同的 item 数量
            val totalItemNumber = inventoryLotLineRepository.countDistinctItemsByWarehouseIds(warehouseIds).toInt()
            val totalInventoryLotNumber = inventoryLotLineRepository.countAllByWarehouseIds(warehouseIds).toInt()
            // 9. 使用 stockTakeSection 作为 stockTakeSession
            result.add(
                AllPickedStockTakeListReponse(
                    id = idCounter++,
                    stockTakeSession = stockTakeSection,
                    lastStockTakeDate = latestStockTake?.actualStart?.toLocalDate(),
                    status = status?:"",
                    currentStockTakeItemNumber = 0, // 临时设为 0，测试性能
                    totalInventoryLotNumber = totalInventoryLotNumber,   // 临时设为 0，测试性能
                    stockTakeId = latestStockTake?.id ?: 0,
                    stockTakerName = stockTakerName,
                    approverName = approverName,
                    TotalItemNumber = totalItemNumber,
                    startTime = latestStockTake?.actualStart,
                    endTime = latestStockTake?.actualEnd,
             
                    ReStockTakeTrueFalse = reStockTakeTrueFalse
                )
            )
        }
        
        return result.sortedBy { it.stockTakeSession }
    }
    open fun getInventoryLotDetailsByWarehouseCode(warehouseCode: String): List<InventoryLotDetailResponse> {
        println("getInventoryLotDetailsByWarehouseCode called with code: $warehouseCode")
        
        // 1. 先查找 warehouse，可能有多个相同 code 的记录，选择 id 最小的（最早的）
        val warehouses = warehouseRepository.findAllByCodeAndDeletedIsFalse(warehouseCode)
        if (warehouses.isEmpty()) {
            logger.warn("Warehouse not found with code: $warehouseCode")
            return emptyList()
        }
        
        // 按 id 排序，取第一个（id 最小的，即最早的）
        val warehouse = warehouses.minByOrNull { it.id ?: Long.MAX_VALUE }
        if (warehouse == null) {
            logger.warn("Warehouse found but id is null for code: $warehouseCode")
            return emptyList()
        }
        println("Found warehouse: id=${warehouse.id}, code=${warehouse.code} (total ${warehouses.size} found, using earliest)")
        
        // 2. 根据 warehouse id 查找所有相关的 InventoryLotLine
        val inventoryLotLines = inventoryLotLineRepository.findAllByWarehouseIdInAndDeletedIsFalse(listOf(warehouse.id!!))
        println("Found ${inventoryLotLines.size} inventory lot lines")
        
        // 3. 转换为 Response
        return inventoryLotLines.map { ill ->
            val inventoryLot = ill.inventoryLot
            val item = inventoryLot?.item
            val warehouse = ill.warehouse
            val availableQty = (ill.inQty ?: BigDecimal.ZERO) 
                .subtract(ill.outQty ?: BigDecimal.ZERO)
                .subtract(ill.holdQty ?: BigDecimal.ZERO)
            
            InventoryLotDetailResponse(
                id = ill.id ?: 0L,
                inventoryLotId = inventoryLot?.id ?: 0L,
                itemId = item?.id ?: 0L,
                itemCode = item?.code,
                itemName = item?.name,
                lotNo = inventoryLot?.lotNo,
                expiryDate = inventoryLot?.expiryDate,
                productionDate = inventoryLot?.productionDate,
                stockInDate = inventoryLot?.stockInDate,
                inQty = ill.inQty,
                outQty = ill.outQty,
                holdQty = ill.holdQty,
                availableQty = availableQty,
                uom = ill.stockUom?.uom?.udfudesc,
                warehouseCode = warehouse?.code,
                warehouseName = warehouse?.name,
                status = ill.status?.name,
                stockTakeRecordStatus = null, 
                stockTakeRecordId = null,
                firstStockTakeQty = null,
                secondStockTakeQty = null,
                approverQty = null,
                varianceQty = null,
                approverBadQty = null,
                finalQty = null,
                 firstBadQty = null,
                 secondBadQty = null,
                remarks = null,
                warehouseSlot = warehouse?.slot,
                warehouseArea = warehouse?.area,
                warehouse = warehouse?.warehouse

            )
        }
    }
    open fun getInventoryLotDetailsByStockTakeSection(
    stockTakeSection: String, 
    stockTakeId: Long? = null,
    pageNum: Int = 0,
    pageSize: Int = 10
): RecordsRes<InventoryLotDetailResponse> {
    println("getInventoryLotDetailsByStockTakeSection called with section: $stockTakeSection, stockTakeId: $stockTakeId, pageNum: $pageNum, pageSize: $pageSize")
    
    val warehouses = warehouseRepository.findAllByStockTakeSectionAndDeletedIsFalse(stockTakeSection)
    if (warehouses.isEmpty()) {
        logger.warn("No warehouses found for stockTakeSection: $stockTakeSection")
        return RecordsRes(emptyList(), 0)
    }
    
    val warehouseIds = warehouses.mapNotNull { it.id }
    println("Found ${warehouses.size} warehouses for section $stockTakeSection")
    
    val inventoryLotLines = inventoryLotLineRepository.findAllByWarehouseIdInAndDeletedIsFalse(warehouseIds)
    println("Found ${inventoryLotLines.size} inventory lot lines")
    
    val stockTakeRecordsMap = if (stockTakeId != null) {
        val allStockTakeRecords = stockTakeRecordRepository.findAll()
            .filter { 
                !it.deleted && 
                it.stockTake?.id == stockTakeId &&
                it.warehouse?.id in warehouseIds
            }
        // 按 lotId 和 warehouseId 建立映射
        allStockTakeRecords.associateBy { 
            Pair(it.lotId ?: 0L, it.warehouse?.id ?: 0L) 
        }
    } else {
        emptyMap()
    }
    
    val allResults = inventoryLotLines.map { ill ->
        val inventoryLot = ill.inventoryLot
        val item = inventoryLot?.item
        val warehouse = ill.warehouse
        val availableQty = (ill.inQty ?: BigDecimal.ZERO) 
            .subtract(ill.outQty ?: BigDecimal.ZERO)
            .subtract(ill.holdQty ?: BigDecimal.ZERO)
        
        val stockTakeRecord = if (stockTakeId != null && inventoryLot?.id != null && warehouse?.id != null) {
            stockTakeRecordsMap[Pair(inventoryLot.id, warehouse.id)]
        } else {
            null
        }
        val inventoryLotLineId = ill.id
        val stockTakeLine = stockTakeLineRepository.findByInventoryLotLineIdAndStockTakeIdAndDeletedIsFalse(inventoryLotLineId, stockTakeId!!)
        InventoryLotDetailResponse(
            id = ill.id ?: 0L,
            inventoryLotId = inventoryLot?.id ?: 0L,
            itemId = item?.id ?: 0L,
            itemCode = item?.code,
            itemName = item?.name,
            lotNo = inventoryLot?.lotNo,
            expiryDate = inventoryLot?.expiryDate,
            productionDate = inventoryLot?.productionDate,
            stockInDate = inventoryLot?.stockInDate,
            inQty = ill.inQty,
            remarks = stockTakeRecord?.remarks,
            outQty = ill.outQty,
            holdQty = ill.holdQty,
            availableQty = availableQty,
            uom = ill.stockUom?.uom?.udfudesc,
            warehouseCode = warehouse?.code,
            warehouseName = warehouse?.name,
            status = ill.status?.name,
            warehouseSlot = warehouse?.slot,
            warehouseArea = warehouse?.area,
            warehouse = warehouse?.warehouse,
            varianceQty = stockTakeRecord?.varianceQty,
            stockTakeRecordId = stockTakeRecord?.id,
            stockTakeRecordStatus = stockTakeRecord?.status,
            firstStockTakeQty = stockTakeRecord?.pickerFirstStockTakeQty,
            secondStockTakeQty = stockTakeRecord?.pickerSecondStockTakeQty,
            firstBadQty = stockTakeRecord?.pickerFirstBadQty,
            secondBadQty = stockTakeRecord?.pickerSecondBadQty,
            approverQty = stockTakeRecord?.approverStockTakeQty ,
            approverBadQty = stockTakeRecord?.approverBadQty,
            finalQty = stockTakeLine?.finalQty,
        )
    }
    
    // Apply pagination
    val pageable = PageRequest.of(pageNum, pageSize)
    val startIndex = pageable.offset.toInt()
    val endIndex = minOf(startIndex + pageSize, allResults.size)
    val paginatedResult = if (startIndex < allResults.size) {
        allResults.subList(startIndex, endIndex)
    } else {
        emptyList()
    }
    
    return RecordsRes(paginatedResult, allResults.size)
}
    open fun saveStockTakeRecord(
        request: SaveStockTakeRecordRequest,
        stockTakeId: Long,
        stockTakerId: Long
    ): StockTakeRecord {
        println("saveStockTakeRecord called with stockTakeRecordId: ${request.stockTakeRecordId}, inventoryLotLineId: ${request.inventoryLotLineId}")
        val user = userRepository.findById(stockTakerId).orElse(null)
        // 1. 获取 inventory lot line
        val inventoryLotLine = inventoryLotLineRepository.findByIdAndDeletedIsFalse(request.inventoryLotLineId)
            ?: throw IllegalArgumentException("Inventory lot line not found: ${request.inventoryLotLineId}")
        
        val inventoryLot = inventoryLotLine.inventoryLot
            ?: throw IllegalArgumentException("Inventory lot not found")
        val item = inventoryLot.item
            ?: throw IllegalArgumentException("Item not found")
        val warehouse = inventoryLotLine.warehouse
            ?: throw IllegalArgumentException("Warehouse not found")
        val stockTake = stockTakeRepository.findByIdAndDeletedIsFalse(stockTakeId)
            ?: throw IllegalArgumentException("Stock take not found: $stockTakeId")
        
        // 2. 计算 availableQty
        val availableQty = (inventoryLotLine.inQty ?: BigDecimal.ZERO)
            .subtract(inventoryLotLine.outQty ?: BigDecimal.ZERO)
            .subtract(inventoryLotLine.holdQty ?: BigDecimal.ZERO)
        
        // 3. 判断是创建还是更新
        val stockTakeRecord = if (request.stockTakeRecordId != null) {
            // 更新现有记录（第二次盘点）
            val existingRecord = stockTakeRecordRepository.findByIdAndDeletedIsFalse(request.stockTakeRecordId)
                ?: throw IllegalArgumentException("Stock take record not found: ${request.stockTakeRecordId}")
            
            // 第二次盘点：允许不匹配，但根据匹配情况设置状态
            val totalInputQty = request.qty.add(request.badQty)
            val isMatched = totalInputQty.compareTo(availableQty) == 0
            val varianceQty = availableQty-request.qty-request.badQty
            // 更新字段（第二次盘点）
            existingRecord.apply {
                this.pickerSecondStockTakeQty = request.qty
                this.pickerSecondBadQty = request.badQty // 更新 badQty
                this.status ="pass"
                this.remarks = request.remark ?: this.remarks
                this.stockTakerName = user?.name
                this.stockTakeEndTime = java.time.LocalDateTime.now()
               
                this.varianceQty = varianceQty
            }
            existingRecord
        } else {

            val totalInputQty = request.qty.add(request.badQty)
            val isMatched = totalInputQty.compareTo(availableQty) == 0
            val isCompled=inventoryLotLine.inQty==inventoryLotLine.outQty
            val varianceQty = availableQty-request.qty-request.badQty
            StockTakeRecord().apply {
                this.itemId = item.id
                this.lotId = inventoryLot.id
                this.warehouse = warehouse
                this.stockTake = stockTake
                this.stockTakeSection = warehouse.stockTakeSection
                this.inventoryLotId = inventoryLot.id
                this.stockTakerId = stockTakerId
                this.stockTakerName = user?.name
                this.pickerFirstStockTakeQty = request.qty
                this.pickerFirstBadQty = request.badQty
                this.bookQty = availableQty
                this.varianceQty = varianceQty
                this.uom = inventoryLotLine.stockUom?.uom?.udfudesc
                this.date = java.time.LocalDate.now()
                this.status = if (isCompled) "completed" else "pass" 
                this.remarks = request.remark
                this.itemCode = item.code
                this.itemName = item.name
                this.lotNo = inventoryLot.lotNo
                this.expiredDate = inventoryLot.expiryDate
                this.stockTakeStartTime = java.time.LocalDateTime.now()
            }
        }
        
        
        val savedRecord = stockTakeRecordRepository.save(stockTakeRecord)
        if (request.stockTakeRecordId == null) {
            val existingRecordsCount = stockTakeRecordRepository.findAll()
                .filter { 
                    !it.deleted && 
                    it.stockTake?.id == stockTakeId &&
                    it.id != savedRecord.id  // 排除刚创建的这条记录
                }
                .count()
            
        
            if (existingRecordsCount == 0 && stockTake.actualStart == null) {
                stockTake.actualStart = java.time.LocalDateTime.now()
                stockTake.status = StockTakeStatus.STOCKTAKING
                stockTakeRepository.save(stockTake)
                println("Stock take $stockTakeId actualStart updated - first record created")
            }
        }
 
    val stockTakeSection = savedRecord.stockTakeSection
    if (stockTakeSection != null) {
        checkAndUpdateStockTakeStatus(stockTakeId, stockTakeSection)
    }
    
    return savedRecord
}
    


open fun batchSaveStockTakeRecords(
    request: BatchSaveStockTakeRecordRequest
): BatchSaveStockTakeRecordResponse {
    
    println("batchSaveStockTakeRecords called for section: ${request.stockTakeSection}, stockTakeId: ${request.stockTakeId}")
    val user = userRepository.findById(request.stockTakerId).orElse(null)
    // 1. 获取 stock take
    val stockTake = stockTakeRepository.findByIdAndDeletedIsFalse(request.stockTakeId)
        ?: throw IllegalArgumentException("Stock take not found: ${request.stockTakeId}")
    
    // 2. 使用 stockTakeSection 获取所有 inventory lot lines（类似 getInventoryLotDetailsByStockTakeSection）
    val warehouses = warehouseRepository.findAllByStockTakeSectionAndDeletedIsFalse(request.stockTakeSection)
    if (warehouses.isEmpty()) {
        logger.warn("No warehouses found for stockTakeSection: ${request.stockTakeSection}")
        return BatchSaveStockTakeRecordResponse(0, 0, listOf("No warehouses found for section: ${request.stockTakeSection}"))
    }
    
    val warehouseIds = warehouses.mapNotNull { it.id }
    println("Found ${warehouses.size} warehouses for section ${request.stockTakeSection}")
    
    // 3. 批量获取所有相关的 InventoryLotLine
    val inventoryLotLines = inventoryLotLineRepository.findAllByWarehouseIdInAndDeletedIsFalse(warehouseIds)
    println("Found ${inventoryLotLines.size} inventory lot lines")
    
    // 4. 使用 stockTakeId 获取已创建的记录，建立映射以排除它们
    val existingRecordsMap = stockTakeRecordRepository.findAll()
        .filter { 
            !it.deleted && 
            it.stockTake?.id == request.stockTakeId &&
            it.warehouse?.id in warehouseIds
        }
        .associateBy { 
            Pair(it.inventoryLotId ?: 0L, it.warehouse?.id ?: 0L) 
        }
    
    println("Found ${existingRecordsMap.size} existing stock take records")
    
    // 5. 找出未创建的记录
    val uncreatedLines = inventoryLotLines.filter { ill ->
        val inventoryLot = ill.inventoryLot
        val warehouse = ill.warehouse
        if (inventoryLot?.id == null || warehouse?.id == null) {
            false
        } else {
            val key = Pair(inventoryLot.id, warehouse.id)
            !existingRecordsMap.containsKey(key)
        }
    }
    
    println("Found ${uncreatedLines.size} uncreated inventory lot lines to process")
    
    if (uncreatedLines.isEmpty()) {
        return BatchSaveStockTakeRecordResponse(0, 0, listOf("No uncreated records found"))
    }
    
    // 6. 批量创建记录
    var successCount = 0
    var errorCount = 0
    val errors = mutableListOf<String>()
    
    uncreatedLines.forEach { ill ->
        try {
            val inventoryLot = ill.inventoryLot
                ?: throw IllegalArgumentException("Inventory lot not found")
            val itemEntity = inventoryLot.item
                ?: throw IllegalArgumentException("Item not found")
            val warehouse = ill.warehouse
                ?: throw IllegalArgumentException("Warehouse not found")
            
            // 计算 availableQty
            val availableQty = (ill.inQty ?: BigDecimal.ZERO)
                .subtract(ill.outQty ?: BigDecimal.ZERO)
                .subtract(ill.holdQty ?: BigDecimal.ZERO)
            
            // 使用 availableQty 作为 qty，badQty 为 0
            val qty = availableQty
            val badQty = BigDecimal.ZERO
            
            // 判断是否匹配
            val totalInputQty = qty.add(badQty)
            val isMatched = totalInputQty.compareTo(availableQty) == 0
            val varianceQty = availableQty-qty-badQty
            val isCompleted = (ill.inQty ?: BigDecimal.ZERO) == (ill.outQty ?: BigDecimal.ZERO)
            // 创建新记录
            val stockTakeRecord = StockTakeRecord().apply {
                this.itemId = itemEntity.id
                this.lotId = inventoryLot.id
                this.warehouse = warehouse
                this.stockTake = stockTake
                this.stockTakeSection = request.stockTakeSection
                this.inventoryLotId = inventoryLot.id
                this.stockTakerId = request.stockTakerId
                this.stockTakerName = user?.name
                this.pickerFirstStockTakeQty = qty
                this.pickerFirstBadQty = badQty
                this.bookQty = availableQty
                this.varianceQty = varianceQty
                this.uom = ill.stockUom?.uom?.udfudesc
                this.date = java.time.LocalDate.now()
                this.status = if (isCompleted) "completed" else "pass"
                this.remarks = null
                this.itemCode = itemEntity.code
                this.itemName = itemEntity.name
                this.lotNo = inventoryLot.lotNo
                this.expiredDate = inventoryLot.expiryDate
                this.stockTakeStartTime = java.time.LocalDateTime.now()
                this.stockTakeEndTime = java.time.LocalDateTime.now()
            }
            
            stockTakeRecordRepository.save(stockTakeRecord)
            successCount++
        } catch (e: Exception) {
            errorCount++
            val errorMsg = "Error saving inventoryLotLineId ${ill.id}: ${e.message}"
            errors.add(errorMsg)
            logger.error(errorMsg, e)
        }
    }
    if (successCount > 0) {
        val existingRecordsCount = stockTakeRecordRepository.findAll()
        .filter { 
            !it.deleted && 
            it.stockTake?.id == request.stockTakeId
        }
        .count()
        checkAndUpdateStockTakeStatus(request.stockTakeId, request.stockTakeSection)
    }
    println("batchSaveStockTakeRecords completed: success=$successCount, errors=$errorCount")
    return BatchSaveStockTakeRecordResponse(
        successCount = successCount,
        errorCount = errorCount,
        errors = errors
    )
}

open fun checkAndUpdateStockTakeStatus(stockTakeId: Long, stockTakeSection: String): Map<String, Any> {
    try {
        val stockTake = stockTakeRepository.findByIdAndDeletedIsFalse(stockTakeId)
            ?: return mapOf("success" to false, "message" to "Stock take not found")
        
        // 1. 获取该 section 下的所有 warehouse
        val warehouses = warehouseRepository.findAllByStockTakeSectionAndDeletedIsFalse(stockTakeSection)
        if (warehouses.isEmpty()) {
            return mapOf("success" to false, "message" to "No warehouses found for section")
        }
        
        val warehouseIds = warehouses.mapNotNull { it.id }
        
        // 2. 获取该 section 下的所有 inventory lot lines
        val inventoryLotLines = inventoryLotLineRepository.findAllByWarehouseIdInAndDeletedIsFalse(warehouseIds)
        
        // 3. 获取该 stock take 下该 section 的所有记录
        val stockTakeRecords = stockTakeRecordRepository.findAll()
            .filter { 
                !it.deleted && 
                it.stockTake?.id == stockTakeId &&
                it.warehouse?.id in warehouseIds &&
                it.stockTakeSection == stockTakeSection
            }
        
        // 4. 检查是否所有 inventory lot lines 都有对应的记录
        val allLinesHaveRecords = inventoryLotLines.all { ill ->
            val inventoryLot = ill.inventoryLot
            val warehouse = ill.warehouse
            if (inventoryLot?.id == null || warehouse?.id == null) {
                false
            } else {
                stockTakeRecords.any { record ->
                    record.inventoryLotId == inventoryLot.id && 
                    record.warehouse?.id == warehouse.id
                }
            }
        }
        
       
        val allRecordsPassed = stockTakeRecords.isNotEmpty() && 
            stockTakeRecords.all { it.status == "pass" || it.status == "completed" }
        val allRecordsCompleted = stockTakeRecords.isNotEmpty() && 
            stockTakeRecords.all { it.status == "completed" }
        // 6. 如果所有记录都已创建且都是 "pass"，更新 stock take 状态为 "approving"
        if (allLinesHaveRecords && allRecordsCompleted) {
            stockTake.status = StockTakeStatus.COMPLETED
            stockTake.planEnd = java.time.LocalDateTime.now()
            stockTakeRepository.save(stockTake)
            println("Stock take $stockTakeId status updated to COMPLETED - all records are completed")
            return mapOf("success" to true, "message" to "Stock take status updated to COMPLETED", "updated" to true)
        } else if (allLinesHaveRecords && allRecordsPassed) {
            // 如果所有记录都已创建且都是 "pass" 或 "completed"，更新 stock take 状态为 "approving"
            stockTake.status = StockTakeStatus.APPROVING
            stockTake.actualEnd = java.time.LocalDateTime.now()
            stockTakeRepository.save(stockTake)
        
            println("Stock take $stockTakeId status updated to APPROVING - all records are pass")
            return mapOf(
                "success" to true, 
                "message" to "Stock take status updated to APPROVING",
                "updated" to true
            )
        } else {
            return mapOf(
                "success" to true, 
                "message" to "Conditions not met for status update",
                "updated" to false,
                "allLinesHaveRecords" to allLinesHaveRecords,
                "allRecordsPassed" to allRecordsPassed,
                "allRecordsCompleted" to allRecordsCompleted
            )
        }
    } catch (e: Exception) {
        logger.error("Error checking and updating stock take status for stockTakeId: $stockTakeId", e)
        return mapOf("success" to false, "message" to "Error: ${e.message}")
    }
}
open fun saveApproverStockTakeRecord(
    request: SaveApproverStockTakeRecordRequest,
    stockTakeId: Long
): StockTakeRecord {
    println("saveApproverStockTakeRecord called with stockTakeRecordId: ${request.stockTakeRecordId}")
    val user = userRepository.findById(request.approverId ?: 0L).orElse(null)
    
 
    val stockTakeRecord = if (request.stockTakeRecordId != null) {
        stockTakeRecordRepository.findByIdAndDeletedIsFalse(request.stockTakeRecordId)
            ?: throw IllegalArgumentException("Stock take record not found: ${request.stockTakeRecordId}")
    } else {
        throw IllegalArgumentException("stockTakeRecordId is required for approver")
    }
    
    val stockTake = stockTakeRepository.findByIdAndDeletedIsFalse(stockTakeId)
        ?: throw IllegalArgumentException("Stock take not found: $stockTakeId")
    

    val finalQty = if (request.approverQty != null && request.approverBadQty != null) {
        request.approverQty
    } else {
        request.qty
    }
    
    val finalBadQty = if (request.approverQty != null && request.approverBadQty != null) {
        request.approverBadQty
    } else {
        request.badQty
    }
    val varianceQty = (finalQty ?: BigDecimal.ZERO)
        .subtract( stockTakeRecord.bookQty?: BigDecimal.ZERO)
    println("finalQty: $finalQty")
    println("stockTakeRecord.bookQty: ${stockTakeRecord.bookQty}")
    println("varianceQty: $varianceQty")
    stockTakeRecord.apply {
        this.approverId = request.approverId
        this.approverName = user?.name
        this.approverStockTakeQty = finalQty
        this.approverBadQty = finalBadQty
        this.varianceQty = varianceQty
        this.status = "completed"
    }
    
  
    val savedRecord = stockTakeRecordRepository.save(stockTakeRecord)
    
   
    val inventoryLotLine = inventoryLotLineRepository.findByIdAndDeletedIsFalse(
        stockTakeRecord.inventoryLotId ?: throw IllegalArgumentException("Inventory lot ID not found")
    ) ?: throw IllegalArgumentException("Inventory lot line not found")
    val inventoryLot = inventoryLotRepository.findByIdAndDeletedFalse(
        inventoryLotLine?.inventoryLot?.id ?: throw IllegalArgumentException("Inventory lot ID not found")
    ) ?: throw IllegalArgumentException("Inventory lot not found")
    val inventory = inventoryRepository.findByItemId(inventoryLot.item?.id ?: throw IllegalArgumentException("Item ID not found"))
    .orElse(null) ?: throw IllegalArgumentException("Inventory not found for item")

    if (varianceQty !=BigDecimal.ZERO ) {
        val stockTakeLine = StockTakeLine().apply {
            this.stockTake = stockTake
            this.inventoryLotLine = inventoryLotLine
            this.initialQty = stockTakeRecord.bookQty
            this.finalQty = finalQty
            this.status = StockTakeLineStatus.COMPLETED
            this.completeDate = java.time.LocalDateTime.now()

        }
        stockTakeLineRepository.save(stockTakeLine)

    if (varianceQty < BigDecimal.ZERO ) {
        var stockOut = stockOutRepository.findByStockTakeIdAndDeletedFalse(stockTakeId)
        ?: StockOut().apply {
            this.type = "stockTake"
            this.status = "completed"
            this.handler = request.approverId
   
        }.also { stockOutRepository.save(it) }
        
        val stockOutLine = StockOutLine().apply {
            this.item=inventoryLot.item
            this.qty=(-varianceQty)?.toDouble()
            this.stockOut = stockOut
            this.inventoryLotLine = inventoryLotLine
            this.status = "completed"
            this.type = "ADJ"
            
        }
        stockOutLineRepository.save(stockOutLine)
        val newBalance = (inventory.onHandQty ?: BigDecimal.ZERO).toDouble()-(varianceQty).toDouble()
        val stockLedger = StockLedger().apply {
            this.inventory=inventory
            this.itemId=inventoryLot.item?.id
            this.itemCode=stockTakeRecord.itemCode
            this.inQty=null
            this.outQty=(-varianceQty)?.toDouble()
            this.stockOutLine=stockOutLine
            this.balance=newBalance
            this.type="Adj"
            this.date = LocalDate.now()
        }
        stockLedgerRepository.save(stockLedger)
    }
    if (varianceQty > BigDecimal.ZERO  ) {
        var stockIn = stockInRepository.findByStockTakeIdAndDeletedFalse(stockTakeId)
        ?: StockIn().apply {
            this.code = stockTake.code
            this.status = "completed"
            this.stockTake = stockTake
        }.also { stockInRepository.save(it) }

        val stockInLine = StockInLine().apply {
            this.stockTakeLine=stockTakeLine
            this.item=inventoryLot.item
            this.itemNo=stockTakeRecord.itemCode
            this.stockIn = stockIn
            this.demandQty=varianceQty
            this.acceptedQty=varianceQty
            this.expiryDate=inventoryLot.expiryDate
            this.inventoryLot=inventoryLot
            this.inventoryLotLine=inventoryLotLine
            this.lotNo=inventoryLot.lotNo
            this.status = "completed"
            this.type = "ADJ"

            
        }
        stockInLineRepository.save(stockInLine)
        val newBalance = (inventory.onHandQty ?: BigDecimal.ZERO).toDouble()+varianceQty.toDouble()
        val stockLedger = StockLedger().apply {
            this.inventory=inventory
            this.itemId=inventoryLot.item?.id
            this.itemCode=inventoryLot.item?.code
            this.inQty=varianceQty?.toDouble()
            this.stockInLine=stockInLine
            this.outQty=null
            this.balance=newBalance
            this.type="Adj"
            this.date = LocalDate.now()
        }
        stockLedgerRepository.save(stockLedger)
    }

        
        val updateRequest = SaveInventoryLotLineRequest(
            id = inventoryLotLine.id,
            inventoryLotId = inventoryLotLine.inventoryLot?.id,
            warehouseId = inventoryLotLine.warehouse?.id,
            stockUomId = inventoryLotLine.stockUom?.id,
            inQty = finalQty,
            outQty = inventoryLotLine.outQty,
            holdQty = inventoryLotLine.holdQty,
            status = inventoryLotLine.status?.value,
            remarks = inventoryLotLine.remarks
        )
        inventoryLotLineService.saveInventoryLotLine(updateRequest)
}
val stockTakeSection = savedRecord.stockTakeSection
if (stockTakeSection != null) {
    checkAndUpdateStockTakeStatus(stockTakeId, stockTakeSection)
}
    return savedRecord
}

open fun batchSaveApproverStockTakeRecords(
    request: BatchSaveApproverStockTakeRecordRequest
): BatchSaveApproverStockTakeRecordResponse {
    println("batchSaveApproverStockTakeRecords called for section: ${request.stockTakeSection}, stockTakeId: ${request.stockTakeId}")
    val user = userRepository.findById(request.approverId).orElse(null)
    
    // 1. Get stock take
    val stockTake = stockTakeRepository.findByIdAndDeletedIsFalse(request.stockTakeId)
        ?: throw IllegalArgumentException("Stock take not found: ${request.stockTakeId}")
    

    val stockTakeRecords = stockTakeRecordRepository.findAll()
        .filter { 
            !it.deleted && 
            it.stockTake?.id == request.stockTakeId &&
            it.stockTakeSection == request.stockTakeSection &&
            it.pickerFirstStockTakeQty != null && it.pickerFirstStockTakeQty!! > BigDecimal.ZERO &&
            it.approverStockTakeQty == null // 只处理未审批的记录
        }
    
    println("Found ${stockTakeRecords.size} records to process")
    
    if (stockTakeRecords.isEmpty()) {
        return BatchSaveApproverStockTakeRecordResponse(0, 0, listOf("No records found matching criteria"))
    }
    
    // 3. Process each record
    var successCount = 0
    var errorCount = 0
    var skippedCount = 0
    val errors = mutableListOf<String>()
    
    stockTakeRecords.forEach { record ->
        try {
            
            val qty: BigDecimal
            val badQty: BigDecimal
            
            if (record.pickerSecondStockTakeQty != null && record.pickerSecondStockTakeQty!! > BigDecimal.ZERO) {
                
                qty = record.pickerSecondStockTakeQty!!
                badQty = record.pickerSecondBadQty ?: BigDecimal.ZERO
            } else {
                
                qty = record.pickerFirstStockTakeQty ?: BigDecimal.ZERO
                badQty = record.pickerFirstBadQty ?: BigDecimal.ZERO
            }
            
            
            val bookQty = record.bookQty ?: BigDecimal.ZERO
            val varianceQty = qty.subtract(bookQty)
            
            
            if (varianceQty.compareTo(BigDecimal.ZERO) != 0) {
                skippedCount++
                println("Skipping record ${record.id}: variance = $varianceQty (qty=$qty, bookQty=$bookQty)")
                return@forEach
            }
            
            // variance = 0，批量保存并设置为 completed
            record.apply {
                this.approverId = request.approverId
                this.approverName = user?.name
                this.approverStockTakeQty = qty
                this.approverBadQty = badQty
                this.varianceQty = varianceQty 
                this.status = "completed"
            }
            
            stockTakeRecordRepository.save(record)
            successCount++
        } catch (e: Exception) {
            errorCount++
            val errorMsg = "Error processing record ${record.id}: ${e.message}"
            errors.add(errorMsg)
            logger.error(errorMsg, e)
        }
    }
    
    // 只在最后调用一次 checkAndUpdateStockTakeStatus
    if (successCount > 0) {
        checkAndUpdateStockTakeStatus(request.stockTakeId, request.stockTakeSection)
    }
    
    println("batchSaveApproverStockTakeRecords completed: success=$successCount, skipped=$skippedCount, errors=$errorCount")
    return BatchSaveApproverStockTakeRecordResponse(
        successCount = successCount,
        errorCount = errorCount,
        errors = errors
    )
}


open fun updateStockTakeRecordStatusToNotMatch(stockTakeRecordId: Long): StockTakeRecord {
    println("updateStockTakeRecordStatusToNotMatch called with stockTakeRecordId: $stockTakeRecordId")
    
    val stockTakeRecord = stockTakeRecordRepository.findByIdAndDeletedIsFalse(stockTakeRecordId)
        ?: throw IllegalArgumentException("Stock take record not found: $stockTakeRecordId")
    
    stockTakeRecord.apply {
        this.status = "notMatch"
    }
    
    return stockTakeRecordRepository.save(stockTakeRecord)
}

open fun getInventoryLotDetailsByStockTakeSectionNotMatch(
    stockTakeSection: String, 
    stockTakeId: Long? = null,
    pageNum: Int = 0,
    pageSize: Int = Int.MAX_VALUE  // Default to return all if not specified
): RecordsRes<InventoryLotDetailResponse> {
    println("getInventoryLotDetailsByStockTakeSectionNotMatch called with section: $stockTakeSection, stockTakeId: $stockTakeId, pageNum: $pageNum, pageSize: $pageSize")
    
    val warehouses = warehouseRepository.findAllByStockTakeSectionAndDeletedIsFalse(stockTakeSection)
    if (warehouses.isEmpty()) {
        logger.warn("No warehouses found for stockTakeSection: $stockTakeSection")
        return RecordsRes(emptyList(), 0)
    }
    
    val warehouseIds = warehouses.mapNotNull { it.id }
    println("Found ${warehouses.size} warehouses for section $stockTakeSection")
    
    val inventoryLotLines = inventoryLotLineRepository.findAllByWarehouseIdInAndDeletedIsFalse(warehouseIds)
    println("Found ${inventoryLotLines.size} inventory lot lines")
    
    val stockTakeRecordsMap = if (stockTakeId != null) {
        val allStockTakeRecords = stockTakeRecordRepository.findAll()
            .filter { 
                !it.deleted && 
                it.stockTake?.id == stockTakeId &&
                it.warehouse?.id in warehouseIds &&
                it.status == "notMatch"  // 只获取 notMatch 状态的记录
            }
        // 按 lotId 和 warehouseId 建立映射
        allStockTakeRecords.associateBy { 
            Pair(it.lotId ?: 0L, it.warehouse?.id ?: 0L) 
        }
    } else {
        emptyMap()
    }
    
    // 只返回有 notMatch 记录的 inventory lot lines
    val allResults = inventoryLotLines.mapNotNull { ill ->
        val inventoryLot = ill.inventoryLot
        val item = inventoryLot?.item
        val warehouse = ill.warehouse
        val availableQty = (ill.inQty ?: BigDecimal.ZERO) 
            .subtract(ill.outQty ?: BigDecimal.ZERO)
            .subtract(ill.holdQty ?: BigDecimal.ZERO)
        
        val stockTakeRecord = if (stockTakeId != null && inventoryLot?.id != null && warehouse?.id != null) {
            stockTakeRecordsMap[Pair(inventoryLot.id, warehouse.id)]
        } else {
            null
        }
        
        // 只返回状态为 notMatch 的记录
        if (stockTakeRecord == null || stockTakeRecord.status != "notMatch") {
            return@mapNotNull null
        }
        
        val inventoryLotLineId = ill.id
        val stockTakeLine = if (stockTakeId != null) {
            stockTakeLineRepository.findByInventoryLotLineIdAndStockTakeIdAndDeletedIsFalse(inventoryLotLineId, stockTakeId)
        } else {
            null
        }
        
        InventoryLotDetailResponse(
            id = ill.id ?: 0L,
            inventoryLotId = inventoryLot?.id ?: 0L,
            itemId = item?.id ?: 0L,
            itemCode = item?.code,
            itemName = item?.name,
            lotNo = inventoryLot?.lotNo,
            expiryDate = inventoryLot?.expiryDate,
            productionDate = inventoryLot?.productionDate,
            stockInDate = inventoryLot?.stockInDate,
            inQty = ill.inQty,
            remarks = stockTakeRecord.remarks,
            outQty = ill.outQty,
            holdQty = ill.holdQty,
            availableQty = availableQty,
            uom = ill.stockUom?.uom?.udfudesc,
            warehouseCode = warehouse?.code,
            warehouseName = warehouse?.name,
            status = ill.status?.name,
            warehouseSlot = warehouse?.slot,
            warehouseArea = warehouse?.area,
            warehouse = warehouse?.warehouse,
            varianceQty = stockTakeRecord.varianceQty,
            stockTakeRecordId = stockTakeRecord.id,
            stockTakeRecordStatus = stockTakeRecord.status,
            firstStockTakeQty = stockTakeRecord.pickerFirstStockTakeQty,
            secondStockTakeQty = stockTakeRecord.pickerSecondStockTakeQty,
            firstBadQty = stockTakeRecord.pickerFirstBadQty,
            secondBadQty = stockTakeRecord.pickerSecondBadQty,
            approverQty = stockTakeRecord.approverStockTakeQty,
            approverBadQty = stockTakeRecord.approverBadQty,
            finalQty = stockTakeLine?.finalQty,
        )
    }
    
    // Apply pagination - if pageSize is Int.MAX_VALUE, return all results
    val paginatedResult = if (pageSize >= allResults.size) {
        // Return all results if pageSize is large enough
        allResults
    } else {
        val pageable = PageRequest.of(pageNum, pageSize)
        val startIndex = pageable.offset.toInt()
        val endIndex = minOf(startIndex + pageSize, allResults.size)
        if (startIndex < allResults.size) {
            allResults.subList(startIndex, endIndex)
        } else {
            emptyList()
        }
    }
    
    return RecordsRes(paginatedResult, allResults.size)
}



fun searchStockTransactions(request: SearchStockTransactionRequest): RecordsRes<StockTransactionResponse> {
    val startTime = System.currentTimeMillis()
    
    // 添加调试日志
    println("Search request received: itemCode=${request.itemCode}, itemName=${request.itemName}, type=${request.type}, startDate=${request.startDate}, endDate=${request.endDate}")
    
    // 验证：itemCode 或 itemName 至少一个不为 null 或空字符串
    val itemCode = request.itemCode?.trim()?.takeIf { it.isNotEmpty() }
    val itemName = request.itemName?.trim()?.takeIf { it.isNotEmpty() }
    
    if (itemCode == null && itemName == null) {
        println("Search validation failed: both itemCode and itemName are null/empty")
        return RecordsRes(emptyList(), 0)
    }
    
    // request.startDate 和 request.endDate 已经是 LocalDate? 类型，不需要转换
    val startDate = request.startDate
    val endDate = request.endDate
    
    println("Processed params: itemCode=$itemCode, itemName=$itemName, startDate=$startDate, endDate=$endDate")
    
    // 使用 Repository 查询（更简单、更快）
    val total = stockLedgerRepository.countStockTransactions(
        itemCode = itemCode,
        itemName = itemName,
        type = request.type,
        startDate = startDate,
        endDate = endDate
    )
    
    println("Total count: $total")
    
    // 如果 pageSize 是默认值（100）或未设置，使用 total 作为 pageSize
    val actualPageSize = if (request.pageSize == 100) {
        total.toInt().coerceAtLeast(1)
    } else {
        request.pageSize
    }
    
    // 计算 offset
    val offset = request.pageNum * actualPageSize
    
    // 查询所有符合条件的记录
    val ledgers = stockLedgerRepository.findStockTransactions(
        itemCode = itemCode,
        itemName = itemName,
        type = request.type,
        startDate = startDate,
        endDate = endDate
    )
    
    println("Found ${ledgers.size} ledgers")
    
    val transactions = ledgers.map { ledger ->
        val stockInLine = ledger.stockInLine
        val stockOutLine = ledger.stockOutLine
        
        StockTransactionResponse(
            id = stockInLine?.id ?: stockOutLine?.id ?: 0L,
            transactionType = if (ledger.inQty != null && ledger.inQty!! > 0) "IN" else "OUT",
            itemId = ledger.itemId ?: 0L,
            itemCode = ledger.itemCode,
            itemName = ledger.inventory?.item?.name,
            balanceQty = ledger.balance?.let { balance: Double -> BigDecimal(balance.toString()) },
            qty = if (ledger.inQty != null && ledger.inQty!! > 0) {
                BigDecimal(ledger.inQty.toString())
            } else {
                BigDecimal(ledger.outQty?.toString() ?: "0")
            },
            type = ledger.type,
            status = stockInLine?.status ?: stockOutLine?.status ?: "",
            transactionDate = ledger.created,
            date = ledger.date,
            lotNo = stockInLine?.lotNo ?: (stockOutLine?.inventoryLotLine?.inventoryLot?.lotNo),
            stockInId = stockInLine?.stockIn?.id,
            stockOutId = stockOutLine?.stockOut?.id,
            remarks = stockInLine?.remarks
        )
    }
    
    // 按 date 排序（从旧到新），如果 date 为 null 则使用 transactionDate 的日期部分
    val sortedTransactions = transactions.sortedWith(
        compareBy<StockTransactionResponse>(
            { it.date ?: it.transactionDate?.toLocalDate() },
            { it.transactionDate }
        )
    )
    
    // 应用分页
    val paginatedTransactions = sortedTransactions.drop(offset).take(actualPageSize)
    val totalTime = System.currentTimeMillis() - startTime
    println("Total time (Repository query): ${totalTime}ms, count: ${paginatedTransactions.size}, total: $total")
    
    return RecordsRes(paginatedTransactions, total.toInt())
}
}
