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

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

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 工单工序排产服务 🏭
/// 路由前缀：/api/Production/scheduling/...
/// </summary>
[ApiDescriptionSettings(Order = 265, Description = "工单工序排产")]
[Route("api/Production")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderSchedulingService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <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 回传）。
    /// </summary>
    private const string SqlWorkOrdMasterMatchDomainOrTenant = """
        WorkOrd = @WorkOrd 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）。</summary>
    private static string SqlWorkOrdMasterMatchDomainOrTenantAliased(string alias) => $"""
        {alias}.WorkOrd = @WorkOrd 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>子表（工序/明细）按工单号 + 域或主表租户匹配。</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 (
                      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」匹配（状态更新等）。</summary>
    private const string SqlWorkOrdMasterMatchRecIdAndDomainOrTenant = """
        RecID = @Id 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 = AidopTenantHelper.Resolve(App.HttpContext);
        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 
        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 
        LEFT JOIN CustMaster cm ON cm.Cust = se.custom_no 
        LEFT JOIN (
            SELECT `Domain`, WorkOrds, MIN(PlanDate) AS PlanDate, MIN(ProdDate) AS ProdDate
            FROM PeriodSequenceDet
            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("工单号与公司域名不能为空");

        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 {SqlWorkOrdMasterMatchDomainOrTenantAliased("a")}
            LIMIT 1
            """;
        var row = (await _db.Ado.SqlQueryAsync<WorkOrderEditPreviewRow>(sql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() }))
            .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("工单号与公司域名不能为空");

        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
            WHERE {SqlWorkOrdMasterMatchDomainOrTenantAliased("a")}
            LIMIT 1
            """;
        var master = (await _db.Ado.SqlQueryAsync<WorkOrderViewMasterRow>(masterSql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() }))
            .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 {SqlWorkOrdChildMatchDomainOrTenant("r", "m")}
            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(), Domain = domain.Trim() });

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

        return new { master, routings, details };
    }

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

        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 {SqlWorkOrdChildMatchDomainOrTenant("d", "m")}
            ORDER BY d.LineNum
            """;
        var list = await _db.Ado.SqlQueryAsync<WorkOrderViewDetailRow>(sql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() });
        return new { list };
    }

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

        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 {SqlWorkOrdChildMatchDomainOrTenant("r", "m")}
            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(), Domain = domain.Trim() });
        var summary = await QueryExecutionProgressAsync(workOrd.Trim(), domain.Trim());
        return new { list, summary };
    }

    /// <summary>
    /// 工序执行进度汇总，与工单执行进度看板共用 <see cref="WorkOrderExecutionProgressSql"/> 口径。
    /// </summary>
    private async Task<WorkOrderExecutionProgress?> QueryExecutionProgressAsync(string workOrd, string domain)
    {
        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 });
        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();

        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
            WHERE {SqlWorkOrdMasterMatchDomainOrTenantAliased("a")}
            LIMIT 1
            """;
        var master = (await _db.Ado.SqlQueryAsync<WorkOrderTraceMasterRow>(masterSql, new { WorkOrd = w, Domain = d }))
            .FirstOrDefault() ?? throw Oops.Oh("工单不存在");

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

        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, string domain)
    {
        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
            WHERE ber.morder_no = @WorkOrd
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.morder_no = @WorkOrd
                  ORDER BY br.create_time DESC
                  LIMIT 1
              )
            ORDER BY bce.id
            """;
        return await _db.Ado.SqlQueryAsync<TraceResourceRow>(sql, new { WorkOrd = workOrd });
    }

    private async Task<List<TraceScheduleRow>> QueryTraceScheduleAsync(string workOrd, string domain)
    {
        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
            WHERE sch.WorkOrds = @WorkOrd
              AND CAST(sch.`Domain` AS CHAR(64)) = CAST(@Domain AS CHAR(64))
            ORDER BY sch.PlanDate
            """;
        return await _db.Ado.SqlQueryAsync<TraceScheduleRow>(sql, new { WorkOrd = workOrd, Domain = domain });
    }

    private async Task<List<TracePoRow>> QueryTracePoAsync(string workOrd, string domain)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY t.po_billno) AS Sno,
                t.po_billno AS PoBillno,
                t.supplier_no AS SupplierNo,
                t.supplier_name AS SupplierName,
                CAST(t.po_total AS CHAR) AS PoTotal,
                IFNULL(t.po_purchaser, '') AS PoPurchaser,
                t.state AS State,
                IFNULL(DATE_FORMAT(t.po_ssend_date, '%Y-%m-%d'), '') AS PoSsendDate,
                IFNULL(DATE_FORMAT(t.create_time, '%Y-%m-%d %H:%i:%s'), '') AS CreateTime
            FROM (
                SELECT DISTINCT
                    pm.po_billno,
                    pm.supplier_no,
                    pm.supplier_name,
                    pm.po_total,
                    pm.po_purchaser,
                    pm.create_time,
                    pm.po_ssend_date,
                    CASE pm.state
                        WHEN 0 THEN '新增'
                        WHEN 1 THEN '审核中'
                        WHEN 2 THEN '同意'
                        WHEN 3 THEN '关闭'
                        ELSE CAST(pm.state AS CHAR)
                    END AS state
                FROM srm_po_main pm
                LEFT JOIN srm_po_list pl ON pm.id = pl.po_id
                LEFT JOIN srm_po_occupy occ ON pl.Id = occ.polist_id
                    AND CAST(pm.factory_id AS CHAR(64)) = CAST(occ.factory_id AS CHAR(64))
                LEFT JOIN mes_morder mo ON mo.id = occ.morder_id
                WHERE mo.morder_no = @WorkOrd
                  AND CAST(mo.factory_id AS CHAR(64)) = CAST(@Domain AS CHAR(64))
                  AND pm.IsDeleted = 0
            ) AS t
            ORDER BY t.po_billno
            """;
        return await _db.Ado.SqlQueryAsync<TracePoRow>(sql, new { WorkOrd = workOrd, Domain = domain });
    }

    private async Task<List<TracePrRow>> QueryTracePrAsync(string workOrd, string domain)
    {
        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 srm_po_occupy occ ON pm.Id = occ.polist_id
                AND CAST(pm.factory_id AS CHAR(64)) = CAST(occ.factory_id AS CHAR(64))
            LEFT JOIN ic_item ic ON pm.icitem_id = ic.Id
            LEFT JOIN mes_morder mo ON mo.id = occ.morder_id
            WHERE mo.morder_no = @WorkOrd
              AND CAST(mo.factory_id AS CHAR(64)) = CAST(@Domain AS CHAR(64))
              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, Domain = domain });
    }

    private async Task<List<TraceNbrRow>> QueryTraceNbrAsync(string workOrd, string domain)
    {
        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
            LEFT JOIN ItemMaster im ON nd.ItemNum = im.ItemNum
            WHERE nm.WorkOrd = @WorkOrd
              AND nm.Type = 'SM'
              AND CAST(nm.`Domain` AS CHAR(64)) = CAST(@Domain AS CHAR(64))
            ORDER BY nm.Nbr
            """;
        return await _db.Ado.SqlQueryAsync<TraceNbrRow>(sql, new { WorkOrd = workOrd, Domain = domain });
    }

    private async Task<List<TraceProdRow>> QueryTraceProdReportAsync(string workOrd, string domain)
    {
        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
            LEFT JOIN ItemMaster im ON ote.ItemNum = im.ItemNum
            WHERE ote.WorkOrd = @WorkOrd
            ORDER BY ote.ProdDate DESC
            """;
        return await _db.Ado.SqlQueryAsync<TraceProdRow>(sql, new { WorkOrd = workOrd });
    }

    private async Task<List<TraceQualityRow>> QueryTraceQualityAsync(string workOrd, string domain)
    {
        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
            WHERE mo.morder_no = @WorkOrd
              AND CAST(mo.factory_id AS CHAR(64)) = CAST(@Domain AS CHAR(64))
              AND mo.IsDeleted = 0
            ORDER BY mo.id
            """;
        return await _db.Ado.SqlQueryAsync<TraceQualityRow>(sql, new { WorkOrd = workOrd, Domain = domain });
    }

    private async Task<List<TraceReceiptRow>> QueryTraceReceiptAsync(string workOrd, string domain)
    {
        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.QtyChange, 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 InvTransHist a ON a.WorkOrd = c.WorkOrd AND a.TransType = 'rct-wo' AND IFNULL(a.QtyChange, 0) > 0
            LEFT JOIN ItemMaster im ON c.ItemNum = im.ItemNum AND im.`Domain` = c.`Domain`
            LEFT JOIN (
                SELECT WorkOrds, SUM(IFNULL(CompQty, 0)) AS CompQty
                FROM PeriodSequenceDet
                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 CAST(mo.factory_id AS CHAR(64)) = CAST(c.`Domain` AS CHAR(64))
            WHERE c.WorkOrd = @WorkOrd AND c.`Domain` = @Domain
            """;
        return await _db.Ado.SqlQueryAsync<TraceReceiptRow>(sql, new { WorkOrd = workOrd, Domain = domain });
    }

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

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

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

        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", domain),
                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", domain),
                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", domain),
                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", domain),
                    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 pars = new List<SugarParameter>
        {
            new("@Status", status),
            new("@UpdateUser", account),
            new("@UpdateTime", DateTime.Now),
            new("@Id", input.Id),
            new("@Domain", input.Domain.Trim())
        };
        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
        {
            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)
                });
            if (row == null || string.IsNullOrWhiteSpace(row.WorkOrd)) return;

            string idem = $"{row.Domain}#{row.WorkOrd}#{status}";
            long tenantId = _userManager.TenantId;
            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));
                
                // 执行关闭逻辑（模拟存储过程）
                await ExecuteCloseWorkOrders(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）
    /// </summary>
    private async Task ExecuteCloseWorkOrders(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 = @Domain AND FIND_IN_SET(WorkOrd, @WorkOrds) > 0",
            new SugarParameter("@Domain", domain),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

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

        // 3. 更新领料单明细状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrDetail SET Status = 'C' WHERE tenant_id = @Domain 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("@Domain", domain),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

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

        // 5. 更新退料单明细状态为C
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE NbrDetail SET Status = 'C' WHERE tenant_id = @Domain 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("@Domain", domain),
            new SugarParameter("@WorkOrds", workOrdsForFindInSet));

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

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

        // 8. 删除工单占用
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM mes_mooccupy WHERE tenant_id = @Domain AND FIND_IN_SET(moo_mo, @WorkOrds) > 0",
            new SugarParameter("@Domain", domain),
            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 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)
        };

        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 = _userManager.TenantId;
        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 = _userManager.TenantId;
        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 = _userManager.TenantId;
        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 = _userManager.TenantId;
        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 = _userManager.TenantId;
        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状态需同步创建领料单NbrDetail
            if (!isP)
            {
                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状态需同步修改领料单NbrDetail
            if (!isP)
            {
                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 = _userManager.TenantId;

        // 获取物料明细信息用于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状态需同步删除NbrDetail
        if (woStatus != "P")
        {
            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 = _userManager.TenantId;
        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 = _userManager.TenantId;

        // 检查后序是否已有完成数量
        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, NbrDate,
                        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 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())
            });
        if (n == 0)
            throw Oops.Oh("工单不存在");
        return new { message = input.Urgent == 2 ? "已设为特急" : "已设为加急" };
    }

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

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