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

import com.ffii.core.support.AbstractRepository
import com.ffii.fpsms.modules.stock.entity.projection.QrCodeInfo
import com.ffii.fpsms.modules.stock.entity.projection.StockInLineInfo
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository
import java.util.Optional
import java.time.LocalDate
import org.springframework.data.repository.query.Param


@Repository
interface StockInLineRepository : AbstractRepository<StockInLine, Long> {
    fun findAllStockInLineInfoByStockInIdAndDeletedFalse(stockInId: Long): List<StockInLineInfo>
    fun findStockInLineInfoByIdAndDeletedFalse(id: Long): StockInLineInfo
    fun findAllByStockInIdAndDeletedFalse(stockInId: Long): List<StockInLine>
    fun findStockInLineInfoByIdInAndDeletedFalse(id: List<Long>): List<StockInLineInfo>
    fun findStockInLineInfoByPurchaseOrderLineIdAndStatusNotAndDeletedFalse(purchaseOrderLineId: Long, status: String): List<StockInLineInfo>
    fun findStockInLineInfoByPurchaseOrderLineIdAndDeletedFalse(purchaseOrderLineId: Long): List<StockInLineInfo>
    fun findStockInLineInfoByIdAndStatusAndDeletedFalse(id: Long, status: String): Optional<StockInLineInfo>
    fun findAllStockInLineInfoByPurchaseOrderIdAndStatusStartsWithAndDeletedFalse(purchaseOrderId: Long, status: String): List<Optional<StockInLineInfo>>
    fun findAllByPurchaseOrderIdAndDeletedFalse(purchaseOrderId: Long): Optional<List<StockInLine>>
    fun findStockInLineInfoByInventoryLotLineId(inventoryLotLineId: Long): Optional<StockInLineInfo>
    fun findStockInLineInfoById(id: Long): Optional<StockInLineInfo>

    @Query("SELECT sil FROM StockInLine sil WHERE sil.item.id = :itemId AND sil.deleted = false")
    fun findAllByItemIdAndDeletedFalse(itemId: Long): List<StockInLine>

    @Query("""
    SELECT sil FROM StockInLine sil 
    WHERE sil.receiptDate IS NOT NULL 
    AND DATE(sil.receiptDate) = :date 
    AND sil.deleted = false
""")
fun findByReceiptDateAndDeletedFalse(date: LocalDate): List<StockInLine>
@Query("""
    SELECT sil FROM StockInLine sil 
    WHERE sil.deleted = false
    AND (:type IS NULL OR sil.type IS NOT NULL)
    AND (:startDate IS NULL OR DATE(sil.created) >= :startDate)
    AND (:endDate IS NULL OR DATE(sil.created) <= :endDate)
    AND (:itemCode IS NULL OR sil.item.code LIKE CONCAT('%', :itemCode, '%'))
    AND (:itemName IS NULL OR sil.item.name LIKE CONCAT('%', :itemName, '%'))
    AND (:type IS NULL OR sil.type = :type)
    ORDER BY sil.created DESC
""")
fun searchStockInLines(
    @Param("startDate") startDate: LocalDate?,
    @Param("endDate") endDate: LocalDate?,
    @Param("itemCode") itemCode: String?,
    @Param("itemName") itemName: String?,
    @Param("type") type: String?
): List<StockInLine>
//AND sil.type IS NOT NULL
@Query("SELECT sil FROM StockInLine sil WHERE sil.item.id IN :itemIds AND sil.deleted = false")
fun findAllByItemIdInAndDeletedFalse(itemIds: List<Long>): List<StockInLine>
}