using System.Text;
using System.Text.RegularExpressions;
using MySqlConnector;
using SqlSugar;

namespace FoodLabeling.Application.MultiTenancy;

/// <summary>
/// 在 MySQL 租户库上执行幂等 DDL/DML 脚本（支持 PREPARE / 用户变量等多语句批次）。
/// </summary>
public static class TenantSqlScriptExecutor
{
    private static readonly Regex PrepareBlockEndRegex =
        new(@"DEALLOCATE\s+PREPARE\s+\w+\s*;\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);

    private static readonly Regex CreateTableEndRegex =
        new(@"\)\s*ENGINE\s*=.+;\s*$", RegexOptions.IgnoreCase | RegexOptions.Compiled);

    /// <summary>
    /// 执行完整 SQL 脚本；仅支持 MySQL。
    /// </summary>
    public static async Task ExecuteMySqlScriptAsync(
        string connectionString,
        string script,
        CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(script))
        {
            return;
        }

        var normalizedConnectionString = EnsureMySqlScriptConnectionOptions(connectionString);
        await using var connection = new MySqlConnection(normalizedConnectionString);
        await connection.OpenAsync(cancellationToken);

        foreach (var batch in SplitIntoBatches(script))
        {
            await using var command = new MySqlCommand(batch, connection);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }

    /// <summary>
    /// 非 MySQL 租户库跳过脚本（当前脚本均为 MySQL 方言）。
    /// </summary>
    public static bool Supports(DbType dbType) => dbType == DbType.MySql;

    /// <summary>
    /// 将脚本拆成可独立提交的批次：PREPARE 块、CREATE TABLE、UPDATE 等。
    /// </summary>
    internal static IReadOnlyList<string> SplitIntoBatches(string script)
    {
        var lines = script.Replace("\r\n", "\n").Split('\n');
        var batches = new List<string>();
        var current = new StringBuilder();

        foreach (var rawLine in lines)
        {
            var line = rawLine.TrimEnd();
            if (string.IsNullOrWhiteSpace(line) || line.TrimStart().StartsWith("--", StringComparison.Ordinal))
            {
                continue;
            }

            current.AppendLine(line);

            var trimmed = line.Trim();
            if (PrepareBlockEndRegex.IsMatch(trimmed)
                || CreateTableEndRegex.IsMatch(trimmed)
                || (trimmed.StartsWith("UPDATE ", StringComparison.OrdinalIgnoreCase) && trimmed.EndsWith(';')))
            {
                var batch = current.ToString().Trim();
                if (!string.IsNullOrEmpty(batch))
                {
                    batches.Add(batch);
                }

                current.Clear();
            }
        }

        var tail = current.ToString().Trim();
        if (!string.IsNullOrEmpty(tail))
        {
            batches.Add(tail);
        }

        return batches;
    }

    private static string EnsureMySqlScriptConnectionOptions(string connectionString)
    {
        var builder = new MySqlConnectionStringBuilder(connectionString)
        {
            AllowUserVariables = true
        };

        return builder.ConnectionString;
    }
}