using FrameworkDAL.DTO.Picklist;
using FrameworkDAL.Query.Picklist;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.Picklist
{
    public class PicklistDAL : IPicklistDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public PicklistDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        public async Task<PicklistDTO> GetPicklist(int PicklistId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = PicklistQB.GET_PICKLIST;
                var parameters = new { picklistid = PicklistId };

                var picklistDict = new Dictionary<int, PicklistDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<PicklistDTO, PicklistOutputDTO, PicklistDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!picklistDict.TryGetValue(parent.PicklistId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PicklistOutputDtos = new List<PicklistOutputDTO>();
                            picklistDict[parent.PicklistId] = existingParent;
                        }

                        if (child != null && child.PicklistOutputId != 0)
                        {
                            existingParent.PicklistOutputDtos.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "PicklistOutputId"
                );

                return picklistDict.Values.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Picklist data: {ex.Message}", ex);
            }
        }

        public async Task<string> GetSelectListPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PicklistQB.GET_SELECTLIST_PICKLIST;
                var Result = await _QueryExecutor.QueryAsync<PicklistPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}