<?php
// Database Initialization Script
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');

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

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$dbname = 'rest_table';

// Create database if it doesn't exist
$sql = "CREATE DATABASE IF NOT EXISTS " . $dbname;
$conn->query($sql);

// Select database
$conn->select_db($dbname);

// Create users table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($sql);

// Create admins table
$sql = "CREATE TABLE IF NOT EXISTS admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    position VARCHAR(100),
    permission_level VARCHAR(50) DEFAULT 'full',
    last_login TIMESTAMP NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($sql);

// Create bookings table
$sql = "CREATE TABLE IF NOT EXISTS bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    booking_date DATE NOT NULL,
    slot_time TIME NOT NULL,
    guests INT NOT NULL,
    meal_type VARCHAR(50),
    table_type VARCHAR(50),
    dietary_needs VARCHAR(255),
    special_requests TEXT,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)";
$conn->query($sql);

// Create orders table
$sql = "CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_number VARCHAR(50) UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    subtotal DECIMAL(10, 2),
    tax DECIMAL(10, 2),
    total DECIMAL(10, 2),
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)";
$conn->query($sql);

// Create order_items table
$sql = "CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
)";
$conn->query($sql);

// Create feedback table
$sql = "CREATE TABLE IF NOT EXISTS feedback (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    subject VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)";
$conn->query($sql);

// Create contacts table
$sql = "CREATE TABLE IF NOT EXISTS contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($sql);

// 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, merged',
    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
)";
$conn->query($sql);

// 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
)";
$conn->query($sql);

// 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
)";
$conn->query($sql);

// 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
)";
$conn->query($sql);

// Create meal_types table
$sql = "CREATE TABLE IF NOT EXISTS meal_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50) DEFAULT 'fas fa-utensils',
    color_code VARCHAR(7) DEFAULT '#d4a574',
    status VARCHAR(20) DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($sql);

// Create time_slots table
$sql = "CREATE TABLE IF NOT EXISTS time_slots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    meal_type_id INT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_name VARCHAR(100),
    description TEXT,
    max_capacity INT DEFAULT 100,
    status VARCHAR(20) DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_slot (meal_type_id, start_time, end_time),
    FOREIGN KEY (meal_type_id) REFERENCES meal_types(id) ON DELETE CASCADE
)";
$conn->query($sql);

// Insert default meal types if they don't exist
$sql = "INSERT IGNORE INTO meal_types (name, description, icon, display_order) VALUES 
('Lunch', 'Lunch service timings', 'fas fa-sun', 1),
('Dinner', 'Dinner service timings', 'fas fa-moon', 2)";
$conn->query($sql);

// Insert default time slots for Lunch
$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '12:00:00', '13:00:00', '12:00 PM - 1:00 PM', 1 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '13:00:00', '14:00:00', '1:00 PM - 2:00 PM', 2 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '14:00:00', '15:00:00', '2:00 PM - 3:00 PM', 3 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

// Insert default time slots for Dinner
$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '18:00:00', '19:00:00', '6:00 PM - 7:00 PM', 1 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '19:00:00', '20:00:00', '7:00 PM - 8:00 PM', 2 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '20:00:00', '21:00:00', '8:00 PM - 9:00 PM', 3 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);


$conn->close();
echo "Database initialized successfully!";
?>
