CREATE DATABASE IF NOT EXISTS emenu;
USE emenu;

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT,
    name VARCHAR(100),
    FOREIGN KEY (vendor_id) REFERENCES vendors(id)
);

CREATE TABLE menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT,
    category_id INT,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    photo VARCHAR(255),
    FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Sample category
INSERT INTO categories (vendor_id, name) VALUES (1, 'Pizzas');

-- Sample item
INSERT INTO menu_items (vendor_id, category_id, name, description, price, photo)
VALUES (1, 1, 'Margherita', 'Classic pizza with mozzarella & tomato', 8.99, 'margherita.jpg');
