// @ts-check
/**
* Person Service — Pure Business Logic
*
* DB operations and data transformations for person-specific flows.
* No HTTP concerns (no req/res). Session values are passed as explicit
* parameters; `req` is forwarded only where the shared personHelpers
* functions still require it internally.
*/
import { query, transaction, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { phonetikArray } from '../../utils/compileTemplates.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { familyAddress } from '../../tree/familyAddress.js';
import { addToTree } from './migratePerson.js';
import {
publishGroupEvent,
handleGroupMembershipMigration,
updatePersonData,
rebuildMemberAccess,
} from './personHelpers.js';
// Re-export so callers of personService have a single import point
export { fetchGroup, fetchMemberExists as fetchPersonExists, rebuildMemberAccess as rebuildPersonAccess, fetchLatestObjectValidFrom } from './personHelpers.js';
// ---------------------------------------------------------------------------
// Mutation helpers
// ---------------------------------------------------------------------------
/**
* Insert a brand-new person into ObjectBase, Member, Links, Visible,
* create the initial family record, and publish the join event.
*
* Note: session values are passed explicitly so this function has no HTTP dependency.
*
* @param {Object} object Rendered person object (from renderObject)
* @param {Buffer} belongsTo UIDBelongsTo (= UIDperson for new persons)
* @param {Buffer} UIDgroup Target group UID
* @param {string} sessionUser req.session.user
* @param {string} sessionRoot req.session.root
* @param {number|undefined} timestamp Unix-seconds backdate, or undefined
*/
export const createNewPerson = async (object, belongsTo, UIDgroup, sessionUser, sessionRoot, timestamp) => {
await transaction(async (connection) => {
await connection.query(
`INSERT INTO ObjectBase(UID, UIDuser, Type, UIDBelongsTo, Title, dindex, hierarchie, stage, gender)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[object.UID, UUID2hex(sessionUser), 'person', belongsTo, object.Title,
object.dindex, object.hierarchie, object.stage, object.gender]
);
await connection.query(
`INSERT INTO Member(UID, Display, SortName, FullTextIndex, PhonetikIndex, Data)
VALUES(?, ?, ?, ?, ?, ?)`,
[belongsTo, object.Display, object.SortIndex, object.FullTextIndex,
phonetikArray([object.Data.firstName, object.Data.lastName]),
JSON.stringify(object.Data)]
);
await connection.query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?, 'memberA', ?)`,
[object.UID, UIDgroup]
);
await connection.query(
`INSERT INTO Visible (UID, UIDUser, Type) VALUES(?, ?, 'changeable')`,
[object.UID, UUID2hex(sessionUser)]
);
}, { backDate: timestamp });
await addToTree(object.UID, UIDgroup, timestamp);
queueAdd(UUID2hex(sessionRoot), UUID2hex(sessionUser), 'person', object.UID, object.UID, null, UIDgroup, timestamp);
// Create the initial family record linked to this person
const [{ UID: UIDfamily }] = await query(`SELECT UIDV1() AS UID`, []);
await query(
`INSERT INTO Member (UID, Display, SortName, FullTextIndex, Data) VALUES(?, ?, '', '', ?)`,
[UIDfamily, 'family: ' + object.Display,
JSON.stringify({ feeAddress: { UID: HEX2uuid(object.UID), Display: object.Display, type: 'person' } })]
);
await query(
`INSERT IGNORE INTO Links (UID, Type, UIDTarget) VALUES(?, 'familyFees', ?)`,
[object.UID, UIDfamily]
);
queueAdd(UUID2hex(sessionRoot), UUID2hex(sessionUser), 'familyB', object.UID, object.UID, null, UIDfamily);
// Propagate family address if any contact data has type='family'
if (
(Array.isArray(object.Data.address) && object.Data.address.find(a => a.type === 'family')) ||
(Array.isArray(object.Data.email) && object.Data.email.find(a => a.type === 'family')) ||
(Array.isArray(object.Data.phone) && object.Data.phone.find(a => a.type === 'family')) ||
(Array.isArray(object.Data.accounts) && object.Data.accounts.find(a => ['family', 'familyFees'].includes(a.type)))
) {
familyAddress(/** @type {import('../../tree/familyAddress.js').FamilyMemberObject} */ (
{ ...object, Type: 'person' }
), sessionRoot);
}
await publishGroupEvent('new', object.UID, 'person', UIDgroup, sessionRoot, timestamp);
};
/**
* Update an existing person record, or migrate an extern record to type 'person'.
*
* Note: `req` is still forwarded because the shared personHelpers functions that
* handle DB updates (`updatePersonData`, `handleGroupMembershipMigration`) require
* session information from it internally.
*
* @param {Object} person Existing DB record (with MemberA, Type, etc.)
* @param {Object} object Rendered person object
* @param {Buffer} UIDperson
* @param {Buffer} UIDgroup
* @param {Object} req Express request object
* @param {number|undefined} timestamp
*/
export const updateExistingPerson = async (person, object, UIDperson, UIDgroup, req, timestamp) => {
object.Type = 'person';
await updatePersonData(UIDperson, object.Data, object, req, timestamp);
if (!person.MemberA || !person.MemberA.equals(UIDgroup) || person.Type === 'extern') {
await handleGroupMembershipMigration(person, UIDperson, UIDgroup, req, timestamp, 'person');
}
};
// ---------------------------------------------------------------------------
// Age-update helper
// ---------------------------------------------------------------------------
/**
* Recalculate `dindex` (numeric age) for every person in the organisation whose
* stored age differs from today's computed value.
*
* @param {Buffer} rootUID Binary UID of the organisation root object
* @param {string} sessionUser req.session.user
* @param {string} sessionRoot req.session.root
* @returns {Promise<number>} Number of persons whose age was updated
*/
export const ageUpdatePersons = async (rootUID, sessionUser, sessionRoot) => {
const sysResult = await query(
`SELECT ObjectBase.UID
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UID = Member.UID AND Links.Type = 'memberA')
WHERE ObjectBase.Type = 'person' AND Links.UIDTarget = ? AND ObjectBase.dindex <>
CAST(CONCAT(YEAR(CURDATE()) - YEAR(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d')),
IF(MONTH(CURDATE()) > MONTH(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))
OR (MONTH(CURDATE()) = MONTH(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))
AND DAY(CURDATE()) >= DAY(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))),
'',
'00')) AS SIGNED)
AND JSON_VALUE(Member.Data,'$.birthDate') IS NOT NULL
AND JSON_VALUE(Member.Data,'$.birthDate') <> ''`,
[rootUID],
{ cast: ['UUID'] }
);
for (const person of sysResult) {
await query(
`UPDATE ObjectBase
SET dindex = CAST(CONCAT(YEAR(CURDATE()) - YEAR(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d')),
IF(MONTH(CURDATE()) > MONTH(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))
OR (MONTH(CURDATE()) = MONTH(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))
AND DAY(CURDATE()) >= DAY(STR_TO_DATE(JSON_VALUE(Member.Data,'$.birthDate'),'%Y-%m-%d'))),
'',
'00')) AS SIGNED)
WHERE UID = ?`,
[person.UID]
);
queueAdd(UUID2hex(sessionRoot), UUID2hex(sessionUser), 'listMember', person.UID, person.UID, null, person.UID);
}
return sysResult.length;
};
/**
* Return the minimum allowed backdate timestamp (seconds) for adding a person/extern
* to a target group.
*
* addToTree inserts Links rows from the person to the target group AND all its
* ancestor groups (discovered via the group's own member/memberA links). If the
* person was previously a member in any of those same groups, historical link rows
* already exist in the system-versioned Links table. Backdating a new insertion
* before the latest existing validFrom of those overlapping links would create an
* impossible temporal ordering.
*
* The floor is therefore MAX(validFrom) across all historical Links rows where
* UID = personUID AND UIDTarget IN (targetGroup + all its ancestor groups).
*
* @param {Buffer} personUID Binary UID of the person or extern
* @param {Buffer} targetGroupUID Binary UID of the group they are being added to
* @returns {Promise<number>} Unix timestamp in seconds (0 if no prior links)
*/
export const getPersonMinBackdate = async (personUID, targetGroupUID) => {
const rows = await query(
`SELECT MAX(UNIX_TIMESTAMP(validFrom)) AS validFrom
FROM Links FOR SYSTEM_TIME ALL
WHERE UID = ? AND Type IN ('member', 'memberA')
AND UIDTarget IN (
SELECT UIDTarget FROM Links WHERE UID = ? AND Type IN ('member', 'memberA')
UNION SELECT ?
)`,
[personUID, targetGroupUID, targetGroupUID]
);
return rows[0]?.validFrom ?? 0;
};