AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SmartOps/KpiSqlReadOnlyExecutor.cs

using System.Data;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>单条 KPI SQL 的运行参数（引擎服务端绑定，不信任前端）。</summary>
public sealed class KpiSqlRunParams
{
    public long TenantId { get; set; }
    public long FactoryId { get; set; } = 1;
    public string ModuleCode { get; set; } = string.Empty;
    public string MetricCode { get; set; } = string.Empty;
    public DateTime BizDate { get; set; }
    public DateTime PeriodStart { get; set; }
    public DateTime PeriodEnd { get; set; }
    public string SourceZtid { get; set; } = "pbxfxp";
}

/// <summary>KPI SQL 执行结果契约。</summary>
public sealed class KpiSqlExecutionResult
{
    /// <summary>SUCCESS / NO_DATA / FAILED。</summary>
    public string Status { get; set; } = "FAILED";
    public decimal? MetricValue { get; set; }
    public decimal? NumeratorValue { get; set; }
    public decimal? DenominatorValue { get; set; }
    public string? ResultStatus { get; set; }
    public string? ResultMessage { get; set; }
    public int RowCount { get; set; }
    public long DurationMs { get; set; }
    public string? ErrorCode { get; set; }
    public string? ErrorMessage { get; set; }
    public string SqlHash { get; set; } = string.Empty;
}

/// <summary>
/// KPI 配置 SQL 只读执行器。用**专用隔离连接** + `START TRANSACTION READ ONLY`（DB 层 1792 强制拒写）
/// + 命令超时 + 参数化 + 单行结果契约解析。绝不在此写任何业务数据；KPI 日值由引擎另行 Upsert。
/// </summary>
public sealed class KpiSqlReadOnlyExecutor : ITransient
{
    /// <summary>正式执行的系统超时上限（秒）。</summary>
    public const int SystemMaxTimeoutSeconds = 60;

    /// <summary>试算超时上限（秒，建议小于正式）。</summary>
    public const int PreviewMaxTimeoutSeconds = 20;

    /// <summary>最大返回行数（契约要求 0/1 行；超过即 FAILED，避免大结果占用连接）。</summary>
    public const int MaxResultRows = 100;

    /// <summary>本地中台库数据源编码（解析为主库 aidopdev）。</summary>
    public const string LocalDataSourceCode = "MDP_LOCAL";

    private readonly ISqlSugarClient _db;

    public KpiSqlReadOnlyExecutor(ISqlSugarClient db)
    {
        _db = db;
    }

    public static string ComputeSqlHash(string sql)
    {
        var bytes = SHA256.HashData(Encoding.UTF8.GetBytes(sql ?? string.Empty));
        return Convert.ToHexString(bytes);
    }

    /// <summary>
    /// 只读执行一段 SQL 并解析单行结果契约。dataSourceCode 目前仅支持 MDP_LOCAL（本地中台库）。
    /// </summary>
    public async Task<KpiSqlExecutionResult> ExecuteAsync(
        string? dataSourceCode, string sql, int timeoutSeconds, bool isPreview,
        KpiSqlRunParams pars, CancellationToken ct)
    {
        var startedAt = DateTime.Now;
        var res = new KpiSqlExecutionResult { SqlHash = ComputeSqlHash(sql) };

        var cap = isPreview ? PreviewMaxTimeoutSeconds : SystemMaxTimeoutSeconds;
        var timeout = timeoutSeconds <= 0 ? cap : Math.Min(timeoutSeconds, cap);

        var code = string.IsNullOrWhiteSpace(dataSourceCode) ? LocalDataSourceCode : dataSourceCode.Trim();
        if (!string.Equals(code, LocalDataSourceCode, StringComparison.OrdinalIgnoreCase))
        {
            res.Status = "FAILED";
            res.ErrorCode = "DATASOURCE_NOT_SUPPORTED";
            res.ErrorMessage = $"数据源 {code} 第一版暂不支持（仅 {LocalDataSourceCode}）";
            res.DurationMs = (long)(DateTime.Now - startedAt).TotalMilliseconds;
            return res;
        }

        // 专用隔离连接（不复用共享 _db，避免只读事务污染主连接 + 并发干扰）。
        var main = _db.CurrentConnectionConfig;
        var roConfig = new ConnectionConfig
        {
            ConnectionString = main.ConnectionString,
            DbType = main.DbType,
            IsAutoCloseConnection = false,
        };

        using var roDb = new SqlSugarClient(roConfig);
        try
        {
            roDb.Ado.CommandTimeOut = timeout;
            await roDb.Ado.ExecuteCommandAsync("START TRANSACTION READ ONLY");
            try
            {
                ct.ThrowIfCancellationRequested();
                var sugarPars = BuildParameters(pars);
                var dt = await roDb.Ado.GetDataTableAsync(sql, sugarPars);
                ParseContract(dt, res);
            }
            finally
            {
                try { await roDb.Ado.ExecuteCommandAsync("ROLLBACK"); } catch { /* 专用连接随 Dispose 关闭 */ }
            }
        }
        catch (OperationCanceledException) when (ct.IsCancellationRequested)
        {
            throw;
        }
        catch (Exception ex)
        {
            res.Status = "FAILED";
            res.ErrorCode = ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) >= 0
                            || ex.Message.IndexOf("Command Timeout", StringComparison.OrdinalIgnoreCase) >= 0
                ? "TIMEOUT" : "SQL_ERROR";
            res.ErrorMessage = Truncate(ex.Message, 480);
        }

        res.DurationMs = (long)(DateTime.Now - startedAt).TotalMilliseconds;
        return res;
    }

    private static SugarParameter[] BuildParameters(KpiSqlRunParams p) => new[]
    {
        new SugarParameter("@tenant_id", p.TenantId),
        new SugarParameter("@factory_id", p.FactoryId),
        new SugarParameter("@module_code", p.ModuleCode),
        new SugarParameter("@metric_code", p.MetricCode),
        new SugarParameter("@biz_date", p.BizDate.Date),
        new SugarParameter("@period_start", p.PeriodStart),
        new SugarParameter("@period_end", p.PeriodEnd),
        new SugarParameter("@ztid", p.SourceZtid),
    };

    private static void ParseContract(DataTable dt, KpiSqlExecutionResult res)
    {
        res.RowCount = dt.Rows.Count;
        if (dt.Rows.Count == 0)
        {
            res.Status = "NO_DATA";
            return;
        }
        if (dt.Rows.Count > MaxResultRows || dt.Rows.Count > 1)
        {
            res.Status = "FAILED";
            res.ErrorCode = "INVALID_RESULT_ROWS";
            res.ErrorMessage = $"正式执行 SQL 只能返回 0 或 1 行，实际 {dt.Rows.Count} 行";
            return;
        }

        var row = dt.Rows[0];
        if (!HasColumn(dt, "metric_value"))
        {
            res.Status = "FAILED";
            res.ErrorCode = "MISSING_METRIC_VALUE";
            res.ErrorMessage = "结果必须含列 metric_value";
            return;
        }

        res.NumeratorValue = TryDecimal(row, dt, "numerator_value");
        res.DenominatorValue = TryDecimal(row, dt, "denominator_value");
        res.ResultStatus = TryString(row, dt, "result_status");
        res.ResultMessage = TryString(row, dt, "result_message");

        var mvObj = row[GetColumn(dt, "metric_value")!];
        if (mvObj == null || mvObj == DBNull.Value)
        {
            res.Status = "NO_DATA";
            res.MetricValue = null;
            return;
        }
        if (!TryToDecimal(mvObj, out var mv))
        {
            res.Status = "FAILED";
            res.ErrorCode = "NON_NUMERIC";
            res.ErrorMessage = $"metric_value 非数值：{Truncate(mvObj.ToString() ?? "", 60)}";
            return;
        }
        res.MetricValue = mv;
        res.Status = "SUCCESS";
    }

    private static bool HasColumn(DataTable dt, string name) => GetColumn(dt, name) != null;

    private static string? GetColumn(DataTable dt, string name)
    {
        foreach (DataColumn c in dt.Columns)
            if (string.Equals(c.ColumnName, name, StringComparison.OrdinalIgnoreCase)) return c.ColumnName;
        return null;
    }

    private static decimal? TryDecimal(DataRow row, DataTable dt, string name)
    {
        var col = GetColumn(dt, name);
        if (col == null) return null;
        var v = row[col];
        if (v == null || v == DBNull.Value) return null;
        return TryToDecimal(v, out var d) ? d : (decimal?)null;
    }

    private static string? TryString(DataRow row, DataTable dt, string name)
    {
        var col = GetColumn(dt, name);
        if (col == null) return null;
        var v = row[col];
        return v == null || v == DBNull.Value ? null : v.ToString();
    }

    private static bool TryToDecimal(object v, out decimal d)
    {
        switch (v)
        {
            case decimal dec: d = dec; return true;
            case double db: d = (decimal)db; return true;
            case float f: d = (decimal)f; return true;
            case long l: d = l; return true;
            case int i: d = i; return true;
            case short s: d = s; return true;
            case byte b: d = b; return true;
            default:
                return decimal.TryParse(Convert.ToString(v, CultureInfo.InvariantCulture),
                    NumberStyles.Any, CultureInfo.InvariantCulture, out d);
        }
    }

    private static string Truncate(string s, int max) =>
        string.IsNullOrEmpty(s) ? "" : (s.Length <= max ? s : s.Substring(0, max));
}