<?php
// Function to create SQLite database and table
function createDatabase($dbFileName) {
    // Open or create the SQLite database
    $pdo = new PDO("sqlite:$dbFileName");

    // Enable exception mode for PDO
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Create the table for categories and subcategories
    $createTableSQL = "
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            parent_id INTEGER,
            FOREIGN KEY (parent_id) REFERENCES categories (id)
        )
    ";
    $pdo->exec($createTableSQL);

    return $pdo;
}

function insertCategory($pdo, $name, $parentId = null) {
    $insertSQL = "INSERT INTO categories (name, parent_id) VALUES (:name, :parent_id)";
    $stmt = $pdo->prepare($insertSQL);
    $stmt->execute([':name' => $name, ':parent_id' => $parentId]);
}

function getCategoryHierarchy($pdo) {
    // Fetch all categories in a single query
    $selectSQL  = "SELECT id, name, parent_id FROM categories ORDER BY name";
    $stmt       = $pdo->query($selectSQL);
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Initialize the data structures
    $hierarchy   = [];
    $categoryMap = [];

    // Create an initial map of categories and root categories
    foreach ($categories as $category) {
        $category['subcategories']    = [];
        $categoryMap[$category['id']] = $category;
    }

    // Build the hierarchy
    foreach ($categories as $category) {
        if ($category['parent_id'] === null) {
            $hierarchy[] = &$categoryMap[$category['id']];
            continue;
        }

        // Check if the parent exists to avoid orphaned categories
        if (isset($categoryMap[$category['parent_id']])) {
            $categoryMap[$category['parent_id']]['subcategories'][] = &$categoryMap[$category['id']];
        } else {
            // Optionally handle orphaned categories here (e.g., log, add to root, etc.)
            // Here we add it to the root for simplicity
            $hierarchy[] = &$categoryMap[$category['id']];
        }
    }

    return $hierarchy;
}

$pdo = createDatabase('dbname.sqlite');

// insertCategory($pdo, 'Electronics');
// insertCategory($pdo, 'Mobile Phones', 1);
// insertCategory($pdo, 'Smartphones', 2);
// insertCategory($pdo, 'Feature Phones', 2);
// insertCategory($pdo, 'Laptops', 1);
// insertCategory($pdo, 'Home Appliances');
// insertCategory($pdo, 'Refrigerators', 6);
// insertCategory($pdo, 'Televisions', 6);
$categoryHierarchy = getCategoryHierarchy($pdo);

echo '<pre>';
print_r($categoryHierarchy);
echo '</pre>';
?>