package cn.ysk.cashier.repository.order;

import cn.ysk.cashier.pojo.order.TbOrderDetail;
import cn.ysk.cashier.vo.CountStockByDayVo;
import cn.ysk.cashier.vo.TbOrderSalesCountByDayV2Vo;
import cn.ysk.cashier.vo.TbOrderSalesCountByDayVo;
import cn.ysk.cashier.vo.TbOrderSalesCountByTable;
import org.apache.ibatis.annotations.Param;
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 java.util.Date;
import java.util.List;

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


    @Query(value = "update tb_order_detail set status = 'closed' where order_id = :orderId and  status='unpaid'",nativeQuery = true)
    @Modifying
    void updateToClosed(@Param("orderId") Integer orderId);

    @Query("SELECT cart FROM TbOrderDetail cart WHERE cart.orderId = :orderId")
    List<TbOrderDetail> searchDetailByOrderId(@Param("orderId")Integer orderId);


    @Query("SELECT cart FROM TbOrderDetail cart WHERE cart.orderId in :ids")
    List<TbOrderDetail> searchDetailByOrderIds(@Param("ids")List<Integer> ids);

    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayV2Vo(" +
            "info.productName,pro.id, cate.name,pro.typeEnum, " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END)) " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND (:cateId IS NULL OR pro.categoryId = :cateId) " +
            "AND (:proName IS NULL OR info.productName LIKE %:proName%) " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') " +
            "GROUP BY info.productId " +
            "ORDER BY salesNum DESC")
    Page<TbOrderSalesCountByDayV2Vo> queryTbOrderSalesCountByProduct(@Param("shopId") Integer shopId,@Param("cateId")String cateId,@Param("proName")String proName, @Param("startTime") Date startTime, @Param("endTime") Date endTime, Pageable pageable);

    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayV2Vo(" +
            "info.productName,pro.id, cate.name,pro.typeEnum, " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END)) " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND (:cateId IS NULL OR pro.categoryId = :cateId) " +
            "AND (:proName IS NULL OR info.productName LIKE %:proName%) " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') " +
            "GROUP BY info.productId " +
            "ORDER BY salesNum DESC")
    List<TbOrderSalesCountByDayV2Vo> queryTbOrderSalesCountByProduct(@Param("shopId") Integer shopId,@Param("cateId")String cateId,@Param("proName")String proName, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayV2Vo(" +
            "info.productSkuName,pro.id, '',''," +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END)) " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "INNER JOIN TbProduct pro ON info.productId = pro.id " +
            " AND pro.typeEnum= 'sku' " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') " +
            "GROUP BY info.productId, info.productSkuId " +
            "ORDER BY salesNum DESC")
    List<TbOrderSalesCountByDayV2Vo> queryTbOrderSalesCountBySku(@Param("shopId") Integer shopId
            , @Param("startTime") Date startTime, @Param("endTime") Date endTime);
    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayVo(" +
            "info.productName, info.productSkuName, cate.name, unit.name,info.price," +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(info.num), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END)) " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "LEFT JOIN TbShopUnit unit ON unit.id = pro.unitId " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND (:cateId IS NULL OR pro.categoryId = :cateId) " +
            "AND (:proName IS NULL OR info.productName LIKE %:proName%) " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') " +
            "GROUP BY info.productId, info.productSkuId " +
            "ORDER BY salesNum DESC")
    Page<TbOrderSalesCountByDayVo> queryTbOrderSalesCountByDay(@Param("shopId") Integer shopId,@Param("cateId")String cateId,@Param("proName")String proName, @Param("startTime") Date startTime, @Param("endTime") Date endTime, Pageable pageable);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayVo(" +
            "info.productName, info.productSkuName, cate.name, unit.name,info.price," +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(info.num), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END), " +
            "info.productId, " +
            "info.productSkuId )  " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "LEFT JOIN TbShopUnit unit ON unit.id = pro.unitId " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND (:cateId IS NULL OR pro.categoryId = :cateId) " +
            "AND (:proName IS NULL OR info.productName LIKE %:proName%) " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') " +
            "GROUP BY info.productId, info.productSkuId " +
            "ORDER BY salesNum DESC")
    List<TbOrderSalesCountByDayVo> queryTbOrderSalesCountByDay(@Param("shopId") Integer shopId,@Param("cateId")String cateId,@Param("proName")String proName, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query("SELECT new cn.ysk.cashier.vo.CountStockByDayVo(" +
            " (SUM( CASE WHEN orders.orderType != 'return' THEN info.priceAmount ELSE 0 END ) - SUM( CASE WHEN orders.orderType = 'return' THEN info.priceAmount ELSE 0 END )) as sale_amount," +
            " (SUM( CASE WHEN orders.orderType != 'return' THEN info.num ELSE 0 END )-SUM( CASE WHEN orders.orderType = 'return' THEN info.num ELSE 0 END )) as sale_num," +
            " orders.tradeDay)" +
            "FROM" +
            " TbOrderInfo orders" +
            " LEFT JOIN TbOrderDetail info ON orders.id = info.orderId" +
            " WHERE " +
            " info.shopId = :shopId" +
            " AND info.createTime > :startTime" +
            " AND info.createTime < :endTime" +
            " AND ( info.status = 'closed' OR info.status = 'refund' ) " +
            "GROUP BY orders.tradeDay " +
            "ORDER BY orders.tradeDay")
    List<CountStockByDayVo> countStockByDay(@Param("shopId") Integer shopId,@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayVo(" +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END)," +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(info.num), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END))  " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "WHERE info.shopId = :shopId " +
            "AND (:cateId IS NULL OR pro.categoryId = :cateId) " +
            "AND (:proName IS NULL OR info.productName LIKE %:proName%) " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') ")
    TbOrderSalesCountByDayVo queryTbOrderSalesCountByDaysummaryCount(@Param("shopId") Integer shopId,@Param("cateId")String cateId,@Param("proName")String proName, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByDayVo(" +
            "COALESCE(SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END),0), " +
            "COALESCE(SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END),0))" +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "WHERE info.shopId = :shopId " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (info.status = 'closed' OR info.status = 'refund') ")
    TbOrderSalesCountByDayVo queryTbOrderSalesCount(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "SELECT d.order_id \n" +
            "    FROM tb_order_detail d \n" +
            "    WHERE d.product_name LIKE %:productName% \n" +
            "    AND d.shop_id = :shopId \n" +
            "    AND d.create_time BETWEEN :startTime AND :endTime \n" +
            "    GROUP BY d.order_id", nativeQuery = true)
    List<Integer> findOrderIdsByProductNameLike(@Param("productName") String productName, @Param("shop_id") String shopId,
                                              @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderSalesCountByTable(" +
            "info.productName, info.productSkuName, cate.name, unit.name,info.price," +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as salesNum, " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END), " +
            "SUM(info.num), " +
            "SUM(CASE WHEN orders.orderType!='return' THEN info.priceAmount ELSE 0 END), " +
            "SUM(CASE WHEN orders.orderType='return' THEN info.priceAmount ELSE 0 END), " +
            "info.productId, " +
            "info.productSkuId," +
            "orders.tableId )  " +
            "FROM TbOrderInfo orders " +
            "LEFT JOIN TbOrderDetail info on orders.id=info.orderId " +
            "LEFT JOIN TbProduct pro ON info.productId = pro.id " +
            "LEFT JOIN TbShopUnit unit ON unit.id = pro.unitId " +
            "LEFT JOIN TbShopCategory cate ON cate.id = pro.categoryId " +
            "WHERE info.shopId = :shopId " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND (orders.status = 'closed' OR orders.orderType = 'return') " +
            "GROUP BY info.productId, info.productSkuId, orders.tableId " +
            "ORDER BY salesNum DESC")
    List<TbOrderSalesCountByTable> queryTbOrderSalesCountByTable(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

}
