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.jpa.repository.Query
import org.springframework.stereotype.Repository
import java.io.Serializable

@Repository
interface InventoryLotLineRepository : AbstractRepository<InventoryLotLine, Long> {
    fun findInventoryLotLineInfoByInventoryLotItemIdIn(ids: List<Serializable>): List<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>
}