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

using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Dto.SmartOps;
using Admin.NET.Plugin.AiDOP.Entity;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// KPI 维度计算配置服务：CRUD + 校验 + 试算 + 发布/停用/激活（发布状态机，单生效版本事务保证）。
/// 与当前生效汇总配置版本绑定；租户显式控制（ClearFilter），落在运行时 KPI 数据租户。
/// SqlScript 属高危配置，接口须登录鉴权（Controller 层）。绝不改动 SUMMARY_SQL。
/// </summary>
public sealed class AdoSmartOpsKpiDimensionConfigService : ITransient
{
    private const string StatusDraft = "DRAFT";
    private const string StatusPublished = "PUBLISHED";
    private const string StatusRetired = "RETIRED";

    private static readonly string[] AggregationTypes =
        { "DIRECT_VALUE", "RATIO_OF_SUMS", "AVERAGE_OF_SUMS", "SUMMARY_ONLY" };

    private readonly ISqlSugarClient _db;
    private readonly KpiDimensionSqlExecutor _executor;
    private readonly AdoSmartOpsKpiCalcConfigService _summaryConfig;

    public AdoSmartOpsKpiDimensionConfigService(
        ISqlSugarClient db, KpiDimensionSqlExecutor executor, AdoSmartOpsKpiCalcConfigService summaryConfig)
    {
        _db = db;
        _executor = executor;
        _summaryConfig = summaryConfig;
    }

    private ISugarQueryable<AdoSmartOpsKpiDimensionConfig> Query() =>
        _db.Queryable<AdoSmartOpsKpiDimensionConfig>().ClearFilter<ITenantIdFilter>();

    /// <summary>
    /// 写路径一律走原生参数化 SQL（表名与实体 SugarTable 一致，列名 PascalCase：EnableUnderLine=false）。
    /// 原因：全局 MoreSettings.IsAutoUpdateQueryFilter / IsAutoDeleteQueryFilter=true 会给 ORM 的 UPDATE/DELETE
    /// 自动追加「登录 JWT 租户」条件，而本表按 ResolveKpiTenantId 落在 KPI 数据租户，
    /// 两者不一致时静默 0 行（同 UAT-S0-07 / UAT-S9-01 缺陷）；SqlSugar 5.1.4 的 IUpdateable/IDeleteable
    /// 又无法关闭该过滤器（EnableQueryFilter 只能开不能关，且 ClearFilter 仅存在于 ISugarQueryable）。
    /// 每条写语句都必须显式带 TenantId + 业务状态条件，**禁止按 Id 裸写**。
    /// </summary>
    private const string TableName = "ado_smart_ops_kpi_dimension_config";

    /// <summary>取配置归属租户；缺失即数据异常，直接拒绝，避免降级成按 Id 跨租户裸写。</summary>
    private static long RequireTenantId(AdoSmartOpsKpiDimensionConfig e) =>
        e.TenantId ?? throw Oops.Bah($"维度配置版本 Id={e.Id} 缺少 TenantId，数据异常，拒绝写入");

    /// <summary>
    /// 0 行写入归因：区分「目标不存在 / 租户不匹配 / 状态不满足 / 并发状态变化」，绝不静默返回成功。
    /// </summary>
    private async Task<Exception> ExplainZeroRowAsync(
        long id, long? expectedTenantId, string action, string statusRequirement, params string[] acceptableStatuses)
    {
        var now = await Query().Where(x => x.Id == id).FirstAsync();
        if (now == null)
            return Oops.Bah($"{action}失败：维度配置版本 Id={id} 不存在（可能已被并发删除）");
        if (now.TenantId != expectedTenantId)
            return Oops.Bah($"{action}失败：维度配置版本 Id={id} 归属租户 {now.TenantId}，与本次作用域租户 {expectedTenantId} 不一致");
        if (acceptableStatuses.Length > 0 && !acceptableStatuses.Contains(now.PublishStatus))
            return Oops.Bah($"{action}失败：维度配置版本 Id={id} 当前状态为 {now.PublishStatus}，不满足要求的 {statusRequirement}（并发修改，请刷新后重试）");
        return Oops.Bah($"{action}失败：维度配置版本 Id={id}（租户 {now.TenantId} / 状态 {now.PublishStatus}）未命中任何行，疑似并发状态变化，请刷新后重试");
    }

    /// <summary>某 KPI 的全部维度配置版本（按版本号倒序）。</summary>
    public async Task<List<KpiDimensionConfigDto>> GetByMetricAsync(string metricCode, string moduleCode)
    {
        var tenantId = AdoSmartOpsKpiCalcConfigService.ResolveKpiTenantId(moduleCode);
        var list = await Query()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId)
            .OrderBy(x => x.DimensionConfigVersion, OrderByType.Desc)
            .ToListAsync();
        return list.Select(ToDto).ToList();
    }

    /// <summary>当前生效维度配置（PUBLISHED + IsCurrent），无则 null。</summary>
    public async Task<AdoSmartOpsKpiDimensionConfig?> GetActiveAsync(long tenantId, string metricCode)
    {
        return await Query()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId
                        && x.IsCurrent && x.PublishStatus == StatusPublished)
            .FirstAsync();
    }

    /// <summary>新增草稿：绑定当前生效汇总配置版本；DimensionConfigVersion = 该汇总版本下现有最大 + 1。</summary>
    public async Task<KpiDimensionConfigDto> CreateDraftAsync(KpiDimensionConfigUpsertDto dto, string operatorName)
    {
        ValidateAggregation(dto.AggregationType);
        var tenantId = AdoSmartOpsKpiCalcConfigService.ResolveKpiTenantId(dto.ModuleCode);

        var summary = await _summaryConfig.GetActiveAsync(tenantId, dto.MetricCode)
            ?? throw Oops.Bah("该 KPI 当前无生效的汇总配置(CONFIG_SQL)，请先发布并激活汇总配置后再配置维度");

        var maxVer = await Query()
            .Where(x => x.MetricCode == dto.MetricCode && x.TenantId == tenantId && x.SummaryConfigId == summary.Id)
            .MaxAsync(x => (int?)x.DimensionConfigVersion) ?? 0;

        var entity = new AdoSmartOpsKpiDimensionConfig
        {
            TenantId = tenantId,
            MetricCode = dto.MetricCode,
            ModuleCode = dto.ModuleCode,
            SummaryConfigId = summary.Id,
            SummaryConfigVersion = summary.VersionNo,
            DimensionConfigVersion = maxVer + 1,
            DataSourceCode = dto.DataSourceCode,
            SqlScript = dto.SqlScript,
            AggregationType = dto.AggregationType,
            SupportedDimensionsJson = dto.SupportedDimensionsJson,
            OutputContractJson = dto.OutputContractJson,
            ParameterContractJson = dto.ParameterContractJson,
            TimeoutSeconds = NormalizeTimeout(dto.TimeoutSeconds),
            PublishStatus = StatusDraft,
            IsCurrent = false,
            BusinessSqlSource = dto.BusinessSqlSource,
            ChangeRemark = dto.ChangeRemark,
            Remark = dto.Remark,
            CreatedBy = operatorName,
            CreatedAt = DateTime.Now,
        };
        entity.Id = await _db.Insertable(entity).ExecuteReturnBigIdentityAsync();
        return ToDto(entity);
    }

    /// <summary>编辑草稿（仅 DRAFT）。</summary>
    public async Task UpdateDraftAsync(long id, KpiDimensionConfigUpsertDto dto, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("维度配置版本不存在");
        if (entity.PublishStatus != StatusDraft)
            throw Oops.Bah("仅草稿(DRAFT)可编辑；已发布/停用版本请新建版本");
        ValidateAggregation(dto.AggregationType);

        var tenantId = RequireTenantId(entity);
        var affected = await _db.Ado.ExecuteCommandAsync(
            $@"UPDATE {TableName}
               SET DataSourceCode=@ds, SqlScript=@sql, AggregationType=@agg,
                   SupportedDimensionsJson=@dims, OutputContractJson=@output, ParameterContractJson=@paramContract,
                   TimeoutSeconds=@timeout, BusinessSqlSource=@bizSrc, ChangeRemark=@chg, Remark=@remark,
                   UpdatedBy=@op, UpdatedAt=@now
               WHERE Id=@id AND TenantId=@tenant AND PublishStatus=@draft",
            new SugarParameter("@ds", dto.DataSourceCode),
            new SugarParameter("@sql", dto.SqlScript),
            new SugarParameter("@agg", dto.AggregationType),
            new SugarParameter("@dims", dto.SupportedDimensionsJson),
            new SugarParameter("@output", dto.OutputContractJson),
            new SugarParameter("@paramContract", dto.ParameterContractJson),
            new SugarParameter("@timeout", NormalizeTimeout(dto.TimeoutSeconds)),
            new SugarParameter("@bizSrc", dto.BusinessSqlSource),
            new SugarParameter("@chg", dto.ChangeRemark),
            new SugarParameter("@remark", dto.Remark),
            new SugarParameter("@op", operatorName),
            new SugarParameter("@now", DateTime.Now),
            new SugarParameter("@id", id),
            new SugarParameter("@tenant", tenantId),
            new SugarParameter("@draft", StatusDraft));
        if (affected <= 0) throw await ExplainZeroRowAsync(id, tenantId, "编辑草稿", "DRAFT", StatusDraft);
    }

    /// <summary>删除草稿（仅 DRAFT）。</summary>
    public async Task DeleteAsync(long id)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("维度配置版本不存在");
        if (entity.PublishStatus != StatusDraft)
            throw Oops.Bah("仅草稿(DRAFT)可删除；已发布版本请用停用");

        var tenantId = RequireTenantId(entity);
        var affected = await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM {TableName} WHERE Id=@id AND TenantId=@tenant AND PublishStatus=@draft",
            new SugarParameter("@id", id),
            new SugarParameter("@tenant", tenantId),
            new SugarParameter("@draft", StatusDraft));
        if (affected <= 0) throw await ExplainZeroRowAsync(id, tenantId, "删除草稿", "DRAFT", StatusDraft);
    }

    /// <summary>SQL 安全校验（复用汇总配置的多层非正则校验器）。</summary>
    public KpiSqlValidateResultDto Validate(string? sql)
    {
        var r = KpiSqlSecurityValidator.Validate(sql);
        return new KpiSqlValidateResultDto
        {
            Ok = r.Ok,
            ValidationStatus = r.Ok ? "VALID" : "INVALID",
            ErrorCode = r.ErrorCode,
            ErrorMessage = r.ErrorMessage,
            ReferencedTables = r.ReferencedTables,
        };
    }

    /// <summary>试算（服务端重校验 + 只读多行执行；不写维度结果）。返回前 20 行样本 + 输出列 + 可识别维度。</summary>
    public async Task<KpiDimensionPreviewResultDto> PreviewAsync(KpiDimensionPreviewDto dto, CancellationToken ct)
    {
        var bizDate = (dto.BizDate ?? DateTime.Today.AddDays(-1)).Date;
        var res = new KpiDimensionPreviewResultDto
        {
            MetricCode = dto.MetricCode,
            DataSourceCode = dto.DataSourceCode,
            BizDate = bizDate.ToString("yyyy-MM-dd"),
        };

        var val = KpiSqlSecurityValidator.Validate(dto.SqlScript);
        res.ValidationStatus = val.Ok ? "VALID" : "INVALID";
        if (!val.Ok)
        {
            res.Ok = false;
            res.ResultStatus = "FAILED";
            res.ErrorCode = val.ErrorCode;
            res.ErrorMessage = val.ErrorMessage;
            return res;
        }

        var tenantId = AdoSmartOpsKpiCalcConfigService.ResolveKpiTenantId(dto.ModuleCode);
        var pars = BuildRunParams(tenantId, dto.ModuleCode, dto.MetricCode, bizDate);
        var exec = await _executor.ExecuteAsync(dto.DataSourceCode, dto.SqlScript ?? "", dto.TimeoutSeconds, true, pars, ct);

        res.Ok = exec.Status != "FAILED";
        res.ResultStatus = exec.Status;
        res.DurationMs = exec.DurationMs;
        res.RowCount = exec.RowCount;
        res.OutputColumns = exec.OutputColumns;
        res.SupportedDimensions = DetectSupportedDimensions(exec.OutputColumns);
        res.ErrorCode = exec.ErrorCode;
        res.ErrorMessage = exec.ErrorMessage;
        res.SampleRows = exec.Rows.Take(20).Select(ToSampleRow).ToList();
        res.ExecutedParameters =
            $"tenant_id={pars.TenantId}, factory_id={pars.FactoryId}, module_code={pars.ModuleCode}, " +
            $"metric_code={pars.MetricCode}, biz_date={pars.BizDate:yyyy-MM-dd}, ztid={pars.SourceZtid}";
        return res;
    }

    /// <summary>发布：绑定汇总版本一致性校验 + 安全校验 + 事务单生效版本。</summary>
    public async Task PublishAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("维度配置版本不存在");
        if (entity.PublishStatus == StatusRetired)
            throw Oops.Bah("已停用版本不可发布，请新建版本");

        ValidateAggregation(entity.AggregationType);
        if (entity.AggregationType != "SUMMARY_ONLY")
        {
            var val = KpiSqlSecurityValidator.Validate(entity.SqlScript);
            if (!val.Ok) throw Oops.Bah($"SQL 安全校验未通过：{val.ErrorCode} {val.ErrorMessage}");
            if (string.IsNullOrWhiteSpace(entity.DataSourceCode))
                throw Oops.Bah("非 SUMMARY_ONLY 的维度配置必须指定数据源");
            if (string.IsNullOrWhiteSpace(entity.SupportedDimensionsJson))
                throw Oops.Bah("必须声明支持维度(SupportedDimensions)");
        }

        // 绑定的汇总配置必须仍存在且为当前生效版本
        var summary = await _summaryConfig.GetActiveAsync(entity.TenantId ?? 0, entity.MetricCode)
            ?? throw Oops.Bah("绑定的汇总配置已不存在或未生效，请基于当前汇总版本新建维度配置");
        if (summary.Id != entity.SummaryConfigId || summary.VersionNo != entity.SummaryConfigVersion)
            throw Oops.Bah($"汇总配置已切换到新版本(v{summary.VersionNo})，本维度配置绑定的是 v{entity.SummaryConfigVersion}，请基于当前汇总版本新建维度配置");

        var tenantId = RequireTenantId(entity);
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            // 旧 current 置 0：0 行是合法结果（本 KPI 此前无生效维度版本），故不做 affected 断言
            await _db.Ado.ExecuteCommandAsync(
                $@"UPDATE {TableName} SET IsCurrent=0
                   WHERE MetricCode=@metric AND TenantId=@tenant AND Id<>@id AND IsCurrent=1",
                new SugarParameter("@metric", entity.MetricCode),
                new SugarParameter("@tenant", tenantId),
                new SugarParameter("@id", id));
            var affected = await _db.Ado.ExecuteCommandAsync(
                $@"UPDATE {TableName}
                   SET PublishStatus=@published, IsCurrent=1, PublishedBy=@op, PublishedAt=@now
                   WHERE Id=@id AND TenantId=@tenant AND PublishStatus<>@retired",
                new SugarParameter("@published", StatusPublished),
                new SugarParameter("@op", operatorName),
                new SugarParameter("@now", DateTime.Now),
                new SugarParameter("@id", id),
                new SugarParameter("@tenant", tenantId),
                new SugarParameter("@retired", StatusRetired));
            if (affected <= 0)
                throw await ExplainZeroRowAsync(id, tenantId, "发布", "非 RETIRED", StatusDraft, StatusPublished);
        });
        if (!tran.IsSuccess) throw tran.ErrorException;
    }

    /// <summary>激活/回滚：把某已发布维度版本设为当前生效。</summary>
    public async Task ActivateAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("维度配置版本不存在");
        if (entity.PublishStatus != StatusPublished)
            throw Oops.Bah("只能激活已发布(PUBLISHED)版本");

        var tenantId = RequireTenantId(entity);
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            // 旧 current 置 0：0 行是合法结果（本 KPI 此前无生效维度版本），故不做 affected 断言
            await _db.Ado.ExecuteCommandAsync(
                $@"UPDATE {TableName} SET IsCurrent=0
                   WHERE MetricCode=@metric AND TenantId=@tenant AND Id<>@id AND IsCurrent=1",
                new SugarParameter("@metric", entity.MetricCode),
                new SugarParameter("@tenant", tenantId),
                new SugarParameter("@id", id));
            var affected = await _db.Ado.ExecuteCommandAsync(
                $@"UPDATE {TableName} SET IsCurrent=1, UpdatedBy=@op, UpdatedAt=@now
                   WHERE Id=@id AND TenantId=@tenant AND PublishStatus=@published",
                new SugarParameter("@op", operatorName),
                new SugarParameter("@now", DateTime.Now),
                new SugarParameter("@id", id),
                new SugarParameter("@tenant", tenantId),
                new SugarParameter("@published", StatusPublished));
            if (affected <= 0)
                throw await ExplainZeroRowAsync(id, tenantId, "激活", "PUBLISHED", StatusPublished);
        });
        if (!tran.IsSuccess) throw tran.ErrorException;
    }

    /// <summary>停用（RETIRED，IsCurrent=0）。停用当前生效版本后无 current → 筛选/下钻显示未配置。</summary>
    public async Task RetireAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("维度配置版本不存在");

        var tenantId = RequireTenantId(entity);
        var affected = await _db.Ado.ExecuteCommandAsync(
            $@"UPDATE {TableName}
               SET PublishStatus=@retired, IsCurrent=0, RetiredBy=@op, RetiredAt=@now
               WHERE Id=@id AND TenantId=@tenant",
            new SugarParameter("@retired", StatusRetired),
            new SugarParameter("@op", operatorName),
            new SugarParameter("@now", DateTime.Now),
            new SugarParameter("@id", id),
            new SugarParameter("@tenant", tenantId));
        if (affected <= 0) throw await ExplainZeroRowAsync(id, tenantId, "停用", "任意状态");
    }

    /// <summary>KPI 维度能力：无生效维度配置 → PENDING_DIMENSION_SQL；SUMMARY_ONLY → SUMMARY_ONLY；否则 CONFIGURED。</summary>
    public async Task<KpiDimensionCapabilityDto> GetCapabilityAsync(string metricCode, string moduleCode)
    {
        var tenantId = AdoSmartOpsKpiCalcConfigService.ResolveKpiTenantId(moduleCode);
        var active = await GetActiveAsync(tenantId, metricCode);
        var cap = new KpiDimensionCapabilityDto { MetricCode = metricCode, ModuleCode = moduleCode };
        if (active == null)
        {
            cap.Supported = false;
            cap.CapabilityStatus = "PENDING_DIMENSION_SQL";
            return cap;
        }
        // 维度绑定的汇总版本（兼容旧字段 + 明确的 Bound* 字段）
        cap.SummaryConfigVersion = active.SummaryConfigVersion;
        cap.BoundSummaryConfigId = active.SummaryConfigId;
        cap.BoundSummaryConfigVersion = active.SummaryConfigVersion;
        cap.DimensionConfigVersion = active.DimensionConfigVersion;
        cap.AggregationType = active.AggregationType;
        cap.SupportedDimensions = ParseDimensions(active.SupportedDimensionsJson);

        // 当前生效的汇总版本（可能已推进超过维度绑定）；一致性同时比对 Id 与 Version
        var currentSummary = await _summaryConfig.GetActiveAsync(tenantId, metricCode);
        cap.CurrentSummaryConfigId = currentSummary?.Id;
        cap.CurrentSummaryConfigVersion = currentSummary?.VersionNo;
        cap.IsSummaryBindingConsistent = currentSummary != null
            && currentSummary.Id == active.SummaryConfigId
            && currentSummary.VersionNo == active.SummaryConfigVersion;

        if (active.AggregationType == "SUMMARY_ONLY")
        {
            cap.Supported = false;
            cap.CapabilityStatus = "SUMMARY_ONLY";
        }
        else if (!cap.IsSummaryBindingConsistent)
        {
            // 汇总已推进 / 当前汇总缺失：旧绑定维度不可直接投用，避免误判可用
            cap.Supported = false;
            cap.CapabilityStatus = "VERSION_MISMATCH";
        }
        else
        {
            cap.Supported = true;
            cap.CapabilityStatus = "CONFIGURED";
        }

        var lastRun = await _db.Queryable<AdoSmartOpsKpiDimensionRunLog>().ClearFilter<ITenantIdFilter>()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId)
            .OrderBy(x => x.StartedAt, OrderByType.Desc)
            .FirstAsync();
        if (lastRun != null)
        {
            cap.LastRunStatus = lastRun.Status;
            cap.LastRunAt = lastRun.StartedAt;
        }
        return cap;
    }

    /// <summary>
    /// 维度明细只读查询（当前生效维度版本）：按 AggregationType 复算聚合值 + 分页明细。
    /// 只读当前激活配置产生的维度结果，绝不读旧 Atomic。无激活配置 → NOT_CONFIGURED。
    /// </summary>
    public async Task<KpiDimensionDetailResultDto> QueryDetailAsync(
        string metricCode, string moduleCode, DateTime? startDate, DateTime? endDate,
        string? dimensionType, string? dimensionCode, int page, int pageSize,
        string? orderNo = null, string? sortField = null, string? sortOrder = null)
    {
        var tenantId = AdoSmartOpsKpiCalcConfigService.ResolveKpiTenantId(moduleCode);
        var active = await GetActiveAsync(tenantId, metricCode);
        var result = new KpiDimensionDetailResultDto { MetricCode = metricCode };
        if (active == null)
        {
            result.Status = "NOT_CONFIGURED";
            return result;
        }
        result.SummaryConfigVersion = active.SummaryConfigVersion;
        result.DimensionConfigVersion = active.DimensionConfigVersion;
        result.AggregationType = active.AggregationType;

        var sd = startDate?.Date;
        var ed = endDate?.Date;
        var ordNo = string.IsNullOrWhiteSpace(orderNo) ? null : orderNo!.Trim();
        ISugarQueryable<AdoSmartOpsKpiDimensionValueDay> Build() =>
            _db.Queryable<AdoSmartOpsKpiDimensionValueDay>().ClearFilter<ITenantIdFilter>()
                .Where(x => x.TenantId == tenantId && x.MetricCode == metricCode
                            && x.DimensionConfigVersion == active.DimensionConfigVersion)
                .WhereIF(sd.HasValue, x => x.ValueDate >= sd!.Value)
                .WhereIF(ed.HasValue, x => x.ValueDate <= ed!.Value)
                .WhereIF(!string.IsNullOrWhiteSpace(dimensionType), x => x.DimensionType == dimensionType)
                .WhereIF(!string.IsNullOrWhiteSpace(dimensionCode), x => x.DimensionCode == dimensionCode)
                .WhereIF(ordNo != null, x => x.OrderNo == ordNo || x.DimensionCode == ordNo);

        result.AggregateValue = active.AggregationType switch
        {
            "AVERAGE_OF_SUMS" => Div(await Build().SumAsync(x => x.SumValue), (decimal?)await Build().SumAsync(x => x.SampleCount)),
            "RATIO_OF_SUMS" => Div(await Build().SumAsync(x => x.Numerator), await Build().SumAsync(x => x.Denominator)),
            "DIRECT_VALUE" => await Build().AvgAsync(x => x.MetricValue),
            _ => null,
        };

        // 排序字段白名单，禁止任意列注入
        var ot = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? OrderByType.Asc : OrderByType.Desc;
        var sorted = (sortField ?? "").Trim().ToLowerInvariant() switch
        {
            "metric_value" or "metricvalue" => Build().OrderBy(x => x.MetricValue, ot).OrderBy(x => x.DimensionCode),
            _ => Build().OrderBy(x => x.ValueDate, ot).OrderBy(x => x.DimensionCode),
        };
        RefAsync<int> total = 0;
        var list = await sorted
            .ToPageListAsync(page <= 0 ? 1 : page, pageSize <= 0 ? 20 : pageSize, total);
        result.Total = total.Value;
        result.List = list.Select(x => new KpiDimensionDetailRowDto
        {
            ValueDate = x.ValueDate.ToString("yyyy-MM-dd"),
            DimensionType = x.DimensionType,
            DimensionCode = x.DimensionCode,
            DimensionName = x.DimensionName,
            MetricValue = x.MetricValue,
            Numerator = x.Numerator,
            Denominator = x.Denominator,
            SumValue = x.SumValue,
            SampleCount = x.SampleCount,
            SourceKey = x.SourceKey,
            BatchId = x.BatchId,
        }).ToList();
        return result;
    }

    private static decimal? Div(decimal? a, decimal? b) =>
        b.HasValue && b.Value != 0 ? (a ?? 0m) / b.Value : null;

    /// <summary>已登记数据源列表（第一版仅本地中台库）。</summary>
    public List<object> ListDataSources() => new()
    {
        new { code = KpiDimensionSqlExecutor.LocalDataSourceCode, name = "本地中台库（aidopdev / mdp_std_* / dwd_*）", readOnly = true },
    };

    internal static KpiSqlRunParams BuildRunParams(long tenantId, string moduleCode, string metricCode, DateTime bizDate) => new()
    {
        TenantId = tenantId,
        FactoryId = 1,
        ModuleCode = moduleCode,
        MetricCode = metricCode,
        BizDate = bizDate,
        PeriodStart = bizDate,
        PeriodEnd = bizDate.AddDays(1).AddSeconds(-1),
        SourceZtid = "pbxfxp",
    };

    private static readonly string[] KnownDimensionColumns =
    {
        "value_date", "org_id", "factory_id", "material_code", "work_order_no", "category_code",
        "warehouse_code", "order_no", "customer_code", "product_code", "equipment_code",
    };

    private static List<string> DetectSupportedDimensions(List<string> outputColumns)
    {
        var set = new HashSet<string>(outputColumns, StringComparer.OrdinalIgnoreCase);
        return KnownDimensionColumns.Where(set.Contains).ToList();
    }

    private static List<string> ParseDimensions(string? json)
    {
        if (string.IsNullOrWhiteSpace(json)) return new();
        try { return System.Text.Json.JsonSerializer.Deserialize<List<string>>(json) ?? new(); }
        catch { return new(); }
    }

    private static Dictionary<string, object?> ToSampleRow(KpiDimensionRow r) => new()
    {
        ["value_date"] = r.ValueDate == DateTime.MinValue ? null : r.ValueDate.ToString("yyyy-MM-dd"),
        ["dimension_type"] = r.DimensionType,
        ["dimension_code"] = r.DimensionCode,
        ["dimension_name"] = r.DimensionName,
        ["metric_value"] = r.MetricValue,
        ["numerator"] = r.Numerator,
        ["denominator"] = r.Denominator,
        ["sum_value"] = r.SumValue,
        ["sample_count"] = r.SampleCount,
    };

    private static void ValidateAggregation(string agg)
    {
        if (!AggregationTypes.Contains(agg))
            throw Oops.Bah($"非法聚合类型：{agg}（DIRECT_VALUE/RATIO_OF_SUMS/AVERAGE_OF_SUMS/SUMMARY_ONLY）");
    }

    private static int NormalizeTimeout(int t) =>
        t <= 0 ? 60 : Math.Min(t, KpiDimensionSqlExecutor.SystemMaxTimeoutSeconds);

    private static KpiDimensionConfigDto ToDto(AdoSmartOpsKpiDimensionConfig e) => new()
    {
        Id = e.Id, TenantId = e.TenantId, MetricCode = e.MetricCode, ModuleCode = e.ModuleCode,
        SummaryConfigId = e.SummaryConfigId, SummaryConfigVersion = e.SummaryConfigVersion,
        DimensionConfigVersion = e.DimensionConfigVersion, DataSourceCode = e.DataSourceCode,
        SqlScript = e.SqlScript, AggregationType = e.AggregationType,
        SupportedDimensionsJson = e.SupportedDimensionsJson, OutputContractJson = e.OutputContractJson,
        ParameterContractJson = e.ParameterContractJson, TimeoutSeconds = e.TimeoutSeconds,
        PublishStatus = e.PublishStatus, IsCurrent = e.IsCurrent, BusinessSqlSource = e.BusinessSqlSource,
        ChangeRemark = e.ChangeRemark, Remark = e.Remark, CreatedBy = e.CreatedBy, CreatedAt = e.CreatedAt,
        PublishedBy = e.PublishedBy, PublishedAt = e.PublishedAt,
    };
}