package com.ffii.fpsms.modules.pickOrder.entity

import com.ffii.core.support.AbstractRepository
import com.ffii.fpsms.modules.pickOrder.entity.projection.PickOrderInfo
import com.ffii.fpsms.modules.pickOrder.enums.PickOrderStatus
import com.ffii.fpsms.modules.pickOrder.web.models.SearchPickOrderRequest
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param
import org.springframework.stereotype.Repository
import java.io.Serializable
import java.time.LocalDateTime

@Repository
interface PickOrderRepository : AbstractRepository<PickOrder, Long> {
    fun findPickOrderInfoByDeletedIsFalse(): List<PickOrderInfo>

    @Query(
        """
            select po from PickOrder po
            where (lower(:code) = 'all' or lower(po.code) like concat('%',lower(:code),'%')) 
            and (:targetDateFrom is null or po.targetDate >= :targetDateFrom) 
            and (:targetDateTo is null or po.targetDate <= :targetDateTo)
            and (lower(:type) = 'all' or lower(po.type) like concat('%',lower(:type),'%'))
            and (lower(:status) = 'all' or lower(po.status) like concat('%',lower(:status),'%'))
            and (lower(:itemName) = 'all' or exists (
                    select 1 from PickOrderLine pol
                    where pol.pickOrder = po
                    and (lower(:itemName) = 'all' or lower(pol.item.name) like concat('%',lower(:itemName),'%'))
                )
            )
            and po.consoCode = null
            and po.deleted = false
        """
    )
    fun findPickOrderInfoByConditionsAndPageable(
        code: String,
        targetDateFrom: LocalDateTime?,
        targetDateTo: LocalDateTime?,
        type: String,
        status: String,
        itemName: String,
        pageable: Pageable,
    ): Page<PickOrderInfo>

    @Query("""
        select po.consoCode from PickOrder po where po.consoCode like :prefix% order by po.consoCode desc limit 1
    """)
    fun findLatestConsoCodeByPrefix(prefix: String): String?
    fun findAllByIdInAndStatus(id: List<Serializable>, status: PickOrderStatus): List<PickOrder>

    fun findAllByIdInAndConsoCodeIsNullAndStatus(id: List<Serializable>, status: PickOrderStatus): List<PickOrder>

    fun findPickOrderInfoByIdIn(id: List<Serializable>): List<PickOrderInfo>

    fun findAllByConsoCode(consoCode: String): List<PickOrder>

    fun findAllByConsoCodeAndStatus(consoCode: String, status: PickOrderStatus): List<PickOrder>
}