package cn.com.capcloud.core.noentitydb;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.env.YamlPropertySourceLoader;
import org.springframework.core.env.PropertySource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * Spring Boot 启动前数据库健康检查。
 */
public class DatabaseHealthCheckService {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseHealthCheckService.class);

    public HealthCheckResult checkBeforeStart() {
        Map<String, Object> properties = loadProperties();
        String url = asString(properties.get("spring.datasource.url"));
        String username = asString(properties.get("spring.datasource.username"));
        String password = asString(properties.get("spring.datasource.password"));
        String driverClassName = asString(properties.get("spring.datasource.driver-class-name"));
        String jpaDatabase = asString(properties.get("spring.jpa.database"));

        HealthCheckResult result = new HealthCheckResult();
        result.setDatasourceUrl(maskJdbcUrl(url));
        result.setDatasourceHost(extractHost(url));
        result.setDatasourcePort(extractPort(url));
        result.setDatasourceDatabase(extractDatabaseName(url));

        try {
            if (driverClassName != null && !driverClassName.isEmpty()) {
                Class.forName(driverClassName);
                result.setDriverClassName(driverClassName);
            }

            validateDatabaseExistsForStartup(url, driverClassName);

            try (Connection connection = DriverManager.getConnection(url, username, password)) {
                DatabaseMetaData metaData = connection.getMetaData();
                result.setDatabaseProductName(metaData.getDatabaseProductName());
                result.setDatabaseProductVersion(metaData.getDatabaseProductVersion());
                result.setDriverName(metaData.getDriverName());
                result.setMessage("数据库连接正常，允许 Spring Boot 启动");
                logger.info("数据库类型：{}", result.getDatabaseProductName());

                if ("com.kingbase8.Driver".equals(driverClassName)) {
                    try (Statement stmt = connection.createStatement();
                         ResultSet rs = stmt.executeQuery("SHOW database_mode")) {
                        if (rs.next()) {
                            String databaseMode = trimToNull(rs.getString(1));
                            String expectedJpaDatabase = trimToNull(jpaDatabase);

                            if (expectedJpaDatabase == null) {
                                logger.warn("JPA database 未配置，跳过兼容模式校验");
                            } else if (databaseMode != null && databaseMode.equalsIgnoreCase(expectedJpaDatabase)) {
                                logger.info("数据库兼容模式与 JPA 配置一致: {}", databaseMode);
                                result.setMessage(result.getMessage() + "，与 JPA 配置一致");
                            } else {
                                String errorMessage = "数据库兼容模式与 JPA 配置不一致! 实际：" + databaseMode + ", JPA：" + expectedJpaDatabase;
                                logger.error(errorMessage);
                                throw new IllegalStateException(errorMessage);
                            }
                        }
                    }
                }
                result.setSuccess(true);
                return result;
            }
        } catch (Exception ex) {
            logger.error("数据库连接失败，请检查，HOST={}，PORT={}",
                    result.getDatasourceHost(), result.getDatasourcePort());
            result.setSuccess(false);
            result.setMessage(buildFailureMessage(result, ex));
            return result;
        }
    }

    private Map<String, Object> loadProperties() {
        Map<String, Object> result = new HashMap<>();
        loadYamlTo(result, new ClassPathResource("application.yml"));

        String activeProfile = System.getProperty("spring.profiles.active");
        if (activeProfile == null || activeProfile.trim().isEmpty()) {
            activeProfile = System.getenv("SPRING_PROFILES_ACTIVE");
        }
        if (activeProfile == null || activeProfile.trim().isEmpty()) {
            activeProfile = asString(result.get("spring.profiles.active"));
        }
        if (activeProfile != null && !activeProfile.trim().isEmpty()) {
            String profile = activeProfile.trim();
            loadYamlTo(result, new ClassPathResource("application-" + profile + ".yml"));
            loadYamlTo(result, new ClassPathResource("application-" + profile + ".yaml"));
        }
        return result;
    }

    private void loadYamlTo(Map<String, Object> target, Resource resource) {
        if (!resource.exists()) {
            return;
        }
        try {
            YamlPropertySourceLoader loader = new YamlPropertySourceLoader();
            List<PropertySource<?>> sources = loader.load(resource.getFilename(), resource);
            for (PropertySource<?> source : sources) {
                Object value = source.getSource();
                if (value instanceof Map) {
                    @SuppressWarnings("unchecked")
                    Map<String, Object> map = (Map<String, Object>) value;
                    flattenMap("", map, target);
                }
            }
        } catch (Exception ex) {
            logger.warn("加载配置文件失败: {}", resource.getDescription(), ex);
        }
    }

    private void flattenMap(String prefix, Map<String, Object> source, Map<String, Object> target) {
        for (Map.Entry<String, Object> entry : source.entrySet()) {
            String key = prefix.isEmpty() ? entry.getKey() : prefix + "." + entry.getKey();
            Object value = entry.getValue();
            if (value instanceof Map) {
                @SuppressWarnings("unchecked")
                Map<String, Object> nested = (Map<String, Object>) value;
                flattenMap(key, nested, target);
            } else {
                target.put(key, value);
            }
        }
    }

    private void validateDatabaseExistsForStartup(String url, String driverClassName) {
        if (!isH2Url(url)) {
            return;
        }

        String normalizedUrl = url.toLowerCase(java.util.Locale.ROOT);
        if (normalizedUrl.contains("mem:")) {
            return;
        }

        if (driverClassName != null && !driverClassName.contains("h2")) {
            return;
        }

        java.nio.file.Path databaseFile = resolveH2DatabaseFile(url);
        if (databaseFile == null) {
            return;
        }

        if (java.nio.file.Files.exists(databaseFile)) {
            return;
        }

        logger.warn("检测到 H2 数据库文件不存在，开始自动导入初始化：{}", databaseFile);
        try {
            MysqlSqlToH2Importer.importMysqlSqlToH2(MysqlSqlToH2Importer.buildDataSource(), MysqlSqlToH2Importer.resolveMysqlSqlResource());
            logger.info("H2 数据库初始化完成：{}", databaseFile);
        } catch (Exception ex) {
            throw new IllegalStateException("H2 数据库不存在：" + databaseFile + "；自动导入初始化失败，请手动执行 MysqlSqlToH2Importer main 方法覆盖", ex);
        }
    }

    private boolean isH2Url(String url) {
        return url != null && url.toLowerCase(java.util.Locale.ROOT).startsWith("jdbc:h2:");
    }

    private java.nio.file.Path resolveH2DatabaseFile(String url) {
        String lowerUrl = url.toLowerCase(java.util.Locale.ROOT);
        int prefixIndex = lowerUrl.indexOf("jdbc:h2:file:");
        if (prefixIndex < 0) {
            return null;
        }

        String filePart = url.substring("jdbc:h2:file:".length());
        int optionIndex = firstIndexOf(filePart, ';', '?');
        if (optionIndex >= 0) {
            filePart = filePart.substring(0, optionIndex);
        }
        filePart = filePart.trim();
        if (filePart.isEmpty()) {
            return null;
        }

        String normalizedPath = filePart.replace('/', java.io.File.separatorChar).replace('\\', java.io.File.separatorChar);
        if (!normalizedPath.endsWith(".mv.db")) {
            normalizedPath = normalizedPath + ".mv.db";
        }
        return java.nio.file.Paths.get(normalizedPath);
    }

    private String buildFailureMessage(HealthCheckResult result, Exception ex) {
        String message = ex.getMessage();
        if (message == null || message.trim().isEmpty()) {
            message = ex.getClass().getSimpleName();
        }

        StringBuilder builder = new StringBuilder("数据库连接失败：").append(message);
        if (result.getDatasourceHost() != null) {
            builder.append("；host=").append(result.getDatasourceHost());
        }
        if (result.getDatasourcePort() != null) {
            builder.append("；port=").append(result.getDatasourcePort());
        }
        if (result.getDatasourceDatabase() != null) {
            builder.append("；database=").append(result.getDatasourceDatabase());
        }
        if (result.getDriverClassName() != null) {
            builder.append("；driver=").append(result.getDriverClassName());
        }
        if (isMissingH2DatabaseMessage(message)) {
            builder.append("；请先执行 MysqlSqlToH2Importer main 方法初始化数据库");
        }
        return builder.toString();
    }

    private boolean isMissingH2DatabaseMessage(String message) {
        if (message == null) {
            return false;
        }
        String lowerMessage = message.toLowerCase(java.util.Locale.ROOT);
        return lowerMessage.contains("h2 数据库文件不存在") || lowerMessage.contains("database file not found") || lowerMessage.contains("file not found");
    }

    private String asString(Object value) {
        return value == null ? null : Objects.toString(value, null);
    }

    private String maskJdbcUrl(String jdbcUrl) {
        if (jdbcUrl == null || jdbcUrl.isEmpty()) {
            return jdbcUrl;
        }
        String masked = jdbcUrl;
        int passwordIndex = masked.toLowerCase().indexOf("password=");
        if (passwordIndex >= 0) {
            int end = masked.indexOf('&', passwordIndex);
            if (end < 0) {
                end = masked.length();
            }
            masked = masked.substring(0, passwordIndex + 9) + "***" + masked.substring(end);
        }
        return masked;
    }

    private String extractHost(String jdbcUrl) {
        JdbcUrlParts parts = parseJdbcUrl(jdbcUrl);
        return parts == null ? null : parts.host;
    }

    private String extractPort(String jdbcUrl) {
        JdbcUrlParts parts = parseJdbcUrl(jdbcUrl);
        return parts == null ? null : parts.port;
    }

    private String extractDatabaseName(String jdbcUrl) {
        JdbcUrlParts parts = parseJdbcUrl(jdbcUrl);
        return parts == null ? null : parts.database;
    }

    private JdbcUrlParts parseJdbcUrl(String jdbcUrl) {
        if (jdbcUrl == null || jdbcUrl.trim().isEmpty()) {
            return null;
        }

        String url = jdbcUrl.trim();
        String withoutPrefix = url.startsWith("jdbc:") ? url.substring(5) : url;
        int paramIndex = firstIndexOf(withoutPrefix, '?', ';');
        String mainPart = paramIndex >= 0 ? withoutPrefix.substring(0, paramIndex) : withoutPrefix;

        if (mainPart.startsWith("mysql://") || mainPart.startsWith("postgresql://")
                || mainPart.startsWith("sqlserver://") || mainPart.startsWith("kingbase://")
                || mainPart.startsWith("oracle://") || mainPart.startsWith("mariadb://")) {
            return parseAuthorityStyle(mainPart);
        }

        if (mainPart.startsWith("oracle:thin:@")) {
            return parseOracleThin(mainPart);
        }

        if (mainPart.startsWith("sqlserver://")) {
            return parseSqlServer(mainPart);
        }

        return parseAuthorityStyle(mainPart);
    }

    private JdbcUrlParts parseAuthorityStyle(String url) {
        int schemeSeparator = url.indexOf("//");
        if (schemeSeparator < 0) {
            return null;
        }
        String afterScheme = url.substring(schemeSeparator + 2);
        int slashIndex = afterScheme.indexOf('/');
        String authority = slashIndex >= 0 ? afterScheme.substring(0, slashIndex) : afterScheme;
        String database = slashIndex >= 0 && slashIndex + 1 < afterScheme.length() ? afterScheme.substring(slashIndex + 1) : null;

        int colonIndex = authority.lastIndexOf(':');
        String host = colonIndex > 0 ? authority.substring(0, colonIndex) : authority;
        String port = colonIndex > 0 && colonIndex + 1 < authority.length() ? authority.substring(colonIndex + 1) : null;
        host = host.isEmpty() ? null : host;
        port = normalizePort(port);
        database = trimDatabase(database);
        return new JdbcUrlParts(host, port, database);
    }

    private JdbcUrlParts parseOracleThin(String url) {
        String remainder = url.substring("oracle:thin:@".length());
        if (remainder.startsWith("//")) {
            return parseAuthorityStyle("//" + remainder.substring(2));
        }

        int atIndex = remainder.indexOf('@');
        if (atIndex >= 0) {
            remainder = remainder.substring(atIndex + 1);
        }
        int colonIndex = remainder.indexOf(':');
        int slashIndex = remainder.indexOf('/');
        if (colonIndex < 0 || slashIndex < 0 || slashIndex <= colonIndex) {
            return null;
        }
        String host = remainder.substring(0, colonIndex);
        String port = remainder.substring(colonIndex + 1, slashIndex);
        String database = remainder.substring(slashIndex + 1);
        return new JdbcUrlParts(trimToNull(host), normalizePort(port), trimDatabase(database));
    }

    private JdbcUrlParts parseSqlServer(String url) {
        return parseAuthorityStyle(url);
    }

    private int firstIndexOf(String value, char... delimiters) {
        int minIndex = -1;
        for (char delimiter : delimiters) {
            int index = value.indexOf(delimiter);
            if (index >= 0 && (minIndex < 0 || index < minIndex)) {
                minIndex = index;
            }
        }
        return minIndex;
    }

    private String normalizePort(String port) {
        return trimToNull(port);
    }

    private String trimDatabase(String database) {
        String value = trimToNull(database);
        if (value == null) {
            return null;
        }
        int slashIndex = value.indexOf('/');
        if (slashIndex >= 0) {
            value = value.substring(0, slashIndex);
        }
        return trimToNull(value);
    }

    private String trimToNull(String value) {
        if (value == null) {
            return null;
        }
        String trimmed = value.trim();
        return trimmed.isEmpty() ? null : trimmed;
    }

    private static final class JdbcUrlParts {
        private final String host;
        private final String port;
        private final String database;

        private JdbcUrlParts(String host, String port, String database) {
            this.host = host;
            this.port = port;
            this.database = database;
        }
    }

    public static class HealthCheckResult {
        private boolean success;
        private String message;
        private String datasourceUrl;
        private String datasourceHost;
        private String datasourcePort;
        private String datasourceDatabase;
        private String driverClassName;
        private String driverName;
        private String databaseProductName;
        private String databaseProductVersion;

        public boolean isSuccess() {
            return success;
        }

        public void setSuccess(boolean success) {
            this.success = success;
        }

        public String getMessage() {
            return message;
        }

        public void setMessage(String message) {
            this.message = message;
        }

        public String getDatasourceUrl() {
            return datasourceUrl;
        }

        public void setDatasourceUrl(String datasourceUrl) {
            this.datasourceUrl = datasourceUrl;
        }

        public String getDatasourceHost() {
            return datasourceHost;
        }

        public void setDatasourceHost(String datasourceHost) {
            this.datasourceHost = datasourceHost;
        }

        public String getDatasourcePort() {
            return datasourcePort;
        }

        public void setDatasourcePort(String datasourcePort) {
            this.datasourcePort = datasourcePort;
        }

        public String getDatasourceDatabase() {
            return datasourceDatabase;
        }

        public void setDatasourceDatabase(String datasourceDatabase) {
            this.datasourceDatabase = datasourceDatabase;
        }

        public String getDriverClassName() {
            return driverClassName;
        }

        public void setDriverClassName(String driverClassName) {
            this.driverClassName = driverClassName;
        }

        public String getDriverName() {
            return driverName;
        }

        public void setDriverName(String driverName) {
            this.driverName = driverName;
        }

        public String getDatabaseProductName() {
            return databaseProductName;
        }

        public void setDatabaseProductName(String databaseProductName) {
            this.databaseProductName = databaseProductName;
        }

        public String getDatabaseProductVersion() {
            return databaseProductVersion;
        }

        public void setDatabaseProductVersion(String databaseProductVersion) {
            this.databaseProductVersion = databaseProductVersion;
        }
    }
}
