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

    // ══════════════════════════════════════════════════════════════
    // 列表 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";
        var pars = new List<SugarParameter>();

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

        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            innerWhere.Add($"(a.WorkOrd COLLATE {C}) LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            innerWhere.Add($"(a.LotSerial COLLATE {C}) LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            innerWhere.Add($"(a.ItemNum COLLATE {C}) 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,
            a.`Domain` AS Domain,
            a.Urgent AS Urgent
        FROM WorkOrdMaster a
        LEFT JOIN ItemMaster b ON a.ItemNum COLLATE {C} = b.ItemNum COLLATE {C}
        LEFT JOIN ReplenishmentWeekPlan r
            ON a.WorkOrd COLLATE {C} = r.ProductionOrder COLLATE {C}
            AND CAST(a.`Domain` AS CHAR(64)) COLLATE {C} = CAST(r.factory_id AS CHAR(64)) COLLATE {C}
        LEFT JOIN crm_seorder se ON se.bill_no COLLATE {C} = a.SalesJob COLLATE {C}
        LEFT JOIN CustMaster cm ON cm.Cust COLLATE {C} = se.custom_no COLLATE {C}
        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)) COLLATE {C} = CAST(s.`Domain` AS CHAR(64)) COLLATE {C}
            AND a.WorkOrd COLLATE {C} = s.WorkOrds COLLATE {C}
        LEFT JOIN (
            SELECT morder_no, MAX(create_time) AS checktime
            FROM b_examine_result
            GROUP BY morder_no
        ) exm ON exm.morder_no COLLATE {C} = a.WorkOrd COLLATE {C}
        LEFT JOIN WorkOrdInStorage ins
            ON a.WorkOrd COLLATE {C} = ins.WorkOrd COLLATE {C}
            AND ins.Remark COLLATE {C} = '工单预留' COLLATE {C}
        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("工单号与公司域名不能为空");

        const string sql = """
            SELECT WorkOrd, ItemNum, QtyOrded, Priority, LotSerial, IFNULL(Urgent, 0) AS Urgent
            FROM WorkOrdMaster
            WHERE WorkOrd = @WorkOrd AND `Domain` = @Domain
            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("工单号与公司域名不能为空");

        const 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 a.WorkOrd = @WorkOrd AND a.`Domain` = @Domain
            LIMIT 1
            """;
        var master = (await _db.Ado.SqlQueryAsync<WorkOrderViewMasterRow>(masterSql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() }))
            .FirstOrDefault() ?? throw Oops.Oh("工单不存在");

        const 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.`Domain` = @Domain
            ORDER BY r.Line, r.ColumnNum
            """;
        var routings = await _db.Ado.SqlQueryAsync<WorkOrderViewRoutingRow>(routingSql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() });

        const string detailSql = """
            SELECT ItemNum, Op, QtyRequired, IFNULL(FrozenBOMQty, 0) AS FrozenBOMQty
            FROM WorkOrdDetail
            WHERE WorkOrd = @WorkOrd AND `Domain` = @Domain
            ORDER BY 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("工单号与公司域名不能为空");

        const string sql = """
            SELECT ItemNum, Op, QtyRequired, IFNULL(FrozenBOMQty, 0) AS FrozenBOMQty
            FROM WorkOrdDetail
            WHERE WorkOrd = @WorkOrd AND `Domain` = @Domain
            ORDER BY 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("工单号与公司域名不能为空");

        const string sql = """
            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.`Domain` = @Domain
            ORDER BY r.Line, r.ColumnNum
            """;
        var list = await _db.Ado.SqlQueryAsync<WorkOrderViewRoutingRow>(sql, new { WorkOrd = workOrd.Trim(), Domain = domain.Trim() });
        return new { list };
    }

    // ══════════════════════════════════════════════════════════════
    // 执行追踪 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();

        const 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 a.WorkOrd = @WorkOrd AND a.`Domain` = @Domain
            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 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", input.WorkOrd.Trim()),
            new("@Domain", input.Domain.Trim())
        };

        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 WorkOrd = @WorkOrd AND `Domain` = @Domain
            """,
            pars);

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

        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 RecID = @Id AND `Domain` = @Domain
            """,
            pars);
        if (n == 0)
            throw Oops.Oh("工单不存在或未更新");
        return new { message = "状态已更新" };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单关闭 POST /api/Production/scheduling/close
    // ══════════════════════════════════════════════════════════════
    [DisplayName("工单关闭（存储过程）")]
    [HttpPost("scheduling/close")]
    public async Task<object> Close([FromBody] WorkOrderCloseInput input)
    {
        await _db.Ado.ExecuteCommandAsync(
            "CALL pr_MES_CloseWorkOrders(@Ids)",
            new SugarParameter("@Ids", input.Ids.Trim()));
        return new { message = "已提交关闭" };
    }

    // ══════════════════════════════════════════════════════════════
    // 同步工艺路线 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 pars = new List<SugarParameter>
        {
            new("@WorkOrd", workOrd),
            new("@Domain", domain)
        };

        await _db.Ado.BeginTranAsync();
        try
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                DELETE FROM WorkOrdRouting
                WHERE WorkOrd = @WorkOrd AND CAST(`Domain` AS CHAR(64)) COLLATE utf8mb4_general_ci = CAST(@Domain AS CHAR(64)) COLLATE utf8mb4_general_ci
                """,
                pars);

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO WorkOrdRouting (
                    `Domain`, Descr, MilestoneOp, WorkOrd, OP, ParentOp, RunTime, ItemNum, QtyOrded, OverlapUnits, Status, IsActive, CommentIndex, CreateTime, StdOp, PackingQty, WorkOrdMasterRecID
                )
                SELECT
                    w.`Domain`,
                    r.Descr,
                    r.MilestoneOp,
                    w.WorkOrd,
                    r.OP,
                    r.ParentOp,
                    r.RunTime,
                    w.ItemNum,
                    w.QtyOrded,
                    r.OverlapUnits,
                    w.Status,
                    1,
                    r.CommentIndex,
                    NOW(),
                    r.StdOp,
                    r.PackingQty,
                    w.RecID
                FROM WorkOrdMaster w
                LEFT JOIN RoutingOpDetail r ON w.ItemNum COLLATE utf8mb4_general_ci = r.RoutingCode COLLATE utf8mb4_general_ci
                WHERE w.WorkOrd = @WorkOrd
                  AND CAST(w.`Domain` AS CHAR(64)) COLLATE utf8mb4_general_ci = CAST(@Domain AS CHAR(64)) COLLATE utf8mb4_general_ci
                  AND r.MilestoneOp IS NOT NULL
                """,
                pars);

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

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

    // ══════════════════════════════════════════════════════════════
    // 加急 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 WorkOrd = @WorkOrd AND `Domain` = @Domain
            """,
            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; }
    }

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

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

    private sealed class WorkOrderViewDetailRow
    {
        public string? ItemNum { get; set; }
        public string? Op { get; set; }
        public decimal? QtyRequired { get; set; }
        public decimal FrozenBOMQty { 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; }
    }
}