using Admin.NET.Plugin.AiDOP.DataPlatform;
using Admin.NET.Plugin.AiDOP.DataPlatform.S1Refresh;
using Admin.NET.Plugin.AiDOP.Infrastructure;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 计划联动看板服务
/// 路由前缀：/api/Order/linkageplan/...
///
/// 【租户安全边界】需认证访问（已移除类级 <c>[AllowAnonymous]</c>）；租户一律经
/// <c>AidopTenantScope.ResolveOrThrow</c> 从认证后 JWT 解析，无有效租户即拒绝，
/// 不读前端 tenantId、无默认回退（原实现：类级匿名 + 直接取 <c>_userManager.TenantId</c>，
/// 匿名时 TenantId=0 与 NULL 比较失败导致查不到数据）。
/// </summary>
[ApiDescriptionSettings(Order = 250, Description = "计划联动看板")]
[Route("api/Order")]
[NonUnify]
public class LinkagePlanService : IDynamicApiController, ITransient
{
    private const string SqlServerSourceCode = "DOPDEMORQ_SQLSERVER";

    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<LinkagePlan> _linkagePlanRep;
    private readonly UserManager _userManager;
    private readonly S1MdpSyncTransformService _s1MdpSyncTransformService;
    private readonly MdpSourceScopeFactory _scopeFactory;

    public LinkagePlanService(
        ISqlSugarClient db,
        SqlSugarRepository<LinkagePlan> linkagePlanRep,
        UserManager userManager,
        S1MdpSyncTransformService s1MdpSyncTransformService,
        MdpSourceScopeFactory scopeFactory)
    {
        _db = db;
        _linkagePlanRep = linkagePlanRep;
        _userManager = userManager;
        _s1MdpSyncTransformService = s1MdpSyncTransformService;
        _scopeFactory = scopeFactory;
    }

    [DisplayName("获取计划联动看板列表")]
    [HttpGet("linkageplan/list")]
    public async Task<object> GetLinkagePlanList([FromQuery] LinkagePlanListInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var q = _db.Queryable<LinkagePlan>()
            .Where(u => u.TenantId == tenantId)
            .WhereIF(!string.IsNullOrWhiteSpace(input.BillNo), u => u.BillNo != null && u.BillNo.Contains(input.BillNo!.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.CustomNo), u => u.CustomNo != null && u.CustomNo.Contains(input.CustomNo!.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.OrderType), u => u.OrderType == input.OrderType!.Trim())
            .WhereIF(!string.IsNullOrWhiteSpace(input.ItemNumber), u => u.ItemNumber == input.ItemNumber!.Trim());

        var paged = await q
            .OrderBy(u => u.BillNo)
            .OrderBy(u => u.ItemNumber)
            .OrderBy(u => u.LevelNum)
            .OrderBy("CASE WHEN type='计划' THEN 1 WHEN type='实际' THEN 2 WHEN type='偏差' THEN 3 ELSE 4 END")
            .ToPagedListAsync(input.Page, input.PageSize);

        var list = paged.Items.Select(u => new
        {
            id = u.Id,
            billNo = u.BillNo,
            customNo = u.CustomNo,
            orderType = u.OrderType,
            itemNumber = u.ItemNumber,
            descr = u.Descr,
            descr1 = u.Descr1,
            itemType = u.ItemType,
            qty = u.Qty,
            updateTime = u.UpdateTime?.ToString("yyyy-MM-dd"),
            sysCapacityDate = u.SysCapacityDate?.ToString("yyyy-MM-dd"),
            type = u.Type,
            bomStart = u.BomStart,
            bomEnd = u.BomEnd,
            lineStart = u.LineStart,
            lineEnd = u.LineEnd,
            productStart = u.ProductStart,
            productEnd = u.ProductEnd,
            needTime = u.NeedTime,
            sysMaterialDate = u.SysMaterialDate,
            cgNeedTime = u.CgNeedTime,
            cgEnd = u.CgEnd,
            blStart = u.BlStart,
            blEnd = u.BlEnd,
            startTime = u.StartTime,
            endTime = u.EndTime,
            ipqcJyStart = u.IpqcJyStart,
            ipqcJyEnd = u.IpqcJyEnd,
            fqcJyStart = u.FqcJyStart,
            fqcJyEnd = u.FqcJyEnd,
            rkStart = u.RkStart,
            rkEnd = u.RkEnd,
            fyStartTime = u.FyStartTime,
            fyEndTime = u.FyEndTime,
            levelNum = u.LevelNum,
            background = u.Background,
            businessId = u.BusinessId,
        }).ToList();

        return new { total = paged.Total, page = input.Page, pageSize = input.PageSize, list };
    }

    [DisplayName("获取计划联动看板详情")]
    [HttpGet("linkageplan/{id:long}")]
    public async Task<object> GetLinkagePlanDetail(long id)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var m = await _linkagePlanRep.GetFirstAsync(u => u.Id == id && u.TenantId == tenantId)
                ?? throw Oops.Oh("计划联动记录不存在");

        return new
        {
            id = m.Id,
            billNo = m.BillNo,
            customNo = m.CustomNo,
            orderType = m.OrderType,
            itemNumber = m.ItemNumber,
            descr = m.Descr,
            descr1 = m.Descr1,
            itemType = m.ItemType,
            qty = m.Qty,
            updateTime = m.UpdateTime?.ToString("yyyy-MM-dd HH:mm:ss"),
            sysCapacityDate = m.SysCapacityDate?.ToString("yyyy-MM-dd HH:mm:ss"),
            type = m.Type,
            bomStart = m.BomStart,
            bomEnd = m.BomEnd,
            lineStart = m.LineStart,
            lineEnd = m.LineEnd,
            productStart = m.ProductStart,
            productEnd = m.ProductEnd,
            needTime = m.NeedTime,
            sysMaterialDate = m.SysMaterialDate,
            cgNeedTime = m.CgNeedTime,
            cgEnd = m.CgEnd,
            blStart = m.BlStart,
            blEnd = m.BlEnd,
            startTime = m.StartTime,
            endTime = m.EndTime,
            ipqcJyStart = m.IpqcJyStart,
            ipqcJyEnd = m.IpqcJyEnd,
            fqcJyStart = m.FqcJyStart,
            fqcJyEnd = m.FqcJyEnd,
            rkStart = m.RkStart,
            rkEnd = m.RkEnd,
            fyStartTime = m.FyStartTime,
            fyEndTime = m.FyEndTime,
            levelNum = m.LevelNum,
            background = m.Background,
        };
    }

    [DisplayName("刷新计划联动数据")]
    [HttpPost("linkageplan/refresh")]
    public async Task<object> RefreshLinkagePlanData()
    {
        try
        {
            var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
            var result = await _s1MdpSyncTransformService.RunFullAsync(S1MdpRunScope.Create(tenantId, 1), triggerType: "MANUAL");

            return new
            {
                message = "数据刷新成功",
                batchId = result.BatchId,
                result.StageRows,
                result.StandardRows,
                result.DwdRows
            };
        }
        catch (Exception ex)
        {
            throw Oops.Oh($"刷新数据失败: {ex.Message}");
        }
    }

    [DisplayName("执行计划联动刷新")]
    [HttpPost("linkageplan/run")]
    public async Task<object> RunLinkagePlanProcedure()
    {
        try
        {
            var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
            var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

            // ── Step 0: 从 SQL Server 读取 InvTransHist / NbrMaster / NbrDetail，写入 MySQL 辅助表 ──
            await PrepareInvTransHistTempAsync();
            await PrepareNbrMasterTempAsync();
            await PrepareNbrDetailTempAsync();

            // ── Step 1: 只清空本租户数据（重算前清理；全表 DELETE 会误删其它租户看板数据）──
            await _db.Ado.ExecuteCommandAsync("DELETE FROM LinkagePlan WHERE tenant_id = @TenantId", new SugarParameter("@TenantId", tenantId));

            // ── Step 2: 插入计划 + 实际数据（IGNORE 跳过重复主键） ──
            await _db.Ado.ExecuteCommandAsync("""
                INSERT IGNORE INTO LinkagePlan(
                    id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType,
                    qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend,
                    productstart, productend, needtime, sys_material_date, cgneedtime, cgend,
                    blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart,
                    fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, tenant_id
                )
                SELECT
                    id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType,
                    qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend,
                    productstart, productend, needtime, sys_material_date, cgneedtime, cgend,
                    blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart,
                    fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, @TenantId
                FROM (
                    -- 计划数据
                    SELECT
                        b.id, a.bill_no, a.custom_no,
                        IF(a.order_type='1', '销售', '计划') AS order_type,
                        b.item_number, c.Descr, c.Descr1, c.ownerapplication AS ItemType,
                        b.qty, h.create_time AS update_time, b.sys_capacity_date,
                        h.create_time AS bomstart,
                        DATE_ADD(h.create_time, INTERVAL IFNULL(c.BOMDesignTime, 0) DAY) AS bomend,
                        DATE_ADD(h.create_time, INTERVAL IFNULL(c.BOMDesignTime, 0) DAY) AS linestart,
                        DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0)) DAY) AS lineend,
                        DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0)) DAY) AS productstart,
                        DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0) +
                            CEILING(IFNULL(((b.qty-1)*d.maxruntime+d.runtime)/8, 0))) DAY) AS productend,
                        h.create_time AS needtime, b.sys_material_date,
                        h.create_time AS cgneedtime, e.kitting_time AS cgend,
                        e.kitting_time AS blstart,
                        DATE_ADD(e.kitting_time, INTERVAL IFNULL(c.mfgmttr, 0) DAY) AS blend,
                        g.starttime, g.endtime,
                        g.endtime AS ipqcjystart,
                        DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) AS ipqcjyend,
                        g.endtime AS fqcjystart,
                        DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) AS fqcjyend,
                        DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) AS rkstart,
                        DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 1) DAY) AS rkend,
                        DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 1) DAY) AS fystarttime,
                        DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 2) DAY) AS fyendtime,
                        '计划' AS `type`, 2 AS levelnum, 0 AS isuse
                    FROM crm_seorder a
                    LEFT JOIN crm_seorderentry b ON a.bill_no = b.bill_no AND b.tenant_id = a.tenant_id
                    LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum AND (c.tenant_id = a.tenant_id OR a.tenant_id IS NULL)
                    LEFT JOIN (
                        SELECT RoutingCode, SUM(runtime) AS runtime, MAX(runtime) AS maxruntime
                        FROM RoutingOpDetail GROUP BY RoutingCode
                    ) d ON c.ItemNum = d.RoutingCode
                    LEFT JOIN (
                        SELECT sentry_id, MAX(kitting_time) AS kitting_time
                        FROM (
                            SELECT ar.sentry_id, bce.kitting_time,
                                   ROW_NUMBER() OVER (PARTITION BY ar.sentry_id ORDER BY ar.create_time DESC) AS rn
                            FROM b_examine_result ar
                            JOIN b_bom_child_examine bce ON ar.id = bce.examine_id
                            WHERE ar.sorderid IS NOT NULL AND bce.erp_cls IN ('2','3')
                              AND ar.tenant_id = @TenantId
                        ) ex WHERE rn = 1
                        GROUP BY sentry_id
                    ) e ON b.id = e.sentry_id
                    LEFT JOIN mes_moentry f ON b.id = f.soentry_id AND f.tenant_id = a.tenant_id
                    LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL AND h.tenant_id = a.tenant_id
                    LEFT JOIN (
                        SELECT workords, MIN(plandate) AS starttime, MAX(plandate) AS endtime
                        FROM PeriodSequenceDet GROUP BY workords
                    ) g ON f.moentry_mono = g.workords
                    WHERE a.IsDeleted = 0 AND h.create_time IS NOT NULL
                      AND a.tenant_id = @TenantId

                    UNION ALL

                    -- 实际数据（InvTransHist 从 SQL Server 临时表读取）
                    SELECT
                        b.id, a.bill_no, a.custom_no,
                        IF(a.order_type='1', '销售', '计划') AS order_type,
                        b.item_number, c.Descr, c.Descr1, c.ownerapplication AS ItemType,
                        b.qty, h.create_time AS update_time, b.sys_capacity_date,
                        h.create_time AS bomstart, nb.CreateTime AS bomend,
                        h.create_time AS linestart, nb.CreateTime AS lineend,
                        d.starttime AS productstart, d.endtime AS productend,
                        h.create_time AS needtime, b.sys_material_date,
                        e.cgneedtime, e.kitting_time AS cgend,
                        nb.CreateTime AS blstart, nd.blend,
                        g.starttime, g.endtime,
                        e.FAPPLYTIME AS ipqcjystart, e.FINSPESTARTDATE AS ipqcjyend,
                        g.endtime AS fqcjystart, fqc.fqcDate AS fqcjyend,
                        i.starttime AS rkstart, i.endtime AS rkend,
                        asn.starttime AS fystarttime, asn.endtime AS fyendtime,
                        '实际' AS `type`, 1 AS levelnum, 0 AS isuse
                    FROM crm_seorder a
                    LEFT JOIN crm_seorderentry b ON a.bill_no = b.bill_no AND b.tenant_id = a.tenant_id
                    LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum AND (c.tenant_id = a.tenant_id OR a.tenant_id IS NULL)
                    LEFT JOIN (
                        SELECT pr.sentry_id,
                               MAX(sh.createtime) AS kitting_time,
                               MIN(jq.jqhf) AS cgneedtime,
                               MIN(iqcjy.FINSPESTARTDATE) AS FINSPESTARTDATE,
                               MAX(iqcjy.FAPPLYTIME) AS FAPPLYTIME
                        FROM srm_pr_main pr
                        LEFT JOIN PurOrdDetail pod ON pr.pr_billno = pod.Req AND pod.tenant_id = pr.tenant_id
                        LEFT JOIN vscm_cgshrk sh ON pod.PurOrd = sh.OrdNbr AND pod.Line = sh.OrdLine
                        LEFT JOIN scm_jhjh_jq jq ON pod.PurOrd = jq.cgdd AND pod.Line = jq.ddhh
                        LEFT JOIN (
                            SELECT f.po_bill, f.po_billline,
                                   MIN(a2.FINSPESTARTDATE) AS FINSPESTARTDATE,
                                   MAX(b2.FAPPLYTIME) AS FAPPLYTIME
                            FROM qms_qcp_inspbill a2
                            LEFT JOIN qms_qcp_inspecapplyn b2 ON a2.lydjbh = b2.FBILLNO
                            LEFT JOIN qms_qcp_insappnentry c2 ON b2.id = c2.glid
                            JOIN scm_shd cd ON c2.shdh = cd.shddh
                            LEFT JOIN scm_shdzb f ON f.shpc = c2.FLOTNUMBER
                                AND f.sh_material_code = c2.FMATERIALCFG
                                AND f.glid = cd.id
                            GROUP BY f.po_bill, f.po_billline
                        ) iqcjy ON pod.PurOrd = iqcjy.po_bill AND pod.Line = iqcjy.po_billline
                        WHERE jq.flag = '1' AND pr.sentry_id IS NOT NULL
                          AND pr.tenant_id = @TenantId
                        GROUP BY pr.sentry_id
                    ) e ON e.sentry_id = b.id
                    LEFT JOIN mes_moentry f ON b.id = f.soentry_id AND f.tenant_id = a.tenant_id
                    LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL AND h.tenant_id = a.tenant_id
                    LEFT JOIN (
                        SELECT workords, MIN(plandate) AS starttime, MAX(plandate) AS endtime
                        FROM PeriodSequenceDet GROUP BY workords
                    ) d ON h.morder_no = d.workords
                    LEFT JOIN _tmp_nbr_master nb ON h.morder_no = nb.WorkOrd
                    LEFT JOIN _tmp_nbr_detail nd ON h.morder_no = nd.WorkOrd
                    LEFT JOIN (
                        SELECT workords, MIN(plandate) AS starttime, MAX(plandate) AS endtime
                        FROM PeriodSequenceDet WHERE CompQty > 0 GROUP BY workords
                    ) g ON f.moentry_mono = g.workords
                    LEFT JOIN _tmp_inv_trans_hist i ON h.morder_no = i.WorkOrd
                    LEFT JOIN (
                        SELECT ad.OrdNbr, ad.ContainerItem, ad.OrdLine,
                               MIN(ad.CreateTime) AS starttime, MAX(ad.UpdateTime) AS endtime
                        FROM ASNBOLShipperDetail ad
                        INNER JOIN ASNBOLShipperMaster am ON ad.ASNBOLShipperRecID = am.RecID
                        WHERE am.shtype = 'SH' AND am.typed <> 'S'
                        GROUP BY ad.OrdNbr, ad.ContainerItem, ad.OrdLine
                    ) asn ON b.bill_no = asn.OrdNbr AND b.item_number = asn.ContainerItem
                    LEFT JOIN WorkOrdMaster wm ON h.morder_no = wm.WorkOrd AND wm.tenant_id = a.tenant_id
                    LEFT JOIN (
                        SELECT sczld, MAX(FINSPEENDDATE) AS fqcDate
                        FROM qms_qcpp_inspbill GROUP BY sczld
                    ) fqc ON wm.Batch = fqc.sczld
                    WHERE a.IsDeleted = 0 AND h.create_time IS NOT NULL
                      AND a.tenant_id = @TenantId
                ) AS viewall
                """, pars);

            // ── Step 3: 插入偏差数据 ──
            await _db.Ado.ExecuteCommandAsync("""
                INSERT IGNORE INTO LinkagePlan(
                    id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType,
                    qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend,
                    productstart, productend, needtime, sys_material_date, cgneedtime, cgend,
                    blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart,
                    fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, tenant_id
                )
                SELECT
                    a.id, a.bill_no, a.custom_no, a.order_type, b.item_number,
                    a.Descr, a.Descr1, a.ItemType, b.qty, a.update_time, b.sys_capacity_date,
                    DATEDIFF(b.bomstart, a.bomstart), DATEDIFF(b.bomend, a.bomend),
                    DATEDIFF(b.linestart, a.linestart), DATEDIFF(b.lineend, a.lineend),
                    DATEDIFF(b.productstart, a.productstart), DATEDIFF(b.productend, a.productend),
                    DATEDIFF(b.needtime, a.needtime), DATEDIFF(b.sys_material_date, a.sys_material_date),
                    DATEDIFF(b.cgneedtime, a.cgneedtime), DATEDIFF(b.cgend, a.cgend),
                    DATEDIFF(b.blstart, a.blstart), DATEDIFF(b.blend, a.blend),
                    DATEDIFF(b.starttime, a.starttime), DATEDIFF(b.endtime, a.endtime),
                    DATEDIFF(b.ipqcjystart, a.ipqcjystart), DATEDIFF(b.ipqcjyend, a.ipqcjyend),
                    DATEDIFF(b.fqcjystart, a.fqcjystart), DATEDIFF(b.fqcjyend, a.fqcjyend),
                    DATEDIFF(b.rkstart, a.rkstart), DATEDIFF(b.rkend, a.rkend),
                    DATEDIFF(b.fystarttime, a.fystarttime), DATEDIFF(b.fyendtime, a.fyendtime),
                    '偏差', 0, 0, @TenantId
                FROM (SELECT * FROM LinkagePlan WHERE `type` = '计划' AND tenant_id = @TenantId) a
                LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际' AND tenant_id = @TenantId) b ON a.id = b.id
                """, pars);

            // ── Step 4: 更新背景色标记 ──
            await _db.Ado.ExecuteCommandAsync("""
                UPDATE LinkagePlan a
                LEFT JOIN (
                    SELECT a2.id, a2.`type`,
                        CONCAT(
                            CASE WHEN a2.`type` = '计划' THEN
                                CASE WHEN a2.bomstart IS NOT NULL AND b2.bomstart IS NULL AND DATEDIFF(NOW(), a2.bomstart) > 7 THEN 'bomstart:red;'
                                     WHEN a2.bomstart IS NOT NULL AND b2.bomstart IS NULL AND DATEDIFF(NOW(), a2.bomstart) > 0 THEN 'bomstart:yellow;'
                                     ELSE '' END
                            WHEN a2.`type` = '偏差' THEN
                                CASE WHEN IFNULL(CAST(a2.bomstart AS SIGNED), 0) > 7 THEN 'bomstart:red;'
                                     WHEN IFNULL(CAST(a2.bomstart AS SIGNED), 0) > 0 THEN 'bomstart:yellow;'
                                     ELSE '' END
                            ELSE '' END,
                            CASE WHEN a2.`type` = '计划' THEN
                                CASE WHEN a2.bomend IS NOT NULL AND b2.bomend IS NULL AND DATEDIFF(NOW(), a2.bomend) > 7 THEN 'bomend:red;'
                                     WHEN a2.bomend IS NOT NULL AND b2.bomend IS NULL AND DATEDIFF(NOW(), a2.bomend) > 0 THEN 'bomend:yellow;'
                                     ELSE '' END
                            WHEN a2.`type` = '偏差' THEN
                                CASE WHEN IFNULL(CAST(a2.bomend AS SIGNED), 0) > 7 THEN 'bomend:red;'
                                     WHEN IFNULL(CAST(a2.bomend AS SIGNED), 0) > 0 THEN 'bomend:yellow;'
                                     ELSE '' END
                            ELSE '' END,
                            CASE WHEN a2.`type` = '计划' THEN
                                CASE WHEN a2.starttime IS NOT NULL AND b2.starttime IS NULL AND DATEDIFF(NOW(), a2.starttime) > 7 THEN 'starttime:red;'
                                     WHEN a2.starttime IS NOT NULL AND b2.starttime IS NULL AND DATEDIFF(NOW(), a2.starttime) > 0 THEN 'starttime:yellow;'
                                     ELSE '' END
                            WHEN a2.`type` = '偏差' THEN
                                CASE WHEN IFNULL(CAST(a2.starttime AS SIGNED), 0) > 7 THEN 'starttime:red;'
                                     WHEN IFNULL(CAST(a2.starttime AS SIGNED), 0) > 0 THEN 'starttime:yellow;'
                                     ELSE '' END
                            ELSE '' END,
                            CASE WHEN a2.`type` = '计划' THEN
                                CASE WHEN a2.endtime IS NOT NULL AND b2.endtime IS NULL AND DATEDIFF(NOW(), a2.endtime) > 7 THEN 'endtime:red;'
                                     WHEN a2.endtime IS NOT NULL AND b2.endtime IS NULL AND DATEDIFF(NOW(), a2.endtime) > 0 THEN 'endtime:yellow;'
                                     ELSE '' END
                            WHEN a2.`type` = '偏差' THEN
                                CASE WHEN IFNULL(CAST(a2.endtime AS SIGNED), 0) > 7 THEN 'endtime:red;'
                                     WHEN IFNULL(CAST(a2.endtime AS SIGNED), 0) > 0 THEN 'endtime:yellow;'
                                     ELSE '' END
                            ELSE '' END
                        ) AS background
                    FROM LinkagePlan a2
                    LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际' AND tenant_id = @TenantId) b2 ON a2.id = b2.id
                    LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '偏差' AND tenant_id = @TenantId) c2 ON a2.id = c2.id
                    WHERE a2.tenant_id = @TenantId
                ) AS bg ON a.id = bg.id AND a.`type` = bg.`type`
                SET a.background = bg.background
                WHERE a.tenant_id = @TenantId
                """, pars);

            // ── 清理辅助表 ──
            await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_inv_trans_hist");
            await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_nbr_master");
            await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_nbr_detail");

            return new { message = "计划联动刷新成功" };
        }
        catch (Exception ex)
        {
            throw Oops.Oh($"执行计划联动刷新失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 从 SQL Server 读取 InvTransHist，写入 MySQL 临时表 _tmp_inv_trans_hist。
    /// </summary>
    private async Task PrepareInvTransHistTempAsync()
    {
        await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_inv_trans_hist");
        await _db.Ado.ExecuteCommandAsync("""
            CREATE TABLE IF NOT EXISTS _tmp_inv_trans_hist (
                WorkOrd VARCHAR(64) PRIMARY KEY,
                starttime DATETIME NULL,
                endtime DATETIME NULL
            )
            """);

        var remote = await _scopeFactory.GetScopeAsync(SqlServerSourceCode);
        var rows = await remote.Ado.SqlQueryAsync<InvTransHistRow>("""
            SELECT WorkOrd,
                   MIN(CreateTime) AS starttime,
                   MAX(CreateTime) AS endtime
            FROM InvTransHist
            WHERE TransType = 'rct-wo' AND QtyChange > 0
            GROUP BY WorkOrd
            """);

        if (rows.Count == 0)
            return;

        // 分批 INSERT（每批 500 条）
        var insertPars = new List<SugarParameter>();
        for (var i = 0; i < rows.Count; i++)
        {
            var r = rows[i];
            if (string.IsNullOrWhiteSpace(r.WorkOrd)) continue;
            insertPars.Add(new SugarParameter($"@wo{i}", r.WorkOrd));
            insertPars.Add(new SugarParameter($"@st{i}", r.StartTime));
            insertPars.Add(new SugarParameter($"@et{i}", r.EndTime));
        }

        const int batchSize = 500;
        for (var offset = 0; offset < insertPars.Count; offset += batchSize * 3)
        {
            var batchEnd = Math.Min(offset + batchSize * 3, insertPars.Count);
            var batchPars = insertPars.GetRange(offset, batchEnd - offset);
            var batchValues = new List<string>();
            for (var j = offset; j < batchEnd; j += 3)
                batchValues.Add($"({insertPars[j].ParameterName}, {insertPars[j+1].ParameterName}, {insertPars[j+2].ParameterName})");

            await _db.Ado.ExecuteCommandAsync(
                $"INSERT INTO _tmp_inv_trans_hist (WorkOrd, starttime, endtime) VALUES {string.Join(",", batchValues)}",
                batchPars);
        }
    }

    private sealed class InvTransHistRow
    {
        public string? WorkOrd { get; set; }
        public DateTime? StartTime { get; set; }
        public DateTime? EndTime { get; set; }
    }

    /// <summary>
    /// 从 SQL Server 读取 NbrMaster (Type='SM')，写入 MySQL 临时表 _tmp_nbr_master。
    /// </summary>
    private async Task PrepareNbrMasterTempAsync()
    {
        await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_nbr_master");
        await _db.Ado.ExecuteCommandAsync("""
            CREATE TABLE IF NOT EXISTS _tmp_nbr_master (
                WorkOrd VARCHAR(64) PRIMARY KEY,
                CreateTime DATETIME NULL
            )
            """);

        var remote = await _scopeFactory.GetScopeAsync(SqlServerSourceCode);
        var rows = await remote.Ado.SqlQueryAsync<NbrMasterRow>("""
            SELECT WorkOrd, MIN(CreateTime) AS CreateTime
            FROM NbrMaster WHERE Type = 'SM'
            GROUP BY WorkOrd
            """);

        if (rows.Count == 0)
            return;

        var insertPars = new List<SugarParameter>();
        for (var i = 0; i < rows.Count; i++)
        {
            var r = rows[i];
            if (string.IsNullOrWhiteSpace(r.WorkOrd)) continue;
            insertPars.Add(new SugarParameter($"@wo{i}", r.WorkOrd));
            insertPars.Add(new SugarParameter($"@ct{i}", r.CreateTime));
        }

        const int batchSize = 500;
        for (var offset = 0; offset < insertPars.Count; offset += batchSize * 2)
        {
            var batchEnd = Math.Min(offset + batchSize * 2, insertPars.Count);
            var batchPars = insertPars.GetRange(offset, batchEnd - offset);
            var batchValues = new List<string>();
            for (var j = offset; j < batchEnd; j += 2)
                batchValues.Add($"({insertPars[j].ParameterName}, {insertPars[j+1].ParameterName})");

            await _db.Ado.ExecuteCommandAsync(
                $"INSERT INTO _tmp_nbr_master (WorkOrd, CreateTime) VALUES {string.Join(",", batchValues)}",
                batchPars);
        }
    }

    /// <summary>
    /// 从 SQL Server 读取 NbrDetail，写入 MySQL 临时表 _tmp_nbr_detail。
    /// </summary>
    private async Task PrepareNbrDetailTempAsync()
    {
        await _db.Ado.ExecuteCommandAsync("DROP TABLE IF EXISTS _tmp_nbr_detail");
        await _db.Ado.ExecuteCommandAsync("""
            CREATE TABLE IF NOT EXISTS _tmp_nbr_detail (
                WorkOrd VARCHAR(64) PRIMARY KEY,
                blend DATETIME NULL
            )
            """);

        var remote = await _scopeFactory.GetScopeAsync(SqlServerSourceCode);
        var rows = await remote.Ado.SqlQueryAsync<NbrDetailRow>("""
            SELECT WorkOrd, MIN(UpdateTime) AS blend
            FROM NbrDetail
            GROUP BY WorkOrd
            """);

        if (rows.Count == 0)
            return;

        var insertPars = new List<SugarParameter>();
        for (var i = 0; i < rows.Count; i++)
        {
            var r = rows[i];
            if (string.IsNullOrWhiteSpace(r.WorkOrd)) continue;
            insertPars.Add(new SugarParameter($"@wo{i}", r.WorkOrd));
            insertPars.Add(new SugarParameter($"@bl{i}", r.Blend));
        }

        const int batchSize = 500;
        for (var offset = 0; offset < insertPars.Count; offset += batchSize * 2)
        {
            var batchEnd = Math.Min(offset + batchSize * 2, insertPars.Count);
            var batchPars = insertPars.GetRange(offset, batchEnd - offset);
            var batchValues = new List<string>();
            for (var j = offset; j < batchEnd; j += 2)
                batchValues.Add($"({insertPars[j].ParameterName}, {insertPars[j+1].ParameterName})");

            await _db.Ado.ExecuteCommandAsync(
                $"INSERT INTO _tmp_nbr_detail (WorkOrd, blend) VALUES {string.Join(",", batchValues)}",
                batchPars);
        }
    }

    private sealed class NbrMasterRow
    {
        public string? WorkOrd { get; set; }
        public DateTime? CreateTime { get; set; }
    }

    private sealed class NbrDetailRow
    {
        public string? WorkOrd { get; set; }
        public DateTime? Blend { get; set; }
    }
}