using System.Globalization;
using System.Text.Json;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Service;
using Admin.NET.Plugin.ApprovalFlow.Service;

namespace Admin.NET.Plugin.AiDOP.Controllers;

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

    public AdoSmartOpsImprovementPlanController(ISqlSugarClient db, FlowEngineService flowEngine)
    {
        _db = db;
        _flowEngine = flowEngine;
    }

    [HttpPost("createFromDiagnosis")]
    public async Task<IActionResult> CreateFromDiagnosis([FromBody] ImprovementPlanCreateInput input)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var now = DateTime.Now;
        var entity = new AdoSmartOpsImprovementPlan
        {
            TenantId = tenantId,
            FactoryId = input.FactoryId <= 0 ? 1 : input.FactoryId,
            PlanNo = await GeneratePlanNoAsync(),
            ModuleCode = (input.ModuleCode ?? "").Trim().ToUpperInvariant(),
            MetricCode = input.MetricCode,
            ProblemLevel = input.ProblemLevel,
            ProblemMetricCode = input.ProblemMetricCode,
            ProblemName = input.ProblemName ?? "",
            ProblemDept = input.ProblemDept,
            TargetValue = input.TargetValue,
            ActualValue = input.ActualValue,
            GapLabel = input.GapLabel,
            RootCause = input.RootCause,
            ActionItemsJson = JsonSerializer.Serialize(input.ActionItems ?? new List<ImprovementActionItem>()),
            OwnerUserId = input.OwnerUserId,
            DueDate = input.DueDate,
            Status = "draft",
            CreateTime = now,
            UpdateTime = now,
        };

        if (string.IsNullOrWhiteSpace(entity.ModuleCode)) return BadRequest(new { message = "模块编码不能为空" });
        if (string.IsNullOrWhiteSpace(entity.ProblemName)) return BadRequest(new { message = "问题名称不能为空" });

        entity.Id = await _db.Insertable(entity).ExecuteReturnBigIdentityAsync();
        return Ok(ToOutput(entity));
    }

    [HttpPost("page")]
    public async Task<IActionResult> Page([FromBody] ImprovementPlanPageInput input)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : input.PageSize;
        var q = _db.Queryable<AdoSmartOpsImprovementPlan>()
            .Where(x => x.TenantId == tenantId)
            .WhereIF(!string.IsNullOrWhiteSpace(input.ModuleCode), x => x.ModuleCode == input.ModuleCode)
            .WhereIF(!string.IsNullOrWhiteSpace(input.Status), x => x.Status == input.Status)
            .WhereIF(!string.IsNullOrWhiteSpace(input.Keyword), x => x.PlanNo.Contains(input.Keyword!) || x.ProblemName.Contains(input.Keyword!))
            .OrderByDescending(x => x.CreateTime);
        var result = await q.ToPagedListAsync(page, pageSize);
        return Ok(new
        {
            result.Page,
            result.PageSize,
            result.Total,
            Items = result.Items.Select(ToOutput).ToList()
        });
    }

    [HttpGet("detail")]
    public async Task<IActionResult> Detail([FromQuery] long id)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        var entity = await _db.Queryable<AdoSmartOpsImprovementPlan>()
            .FirstAsync(x => x.Id == id && x.TenantId == tenantId);
        return entity == null ? NotFound() : Ok(ToOutput(entity));
    }

    [HttpGet("verificationDashboard")]
    public async Task<IActionResult> VerificationDashboard([FromQuery] long id)
    {
        var plan = await LoadPlanAsync(id) ?? throw Oops.Oh("改善计划不存在");
        var actions = ParseActionItems(plan.ActionItemsJson);
        var startDate = plan.CreateTime.Date;
        var endDate = ResolveVerificationEndDate(plan, actions);
        if (endDate < startDate) endDate = startDate;

        var metricCode = (plan.ProblemMetricCode ?? plan.MetricCode ?? "").Trim();
        var metricMeta = string.IsNullOrWhiteSpace(metricCode)
            ? null
            : await _db.Queryable<AdoSmartOpsKpiMaster>().FirstAsync(x => x.TenantId == plan.TenantId && x.MetricCode == metricCode);
        var metricLevel = metricMeta?.MetricLevel ?? NormalizeMetricLevel(plan.ProblemLevel);
        var targetFallback = ParseDecimal(plan.TargetValue);
        var table = ResolveKpiValueTable(metricLevel);
        var trendRows = await QueryVerificationTrendAsync(table, plan, metricCode, startDate, endDate);
        if (trendRows.Count == 0)
        {
            foreach (var fallbackTable in new[] { "ado_s9_kpi_value_l4_day", "ado_s9_kpi_value_l3_day", "ado_s9_kpi_value_l2_day", "ado_s9_kpi_value_l1_day" })
            {
                if (fallbackTable == table) continue;
                trendRows = await QueryVerificationTrendAsync(fallbackTable, plan, metricCode, startDate, endDate);
                if (trendRows.Count > 0) break;
            }
        }

        var trendByDate = trendRows
            .GroupBy(x => x.BizDate.Date)
            .ToDictionary(x => x.Key, x => x.OrderByDescending(r => r.TargetValue.HasValue).First());
        var timeline = BuildDateRange(startDate, endDate)
            .Select(day => new
            {
                Date = day.ToString("yyyy-MM-dd"),
                IsStart = day == startDate,
                IsEnd = day == endDate,
                Actions = actions
                    .Select((action, index) => BuildVerificationActionNode(action, index, day))
                    .Where(x => x != null)
                    .ToList(),
            })
            .ToList();
        var series = BuildDateRange(startDate, endDate)
            .Select(day =>
            {
                trendByDate.TryGetValue(day, out var row);
                return new
                {
                    Date = day.ToString("yyyy-MM-dd"),
                    Value = row?.MetricValue,
                    Target = row?.TargetValue ?? targetFallback,
                };
            })
            .ToList();

        return Ok(new
        {
            Plan = ToOutput(plan),
            Metric = new
            {
                MetricCode = metricCode,
                MetricName = metricMeta?.MetricName ?? plan.ProblemName,
                Unit = metricMeta?.Unit ?? ResolveUnitFromText(plan.TargetValue),
                TargetValue = targetFallback,
                Level = metricLevel,
            },
            StartDate = startDate.ToString("yyyy-MM-dd"),
            EndDate = endDate.ToString("yyyy-MM-dd"),
            Timeline = timeline,
            Trend = series,
        });
    }

    [HttpPost("submitApproval")]
    public async Task<IActionResult> SubmitApproval([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status is "approving" or "executing" or "pending_verify" or "closed")
            return BadRequest(new { message = "当前状态不可提交审批" });

        try
        {
            var instanceId = await _flowEngine.StartFlow(new StartFlowInput
            {
                BizType = SmartOpsImprovementBizHandler.BizTypeCode,
                BizId = plan.Id,
                BizNo = plan.PlanNo,
                Title = $"智慧诊断改善计划-{plan.PlanNo}",
                Comment = input.Comment,
                BizData = new Dictionary<string, object>
                {
                    ["moduleCode"] = plan.ModuleCode,
                    ["problemLevel"] = plan.ProblemLevel,
                    ["problemName"] = plan.ProblemName,
                    ["problemDept"] = plan.ProblemDept ?? "",
                }
            });
            plan = await LoadPlanAsync(input.Id) ?? plan;
            return Ok(new { instanceId, plan = ToOutput(plan) });
        }
        catch (Exception ex)
        {
            return BadRequest(new { message = $"提交审批失败：{ex.Message}" });
        }
    }

    [HttpPost("startExecution")]
    public async Task<IActionResult> StartExecution([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status != "approved" && plan.Status != "executing")
            return BadRequest(new { message = "审批通过后才能开始执行" });
        await UpdateStatusAsync(plan.Id, "executing");
        return Ok(ToOutput((await LoadPlanAsync(plan.Id))!));
    }

    [HttpPost("updateActionItem")]
    public async Task<IActionResult> UpdateActionItem([FromBody] ImprovementActionItemUpdateInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (input.Index <= 0) return BadRequest(new { message = "行动项序号无效" });

        var actions = ParseActionItems(plan.ActionItemsJson);
        var index = input.Index - 1;
        if (index < 0 || index >= actions.Count) return BadRequest(new { message = "行动项不存在" });

        var status = NormalizeManualActionStatus(input.Status);
        actions[index].Status = status;
        actions[index].CompletedAt = ResolveActionCompletedAt(status, input.CompletedAt);
        var actionItemsJson = JsonSerializer.Serialize(actions);
        var now = DateTime.Now;

        await _db.Updateable<AdoSmartOpsImprovementPlan>()
            .SetColumns(x => new AdoSmartOpsImprovementPlan
            {
                ActionItemsJson = actionItemsJson,
                UpdateTime = now
            })
            .Where(x => x.Id == plan.Id)
            .ExecuteCommandAsync();

        return Ok(ToOutput((await LoadPlanAsync(plan.Id))!));
    }

    [HttpPost("submitVerification")]
    public async Task<IActionResult> SubmitVerification([FromBody] ImprovementVerificationInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status is not ("executing" or "pending_verify"))
            return BadRequest(new { message = "执行中计划才能提交验证" });

        await _db.Updateable<AdoSmartOpsImprovementPlan>()
            .SetColumns(x => x.Status == "pending_verify")
            .SetColumns(x => x.VerifyResult == input.VerifyResult)
            .SetColumns(x => x.VerifyValue == input.VerifyValue)
            .SetColumns(x => x.VerifyRemark == input.VerifyRemark)
            .SetColumns(x => x.UpdateTime == DateTime.Now)
            .Where(x => x.Id == plan.Id)
            .ExecuteCommandAsync();
        return Ok(ToOutput((await LoadPlanAsync(plan.Id))!));
    }

    [HttpPost("close")]
    public async Task<IActionResult> Close([FromBody] ImprovementPlanIdInput input)
    {
        var plan = await LoadPlanAsync(input.Id) ?? throw Oops.Oh("改善计划不存在");
        if (plan.Status != "pending_verify")
            return BadRequest(new { message = "提交验证后才能关闭" });
        await UpdateStatusAsync(plan.Id, "closed");
        return Ok(ToOutput((await LoadPlanAsync(plan.Id))!));
    }

    private async Task<AdoSmartOpsImprovementPlan?> LoadPlanAsync(long id)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        return await _db.Queryable<AdoSmartOpsImprovementPlan>().FirstAsync(x => x.Id == id && x.TenantId == tenantId);
    }

    private async Task UpdateStatusAsync(long id, string status)
    {
        await _db.Updateable<AdoSmartOpsImprovementPlan>()
            .SetColumns(x => x.Status == status)
            .SetColumns(x => x.UpdateTime == DateTime.Now)
            .Where(x => x.Id == id)
            .ExecuteCommandAsync();
    }

    private async Task<string> GeneratePlanNoAsync()
    {
        var prefix = $"SOP-{DateTime.Now:yyyyMMdd}";
        var count = await _db.Queryable<AdoSmartOpsImprovementPlan>()
            .Where(x => x.PlanNo.StartsWith(prefix))
            .CountAsync();
        return $"{prefix}-{count + 1:000}";
    }

    private static object ToOutput(AdoSmartOpsImprovementPlan x)
    {
        return new
        {
            x.Id,
            x.PlanNo,
            x.ModuleCode,
            x.MetricCode,
            x.ProblemLevel,
            x.ProblemMetricCode,
            x.ProblemName,
            x.ProblemDept,
            x.TargetValue,
            x.ActualValue,
            x.GapLabel,
            x.RootCause,
            ActionItems = ParseActionItems(x.ActionItemsJson),
            x.OwnerUserId,
            DueDate = x.DueDate?.ToString("yyyy-MM-dd"),
            x.Status,
            x.FlowInstanceId,
            x.VerifyResult,
            x.VerifyValue,
            x.VerifyRemark,
            x.CreateTime,
            x.UpdateTime,
        };
    }

    private async Task<List<ImprovementVerificationTrendRow>> QueryVerificationTrendAsync(
        string table,
        AdoSmartOpsImprovementPlan plan,
        string metricCode,
        DateTime startDate,
        DateTime endDate)
    {
        if (string.IsNullOrWhiteSpace(metricCode)) return new List<ImprovementVerificationTrendRow>();
        var sql = $"""
SELECT biz_date AS BizDate, metric_value AS MetricValue, target_value AS TargetValue
FROM {table}
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode
  AND metric_code=@metricCode AND is_deleted=0 AND biz_date BETWEEN @startDate AND @endDate
ORDER BY biz_date
""";
        return await _db.Ado.SqlQueryAsync<ImprovementVerificationTrendRow>(sql, new
        {
            tenantId = plan.TenantId,
            factoryId = plan.FactoryId <= 0 ? 1 : plan.FactoryId,
            moduleCode = plan.ModuleCode,
            metricCode,
            startDate,
            endDate,
        });
    }

    private static DateTime ResolveVerificationEndDate(AdoSmartOpsImprovementPlan plan, List<ImprovementActionItem> actions)
    {
        var candidates = new List<DateTime> { plan.CreateTime.Date };
        if (plan.DueDate.HasValue) candidates.Add(plan.DueDate.Value.Date);
        foreach (var action in actions)
        {
            if (DateTime.TryParse(action.DueDate, out var dueDate)) candidates.Add(dueDate.Date);
            if (DateTime.TryParse(action.CompletedAt, out var completedAt)) candidates.Add(completedAt.Date);
        }
        return candidates.Max();
    }

    private static IEnumerable<DateTime> BuildDateRange(DateTime startDate, DateTime endDate)
    {
        for (var day = startDate.Date; day <= endDate.Date; day = day.AddDays(1))
            yield return day;
    }

    private static ImprovementVerificationActionNode? BuildVerificationActionNode(ImprovementActionItem action, int index, DateTime day)
    {
        if (!DateTime.TryParse(action.DueDate, out var dueDate) || dueDate.Date != day.Date) return null;
        var status = NormalizeActionStatus(action.Status, action.CompletedAt, dueDate.Date);
        return new ImprovementVerificationActionNode
        {
            Index = index + 1,
            Content = action.Content,
            Owner = action.Owner,
            DueDate = dueDate.ToString("yyyy-MM-dd"),
            Status = status,
            StatusLabel = ActionStatusLabel(status),
            CompletedAt = DateTime.TryParse(action.CompletedAt, out var completedAt) ? completedAt.ToString("yyyy-MM-dd") : null,
        };
    }

    private static string NormalizeActionStatus(string? status, string? completedAt, DateTime dueDate)
    {
        var normalized = (status ?? "").Trim().ToLowerInvariant();
        if (normalized is "completed" or "done") return "completed";
        if (normalized is "doing" or "in_progress") return "doing";
        if (!string.IsNullOrWhiteSpace(completedAt)) return "completed";
        if (dueDate < DateTime.Today) return "overdue";
        return string.IsNullOrWhiteSpace(normalized) ? "pending" : normalized;
    }

    private static string NormalizeManualActionStatus(string? status)
    {
        var normalized = (status ?? "").Trim().ToLowerInvariant();
        return normalized switch
        {
            "completed" or "done" => "completed",
            "doing" or "in_progress" => "doing",
            _ => "pending",
        };
    }

    private static string? ResolveActionCompletedAt(string status, string? completedAt)
    {
        if (status != "completed") return null;
        if (DateTime.TryParse(completedAt, out var value)) return value.ToString("yyyy-MM-dd");
        return DateTime.Today.ToString("yyyy-MM-dd");
    }

    private static string ActionStatusLabel(string status)
    {
        return status switch
        {
            "completed" => "已完成",
            "doing" => "进行中",
            "overdue" => "逾期",
            _ => "未开始",
        };
    }

    private static int NormalizeMetricLevel(int problemLevel)
    {
        return problemLevel switch
        {
            <= 1 => 2,
            2 => 3,
            >= 3 => 4,
        };
    }

    private static string ResolveKpiValueTable(int metricLevel)
    {
        return metricLevel switch
        {
            1 => "ado_s9_kpi_value_l1_day",
            2 => "ado_s9_kpi_value_l2_day",
            3 => "ado_s9_kpi_value_l3_day",
            4 => "ado_s9_kpi_value_l4_day",
            _ => "ado_s9_kpi_value_l2_day",
        };
    }

    private static decimal? ParseDecimal(string? text)
    {
        if (string.IsNullOrWhiteSpace(text)) return null;
        var digits = new string(text.Where(c => char.IsDigit(c) || c is '.' or '-').ToArray());
        return decimal.TryParse(digits, NumberStyles.Any, CultureInfo.InvariantCulture, out var value) ? value : null;
    }

    private static string? ResolveUnitFromText(string? text)
    {
        if (string.IsNullOrWhiteSpace(text)) return null;
        var unit = new string(text.Where(c => !char.IsDigit(c) && c is not '.' and not '-' and not '/').ToArray()).Trim();
        return string.IsNullOrWhiteSpace(unit) ? null : unit;
    }

    private static List<ImprovementActionItem> ParseActionItems(string? json)
    {
        if (string.IsNullOrWhiteSpace(json)) return new List<ImprovementActionItem>();
        try
        {
            return JsonSerializer.Deserialize<List<ImprovementActionItem>>(json) ?? new List<ImprovementActionItem>();
        }
        catch
        {
            return new List<ImprovementActionItem>();
        }
    }
}

public class ImprovementPlanCreateInput
{
    public long FactoryId { get; set; } = 1;
    public string? ModuleCode { get; set; }
    public string? MetricCode { get; set; }
    public int ProblemLevel { get; set; }
    public string? ProblemMetricCode { get; set; }
    public string? ProblemName { get; set; }
    public string? ProblemDept { get; set; }
    public string? TargetValue { get; set; }
    public string? ActualValue { get; set; }
    public string? GapLabel { get; set; }
    public string? RootCause { get; set; }
    public List<ImprovementActionItem>? ActionItems { get; set; }
    public long? OwnerUserId { get; set; }
    public DateTime? DueDate { get; set; }
}

public class ImprovementActionItem
{
    public string Content { get; set; } = "";
    public string? Owner { get; set; }
    public string? DueDate { get; set; }
    public string? Status { get; set; }
    public string? CompletedAt { get; set; }
}

public class ImprovementPlanPageInput
{
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 20;
    public string? ModuleCode { get; set; }
    public string? Status { get; set; }
    public string? Keyword { get; set; }
}

public class ImprovementPlanIdInput
{
    public long Id { get; set; }
    public string? Comment { get; set; }
}

public class ImprovementVerificationInput
{
    public long Id { get; set; }
    public string? VerifyResult { get; set; }
    public string? VerifyValue { get; set; }
    public string? VerifyRemark { get; set; }
}

public class ImprovementActionItemUpdateInput
{
    public long Id { get; set; }
    public int Index { get; set; }
    public string? Status { get; set; }
    public string? CompletedAt { get; set; }
}

public class ImprovementVerificationActionNode
{
    public int Index { get; set; }
    public string Content { get; set; } = "";
    public string? Owner { get; set; }
    public string? DueDate { get; set; }
    public string Status { get; set; } = "pending";
    public string StatusLabel { get; set; } = "未开始";
    public string? CompletedAt { get; set; }
}

public class ImprovementVerificationTrendRow
{
    public DateTime BizDate { get; set; }
    public decimal? MetricValue { get; set; }
    public decimal? TargetValue { get; set; }
}