AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/AidopKanbanController.S4.cs

using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Controllers;

public partial class AidopKanbanController
{
    public const int MaxL1PerModule = 8;

    /// <summary>S4 指标列表（从 KpiMaster 读取，可按层级过滤）。</summary>
    [HttpGet("s4-metric-catalog")]
    public async Task<IActionResult> GetS4MetricCatalog([FromQuery] int? metricLevel = null)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var q = _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == "S4" && x.IsEnabled);
        if (metricLevel is > 0)
            q = q.Where(x => x.MetricLevel == metricLevel);
        var list = await q.OrderBy(x => x.SortNo).ToListAsync();
        return Ok(list.Select(x => new
        {
            id = x.Id,
            metricCode = x.MetricCode,
            moduleCode = x.ModuleCode,
            metricLevel = x.MetricLevel,
            defaultName = x.MetricName,
            unit = x.Unit,
            direction = x.Direction,
            sortHint = x.SortNo,
            parentId = x.ParentId,
            description = x.Description,
            formula = x.Formula,
            calcRule = x.CalcRule,
            dataSource = x.DataSource,
            statFrequency = x.StatFrequency,
            department = x.Department
        }));
    }

    /// <summary>S4 运营布局（L1/L2/L3 + 九宫格模块行）。</summary>
    [HttpGet("s4-operation-layout")]
    public async Task<IActionResult> GetS4OperationLayout([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var hm = (await _db.Queryable<AdoSmartOpsHomeModule>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4")
            .ToListAsync()).FirstOrDefault();
        var items = await _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4" && x.IsEnabled == 1)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var kpi = await _db.Queryable<AdoSmartOpsKpiMaster>().Where(x => x.TenantId == tenantId && x.ModuleCode == "S4").ToListAsync();
        var kpiByCode = kpi.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

        object Row(AdoSmartOpsLayoutItem r)
        {
            kpiByCode.TryGetValue(r.MetricCode, out var k);
            return new
            {
                rowId = r.RowId,
                metricCode = r.MetricCode,
                metricLevel = r.MetricLevel,
                displayName = string.IsNullOrWhiteSpace(r.DisplayName) ? k?.MetricName : r.DisplayName,
                sortNo = r.SortNo,
                parentRowId = r.ParentRowId,
                formulaText = r.FormulaText,
                panelZone = r.PanelZone,
                unit = k?.Unit,
                direction = k?.Direction
            };
        }

        return Ok(new
        {
            layoutPattern = hm?.LayoutPattern ?? "card_grid",
            l1 = items.Where(x => x.MetricLevel == 1).Select(Row).ToList(),
            l2 = items.Where(x => x.MetricLevel >= 2).Select(Row).ToList()
        });
    }

    public class S4LayoutSaveDto
    {
        public List<S4LayoutL1Dto>? L1 { get; set; }
        public List<S4LayoutL2Dto>? L2 { get; set; }
    }

    public class S4LayoutL1Dto
    {
        public string? RowId { get; set; }
        public string? MetricCode { get; set; }
        public string? DisplayName { get; set; }
        public int SortNo { get; set; }
        public string? FormulaText { get; set; }
    }

    public class S4LayoutL2Dto
    {
        public string? RowId { get; set; }
        public string? MetricCode { get; set; }
        public string? DisplayName { get; set; }
        public int SortNo { get; set; }
        public string? ParentRowId { get; set; }
        public string? FormulaText { get; set; }
        public string? PanelZone { get; set; }
    }

    /// <summary>保存 S4 布局并同步更新 KpiMaster；L1 最多 8 条。</summary>
    [HttpPut("s4-operation-layout")]
    public async Task<IActionResult> PutS4OperationLayout([FromBody] S4LayoutSaveDto? body, [FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        if (body?.L1 == null || body.L2 == null)
            return BadRequest(new { message = "L1/L2 不能为空" });
        if (body.L1.Count > MaxL1PerModule)
            return BadRequest(new { message = $"L1 最多 {MaxL1PerModule} 个" });

        var kpiAll = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == "S4" && x.IsEnabled)
            .ToListAsync();
        var kpiByCode = kpiAll.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

        foreach (var r in body.L1)
        {
            if (string.IsNullOrWhiteSpace(r.MetricCode) || !kpiByCode.TryGetValue(r.MetricCode.Trim(), out var k) || k.MetricLevel != 1)
                return BadRequest(new { message = $"无效的 L1 指标: {r.MetricCode}" });
        }

        var l1Ids = new HashSet<string>(StringComparer.Ordinal);
        foreach (var r in body.L1)
        {
            var rid = string.IsNullOrWhiteSpace(r.RowId) ? $"S4-L1-{r.MetricCode!.Trim()}" : r.RowId.Trim();
            if (!l1Ids.Add(rid))
                return BadRequest(new { message = $"重复的 L1 RowId: {rid}" });
        }

        foreach (var r in body.L2)
        {
            if (string.IsNullOrWhiteSpace(r.MetricCode) || !kpiByCode.TryGetValue(r.MetricCode.Trim(), out var k) || k.MetricLevel < 2)
                return BadRequest(new { message = $"无效的 L2/L3/L4 指标: {r.MetricCode}" });
        }

        var now = DateTime.Now;
        try
        {
            _db.Ado.BeginTran();

            // -- 保存 LayoutItem --
            await _db.Deleteable<AdoSmartOpsLayoutItem>()
                .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4")
                .ExecuteCommandAsync();

            var rows = new List<AdoSmartOpsLayoutItem>();
            foreach (var r in body.L1)
            {
                var rid = string.IsNullOrWhiteSpace(r.RowId) ? $"S4-L1-{r.MetricCode!.Trim()}" : r.RowId.Trim();
                rows.Add(new AdoSmartOpsLayoutItem
                {
                    TenantId = tenantId, FactoryId = factoryId, ModuleCode = "S4",
                    RowId = rid, MetricLevel = 1, MetricCode = r.MetricCode!.Trim(),
                    DisplayName = string.IsNullOrWhiteSpace(r.DisplayName) ? null : r.DisplayName.Trim(),
                    SortNo = r.SortNo, ParentRowId = null,
                    FormulaText = string.IsNullOrWhiteSpace(r.FormulaText) ? null : r.FormulaText.Trim(),
                    PanelZone = null, IsEnabled = 1, UpdateTime = now
                });
            }

            foreach (var r in body.L2)
            {
                var code = r.MetricCode!.Trim();
                var level = kpiByCode.TryGetValue(code, out var kk) ? kk.MetricLevel : 2;
                var prefix = $"S4-L{level}-";
                var rid = string.IsNullOrWhiteSpace(r.RowId) ? $"{prefix}{code}" : r.RowId.Trim();
                var p = string.IsNullOrWhiteSpace(r.ParentRowId) ? null : r.ParentRowId.Trim();
                rows.Add(new AdoSmartOpsLayoutItem
                {
                    TenantId = tenantId, FactoryId = factoryId, ModuleCode = "S4",
                    RowId = rid, MetricLevel = level, MetricCode = code,
                    DisplayName = string.IsNullOrWhiteSpace(r.DisplayName) ? null : r.DisplayName.Trim(),
                    SortNo = r.SortNo, ParentRowId = p,
                    FormulaText = string.IsNullOrWhiteSpace(r.FormulaText) ? null : r.FormulaText,
                    PanelZone = string.IsNullOrWhiteSpace(r.PanelZone) ? null : r.PanelZone.Trim(),
                    IsEnabled = 1, UpdateTime = now
                });
            }

            if (rows.Count > 0)
                await _db.Insertable(rows).ExecuteCommandAsync();

            // -- 同步 KpiMaster：如果 DisplayName 有值且与 MetricName 不同，更新 MetricName --
            foreach (var r in body.L1.Concat<object>(body.L2).Cast<dynamic>())
            {
                string? code = (string?)r.MetricCode?.Trim();
                string? dn = (string?)r.DisplayName?.Trim();
                if (string.IsNullOrWhiteSpace(code) || string.IsNullOrWhiteSpace(dn)) continue;
                if (!kpiByCode.TryGetValue(code, out var kpi)) continue;
                if (!string.Equals(kpi.MetricName, dn, StringComparison.Ordinal))
                {
                    await _db.Updateable<AdoSmartOpsKpiMaster>()
                        .SetColumns(x => new AdoSmartOpsKpiMaster { MetricName = dn, UpdatedAt = now })
                        .Where(x => x.Id == kpi.Id)
                        .ExecuteCommandAsync();
                }
            }

            var hmRows = await _db.Updateable<AdoSmartOpsHomeModule>()
                .SetColumns(x => new AdoSmartOpsHomeModule { UpdateTime = now })
                .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4")
                .ExecuteCommandAsync();
            if (hmRows == 0)
            {
                await _db.Insertable(new AdoSmartOpsHomeModule
                {
                    TenantId = tenantId, FactoryId = factoryId, ModuleCode = "S4",
                    LayoutPattern = "card_grid", UpdateTime = now
                }).ExecuteCommandAsync();
            }

            _db.Ado.CommitTran();
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            return BadRequest(new { message = ex.Message });
        }

        return Ok(new { ok = true });
    }

    /// <summary>九宫格 S4：合并 L1 布局与日表。</summary>
    [HttpGet("s4-home-grid")]
    public async Task<IActionResult> GetS4HomeGrid([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var layout = await _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4" && x.IsEnabled == 1 && x.MetricLevel == 1)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var kpi = await _db.Queryable<AdoSmartOpsKpiMaster>().Where(x => x.TenantId == tenantId && x.ModuleCode == "S4").ToListAsync();
        var kpiBy = kpi.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

        var bizDate = await ResolveMaxBizDateAsync("ado_s9_kpi_value_l1_day", tenantId, factoryId);

        const string valSql = """
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue
FROM ado_s9_kpi_value_l1_day
WHERE tenant_id=@t AND factory_id=@f AND module_code='S4' AND is_deleted=0 AND biz_date=@d
""";
        var vals = await _db.Ado.SqlQueryAsync<S4ValRow>(valSql, new { t = tenantId, f = factoryId, d = bizDate });
        var valBy = vals.ToDictionary(x => x.MetricCode ?? "", StringComparer.OrdinalIgnoreCase);

        var hm = (await _db.Queryable<AdoSmartOpsHomeModule>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4")
            .ToListAsync()).FirstOrDefault();

        var items = new List<object>();
        foreach (var row in layout)
        {
            kpiBy.TryGetValue(row.MetricCode, out var k);
            valBy.TryGetValue(row.MetricCode, out var v);
            var cur = v?.MetricValue;
            var tgt = v?.TargetValue;
            var dir = k?.Direction ?? "higher_is_better";
            var level = AidopS4KpiMerge.AchievementLevel(cur, tgt, dir, k?.YellowThreshold, k?.RedThreshold);
            var gap = AidopS4KpiMerge.GapValue(cur, tgt);
            var arrow = AidopS4KpiMerge.GapArrow(gap);
            var label = FormatGapLabel(gap, k?.Unit);
            items.Add(new
            {
                rowId = row.RowId,
                metricCode = row.MetricCode,
                displayName = string.IsNullOrWhiteSpace(row.DisplayName) ? k?.MetricName : row.DisplayName,
                unit = k?.Unit ?? "",
                currentValue = cur,
                targetValue = tgt,
                gapValue = gap,
                gapLabel = label,
                gapArrow = arrow,
                achievementLevel = level,
                formulaText = row.FormulaText
            });
        }

        return Ok(new
        {
            layoutPattern = hm?.LayoutPattern ?? "card_grid",
            bizDate = bizDate.ToString("yyyy-MM-dd"),
            items
        });
    }

    /// <summary>S4 详情主 KPI：合并 L2/L3/L4 布局与日表，可按 panelZone 过滤。</summary>
    [HttpGet("s4-detail-kpis")]
    public async Task<IActionResult> GetS4DetailKpis([FromQuery] long factoryId = 1, [FromQuery] string? panelZone = null)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var q = _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == "S4" && x.IsEnabled == 1 && x.MetricLevel >= 2);
        if (!string.IsNullOrWhiteSpace(panelZone))
            q = q.Where(x => x.PanelZone == panelZone);
        var layout = await q.OrderBy(x => x.SortNo).ToListAsync();

        var kpi = await _db.Queryable<AdoSmartOpsKpiMaster>().Where(x => x.TenantId == tenantId && x.ModuleCode == "S4").ToListAsync();
        var kpiBy = kpi.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

        var bizDateL2 = await ResolveMaxBizDateAsync("ado_s9_kpi_value_l2_day", tenantId, factoryId);
        var bizDateL3 = await ResolveMaxBizDateAsync("ado_s9_kpi_value_l3_day", tenantId, factoryId);
        AidopTenantMigration.EnsureKpiValueL4Table(_db);
        var bizDateL4 = await ResolveMaxBizDateAsync("ado_s9_kpi_value_l4_day", tenantId, factoryId);
        var bizDate = new[] { bizDateL2, bizDateL3, bizDateL4 }.Max();

        const string valSqlL2 = """
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue
FROM ado_s9_kpi_value_l2_day
WHERE tenant_id=@t AND factory_id=@f AND module_code='S4' AND is_deleted=0 AND biz_date=@d
""";
        const string valSqlL3 = """
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue
FROM ado_s9_kpi_value_l3_day
WHERE tenant_id=@t AND factory_id=@f AND module_code='S4' AND is_deleted=0 AND biz_date=@d
""";
        const string valSqlL4 = """
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue
FROM ado_s9_kpi_value_l4_day
WHERE tenant_id=@t AND factory_id=@f AND module_code='S4' AND is_deleted=0 AND biz_date=@d
""";
        var valsL2 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL2, new { t = tenantId, f = factoryId, d = bizDateL2 });
        var valsL3 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL3, new { t = tenantId, f = factoryId, d = bizDateL3 });
        var valsL4 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL4, new { t = tenantId, f = factoryId, d = bizDateL4 });
        var valBy = new Dictionary<string, S4ValRow>(StringComparer.OrdinalIgnoreCase);
        foreach (var r in valsL2)
            if (!string.IsNullOrEmpty(r.MetricCode))
                valBy[r.MetricCode] = r;
        foreach (var r in valsL3)
            if (!string.IsNullOrEmpty(r.MetricCode))
                valBy[r.MetricCode] = r;
        foreach (var r in valsL4)
            if (!string.IsNullOrEmpty(r.MetricCode))
                valBy[r.MetricCode] = r;

        var items = new List<object>();
        foreach (var row in layout)
        {
            kpiBy.TryGetValue(row.MetricCode, out var k);
            valBy.TryGetValue(row.MetricCode, out var v);
            var cur = v?.MetricValue;
            var tgt = v?.TargetValue;
            var dir = k?.Direction ?? "higher_is_better";
            var level = AidopS4KpiMerge.AchievementLevel(cur, tgt, dir, k?.YellowThreshold, k?.RedThreshold);
            var gap = AidopS4KpiMerge.GapValue(cur, tgt);
            var arrow = AidopS4KpiMerge.GapArrow(gap);
            items.Add(new
            {
                rowId = row.RowId,
                metricCode = row.MetricCode,
                displayName = string.IsNullOrWhiteSpace(row.DisplayName) ? k?.MetricName : row.DisplayName,
                unit = k?.Unit ?? "",
                currentValue = cur,
                targetValue = tgt,
                gapValue = gap,
                gapLabel = FormatGapLabel(gap, k?.Unit),
                gapArrow = arrow,
                achievementLevel = level,
                formulaText = row.FormulaText,
                parentRowId = row.ParentRowId,
                panelZone = row.PanelZone
            });
        }

        return Ok(new { bizDate = bizDate.ToString("yyyy-MM-dd"), items });
    }

    private async Task<DateTime> ResolveMaxBizDateAsync(string table, long tenantId, long factoryId)
    {
        var sql = $"""
SELECT MAX(biz_date) AS MaxBiz FROM {table}
WHERE tenant_id=@t AND factory_id=@f AND module_code='S4' AND is_deleted=0
""";
        var rows = await _db.Ado.SqlQueryAsync<S4MaxDateRow>(sql, new { t = tenantId, f = factoryId });
        return rows.FirstOrDefault()?.MaxBiz ?? DateTime.Today;
    }

    private sealed class S4MaxDateRow
    {
        public DateTime? MaxBiz { get; set; }
    }

    private static string FormatGapLabel(decimal? gap, string? unit)
    {
        if (gap == null) return "—";
        var g = gap.Value;
        var sign = g >= 0 ? "+" : "";
        var s = $"{sign}{Math.Round(g, 2)}";
        if (unit == "%")
            return s + "%";
        return s;
    }

    private sealed class S4ValRow
    {
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
    }
}