PronounsPage/server/api/admin/users.get.ts

import SQL from 'sql-template-strings';

import type { UserRow } from '~/server/express/user.ts';

export default defineEventHandler(async (event) => {
    const query = getQuery(event);
    const checkLocale = query.localeFilter ? global.config.locale : '*';

    const { isGranted } = await useAuthentication(event);
    if (!isGranted('users', checkLocale) && !isGranted('community', checkLocale)) {
        throw createError({
            status: 401,
            statusMessage: 'Unauthorised',
        });
    }

    const conditions = [];

    let sql = SQL`
        SELECT u.id, u.username, u.email, u.roles, u.avatarSource, group_concat(p.locale) AS profiles
        FROM users u
        LEFT JOIN profiles p ON p.userId = u.id
    `;

    if (typeof query.filter === 'string') {
        conditions.push(SQL`(lower(u.username) LIKE ${`%${query.filter.toLowerCase()}%`} OR lower(u.email) LIKE ${`%${query.filter.toLowerCase()}%`})`);
    }
    if (query.localeFilter) {
        conditions.push(SQL`p.locale=${global.config.locale}`);
    }
    if (query.adminsFilter) {
        conditions.push(SQL`u.roles != ''`);
    }

    let conditionsSql = SQL``;
    if (conditions.length) {
        let i = 0;
        for (const condition of conditions) {
            conditionsSql = conditionsSql.append(i++ ? SQL` AND ` : SQL` WHERE `).append(condition);
        }
    }

    sql = sql.append(conditionsSql).append(SQL`
        GROUP BY u.id
        ORDER BY u.id DESC
        LIMIT ${query.limit ? parseInt(query.limit as string) : 100}
        OFFSET ${query.offset ? parseInt(query.offset as string) : 0}
    `);

    const countSql = SQL`SELECT COUNT(*) AS c FROM (SELECT u.id FROM users u LEFT JOIN profiles p ON p.userId = u.id`.append(conditionsSql).append(' GROUP BY u.id)');

    const db = useDatabase();
    return {
        count: (await db.get<{ c: number }>(countSql))!.c,
        data: (await db.all<Pick<UserRow, 'id' | 'username' | 'email' | 'roles' | 'avatarSource'> & { profiles: string }>(sql)).map((u) => {
            return {
                ...u,
                profiles: u.profiles ? u.profiles.split(',') : [],
            };
        }),
    };
});