using Admin.NET.Plugin.AiDOP.DataPlatform;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 工单物料齐套上线看板服务
/// </summary>
[ApiDescriptionSettings(Order = 312, Description = "工单物料齐套上线看板")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderMaterialReadinessService : IDynamicApiController, ITransient
{
    private const string SqlServerSourceCode = "DOPDEMORQ_SQLSERVER";

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly SourceDomainTenantResolver _domainTenant;

    public WorkOrderMaterialReadinessService(
        ISqlSugarClient db,
        UserManager userManager,
        MdpSourceScopeFactory scopeFactory,
        SourceDomainTenantResolver domainTenant)
    {
        _db = db;
        _userManager = userManager;
        _scopeFactory = scopeFactory;
        _domainTenant = domainTenant;
    }

    [DisplayName("工单物料齐套上线看板列表")]
    [HttpGet("work-order-material-readiness/list")]
    public async Task<object> GetList([FromQuery] WorkOrderMaterialReadinessListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        // 租户隔离：只查当前租户的 WorkOrdTemp 数据
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        where.Add("a.tenant_id = @TenantId");
        pars.Add(new SugarParameter("@TenantId", tenantId));

        if (!string.IsNullOrWhiteSpace(input.SeOrder))
        {
            where.Add("a.SeOrder LIKE @SeOrder");
            pars.Add(new SugarParameter("@SeOrder", $"%{input.SeOrder.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Workord))
        {
            where.Add("a.workord LIKE @Workord");
            pars.Add(new SugarParameter("@Workord", $"%{input.Workord.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BomNumber))
        {
            where.Add("a.bom_number = @BomNumber");
            pars.Add(new SugarParameter("@BomNumber", input.BomNumber.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("a.ItemNum = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierName))
        {
            where.Add("a.supplier_name LIKE @SupplierName");
            pars.Add(new SugarParameter("@SupplierName", $"%{input.SupplierName.Trim()}%"));
        }

        // 始终使用 WorkOrdTemp 路径（字段完整）；dwd_material_readiness 缺少 SeOrder/Priority/MorderType 等列，
        // 走 DWD 路径会导致订单号、工单优先级、工单类型等列全部为 NULL。
        // if (await ShouldUseDwdAsync(input))
        // {
        //     return await GetDwdListAsync(input, page, pageSize, offset);
        // }

        var fromSql = $"FROM WorkOrdTemp a WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM (SELECT DISTINCT a.RowNum, a.SeOrder, a.workord, a.ItemNum, a.bom_number, a.supplier_number {fromSql}) t", pars);
        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);

        var list = await _db.Ado.SqlQueryAsync<WorkOrderMaterialReadinessRow>(
            $"""
            SELECT DISTINCT
                a.RowNum AS RowNum,
                a.SeOrder AS SeOrder,
                a.SePriority AS SePriority,
                a.workord AS Workord,
                a.Priority AS Priority,
                a.morder_type AS MorderType,
                a.LotSerial AS LotSerial,
                a.bom_number AS BomNumber,
                a.Descr AS Descr,
                a.Descr1 AS Descr1,
                a.production_number AS ProductionNumber,
                a.checktime AS Checktime,
                a.PMBOM AS PMBOM,
                a.ItemType AS ItemType,
                a.OwnerApplication AS OwnerApplication,
                a.ItemNum AS ItemNum,
                a.ComponentItemDescr AS ComponentItemDescr,
                a.ComponentItemDrawing AS ComponentItemDrawing,
                a.QtyRequired AS QtyRequired,
                a.ComponentItemUM AS ComponentItemUM,
                a.ComponentItemKittingTime AS ComponentItemKittingTime,
                a.QtyRec AS QtyRec,
                a.RecDate AS RecDate,
                a.shortage AS Shortage,
                a.QtyFrom AS QtyFrom,
                a.FromDate AS FromDate,
                a.zjsl AS Zjsl,
                a.jykssj AS Jykssj,
                a.ztsl AS Ztsl,
                a.jhshrq AS Jhshrq,
                a.jqsl AS Jqsl,
                a.hfjq AS Hfjq,
                a.PurOrdQty AS PurOrdQty,
                a.PurOrdorddate AS PurOrdorddate,
                a.PurOrds AS PurOrds,
                a.ContractDate AS ContractDate,
                a.ContractNO AS ContractNO,
                a.supplier_number AS SupplierNumber,
                a.supplier_name AS SupplierName,
                a.Buyer AS Buyer,
                a.BuyerUser AS BuyerUser
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    [DisplayName("刷新工单物料齐套上线看板")]
    [HttpPost("work-order-material-readiness/refresh")]
    public async Task<object> Refresh()
    {
        var sw = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
            var now = DateTime.Now;
            var batchId = $"workordtemp-{now:yyyyMMdd-HHmmss}";
            var tenantPar = new SugarParameter("@TenantId", tenantId);
            var domain = await _domainTenant.ResolveDomainAsync(SqlServerSourceCode, tenantId);

            // Step 0: 从 SQL Server 读取 InvMaster，写入 MySQL 辅助表
            await PrepareInvMasterTempAsync(domain, tenantId);

            // Step 1: 清空本租户 WorkOrdTemp
            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM WorkOrdTemp WHERE tenant_id = @TenantId", tenantPar);

            // Step 2: INSERT（原存储过程核心查询，所有源表加租户过滤）
            await _db.Ado.ExecuteCommandAsync("""
                INSERT INTO WorkOrdTemp(
                    RowNum, Status, workord, Priority, OrdDate, morder_type, bom_number,
                    Descr, Descr1, production_number, QtyRequired, supplier_name, ItemNum,
                    supplier_number, ljsl, SeOrder, SePriority, LotSerial,
                    checktime, PMBOM, ItemType, OwnerApplication, ComponentItemDescr,
                    ComponentItemDrawing, ComponentItemUM, ComponentItemKittingTime,
                    QtyRec, RecDate, QtyFrom, FromDate, jhshrq, PurOrdQty, PurOrdorddate,
                    Buyer, BuyerUser, tenant_id
                )
                SELECT
                    viewql.RowNum, viewql.Status, viewql.workord, viewql.Priority, viewql.OrdDate,
                    viewql.morder_type, viewql.bom_number, viewql.Descr, viewql.Descr1,
                    viewql.morder_production_number, viewql.QtyRequired, viewql.supplier_name,
                    viewql.ItemNum, viewql.supplier_number,
                    SUM(viewql.QtyRequired) OVER (
                        PARTITION BY viewql.ItemNum
                        ORDER BY viewql.Priority, viewql.workord
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS ljsl,
                    viewql.fbill_no, viewql.Priority AS SePriority,
                    viewql.LotSerial, viewql.checktime,
                    viewql.PMBOM, viewql.ItemType, viewql.OwnerApplication,
                    viewql.ComponentItemDescr, viewql.ComponentItemDrawing,
                    viewql.ComponentItemUM, viewql.ComponentItemKittingTime,
                    viewql.QtyRec, viewql.RecDate, viewql.QtyFrom, viewql.FromDate,
                    viewql.jhshrq, viewql.PurOrdQty, viewql.PurOrdorddate,
                    viewql.Buyer, '' AS BuyerUser,
                    @TenantId AS tenant_id
                FROM (
                    SELECT
                        a.RowNum, a.Status, a.workord, a.Priority, a.OrdDate, a.LotSerial,
                        b.morder_type, c.ItemNum AS bom_number, c.Descr, c.Descr1,
                        b.morder_production_number, d.QtyRequired, e.supplier_name,
                        d.ItemNum, e.supplier_number, f.fbill_no,
                        c.PMBOM,
                        Sonitem.ItemType, Sonitem.OwnerApplication,
                        Sonitem.Descr AS ComponentItemDescr,
                        Sonitem.Drawing AS ComponentItemDrawing,
                        Sonitem.UM AS ComponentItemUM,
                        exm.checktime,
                        exd.kitting_time AS ComponentItemKittingTime,
                        j.QtyRec, j.updatetime AS RecDate,
                        j.QtyFrom, j.updatetime AS FromDate,
                        sh.jhshrq,
                        pm.QtyOrded AS PurOrdQty, pm.orddate AS PurOrdorddate,
                        pm.Buyer
                    FROM (
                        SELECT RowNum, Status, workord, Priority, OrdDate, LotSerial
                        FROM (
                            SELECT CAST(ROW_NUMBER() OVER (PARTITION BY Status ORDER BY Priority ASC) AS CHAR(50)) AS RowNum,
                                   Status, workord, Priority, OrdDate, LotSerial
                            FROM WorkOrdMaster
                            WHERE Status IN ('W','S','r','p') AND tenant_id = @TenantId
                        ) AS workview
                        WHERE workord IN (
                            SELECT WorkOrd FROM NbrMaster
                            WHERE Type = 'SM' AND Status = ''
                            GROUP BY WorkOrd
                        ) OR Status = 'p'
                    ) a
                    LEFT JOIN mes_morder b ON a.workord = b.morder_no AND b.tenant_id = @TenantId
                    LEFT JOIN ItemMaster c ON b.product_code = c.ItemNum AND c.tenant_id = @TenantId
                    LEFT JOIN WorkOrdDetail d ON a.WorkOrd = d.WorkOrd AND d.tenant_id = @TenantId
                    LEFT JOIN ItemMaster Sonitem ON d.itemnum = Sonitem.itemnum AND Sonitem.tenant_id = @TenantId
                    LEFT JOIN (
                        SELECT moentry_mono, GROUP_CONCAT(DISTINCT fbill_no SEPARATOR ',') AS fbill_no
                        FROM mes_moentry WHERE tenant_id = @TenantId
                        GROUP BY moentry_mono
                    ) f ON b.morder_no = f.moentry_mono
                    LEFT JOIN (
                        SELECT workord, itemnum,
                               SUM(QtyRec) AS QtyRec, MAX(updatetime) AS updatetime,
                               SUM(QtyFrom) AS QtyFrom
                        FROM NbrDetail
                        WHERE tenant_id = @TenantId
                        GROUP BY workord, itemnum
                    ) j ON a.workord = j.workord AND d.itemnum = j.itemnum
                    LEFT JOIN (
                        SELECT a0.number,
                       GROUP_CONCAT(a0.supplier_name SEPARATOR ',') AS supplier_name,
                       GROUP_CONCAT(a0.supplier_number SEPARATOR ',') AS supplier_number
                FROM srm_purchase a0
                WHERE a0.quota_rate > 0 AND a0.tenant_id = @TenantId
                GROUP BY a0.number
                    ) e ON d.ItemNum = e.number
                    LEFT JOIN LATERAL (
                        SELECT create_time AS checktime, id
                        FROM b_examine_result
                        WHERE morder_no = a.WorkOrd AND tenant_id = @TenantId
                        ORDER BY create_time DESC LIMIT 1
                    ) exm ON TRUE
                    LEFT JOIN (
                        SELECT examine_id, item_number, MIN(kitting_time) AS kitting_time
                        FROM b_bom_child_examine
                        GROUP BY examine_id, item_number
                    ) exd ON exm.id = exd.examine_id AND exd.item_number != b.product_code and exd.item_number=d.ItemNum
                    LEFT JOIN (
                        SELECT MIN(shd.jhshrq) AS jhshrq,
                               SUM(shzb.sh_delivery_quantity) AS sh_delivery_quantity,
                               shzb.sh_material_code
                        FROM scm_shd shd
                        LEFT JOIN scm_shdzb shzb ON shd.id = shzb.glid AND shzb.tenant_id = shd.tenant_id
                        WHERE shd.shzt != '完成' AND shd.tenant_id = @TenantId
                        GROUP BY shzb.sh_material_code
                    ) sh ON d.itemnum = sh.sh_material_code
                    LEFT JOIN (
                        SELECT SUM(pd.QtyOrded) AS QtyOrded, pd.itemnum,
                               MIN(pm.orddate) AS orddate,
                               GROUP_CONCAT(pm.Buyer SEPARATOR ',') AS Buyer
                        FROM PurOrdMaster pm
                        LEFT JOIN PurOrdDetail pd ON pm.purord = pd.purord AND pd.tenant_id = pm.tenant_id
                        WHERE pm.status != 'C' AND pd.status != 'C' AND pm.tenant_id = @TenantId
                        GROUP BY pd.itemnum
                    ) pm ON d.itemnum = pm.itemnum
                ) viewql
                """, new List<SugarParameter> { tenantPar });

            // 兜底：确保所有行都有 tenant_id（防止参数绑定异常导致写入 NULL）
            await _db.Ado.ExecuteCommandAsync(
                "UPDATE WorkOrdTemp SET tenant_id = @TenantId WHERE tenant_id IS NULL",
                new List<SugarParameter> { tenantPar });

            var workOrdTempCount = await _db.Ado.GetIntAsync(
                "SELECT COUNT(1) FROM WorkOrdTemp WHERE IFNULL(workord,'') <> '' AND tenant_id = @TenantId",
                tenantPar);

            // Step 3: UPDATE locStock（从 _tmp_inv_master 辅助表 JOIN）
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp a
                LEFT JOIN _tmp_inv_master t ON a.ItemNum = t.ItemNum AND t.tenant_id = @TenantId
                SET a.locStock = IFNULL(t.locStock, 0)
                WHERE a.tenant_id = @TenantId
                """, tenantPar);

            // Step 4: UPDATE ztsl / zjsl / jykssj
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp a
                LEFT JOIN (
                    SELECT ItemNum, SUM(ztsl) AS ztsl, SUM(zsl1) AS zjsl, MIN(jykssj) AS jykssj
                    FROM (
                        SELECT p.ItemNum,
                               CASE WHEN IFNULL(p.zfhl, 0) <= IFNULL(p.zshl, 0) THEN 0
                                    ELSE IFNULL(p.zfhl, 0) - IFNULL(p.zshl, 0) END AS ztsl,
                               IFNULL(p.zsl, 0) AS zsl1, p.jykssj
                        FROM (
                            SELECT p0.PurOrd, p0.Line, p0.ItemNum,
                                   IFNULL(s0.fhsl, 0) - IFNULL(Do0.thsl, 0) AS zfhl,
                                   p0.ReceiptQty + p0.RctQty AS zshl,
                                   IFNULL(i0.zjsl, 0) AS zsl, i0.jykssj
                            FROM PurOrdDetail p0
                            LEFT JOIN LATERAL (
                                SELECT SUM(sh_delivery_quantity) AS fhsl
                                FROM scm_shdzb WHERE po_bill = p0.PurOrd AND po_billline = p0.Line
                                  AND tenant_id = p0.tenant_id
                            ) s0 ON TRUE
                            LEFT JOIN LATERAL (
                                SELECT SUM(m.Qty) AS zjsl, MIN(qms.FAPPLYTIME) AS jykssj
                                FROM MissedPrint m
                                LEFT JOIN qms_qcp_inspecapplyn qms ON m.checkno = qms.FBILLNO
                                WHERE m.Status = 'I' AND m.PurOrd = p0.PurOrd AND m.PurLine = p0.Line
                                  AND m.tenant_id = p0.tenant_id
                            ) i0 ON TRUE
                            LEFT JOIN LATERAL (
                                SELECT SUM(t.QtyReturn) AS thsl
                                FROM PurOrdRctDetail t
                                WHERE t.OrdNbr = p0.PurOrd AND t.OrdLine = p0.Line
                                  AND t.rcttype IN ('pt','temp') AND t.tenant_id = p0.tenant_id
                            ) Do0 ON TRUE
                            WHERE p0.Status <> 'C' AND p0.tenant_id = @TenantId
                        ) p
                    ) viewpur
                    GROUP BY ItemNum
                ) b ON a.ItemNum = b.ItemNum
                SET a.ztsl = IFNULL(b.ztsl, 0),
                    a.zjsl = IFNULL(b.zjsl, 0),
                    a.jykssj = b.jykssj
                WHERE a.tenant_id = @TenantId
                """, tenantPar);

            // Step 5: UPDATE jqsl / hfjq
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp a
                LEFT JOIN (
                    SELECT IF(jqsl > schedqty, schedqty, jqsl) AS jqsl, ItemNum, jqhf
                    FROM (
                        SELECT SUM(CAST(IFNULL(jq.jhdsl, 0) AS DECIMAL(18,6))) AS jqsl,
                               MAX(ds.schedqty) AS schedqty,
                               ds.ItemNum, DATE(jq.jqhf) AS jqhf
                        FROM scm_jhjh_jq jq
                        INNER JOIN srm_polist_ds ds ON jq.glid = ds.id AND ds.tenant_id = jq.tenant_id
                        LEFT JOIN (
                            SELECT SUM(sh_delivery_quantity) AS sh_delivery_quantity, jhdbh
                            FROM scm_shdzb WHERE tenant_id = @TenantId
                            GROUP BY jhdbh
                        ) c ON ds.dsnum = c.jhdbh
                        WHERE jq.flag = 0 AND jq.tenant_id = @TenantId
                          AND IFNULL(c.sh_delivery_quantity, 0) < ds.schedqty
                          AND ds.status = 'P' AND ds.schedqty - ds.sentqty > 0
                          AND DATE(jq.jqhf) > CURDATE()
                        GROUP BY ds.ItemNum, DATE(jq.jqhf)
                    ) jqview
                ) b ON a.ItemNum = b.ItemNum AND DATE(a.OrdDate) = b.jqhf
                SET a.jqsl = CAST(IFNULL(b.jqsl, 0) AS DECIMAL(18,6)),
                    a.hfjq = b.jqhf
                WHERE a.tenant_id = @TenantId
                """, tenantPar);

            // Step 6: UPDATE available / shortage（按日期分段累加）
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp
                SET available = IFNULL(locStock, 0),
                    shortage  = IFNULL(locStock, 0) - IFNULL(ljsl, 0)
                WHERE tenant_id = @TenantId AND DATE(OrdDate) <= CURDATE() + INTERVAL 1 DAY
                """, tenantPar);
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp
                SET available = IFNULL(locStock, 0) + IFNULL(zjsl, 0),
                    shortage  = IFNULL(locStock, 0) + IFNULL(zjsl, 0) - IFNULL(ljsl, 0)
                WHERE tenant_id = @TenantId AND DATE(OrdDate) = CURDATE() + INTERVAL 2 DAY
                """, tenantPar);
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp
                SET available = IFNULL(locStock, 0) + IFNULL(zjsl, 0) + IFNULL(ztsl, 0),
                    shortage  = IFNULL(locStock, 0) + IFNULL(zjsl, 0) + IFNULL(ztsl, 0) - IFNULL(ljsl, 0)
                WHERE tenant_id = @TenantId AND DATE(OrdDate) = CURDATE() + INTERVAL 3 DAY
                """, tenantPar);
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE WorkOrdTemp
                SET available = IFNULL(locStock, 0) + IFNULL(zjsl, 0) + IFNULL(ztsl, 0) + IFNULL(jqsl, 0),
                    shortage  = IFNULL(locStock, 0) + IFNULL(zjsl, 0) + IFNULL(ztsl, 0) + IFNULL(jqsl, 0) - IFNULL(ljsl, 0)
                WHERE tenant_id = @TenantId
                  AND DATE(OrdDate) >= CURDATE() + INTERVAL 4 DAY
                  AND DATE(OrdDate) <= CURDATE() + INTERVAL 14 DAY
                """, tenantPar);

            // 清理辅助表（仅本租户）
            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM _tmp_inv_master WHERE tenant_id = @TenantId", tenantPar);

            // ── DWD 写入 ──
            await _db.Ado.BeginTranAsync();
            try
            {
            await _db.Ado.ExecuteCommandAsync(
                """
                DELETE FROM dwd_material_readiness
                WHERE tenant_id = @TenantId
                  AND work_order NOT IN (
                    SELECT DISTINCT workord FROM WorkOrdTemp WHERE IFNULL(workord, '') <> '' AND tenant_id = @TenantId
                )
                """, tenantPar);

            var dwdCount = await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO dwd_material_readiness
                (tenant_id, stat_date, work_order, op_code, parent_item_code, component_item_code, component_item_name, required_qty, cumulative_required_qty, stock_available_qty, qc_pending_qty, in_transit_qty, delivery_reply_qty, available_qty, shortage_qty, ready_status, supplier_code, supplier_name, need_date, calc_batch_id, calc_time)
                SELECT
                    @TenantId, @StatDate,
                    IFNULL(workord, ''), CAST(IFNULL(Op, 0) AS CHAR), IFNULL(bom_number, ''),
                    COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
                    IFNULL(ComponentItemDescr, ''),
                    IFNULL(QtyRequired, 0), IFNULL(ljsl, 0), IFNULL(locStock, 0),
                    IFNULL(jqsl, 0), IFNULL(ztsl, 0), IFNULL(PurOrdQty, 0), IFNULL(available, 0),
                    GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)),
                    CASE WHEN GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)) > 0 THEN 'SHORTAGE' ELSE 'READY' END,
                    supplier_number, supplier_name, COALESCE(jhshrq, hfjq, RecDate),
                    @BatchId, @SyncTime
                FROM WorkOrdTemp
                WHERE IFNULL(workord, '') <> '' AND tenant_id = @TenantId
                ON DUPLICATE KEY UPDATE
                    component_item_name = VALUES(component_item_name),
                    required_qty = VALUES(required_qty),
                    cumulative_required_qty = VALUES(cumulative_required_qty),
                    stock_available_qty = VALUES(stock_available_qty),
                    qc_pending_qty = VALUES(qc_pending_qty),
                    in_transit_qty = VALUES(in_transit_qty),
                    delivery_reply_qty = VALUES(delivery_reply_qty),
                    available_qty = VALUES(available_qty),
                    shortage_qty = VALUES(shortage_qty),
                    ready_status = VALUES(ready_status),
                    supplier_code = VALUES(supplier_code),
                    supplier_name = VALUES(supplier_name),
                    need_date = VALUES(need_date),
                    calc_time = VALUES(calc_time),
                    update_time = CURRENT_TIMESTAMP
                """,
                new SugarParameter("@StatDate", now.Date),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@SyncTime", now),
                tenantPar);

            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM dwd_material_shortage WHERE stat_date = @StatDate AND tenant_id = @TenantId",
                new SugarParameter("@StatDate", now.Date), tenantPar);

            var shortageCount = await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO dwd_material_shortage
                (tenant_id, stat_date, work_order, op_code, component_item_code, shortage_qty, shortage_reason, expected_supply_date, supplier_code, related_po_no, risk_level, calc_batch_id, calc_time)
                SELECT
                    @TenantId, @StatDate,
                    IFNULL(workord, ''), CAST(IFNULL(Op, 0) AS CHAR),
                    COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
                    GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)),
                    '当前可用量不足', COALESCE(jhshrq, hfjq, RecDate),
                    supplier_number, PurOrds,
                    CASE
                        WHEN GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)) > IFNULL(QtyRequired, 0) THEN 'HIGH'
                        WHEN GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)) > 0 THEN 'MEDIUM'
                        ELSE 'LOW'
                    END,
                    @BatchId, @SyncTime
                FROM WorkOrdTemp
                WHERE IFNULL(workord, '') <> '' AND tenant_id = @TenantId
                  AND GREATEST(IFNULL(shortage, 0), IFNULL(QtyRequired, 0) - IFNULL(locStock, 0) - IFNULL(jqsl, 0) - IFNULL(ztsl, 0) - IFNULL(PurOrdQty, 0)) > 0
                """,
                new SugarParameter("@StatDate", now.Date),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@SyncTime", now),
                tenantPar);

                await _db.Ado.CommitTranAsync();
                return new
                {
                    enabled = true, batchId, workOrdTempCount, dwdCount, shortageCount,
                    elapsedMs = sw.ElapsedMilliseconds,
                    message = $"齐套/缺料刷新完成：获取 {workOrdTempCount} 条 WorkOrdTemp，写入 DWD 齐套 {dwdCount} 条、缺料 {shortageCount} 条。"
                };
            }
            catch
            {
                await _db.Ado.RollbackTranAsync();
                throw;
            }
        }
        catch (Exception ex)
        {
            return new { enabled = false, elapsedMs = sw.ElapsedMilliseconds, message = ex.Message };
        }
    }

    /// <summary>
    /// 从 SQL Server 读取 InvMaster 在库库存，写入 MySQL 辅助表 _tmp_inv_master。
    /// </summary>
    private async Task PrepareInvMasterTempAsync(string domain, long tenantId)
    {
        await _db.Ado.ExecuteCommandAsync("""
            CREATE TABLE IF NOT EXISTS _tmp_inv_master (
                tenant_id BIGINT NOT NULL,
                ItemNum VARCHAR(50) NOT NULL,
                locStock DECIMAL(18,6),
                PRIMARY KEY (tenant_id, ItemNum)
            )
            """);
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM _tmp_inv_master WHERE tenant_id = @TenantId",
            new List<SugarParameter> { new("@TenantId", tenantId) });

        var locRange = await _db.Ado.GetStringAsync(
            "SELECT Val FROM ConfigurationItem WHERE FldName='MRPLocationRange' AND tenant_id = @TenantId LIMIT 1",
            new List<SugarParameter> { new("@TenantId", tenantId) });
        if (string.IsNullOrWhiteSpace(locRange)) return;

        var locations = locRange.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
        if (locations.Length == 0) return;

        var remote = await _scopeFactory.GetScopeAsync(SqlServerSourceCode);
        var locParams = new List<string>();
        var pars = new List<SugarParameter> { new("@Domain", domain) };
        for (var i = 0; i < locations.Length; i++)
        {
            locParams.Add($"@loc{i}");
            pars.Add(new SugarParameter($"@loc{i}", locations[i]));
        }

        var invSql = $"""
            SELECT ItemNum, SUM(ISNULL(AvailStatusQty, 0) + ISNULL(Assay, 0)) AS locStock
            FROM InvMaster
            WHERE Domain = @Domain AND Location IN ({string.Join(",", locParams)})
            GROUP BY ItemNum
            """;
        var rows = await remote.Ado.SqlQueryAsync<LocStockRow>(invSql, pars);
        if (rows.Count == 0) return;

        const int batchSize = 500;
        for (var offset = 0; offset < rows.Count; offset += batchSize)
        {
            var batch = rows.Skip(offset).Take(batchSize).ToList();
            var batchPars = new List<SugarParameter> { new("@TenantId", tenantId) };
            var values = new List<string>();
            for (var i = 0; i < batch.Count; i++)
            {
                if (string.IsNullOrWhiteSpace(batch[i].ItemNum)) continue;
                values.Add($"(@TenantId, @item{i}, @stock{i})");
                batchPars.Add(new SugarParameter($"@item{i}", batch[i].ItemNum));
                batchPars.Add(new SugarParameter($"@stock{i}", batch[i].LocStock));
            }
            if (values.Count > 0)
            {
                await _db.Ado.ExecuteCommandAsync(
                    $"INSERT INTO _tmp_inv_master (tenant_id, ItemNum, locStock) VALUES {string.Join(",", values)}",
                    batchPars);
            }
        }
    }

    private sealed class LocStockRow
    {
        public string? ItemNum { get; set; }
        public decimal LocStock { get; set; }
    }

    private async Task<bool> ShouldUseDwdAsync(WorkOrderMaterialReadinessListInput input)
    {
        if (!string.IsNullOrWhiteSpace(input.SeOrder))
            return false;

        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM dwd_material_readiness WHERE tenant_id=@TenantId LIMIT 1", pars) > 0;
    }

    private async Task<object> GetDwdListAsync(WorkOrderMaterialReadinessListInput input, int page, int pageSize, int offset)
    {
        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        where.Add("a.tenant_id=@TenantId");
        pars.Add(new SugarParameter("@TenantId", tenantId));

        if (!string.IsNullOrWhiteSpace(input.Workord))
        {
            where.Add("a.work_order LIKE @Workord");
            pars.Add(new SugarParameter("@Workord", $"%{input.Workord.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BomNumber))
        {
            where.Add("a.parent_item_code = @BomNumber");
            pars.Add(new SugarParameter("@BomNumber", input.BomNumber.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("a.component_item_code = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierName))
        {
            where.Add("a.supplier_name LIKE @SupplierName");
            pars.Add(new SugarParameter("@SupplierName", $"%{input.SupplierName.Trim()}%"));
        }

        var fromSql = $"FROM dwd_material_readiness a WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var orderBy = BuildDwdOrderBy(input.SortField, input.SortOrder);
        var list = await _db.Ado.SqlQueryAsync<WorkOrderMaterialReadinessRow>(
            $"""
            SELECT
                CAST(a.id AS CHAR) AS RowNum,
                NULL AS SeOrder,
                NULL AS SePriority,
                a.work_order AS Workord,
                NULL AS Priority,
                NULL AS MorderType,
                NULL AS LotSerial,
                a.op_code AS Op,
                a.need_date AS OpOrdDate,
                a.parent_item_code AS BomNumber,
                a.parent_item_code AS Descr,
                NULL AS Descr1,
                NULL AS ProductionNumber,
                a.calc_time AS Checktime,
                a.parent_item_code AS PMBOM,
                NULL AS ItemType,
                'DWD' AS OwnerApplication,
                a.component_item_code AS ItemNum,
                a.component_item_name AS ComponentItemDescr,
                NULL AS ComponentItemDrawing,
                a.required_qty AS QtyRequired,
                NULL AS ComponentItemUM,
                NULL AS ComponentItemKittingTime,
                NULL AS QtyRec,
                NULL AS RecDate,
                a.shortage_qty AS Shortage,
                NULL AS QtyFrom,
                NULL AS FromDate,
                a.available_qty AS Zjsl,
                NULL AS Jykssj,
                a.in_transit_qty AS Ztsl,
                a.need_date AS Jhshrq,
                a.qc_pending_qty AS Jqsl,
                a.need_date AS Hfjq,
                a.delivery_reply_qty AS PurOrdQty,
                NULL AS PurOrdorddate,
                NULL AS PurOrds,
                NULL AS ContractDate,
                NULL AS ContractNO,
                a.supplier_code AS SupplierNumber,
                a.supplier_name AS SupplierName,
                NULL AS Buyer,
                NULL AS BuyerUser
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list, source = "dwd_material_readiness" };
    }

    private static string BuildDwdOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "rownum" => $"a.id {dir}",
            "workord" => $"a.work_order {dir}",
            "op" => $"a.op_code {dir}",
            "bomnumber" => $"a.parent_item_code {dir}",
            "itemnum" => $"a.component_item_code {dir}",
            "componentitemdescr" => $"a.component_item_name {dir}",
            "qtyrequired" => $"a.required_qty {dir}",
            "shortage" => $"a.shortage_qty {dir}",
            "zjsl" => $"a.available_qty {dir}",
            "ztsl" => $"a.in_transit_qty {dir}",
            "jhshrq" => $"a.need_date {dir}",
            "jqsl" => $"a.qc_pending_qty {dir}",
            "hfjq" => $"a.need_date {dir}",
            "purordqty" => $"a.delivery_reply_qty {dir}",
            "suppliernumber" => $"a.supplier_code {dir}",
            "suppliername" => $"a.supplier_name {dir}",
            _ => "a.stat_date DESC, a.work_order ASC, a.component_item_code ASC"
        };
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "rownum" => $"a.RowNum {dir}",
            "seorder" => $"a.SeOrder {dir}",
            "sepriority" => $"a.SePriority {dir}",
            "workord" => $"a.workord {dir}",
            "priority" => $"a.Priority {dir}",
            "mordertype" => $"a.morder_type {dir}",
            "lotserial" => $"a.LotSerial {dir}",
            "op" => $"a.Op {dir}",
            "oporddate" => $"a.OpOrdDate {dir}",
            "bomnumber" => $"a.bom_number {dir}",
            "descr" => $"a.Descr {dir}",
            "descr1" => $"a.Descr1 {dir}",
            "productionnumber" => $"a.production_number {dir}",
            "checktime" => $"a.checktime {dir}",
            "pmbom" => $"a.PMBOM {dir}",
            "itemtype" => $"a.ItemType {dir}",
            "ownerapplication" => $"a.OwnerApplication {dir}",
            "itemnum" => $"a.ItemNum {dir}",
            "componentitemdescr" => $"a.ComponentItemDescr {dir}",
            "componentitemdrawing" => $"a.ComponentItemDrawing {dir}",
            "qtyrequired" => $"a.QtyRequired {dir}",
            "componentitemum" => $"a.ComponentItemUM {dir}",
            "componentitemkittingtime" => $"a.ComponentItemKittingTime {dir}",
            "qtyrec" => $"a.QtyRec {dir}",
            "recdate" => $"a.RecDate {dir}",
            "shortage" => $"a.shortage {dir}",
            "qtyfrom" => $"a.QtyFrom {dir}",
            "fromdate" => $"a.FromDate {dir}",
            "zjsl" => $"a.zjsl {dir}",
            "jykssj" => $"a.jykssj {dir}",
            "ztsl" => $"a.ztsl {dir}",
            "jhshrq" => $"a.jhshrq {dir}",
            "jqsl" => $"a.jqsl {dir}",
            "hfjq" => $"a.hfjq {dir}",
            "purordqty" => $"a.PurOrdQty {dir}",
            "purordorddate" => $"a.PurOrdorddate {dir}",
            "purords" => $"a.PurOrds {dir}",
            "contractdate" => $"a.ContractDate {dir}",
            "contractno" => $"a.ContractNO {dir}",
            "suppliernumber" => $"a.supplier_number {dir}",
            "suppliername" => $"a.supplier_name {dir}",
            "buyer" => $"a.Buyer {dir}",
            "buyeruser" => $"a.BuyerUser {dir}",
            _ => "a.Priority ASC, a.workord ASC"
        };
    }
}