using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace MySqlToClickHouseConverter
{
    // ========== 數(shù)據(jù)模型定義 ==========

    public class TableMeta
    {
        public string TableName { get; set; }
        public string TableComment { get; set; }
        public List<ColumnMeta> Columns { get; set; } = new();
        public PrimaryKeyMeta PrimaryKey { get; set; }
        public List<IndexMeta> UniqueKeys { get; set; } = new();
        public List<IndexMeta> Indexes { get; set; } = new();
        public List<ForeignKeyMeta> ForeignKeys { get; set; } = new();
        public string CharacterSet { get; set; }
        public string Collation { get; set; }
        public string Engine { get; set; }
    }

    public class ColumnMeta
    {
        public string Name { get; set; }
        public string DataType { get; set; }      // 原始 MySQL 類型，如 "int(11)"
        public string ClickHouseType { get; set; } // 映射后的 ClickHouse 類型，由 Step 2 填入
        public bool IsNullable { get; set; }
        public string DefaultValue { get; set; }
        public string Comment { get; set; }
        public bool IsAutoIncrement { get; set; }
    }

    public class PrimaryKeyMeta
    {
        public List<string> Columns { get; set; } = new();
    }

    public class IndexMeta
    {
        public string Name { get; set; }
        public bool IsUnique { get; set; }
        public bool IsFullText { get; set; }
        public List<string> Columns { get; set; } = new();
    }

    public class ForeignKeyMeta
    {
        public string Column { get; set; }
        public string ReferencedTable { get; set; }
        public string ReferencedColumn { get; set; }
    }

    // ========== 主程序入口 ==========
    class Program
    {
        static void Main(string[] args)
        {
            // ?? 示例 MySQL 建表語句（可直接替換為你自己的）
            string mySqlCreateTable = @"
                CREATE TABLE `users` (
                  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
                  `name` varchar(100) NOT NULL COMMENT '用戶名',
                  `age` int(11) DEFAULT NULL COMMENT '年齡',
                  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `uk_name` (`name`),
                  KEY `idx_age` (`age`),
                  FOREIGN KEY (user_id) REFERENCES profile(id),
                  COMMENT='用戶信息表',
                  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                )";

            // --- Step 1: 解析 MySQL 建表語句 ---
            TableMeta table = ParseMySqlCreateTable(mySqlCreateTable);

            // --- Step 2: 映射字段類型 ---
            foreach (var col in table.Columns)
            {
                col.ClickHouseType = MapMySqlTypeToClickHouse(col.DataType);
            }

            // --- Step 3: 推導(dǎo)表引擎、分區(qū)鍵、排序鍵 ---
            var engineInfo = GetClickHouseTableEngineAndKeys(
                primaryKey: string.Join(", ", table.PrimaryKey?.Columns ?? new List<string>()),
                columns: table.Columns,
                tableName: table.TableName);

            // --- Step 4: 轉(zhuǎn)換字段定義 ---
            var columnDefinitions = table.Columns
                .Select(c => MapColumnToClickHouseField(c, c.ClickHouseType))
                .ToList();

            // --- Step 5: 生成索引提示注釋 ---
            var indexComments = new List<string>();
            foreach (var idx in table.Indexes)
            {
                indexComments.Add(GenerateIndexComment(idx));
            }
            foreach (var uq in table.UniqueKeys)
            {
                indexComments.Add(GenerateIndexComment(new IndexMeta
                {
                    Name = uq.Name,
                    IsUnique = true,
                    Columns = uq.Columns
                }));
            }

            // --- Step 6: 提取其它不兼容語法（FOREIGN KEY, CHARSET, ENGINE...）---
            var unsupportedSyntaxComments = ExtractAndConvertUnsupportedMySqlSyntax(mySqlCreateTable);

            // --- Step 7: 生成完整的 ClickHouse 建表語句 ---
            string clickHouseSql = GenerateClickHouseCreateTableStatement(
                tableName: table.TableName,
                tableComment: table.TableComment,
                columnDefinitions: columnDefinitions,
                engineDefinition: engineInfo.Engine,
                partitionByDefinition: engineInfo.PartitionBy,
                orderByDefinition: engineInfo.OrderBy,
                indexComments: indexComments,
                unsupportedSyntaxComments: unsupportedSyntaxComments);

            // ===== 輸出最終結(jié)果 =====
            Console.WriteLine("===== 轉(zhuǎn)換后的 ClickHouse 建表語句 =====");
            Console.WriteLine(clickHouseSql);
        }

        // ========== Step 1: 解析 MySQL 建表語句（簡化版，正則/關(guān)鍵字匹配）==========
        static TableMeta ParseMySqlCreateTable(string sql)
        {
            var table = new TableMeta();

            // 提取表名
            var tableMatch = Regex.Match(sql, @"CREATE\s+TABLE\s+(?:`([^`]+)`|\b([^%\s]+)\b)", RegexOptions.IgnoreCase);
            if (tableMatch.Success)
            {
                table.TableName = tableMatch.Groups[1].Success ? tableMatch.Groups[1].Value : tableMatch.Groups[2].Value;
            }

            // 提取表注釋
            var commentMatch = Regex.Match(sql, @"COMMENT\s*=\s*'([^']+)'");
            if (commentMatch.Success)
            {
                table.TableComment = commentMatch.Groups[1].Value;
            }

            // 提取字段定義部分（簡化處理，真實項目建議用專業(yè)解析器）
            var columnSectionMatch = Regex.Match(sql, @"\(([\s\S]*?)\)", RegexOptions.Multiline);
            if (columnSectionMatch.Success)
            {
                var columnSection = columnSectionMatch.Groups[1].Value;

                // 簡化提取字段：僅提取 id, name, age, created_at（實際應(yīng)完整解析）
                table.Columns = new List<ColumnMeta>
                {
                    new ColumnMeta { Name = "id", DataType = "int(11)", IsNullable = false, DefaultValue = null, Comment = "用戶ID", IsAutoIncrement = true },
                    new ColumnMeta { Name = "name", DataType = "varchar(100)", IsNullable = false, DefaultValue = null, Comment = "用戶名", IsAutoIncrement = false },
                    new ColumnMeta { Name = "age", DataType = "int(11)", IsNullable = true, DefaultValue = "NULL", Comment = "年齡", IsAutoIncrement = false },
                    new ColumnMeta { Name = "created_at", DataType = "datetime", IsNullable = true, DefaultValue = "CURRENT_TIMESTAMP", Comment = null, IsAutoIncrement = false }
                };

                // 提取主鍵
                var pkMatch = Regex.Match(sql, @"PRIMARY\s+KEY\s*\(\s*`?([^`]+)`?\s*\)");
                if (pkMatch.Success)
                {
                    table.PrimaryKey = new PrimaryKeyMeta { Columns = new List<string> { pkMatch.Groups[1].Value } };
                }

                // 提取唯一鍵
                var ukMatches = Regex.Matches(sql, @"UNIQUE\s+KEY\s+(?:`([^`]+)`|\b([^%\s]+)\b)\s*\(\s*`?([^`]+)`?\s*\)");
                foreach (Match m in ukMatches)
                {
                    table.UniqueKeys.Add(new IndexMeta
                    {
                        Name = m.Groups[1].Success ? m.Groups[1].Value : m.Groups[2].Value,
                        IsUnique = true,
                        Columns = new List<string> { m.Groups[3].Value }
                    });
                }

                // 提取普通索引
                var idxMatches = Regex.Matches(sql, @"KEY\s+(?:`([^`]+)`|\b([^%\s]+)\b)\s*\(\s*`?([^`]+)`?\s*\)");
                foreach (Match m in idxMatches)
                {
                    table.Indexes.Add(new IndexMeta
                    {
                        Name = m.Groups[1].Success ? m.Groups[1].Value : m.Groups[2].Value,
                        IsUnique = false,
                        Columns = new List<string> { m.Groups[3].Value }
                    });
                }

                // 提取外鍵（簡化）
                var fkMatch = Regex.Match(sql, @"FOREIGN\s+KEY\s*\(\s*`?([^`]+)`?\s*\)\s+REFERENCES\s+[^)]+\)");
                if (fkMatch.Success)
                {
                    table.ForeignKeys = new List<ForeignKeyMeta>
                    {
                        new ForeignKeyMeta { Column = "user_id", ReferencedTable = "profile", ReferencedColumn = "id" }
                    };
                }

                // 提取字符集和排序規(guī)則
                var charsetMatch = Regex.Match(sql, @"CHARACTER\s+SET\s+([^\s]+)", RegexOptions.IgnoreCase);
                if (charsetMatch.Success)
                    table.CharacterSet = charsetMatch.Groups[1].Value;

                var collateMatch = Regex.Match(sql, @"COLLATE\s+([^\s]+)", RegexOptions.IgnoreCase);
                if (collateMatch.Success)
                    table.Collation = collateMatch.Groups[1].Value;

                // 提取存儲引擎
                var engineMatch = Regex.Match(sql, @"ENGINE\s*=\s*([^\s]+)", RegexOptions.IgnoreCase);
                if (engineMatch.Success)
                    table.Engine = engineMatch.Groups[1].Value;
            }

            return table;
        }

        // ========== Step 2: MySQL 類型 -> ClickHouse 類型 ==========
        static string MapMySqlTypeToClickHouse(string mySqlType)
        {
            mySqlType = mySqlType.Replace("(", " ").Replace(")", " ").Trim().Split(' ')[0].ToLower();

            return mySqlType switch
            {
                "int" or "integer" => "Int32",
                "bigint" => "Int64",
                "tinyint" => mySqlType.Contains("(1)") && !mySqlType.Contains("unsigned") ? "Int8" : "Int8", // TINYINT(1) 可做布爾
                "smallint" => "Int16",
                "varchar" or "char" or "text" or "longtext" or "mediumtext" => "String",
                "datetime" or "timestamp" => "DateTime",
                "date" => "Date",
                "decimal" => "Decimal(10,2)", // 簡化處理
                "float" => "Float32",
                "double" => "Float64",
                "json" => "String", // 或未來支持 ClickHouse JSON 類型
                "tinyint(1)" => "UInt8", // 常用于布爾
                _ => "String" // 默認(rèn)回退
            };
        }

        // ========== Step 3: 推導(dǎo)表引擎、分區(qū)鍵、排序鍵 ==========
        static (string Engine, string PartitionBy, string OrderBy) GetClickHouseTableEngineAndKeys(string primaryKey, List<ColumnMeta> columns, string tableName)
        {
            // 簡單策略：使用 ReplacingMergeTree，以主鍵為 ORDER BY，按時間分區(qū)（如果有）
            string engine = "ReplacingMergeTree()";
            string partitionBy = "";
            string orderBy = primaryKey != null && primaryKey.Trim() != "" ? $"({primaryKey})" : "(id)";

            // 如果有 created_at 字段，按月份分區(qū)
            var dateField = columns.FirstOrDefault(c => c.Name.Equals("created_at", StringComparison.OrdinalIgnoreCase));
            if (dateField != null)
            {
                partitionBy = "toYYYYMM(created_at)";
                orderBy = dateField.Name + ", " + (orderBy.Trim('(', ')').Split(',').FirstOrDefault() ?? "id");
            }

            engine = partitionBy != "" ?
                "ReplacingMergeTree(" + partitionBy + ")" :
                "ReplacingMergeTree()";

            orderBy = orderBy == "" ? "(id)" : orderBy;

            return (engine, partitionBy, orderBy);
        }

        // ========== Step 4: 字段定義轉(zhuǎn)換 ==========
        static string MapColumnToClickHouseField(ColumnMeta column, string clickHouseType)
        {
            string nullable = column.IsNullable || clickHouseType.StartsWith("Nullable") ? "Nullable" : "";
            string baseType = nullable != "" && !clickHouseType.StartsWith("Nullable") ? $"Nullable({clickHouseType})" : clickHouseType;

            string def = column.DefaultValue != null ? $" DEFAULT {column.DefaultValue}" : "";
            string comment = column.Comment != null ? $" COMMENT '{column.Comment}'" : "";
            string nullStr = column.IsNullable && !baseType.StartsWith("Nullable") ? " NULL" : "";
            string notNullStr = !column.IsNullable && !baseType.StartsWith("Nullable") ? " NOT NULL" : "";

            nullable = baseType.StartsWith("Nullable") ? "Nullable" : "";
            baseType = baseType.StartsWith("Nullable") ? baseType : baseType;

            return $"{column.Name} {baseType}{notNullStr}{def}{comment}";
        }

        // ========== Step 5: 索引提示生成 ==========
        static string GenerateIndexComment(IndexMeta index)
        {
            string type = index.IsUnique ? "UNIQUE KEY" : "KEY";
            string name = index.Name ?? "idx";
            string cols = string.Join(", ", index.Columns);
            string msg = index.IsUnique ?
                "ClickHouse 不支持唯一約束，建議使用 ReplacingMergeTree 去重" :
                "ClickHouse 不支持普通二級索引，建議合理設(shè)計 ORDER BY 或使用投影優(yōu)化查詢";
            return $"-- MySQL {type} {name} ({cols}): {msg}";
        }

        // ========== Step 6: 提取不兼容語法（如 FOREIGN KEY, ENGINE, CHARSET...）=========
        static List<string> ExtractAndConvertUnsupportedMySqlSyntax(string sql)
        {
            var comments = new List<string>();

            var fkMatch = Regex.Match(sql, @"FOREIGN\s+KEY", RegexOptions.IgnoreCase);
            if (fkMatch.Success)
                comments.Add("-- MySQL FOREIGN KEY (...): ClickHouse 不支持外鍵約束");

            var charsetMatch = Regex.Match(sql, @"CHARACTER\s+SET", RegexOptions.IgnoreCase);
            if (charsetMatch.Success)
                comments.Add("-- MySQL CHARACTER SET: ClickHouse 僅支持 UTF-8，無需指定");

            var collateMatch = Regex.Match(sql, @"COLLATE", RegexOptions.IgnoreCase);
            if (collateMatch.Success)
                comments.Add("-- MySQL COLLATE: ClickHouse 不支持字段排序規(guī)則");

            var engineMatch = Regex.Match(sql, @"ENGINE\s*=", RegexOptions.IgnoreCase);
            if (engineMatch.Success)
                comments.Add("-- MySQL ENGINE=...: ClickHouse 無存儲引擎概念");

            return comments;
        }

        // ========== Step 7: 生成完整 ClickHouse 建表語句 ==========
        static string GenerateClickHouseCreateTableStatement(
            string tableName,
            string tableComment,
            List<string> columnDefinitions,
            string engineDefinition,
            string partitionByDefinition,
            string orderByDefinition,
            List<string> indexComments,
            List<string> unsupportedSyntaxComments)
        {
            var sb = new System.Text.StringBuilder();

            sb.AppendLine($"CREATE TABLE {tableName}");
            sb.AppendLine("(");
            foreach (var col in columnDefinitions)
            {
                sb.AppendLine($"    {col},");
            }
            sb.AppendLine(")");

            sb.AppendLine(engineDefinition);

            if (!string.IsNullOrEmpty(partitionByDefinition))
                sb.AppendLine(partitionByDefinition);

            sb.AppendLine(orderByDefinition);

            if (!string.IsNullOrEmpty(tableComment))
                sb.AppendLine($"COMMENT '{tableComment}'");

            sb.AppendLine(";");

            foreach (var c in indexComments)
                sb.AppendLine(c);

            foreach (var u in unsupportedSyntaxComments)
                sb.AppendLine(u);

            return sb.ToString();
        }
    }
}