package cn.ysk.cashier.repository.product;

import cn.ysk.cashier.pojo.product.TbProductSku;
import cn.ysk.cashier.vo.StockV2Vo;
import cn.ysk.cashier.vo.StockVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;
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;

import javax.transaction.Transactional;
import java.math.BigDecimal;
import java.util.List;

/**
* @website https://eladmin.vip
* @author lyf
* @date 2024-01-03
**/
public interface TbProductSkuRepository extends JpaRepository<TbProductSku, Integer>, JpaSpecificationExecutor<TbProductSku> {

    @Query("SELECT sku FROM TbProductSku sku WHERE sku.productId IN :productId and sku.isDel=0")
    List<TbProductSku> searchSku(@Param("productId")List<String> productId);

    @Query("SELECT sku FROM TbProductSku sku WHERE sku.productId = :productId and sku.isDel=0")
    List<TbProductSku> searchSku(@Param("productId")String productId);

    @Transactional
    @Modifying
    @Query("DELETE FROM TbProductSku sku WHERE sku.productId = :productId")
    Integer deleteByProductId(@Param("productId") String productId);

    @Transactional
    @Modifying
    @Query("update FROM TbProductSku sku set sku.isDel=1 WHERE sku.id = :skuId")
    Integer deleteBySkuId(@Param("skuId") Integer skuId);

    @Query("SELECT sku FROM TbProductSku sku WHERE sku.barCode = :barCode and sku.isDel=0")
    TbProductSku searchBarCode(@Param("barCode")String barCode);

    @Query("SELECT new cn.ysk.cashier.vo.StockVo(" +
            "sku.id,pro.id,pro.coverImg,pro.name,unit.name,pro.typeEnum,sku.specSnap,pro.isStock,sku.stockNumber" +
            ") " +
            "from " +
            "TbProduct pro " +
            "LEFT JOIN TbProductSku sku on pro.id = sku.productId " +
            "LEFT JOIN TbShopUnit unit ON pro.unitId = unit.id " +
            "WHERE " +
            "pro.shopId = :shopId " +
            "AND pro.status = 1 " +
            "AND sku.isDel=0 " +
            "AND (:proName IS NULL OR pro.name LIKE %:proName%) " +
            "AND (:num IS NULL OR sku.stockNumber < :num) " +
            "AND (:isStock IS NULL OR pro.isStock = :isStock) " +
            "ORDER BY " +
            "pro.name DESC,sku.stockNumber DESC")
    Page<StockVo> searchProStock(@Param("shopId") String shopId, @Param("proName") String proName, @Param("isStock")Integer isStock,@Param("num")Double num, Pageable pageable);

    @Query("SELECT new cn.ysk.cashier.vo.StockV2Vo(" +
            "sku.id,pro.id,pro.coverImg,pro.name,unit.name,pro.typeEnum,sku.specSnap,pro.isStock, " +
            "CASE WHEN pro.isDistribute = 1 THEN IFNULL(pro.stockNumber, 0) ELSE SUM(sku.stockNumber) END as number" +
            ", pro.isDistribute, pro.isPauseSale, true, sku.warnLine, pro.lowPrice, CASE WHEN sum(sku.isGrounding) > 0 THEN true ELSE false END as isGrounding)  " +
            "from " +
            "TbProduct pro " +
            "LEFT JOIN TbProductSku sku on pro.id = sku.productId " +
            "LEFT JOIN TbShopUnit unit ON pro.unitId = unit.id " +
            "WHERE " +
            "pro.shopId = :shopId " +
            "AND pro.status = 1 " +
            "AND sku.isDel = 0 " +
            "AND (:categoryId IS NULL OR pro.categoryId = :categoryId) " +
            "AND (:proName IS NULL OR pro.name LIKE %:proName%) " +
            "AND (:isStock IS NULL OR pro.isStock = :isStock) " +
            "group by pro.id " +
            "order by number asc ")
    Page<StockV2Vo> searchProStockV2(@Param("shopId") String shopId, @Param("proName") String proName, @Param("isStock")Integer isStock,
                                     @Param("categoryId") String categoryId, Pageable pageable);

    @Query("SELECT new cn.ysk.cashier.vo.StockV2Vo(" +
            "sku.id,pro.id,pro.coverImg,pro.name,unit.name,pro.typeEnum,sku.specSnap,pro.isStock, " +
            "CASE WHEN pro.isDistribute = 1 THEN IFNULL(pro.stockNumber, 0) ELSE SUM(sku.stockNumber) END as number" +
            ", pro.isDistribute, pro.isPauseSale, true, sku.warnLine, pro.lowPrice, CASE WHEN sum(sku.isGrounding) > 0 THEN true ELSE false END as isGrounding)  " +
            "from " +
            "TbProduct pro " +
            "LEFT JOIN TbProductSku sku on pro.id = sku.productId " +
            "LEFT JOIN TbShopUnit unit ON pro.unitId = unit.id " +
            "WHERE " +
            "pro.shopId = :shopId " +
            "AND pro.status = 1 " +
            "AND sku.isDel = 0 " +
            "AND (:categoryId IS NULL OR pro.categoryId = :categoryId) " +
            "AND (:proName IS NULL OR pro.name LIKE %:proName%) " +
            "AND (:isStock IS NULL OR pro.isStock = :isStock) " +
            "group by pro.id " +
            "order by number desc ")
    Page<StockV2Vo> searchProStockV2ByDesc(@Param("shopId") String shopId, @Param("proName") String proName, @Param("isStock")Integer isStock,
                                     @Param("categoryId") String categoryId, Pageable pageable);

    @Query("SELECT new cn.ysk.cashier.vo.StockV2Vo(" +
            "sku.id,pro.id,pro.coverImg,pro.name,unit.name,pro.typeEnum,sku.specSnap,pro.isStock, " +
            "CASE " +
            "        WHEN pro.isDistribute = 1 THEN pro.stockNumber " +
            "        ELSE SUM(sku.stockNumber) " +
            "    END " +
            ", pro.isDistribute, pro.isPauseSale, true ) " +
            "from " +
            "TbProduct pro " +
            "LEFT JOIN TbProductSku sku on pro.id = sku.productId " +
            "LEFT JOIN TbShopUnit unit ON pro.unitId = unit.id " +
            "WHERE " +
            "pro.shopId = :shopId " +
            "AND pro.status = 1 " +
            "AND sku.isDel=0 " +
            "AND (:categoryId IS NULL OR pro.categoryId = :categoryId) " +
            "AND (:proName IS NULL OR pro.name LIKE %:proName%) " +
            "AND (:isStock IS NULL OR pro.isStock = :isStock) " +
            "group by pro.id " +
            "ORDER BY " +
            "pro.name DESC,sku.stockNumber DESC")
    List<StockV2Vo> searchProStockV2(@Param("shopId") String shopId, @Param("proName") String proName, @Param("isStock")Integer isStock,
                                     @Param("categoryId") String categoryId);


    @Query("SELECT new cn.ysk.cashier.vo.StockV2Vo(" +
            "sku.id,pro.id,pro.coverImg,pro.name," +
            "unit.name,pro.typeEnum,sku.specSnap,pro.isStock,sku.stockNumber, 1, sku.isPauseSale, false, 0, sku.salePrice, CASE WHEN sku.isGrounding=1 THEN true ELSE false END" +
            ") " +
            "from " +
            "TbProductSku sku " +
            "left join TbProduct pro on sku.productId = pro.id " +
            "left join TbShopUnit unit on pro.unitId = unit.id " +
            "where " +
            "sku.productId = :productId " +
            "AND sku.isDel=0 " +
            "order by " +
            "sku.stockNumber desc "
    )
    List<StockV2Vo> searchProductSkuByProductId(@Param("productId") String productId);

    @Query("SELECT new cn.ysk.cashier.vo.StockVo(" +
            "sku.id,pro.id,pro.coverImg,pro.name,unit.name,pro.typeEnum,sku.specSnap,pro.isStock,sku.stockNumber" +
            ") " +
            "from " +
            "TbProduct pro " +
            "LEFT JOIN TbProductSku sku on pro.id = sku.productId " +
            "LEFT JOIN TbShopUnit unit ON pro.unitId = unit.id " +
            "WHERE " +
            "pro.shopId = :shopId " +
            "AND pro.status = 1 " +
            "AND sku.isDel=0 " +
            "AND (:proName IS NULL OR pro.name LIKE %:proName%) " +
            "AND (:isStock IS NULL OR pro.isStock = :isStock) " +
            "AND (:num IS NULL OR sku.stockNumber < :num) " +
            "ORDER BY " +
            "pro.name DESC,sku.stockNumber DESC")
    List<StockVo> searchProStock(@Param("shopId") String shopId, @Param("proName") String proName, @Param("isStock")Integer isStock,@Param("num")Double num);


    @Transactional
    @Modifying
    @Query("update FROM TbProductSku sku set sku.costPrice=:costPrice,sku.coverImg =:coverImg, " +
            "sku.firstShared=:firstShared,sku.memberPrice = :memberPrice,sku.originPrice = :originPrice, " +
            "sku.salePrice=:salePrice,sku.specSnap =:specSnap " +
            "WHERE sku.barCode = :barCode and sku.id =:id")
    Integer updateByBarCode(@Param("barCode") String barCode, @Param("costPrice")BigDecimal costPrice, @Param("coverImg")String coverImg,
    @Param("firstShared")BigDecimal firstShared,@Param("memberPrice")BigDecimal memberPrice,@Param("originPrice") BigDecimal originPrice,
    @Param("salePrice")BigDecimal salePrice,@Param("specSnap") String specSnap,@Param("id") Integer id);


    @Modifying
    @Query("update FROM TbProductSku sku set sku.stockNumber=sku.stockNumber+:stockNumber WHERE sku.id =:id")
    Integer updateStockNumber(@Param("id") Integer id, @Param("stockNumber") Double stockNumber);

    @Modifying
    @Query("update FROM TbProductSku sku set sku.stockNumber=:stockNumber WHERE sku.shopId=:shopId and sku.id =:id")
    Integer updateStockNumber2(@Param("id") Integer id,@Param("shopId")String shopId, @Param("stockNumber") Double stockNumber);

    @Modifying
    @Query("update FROM TbProductSku sku set sku.costPrice=:cost WHERE sku.productId=:productId")
    Integer updateCostByProductId(String productId, BigDecimal cost);

    @Transactional
    @Modifying
    @Query("UPDATE TbProductSku p SET p.warnLine = :warnLine WHERE p.shopId = :shopId")
    Integer updateWarnLineByShopId(@Param("warnLine")Integer warnLine, @Param("shopId")String shopId);

    @Modifying
    @Query("update TbProductSku set stockNumber=stockNumber+:num where id=:productId")
    void incrStock(@Param("productId") Integer productId, @Param("num") Double num);

    @Transactional
    @Modifying
    @Query("update TbProductSku set isGrounding=:isGrounding where productId=:productId")
    void updateGroundingByProId(@Param("productId") String productId, @Param("isGrounding") Integer isGrounding);

    @Transactional
    @Modifying
    @Query("update TbProductSku set isGrounding=:isGrounding where id=:skuId")
    void updateGrounding(Integer skuId,int isGrounding);
}
