detect zombie files

<?php

$SERVERNAME = "localhost";
$USERNAME = "root";
$PASSWORD = "root";
$DATABASE = "uss";

$ROOTPATH = "/var/www/blabla";
$OUT_FILE_PATH = "zombies.txt";

$REMOVE_ZOMBIE_FILES = false;
$REMOVE_FROM_DATABASE = false;

// Create connection
$conn = new mysqli($SERVERNAME, $USERNAME, $PASSWORD, $DATABASE);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully\n";

// Prepare queries
$joinTables = [
    'message_file',
    'third_party_license_verification_files',
    'training_exercise_file',
    'user',
    'work_expirence_verification_files',
    'easy_sports_resource',
    'resource',
];

$jsonResultTables = [
    'application_result',
    'assignment_result',
];

$queries = [
    'file' => "SELECT f.id as id FROM file f",
];

foreach ($joinTables as $table) {
    $queries[$table] = "SELECT f.id as id FROM file f join $table a on a.file_id = f.id";
}

foreach ($jsonResultTables as $table) {
    $queries[$table] = "SELECT a.result_given as result FROM $table a";
}


// Executing queries
$usedFileIds = $allFileIds = [];

foreach ($queries as $tableName => $query) {

    $result = $conn->query($query);
    if (!$result) {
        echo "Error executing query: ".$query."\n".$conn->error;
        exit;
    }


    $ids = [];

    $isJson = in_array($tableName, ['application_result', 'assignment_result']);


    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            if ($isJson) {
                $data = $row['result'];
                $jsonData = json_decode($data, true);

                searchIds($ids, $jsonData);

                continue;
            }

            $ids[] = $row["id"];
        }
    }

    if ($isJson) echo "[FROM-JSON] ";
    echo "Found ".count($ids)." entities on {$tableName}\n";

    $result->free();

    if ($tableName == 'file') {
        $allFileIds = $ids;
        continue;
    }

    $diff = array_intersect($usedFileIds, $ids);
    if (count($diff)) {
        echo "Warning! Found already ".count($diff)." entities on other tables!\n";
    }

    $usedFileIds = array_merge($usedFileIds, $ids);
    echo "  Total found used files: ".count($usedFileIds)."\n";
}


$unusedFileIds = array_diff($allFileIds, $usedFileIds);
$unusedPercentage = number_format(count($unusedFileIds) / count($allFileIds) * 100, 2);

echo "\nThere was found ".count($unusedFileIds)." zombie files ( {$unusedPercentage} % )\n";
if (!$unusedFileIds) {
    echo "Nothing to delete. Exiting\n";
    exit;
}

// prepare path files
$paths = [];
$query = "SELECT f.path as p FROM file f where id in (". implode(',', $unusedFileIds) .")";
$result = $conn->query($query);
if (!$result) {
    echo "Error executing query: ".$query."\n".$conn->error;
    exit;
}
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $paths[] = $row["p"];
    }
}

// remove unused entities
if ($REMOVE_FROM_DATABASE) {
    $query = "DELETE FROM file where id in (". implode(',', $unusedFileIds) .")";
    $conn->exec($query);
    echo "Records (".$unusedFileIds.") deleted successfully\n";
}

// removing path files
$unexistingFiles = $readyToDeleteFiles = [];
foreach ($paths as $path) {
    $fullPath = $ROOTPATH.$path;
    if (!file_exists($fullPath)) {
        $unexistingFiles[] = $path;
        continue;
    }
    $readyToDeleteFiles[] = $fullPath;
    if ($REMOVE_ZOMBIE_FILES) {
        unlink($fullPath);
    }
}

if (count($unexistingFiles) > 0) {

    $unexistingPercentage = number_format(count($unexistingFiles) / count($paths) * 100, 2);

    echo "Unexisting files: ".count($unexistingFiles)." ( {$unexistingPercentage} % )\n";
}

if (count($readyToDeleteFiles) > 0) {
    $myfile = fopen($OUT_FILE_PATH, "w+") or die("Unable to open file!");
    foreach ($readyToDeleteFiles as $filePath) {
        $txt = $filePath."\n";
        fwrite($myfile, $txt);
    }
    fclose($myfile);

    if ($REMOVE_ZOMBIE_FILES) {
        echo "Deleted files: ".count($readyToDeleteFiles)."\n";
    } else {
        echo "Ready to delete files: ".count($readyToDeleteFiles)."\n";
    }

    echo "The files path are written in ".$OUT_FILE_PATH."\n";
}



mysqli_close($conn);

/*=================================
=            functions            =
=================================*/

function searchIds(array &$ids, array $json = null)
{
    if (!$json) return;

    if (!is_array($json)) return;

    if (isset($json['path']) && isset($json['id'])) {
        $ids[] = $json['id'];
    }

    foreach ($json as $key => $value) {
        if (is_array($value)) {
            searchIds($ids, $value);
        }
    }
}

/*=====  End of functions  ======*/

?>