tinker_tickets/scripts/cleanup_orphan_uploads.php

#!/usr/bin/env php
<?php

/**
 * Orphan Upload Cleanup
 *
 * Removes files under uploads/<ticketId>/ that have NO matching row in
 * ticket_attachments (e.g. leftovers from a failed DB insert). Intended to be
 * run from cron:
 *   0 4 * * * /usr/bin/php /path/to/scripts/cleanup_orphan_uploads.php >> /var/log/orphan_uploads.log 2>&1
 *
 * SAFETY:
 *   - Only files older than a grace period (GRACE_SECONDS, default 24h) are
 *     considered, so a freshly written file whose DB row has not been inserted
 *     yet (in-flight upload) is never deleted.
 *   - Only 9-digit ticket directories are scanned. uploads/avatars/ (and any
 *     other non-ticket directory) is skipped entirely.
 *   - A file is deleted only when no ticket_attachments row references its
 *     stored filename (looked up with a prepared statement).
 *
 * Usage:
 *   php cleanup_orphan_uploads.php            # delete orphaned files past grace period
 *   php cleanup_orphan_uploads.php --dry-run  # report only, delete nothing
 */

// Prevent web access
if (php_sapi_name() !== 'cli') {
    http_response_code(403);
    exit('CLI access only');
}

require_once dirname(__DIR__) . '/config/config.php';
require_once dirname(__DIR__) . '/helpers/Database.php';

/** Files younger than this (seconds) are never touched — protects in-flight uploads. */
const GRACE_SECONDS = 86400;

$dryRun = in_array('--dry-run', $argv, true);

function logMessage($message)
{
    echo '[' . date('Y-m-d H:i:s') . '] ' . $message . "\n";
}

$uploadDir = $GLOBALS['config']['UPLOAD_DIR'] ?? (dirname(__DIR__) . '/uploads');
$uploadRoot = realpath($uploadDir);

if ($uploadRoot === false || !is_dir($uploadRoot)) {
    logMessage("Upload directory not found: {$uploadDir}");
    exit(0);
}

logMessage('Starting orphan upload cleanup' . ($dryRun ? ' (DRY RUN)' : ''));

try {
    $conn = Database::getConnection();
} catch (Exception $e) {
    logMessage('FATAL ERROR: could not connect to database: ' . $e->getMessage());
    exit(1);
}

// Prepared lookup: does any attachment row reference this stored filename?
// Stored filenames are globally unique (uniqid), so filename alone is sufficient
// and safe — a match in any ticket means the file is a real attachment.
$lookup = $conn->prepare('SELECT 1 FROM ticket_attachments WHERE filename = ? LIMIT 1');
if ($lookup === false) {
    logMessage('FATAL ERROR: could not prepare lookup statement: ' . $conn->error);
    exit(1);
}

$now = time();
$scanned = 0;
$orphaned = 0;
$deleted = 0;
$skippedTooNew = 0;
$errors = 0;

foreach (new DirectoryIterator($uploadRoot) as $entry) {
    if ($entry->isDot() || !$entry->isDir() || $entry->isLink()) {
        continue;
    }

    // Ticket directories are 9-digit ticket IDs. Skip avatars/ and anything else.
    $dirName = $entry->getFilename();
    if (!preg_match('/^\d{9}$/', $dirName)) {
        continue;
    }

    foreach (new DirectoryIterator($entry->getPathname()) as $file) {
        if ($file->isDot() || !$file->isFile() || $file->isLink()) {
            continue;
        }

        $scanned++;
        $filename = $file->getFilename();

        // Never touch files younger than the grace period (in-flight uploads).
        $age = $now - $file->getMTime();
        if ($age < GRACE_SECONDS) {
            $skippedTooNew++;
            continue;
        }

        // Keep the file if any attachment row references it.
        $lookup->bind_param('s', $filename);
        $lookup->execute();
        $hasRow = $lookup->get_result()->num_rows > 0;

        if ($hasRow) {
            continue;
        }

        $orphaned++;
        $path = $file->getPathname();

        if ($dryRun) {
            logMessage("WOULD DELETE orphan: {$dirName}/{$filename}");
            continue;
        }

        if (@unlink($path)) {
            $deleted++;
            logMessage("Deleted orphan: {$dirName}/{$filename}");
        } else {
            $errors++;
            logMessage("ERROR: could not delete: {$dirName}/{$filename}");
        }
    }
}

$lookup->close();
Database::close();

logMessage('Cleanup complete' . ($dryRun ? ' (DRY RUN — nothing deleted)' : '') . ':');
logMessage("  - Scanned:           {$scanned} files");
logMessage("  - Orphaned:          {$orphaned} files");
logMessage("  - Deleted:           {$deleted} files");
logMessage("  - Skipped (too new): {$skippedTooNew} files");
if ($errors > 0) {
    logMessage("  - Errors:          {$errors} files");
}

exit($errors > 0 ? 1 : 0);