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

import SQL from 'sql-template-strings';

import { PermissionAreas } from '#shared/helpers.ts';
import type { LocaleCode } from '#shared/helpers.ts';
import { getLocale } from '~~/server/data.ts';
import type { UserRow } from '~~/server/express/user.ts';

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

    const { multiIsGranted } = await useAuthentication(event);
    if (!multiIsGranted([PermissionAreas.Users, PermissionAreas.Community], checkLocale as LocaleCode)) {
        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=${locale}`);
    }
    if (query.adminsFilter) {
        conditions.push(SQL`(u.roles != '' AND u.roles != '*-external')`);
    }

    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(',') : [],
            };
        }),
    };
});