xamxam/scripts/backup-watchdog.php

#!/usr/bin/env php
<?php
/**
 * backup-watchdog.php — alert by email when SQLite backups go stale.
 *
 * Checks the newest db-*.db.gz in /var/backups/xamxam and, if it is older
 * than the staleness threshold, sends an alert via the app's SMTP relay to
 * xamxam@erg.be (or the configured notify_email). A state file prevents
 * repeated alerts for the same incident: an email is only sent on the
 * transition fresh → stale, not on every run while backups remain down.
 *
 * Usage:
 *   php scripts/backup-watchdog.php                 # 2h staleness threshold
 *   STALE_AFTER_SECONDS=86400 php scripts/backup-watchdog.php
 *
 * Expected to run from cron (after each backup cron), e.g.:
 *   15 * * * * www-data php /usr/local/bin/backup-watchdog.php >> /var/log/xamxam-backup-$(date +\%Y-\%m-\%d).log 2>&1
 *
 * Exit codes: 0 ok (or alert sent), 1 error.
 */

declare(strict_types=1);

// Resolve APP_ROOT robustly — same convention as the other CLI scripts here.
$prodRoot = '/var/www/xamxam';
if (is_dir($prodRoot . '/src') && is_file($prodRoot . '/src/Database.php')) {
    define('APP_ROOT', $prodRoot);
} else {
    define('APP_ROOT', dirname(__DIR__) . '/app');
}

const BACKUP_DIR = '/var/backups/xamxam';
const STATE_FILE = BACKUP_DIR . '/.last_backup_alert';
const ALERT_EMAIL = 'xamxam@erg.be';
const WEBDAV_BASE = 'https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK';
const REMOTE_PREFIX = 'xamxam-db-';
const REMOTE_STALE_SECONDS = 172800; // 48h — daily sync, allow one missed day

// Autoload dependencies (PHPMailer, etc.) from composer.
$autoloads = [
    APP_ROOT . '/../vendor/autoload.php',   // repo root / vendor
    APP_ROOT . '/vendor/autoload.php',      // app / vendor
];
foreach ($autoloads as $a) {
    if (is_file($a)) {
        require_once $a;
        break;
    }
}

// Read-only probe: do NOT run schema migrations against the live DB just to
// check backup freshness. If DatabaseMigrations isn't loaded, no-op it so the
// Database constructor only opens a PDO connection.
if (!class_exists('DatabaseMigrations', false)) {
    class DatabaseMigrations
    {
        public function run(): void {}
    }
}

require_once APP_ROOT . '/src/Crypto.php';
require_once APP_ROOT . '/src/Database.php';
require_once APP_ROOT . '/src/SmtpRelay.php';

// Staleness threshold: default 2 hours (matches the admin system-page status).
$staleAfterSeconds = (int) (getenv('STALE_AFTER_SECONDS') ?: 7200);
if ($staleAfterSeconds < 60) {
    $staleAfterSeconds = 7200;
}

$newestMtime = 0;
$newestName  = null;
$files = glob(BACKUP_DIR . '/db-*.db.gz') ?: [];
foreach ($files as $f) {
    $m = @filemtime($f);
    if ($m !== false && $m > $newestMtime) {
        $newestMtime = $m;
        $newestName  = $f;
    }
}

$now = time();
$age = $newestMtime > 0 ? ($now - $newestMtime) : null;
$localDown = ($age === null) || ($age > $staleAfterSeconds);

// Remote (Nextcloud) staleness — only relevant if we have credentials.
// PROPFIND the backup folder and find the newest snapshot's last-modified time.
$remoteDown  = false;
$remoteStatus = '';

try {
    $db = new Database();
    $smtp = SmtpRelay::getSettings($db);
} catch (Throwable $e) {
    // Cannot read credentials — skip the remote check rather than fail the run.
    $db = null;
    $smtp = null;
}

if ($db !== null && !empty($smtp['username']) && !empty($smtp['password'])) {
    $remote = remoteFreshness($smtp['username'], $smtp['password']);
    if ($remote['oldest_age'] === null) {
        $remoteDown = true;
        $remoteStatus = 'aucune copie distante trouvée dans XAMXAM-BCK';
    } elseif ($remote['oldest_age'] > REMOTE_STALE_SECONDS) {
        $remoteDown = true;
        $hours = (int) round($remote['oldest_age'] / 3600.0);
        $remoteStatus = "la copie Nextcloud la plus récente date d'il y a {$hours} h";
    }
} else {
    // No creds — can't check; don't alarm about remote.
    $remoteDown = false;
}

$isDown = $localDown || $remoteDown;

if (!$isDown) {
    // Backups are fresh. Clear any prior alert marker so a future outage
    // triggers a fresh email.
    if (is_file(STATE_FILE)) {
        @unlink(STATE_FILE);
    }
    exit(0);
}

// Stale/absent — avoid flooding: only email once per incident.
if (is_file(STATE_FILE)) {
    exit(0);
}

// Compose the alert.
$issues = [];
if ($localDown) {
    if ($age === null) {
        $issues[] = 'aucune sauvegarde locale trouvée dans ' . BACKUP_DIR;
    } else {
        $hours = (int) round($age / 3600.0);
        $issues[] = "sauvegarde locale la plus récente il y a {$hours} h"
            . ($newestName ? ' (' . basename($newestName) . ')' : '');
    }
}
if ($remoteDown) {
    $issues[] = $remoteStatus;
}
$status = implode(' ; ', $issues);

$subject = '⚠ XAMXAM — sauvegarde SQLite inactive';
$remoteNote = $remoteDown
    ? "\n    <p>Copie distante (Nextcloud) : <strong>en retard</strong> — {$remoteStatus}.</p>"
    : '';
$body = <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family:system-ui,Arial,sans-serif;line-height:1.6;color:#333">
  <div style="max-width:600px;margin:0 auto;padding:20px">
    <h2 style="color:#c53030">Sauvegarde SQLite inactive</h2>
    <p>Le système de sauvegarde XAMXAM semble en panne : {$status}.</p>{$remoteNote}
    <p>Veuillez vérifier le cron de sauvegarde et les journaux :</p>
    <pre style="background:#f7fafc;padding:12px;border-left:4px solid #c53030;overflow-x:auto">tail -50 /var/log/xamxam-backup-$(date +%Y-%m-%d).log
ls -lth /var/backups/xamxam/</pre>
    <p style="margin-top:20px;color:#666;font-size:.9em">
      Cet e-mail est envoyé automatiquement par le moniteur de sauvegarde XAMXAM.
    </p>
  </div>
</body>
</html>
HTML;

$plain = "Sauvegarde SQLite inactive — {$status}.\n\n"
       . "Vérifiez : tail -50 /var/log/xamxam-backup-$(date +%Y-%m-%d).log\n"
       . "et : ls -lth /var/backups/xamxam/\n";

try {
    if ($db === null) {
        // Cannot read SMTP credentials to send the alert — log and exit.
        error_log('[backup-watchdog] Backup stale but SMTP creds unreadable; alert not sent. ' . $status);
        exit(1);
    }

    // Prefer the configured admin notification address; fall back to the
    // hard-coded XAMXAM account so this alert is never silently dropped.
    $to = SmtpRelay::getNotifyEmail($db);
    if ($to === '') {
        $to = ALERT_EMAIL;
    }

    SmtpRelay::send($db, $to, $subject, $body, $plain);

    // Persist the alert marker only after a successful send, so a transient
    // SMTP failure does not suppress the next run's alert.
    @file_put_contents(STATE_FILE, $now . "\n");
    error_log('[backup-watchdog] Alert sent to ' . $to . ' — ' . $status);
    exit(0);
} catch (Throwable $e) {
    error_log('[backup-watchdog] Alert delivery failed: ' . $e->getMessage());
    exit(1);
}

/**
 * PROPFIND the Nextcloud backup folder and return the newest snapshot's age.
 *
 * @return array{oldest_age:?int, count:int}  oldest_age in seconds (null if none)
 */
function remoteFreshness(string $user, string $pass): array
{
    $ch = curl_init(rtrim(WEBDAV_BASE, '/') . '/');
    curl_setopt_array($ch, [
        CURLOPT_CUSTOMREQUEST  => 'PROPFIND',
        CURLOPT_USERPWD        => $user . ':' . $pass,
        CURLOPT_HTTPAUTH       => CURLAUTH_BASIC,
        CURLOPT_HTTPHEADER     => ['Depth: 1'],
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 30,
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_SSL_VERIFYHOST => 2,
    ]);

    $body = curl_exec($ch);
    curl_close($ch);

    if ($body === false) {
        return ['oldest_age' => null, 'count' => 0]; // unreachable → treat as stale
    }

    // Find the newest last-modified timestamp among our snapshot files.
    $newest = 0;
    $count = 0;
    // Match <d:href>…</d:href> and adjacent lastmodified, or fall back to
    // parsing <d:getlastmodified> entries in order.
    preg_match_all(
        '/<d:href>([^<]*' . preg_quote(REMOTE_PREFIX, '/') . '[^<]*\.db\.gz)<\/d:href>.*?<d:getlastmodified>([^<]+)<\/d:getlastmodified>/is',
        $body,
        $m,
        PREG_SET_ORDER
    );
    foreach ($m as $entry) {
        $href = rawurldecode(basename(rtrim($entry[1], '/')));
        if (!str_starts_with($href, REMOTE_PREFIX) || !str_ends_with($href, '.db.gz')) {
            continue;
        }
        $count++;
        $ts = strtotime($entry[2]);
        if ($ts !== false && $ts > $newest) {
            $newest = $ts;
        }
    }

    if ($newest === 0) {
        return ['oldest_age' => null, 'count' => 0]; // none found
    }

    return ['oldest_age' => time() - $newest, 'count' => $count];
}