package com.erp.report.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.erp.report.entity.OrderEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * 订单统计Repository
 */
@Mapper
public interface OrderRepository extends BaseMapper<OrderEntity> {

    /**
     * 按平台分组统计订单
     */
    @Select("""
        SELECT 
            platform,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount,
            SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        GROUP BY platform
        ORDER BY orderCount ${trend}
        """)
    List<Map<String, Object>> selectGroupByPlatform(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate,
            @Param("trend") String trend);

    /**
     * 按店铺分组统计订单
     */
    @Select("""
        SELECT 
            shop_id,
            shop_name,
            platform,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount,
            SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        GROUP BY shop_id, shop_name, platform
        ORDER BY orderCount ${trend}
        """ )
    List<Map<String, Object>> selectGroupByShop(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate,
            @Param("trend") String trend);

    /**
     * 按日期分组统计订单
     */
    @Select("""
        SELECT 
            DATE_FORMAT(payment_time, :dateFormat) as dateKey,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        GROUP BY DATE_FORMAT(payment_time, :dateFormat)
        ORDER BY dateKey ${trend}
        """ )
    List<Map<String, Object>> selectGroupByDate(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate,
            @Param("dateFormat") String dateFormat,
            @Param("trend") String trend);

    /**
     * 销售汇总统计
     */
    @Select("""
        SELECT 
            COUNT(*) as totalOrders,
            COALESCE(SUM(total_amount), 0) as totalSales,
            COALESCE(AVG(total_amount), 0) as avgOrderAmount,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        """ )
    Map<String, Object> selectSalesSummary(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 按日期统计销售收入/订单数
     */
    @Select("""
        SELECT 
            DATE_FORMAT(payment_time, :dateFormat) as dateKey,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as salesAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(AVG(total_amount), 0) as avgOrderAmount,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        GROUP BY DATE_FORMAT(payment_time, :dateFormat)
        ORDER BY dateKey ${trend}
        """ )
    List<Map<String, Object>> selectSalesByDate(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate,
            @Param("dateFormat") String dateFormat,
            @Param("trend") String trend);

    /**
     * 资金报表 - 订单收入汇总
     */
    @Select("""
        SELECT 
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalIncome,
            COALESCE(SUM(goods_amount), 0) as goodsIncome,
            COALESCE(SUM(freight), 0) as freightIncome
        FROM orders
        WHERE deleted_at IS NULL
        AND order_status = 'completed'
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        """ )
    Map<String, Object> selectIncomeSummary(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 资金报表 - 按日期统计收入
     */
    @Select("""
        SELECT 
            DATE_FORMAT(payment_time, '%Y-%m-%d') as dateKey,
            COALESCE(SUM(total_amount), 0) as income,
            COUNT(*) as orderCount
        FROM orders
        WHERE deleted_at IS NULL
        AND order_status = 'completed'
        AND payment_time IS NOT NULL
        AND (:platform IS NULL OR platform = :platform)
        AND (:shopId IS NULL OR shop_id = :shopId)
        AND (:startDate IS NULL OR payment_time >= :startDate)
        AND (:endDate IS NULL OR payment_time <= :endDate)
        GROUP BY DATE_FORMAT(payment_time, '%Y-%m-%d')
        ORDER BY dateKey ${trend}
        """ )
    List<Map<String, Object>> selectIncomeByDate(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate,
            @Param("trend") String trend);
}
