PronounsPage/server/express/profile.ts

import fs from 'fs';
import zlib from 'node:zlib';

import { S3, NoSuchKey } from '@aws-sdk/client-s3';
import * as Sentry from '@sentry/node';
import { Router } from 'express';
import type { Request, Response } from 'express';
import { getH3Event } from 'h3-express';
import md5 from 'js-md5';
import type { ParsedQs } from 'qs';
import SQL from 'sql-template-strings';
import { ulid } from 'ulid';

import allLocales from '../../locale/locales.ts';
import type { LocaleDescription } from '../../locale/locales.ts';
import { birthdateRange, formatDate, parseDate } from '../../src/birthdate.ts';
import { handleErrorAsync, now, isValidLink } from '../../src/helpers.ts';
import { normaliseUrl } from '../../src/links.ts';
import type { Opinion } from '../../src/opinions.ts';
import { ProfileVisibility, applyProfileVisibilityRules } from '../../src/profile.ts';
import type {
    LinkMetadata,
    OpinionFormValue,
    Profile,
    ProfileV1,
    RelatedPerson,
    SaveProfilePayload,
    ValueOpinion,
} from '../../src/profile.ts';
import { socialProviders } from '../../src/socialProviders.ts';
import { colours, styles } from '../../src/styling.ts';
import { auditLog } from '../audit.ts';
import avatar from '../avatar.ts';
import { awsConfig, awsParams } from '../aws.ts';
import crypto from '../crypto.ts';
import type { Database } from '../db.ts';
import { rootDir } from '../paths.ts';
import { downgradeToV1, upgradeToV2 } from '../profileV2.ts';

import type { AuthenticatorRow, UserRow } from './user.ts';

interface LinkRow {
    url: string | null;
    expiresAt: number | null;
    favicon: string | null;
    faviconCache: string | null;
    relMe: string | null;
    nodeinfo: string | null;
}

interface ProfileRow {
    id: string;
    userId: string;
    locale: string;
    names: string;
    pronouns: string;
    description: string;
    birthday: string | null;
    links: string;
    flags: string;
    words: string;
    active: number;
    teamName: string | null;
    footerName: string | null;
    footerAreas: string | null;
    customFlags: string;
    card: string | null;
    credentials: string | null;
    credentialsLevel: number | null;
    credentialsName: number | null;
    cardDark: string | null;
    opinions: string;
    timezone: string | null;
    sensitive: string;
    markdown: number | null;
    events: string | null;
    customEvents: string | null;
    lastUpdate: string | null;
    visibility: ProfileVisibility;
}

interface UserConnectionRow {
    id: string;
    from_profileId: string;
    to_userId: string;
    relationship: string;
}

const normalise = (s: string): string => decodeURIComponent(s.trim().toLowerCase());
const normaliseWithLink = (s: string): string => {
    return normalise(s.replace(/^@/, '').replace(new RegExp('^https://.*?/@'), ''));
};

const calcAge = (birthday: string): number | null => {
    if (!birthday) {
        return null;
    }

    const now = new Date();
    const birth = parseDate(birthday)!;

    const diff = now.getTime() - birth.getTime();

    return Math.floor(diff / 1000 / 60 / 60 / 24 / 365.25);
};

const providersWithLinks = Object.keys(socialProviders)
    .filter((p) => socialProviders[p].linkRegex !== undefined);

const domains = ['https://pronouns.page', ...allLocales.map((l) => l.url)];

const verifyLinks = (
    links: string[],
    authenticators: Pick<AuthenticatorRow, 'type' | 'payload'>[],
    username: string,
    linksMetadata: Record<string, LinkMetadata>,
): Record<string, string> => {
    const verifiedLinks: Record<string, string> = {};
    for (const link of links) {
        const linkMetadata = linksMetadata[link];
        if (!linkMetadata || !linkMetadata.relMe) {
            continue;
        }
        for (let relMe of linkMetadata.relMe) {
            if (domains.filter((d) => relMe.startsWith(d)).length === 0) {
                continue;
            }
            try {
                relMe = new URL(relMe).pathname;
            } catch {
                continue;
            }
            if (!relMe.startsWith('/@') && !relMe.startsWith('/u/')) {
                continue;
            }
            relMe = relMe.replace(new RegExp('^/@'), '').replace(new RegExp('^/u/'), '');
            if (normalise(username) === normalise(relMe)) {
                verifiedLinks[link] = 'relMe';
            }
        }
    }
    for (const provider of providersWithLinks) {
        for (const a of authenticators) {
            if (a.type !== provider) {
                continue;
            }
            const regex = new RegExp(socialProviders[a.type].linkRegex!(JSON.parse(a.payload)), 'i');
            for (const link of links) {
                if (link.match(regex)) {
                    verifiedLinks[link] = provider;
                }
            }
        }
    }

    return verifiedLinks;
};

interface ProfileProps {
    enabled: boolean;
    default_value: boolean;
    [property: string]: boolean;
}

export class ProfileOptions {
    default_props: ProfileProps = {
        enabled: true,
        default_value: true,
    };

    locale_specific_props: Record<string, ProfileProps> = {};

    constructor(query_obj: ParsedQs, locales?: Record<string, LocaleDescription>) {
        if (query_obj == null) {
            return;
        }

        const default_props = query_obj.props;
        this.default_props = ProfileOptions._parse_props(default_props! as string | string[] | ParsedQs);

        if (locales != null) {
            for (const locale of Object.values(locales)) {
                const key = locale.code.toLowerCase();
                const props = query_obj[`lprops[${key}]`];
                if (props !== undefined) {
                    this.locale_specific_props[key] = ProfileOptions._parse_props(props as string | string[] | ParsedQs);
                }
            }
        } else {
            // console.warn("ProfileOptions object was constructed without specifying locales parameter - not providing locale-specific data");
        }
    }

    static _parse_props(raw_param: string | string[] | ParsedQs) {
        if (raw_param == null) {
            return {
                enabled: true,
                default_value: true,
            };
        }
        let properties: string[] = [];
        const default_value = false;
        const obj: Record<string, boolean> = {};
        if (Array.isArray(raw_param)) {
            properties = raw_param;
        } else if (typeof raw_param === 'object') {
            return {
                ...raw_param,
                enabled: true,
                default_value: false,
            };
        } else if (typeof raw_param === 'string') {
            switch (raw_param) {
                case 'none':
                // Don't show the locale at all
                    return {
                        enabled: false,
                        default_value: false,
                    };
                case 'empty':
                // Include it as an object, but don't include any data
                    return {
                        enabled: true,
                        default_value: false,
                    };
                case 'all':
                // All properties should be shown
                    return {
                        enabled: true,
                        default_value: true,
                    };
            }
            // console.log("unrecognised: ", raw_param)
            properties = raw_param.split(',');
        }

        for (const property of properties) {
            obj[property] = !default_value;
        }

        return {
            ...obj,
            enabled: true,
            default_value,
        };
    }

    _data_for(locale: string) {
        if (typeof locale !== 'string') {
            throw new Error('Locale must be string');
        }
        return this.locale_specific_props[locale] ?? this.default_props;
    }

    show_at_all(locale: string): boolean {
        return this._data_for(locale).enabled ?? true;
    }

    prop(locale: string, property: string): boolean {
        if (!this.show_at_all(locale)) {
            return false;
        }
        // it's duplicate data getting (show_at_all also calls _data_for),
        // but it's not that important to optimise
        const data = this._data_for(locale);
        return data[property] ?? data.default_value ?? true;
    }

    propv<T>(locale: string, property: string, value: () => T): T | undefined {
        if (this.prop(locale, property)) {
            return value();
        } else {
            return undefined;
        }
    }
}

const fetchProfiles = async (
    db: Database,
    username: string,
    self: boolean,
    optsArg: ProfileOptions | undefined = undefined,
): Promise<Record<string, Partial<Profile>>> => {
    const opts = optsArg ?? new ProfileOptions({});
    const user = await db.get<Pick<UserRow, 'id'>>(SQL`SELECT id FROM users WHERE usernameNorm = ${normalise(username)}`);
    if (!user) {
        return {};
    }

    const profiles = await db.all<ProfileRow>(SQL`
        SELECT profiles.*
        FROM profiles
        WHERE userId = ${user.id}
        ORDER BY profiles.locale
    `);

    const linkAuthenticators = await db.all<Pick<AuthenticatorRow, 'type' | 'payload'>>(SQL`
        SELECT a.type, a.payload
        FROM authenticators a
        WHERE a.userId = ${user.id}
        AND a.type IN (`.append(providersWithLinks.map((k) => `'${k}'`).join(',')).append(SQL`)
        AND (a.validUntil IS NULL OR a.validUntil > ${now()})
    `));

    const p: Record<string, Partial<Profile>> = {};
    for (const profile of profiles) {
        if (!opts.show_at_all(profile.locale)) {
            continue;
        }
        const propv = <T>(property: string, value: () => T): T | undefined => {
            return opts.propv(profile.locale, property, value);
        };

        type LinkData = Partial<{ links: string[]; linksMetadata: Record<string, LinkMetadata>; verifiedLinks: Record<string, string> }>;
        const link_data: LinkData = await propv('links', async () => {
            const links = (JSON.parse(profile.links) as string[]).filter((l) => !!normaliseUrl(l));
            const linksMetadata: Record<string, LinkMetadata> = {};
            for (const link of await db.all<LinkRow>(SQL`SELECT * FROM links WHERE url IN (`.append(links.map((k) => `'${k.replace(/'/g, '\'\'')}'`).join(',')).append(SQL`)`))) {
                linksMetadata[link.url!] = {
                    favicon: link.faviconCache || link.favicon,
                    relMe: JSON.parse(link.relMe!),
                    nodeinfo: JSON.parse(link.nodeinfo!),
                };
            }
            const verifyLinksResult = verifyLinks(links, linkAuthenticators, username, linksMetadata);
            return { links, linksMetadata, verifiedLinks: verifyLinksResult };
        }) ?? {};

        const profile_obj: Partial<Profile> = {
            id: propv('id', () => profile.id),
            opinions: propv('opinions', () => JSON.parse(profile.opinions)),
            names: propv('names', () => {
                return JSON.parse(profile.names).map((name: ValueOpinion) => {
                    return { pronunciation: null, ...name };
                });
            }),
            pronouns: propv('pronouns', () => JSON.parse(profile.pronouns)),
            description: propv('description', () => profile.description),
            age: propv('age', () => calcAge(profile.birthday!)),
            links: link_data.links,
            linksMetadata: link_data.linksMetadata,
            verifiedLinks: link_data.verifiedLinks,
            flags: propv('flags', () => JSON.parse(profile.flags)),
            customFlags: propv('flags', () => JSON.parse(profile.customFlags)),
            words: propv('words', () => JSON.parse(profile.words)),
            birthday: propv('age', () => self ? profile.birthday : undefined),
            timezone: propv('timezone', () => profile.timezone ? JSON.parse(profile.timezone) : null),
            teamName: propv('team', () => profile.teamName),
            footerName: propv('team', () => profile.footerName),
            footerAreas: propv('team', () => profile.footerAreas ? profile.footerAreas.split(',') : []),
            credentials: propv('credentials', () => profile.credentials ? profile.credentials.split('|') : []),
            credentialsLevel: propv('credentials', () => profile.credentialsLevel),
            credentialsName: propv('credentials', () => profile.credentialsName),
            card: propv('card_image', () => profile.card),
            cardDark: propv('card_image', () => profile.cardDark),
            circle: await propv('circle', () => fetchCircles(db, profile.id, user.id)),
            sensitive: propv('sensitive', () => JSON.parse(profile.sensitive)),
            markdown: propv('markdown', () => !!profile.markdown),
            events: propv('sensitive', () => JSON.parse(profile.events || '[]')),
            customEvents: propv('sensitive', () => {
                return JSON.parse(profile.customEvents || '[]').map((customEvent: Partial<CustomEvent>) => {
                    return { icon: 'user', ...customEvent };
                });
            }),
            visibility: profile.visibility,
            access: true,
            lastUpdate: propv('lastUpdate', () => profile.lastUpdate),
        };
        p[profile.locale] = profile_obj;
    }
    return p;
};

export const profilesSnapshot = async (
    db: Database,
    username: string,
    opts: ProfileOptions | undefined = undefined,
): Promise<string> => {
    return JSON.stringify(await fetchProfiles(db, username, true, opts), null, 4);
};

const susRegexes = fs.readFileSync(`${rootDir}/moderation/sus.txt`).toString('utf-8')
    .split('\n')
    .filter((x) => !!x);

function* isSuspicious(profile: SaveProfilePayload) {
    for (let s of [
        profile.description,
        JSON.stringify(profile.customFlags),
        JSON.stringify(profile.pronouns),
        JSON.stringify(profile.names),
        JSON.stringify(profile.words),
        JSON.stringify(profile.opinions),
        JSON.stringify(profile.circle),
    ]) {
        s = s.toLowerCase().replace(/\s+/g, ' ');
        for (const sus of susRegexes) {
            const m = s.match(new RegExp(sus, 'ig'));
            if (m) {
                yield `${m[0]} (${sus})`;
            }
        }
    }
}

const mildSus = [
    'fag',
    'faggot',
    'tranny',
    'phobic',
    'kys',
];

const hasAutomatedReports = async (db: Database, id: string): Promise<boolean> => {
    return (await db.get<{ c: number }>(SQL`SELECT COUNT(*) AS c FROM reports WHERE userId = ${id} AND isAutomatic = 1`))!.c > 0;
};

const usernamesToIds = async (db: Database, usernames: string[]): Promise<Record<string, string>> => {
    const users = await db.all<Pick<UserRow, 'id' | 'usernameNorm'>>(SQL`
        SELECT id, usernameNorm
        FROM users
        WHERE users.usernameNorm IN (`.append(usernames.map((k) => `'${normaliseWithLink(k)}'`).join(',')).append(SQL`)`));

    const idMap: Record<string, string> = {};
    for (const username of usernames) {
        for (const { id, usernameNorm } of users) {
            if (normaliseWithLink(username) === usernameNorm) {
                idMap[normaliseWithLink(username)] = id;
            }
        }
    }

    return idMap;
};

const selectBestLocale = (availableLocales: string[]): string => {
    if (availableLocales.length === 1) {
        return availableLocales[0];
    }

    if (availableLocales.includes(global.config.locale)) {
        return global.config.locale;
    }

    return '_';
};

const fetchCircles = async (db: Database, profileId: string, userId: string): Promise<RelatedPerson[]> => {
    type QueryResult = Pick<UserRow, 'id' | 'username' | 'avatarSource' | 'email'> & Pick<ProfileRow, 'locale'> & Pick<UserConnectionRow, 'relationship'>;
    const connections = await db.all<QueryResult>(SQL`
        SELECT u.id, u.username, u.avatarSource, u.email, p.locale, c.relationship
        FROM user_connections c
            LEFT JOIN users u ON u.id = c.to_userId
            LEFT JOIN profiles p ON p.userId = u.id
        WHERE from_profileId = ${profileId}
            AND u.bannedReason IS NULL
        ORDER BY c.id
    `);

    const mentions = await findCircleMentions(db, userId);

    const circle: Record<string, Omit<RelatedPerson, 'locale'> & { locale: string[] }> = {};

    for (const connection of connections) {
        if (!circle.hasOwnProperty(connection.username)) {
            circle[connection.username] = {
                username: connection.username,
                avatar: await avatar(db, connection),
                circleMutual: mentions[connection.username] !== undefined,
                locale: [],
                relationship: connection.relationship,
            };
        }
        circle[connection.username].locale.push(connection.locale);
    }

    return Object.values(circle).map((relatedPerson) => ({
        ...relatedPerson,
        locale: selectBestLocale(relatedPerson.locale),
    }));
};

const router = Router();

const fetchProfilesRoute = async (req: Request, res: Response, user: any): Promise<Response> => {
    const isSelf = !!req.user && req.user.username === req.params.username;
    const isAdmin = req.isGranted('users') || req.isGranted('community');
    const opts = new ProfileOptions(req.query, req.locales);

    if (!user || user.bannedReason !== null && !isAdmin && !isSelf) {
        return res.json({
            profiles: {},
        });
    }

    user.emailHash = md5(user.email);
    delete user.email;
    user.avatar = await avatar(req.db, user);

    user.bannedTerms = user.bannedTerms ? user.bannedTerms.split(',') : [];

    const profiles: Record<string, Partial<Profile | ProfileV1>> = await fetchProfiles(req.db, user.username, isSelf, opts);
    if (req.query.version !== '2') {
        for (const [locale, profile] of Object.entries(profiles)) {
            profiles[locale] = downgradeToV1(profile as Partial<Profile>);
        }
    }

    for (const [locale, profile] of Object.entries(profiles)) {
        profiles[locale] = applyProfileVisibilityRules(req.user, profile as Partial<Profile>, true);
    }

    return res.json({
        ...user,
        profiles,
    });
};

router.get('/profile/get/:username', handleErrorAsync(async (req, res) => {
    const user = await req.db.get(SQL`
        SELECT
            users.id,
            users.username,
            users.email,
            users.avatarSource,
            users.bannedReason,
            users.bannedTerms,
            users.bannedBy,
            users.roles != '' AS team
        FROM users
        WHERE users.usernameNorm = ${normalise(req.params.username)}
    `);

    return await fetchProfilesRoute(req, res, user);
}));

router.get('/profile/get-id/:id', handleErrorAsync(async (req, res) => {
    const user = await req.db.get(SQL`
        SELECT
            users.id,
            users.username,
            users.email,
            users.avatarSource,
            users.bannedReason,
            users.bannedTerms,
            users.bannedBy,
            users.roles != '' AS team
        FROM users
        WHERE users.id = ${req.params.id}
    `);

    return await fetchProfilesRoute(req, res, user);
}));

router.get('/profile/versions/:username', handleErrorAsync(async (req, res) => {
    return res.json((await req.db.all<Pick<ProfileRow, 'locale'>>(SQL`
        SELECT
            profiles.locale
        FROM users
            LEFT JOIN profiles ON profiles.userId = users.id
        WHERE users.usernameNorm = ${normaliseWithLink(req.params.username)}
    `)).map((x) => x.locale));
}));

const findCircleMentions = async (db: Database, userId: string) => {
    type QueryResult = Pick<UserRow, 'username'> & Pick<ProfileRow, 'locale'> & Pick<UserConnectionRow, 'relationship'>;
    const mentionsRaw = await db.all<QueryResult>(SQL`
        SELECT
            u.username, p.locale, c.relationship
        FROM user_connections c
            LEFT JOIN profiles p ON p.id = c.from_profileId
            LEFT JOIN users u ON u.id = p.userId
        WHERE c.to_userId = ${userId}
    `);

    const mentionsGrouped: Record<string, Record<string, string>> = {};
    for (const { username, locale, relationship } of mentionsRaw) {
        if (!mentionsGrouped.hasOwnProperty(username)) {
            mentionsGrouped[username] = {};
        }

        mentionsGrouped[username][locale] = relationship;
    }

    return mentionsGrouped;
};

router.get('/profile/my-circle-mentions', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

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

const cleanupBirthday = (bd: string | null): string | null => {
    if (!bd) {
        return null;
    }
    const match = bd.match(/^(\d\d\d\d)-(\d\d)-(\d\d)$/);
    if (!match) {
        return null;
    }

    const date = new Date(parseInt(match[1]), parseInt(match[2]) - 1, parseInt(match[3]));
    if (date < birthdateRange(global.config).min || date > birthdateRange(global.config).max) {
        return null;
    }

    return formatDate(date);
};

const cleanupOpinions = (opinions: OpinionFormValue[]) => {
    const cleanOpinions: Record<string, Opinion & { description: string }> = {};
    for (const opinion of opinions) {
        if (!opinion.icon || !opinion.description) {
            continue;
        }
        cleanOpinions[opinion.icon] = {
            icon: opinion.icon,
            description: opinion.description.substring(0, 36),
            colour: opinion.colour && colours.includes(opinion.colour) ? opinion.colour : undefined,
            style: opinion.style && styles.includes(opinion.style) ? opinion.style : undefined,
        };
    }
    return cleanOpinions;
};

type SaveProfile = Omit<Profile, 'linksMetadata' | 'verifiedLinks' | 'footerAreas' | 'credentials' |
'card' | 'cardDark' | 'circle' | 'markdown' | 'events' | 'customEvents' | 'lastUpdate' | 'id' | 'access'>
& Partial<Pick<Profile, 'markdown' | 'events' | 'customEvents'>>
& Pick<ProfileRow, 'footerAreas' | 'credentials'>;

const saveProfile = async (req: Request, locale: string, {
    opinions, names, pronouns, description, birthday, timezone, links, flags, customFlags, words, sensitive,
    teamName, footerName, footerAreas, credentials, credentialsLevel, credentialsName,
    visibility,
    markdown = false, events = [], customEvents = [],
}: SaveProfile) => {
    // TODO just make it a transaction...
    const ids = (await req.db.all<ProfileRow>(SQL`SELECT * FROM profiles WHERE userId = ${req.user!.id} AND locale = ${locale}`)).map((row) => row.id);
    let profileId;
    if (ids.length) {
        profileId = ids[0];
        await req.db.get(SQL`UPDATE profiles
            SET
                opinions = ${JSON.stringify(opinions)},
                names = ${JSON.stringify(names)},
                pronouns = ${JSON.stringify(pronouns)},
                description = ${description},
                birthday = ${birthday},
                timezone = ${JSON.stringify(timezone)},
                links = ${JSON.stringify(links)},
                flags = ${JSON.stringify(flags)},
                customFlags = ${JSON.stringify(customFlags)},
                words = ${JSON.stringify(words)},
                sensitive = ${JSON.stringify(sensitive)},
                markdown = ${markdown ? 1 : 0},
                events = ${JSON.stringify(events)},
                customEvents = ${JSON.stringify(customEvents)},
                teamName = ${req.isGranted() ? teamName || null : ''},
                footerName = ${req.isGranted() ? footerName || null : ''},
                footerAreas = ${req.isGranted() ? footerAreas || null : ''},
                credentials = ${req.isGranted() ? credentials || null : null},
                credentialsLevel = ${req.isGranted() ? credentialsLevel || null : null},
                credentialsName = ${req.isGranted() ? credentialsName || null : null},
                visibility = ${visibility},
                card = NULL,
                cardDark = NULL,
                lastUpdate = ${ulid()}
            WHERE id = ${profileId}
        `);
    } else {
        profileId = ulid();
        await req.db.get(SQL`INSERT INTO profiles (id, userId, locale, opinions, names, pronouns, description, birthday, timezone, links, flags, customFlags, words, sensitive, markdown, events, customEvents, active, teamName, footerName, footerAreas, visibility, lastUpdate)
            VALUES (${profileId}, ${req.user!.id}, ${locale},
                    ${JSON.stringify(opinions)},
                    ${JSON.stringify(names)},
                    ${JSON.stringify(pronouns)},
                    ${description},
                    ${birthday},
                    ${JSON.stringify(timezone)},
                    ${JSON.stringify(links)},
                    ${JSON.stringify(flags)},
                    ${JSON.stringify(customFlags)},
                    ${JSON.stringify(words)},
                    ${JSON.stringify(sensitive)},
                    ${markdown ? 1 : 0},
                    ${JSON.stringify(events)},
                    ${JSON.stringify(customEvents)},
                    1,
                    ${req.isGranted() ? teamName || null : ''},
                    ${req.isGranted() ? footerName || null : ''},
                    ${req.isGranted() ? footerAreas || null : ''},
                    ${visibility},
                    ${ulid()}
        )`);
    }

    return profileId;
};

router.post('/profile/save', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    if (req.body.username && req.user.username !== req.body.username) {
        await auditLog(req, 'profile/username_mismatch', { intended: req.body.username, token: req.user.username });
        return res.status(401).json({ error: 'Payload username does not match the token' });
    }

    if (!Array.isArray(req.body.names)) {
        // service worker cache sends v1 requests
        req.body = upgradeToV2(req.body);
    }

    if (!Array.isArray(req.body.customFlags)) {
        // no idea WTF is happening here, but somehow we got values like {"0": ..., "1": ..., ...}
        req.body.customFlags = Object.values(req.body.customFlags);
    }

    const profile: SaveProfilePayload = req.body;

    if (profile.opinions.length > 10 ||
        profile.names.length > 128 ||
        profile.pronouns.length > 128 ||
        profile.links.length > 128 ||
        profile.customFlags.length > 128 ||
        profile.circle.length > 16 ||
        profile.words.filter((c) => c.values.length > 64).length > 0 ||
        profile.sensitive.length > 16 ||
        profile.events.length > 100 ||
        profile.customEvents.length > 100 ||
        !Object.values(ProfileVisibility).includes(profile.visibility)
    ) {
        await auditLog(req, 'profile/form_validation_failed', { ...req.body });
        return res.status(400).json({ error: 'crud.validation.genericForm' });
    }

    const opinions = cleanupOpinions(profile.opinions);
    const nameMaxLength = global.config.profile.editorEnabled && global.config.profile.longNames ? 256 : 32;
    const names = profile.names.map((p) => {
        return { ...p, value: p.value.substring(0, nameMaxLength) };
    });
    const pronouns = profile.pronouns.map((p) => {
        return { ...p, value: p.value.substring(0, 192) };
    });
    const description = profile.description.substring(0, 1024);
    const birthday = cleanupBirthday(profile.birthday || null);
    const links = profile.links.filter((x) => !!x && isValidLink(x));
    const customFlags = profile.customFlags.filter((x) => x.name && (!x.link || isValidLink(x.link))).map((x) => {
        return {
            value: x.value,
            name: x.name.substring(0, 24),
            description: x.description ? x.description.substring(0, 512) : null,
            alt: x.alt ? x.alt.substring(0, 512) : null,
            link: x.link ? normaliseUrl(x.link) : null,
        };
    });
    const words = profile.words.map((c) => {
        return { ...c,
            values: c.values.map((p) => {
                return { ...p, value: p.value.substring(0, 32) };
            }) };
    });
    const sensitive = profile.sensitive.filter((x) => !!x).map((x) => x.substring(0, 64));
    const timezone = profile.timezone && profile.timezone.tz
        ? {
                tz: profile.timezone.tz,
                area: profile.timezone.area,
                loc: profile.timezone.loc,
            }
        : null;
    const markdown = !!profile.markdown;
    const events = profile.events.filter((x) => !!x && x.length < 256);
    const customEvents = profile.customEvents.filter((x) => {
        return x.name && x.name.length <= 24 &&
            x.month && x.day &&
            (x.comment || '').length <= 128;
    });

    const profileId = await saveProfile(req, global.config.locale, {
        opinions,
        names,
        pronouns,
        description,
        birthday,
        timezone,
        links,
        events,
        customEvents,
        flags: profile.flags,
        customFlags,
        words,
        sensitive,
        markdown,
        teamName: profile.teamName,
        footerName: profile.footerName,
        footerAreas: (profile.footerAreas || []).join(','),
        credentials: (profile.credentials || []).join('|'),
        credentialsLevel: profile.credentialsLevel,
        credentialsName: profile.credentialsName,
        visibility: profile.visibility,
    });

    await req.db.get(SQL`DELETE FROM user_connections WHERE from_profileId = ${profileId}`);
    const usernameIdMap = await usernamesToIds(req.db, profile.circle.map((r) => r.username));
    for (const connection of profile.circle) {
        const toUserId = usernameIdMap[normaliseWithLink(connection.username)];
        const relationship = connection.relationship.substring(0, 64).trim();
        if (toUserId === undefined || !relationship) {
            continue;
        }
        await req.db.get(SQL`INSERT INTO user_connections (id, from_profileId, to_userId, relationship) VALUES (
            ${ulid()},
            ${profileId},
            ${toUserId},
            ${relationship}
        )`);
    }

    if ((profile.propagate || []).includes('teamName')) {
        await req.db.get(SQL`UPDATE profiles
            SET teamName = ${req.isGranted() ? profile.teamName || null : ''}
            WHERE userId = ${req.user.id} AND teamName != '' AND teamName IS NOT NULL;
        `);
    }

    if ((profile.propagate || []).includes('footerName')) {
        await req.db.get(SQL`UPDATE profiles
            SET footerName = ${req.isGranted() ? profile.footerName || null : ''}
            WHERE userId = ${req.user.id} AND footerName != '' AND footerName IS NOT NULL;
        `);
    }

    if ((profile.propagate || []).includes('names')) {
        await req.db.get(SQL`UPDATE profiles
            SET names = ${JSON.stringify(profile.names)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('flags')) {
        await req.db.get(SQL`UPDATE profiles
            SET flags = ${JSON.stringify(profile.flags)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('customFlags')) {
        await req.db.get(SQL`UPDATE profiles
            SET customFlags = ${JSON.stringify(profile.customFlags)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('links')) {
        await req.db.get(SQL`UPDATE profiles
            SET links = ${JSON.stringify(profile.links.filter((x) => !!x))}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('links')) {
        await req.db.get(SQL`UPDATE profiles
            SET links = ${JSON.stringify(profile.links.filter((x) => !!x))}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('birthday')) {
        await req.db.get(SQL`UPDATE profiles
            SET birthday = ${cleanupBirthday(profile.birthday || null)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('timezone')) {
        await req.db.get(SQL`UPDATE profiles
            SET timezone = ${JSON.stringify(timezone)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('events')) {
        await req.db.get(SQL`UPDATE profiles
            SET events = ${JSON.stringify(events)}
            WHERE userId = ${req.user.id};
        `);
    }

    if ((profile.propagate || []).includes('visibility')) {
        await req.db.get(SQL`UPDATE profiles
            SET visibility = ${profile.visibility}
            WHERE userId = ${req.user.id};
        `);
    }

    for (const url of links) {
        const normalizedUrl = normaliseUrl(url);
        if (!normalizedUrl) {
            continue;
        }
        await req.db.get(SQL`INSERT INTO links (url) VALUES (${normalizedUrl}) ON CONFLICT (url) DO UPDATE SET expiresAt = null`);
    }

    let sus = [...isSuspicious(profile)];
    // keywords with a lot of false positives should only trigger when accompanied by at least one other keyword
    if (sus.length === 1 && mildSus.filter((k) => sus[0].startsWith(`${k} (`)).length > 0) {
        sus = [];
    }
    if (sus.length && !await hasAutomatedReports(req.db, req.user.id)) {
        await req.db.get(SQL`
            INSERT INTO reports (id, userId, reporterId, isAutomatic, comment, isHandled, snapshot)
            VALUES (${ulid()}, ${req.user.id}, null, 1, ${sus.join(', ')}, 0, ${await profilesSnapshot(req.db, normalise(req.user.username), new ProfileOptions({}))});
        `);
        await auditLog(req, 'profile/triggered_report', { ...req.body });
    }

    if (profile.teamName) {
        await invalidateCache('admin-list');
        await invalidateCache('admin-footer');
    }

    await req.db.get(SQL`UPDATE users SET inactiveWarning = null WHERE id = ${req.user.id}`);

    await auditLog(req, 'profile/updated', { ...req.body, locale: global.config.locale });

    return res.json(await fetchProfiles(req.db, req.user.username, true));
}));

router.post('/profile/delete/:locale', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(400).json({ error: 'Missing user' });
    }

    await req.db.get(SQL`DELETE FROM profiles WHERE userId = ${req.user.id} AND locale = ${req.params.locale}`);

    await auditLog(req, 'profile/deleted', { locale: req.params.locale });

    return res.json(await fetchProfiles(req.db, req.user.username, true));
}));

router.post('/profile/report/:username', handleErrorAsync(async (req, res) => {
    const user = await req.db.get<Pick<UserRow, 'id'>>(SQL`SELECT id FROM users WHERE usernameNorm = ${normalise(req.params.username)}`);
    if (!user) {
        return res.status(400).json({ error: 'Missing user' });
    }
    if (!req.body.comment) {
        return res.status(400).json({ error: 'Missing comment' });
    }

    await req.db.get(SQL`
        INSERT INTO reports (id, userId, reporterId, isAutomatic, comment, isHandled, snapshot)
        VALUES (${ulid()}, ${user.id}, ${req.user!.id}, 0, ${req.body.comment}, 0, ${await profilesSnapshot(req.db, normalise(req.params.username))});
    `);

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

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

router.post('/profile/request-card', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(400).json({ error: 'Missing user' });
    }

    if (req.query.dark === '1') {
        await req.db.get(SQL`
            UPDATE profiles
            SET cardDark = ''
            WHERE userId=${req.user.id} AND locale=${global.config.locale} AND cardDark IS NULL
        `);
    } else {
        await req.db.get(SQL`
            UPDATE profiles
            SET card = ''
            WHERE userId=${req.user.id} AND locale=${global.config.locale} AND card IS NULL
        `);
    }

    await auditLog(req, 'profile/requested_card_image');

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

router.get('/profile/has-card', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(400).json({ error: 'Missing user' });
    }

    const card = await req.db.get(SQL`
        SELECT card, cardDark
        FROM profiles
        WHERE userId=${req.user.id} AND locale=${global.config.locale}
    `);

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

router.post('/profile/remove-self-circle/:username', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(401).json({ error: 'Unauthorised' });
    }
    const user = await req.db.get<Pick<UserRow, 'id'>>(SQL`SELECT id FROM users WHERE usernameNorm = ${normalise(req.params.username)}`);
    if (!user) {
        return res.status(400).json({ error: 'Missing user' });
    }

    await req.db.get(SQL`
        DELETE FROM user_connections WHERE to_userId = ${req.user.id} AND from_profileId IN (
            SELECT id FROM profiles WHERE userId = ${user.id}
        )
    `);

    await auditLog(req, 'profile/removed_self_from_circle', {
        userId: user.id,
    });

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

interface ProfileExportData {
    version: 1;
    profiles: Record<string, ProfileExportProfile>;
    images: Record<string, { flag: string }>;
}

type ProfileExportProfile = Omit<Profile, 'linksMetadata' | 'verifiedLinks' | 'footerAreas' | 'credentials' |
'card' | 'cardDark' | 'circle' | 'lastUpdate' | 'id' | 'access'>
& Pick<ProfileRow, 'footerAreas' | 'credentials'>;

router.get('/profile/export', handleErrorAsync(async (req: Request, res: Response) => {
    if (!req.user || req.user.bannedReason) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const profiles: ProfileExportData['profiles'] = {};
    const customFlagIds: Set<string> = new Set();
    for (const profile of await req.db.all<ProfileRow>(SQL`SELECT * FROM profiles WHERE userId = ${req.user.id}`)) {
        const exportProfile: ProfileExportProfile = {
            names: JSON.parse(profile.names),
            pronouns: JSON.parse(profile.pronouns),
            description: profile.description,
            birthday: profile.birthday,
            links: JSON.parse(profile.links),
            flags: JSON.parse(profile.flags),
            words: JSON.parse(profile.words),
            customFlags: JSON.parse(profile.customFlags),
            opinions: JSON.parse(profile.opinions),
            timezone: JSON.parse(profile.timezone!),
            sensitive: JSON.parse(profile.sensitive),
            teamName: profile.teamName,
            footerName: profile.footerName,
            footerAreas: profile.footerAreas,
            credentials: profile.credentials,
            credentialsLevel: profile.credentialsLevel,
            credentialsName: profile.credentialsName,
            markdown: !!profile.markdown,
            events: JSON.parse(profile.events || '[]'),
            customEvents: JSON.parse(profile.customEvents || '[]'),
            visibility: profile.visibility,
        };

        for (const customFlag of exportProfile.customFlags) {
            customFlagIds.add(customFlag.value);
        }

        profiles[profile.locale] = exportProfile;
    }

    const s3 = new S3(awsConfig);
    const images: ProfileExportData['images'] = {};
    for (const id of customFlagIds) {
        try {
            const data = await s3.getObject({
                Key: `images/${id}-flag.png`,
                ...awsParams,
            });
            images[id] = {
                flag: await data.Body!.transformToString('base64'),
            };
        } catch (error) {
            Sentry.captureException(error);
        }
    }

    await auditLog(req, 'profile/exported', {
        profiles,
    });

    const payload = Buffer.from(JSON.stringify({
        version: 1,
        profiles,
        images,
    } satisfies ProfileExportData)).toString('base64');

    const signature = crypto.sign(payload);

    res.setHeader('Content-disposition', `attachment; filename=pronounspage-${req.user.username}-${+new Date()}.card.gz`);
    res.setHeader('Content-type', 'application/gzip');
    res.end(zlib.gzipSync(`${payload}\n${
        signature}`));
}));

router.post('/profile/import', handleErrorAsync(async (req, res) => {
    if (!req.user) {
        return res.status(401).json({ error: 'Unauthorised' });
    }

    const files = await readMultipartFormData(getH3Event(req));

    if (!files || files.length !== 1) {
        return res.status(401).json({ error: 'One file expected' });
    }

    const contentParts = zlib.gunzipSync(files[0].data as zlib.InputType).toString('utf-8')
        .split('\n');
    if (contentParts.length !== 2) {
        return res.status(401).json({ error: 'profile.backup.error.signature' });
    }

    const [payload, signature] = contentParts;

    if (!crypto.validate(payload, signature)) {
        return res.status(400).json({ error: 'profile.backup.error.signature' });
    }

    const { profiles, images } = JSON.parse(Buffer.from(payload, 'base64').toString('utf-8')) as ProfileExportData;

    const s3 = new S3(awsConfig);
    for (const [id, sizes] of Object.entries(images)) {
        for (const [size, content] of Object.entries(sizes)) {
            try {
                await s3.headObject({
                    Key: `images/${id}-${size}.png`,
                    ...awsParams,
                });
                continue;
            } catch (error) {
                if (!(error instanceof NoSuchKey)) {
                    throw error;
                }
            }

            await s3.putObject({
                Key: `images/${id}-${size}.png`,
                Body: Buffer.from(content, 'base64'),
                ContentType: 'image/png',
                ACL: 'public-read',
                ...awsParams,
            });
        }
    }

    for (const [locale, profile] of Object.entries(profiles)) {
        await saveProfile(req, locale, profile);
    }

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

export default router;