DamageAssessment_Backend/DamageAssesmentApi/DamageAssesment.Api.Responses/Providers/SurveyResponsesProvider.cs

using AutoMapper;
using DamageAssesment.Api.Responses.Db;
using DamageAssesment.Api.Responses.Interfaces;
using DamageAssesment.Api.Responses.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Reflection;
using System.Text.Json;

namespace DamageAssesment.Api.Responses.Providers
{
    public class SurveyResponsesProvider : ISurveysResponse
    {
        private readonly SurveyResponseDbContext surveyResponseDbContext;
        private readonly ILogger<SurveyResponsesProvider> logger;
        private readonly IAnswerServiceProvider answerServiceProvider;
        private readonly IRegionServiceProvider regionServiceProvider;
        private readonly ILocationServiceProvider locationServiceProvider;
        private readonly IEmployeeServiceProvider employeeServiceProvider;
        private readonly IAttachmentServiceProvider attachmentServiceProvider;
        private readonly IQuestionServiceProvider questionServiceProvider;
        private readonly ISurveyServiceProvider surveyServiceProvider;
        private readonly IMapper mapper;

        public SurveyResponsesProvider(SurveyResponseDbContext surveyResponseDbContext, ILogger<SurveyResponsesProvider> logger, IAnswerServiceProvider answerServiceProvider, IRegionServiceProvider regionServiceProvider, ILocationServiceProvider locationServiceProvider, IEmployeeServiceProvider employeeServiceProvider, IAttachmentServiceProvider attachmentServiceProvider, IQuestionServiceProvider questionServiceProvider, ISurveyServiceProvider surveyServiceProvider, IMapper mapper)
        {
            this.surveyResponseDbContext = surveyResponseDbContext;
            this.logger = logger;
            this.answerServiceProvider = answerServiceProvider;
            this.regionServiceProvider = regionServiceProvider;
            this.locationServiceProvider = locationServiceProvider;
            this.employeeServiceProvider = employeeServiceProvider;
            this.attachmentServiceProvider = attachmentServiceProvider;
            this.questionServiceProvider = questionServiceProvider;
            this.surveyServiceProvider = surveyServiceProvider;
            this.mapper = mapper;
            SeedData();
        }

        public void SeedData()
        {
            // Check if SurveyResponses exist, if not, seed data
            if (!surveyResponseDbContext.SurveyResponses.Any())
            {

                // Create and save SurveyResponse records with references to existing Employee and Location records
                surveyResponseDbContext.SurveyResponses.Add(new Db.SurveyResponse { SurveyId = 1, EmployeeId = 1, LocationId = 1, ClientDevice = "Mobile", Latitude = 98.8767, Longitute = -129.9897, KeyAnswerResult = "true", CreatedDate = DateTime.Now });
                surveyResponseDbContext.SurveyResponses.Add(new Db.SurveyResponse { SurveyId = 1, EmployeeId = 2, LocationId = 2, ClientDevice = "Mobile", Latitude = 98.8767, Longitute = -129.9897, KeyAnswerResult = "true", CreatedDate = DateTime.Now });
                surveyResponseDbContext.SaveChanges();
            }
        }


        public async Task<(bool IsSuccess, dynamic Answers, string ErrorMessage)> GetAnswersByRegionAsync(int surveyId, int employeeid)
        {
            try
            {
                logger?.LogInformation("Querying to get SurveyResponse object from DB");
                List<Db.SurveyResponse> listSurveyResponse = null;
                if (employeeid == 0)
                {
                    listSurveyResponse = surveyResponseDbContext.SurveyResponses.Where(s => s.SurveyId == surveyId).ToList();
                }
                else
                {
                    listSurveyResponse = surveyResponseDbContext.SurveyResponses.Where(s => s.SurveyId == surveyId && s.EmployeeId == employeeid).ToList();
                }
                listSurveyResponse = listSurveyResponse
               .OrderByDescending(obj => obj.Id)
               .GroupBy(obj => new { obj.SurveyId,  obj.LocationId })//obj.EmployeeId,
               .Select(group => group.FirstOrDefault())  // or .FirstOrDefault() if you want to handle empty groups
               .ToList();
                if (listSurveyResponse.Any())
                {
                    var answers = await getAnswersByRegionAndSurveyIdAsync(listSurveyResponse);
                    return (true, answers, "Request Successful.");
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic SurveyResponse, string ErrorMessage)> GetSurveyResponseByIdAsync(int responseId)
        {
            try
            {
                logger?.LogInformation("Querying to get SurveyResponse object from DB");
                var surveyResponse = surveyResponseDbContext.SurveyResponses.Where(s => s.Id == responseId).SingleOrDefault();

                if (surveyResponse != null)
                {
                    var answers = await getSurveyResponseByResponseIdAsync(surveyResponse);

                    if (answers != null)
                        return (true, answers, "Request Successful.");
                    else
                    {
                        answers = new { };
                        return (true, answers, "Empty object returned");
                    }
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic Surveys, string ErrorMessage)> GetActiveSurveysAsync(int? employeeid, string language)
        {
            try
            {
                logger?.LogInformation("Querying to get SurveyResponse object from DB");
                //get all the survey that already taken by the employee
                var surveys = await surveyServiceProvider.getSurveysAsync(language);
                surveys = surveys.Where(s => s.IsEnabled == true && s.Status == SurveyStatus.ACTIVE.ToString()).ToList();
                if (employeeid == null || employeeid == 0)
                    return (true, surveys, null);
                List<int> listOfsurveysId = await surveyResponseDbContext.SurveyResponses.Where(x => x.EmployeeId == employeeid.Value).Select(y => y.SurveyId).ToListAsync();
                var activeSurveys = surveys.Where(s => !listOfsurveysId.Contains(s.Id));
                return (true, activeSurveys, null);
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic Surveys, string ErrorMessage)> GetHistoricSurveysAsync(int? employeeid, string language)
        {
            try
            {
                logger?.LogInformation("Querying to get SurveyResponse object from DB");

                var surveys = await surveyServiceProvider.getSurveysAsync(language);
                // returning only historic data: end date is less than current date.
                surveys = surveys.Where(s => s.Status == SurveyStatus.INACTIVE.ToString()).ToList();
                if (employeeid == null || employeeid == 0)
                    return (true, surveys, null);
                var surveyResponses = await surveyResponseDbContext.SurveyResponses.Where(x => x.EmployeeId == employeeid).ToListAsync();
                var historicSurveys = from s in surveys
                                      from r in surveyResponses
                                      where s.Id == r.SurveyId
                                      select s;
                return (true, historicSurveys, null);

            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic SurveyResponses, string ErrorMessage)> GetSurveyResponsesBySurveyAsync(int surveyId, int employeeid)
        {
            try
            {
                logger?.LogInformation("Querying to get Survey object from microservice");
                var survey = await surveyServiceProvider.getSurveyAsync(surveyId);

                if (survey != null)
                {
                    var answers = await getSurveyResponsesBySurveyIdAsync(surveyId, employeeid);

                    if (answers != null)
                        return (true, answers, "Request Successful.");
                    else
                    {
                        answers = new List<Models.SurveyResponse>();
                        return (true, answers, "Empty object returned");
                    }
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic SurveyResponses, string ErrorMessage)> GetSurveyResponsesBySurveyAndLocationAsync(int surveyId, int locationId, int employeeid)
        {
            try
            {
                logger?.LogInformation("Querying to get Survey object from microservice");
                var survey = await surveyServiceProvider.getSurveyAsync(surveyId);

                if (survey != null)
                {
                    var answers = await getSurveyResponsesBySurveyIdLocationIdAsync(surveyId, locationId, employeeid);

                    if (answers != null)
                        return (true, answers, "Request Successful.");
                    else
                    {
                        answers = new List<Models.SurveyResponse>();
                        return (true, answers, "Empty object returned");
                    }
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic SurveyResponses, string ErrorMessage)> GetSurveyResponsesByMaintenanceCenterAsync(int surveyId, int employeeid)
        {
            try
            {
                logger?.LogInformation("Querying to get Survey object from microservice");
                var survey = await surveyServiceProvider.getSurveyAsync(surveyId);

                if (survey != null)
                {
                    var answers = await getResultsByMaintenanceCenterAsync(surveyId, employeeid);

                    if (answers != null)
                        return (true, answers, "Request Successful.");
                    else
                    {
                        answers = new List<Models.SurveyResponse>();
                        return (true, answers, "Empty object returned");
                    }
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, dynamic SurveyResponses, string ErrorMessage)> GetResponsesByAnswerAsync(int surveyId, int questionId, string answer, int employeeid)
        {
            try
            {
                logger?.LogInformation("Querying to get Survey object from microservice");
                var survey = await surveyServiceProvider.getSurveyAsync(surveyId);
                var question = await questionServiceProvider.getQuestionsAsync(questionId);
                bool IsCorrectAnswer = answer.ToLower().Equals("yes") || answer.ToLower().Equals("no") ? true : false;


                if (survey != null && question != null && IsCorrectAnswer)
                {
                    var answers = await getSurveyResponsesByAnswerAsync(survey, question, answer, employeeid);

                    if (answers != null)
                        return (true, answers, "Request Successful.");
                    else
                    {
                        answers = new List<Models.SurveyResponse>();
                        return (true, answers, "Empty object returned");
                    }
                }
                else
                {
                    return (false, null, "Not found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }


        public async Task<(bool IsSuccess, dynamic surveyResponses, string ErrorMessage)> GetSurveyResponsesAsync(int employeeid)
        {
            try
            {
                var responses = await getAllSurveyResponsesAsync(employeeid);

                if (responses != null)
                    return (true, responses, "Request Successful.");
                else
                {
                    responses = new List<Models.SurveyResponse>();
                    return (true, responses, "Empty object returned");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }
        public async Task<(bool IsSuccess, List<object> surveyResponses, string ErrorMessage)> ExportSurveyResponsesAsync(int surveyId, string language, bool isadmin)
        {
            try
            {
                var responses = await getAllSurveyResponsesExcelAsync(surveyId, language, isadmin);

                if (responses != null)
                    return (true, responses, "Request Successful.");
                else
                {
                    responses = null;
                    return (true, responses, "Empty object returned");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, Models.SurveyResponse SurveyResponse, string ErrorMessage)> PostSurveyResponseAsync(Models.SurveyResponse surveyResponse)
        {
            try
            {
                if (surveyResponse != null)
                {
                    var _surveyResponse = mapper.Map<Models.SurveyResponse, Db.SurveyResponse>(surveyResponse);
                    surveyResponseDbContext.SurveyResponses.Add(_surveyResponse);
                    await surveyResponseDbContext.SaveChangesAsync();
                    surveyResponse.Id = _surveyResponse.Id;
                    return (true, surveyResponse, "Request Successful");
                }
                else
                {
                    logger?.LogInformation($"SurveyResponse cannot be added");
                    return (false, null, "SurveyResponse cannot be added");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }


        public async Task<(bool IsSuccess, Models.SurveyResponse SurveyResponse, string ErrorMessage)> PutSurveyResponseAsync(int Id, Models.SurveyResponse SurveyResponse)
        {
            try
            {
                if (SurveyResponse != null)
                {
                    var _SurveyResponse = await surveyResponseDbContext.SurveyResponses.AsNoTracking().Where(s => s.Id == Id).FirstOrDefaultAsync();

                    if (_SurveyResponse != null)
                    {
                        var response = mapper.Map<Models.SurveyResponse, Db.SurveyResponse>(SurveyResponse);
                        surveyResponseDbContext.Update(response);
                        await surveyResponseDbContext.SaveChangesAsync();
                        return (true, SurveyResponse, "Successful");
                    }
                    else
                    {
                        logger?.LogInformation($"SurveyReponseId = {Id} Not found");
                        return (false, null, "Not Found");
                    }

                }
                else
                {
                    logger?.LogInformation($"SurveyReponseId = {Id} Bad Request");
                    return (false, null, "Bad request");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        public async Task<(bool IsSuccess, Models.SurveyResponse SurveyResponse, string ErrorMessage)> DeleteSurveyResponseAsync(int Id)
        {
            try
            {
                var _SurveyResponse = await surveyResponseDbContext.SurveyResponses.Where(s => s.Id == Id).FirstOrDefaultAsync();

                if (_SurveyResponse != null)
                {
                    surveyResponseDbContext.Remove(_SurveyResponse);
                    await surveyResponseDbContext.SaveChangesAsync();
                    return (true, mapper.Map<Db.SurveyResponse, Models.SurveyResponse>(_SurveyResponse), "Successful");
                }
                else
                {
                    logger?.LogInformation($"SurveyReponseId = {Id} Not found");
                    return (false, null, "Not Found");
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }

        //Method to get Answers by region with surveyId as input parameter
        private async Task<dynamic> getAnswersByRegionAndSurveyIdAsync(List<Db.SurveyResponse> surveyResponses)
        {
            try
            {
                var answersList = await answerServiceProvider.getAnswersAsync();
                if (answersList == null || !answersList.Any())
                    return null;
                //get all the answers for the particular survey
                var surveyAnswers = answersList.Join(
                                       surveyResponses,
                                       answer => answer.SurveyResponseId,
                                       surveyResponse => surveyResponse.Id,
                                       (answer, surveyResponse) => new

                                       {
                                           answer.Id,
                                           answer.QuestionId,
                                           answer.AnswerText,
                                           answer.Comment,
                                           surveyResponse.LocationId,
                                           SurveyResponseId = surveyResponse.Id
                                       });

                if (surveyAnswers == null || !surveyAnswers.Any())
                    return null;

                var regions = await regionServiceProvider.getRegionsAsync();
                var locations = await locationServiceProvider.getLocationsAsync();

                if (regions == null || !regions.Any() || locations == null || !locations.Any())
                    return null;


                //get all the answers based on the locations
                var result = from answer in surveyAnswers
                             from location in locations
                             where answer.LocationId.Equals(location.Id)
                             select new
                             {
                                 answer.Id,
                                 answer.QuestionId,
                                 answer.AnswerText,
                                 answer.Comment,
                                 location.RegionId,
                                 LocationId = location.Id,
                                 answer.SurveyResponseId
                             };



                //group records by answer and region
                var q = from e in result
                        group e by (e.RegionId, e.AnswerText) into g
                        select new
                        {
                            g.Key.RegionId,
                            Answers = new
                            {
                                g.Key.AnswerText,
                                Counter = g.Count()
                            }
                        };

                //build the result

                List<dynamic> resultList = new List<dynamic>();
                foreach (Region region in regions)
                {
                    var answers = from u in q.ToList()
                                  where u.RegionId.Equals(region.Id)
                                  select u.Answers;

                    resultList.Add(new { RegionId = region.Id, region.Name, region.Abbreviation, Answers = answers });
                }
                //return the object result
                return new { Regions = resultList };
            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResultAsync()");
                return null;
            }
        }

        //Method to get Survey Response by ResponseId
        private async Task<dynamic> getSurveyResponseByResponseIdAsync(Db.SurveyResponse surveyResponse)
        {
            try
            {
                var employee = await employeeServiceProvider.getEmployeeAsync(surveyResponse.EmployeeId);
                var answers = await answerServiceProvider.GetAnswersByResponseIdAsync(surveyResponse.Id);
                var allQuestions = await questionServiceProvider.getQuestionsAsync(null);
                var questions = allQuestions.Where(s => s.SurveyId == surveyResponse.SurveyId);
                var attachments = await attachmentServiceProvider.getAttachmentsAsync();
                var Locations = await locationServiceProvider.getLocationsAsync();
                var location = Locations.Where(a => a.Id == surveyResponse.LocationId).FirstOrDefault();
                var result = new{
                    surveyResponse.Id,
                    surveyResponse.SurveyId,
                    surveyResponse.LocationId,
                    surveyResponse.EmployeeId,
                    surveyResponse.ClientDevice,
                    surveyResponse.KeyAnswerResult,
                    DataValue=(location!=null?location.DataValue:0),
                    Enrollment= (location != null ? location.Enrollment : 0),
                    Longitute = (location != null ? location.Longitute : surveyResponse.Longitute),
                    Latitude=(location != null ? location.Latitude : surveyResponse.Latitude),
                    Employee = employee,
                    answers = from ans in answers
                              select new
                              {
                                  ans.QuestionId,
                                  ans.Id,
                                  ans.AnswerText,
                                  ans.Comment,
                                  Questions = (from q in questions where q.Id == ans.QuestionId select new { q.Id, q.QuestionNumber, q.CategoryId, q.Text }).SingleOrDefault(),
                                  Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }
                              }
                };
                return result;
            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResultAsync()");
                return null;
            }
        }


        //Method to get Survey Responses by surveyId
        private async Task<dynamic> getSurveyResponsesBySurveyIdAsync(int surveyId, int employeeid)
        {
            try
            {
                List<Db.SurveyResponse> surveyResonses = null;
                Employee employee = null;
                List<Employee> employees = null;
                if (employeeid == 0)
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId).ToListAsync();
                    employees = await employeeServiceProvider.getEmployeesAsync();
                }
                else
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId && x.EmployeeId == employeeid).ToListAsync();
                    employee = await employeeServiceProvider.getEmployeeAsync(employeeid);
                }
                surveyResonses = surveyResonses
                .OrderByDescending(obj => obj.Id)
                .GroupBy(obj => new { obj.SurveyId, obj.LocationId })//obj.EmployeeId,
                .Select(group => group.FirstOrDefault())  // or .FirstOrDefault() if you want to handle empty groups
                .ToList();
                var answers = await answerServiceProvider.getAnswersAsync();
                var questions = await questionServiceProvider.getQuestionsAsync(null);
                var Locations = await locationServiceProvider.getLocationsAsync();
                var surveyQuestions = from q in questions where q.SurveyId == surveyId select q;

                //var surveyQuestions = await questionServiceProvider.getSurveyQuestionsAsync(surveyId);

                var attachments = await attachmentServiceProvider.getAttachmentsAsync();

                if (employeeid == 0)
                {
                    var result = from r in surveyResonses
                                 join loc in Locations on r.LocationId equals loc.Id
                                 select new
                                 {
                                     r.Id,
                                     r.SurveyId,
                                     r.LocationId,
                                     r.EmployeeId,
                                     r.ClientDevice,
                                    // r.KeyAnswerResult,
                                     loc.DataValue,
                                     loc.Enrollment,
                                     loc.Longitute,
                                     loc.Latitude,
                                    // Employee = (from e in employees where e.Id == r.EmployeeId select new { e.Id, e.Name, e.BirthDate, e.Email, e.OfficePhoneNumber }).SingleOrDefault(),
                                     answers = from ans in answers
                                               where ans.SurveyResponseId == r.Id
                                               select new
                                               {
                                                   ans.Id,
                                                //   ans.QuestionId,
                                                   ans.AnswerText,
                                                   ans.Comment,
                                                   Questions = (from q in surveyQuestions where q.Id == ans.QuestionId select new { q.Id, q.QuestionNumber, q.CategoryId, q.Text }).SingleOrDefault(),
                                                   Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }

                                               }
                                 };
                    return result;
                }
                else
                {
                    object _employee = new { };
                    if (employee != null)
                    {
                        _employee = new { employee.Id, employee.Name, employee.BirthDate, employee.Email, employee.OfficePhoneNumber };
                    }
                    var result = from r in surveyResonses
                                 join loc in Locations on r.LocationId equals loc.Id
                                 select new
                                 {
                                     r.Id,
                                     r.SurveyId,
                                     r.LocationId,
                                     r.EmployeeId,
                                     r.ClientDevice,
                                   //  r.KeyAnswerResult,
                                     loc.DataValue,
                                     loc.Enrollment,
                                     loc.Longitute,
                                     loc.Latitude,
                                   //  Employee = _employee,
                                     answers = from ans in answers
                                               where ans.SurveyResponseId == r.Id
                                               select new
                                               {
                                                   ans.Id,
                                             //      ans.QuestionId,
                                                   ans.AnswerText,
                                                   ans.Comment,
                                                   Questions = (from q in questions where q.Id == ans.QuestionId select new { q.Id, q.QuestionNumber, q.CategoryId, q.Text }).SingleOrDefault(),
                                                   Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }
                                               }
                                 };

                    return result;
                }
            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }


        //Method to get All Survey Responses
        private async Task<dynamic> getAllSurveyResponsesAsync(int employeeid)
        {
            try
            {
                List<Db.SurveyResponse> surveyResonses = null;
                Employee employee = null;
                List<Employee> employees = null;
                object _employee = new { };
                if (employeeid == 0)
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.ToListAsync();
                    employees = await employeeServiceProvider.getEmployeesAsync();
                }
                else
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(x => x.EmployeeId == employeeid).ToListAsync();
                    employee = await employeeServiceProvider.getEmployeeAsync(employeeid);

                    if (employee != null)
                    {
                        _employee = new { employee.Id, employee.Name, employee.BirthDate, employee.Email, employee.OfficePhoneNumber };
                    }
                }


                var answers = await answerServiceProvider.getAnswersAsync();
                var questions = await questionServiceProvider.getQuestionsAsync(null);
                var attachments = await attachmentServiceProvider.getAttachmentsAsync();

                var Locations = await locationServiceProvider.getLocationsAsync();
                var result = from r in surveyResonses
                             join loc in Locations on r.LocationId equals loc.Id
                             select new
                             {
                                 r.Id,
                                 r.SurveyId,
                                 r.LocationId,
                                 r.EmployeeId,
                                 r.ClientDevice,
                               //  r.KeyAnswerResult,
                                 loc.DataValue,
                                 loc.Enrollment,
                                 loc.Longitute,
                                 loc.Latitude,
                               //  Employee = employeeid != 0 ? _employee : (from e in employees where r.EmployeeId == e.Id select new { e.Id, e.Name, e.BirthDate, e.Email, e.OfficePhoneNumber }).SingleOrDefault(),
                                 answers = from ans in answers
                                           where ans.SurveyResponseId == r.Id
                                           select new
                                           {
                                               ans.Id,
                                         //      ans.QuestionId,
                                               ans.AnswerText,
                                               ans.Comment,
                                               Questions = (from q in questions where q.Id == ans.QuestionId select new { q.Id, q.QuestionNumber, q.CategoryId, q.Text }).SingleOrDefault(),
                                               Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }
                                           }
                             };
                return result;


            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }
        //Method to get All Survey Responses for excel export
        private async Task<List<object>> getAllSurveyResponsesExcelAsync(int surveyId, string language, bool isadmin)
        {
            try
            {
                if (string.IsNullOrEmpty(language)) language = "en";
                List<Db.SurveyResponse> surveyResonses;
                surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(a => a.SurveyId == surveyId).ToListAsync();
                var answers = await answerServiceProvider.getAnswersAsync();
                var Locations = await locationServiceProvider.getLocationsAsync();
                var regions = await regionServiceProvider.getRegionsAsync();
                var questions = await questionServiceProvider.getQuestionsAsync(language);
                var categories = await questionServiceProvider.GetQuestionCategoriesAsync(language);
                var attachments = await attachmentServiceProvider.getAttachmentsAsync();
                List<object> questionLists = new List<object>();
                var allques = from res in surveyResonses
                              join loc in Locations on res.LocationId equals loc.Id
                              join reg in regions on loc.RegionId equals reg.Id
                              join ans in answers on res.Id equals ans.SurveyResponseId
                              join q in questions on ans.QuestionId equals q.Id
                              join qc in categories on q.CategoryId equals qc.Id
                              select new
                              {
                                  responseId = res.Id,
                                  questionId = q.Id,
                                  QuestionNumber = q.QuestionNumber,
                                  Category = JsonSerializer.Deserialize<Dictionary<string, string>>(qc.Titles.ToString())[language],
                                  question = q.Text[language],
                                  answerId = ans.Id,
                                  AnswerText = ans.AnswerText,
                                  Comment = ans.Comment,
                                  Location = loc.LocationCode,
                                  school = loc.Name,
                                  Region = reg.Name,
                                  MC = loc.MaintenanceCenter,
                                  ResponseDate = res.CreatedDate,
                                  EmployeeId = res.EmployeeId,
                                  ClientDevice = res.ClientDevice,
                                  Attachments = attachments.Where(a => a.AnswerId == ans.Id).Select(a => a.FileName + "##" + a.URI).ToList()
                              };
                List<object> allresoponses = new List<object>();
                foreach (var item in allques)
                {

                    List<string> ansattachments = item.Attachments.ToList();

                    //// Initialize the attachment dictionary
                    //var attachmentsobject = new Dictionary<string, string>();
                    //for (int i = 0; i < ansattachments.Count; i++)
                    //{
                    //    attachmentsobject["Attachment"+(i+1).ToString()] = ansattachments[i];
                    //}


                    string[] variables = new string[5];
                    for (int i = 0; i < 5; i++) // Assuming you want to assign 5 values
                    {
                        if (i < ansattachments.Count())
                        {
                            variables[i] = ansattachments[i];
                        }
                        else
                        {
                            variables[i] = string.Empty; // or null, or any other default value
                        }
                    }

                    // Now, you can access the values using the variables
                    string att1 = variables[0], att2 = variables[1], att3 = variables[2], att4 = variables[3], att5 = variables[4];
                    object response;
                    if (isadmin)
                    {
                        response = new
                        {
                            SurveyQuestion = item.question,
                            Answer = item.AnswerText,
                            Category = item.Category,
                            School = item.school,
                            Location = item.Location,
                            Region = item.Region,
                            MC = item.MC,
                            ResponseDate = item.ResponseDate.ToString(),
                            Notes = item.Comment,
                            Attachment1 = att1,
                            Attachment2 = att2,
                            Attachment3 = att3,
                            Attachment4 = att4,
                            Attachment5 = att5,
                            User = item.EmployeeId,
                            DeviceType = item.ClientDevice,
                            Reference = item.responseId
                        };
                        // Add the attachment dictionary to the response object
                        // response = new { response, Attachments = attachments };
                    }
                    else
                    {
                        response = new
                        {
                            SurveyQuestion = item.question,
                            Answer = item.AnswerText,
                            Category = item.Category,
                            School = item.school,
                            Location = item.Location,
                            Region = item.Region,
                            MC = item.MC,
                            ResponseDate = item.ResponseDate.ToString(),
                            Notes = item.Comment,
                            Attachment1 = att1,
                            Attachment2 = att2,
                            Attachment3 = att3,
                            Attachment4 = att4,
                            Attachment5 = att5
                        };

                        // Add the attachment dictionary to the response object
                        //  response = new { response, Attachments = attachments };
                    }
                    allresoponses.Add(response);
                }
                return allresoponses;


            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }

        //Method to get Answers By Maintenance Center by surveyId
        private async Task<dynamic> getResultsByMaintenanceCenterAsync(int surveyId, int employeeid)
        {
            try
            {
                List<Db.SurveyResponse> surveyResponses = null;
                if (employeeid == 0)
                {
                    surveyResponses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId).ToListAsync();
                }
                else
                {
                    surveyResponses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId && x.EmployeeId == employeeid).ToListAsync();
                }
                var answers = await answerServiceProvider.getAnswersAsync();
                var locations = await locationServiceProvider.getLocationsAsync();
                var maintenanceCenters = locations.DistinctBy(m => m.MaintenanceCenter);

                //get all the answers for the particular survey
                var surveyAnswers = from resp in surveyResponses
                                    from ans in answers
                                    where ans.SurveyResponseId.Equals(resp.Id)
                                    select new
                                    {
                                        AnswerId = ans.Id,
                                        ans.QuestionId,
                                        ans.AnswerText,
                                        ans.Comment,
                                        resp.LocationId,
                                        ResponseId = resp.Id
                                    };

                //get all the answers with location
                var surveyAnswersLocations = from surveyAns in surveyAnswers
                                             from location in locations
                                             where surveyAns.LocationId.Equals(location.Id)
                                             select new { surveyAns, location.MaintenanceCenter };

                //aggreting the answers
                var aggregatedResult = from e in surveyAnswersLocations
                                       group e by (e.MaintenanceCenter, e.surveyAns.AnswerText) into g
                                       select new
                                       {
                                           g.Key.MaintenanceCenter,
                                           Answers = new
                                           {
                                               g.Key.AnswerText,
                                               Counter = g.Count()
                                           }
                                       };

                //format the result foreach maintenance center
                List<dynamic> results = new List<dynamic>();
                foreach (Location m in maintenanceCenters)
                {
                    var _answers = aggregatedResult.Where(x => x.MaintenanceCenter.Equals(m.MaintenanceCenter)).Select(x => x.Answers).ToList();
                    results.Add(new { m.Name, m.Id, m.RegionId, m.MaintenanceCenter, m.SchoolType, Answers = _answers });
                }

                return results;
            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }

        //Method to get Survey Responses by surveyId and LocationId
        private async Task<dynamic> getSurveyResponsesBySurveyIdLocationIdAsync(int surveyId, int locationId, int employeeid)
        {
            try
            {
                List<Db.SurveyResponse> surveyResonses = null;
                Employee employee = null;
                List<Employee> employees = null;
                object _employee = new { };

                if (employeeid == 0)
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId && x.LocationId == locationId).ToListAsync();
                    employees = await employeeServiceProvider.getEmployeesAsync();
                }
                else
                {
                    surveyResonses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == surveyId && x.EmployeeId == employeeid && x.LocationId == locationId).ToListAsync();
                    employee = await employeeServiceProvider.getEmployeeAsync(employeeid);

                    if (employee != null)
                    {
                        _employee = new { employee.Id, employee.Name, employee.BirthDate, employee.Email, employee.OfficePhoneNumber };
                    }
                }
                surveyResonses = surveyResonses
                .OrderByDescending(obj => obj.Id)
                .GroupBy(obj => new { obj.SurveyId,  obj.LocationId }) //obj.EmployeeId,
                .Select(group => group.FirstOrDefault())  // or .FirstOrDefault() if you want to handle empty groups
                .ToList();
                var answers = await answerServiceProvider.getAnswersAsync();
                var questions = await questionServiceProvider.getQuestionsAsync(null);
                var surveyQuestions = from q in questions where q.SurveyId == surveyId select q;
                var attachments = await attachmentServiceProvider.getAttachmentsAsync();

                var Locations = await locationServiceProvider.getLocationsAsync();
                var result = from r in surveyResonses
                             join loc in Locations on r.LocationId equals loc.Id
                             select new
                             {
                                 r.Id,
                                 r.SurveyId,
                                 r.LocationId,
                                 r.EmployeeId,
                                 r.ClientDevice,
                               //  r.KeyAnswerResult,
                                 loc.DataValue,
                                 loc.Enrollment,
                                 loc.Longitute,
                                 loc.Latitude,
                               //  Employee = employeeid != 0 ? _employee : (from e in employees where r.EmployeeId == e.Id select new { e.Id, e.Name, e.BirthDate, e.Email, e.OfficePhoneNumber }).SingleOrDefault(),
                                 answers = from ans in answers
                                           where ans.SurveyResponseId == r.Id

                                           select new
                                           {
                                          //     ans.QuestionId,
                                               ans.Id,
                                               ans.AnswerText,
                                               ans.Comment,
                                               Questions = (from q in surveyQuestions where q.Id == ans.QuestionId select new { q.Id, q.QuestionNumber, q.CategoryId, q.Text }).SingleOrDefault(),
                                               Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }
                                           }
                             };
                return result;

            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }


        //Method to get Survey Responses by surveyId questionId and answer
        private async Task<dynamic> getSurveyResponsesByAnswerAsync(Survey survey, Question question, string answer, int employeeid)
        {
            try
            {
                List<Db.SurveyResponse> surveyResponses = null;
                Employee employee = null;
                List<Employee> employees = null;
                object _employee = new { };

                if (employeeid == 0)
                {
                    surveyResponses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == survey.Id).ToListAsync();
                    employees = await employeeServiceProvider.getEmployeesAsync();
                }
                else
                {
                    surveyResponses = await surveyResponseDbContext.SurveyResponses.Where(x => x.SurveyId == survey.Id && x.EmployeeId == employeeid).ToListAsync();
                    employee = await employeeServiceProvider.getEmployeeAsync(employeeid);

                    if (employee != null)
                    {
                        _employee = new { employee.Id, employee.Name, employee.BirthDate, employee.Email, employee.OfficePhoneNumber };
                    }
                }
                surveyResponses = surveyResponses
                .OrderByDescending(obj => obj.Id)
                .GroupBy(obj => new { obj.SurveyId, obj.LocationId })//, obj.EmployeeId
                .Select(group => group.FirstOrDefault())  // or .FirstOrDefault() if you want to handle empty groups
                .ToList();
                //var surveyResponses = await surveyResponseDbContext.Responses.Where(x => x.SurveyId == survey.Id).ToListAsync();
                // var employees = await employeeServiceProvider.getEmployeesAsync();
                var answers = await answerServiceProvider.getAnswersAsync();
                var attachments = await attachmentServiceProvider.getAttachmentsAsync();
                var Locations = await locationServiceProvider.getLocationsAsync();
                var result = from r in surveyResponses
                             join loc in Locations on r.LocationId equals loc.Id
                             select new
                             {
                                 r.Id,
                                 r.SurveyId,
                                 r.LocationId,
                                 r.EmployeeId,
                                 r.ClientDevice,
                             //    r.KeyAnswerResult,
                                 loc.DataValue,
                                 loc.Enrollment,
                                 loc.Longitute,
                                 loc.Latitude,
                               //  Employee = employeeid != 0 ? _employee : (from e in employees where r.EmployeeId == e.Id select new { e.Id, e.Name, e.BirthDate, e.Email, e.OfficePhoneNumber }).SingleOrDefault(),
                                 answers = from ans in answers
                                           where ans.SurveyResponseId == r.Id
                                           && ans.QuestionId == question.Id
                                           && ans.AnswerText.ToLower().Equals(answer.ToLower())

                                           select new
                                           {
                                              // ans.QuestionId,
                                               AnswerId = ans.Id,
                                               ans.AnswerText,
                                               ans.Comment,
                                               Question = question,
                                               Attachments = from att in attachments where att.AnswerId == ans.Id select new { att.Id, att.URI }
                                           }
                             };
                return result;
            }
            catch (Exception ex)
            {
                logger?.LogError($"Exception Found : {ex.Message} - Ref: SurveyResponsesProvider.getSurveyResponseBySurveyIdAsync()");
                return null;
            }
        }


        async Task<bool> ProcessAnswers(AnswerRequest answerRequest, int surveyResponseId)
        {
            if (answerRequest != null)
            {
                var answer = await answerServiceProvider.PostAnswersAsync(new Models.Answer { QuestionId = answerRequest.QuestionId, AnswerText = answerRequest.AnswerText, Comment = answerRequest.Comment, SurveyResponseId = surveyResponseId });
                if (answer != null)
                {
                    List<AnswerInfo> listAnswerInfo = new List<AnswerInfo>();
                    listAnswerInfo.Add(new AnswerInfo { AnswerId = answer.Id, postedFiles = answerRequest.PostedFiles });
                    var attachments = attachmentServiceProvider.PostAttachmentsAsync(new AttachmentInfo { ResponseId = surveyResponseId, Answers = listAnswerInfo });

                    string message = $"Answer for question {answerRequest.QuestionId} saved to the database";
                    logger?.LogInformation(message);
                    return (true);
                }
                else
                {
                    string message = $"Answer for question {answerRequest.QuestionId} cannot be saved to the database";
                    logger?.LogInformation(message);
                    return (false);
                }
            }
            else
            {
                var message = $"Answer for question {answerRequest.QuestionId} cannot be saved to the database - answerRequest object is null";
                logger?.LogInformation(message);
                return (false);
            }
        }


        public async Task<(bool IsSuccess, Models.SurveyResponse SurveyResponse, string ErrorMessage)> PostSurveyAnswersAsync(Models.Request request)
        {
            try
            {
                if (request != null)
                {
                    var response = await PostSurveyResponseAsync(new Models.SurveyResponse { SurveyId = request.SurveyId, EmployeeId = request.EmployeeId, LocationId = request.LocationId, ClientDevice = request.ClientDevice, KeyAnswerResult = request.KeyAnswerResult, Latitude = Convert.ToDouble(request.Latitude), Longitute = Convert.ToDouble(request.Longitute), CreatedDate = DateTime.Now });
                    if (response.IsSuccess)
                    {
                        var surveyResponse = response.SurveyResponse;
                        var tasks = request.Answers.Select(x => ProcessAnswers(x, surveyResponse.Id));
                        await Task.WhenAll(tasks);
                        return (true, surveyResponse, null);
                    }
                    else
                    {
                        var message = "Survey answers cannot be saved to the database";
                        logger?.LogInformation(message);
                        return (false, null, message);
                    }
                }
                else
                {
                    var message = "Request failed because null object is submitted";
                    logger?.LogInformation(message);
                    return (false, null, message);
                }
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return (false, null, ex.Message);
            }
        }
    }
}