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>某 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);
        entity.DataSourceCode = dto.DataSourceCode;
        entity.SqlScript = dto.SqlScript;
        entity.AggregationType = dto.AggregationType;
        entity.SupportedDimensionsJson = dto.SupportedDimensionsJson;
        entity.OutputContractJson = dto.OutputContractJson;
        entity.ParameterContractJson = dto.ParameterContractJson;
        entity.TimeoutSeconds = NormalizeTimeout(dto.TimeoutSeconds);
        entity.BusinessSqlSource = dto.BusinessSqlSource;
        entity.ChangeRemark = dto.ChangeRemark;
        entity.Remark = dto.Remark;
        entity.UpdatedBy = operatorName;
        entity.UpdatedAt = DateTime.Now;
        await _db.Updateable(entity).ExecuteCommandAsync();
    }

    /// <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)可删除；已发布版本请用停用");
        await _db.Deleteable<AdoSmartOpsKpiDimensionConfig>().Where(x => x.Id == id).ExecuteCommandAsync();
    }

    /// <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 = entity.TenantId;
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            await _db.Updateable<AdoSmartOpsKpiDimensionConfig>()
                .SetColumns(x => new AdoSmartOpsKpiDimensionConfig { IsCurrent = false })
                .Where(x => x.MetricCode == entity.MetricCode && x.TenantId == tenantId && x.Id != id && x.IsCurrent)
                .ExecuteCommandAsync();
            await _db.Updateable<AdoSmartOpsKpiDimensionConfig>()
                .SetColumns(x => new AdoSmartOpsKpiDimensionConfig
                {
                    PublishStatus = StatusPublished, IsCurrent = true,
                    PublishedBy = operatorName, PublishedAt = DateTime.Now,
                })
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();
        });
        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 = entity.TenantId;
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            await _db.Updateable<AdoSmartOpsKpiDimensionConfig>()
                .SetColumns(x => new AdoSmartOpsKpiDimensionConfig { IsCurrent = false })
                .Where(x => x.MetricCode == entity.MetricCode && x.TenantId == tenantId && x.Id != id && x.IsCurrent)
                .ExecuteCommandAsync();
            await _db.Updateable<AdoSmartOpsKpiDimensionConfig>()
                .SetColumns(x => new AdoSmartOpsKpiDimensionConfig { IsCurrent = true, UpdatedBy = operatorName, UpdatedAt = DateTime.Now })
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();
        });
        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("维度配置版本不存在");
        await _db.Updateable<AdoSmartOpsKpiDimensionConfig>()
            .SetColumns(x => new AdoSmartOpsKpiDimensionConfig
            {
                PublishStatus = StatusRetired, IsCurrent = false,
                RetiredBy = operatorName, RetiredAt = DateTime.Now,
            })
            .Where(x => x.Id == id)
            .ExecuteCommandAsync();
    }

    /// <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,
    };
}