package cn.com.capcloud.core.noentitydb;

import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

/**
 * 将项目根目录 `database/` 目录下的 `mysql.sql` 或 `mysql.zip` 导入到 H2 数据库的独立工具类。
 *
 * <p>如果目标 H2 数据库已经存在，执行时会按当前脚本内容重新初始化，并覆盖现有结构与数据。
 * 当传入 `--reset` 参数时，还会先删除本地 H2 文件，再重新创建数据库后导入脚本。
 */
public class MysqlSqlToH2Importer {

    private static final String MYSQL_SQL_NAME = "mysql.sql";
    private static final String MYSQL_SQL_FS_PATH = "database/mysql.sql";
    private static final String MYSQL_SQL_ZIP_FS_PATH = "database/mysql.zip";
    private static final String H2_URL = "jdbc:h2:file:./h2/data/ccui2;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;DEFAULT_NULL_ORDERING=HIGH;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "";
    private static final String H2_FILE_PREFIX = "ccui2";

    public static void main(String[] args) throws Exception {
        boolean resetDatabase = args.length > 0 && "--reset".equalsIgnoreCase(args[0]);
        if (resetDatabase) {
            deleteExistingH2Files();
        }

        Resource resource = resolveMysqlSqlResource();

        DataSource dataSource = buildDataSource();
        importMysqlSqlToH2(dataSource, resource);
        System.out.println("导入完成：" + MYSQL_SQL_FS_PATH + (resetDatabase ? "（已重建数据库）" : ""));
    }

    public static void importMysqlSqlToH2(DataSource dataSource, Resource resource) throws IOException, SQLException {
        try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) {
            statement.execute("SET MODE MySQL");
            statement.execute("SET NON_KEYWORDS KEY");
            executeFilteredSqlScript(connection, statement, resource);
        }
    }

    static Resource resolveMysqlSqlResource() {
        Resource primaryResource = new FileSystemResource(MYSQL_SQL_FS_PATH);
        if (primaryResource.exists()) {
            return primaryResource;
        }

        Resource fallbackResource = new FileSystemResource(MYSQL_SQL_ZIP_FS_PATH);
        if (fallbackResource.exists()) {
            return extractMysqlSqlFromZip(fallbackResource);
        }

        throw new IllegalStateException(
                "未找到位于项目根目录 `database/` 下的 " + MYSQL_SQL_FS_PATH + "，也未找到备用压缩包 " + MYSQL_SQL_ZIP_FS_PATH + "。"
        );
    }

    private static Resource extractMysqlSqlFromZip(Resource zipResource) {
        try (ZipInputStream zipInputStream = new ZipInputStream(zipResource.getInputStream(), StandardCharsets.UTF_8)) {
            ZipEntry entry;
            while ((entry = zipInputStream.getNextEntry()) != null) {
                if (!entry.isDirectory() && MYSQL_SQL_NAME.equals(Paths.get(entry.getName()).getFileName().toString())) {
                    java.io.ByteArrayOutputStream outputStream = new java.io.ByteArrayOutputStream();
                    byte[] buffer = new byte[8192];
                    int len;
                    while ((len = zipInputStream.read(buffer)) != -1) {
                        outputStream.write(buffer, 0, len);
                    }
                    return new ByteArrayResource(outputStream.toByteArray()) {
                        @Override
                        public String getFilename() {
                            return MYSQL_SQL_NAME;
                        }
                    };
                }
            }
        } catch (IOException e) {
            throw new IllegalStateException("从 " + MYSQL_SQL_ZIP_FS_PATH + " 解压获取 " + MYSQL_SQL_NAME + " 失败", e);
        }

        throw new IllegalStateException("在 " + MYSQL_SQL_ZIP_FS_PATH + " 中未找到 " + MYSQL_SQL_NAME);
    }

    private static void executeFilteredSqlScript(Connection connection, Statement statement, Resource resource) throws IOException, SQLException {
        StringBuilder sqlBuilder = new StringBuilder();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(resource.getInputStream(), StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                String trimmed = line.trim();
                if (trimmed.isEmpty() || trimmed.startsWith("--") || trimmed.startsWith("//#")) {
                    continue;
                }
                if (trimmed.startsWith("LOCK TABLES") || trimmed.startsWith("UNLOCK TABLES")) {
                    continue;
                }
                if (trimmed.startsWith("/*!40000 ALTER TABLE") && (trimmed.contains("DISABLE KEYS") || trimmed.contains("ENABLE KEYS"))) {
                    continue;
                }
                if (trimmed.startsWith("/*!")) {
                    int endIndex = trimmed.indexOf("*/");
                    if (endIndex >= 0 && endIndex + 2 < trimmed.length()) {
                        String mysqlCompatibleSql = trimmed.substring(endIndex + 2).trim();
                        if (!mysqlCompatibleSql.isEmpty()) {
                            sqlBuilder.append(mysqlCompatibleSql).append('\n');
                        }
                    }
                    continue;
                }
                sqlBuilder.append(line).append('\n');
            }
        }

        String sqlScript = sqlBuilder.toString();
        for (String sql : splitSqlStatements(sqlScript)) {
            String rawSql = sql.trim();
            String normalized = normalizeSql(rawSql);
            if (!normalized.isEmpty()) {
                if (isCreateTableStatement(normalized)) {
                    String createTableSql = stripSchemaComments(normalized);
                    statement.execute(createTableSql);
                    applyColumnComments(connection, statement, rawSql);
                    applyTableComment(statement, rawSql);
                } else {
                    statement.execute(normalized);
                }
            }
        }
    }

    private static String normalizeSql(String sql) {
        String normalized = unescapeMySqlStringLiterals(sql.trim());
        if (normalized.isEmpty()) {
            return "";
        }

        normalized = normalized.trim();
        if (normalized.isEmpty()) {
            return "";
        }

        boolean schemaStatement = isSchemaStatement(normalized);

        if (schemaStatement) {
            normalized = normalized.replace("`", "");
            normalized = normalized.replaceAll("(?i)ENGINE=InnoDB", "");
            normalized = normalized.replaceAll("(?i)AUTO_INCREMENT=\\d+", "");
            normalized = normalized.replaceAll("(?i)DEFAULT CHARSET=[^\\s]+", "");
            normalized = normalized.replaceAll("(?i)COLLATE\\s*=\\s*[^\\s)]+", "");
            normalized = normalized.replaceAll("(?i)COLLATE\\s+[^\\s)]+", "");
            normalized = normalized.replaceAll("(?i)CHECKSUM=\\d+", "");
            normalized = normalized.replaceAll("(?i)DELAY_KEY_WRITE=\\d+", "");
            normalized = normalized.replaceAll("(?i)ROW_FORMAT=[^\\s]+", "");
            normalized = normalized.replaceAll("(?i)USING\\s+BTREE", "");
            normalized = normalized.replaceAll("(?i)USING\\s+HASH", "");
            normalized = normalized.replaceAll("(?i),?\\s*CONSTRAINT\\s+[^,]+\\s+FOREIGN KEY\\s*\\([^)]*\\)\\s+REFERENCES\\s+[^,]+", "");
            normalized = normalized.replaceAll("(?i)FOREIGN KEY\\s*\\([^)]*\\)\\s+REFERENCES\\s+[^,]+", "");
            normalized = normalized.replaceAll("(?i)longtext", "CLOB");
            normalized = normalized.replaceAll("(?i)mediumtext", "CLOB");
            normalized = normalized.replaceAll("(?i)tinytext", "CLOB");
            normalized = normalized.replaceAll("(?i)longclob", "CLOB");
            normalized = normalized.replaceAll("(?i)mediumclob", "CLOB");
            normalized = normalized.replaceAll("(?i)\\btext\\b", "CLOB");
            normalized = preserveTypeLength(normalized, "varchar");
            normalized = preserveTypeLength(normalized, "char");
            normalized = preserveTypeLength(normalized, "varbinary");
            normalized = preserveTypeLength(normalized, "binary");
            normalized = preserveTypeLength(normalized, "decimal");
            normalized = preserveTypeLength(normalized, "numeric");
            normalized = preserveTypeLength(normalized, "float");
            normalized = preserveTypeLength(normalized, "double");
            normalized = normalized.replaceAll("(?i)int\\(\\d+\\)", "INT");
            normalized = normalized.replaceAll("(?i)bigint\\(\\d+\\)", "BIGINT");
            normalized = normalized.replaceAll("(?i)tinyint\\(1\\)", "BOOLEAN");
            normalized = normalized.replaceAll("(?i)datetime", "TIMESTAMP");

            normalized = normalized.replaceAll("(?i)PRIMARY KEY", "PRIMARY_KEY_PLACEHOLDER");
            normalized = normalized.replaceAll("(?i),\\s*KEY\\s+[^,()]+\\([^()]*\\)", "");
            normalized = normalized.replaceAll("(?i)\\(\\s*KEY\\s+[^,()]+\\([^()]*\\)\\s*,", "(");
            normalized = normalized.replaceAll("(?i)(^|,\\s*)key\\b", "$1vm_key");
            normalized = normalized.replaceAll("PRIMARY_KEY_PLACEHOLDER", "PRIMARY KEY");

            normalized = normalized.replaceAll("(?i)DEFAULT NULL", "");
            normalized = normalized.replaceAll("(?i)DEFAULT ''", "DEFAULT ''");
            normalized = normalized.replaceAll("(?i),?\\s*COMMENT\\s*=\\s*'((?:''|[^'])*)'", "");
            normalized = normalized.replaceAll("(?i),?\\s*COMMENT\\s+'((?:''|[^'])*)'", "");
            normalized = normalized.replaceAll("(?i)\\)\\s*COLLATE\\s+[^\\s,)]*", ")");
            normalized = normalized.replaceAll("\\s+,", ",");
            normalized = normalized.replaceAll(",\\s+\\)", ")");
            normalized = normalized.replaceAll("\\s{2,}", " ");
            normalized = normalized.replaceAll("\\s+\\)", ")");
            normalized = normalized.replaceAll("\\(\\s+", "(");
            normalized = normalized.replaceAll(",\\s*,", ",");
            normalized = normalized.replaceAll(",\\s*$", "");
            normalized = normalized.replaceAll("\\)\\s*\\)", ")");
            normalized = normalized.replaceAll(",\\s*$", "");
            if (normalized.regionMatches(true, 0, "CREATE TABLE", 0, "CREATE TABLE".length())) {
                long openCount = normalized.chars().filter(ch -> ch == '(').count();
                long closeCount = normalized.chars().filter(ch -> ch == ')').count();
                while (closeCount < openCount) {
                    normalized = normalized + ")";
                    closeCount++;
                }
            }
        }

        return normalized.trim();
    }

    private static String preserveTypeLength(String sql, String typeName) {
        return sql.replaceAll("(?i)\\b" + typeName + "\\(([^)]+)\\)", typeName.toUpperCase(java.util.Locale.ROOT) + "($1)");
    }

    private static void applyColumnComments(Connection connection, Statement statement, String sql) throws SQLException {
        String tableName = extractTableName(sql);
        int openParenIndex = sql.indexOf('(');
        int closeParenIndex = sql.lastIndexOf(')');
        if (openParenIndex < 0 || closeParenIndex <= openParenIndex) {
            return;
        }

        String columnSection = sql.substring(openParenIndex + 1, closeParenIndex);
        java.util.Set<String> existingColumns = getExistingColumns(connection, tableName);
        for (String definition : splitColumnDefinitions(columnSection)) {
            String trimmed = definition.trim();
            if (trimmed.isEmpty() || trimmed.startsWith("PRIMARY KEY") || trimmed.startsWith("UNIQUE KEY") || trimmed.startsWith("KEY ") || trimmed.startsWith("INDEX ") || trimmed.startsWith("CONSTRAINT ")) {
                continue;
            }

            java.util.regex.Matcher nameMatcher = java.util.regex.Pattern.compile("^`?([a-zA-Z0-9_]+)`?\\s+", java.util.regex.Pattern.CASE_INSENSITIVE).matcher(trimmed);
            java.util.regex.Matcher commentMatcher = java.util.regex.Pattern.compile("(?i)COMMENT\\s+'((?:''|[^'])*)'").matcher(trimmed);
            if (nameMatcher.find() && commentMatcher.find()) {
                String rawColumnName = nameMatcher.group(1);
                String columnName = normalizeColumnName(rawColumnName);
                String comment = commentMatcher.group(1).replace("''", "'");
                String escapedComment = comment.replace("'", "''");
                if (existingColumns.contains(columnName.toLowerCase(java.util.Locale.ROOT))) {
                    statement.execute("COMMENT ON COLUMN " + quoteIdentifier(tableName) + "." + quoteIdentifier(columnName) + " IS '" + escapedComment + "'");
                }
            }
        }
    }

    private static java.util.Set<String> getExistingColumns(Connection connection, String tableName) throws SQLException {
        java.util.Set<String> columns = new java.util.HashSet<>();
        String sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = ?";
        try (java.sql.PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, tableName.toLowerCase(java.util.Locale.ROOT));
            try (java.sql.ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    columns.add(rs.getString("COLUMN_NAME").toLowerCase(java.util.Locale.ROOT));
                }
            }
        }
        return columns;
    }

    private static java.util.List<String> splitColumnDefinitions(String columnSection) {
        java.util.List<String> definitions = new java.util.ArrayList<>();
        StringBuilder current = new StringBuilder();
        int depth = 0;
        boolean inSingleQuote = false;
        boolean inDoubleQuote = false;
        boolean inBacktick = false;

        for (int i = 0; i < columnSection.length(); i++) {
            char c = columnSection.charAt(i);
            if (c == '\'' && !inDoubleQuote && !inBacktick) {
                inSingleQuote = !inSingleQuote;
            } else if (c == '"' && !inSingleQuote && !inBacktick) {
                inDoubleQuote = !inDoubleQuote;
            } else if (c == '`' && !inSingleQuote && !inDoubleQuote) {
                inBacktick = !inBacktick;
            } else if (!inSingleQuote && !inDoubleQuote && !inBacktick) {
                if (c == '(') {
                    depth++;
                } else if (c == ')') {
                    depth--;
                } else if (c == ',' && depth == 0) {
                    definitions.add(current.toString());
                    current.setLength(0);
                    continue;
                }
            }
            current.append(c);
        }

        String tail = current.toString().trim();
        if (!tail.isEmpty()) {
            definitions.add(tail);
        }
        return definitions;
    }

    private static String stripSchemaComments(String sql) {
        return sql.replaceAll("(?i)\\s+COMMENT\\s*=\\s*'((?:''|[^'])*)'", "")
                .replaceAll("(?i)\\s+COMMENT\\s+'((?:''|[^'])*)'", "");
    }

    private static void applyTableComment(Statement statement, String sql) throws SQLException {
        java.util.regex.Matcher matcher = java.util.regex.Pattern.compile("(?i)COMMENT\\s*=\\s*'((?:''|[^'])*)'\\s*$").matcher(sql);
        if (!matcher.find()) {
            matcher = java.util.regex.Pattern.compile("(?i)COMMENT\\s+'((?:''|[^'])*)'\\s*$").matcher(sql);
        }
        if (!matcher.find()) {
            return;
        }
        String comment = matcher.group(1).replace("''", "'");
        String escapedComment = comment.replace("'", "''");
        String tableName = extractTableName(sql);
        statement.execute("COMMENT ON TABLE " + quoteIdentifier(tableName) + " IS '" + escapedComment + "'");
    }

    private static String quoteIdentifier(String identifier) {
        return "\"" + identifier.replace("\"", "\"\"") + "\"";
    }

    private static String normalizeColumnName(String columnName) {
        if ("key".equalsIgnoreCase(columnName)) {
            return "vm_key";
        }
        return columnName;
    }

    private static String extractTableName(String sql) {
        java.util.regex.Matcher matcher = java.util.regex.Pattern.compile("(?i)CREATE\\s+TABLE\\s+`?([a-zA-Z0-9_]+)`?").matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        throw new IllegalStateException("无法解析表名: " + sql);
    }

    private static boolean isSchemaStatement(String sql) {
        String upper = sql.trim().toUpperCase(java.util.Locale.ROOT);
        return upper.startsWith("CREATE TABLE")
                || upper.startsWith("ALTER TABLE")
                || upper.startsWith("DROP TABLE")
                || upper.startsWith("TRUNCATE TABLE")
                || upper.startsWith("CREATE INDEX")
                || upper.startsWith("DROP INDEX");
    }

    private static boolean isCreateTableStatement(String sql) {
        String upper = sql.trim().toUpperCase(java.util.Locale.ROOT);
        return upper.startsWith("CREATE TABLE");
    }

    private static String unescapeMySqlStringLiterals(String sql) {
        if (sql.indexOf('\\') < 0) {
            return sql;
        }

        StringBuilder result = new StringBuilder(sql.length());
        boolean inSingleQuote = false;
        boolean inDoubleQuote = false;
        boolean inBacktick = false;

        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);

            if (c == '\'' && !inDoubleQuote && !inBacktick) {
                inSingleQuote = !inSingleQuote;
                result.append(c);
                continue;
            }
            if (c == '"' && !inSingleQuote && !inBacktick) {
                inDoubleQuote = !inDoubleQuote;
                result.append(c);
                continue;
            }
            if (c == '`' && !inSingleQuote && !inDoubleQuote) {
                inBacktick = !inBacktick;
                result.append(c);
                continue;
            }

            if ((inSingleQuote || inDoubleQuote) && c == '\\' && i + 1 < sql.length()) {
                char next = sql.charAt(i + 1);
                if (next == 'n') {
                    result.append('\n');
                    i++;
                    continue;
                }
                if (next == 'r') {
                    result.append('\r');
                    i++;
                    continue;
                }
                if (next == '\\') {
                    if (i + 2 < sql.length()) {
                        char next2 = sql.charAt(i + 2);
                        if (next2 == 'n') {
                            result.append('\\').append('n');
                            i += 2;
                            continue;
                        }
                        if (next2 == 'r') {
                            result.append('\\').append('r');
                            i += 2;
                            continue;
                        }
                        if (next2 == '\\') {
                            result.append('\\');
                            i += 2;
                            continue;
                        }
                    }
                    result.append('\\');
                    i++;
                    continue;
                }
                if (next == '\'' || next == '"') {
                    result.append(next);
                    i++;
                    continue;
                }
            }

            result.append(c);
        }

        return result.toString();
    }

    private static String[] splitSqlStatements(String sqlScript) {
        StringBuilder current = new StringBuilder();
        java.util.List<String> statements = new java.util.ArrayList<>();
        boolean inSingleQuote = false;
        boolean inDoubleQuote = false;
        boolean inBacktick = false;
        boolean inLineComment = false;
        boolean inBlockComment = false;

        for (int i = 0; i < sqlScript.length(); i++) {
            char c = sqlScript.charAt(i);
            char next = i + 1 < sqlScript.length() ? sqlScript.charAt(i + 1) : '\0';

            if (inLineComment) {
                if (c == '\n') {
                    inLineComment = false;
                }
                continue;
            }
            if (inBlockComment) {
                if (c == '*' && next == '/') {
                    i++;
                    inBlockComment = false;
                }
                continue;
            }

            if (!inSingleQuote && !inDoubleQuote && !inBacktick) {
                if (c == '-' && next == '-') {
                    inLineComment = true;
                    i++;
                    continue;
                }
                if (c == '/' && next == '*') {
                    inBlockComment = true;
                    i++;
                    continue;
                }
            }

            if (c == '\'' && !inDoubleQuote && !inBacktick) {
                inSingleQuote = !inSingleQuote;
            } else if (c == '"' && !inSingleQuote && !inBacktick) {
                inDoubleQuote = !inDoubleQuote;
            } else if (c == '`' && !inSingleQuote && !inDoubleQuote) {
                inBacktick = !inBacktick;
            }

            if (c == ';' && !inSingleQuote && !inDoubleQuote && !inBacktick) {
                String statement = current.toString().trim();
                if (!statement.isEmpty()) {
                    statements.add(statement);
                }
                current.setLength(0);
            } else {
                current.append(c);
            }
        }

        String tail = current.toString().trim();
        if (!tail.isEmpty()) {
            statements.add(tail);
        }
        return statements.toArray(new String[0]);
    }

    private static void deleteExistingH2Files() throws IOException {
        Path base = Paths.get("data", H2_FILE_PREFIX);
        Files.deleteIfExists(Paths.get(base.toString() + ".mv.db"));
        Files.deleteIfExists(Paths.get(base.toString() + ".trace.db"));
        Files.deleteIfExists(Paths.get(base.toString() + ".lock.db"));
        Files.deleteIfExists(Paths.get("data", H2_FILE_PREFIX + ".mv.db"));
        Files.deleteIfExists(Paths.get("data", H2_FILE_PREFIX + ".trace.db"));
        Files.deleteIfExists(Paths.get("data", H2_FILE_PREFIX + ".lock.db"));
    }

    static DataSource buildDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl(H2_URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
        return dataSource;
    }
}
