<?php
/**
 * Migration Script: Add assigned_table_name column to table_bookings
 * This stores the table name at the time of assignment, preserving history after unmerging
 * Run this once to update the database schema
 */

require 'config.php';

// Add assigned_table_name column if it doesn't exist
$sql = "ALTER TABLE table_bookings 
        ADD COLUMN assigned_table_name VARCHAR(100) DEFAULT NULL 
        AFTER table_id";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'Successfully added assigned_table_name column to table_bookings']);
} else {
    // Column might already exist, check if it's a duplicate column error
    if (strpos($conn->error, 'Duplicate column') !== false) {
        echo json_encode(['success' => true, 'message' => 'Column assigned_table_name already exists']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Error adding column: ' . $conn->error]);
    }
}

// Also populate existing records with current table names
$update_sql = "UPDATE table_bookings tb
               INNER JOIN restaurant_tables rt ON tb.table_id = rt.id
               SET tb.assigned_table_name = rt.table_number
               WHERE tb.assigned_table_name IS NULL";

if ($conn->query($update_sql)) {
    echo json_encode(['success' => true, 'message' => 'Successfully populated assigned_table_name for existing bookings']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error populating data: ' . $conn->error]);
}

$conn->close();
?>
