using BMA.EHR.Application.Common.Interfaces;
using BMA.EHR.Application.Messaging;
using BMA.EHR.Application.Responses.Leaves;
using BMA.EHR.Domain.Models.Leave.Commons;
using BMA.EHR.Domain.Models.Leave.Requests;
using BMA.EHR.Domain.Models.Notifications;
using BMA.EHR.Domain.Shared;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.IO.Compression;
using System.Net.Http.Headers;
using System.Net.Http.Json;

namespace BMA.EHR.Application.Repositories.Leaves.LeaveRequests
{
    public class LeaveRequestRepository : GenericLeaveRepository<Guid, LeaveRequest>
    {
        #region " Fields "

        private readonly ILeaveDbContext _dbContext;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly OrganizationCommonRepository _organizationCommonRepository;
        private readonly UserProfileRepository _userProfileRepository;
        private readonly IConfiguration _configuration;
        private readonly EmailSenderService _emailSenderService;

        private readonly IApplicationDBContext _appDbContext;
        private readonly MinIOLeaveService _minIOService;

        private readonly LeaveBeginningRepository _leaveBeginningRepository;

        private readonly string URL = string.Empty;

        #endregion

        #region " Constructor and Destuctor "

        public LeaveRequestRepository(ILeaveDbContext dbContext,
                                      IHttpContextAccessor httpContextAccessor,
                                      OrganizationCommonRepository organizationCommonRepository,
                                      UserProfileRepository userProfileRepository,
                                      IConfiguration configuration,
                                      EmailSenderService emailSenderService,
                                      IApplicationDBContext appDbContext,
                                      MinIOLeaveService minIOService,
                                      LeaveBeginningRepository leaveBeginningRepository) : base(dbContext, httpContextAccessor)
        {
            _dbContext = dbContext;
            _httpContextAccessor = httpContextAccessor;
            _organizationCommonRepository = organizationCommonRepository;
            _userProfileRepository = userProfileRepository;
            _configuration = configuration;
            _emailSenderService = emailSenderService;
            _appDbContext = appDbContext;

            URL = (_configuration["VITE_URL_MGT"]).Replace("/api/v1", "");
            Console.WriteLine($"URL : {URL}");
            _minIOService = minIOService;
            _leaveBeginningRepository = leaveBeginningRepository;
        }

        #endregion

        #region " Properties "

        protected Guid UserOrganizationId
        {
            get
            {
                if (UserId != null || UserId != "")
                    return _userProfileRepository.GetUserOCId(Guid.Parse(UserId!), AccessToken);
                else
                    return Guid.Empty;
            }
        }

        #endregion

        #region " Methods "

        #region " Overrides "

        public async Task<LeaveRequest?> GetByIdWithTrackingAsync(Guid id)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                                //.AsNoTracking()
                                .Include(x => x.Approvers)
                                .Include(x => x.LeaveDocument)
                                .ThenInclude(x => x.Document)
                                .Include(x => x.LeaveDraftDocument)
                                .Include(x => x.LeaveCancelDocument)
                                .Include(x => x.Type)
                                .FirstOrDefaultAsync(x => x.Id == id);

            return data;
        }

        public override async Task<LeaveRequest?> GetByIdAsync(Guid id)
        {
            try
            {
                var data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                                .AsNoTracking()
                                .Include(x => x.Approvers)
                                .Include(x => x.LeaveDocument)
                                .ThenInclude(x => x.Document)
                                .Include(x => x.LeaveDraftDocument)
                                .Include(x => x.LeaveCancelDocument)
                                .Include(x => x.Type)
                                .FirstOrDefaultAsync(x => x.Id == id);

                return data;
            }
            catch
            {
                throw;
            }

        }

        public override async Task<LeaveRequest> AddAsync(LeaveRequest entity)
        {
            if (entity.LeaveCancelDocument != null)
                _dbContext.Attatch(entity.LeaveCancelDocument);

            if (entity.LeaveDraftDocument != null)
                _dbContext.Attatch(entity.LeaveDraftDocument);

            if (entity.LeaveDocument != null)
            {
                foreach (var d in entity.LeaveDocument)
                {
                    _dbContext.Attatch(d);
                }

            }


            if (entity.Type != null)
                _dbContext.Attatch(entity.Type);

            return await base.AddAsync(entity);
        }

        public async Task<LeaveRequest> UpdateWithTrackingAsync(LeaveRequest entity)
        {
            // detach
            //_dbContext.Detach(entity);

            if (entity.LeaveCancelDocument != null)
                _dbContext.Attatch(entity.LeaveCancelDocument);

            if (entity.LeaveDraftDocument != null)
                _dbContext.Attatch(entity.LeaveDraftDocument);

            if (entity.LeaveDocument != null)
            {
                foreach (var d in entity.LeaveDocument)
                {
                    _dbContext.Attatch(d);
                }
            }

            if (entity.Type != null)
            {
                //_dbContext.Attatch(entity.Type);
                //_dbContext.Detach(entity.Type);
            }


            return await base.UpdateAsync(entity);
        }

        public override async Task<LeaveRequest> UpdateAsync(LeaveRequest entity)
        {
            // detach
            //_dbContext.Detach(entity);

            if (entity.LeaveCancelDocument != null)
                _dbContext.Attatch(entity.LeaveCancelDocument);

            if (entity.LeaveDraftDocument != null)
                _dbContext.Attatch(entity.LeaveDraftDocument);

            if (entity.LeaveDocument != null)
            {
                foreach (var d in entity.LeaveDocument)
                {
                    _dbContext.Attatch(d);
                }
            }

            if (entity.Type != null)
            {
                _dbContext.Attatch(entity.Type);
                //_dbContext.Detach(entity.Type);
            }


            return await base.UpdateAsync(entity);
        }


        #endregion

        public async Task AddLeaveDocumentAsync(Guid id, LeaveDocument doc)
        {
            try
            {
                var req = await _dbContext.Set<LeaveRequest>().FirstOrDefaultAsync(x => x.Id == id);
                req!.LeaveDocument.Add(doc);
                await _dbContext.SaveChangesAsync();
            }
            catch
            {
                throw;
            }
        }

        public async Task AddApproversAsync(Guid id, List<LeaveRequestApprover> approvers)
        {
            try
            {
                var req = await _dbContext.Set<LeaveRequest>().FirstOrDefaultAsync(x => x.Id == id);
                req!.Approvers.AddRange(approvers);
                await _dbContext.SaveChangesAsync();
            }
            catch
            {
                throw;
            }
        }

        public async Task RemoveApproversAsync(Guid id, string type)
        {
            try
            {
                var data = await _dbContext.Set<LeaveRequestApprover>()
                           .Where(x => x.LeaveRequest.Id == id && x.ApproveType.ToUpper() == type.ToUpper())
                           .ToListAsync();

                _dbContext.Set<LeaveRequestApprover>().RemoveRange(data);

                await _dbContext.SaveChangesAsync();
            }
            catch
            {
                throw;
            }
        }


        public async Task<List<LeaveRequest>> GetLeaveRequestByYearAsync(int year, Guid userId)
        {
            // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(userId, AccessToken);
            var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(userId, AccessToken);

            if (profile == null)
            {
                throw new Exception("ไม่พบข้อมูลผู้ใช้งานในระบบทะเบียนประวัติ!!");
            }

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.RootId == profile.RootId &&
                                        x.Child1Id == profile.Child1Id &&
                                        x.Child2Id == profile.Child2Id &&
                                        x.Child3Id == profile.Child3Id &&
                                        x.Child4Id == profile.Child4Id)
                            .Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStatus != "REJECT" && x.LeaveStatus != "DELETE")
                            .ToListAsync();

            return data;
        }

        public async Task<List<LeaveRequest>> GetLeaveRequestByYearForAdminAsync(int year, string role, string? nodeId, int? node)
        {
            var rawData =  _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.LeaveStatus != "REJECT" && x.LeaveStatus != "DELETE");
                            //.ToListAsync();
            if (year != 0)
            {
                var startFiscalDate = new DateTime(year - 1, 10, 1);
                var endFiscalDate = new DateTime(year, 9, 30);
                rawData = rawData.Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate);
            }
                    
            if (role == "OWNER")
            {
                node = null;
            }
            if (role == "OWNER" || role == "CHILD")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true))))));
            }
            else if (role == "BROTHER")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 1 || node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true)))));
            }
            else if (role == "ROOT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!));
            }
            else if (role == "PARENT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId != null);
            }
            else if (role == "NORMAL")
            {
                rawData = rawData
                    .Where(x =>
                        node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId == null :
                        node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) && x.Child2DnaId == null :
                        node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) && x.Child3DnaId == null :
                        node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) && x.Child4DnaId == null :
                        node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : true);
            }

            return await rawData.ToListAsync();
        }

        public async Task<List<LeaveRequest>> GetLeaveRequestByUserIdAsync(Guid keycloakUserId, int year, Guid type, string status)
        {
            var rawData = _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                                    .Include(x => x.Type)
                                    .Where(x => x.KeycloakUserId == keycloakUserId);

            if (year != 0)
            {
                var startFiscalDate = new DateTime(year - 1, 10, 1);
                var endFiscalDate = new DateTime(year, 9, 30);
                rawData = rawData.Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate);
            }
            //rawData = rawData.Where(x => x.LeaveStartDate.Year == year);

            if (type != Guid.Empty)
                rawData = rawData.Where(x => x.Type.Id == type);

            if (status.Trim().ToUpper() != "ALL")
                rawData = rawData.Where(x => x.LeaveStatus == status);

            return await rawData.ToListAsync();
        }

        public async Task<List<LeaveRequest>> GetLeaveRequestForAdminAsync(int year, Guid type, string status, DateTime startDate, DateTime endDate)
        {
            var rawData = _dbContext.Set<LeaveRequest>().AsNoTracking()
                                    .Include(x => x.Type)
                                    .Where(x => x.LeaveStatus != "DRAFT")
                                    .OrderByDescending(x => x.CreatedAt)
                                    .AsQueryable();

            if (year != 0)
                rawData = rawData.Where(x => x.LeaveStartDate.Year == year);

            if (type != Guid.Empty)
                rawData = rawData.Where(x => x.Type.Id == type);

            if (status.Trim().ToUpper() != "ALL")
                rawData = rawData.Where(x => x.LeaveStatus == status);

            if (startDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveStartDate >= startDate);

            if (endDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveEndDate <= endDate);

            return await rawData.ToListAsync();
        }

        public async Task<List<LeaveRequest>> GetListLeaveRequestForAdminAsync(int year, Guid type, string status, DateTime startDate, DateTime endDate, string role, string? nodeId, int? node)
        {
            var rawData = _dbContext.Set<LeaveRequest>().AsNoTracking()
                                    .Include(x => x.Type)
                                    .Where(x => x.LeaveStatus != "DRAFT")
                                    .OrderByDescending(x => x.CreatedAt)
                                    .AsQueryable();
            // fix issue : 1830
            if (year != 0)
            {
                var startFiscalDate = new DateTime(year - 1, 10, 1);
                var endFiscalDate = new DateTime(year, 9, 30);
                rawData = rawData.Where(x => x.LeaveStartDate >= startFiscalDate && x.LeaveStartDate <= endFiscalDate);
            }

            if (type != Guid.Empty)
                rawData = rawData.Where(x => x.Type.Id == type);

            if (status.Trim().ToUpper() != "ALL")
                rawData = rawData.Where(x => x.LeaveStatus == status);

            if (startDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveStartDate >= startDate);

            if (endDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveEndDate <= endDate);

            if (role == "OWNER")
            {
                node = null;
            }
            if (role == "OWNER" || role == "CHILD")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true))))));
            }
            else if (role == "BROTHER")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 1 || node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true)))));
            }
            else if (role == "ROOT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!));
            }
            else if (role == "PARENT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId != null);
            }
            else if (role == "NORMAL")
            {
                rawData = rawData
                    .Where(x =>
                        node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId == null :
                        node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) && x.Child2DnaId == null :
                        node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) && x.Child3DnaId == null :
                        node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) && x.Child4DnaId == null :
                        node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : true);
            }


            return await rawData.ToListAsync();
        }

        public async Task<List<LeaveRequest>> GetLeaveRequestForAdminWithAuthAsync(int year, Guid type, string status, DateTime startDate, DateTime endDate, List<Guid> keycloakIdList)
        {
            var rawData = _dbContext.Set<LeaveRequest>().AsNoTracking()
                                    .Include(x => x.Type)
                                    .Where(x => keycloakIdList.Contains(x.KeycloakUserId))
                                    .Where(x => x.LeaveStatus != "DRAFT")
                                    .OrderByDescending(x => x.CreatedAt)
                                    .AsQueryable();

            if (year != 0)
                rawData = rawData.Where(x => x.LeaveStartDate.Year == year);

            if (type != Guid.Empty)
                rawData = rawData.Where(x => x.Type.Id == type);

            if (status.Trim().ToUpper() != "ALL")
                rawData = rawData.Where(x => x.LeaveStatus == status);

            if (startDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveStartDate >= startDate);

            if (endDate != DateTime.MinValue)
                rawData = rawData.Where(x => x.LeaveEndDate <= endDate);

            return await rawData.ToListAsync();
        }

        public async Task<double> GetRestDayTotalByYearForUserAsync(Guid keycloakUserId, int year)
        {
            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);


            var leaveType = await _dbContext.Set<LeaveType>().AsQueryable().AsNoTracking()
                                        .FirstOrDefaultAsync(l => l.Code.Trim().ToUpper() == "LV-005");

            if (leaveType == null)
            {
                throw new Exception("ไม่พบข้อมูลประเภทการลาพักผ่อน โปรดติดต่อผู้ดูและระบบ");
            }

            var data = _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveType.Id)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .Sum(x => x.LeaveTotal);

            return data;
        }

        public async Task<double> GetSumLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        {
            // var pf = await _userProfileRepository.GetProfileByKeycloakIdAsync(keycloakUserId, AccessToken);
            var pf = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(keycloakUserId, AccessToken);
            if (pf == null)
                throw new Exception(GlobalMessages.DataNotFound);

            var beginningLeave = await _dbContext.Set<LeaveBeginning>().AsNoTracking()
                                    .Where(x => x.ProfileId == pf.Id)
                                    .Where(x => x.LeaveYear == year)
                                    .Where(x => x.LeaveTypeId == leaveTypeId)
                                    .FirstOrDefaultAsync();



            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            //.Where(x => x.LeaveStatus != "REJECT" && x.LeaveStatus != "DELETE")
                            .ToListAsync();

            return data.Sum(x => x.LeaveTotal) + (beginningLeave == null ? 0 : beginningLeave.LeaveDaysUsed);
        }

        //public async Task<double> GetSumApproveLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        //{
        //    var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
        //                    .Include(x => x.Type)
        //                    .Where(x => x.KeycloakUserId == keycloakUserId)
        //                    .Where(x => x.Type.Id == leaveTypeId)
        //                    .Where(x => x.LeaveStartDate.Year == year)
        //                    .Where(x => x.LeaveStatus == "APPROVE")
        //                    .ToListAsync();

        //    return data.Sum(x => x.LeaveTotal);
        //}

        public async Task<DateTime?> GetLeaveLastByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            //.Where(x => x.LeaveStatus != "REJECT" && x.LeaveStatus != "DELETE")
                            .OrderByDescending(x => x.LeaveStartDate.Date)
                            .Select(x => x.LeaveStartDate.Date)
                            .FirstOrDefaultAsync();

            return data;
        }

        public async Task<LeaveRequest?> GetLastLeaveRequestByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, DateTime beforeDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.LeaveStartDate.Date < beforeDate.Date)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            //.Where(x => x.LeaveStatus != "REJECT" && x.LeaveStatus != "DELETE")
                            .OrderByDescending(x => x.LeaveStartDate.Date)
                            .FirstOrDefaultAsync();

            return data;
        }

        public async Task<List<LeaveRequest>> GetCancelLeaveRequestForAdminAsync(int year, Guid type, string status, string role, string? nodeId, int? node)
        {
            var rawData = _dbContext.Set<LeaveRequest>().AsNoTracking()
                                    .Include(x => x.Type)
                                    .Where(x => x.LeaveStatus == "DELETE" || x.LeaveStatus == "DELETING")
                                    .AsQueryable();

            // if (year != 0)
            //     rawData = rawData.Where(x => x.LeaveStartDate.Year == year);
            if (year != 0)
            {
                var startFiscalDate = new DateTime(year - 1, 10, 1);
                var endFiscalDate = new DateTime(year, 9, 30);
                rawData = rawData.Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate);
            }

            if (type != Guid.Empty)
                rawData = rawData.Where(x => x.Type.Id == type);

            if (status.Trim().ToUpper() != "ALL")
                rawData = rawData.Where(x => x.LeaveCancelStatus == status);


            if (role == "OWNER")
            {
                node = null;
            }
            if (role == "OWNER" || role == "CHILD")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true))))));
            }
            else if (role == "BROTHER")
            {
                rawData = rawData
                    .Where(x => node == 4 ? x.Child3DnaId == Guid.Parse(nodeId!) : (node == 3 ? x.Child2DnaId == Guid.Parse(nodeId!) : (node == 2 ? x.Child1DnaId == Guid.Parse(nodeId!) : (node == 1 || node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) : (node == null ? true : true)))));
            }
            else if (role == "ROOT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!));
            }
            else if (role == "PARENT")
            {
                rawData = rawData
                    .Where(x => x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId != null);
            }
            else if (role == "NORMAL")
            {
                rawData = rawData
                    .Where(x =>
                        node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId == null :
                        node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) && x.Child2DnaId == null :
                        node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) && x.Child3DnaId == null :
                        node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) && x.Child4DnaId == null :
                        node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) : true);
            }

            return await rawData.ToListAsync();
        }

        public async Task<LeaveRequest> ApproveCancelLeaveRequestAsync(LeaveRequest data, string Reason, string LeaveReason)
        {
            try
            {
                // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(data.KeycloakUserId, AccessToken ?? "");
                var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(data.KeycloakUserId, AccessToken ?? "");
                if (profile == null)
                {
                    throw new Exception(GlobalMessages.DataNotFound);
                }


                // ปรับสถานะ จาก DELETING มาเป็น DELETE
                data.LeaveStatus = "DELETE";

                data.LeaveCancelStatus = "APPROVE";
                data.LeaveDirectorComment = Reason;
                data.LeaveCancelComment = LeaveReason;

                // TODO : Update ไปตาราง beginning
                if (data.ApproveStep == "st4") // ถ้ามีการอนุมัติจากผู้มีอำนาจแล้ว
                {

                    var toDay = data.LeaveStartDate.Date;
                    var thisYear = data.LeaveStartDate.Year;
                    if (toDay >= new DateTime(thisYear, 10, 1) && toDay <= new DateTime(thisYear, 12, 31))
                    {
                        thisYear = thisYear + 1;
                    }
                    await _leaveBeginningRepository.UpdateLeaveUsageAsync(thisYear, data.Type.Id, data.KeycloakUserId, -1 * data.LeaveTotal);

                    var _baseAPI = _configuration["API"];
                    var apiUrlSalary = $"{_baseAPI}/org/profile/leave/cancel/{data.Id}";
                    using (var client = new HttpClient())
                    {
                        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken.Replace("Bearer ", ""));
                        client.DefaultRequestHeaders.Add("api-key", _configuration["API_KEY"]);

                        var request = new HttpRequestMessage(HttpMethod.Patch, apiUrlSalary);

                        // ส่ง request
                        var _res = await client.SendAsync(request);
                        if (!_res.IsSuccessStatusCode)
                            throw new Exception("ไม่สามารถอัพเดตการยกเลิกรายการลาไปยังระบบทะเบียนประวัติ");
                        //var _result = await _res.Content.ReadAsStringAsync();
                    }
                }


                // Send Noti
                var noti = new Notification
                {
                    Body = $"การขอยกเลิกใบลาของคุณได้รับการอนุมัติ",
                    ReceiverUserId = profile.Id,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti);
                await _appDbContext.SaveChangesAsync();

                return data;
            }
            catch
            {
                throw;
            }

        }


        public async Task ApproveCancelLeaveRequestAsync(Guid id, string Reason)
        {
            var rawData = await GetByIdAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(rawData.KeycloakUserId, AccessToken ?? "");
            var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(rawData.KeycloakUserId, AccessToken ?? "");
            if (profile == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            rawData.LeaveStatus = "DELETE";

            rawData.LeaveCancelStatus = "APPROVE";
            rawData.LeaveDirectorComment = Reason;

            await UpdateWithTrackingAsync(rawData);

            // TODO : Update ไปตาราง beginning
            if (rawData.ApproveStep == "st4") // ถ้ามีการอนุมัติจากผู้มีอำนาจแล้ว
            {

                var toDay = rawData.LeaveStartDate.Date;
                var thisYear = rawData.LeaveStartDate.Year;
                if (toDay >= new DateTime(thisYear, 10, 1) && toDay <= new DateTime(thisYear, 12, 31))
                {
                    thisYear = thisYear + 1;
                }

                await _leaveBeginningRepository.UpdateLeaveUsageAsync(thisYear, rawData.Type.Id, rawData.KeycloakUserId, -1 * rawData.LeaveTotal);
                // update leave count ลดลง 1 ครั้ง
                await _leaveBeginningRepository.UpdateLeaveCountAsync(thisYear, rawData.Type.Id, rawData.KeycloakUserId, -1);

                var _baseAPI = _configuration["API"];
                var apiUrlSalary = $"{_baseAPI}/org/profile/leave/cancel/{rawData.Id}";
                using (var client = new HttpClient())
                {
                    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken.Replace("Bearer ", ""));
                    client.DefaultRequestHeaders.Add("api-key", _configuration["API_KEY"]);

                    var request = new HttpRequestMessage(HttpMethod.Patch, apiUrlSalary);

                    // ส่ง request
                    var _res = await client.SendAsync(request);
                    if (!_res.IsSuccessStatusCode)
                        throw new Exception("ไม่สามารถอัพเดตการยกเลิกรายการลาไปยังระบบทะเบียนประวัติ");
                    //var _result = await _res.Content.ReadAsStringAsync();
                }
            }

            // TODO: remove วันลา

            // Send Noti หาเจ้าของใบลา
            var noti = new Notification
            {
                Body = $"การขอยกเลิกใบลาของคุณได้รับการอนุมัติ",
                ReceiverUserId = profile.Id,
                Type = "",
                Payload = "",
            };
            _appDbContext.Set<Notification>().Add(noti);

            var commanders = rawData.Approvers
                                .Where(x => x.ApproveType!.ToUpper() == "COMMANDER")
                                .OrderBy(x => x.Seq)
                                .ToList();

            foreach (var commander in commanders)
            {
                var noti1 = new Notification
                {
                    Body = $"การขอยกเลิกใบลาของ {rawData.FirstName} {rawData.LastName} ได้รับการอนุมัติแล้ว",
                    ReceiverUserId = commander.ProfileId,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti1);
            }


            await _appDbContext.SaveChangesAsync();


        }

        public async Task RejectCancelLeaveRequestAsync(Guid id, string Reason)
        {
            var rawData = await GetByIdAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(rawData.KeycloakUserId, AccessToken ?? "");
            var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(rawData.KeycloakUserId, AccessToken ?? "");
            if (profile == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            rawData.LeaveCancelStatus = "REJECT";
            rawData.LeaveDirectorComment = Reason;

            // fix issue : ระบบลา (User) >> กรณีขอยกเลิกการลา แต่ผู้บังคับบัญชาไม่อนุมัติ (สถานะการลาไม่อัปเดต) #846
            if (rawData.ApproveStep == "st4")
            {
                rawData.LeaveStatus = "APPROVE";
            }
            else
            {
                rawData.LeaveStatus = "NEW";
            }

            await UpdateAsync(rawData);

            // fix issue : SIT ระบบบันทึกการลา>>รายการลา (กรณีขอยกเลิกการลา) #935
            // TODO: remove วันลา

            //var leaveType = await _appDbContext.Set<TypeLeave>()
            //                   .FirstOrDefaultAsync(x => x.Name == rawData.Type.Name);

            // insert to profile leave
            //var profileLeave = await _appDbContext.Set<ProfileLeave>()
            //                            .Where(x => x.TypeLeave.Id == leaveType.Id)
            //                            .Where(x => x.ProfileId == profile.Id)
            //                            .Where(x => x.DateStartLeave == rawData.LeaveStartDate && x.DateEndLeave == rawData.LeaveEndDate)
            //                            .FirstOrDefaultAsync();
            //if (profileLeave != null)
            //{
            //    _appDbContext.Set<ProfileLeave>().Remove(profileLeave);
            //    await _appDbContext.SaveChangesAsync();
            //}


            // Send Noti
            var noti = new Notification
            {
                Body = $"การขอยกเลิกใบลาของคุณไม่ได้รับการอนุมัติ \r\nเนืองจาก {Reason}",
                ReceiverUserId = profile.Id,
                Type = "",
                Payload = "",
            };
            _appDbContext.Set<Notification>().Add(noti);

            var commanders = rawData.Approvers
                                .Where(x => x.ApproveType!.ToUpper() == "COMMANDER")
                                .OrderBy(x => x.Seq)
                                .ToList();

            foreach (var commander in commanders)
            {
                var noti1 = new Notification
                {
                    Body = $"การขอยกเลิกใบลาของ {rawData.FirstName} {rawData.LastName} ไม่ได้รับการอนุมัติ \r\nเนืองจาก {Reason}",
                    ReceiverUserId = commander.ProfileId,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti1);
            }


            await _appDbContext.SaveChangesAsync();
        }

        public async Task SendToOfficerAsync(Guid id)
        {
            var rawData = await GetByIdAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            var isDuplicate = await CheckDuplicateLeave(rawData.KeycloakUserId, rawData.LeaveStartDate.Date, rawData.LeaveEndDate.Date, rawData.LeaveRange ?? "ALL");
            if (isDuplicate)
            {
                throw new Exception("ไม่สามารถขอลาในช่วงเวลาเดียวกันได้ เนื่องจากมีการขอลาในช่วงเวลาดังกล่าวแล้ว");
            }


            rawData.LeaveStatus = "NEW";
            //rawData.ApproveStep = "st2";

            await UpdateAsync(rawData);
        }

        public async Task OfficerApproveLeaveRequest(Guid id)
        {
            var rawData = await GetByIdAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            rawData.LeaveStatus = "PENDING";
            rawData.ApproveStep = "st2";

            await UpdateAsync(rawData);

            // TODO: Send notification to 1st Commander
            var firstCommander = rawData.Approvers
                            .Where(x => x.ApproveType!.ToUpper() == "COMMANDER")
                            .OrderBy(x => x.Seq)
                            .FirstOrDefault();

            // fix: If no commander, skip notification
            if (firstCommander != null)
            {
                // Send Notification
                var noti1 = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = firstCommander!.ProfileId,
                    Type = "",
                    Payload = $"{URL}/leave/detail/{id}",
                };
                _appDbContext.Set<Notification>().Add(noti1);
            }
            else
            {
                // มีแต่ approver อย่างเดียว 
                var firstApprover = rawData.Approvers
                            .Where(x => x.ApproveType!.ToUpper() == "APPROVER")
                            .OrderBy(x => x.Seq)
                            .FirstOrDefault();
                if(firstApprover != null)
                {
                    // Send Notification
                    var noti2 = new Notification
                    {
                        Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                        ReceiverUserId = firstApprover!.ProfileId,
                        Type = "",
                        Payload = $"{URL}/leave/detail/{id}",
                    };
                    _appDbContext.Set<Notification>().Add(noti2);
                }
            }
            await _appDbContext.SaveChangesAsync();
        }

        public async Task CommanderApproveLeaveRequest(Guid id, string reason)
        {
            // Get UserId from token
            var userId = UserId == null ? Guid.Empty : Guid.Parse(UserId);


            var rawData = await GetByIdWithTrackingAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            if (rawData.ApproveStep != "st2")
            {
                throw new Exception("คำขอนี้ยังไม่ได้อยู่ในขั้นตอนที่สามารถอนุมัติได้ ไม่สามารถทำรายการได้");
            }

            // check commander approve
            //var approvers = await _dbContext.Set<LeaveRequestApprover>()
            //                        //.AsNoTracking()
            //                        .Include(x => x.LeaveRequest)
            //                        .Where(x => x.LeaveRequest.Id == id && x.ApproveType == "COMMANDER")
            //                        .OrderBy(x => x.Seq)
            //                        .ToListAsync();

            var approvers = rawData.Approvers.Where(x => x.ApproveType!.ToUpper() == "COMMANDER").OrderBy(x => x.Seq).ToList();

            var approver = approvers.FirstOrDefault(x => x.KeycloakId == userId);
            if (approver == null)
            {
                throw new Exception("คุณไม่มีสิทธิ์อนุมัติการลาในขั้นตอนนี้");
            }

            // check prev approver มี action แล้วหรือไม่?
            var prevApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq - 1);

            if (prevApprover != null)
            {
                if (prevApprover.ApproveStatus == "PENDING")
                {
                    throw new Exception("ไม่สามารถทำการอนุมัติได้ เนื่องจากยังอยู่ระหว่างการพิจารณาโดยผู้บังคับบัญชารายก่อนหน้า");
                }
            }

            var maxSeq = approvers.Max(x => x.Seq);

            //var data = await _dbContext.Set<LeaveRequestApprover>()
            //                    .AsNoTracking()
            //                    .Include(x => x.LeaveRequest)
            //                    .Where(x => x.LeaveRequest.Id == id && x.KeycloakId == userId && x.ApproveType == "COMMANDER")
            //                    .FirstOrDefaultAsync();

            //if(data != null)
            //{
            //    data.ApproveStatus = "APPROVE";
            //    data.Comment = reason;

            //    data.LastUpdatedAt = DateTime.Now;
            //    data.LastUpdateUserId = userId.ToString("D");
            //    data.LastUpdateFullName = FullName ?? "";

            //    await _appDbContext.SaveChangesAsync();
            //}

            approver.ApproveStatus = "APPROVE";
            approver.Comment = reason;
            approver.LastUpdateFullName = FullName ?? "";
            approver.LastUpdateUserId = userId.ToString("D");
            approver.LastUpdatedAt = DateTime.Now;
            //await _dbContext.SaveChangesAsync();

            if (approver.Seq != maxSeq)
            {
                rawData.LeaveStatus = "PENDING";
                await UpdateWithTrackingAsync(rawData);

                var nextApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq + 1);

                // Send Noti
                var noti = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = nextApprover!.ProfileId,
                    Type = "",
                    Payload = $"{URL}/leave/detail/{id}",
                };
                _appDbContext.Set<Notification>().Add(noti);
                await _appDbContext.SaveChangesAsync();
            }
            else
            {
                rawData.LeaveStatus = "PENDING";
                rawData.LeaveComment = reason;
                rawData.ApproveStep = "st3";

                await UpdateWithTrackingAsync(rawData);

                // TODO: Send notification to 1st Approver
                var firstCommander = rawData.Approvers
                           .Where(x => x.ApproveType!.ToUpper() == "APPROVER")
                           .OrderBy(x => x.Seq)
                           .FirstOrDefault();

                // Send Notification
                var noti1 = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = firstCommander!.ProfileId,
                    Type = "",
                    Payload = $"{URL}/leave/detail/{id}",
                };
                _appDbContext.Set<Notification>().Add(noti1);
                await _appDbContext.SaveChangesAsync();
            }
        }

        public async Task CommanderRejectLeaveRequest(Guid id, string reason)
        {
            // Get UserId from token
            var userId = UserId == null ? Guid.Empty : Guid.Parse(UserId);


            var rawData = await GetByIdWithTrackingAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            if (rawData.ApproveStep != "st2")
            {
                throw new Exception("คำขอนี้ยังไม่ได้อยู่ในขั้นตอนที่สามารถอนุมัติได้ ไม่สามารถทำรายการได้");
            }

            // check commander approve
            var approvers = rawData.Approvers.Where(x => x.ApproveType!.ToUpper() == "COMMANDER").OrderBy(x => x.Seq).ToList();

            var approver = approvers.FirstOrDefault(x => x.KeycloakId == userId);
            if (approver == null)
            {
                throw new Exception("คุณไม่มีสิทธิ์อนุมัติการลาในขั้นตอนนี้");
            }

            // check prev approver มี action แล้วหรือไม่?
            var prevApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq - 1);

            if (prevApprover != null)
            {
                if (prevApprover.ApproveStatus == "PENDING")
                {
                    throw new Exception("ไม่สามารถทำการอนุมัติได้ เนื่องจากยังอยู่ระหว่างการพิจารณาโดยผู้บังคับบัญชารายก่อนหน้า");
                }
            }


            var maxSeq = approvers.Max(x => x.Seq);

            approver.ApproveStatus = "REJECT";
            approver.Comment = reason;
            approver.LastUpdateFullName = FullName ?? "";
            approver.LastUpdateUserId = userId.ToString("D");
            approver.LastUpdatedAt = DateTime.Now;
            if (approver.Seq != maxSeq)
            {


                var nextApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq + 1);
                // Send Noti
                var noti = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = nextApprover!.ProfileId,
                    Type = "",
                    Payload = $"{URL}/leave/detail/{id}",
                };
                _appDbContext.Set<Notification>().Add(noti);
                await _appDbContext.SaveChangesAsync();

                rawData.LeaveStatus = "PENDING";
                await UpdateWithTrackingAsync(rawData);
            }
            else
            {
                rawData.LeaveStatus = "PENDING";
                rawData.LeaveComment = reason;
                rawData.ApproveStep = "st3";

                await UpdateWithTrackingAsync(rawData);

                // TODO: Send notification to 1st Approver
                var firstCommander = rawData.Approvers
                           .Where(x => x.ApproveType!.ToUpper() == "APPROVER")
                           .OrderBy(x => x.Seq)
                           .FirstOrDefault();
                // Send Notification
                var noti1 = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = firstCommander!.ProfileId,
                    Type = "",
                    Payload = $"{URL}/leave/detail/{id}",
                };
                _appDbContext.Set<Notification>().Add(noti1);
                await _appDbContext.SaveChangesAsync();
            }

        }

        public async Task ApproveLeaveRequest(Guid id, string reason)
        {
            // Get UserId from token
            var userId = UserId == null ? Guid.Empty : Guid.Parse(UserId);

            var rawData = await GetByIdWithTrackingAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            // ถ้าไม่มี commander ข้ามไปเช็ค approver ได้เลย
            var commanders = rawData.Approvers
                                .Where(x => x.ApproveType!.ToUpper() == "COMMANDER")
                                .OrderBy(x => x.Seq)
                                .ToList();
            if (commanders.Count > 0 && commanders != null)
            {
                if (rawData.ApproveStep != "st3")
                {
                    throw new Exception("คำขอนี้ยังไม่ได้อยู่ในขั้นตอนที่สามารถอนุมัติได้ ไม่สามารถทำรายการได้");
                }
            }

            // check commander approve
            var approvers = rawData.Approvers.Where(x => x.ApproveType!.ToUpper() == "APPROVER").OrderBy(x => x.Seq).ToList();

            var approver = approvers.FirstOrDefault(x => x.KeycloakId == userId);
            if (approver == null)
            {
                throw new Exception("คุณไม่มีสิทธิ์อนุมัติการลาในขั้นตอนนี้");
            }

            // check prev approver มี action แล้วหรือไม่?
            var prevApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq - 1);

            if (prevApprover != null)
            {
                if (prevApprover.ApproveStatus == "PENDING")
                {
                    throw new Exception("ไม่สามารถทำการอนุมัติได้ เนื่องจากยังอยู่ระหว่างการพิจารณาโดยผู้บังคับบัญชารายก่อนหน้า");
                }
            }


            var maxSeq = approvers.Max(x => x.Seq);

            approver.ApproveStatus = "APPROVE";
            approver.Comment = reason;
            approver.LastUpdateFullName = FullName ?? "";
            approver.LastUpdateUserId = userId.ToString("D");
            approver.LastUpdatedAt = DateTime.Now;
            if (approver.Seq != maxSeq)
            {
                var nextApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq + 1);
                // Send Noti
                var noti1 = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = nextApprover!.ProfileId,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti1);
                await _appDbContext.SaveChangesAsync();

                await UpdateWithTrackingAsync(rawData);
            }
            else
            {
                // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(rawData.KeycloakUserId, AccessToken);
                var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(rawData.KeycloakUserId, AccessToken);
                if (profile == null)
                {
                    throw new Exception(GlobalMessages.DataNotFound);
                }

                rawData.LeaveStatus = "APPROVE";
                rawData.LeaveDirectorComment = reason;
                rawData.ApproveStep = "st4";

                await UpdateWithTrackingAsync(rawData);

                var toDay = rawData.LeaveStartDate.Date;
                var thisYear = rawData.LeaveStartDate.Year;
                if (toDay >= new DateTime(thisYear, 10, 1) && toDay <= new DateTime(thisYear, 12, 31))
                {
                    thisYear = thisYear + 1;
                }

                // TODO : Update ไปตาราง beginning
                await _leaveBeginningRepository.UpdateLeaveUsageAsync(thisYear, rawData.Type.Id, rawData.KeycloakUserId, rawData.LeaveTotal);
                // update leave count เพิ่มขึ้น 1 ครั้ง
                await _leaveBeginningRepository.UpdateLeaveCountAsync(thisYear, rawData.Type.Id, rawData.KeycloakUserId, 1);


                var _baseAPI = _configuration["API"];
                var apiUrlSalary = string.Empty;
                if (profile.ProfileType == "OFFICER")
                {
                    apiUrlSalary = $"{_baseAPI}/org/profile/leave";
                    using (var client = new HttpClient())
                    {
                        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken.Replace("Bearer ", ""));
                        client.DefaultRequestHeaders.Add("api-key", _configuration["API_KEY"]);
                        var _res = await client.PostAsJsonAsync(apiUrlSalary, new
                        {
                            profileId = profile.Id,
                            leaveTypeId = rawData?.Type?.Id ?? null,
                            dateLeaveStart = rawData.LeaveStartDate,
                            dateLeaveEnd = rawData.LeaveEndDate,
                            totalLeave = 0,//หน้า fe ไม่ได้ใช้
                            leaveCount = 0,//หน้า fe ไม่ได้ใช้
                            leaveDays = rawData.LeaveTotal,
                            status = "approve",
                            reason = rawData.LeaveDetail,
                            leaveId = rawData.Id,
                        });
                        // var _result = await _res.Content.ReadAsStringAsync();
                    }
                }
                else if (profile.ProfileType == "EMPLOYEE")
                {
                    apiUrlSalary = $"{_baseAPI}/org/profile-employee/leave";
                    using (var client = new HttpClient())
                    {
                        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken.Replace("Bearer ", ""));
                        client.DefaultRequestHeaders.Add("api-key", _configuration["API_KEY"]);
                        var _res = await client.PostAsJsonAsync(apiUrlSalary, new
                        {
                            profileEmployeeId = profile.Id,
                            leaveTypeId = rawData?.Type?.Id ?? null,
                            dateLeaveStart = rawData.LeaveStartDate,
                            dateLeaveEnd = rawData.LeaveEndDate,
                            totalLeave = 0,
                            leaveCount = 0,
                            leaveDays = rawData.LeaveTotal,
                            status = "approve",
                            reason = rawData.LeaveDetail,
                            leaveId = rawData.Id,
                        });
                    }
                }
                else
                {
                    throw new Exception("ไม่สามารถทำรายการได้");
                }
                await _appDbContext.SaveChangesAsync();

                // insert to process timestamp


                // Send Noti
                var noti = new Notification
                {
                    Body = $"การขอลาของคุณได้รับการอนุมัติ",
                    ReceiverUserId = profile.Id,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti);
                await _appDbContext.SaveChangesAsync();
            }

        }

        public async Task RejectLeaveRequest(Guid id, string reason)
        {
            // Get UserId from token
            var userId = UserId == null ? Guid.Empty : Guid.Parse(UserId);

            var rawData = await GetByIdWithTrackingAsync(id);
            if (rawData == null)
            {
                throw new Exception(GlobalMessages.DataNotFound);
            }

            // ถ้าไม่มี commander ข้ามไปเช็ค approver ได้เลย
            var commanders = rawData.Approvers
                                .Where(x => x.ApproveType!.ToUpper() == "COMMANDER")
                                .OrderBy(x => x.Seq)
                                .ToList();
            if (commanders.Count > 0 && commanders != null)
            {
                if (rawData.ApproveStep != "st3")
                {
                    throw new Exception("คำขอนี้ยังไม่ได้อยู่ในขั้นตอนที่สามารถอนุมัติได้ ไม่สามารถทำรายการได้");
                }
            }

            // check commander approve
            var approvers = rawData.Approvers.Where(x => x.ApproveType!.ToUpper() == "APPROVER").OrderBy(x => x.Seq).ToList();

            var approver = approvers.FirstOrDefault(x => x.KeycloakId == userId);
            if (approver == null)
            {
                throw new Exception("คุณไม่มีสิทธิ์อนุมัติการลาในขั้นตอนนี้");
            }

            // check prev approver มี action แล้วหรือไม่?
            var prevApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq - 1);

            if (prevApprover != null)
            {
                if (prevApprover.ApproveStatus == "PENDING")
                {
                    throw new Exception("ไม่สามารถทำการอนุมัติได้ เนื่องจากยังอยู่ระหว่างการพิจารณาโดยผู้บังคับบัญชารายก่อนหน้า");
                }
            }


            var maxSeq = approvers.Max(x => x.Seq);

            approver.ApproveStatus = "REJECT";
            approver.Comment = reason;
            approver.LastUpdateFullName = FullName ?? "";
            approver.LastUpdateUserId = userId.ToString("D");
            approver.LastUpdatedAt = DateTime.Now;
            if (approver.Seq != maxSeq)
            {
                var nextApprover = approvers.FirstOrDefault(x => x.Seq == approver.Seq + 1);
                // Send Noti
                var noti1 = new Notification
                {
                    Body = $"การขอลาของคุณ {rawData.FirstName} {rawData.LastName} รอรับการอนุมัติจากคุณ",
                    ReceiverUserId = nextApprover!.ProfileId,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti1);
                await _appDbContext.SaveChangesAsync();

                await UpdateWithTrackingAsync(rawData);
            }
            else
            {
                // var profile = await _userProfileRepository.GetProfileByKeycloakIdAsync(rawData.KeycloakUserId, AccessToken);
                var profile = await _userProfileRepository.GetProfileByKeycloakIdNewAsync(rawData.KeycloakUserId, AccessToken);
                if (profile == null)
                {
                    throw new Exception(GlobalMessages.DataNotFound);
                }

                rawData.LeaveStatus = "REJECT";
                rawData.LeaveDirectorComment = reason;
                rawData.ApproveStep = "st5";

                await UpdateWithTrackingAsync(rawData);

                // Send Noti
                var noti = new Notification
                {
                    Body = $"การขอลาของคุณไม่ได้รับการอนุมัติ \r\nเนื่องจาก{reason}",
                    ReceiverUserId = profile.Id,
                    Type = "",
                    Payload = "",
                };
                _appDbContext.Set<Notification>().Add(noti);
                await _appDbContext.SaveChangesAsync();
            }
        }

        public async Task<List<GetSumApproveLeaveByTypeDto>> GetSumSendLeaveAsync(int year)
        {
            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate)
                            //.Where(x => x.LeaveStatus == "NEW") // fix issue : #729
                            .Where(x => x.LeaveStatus != "DRAFT") // fix issue : #1524
                            .ToListAsync();

            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetSumApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           SumLeaveDay = grp.Sum(x => x.LeaveTotal)
                       })
                      .ToList();

            return res;
        }

        public async Task<List<GetSumApproveLeaveByTypeDto>> GetSumApproveLeaveAsync(int year)
        {
            // Get จาก LeaveBeginning
            var beginning = await _dbContext.Set<LeaveBeginning>().AsNoTracking()
                                .Include(x => x.LeaveType)
                                .Where(x => x.LeaveYear == year)

                                .ToListAsync();

            var beginningData = new List<GetSumApproveLeaveByTypeDto>();

            foreach (var b in beginning)
            {
                var pf = await _userProfileRepository.GetProfileByProfileIdAsync(b.ProfileId, AccessToken);
                if (pf != null)
                {
                    beginningData.Add(new GetSumApproveLeaveByTypeDto
                    {
                        KeycloakUserId = pf.Keycloak == null ? Guid.Empty : pf.Keycloak.Value,
                        LeaveTypeId = b.LeaveTypeId,
                        LeaveTypeCode = b.LeaveType!.Code,
                        SumLeaveDay = b.LeaveDaysUsed
                    });
                }
            }


            // fix issue : #729
            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate) // fix issue : #729
                            .Where(x => x.LeaveStatus == "APPROVE")
                            .ToListAsync();



            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetSumApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           SumLeaveDay = grp.Sum(x => x.LeaveTotal)
                       })
                      .ToList();


            var resUnion = (from d in res.Union(beginningData)
                            group d by new { d.KeycloakUserId, d.LeaveTypeId, d.LeaveTypeCode } into grp
                            select new GetSumApproveLeaveByTypeDto
                            {
                                KeycloakUserId = grp.Key.KeycloakUserId,
                                LeaveTypeId = grp.Key.LeaveTypeId,
                                LeaveTypeCode = grp.Key.LeaveTypeCode,
                                SumLeaveDay = grp.Sum(x => x.SumLeaveDay)
                            })
                      .ToList();

            return resUnion;
        }

        public async Task<List<GetSumApproveLeaveByTypeDto>> GetSumRejectLeaveAsync(int year)
        {
            // fix issue : #729
            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate) // fix issue : #729
                            .Where(x => x.LeaveStatus == "REJECT")
                            .ToListAsync();

            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetSumApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           SumLeaveDay = grp.Sum(x => x.LeaveTotal)
                       })
                      .ToList();

            return res;
        }

        public async Task<List<GetSumApproveLeaveByTypeDto>> GetSumDeleteLeaveAsync(int year)
        {
            // fix issue : #729
            var startFiscalDate = new DateTime(year - 1, 10, 1);
            var endFiscalDate = new DateTime(year, 9, 30);

            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            //.Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStartDate.Date >= startFiscalDate && x.LeaveStartDate.Date <= endFiscalDate) // fix issue : #729
                            .Where(x => x.LeaveStatus == "DELETE" && x.LeaveCancelStatus == "APPROVE")
                            .ToListAsync();

            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetSumApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           SumLeaveDay = grp.Sum(x => x.LeaveTotal)
                       })
                      .ToList();

            return res;
        }

        public async Task<double> GetSumSendLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Year == year)
                            .ToListAsync();

            return data.Sum(x => x.LeaveTotal);
        }

        public async Task<double> GetSumApproveLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0.0;
        }

        public async Task<double> GetSumApproveLeaveByTypeAndRangeForUser(Guid keycloakUserId, Guid leaveTypeId, DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0;
        }

        public async Task<double> GetSumApproveLeaveByRangeForUser(Guid keycloakUserId, DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0;
        }

        public async Task<List<GetSumApproveLeaveByTypeDto>> GetSumApproveLeaveByTypeAndRange(DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                            .Include(x => x.Type)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING").ToListAsync();

            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetSumApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           SumLeaveDay = grp.Sum(x => x.LeaveTotal),
                           CountLeaveDay = grp.Count()
                       })
                      .ToList();

            return res;
        }

        public async Task<List<GetSumApproveLeaveByRootDto>> GetSumApproveLeaveByRootAndRange(DateTime startDate, DateTime endDate, string type, string role, string? nodeId, int? node, string? nodeIdByReq, int? nodeByReq)
        {
            var data = new List<LeaveRequest>();
            data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                        .Include(x => x.Type)
                        .Where(x => x.ProfileType == type.Trim().ToUpper())
                        .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                        .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING").ToListAsync();
            // กรองตามสิทธิ์ admin ก่อน
            if (role == "CHILD")
            {
                data = data.Where(x =>
                    node == 4 ? x.Child4DnaId == Guid.Parse(nodeId) :
                    node == 3 ? x.Child3DnaId == Guid.Parse(nodeId) :
                    node == 2 ? x.Child2DnaId == Guid.Parse(nodeId) :
                    node == 1 ? x.Child1DnaId == Guid.Parse(nodeId) :
                    node == 0 ? x.RootDnaId == Guid.Parse(nodeId) :
                    node == null ? true : true
                ).ToList();
            }
            else if (role == "BROTHER")
            {
                data = data.Where(x =>
                    node == 4 ? x.Child3DnaId == Guid.Parse(nodeId) :
                    node == 3 ? x.Child2DnaId == Guid.Parse(nodeId) :
                    node == 2 ? x.Child1DnaId == Guid.Parse(nodeId) :
                    node == 1 || node == 0 ? x.RootDnaId == Guid.Parse(nodeId) :
                    node == null ? true : true
                ).ToList();
            }
            else if (role == "ROOT")
            {
                data = data.Where(x => x.RootDnaId == Guid.Parse(nodeId)).ToList();
            }
            else if (role == "PARENT")
            {
                data = data.Where(x => x.RootDnaId == Guid.Parse(nodeId) && x.Child1DnaId != null).ToList();
            }
            else if (role == "NORMAL")
            {
                data = data.Where(x =>
                    node == 0 ? x.RootDnaId == Guid.Parse(nodeId!) && x.Child1DnaId == null :
                    node == 1 ? x.Child1DnaId == Guid.Parse(nodeId!) && x.Child2DnaId == null :
                    node == 2 ? x.Child2DnaId == Guid.Parse(nodeId!) && x.Child3DnaId == null :
                    node == 3 ? x.Child3DnaId == Guid.Parse(nodeId!) && x.Child4DnaId == null :
                    node == 4 ? x.Child4DnaId == Guid.Parse(nodeId!) :
                    true
                ).ToList();
            }
            // กรองตามที่ fe ส่งมา
            if (role == "ROOT" || role == "OWNER" || role == "CHILD" || role == "BROTHER" || role == "PARENT")
            {
                data = data
                    .Where(x => nodeByReq == 4 ? x.Child4Id == Guid.Parse(nodeIdByReq) : nodeByReq == 3 ? x.Child3Id == Guid.Parse(nodeIdByReq) : nodeByReq == 2 ? x.Child2Id == Guid.Parse(nodeIdByReq) : nodeByReq == 1 ? x.Child1Id == Guid.Parse(nodeIdByReq) : nodeByReq == 0 ? x.RootId == Guid.Parse(nodeIdByReq) : true)
                    .ToList();
            }
            // รายงานการลางานจำแนกตามเพศฯ Template ให้หน่วยงานแสดงก่อนส่วนราชการ
            var org = _userProfileRepository.GetOc(Guid.Parse(nodeIdByReq), nodeByReq ?? 0, AccessToken);
            var organizationName = $"{org.Root ?? ""}{(!string.IsNullOrEmpty(org.Child1) ? "/" + org.Child1 : "")}{(!string.IsNullOrEmpty(org.Child2) ? "/" + org.Child2 : "")}{(!string.IsNullOrEmpty(org.Child3) ? "/" + org.Child3 : "")}{(!string.IsNullOrEmpty(org.Child4) ? "/" + org.Child4 : "")}";
            if (data.Count > 0)
            {
                var grouped = data.GroupBy(d => nodeByReq switch
                {
                    0 => d.Root,
                    1 => d.Child1,
                    2 => d.Child2,
                    3 => d.Child3,
                    4 => d.Child4,
                    _ => d.Root
                });
                var res = (/*from d in data
                           group d by new { d.Root, d.Child1, d.Child2, d.Child3, d.Child4 } into grp
                           orderby grp.Key.Root, grp.Key.Child1, grp.Key.Child2, grp.Key.Child3, grp.Key.Child4*/
                            from grp in grouped
                            orderby grp.Key
                            select new GetSumApproveLeaveByRootDto
                            {
                                //Root = $"{grp.Key.Root}{(!string.IsNullOrEmpty(grp.Key.Child1) ? "/" + grp.Key.Child1 : "")}{(!string.IsNullOrEmpty(grp.Key.Child2) ? "/" + grp.Key.Child2 : "")}{(!string.IsNullOrEmpty(grp.Key.Child3) ? "/" + grp.Key.Child3 : "")}{(!string.IsNullOrEmpty(grp.Key.Child4) ? "/" + grp.Key.Child4 : "")}",
                                Root = organizationName,
                                SumLeaveDay = grp.Sum(x => x.LeaveTotal),
                                sickDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-001").Sum(x => x.LeaveTotal),
                                personalDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-002").Sum(x => x.LeaveTotal),
                                maternityDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-003").Sum(x => x.LeaveTotal),
                                wifeDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-004").Sum(x => x.LeaveTotal),
                                restDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-005").Sum(x => x.LeaveTotal),
                                ordainDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-006").Sum(x => x.LeaveTotal),
                                absentDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-007").Sum(x => x.LeaveTotal),
                                studyDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-008").Sum(x => x.LeaveTotal),
                                agencyDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-009").Sum(x => x.LeaveTotal),
                                coupleDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-010").Sum(x => x.LeaveTotal),
                                therapyDayCountMale = grp.Where(x => x.Gender == "ชาย" && x.LeaveTypeCode == "LV-011").Sum(x => x.LeaveTotal),

                                sickDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-001").Sum(x => x.LeaveTotal),
                                personalDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-002").Sum(x => x.LeaveTotal),
                                maternityDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-003").Sum(x => x.LeaveTotal),
                                wifeDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-004").Sum(x => x.LeaveTotal),
                                restDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-005").Sum(x => x.LeaveTotal),
                                ordainDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-006").Sum(x => x.LeaveTotal),
                                absentDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-007").Sum(x => x.LeaveTotal),
                                studyDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-008").Sum(x => x.LeaveTotal),
                                agencyDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-009").Sum(x => x.LeaveTotal),
                                coupleDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-010").Sum(x => x.LeaveTotal),
                                therapyDayCountFemale = grp.Where(x => x.Gender == "หญิง" && x.LeaveTypeCode == "LV-011").Sum(x => x.LeaveTotal),

                                sickDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-001").Sum(x => x.LeaveTotal),
                                personalDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-002").Sum(x => x.LeaveTotal),
                                maternityDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-003").Sum(x => x.LeaveTotal),
                                wifeDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-004").Sum(x => x.LeaveTotal),
                                restDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-005").Sum(x => x.LeaveTotal),
                                ordainDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-006").Sum(x => x.LeaveTotal),
                                absentDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-007").Sum(x => x.LeaveTotal),
                                studyDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-008").Sum(x => x.LeaveTotal),
                                agencyDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-009").Sum(x => x.LeaveTotal),
                                coupleDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-010").Sum(x => x.LeaveTotal),
                                therapyDayCountNo = grp.Where(x => x.Gender != "ชาย" && x.Gender != "หญิง" && x.LeaveTypeCode == "LV-011").Sum(x => x.LeaveTotal),
                            })
                          .ToList();

                return res;
            }
            else
            {
                return new List<GetSumApproveLeaveByRootDto>
                {
                    new GetSumApproveLeaveByRootDto
                    {
                        Root = organizationName,
                        SumLeaveDay = 0,
                        sickDayCountMale = 0,
                        maternityDayCountMale = 0,
                        wifeDayCountMale = 0,
                        personalDayCountMale = 0,
                        restDayCountMale = 0,
                        ordainDayCountMale = 0,
                        absentDayCountMale = 0,
                        studyDayCountMale = 0,
                        agencyDayCountMale = 0,
                        coupleDayCountMale = 0,
                        therapyDayCountMale = 0,
                        sickDayCountFemale = 0,
                        maternityDayCountFemale = 0,
                        wifeDayCountFemale = 0,
                        personalDayCountFemale = 0,
                        restDayCountFemale = 0,
                        ordainDayCountFemale = 0,
                        absentDayCountFemale = 0,
                        studyDayCountFemale = 0,
                        agencyDayCountFemale = 0,
                        coupleDayCountFemale = 0,
                        therapyDayCountFemale = 0,
                        sickDayCountNo = 0,
                        maternityDayCountNo = 0,
                        wifeDayCountNo = 0,
                        personalDayCountNo = 0,
                        restDayCountNo = 0,
                        ordainDayCountNo = 0,
                        absentDayCountNo = 0,
                        studyDayCountNo = 0,
                        agencyDayCountNo = 0,
                        coupleDayCountNo = 0,
                        therapyDayCountNo = 0
                    }
                };
            }
        }

        public async Task<List<GetCountApproveLeaveByTypeDto>> GetCountApproveLeaveByTypeAndRange(DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                            .Include(x => x.Type)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            var res = (from d in data
                       group d by new { d.KeycloakUserId, LeaveTypeId = d.Type.Id, LeaveTypeCode = d.Type.Code } into grp
                       select new GetCountApproveLeaveByTypeDto
                       {
                           KeycloakUserId = grp.Key.KeycloakUserId,
                           LeaveTypeId = grp.Key.LeaveTypeId,
                           LeaveTypeCode = grp.Key.LeaveTypeCode,
                           CountLeave = grp.Count()
                       })
                      .ToList();

            return res;
        }

        public async Task<double> GetSumApproveLeaveTotalByTypeAndRangeForUser(Guid keycloakUserId, Guid leaveTypeId, DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0;
        }

        public async Task<int> GetCountApproveLeaveByTypeAndRangeForUser(Guid keycloakUserId, Guid leaveTypeId, DateTime startDate, DateTime endDate)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Date >= startDate.Date && x.LeaveStartDate.Date <= endDate.Date)
                            .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                            .ToListAsync();

            return data.Count;
        }

        public async Task<double> GetSumRejectLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStatus == "REJECT")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0;
        }

        public async Task<double> GetSumDeleteLeaveByTypeForUserAsync(Guid keycloakUserId, Guid leaveTypeId, int year)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable()
                            .Include(x => x.Type)
                            .Where(x => x.KeycloakUserId == keycloakUserId)
                            .Where(x => x.Type.Id == leaveTypeId)
                            .Where(x => x.LeaveStartDate.Year == year)
                            .Where(x => x.LeaveStatus == "DELETE")
                            .ToListAsync();

            if (data.Count > 0)
                return data.Sum(x => x.LeaveTotal);
            else
                return 0;
        }

        public async Task<bool> CheckDuplicateLeave(Guid keycloakUserId, DateTime startDate, DateTime endDate, string range)
        {
            var leaveStatus = new List<string>() { "NEW", "PENDING", "APPROVE" };
            if (range == "ALL")
            {
                var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                                            .Include(x => x.Type)
                                            //.Where(x => x.LeaveRange == "ALL")
                                            .Where(x => x.KeycloakUserId == keycloakUserId)
                                            .Where(x => x.LeaveStartDate.Date == startDate.Date || x.LeaveEndDate.Date == endDate.Date)
                                            .Where(x => leaveStatus.Contains(x.LeaveStatus))
                                            .ToListAsync();

                return data.Count > 0;
            }
            else
            {
                var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                                            .Include(x => x.Type)
                                            .Where(x => x.LeaveRange == range)
                                            .Where(x => x.KeycloakUserId == keycloakUserId)
                                            .Where(x => x.LeaveStartDate.Date == startDate.Date || x.LeaveEndDate.Date == endDate.Date)
                                            .Where(x => leaveStatus.Contains(x.LeaveStatus))
                                            .ToListAsync();

                return data.Count > 0;
            }

        }

        public async Task DeleteLeaveDocumentAsync(Guid Id)
        {
            var doc = await _dbContext.Set<LeaveDocument>()
                            .Where(x => x.Id == Id)
                            .FirstOrDefaultAsync();
            if (doc != null)
            {
                _dbContext.Set<LeaveDocument>().Remove(doc);
                await _dbContext.SaveChangesAsync();
            }
        }

        public async Task<LeaveRequest?> GetLeavePeriodAsync(Guid keycloakUserId, DateTime date)
        {
            var data = await _dbContext.Set<LeaveRequest>().AsQueryable().AsNoTracking()
                                .Include(x => x.Type)
                                .Where(x => x.KeycloakUserId == keycloakUserId)
                                .Where(x => x.LeaveStatus == "APPROVE" || x.LeaveStatus == "DELETING")
                                .Where(x => x.LeaveStartDate.Date <= date.Date && x.LeaveEndDate >= date.Date)
                                .FirstOrDefaultAsync();
            return data;
        }

        #endregion
    }
}