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

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

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>解析后的单条维度结果行。</summary>
public sealed class KpiDimensionRow
{
    public DateTime ValueDate { get; set; }
    public string DimensionType { get; set; } = string.Empty;
    public string DimensionCode { get; set; } = string.Empty;
    public string? DimensionName { get; set; }
    public long? OrgId { get; set; }
    public long? FactoryId { get; set; }
    public string? MaterialCode { get; set; }
    public string? WorkOrderNo { get; set; }
    public string? CategoryCode { get; set; }
    public string? WarehouseCode { get; set; }
    public string? OrderNo { get; set; }
    public string? CustomerCode { get; set; }
    public string? ProductCode { get; set; }
    public string? EquipmentCode { get; set; }
    public decimal? MetricValue { get; set; }
    public decimal? Numerator { get; set; }
    public decimal? Denominator { get; set; }
    public decimal? SumValue { get; set; }
    public int? SampleCount { get; set; }
    public string? SourceKey { get; set; }
}

/// <summary>维度 SQL 执行结果契约（多行）。</summary>
public sealed class KpiDimensionExecutionResult
{
    /// <summary>SUCCESS / NO_DATA / FAILED。</summary>
    public string Status { get; set; } = "FAILED";
    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;
    public List<string> OutputColumns { get; set; } = new();
    public List<KpiDimensionRow> Rows { get; set; } = new();
}

/// <summary>
/// KPI 维度 SQL 只读执行器。与 <see cref="KpiSqlReadOnlyExecutor"/> 同构的只读隔离连接
/// + START TRANSACTION READ ONLY + 参数化，但解析**多行维度结果契约**（用于筛选/下钻）。
/// 必填列：value_date / dimension_type / dimension_code。绝不写业务数据。
/// </summary>
public sealed class KpiDimensionSqlExecutor : ITransient
{
    public const int SystemMaxTimeoutSeconds = 120;
    public const int PreviewMaxTimeoutSeconds = 30;

    /// <summary>维度结果最大行数（超过即 FAILED，避免大结果占用连接）。</summary>
    public const int MaxResultRows = 5000;

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

    private readonly ISqlSugarClient _db;

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

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

    public async Task<KpiDimensionExecutionResult> ExecuteAsync(
        string? dataSourceCode, string sql, int timeoutSeconds, bool isPreview,
        KpiSqlRunParams pars, CancellationToken ct)
    {
        var startedAt = DateTime.Now;
        var res = new KpiDimensionExecutionResult { 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;
        }

        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 readonly string[] RequiredColumns = { "value_date", "dimension_type", "dimension_code" };

    private static void ParseContract(DataTable dt, KpiDimensionExecutionResult res)
    {
        foreach (DataColumn c in dt.Columns) res.OutputColumns.Add(c.ColumnName);
        res.RowCount = dt.Rows.Count;

        if (dt.Rows.Count == 0)
        {
            res.Status = "NO_DATA";
            return;
        }
        if (dt.Rows.Count > MaxResultRows)
        {
            res.Status = "FAILED";
            res.ErrorCode = "TOO_MANY_ROWS";
            res.ErrorMessage = $"维度结果超过上限 {MaxResultRows} 行（实际 {dt.Rows.Count}），请在 SQL 内聚合或限定范围";
            return;
        }
        foreach (var col in RequiredColumns)
        {
            if (GetColumn(dt, col) == null)
            {
                res.Status = "FAILED";
                res.ErrorCode = "MISSING_REQUIRED_COLUMN";
                res.ErrorMessage = $"维度结果必须含列 {col}";
                return;
            }
        }

        foreach (DataRow row in dt.Rows)
        {
            var dimCode = TryString(row, dt, "dimension_code");
            if (string.IsNullOrWhiteSpace(dimCode)) continue; // 维度编码空的行跳过（无法作为下钻键）
            res.Rows.Add(new KpiDimensionRow
            {
                ValueDate = TryDate(row, dt, "value_date") ?? DateTime.MinValue,
                DimensionType = TryString(row, dt, "dimension_type") ?? string.Empty,
                DimensionCode = dimCode!,
                DimensionName = TryString(row, dt, "dimension_name") ?? dimCode,
                OrgId = TryLong(row, dt, "org_id"),
                FactoryId = TryLong(row, dt, "factory_id"),
                MaterialCode = TryString(row, dt, "material_code"),
                WorkOrderNo = TryString(row, dt, "work_order_no"),
                CategoryCode = TryString(row, dt, "category_code"),
                WarehouseCode = TryString(row, dt, "warehouse_code"),
                OrderNo = TryString(row, dt, "order_no"),
                CustomerCode = TryString(row, dt, "customer_code"),
                ProductCode = TryString(row, dt, "product_code"),
                EquipmentCode = TryString(row, dt, "equipment_code"),
                MetricValue = TryDecimal(row, dt, "metric_value"),
                Numerator = TryDecimal(row, dt, "numerator") ?? TryDecimal(row, dt, "numerator_value"),
                Denominator = TryDecimal(row, dt, "denominator") ?? TryDecimal(row, dt, "denominator_value"),
                SumValue = TryDecimal(row, dt, "sum_value"),
                SampleCount = (int?)TryLong(row, dt, "sample_count"),
                SourceKey = TryString(row, dt, "source_key"),
            });
        }

        res.RowCount = res.Rows.Count;
        res.Status = res.Rows.Count == 0 ? "NO_DATA" : "SUCCESS";
    }

    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 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 DateTime? TryDate(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;
        if (v is DateTime d) return d;
        return DateTime.TryParse(Convert.ToString(v, CultureInfo.InvariantCulture), CultureInfo.InvariantCulture,
            DateTimeStyles.None, out var parsed) ? parsed : (DateTime?)null;
    }

    private static long? TryLong(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 long.TryParse(Convert.ToString(v, CultureInfo.InvariantCulture), NumberStyles.Any,
            CultureInfo.InvariantCulture, out var l) ? l : (long?)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 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));
}