using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using MMDAL.DTO.BOM;
using MMDAL.DTO.Item;
using MMDAL.DTO.Lot;
using MMDAL.DTO.Stock;
using MMDAL.DTO.Store;
using MMDAL.Query.BOM;
using MMDAL.Query.Lot;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using static NPOI.HSSF.Util.HSSFColor;
namespace MMDAL.CustomCode.BOM
{
    public class BOMDAL : IBOMDAL
    {
        private static readonly HashSet<string> ControlFieldNames = new(StringComparer.OrdinalIgnoreCase)
        {
            "Type", "LinkId", "LinkDetailId", "BOMProductionItemId", "InvSearch"
        };

        private readonly IQueryExecutor _QueryExecutor;

        public BOMDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }


        // GB4 parity: merges BOM.svc GetSelectListBOM (Type=0 Product/1 Order/2 Consumable)
        // and GetSelectListBOMNew (generic Code/Name search) into one endpoint.
        // Type/BOMProductionItemId/LinkId/LinkDetailId/InvSearch select WHICH query runs and
        // are read via CriteriaParser since they aren't simple WHERE-clause filters; everything
        // else (Name, Code, DefaultBOMId, ProductionItemId) flows through CriteriaDTO/{DYNAMIC_WHERE}.
        public async Task<string> GetSelectListBOM(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var Fields = GB5Shared.DTO.Framework.Criteria.CriteriaParser.Parse(CriteriaDTO);
                string Type = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetString(Fields, "Type", "0");
                string InvSearch = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetString(Fields, "InvSearch");
                int BOMProductionItemId = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(Fields, "BOMProductionItemId", -1);

                if (Type == "1")
                {
                    int LinkId = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(Fields, "LinkId", -1);
                    int LinkDetailId = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(Fields, "LinkDetailId", -1);

                    var Parameters = new
                    {
                        Type = 1,
                        LinkId,
                        LinkDetailId,
                        BOMProductionItemId
                    };
                    var OrderResult = await _QueryExecutor.QueryAsync<BOMPicklistDTO>(LoginDTO, BOMQB.GET_SELECTLISTBOM_SALESORDER, Parameters);
                    return JsonConvert.SerializeObject(OrderResult);
                }

                string ExtraWhere = " AND MB.TENANTID IN (-1, @TenantId)";
                if (Type == "2")
                {
                    ExtraWhere += " AND MB.BOMTYPE = 2";
                }
                if (BOMProductionItemId != -1)
                {
                    ExtraWhere += " AND MB.PRODUCTIONITEMID = @BOMProductionItemId";
                }
                if (!string.IsNullOrWhiteSpace(InvSearch))
                {
                    ExtraWhere += " AND (MB.BOMCODE LIKE @InvSearch OR MB.BOMNAME LIKE @InvSearch OR MB.VERSIONNAME LIKE @InvSearch)";
                }
                string SQL = BOMQB.GET_SELECTLISTBOM.Replace("{DYNAMIC_WHERE}", ExtraWhere + "\n{DYNAMIC_WHERE}");

                var QueryParams = new
                {
                    TenantId = LoginDTO.ClientId,
                    BOMProductionItemId,
                    InvSearch = $"%{InvSearch}%"
                };

                // Type/LinkId/LinkDetailId/BOMProductionItemId/InvSearch are control fields
                // already consumed above via CriteriaParser and applied explicitly as
                // ExtraWhere — they must not reach {DYNAMIC_WHERE} since they aren't real
                // MBOM column names (e.g. BOMProductionItemId -> PRODUCTIONITEMID).
                var DynamicWhereCriteriaDTO = new CriteriaDTO
                {
                    SectionCriteriaList = CriteriaDTO?.SectionCriteriaList?
                        .Select(section => new SectionCriteriaDTO
                        {
                            SectionId = section.SectionId,
                            OperationType = section.OperationType,
                            AttributesCriteriaList = section.AttributesCriteriaList?
                                .Where(attr => !ControlFieldNames.Contains(attr.FieldName))
                                .ToList()
                        })
                        .ToList()
                };

                string Json = await _QueryExecutor.QueryWithCriteriaJsonAsync<BOMPicklistDTO>(LoginDTO, SQL, QueryParams, DynamicWhereCriteriaDTO);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }


        public async Task<string> GetSelectListBOMDetailsForTransaction(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO LoginDTO,
    bool Count = false)
        {
            try
            {
                object GetValue(object val)
                {
                    if (val == null) return null;
                    if (val is JsonElement je)
                    {
                        return je.ValueKind switch
                        {
                            JsonValueKind.Number when je.TryGetInt32(out var i) => i,
                            JsonValueKind.Number when je.TryGetInt64(out var l) => l,
                            JsonValueKind.Number when je.TryGetDouble(out var d) => d,
                            JsonValueKind.String => je.GetString(),
                            JsonValueKind.True => true,
                            JsonValueKind.False => false,
                            _ => je.ToString()
                        };
                    }
                    return val;
                }

                // 1️⃣ Initialize parameters
                var paramDict = new Dictionary<string, object>
                {
                    ["FirstNumber"] = FirstNumber,
                    ["MaxResult"] = MaxResult,
                    ["StoreId"] = null!,
                    ["ItemId"] = null!,
                    ["SKUId"] = null!,
                    ["BOMDetailProcessId"] = null!
                };

                // 2️⃣ Apply CriteriaDTO
                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null) continue;
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (!string.IsNullOrWhiteSpace(attr.FieldName))
                                paramDict[attr.FieldName] = GetValue(attr.FieldValue);
                        }
                    }
                }

                // 3️⃣ Apply UserCriteriaDTO
                if (LoginDTO.UserCriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in LoginDTO.UserCriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null) continue;
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (!string.IsNullOrWhiteSpace(attr.FieldName))
                                paramDict[attr.FieldName] = GetValue(attr.FieldValue);
                        }
                    }
                }

                // 4️⃣ Count Mode
                if (Count)
                {
                    int total = await _QueryExecutor.TotalCountSql(BOMQB.GET_BOMDETAILS_FOR_TRANSACTION, LoginDTO, paramDict);
                    return JsonConvert.SerializeObject(new List<BOMDetailDTO> { new BOMDetailDTO { NumberOfRecords = total } });
                }

                // 5️⃣ Fetch BOM details
                var result = await _QueryExecutor.QueryAsync<BOMDetailDTO>(
                    LoginDTO,
                    BOMQB.GET_BOMDETAILS_FOR_TRANSACTION,
                    paramDict
                );

                // 6️⃣ Fetch stock if StoreId provided
                if (paramDict["StoreId"] is int storeId && storeId > 0 && result.Any())
                {
                    foreach (var bom in result)
                    {
                        var stockParams = new
                        {
                            StoreId = storeId,
                            ItemId = bom.BOMDetailItemId,
                            OUId = LoginDTO.WorkOUId,
                            SKUId = bom.SKUId
                        };

                        var stockResult = await _QueryExecutor.QueryAsync<StockPositionDTO>(
                            LoginDTO,
                            BOMQB.GET_STOCK_POSITION_QTY_FOR_BOM,
                            stockParams
                        );

                        if (stockResult.Any())
                            bom.StockQuantity = stockResult.First().StockPositionQuantity;
                    }
                }

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception("Error fetching BOM details", ex);
            }
        }
        public async Task<string> GetBOM(int BOMId, LoginDTO loginDTO)
        {
            try
            {
                string sql = BOMQB.GET_BOM;
                var parameters = new { BOMId = BOMId };

                var bomDict = new Dictionary<int, BOMDTO>();

                await _QueryExecutor.QueryMultiMapAsync<
                    BOMDTO,
                    BOMDetailDTO,
                    BOMDetailParameterDTO,
                    BOMDTO>
                (
                    loginDTO,
                    sql,
                    (bom, detail, parameter) =>
                    {
                        // Header
                        if (!bomDict.TryGetValue(bom.BOMId, out var existingBom))
                        {
                            existingBom = bom;
                            existingBom.BOMDetailArray = new List<BOMDetailDTO>();
                            bomDict.Add(existingBom.BOMId, existingBom);
                        }

                        // Detail
                        if (detail != null && detail.BOMDetailLineId != 0)
                        {
                            var existingDetail = existingBom.BOMDetailArray
                                .FirstOrDefault(x => x.BOMDetailLineId == detail.BOMDetailLineId);

                            if (existingDetail == null)
                            {
                                existingDetail = detail;
                                existingDetail.BOMDetailParameterArray =
                                    new List<BOMDetailParameterDTO>();

                                existingBom.BOMDetailArray.Add(existingDetail);
                            }

                            // Parameter
                            if (parameter != null &&
                                parameter.BOMDetailParameterId != 0)
                            {
                                if (!existingDetail.BOMDetailParameterArray.Any(x =>
                                    x.BOMDetailParameterId ==
                                    parameter.BOMDetailParameterId))
                                {
                                    existingDetail.BOMDetailParameterArray
                                        .Add(parameter);
                                }
                            }
                        }

                        return existingBom;
                    },
                    parameters,
                    splitOn: "BOMDetailLineId,BOMDetailParameterId"
                );

                var finalResult = bomDict.Values.FirstOrDefault();

                return JsonConvert.SerializeObject(finalResult, new JsonSerializerSettings
                {
                    NullValueHandling = NullValueHandling.Include,
                    DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                    Formatting = Formatting.None  // ✅ Changed to None
                });
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Failed to retrieve BOM data: {ex.Message}",
                    ex);
            }
        }

        // ── Header ───────────────────────────────────────────────

        public async Task SaveBOMAsync(BOMDTO dto, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.SAVE_BOM, dto);

        public async Task UpdateBOMAsync(BOMDTO dto, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.UPDATE_BOM, dto);
        // ── Versioning ───────────────────────────────────────────
        public async Task<BOMDTO?> GetPreviousVersionAsync(BOMDTO dto, LoginDTO login)
        {
            // BOMType 0 = Product, BOMType 1 = Order-linked
            string sql = dto.BOMType == 0
                ? BOMQB.GET_BOM_PREVIOUS_VERSION
                : BOMQB.GET_BOMLINKTYPE_PREVIOUS_VERSION;

            var results = await _QueryExecutor.QueryAsync<BOMDTO>(login, sql, new
            {
                dto.BOMProductionItemId,
                dto.DefaultBOMId,
                dto.BOMLinkId,
                dto.BOMFromDate
            });

            return results.FirstOrDefault();
        }

        public async Task UpdatePreviousVersionToDateAsync(BOMDTO dto, LoginDTO login)
        {
            string sql = dto.BOMType == 0
                ? BOMQB.UPDATE_TO_DATE_FOR_BOM
                : BOMQB.UPDATE_TO_DATE_FOR_BOMLINKTYPE;

            await _QueryExecutor.ExecuteAsync(login, sql, new
            {
                dto.BOMProductionItemId,
                dto.DefaultBOMId,
                dto.BOMLinkId,
                dto.BOMFromDate
            });
        }
        // ── Item Lookup ───────────────────────────────────────────

        public async Task<ItemDTO?> GetItemByIdAsync(int itemId, LoginDTO login)
        {
            var results = await _QueryExecutor.QueryAsync<ItemDTO>(login, BOMQB.GET_ITEM_BY_ID,
                new { ItemId = itemId });
            return results.FirstOrDefault();
        }

        // ── Detail ───────────────────────────────────────────────

        public async Task SaveBOMDetailAsync(BOMDetailDTO detail, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.SAVE_BOM_DETAIL, detail);

        public async Task UpdateBOMDetailAsync(BOMDetailDTO detail, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.UPDATE_BOM_DETAIL, detail);

        // ── Detail Parameter ─────────────────────────────────────

        public async Task SaveBOMDetailParameterAsync(BOMDetailParameterDTO param, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.SAVE_BOM_DETAIL_PARAMETER, param);

        public async Task UpdateBOMDetailParameterAsync(BOMDetailParameterDTO param, LoginDTO login)
            => await _QueryExecutor.ExecuteAsync(login, BOMQB.UPDATE_BOM_DETAIL_PARAMETER, param);
    


public async Task<string> DeleteBOM(int BOMId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = BOMQB.DELETE_BOM;
                var parameter = new { BOMid = BOMId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<BOMItemDetailDTO>> GetBOMTree(int bomId, decimal quantityForBom, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var param = new
            {
                BOMId = bomId,
                TenantId = loginDTO.ClientId,
                QuantityForBom = quantityForBom,
                MaxRecursion = 32
            };
            var result = await _QueryExecutor.QueryAsync<BOMItemDetailDTO>(loginDTO, BOMQB.GET_BOM_TREE, param, cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        public async Task<CyclicalBOMDTO?> CheckCyclicalBom(int productionItemId, IEnumerable<int> candidateItemIds, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var ids = candidateItemIds?.Where(id => id > 0 && id != productionItemId).Distinct().ToList() ?? new List<int>();
            if (ids.Count == 0) return null;

            var param = new
            {
                CandidateItemIds = string.Join(",", ids),
                ProductionItemId = productionItemId,
                TenantId = loginDTO.ClientId,
                MaxRecursion = 32
            };
            var result = await _QueryExecutor.QueryAsync<CyclicalBOMDTO>(loginDTO, BOMQB.GET_CYCLICAL_BOM_CHECK, param, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

    }
}