package cn.ysk.cashier.repository.shop;

import cn.ysk.cashier.dto.shop.TbCountStorageDto;
import cn.ysk.cashier.pojo.shop.TbShopStorage;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

/**
 * @website https://eladmin.vip
 * @author ww
 * @date 2024-05-21
 **/
public interface TbShopStorageRepository extends JpaRepository<TbShopStorage, Integer>, JpaSpecificationExecutor<TbShopStorage> {


    @Query("SELECT new cn.ysk.cashier.dto.shop.TbCountStorageDto(" +
            "storage.name, " +
            "storage.imgUrl, " +
            "SUM( CASE WHEN storage.status = 1 THEN storage.num ELSE 0 END), " +
            "SUM( CASE WHEN storage.status = 2 THEN storage.num ELSE 0 END)" +
            ") " +
            "FROM TbShopStorage storage  " +
            "WHERE (:shopId IS NULL OR storage.shopId = :shopId) " +
            "GROUP BY storage.name " +
            "ORDER BY SUM(CASE WHEN storage.status = 1 THEN storage.num ELSE 0 END) desc")
    Page<TbCountStorageDto> countStorage(Integer shopId, Pageable pageable);

    @Modifying
    @Query(value = "UPDATE tb_shop_storage SET `status` = '2' WHERE exp_time > DATE_ADD( NOW(), INTERVAL -2 DAY ) AND exp_time < NOW() AND `status` = '1'",nativeQuery = true)
    void expStorage();
}