using FrameworkDAL.DTO.WebService;
using FrameworkDAL.Query.WebServiceSetting;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.WebServiceSetting
{
    public class WebServiceSettingDAL : IWebServiceSettingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public WebServiceSettingDAL(IQueryExecutor IQueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetWebServiceSetting(int webServiceSettingId, LoginDTO loginDTO)
        {
            try
            {
                var sql = WebServiceSettingQB.GET_WEBSERVICESETTING;
                var parameters = new { WebServiceSettingId = webServiceSettingId };
                var webServiceSettingDict = new Dictionary<int, WebServiceSettingDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<WebServiceSettingDTO, WebServiceSettingDetailDTO, WebServiceSettingDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        // Check if parent already exists in dictionary
                        if (!webServiceSettingDict.TryGetValue(parent.WebServiceSettingId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.WebServiceSettingDetailArray = new List<WebServiceSettingDetailDTO>();
                            webServiceSettingDict[parent.WebServiceSettingId] = existingParent;
                        }

                        // Add child only if it is valid (not null row from LEFT JOIN)
                        if (child != null && child.WebServiceSettingDetailId > 0)
                        {
                            existingParent.WebServiceSettingDetailArray.Add(child);
                        }

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

                var finalResult = webServiceSettingDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve WebServiceSetting data: {ex.Message}", ex);
            }
        }


        public async Task<int> SaveWebServiceSetting(WebServiceSettingDTO WebServiceSettingDTO, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO,
                WebServiceSettingQB.SAVE_WEBSERVICESETTING, WebServiceSettingDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateWebServiceSetting(WebServiceSettingDTO WebServiceSettingDTO, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO,
                WebServiceSettingQB.UPDATE_WEBSERVICESETTING, WebServiceSettingDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }


        public async Task<string> DeleteWebServiceSetting(int WebServiceSettingId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = WebServiceSettingQB.DELETE_WEBSERVICESETTING;
                var Parameters = new { webservicesettingid = WebServiceSettingId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> GetSelectListWebServiceSetting(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = WebServiceSettingQB.GET_SELECTLIST_WEBSERVICESETTING;
                SQL = ApplySelectListForWebServiceSetting(SQL, CriteriaDTO);
                SQL = ApplySelectListForWebServiceSetting(SQL, LoginDTO.UserCriteriaDTO);

                var Result = await _QueryExecutor.QueryAsync<WebServiceSettingPicklistDTO>(LoginDTO, SQL, null!);
                return JsonConvert.SerializeObject(Result);
            }
            catch
            {
                throw;
            }
        }
        private string ApplySelectListForWebServiceSetting(string SQL, CriteriaDTO CriteriaDTO)
        {
            if (CriteriaDTO == null)
                return SQL;

            // 🔹 Check if Id exists and append filter
            if (CriteriaDTO.Id > 0)
                SQL += $" AND A.WEBSERVICESETTINGID = {CriteriaDTO.Id} ";

            return SQL;
        }


    }
}