source: src/FinkiChattery/FinkiChattery.Persistence/Repositories/Implementations/QuestionRepo.cs@ ad0fcd3

using FinkiChattery.Persistence.Context;
using FinkiChattery.Persistence.Models;
using FinkiChattery.Persistence.Repositories.Contracts;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace FinkiChattery.Persistence.Repositories
{
    public class QuestionRepo : Repository<Question>, IQuestionRepo
    {
        public QuestionRepo(ApplicationDbContext dbContext) : base(dbContext)
        {
        }

        public async Task<List<QuestionPreviewDto>> GetPreviewQuestionsLatest()
        {
            Expression<Func<Question, DateTime>> orderBy = x => x.CreatedOn;
            return await GetPreviewQuestions(orderBy);
        }

        public async Task<List<QuestionPreviewDto>> GetPreviewQuestionsPopular()
        {
            Expression<Func<Question, long>> orderBy = x => x.Views;
            return await GetPreviewQuestions(orderBy);
        }

        private async Task<List<QuestionPreviewDto>> GetPreviewQuestions<T>(Expression<Func<Question, T>> orderBy)
        {
            return await DbSet
                .AsNoTracking()
                .Include(x => x.QuestionCategories).ThenInclude(x => x.Category)
                .OrderByDescending(orderBy)
                .Select(x => new QuestionPreviewDto(x.Id,
                                                    x.Uid,
                                                    x.Title,
                                                    x.Views,
                                                    x.AnswersCount,
                                                    x.CreatedOn,
                                                    x.QuestionCategories.Select(y => new QuestionPreviewCategoryDto(y.Id, y.Uid, y.Category.Name))))
                .Skip(0).Take(30)
                .ToListAsync();
        }

        public async Task<QuestionStateDto> GetQuestionState(Guid questionUid)
        {
            // TODO: MAYBE WRITE THIS QUERY AS SP ??
            var questionDto = await DbSet
              .AsNoTracking()
              .Include(x => x.Student)
              .Include(x => x.Team)
              .Include(x => x.Answers).ThenInclude(y => y.Student)
              .Include(x => x.Answers).ThenInclude(y => y.AnswerResponses).ThenInclude(y => y.Student)
              .Include(x => x.QuestionCategories).ThenInclude(y => y.Category)
              .Where(x => x.Uid == questionUid)
              .Select(x => new QuestionStateDto(
                                                x.Id,
                                                x.Uid,
                                                x.Title,
                                                x.Text,
                                                x.CreatedOn,
                                                x.Views,
                                                x.LastActiveOn,
                                                new StudentQuestionStateDto(
                                                  x.Student.Id,
                                                  x.Student.Uid,
                                                  x.Student.IndexNumber,
                                                  x.Student.ImageUrl,
                                                  x.Student.Reputation),
                                                x.Answers.Select(y =>
                                                new AnswerQuestionStateDto(
                                                    y.Id,
                                                    y.Uid,
                                                    y.Text,
                                                    y.CorrectAnswer,
                                                    y.CreatedOn,
                                                    y.VotesCount,
                                                    new AnswerStudentQuestionStateDto(
                                                        y.Student.Id,
                                                        y.Student.Uid,
                                                        y.Student.IndexNumber,
                                                        y.Student.ImageUrl,
                                                        y.Student.Reputation),
                                                    y.AnswerResponses.Select(z =>
                                                    new AnswerResponseQuestionStateDto(
                                                        z.Id,
                                                        z.Uid,
                                                        z.Text,
                                                        z.CreatedOn,
                                                        new AnswerResponseStudentQuestionStateDto(
                                                            z.Student.Id,
                                                            z.Student.Uid,
                                                            z.Student.IndexNumber,
                                                            z.Student.ImageUrl,
                                                            z.Student.Reputation))))),
                                                x.QuestionCategories.Select(y =>
                                                new QuestionCategoryQuestionStateDto(
                                                    y.Id,
                                                    y.Uid,
                                                    y.Category.Name)),
                                                x.Team == null ? null : new TeamQuestionStateDto(
                                                    x.Team.Id,
                                                    x.Team.Uid,
                                                    x.Team.Name)))
              .FirstOrDefaultAsync();

            return questionDto;
        }

        public async Task<List<QuestionPreviewDto>> SearchQuestions(string searchText, IEnumerable<Guid> categories)
        {
            var search = Regex.Replace(searchText, "[\\\\/:*?\"<>\\]\\[|&'`~^=%,(){}_\\-]", " ")
                                     .Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)
                                     .Select(c => $"\"{c}*\"");

            var searchString = string.Join(" AND ", search);

            var rawQuery = (IQueryable<Question>)
                DbSet.FromSqlRaw(@"
                        SELECT [q].[Id], [q].[Uid], [q].[Title], [q].[Views], [q].[AnswersCount], [q].[CreatedOn] 
                        FROM [dbo].[Question] AS [q]
                        INNER JOIN CONTAINSTABLE(Question, Search, @searchString, 30) ccontains ON [q].[Id] = ccontains.[KEY]",
                        new SqlParameter("searchString", searchString))
                .Include(x => x.QuestionCategories).ThenInclude(x => x.Category);

            if (categories.Any())
            {
                rawQuery = rawQuery.Where(x => x.QuestionCategories.Any(y => categories.Contains(y.Category.Uid)));
            }

            return await rawQuery
                .Select(x => new QuestionPreviewDto(x.Id,
                                    x.Uid,
                                    x.Title,
                                    x.Views,
                                    x.AnswersCount,
                                    x.CreatedOn,
                                    x.QuestionCategories.Select(y => new QuestionPreviewCategoryDto(y.Id, y.Uid, y.Category.Name))))
                .ToListAsync();
        }
    }
}