package com.talentcard.server.business.merchant;

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

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

/**
 * 进件新增的表和列在开发库（{@code spring.flyway.enabled=false}）只能靠
 * {@link MerchantOnboardingSchemaInitializer} 补建。
 *
 * <p>测试库有 Flyway，所以这两个断言在测试里看不出必要性：V144 漏登记时测试全绿，而开发库的
 * `GET /api/merchant/onboarding/status` 第一个请求就 500（`Table
 * 'talent_card.merchant_onboarding_progress' doesn't exist`）。这里查的是"迁移与初始化器是否
 * 同步"这件事本身，不依赖数据库。
 */
class MerchantOnboardingSchemaInitializerTests {
    /** V80 建的进件主表：它是加列和后续一切的前提，不属于"迁移后续新增"。 */
    private static final Set<String> BASELINE_TABLES = Set.of("merchant_onboarding_application");
    private static final Set<String> CONSTRAINT_KEYWORDS = Set.of("primary", "unique", "key", "index", "constraint", "foreign");

    @Test
    void ensuresEveryOnboardingTableAddedByAMigration() throws Exception {
        Set<String> createdByMigrations = new TreeSet<>();
        for (String migration : migrations()) {
            Matcher matcher = Pattern.compile("(?i)create table (?:if not exists )?(merchant_onboarding_[a-z_]+)").matcher(migration);
            while (matcher.find()) createdByMigrations.add(matcher.group(1).toLowerCase(Locale.ROOT));
        }
        createdByMigrations.removeAll(BASELINE_TABLES);

        assertThat(createdByMigrations).as("migrations creating onboarding tables must be discoverable").isNotEmpty();
        assertThat(MerchantOnboardingSchemaInitializer.tableDdl().keySet()).containsAll(createdByMigrations);
    }

    @Test
    void initializerTablesCarryTheSameColumnsAsTheMigrationThatCreatedThem() throws Exception {
        for (var table : MerchantOnboardingSchemaInitializer.tableDdl().entrySet()) {
            String migrationStatement = createTableStatement(table.getKey());
            assertThat(migrationStatement).as("no migration creates %s, so the initializer copy cannot be verified", table.getKey()).isNotNull();
            assertThat(columnsOf(table.getValue())).as("columns of %s", table.getKey()).isEqualTo(columnsOf(migrationStatement));
        }
    }

    /** 初始化器里的建表语句和迁移里必须一致，否则开发库与测试库的表结构会悄悄分叉。 */
    private static Set<String> columnsOf(String statement) {
        List<String> lines = List.of(statement.split("\n"));
        Set<String> columns = new LinkedHashSet<>();
        for (int index = 1; index < lines.size(); index++) { // 第 0 行是 create table 表头
            String definition = lines.get(index).strip().replaceFirst(",$", "");
            if (definition.isEmpty()) continue;
            if (definition.startsWith(")")) break; // 列清单结束，后面是表选项
            String name = definition.split("\\s+")[0].toLowerCase(Locale.ROOT);
            if (!CONSTRAINT_KEYWORDS.contains(name)) columns.add(name);
        }
        return columns;
    }

    private static String createTableStatement(String table) throws Exception {
        Matcher matcher = Pattern.compile("(?i)create table (?:if not exists )?" + table + "\\s*\\(").matcher("");
        for (String migration : migrations()) {
            matcher.reset(migration);
            if (matcher.find()) return migration.substring(matcher.start(), migration.indexOf(");", matcher.end()));
        }
        return null;
    }

    private static List<String> migrations() throws Exception {
        List<String> statements = new ArrayList<>();
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath*:db/migration/*.sql");
        for (Resource resource : resources) {
            try (InputStream stream = resource.getInputStream()) {
                statements.add(new String(stream.readAllBytes(), StandardCharsets.UTF_8));
            }
        }
        assertThat(resources).as("migration files must be on the test classpath").isNotEmpty();
        return statements;
    }
}