// @ts-check
import { query, transaction, HEX2uuid } from '@commtool/sql-query';
import { invalidateUserCache } from '../../utils/userUtils.js';
/**
* Ensure identifyer link and self-visibility are in sync for one person.
* @param {Buffer} UIDperson
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, message?: string}>}
*/
export const putUser = async (UIDperson, UIDroot) => {
const persons = await query(
`SELECT JSON_VALUE(Member.Data,'$.UIDuser') AS UIDuser
FROM Member
INNER JOIN Links AS MLink ON (MLink.UID=Member.UID AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Member.UID=?`,
[UIDroot, UIDperson]
);
if (persons.length === 0) {
return { success: false, message: 'person with this UID not found in this organisation' };
}
const UIDidentifyer = persons[0].UIDuser;
if (!UIDidentifyer) {
return { success: false, message: 'person has no UIDuser in Member.Data' };
}
// Links is system-versioned: only write rows that actually change to avoid
// creating unnecessary history entries.
// Delete only stale links (wrong KC UID); INSERT IGNORE is a no-op when the
// correct link already exists, so that case produces zero history entries.
await query(
`DELETE Links FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UIDTarget AND ObjectBase.Type IN ('person','extern'))
WHERE Links.UIDTarget=? AND Links.Type='identifyer' AND Links.UID != U_UUID2BIN(?)`,
[UIDperson, UIDidentifyer]
);
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget)
SELECT U_UUID2BIN(?), 'identifyer', UID FROM ObjectBase WHERE UID=? AND Type IN ('person','extern')`,
[UIDidentifyer, UIDperson]
);
// Visible is not system-versioned; direct upsert is fine.
await query(
`INSERT INTO Visible (UID,Type,UIDUser) VALUES(?,'changeable',?) ON DUPLICATE KEY UPDATE Type='changeable'`,
[UIDperson, UIDperson]
);
return { success: true };
};
/**
* Update a person's UIDuser and rebuild identifyer link.
* @param {Buffer} UIDperson
* @param {Buffer} UIDidentifyer
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, message?: string}>}
*/
export const updatePersonIdentifyer = async (UIDperson, UIDidentifyer, UIDroot) => {
const typeCheck = await query(
`SELECT ObjectBase.Type FROM ObjectBase
INNER JOIN Links AS MLink ON (MLink.UID=ObjectBase.UID AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE ObjectBase.UID=? AND ObjectBase.Type IN ('person','extern') LIMIT 1`,
[UIDroot, UIDperson]
);
if (!typeCheck.length) {
return { success: false, message: 'target is not a person or extern type in this organisation' };
}
await query(
`UPDATE Member SET Data=JSON_SET(Data, '$.UIDuser', ?) WHERE UID=?`,
[HEX2uuid(UIDidentifyer), UIDperson]
);
return putUser(UIDperson, UIDroot);
};
/**
* Sync identifyer links and add self-visibility for all persons in one orga.
* @param {Buffer} UIDsource
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean}>}
*/
export const syncUsersBySource = async (UIDsource, UIDroot) => {
/* const persons = await query(
`SELECT Member.Data,Member.UID FROM Member
INNER JOIN ObjectBase ON (ObjectBase.UIDBelongsTo=Member.UID)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
WHERE Links.UIDTarget=?`,
[UIDsource],
{ cast: ['json'] }
);
/* if (persons.length === 0) {
return { success: false, message: 'no persons found for this organisation' };
}*/
// ── Links (system-versioned): diff-only ─────────────────────────────────
// Snapshot the current identifyer links within the org scope.
const oldLinks = await query(
`SELECT Links.UID, Links.UIDTarget
FROM Links
INNER JOIN ObjectBase AS Item ON (Item.UID=Links.UIDTarget AND Links.Type='identifyer' AND Item.Type IN ('person','extern'))
INNER JOIN ObjectBase AS User ON (User.UIDBelongsTo=Item.UIDBelongsTo)
INNER JOIN Links AS MLink ON (User.UID=MLink.UID AND MLink.Type IN ('member','memberA'))
WHERE MLink.UIDTarget=?`,
[UIDroot]
);
// Compute desired identifyer links from Member.Data (equivalent of the INSERT SELECT).
const newLinks = await query(
`SELECT
U_UUID2BIN(JSON_VALUE(Member.Data,'$.UIDuser')) AS UID,
Main.UIDBelongsTo AS UIDTarget
FROM
ObjectBase AS ObjectBase
INNER JOIN ObjectBase AS Main ON (Main.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Links AS Links ON (Links.UID = ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Member AS Member ON (Main.UIDBelongsTo = Member.UID)
WHERE
Main.Type IN ('person','extern') AND Links.UIDTarget=? AND NOT U_UUID2BIN(JSON_VALUE(Member.Data,'$.UIDuser')) IS NULL
GROUP BY Main.UIDBelongsTo`,
[UIDsource],
{ log: false }
);
const toDelete = oldLinks.filter(old =>
!newLinks.some(n => n.UID.equals(old.UID) && n.UIDTarget.equals(old.UIDTarget))
);
const toAdd = newLinks.filter(n =>
!oldLinks.some(old => old.UID.equals(n.UID) && old.UIDTarget.equals(n.UIDTarget))
);
for (const link of toDelete) {
await query(
`DELETE FROM Links WHERE UID=? AND UIDTarget=? AND Type='identifyer'`,
[link.UID, link.UIDTarget]
);
}
for (const link of toAdd) {
await query(
`INSERT IGNORE INTO Links (UID, Type, UIDTarget) VALUES (?, 'identifyer', ?)`,
[link.UID, link.UIDTarget]
);
}
if(toAdd.length>0) {
// add self visbility for all persons in the orga (INSERT IGNORE is a no-op for existing rows)
await query(
`INSERT IGNORE INTO Visible (UID,Type,UIDUser) VALUES(?,'changeable',?)`,
toAdd.map(link=>([link.UID,link.UID])),
{batch: true}
)
}
return { success: true };
};
/**
* Sync identifyer links for selected persons.
* @param {Buffer[]} users
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean}>}
*/
export const syncUsers = async (users, UIDroot) => {
// ── Links (system-versioned): diff-only ─────────────────────────────────
const oldLinks = await query(
`SELECT Links.UID, Links.UIDTarget
FROM Links
INNER JOIN ObjectBase AS Item ON (Item.UID=Links.UIDTarget AND Links.Type='identifyer' AND Item.Type IN ('person','extern'))
INNER JOIN Links AS MLink ON (MLink.UID=Links.UIDTarget AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Links.UIDTarget IN (?)`,
[UIDroot, users]
);
const newLinks = await query(
`SELECT
U_UUID2BIN(JSON_VALUE(Member.Data,'$.UIDuser')) AS UID,
Main.UID AS UIDTarget
FROM ObjectBase AS Main
INNER JOIN Member AS Member ON (Main.UIDBelongsTo = Member.UID)
INNER JOIN Links AS MLink ON (MLink.UID=Main.UID AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE
Main.UID IN (?) AND Main.Type IN ('person','extern') AND NOT U_UUID2BIN(JSON_VALUE(Member.Data,'$.UIDuser')) IS NULL
GROUP BY Member.UID`,
[UIDroot, users]
);
const toDelete = oldLinks.filter(old =>
!newLinks.some(n => n.UID.equals(old.UID) && n.UIDTarget.equals(old.UIDTarget))
);
const toAdd = newLinks.filter(n =>
!oldLinks.some(old => old.UID.equals(n.UID) && old.UIDTarget.equals(n.UIDTarget))
);
for (const link of toDelete) {
await query(`DELETE FROM Links WHERE UID=? AND UIDTarget=? AND Type='identifyer'`, [link.UID, link.UIDTarget]);
}
for (const link of toAdd) {
await query(`INSERT IGNORE INTO Links (UID, Type, UIDTarget) VALUES (?, 'identifyer', ?)`, [link.UID, link.UIDTarget]);
}
if(toAdd.length>0) {
// add self visbility for all persons in the orga (INSERT IGNORE is a no-op for existing rows)
await query(
`INSERT IGNORE INTO Visible (UID,Type,UIDUser) VALUES(?,'changeable',?)`,
toAdd.map(link=>([link.UID,link.UID])),
{batch: true}
)
}
return { success: true };
};
/**
* Remove identifyer link for one person.
* @param {Buffer} UIDperson
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean}>}
*/
export const deleteUserByPerson = async (UIDperson, UIDroot) => {
await query(
`DELETE Links FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UIDTarget AND ObjectBase.Type IN ('person','extern'))
INNER JOIN Links AS MLink ON (MLink.UID=Links.UIDTarget AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Links.UIDTarget=? AND Links.Type='identifyer'`,
[UIDroot, UIDperson]
);
return { success: true };
};
/**
* Remove identifyer links for selected users.
* @param {Buffer[]} users
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean}>}
*/
export const deleteUsers = async (users, UIDroot) => {
await query(
`DELETE Links FROM Links
INNER JOIN ObjectBase AS Item ON (Item.UID=Links.UIDTarget AND Links.Type='identifyer' AND Item.Type IN ('person','extern'))
INNER JOIN Links AS MLink ON (MLink.UID=Links.UIDTarget AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Links.UID IN (
SELECT UID FROM ObjectBase WHERE ObjectBase.UIDBelongsTo IN (?) AND ObjectBase.Type IN ('person','extern')
)`,
[UIDroot, users]
);
return { success: true };
};
/**
* Invalidate user cache for one/multiple/all users.
* @param {any} body
* @param {string} orgaUID
* @returns {Promise<{success: boolean, invalidated?: number, message?: string}>}
*/
export const invalidateUserCacheService = async (body, orgaUID) => {
let invalidated = 0;
if (body.user) {
await invalidateUserCache(body.user, orgaUID);
invalidated = 1;
} else if (body.users && Array.isArray(body.users)) {
for (const userUID of body.users) {
await invalidateUserCache(userUID, orgaUID);
invalidated += 1;
}
} else if (body.all === true) {
await invalidateUserCache('*', orgaUID);
invalidated = -1;
} else {
return {
success: false,
message: 'Body must contain { user: "UUID-..." } or { users: [...] } or { all: true }'
};
}
return { success: true, invalidated };
};
/**
* Search users for UI selector.
* @param {string} search
* @param {Buffer} UIDroot
* @returns {Promise<any[]>}
*/
export const getSearchData = async (search, UIDroot) => {
return query(
`SELECT
CONCAT(ObjectBase.Title ,' ',Member.Display) AS title,
ObjectBase.UID AS value, ObjectBase.UID AS myKey, ObjectBase.UIDBelongsTo, ObjectBase.Type AS type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ULink.UID AS UIDuser
FROM
ObjectBase AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN (Links AS MLink INNER JOIN Links AS ULink ON (MLink.UID=ULink.UIDTarget AND ULink.Type='identifyer'))
ON (Member.UID=MLink.UID AND MLink.Type IN ('member','memberA','memberSys'))
WHERE
MATCH(Member.FullTextIndex) AGAINST (? IN BOOLEAN MODE)
AND ObjectBase.Type IN ('person','guest','extern')
AND MLink.UIDTarget=?
GROUP BY ObjectBase.UIDBelongsTo
ORDER BY hierarchie ASC`,
[search, UIDroot],
{ log: false, cast: ['UUID'] }
);
};
/**
* Get identifyer UID for a person.
* @param {Buffer} UIDperson
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, result?: any, message?: string}>}
*/
export const getPersonIdentifyer = async (UIDperson, UIDroot) => {
const links = await query(
`SELECT Links.UID FROM Links
INNER JOIN Links AS MLink ON (MLink.UID=Links.UIDTarget AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Links.UIDTarget=? AND Links.Type='identifyer'`,
[UIDroot, UIDperson],
{ cast: ['UUID'] }
);
if (!links.length) {
return { success: false, message: 'no identifyer found for person' };
}
return { success: true, result: links[0].UID };
};
/**
* Get user object by identifyer UID in current orga.
* @param {Buffer} UIDuser
* @param {string} rootUID
* @returns {Promise<{success: boolean, result?: any, message?: string}>}
*/
export const getUserByUID = async (UIDuser, rootUID) => {
const users = await query(
`SELECT
Links.UIDTarget AS UID,
MUser.Data,
MUser.Display,
OBase.Type AS Type,
OBase.Title AS Title,
MLink.UIDTarget AS UIDorga,
GMember.UID AS UIDgroup,
GMember.Display AS DisplayGroup,
GBase.Title AS TitleGroup,
GMember.Data AS DataGroup,
Links.UID AS UIDidentifyer
FROM Links
INNER JOIN Links AS MLink ON (Links.UIDTarget=MLink.UID AND MLink.Type IN ('member','memberA','memberSys') AND MLink.UIDTarget=U_UUID2BIN(?))
INNER JOIN Member AS MUser ON (MLink.UID=MUser.UID)
INNER JOIN ObjectBase AS OBase ON (OBase.UIDBelongsTo=MUser.UID AND OBase.Type IN ('person','guest','extern','job'))
INNER JOIN Links AS GLink ON (GLink.UID=OBase.UID AND GLink.Type='memberA')
INNER JOIN ObjectBase AS GBase ON (GBase.UID=GLink.UIDTarget AND GBase.Type='group')
INNER JOIN Member AS GMember ON (GMember.UID=GBase.UIDBelongsTo)
WHERE Links.UID=? AND Links.Type='identifyer'`,
[rootUID, UIDuser],
{ cast: ['json', 'UUID'], log: false }
);
if (!users.length) {
return { success: false, message: 'user not found in this orga' };
}
return { success: true, result: users[0] };
};
/**
* Get all identifyer links for the current organisation.
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, result: any[]}>}
*/
export const getAllIdentifyers = async (UIDroot) => {
const links = await query(
`SELECT Links.UID AS UIDidentifyer, Links.UIDTarget AS UIDuser
FROM Links
INNER JOIN Links AS MLink ON (MLink.UID=Links.UIDTarget AND MLink.Type IN ('member','memberA') AND MLink.UIDTarget=?)
WHERE Links.Type='identifyer'`,
[UIDroot],
{ cast: ['UUID'] }
);
return { success: true, result: links };
};
/**
* Create or refresh a portal pending link and token payload.
* @param {Buffer} UIDperson
* @param {Buffer} UIDkc
* @param {{tokenHash?: string, expiry?: string|number, lastSent?: string|number, sentCount?: number}} payload
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, message?: string}>}
*/
export const usertPortalPending = async (UIDperson, UIDkc, payload, UIDroot) => {
const orgCheck = await query(
`SELECT 1 FROM Links WHERE UID=? AND Type IN ('member','memberA') AND UIDTarget=? LIMIT 1`,
[UIDperson, UIDroot]
);
if (!orgCheck.length) {
return { success: false, message: 'person not found in this organisation' };
}
const { tokenHash, expiry, lastSent, sentCount } = payload || {};
// Links is system-versioned: only delete the existing link when the KC UID
// is actually changing; INSERT IGNORE is a no-op if the same row is already present.
await query(
`DELETE FROM Links WHERE UIDTarget=? AND Type='portal_pending' AND UID != ?`,
[UIDperson, UIDkc]
);
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?, 'portal_pending', ?)`,
[UIDkc, UIDperson]
);
// Member.Data always needs updating (token/expiry may change even when KC UID is unchanged).
await query(
`UPDATE Member SET Data=JSON_SET(Data,
'$._portalToken', ?,
'$._portalExpiry', ?,
'$._portalLastSent', ?,
'$._portalSentCount', ?
) WHERE UID=?`,
[tokenHash, expiry, lastSent ?? null, sentCount ?? 1, UIDperson]
);
return { success: true };
};
/**
* Get pending portal link details by KC UID and root orga.
* @param {Buffer} UIDkc
* @param {string} rootUID
* @returns {Promise<{success: boolean, result?: any, message?: string}>}
*/
export const getPortalPendingByKcUID = async (UIDkc, rootUID) => {
const rows = await query(
`SELECT Links.UIDTarget AS UID, MUser.Data, MUser.Display, MLink.UIDTarget AS UIDorga
FROM Links
INNER JOIN Links AS MLink
ON (Links.UIDTarget = MLink.UID AND MLink.Type IN ('member','memberA','memberSys') AND MLink.UIDTarget=U_UUID2BIN(?))
INNER JOIN Member AS MUser ON (MLink.UID = MUser.UID)
WHERE Links.UID = ? AND Links.Type = 'portal_pending'`,
[rootUID, UIDkc],
{ cast: ['json', 'UUID'], log: false }
);
if (!rows.length) {
return { success: false, message: 'No pending link found for this organization' };
}
return { success: true, result: rows[0] };
};
/**
* Delete pending portal link and token payload.
* @param {Buffer} UIDperson
* @param {Buffer} UIDroot
* @returns {Promise<{success: boolean, message?: string}>}
*/
export const deletePortalPending = async (UIDperson, UIDroot) => {
const orgCheck = await query(
`SELECT 1 FROM Links WHERE UID=? AND Type IN ('member','memberA') AND UIDTarget=? LIMIT 1`,
[UIDperson, UIDroot]
);
if (!orgCheck.length) {
return { success: false, message: 'person not found in this organisation' };
}
await transaction(async (connection) => {
await query(
`DELETE FROM Links WHERE UIDTarget=? AND Type='portal_pending'`,
[UIDperson],
{ connection }
);
await query(
`UPDATE Member SET Data=JSON_REMOVE(Data,
'$._portalToken', '$._portalExpiry',
'$._portalLastSent', '$._portalSentCount'
) WHERE UID=?`,
[UIDperson],
{ connection }
);
});
return { success: true };
};