step3/api/QuestionsApi/Services/QuestionStore.cs

using MongoDB.Driver;
using MongoDB.Bson;
using QuestionsApi.Models;

namespace QuestionsApi.Services;

public class QuestionStore
{
    private const string MediaReviewReason = "Contains media - pending review";
    private const string MediaReviewFlaggedBy = "system";

    private readonly IMongoCollection<Question> _col;

    public QuestionStore(MongoDatabaseProvider mongo)
    {
        _col = mongo.Database.GetCollection<Question>("Questions");

        var externalIdIndex = Builders<Question>.IndexKeys.Ascending(q => q.ExternalId);
        _col.Indexes.CreateOne(new CreateIndexModel<Question>(externalIdIndex, new CreateIndexOptions { Unique = true }));
        var systemIndex = Builders<Question>.IndexKeys.Ascending(q => q.System).Ascending(q => q.IsPublished);
        _col.Indexes.CreateOne(new CreateIndexModel<Question>(systemIndex));
        var tagsIndex = Builders<Question>.IndexKeys.Ascending(q => q.Tags);
        _col.Indexes.CreateOne(new CreateIndexModel<Question>(tagsIndex));
    }

    public async Task<List<Question>> SearchAsync(QuestionSearchRequest req, bool includeUnpublished)
    {
        var builder = Builders<Question>.Filter;
        var filters = new List<FilterDefinition<Question>>();

        if (!includeUnpublished)
        {
            filters.Add(builder.Eq(q => q.IsPublished, true));
            filters.Add(builder.Eq(q => q.FlaggedForReviewUtc, null));
        }
        if (!string.IsNullOrWhiteSpace(req.System) && req.System != "All Systems")
            filters.Add(builder.Eq(q => q.System, req.System.Trim()));
        if (!string.IsNullOrWhiteSpace(req.Discipline))
            filters.Add(builder.Eq(q => q.Discipline, req.Discipline.Trim()));
        if (req.Tags.Count > 0)
            filters.Add(builder.All(q => q.Tags, req.Tags.Where(t => !string.IsNullOrWhiteSpace(t)).Select(t => t.Trim())));
        if (req.DifficultyMin is not null)
            filters.Add(builder.Gte(q => q.Difficulty, req.DifficultyMin.Value));
        if (req.DifficultyMax is not null)
            filters.Add(builder.Lte(q => q.Difficulty, req.DifficultyMax.Value));
        if (req.RequireMedia)
            filters.Add(builder.Exists("Media.0", true));
        var excludedIds = req.ExcludeExternalIds
            .Where(id => !string.IsNullOrWhiteSpace(id))
            .Select(id => id.Trim())
            .Distinct(StringComparer.Ordinal)
            .ToList();
        if (excludedIds.Count > 0)
            filters.Add(builder.Nin(q => q.ExternalId, excludedIds));

        var filter = filters.Count == 0 ? builder.Empty : builder.And(filters);
        var limit = Math.Clamp(req.Limit, 1, 80);
        return await _col.Aggregate()
            .Match(filter)
            .Sample(limit)
            .ToListAsync();
    }

    public async Task<Question?> GetByIdAsync(string id) =>
        await _col.Find(q => q.Id == id).FirstOrDefaultAsync();

    public async Task<Question?> GetByExternalIdAsync(string externalId) =>
        await _col.Find(q => q.ExternalId == externalId).FirstOrDefaultAsync();

    public async Task<List<Question>> GetAllAsync() =>
        await _col.Find(Builders<Question>.Filter.Empty).ToListAsync();

    public async Task<List<Question>> GetPublishedAsync() =>
        await _col.Find(question => question.IsPublished && question.FlaggedForReviewUtc == null).ToListAsync();

    public async Task<Dictionary<string, Question>> GetByExternalIdsAsync(IEnumerable<string> externalIds)
    {
        var ids = externalIds.Distinct(StringComparer.Ordinal).ToList();
        var questions = await _col.Find(question => ids.Contains(question.ExternalId)).ToListAsync();
        return questions.ToDictionary(question => question.ExternalId, StringComparer.Ordinal);
    }

    public async Task<(List<Question> Items, string? NextCursor, bool HasMore)> ExportPageAsync(
        string? after,
        int requestedLimit)
    {
        var limit = Math.Clamp(requestedLimit, 1, 200);
        var filter = Builders<Question>.Filter.Empty;
        if (!string.IsNullOrWhiteSpace(after))
        {
            if (!ObjectId.TryParse(after, out var cursor))
                throw new ArgumentException("after must be a valid question cursor", nameof(after));
            filter = new BsonDocument("_id", new BsonDocument("$gt", cursor));
        }

        var page = await _col.Find(filter)
            .Sort(Builders<Question>.Sort.Ascending("_id"))
            .Limit(limit + 1)
            .ToListAsync();
        var hasMore = page.Count > limit;
        if (hasMore)
            page.RemoveAt(page.Count - 1);
        return (page, hasMore ? page[^1].Id : null, hasMore);
    }

    public async Task<Question?> CreateAsync(UpsertQuestionRequest req)
    {
        var question = FromRequest(req);
        question.CreatedUtc = DateTime.UtcNow;
        question.UpdatedUtc = DateTime.UtcNow;
        ApplyMediaReviewGate(question);

        try
        {
            await _col.InsertOneAsync(question);
            return question;
        }
        catch (MongoWriteException ex) when (ex.WriteError.Category == ServerErrorCategory.DuplicateKey)
        {
            return null;
        }
    }

    public async Task<Question?> UpdateAsync(string id, UpsertQuestionRequest req)
    {
        var question = FromRequest(req);
        question.Id = id;
        question.UpdatedUtc = DateTime.UtcNow;

        var existing = await GetByIdAsync(id);
        if (existing is null)
            return null;

        question.CreatedUtc = existing.CreatedUtc;
        question.FlaggedForReviewUtc = existing.FlaggedForReviewUtc;
        question.FlagReason = existing.FlagReason;
        question.FlaggedByUserId = existing.FlaggedByUserId;
        ApplyMediaReviewGate(question);
        var result = await _col.ReplaceOneAsync(q => q.Id == id, question);
        return result.MatchedCount == 0 ? null : question;
    }

    public async Task<int> UpsertManyByExternalIdAsync(IEnumerable<UpsertQuestionRequest> requests)
    {
        var count = 0;
        foreach (var req in requests)
        {
            var question = FromRequest(req);
            question.UpdatedUtc = DateTime.UtcNow;

            var filter = Builders<Question>.Filter.Eq(q => q.ExternalId, question.ExternalId);
            var existing = await _col.Find(filter).FirstOrDefaultAsync();
            if (existing is not null)
            {
                question.Id = existing.Id;
                question.CreatedUtc = existing.CreatedUtc;
                question.FlaggedForReviewUtc = existing.FlaggedForReviewUtc;
                question.FlagReason = existing.FlagReason;
                question.FlaggedByUserId = existing.FlaggedByUserId;
            }
            else
            {
                question.Id = ObjectId.GenerateNewId().ToString();
                question.CreatedUtc = DateTime.UtcNow;
            }
            ApplyMediaReviewGate(question);

            await _col.ReplaceOneAsync(filter, question, new ReplaceOptions { IsUpsert = true });
            count++;
        }

        return count;
    }

    public async Task<bool> DeleteAsync(string id)
    {
        var result = await _col.DeleteOneAsync(q => q.Id == id);
        return result.DeletedCount > 0;
    }

    public async Task<List<string>> GetSystemsAsync(bool includeUnpublished)
    {
        var filter = includeUnpublished
            ? Builders<Question>.Filter.Empty
            : Builders<Question>.Filter.Eq(q => q.IsPublished, true) & Builders<Question>.Filter.Eq(q => q.FlaggedForReviewUtc, null);
        var systems = await _col.Distinct(q => q.System, filter).ToListAsync();
        return systems.Where(s => !string.IsNullOrWhiteSpace(s)).OrderBy(s => s).ToList();
    }

    public async Task<List<string>> GetMissingExternalIdsAsync(IEnumerable<string> externalIds)
    {
        var requested = externalIds.Distinct(StringComparer.Ordinal).ToList();
        var existing = await _col.Find(Builders<Question>.Filter.In(q => q.ExternalId, requested))
            .Project(q => q.ExternalId)
            .ToListAsync();
        return requested.Except(existing, StringComparer.Ordinal).OrderBy(id => id).ToList();
    }

    // One-time bulk sweep: flags every question that has at least one image and isn't already
    // flagged, in a single update rather than fetching everything into memory.
    public async Task<long> FlagAllWithMediaAsync(string flaggedByUserId, string reason)
    {
        var filter = Builders<Question>.Filter.Exists("Media.0", true)
            & Builders<Question>.Filter.Eq(q => q.FlaggedForReviewUtc, null);
        var update = Builders<Question>.Update
            .Set(q => q.FlaggedForReviewUtc, DateTime.UtcNow)
            .Set(q => q.FlagReason, reason)
            .Set(q => q.FlaggedByUserId, flaggedByUserId);
        var result = await _col.UpdateManyAsync(filter, update);
        return result.ModifiedCount;
    }

    public async Task<Question?> FlagAsync(string id, string reason, string flaggedByUserId)
    {
        var update = Builders<Question>.Update
            .Set(q => q.FlaggedForReviewUtc, DateTime.UtcNow)
            .Set(q => q.FlagReason, reason)
            .Set(q => q.FlaggedByUserId, flaggedByUserId);
        return await _col.FindOneAndUpdateAsync(q => q.Id == id, update,
            new FindOneAndUpdateOptions<Question> { ReturnDocument = ReturnDocument.After });
    }

    public async Task<Question?> UnflagAsync(string id)
    {
        var update = Builders<Question>.Update
            .Set(q => q.FlaggedForReviewUtc, null)
            .Set(q => q.FlagReason, "")
            .Set(q => q.FlaggedByUserId, "");
        return await _col.FindOneAndUpdateAsync(q => q.Id == id, update,
            new FindOneAndUpdateOptions<Question> { ReturnDocument = ReturnDocument.After });
    }

    public async Task<List<Question>> GetFlaggedAsync() =>
        await _col.Find(q => q.FlaggedForReviewUtc != null)
            .SortByDescending(q => q.FlaggedForReviewUtc)
            .ToListAsync();

    public async Task<int> UpdateReferencesByExternalIdAsync(IEnumerable<QuestionReferencesUpdate> updates)
    {
        var now = DateTime.UtcNow;
        var writes = updates.Select(update =>
            new UpdateOneModel<Question>(
                Builders<Question>.Filter.Eq(question => question.ExternalId, update.ExternalId.Trim()),
                Builders<Question>.Update
                    .Set(question => question.References, NormalizeReferences(update.References))
                    .Set(question => question.UpdatedUtc, now)))
            .ToList();
        if (writes.Count == 0)
            return 0;

        var result = await _col.BulkWriteAsync(writes, new BulkWriteOptions { IsOrdered = true });
        return (int)result.MatchedCount;
    }

    // Questions with media must be reviewed before reaching students - gate any question that
    // gains media and isn't already flagged, so newly created/imported/edited questions can't
    // slip past the media-review requirement the way a one-time sweep alone would miss.
    internal static void ApplyMediaReviewGate(Question question)
    {
        if (question.Media.Count == 0 || question.FlaggedForReviewUtc is not null)
            return;
        question.FlaggedForReviewUtc = DateTime.UtcNow;
        question.FlagReason = MediaReviewReason;
        question.FlaggedByUserId = MediaReviewFlaggedBy;
    }

    private static Question FromRequest(UpsertQuestionRequest req) => new()
    {
        ExternalId = req.ExternalId.Trim(),
        Stem = req.Stem.Trim(),
        LeadIn = req.LeadIn.Trim(),
        Choices = req.Choices,
        CorrectChoiceId = req.CorrectChoiceId.Trim(),
        Explanation = req.Explanation.Trim(),
        KeyTakeaway = req.KeyTakeaway.Trim(),
        UsmlePearl = req.UsmlePearl.Trim(),
        ReviewFollowups = req.ReviewFollowups,
        ChoiceExplanations = req.ChoiceExplanations,
        WrongAnswerFollowups = req.WrongAnswerFollowups,
        ChoiceSelectionPercentages = req.ChoiceSelectionPercentages,
        Discipline = req.Discipline.Trim(),
        System = req.System.Trim(),
        PerformanceCategory = RatingCategory.Normalize(req.PerformanceCategory, req.System, req.Discipline),
        Difficulty = req.Difficulty,
        Labs = req.Labs,
        Media = req.Media,
        Tags = req.Tags.Where(t => !string.IsNullOrWhiteSpace(t)).Select(t => t.Trim()).Distinct(StringComparer.OrdinalIgnoreCase).ToList(),
        References = NormalizeReferences(req.References),
        IsPublished = req.IsPublished
    };

    private static List<QuestionReference> NormalizeReferences(IEnumerable<QuestionReference>? references) =>
        (references ?? []).Select(reference => new QuestionReference
        {
            Title = reference.Title.Trim(),
            Publisher = reference.Publisher.Trim(),
            Url = reference.Url.Trim(),
            Supports = reference.Supports.Trim(),
            AccessedOn = reference.AccessedOn.Trim(),
            Reviewer = reference.Reviewer.Trim()
        }).ToList();
}