PronounsPage/server/express/terms.ts

import { Router } from 'express';
import type { Request } from 'express';
import SQL from 'sql-template-strings';
import { ulid } from 'ulid';

import { handleErrorAsync, sortClearedLinkedText, clearKey } from '../../src/helpers.ts';
import type { User } from '../../src/user.ts';
import { auditLog } from '../audit.ts';
import type { Database } from '../db.ts';

interface TermDb {
    id: string;
    term: string;
    original: string | null;
    definition: string;
    locale: string;
    approved: number;
    base_id: string;
    author_id: string | null;
    deleted: number;
    flags: string;
    category: string | null;
    images: string;
    key: string | null;
}

type TermWithUsername = TermDb & Pick<User, 'username'>;

const approve = async (db: Database, id: string) => {
    const { base_id } = (await db.get<Pick<TermDb, 'base_id'>>(SQL`SELECT base_id FROM terms WHERE id=${id}`))!;
    if (base_id) {
        await db.get(SQL`
            UPDATE terms
            SET deleted=1
            WHERE id = ${base_id}
        `);
    }
    await db.get(SQL`
        UPDATE terms
        SET approved = 1, base_id = NULL
        WHERE id = ${id}
    `);
    await invalidateCache('terms');
    await invalidateCache('search', 'term');
};

const linkOtherVersions = async (db: Database, isGranted: Request['isGranted'], terms: TermWithUsername[]) => {
    const keys = new Set(terms.filter((s) => !!s && s.key).map((s) => `'${clearKey(s.key)}'`));

    const otherVersions = await db.all<TermWithUsername & { key: string }>(SQL`
        SELECT t.*, u.username AS author FROM terms t
        LEFT JOIN users u ON t.author_id = u.id
        WHERE t.locale != ${global.config.locale}
        AND t.deleted = 0
        AND t.approved >= ${isGranted('terms') ? 0 : 1}
        AND t.key IN (`.append([...keys].join(',')).append(SQL`)
    `));

    const otherVersionsMap: Record<string, TermWithUsername[]> = {};
    otherVersions.forEach((version) => {
        if (otherVersionsMap[version.key] === undefined) {
            otherVersionsMap[version.key] = [];
        }
        otherVersionsMap[version.key].push(version);
    });

    return terms.map((t) => ({
        ...t,
        versions: t.key ? otherVersionsMap[t.key] || [] : [],
    }));
};

const router = Router();

export const getTermsEntries = defineCachedFunction(async (db: Database, isGranted: Request['isGranted']) => {
    return await linkOtherVersions(
        db,
        isGranted,
        sortClearedLinkedText(await db.all<TermWithUsername>(SQL`
                SELECT i.*, u.username AS author FROM terms i
                LEFT JOIN users u ON i.author_id = u.id
                WHERE i.locale = ${global.config.locale}
                AND i.approved >= ${isGranted('terms') ? 0 : 1}
                AND i.deleted = 0
            `), 'term'),
    );
}, {
    name: 'terms',
    getKey: () => 'default',
    shouldBypassCache: (db, isGranted) => isGranted('terms'),
    maxAge: 24 * 60 * 60,
});

router.get('/terms', handleErrorAsync(async (req, res) => {
    return res.json(await getTermsEntries(req.db, req.isGranted));
}));

router.get('/terms/search/:term', handleErrorAsync(async (req, res) => {
    const term = `%${req.params.term}%`;
    return res.json(await linkOtherVersions(
        req.db,
        req.isGranted,
        sortClearedLinkedText(await req.db.all<TermWithUsername>(SQL`
                SELECT i.*, u.username AS author FROM terms i
                LEFT JOIN users u ON i.author_id = u.id
                WHERE i.locale = ${global.config.locale}
                AND i.approved >= ${req.isGranted('terms') ? 0 : 1}
                AND i.deleted = 0
                AND (i.term like ${term} OR i.original like ${term})
            `), 'term'),
    ));
}));

router.post('/terms/submit', handleErrorAsync(async (req, res) => {
    if (!req.user || !await req.isUserAllowedToPost()) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const id = ulid();
    await req.db.get(SQL`
        INSERT INTO terms (id, term, original, key, definition, approved, base_id, locale, author_id, category, flags, images)
        VALUES (
            ${id},
            ${req.body.term.join('|')}, ${req.body.original.join('|')}, ${clearKey(req.body.key)}, ${req.body.definition},
            0, ${req.body.base}, ${global.config.locale}, ${req.user ? req.user.id : null},
            ${req.body.categories.join(',')}, ${JSON.stringify(req.body.flags)}, ${req.body.images ? req.body.images.join(',') : null}
        )
    `);
    await auditLog(req, 'terms/submitted', { ...req.body });

    if (req.isGranted('terms')) {
        await approve(req.db, id);
        await auditLog(req, 'terms/approved', { id });
    }

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

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

    await req.db.get(SQL`
        UPDATE terms
        SET approved = 0
        WHERE id = ${req.params.id}
    `);

    await invalidateCache('terms');
    await invalidateCache('search', 'term');

    await auditLog(req, 'terms/hidden', { id: req.params.id });

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

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

    await approve(req.db, req.params.id);

    await auditLog(req, 'terms/approved', { id: req.params.id });

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

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

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

    await invalidateCache('terms');
    await invalidateCache('search', 'term');

    await auditLog(req, 'terms/removed', { id: req.params.id });

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

router.get('/terms/keys', handleErrorAsync(async (req, res) => {
    const keys = await req.db.all<{ key: string }>(SQL`
        SELECT trim(key) AS key
        FROM terms
        WHERE key IS NOT NULL
          AND deleted = 0
          AND approved = 1
        GROUP BY key
        ORDER BY key
    `);

    return res.json(
        Object.fromEntries(keys.map((k) => [k.key, k.key])),
    );
}));

export default router;