<?php
/**
 * Test Script: Verify Booking History Integrity After Unmerge
 * 
 * This test verifies that:
 * 1. assigned_table_name column exists in table_bookings
 * 2. Table names are correctly stored when bookings are assigned
 * 3. Historical table names are preserved after unmerging
 * 4. Booking display queries use the snapshot names
 */

require 'config.php';

echo "<h1>Booking History Integrity Test</h1>";

// Test 1: Check if column exists
echo "<h2>Test 1: Check assigned_table_name column exists</h2>";
$check_sql = "DESCRIBE table_bookings";
$result = $conn->query($check_sql);
$column_exists = false;
while ($row = $result->fetch_assoc()) {
    if ($row['Field'] === 'assigned_table_name') {
        $column_exists = true;
        echo "<p style='color: green;'><strong>✓ Column 'assigned_table_name' exists</strong></p>";
        echo "<p>Type: " . $row['Type'] . " | Null: " . $row['Null'] . "</p>";
        break;
    }
}
if (!$column_exists) {
    echo "<p style='color: red;'><strong>✗ Column 'assigned_table_name' does NOT exist</strong></p>";
    echo "<p>Run: <code>backend/migrate_add_assigned_table_name.php</code></p>";
}

// Test 2: Check if existing bookings have snapshot names populated
echo "<h2>Test 2: Check existing bookings have snapshot names</h2>";
$stat_sql = "SELECT 
    COUNT(*) as total_bookings,
    COUNT(assigned_table_name) as with_snapshot,
    COUNT(CASE WHEN assigned_table_name IS NULL THEN 1 END) as without_snapshot
FROM table_bookings";
$result = $conn->query($stat_sql);
if ($result) {
    $stats = $result->fetch_assoc();
    echo "<p>Total bookings: " . $stats['total_bookings'] . "</p>";
    echo "<p>With snapshot: " . $stats['with_snapshot'] . "</p>";
    echo "<p>Without snapshot: " . $stats['without_snapshot'] . "</p>";
    
    if ($stats['total_bookings'] > 0 && $stats['with_snapshot'] > 0) {
        echo "<p style='color: green;'><strong>✓ Bookings have snapshot names populated</strong></p>";
    }
} else {
    echo "<p style='color: orange;'>Could not retrieve statistics: " . $conn->error . "</p>";
}

// Test 3: Sample booking with merged table scenario
echo "<h2>Test 3: Sample bookings and their table assignments</h2>";
$sample_sql = "SELECT 
    b.id as booking_id,
    b.name as customer_name,
    b.booking_date,
    rt.table_number as current_table_name,
    tb.assigned_table_name as historical_table_name,
    COALESCE(tb.assigned_table_name, rt.table_number) as display_name,
    rt.status as table_status
FROM bookings b
INNER JOIN table_bookings tb ON b.id = tb.booking_id
INNER JOIN restaurant_tables rt ON tb.table_id = rt.id
ORDER BY b.created_at DESC
LIMIT 5";

$result = $conn->query($sample_sql);
if ($result && $result->num_rows > 0) {
    echo "<table border='1' style='width: 100%; border-collapse: collapse; margin-top: 10px;'>";
    echo "<tr style='background: #f0f0f0;'>";
    echo "<th style='padding: 8px;'>Booking ID</th>";
    echo "<th style='padding: 8px;'>Customer</th>";
    echo "<th style='padding: 8px;'>Date</th>";
    echo "<th style='padding: 8px;'>Current Table</th>";
    echo "<th style='padding: 8px;'>Historical Name</th>";
    echo "<th style='padding: 8px;'>Display Name</th>";
    echo "<th style='padding: 8px;'>Table Status</th>";
    echo "</tr>";
    
    while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td style='padding: 8px;'>" . $row['booking_id'] . "</td>";
        echo "<td style='padding: 8px;'>" . htmlspecialchars($row['customer_name']) . "</td>";
        echo "<td style='padding: 8px;'>" . $row['booking_date'] . "</td>";
        echo "<td style='padding: 8px;'>" . htmlspecialchars($row['current_table_name']) . "</td>";
        echo "<td style='padding: 8px;'>";
        if ($row['historical_table_name']) {
            echo "<span style='background: #d4edda; padding: 4px 8px; border-radius: 3px;'>";
            echo htmlspecialchars($row['historical_table_name']);
            echo "</span>";
        } else {
            echo "<em>NULL (will use current)</em>";
        }
        echo "</td>";
        echo "<td style='padding: 8px;'>";
        echo "<strong>" . htmlspecialchars($row['display_name']) . "</strong>";
        echo "</td>";
        echo "<td style='padding: 8px;'>" . $row['table_status'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p>No bookings found. Create a test booking to verify the feature.</p>";
}

// Test 4: Verify API queries use COALESCE
echo "<h2>Test 4: Query Preview - How snapshot names are used</h2>";
echo "<pre style='background: #f5f5f5; padding: 10px; border-radius: 4px; overflow-x: auto;'>";
echo "-- Query used in booking lists:\n";
echo "SELECT b.*, \n";
echo "  GROUP_CONCAT(COALESCE(tb.assigned_table_name, rt.table_number)) AS assigned_tables\n";
echo "FROM bookings b\n";
echo "LEFT JOIN table_bookings tb ON b.id = tb.booking_id\n";
echo "LEFT JOIN restaurant_tables rt ON tb.table_id = rt.id\n";
echo "WHERE rt.status != 'merged'\n";
echo "GROUP BY b.id;\n\n";
echo "-- This ensures:\n";
echo "-- 1. If assigned_table_name exists (snapshot), use it\n";
echo "-- 2. Otherwise, fall back to current table_number\n";
echo "-- 3. Merged tables are excluded to prevent duplicates\n";
echo "</pre>";

// Test 5: Information about the fix
echo "<h2>How the Fix Works</h2>";
echo "<ol>";
echo "<li><strong>When booking is assigned:</strong> Table name is saved as snapshot in <code>assigned_table_name</code></li>";
echo "<li><strong>When tables are unmerged:</strong> Table names in <code>restaurant_tables</code> revert to original</li>";
echo "<li><strong>When booking is displayed:</strong> Query uses COALESCE to show snapshot if available, else current name</li>";
echo "<li><strong>Result:</strong> Historical names preserved even after table operations</li>";
echo "</ol>";

echo "<h2>Testing Merge → Unmerge Scenario</h2>";
echo "<ol>";
echo "<li>Merge tables T01 + T02 into a group</li>";
echo "<li>Create a booking assigned to the merged tables</li>";
echo "<li>Check: <code>SELECT * FROM table_bookings WHERE booking_id = [ID]</code></li>";
echo "<li>You should see <code>assigned_table_name</code> contains the merged name</li>";
echo "<li>Unmerge the tables</li>";
echo "<li>View the booking - name should still show merged tables</li>";
echo "</ol>";

echo "<h2>Result Summary</h2>";
if ($column_exists && $stats['total_bookings'] > 0 && $stats['with_snapshot'] > 0) {
    echo "<p style='color: green; font-weight: bold; font-size: 16px;'>";
    echo "✓ Booking history integrity fix is properly installed!</p>";
} else if (!$column_exists) {
    echo "<p style='color: red; font-weight: bold; font-size: 16px;'>";
    echo "✗ Migration needed - run <code>backend/migrate_add_assigned_table_name.php</code></p>";
} else {
    echo "<p style='color: orange; font-weight: bold; font-size: 16px;'>";
    echo "⚠ Fix installed but no bookings found. Create a test booking to verify.</p>";
}

$conn->close();
?>
