using System.Data.Common;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.BOM;
using MMDAL.DTO.MRP;
using MMDAL.DTO.MRPRun;
using MMDAL.Query.MRPRun;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.MRPRun
{
    public class MRPRunDAL : IMRPRunDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public MRPRunDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

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

        public async Task<string> UndoMRP(UndoMRPDTO undoMRPDTO, LoginDTO loginDTO)
        {
            var param = new { mrprunid = undoMRPDTO.MRPRunId };

            var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_PEGGING,                              param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_MRPACTION,                            param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_MRPRUNDETAILPART,                     param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_MRPRUNDETAILS,                        param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_MASTERSCHEDULEDETAIL_SOURCETYPE5,     param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_UPDATE_MASTERSCHEDULEDETAIL_RESET,           param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_DELETE_TMRPRUN,                              param, Trans);
                await _QueryExecutor.ExecuteAsync(loginDTO, MRPRunQB.UNDO_MRP_UPDATE_MASTERSCHEDULEDETAIL_BALANCE,         param, Trans);

                await _QueryExecutor.CommitAsync(Trans);
                return JsonConvert.SerializeObject(new { Success = true });
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(Trans);
                throw;
            }
        }

        public async Task<string> GetSelectListMRP(CriteriaDTO crt, LoginDTO loginDTO, int firstNumber = -1, int maxResult = -1)
        {
            var attrs = crt.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList ?? [])
                .ToList() ?? [];

            int mrpId      = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "MrpId")?.FieldValue);
            int type       = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "Type")?.FieldValue);
            int lastRecord = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "LastRecord")?.FieldValue);

            // GB4 parity: LastRecord==0 (default here) restricts to the latest run per
            // MrpId/Type (GB4's GET_MRPRUN_LAST_RECORD_SELECTLIST). LastRecord==1 returns
            // the full, paged run history (GB4's default GET_MRPRUN_SELECTLIST path).
            if (lastRecord == 1)
            {
                int masterScheduleId = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "MasterScheduleId")?.FieldValue);
                string? runByCode = attrs.FirstOrDefault(a => a.FieldName == "RunbyCode")?.FieldValue?.ToString();
                string? runByName = attrs.FirstOrDefault(a => a.FieldName == "RunbyName")?.FieldValue?.ToString();

                var allParam = new
                {
                    mrpid = mrpId == 0 ? -1 : mrpId,
                    type = type == 0 ? -1 : type,
                    masterscheduleid = masterScheduleId == 0 ? -1 : masterScheduleId,
                    runbycode = string.IsNullOrWhiteSpace(runByCode) ? null : runByCode,
                    runbyname = string.IsNullOrWhiteSpace(runByName) ? null : runByName,
                    ouid = loginDTO.WorkOUId,
                    firstnumber = firstNumber < 0 ? 0 : firstNumber,
                    maxresult = maxResult <= 0 ? 50 : maxResult
                };

                var allResult = await _QueryExecutor.QueryAsync<MRPRunSelectListDTO>(
                    loginDTO, MRPRunQB.GET_SELECTLIST_MRP_ALL, allParam);

                return JsonConvert.SerializeObject(allResult);
            }

            var param = new { mrpid = mrpId, type = type, ouid = loginDTO.WorkOUId };

            var result = await _QueryExecutor.QueryAsync<MRPRunSelectListDTO>(
                loginDTO, MRPRunQB.GET_SELECTLIST_MRP, param);

            return JsonConvert.SerializeObject(result);
        }

        // ── MRP Run CRUD ──────────────────────────────────────────────────

        public async Task<MRPRunDTO?> GetMRPRun(int MRPRunId, LoginDTO loginDTO)
        {
            var param = new { MrpRunId = MRPRunId };
            return await _QueryExecutor
                .QuerySingleAsync<MRPRunDTO>(loginDTO, MRPRunQB.GET_MRPRUN, param)
                .ConfigureAwait(false);
        }

        public async Task<Result<string>> SaveMRPRun(MRPRunDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            if (dto == null)
                return Result<string>.Failure("MRP Run data is null.");

            DateTime currentDateTime = DateTime.UtcNow;

            dto.MRPRunCreatedById = loginDTO.UserId;
            dto.MRPRunCreatedOn = currentDateTime;
            dto.MRPRunModifiedById = loginDTO.UserId;
            dto.MRPRunModifiedOn = currentDateTime;
            dto.TenantId = loginDTO.ClientId;

            // Prevent SqlDateTime overflow
            if (dto.MRPRunMRPDate < Convert.ToDateTime("1753-01-01"))
                dto.MRPRunMRPDate = currentDateTime;

            // Guard: SCOPEOBJECTID is NOT NULL in DB — default to 0 for Macro scope
            dto.ScopeObjectId ??= 0;

            int rows = await _QueryExecutor
                .ExecuteAsync(loginDTO, MRPRunQB.SAVE_MRPRUN, dto, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("MRP Run saved successfully.")
                : Result<string>.Failure("Failed to save MRP Run.");
        }

        public async Task<Result<string>> UpdateMRPRun(MRPRunDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.MRPRunModifiedById = loginDTO.UserId;
            dto.MRPRunModifiedOn = DateTime.UtcNow;

            // Guard: SCOPEOBJECTID is NOT NULL in DB — default to 0 for Macro scope
            dto.ScopeObjectId ??= 0;

            int rows = await _QueryExecutor
                .ExecuteAsync(loginDTO, MRPRunQB.UPDATE_MRPRUN, dto, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("MRP Run updated.")
                : Result<string>.Failure("Failed to update MRP Run.");
        }

        public async Task<Result<string>> DeleteMRPRun(int mrpRunId, LoginDTO loginDTO, DbTransaction tx)
        {
            int rows = await _QueryExecutor
                .ExecuteAsync(loginDTO, MRPRunQB.DELETE_MRPRUN, new { MRPRunId = mrpRunId }, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("MRP Run deleted.")
                : Result<string>.Failure("MRP Run not found or already deleted.");
        }

        public async Task<IEnumerable<MRPRunListDTO>> GetMRPRunList(int pageOffset, int pageSize, LoginDTO loginDTO)
        {
            var param = new { PageOffset = pageOffset, PageSize = pageSize };
            return await _QueryExecutor
                .QueryAsync<MRPRunListDTO>(loginDTO, MRPRunQB.GET_MRPRUN_LIST, param)
                .ConfigureAwait(false) ?? [];
        }

        public async Task<int> GetMRPRunListCount(LoginDTO loginDTO)
        {
            return await _QueryExecutor
                .ExecuteScalarAsync<int>(loginDTO, MRPRunQB.GET_MRPRUN_LIST_COUNT, null)
                .ConfigureAwait(false);
        }

         

        public async Task<MRPDTO?> GetMRPConfig(int mrpId, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor
                .QuerySingleAsync<MRPDTO>(login, MRPRunQB.GET_MRP_CONFIG, new { MrpId = mrpId,TenantId=login.ClientId })
                .ConfigureAwait(false);
        }

        public async Task<bool> CheckMPSRunExists(int mrpId, LoginDTO login, CancellationToken ct)
        {
            int count = await _QueryExecutor
                .ExecuteScalarAsync<int>(login, MRPRunQB.GET_MPS_RUN_EXISTS, new { MrpId = mrpId,TenantId=login.ClientId })
                .ConfigureAwait(false);
            return count > 0;
        }

        public async IAsyncEnumerable<MRPDemandLineDTO> StreamDemandForMRP(
            int masterScheduleId, DemandFilterSpecDTO spec, LoginDTO login,
            [EnumeratorCancellation] CancellationToken ct)
        {
            string sql = BuildDemandFilterSql(spec);
            var param  = BuildDemandFilterParams(masterScheduleId, login.ClientId, spec);

            await foreach (var row in _QueryExecutor.StreamAsync<MRPDemandLineDTO>(login, sql, param).ConfigureAwait(false))
                yield return row;
        }

        public async IAsyncEnumerable<MRPDemandLineDTO> StreamDemandFromSalesOrder(
            int salesOrderId, LoginDTO login,
            [EnumeratorCancellation] CancellationToken ct)
        {
            await foreach (var row in _QueryExecutor
                .StreamAsync<MRPDemandLineDTO>(login, MRPRunQB.GET_DEMAND_FROM_SALESORDER,
                    new { SalesOrderId = salesOrderId })
                .ConfigureAwait(false))
            {
                yield return row;
            }
        }

        public async Task<IEnumerable<MRPOpeningStockDTO>> GetOpeningStock(int ouId, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor
                .QueryAsync<MRPOpeningStockDTO>(login, MRPRunQB.GET_OPENING_STOCK_MRP_PROCESS,
                    new { OuId = ouId })
                .ConfigureAwait(false) ?? [];
        }

        public async Task<IEnumerable<MRPOpeningStockDTO>> GetOpeningStockAllocationScoped(int allocationId, int ouId, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor
                .QueryAsync<MRPOpeningStockDTO>(login, MRPRunQB.GET_OPENING_STOCK_ALLOCATION_SCOPED,
                    new { AllocationId = allocationId, OuId = ouId })
                .ConfigureAwait(false) ?? [];
        }

        public async Task<IEnumerable<MRPScheduledReceiptDTO>> GetScheduledReceipts(int ouId, DateTime fromDate, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor
                .QueryAsync<MRPScheduledReceiptDTO>(login, MRPRunQB.GET_SCHEDULED_RECEIPTS,
                    new { OuId = ouId, FromDate = fromDate,TenantId=login.ClientId })
                .ConfigureAwait(false) ?? [];
        }

        public async Task<IEnumerable<BOMMRPDTO>> GetBOMExplosion(IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor
                .QueryAsync<BOMMRPDTO>(login, MRPRunQB.GET_BOM_EXPLOSION,
                    new { ItemIds = itemIds,TenantId=login.ClientId })
                .ConfigureAwait(false) ?? [];
        }

        // ── Run status ────────────────────────────────────────────────────

        public async Task<int> SaveMRPRunStatus(MRPRunStatusDTO dto, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.SAVE_MRPRUNSTATUS, dto, null, ct)
                .ConfigureAwait(false);
            return dto.MrpRunStatusId;
        }

        public async Task UpdateMRPRunStatus(int mrpRunStatusId, byte status, byte percent, string step,
            string? error, DateTime? completedAt, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.UPDATE_MRPRUNSTATUS, new
                {
                    MrpRunStatusId = mrpRunStatusId,
                    Status         = status,
                    ProgressPercent = percent,
                    CurrentStep    = step,
                    ErrorMessage   = error,
                    CompletedAt    = completedAt,
                    TenantId=login.ClientId
                }, null, ct)
                .ConfigureAwait(false);
        }

        public async Task<MRPRunStatusDTO?> GetMRPRunStatus(int MRPRunId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _QueryExecutor
                .QueryAsync<MRPRunStatusDTO>(login, MRPRunQB.GET_MRPRUNSTATUS,
                    new
                    {
                        MrpRunId = MRPRunId,
                        TenantId = login.ClientId
                    })
                .ConfigureAwait(false);
            return rows?.FirstOrDefault();
        }

        // ── Bulk inserts ──────────────────────────────────────────────────

        public async Task BulkInsertMRPRunDetails(IEnumerable<MRPRunDetailInsertDTO> rows, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .BulkInsertAsync(login, MRPRunQB.BULK_INSERT_MRPRUNDETAILS, rows)
                .ConfigureAwait(false);
        }

        public async Task BulkInsertMRPActions(IEnumerable<MRPActionInsertDTO> rows, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .BulkInsertAsync(login, MRPRunQB.BULK_INSERT_MRPACTION, rows)
                .ConfigureAwait(false);
        }

        public async Task<Dictionary<int, int>> GetMRPActionIdsByRunAsync(int mrpRunId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _QueryExecutor
                .QueryAsync<(int MrpRunDetailId, int MrpActionId)>(
                    login, MRPRunQB.GET_MRPACTION_IDS_BY_RUN, new { MrpRunId = mrpRunId })
                .ConfigureAwait(false);
            return rows.ToDictionary(r => r.MrpRunDetailId, r => r.MrpActionId);
        }

        public async Task BulkInsertPegging(IEnumerable<MRPPeggingInsertDTO> rows, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .BulkInsertAsync(login, MRPRunQB.BULK_INSERT_PEGGING, rows)
                .ConfigureAwait(false);
        }

        public async Task UpdateMasterScheduleDetailMRPRunId(int mrpId,int mrpRunId, int masterScheduleId,int typeofrun, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.UPDATE_MASTERSCHEDULEDETAIL_MRPRUNID,
                    new { MrpId= mrpId, MrpRunId = mrpRunId, MasterScheduleId = masterScheduleId, TenantId = login.ClientId,OuId= login.WorkOUId,TypeOfRun= typeofrun }, null, ct)
                .ConfigureAwait(false);
        }

        public async Task InsertMRPRunDetailParts(int mrpRunId, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.INSERT_MRPRUNDETAILPART,
                    new { MrpRunId = mrpRunId, TenantId = login.ClientId }, null, ct)
                .ConfigureAwait(false);
        }

        public async Task InsertDependentDemandRows(int mrpRunId, int masterScheduleId, int levelCode, LoginDTO login, CancellationToken ct)
        {
            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.INSERT_MASTERSCHEDULEDETAIL_DEPENDENT_DEMAND,
                    new { MrpRunId = mrpRunId, MasterScheduleId = masterScheduleId,
                          UserId = login.UserId, TenantId = login.ClientId }, null, ct)
                .ConfigureAwait(false);

            await _QueryExecutor
                .ExecuteAsync(login, MRPRunQB.EXEC_UPDATE_MASTERSCHEDULEDETAIL_GROSS_REQUIREMENT,
                    new { MrpRunId = mrpRunId, LevelCode = levelCode }, null, ct)
                .ConfigureAwait(false);
        }

        private static string BuildDemandFilterSql(DemandFilterSpecDTO spec)
        {
            var sb = new StringBuilder();
            if (spec.RunScope == MrpRunScope.Allocation && spec.AllocationId.HasValue)
                sb.Append(" AND msd.ALLOCATIONID = @AllocationId");
            else if (spec.RunScope == MrpRunScope.SalesOrder && spec.SalesOrderId.HasValue)
                sb.Append(" AND msd.SALESORDERID = @SalesOrderId");
            else if (spec.RunScope == MrpRunScope.FGItem && spec.FGItemIds?.Count > 0)
                sb.Append(" AND msd.ITEMID IN @FGItemIds");

            if (spec.MaxPriorityToInclude.HasValue)
                sb.Append(" AND (msd.PRIORITY = 0 OR msd.PRIORITY <= @MaxPriorityToInclude)");

            return string.Format(MRPRunQB.GET_DEMAND_FOR_MRP_BASE, sb.ToString());
        }

        private static DynamicParameters BuildDemandFilterParams(int masterScheduleId,int TenantId, DemandFilterSpecDTO spec)
        {
            var p = new DynamicParameters();
            p.Add("MasterScheduleId", masterScheduleId);
            p.Add("TenantId", TenantId);

            if (spec.AllocationId.HasValue)   p.Add("AllocationId",         spec.AllocationId.Value);
            if (spec.SalesOrderId.HasValue)   p.Add("SalesOrderId",         spec.SalesOrderId.Value);
            if (spec.FGItemIds?.Count > 0)    p.Add("FGItemIds",            spec.FGItemIds);
            if (spec.MaxPriorityToInclude.HasValue) p.Add("MaxPriorityToInclude", spec.MaxPriorityToInclude.Value);

            return p;
        }

        private static int ParseInt(object? value) => value switch
        {
            null             => 0,
            JsonElement je   => je.ValueKind == JsonValueKind.Number ? je.GetInt32() : int.Parse(je.GetString()!),
            IConvertible ic  => Convert.ToInt32(ic),
            _                => int.Parse(value.ToString()!)
        };

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

        public async Task<MRPRunTraceDTO?> GetMRPRunTrace(int mrpRunId, LoginDTO login, CancellationToken ct)
        {
            var p = new { MrpRunId = mrpRunId, TenantId = login.ClientId };

            var header = await _QueryExecutor.QuerySingleAsync<MRPRunTraceDTO>(
                login, MRPRunQB.GET_TRACE_RUN_HEADER, p, cancellationToken: ct).ConfigureAwait(false);

            if (header is null) return null;

            var demandTask     = _QueryExecutor.QueryAsync<TraceDemandLineDTO>(login, MRPRunQB.GET_TRACE_DEMAND,      p, cancellationToken: ct);
            var runDetailTask  = _QueryExecutor.QueryAsync<TraceRunDetailDTO>( login, MRPRunQB.GET_TRACE_RUN_DETAILS, p, cancellationToken: ct);
            var actionsTask    = _QueryExecutor.QueryAsync<TraceActionRawDTO>( login, MRPRunQB.GET_TRACE_ACTIONS,     p, cancellationToken: ct);
            var peggingTask    = _QueryExecutor.QueryAsync<TracePeggingDTO>(   login, MRPRunQB.GET_TRACE_PEGGING,     p, cancellationToken: ct);
            var bomPartsTask   = _QueryExecutor.QueryAsync<TraceBomPartDTO>(   login, MRPRunQB.GET_TRACE_BOM_PARTS,   p, cancellationToken: ct);

            await Task.WhenAll(demandTask, runDetailTask, actionsTask, peggingTask, bomPartsTask).ConfigureAwait(false);

            header.Data = new TraceDataDTO
            {
                DemandLines = (await demandTask).ToList(),
                RunDetails  = (await runDetailTask).ToList(),
                Actions     = (await actionsTask).Select(a => new TraceActionDTO
                {
                    ActionId  = a.ActionId,
                    ItemCode  = a.ItemCode,
                    ItemName  = a.ItemName,
                    PlanType  = a.PlanType == 2 ? "Buy" : "Make",
                    Qty       = a.Qty,
                    OrderDate = a.OrderDate,
                    DueDate   = a.DueDate
                }).ToList(),
                Pegging  = (await peggingTask).ToList(),
                BomParts = (await bomPartsTask).ToList()
            };

            header.Steps  = BuildStepList(header.Status, header.Data);
            return header;
        }

        private static List<TraceStepDTO> BuildStepList(byte status, TraceDataDTO data)
        {
            var steps = new[]
            {
                ("create-status",           0),
                ("load-config",             0),
                ("validate-mps",            0),
                ("load-demand",             data.DemandLines.Count),
                ("load-stock",              0),
                ("load-receipts",           0),
                ("bom-explosion",           data.BomParts.Count),
                ("net-requirements",        data.RunDetails.Count),
                ("insert-run-details",      data.RunDetails.Count),
                ("insert-detail-parts",     data.BomParts.Count),
                ("insert-dependent-demand", 0),
                ("generate-actions",        data.Actions.Count),
                ("insert-actions",          data.Actions.Count),
                ("backfill-action-ids",     0),
                ("pegging",                 data.Pegging.Count),
                ("link-demand",             0),
                ("mark-complete",           0),
            };
            var stepStatus = status == 3 ? "failed" : "completed";
            return steps.Select(s => new TraceStepDTO
            {
                Id     = s.Item1,
                Status = stepStatus,
                Count  = s.Item2 > 0 ? s.Item2 : null
            }).ToList();
        }

        // Intermediate record: PLANTYPE comes from DB as byte; mapped to string in GetMRPRunTrace.
        private record TraceActionRawDTO(
            int ActionId, string ItemCode, string ItemName,
            byte PlanType, decimal Qty, DateTime OrderDate, DateTime DueDate);
    }
}