/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package cn.ysk.cashier.repository.order;

import cn.ysk.cashier.pojo.order.TbOrderInfo;
import cn.ysk.cashier.vo.ShopTableSaleInfoVo;
import cn.ysk.cashier.vo.ShopTableVo;
import cn.ysk.cashier.vo.TbOrderPayCountByDayVo;
import cn.ysk.cashier.vo.TbOrderPayCountVo;
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.Query;

import javax.persistence.Tuple;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
* @website https://eladmin.vip
* @author lyf
* @date 2024-03-02
**/
public interface TbOrderInfoRepository extends JpaRepository<TbOrderInfo, Integer>, JpaSpecificationExecutor<TbOrderInfo> {
    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountVo(info.payType, SUM(info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE 1=1 and (:tableName is null or info.tableName=:tableName ) and info.shopId = :shopId " +
            "AND info.createdAt>:start AND info.createdAt<:end " +
            "AND ((info.status = 'closed') OR (info.status = 'refund' AND info.orderType != 'return')) " +
            "GROUP BY info.payType")
    List<TbOrderPayCountVo> queryTbOrderPayCount(@Param("shopId")String shopId,@Param("tableName") String tableName ,@Param("start") Long start, @Param("end") Long end);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountVo('refund', SUM(info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE 1=1 and (:tableName is null or info.tableName=:tableName ) and info.shopId = :shopId " +
            "AND info.status = 'refund' AND info.orderType = 'return' " +
            "AND info.createdAt>:start AND info.createdAt<:end")
    TbOrderPayCountVo queryTbOrderRefund(@Param("shopId")String shopId, @Param("tableName") String tableName ,@Param("start") Long start, @Param("end") Long end);

    @Query(value = "SELECT COUNT(1) ,pay_type AS payType FROM tb_order_info Where shop_id = :shopId AND  " +
            " created_at BETWEEN :startTime AND :endTime AND status='closed'AND order_type <>'return' GROUP BY pay_type", nativeQuery = true)
    List<Object[]> countByShopId(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime);

    @Query("SELECT info FROM TbOrderInfo info WHERE info.source = :source AND info.shopId=:shopId")
    List<TbOrderInfo> selTbOrdersBysource(@Param("source") Integer source, @Param("shopId") String shopId);


    @Query("SELECT count(1) FROM TbOrderInfo WHERE source = :source AND shopId=:shopId")
    int isRefund(@Param("source") Integer source, @Param("shopId") String shopId);

    /**
     * @param shopId
     * @param startTime
     * @param endTime
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountByDayVo(" +
            "info.tradeDay," +
            "SUM( CASE WHEN info.payType = 'scanCode' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'deposit' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'wx_lite' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'cash' THEN info.orderAmount ELSE 0 END )," +
            "SUM( info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE info.shopId = :shopId " +
            "AND info.createdAt > :startTime AND info.createdAt < :endTime " +
            "AND ((info.status = 'closed') OR ( info.status ='refund' AND info.orderType != 'return' )) " +
            "GROUP BY info.tradeDay " +
            "ORDER BY info.tradeDay DESC")
    Page<TbOrderPayCountByDayVo> queryTbOrderPayCountByDay(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime, Pageable pageable);

    // 查询退款总金额
    @Query("SELECT SUM(info.orderAmount) FROM TbOrderInfo info " +
            "WHERE info.shopId = :shopId AND info.status = 'refund' " +
            "AND info.orderType = 'return' " +
            "AND info.tradeDay = :tradeDay")
    BigDecimal queryRefundOrderAmountByTradeDay(@Param("shopId") String shopId, @Param("tradeDay") String tradeDay);

    @Query(value = "SELECT " +
            "row_number() over (ORDER BY SUM(num) DESC) AS productId," +
            "product_name," +
            "SUM( CASE WHEN status = 'closed' THEN num ELSE 0 END ) as salesNum," +
            "SUM( price ) " +
            "FROM tb_order_detail  " +
            "WHERE shop_id = :shopId " +
            "AND create_time > :startTime AND create_time < :endTime " +
            "AND ((status = 'closed') OR (status ='refund')) " +
            "GROUP BY product_id,product_sku_id " +
            "ORDER BY salesNum DESC " +
            "Limit :currentPage, :currentSize", nativeQuery = true)
    List<Object[]> queryTbOrderPayCountByDayExt(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime,
                                                @Param("currentPage") Integer currentPage, @Param("currentSize") Integer currentSize);


    @Query(value = "SELECT ifnull( sum( order_amount ), 0 ) AS amount,trade_day as tradeDay " +
            "FROM tb_order_info WHERE shop_id = :shopId AND ((status = 'closed') OR ( status ='refund' AND order_type != 'return' ))" +
            "AND trade_day BETWEEN :startTime AND :endTime " +
            "GROUP BY shop_id,trade_day", nativeQuery = true)
    List<Object[]> queryTbOrderPaySumByDay(@Param("shopId") String shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "SELECT ifnull( sum( order_amount ), 0 )" +
            "FROM tb_order_info WHERE shop_id = :shopId AND ((status = 'closed') OR ( status ='refund' AND order_type != 'return' ))" +
            "AND created_at > :startTime AND created_at < :endTime ", nativeQuery = true)
    Tuple queryPaySumByDayAll(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime);


    @Query("select count(1),sum (info.payAmount) FROM TbOrderInfo info WHERE info.shopId = :shopId AND info.status = 'closed' ")
    Tuple countByShopId(@Param("shopId") String shopId);


    @Query(value = "select count(1), trade_day FROM tb_order_info WHERE shop_id = :shopId " +
            "AND ((status = 'closed') OR ( status ='refund' AND order_type != 'return' ))" +
            "AND trade_day BETWEEN :startTime AND :endTime " +
            "GROUP BY shop_id,trade_day", nativeQuery = true)
    List<Object[]> sumByDateOrderNum(@Param("shopId") String shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "select count(1), sum(pay_amount) FROM tb_order_info WHERE shop_id = :shopId AND status = 'closed' " +
            "AND trade_day BETWEEN :startTime AND :endTime ", nativeQuery = true)
    Tuple sumByShopIdToday(@Param("shopId") String shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountByDayVo(" +
            "info.tradeDay," +
            "SUM( CASE WHEN info.payType = 'scanCode' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'deposit' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'wx_lite' THEN info.orderAmount ELSE 0 END )," +
            "SUM( CASE WHEN info.payType = 'cash' THEN info.orderAmount ELSE 0 END )," +
            "SUM( info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE info.shopId = :shopId " +
            "AND info.createdAt > :startTime AND info.createdAt < :endTime " +
            "AND ((info.status = 'closed') OR ( info.status ='refund' AND info.orderType != 'return' )) " +
            "GROUP BY info.tradeDay " +
            "ORDER BY info.tradeDay DESC")
    List<TbOrderPayCountByDayVo> queryTbOrderPayCountByDay(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime);

    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountVo(" +
            "'总金额'," +
            "SUM( info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE info.shopId = :shopId " +
            "AND info.createdAt > :startTime AND info.createdAt < :endTime " +
            "AND ((info.status = 'closed') OR ( info.status ='refund' AND info.orderType != 'return' )) ")
    TbOrderPayCountVo queryOrderPayCount(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime);


    @Query("SELECT new cn.ysk.cashier.vo.TbOrderPayCountVo(" +
            "'现金支付'," +
            "SUM( info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "WHERE info.shopId = :shopId " +
            "AND info.createdAt > :startTime AND info.createdAt < :endTime " +
            "AND info.payType = 'cash' " +
            "AND ((info.status = 'closed') OR ( info.status ='refund' AND info.orderType != 'return' )) ")
    TbOrderPayCountVo queryOrderPayCash(@Param("shopId") String shopId, @Param("startTime") Long startTime, @Param("endTime") Long endTime);

    @Query("SELECT new cn.ysk.cashier.vo.ShopTableVo(" +
            "table.qrcode," +
            "table.shopId," +
            "table.id," +
            "table.name," +
            "table.areaId," +
            "area.name) " +
            "FROM TbShopTable table " +
            "LEFT JOIN TbShopArea as area on table.areaId = area.id " +
            "WHERE table.shopId = :shopId " +
            "AND table.qrcode is not null " +
            "AND table.qrcode != '' ")
    List<ShopTableVo> queryShopTableVoByShopId(@Param("shopId") Integer shopId);

    @Query("SELECT new cn.ysk.cashier.vo.ShopTableSaleInfoVo(" +
            "info.id," +
            "info.shopId," +
            "info.tableId," +
            "table.qrcode," +
            "table.name," +
            "table.areaId," +
            "area.name," +
            "COUNT(1)," +
            "SUM( info.orderAmount)) " +
            "FROM TbOrderInfo info " +
            "LEFT JOIN TbShopTable table ON info.tableId = table.qrcode " +
            "LEFT JOIN TbShopArea as area on table.areaId = area.id " +
            "WHERE info.shopId = :shopId " +
            "AND info.createdAt > :startTime AND info.createdAt < :endTime " +
            "AND ((info.status = 'closed') OR ( info.status ='refund' AND info.orderType != 'return' )) " +
            "AND table.qrcode is not null " +
            "AND table.qrcode != '' " +
            "GROUP BY info.tableId ")
    List<ShopTableSaleInfoVo> queryShopTableSaleInfo(@Param("shopId") String shopId,
                                               @Param("startTime") Long startTime, @Param("endTime") Long endTime);



    @Query(value = "select count(*) from tb_order_info where user_id=:userId and shop_id=:shopId and `status` in ('closed','refund')",nativeQuery = true)
    Integer countByUserIdAndStatusAndShopId(String userId,String shopId);
}
