feat(merchant): 每日凌晨按商户汇总已核销订单生成提现记录

yeg

2026-09-12 8ccb070e4054765c23ad3a76a962feaf334c37b1

feat(merchant): 每日凌晨按商户汇总已核销订单生成提现记录

工单 04。新增 AutoWithdrawalAggregationService / AutoWithdrawalScheduler：
北京时间每日 00:10 汇总前一个自然日（窗口为「核销时间不晚于该日 24:00」，
无下界，跳过一天的订单下一次仍会汇总，不会被永久留在未提现状态）。

- 只汇总已支付、已核销、未退款、未进入其它提现记录的订单；每个订单先经
  OrderFundBoundaryService.lock 复检并 lockIntoWithdrawal 锁定，再登记关联。
- 每个商户当天生成一条 merchant_withdrawal（金额为整数分，状态 pending），
  业务日写新列 withdrawal_date；来源订单写新表 merchant_withdrawal_order，
  订单唯一。
- 低于最低金额（默认 1000 分）或扣掉手续费缓冲后不足最低金额时顺延，
  不生成记录，金额留到下一次任务累积。
- 幂等三层：先查当天是否已有记录、业务请求号固定为 auto-withdrawal:{merchantId}:{yyyyMMdd}、
  唯一索引 uk_merchant_withdrawal_daily(merchant_id, withdrawal_date)，重复执行与并发
  都只出一条记录一份金额。
- 每个商户一个事务，一个商户失败不影响其它商户；不调用斗拱，提交与到账结果留给工单 05。

迁移 V133 新增 withdrawal_date、唯一索引和关联表。新增 AutoWithdrawalAggregationTests
10 项覆盖北京时间边界、汇总范围、关联、顺延、重复执行和数据库层唯一约束。全量 152 项
测试仅剩 docs/agents/testing.md 记录的 3 项基线失败。

 .scratch/automatic-withdrawal/issues/04-daily-withdrawal-aggregation.md

@@ -4,11 +4,50 @@

**Blocked by:** 03 — 核销幂等与退款边界

**Status:** ready-for-agent
**Status:** ready-for-human

- [ ] 任务按北京时间统计前一自然日订单。
- [ ] 只汇总已核销、未退款且未进入其他提现记录的订单。
- [ ] 同一商户生成一条日汇总提现记录。
- [ ] 内部保存提现记录与来源订单的关联。
- [ ] 最低金额和手续费缓冲不足时顺延。
- [ ] 重复执行任务不会重复生成提现金额。
- [x] 任务按北京时间统计前一自然日订单。
- [x] 只汇总已核销、未退款且未进入其他提现记录的订单。
- [x] 同一商户生成一条日汇总提现记录。
- [x] 内部保存提现记录与来源订单的关联。
- [x] 最低金额和手续费缓冲不足时顺延。
- [x] 重复执行任务不会重复生成提现金额。

## Comments

已实现，主要落点：

- 新增 `AutoWithdrawalAggregationService`：`run()` 汇总前一个自然日（北京时间），`run(businessDate)`
  供测试和补跑指定业务日。业务日窗口是「核销时间不晚于该业务日 24:00」且没有下界——凌晨执行时当天
  核销的订单都在窗口内，某天被跳过的订单（退款在途、商户当时没有可用结算银行卡）下一次仍在窗口内，
  不会永久留在未提现状态。业务日与日期边界都用 `Asia/Shanghai`，不依赖服务器或 JVM 默认时区。
- 新增 `AutoWithdrawalScheduler`：cron 默认 `0 10 0 * * *` 且 `zone = "Asia/Shanghai"`，可用
  `app.merchant.auto-withdrawal.*` 配置。只生成本地提现记录，不调用斗拱；提交给斗拱、到账结果与
  失败顺延留给工单 05。
- 新增 `AutoWithdrawalProperties`（`min-amount-cents` 默认 1000、`fee-buffer-cents` 默认 0、
  `enabled`、`cron`）。可提金额低于最低金额、或扣掉手续费缓冲后仍低于最低金额时，该商户当天不生成
  记录，金额顺延到下一次任务。
- 提现记录复用 `merchant_withdrawal`，金额为整数分（来源订单 `pay_amount` 之和），状态保持本地
  `pending`，业务日写新列 `withdrawal_date`。
- 来源订单关联写新表 `merchant_withdrawal_order(withdrawal_id, order_id, amount_cents)`，
  `order_id` 唯一。每个订单先 `OrderFundBoundaryService.lock(orderId)` 复检「已支付 + 已核销 +
  未退款 + 未进入其它提现记录」，再 `lockIntoWithdrawal(orderId)` 锁定，最后登记关联；锁定失败即整笔
  汇总回滚，因此同一订单不可能出现在两条提现记录里。
- 每个商户一个事务（`TransactionTemplate`），一个商户失败不影响其它商户；失败时订单保持未锁定，
  下一次任务重新汇总。
- 幂等三层：先查当天是否已有记录（有则跳过）、业务请求号固定为 `auto-withdrawal:{merchantId}:{yyyyMMdd}`
  （`uk_merchant_withdrawal_key`）、新增唯一索引 `uk_merchant_withdrawal_daily(merchant_id, withdrawal_date)`。
  重复执行或两个实例并发都只会有一条记录、一份金额。
- 迁移 `V133__daily_withdrawal_aggregation.sql`：新增 `withdrawal_date` 列、唯一索引和关联表。
- 文档：`talent-card-server/docs/huifu-payment-integration.md` 新增 "Daily withdrawal aggregation"
  一节，写明规则表、配置项和窗口无下界的原因。

测试：新增 `AutoWithdrawalAggregationTests` 10 项（MySQL），覆盖北京时间业务日边界（含 UTC 与北京
日期不同的时刻）、业务日窗口右端、只汇总已核销未退款且未进入其它提现记录的订单、来源订单与金额关联、
最低金额顺延（跨天累积）、手续费缓冲顺延、重复执行不重复出账（当天跑完后又核销一笔）、数据库层
唯一约束、使用审核通过的结算银行卡、没有可用银行卡的商户不汇总、默认 `run()` 汇总北京时间昨天。
全量 152 项测试仅剩 `docs/agents/testing.md` 记录的 3 项基线失败。

遗留（不在本 ticket 范围）：提交斗拱、到账结果处理、失败重试与失败顺延解锁订单，见工单 05；失败解锁
需要 `OrderFundBoundaryService` 增加显式的解锁方法。首次上线会把历史上所有「已核销未提现」订单一次
汇总进来，若单笔金额超过斗拱主动取现上限，需要在上限约束（工单 05/06）里处理。

 talent-card-server/docs/huifu-payment-integration.md

@@ -161,6 +161,41 @@
duplicate historical verification records as revoked (keeping the earliest one) before creating the
unique key.

## Daily withdrawal aggregation

`AutoWithdrawalScheduler` runs `AutoWithdrawalAggregationService` every day at 00:10 Beijing time
(`app.merchant.auto-withdrawal.cron`, zone `Asia/Shanghai`) for the previous calendar day. The service
turns one merchant-day into one `merchant_withdrawal` row; it does not call the provider, so the row
stays in the local `pending` state until a later ticket submits it.

| Rule | Implementation |
| --- | --- |
| Business day | Beijing time. The window is `verify_time < business day 24:00` with no lower bound |
| Order eligibility | `order_status = 1`, `verify_status = 1`, `refund_status = 0`, `withdrawal_status = 0`, plus an active `verify_record` |
| Merchant eligibility | Newest approved onboarding of each merchant store with an approved settlement card that has a provider token |
| One record per merchant-day | `merchant_withdrawal.withdrawal_date` (Beijing `yyyyMMdd`) plus unique key `uk_merchant_withdrawal_daily` |
| Source order links | `merchant_withdrawal_order`, one row per order, unique key on `order_id` |
| Amount | Integer cents, the sum of `benefit_order.pay_amount` |
| Deferral | Skip the day when the total is below `min-amount-cents`, or when `total - fee-buffer-cents` is still below it |
| Idempotency | The day's record is looked up first; the unique keys on `(merchant_id, withdrawal_date)` and `order_id` are the database-level guard |
| Locking | Every aggregated order goes through `OrderFundBoundaryService.lockIntoWithdrawal`, so it can no longer be revoked, refunded or aggregated again |

The window has no lower bound on purpose: an order that was skipped because its refund was still in
flight, or because the merchant had no usable settlement card that day, is picked up by the next run
instead of being stranded. A merchant-day that never reaches the minimum simply never creates a row,
and the amount carries forward into the next run.

```text
AUTO_WITHDRAWAL_ENABLED=true
AUTO_WITHDRAWAL_MIN_AMOUNT_CENTS=1000
AUTO_WITHDRAWAL_FEE_BUFFER_CENTS=0
AUTO_WITHDRAWAL_CRON=0 10 0 * * *
```

`AutoWithdrawalAggregationTests` covers the Beijing date boundary, the eligibility filters, the source
order links, the minimum amount and fee-buffer deferral, repeated runs, the database uniqueness guard,
the approved settlement card and the merchant without a usable card.

## Reconciliation and exception handling

The server records verified callback audit events, then uses the provider query API as a second confirmation path for payments and refunds that remain pending. It never converts an order to paid from a client request or a local timer result alone.

 talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java

New file
@@ -0,0 +1,297 @@
package com.talentcard.server.business.merchant;

import java.time.Clock;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.talentcard.server.business.BusinessIdGenerator;
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.order.OrderFundBoundaryService;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 每日自动提现汇总。
 *
 * <p>按北京时间取前一个自然日，把每个商户「截至该日结束已核销、未退款、还没进入其它提现记录」的订单
 * 汇总成一条提现记录：金额写 {@code merchant_withdrawal}，来源订单写内部关联表
 * {@code merchant_withdrawal_order}，提现记录的业务日写 {@code withdrawal_date}。
 * 汇总后的订单通过 {@link OrderFundBoundaryService#lockIntoWithdrawal(long)} 标记为已进入提现记录，
 * 因此不能再撤销核销或退款，也不会被以后的任务重复计入。
 *
 * <p>业务日窗口是「核销时间不晚于该业务日 24:00」，没有下界：任务在凌晨跑，因此当天白天核销的订单
 * 都落在窗口内；某个业务日没被汇总的订单（核销时退款还在途、或商户当时没有可用的结算银行卡）
 * 不会被丢掉，下一次任务仍在窗口内，会随新订单一起汇总。这个选择保证订单不会因为一次跳过而永久
 * 留在未提现状态，代价是一次任务会扫描商户全部未进入提现记录的已核销订单（{@code verify_record.verify_time}
 * 和 {@code benefit_order.merchant_id} 都有索引）。
 *
 * <p>可提金额低于最低提现金额、或扣掉手续费缓冲后不足最低金额时，这个商户当天不生成记录，
 * 金额留到下一次任务继续累积（顺延）。
 *
 * <p>幂等：同一商户同一业务日只可能有一条日汇总记录（先查后写，并有
 * {@code uk_merchant_withdrawal_daily} 兜底），任务重复执行不会重复生成提现金额。
 *
 * <p>只在本服务内生成提现记录并把订单标记为处理中，不调用斗拱主动取现接口；提交给斗拱、处理
 * 到账结果和失败顺延由后续工单负责。
 */
@Service
public class AutoWithdrawalAggregationService {

    private static final Logger log = LoggerFactory.getLogger(AutoWithdrawalAggregationService.class);

    /** 日汇总和日期边界一律按北京时间计算，与商户看到的「日」一致。 */
    public static final ZoneId BEIJING = ZoneId.of("Asia/Shanghai");

    private static final DateTimeFormatter BUSINESS_DATE = DateTimeFormatter.ofPattern("yyyyMMdd");

    private static final String ELIGIBLE_MERCHANTS = """
            select o.merchant_id, o.store_id, coalesce(i.id, r.id) as bank_card_id,
                   coalesce(i.provider_token_no, r2.provider_token_no) as token_no
            from merchant_onboarding_application o
            join (select merchant_id, store_id, max(id) as onboarding_id
                  from merchant_onboarding_application
                  where status = 'approved'
                  group by merchant_id, store_id) newest
              on newest.onboarding_id = o.id
            left join merchant_bank_card i on i.id = o.initial_bank_card_id and i.status = 'approved'
            left join (select merchant_id, store_id, max(id) as id
                       from merchant_bank_card
                       where status = 'approved' and provider_token_no is not null and provider_token_no <> ''
                       group by merchant_id, store_id) r
              on r.merchant_id = o.merchant_id and r.store_id = o.store_id
            left join merchant_bank_card r2 on r2.id = r.id
            where coalesce(i.provider_token_no, r2.provider_token_no) is not null
              and coalesce(i.provider_token_no, r2.provider_token_no) <> ''
              and exists (select 1
                          from benefit_order bo
                          join verify_record vr on vr.order_id = bo.id and vr.status = 1
                          where bo.merchant_id = o.merchant_id
                            and bo.deleted = 0 and bo.order_status = 1 and bo.verify_status = 1
                            and bo.refund_status = 0 and bo.withdrawal_status = 0
                            and vr.verify_time < ?)
            order by o.id desc
            """;

    private static final String ELIGIBLE_ORDERS = """
            select bo.id, bo.pay_amount
            from benefit_order bo
            where bo.merchant_id = ?
              and bo.deleted = 0 and bo.order_status = 1 and bo.verify_status = 1
              and bo.refund_status = 0 and bo.withdrawal_status = 0
              and exists (select 1
                          from verify_record vr
                          where vr.order_id = bo.id and vr.status = 1
                            and vr.verify_time < ?)
            order by bo.id asc
            """;

    private final JdbcTemplate jdbc;
    private final OrderFundBoundaryService orderFundBoundary;
    private final TransactionTemplate transactions;
    private final AutoWithdrawalProperties properties;
    private final Clock clock;

    @Autowired
    public AutoWithdrawalAggregationService(JdbcTemplate jdbc, OrderFundBoundaryService orderFundBoundary,
                                            TransactionTemplate transactions, AutoWithdrawalProperties properties) {
        this(jdbc, orderFundBoundary, transactions, properties, Clock.system(BEIJING));
    }

    /**
     * 供测试或补跑脚本按指定时钟构造：业务日由时钟的北京时间决定，
     * 因此「昨天」的判定可以脱离运行环境验证。
     */
    public AutoWithdrawalAggregationService(JdbcTemplate jdbc, OrderFundBoundaryService orderFundBoundary,
                                            TransactionTemplate transactions, AutoWithdrawalProperties properties,
                                            Clock clock) {
        this.jdbc = jdbc;
        this.orderFundBoundary = orderFundBoundary;
        this.transactions = transactions;
        this.properties = properties;
        this.clock = clock;
    }

    /** 定时任务入口：按北京时间汇总前一个自然日。 */
    public Result run() {
        return run(previousBusinessDate(clock.instant()));
    }

    /** 某个业务日的汇总。业务日始终按北京时间解释，因此测试和补跑都可以指定具体日期。 */
    public Result run(LocalDate businessDate) {
        // 窗口右端是该业务日 24:00（北京时间）。任务在凌晨执行，当天核销的订单都在窗口内；
        // 没被汇总的订单下一次仍在窗口内，会随新订单一起顺延。
        LocalDateTime windowEnd = businessDate.plusDays(1).atStartOfDay();
        String dateText = businessDate.format(BUSINESS_DATE);

        Result result = new Result(dateText);
        for (Map<String, Object> candidate : DatabaseRows.normalize(jdbc.queryForList(
                ELIGIBLE_MERCHANTS, windowEnd))) {
            Long merchantId = number(candidate.get("merchantId"));
            Long storeId = number(candidate.get("storeId"));
            Long bankCardId = number(candidate.get("bankCardId"));
            try {
                aggregateMerchant(merchantId, storeId, bankCardId, dateText, windowEnd, result);
            } catch (RuntimeException exception) {
                // 一个商户汇总失败不影响其它商户；失败的订单保持未锁定，下一次任务会重新汇总。
                // 与另一个实例撞在一起时唯一索引会抛 DuplicateKeyException：那一天的记录由对方生成，
                // 本实例没有出账，属于「已存在」而不是失败。
                if (exception instanceof DuplicateKeyException) {
                    result.merchantSkipped();
                    log.info("Auto withdrawal for merchant {} on {} already exists, created by another run",
                            merchantId, dateText);
                } else {
                    result.merchantFailed();
                    log.warn("Auto withdrawal aggregation failed for merchant {} on {}: {}",
                            merchantId, dateText, exception.getClass().getSimpleName());
                }
            }
        }
        return result;
    }

    /** 前一个自然日（北京时间）。凌晨触发时汇总的就是昨天，跨月跨年由 LocalDate 处理。 */
    public static LocalDate previousBusinessDate(Instant instant) {
        return LocalDate.ofInstant(instant, BEIJING).minusDays(1);
    }

    private void aggregateMerchant(Long merchantId, Long storeId, Long bankCardId, String dateText,
                                   LocalDateTime windowEnd, Result result) {
        if (merchantId == null || storeId == null || bankCardId == null) return;
        transactions.executeWithoutResult(status -> {
            if (hasDailyWithdrawal(merchantId, dateText)) {
                result.merchantSkipped();
                return;
            }
            List<OrderAmount> orders = new ArrayList<>();
            long total = 0;
            for (Map<String, Object> row : DatabaseRows.normalize(jdbc.queryForList(
                    ELIGIBLE_ORDERS, merchantId, windowEnd))) {
                Long orderId = number(row.get("id"));
                Long payAmount = number(row.get("payAmount"));
                long amount = payAmount == null ? 0 : payAmount;
                OrderFundBoundaryService.LockedOrder locked = orderFundBoundary.lock(orderId);
                if (locked.withdrawalLocked()) throw new BusinessException(ErrorCode.CONFLICT, "订单已进入其它提现记录");
                if (!isEligible(locked)) throw new BusinessException(ErrorCode.CONFLICT, "订单已不满足进入提现记录的条件");
                orders.add(new OrderAmount(orderId, amount));
                total += amount;
            }
            if (orders.isEmpty()) return;
            if (deferred(total)) {
                result.merchantDeferred();
                return;
            }

            createWithdrawal(merchantId, storeId, bankCardId, dateText, total, orders);
            result.merchantAggregated(total, orders.size());
        });
    }

    /** 行锁拿到的状态才是判断依据：候选查询之后订单可能已经被退款或核销撤销。 */
    private boolean isEligible(OrderFundBoundaryService.LockedOrder locked) {
        return locked.orderStatus() == 1 && locked.verifyStatus() == 1
                && locked.refundStatus() == OrderFundBoundaryService.REFUND_NONE
                && !locked.withdrawalLocked();
    }

    private boolean deferred(long total) {
        long buffer = Math.max(0, properties.getFeeBufferCents());
        long minimum = Math.max(0, properties.getMinAmountCents());
        return total < minimum || total - buffer < minimum;
    }

    private boolean hasDailyWithdrawal(Long merchantId, String dateText) {
        Integer count = jdbc.queryForObject(
                "select count(*) from merchant_withdrawal where merchant_id = ? and withdrawal_date = ?",
                Integer.class, merchantId, dateText);
        return count != null && count > 0;
    }

    private void createWithdrawal(Long merchantId, Long storeId, Long bankCardId, String dateText,
                                  long total, List<OrderAmount> orders) {
        // 业务请求号固定为商户 + 业务日：重复插入会被 uk_merchant_withdrawal_key 拦住，不会重复出账。
        String idempotencyKey = "auto-withdrawal:" + merchantId + ":" + dateText;
        jdbc.update("""
                insert into merchant_withdrawal(merchant_id, store_id, withdrawal_no, bank_card_id,
                                                amount_cents, idempotency_key, withdrawal_date)
                values (?, ?, ?, ?, ?, ?, ?)
                """, merchantId, storeId, BusinessIdGenerator.next("WA"), bankCardId, total, idempotencyKey, dateText);
        Long withdrawalId = jdbc.queryForObject(
                "select id from merchant_withdrawal where merchant_id = ? and idempotency_key = ?",
                Long.class, merchantId, idempotencyKey);
        for (OrderAmount order : orders) {
            orderFundBoundary.lockIntoWithdrawal(order.orderId());
            jdbc.update("""
                    insert into merchant_withdrawal_order(withdrawal_id, order_id, amount_cents)
                    values (?, ?, ?)
                    """, withdrawalId, order.orderId(), order.amount());
        }
    }

    private Long number(Object value) {
        return value instanceof Number number ? number.longValue() : null;
    }

    private record OrderAmount(Long orderId, long amount) { }

    /** 一次汇总的结果，用于日志和测试断言。 */
    public static final class Result {

        private final String businessDate;
        private int merchantsAggregated;
        private int merchantsDeferred;
        private int merchantsSkipped;
        private int merchantsFailed;
        private int ordersAggregated;
        private long amountCents;

        Result(String businessDate) {
            this.businessDate = businessDate;
        }

        void merchantAggregated(long amountCents, int orders) {
            merchantsAggregated++;
            this.amountCents += amountCents;
            ordersAggregated += orders;
        }

        void merchantDeferred() {
            merchantsDeferred++;
        }

        void merchantSkipped() {
            merchantsSkipped++;
        }

        void merchantFailed() {
            merchantsFailed++;
        }

        public String businessDate() { return businessDate; }
        public int merchantsAggregated() { return merchantsAggregated; }
        public int merchantsDeferred() { return merchantsDeferred; }
        public int merchantsSkipped() { return merchantsSkipped; }
        public int merchantsFailed() { return merchantsFailed; }
        public int ordersAggregated() { return ordersAggregated; }
        public long amountCents() { return amountCents; }

        @Override
        public String toString() {
            return "businessDate=" + businessDate + " aggregated=" + merchantsAggregated
                    + " deferred=" + merchantsDeferred + " skipped=" + merchantsSkipped
                    + " failed=" + merchantsFailed + " orders=" + ordersAggregated + " amountCents=" + amountCents;
        }
    }
}

 talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalProperties.java

New file
@@ -0,0 +1,34 @@
package com.talentcard.server.business.merchant;

import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * 每日自动提现汇总的阈值。两个值都是整数分，和库里的金额列一致。
 *
 * <p>低于最低提现金额，或者扣掉手续费缓冲后低于最低提现金额，这一天的汇总就顺延到下一次任务，
 * 不生成提现记录（CONTEXT.md「可提现订单」）。
 */
@ConfigurationProperties(prefix = "app.merchant.auto-withdrawal")
public class AutoWithdrawalProperties {

    /** 最低提现金额（分）。斗拱主动取现的最小金额以目标产品为准，部署时按产品配置。 */
    private long minAmountCents = 1000;

    /** 手续费缓冲（分）：可提金额减去缓冲后仍要达到最低金额，否则顺延。 */
    private long feeBufferCents = 0;

    /** 任务是否启用。凌晨的定时任务按北京时间触发，本地开发可以关掉。 */
    private boolean enabled = true;

    /** 每天触发日汇总的时间（北京时间，cron 的 秒 分 时 日 月 周）。 */
    private String cron = "0 10 0 * * *";

    public long getMinAmountCents() { return minAmountCents; }
    public void setMinAmountCents(long minAmountCents) { this.minAmountCents = minAmountCents; }
    public long getFeeBufferCents() { return feeBufferCents; }
    public void setFeeBufferCents(long feeBufferCents) { this.feeBufferCents = feeBufferCents; }
    public boolean isEnabled() { return enabled; }
    public void setEnabled(boolean enabled) { this.enabled = enabled; }
    public String getCron() { return cron; }
    public void setCron(String cron) { this.cron = cron; }
}

 talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalScheduler.java

New file
@@ -0,0 +1,35 @@
package com.talentcard.server.business.merchant;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

/**
 * 北京时间每日凌晨 00:10 触发前一个自然日的自动提现汇总（cron 的时区固定为北京时间，
 * 不依赖服务器或 JVM 的默认时区）。
 */
@Component
public class AutoWithdrawalScheduler {

    private static final Logger log = LoggerFactory.getLogger(AutoWithdrawalScheduler.class);

    private final AutoWithdrawalAggregationService service;
    private final AutoWithdrawalProperties properties;

    public AutoWithdrawalScheduler(AutoWithdrawalAggregationService service, AutoWithdrawalProperties properties) {
        this.service = service;
        this.properties = properties;
    }

    @Scheduled(cron = "${app.merchant.auto-withdrawal.cron:0 10 0 * * *}", zone = "Asia/Shanghai")
    public void aggregateDaily() {
        if (!properties.isEnabled()) return;
        try {
            AutoWithdrawalAggregationService.Result result = service.run();
            log.info("Auto withdrawal aggregation {}", result);
        } catch (RuntimeException exception) {
            log.warn("Auto withdrawal aggregation run failed: {}", exception.getClass().getSimpleName());
        }
    }
}

 talent-card-server/src/main/resources/application.yml

@@ -83,6 +83,13 @@
        enabled: ${HUIFU_CAPABILITY_REFRESH_ENABLED:true}
        stale-hours: ${HUIFU_CAPABILITY_STALE_HOURS:6}
        fixed-delay-ms: ${HUIFU_CAPABILITY_REFRESH_FIXED_DELAY_MS:1800000}
  merchant:
    auto-withdrawal:
      # 每日凌晨按北京时间汇总前一个自然日已核销订单，生成一条商户日汇总提现记录。
      enabled: ${AUTO_WITHDRAWAL_ENABLED:true}
      min-amount-cents: ${AUTO_WITHDRAWAL_MIN_AMOUNT_CENTS:1000}
      fee-buffer-cents: ${AUTO_WITHDRAWAL_FEE_BUFFER_CENTS:0}
      cron: ${AUTO_WITHDRAWAL_CRON:0 10 0 * * *}
  wechat:
    mini-program:
      # The app id must be the same mini-program that invokes uni.login and uni.requestPayment.

 talent-card-server/src/main/resources/db/migration/V133__daily_withdrawal_aggregation.sql

New file
@@ -0,0 +1,24 @@
-- 每日自动提现汇总：提现记录与来源订单的内部关联，以及「一个商户一个自然日一条日汇总记录」的幂等约束。

-- 内部关联表：一笔自动提现汇总了哪些已核销订单。它只用于防止重复提现、失败顺延和退款判断，
-- 不对外提供批次详情或订单明细入口（CONTEXT.md「提现关联」）。每个订单最多出现在一条提现记录里，
-- 由唯一索引 uk_withdrawal_order_assoc_order 在数据库层保证。
create table if not exists merchant_withdrawal_order (
    id bigint not null auto_increment,
    withdrawal_id bigint not null,
    order_id bigint not null,
    amount_cents bigint not null,
    created_at datetime not null default current_timestamp,
    primary key (id),
    unique key uk_withdrawal_order_assoc_order (order_id),
    key idx_withdrawal_order_assoc_withdrawal (withdrawal_id)
) engine=InnoDB default charset=utf8mb4 comment='自动提现记录与来源订单的内部关联';

-- 日汇总的业务日期（北京时间 yyyyMMdd）。只有自动提现写入，人工提交的历史提现记录保持 NULL。
alter table merchant_withdrawal
    add column withdrawal_date char(8) default null comment '自动提现的日汇总业务日期（北京时间 yyyyMMdd）';

-- 「同一商户同一自然日只生成一条日汇总提现记录」在数据库层成立，任务重复执行或并发执行都不会
-- 重复生成提现金额。MySQL 唯一索引允许多个 NULL，因此这条约束不影响 withdrawal_date 为空的行。
alter table merchant_withdrawal
    add unique key uk_merchant_withdrawal_daily (merchant_id, withdrawal_date);

 talent-card-server/src/test/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationTests.java

New file
@@ -0,0 +1,328 @@
package com.talentcard.server.business.merchant;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicLong;

import com.talentcard.server.business.order.OrderFundBoundaryService;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 每日自动提现汇总 seam：按北京时间统计前一个自然日；只汇总已核销、未退款且未进入其它提现记录的订单；
 * 同一商户生成一条日汇总记录并内部保存来源订单关联；最低金额和手续费缓冲不足时顺延；重复执行不重复出账。
 *
 * <p>任务按设计会扫描全部商户，而多个 Spring 测试共用同一个不清空的 schema，因此断言只看本用例自己创建的
 * 商户与业务日；每个用例的业务日也互不相同。
 */
@SpringBootTest
@ActiveProfiles("test")
class AutoWithdrawalAggregationTests {

    private static final DateTimeFormatter BUSINESS_DATE = DateTimeFormatter.ofPattern("yyyyMMdd");

    /** 未来业务日的随机起点 + 每次调用递增，保证同一次运行内每个用例的业务日互不相同。 */
    private static final AtomicLong BUSINESS_DATE_OFFSET =
            new AtomicLong(10_000 + ThreadLocalRandom.current().nextLong(1_000_000));

    @Autowired
    private JdbcTemplate jdbc;

    @Autowired
    private AutoWithdrawalAggregationService service;

    private Long merchantId;
    private Long storeId;
    private Long bankCardId;
    private Long talentId;
    private String suffix;

    @BeforeEach
    void prepareMerchant() {
        suffix = String.valueOf(System.nanoTime());
        jdbc.update("insert into merchant(merchant_name, status, deleted) values (?, 1, 0)", "Auto withdrawal merchant " + suffix);
        merchantId = jdbc.queryForObject("select max(id) from merchant", Long.class);
        jdbc.update("insert into merchant_store(merchant_id, store_name, status, deleted) values (?, ?, 1, 0)", merchantId, "Auto withdrawal store " + suffix);
        storeId = jdbc.queryForObject("select max(id) from merchant_store", Long.class);
        jdbc.update("insert into talent_user(openid, name, mobile, status, deleted) values (?, ?, ?, 1, 0)", "auto-withdrawal-" + suffix, "自动提现用户", "13900000003");
        talentId = jdbc.queryForObject("select max(id) from talent_user", Long.class);
        jdbc.update("""
                insert into merchant_onboarding_application(merchant_id, store_id, application_no, subject_type, subject_name,
                                                            contact_name, contact_mobile, idempotency_key, status, provider_huifu_id)
                values (?, ?, ?, 'individual', 'Auto withdrawal owner', 'Auto withdrawal owner', '13800000001', ?, 'approved', ?)
                """, merchantId, storeId, "MO-AWA-" + suffix, "auto-withdrawal-onboarding-" + suffix, shortReference("H", suffix));
        jdbc.update("""
                insert into merchant_bank_card(merchant_id, store_id, bank_name, branch_name, city_code, card_tail, card_masked,
                                               card_hash, status, idempotency_key, provider_token_no)
                values (?, ?, '中国建设银行', '攀枝花东区支行', '510400', '8888', '**** **** **** 8888', ?, 'approved', ?, ?)
                """, merchantId, storeId, "hash-auto-withdrawal-" + suffix, "auto-withdrawal-card-" + suffix, shortReference("T", suffix));
        bankCardId = jdbc.queryForObject("select max(id) from merchant_bank_card", Long.class);
        jdbc.update("update merchant_onboarding_application set initial_bank_card_id=? where application_no=?", bankCardId, "MO-AWA-" + suffix);
    }

    @Test
    void sumsOnlyTheVerifiedUnrefundedOrdersOfTheBusinessDayIntoOneRecord() {
        LocalDate businessDate = someBusinessDate();
        Long first = verifiedOrder(businessDate, 9000);
        Long second = verifiedOrder(businessDate, 3000);
        Long unpaid = order(1, 0, 0, 0, 7000, businessDate);            // 未支付
        Long unverified = order(1, 1, 0, 0, 5000, businessDate);        // 支付但未核销
        Long refunded = order(1, 1, 1, 2, 4000, businessDate);          // 核销后退款
        Long alreadyInWithdrawal = order(1, 1, 1, 0, 6000, businessDate); // 已进入其它提现记录
        jdbc.update("update benefit_order set withdrawal_status = 1 where id = ?", alreadyInWithdrawal);

        service.run(businessDate);

        Map<String, Object> withdrawal = dailyWithdrawal(businessDate);
        assertThat(withdrawal).containsEntry("amount_cents", 12000L).containsEntry("status", "pending");
        assertThat(sourceOrders(withdrawal)).containsExactlyInAnyOrder(
                new SourceOrder(first, 9000L), new SourceOrder(second, 3000L));
        // 未支付、未核销、已退款的订单没有进入提现记录；已在其它提现记录里的保持原状。
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(first, second, alreadyInWithdrawal)
                .doesNotContain(unpaid, unverified, refunded);
    }

    @Test
    void ordersVerifiedAfterTheBusinessDayWaitForTheNextRun() {
        LocalDate businessDate = someBusinessDate();
        Long early = verifiedOrderAt(businessDate.minusDays(1).atTime(23, 59, 59), 1500);
        Long lastMoment = verifiedOrderAt(businessDate.atTime(23, 59, 59), 2000);
        Long afterWindow = verifiedOrderAt(businessDate.plusDays(1).atStartOfDay(), 3000);

        service.run(businessDate);

        // 窗口右端是业务日 24:00：当天核销的订单都被汇总，更晚核销的留到下一次任务。
        assertThat(sourceOrderIds(businessDate)).containsExactlyInAnyOrder(early, lastMoment);
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(early, lastMoment);

        service.run(businessDate.plusDays(1));

        // 晚于窗口的订单在下一次任务里进入新记录；已经汇总过的订单不会被重复计入。
        assertThat(sourceOrderIds(businessDate.plusDays(1))).containsExactly(afterWindow);
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(early, lastMoment, afterWindow);
        assertThat(allSourceOrderIds()).containsExactlyInAnyOrder(early, lastMoment, afterWindow);
    }

    @Test
    void anAmountBelowTheMinimumIsDeferredToTheNextRun() {
        LocalDate businessDate = someBusinessDate();
        Long small = verifiedOrder(businessDate, 500);

        service.run(businessDate);

        assertThat(dailyWithdrawalOrNull(businessDate)).isNull();
        assertThat(lockedOrderIds()).isEmpty();

        // 第二天新增一笔订单，两天的金额一起顺延到下一次汇总。
        Long later = verifiedOrder(businessDate.plusDays(1), 1500);
        service.run(businessDate.plusDays(1));

        assertThat(dailyWithdrawal(businessDate.plusDays(1))).containsEntry("amount_cents", 2000L);
        assertThat(sourceOrderIds(businessDate.plusDays(1))).contains(small, later);
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(small, later);
    }

    @Test
    void anAmountThatWouldNotCoverTheFeeBufferIsDeferred() {
        AutoWithdrawalProperties properties = new AutoWithdrawalProperties();
        properties.setMinAmountCents(1000);
        properties.setFeeBufferCents(500);
        AutoWithdrawalAggregationService buffered = new AutoWithdrawalAggregationService(
                jdbc, new OrderFundBoundaryService(jdbc),
                new TransactionTemplate(new DataSourceTransactionManager(jdbc.getDataSource())),
                properties);

        LocalDate businessDate = someBusinessDate();
        Long exact = verifiedOrder(businessDate, 1200);

        buffered.run(businessDate);

        // 1200 分虽然过了最低金额，但扣掉 500 分缓冲后不足最低金额，仍然顺延。
        assertThat(dailyWithdrawalOrNull(businessDate)).isNull();
        assertThat(lockedOrderIds()).isEmpty();

        Long extra = verifiedOrder(businessDate, 300);
        buffered.run(businessDate);

        assertThat(dailyWithdrawal(businessDate)).containsEntry("amount_cents", 1500L);
        assertThat(sourceOrderIds(businessDate)).contains(exact, extra);
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(exact, extra);
    }

    @Test
    void repeatingTheTaskDoesNotCreateASecondWithdrawal() {
        LocalDate businessDate = someBusinessDate();
        Long first = verifiedOrder(businessDate, 2000);
        Long second = verifiedOrder(businessDate, 3000);

        service.run(businessDate);
        // 任务跑完后当天又核销了一笔：这一天的记录已经存在，重复执行不会再生成第二笔提现。
        Long late = verifiedOrder(businessDate, 4000);
        service.run(businessDate);

        assertThat(dailyWithdrawalCount(businessDate)).isEqualTo(1);
        assertThat(dailyWithdrawal(businessDate)).containsEntry("amount_cents", 5000L);
        assertThat(sourceOrderIds(businessDate)).containsExactlyInAnyOrder(first, second);
        assertThat(lockedOrderIds()).containsExactlyInAnyOrder(first, second);
        assertThat(lockedOrderIds()).doesNotContain(late);
    }

    @Test
    void theDatabaseRejectsASecondDailyWithdrawalForOneMerchantAndDay() {
        LocalDate businessDate = someBusinessDate();
        verifiedOrder(businessDate, 2000);
        service.run(businessDate);
        String dateText = businessDate.format(BUSINESS_DATE);

        assertThatThrownBy(() -> jdbc.update("""
                insert into merchant_withdrawal(merchant_id, store_id, withdrawal_no, bank_card_id, amount_cents,
                                                idempotency_key, withdrawal_date)
                values (?, ?, ?, ?, 100, ?, ?)
                """, merchantId, storeId, "WA-DUP-" + suffix, bankCardId, "auto-withdrawal-duplicate:" + suffix, dateText))
                .isInstanceOf(DataIntegrityViolationException.class);
    }

    @Test
    void theWithdrawalUsesTheApprovedSettlementCardOfTheMerchant() {
        LocalDate businessDate = someBusinessDate();
        verifiedOrder(businessDate, 2000);

        service.run(businessDate);

        assertThat(dailyWithdrawal(businessDate)).containsEntry("bank_card_id", bankCardId);
    }

    @Test
    void aMerchantWithoutAnApprovedSettlementCardIsNotAggregated() {
        jdbc.update("update merchant_bank_card set provider_token_no = null where id = ?", bankCardId);
        LocalDate businessDate = someBusinessDate();
        Long orderId = verifiedOrder(businessDate, 9000);

        service.run(businessDate);

        assertThat(dailyWithdrawalOrNull(businessDate)).isNull();
        assertThat(lockedOrderIds()).doesNotContain(orderId);
    }

    @Test
    void theDefaultRunAggregatesYesterdayInBeijingTime() {
        LocalDate yesterday = LocalDate.now(ZoneId.of("Asia/Shanghai")).minusDays(1);
        Long orderId = verifiedOrder(yesterday, 2000);

        service.run();

        assertThat(sourceOrderIds(yesterday)).contains(orderId);
        assertThat(jdbc.queryForObject("select withdrawal_date from merchant_withdrawal where merchant_id = ?", String.class, merchantId))
                .isEqualTo(yesterday.format(BUSINESS_DATE));
    }

    @Test
    void theBusinessDayIsOneDayBeforeTheBeijingDate() {
        // 北京时间 2026-03-02 00:30（UTC 还是 3 月 1 日）：业务日是 3 月 1 日，不是 UTC 的 2 月 28 日。
        assertThat(AutoWithdrawalAggregationService.previousBusinessDate(Instant.parse("2026-03-01T16:30:00Z")))
                .isEqualTo(LocalDate.of(2026, 3, 1));
        assertThat(AutoWithdrawalAggregationService.previousBusinessDate(Instant.parse("2026-03-01T15:59:59Z")))
                .isEqualTo(LocalDate.of(2026, 2, 28));
    }

    private LocalDate someBusinessDate() {
        // 共享测试库不清理，同一业务日上的历史提现记录会把订单算成「已进入提现记录」，
        // 所以每个用例取一个互不相同的未来业务日。
        return LocalDate.now(ZoneId.of("Asia/Shanghai")).plusDays(BUSINESS_DATE_OFFSET.getAndIncrement());
    }

    /** 已支付、已核销的订单，核销时间落在业务日 10:00，返回订单 ID。 */
    private Long verifiedOrder(LocalDate businessDate, long payAmount) {
        return verifiedOrderAt(businessDate.atTime(10, 0), payAmount);
    }

    private Long verifiedOrderAt(LocalDateTime verifyTime, long payAmount) {
        Long orderId = order(1, 1, 0, 0, payAmount, verifyTime.toLocalDate());
        jdbc.update("""
                insert into verify_record(verify_no, order_id, talent_id, merchant_id, store_id, verify_type,
                                          verify_amount, verify_time, status)
                values (?, ?, ?, ?, ?, 'ORDER_CODE', ?, ?, 1)
                """, "V-AWA-" + suffix + "-" + orderId, orderId, talentId, merchantId, storeId, payAmount, verifyTime);
        return orderId;
    }

    private Long order(int orderStatus, int verifyStatus, int deleted, int refundStatus, long payAmount, LocalDate createdAt) {
        String orderNo = "AWA-" + suffix + "-" + System.nanoTime();
        jdbc.update("""
                insert into benefit_order(order_no, talent_id, merchant_id, store_id, order_type, order_amount,
                                          subsidy_amount, pay_amount, order_status, verify_status, refund_status,
                                          withdrawal_status, deleted, created_at)
                values (?, ?, ?, ?, 'GROUP_BUY', ?, 0, ?, ?, ?, ?, 0, ?, ?)
                """, orderNo, talentId, merchantId, storeId, payAmount, payAmount, orderStatus, verifyStatus,
                refundStatus, deleted, createdAt.atTime(9, 0));
        return jdbc.queryForObject("select id from benefit_order where order_no = ?", Long.class, orderNo);
    }

    private Map<String, Object> dailyWithdrawal(LocalDate businessDate) {
        Map<String, Object> row = dailyWithdrawalOrNull(businessDate);
        assertThat(row).as("每日汇总记录").isNotNull();
        return row;
    }

    private Map<String, Object> dailyWithdrawalOrNull(LocalDate businessDate) {
        List<Map<String, Object>> rows = jdbc.queryForList(
                "select * from merchant_withdrawal where merchant_id = ? and withdrawal_date = ?",
                merchantId, businessDate.format(BUSINESS_DATE));
        return rows.isEmpty() ? null : rows.get(0);
    }

    private Integer dailyWithdrawalCount(LocalDate businessDate) {
        return jdbc.queryForObject("select count(*) from merchant_withdrawal where merchant_id = ? and withdrawal_date = ?",
                Integer.class, merchantId, businessDate.format(BUSINESS_DATE));
    }

    private List<Long> sourceOrderIds(LocalDate businessDate) {
        Map<String, Object> withdrawal = dailyWithdrawal(businessDate);
        return sourceOrders(withdrawal).stream().map(SourceOrder::orderId).toList();
    }

    /** 通过提现记录主键读关联，避免在测试里再拼一次日期条件。 */
    private List<SourceOrder> sourceOrders(Map<String, Object> withdrawal) {
        return jdbc.query("""
                select order_id, amount_cents from merchant_withdrawal_order where withdrawal_id = ?
                """, (rs, rowNum) -> new SourceOrder(rs.getLong("order_id"), rs.getLong("amount_cents")),
                ((Number) withdrawal.get("id")).longValue());
    }

    private List<Long> lockedOrderIds() {
        return jdbc.queryForList("select id from benefit_order where merchant_id = ? and withdrawal_status = 1",
                Long.class, merchantId);
    }

    /** 本用例商户进入过的全部来源订单，用来验证同一订单不会被两条提现记录重复汇总。 */
    private List<Long> allSourceOrderIds() {
        return jdbc.queryForList("""
                select a.order_id from merchant_withdrawal_order a
                join merchant_withdrawal w on w.id = a.withdrawal_id
                where w.merchant_id = ?
                """, Long.class, merchantId);
    }

    private record SourceOrder(long orderId, long amountCents) { }

    /** 通道标识在库里限长 32 位，测试用后缀的尾部保证唯一且够短。 */
    private String shortReference(String prefix, String suffix) {
        return prefix + suffix.substring(Math.max(0, suffix.length() - 8));
    }
}