using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.ProcurementExecution;
using Admin.NET.Plugin.AiDOP.WorkOrder;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// S4 采购执行闭环：缺料生成 PR → 合并 → 要货令转 DO/PO → 外部推送日志。
/// 对应旧系统 AutoTransferDoOrPo / AutomaticPrAdjustDate 业务意图。
/// </summary>
[ApiDescriptionSettings(Order = 309, Description = "采购执行闭环")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class ProcurementPipelineService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly PurchaseRequestFromShortageService _shortageService;
    private readonly PurchaseRequestMergeService _mergeService;
    private readonly NumberRuleService _numberRuleService;
    private readonly PurchaseOrderTransferService _transferService;
    private readonly PurchaseRequestExternalPushService _pushService;
    private readonly AidopActionRunLogWriter _runLog;
    private readonly S4MdpSyncTransformService _s4MdpSync;
    private readonly S4ReadPathConsistencyService _readPathCheck;

    public ProcurementPipelineService(
        ISqlSugarClient db,
        UserManager userManager,
        PurchaseRequestFromShortageService shortageService,
        PurchaseRequestMergeService mergeService,
        NumberRuleService numberRuleService,
        PurchaseOrderTransferService transferService,
        PurchaseRequestExternalPushService pushService,
        AidopActionRunLogWriter runLog,
        S4MdpSyncTransformService s4MdpSync,
        S4ReadPathConsistencyService readPathCheck)
    {
        _db = db;
        _userManager = userManager;
        _shortageService = shortageService;
        _mergeService = mergeService;
        _numberRuleService = numberRuleService;
        _transferService = transferService;
        _pushService = pushService;
        _runLog = runLog;
        _s4MdpSync = s4MdpSync;
        _readPathCheck = readPathCheck;
    }

    /// <summary>执行采购闭环（合并待处理 PR、转单）。</summary>
    [DisplayName("采购执行闭环")]
    [HttpPost("procurement/execute-pipeline")]
    public async Task<ProcurementPipelineResult> ExecutePipeline(
        [FromQuery] string? domain,
        [FromQuery] bool createFromShortage = true,
        [FromQuery] bool mergeHistorical = true,
        [FromQuery] bool enableExternalPush = true)
    {
        var tenantId = ResolveTenantId(domain);
        var account = _userManager.Account ?? "system";
        var logId = await _runLog.StartAsync("S4_PROCUREMENT_PIPELINE", tenantId, "tenant", tenantId, domain ?? tenantId.ToString());

        try
        {
            var result = await ExecuteCoreAsync(tenantId, account, createFromShortage, mergeHistorical, enableExternalPush);
            result.ReadPathConsistency = await _readPathCheck.CheckTenantAsync(tenantId);
            result.Warnings.AddRange(await TryTriggerS4MdpRefreshAsync(result));
            await _runLog.SuccessAsync(logId, result.Message, result);
            return result;
        }
        catch (Exception ex)
        {
            await _runLog.FailedAsync(logId, ex.Message, new { tenantId, domain });
            throw Oops.Oh(ex.Message);
        }
    }

    /// <summary>S4 读路径一致性检查（运行表 vs DWD）。</summary>
    [DisplayName("S4读路径一致性检查")]
    [HttpGet("procurement/read-path-consistency")]
    public async Task<S4ReadPathConsistencyResult> ReadPathConsistency([FromQuery] string? domain)
    {
        var tenantId = ResolveTenantId(domain);
        return await _readPathCheck.CheckTenantAsync(tenantId);
    }

    public async Task<ProcurementPipelineResult> ExecuteCoreAsync(
        long tenantId,
        string account,
        bool createFromShortage,
        bool mergeHistorical = true,
        bool enableExternalPush = true)
    {
        var result = new ProcurementPipelineResult();
        var now = DateTime.Now;

        await _db.Ado.BeginTranAsync();
        try
        {
            var newRequests = new List<PurchaseRequestMain>();
            var deleteVouchers = new List<long>();
            if (createFromShortage)
            {
                var candidates = await _shortageService.BuildFromResourceCheckAsync(tenantId, account);
                result.ShortageItemCount = candidates.Count;

                // 幂等处理：对比已有 PR，更新/删除/保留
                var (toAdd, toDelete) = await _shortageService.ReconcileAsync(tenantId, candidates, account);
                newRequests = toAdd;
                deleteVouchers = toDelete;
                result.PrUpdatedCount = candidates.Count - toAdd.Count - toDelete.Count;
            }

            if (newRequests.Count > 0)
            {
                // 保留各工单独立 PR + 创建合并 PR（数量重新计算）
                var split = await _mergeService.SplitMergeWithRecalcAsync(newRequests, account);

                var individualPrs = split.IndividualRequests;
                var mergedPrs = split.MergedRequests;

                // 分配编号
                await AssignPrNumbersAsync(individualPrs, account);
                await AssignPrNumbersAsync(mergedPrs, account);

                // 独立 PR: state=0（合并后旧PR关闭，不参与 DO/PO 转单）
                foreach (var pr in individualPrs) pr.State = 0;
                // 关联独立 PR 到合并 PR 编号
                foreach (var group in individualPrs.GroupBy(x => new { x.IcitemId, x.PrPurchaseId }))
                {
                    var merged = mergedPrs.FirstOrDefault(m =>
                        m.IcitemId == group.Key.IcitemId && m.PrPurchaseId == group.Key.PrPurchaseId);
                    if (merged is not null)
                    {
                        foreach (var ind in group)
                            ind.IndividualPrReferBillNo = merged.PrBillNo;
                    }
                }

                // 插入独立 PR（参考）
                await InsertIndividualPurchaseRequestsAsync(individualPrs);
                // 插入合并 PR（可转单）
                await InsertPurchaseRequestsAsync(mergedPrs);

                result.PrCreatedCount = mergedPrs.Count;
                result.IndividualPrCount = individualPrs.Count;
                result.PrMergeReducedCount = Math.Max(0, individualPrs.Count - mergedPrs.Count);
            }

            if (mergeHistorical)
            {
                var historicalMerge = await _mergeService.MergeTenantPendingAsync(tenantId, account);
                result.PrMergeReducedCount += historicalMerge.ReducedCount;
                result.HistoricalPrMergedGroups = historicalMerge.MergedGroupCount;
                result.PrCreatedCount += historicalMerge.CreatedPrCount;
            }

            // 删除已无缺料的旧 PR
            if (deleteVouchers.Count > 0)
            {
                foreach (var v in deleteVouchers)
                {
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE srm_pr_main
                        SET IsDeleted = 1, update_by_name = @User, update_time = @Now
                        WHERE voucher = @Voucher
                        """,
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", DateTime.Now),
                        new SugarParameter("@Voucher", v));
                }
                result.PrDeletedCount = deleteVouchers.Count;
            }

            // ── 生成物料交货计划（ic_demandschedule）──
            result.DemandScheduleCount = await GenerateDemandScheduleAsync(tenantId, account);

            var pending = await LoadPendingPurchaseRequestsAsync(tenantId);
            result.PendingPrCount = pending.Count;

            if (pending.Count > 0)
            {
                var transfer = await _transferService.TransferGeneratedRequireGoodsAsync(pending, account);
                result.PoCreatedCount = transfer.CreatedOrderCount;
                result.PoTransferredPrCount = transfer.TransferredPrCount;
                result.PoOccupyRehangedCount = transfer.PoOccupyRehangedCount;
                result.CreatedPoNumbers = transfer.CreatedOrders;
                await PersistPrStateUpdatesAsync(transfer.TransferredPrIds, 4, account);
            }

            await _db.Ado.CommitTranAsync();
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }

        result.Message = BuildMessage(result);
        return result;
    }

    private async Task<List<PurchaseRequestMain>> LoadPendingPurchaseRequestsAsync(long tenantId)
    {
        var windowEnd = DateTime.Today.AddDays(35);
        var rows = await _db.Ado.SqlQueryAsync<PurchaseRequestMain>(
            """
            SELECT
                Id, pr_billno AS PrBillNo, pr_purchaseid AS PrPurchaseId,
                pr_purchasenumber AS PrPurchaseNumber, pr_purchasename AS PrPurchaseName,
                pr_purchaser AS PrPurchaser, pr_purchaser_num AS PrPurchaserNum,
                pr_rqty AS PrRqty, pr_aqty AS PrAqty, pr_sqty AS PrSqty,
                icitem_id AS IcitemId, icitem_name AS IcitemName,
                pr_ssend_date AS PrSsendDate, pr_sarrive_date AS PrSarriveDate,
                pr_unit AS PrUnit, state AS State, pr_type AS PrType,
                currencytype AS CurrencyType, tenant_id AS TenantId,
                factory_id AS FactoryId, org_id AS OrgId, company_id AS CompanyId,
                IsRequireGoods, supplier_type AS SupplierType, IsDeleted
            FROM srm_pr_main
            WHERE tenant_id = @TenantId
              AND IFNULL(IsDeleted, 0) = 0
              AND IFNULL(state, 0) = 1
              AND IFNULL(analogcalcversion, '') = ''
              AND pr_ssend_date IS NOT NULL
              AND pr_ssend_date <= @WindowEnd
            ORDER BY pr_ssend_date, Id
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WindowEnd", windowEnd));

        return rows;
    }

    private async Task AssignPrNumbersAsync(List<PurchaseRequestMain> requests, string account)
    {
        foreach (var group in requests.GroupBy(x => x.TenantId.ToString()))
        {
            var rows = group.ToList();
            var numbers = await _numberRuleService.NextBatchInCurrentTransactionAsync("PR", group.Key, rows.Count, account);
            if (numbers.Count < rows.Count)
                throw Oops.Oh("采购申请编号生成失败");
            for (var i = 0; i < rows.Count; i++)
                rows[i].PrBillNo = numbers[i].Trim();
        }
    }

    private async Task InsertPurchaseRequestsAsync(List<PurchaseRequestMain> requests)
    {
        foreach (var pr in requests)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO srm_pr_main
                (Id,pr_billno,pr_mono,entity_id,pr_purchaseid,pr_purchasenumber,pr_purchasename,pr_purchaser,pr_purchaser_num,
                 pr_rqty,pr_aqty,pr_sqty,icitem_id,icitem_name,pr_ssend_date,pr_sarrive_date,pr_unit,state,pr_type,currencytype,
                 create_by_name,create_time,update_by_name,update_time,tenant_id,factory_id,org_id,IsDeleted,company_id,IsRequireGoods,supplier_type)
                VALUES
                (@Id,@PrBillNo,@PrMono,@EntityId,@PrPurchaseId,@PrPurchaseNumber,@PrPurchaseName,@PrPurchaser,@PrPurchaserNum,
                 @PrRqty,@PrAqty,@PrSqty,@IcitemId,@IcitemName,@PrSsendDate,@PrSarriveDate,@PrUnit,@State,@PrType,@CurrencyType,
                 @CreateByName,@CreateTime,@UpdateByName,@UpdateTime,@TenantId,@FactoryId,@OrgId,0,@CompanyId,@IsRequireGoods,@SupplierType)
                """,
                new SugarParameter("@Id", pr.Id),
                new SugarParameter("@PrBillNo", pr.PrBillNo),
                new SugarParameter("@PrMono", pr.PrMono ?? (object)DBNull.Value),
                new SugarParameter("@EntityId", pr.EntityId ?? (object)DBNull.Value),
                new SugarParameter("@PrPurchaseId", pr.PrPurchaseId),
                new SugarParameter("@PrPurchaseNumber", pr.PrPurchaseNumber),
                new SugarParameter("@PrPurchaseName", pr.PrPurchaseName),
                new SugarParameter("@PrPurchaser", pr.PrPurchaser),
                new SugarParameter("@PrPurchaserNum", pr.PrPurchaserNum),
                new SugarParameter("@PrRqty", pr.PrRqty),
                new SugarParameter("@PrAqty", pr.PrAqty),
                new SugarParameter("@PrSqty", pr.PrSqty),
                new SugarParameter("@IcitemId", pr.IcitemId),
                new SugarParameter("@IcitemName", pr.IcitemName),
                new SugarParameter("@PrSsendDate", pr.PrSsendDate),
                new SugarParameter("@PrSarriveDate", pr.PrSarriveDate),
                new SugarParameter("@PrUnit", pr.PrUnit),
                new SugarParameter("@State", pr.State ?? 1),
                new SugarParameter("@PrType", pr.PrType ?? 3),
                new SugarParameter("@CurrencyType", pr.CurrencyType),
                new SugarParameter("@CreateByName", pr.CreateByName),
                new SugarParameter("@CreateTime", pr.CreateTime),
                new SugarParameter("@UpdateByName", pr.UpdateByName),
                new SugarParameter("@UpdateTime", pr.UpdateTime),
                new SugarParameter("@TenantId", pr.TenantId),
                new SugarParameter("@FactoryId", pr.FactoryId),
                new SugarParameter("@OrgId", pr.OrgId),
                new SugarParameter("@CompanyId", pr.CompanyId ?? 1000),
                new SugarParameter("@IsRequireGoods", pr.IsRequireGoods),
                new SugarParameter("@SupplierType", pr.SupplierType));
        }
    }

    private async Task InsertIndividualPurchaseRequestsAsync(List<PurchaseRequestMain> requests)
    {
        foreach (var pr in requests)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO srm_pr_main
                (Id,pr_billno,pr_mono,entity_id,pr_purchaseid,pr_purchasenumber,pr_purchasename,pr_purchaser,pr_purchaser_num,
                 pr_rqty,pr_aqty,pr_sqty,icitem_id,icitem_name,pr_ssend_date,pr_sarrive_date,pr_unit,state,pr_type,currencytype,
                 create_by_name,create_time,update_by_name,update_time,tenant_id,factory_id,org_id,IsDeleted,company_id,
                 IsRequireGoods,supplier_type,refer_pr_billno)
                VALUES
                (@Id,@PrBillNo,@PrMono,@EntityId,@PrPurchaseId,@PrPurchaseNumber,@PrPurchaseName,@PrPurchaser,@PrPurchaserNum,
                 @PrRqty,@PrAqty,@PrSqty,@IcitemId,@IcitemName,@PrSsendDate,@PrSarriveDate,@PrUnit,@State,@PrType,@CurrencyType,
                 @CreateByName,@CreateTime,@UpdateByName,@UpdateTime,@TenantId,@FactoryId,@OrgId,0,@CompanyId,
                 @IsRequireGoods,@SupplierType,@ReferPrBillNo)
                """,
                new SugarParameter("@Id", pr.Id),
                new SugarParameter("@PrBillNo", pr.PrBillNo),
                new SugarParameter("@PrMono", pr.PrMono ?? (object)DBNull.Value),
                new SugarParameter("@EntityId", pr.EntityId ?? (object)DBNull.Value),
                new SugarParameter("@PrPurchaseId", pr.PrPurchaseId),
                new SugarParameter("@PrPurchaseNumber", pr.PrPurchaseNumber),
                new SugarParameter("@PrPurchaseName", pr.PrPurchaseName),
                new SugarParameter("@PrPurchaser", pr.PrPurchaser),
                new SugarParameter("@PrPurchaserNum", pr.PrPurchaserNum),
                new SugarParameter("@PrRqty", pr.PrRqty),
                new SugarParameter("@PrAqty", pr.PrAqty),
                new SugarParameter("@PrSqty", pr.PrSqty),
                new SugarParameter("@IcitemId", pr.IcitemId),
                new SugarParameter("@IcitemName", pr.IcitemName),
                new SugarParameter("@PrSsendDate", pr.PrSsendDate),
                new SugarParameter("@PrSarriveDate", pr.PrSarriveDate),
                new SugarParameter("@PrUnit", pr.PrUnit),
                new SugarParameter("@State", pr.State ?? 0),
                new SugarParameter("@PrType", pr.PrType ?? 3),
                new SugarParameter("@CurrencyType", pr.CurrencyType),
                new SugarParameter("@CreateByName", pr.CreateByName),
                new SugarParameter("@CreateTime", pr.CreateTime),
                new SugarParameter("@UpdateByName", pr.UpdateByName),
                new SugarParameter("@UpdateTime", pr.UpdateTime),
                new SugarParameter("@TenantId", pr.TenantId),
                new SugarParameter("@FactoryId", pr.FactoryId),
                new SugarParameter("@OrgId", pr.OrgId),
                new SugarParameter("@CompanyId", pr.CompanyId ?? 1000),
                new SugarParameter("@IsRequireGoods", pr.IsRequireGoods),
                new SugarParameter("@SupplierType", pr.SupplierType),
                new SugarParameter("@ReferPrBillNo", pr.IndividualPrReferBillNo ?? (object)DBNull.Value));
        }
    }

    private async Task PersistPrStateUpdatesAsync(IReadOnlyList<long> prIds, int state, string account)
    {
        if (prIds.Count == 0) return;
        foreach (var id in prIds.Distinct())
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE srm_pr_main
                SET state = @State, update_by_name = @User, update_time = @Now
                WHERE Id = @Id
                """,
                new SugarParameter("@State", state),
                new SugarParameter("@User", account),
                new SugarParameter("@Now", DateTime.Now),
                new SugarParameter("@Id", id));
        }
    }

    private long ResolveTenantId(string? domain)
    {
        if (!string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var tid) && tid > 0)
            return tid;
        return AidopTenantHelper.Resolve(App.HttpContext);
    }

    private async Task<List<string>> TryTriggerS4MdpRefreshAsync(ProcurementPipelineResult result)
    {
        var warnings = new List<string>();
        if (result.PrCreatedCount == 0 && result.PoCreatedCount == 0)
            return warnings;

        try
        {
            var mdp = await _s4MdpSync.RunFullAsync(triggerType: "PROCUREMENT_PIPELINE");
            result.S4MdpRefreshed = true;
            result.S4MdpBatchId = mdp.BatchId;
        }
        catch (Exception ex)
        {
            warnings.Add($"S4 MDP/DWD 刷新未完成：{ex.Message}");
        }
        return warnings;
    }

    /// <summary>
    /// 生成物料交货计划（ic_demandschedule）：按 8 周窗口汇总工单缺料与领料单待发料，
    /// 扣减库存和已发布交货单在途后写入需求计划。
    /// </summary>
    private async Task<int> GenerateDemandScheduleAsync(long tenantId, string account)
    {
        var now = DateTime.Now;

        // ── 1. 软删除旧未发布需求计划 + 禁用旧未发布交货单 + 标记历史版本 ──
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE ic_demandschedule
            SET IsDeleted = 1, update_by_name = @User, update_time = @Now,
                remarks = CONCAT(@NowStr, ',重新生成交货计划把未发布交货计划软删除')
            WHERE tenant_id = @TenantId
              AND IFNULL(IsDeleted, 0) = 0
              AND IFNULL(ishistoryversion, 'N') <> 'Y'
              AND IFNULL(status, '') <> 'P'
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@NowStr", now.ToString("yyyy-MM-dd HH:mm:ss")),
            new SugarParameter("@TenantId", tenantId));

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE srm_polist_ds
            SET isactive = 0, updateuser = @User, updatetime = @Now,
                remarks = CONCAT(@NowStr, ',重新生成交货计划把未发布交货单禁用')
            WHERE tenant_id = @TenantId AND isactive = 1 AND IFNULL(status, 'N') = 'N'
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@NowStr", now.ToString("yyyy-MM-dd HH:mm:ss")),
            new SugarParameter("@TenantId", tenantId));

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE ic_demandschedule
            SET ishistoryversion = 'Y', historyversionTime = @Now
            WHERE tenant_id = @TenantId
              AND IFNULL(IsDeleted, 0) = 0
              AND IFNULL(ishistoryversion, 'N') <> 'Y'
            """,
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId));

        // ── 2. 计算 8 周窗口（从下周一开始） ──
        var weekday = (int)now.DayOfWeek;
        var addDays = weekday == 0 ? 1 : 8 - weekday;
        var beginTime = now.Date.AddDays(addDays);
        var endTime = beginTime.AddDays(21 + 28 + 6).Add(new TimeSpan(23, 59, 59));

        // ── 3. 加载窗口内工单（Status 为空或 P） ──
        var workOrds = await _db.Ado.SqlQueryAsync<WorkOrdRow>(
            """
            SELECT WorkOrd, OrdDate, Status
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND OrdDate >= @Begin AND OrdDate <= @End
              AND (Status IS NULL OR UPPER(Status) = 'P')
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@Begin", beginTime),
            new SugarParameter("@End", endTime));

        // ── 4. 加载已下达/投产/暂停工单（提前开工，用于领料单） ──
        var pickBillWorkOrds = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT DISTINCT WorkOrd
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND Status IS NOT NULL AND UPPER(Status) NOT IN ('C', 'P')
            """,
            new SugarParameter("@TenantId", tenantId));

        var allWorkOrdList = workOrds.Select(w => w.WorkOrd!).ToList();
        var pickBillWorkOrdList = pickBillWorkOrds.ToList();
        allWorkOrdList = allWorkOrdList.Union(pickBillWorkOrdList).Distinct().ToList();

        if (allWorkOrdList.Count == 0)
            return 0;

        // ── 5. 加载每个工单的最新资源检查结果 ──
        var examineResults = await _db.Ado.SqlQueryAsync<ExamineResultRow>(
            """
            SELECT ber.morder_no AS MorderNo, MAX(ber.Id) AS ExamineId, MAX(ber.bangid) AS BangId
            FROM b_examine_result ber
            WHERE ber.tenant_id = @TenantId AND ber.IsDeleted = 0
              AND ber.morder_no IN (@WorkOrds)
            GROUP BY ber.morder_no
            """.Replace("@WorkOrds", string.Join(",", allWorkOrdList.Select((_, i) => $"@Wo{i}"))),
            BuildInParams(allWorkOrdList, tenantId));

        var examineIds = examineResults.Select(e => e.ExamineId).ToList();

        // ── 6. 加载 BOM 子件检查行（erp_cls=3 外购 或 2 委外） ──
        var examines = examineIds.Count == 0
            ? new List<BomChildExamineRow>()
            : await _db.Ado.SqlQueryAsync<BomChildExamineRow>(
                """
                SELECT item_number AS ItemNumber, examine_id AS ExamineId,
                       IFNULL(lack_qty, 0) AS LackQty, IFNULL(needCount, 0) AS NeedCount,
                       tenant_id AS TenantId, company_id AS CompanyId
                FROM b_bom_child_examine
                WHERE tenant_id = @TenantId AND is_use = 1
                  AND (erp_cls = 3 OR erp_cls = 2)
                  AND examine_id IN (@ExamineIds)
                """.Replace("@ExamineIds", string.Join(",", examineIds.Select((_, i) => $"@Ei{i}"))),
                BuildExamineParams(examineIds, tenantId));

        // ── 7. 加载领料单待发料明细（NbrDetail Type=SM, QtyOrd-QtyRec>0, Status!=C） ──
        var pickBills = await _db.Ado.SqlQueryAsync<PickBillRow>(
            """
            SELECT ItemNum, QtyOrd, QtyRec
            FROM NbrDetail
            WHERE tenant_id = @TenantId AND UPPER(Type) = 'SM'
              AND (QtyOrd - QtyRec) > 0 AND UPPER(IFNULL(Status, '')) <> 'C'
              AND WorkOrd IN (@PickWorkOrds)
            """.Replace("@PickWorkOrds", string.Join(",", pickBillWorkOrdList.Select((_, i) => $"@Pw{i}"))),
            BuildInParams(pickBillWorkOrdList, tenantId, "Pw"));

        // ── 8. 加载已发布交货单在途（srm_polist_ds isactive=1, status='P'） ──
        var publishedDsList = await _db.Ado.SqlQueryAsync<PublishedDsRow>(
            """
            SELECT itemnum, IFNULL(SUM(schedqty - sentqty), 0) AS PendingQty
            FROM srm_polist_ds
            WHERE tenant_id = @TenantId AND isactive = 1 AND IFNULL(status, 'N') = 'P'
            GROUP BY itemnum
            """,
            new SugarParameter("@TenantId", tenantId));

        // ── 9. 收集所有涉及物料编号 ──
        var items = examines.Select(e => e.ItemNumber!).Distinct().ToList();
        items.AddRange(pickBills.Select(p => p.ItemNum!));
        items = items.Distinct().ToList();

        if (items.Count == 0)
            return 0;

        // ── 10. 加载物料主数据 ──
        var itemList = await _db.Ado.SqlQueryAsync<ItemMasterRow>(
            """
            SELECT ItemNum, IFNULL(Rev, '') AS Rev, IFNULL(Drawing, '') AS Drawing,
                   IFNULL(InsLT, 0) AS InsLT, IFNULL(MFGMTTR, 0) AS MFGMTTR
            FROM ItemMaster
            WHERE tenant_id = @TenantId AND ItemNum IN (@Items)
            """.Replace("@Items", string.Join(",", items.Select((_, i) => $"@It{i}"))),
            BuildInParams(items, tenantId, "It"));

        // ── 11. 加载库存（InvMaster，按配置的库位范围） ──
        var locationList = new List<string> { "1001", "5007", "5008", "8000", "8001" };
        var locationInClause = string.Join(",", locationList.Select(l => $"'{l}'"));

        var stockRows = await _db.Ado.SqlQueryAsync<StockRow>(
            $"""
            SELECT ItemNum,
                   IFNULL(SUM(IFNULL(AvailStatusQty, 0) + IFNULL(Assay, 0)), 0) AS Qty
            FROM InvMaster
            WHERE ItemNum IN ({string.Join(",", items.Select((_, i) => $"@Sk{i}"))})
              AND IsActive = 1
              AND Location IN ({locationInClause})
              AND tenant_id = @TenantId
            GROUP BY ItemNum
            """,
            BuildInParams(items, tenantId, "Sk"));

        var weekStockQty = stockRows.ToDictionary(s => s.ItemNum!, s => s.Qty, StringComparer.OrdinalIgnoreCase);
        var weekDsQty = publishedDsList.ToDictionary(d => d.ItemNum!, d => d.PendingQty, StringComparer.OrdinalIgnoreCase);

        // ── 12. 按 8 周循环生成需求计划 ──
        var dsRecords = new List<DemandScheduleInsertRow>();
        var remainingPickBills = pickBills.ToList();

        for (var i = 0; i < 8; i++)
        {
            var itemBegin = beginTime.AddDays(i * 7);
            var itemEnd = endTime.AddDays(7 * i - 21 - 28);
            var weekWorkOrds = workOrds.Where(a => a.OrdDate >= itemBegin && a.OrdDate <= itemEnd).ToList();
            var itemQtyList = new List<DemandItemDto>();

            if (weekWorkOrds.Count > 0)
            {
                foreach (var wo in weekWorkOrds)
                {
                    // 已下达工单(R)的缺料已计入领料单，不在此重复
                    if (!string.IsNullOrEmpty(wo.Status) && wo.Status.ToUpper() == "R")
                        continue;

                    var exam = examineResults.FirstOrDefault(e => e.MorderNo == wo.WorkOrd);
                    if (exam is null || exam.ExamineId <= 0)
                        continue;

                    var itemLackList = examines.Where(a => a.ExamineId == exam.ExamineId).ToList();
                    foreach (var lack in itemLackList)
                    {
                        var existing = itemQtyList.FirstOrDefault(x => x.ItemNum == lack.ItemNumber);
                        if (existing != null)
                        {
                            existing.LackQty += lack.LackQty;
                            existing.NeedQty += lack.NeedCount;
                            if (!existing.WorkOrds.Contains(wo.WorkOrd!))
                                existing.WorkOrds += "," + wo.WorkOrd;
                            if (!existing.BangId.Contains(exam.BangId?.ToString() ?? ""))
                                existing.BangId += "," + (exam.BangId?.ToString() ?? "");
                        }
                        else
                        {
                            itemQtyList.Add(new DemandItemDto
                            {
                                ItemNum = lack.ItemNumber!,
                                LackQty = lack.LackQty,
                                NeedQty = lack.NeedCount,
                                WorkOrds = wo.WorkOrd!,
                                BangId = exam.BangId?.ToString() ?? ""
                            });
                        }
                    }
                }

                var requestDate = weekWorkOrds.Min(a => a.OrdDate) ?? DateTime.Today;

                foreach (var d in itemQtyList)
                {
                    var im = itemList.FirstOrDefault(a => a.ItemNum == d.ItemNum);
                    var arrivalDate = requestDate.AddDays(-1).AddDays(-(im?.InsLT ?? 0)).AddDays(-(im?.MFGMTTR ?? 0));

                    decimal mesQty = d.NeedQty;
                    // 第一周：工单需求 + 已下达工单领料单待发料（只算一次）
                    if (i == 0)
                    {
                        var pickQty = remainingPickBills.Where(p => p.ItemNum == d.ItemNum).Sum(q => q.QtyOrd - q.QtyRec);
                        mesQty = d.NeedQty + pickQty;
                        remainingPickBills.RemoveAll(p => p.ItemNum == d.ItemNum);
                    }

                    // 库存扣减
                    decimal stockDeduction = 0;
                    weekStockQty.TryGetValue(d.ItemNum, out var stock);
                    var locQty = stock;
                    if (mesQty >= locQty)
                    {
                        weekStockQty[d.ItemNum] = 0;
                        stockDeduction = locQty;
                    }
                    else
                    {
                        weekStockQty[d.ItemNum] = locQty - mesQty;
                        stockDeduction = mesQty;
                    }

                    // 交货单在途扣减
                    decimal sechedQty = 0;
                    if (weekDsQty.TryGetValue(d.ItemNum, out var dsPending))
                    {
                        sechedQty = dsPending;
                        if (mesQty - stockDeduction - sechedQty >= 0)
                            weekDsQty[d.ItemNum] = 0;
                        else
                            weekDsQty[d.ItemNum] = dsPending - (mesQty - stockDeduction);
                    }

                    var toSechedQty = mesQty - (locQty + sechedQty);
                    dsRecords.Add(new DemandScheduleInsertRow
                    {
                        ItemNum = d.ItemNum,
                        FVersion = im?.Rev ?? "",
                        Drawing = im?.Drawing ?? "",
                        RequestDate = requestDate,
                        ArrivalDate = arrivalDate,
                        ShortQty = d.LackQty,
                        MesQty = mesQty,
                        LocQty = locQty,
                        SechedQty = sechedQty,
                        ToSechedQty = toSechedQty,
                        WoList = d.WorkOrds,
                        BangId = d.BangId
                    });
                }
            }

            // 第一周额外处理：领料单中剩余的物料（不在资源检查缺料中的已下达工单待发料）
            if (i == 0 && remainingPickBills.Count > 0)
            {
                var pickbillItems = remainingPickBills.Select(a => a.ItemNum!).Distinct().ToList();
                var requestDate = itemBegin;
                foreach (var item in pickbillItems)
                {
                    var im = itemList.FirstOrDefault(a => a.ItemNum == item);
                    if (im is null) continue;

                    var arrivalDate = requestDate.AddDays(-1).AddDays(-im.InsLT).AddDays(-im.MFGMTTR);
                    var mesQty = remainingPickBills.Where(p => p.ItemNum == item).Sum(q => q.QtyOrd - q.QtyRec);

                    decimal stockDeduction = 0;
                    weekStockQty.TryGetValue(item, out var stock);
                    var locQty = stock;
                    if (mesQty >= locQty)
                    {
                        weekStockQty[item] = 0;
                        stockDeduction = locQty;
                    }
                    else
                    {
                        weekStockQty[item] = locQty - mesQty;
                        stockDeduction = mesQty;
                    }

                    decimal sechedQty = 0;
                    if (weekDsQty.TryGetValue(item, out var dsPending))
                    {
                        sechedQty = dsPending;
                        if (mesQty - stockDeduction - sechedQty >= 0)
                            weekDsQty[item] = 0;
                        else
                            weekDsQty[item] = dsPending - (mesQty - stockDeduction);
                    }

                    var toSechedQty = mesQty - (locQty + sechedQty);
                    dsRecords.Add(new DemandScheduleInsertRow
                    {
                        ItemNum = item,
                        FVersion = im.Rev,
                        Drawing = im.Drawing,
                        RequestDate = requestDate,
                        ArrivalDate = arrivalDate,
                        ShortQty = 0,
                        MesQty = mesQty,
                        LocQty = locQty,
                        SechedQty = sechedQty,
                        ToSechedQty = toSechedQty,
                        WoList = "",
                        BangId = ""
                    });
                }
            }
        }

        // ── 13. 批量插入 ic_demandschedule ──
        foreach (var ds in dsRecords)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ic_demandschedule
                (Id, itemnum, fversion, drawing, requestdate, arrivaldate,
                 shortqty, mesqty, locqty, sechedqty, tosechedqty,
                 status, remarks, ishistoryversion,
                 create_by_name, create_time, update_by_name, update_time,
                 tenant_id, factory_id, org_id, company_id, IsDeleted,
                 wolist, bangid)
                VALUES
                (@Id, @ItemNum, @FVersion, @Drawing, @RequestDate, @ArrivalDate,
                 @ShortQty, @MesQty, @LocQty, @SechedQty, @ToSechedQty,
                 '', '', 'N',
                 @User, @Now, @User, @Now,
                 @TenantId, @TenantId, @TenantId, 1000, 0,
                 @WoList, @BangId)
                """,
                new SugarParameter("@Id", YitIdHelper.NextId()),
                new SugarParameter("@ItemNum", ds.ItemNum),
                new SugarParameter("@FVersion", ds.FVersion ?? ""),
                new SugarParameter("@Drawing", ds.Drawing ?? ""),
                new SugarParameter("@RequestDate", ds.RequestDate),
                new SugarParameter("@ArrivalDate", ds.ArrivalDate),
                new SugarParameter("@ShortQty", ds.ShortQty),
                new SugarParameter("@MesQty", ds.MesQty),
                new SugarParameter("@LocQty", ds.LocQty),
                new SugarParameter("@SechedQty", ds.SechedQty),
                new SugarParameter("@ToSechedQty", ds.ToSechedQty),
                new SugarParameter("@User", account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WoList", ds.WoList ?? ""),
                new SugarParameter("@BangId", ds.BangId ?? ""));
        }

        return dsRecords.Count;
    }

    private static List<SugarParameter> BuildInParams(List<string> values, long tenantId, string prefix = "Wo")
    {
        var ps = new List<SugarParameter> { new("@TenantId", tenantId) };
        for (var i = 0; i < values.Count; i++)
            ps.Add(new SugarParameter($"@{prefix}{i}", values[i]));
        return ps;
    }

    private static List<SugarParameter> BuildExamineParams(List<long> ids, long tenantId)
    {
        var ps = new List<SugarParameter> { new("@TenantId", tenantId) };
        for (var i = 0; i < ids.Count; i++)
            ps.Add(new SugarParameter($"@Ei{i}", ids[i]));
        return ps;
    }

    // ── DTO for demand schedule generation ──
    private sealed class WorkOrdRow
    {
        public string? WorkOrd { get; set; }
        public DateTime? OrdDate { get; set; }
        public string? Status { get; set; }
    }

    private sealed class ExamineResultRow
    {
        public string? MorderNo { get; set; }
        public long ExamineId { get; set; }
        public long? BangId { get; set; }
    }

    private sealed class BomChildExamineRow
    {
        public string? ItemNumber { get; set; }
        public long? ExamineId { get; set; }
        public decimal LackQty { get; set; }
        public decimal NeedCount { get; set; }
        public long TenantId { get; set; }
        public long CompanyId { get; set; }
    }

    private sealed class PickBillRow
    {
        public string? ItemNum { get; set; }
        public decimal QtyOrd { get; set; }
        public decimal QtyRec { get; set; }
    }

    private sealed class PublishedDsRow
    {
        public string? ItemNum { get; set; }
        public decimal PendingQty { get; set; }
    }

    private sealed class ItemMasterRow
    {
        public string? ItemNum { get; set; }
        public string? Rev { get; set; }
        public string? Drawing { get; set; }
        public int InsLT { get; set; }
        public int MFGMTTR { get; set; }
    }

    private sealed class StockRow
    {
        public string? ItemNum { get; set; }
        public decimal Qty { get; set; }
    }

    private sealed class DemandItemDto
    {
        public string ItemNum { get; set; } = string.Empty;
        public decimal LackQty { get; set; }
        public decimal NeedQty { get; set; }
        public string WorkOrds { get; set; } = string.Empty;
        public string BangId { get; set; } = string.Empty;
    }

    private sealed class DemandScheduleInsertRow
    {
        public string? ItemNum { get; set; }
        public string? FVersion { get; set; }
        public string? Drawing { get; set; }
        public DateTime RequestDate { get; set; }
        public DateTime ArrivalDate { get; set; }
        public decimal ShortQty { get; set; }
        public decimal MesQty { get; set; }
        public decimal LocQty { get; set; }
        public decimal SechedQty { get; set; }
        public decimal ToSechedQty { get; set; }
        public string? WoList { get; set; }
        public string? BangId { get; set; }
    }

    private static string BuildMessage(ProcurementPipelineResult r)
    {
        var parts = new List<string>();
        if (r.IndividualPrCount > 0) parts.Add($"独立 PR {r.IndividualPrCount}");
        if (r.PrCreatedCount > 0) parts.Add($"合并 PR {r.PrCreatedCount}");
        if (r.PrUpdatedCount > 0) parts.Add($"更新 PR {r.PrUpdatedCount}");
        if (r.PrDeletedCount > 0) parts.Add($"删除 PR {r.PrDeletedCount}");
        if (r.PoCreatedCount > 0) parts.Add($"转 DO/PO {r.PoCreatedCount}");

        if (r.DemandScheduleCount > 0) parts.Add($"交货计划 {r.DemandScheduleCount}");
        return parts.Count > 0 ? string.Join("，", parts) : "无待处理采购申请";
    }
}

public sealed class ProcurementPipelineResult
{
    public int ShortageItemCount { get; set; }
    public int IndividualPrCount { get; set; }
    public int PrCreatedCount { get; set; }
    public int PrUpdatedCount { get; set; }
    public int PrDeletedCount { get; set; }
    public int PrMergeReducedCount { get; set; }
    public int HistoricalPrMergedGroups { get; set; }
    public int PendingPrCount { get; set; }
    public int PoCreatedCount { get; set; }
    public int PoTransferredPrCount { get; set; }
    public int PoOccupyRehangedCount { get; set; }
    public int QadTrackingCount { get; set; }
    public int QadTrackingSkippedCount { get; set; }
    public int DemandScheduleCount { get; set; }
    public List<string> CreatedPoNumbers { get; set; } = new();
    public List<string> Warnings { get; set; } = new();
    public bool S4MdpRefreshed { get; set; }
    public string? S4MdpBatchId { get; set; }
    public S4ReadPathConsistencyResult? ReadPathConsistency { get; set; }
    public string Message { get; set; } = string.Empty;
}