SQL

CREATE DATABASE butternbread;

USE DATABASE butternbread;

CREATE TABLE restaurants (
    restaurant_id CHAR(128),
    name VARCHAR(20),
    phone VARCHAR(15),
    adress VARCHAR(100),
    description TEXT,
    PRIMARY KEY (restaurant_id)
);

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    email VARCHAR(30) UNIQUE,
    name VARCHAR(20),
    password CHAR(128),
    role VARCHAR(5),
    language VARCHAR(5),
    currency VARCHAR(10),
    restaurant_id CHAR(128),
    verification BOOLEAN NOT NULL DEFAULT FALSE
    PRIMARY KEY(user_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT,
    name VARCHAR(20),
    restaurant_id CHAR(128),
    PRIMARY KEY (category_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    name VARCHAR(20),
    price DECIMAL(6, 2),
    loss DECIMAL(6, 2),
    cost DECIMAL(6, 2),
    category_id INT,
    restaurant_id CHAR(128),
    PRIMARY KEY (product_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

CREATE TABLE dishes (
    dish_id INT AUTO_INCREMENT,
    name VARCHAR(55),
    category VARCHAR(20),
    cost DECIMAL(8, 2),
    restaurant_id CHAR(128),
    PRIMARY KEY (dish_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE ingredients (
    ingredient_id INT AUTO_INCREMENT,
    product_id INT,
    dish_id INT,
    gPP DECIMAL(6, 2),
    PRIMARY KEY (ingredient_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL,
    FOREIGN KEY (dish_id) REFERENCES dishes(dish_id) ON DELETE CASCADE
);

CREATE TABLE recipes (
    recipe_id INT AUTO_INCREMENT,
    dish_id INT,
    recipe TEXT,
    restaurant_id char(128),
    minimum_production DECIMAL (6, 2),
    PRIMARY KEY (recipe_id),
    FOREIGN KEY (dish_id) REFERENCES dishes(dish_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT,
    name VARCHAR(20),
    email VARCHAR(30),
    phone VARCHAR(15),
    comertial VARCHAR(20),
    restaurant_id CHAR(128),
    PRIMARY KEY (supplier_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    supplier_id INT,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    restaurant_id CHAR(128),
    PRIMARY KEY (order_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE SET NULL
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT,
    product_id INT,
    quantity DECIMAL(6, 2),
    unit VARCHAR(5),
    order_id INT,
    PRIMARY KEY (item_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

CREATE TABLE sessions(
    session_id CHAR(128),
    user_id INT,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE revoked_sessions(
    session_id CHAR(128),
    date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id)
);

CREATE TABLE invitation_links(
    invitation_id CHAR(128),
    restaurant_id CHAR(128),
    role VARCHAR(6),
    date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (invitation_id)
);

CREATE TABLE verifications(
    code CHAR(128),
    user_id INT,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (code),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE forgots(
    user_id INT,
    code CHAR(128),
    PRIMARY KEY (code),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);


drop table restaurants;
drop table categories;
drop table users;
drop table products;
drop table dishes;
drop table recipes;
drop table ingredients;
drop table suppliers;
drop table orders;
drop table order_items;
drop table sessions;
drop table revoked_sessions;

DELIMITER // 
CREATE PROCEDURE US_restaurant (IN _restaurant_id CHAR(128), IN _restaurant_name VARCHAR(20), IN _phone VARCHAR(20), IN _adress VARCHAR(100), IN _description TEXT)
BEGIN
    UPDATE restaurants 
        SET name = _restaurant_name, phone = _phone, adress = _adress, description = _description
            WHERE restaurant_id = _restaurant_id;
    SELECT name AS restaurant_name, phone, adress, description FROM restaurants
        WHERE restaurant_id = _restaurant_id;
END //
DELIMITER;

DELIMITER //
CREATE PROCEDURE IS_user (IN _email VARCHAR(30), _name VARCHAR(20), _password CHAR(128), _role VARCHAR(6), _language VARCHAR(3), _currency VARCHAR(10), _restaurant_id CHAR(128))
    BEGIN
        INSERT INTO users(email, name, password, role, language, currency, restaurant_id)
            VALUES(_email, _name, _password, _role, _language, _currency, _restaurant_id);
        SET @last_id = LAST_INSERT_ID();
                SELECT user_id
                    FROM users
                        WHERE user_id = @last_id;
END//

DELIMITER //
CREATE PROCEDURE US_user_email (IN old_email VARCHAR(30), IN new_email VARCHAR(30))
BEGIN 
    UPDATE users
        SET email = new_email
            WHERE email = old_email;
    SELECT user_id, email, name, role, language, restaurant_id FROM users
        WHERE email = new_email;
END //
DELIMITER;
    
DELIMITER //
CREATE PROCEDURE US_user_name (IN in_email VARCHAR(30), IN in_name VARCHAR(20))
BEGIN
    UPDATE users
        SET name = in_name
            WHERE email = in_email;
    SELECT user_id, email, name, role, language, restaurant_id
        FROM users
            WHERE email = in_email;
END //
DELIMITER;

DELIMITER //
CREATE PROCEDURE US_user_language (IN _user_id INT, IN _language VARCHAR(5))
BEGIN
    UPDATE users
        SET language = _language
            WHERE user_id = user_id;
    SELECT user_id, email, name, language FROM users
        WHERE user_id = _user_id;
END //
DELIMITER;

CREATE PROCEDURE US_user_currency (IN _user_id INT, IN _currency VARCHAR(10))
BEGIN
    UPDATE users
        SET currency = _currency
            WHERE user_id = _user_id;
    SELECT user_id, email, name, language, currency FROM users
        WHERE user_id = _user_id;
END//

DELIMITER //
CREATE PROCEDURE D_invitations (IN id CHAR(128))
BEGIN
    DELETE FROM invitation_links WHERE invitation_id = id;
END //
DELIMITER ;

CREATE PROCEDURE I_invitations (IN id CHAR(128), res_id CHAR(128), role VARCHAR(6))
BEGIN
    INSERT INTO invitation_links(invitation_id, restaurant_id, role) VALUES (id, res_id, role);
END //

CREATE PROCEDURE D_user (IN id INT, IN res_id CHAR(128))
BEGIN
    SELECT user_id, email, name, role FROM users WHERE user_id = id;
    DELETE FROM users WHERE user_id = id AND restaurant_id = res_id;
END //

CREATE PROCEDURE IS_product (IN _name VARCHAR(20), _price DECIMAL(6, 2), _loss DECIMAL(6, 2), _cost DECIMAL(6, 2), _category_id INT, _restaurant_id CHAR(128))
    BEGIN
        INSERT INTO products(name, price, loss, cost, category_id, restaurant_id)
        VALUES (_name, _price, _loss, _cost, _category_id, _restaurant_id);
        SET @last_id = LAST_INSERT_ID();
            SELECT products.product_id, products.name, products.price, products.loss, products.cost, categories.name AS category
                FROM products
                    JOIN categories
                        ON products.category_id = categories.category_id 
                            WHERE products.restaurant_id = _restaurant_id AND products.product_id = @last_id;
END //

CREATE PROCEDURE DS_product (IN id INT, _restaurant_id CHAR(128))
    BEGIN
        SELECT products.product_id, products.name, products.price, products.loss, products.cost, categories.name AS category
            FROM products
                JOIN categories
                    ON products.category_id = categories.category_id 
                        WHERE products.product_id = id AND products.restaurant_id = _restaurant_id;
        DELETE FROM products WHERE product_id = id AND products.restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE US_product (IN id INT, IN _name VARCHAR(20), _price DECIMAL(6, 2), _loss DECIMAL(6, 2), _cost DECIMAL(6, 2), _category_id INT, _restaurant_id CHAR(128))
    BEGIN 
        UPDATE products 
            SET name = _name, price = _price, loss = _loss, cost = _cost, category_id = _category_id 
        WHERE product_id = id AND restaurant_id = _restaurant_id;
        SELECT products.product_id, products.name, products.price, products.loss, products.cost, categories.name AS category
            FROM products
                JOIN categories
                    ON products.category_id = categories.category_id 
                        WHERE products.product_id = id AND products.restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE IS_category (IN _name VARCHAR(20), IN _restaurant_id CHAR(128))
    BEGIN
        INSERT INTO categories(name, restaurant_id) VALUES (_name, _restaurant_id);
        SET @last_id = LAST_INSERT_ID();
        SELECT category_id, name FROM categories
            WHERE restaurant_id = _restaurant_id AND category_id = @last_id;
END//

CREATE PROCEDURE US_category (IN _id INT, IN _name VARCHAR(20), IN _restaurant_id CHAR(128))
    BEGIN
        UPDATE categories
            SET categories.name = _name
                WHERE categories.restaurant_id = _restaurant_id AND categories.category_id = _id;
        SELECT category_id, name FROM categories
            WHERE categories.restaurant_id = _restaurant_id AND categories.category_id = _id;
END//

CREATE PROCEDURE DS_category (IN _id INT, IN _restaurant_id CHAR(128))
    BEGIN
        SELECT category_id, name FROM categories
            WHERE category_id = _id;
        DELETE FROM categories
            WHERE categories.category_id = _id AND categories.restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE IS_dish (IN _name VARCHAR(30), _category VARCHAR(20), _cost DECIMAL(8, 2), _restaurant_id CHAR(128))
    BEGIN
        INSERT INTO dishes (name, category, cost, restaurant_id)
            VALUES (_name, _category, _cost, _restaurant_id);
   restaurant     SET @last_id = LAST_INSERT_ID();
        SELECT dish_id, name, category, cost
            FROM dishes
                WHERE dish_id = @last_id;
END//


CREATE PROCEDURE US_dish (IN _dish_id INT, _name VARCHAR(30), _category VARCHAR(20), _cost DECIMAL(8, 2), _restaurant_id CHAR(128))
    BEGIN
        UPDATE dishes
            SET name = _name, category = _category, cost = _cost
                WHERE dish_id = _dish_id;
        SELECT dish_id, name, category, cost
            FROM dishes
                WHERE dish_id = _dish_id AND restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE DS_dish (IN _dish_id INT, _restaurant_id CHAR(128))
    BEGIN
        SELECT dish_id, name, category, cost
            FROM dishes
                WHERE dish_id = _dish_id AND restaurant_id = _restaurant_id;
        DELETE FROM dishes
            WHERE dish_id = _dish_id AND restaurant_id = _restaurant_id;
END//


CREATE PROCEDURE US_ingredient (IN _product_id INT, _dish_id INT, _gPP DECIMAL(6, 2))
    BEGIN
        UPDATE ingredients
            SET gPP = _gPP
                WHERE product_id = _product_id AND dish_id = _dish_id;
        SELECT product_id, dish_id, gPP
            FROM ingredients
                WHERE product_id = _product_id AND dish_id = _dish_id;
END//

CREATE PROCEDURE DS_ingredient (IN _product_id INT, _dish_id INT)
    BEGIN
        SELECT product_id, dish_id, gPP
            FROM ingredients
                WHERE product_id = _product_id AND dish_id = _dish_id;
        DELETE FROM ingredients
                WHERE product_id = _product_id AND dish_id = _dish_id;
END//


CREATE EVENT delete_invitation
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
        DO CALL D_invitations(@something);
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id) ON DELETE CASCADE
SELECT ingredients.product_id, products.name, ingredients.gPP
        FROM ingredients
            JOIN products
                ON ingredients.product_id = products.product_id
                    WHERE ingredients.dish_id = 8;
                
CREATE PROCEDURE IS_supplier (IN _name VARCHAR(20), IN _email VARCHAR(30), IN _phone VARCHAR(20), IN _comertial VARCHAR(20), IN _restaurant_id CHAR(128))
BEGIN 
    INSERT INTO suppliers (name, email, phone, comertial, restaurant_id)
        VALUES(_name, _email, _phone, _comertial, _restaurant_id);
    SET @last_id = LAST_INSERT_ID(); 
    SELECT supplier_id, name, email, phone, comertial
        FROM suppliers
            WHERE supplier_id = @last_id AND restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE US_supplier (IN _supplier_id INT, IN _name VARCHAR(20), IN _email VARCHAR(30), IN _phone VARCHAR(20), IN _comertial VARCHAR(20), IN _restaurant_id CHAR(128))
BEGIN
    UPDATE suppliers
        SET name = _name, email = _email, phone = _phone, comertial = _comertial
            WHERE supplier_id = _supplier_id;
    SELECT supplier_id, name, email, phone, comertial
        FROM suppliers
            WHERE supplier_id = _supplier_id AND restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE DS_supplier (IN _supplier_id INT, IN _restaurant_id CHAR(128))
BEGIN
    SELECT supplier_id, name, email, phone, comertial
        FROM suppliers
            WHERE supplier_id = _supplier_id AND restaurant_id = _restaurant_id;
    DELETE FROM suppliers
        WHERE supplier_id = _supplier_id AND restaurant_id = _restaurant_id;
END//;

CREATE PROCEDURE IS_order (IN _supplier_id INT,IN _user_id INT, IN _restaurant_id CHAR(128))
    BEGIN
        INSERT INTO orders (supplier_id, user_id, restaurant_id)
            VALUES(_supplier_id, _user_id, _restaurant_id);
        SET @last_id = LAST_INSERT_ID();
        SELECT order_id, _supplier_id, _user_id, date
            FROM orders
                WHERE order_id = @last_id AND restaurant_id = _restaurant_id;
END//


SELECT orders.order_id, users.name AS madeBy, restaurants.name AS restaurant, supplier.name AS supplier
    FROM orders
        JOIN users, restaurants, suppliers
            ON (
                orders.user_id = users.user_id AND
                orders.restaurant_id = restaurants.restaurant_id AND
                orders.supplier_id = suppliers.supplier_id
                )
                WHERE order_id = _order_id AND restaurant_id = _restaurant_id;
                
CREATE PROCEDURE S_1_order (IN _order_id INT, IN _restaurant_id CHAR(128))
    BEGIN 
        SELECT orders.order_id, orders.date, users.name AS madeBy, suppliers.supplier_id, suppliers.name AS supplier, restaurants.name AS restaurant
            FROM orders
                JOIN users ON orders.user_id = users.user_id
                        LEFT JOIN suppliers ON orders.supplier_id = suppliers.supplier_id
                            LEFT JOIN restaurants ON orders.restaurant_id = restaurants.restaurant_id
                                WHERE orders.order_id = _order_id AND orders.restaurant_id = _restaurant_id
                                        ORDER BY date ASC
        LIMIT 1;
END//


CREATE PROCEDURE S_20_order (IN _restaurant_id CHAR(128))
    BEGIN 
        SELECT orders.order_id, orders.date, users.name AS madeBy, suppliers.name AS supplier, restaurants.name AS restaurant
            FROM orders
                JOIN users ON orders.user_id = users.user_id
                        LEFT JOIN suppliers ON orders.supplier_id = suppliers.supplier_id
                            LEFT JOIN restaurants ON orders.restaurant_id = restaurants.restaurant_id
                                WHERE orders.restaurant_id = _restaurant_id
                                    ORDER BY date DESC
        LIMIT 20;
END//;

CREATE PROCEDURE S_products_query (IN _restaurant_id CHAR(128), IN _expression VARCHAR(30), IN _offset INT)
    BEGIN 
        SELECT products.product_id, products.name, categories.name AS category, products.loss, products.price, products.cost
            FROM products
                JOIN categories
                    ON products.category_id = categories.category_id
                        WHERE products.restaurant_id = _restaurant_id AND products.name LIKE _expression
                            ORDER BY products.name ASC
                                LIMIT _offset, 5;
                                  SELECT COUNT(*) AS total_sum
                                        FROM products
                                            WHERE products.restaurant_id = _restaurant_id;
END//;


CREATE PROCEDURE U_dish_cost (IN _dish_id INT)
BEGIN
    SELECT SUM(ingredients.gPP * products.cost / 1000) INTO @cost
        FROM ingredients
            JOIN products
                ON ingredients.product_id = products.product_id
                    WHERE ingredients.dish_id = _dish_id;
                        UPDATE dishes
                            SET cost = @cost
                                WHERE dishes.dish_id = _dish_id;
END//


CREATE PROCEDURE U_restaurant_user (IN _role VARCHAR(6), IN _user_id INT, IN _restaurant_id CHAR(128))
BEGIN
    UPDATE users
        SET role = _role
            WHERE user_id = _user_id AND restaurant_id = _restaurant_id;
                SELECT user_id, email, name, role
                    FROM users
                        WHERE user_id = _user_id AND restaurant_id = _restaurant_id;
END//

CREATE PROCEDURE S_user_I_forgot_code (IN _email VARCHAR(30), IN _code CHAR(128))
BEGIN
    SELECT user_id INTO @id
        FROM users
            WHERE email = _email;
                SELECT user_id 
                    FROM users
                        WHERE email = _emal
                           INSERT INTO forgots (user_id, code)
                                            VALUES (@id, _code);
END//

CREATE PROCEDURE DS_forgot_code (IN _code CHAR(128))
BEGIN
  SELECT users.user_id, forgots.code
    FROM users
        JOIN forgots
            ON forgots.user_id = users.user_id
                WHERE forgots.code = _code;
    DELETE FROM forgots 
        WHERE code = _code;
END//

CREATE PROCEDURE IS_recipe (IN _dish_id INT, IN _minimum_production DECIMAL(6, 2), IN _recipe TEXT, IN _restaurant_id CHAR(128))
BEGIN
    INSERT INTO recipes (dish_id, minimum_production, recipe, restaurant_id)
        VALUES (_dish_id, _minimum_production, _recipe, _restaurant_id);
    SET @last_id = LAST_INSERT_ID(); 
    SELECT dishes.name, recipe_id, recipes.dish_id, recipe, minimum_production
        FROM recipes
            JOIN dishes
                ON recipes.dish_id = dishes.dish_id
                    WHERE recipe_id = @last_id;
END//

CREATE PROCEDURE US_recipe (IN _recipe_id INT, IN _minimum_production DECIMAL(6, 2), IN _recipe TEXT)
BEGIN
    UPDATE recipes
        SET recipe = _recipe, minimum_production = _minimum_production
            WHERE recipe_id = _recipe_id;
    SELECT dishes.name, recipe_id, recipes.dish_id, recipe, minimum_production
        FROM recipes
            JOIN dishes
                ON recipes.dish_id = dishes.dish_id
            WHERE recipe_id = _recipe_id;
END//

CREATE PROCEDURE DS_recipe (IN _recipe_id INT)
    SELECT recipe_id, dish_id, recipe
        FROM recipes
            WHERE recipe_id = _recipe_id;
    DELETE FROM recipes
        WHERE recipe_id = _recipe_id;
END//

CREATE PROCEDURE S_stats (IN _restaurant_id CHAR(128))
BEGIN
    SELECT  (
            SELECT COUNT(*)
                FROM dishes
                    WHERE restaurant_id = _restaurant_id
            ) AS dishes,
            (
            SELECT COUNT(*)
                FROM products
                    WHERE restaurant_id = _restaurant_id
            ) AS products,
            (
            SELECT COUNT(*)
                FROM suppliers
                    WHERE restaurant_id = _restaurant_id
            ) AS suppliers,
            (
            SELECT COUNT(*)
                FROM orders
                    WHERE restaurant_id = _restaurant_id
            ) AS orders,
            (
            SELECT COUNT(*)
                FROM recipes
                    WHERE restaurant_id = _restaurant_id
            ) AS recipes,
            (
            SELECT COUNT(*)
                FROM users
                    WHERE restaurant_id = _restaurant_id
            ) AS users;
END//