
DROP PROCEDURE IF EXISTS insert_new_user;
DROP PROCEDURE IF EXISTS validate_user;
DROP PROCEDURE IF EXISTS remove_item;
DROP PROCEDURE IF EXISTS get_all_items;
DROP PROCEDURE IF EXISTS add_to_groceries;
DROP PROCEDURE IF EXISTS remove_from_groceries;

CREATE TABLE ITEMS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    ITEM_NAME VARCHAR(255) NOT NULL,
    AMOUNT FLOAT NOT NULL,
    UNIT ENUM("tsp", "tbsp", "fl oz", "cup", "pint", "quart", "ounce", "lb"),
    USER_ID INT,
    FOREIGN KEY (USER_ID) REFERENCES USER_LOGIN(ID)
);

CREATE TABLE RECIPES(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    RECIPE_NAME VARCHAR(255) NOT NULL,
    USER_ID INT,
    FOREIGN KEY (USER_ID) REFERENCES USER_LOGIN(ID)
);

CREATE TABLE RECIPE_ITEMS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    RECIPE_ID INT,
    ITEM_ID INT,
    AMOUNT FLOAT NOT NULL,
    UNIT ENUM("tbsp", "tsp", "cup", "oz", "lb"),
    USER_ID INT,
    FOREIGN KEY (RECIPE_ID) REFERENCES RECIPES(ID),
    FOREIGN KEY (ITEM_ID) REFERENCES ITEMS(ID),
    FOREIGN KEY (USER_ID) REFERENCES USER_LOGIN(ID)
);

CREATE TABLE GROCERY_LIST(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    AMOUNT FLOAT NOT NULL,
    UNIT ENUM("tbsp", "tsp", "cup", "oz", "lb"),
    ITEM_ID INT,
    USER_ID INT,
    FOREIGN KEY (ITEM_ID) REFERENCES ITEMS(ID),
    FOREIGN KEY (USER_ID) REFERENCES USER_LOGIN(ID)
);

DELIMITER //
CREATE PROCEDURE insert_new_user(IN input_email VARCHAR(255), IN input_pass VARCHAR(255), IN input_random_bytes CHAR(64))
BEGIN
    DECLARE emailExists INT;
    DECLARE passLength INT;
    DECLARE tokenID CHAR(255);
    DECLARE userID INT;
    DECLARE salt VARCHAR(255);

    SELECT LENGTH(input_pass) INTO passLength;
    ELSE
        SET salt = input_random_bytes;

        SET tokenID = UUID();
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE validate_user(IN input_email VARCHAR(255), IN input_pass VARCHAR(255))
BEGIN
    DECLARE isValid INT;
    DECLARE userID INT;
    DECLARE userSalt VARCHAR(255);
    DECLARE tokenID CHAR(255);
    DECLARE hashedPass CHAR(255);
        SET hashedPass = SHA2(CONCAT(input_pass, userSalt), 256);
            END IF;
        END IF;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE remove_item(IN input_token CHAR(255), IN input_item_id INT, IN input_amount FLOAT)
BEGIN
    DECLARE isValid INT;
    DECLARE userID INT;
    DECLARE oldAmount FLOAT;
    DECLARE newAmount FLOAT;

        SELECT COUNT(*) INTO isValid FROM ITEMS WHERE ID = input_item_id AND USER_ID = userID;

        SELECT AMOUNT into oldAmount FROM ITEMS WHERE ID = input_item_id;

        SET newAmount = oldAmount - input_amount;

        IF isValid > 0 THEN
            UPDATE ITEMS SET AMOUNT = newAmount WHERE ID = input_item_id AND USER_ID = userID;
        END IF;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE get_all_items(IN input_token CHAR(255))
BEGIN
    DECLARE isValid INT;
    DECLARE userID INT;
        SELECT * FROM ITEMS WHERE USER_ID = userID;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE add_to_groceries(IN input_token CHAR(255), IN input_item_id INT, IN input_amount INT)
BEGIN
    DECLARE isValid INT;
    DECLARE userID INT;
    DECLARE itemUnit ENUM("tsp", "tbsp", "fl oz", "cup", "pint", "quart", "ounce", "lb");
        SELECT COUNT(*) INTO isValid FROM ITEMS WHERE ID = input_item_id AND USER_ID = userID;

        IF isValid > 0 THEN
            SELECT UNIT INTO itemUnit FROM ITEMS WHERE ID = input_item_id AND USER_ID = userID;

            INSERT INTO GROCERY_LIST (ITEM_ID, AMOUNT, UNIT, USER_ID) VALUES (input_item_id, input_amount, itemUnit, userID);
        END IF;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE remove_from_groceries(IN input_token CHAR(255), IN input_item_id INT)
BEGIN
    DECLARE isValid INT;
    DECLARE userID INT;
    DECLARE itemUnit ENUM("tsp", "tbsp", "fl oz", "cup", "pint", "quart", "ounce", "lb");
        SELECT COUNT(*) INTO isValid FROM GROCERY_LIST WHERE ID = input_item_id AND USER_ID = userID;

        IF isValid > 0 THEN
            DELETE FROM GROCERY_LIST WHERE ID = input_item_id AND USER_ID = userID;
        END IF;
    END IF;
END //
DELIMITER ;

-- Testing time wooooo
insert into RECIPES (RECIPE_NAME) values ("Pasta");
insert into RECIPES (RECIPE_NAME) values ("Pizza");

insert into ITEMS (ITEM_NAME, AMOUNT, UNIT) values ("Pepperoni", 12, 4);

insert into RECIPE_ITEMS (RECIPE_ID, ITEM_ID, AMOUNT, UNIT) values (2, 1, 2, 4);