又又更新

shinnosuke

2026-08-28 88c94d51fb9f82e856245908696ea4a970541a33

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

@@ -3,6 +3,7 @@
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@@ -11,6 +12,15 @@
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.business.merchant.auth.MerchantPrincipal;
import com.talentcard.server.business.payment.huifu.HuifuIndividualOnboardingCommand;
import com.talentcard.server.business.payment.huifu.HuifuIndividualOnboardingGateway;
import com.talentcard.server.business.payment.huifu.HuifuEnterpriseOnboardingCommand;
import com.talentcard.server.business.payment.huifu.HuifuEnterpriseOnboardingGateway;
import com.talentcard.server.business.payment.huifu.HuifuPaymentGatewayException;
import com.talentcard.server.business.payment.huifu.HuifuPaymentProperties;
import com.talentcard.server.business.payment.huifu.HuifuUserBusinessOpenCommand;
import com.talentcard.server.business.payment.huifu.HuifuUserBusinessOpenGateway;
import com.talentcard.server.common.SensitiveDataCrypto;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.springframework.jdbc.core.JdbcTemplate;
@@ -20,8 +30,22 @@
@Service
public class MerchantSettlementService {
    private final JdbcTemplate jdbc;
    private final SensitiveDataCrypto sensitiveDataCrypto;
    private final HuifuIndividualOnboardingGateway individualOnboardingGateway;
    private final HuifuEnterpriseOnboardingGateway enterpriseOnboardingGateway;
    private final HuifuMerchantWithdrawalService withdrawalService;
    private final HuifuUserBusinessOpenGateway userBusinessOpenGateway;
    private final HuifuPaymentProperties huifuPaymentProperties;

    public MerchantSettlementService(JdbcTemplate jdbc) { this.jdbc = jdbc; }
    public MerchantSettlementService(JdbcTemplate jdbc, SensitiveDataCrypto sensitiveDataCrypto, HuifuIndividualOnboardingGateway individualOnboardingGateway, HuifuEnterpriseOnboardingGateway enterpriseOnboardingGateway, HuifuMerchantWithdrawalService withdrawalService, HuifuUserBusinessOpenGateway userBusinessOpenGateway, HuifuPaymentProperties huifuPaymentProperties) {
        this.jdbc = jdbc;
        this.sensitiveDataCrypto = sensitiveDataCrypto;
        this.individualOnboardingGateway = individualOnboardingGateway;
        this.enterpriseOnboardingGateway = enterpriseOnboardingGateway;
        this.withdrawalService = withdrawalService;
        this.userBusinessOpenGateway = userBusinessOpenGateway;
        this.huifuPaymentProperties = huifuPaymentProperties;
    }

    @Transactional(readOnly = true)
    public Map<String, Object> onboardingStatus(MerchantPrincipal principal) {
@@ -39,12 +63,49 @@
        if (!active.isEmpty()) return onboarding(active.get(0));
        boolean enterprise = "enterprise".equals(request.subjectType());
        if (enterprise && (request.creditCode() == null || !request.creditCode().trim().matches("[0-9A-Z]{18}"))) throw new BusinessException(ErrorCode.BAD_REQUEST, "请输入18位统一社会信用代码");
        if (enterprise && (request.subjectIdNumber() == null || !request.subjectIdNumber().trim().matches("(?:\\d{15}|\\d{17}[0-9X])"))) throw new BusinessException(ErrorCode.BAD_REQUEST, "请输入法人正确的身份证号");
        if (enterprise && (request.subjectCertificateBeginDate() == null || request.subjectCertificateBeginDate().isBlank())) throw new BusinessException(ErrorCode.BAD_REQUEST, "请输入营业执照成立日期");
        if (!enterprise && (request.subjectIdNumber() == null || !request.subjectIdNumber().trim().matches("(?:\\d{15}|\\d{17}[0-9X])"))) throw new BusinessException(ErrorCode.BAD_REQUEST, "请输入正确的身份证号");
        requireCertificatePeriod(request.subjectCertificateValidityType(), request.subjectCertificateBeginDate(), request.subjectCertificateEndDate(), "Identity document");
        if (enterprise) {
            requireCertificatePeriod(request.licenseValidityType(), request.licenseBeginDate(), request.licenseEndDate(), "Business licence");
            if (blank(request.registeredProvinceCode()) || blank(request.registeredCityCode()) || blank(request.registeredDistrictCode())) throw new BusinessException(ErrorCode.BAD_REQUEST, "Enterprise registration address codes are required");
        }
        String no = BusinessIdGenerator.next("MO");
        jdbc.update("insert into merchant_onboarding_application(merchant_id, store_id, application_no, subject_type, subject_name, credit_code, subject_id_no_masked, registered_region, registered_address, contact_name, contact_mobile, idempotency_key) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", principal.merchantId(), principal.storeId(), no, request.subjectType(), request.subjectName().trim(), clean(request.creditCode()), maskId(request.subjectIdNumber()), clean(request.registeredRegion()), clean(request.registeredAddress()), request.contactName().trim(), request.contactMobile().trim(), key.trim());
        String certificateValidityType = certificateValidityType(request.subjectCertificateValidityType());
        String certificateBeginDate = certificateBeginDate(request.subjectCertificateBeginDate());
        jdbc.update("insert into merchant_onboarding_application(merchant_id, store_id, application_no, subject_type, subject_name, credit_code, license_validity_type, license_begin_date, license_end_date, subject_id_no_masked, subject_id_no_encrypted, subject_cert_validity_type, subject_cert_begin_date, subject_cert_end_date, registered_region, registered_province_code, registered_city_code, registered_district_code, registered_address, contact_name, contact_mobile, idempotency_key) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", principal.merchantId(), principal.storeId(), no, request.subjectType(), request.subjectName().trim(), clean(request.creditCode()), certificateValidityType(request.licenseValidityType()), certificateBeginDate(request.licenseBeginDate()), certificateEndDate(request.licenseEndDate()), maskId(request.subjectIdNumber()), sensitiveDataCrypto.encrypt(clean(request.subjectIdNumber()).toUpperCase()), certificateValidityType, certificateBeginDate, certificateEndDate(request.subjectCertificateEndDate()), clean(request.registeredRegion()), clean(request.registeredProvinceCode()), clean(request.registeredCityCode()), clean(request.registeredDistrictCode()), clean(request.registeredAddress()), request.contactName().trim(), request.contactMobile().trim(), key.trim());
        if ("individual".equals(request.subjectType())) submitIndividualOnboarding(no, request);
        else submitEnterpriseOnboarding(no, request);
        return onboarding(rows("select * from merchant_onboarding_application where application_no = ?", no).get(0));
    }

    private void submitEnterpriseOnboarding(String applicationNo, MerchantOnboardingRequest request) {
        try {
            HuifuEnterpriseOnboardingGateway.Result result = enterpriseOnboardingGateway.create(new HuifuEnterpriseOnboardingCommand(
                    applicationNo, request.subjectName().trim(), request.creditCode().trim(),
                    certificateValidityType(request.licenseValidityType()), certificateBeginDate(request.licenseBeginDate()), certificateEndDate(request.licenseEndDate()),
                    clean(request.registeredProvinceCode()), clean(request.registeredCityCode()), clean(request.registeredDistrictCode()), clean(request.registeredAddress()), request.subjectName().trim(),
                    clean(request.subjectIdNumber()).toUpperCase(), certificateValidityType(request.subjectCertificateValidityType()),
                    certificateBeginDate(request.subjectCertificateBeginDate()), certificateEndDate(request.subjectCertificateEndDate()), request.contactName().trim(), request.contactMobile().trim()));
            if (result.accepted() && !result.huifuId().isBlank()) jdbc.update("update merchant_onboarding_application set status='approved', provider_huifu_id=?, reviewed_at=now(), reject_reason=null where application_no=? and status='pending'", result.huifuId(), applicationNo);
            else if (!result.accepted()) jdbc.update("update merchant_onboarding_application set status='rejected', reject_reason=?, reviewed_at=now() where application_no=? and status='pending'", providerReason(result.code(), result.message()), applicationNo);
            else jdbc.update("update merchant_onboarding_application set reject_reason=? where application_no=? and status='pending'", "Huifu accepted the request but did not return huifu_id", applicationNo);
        } catch (HuifuPaymentGatewayException exception) {
            jdbc.update("update merchant_onboarding_application set reject_reason=? where application_no=? and status='pending'", "Huifu submission outcome is pending confirmation", applicationNo);
        }
    }

    private void submitIndividualOnboarding(String applicationNo, MerchantOnboardingRequest request) {
        try {
            HuifuIndividualOnboardingGateway.Result result = individualOnboardingGateway.create(new HuifuIndividualOnboardingCommand(applicationNo, request.subjectName().trim(), request.subjectIdNumber().trim().toUpperCase(), certificateValidityType(request.subjectCertificateValidityType()), certificateBeginDate(request.subjectCertificateBeginDate()), certificateEndDate(request.subjectCertificateEndDate()), request.contactMobile().trim(), clean(request.registeredAddress())));
            if (result.accepted() && !result.huifuId().isBlank()) jdbc.update("update merchant_onboarding_application set status='approved', provider_huifu_id=?, reviewed_at=now(), reject_reason=null where application_no=? and status='pending'", result.huifuId(), applicationNo);
            else if (!result.accepted()) jdbc.update("update merchant_onboarding_application set status='rejected', reject_reason=?, reviewed_at=now() where application_no=? and status='pending'", providerReason(result), applicationNo);
            else jdbc.update("update merchant_onboarding_application set reject_reason=? where application_no=? and status='pending'", "Huifu accepted the request but did not return huifu_id", applicationNo);
        } catch (HuifuPaymentGatewayException exception) {
            jdbc.update("update merchant_onboarding_application set reject_reason=? where application_no=? and status='pending'", "Huifu submission outcome is pending confirmation", applicationNo);
        }
    }
    @Transactional(readOnly = true)
    public List<Map<String, Object>> bankCards(MerchantPrincipal principal) {
        store(principal);
@@ -57,14 +118,20 @@
        List<Map<String, Object>> repeated = rows("select * from merchant_bank_card where merchant_id = ? and idempotency_key = ? limit 1", principal.merchantId(), key.trim());
        if (!repeated.isEmpty()) return bankCard(repeated.get(0));
        String number = request.cardNumber().trim(); String hash = sha256(number);
        List<Map<String, Object>> same = rows("select * from merchant_bank_card where merchant_id = ? and card_hash = ? limit 1", principal.merchantId(), hash);
        if (!same.isEmpty()) return bankCard(same.get(0));
        jdbc.update("insert into merchant_bank_card(merchant_id, store_id, bank_name, branch_name, city_code, card_tail, card_masked, card_hash, idempotency_key) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", principal.merchantId(), principal.storeId(), request.bankName().trim(), request.branchName().trim(), request.cityCode().trim(), number.substring(number.length() - 4), "**** **** **** " + number.substring(number.length() - 4), hash, key.trim());
        return bankCard(rows("select * from merchant_bank_card where merchant_id = ? and card_hash = ?", principal.merchantId(), hash).get(0));
        List<Map<String, Object>> branchRecords = rows("select clearing_code, branch_name, bank_name, head_bank_code from bank_branch_directory where clearing_code = ? limit 1", request.branchCode().trim());
        if (branchRecords.isEmpty()) throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择真实开户行");
        Map<String, Object> branch = branchRecords.get(0);
        List<Map<String, Object>> active = rows("select id from merchant_bank_card where merchant_id = ? and card_hash = ? and status in ('pending', 'approved') order by id desc limit 1", principal.merchantId(), hash);
        if (!active.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "该银行卡已有审核中或已通过的申请，请勿重复添加");
        jdbc.update("insert into merchant_bank_card(merchant_id, store_id, bank_name, branch_name, branch_code, city_code, card_tail, card_masked, card_hash, card_no_encrypted, idempotency_key) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", principal.merchantId(), principal.storeId(), clean(branch.get("bankName")), clean(branch.get("branchName")), clean(branch.get("clearingCode")), request.cityCode().trim(), number.substring(number.length() - 4), "**** **** **** " + number.substring(number.length() - 4), hash, sensitiveDataCrypto.encrypt(number), key.trim());
        Map<String, Object> card = rows("select * from merchant_bank_card where merchant_id = ? and idempotency_key = ? limit 1", principal.merchantId(), key.trim()).get(0);
        submitBankCardToHuifu(principal, card, number, branch);
        return bankCard(rows("select * from merchant_bank_card where id = ?", card.get("id")).get(0));
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> bankBranches(String cityCode, String keyword, int page, int size) {
    public PageResult<Map<String, Object>> bankBranches(String cityCode, String cityName, String keyword, int page, int size) {
        if (jdbc != null) return bankBranchesFromDirectory(cityCode, cityName, keyword, page, size);
        // The payment provider's bank directory should replace this curated fallback during Huifu onboarding.
        List<Map<String, Object>> all = List.of(
                branch("CCB510400", "中国建设银行攀枝花东区支行营业部", "中国建设银行"), branch("CCB510401", "中国建设银行攀枝花分行营业部", "中国建设银行"),
@@ -73,6 +140,33 @@
        List<Map<String, Object>> matched = all.stream().filter(value -> query.isEmpty() || (value.get("branchName") + "" + value.get("bankName")).toLowerCase().contains(query)).toList();
        int start = Math.min((safePage - 1) * safeSize, matched.size()); int end = Math.min(start + safeSize, matched.size());
        return new PageResult<>(matched.subList(start, end), safePage, safeSize, matched.size());
    }

    private PageResult<Map<String, Object>> bankBranchesFromDirectory(String cityCode, String cityName, String keyword, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 50);
        StringBuilder where = new StringBuilder(" where 1=1");
        List<Object> params = new ArrayList<>();
        String cityFilter = blank(cityName) ? clean(cityCode) : clean(cityName);
        int citySeparator = cityFilter.lastIndexOf(' ');
        if (citySeparator >= 0 && citySeparator + 1 < cityFilter.length()) cityFilter = cityFilter.substring(citySeparator + 1).trim();
        cityFilter = cityKeyword(cityFilter);
        if (!blank(cityFilter)) {
            where.append(" and (branch_name like ? or bank_name like ?)");
            String match = "%" + cityFilter + "%";
            params.add(match);
            params.add(match);
        }
        if (!blank(keyword)) {
            where.append(" and (branch_name like ? or bank_name like ? or clearing_code = ?)");
            String match = "%" + clean(keyword) + "%";
            params.add(match);
            params.add(match);
            params.add(clean(keyword));
        }
        Long total = jdbc.queryForObject("select count(*) from bank_branch_directory" + where, Long.class, params.toArray());
        List<Map<String, Object>> values = rows("select clearing_code as id, clearing_code as branch_code, branch_name, head_bank_code, bank_name from bank_branch_directory" + where + " order by branch_name limit ? offset ?", appendPaging(params, safeSize, (safePage - 1) * safeSize));
        return new PageResult<>(values, safePage, safeSize, total == null ? 0 : total);
    }

    @Transactional(readOnly = true)
@@ -104,19 +198,81 @@
        if (request.amountCents() > available) throw new BusinessException(ErrorCode.CONFLICT, "提现金额不能超过当前可提金额");
        String no = BusinessIdGenerator.next("MW");
        jdbc.update("insert into merchant_withdrawal(merchant_id, store_id, withdrawal_no, bank_card_id, amount_cents, idempotency_key) values (?, ?, ?, ?, ?, ?)", principal.merchantId(), principal.storeId(), no, cardId, request.amountCents(), key.trim());
        Long withdrawalId = jdbc.queryForObject("select id from merchant_withdrawal where withdrawal_no = ?", Long.class, no);
        withdrawalService.dispatch(withdrawalId);
        return withdrawal(rows("select w.*, c.bank_name, c.card_masked from merchant_withdrawal w join merchant_bank_card c on c.id = w.bank_card_id where w.withdrawal_no = ?", no).get(0));
    }

    private void requireApproved(MerchantPrincipal principal) { if (!"approved".equals(onboardingStatus(principal).get("status"))) throw new BusinessException(ErrorCode.CONFLICT, "商户进件尚未审核通过"); }
    private void submitBankCardToHuifu(MerchantPrincipal principal, Map<String, Object> card, String cardNumber, Map<String, Object> branch) {
        List<Map<String, Object>> onboarding = rows("select * from merchant_onboarding_application where merchant_id=? and store_id=? and status='approved' and provider_huifu_id is not null order by id desc limit 1", principal.merchantId(), principal.storeId());
        if (onboarding.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "Huifu merchant onboarding is not approved");
        Map<String, Object> subject = onboarding.get(0);
        String bankCode = clean(branch.get("headBankCode"));
        if (bankCode.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "The selected bank branch has no Huifu bank code");
        boolean enterprise = "enterprise".equalsIgnoreCase(clean(subject.get("subjectType")));
        String cardName = enterprise ? clean(branch.get("bankName")) : clean(subject.get("subjectName"));
        if (!enterprise && cardName.isBlank()) throw new BusinessException(ErrorCode.CONFLICT, "进件姓名缺失，无法绑定个人银行卡");
        String requestNo = BusinessIdGenerator.next("BC");
        try {
            HuifuUserBusinessOpenGateway.Result result = userBusinessOpenGateway.create(new HuifuUserBusinessOpenCommand(
                    requestNo, clean(subject.get("providerHuifuId")), huifuPaymentProperties.getMerchantId().trim(), enterprise ? "0" : "1", cardName, cardNumber,
                    provinceCode(clean(card.get("cityCode"))), clean(card.get("cityCode")), bankCode, clean(branch.get("clearingCode")), clean(branch.get("branchName")),
                    sensitiveDataCrypto.decrypt(clean(subject.get("subjectIdNoEncrypted"))), certificateValidityType(clean(subject.get("subjectCertValidityType"))), clean(subject.get("subjectCertBeginDate")), clean(subject.get("subjectCertEndDate")), clean(subject.get("contactMobile"))));
            if (result.accepted() && !result.tokenNo().isBlank()) jdbc.update("update merchant_bank_card set status='approved', provider_token_no=?, provider_apply_no=?, provider_request_no=?, provider_response_code=?, provider_response_message=?, reviewed_at=now(), reject_reason=null where id=?", result.tokenNo(), result.applyNo(), requestNo, result.code(), result.message(), card.get("id"));
            else if (result.accepted()) jdbc.update("update merchant_bank_card set status='pending', provider_apply_no=?, provider_request_no=?, provider_response_code=?, provider_response_message=?, reject_reason=null where id=?", result.applyNo(), requestNo, result.code(), result.message(), card.get("id"));
            else jdbc.update("update merchant_bank_card set status='rejected', provider_request_no=?, provider_response_code=?, provider_response_message=?, reject_reason=?, reviewed_at=now() where id=?", requestNo, result.code(), result.message(), providerReason(result.code(), result.message()), card.get("id"));
        } catch (HuifuPaymentGatewayException exception) {
            jdbc.update("update merchant_bank_card set provider_request_no=?, reject_reason=? where id=?", requestNo, "Huifu submission outcome is pending confirmation", card.get("id"));
        }
    }

    @Transactional
    @SuppressWarnings("unchecked")
    public boolean handleBankCardCallback(Map<String, Object> payload) {
        Map<String, Object> data = payload;
        Object nested = payload.get("data");
        if (nested instanceof String value && !value.isBlank()) {
            try { data = com.alibaba.fastjson.JSON.parseObject(value, Map.class); } catch (Exception ignored) { }
        } else if (nested instanceof Map<?, ?> value) data = (Map<String, Object>) value;
        String requestNo = clean(data.get("req_seq_id"));
        if (requestNo.isBlank()) requestNo = clean(payload.get("req_seq_id"));
        if (requestNo.isBlank()) return false;
        List<Map<String, Object>> cards = rows("select * from merchant_bank_card where provider_request_no=? limit 1 for update", requestNo);
        if (cards.isEmpty()) return false;
        String tokenNo = clean(data.get("token_no"));
        String code = clean(data.get("sub_resp_code")); if (code.isBlank()) code = clean(data.get("resp_code"));
        String message = clean(data.get("sub_resp_desc")); if (message.isBlank()) message = clean(data.get("resp_desc"));
        String applyNo = clean(data.get("apply_no"));
        if (!tokenNo.isBlank() && providerSuccess(code)) jdbc.update("update merchant_bank_card set status='approved', provider_token_no=?, provider_apply_no=coalesce(nullif(?,''),provider_apply_no), provider_response_code=?, provider_response_message=?, reviewed_at=now(), reject_reason=null where id=? and status='pending'", tokenNo, applyNo, code, message, cards.get(0).get("id"));
        else if (!providerSuccess(code)) jdbc.update("update merchant_bank_card set status='rejected', provider_response_code=?, provider_response_message=?, reject_reason=?, reviewed_at=now() where id=? and status='pending'", code, message, providerReason(code, message), cards.get(0).get("id"));
        else jdbc.update("update merchant_bank_card set provider_apply_no=coalesce(nullif(?,''),provider_apply_no), provider_response_code=?, provider_response_message=? where id=? and status='pending'", applyNo, code, message, cards.get(0).get("id"));
        return true;
    }
    private void requireKey(String key) { if (key == null || key.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "缺少幂等键"); }
    private void store(MerchantPrincipal principal) { if (rows("select id from merchant_store where id = ? and merchant_id = ? and status = 1 and deleted = 0", principal.storeId(), principal.merchantId()).isEmpty()) throw new BusinessException(ErrorCode.FORBIDDEN, "当前门店不可操作"); }
    private Map<String, Object> onboarding(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "applicationNo", row.get("applicationNo"), "status", row.get("status"), "reason", clean(row.get("rejectReason")), "submittedAt", String.valueOf(row.get("createdAt"))); }
    private Map<String, Object> onboarding(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "applicationNo", row.get("applicationNo"), "status", row.get("status"), "subjectType", clean(row.get("subjectType")), "reason", clean(row.get("rejectReason")), "submittedAt", String.valueOf(row.get("createdAt"))); }
    private Map<String, Object> bankCard(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "bankName", row.get("bankName"), "branchName", row.get("branchName"), "cardMasked", row.get("cardMasked"), "status", row.get("status"), "reason", clean(row.get("rejectReason")), "createdAt", String.valueOf(row.get("createdAt"))); }
    private Map<String, Object> withdrawal(Map<String, Object> row) { Map<String, Object> value = new LinkedHashMap<>(); value.put("id", String.valueOf(row.get("id"))); value.put("withdrawalNo", row.get("withdrawalNo")); value.put("amountCents", row.get("amountCents")); value.put("status", row.get("status")); value.put("reason", clean(row.get("failureReason"))); value.put("bankName", clean(row.get("bankName"))); value.put("cardMasked", clean(row.get("cardMasked"))); value.put("createdAt", String.valueOf(row.get("createdAt"))); value.put("reviewedAt", clean(row.get("reviewedAt"))); value.put("paidAt", clean(row.get("paidAt"))); return value; }
    private Map<String, Object> branch(String id, String branchName, String bankName) { return Map.of("id", id, "branchName", branchName, "bankName", bankName); }
    private Object[] appendPaging(List<Object> params, int size, int offset) { Object[] values = new Object[params.size() + 2]; for (int index = 0; index < params.size(); index++) values[index] = params.get(index); values[params.size()] = size; values[params.size() + 1] = offset; return values; }
    private List<Map<String, Object>> rows(String sql, Object... args) { return DatabaseRows.normalize(jdbc.queryForList(sql, args)); }
    private Long number(String value) { try { return Long.valueOf(value); } catch (Exception ignored) { throw new BusinessException(ErrorCode.BAD_REQUEST, "编号无效"); } }
    private String clean(Object value) { return value == null ? "" : String.valueOf(value).trim(); }
    private String cityKeyword(String value) { String text = clean(value); for (String suffix : List.of("自治州", "地区", "盟", "市", "州")) if (text.endsWith(suffix)) return text.substring(0, text.length() - suffix.length()); return text; }
    private String certificateValidityType(String value) { return "long_term".equals(value) ? "1" : "0"; }
    private String certificateBeginDate(String value) { return clean(value).replace("-", ""); }
    private String certificateEndDate(String value) { return certificateBeginDate(value); }
    private String provinceCode(String cityCode) { return cityCode != null && cityCode.matches("\\d{6}") ? cityCode.substring(0, 2) + "0000" : ""; }
    private void requireCertificatePeriod(String validityType, String beginDate, String endDate, String label) {
        if (blank(beginDate)) throw new BusinessException(ErrorCode.BAD_REQUEST, label + " begin date is required");
        if (!"long_term".equals(validityType) && blank(endDate)) throw new BusinessException(ErrorCode.BAD_REQUEST, label + " end date is required when it is not long-term");
        if (!blank(endDate) && clean(beginDate).compareTo(clean(endDate)) > 0) throw new BusinessException(ErrorCode.BAD_REQUEST, label + " end date must not precede its begin date");
    }
    private boolean blank(String value) { return value == null || value.isBlank(); }
    private String providerReason(HuifuIndividualOnboardingGateway.Result result) { return providerReason(result.code(), result.message()); }
    private String providerReason(String code, String message) { String value = clean(message); return value.isEmpty() ? "Huifu onboarding rejected: " + clean(code) : value; }
    private boolean providerSuccess(String code) { return "00000000".equals(code) || "10000".equals(code) || "0".equals(code) || "SUCCESS".equalsIgnoreCase(code); }
    private String maskId(String id) { String value = clean(id); return value.length() < 8 ? "" : value.substring(0, 3) + "********" + value.substring(value.length() - 4); }
    private String sha256(String value) { try { byte[] digest = MessageDigest.getInstance("SHA-256").digest(value.getBytes(StandardCharsets.UTF_8)); StringBuilder result = new StringBuilder(); for (byte valueByte : digest) result.append(String.format("%02x", valueByte)); return result.toString(); } catch (Exception exception) { throw new IllegalStateException("Unable to hash bank card", exception); } }
}