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

using System.Text.Json;
using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Dto.SmartOps;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Infrastructure.FormulaExpr;
using Microsoft.AspNetCore.Http;
using MiniExcelLibs;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Controllers;

[ApiController]
[Route("api/[controller]")]
[AllowAnonymous]
[NonUnify]
public class AdoSmartOpsKpiMasterController : ControllerBase
{
    private readonly ISqlSugarClient _db;

    public AdoSmartOpsKpiMasterController(ISqlSugarClient db)
    {
        _db = db;
    }

    /// <summary>获取树形结构（左侧树用）</summary>
    [HttpGet("tree")]
    public async Task<IActionResult> GetTree([FromQuery] string? moduleCode = null, [FromQuery] string? keyword = null)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var q = _db.Queryable<AdoSmartOpsKpiMaster>().Where(x => x.TenantId == tenantId);
        if (!string.IsNullOrWhiteSpace(moduleCode))
            q = q.Where(x => x.ModuleCode == moduleCode);
        if (!string.IsNullOrWhiteSpace(keyword))
            q = q.Where(x => x.MetricName.Contains(keyword) || x.MetricCode.Contains(keyword));

        var all = await q.OrderBy(x => x.SortNo).ToListAsync();
        var nodes = all.Select(x => new KpiMasterTreeNodeDto
        {
            Id = x.Id,
            MetricCode = x.MetricCode,
            ModuleCode = x.ModuleCode,
            MetricLevel = x.MetricLevel,
            MetricName = x.MetricName,
            ParentId = x.ParentId,
            SortNo = x.SortNo,
            IsEnabled = x.IsEnabled
        }).ToList();

        if (!string.IsNullOrWhiteSpace(keyword))
        {
            var matchIds = new HashSet<long>(nodes.Select(n => n.Id));
            var allForAncestor = await _db.Queryable<AdoSmartOpsKpiMaster>()
                .Where(x => x.TenantId == tenantId)
                .WhereIF(!string.IsNullOrWhiteSpace(moduleCode), x => x.ModuleCode == moduleCode)
                .ToListAsync();
            var byId = allForAncestor.ToDictionary(x => x.Id);
            var needed = new HashSet<long>(matchIds);
            foreach (var id in matchIds)
            {
                var cur = byId.GetValueOrDefault(id);
                while (cur?.ParentId != null && needed.Add(cur.ParentId.Value))
                    cur = byId.GetValueOrDefault(cur.ParentId.Value);
            }
            var extra = allForAncestor.Where(x => needed.Contains(x.Id) && !matchIds.Contains(x.Id));
            foreach (var x in extra)
                nodes.Add(new KpiMasterTreeNodeDto
                {
                    Id = x.Id, MetricCode = x.MetricCode, ModuleCode = x.ModuleCode,
                    MetricLevel = x.MetricLevel, MetricName = x.MetricName,
                    ParentId = x.ParentId, SortNo = x.SortNo, IsEnabled = x.IsEnabled
                });
        }

        var tree = BuildTree(nodes);
        return Ok(tree);
    }

    /// <summary>获取单条详情（右侧面板用）</summary>
    [HttpGet("{id:long}")]
    public async Task<IActionResult> GetDetail(long id)
    {
        var e = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == id);
        if (e == null) return NotFound();

        string? parentName = null;
        if (e.ParentId.HasValue)
        {
            var p = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == e.ParentId.Value);
            parentName = p?.MetricName;
        }

        return Ok(new KpiMasterDetailDto
        {
            Id = e.Id, MetricCode = e.MetricCode, ModuleCode = e.ModuleCode,
            MetricLevel = e.MetricLevel, ParentId = e.ParentId, ParentName = parentName,
            MetricName = e.MetricName, Description = e.Description, Formula = e.Formula,
            CalcRule = e.CalcRule,
            FormulaExpr = e.FormulaExpr, FormulaPreview = e.FormulaPreview, FormulaRefs = e.FormulaRefs,
            DataSource = e.DataSource, StatFrequency = e.StatFrequency,
            Department = e.Department, DopFields = e.DopFields, Unit = e.Unit,
            Direction = e.Direction,
            YellowThreshold = e.YellowThreshold, RedThreshold = e.RedThreshold,
            IsHomePage = e.IsHomePage, SortNo = e.SortNo,
            Remark = e.Remark, IsEnabled = e.IsEnabled, TenantId = e.TenantId.GetValueOrDefault(),
            CreatedAt = e.CreatedAt, UpdatedAt = e.UpdatedAt
        });
    }

    /// <summary>新增指标（MetricCode 自动生成）</summary>
    [HttpPost]
    public async Task<IActionResult> Create([FromBody] KpiMasterUpsertDto dto)
    {
        if (string.IsNullOrWhiteSpace(dto.MetricName))
            return BadRequest(new { message = "指标名称不能为空" });
        if (dto.MetricLevel < 1 || dto.MetricLevel > 4)
            return BadRequest(new { message = "层级必须为 1/2/3/4" });
        if (dto.MetricLevel > 1 && dto.ParentId == null)
            return BadRequest(new { message = "L2/L3/L4 必须指定父指标" });

        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);

        // Q4-C：对 FormulaExpr 做解析 + 校验；启用指标硬校验，否则仅警告
        var fxResult = await ProcessFormulaAsync(tenantId, dto.FormulaExpr, dto.IsEnabled);
        if (fxResult.BlockSave)
            return BadRequest(new { message = "公式校验失败", errors = fxResult.Errors });

        var code = await GenerateMetricCodeAsync(dto.ModuleCode, dto.MetricLevel, tenantId);

        var entity = new AdoSmartOpsKpiMaster
        {
            MetricCode = code,
            ModuleCode = dto.ModuleCode,
            MetricLevel = dto.MetricLevel,
            ParentId = dto.ParentId,
            MetricName = dto.MetricName,
            Description = dto.Description,
            Formula = dto.Formula,
            CalcRule = dto.CalcRule,
            FormulaExpr = dto.FormulaExpr,
            FormulaPreview = fxResult.Preview,
            FormulaRefs = fxResult.RefsJson,
            DataSource = dto.DataSource,
            StatFrequency = dto.StatFrequency,
            Department = dto.Department,
            DopFields = dto.DopFields,
            Unit = dto.Unit,
            Direction = dto.Direction,
            YellowThreshold = dto.YellowThreshold,
            RedThreshold = dto.RedThreshold,
            IsHomePage = dto.IsHomePage,
            SortNo = dto.SortNo,
            Remark = dto.Remark,
            IsEnabled = dto.IsEnabled,
            TenantId = tenantId,
            CreatedAt = DateTime.Now
        };

        var newId = await _db.Insertable(entity).ExecuteReturnBigIdentityAsync();
        entity.Id = newId;
        return Ok(new
        {
            id = newId,
            metricCode = code,
            formulaPreview = fxResult.Preview,
            formulaWarnings = fxResult.Warnings
        });
    }

    /// <summary>编辑指标</summary>
    [HttpPut("{id:long}")]
    public async Task<IActionResult> Update(long id, [FromBody] KpiMasterUpsertDto dto)
    {
        var entity = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == id);
        if (entity == null) return NotFound();

        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        if (dto.MetricLevel < 1 || dto.MetricLevel > 4)
            return BadRequest(new { message = "层级必须为 1/2/3/4" });
        if (dto.MetricLevel > 1 && dto.ParentId == null)
            return BadRequest(new { message = "L2/L3/L4 必须指定父指标" });

        // Q4-C：公式解析 + 校验
        var fxResult = await ProcessFormulaAsync(tenantId, dto.FormulaExpr, dto.IsEnabled, excludeKpiId: id);
        if (fxResult.BlockSave)
            return BadRequest(new { message = "公式校验失败", errors = fxResult.Errors });

        entity.ModuleCode = dto.ModuleCode;
        entity.MetricLevel = dto.MetricLevel;
        entity.ParentId = dto.ParentId;
        entity.MetricName = dto.MetricName;
        entity.Description = dto.Description;
        entity.Formula = dto.Formula;
        entity.CalcRule = dto.CalcRule;
        entity.FormulaExpr = dto.FormulaExpr;
        entity.FormulaPreview = fxResult.Preview;
        entity.FormulaRefs = fxResult.RefsJson;
        entity.DataSource = dto.DataSource;
        entity.StatFrequency = dto.StatFrequency;
        entity.Department = dto.Department;
        entity.DopFields = dto.DopFields;
        entity.Unit = dto.Unit;
        entity.Direction = dto.Direction;
        entity.YellowThreshold = dto.YellowThreshold;
        entity.RedThreshold = dto.RedThreshold;
        entity.IsHomePage = dto.IsHomePage;
        entity.SortNo = dto.SortNo;
        entity.Remark = dto.Remark;
        entity.IsEnabled = dto.IsEnabled;
        entity.UpdatedAt = DateTime.Now;

        await _db.Updateable(entity).ExecuteCommandAsync();
        return Ok(new
        {
            ok = true,
            formulaPreview = fxResult.Preview,
            formulaWarnings = fxResult.Warnings
        });
    }

    /// <summary>删除指标（含子节点级联删除）</summary>
    [HttpDelete("{id:long}")]
    public async Task<IActionResult> Delete(long id)
    {
        var entity = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == id);
        if (entity == null) return NotFound();

        var idsToDelete = new List<long> { id };
        await CollectChildIds(id, idsToDelete);

        await _db.Deleteable<AdoSmartOpsKpiMaster>().Where(x => idsToDelete.Contains(x.Id)).ExecuteCommandAsync();
        return Ok(new { ok = true, deletedCount = idsToDelete.Count });
    }

    /// <summary>启用/禁用</summary>
    [HttpPatch("{id:long}/toggle-enabled")]
    public async Task<IActionResult> ToggleEnabled(long id)
    {
        var entity = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == id);
        if (entity == null) return NotFound();

        entity.IsEnabled = !entity.IsEnabled;
        entity.UpdatedAt = DateTime.Now;
        await _db.Updateable(entity).UpdateColumns(x => new { x.IsEnabled, x.UpdatedAt }).ExecuteCommandAsync();
        return Ok(new { ok = true, isEnabled = entity.IsEnabled });
    }

    /// <summary>批量更新排序</summary>
    [HttpPut("sort")]
    public async Task<IActionResult> UpdateSort([FromBody] List<KpiMasterSortItemDto> items)
    {
        if (items == null || items.Count == 0)
            return BadRequest(new { message = "排序列表不能为空" });

        foreach (var item in items)
        {
            await _db.Updateable<AdoSmartOpsKpiMaster>()
                .SetColumns(x => new AdoSmartOpsKpiMaster { SortNo = item.SortNo, UpdatedAt = DateTime.Now })
                .Where(x => x.Id == item.Id)
                .ExecuteCommandAsync();
        }
        return Ok(new { ok = true });
    }

    /// <summary>拖拽移动（变更 ParentId + SortNo）</summary>
    [HttpPut("{id:long}/move")]
    public async Task<IActionResult> Move(long id, [FromBody] KpiMasterMoveDto dto)
    {
        var entity = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == id);
        if (entity == null) return NotFound();

        if (dto.NewParentId.HasValue)
        {
            var parent = await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.Id == dto.NewParentId.Value);
            if (parent == null) return BadRequest(new { message = "目标父节点不存在" });
            if (parent.MetricLevel >= entity.MetricLevel)
                return BadRequest(new { message = "不能移动到同级或更低层级下" });
            entity.ParentId = dto.NewParentId;
            entity.MetricLevel = parent.MetricLevel + 1;
        }
        else
        {
            entity.ParentId = null;
            entity.MetricLevel = 1;
        }

        entity.SortNo = dto.NewSortNo;
        entity.UpdatedAt = DateTime.Now;
        await _db.Updateable(entity).ExecuteCommandAsync();
        return Ok(new { ok = true });
    }

    /// <summary>导入 Demo 数据（上传 Excel，解析后生成 30 天日值写入 DB）</summary>
    [HttpPost("import-demo-data")]
    public async Task<IActionResult> ImportDemoData(IFormFile file)
    {
        if (file == null || file.Length == 0)
            return BadRequest(new { message = "请上传 Excel 文件" });

        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const int DAYS = 30;
        var today = DateTime.Today;
        var startDate = today.AddDays(-(DAYS - 1));

        using var stream = new MemoryStream();
        await file.CopyToAsync(stream);
        stream.Position = 0;

        var rows = stream.Query(useHeaderRow: true).ToList();

        var masters = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId)
            .ToListAsync();
        var masterByCode = masters.ToDictionary(m => m.MetricCode, m => m);

        var excelOverrides = new Dictionary<string, DemoImportRow>();
        foreach (IDictionary<string, object> row in rows)
        {
            object? Val(string key) => row.TryGetValue(key, out var v) ? v : null;

            var code = Val("指标编码")?.ToString()?.Trim();
            if (string.IsNullOrEmpty(code) || !code.StartsWith("S")) continue;
            if (!masterByCode.TryGetValue(code, out var master)) continue;

            decimal? target = TryDecimal(Val("目标值 ★"));
            decimal? actual = TryDecimal(Val("实际值 ★"));
            decimal? yellowTh = TryDecimal(Val("黄色阈值% ★"));
            decimal? redTh = TryDecimal(Val("红色阈值% ★"));

            excelOverrides[code] = new DemoImportRow
            {
                MetricCode = code,
                Master = master,
                Target = target ?? 0,
                Actual = actual ?? 0,
                HasTarget = target != null,
                HasActual = actual != null,
                YellowThreshold = yellowTh,
                RedThreshold = redTh,
            };
        }

        var rng = new Random(42);
        var parsed = new List<DemoImportRow>();
        foreach (var m in masters)
        {
            if (TryGetFixedS1DiagnosisDemoValue(m.MetricCode, out var fixedValue))
            {
                parsed.Add(new DemoImportRow
                {
                    MetricCode = m.MetricCode, Master = m,
                    Target = fixedValue.Target, Actual = fixedValue.Actual,
                    HasTarget = true, HasActual = true,
                    YellowThreshold = null, RedThreshold = null,
                    FixedStatus = fixedValue.Status,
                });
            }
            else if (excelOverrides.TryGetValue(m.MetricCode, out var ov))
            {
                var tgt = ov.HasTarget ? ov.Target : ov.HasActual ? ov.Actual * 1.05m : AutoTarget(m, rng);
                var act = ov.HasActual ? ov.Actual : ov.HasTarget ? ov.Target * AutoActualFactor(m, rng) : AutoActual(AutoTarget(m, rng), m, rng);
                parsed.Add(new DemoImportRow
                {
                    MetricCode = m.MetricCode, Master = m,
                    Target = tgt, Actual = act,
                    HasTarget = true, HasActual = true,
                    YellowThreshold = ov.YellowThreshold, RedThreshold = ov.RedThreshold,
                });
            }
            else
            {
                var tgt = AutoTarget(m, rng);
                var act = AutoActual(tgt, m, rng);
                parsed.Add(new DemoImportRow
                {
                    MetricCode = m.MetricCode, Master = m,
                    Target = tgt, Actual = act,
                    HasTarget = true, HasActual = true,
                    YellowThreshold = null, RedThreshold = null,
                });
            }
        }

        foreach (var p in parsed)
        {
            if (p.YellowThreshold != null || p.RedThreshold != null)
            {
                var m = p.Master;
                if (p.YellowThreshold != null) m.YellowThreshold = p.YellowThreshold;
                if (p.RedThreshold != null) m.RedThreshold = p.RedThreshold;
                m.UpdatedAt = DateTime.Now;
                await _db.Updateable(m)
                    .UpdateColumns(x => new { x.YellowThreshold, x.RedThreshold, x.UpdatedAt })
                    .ExecuteCommandAsync();
            }
        }

        var l1 = parsed.Where(p => p.Master.MetricLevel == 1).ToList();
        var l2 = parsed.Where(p => p.Master.MetricLevel == 2).ToList();
        var l3 = parsed.Where(p => p.Master.MetricLevel == 3).ToList();
        var l4 = parsed.Where(p => p.Master.MetricLevel == 4).ToList();

        AidopTenantMigration.EnsureKpiValueL4Table(_db);
        await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM ado_s9_kpi_value_l1_day WHERE tenant_id = {tenantId}");
        await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM ado_s9_kpi_value_l2_day WHERE tenant_id = {tenantId}");
        await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM ado_s9_kpi_value_l3_day WHERE tenant_id = {tenantId}");
        await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM ado_s9_kpi_value_l4_day WHERE tenant_id = {tenantId}");

        async Task InsertLevel(List<DemoImportRow> items, string table)
        {
            if (items.Count == 0) return;
            var maxIdValue = await _db.Ado.GetScalarAsync($"SELECT COALESCE(MAX(id), 0) FROM `{table}`");
            var nextId = Convert.ToInt64(maxIdValue);
            var levelRng = new Random(42);
            var batchValues = new List<string>();
            foreach (var p in items)
            {
                var dir = p.Master.Direction ?? "higher_is_better";
                var yTh = p.YellowThreshold ?? p.Master.YellowThreshold;
                var rTh = p.RedThreshold ?? p.Master.RedThreshold;
                decimal prevVal = p.Actual;

                for (int i = 0; i < DAYS; i++)
                {
                    nextId++;
                    var d = startDate.AddDays(i);
                    decimal val;
                    if (i == DAYS - 1)
                        val = p.Actual;
                    else
                    {
                        var noise = (decimal)(levelRng.NextDouble() * 0.06 - 0.03);
                        val = Math.Max(0, p.Actual * (1 + noise));
                    }
                    val = Math.Round(val, 4);
                    var sc = i == DAYS - 1 && !string.IsNullOrWhiteSpace(p.FixedStatus)
                        ? p.FixedStatus
                        : AidopS4KpiMerge.AchievementLevel(val, p.Target, dir, yTh, rTh);
                    var tf = "flat";
                    if (i > 0)
                    {
                        var change = prevVal == 0 ? 0 : (val - prevVal) / prevVal;
                        tf = change > 0.02m ? "up" : change < -0.02m ? "down" : "flat";
                    }
                    prevVal = val;

                    batchValues.Add(
                        $"({nextId},{tenantId},1,'{d:yyyy-MM-dd}'," +
                        $"'{p.Master.ModuleCode}','{p.MetricCode}',{val},{p.Target},'{sc}','{tf}',0,1)");
                }
            }

            const int BATCH = 500;
            for (int i = 0; i < batchValues.Count; i += BATCH)
            {
                var chunk = batchValues.Skip(i).Take(BATCH);
                var sql = $"INSERT INTO `{table}` " +
                    "(id,tenant_id,factory_id,biz_date,module_code,metric_code," +
                    "metric_value,target_value,status_color,trend_flag,is_deleted,is_active) VALUES\n" +
                    string.Join(",\n", chunk);
                await _db.Ado.ExecuteCommandAsync(sql);
            }
        }

        await InsertLevel(l1, "ado_s9_kpi_value_l1_day");
        await InsertLevel(l2, "ado_s9_kpi_value_l2_day");
        await InsertLevel(l3, "ado_s9_kpi_value_l3_day");
        await InsertLevel(l4, "ado_s9_kpi_value_l4_day");

        // ---- 同步 LayoutItem / HomeModule（只补不改，保护已有布局配置） ----
        // 背景：
        //   seed_layout_items_all_modules.py 的语义是"(tenant,module) 已有布局就 skip"；
        //   之后在 KpiMaster 里新增某个 L1/L2 指标时，LayoutItem 不会自动跟进，九宫格 / 详情会少条目。
        //   这里按本次 Demo 导入涉及的指标做"缺啥补啥"的幂等同步，不覆盖任何已有行。
        const long layoutFactoryId = 1;
        var existingLayout = await _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == layoutFactoryId)
            .ToListAsync();
        var layoutKey = new HashSet<string>(
            existingLayout.Select(x => $"{x.ModuleCode}|{x.MetricCode}|{x.MetricLevel}"),
            StringComparer.OrdinalIgnoreCase);

        var now = DateTime.Now;
        var toInsertLayout = new List<AdoSmartOpsLayoutItem>();
        foreach (var p in parsed)
        {
            var m = p.Master;
            var key = $"{m.ModuleCode}|{m.MetricCode}|{m.MetricLevel}";
            if (layoutKey.Contains(key)) continue;
            string? zone = null;
            if (m.MetricLevel >= 2)
            {
                var nm = m.MetricName ?? "";
                if (nm.Contains("周期") || nm.Contains("人效")) zone = "left";
                else if (nm.Contains("满足率") || nm.Contains("周转")) zone = "right";
                else zone = (m.SortNo % 2 == 1) ? "left" : "right";
            }
            toInsertLayout.Add(new AdoSmartOpsLayoutItem
            {
                TenantId = tenantId,
                FactoryId = layoutFactoryId,
                ModuleCode = m.ModuleCode,
                RowId = $"{m.ModuleCode}-L{m.MetricLevel}-{m.MetricCode}",
                MetricLevel = m.MetricLevel,
                MetricCode = m.MetricCode,
                DisplayName = null,
                SortNo = m.SortNo,
                ParentRowId = null,
                FormulaText = null,
                PanelZone = zone,
                IsEnabled = 1,
                UpdateTime = now,
            });
            layoutKey.Add(key);
        }
        if (toInsertLayout.Count > 0)
            await _db.Insertable(toInsertLayout).ExecuteCommandAsync();

        var moduleCodes = parsed.Select(p => p.Master.ModuleCode).Distinct().ToList();
        var existingHm = (await _db.Queryable<AdoSmartOpsHomeModule>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == layoutFactoryId)
            .ToListAsync())
            .Select(x => x.ModuleCode)
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
        var toInsertHm = new List<AdoSmartOpsHomeModule>();
        foreach (var mc in moduleCodes)
        {
            if (string.IsNullOrWhiteSpace(mc) || existingHm.Contains(mc)) continue;
            toInsertHm.Add(new AdoSmartOpsHomeModule
            {
                TenantId = tenantId,
                FactoryId = layoutFactoryId,
                ModuleCode = mc,
                LayoutPattern = "card_grid",
                UpdateTime = now,
            });
            existingHm.Add(mc);
        }
        if (toInsertHm.Count > 0)
            await _db.Insertable(toInsertHm).ExecuteCommandAsync();

        var fromExcel = excelOverrides.Values.Count(v => v.HasTarget || v.HasActual);
        return Ok(new
        {
            ok = true,
            message = $"导入成功：Excel 填入 {fromExcel} 条，自动补全 {parsed.Count - fromExcel} 条，共 {parsed.Count} 条指标 × {DAYS} 天；补齐布局行 {toInsertLayout.Count} 条、模块模板 {toInsertHm.Count} 条",
            detail = new
            {
                l1 = l1.Count,
                l2 = l2.Count,
                l3 = l3.Count,
                l4 = l4.Count,
                days = DAYS,
                fromExcel,
                autoGen = parsed.Count - fromExcel,
                layoutInserted = toInsertLayout.Count,
                homeModuleInserted = toInsertHm.Count
            }
        });
    }

    /// <summary>下载 Demo 数据 Excel 模板</summary>
    [HttpGet("demo-template")]
    public async Task<IActionResult> DownloadDemoTemplate()
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var masters = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId)
            .OrderBy(x => x.ModuleCode).OrderBy(x => x.MetricLevel).OrderBy(x => x.SortNo)
            .ToListAsync();

        var templateRows = masters.Select(m => new Dictionary<string, object>
        {
            ["指标编码"] = m.MetricCode,
            ["模块"] = m.ModuleCode,
            ["层级"] = $"L{m.MetricLevel}",
            ["指标名称"] = m.MetricName,
            ["单位"] = m.Unit ?? "",
            ["方向"] = m.Direction == "lower_is_better" ? "越低越好" : "越高越好",
            ["目标值 ★"] = (object)null!,
            ["实际值 ★"] = (object)null!,
            ["黄色阈值% ★"] = (object)(m.YellowThreshold ?? (m.Direction == "lower_is_better" ? 110m : 95m)),
            ["红色阈值% ★"] = (object)(m.RedThreshold ?? (m.Direction == "lower_is_better" ? 120m : 80m)),
        }).ToList();

        var ms = new MemoryStream();
        await ms.SaveAsAsync(templateRows);
        ms.Position = 0;

        return new FileStreamResult(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = $"demo_data_template_{DateTime.Now:yyyyMMdd}.xlsx"
        };
    }

    private static decimal? TryDecimal(object? val)
    {
        if (val == null) return null;
        var s = val.ToString()?.Trim();
        if (string.IsNullOrEmpty(s)) return null;
        return decimal.TryParse(s, out var d) ? d : null;
    }

    private sealed class DemoImportRow
    {
        public string MetricCode { get; set; } = "";
        public AdoSmartOpsKpiMaster Master { get; set; } = null!;
        public decimal Target { get; set; }
        public decimal Actual { get; set; }
        public bool HasTarget { get; set; }
        public bool HasActual { get; set; }
        public decimal? YellowThreshold { get; set; }
        public decimal? RedThreshold { get; set; }
        public string? FixedStatus { get; set; }
    }

    private static decimal AutoTarget(AdoSmartOpsKpiMaster m, Random rng)
    {
        var unit = (m.Unit ?? "").Trim();
        if (unit == "%") return Math.Round((decimal)(rng.NextDouble() * 18 + 80), 2);
        if (unit == "天") return Math.Round((decimal)(rng.NextDouble() * 13 + 2), 2);
        if (unit.Contains("PPM")) return Math.Round((decimal)(rng.NextDouble() * 5000 + 3000), 0);
        if (unit.Contains("/")) return Math.Round((decimal)(rng.NextDouble() * 250 + 50), 2);
        if (unit is "个" or "颗/人" or "家/人" or "小时" or "颗/人" or "人")
            return Math.Round((decimal)(rng.NextDouble() * 195 + 5), 0);
        return Math.Round((decimal)(rng.NextDouble() * 150 + 50), 2);
    }

    private static decimal AutoActual(decimal target, AdoSmartOpsKpiMaster m, Random rng)
    {
        var factor = (m.Direction == "lower_is_better")
            ? (decimal)(rng.NextDouble() * 0.35 + 0.90)
            : (decimal)(rng.NextDouble() * 0.23 + 0.82);
        return Math.Round(target * factor, 4);
    }

    private static bool TryGetFixedS1DiagnosisDemoValue(string? metricCode, out (decimal Target, decimal Actual, string Status) value)
    {
        value = default;
        if (string.IsNullOrWhiteSpace(metricCode)) return false;
        var fixedValues = new Dictionary<string, (decimal Target, decimal Actual, string Status)>(StringComparer.OrdinalIgnoreCase)
        {
            ["S1_L2_004"] = (5m, 6.2m, "red"),
            ["S1_L3_201"] = (8m, 14.4m, "red"),
            ["S1_L3_202"] = (12m, 14.2m, "yellow"),
            ["S1_L3_203"] = (8m, 7m, "green"),
            ["S1_L3_204"] = (10m, 12m, "yellow"),
            ["S1_L3_205"] = (2m, 2m, "green"),
            ["S1_L4_201"] = (2m, 2m, "green"),
            ["S1_L4_202"] = (3m, 9.2m, "red"),
            ["S1_L4_203"] = (1m, 1.2m, "yellow"),
            ["S1_L4_204"] = (2m, 2m, "green"),
        };
        return fixedValues.TryGetValue(metricCode, out value);
    }

    private static decimal AutoActualFactor(AdoSmartOpsKpiMaster m, Random rng)
    {
        return (m.Direction == "lower_is_better")
            ? (decimal)(rng.NextDouble() * 0.35 + 0.90)
            : (decimal)(rng.NextDouble() * 0.23 + 0.82);
    }

    // ── 私有方法 ──

    private async Task<string> GenerateMetricCodeAsync(string moduleCode, int level, long tenantId)
    {
        var prefix = $"{moduleCode}_L{level}_";
        var maxCode = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == moduleCode && x.MetricLevel == level)
            .OrderByDescending(x => x.MetricCode)
            .Select(x => x.MetricCode)
            .FirstAsync();

        var seq = 1;
        if (!string.IsNullOrEmpty(maxCode) && maxCode.StartsWith(prefix) && int.TryParse(maxCode[prefix.Length..], out var n))
            seq = n + 1;

        return $"{prefix}{seq:D3}";
    }

    private async Task CollectChildIds(long parentId, List<long> result)
    {
        var children = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.ParentId == parentId)
            .Select(x => x.Id)
            .ToListAsync();
        foreach (var cid in children)
        {
            result.Add(cid);
            await CollectChildIds(cid, result);
        }
    }

    private static List<KpiMasterTreeNodeDto> BuildTree(List<KpiMasterTreeNodeDto> flat)
    {
        var byId = flat.ToDictionary(n => n.Id);
        var roots = new List<KpiMasterTreeNodeDto>();
        foreach (var n in flat)
        {
            if (n.ParentId.HasValue && byId.TryGetValue(n.ParentId.Value, out var parent))
                parent.Children.Add(n);
            else
                roots.Add(n);
        }
        SortChildren(roots);
        return roots;
    }

    private static void SortChildren(List<KpiMasterTreeNodeDto> nodes)
    {
        nodes.Sort((a, b) => a.SortNo.CompareTo(b.SortNo));
        foreach (var n in nodes)
        {
            if (n.Children.Count > 0)
                SortChildren(n.Children);
        }
    }

    // ── 公式解析 / 校验 / 预览：第三批新增 ──

    private sealed class FormulaProcessResult
    {
        public string? Preview { get; init; }
        public string? RefsJson { get; init; }
        public List<string> Errors { get; init; } = new();
        public List<string> Warnings { get; init; } = new();
        public bool BlockSave { get; init; }
    }

    private async Task<FormulaProcessResult> ProcessFormulaAsync(
        long tenantId, string? expr, bool isEnabled, long? excludeKpiId = null)
    {
        var parsed = FormulaParser.Parse(expr);
        if (parsed.IsEmpty)
            return new FormulaProcessResult();

        var validation = await FormulaValidator.ValidateAsync(_db, tenantId, parsed, isEnabled, excludeKpiId);

        // 启用指标 → 硬校验；否则仅警告
        if (isEnabled && validation.HasErrors)
        {
            return new FormulaProcessResult
            {
                Errors = validation.Errors,
                Warnings = validation.Warnings,
                BlockSave = true,
            };
        }

        var preview = await FormulaPreviewBuilder.BuildAsync(_db, tenantId, expr, parsed);
        var refsJson = JsonSerializer.Serialize(new
        {
            metrics = parsed.MetricRefs,
            facts = parsed.FactRefs,
        });

        return new FormulaProcessResult
        {
            Preview = preview,
            RefsJson = refsJson,
            Errors = validation.Errors,
            Warnings = validation.Warnings,
            BlockSave = false,
        };
    }

    /// <summary>实时预览公式（不落库）。前端编辑器失焦/定时调用。</summary>
    [HttpPost("preview-formula")]
    public async Task<IActionResult> PreviewFormula([FromBody] PreviewFormulaIn input)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var parsed = FormulaParser.Parse(input?.FormulaExpr);
        if (parsed.IsEmpty)
            return Ok(new { preview = "", metrics = new List<string>(), facts = new List<string>(), errors = Array.Empty<string>(), warnings = Array.Empty<string>() });

        var validation = await FormulaValidator.ValidateAsync(
            _db, tenantId, parsed, isEnabled: input?.IsEnabled ?? true, excludeKpiId: input?.ExcludeKpiId);
        var preview = await FormulaPreviewBuilder.BuildAsync(_db, tenantId, input!.FormulaExpr, parsed);

        return Ok(new
        {
            preview,
            metrics = parsed.MetricRefs,
            facts = parsed.FactRefs,
            errors = validation.Errors,
            warnings = validation.Warnings,
        });
    }

    public sealed class PreviewFormulaIn
    {
        public string? FormulaExpr { get; set; }
        public bool IsEnabled { get; set; } = true;
        public long? ExcludeKpiId { get; set; }
    }
}