using FrameworkDAL.DTO.User;
using FrameworkDAL.Query.UserGroup;
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 FrameworkDAL.CustomCode.UserGroup
{
    public class UserGroupDAL : IUserGroupDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;


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

        public async Task<string> GetUserGroupOnUserId(int UserId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = UserGroupQB.GET_USERGROUP_ON_USERID;
                var parameters = new { userid = UserId };
                IEnumerable<UserGroupDTO> UserGroupDTOs = await _QueryExecutor.QueryAsync<UserGroupDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(UserGroupDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetUserGroup(int UserGroupId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = UserGroupQB.GET_USERGROUP;
                var parameters = new { usergroupid = UserGroupId };
                var UserGroupDict = new Dictionary<int, UserGroupDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<UserGroupDTO, UserGroupDetailDTO, UserGroupDTO>(LoginDTO,
                   sql,
                   (parent, child) =>
                   {
                       if (!UserGroupDict.TryGetValue(parent.UserGroupId, out var existingParent))
                       {
                           existingParent = parent;
                           existingParent.UserGroupDetailArray = new List<UserGroupDetailDTO>();
                           UserGroupDict[parent.UserGroupId] = existingParent;
                       }

                       if (child != null && child.UserGroupDetailId != 0)
                       {
                           existingParent.UserGroupDetailArray.Add(child);
                       }

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

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

        public async Task<int> SaveUserGroup(UserGroupDTO UserGroupDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string UserGroupSql = UserGroupQB.SAVE_USERGROUP;
                string detailSql = UserGroupQB.SAVE_USERGROUP_DETAIL;
                string countSql = UserGroupQB.GET_USERGROUP_COUNT;

                // DUPLICATE CHECK
                int existingCount = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql,
                    new
                    {
                        UserGroupDTO.UserGroupId
                    });

                if (existingCount > 0)
                {
                    throw new MethodNotAllowedException(
                        "Duplicate UserGroup record exists."
                    );
                }

                // TRANSACTION BUILD
                var statements = new List<(string sql, object param)>
        {
            (UserGroupSql, UserGroupDTO)
        };

                if (UserGroupDTO.UserGroupDetailArray?.Count > 0)
                {
                    foreach (var detail in UserGroupDTO.UserGroupDetailArray)
                    {
                        detail.UserGroupId = UserGroupDTO.UserGroupId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdateUserGroup(UserGroupDTO UserGroupDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((UserGroupQB.UPDATE_USERGROUP, UserGroupDTO));

            foreach (var d in UserGroupDTO.UserGroupDetailArray)
            {
                d.UserGroupId = UserGroupDTO.UserGroupId;
                if (d.UserGroupDetailId == 0)
                    statements.Add((UserGroupQB.SAVE_USERGROUP_DETAIL, d));
                else
                    statements.Add((UserGroupQB.UPDATE_USERGROUP_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return UserGroupDTO.UserGroupId;
        }

        public async Task<int> DeleteUserGroupDetail(int UserGroupId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    UserGroupQB.DELETE_USERGROUP_DETAIL,
                    new { UserGroupid = UserGroupId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting UserGroup detail for ID {UserGroupId}", ex);
            }
        }


        public async Task<int> DeleteUserGroup(int UserGroupId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    UserGroupQB.DELETE_USERGROUP,
                    new { UserGroupid = UserGroupId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting UserGroup with ID {UserGroupId}", ex);
            }
        }

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