<?php
/**
 * Test script to verify table availability filtering
 * Usage: Call this file with parameters like:
 * test_table_availability.php?date=2026-02-27&status=available
 */

require 'config.php';

header('Content-Type: application/json');

$date = isset($_GET['date']) ? $_GET['date'] : '2026-02-27';
$status = isset($_GET['status']) ? $_GET['status'] : 'available';

echo json_encode([
    'test_date' => $date,
    'test_status' => $status,
    'test_description' => 'Testing table availability for ' . $date,
    'results' => []
], JSON_PRETTY_PRINT);

// Query to fetch available tables for the specified date
$sql = "SELECT 
    rt.id,
    rt.table_number,
    rt.status,
    rt.current_capacity,
    rt.floor_level,
    rt.section,
    COUNT(DISTINCT CASE 
        WHEN b.booking_date = '" . $date . "' AND b.status NOT IN ('cancelled', 'expired', 'no_show', 'checked_in')
        THEN tb.booking_id
    END) as bookings_on_date
FROM restaurant_tables rt
LEFT JOIN table_bookings tb ON rt.id = tb.table_id
LEFT JOIN bookings b ON tb.booking_id = b.id
WHERE rt.status != 'merged'
GROUP BY rt.id, rt.table_number, rt.status, rt.current_capacity, rt.floor_level, rt.section
ORDER BY rt.floor_level, rt.section, rt.table_number";

$result = $conn->query($sql);

if ($result) {
    $tables = [];
    while ($row = $result->fetch_assoc()) {
        $tables[] = [
            'id' => $row['id'],
            'table_number' => $row['table_number'],
            'status' => $row['status'],
            'capacity' => $row['current_capacity'],
            'floor' => $row['floor_level'],
            'section' => $row['section'],
            'bookings_on_this_date' => $row['bookings_on_date'],
            'available_for_date' => ($row['status'] === 'available' || $row['bookings_on_date'] == 0)
        ];
    }
    
    echo json_encode([
        'success' => true,
        'test_date' => $date,
        'total_tables' => count($tables),
        'available_tables' => count(array_filter($tables, fn($t) => $t['available_for_date'])),
        'tables' => $tables
    ], JSON_PRETTY_PRINT);
} else {
    echo json_encode([
        'success' => false,
        'error' => $conn->error
    ], JSON_PRETTY_PRINT);
}

$conn->close();
?>
