package com.talentcard.server.mapper;

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

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.talentcard.server.entity.BenefitOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

@Mapper
public interface MerchantSettlementStatisticsMapper extends BaseMapper<BenefitOrder> {

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectPeriodSummaries")
    List<Map<String, Object>> selectPeriodSummaries(@Param("merchantId") Long merchantId,
                                                    @Param("merchantType") String merchantType,
                                                    @Param("periodType") String periodType,
                                                    @Param("startTime") LocalDateTime startTime,
                                                    @Param("endTime") LocalDateTime endTime,
                                                    @Param("size") int size,
                                                    @Param("offset") int offset);

    @Select("""
            <script>
            select count(*)
            from (
                select base.period_key, base.merchant_id
                from (
                    select case
                               when #{periodType} = 'MONTH' then date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m')
                               else date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m-%d')
                           end as period_key,
                           o.merchant_id
                    from benefit_order o
                    join merchant m on m.id = o.merchant_id and m.deleted = 0
                    left join (
                        select order_id, max(verify_time) as verify_time
                        from verify_record
                        where status = 1
                        group by order_id
                    ) v on v.order_id = o.id
                    where o.deleted = 0
                      and o.order_status = 1
                      and o.verify_status = 1
                      <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
                      <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
                      <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &gt;= #{startTime}</if>
                      <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{endTime}</if>
                    union all
                    select case
                               when #{periodType} = 'MONTH' then date_format(r.verify_time, '%Y-%m')
                               else date_format(r.verify_time, '%Y-%m-%d')
                           end as period_key,
                           r.merchant_id
                    from verify_record r
                    join merchant m on m.id = r.merchant_id and m.deleted = 0
                    where r.status = 1
                      and r.order_id is null
                      and upper(r.verify_type) = 'HOTEL_FREE'
                      <if test="merchantId != null">and r.merchant_id = #{merchantId}</if>
                      <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
                      <if test="startTime != null">and r.verify_time &gt;= #{startTime}</if>
                      <if test="endTime != null">and r.verify_time &lt; #{endTime}</if>
                ) base
                group by base.period_key, base.merchant_id
            ) grouped
            </script>
            """)
    Long countPeriodSummaries(@Param("merchantId") Long merchantId,
                              @Param("merchantType") String merchantType,
                              @Param("periodType") String periodType,
                              @Param("startTime") LocalDateTime startTime,
                              @Param("endTime") LocalDateTime endTime);

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectMerchantOverviews")
    List<Map<String, Object>> selectMerchantOverviews(@Param("merchantId") Long merchantId,
                                                      @Param("merchantType") String merchantType,
                                                      @Param("startTime") LocalDateTime startTime,
                                                      @Param("endTime") LocalDateTime endTime);

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectSettlementExportDetails")
    List<Map<String, Object>> selectSettlementExportDetails(@Param("merchantId") Long merchantId,
                                                            @Param("orderStatus") Integer orderStatus,
                                                            @Param("verifyStatus") Integer verifyStatus,
                                                            @Param("startTime") LocalDateTime startTime,
                                                            @Param("endTime") LocalDateTime endTime,
                                                            @Param("limit") int limit);

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectDashboardCards")
    Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
                                             @Param("todayStart") LocalDateTime todayStart,
                                             @Param("todayEnd") LocalDateTime todayEnd,
                                             @Param("weekStart") LocalDateTime weekStart,
                                             @Param("weekEnd") LocalDateTime weekEnd);
}