package com.talentcard.server.business.merchant;
|
|
import java.io.InputStream;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.boot.ApplicationArguments;
|
import org.springframework.boot.ApplicationRunner;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Component;
|
|
@Slf4j
|
@Component
|
public class BankBranchDirectoryInitializer implements ApplicationRunner {
|
private static final String DIRECTORY_PATH = "bank-branch-directory/bank-branches.xlsx";
|
private static final int BATCH_SIZE = 1000;
|
|
private final JdbcTemplate jdbc;
|
private final boolean importEnabled;
|
|
public BankBranchDirectoryInitializer(
|
JdbcTemplate jdbc,
|
@Value("${app.merchant.bank-branch-directory.import-enabled:true}") boolean importEnabled
|
) {
|
this.jdbc = jdbc;
|
this.importEnabled = importEnabled;
|
}
|
|
@Override
|
public void run(ApplicationArguments args) {
|
try {
|
ensureBankCardBranchCodeColumn();
|
ensureBankCardHistoryIndex();
|
ensureDirectoryTable();
|
// Importing the spreadsheet inserts roughly 100k rows in batches and takes minutes, so
|
// deployments that do not need it (tests) switch it off.
|
if (importEnabled) importDirectoryIfEmpty();
|
} catch (Exception exception) {
|
log.warn("Bank branch directory initialization skipped", exception);
|
}
|
}
|
|
private void ensureBankCardBranchCodeColumn() throws Exception {
|
if (hasColumn("merchant_bank_card", "branch_code")) return;
|
jdbc.execute("alter table merchant_bank_card add column branch_code varchar(12) default null after branch_name");
|
}
|
|
private void ensureBankCardHistoryIndex() {
|
Integer nonUnique = jdbc.query("""
|
select non_unique from information_schema.statistics
|
where table_schema = database() and table_name = 'merchant_bank_card'
|
and index_name = 'uk_merchant_bank_card_hash'
|
limit 1
|
""", result -> result.next() ? result.getInt("non_unique") : null);
|
if (nonUnique != null && nonUnique == 0) {
|
jdbc.execute("alter table merchant_bank_card drop index uk_merchant_bank_card_hash");
|
}
|
Integer historyIndex = jdbc.query("""
|
select 1 from information_schema.statistics
|
where table_schema = database() and table_name = 'merchant_bank_card'
|
and index_name = 'idx_merchant_bank_card_hash'
|
limit 1
|
""", result -> result.next() ? 1 : null);
|
if (historyIndex == null) {
|
jdbc.execute("create index idx_merchant_bank_card_hash on merchant_bank_card(merchant_id, card_hash)");
|
}
|
}
|
|
private void ensureDirectoryTable() {
|
jdbc.execute("""
|
create table if not exists bank_branch_directory (
|
clearing_code varchar(12) not null,
|
branch_name varchar(255) not null,
|
head_bank_code varchar(32) default null,
|
bank_name varchar(128) not null,
|
created_at datetime not null default current_timestamp,
|
updated_at datetime not null default current_timestamp on update current_timestamp,
|
primary key (clearing_code),
|
key idx_bank_branch_directory_bank (bank_name),
|
key idx_bank_branch_directory_head_bank (head_bank_code),
|
key idx_bank_branch_directory_branch (branch_name(64))
|
) engine=InnoDB default charset=utf8mb4 comment='Bank branch clearing code directory'
|
""");
|
}
|
|
private void importDirectoryIfEmpty() throws Exception {
|
Long total = jdbc.queryForObject("select count(*) from bank_branch_directory", Long.class);
|
if (total != null && total > 0) return;
|
|
ClassPathResource resource = new ClassPathResource(DIRECTORY_PATH);
|
if (!resource.exists()) {
|
log.warn("Bank branch directory file not found: {}", DIRECTORY_PATH);
|
return;
|
}
|
|
int imported = 0;
|
DataFormatter formatter = new DataFormatter();
|
try (InputStream input = resource.getInputStream(); Workbook workbook = WorkbookFactory.create(input)) {
|
Sheet sheet = workbook.getSheetAt(0);
|
List<Object[]> batch = new ArrayList<>(BATCH_SIZE);
|
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
|
Row row = sheet.getRow(rowIndex);
|
if (row == null) continue;
|
String clearingCode = clearingCode(formatter.formatCellValue(row.getCell(0)));
|
String branchName = text(formatter.formatCellValue(row.getCell(1)));
|
String headBankCode = text(formatter.formatCellValue(row.getCell(2)));
|
String bankName = text(formatter.formatCellValue(row.getCell(3)));
|
if (clearingCode.isEmpty() || branchName.isEmpty() || bankName.isEmpty()) continue;
|
batch.add(new Object[] { clearingCode, branchName, headBankCode, bankName });
|
if (batch.size() >= BATCH_SIZE) {
|
imported += insertBatch(batch);
|
batch.clear();
|
}
|
}
|
if (!batch.isEmpty()) imported += insertBatch(batch);
|
}
|
log.info("Imported {} bank branch directory rows", imported);
|
}
|
|
private int insertBatch(List<Object[]> batch) {
|
int[] counts = jdbc.batchUpdate("""
|
insert into bank_branch_directory(clearing_code, branch_name, head_bank_code, bank_name)
|
values (?, ?, ?, ?)
|
on duplicate key update branch_name = values(branch_name), head_bank_code = values(head_bank_code), bank_name = values(bank_name)
|
""", batch);
|
int total = 0;
|
for (int count : counts) total += Math.max(count, 0);
|
return total;
|
}
|
|
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();
|
}
|
}
|
|
private String clearingCode(String value) {
|
String text = text(value).replace(" ", "");
|
if (text.endsWith(".0")) text = text.substring(0, text.length() - 2);
|
if (!text.matches("\\d{1,12}")) return "";
|
while (text.length() < 12) text = "0" + text;
|
return text;
|
}
|
|
private String text(String value) {
|
return value == null ? "" : value.trim();
|
}
|
}
|
