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 org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

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

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummary")
    Map<String, Object> selectSummary();

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectCompareSummary")
    Map<String, Object> selectCompareSummary();

    @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummaryTrend")
    List<Map<String, Object>> selectSummaryTrend();

    @Select("""
            select buckets.bucket_time as time,
                   coalesce(count(v.id), 0) as `order`,
                   round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
            from (
              select 0 bucket_hour, '0:00' bucket_time union all select 2, '2:00'
              union all select 4, '4:00' union all select 6, '6:00'
              union all select 8, '8:00' union all select 10, '10:00'
              union all select 12, '12:00' union all select 14, '14:00'
              union all select 16, '16:00' union all select 18, '18:00'
              union all select 20, '20:00' union all select 22, '22:00'
            ) buckets
            left join (
              select o.id, o.pay_amount,
                     coalesce(
                       (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
                       (select max(pt.paid_at) from payment_transaction pt
                         where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                       o.updated_at
                     ) as effective_time
                from benefit_order o
               where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            ) v on v.effective_time >= current_date
              and v.effective_time < date_add(current_date, interval 1 day)
              and floor(hour(v.effective_time) / 2) * 2 = buckets.bucket_hour
            group by buckets.bucket_hour, buckets.bucket_time
            order by buckets.bucket_hour
            """)
    List<Map<String, Object>> selectDailyVerificationTrend();

    @Select("""
            select date_format(days.bucket_day, '%c/%e') as time,
                   coalesce(count(v.id), 0) as `order`,
                   round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
            from (
              select date_sub(current_date, interval 6 day) as bucket_day union all
              select date_sub(current_date, interval 5 day) union all
              select date_sub(current_date, interval 4 day) union all
              select date_sub(current_date, interval 3 day) union all
              select date_sub(current_date, interval 2 day) union all
              select date_sub(current_date, interval 1 day) union all
              select current_date
            ) days
            left join (
              select o.id, o.pay_amount,
                     coalesce(
                       (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
                       (select max(pt.paid_at) from payment_transaction pt
                         where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                       o.updated_at
                     ) as effective_time
                from benefit_order o
               where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            ) v on v.effective_time >= days.bucket_day
              and v.effective_time < date_add(days.bucket_day, interval 1 day)
            group by days.bucket_day
            order by days.bucket_day
            """)
    List<Map<String, Object>> selectWeeklyVerificationTrend();

    @Select("""
            select concat(weeks.week_start, '~', weeks.week_end) as time,
                   coalesce(count(v.id), 0) as `order`,
                   round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
            from (
              select 0 bucket_index,
                     date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 0 day), '%c/%e') as week_start,
                     date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 6 day), last_day(current_date)), '%c/%e') as week_end,
                     date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 0 day) as start_date,
                     least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 6 day), last_day(current_date)) as end_date
              union all
              select 1,
                     date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 7 day), '%c/%e'),
                     date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 13 day), last_day(current_date)), '%c/%e'),
                     date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 7 day),
                     least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 13 day), last_day(current_date))
              union all
              select 2,
                     date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 14 day), '%c/%e'),
                     date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 20 day), last_day(current_date)), '%c/%e'),
                     date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 14 day),
                     least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 20 day), last_day(current_date))
              union all
              select 3,
                     date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 21 day), '%c/%e'),
                     date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 27 day), last_day(current_date)), '%c/%e'),
                     date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 21 day),
                     least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 27 day), last_day(current_date))
              union all
              select 4,
                     date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day), '%c/%e'),
                     date_format(last_day(current_date), '%c/%e'),
                     date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day),
                     last_day(current_date)
            ) weeks
            left join (
              select o.id, o.pay_amount,
                     coalesce(
                       (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
                       (select max(pt.paid_at) from payment_transaction pt
                         where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                       o.updated_at
                     ) as effective_time
                from benefit_order o
               where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            ) v on v.effective_time >= weeks.start_date
              and v.effective_time < date_add(weeks.end_date, interval 1 day)
            where weeks.start_date <= last_day(current_date)
            group by weeks.bucket_index, weeks.week_start, weeks.week_end, weeks.start_date
            order by weeks.bucket_index
            """)
    List<Map<String, Object>> selectMonthlyVerificationTrend();

    @Select("""
            select concat(months.bucket_month, '月') as time,
                   coalesce(count(v.id), 0) as `order`,
                   round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
            from (
              select 1 bucket_month union all select 2 union all select 3 union all select 4
              union all select 5 union all select 6 union all select 7 union all select 8
              union all select 9 union all select 10 union all select 11 union all select 12
            ) months
            left join (
              select o.id, o.pay_amount,
                     coalesce(
                       (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
                       (select max(pt.paid_at) from payment_transaction pt
                         where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                       o.updated_at
                     ) as effective_time
                from benefit_order o
               where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            ) v on year(v.effective_time) = year(current_date)
              and month(v.effective_time) = months.bucket_month
            group by months.bucket_month
            order by months.bucket_month
            """)
    List<Map<String, Object>> selectYearlyVerificationTrend();

    @Select("""
            select '体检预约' as label, coalesce(sum(case when application_type = 'MEDICAL_RESERVATION' then 1 else 0 end), 0) as value, '#2f91ff' as color
            from service_application
            where deleted = 0 and status in ('待处理', '待审核')
            union all
            select '就医申请' as label, coalesce(sum(case when application_type = 'FAST_TRACK' then 1 else 0 end), 0) as value, '#20c6ee' as color
            from service_application
            where deleted = 0 and status in ('待处理', '待审核')
            union all
            select '停车车牌' as label, count(*) as value, '#35bfc7' as color
            from transport_parking_plate_registration
            where deleted = 0 and status in ('SUBMITTED', 'PENDING', '待处理', '待审核')
            union all
            select '酒店预约' as label, coalesce(sum(case when application_type = 'HOTEL_RESERVATION' then 1 else 0 end), 0) as value, '#7cb5ff' as color
            from service_application
            where deleted = 0 and status in ('待处理', '待审核')
            union all
            select '家属卡申请' as label, count(*) as value, '#9b7cff' as color
            from dependent_card_apply
            where deleted = 0 and status = 0
            union all
            select '配偶就业申请' as label, coalesce(sum(case when application_type = 'SPOUSE_EMPLOYMENT' then 1 else 0 end), 0) as value, '#ff9f5f' as color
            from service_application
            where deleted = 0 and status in ('待处理', '待审核')
            """)
    List<Map<String, Object>> selectPendingItems();

    @Select("""
            select coalesce(nullif(m.merchant_name, ''), '未知商家') as merchantName,
                   count(v.id) as verificationCount,
                   round(coalesce(sum(v.verify_amount), 0) / 100, 2) as verificationAmount,
                   coalesce(v.merchant_id, o.merchant_id) as merchantGroupId,
                   coalesce(m.id, 0) as merchantSortId
            from verify_record v
            join benefit_order o on o.id = v.order_id and o.deleted = 0
            left join merchant m on m.id = coalesce(v.merchant_id, o.merchant_id) and m.deleted = 0
            where v.status = 1
            group by coalesce(v.merchant_id, o.merchant_id), coalesce(nullif(m.merchant_name, ''), '未知商家'), m.id
            order by verificationCount desc, verificationAmount desc, merchantSortId desc
            limit 50
            """)
    List<Map<String, Object>> selectHotMerchants();

    @Select("""
            select category.label,
                   case when total.total_count = 0 then 0 else round(category.category_count * 100 / total.total_count, 2) end as value,
                   category.color
            from (
              select '到店消费' as label,
                     '#2f91ff' as color,
                     coalesce(sum(case when o.order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费') then 1 else 0 end), 0) as category_count
              from benefit_order o
              where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
              union all
              select '团购' as label,
                     '#20c6ee' as color,
                     coalesce(sum(case when o.order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单') then 1 else 0 end), 0) as category_count
              from benefit_order o
              where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            ) category
            cross join (
              select coalesce(sum(total_source.category_count), 0) as total_count
              from (
                select coalesce(sum(case when o.order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费') then 1 else 0 end), 0) as category_count
                from benefit_order o
                where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
                union all
                select coalesce(sum(case when o.order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单') then 1 else 0 end), 0) as category_count
                from benefit_order o
                where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
              ) total_source
            ) total
            order by field(category.label, '到店消费', '团购')
            """)
    List<Map<String, Object>> selectOrderCategoryShares();
}