PronounsPage/server/routes/admin.ts

import { Router } from 'express';
import type { Request } from 'express';
import SQL from 'sql-template-strings';
import avatar from '../avatar.ts';
import { buildDict, now, shuffle, handleErrorAsync, filterObjectKeys } from '../../src/helpers.ts';
import type { LocaleDescription } from '../../locale/locales.ts';
import allLocales from '../../locale/locales.ts';
import fs from 'fs';
import { caches } from '../../src/cache.ts';
import mailer from '../../src/mailer.ts';
import { profilesSnapshot } from './profile.ts';
import { archiveBan, liftBan } from '../ban.ts';
import markdownit from 'markdown-it';
import { loadCurrentUser } from './user.ts';
import { encodeTime, decodeTime, ulid } from 'ulid';
import Suml from 'suml';
import buildLocaleList from '../../src/buildLocaleList.ts';
import auditLog from '../audit.ts';
import type { UserRow } from './user.ts';
import type { Database } from '../db.ts';
import type { Config } from '../../locale/config.ts';

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

interface StatRow {
    id: string;
    locale: string;
    users: number;
    data: string;
}

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

const router = Router();

router.get('/admin/list', handleErrorAsync(async (req, res) => {
    return res.json(await caches.admins.fetch(async () => {
        const admins = await req.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(req.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;
    }));
}));

router.get('/admin/list/footer', handleErrorAsync(async (req, res) => {
    return res.json(shuffle(await caches.adminsFooter.fetch(async () => {
        const fromDb = await req.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];
    })));
}));

router.get('/admin/users', handleErrorAsync(async (req, res) => {
    if (!req.isGranted('users') && !req.isGranted('community')) {
        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(',') : [],
            };
        }),
    });
}));

const fetchStats = async (req: Request): Promise<any> => {
    const maxId = (await req.db.get<{ maxId: StatRow['id'] | null }>('SELECT MAX(id) AS maxId FROM stats'))!.maxId;

    if (maxId == null) {
        return {
            _: {},
        };
    }

    const stats: any = {
        calculatedAt: decodeTime(maxId) / 1000,
    };

    for (const statsRow of await req.db.all<Pick<StatRow, 'locale' | 'users' | 'data'>>(SQL`SELECT locale, users, data FROM stats WHERE id = ${maxId}`)) {
        stats[statsRow.locale] = {
            users: statsRow.users,
            ...JSON.parse(statsRow.data),
        };
    }

    return stats;
};

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

    const stats = await fetchStats(req);

    for (const locale of Object.keys(stats)) {
        if (locale === '_' || locale === 'calculatedAt') {
            continue;
        }

        if (!req.isGranted('panel', locale)) {
            delete stats[locale];
        }
    }

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

interface Stats {
    calculatedAt: number;
    overall: LocaleStats;
    current: Partial<LocaleStats>;
}

interface LocaleStats {
    users: number;
    cards: number;
    pageViews?: number;
    visitors?: number;
    online?: number;
    visitDuration?: number;
    uptime?: number;
    responseTime?: number;
}

router.get('/admin/stats-public', handleErrorAsync(async (req, res) => {
    const statsAll = await fetchStats(req);

    const stats: Stats = {
        calculatedAt: statsAll.calculatedAt,
        overall: {
            users: statsAll._.users,
            cards: 0,
            pageViews: statsAll._.plausible?.pageviews || 0,
            visitors: statsAll._.plausible?.visitors || 0,
            online: statsAll._.plausible?.realTimeVisitors || 0,
        },
        current: {},
    };

    for (const [locale, localeStats] of Object.entries(statsAll) as any) {
        if (locale === '_' || locale === 'calculatedAt') {
            continue;
        }

        stats.overall.cards += localeStats.users;
        if (locale === global.config.locale) {
            stats.current = {
                cards: localeStats.users,
            };
        }
        if (localeStats.plausible) {
            stats.overall.pageViews += localeStats.plausible.pageviews;
            stats.overall.visitors += localeStats.plausible.visitors;
            stats.overall.online += localeStats.plausible.realTimeVisitors;
            if (locale === global.config.locale) {
                stats.current.pageViews = localeStats.plausible.pageviews;
                stats.current.visitors = localeStats.plausible.visitors;
                stats.current.online = localeStats.plausible.realTimeVisitors;
                stats.current.visitDuration = localeStats.plausible.visit_duration;
            }
        }
        if (localeStats.heartbeat && locale === global.config.locale) {
            stats.current.uptime = localeStats.heartbeat.uptime;
            stats.current.responseTime = localeStats.heartbeat.avgResponseTime;
        }
    }

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

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);
}));

type LocalDescriptionWithConfig = LocaleDescription & { config?: Config };

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

    const locales: Record<string, LocalDescriptionWithConfig> = buildLocaleList(global.config.locale, true);
    for (const locale in locales) {
        if (!locales.hasOwnProperty(locale)) {
            continue;
        }
        locales[locale].config = new Suml().parse(fs.readFileSync(`./locale/${locale}/config.suml`).toString()) as Config;
    }

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

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);
}));

const md = markdownit({ html: true });

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

    const dir = `${__dirname}/../../moderation`;

    return res.json({
        susRegexes: fs.readFileSync(`${dir}/sus.txt`).toString('utf-8')
            .split('\n')
            .filter((x) => !!x && !x.startsWith('#')),
        rulesUsers: md.render(fs.readFileSync(`${dir}/rules-users.md`).toString('utf-8')),
        rulesTerminology: md.render(fs.readFileSync(`${dir}/rules-terminology.md`).toString('utf-8')),
        rulesSources: md.render(fs.readFileSync(`${dir}/rules-sources.md`).toString('utf-8')),
        timesheets: md.render(fs.readFileSync(`${dir}/timesheets.md`).toString('utf-8')),
    });
}));

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 req.db.all(SQL`
        SELECT * FROM audit_log
        WHERE username = ${req.params.username} OR userId = ${req.params.id}
        ORDER BY id DESC
    `));
}));

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;