package cn.ysk.cashier.repository.product;

import cn.ysk.cashier.dto.product.TbProductStockListDto;
import cn.ysk.cashier.vo.TbProductStockCountVo;
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.Query;

import java.util.Date;

public interface ProductStockCountRepository extends JpaRepository<TbProductStockListDto, Integer> {


    @Query(value = "SELECT" +
            " d.product_id as id," +
            " pro.name AS name," +
//            " d.spec_snap AS spec_snap," +
            " pro.stock_number AS stock_number," +
            " SUM( CASE WHEN d.sub_type = - 1 THEN ABS(d.stock_number) ELSE 0 END ) AS sub_count_number," +
            " SUM( CASE WHEN d.type IN ( '供应商入库', '盘点入库', '其他入库' ) THEN ABS(d.stock_number) ELSE 0 END ) AS add_number," +
            " SUM( CASE WHEN d.type IN ( '供应商出库', '盘点出库', '其他出库' ) THEN ABS(d.stock_number) ELSE 0 END ) AS sub_number," +
            " SUM( CASE WHEN d.type = '售出记录' THEN ABS(d.stock_number) ELSE 0 END ) AS sale_number," +
            " SUM( CASE WHEN d.type = '报损' THEN ABS(d.stock_number) ELSE 0 END ) AS loss_number," +
            " null AS stock_in_number," +
            " null AS stock_out_number," +
            " SUM( CASE WHEN d.type = '退单' THEN ABS(d.stock_number) ELSE 0 END ) AS refund_number " +
            "FROM" +
            " tb_product_stock_detail d " +
            " INNER JOIN tb_product pro ON d.product_id = pro.id " +
            "   AND pro.shop_id = :shopId " +
            "   AND (:categoryId IS NULL OR pro.category_id = :categoryId) " +
            "   AND (:productName IS NULL OR pro.name LIKE %:productName%) " +
            "    AND (:type IS NULL OR pro.type_enum = :type) " +
            " WHERE " +
            " d.shop_id = :shopId " +
            "AND d.created_at > :startTime " +
            "AND d.created_at < :endTime " +
            "GROUP BY " +
            "d.product_id ",
            countQuery = "SELECT count(1)" +
                    " FROM " +
                    "  tb_product_stock_detail d  " +
                    "  INNER JOIN tb_product pro ON d.product_id = pro.id  " +
                    "    and pro.shop_id = :shopId  " +
                    "    and (:categoryId IS NULL OR pro.category_id = :categoryId)  " +
                    "    AND (:productName IS NULL OR pro.name LIKE %:productName%) " +
                    "    AND (:type IS NULL OR pro.type_enum = :type) " +
                    "  WHERE  " +
                    "  d.shop_id = :shopId  " +
                    " AND d.created_at > :startTime  " +
                    " AND d.created_at < :endTime  " +
                    " GROUP BY  " +
                    " d.product_id "
            , nativeQuery = true)
    Page<TbProductStockListDto> productStock(String shopId, String productName, String categoryId, String type,
                                             long startTime, long endTime, Pageable pageable);

    @Query("SELECT new cn.ysk.cashier.vo.TbProductStockCountVo( " +
            " SUM( pro.stockNumber ) , " +
            " SUM( CASE WHEN detail.subType = 1 THEN ABS(detail.stockNumber) ELSE 0 END ) , " +
            " SUM( CASE WHEN detail.subType = - 1 THEN ABS(detail.stockNumber)  ELSE 0 END ) , " +
            " SUM( CASE WHEN detail.type IN ( '供应商入库', '盘点入库', '其他入库' ) THEN ABS(detail.stockNumber)  ELSE 0 END ) , " +
            " SUM( CASE WHEN detail.type IN ( '供应商出库', '盘点出库', '其他出库' ) THEN ABS(detail.stockNumber)  ELSE 0 END ) , " +
            " SUM( CASE WHEN detail.type = '售出记录' THEN ABS(detail.stockNumber)  ELSE 0 END ) , " +
            " SUM( CASE WHEN detail.type = '报损' THEN ABS(detail.stockNumber)  ELSE 0 END ) ," +
            " SUM( CASE WHEN detail.type = '退单' THEN ABS(detail.stockNumber)  ELSE 0 END ))" +
            "FROM " +
            " TbProductStockDetail detail " +
            " INNER JOIN TbProduct pro ON detail.productId = pro.id " +
            " AND pro.shopId = :shopId  " +
            " AND ( :categoryId IS NULL OR pro.categoryId = :categoryId )  " +
            " AND ( :productName IS NULL OR pro.name LIKE %:productName% )  " +
            " AND ( :type IS NULL OR pro.typeEnum = :type )  " +
            "WHERE " +
            " detail.shopId = :shopId  " +
            " AND detail.createdAt > :startTime  " +
            " AND detail.createdAt < :endTime")
    TbProductStockCountVo productStockCount(String shopId,
                                     String productName,
                                     String categoryId, String type, long startTime, long endTime);


    @Query(value = "SELECT " +
            " flow.cons_id AS id, " +
            " cons.con_name AS `name`, " +
            " cons.stock_number AS stock_number,  " +
            " SUM( CASE WHEN flow.biz_type = - 1 THEN flow.amount ELSE 0 END ) AS sub_count_number,   " +
            " SUM( CASE WHEN flow.biz_code IN ( 'checkStockIn','stockOtherIn' ) THEN flow.amount ELSE 0 END ) AS add_number,  " +
            " SUM( CASE WHEN flow.biz_code = 'checkStockOut' THEN flow.amount ELSE 0 END ) AS sub_number,   " +
            " SUM( CASE WHEN flow.biz_code = 'stockIn' THEN flow.amount ELSE 0 END ) AS stock_in_number,  " +
            " SUM( CASE WHEN flow.biz_code = 'stockout' THEN flow.amount ELSE 0 END ) AS stock_out_number,   " +
            " SUM( CASE WHEN flow.biz_code = 'createCart' THEN flow.amount ELSE 0 END ) AS sale_number,   " +
            " SUM( CASE WHEN flow.biz_code = 'frmLoss' THEN flow.amount ELSE 0 END ) AS loss_number,   " +
            " SUM( CASE WHEN flow.biz_code = 'cancelCart' THEN flow.amount ELSE 0 END ) AS refund_number    " +
            "FROM " +
            " tb_cons_info_flow flow " +
            " INNER JOIN tb_cons_info cons ON flow.cons_id = cons.id  " +
            " AND cons.shop_id = :shopId  " +
            " AND (:conTypeId IS NULL OR cons.con_type_id = :conTypeId )  " +
            " AND (:conName IS NULL OR flow.con_name LIKE %:conName%)  " +
            "WHERE " +
            " flow.shop_id = :shopId  " +
            " AND flow.created_at > :startTime  " +
            " AND flow.created_at < :endTime  " +
            "GROUP BY " +
            " flow.product_id",
            countQuery = "SELECT " +
                    " count(1) " +
                    "FROM " +
                    " tb_cons_info_flow flow " +
                    " INNER JOIN tb_cons_info cons ON flow.cons_id = cons.id  " +
                    " AND cons.shop_id = :shopId  " +
                    " AND (:conTypeId IS NULL OR pro.con_type_id = :conTypeId )  " +
                    " AND (:conName IS NULL OR flow.con_name LIKE %:conName%)  " +
                    "WHERE " +
                    " flow.shop_id = :shopId  " +
                    " AND flow.create_time > :startTime  " +
                    " AND flow.create_time < :endTime  " +
                    "GROUP BY " +
                    " flow.product_id"
            , nativeQuery = true)
    Page<TbProductStockListDto> consStock(String shopId,
                                          String conName,
                                          String conTypeId, String startTime, String endTime, Pageable pageable);


    //现有数量stockNumber;
    //增加数量addCountNumber;
    //减少数量subCountNumber;

    //手动增加addNumber;    checkStockIn stockOtherIn
    //手动减少subNumber;    checkStockOut
    //销售量/消耗saleNumber; createCart
    //报损lossNumber;     frmLoss
    //退货refundNumber;   cancelCart
    //入库stockInNumber;  stockIn
    //出库stockOutNumber; stockout
    @Query(" SELECT new cn.ysk.cashier.vo.TbProductStockCountVo(" +
            " SUM( cons.stockNumber ) , " +
            " SUM( CASE WHEN flow.bizType = '+' THEN flow.amount ELSE 0 END ) , " +
            " SUM( CASE WHEN flow.bizType = '-' THEN flow.amount ELSE 0 END ) , " +

            " SUM( CASE WHEN flow.bizCode IN ( 'checkStockIn','stockOtherIn' ) THEN flow.amount ELSE 0 END ) , " +
            " SUM( CASE WHEN flow.bizCode  = 'checkStockOut' THEN flow.amount ELSE 0 END ) , " +
            " SUM( CASE WHEN flow.bizCode = 'createCart' THEN flow.amount ELSE 0 END ) , " +
            " SUM( CASE WHEN flow.bizCode = 'frmLoss' THEN flow.amount ELSE 0 END ) ," +
            " SUM( CASE WHEN flow.bizCode = 'cancelCart' THEN flow.amount ELSE 0 END ) ," +
            " SUM( CASE WHEN flow.bizCode = 'stockIn' THEN flow.amount ELSE 0 END ) ," +
            " SUM( CASE WHEN flow.bizCode = 'stockout' THEN flow.amount ELSE 0 END )) " +
            "FROM " +
            " TbConsInfoFlow flow " +
            " INNER JOIN TbConsInfo cons ON flow.consId = cons.id  " +
            " AND cons.shopId = :shopId  " +
            " AND (:consId IS NULL OR cons.id = :consId )  " +
            " AND (:conTypeId IS NULL OR cons.conTypeId = :conTypeId )  " +
            " AND (:conName IS NULL OR flow.conName LIKE %:conName%)  " +
            "WHERE " +
            " flow.shopId = :shopId  " +
            " AND flow.createTime > :startTime  " +
            " AND flow.createTime < :endTime  ")
    TbProductStockCountVo consStockCount(Integer shopId, Integer consId, String conName,
                                         Integer conTypeId, Date startTime, Date endTime);

}
