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;

@Mapper
public interface MerchantSettlementStatisticsMapper extends BaseMapper<BenefitOrder> {

    @Select("""
            <script>
            select base.period_key as periodKey,
                   base.merchant_id as merchantId,
                   base.merchant_name as merchantName,
                   base.merchant_type as merchantType,
                   count(*) as orderCount,
                   coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
                   coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
                   coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
                   coalesce(sum(base.order_amount), 0) as originalAmountCents,
                   coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
                   coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
                   min(base.business_time) as periodStartTime,
                   max(base.business_time) as periodEndTime
            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,
                       coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
                       o.merchant_id,
                       m.merchant_name,
                       m.merchant_type,
                       coalesce(o.order_amount, 0) as order_amount,
                       coalesce(o.pay_amount, 0) as pay_amount,
                       coalesce(o.coupon_amount, 0) as coupon_amount,
                       (
                           case
                               when coalesce(o.subsidy_amount, 0) > 0
                                    and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                                   then coalesce(o.subsidy_amount, 0)
                               else 0
                           end
                           +
                           case
                               when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                                   then coalesce(o.coupon_amount, 0)
                               else 0
                           end
                       ) as government_subsidy_amount
                from benefit_order o
                join merchant m on m.id = o.merchant_id and m.deleted = 0
                left join merchant_product p on p.id = o.product_id and p.deleted = 0
                left join merchant_coupon c on c.id = o.coupon_id and c.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 = #{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>
            ) base
            group by base.period_key, base.merchant_id, base.merchant_name, base.merchant_type
            order by base.period_key desc, base.merchant_id desc
            limit #{size} offset #{offset}
            </script>
            """)
    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 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 = #{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>
                group by period_key, o.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);

    @Select("""
            <script>
            select base.merchant_id as merchantId,
                   max(base.merchant_name) as merchantName,
                   max(base.merchant_type) as merchantType,
                   count(*) as orderCount,
                   coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
                   coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
                   coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
                   coalesce(sum(base.order_amount), 0) as originalAmountCents,
                   coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
                   coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
                   min(base.business_time) as startTime,
                   max(base.business_time) as endTime
            from (
                select coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
                       o.merchant_id,
                       m.merchant_name,
                       m.merchant_type,
                       coalesce(o.order_amount, 0) as order_amount,
                       coalesce(o.pay_amount, 0) as pay_amount,
                       coalesce(o.coupon_amount, 0) as coupon_amount,
                       (
                           case
                               when coalesce(o.subsidy_amount, 0) > 0
                                    and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                                   then coalesce(o.subsidy_amount, 0)
                               else 0
                           end
                           +
                           case
                               when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                                   then coalesce(o.coupon_amount, 0)
                               else 0
                           end
                       ) as government_subsidy_amount
                from benefit_order o
                join merchant m on m.id = o.merchant_id and m.deleted = 0
                left join merchant_product p on p.id = o.product_id and p.deleted = 0
                left join merchant_coupon c on c.id = o.coupon_id and c.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 = #{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>
            ) base
            group by base.merchant_id
            order by settlementAmountCents desc, base.merchant_id desc
            </script>
            """)
    List<Map<String, Object>> selectMerchantOverviews(@Param("merchantId") Long merchantId,
                                                      @Param("merchantType") String merchantType,
                                                      @Param("startTime") LocalDateTime startTime,
                                                      @Param("endTime") LocalDateTime endTime);
    @Select("""
            <script>
            select o.id as orderId,
                   o.order_no as orderNo,
                   o.order_type as orderType,
                   o.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   o.store_id as storeId,
                   s.store_name as storeName,
                   o.talent_id as talentId,
                   t.name as talentName,
                   t.mobile as talentMobile,
                   tc.card_no as cardNo,
                   tc.card_level as cardLevel,
                   coalesce(o.product_name_snapshot, p.product_name, '') as productName,
                   coalesce(o.order_amount, 0) as originalAmountCents,
                   coalesce(o.pay_amount, 0) as transactionAmountCents,
                   (
                       case
                           when coalesce(o.subsidy_amount, 0) > 0
                                and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                               then coalesce(o.subsidy_amount, 0)
                           else 0
                       end
                       +
                       case
                           when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                               then coalesce(o.coupon_amount, 0)
                           else 0
                       end
                   ) as subsidyAmountCents,
                   coalesce(o.pay_amount, 0)
                   +
                   (
                       case
                           when coalesce(o.subsidy_amount, 0) > 0
                                and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                               then coalesce(o.subsidy_amount, 0)
                           else 0
                       end
                       +
                       case
                           when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                               then coalesce(o.coupon_amount, 0)
                           else 0
                       end
                   ) as settlementAmountCents,
                   coalesce(o.coupon_amount, 0) as couponAmountCents,
                   o.order_status as orderStatus,
                   o.verify_status as verifyStatus,
                   o.created_at as createdAt,
                   o.updated_at as paidAt,
                   v.verify_time as verifyTime,
                   v.verify_operator as verifyOperator
            from benefit_order o
            join merchant m on m.id = o.merchant_id and m.deleted = 0
            left join merchant_store s on s.id = o.store_id and s.deleted = 0
            left join talent_user t on t.id = o.talent_id and t.deleted = 0
            left join talent_card tc on tc.id = o.card_id and tc.deleted = 0
            left join merchant_product p on p.id = o.product_id and p.deleted = 0
            left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
            left join (
                select order_id, max(verify_time) as verify_time, max(staff_id) as verify_operator
                from verify_record
                where status = 1
                group by order_id
            ) v on v.order_id = o.id
            where o.deleted = 0
              and o.merchant_id = #{merchantId}
              <if test="orderStatus != null">and o.order_status = #{orderStatus}</if>
              <if test="verifyStatus != null">and o.verify_status = #{verifyStatus}</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>
            order by coalesce(v.verify_time, o.updated_at, o.created_at) desc, o.id desc
            limit #{limit}
            </script>
            """)
    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);
    @Select("""
            select
                coalesce((
                    select sum(o.pay_amount)
                    from benefit_order o
                    where o.deleted = 0
                      and o.order_status = 1
                      and o.merchant_id = #{merchantId}
                ), 0) as transactionAmountCents,
                coalesce((
                    select count(*)
                    from verify_record r
                    where r.status = 1
                      and r.merchant_id = #{merchantId}
                      and r.verify_time >= #{todayStart}
                      and r.verify_time < #{todayEnd}
                ), 0) as todayVerificationCount,
                coalesce((
                    select count(*)
                    from verify_record r
                    where r.status = 1
                      and r.merchant_id = #{merchantId}
                      and r.verify_time >= #{weekStart}
                      and r.verify_time < #{weekEnd}
                ), 0) as weekVerificationCount,
                coalesce((
                    select sum(
                        case
                            when coalesce(o.subsidy_amount, 0) > 0
                                 and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                                then coalesce(o.subsidy_amount, 0)
                            else 0
                        end
                        +
                        case
                            when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                                then coalesce(o.coupon_amount, 0)
                            else 0
                        end
                    )
                    from benefit_order o
                    left join merchant_product p on p.id = o.product_id and p.deleted = 0
                    left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
                    where o.deleted = 0
                      and o.order_status = 1
                      and o.merchant_id = #{merchantId}
                ), 0) as subsidyAmountCents
            """)
    Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
                                             @Param("todayStart") LocalDateTime todayStart,
                                             @Param("todayEnd") LocalDateTime todayEnd,
                                             @Param("weekStart") LocalDateTime weekStart,
                                             @Param("weekEnd") LocalDateTime weekEnd);
}