misuzu/tools/cron

#!/usr/bin/env php
<?php
namespace Misuzu;

use stdClass;
use Exception;

require_once __DIR__ . '/../misuzu.php';

$schedTasks = [];
$runSlow = false; // by what metric? i made it up.

function msz_sched_task_sql(string $name, bool $slow, string $command): void {
    global $schedTasks, $runSlow;

    if($slow && !$runSlow)
        return;

    $schedTasks[] = $task = new stdClass;
    $task->name = $name;
    $task->type = 'sql';
    $task->command = $command;
}

function msz_sched_task_func(string $name, bool $slow, callable $command): void {
    global $schedTasks, $runSlow;

    if($slow && !$runSlow)
        return;

    $schedTasks[] = $task = new stdClass;
    $task->name = $name;
    $task->type = 'func';
    $task->command = $command;
}

for($i = 1; $i < count($argv); ++$i) {
    $argvi = $argv[$i];
    if($argvi === 'slow')
        $runSlow = true;
    else die('Unknown argument specified.' . PHP_EOL);
}

if($runSlow)
    echo 'Also running slow tasks!' . PHP_EOL;
else
    echo 'Only running quick tasks!' . PHP_EOL;
echo PHP_EOL;

msz_sched_task_sql('Ensure main role exists.', true,
    'INSERT IGNORE INTO msz_roles (role_id, role_name, role_hierarchy, role_colour, role_description, role_created) VALUES (1, "Member", 1, 1073741824, NULL, NOW())');

msz_sched_task_sql('Ensure all users have the main role.', true,
    'INSERT INTO msz_users_roles (user_id, role_id) SELECT user_id, 1 FROM msz_users AS u WHERE NOT EXISTS (SELECT 1 FROM msz_users_roles AS ur WHERE role_id = 1 AND u.user_id = ur.user_id)');

msz_sched_task_sql('Ensure all display_role field values are correct with msz_users_roles.', true,
    'UPDATE msz_users AS u SET display_role = (SELECT ur.role_id FROM msz_users_roles AS ur LEFT JOIN msz_roles AS r ON r.role_id = ur.role_id WHERE ur.user_id = u.user_id ORDER BY role_hierarchy DESC LIMIT 1) WHERE NOT EXISTS (SELECT 1 FROM msz_users_roles AS ur WHERE ur.role_id = u.display_role AND ur.user_id = u.user_id)');

msz_sched_task_sql('Remove expired sessions.', false,
    'DELETE FROM msz_sessions WHERE session_expires < NOW()');

msz_sched_task_sql('Remove old password reset tokens.', false,
    'DELETE FROM msz_users_password_resets WHERE reset_requested < NOW() - INTERVAL 1 WEEK');

msz_sched_task_sql('Remove old login attempt logs.', false,
    'DELETE FROM msz_login_attempts WHERE attempt_created < NOW() - INTERVAL 1 MONTH');

msz_sched_task_sql('Remove old audit log entries.', false,
    'DELETE FROM msz_audit_log WHERE log_created < NOW() - INTERVAL 3 MONTH');

msz_sched_task_sql('Remove stale forum tracking entries.', false,
    'DELETE tt FROM msz_forum_topics_track AS tt LEFT JOIN msz_forum_topics AS t ON t.topic_id = tt.topic_id WHERE t.topic_bumped < NOW() - INTERVAL 1 MONTH');

msz_sched_task_sql('Synchronise forum_id.', true,
    'UPDATE msz_forum_posts AS p INNER JOIN msz_forum_topics AS t ON t.topic_id = p.topic_id SET p.forum_id = t.forum_id');

msz_sched_task_func('Recount forum topics and posts.', true, function() use ($msz) {
    $msz->getForum()->syncForumCounters();
});

msz_sched_task_sql('Clean up expired 2fa tokens.', false,
    'DELETE FROM msz_auth_tfa WHERE tfa_created < NOW() - INTERVAL 15 MINUTE');

// make sure this one remains last
msz_sched_task_func('Resync statistics counters.', true, function() use ($msz) {
    $dbConn = $msz->getDbConn();
    $counters = $msz->getCounters();

    $stats = [
        'users:total'                  => 'SELECT COUNT(*) FROM msz_users',
        'users:active'                 => 'SELECT COUNT(*) FROM msz_users WHERE user_active IS NOT NULL AND user_deleted IS NULL',
        'users:inactive'               => 'SELECT COUNT(*) FROM msz_users WHERE user_active IS NULL OR user_deleted IS NOT NULL',
        'users:online:recent'          => 'SELECT COUNT(*) FROM msz_users WHERE user_active >= NOW() - INTERVAL 1 HOUR', // used to be 5 minutes, but this script runs every hour soooo
        'users:online:today'           => 'SELECT COUNT(*) FROM msz_users WHERE user_active >= NOW() - INTERVAL 24 HOUR',
        'auditlogs:total'              => 'SELECT COUNT(*) FROM msz_audit_log',
        'changelog:changes:total'      => 'SELECT COUNT(*) FROM msz_changelog_changes',
        'changelog:tags:total'         => 'SELECT COUNT(*) FROM msz_changelog_tags',
        'comments:cats:total'          => 'SELECT COUNT(*) FROM msz_comments_categories',
        'comments:cats:locked'         => 'SELECT COUNT(*) FROM msz_comments_categories WHERE category_locked IS NOT NULL',
        'comments:posts:total'         => 'SELECT COUNT(*) FROM msz_comments_posts',
        'comments:posts:visible'       => 'SELECT COUNT(*) FROM msz_comments_posts WHERE comment_deleted IS NULL',
        'comments:posts:deleted'       => 'SELECT COUNT(*) FROM msz_comments_posts WHERE comment_deleted IS NOT NULL',
        'comments:posts:replies'       => 'SELECT COUNT(*) FROM msz_comments_posts WHERE comment_reply_to IS NOT NULL',
        'comments:posts:pinned'        => 'SELECT COUNT(*) FROM msz_comments_posts WHERE comment_pinned IS NOT NULL',
        'comments:posts:edited'        => 'SELECT COUNT(*) FROM msz_comments_posts WHERE comment_edited IS NOT NULL',
        'comments:votes:likes'         => 'SELECT COUNT(*) FROM msz_comments_votes WHERE comment_vote > 0',
        'comments:votes:dislikes'      => 'SELECT COUNT(*) FROM msz_comments_votes WHERE comment_vote < 0',
        'forum:posts:total'            => 'SELECT COUNT(*) FROM msz_forum_posts',
        'forum:posts:visible'          => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_deleted IS NULL',
        'forum:posts:deleted'          => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_deleted IS NOT NULL',
        'forum:posts:edited'           => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_edited IS NOT NULL',
        'forum:posts:parse:plain'      => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_parse = 0',
        'forum:posts:parse:bbcode'     => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_parse = 1',
        'forum:posts:parse:markdown'   => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_parse = 2',
        'forum:posts:signature'        => 'SELECT COUNT(*) FROM msz_forum_posts WHERE post_display_signature <> 0',
        'forum:topics:total'           => 'SELECT COUNT(*) FROM msz_forum_topics',
        'forum:topics:type:normal'     => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_type = 0',
        'forum:topics:type:pinned'     => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_type = 1',
        'forum:topics:type:announce'   => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_type = 2',
        'forum:topics:type:global'     => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_type = 3',
        'forum:topics:visible'         => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_deleted IS NULL',
        'forum:topics:deleted'         => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_deleted IS NOT NULL',
        'forum:topics:locked'          => 'SELECT COUNT(*) FROM msz_forum_topics WHERE topic_locked IS NOT NULL',
        'auth:attempts:total'          => 'SELECT COUNT(*) FROM msz_login_attempts',
        'auth:attempts:failed'         => 'SELECT COUNT(*) FROM msz_login_attempts WHERE attempt_success = 0',
        'auth:sessions:total'          => 'SELECT COUNT(*) FROM msz_sessions',
        'auth:tfasessions:total'       => 'SELECT COUNT(*) FROM msz_auth_tfa',
        'auth:recovery:total'          => 'SELECT COUNT(*) FROM msz_users_password_resets',
        'users:modnotes:total'         => 'SELECT COUNT(*) FROM msz_users_modnotes',
        'users:warnings:total'         => 'SELECT COUNT(*) FROM msz_users_warnings',
        'users:warnings:visible'       => 'SELECT COUNT(*) FROM msz_users_warnings WHERE warn_created > NOW() - INTERVAL 90 DAY',
        'users:bans:total'             => 'SELECT COUNT(*) FROM msz_users_bans',
        'users:bans:active'            => 'SELECT COUNT(*) FROM msz_users_bans WHERE ban_expires IS NULL OR ban_expires > NOW()',
    ];

    foreach($stats as $name => $query) {
        $result = $dbConn->query($query);
        $counters->set($name, $result->next() ? $result->getInteger(0) : 0);
    }
});

echo 'Running ' . count($schedTasks) . ' tasks...' . PHP_EOL;

$dbConn = $msz->getDbConn();

foreach($schedTasks as $task) {
    echo '=> ' . $task->name . PHP_EOL;
    $success = true;

    try {
        switch($task->type) {
            case 'sql':
                $affected = $dbConn->execute($task->command);
                echo $affected . ' rows affected. ' . PHP_EOL;
                break;

            case 'func':
                $result = ($task->command)();
                if(is_bool($result))
                    $success = $result;
                break;

            default:
                $success = false;
                echo 'Unknown task type.' . PHP_EOL;
                break;
        }
    } catch(Exception $ex) {
        $success = false;
        echo (string)$ex;
    } finally {
        if($success)
            echo 'Done!';
        else
            echo '!!! FAILED !!!';
        echo PHP_EOL;
    }

    echo PHP_EOL;
}

echo 'Took ' . number_format(microtime(true) - MSZ_STARTUP, 5) . 'ms.' . PHP_EOL;