app/schemas/restaurant.sql

-- Menu Items Table
CREATE TABLE menu_items (
    item_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL
);

-- Customers Table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    contact TEXT,
    email TEXT
);

-- Orders Table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order Items Table
CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    item_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);


INSERT INTO menu_items VALUES 
(1, 'Margherita Pizza', 'Main Course', 250.0),
(2, 'Paneer Tikka', 'Appetizer', 180.0),
(3, 'Chocolate Brownie', 'Dessert', 120.0),
(4, 'Cold Coffee', 'Beverage', 90.0),
(5, 'Veg Burger', 'Main Course', 150.0),
(6, 'French Fries', 'Appetizer', 100.0),
(7, 'Lemonade', 'Beverage', 60.0);

INSERT INTO customers VALUES 
(1, 'Aman Gupta', '9876543210', 'aman.g@example.com'),
(2, 'Neha Sharma', '9876501234', 'neha.s@example.com'),
(3, 'Rohit Mehra', '9876567890', 'rohit.m@example.com');

INSERT INTO orders VALUES 
(1, 1, '2023-06-10', 500.0),
(2, 2, '2023-06-11', 360.0),
(3, 1, '2023-06-15', 420.0),
(4, 3, '2023-06-17', 270.0);

INSERT INTO order_items VALUES 
(1, 1, 1, 2),   -- 2 Margherita Pizzas
(2, 2, 2, 1),   -- 1 Paneer Tikka
(3, 2, 4, 2),   -- 2 Cold Coffees
(4, 3, 5, 1),   -- 1 Veg Burger
(5, 3, 3, 2),   -- 2 Brownies
(6, 3, 4, 1),   -- 1 Cold Coffee
(7, 4, 6, 2),   -- 2 French Fries
(8, 4, 7, 1);   -- 1 Lemonade