AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/AidopKanbanController.Generic.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
{
    // S4.cs 中的 FormatGapLabel / ResolveMaxBizDateAsync 是 private，在同一 partial 类里可直接复用

    /// <summary>
    /// 通用运营布局（L1/L2/L3/L4）。moduleCode 指定模块；S4 也可走这个端点。
    /// </summary>
    [HttpGet("operation-layout/{moduleCode}")]
    public async Task<IActionResult> GetOperationLayoutGeneric(string moduleCode, [FromQuery] long factoryId = 1)
    {
        var mc = (moduleCode ?? "").ToUpperInvariant();
        if (string.IsNullOrWhiteSpace(mc)) return BadRequest(new { message = "moduleCode 必填" });
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);

        var hm = (await _db.Queryable<AdoSmartOpsHomeModule>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc)
            .ToListAsync()).FirstOrDefault();
        var items = await _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc && x.IsEnabled == 1)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var kpi = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == mc)
            .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
        {
            moduleCode = mc,
            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()
        });
    }

    /// <summary>
    /// 通用九宫格/首页（L1 合并日表）。moduleCode 指定模块。
    /// </summary>
    [HttpGet("home-grid/{moduleCode}")]
    public async Task<IActionResult> GetHomeGridGeneric(string moduleCode, [FromQuery] long factoryId = 1)
    {
        var mc = (moduleCode ?? "").ToUpperInvariant();
        if (string.IsNullOrWhiteSpace(mc)) return BadRequest(new { message = "moduleCode 必填" });
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        if (mc == "S8")
            await _s8KpiSnapshot.RefreshAsync(tenantId, factoryId);

        var layout = await _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc && x.IsEnabled == 1 && x.MetricLevel == 1)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var kpi = await _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == mc)
            .ToListAsync();
        var kpiBy = kpi.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

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

        const string valSql = """
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue
FROM ado_s9_kpi_value_l1_day v
WHERE v.tenant_id=@t AND v.factory_id=@f AND v.module_code=@m AND v.is_deleted=0
  AND v.biz_date=(
      SELECT MAX(v2.biz_date)
      FROM ado_s9_kpi_value_l1_day v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.module_code=v.module_code AND v2.metric_code=v.metric_code AND v2.is_deleted=0
  )
""";
        var vals = await _db.Ado.SqlQueryAsync<S4ValRow>(valSql, new { t = tenantId, f = factoryId, m = mc });
        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 == mc)
            .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 = FormatGapLabelGeneric(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
        {
            moduleCode = mc,
            layoutPattern = hm?.LayoutPattern ?? "card_grid",
            bizDate = bizDate.ToString("yyyy-MM-dd"),
            items
        });
    }

    /// <summary>
    /// 通用详情 KPI（L2/L3/L4 合并日表），可按 panelZone 过滤。
    /// </summary>
    [HttpGet("detail-kpis/{moduleCode}")]
    public async Task<IActionResult> GetDetailKpisGeneric(string moduleCode, [FromQuery] long factoryId = 1, [FromQuery] string? panelZone = null)
    {
        var mc = (moduleCode ?? "").ToUpperInvariant();
        if (string.IsNullOrWhiteSpace(mc)) return BadRequest(new { message = "moduleCode 必填" });
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        if (mc == "S8")
            await _s8KpiSnapshot.RefreshAsync(tenantId, factoryId);

        var q = _db.Queryable<AdoSmartOpsLayoutItem>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc && 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 == mc)
            .ToListAsync();
        var kpiBy = kpi.ToDictionary(x => x.MetricCode, StringComparer.OrdinalIgnoreCase);

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

        const string valSqlL2 = """
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue
FROM ado_s9_kpi_value_l2_day v
WHERE v.tenant_id=@t AND v.factory_id=@f AND v.module_code=@m AND v.is_deleted=0
  AND v.biz_date=(SELECT MAX(v2.biz_date) FROM ado_s9_kpi_value_l2_day v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.module_code=v.module_code AND v2.metric_code=v.metric_code AND v2.is_deleted=0)
""";
        const string valSqlL3 = """
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue
FROM ado_s9_kpi_value_l3_day v
WHERE v.tenant_id=@t AND v.factory_id=@f AND v.module_code=@m AND v.is_deleted=0
  AND v.biz_date=(SELECT MAX(v2.biz_date) FROM ado_s9_kpi_value_l3_day v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.module_code=v.module_code AND v2.metric_code=v.metric_code AND v2.is_deleted=0)
""";
        const string valSqlL4 = """
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue
FROM ado_s9_kpi_value_l4_day v
WHERE v.tenant_id=@t AND v.factory_id=@f AND v.module_code=@m AND v.is_deleted=0
  AND v.biz_date=(SELECT MAX(v2.biz_date) FROM ado_s9_kpi_value_l4_day v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.module_code=v.module_code AND v2.metric_code=v.metric_code AND v2.is_deleted=0)
""";
        var valsL2 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL2, new { t = tenantId, f = factoryId, m = mc });
        var valsL3 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL3, new { t = tenantId, f = factoryId, m = mc });
        var valsL4 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL4, new { t = tenantId, f = factoryId, m = mc });
        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 = FormatGapLabelGeneric(gap, k?.Unit),
                gapArrow = arrow,
                achievementLevel = level,
                formulaText = row.FormulaText,
                parentRowId = row.ParentRowId,
                panelZone = row.PanelZone
            });
        }

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

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

    private static string FormatGapLabelGeneric(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 GenericMaxDateRow
    {
        public DateTime? MaxBiz { get; set; }
    }

    /// <summary>
    /// 通用指标字典（按模块）。与 s4-metric-catalog 等价，moduleCode 必填。
    /// </summary>
    [HttpGet("metric-catalog/{moduleCode}")]
    public async Task<IActionResult> GetMetricCatalogGeneric(string moduleCode, [FromQuery] int? metricLevel = null)
    {
        var mc = (moduleCode ?? "").ToUpperInvariant();
        if (string.IsNullOrWhiteSpace(mc)) return BadRequest(new { message = "moduleCode 必填" });
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);

        var q = _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.ModuleCode == mc && 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,
            formulaExpr = x.FormulaExpr,
            formulaPreview = x.FormulaPreview,
            calcRule = x.CalcRule,
            dataSource = x.DataSource,
            statFrequency = x.StatFrequency,
            department = x.Department
        }));
    }

    public class GenericLayoutSaveDto
    {
        public List<GenericLayoutL1Dto>? L1 { get; set; }
        public List<GenericLayoutL2Dto>? L2 { get; set; }
        /// <summary>
        /// 九宫格首页模块卡呈现样式（card_grid | table_list | 未来新增）。
        /// 可选：未传或空值时保持 HomeModule 当前值；首次无记录时回落默认 card_grid。
        /// </summary>
        public string? LayoutPattern { get; set; }
    }

    /// <summary>
    /// 允许的 LayoutPattern 白名单。新增样式时在此增加条目（与前端 homeCardStyles.ts 注册表保持一致）。
    /// </summary>
    private static readonly HashSet<string> AllowedLayoutPatterns = new(StringComparer.OrdinalIgnoreCase)
    {
        "card_grid",
        "table_list",
        "tile_cards",
    };

    public class GenericLayoutL1Dto
    {
        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 GenericLayoutL2Dto
    {
        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>
    /// 通用保存运营布局：按 moduleCode 保存 L1/L2 到 LayoutItem；同步 KpiMaster.DisplayName。
    /// 约束：L1 最多 MaxL1PerModule；必须命中该 moduleCode 的字典项。
    /// </summary>
    [HttpPut("operation-layout/{moduleCode}")]
    public async Task<IActionResult> PutOperationLayoutGeneric(string moduleCode, [FromBody] GenericLayoutSaveDto? body, [FromQuery] long factoryId = 1)
    {
        var mc = (moduleCode ?? "").ToUpperInvariant();
        if (string.IsNullOrWhiteSpace(mc)) return BadRequest(new { message = "moduleCode 必填" });
        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 == mc && 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) ? $"{mc}-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();

            await _db.Deleteable<AdoSmartOpsLayoutItem>()
                .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc)
                .ExecuteCommandAsync();

            var rows = new List<AdoSmartOpsLayoutItem>();
            foreach (var r in body.L1)
            {
                var rid = string.IsNullOrWhiteSpace(r.RowId) ? $"{mc}-L1-{r.MetricCode!.Trim()}" : r.RowId.Trim();
                rows.Add(new AdoSmartOpsLayoutItem
                {
                    TenantId = tenantId, FactoryId = factoryId, ModuleCode = mc,
                    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 = $"{mc}-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 = mc,
                    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();

            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 lp = (body.LayoutPattern ?? "").Trim();
            if (!string.IsNullOrEmpty(lp) && !AllowedLayoutPatterns.Contains(lp))
                return BadRequest(new { message = $"无效的 LayoutPattern: {body.LayoutPattern}" });
            var lpNormalized = string.IsNullOrEmpty(lp) ? null : lp.ToLowerInvariant();

            int hmRows;
            if (lpNormalized != null)
            {
                hmRows = await _db.Updateable<AdoSmartOpsHomeModule>()
                    .SetColumns(x => new AdoSmartOpsHomeModule { LayoutPattern = lpNormalized, UpdateTime = now })
                    .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc)
                    .ExecuteCommandAsync();
            }
            else
            {
                hmRows = await _db.Updateable<AdoSmartOpsHomeModule>()
                    .SetColumns(x => new AdoSmartOpsHomeModule { UpdateTime = now })
                    .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.ModuleCode == mc)
                    .ExecuteCommandAsync();
            }
            if (hmRows == 0)
            {
                await _db.Insertable(new AdoSmartOpsHomeModule
                {
                    TenantId = tenantId, FactoryId = factoryId, ModuleCode = mc,
                    LayoutPattern = lpNormalized ?? "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 });
    }
}