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）。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);

        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 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=@m AND is_deleted=0 AND biz_date=@d
""";
        var vals = await _db.Ado.SqlQueryAsync<S4ValRow>(valSql, new { t = tenantId, f = factoryId, m = mc, 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 == 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 合并日表），可按 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);

        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);
        var bizDate = bizDateL2 >= bizDateL3 ? bizDateL2 : bizDateL3;

        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=@m 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=@m AND is_deleted=0 AND biz_date=@d
""";
        var valsL2 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL2, new { t = tenantId, f = factoryId, m = mc, d = bizDateL2 });
        var valsL3 = await _db.Ado.SqlQueryAsync<S4ValRow>(valSqlL3, new { t = tenantId, f = factoryId, m = mc, d = bizDateL3 });
        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;

        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 factory_id=@f AND module_code=@m AND is_deleted=0
""";
        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; }
    }
}