// @ts-check
import { query, UUID2hex, HEX2uuid, transaction } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates, BannerRules, getConfig, phonetikArray } from '../../utils/compileTemplates.js';
import { jobQualified } from '../job/utilities.js';
import _ from 'lodash';
// ---------------------------------------------------------------------------
// Shared write helpers
// ---------------------------------------------------------------------------
/**
* Updates system-versioned ObjectBase columns with proper backDate handling.
* Finds the earliest safe timestamp for the backDate and wraps the UPDATE in a
* transaction so MariaDB records the change against the existing history row.
*
* @param {Buffer} uid - Binary UID of the ObjectBase row
* @param {Object} fields - Column/value map for the SET clause,
* e.g. { Title, SortName, hierarchie, stage, gender }
* @param {number} validFrom - UNIX timestamp of the current row's validFrom
*/
export const updateObjectBaseVersioned = async (uid, fields, validFrom) => {
const latestBefore = await query(
`SELECT UNIX_TIMESTAMP(MAX(validUntil)) as backDate FROM ObjectBase FOR SYSTEM_TIME ALL
WHERE UID=? AND validUntil<'2038-01-01'`,
[uid], { log: false }
);
const backDate = parseFloat(latestBefore[0].backDate);
const timestamp = backDate > validFrom ? backDate : validFrom;
const keys = Object.keys(fields);
const setClauses = keys.map(k => `${k}=?`).join(', ');
const values = [...keys.map(k => fields[k]), uid];
await transaction(async (connection) => {
await query(`UPDATE ObjectBase SET ${setClauses} WHERE UID=?`, values, { connection });
}, { backDate: (timestamp + 3600) });
};
/**
* Updates the display fields in the Member table (no system versioning).
*
* @param {Buffer} uid
* @param {string|null} display
* @param {string|null} sortIndex
* @param {string|null} fullTextIndex
*/
export const updateMemberDisplay = async (uid, display, sortIndex, fullTextIndex) => {
await query(
`UPDATE Member SET Display=?, SortName=?, FullTextIndex=? WHERE UID=?`,
[display, sortIndex, fullTextIndex, uid]
);
};
// ---------------------------------------------------------------------------
// Type handlers — with Member row
// (ObjectBase ← system-versioned UPDATE, Member ← plain UPDATE)
// ---------------------------------------------------------------------------
/**
* Recreates person / extern / guest objects.
* Inherits stage, hierarchie and banner from the member group, then re-renders.
*
* @param {string} type - 'person' | 'extern' | 'guest'
* @param {string} root - Organisation UUID (string)
* @param {any} template
* @param {any} req
*/
export const recreatePersonsExternGuests = async (type, root, template, req) => {
const members = await query(
`SELECT ObjectBase.UID, Member.Data, PGroup.Data AS GroupData, ObjectBase.Type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom, ObjectBase.SortName, ObjectBase.Title,
ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage, ObjectBase.Data AS BaseData,
Member.FullTextIndex, Member.PhonetikIndex, Member.SortName AS SortIndex
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Member AS PGroup ON (PGroup.UID=GLink.UIDTarget)
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`,
[type, UUID2hex(root)],
{ cast: ['json'] }
);
for (const member of members) {
const memberData = member.Data;
const GroupData = member.GroupData;
const oldData = _.cloneDeep(memberData);
const oldMemberStage = memberData.stage;
const config = await getConfig('db', req);
memberData.stage = GroupData.stage > 0 ? GroupData.stage : config.DefaultStage;
if (oldMemberStage != memberData.stage)
await query(`UPDATE Member SET Data=? WHERE UID=?`, [JSON.stringify(memberData), member.UID]);
memberData.hierarchie = GroupData.hierarchie;
if (BannerRules[root].member[0] === 'inherit') {
if (GroupData.banner) memberData.banner = GroupData.banner;
} else if (BannerRules[root].member.includes('inherit')) {
memberData.banner = GroupData.banner;
}
const object = await renderObject(template, { UID: HEX2uuid(member.UID), ...memberData, group: GroupData }, req);
const phonetikIndex = phonetikArray([memberData.firstName, memberData.lastName]);
object.UID = member.UID;
if (member.Title !== object.Title || member.SortName !== object.SortBase
|| member.hierarchie !== object.hierarchie || member.gender !== object.gender
|| member.stage !== object.stage) {
await updateObjectBaseVersioned(member.UID, {
Title: object.Title, SortName: object.SortBase,
hierarchie: object.hierarchie, stage: object.stage, gender: object.gender,
Data: JSON.stringify(member.BaseData)
}, member.validFrom);
}
if (type !== 'guest') {
if (oldData.banner !== memberData.banner || member.SortIndex != object.SortIndex
|| member.FullTextIndex !== object.FullTextIndex || member.PhonetikIndex !== phonetikIndex
|| oldData.stage !== memberData.stage || oldData.hierarchie !== memberData.hierarchie) {
await query(
`UPDATE Member SET Display=?, SortName=?, FullTextIndex=?, PhonetikIndex=?, Data=? WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex, phonetikIndex, JSON.stringify(memberData), member.UID]
);
}
}
}
};
/**
* Recreates event / location objects.
* ObjectBase holds system-versioned fields; Member holds display fields.
*
* @param {string} type - 'event' | 'location'
* @param {string} root
* @param {any} template
* @param {any} req
*/
export const recreateEventsLocations = async (type, root, template, req) => {
const app = type === 'event' ? 'events' : 'locations';
const objects = await query(
`SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo, Member.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title, Member.Display, ObjectBase.SortName AS SortBase, Member.SortName AS SortIndex,
ObjectBase.Type, Member.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA','location'))
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`,
[type, UUID2hex(root)],
{ cast: ['json'], log: false }
);
for (const obj of objects) {
const object = await renderObject(template, { UID: HEX2uuid(obj.UID), ...obj.Data }, req, app);
if (obj.Title !== object.Title || obj.SortBase !== object.SortBase
|| obj.hierarchie !== object.hierarchie || obj.gender !== object.gender
|| obj.stage !== object.stage) {
await updateObjectBaseVersioned(obj.UID, {
Title: object.Title, SortName: object.SortBase,
hierarchie: object.hierarchie, stage: object.stage, gender: object.gender
}, obj.validFrom);
}
if (obj.Display !== object.Display || obj.SortIndex !== object.SortIndex
|| obj.FullTextIndex !== object.FullTextIndex) {
await updateMemberDisplay(obj.UID, object.Display, object.SortIndex, object.FullTextIndex);
}
}
};
/**
* Recreates group / list / dlist / email (and any other type that stores its
* data in Member.Data and display fields in Member).
* ObjectBase holds system-versioned fields; Member holds display fields.
*
* @param {string} type
* @param {string} root
* @param {any} template
* @param {any} req
*/
export const recreateWithMemberRow = async (type, root, template, req) => {
const objects = await query(
`SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo, Member.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title, Member.Display, Member.SortName AS SortIndex, ObjectBase.Type,
Member.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`,
[type, UUID2hex(root)],
{ cast: ['json'] }
);
for (const obj of objects) {
const object = await renderObject(template, {
UID: HEX2uuid(obj.UID),
...obj.Data,
hierarchie: obj.hierarchie,
stage: obj.stage,
gender: obj.gender,
}, req);
if (obj.Title !== object.Title || obj.hierarchie !== object.hierarchie
|| obj.gender !== object.gender || obj.stage !== object.stage) {
await updateObjectBaseVersioned(obj.UID, {
Title: object.Title,
hierarchie: object.hierarchie, stage: object.stage, gender: object.gender
}, obj.validFrom);
}
if (obj.Display !== object.Display || obj.SortIndex !== object.SortIndex
|| obj.FullTextIndex !== object.FullTextIndex) {
await updateMemberDisplay(obj.UID, object.Display, object.SortIndex, obj.FullTextIndex);
}
}
};
// ---------------------------------------------------------------------------
// Type handlers — without Member row
// (ObjectBase only — either versioned UPDATE or DELETE+INSERT)
// ---------------------------------------------------------------------------
/**
* Recreates ggroup (guest group) objects.
* All fields live in ObjectBase — no own Member row.
* Uses versioned UPDATE (not DELETE+INSERT) to preserve clean history.
*
* @param {string} root
* @param {any} template
* @param {any} req
*/
export const recreateGgroups = async (root, template, req) => {
const ggroups = await query(
`SELECT ggroup.UID, ggroup.UIDBelongsTo, Member.Data,
UNIX_TIMESTAMP(ggroup.validFrom) AS validFrom,
ggroup.Title, ggroup.Display, ggroup.SortName AS SortBase, ggroup.FullTextIndex,
ggroup.dindex, ggroup.hierarchie, ggroup.gender, ggroup.stage,
GroupMember.Data AS GroupData
FROM ObjectBase AS ggroup
INNER JOIN Member ON (Member.UID = ggroup.UIDBelongsTo)
INNER JOIN Links AS OrgLink ON (OrgLink.UID = ggroup.UIDBelongsTo
AND OrgLink.Type IN ('member','memberA') AND OrgLink.UIDTarget = ?)
LEFT JOIN Links AS GALink ON (GALink.UID = ggroup.UID AND GALink.Type = 'memberGA')
LEFT JOIN Member AS GroupMember ON (GroupMember.UID = GALink.UIDTarget)
WHERE ggroup.Type = 'ggroup'`,
[UUID2hex(root)],
{ cast: ['json'] }
);
for (const ggroup of ggroups) {
const object = await renderObject(template, {
UID: HEX2uuid(ggroup.UID),
...ggroup.Data,
group: ggroup.GroupData
}, req);
if (ggroup.Title !== object.Title || ggroup.SortBase !== object.SortBase
|| ggroup.hierarchie !== object.hierarchie || ggroup.gender !== object.gender
|| ggroup.stage !== object.stage || ggroup.Display !== object.Display
|| ggroup.FullTextIndex !== object.FullTextIndex) {
await updateObjectBaseVersioned(ggroup.UID, {
Title: object.Title, Display: object.Display, SortName: object.SortBase,
FullTextIndex: object.FullTextIndex,
hierarchie: object.hierarchie, stage: object.stage, gender: object.gender
}, ggroup.validFrom);
}
}
};
/**
* Recreates job objects.
* ObjectBase only (UIDBelongsTo = person UID). Uses DELETE+INSERT because
* jobs carry a computed `dindex` (qualification status) that must be replaced.
*
* @param {string} root
* @param {any} template
* @param {any} req
*/
export const recreateJobs = async (root, template, req) => {
const jobs = await query(
`SELECT ObjectBase.UID, ObjectBase.Data, ObjectBase.dindex as qualified, PGroup.Data AS GroupData,
Person.UID AS UIDperson, FunctionT.Data AS FunctionData, ObjectBase.Title,
FunctionT.UID AS UIDfunction, UNIX_TIMESTAMP(Person.ValidFrom) AS validFrom, ObjectBase.SortName,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Member AS PGroup ON (PGroup.UID=GLink.UIDTarget)
INNER JOIN ObjectBase AS Person ON (ObjectBase.UIDBelongsTo=Person.UID AND Person.Type='person')
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
INNER JOIN ObjectBase AS FunctionT ON (FunctionT.UID=FLink.UID)
WHERE ObjectBase.Type='job' AND Links.UIDTarget=?
GROUP BY ObjectBase.UID`,
[UUID2hex(root)],
{ cast: ['json'] }
);
for (const job of jobs) {
const functionData = job.FunctionData;
const qualified = await jobQualified(job.UIDperson, functionData.qualification ?? {}, Date.now() / 1000);
const Data = {
UID: HEX2uuid(job.UID),
qualified,
function: { ...job.FunctionData, functionUID: HEX2uuid(job.UIDfunction) },
group: job.GroupData
};
const object = await renderObject(template, Data, req);
if (job.Title !== object.Title || job.SortName !== object.SortBase || job.dindex !== qualified
|| job.hierarchie !== object.hierarchie || job.gender !== object.gender || job.stage !== object.stage) {
transaction(async (connection) => {
await query(`DELETE FROM ObjectBase WHERE UID=?`, [job.UID], { connection });
await query(
`INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,SortName,dindex,hierarchie,stage,gender,Data)
VALUES (?,'job',?,?,?,?,?,?,?,?)`,
[job.UID, job.UIDperson, object.Title, object.SortBase, qualified,
object.hierarchie, object.stage, object.gender, JSON.stringify(Data)],
{ connection }
);
}, { backDate: (parseFloat(job.validFrom) + 3600) });
}
}
};
/**
* Recreates function template objects.
* ObjectBase only (UIDBelongsTo = org UID). Uses DELETE+INSERT.
*
* @param {string} root
* @param {any} template
* @param {any} req
*/
export const recreateFunctions = async (root, template, req) => {
const objects = await query(
`SELECT ObjectBase.UID, UIDBelongsTo, ObjectBase.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title, ObjectBase.Display, ObjectBase.SortName, ObjectBase.Type, ObjectBase.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
WHERE ObjectBase.Type=? AND ObjectBase.UIDBelongsTo=?`,
['function', UUID2hex(root)],
{ cast: ['json'] }
);
for (const obj of objects) {
const object = await renderObject(template, { UID: HEX2uuid(obj.UID), ...obj.Data }, req);
if (obj.Title !== object.Title || obj.Display !== object.Display || obj.SortName !== object.SortIndex
|| obj.hierarchie !== object.hierarchie || obj.FullTextIndex !== object.FullTextIndex
|| obj.gender !== object.gender || obj.stage !== object.stage) {
await transaction(async (connection) => {
await query(`DELETE FROM ObjectBase WHERE UID=?`, [obj.UID], { connection });
await query(
`INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName,FullTextIndex,dindex,hierarchie,stage,gender,Data)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,
[obj.UID, obj.Type, UUID2hex(root), object.Title, object.Display, object.SortIndex,
object.FullTextIndex, object.dindex, object.hierarchie, object.stage, object.gender, obj.Data],
{ connection }
);
}, { backDate: (parseFloat(obj.validFrom) + 1) });
}
}
};
// ---------------------------------------------------------------------------
// Main dispatcher
// ---------------------------------------------------------------------------
/**
* Dispatches recreate logic to the appropriate handler based on object type.
*
* @param {string} type - Object type from req.params.type
* @param {string} root - Organisation UUID (string)
* @param {any} req
*/
export const dispatchRecreate = async (type, root, req) => {
const template = Templates[root][type];
if (['person', 'extern', 'guest'].includes(type)) {
await recreatePersonsExternGuests(type, root, template, req);
} else if (type === 'job') {
await recreateJobs(root, template, req);
} else if (type === 'function') {
await recreateFunctions(root, template, req);
} else if (type === 'ggroup') {
await recreateGgroups(root, template, req);
} else if (['event', 'location'].includes(type)) {
await recreateEventsLocations(type, root, template, req);
} else {
// group, list, dlist, email, and any future type that stores data in Member
await recreateWithMemberRow(type, root, template, req);
}
};