// @ts-check
/**
* @import {ExpressRequestAuthorized} from './../../types.js'
*/
/**
* Group Service — Business Logic
*
* Contains all database queries and business logic for group operations:
* - Account read-only permission checks
* - Sister group link management
* - Banner recreation and inheritance
* - Group creation, updates, and deletion
* - Group data retrieval with optional parent/sibling info
* - Admin permission checks
* - Minimum timestamp calculations
*/
import { query, transaction, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { parseTimestampToSeconds, parseTimestampToSecondsOrDefault } from '../../utils/parseTimestamp.js';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates, BannerRules, getConfig, addExtraConfigToOrgaData } from '../../utils/compileTemplates.js';
import { getUID, isValidUID } from '../../utils/UUIDs.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { addUpdateList, addUpdateEntry } from '../../server.ws.js';
import { recreateJobsGroup } from '../job/utilities.js';
import { isObjectAdmin } from '../../utils/authChecks.js';
import { publishEvent } from '../../utils/events.js';
import { rebuildFeesGroup } from '../../tree/rebuildFees.js';
import { errorLoggerUpdate } from '../../utils/requestLogger.js';
import { keysEqual } from '../../utils/keyCompare.js';
import { timestampCheck } from '../person/migratePerson.js';
// ---------------------------------------------------------------------------
// Private helpers
// ---------------------------------------------------------------------------
/**
* Checks if the current user has read-only access to group accounts.
* Returns true if accounts should be protected from writes for this user.
* @param {ExpressRequestAuthorized} req
* @returns {Promise<boolean>}
*/
const accountsReadOnly = async (req) => {
const config = await getConfig('db', req);
if (config.fees?.listManagersGroupAccounts) {
const result = await query(`
SELECT Main.Title, Member.Display, ObjectBase.Data
FROM ObjectBase
INNER JOIN ObjectBase AS Main ON (Main.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN ObjectBase AS list ON (list.UID = Links.UIDTarget AND list.Type IN ('list','dlist'))
WHERE Main.Type IN ('person','extern')
AND ObjectBase.UIDBelongsTo = ?
AND Links.UIDTarget = ?
`, [
UUID2hex(req.session.user),
UUID2hex(config.fees.listManagersGroupAccounts)
]);
return result.length === 0;
}
return false;
};
/**
* Manages the sister-group link for a group.
* If belongsTo changes, removes old memberS link, inserts new one, and syncs members.
* @param {ExpressRequestAuthorized} req
* @param {Buffer} UID - Group UID (hex Buffer)
* @returns {Promise<object|null>} The new sister-group object, or null if unchanged/removed
*/
const handleSisterLink = async (req, UID) => {
const belongsTo = req.query.belongsTo ? UUID2hex(req.query.belongsTo) : null;
const rootHex = UUID2hex(req.session.root);
const userHex = UUID2hex(req.session.user);
const oldBT = await query(`SELECT UIDTarget FROM Links WHERE UID=? AND Type='memberS'`, [UID]);
if (oldBT.length === 0 || !belongsTo || !oldBT[0].UIDTarget.equals(belongsTo)) {
await query(`DELETE FROM Links WHERE UID=? AND Type='memberS'`, [UID]);
const members = await query(`SELECT MLink.UID AS UID, ObjectBase.Type FROM Links AS MLink
INNER JOIN ObjectBase ON (ObjectBase.UID=MLink.UID AND ObjectBase.Type IN ('person','extern','job','guest','group','ggroup'))
WHERE MLink.UIDTarget=? AND MLink.Type IN ('member','memberA','memberS')`, [UID]);
if (belongsTo) {
await query(`INSERT INTO Links (UID,Type,UIDTarget) VALUES(?,'memberS',?)`, [UID, belongsTo]);
if (members.length > 0) {
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'member',?)`,
members.map(m => ([m.UID, belongsTo])), { batch: true });
}
const s = await query(`SELECT ObjectBase.UID,ObjectBase.Type,ObjectBase.Title,Member.Display AS Display,
ObjectBase.dindex, Member.Data AS Data,ObjectBase.gender, ObjectBase.hierarchie, ObjectBase.stage ,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type ='group'`,
[belongsTo],
{ cast: ['UUID', 'json'], log: false }
);
const oldBTUID = oldBT.length > 0 ? oldBT[0].UIDTarget : null;
if (oldBTUID) {
await queueAdd(rootHex, userHex, 'group', UID, UID, oldBTUID, null, null);
}
await queueAdd(rootHex, userHex, 'group', UID, UID, null, belongsTo, null);
return s[0];
} else {
const oldBTUID = oldBT.length > 0 ? oldBT[0].UIDTarget : null;
if (oldBTUID) {
await queueAdd(rootHex, userHex, 'group', UID, UID, oldBTUID, null, null);
}
}
}
return null;
};
/**
* Recursively propagates a banner update to child groups, jobs, and persons.
* @param {Buffer} UID - Group UID
* @param {string} banner - Banner URL/path
* @param {object} bannerRules - Banner inheritance rules
* @param {ExpressRequestAuthorized} req
*/
const recreateBanner = async (UID, banner, bannerRules, req) => {
try {
const children = await query(`SELECT Member.Data, ObjectBase.Type, ObjectBase.Data AS OData,
ObjectBase.UID, gMember.Data AS gData, Links.Type AS LType
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Links AS gLink ON (gLink.UID=ObjectBase.UID AND gLink.Type='memberA')
INNER JOIN Member AS gMember ON (gMember.UID=gLink.UIDTarget)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('memberA') )
WHERE Links.UIDTarget=? AND ObjectBase.Type IN ('job','group','person')
GROUP BY ObjectBase.UID
`,
[UID],
{
cast: ['json'],
filter: (entry) => {
return (bannerRules.group[entry.Data.hierarchie][0] === 'inherit' && entry.Type === 'group' ||
bannerRules.group[entry.gData.hierarchie].includes('inherit') && entry.Type === 'job' ||
(bannerRules.group[entry.gData.hierarchie].includes('inherit') || entry.LType === 'memberA') && entry.Type === 'person'
);
}
});
const updatedGroups = [];
for (const child of children) {
if (child.Type === 'group' || child.Type === 'person') {
const NewData = { ...child.Data, banner: banner };
await query(`UPDATE Member SET Data = ? WHERE UID=?`,
[JSON.stringify(NewData), child.UID]);
if (child.Type === 'group') {
recreateBanner(child.UID, banner, bannerRules, req);
updatedGroups.push(child.UID);
publishEvent(`/change/group/${req.session.root}/${HEX2uuid(UID)}`, {
organization: req.session.root,
data: { banner: NewData.banner }
});
}
} else {
if (child.OData.banner !== banner) {
await query(`UPDATE ObjectBase SET Data = ? WHERE UID=?`,
[JSON.stringify({ ...child.OData, banner: banner }), child.UID]);
}
}
}
const parents = await query(`SELECT Links.UIDTarget AS UID FROM Links WHERE Links.UID=? AND Links.Type IN ('member','memberA','memberS','memberG')`, [UID]);
addUpdateList(parents.map(el => el.UID));
addUpdateList([UID, ...updatedGroups]);
} catch (e) {
errorLoggerUpdate(e);
}
};
// ---------------------------------------------------------------------------
// Exported service functions
// ---------------------------------------------------------------------------
/**
* @typedef {Object} ServiceResult
* @property {boolean} success
* @property {any} [result]
* @property {string} [message]
* @property {number} [status] - HTTP status to respond with (default 200)
*/
/**
* Creates a new group under a parent, or updates an existing group by UID.
*
* When the group UID from the request body does not yet exist in the DB a new
* group is inserted as a child of UIDparent. When it already exists (update
* path) its Member data is refreshed and sister-group links are re-evaluated.
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<ServiceResult>}
*/
export const createOrUpdateGroup = async (req) => {
const rootHex = UUID2hex(req.session.root);
const userHex = UUID2hex(req.session.user);
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
const template = Templates[req.session.root].group;
const UID = await getUID(req);
const object = await renderObject(template, req.body, req);
const UIDgroup = UUID2hex(req.params.UIDparent);
const accountsProtected = await accountsReadOnly(req);
const mgroups = await query(`
SELECT Member.Data,ObjectBase.UID,Member.Display,ObjectBase.Title,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='group'`,
[UIDgroup], { cast: ['json'] }
);
if (!mgroups.length) {
return { success: false, status: 300, message: 'invalid group UID' };
}
const group = mgroups[0];
if (group.Data.gender !== req.body.gender && group.Data.gender !== 'C' && group.Data.gender !== 'B' && req.body.gender !== 'B' && req.body.gender !== 'C') {
return { success: false, status: 300, message: 'invalid group gender' };
}
if (group.Data.stage !== req.body.stage && group.Data.stage !== 0) {
return { success: false, status: 300, message: 'invalid group stage' };
}
if (BannerRules[req.session.root].group[req.body.hierarchie] === 'inherit') {
if (group.Data.banner) {
req.body.banner = group.Data.banner;
}
} else if (BannerRules[req.session.root].group[req.body.hierarchie].includes('inherit') && !req.body.banner) {
req.body.banner = group.Data.banner;
}
const groups = await query(`
SELECT Member.Data,Links.UIDTarget AS MemberA,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
Title,UIDBelongsTo
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON(Links.Type='memberA' AND Links.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='group'`, [UID]);
if (!groups.length) {
// --- Create new group ---
const limitedTimestamp = await timestampCheck(UIDgroup, timestamp);
if (!isValidUID(req.body.UID)) {
return { success: false, status: 300, message: 'invalid UID format in body.UID' };
}
const NewData = { ...req.body, UID: undefined };
if (accountsProtected) {
NewData.accounts = [];
}
await transaction(async (connection) => {
await query(`
INSERT INTO ObjectBase(UID,UIDuser,Type,UIDBelongsTo,Title, dindex,hierarchie,stage,gender)
VALUES (?,?,'group',?,?,?,?,?,?)`,
[UID, userHex, UID, object.Title, 0,
object.hierarchie, object.stage, object.gender],
{ connection, log: false });
await query(`INSERT IGNORE INTO Links(UID, Type, UIDTarget,UIDuser) VALUES (?,'memberA',?,?)`,
[UID, UIDgroup, userHex],
{ connection, log: false });
}, { backDate: limitedTimestamp });
await query(`INSERT INTO Member
(UID,Display,SortName,FullTextIndex, Data)
VALUES(?,?,?,?,?)
ON DUPLICATE KEY UPDATE Display=VALUE(Display),SortName=VALUE(SortName),FullTextIndex=VALUE(FullTextIndex),Data=VALUE(Data)`,
[UID, object.Display, object.SortIndex, object.FullTextIndex, JSON.stringify(NewData)]);
queueAdd(rootHex, userHex, 'group', object.UID, object.UID, null, UIDgroup, timestamp);
object.sibling = await handleSisterLink(req, UID);
publishEvent(`/add/group/group/${req.session.root}/${req.params.UIDparent}`, {
organization: req.session.root,
data: [HEX2uuid(object.UID)]
});
return { success: true, result: { ...object, UID: HEX2uuid(object.UID), Data: NewData } };
} else {
// --- Update existing group ---
if (!await isObjectAdmin(req, UIDgroup)) {
return { success: false, status: 403, message: 'user not authorized to change this group' };
}
const existingGroup = { ...groups[0], Data: JSON.parse(groups[0].Data) };
const updateTimestamp = parseTimestampToSecondsOrDefault(req.query.timestamp, groups[0].validFrom + 1);
if (existingGroup.Data.hierarchie !== req.body.hierarchie) {
return { success: false, message: 'you can not update hierarchie of an existing group' };
}
if (existingGroup.Data.stage !== req.body.stage) {
return { success: false, message: 'you can not update stage of an existing group' };
}
if (existingGroup.Data.gender !== req.body.gender) {
return { success: false, message: 'you can not update gender of an existing group' };
}
if (JSON.stringify(existingGroup.Data.fees) !== JSON.stringify(req.body.fees)) {
rebuildFeesGroup(UID, req.session.root);
}
const NewData = { ...req.body, UID: undefined };
if (accountsProtected) {
NewData.accounts = existingGroup.Data.accounts;
}
if (existingGroup.Title !== object.Title) {
await transaction(async (connection) => {
await connection.query(`UPDATE ObjectBase SET Title=? WHERE UID=?`,
[object.Title, UID]);
if (!existingGroup.MemberA.equals(UIDgroup)) {
return { result: false, message: 'group migration currently not supported' };
}
recreateJobsGroup(req, UIDgroup, updateTimestamp);
return { success: true, result: { ...object, UID: HEX2uuid(object.UID), Data: NewData } };
}, { backDate: updateTimestamp });
}
await query(`UPDATE Member SET
Display=?,SortName=?,FullTextIndex=?, Data=?
WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex, JSON.stringify(NewData), UID]);
object.sibling = await handleSisterLink(req, UID);
addUpdateList(UID);
const [equal, diff] = keysEqual(NewData, existingGroup.Data);
if (!equal) {
publishEvent(`/change/group/${req.session.root}/${HEX2uuid(UID)}`, {
organization: req.session.root,
data: { UID: HEX2uuid(UID), diff },
backDate: updateTimestamp
});
}
addUpdateEntry(UID, { group: { ...object, Data: NewData, UID: req.params.UID, parent: existingGroup } });
return { success: true, result: { ...object, UID: HEX2uuid(UID), Data: NewData } };
}
};
/**
* Updates an existing group's display data, sister-group link, banner, and fees.
* Unlike createOrUpdateGroup, this merges the request body over existing data
* rather than replacing it, and rejects changes to immutable fields.
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<ServiceResult>}
*/
export const updateGroupData = async (req) => {
const UID = UUID2hex(req.params.UID);
const accountsProtected = await accountsReadOnly(req);
const mgroups = await query(`SELECT Member.Data,ObjectBase.Title,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN Member ON (ObjectBase.UID=Member.UID)
WHERE ObjectBase.UID=? `, [UID], { cast: ['json'] });
if (!mgroups.length) {
return { success: false, status: 300, message: 'invalid group UID' };
}
const timestamp = parseTimestampToSecondsOrDefault(req.query.timestamp, Number(mgroups[0].validFrom));
const group = mgroups[0];
if (req.body.hierarchie && group.Data.hierarchie !== req.body.hierarchie) {
return { success: false, message: 'you can not update hierarchie of an existing group' };
}
if (req.body.stage && group.Data.stage !== req.body.stage) {
return { success: false, message: 'you can not update stage of an existing group' };
}
if (req.body.gender && group.Data.gender !== req.body.gender) {
return { success: false, message: 'you can not update gender of an existing group' };
}
const NewData = { ...group.Data, ...req.body, UID: UID };
if (accountsProtected) {
NewData.accounts = group.Data.accounts;
}
const template = Templates[req.session.root].group;
const object = await renderObject(template, NewData, req);
if (mgroups[0].Title !== object.Title) {
await query(`UPDATE ObjectBase SET Title=? WHERE UID=?`,
[object.Title, UID], { backDate: timestamp });
}
await query(`UPDATE Member SET
Display=?,SortName=?,FullTextIndex=?,Data=?
WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex,
JSON.stringify({ ...NewData, UID: undefined }), UID]);
if (req.query.belongsTo && req.query.belongsTo !== 'undefined') {
object.sibling = await handleSisterLink(req, UID);
}
recreateJobsGroup(req, UID, timestamp);
if (NewData.banner !== group.Data.banner) {
recreateBanner(UID, NewData.banner, BannerRules[req.session.root], req);
}
delete NewData.UID;
const [equal, diff] = keysEqual(NewData, group.Data);
if (!equal) {
publishEvent(`/change/group/${req.session.root}/${HEX2uuid(UID)}`, {
organization: req.session.root,
data: { UID: HEX2uuid(UID), diff },
backDate: timestamp
});
if (req.params.UID === req.session.root) {
const config = await getConfig('db', req.session.root);
publishEvent(`/change/orga/${req.session.root}`, {
organization: req.session.root,
data: { Display: object.Display, OrgaShort: config.OrgaShort },
backDate: timestamp
});
}
}
if (req.body.fees && (JSON.stringify(group.Data.fees) !== JSON.stringify(req.body.fees))) {
rebuildFeesGroup(UID, req.session.root);
}
if (req.params.UID === req.session.root && req.body.extraConfig) {
addExtraConfigToOrgaData(req.session.root, req.body.extraConfig);
}
addUpdateEntry(UID, { group: { ...object, Data: NewData, UID: req.params.UID } });
return { success: true, status: 201, result: { ...object, UID: HEX2uuid(UID), Data: NewData } };
};
/**
* Deletes a group if it has no remaining members.
* Responds with the list of remaining members if deletion is blocked.
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<ServiceResult>}
*/
export const deleteGroupById = async (req) => {
const UID = UUID2hex(req.params.UID);
const hasLinks = await query(`
SELECT ObjectBase.UID,
Member.Display AS Display,
ObjectBase.Title
FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UID)
INNER JOIN Member ON (Member.UID=ObjectBase.UIDbelongsTo)
WHERE Links.UIDTarget=?
AND Links.UID<>?
AND Links.Type IN ('member','memberA')
AND ObjectBase.Type IN ('person','extern','guest','ggroup','group','job')
`, [UID, UID], { cast: ['UUID'] });
if (hasLinks.length) {
return { success: false, status: 300, message: 'has still members', result: hasLinks };
}
// Fire-and-forget deletes (order-independent, no backDate needed)
query(`DELETE FROM ObjectBase WHERE UID =?`, [UID]);
query(`DELETE FROM Member WHERE UID =?`, [UID]);
const update = await query(`SELECT UIDTarget,Type FROM Links WHERE UID = ?`, [UID]);
const parentGroup = update.find(l => l.Type === 'memberA')?.UIDTarget;
await query(`DELETE FROM Links WHERE UID=?`, [UID]);
await query(`DELETE FROM Visible WHERE UID=?`, [UID]);
addUpdateList(update.map(el => el.UIDTarget));
publishEvent(`/remove/group/group/${parentGroup}`, {
organization: req.session.root,
data: [req.params.UID]
});
return { success: true };
};
/**
* Retrieves a group by UID with optional parent and sibling data.
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<ServiceResult>}
*/
export const getGroupById = async (req) => {
const UID = UUID2hex(req.params.UID);
if (!UID) {
return { success: false, status: 300, message: 'no UID supplied' };
}
const resa = await query(`SELECT ObjectBase.UID,ObjectBase.UIDBelongsTo,ObjectBase.Type,ObjectBase.Title,
Member.Display AS Display,
ObjectBase.dindex,ObjectBase.gender, ObjectBase.hierarchie, ObjectBase.stage,
Member.Data AS Data,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type IN('group','ggroup')`,
[UID],
{ cast: ['UUID', 'json'] }
);
if (resa.length === 0) {
return { success: false, message: `group ${req.params.UID} does not exist` };
}
const result = resa[0];
if (req.query.parent) {
const p = await query(`SELECT ObjectBase.UID,ObjectBase.Type,ObjectBase.Title,Member.Display,
ObjectBase.dindex,Member.Data, ObjectBase.gender, ObjectBase.hierarchie,
ObjectBase.stage, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.UIDBelongsTo ,UNIX_TIMESTAMP(Links.validFrom) AS LvalidFrom
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='memberA')
WHERE Links.UID=?`,
[UID],
{ cast: ['UUID', 'json'] }
);
if (p.length > 0) {
const ps = await query(`
SELECT Siblings.UID,Siblings.Title,Member.Display, Siblings.dindex,Member.Data,Siblings.gender, Siblings.hierarchie,Siblings.stage,UNIX_TIMESTAMP(Siblings.validFrom) AS validFrom
FROM Links AS SLinks
INNER JOIN ObjectBase AS Siblings ON (Siblings.UID=SLinks.UIDTarget AND Siblings.Type='group')
INNER JOIN Member ON (Member.UID=Siblings.UID)
INNER JOIN Links ON (Links.UIDTarget=Siblings.UID AND Links.Type='member')
WHERE Links.UID=? AND SLinks.UID IN (?) AND SLinks.Type='memberS';
`,
[UID, p.map(pp => UUID2hex(pp.UID))],
{ cast: ['UUID', 'json'], log: false }
);
result.parent = p[0];
if (ps.length > 0) {
result.parentS = ps[0];
}
}
}
if (req.query.sibling) {
const s = await query(`SELECT ObjectBase.UID,ObjectBase.Type,ObjectBase.Title,Member.Display AS Display,
ObjectBase.dindex, Member.Data AS Data,ObjectBase.gender, ObjectBase.hierarchie, ObjectBase.stage, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='memberS')
WHERE Links.UID=? AND ObjectBase.Type='group'`,
[UID],
{ cast: ['UUID', 'json'], log: false }
);
result.sibling = s[0];
}
return { success: true, result };
};
/**
* Checks whether the current user has admin rights for the specified group.
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<ServiceResult>}
*/
export const checkGroupAdminStatus = async (req) => {
const admin = await isObjectAdmin(req, req.params.UID);
return { success: true, result: admin };
};