using System.Data.Common;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.MRPRun;
using MMDAL.Query.MRPRun;

namespace MMDAL.CustomCode.MRPRun
{
    public class MRPActionDAL : IMRPActionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public MRPActionDAL(IQueryExecutor queryExecutor) => _QueryExecutor = queryExecutor;

        public async Task<MRPActionDTO?> GetMRPAction(
            int MRPActionId, LoginDTO loginDTO, CancellationToken ct)
            => await _QueryExecutor.QuerySingleAsync<MRPActionDTO>(
                   loginDTO, MRPActionQB.GET_MRPACTION,
                   new { mrpActionId = MRPActionId },
                   cancellationToken: ct).ConfigureAwait(false);

        public async Task<IEnumerable<MRPActionDTO>> GetMRPActionList(
            int MRPRunId, LoginDTO loginDTO, CancellationToken ct)
            => await _QueryExecutor.QueryAsync<MRPActionDTO>(
                   loginDTO, MRPActionQB.GET_MRPACTION_LIST,
                   new { MrpRunId = MRPRunId },
                   cancellationToken: ct).ConfigureAwait(false);

        public async Task<PagedResult<WorkBenchDTO>> GetWorkbench(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            // Generic CriteriaDTO in, same shape as GetSelectListSkill — GB4 parity
            // (MRPRun.svc.cs GetWorkBenchDetail): only MRPRunId is mandatory.
            var criteria = WorkBenchCriteriaDTO.FromCriteria(criteriaDTO);

            bool restrictToParents = criteria.OnlyParent
                                      && criteria.MakeOrBuy == 0
                                      && criteria.MasterScheduleId.HasValue;

            var (dataSql, countSql, p) = MRPActionQB.BuildWorkbench(
                criteria, loginDTO.ClientId, applyPaging: !restrictToParents);

            var items = (await _QueryExecutor.QueryAsync<WorkBenchDTO>(
                loginDTO, dataSql, p, cancellationToken: ct).ConfigureAwait(false)).ToList();

            int totalCount;
            if (restrictToParents)
            {
                var parentIds = (await _QueryExecutor.QueryAsync<int>(
                    loginDTO, MRPActionQB.GET_WORKBENCH_PARENT_ITEMS,
                    new
                    {
                        MasterScheduleId = criteria.MasterScheduleId!.Value,
                        MrpRunId         = criteria.MrpRunId,
                        TenantId         = loginDTO.ClientId
                    },
                    cancellationToken: ct).ConfigureAwait(false)).ToHashSet();

                items = items.Where(w => parentIds.Contains(w.PlanItemId)).ToList();
                foreach (var w in items) w.IsParentItem = true;

                totalCount = items.Count;

                var pageSize = criteria.PageSize > 0 ? criteria.PageSize : 50;
                items = items.Skip(criteria.PageOffset).Take(pageSize).ToList();
            }
            else
            {
                totalCount = await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO, countSql, p, cancellationToken: ct).ConfigureAwait(false);
            }

            await EnrichWorkbenchAsync(items, criteria.MrpRunId, loginDTO, ct).ConfigureAwait(false);

            return new PagedResult<WorkBenchDTO>
            {
                Items      = items,
                TotalCount = totalCount
            };
        }

        // GB4 parity (MRPRun.svc.cs GetWorkBenchDetail): joins in MaterialTotalCost from
        // MPRODUCTCOST and Stock/Purchase UOM names from MITEM, keyed by ItemId.
        private async Task EnrichWorkbenchAsync(
            List<WorkBenchDTO> items, int mrpRunId, LoginDTO loginDTO, CancellationToken ct)
        {
            if (items.Count == 0) return;

            var rates = await _QueryExecutor.QueryAsync<WorkbenchRateRow>(
                loginDTO, MRPActionQB.GET_WORKBENCH_RATE,
                new { OUId = loginDTO.WorkOUId, MrpRunId = mrpRunId, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            var rateByItem = rates.ToDictionary(r => r.ItemId, r => r.MaterialTotalCost);

            var uoms = await _QueryExecutor.QueryAsync<WorkbenchUomRow>(
                loginDTO, MRPActionQB.GET_WORKBENCH_UOM,
                new { MrpRunId = mrpRunId, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            var uomByItem = uoms.ToDictionary(u => u.ItemId);

            foreach (var w in items)
            {
                if (rateByItem.TryGetValue(w.PlanItemId, out var cost))
                    w.MaterialTotalCost = cost;

                if (uomByItem.TryGetValue(w.PlanItemId, out var uom))
                {
                    w.StockUOMId      = uom.StockUOMId;
                    w.StockUOMName    = uom.StockUOMName;
                    w.PurchaseUOMId   = uom.PurchaseUOMId;
                    w.PurchaseUOMName = uom.PurchaseUOMName;
                }
            }
        }

        private sealed class WorkbenchRateRow
        {
            public int     ItemId            { get; set; }
            public decimal MaterialTotalCost { get; set; }
        }

        private sealed class WorkbenchUomRow
        {
            public int    ItemId          { get; set; }
            public int    StockUOMId      { get; set; } = -1;
            public string StockUOMName    { get; set; } = string.Empty;
            public int    PurchaseUOMId   { get; set; } = -1;
            public string PurchaseUOMName { get; set; } = string.Empty;
        }

        public async Task<IEnumerable<WorkBenchDTO>> GetWorkbenchItemWise(
            WorkBenchCriteriaDTO criteria, LoginDTO loginDTO, CancellationToken ct)
        {
            var (dataSql, p) = MRPActionQB.BuildWorkbenchItemWise(criteria, loginDTO.ClientId);

            var items = (await _QueryExecutor.QueryAsync<WorkBenchDTO>(
                loginDTO, dataSql, p, cancellationToken: ct).ConfigureAwait(false)).ToList();

            if (criteria.OnlyParent && criteria.MakeOrBuy == 0 && criteria.MasterScheduleId.HasValue)
            {
                var parentIds = (await _QueryExecutor.QueryAsync<int>(
                    loginDTO, MRPActionQB.GET_WORKBENCH_PARENT_ITEMS,
                    new
                    {
                        MasterScheduleId = criteria.MasterScheduleId.Value,
                        MrpRunId         = criteria.MrpRunId,
                        TenantId         = loginDTO.ClientId
                    },
                    cancellationToken: ct).ConfigureAwait(false)).ToHashSet();

                items = items.Where(w => parentIds.Contains(w.PlanItemId)).ToList();
                foreach (var w in items) w.IsParentItem = true;
            }

            await EnrichWorkbenchAsync(items, criteria.MrpRunId, loginDTO, ct).ConfigureAwait(false);

            return items;
        }

        public async Task<Result<string>> SaveMRPAction(
            MRPActionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            dto.TenantId     = loginDTO.ClientId;
            dto.CreatedById = loginDTO.UserId;
            // Audit fields set by the caller via ExecuteSaveAsync pipeline (CreatedById/On etc)
            int rows = await _QueryExecutor.ExecuteAsync(
                loginDTO, MRPActionQB.SAVE_MRPACTION, dto, tx,
                cancellationToken: ct).ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("MRP Action saved.")
                : Result<string>.Failure("Failed to save MRP Action.");
        }

        public async Task<Result<string>> UpdateMRPAction(
            MRPActionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                loginDTO, MRPActionQB.UPDATE_MRPACTION, dto, tx,
                cancellationToken: ct).ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("MRP Action updated.")
                : Result<string>.Failure("MRP Action not found.");
        }

        public async Task<Result<string>> UpdateMRPActionRelease(
            MRPActionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                loginDTO, MRPActionQB.UPDATE_MRPACTION_RELEASE, dto, tx,
                cancellationToken: ct).ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("MRP Action release updated.")
                : Result<string>.Failure("MRP Action not found.");
        }
    }
}