db_handler.php

<?php
// php/db_handler.php

/**
 * Queries data from a database (simulated).
 * In a real application, use PDO with prepared statements for security.
 *
 * @param string $tableName The name of the table to query.
 * @param array $columns Specific columns to select (defaults to all).
 * @param array $conditions Conditions for a WHERE clause (highly simplified).
 * @return array|null The query result formatted for the dashboard, or null/error array on failure.
 */
function queryDatabase(string $tableName, array $columns = ['*'], array $conditions = []): ?array
{
    error_log("DB Handler: Called for table '$tableName' with columns: " . json_encode($columns) . " and conditions: " . json_encode($conditions));

    // Simulate DB interaction
    // usleep(rand(100000, 500000)); // 100ms to 500ms

    // Simulated data - replace with actual database connection and query
    $availableTables = [
        'sales_overview' => [
            'columns' => ['id', 'product_name', 'category', 'quantity_sold', 'unit_price', 'total_revenue', 'sale_date'],
            'rows' => [
                ['id' => 1, 'product_name' => 'Alpha Widget', 'category' => 'Electronics', 'quantity_sold' => rand(50, 200), 'unit_price' => 25.99, 'total_revenue' => 0, 'sale_date' => '2023-03-15'],
                ['id' => 2, 'product_name' => 'Beta Gadget', 'category' => 'Accessories', 'quantity_sold' => rand(100, 300), 'unit_price' => 12.50, 'total_revenue' => 0, 'sale_date' => '2023-03-16'],
                ['id' => 3, 'product_name' => 'Gamma Device', 'category' => 'Electronics', 'quantity_sold' => rand(30, 150), 'unit_price' => 75.00, 'total_revenue' => 0, 'sale_date' => '2023-03-16'],
                ['id' => 4, 'product_name' => 'Delta Tool', 'category' => 'Hardware', 'quantity_sold' => rand(200, 500), 'unit_price' => 7.99, 'total_revenue' => 0, 'sale_date' => '2023-03-17'],
                ['id' => 5, 'product_name' => 'Epsilon Gear', 'category' => 'Accessories', 'quantity_sold' => rand(80, 250), 'unit_price' => 19.95, 'total_revenue' => 0, 'sale_date' => '2023-03-18'],
            ]
        ],
        'user_profiles' => [
            'columns' => ['user_id', 'username', 'email', 'registration_date', 'last_login'],
            'rows' => [
                ['user_id' => 101, 'username' => 'johndoe', 'email' => 'john.doe@example.com', 'registration_date' => '2022-01-10', 'last_login' => '2023-03-18 10:00:00'],
                ['user_id' => 102, 'username' => 'janismith', 'email' => 'jane.smith@example.com', 'registration_date' => '2022-02-15', 'last_login' => '2023-03-17 14:30:00'],
            ]
        ]
    ];

    if (isset($availableTables[$tableName])) {
        $tableData = $availableTables[$tableName];
        // Simulate calculating total_revenue for sales_overview
        if ($tableName === 'sales_overview') {
            foreach ($tableData['rows'] as &$row) { // Use reference to modify array directly
                $row['total_revenue'] = $row['quantity_sold'] * $row['unit_price'];
            }
            unset($row); // Unset reference
        }

        // Simulate column selection
        $selectedColumns = ($columns === ['*'] || empty($columns)) ? $tableData['columns'] : $columns;
        $filteredRows = [];
        foreach ($tableData['rows'] as $row) {
            $newRow = [];
            foreach ($selectedColumns as $col) {
                if (array_key_exists($col, $row)) {
                    $newRow[$col] = $row[$col];
                }
            }
            $filteredRows[] = $newRow;
        }

        // Ensure the 'columns' key in the output matches the actual columns returned
        $finalColumns = !empty($filteredRows) ? array_keys($filteredRows[0]) : $selectedColumns;


        return ['columns' => $finalColumns, 'rows' => $filteredRows];
    } else {
        error_log("DB Handler: Unknown table '$tableName'");
        return ['error' => "Database table not found or not simulated: $tableName"];
    }
}

// Example of how you might call this (not used by data_provider.php directly like this)
/*
if (basename(__FILE__) == basename($_SERVER["SCRIPT_FILENAME"])) {
    header('Content-Type: application/json');
    $testTable = $_GET['table'] ?? 'sales_overview';
    $simulatedData = queryDatabase($testTable, $_GET['cols'] ?? ['*']);
     if (isset($simulatedData['error'])) {
        http_response_code(404);
    }
    echo json_encode($simulatedData);
}
*/
?>