import { Investigation } from "@models/investigation/investigation.model";
import { GRADE_MAX, GRADE_MIN, INVESTIGATIONS_FETCH_ALL_LIMIT, InvestigationSectionType, } from "@typez/investigation";
import { getLogger } from "@utils/asyncLocalStorage";
import { buildGroupSortFields, buildSectionSortFields, buildSortSpec, InvestigationSortingHelper, URL_LOOKUP_STAGES, } from "@utils/investigation";
import { subjectsFactory } from "@utils/subjectsFactory";
import { batchFetchUserProfiles, enrichUserItems } from "@utils/user-profile.utils";
import { Types } from "mongoose";
/**
* Builds MongoDB aggregation stage for relevance scoring with priorities
* Priority order: Title (highest) > Curriculum/Unit/Lesson/Grade > Objectives/Goals/AnalyticalFacts/NGSS > Day/Steps/Objects
* Exact matches score higher than partial matches
* @category Services
* @param {string} escapedSearchTerm - Escaped search term for regex matching
* @returns {object} MongoDB $addFields stage with relevanceScore calculation
*/
function buildRelevanceScoreStage(escapedSearchTerm) {
const searchTermLower = escapedSearchTerm.toLowerCase();
const exactMatchRegex = `^${searchTermLower}$`;
const partialMatchRegex = searchTermLower;
// Helper to safely convert field value to string (handles arrays, nulls, etc.)
// Inline function that returns the expression object for a given field path
const safeToStringExpr = (fieldPath) => ({
$cond: {
if: { $isArray: fieldPath },
then: {
$ifNull: [{ $toString: { $arrayElemAt: [fieldPath, 0] } }, ""],
},
else: {
$ifNull: [
{
$convert: {
input: fieldPath,
to: "string",
onError: "",
onNull: "",
},
},
"",
],
},
},
});
// Helper to create a regex match expression for a field path
const createRegexMatch = (fieldPath, regexPattern) => ({
$regexMatch: {
input: {
$toLower: safeToStringExpr(fieldPath),
},
regex: regexPattern,
},
});
// Helper to check if any element in an array field matches the regex
const arrayMatchExists = (arrayFieldPath, elementAlias, elementFieldPath, regexPattern) => ({
$gt: [
{
$size: {
$filter: {
input: {
$cond: {
if: { $isArray: arrayFieldPath },
then: arrayFieldPath,
else: [],
},
},
as: elementAlias,
cond: createRegexMatch(elementFieldPath, regexPattern),
},
},
},
0,
],
});
const scoreExpressions = [
// Title exact match (highest priority: 1000 points)
{
$cond: [createRegexMatch("$title.value", exactMatchRegex), 1000, 0],
},
// Title partial match (high priority: 500 points)
{
$cond: [createRegexMatch("$title.value", partialMatchRegex), 500, 0],
},
// High-priority fields exact match (curriculum, unit, lesson, grade: 300 points)
{
$cond: [
{
$or: [
createRegexMatch("$curriculum.value", exactMatchRegex),
createRegexMatch("$unitNumberAndTitle.value", exactMatchRegex),
createRegexMatch("$lessonNumberAndTitle.value", exactMatchRegex),
createRegexMatch("$grade.value", exactMatchRegex),
],
},
300,
0,
],
},
// High-priority fields partial match (curriculum, unit, lesson, grade: 150 points)
{
$cond: [
{
$or: [
createRegexMatch("$curriculum.value", partialMatchRegex),
createRegexMatch("$unitNumberAndTitle.value", partialMatchRegex),
createRegexMatch("$lessonNumberAndTitle.value", partialMatchRegex),
createRegexMatch("$grade.value", partialMatchRegex),
],
},
150,
0,
],
},
// Medium-priority fields exact match (objectives, goals, analyticalFacts, ngss: 100 points)
{
$cond: [
{
$or: [
createRegexMatch("$objectives.value", exactMatchRegex),
createRegexMatch("$goals.value", exactMatchRegex),
createRegexMatch("$analyticalFacts.value", exactMatchRegex),
createRegexMatch("$ngss.value", exactMatchRegex),
],
},
100,
0,
],
},
// Medium-priority fields partial match (objectives, goals, analyticalFacts, ngss: 50 points)
{
$cond: [
{
$or: [
createRegexMatch("$objectives.value", partialMatchRegex),
createRegexMatch("$goals.value", partialMatchRegex),
createRegexMatch("$analyticalFacts.value", partialMatchRegex),
createRegexMatch("$ngss.value", partialMatchRegex),
],
},
50,
0,
],
},
// Lower-priority fields: Day exact match (50 points)
{
$cond: [createRegexMatch("$day.value", exactMatchRegex), 50, 0],
},
// Lower-priority fields: Day partial match (25 points)
{
$cond: [createRegexMatch("$day.value", partialMatchRegex), 25, 0],
},
// Steps: title exact match (50 points)
{
$cond: [arrayMatchExists("$steps", "step", "$$step.title.value", exactMatchRegex), 50, 0],
},
// Steps: title partial match (25 points)
{
$cond: [arrayMatchExists("$steps", "step", "$$step.title.value", partialMatchRegex), 25, 0],
},
// Steps: descriptionEn exact match (50 points)
{
$cond: [
arrayMatchExists("$steps", "step", "$$step.descriptionEn.value", exactMatchRegex),
50,
0,
],
},
// Steps: descriptionEn partial match (25 points)
{
$cond: [
arrayMatchExists("$steps", "step", "$$step.descriptionEn.value", partialMatchRegex),
25,
0,
],
},
// Objects: name exact match (50 points)
{
$cond: [arrayMatchExists("$objects", "obj", "$$obj.name.value", exactMatchRegex), 50, 0],
},
// Objects: name partial match (25 points)
{
$cond: [arrayMatchExists("$objects", "obj", "$$obj.name.value", partialMatchRegex), 25, 0],
},
];
return {
$addFields: {
relevanceScore: {
$add: scoreExpressions,
},
},
};
}
/**
* Builds MongoDB query from search filters
* @param {IInvestigationSearchDto} searchDto - The search and filter parameters
* @returns {{ query: Record<string, unknown>, hasSearch: boolean, escapedSearchTerm: string }} Query object and search metadata
*/
function buildInvestigationQuery(searchDto) {
const query = {};
let hasSearch = false;
let escapedSearchTerm = "";
// Search filter
if (searchDto.search && searchDto.search.trim()) {
hasSearch = true;
const searchTerm = searchDto.search.trim();
escapedSearchTerm = searchTerm.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
const searchRegex = new RegExp(escapedSearchTerm, "i");
const searchFields = [
"title.value",
"curriculum.value",
"unitNumberAndTitle.value",
"lessonNumberAndTitle.value",
"grade.value",
"objectives.value",
"goals.value",
"analyticalFacts.value",
"ngss.value",
"day.value",
"steps.title.value",
"steps.descriptionEn.value",
"steps.desiredOutcome.value",
"steps.alternativeOutcome.value",
"steps.name",
"objects.name.value",
"objects.objectId.value",
];
query.$or = searchFields.map((field) => ({ [field]: { $regex: searchRegex } }));
}
// Status filter
if (searchDto.status?.length) {
query.status = { $in: searchDto.status };
}
// Curriculum filter
if (searchDto.curriculum?.length) {
query["curriculum.value"] = { $in: searchDto.curriculum };
}
// Grade range filter
// Generate array of valid grade strings within the range
if (searchDto.gradeFrom !== undefined || searchDto.gradeTo !== undefined) {
const from = searchDto.gradeFrom ?? GRADE_MIN; // Default to minimum grade
const to = searchDto.gradeTo ?? GRADE_MAX; // Default to maximum grade
// Generate array of grade strings: ["1", "2", "3", ..., "12"]
const validGrades = [];
for (let grade = from; grade <= to; grade++) {
validGrades.push(String(grade));
}
query["grade.value"] = { $in: validGrades };
}
// Date created filter
if (searchDto.dateCreatedFrom || searchDto.dateCreatedTo) {
const dateQuery = {};
if (searchDto.dateCreatedFrom) {
dateQuery.$gte = new Date(searchDto.dateCreatedFrom);
}
if (searchDto.dateCreatedTo) {
dateQuery.$lte = new Date(searchDto.dateCreatedTo);
}
query["metadata.dateOfCreation"] = dateQuery;
}
// Date published filter (only show published investigations)
if (searchDto.datePublishedFrom || searchDto.datePublishedTo) {
const dateQuery = {
$ne: null, // Must have a published date
};
if (searchDto.datePublishedFrom) {
dateQuery.$gte = new Date(searchDto.datePublishedFrom);
}
if (searchDto.datePublishedTo) {
dateQuery.$lte = new Date(searchDto.datePublishedTo);
}
query["metadata.dateOfPublishing"] = dateQuery;
}
// Sent to development filter (only show in_development status)
if (searchDto.sentToDevelopmentFrom || searchDto.sentToDevelopmentTo) {
query.status = "in_development";
const dateQuery = {
$ne: null,
};
if (searchDto.sentToDevelopmentFrom) {
dateQuery.$gte = new Date(searchDto.sentToDevelopmentFrom);
}
if (searchDto.sentToDevelopmentTo) {
dateQuery.$lte = new Date(searchDto.sentToDevelopmentTo);
}
query["metadata.dateOfDevelopmentDelivery"] = dateQuery;
}
// Editors filter
if (searchDto.editors?.length) {
query["metadata.editors"] = {
$in: searchDto.editors.map((id) => new Types.ObjectId(id)),
};
}
// Authors filter
if (searchDto.authors?.length) {
query["metadata.author"] = {
$in: searchDto.authors.map((id) => new Types.ObjectId(id)),
};
}
return { query, hasSearch, escapedSearchTerm };
}
/**
* Enriches investigation list items with author profile information.
* @category Services
* @param {IInvestigationListItemDto[]} items - The investigation items to enrich.
* @param {AuthGrpcService} [grpcClient] - Optional gRPC client for fetching user profiles.
* @returns {Promise<IInvestigationListItemDto[]>} - Items enriched with author profiles.
*/
async function enrichItemsWithProfiles(items, grpcClient) {
if (!items.length || !grpcClient) {
return items;
}
// Extract author objects from items (with default values for null authors)
const authors = items.map((item) => item.author ?? { id: null, name: null, email: null, avatar: null });
const enrichedAuthors = await enrichUserItems(authors, grpcClient);
// Map enriched authors back to items
return items.map((item, index) => ({
...item,
author: enrichedAuthors[index] ?? null,
}));
}
/**
* Retrieves investigations with filtering, sorting, and grouping.
* @category Services
* @param {IInvestigationSearchDto} searchDto - The search and pagination parameters.
* @param {AuthGrpcService} [grpcClient] - Optional gRPC client for enriching user profiles.
* @returns {Promise<IInvestigationListResponseDto>}- The investigations with sections/items.
*/
export async function getInvestigations(searchDto, grpcClient) {
const logger = getLogger();
try {
logger.info({ searchDto }, "Getting investigations with filtering, sorting, and grouping");
// Build MongoDB query with all filters
const { query, hasSearch, escapedSearchTerm } = buildInvestigationQuery(searchDto);
// Get pagination params - when fetchAll is true, ignore limit/offset and use hard cap
const isFetchAll = searchDto.fetchAll === true;
const limit = isFetchAll ? INVESTIGATIONS_FETCH_ALL_LIMIT : searchDto.limit || 100;
const offset = isFetchAll ? 0 : searchDto.offset || 0;
// Initialize helper for sorting and grouping operations
const sortingHelper = new InvestigationSortingHelper();
// Pre-fetch and sort authors for AUTHOR section (enables surname A→Z sorting at DB level)
let sortedAuthorIds;
const section = searchDto.section;
if (section === InvestigationSectionType.AUTHOR && grpcClient) {
try {
// Get distinct author IDs from matching investigations
const distinctAuthorIds = await Investigation.distinct("metadata.author", query);
const authorIdStrings = distinctAuthorIds
.filter((id) => id != null)
.map((id) => id.toString());
if (authorIdStrings.length > 0) {
// Fetch profiles using batch gRPC call for efficiency
const profilesMap = await batchFetchUserProfiles(authorIdStrings, grpcClient);
const profiles = authorIdStrings.map((id) => {
const profile = profilesMap.get(id);
return { id: profile?.id ?? id, name: profile?.name ?? null };
});
// Sort profiles: current user first, then by surname A→Z
const sortedProfiles = sortingHelper.sortAuthorProfiles(profiles, searchDto.currentUserId);
// Convert to ObjectIds for $indexOfArray in aggregation
sortedAuthorIds = sortedProfiles
.filter((p) => p.id != null)
.map((p) => new Types.ObjectId(p.id));
logger.debug({ authorCount: sortedAuthorIds.length }, "Pre-fetched and sorted author profiles for section-aware sorting");
}
}
catch (error) {
logger.warn({ error }, "Failed to pre-fetch authors for section sorting, using fallback");
}
}
// Build sort specification (with optional section-aware or group-aware sorting)
// Section sorting takes priority over group sorting
const hasSortedAuthorIds = Boolean(sortedAuthorIds && sortedAuthorIds.length > 0);
const group = searchDto.group ?? null;
// Use section sort fields if section is set, otherwise use group sort fields if group is set
const sortableStages = section
? buildSectionSortFields(section, searchDto.currentUserId, sortedAuthorIds)
: buildGroupSortFields(group);
const sortSpec = buildSortSpec(searchDto.sortBy, searchDto.sortOrder, hasSearch, section ?? null, hasSortedAuthorIds, group);
const aggregationResult = await Investigation.aggregate([
{ $match: query },
...(hasSearch ? [buildRelevanceScoreStage(escapedSearchTerm)] : []),
// Add computed section sort fields before URL lookup
...sortableStages,
// Lookup URLs and compute views/completions before sorting
...URL_LOOKUP_STAGES,
// Sort BEFORE pagination for consistent results across pages (section-aware)
{ $sort: sortSpec },
{
$facet: {
totalCount: [{ $count: "count" }],
paginatedResults: [
{ $skip: offset },
{ $limit: limit },
{ $addFields: { urls: { $size: "$urls" } } },
{
$project: {
_id: 1,
status: 1,
titleValue: "$title.value",
gradeValue: "$grade.value",
curriculumValue: "$curriculum.value",
views: 1,
completions: 1,
urls: 1,
author: "$metadata.author",
editors: "$metadata.editors",
unit: "$unitNumberAndTitle.value",
lesson: "$lessonNumberAndTitle.value",
day: "$day.value",
createdAt: "$createdAt",
updatedAt: "$updatedAt",
publishedAt: "$metadata.dateOfPublishing",
sentToDevelopmentAt: "$metadata.dateOfDevelopmentDelivery",
activity: null,
timeInDevelopment: "$metadata.dateOfDevelopment",
},
},
],
},
},
]);
const total = aggregationResult[0]?.totalCount?.[0]?.count || 0;
const investigations = aggregationResult[0]?.paginatedResults || [];
// Convert to DTOs
const investigationListItems = investigations.map((investigation) => ({
id: investigation._id.toString(),
status: investigation.status,
title: investigation.titleValue || "",
grade: investigation.gradeValue || "",
curriculum: investigation.curriculumValue || "",
views: investigation.views || 0,
completions: investigation.completions || 0,
urls: investigation.urls || 0,
author: {
id: investigation.author ? investigation.author.toString() : null,
name: null,
email: null,
avatar: null,
},
editors: investigation.editors || [],
subject: subjectsFactory[investigation.unit?.split(" ")[1]?.split(".")[0] ?? ""] || null,
unit: investigation.unit || null,
lesson: investigation.lesson || null,
day: investigation.day ?? null,
createdAt: investigation.createdAt || null,
updatedAt: investigation.updatedAt || null,
publishedAt: investigation.publishedAt || null,
sentToDevelopmentAt: investigation.sentToDevelopmentAt || null,
activity: investigation.activity || null,
timeInDevelopment: investigation.timeInDevelopment || null,
}));
// Calculate pagination metadata
const currentPage = Math.floor(offset / limit) + 1;
const totalPages = Math.ceil(total / limit);
const hasNextPage = currentPage < totalPages;
const hasPrevPage = currentPage > 1;
// Determine if filters are applied
const hasFilters = Boolean(searchDto.status ||
searchDto.curriculum ||
searchDto.gradeFrom ||
searchDto.gradeTo ||
searchDto.dateCreatedFrom ||
searchDto.dateCreatedTo ||
searchDto.datePublishedFrom ||
searchDto.datePublishedTo ||
searchDto.sentToDevelopmentFrom ||
searchDto.sentToDevelopmentTo ||
searchDto.editors ||
searchDto.authors);
// Enrich items with author profiles before sectioning/grouping
const enrichedItems = await enrichItemsWithProfiles(investigationListItems, grpcClient);
// Flat list for changeDate/no section, otherwise grouped sections
const isFlatList = !section || section === InvestigationSectionType.CHANGE_DATE;
const data = isFlatList
? { items: enrichedItems }
: { sections: sortingHelper.buildSections(enrichedItems, section, searchDto) };
const result = searchDto.group
? sortingHelper.applyGrouping(data, searchDto.group, searchDto)
: data;
logger.info({
total,
limit,
offset,
itemsCount: result.items?.length || 0,
sectionsCount: result.sections?.length || 0,
}, "Investigations retrieved successfully");
return {
...result,
pagination: {
total,
limit,
offset,
currentPage,
totalPages,
hasNextPage,
hasPrevPage,
},
hasFilters,
appliedFilters: {
section: searchDto.section,
group: searchDto.group,
sortBy: searchDto.sortBy,
sortOrder: searchDto.sortOrder,
...searchDto,
},
};
}
catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
logger.error({ error, errorMessage, searchDto }, "Failed to get investigations");
throw new Error(`Failed to get investigations: ${errorMessage}`);
}
}
/**
* Retrieves filter metadata for investigations list.
* Returns available filter options with counts for status, curriculum, grade, authors, and editors.
* Uses MongoDB $facet aggregation to compute all counts in a single query.
* @category Services
* @returns {Promise<IInvestigationFilterMetadataDto>} - Filter metadata with counts for each dimension.
*/
export async function getFilterMetadata() {
const logger = getLogger();
try {
logger.info("Getting filter metadata for investigations");
const aggregationResult = await Investigation.aggregate([
{
$facet: {
// Total count of all investigations
totalCount: [{ $count: "count" }],
// Status counts
statusCounts: [{ $group: { _id: "$status", count: { $sum: 1 } } }, { $sort: { _id: 1 } }],
// Curriculum counts
curriculumCounts: [
{ $group: { _id: "$curriculum.value", count: { $sum: 1 } } },
{ $match: { _id: { $ne: null } } },
{ $sort: { _id: 1 } },
],
// Grade counts
gradeCounts: [
{ $group: { _id: "$grade.value", count: { $sum: 1 } } },
{ $match: { _id: { $ne: null } } },
{ $sort: { _id: 1 } },
],
// Author counts
authorCounts: [
{ $group: { _id: "$metadata.author", count: { $sum: 1 } } },
{ $match: { _id: { $ne: null } } },
{ $sort: { count: -1 } },
],
// Editor counts (need $unwind since it's an array)
editorCounts: [
{ $unwind: { path: "$metadata.editors", preserveNullAndEmptyArrays: false } },
{ $group: { _id: "$metadata.editors", count: { $sum: 1 } } },
{ $sort: { count: -1 } },
],
},
},
]);
const result = aggregationResult[0];
const totalCount = result?.totalCount?.[0]?.count || 0;
// Map status counts
const statuses = (result?.statusCounts || []).map((item) => ({
status: item._id,
count: item.count,
}));
// Map curriculum counts
const curriculums = (result?.curriculumCounts || []).map((item) => ({
curriculum: item._id || "",
count: item.count,
}));
// Map grade counts and sort numerically
const grades = (result?.gradeCounts || [])
.map((item) => ({
grade: item._id || "",
count: item.count,
}))
.sort((a, b) => {
const gradeA = parseInt(a.grade, 10) || 0;
const gradeB = parseInt(b.grade, 10) || 0;
return gradeA - gradeB;
});
// Map author counts (user profile enrichment will be done in the controller)
const authors = (result?.authorCounts || []).map((item) => ({
id: item._id?.toString() || "",
name: null,
email: null,
count: item.count,
}));
// Map editor counts (user profile enrichment will be done in the controller)
const editors = (result?.editorCounts || []).map((item) => ({
id: item._id?.toString() || "",
name: null,
email: null,
count: item.count,
}));
logger.info({
totalCount,
statusCount: statuses.length,
curriculumCount: curriculums.length,
gradeCount: grades.length,
authorCount: authors.length,
editorCount: editors.length,
}, "Filter metadata retrieved successfully");
return {
statuses,
curriculums,
grades,
authors,
editors,
totalCount,
};
}
catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
logger.error({ error, errorMessage }, "Failed to get filter metadata");
throw new Error(`Failed to get filter metadata: ${errorMessage}`);
}
}
Source