using Amazon.S3.Model;
using Dapper;
using DataBase.Entities.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Graph.SecurityNamespace;
using Microsoft.IdentityModel.Tokens;
using MySqlConnector;
using Newtonsoft.Json;
using System.Data;
using System.Diagnostics.CodeAnalysis;
using User.Insight.Tracker.Common;
using User.Insight.Tracker.DataContracts;
using User.Insight.Tracker.DataContracts.Requests;

namespace User.Insight.Tracker.DataProviders
{
    [ExcludeFromCodeCoverage]
    public class UserInsightTrackerProvider : IUserInsightTrackerProvider
    {
        private readonly ComputraceqaContext _dbContext;
        private readonly ILogger _logger;
        public UserInsightTrackerProvider(ILogger<UserInsightTrackerProvider> logger, ComputraceqaContext dbContext)
        {
            _logger = logger;
            _dbContext = dbContext;
        }
        [ExcludeFromCodeCoverage]
        public List<CallDetail> GetEmployeeCallDetails(SearchRequest<CallDetailFilterRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetEmployeeCallDetails()");
            var filter = filters.Filter;

            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(filters.SearchText) ? filters.SearchText : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_call_started_on", (filter != null && filter.CallStartedOn.HasValue) ? filter.CallStartedOn : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_call_ended_on", (filter != null && filter.CallEndedOn.HasValue) ? filter.CallEndedOn : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", !string.IsNullOrEmpty(filters.SortOrder) ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", !string.IsNullOrEmpty(filters.SortColumn) ? filters.SortColumn : DBNull.Value);

            var results = _dbContext.CallDetails
                .FromSqlRaw("CALL GetCallDetails(@p_email,@p_emp_code, @p_call_started_on, @p_call_ended_on, @p_results_for, @p_page, @p_page_size, @p_ignore_paging, @p_sort_column, @p_sort_type)",
                   emailParam, empCodeParam, startedOnParam, endedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<SystemSleepDetail> GetSystemSleepDetails(SearchRequest<SystemSleepRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetSystemSleepDetails()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var triggeredOnParam = new MySqlParameter("@p_triggered_on", (filter != null && filter.TriggeredOn.HasValue) ? filter.TriggeredOn : DBNull.Value); // or specify a date
            var triggeredOffParam = new MySqlParameter("@p_triggered_off", (filter != null && filter.TriggeredOff.HasValue) ? filter.TriggeredOff : DBNull.Value); // or specify a date
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var descriptionParam = new MySqlParameter("@p_description", (filter != null && filter.Description != "") ? filter.Description : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);


            var results = _dbContext.SystemSleepDetails
                .FromSqlRaw("CALL GetSystemSleepDetails(@p_email,@p_emp_code, @p_triggered_on,@p_triggered_off, @p_results_for,@p_description, @p_page, @p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, triggeredOnParam, triggeredOffParam, resultsForParam, descriptionParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }
        [ExcludeFromCodeCoverage]
        public List<EmployeeWorkingTime> GetEmployeeWorkingTime(SearchRequest<EmployeeWorkingTimeRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetEmployeeWorkingTime()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var workLoggedOnParam = new MySqlParameter("@p_work_logged_on", (filter != null && filter.WorkLoggedOn.HasValue) ? filter.WorkLoggedOn : DBNull.Value); // or specify a date
            var workEndedOnParam = new MySqlParameter("@p_work_ended_on", (filter != null && filter.WorkEndedOn.HasValue) ? filter.WorkEndedOn : DBNull.Value); // or specify a date
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);


            var results = _dbContext.EmployeeWorkingTimes
                .FromSqlRaw("CALL GetEmployeeWorkingTime(@p_email,@p_emp_code, @p_work_logged_on,@p_work_ended_on,@p_results_for,@p_page, @p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, workLoggedOnParam, workEndedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }
        [ExcludeFromCodeCoverage]
        public List<SystemLoginDetail> GetSystemLoginDetails(SearchRequest<SystemLoginDetailsRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetSystemLoginDetails()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var systemLoggedOnParam = new MySqlParameter("@p_login_time", (filter != null && filter.LoginTime.HasValue) ? filter.LoginTime : DBNull.Value);
            var systemLoggedOutParam = new MySqlParameter("@p_logout_time", (filter != null && filter.LogoutTime.HasValue) ? filter.LogoutTime : DBNull.Value);// or specify a date
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);


            var results = _dbContext.SystemLoginDetails
                .FromSqlRaw("CALL GetSystemLoginDetails(@p_email,@p_emp_code, @p_login_time,@p_logout_time,@p_results_for, @p_page, @p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, systemLoggedOnParam, systemLoggedOutParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }
        [ExcludeFromCodeCoverage]
        public async Task<List<RunningApplicationDetail>> GetRunningApplications(SearchRequest<RunningApplicationRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetRunningApplications()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_started_on", (filter != null && filter.StartedOn.HasValue) ? filter.StartedOn : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_ended_on", (filter != null && filter.EndedOn.HasValue) ? filter.EndedOn : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);
            var descriptionParam = new MySqlParameter("@p_Description", filter != null && !string.IsNullOrEmpty(filter.Description) ? filter.Description : DBNull.Value);


            var results = await _dbContext.RunningApplicationDetails
                .FromSqlRaw("CALL GetRunningApplications(@p_email,@p_emp_code,@p_Description,@p_started_on,@p_ended_on,@p_results_for, @p_page, @p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, descriptionParam, startedOnParam, endedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToListAsync();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<GraphDetail> GetGraphicData(SearchRequest<GraphDetailsRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetGraphicData()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var reqTime = new MySqlParameter("@Req_time", filter.RequestTime);
            var livedata = new MySqlParameter("@Live_data_flag", filter.IsLiveData);
            var graphDetails = _dbContext.GraphDetail
                                          .FromSqlRaw("CALL GetGraphDetail_new(@p_email,@Req_time,@Live_data_flag)", emailParam, reqTime, livedata)
                                          .ToList();

            return graphDetails;
        }

        [ExcludeFromCodeCoverage]
        public List<DashboardDetails> GetDashboardData(SearchRequest<DashboardDetailsRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetDashboardData()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var reqStartTime = new MySqlParameter("@Req_time_start", filter.RequestStartDate);
            var reqEndTime = new MySqlParameter("@Req_time_end", filter.RequestEndDate);
            var results = _dbContext.DashboardDetails
                .FromSqlRaw("CALL GetDashBoardDetail_new(@p_email,@Req_time_start,@Req_time_end)",
                    emailParam, reqStartTime, reqEndTime)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<UserProfile> GetUserProfileData(SearchRequest<UserProfileRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetUserProfileData()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var dateOnParam = new MySqlParameter("@p_req_date", (filter != null && filter.RequestDateStart.HasValue) ? filter.RequestDateStart : DBNull.Value); // or specify a date
            var dateendParam = new MySqlParameter("@p_req_date_end", (filter != null && filter.RequestDateEnd.HasValue) ? filter.RequestDateEnd : DBNull.Value); // or specify a date
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);
            var activityUsers = new MySqlParameter("@p_active_users", filter?.ActiveUsers ?? false); // Default to false or any other appropriate value

            var results = _dbContext.UserProfile
                .FromSqlRaw("CALL GetUserProfileData(@p_email,@p_emp_code,@p_active_users,@p_page, @p_page_size,@p_ignore_paging,@p_req_date,@p_req_date_end,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, activityUsers, dateOnParam, dateendParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<ActivityDetails> GetAllActivity(int Type, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAllActivity()");

            var typeParam = new MySqlParameter("@p_type", Type);
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);


            var results = _dbContext.ActivityDetails
                .FromSqlRaw("CALL GetActivityDetails(@p_type ,@p_email)",
                   typeParam, emailParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<UserActivityDetails> GetUserActivityDetails(SearchRequest<UserActivityDetailsRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetUserActivityDetails()");
            var filter = filters.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filters.SearchText != "" ? filters.SearchText : DBNull.Value);
            var systemLoggedOnParam = new MySqlParameter("@p_login_time", (filter != null && filter.LoginTime.HasValue) ? filter.LoginTime.Value : DBNull.Value);
            var systemLoggedOutParam = new MySqlParameter("@p_logout_time", (filter != null && filter.LogoutTime.HasValue) ? filter.LogoutTime.Value : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page_size", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var activity = new MySqlParameter("@p_filter", filter?.Activity);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);

            var results = _dbContext.UserActivityDetails
                .FromSqlRaw("CALL ActivityAlertDetails(@p_email,@p_emp_code, @p_page_size,@p_page, @p_ignore_paging, @p_filter, @p_login_time, @p_logout_time ,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, pageSizeParam, pageParam, ignorePagingParam, activity, systemLoggedOnParam, systemLoggedOutParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<ApplicationActivityReportResult> GetApplicationActivityReport(ApplicationActivityReportRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetApplicationActivityReport()");
            var filter = searchrequest;

            List<ApplicationActivityReportResult> objApplicationActivityReportResultList = new List<ApplicationActivityReportResult>();
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(filter.EmpCode) ? filter.EmpCode : (object)DBNull.Value);
            var systemStartOnParam = new MySqlParameter("@p_started_on", filter.StartDate.HasValue ? filter.StartDate.Value : (object)DBNull.Value);
            var systemEndOnParam = new MySqlParameter("@p_ended_on", filter.EndDate.HasValue ? filter.EndDate.Value : (object)DBNull.Value);
            var showParam = new MySqlParameter("@p_show", !string.IsNullOrEmpty(filter.Show) ? filter.Show : (object)DBNull.Value);
            var deviceParam = new MySqlParameter("@p_device", !string.IsNullOrEmpty(filter.Device) ? filter.Device : (object)DBNull.Value);
            var chartTypeParam = new MySqlParameter("@p_chartType", !string.IsNullOrEmpty(filter.ChartType) ? filter.ChartType : (object)DBNull.Value);
            var typeParam = new MySqlParameter("@p_type", filter.Type);

            if (filter.Type == "Applications" || filter.Type == "Application(Top 10)")
            {
                var results = _dbContext.ApplicationActivityReport
                    .FromSqlRaw("CALL GetApplicationChart(@p_email,@p_emp_code, @p_started_on, @p_ended_on, @p_show, @p_device, @p_chartType, @p_type)",
                        emailParam, empCodeParam, systemStartOnParam, systemEndOnParam, showParam, deviceParam, chartTypeParam, typeParam)
                    .ToList();

                // Calculate the total count
                long totalCount = results.Sum(r => r.ApplicationCount ?? 0);

                var objApplicationActivityReportResult = new ApplicationActivityReportResult
                {
                    TotalCount = totalCount,
                    Results = results
                };

                objApplicationActivityReportResultList.Add(objApplicationActivityReportResult);
            }


            return objApplicationActivityReportResultList;
        }

        [ExcludeFromCodeCoverage]
        public List<CallsActivityReportResult> GetCallsActivityReport(CallsActivityReportRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetCallsActivityReport()");
            var filter = searchrequest;

            List<CallsActivityReportResult> objApplicationActivityReportResultList = new List<CallsActivityReportResult>();
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(filter.EmpCode) ? filter.EmpCode : (object)DBNull.Value);
            var systemStartOnParam = new MySqlParameter("@p_started_on", filter.StartDate.HasValue ? filter.StartDate.Value : (object)DBNull.Value);
            var systemEndOnParam = new MySqlParameter("@p_ended_on", filter.EndDate.HasValue ? filter.EndDate.Value : (object)DBNull.Value);
            var showParam = new MySqlParameter("@p_show", !string.IsNullOrEmpty(filter.Show) ? filter.Show : (object)DBNull.Value);
            var deviceParam = new MySqlParameter("@p_device", !string.IsNullOrEmpty(filter.Device) ? filter.Device : (object)DBNull.Value);
            var chartTypeParam = new MySqlParameter("@p_chartType", !string.IsNullOrEmpty(filter.ChartType) ? filter.ChartType : (object)DBNull.Value);
            var typeParam = new MySqlParameter("@p_type", filter.Type);

            if (filter.Type == "Calls" || filter.Type == "Calls(Top 10)")
            {
                var results = _dbContext.CallsActivityReport
                    .FromSqlRaw("CALL GetCallsChart(@p_email,@p_emp_code, @p_started_on, @p_ended_on, @p_show, @p_device, @p_chartType, @p_type)",
                        emailParam, empCodeParam, systemStartOnParam, systemEndOnParam, showParam, deviceParam, chartTypeParam, typeParam)
                    .ToList();

                // Calculate the total count
                long totalCount = results.Sum(r => r.CallsCount ?? 0);

                var objApplicationActivityReportResult = new CallsActivityReportResult
                {
                    TotalCount = totalCount,
                    Results = results
                };

                objApplicationActivityReportResultList.Add(objApplicationActivityReportResult);
            }


            return objApplicationActivityReportResultList;
        }

        [ExcludeFromCodeCoverage]
        public List<SessionActivityReportResult> GetSessionsActivityReport(SessionActivityReportRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetCallsActivityReport()");
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            List<SessionActivityReportResult> objApplicationActivityReportResultList = new List<SessionActivityReportResult>();
            var empCodeParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(searchrequest.EmpCode) ? searchrequest.EmpCode : (object)DBNull.Value);
            var systemStartOnParam = new MySqlParameter("@p_started_on", searchrequest.StartedOn.HasValue ? searchrequest.StartedOn.Value : (object)DBNull.Value);
            var systemEndOnParam = new MySqlParameter("@p_ended_on", searchrequest.EndedOn.HasValue ? searchrequest.EndedOn.Value : (object)DBNull.Value);
            var deviceParam = new MySqlParameter("@p_device", !string.IsNullOrEmpty(searchrequest.Device) ? searchrequest.Device : (object)DBNull.Value);
            var chartTypeParam = new MySqlParameter("@ChartType", !string.IsNullOrEmpty(searchrequest.ChartType) ? searchrequest.ChartType : (object)DBNull.Value);


            var results = _dbContext.SessionActivityReport
                 .FromSqlRaw("CALL GetSessionsChart(@p_email,@p_emp_code, @p_started_on, @p_ended_on, @p_device,@ChartType)",
                  emailParam, empCodeParam, systemStartOnParam, systemEndOnParam, deviceParam, chartTypeParam)
                 .ToList();

            // Calculate the total count
            long totalCount = results.Sum(r => r.TotalCount ?? 0);

            var objApplicationActivityReportResult = new SessionActivityReportResult
            {
                TotalCount = totalCount,
                Results = results
            };

            objApplicationActivityReportResultList.Add(objApplicationActivityReportResult);



            return objApplicationActivityReportResultList;
        }


        [ExcludeFromCodeCoverage]
        public List<LogInActivityReportResult> GetLogInActivityReport(LogInActivityReportRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetLogInActivityReport()");

            List<LogInActivityReportResult> objApplicationActivityReportResultList = new List<LogInActivityReportResult>();
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(searchrequest.EmpCode) ? searchrequest.EmpCode : (object)DBNull.Value);
            var systemStartOnParam = new MySqlParameter("@p_started_on", searchrequest.StartedOn != null ? searchrequest.StartedOn.Value : null);
            var systemEndOnParam = new MySqlParameter("@p_ended_on", searchrequest.EndedOn != null ? searchrequest.EndedOn.Value : null);
            var chartTypeParam = new MySqlParameter("@ChartType", !string.IsNullOrEmpty(searchrequest.ChartType) ? searchrequest.ChartType : (object)DBNull.Value);


            var results = _dbContext.LogInActivityReport
                 .FromSqlRaw("CALL GetLogInChart(@p_email,@p_emp_code,@p_started_on, @p_ended_on, @ChartType)",
                  emailParam, empCodeParam, systemStartOnParam, systemEndOnParam, chartTypeParam)
                 .ToList();

            var objApplicationActivityReportResult = new LogInActivityReportResult
            {

                Results = results
            };

            objApplicationActivityReportResultList.Add(objApplicationActivityReportResult);



            return objApplicationActivityReportResultList;
        }

        [ExcludeFromCodeCoverage]
        public List<ReportActivityDetailsResults> GetReportActivity(JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetReportActivity()");
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);

            var jsonResults = _dbContext.JsonResultModel
                 .FromSqlRaw("CALL GetReportActivityDetails(@p_email)", emailParam)
                 .AsEnumerable()
                 .Select(r => r.CombinedData)
                 .ToList();

            var results = jsonResults
                .Select(json => JsonConvert.DeserializeObject<ReportActivityDetailsResults>(json))
                .ToList();

            return results;
        }



        [ExcludeFromCodeCoverage]
        public List<AllUserDetails> GetAllUser(SearchRequestResponse<UserDetailedRequest> searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAllUser()");

            var filter = searchrequest.Filter;

            // Handle null filter scenario
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            string combinedString = (filter != null && filter.FilterColumn != null) ? string.Join(",", filter.FilterColumn) : null;
            string combinedStringvalue = (filter != null && filter.FilterValue != null) ? string.Join(",", filter.FilterValue) : null;
            var sortColumn = new MySqlParameter("@p_sort_column", filter != null && !string.IsNullOrEmpty(filter.SortColumn) ? filter.SortColumn : (object)DBNull.Value);
            var sortDirection = new MySqlParameter("@p_sort_direction", filter != null && !string.IsNullOrEmpty(filter.SortDirection) ? filter.SortDirection : (object)DBNull.Value);
            var pageNumber = new MySqlParameter("@p_page_number", searchrequest.PageNumber != 0 ? searchrequest.PageNumber : 1);
            var pageSize = new MySqlParameter("@p_page_size", searchrequest.PageSize != 0 ? searchrequest.PageSize : 10);
            var filterColumn = new MySqlParameter("@p_filter_column", combinedString);
            var filterValue = new MySqlParameter("@p_filter_value", combinedStringvalue);

            var results = _dbContext.AllUserDetails
                .FromSqlRaw("CALL GetuserDetails(@p_sort_column,@p_sort_direction,@p_page_number,@p_page_size,@p_filter_column,@p_filter_value,@p_email)",
                    sortColumn, sortDirection, pageNumber, pageSize, filterColumn, filterValue, emailParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public async Task< List<AllUser>> GetUserDetails(string Email)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetUserDetails()");
            var UserEmail = new MySqlParameter("@p_User_Email", Email);

            var result = await _dbContext.AllUsers
               .FromSqlRaw("CALL GetUserExistDetails(@p_User_Email)", UserEmail)
               .ToListAsync(); 
            return result;
        }       


        [ExcludeFromCodeCoverage]
        public string DeleteUser(DeleteUserRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.DeleteUser()");
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var userId = new MySqlParameter("@p_user_id", searchrequest.UserId);
            var userStatus = new MySqlParameter("@p_user_status", searchrequest.UserStatus);

            var results = _dbContext.Database
                .ExecuteSqlRaw("CALL UpdateUserStatus(@p_user_id,@p_user_status,@p_email)",
                    userId, userStatus, emailParam);
            if (results == 1)
            {
                return "User Deleted Successfully";
            }
            else
            {
                return "Request Not Completed";
            }
        }

        [ExcludeFromCodeCoverage]
        public UserActiveResponse UserActive(UserActiveRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.UserActive()");

            var emailParam = new MySqlParameter("@p_email",
                string.IsNullOrEmpty(userDetails.Email) ? (object)DBNull.Value : userDetails.Email);
            var userId = new MySqlParameter("@p_user_id", searchrequest.UserId);
            var userStatus = new MySqlParameter("@p_user_status", searchrequest.UserStatus);
            var userDelete = new MySqlParameter("@p_user_deleted", searchrequest.Isdelete);

            var result = _dbContext.UserActives
                .FromSqlRaw("CALL UpdateUserIsActive(@p_user_id, @p_user_status, @p_email, @p_user_deleted)",
                    userId, userStatus, emailParam, userDelete)
                .AsEnumerable()
                .FirstOrDefault();

            if (result == null)
            {
                _logger.LogWarning("Stored procedure returned no result for userId: {UserId}", searchrequest.UserId);
                return new UserActiveResponse
                {
                    Message = "No data returned."
                };
            }

            return new UserActiveResponse
            {
                Message = result.Message
            };
        }






        [ExcludeFromCodeCoverage]
        public string AddUpdateUser(AddUpdateUserRequest searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.UserActive()");

            // Define parameters for the stored procedure
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var userId = new MySqlParameter("@p_user_id", searchrequest.UserId);
            var userFirstName = new MySqlParameter("@p_user_first_name", searchrequest.UserFirstName);
            var userLastName = new MySqlParameter("@p_user_last_name", searchrequest.UserLastName);
            var userEmail = new MySqlParameter("@p_user_email", searchrequest.UserEmail);
            var userStatus = new MySqlParameter("@p_user_status", searchrequest.UserStatus);
            var userRoleId = new MySqlParameter("@p_user_role_id", searchrequest.UserRoleId);

            // Call the stored procedure and capture the result
            var result = _dbContext.TempDataResult
                .FromSqlRaw("CALL InsertOrUpdateUser(@p_user_id, @p_user_first_name, @p_user_last_name, @p_user_email, @p_user_status, @p_user_role_id ,@p_email)",
                    userId, userFirstName, userLastName, userEmail, userStatus, userRoleId, emailParam)
                .AsEnumerable()
                .FirstOrDefault();  // Get the first row from the result

            // Check the result code and return appropriate message
            if (result != null)
            {
                switch (result.Code)
                {
                    case 200:
                        return "Success";
                    case 151:
                        return "Email already exists with another user.";
                    case 152:
                        return "Email already exists with another user.";
                    default:
                        return "Unexpected result from stored procedure.";
                }
            }
            else
            {
                return "Request Not Completed";
            }
        }


        [ExcludeFromCodeCoverage]
        public List<SummaryDetails> GetUserSummaryDetails(SearchRequestResponse<SummaryDetailsRequest> searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.UserSummaryDetails()");
            var filter = searchrequest.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@emp_code_param", filter.EmpCode != null ? filter.EmpCode : DBNull.Value);
            var startOnParam = new MySqlParameter("@call_started_param", filter.StartedOn != null ? filter.StartedOn.Value : null);
            var endOnParam = new MySqlParameter("@call_ended_param", filter.EndedOn != null ? filter.EndedOn.Value : null);
            var resultsForParam = new MySqlParameter("@results_for", (filter.ResultsFor != "") ? filter.ResultsFor : DBNull.Value); // or specify a date
            var results = _dbContext.SummaryDetails
                .FromSqlRaw("CALL GetSummaryDetails(@emp_code_param,@call_started_param,@call_ended_param,@results_for,@p_email)",
                    empCodeParam, startOnParam, endOnParam, resultsForParam, emailParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]

        public List<BrowsingHistoryDetails> GetBrowsingDetails(SearchRequest<BrowsingHistoryRequest> searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetBrowsingDetails()");
            var filter = searchrequest.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filter.EmpCode != null ? filter.EmpCode : DBNull.Value);
            var startOnParam = new MySqlParameter("@p_started_on", filter.StartedOn != null ? filter.StartedOn.Value : null);
            var endOnParam = new MySqlParameter("@p_ended_on", filter.EndedOn != null ? filter.EndedOn.Value : null);
            var descriptionParam = new MySqlParameter("@p_description", filter != null && !string.IsNullOrEmpty(filter.Description) ? filter.Description : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", searchrequest.PageNumber != 0 ? searchrequest.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", searchrequest.PageSize != 0 ? searchrequest.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", searchrequest.IgnorePaging);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", searchrequest.SortColumn != "" ? searchrequest.SortColumn : DBNull.Value);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", searchrequest.SortOrder != "" ? searchrequest.SortOrder : DBNull.Value);

            var results = _dbContext.BrowsingHistoryDetails
                .FromSqlRaw("CALL GetBrowsingHistory(@p_email,@p_emp_code,@p_started_on,@p_ended_on,@p_description,@p_results_for,@p_page,@p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, startOnParam, endOnParam, descriptionParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public List<ApplicationTabDetail> GetApplicationTabDetails(SearchRequest<ApplicationTabRequest> searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetApplicationTabDetails()");
            var filter = searchrequest.Filter;
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filter.EmpCode != "" ? filter.EmpCode : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_started_on", (filter != null && filter.StartedOn.HasValue) ? filter.StartedOn : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_ended_on", (filter != null && filter.EndedOn.HasValue) ? filter.EndedOn : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", searchrequest.PageNumber != 0 ? searchrequest.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", searchrequest.PageSize != 0 ? searchrequest.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", searchrequest.IgnorePaging);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", searchrequest.SortColumn != "" ? searchrequest.SortColumn : DBNull.Value);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", searchrequest.SortOrder != "" ? searchrequest.SortOrder : DBNull.Value);

            var results = _dbContext.ApplicationTabDetail
                .FromSqlRaw("CALL GetApplicationTimeSpent(@p_email,@p_emp_code,@p_started_on,@p_ended_on,@p_results_for,@p_page,@p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    emailParam, empCodeParam, startedOnParam, endedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<ApplicationTabDetailsView> GetRunningApplicationsDetailed(SearchRequest<ApplicationTabDetailsRequest> searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetRunningApplicationsDetailed()");
            var filter = searchrequest.Filter;
            var applicationNameParam = new MySqlParameter("@p_application_name", filter.ApplicationName != "" ? filter.ApplicationName : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code", filter.EmpCode != "" ? filter.EmpCode : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_started_on", (filter != null && filter.StartedOn.HasValue) ? filter.StartedOn : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_ended_on", (filter != null && filter.EndedOn.HasValue) ? filter.EndedOn : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", searchrequest.PageNumber != 0 ? searchrequest.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", searchrequest.PageSize != 0 ? searchrequest.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", searchrequest.IgnorePaging);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", searchrequest.SortColumn != "" ? searchrequest.SortColumn : DBNull.Value);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", searchrequest.SortOrder != "" ? searchrequest.SortOrder : DBNull.Value);

            var results = _dbContext.ApplicationTabDetailsView
                .FromSqlRaw("CALL GetRunningApplicationsDetailedview(@p_emp_code,@p_application_name,@p_started_on,@p_ended_on,@p_results_for,@p_page,@p_page_size,@p_ignore_paging,@p_sort_column,@p_sort_type)",
                    empCodeParam, applicationNameParam, startedOnParam, endedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<TopBottomDetailsResponseResult> GetSummaryTopBottomDetails(SummaryTopBottomDetails searchrequest, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetSummaryTopBottomDetails() called.");

            if (searchrequest == null)
                throw new ArgumentNullException(nameof(searchrequest), "Search request cannot be null.");

            using (var connection = _dbContext.Database.GetDbConnection())
            {
                try
                {
                    connection.Open();

                    var parameters = new DynamicParameters();
                    parameters.Add("@p_email", userDetails.Email ?? (object)DBNull.Value, DbType.String);
                    parameters.Add("@p_emp_code", searchrequest.EmpCode ?? (object)DBNull.Value, DbType.String);
                    parameters.Add("@p_started_on", searchrequest.StartedOn ?? (object)DBNull.Value, DbType.Date);

                    // Execute the stored procedure and retrieve multiple result sets
                    using (var multi = connection.QueryMultiple(
                        "CALL GetSummaryTopBottomDetails(@p_email,@p_emp_code, @p_started_on)", parameters))
                    {
                        var topActiveUsers = multi.Read<SystemLoginDetailsTop10ActiveTimeResult>();
                        var bottomActiveUsers = multi.Read<SystemLoginDetailsTop10BottomTimeResult>();
                        var topSleepUsers = multi.Read<SystemLoginDetailsTop10SleepTimeResult>();
                        var bottomSleepUsers = multi.Read<SystemLoginDetailsBottom10SleepTimeResult>();
                        var topCallUsers = multi.Read<SystemLoginDetailsTop10CallDurationResult>();
                        var bottomCallUsers = multi.Read<SystemLoginDetailsBottom10CallDurationResult>();

                        TopBottomDetailsResponseResult obj = new TopBottomDetailsResponseResult();
                        obj.SystemLoginDetailsTop10ActiveTime = new List<SystemLoginDetailsTop10ActiveTimeResult>();
                        obj.SystemLoginDetailsBottom10ActiveTime = new List<SystemLoginDetailsTop10BottomTimeResult>();
                        obj.SystemLoginDetailsTop10SleepTime = new List<SystemLoginDetailsTop10SleepTimeResult>();
                        obj.SystemLoginDetailsBottom10SleepTime = new List<SystemLoginDetailsBottom10SleepTimeResult>();
                        obj.SystemLoginDetailsTop10CallDurationTime = new List<SystemLoginDetailsTop10CallDurationResult>();
                        obj.SystemLoginDetailsBottom10CallDurationTime = new List<SystemLoginDetailsBottom10CallDurationResult>();
                        obj.SystemLoginDetailsTop10ActiveTime.AddRange(topActiveUsers);
                        obj.SystemLoginDetailsBottom10ActiveTime.AddRange(bottomActiveUsers);
                        obj.SystemLoginDetailsTop10SleepTime.AddRange(topSleepUsers);
                        obj.SystemLoginDetailsBottom10SleepTime.AddRange(bottomSleepUsers);
                        obj.SystemLoginDetailsTop10CallDurationTime.AddRange(topCallUsers);
                        obj.SystemLoginDetailsBottom10CallDurationTime.AddRange(bottomCallUsers);

                        return new List<TopBottomDetailsResponseResult> { obj };
                    }
                }
                catch (Exception ex)
                {                   
                    _logger.LogError(ex, "Error occurred while fetching summary top-bottom details.");
                    //  Depending on your error handling strategy, you might want to return a specific error response or rethrow the exception.
                    throw new InvalidOperationException("Summary details could not be processed.");
                }
            }
        }

        [ExcludeFromCodeCoverage]
        public List<LocationDetails> GetLocationDetails(SearchRequest<LocationDetailsRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetEmployeeCallDetails()");
            var filter = filters.Filter;

            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var empCodeParam = new MySqlParameter("@p_emp_code ", !string.IsNullOrEmpty(filter.EmpCode) ? filters.SearchText : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_updated_on_start ", (filter != null && filter.UpdateOnStart.HasValue) ? filter.UpdateOnStart : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_updated_on_end", (filter != null && filter.UpdateOnEnd.HasValue) ? filter.UpdateOnEnd : DBNull.Value);
            var resultsForParam = new MySqlParameter("@p_results_for", (filter != null && !string.IsNullOrEmpty(filter.ResultsFor)) ? filter.ResultsFor : DBNull.Value);
            var pageParam = new MySqlParameter("@p_page", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var sortColumnCodeParam = new MySqlParameter("@p_sort_column", filters.SortColumn != "" ? filters.SortColumn : DBNull.Value);
            var sortTypeCodeParam = new MySqlParameter("@p_sort_type", filters.SortOrder != "" ? filters.SortOrder : DBNull.Value);


            var results = _dbContext.LocationDetails
                .FromSqlRaw("CALL GetLocationDetails_new(@p_email,@p_emp_code,@p_updated_on_start,@p_updated_on_end,@p_results_for, @p_page, @p_page_size, @p_ignore_paging,@p_sort_column,@p_sort_type)",
                   emailParam, empCodeParam, startedOnParam, endedOnParam, resultsForParam, pageParam, pageSizeParam, ignorePagingParam, sortColumnCodeParam, sortTypeCodeParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public List<PermissionDetails> GetPermissionDetails(PermissionDetailRequest permissionDetailRequest, JwtTokenDetail userDetails)
        {

            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);

            var results = _dbContext.PermissionDetails
       .FromSqlRaw("CALL GetUserRolePermission(@p_email)", emailParam)
       .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<ManagerRoles> GetManagerRoles(ManagerRolesRequest managerRoles, JwtTokenDetail userDetails)
        {

            var emailParam = new MySqlParameter("@p_email",
         !string.IsNullOrEmpty(managerRoles.Email) ? managerRoles.Email : (object)DBNull.Value);

            var pageParam = new MySqlParameter("@p_page",
                managerRoles.PageNumber > 0 ? managerRoles.PageNumber : (object)DBNull.Value);

            var pageSizeParam = new MySqlParameter("@p_page_size",
                managerRoles.PageSize > 0 ? managerRoles.PageSize : (object)DBNull.Value);

            var sortColumnParam = new MySqlParameter("@p_sort_column",
                !string.IsNullOrEmpty(managerRoles.SortColumn) ? managerRoles.SortColumn : (object)DBNull.Value);

            var sortTypeParam = new MySqlParameter("@p_sort_type",
                !string.IsNullOrEmpty(managerRoles.SortType) ? managerRoles.SortType : (object)DBNull.Value);

            var roleIdParam = new MySqlParameter("@p_role_id",
             !string.IsNullOrEmpty(managerRoles.RoleId) ? managerRoles.RoleId : (object)DBNull.Value);  // Fixed RoleId check

            var statusParam = new MySqlParameter("@p_status", managerRoles.Status);  // No need for DBNull.Value, bool is always valid

            var results = _dbContext.ManagerRoles
                .FromSqlRaw("CALL GetManageRole(@p_email, @p_page, @p_page_size, @p_sort_column, @p_sort_type, @p_role_id, @p_status)",
                    emailParam, pageParam, pageSizeParam, sortColumnParam, sortTypeParam, roleIdParam, statusParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<RoleDetails> GetRoleDetails(RoleDetailsRequest roleDetails, JwtTokenDetail userDetails)
        {
            var emailParam = new MySqlParameter("@p_email",
                !string.IsNullOrEmpty(roleDetails.Email) ? roleDetails.Email : (object)DBNull.Value);

            var roleIdParam = new MySqlParameter("@p_RoleId",  // Corrected parameter name to match procedure
                !string.IsNullOrEmpty(roleDetails.RoleId) ? roleDetails.RoleId : (object)DBNull.Value); // Assuming RoleId is a string

            var statusParam = new MySqlParameter("@p_status", roleDetails.Status); // Boolean doesn't need DBNull.Value

            var results = _dbContext.RoleDetails
                .FromSqlRaw("CALL GetRoleDetails(@p_email,@p_RoleId,@p_status)",
                            emailParam, roleIdParam, statusParam)
                .ToList();


            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<CreateRoles> CreateOrModifyRoles(CreateRolesRequest createRoles, JwtTokenDetail userDetails)
        {
            var emailParam = new MySqlParameter("@p_email",
                !string.IsNullOrEmpty(userDetails.Email) ? (object)userDetails.Email : DBNull.Value);

            var roleParam = new MySqlParameter("@p_role_name",
                !string.IsNullOrEmpty(createRoles.RoleName) ? (object)createRoles.RoleName : DBNull.Value);

            var activeParam = new MySqlParameter("@p_is_active", (bool)createRoles.IsActive ? 1 : 0);
            var deleteParam = new MySqlParameter("@p_is_deleted", (bool)createRoles.IsDeleted ? 1 : 0);

            var results = _dbContext.CreateRoles
                .FromSqlRaw("CALL CreateOrModifyRoles(@p_email, @p_role_name, @p_is_active, @p_is_deleted)",
                    emailParam, roleParam, activeParam, deleteParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<UpdateRoles> UpdateRolePermission(UpdateRoleRequest roleRequest, JwtTokenDetail userDetails)
        {
            var results = new List<UpdateRoles>();

            foreach (var permission in roleRequest.Permissions)  // Iterate over Permissions
            {
                var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
                var roleIdParam = new MySqlParameter("@p_role_id", roleRequest.RoleId > 0 ? roleRequest.RoleId : DBNull.Value);
                var subFeatureId = new MySqlParameter("@p_feature_subfeature_id", !string.IsNullOrEmpty(permission.FeatureSubId) ? permission.FeatureSubId : (object)DBNull.Value);
                var permissionId = new MySqlParameter("@p_permission_id", !string.IsNullOrEmpty(permission.PermissionId) ? permission.PermissionId : (object)DBNull.Value);
                var roleParam = new MySqlParameter("@p_role_name",
                !string.IsNullOrEmpty(roleRequest.RoleName) ? (object)roleRequest.RoleName : DBNull.Value);

                var activeParam = new MySqlParameter("@p_is_active", (bool)roleRequest.IsActive ? 1 : 0);
                var deleteParam = new MySqlParameter("@p_is_deleted", (bool)roleRequest.IsDeleted ? 1 : 0);
                var viewType = new MySqlParameter("@p_view_type", roleRequest.ViewType);

                var result = _dbContext.UpdateRoles
                    .FromSqlRaw("CALL UpdateRolePermission(@p_email, @p_role_id, @p_feature_subfeature_id, @p_permission_id,@p_role_name,@p_is_active,@p_is_deleted,@p_view_type)",
                        emailParam, roleIdParam, subFeatureId, permissionId, roleParam, activeParam, deleteParam, viewType)
                    .ToList();

                results.AddRange(result);
            }

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<ApplicationProperties> GetAllApplication(JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAllApplication()");

            var results = _dbContext.ApplicationProperties
                .FromSqlRaw("CALL GetAllApplicationProperties")
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<MenuList> GetAllMenu(JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAllMenu()");

            var results = _dbContext.MenuList
                .FromSqlRaw("CALL GetMenuDetails")
                .ToList();

            return results;
        }


    }
}