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

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

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// KPI 计算配置服务：CRUD + 校验 + 试算 + 发布/停用/激活（发布状态机，单生效版本事务保证）。
/// 租户显式控制（ClearFilter）：配置落在"分发器运行时查找的租户"= KPI 数据租户（S5=AidopSourceTenantMap 解析），
/// 而非 API 调用者 JWT 租户；否则运行时找不到配置。SqlScript 属高危配置，接口须登录鉴权（Controller 层）。
/// </summary>
public sealed class AdoSmartOpsKpiCalcConfigService : ITransient
{
    private const string EngineLegacyCode = "LEGACY_CODE";
    private const string EngineConfigSql = "CONFIG_SQL";
    private const string EngineLegacyTvf = "LEGACY_TVF";
    private const string StatusDraft = "DRAFT";
    private const string StatusPublished = "PUBLISHED";
    private const string StatusRetired = "RETIRED";
    private static readonly string[] TenantScopedModules = { "S5", "S6", "S7" };

    private readonly ISqlSugarClient _db;
    private readonly KpiSqlReadOnlyExecutor _executor;
    private readonly AdoSmartOpsKpiBusinessInputService _businessInput;

    public AdoSmartOpsKpiCalcConfigService(ISqlSugarClient db, KpiSqlReadOnlyExecutor executor, AdoSmartOpsKpiBusinessInputService businessInput)
    {
        _db = db;
        _executor = executor;
        _businessInput = businessInput;
    }

    /// <summary>
    /// CONFIG_SQL 业务门禁：仅 BusinessInputStatus=READY_FOR_CONFIG 放行 试算/发布/激活。
    /// 未就绪则明确拒绝——不执行 SQL、不写运行日志、不改版本状态/IsCurrent。
    /// </summary>
    private async Task EnsureBusinessReadyAsync(long tenantId, string metricCode, string action)
    {
        var status = await _businessInput.GetStatusAsync(tenantId, metricCode);
        if (status != AdoSmartOpsKpiBusinessInputService.StatusReady)
            throw Oops.Bah($"业务来源未就绪（当前 {status}）：{action} 需先在「业务来源登记」补齐来源系统/表/字段/SQL来源并置为 READY_FOR_CONFIG");
    }

    /// <summary>解析配置应归属的租户（= 运行时 KPI 落库租户）。S5/S6/S7 走 T8 账套映射（pbxfxp→AIDOP）。</summary>
    public static long ResolveKpiTenantId(string moduleCode)
    {
        if (TenantScopedModules.Contains((moduleCode ?? "").Trim().ToUpperInvariant()))
            return AidopSourceTenantMap.ResolveTenantId("pbxfxp");
        return SqlSugarConst.DefaultTenantId;
    }

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

    /// <summary>
    /// 写路径一律走原生参数化 SQL（表名与实体 SugarTable 一致，列名 PascalCase：EnableUnderLine=false）。
    /// 原因：全局 MoreSettings.IsAutoUpdateQueryFilter / IsAutoDeleteQueryFilter=true 会给 ORM 的 UPDATE/DELETE
    /// 自动追加「登录 JWT 租户」条件，而本表按 <see cref="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_calc_config";

    /// <summary>取配置归属租户；缺失即数据异常，直接拒绝，避免降级成按 Id 跨租户裸写。</summary>
    private static long RequireTenantId(AdoSmartOpsKpiCalcConfig 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<KpiCalcConfigDto>> GetByMetricAsync(string metricCode, string moduleCode)
    {
        var tenantId = ResolveKpiTenantId(moduleCode);
        var list = await Query()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId)
            .OrderBy(x => x.VersionNo, OrderByType.Desc)
            .ToListAsync();
        return list.Select(ToDto).ToList();
    }

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

    /// <summary>新增草稿（VersionNo = 现有最大 + 1）。</summary>
    public async Task<KpiCalcConfigDto> CreateDraftAsync(KpiCalcConfigUpsertDto dto, string operatorName)
    {
        ValidateEngineType(dto.CalcEngineType);
        var tenantId = ResolveKpiTenantId(dto.ModuleCode);
        var maxVer = await Query()
            .Where(x => x.MetricCode == dto.MetricCode && x.TenantId == tenantId)
            .MaxAsync(x => (int?)x.VersionNo) ?? 0;

        var entity = new AdoSmartOpsKpiCalcConfig
        {
            TenantId = tenantId,
            MetricCode = dto.MetricCode,
            ModuleCode = dto.ModuleCode,
            VersionNo = maxVer + 1,
            CalcEngineType = dto.CalcEngineType,
            DataSourceCode = dto.DataSourceCode,
            SqlScript = dto.SqlScript,
            SqlParametersJson = dto.SqlParametersJson,
            TimeoutSeconds = NormalizeTimeout(dto.TimeoutSeconds),
            PublishStatus = StatusDraft,
            IsCurrent = false,
            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, KpiCalcConfigUpsertDto dto, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        if (entity.PublishStatus != StatusDraft)
            throw Oops.Bah("仅草稿(DRAFT)可编辑；已发布/停用版本请新建版本");
        ValidateEngineType(dto.CalcEngineType);

        var tenantId = RequireTenantId(entity);
        var affected = await _db.Ado.ExecuteCommandAsync(
            $@"UPDATE {TableName}
               SET CalcEngineType=@engine, DataSourceCode=@ds, SqlScript=@sql, SqlParametersJson=@pars,
                   TimeoutSeconds=@timeout, Remark=@remark, UpdatedBy=@op, UpdatedAt=@now
               WHERE Id=@id AND TenantId=@tenant AND PublishStatus=@draft",
            new SugarParameter("@engine", dto.CalcEngineType),
            new SugarParameter("@ds", dto.DataSourceCode),
            new SugarParameter("@sql", dto.SqlScript),
            new SugarParameter("@pars", dto.SqlParametersJson),
            new SugarParameter("@timeout", NormalizeTimeout(dto.TimeoutSeconds)),
            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>试算（服务端重校验 + 只读执行；不写正式 KPI 值）。</summary>
    public async Task<KpiSqlPreviewResultDto> PreviewAsync(KpiSqlPreviewDto dto, CancellationToken ct)
    {
        var bizDate = (dto.BizDate ?? DateTime.Today.AddDays(-1)).Date;
        var res = new KpiSqlPreviewResultDto
        {
            MetricCode = dto.MetricCode,
            EngineType = EngineConfigSql,
            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 = ResolveKpiTenantId(dto.ModuleCode);
        // 业务门禁：试算是真实数据只读执行，未 READY 直接拒绝（不落只读事务、不占连接）。
        await EnsureBusinessReadyAsync(tenantId, dto.MetricCode, "试算");
        var pars = new KpiSqlRunParams
        {
            TenantId = tenantId,
            FactoryId = 1,
            ModuleCode = dto.ModuleCode,
            MetricCode = dto.MetricCode,
            BizDate = bizDate,
            PeriodStart = bizDate,
            PeriodEnd = bizDate.AddDays(1).AddSeconds(-1),
            SourceZtid = "pbxfxp",
        };
        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.MetricValue = exec.MetricValue;
        res.NumeratorValue = exec.NumeratorValue;
        res.DenominatorValue = exec.DenominatorValue;
        res.ResultMessage = exec.ResultMessage;
        res.ErrorCode = exec.ErrorCode;
        res.ErrorMessage = exec.ErrorMessage;
        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>发布：事务内校验→旧 current 置 0→本版 PUBLISHED+IsCurrent=1（保证单生效版本）。</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("已停用版本不可发布，请新建版本");

        // CONFIG_SQL 发布前必须再过一次安全校验
        if (entity.CalcEngineType == EngineConfigSql)
        {
            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("CONFIG_SQL 必须指定数据源");
            await EnsureBusinessReadyAsync(entity.TenantId ?? 0, entity.MetricCode, "发布");
        }

        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>激活/回滚：把某历史版本设为当前生效（其它同 KPI 版本 IsCurrent 置 0），事务内。仅对已发布版本。</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)版本；回滚是激活历史已发布版本，不是复制重发");
        if (entity.CalcEngineType == EngineConfigSql)
            await EnsureBusinessReadyAsync(entity.TenantId ?? 0, entity.MetricCode, "激活");

        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 → 运行时回落 legacy。</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>已登记数据源列表（第一版仅本地中台库）。</summary>
    public List<object> ListDataSources() => new()
    {
        new { code = KpiSqlReadOnlyExecutor.LocalDataSourceCode, name = "本地中台库（aidopdev / mdp_std_* / dwd_*）", readOnly = true },
    };

    private static void ValidateEngineType(string engine)
    {
        if (engine != EngineLegacyCode && engine != EngineConfigSql && engine != EngineLegacyTvf)
            throw Oops.Bah($"非法引擎类型：{engine}（LEGACY_CODE/CONFIG_SQL/LEGACY_TVF）");
    }

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

    private static KpiCalcConfigDto ToDto(AdoSmartOpsKpiCalcConfig e) => new()
    {
        Id = e.Id, TenantId = e.TenantId, MetricCode = e.MetricCode, ModuleCode = e.ModuleCode,
        VersionNo = e.VersionNo, CalcEngineType = e.CalcEngineType, DataSourceCode = e.DataSourceCode,
        SqlScript = e.SqlScript, SqlParametersJson = e.SqlParametersJson, TimeoutSeconds = e.TimeoutSeconds,
        PublishStatus = e.PublishStatus, IsCurrent = e.IsCurrent, Remark = e.Remark,
        CreatedBy = e.CreatedBy, CreatedAt = e.CreatedAt, PublishedBy = e.PublishedBy, PublishedAt = e.PublishedAt,
    };
}