Logical and Physical Design – Database Creation: DML.sql

INSERT INTO app_user (user_id, first_name, last_name, email, password, phone)
VALUES 
(1, 'Aysun', 'Suleymanturk', 'aysun@example.com', 'pass123', '5551112233'),  
(2, 'Ece', 'Mucaiti', 'ece@example.com', 'pass123', '5554446677'),          
(3, 'Esin', 'Suleymanturk', 'esin@example.com', 'pass123', '5558889999');   

INSERT INTO ADMINISTRATOR (user_id, authorized_from, authorized_till) VALUES
(3, '2025-01-01', '2025-12-31');

INSERT INTO address (address_id, city, street, postal_code)
VALUES 
(1, 'Skopje', 'Maksim Gorki', '1000'),
(2, 'Skopje', 'Gjorče Petrov', '1060');


INSERT INTO category (category_id, name, description)
VALUES
(1, 'Pizza', 'All kinds of pizza'),
(2, 'Burgers', 'Delicious burgers'),
(3, 'Drinks', 'Beverages');


INSERT INTO item (item_id, image_url, name, price, description)
VALUES
(1, 'pizza.jpg', 'Margherita Pizza', 80.00, 'Classic cheese pizza'),
(2, 'burger.jpg', 'Cheeseburger', 60.00, 'Juicy cheeseburger'),
(3, 'cola.jpg', 'Coca Cola', 20.00, 'Chilled soft drink');


INSERT INTO ingredient (ingredient_id, name)
VALUES
(1, 'Cheese'),
(2, 'Tomato'),
(3, 'Beef'),
(4, 'Lettuce');


INSERT INTO delivery_firm (deliveryfirm_id, name)
VALUES (1, 'FastDelivery');



INSERT INTO restaurant (restaurant_id, name, website_url, opening_time, closing_time, email, phone_number, address_id)
VALUES
(1, 'Pizza Hut', 'www.pizzahut.com', '10:00', '23:00', 'info@pizzahut.com', '2121112233', 1),
(2, 'Burger World', 'www.burgerworld.com', '11:00', '22:00', 'contact@burgerworld.com', '3124445566', 2);



INSERT INTO customer (user_id) VALUES (1);   
INSERT INTO delivery_man (user_id) VALUES (2); 
INSERT INTO restaurant_owner (user_id) VALUES (3);



INSERT INTO user_addresses (address_id, user_id) VALUES (1, 1);



INSERT INTO restaurant_owners (user_id, restaurant_id) VALUES (3, 1);


INSERT INTO item_category (category_id, item_id) VALUES
(1, 1),
(2, 2),
(3, 3);



INSERT INTO restaurant_category (category_id, restaurant_id) VALUES
(1, 1),
(2, 2);



INSERT INTO earnings (earning_id, restaurant_id, date, amount, source)
VALUES
(1, 1, '2025-08-01', 2000.50, 'Orders'),
(2, 2, '2025-08-02', 1500.00, 'Orders');



INSERT INTO costs (cost_id, restaurant_id, date, amount, type)
VALUES
(1, 1, '2025-08-01', 500.00, 'Ingredients'),
(2, 2, '2025-08-02', 300.00, 'Utilities');



INSERT INTO item_ingredient (item_id, ingredient_id, quantity) VALUES
(1, 1, '100g'), 
(1, 2, '50g'),   
(2, 3, '150g'),  
(2, 4, '30g');  



INSERT INTO delivery_assignment (deliveryfirm_id, user_id)
VALUES
(1, 2);


INSERT INTO MENU (restaurant_id, name) VALUES
(1, 'Pizza Hut Menu'),
(2, 'Burger World Menu');


INSERT INTO MENU_ITEM (menu_id, item_id) VALUES
(1, 1),
(2, 2),
(2, 3); 

INSERT INTO SHOPPING_CART (user_id) VALUES
(1),
(2); 


INSERT INTO CART_ITEMS (cart_id, item_id, quantity) VALUES
(1, 1, 2),  
(1, 3, 1),  
(2, 2, 1);


INSERT INTO ORDERS (address_id, restaurant_id, deliveryman_id, user_id, order_status, total_amount) VALUES
(1, 1, 2, 1, 'Pending', 180.00),  
(2, 2, 2, 1, 'Delivered', 80.00); 


INSERT INTO ORDER_ITEMS (order_id, item_id, quantity, total_price) VALUES
(1, 1, 2, 160.00),
(1, 3, 1, 20.00),
(2, 2, 1, 60.00);