package com.czg.service.order.mapper;

import com.czg.order.entity.ShopOrderStatistic;
import com.czg.order.vo.CostLineChartVO;
import com.czg.order.vo.ProductCostAmountVO;
import com.czg.order.vo.ProfitRateVO;
import com.czg.order.vo.TotalVo;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 店铺订单统计报表 映射层。
 *
 * @author ww
 * @since 2025-11-20
 */
public interface ShopOrderStatisticMapper extends BaseMapper<ShopOrderStatistic> {


    @Select("SELECT" +
            "    SUM(pay_amount) AS payAmount," +
            "    SUM(online_pay_amount) AS onlinePayAmount," +
            "    SUM(member_pay_amount) AS memberPayAmount," +
            "    SUM(member_pay_count) AS memberPayCount," +
            "    SUM(refund_amount) AS refundAmount," +
            "    SUM(online_refund_amount) AS onlineRefundAmount," +
            "    SUM(recharge_amount) AS rechargeAmount," +
            "    SUM(online_recharge_amount) AS onlineRechargeAmount," +
            "    SUM(give_amount) AS giveAmount," +
            "    SUM(recharge_refund_amount) AS rechargeRefundAmount," +
            "    SUM(online_recharge_refund_amount) AS onlineRechargeRefundAmount," +
            "    SUM(cash_recharge_refund_amount) AS cashRechargeRefundAmount," +
            "    SUM(cash_recharge_amount) AS cashRechargeAmount," +
            "    SUM(member_refund_amount) AS memberRefundAmount," +
            "    SUM(cash_refund_amount) AS cashRefundAmount," +
            "    SUM(new_member_count) AS newMemberCount," +
            "    SUM(customer_count) AS customerCount," +
            "    SUM(order_count) AS orderCount," +
            "    SUM(table_count) AS tableCount," +
            "    SUM(avg_pay_amount) AS avgPayAmount," +
            "    SUM(turnover_rate) AS turnoverRate," +
            "    SUM(profit_amount) AS profitAmount," +
            "    SUM(product_cost_amount) AS productCostAmount," +
            "    SUM(profit_rate) AS profitRate," +
            "    SUM(net_profit_amount) AS netProfitAmount," +
            "    SUM(net_profit_rate) AS netProfitRate," +
            "    SUM(discount_amount) AS discountAmount," +
            "    SUM(discount_count) AS discountCount," +
            "    SUM(new_customer_discount_amount) AS newCustomerDiscountAmount," +
            "    SUM(full_discount_amount) AS fullDiscountAmount," +
            "    SUM(coupon_discount_amount) AS couponDiscountAmount," +
            "    SUM(point_discount_amount) AS pointDiscountAmount," +
            "    SUM(back_discount_amount) AS backDiscountAmount," +
            "    SUM(member_discount_amount) AS memberDiscountAmount," +
            "    SUM(order_price_discount_amount) AS orderPriceDiscountAmount," +
            "    SUM(cash_pay_amount) AS cashPayAmount," +
            "    SUM(wechat_pay_amount) AS wechatPayAmount," +
            "    SUM(alipay_pay_amount) AS alipayPayAmount," +
            "    SUM(back_scan_pay_amount) AS backScanPayAmount," +
            "    SUM(main_scan_pay_amount) AS mainScanPayAmount," +
            "    SUM(credit_pay_amount) AS creditPayAmount " +
            "FROM" +
            "    tb_shop_order_statistic " +
            "WHERE " +
            "    shop_id = #{shopId} " +
            "    AND statistic_date BETWEEN #{start} AND #{end} ")
    ShopOrderStatistic countStatistic(Long shopId, LocalDate start, LocalDate end);
    //---------------------------------------------金额统计---------------------------------------------------

    /**
     * 订单金额统计 当日实时数据
     */
    @Select("SELECT" +
            " SUM(origin_amount) as orderAmount, " +
            " SUM(order_amount) as actualAmount, " +
            " SUM(origin_amount - order_amount) as discountAmount, " +
            " #{tradeDay} as tradeDay" +
            " FROM" +
            " tb_order_info " +
            " WHERE" +
            " shop_id = #{shopId} " +
            "and trade_day = #{tradeDay} " +
            "and paid_time is not null " +
            "GROUP BY trade_day")
    TotalVo getOnlineDataAmount(Long shopId, LocalDate tradeDay);

    /**
     * 订单金额统计 按日期范围查询
     */
    @Select("SELECT" +
            " SUM(origin_amount) as orderAmount, " +
            " SUM(pay_amount) as actualAmount, " +
            " SUM(origin_amount - pay_amount) as discountAmount, " +
            " statistic_date as tradeDay" +
            " FROM" +
            " tb_shop_order_statistic " +
            " WHERE" +
            " shop_id = #{shopId} " +
            "and statistic_date >= #{start} " +
            "and statistic_date <= #{end} " +
            "GROUP BY tradeDay")
    List<TotalVo> getStatDateRange(Long shopId, LocalDate start, LocalDate end);

    /**
     * 订单支付方式统计 当日实时数据
     */
    @Select("SELECT" +
            " SUM(CASE WHEN pay_type = 'main_scan' THEN pay_amount ELSE 0 END) AS mainScanPay," +
            " SUM(CASE WHEN pay_type = 'back_scan' THEN pay_amount ELSE 0 END) AS backScanPay," +
            " SUM(CASE WHEN pay_type = 'wechat_mini' THEN pay_amount ELSE 0 END) AS wechatPay," +
            " SUM(CASE WHEN pay_type = 'alipay_mini' THEN pay_amount ELSE 0 END) AS alipayPay," +
            " SUM(CASE WHEN pay_type = 'vip_pay' THEN pay_amount ELSE 0 END) AS memberPay," +
            " SUM(CASE WHEN pay_type = 'cash_pay' THEN pay_amount ELSE 0 END) AS cashPay," +
            " SUM(CASE WHEN pay_type = 'credit_pay' THEN pay_amount ELSE 0 END) AS creditPay" +
            " FROM" +
            " tb_order_info " +
            " WHERE" +
            " shop_id = #{shopId} " +
            "and trade_day = #{tradeDay} " +
            "and paid_time is not null ")
    Map<String, BigDecimal> getOnlinePayTypeDate(Long shopId, LocalDate tradeDay);

    /**
     * 订单支付方式统计 按日期范围查询
     */
    @Select("SELECT" +
            " SUM(member_pay_count) as memberPay, " +
            " SUM(cash_pay_count) as cashPay, " +
            " SUM(wechat_pay_count) as wechatPay, " +
            " SUM(alipay_pay_count) as alipayPay, " +
            " SUM(main_scan_pay_count) as mainScanPay, " +
            " SUM(back_scan_pay_count) as backScanPay, " +
            " SUM(credit_pay_count) as creditPay " +
            " FROM tb_shop_order_statistic " +
            " WHERE shop_id = #{shopId} " +
            " AND statistic_date >= #{start} " +
            " AND statistic_date <= #{end} ")
    Map<String, BigDecimal> getPayTypeDateRangeRaw(Long shopId, LocalDate start, LocalDate end);


    @Select("SELECT" +
            " profit_rate as profitRate, " +
            " net_profit_rate as netProfitRate, " +
            " statistic_date as tradeDay" +
            " FROM" +
            " tb_shop_order_statistic " +
            " WHERE" +
            " shop_id = #{shopId} " +
            "and statistic_date >= #{start} " +
            "and statistic_date <= #{end} ")
    List<ProfitRateVO> profitRateBarChart(Long shopId, LocalDate start, LocalDate end);


    @Select("SELECT" +
            " product_cost_amount as productCostAmount, " +
            " statistic_date as tradeDay" +
            " FROM" +
            " tb_shop_order_statistic " +
            " WHERE" +
            " shop_id = #{shopId} " +
            "and statistic_date >= #{start} " +
            "and statistic_date <= #{end} ")
    List<CostLineChartVO> costLineChart(Long shopId, LocalDate start, LocalDate end);


    //*********************以下为日常统计********************************************************************************


    /**
     * 统计某日支付数据 当日实时数据/昨日数据落地
     * order	订单支付
     * refund	订单退款
     * free	霸王餐
     * memberIn	会员充值
     * memberRefund	会员充值的退款
     */
    @Select("SELECT " +
            "    SUM(CASE WHEN pay_type = 'pay' and source_type = 'order' THEN amount ELSE 0 END) AS onlinePayAmount," +
            "    SUM(CASE WHEN pay_type = 'refund' and source_type = 'order' THEN amount ELSE 0 END) AS onlineRefundAmount," +
            "    SUM(CASE WHEN pay_type = 'pay' and source_type = 'free' THEN amount ELSE 0 END) AS backDiscountAmount," +
            "    SUM(CASE WHEN pay_type = 'pay' and source_type = 'memberIn' THEN amount ELSE 0 END) AS onlineRechargeAmount, " +
            "    SUM(CASE WHEN pay_type = 'refund' and source_type = 'memberIn' THEN amount ELSE 0 END) AS onlineRechargeRefundAmount " +
            "FROM" +
            "    tb_order_payment " +
            "WHERE " +
            "    pay_status = 'success' " +
            "    AND shop_id = #{shopId}" +
            "    AND source_type NOT IN ('memberPay', 'distribution', 'distributionRecharge', 'point' ,'ware')" +
            "    AND pay_type != 'refundCompensate'" +
            "    AND DATE(update_time) = #{tradeDay} ;")
    ShopOrderStatistic getOnlineStatSingleDate(Long shopId, LocalDate tradeDay);

    /**
     * 统计某日订单数据 当日实时数据/昨日数据落地
     */
    @Select("SELECT" +
            " SUM(tb_order_info.origin_amount) AS originAmount," +
            " SUM(CASE WHEN pay_type = 'main_scan' THEN pay_amount ELSE 0 END) AS mainScanPayAmount," +
            " SUM(CASE WHEN pay_type = 'back_scan' THEN pay_amount ELSE 0 END) AS backScanPayAmount," +
            " SUM(CASE WHEN pay_type = 'wechat_mini' THEN pay_amount ELSE 0 END) AS wechatPayAmount," +
            " SUM(CASE WHEN pay_type = 'alipay_mini' THEN pay_amount ELSE 0 END) AS alipayPayAmount," +
            " SUM(CASE WHEN pay_type = 'vip_pay' THEN pay_amount ELSE 0 END) AS memberPayAmount," +
            " SUM(CASE WHEN pay_type = 'cash_pay' THEN pay_amount ELSE 0 END) AS cashPayAmount," +
            " SUM(CASE WHEN pay_type = 'credit_pay' THEN pay_amount ELSE 0 END) AS creditPayAmount," +
            " SUM(CASE WHEN pay_type = 'free_pay' THEN order_amount ELSE 0 END) AS backDiscountAmount," +

            " SUM(CASE WHEN pay_type = 'main_scan' THEN pay_amount ELSE 0 END) AS mainScanPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'back_scan' THEN pay_amount ELSE 0 END) AS backScanPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'wechat_mini' THEN pay_amount ELSE 0 END) AS wechatPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'alipay_mini' THEN pay_amount ELSE 0 END) AS alipayPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'vip_pay' THEN pay_amount ELSE 0 END) AS memberPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'cash_pay' THEN pay_amount ELSE 0 END) AS cashPayAmountOriginal," +
            " SUM(CASE WHEN pay_type = 'credit_pay' THEN pay_amount ELSE 0 END) AS creditPayAmountOriginal," +
            " " +
            " SUM(CASE WHEN pay_type = 'vip_pay' THEN refund_amount ELSE 0 END) AS memberRefundAmount," +
            " SUM(CASE WHEN pay_type = 'credit_pay' THEN refund_amount ELSE 0 END) AS creditRefundAmount," +

            " SUM(CASE WHEN status = 'refund' THEN refund_amount ELSE 0 END) AS refundAllAmount," +
            " SUM(CASE WHEN status = 'refund' THEN 1 ELSE 0 END) AS refundAllNum," +
            " SUM(CASE WHEN discount_all_amount>0  THEN 1 ELSE 0 END) AS discountOrderNum," +
            " SUM(CASE WHEN discount_all_amount>0  THEN discount_all_amount ELSE 0 END) AS discountOrderAmount," +
            " " +
            " SUM(CASE WHEN pay_type = 'vip_pay' THEN 1 ELSE 0 END) as memberPayCount," +
            " SUM(CASE WHEN pay_type = 'cash_pay' THEN 1 ELSE 0 END) as cashPayCount," +
            " SUM(CASE WHEN pay_type = 'wechat_mini' THEN 1 ELSE 0 END) as wechatPayCount," +
            " SUM(CASE WHEN pay_type = 'alipay_mini' THEN 1 ELSE 0 END) as alipayPayCount," +
            " SUM(CASE WHEN pay_type = 'back_scan' THEN 1 ELSE 0 END) as backScanPayCount," +
            " SUM(CASE WHEN pay_type = 'main_scan' THEN 1 ELSE 0 END) as mainScanPayCount," +
            " SUM(CASE WHEN pay_type = 'credit_pay' THEN 1 ELSE 0 END) as creditPayCount," +
            " " +
            " IFNULL(SUM(refund_amount), 0) as refundAmount," +
            " SUM(CASE WHEN refund_amount > 0 THEN 1 END) AS refundCount," +
            " SUM(CASE WHEN refund_type = 'cash' or pay_type = 'cash_pay' THEN refund_amount ELSE 0 END) as cashRefundAmount," +
            " " +
            " IFNULL(SUM(new_customer_discount_amount), 0) as newCustomerDiscountAmount," +
            " IFNULL(SUM(discount_act_amount), 0) as fullDiscountAmount," +
            " IFNULL(SUM(product_coupon_discount_amount), 0) + IFNULL(SUM(other_coupon_discount_amount), 0) as couponDiscountAmount," +
            " IFNULL(SUM(points_discount_amount), 0) as pointDiscountAmount," +
            " IFNULL(SUM(vip_discount_amount), 0) as memberDiscountAmount," +
            " IFNULL(SUM(discount_amount), 0) as orderPriceDiscountAmount ," +
            " count(1) as orderCount," +
            " sum(CASE WHEN seat_num IS NULL OR seat_num <= 0 THEN 1 ELSE seat_num END) as customerCount " +
            "FROM" +
            " tb_order_info " +
            "WHERE" +
            " shop_id = #{shopId} " +
            "and trade_day = #{tradeDay} " +
            "and paid_time is not null")
    ShopOrderStatistic getOrderStatSingleDate(Long shopId, LocalDate tradeDay);

    /**
     * 统计待支付数据 当日实时数据/昨日数据落地
     */
    @Select("SELECT " +
            "  SUM(CASE WHEN origin_amount > 0 THEN origin_amount ELSE 0 END) AS unpaidOrderAmount,  " +
            "  SUM(CASE WHEN origin_amount > 0 THEN 1 ELSE 0 END) AS unpaidOrderNum  " +
            "FROM " +
            "  tb_order_info " +
            "WHERE " +
            "  shop_id = #{shopId} " +
            "  AND status = 'unpaid'" +
            "  AND trade_day = #{tradeDay} ;")
    ShopOrderStatistic getUnpaidOrder(Long shopId, LocalDate tradeDay);

    /**
     * 统计退菜
     */

    @Select("SELECT" +
            " sum(CASE WHEN detail.refund_num then detail.refund_num else 0 end) as refundItemNum," +
            " sum(CASE WHEN detail.return_amount>0 then 1 else 0 end) as refundItemAmount " +
            " FROM" +
            " tb_order_info `order`" +
            " INNER JOIN tb_order_detail detail ON `order`.id = detail.order_id" +
            " WHERE" +
            " `order`.shop_id = #{shopId}" +
            " AND `order`.trade_day = #{tradeDay}" +
            " AND `order`.paid_time IS NOT NULL")
    ShopOrderStatistic getRefundItem(Long shopId, LocalDate tradeDay);

    /**
     * 统计某日用户用户余额数据 当日实时数据/昨日数据落地
     */
    @Select("SELECT " +
            "  SUM(CASE WHEN biz_code = 'cashIn' THEN amount ELSE 0 END) AS cashRechargeAmount,  " +
            "  SUM(CASE WHEN biz_code = 'awardIn' THEN amount ELSE 0 END) AS giveAmount,  " +
            "  SUM(CASE WHEN biz_code = 'rechargeCashRefund' THEN ABS(amount) ELSE 0 END) AS cashRechargeRefundAmount  " +
            "FROM " +
            "  tb_shop_user_flow " +
            "WHERE " +
            "  shop_id = #{shopId} " +
            "  AND trade_day = #{tradeDay} ;")
    ShopOrderStatistic getShopUserFlowStatSingleDate(Long shopId, LocalDate tradeDay);

    /**
     * 优惠笔数 discountCount
     */
    @Select("SELECT count(*) " +
            "FROM tb_order_info WHERE shop_id = #{shopId} and trade_day = #{tradeDay} and paid_time is not null and discount_all_amount > 0")
    Long countDiscountOrder(Long shopId, LocalDate tradeDay);

    /**
     * 优惠总金额 discountAmount
     */
    @Select("SELECT sum(discount_all_amount) " +
            "FROM tb_order_info WHERE shop_id = #{shopId} and trade_day = #{tradeDay} and paid_time is not null and discount_all_amount > 0")
    BigDecimal countDiscountAmount(Long shopId, LocalDate tradeDay);


    /**
     * 统计店铺桌台数 tableCount
     */
    @Select("SELECT count(*) FROM tb_shop_table WHERE shop_id = #{shopId} and status != 'unbound'")
    Long countShopTable(Long shopId);

    /**
     * 新增会员数 newMemberCount
     */
    @Select("SELECT count(*) FROM tb_shop_user WHERE main_shop_id = #{mainShopId} and is_vip = 1 " +
            "AND join_time BETWEEN CONCAT(#{tradeDay}, ' 00:00:00') AND CONCAT(#{tradeDay}, ' 23:59:59')")
    Long countNewMember(Long mainShopId, LocalDate tradeDay);

    /**
     * 商品成本 productCostAmount
     */
    @Select("SELECT" +
            " sku.id AS skuId," +
            " sku.cost_price AS costAmount " +
            "FROM" +
            " tb_product product" +
            " LEFT JOIN tb_prod_sku sku ON product.id = sku.product_id " +
            " AND product.shop_id = sku.shop_id " +
            "WHERE" +
            " product.shop_id = #{shopId} " +
            " AND sku.cost_price > 0")
    List<ProductCostAmountVO> getSkuCostAmount(Long shopId);

    @Select("SELECT" +
            " relation.product_id as productId," +
            " sum(ROUND(relation.surplus_stock * cons.price, 2)) AS costAmount" +
            " FROM" +
            " tb_prod_cons_relation relation " +
            " INNER JOIN tb_cons_info cons on relation.cons_info_id = cons.id and relation.shop_id = cons.shop_id and price > 0" +
            " WHERE" +
            " relation.shop_id = #{shopId} " +
            " group by relation.product_id" +
            " order by relation.product_id")
    List<ProductCostAmountVO> getConsCostAmount(Long shopId);


    @Select("SELECT  " +
            "  detail.product_id as productId," +
            "  detail.sku_id as skuId," +
            "  sum(detail.num - detail.return_num) as count " +
            "FROM  " +
            "   tb_order_info `order`  " +
            "   INNER JOIN tb_order_detail detail ON `order`.id = detail.order_id AND is_temporary = 0  " +
            "WHERE  " +
            "   `order`.shop_id = #{shopId}   " +
            "   and trade_day = #{tradeDay}   " +
            "   and paid_time is not null" +
            " group by detail.product_id, detail.sku_id")
    List<ProductCostAmountVO> getOrderDetailProduct(Long shopId, LocalDate tradeDay);

}
