<?php
// Set the Content-Type header to indicate JSON output
header('Content-Type: application/json');

// Database connection parameters
// !! IMPORTANT: Replace these with your actual database credentials !!
$host = 'localhost'; // Database host
$db   = 'digitalb_beaccurate'; // Your database name
$user = 'digitalb_beaccurate';   // Your database username
$pass = 'Saiful.1633';   // Your database password
$charset = 'utf8mb4'; // Character set

// Data Source Name (DSN) string
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// PDO options for error handling and fetching data
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch rows as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Use real prepared statements
];

try {
    // Create a new PDO instance (connect to the database)
    $pdo = new PDO($dsn, $user, $pass, $options);

    // SQL query to select category and SubCategory
    // Ordering by category makes processing easier
    $sql = "SELECT category, SubCategory FROM Products_Category ORDER BY category, SubCategory";

    // Execute the query
    $stmt = $pdo->query($sql);

    // Initialize an array to hold the organized data
    $organizedData = [];

    // Fetch rows and organize them into the desired structure
    while ($row = $stmt->fetch()) {
        $category = $row['category'];
        $subCategory = $row['SubCategory'];

        // If the category doesn't exist as a key yet, initialize it as an empty array
        if (!isset($organizedData[$category])) {
            $organizedData[$category] = [];
        }

        // Add the subcategory to the array for the current category
        // Only add if SubCategory is not null or empty (optional check)
        if (!empty($subCategory)) {
             $organizedData[$category][] = $subCategory;
        }
    }

    // Output the organized data as JSON
    echo json_encode($organizedData);

} catch (\PDOException $e) {
    // Handle database connection or query errors
    http_response_code(500); // Set HTTP status code to 500 Internal Server Error
    echo json_encode([
        "error" => "Database operation failed.",
        "details" => $e->getMessage() // Consider removing $e->getMessage() in production
    ]);
    // error_log("PDO Error: " . $e->getMessage()); // Log the error

} catch (\Exception $e) {
    // Catch any other general exceptions
     http_response_code(500); // Set HTTP status code
     echo json_encode([
        "error" => "An unexpected error occurred.",
        "details" => $e->getMessage() // Consider removing $e->getMessage() in production
    ]);
    // error_log("General Error: " . $e->getMessage()); // Log the error
}

// The PDO connection is automatically closed.

?>