using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Dto.SmartOps;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
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, 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 > 3)
            return BadRequest(new { message = "层级必须为 1/2/3" });
        if (dto.MetricLevel > 1 && dto.ParentId == null)
            return BadRequest(new { message = "L2/L3 必须指定父指标" });

        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        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,
            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 });
    }

    /// <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();

        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.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 });
    }

    /// <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 (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();

        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}");

        long seq = 0;
        async Task InsertLevel(List<DemoImportRow> items, string table)
        {
            if (items.Count == 0) return;
            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++)
                {
                    seq++;
                    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 = 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(
                        $"({seq},{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");

        var fromExcel = excelOverrides.Values.Count(v => v.HasTarget || v.HasActual);
        return Ok(new
        {
            ok = true,
            message = $"导入成功：Excel 填入 {fromExcel} 条，自动补全 {parsed.Count - fromExcel} 条，共 {parsed.Count} 条指标 × {DAYS} 天",
            detail = new { l1 = l1.Count, l2 = l2.Count, l3 = l3.Count, days = DAYS, fromExcel, autoGen = parsed.Count - fromExcel }
        });
    }

    /// <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; }
    }

    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 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);
        }
    }
}