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 tables and 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;
|
|
/**
|
* 进件新增的表，与迁移里的建表语句逐字对齐（V144）。
|
*
|
* <p>开发库（和任何 `spring.flyway.enabled=false` 的部署）不会自动补表：Flyway 不跑，而这个
|
* 初始化器原来只会加列。结果是 V144 建的两张表在开发库永远不存在，`GET
|
* /api/merchant/onboarding/status` 的第一个请求就以
|
* `Table 'talent_card.merchant_onboarding_progress' doesn't exist` 500——测试库有 Flyway，
|
* 看不出来。
|
*/
|
private static final Map<String, String> TABLES = new LinkedHashMap<>();
|
static {
|
TABLES.put("merchant_onboarding_progress", """
|
create table if not exists merchant_onboarding_progress (
|
application_id bigint not null primary key,
|
stages_json text not null,
|
last_synced_at datetime null,
|
sync_error varchar(512) not null default '',
|
sync_owner varchar(36) null,
|
sync_until datetime null,
|
updated_at datetime not null default current_timestamp on update current_timestamp
|
)""");
|
TABLES.put("merchant_onboarding_attempt", """
|
create table if not exists merchant_onboarding_attempt (
|
id bigint not null auto_increment primary key,
|
application_id bigint not null,
|
step varchar(24) not null,
|
request_no varchar(32) not null,
|
request_date char(8) not null,
|
apply_no varchar(32) null,
|
status varchar(24) not null,
|
response_code varchar(32) null,
|
response_message varchar(512) null,
|
created_at datetime not null default current_timestamp,
|
unique key uk_onboarding_attempt_request (request_no),
|
key idx_onboarding_attempt_step (application_id, step, id)
|
)""");
|
}
|
|
private final JdbcTemplate jdbc;
|
|
public MerchantOnboardingSchemaInitializer(JdbcTemplate jdbc) { this.jdbc = jdbc; }
|
|
static Map<String, String> tableDdl() { return Map.copyOf(TABLES); }
|
|
@Override
|
public void run(ApplicationArguments args) {
|
try {
|
for (Map.Entry<String, String> table : TABLES.entrySet()) {
|
if (!hasTable(table.getKey())) createTable(table.getKey(), table.getValue());
|
}
|
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");
|
// 商户端「立即核实」的冷却时间戳，见 V148。
|
columns.put("capability_sync_until", "datetime default null");
|
// 法人手机号：微信实名认证走法人时的必填项，实名接口的 name／mobile／id_card_number
|
// 都必须是法人本人的，不能用联系人手机号顶替。见 V145 的说明。
|
columns.put("legal_mobile", "varchar(20) default null");
|
columns.put("realname_status", "varchar(16) not null default 'not_started'");
|
columns.put("realname_request_no", "varchar(32) default null");
|
columns.put("realname_apply_no", "varchar(32) default null");
|
columns.put("realname_response_code", "varchar(32) default null");
|
columns.put("realname_response_message", "varchar(512) default null");
|
// 门店照片：个人（小微）商户的微信业务开通与微信实名都要这两张图，
|
// 企业主体用不到。见 V146 的说明。
|
columns.put("store_header_url", "varchar(512) default null");
|
columns.put("store_indoor_url", "varchar(512) 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) {
|
// 这里必须是 error 而不是 warn：进件的 INSERT/UPDATE 都显式列出列名，缺列不是"只影响重试功能"，
|
// 而是商户一提交进件就报 Unknown column。原先只记 warn、应用照常启动，于是症状变成
|
// "重启了还是报错、又看不出原因"（2026-09-16 在开发库的 legal_mobile 上实际发生过：
|
// 同一轮循环里它后面的列都加成功了，只有它抢锁失败）。日志里直接给出补救语句。
|
log.error("Merchant onboarding schema: could not add {}.{} within {}s (table busy); the column is"
|
+ " referenced by the onboarding INSERT, so onboarding submissions will fail until it"
|
+ " exists. Retried on the next restart, or run manually: alter table {} add column {} {}."
|
+ " cause={}",
|
tableName, columnName, DDL_LOCK_WAIT_TIMEOUT_SECONDS, tableName, columnName, definition,
|
exception.getClass().getSimpleName());
|
}
|
}
|
|
/** 与 {@link #addColumn} 同样地失败即放弃：缺表只影响进件进度，不能让整个服务起不来。 */
|
private void createTable(String tableName, String ddl) {
|
try {
|
jdbc.execute("set session lock_wait_timeout = " + DDL_LOCK_WAIT_TIMEOUT_SECONDS);
|
jdbc.execute(ddl);
|
log.info("Merchant onboarding schema: created table {}", tableName);
|
} catch (Exception exception) {
|
log.warn("Merchant onboarding schema: could not create {} within {}s (table busy); it will be retried on the next restart. cause={}",
|
tableName, DDL_LOCK_WAIT_TIMEOUT_SECONDS, exception.getClass().getSimpleName());
|
}
|
}
|
|
private boolean hasTable(String tableName) throws Exception {
|
try (Connection connection = jdbc.getDataSource().getConnection();
|
ResultSet result = connection.getMetaData().getTables(connection.getCatalog(), null, tableName, new String[] { "TABLE" })) {
|
if (result.next()) return true;
|
}
|
try (Connection connection = jdbc.getDataSource().getConnection();
|
ResultSet result = connection.getMetaData().getTables(connection.getCatalog(), null, tableName.toUpperCase(), new String[] { "TABLE" })) {
|
return result.next();
|
}
|
}
|
|
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();
|
}
|
}
|
}
|
