<?php
// Database Configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'rest_table');

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

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

// Create online_tables table
$sql = "CREATE TABLE IF NOT EXISTS online_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, reserved',
    floor_level INT DEFAULT 1,
    section VARCHAR(100),
    table_type VARCHAR(50) DEFAULT 'online' COMMENT 'online, shared',
    availability_from TIME DEFAULT '10:00:00',
    availability_to TIME DEFAULT '22:00:00',
    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' => 'online_tables created successfully']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating online_tables: ' . $conn->error]);
}

// Create online_table_bookings table (junction between online tables and bookings)
$sql = "CREATE TABLE IF NOT EXISTS online_table_bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    online_table_id INT NOT NULL,
    allocation_status VARCHAR(50) DEFAULT 'allocated' COMMENT 'allocated, available, released',
    allocated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    released_at TIMESTAMP NULL,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (online_table_id) REFERENCES online_tables(id) ON DELETE CASCADE
)";

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

$conn->close();
echo json_encode(['success' => true, 'message' => 'Online tables database initialized successfully!']);
?>
