using AdminDAL.DTO.IceData; 
using AdminDAL.Query.IceDataQB;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AdminDAL.CustomCode.IceData
{
    public class IceDataDAL : IIceDataDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public IceDataDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<string> GetIceDataFull(int IceDataId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = IceDataQB.GET_ICEDATA_Full;
                var parameters = new { icedataid = IceDataId };

                var IceDataDict = new Dictionary<int, IceDataDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<IceDataDTO, IceDataDetailDTO, IceDataDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!IceDataDict.TryGetValue(parent.IceDataId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.IceDataDetailArray = new List<IceDataDetailDTO>();
                            IceDataDict[parent.IceDataId] = existingParent;
                        }

                        if (child != null && child.IceDataDetailId != 0)
                        {
                            existingParent.IceDataDetailArray.Add(child);
                        }

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

                var finalResult = IceDataDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve IceData: {ex.Message}", ex);
            }
        }
        public async Task<IceDataDTO> GetIceData(int IceDataId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = IceDataQB.GET_ICEDATA;
                var parameters = new { icedataid = IceDataId };
                IceDataDTO IceDataDTOs = await _QueryExecutor.QuerySingleAsync<IceDataDTO>(LoginDTO, sql, parameters);
                return IceDataDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<IceDataDetailDTO> GetIceDataDetail(int IceDataDetailId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = IceDataQB.GET_ICEDATA_DETAIL;
                var parameters = new { icedatadetailid = IceDataDetailId };
                IceDataDetailDTO IceDataDetailDTOs = await _QueryExecutor.QuerySingleAsync<IceDataDetailDTO>(LoginDTO, sql, parameters);
                return IceDataDetailDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<int> SaveIceData(IceDataDTO IceDataDTO, LoginDTO login)
        {
            const string parentSql = IceDataQB.SAVE_ICEDATA;

            int newId = await _QueryExecutor.ExecuteScalarAsync<int>(login, parentSql, IceDataDTO);
            IceDataDTO.IceDataId = newId;

            if (IceDataDTO.IceDataDetailArray?.Count > 0)
            {
                var statements = new List<(string sql, object param)>();

                foreach (var d in IceDataDTO.IceDataDetailArray)
                {
                    d.IceDataId = newId;
                    statements.Add((IceDataQB.SAVE_ICE_DATA_DETAIL, d));
                }

                await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            }

            return newId;
        }
        public async Task<int> UpdateIceDataFull(IceDataDTO IceDataDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((IceDataQB.UPDATE_ICEDATA_Full, IceDataDTO));

            foreach (var d in IceDataDTO.IceDataDetailArray)
            {
                d.IceDataId = IceDataDTO.IceDataId;
                if (d.IceDataDetailId == 0)
                    statements.Add((IceDataQB.SAVE_ICE_DATA_DETAIL, d));
                else
                    statements.Add((IceDataQB.UPDATE_ICE_DATA_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return IceDataDTO.IceDataId;
        }
        public async Task<int> UpdateIceData(IceDataDTO IceDataDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = IceDataQB.UPDATE_ICEDATA;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, IceDataDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<int> DeleteIceDataDetail(int IceDataId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    IceDataQB.DELETE_ICE_DATA_DETAIL,
                    new { icedataid = IceDataId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting IceData detail for ID {IceDataId}", ex);
            }
        }
        public async Task<int> DeleteIceData(int IceDataId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    IceDataQB.DELETE_ICEDATA,
                    new { icedataid = IceDataId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting IceData with ID {IceDataId}", ex);
            }
        }
        public async Task<string> DeleteIceDataDetailById(int IceDataDetailId, LoginDTO LoginDTO)
        {
            try
            {
                int result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    IceDataQB.DELETE_ICE_DATA_DETAIL_BY_ID,
                    new { icedatadetailid = IceDataDetailId }
                );

                return result > 0
                    ? SuccessResponse.DeleteSuccessMessage
                    : ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting IceData Detail with ID {IceDataDetailId}", ex);
            }
        }
        public async Task<IEnumerable<IceDataDTO>> GetSelectListIceDataFull(CriteriaDTO criteriaDTO, LoginDTO LoginDTO)
        {
            var IceDataDict = new Dictionary<int, IceDataDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<IceDataDTO, IceDataDetailDTO, IceDataDTO>(
                LoginDTO,
                IceDataQB.GET_SELECTLIST_ICEDATA_Full,
                (parent, detail) =>
                {
                    if (!IceDataDict.TryGetValue(parent.IceDataId, out var current))
                    {
                        current = parent;
                        current.IceDataDetailArray = new List<IceDataDetailDTO>();
                        IceDataDict.Add(current.IceDataId, current);
                    }

                    if (detail != null &&
                        !current.IceDataDetailArray.Any(k => k.IceDataDetailId == detail.IceDataDetailId))
                    {
                        current.IceDataDetailArray.Add(detail);
                    }

                    return current;
                },
                null,
                splitOn: "ICEDATADETAILID"
            );

            return IceDataDict.Values.ToList();
        }
        public async Task<string> GetSelectListIceData(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = IceDataQB.GET_SELECTLIST_ICEDATA;
                var Result = await _QueryExecutor.QueryAsync<IceDataDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectListIceDataDetail(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = IceDataQB.GET_SELECTLIST_ICEDATA_DETAIL;
                var Result = await _QueryExecutor.QueryAsync<IceDataDetailDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}