package cn.ysk.cashier.repository.order;

import cn.ysk.cashier.dto.product.StockCountDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://eladmin.vip
* @author lyf
* @date 2024-03-02
**/
public interface StockCountRepository extends JpaRepository<StockCountDTO, Integer> {

    @Query(value = "SELECT pro.shop_id as shop_id, pro.id as pro_id, info.product_name as pro_name, pro.is_stock," +
            " info.product_sku_name as sku_name, unit.NAME as unit_name ," +
            "SUM( CASE WHEN orders.order_type != 'return' THEN info.num ELSE 0 END )- SUM( CASE WHEN orders.order_type = 'return' THEN info.num ELSE 0 END ) as stock_count," +
            "CASE WHEN pro.is_distribute = '0' THEN sku.stock_number ELSE pro.stock_number END AS stock_number " +
            "FROM " +
            "tb_order_info orders " +
            "LEFT JOIN tb_order_detail info ON orders.id = info.order_id " +
            "LEFT JOIN tb_product pro ON info.product_id = pro.id " +
            "LEFT JOIN (SELECT product_id,sum(stock_number) as stock_number from tb_product_sku GROUP BY product_id) sku ON info.product_id = sku.product_id " +
            "LEFT JOIN tb_shop_unit unit ON unit.id = pro.unit_id " +
            "WHERE " +
            "info.create_time > :startTime " +
            "AND info.create_time < :endTime " +
            "AND ( info.STATUS = 'closed' OR info.STATUS = 'refund' )  " +
            "GROUP BY " +
            "info.product_id ",nativeQuery = true)
    List<StockCountDTO> countStock( @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Query(value = "SELECT\n" +
            "info.shop_id AS shop_id,\n" +
            "info.product_id AS pro_id,\n" +
            "info.product_name AS pro_name,\n" +
            "info.product_sku_id AS sku_id,\n" +
            "pro.is_stock,\n" +
            "info.product_sku_name AS sku_name,\n" +
            "unit.NAME AS unit_name,\n" +
            "SUM( CASE WHEN orders.order_type != 'return' THEN info.num ELSE 0 END ) - SUM( CASE WHEN orders.order_type = 'return' THEN info.num ELSE 0 END ) AS stock_count,\n" +
            "CASE\n" +
            "\n" +
            "WHEN pro.is_distribute = 0 THEN\n" +
            "sku.stock_number ELSE pro.stock_number \n" +
            "END AS stock_number, ifnull(sku.spec_snap, '') spec_snap\n" +
            "FROM\n" +
            "tb_order_detail info\n" +
            "LEFT JOIN tb_order_info orders ON orders.id = info.order_id\n" +
            "LEFT JOIN tb_product pro ON info.product_id = pro.id\n" +
            "LEFT JOIN tb_product_sku sku ON info.product_sku_id = sku.id\n" +
            "LEFT JOIN tb_shop_unit unit ON unit.id = pro.unit_id \n" +
            "WHERE\n" +
            "orders.id = :orderId \n" +
            "AND ( info.STATUS = 'closed' OR info.STATUS = 'refund' ) \n" +
            "GROUP BY\n" +
            "info.product_id,\n" +
            "info.product_sku_id;",nativeQuery = true)
    List<StockCountDTO> countStockById(Integer orderId);
}
