package cn.ysk.cashier.repository.order;

import cn.ysk.cashier.dto.product.StockCountDTO;
import cn.ysk.cashier.pojo.order.TbFullOrderDetail;
import cn.ysk.cashier.pojo.order.TbOrderDetail;
import cn.ysk.cashier.vo.TbOrderPayCountVo;
import cn.ysk.cashier.vo.TbOrderSaleVO;
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 javax.persistence.Tuple;
import java.sql.Time;
import java.sql.Timestamp;
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.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(value = "SELECT pro.shop_id as shop_id, pro.id as pro_id, info.product_name as pro_name, info.product_sku_id as sku_id, pro.is_stock as 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 " +
            "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 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, " +
            "info.product_sku_id ",nativeQuery = true)
    List<StockCountDTO> countStock(@Param("startTime") String startTime, @Param("endTime") String 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), " +
            "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(value = "SELECT " +
            "new cn.ysk.cashier.vo.TbOrderSaleVO(oi.orderNo, od.num, od.price, od.status,od.productSkuId)\n" +
            "FROM\n" +
            "TbOrderInfo oi\n" +
            "LEFT JOIN TbOrderDetail od ON oi.id = od.orderId \n" +
            "WHERE\n" +
            "od.shopId = :shopId \n" +
            "AND ( od.status = 'closed' OR od.status = 'refund' ) \n" +
            "AND od.createTime > :startTime \n" +
            "AND od.createTime < :endTime \n" +
            "AND (:productId is null or od.productId = :productId)\n" +
            "AND (:productSkuId is null or od.productSkuId = :productSkuId)")
    List<TbOrderSaleVO> querySaleOrderInfo(@Param("startTime") Timestamp startTime, @Param("endTime") Timestamp endTime, @Param("productId") Integer productId, @Param("productSkuId") Integer productSkuId, @Param("shopId") Integer shopId);
    @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(CAST(SUM(CASE WHEN orders.orderType!='return' THEN info.num ELSE 0 END) as long),0), " +
            "COALESCE(CAST(SUM(CASE WHEN orders.orderType='return' THEN info.num ELSE 0 END) as long),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("SELECT new cn.ysk.cashier.vo.TbOrderPayCountVo(" +
            "info.status," +
            "SUM( info.num )) " +
            "FROM TbOrderDetail info " +
            "WHERE info.shopId = :shopId " +
            "AND info.createTime > :startTime AND info.createTime < :endTime " +
            "AND ((info.status = 'closed') OR ( info.status ='refund')) " )
    TbOrderPayCountVo queryTbOrderSalesCountExt(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query(value = "select sum(num),count(1) from tb_order_detail where shop_id = :shopId " +
            "AND ((status = 'closed') OR (status ='refund')) " +
            "AND create_time > :startTime AND create_time < :endTime",nativeQuery = true)
    Tuple sumByShopId(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "select sum(num),count(1),DATE(create_time) from tb_order_detail where shop_id = :shopId AND status = 'closed' " +
            "AND create_time > :startTime AND create_time < :endTime " +
            "GROUP BY DATE(create_time)",nativeQuery = true)
    List<Object[]> sumByShopIdGroup(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "SELECT COUNT( 0 ) FROM ( SELECT product_id FROM tb_order_detail WHERE shop_id = :shopId " +
            "AND create_time > :startTime AND create_time < :endTime AND (( STATUS = 'closed' ) \n" +
            "\t\tOR ( STATUS = 'refund' )) \n" +
            "\tGROUP BY\n" +
            "\t\tproduct_id,\n" +
            "\t\tproduct_sku_id \n" +
            "\t) AS total ", nativeQuery = true)
    Tuple searchCount(@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 (info.status = 'closed' OR info.status = 'refund') " +
            "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);

}
