validate_category_paths.php

<?php
use Magento\Framework\App\Bootstrap;

// Initialize Magento
require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

// Get resource connection
$resource = $objectManager->get(\Magento\Framework\App\ResourceConnection::class);
$connection = $resource->getConnection();

// Prepare queries
$categoryTable = $resource->getTableName('catalog_category_entity');

// Get all categories with their paths
$categoriesQuery = "SELECT entity_id, path FROM {$categoryTable}";
$categories = $connection->fetchAll($categoriesQuery);

$invalidPaths = [];
$processedIds = [];

foreach ($categories as $category) {
    $categoryId = $category['entity_id'];
    $path = $category['path'];
    $pathIds = explode('/', $path);
    
    // Check each ID in the path
    foreach ($pathIds as $pathId) {
        if (isset($processedIds[$pathId])) {
            continue; // Skip if we've already verified this ID
        }
        
        // Query to check if the ID exists
        $checkQuery = "SELECT entity_id FROM {$categoryTable} WHERE entity_id = ?";
        $result = $connection->fetchOne($checkQuery, [$pathId]);
        
        if (!$result) {
            $invalidPaths[$categoryId][] = $pathId;
        }
        
        $processedIds[$pathId] = true;
    }
}

// Output results
echo "Category Path Validation Results:\n";
echo "================================\n\n";

if (empty($invalidPaths)) {
    echo "✅ All category paths are valid!\n";
} else {
    echo "❌ Found invalid category paths:\n\n";
    foreach ($invalidPaths as $categoryId => $missingIds) {
        $categoryQuery = "SELECT path FROM {$categoryTable} WHERE entity_id = ?";
        $currentPath = $connection->fetchOne($categoryQuery, [$categoryId]);
        
        echo "Category ID: {$categoryId}\n";
        echo "Current path: {$currentPath}\n";
        echo "Missing parent IDs: " . implode(', ', $missingIds) . "\n\n";
    }
}