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>
    /// 主表 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 pars = new List<SugarParameter>();

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

        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
            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.ItemNum, d.Op, d.QtyRequired, IFNULL(d.FrozenBOMQty, 0) AS FrozenBOMQty
            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.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 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();

        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 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 {SqlWorkOrdMasterMatchDomainOrTenant}
            """,
            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 {SqlWorkOrdMasterMatchRecIdAndDomainOrTenant}
            """,
            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 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）。
            // 与历史脚本一致：DELETE 仍按工单 + 域/租户匹配，避免误删其它域数据。
            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`, `CreateTime`, `CreateUser`, `tenant_id`,`CommentIndex`,`WaitTime`
                )
                SELECT
                    a.`Descr`,
                    LEFT(TRIM(COALESCE(NULLIF(TRIM(IFNULL(w.`Domain`, '')), ''), NULLIF(TRIM(IFNULL(a.`Domain`, '')), ''), ' ')), 8),
                    '',
                    b.`InternalEquipmentCode`,
                    IFNULL(b.`StandardStaffCount`, 0),
                    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,
                    IFNULL(b.`Rate`, 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(IFNULL(b.`Line`, ''), 8),
                    NOW(3),
                    @CreateUser,
                    w.`tenant_id`,CAST(IFNULL(a.`MilestoneOp`, 0) AS UNSIGNED),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 = "同步工艺路线已执行" };
    }

    // ══════════════════════════════════════════════════════════════
    // 加急 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; }
    }

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

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