AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/AdoSmartOpsImprovementPlanController.cs

using System.Globalization;
using System.Text.Json;
using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Entity.S0.Warehouse;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Service;
using Admin.NET.Plugin.AiDOP.SmartOps;
using Admin.NET.Plugin.ApprovalFlow.Service;

namespace Admin.NET.Plugin.AiDOP.Controllers;

[ApiController]
[Route("api/[controller]")]
[NonUnify]
public class AdoSmartOpsImprovementPlanController : ControllerBase
{
    private readonly ISqlSugarClient _db;
    private readonly FlowEngineService _flowEngine;
    private readonly SmartOpsTrustedScopeResolver _trustedScope;
    private readonly SmartOpsImprovementActionService _actions;
    private readonly SmartOpsImprovementApproverGuard _approverGuard;

    public AdoSmartOpsImprovementPlanController(
        ISqlSugarClient db,
        FlowEngineService flowEngine,
        SmartOpsTrustedScopeResolver trustedScope,
        SmartOpsImprovementActionService actions,
        SmartOpsImprovementApproverGuard approverGuard)
    {
        _db = db;
        _flowEngine = flowEngine;
        _trustedScope = trustedScope;
        _actions = actions;
        _approverGuard = approverGuard;
    }

    [HttpPost("createFromDiagnosis")]
    public async Task<IActionResult> CreateFromDiagnosis([FromBody] ImprovementPlanCreateInput input)
    {
        var scope = await _trustedScope.ResolveAsync(input.FactoryId);
        var tenantId = scope.TenantId;
        var now = DateTime.Now;
        var entity = new AdoSmartOpsImprovementPlan
        {
            TenantId = tenantId,
            FactoryId = scope.FactoryId,
            PlanNo = await GeneratePlanNoAsync(tenantId),
            ModuleCode = (input.ModuleCode ?? "").Trim().ToUpperInvariant(),
            MetricCode = input.MetricCode,
            ProblemLevel = input.ProblemLevel,
            ProblemMetricCode = input.ProblemMetricCode,
            ProblemName = input.ProblemName ?? "",
            ProblemDept = input.ProblemDept,
            ProblemSeverity = SmartOpsImprovementApprovalRules.NormalizeSeverity(input.ProblemSeverity),
            IsCrossDept = SmartOpsImprovementApprovalRules.IsCrossDept(input.ModuleCode, input.IsCrossDept, input.ProblemDept),
            TargetValue = input.TargetValue,
            ActualValue = input.ActualValue,
            GapLabel = input.GapLabel,
            RootCause = input.RootCause,
            ActionItemsJson = JsonSerializer.Serialize(input.ActionItems ?? new List<ImprovementActionItem>()),
            OwnerUserId = input.OwnerUserId,
            DueDate = SmartOpsImprovementActionService.ResolveLatestDueDate(input.ActionItems),
            Status = "draft",
            CreateTime = now,
            UpdateTime = now,
        };

        if (string.IsNullOrWhiteSpace(entity.ModuleCode)) return BadRequest(new { message = "模块编码不能为空" });
        if (string.IsNullOrWhiteSpace(entity.ProblemName)) return BadRequest(new { message = "问题名称不能为空" });

        entity.Id = await InsertPlanWithUniqueNoAsync(entity);
        await _actions.ReplaceFromDraftAsync(entity, input.ActionItems ?? new List<ImprovementActionItem>(), scope);
        return Ok(await ToOutputAsync(entity));
    }

    [HttpPost("page")]
    public async Task<IActionResult> Page([FromBody] ImprovementPlanPageInput input)
    {
        var tenantId = (await _trustedScope.ResolveAsync()).TenantId;
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : input.PageSize;
        var q = ApplyLedgerFilter(_db.Queryable<AdoSmartOpsImprovementPlan>(), tenantId, input)
            .OrderByDescending(x => x.CreateTime);
        var result = await q.ToPagedListAsync(page, pageSize);
        var items = new List<object>();
        foreach (var item in result.Items)
            items.Add(await ToOutputAsync(item));
        return Ok(new
        {
            result.Page,
            result.PageSize,
            result.Total,
            Items = items
        });
    }

    [HttpPost("export")]
    public async Task<IActionResult> Export([FromBody] ImprovementPlanPageInput input)
    {
        var tenantId = (await _trustedScope.ResolveAsync()).TenantId;
        var rows = await ApplyLedgerFilter(_db.Queryable<AdoSmartOpsImprovementPlan>(), tenantId, input)
            .OrderByDescending(x => x.CreateTime)
            .ToListAsync();
        var sb = new System.Text.StringBuilder();
        sb.AppendLine("计划号,模块,指标,问题名称,责任部门,责任人,状态,自动评价,到期日,创建时间,是否逾期");
        foreach (var x in rows)
        {
            var overdue = x.DueDate.HasValue && x.DueDate.Value.Date < DateTime.Today && x.Status != "closed";
            sb.AppendLine(string.Join(",",
                Csv(x.PlanNo),
                Csv(x.ModuleCode),
                Csv(x.ProblemMetricCode ?? x.MetricCode),
                Csv(x.ProblemName),
                Csv(x.ProblemDept),
                Csv(x.OwnerUserId?.ToString()),
                Csv(x.Status),
                Csv(x.AutoVerifyResult),
                Csv(x.DueDate?.ToString("yyyy-MM-dd")),
                Csv(x.CreateTime.ToString("yyyy-MM-dd HH:mm")),
                overdue ? "是" : "否"));
        }
        var bytes = System.Text.Encoding.UTF8.GetPreamble().Concat(System.Text.Encoding.UTF8.GetBytes(sb.ToString())).ToArray();
        return File(bytes, "text/csv; charset=utf-8", $"改善计划台账-{DateTime.Today:yyyyMMdd}.csv");
    }

    [HttpGet("ledgerLogs")]
    public async Task<IActionResult> LedgerLogs([FromQuery] long id)
    {
        var plan = await LoadPlanAsync(id) ?? throw Oops.Oh("改善计划不存在");
        var actions = await _actions.ListByPlanAsync(plan.TenantId ?? 0, plan.Id);
        var actionLogs = new List<AdoSmartOpsImprovementActionLog>();
        foreach (var action in actions)
            actionLogs.AddRange(await _actions.ListLogsAsync(plan.TenantId ?? 0, action.Id));
        var verifyLogs = await _db.Queryable<AdoSmartOpsImprovementVerifyLog>()
            .Where(x => x.TenantId == plan.TenantId && x.PlanId == plan.Id)
            .OrderByDescending(x => x.CreateTime)
            .ToListAsync();
        return Ok(new { actionLogs, verifyLogs });
    }

    /// <summary>
    /// 智慧诊断选人/选部门：当前租户用户与机构，不走系统管理 sysUser/sysOrg（避免无按钮权限的 403 弹窗）。
    /// </summary>
    [HttpGet("tenantPickers")]
    public async Task<IActionResult> GetTenantPickers([FromQuery] string? keyword = null)
    {
        var tenantId = (await _trustedScope.ResolveAsync()).TenantId;
        var kw = string.IsNullOrWhiteSpace(keyword) ? null : keyword.Trim();

        var userQuery = _db.Queryable<SysUser>()
            .LeftJoin<SysOrg>((u, o) => u.OrgId == o.Id)
            .Where(u => u.TenantId == tenantId)
            .Where(u => u.Status == StatusEnum.Enable)
            .Where(u => u.AccountType != AccountTypeEnum.SuperAdmin)
            .WhereIF(kw != null, (u, o) =>
                u.RealName.Contains(kw!) || u.Account.Contains(kw!) || o.Name.Contains(kw!));

        var users = await userQuery
            .OrderBy(u => u.OrderNo)
            .OrderBy(u => u.Id)
            .Take(200)
            .Select((u, o) => new
            {
                id = u.Id,
                account = u.Account,
                realName = u.RealName,
                orgId = u.OrgId,
                orgName = o.Name
            })
            .ToListAsync();

        var orgs = await _db.Queryable<SysOrg>()
            .Where(o => o.TenantId == tenantId && o.Status == StatusEnum.Enable)
            .OrderBy(o => o.OrderNo)
            .Select(o => new { id = o.Id, name = o.Name })
            .ToListAsync();

        // 行动项责任部门走 S0 部门主数据，不是系统机构 SysOrg（租户往往只有一条公司根节点）。
        var departments = await _db.Queryable<AdoS0DepartmentMaster>()
            .ClearFilter()
            .Where(d => d.TenantId == tenantId && d.IsActive)
            .OrderBy(d => d.Department)
            .Select(d => new
            {
                id = d.Id,
                name = d.Department,
                descr = d.Descr
            })
            .ToListAsync();

        return Ok(new { users, orgs, departments });
    }

    [HttpGet("detail")]
    public async Task<IActionResult> Detail([FromQuery] long id)
    {
        var tenantId = (await _trustedScope.ResolveAsync()).TenantId;
        var entity = await _db.Queryable<AdoSmartOpsImprovementPlan>()
            .FirstAsync(x => x.Id == id && x.TenantId == tenantId);
        return entity == null ? NotFound() : Ok(await ToOutputAsync(entity));
    }

    [HttpGet("verificationDashboard")]
    public async Task<IActionResult> VerificationDashboard([FromQuery] long id)
    {
        var plan = await LoadPlanAsync(id) ?? throw Oops.Oh("改善计划不存在");
        var actionRows = await _actions.ListByPlanAsync(plan.TenantId ?? 0, plan.Id);
        var actions = SmartOpsImprovementActionService.ToLegacyItems(actionRows);
        var startDate = plan.CreateTime.Date;
        var endDate = ResolveVerificationEndDate(plan, actions);
        if (endDate < startDate) endDate = startDate;
        var queryStart = startDate.AddDays(-30);

        var metricCode = (plan.ProblemMetricCode ?? plan.MetricCode ?? "").Trim();
        var metricMeta = string.IsNullOrWhiteSpace(metricCode)
            ? null
            : await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.TenantId == plan.TenantId && x.MetricCode == metricCode);
        var metricLevel = metricMeta?.MetricLevel ?? NormalizeMetricLevel(plan.ProblemLevel);
        var targetFallback = ParseDecimal(plan.TargetValue);
        var table = ResolveKpiValueTable(metricLevel);
        var trendRows = await QueryVerificationTrendAsync(table, plan, metricCode, queryStart, endDate);
        if (trendRows.Count == 0)
        {
            foreach (var fallbackTable in new[] { "ado_s9_kpi_value_l4_day", "ado_s9_kpi_value_l3_day", "ado_s9_kpi_value_l2_day", "ado_s9_kpi_value_l1_day" })
            {
                if (fallbackTable == table) continue;
                trendRows = await QueryVerificationTrendAsync(fallbackTable, plan, metricCode, queryStart, endDate);
                if (trendRows.Count > 0) break;
            }
        }

        var trendByDate = trendRows
            .GroupBy(x => x.BizDate.Date)
            .ToDictionary(x => x.Key, x => x.OrderByDescending(r => r.TargetValue.HasValue).First());
        var timeline = BuildDateRange(startDate, endDate)
            .Select(day => new
            {
                Date = day.ToString("yyyy-MM-dd"),
                IsStart = day == startDate,
                IsEnd = day == endDate,
                Actions = actions
                    .Select((action, index) => BuildVerificationActionNode(action, index, day))
                    .Where(x => x != null)
                    .ToList(),
            })
            .ToList();
        var series = BuildDateRange(startDate, endDate)
            .Select(day =>
            {
                trendByDate.TryGetValue(day, out var row);
                return new
                {
                    Date = day.ToString("yyyy-MM-dd"),
                    Value = row?.MetricValue,
                    Target = row?.TargetValue ?? targetFallback,
                };
            })
            .ToList();

        return Ok(new
        {
            Plan = await ToOutputAsync(plan),
            Metric = new
            {
                MetricCode = metricCode,
                MetricName = metricMeta?.MetricName ?? plan.ProblemName,
                Unit = metricMeta?.Unit ?? ResolveUnitFromText(plan.TargetValue),
                TargetValue = targetFallback,
                Level = metricLevel,
                Direction = metricMeta?.Direction ?? "higher_is_better",
            },
            StartDate = startDate.ToString("yyyy-MM-dd"),
            EndDate = endDate.ToString("yyyy-MM-dd"),
            Timeline = timeline,
            Trend = series,
            Effectiveness = EvaluateEffectiveness(plan, actions, trendRows, metricMeta?.Direction, targetFallback),
        });
    }

    [HttpPost("submitApproval")]
    public async Task<IActionResult> SubmitApproval([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status is "approving" && plan.FlowInstanceId is > 0)
            return Ok(new { instanceId = plan.FlowInstanceId, plan = await ToOutputAsync(plan) });
        if (plan.Status is "approving" or "approved" or "executing" or "pending_verify" or "closed")
            return BadRequest(new { message = "当前状态不可提交审批" });

        try
        {
            await _approverGuard.EnsureResolvableAsync(plan);
            var route = SmartOpsImprovementApprovalRules.ResolveRoute(plan);
            var instanceId = await _flowEngine.StartFlow(new StartFlowInput
            {
                BizType = SmartOpsImprovementBizHandler.BizTypeCode,
                BizId = plan.Id,
                BizNo = plan.PlanNo,
                Title = $"智慧诊断改善计划-{plan.PlanNo}",
                Comment = input.Comment,
                BizData = new Dictionary<string, object>
                {
                    ["moduleCode"] = plan.ModuleCode,
                    ["problemLevel"] = plan.ProblemLevel,
                    ["problemName"] = plan.ProblemName,
                    ["problemDept"] = plan.ProblemDept ?? "",
                    ["problemSeverity"] = SmartOpsImprovementApprovalRules.NormalizeSeverity(plan.ProblemSeverity),
                    ["isCrossDept"] = plan.IsCrossDept ? 1 : 0,
                    ["route"] = route,
                }
            });
            plan = await LoadPlanAsync(input.Id) ?? plan;
            return Ok(new { instanceId, plan = await ToOutputAsync(plan) });
        }
        catch (Exception ex)
        {
            return BadRequest(new { message = $"提交审批失败：{ex.Message}" });
        }
    }

    [HttpPost("startExecution")]
    public async Task<IActionResult> StartExecution([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        var scope = await _trustedScope.ResolveAsync();
        await EnsureCanStartExecutionAsync(plan, scope);
        var alreadyExecuting = plan.Status == "executing";
        await UpdateStatusAsync(plan.Id, plan.TenantId, "executing");
        var latest = (await LoadPlanAsync(plan.Id))!;
        if (!alreadyExecuting)
            await _actions.NotifyAssignedForPlanAsync(latest, scope.UserId);
        return Ok(await ToOutputAsync(latest));
    }

    [HttpPost("updateActionItem")]
    public async Task<IActionResult> UpdateActionItem([FromBody] ImprovementActionItemUpdateInput input)
    {
        var scope = await _trustedScope.ResolveAsync();
        await _actions.MutateAsync(
            input.Id,
            input.Index,
            input.ActionId,
            input.Status,
            input.CompletedAt,
            input.OwnerUserId,
            input.Owner,
            input.ProofRemark,
            scope);
        return Ok(await ToOutputAsync((await LoadPlanAsync(input.Id))!));
    }

    [HttpGet("myActions")]
    public async Task<IActionResult> MyActions()
    {
        var scope = await _trustedScope.ResolveAsync();
        var items = await _actions.ListMineAsync(scope);
        return Ok(items.Select(SmartOpsImprovementActionService.ToApiItem).ToList());
    }

    [HttpGet("actionLogs")]
    public async Task<IActionResult> ActionLogs([FromQuery] long actionId)
    {
        var scope = await _trustedScope.ResolveAsync();
        return Ok(await _actions.ListLogsAsync(scope.TenantId, actionId));
    }

    [HttpPost("submitVerification")]
    public async Task<IActionResult> SubmitVerification([FromBody] ImprovementVerificationInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status is not ("executing" or "pending_verify"))
            return BadRequest(new { message = "执行中计划才能提交验证" });

        var scope = await _trustedScope.ResolveAsync();
        var actionRows = await _actions.ListByPlanAsync(plan.TenantId ?? 0, plan.Id);
        var actions = SmartOpsImprovementActionService.ToLegacyItems(actionRows);
        var metricCode = (plan.ProblemMetricCode ?? plan.MetricCode ?? "").Trim();
        var metricMeta = string.IsNullOrWhiteSpace(metricCode)
            ? null
            : await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.TenantId == plan.TenantId && x.MetricCode == metricCode);
        var startDate = plan.CreateTime.Date;
        var endDate = ResolveVerificationEndDate(plan, actions);
        if (endDate < startDate) endDate = startDate;
        var table = ResolveKpiValueTable(metricMeta?.MetricLevel ?? NormalizeMetricLevel(plan.ProblemLevel));
        var trendRows = await QueryVerificationTrendAsync(table, plan, metricCode, startDate.AddDays(-30), endDate);
        var auto = EvaluateEffectiveness(plan, actions, trendRows, metricMeta?.Direction, ParseDecimal(plan.TargetValue));
        if (ManualDiffersFromAuto(input.VerifyResult, auto.Conclusion) && string.IsNullOrWhiteSpace(input.VerifyRemark))
            return BadRequest(new { message = "人工结论与自动评价不一致时必须填写理由" });

        var now = DateTime.Now;
        var autoJson = JsonSerializer.Serialize(auto);
        await _db.Updateable<AdoSmartOpsImprovementPlan>()
            .SetColumns(x => x.Status == "pending_verify")
            .SetColumns(x => x.VerifyResult == input.VerifyResult)
            .SetColumns(x => x.VerifyValue == input.VerifyValue)
            .SetColumns(x => x.VerifyRemark == input.VerifyRemark)
            .SetColumns(x => x.AutoVerifyResult == auto.Conclusion)
            .SetColumns(x => x.AutoVerifyJson == autoJson)
            .SetColumns(x => x.AutoVerifyTime == now)
            .SetColumns(x => x.UpdateTime == now)
            .Where(x => x.Id == plan.Id && x.TenantId == plan.TenantId)
            .ExecuteCommandAsync();
        await _db.Insertable(new AdoSmartOpsImprovementVerifyLog
        {
            TenantId = plan.TenantId,
            PlanId = plan.Id,
            AutoResult = auto.Conclusion,
            ManualResult = input.VerifyResult,
            Remark = input.VerifyRemark,
            OperatorUserId = scope.UserId,
            CreateTime = now,
        }).ExecuteCommandAsync();
        return Ok(await ToOutputAsync((await LoadPlanAsync(plan.Id))!));
    }

    [HttpPost("close")]
    public async Task<IActionResult> Close([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status != "pending_verify")
            return BadRequest(new { message = "提交验证后才能关闭" });
        var openActions = (await _actions.ListByPlanAsync(plan.TenantId ?? 0, plan.Id))
            .Where(x => SmartOpsImprovementActionStatus.IsOpen(x.Status))
            .Select(x => x.Id)
            .ToList();
        if (openActions.Count > 0)
            return BadRequest(new { message = "仍有未完成的行动项，不能关闭" });
        await UpdateStatusAsync(plan.Id, plan.TenantId, "closed");
        return Ok(await ToOutputAsync((await LoadPlanAsync(plan.Id))!));
    }

    private async Task<AdoSmartOpsImprovementPlan?> LoadPlanAsync(long id)
    {
        var tenantId = (await _trustedScope.ResolveAsync()).TenantId;
        return await _db.Queryable<AdoSmartOpsImprovementPlan>().FirstAsync(x => x.Id == id && x.TenantId == tenantId);
    }

    private async Task EnsureCanStartExecutionAsync(AdoSmartOpsImprovementPlan plan, SmartOpsTrustedScope scope)
    {
        if (plan.Status != "approved" && plan.Status != "executing")
            throw Oops.Oh("审批通过后才能开始执行");
        var ownerId = await _actions.ResolveOwnerUserIdAsync(plan);
        if (ownerId is > 0 && plan.OwnerUserId != ownerId)
        {
            await _db.Updateable<AdoSmartOpsImprovementPlan>()
                .SetColumns(x => x.OwnerUserId == ownerId)
                .SetColumns(x => x.UpdateTime == DateTime.Now)
                .Where(x => x.Id == plan.Id && x.TenantId == plan.TenantId)
                .ExecuteCommandAsync();
            plan.OwnerUserId = ownerId;
        }
        if (plan.OwnerUserId is not > 0)
            throw Oops.Oh("该计划未指定总负责人，无法开始执行");
        if (!SmartOpsImprovementApprovalRules.CanStartExecution(plan.OwnerUserId, scope.UserId, plan.Status))
            throw Oops.Oh("仅改善计划总负责人可以开始执行");
    }

    private async Task UpdateStatusAsync(long id, long? tenantId, string status)
    {
        await _db.Updateable<AdoSmartOpsImprovementPlan>()
            .SetColumns(x => x.Status == status)
            .SetColumns(x => x.UpdateTime == DateTime.Now)
            .Where(x => x.Id == id && x.TenantId == tenantId)
            .ExecuteCommandAsync();
    }

    private async Task<string> GeneratePlanNoAsync(long tenantId)
    {
        var prefix = SmartOpsImprovementPlanNo.BuildPrefix(DateTime.Now);
        var existing = await _db.Queryable<AdoSmartOpsImprovementPlan>()
            .Where(x => x.TenantId == tenantId && x.PlanNo.StartsWith(prefix))
            .Select(x => x.PlanNo)
            .ToListAsync();
        return SmartOpsImprovementPlanNo.Next(prefix, existing);
    }

    private async Task<long> InsertPlanWithUniqueNoAsync(AdoSmartOpsImprovementPlan entity)
    {
        for (var attempt = 0; attempt < 8; attempt++)
        {
            try
            {
                return await _db.Insertable(entity).ExecuteReturnBigIdentityAsync();
            }
            catch (Exception ex) when (attempt < 7 && IsDuplicatePlanNo(ex))
            {
                entity.PlanNo = await GeneratePlanNoAsync(entity.TenantId ?? 0);
            }
        }
        throw Oops.Oh("改善计划编号生成失败，请重试");
    }

    private static bool IsDuplicatePlanNo(Exception ex)
    {
        for (var current = ex; current != null; current = current.InnerException)
        {
            var message = current.Message ?? "";
            if (message.Contains("Duplicate", StringComparison.OrdinalIgnoreCase)
                || message.Contains("uk_smart_ops_improve_tenant_plan", StringComparison.OrdinalIgnoreCase))
                return true;
        }
        return false;
    }

    private async Task<string?> ResolveOwnerNameAsync(long? ownerUserId)
    {
        if (ownerUserId is not > 0) return null;
        var names = await _db.Queryable<SysUser>().ClearFilter<ITenantIdFilter>()
            .Where(u => u.Id == ownerUserId)
            .Select(u => u.RealName ?? u.Account)
            .Take(1)
            .ToListAsync();
        return names.FirstOrDefault();
    }

    private async Task<object> ToOutputAsync(AdoSmartOpsImprovementPlan x)
    {
        var actionRows = x.TenantId is > 0
            ? await _actions.ListByPlanAsync(x.TenantId.Value, x.Id)
            : new List<AdoSmartOpsImprovementAction>();
        return new
        {
            x.Id,
            x.PlanNo,
            x.ModuleCode,
            x.MetricCode,
            x.ProblemLevel,
            x.ProblemMetricCode,
            x.ProblemName,
            x.ProblemDept,
            x.ProblemSeverity,
            x.IsCrossDept,
            ApprovalRoute = SmartOpsImprovementApprovalRules.ResolveRoute(x),
            x.TargetValue,
            x.ActualValue,
            x.GapLabel,
            x.RootCause,
            ActionItems = actionRows.Select(SmartOpsImprovementActionService.ToApiItem).ToList(),
            x.OwnerUserId,
            OwnerName = await ResolveOwnerNameAsync(x.OwnerUserId),
            DueDate = x.DueDate?.ToString("yyyy-MM-dd"),
            x.Status,
            x.FlowInstanceId,
            x.VerifyResult,
            x.VerifyValue,
            x.VerifyRemark,
            x.AutoVerifyResult,
            x.AutoVerifyJson,
            x.AutoVerifyTime,
            Overdue = x.DueDate.HasValue && x.DueDate.Value.Date < DateTime.Today && x.Status != "closed",
            x.CreateTime,
            x.UpdateTime,
        };
    }

    private async Task<List<ImprovementVerificationTrendRow>> QueryVerificationTrendAsync(
        string table,
        AdoSmartOpsImprovementPlan plan,
        string metricCode,
        DateTime startDate,
        DateTime endDate)
    {
        if (string.IsNullOrWhiteSpace(metricCode)) return new List<ImprovementVerificationTrendRow>();
        var sql = $"""
SELECT biz_date AS BizDate, metric_value AS MetricValue, target_value AS TargetValue
FROM {table}
WHERE tenant_id=@tenantId AND module_code=@moduleCode
  AND metric_code=@metricCode AND is_deleted=0 AND biz_date BETWEEN @startDate AND @endDate
  AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
  AND (factory_id IS NULL OR factory_id<>@tenantId)
ORDER BY biz_date
""";
        return await _db.Ado.SqlQueryAsync<ImprovementVerificationTrendRow>(sql, new
        {
            tenantId = plan.TenantId,
            factoryId = plan.FactoryId <= 0 ? 1 : plan.FactoryId,
            moduleCode = plan.ModuleCode,
            metricCode,
            startDate,
            endDate,
        });
    }

    private static SmartOpsEffectivenessResult EvaluateEffectiveness(
        AdoSmartOpsImprovementPlan plan,
        List<ImprovementActionItem> actions,
        List<ImprovementVerificationTrendRow> trendRows,
        string? direction,
        decimal? target)
    {
        var create = plan.CreateTime.Date;
        var afterEnd = ResolveVerificationEndDate(plan, actions);
        var before = SmartOpsImprovementEffectiveness.TakeLastValid(
            trendRows.Where(x => x.BizDate.Date < create).OrderBy(x => x.BizDate).Select(x => x.MetricValue));
        var after = SmartOpsImprovementEffectiveness.TakeLastValid(
            trendRows.Where(x => x.BizDate.Date >= create && x.BizDate.Date <= afterEnd).OrderBy(x => x.BizDate).Select(x => x.MetricValue));
        var exBefore = before.Count(v => !SmartOpsImprovementEffectiveness.MeetsTarget(v, target, direction));
        var exAfter = after.Count(v => !SmartOpsImprovementEffectiveness.MeetsTarget(v, target, direction));
        return SmartOpsImprovementEffectiveness.Evaluate(before, after, target, direction, exBefore, exAfter);
    }

    private static bool ManualDiffersFromAuto(string? manual, string auto)
    {
        var mapped = (manual ?? "").Trim().ToLowerInvariant() switch
        {
            "effective" => SmartOpsImprovementEffectiveness.AutoEffective,
            "ineffective" => SmartOpsImprovementEffectiveness.AutoIneffective,
            "partial" => SmartOpsImprovementEffectiveness.AutoInconclusive,
            _ => "",
        };
        return mapped.Length > 0 && !string.Equals(mapped, auto, StringComparison.OrdinalIgnoreCase);
    }

    private static ISugarQueryable<AdoSmartOpsImprovementPlan> ApplyLedgerFilter(
        ISugarQueryable<AdoSmartOpsImprovementPlan> q,
        long tenantId,
        ImprovementPlanPageInput input)
    {
        var today = DateTime.Today;
        return q.Where(x => x.TenantId == tenantId)
            .WhereIF(!string.IsNullOrWhiteSpace(input.ModuleCode), x => x.ModuleCode == input.ModuleCode)
            .WhereIF(!string.IsNullOrWhiteSpace(input.Status), x => x.Status == input.Status)
            .WhereIF(!string.IsNullOrWhiteSpace(input.MetricCode), x => x.MetricCode == input.MetricCode || x.ProblemMetricCode == input.MetricCode)
            .WhereIF(!string.IsNullOrWhiteSpace(input.ProblemDept), x => x.ProblemDept != null && x.ProblemDept.Contains(input.ProblemDept!))
            .WhereIF(input.OwnerUserId is > 0, x => x.OwnerUserId == input.OwnerUserId)
            .WhereIF(input.CreateFrom.HasValue, x => x.CreateTime >= input.CreateFrom)
            .WhereIF(input.CreateTo.HasValue, x => x.CreateTime < input.CreateTo!.Value.Date.AddDays(1))
            .WhereIF(input.DueFrom.HasValue, x => x.DueDate >= input.DueFrom)
            .WhereIF(input.DueTo.HasValue, x => x.DueDate < input.DueTo!.Value.Date.AddDays(1))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AutoVerifyResult), x => x.AutoVerifyResult == input.AutoVerifyResult)
            .WhereIF(input.Overdue == true, x => x.DueDate != null && x.DueDate < today && x.Status != "closed")
            .WhereIF(!string.IsNullOrWhiteSpace(input.Keyword), x => x.PlanNo.Contains(input.Keyword!) || x.ProblemName.Contains(input.Keyword!));
    }

    private static string Csv(string? value)
    {
        var text = value ?? "";
        if (text.Contains(',') || text.Contains('"') || text.Contains('\n'))
            return "\"" + text.Replace("\"", "\"\"") + "\"";
        return text;
    }

    private static DateTime ResolveVerificationEndDate(AdoSmartOpsImprovementPlan plan, List<ImprovementActionItem> actions)
    {
        var candidates = new List<DateTime> { plan.CreateTime.Date };
        if (plan.DueDate.HasValue) candidates.Add(plan.DueDate.Value.Date);
        foreach (var action in actions)
        {
            if (DateTime.TryParse(action.DueDate, out var dueDate)) candidates.Add(dueDate.Date);
            if (DateTime.TryParse(action.CompletedAt, out var completedAt)) candidates.Add(completedAt.Date);
        }
        return candidates.Max();
    }

    private static IEnumerable<DateTime> BuildDateRange(DateTime startDate, DateTime endDate)
    {
        for (var day = startDate.Date; day <= endDate.Date; day = day.AddDays(1))
            yield return day;
    }

    private static ImprovementVerificationActionNode? BuildVerificationActionNode(ImprovementActionItem action, int index, DateTime day)
    {
        if (!DateTime.TryParse(action.DueDate, out var dueDate) || dueDate.Date != day.Date) return null;
        var status = NormalizeActionStatus(action.Status, action.CompletedAt, dueDate.Date);
        return new ImprovementVerificationActionNode
        {
            Index = index + 1,
            Content = action.Content,
            Owner = action.Owner,
            DueDate = dueDate.ToString("yyyy-MM-dd"),
            Status = status,
            StatusLabel = ActionStatusLabel(status),
            CompletedAt = DateTime.TryParse(action.CompletedAt, out var completedAt) ? completedAt.ToString("yyyy-MM-dd") : null,
        };
    }

    private static string NormalizeActionStatus(string? status, string? completedAt, DateTime dueDate)
    {
        var normalized = (status ?? "").Trim().ToLowerInvariant();
        if (normalized is "completed" or "done") return "completed";
        if (normalized is "doing" or "in_progress") return "doing";
        if (!string.IsNullOrWhiteSpace(completedAt)) return "completed";
        if (dueDate < DateTime.Today) return "overdue";
        return string.IsNullOrWhiteSpace(normalized) ? "pending" : normalized;
    }

    private static string NormalizeManualActionStatus(string? status)
    {
        var normalized = (status ?? "").Trim().ToLowerInvariant();
        return normalized switch
        {
            "completed" or "done" => "completed",
            "doing" or "in_progress" => "doing",
            _ => "pending",
        };
    }

    private static string? ResolveActionCompletedAt(string status, string? completedAt)
    {
        if (status != "completed") return null;
        if (DateTime.TryParse(completedAt, out var value)) return value.ToString("yyyy-MM-dd");
        return DateTime.Today.ToString("yyyy-MM-dd");
    }

    private static string ActionStatusLabel(string status)
    {
        return status switch
        {
            "completed" => "已完成",
            "doing" => "进行中",
            "overdue" => "逾期",
            _ => "未开始",
        };
    }

    private static int NormalizeMetricLevel(int problemLevel)
    {
        return problemLevel switch
        {
            <= 1 => 2,
            2 => 3,
            >= 3 => 4,
        };
    }

    private static string ResolveKpiValueTable(int metricLevel)
    {
        return metricLevel switch
        {
            1 => "ado_s9_kpi_value_l1_day",
            2 => "ado_s9_kpi_value_l2_day",
            3 => "ado_s9_kpi_value_l3_day",
            4 => "ado_s9_kpi_value_l4_day",
            _ => "ado_s9_kpi_value_l2_day",
        };
    }

    private static decimal? ParseDecimal(string? text)
    {
        if (string.IsNullOrWhiteSpace(text)) return null;
        var digits = new string(text.Where(c => char.IsDigit(c) || c is '.' or '-').ToArray());
        return decimal.TryParse(digits, NumberStyles.Any, CultureInfo.InvariantCulture, out var value) ? value : null;
    }

    private static string? ResolveUnitFromText(string? text)
    {
        if (string.IsNullOrWhiteSpace(text)) return null;
        var unit = new string(text.Where(c => !char.IsDigit(c) && c is not '.' and not '-' and not '/').ToArray()).Trim();
        return string.IsNullOrWhiteSpace(unit) ? null : unit;
    }

    private static List<ImprovementActionItem> ParseActionItems(string? json)
    {
        if (string.IsNullOrWhiteSpace(json)) return new List<ImprovementActionItem>();
        try
        {
            return JsonSerializer.Deserialize<List<ImprovementActionItem>>(json) ?? new List<ImprovementActionItem>();
        }
        catch
        {
            return new List<ImprovementActionItem>();
        }
    }
}

public class ImprovementPlanCreateInput
{
    /// <summary>客户端工厂仅作筛选建议，服务端以租户 OrgId 为准。</summary>
    public long FactoryId { get; set; } = 1;
    public string? ModuleCode { get; set; }
    public string? MetricCode { get; set; }
    public int ProblemLevel { get; set; }
    public string? ProblemMetricCode { get; set; }
    public string? ProblemName { get; set; }
    public string? ProblemDept { get; set; }
    public string? ProblemSeverity { get; set; }
    public bool IsCrossDept { get; set; }
    public string? TargetValue { get; set; }
    public string? ActualValue { get; set; }
    public string? GapLabel { get; set; }
    public string? RootCause { get; set; }
    public List<ImprovementActionItem>? ActionItems { get; set; }
    public long? OwnerUserId { get; set; }
    /// <summary>忽略。整单到期日由行动项完成日期中最晚一天写入。</summary>
    public DateTime? DueDate { get; set; }
}

public class ImprovementActionItem
{
    public string Content { get; set; } = "";
    public string? Owner { get; set; }
    public long? OwnerUserId { get; set; }
    public string? DueDate { get; set; }
    public string? Status { get; set; }
    public string? CompletedAt { get; set; }
}

public class ImprovementPlanPageInput
{
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 20;
    public string? ModuleCode { get; set; }
    public string? Status { get; set; }
    public string? Keyword { get; set; }
    public string? MetricCode { get; set; }
    public string? ProblemDept { get; set; }
    public long? OwnerUserId { get; set; }
    public DateTime? CreateFrom { get; set; }
    public DateTime? CreateTo { get; set; }
    public DateTime? DueFrom { get; set; }
    public DateTime? DueTo { get; set; }
    public bool? Overdue { get; set; }
    public string? AutoVerifyResult { get; set; }
}

public class ImprovementPlanIdInput
{
    public long Id { get; set; }
    public string? Comment { get; set; }
}

public class ImprovementVerificationInput
{
    public long Id { get; set; }
    public string? VerifyResult { get; set; }
    public string? VerifyValue { get; set; }
    public string? VerifyRemark { get; set; }
}

public class ImprovementActionItemUpdateInput
{
    public long Id { get; set; }
    public int Index { get; set; }
    public long? ActionId { get; set; }
    public string? Status { get; set; }
    public string? CompletedAt { get; set; }
    public long? OwnerUserId { get; set; }
    public string? Owner { get; set; }
    public string? ProofRemark { get; set; }
}

public class ImprovementVerificationActionNode
{
    public int Index { get; set; }
    public string Content { get; set; } = "";
    public string? Owner { get; set; }
    public string? DueDate { get; set; }
    public string Status { get; set; } = "pending";
    public string StatusLabel { get; set; } = "未开始";
    public string? CompletedAt { get; set; }
}

public class ImprovementVerificationTrendRow
{
    public DateTime BizDate { get; set; }
    public decimal? MetricValue { get; set; }
    public decimal? TargetValue { get; set; }
}