PronounsPage/server/buildStats.ts

import fs from 'fs';

import Plausible from 'plausible-api';
import SQL from 'sql-template-strings';
import { decodeTime, ulid } from 'ulid';

import type { Config } from '../locale/config.ts';
import type { LocaleDescription } from '../locale/locales.ts';
import type { Translations } from '../locale/translations.ts';

import { newDate } from '#shared/helpers.ts';
import { listMissingTranslations } from '#shared/missingTranslations.ts';
import { loadTranslator } from '~~/server/data.ts';
import type { Database } from '~~/server/db.ts';
import { loadSuml } from '~~/server/loader.ts';
import mailer from '~~/server/mailer.ts';

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

export const buildChart = (rows: { id: string }[], cumulative = true): Record<string, number> => {
    const dates = rows.map((row) => new Date(decodeTime(row.id)));

    const chart: Record<string, number> = {};

    let loop = dates[0];
    const end = dates[dates.length - 1];
    while (loop <= end) {
        chart[formatDate(loop)] = 0;
        loop = new Date(loop.setDate(loop.getDate() + 1));
    }
    if (!loop) {
        return {};
    }
    chart[formatDate(loop)] = 0;

    for (const date of dates) {
        chart[formatDate(date)]++;
    }

    if (!cumulative) {
        return chart;
    }

    const cumChart: Record<string, number> = {};
    let cum = 0;
    for (const [date, count] of Object.entries(chart)) {
        cum += count;
        cumChart[date] = cum;
    }

    return cumChart;
};

const plausibleClient = new Plausible(process.env.PLAUSIBLE_API_KEY!, `${process.env.NUXT_PUBLIC_PLAUSIBLE_API_HOST}/api/v1/stats`);

interface PlausibleData {
    visitors: number;
    pageviews: number;
    visit_duration: number;
    realTimeVisitors: number;
}

const checkPlausible = async (url: string): Promise<PlausibleData | undefined> => {
    try {
        const domain = url.replace(new RegExp('^https?://'), '');
        const plausible = await plausibleClient.aggregate(domain, '30d', ['visitors', 'pageviews', 'visit_duration']) as PlausibleData;
        plausible.realTimeVisitors = await plausibleClient.getRealtimeVisitors(domain);
        return plausible;
    } catch {
        return undefined;
    }
};

const checkHeartbeat = async (): Promise<Record<string, { uptime: number; avgResponseTime: number }>> => {
    const heartbeat: Record<string, { uptime: number; avgResponseTime: number }> = {};
    try {
        const payload = (await (await fetch(`${process.env.NUXT_PUBLIC_HEARTBEAT_LINK}/30d.json`)).json()).pages as
            Record<string, { uptime: number; avgResponseTime: number }>;
        for (const [page, pageStats] of Object.entries(payload)) {
            if (page.startsWith('dns-')) {
                continue;
            }
            heartbeat[`https://${page}`] = {
                uptime: pageStats.uptime,
                avgResponseTime: pageStats.avgResponseTime,
            };
        }
    } catch {}

    return heartbeat;
};

const deduplicateAdminMail = (projectDir: string, type: string, seconds: number): boolean => {
    const filename = `${projectDir}/dedup-${type}`;
    if (!fs.existsSync(filename)) {
        fs.writeFileSync(filename, `${+newDate()}`);
        return true;
    }

    try {
        const lastSent = new Date(parseInt(fs.readFileSync(filename).toString()));
        if (newDate().getTime() - lastSent.getTime() < seconds * 1000) {
            return false;
        }
    } catch { /* ignore */ }

    fs.writeFileSync(filename, `${+newDate()}`);
    return true;
};

export interface LocaleStats {
    locale: string;
    users: number;
    data: OverallStatsData | LocaleStatsData;
}

export interface OverallStatsData {
    admins: number;
    userReports: number;
    bansPending: number;
    heartbeat: { uptime: number; avgResponseTime: number };
    plausible: PlausibleData | undefined;
    cardsQueue: number;
    linksQueue: number;
}

export interface LocaleStatsData {
    nouns: { approved: number; awaiting: number };
    inclusive: { approved: number; awaiting: number };
    terms: { approved: number; awaiting: number };
    sources: { approved: number; awaiting: number };
    names: { approved: number; awaiting: number };
    translations: { missing: number; awaitingApproval: number; awaitingMerge: number };
    heartbeat: { uptime: number; avgResponseTime: number };
    plausible: PlausibleData | undefined;
}

export const calculateStats = async (
    db: Database,
    locales: LocaleDescription[],
    projectDir: string,
): Promise<LocaleStats[]> => {
    const translator = await loadTranslator('_');

    const id = ulid();

    const heartbeat = await checkHeartbeat();

    const cardsQueue = (await db.get('SELECT count(*) as c FROM profiles WHERE card = \'\' OR cardDark = \'\'') as { c: number }).c;
    if (cardsQueue > 64 && deduplicateAdminMail(projectDir, 'cards', 60 * 60)) {
        await mailer('technical@pronouns.page', 'cardsWarning', translator, { count: cardsQueue });
    }
    const linksQueue = (await db.get(
        `SELECT count(*) as c
        FROM links
        WHERE (expiresAt IS NULL OR expiresAt <= ${newDate().getTime() / 1000})`,
    ) as { c: number }).c;
    if (linksQueue > 256 && deduplicateAdminMail(projectDir, 'links', 60 * 60)) {
        await mailer('technical@pronouns.page', 'linksWarning', translator, { count: linksQueue });
    }

    const stats = [];
    stats.push({
        locale: '_',
        users: (await db.get('SELECT count(*) AS c FROM users') as { c: number }).c,
        monthlyActiveUsers: (await db.get(
            `SELECT count(*) AS c
            FROM users
            WHERE lastActive >= ${newDate().getTime() - 30 * 24 * 60 * 60 * 1000}`,
        ) as { c: number }).c,
        data: {
            admins: (await db.get('SELECT count(*) AS c FROM users WHERE roles!=\'\'') as { c: number }).c,
            userReports: (await db.get(`SELECT count(*) AS c FROM reports
                LEFT JOIN users sus ON reports.userId = sus.id
                WHERE isHandled = 0 AND sus.username IS NOT NULL`) as { c: number }).c,
            bansPending: (await db.get('SELECT count(*) AS c FROM ban_proposals p LEFT JOIN users u ON p.userId = u.id WHERE u.bannedBy IS NULL') as { c: number }).c,
            heartbeat: heartbeat['https://pronouns.page'],
            plausible: await checkPlausible('https://pronouns.page'),
            cardsQueue,
            linksQueue,
        },
    });

    const baseTranslations = await loadSuml<Translations>('locale/_base/translations.suml');

    for (const locale of locales) {
        if (locale.code === '_') {
            continue;
        }

        const translations = await loadSuml<Translations>(`locale/${locale.code}/translations.suml`);
        const config = await loadSuml<Config>(`locale/${locale.code}/config.suml`);
        const missingTranslations = listMissingTranslations(translations, baseTranslations, config)
            .filter((k) => !k.startsWith('flags_alt.'))
            .length;

        stats.push({
            locale: locale.code,
            users: (await db.get(SQL`SELECT count(*) as c FROM profiles WHERE locale=${locale.code}`) as { c: number }).c,
            data: {
                nouns: {
                    approved: (await db.get(SQL`SELECT count(*) AS c FROM nouns WHERE locale=${locale.code} AND approved=1 AND deleted=0`) as { c: number }).c,
                    awaiting: (await db.get(SQL`SELECT count(*) AS c FROM nouns WHERE locale=${locale.code} AND approved=0 AND deleted=0`) as { c: number }).c,
                },
                inclusive: {
                    approved: (await db.get(SQL`SELECT count(*) AS c FROM inclusive WHERE locale=${locale.code} AND approved=1 AND deleted=0`) as { c: number }).c,
                    awaiting: (await db.get(SQL`SELECT count(*) AS c FROM inclusive WHERE locale=${locale.code} AND approved=0 AND deleted=0`) as { c: number }).c,
                },
                terms: {
                    approved: (await db.get(SQL`SELECT count(*) AS c FROM terms WHERE locale=${locale.code} AND approved=1 AND deleted=0`) as { c: number }).c,
                    awaiting: (await db.get(SQL`SELECT count(*) AS c FROM terms WHERE locale=${locale.code} AND approved=0 AND deleted=0`) as { c: number }).c,
                },
                sources: {
                    approved: (await db.get(SQL`SELECT count(*) AS c FROM sources WHERE locale=${locale.code} AND approved=1 AND deleted=0`) as { c: number }).c,
                    awaiting: (await db.get(SQL`SELECT count(*) AS c FROM sources WHERE locale=${locale.code} AND approved=0 AND deleted=0`) as { c: number }).c,
                },
                names: {
                    approved: (await db.get(SQL`SELECT count(*) AS c FROM names WHERE locale=${locale.code} AND approved=1 AND deleted=0`) as { c: number }).c,
                    awaiting: (await db.get(SQL`SELECT count(*) AS c FROM names WHERE locale=${locale.code} AND approved=0 AND deleted=0`) as { c: number }).c,
                },
                translations: {
                    missing: missingTranslations,
                    awaitingApproval: (await db.get(SQL`SELECT count(*) AS c FROM translations WHERE locale=${locale.code} AND status=0`) as { c: number }).c,
                    awaitingMerge: (await db.get(SQL`SELECT count(*) AS c FROM translations WHERE locale=${locale.code} AND status=1`) as { c: number }).c,
                },
                plausible: await checkPlausible(locale.url),
                heartbeat: heartbeat[locale.url],
            },
        });
    }

    const DOUBLE_APOSTROPHE = '\'\'';
    for (const statsLocale of stats) {
        await db.get(`REPLACE INTO stats (id, locale, users, data) VALUES (
            '${id}',
            '${statsLocale.locale}',
            ${statsLocale.users},
            '${JSON.stringify(statsLocale.data).replace(/'/g, DOUBLE_APOSTROPHE)}'
        )`);
    }

    return stats;
};