PronounsPage/server/express/admin.ts

import { Router } from 'express';
import SQL from 'sql-template-strings';
import { encodeTime, decodeTime, ulid } from 'ulid';

import allLocales from '../../locale/locales.ts';
import { buildDict, now, shuffle, handleErrorAsync, filterObjectKeys } from '../../src/helpers.ts';
import { auditLog, fetchAuditLog } from '../audit.ts';
import avatar from '../avatar.ts';
import { archiveBan, liftBan } from '../ban.ts';
import type { Database } from '../db.ts';
import mailer from '../mailer.ts';

import { profilesSnapshot } from './profile.ts';
import { loadCurrentUser } from './user.ts';
import type { UserRow } from './user.ts';

import type { StatRow } from '~/server/admin.ts';

interface BanProposalRow {
    id: string;
    userId: string;
    bannedBy: string;
    bannedTerms: string;
    bannedReason: string;
}

interface UserMessageRow {
    id: string;
    userId: string;
    adminId: string;
    message: string;
}

const router = Router();

const getAdminList = defineCachedFunction(async (db: Database) => {
    const admins = await db.all<any>(SQL`
            SELECT u.username, p.teamName, p.locale, u.id, u.email, u.avatarSource, p.credentials, p.credentialsLevel, p.credentialsName, a.payload
            FROM users u
                LEFT JOIN profiles p ON p.userId = u.id
                LEFT JOIN authenticators a ON u.id = a.userId AND a.type = u.avatarSource
            WHERE p.teamName IS NOT NULL
              AND p.teamName != ''
              AND (a.validUntil IS NULL OR a.validUntil > ${now()})
            GROUP BY u.username, p.locale
            ORDER BY RANDOM()
        `);

    const adminsGroupped: Record<string, any[]> = buildDict(function* () {
        yield [global.config.locale, []];
        for (const { code, published } of allLocales) {
            if (code !== global.config.locale && published) {
                yield [code, []];
            }
        }
        yield ['', []];
    });
    for (const admin of admins) {
        admin.avatar = await avatar(db, admin);
        delete admin.id;
        delete admin.email;
        delete admin.payload;
        if (admin.credentials) {
            admin.credentials = admin.credentials.split('|');
        }

        if (adminsGroupped[admin.locale] !== undefined) {
            adminsGroupped[admin.locale].push(admin);
        } else {
            adminsGroupped[''].push(admin);
        }
    }

    return adminsGroupped;
}, {
    name: 'admin-list',
    getKey: () => 'default',
    maxAge: 24 * 60 * 60,
});

router.get('/admin/list', handleErrorAsync(async (req, res) => {
    return res.json(await getAdminList(req.db));
}));

const getAdminsFooter = defineCachedFunction(async (db: Database) => {
    const fromDb = await db.all(SQL`
            SELECT u.username, p.footerName, p.footerAreas, p.locale
            FROM users u
            LEFT JOIN profiles p ON p.userId = u.id
            WHERE p.locale = ${global.config.locale}
              AND p.footerName IS NOT NULL AND p.footerName != ''
              AND p.footerAreas IS NOT NULL AND p.footerAreas != ''
        `);

    const fromConfig = global.config.contact.authors || [];

    return [...fromDb, ...fromConfig];
}, {
    name: 'admin-footer',
    getKey: () => 'default',
    maxAge: 24 * 60 * 60,
});

router.get('/admin/list/footer', handleErrorAsync(async (req, res) => {
    return res.json(shuffle(await getAdminsFooter(req.db)));
}));

router.get('/admin/users', handleErrorAsync(async (req, res) => {
    const checkLocale = req.query.localeFilter ? global.config.locale : '*';
    if (!req.isGranted('users', checkLocale) && !req.isGranted('community', checkLocale)) {
        return res.status(401).json({ error: '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 req.query.filter === 'string') {
        conditions.push(SQL`(lower(u.username) LIKE ${`%${req.query.filter.toLowerCase()}%`} OR lower(u.email) LIKE ${`%${req.query.filter.toLowerCase()}%`})`);
    }
    if (req.query.localeFilter) {
        conditions.push(SQL`p.locale=${global.config.locale}`);
    }
    if (req.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 ${req.query.limit ? parseInt(req.query.limit as string) : 100}
        OFFSET ${req.query.offset ? parseInt(req.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)');

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

router.get('/admin/stats/users-chart/:locale', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const formatDate = (d: Date) => `${d.getFullYear()}-${(d.getMonth() + 1).toString().padStart(2, '0')}-${d.getDate().toString()
        .padStart(2, '0')}`;

    const stats: Record<string, number> = {};
    await req.db.each<Pick<StatRow, 'id' | 'users'>>(
        SQL`SELECT id, users FROM stats WHERE locale = ${req.params.locale} ORDER BY id ASC`,
        (_err, { id, users }) => {
            const date = formatDate(new Date(decodeTime(id)));
            stats[date] = users; // overwrite with the latest one for the day
        },
    );

    const incrementsChart: Record<string, number> = {};
    let prevUsers = null;
    for (const [date, users] of Object.entries(stats)) {
        incrementsChart[date] = prevUsers === null
            ? users
            : users - prevUsers;

        prevUsers = users;
    }

    return res.json(incrementsChart);
}));

const normalise = (s: string): string => s.trim().toLowerCase();

const fetchUserByUsername = async (db: Database, username: string) => {
    return await db.get<Pick<UserRow, 'id' | 'email'>>(SQL`SELECT id, email FROM users WHERE usernameNorm = ${normalise(username)}`);
};

const fetchBanProposals = async (db: Database, userId: string) => {
    return await db.all(SQL`
        SELECT p.*, a.username AS bannedByUsername
        FROM ban_proposals p
            LEFT JOIN users a ON p.bannedBy = a.id
        WHERE userId = ${userId}
    `);
};

router.get('/admin/ban-snapshot/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const row = await req.db.get<{ banSnapshot: string }>(SQL`
        SELECT banSnapshot
        FROM users
        WHERE users.id = ${req.params.id}
    `);

    return res.json(row ? row.banSnapshot : null);
}));

router.get('/admin/ban-proposals', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const cutoff = encodeTime(Date.now() - 3 * 31 * 24 * 60 * 60 * 1000, 10) + '0'.repeat(16);

    return res.json(await req.db.all(SQL`
        SELECT u.username, group_concat(p.locale) as profiles, count(bp.id) / count(p.locale) as votes
        FROM ban_proposals bp
            LEFT JOIN users u ON bp.userId = u.id
            LEFT JOIN profiles p on u.id = p.userId
        WHERE bp.id > ${cutoff}
            AND u.bannedBy IS NULL
        GROUP BY u.username
    `));
}));

router.get('/admin/ban-proposals/:username', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);

    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    return res.json(await fetchBanProposals(req.db, user.id));
}));

router.post('/admin/propose-ban/:username', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);
    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    if (req.body.reason) {
        if (!req.body.terms.length) {
            return res.status(400).json({ error: 'Terms are required' });
        }
        await req.db.get(SQL`
            DELETE FROM ban_proposals
            WHERE userId = ${user.id} AND bannedBy = ${req.user!.id}
        `);
        await req.db.get(SQL`
            INSERT INTO ban_proposals (id, userId, bannedBy, bannedTerms, bannedReason) VALUES (
                ${ulid()}, ${user.id},
                ${req.user!.id}, ${req.body.terms.join(',')}, ${req.body.reason}
            )`);
        await auditLog(req, 'mod/proposed_ban', {
            userId: user.id,
            terms: req.body.terms,
            reason: req.body.reason,
        });
    } else {
        await req.db.get(SQL`
            DELETE FROM ban_proposals
            WHERE userId = ${user.id} AND bannedBy = ${req.user!.id}
        `);
        await auditLog(req, 'mod/cancelled_ban_proposal', {
            userId: user.id,
        });
    }

    return res.json(true);
}));

router.post('/admin/apply-ban/:username/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);
    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    const proposals = await fetchBanProposals(req.db, user.id);

    if (req.params.id && req.params.id !== '0') {
        if (!req.isGranted('*') && proposals.length < 2) {
            return res.status(401).json({ error: 'Unauthorised' });
        }
        const proposal = await req.db.get<BanProposalRow>(SQL`SELECT * FROM ban_proposals WHERE id = ${req.params.id}`);
        if (!proposal || proposal.userId !== user.id) {
            return res.status(400).json({ error: 'Invalid ban proposal id' });
        }
        await req.db.get(SQL`
            UPDATE users
            SET bannedReason = ${proposal.bannedReason},
                bannedTerms = ${proposal.bannedTerms},
                bannedBy = ${req.user!.id},
                banSnapshot = ${await profilesSnapshot(req.db, normalise(req.params.username))}
            WHERE id = ${user.id}
        `);
        await archiveBan(req.db, user);
        mailer(user.email, 'ban', { reason: proposal.bannedReason, username: normalise(req.params.username) });
        await auditLog(req, 'mod/banned', {
            userId: user.id,
            terms: proposal.bannedTerms,
            reason: proposal.bannedReason,
        });
    } else {
        await req.db.get(SQL`
            UPDATE users
            SET bannedReason = null,
               bannedBy = ${req.user!.id}
            WHERE id = ${user.id}
        `);
        await auditLog(req, 'mod/unbanned', {
            userId: user.id,
        });
        await liftBan(req.db, user);
    }

    await req.db.get(SQL`
        UPDATE reports
        SET isHandled = 1
        WHERE userId = ${user.id}
    `);

    return res.json(true);
}));

router.get('/admin/reports', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const cutoff = encodeTime(Date.now() - 3 * 31 * 24 * 60 * 60 * 1000, 10) + '0'.repeat(16);

    return res.json(await req.db.all(SQL`
        SELECT reports.id, group_concat(p.locale) as profiles, sus.username AS susUsername, reporter.username AS reporterUsername, reports.comment, reports.isAutomatic, reports.isHandled
        FROM reports
            LEFT JOIN users sus ON reports.userId = sus.id
            LEFT JOIN users reporter ON reports.reporterId = reporter.id
            LEFT JOIN profiles p on sus.id = p.userId
        WHERE reports.id > ${cutoff}
          AND sus.username IS NOT NULL
        GROUP BY reports.id
        ORDER BY min(reports.isHandled) ASC, min(reports.isAutomatic) ASC, reports.id ASC
    `));
}));

router.get('/admin/reports/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    return res.json(await req.db.all(SQL`
        SELECT reports.id, sus.username AS susUsername, reporter.username AS reporterUsername, reports.comment, reports.isAutomatic, reports.isHandled, reports.snapshot
        FROM reports
        LEFT JOIN users sus ON reports.userId = sus.id
        LEFT JOIN users reporter ON reports.reporterId = reporter.id
        WHERE reports.userId = ${req.params.id}
        ORDER BY reports.isHandled ASC, reports.id DESC
    `));
}));

router.post('/admin/reports/handle/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    await req.db.get(SQL`
        UPDATE reports
        SET isHandled = 1
        WHERE id=${req.params.id}
    `);

    await auditLog(req, 'mod/handled_report', {
        id: req.params.id,
    });

    return res.json(true);
}));

const fetchModMessages = async (db: Database, user: Pick<UserRow, 'id'>) => {
    return db.all<Pick<UserMessageRow, 'id' | 'message'> & { adminUsername: UserRow['username'] }>(SQL`
        SELECT m.id, a.username as adminUsername, m.message
        FROM user_messages m
            LEFT JOIN users a ON m.adminId = a.id
        WHERE m.userId = ${user.id}
    `);
};

router.post('/admin/mod-message/:username', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    if (!req.body.message) {
        return res.status(400).json({ error: 'Bad request' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);

    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    await req.db.get(SQL`INSERT INTO user_messages (id, userId, adminId, message) VALUES (
        ${ulid()},
        ${user.id},
        ${req.user!.id},
        ${req.body.message}
    )`);

    mailer(user.email, 'modMessage', {
        message: req.body.message,
        username: req.params.username,
        modUsername: req.user!.username,
    });

    await auditLog(req, 'mod/sent_mod_message', {
        userId: user.id,
        message: req.body.message,
    });

    return res.json(await fetchModMessages(req.db, user));
}));

router.get('/admin/mod-messages/:username', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);

    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    return res.json(await fetchModMessages(req.db, user));
}));

router.post('/admin/overwrite-sensitive/:username', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    if (req.body.sensitive === undefined || !Array.isArray(req.body.sensitive)) {
        return res.status(400).json({ error: 'Bad request' });
    }

    const user = await fetchUserByUsername(req.db, req.params.username);

    if (!user) {
        return res.status(400).json({ error: 'No such user' });
    }

    await req.db.get(SQL`UPDATE profiles SET sensitive = ${JSON.stringify(req.body.sensitive)} WHERE userId=${user.id} AND locale=${global.config.locale}`);

    if (req.body.sensitive.length) {
        mailer(user.email, 'sensitiveApplied', {
            warnings: req.body.sensitive.join('; '),
            username: req.params.username,
            modUsername: req.user!.username,
        });
    }

    await auditLog(req, 'mod/overwrote_content_warnings', {
        userId: user.id,
        warnings: req.body.sensitive,
    });

    return res.json(req.body.sensitive);
}));

router.post('/admin/set-notification-frequency', handleErrorAsync(async (req, res) => {
    if (!req.isGranted()) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    if (![0, 1, 7].includes(req.body.frequency)) {
        return res.status(400).json({ error: 'Bad request' });
    }

    await req.db.get(SQL`UPDATE users SET adminNotifications = ${req.body.frequency} WHERE id = ${req.user!.id}`);

    await auditLog(req, 'team/changed_notification_frequency', {
        frequency: req.body.frequency,
    });

    return await loadCurrentUser(req, res);
}));

router.get('/admin/timesheet', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('panel')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const ts = (await req.db.get<{ timesheets: string }>(SQL`SELECT timesheets FROM users WHERE id = ${req.user!.id}`))!.timesheets;

    return res.json(ts ? JSON.parse(ts) : null);
}));

router.post('/admin/timesheet', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('panel')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    await req.db.get(SQL`UPDATE users SET timesheets = ${JSON.stringify(req.body.timesheets)} WHERE id = ${req.user!.id}`);

    await auditLog(req, 'team/updated_timesheets', {
        timesheets: req.body.timesheets,
    });

    return res.json('OK');
}));

router.get('/admin/timesheets', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('panel')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const timesheetsByUsername: Record<string, string> = {};
    for (let { username, timesheets } of await req.db.all<{ username: string; timesheets: any }>(SQL`SELECT username, timesheets FROM users WHERE timesheets IS NOT NULL`)) {
        timesheets = JSON.parse(timesheets);
        if (!req.isGranted('org')) {
            delete timesheets.details;
        }
        timesheetsByUsername[username] = timesheets;
    }

    return res.json(timesheetsByUsername);
}));

router.get('/admin/audit-log/:username/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('*')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    return res.json(await fetchAuditLog(req.params.username, req.params.id, req.params.id));
}));

router.get('/admin/authenticators/:id', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('community') && !req.isGranted('*')) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const authenticators = (await req.db.all<any>(SQL`
        SELECT * FROM authenticators
        WHERE userId = ${req.params.id}
        ORDER BY id DESC
    `)).map((auth) => {
        delete auth.userId;

        const payload = JSON.parse(auth.payload);
        auth.payload = typeof payload === 'string'
            ? null
            : filterObjectKeys(payload, ['id', 'email', 'name', 'instance', 'username']);

        return auth;
    });

    return res.json(authenticators);
}));

export default router;