AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/S8ConfigDraftService.cs

using System.Globalization;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Text.RegularExpressions;
using Admin.NET.Plugin.AiDOP.Dto.S8;
using Admin.NET.Plugin.AiDOP.Entity.S8;

namespace Admin.NET.Plugin.AiDOP.Service.S8;

internal static class S8ConfigDraftStatus
{
    public const string InProgress = "IN_PROGRESS";
    public const string Generated = "GENERATED";
}

public class S8ConfigDraftService : ITransient
{
    private readonly SqlSugarRepository<AdoS8ConfigDraft> _rep;
    private readonly S8WatchRuleService _watchRuleService;
    // CONFIG-WIZARD-GENERATE-RULE-SERVER-BUILD-1：rulePayload 缺省时按 wizard_json 重建用到的字典 Repository。
    private readonly SqlSugarRepository<AdoS8MonitorObject> _monitorObjectRep;
    private readonly SqlSugarRepository<AdoS8MonitorMetric> _monitorMetricRep;
    private readonly SqlSugarRepository<AdoS8ExceptionType> _exceptionTypeRep;
    private readonly SqlSugarRepository<AdoS8DataSource> _dataSourceRep;
    private readonly SqlSugarRepository<AdoS8SceneConfig> _sceneRep;

    public S8ConfigDraftService(
        SqlSugarRepository<AdoS8ConfigDraft> rep,
        S8WatchRuleService watchRuleService,
        SqlSugarRepository<AdoS8MonitorObject> monitorObjectRep,
        SqlSugarRepository<AdoS8MonitorMetric> monitorMetricRep,
        SqlSugarRepository<AdoS8ExceptionType> exceptionTypeRep,
        SqlSugarRepository<AdoS8DataSource> dataSourceRep,
        SqlSugarRepository<AdoS8SceneConfig> sceneRep)
    {
        _rep = rep;
        _watchRuleService = watchRuleService;
        _monitorObjectRep = monitorObjectRep;
        _monitorMetricRep = monitorMetricRep;
        _exceptionTypeRep = exceptionTypeRep;
        _dataSourceRep = dataSourceRep;
        _sceneRep = sceneRep;
    }

    // CONFIG-WIZARD-GENERATE-RULE-SERVER-BUILD-1：解析 wizard_json 用 JsonSerializerOptions（前端 camelCase）。
    private static readonly JsonSerializerOptions WizardJsonReadOptions = new()
    {
        PropertyNameCaseInsensitive = true,
        ReadCommentHandling = JsonCommentHandling.Skip,
        AllowTrailingCommas = true,
    };

    // 后端生成 params_json 时 camelCase + 忽略 null。
    private static readonly JsonSerializerOptions ParamsJsonWriteOptions = new()
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
        DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull,
    };

    public async Task<AdoS8ConfigDraftListResultDto> ListAsync(AdoS8ConfigDraftListQueryDto query)
    {
        var page = query.Page < 1 ? 1 : query.Page;
        var pageSize = query.PageSize < 1 ? 20 : Math.Min(query.PageSize, 200);
        var status = string.IsNullOrWhiteSpace(query.Status) ? null : query.Status.Trim();

        var q = _rep.AsQueryable()
            .Where(x => x.TenantId == query.TenantId && x.FactoryId == query.FactoryId);
        if (status != null)
            q = q.Where(x => x.Status == status);

        RefAsync<int> total = 0;
        var rows = await q
            .OrderBy(x => x.Id, OrderByType.Desc)
            .Select(x => new AdoS8ConfigDraftListItemDto
            {
                Id = x.Id,
                TenantId = x.TenantId,
                FactoryId = x.FactoryId,
                DraftCode = x.DraftCode,
                DraftName = x.DraftName,
                Status = x.Status,
                CurrentStep = x.CurrentStep,
                Mechanism = x.Mechanism,
                StageCode = x.StageCode,
                OrderFlowCode = x.OrderFlowCode,
                ExceptionTypeCode = x.ExceptionTypeCode,
                GeneratedRuleId = x.GeneratedRuleId,
                Remark = x.Remark,
                CreatedAt = x.CreatedAt,
                UpdatedAt = x.UpdatedAt
            })
            .ToPageListAsync(page, pageSize, total);

        return new AdoS8ConfigDraftListResultDto
        {
            Items = rows,
            Total = total.Value,
            Page = page,
            PageSize = pageSize
        };
    }

    public async Task<AdoS8ConfigDraftDetailDto> GetAsync(long id)
    {
        var e = await _rep.GetByIdAsync(id) ?? throw new S8BizException("草稿不存在");
        return ToDetail(e);
    }

    public async Task<AdoS8ConfigDraftDetailDto> CreateAsync(AdoS8ConfigDraftCreateDto dto)
    {
        if (string.IsNullOrWhiteSpace(dto.DraftCode))
            throw new S8BizException("草稿编码必填");
        if (string.IsNullOrWhiteSpace(dto.WizardJson))
            throw new S8BizException("草稿 wizard_json 必填");

        var draftCode = dto.DraftCode.Trim();
        var exists = await _rep.AsQueryable()
            .AnyAsync(x => x.TenantId == dto.TenantId && x.FactoryId == dto.FactoryId && x.DraftCode == draftCode);
        if (exists) throw new S8BizException("草稿编码已存在");

        var entity = new AdoS8ConfigDraft
        {
            TenantId = dto.TenantId,
            FactoryId = dto.FactoryId,
            DraftCode = draftCode,
            DraftName = NormalizeOrNull(dto.DraftName),
            WizardJson = dto.WizardJson,
            CurrentStep = dto.CurrentStep ?? 0,
            Mechanism = NormalizeOrNull(dto.Mechanism),
            StageCode = NormalizeOrNull(dto.StageCode),
            OrderFlowCode = NormalizeOrNull(dto.OrderFlowCode),
            ExceptionTypeCode = NormalizeOrNull(dto.ExceptionTypeCode),
            Remark = NormalizeOrNull(dto.Remark),
            Status = S8ConfigDraftStatus.InProgress,
            CreatedAt = DateTime.Now
        };
        entity.Id = await _rep.AsInsertable(entity).ExecuteReturnBigIdentityAsync();
        return ToDetail(entity);
    }

    public async Task<AdoS8ConfigDraftDetailDto> UpdateAsync(long id, AdoS8ConfigDraftUpdateDto dto)
    {
        var e = await _rep.GetByIdAsync(id) ?? throw new S8BizException("草稿不存在");
        if (e.Status == S8ConfigDraftStatus.Generated)
            throw new S8BizException("草稿已生成正式规则，禁止再编辑");

        if (dto.DraftName != null) e.DraftName = NormalizeOrNull(dto.DraftName);
        if (dto.WizardJson != null)
        {
            if (string.IsNullOrWhiteSpace(dto.WizardJson))
                throw new S8BizException("wizard_json 不能为空字符串");
            e.WizardJson = dto.WizardJson;
        }
        if (dto.CurrentStep.HasValue) e.CurrentStep = dto.CurrentStep.Value;
        if (dto.Mechanism != null) e.Mechanism = NormalizeOrNull(dto.Mechanism);
        if (dto.StageCode != null) e.StageCode = NormalizeOrNull(dto.StageCode);
        if (dto.OrderFlowCode != null) e.OrderFlowCode = NormalizeOrNull(dto.OrderFlowCode);
        if (dto.ExceptionTypeCode != null) e.ExceptionTypeCode = NormalizeOrNull(dto.ExceptionTypeCode);
        if (dto.Remark != null) e.Remark = NormalizeOrNull(dto.Remark);

        e.UpdatedAt = DateTime.Now;
        await _rep.UpdateAsync(e);
        return ToDetail(e);
    }

    public async Task DeleteAsync(long id)
    {
        var e = await _rep.GetByIdAsync(id) ?? throw new S8BizException("草稿不存在");
        await _rep.DeleteByIdAsync(e.Id);
    }

    public async Task<AdoS8ConfigDraftGenerateRuleResultDto> GenerateRuleAsync(long id, AdoS8ConfigDraftGenerateRuleDto dto)
    {
        var draft = await _rep.GetByIdAsync(id) ?? throw new S8BizException("草稿不存在");

        if (draft.GeneratedRuleId.HasValue)
        {
            return new AdoS8ConfigDraftGenerateRuleResultDto
            {
                RuleId = draft.GeneratedRuleId.Value,
                RuleCode = await ResolveExistingRuleCodeAsync(draft.GeneratedRuleId.Value),
                DraftStatus = draft.Status
            };
        }
        if (draft.Status == S8ConfigDraftStatus.Generated)
            throw new S8BizException("草稿状态异常：已标记 GENERATED 但未回写规则 ID");

        // CONFIG-WIZARD-API-RULEPAYLOAD-CLEANUP-1：始终由后端基于 wizard_json + 字典重建，dto 已不再承载 RulePayload。
        var payload = await RebuildPayloadFromWizardJsonAsync(draft);
        if (string.IsNullOrWhiteSpace(payload.RuleCode))
            throw new S8BizException("规则编码不能为空");

        // 安全收口：强制对齐草稿租户/工厂、强制 enabled=false。
        payload.Id = 0;
        payload.TenantId = draft.TenantId;
        payload.FactoryId = draft.FactoryId;
        payload.Enabled = false;

        var db = _rep.Context;
        await db.Ado.BeginTranAsync();
        try
        {
            var created = await _watchRuleService.CreateAsync(payload);

            // S8WatchRuleService.CreateAsync 内部用 InsertAsync 不回填 Id；按 RuleCode 反查真实 id。
            var newRuleId = created.Id;
            if (newRuleId <= 0)
            {
                newRuleId = await db.Queryable<AdoS8WatchRule>()
                    .Where(x => x.TenantId == draft.TenantId
                                && x.FactoryId == draft.FactoryId
                                && x.RuleCode == created.RuleCode)
                    .Select(x => x.Id)
                    .FirstAsync();
            }
            if (newRuleId <= 0)
                throw new S8BizException("生成规则失败：无法定位新规则 id");

            await db.Updateable<AdoS8ConfigDraft>()
                .SetColumns(x => new AdoS8ConfigDraft
                {
                    GeneratedRuleId = newRuleId,
                    Status = S8ConfigDraftStatus.Generated,
                    UpdatedAt = DateTime.Now
                })
                .Where(x => x.Id == draft.Id)
                .ExecuteCommandAsync();

            await db.Ado.CommitTranAsync();

            return new AdoS8ConfigDraftGenerateRuleResultDto
            {
                RuleId = newRuleId,
                RuleCode = created.RuleCode,
                DraftStatus = S8ConfigDraftStatus.Generated
            };
        }
        catch
        {
            await db.Ado.RollbackTranAsync();
            throw;
        }
    }

    private async Task<string> ResolveExistingRuleCodeAsync(long ruleId)
    {
        var rule = await _rep.Context.Queryable<AdoS8WatchRule>()
            .Where(x => x.Id == ruleId)
            .Select(x => new { x.RuleCode })
            .FirstAsync();
        return rule?.RuleCode ?? string.Empty;
    }

    private static AdoS8ConfigDraftDetailDto ToDetail(AdoS8ConfigDraft e) => new()
    {
        Id = e.Id,
        TenantId = e.TenantId,
        FactoryId = e.FactoryId,
        DraftCode = e.DraftCode,
        DraftName = e.DraftName,
        Status = e.Status,
        CurrentStep = e.CurrentStep,
        Mechanism = e.Mechanism,
        StageCode = e.StageCode,
        OrderFlowCode = e.OrderFlowCode,
        ExceptionTypeCode = e.ExceptionTypeCode,
        GeneratedRuleId = e.GeneratedRuleId,
        Remark = e.Remark,
        CreatedAt = e.CreatedAt,
        UpdatedAt = e.UpdatedAt,
        WizardJson = e.WizardJson
    };

    private static string? NormalizeOrNull(string? value)
    {
        if (string.IsNullOrWhiteSpace(value)) return null;
        var trimmed = value.Trim();
        return trimmed.Length == 0 ? null : trimmed;
    }

    // ============================================================
    // CONFIG-WIZARD-GENERATE-RULE-SERVER-BUILD-1：基于 wizard_json + 字典重建 AdoS8WatchRule
    // ============================================================

    /// <summary>
    /// 从草稿的 wizard_json 解析向导态，结合 monitor object/metric、exception_type、data_source、scene_config
    /// 重建 AdoS8WatchRule。所有错误统一抛 S8BizException → Controller 转 400。
    /// </summary>
    private async Task<AdoS8WatchRule> RebuildPayloadFromWizardJsonAsync(AdoS8ConfigDraft draft)
    {
        if (string.IsNullOrWhiteSpace(draft.WizardJson))
            throw new S8BizException("草稿配置内容为空");

        S8WizardJsonV1 parsed;
        try
        {
            parsed = JsonSerializer.Deserialize<S8WizardJsonV1>(draft.WizardJson, WizardJsonReadOptions)
                     ?? throw new S8BizException("草稿配置内容为空");
        }
        catch (JsonException)
        {
            throw new S8BizException("草稿配置内容格式错误");
        }

        if (parsed.Version != 1)
            throw new S8BizException($"暂不支持该草稿版本（version={parsed.Version}）");

        var form = parsed.Form ?? throw new S8BizException("草稿配置内容不完整");
        var labels = parsed.Labels;

        // 必填基础字段
        var ruleCode = NormalizeOrNull(form.RuleCode) ?? throw new S8BizException("规则编码不能为空");
        var mechanism = NormalizeOrNull(form.Mechanism) ?? throw new S8BizException("报警机制未选择");
        if (mechanism == "MANUAL_REPORT")
            throw new S8BizException("主动提报无需生成自动监控规则");
        var stageCode = NormalizeOrNull(form.StageCode) ?? throw new S8BizException("阶段维度未选择");
        var exceptionTypeCode = NormalizeOrNull(form.ExceptionTypeCode) ?? throw new S8BizException("异常类型未选择");
        var objectType = NormalizeOrNull(form.ObjectType) ?? throw new S8BizException("监控对象未选择");
        var objectLabel = NormalizeOrNull(form.ObjectLabel) ?? throw new S8BizException("监控对象未选择");
        var metricCode = NormalizeOrNull(form.MetricCode) ?? throw new S8BizException("监控指标未选择");

        // 监控对象（用 objectType + objectName 在字典里反查；tenant 覆盖 baseline）
        var monitorObject = await ResolveMonitorObjectAsync(draft.TenantId, draft.FactoryId, objectType, objectLabel);

        // 监控指标
        var metric = await ResolveMonitorMetricAsync(draft.TenantId, draft.FactoryId, metricCode, mechanism);

        // CONFIG-WIZARD-T3K-ORDER-FLOW-STAGE-VALIDATE-1：销售订单链路阶段额外协议校验
        if (string.Equals(monitorObject.ObjectCode, "SALES_ORDER_FLOW_STAGE", StringComparison.Ordinal))
        {
            if (string.IsNullOrWhiteSpace(form.OrderFlowCode))
                throw new S8BizException("订单链路阶段规则必须选择订单流程节点");
            var ocf = NormalizeOrNull(metric.ObjectCodeField);
            if (!string.Equals(ocf, "order_code", StringComparison.OrdinalIgnoreCase))
                throw new S8BizException("订单链路阶段规则的 objectCodeField 必须为 order_code");
        }

        // 异常类型（baseline 0/0 + tenant 覆盖；enabled 必须 true；sceneCode 必须等于 stageCode）
        var exceptionType = await ResolveExceptionTypeAsync(draft.TenantId, draft.FactoryId, exceptionTypeCode);
        if (!string.IsNullOrWhiteSpace(exceptionType.SceneCode) && exceptionType.SceneCode != stageCode)
            throw new S8BizException("异常类型所属阶段与当前规则阶段不一致");

        // severity 优先 wizard_json，再 exceptionType.SeverityDefault，再 FOLLOW
        var severity = NormalizeOrNull(form.Severity)
                       ?? NormalizeOrNull(exceptionType.SeverityDefault)
                       ?? "FOLLOW";

        // dataSource：tenant/factory 下第一个 enabled
        var dataSource = await _dataSourceRep.AsQueryable()
            .Where(x => x.TenantId == draft.TenantId && x.FactoryId == draft.FactoryId && x.Enabled)
            .OrderBy(x => x.Id)
            .FirstAsync()
            ?? throw new S8BizException("未找到可用数据源");

        // scene_config：tenant/factory + stageCode + enabled（CreateAsync 也会校验，提前查给更清晰错误）
        var scene = await _sceneRep.AsQueryable()
            .FirstAsync(x => x.TenantId == draft.TenantId && x.FactoryId == draft.FactoryId && x.SceneCode == stageCode)
            ?? throw new S8BizException("规则所属场景不存在");
        if (!scene.Enabled) throw new S8BizException("规则所属场景未启用");

        // params_json + expression
        var paramsJson = BuildParamsJson(form, labels, mechanism, metric, monitorObject, exceptionTypeCode);
        var expression = BuildExpression(mechanism, monitorObject, metric, form);

        return new AdoS8WatchRule
        {
            TenantId = draft.TenantId,
            FactoryId = draft.FactoryId,
            RuleCode = ruleCode,
            SceneCode = stageCode,
            DataSourceId = dataSource.Id,
            WatchObjectType = monitorObject.ObjectType,
            RuleType = RuleTypeOf(mechanism),
            RuleMechanism = mechanism,
            StageCode = stageCode,
            OrderFlowCode = NormalizeOrNull(form.OrderFlowCode),
            SourceObjectType = monitorObject.ObjectType,
            Severity = severity,
            Expression = expression,
            ParamsJson = paramsJson,
            PollIntervalSeconds = form.PollIntervalSeconds is > 0 ? form.PollIntervalSeconds.Value : 300,
            TriggerCountRequired = form.TriggerCountRequired is > 0 ? form.TriggerCountRequired.Value : 1,
            RecoverCountRequired = form.RecoverCountRequired is > 0 ? form.RecoverCountRequired.Value : 1,
            ConsecutiveFailureCount = 0,
            Enabled = false,
        };
    }

    private async Task<AdoS8MonitorObject> ResolveMonitorObjectAsync(long tenantId, long factoryId, string objectType, string objectName)
    {
        var rows = await _monitorObjectRep.AsQueryable()
            .Where(x => x.ObjectType == objectType && x.ObjectName == objectName
                && ((x.TenantId == 0 && x.FactoryId == 0)
                    || (x.TenantId == tenantId && x.FactoryId == factoryId)))
            .ToListAsync();
        var picked = rows
            .OrderByDescending(x => x.FactoryId)
            .ThenByDescending(x => x.TenantId)
            .FirstOrDefault();
        if (picked == null || !picked.Enabled)
            throw new S8BizException($"监控对象 '{objectName}' 不存在或未启用");
        return picked;
    }

    private async Task<AdoS8MonitorMetric> ResolveMonitorMetricAsync(long tenantId, long factoryId, string metricCode, string mechanism)
    {
        var rows = await _monitorMetricRep.AsQueryable()
            .Where(x => x.MetricCode == metricCode
                && ((x.TenantId == 0 && x.FactoryId == 0)
                    || (x.TenantId == tenantId && x.FactoryId == factoryId)))
            .ToListAsync();
        var picked = rows
            .OrderByDescending(x => x.FactoryId)
            .ThenByDescending(x => x.TenantId)
            .FirstOrDefault();
        if (picked == null)
            throw new S8BizException($"监控指标 '{metricCode}' 不存在");
        if (!picked.Enabled)
        {
            var label = string.IsNullOrWhiteSpace(picked.MetricName) ? picked.MetricCode : picked.MetricName;
            throw new S8BizException($"监控指标 '{label}' 未启用，请先在监控指标字典中启用后再生成规则");
        }
        if (!string.IsNullOrWhiteSpace(picked.Mechanism) && picked.Mechanism != mechanism)
            throw new S8BizException("监控指标与报警机制不匹配");
        return picked;
    }

    private async Task<AdoS8ExceptionType> ResolveExceptionTypeAsync(long tenantId, long factoryId, string typeCode)
    {
        var rows = await _exceptionTypeRep.AsQueryable()
            .Where(x => x.TypeCode == typeCode
                && ((x.TenantId == 0 && x.FactoryId == 0)
                    || (x.TenantId == tenantId && x.FactoryId == factoryId)))
            .ToListAsync();
        var picked = rows.OrderByDescending(x => x.FactoryId).ThenByDescending(x => x.TenantId).FirstOrDefault();
        if (picked == null) throw new S8BizException($"异常类型 '{typeCode}' 不存在");
        if (!picked.Enabled) throw new S8BizException($"异常类型 '{picked.TypeName}' 未启用");
        return picked;
    }

    private static string RuleTypeOf(string mechanism) => mechanism switch
    {
        "DATE" => "TIMEOUT",
        "VALUE_RANGE" => "OUT_OF_RANGE",
        "RATIO" => "OUT_OF_RANGE",
        "MANUAL_REPORT" => throw new S8BizException("主动提报无需生成自动监控规则"),
        _ => throw new S8BizException($"不支持的报警机制：{mechanism}"),
    };

    // CONFIG-WIZARD-EXPRESSION-REAL-SQL-1：基于字典 source_table + 字段映射生成真实 SELECT。
    // 仅 DATE / VALUE_RANGE 走真实 SQL；source_table 为空时回退占位（规则仍 enabled=false，调度不会命中）。
    // 表名/列名走 IsSafeIdentifier 白名单校验，杜绝字典脏值导致的注入。
    private static readonly Regex SafeIdentifierPattern =
        new(@"^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$", RegexOptions.Compiled);

    private static string ValidateSqlIdentifier(string raw, string what)
    {
        if (string.IsNullOrWhiteSpace(raw))
            throw new S8BizException($"字典字段缺失：{what}");
        var trimmed = raw.Trim();
        if (!SafeIdentifierPattern.IsMatch(trimmed))
            throw new S8BizException($"字典字段不合法：{what}={raw}");
        return trimmed;
    }

    private const string DatePlaceholderExpression =
        "SELECT 'DEMO-OBJECT-001' AS related_object_code, 'DEMO-OBJECT-001' AS source_object_id, 'DEMO-OBJECT-001' AS related_object_name, DATE_SUB(NOW(), INTERVAL 1 HOUR) AS due_at, 'PENDING' AS status WHERE 1=0";

    private const string ValuePlaceholderExpression =
        "SELECT 'DEMO-OBJECT-001' AS related_object_code, 'DEMO-OBJECT-001' AS source_object_id, 'DEMO-OBJECT-001' AS related_object_name, 0 AS measured_value WHERE 1=0";

    /// <summary>
    /// 含逻辑删除字段的源表追加过滤，避免规则启用后误命中已软删行。
    /// 当前仅 ic_item_stock 含 IsDeleted bit(1)；其他表无 schema 元数据，不追加。
    /// </summary>
    private static string BuildSoftDeletePredicate(string? sourceTable)
    {
        if (string.IsNullOrWhiteSpace(sourceTable)) return string.Empty;
        if (sourceTable.Equals("ic_item_stock", StringComparison.OrdinalIgnoreCase))
            return " AND IsDeleted = 0";
        return string.Empty;
    }

    private static string BuildExpression(string mechanism, AdoS8MonitorObject monitorObject, AdoS8MonitorMetric metric, S8WizardFormV1 form)
    {
        var sourceTable = NormalizeOrNull(monitorObject.SourceTable);
        if (string.IsNullOrWhiteSpace(sourceTable))
        {
            // 字典中未配置 source_table；回退占位 SQL，规则仍默认 enabled=false。
            return mechanism == "DATE" ? DatePlaceholderExpression : ValuePlaceholderExpression;
        }

        var table = ValidateSqlIdentifier(sourceTable, "source_table");
        var idCol = ValidateSqlIdentifier(NormalizeOrNull(metric.ObjectIdField) ?? "id", "object_id_field");
        var codeCol = ValidateSqlIdentifier(NormalizeOrNull(metric.ObjectCodeField) ?? idCol, "object_code_field");
        var nameCol = ValidateSqlIdentifier(NormalizeOrNull(metric.ObjectNameField) ?? codeCol, "object_name_field");

        if (mechanism == "DATE")
        {
            var dueCol = ValidateSqlIdentifier(NormalizeOrNull(metric.DueAtField) ?? "due_at", "due_at_field");
            var statusCol = ValidateSqlIdentifier(NormalizeOrNull(metric.StatusField) ?? "status", "status_field");

            var grace = form.GraceMinutes ?? metric.DefaultGraceMinutes ?? 0;
            if (grace < 0) grace = 0;
            var graceClause = grace > 0
                ? $"DATE_SUB(NOW(), INTERVAL {grace} MINUTE)"
                : "NOW()";

            var states = ParseCsvStates(form.CompletedStates);
            if (states.Count == 0) states = ParseCsvStates(metric.DefaultCompletedStates);
            var stateClause = string.Empty;
            if (states.Count > 0)
            {
                var quoted = string.Join(",", states.Select(s => "'" + s.Replace("'", "''") + "'"));
                stateClause = $" AND {statusCol} NOT IN ({quoted})";
            }

            var softDeleteClause = BuildSoftDeletePredicate(table);
            return $"SELECT {idCol} AS source_object_id, {codeCol} AS related_object_code, {nameCol} AS related_object_name, {dueCol} AS due_at, {statusCol} AS status FROM {table} WHERE {dueCol} IS NOT NULL AND {dueCol} < {graceClause}{stateClause}{softDeleteClause}";
        }

        if (mechanism == "VALUE_RANGE")
        {
            var mvCol = ValidateSqlIdentifier(NormalizeOrNull(metric.MeasuredValueField) ?? "measured_value", "measured_value_field");

            var lower = form.LowerBound ?? metric.DefaultLowerBound;
            var upper = form.UpperBound ?? metric.DefaultUpperBound;
            var preds = new List<string>();
            if (lower.HasValue) preds.Add($"{mvCol} < {lower.Value.ToString(CultureInfo.InvariantCulture)}");
            if (upper.HasValue) preds.Add($"{mvCol} > {upper.Value.ToString(CultureInfo.InvariantCulture)}");
            var oorClause = preds.Count > 0 ? " AND (" + string.Join(" OR ", preds) + ")" : string.Empty;

            var softDeleteClause = BuildSoftDeletePredicate(table);
            return $"SELECT {idCol} AS source_object_id, {codeCol} AS related_object_code, {nameCol} AS related_object_name, {mvCol} AS measured_value FROM {table} WHERE {mvCol} IS NOT NULL{oorClause}{softDeleteClause}";
        }

        // RATIO 在上游已被拒绝（metric.Enabled=false 触发 400），此处兜底返回数值占位。
        return ValuePlaceholderExpression;
    }

    private static string BuildParamsJson(
        S8WizardFormV1 form,
        S8WizardLabelsV1? labels,
        string mechanism,
        AdoS8MonitorMetric metric,
        AdoS8MonitorObject monitorObject,
        string exceptionTypeCode)
    {
        var objectLabel = NormalizeOrNull(labels?.ObjectLabel)
                          ?? NormalizeOrNull(form.ObjectLabel)
                          ?? monitorObject.ObjectName;
        var metricLabel = NormalizeOrNull(labels?.MetricLabel)
                          ?? NormalizeOrNull(form.MetricLabel)
                          ?? metric.MetricName;
        var unit = NormalizeOrNull(form.Unit) ?? metric.Unit;
        var thresholdDisplay = NormalizeOrNull(labels?.ThresholdDisplay)
                               ?? BuildThresholdDisplay(mechanism, form, metric, unit);

        var objectIdField = NormalizeOrNull(metric.ObjectIdField) ?? "source_object_id";
        var objectCodeField = NormalizeOrNull(metric.ObjectCodeField) ?? "related_object_code";
        var objectNameField = NormalizeOrNull(metric.ObjectNameField) ?? "related_object_name";

        var dict = new Dictionary<string, object?>
        {
            ["objectIdField"] = objectIdField,
            ["objectCodeField"] = objectCodeField,
            ["objectNameField"] = objectNameField,
            ["exceptionTypeCode"] = exceptionTypeCode,
            ["objectLabel"] = objectLabel,
            ["metricLabel"] = metricLabel,
        };

        if (mechanism == "DATE")
        {
            var states = ParseCsvStates(form.CompletedStates);
            if (states.Count == 0) states = ParseCsvStates(metric.DefaultCompletedStates);
            if (states.Count == 0) states = new List<string> { "CLOSED", "COMPLETED", "DONE" };

            var grace = form.GraceMinutes ?? metric.DefaultGraceMinutes ?? 0;
            dict["dueAtField"] = NormalizeOrNull(metric.DueAtField) ?? "due_at";
            dict["statusField"] = NormalizeOrNull(metric.StatusField) ?? "status";
            dict["completedStates"] = states;
            dict["graceMinutes"] = grace;
            dict["unit"] = unit ?? "分钟";
            dict["thresholdDisplay"] = thresholdDisplay;
        }
        else if (mechanism == "VALUE_RANGE")
        {
            var lower = form.LowerBound ?? metric.DefaultLowerBound;
            var upper = form.UpperBound ?? metric.DefaultUpperBound;
            if (lower == null && upper == null)
                throw new S8BizException("请配置上限或下限至少其一");
            dict["measuredValueField"] = NormalizeOrNull(metric.MeasuredValueField) ?? "measured_value";
            dict["unit"] = unit;
            dict["thresholdDisplay"] = thresholdDisplay;
            if (lower.HasValue) dict["lowerBound"] = lower.Value;
            if (upper.HasValue) dict["upperBound"] = upper.Value;
            if (form.ToleranceAbs is > 0) dict["toleranceAbs"] = form.ToleranceAbs.Value;
            if (form.ToleranceRatioPct is > 0) dict["toleranceRatio"] = form.ToleranceRatioPct.Value / 100m;
        }
        else if (mechanism == "RATIO")
        {
            var target = form.TargetRatio ?? metric.DefaultTargetRatio;
            if (!target.HasValue) throw new S8BizException("请配置目标比例");
            dict["measuredValueField"] = NormalizeOrNull(metric.MeasuredValueField) ?? "measured_value";
            dict["lowerBound"] = target.Value;
            dict["unit"] = "%";
            dict["thresholdDisplay"] = thresholdDisplay;
        }
        else
        {
            throw new S8BizException($"不支持的报警机制：{mechanism}");
        }

        return JsonSerializer.Serialize(dict, ParamsJsonWriteOptions);
    }

    private static List<string> ParseCsvStates(string? csv)
    {
        if (string.IsNullOrWhiteSpace(csv)) return new List<string>();
        return csv.Split(new[] { ',', ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                  .ToList();
    }

    private static string BuildThresholdDisplay(string mechanism, S8WizardFormV1 form, AdoS8MonitorMetric metric, string? unit)
    {
        var u = unit ?? string.Empty;
        if (mechanism == "DATE")
        {
            var grace = form.GraceMinutes ?? metric.DefaultGraceMinutes ?? 0;
            return grace > 0 ? $"超期 {grace} 分钟仍未完成触发" : "超期即触发";
        }
        if (mechanism == "RATIO")
        {
            var target = form.TargetRatio ?? metric.DefaultTargetRatio;
            return target.HasValue
                ? string.Format(CultureInfo.InvariantCulture, "低于 {0}% 触发", target.Value)
                : "低于目标比例触发";
        }
        if (mechanism == "VALUE_RANGE")
        {
            var lower = form.LowerBound ?? metric.DefaultLowerBound;
            var upper = form.UpperBound ?? metric.DefaultUpperBound;
            var segs = new List<string>();
            if (lower.HasValue) segs.Add(string.Format(CultureInfo.InvariantCulture, "低于 {0}{1}", lower.Value, u));
            if (upper.HasValue) segs.Add(string.Format(CultureInfo.InvariantCulture, "高于 {0}{1}", upper.Value, u));
            return segs.Count > 0 ? string.Join(" 或 ", segs) + " 触发" : "超出设定范围触发";
        }
        return string.Empty;
    }

    // ----- wizard_json 解析私有 DTO -----

    private sealed class S8WizardJsonV1
    {
        public int Version { get; set; }
        public int Step { get; set; }
        public S8WizardFormV1? Form { get; set; }
        public S8WizardLabelsV1? Labels { get; set; }
    }

    private sealed class S8WizardFormV1
    {
        public string? Mechanism { get; set; }
        public string? StageCode { get; set; }
        public string? OrderFlowCode { get; set; }
        public string? ExceptionTypeCode { get; set; }
        public string? Severity { get; set; }
        public string? ObjectType { get; set; }
        public string? ObjectLabel { get; set; }
        public string? MetricCode { get; set; }
        public string? MetricLabel { get; set; }
        public string? Unit { get; set; }
        public int? GraceMinutes { get; set; }
        // 前端 wizard_json 中 completedStates 是 CSV 字符串（如 "CLOSED,COMPLETED,DONE"），不是数组
        public string? CompletedStates { get; set; }
        public decimal? LowerBound { get; set; }
        public decimal? UpperBound { get; set; }
        public decimal? ToleranceAbs { get; set; }
        public decimal? ToleranceRatioPct { get; set; }
        public decimal? TargetRatio { get; set; }
        public string? RuleCode { get; set; }
        public int? PollIntervalSeconds { get; set; }
        public int? TriggerCountRequired { get; set; }
        public int? RecoverCountRequired { get; set; }
    }

    private sealed class S8WizardLabelsV1
    {
        public string? MechanismLabel { get; set; }
        public string? StageLabel { get; set; }
        public string? ObjectLabel { get; set; }
        public string? MetricLabel { get; set; }
        public string? ThresholdDisplay { get; set; }
    }
}