/**
* Event Service Layer
*
* This service manages events in the CommTool system. Events are time-based
* activities linked to groups and event templates.
*
* Key concepts:
* - Events: Time-based activities with location, participants, and permissions
* - Event Templates: Define structure and UI for events
* - Groups: Events belong to groups for organization
* - Visibility: Controls who can see and edit events
*
* Database structure:
* - ObjectBase: Main table storing events with Type='event'
* - Member: Extended data including geo-location and custom fields
* - Links: Junction table linking events to templates and groups
* - Visible: Access control for events
*
* @module EventService
*/
// @ts-check
/**
* @import {ExpressRequestAuthorized} from './../../types.js'
*/
import { query, UUID2hex, HEX2uuid, pool } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates } from '../../utils/compileTemplates.js';
import { getUID } from '../../utils/UUIDs.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js';
import { isObjectAdmin, isAdmin } from '../../utils/authChecks.js';
import { addUpdateEntry, addUpdateList } from '../../server.ws.js';
import { publishEvent } from '../../utils/events.js';
import { diff } from 'deep-object-diff';
import { insertList } from '../../Router/list.js';
import { addGroupVisibility, rebuildEventVisibility } from '../shared/eventVisibility.js';
import { getConfig } from '../../utils/compileTemplates.js';
/**
* Create a new event
*
* Creates an event with specified template and group association.
* Handles geo-location, visibility, and participant lists.
*
* @param {Buffer} groupUID - The group UID in hex format
* @param {Buffer} templateUID - The template UID in hex format
* @param {Object} body - Request body with event data
* @param {Object} session - User session object
* @param {ExpressRequestAuthorized} req - Full request object
* @returns {Promise<Object>} Created event object
* @throws {Error} When validation fails or user is not authorized
*
* Security features:
* - Validates user has admin rights to the group
* - Creates visibility entries for creator
* - Links event to group for access control
*/
export const createEvent = async (groupUID, templateUID, body, session, req) => {
const eventUID = await getUID(req);
// Fetch event template
const eTemplates = await query(
`SELECT eventT.Data, eventT.Display FROM ObjectBase
AS eventT WHERE eventT.UID=? AND Type='eventT'`,
[templateUID],
{ cast: ['json'] }
);
if (!eTemplates.length) {
throw new Error('missing or unknown event template');
}
if (!isObjectAdmin(req, groupUID)) {
throw new Error('you are not authorized to add an event to this group');
}
const eTemplate = eTemplates[0];
const eData = eTemplate.Data;
// Fetch group data
const [group] = await query(
`SELECT Member.Data, mygroup.UID AS UIDGroup
FROM ObjectBase AS mygroup
INNER JOIN Member ON (Member.UID=mygroup.UID)
WHERE mygroup.UID=?`,
[groupUID]
);
if (!group) {
throw new Error('invalid group UID');
}
// Ensure extraParameter has UIDqueryGroup
if (!body.extraParameter || !Array.isArray(body.extraParameter)) {
body.extraParameter = [{ name: 'UIDqueryGroup', type: 'string' }];
}
if (!body.extraParameter.find(el => el.name === 'UIDqueryGroup')) {
body.extraParameter.push({ name: 'UIDqueryGroup', type: 'string' });
}
const groupData = JSON.parse(group.Data);
const template = Templates[session.root].event;
const object = await renderObject(
template,
{
template: eData,
group: groupData,
...body
},
req,
'events'
);
// Check if event already exists
const events = await query(
`SELECT Member.Data FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='event'`,
[eventUID]
);
const event = events[0];
// Get geo data
let geoResult;
if (body.location && body.location.UID) {
geoResult = await query(
`SELECT Geo FROM Member WHERE UID=?`,
[UUID2hex(body.location.UID)]
);
} else {
geoResult = await query(`SELECT st_geometryfromtext('POINT(0 0)') AS Geo`, []);
}
if (!event) {
// Insert new event
await query(
`INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,SortName,stage,gender,hierarchie,dindex)
VALUES (?,'event',?,?,?,?,?,?,?)`,
[
object.UID, groupUID, object.Title, object.SortBase,
object.stage, object.gender, object.hierarchie, object.dindex
]
);
await query(
`INSERT INTO Member (UID,Display,SortName,FullTextIndex,Data,Member.Geo)
VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE Data=VALUE(Data)`,
[
object.UID, object.Display, object.SortIndex, object.FullTextIndex,
JSON.stringify({ ...body, UID: undefined }), geoResult[0].Geo
]
);
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,'event',?)`,
[eventUID, templateUID]
);
// Link event to group
await query(
`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'memberA',?)`,
[eventUID, groupUID]
);
// Make visible to current user
await query(
`INSERT INTO Visible(UID,UIDUser,Type) VALUES(?,?,'changeable')`,
[object.UID, UUID2hex(session.user)]
);
// Create participant list
const partList = await insertList(
{
...req,
query: { private: true },
body: {
name: `${eTemplate.Data.participantListPrefix} ${eTemplate.Display} ${object.Display}`,
tag: ['participant', 'event', 'all']
}
},
eventUID,
'dlist'
);
// Handle group visibility
addGroupVisibility(eventUID, groupUID, req);
// Publish event
publishEvent(
`/add/eventT/event/${HEX2uuid(templateUID)}`,
{
organization: session.root,
data:HEX2uuid(object.UID)
}
);
} else {
throw new Error('Event already exists, use update instead');
}
addUpdateList(UUID2hex(session.root));
addUpdateEntry(eventUID, { event: { UID: HEX2uuid(eventUID), ...object, Data: body } });
return { ...object, UID: HEX2uuid(object.UID) };
};
/**
* Update an existing event
*
* Updates event data, template references, and group associations.
* Tracks changes using diff for audit purposes.
*
* @param {Buffer} UID - Event UID in hex format
* @param {Object} body - Request body with updated event data
* @param {Object} session - User session object
* @param {ExpressRequestAuthorized} req - Full request object
* @returns {Promise<Object>} Updated event data
* @throws {Error} When event not found or user is not authorized
*
* Security features:
* - Validates user has change rights to the event
* - Tracks change history with diff
* - Maintains group ownership validation
*/
export const updateEvent = async (UID, body, session, req) => {
const events = await query(
`SELECT Data FROM Member WHERE UID=?`,
[UID],
{ cast: ['json'] }
);
const event = events[0];
if (!event) {
throw new Error('not found');
}
if (!isObjectAdmin(req, UID)) {
throw new Error('you are not authorized to change this event');
}
const eTemplates = await query(
`SELECT eventT.Data, myGroup.Data AS groupData, myGroup.UID AS UIDgroup
FROM ObjectBase AS eventT
INNER JOIN Links ON (Links.UIDTarget=eventT.UID AND Links.Type='event')
INNER JOIN Member AS myGroup ON (myGroup.UID=eventT.UIDBelongsTo)
WHERE Links.UID=?`,
[UID],
{ cast: ['json'] }
);
const eTemplate = eTemplates[0];
const Data = { ...event.Data, ...body };
// Ensure extraParameter has UIDqueryGroup
if (!Data.extraParameter || !Array.isArray(body.extraParameter)) {
Data.extraParameter = [{ name: 'UIDqueryGroup', type: 'string' }];
}
if (!Data.extraParameter.find(el => el.name === 'UIDqueryGroup')) {
Data.extraParameter.push({ name: 'UIDqueryGroup', type: 'string' });
}
const template = Templates[session.root].event;
const object = await renderObject(
template,
{
template: eTemplate.Data,
group: eTemplate.groupData,
...Data
},
req,
'events'
);
const myDiff = {
UID: HEX2uuid(UID),
diffNew: diff(event.Data, Data),
diffOld: diff(Data, event.Data),
user: session.user
};
await query(
`UPDATE ObjectBase,Member SET
ObjectBase.Title=?,Member.Display=?,Member.SortName=?,
Member.FullTextIndex=?,ObjectBase.stage=?,ObjectBase.gender=?,ObjectBase.hierarchie=?,
ObjectBase.dindex=?,Member.Data=?,ObjectBase.UIDBelongsTo=?
WHERE ObjectBase.UID=? AND Member.UID=ObjectBase.UID`,
[
object.Title, object.Display, object.SortIndex,
object.FullTextIndex, object.stage, object.gender, object.hierarchie,
object.dindex, JSON.stringify(Data), eTemplate.UIDgroup,
UID
]
);
addUpdateEntry(session.root, { event: { UID: HEX2uuid(UID), ...object, Data: Data } });
addUpdateList(UUID2hex(session.root));
return Data;
};
/**
* Delete an event
*
* Removes event, associated links, visibility filters, and related jobs.
* Member data is retained for history.
*
* @param {Buffer} UID - Event UID in hex format
* @param {Object} session - User session object
* @param {ExpressRequestAuthorized} req - Full request object
* @returns {Promise<boolean>} Success status
* @throws {Error} When user is not authorized
*
* Security features:
* - Validates user has admin rights to the event
* - Cascades deletion to related visibility and job objects
* - Preserves Member table for audit trail
*/
export const deleteEvent = async (UID, session, req) => {
if (!isObjectAdmin(req, UID)) {
throw new Error('you are not authorized to delete this event');
}
// Delete event and links
await query(
`DELETE ObjectBase,Links
FROM ObjectBase
LEFT JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type= 'event')
WHERE ObjectBase.UID =? AND ObjectBase.Type ='event'`,
[UID]
);
// Delete visibility filters
await query(
`DELETE ObjectBase,Links FROM ObjectBase
LEFT JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type = 'list')
WHERE Links.UIDTarget =? AND ObjectBase.Type IN ('visible','changeable')`,
[UID]
);
// Delete associated jobs
await query(
`DELETE ObjectBase,Links
FROM ObjectBase
LEFT JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('memberA','member'))
WHERE Links.UIDTarget =? AND ObjectBase.Type ='eventJob'`,
[UID]
);
addUpdateList(UUID2hex(session.root));
return true;
};
/**
* Get a single event by UID
*
* Retrieves complete event details including template data, group info,
* and responsible person assignments.
*
* @param {Buffer} UID - Event UID in hex format
* @param {Object} session - User session object
* @param {ExpressRequestAuthorized} req - Full request object
* @returns {Promise<Object>} Event object with complete details
* @throws {Error} When event not found or not accessible
*
* Security features:
* - Validates event visibility for current user
* - Uses temporal queries for template versioning
* - Returns null if user lacks access rights
*
* Returns complete event data including:
* - Event metadata (UID, Title, Display)
* - Template structure and configuration
* - Group and organization context
* - Responsible person assignments
*/
export const getEvent = async (UID, session, req) => {
if (!UID) {
throw new Error('supplied invalid UID');
}
const eventConfig = await getConfig('events', req);
const minTemplateTime = eventConfig.minTemplateTime;
const result = await query(
`SELECT ObjectBase.UID,ObjectBase.UIDBelongsTo,ObjectBase.Title,eMember.Display,
eMember.Data AS Data , ALink.UIDTarget AS UIDTemplate, Template.Data AS TemplateData,
Template.Display AS DisplayTemplate,
pGroup.Title AS GroupTitle, pMember.Display AS GroupDisplay, pGroup.UID AS UIDgroup,
rMember.UID AS UIDresponsible, job.Title AS titleResponsible, rMember.Display AS responsible
FROM ObjectBase
INNER JOIN Member AS eMember ON (eMember.UID=ObjectBase.UID)
INNER JOIN ObjectBase AS pGroup ON (pGroup.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member AS pMember ON (pMember.UID=pGroup.UID)
INNER JOIN Links AS ALink ON (ALink.UID=ObjectBase.UID AND ALink.Type='event')
INNER JOIN ObjectBase FOR SYSTEM_TIME AS OF IF(ObjectBase.validFrom < TIMESTAMP '${minTemplateTime}',NOW(),ObjectBase.validFrom) AS Template
ON (Template.UID=ALink.UIDTarget AND Template.Type='eventT')
LEFT JOIN (Links AS RLinks INNER JOIN ObjectBase AS job ON (job.UID=RLinks.UID AND job.Type='eventJob' )
INNER JOIN Member AS rMember ON (rMember.UID=job.UIDBelongsTo))
ON (RLinks.UIDTarget=ObjectBase.UID AND RLinks.Type ='memberA')
WHERE ObjectBase.UID=?`,
[UID, UUID2hex(session.user)],
{
log: false,
cast: ['UUID', 'json']
}
);
if (result.length === 0) {
throw new Error('event does not exist or is not accessible for this user');
}
return result[0];
};
/**
* Get visibility SQL clause for event listing
*
* Generates SQL WHERE clause for filtering events based on user visibility.
* Admins see all events, regular users only see visible events.
*
* @param {ExpressRequestAuthorized} req - Request object with session
* @returns {Promise<string>} SQL clause for visibility filtering
*/
const getListVisibleSql = async (req) => {
if (await isAdmin(req.session)) {
return '';
}
return `INNER JOIN Visible ON (Visible.UID=ObjectBase.UID AND Visible.UIDUser=U_UUID2BIN('${req.session.user}'))`;
};
/**
* Get list of events with optional filtering and pagination
*
* Retrieves events with support for:
* - Custom data field selection via JSON paths
* - Date range filtering (after/before timestamps)
* - Geo-location data (lat/lng)
* - Responsible person information
* - User visibility filtering
*
* @param {ExpressRequestAuthorized} req - Request object with query parameters
* @returns {Promise<Array>} Array of event objects
*
* Query parameters:
* - Data: 'all' or JSON array of field paths to retrieve
* - after: Filter events finishing after timestamp
* - before: Filter events starting before timestamp
* - __page: For pagination (handled by controller)
*
* Performance considerations:
* - Uses indexed queries on event type and organization
* - Supports spatial queries for geo-location
* - Groups results to eliminate duplicates from joins
*/
export const getEventListing = async (req) => {
let dataFields = '';
let extraWhere = '';
// Handle custom data fields
if (req.query.Data) {
if (req.query.Data === 'all') {
dataFields = ',Member.Data AS Data';
} else {
let fields = [];
try {
fields = req.query.Data ? JSON.parse(String(req.query.Data)) : null;
} catch (e) {
fields[0] = [req.query.Data];
}
for (const field of fields) {
dataFields += `,JSON_VALUE(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
}
// Filter by date range
if (req.query.after) {
extraWhere += ` AND JSON_VALUE(Member.Data,'$.to')>${parseTimestampToSeconds(req.query.after)}`;
}
if (req.query.before) {
extraWhere += ` AND JSON_VALUE(Member.Data,'$.from')<${parseTimestampToSeconds(req.query.before)}`;
}
const vSql = await getListVisibleSql(req);
const result = await query(
`SELECT
ObjectBase.UID, ObjectBase.Title, Member.Display, ObjectBase.dindex AS fromDate, ObjectBase.stage, ObjectBase.gender,
JSON_VALUE(Member.Data,'$.to') AS toDate, Member.SortName,
ST_X(Member.Geo) AS lat, ST_Y(Member.Geo) AS lng,
pMember.Display AS responsible ${dataFields}
FROM
ObjectBase AS ObjectBase
INNER JOIN Member AS Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type='event')
INNER JOIN ObjectBase AS eventT ON (eventT.UID=Links.UIDTarget)
${vSql}
LEFT JOIN (Links AS RLinks INNER JOIN ObjectBase AS job ON (job.UID=RLinks.UID AND job.Type='eventJob')
INNER JOIN Member AS pMember ON (pMember.UID=job.UIDBelongsTo))
ON (RLinks.UIDTarget=ObjectBase.UID AND RLinks.Type='memberA')
WHERE
ObjectBase.Type='event' AND eventT.UIDBelongsTo=? ${extraWhere}
GROUP BY ObjectBase.UID
ORDER BY
ObjectBase.dindex DESC, ObjectBase.Display`,
[UUID2hex(req.session.root)],
{
cast: ['UUID', 'json'],
log: false
}
);
return result;
};