-- Create Customer Table
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone_number VARCHAR(50),
    email VARCHAR(100)
);

-- Create TableEntity Table
CREATE TABLE TableEntity (
    table_id INT PRIMARY KEY,
    table_number INT,
    seating_capacity INT,
    location VARCHAR(50)
);

-- Create MenuItem Table
CREATE TABLE MenuItem (
    item_id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    category VARCHAR(50)
);

-- Create Reservation Table
CREATE TABLE Reservation (
    reservation_id INT PRIMARY KEY,
    customer_id INT,
    table_id INT,
    reservation_datetime DATETIME,
    number_of_guests INT,
    status VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (table_id) REFERENCES TableEntity(table_id)
);

-- Create OrderEntity Table
CREATE TABLE OrderEntity (
    order_id INT PRIMARY KEY,
    customer_id INT,
    table_id INT,
    order_datetime DATETIME,
    status VARCHAR(50),
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (table_id) REFERENCES TableEntity(table_id)
);

-- Create OrderItem Table
CREATE TABLE OrderItem (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    item_id INT,
    quantity INT,
    price_at_order_time DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES OrderEntity(order_id),
    FOREIGN KEY (item_id) REFERENCES MenuItem(item_id)
);