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.List;
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), 0) as subsidyAmountCents,
              (select count(*) from benefit_order verified_order
                 where verified_order.deleted = 0
                   and verified_order.order_status = 1
                   and verified_order.verify_status = 1
                   and coalesce(
                         (select max(v.verify_time) from verify_record v
                           where v.order_id = verified_order.id and v.status = 1),
                         (select max(pt.paid_at) from payment_transaction pt
                           where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                         verified_order.updated_at
                       ) >= date_format(current_date, '%Y-%m-01')
                   and coalesce(
                         (select max(v.verify_time) from verify_record v
                           where v.order_id = verified_order.id and v.status = 1),
                         (select max(pt.paid_at) from payment_transaction pt
                           where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                         verified_order.updated_at
                       ) < 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 ('待处理', '待审核')) as pendingApplicationCount
            from benefit_order o
            left join merchant_product p on p.id = o.product_id and p.deleted = 0
            where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
            """)
    Map<String, Object> selectSummary();

    @Select("""
            select
              coalesce((select count(*) from talent_user u
                         where u.deleted = 0
                           and date(u.created_at) = date_sub(current_date, interval 1 day)), 0) as talentCompare,
              (coalesce((select count(*) from benefit_order current_month_order
                          where current_month_order.deleted = 0
                            and current_month_order.order_status = 1
                            and current_month_order.verify_status = 1
                            and coalesce(
                                  (select max(v.verify_time) from verify_record v
                                    where v.order_id = current_month_order.id and v.status = 1),
                                  (select max(pt.paid_at) from payment_transaction pt
                                    where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                                  current_month_order.updated_at
                                ) >= date_format(current_date, '%Y-%m-01')
                            and coalesce(
                                  (select max(v.verify_time) from verify_record v
                                    where v.order_id = current_month_order.id and v.status = 1),
                                  (select max(pt.paid_at) from payment_transaction pt
                                    where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                                  current_month_order.updated_at
                                ) < date_add(last_day(current_date), interval 1 day)), 0)
               - coalesce((select count(*) from benefit_order previous_month_order
                            where previous_month_order.deleted = 0
                              and previous_month_order.order_status = 1
                              and previous_month_order.verify_status = 1
                              and coalesce(
                                    (select max(v.verify_time) from verify_record v
                                      where v.order_id = previous_month_order.id and v.status = 1),
                                    (select max(pt.paid_at) from payment_transaction pt
                                      where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                                    previous_month_order.updated_at
                                  ) >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
                              and coalesce(
                                    (select max(v.verify_time) from verify_record v
                                      where v.order_id = previous_month_order.id and v.status = 1),
                                    (select max(pt.paid_at) from payment_transaction pt
                                      where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                                    previous_month_order.updated_at
                                  ) < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
              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), 0) as subsidyCompareCents
            from benefit_order o
            left join merchant_product p on p.id = o.product_id and p.deleted = 0
            where o.deleted = 0
              and o.order_status = 1
              and o.verify_status = 1
              and exists (
                select 1 from verify_record yesterday_verify
                where yesterday_verify.order_id = o.id
                  and yesterday_verify.status = 1
                  and yesterday_verify.verify_time >= date_sub(current_date, interval 1 day)
                  and yesterday_verify.verify_time < current_date
              )
            """)
    Map<String, Object> selectCompareSummary();

    @Select("""
            select date_format(days.bucket_day, '%c/%e') as time,
                   (select count(*)
                      from talent_user u
                     where u.deleted = 0
                       and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
                   (select count(*)
                      from benefit_order verified_order
                     where verified_order.deleted = 0
                       and verified_order.order_status = 1
                       and verified_order.verify_status = 1
                       and coalesce(
                             (select max(v.verify_time) from verify_record v
                               where v.order_id = verified_order.id and v.status = 1),
                             (select max(pt.paid_at) from payment_transaction pt
                               where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                             verified_order.updated_at
                           ) >= days.bucket_day
                       and coalesce(
                             (select max(v.verify_time) from verify_record v
                               where v.order_id = verified_order.id and v.status = 1),
                             (select max(pt.paid_at) from payment_transaction pt
                               where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
                             verified_order.updated_at
                           ) < date_add(days.bucket_day, interval 1 day)) as verification,
                   round(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 o.subsidy_amount else 0 end)
                      from benefit_order o
                      left join merchant_product p on p.id = o.product_id and p.deleted = 0
                     where o.deleted = 0
                       and o.order_status = 1
                       and o.verify_status = 1
                       and exists (
                         select 1
                           from verify_record subsidy_verify
                          where subsidy_verify.order_id = o.id
                            and subsidy_verify.status = 1
                            and subsidy_verify.verify_time < date_add(days.bucket_day, interval 1 day)
                       )), 0) / 100, 2) as subsidy
              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
             order by days.bucket_day
            """)
    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();
}