package com.talentcard.server.mapper;

/** MyBatis SQL provider：政府补贴表达式在运行期拼接，兼容 MySQL 5.7。 */
public class StatisticsQuerySql {
    public String selectSummary() {
        return sql("""
                select
                  coalesce(sum(/*AMOUNT*/), 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
                left join merchant m on m.id = o.merchant_id and m.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
                """);
    }

    public String selectCompareSummary() {
        return sql("""
                select
                  coalesce((select count(*) from talent_user u
                             where u.deleted = 0
                               and u.created_at >= current_date
                               and u.created_at < date_add(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(/*AMOUNT*/), 0) as subsidyCompareCents
                from benefit_order o
                left join merchant_product p on p.id = o.product_id and p.deleted = 0
                left join merchant m on m.id = o.merchant_id and m.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 /*ORDER_TIME*/ >= current_date
                  and /*ORDER_TIME*/ < date_add(current_date, interval 1 day)
                """);
    }

    public String selectSummaryTrend() {
        return sql("""
                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(/*AMOUNT*/)
                          from benefit_order o
                          left join merchant_product p on p.id = o.product_id and p.deleted = 0
                          left join merchant m on m.id = o.merchant_id and m.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 /*ORDER_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
                """);
    }

    public String selectPeriodSummaries() {
        return sql("""
            <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,
                       /*AMOUNT*/ 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_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.verify_time as business_time,
                       r.merchant_id,
                       m.merchant_name,
                       m.merchant_type,
                       0 as order_amount,
                       0 as pay_amount,
                       0 as coupon_amount,
                       coalesce(r.subsidy_amount, 0) as government_subsidy_amount
                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, base.merchant_name, base.merchant_type
            order by base.period_key desc, base.merchant_id desc
            limit #{size} offset #{offset}
            </script>
            """);
    }

    public String selectMerchantOverviews() {
        return sql("""
            <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,
                       /*AMOUNT*/ 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_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 r.verify_time as business_time,
                       r.merchant_id,
                       m.merchant_name,
                       m.merchant_type,
                       0 as order_amount,
                       0 as pay_amount,
                       0 as coupon_amount,
                       coalesce(r.subsidy_amount, 0) as government_subsidy_amount
                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.merchant_id
            order by settlementAmountCents desc, base.merchant_id desc
            </script>
            """);
    }

    public String selectSettlementExportDetails() {
        return sql("""
            <script>
            select details.*
            from (
            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,
                   /*AMOUNT*/ as subsidyAmountCents,
                   coalesce(o.pay_amount, 0) + /*AMOUNT*/ 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,
                   coalesce(v.verify_time, o.updated_at, o.created_at) as businessTime
            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>
            union all
            select r.id as orderId,
                   r.verify_no as orderNo,
                   'HOTEL_FREE' as orderType,
                   r.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   r.store_id as storeId,
                   s.store_name as storeName,
                   r.talent_id as talentId,
                   t.name as talentName,
                   t.mobile as talentMobile,
                   tc.card_no as cardNo,
                   tc.card_level as cardLevel,
                   '酒店免费入住券' as productName,
                   0 as originalAmountCents,
                   0 as transactionAmountCents,
                   coalesce(r.subsidy_amount, 0) as subsidyAmountCents,
                   coalesce(r.subsidy_amount, 0) as settlementAmountCents,
                   0 as couponAmountCents,
                   1 as orderStatus,
                   1 as verifyStatus,
                   r.verify_time as createdAt,
                   null as paidAt,
                   r.verify_time as verifyTime,
                   r.staff_id as verifyOperator,
                   r.verify_time as businessTime
            from verify_record r
            join merchant m on m.id = r.merchant_id and m.deleted = 0
            left join merchant_store s on s.id = r.store_id and s.deleted = 0
            left join talent_user t on t.id = r.talent_id and t.deleted = 0
            left join talent_card tc on tc.id = r.card_id and tc.deleted = 0
            where r.status = 1
              and r.order_id is null
              and upper(r.verify_type) = 'HOTEL_FREE'
              and r.merchant_id = #{merchantId}
              <if test="orderStatus != null">and #{orderStatus} = 1</if>
              <if test="verifyStatus != null">and #{verifyStatus} = 1</if>
              <if test="startTime != null">and r.verify_time &gt;= #{startTime}</if>
              <if test="endTime != null">and r.verify_time &lt; #{endTime}</if>
            ) details
            order by details.businessTime desc, details.orderId desc
            limit #{limit}
            </script>
            """);
    }

    public String selectDashboardCards() {
        return sql("""
            select
                coalesce((
                    -- 只算已核销的实付金额：未核销的钱还扣在通道的延迟户里，核销后才分账给商户；
                    -- 退款中的订单在通道结果收敛前仍是 order_status = 1，只有「已核销」能立刻挡住它。
                    select sum(o.pay_amount)
                    from benefit_order o
                    where o.deleted = 0
                      and o.order_status = 1
                      and o.verify_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(/*AMOUNT*/)
                    from benefit_order o
                    left join merchant_product p on p.id = o.product_id and p.deleted = 0
                    left join merchant m on m.id = o.merchant_id and m.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)
                +
                coalesce((
                    select sum(r.subsidy_amount)
                    from verify_record r
                    where r.status = 1
                      and r.order_id is null
                      and upper(r.verify_type) = 'HOTEL_FREE'
                      and r.merchant_id = #{merchantId}
                ), 0) as subsidyAmountCents
            """);
    }

    private static String sql(String template) {
        String amount = template.contains("<script>")
                ? GovernmentSubsidySql.AMOUNT_CENTS.replace("<", "&lt;")
                : GovernmentSubsidySql.AMOUNT_CENTS;
        return template.replace("/*AMOUNT*/", amount)
                .replace("/*ORDER_TIME*/", GovernmentSubsidySql.ORDER_TIME);
    }
}