PronounsPage/server/cleanupAccounts.ts

import './setup.ts';

import * as Sentry from '@sentry/node';
import SQL from 'sql-template-strings';

import dbConnection from './db.ts';
import type { Database } from './db.ts';
import mailer from './mailer.ts';

import { newDate } from '#shared/helpers.ts';
import type { User } from '#shared/user.ts';
import { loadTranslator } from '~~/server/data.ts';

const execute = process.env.EXECUTE === '1';
console.log(execute ? 'WILL EXECUTE!' : 'Dry run');

const now = +newDate();
const month = 30 * 24 * 60 * 60 * 1000;
const week = 7 * 24 * 60 * 60 * 1000;
const twoWeeks = 14 * 24 * 60 * 60 * 1000;

const sleep = (ms: number) => new Promise((res) => setTimeout(res, ms));

async function warnInactive(db: Database) {
    console.log('--- Fetching ids to warn ---');

    const users = await db.all<Pick<User, 'id' | 'username' | 'email' | 'bannedReason'>>(`
        SELECT u.id, u.username, u.email, u.bannedReason
        FROM users u
        WHERE
            inactiveWarning IS NULL
            AND (
                (
                    u.id NOT IN (SELECT DISTINCT p.userId FROM profiles p)
                    AND lastActive < ${now - month}
                )
                OR bannedReason IS NOT NULL
            )
    `);

    console.log(users.length);

    const translator = await loadTranslator('en');

    for (const user of users) {
        console.log('warn', user);
        if (!execute) {
            continue;
        }

        try {
            const userRefreshed = await db.get<
                Pick<User, 'id' | 'username' | 'email' | 'bannedReason' | 'inactiveWarning'>
            >(SQL`
                SELECT u.id, u.username, u.email, u.bannedReason, u.inactiveWarning
                FROM users u
                WHERE u.id = '${user.id}'
            `);
            if (userRefreshed!.inactiveWarning !== null) {
                continue;
            }

            if (userRefreshed!.email.endsWith('.oauth')) {
                await db.get(`
                    UPDATE users SET inactiveWarning = ${now - week - 1000}
                    WHERE id = '${userRefreshed!.id}'
                `);
                continue;
            }
            await db.get(`UPDATE users SET inactiveWarning = ${now} WHERE id = '${userRefreshed!.id}'`);
            if (userRefreshed!.bannedReason !== null) {
                continue;
            }
            await mailer(userRefreshed!.email, 'inactivityWarning', translator, {
                username: userRefreshed!.username,
            });
        } catch (error) {
            Sentry.captureException(error);
        }
        await sleep(3000);
    }
}

async function removeWarned(db: Database) {
    console.log('--- Fetching ids to remove ---');

    const users = await db.all<Pick<User, 'id' | 'username' | 'email'>>(`
        SELECT u.id, u.username, u.email
        FROM users u
        WHERE (
            (
                u.id NOT IN (SELECT DISTINCT p.userId FROM profiles p)
                AND lastActive < ${now - month}
                AND inactiveWarning IS NOT NULL
                AND inactiveWarning < ${now - week}
            )
            OR (
                bannedReason IS NOT NULL
                AND inactiveWarning IS NOT NULL
                AND inactiveWarning < ${now - twoWeeks}
            )
            )
    `);

    console.log(users.length);

    for (const user of users) {
        console.log('remove', user);
        if (!execute) {
            continue;
        }
        await db.get(`DELETE FROM users WHERE id = '${user.id}'`);
    }
}

async function cleanup() {
    const db = await dbConnection();

    await db.get('PRAGMA foreign_keys = ON');

    await warnInactive(db);
    await removeWarned(db);
}

cleanup();