<?php
// Table Management Database Initialization
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'granny_restaurant');

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if ($conn->connect_error) {
    die(json_encode(['success' => false, 'message' => 'Connection failed: ' . $conn->connect_error]));
}

// Create restaurant_tables table
$sql = "CREATE TABLE IF NOT EXISTS restaurant_tables (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_number VARCHAR(50) NOT NULL UNIQUE,
    base_capacity INT NOT NULL DEFAULT 4,
    current_capacity INT NOT NULL DEFAULT 4,
    status VARCHAR(50) DEFAULT 'available' COMMENT 'available, booked, maintenance',
    floor_level INT DEFAULT 1,
    section VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'restaurant_tables created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating restaurant_tables: ' . $conn->error]);
}

// Create table_bookings table (junction between tables and bookings)
$sql = "CREATE TABLE IF NOT EXISTS table_bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    table_id INT NOT NULL,
    is_merged BOOLEAN DEFAULT FALSE,
    merged_with_table_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (merged_with_table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'table_bookings created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating table_bookings: ' . $conn->error]);
}

$conn->close();
?>
