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

import com.ffii.core.support.AbstractRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param
import org.springframework.stereotype.Repository
import java.time.LocalDate
import java.util.Optional
@Repository
interface StockLedgerRepository: AbstractRepository<StockLedger, Long> {
    
    @Query("""
        SELECT sl FROM StockLedger sl
        LEFT JOIN sl.stockInLine sil
        LEFT JOIN sl.stockOutLine sol
        LEFT JOIN sl.inventory inv
        LEFT JOIN inv.item i
        WHERE sl.deleted = false
          AND (:itemCode IS NULL OR sl.itemCode LIKE CONCAT('%', :itemCode, '%'))
          AND (:itemName IS NULL OR i.name LIKE CONCAT('%', :itemName, '%'))
          AND (:type IS NULL OR sl.type = :type)
          AND (:startDate IS NULL OR DATE(sl.created) >= :startDate)
          AND (:endDate IS NULL OR DATE(sl.created) <= :endDate)
        ORDER BY sl.created ASC, sl.itemId
    """)
    fun findStockTransactions(
        @Param("itemCode") itemCode: String?,
        @Param("itemName") itemName: String?,
        @Param("type") type: String?,
        @Param("startDate") startDate: LocalDate?,
        @Param("endDate") endDate: LocalDate?
    ): List<StockLedger>
    
    @Query("""
        SELECT COUNT(sl) FROM StockLedger sl
        LEFT JOIN sl.inventory inv
        LEFT JOIN inv.item i
        WHERE sl.deleted = false
          AND (:itemCode IS NULL OR sl.itemCode LIKE CONCAT('%', :itemCode, '%'))
          AND (:itemName IS NULL OR i.name LIKE CONCAT('%', :itemName, '%'))
          AND (:type IS NULL OR sl.type = :type)
          AND (:startDate IS NULL OR DATE(sl.created) >= :startDate)
          AND (:endDate IS NULL OR DATE(sl.created) <= :endDate)
    """)
    fun countStockTransactions(
        @Param("itemCode") itemCode: String?,
        @Param("itemName") itemName: String?,
        @Param("type") type: String?,
        @Param("startDate") startDate: LocalDate?,
        @Param("endDate") endDate: LocalDate?
    ): Long


    @Query("""
    SELECT sl FROM StockLedger sl
    WHERE sl.itemId = :itemId AND sl.deleted = false
    ORDER BY sl.date DESC, sl.id DESC
""")
fun findLatestByItemId(@Param("itemId") itemId: Long): List<StockLedger>
}