console

<?php
/**
 * CONSOLE — the command-line tool. Run from the project root:
 *
 *   php console db:migrate          run any new migrations
 *   php console db:install          migrate, then load the demo data
 *   php console db:install --fresh  DROP everything, then migrate + seed  (DEV ONLY)
 *   php console db:seed             load the demo data
 *   php console db:cleanup          prune expired tokens + old login attempts
 *   php console monitor:run [--due] run checks (all active, or only those due)
 *
 * Migrations are the plain .sql files in database/migrations/, run in filename
 * order. Each one that runs is recorded in a `Migration` table, so db:migrate
 * only ever runs the new ones. To change the schema later, add the next
 * numbered file (e.g. 009_add_column.sql) and run db:migrate.
 */

require __DIR__ . '/bootstrap.php';

$command = $argv[1] ?? 'help';
$flags   = array_slice($argv, 2);

try {
    match ($command) {
        'db:migrate' => migrate(),
        'db:install' => install(in_array('--fresh', $flags, true)),
        'db:seed'    => seed(),
        'db:cleanup' => cleanup(),
        'demo:reset' => demoReset(),
        'monitor:run'     => monitorRun($flags),
        'monitor:enqueue' => monitorEnqueue($flags),
        'monitor:work'    => monitorWork($flags),
        'monitor:queue'   => monitorQueue(),
        default      => help(),
    };
} catch (Throwable $e) {
    fwrite(STDERR, "Error: " . $e->getMessage() . PHP_EOL);
    exit(1);
}

// --- commands --------------------------------------------------------------

function migrate(): void
{
    db()->run(
        'CREATE TABLE IF NOT EXISTS `Migration` (
            `PK_MigrationID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `Filename` VARCHAR(190) NOT NULL,
            `RanAt` DATETIME NOT NULL,
            PRIMARY KEY (`PK_MigrationID`),
            UNIQUE KEY `uq_migration` (`Filename`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $done = array_column(db()->all('SELECT `Filename` FROM `Migration`'), 'Filename');
    $files = glob(BASE_PATH . '/database/migrations/*.sql');
    sort($files);

    $ran = 0;
    foreach ($files as $file) {
        $name = basename($file);
        if (in_array($name, $done, true)) {
            continue;
        }
        db()->run('SET FOREIGN_KEY_CHECKS = 1'); // be explicit
        db()->run('INSERT INTO `Migration` (`Filename`, `RanAt`) VALUES (?, ?)', [$name, gmdate('Y-m-d H:i:s')]);
        // A migration file may contain several statements; execRaw allows that.
        db()->execRaw(file_get_contents($file));
        echo "  migrated  {$name}" . PHP_EOL;
        $ran++;
    }
    echo $ran === 0 ? "Nothing to migrate (up to date)." . PHP_EOL : "Done: {$ran} migration(s)." . PHP_EOL;
}

function install(bool $fresh): void
{
    if ($fresh) {
        echo "Dropping all tables (--fresh)..." . PHP_EOL;
        dropAllTables();
    }
    migrate();
    seed();
}

function seed(): void
{
    // The seed file has multiple INSERT statements; execRaw runs them all
    // (a prepared statement would reject more than one statement at once).
    db()->execRaw(file_get_contents(BASE_PATH . '/database/seed.sql'));
    echo "Seeded demo data." . PHP_EOL;
}

function cleanup(): void
{
    $now = gmdate('Y-m-d H:i:s');
    $old = gmdate('Y-m-d H:i:s', time() - 60 * 60 * 24 * 30); // 30 days ago

    db()->run('DELETE FROM `PasswordReset` WHERE `ExpiresAt` < ?', [$now]);
    db()->run('DELETE FROM `EmailVerification` WHERE `ExpiresAt` < ?', [$now]);
    db()->run('DELETE FROM `RememberToken` WHERE `ExpiresAt` < ?', [$now]);
    db()->run('DELETE FROM `LoginAttempt` WHERE `CreatedAt` < ?', [$old]);
    db()->run("DELETE FROM `ScanJob` WHERE `Status` = 'failed' AND `CreatedAt` < ?", [$old]);
    echo "Cleaned up expired tokens, old login attempts, and dead scan jobs." . PHP_EOL;
}

/** demo:reset — restore the shared demo account to its canonical targets + rescan. */
function demoReset(): void
{
    (new DemoService())->reset();
    echo "Demo account reset to its canonical targets." . PHP_EOL;
}

function monitorRun(array $flags = []): void
{
    // Scheduled-scan entry point. A thin CLI wrapper over the SAME path the
    // "Scan all" button uses: MonitorService::runChecks() loads active targets
    // across ALL users (a system job, not user-scoped), runs the right checker,
    // writes a CheckResult row and refreshes the Last* snapshot. THEN fires email
    // alerts via AlertDispatcher (only here — never the dashboard scan). If the
    // service throws, we let it bubble to the top-level try/catch, which exits
    // non-zero so a scheduler can detect it.
    //
    //   monitor:run         force-check EVERY active target (manual/full run)
    //   monitor:run --due   check only targets not checked within
    //                       config('scan_interval_minutes') — the cron-friendly mode
    //
    // Every invocation records one MonitorRun row (incl. a "nothing due" run), so
    // you can confirm the scheduler is firing and see what each run did.
    $startedAt = gmdate('Y-m-d H:i:s');
    $t0        = microtime(true);

    $due      = in_array('--due', $flags, true);
    $mode     = $due ? 'due' : 'full';
    $dueCount = null; // stays null for a full run (no "due" selection happened)
    $ids      = null; // null = every active target (full mode)

    if ($due) {
        $intervalMin = max(1, (int) config('scan_interval_minutes', 720));
        $cutoff = gmdate('Y-m-d H:i:s', time() - $intervalMin * 60);

        // Due = active AND (never checked OR last check older than the interval).
        $rows = db()->all(
            'SELECT `PK_MonitoredTargetID`
               FROM `MonitoredTarget`
              WHERE `IsActive` = 1
                AND (`LastCheckedAt` IS NULL OR `LastCheckedAt` < ?)
           ORDER BY `PK_MonitoredTargetID`',
            [$cutoff],
        );
        $ids      = array_map(static fn(array $r): int => (int) $r['PK_MonitoredTargetID'], $rows);
        $dueCount = count($ids);
    }

    // Create the run row UP FRONT (counts zeroed) so every check this run writes
    // can link to it via CheckResult.FK_MonitorRunID — that's what powers the
    // admin per-run drill-down. We fill in the tallies once the run finishes.
    $runId = db()->insert('MonitorRun', [
        'StartedAt'    => $startedAt,
        'Mode'         => $mode,
        'DueCount'     => $dueCount,
        'CheckedCount' => 0,
        'OkCount'      => 0,
        'FailedCount'  => 0,
        'DurationMs'   => 0,
    ]);

    if ($due && $ids === []) {
        echo "Due run (interval {$intervalMin}m): nothing due — all active targets are fresh." . PHP_EOL;
        $results = [];
    } else {
        if ($due) {
            echo "Due run (interval {$intervalMin}m): {$dueCount} target(s) due." . PHP_EOL;
        }
        $results = (new MonitorService())->runChecks($ids, true, 'scheduled', $runId);
    }

    $total = count($results);
    $ok    = 0;
    foreach ($results as $r) {
        if (!empty($r['ok'])) {
            $ok++;
        }
    }
    $failed = $total - $ok;

    // Alerts fire ONLY from the scheduled run (here), never from the dashboard
    // scan. AlertDispatcher emails verified users on expiry tiers + new failures.
    $alertsSent = ($results !== [] && config('alerts_enabled', true))
        ? (new AlertDispatcher())->dispatch($results)
        : 0;

    // Backfill the run row's final tallies.
    db()->run(
        'UPDATE `MonitorRun` SET `CheckedCount` = ?, `OkCount` = ?, `FailedCount` = ?, `DurationMs` = ?
          WHERE `PK_MonitorRunID` = ?',
        [$total, $ok, $failed, (int) round((microtime(true) - $t0) * 1000), $runId],
    );

    $alertNote = $alertsSent > 0 ? " Sent {$alertsSent} alert(s)." : '';
    echo "Checked {$total} target(s): {$ok} ok, {$failed} failed.{$alertNote}" . PHP_EOL;
}

// --- Scalable scanning: queue + workers (see docs/scheduling.md) ------------
// monitor:run is the simple all-in-one for small scale. At scale, split it:
//   monitor:enqueue --due   (fast) finds due targets and queues them
//   monitor:work            run MANY of these in parallel; each claims a batch
// Concurrency = number of monitor:work processes. Each worker is sequential
// internally but they don't collide (atomic claim-by-token).

/** Pull a single `--name=value` flag, or a default. */
function flagValue(array $flags, string $name, string $default): string
{
    foreach ($flags as $f) {
        if (str_starts_with($f, $name . '=')) {
            return substr($f, strlen($name) + 1);
        }
    }
    return $default;
}

/** monitor:enqueue [--due] — queue active (or due) targets not already queued. */
function monitorEnqueue(array $flags): void
{
    $now    = gmdate('Y-m-d H:i:s');
    $due    = in_array('--due', $flags, true);
    $where  = 't.`IsActive` = 1';
    $params = [$now];

    if ($due) {
        $intervalMin = max(1, (int) config('scan_interval_minutes', 720));
        $where  .= ' AND (t.`LastCheckedAt` IS NULL OR t.`LastCheckedAt` < ?)';
        $params[] = gmdate('Y-m-d H:i:s', time() - $intervalMin * 60);
    }

    // One statement enqueues every eligible target that isn't already queued.
    $stmt = db()->run(
        "INSERT INTO `ScanJob` (`FK_MonitoredTargetID`, `Status`, `CreatedAt`)
         SELECT t.`PK_MonitoredTargetID`, 'pending', ?
           FROM `MonitoredTarget` t
          WHERE {$where}
            AND NOT EXISTS (
                SELECT 1 FROM `ScanJob` j
                 WHERE j.`FK_MonitoredTargetID` = t.`PK_MonitoredTargetID`
                   AND j.`Status` IN ('pending', 'running')
            )",
        $params,
    );

    echo 'Enqueued ' . $stmt->rowCount() . ' target(s)' . ($due ? ' (due mode)' : '') . '.' . PHP_EOL;
}

/**
 * monitor:work [--batch=N] [--max=N] [--seconds=N] — claim + run queued jobs.
 * Runs until the queue is empty (or the caps are hit), then exits — so it's safe
 * to schedule frequently, and safe to run many copies at once.
 */
function monitorWork(array $flags): void
{
    $batch    = max(1, (int) flagValue($flags, '--batch', '10'));
    $maxJobs  = max(1, (int) flagValue($flags, '--max', '500'));
    $maxSecs  = max(1, (int) flagValue($flags, '--seconds', '50'));
    $worker   = bin2hex(random_bytes(8));               // unique claim token
    $t0       = microtime(true);

    // Park poison jobs (repeatedly failed) so they stop being reclaimed.
    db()->run(
        "UPDATE `ScanJob` SET `Status` = 'failed'
          WHERE `Status` = 'running' AND `Attempts` >= 5 AND `ClaimedAt` < ?",
        [gmdate('Y-m-d H:i:s', time() - 300)],
    );

    $svc = new MonitorService();
    $processed = 0; $ok = 0; $failed = 0; $alerts = 0;

    while ($processed < $maxJobs && (microtime(true) - $t0) < $maxSecs) {
        $lim   = (int) min($batch, $maxJobs - $processed);
        $stale = gmdate('Y-m-d H:i:s', time() - 300); // reclaim jobs stuck >5 min

        // Atomic claim: stamp our token on up to $lim pending (or stale) jobs.
        db()->run(
            "UPDATE `ScanJob`
                SET `Status` = 'running', `ClaimedBy` = ?, `ClaimedAt` = ?, `Attempts` = `Attempts` + 1
              WHERE (`Status` = 'pending' OR (`Status` = 'running' AND `ClaimedAt` < ?))
                AND `Attempts` < 5
              ORDER BY `PK_ScanJobID`
              LIMIT {$lim}",
            [$worker, gmdate('Y-m-d H:i:s'), $stale],
        );

        $jobs = db()->all(
            "SELECT `FK_MonitoredTargetID` FROM `ScanJob` WHERE `ClaimedBy` = ? AND `Status` = 'running'",
            [$worker],
        );
        if ($jobs === []) {
            break; // nothing left to do
        }

        try {
            $ids     = array_map(static fn ($j): int => (int) $j['FK_MonitoredTargetID'], $jobs);
            $results = $svc->runChecks($ids);
            foreach ($results as $r) {
                $processed++;
                empty($r['ok']) ? $failed++ : $ok++;
            }
            if (config('alerts_enabled', true) && $results !== []) {
                $alerts += (new AlertDispatcher())->dispatch($results);
            }
            db()->run("DELETE FROM `ScanJob` WHERE `ClaimedBy` = ? AND `Status` = 'running'", [$worker]);
        } catch (Throwable $e) {
            // Leave the claimed jobs to be reclaimed when they go stale; keep going.
            fwrite(STDERR, "batch error: {$e->getMessage()}" . PHP_EOL);
            break;
        }
    }

    $ms = (int) round((microtime(true) - $t0) * 1000);
    $alertNote = $alerts > 0 ? " Sent {$alerts} alert(s)." : '';
    echo "Worker {$worker}: processed {$processed} ({$ok} ok, {$failed} failed) in {$ms}ms.{$alertNote}" . PHP_EOL;
}

/** monitor:queue — quick queue depth (pending / running / failed). */
function monitorQueue(): void
{
    $r = db()->first(
        "SELECT SUM(`Status` = 'pending') AS p, SUM(`Status` = 'running') AS r, SUM(`Status` = 'failed') AS f
           FROM `ScanJob`",
    );
    printf("Queue — pending: %d, running: %d, failed: %d" . PHP_EOL,
        (int) ($r['p'] ?? 0), (int) ($r['r'] ?? 0), (int) ($r['f'] ?? 0));
}

function dropAllTables(): void
{
    db()->run('SET FOREIGN_KEY_CHECKS = 0');
    foreach (db()->all('SHOW TABLES') as $row) {
        $table = array_values($row)[0];
        db()->run("DROP TABLE IF EXISTS `{$table}`");
    }
    db()->run('SET FOREIGN_KEY_CHECKS = 1');
}

function help(): void
{
    echo <<<TXT
    Usage: php console <command>

      db:migrate          run any new migrations
      db:install          migrate, then load demo data
      db:install --fresh  DROP all tables, then migrate + seed (DEV ONLY)
      db:seed             load demo data
      db:cleanup          prune expired tokens + old login attempts (good for cron)
      demo:reset          restore the shared demo account to its canonical targets (good for cron)
      monitor:run         run checks now on ALL active targets (manual/full run)
      monitor:run --due   check only targets due per scan_interval_minutes (good for cron)

      monitor:enqueue [--due]   queue targets for the worker pool (scales out)
      monitor:work [--batch=N] [--max=N] [--seconds=N]
                                claim + run queued jobs; run MANY in parallel
      monitor:queue             show queue depth (pending / running / failed)

    TXT;
}