/**
* SearchData Service Layer
*
* This service handles all database operations related to searching entities
* in the CommTool system. It provides both fulltext search and semantic search
* capabilities using vector embeddings.
*
* Key search types:
* - Fulltext search: MySQL MATCH...AGAINST for keyword-based search
* - Semantic search: Vector similarity search using AI embeddings
* - Type-specific search: Filtered by entity type (member, person, group, etc.)
*
* Database tables involved:
* - ObjectBase: Core entity records
* - Member: Display names and fulltext search indices
* - Links: Entity relationships
* - Visible: User visibility permissions
*
* @module SearchDataService
*/
import { query, pool, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import mysqlTime from '../../utils/mysqlTime.js';
import { isAdmin } from '../../utils/authChecks.js';
import { getSuperAdmin } from '../orga/service.js';
import { searchSimilarEntities } from '../../utils/embeddings.js';
/**
* Build visibility join clause based on user permissions
*
* @param {Object} session - Session data
* @param {string} session.user - User UID
* @param {string} session.root - Organization UID
* @param {boolean} changeable - Whether to include changeable filter
* @returns {Promise<string>} SQL JOIN clause for visibility
*/
export const buildVisibilityClause = async (session, changeable = false) => {
let visibility = `INNER JOIN Visible ON (Visible.UID=ObjectBase.UID AND Visible.UIDuser=U_UUID2BIN('${session.user}'))`;
if (changeable) {
visibility = `INNER JOIN Visible ON (Visible.UID=ObjectBase.UID AND Visible.UIDuser=U_UUID2BIN('${session.user}') AND Visible.Type IN('changeable','admin'))`;
}
if (await isAdmin(session)) {
const superadmin = await getSuperAdmin(session.root);
visibility = `INNER JOIN Visible ON (Visible.UID=ObjectBase.UID AND Visible.UIDuser=U_UUID2BIN('${superadmin}'))`;
}
return visibility;
};
/**
* Build filter clauses from query parameters
*
* @param {Object} query - Query parameters
* @param {string|Array} [query.types] - Entity types to filter
* @param {string|Array} [query.gender] - Gender filter
* @param {string|Array} [query.stage] - Stage filter
* @param {string|Array} [query.hierarchie] - Hierarchy filter
* @returns {Object} Filter clauses {types, gender, stage, hierarchie}
*/
export const buildFilterClauses = (query) => {
const filters = {
types: "",
gender: "",
stage: "",
hierarchie: ""
};
// Types filter
if (query.types) {
let para;
try {
para = JSON.parse(String(query.types));
para = para.map(p => pool.escape(p)).join(',');
if (para.length > 0) {
filters.types = ` ObjectBase.Type IN(${para})`;
}
} catch (e) {
para = pool.escape(String(query.types));
filters.types = ` ObjectBase.Type=${para}`;
}
} else {
filters.types = " ObjectBase.Type IN('group','person','guest','job','extern','list','dlist')";
}
// Gender filter
if (query.gender) {
let para;
try {
para = JSON.parse(String(query.gender));
para = para.map(p => pool.escape(p)).join(',');
if (para.length > 0) {
filters.gender = `AND ObjectBase.gender IN(${para})`;
}
} catch (e) {
para = pool.escape(String(query.gender));
filters.gender = ` AND ObjectBase.gender=${para}`;
}
}
// Stage filter
if (query.stage) {
let para;
try {
para = JSON.parse(String(query.stage));
para = para.map(p => pool.escape(p)).join(',');
if (para.length > 0) {
filters.stage = `AND ObjectBase.stage IN(${para})`;
}
} catch (e) {
para = pool.escape(String(query.stage));
filters.stage = ` AND ObjectBase.stage=${para}`;
}
}
// Hierarchie filter
if (query.hierarchie) {
let para;
try {
para = JSON.parse(String(query.hierarchie));
para = para.map(p => pool.escape(p)).join(',');
if (para.length > 0) {
filters.hierarchie = `AND ObjectBase.hierarchie IN(${para})`;
}
} catch (e) {
para = pool.escape(String(query.hierarchie));
filters.hierarchie = ` AND ObjectBase.hierarchie =${para}`;
}
}
return filters;
};
/**
* Reduce results by removing duplicates based on UIDBelongsTo
*
* @param {Array} result - Query results
* @returns {Array} Deduplicated results
*/
export const reduceResult = (result) => {
if (result) {
return result.reduce((newArray, current) => {
const duplicate = newArray.find(el => el.UIDBelongsTo.compare(current.UIDBelongsTo) === 0);
if (duplicate) {
if (current.type === 'person' || (current.type === 'guest' && duplicate.type === 'job')) {
duplicate.title = current.title;
duplicate.type = current.type;
duplicate.value = current.value;
}
return newArray;
} else {
return [...newArray, current];
}
}, []);
} else {
return [];
}
};
/**
* Search all entity types with fulltext search
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchAllEntities = async (searchQuery, session, queryParams) => {
const visibility = await buildVisibilityClause(session, queryParams.changeable);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',IF(ObjectBase.Type IN('job','person','guest','extern','group','ggroup','list','dlist','email'),Member.Display,ObjectBase.Display)) AS title,
IF(ObjectBase.Type IN('job','guest'),ObjectBase.UIDBelongsTo,ObjectBase.UID) AS value,
ObjectBase.UID AS myKey,
ObjectBase.UID AS \`key\`,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
LEFT JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
${visibility}
WHERE
( MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE) ) AND ${filters.types} ${filters.gender} ${filters.stage} ${filters.hierarchie}
GROUP BY myKey`,
[searchQuery, searchQuery],
{ cast: ['UUID'], log: false });
return result;
};
/**
* Search members (persons, jobs, guests) with fulltext search
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchMembers = async (searchQuery, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UID AS value,
ObjectBase.UID AS myKey,
ObjectBase.UIDBelongsTo,
ObjectBase.Type as type,
ObjectBase.hierarchie,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
LEFT JOIN Links AS GLinks ON (GLinks.UID=ObjectBase.UID AND GLinks.Type IN ('member','memberA','memberG','memberSys'))
${visibility}
WHERE
MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type IN ('person','job','guest') ${filters.gender}${filters.stage}${filters.hierarchie}
ORDER BY hierarchie ASC`,
[searchQuery]);
return reduceResult(result);
};
/**
* Search external contacts with fulltext search
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchExtern = async (searchQuery, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UIDBelongsTo AS value,
ObjectBase.UID AS myKey,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
${visibility}
WHERE
MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type='extern' ${filters.gender}${filters.stage}${filters.hierarchie}`,
[searchQuery]);
return result;
};
/**
* Search persons with fulltext search
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchPersons = async (searchQuery, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UID AS myKey,
ObjectBase.UIDBelongsTo AS value,
ObjectBase.UIDBelongsTo,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
${visibility}
WHERE
MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type IN ('person','job','guest','extern') ${filters.gender}${filters.stage}${filters.hierarchie}`,
[searchQuery]);
return reduceResult(result);
};
/**
* Search groups with fulltext search
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchGroups = async (searchQuery, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UID AS myKey,
ObjectBase.UID AS value,
ObjectBase.UIDBelongsTo,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
${visibility}
LEFT JOIN Links AS GLinks ON (GLinks.UID=ObjectBase.UID AND GLinks.Type IN ('member','memberA','memberG'))
WHERE
MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type IN ('group','ggroup')
AND (GLinks.UIDTarget=U_UUID2BIN('${session.root}') OR ObjectBase.UID=U_UUID2BIN('${session.root}'));
${filters.gender}${filters.stage}${filters.hierarchie}`,
[searchQuery],
{ log: false });
return result;
};
/**
* Search lists with fulltext search
*
* @param {string} searchQuery - Search term
* @param {string} type - List type (list, dlist, email)
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchLists = async (searchQuery, type, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UID AS myKey,
ObjectBase.UID AS value,
ObjectBase.UIDBelongsTo,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
${visibility}
WHERE
MATCH(Member.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type =?`,
[searchQuery, type],
{ log: false });
return result;
};
/**
* Search generic entities with fulltext search
*
* @param {string} searchQuery - Search term
* @param {string} type - Entity type
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results
*/
export const searchGeneric = async (searchQuery, type, session, queryParams) => {
const visibility = await buildVisibilityClause(session);
const filters = buildFilterClauses(queryParams);
const time = queryParams.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(queryParams.Timestamp)}'` : '';
const result = await query(`SELECT
CONCAT(ObjectBase.Title ,' ',ObjectBase.Display) AS title,
ObjectBase.UID AS value,
ObjectBase.UID AS myKey,
ObjectBase.Type as type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM
ObjectBase ${time} AS ObjectBase
${visibility}
WHERE
MATCH(ObjectBase.FullTextIndex ) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type=? ${filters.gender}${filters.stage}${filters.hierarchie}`,
[searchQuery, type],
{ log: false });
return result;
};
/**
* Search entities using semantic vector similarity
*
* @param {string} searchQuery - Search term
* @param {Object} session - Session data
* @param {Object} queryParams - Query parameters
* @returns {Promise<Array>} Search results with similarity scores
*/
export const searchBySemantic = async (searchQuery, session, queryParams) => {
const organizationUID = session.root;
// Build search options
const searchOptions = {
organizationUID: organizationUID ? UUID2hex(organizationUID) : undefined,
entityType: queryParams.type ? String(queryParams.type) : undefined,
limit: parseInt(String(queryParams.limit || '10')) || 10,
threshold: parseFloat(String(queryParams.threshold || '0.7')) || 0.7,
distanceMetric: queryParams.metric ? String(queryParams.metric) : 'cosine',
sanitize: true
};
// Perform vector similarity search
const results = await searchSimilarEntities(searchQuery, searchOptions);
// Format results to match fulltext search response
return results.map((result, index) => ({
title: result.title || result.display || 'No title',
value: result.uid,
key: result.tuid || index,
type: result.entityType,
similarity: result.similarity,
distance: result.distance,
model: result.model
}));
};