using System.Globalization;
using Admin.NET.Plugin.AiDOP.DataPlatform;
using Admin.NET.Plugin.AiDOP.DataPlatform.Wms;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 工单下达服务 🏭
/// 路由前缀：/api/WorkOrder/dispatch/...
/// </summary>
[ApiDescriptionSettings(Order = 260, Description = "工单下达")]
[Route("api/WorkOrder")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderDispatchService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly WorkOrderKittingCheckService _kittingCheck;
    private readonly IPickBillCreator _pickBillCreator;
    private readonly AidopPickBillOptions _pickBillOpt;
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly KeeperResolveService _keeper;
    private readonly AidopActionRunLogWriter _runLog;
    private readonly ILogger<WorkOrderDispatchService> _logger;

    /// <summary>单次下达请求内的诊断上下文（写入 run_log.detail_json，不回传前端）。</summary>
    private ReleaseDiagContext? _diag;

    public WorkOrderDispatchService(
        ISqlSugarClient db,
        UserManager userManager,
        WorkOrderKittingCheckService kittingCheck,
        IPickBillCreator pickBillCreator,
        IOptions<AidopPickBillOptions> pickBillOpt,
        MdpSourceScopeFactory scopeFactory,
        KeeperResolveService keeper,
        AidopActionRunLogWriter runLog,
        ILogger<WorkOrderDispatchService> logger)
    {
        _db = db;
        _userManager = userManager;
        _kittingCheck = kittingCheck;
        _pickBillCreator = pickBillCreator;
        _pickBillOpt = pickBillOpt.Value;
        _scopeFactory = scopeFactory;
        _keeper = keeper;
        _runLog = runLog;
        _logger = logger;
    }

    private long ResolveTenantId(long? requestTenantId) =>
        AidopTenantHelper.Resolve(App.HttpContext, requestTenantId);

    /// <summary>校验：本租户下存在处于初始状态(p)的该工单。</summary>
    private async Task AssertInitialWorkOrderExistsAsync(long tenantId, string workOrd)
    {
        var wo = workOrd.Trim();
        var cnt = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND WorkOrd = @WorkOrd
              AND LOWER(TRIM(IFNULL(Status,''))) = 'p'
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", wo));
        if (cnt == 0)
            throw Oops.Oh("未找到处于初始状态(p)的本租户工单，请确认工单号与租户是否正确");
    }

    // ══════════════════════════════════════════════════════════════
    // 列表 GET /api/WorkOrder/dispatch/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取工单下达分页列表 🏭</summary>
    [DisplayName("获取工单下达列表")]
    [HttpGet("dispatch/list")]
    public async Task<object> GetDispatchList([FromQuery] WorkOrderDispatchListInput input)
    {
        var tid = AidopTenantHelper.Resolve(App.HttpContext);
        var pars = new List<SugarParameter> { new SugarParameter("@TenantId", tid) };
        // 与库内其它原生 SQL 一致：统一 COLLATE，避免 utf8mb4_general_ci / utf8mb4_0900_ai_ci 混用报错
        const string C = "utf8mb4_general_ci";
        var conditions = new List<string> { $"a.tenant_id = @TenantId", $"a.Status COLLATE {C} = 'p'" };

        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            conditions.Add($"(a.WorkOrd COLLATE {C}) LIKE (@WorkOrd COLLATE {C})");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            conditions.Add($"(a.ItemNum COLLATE {C}) LIKE (@ItemNum COLLATE {C})");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Descr))
        {
            conditions.Add($"(b.Descr COLLATE {C}) LIKE (@Descr COLLATE {C})");
            pars.Add(new SugarParameter("@Descr", $"%{input.Descr.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.OrdDateFrom))
        {
            conditions.Add("a.OrdDate >= @OrdDateFrom");
            pars.Add(new SugarParameter("@OrdDateFrom", input.OrdDateFrom.Trim()));
        }

        var whereClause = "WHERE " + string.Join(" AND ", conditions);

        // mes_morder 与工单的关联：历史上用 factory_id=Domain 直连，但部分库 factory_id 存租户/组织数值而 Domain 为工厂编码，
        // 会导致 JOIN 匹配不到、齐套列为空。改为按工单号取齐套，优先 factory_id 与 Domain 文本一致的那条。
        var baseSql = $"""
            SELECT
                a.RecID           AS Id,
                a.Domain,
                a.WorkOrd,
                a.Priority,
                a.ItemNum,
                b.Descr,
                b.Descr1,
                a.QtyOrded,
                m.MaterialSituation,
                a.OrdDate,
                a.DueDate,
                LOWER(a.Status)   AS Status,
                a.LotSerial,
                IFNULL(nm.Nbr,'')  AS PrevNbr,
                IFNULL(nm1.Nbr,'') AS Nbr,
                CONCAT(IFNULL(b.ItemNum,''), IFNULL(b.Descr,''), IFNULL(b.Descr1,'')) AS Wl
            FROM WorkOrdMaster a
            LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum AND (a.tenant_id = b.tenant_id OR a.tenant_id IS NULL)
            LEFT JOIN mes_morder m ON a.WorkOrd = m.morder_no
            LEFT JOIN NbrMaster nm ON a.WorkOrd= nm.WorkOrd
                AND nm.Type = 'SM' AND nm.TransType = 'PrevProcess'
            LEFT JOIN NbrMaster nm1 ON a.WorkOrd = nm1.WorkOrd
                AND nm1.Type = 'SM' AND nm1.TransType = ''
            {whereClause}
            """;

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t", pars);
        var list = await _db.Ado.SqlQueryAsync<WorkOrderDispatchRow>(
            $"SELECT * FROM ({baseSql}) AS t ORDER BY t.Id DESC LIMIT {input.PageSize} OFFSET {offset}",
            pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    // ══════════════════════════════════════════════════════════════
    // 下一生产批号 GET /api/WorkOrder/dispatch/next-lot-serial
    // 规则：以服务器「当前日期」的 yyMMdd + 三位流水；同一 tenant_id 下按已占用流水递增，避免重复。
    // 须存在：tenant_id + 工单号 + 状态 p。
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取下一可用生产批号（当前日期 + 三位流水）🏭</summary>
    [DisplayName("获取下一生产批号")]
    [HttpGet("dispatch/next-lot-serial")]
    public async Task<object> GetNextLotSerial([FromQuery] long? tenantId, [FromQuery] string workOrd)
    {
        var tid = ResolveTenantId(tenantId);
        if (string.IsNullOrWhiteSpace(workOrd))
            throw Oops.Oh("工单编号不能为空");

        await AssertInitialWorkOrderExistsAsync(tid, workOrd);

        var prefix = DateTime.Today.ToString("yyMMdd", CultureInfo.InvariantCulture);

        var maxSql = """
            SELECT IFNULL(MAX(CAST(RIGHT(TRIM(w.LotSerial), 3) AS UNSIGNED)), 0)
            FROM WorkOrdMaster w
            WHERE w.tenant_id = @TenantId
              AND TRIM(w.LotSerial) REGEXP '^[0-9]{9}$'
              AND LEFT(TRIM(w.LotSerial), 6) = @Prefix
            """;

        var maxSeq = await _db.Ado.GetIntAsync(maxSql,
            new SugarParameter("@TenantId", tid),
            new SugarParameter("@Prefix", prefix));
        var next = maxSeq + 1;
        if (next > 999)
            throw Oops.Oh($"当日生产批号流水已超过 999（前缀 {prefix}），请联系管理员");

        var lotSerial = prefix + next.ToString("D3", CultureInfo.InvariantCulture);
        return new { lotSerial };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单下达 POST /api/WorkOrder/dispatch/release
    // ══════════════════════════════════════════════════════════════
    /// <summary>工单下达（更新开工日期、生产批号，状态改为 r）。若原开工日、新开工日、原完工日均非空，则完工日按日历同天数平移。</summary>
    [DisplayName("工单下达")]
    [ApiDescriptionSettings(Name = "ReleaseWorkOrder"), HttpPost("dispatch/release")]
    public async Task<object> ReleaseWorkOrder([FromBody] WorkOrderReleaseInput input)
    {
        var tid = ResolveTenantId(input.TenantId > 0 ? input.TenantId : null);
        var wo = input.WorkOrd.Trim();
        var logId = await _runLog.StartAsync("S2_WORK_ORDER_RELEASE", tid, "WorkOrdMaster", null, wo);

        try
        {
            await AssertInitialWorkOrderExistsAsync(tid, wo);

            // ── 下达前置校验 ──
            // 1. 生产批号必须有值
            if (string.IsNullOrWhiteSpace(input.LotSerial))
                throw Oops.Oh("下达失败！生产批号不能为空，请先获取或输入生产批号。");

            // 2. 工单物料明细至少一条
            var detailCount = await _db.Ado.GetIntAsync(
                """
                SELECT COUNT(*) FROM WorkOrdDetail
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                """,
                new SugarParameter("@TenantId", tid),
                new SugarParameter("@WorkOrd", wo));
            if (detailCount == 0)
                throw Oops.Oh("下达失败！工单物料明细为空，请先同步或维护物料后重试。");

            // 3. 工单工序明细至少一条
            var routingCount = await _db.Ado.GetIntAsync(
                """
                SELECT COUNT(*) FROM WorkOrdRouting
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                """,
                new SugarParameter("@TenantId", tid),
                new SugarParameter("@WorkOrd", wo));
            if (routingCount == 0)
                throw Oops.Oh("下达失败！工单工序明细为空，请先同步工艺路线后重试。");

            if (!string.IsNullOrWhiteSpace(input.LotSerial))
            {
                var ls = input.LotSerial.Trim();
                var dup = await _db.Ado.GetIntAsync(
                    """
                    SELECT COUNT(*) FROM WorkOrdMaster
                    WHERE tenant_id = @TenantId
                      AND TRIM(IFNULL(LotSerial,'')) = @LotSerial
                      AND WorkOrd <> @WorkOrd
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@LotSerial", ls),
                    new SugarParameter("@WorkOrd", wo));
                if (dup > 0)
                    throw Oops.Oh("下达失败！生产批号已被其他工单占用，请重新获取或修改。");
            }

            DateTime? ordDate = string.IsNullOrWhiteSpace(input.OrdDate)
                ? null
                : DateTime.Parse(input.OrdDate.Trim(), CultureInfo.InvariantCulture).Date;
            var releaseDate = DateTime.Now;
            var account = _userManager.Account ?? "system";
            var domain = await ResolveWorkOrderDomainAsync(tid, wo);

            var kitting = await _kittingCheck.CheckSingleAsync(tid, wo, account);

            // WP9 S4：Mes 权威时先校验 165/保管员，再建领料（失败则本库仍为 p），最后写下达字段
            if (_pickBillOpt.IsMes)
                await EnsureMesPickPrereqsAsync(tid, domain, wo);

            PickBillResult pickResult;
            if (_pickBillOpt.IsMes)
            {
                pickResult = await CreatePickBillForReleaseAsync(tid, domain, wo, account);
                await _db.Ado.BeginTranAsync();
                try
                {
                    await ApplyReleaseLocalAsync(tid, wo, account, ordDate, input.LotSerial, releaseDate, requireStatusP: false);
                    await _db.Ado.CommitTranAsync();
                }
                catch (Exception ex)
                {
                    await _db.Ado.RollbackTranAsync();
                    throw ThrowReleaseFail(
                        PickBillUserMessages.ForReleaseFailure(null, "本库下达字段更新失败：" + TruncMsg(ex.Message), "请联系系统管理员。"),
                        "LOCAL_UPDATE_FAILED", tid, domain, wo, ex);
                }
            }
            else
            {
                await _db.Ado.BeginTranAsync();
                try
                {
                    pickResult = await CreatePickBillForReleaseAsync(tid, domain, wo, account);
                    await ApplyReleaseLocalAsync(tid, wo, account, ordDate, input.LotSerial, releaseDate, requireStatusP: true);
                    await _db.Ado.CommitTranAsync();
                }
                catch (Exception ex)
                {
                    await _db.Ado.RollbackTranAsync();
                    CaptureDiag("LOCAL_TRAN_FAILED", tid, domain, wo, ex, ex.Message);
                    throw;
                }
            }

            var response = new
            {
                message = "工单下达成功",
                materialSituation = kitting.MaterialSituation,
                pickBillNbr = pickResult.Nbr,
                pickBillCreated = pickResult.Created,
                pickBillLineCount = pickResult.LineCount,
                pickBillSource = pickResult.Source,
                pickBillMode = _pickBillOpt.Mode
            };
            await _runLog.SuccessAsync(logId, "工单下达成功", response);
            return response;
        }
        catch (Exception ex)
        {
            if (_diag == null)
                CaptureDiag(null, tid, null, wo, ex, ex.Message);

            await _runLog.FailedAsync(logId, ex.Message, new
            {
                workOrd = wo,
                tenantId = tid,
                mode = _pickBillOpt.Mode,
                reasonCode = _diag?.ReasonCode,
                domain = _diag?.Domain,
                userMessage = ex.Message,
                exceptionType = _diag?.ExceptionType ?? ex.GetType().FullName,
                techDetail = _diag?.TechDetail ?? TruncMsg(ex.ToString(), 4000),
                loggedAt = DateTime.Now.ToString("O")
            });
            throw;
        }
    }

    /// <summary>批量齐套检查（租户下 p/r 工单）。</summary>
    [DisplayName("批量齐套检查")]
    [HttpPost("dispatch/kitting-check")]
    public async Task<object> BatchKittingCheck([FromQuery] string domain, [FromQuery] string? userAccount)
    {
        var tid = ResolveTenantId(
            !string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var d) && d > 0 ? d : null);
        var account = string.IsNullOrWhiteSpace(userAccount)
            ? (_userManager.Account ?? "system")
            : userAccount.Trim();
        var logId = await _runLog.StartAsync("S2_KITTING_CHECK_BATCH", tid, "tenant", tid, domain ?? tid.ToString());
        try
        {
            var result = await _kittingCheck.CheckTenantWorkOrdersAsync(tid, account);
            await _runLog.SuccessAsync(logId, "批量齐套检查完成", result);
            return new
            {
                message = "ok",
                checkedCount = result.CheckedCount,
                kittedCount = result.KittedCount,
                shortageCount = result.ShortageCount
            };
        }
        catch (Exception ex)
        {
            await _runLog.FailedAsync(logId, ex.Message, new { tenantId = tid, domain });
            throw;
        }
    }

    /// <summary>单工单齐套检查（下达后或手动触发）。</summary>
    [DisplayName("单工单齐套检查")]
    [HttpPost("dispatch/work-order-kitting-check")]
    public async Task<object> SingleKittingCheck([FromQuery] string workord, [FromQuery] string domain, [FromQuery] string? userAccount)
    {
        var tid = ResolveTenantId(
            !string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var d) && d > 0 ? d : null);
        var account = string.IsNullOrWhiteSpace(userAccount)
            ? (_userManager.Account ?? "system")
            : userAccount.Trim();
        var wo = workord.Trim();
        var logId = await _runLog.StartAsync("S2_KITTING_CHECK_SINGLE", tid, "WorkOrdMaster", null, wo);
        try
        {
            var result = await _kittingCheck.CheckSingleAsync(tid, wo, account);
            if (!result.IsKitted)
            {
                await _runLog.FailedAsync(logId, $"缺料行 {result.ShortageLineCount}", result);
                throw Oops.Oh($"工单 {workord} 齐套检查未通过（缺料行 {result.ShortageLineCount}）");
            }

            await _runLog.SuccessAsync(logId, "单工单齐套检查通过", result);
            return new { message = "ok", materialSituation = result.MaterialSituation };
        }
        catch (Exception ex)
        {
            if (ex.Message.Contains("齐套检查未通过", StringComparison.Ordinal))
                throw;
            await _runLog.FailedAsync(logId, ex.Message, new { workOrd = wo, tenantId = tid });
            throw;
        }
    }

    private async Task<string> ResolveWorkOrderDomainAsync(long tenantId, string workOrd)
    {
        var domain = await _db.Ado.GetStringAsync(
            """
            SELECT TRIM(IFNULL(`Domain`, '')) FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@WorkOrd", workOrd)
            });
        return string.IsNullOrWhiteSpace(domain) ? "8010" : domain;
    }

    private async Task<PickBillResult> CreatePickBillForReleaseAsync(
        long tenantId, string domain, string workOrd, string account)
    {
        try
        {
            return await _pickBillCreator.CreateAsync(tenantId, domain, workOrd, account);
        }
        catch (Exception ex)
        {
            var raw = ex.Message ?? "";
            var reasonCode = GuessReasonCode(raw);
            if (raw.Contains("下达失败", StringComparison.Ordinal) || raw.Contains("建领料失败", StringComparison.Ordinal))
            {
                throw ThrowReleaseFail(
                    raw.Replace("建领料失败！", "下达失败！", StringComparison.Ordinal),
                    reasonCode, tenantId, domain, workOrd, ex);
            }

            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure(reasonCode, raw, "请联系系统管理员。"),
                reasonCode ?? "PICK_CREATE_FAILED", tenantId, domain, workOrd, ex);
        }
    }

    /// <summary>WP9 S4：下达前校验 165 可达 + 保管员可解析。</summary>
    private async Task EnsureMesPickPrereqsAsync(long tenantId, string domain, string workOrd)
    {
        try
        {
            await _scopeFactory.GetScopeAsync(CreatePickBillService.TargetSource);
        }
        catch (Exception ex)
        {
            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure("MES_UNREACHABLE"),
                "MES_UNREACHABLE", tenantId, domain, workOrd, ex);
        }

        var itemNums = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT DISTINCT TRIM(ItemNum) AS ItemNum
            FROM WorkOrdDetail
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
              AND IFNULL(IsActive, 0) = 1 AND IFNULL(ItemNum, '') <> ''
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (itemNums.Count == 0)
            throw ThrowReleaseFail(
                "下达失败！工单物料明细为空，请先同步或维护物料后重试。",
                "NO_DETAIL", tenantId, domain, workOrd);

        KeeperResolveService.KeeperResolveResult keeper;
        try
        {
            keeper = await _keeper.ResolveByItemsAsync(domain, itemNums);
        }
        catch (Exception ex)
        {
            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure("MES_UNREACHABLE"),
                "MES_UNREACHABLE", tenantId, domain, workOrd, ex);
        }

        if (keeper.UnsafeItems.Count > 0)
            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure("KEEPER_RULE_UNSAFE", items: keeper.UnsafeItems),
                "KEEPER_RULE_UNSAFE", tenantId, domain, workOrd,
                techNote: "unsafeItems=" + string.Join(",", keeper.UnsafeItems.Take(30)));

        if (keeper.EmptyLocationItems.Count > 0)
            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure("NO_KEEPER", items: keeper.EmptyLocationItems),
                "NO_KEEPER", tenantId, domain, workOrd,
                techNote: "emptyLocationItems=" + string.Join(",", keeper.EmptyLocationItems.Take(30)));

        if (keeper.MissingItems.Count > 0)
            throw ThrowReleaseFail(
                PickBillUserMessages.ForReleaseFailure("NO_KEEPER", items: keeper.MissingItems),
                "NO_KEEPER", tenantId, domain, workOrd,
                techNote: "missingItems=" + string.Join(",", keeper.MissingItems.Take(30)));
    }

    private Exception ThrowReleaseFail(
        string userMessage,
        string? reasonCode,
        long tenantId,
        string? domain,
        string workOrd,
        Exception? techEx = null,
        string? techNote = null)
    {
        CaptureDiag(reasonCode, tenantId, domain, workOrd, techEx, userMessage, techNote);
        return Oops.Oh(userMessage);
    }

    private void CaptureDiag(
        string? reasonCode,
        long tenantId,
        string? domain,
        string workOrd,
        Exception? techEx,
        string? userMessage,
        string? techNote = null)
    {
        var tech = techEx != null
            ? TruncMsg(techEx.ToString(), 4000)
            : TruncMsg(techNote, 2000);

        _diag = new ReleaseDiagContext
        {
            ReasonCode = reasonCode,
            Domain = domain,
            ExceptionType = techEx?.GetType().FullName,
            TechDetail = tech,
            UserMessage = userMessage
        };

        if (techEx != null)
        {
            _logger.LogError(
                techEx,
                "[WorkOrderRelease] reasonCode={ReasonCode} workOrd={WorkOrd} tenantId={TenantId} domain={Domain} mode={Mode} userMsg={UserMsg}",
                reasonCode, workOrd, tenantId, domain, _pickBillOpt.Mode, userMessage);
        }
        else
        {
            _logger.LogWarning(
                "[WorkOrderRelease] reasonCode={ReasonCode} workOrd={WorkOrd} tenantId={TenantId} domain={Domain} mode={Mode} userMsg={UserMsg} note={Note}",
                reasonCode, workOrd, tenantId, domain, _pickBillOpt.Mode, userMessage, techNote);
        }
    }

    private static string? GuessReasonCode(string message)
    {
        if (message.Contains("无法连接 MES", StringComparison.Ordinal) ||
            message.Contains("MES_UNREACHABLE", StringComparison.OrdinalIgnoreCase))
            return "MES_UNREACHABLE";
        if (message.Contains("保管员", StringComparison.Ordinal) ||
            message.Contains("NO_KEEPER", StringComparison.OrdinalIgnoreCase))
            return "NO_KEEPER";
        if (message.Contains("KEEPER_RULE_UNSAFE", StringComparison.OrdinalIgnoreCase) ||
            message.Contains("不支持的字符", StringComparison.Ordinal))
            return "KEEPER_RULE_UNSAFE";
        if (message.Contains("取号", StringComparison.Ordinal) ||
            message.Contains("SEQ_FAILED", StringComparison.OrdinalIgnoreCase))
            return "SEQ_FAILED";
        return null;
    }

    private static string TruncMsg(string? s, int max = 120)
    {
        s ??= "";
        return s.Length <= max ? s : s[..max] + "…";
    }

    private sealed class ReleaseDiagContext
    {
        public string? ReasonCode { get; set; }
        public string? Domain { get; set; }
        public string? ExceptionType { get; set; }
        public string? TechDetail { get; set; }
        public string? UserMessage { get; set; }
    }

    /// <summary>
    /// 本库下达字段：Status/批号/日期/里程碑工序/mes_morder。
    /// Mes 路径下建单服务可能已将 Status 置 r，故 <paramref name="requireStatusP"/> 为 false。
    /// </summary>
    private async Task ApplyReleaseLocalAsync(
        long tenantId,
        string workOrd,
        string account,
        DateTime? ordDate,
        string? lotSerial,
        DateTime releaseDate,
        bool requireStatusP)
    {
        var updatePars = new List<SugarParameter>
        {
            new("@Status", "r"),
            new("@OrdDate", ordDate ?? (object)DBNull.Value),
            new("@LotSerial", string.IsNullOrWhiteSpace(lotSerial) ? (object)DBNull.Value : lotSerial.Trim()),
            new("@ReleaseDate", releaseDate),
            new("@UpdateUser", account),
            new("@UpdateTime", releaseDate),
            new("@WorkOrd", workOrd),
            new("@TenantId", tenantId)
        };

        var statusFilter = requireStatusP
            ? " AND LOWER(TRIM(IFNULL(Status,''))) = 'p'"
            : "";

        var affected = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET Status      = @Status,
                OrdDate     = @OrdDate,
                DueDate     = CASE
                    WHEN OrdDate IS NOT NULL AND @OrdDate IS NOT NULL AND DueDate IS NOT NULL THEN
                        DATE_ADD(DATE(DueDate), INTERVAL DATEDIFF(DATE(@OrdDate), DATE(OrdDate)) DAY)
                    ELSE DueDate
                END,
                LotSerial   = @LotSerial,
                ReleaseDate = @ReleaseDate,
                Eff         = @ReleaseDate,
                UpdateUser  = @UpdateUser,
                UpdateTime  = @UpdateTime,
                Batch       = SUBSTRING(@WorkOrd, 2)
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            {statusFilter}
            """,
            updatePars);

        if (affected == 0)
            throw Oops.Oh("下达失败！未更新到对应工单，请确认工单仍为初始状态后重试。");

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE WorkOrdRouting
            SET Status = 'r', UpdateUser = @User, UpdateTime = @Now
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
              AND IFNULL(MilestoneOp, 0) = 1 AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", releaseDate),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_morder
            SET morder_state = '下达',
                MaterialSituation = CASE
                    WHEN MaterialSituation = '缺料' THEN '未备料'
                    ELSE IFNULL(MaterialSituation, '齐套')
                END,
                update_by_name = @User,
                update_time = @Now
            WHERE tenant_id = @TenantId AND morder_no = @WorkOrd AND IsDeleted = 0
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", releaseDate),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    // ──────────────── 内部查询结果映射类 ────────────────

    private sealed class WorkOrderDispatchRow
    {
        public int Id { get; set; }
        public string? Domain { get; set; }
        public string? WorkOrd { get; set; }
        public string? Priority { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public decimal? QtyOrded { get; set; }
        public string? MaterialSituation { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
        public string? LotSerial { get; set; }
        public string? PrevNbr { get; set; }
        public string? Nbr { get; set; }
        public string? Wl { get; set; }
    }
}