using Admin.NET.Plugin.AiDOP.Rule;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 物料交货计划服务
/// </summary>
[ApiDescriptionSettings(Order = 306, Description = "物料交货计划")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class DeliveryScheduleService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<PolistDeliverySchedule> _rep;
    private readonly UserManager _userManager;
    private readonly NumberRuleService _numberRuleService;
    private readonly PurchaseRequestMergeService _purchaseRequestMergeService;
    private readonly PurchaseOrderTransferService _purchaseOrderTransferService;
    private readonly PurchaseRequestExternalPushService _purchaseRequestExternalPushService;
    private readonly S3OperationLogService _operationLogService;
    private readonly RuleConfigService _ruleConfigService;

    public DeliveryScheduleService(
        ISqlSugarClient db,
        SqlSugarRepository<PolistDeliverySchedule> rep,
        UserManager userManager,
        NumberRuleService numberRuleService,
        PurchaseRequestMergeService purchaseRequestMergeService,
        PurchaseOrderTransferService purchaseOrderTransferService,
        PurchaseRequestExternalPushService purchaseRequestExternalPushService,
        S3OperationLogService operationLogService,
        RuleConfigService ruleConfigService)
    {
        _db = db;
        _rep = rep;
        _userManager = userManager;
        _numberRuleService = numberRuleService;
        _purchaseRequestMergeService = purchaseRequestMergeService;
        _purchaseOrderTransferService = purchaseOrderTransferService;
        _purchaseRequestExternalPushService = purchaseRequestExternalPushService;
        _operationLogService = operationLogService;
        _ruleConfigService = ruleConfigService;
    }

    [DisplayName("物料交货计划列表")]
    [HttpGet("delivery-schedule/list")]
    public async Task<object> GetList([FromQuery] DeliveryScheduleListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        //if (await ShouldUseStdAsync())
        //{
            //return await GetStdListAsync(input, page, pageSize, offset);
        //}

        var pars = new List<SugarParameter>();
        var where = new List<string> { "ss.isactive = 1" };
        if (_userManager.TenantId > 0)
        {
            where.Add("ss.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PoNumber))
        {
            where.Add("ss.ponumber LIKE @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", $"%{input.PoNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            where.Add("ss.dsnum LIKE @DsNum");
            pars.Add(new SugarParameter("@DsNum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("ss.itemnum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("(ss.suppliercode LIKE @Supplier OR ss.supplier LIKE @Supplier)");
            pars.Add(new SugarParameter("@Supplier", $"%{input.Supplier.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            where.Add("ss.status = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }

        var orderBy = BuildListOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM srm_polist_ds ss
            LEFT JOIN ItemMaster im ON ss.itemnum = im.ItemNum
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<DeliveryScheduleListRow>(
            $"""
            SELECT
                ss.Id AS Id,
                ss.domain AS Domain,
                ss.icdsid AS IcdsId,
                ss.dsnum AS DsNum,
                ss.status AS Status,
                ss.itemnum AS ItemNum,
                im.Descr AS Descr,
                ss.um AS Um,
                ss.purgroup AS PurGroup,
                ss.suppliercode AS SupplierCode,
                ss.supplier AS Supplier,
                ss.submitdate AS SubmitDate,
                ss.requestdate AS RequestDate,
                ss.needdate AS NeedDate,
                ss.ponumber AS PoNumber,
                ss.poline AS PoLine,
                ss.schedqty AS SchedQty,
                ss.lastsentdate AS LastSentDate,
                ss.lastsentqty AS LastSentQty,
                ss.sentqty AS SentQty,
                ss.restqty AS RestQty
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private async Task<bool> ShouldUseStdAsync()
    {
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM mdp_std_delivery_schedule LIMIT 1") > 0;
    }

    private async Task<object> GetStdListAsync(DeliveryScheduleListInput input, int page, int pageSize, int offset)
    {
        var pars = new List<SugarParameter>();
        var where = new List<string> { "1=1" };
        if (_userManager.TenantId > 0)
        {
            where.Add("ss.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PoNumber))
        {
            where.Add("ss.po_no LIKE @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", $"%{input.PoNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            where.Add("ss.delivery_plan_no LIKE @DsNum");
            pars.Add(new SugarParameter("@DsNum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("ss.item_code LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("(ss.supplier_code LIKE @Supplier OR ss.supplier_name LIKE @Supplier)");
            pars.Add(new SugarParameter("@Supplier", $"%{input.Supplier.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            where.Add("ss.status = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }

        var orderBy = BuildStdListOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"FROM mdp_std_delivery_schedule ss LEFT JOIN mdp_std_item im ON ss.tenant_id=im.tenant_id AND ss.item_code=im.item_code WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<DeliveryScheduleListRow>(
            $"""
            SELECT
                ss.id AS Id,
                NULL AS Domain,
                NULL AS IcdsId,
                ss.delivery_plan_no AS DsNum,
                ss.status AS Status,
                ss.item_code AS ItemNum,
                im.item_name AS Descr,
                NULL AS Um,
                NULL AS PurGroup,
                ss.supplier_code AS SupplierCode,
                ss.supplier_name AS Supplier,
                ss.submit_date AS SubmitDate,
                ss.request_date AS RequestDate,
                ss.need_date AS NeedDate,
                ss.po_no AS PoNumber,
                CAST(ss.po_line AS SIGNED) AS PoLine,
                ss.schedule_qty AS SchedQty,
                ss.last_sent_date AS LastSentDate,
                ss.sent_qty AS LastSentQty,
                ss.sent_qty AS SentQty,
                ss.rest_qty AS RestQty
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private static string BuildStdListOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "dsnum" => $"ss.delivery_plan_no {dir}",
            "status" => $"ss.status {dir}",
            "itemnum" => $"ss.item_code {dir}",
            "suppliercode" => $"ss.supplier_code {dir}",
            "supplier" => $"ss.supplier_name {dir}",
            "submitdate" => $"ss.submit_date {dir}",
            "requestdate" => $"ss.request_date {dir}",
            "needdate" => $"ss.need_date {dir}",
            "ponumber" => $"ss.po_no {dir}",
            "poline" => $"ss.po_line {dir}",
            "schedqty" => $"ss.schedule_qty {dir}",
            "sentqty" => $"ss.sent_qty {dir}",
            "restqty" => $"ss.rest_qty {dir}",
            _ => "ss.need_date DESC, ss.delivery_plan_no DESC"
        };
    }

    [DisplayName("生成交货单")]
    [HttpPost("delivery-schedule/generate")]
    public async Task<object> Generate()
    {
        try
        {
            var result = await GenerateDeliverySchedulesFromDemandAsync();
            await _operationLogService.WriteAsync(new S3OperationLogDto
            {
                Action = "S3_DELIVERY_GENERATE",
                DisplayTitle = "S3 生成交货单",
                Message = result.Message,
                Result = result,
                Success = true
            });
            return result;
        }
        catch (Exception ex)
        {
            await _operationLogService.WriteAsync(new S3OperationLogDto
            {
                Action = "S3_DELIVERY_GENERATE",
                DisplayTitle = "S3 生成交货单失败",
                Message = ex.Message,
                Exception = ex.ToString(),
                Success = false
            });
            throw;
        }
    }

    [DisplayName("批量添加交货单")]
    [HttpPost("delivery-schedule/batch-generate")]
    public async Task<object> BatchGenerate([FromBody] DeliveryScheduleGenerateInput input)
    {
        if (string.IsNullOrWhiteSpace(input.PoNumber))
            throw Oops.Oh("采购单号不能为空");

        return await GenerateDeliverySchedulesFromPurchaseOrdersAsync(input.PoNumber.Trim());
    }

    [DisplayName("发布交货单")]
    [HttpPost("delivery-schedule/publish")]
    public async Task<object> Publish([FromBody] DeliveryScheduleBatchIdsInput input)
    {
        var ids = ParseIds(input.Ids);
        if (!ids.Any()) throw Oops.Oh("请勾选要发布的数据");

        var account = _userManager.Account ?? "system";
        var affected = await _db.Updateable<PolistDeliverySchedule>()
            .SetColumns(x => new PolistDeliverySchedule
            {
                Status = "P",
                SubmitDate = DateTime.Now,
                UpdateUser = account,
                UpdateTime = DateTime.Now
            })
            .Where(x => ids.Contains(x.Id) && x.IsActive == 1 && x.Status == "N")
            .ExecuteCommandAsync();
        return new { affected, message = affected > 0 ? "发布成功" : "无可发布数据" };
    }

    [DisplayName("取消发布交货单")]
    [HttpPost("delivery-schedule/unpublish/{id:long}")]
    public async Task<object> UnPublish(long id)
    {
        var row = await _rep.GetFirstAsync(x => x.Id == id && x.IsActive == 1) ?? throw Oops.Oh("交货单不存在");
        row.Status = "N";
        row.UpdateUser = _userManager.Account;
        row.UpdateTime = DateTime.Now;
        await _rep.UpdateAsync(row);
        return new { message = "取消发布成功" };
    }

    [DisplayName("作废交货单")]
    [HttpPost("delivery-schedule/cancel/{id:long}")]
    public async Task<object> Cancel(long id)
    {
        var row = await _rep.GetFirstAsync(x => x.Id == id && x.IsActive == 1) ?? throw Oops.Oh("交货单不存在");
        row.Status = "C";
        row.UpdateUser = _userManager.Account;
        row.UpdateTime = DateTime.Now;
        await _rep.UpdateAsync(row);
        return new { message = "作废成功" };
    }

    [DisplayName("选择采购单分页")]
    [HttpGet("delivery-schedule/purchase-order/page")]
    public async Task<object> GetPurchaseOrderPage([FromQuery] PurchaseOrderSelectInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(m.Status,'') <> 'C'",
            "IFNULL(d.Status,'') <> 'C'",
            "(IFNULL(ds.dsnum, '') = '' OR (ds.status = 'C' AND d.QtyOrded - d.RctQty > 0))"
        };
        if (!string.IsNullOrWhiteSpace(input.PurOrd))
        {
            where.Add("m.PurOrd LIKE @PurOrd");
            pars.Add(new SugarParameter("@PurOrd", $"%{input.PurOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("d.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supp))
        {
            where.Add("(m.Supp LIKE @Supp OR s.Name LIKE @Supp)");
            pars.Add(new SugarParameter("@Supp", $"%{input.Supp.Trim()}%"));
        }

        var orderBy = BuildPurchaseOrderOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM PurOrdMaster m
            INNER JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN srm_polist_ds ds ON d.PurOrd = ds.ponumber AND d.Line = ds.poline AND ds.isactive = 1
            LEFT JOIN ConsigneeAddressMaster s ON m.Supp = s.Address AND s.Typed = 'Supp'
            WHERE {string.Join(" AND ", where)}
            """;
        var distinctSql = $"SELECT DISTINCT m.PurOrd, d.Line, d.ItemNum {fromSql}";

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({distinctSql}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseOrderSelectRow>(
            $"""
            SELECT DISTINCT
                m.PurOrd AS PurOrd,
                d.Line AS Line,
                d.ItemNum AS ItemNum,
                d.UM AS Um,
                m.Supp AS Supp,
                s.Name AS Name,
                d.DueDate AS DueDate,
                d.QtyOrded AS QtyOrded,
                m.Buyer AS Buyer
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private async Task<DeliveryScheduleGenerateResult> GenerateDeliverySchedulesFromDemandAsync()
    {
        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;
        var optTime = now;
        var ruleResult = await _ruleConfigService.ResolveS3DeliveryGenerateOptionsAsync(_userManager.TenantId, null);
        if (!ruleResult.Success)
        {
            throw Oops.Oh($"S3交货单生成规则配置无效：{string.Join("；", ruleResult.Errors)}");
        }
        var rules = ruleResult.Options;
        var ruleSnapshot = _ruleConfigService.CreateSnapshot("S3", "DELIVERY_GENERATE", _userManager.TenantId, null, ruleResult, now);
        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(a.status,'') = 'P'",
            "IFNULL(a.tosechedqty,0) > 0",
            "(IFNULL(a.ishistoryversion,'') = '' OR a.ishistoryversion = 'N')",
            "IFNULL(a.IsDeleted,0)=0"
        };
        if (_userManager.TenantId > 0)
        {
            where.Add("a.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }

        var demands = await _db.Ado.SqlQueryAsync<DemandScheduleCandidateRow>(
            $"""
            SELECT
                a.Id AS IcdsId,
                IFNULL(TRIM(a.itemnum),'') AS ItemNum,
                a.requestdate AS RequestDate,
                a.arrivaldate AS ArrivalDate,
                IFNULL(a.tosechedqty,0) AS DemandQty,
                a.tenant_id AS TenantId,
                a.factory_id AS FactoryId,
                a.company_id AS CompanyId,
                IFNULL(TRIM(im.PurMfg),'') AS PurMfg,
                IFNULL(TRIM(im.UM),'') AS Um,
                IFNULL(TRIM(im.Descr),'') AS ItemName,
                ic.Id AS IcItemId
            FROM ic_demandschedule a
            LEFT JOIN ItemMaster im ON a.itemnum = im.ItemNum
            LEFT JOIN ic_item ic ON a.itemnum = ic.number AND (a.tenant_id = ic.tenant_id OR a.tenant_id IS NULL)
            WHERE {string.Join(" AND ", where)}
            ORDER BY a.arrivaldate, a.requestdate, a.Id
            """,
            pars);

        if (demands.Count == 0)
        {
            return new DeliveryScheduleGenerateResult
            {
                DemandCount = 0,
                CandidateCount = 0,
                CreatedCount = 0,
                RuleSnapshot = ruleSnapshot,
                Message = "无已发布且待生成交货单的需求计划"
            };
        }

        var demandIds = demands.Select(x => x.IcdsId).Distinct().ToList();
        var existingUsages = await QueryExistingScheduleUsagesAsync(demandIds);
        var itemNums = demands.Select(x => x.ItemNum).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
        var sourceLists = await QuerySourceListsAsync(itemNums);
        var supplierCodes = sourceLists.Select(x => x.SupplierCode).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
        var poLines = supplierCodes.Count == 0
            ? new List<PurchaseOrderAvailableRow>()
            : await QueryAvailablePurchaseOrderLinesAsync(itemNums, supplierCodes, rules);

        var exceptions = new List<DeliveryExceptionMaster>();
        var schedules = new List<PolistDeliverySchedule>();
        var purchaseRequests = new List<PurchaseRequestMain>();
        var prMergeReducedCount = 0;
        var purchaseOrderTransferResult = new PurchaseOrderTransferResult();
        var externalPushResult = new PurchaseRequestExternalPushResult();
        var allocatedByPoLine = new Dictionary<string, decimal>();
        var skippedCount = 0;

        foreach (var demand in demands)
        {
            if (string.IsNullOrWhiteSpace(demand.ItemNum))
            {
                skippedCount++;
                AddDeliveryException(exceptions, demand, optTime, "需求计划缺少物料编码，无法生成交货单", demand.DemandQty);
                continue;
            }
            if (!string.Equals(demand.PurMfg, "P", StringComparison.OrdinalIgnoreCase))
            {
                skippedCount++;
                AddDeliveryException(exceptions, demand, optTime, $"物料[{demand.ItemNum}]不是采购件，不需要生成交货单", demand.DemandQty);
                continue;
            }

            var usedQty = existingUsages.TryGetValue(demand.IcdsId, out var used) ? used : 0m;
            var demandRestQty = demand.DemandQty - usedQty;
            if (demandRestQty <= 0)
            {
                skippedCount++;
                continue;
            }

            var itemSources = sourceLists
                .Where(x => string.Equals(x.ItemNum, demand.ItemNum, StringComparison.OrdinalIgnoreCase))
                .OrderByDescending(x => x.QuotaRate)
                .ToList();
            if (itemSources.Count == 0)
            {
                skippedCount++;
                AddDeliveryException(exceptions, demand, optTime, $"物料交货计划[{demand.ItemNum}:{FormatDate(demand.RequestDate)}]没有维护货源清单，无法转交货单", demandRestQty);
                continue;
            }

            var quotaTotal = itemSources.Sum(x => x.QuotaRate);
            if (Math.Abs(quotaTotal - rules.QuotaRequiredTotal) > rules.QuotaTolerance)
            {
                skippedCount++;
                AddDeliveryException(exceptions, demand, optTime, $"物料交货计划[{demand.ItemNum}:{FormatDate(demand.RequestDate)}]的货源清单供应商配额之和不为{rules.QuotaRequiredTotal:0.####}%，无法转交货单", demandRestQty);
                continue;
            }

            var allocatedForDemand = 0m;
            for (var i = 0; i < itemSources.Count; i++)
            {
                var source = itemSources[i];
                var remainingDemand = demandRestQty - allocatedForDemand;
                if (remainingDemand <= 0) break;

                var supplierNeedQty = i == itemSources.Count - 1
                    ? remainingDemand
                    : Math.Min(remainingDemand, Math.Ceiling(demandRestQty * source.QuotaRate / 100m));
                if (supplierNeedQty <= 0) continue;

                var supplierAllocated = AllocateDemandToPurchaseLines(
                    demand,
                    source,
                    poLines,
                    allocatedByPoLine,
                    supplierNeedQty,
                    remainingDemand,
                    schedules,
                    now,
                    account,
                    rules);
                allocatedForDemand += supplierAllocated;

                var shortageQty = supplierNeedQty - supplierAllocated;
                if (shortageQty > 0)
                {
                    var prCreated = rules.ShortageCreatePr && TryAddPurchaseRequest(purchaseRequests, demand, source, shortageQty, now, account, rules);
                    AddDeliveryException(
                        exceptions,
                        demand,
                        optTime,
                        prCreated
                            ? $"物料交货计划[{demand.ItemNum}:{FormatDate(demand.RequestDate)}]对应供应商[{source.SupplierName}]采购订单不足，已生成采购申请，后续待合并/转单/推送"
                            : rules.ShortageCreatePr
                                ? $"物料交货计划[{demand.ItemNum}:{FormatDate(demand.RequestDate)}]对应供应商[{source.SupplierName}]采购订单不足，需补充 {shortageQty:0.####}；因物料或供应商主键缺失，未自动生成PR"
                                : $"物料交货计划[{demand.ItemNum}:{FormatDate(demand.RequestDate)}]对应供应商[{source.SupplierName}]采购订单不足，需补充 {shortageQty:0.####}；规则配置关闭自动生成PR",
                        shortageQty);
                }
            }
        }

        if (schedules.Count == 0 && exceptions.Count == 0)
        {
            return new DeliveryScheduleGenerateResult
            {
                DemandCount = demands.Count,
                CandidateCount = 0,
                CreatedCount = 0,
                SkippedCount = skippedCount,
                RuleSnapshot = ruleSnapshot,
                Message = "没有可生成交货单的需求计划"
            };
        }

        try
        {
            _db.Ado.BeginTran();
            if (purchaseRequests.Count > 0 && rules.EnablePrMerge)
            {
                var mergeResult = _purchaseRequestMergeService.MergeGeneratedRequests(purchaseRequests);
                purchaseRequests = mergeResult.Requests;
                prMergeReducedCount = mergeResult.ReducedCount;
            }
            if (purchaseRequests.Count > 0)
            {
                await AssignPurchaseRequestNumbersAsync(purchaseRequests, account);
            }
            if (rules.EnableRequireGoodsToPo && purchaseRequests.Any(x => x.IsRequireGoods == 1))
            {
                purchaseOrderTransferResult = await _purchaseOrderTransferService.TransferGeneratedRequireGoodsAsync(purchaseRequests, account);
            }
            if (rules.EnableExternalPushTracking && purchaseRequests.Any(x => x.IsRequireGoods == 0))
            {
                externalPushResult = await _purchaseRequestExternalPushService.CreateQadTrackingForGeneratedRequestsAsync(purchaseRequests, account);
            }
            if (schedules.Count > 0)
            {
                var dsNumbersByKey = new Dictionary<string, string>();
                foreach (var group in schedules.GroupBy(x => x.Domain ?? string.Empty))
                {
                    var groupRows = group.ToList();
                    var dsNumbers = await _numberRuleService.NextBatchInCurrentTransactionAsync("M8", group.Key, groupRows.Count, account);
                    if (dsNumbers.Count < groupRows.Count || dsNumbers.Any(string.IsNullOrWhiteSpace))
                        throw Oops.Oh($"当前交货计划号生成失败，请检查交货计划编号规则维护。Domain={group.Key}");

                    for (var index = 0; index < groupRows.Count; index++)
                    {
                        dsNumbersByKey[BuildScheduleTempKey(groupRows[index])] = dsNumbers[index].Trim();
                    }
                }

                foreach (var schedule in schedules)
                {
                    schedule.DsNum = dsNumbersByKey[BuildScheduleTempKey(schedule)];
                }

                await _db.Insertable(schedules).ExecuteCommandAsync();
                await UpsertDeliveryScheduleMdpAsync(schedules, now);
            }
            if (purchaseRequests.Count > 0)
            {
                await InsertPurchaseRequestsAsync(purchaseRequests);
            }
            if (exceptions.Count > 0)
            {
                await _db.Insertable(exceptions).ExecuteCommandAsync();
            }
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }

        return new DeliveryScheduleGenerateResult
        {
            DemandCount = demands.Count,
            CandidateCount = schedules.Count,
            CreatedCount = schedules.Count,
            ExceptionCount = exceptions.Count,
            PurchaseRequestCount = purchaseRequests.Count,
            PurchaseRequestMergeReducedCount = prMergeReducedCount,
            PurchaseOrderCount = purchaseOrderTransferResult.CreatedOrderCount,
            PurchaseOrderLineCount = purchaseOrderTransferResult.TransferredPrCount,
            QadTrackingCount = externalPushResult.TrackingCount,
            SkippedCount = skippedCount,
            DsNums = schedules.Select(x => x.DsNum).Where(x => !string.IsNullOrWhiteSpace(x)).Take(20).ToList(),
            RuleSnapshot = ruleSnapshot,
            Message = schedules.Count > 0
                ? $"生成交货单成功，共生成 {schedules.Count} 条；自动生成PR {purchaseRequests.Count} 条；合并减少 {prMergeReducedCount} 条；生成DO/PO {purchaseOrderTransferResult.CreatedOrderCount} 单；写QadTracking {externalPushResult.TrackingCount} 条；异常 {exceptions.Count} 条"
                : $"未生成交货单，自动生成PR {purchaseRequests.Count} 条，合并减少 {prMergeReducedCount} 条，生成DO/PO {purchaseOrderTransferResult.CreatedOrderCount} 单，写QadTracking {externalPushResult.TrackingCount} 条，已记录异常 {exceptions.Count} 条"
        };
    }

    private async Task<DeliveryScheduleGenerateResult> GenerateDeliverySchedulesFromPurchaseOrdersAsync(string poNumber)
    {
        var account = _userManager.Account ?? "system";
        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(m.Status,'') <> 'C'",
            "IFNULL(d.Status,'') <> 'C'",
            "(IFNULL(d.QtyOrded,0) - IFNULL(d.RctQty,0)) > 0",
            """
            NOT EXISTS (
                SELECT 1
                FROM srm_polist_ds ds
                WHERE ds.ponumber = d.PurOrd
                  AND ds.poline = d.Line
                  AND ds.isactive = 1
                  AND IFNULL(ds.status,'') <> 'C'
            )
            """
        };
        if (_userManager.TenantId > 0)
        {
            where.Add("m.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(poNumber))
        {
            where.Add("m.PurOrd = @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", poNumber.Trim()));
        }

        var candidates = await _db.Ado.SqlQueryAsync<DeliveryScheduleCandidateRow>(
            $"""
            SELECT
                IFNULL(TRIM(m.Domain),'') AS Domain,
                d.RecID AS IcdsId,
                IFNULL(TRIM(d.PurOrd),'') AS PoNumber,
                IFNULL(d.Line,0) AS PoLine,
                IFNULL(TRIM(d.ItemNum),'') AS ItemNum,
                IFNULL(TRIM(d.UM),'') AS Um,
                IFNULL(TRIM(m.Buyer),'') AS PurGroup,
                IFNULL(TRIM(m.Supp),'') AS SupplierCode,
                IFNULL(TRIM(s.Name),'') AS Supplier,
                d.DueDate AS NeedDate,
                (IFNULL(d.QtyOrded,0) - IFNULL(d.RctQty,0)) AS RestQty,
                m.tenant_id AS TenantId
            FROM PurOrdMaster m
            INNER JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN ConsigneeAddressMaster s ON m.Supp = s.Address AND s.Typed = 'Supp'
            WHERE {string.Join(" AND ", where)}
            ORDER BY m.PurOrd, d.Line
            """,
            pars);
        if (candidates.Count == 0)
        {
            return new DeliveryScheduleGenerateResult
            {
                CandidateCount = 0,
                CreatedCount = 0,
                Message = string.IsNullOrWhiteSpace(poNumber)
                    ? "无可生成交货单的采购行"
                    : "当前采购单无可生成交货单的采购行"
            };
        }

        try
        {
            _db.Ado.BeginTran();
            var dsNumbersByKey = new Dictionary<string, string>();
            foreach (var group in candidates.GroupBy(x => x.Domain ?? string.Empty))
            {
                var groupRows = group.ToList();
                var dsNumbers = await _numberRuleService.NextBatchInCurrentTransactionAsync("M8", group.Key, groupRows.Count, account);
                if (dsNumbers.Count < groupRows.Count || dsNumbers.Any(string.IsNullOrWhiteSpace))
                    throw Oops.Oh($"当前交货计划号生成失败，请检查交货计划编号规则维护。Domain={group.Key}");

                for (var index = 0; index < groupRows.Count; index++)
                {
                    dsNumbersByKey[BuildCandidateKey(groupRows[index])] = dsNumbers[index].Trim();
                }
            }

            var now = DateTime.Now;
            var schedules = candidates.Select(row => new PolistDeliverySchedule
            {
                Id = YitIdHelper.NextId(),
                Domain = row.Domain ?? string.Empty,
                IcdsId = row.IcdsId,
                DsNum = dsNumbersByKey[BuildCandidateKey(row)],
                Status = "N",
                ItemNum = row.ItemNum ?? string.Empty,
                Um = row.Um ?? string.Empty,
                PurGroup = row.PurGroup ?? string.Empty,
                SupplierCode = row.SupplierCode ?? string.Empty,
                Supplier = row.Supplier ?? string.Empty,
                RequestDate = now,
                NeedDate = row.NeedDate,
                PoNumber = row.PoNumber ?? string.Empty,
                PoLine = row.PoLine,
                SchedQty = row.RestQty,
                LastSentQty = 0,
                SentQty = 0,
                RestQty = row.RestQty,
                CreateUser = account,
                UpdateUser = account,
                CreateTime = now,
                UpdateTime = now,
                Remarks = "由系统按采购行自动生成",
                IsActive = 1,
                ReturnQty = 0,
                TenantId = _userManager.TenantId > 0 ? _userManager.TenantId : row.TenantId
            }).ToList();

            var createdCount = await _db.Insertable(schedules).ExecuteCommandAsync();
            await UpsertDeliveryScheduleMdpAsync(schedules, now);
            _db.Ado.CommitTran();
            return new DeliveryScheduleGenerateResult
            {
                CandidateCount = candidates.Count,
                CreatedCount = createdCount,
                DsNums = schedules.Select(x => x.DsNum).Take(20).ToList(),
                Message = createdCount > 0
                    ? $"生成交货单成功，共生成 {createdCount} 条"
                    : "未生成交货单，请检查采购行是否已存在有效交货计划"
            };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    private async Task UpsertDeliveryScheduleMdpAsync(List<PolistDeliverySchedule> schedules, DateTime now)
    {
        if (schedules.Count == 0) return;

        var batchId = $"S3_DELIVERY_{now:yyyyMMddHHmmss}";
        foreach (var schedule in schedules)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO mdp_std_delivery_schedule
                (tenant_id, source_system, delivery_plan_no, po_no, po_line, item_code, supplier_code, supplier_name, schedule_qty, sent_qty, rest_qty, return_qty, request_date, need_date, submit_date, last_sent_date, status, source_biz_key, sync_batch_id, sync_time)
                VALUES
                (@TenantId, 'AIDOP', @DsNum, @PoNumber, @PoLine, @ItemNum, @SupplierCode, @Supplier, @SchedQty, @SentQty, @RestQty, @ReturnQty, @RequestDate, @NeedDate, @SubmitDate, @LastSentDate, @Status, @DsNum, @BatchId, @Now)
                ON DUPLICATE KEY UPDATE
                    po_no=VALUES(po_no),
                    po_line=VALUES(po_line),
                    item_code=VALUES(item_code),
                    supplier_code=VALUES(supplier_code),
                    supplier_name=VALUES(supplier_name),
                    schedule_qty=VALUES(schedule_qty),
                    sent_qty=VALUES(sent_qty),
                    rest_qty=VALUES(rest_qty),
                    return_qty=VALUES(return_qty),
                    request_date=VALUES(request_date),
                    need_date=VALUES(need_date),
                    submit_date=VALUES(submit_date),
                    last_sent_date=VALUES(last_sent_date),
                    status=VALUES(status),
                    sync_batch_id=VALUES(sync_batch_id),
                    sync_time=VALUES(sync_time),
                    update_time=CURRENT_TIMESTAMP
                """,
                new SugarParameter("@TenantId", schedule.TenantId ?? 0),
                new SugarParameter("@DsNum", schedule.DsNum),
                new SugarParameter("@PoNumber", schedule.PoNumber),
                new SugarParameter("@PoLine", schedule.PoLine.ToString()),
                new SugarParameter("@ItemNum", schedule.ItemNum),
                new SugarParameter("@SupplierCode", schedule.SupplierCode),
                new SugarParameter("@Supplier", schedule.Supplier),
                new SugarParameter("@SchedQty", schedule.SchedQty),
                new SugarParameter("@SentQty", schedule.SentQty),
                new SugarParameter("@RestQty", schedule.RestQty),
                new SugarParameter("@ReturnQty", schedule.ReturnQty),
                new SugarParameter("@RequestDate", schedule.RequestDate),
                new SugarParameter("@NeedDate", schedule.NeedDate),
                new SugarParameter("@SubmitDate", schedule.SubmitDate),
                new SugarParameter("@LastSentDate", schedule.LastSentDate),
                new SugarParameter("@Status", schedule.Status),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@Now", now));

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO dwd_supplier_delivery
                (tenant_id, stat_date, po_no, po_line, po_type, supplier_code, supplier_name, item_code, item_name, order_qty, schedule_qty, delivery_qty, receipt_qty, return_qty, remaining_qty, due_date, need_date, last_delivery_date, delivery_status, source_system, sync_batch_id, calc_time)
                SELECT
                    @TenantId,
                    @StatDate,
                    IFNULL(m.PurOrd, @PoNumber),
                    CAST(IFNULL(d.Line, @PoLineInt) AS CHAR),
                    IFNULL(m.Potype, ''),
                    IFNULL(m.Supp, @SupplierCode),
                    @Supplier,
                    IFNULL(d.ItemNum, @ItemNum),
                    IFNULL(i.Descr, ''),
                    IFNULL(d.QtyOrded, @SchedQty),
                    @SchedQty,
                    @SentQty,
                    IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
                    @ReturnQty,
                    @RestQty,
                    d.DueDate,
                    @NeedDate,
                    @LastSentDate,
                    CASE
                        WHEN IFNULL(@Status, '') = 'C' THEN 'CANCELLED'
                        WHEN IFNULL(@RestQty, 0) <= 0 THEN 'CLOSED'
                        WHEN IFNULL(@SentQty, 0) > 0 THEN 'PARTIAL'
                        ELSE 'OPEN'
                    END,
                    'AIDOP',
                    @BatchId,
                    @Now
                FROM PurOrdMaster m
                JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
                LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
                WHERE m.PurOrd = @PoNumber AND d.Line = @PoLineInt
                LIMIT 1
                ON DUPLICATE KEY UPDATE
                    supplier_code=VALUES(supplier_code),
                    supplier_name=VALUES(supplier_name),
                    item_code=VALUES(item_code),
                    item_name=VALUES(item_name),
                    order_qty=VALUES(order_qty),
                    schedule_qty=VALUES(schedule_qty),
                    delivery_qty=VALUES(delivery_qty),
                    receipt_qty=VALUES(receipt_qty),
                    return_qty=VALUES(return_qty),
                    remaining_qty=VALUES(remaining_qty),
                    due_date=VALUES(due_date),
                    need_date=VALUES(need_date),
                    last_delivery_date=VALUES(last_delivery_date),
                    delivery_status=VALUES(delivery_status),
                    sync_batch_id=VALUES(sync_batch_id),
                    calc_time=VALUES(calc_time),
                    update_time=CURRENT_TIMESTAMP
                """,
                new SugarParameter("@TenantId", schedule.TenantId ?? 0),
                new SugarParameter("@StatDate", now.Date),
                new SugarParameter("@PoNumber", schedule.PoNumber),
                new SugarParameter("@PoLineInt", schedule.PoLine),
                new SugarParameter("@SupplierCode", schedule.SupplierCode),
                new SugarParameter("@Supplier", schedule.Supplier),
                new SugarParameter("@ItemNum", schedule.ItemNum),
                new SugarParameter("@SchedQty", schedule.SchedQty),
                new SugarParameter("@SentQty", schedule.SentQty),
                new SugarParameter("@ReturnQty", schedule.ReturnQty),
                new SugarParameter("@RestQty", schedule.RestQty),
                new SugarParameter("@NeedDate", schedule.NeedDate),
                new SugarParameter("@LastSentDate", schedule.LastSentDate),
                new SugarParameter("@Status", schedule.Status),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@Now", now));
        }
    }

    private async Task<Dictionary<long, decimal>> QueryExistingScheduleUsagesAsync(List<long> demandIds)
    {
        if (demandIds.Count == 0) return new Dictionary<long, decimal>();

        var pars = new List<SugarParameter>();
        var inClause = BuildInClause(demandIds.Select(x => x.ToString()).ToList(), "DemandId", pars);
        var rows = await _db.Ado.SqlQueryAsync<ExistingScheduleUsageRow>(
            $"""
            SELECT
                icdsid AS IcdsId,
                SUM(
                    CASE
                        WHEN IFNULL(status,'') = 'C' THEN IFNULL(sentqty,0)
                        WHEN IFNULL(schedqty,0) >= IFNULL(sentqty,0) THEN IFNULL(schedqty,0)
                        ELSE IFNULL(sentqty,0)
                    END
                ) AS UsedQty
            FROM srm_polist_ds
            WHERE isactive = 1 AND icdsid IN ({inClause})
            GROUP BY icdsid
            """,
            pars);

        return rows.ToDictionary(x => x.IcdsId, x => x.UsedQty);
    }

    private async Task<List<SourceListRow>> QuerySourceListsAsync(List<string> itemNums)
    {
        if (itemNums.Count == 0) return new List<SourceListRow>();

        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(sp.IsDeleted,0)=0",
            "IFNULL(sp.supplier_number,'') <> ''",
            "IFNULL(sp.quota_rate,0) > 0",
            """
            (
              IFNULL(sp.is_active,'') = ''
              OR sp.is_active = '否'
              OR sp.is_active = 'N'
              OR sp.is_active = '0'
              OR LOWER(sp.is_active) = 'false'
            )
            """
        };
        if (_userManager.TenantId > 0)
        {
            where.Add("sp.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        var inClause = BuildInClause(itemNums, "ItemNum", pars);
        where.Add($"sp.number IN ({inClause})");

        return await _db.Ado.SqlQueryAsync<SourceListRow>(
            $"""
            SELECT
                IFNULL(TRIM(sp.number),'') AS ItemNum,
                IFNULL(TRIM(sp.supplier_number),'') AS SupplierCode,
                IFNULL(TRIM(sp.supplier_name),'') AS SupplierName,
                IFNULL(TRIM(sp.supplier_type),'') AS SupplierType,
                IFNULL(sp.quota_rate,0) AS QuotaRate,
                IFNULL(sp.packaging_qty,0) AS PackagingQty,
                IFNULL(sp.lead_time,0) AS LeadTime,
                IFNULL(sp.IsRequireGoods,0) AS IsRequireGoods,
                sp.supplier_id AS SupplierId,
                IFNULL(TRIM(sp.order_rector_name),'') AS OrderRectorName,
                IFNULL(TRIM(sp.order_rector_num),'') AS OrderRectorNum,
                IFNULL(TRIM(sp.purchase_unit),'') AS PurchaseUnit,
                sp.currency_type AS CurrencyType
            FROM srm_purchase sp
            WHERE {string.Join(" AND ", where)}
            ORDER BY sp.number, IFNULL(sp.quota_priority, 999999), sp.quota_rate DESC
            """,
            pars);
    }

    private async Task<List<PurchaseOrderAvailableRow>> QueryAvailablePurchaseOrderLinesAsync(
        List<string> itemNums,
        List<string> supplierCodes,
        S3DeliveryGenerateRuleOptions rules)
    {
        var pars = new List<SugarParameter>();
        var poBuyerCodesClause = BuildInClause(rules.PoBuyerCodes, "PoBuyerCode", pars);
        var doUsagesClause = BuildInClause(rules.DoUsages, "DoUsage", pars);
        var where = new List<string>
        {
            "IFNULL(m.Status,'') <> 'C'",
            "IFNULL(d.Status,'') <> 'C'",
            "IFNULL(d.ItemNum,'') <> ''",
            "IFNULL(m.Supp,'') <> ''",
            "(IFNULL(d.QtyOrded,0) - IFNULL(d.RctQty,0)) > 0",
            """
            (
                (m.PurOrd NOT LIKE 'DO%' AND IFNULL(m.Buyer,'') IN ({0}))
                OR
                (m.PurOrd LIKE 'DO%' AND IFNULL(m.USAGE,'') IN ({1}))
            )
            """.Replace("{0}", poBuyerCodesClause).Replace("{1}", doUsagesClause)
        };
        if (_userManager.TenantId > 0)
        {
            where.Add("m.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        where.Add($"d.ItemNum IN ({BuildInClause(itemNums, "PoItemNum", pars)})");
        where.Add($"m.Supp IN ({BuildInClause(supplierCodes, "SupplierCode", pars)})");

        return await _db.Ado.SqlQueryAsync<PurchaseOrderAvailableRow>(
            $"""
            SELECT *
            FROM (
                SELECT
                    IFNULL(TRIM(m.Domain),'') AS Domain,
                    IFNULL(TRIM(m.PurOrd),'') AS PoNumber,
                    IFNULL(d.Line,0) AS PoLine,
                    d.RecID AS DetailRecId,
                    IFNULL(TRIM(d.ItemNum),'') AS ItemNum,
                    IFNULL(TRIM(d.UM),'') AS Um,
                    IFNULL(TRIM(m.Buyer),'') AS PurGroup,
                    IFNULL(TRIM(m.Supp),'') AS SupplierCode,
                    IFNULL(TRIM(s.Name),'') AS SupplierName,
                    d.DueDate AS DueDate,
                    IFNULL(d.QtyOrded,0) AS OrderQty,
                    IFNULL(d.RctQty,0) AS ReceiptQty,
                    m.tenant_id AS TenantId,
                    (
                        IFNULL(d.QtyOrded,0)
                        - IFNULL(d.RctQty,0)
                        - IFNULL((
                            SELECT SUM(
                                CASE
                                    WHEN IFNULL(ds.status,'') = 'C' THEN IFNULL(ds.sentqty,0)
                                    WHEN IFNULL(ds.schedqty,0) >= IFNULL(ds.sentqty,0) THEN IFNULL(ds.schedqty,0)
                                    ELSE IFNULL(ds.sentqty,0)
                                END)
                            FROM srm_polist_ds ds
                            WHERE ds.ponumber = d.PurOrd
                              AND ds.poline = d.Line
                              AND ds.itemnum = d.ItemNum
                              AND ds.isactive = 1
                        ), 0)
                    ) AS AvailableQty
                FROM PurOrdMaster m
                INNER JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
                LEFT JOIN ConsigneeAddressMaster s ON m.Supp = s.Address AND s.Typed = 'Supp'
                WHERE {string.Join(" AND ", where)}
            ) t
            WHERE t.AvailableQty > 0
            ORDER BY t.ItemNum, t.SupplierCode, CASE WHEN t.PoNumber LIKE 'DO%' THEN 1 ELSE 0 END, t.DueDate, t.DetailRecId
            """,
            pars);
    }

    private static decimal AllocateDemandToPurchaseLines(
        DemandScheduleCandidateRow demand,
        SourceListRow source,
        List<PurchaseOrderAvailableRow> poLines,
        Dictionary<string, decimal> allocatedByPoLine,
        decimal supplierNeedQty,
        decimal remainingDemand,
        List<PolistDeliverySchedule> schedules,
        DateTime now,
        string account,
        S3DeliveryGenerateRuleOptions rules)
    {
        var supplierAllocated = 0m;
        var lines = poLines
            .Where(x =>
                string.Equals(x.ItemNum, demand.ItemNum, StringComparison.OrdinalIgnoreCase)
                && string.Equals(x.SupplierCode, source.SupplierCode, StringComparison.OrdinalIgnoreCase))
            .ToList();

        foreach (var line in lines)
        {
            if (supplierAllocated >= supplierNeedQty) break;

            var key = BuildPoLineKey(line);
            var runAllocated = allocatedByPoLine.TryGetValue(key, out var used) ? used : 0m;
            var availableQty = line.AvailableQty - runAllocated;
            if (availableQty <= 0) continue;

            var requiredQty = supplierNeedQty - supplierAllocated;
            var qty = Math.Min(availableQty, requiredQty);
            if (rules.EnablePackagingRoundUp && source.PackagingQty > 0 && availableQty > qty)
            {
                var roundedQty = Math.Ceiling(qty / source.PackagingQty) * source.PackagingQty;
                if (roundedQty <= availableQty && roundedQty <= remainingDemand - supplierAllocated)
                {
                    qty = roundedQty;
                }
            }
            if (qty <= 0) continue;

            allocatedByPoLine[key] = runAllocated + qty;
            supplierAllocated += qty;
            schedules.Add(new PolistDeliverySchedule
            {
                Id = YitIdHelper.NextId(),
                Domain = line.Domain ?? string.Empty,
                IcdsId = demand.IcdsId,
                DsNum = string.Empty,
                Status = "N",
                ItemNum = demand.ItemNum ?? string.Empty,
                Um = line.Um ?? demand.Um ?? string.Empty,
                PurGroup = line.PurGroup ?? string.Empty,
                SupplierCode = source.SupplierCode ?? line.SupplierCode ?? string.Empty,
                Supplier = string.IsNullOrWhiteSpace(source.SupplierName) ? line.SupplierName ?? string.Empty : source.SupplierName,
                RequestDate = demand.ArrivalDate ?? now,
                NeedDate = demand.RequestDate,
                PoNumber = line.PoNumber ?? string.Empty,
                PoLine = line.PoLine,
                SchedQty = qty,
                LastSentQty = 0,
                SentQty = 0,
                RestQty = qty,
                CreateUser = account,
                UpdateUser = account,
                CreateTime = now,
                UpdateTime = now,
                Remarks = "由系统按需求计划、货源配额和采购单可用量自动生成",
                IsActive = 1,
                ReturnQty = 0,
                TenantId = demand.TenantId ?? line.TenantId
            });
        }

        return supplierAllocated;
    }

    private static void AddDeliveryException(
        List<DeliveryExceptionMaster> exceptions,
        DemandScheduleCandidateRow demand,
        DateTime optTime,
        string remark,
        decimal needQty)
    {
        exceptions.Add(new DeliveryExceptionMaster
        {
            RecID = YitIdHelper.NextId(),
            Domain = demand.TenantId?.ToString() ?? string.Empty,
            IcdsId = demand.IcdsId,
            OptTime = optTime,
            ItemNum = demand.ItemNum,
            Remark = remark,
            NeedQty = needQty,
            TenantId = demand.TenantId
        });
    }

    private static bool TryAddPurchaseRequest(
        List<PurchaseRequestMain> purchaseRequests,
        DemandScheduleCandidateRow demand,
        SourceListRow source,
        decimal shortageQty,
        DateTime now,
        string account,
        S3DeliveryGenerateRuleOptions rules)
    {
        if (shortageQty <= 0) return false;
        if (!demand.IcItemId.HasValue || demand.IcItemId.Value <= 0) return false;
        if (!source.SupplierId.HasValue || source.SupplierId.Value <= 0) return false;

        var arriveDate = demand.ArrivalDate ?? demand.RequestDate ?? now.Date;
        var leadDays = source.LeadTime > 0 ? (int)Math.Ceiling(source.LeadTime) : rules.DefaultLeadDays;
        if (leadDays < 0) leadDays = 0;
        var sendDate = arriveDate.Date.AddDays(-leadDays);
        if (sendDate < now.Date) sendDate = now.Date;

        purchaseRequests.Add(new PurchaseRequestMain
        {
            Id = YitIdHelper.NextId(),
            PrPurchaseId = source.SupplierId.Value,
            PrPurchaseNumber = source.SupplierCode,
            PrPurchaseName = source.SupplierName,
            PrPurchaser = source.OrderRectorName,
            PrPurchaserNum = source.OrderRectorNum,
            PrRqty = shortageQty,
            PrAqty = shortageQty,
            PrSqty = shortageQty,
            IcitemId = demand.IcItemId.Value,
            IcitemName = string.IsNullOrWhiteSpace(demand.ItemName) ? demand.ItemNum : demand.ItemName,
            PrSsendDate = sendDate,
            PrSarriveDate = arriveDate,
            PrUnit = string.IsNullOrWhiteSpace(source.PurchaseUnit) ? demand.Um : source.PurchaseUnit,
            State = 1,
            PrType = 3,
            CurrencyType = source.CurrencyType ?? 1,
            CreateBy = null,
            CreateByName = account,
            CreateTime = now,
            UpdateBy = null,
            UpdateByName = account,
            UpdateTime = now,
            TenantId = demand.TenantId ?? 0,
            FactoryId = demand.FactoryId,
            OrgId = demand.FactoryId,
            IsDeleted = false,
            CompanyId = demand.CompanyId ?? 1000,
            IsRequireGoods = source.IsRequireGoods,
            SupplierType = source.SupplierType
        });
        return true;
    }

    private async Task AssignPurchaseRequestNumbersAsync(List<PurchaseRequestMain> purchaseRequests, string account)
    {
        foreach (var group in purchaseRequests.GroupBy(x => x.TenantId.ToString()))
        {
            var groupRows = group.ToList();
            var prNumbers = await _numberRuleService.NextBatchInCurrentTransactionAsync("PR", group.Key, groupRows.Count, account);
            if (prNumbers.Count < groupRows.Count || prNumbers.Any(string.IsNullOrWhiteSpace))
                throw Oops.Oh($"当前采购申请号生成失败，请检查采购申请编号规则维护。Domain={group.Key}");

            for (var index = 0; index < groupRows.Count; index++)
            {
                groupRows[index].PrBillNo = prNumbers[index].Trim();
            }
        }
    }

    private async Task InsertPurchaseRequestsAsync(List<PurchaseRequestMain> purchaseRequests)
    {
        if (purchaseRequests.Count == 0) return;

        foreach (var pr in purchaseRequests)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO srm_pr_main
                (Id,pr_billno,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,create_by_name,create_time,update_by,update_by_name,update_time,tenant_id,factory_id,org_id,IsDeleted,company_id,IsRequireGoods,supplier_type)
                VALUES
                (@Id,@PrBillNo,@PrPurchaseId,@PrPurchaseNumber,@PrPurchaseName,@PrPurchaser,@PrPurchaserNum,
                 @PrRqty,@PrAqty,@PrSqty,@IcitemId,@IcitemName,@PrSsendDate,@PrSarriveDate,@PrUnit,@State,@PrType,@CurrencyType,
                 @CreateBy,@CreateByName,@CreateTime,@UpdateBy,@UpdateByName,@UpdateTime,@TenantId,@FactoryId,@OrgId,@IsDeleted,@CompanyId,@IsRequireGoods,@SupplierType)
                """,
                new SugarParameter("@Id", pr.Id),
                new SugarParameter("@PrBillNo", pr.PrBillNo),
                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),
                new SugarParameter("@PrType", pr.PrType),
                new SugarParameter("@CurrencyType", pr.CurrencyType),
                new SugarParameter("@CreateBy", pr.CreateBy),
                new SugarParameter("@CreateByName", pr.CreateByName),
                new SugarParameter("@CreateTime", pr.CreateTime),
                new SugarParameter("@UpdateBy", pr.UpdateBy),
                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("@IsDeleted", pr.IsDeleted ? 1 : 0),
                new SugarParameter("@CompanyId", pr.CompanyId),
                new SugarParameter("@IsRequireGoods", pr.IsRequireGoods),
                new SugarParameter("@SupplierType", pr.SupplierType));
        }
    }

    private static string BuildInClause(List<string> values, string prefix, List<SugarParameter> pars)
    {
        if (values.Count == 0) return "NULL";

        var names = new List<string>();
        for (var i = 0; i < values.Count; i++)
        {
            var name = $"@{prefix}{i}";
            names.Add(name);
            pars.Add(new SugarParameter(name, values[i]));
        }
        return string.Join(",", names);
    }

    private static string BuildPoLineKey(PurchaseOrderAvailableRow row)
    {
        return $"{row.PoNumber}|{row.PoLine}|{row.ItemNum}|{row.DetailRecId}";
    }

    private static string BuildScheduleTempKey(PolistDeliverySchedule row)
    {
        return $"{row.Id}|{row.PoNumber}|{row.PoLine}|{row.IcdsId}";
    }

    private static string FormatDate(DateTime? value) => value?.ToString("yyyy-MM-dd") ?? "";

    private static List<long> ParseIds(string ids)
    {
        return ids.Split(',', StringSplitOptions.RemoveEmptyEntries)
            .Select(x => long.TryParse(x.Trim(), out var id) ? id : 0)
            .Where(x => x > 0)
            .Distinct()
            .ToList();
    }

    private static string BuildCandidateKey(DeliveryScheduleCandidateRow row)
    {
        return $"{row.Domain}|{row.PoNumber}|{row.PoLine}|{row.IcdsId}";
    }

    private static string BuildListOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "dsnum" => $"ss.dsnum {dir}",
            "status" => $"ss.status {dir}",
            "itemnum" => $"ss.itemnum {dir}",
            "requestdate" => $"ss.requestdate {dir}",
            "needdate" => $"ss.needdate {dir}",
            "schedqty" => $"ss.schedqty {dir}",
            "sentqty" => $"ss.sentqty {dir}",
            "restqty" => $"ss.restqty {dir}",
            "submitdate" => $"ss.submitdate {dir}",
            "ponumber" => $"ss.ponumber {dir}",
            _ => "ss.Id DESC"
        };
    }

    private static string BuildPurchaseOrderOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "purord" => $"m.PurOrd {dir}",
            "line" => $"d.Line {dir}",
            "itemnum" => $"d.ItemNum {dir}",
            "supp" => $"m.Supp {dir}",
            "name" => $"s.Name {dir}",
            "duedate" => $"d.DueDate {dir}",
            "qtyorded" => $"d.QtyOrded {dir}",
            "buyer" => $"m.Buyer {dir}",
            _ => "m.PurOrd DESC, d.Line ASC"
        };
    }

    private sealed class DeliveryScheduleListRow
    {
        public long Id { get; set; }
        public string? Domain { get; set; }
        public long? IcdsId { get; set; }
        public string? DsNum { get; set; }
        public string? Status { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Um { get; set; }
        public string? PurGroup { get; set; }
        public string? SupplierCode { get; set; }
        public string? Supplier { get; set; }
        public DateTime? SubmitDate { get; set; }
        public DateTime? RequestDate { get; set; }
        public DateTime? NeedDate { get; set; }
        public string? PoNumber { get; set; }
        public int? PoLine { get; set; }
        public decimal? SchedQty { get; set; }
        public DateTime? LastSentDate { get; set; }
        public decimal? LastSentQty { get; set; }
        public decimal? SentQty { get; set; }
        public decimal? RestQty { get; set; }
    }

    private sealed class PurchaseOrderSelectRow
    {
        public string? PurOrd { get; set; }
        public int? Line { get; set; }
        public string? ItemNum { get; set; }
        public string? Um { get; set; }
        public string? Supp { get; set; }
        public string? Name { get; set; }
        public DateTime? DueDate { get; set; }
        public decimal? QtyOrded { get; set; }
        public string? Buyer { get; set; }
    }

    private sealed class DeliveryScheduleGenerateResult
    {
        public int DemandCount { get; set; }
        public int CandidateCount { get; set; }
        public int CreatedCount { get; set; }
        public int ExceptionCount { get; set; }
        public int PurchaseRequestCount { get; set; }
        public int PurchaseRequestMergeReducedCount { get; set; }
        public int PurchaseOrderCount { get; set; }
        public int PurchaseOrderLineCount { get; set; }
        public int QadTrackingCount { get; set; }
        public int SkippedCount { get; set; }
        public List<string> DsNums { get; set; } = new();
        public RuleConfigSnapshot<S3DeliveryGenerateRuleOptions>? RuleSnapshot { get; set; }
        public string Message { get; set; } = string.Empty;
    }

    private sealed class DeliveryScheduleCandidateRow
    {
        public string? Domain { get; set; }
        public long IcdsId { get; set; }
        public string? PoNumber { get; set; }
        public int PoLine { get; set; }
        public string? ItemNum { get; set; }
        public string? Um { get; set; }
        public string? PurGroup { get; set; }
        public string? SupplierCode { get; set; }
        public string? Supplier { get; set; }
        public DateTime? NeedDate { get; set; }
        public decimal RestQty { get; set; }
        public long? TenantId { get; set; }
    }

    private sealed class DemandScheduleCandidateRow
    {
        public long IcdsId { get; set; }
        public string? ItemNum { get; set; }
        public DateTime? RequestDate { get; set; }
        public DateTime? ArrivalDate { get; set; }
        public decimal DemandQty { get; set; }
        public long? TenantId { get; set; }
        public long? FactoryId { get; set; }
        public long? CompanyId { get; set; }
        public string? PurMfg { get; set; }
        public string? Um { get; set; }
        public string? ItemName { get; set; }
        public long? IcItemId { get; set; }
    }

    private sealed class ExistingScheduleUsageRow
    {
        public long IcdsId { get; set; }
        public decimal UsedQty { get; set; }
    }

    private sealed class SourceListRow
    {
        public string? ItemNum { get; set; }
        public string? SupplierCode { get; set; }
        public string? SupplierName { get; set; }
        public string? SupplierType { get; set; }
        public decimal QuotaRate { get; set; }
        public decimal PackagingQty { get; set; }
        public decimal LeadTime { get; set; }
        public int IsRequireGoods { get; set; }
        public long? SupplierId { get; set; }
        public string? OrderRectorName { get; set; }
        public string? OrderRectorNum { get; set; }
        public string? PurchaseUnit { get; set; }
        public long? CurrencyType { get; set; }
    }

    private sealed class PurchaseOrderAvailableRow
    {
        public string? Domain { get; set; }
        public string? PoNumber { get; set; }
        public int PoLine { get; set; }
        public long DetailRecId { get; set; }
        public string? ItemNum { get; set; }
        public string? Um { get; set; }
        public string? PurGroup { get; set; }
        public string? SupplierCode { get; set; }
        public string? SupplierName { get; set; }
        public DateTime? DueDate { get; set; }
        public decimal OrderQty { get; set; }
        public decimal ReceiptQty { get; set; }
        public long? TenantId { get; set; }
        public decimal AvailableQty { get; set; }
    }
}