package com.ffii.fpsms.modules.stock.entity

import com.ffii.core.support.AbstractRepository
import com.ffii.fpsms.modules.stock.entity.projection.CurrentInventoryItemInfo
import com.ffii.fpsms.modules.stock.entity.projection.InventoryLotLineInfo
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository
import java.io.Serializable
import com.ffii.fpsms.modules.stock.entity.enum.InventoryLotLineStatus
import org.springframework.data.repository.query.Param

@Repository
interface InventoryLotLineRepository : AbstractRepository<InventoryLotLine, Long> {

    fun findByIdAndDeletedIsFalse(id: Serializable): InventoryLotLine;

    fun findInventoryLotLineInfoByInventoryLotItemIdIn(ids: List<Serializable>): List<InventoryLotLineInfo>

    @Query("select ill from InventoryLotLine ill where :id is null or ill.inventoryLot.item.id = :id order by ill.id desc")
    fun findInventoryLotLineInfoByItemId(id: Long?, pageable: Pageable): Page<InventoryLotLineInfo>

    @Query("""
        select 
            i.id as id,
            i.code as itemCode,
            i.name as itemName,
            sum(coalesce(ill.inQty, 0) - coalesce(ill.outQty, 0) - coalesce(ill.holdQty, 0)) as availableQty
        from InventoryLotLine ill 
        left join InventoryLot il on ill.inventoryLot = il
        left join Items i on il.item = i
        where i.id in :items
        group by i.id
    """)
    fun findCurrentInventoryByItems(items: List<Serializable>): List<CurrentInventoryItemInfo>

    fun findAllByIdIn(ids: List<Serializable>): List<InventoryLotLine>

    fun findAllByInventoryLotId(id: Serializable): List<InventoryLotLine>

    fun findByInventoryLotStockInLineIdAndWarehouseId(inventoryLotStockInLineId: Long, warehouseId: Long): InventoryLotLine?

    fun findAllByInventoryLotItemIdAndStatus(itemId: Long, status: String): List<InventoryLotLine>
    fun findAllByInventoryLotItemIdAndStatus(itemId: Long, status: InventoryLotLineStatus): List<InventoryLotLine>
}