package com.ruoyi.web.test;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.util.*;
import java.util.regex.*;

public class SqlCleanerWithComment {
    public static void main(String[] args) {
        String inputFilePath = "C:\\Users\\86176\\Desktop\\smartor_lisui_jg.sql";   // 原始 MySQL 建表文件
        String outputFilePath = "C:\\Users\\86176\\Desktop\\smartor_lisui_jg_gs.sql"; // 输出 openGauss 兼容建表文件

        try {
            // 读取 SQL 文件内容（兼容 JDK 8）
            String originalSql = new String(Files.readAllBytes(Paths.get(inputFilePath)), StandardCharsets.UTF_8);

            // 获取表名
            String tableName = getTableName(originalSql);

            // 提取 COMMENT 字段注释
            Map<String, String> fieldComments = extractFieldComments(originalSql);

            // 提取 AUTO_INCREMENT = <数字> 的值
            Pattern autoIncrementPattern = Pattern.compile("AUTO_INCREMENT = (\\d+)");
            Matcher matcher = autoIncrementPattern.matcher(originalSql);
            String autoIncrementValue = null;
            if (matcher.find()) {
                autoIncrementValue = matcher.group(1);  // 获取 AUTO_INCREMENT 后的数字
            }

            // 清理 MySQL 语法特性：字段尾部属性
            String cleanedSql = originalSql
                    .replaceAll("(CHARACTER SET\\s+\\w+|COLLATE\\s+\\w+|DEFAULT\\s+[^,\\n]+|COMMENT\\s+'.*?')", "")
                    .replaceAll("\\s+NULL(?!\\s|,)", "")
                    .replaceAll("\\s{2,}", " ")
                    .replaceAll(",\\s*\\)", "\n)")
                    .replaceAll("(?i)ENGINE\\s*=\\s*\\w+", "")
                    .replaceAll("(?i)CHARACTER SET\\s*=\\s*\\w+", "")
                    .replaceAll("(?i)COLLATE\\s*=\\s*\\w+", "")
                    .replaceAll("(?i)COMMENT\\s*=\\s*'.*?'", "")
                    .replaceAll("(?i)ROW_FORMAT\\s*=\\s*\\w+", "")
                    .replaceAll("(?i)\\bdatetime\\b", "timestamp")
                    .replaceAll("(?i)\\btinyint\\b", "smallint")  // 替换 tinyint
                    .replaceAll("AUTO_INCREMENT = \\d+", "")
                    .replaceAll("USING BTREE", "")
                    .replaceAll("longtext", "text")
                    .replaceAll("(?i)(CREATE TABLE)\\s+`([a-zA-Z_][a-zA-Z0-9_]*)`", "$1 \"public\".\"$2\"") // 只在表名上加 "public"
                    .replaceAll("`", "\"")
                    .trim();

            // 构造 COMMENT ON COLUMN 语句
            StringBuilder commentSql = new StringBuilder();
            for (Map.Entry<String, String> entry : fieldComments.entrySet()) {
                commentSql.append(String.format(
                        "COMMENT ON COLUMN \"%s\".\"%s\" IS '%s';\n",
                        tableName, entry.getKey(), entry.getValue()
                ));
            }

            // 替换 SERIAL 的自增字段，确保 openGauss 的兼容性
            cleanedSql = cleanedSql.replaceAll("int NOT NULL AUTO_INCREMENT", "SERIAL NOT NULL");

            // 如果找到了 AUTO_INCREMENT 值，添加 ALTER SEQUENCE 来设置自增起始值
            if (autoIncrementValue != null) {
                cleanedSql += "\n\n-- Adjust the sequence for 'id' field starting value\n";
                cleanedSql += "ALTER SEQUENCE ivr_liba_template_id_seq RESTART WITH " + autoIncrementValue + ";";
            }

            // 提取并移除 INDEX 子句（包括 ASC/DESC/USING BTREE 等）
            Pattern indexPattern = Pattern.compile("INDEX\\s+\"(\\w+)\"\\s*\\(\\s*\"(\\w+)\"(?:\\s+(ASC|DESC))?\\s*\\)", Pattern.CASE_INSENSITIVE);
            Matcher indexMatcher = indexPattern.matcher(originalSql);
            StringBuilder indexStatements = new StringBuilder();

            while (indexMatcher.find()) {
                String indexName = indexMatcher.group(1);
                String columnName = indexMatcher.group(2);
                // 移除 ASC/DESC，直接使用列名创建索引
                indexStatements.append("CREATE INDEX ")
                        .append(indexName)
                        .append("_idx ON \"public\".\"")
                        .append(tableName)
                        .append("\" (\"")
                        .append(columnName)
                        .append("\");\n");
            }

            // 合并建表语句 + 注释语句 + 索引语句
            String finalOutput = cleanedSql + "\n\n" + commentSql.toString() + indexStatements.toString();

            // 写入输出文件
            Files.write(Paths.get(outputFilePath), finalOutput.getBytes(StandardCharsets.UTF_8));
            System.out.println("✅ 转换完成！输出文件为：" + outputFilePath);
        } catch (IOException e) {
            System.err.println("❌ 文件读取/写入失败: " + e.getMessage());
        }
    }

    // 提取表名
    private static String getTableName(String sql) {
        Pattern pattern = Pattern.compile("CREATE TABLE\\s+\"[^\"]+\"\\.\"([^\"]+)\"", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        return matcher.find() ? matcher.group(1) : "unknown_table";
    }

    // 提取字段注释
    private static Map<String, String> extractFieldComments(String sql) {
        Map<String, String> comments = new LinkedHashMap<>();
        Pattern pattern = Pattern.compile("\"(\\w+)\"\\s+\\w+(\\([^)]*\\))?[^\\n]*?COMMENT\\s+'([^']+)'", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            comments.put(matcher.group(1), matcher.group(3));
        }
        return comments;
    }
}