/**
* Person Helper Functions
*
* Shared helper functions used by both person and extern controllers:
* - Person data updates
* - Group membership migration
* - Event publishing (change, join, leave)
*/
// @ts-check
import { query, UUID2hex, HEX2uuid, transaction } from '@commtool/sql-query';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js'
import { Templates, phonetikArray, getConfig } from '../../utils/compileTemplates.js';
import { renderObject } from '../../utils/renderTemplates.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { familyAddress } from '../../tree/familyAddress.js';
import { publishEvent } from '../../utils/events.js';
import { addToTree, migratePerson } from './migratePerson.js';
import { keysEqual } from '../../utils/keyCompare.js';
import { filterKeys } from '@commtool/object-filter';
import { errorLoggerUpdate } from '../../utils/requestLogger.js';
import { addUpdateList, addUpdateEntry } from '../../server.ws.js';
import { updateUserSettings } from '../../server.ws.js';
import { encryptIbans } from '../../utils/crypto.js';
import { invalidateUserCache } from '../../utils/userUtils.js';
import { updateEntityEmbedding } from '../../utils/embeddings.js';
import { personRebuildAccess, personListRebuildAccess } from '../../tree/rebuildList.js';
import _ from 'lodash';
/**
* Lodash merge customizer - do not merge arrays
*/
function mergeCustomizer(objValue, srcValue) {
if (_.isArray(objValue)) {
return srcValue;
}
}
/**
* Updates or creates AI embeddings for a person/extern asynchronously
* Wrapper around updateEntityEmbedding with sanitize option enabled
*
* @param {Buffer} UID - Entity UID as buffer
* @param {Object} data - Entity data object
* @param {string} Type - Object type ('person', 'extern', 'group', etc.)
* @param {Buffer} UIDOrganization - Organization UID as buffer
* @param {Buffer} UIDuser - User UID as buffer
* @returns {Promise<void>}
*/
export async function updatePersonEmbedding(UID, data, Type, UIDOrganization, UIDuser) {
const titles= await query(`SELECT Title FROM ObjectBase WHERE UID=?`, [UID]);
const eData= { Title: titles[0].Title};
['email','phone','address','accounts','firstName','lastName'].forEach((field) => {
if (data[field]) eData[field]=data[field];
});
return updateEntityEmbedding(UID, eData, Type, UIDOrganization, UIDuser,{ sanitize: true });
}
/**
* Updates person/extern data with partial merge (used by POST /:UID endpoint)
*
* This function performs partial data updates by merging the provided data with existing
* Member data. It only updates the Member table, not ObjectBase fields. Used by both
* person and extern POST endpoints for partial updates without changing group membership.
*
* Difference from updatePersonData:
* - Updates ONLY Member table (vs. both ObjectBase and Member)
* - Performs partial merge with existing data (vs. full object replacement)
* - Uses direct publishEvent (vs. publishChangeEvent through group hierarchy)
* - No group membership handling (vs. full create/update flow)
*
* @param {Buffer} UID - Person/Extern UID as buffer
* @param {Object} partialData - Partial data to merge with existing data
* @param {Object} req - Express request object
* @returns {Promise<Object>} Result with updated data
*/
export const updatePersonPartial = async (UID, partialData, req) => {
// Validate required session parameters
if (!req || !req.session || !req.session.root || !req.session.user) {
throw new Error('Invalid request session - missing root or user');
}
// Get original data
const origPerson = await query(`SELECT Member.Data, ObjectBase.Data AS BaseData, ObjectBase.Type FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.UID=?`, [UID], { cast: ['json'] });
if (!origPerson.length) {
throw new Error('Person not found');
}
const origData = _.cloneDeep(origPerson[0].Data);
const newData = _.mergeWith(_.cloneDeep(origData), partialData, mergeCustomizer);
// Encrypt IBANs
const unencryptedData = _.cloneDeep(newData); // store unencrypted data for websocket
encryptIbans(await getConfig('db', req), newData);
// Update Member table only
await query(`UPDATE Member SET Data=? WHERE UID=?`, [JSON.stringify(newData), UID]);
// Invalidate Redis user cache so the next config load gets fresh data (e.g. after settings change).
// The cache key is built from the Keycloak UID, not the Member UID.
// If the updated person is the currently logged-in user, we can invalidate directly from the session.
// Otherwise, find the linked Keycloak identity to invalidate the right cache key.
try {
const sessionUserUID = req.session?.user; // UUID of the Member
if (sessionUserUID && UUID2hex(sessionUserUID).equals(UID)) {
// Updating own data — Keycloak UID is available in session
await invalidateUserCache(req.session.authUser?.userUID);
} else {
// Updating another person — look up their Keycloak identity link
const identLinks = await query(
`SELECT Links.UID FROM Links WHERE Links.UIDTarget=? AND Links.Type='identifyer'`,
[UID], { cast: ['UUID'] }
);
for (const link of identLinks) {
await invalidateUserCache(link.UID);
}
}
} catch (cacheErr) {
console.error('[updatePersonPartial] Failed to invalidate user cache:', cacheErr.message);
}
// Check for changes and publish events
const [equal, myDiff, filteredDiff] = keysEqual(origData, newData);
if (!equal) {
// queue should only be invoked if there is a relevant key for filters been updated
if (filteredDiff)
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'personFilter', UID, UID, null, null);
// Publish change events to all parent groups
await publishChangeEvent({ Type: 'person', UID: UID }, myDiff, null, req.session.root);
updatePersonEmbedding(UID, newData, origPerson[0].Type || 'person', UUID2hex(req.session.root), UUID2hex(req.session.user)).catch(err => {
console.error('Error updating person embedding:', err);
});
// Handle family address updates
if ((Array.isArray(newData.address) && newData.address.find((a /** @type {any} */) => (a.type === 'family'))) ||
(Array.isArray(newData.email) && newData.email.find((a /** @type {any} */) => (a.type === 'family'))) ||
(Array.isArray(newData.phone) && newData.phone.find((a /** @type {any} */) => (a.type === 'family'))) ||
(Array.isArray(newData.accounts) && newData.accounts.find((a /** @type {any} */) => (['family', 'familyFees'].includes(a.type))))) {
familyAddress(/** @type {import('../../tree/familyAddress.js').FamilyMemberObject} */ ({ UID, Type: 'person', Data: newData }), req.session.root);
}
// WebSocket updates
addUpdateEntry(UID, { person: { Type: 'person', Data: unencryptedData, UID: HEX2uuid(UID) } });
addUpdateList([UID]);
// If settings changed, push the new settings directly to the affected user's WS connection
// so the frontend can update the config context without a full page reload
if (unencryptedData.settings && myDiff && Object.prototype.hasOwnProperty.call(myDiff, 'settings')) {
const affectedKeycloakUID = req.session?.authUser?.userUID;
if (affectedKeycloakUID) {
updateUserSettings(affectedKeycloakUID, unencryptedData.settings);
}
}
}
const result /** @type {{Data: any, UID: string | undefined, BaseData?: any}} */ = {Data: unencryptedData, UID: HEX2uuid(UID)};
if(origPerson[0].BaseData!=='null' )
result.BaseData=origPerson[0].BaseData;
return result ;
};
/**
* Updates person data in both ObjectBase and Member tables (full object update)
*
* This function is used during create/update operations (PUT /:group) where a complete
* object with all rendered fields is provided. It updates both database tables, handles
* event publishing, family address updates, and WebSocket notifications.
*
* Difference from updatePersonPartial:
* - Updates BOTH ObjectBase and Member tables (vs. Member only)
* - Expects a fully rendered object with Title, Display, SortBase, etc. (vs. partial data)
* - Used for PUT operations during create/update flows (vs. POST for partial updates)
* - Uses publishChangeEvent for group hierarchy (vs. direct publishEvent)
*
* @param {Buffer} UIDperson - Person UID as buffer
* @param {Object} newData - New person data
* @param {Object} object - Fully rendered object with all fields (Title, Display, SortBase, etc.)
* @param {Object} req - Express request object
* @param {number} timestamp - Optional timestamp for transaction
* @returns {Promise<Object>} Update result with diff information
*/
export const updatePersonData = async (UIDperson, newData, object, req, timestamp = null) => {
// Validate required session parameters
if (!req || !req.session || !req.session.root || !req.session.user) {
throw new Error('Invalid request session - missing root or user');
}
// Get original data
const persons = await query(`SELECT Member.Data, ObjectBase.dindex FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.UID=?`, [UIDperson], { cast: ['json'] });
if (!persons.length) {
throw new Error('Person not found');
}
const origData = _.cloneDeep(persons[0].Data);
const origDindex = persons[0].dindex;
// Check for changes first
const [equal, myDiff, filteredDiff] = keysEqual(origData, object.Data);
// Only update ObjectBase if data actually changed (prevents unnecessary history entries in system-versioned tables)
if (!equal) {
await transaction(async (connection) => {
await connection.query(`UPDATE ObjectBase SET
Title=?,dindex=?,hierarchie=?,stage=?,gender=?
WHERE UID=?`,
[object.Title, object.dindex,
object.hierarchie, object.stage, object.gender, UIDperson]);
}, { backDate: timestamp });
// Update Member table - Display, FullTextIndex, Data should only be updated here for persons
await query(`UPDATE Member SET Display=?,SortName=?,FullTextIndex=?,PhonetikIndex=?,Data=? WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex, phonetikArray([object.Data.firstName, object.Data.lastName]), JSON.stringify(object.Data), UIDperson]);
// queue is only run for keys, which are relevant for filters
if (filteredDiff)
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'personFilter', UIDperson, UIDperson, null, null);
// event is published for all changes
await publishChangeEvent({ Type: object.Type || 'person', UID: UIDperson }, myDiff, timestamp, req.session.root);
// Handle family address
if ((object.Data.address && object.Data.address.find(a => (a.type === 'family'))) ||
(object.Data.email && object.Data.email.find(a => (a.type === 'family'))) ||
(object.Data.phone && object.Data.phone.find(a => (a.type === 'family'))) ||
(object.Data.accounts && object.Data.accounts.find(a => (['family', 'familyFees'].includes(a.type))))) {
familyAddress(/** @type {import('../../tree/familyAddress.js').FamilyMemberObject} */ ({ ...object, Type: 'person' }), req.session.root);
}
addUpdateList([UIDperson]);
// Asynchronously update AI embeddings (non-blocking)
updatePersonEmbedding(UIDperson, object.Data, object.Type || 'person', UUID2hex(req.session.root), UUID2hex(req.session.user)).catch(err => {
console.error('Background embedding update failed:', err);
});
}
// Handle dindex changes
if (origDindex !== object.dindex) {
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'listMember', UIDperson, UIDperson, null, UIDperson);
}
// WebSocket updates
addUpdateEntry(UIDperson, { person: { Type: object.Type || 'person', Data: object.Data, UID: HEX2uuid(UIDperson) } });
return { equal, myDiff, origData };
};
/**
* Handles group membership migration for both person and extern types
* @param {Object} existingPerson - Current person/extern data with MemberA field
* @param {Buffer} UIDperson - Person UID as buffer
* @param {Buffer} UIDgroup - Target group UID as buffer
* @param {Object} req - Express request object
* @param {number} timestamp - Optional timestamp for migration
* @param {string} type - 'person' or 'extern' type for specific handling
* @returns {Promise<void>}
*/
export const handleGroupMembershipMigration = async (existingPerson, UIDperson, UIDgroup, req, timestamp = null, type = 'person') => {
// Validate required session parameters
if (!req || !req.session || !req.session.root || !req.session.user) {
throw new Error('Invalid request session - missing root or user');
}
const hasCurrentGroup = existingPerson.MemberA && existingPerson.MemberA.equals(UIDgroup);
const isTypeConversion = existingPerson.Type !== type;
// Check if migration is needed
if (!hasCurrentGroup || isTypeConversion) {
if (existingPerson.MemberA) {
// Migrate from existing group to new group
// add/remove events will be sent from migratePerson
await migratePerson(UIDperson, existingPerson.MemberA, UIDgroup, timestamp);
// Handle type-specific queue updates
if (type === 'extern' && existingPerson.Type === 'person') {
// Person -> Extern conversion: handle as person exit
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'person', UIDperson, UIDperson, existingPerson.MemberA, null, timestamp);
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'extern', UIDperson, UIDperson, null, UIDgroup, timestamp);
// Publish exit event for person leaving organization
await publishGroupEvent('exit', UIDperson, 'person', existingPerson.MemberA, req.session.root, timestamp);
// Handle family fees change for person -> extern
const family = await query(`SELECT Links.UIDTarget FROM Links
INNER Join Member ON (Links.UIDTarget=Member.UID AND Links.Type IN ('family','familyFees'))
WHERE Links.UID=? `, [UIDperson]);
if (family.length > 0) {
const UIDfamily = family[0].UIDTarget;
await query(`UPDATE Links SET Links.Type='family' WHERE Links.UID=? AND Links.UIDTarget=? AND Links.Type='familyFees' `, [UIDperson, UIDfamily]);
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'family', UIDperson, UIDperson, null, UIDfamily);
}
} else if (type === 'person' && existingPerson.Type === 'extern') {
// Extern -> Person conversion: person becomes member
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'extern', UIDperson, UIDperson, existingPerson.MemberA, null, timestamp);
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'person', UIDperson, UIDperson, null, UIDgroup, timestamp);
// Publish new event for person joining organization
await publishGroupEvent('new', UIDperson, 'person', UIDgroup, req.session.root, timestamp);
// Handle family fees change for extern -> person (upgrade to familyFees)
const family = await query(`SELECT Links.UIDTarget FROM Links
INNER Join Member ON (Links.UIDTarget=Member.UID AND Links.Type IN ('family','familyFees'))
WHERE Links.UID=? `, [UIDperson]);
if (family.length > 0) {
const UIDfamily = family[0].UIDTarget;
await query(`UPDATE Links SET Links.Type='familyFees' WHERE Links.UID=? AND Links.UIDTarget=? AND Links.Type='family' `, [UIDperson, UIDfamily]);
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'family', UIDperson, UIDperson, null, UIDfamily);
}
} else if (type === 'person') {
// Regular person migration between groups
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'person', UIDperson, UIDperson, existingPerson.MemberA, UIDgroup, timestamp);
// Trigger family fees recalculation on group change
const family = await query(`SELECT Links.UIDTarget FROM Links
INNER Join Member ON (Links.UIDTarget=Member.UID AND Links.Type IN ('family','familyFees'))
WHERE Links.UID=? `, [UIDperson]);
if (family.length > 0) {
const UIDfamily = family[0].UIDTarget;
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'family', UIDperson, UIDperson, null, UIDfamily);
}
} else {
// Extern -> Extern migration
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'extern', UIDperson, UIDperson, existingPerson.MemberA, UIDgroup, timestamp);
// Trigger family fees recalculation on group change
const family = await query(`SELECT Links.UIDTarget FROM Links
INNER Join Member ON (Links.UIDTarget=Member.UID AND Links.Type IN ('family','familyFees'))
WHERE Links.UID=? `, [UIDperson]);
if (family.length > 0) {
const UIDfamily = family[0].UIDTarget;
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'family', UIDperson, UIDperson, null, UIDfamily);
}
}
} else {
// No existing group - add to tree
await addToTree(UIDperson, UIDgroup, timestamp);
}
}
};
/**
* Publishes change events for person modifications.
* Notifies related entities (groups, organizations) about changes to a person.
*
* @param {Object} person - The person object containing at least `Type` and `UID` properties.
* @param {Object} diff - An object representing the differences or changes to be published.
* @param {number} timestamp - The timestamp to associate with the event.
* @param {string} organization - The organization UID for multi-tenant context.
*/
export const publishChangeEvent = async (person, diff, timestamp, organization) => {
try {
if (!diff || Object.keys(diff).length === 0) return;
// Get all objects with the same UIDBelongsTo (person and all their entries/jobs/guests)
// along with their membership links to groups
const entries = await query(`
SELECT
ObjectBase.UID AS UID,
ObjectBase.Type AS BaseType,
Links.UIDTarget,
Target.Type AS TargetType
FROM ObjectBase
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN ObjectBase AS Target ON (Target.UID = Links.UIDTarget)
WHERE ObjectBase.UIDBelongsTo = ?
AND ObjectBase.Type IN ('person','extern','job','guest','entry')
GROUP BY ObjectBase.UID, Links.UIDTarget
`, [person.UID], { backDate: timestamp, log: true });
// Publish change events for each object-group relationship
for (const entry of entries) {
// Always publish event for the base type (entry/job/guest/person/extern)
publishEvent(`/change/${entry.TargetType}/${entry.BaseType}/${HEX2uuid(entry.UIDTarget)}`, {
organization: organization,
data: { UID: HEX2uuid(entry.UID), diff },
backDate: timestamp
});
// For entries, jobs, and guests, also publish event for the person type
if (['entry', 'job', 'guest'].includes(entry.BaseType)) {
publishEvent(`/change/${entry.TargetType}/${person.Type}/${HEX2uuid(entry.UIDTarget)}`, {
organization: organization,
data: { UID: HEX2uuid(entry.UID), diff },
backDate: timestamp
});
}
}
addUpdateList(entries.map(e => e.UIDTarget));
}
catch (e) {
errorLoggerUpdate(e)
}
};
/**
* Publishes an event for a person joining or leaving an organization.
* Notifies the group and its parent groups about the person's status change.
*
* @param {'new'|'exit'} eventType - The type of event: 'new' for joining, 'exit' for leaving
* @param {Buffer|string} UIDperson - The UID of the person (can be Buffer or from object.UID)
* @param {string} ObjectType - The type of the object ('person' or 'extern')
* @param {Buffer} UIDgroup - The UID of the group
* @param {string} organization - The organization UID for multi-tenant context
* @param {number} timestamp - The timestamp to associate with the event
*/
export const publishGroupEvent = async (eventType, UIDperson, ObjectType, UIDgroup, organization, timestamp) => {
try {
// Query for the group and its parents
const groups = await query(`SELECT ObjectBase.UID AS UID,ObjectBase.Type
FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UIDTarget
AND Links.Type IN ('member','memberA','memberS','memberSys') AND ObjectBase.Type IN('group','ggroup'))
WHERE Links.UID=?
GROUP BY ObjectBase.UID`, [UIDgroup], { backDate: timestamp });
const groupUIDs = [...groups.map(el => el.UID), UIDgroup];
// Convert UIDperson to hex string if it's a Buffer
const personUIDHex = Buffer.isBuffer(UIDperson) ? HEX2uuid(UIDperson) : HEX2uuid(UIDperson);
// Publish event to all groups
groupUIDs.forEach(group =>
publishEvent(`/${eventType}/group/${ObjectType}/${HEX2uuid(group)}`, {
organization: organization,
data: [personUIDHex],
backDate: timestamp
})
);
} catch (e) {
errorLoggerUpdate(e);
}
};
/**
* Shared function to update group membership for person or extern
* @param {Object} req - Express request object
* @param {Object} res - Express response object
* @param {'person'|'extern'} targetType - The target type to convert to
* @param {Function} isObjectAdmin - Function to check if user has admin rights
*/
export const updateGroupMembershipShared = async (req, res, targetType, isObjectAdmin) => {
try {
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
if (!isObjectAdmin(req, req.params.UID)) {
res.status(403).json({ success: false, message: 'user not authorized for this person' });
return;
}
const UIDgroup = UUID2hex(req.params.group);
const UIDperson = UUID2hex(req.params.UID);
// Validate group exists
const groups = await query(`SELECT ObjectBase.UID,ObjectBase.Title,Member.Display,
ObjectBase.dindex,Member.Data, ObjectBase.gender, ObjectBase.hierarchie,
ObjectBase.stage, ObjectBase.validFrom, ObjectBase.UIDBelongsTo
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=?`, [UIDgroup], { cast: ['json', 'UUID'] });
if (!groups || groups.length === 0) {
res.status(404).json({ success: false, message: 'group not found' });
return;
}
const group = groups[0];
// Combined-gender groups (gender='C') are structural containers only;
// individual persons/externs may not be added directly to them.
if (group.gender === 'C') {
res.status(400).json({ success: false, message: 'persons cannot be added to a combined-gender group (gender C)' });
return;
}
// Fetch person data
const persons = await query(`SELECT Member.Data,Links.UIDTarget AS MemberA,
ObjectBase.Data AS BaseData, ObjectBase.dindex, ObjectBase.Type,ObjectBase.SortName AS SortBase,
ObjectBase.ValidFrom, ObjectBase.ValidUntil
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON(Links.Type='memberA' AND Links.UID=ObjectBase.UID)
INNER JOIN ObjectBase AS MainGroup ON (Links.UIDTarget=MainGroup.UID AND MainGroup.Type='group')
WHERE ObjectBase.UID=? AND ObjectBase.Type IN ('person','extern')`,
[UIDperson],
{
log: false,
cast: ['json']
});
if (persons.length === 0) {
res.status(300).json({ success: false, message: 'person not found' });
return;
}
const person = persons[0];
const PersonData = person.Data;
const GroupData = group.Data;
// Render new object data with appropriate template
const template = Templates[req.session.root][targetType];
const object = await renderObject(template, { UID: UIDperson, ...PersonData, group: GroupData }, req);
object.UID = UIDperson;
// Determine if type conversion is happening
const sourceType = person.Type;
const isTypeConversion = sourceType !== targetType;
// Update ObjectBase if type changed or other fields need updating
if (isTypeConversion || object.SortBase !== person.SortBase) {
await query(`UPDATE ObjectBase SET Type=?,Title=?,SortName=?,dindex=?,hierarchie=?,stage=?,gender=?,Data=? WHERE UID=?`,
[targetType, object.Title, object.SortBase, object.dindex, object.hierarchie, object.stage, object.gender, JSON.stringify(person.BaseData), object.UID],
{ backDate: timestamp });
}
// Handle group membership migration if group changed or type changed
if (!person.MemberA.equals(UIDgroup) || isTypeConversion) {
await handleGroupMembershipMigration(person, UIDperson, UIDgroup, req, timestamp, targetType);
// Publish appropriate event for type conversion
if (isTypeConversion) {
if (targetType === 'person' && sourceType === 'extern') {
// Extern -> Person: joining organization
await publishGroupEvent('new', object.UID, targetType, UIDgroup, req.session.root, timestamp);
} else if (targetType === 'extern' && sourceType === 'person') {
// Person -> Extern: leaving organization
await publishGroupEvent('exit', UIDperson, targetType, UIDgroup, req.session.root, timestamp);
}
}
// Update WebSocket clients
addUpdateEntry(req.params.UID, {
person: {
Type: targetType,
...object,
Data: PersonData,
UID: HEX2uuid(object.UID)
},
parent: group
});
addUpdateList([person.MemberA, UIDgroup]);
}
res.json({ success: true , result:null});
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
// ---------------------------------------------------------------------------
// Shared DB helpers — used by both person and extern flows
// ---------------------------------------------------------------------------
/**
* Fetch a group record (ObjectBase + Member) by its binary UID.
* Shared entry point for person and extern create/update flows.
*
* @param {Buffer} UIDgroup
* @returns {Promise<Object|null>}
*/
export const fetchGroup = async (UIDgroup) => {
const rows = await query(
`SELECT ObjectBase.UID, ObjectBase.Title, Member.Display,
ObjectBase.dindex, Member.Data, ObjectBase.hierarchie, ObjectBase.gender,
ObjectBase.stage, ObjectBase.validFrom, ObjectBase.UIDBelongsTo
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
WHERE ObjectBase.UID = ?`,
[UIDgroup],
{ cast: ['json', 'UUID'] }
);
return rows[0] ?? null;
};
/**
* Rebuild visibility and list access for a person or extern and all its job objects.
* Shared implementation for /person/rebuildAccess and /extern/rebuildAccess.
*
* @param {Buffer} UID Binary UID of the person or extern
* @param {string} sessionRoot req.session.root
*/
/**
* Return the UNIX timestamp (seconds) of the most recent ObjectBase row for a UID
* across all system-time history. Used to prevent backdated type-change operations
* (extern → person or person → extern) from landing before existing history rows,
* which would create impossible temporal ordering in the versioned table.
*
* @param {Buffer} UID Binary UID of the person or extern
* @returns {Promise<number|null>} Latest validFrom in seconds, or null if not found
*/
export const fetchLatestObjectValidFrom = async (UID) => {
const rows = await query(
`SELECT UNIX_TIMESTAMP(MAX(validFrom)) AS latestValidFrom
FROM ObjectBase FOR SYSTEM_TIME ALL
WHERE UID = ?`,
[UID]
);
return rows[0]?.latestValidFrom ?? null;
};
/**
* Check whether a person or extern record already exists for the given binary UID.
* Shared by both person and extern create/update flows.
*
* @param {Buffer} UID Binary UID of the person or extern
* @param {string} asOf SQL FOR SYSTEM_TIME clause or empty string
* @returns {Promise<Object[]>}
*/
export const fetchMemberExists = async (UID, asOf) => {
return query(
`SELECT Member.UID, Member.Data, Links.UIDTarget AS MemberA, ObjectBase.Data AS BaseData,
ObjectBase.Type, ObjectBase.dindex, ObjectBase.SortName AS SortBase
FROM ObjectBase ${asOf}
INNER JOIN Member ON (Member.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Links ${asOf} ON (Links.Type = 'memberA' AND Links.UID = ObjectBase.UID)
INNER JOIN ObjectBase ${asOf} AS MainGroup
ON (Links.UIDTarget = MainGroup.UID AND MainGroup.Type = 'group')
WHERE ObjectBase.UID = ? AND ObjectBase.Type IN ('person', 'extern')`,
[UID],
{ cast: ['json'] }
);
};
export const rebuildMemberAccess = async (UID, sessionRoot) => {
const jobs = await query(
`SELECT UID FROM ObjectBase WHERE UIDBelongsTo = ? AND Type = 'job'`,
[UID]
);
for (const job of jobs) {
await personRebuildAccess(job.UID);
await personListRebuildAccess(job.UID, sessionRoot);
}
};