package com.talentcard.server.business.merchant;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.util.LinkedHashMap;
|
import java.util.Map;
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.boot.ApplicationArguments;
|
import org.springframework.boot.ApplicationRunner;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Component;
|
|
/** Keeps merchant onboarding additive columns available when Flyway is disabled in the deployment. */
|
@Slf4j
|
@Component
|
public class MerchantOnboardingSchemaInitializer implements ApplicationRunner {
|
/** DDL 等锁上限：拿不到就放弃这一列，绝不把整个服务拖住。 */
|
private static final int DDL_LOCK_WAIT_TIMEOUT_SECONDS = 5;
|
private final JdbcTemplate jdbc;
|
|
public MerchantOnboardingSchemaInitializer(JdbcTemplate jdbc) { this.jdbc = jdbc; }
|
|
@Override
|
public void run(ApplicationArguments args) {
|
try {
|
Map<String, String> columns = new LinkedHashMap<>();
|
columns.put("merchant_name", "varchar(128) default null");
|
columns.put("merchant_short_name", "varchar(128) default null");
|
columns.put("company_type", "varchar(64) default null");
|
columns.put("business_license_url", "varchar(512) default null");
|
columns.put("established_date", "char(10) default null");
|
columns.put("registered_capital", "varchar(64) default null");
|
columns.put("legal_id_front_url", "varchar(512) default null");
|
columns.put("legal_id_back_url", "varchar(512) default null");
|
columns.put("legal_certificate_address", "varchar(255) default null");
|
columns.put("administrator_email", "varchar(128) default null");
|
// 进件入口在插入申请时就写这些列（企业进件还要写入营业执照有效期）；历史迁移漏建了它们，
|
// 缺少时商户第一次提交进件就会以 SQL 语法错误失败，见 V137/V138。
|
columns.put("registered_province_code", "varchar(6) default null");
|
columns.put("registered_city_code", "varchar(6) default null");
|
columns.put("registered_district_code", "varchar(6) default null");
|
columns.put("license_validity_type", "varchar(8) default null");
|
columns.put("license_begin_date", "char(8) default null");
|
columns.put("license_end_date", "char(8) default null");
|
columns.put("subject_cert_validity_type", "varchar(8) default null");
|
columns.put("subject_cert_begin_date", "char(8) default null");
|
columns.put("subject_cert_end_date", "char(8) default null");
|
columns.put("initial_bank_card_id", "bigint default null");
|
// 结算凭证图（对公=开户许可证、对私/个体工商户=银行卡卡号面）。进件时要上报通道，
|
// 而进件在通道侧配置失败后需要能**重新提交**；不存这一列就没法复用商户填过的图，
|
// 只能让商户把整张表重填一遍——实际就是为了这个原因反复重填过。
|
columns.put("settlement_card_front_url", "varchar(512) default null");
|
columns.put("provider_huifu_id", "varchar(32) default null");
|
columns.put("provider_apply_no", "varchar(32) default null");
|
columns.put("provider_response_code", "varchar(32) default null");
|
columns.put("provider_response_message", "varchar(512) default null");
|
columns.put("wechat_config_status", "varchar(16) not null default 'not_started'");
|
columns.put("wechat_config_request_no", "varchar(32) default null");
|
columns.put("wechat_config_response_code", "varchar(32) default null");
|
columns.put("wechat_config_response_message", "varchar(512) default null");
|
columns.put("business_open_status", "varchar(16) not null default 'not_started'");
|
columns.put("business_open_request_no", "varchar(32) default null");
|
columns.put("business_open_apply_no", "varchar(32) default null");
|
columns.put("business_open_response_code", "varchar(32) default null");
|
columns.put("business_open_response_message", "varchar(512) default null");
|
columns.put("merchant_stat", "varchar(32) default null");
|
columns.put("auto_settle_status", "char(1) default null");
|
columns.put("cash_switch_status", "char(1) default null");
|
columns.put("cash_type", "varchar(8) default null");
|
columns.put("capability_response_code", "varchar(32) default null");
|
columns.put("capability_response_message", "varchar(512) default null");
|
columns.put("capability_verified_at", "datetime default null");
|
for (Map.Entry<String, String> column : columns.entrySet()) {
|
if (!hasColumn("merchant_onboarding_application", column.getKey())) addColumn("merchant_onboarding_application", column.getKey(), column.getValue());
|
}
|
Map<String, String> bankCardColumns = new LinkedHashMap<>();
|
bankCardColumns.put("provider_token_no", "varchar(32) default null");
|
bankCardColumns.put("provider_apply_no", "varchar(32) default null");
|
bankCardColumns.put("provider_request_no", "varchar(32) default null");
|
bankCardColumns.put("provider_response_code", "varchar(32) default null");
|
bankCardColumns.put("provider_response_message", "varchar(512) default null");
|
bankCardColumns.put("card_no_encrypted", "varchar(1024) default null");
|
bankCardColumns.put("branch_code", "varchar(12) default null");
|
for (Map.Entry<String, String> column : bankCardColumns.entrySet()) {
|
if (!hasColumn("merchant_bank_card", column.getKey())) addColumn("merchant_bank_card", column.getKey(), column.getValue());
|
}
|
} catch (Exception exception) {
|
log.warn("Merchant onboarding schema initialization failed", exception);
|
}
|
}
|
|
/**
|
* 加列，但**绝不无限期等锁**。
|
*
|
* <p>`ALTER TABLE` 需要排他元数据锁。只要表上还有一个没提交的事务（哪怕它已经读完、
|
* {@code trx_query} 为空），DDL 就会一直 PENDING；而 MySQL 默认 `lock_wait_timeout` 是
|
* 数万秒，于是 DDL 会一直挂着，**同时把该表上所有后续查询全部排队**，
|
* 最终借光连接池、整个后台不可用。2026-09-15 就是这样瘫掉的：
|
*
|
* <pre>
|
* conn 201 SHARED_READ(GRANTED) 空闲未提交事务，已持有 280 秒
|
* conn 202 EXCLUSIVE(PENDING) alter table ... add column settlement_card_front_url
|
* 其余会话 SHARED_READ(PENDING) 全部排队
|
* </pre>
|
*
|
* <p>所以这里显式把等待上限压到几秒：拿不到锁就放弃这一列，记一条日志，
|
* 让应用照常启动（缺列只影响重试功能，不影响整个服务）。SQL 里用
|
* {@code SET SESSION} 而非驱动参数，避免依赖 JDBC URL 是否带 allowMultiQueries。
|
*/
|
private void addColumn(String tableName, String columnName, String definition) {
|
try {
|
jdbc.execute("set session lock_wait_timeout = " + DDL_LOCK_WAIT_TIMEOUT_SECONDS);
|
jdbc.execute("alter table " + tableName + " add column " + columnName + " " + definition);
|
log.info("Merchant onboarding schema: added {}.{}", tableName, columnName);
|
} catch (Exception exception) {
|
log.warn("Merchant onboarding schema: could not add {}.{} within {}s (table busy); it will be retried on the next restart. cause={}",
|
tableName, columnName, DDL_LOCK_WAIT_TIMEOUT_SECONDS, exception.getClass().getSimpleName());
|
}
|
}
|
|
private boolean hasColumn(String tableName, String columnName) throws Exception {
|
try (Connection connection = jdbc.getDataSource().getConnection(); ResultSet result = connection.getMetaData().getColumns(connection.getCatalog(), null, tableName, columnName)) {
|
if (result.next()) return true;
|
}
|
try (Connection connection = jdbc.getDataSource().getConnection(); ResultSet result = connection.getMetaData().getColumns(connection.getCatalog(), null, tableName.toUpperCase(), columnName.toUpperCase())) {
|
return result.next();
|
}
|
}
|
}
|
