DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS users;




CREATE TABLE users (

                       id SERIAL PRIMARY KEY,

                       username VARCHAR(50) UNIQUE NOT NULL,

                       password VARCHAR(255) NOT NULL,

                       email VARCHAR(100)

);





CREATE TABLE menu_items (

                            id SERIAL PRIMARY KEY,

                            name VARCHAR(100) NOT NULL,

                            description TEXT,

                            price NUMERIC(10,2) NOT NULL CHECK(price > 0),

                            category VARCHAR(50)

);





CREATE TABLE orders (

                        id SERIAL PRIMARY KEY,

                        user_id INTEGER NOT NULL,

                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                        total_price NUMERIC(10,2) NOT NULL,


                        CONSTRAINT fk_order_user

                            FOREIGN KEY(user_id)

                                REFERENCES users(id)

                                ON DELETE CASCADE

);





CREATE TABLE order_details (

                               id SERIAL PRIMARY KEY,


                               order_id INTEGER NOT NULL,


                               menu_item_id INTEGER NOT NULL,


                               quantity INTEGER NOT NULL CHECK(quantity > 0),


                               price NUMERIC(10,2) NOT NULL CHECK(price > 0),


                               CONSTRAINT fk_detail_order

                                   FOREIGN KEY(order_id)

                                       REFERENCES orders(id)

                                       ON DELETE CASCADE,


                               CONSTRAINT fk_detail_menu

                                   FOREIGN KEY(menu_item_id)

                                       REFERENCES menu_items(id)

);






INSERT INTO menu_items
(name, description, price, category)

VALUES

    ('Pizza','Cheese pizza',10.00,'Pizza'),

    ('Burger','Beef burger',8.00,'Burger'),

    ('Pasta','Italian pasta',12.00,'Pasta'),

    ('Cola','Cold drink',2.50,'Drink');