using System.Diagnostics;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EntityHandler;
using GB5Shared.GB5Library.Qualifier;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using MMDAL.CustomCode.MRPRun;
using MMDAL.DTO.BOM;
using MMDAL.DTO.MRPRun;
using static GB5Shared.GB5Constant.Constant;

namespace MMBLL.MRPRun
{
    public class MRPRunBLL : IMRPRunBLL
    {
        private readonly IMRPRunDAL                         _MRPRunDAL;
        private readonly IValidation                        _Validation;
        private readonly AutoNumber                         _AutoNumber;
        private readonly IQueryExecutor                     _QueryExecutor;
        private readonly KeyInvalidate                      _KeyInvalidate;
        private readonly BaseEntityAppService<MRPRunDTO>    _BaseEntityAppService;
        private readonly ILogger<MRPRunBLL>                 _Logger;

        public MRPRunBLL(
            IMRPRunDAL mrpRunDAL,
            IValidation validation,
            AutoNumber autoNumber,
            IQueryExecutor queryExecutor,
            KeyInvalidate keyInvalidate,
            BaseEntityAppService<MRPRunDTO> baseEntityAppService,
            ILogger<MRPRunBLL> logger)
        {
            _MRPRunDAL            = mrpRunDAL;
            _Validation           = validation;
            _AutoNumber           = autoNumber;
            _QueryExecutor        = queryExecutor;
            _KeyInvalidate        = keyInvalidate;
            _BaseEntityAppService = baseEntityAppService;
            _Logger               = logger;
        }

        // ── Legacy methods (kept intact) ──────────────────────────────────

        public async Task<string> UndoMRP(UndoMRPDTO undoMRPDTO, LoginDTO loginDTO)
        {
            if (undoMRPDTO is null)
                throw new ArgumentNullException(nameof(undoMRPDTO));

            await _Validation.NotEmpty(undoMRPDTO.MRPRunId.ToString(), nameof(undoMRPDTO.MRPRunId));

            if (undoMRPDTO.MRPRunId == -1)
                throw new ArgumentException("MRPRunId cannot be -1.");

            return await _MRPRunDAL.UndoMRP(undoMRPDTO, loginDTO);
        }


        public async Task<string> GetSelectListMRP(CriteriaDTO crt, LoginDTO loginDTO, int firstNumber = -1, int maxResult = -1)
            => await _MRPRunDAL.GetSelectListMRP(crt, loginDTO, firstNumber, maxResult);

        // ── CRUD ──────────────────────────────────────────────────────────

        public async Task<MRPRunDTO?> GetMRPRun(int MRPRunId, LoginDTO loginDTO)
            => await _MRPRunDAL.GetMRPRun(MRPRunId, loginDTO).ConfigureAwait(false);

        public async Task<int> SaveMRPRun(MRPRunDTO dto, LoginDTO loginDTO)
        {
            var trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                bool isNew = dto.MRPRunId == 0;

                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(
                        1,
                        AUTONUMBERCONSTANT.MRPRUN,
                        loginDTO).ConfigureAwait(false);

                    dto.MRPRunId = autoNumber.StartNumber;

                    await _MRPRunDAL.SaveMRPRun(
                        dto,
                        loginDTO,
                        trans).ConfigureAwait(false);
                }
                else
                {
                    await _MRPRunDAL.UpdateMRPRun(
                        dto,
                        loginDTO,
                        trans).ConfigureAwait(false);
                }

                await _QueryExecutor.CommitAsync(trans).ConfigureAwait(false);

                var keyGen = new CacheKeyGeneration();

                var cacheKey = keyGen.KeyGeneration(
                    dto.MRPRunId,
                    EntityConstant.OBJECTMRPRUN,
                    CacheKeyLevel.NOT_REQUIRED,
                    loginDTO);

                await _KeyInvalidate.AllInvalidateCache(cacheKey)
                    .ConfigureAwait(false);

                return dto.MRPRunId;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans)
                    .ConfigureAwait(false);

                throw;
            }
        }
        public async Task<string> DeleteMRPRun(int mrpRunId, LoginDTO loginDTO)
        {
            var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                await _BaseEntityAppService.ExecuteSaveAsync(
    EntityConstant.OBJECTMRPRUN,
    EventTypeConstant.DELETEMRPRUNEVENTTYPEID,
    new MRPRunDTO { MRPRunId = mrpRunId },
    loginDTO,
    async tx =>
    {
        await _MRPRunDAL.DeleteMRPRun(mrpRunId, loginDTO, tx);
        return mrpRunId;
    },
    null,
    -1,
    -1,
    Trans);

                await _QueryExecutor.CommitAsync(Trans);

                //var cacheKey = KeyGenerator.KeyGeneration(
                //    mrpRunId, EntityConstant.OBJECTMRPRUN, CacheKeyLevel.NOT_REQUIRED, loginDTO);
                //await _KeyInvalidate.AllInvalidateCache(cacheKey);

                return SuccessResponse.DeleteSuccessMessage;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(Trans);
                throw;
            }
        }

        public async Task<IEnumerable<MRPRunListDTO>> GetMRPRunList(int pageOffset, int pageSize, LoginDTO loginDTO)
            => await _MRPRunDAL.GetMRPRunList(pageOffset, pageSize, loginDTO).ConfigureAwait(false);

        // ── Status query ──────────────────────────────────────────────────

        public async Task<MRPRunStatusDTO?> GetMRPRunStatus(int MRPRunId, LoginDTO loginDTO, CancellationToken ct)
            => await _MRPRunDAL.GetMRPRunStatus(MRPRunId, loginDTO, ct).ConfigureAwait(false);

        // ── MRP Processing Engine ─────────────────────────────────────────

        public async Task<MRPRunSummaryDTO> ProcessMRPRunAsync(
            MRPRunTriggerDTO trigger, int mrpRunId,LoginDTO loginDTO,
            IProgress<MRPProgressReport> progress, CancellationToken ct)
        {
            var sw     = Stopwatch.StartNew();
            var login = loginDTO;// BuildLoginFromTrigger(trigger);
            int statusId = 0;

            try
            {
                // Create status row (Pending→Running)
                var autoStatus = await _AutoNumber.GetNumberAsync(
                    1, AUTONUMBERCONSTANT.MRPRUNSTATUS, login).ConfigureAwait(false);
                var statusDto = new MRPRunStatusDTO
                {
                    MrpRunStatusId  = autoStatus.StartNumber,
                    MrpRunId        = mrpRunId,
                    Status          = 1,
                    ProgressPercent = 0,
                    CurrentStep     = "Starting",
                    StartedAt       = DateTime.UtcNow,
                    TenantId        = trigger.TenantId
                };
                statusId = await _MRPRunDAL.SaveMRPRunStatus(statusDto, login, ct)
                    .ConfigureAwait(false);

                // Step 1 (5%): Load MRP config
                await ReportAndUpdate(progress, statusId, 5, "Loading MRP configuration", login, ct);
                var config = await _MRPRunDAL.GetMRPConfig(trigger.MrpId, login, ct).ConfigureAwait(false)
                    ?? throw new InvalidOperationException($"MRP config {trigger.MrpId} not found.");

                // Step 2 (8%): Validate MPS prerequisite for MRP type=1
                if (trigger.TypeofRun == 1)
                {
                    await ReportAndUpdate(progress, statusId, 8, "Validating MPS prerequisite", login, ct);
                    bool mpsExists = await _MRPRunDAL.CheckMPSRunExists(trigger.MrpId, login, ct)
                        .ConfigureAwait(false);
                    if (!mpsExists)
                        throw new InvalidOperationException(
                            "MRP (Type=1) requires a prior MPS run (Type=0). Run MPS first.");
                }

                // Step 3-4 (12-15%): Load demand
                await ReportAndUpdate(progress, statusId, 12, "Loading demand data", login, ct);
                var demandLines = await LoadDemandAsync(trigger, config, login, ct).ConfigureAwait(false);
                if (demandLines.Count == 0)
                    throw new InvalidOperationException("No demand found for this MRP run configuration.");

                // Step 5 (20%): Load opening stock
                await ReportAndUpdate(progress, statusId, 20, "Loading opening stock", login, ct);
                var stockByItem = await LoadOpeningStockAsync(trigger, config, login, ct).ConfigureAwait(false);

                // Step 6 (25%): Load scheduled receipts



                await ReportAndUpdate(progress, statusId, 25, "Loading scheduled receipts", login, ct);
                var receiptsByItem = await LoadScheduledReceiptsAsync(trigger, login, ct).ConfigureAwait(false);

                // Step 7 (35%): BOM explosion — skip for MinMax (2) and ROL (3)
                Dictionary<int, List<BOMMRPDTO>> bomByParent = [];
                if (trigger.TypeofRun <= 1)
                {
                    await ReportAndUpdate(progress, statusId, 35, "Exploding BOMs", login, ct);
                    var topItemIds = demandLines.Select(d => d.ItemId).Distinct().ToList();
                    var bomLines   = await _MRPRunDAL.GetBOMExplosion(topItemIds, login, ct)
                        .ConfigureAwait(false);
                    bomByParent = bomLines.GroupBy(b => b.ItemId)
                        .ToDictionary(g => g.Key, g => g.ToList());
                }

                // Step 8 (50%): Net requirement calculation
                await ReportAndUpdate(progress, statusId, 50, "Calculating net requirements", login, ct);
                var (runDetails, detailCount) = await CalculateNetRequirementsAsync(
                    mrpRunId, config, trigger, demandLines, stockByItem, receiptsByItem, bomByParent, login)
                    .ConfigureAwait(false);

                // Step 9 (65%): BulkInsert TMRPRUNDETAILS
                await ReportAndUpdate(progress, statusId, 65, "Saving run details", login, ct);
                await _MRPRunDAL.BulkInsertMRPRunDetails(runDetails, login, ct).ConfigureAwait(false);

                // Step 9b (68%): Write BOM component quantities to TMRPRUNDETAILPART
                // Required for MRP workbench drill-down and UndoMRP (UNDO_MRP_DELETE_MRPRUNDETAILPART).
                await ReportAndUpdate(progress, statusId, 68, "Saving BOM part breakdown", login, ct);
                GB5Trace.Step("insert-mrprundetailpart", new { mrpRunId });
                await _MRPRunDAL.InsertMRPRunDetailParts(mrpRunId, login, ct).ConfigureAwait(false);

                // Step 9c (71%): Write component demand back to MMASTERSCHEDULEDETAIL as SOURCETYPE=5
                // Required for Master Schedule workbench to show dependent (BOM-driven) demand.
                // Undo counterpart: UNDO_MRP_DELETE_MASTERSCHEDULEDETAIL_SOURCETYPE5

                await ReportAndUpdate(progress, statusId, 71, "Writing dependent demand rows", login, ct);
                GB5Trace.Step("insert-dependent-demand", new { mrpRunId, config.MasterScheduleId });
                await _MRPRunDAL.InsertDependentDemandRows(mrpRunId, config.MasterScheduleId, 0, login, ct)
                    .ConfigureAwait(false);

                // Step 10 (75%): Generate MRP actions from planned orders
                await ReportAndUpdate(progress, statusId, 75, "Generating MRP actions", login, ct);
                var (actions, actionCount) = await GenerateMRPActionsAsync(
                    mrpRunId, trigger, runDetails, login).ConfigureAwait(false);

                // Step 11 (80%): BulkInsert TMRPACTION
                await ReportAndUpdate(progress, statusId, 80, "Saving MRP actions", login, ct);
                await _MRPRunDAL.BulkInsertMRPActions(actions, login, ct).ConfigureAwait(false);

                // Backfill the DB-assigned IDENTITY IDs onto the in-memory list.
                // TMRPACTION.MRPACTIONID is an identity column — the insert omits it,
                // so actions[i].MrpActionId is 0 until we re-read it here.
                // Pegging rows must reference the real IDs, so this step is mandatory.
                var actionIdMap = await _MRPRunDAL
                    .GetMRPActionIdsByRunAsync(mrpRunId, login, ct).ConfigureAwait(false);
                foreach (var action in actions)
                {
                    if (actionIdMap.TryGetValue(action.MRPRunDetailId, out int assignedId))
                        action.MrpActionId = assignedId;
                }

                // Step 12-13 (85%): Priority-aware pegging
                int peggingRowsCreated = 0;
                if (trigger.IsPeggingRequired == 1 && actions.Count > 0)
                {
                    await ReportAndUpdate(progress, statusId, 82, "Running priority-aware pegging", login, ct);
                    var (peggingRows, peggingAutoStart) = await GeneratePeggingAsync(
                        demandLines, actions, runDetails, trigger.TenantId, login)
                        .ConfigureAwait(false);
                    if (peggingRows.Count > 0)
                    {
                        await _MRPRunDAL.BulkInsertPegging(peggingRows, login, ct).ConfigureAwait(false);
                        peggingRowsCreated = peggingRows.Count;
                    }
                }

                // Step 14 (88%): Update MMASTERSCHEDULEDETAIL.MRPRUNID
                await ReportAndUpdate(progress, statusId, 88, "Linking demand to run", login, ct);
                if (trigger.TypeofRun <= 1)
                {
                    await _MRPRunDAL.UpdateMasterScheduleDetailMRPRunId(
                        trigger.MrpId, mrpRunId, config.MasterScheduleId, trigger.TypeofRun, login, ct).ConfigureAwait(false);
                }

                // Complete
                await _MRPRunDAL.UpdateMRPRunStatus(
                    statusId, 2, 100, "Completed", null, DateTime.UtcNow, login, ct)
                    .ConfigureAwait(false);

                _Logger.LogInformation("MRP run {MrpRunId} completed in {Ms}ms. " +
                    "Items: {Items}, Actions: {Actions}, Pegging: {Pegging}",
                    mrpRunId, sw.ElapsedMilliseconds, demandLines.Select(d => d.ItemId).Distinct().Count(),
                    actionCount, peggingRowsCreated);

                return new MRPRunSummaryDTO
                {
                    MrpRunId           = mrpRunId,
                    ItemsPlanned       = demandLines.Select(d => d.ItemId).Distinct().Count(),
                    ActionsCreated     = actionCount,
                    PeggingRowsCreated = peggingRowsCreated,
                    DurationMs         = sw.ElapsedMilliseconds,
                    RunScope           = (byte)trigger.RunScope
                };
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex, "MRP run {MrpRunId} failed", mrpRunId);
                if (statusId > 0)
                {
                    await _MRPRunDAL.UpdateMRPRunStatus(
                        statusId, 3, 0, "Failed", ex.Message, DateTime.UtcNow, login,
                        CancellationToken.None).ConfigureAwait(false);
                }
                throw;
            }
        }

        // ── Private helpers ───────────────────────────────────────────────

        private async Task ReportAndUpdate(IProgress<MRPProgressReport> progress, int statusId,
            byte percent, string step, LoginDTO login, CancellationToken ct)
        {
            try
            {
                progress.Report(new MRPProgressReport(percent, step));
                await _MRPRunDAL.UpdateMRPRunStatus(statusId, 1, percent, step, null, null, login, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to report/update progress for MrpRunStatusId {StatusId} at step '{Step}'",
                    statusId, step);
                throw;
            }
        }

        private async Task<List<MRPDemandLineDTO>> LoadDemandAsync(
            MRPRunTriggerDTO trigger, MMDAL.DTO.MRP.MRPDTO config,
            LoginDTO login, CancellationToken ct)
        {
            try
            {
                var lines = new List<MRPDemandLineDTO>();

                if (trigger.RunScope == MrpRunScope.SalesOrder && trigger.SalesOrderId.HasValue)
                {
                    await foreach (var d in _MRPRunDAL.StreamDemandFromSalesOrder(
                        trigger.SalesOrderId.Value, login, ct).ConfigureAwait(false))
                        lines.Add(d);
                }
                else
                {
                    var spec = new DemandFilterSpecDTO
                    {
                        RunScope              = trigger.RunScope,
                        MasterScheduleId      = config.MasterScheduleId,
                        AllocationId          = trigger.AllocationId,
                        SalesOrderId          = trigger.SalesOrderId,
                        FGItemIds             = trigger.FGItemIds,
                        MaxPriorityToInclude  = trigger.MaxPriorityToInclude
                    };
                    await foreach (var d in _MRPRunDAL.StreamDemandForMRP(
                        config.MasterScheduleId, spec, login, ct).ConfigureAwait(false))
                        lines.Add(d);
                }

                return lines;
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to load demand for MrpId {MrpId}, RunScope {RunScope}",
                    trigger.MrpId, trigger.RunScope);
                throw;
            }
        }

        private async Task<Dictionary<int, decimal>> LoadOpeningStockAsync(
            MRPRunTriggerDTO trigger, MMDAL.DTO.MRP.MRPDTO config,
            LoginDTO login, CancellationToken ct)
        {
            try
            {
                IEnumerable<MRPOpeningStockDTO> stockRows;

                // Ring-fenced stock for allocation scope (only if IncludeStock=1)
                if (trigger.RunScope == MrpRunScope.Allocation && trigger.AllocationId.HasValue
                    && config.MRPIncludeStock == 1)
                {
                    stockRows = await _MRPRunDAL.GetOpeningStockAllocationScoped(
                        trigger.AllocationId.Value, login.WorkOUId, login, ct).ConfigureAwait(false);
                }
                else if (config.MRPIncludeStock == 1)
                {
                    stockRows = await _MRPRunDAL.GetOpeningStock(login.WorkOUId, login, ct)
                        .ConfigureAwait(false);
                }
                else
                {
                    return [];
                }

                return stockRows.ToDictionary(s => s.ItemId, s => s.StockLedgerGoodQuantity);
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to load opening stock for MrpId {MrpId}, WorkOUId {WorkOUId}",
                    trigger.MrpId, login.WorkOUId);
                throw;
            }
        }

        private async Task<Dictionary<int, List<MRPScheduledReceiptDTO>>> LoadScheduledReceiptsAsync(
            MRPRunTriggerDTO trigger, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var receipts = await _MRPRunDAL.GetScheduledReceipts(
                    login.WorkOUId, trigger.FromDate, login, ct).ConfigureAwait(false);

                return receipts
                    .GroupBy(r => r.ItemId)
                    .ToDictionary(g => g.Key, g => g.ToList());
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to load scheduled receipts for MrpId {MrpId}, WorkOUId {WorkOUId}",
                    trigger.MrpId, login.WorkOUId);
                throw;
            }
        }

        // ── Net requirement calculation ───────────────────────────────────

        private async Task<(List<MRPRunDetailInsertDTO> rows, int count)> CalculateNetRequirementsAsync(
            int mrpRunId,
            MMDAL.DTO.MRP.MRPDTO config,
            MRPRunTriggerDTO trigger,
            List<MRPDemandLineDTO> demandLines,
            Dictionary<int, decimal> stockByItem,
            Dictionary<int, List<MRPScheduledReceiptDTO>> receiptsByItem,
            Dictionary<int, List<BOMMRPDTO>> bomByParent,
            LoginDTO login)
        {
            try
            {
            // Demand keyed by (ItemId, PeriodDate) — sum DailyQuantity
            var independentDemand = demandLines
                .GroupBy(d => (d.ItemId, d.PeriodDate.Date))
                .ToDictionary(g => g.Key, g => new DemandAggregation
                {
                    IndependentPlanDemand    = g.Sum(x => x.DailyQuantity),
                    AllocationId             = g.First().AllocationId,
                    SalesOrderId             = g.First().SalesOrderId,
                    Priority                 = g.Min(x => x.Priority)   // highest (lowest value)
                });

            // All items to plan: start with FG items; BOM explosion adds components
            // Process order: by BOM level (0 = FG, 1 = direct component, etc.)
            var allItems = CollectAllPlanningItems(demandLines, bomByParent);
            var maxLevel = allItems.Values.Max(i => i.Level);

            // Dependent demand accumulator: (ItemId, PeriodDate) → qty
            var dependentDemand = new Dictionary<(int, DateTime), decimal>();

            var resultRows = new List<MRPRunDetailInsertDTO>();
            short detailSlNo = 0;

            var autoDetail = await _AutoNumber.GetNumberAsync(
                allItems.Count * 10, AUTONUMBERCONSTANT.MRPRUN, login).ConfigureAwait(false);
            int detailId = autoDetail.StartNumber;

            // Process level by level (0 = FG first, deeper = later)
            for (int level = 0; level <= maxLevel; level++)
            {
                var itemsAtLevel = allItems.Where(kv => kv.Value.Level == level)
                    .Select(kv => kv.Key).ToList();

                foreach (int itemId in itemsAtLevel)
                {
                    var itemInfo  = allItems[itemId];
                    var periods   = GetPlanningPeriods(trigger.FromDate, trigger.ToDate);
                    decimal runSOH = stockByItem.GetValueOrDefault(itemId, 0);

                    foreach (var period in periods)
                    {
                        decimal indDemand = independentDemand.GetValueOrDefault(
                            (itemId, period.Date), new DemandAggregation()).IndependentPlanDemand;
                        decimal depDemand = dependentDemand.GetValueOrDefault((itemId, period.Date), 0);
                        decimal grossReq  = indDemand + depDemand;

                        decimal schedReceipts = receiptsByItem.GetValueOrDefault(itemId, [])
                            .Where(r => r.ExpectedDate.Date == period.Date)
                            .Sum(r => r.Quantity);

                        decimal netReq     = Math.Max(0, grossReq - runSOH - schedReceipts);
                        decimal lotSize    = itemInfo.LotSize;
                        decimal plannedQty = netReq <= 0 ? 0 :
                            config.MRPIncludeLotSize == 1
                                ? Math.Ceiling(netReq / lotSize) * lotSize
                                : netReq;

                        // Planned order receipt date = period; release date = period - LeadTime
                        decimal projSOH = runSOH + schedReceipts + plannedQty - grossReq;

                        if (grossReq > 0 || plannedQty > 0)
                        {
                            var demAgg = independentDemand.GetValueOrDefault(
                                (itemId, period.Date), new DemandAggregation());

                            resultRows.Add(new MRPRunDetailInsertDTO
                            {
                                MRPRunDetailId = detailId++,
                                MRPRunId = mrpRunId,
                                PlanItemId = itemId,
                                PlanSKUId = itemInfo.SKUId,
                                SlNo = ++detailSlNo,
                                DemandType = 0,//(byte)level,
                                Level = (byte)level,
                                LowLevelCode = (byte)itemInfo.Level,
                                RequiredPeriod = period,
                                TotalGrossRequirement = grossReq,
                                IndependentPlanDemand = indDemand,
                                IndependentCustomerOrder = 0,
                                DependentPlanDemand = depDemand,
                                DependentCustomerOrder = 0,
                                Allocation = 0,
                                ScheduledReceipts = schedReceipts,
                                ProjectedSOH = projSOH,
                                SOH = runSOH,
                                WIP = 0,
                                NetRequirement = netReq,
                                PlannedOrderReceipts = plannedQty,
                                PlannedOrderReleases = plannedQty,
                                LeadTime = itemInfo.LeadTime,
                                TenantId = trigger.TenantId
                            });

                            // Propagate dependent demand to components (BOM explosion)
                            if (plannedQty > 0 && bomByParent.TryGetValue(itemId, out var components))
                            {
                                // Release date for components = period - this item's leadtime
                                DateTime releaseDate = config.MRPIncludeLeadTime == 1
                                    ? period.AddDays(-itemInfo.LeadTime)
                                    : period;

                                foreach (var comp in components.Where(c => c.DetailItemLowLevelCode == level))
                                {
                                    var key = (comp.DetailItemId, releaseDate.Date);
                                    dependentDemand[key] = dependentDemand.GetValueOrDefault(key, 0)
                                        + plannedQty * comp.FinalQuantity;
                                }
                            }
                        }

                        runSOH = projSOH;
                    }
                }
            }

            return (resultRows, resultRows.Count);
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to calculate net requirements for MRP run {MrpRunId}",
                    mrpRunId);
                throw;
            }
        }

        private async Task<(List<MRPActionInsertDTO> actions, int count)> GenerateMRPActionsAsync(
            int mrpRunId, MRPRunTriggerDTO trigger,
            List<MRPRunDetailInsertDTO> runDetails, LoginDTO login)
        {
            try
            {
            var plannedOrders = runDetails
                .Where(d => d.NetRequirement > 0)
                .ToList();

            if (plannedOrders.Count == 0)
                return ([], 0);

            //var auto = await _AutoNumber.GetNumberAsync(
            //    plannedOrders.Count, AUTONUMBERCONSTANT.MRPACTION, login).ConfigureAwait(false);
            //int actionId = auto.StartNumber;
            short slNo   = 0;

            var actions = plannedOrders.Select(d =>
            {
                DateTime plannedOrderDate = d.LeadTime > 0
                    ? d.RequiredPeriod.AddDays(-d.LeadTime)
                    : d.RequiredPeriod;

                return new MRPActionInsertDTO
                {
                    //MrpActionId                = actionId++,
                    MrpRunId                   = mrpRunId,
                    SlNo                       = ++slNo,
                    PlanItemId                 = d.PlanItemId,
                    PlanSKUId                  = d.PlanSKUId,
                    PlanType                   = 0,
                    ObjectId                   = -1,
                    PlannedStartDate           = plannedOrderDate,
                    PlannedOrderDate           = plannedOrderDate,
                    PlannedDueDate             = d.RequiredPeriod,
                    PlannedQuantity            = d.PlannedOrderReceipts,
                    OriginalPlannedQuantity    = d.PlannedOrderReceipts,
                    InProcessQuantity          = 0,
                    ImplementedQuantity        = 0,
                    CompressionDays            = 0,
                    IsFirmPlan                 = 0,
                    FirmDate                   = plannedOrderDate,
                    FirmQuantity               = 0,
                    PlannedAction              = 0,
                    Remarks                    = string.Empty,
                    PlanStatus                 = 0,
                    StoreId                    = -1,
                    AllocationId               = trigger.AllocationId ?? -1,
                    TaskId                     = -1,
                    ReleaseType                = 0,
                    ReleaseBizTransactionTypeId = -1,
                    ReleaseId                  = -1,
                    PartyId                    = -1,
                    PartyBranchId              = -1,
                    AlternateBOMId             = -1,
                    AlternateRoutingId         = -1,
                    MRPRunDetailId             = d.MRPRunDetailId,
                    NestingPlanId              = -1,
                    IsAllocationBasedRelease   = 0,
                    Priority                   = 0,
                    SalesOrderId               = trigger.SalesOrderId ?? 0,
                    RunScope                   = (byte)trigger.RunScope,
                    TenantId                   = trigger.TenantId
                };
            }).ToList();

            return (actions, actions.Count);
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to generate MRP actions for MRP run {MrpRunId}",
                    mrpRunId);
                throw;
            }
        }

        private async Task<(List<MRPPeggingInsertDTO> pegging, int autoStart)> GeneratePeggingAsync(
            List<MRPDemandLineDTO> demandLines,
            List<MRPActionInsertDTO> actions,
            List<MRPRunDetailInsertDTO> runDetails,
            int tenantId, LoginDTO login)
        {
            try
            {
            if (actions.Count == 0 || demandLines.Count == 0)
                return ([], 0);

            //var auto = await _AutoNumber.GetNumberAsync(
            //    actions.Count + demandLines.Count, AUTONUMBERCONSTANT.PEGGING, login)
            //    .ConfigureAwait(false);
            //int peggingId = auto.StartNumber;

            // Priority-aware pegging: sort demand by priority ASC (0 = unset → lowest)
            var sortedDemand = demandLines
                .OrderBy(d => d.Priority == 0 ? int.MaxValue : (int)d.Priority)
                .ThenBy(d => d.PeriodDate)
                .ToList();

            // Map action to its run detail for qty reference
            var actionByDetailId = actions.ToDictionary(a => a.MRPRunDetailId, a => a);
            var detailById       = runDetails.ToDictionary(d => d.MRPRunDetailId, d => d);

            // Cumulative pegging per action: how much of each planned order is already pegged
            var peggedPerAction = new Dictionary<int, decimal>();
            var peggingRows     = new List<MRPPeggingInsertDTO>();

            foreach (var demand in sortedDemand)
            {
                // Find run detail rows for this demand item + period
                var matchingDetails = runDetails
                    .Where(d => d.PlanItemId == demand.ItemId
                             && d.RequiredPeriod.Date == demand.PeriodDate.Date)
                    .ToList();

                foreach (var detail in matchingDetails)
                {
                    if (!actionByDetailId.TryGetValue(detail.MRPRunDetailId, out var action))
                        continue;

                    decimal available = action.PlannedQuantity
                        - peggedPerAction.GetValueOrDefault(action.MrpActionId, 0);
                    if (available <= 0) continue;

                    decimal pegQty = Math.Min(demand.DailyQuantity, available);

                    peggingRows.Add(new MRPPeggingInsertDTO
                    {
                        //PeggingId      = peggingId++,
                        MrpActionId    = action.MrpActionId,
                        MrpRunDetailId = detail.MRPRunDetailId,
                        PeggedQuantity = pegQty,
                        Priority       = demand.Priority,
                        TenantId       = tenantId
                    });

                    peggedPerAction[action.MrpActionId] =
                        peggedPerAction.GetValueOrDefault(action.MrpActionId, 0) + pegQty;
                }
            }

            return (peggingRows, 1);
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex,
                    "Failed to generate pegging for tenant {TenantId}",
                    tenantId);
                throw;
            }
        }

        // ── Utility methods ───────────────────────────────────────────────

        private static LoginDTO BuildLoginFromTrigger(MRPRunTriggerDTO trigger)
            => new() { ClientId = trigger.TenantId, WorkOUId = trigger.WorkOUId, UserId = trigger.UserId };

        private static List<DateTime> GetPlanningPeriods(DateTime from, DateTime to)
        {
            try
            {
                var periods = new List<DateTime>();
                for (var d = from.Date; d <= to.Date; d = d.AddDays(1))
                    periods.Add(d);
                return periods;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(
                    $"Failed to build planning periods from {from:d} to {to:d}.", ex);
            }
        }

        private static Dictionary<int, PlanningItemInfo> CollectAllPlanningItems(
            List<MRPDemandLineDTO> demandLines,
            Dictionary<int, List<BOMMRPDTO>> bomByParent)
        {
            try
            {
            var items = new Dictionary<int, PlanningItemInfo>();

            // Level 0 = FG items from demand
            foreach (var d in demandLines)
            {
                if (!items.ContainsKey(d.ItemId))
                    items[d.ItemId] = new PlanningItemInfo
                    {
                        ItemId  = d.ItemId,
                        SKUId   = d.SKUId,
                        Level   = 0,
                        LotSize = 1,
                        LeadTime = 0
                    };
            }

            // Add components from BOM explosion
            foreach (var parent in bomByParent)
            {
                foreach (var comp in parent.Value)
                {
                    var newLevel = comp.DetailItemLowLevelCode + 1;
                    if (!items.ContainsKey(comp.DetailItemId))
                        items[comp.DetailItemId] = new PlanningItemInfo
                        {
                            ItemId   = comp.DetailItemId,
                            SKUId    = comp.SKUId,
                            Level    = newLevel,
                            LotSize  = comp.DetailItemLotSize,
                            LeadTime = comp.DetailItemLeadTime
                        };
                    else
                    {
                        // Update to deepest level seen (LLC rule)
                        var existing = items[comp.DetailItemId];
                        if (newLevel > existing.Level)
                            existing.Level = newLevel;
                    }
                }
            }

            return items;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException("Failed to collect MRP planning items.", ex);
            }
        }

        // ── Inner types ───────────────────────────────────────────────────

        private class DemandAggregation
        {
            public decimal IndependentPlanDemand { get; set; }
            public int     AllocationId          { get; set; }
            public int     SalesOrderId          { get; set; }
            public short   Priority              { get; set; }
        }

        private class PlanningItemInfo
        {
            public int     ItemId   { get; set; }
            public int     SKUId    { get; set; }
            public int     Level    { get; set; }
            public decimal LotSize  { get; set; } = 1;
            public short   LeadTime { get; set; }
        }

        // ── Trace export ─────────────────────────────────────────────────────

        public async Task<string> GetMRPRunTrace(int mrpRunId, LoginDTO loginDTO, CancellationToken ct)
        {
            GB5Trace.Step("get-mrprun-trace", new { mrpRunId });
            var trace = await _MRPRunDAL.GetMRPRunTrace(mrpRunId, loginDTO, ct).ConfigureAwait(false);
            return Newtonsoft.Json.JsonConvert.SerializeObject(trace);
        }
    }
}