// @ts-check
/**
* List Sync Service - Business Logic for Change Tracking
*
* Provides temporal query logic for tracking additions and removals
* from both static and dynamic lists since a given timestamp.
*/
import { query, pool, UUID2hex } from '@commtool/sql-query';
import { isAdmin } from '../../utils/authChecks.js';
import { errorLoggerRead } from '../../utils/requestLogger.js';
import mysqlTime from '../../utils/mysqlTime.js';
/**
* Build the Visible JOIN fragment based on the current user's permission level.
* Admins get no filter; changeable/admin visibility users see all entries;
* read-only users only see entries where they have changeable rights.
*
* @param {Buffer} UID - List UID (hex buffer)
* @param {object} session - Express session
* @returns {Promise<string>} SQL fragment to inject into the FROM clause
*/
export const getVisibleFilter = async (UID, session) => {
const UIDuser = UUID2hex(session.user);
if (await isAdmin(session))
return '';
const visibility = await query(
`SELECT Type FROM Visible WHERE UID=? AND UIDUser=?`,
[UID, UIDuser]
);
if (!visibility || !visibility[0])
return `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=${pool.escape(UIDuser)} AND Visible.Type='changeable')`;
if (visibility[0].Type === 'changeable' || visibility[0].Type === 'admin')
return `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=${pool.escape(UIDuser)})`;
return `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=${pool.escape(UIDuser)} AND Visible.Type='changeable')`;
};
/**
* Retrieve entries that have been added to a list since the given timestamp.
* Works for both static and dynamic lists.
*
* The result is deduplicated and entries with `member0` link type are excluded.
* Where a member appears multiple times (multiple links), their Data objects are merged.
*
* @param {Buffer} UID - List UID (hex buffer)
* @param {string} timestamp - Unix timestamp string
* @param {object} session - Express session
* @returns {Promise<object[]>} Array of added member entries
*/
export const getAddedListing = async (UID, timestamp, session) => {
try {
const visibleFilter = await getVisibleFilter(UID, session);
const result = await query(
`SELECT
ObjectBase.UID, MainBase.Type, ObjectBase.UIDBelongsTo, MainBase.Title , Member.Display ,
MainBase.SortName AS SortBase , Member.SortName, pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.hierarchie, Main.stage,
Main.gender, Main.dindex, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
Links.Type AS linkType, Member.Data AS Data
FROM
ObjectBase
INNER JOIN ObjectBase AS MainBase ON (MainBase.UID=ObjectBase.UIDBelongsTo)
INNER JOIN ObjectBase AS Main ON (Main.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID=Main.UID)
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
)
ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
LEFT JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(parseInt(timestamp) * 1000)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA') AND beforeLink.UIDTarget=?)
${visibleFilter}
WHERE Links.UIDTarget=? AND beforeLink.UID IS NULL
GROUP BY MainBase.UID
ORDER BY Member.SortName`,
[UID, UID],
{ cast: ['UUID', 'json'] }
);
return reduceResult(result);
} catch (e) {
errorLoggerRead(e);
}
};
/**
* Retrieve entries that have been removed from a list since the given timestamp.
* Works for both static and dynamic lists.
*
* The result is deduplicated and entries with `member0` link type are excluded.
* Where a member appears multiple times (multiple links), their Data objects are merged.
*
* @param {Buffer} UID - List UID (hex buffer)
* @param {string} timestamp - Unix timestamp string
* @param {object} session - Express session
* @returns {Promise<object[]>} Array of removed member entries
*/
export const getRemovedListing = async (UID, timestamp, session) => {
try {
const visibleFilter = await getVisibleFilter(UID, session);
const result = await query(
`SELECT
ObjectBase.UID, MainBase.Type, ObjectBase.UIDBelongsTo, MainBase.Title , Member.Display ,
MainBase.SortName AS SortBase , Member.SortName, pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.hierarchie, Main.stage,
Main.gender, Main.dindex, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
Links.Type AS linkType, Member.Data AS Data
FROM
ObjectBase FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(parseInt(timestamp) * 1000)}'
INNER JOIN ObjectBase AS MainBase ON (MainBase.UID=ObjectBase.UIDBelongsTo)
INNER JOIN ObjectBase AS Main ON (Main.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID=Main.UID)
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
)
ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(parseInt(timestamp) * 1000)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA'))
LEFT JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA') AND Links.UIDTarget=?)
${visibleFilter}
WHERE Links.UIDTarget IS NULL AND beforeLink.UIDTarget=?
GROUP BY MainBase.UID
ORDER BY Member.SortName`,
[UID, UID],
{ cast: ['UUID', 'json'] }
);
return reduceResult(result);
} catch (e) {
errorLoggerRead(e);
}
};
/**
* Deduplicate a raw query result set:
* - Skip entries with linkType `member0`
* - Merge Data objects for duplicate UIDBelongsTo values
* - Omit entries entirely if a `member0` counterpart exists
*
* @param {object[]} result - Raw query result rows
* @returns {object[]} Deduplicated result
*/
const reduceResult = (result) => {
const reducedResult = [];
result.forEach((cur) => {
if (cur.linkType === 'member0') return;
const exist = reducedResult.find(el => el.UIDBelongsTo === cur.UIDBelongsTo);
if (exist) {
exist.Data = { ...exist.Data, ...cur.Data };
} else {
const exist0 = result.find(
el => el.UIDBelongsTo === cur.UIDBelongsTo && el.linkType === 'member0'
);
if (!exist0) reducedResult.push(cur);
}
}, []);
return reducedResult;
};