<?php
/**
 * Debug: Check exactly why assigned_tables are not showing
 */

include 'config.php';

$server_id = 6;

echo "<h2>Deep Debug Query (Server ID: " . $server_id . ")</h2>";
echo "<pre>";

// Test 1: Check if server exists
echo "=== TEST 1: Server Verification ===\n";
$server_check = "SELECT id, name, employee_id FROM servers WHERE id = " . intval($server_id);
echo "Query: " . $server_check . "\n";
$result = $conn->query($server_check);
if ($result && $result->num_rows > 0) {
    $server_data = $result->fetch_assoc();
    echo "✓ Server found: " . $server_data['name'] . "\n\n";
} else {
    echo "✗ Server not found\n";
    exit;
}

// Test 2: Check if table_assignments table has ANY records for this server
echo "=== TEST 2: All Assignments for Server ===\n";
$all_assignments = "SELECT id, table_id, server_id, status, number_of_guests FROM table_assignments WHERE server_id = " . intval($server_id);
echo "Query: " . $all_assignments . "\n";
$result = $conn->query($all_assignments);
echo "Results: " . ((!$result) ? "ERROR: " . $conn->error : $result->num_rows . " rows") . "\n";
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo json_encode($row) . "\n";
    }
} else {
    echo "No assignments found for this server at all!\n";
}
echo "\n";

// Test 3: Check if there are assignments with status='assigned'
echo "=== TEST 3: Assigned Status Only ===\n";
$assigned_only = "SELECT id, table_id, server_id, status FROM table_assignments WHERE server_id = " . intval($server_id) . " AND status = 'assigned'";
echo "Query: " . $assigned_only . "\n";
$result = $conn->query($assigned_only);
echo "Results: " . ((!$result) ? "ERROR: " . $conn->error : $result->num_rows . " rows") . "\n";
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo json_encode($row) . "\n";
    }
} else {
    echo "No assignments with 'assigned' status!\n";
}
echo "\n";

// Test 4: Check restaurant_tables exists and has records
echo "=== TEST 4: Restaurant Tables Check ===\n";
$tables_check = "SELECT id, table_number FROM restaurant_tables LIMIT 5";
echo "Query: " . $tables_check . "\n";
$result = $conn->query($tables_check);
if (!$result) {
    echo "ERROR: " . $conn->error . "\n";
} else {
    echo "Sample tables (" . $result->num_rows . " shown):\n";
    while ($row = $result->fetch_assoc()) {
        echo "  ID: " . $row['id'] . ", Number: " . $row['table_number'] . "\n";
    }
}
echo "\n";

// Test 5: Test the JOIN directly
echo "=== TEST 5: Test JOIN Query ===\n";
$join_query = "SELECT 
                ta.id,
                ta.table_id,
                ta.server_id,
                ta.status as assignment_status,
                rt.table_number,
                rt.capacity
               FROM table_assignments ta
               INNER JOIN restaurant_tables rt ON ta.table_id = rt.id
               WHERE ta.server_id = " . intval($server_id) . " 
               AND ta.status = 'assigned'";
echo "Query: " . $join_query . "\n\n";
$result = $conn->query($join_query);
if (!$result) {
    echo "ERROR executing query: " . $conn->error . "\n";
} else {
    echo "Query executed successfully. Rows: " . $result->num_rows . "\n";
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo json_encode($row, JSON_PRETTY_PRINT) . "\n";
        }
    } else {
        echo "Query returned 0 rows\n\n";
        echo "Checking if it's a JOIN issue...\n";
        
        // Check what table_id values exist
        $check_table_ids = "SELECT DISTINCT ta.table_id FROM table_assignments ta WHERE ta.server_id = " . intval($server_id);
        echo "Table IDs in assignments: ";
        $result = $conn->query($check_table_ids);
        while ($row = $result->fetch_assoc()) {
            echo $row['table_id'] . ", ";
        }
        echo "\n";
        
        // Check if those tables exist
        $assigned_tables = $conn->query($check_table_ids);
        $table_ids = [];
        while ($row = $assigned_tables->fetch_assoc()) {
            $table_ids[] = $row['table_id'];
        }
        
        if (!empty($table_ids)) {
            $check_exists = "SELECT id FROM restaurant_tables WHERE id IN (" . implode(",", $table_ids) . ")";
            $result = $conn->query($check_exists);
            echo "Do these table IDs exist in restaurant_tables? " . $result->num_rows . " found\n";
        }
    }
}

echo "\n=== END DEBUG ===\n";
echo "</pre>";
?>
