PronounsPage/server/routes/sources.ts

import { Router } from 'express';
import type { Request } from 'express';
import SQL from 'sql-template-strings';
import { ulid } from 'ulid';
import { clearKey, handleErrorAsync } from '../../src/helpers.ts';
import auditLog from '../audit.ts';
import type { Database } from '../db.ts';

export interface SourceRow {
    id: string;
    locale: string;
    pronouns: string;
    type: string;
    author: string | null;
    title: string;
    extra: string | null;
    year: number | null;
    fragments: string;
    comment: string | null;
    link: string | null;
    submitter_id: string | null;
    approved: number | null;
    deleted: number | null;
    base_id: string | null;
    key: string | null;
    images: string | null;
    spoiler: string;
}

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

const keyGetMain = (key: string): string => {
    return key.split('/')[0];
};

const linkOtherVersions = async (req: Request, sources: SourceRow[]) => {
    const keys = new Set(sources.filter((s) => !!s && s.key).map((s) => keyGetMain(clearKey(s.key)!)));

    let sql = SQL`
        SELECT s.*, u.username AS submitter FROM sources s
        LEFT JOIN users u ON s.submitter_id = u.id
        WHERE s.locale != ${global.config.locale}
        AND s.deleted = 0
        AND s.approved >= ${req.isGranted('sources') ? 0 : 1}
        AND (`;
    for (const key of keys) {
        sql = sql.append(SQL`s.key = ${key} OR s.key LIKE ${`${key}/%`} OR `);
    }
    sql = sql.append(SQL`0=1)`);

    const otherVersions = await req.db.all<SourceRow>(sql);

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

    return sources.map((s) => ({
        ...s,
        versions: s.key ? otherVersionsMap[keyGetMain(s.key)] || [] : [],
    }));
};

const router = Router();

router.get('/sources', handleErrorAsync(async (req, res) => {
    const sql = SQL`
        SELECT s.*, u.username AS submitter FROM sources s
        LEFT JOIN users u ON s.submitter_id = u.id
        WHERE s.locale = ${global.config.locale}
        AND s.deleted = 0
        AND s.approved >= ${req.isGranted('sources') ? 0 : 1}
    `;
    if (req.query.pronoun) {
        sql.append(SQL`AND s.pronouns LIKE ${`%${req.query.pronoun}%`}`);
    }
    return res.json(await linkOtherVersions(req, await req.db.all(sql)));
}));

router.get('/sources/:id', handleErrorAsync(async (req, res) => {
    return res.json(await linkOtherVersions(req, await req.db.all(SQL`
        SELECT s.*, u.username AS submitter FROM sources s
        LEFT JOIN users u ON s.submitter_id = u.id
        WHERE s.locale = ${global.config.locale}
        AND s.deleted = 0
        AND s.approved >= ${req.isGranted('sources') ? 0 : 1}
        AND s.id = ${req.params.id}
    `)));
}));

router.post('/sources/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 sources (id, locale, pronouns, type, author, title, extra, year, fragments, comment, link, key, images, spoiler, submitter_id, base_id)
        VALUES (
            ${id}, ${global.config.locale}, ${req.body.pronouns.join(';')},
            ${req.body.type}, ${req.body.author}, ${req.body.title}, ${req.body.extra}, ${req.body.year},
            ${req.body.fragments.join('@').replace(/\n/g, '|')}, ${req.body.comment}, ${req.body.link},
            ${clearKey(req.body.key)}, ${req.body.images ? req.body.images.join(',') : null}, ${req.body.spoiler ? 1 : 0},
            ${req.user ? req.user.id : null}, ${req.body.base}
        )
    `);
    await auditLog(req, 'sources/submitted', { ...req.body });

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

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

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

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

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

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

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

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

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

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

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

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

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

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

export default router;