Source: Router/collections/service.js

// @ts-check
/**
 * @typedef {Object} CollectionsListingInput
 * @property {any} session
 * @property {Record<string, any>} query
 * @property {any} body
 * @property {Record<string, any>} params
 */

import { UUID2hex, HEX2uuid, pool, query } from '@commtool/sql-query';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js';
import { matchObject } from '@commtool/object-filter';

const sqlPool = /** @type {any} */ (pool);

/**
 * Get collections listing based on filter/query input.
 * @param {CollectionsListingInput} input
 * @param {string} [fields='default']
 * @returns {Promise<any[]>}
 */
export const getCollectionsListing = async (input, fields = 'default') => {
    let visibleFilter;
  
    visibleFilter = `INNER JOIN Visible ON (Visible.UID = Main.UID AND Visible.UIDUser=U_UUID2BIN('${HEX2uuid(input.session.user)}'))`;
  
    const timestamp = parseTimestampToSeconds(input.query.timestamp ? String(input.query.timestamp) : undefined);
    const asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';

    let where;
    let result;
    let type;
    let filter = '';
    let dataFields = '';
    let orderBy = ' Member.SortName ';

    if (input.body && Array.isArray(input.body)) {
        where = ` Links.UIDTarget IN (${input.body.map(UID => `U_UUID2BIN(${sqlPool.escape(UID)})`).join(',')}) `;
    } else if (input.params.UID) {
        where = ` Links.UIDTarget = U_UUID2BIN(${sqlPool.escape(input.params.UID)}) `;
    } else {
        where = '1';
    }

    try {
        type = input.query.type ? JSON.parse(String(input.query.type)) : type;
    } catch (e) {
        type = [input.query.type];
    }

    if (!type) {
        type = ['group'];
    }

    type = type.filter(/** @param {string} el */(el) => (['group', 'ggroup', 'list', 'dlist', 'email', 'event', 'eventGroup'].includes(el)));
    if (type.length === 0) {
        return [];
    }

    if (input.query.hierarchie) {
        filter += ` AND Main.hierarchie=${input.query.hierarchie}`;
    }
    if (input.query.stage) {
        filter += ` AND Main.stage=${input.query.stage}`;
    }
    if (input.query.gender) {
        filter += ` AND Main.gender='${input.query.gender}'`;
    }

    if (input.query.Data && input.query.Data !== 'full' && input.query.Data !== 'all') {
        let fieldsToSelect = [];
        try {
            fieldsToSelect = input.query.Data ? JSON.parse(String(input.query.Data)) : null;
        } catch (e) {
            fieldsToSelect[0] = [input.query.Data];
        }
        for (const field of fieldsToSelect) {
            dataFields += `,JSON_VALUE(Member.Data,${sqlPool.escape(field.path)}) AS ${sqlPool.escape(field.alias)}`;
        }
    }

    if (input.query.dataFilter || input.query.Data === 'full' || input.query.Data === 'all') {
        dataFields += ',Member.Data AS Data';
    }

    if (input.query.orderBy === 'time') {
        orderBy = ' Main.validFrom DESC';
    }

    if (input.query.siblings) {
        result = await query(`
            SELECT
                Main.UID,  Main.Type, Main.UIDBelongsTo, Main.Title , Member.Display , Member.SortName , pgroup.UID AS UIDgroup , 
                CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.hierarchie, Main.stage, Main.gender
                ${dataFields}
            FROM
                ObjectBase ${asOf} AS Main
                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 =Main.UID AND GLink.Type ='memberA')
                INNER JOIN Links AS SLinks ON (SLinks.UID=Main.UID)
                INNER JOIN ObjectBase ${asOf} AS Sisters ON (Sisters.UID=SLinks.UIDTarget)
                INNER JOIN Visible ON (Visible.UID=Main.UID)
            WHERE
                Sisters.UID=?  ${filter}  AND Main.Type IN (?) AND Visible.UIDUser=?
            GROUP BY
                Main.UID
            ORDER BY
                Main.SortName
            `, [UUID2hex(input.params.UID), type, UUID2hex(input.session.user)],
        {
            cast: ['UUID', 'json'],
            filter: input.query.dataFilter
                ? (/** @type {any} */ row) => (
                    matchObject(row, JSON.parse(String(input.query.dataFilter)), timestamp)
                )
                : null,
        });
    } else {
        result = await query(`
            SELECT
                Main.UID,  Main.Type, Main.UIDBelongsTo, Main.Title , Member.Display , Member.SortName , pgroup.UID AS UIDgroup , SLink.UIDTarget AS UIDsister,
                CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.hierarchie, Main.stage, Main.gender
                ${dataFields}
            FROM
                ObjectBase ${asOf} Main
                INNER JOIN Member ON (Member.UID=Main.UID)
                    LEFT JOIN (ObjectBase ${asOf} AS pgroup
                        INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
                        INNER JOIN Links ${asOf} AS GLink
                            ON (GLink.UIDTarget = pgroup.UID )
                        )
                        ON (GLink.UID =Main.UID AND GLink.Type ='memberA')
                    LEFT JOIN Links ${asOf} AS SLink
                                ON (SLink.UID=Main.UID AND SLink.Type='memberS' )
                    INNER JOIN Links ${asOf} ON ( Links.UID=Main.UID AND Links.Type IN('member','memberA','memberS'))
                    ${visibleFilter}
            WHERE
            ${where} ${filter} AND Main.Type IN (?)
            GROUP BY
                Main.UID
            ORDER BY
                ${orderBy}
            `, [type], {
            cast: ['UUID', 'json'],
            filter: input.query.dataFilter
                ? (/** @type {any} */ row) => (
                    matchObject(row, JSON.parse(String(input.query.dataFilter)), timestamp)
                )
                : null,
            log: false,
        });
    }

    return result;
};

/**
 * Get membership counts for collections.
 * @param {Pick<CollectionsListingInput, 'query'|'body'>} input
 * @returns {Promise<any[]>}
 */
export const getCollectionCounts = async (input) => {
    const timestamp = parseTimestampToSeconds(input.query.timestamp ? String(input.query.timestamp) : undefined);
    const asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
    const collections = input.body.map(/** @param {string} el */(el) => UUID2hex(el));

    let counts = [];
    if (collections.length > 0) {
        counts = await query(`SELECT COUNT(Links.UID) AS count,Links.UIDTarget AS UID
                                    FROM Links ${asOf}
                                    INNER JOIN ObjectBase ${asOf} AS PObjects
                                    ON (PObjects.UID=Links.UID AND PObjects.Type='person')
                                WHERE Links.Type IN ('member','memberA') AND Links.UIDTarget  IN (?)
                                GROUP BY Links.UIDTarget`, [collections], { cast: ['UUID'] });
    }

    return counts;
};