using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Ice;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace GB5Shared.IceMap
{
    public interface IIceMapDAL
    {
        Task<string> GetIceMap(int IceMapId, LoginDTO LoginDTO);

        Task<int> SaveIceMap(IceMapDTO IceMapDTO, LoginDTO LoginDTO);

        Task<int> UpdateIceMap(IceMapDTO IceMapDTO, LoginDTO LoginDTO);

        Task<Result<string>> DeleteIceMap(int IceMapId, LoginDTO LoginDTO);

        Task<string> GetSelectListIceMap(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO);
    }

    public class IceMapDAL : IIceMapDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetIceMap(int IceMapId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = IceMapQB.GET_ICEMAP;
                var parameters = new { icemapid = IceMapId };

                var IceMapDict = new Dictionary<int, IceMapDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<IceMapDTO, IceMapDetailsDTO, IceMapDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!IceMapDict.TryGetValue(parent.IceMapId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.IceMapDetailsArray = new List<IceMapDetailsDTO>();
                            IceMapDict[parent.IceMapId] = existingParent;
                        }

                        if (child != null && child.IceMapDetailsId != 0)
                        {
                            existingParent.IceMapDetailsArray.Add(child);
                        }

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

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

        public async Task<int> SaveIceMap(IceMapDTO IceMapDTO, LoginDTO loginDTO)
        {
            try
            {

                string IceMapSql = IceMapQB.SAVE_ICEMAP;
                string childSql = IceMapQB.SAVE_ICEMAPDETAILS;

                var statements = new List<(string sql, object param)>
                {
                    (IceMapSql, IceMapDTO)
                };

                if (IceMapDTO.IceMapDetailsArray != null && IceMapDTO.IceMapDetailsArray.Count > 0)
                {
                    foreach (var child in IceMapDTO.IceMapDetailsArray)
                    {
                        child.IceMapId = IceMapDTO.IceMapId;
                        statements.Add((childSql, child));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return IceMapDTO.IceMapId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateIceMap(IceMapDTO IceMapDTO, LoginDTO loginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                statements.Add((
                    IceMapQB.UPDATE_ICEMAP,
                    IceMapDTO
                ));

                var parameters = new { icemapid = IceMapDTO.IceMapId };
                var maxId = await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    IceMapQB.GET_MAX_ICEMAPDETAILSID,
                    parameters
                );

                maxId = maxId - 1;

                if (IceMapDTO.IceMapDetailsArray != null)
                {
                    foreach (var child in IceMapDTO.IceMapDetailsArray)
                    {
                        if (child.IceMapDetailsId == 0)
                        {
                            child.IceMapDetailsId = maxId--;
                            statements.Add((
                                IceMapQB.SAVE_ICEMAPDETAILS,
                                child
                            ));
                        }
                        else
                        {
                            statements.Add((
                                IceMapQB.UPDATE_ICEMAPDETAILS,
                                child
                            ));
                        }
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return IceMapDTO.IceMapId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.UpdateErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<Result<string>> DeleteIceMap(int IceMapId, LoginDTO LoginDTO)
        {
            try
            {
                string childsql = IceMapQB.DELETE_ICEMAPDETAILS;
                string parentSql = IceMapQB.DELETE_ICEMAP;
                var parameter = new { icemapid = IceMapId };
                var childresult = await _QueryExecutor.ExecuteAsync(LoginDTO, childsql, parameter);
                var parentresult = await _QueryExecutor.ExecuteAsync(LoginDTO, parentSql, parameter);

                if (parentresult > 0)
                {
                    return Result<string>.Success("IceMap Details Deleted Successfully.");
                }
                else
                {
                    return Result<string>.Failure("IceMap not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting IceMap Details", ex);
            }
        }

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