// Admin.NET 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。使用本项目应遵守相关法律法规和许可证的要求。
//
// 本项目主要遵循 MIT 许可证和 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-MIT 和 LICENSE-APACHE 文件。
//
// 不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动！任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任！

#if NET10_0_OR_GREATER

using System.Data;
using System.Diagnostics;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Builder;
using XiHan.Framework.Utils.Logging;
using XiHan.Framework.Utils.Reflections;

namespace Admin.NET.Core.Update;

/// <summary>
/// 自动版本更新中间件拓展
/// </summary>
[SuppressSniffer]
public static class AutoVersionUpdate
{
    private const int MigrationCommandTimeoutSeconds = 600;
    private const string MigrationLogTable = "sys_db_migration_log";
    private const string StatusRunning = "Running";
    private const string StatusSuccess = "Success";
    private const string StatusFailed = "Failed";
    private const string StatusSkipped = "Skipped";
    private const string VerifyNotConfigured = "NotConfigured";
    private const string VerifySuccess = "Success";
    private const string VerifyFailed = "Failed";

    /// <summary>
    /// 使用自动版本更新中间件
    /// </summary>
    public static IApplicationBuilder UseAutoVersionUpdate(this IApplicationBuilder app)
    {
        LogHelper.Info("AutoVersionUpdate 中间件运行");

        var snowIdOpt = App.GetConfig<SnowIdOptions>("SnowId", true);
        if (snowIdOpt.WorkerId != 1)
        {
            LogHelper.Handle("非主节点，不执行脚本");
            return app;
        }

        var stopOnFailure = App.GetConfig<bool?>("AutoVersionUpdate:StopApplicationOnFailure") ?? true;

        try
        {
            RunPendingMigrations(app);
            LogHelper.Success("AutoVersionUpdate 中间件结束");
        }
        catch (Exception ex)
        {
            LogHelper.Error($"AutoVersionUpdate 执行失败：{ex.Message}");
            if (stopOnFailure)
                throw;
        }

        return app;
    }

    private static void RunPendingMigrations(IApplicationBuilder app)
    {
        using var scope = App.GetRequiredService<IServiceScopeFactory>().CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();

        EnsureMigrationLogTable(db);

        var currentVersionText = GetEntryAssemblyCurrentVersion();
        var currentVersion = ParseVersion(currentVersionText);
        var historyFromTxt = GetEntryAssemblyHistoryVersionInfo();
        var databaseName = GetDatabaseName(db);

        LogHelper.Handle($"当前版本：{currentVersionText}，目标数据库：{databaseName ?? "(unknown)"}");

        var scripts = LoadMigrationScripts();
        var migrationRows = LoadMigrationLogs(db);

        // 已登记的「迁移记录被误记」重整。必须在计算 pending **之前**做：
        // ShouldSkipScript 正是在计算 pending 时就对 hash 不符抛错，放在执行循环里（像
        // TryRecoverKnownFailedMigration 那样）永远够不到这类事故。
        if (TryReconcileKnownCollisions(db, scripts, migrationRows))
            migrationRows = LoadMigrationLogs(db);

        var successVersions = migrationRows
            .Where(x => string.Equals(x.Status, StatusSuccess, StringComparison.OrdinalIgnoreCase))
            .Select(x => ParseVersion(x.Version))
            .ToHashSet();

        Version? legacyBoundary = null;
        if (migrationRows.Count == 0 &&
            !string.IsNullOrWhiteSpace(historyFromTxt.Version) &&
            historyFromTxt.IsRunScript &&
            Version.TryParse(historyFromTxt.Version, out var legacyVersion))
        {
            legacyBoundary = legacyVersion;
            LogHelper.Handle(
                $"检测到 {MigrationLogTable} 为空但 version.txt 存在，legacy 跳过边界：{legacyBoundary}");
        }

        var pending = scripts
            .Where(s => s.ParsedVersion <= currentVersion)
            .Where(s => !ShouldSkipScript(s, migrationRows, legacyBoundary))
            .OrderBy(s => s.ParsedVersion)
            .ToList();

        LogHelper.Handle($"发现脚本 {scripts.Count} 个，已成功 {successVersions.Count} 个，待执行 {pending.Count} 个");

        if (pending.Count == 0)
        {
            SetEntryAssemblyCurrentVersion(currentVersionText, true);
            return;
        }

        foreach (var script in pending)
        {
            TryRecoverKnownFailedMigration(db, script, migrationRows);
            ExecuteOneMigrationScript(db, script, databaseName);
        }

        SetEntryAssemblyCurrentVersion(currentVersionText, true);
    }

    /// <summary>
    /// 已登记的「迁移记录被误记」重整。裁决全部交给 <see cref="MigrationCollisionPolicy"/>（纯函数、可单测），
    /// 本方法只负责执行副作用。
    ///
    /// <para><b>不删除任何历史行</b>：只把误记行的 status 置为
    /// <see cref="MigrationCollisionPolicy.SupersededStatus"/>，随后由原有正常流程执行真正的脚本，
    /// <see cref="UpsertMigrationLog"/> 会按 version 原地把同一行覆写成真实执行结果。</para>
    ///
    /// <para><b>幂等</b>：重整后若进程崩溃，该行停在 Superseded、hash 仍是旧值，
    /// 下次启动闸门 3 不成立 ⇒ 返回 None，而 ShouldSkipScript 对非 Success 行返回 false，
    /// 脚本照常执行，仍然收敛。执行成功后 hash 变为目标值，闸门 4 不成立 ⇒ 永不再触发。</para>
    /// </summary>
    /// <returns>是否实际改动过迁移日志（true 时调用方需重新载入）。</returns>
    private static bool TryReconcileKnownCollisions(
        ISqlSugarClient db,
        List<MigrationScript> scripts,
        List<MigrationLogRow> migrationRows)
    {
        var changed = false;

        foreach (var plan in MigrationCollisionPolicy.KnownCollisions)
        {
            var rows = migrationRows
                .Where(x => string.Equals(x.Version, plan.Version, StringComparison.OrdinalIgnoreCase))
                .ToList();
            var row = rows.Count == 1 ? rows[0] : null;
            var displacedRow = migrationRows.FirstOrDefault(x =>
                string.Equals(x.Version, plan.DisplacedVersion, StringComparison.OrdinalIgnoreCase));

            var targetScript = scripts.FirstOrDefault(s =>
                string.Equals(s.Version, plan.Version, StringComparison.OrdinalIgnoreCase));
            var displacedScript = scripts.FirstOrDefault(s =>
                string.Equals(s.Version, plan.DisplacedVersion, StringComparison.OrdinalIgnoreCase));

            var outcome = MigrationCollisionPolicy.Decide(
                plan.Version,
                rows.Count,
                row?.Status,
                row?.FileHash,
                targetScript != null && MigrationScriptHash.MatchesFile(plan.ExpectedDiskHash, targetScript.FilePath),
                displacedRow?.Status,
                displacedRow?.FileHash,
                displacedScript != null && MigrationScriptHash.MatchesFile(plan.DisplacedFileHash, displacedScript.FilePath));

            if (outcome.Decision == MigrationCollisionDecision.None) continue;

            if (outcome.Decision == MigrationCollisionDecision.Reject)
                throw new InvalidOperationException(
                    $"版本 {plan.Version} 命中已登记的记录冲突策略 {plan.Strategy}，但拒绝执行："
                    + $"{outcome.RejectReason}。该操作会修改迁移历史，不对未确认状态执行，请人工确认。");

            LogHelper.Handle(
                $"Migration collision reconcile start version={plan.Version} strategy={plan.Strategy} "
                + $"collided={plan.CollidedFileHash} displaced_by={plan.DisplacedVersion}");

            ResetCollidedMigrationRecord(db, plan);
            changed = true;

            LogHelper.Handle(
                $"Migration collision reconcile done version={plan.Version}；"
                + "该行已置为 Superseded，接下来由原迁移脚本正常执行并原地覆写，状态由正常流程写入。");
        }

        return changed;
    }

    /// <summary>
    /// 把误记行的状态重整掉。事务内执行，命中数必须恰为 1，否则回滚并停机。
    /// 谓词同时钉死 version + file_hash + status，确保只可能命中那一行。
    /// </summary>
    private static void ResetCollidedMigrationRecord(ISqlSugarClient db, MigrationCollisionPlan plan)
    {
        var audit =
            $"{plan.Strategy}: 本行原记录 version={plan.Version} / hash={plan.CollidedFileHash}，"
            + $"实为 {plan.DisplacedVersion} 的脚本内容被误记；该内容已由 {plan.DisplacedVersion} "
            + $"（hash={plan.DisplacedFileHash}）合法记录在案。本行已重整，待 {plan.Version} 正式执行后覆写。";

        db.Ado.BeginTran();
        try
        {
            var affected = db.Ado.ExecuteCommand(
                $"""
                 UPDATE {MigrationLogTable}
                    SET status = @newStatus,
                        error_message = @audit,
                        updated_at = @now
                  WHERE version = @version
                    AND file_hash = @collidedHash
                    AND status = '{StatusSuccess}'
                 """,
                new
                {
                    newStatus = MigrationCollisionPolicy.SupersededStatus,
                    audit,
                    now = DateTime.Now,
                    version = plan.Version,
                    collidedHash = plan.CollidedFileHash
                });

            if (affected != 1)
                throw new InvalidOperationException(
                    $"重整 {plan.Version} 误记行时命中 {affected} 行，期望恰好 1 行。已回滚，不继续启动。");

            db.Ado.CommitTran();
        }
        catch
        {
            db.Ado.RollbackTran();
            throw;
        }
    }

    /// <summary>
    /// 已知失败迁移的「重跑前恢复」。裁决全部交给 <see cref="MigrationRecoveryPolicy"/>（纯函数、可单测），
    /// 本方法只负责执行副作用。
    ///
    /// <para><b>已 Success 的环境零影响</b>：<see cref="ShouldSkipScript"/> 已把它们排除在 pending 之外，
    /// 本方法根本不会被调用；策略里再判一次状态是纵深防御。</para>
    ///
    /// <para><b>幂等</b>：TRUNCATE 空表安全。若 TRUNCATE 之后进程崩溃，下次启动日志行仍是 Failed、
    /// hash 仍是原始值，会再 TRUNCATE 一次空表，无副作用。</para>
    ///
    /// <para><b>不写迁移日志</b>：恢复只清数据，状态一律由原有正常流程写入 ——
    /// 让「这条迁移到底成没成功」始终是脚本自己跑出来的结论。</para>
    /// </summary>
    private static void TryRecoverKnownFailedMigration(
        ISqlSugarClient db,
        MigrationScript script,
        List<MigrationLogRow> migrationRows)
    {
        var row = migrationRows.FirstOrDefault(x =>
            string.Equals(x.Version, script.Version, StringComparison.OrdinalIgnoreCase));

        var registered = MigrationRecoveryPolicy.KnownFailedRecoveries.FirstOrDefault(x =>
            string.Equals(x.Version, script.Version, StringComparison.OrdinalIgnoreCase));

        // 只有登记过的版本才需要读盘算 hash，避免给每条迁移平白加一次 IO。
        var diskMatches = registered != null
                          && MigrationScriptHash.MatchesFile(registered.OriginalFileHash, script.FilePath);

        var outcome = MigrationRecoveryPolicy.Decide(
            script.Version, row?.Status, row?.FileHash, diskMatches);

        if (outcome.Decision == MigrationRecoveryDecision.None) return;

        if (outcome.Decision == MigrationRecoveryDecision.Reject)
            throw new InvalidOperationException(
                $"版本 {script.Version} 处于 Failed 且命中已登记的恢复策略 {outcome.Plan!.Strategy}，"
                + $"但拒绝执行：{outcome.RejectReason}。"
                + "该策略含不可回滚操作，不对未知版本执行，请人工确认。");

        var plan = outcome.Plan!;
        if (!TableExists(db, plan.TableName)) return;

        LogHelper.Handle(
            $"Migration recovery start version={plan.Version} strategy={plan.Strategy} table={plan.TableName}");

        db.Ado.ExecuteCommand($"TRUNCATE TABLE `{plan.TableName}`");

        LogHelper.Handle(
            $"Migration recovery done version={plan.Version} strategy={plan.Strategy}；"
            + "接下来由原迁移脚本原样重跑，日志状态由正常流程写入。");
    }

    /// <summary>目标表是否存在于当前库。恢复策略只在表确实存在时才动手。</summary>
    private static bool TableExists(ISqlSugarClient db, string tableName)
    {
        var count = db.Ado.SqlQuerySingle<int>(
            "SELECT COUNT(*) FROM information_schema.TABLES "
            + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @name",
            new { name = tableName });
        return count > 0;
    }

    private static bool ShouldSkipScript(
        MigrationScript script,
        List<MigrationLogRow> migrationRows,
        Version? legacyBoundary)
    {
        var row = migrationRows.FirstOrDefault(x =>
            string.Equals(x.Version, script.Version, StringComparison.OrdinalIgnoreCase));

        if (row != null)
        {
            if (string.Equals(row.Status, StatusSuccess, StringComparison.OrdinalIgnoreCase))
            {
                // 只容忍 CRLF/CR/LF 行尾差异（同一文件在 Windows 与 Linux 检出字节不同）；
                // SQL 字符内容一旦变化仍然抛错，历史脚本不可被静默改写。
                if (!MigrationScriptHash.MatchesFile(row.FileHash, script.FilePath))
                {
                    throw new InvalidOperationException(
                        $"版本脚本 {script.FileName} 的 SHA256 与已成功记录不一致，禁止静默覆盖。请新建更高版本脚本。");
                }

                LogHelper.Handle($"版本 {script.Version} 已成功且 hash 未变，跳过");
                return true;
            }

            return false;
        }

        if (legacyBoundary != null && script.ParsedVersion <= legacyBoundary)
        {
            LogHelper.Handle($"版本 {script.Version} 处于 legacy 边界内，跳过");
            return true;
        }

        return false;
    }

    private static void ExecuteOneMigrationScript(ISqlSugarClient db, MigrationScript script, string? databaseName)
    {
        var sql = File.ReadAllText(script.FilePath);
        if (string.IsNullOrWhiteSpace(sql))
        {
            LogHelper.Handle($"版本 {script.Version} 脚本为空，记录 Skipped");
            UpsertMigrationLog(db, script, databaseName, StatusSkipped, 0, 0, VerifyNotConfigured, null, 0);
            return;
        }

        if (SqlScriptSplitter.ContainsDelimiterDirective(sql))
        {
            throw new InvalidOperationException(
                $"版本脚本 {script.FileName} 包含 DELIMITER 指令，当前执行器不支持，请改为普通 SQL 或手工执行。");
        }

        var startedAt = DateTime.Now;
        var sw = Stopwatch.StartNew();
        UpsertMigrationLog(db, script, databaseName, StatusRunning, 0, 0, VerifyNotConfigured, null, 0);
        var originalCommandTimeout = db.Ado.CommandTimeOut;
        db.Ado.CommandTimeOut = Math.Max(originalCommandTimeout, MigrationCommandTimeoutSeconds);

        try
        {
            LogHelper.Handle($"执行版本 {script.Version} 脚本 {script.FileName}，SHA256={script.Hash}");

            var executeResult = SqlScriptSplitter.Execute(db, sql);
            var verifyStatus = RunVerifyScriptIfExists(db, script);

            sw.Stop();
            UpsertMigrationLog(
                db,
                script,
                databaseName,
                StatusSuccess,
                executeResult.StatementCount,
                executeResult.AffectedRows,
                verifyStatus,
                null,
                sw.ElapsedMilliseconds);

            LogHelper.Handle(
                $"版本 {script.Version} 成功：语句 {executeResult.StatementCount} 条，影响行 {executeResult.AffectedRows}，校验 {verifyStatus}，耗时 {sw.ElapsedMilliseconds}ms");
        }
        catch (Exception ex)
        {
            sw.Stop();
            var message = BuildExceptionMessage(ex);
            UpsertMigrationLog(
                db,
                script,
                databaseName,
                StatusFailed,
                0,
                0,
                VerifyFailed,
                message,
                sw.ElapsedMilliseconds);

            LogHelper.Error($"AutoVersionUpdate 版本 {script.Version} 失败：{message}");
            throw new InvalidOperationException($"AutoVersionUpdate 版本 {script.Version} 执行失败：{message}", ex);
        }
        finally
        {
            db.Ado.CommandTimeOut = originalCommandTimeout;
        }
    }

    private static string RunVerifyScriptIfExists(ISqlSugarClient db, MigrationScript script)
    {
        var verifyPath = Path.ChangeExtension(script.FilePath, ".verify.sql");
        if (!File.Exists(verifyPath))
            return VerifyNotConfigured;

        var verifySql = File.ReadAllText(verifyPath);
        var statements = SqlScriptSplitter.Split(verifySql);
        if (statements.Count == 0)
            return VerifyNotConfigured;

        for (var i = 0; i < statements.Count; i++)
        {
            var statement = statements[i];
            var result = db.Ado.SqlQuerySingle<object>(statement);
            if (!IsTruthy(result))
            {
                throw new InvalidOperationException(
                    $"校验 SQL 第 {i + 1} 条未通过：{TrimForLog(statement)}");
            }
        }

        return VerifySuccess;
    }

    private static bool IsTruthy(object? value)
    {
        if (value == null || value is DBNull)
            return false;

        return value switch
        {
            bool b => b,
            byte or sbyte or short or ushort or int or uint or long or ulong or float or double or decimal =>
                Convert.ToDecimal(value) != 0,
            string s => !string.IsNullOrWhiteSpace(s) &&
                          !string.Equals(s, "0", StringComparison.OrdinalIgnoreCase) &&
                          !string.Equals(s, "false", StringComparison.OrdinalIgnoreCase),
            _ => true
        };
    }

    private static void EnsureMigrationLogTable(ISqlSugarClient db)
    {
        db.Ado.ExecuteCommand(
            """
            CREATE TABLE IF NOT EXISTS sys_db_migration_log (
                id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
                version VARCHAR(50) NOT NULL COMMENT '脚本版本',
                file_name VARCHAR(255) NOT NULL COMMENT '脚本文件名',
                file_hash VARCHAR(128) NOT NULL COMMENT '脚本 SHA256',
                status VARCHAR(20) NOT NULL COMMENT 'Running/Success/Failed/Skipped',
                started_at DATETIME(3) NOT NULL COMMENT '开始时间',
                finished_at DATETIME(3) NULL COMMENT '结束时间',
                elapsed_ms BIGINT NULL COMMENT '耗时毫秒',
                statement_count INT NOT NULL DEFAULT 0 COMMENT '执行语句数',
                affected_rows BIGINT NULL COMMENT '影响行数合计',
                verify_status VARCHAR(20) NULL COMMENT '校验状态',
                error_message LONGTEXT NULL COMMENT '错误信息',
                database_name VARCHAR(128) NULL COMMENT '执行数据库',
                created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
                updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
                PRIMARY KEY (id),
                UNIQUE KEY uk_sys_db_migration_log_version (version),
                KEY idx_sys_db_migration_log_status (status),
                KEY idx_sys_db_migration_log_started_at (started_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库版本脚本执行记录'
            """);
    }

    private static List<MigrationLogRow> LoadMigrationLogs(ISqlSugarClient db)
    {
        try
        {
            return db.Ado.SqlQuery<MigrationLogRow>(
                $"""
                 SELECT version AS Version, file_hash AS FileHash, status AS Status
                 FROM {MigrationLogTable}
                 """);
        }
        catch
        {
            return [];
        }
    }

    private static void UpsertMigrationLog(
        ISqlSugarClient db,
        MigrationScript script,
        string? databaseName,
        string status,
        int statementCount,
        long affectedRows,
        string verifyStatus,
        string? errorMessage,
        long elapsedMs)
    {
        var now = DateTime.Now;
        var existing = db.Ado.SqlQuerySingle<int?>(
            $"SELECT id FROM {MigrationLogTable} WHERE version = @version LIMIT 1",
            new { version = script.Version });

        if (existing is > 0)
        {
            db.Ado.ExecuteCommand(
                $"""
                 UPDATE {MigrationLogTable}
                 SET file_name = @fileName,
                     file_hash = @fileHash,
                     status = @status,
                     started_at = CASE WHEN @status = '{StatusRunning}' THEN @startedAt ELSE started_at END,
                     finished_at = @finishedAt,
                     elapsed_ms = @elapsedMs,
                     statement_count = @statementCount,
                     affected_rows = @affectedRows,
                     verify_status = @verifyStatus,
                     error_message = @errorMessage,
                     database_name = @databaseName,
                     updated_at = @updatedAt
                 WHERE version = @version
                 """,
                new
                {
                    version = script.Version,
                    fileName = script.FileName,
                    fileHash = script.Hash,
                    status,
                    startedAt = now,
                    finishedAt = status is StatusRunning ? (DateTime?)null : now,
                    elapsedMs = status is StatusRunning ? (long?)null : elapsedMs,
                    statementCount,
                    affectedRows,
                    verifyStatus,
                    errorMessage,
                    databaseName,
                    updatedAt = now
                });
            return;
        }

        db.Ado.ExecuteCommand(
            $"""
             INSERT INTO {MigrationLogTable}
             (version, file_name, file_hash, status, started_at, finished_at, elapsed_ms,
              statement_count, affected_rows, verify_status, error_message, database_name)
             VALUES
             (@version, @fileName, @fileHash, @status, @startedAt, @finishedAt, @elapsedMs,
              @statementCount, @affectedRows, @verifyStatus, @errorMessage, @databaseName)
             """,
            new
            {
                version = script.Version,
                fileName = script.FileName,
                fileHash = script.Hash,
                status,
                startedAt = now,
                finishedAt = status is StatusRunning ? (DateTime?)null : now,
                elapsedMs = status is StatusRunning ? (long?)null : elapsedMs,
                statementCount,
                affectedRows,
                verifyStatus,
                errorMessage,
                databaseName
            });
    }

    private static List<MigrationScript> LoadMigrationScripts()
    {
        var path = Path.Combine(AppContext.BaseDirectory, "UpdateScripts");
        if (!Directory.Exists(path))
            return [];

        return Directory.GetFiles(path, "*.sql", SearchOption.TopDirectoryOnly)
            .Where(file =>
            {
                var name = Path.GetFileName(file);
                return !name.EndsWith(".verify.sql", StringComparison.OrdinalIgnoreCase);
            })
            .Select(file =>
            {
                var versionText = Path.GetFileNameWithoutExtension(file);
                if (!Version.TryParse(versionText, out var parsedVersion))
                    return null;

                return new MigrationScript(
                    versionText,
                    parsedVersion,
                    file,
                    Path.GetFileName(file),
                    ComputeSha256(file));
            })
            .Where(x => x != null)
            .Cast<MigrationScript>()
            .OrderBy(x => x.ParsedVersion)
            .ToList();
    }

    private static string GetEntryAssemblyCurrentVersion()
    {
        var entryAssemblyVersion = ReflectionHelper.GetEntryAssemblyVersion();
        return entryAssemblyVersion.ToString(3);
    }

    private static void SetEntryAssemblyCurrentVersion(string version, bool isRunScript)
    {
        var path = Path.Combine(AppContext.BaseDirectory, "version.txt");
        var now = DateTime.Now;
        File.WriteAllText(path, $"{version}^{now:yyyy-MM-dd HH:mm:ss}^{isRunScript}");
    }

    private static HistoryVersionInfo GetEntryAssemblyHistoryVersionInfo()
    {
        var path = Path.Combine(AppContext.BaseDirectory, "version.txt");
        if (!File.Exists(path))
            return new HistoryVersionInfo(string.Empty, string.Empty, false);

        var info = File.ReadAllText(path);
        if (!info.Contains('^'))
            return new HistoryVersionInfo(string.Empty, string.Empty, false);

        var parts = info.Split('^');
        var version = parts.Length > 0 ? parts[0] : string.Empty;
        var date = parts.Length > 1 ? parts[1] : string.Empty;
        var isRunScript = parts.Length > 2 && parts[2].ToBoolean();
        return new HistoryVersionInfo(version, date, isRunScript);
    }

    private static Version ParseVersion(string value)
    {
        if (!Version.TryParse(value, out var version))
            throw new InvalidOperationException($"非法版本号：{value}");
        return version;
    }

    /// <summary>
    /// 迁移脚本 hash 一律用 LF 归一化结果（<see cref="MigrationScriptHash.ComputeCanonical"/>），
    /// 使 Windows / Linux / macOS 执行同一文件时写入数据库的 hash 一致。
    /// 历史上已写入的 raw CRLF / raw LF 记录由 <see cref="MigrationScriptHash.Matches"/> 继续兼容。
    /// </summary>
    private static string ComputeSha256(string filePath)
        => MigrationScriptHash.ComputeCanonicalFromFile(filePath);

    private static string? GetDatabaseName(ISqlSugarClient db)
    {
        try
        {
            return db.Ado.GetString("SELECT DATABASE()");
        }
        catch
        {
            return null;
        }
    }

    private static string BuildExceptionMessage(Exception ex)
    {
        var messages = new List<string>();
        for (var current = ex; current != null; current = current.InnerException)
            messages.Add(current.Message);
        return string.Join(" | ", messages);
    }

    private static string TrimForLog(string sql, int maxLength = 300)
    {
        var normalized = sql.Replace('\r', ' ').Replace('\n', ' ').Trim();
        return normalized.Length <= maxLength ? normalized : normalized[..maxLength] + "...";
    }

    private sealed record MigrationScript(
        string Version,
        Version ParsedVersion,
        string FilePath,
        string FileName,
        string Hash);

    private sealed class MigrationLogRow
    {
        public string Version { get; set; } = string.Empty;
        public string FileHash { get; set; } = string.Empty;
        public string Status { get; set; } = string.Empty;
    }

    private sealed record ScriptExecuteResult(int StatementCount, long AffectedRows);

    private static class SqlScriptSplitter
    {
        public static bool ContainsDelimiterDirective(string sql) =>
            sql.Contains("DELIMITER", StringComparison.OrdinalIgnoreCase);

        public static ScriptExecuteResult Execute(ISqlSugarClient db, string sql)
            // SET @var / PREPARE / EXECUTE 依赖同一连接，但不需要作为一个超长批次提交。
            // 分句执行既保留会话变量，又让超时和失败定位落到单条 DDL。
            => ExecuteSplitWithSharedConnection(db, sql);

        private static ScriptExecuteResult ExecuteSplitWithSharedConnection(ISqlSugarClient db, string sql)
        {
            var statements = Split(sql);
            long affectedRows = 0;
            var wasOpen = EnsureConnectionOpen(db);

            try
            {
                for (var i = 0; i < statements.Count; i++)
                {
                    var statement = statements[i];
                    try
                    {
                        using var command = db.Ado.Connection.CreateCommand();
                        command.CommandText = statement;
                        command.CommandTimeout = db.Ado.CommandTimeOut;
                        affectedRows += Math.Max(0, command.ExecuteNonQuery());
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException(
                            $"第 {i + 1}/{statements.Count} 条语句失败：{TrimForLog(statement)} | {ex.Message}", ex);
                    }
                }

                return new ScriptExecuteResult(statements.Count, affectedRows);
            }
            finally
            {
                RestoreConnection(db, wasOpen);
            }
        }

        private static bool EnsureConnectionOpen(ISqlSugarClient db)
        {
            var connection = db.Ado.Connection;
            if (connection.State == ConnectionState.Open)
                return true;

            connection.Open();
            return false;
        }

        private static void RestoreConnection(ISqlSugarClient db, bool wasOpen)
        {
            if (wasOpen)
                return;

            var connection = db.Ado.Connection;
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }

        public static List<string> Split(string sql)
        {
            var statements = new List<string>();
            if (string.IsNullOrWhiteSpace(sql))
                return statements;

            var current = new StringBuilder();
            var inSingleQuote = false;
            var inDoubleQuote = false;
            var inBacktick = false;
            var inLineComment = false;
            var inBlockComment = false;

            for (var i = 0; i < sql.Length; i++)
            {
                var ch = sql[i];
                var next = i + 1 < sql.Length ? sql[i + 1] : '\0';

                if (inLineComment)
                {
                    current.Append(ch);
                    if (ch is '\n' or '\r')
                        inLineComment = false;
                    continue;
                }

                if (inBlockComment)
                {
                    current.Append(ch);
                    if (ch == '*' && next == '/')
                    {
                        current.Append(next);
                        i++;
                        inBlockComment = false;
                    }

                    continue;
                }

                if (!inSingleQuote && !inDoubleQuote && !inBacktick)
                {
                    if (ch == '-' && next == '-')
                    {
                        inLineComment = true;
                        current.Append(ch);
                        continue;
                    }

                    if (ch == '#')
                    {
                        inLineComment = true;
                        current.Append(ch);
                        continue;
                    }

                    if (ch == '/' && next == '*')
                    {
                        inBlockComment = true;
                        current.Append(ch);
                        continue;
                    }
                }

                if (!inDoubleQuote && !inBacktick && ch == '\'' && !inSingleQuote)
                {
                    inSingleQuote = true;
                    current.Append(ch);
                    continue;
                }

                if (inSingleQuote)
                {
                    current.Append(ch);
                    if (ch == '\'' && next == '\'')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\\' && next != '\0')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\'')
                        inSingleQuote = false;
                    continue;
                }

                if (!inSingleQuote && !inBacktick && ch == '"' && !inDoubleQuote)
                {
                    inDoubleQuote = true;
                    current.Append(ch);
                    continue;
                }

                if (inDoubleQuote)
                {
                    current.Append(ch);
                    if (ch == '"' && next == '"')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '\\' && next != '\0')
                    {
                        current.Append(next);
                        i++;
                        continue;
                    }

                    if (ch == '"')
                        inDoubleQuote = false;
                    continue;
                }

                if (!inSingleQuote && !inDoubleQuote && ch == '`' && !inBacktick)
                {
                    inBacktick = true;
                    current.Append(ch);
                    continue;
                }

                if (inBacktick)
                {
                    current.Append(ch);
                    if (ch == '`')
                        inBacktick = false;
                    continue;
                }

                if (ch == ';')
                {
                    AppendStatement(statements, current);
                    continue;
                }

                current.Append(ch);
            }

            AppendStatement(statements, current);
            return statements;
        }

        private static void AppendStatement(List<string> statements, StringBuilder current)
        {
            var text = current.ToString().Trim();
            current.Clear();
            if (string.IsNullOrWhiteSpace(text))
                return;

            statements.Add(text);
        }
    }
}

public record HistoryVersionInfo(string Version, string Date, bool IsRunScript);

#endif // NET10_0_OR_GREATER