AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Production/WorkOrderSchedulingService.cs

using System.Text.Json;
using Admin.NET.Plugin.AiDOP.DataPlatform.Wms;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 工单工序排产服务 🏭
/// 路由前缀：/api/Production/scheduling/...
///
/// 【租户安全边界】租户一律经 <see cref="AidopTenantScope.ResolveOrThrow"/> 从认证后 JWT 解析，
/// 无有效租户即拒绝，不读前端 tenantId、无默认回退。
/// <para>
/// 原实现：类级 <c>[AllowAnonymous]</c> + 部分端点 <c>AidopTenantHelper.Resolve(App.HttpContext)</c>
/// （解析链含 query 参数 tenantId 与 DefaultTenantId 兜底）；且写端点（save/status/urgent/sync-routing 等）
/// 仅按前端直传的 <c>Domain</c> 字符串定位 WorkOrdMaster（无 tenant_id 条件），
/// 叠加后未认证方可指定任意租户改写工单。现共享匹配片段一律追加 <c>tenant_id = @TenantId</c> 强校验。
/// </para>
/// </summary>
[ApiDescriptionSettings(Order = 265, Description = "工单工序排产")]
[Route("api/Production")]
[NonUnify]
public class WorkOrderSchedulingService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly PickBillNbrSyncService _pickNbrSync;

    public WorkOrderSchedulingService(
        ISqlSugarClient db,
        UserManager userManager,
        PickBillNbrSyncService pickNbrSync)
    {
        _db = db;
        _userManager = userManager;
        _pickNbrSync = pickNbrSync;
    }

    /// <summary>
    /// 工单信息类（用于查询结果映射）
    /// </summary>
    private class WorkOrderInfo
    {
        public string WorkOrd { get; set; } = string.Empty;
        public string Domain { get; set; } = string.Empty;
    }

    /// <summary>
    /// 主表 WorkOrdMaster 按工单号 +「公司域或租户 id」匹配：<c>domain</c> 可为原 <c>Domain</c> 列，或与 <c>tenant_id</c> 相同的字符串（列表已做 COALESCE 回传）。
    /// 追加 <c>tenant_id = @TenantId</c>：Domain 仅作定位，租户强校验不可被前端直传值绕过（每处使用需带 @TenantId 参数）。
    /// </summary>
    private const string SqlWorkOrdMasterMatchDomainOrTenant = """
        WorkOrd = @WorkOrd AND tenant_id = @TenantId AND (
            TRIM(IFNULL(`Domain`, '')) = TRIM(@Domain)
            OR (
                TRIM(IFNULL(`Domain`, '')) = ''
                AND TRIM(@Domain) <> ''
                AND CAST(IFNULL(tenant_id, 0) AS CHAR) = TRIM(@Domain)
            )
        )
        """;

    /// <summary>带表别名的 WorkOrdMaster 匹配（参数 <c>alias</c> 为表别名，如 a、w）；同上追加 <c>tenant_id = @TenantId</c> 强校验。</summary>
    private static string SqlWorkOrdMasterMatchDomainOrTenantAliased(string alias) => $"""
        {alias}.WorkOrd = @WorkOrd AND {alias}.tenant_id = @TenantId AND (
            TRIM(IFNULL({alias}.`Domain`, '')) = TRIM(@Domain)
            OR (
                TRIM(IFNULL({alias}.`Domain`, '')) = ''
                AND TRIM(@Domain) <> ''
                AND CAST(IFNULL({alias}.tenant_id, 0) AS CHAR) = TRIM(@Domain)
            )
        )
        """;

    /// <summary>子表（工序/明细）按工单号 + 域或主表租户匹配；EXISTS 内追加主表 <c>tenant_id = @TenantId</c> 强校验。</summary>
    private static string SqlWorkOrdChildMatchDomainOrTenant(string childAlias, string masterAlias) => $"""
        {childAlias}.WorkOrd = @WorkOrd AND (
            TRIM(IFNULL({childAlias}.`Domain`, '')) = TRIM(@Domain)
            OR EXISTS (
                SELECT 1 FROM WorkOrdMaster {masterAlias}
                WHERE {masterAlias}.WorkOrd = {childAlias}.WorkOrd
                  AND {masterAlias}.tenant_id = @TenantId
                  AND (
                      TRIM(IFNULL({masterAlias}.`Domain`, '')) = TRIM(@Domain)
                      OR (
                          TRIM(IFNULL({masterAlias}.`Domain`, '')) = ''
                          AND TRIM(@Domain) <> ''
                          AND CAST(IFNULL({masterAlias}.tenant_id, 0) AS CHAR) = TRIM(@Domain)
                      )
                  )
            )
        )
        """;

    /// <summary>按主键 +「公司域或租户 id」匹配（状态更新等）；同上追加 <c>tenant_id = @TenantId</c> 强校验。</summary>
    private const string SqlWorkOrdMasterMatchRecIdAndDomainOrTenant = """
        RecID = @Id AND tenant_id = @TenantId AND (
            TRIM(IFNULL(`Domain`, '')) = TRIM(@Domain)
            OR (
                TRIM(IFNULL(`Domain`, '')) = ''
                AND TRIM(@Domain) <> ''
                AND CAST(IFNULL(tenant_id, 0) AS CHAR) = TRIM(@Domain)
            )
        )
        """;

    // ══════════════════════════════════════════════════════════════
    // 列表 GET /api/Production/scheduling/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>工单工序排产分页列表（MySQL 口径，与 WorkOrdMaster 等表一致）</summary>
    [DisplayName("工单工序排产列表")]
    [HttpGet("scheduling/list")]
    public async Task<object> GetList([FromQuery] WorkOrderSchedulingListInput input)
    {
        //const string C = "utf8mb4_general_ci";
        const string C = "utf8mb4_0900_ai_ci";
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        var innerWhere = new List<string>
        {
            "IFNULL(a.Status,'') <> ''",
            "a.tenant_id = @TenantId"
        };

        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            innerWhere.Add($"a.WorkOrd LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            innerWhere.Add($"a.LotSerial LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            innerWhere.Add($"a.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.StartDateFrom))
        {
            innerWhere.Add("COALESCE(DATE(s.PlanDate), DATE(a.OrdDate)) >= @StartDateFrom");
            pars.Add(new SugarParameter("@StartDateFrom", input.StartDateFrom.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            innerWhere.Add("LOWER(a.Status) = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim().ToLowerInvariant()));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", innerWhere), C);

        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<WorkOrderSchedulingListRow>(
            $"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 };
    }

    private static string BuildListBaseSql(string innerWhere, string C) => $"""
        SELECT
            a.RecID AS Id,
            a.Priority AS Priority,
            a.WorkOrd AS WorkOrd,
            a.LotSerial AS LotSerial,
            a.IssueSite AS IssueSite,
            a.ItemNum AS ItemNum,
            b.Descr AS Descr,
            b.Descr1 AS Descr1,
            a.QtyOrded AS QtyOrded,
            (IFNULL(a.LocationStock, 0) + IFNULL(a.OpQtyCompleted, 0)) AS LocationStock,
            a.QtyCompleted AS QtyCompleted,
            s.PlanDate AS PlanDate,
            s.ProdDate AS ProdDate,
            LOWER(a.Status) AS Status,
            COALESCE(NULLIF(TRIM(a.`Domain`), ''), IFNULL(CAST(a.tenant_id AS CHAR), '')) AS Domain,
            a.Urgent AS Urgent
        FROM WorkOrdMaster a
        LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum AND b.tenant_id = @TenantId
        LEFT JOIN ReplenishmentWeekPlan r
            ON a.WorkOrd = r.ProductionOrder
            AND CAST(a.`Domain` AS CHAR(64))  = CAST(r.factory_id AS CHAR(64)) 
        LEFT JOIN crm_seorder se ON se.bill_no = a.SalesJob AND se.tenant_id = @TenantId
        LEFT JOIN CustMaster cm ON cm.Cust = se.custom_no AND cm.tenant_id = @TenantId
        LEFT JOIN (
            SELECT `Domain`, WorkOrds, MIN(PlanDate) AS PlanDate, MIN(ProdDate) AS ProdDate
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId
            GROUP BY `Domain`, WorkOrds
        ) s ON CAST(a.`Domain` AS CHAR(64))  = CAST(s.`Domain` AS CHAR(64)) 
            AND a.WorkOrd  = s.WorkOrds 
        LEFT JOIN (
            SELECT morder_no, MAX(create_time) AS checktime
            FROM b_examine_result
            GROUP BY morder_no
        ) exm ON exm.morder_no = a.WorkOrd 
        LEFT JOIN WorkOrdInStorage ins
            ON a.WorkOrd  = ins.WorkOrd 
            AND ins.Remark  = '工单预留' 
        WHERE {innerWhere}
        """;

    // ══════════════════════════════════════════════════════════════
    // 编辑预览 GET /api/Production/scheduling/edit-preview
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单优先级编辑数据")]
    [HttpGet("scheduling/edit-preview")]
    public async Task<object> GetEditPreview([FromQuery] string workOrd, [FromQuery] string domain)
    {
        if (string.IsNullOrWhiteSpace(workOrd) || string.IsNullOrWhiteSpace(domain))
            throw Oops.Oh("工单号与公司域名不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        string sql = $"""
            SELECT
                a.WorkOrd,
                a.ItemNum,
                a.QtyOrded,
                a.Priority,
                a.LotSerial,
                IFNULL(a.Urgent, 0) AS Urgent,
                DATE(a.OrdDate) AS OrdDate,
                DATE(a.DueDate) AS DueDate
            FROM WorkOrdMaster a
            WHERE a.WorkOrd = @WorkOrd AND a.tenant_id = @TenantId
            LIMIT 1
            """;
        var row = (await _db.Ado.SqlQueryAsync<WorkOrderEditPreviewRow>(sql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId }))
            .FirstOrDefault() ?? throw Oops.Oh("工单不存在");
        return row;
    }

    // ══════════════════════════════════════════════════════════════
    // 查看 GET /api/Production/scheduling/view
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单查看（主表+工序+物料）")]
    [HttpGet("scheduling/view")]
    public async Task<object> GetView([FromQuery] string workOrd, [FromQuery] string domain)
    {
        if (string.IsNullOrWhiteSpace(workOrd) || string.IsNullOrWhiteSpace(domain))
            throw Oops.Oh("工单号与公司域名不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        string masterSql = $"""
            SELECT
                a.WorkOrd,
                a.QtyOrded,
                a.QtyCompleted,
                a.ItemNum,
                IFNULL(a.ItemName, b.Descr) AS ItemName,
                b.Descr1 AS ItemModel,
                DATE(a.OrdDate) AS OrdDate,
                DATE(a.DueDate) AS DueDate,
                LOWER(a.Status) AS Status,
                IFNULL(a.Remark, '') AS Remark
            FROM WorkOrdMaster a
            LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum AND b.tenant_id = a.tenant_id
            WHERE a.WorkOrd = @WorkOrd AND a.tenant_id = @TenantId
            LIMIT 1
            """;
        var master = (await _db.Ado.SqlQueryAsync<WorkOrderViewMasterRow>(masterSql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId }))
            .FirstOrDefault() ?? throw Oops.Oh("工单不存在");

        string routingSql = $"""
            SELECT
                r.OP AS Op,
                r.Descr AS Descr,
                IFNULL(r.ParentOp, '') AS ParentOp,
                IFNULL(r.MilestoneOp, 0) AS MilestoneOp,
                IFNULL(r.PackingQty, 0) AS PackingQty,
                IFNULL(r.QtyComplete, 0) AS QtyComplete,
                IFNULL(r.QtyReject, 0) AS QtyReject,
                IFNULL(r.QtyScrap, 0) AS QtyScrap,
                r.Status AS Status
            FROM WorkOrdRouting r
            WHERE r.WorkOrd = @WorkOrd AND r.tenant_id = @TenantId
            ORDER BY (r.OP + 0) ASC, r.OP ASC, r.Line ASC, r.ColumnNum ASC
            """;
        var routings = await _db.Ado.SqlQueryAsync<WorkOrderViewRoutingRow>(routingSql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId });

        string detailSql = $"""
            SELECT d.ItemNum, d.Op, d.QtyRequired, IFNULL(d.FrozenBOMQty, 0) AS FrozenBOMQty
            FROM WorkOrdDetail d
            WHERE d.WorkOrd = @WorkOrd AND d.tenant_id = @TenantId
            ORDER BY d.LineNum
            """;
        var details = await _db.Ado.SqlQueryAsync<WorkOrderViewDetailRow>(detailSql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId });

        return new { master, routings, details };
    }

    // ══════════════════════════════════════════════════════════════
    // 物料 / 工序明细列表（新标签页）
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单物料明细列表")]
    [HttpGet("scheduling/materials")]
    public async Task<object> GetMaterials([FromQuery] string workOrd, [FromQuery] string domain)
    {
        if (string.IsNullOrWhiteSpace(workOrd))
            throw Oops.Oh("工单号不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        string sql = $"""
            SELECT d.RecID, d.ItemNum, d.Op, d.QtyRequired, IFNULL(d.FrozenBOMQty, 0) AS FrozenBOMQty,
                   IFNULL(d.Location, '') AS Location, IFNULL(d.LotSerial, '') AS LotSerial, d.WorkOrd
            FROM WorkOrdDetail d
            WHERE d.WorkOrd = @WorkOrd AND d.tenant_id = @TenantId
            ORDER BY d.LineNum
            """;
        var list = await _db.Ado.SqlQueryAsync<WorkOrderViewDetailRow>(sql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId });
        return new { list };
    }

    [DisplayName("工单工序明细列表")]
    [HttpGet("scheduling/routings")]
    public async Task<object> GetRoutings([FromQuery] string workOrd, [FromQuery] string domain)
    {
        if (string.IsNullOrWhiteSpace(workOrd))
            throw Oops.Oh("工单号不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        string sql = $"""
            SELECT
                r.RecID, r.OP AS Op, r.Descr AS Descr,
                IFNULL(r.ParentOp, '') AS ParentOp,
                IFNULL(r.MilestoneOp, 0) AS MilestoneOp,
                IFNULL(r.PackingQty, 0) AS PackingQty,
                IFNULL(r.QtyComplete, 0) AS QtyComplete,
                IFNULL(r.QtyReject, 0) AS QtyReject,
                IFNULL(r.QtyScrap, 0) AS QtyScrap,
                r.Status AS Status, r.WorkOrd,
                IFNULL(r.WorkCtr, '') AS WorkCtr,
                IFNULL(r.ProdLine, '') AS ProdLine,
                IFNULL(r.StdSetupTime, 0) AS StdSetupTime,
                IFNULL(r.QtyOrded, 0) AS QtyOrded,
                IFNULL(r.ProcessOut, 0) AS ProcessOut,
                IFNULL(r.ProcessOutSupp, '') AS ProcessOutSupp,
                IFNULL(r.ProcessOutDay, 0) AS ProcessOutDay,
                IFNULL(r.Machine, '') AS Machine,
                IFNULL(r.ToolCode, '') AS ToolCode,
                IFNULL(r.RunCrew, 0) AS RunCrew,
                IFNULL(r.Engineer, '') AS Engineer,
                IFNULL(r.MachBdnRate, 0) AS MachBdnRate,
                IFNULL(r.WorkCode, '') AS WorkCode,
                IFNULL(r.Setup, 0) AS Setup,
                IFNULL(r.MachinesperOp, 0) AS MachinesperOp,
                IFNULL(r.Labor, 0) AS Labor,
                IFNULL(r.ChargeCode, '') AS ChargeCode,
                IFNULL(r.ItemNum, '') AS ItemNum
            FROM WorkOrdRouting r
            WHERE r.WorkOrd = @WorkOrd AND r.tenant_id = @TenantId
            ORDER BY (r.OP + 0) ASC, r.OP ASC, r.Line ASC, r.ColumnNum ASC
            """;
        var list = await _db.Ado.SqlQueryAsync<WorkOrderViewRoutingRow>(sql, new { WorkOrd = workOrd.Trim(), TenantId = tenantId });
        var summary = await QueryExecutionProgressAsync(workOrd.Trim(), domain?.Trim() ?? "", tenantId);
        return new { list, summary };
    }

    /// <summary>
    /// 工序执行进度汇总，与工单执行进度看板共用 <see cref="WorkOrderExecutionProgressSql"/> 口径。
    /// </summary>
    private async Task<WorkOrderExecutionProgress?> QueryExecutionProgressAsync(string workOrd, string domain, long tenantId)
    {
        var routingMatch = SqlWorkOrdChildMatchDomainOrTenant("r", "rm");
        string sql = $"""
            SELECT
                IFNULL(wm.QtyOrded, 0) AS QtyOrded,
                IFNULL(wm.QtyCompleted, 0) AS QtyCompleted,
                {WorkOrderExecutionProgressSql.Columns("wm", "curOp", "lastOp", "opAgg")}
            FROM WorkOrdMaster wm
            {WorkOrderExecutionProgressSql.Laterals(routingMatch)}
            WHERE {SqlWorkOrdMasterMatchDomainOrTenantAliased("wm")}
            LIMIT 1
            """;
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderExecutionProgress>(sql, new { WorkOrd = workOrd, Domain = domain, TenantId = tenantId });
        return rows.FirstOrDefault();
    }

    // ══════════════════════════════════════════════════════════════
    // 执行追踪 GET /api/Production/scheduling/trace
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单执行追踪")]
    [HttpGet("scheduling/trace")]
    public async Task<object> GetTrace([FromQuery] string workOrd, [FromQuery] string domain)
    {
        if (string.IsNullOrWhiteSpace(workOrd) || string.IsNullOrWhiteSpace(domain))
            throw Oops.Oh("工单号与公司域名不能为空");

        var w = workOrd.Trim();
        var d = domain.Trim();
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        string masterSql = $"""
            SELECT
                a.WorkOrd,
                a.QtyOrded,
                a.QtyCompleted,
                a.ItemNum,
                IFNULL(b.Descr, '') AS ItemName,
                IFNULL(b.Descr1, '') AS ItemModel,
                DATE(a.OrdDate) AS OrdDate,
                DATE(a.DueDate) AS DueDate,
                LOWER(a.Status) AS Status
            FROM WorkOrdMaster a
            LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum AND (b.tenant_id = a.tenant_id OR a.tenant_id IS NULL)
            WHERE a.WorkOrd = @WorkOrd AND a.tenant_id = @TenantId
            LIMIT 1
            """;
        var master = (await _db.Ado.SqlQueryAsync<WorkOrderTraceMasterRow>(masterSql, new { WorkOrd = w, TenantId = tenantId }))
            .FirstOrDefault() ?? throw Oops.Oh("工单不存在");

        var tab1 = await QueryTraceResourceCheckAsync(w, tenantId);
        var tab2 = await QueryTraceScheduleAsync(w, tenantId);
        var tab3Po = await QueryTracePoAsync(w, tenantId);
        var tab3Pr = await QueryTracePrAsync(w, tenantId);
        var tab4 = await QueryTraceNbrAsync(w, tenantId);
        var tab5 = await QueryTraceProdReportAsync(w, tenantId);
        var tab6 = await QueryTraceQualityAsync(w, tenantId);
        var tab7 = await QueryTraceReceiptAsync(w, tenantId);

        return new
        {
            master,
            resourceCheck = tab1,
            schedule = tab2,
            purchaseOrders = tab3Po,
            purchaseReqs = tab3Pr,
            picking = tab4,
            production = tab5,
            quality = tab6,
            receipt = tab7
        };
    }

    private async Task<List<TraceResourceRow>> QueryTraceResourceCheckAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY bce.id) AS Sno,
                CAST(bce.num AS CHAR) AS Num,
                bce.item_number AS ItemNumber,
                bce.item_name AS ItemName,
                bce.model AS Model,
                IFNULL(bce.unit, '') AS Unit,
                IFNULL(bce.bom_number, '') AS BomNumber,
                DATE_FORMAT(bce.kitting_time, '%Y-%m-%d') AS KittingTime,
                CASE bce.erp_cls
                    WHEN 0 THEN '配置类'
                    WHEN 1 THEN '自制'
                    WHEN 2 THEN '委外加工'
                    WHEN 3 THEN '外购'
                    WHEN 4 THEN '虚拟件'
                    ELSE ''
                END AS ErpClsName,
                CASE WHEN bce.backflush = 1 THEN '是' ELSE '否' END AS Backflush,
                CAST(bce.qty AS CHAR) AS Qty,
                CAST(bce.needCount AS CHAR) AS NeedCount
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.is_use = 1 AND bce.tenant_id = ber.tenant_id
            WHERE ber.morder_no = @WorkOrd
              AND ber.tenant_id = @TenantId
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.morder_no = @WorkOrd AND br.tenant_id = @TenantId
                  ORDER BY br.create_time DESC
                  LIMIT 1
              )
            ORDER BY bce.id
            """;
        return await _db.Ado.SqlQueryAsync<TraceResourceRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TraceScheduleRow>> QueryTraceScheduleAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY sch.PlanDate) AS Sno,
                sch.WorkOrds AS WorkOrds,
                DATE_FORMAT(sch.PlanDate, '%Y-%m-%d') AS WorkDate,
                IFNULL(sch.Line, '') AS Line,
                IFNULL(sch.Op, '') AS Op,
                CAST(IFNULL(sch.OrdQty, 0) AS CHAR) AS WorkQty,
                IFNULL(sch.ItemNum, '') AS ItemNum,
                IFNULL(im.Descr, '') AS Descr,
                IFNULL(im.Descr1, '') AS Descr1,
                DATE_FORMAT(sch.CreateTime, '%Y-%m-%d %H:%i:%s') AS CreateTime
            FROM PeriodSequenceDet sch
            LEFT JOIN ItemMaster im ON sch.ItemNum = im.ItemNum AND (im.tenant_id = sch.tenant_id OR sch.tenant_id IS NULL)
            WHERE sch.WorkOrds = @WorkOrd
              AND sch.tenant_id = @TenantId
            ORDER BY sch.PlanDate
            """;
        return await _db.Ado.SqlQueryAsync<TraceScheduleRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TracePoRow>> QueryTracePoAsync(string workOrd, long tenantId)
    {
        // 双路合并：
        //   ① PW（委外加工）：PurOrdMaster.WorkOrd 直连
        //   ② DO/PO（要货令/采购订单）：srm_pr_main.pr_billno = PurOrdDetail.Req
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY PoBillno) AS Sno,
                PoBillno, SupplierNo, SupplierName, PoTotal, PoPurchaser, State, PoSsendDate, CreateTime
            FROM (
                SELECT DISTINCT
                    pm.PurOrd AS PoBillno,
                    IFNULL(TRIM(pm.Supp), '') AS SupplierNo,
                    IFNULL(TRIM(s.SortName), '') AS SupplierName,
                    CAST(IFNULL(det.PoTotal, 0) AS CHAR) AS PoTotal,
                    IFNULL(TRIM(pm.Buyer), '') AS PoPurchaser,
                    CASE
                        WHEN IFNULL(LENGTH(pm.Status), 0) = 0 THEN '新增'
                        WHEN pm.Status = 'R' THEN '新增'
                        WHEN pm.Status = 'S' THEN '已下达'
                        WHEN pm.Status = 'C' THEN '关闭'
                        ELSE pm.Status
                    END AS State,
                    IFNULL(DATE_FORMAT(pm.OrdDate, '%Y-%m-%d'), '') AS PoSsendDate,
                    IFNULL(DATE_FORMAT(pm.CreateTime, '%Y-%m-%d %H:%i:%s'), '') AS CreateTime
                FROM PurOrdMaster pm
                LEFT JOIN SuppMaster s ON pm.Supp = s.Supp AND s.tenant_id = pm.tenant_id
                LEFT JOIN (
                    SELECT PurOrdRecID,
                           SUM(IFNULL(QtyOrded, 0) * IFNULL(PurCost, 0)) AS PoTotal
                    FROM PurOrdDetail
                    GROUP BY PurOrdRecID
                ) det ON det.PurOrdRecID = pm.RecID
                WHERE pm.tenant_id = @TenantId
                  AND IFNULL(pm.IsActive, 0) <= 1
                  AND (
                      -- ① PW：PurOrdMaster.WorkOrd 直连
                      pm.WorkOrd = @WorkOrd
                      OR
                      -- ② DO/PO：srm_pr_main.pr_billno → PurOrdDetail.Req
                      pm.RecID IN (
                          SELECT pd.PurOrdRecID
                          FROM PurOrdDetail pd
                          INNER JOIN srm_pr_main pr
                              ON (pr.pr_billno = pd.Req OR pr.SAP_pr_billno = pd.Req)
                              AND pr.tenant_id = pd.tenant_id
                          WHERE pr.pr_mono = @WorkOrd
                            AND pr.tenant_id = @TenantId
                            AND IFNULL(pr.state, 0) <> 0
                            AND IFNULL(pr.IsDeleted, 0) = 0
                      )
                  )
            ) AS t
            ORDER BY PoBillno
            """;
        return await _db.Ado.SqlQueryAsync<TracePoRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TracePrRow>> QueryTracePrAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY pm.pr_billno) AS Sno,
                pm.pr_billno AS PrBillno,
                IFNULL(ic.number, '') AS Number,
                IFNULL(pm.icitem_name, '') AS IcitemName,
                IFNULL(ic.model, '') AS Model,
                IFNULL(pm.pr_purchasenumber, '') AS PrPurchasenumber,
                IFNULL(pm.pr_purchasename, '') AS PrPurchasename,
                CAST(IFNULL(pm.pr_aqty, 0) AS CHAR) AS PrAqty,
                IFNULL(DATE_FORMAT(pm.pr_ssend_date, '%Y-%m-%d'), '') AS PrSsendDate,
                IFNULL(DATE_FORMAT(pm.pr_sarrive_date, '%Y-%m-%d'), '') AS PrSarriveDate,
                IFNULL(pm.pr_unit, '') AS PrUnit,
                IFNULL(pm.pr_purchaser, '') AS PrPurchaser
            FROM srm_pr_main pm
            LEFT JOIN ic_item ic ON pm.icitem_id = ic.Id
            WHERE pm.pr_mono = @WorkOrd
              AND pm.tenant_id = @TenantId
              AND IFNULL(pm.state, 0) <> 0
              AND pm.IsDeleted = 0
            ORDER BY pm.pr_billno
            """;
        return await _db.Ado.SqlQueryAsync<TracePrRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TraceNbrRow>> QueryTraceNbrAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                CAST(nd.Line AS CHAR) AS Line,
                nm.Nbr AS Nbr,
                nd.ItemNum AS ItemNum,
                IFNULL(im.Descr, '') AS Descr,
                IFNULL(im.Descr1, '') AS Descr1,
                IFNULL(nd.LocationFrom, '') AS LocationFrom,
                IFNULL(nd.LocationTo, '') AS LocationTo,
                CAST(IFNULL(nd.CurrQtyOpened, 0) AS CHAR) AS CurrQtyOpened,
                CAST(IFNULL(nd.QtyOrd, 0) AS CHAR) AS QtyOrd,
                CAST(IFNULL(nd.QtyFrom, 0) AS CHAR) AS QtyFrom,
                CAST(IFNULL(nd.QtyRec, 0) AS CHAR) AS QtyRec,
                IFNULL(nd.UM, '') AS Unit
            FROM NbrDetail nd
            LEFT JOIN NbrMaster nm ON nd.nbr = nm.nbr AND nm.tenant_id = nd.tenant_id
            LEFT JOIN ItemMaster im ON nd.ItemNum = im.ItemNum AND (im.tenant_id = nd.tenant_id OR nd.tenant_id IS NULL)
            WHERE nm.WorkOrd = @WorkOrd
              AND nm.Type = 'SM'
              AND nm.tenant_id = @TenantId
            ORDER BY nm.Nbr
            """;
        return await _db.Ado.SqlQueryAsync<TraceNbrRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TraceProdRow>> QueryTraceProdReportAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY ote.ProdDate DESC) AS Sno,
                ote.WorkOrd AS WorkOrd,
                IFNULL(ote.ItemNum, '') AS ItemNum,
                IFNULL(im.Descr, '') AS Descr,
                IFNULL(im.Descr1, '') AS Descr1,
                IFNULL(im.Um, '') AS Um,
                CAST(IFNULL(ote.QtyCompleted, 0) AS CHAR) AS QtyCompleted,
                CAST(IFNULL(ote.QtyReject, 0) AS CHAR) AS QtyReject,
                CAST(IFNULL(ote.QtyScrapped, 0) AS CHAR) AS QtyScrapped,
                IFNULL(DATE_FORMAT(ote.ProdDate, '%Y-%m-%d %H:%i:%s'), '') AS ProdDate,
                IFNULL(ote.Op, '') AS Op,
                IFNULL(ote.Name, '') AS Name
            FROM OpTransEmployee ote
            INNER JOIN mes_morder mo ON mo.morder_no = ote.WorkOrd AND mo.tenant_id = @TenantId
            LEFT JOIN ItemMaster im ON ote.ItemNum = im.ItemNum AND (im.tenant_id = mo.tenant_id OR mo.tenant_id IS NULL)
            WHERE ote.WorkOrd = @WorkOrd
            ORDER BY ote.ProdDate DESC
            """;
        return await _db.Ado.SqlQueryAsync<TraceProdRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TraceQualityRow>> QueryTraceQualityAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY mo.id) AS Sno,
                IFNULL(im.Um, '') AS Unit,
                IFNULL(mo.morder_no, '') AS MorderNo,
                CAST('' AS CHAR) AS WorkNumber,
                CAST('' AS CHAR) AS MorderProductionNumber,
                CAST('' AS CHAR) AS InspectionNumber,
                CAST('' AS CHAR) AS QualifiedNumber,
                CAST('' AS CHAR) AS InventoryNumber,
                IFNULL(mo.moentry_wrkcname, '') AS MoentryWrkcname,
                CAST('' AS CHAR) AS PlannerStartDate,
                CAST('' AS CHAR) AS PlannerEndDate,
                IFNULL(mo.moentry_prdname, '') AS MoentryPrdname,
                CAST('' AS CHAR) AS MorderFstate
            FROM mes_morder mo
            LEFT JOIN ItemMaster im ON mo.product_code = im.ItemNum AND (im.tenant_id = mo.tenant_id OR mo.tenant_id IS NULL)
            WHERE mo.morder_no = @WorkOrd
              AND mo.tenant_id = @TenantId
              AND mo.IsDeleted = 0
            ORDER BY mo.id
            """;
        return await _db.Ado.SqlQueryAsync<TraceQualityRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    private async Task<List<TraceReceiptRow>> QueryTraceReceiptAsync(string workOrd, long tenantId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY c.WorkOrd) AS Sno,
                CASE
                    WHEN LOWER(c.Status) = 'r' THEN '下达'
                    WHEN LOWER(c.Status) = 'c' THEN '关闭'
                    WHEN LOWER(c.Status) = 'w' THEN '投产'
                    ELSE IFNULL(c.Status, '')
                END AS Status,
                c.WorkOrd AS WorkOrd,
                IFNULL(im.Um, '') AS UM,
                CAST(IFNULL(c.QtyOrded, 0) AS CHAR) AS QtyOrded,
                CAST(IFNULL(compp.CompQty, 0) AS CHAR) AS CompQty,
                CAST('' AS CHAR) AS InspectionNumber,
                CAST('' AS CHAR) AS QualifiedNumber,
                CAST(IFNULL(a.qty_change, 0) AS CHAR) AS QtyChangeAdvance,
                IFNULL(DATE_FORMAT(c.OrdDate, '%Y-%m-%d'), '') AS OrdDate,
                IFNULL(DATE_FORMAT(c.DueDate, '%Y-%m-%d'), '') AS DueDate
            FROM WorkOrdMaster c
            LEFT JOIN mdp_std_inv_trans a ON a.work_ord = c.WorkOrd AND a.trans_type = 'rct-wo' AND IFNULL(a.qty_change, 0) > 0
                AND a.tenant_id = c.tenant_id
            LEFT JOIN ItemMaster im ON c.ItemNum = im.ItemNum AND (im.tenant_id = c.tenant_id OR c.tenant_id IS NULL)
            LEFT JOIN (
                SELECT WorkOrds, SUM(IFNULL(CompQty, 0)) AS CompQty
                FROM PeriodSequenceDet
                WHERE tenant_id = @TenantId
                GROUP BY WorkOrds
            ) compp ON c.WorkOrd = compp.WorkOrds
            LEFT JOIN mes_morder mo ON mo.morder_no = c.WorkOrd AND mo.IsDeleted = 0 AND mo.tenant_id = c.tenant_id
            WHERE c.WorkOrd = @WorkOrd AND c.tenant_id = @TenantId
            """;
        return await _db.Ado.SqlQueryAsync<TraceReceiptRow>(sql, new { WorkOrd = workOrd, TenantId = tenantId });
    }

    // ══════════════════════════════════════════════════════════════
    // 保存 POST /api/Production/scheduling/save
    // ══════════════════════════════════════════════════════════════
    [DisplayName("保存工单（数量/批次/优先级/加急）")]
    [HttpPost("scheduling/save")]
    public async Task<object> Save([FromBody] WorkOrderSchedulingSaveInput input)
    {
        var account = _userManager.Account ?? "system";
        var workOrd = input.WorkOrd.Trim();
        var domain = input.Domain.Trim();
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        // ── 1. 获取旧的 QtyOrded，用于判断数量是否变更 ──
        var oldQtyOrded = await _db.Ado.GetDecimalAsync(
            $"SELECT IFNULL(QtyOrded, 0) FROM WorkOrdMaster WHERE {SqlWorkOrdMasterMatchDomainOrTenant}",
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@Domain", domain),
            new SugarParameter("@TenantId", tenantId));

        var newQtyOrded = input.QtyOrded;
        var qtyChanged = newQtyOrded.HasValue && oldQtyOrded > 0 && newQtyOrded.Value != oldQtyOrded;

        // ── 2. 更新 WorkOrdMaster ──
        var pars = new List<SugarParameter>
        {
            new("@QtyOrded", input.QtyOrded ?? (object)DBNull.Value),
            new("@Priority", input.Priority ?? (object)DBNull.Value),
            new("@LotSerial", string.IsNullOrWhiteSpace(input.LotSerial) ? DBNull.Value : input.LotSerial.Trim()),
            new("@Urgent", input.Urgent ?? (object)DBNull.Value),
            new("@UpdateUser", account),
            new("@UpdateTime", DateTime.Now),
            new("@WorkOrd", workOrd),
            new("@Domain", domain),
            new("@TenantId", tenantId)
        };

        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET QtyOrded = COALESCE(@QtyOrded, QtyOrded),
                Priority = COALESCE(@Priority, Priority),
                LotSerial = COALESCE(@LotSerial, LotSerial),
                Urgent = COALESCE(@Urgent, Urgent),
                UpdateUser = @UpdateUser,
                UpdateTime = @UpdateTime
            WHERE {SqlWorkOrdMasterMatchDomainOrTenant}
            """,
            pars);

        if (n == 0)
            throw Oops.Oh("工单不存在或未更新");

        // ── 3. 数量变更时同步子表 ──
        if (qtyChanged)
        {
            var ratio = newQtyOrded!.Value / oldQtyOrded;
            var now = DateTime.Now;

            // 3a. WorkOrdDetail: 按比例调整 QtyRequired
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdDetail
                SET QtyRequired = ROUND(QtyRequired * @Ratio, 6),
                    UpdateUser = @User, UpdateTime = @Now
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                  AND IFNULL(IsActive, 0) = 1
                """,
                new SugarParameter("@Ratio", ratio),
                new SugarParameter("@User", account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", workOrd));

            // 3b. WorkOrdRouting: 同步 QtyOrded
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdRouting
                SET QtyOrded = @QtyOrded,
                    UpdateUser = @User, UpdateTime = @Now
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                  AND IFNULL(IsActive, 0) = 1
                """,
                new SugarParameter("@QtyOrded", newQtyOrded.Value),
                new SugarParameter("@User", account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", workOrd));

            // 3c. NbrDetail: 按比例调整 QtyOrd 和 CurrQtyOpened（仅下达/投产/暂停状态）
            var woStatus = await _db.Ado.GetStringAsync(
                """
                SELECT IFNULL(LOWER(TRIM(Status)), '') FROM WorkOrdMaster
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd LIMIT 1
                """,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", workOrd));

            if (woStatus is "r" or "w" or "s")
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE NbrDetail
                    SET QtyOrd = ROUND(QtyOrd * @Ratio, 6),
                        CurrQtyOpened = ROUND(CurrQtyOpened * @Ratio, 6),
                        UpdateUser = @User, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND Type = 'SM'
                      AND Nbr IN (
                          SELECT Nbr FROM NbrMaster
                          WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
                            AND IFNULL(IsActive, 0) = 1
                      )
                      AND IFNULL(IsActive, 0) = 1
                      AND IFNULL(Status, '') <> 'C'
                    """,
                    new SugarParameter("@Ratio", ratio),
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@WorkOrd", workOrd));
            }
        }

        return new { message = "保存成功" };
    }

    // ══════════════════════════════════════════════════════════════
    // 状态 PATCH POST /api/Production/scheduling/status
    // ══════════════════════════════════════════════════════════════
    [DisplayName("更新工单状态")]
    [HttpPost("scheduling/status")]
    public async Task<object> PatchStatus([FromBody] WorkOrderStatusPatchInput input)
    {
        var account = _userManager.Account ?? "system";
        var status = input.Status.Trim().ToLowerInvariant();
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter>
        {
            new("@Status", status),
            new("@UpdateUser", account),
            new("@UpdateTime", DateTime.Now),
            new("@Id", input.Id),
            new("@Domain", input.Domain.Trim()),
            new("@TenantId", tenantId)
        };
        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET Status = @Status,
                UpdateUser = @UpdateUser,
                UpdateTime = @UpdateTime
            WHERE {SqlWorkOrdMasterMatchRecIdAndDomainOrTenant}
            """,
            pars);
        if (n == 0)
            throw Oops.Oh("工单不存在或未更新");

        // 双模式出站：状态确认类变更写 Outbox（WMS_API/MES 占位）
        if (status is "released" or "confirm" or "confirmed" or "c" or "r")
            await TryEnqueueScheduleDispatchOutboxAsync(input.Id, input.Domain.Trim(), status);

        return new { message = "状态已更新" };
    }

    private async Task TryEnqueueScheduleDispatchOutboxAsync(long recId, string domain, string status)
    {
        try
        {
            long tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
            var row = await _db.Ado.SqlQuerySingleAsync<WorkOrderInfo>(
                $"""
                SELECT WorkOrd, IFNULL(NULLIF(TRIM(`Domain`),''), CAST(IFNULL(tenant_id,0) AS CHAR)) AS Domain
                FROM WorkOrdMaster
                WHERE {SqlWorkOrdMasterMatchRecIdAndDomainOrTenant}
                LIMIT 1
                """,
                new List<SugarParameter>
                {
                    new("@Id", recId),
                    new("@Domain", domain),
                    new("@TenantId", tenantId)
                });
            if (row == null || string.IsNullOrWhiteSpace(row.WorkOrd)) return;

            string idem = $"{row.Domain}#{row.WorkOrd}#{status}";
            var payload = JsonSerializer.Serialize(new
            {
                path = "/mes/schedule/dispatch",
                method = "POST",
                body = new
                {
                    domain = row.Domain,
                    workOrd = row.WorkOrd,
                    status,
                    action = "S2_SCHEDULE_DISPATCH"
                }
            });
            var exists = await _db.Ado.GetIntAsync(
                "SELECT COUNT(1) FROM mdp_outbox WHERE tenant_id=@tid AND target_source_code='WMS_API' AND action_code='S2_SCHEDULE_DISPATCH' AND idem_key=@idem",
                new List<SugarParameter> { new("@tid", tenantId), new("@idem", idem) }) > 0;
            if (exists) return;

            await _db.Insertable(new MdpOutbox
            {
                TenantId = tenantId,
                TargetSourceCode = "WMS_API",
                ActionCode = "S2_SCHEDULE_DISPATCH",
                IdemKey = idem,
                PayloadJson = payload,
                Status = 0,
                RetryCount = 0,
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            }).ExecuteCommandAsync();
        }
        catch { /* Outbox 失败不影响状态更新 */ }
    }

    // ══════════════════════════════════════════════════════════════
    // 工单关闭 POST /api/Production/scheduling/close
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单关闭")]
    [HttpPost("scheduling/close")]
    public async Task<object> Close([FromBody] WorkOrderCloseInput input)
    {
        var ids = input.Ids.Trim();
        if (string.IsNullOrWhiteSpace(ids))
            throw Oops.Oh("工单主键不能为空");

        try
        {
            // 开启事务
            _db.Ado.BeginTran();

            var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);

            // 根据 RecID 获取工单号和域信息
            // 注意：存储过程 pr_MES_CloseWorkOrders 需要两个参数：域和工单号（竖线分隔）
            // 但当前调用只传递 RecID，因此需要根据 RecID 查询对应的工单和域

            var inParams = GetInParameters(ids).ToList();
            inParams.Add(new SugarParameter("@TenantId", tenantId));
            var workOrders = await _db.Ado.SqlQueryAsync<WorkOrderInfo>(
                "SELECT WorkOrd AS WorkOrd, COALESCE(NULLIF(TRIM(`Domain`), ''), CAST(tenant_id AS CHAR(20))) AS Domain FROM WorkOrdMaster WHERE tenant_id = @TenantId AND RecID IN (" + BuildInClause(ids) + ")",
                inParams.ToArray());
            
            if (!workOrders.Any())
                throw Oops.Oh("未找到对应的工单");

            // 为每个域执行关闭操作
            var domains = workOrders.Select(x => x.Domain).Distinct();
            foreach (var domain in domains)
            {
                // 获取该域的工单号列表（竖线分隔）
                var domainWorkOrds = string.Join("|", workOrders.Where(x => x.Domain == domain).Select(x => x.WorkOrd));
                
                // 执行关闭逻辑（模拟存储过程）；租户一律用严格解析的 tenantId，不再用 Domain 冒充
                await ExecuteCloseWorkOrders(tenantId, domain, domainWorkOrds);
            }

            _db.Ado.CommitTran();
            return new { message = "工单关闭成功" };
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            return new { message = $"工单关闭失败: {ex.Message}" };
        }
    }

    /// <summary>
    /// 构建 IN 子句参数
    /// </summary>
    private string BuildInClause(string ids)
    {
        var idList = ids.Split(',').Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x));
        return string.Join(",", idList.Select((_, i) => $"@Id{i}"));
    }

    /// <summary>
    /// 获取 IN 子句参数
    /// </summary>
    private SugarParameter[] GetInParameters(string ids)
    {
        var idList = ids.Split(',').Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x));
        return idList.Select((id, i) => new SugarParameter($"@Id{i}", id)).ToArray();
    }

    /// <summary>
    /// 执行关闭工单逻辑（模拟存储过程 pr_MES_CloseWorkOrders）；tenant_id 一律用严格解析的 tenantId，Domain 仅用于 NbrMaster.Domain 列匹配。
    /// </summary>
    private async Task ExecuteCloseWorkOrders(long tenantId, string domain, string workOrds)
    {
        if (string.IsNullOrWhiteSpace(workOrds))
            return;

        // 存储过程使用竖线分隔，但FIND_IN_SET需要逗号分隔
        var workOrdsForFindInSet = workOrds.Replace("|", ",");
        
        // 1. 更新工单状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE WorkOrdMaster SET Status = 'C', UpdateUser = 'proc' WHERE tenant_id = @TenantId AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 2. 更新领料单状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrMaster SET Status = 'C' WHERE Type = 'SM' AND tenant_id = @TenantId AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 3. 更新领料单明细状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrDetail SET Status = 'C' WHERE tenant_id = @TenantId AND Type = 'SM' AND Nbr IN (SELECT Nbr FROM NbrMaster WHERE Type = 'SM' AND Domain = @Domain AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0)",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@Domain", domain),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 4. 更新退料单状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrMaster SET Status = 'C' WHERE Type = 'WOD' AND tenant_id = @TenantId AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 5. 更新退料单明细状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrDetail SET Status = 'C' WHERE tenant_id = @TenantId AND Type = 'WOD' AND Nbr IN (SELECT Nbr FROM NbrMaster WHERE Type = 'WOD' AND Domain = @Domain AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0)",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@Domain", domain),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 6. 删除库存占用
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM ic_item_stockoccupy WHERE tenant_id = @TenantId AND FIND_IN_SET(morder_mo, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 7. 删除PO占用
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM srm_po_occupy WHERE tenant_id = @TenantId AND FIND_IN_SET(morder_mo, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

        // 8. 删除工单占用
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM mes_mooccupy WHERE tenant_id = @TenantId AND FIND_IN_SET(moo_mo, @WorkOrds) > 0",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));
    }

    // ══════════════════════════════════════════════════════════════
    // 同步工艺路线 POST /api/Production/scheduling/sync-routing
    // ══════════════════════════════════════════════════════════════
    [DisplayName("同步工艺路线")]
    [HttpPost("scheduling/sync-routing")]
    public async Task<object> SyncRouting([FromBody] WorkOrderKeyInput input)
    {
        var workOrd = input.WorkOrd.Trim();
        var domain = input.Domain.Trim();
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var createUser = (_userManager.Account ?? "system").Trim();
        if (createUser.Length > 24)
            createUser = createUser[..24];

        var pars = new List<SugarParameter>
        {
            new("@WorkOrd", workOrd),
            new("@Domain", domain),
            new("@CreateUser", createUser),
            new("@TenantId", tenantId)
        };

        await _db.Ado.BeginTranAsync();
        try
        {
            await _db.Ado.ExecuteCommandAsync(
                $"""
                DELETE FROM WorkOrdRouting rr
                WHERE {SqlWorkOrdChildMatchDomainOrTenant("rr", "m")}
                """,
                pars);

            // 业务口径：由 RoutingOpDetail + ProdLineDetail 重算工单工艺路线（MySQL）。
            // G2 映射与 WorkOrderRoutingSyncService 对齐：StdRunTime=1/Rate；RunCrew←ProdLineDetail.RunCrew；
            // Line/ProdLine←TRIM(Line)；MachBdnRate 不再承载 Rate。
            await _db.Ado.ExecuteCommandAsync(
                $"""
                INSERT INTO WorkOrdRouting (
                    `Descr`, `Domain`, `ChargeCode`, `Machine`, `RunCrew`, `MilestoneOp`, `OP`, `StdOp`, `ItemNum`, `WorkCtr`,
                    `Ufld1`, `Ufld3`, `Setup`, `MachinesperOp`, `Labor`, `RunTime`, `MachBdnRate`, `WorkCode`, `StdSetupTime`, `Engineer`,
                    `WorkOrd`, `WorkOrdMasterRecID`, `ERPfld1`, `QtyOrded`, `ProcessOut`, `ProcessOutDay`, `ProcessOutSupp`,
                    `IsActive`, `Status`, `ProdLine`, `Line`, `StdRunTime`,
                    `CreateTime`, `CreateUser`, `tenant_id`,`CommentIndex`,`WaitTime`,`QtyComplete`,`ParentOp`
                )
                SELECT
                    a.`Descr`,
                    LEFT(TRIM(COALESCE(NULLIF(TRIM(IFNULL(w.`Domain`, '')), ''), NULLIF(TRIM(IFNULL(a.`Domain`, '')), ''), ' ')), 8),
                    '',
                    b.`InternalEquipmentCode`,
                    CAST(IFNULL(NULLIF(TRIM(IFNULL(b.`RunCrew`, '')), ''), '0') AS DECIMAL(18, 4)),
                    CAST(IFNULL(a.`MilestoneOp`, 0) AS UNSIGNED),
                    IFNULL(a.`Op`, 0),
                    a.`StdOp`,
                    a.`RoutingCode`,
                    LEFT(IFNULL(b.`Site`, ''), 8),
                    '',
                    '',
                    IFNULL(a.`UDeci1`, 0),
                    IFNULL(a.`UDeci2`, 0),
                    IFNULL(a.`UDeci3`, 0),
                    IFNULL(a.`UDeci3`, 0) / 3600.0,
                    0,
                    b.`OpType`,
                    IFNULL(b.`SetupTime`, 0),
                    b.`SkillNo`,
                    w.`WorkOrd`,
                    w.`RecID`,
                    w.`ERPfld1`,
                    IFNULL(w.`QtyOrded`, 0),
                    CAST(IFNULL(a.`UDeci5`, 0) AS SIGNED),
                    IFNULL(a.`ProcessOutDay`, 0),
                    a.`ProcessOutSupp`,
                    b'1',
                    'r',
                    LEFT(TRIM(IFNULL(b.`Line`, '')), 8),
                    LEFT(TRIM(IFNULL(b.`Line`, '')), 8),
                    CASE WHEN IFNULL(b.`Rate`, 0) > 0 THEN 1.0 / b.`Rate` ELSE NULL END,
                    NOW(3),
                    @CreateUser,
                    w.`tenant_id`,CAST(IFNULL(a.`MilestoneOp`, 0) AS UNSIGNED),0,0,0
                FROM WorkOrdMaster w
                LEFT JOIN RoutingOpDetail a ON w.`ItemNum` = a.`RoutingCode`
                LEFT JOIN ProdLineDetail b ON a.`RoutingCode` = b.`Part` AND a.`Op` = b.`Op`
                WHERE w.`WorkOrd` = @WorkOrd
                  AND {SqlWorkOrdMasterMatchDomainOrTenantAliased("w")}
                  AND IFNULL(a.`IsActive`, 0) = 1
                  AND a.`MilestoneOp` IS NOT NULL
                """,
                pars);

            await _db.Ado.CommitTranAsync();
        }
        catch (Exception ex)
        {
            await _db.Ado.RollbackTranAsync();
            throw Oops.Oh($"同步工艺路线失败：{ex.Message}");
        }

        return new { message = "同步工艺路线已执行" };
    }

    // ══════════════════════════════════════════════════════════════
    // 下拉数据 GET endpoints
    // ══════════════════════════════════════════════════════════════

    /// <summary>
    /// 获取工单下拉选项（状态为空/P/R/W/S 且类型为空/RW/TEST/TCN）
    /// </summary>
    [DisplayName("工单下拉选项")]
    [HttpGet("scheduling/work-ord-options")]
    public async Task<object> GetWorkOrdOptions()
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var list = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT WorkOrd FROM WorkOrdMaster
            WHERE UPPER(IFNULL(Status, '')) IN ('', 'P', 'R', 'W', 'S')
              AND UPPER(IFNULL(Typed, '')) IN ('', 'RW', 'TEST', 'TCN')
              AND tenant_id = @TenantId
            ORDER BY WorkOrd DESC
            """,
            new { TenantId = tenantId });
        return new { list };
    }

    /// <summary>
    /// 获取工作中心下拉选项
    /// </summary>
    [DisplayName("工作中心下拉选项")]
    [HttpGet("scheduling/work-ctr-options")]
    public async Task<object> GetWorkCtrOptions()
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var list = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT WorkCtr, IFNULL(Descr, '') AS Descr
            FROM WorkCtrMaster
            WHERE tenant_id = @TenantId
            ORDER BY WorkCtr
            """,
            new { TenantId = tenantId });
        return new { list };
    }

    /// <summary>
    /// 获取生产线下拉选项
    /// </summary>
    [DisplayName("生产线下拉选项")]
    [HttpGet("scheduling/line-options")]
    public async Task<object> GetLineOptions()
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var list = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT Line, IFNULL(`Describe`, '') AS `Describe`
            FROM LineMaster
            WHERE tenant_id = @TenantId
            ORDER BY Line
            """,
            new { TenantId = tenantId });
        return new { list };
    }

    /// <summary>
    /// 获取供应商下拉选项
    /// </summary>
    [DisplayName("供应商下拉选项")]
    [HttpGet("scheduling/supp-options")]
    public async Task<object> GetSuppOptions()
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var list = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT Supp, TRIM(CONCAT(Supp, ' ', IFNULL(SortName, ''))) AS SuppName
            FROM SuppMaster
            WHERE tenant_id = @TenantId
            ORDER BY Supp
            """,
            new { TenantId = tenantId });
        return new { list };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单物料明细 CRUD
    // ══════════════════════════════════════════════════════════════

    /// <summary>
    /// 保存工单物料明细（新增/编辑）
    /// </summary>
    [DisplayName("保存工单物料明细")]
    [HttpPost("scheduling/material-save")]
    public async Task<object> SaveMaterialDetail([FromBody] WorkOrdDetailSaveInput input)
    {
        if (string.IsNullOrWhiteSpace(input.WorkOrd))
            throw Oops.Oh("工单编号不能为空");
        if (string.IsNullOrWhiteSpace(input.ItemNum))
            throw Oops.Oh("物料号不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;
        var workOrd = input.WorkOrd.Trim();

        // 获取工单主记录信息和域
        var master = await _db.Ado.SqlQueryAsync<dynamic>(
            $"""
            SELECT RecID, UPPER(IFNULL(Status, '')) AS Status, IFNULL(`Domain`, '') AS Domain
            FROM WorkOrdMaster
            WHERE WorkOrd = @WorkOrd AND tenant_id = @TenantId
            LIMIT 1
            """,
            new { WorkOrd = workOrd, TenantId = tenantId });
        if (master == null || master.Count == 0)
            throw Oops.Oh("工单不存在");
        var masterRow = master[0];
        var masterRecId = (long)masterRow.RecID;
        var woStatus = (string)masterRow.Status;
        var domain = (string)masterRow.Domain ?? "";

        // 查找物料是否存在于ItemMaster
        var itemExists = await _db.Ado.SqlQueryAsync<int>(
            """
            SELECT COUNT(1) FROM ItemMaster
            WHERE ItemNum = @ItemNum AND tenant_id = @TenantId
            """,
            new { ItemNum = input.ItemNum.Trim(), TenantId = tenantId });
        if (itemExists[0] == 0)
            throw Oops.Oh($"物料 {input.ItemNum} 在基础数据中不存在，不能新增");

        // 查找物料的Descr和Unit
        var itemInfo = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT IFNULL(Descr, '') AS ItemName, IFNULL(UM, '') AS UM
            FROM ItemMaster
            WHERE ItemNum = @ItemNum AND tenant_id = @TenantId
            LIMIT 1
            """,
            new { ItemNum = input.ItemNum.Trim(), TenantId = tenantId });
        var itemName = itemInfo.Count > 0 ? (string)itemInfo[0].ItemName : "";
        var um = itemInfo.Count > 0 ? (string)itemInfo[0].UM : "";

        var isP = woStatus == "P";

        if (input.RecID == 0)
        {
            // ── 新增 ──
            // 检查工单中是否已有此物料
            var dupCheck = await _db.Ado.SqlQueryAsync<int>(
                """
                SELECT COUNT(1) FROM WorkOrdDetail
                WHERE WorkOrd = @WorkOrd AND ItemNum = @ItemNum
                  AND tenant_id = @TenantId
                """,
                new { WorkOrd = workOrd, ItemNum = input.ItemNum.Trim(), TenantId = tenantId });
            if (dupCheck[0] > 0)
                throw Oops.Oh($"工单 {workOrd} 中已有物料 {input.ItemNum}，不能重复新增");

            // 获取下一个RecID
            var nextIdRows = await _db.Ado.SqlQueryAsync<long>(
                "SELECT IFNULL(MAX(RecID), 0) + 1 FROM WorkOrdDetail");
            var newRecId = nextIdRows[0];

            // 获取下一个LineNum
            var lineNumRows = await _db.Ado.SqlQueryAsync<int>(
                "SELECT IFNULL(MAX(LineNum), 0) + 1 FROM WorkOrdDetail WHERE WorkOrd = @WorkOrd",
                new { WorkOrd = workOrd });
            var lineNum = lineNumRows[0];

            // 插入WorkOrdDetail
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO WorkOrdDetail (
                    RecID, WorkOrdMasterRecID, `Domain`, WorkOrd, LineNum, ItemNum, Op,
                    Location, QtyRequired, QtyPosted, UM, LotSerial,
                    IsActive, IsConfirm, CreateUser, CreateTime, tenant_id
                ) VALUES (
                    @RecID, @MasterRecId, @Domain, @WorkOrd, @LineNum, @ItemNum, @Op,
                    @Location, @QtyRequired, 0, @UM, @LotSerial,
                    1, 0, @User, @Now, @TenantId
                )
                """,
                new SugarParameter("@RecID", newRecId),
                new SugarParameter("@MasterRecId", masterRecId),
                new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
                new SugarParameter("@WorkOrd", workOrd),
                new SugarParameter("@LineNum", lineNum),
                new SugarParameter("@ItemNum", input.ItemNum.Trim()),
                new SugarParameter("@Op", (object?)input.Op ?? DBNull.Value),
                new SugarParameter("@QtyRequired", input.QtyRequired),
                new SugarParameter("@Location", (object?)input.Location ?? DBNull.Value),
                new SugarParameter("@LotSerial", (object?)input.LotSerial ?? DBNull.Value),
                new SugarParameter("@UM", string.IsNullOrEmpty(um) ? DBNull.Value : um),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId));

            // 非P状态：Mes 入队回写 165；Local 仍写本库 NbrDetail
            if (!isP)
            {
                if (!await _pickNbrSync.TryEnqueueAfterPlanChangeAsync(tenantId, workOrd, domain, account))
                {
                    await CreateOrUpdateNbrDetailForMaterial(workOrd, domain, input.ItemNum.Trim(),
                        input.QtyRequired, um, itemName, tenantId, account, now, isNew: true);
                }
            }

            return new { message = "新增成功", recID = newRecId };
        }
        else
        {
            // ── 编辑 ──
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdDetail
                SET ItemNum = @ItemNum, Op = @Op, QtyRequired = @QtyRequired,
                    Location = @Location, LotSerial = @LotSerial,
                    UM = @UM,
                    UpdateUser = @User, UpdateTime = @Now
                WHERE RecID = @RecID AND tenant_id = @TenantId
                """,
                new SugarParameter("@ItemNum", input.ItemNum.Trim()),
                new SugarParameter("@Op", (object?)input.Op ?? DBNull.Value),
                new SugarParameter("@QtyRequired", input.QtyRequired),
                new SugarParameter("@Location", (object?)input.Location ?? DBNull.Value),
                new SugarParameter("@LotSerial", (object?)input.LotSerial ?? DBNull.Value),
                new SugarParameter("@UM", string.IsNullOrEmpty(um) ? DBNull.Value : um),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@RecID", input.RecID),
                new SugarParameter("@TenantId", tenantId));

            // 非P状态：Mes 入队回写 165；Local 仍写本库 NbrDetail
            if (!isP)
            {
                if (!await _pickNbrSync.TryEnqueueAfterPlanChangeAsync(tenantId, workOrd, domain, account))
                {
                    await CreateOrUpdateNbrDetailForMaterial(workOrd, domain, input.ItemNum.Trim(),
                        input.QtyRequired, um, itemName, tenantId, account, now, isNew: false);
                }
            }

            return new { message = "保存成功" };
        }
    }

    /// <summary>
    /// 删除工单物料明细
    /// </summary>
    [DisplayName("删除工单物料明细")]
    [HttpPost("scheduling/material-delete")]
    public async Task<object> DeleteMaterialDetail([FromBody] WorkOrdDetailDeleteInput input)
    {
        if (input.RecID == 0)
            throw Oops.Oh("主键不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        // 获取物料明细信息用于NbrDetail同步
        var detailRows = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT d.WorkOrd, d.ItemNum, IFNULL(d.`Domain`, '') AS Domain, m.Status
            FROM WorkOrdDetail d
            LEFT JOIN WorkOrdMaster m ON d.WorkOrd = m.WorkOrd AND d.tenant_id = m.tenant_id
            WHERE d.RecID = @RecID AND d.tenant_id = @TenantId
            """,
            new { RecID = input.RecID, TenantId = tenantId });
        if (detailRows == null || detailRows.Count == 0)
            throw Oops.Oh("物料明细不存在");

        var row = detailRows[0];
        var workOrd = (string)row.WorkOrd;
        var itemNum = (string)row.ItemNum;
        var domain = (string)row.Domain ?? "";
        var woStatus = (string)(row.Status ?? "");

        // 删除WorkOrdDetail
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM WorkOrdDetail WHERE RecID = @RecID AND tenant_id = @TenantId",
            new SugarParameter("@RecID", input.RecID),
            new SugarParameter("@TenantId", tenantId));

        // 非P状态：Mes 入队关 165 未领行；Local 仍删本库 NbrDetail
        if (woStatus != "P")
        {
            if (!await _pickNbrSync.TryEnqueueAfterPlanChangeAsync(tenantId, workOrd, domain, _userManager.Account ?? "system"))
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    DELETE FROM NbrDetail
                    WHERE WorkOrd = @WorkOrd AND ItemNum = @ItemNum AND Type = 'SM'
                      AND tenant_id = @TenantId
                    """,
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@ItemNum", itemNum),
                    new SugarParameter("@TenantId", tenantId));
            }
        }

        return new { message = "删除成功" };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单工序明细 CRUD
    // ══════════════════════════════════════════════════════════════

    /// <summary>
    /// 保存工单工序明细（新增/编辑）
    /// </summary>
    [DisplayName("保存工单工序明细")]
    [HttpPost("scheduling/routing-save")]
    public async Task<object> SaveRoutingDetail([FromBody] WorkOrdRoutingSaveInput input)
    {
        if (string.IsNullOrWhiteSpace(input.WorkOrd))
            throw Oops.Oh("工单编号不能为空");
        if (string.IsNullOrWhiteSpace(input.OP))
            throw Oops.Oh("工序不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;
        var workOrd = input.WorkOrd.Trim();

        // 获取工单主记录信息和域
        var master = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT RecID, IFNULL(`Domain`, '') AS Domain, IFNULL(ERPfld1, '') AS ERPfld1,
                   IFNULL(QtyOrded, 0) AS QtyOrded
            FROM WorkOrdMaster
            WHERE WorkOrd = @WorkOrd AND tenant_id = @TenantId
            LIMIT 1
            """,
            new { WorkOrd = workOrd, TenantId = tenantId });
        if (master == null || master.Count == 0)
            throw Oops.Oh("工单不存在");
        var masterRow = master[0];
        var masterRecId = (long)masterRow.RecID;
        var domain = (string)masterRow.Domain ?? "";
        var erpfld1 = (string)masterRow.ERPfld1 ?? "";
        var qtyOrded = (decimal)masterRow.QtyOrded;

        // 检查是否已有相同工序编号（新增时检查，编辑时排除自身）
        var dupCheck = await _db.Ado.SqlQueryAsync<int>(
            $"""
            SELECT COUNT(1) FROM WorkOrdRouting
            WHERE WorkOrd = @WorkOrd AND OP = @OP
              AND tenant_id = @TenantId
              {(input.RecID > 0 ? "AND RecID <> @RecID" : "")}
            """,
            new { WorkOrd = workOrd, OP = input.OP.Trim(), TenantId = tenantId, RecID = input.RecID });
        if (dupCheck[0] > 0)
            throw Oops.Oh($"工单 {workOrd} 已有相同工序编号 {input.OP}，不允许保存");

        // 检查后序是否已有完成数量（编辑时）
        if (input.RecID > 0)
        {
            var currentOpRows = await _db.Ado.SqlQueryAsync<int>(
                $"""
                SELECT CAST(IFNULL(OP, 0) AS SIGNED) FROM WorkOrdRouting
                WHERE RecID = @RecID AND tenant_id = @TenantId
                """,
                new { RecID = input.RecID, TenantId = tenantId });
            if (currentOpRows.Count > 0)
            {
                var currentOp = currentOpRows[0];
                var afterComplete = await _db.Ado.SqlQueryAsync<int>(
                    """
                    SELECT COUNT(1) FROM WorkOrdRouting
                    WHERE WorkOrd = @WorkOrd
                      AND CAST(IFNULL(OP, 0) AS SIGNED) > @CurrentOp
                      AND IFNULL(QtyComplete, 0) > 0
                      AND tenant_id = @TenantId
                    """,
                    new { WorkOrd = workOrd, CurrentOp = currentOp, TenantId = tenantId });
                if (afterComplete[0] > 0)
                    throw Oops.Oh("后序已有完成数量，不允许保存");
            }
        }

        if (input.RecID == 0)
        {
            // ── 新增 ──
            var nextIdRows = await _db.Ado.SqlQueryAsync<long>(
                "SELECT IFNULL(MAX(RecID), 0) + 1 FROM WorkOrdRouting");
            var newRecId = nextIdRows[0];

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO WorkOrdRouting (
                    RecID, `Descr`, `Domain`, `ChargeCode`, `Machine`, `RunCrew`, `MilestoneOp`,
                    `OP`, `ItemNum`, `WorkCtr`, `Setup`, `MachinesperOp`, `Labor`,
                    `MachBdnRate`, `WorkCode`, `StdSetupTime`, `Engineer`,
                    `WorkOrd`, `WorkOrdMasterRecID`, `ERPfld1`, `QtyOrded`,
                    `ProcessOut`, `ProcessOutDay`, `ProcessOutSupp`,
                    `IsActive`, `Status`, `ProdLine`, `ToolCode`,
                    `CreateTime`, `CreateUser`, `tenant_id`, `CommentIndex`, `WaitTime`, `QtyComplete`, `ParentOp`
                ) VALUES (
                    @RecID, @Descr, @Domain, @ChargeCode, @Machine, @RunCrew, @MilestoneOp,
                    @OP, @ItemNum, @WorkCtr, @Setup, @MachinesperOp, @Labor,
                    @MachBdnRate, @WorkCode, @StdSetupTime, @Engineer,
                    @WorkOrd, @MasterRecId, @ERPfld1, @QtyOrded,
                    @ProcessOut, @ProcessOutDay, @ProcessOutSupp,
                    b'1', 'r', @ProdLine, @ToolCode,
                    @Now, @User, @TenantId, 0, 0, 0, 0
                )
                """,
                new SugarParameter("@RecID", newRecId),
                new SugarParameter("@Descr", (object?)input.Descr ?? DBNull.Value),
                new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
                new SugarParameter("@ChargeCode", (object?)input.ChargeCode ?? DBNull.Value),
                new SugarParameter("@Machine", (object?)input.Machine ?? DBNull.Value),
                new SugarParameter("@RunCrew", input.RunCrew),
                new SugarParameter("@MilestoneOp", input.MilestoneOp ? 1 : 0),
                new SugarParameter("@OP", input.OP.Trim()),
                new SugarParameter("@ItemNum", (object?)input.ItemNum ?? DBNull.Value),
                new SugarParameter("@WorkCtr", (object?)input.WorkCtr ?? DBNull.Value),
                new SugarParameter("@Setup", input.Setup),
                new SugarParameter("@MachinesperOp", input.MachinesperOp),
                new SugarParameter("@Labor", input.Labor),
                new SugarParameter("@MachBdnRate", input.MachBdnRate),
                new SugarParameter("@WorkCode", (object?)input.WorkCode ?? DBNull.Value),
                new SugarParameter("@StdSetupTime", input.StdSetupTime),
                new SugarParameter("@Engineer", (object?)input.Engineer ?? DBNull.Value),
                new SugarParameter("@WorkOrd", workOrd),
                new SugarParameter("@MasterRecId", masterRecId),
                new SugarParameter("@ERPfld1", erpfld1),
                new SugarParameter("@QtyOrded", input.QtyOrded > 0 ? input.QtyOrded : qtyOrded),
                new SugarParameter("@ProcessOut", input.ProcessOut ? 1 : 0),
                new SugarParameter("@ProcessOutDay", input.ProcessOutDay),
                new SugarParameter("@ProcessOutSupp", (object?)input.ProcessOutSupp ?? DBNull.Value),
                new SugarParameter("@ProdLine", (object?)input.ProdLine ?? DBNull.Value),
                new SugarParameter("@ToolCode", (object?)input.ToolCode ?? DBNull.Value),
                new SugarParameter("@Now", now),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@TenantId", tenantId));

            return new { message = "新增成功", recID = newRecId };
        }
        else
        {
            // ── 编辑 ──
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdRouting
                SET `Descr` = @Descr, `ChargeCode` = @ChargeCode, `Machine` = @Machine,
                    `RunCrew` = @RunCrew, `MilestoneOp` = @MilestoneOp, `OP` = @OP,
                    `ItemNum` = @ItemNum, `WorkCtr` = @WorkCtr,
                    `Setup` = @Setup, `MachinesperOp` = @MachinesperOp, `Labor` = @Labor,
                    `MachBdnRate` = @MachBdnRate, `WorkCode` = @WorkCode,
                    `StdSetupTime` = @StdSetupTime, `Engineer` = @Engineer,
                    `QtyOrded` = @QtyOrded, `ProcessOut` = @ProcessOut,
                    `ProcessOutDay` = @ProcessOutDay, `ProcessOutSupp` = @ProcessOutSupp,
                    `ProdLine` = @ProdLine, `ToolCode` = @ToolCode,
                    `UpdateUser` = @User, `UpdateTime` = @Now
                WHERE RecID = @RecID AND tenant_id = @TenantId
                """,
                new SugarParameter("@Descr", (object?)input.Descr ?? DBNull.Value),
                new SugarParameter("@ChargeCode", (object?)input.ChargeCode ?? DBNull.Value),
                new SugarParameter("@Machine", (object?)input.Machine ?? DBNull.Value),
                new SugarParameter("@RunCrew", input.RunCrew),
                new SugarParameter("@MilestoneOp", input.MilestoneOp ? 1 : 0),
                new SugarParameter("@OP", input.OP.Trim()),
                new SugarParameter("@ItemNum", (object?)input.ItemNum ?? DBNull.Value),
                new SugarParameter("@WorkCtr", (object?)input.WorkCtr ?? DBNull.Value),
                new SugarParameter("@Setup", input.Setup),
                new SugarParameter("@MachinesperOp", input.MachinesperOp),
                new SugarParameter("@Labor", input.Labor),
                new SugarParameter("@MachBdnRate", input.MachBdnRate),
                new SugarParameter("@WorkCode", (object?)input.WorkCode ?? DBNull.Value),
                new SugarParameter("@StdSetupTime", input.StdSetupTime),
                new SugarParameter("@Engineer", (object?)input.Engineer ?? DBNull.Value),
                new SugarParameter("@QtyOrded", input.QtyOrded),
                new SugarParameter("@ProcessOut", input.ProcessOut ? 1 : 0),
                new SugarParameter("@ProcessOutDay", input.ProcessOutDay),
                new SugarParameter("@ProcessOutSupp", (object?)input.ProcessOutSupp ?? DBNull.Value),
                new SugarParameter("@ProdLine", (object?)input.ProdLine ?? DBNull.Value),
                new SugarParameter("@ToolCode", (object?)input.ToolCode ?? DBNull.Value),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@RecID", input.RecID),
                new SugarParameter("@TenantId", tenantId));

            return new { message = "保存成功" };
        }
    }

    /// <summary>
    /// 删除工单工序明细
    /// </summary>
    [DisplayName("删除工单工序明细")]
    [HttpPost("scheduling/routing-delete")]
    public async Task<object> DeleteRoutingDetail([FromBody] WorkOrdRoutingDeleteInput input)
    {
        if (input.RecID == 0)
            throw Oops.Oh("主键不能为空");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        // 检查后序是否已有完成数量
        var detailRows = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT r.WorkOrd, CAST(IFNULL(r.OP, 0) AS SIGNED) AS OP, IFNULL(r.`Domain`, '') AS Domain
            FROM WorkOrdRouting r
            WHERE r.RecID = @RecID AND r.tenant_id = @TenantId
            """,
            new { RecID = input.RecID, TenantId = tenantId });
        if (detailRows == null || detailRows.Count == 0)
            throw Oops.Oh("工序明细不存在");

        var row = detailRows[0];
        var workOrd = (string)row.WorkOrd;
        var currentOp = (int)row.OP;
        var domain = (string)row.Domain ?? "";

        var afterComplete = await _db.Ado.SqlQueryAsync<int>(
            """
            SELECT COUNT(1) FROM WorkOrdRouting
            WHERE WorkOrd = @WorkOrd
              AND CAST(IFNULL(OP, 0) AS SIGNED) > @CurrentOp
              AND IFNULL(QtyComplete, 0) > 0
              AND tenant_id = @TenantId
            """,
            new { WorkOrd = workOrd, CurrentOp = currentOp, TenantId = tenantId });
        if (afterComplete[0] > 0)
            throw Oops.Oh("后序已有完成数量，不允许删除");

        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM WorkOrdRouting WHERE RecID = @RecID AND tenant_id = @TenantId",
            new SugarParameter("@RecID", input.RecID),
            new SugarParameter("@TenantId", tenantId));

        return new { message = "删除成功" };
    }

    // ══════════════════════════════════════════════════════════════
    // 辅助方法
    // ══════════════════════════════════════════════════════════════

    /// <summary>
    /// 为物料明细创建或更新领料单NbrDetail
    /// </summary>
    private async Task CreateOrUpdateNbrDetailForMaterial(
        string workOrd, string domain, string itemNum, decimal qtyRequired,
        string um, string itemName, long tenantId, string account, DateTime now, bool isNew)
    {
        // 查找此工单此物料的SM类型领料单明细
        var existRows = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT nd.RecID, nd.NbrRecID
            FROM NbrDetail nd
            WHERE nd.WorkOrd = @WorkOrd AND nd.ItemNum = @ItemNum AND nd.Type = 'SM'
              AND nd.tenant_id = @TenantId
            """,
            new { WorkOrd = workOrd, ItemNum = itemNum, TenantId = tenantId });

        if (existRows == null || existRows.Count == 0)
        {
            // 没有领料单明细，创建一条
            // 先查找此工单是否有SM类型的领料单主记录
            var nbrMasterRows = await _db.Ado.SqlQueryAsync<dynamic>(
                """
                SELECT RecID, Nbr FROM NbrMaster
                WHERE WorkOrd = @WorkOrd AND Type = 'SM' AND tenant_id = @TenantId
                ORDER BY RecID DESC LIMIT 1
                """,
                new { WorkOrd = workOrd, TenantId = tenantId });

            long nbrRecId = 0;
            string nbrNo = "";

            if (nbrMasterRows == null || nbrMasterRows.Count == 0)
            {
                // 创建领料单主记录
                var nextMasterIdRows = await _db.Ado.SqlQueryAsync<long>(
                    "SELECT IFNULL(MAX(RecID), 0) + 1 FROM NbrMaster");
                nbrRecId = nextMasterIdRows[0];
                nbrNo = $"SM-{workOrd}-{DateTime.Now:yyyyMMddHHmmss}";

                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO NbrMaster (
                        RecID, `Domain`, Type, Nbr, WorkOrd, `Date`,
                        Status, IsActive, CreateUser, CreateTime, tenant_id
                    ) VALUES (
                        @RecID, @Domain, 'SM', @Nbr, @WorkOrd, @Now,
                        '', 1, @User, @Now, @TenantId
                    )
                    """,
                    new SugarParameter("@RecID", nbrRecId),
                    new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
                    new SugarParameter("@Nbr", nbrNo),
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                    new SugarParameter("@TenantId", tenantId));
            }
            else
            {
                nbrRecId = (long)nbrMasterRows[0].RecID;
                nbrNo = (string)nbrMasterRows[0].Nbr;
            }

            // 创建领料单明细
            var nextDetailIdRows = await _db.Ado.SqlQueryAsync<long>(
                "SELECT IFNULL(MAX(RecID), 0) + 1 FROM NbrDetail");
            var newDetailId = nextDetailIdRows[0];

            var maxLineRows = await _db.Ado.SqlQueryAsync<short>(
                "SELECT IFNULL(MAX(Line), 0) + 1 FROM NbrDetail WHERE NbrRecID = @NbrRecID AND tenant_id = @TenantId",
                new { NbrRecID = nbrRecId, TenantId = tenantId });
            var newLine = maxLineRows[0];

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO NbrDetail (
                    RecID, `Domain`, Type, Nbr, Line, ItemNum, Dimension1, Dimension2,
                    LocationFrom, LocationTo, QtyFrom, QtyTo, QtyOrd, QtyRec,
                    CurrQtyOpened, UM, WorkOrd, ItemName, Status,
                    IsActive, IsConfirm, IsChanged, BusinessID, NbrRecID,
                    CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                ) VALUES (
                    @RecID, @Domain, 'SM', @Nbr, @Line, @ItemNum, '', '',
                    '', '', 0, 0, @QtyOrd, 0,
                    @QtyOrd, @UM, @WorkOrd, @ItemName, '',
                    1, 0, 1, 0, @NbrRecID,
                    @User, @Now, @User, @Now, @TenantId
                )
                """,
                new SugarParameter("@RecID", newDetailId),
                new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
                new SugarParameter("@Nbr", nbrNo),
                new SugarParameter("@Line", newLine),
                new SugarParameter("@ItemNum", itemNum),
                new SugarParameter("@QtyOrd", qtyRequired),
                new SugarParameter("@UM", string.IsNullOrEmpty(um) ? DBNull.Value : um),
                new SugarParameter("@WorkOrd", workOrd),
                new SugarParameter("@ItemName", string.IsNullOrEmpty(itemName) ? DBNull.Value : itemName),
                new SugarParameter("@NbrRecID", nbrRecId),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId));
        }
        else
        {
            // 更新已有的领料单明细
            var ndRecId = (long)existRows[0].RecID;
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE NbrDetail
                SET QtyOrd = @QtyOrd, CurrQtyOpened = @QtyOrd,
                    UM = @UM, ItemName = @ItemName,
                    UpdateUser = @User, UpdateTime = @Now
                WHERE RecID = @RecID AND tenant_id = @TenantId
                """,
                new SugarParameter("@QtyOrd", qtyRequired),
                new SugarParameter("@UM", string.IsNullOrEmpty(um) ? DBNull.Value : um),
                new SugarParameter("@ItemName", string.IsNullOrEmpty(itemName) ? DBNull.Value : itemName),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@RecID", ndRecId),
                new SugarParameter("@TenantId", tenantId));
        }
    }

    // ══════════════════════════════════════════════════════════════
    // 加急 POST /api/Production/scheduling/urgent
    // ══════════════════════════════════════════════════════════════
    [DisplayName("设置加急")]
    [HttpPost("scheduling/urgent")]
    public async Task<object> SetUrgent([FromBody] WorkOrderUrgentInput input)
    {
        var account = _userManager.Account ?? "system";
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET Urgent = @Urgent,
                UpdateUser = @UpdateUser,
                UpdateTime = @UpdateTime
            WHERE {SqlWorkOrdMasterMatchDomainOrTenant}
            """,
            new List<SugarParameter>
            {
                new("@Urgent", input.Urgent),
                new("@UpdateUser", account),
                new("@UpdateTime", DateTime.Now),
                new("@WorkOrd", input.WorkOrd.Trim()),
                new("@Domain", input.Domain.Trim()),
                new("@TenantId", tenantId)
            });
        if (n == 0)
            throw Oops.Oh("工单不存在");
        return new { message = input.Urgent == 2 ? "已设为特急" : "已设为加急" };
    }

    // ══════════════════════════════════════════════════════════════
    // 甘特图数据 GET /api/Production/scheduling/gantt-data
    // ══════════════════════════════════════════════════════════════
    /// <summary>
    /// 订单生产排程看板 — 甘特图数据（工单 + 工序级别，含计划/实际开工完工日期、物料齐套率、交期）
    /// </summary>
    [DisplayName("甘特图数据")]
    [HttpGet("scheduling/gantt-data")]
    public async Task<object> GetGanttData([FromQuery] string? workOrd, [FromQuery] string? startDate, [FromQuery] string? endDate)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var where = new List<string>
        {
            "w.tenant_id = @TenantId",
            "IFNULL(w.Status, '') <> ''",
            "LOWER(w.Status) IN ('w', 'r', 'p')"
        };

        if (!string.IsNullOrWhiteSpace(workOrd))
        {
            where.Add("w.WorkOrd LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{workOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(startDate))
        {
            where.Add("COALESCE(plan.PlanStartDate, sm.WorkDate) >= @StartDate");
            pars.Add(new SugarParameter("@StartDate", startDate.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(endDate))
        {
            where.Add("COALESCE(plan.PlanEndDate, sm.WorkDate) <= @EndDate");
            pars.Add(new SugarParameter("@EndDate", endDate.Trim()));
        }

        // 主查询：工单信息 + 工序排产 + 计划日期范围 + 齐套率
        var sql = $"""
            SELECT
                w.WorkOrd,
                w.ItemNum,
                IFNULL(w.ItemName, im.Descr) AS ItemName,
                im.Descr1 AS ItemModel,
                w.QtyOrded,
                DATE(w.DueDate) AS DueDate,
                LOWER(w.Status) AS Status,
                COALESCE(NULLIF(TRIM(w.`Domain`), ''), IFNULL(CAST(w.tenant_id AS CHAR), '')) AS Domain,
                IFNULL(nm.MFRate, 0) AS MFRate,
                sm.Op,
                IFNULL(sm.OpDescr, '') AS OpDescr,
                DATE(COALESCE(plan.PlanStartDate, sm.WorkDate)) AS PlanStartDate,
                DATE(COALESCE(plan.PlanEndDate, sm.WorkDate)) AS PlanEndDate,
                DATE(sm.WorkStartTime) AS ActualStartDate,
                DATE(sm.WorkEndTime) AS ActualEndDate,
                IFNULL(sm.WorkQty, 0) AS WorkQty,
                IFNULL(w.QtyCompleted, 0) AS QtyCompleted,
                LOWER(IFNULL(routing.OpStatus, '')) AS OpStatus
            FROM ScheduleResultOpMaster sm
            INNER JOIN WorkOrdMaster w
                ON w.WorkOrd = sm.WorkOrd
                AND (
                    TRIM(IFNULL(w.`Domain`, '')) = TRIM(IFNULL(sm.`Domain`, ''))
                    OR w.tenant_id = sm.tenant_id
                )
            LEFT JOIN ItemMaster im ON w.ItemNum = im.ItemNum AND (im.tenant_id = w.tenant_id OR w.tenant_id IS NULL)
            LEFT JOIN (
                SELECT WorkOrd, MAX(MFRate) AS MFRate
                FROM NbrMaster
                WHERE tenant_id = @TenantId AND Type = 'SM'
                GROUP BY WorkOrd
            ) nm ON nm.WorkOrd = sm.WorkOrd
            LEFT JOIN (
                SELECT WorkOrds, Op, MIN(PlanDate) AS PlanStartDate, MAX(PlanDate) AS PlanEndDate
                FROM PeriodSequenceDet
                WHERE tenant_id = @TenantId AND IFNULL(IsActive, 1) = 1
                GROUP BY WorkOrds, Op
            ) plan ON plan.WorkOrds = sm.WorkOrd AND plan.Op = sm.Op
            LEFT JOIN (
                SELECT WorkOrd, CAST(OP AS UNSIGNED) AS OpNum, MAX(Status) AS OpStatus
                FROM WorkOrdRouting
                WHERE tenant_id = @TenantId
                GROUP BY WorkOrd, OP
            ) routing ON routing.WorkOrd = sm.WorkOrd AND routing.OpNum = CAST(sm.Op AS UNSIGNED)
            WHERE {string.Join(" AND ", where)}
            ORDER BY w.WorkOrd ASC, CAST(sm.Op AS UNSIGNED) ASC
            """;

        var rows = await _db.Ado.SqlQueryAsync<GanttOpRow>(sql, pars);
        return new { list = rows };
    }

    private sealed class GanttOpRow
    {
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public string? ItemModel { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
        public string? Domain { get; set; }
        public decimal? MFRate { get; set; }
        public int? Op { get; set; }
        public string? OpDescr { get; set; }
        public DateTime? PlanStartDate { get; set; }
        public DateTime? PlanEndDate { get; set; }
        public DateTime? ActualStartDate { get; set; }
        public DateTime? ActualEndDate { get; set; }
        public decimal? WorkQty { get; set; }
        public decimal? QtyCompleted { get; set; }
        public string? OpStatus { get; set; }
    }

    // ══════════════════════════════════════════════════════════════
    // 产线生产排程看板 GET /api/Production/scheduling/line-gantt-data
    // ══════════════════════════════════════════════════════════════
    /// <summary>
    /// 产线生产排程看板 — 甘特图数据（按产线分组，含工单/工序/计划实际日期）
    /// </summary>
    [DisplayName("产线甘特图数据")]
    [HttpGet("scheduling/line-gantt-data")]
    public async Task<object> GetLineGanttData([FromQuery] string? prodLine, [FromQuery] string? startDate, [FromQuery] string? endDate)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var where = new List<string>
        {
            "w.tenant_id = @TenantId",
            "IFNULL(w.Status, '') <> ''",
            "LOWER(w.Status) IN ('w', 'r', 'p')"
        };

        if (!string.IsNullOrWhiteSpace(prodLine))
        {
            where.Add("IFNULL(routing.ProdLine, '') LIKE @ProdLine");
            pars.Add(new SugarParameter("@ProdLine", $"%{prodLine.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(startDate))
        {
            where.Add("COALESCE(plan.PlanStartDate, sm.WorkDate) >= @StartDate");
            pars.Add(new SugarParameter("@StartDate", startDate.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(endDate))
        {
            where.Add("COALESCE(plan.PlanEndDate, sm.WorkDate) <= @EndDate");
            pars.Add(new SugarParameter("@EndDate", endDate.Trim()));
        }

        var sql = $"""
            SELECT
                IFNULL(routing.ProdLine, '') AS ProdLine,
                w.WorkOrd,
                w.ItemNum,
                IFNULL(w.ItemName, im.Descr) AS ItemName,
                im.Descr1 AS ItemModel,
                w.QtyOrded,
                DATE(w.DueDate) AS DueDate,
                LOWER(w.Status) AS Status,
                COALESCE(NULLIF(TRIM(w.`Domain`), ''), IFNULL(CAST(w.tenant_id AS CHAR), '')) AS Domain,
                IFNULL(nm.MFRate, 0) AS MFRate,
                sm.Op,
                IFNULL(sm.OpDescr, '') AS OpDescr,
                DATE(COALESCE(plan.PlanStartDate, sm.WorkDate)) AS PlanStartDate,
                DATE(COALESCE(plan.PlanEndDate, sm.WorkDate)) AS PlanEndDate,
                DATE(sm.WorkStartTime) AS ActualStartDate,
                DATE(sm.WorkEndTime) AS ActualEndDate,
                IFNULL(sm.WorkQty, 0) AS WorkQty,
                IFNULL(w.QtyCompleted, 0) AS QtyCompleted,
                LOWER(IFNULL(routing.OpStatus, '')) AS OpStatus
            FROM ScheduleResultOpMaster sm
            INNER JOIN WorkOrdMaster w
                ON w.WorkOrd = sm.WorkOrd
                AND (
                    TRIM(IFNULL(w.`Domain`, '')) = TRIM(IFNULL(sm.`Domain`, ''))
                    OR w.tenant_id = sm.tenant_id
                )
            LEFT JOIN ItemMaster im ON w.ItemNum = im.ItemNum AND (im.tenant_id = w.tenant_id OR w.tenant_id IS NULL)
            LEFT JOIN (
                SELECT WorkOrd, MAX(MFRate) AS MFRate
                FROM NbrMaster
                WHERE tenant_id = @TenantId AND Type = 'SM'
                GROUP BY WorkOrd
            ) nm ON nm.WorkOrd = sm.WorkOrd
            LEFT JOIN (
                SELECT WorkOrds, Op, MIN(PlanDate) AS PlanStartDate, MAX(PlanDate) AS PlanEndDate
                FROM PeriodSequenceDet
                WHERE tenant_id = @TenantId AND IFNULL(IsActive, 1) = 1
                GROUP BY WorkOrds, Op
            ) plan ON plan.WorkOrds = sm.WorkOrd AND plan.Op = sm.Op
            LEFT JOIN (
                SELECT WorkOrd, CAST(OP AS UNSIGNED) AS OpNum,
                       MAX(Status) AS OpStatus,
                       MAX(ProdLine) AS ProdLine
                FROM WorkOrdRouting
                WHERE tenant_id = @TenantId
                GROUP BY WorkOrd, OP
            ) routing ON routing.WorkOrd = sm.WorkOrd AND routing.OpNum = CAST(sm.Op AS UNSIGNED)
            WHERE {string.Join(" AND ", where)}
            ORDER BY IFNULL(routing.ProdLine, '') ASC, w.WorkOrd ASC, CAST(sm.Op AS UNSIGNED) ASC
            """;

        var rows = await _db.Ado.SqlQueryAsync<LineGanttOpRow>(sql, pars);
        return new { list = rows };
    }

    private sealed class LineGanttOpRow
    {
        public string? ProdLine { get; set; }
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public string? ItemModel { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
        public string? Domain { get; set; }
        public decimal? MFRate { get; set; }
        public int? Op { get; set; }
        public string? OpDescr { get; set; }
        public DateTime? PlanStartDate { get; set; }
        public DateTime? PlanEndDate { get; set; }
        public DateTime? ActualStartDate { get; set; }
        public DateTime? ActualEndDate { get; set; }
        public decimal? WorkQty { get; set; }
        public decimal? QtyCompleted { get; set; }
        public string? OpStatus { get; set; }
    }

    // ──────────────── 行类型 ────────────────

    private sealed class WorkOrderSchedulingListRow
    {
        public int Id { get; set; }
        public string? Priority { get; set; }
        public string? WorkOrd { get; set; }
        public string? LotSerial { get; set; }
        public string? IssueSite { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? LocationStock { get; set; }
        public decimal? QtyCompleted { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? ProdDate { get; set; }
        public string? Status { get; set; }
        public string? Domain { get; set; }
        public int? Urgent { get; set; }
    }

    // ══════════════════════════════════════════════════════════════
    // Input/Output DTOs
    // ══════════════════════════════════════════════════════════════

    public class WorkOrdDetailSaveInput
    {
        public long RecID { get; set; }
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public string? Op { get; set; }
        public decimal QtyRequired { get; set; }
        public string? Location { get; set; }
        public string? LotSerial { get; set; }
    }

    public class WorkOrdDetailDeleteInput
    {
        public long RecID { get; set; }
    }

    public class WorkOrdRoutingSaveInput
    {
        public long RecID { get; set; }
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public string? OP { get; set; }
        public string? Descr { get; set; }
        public string? WorkCtr { get; set; }
        public string? ProdLine { get; set; }
        public decimal StdSetupTime { get; set; }
        public decimal QtyOrded { get; set; }
        public bool MilestoneOp { get; set; } = true;
        public bool ProcessOut { get; set; }
        public string? ProcessOutSupp { get; set; }
        public decimal ProcessOutDay { get; set; }
        public string? Machine { get; set; }
        public string? ToolCode { get; set; }
        public decimal RunCrew { get; set; }
        public string? Engineer { get; set; }
        public decimal MachBdnRate { get; set; }
        public string? WorkCode { get; set; }
        public decimal Setup { get; set; }
        public decimal MachinesperOp { get; set; }
        public decimal Labor { get; set; }
        public string? ChargeCode { get; set; }
    }

    public class WorkOrdRoutingDeleteInput
    {
        public long RecID { get; set; }
    }

    private sealed class WorkOrderEditPreviewRow
    {
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public decimal? QtyOrded { get; set; }
        public string? Priority { get; set; }
        public string? LotSerial { get; set; }
        public int Urgent { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
    }

    private sealed class WorkOrderViewMasterRow
    {
        public string? WorkOrd { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? QtyCompleted { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public string? ItemModel { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
        public string? Remark { get; set; }
    }

    private sealed class WorkOrderViewRoutingRow
    {
        public long RecID { get; set; }
        public string? Op { get; set; }
        public string? Descr { get; set; }
        public string? ParentOp { get; set; }
        public int MilestoneOp { get; set; }
        public decimal PackingQty { get; set; }
        public decimal QtyComplete { get; set; }
        public decimal QtyReject { get; set; }
        public decimal QtyScrap { get; set; }
        public string? Status { get; set; }
        public string? WorkOrd { get; set; }
        public string? WorkCtr { get; set; }
        public string? ProdLine { get; set; }
        public decimal StdSetupTime { get; set; }
        public decimal QtyOrded { get; set; }
        public int ProcessOut { get; set; }
        public string? ProcessOutSupp { get; set; }
        public decimal ProcessOutDay { get; set; }
        public string? Machine { get; set; }
        public string? ToolCode { get; set; }
        public decimal RunCrew { get; set; }
        public string? Engineer { get; set; }
        public decimal MachBdnRate { get; set; }
        public string? WorkCode { get; set; }
        public decimal Setup { get; set; }
        public decimal MachinesperOp { get; set; }
        public decimal Labor { get; set; }
        public string? ChargeCode { get; set; }
        public string? ItemNum { get; set; }
    }

    private sealed class WorkOrderViewDetailRow
    {
        public long RecID { get; set; }
        public string? ItemNum { get; set; }
        public string? Op { get; set; }
        public decimal? QtyRequired { get; set; }
        public decimal FrozenBOMQty { get; set; }
        public string? Location { get; set; }
        public string? LotSerial { get; set; }
        public string? WorkOrd { get; set; }
    }

    private sealed class WorkOrderTraceMasterRow
    {
        public string? WorkOrd { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? QtyCompleted { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public string? ItemModel { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
    }

    private sealed class TraceResourceRow
    {
        public long Sno { get; set; }
        public string? Num { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Model { get; set; }
        public string? Unit { get; set; }
        public string? BomNumber { get; set; }
        public string? KittingTime { get; set; }
        public string? ErpClsName { get; set; }
        public string? Backflush { get; set; }
        public string? Qty { get; set; }
        public string? NeedCount { get; set; }
    }

    private sealed class TraceScheduleRow
    {
        public long Sno { get; set; }
        public string? WorkOrds { get; set; }
        public string? WorkDate { get; set; }
        public string? Line { get; set; }
        public string? Op { get; set; }
        public string? WorkQty { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public string? CreateTime { get; set; }
    }

    private sealed class TracePoRow
    {
        public long Sno { get; set; }
        public string? PoBillno { get; set; }
        public string? SupplierNo { get; set; }
        public string? SupplierName { get; set; }
        public string? PoTotal { get; set; }
        public string? PoPurchaser { get; set; }
        public string? State { get; set; }
        public string? PoSsendDate { get; set; }
        public string? CreateTime { get; set; }
    }

    private sealed class TracePrRow
    {
        public long Sno { get; set; }
        public string? PrBillno { get; set; }
        public string? Number { get; set; }
        public string? IcitemName { get; set; }
        public string? Model { get; set; }
        public string? PrPurchasenumber { get; set; }
        public string? PrPurchasename { get; set; }
        public string? PrAqty { get; set; }
        public string? PrSsendDate { get; set; }
        public string? PrSarriveDate { get; set; }
        public string? PrUnit { get; set; }
        public string? PrPurchaser { get; set; }
    }

    private sealed class TraceNbrRow
    {
        public string? Line { get; set; }
        public string? Nbr { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public string? LocationFrom { get; set; }
        public string? LocationTo { get; set; }
        public string? CurrQtyOpened { get; set; }
        public string? QtyOrd { get; set; }
        public string? QtyFrom { get; set; }
        public string? QtyRec { get; set; }
        public string? Unit { get; set; }
    }

    private sealed class TraceProdRow
    {
        public long Sno { get; set; }
        public string? WorkOrd { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public string? Um { get; set; }
        public string? QtyCompleted { get; set; }
        public string? QtyReject { get; set; }
        public string? QtyScrapped { get; set; }
        public string? ProdDate { get; set; }
        public string? Op { get; set; }
        public string? Name { get; set; }
    }

    private sealed class TraceQualityRow
    {
        public long Sno { get; set; }
        public string? Unit { get; set; }
        public string? MorderNo { get; set; }
        public string? WorkNumber { get; set; }
        public string? MorderProductionNumber { get; set; }
        public string? InspectionNumber { get; set; }
        public string? QualifiedNumber { get; set; }
        public string? InventoryNumber { get; set; }
        public string? MoentryWrkcname { get; set; }
        public string? PlannerStartDate { get; set; }
        public string? PlannerEndDate { get; set; }
        public string? MoentryPrdname { get; set; }
        public string? MorderFstate { get; set; }
    }

    private sealed class TraceReceiptRow
    {
        public long Sno { get; set; }
        public string? Status { get; set; }
        public string? WorkOrd { get; set; }
        public string? UM { get; set; }
        public string? QtyOrded { get; set; }
        public string? CompQty { get; set; }
        public string? InspectionNumber { get; set; }
        public string? QualifiedNumber { get; set; }
        public string? QtyChangeAdvance { get; set; }
        public string? OrdDate { get; set; }
        public string? DueDate { get; set; }
    }
}