package com.talentcard.server.mapper;

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

import java.util.Map;

/** 后台工作台统计查询，仅负责读取统计数据。 */
@Mapper
public interface AdminWorkbenchStatisticsMapper extends BaseMapper<BenefitOrder> {

    @Select("""
            select
              coalesce(sum(case
                when coalesce(o.subsidy_amount, 0) > 0
                     and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
                  then o.subsidy_amount else 0 end
                + case when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
                  then o.coupon_amount else 0 end), 0) as subsidyAmountCents,
              (select count(*) from verify_record v
                 where v.status = 1
                   and v.verify_time >= date_format(current_date, '%Y-%m-01')
                   and v.verify_time < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
              (select count(*) from talent_user u where u.deleted = 0) as talentCount,
              (select count(*) from benefit_order po where po.deleted = 0 and po.order_status = 0) as pendingOrderCount,
              (select count(*) from merchant_product pp where pp.deleted = 0 and pp.audit_status = 1) as pendingProductCount,
              (select count(*) from service_application sa where sa.deleted = 0 and sa.status in (0, 1)) as pendingApplicationCount
            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.verify_status = 1
            """)
    Map<String, Object> selectSummary();

    @Select("""
            select count(*) from benefit_order o
            where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
              and o.created_at >= date_format(current_date, '%Y-%m-01')
              and o.created_at < date_add(last_day(current_date), interval 1 day)
              and (o.subsidy_amount > 0 or o.coupon_amount > 0)
            """)
    Long countMonthlySubsidyOrders();
}