DROP PROCEDURE IF EXISTS `add_product`;
DELIMITER ;;
CREATE PROCEDURE `add_product` (
    IN p_product_name VARCHAR(100),
    IN p_product_price FLOAT,
    IN p_product_description VARCHAR(1000),
    IN p_product_stock INT,
    IN p_product_category INT,
    IN p_session_id CHAR(64)
)
proc:BEGIN
    -- must have input sanitation
    IF (SELECT COUNT(session_id) FROM sessions WHERE session_id = LOWER(p_session_id)) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'add_product: No user logged in';
        LEAVE proc;
    END IF;
    IF p_product_name = '' OR p_product_name IS NULL THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'add_product: No product name';
        LEAVE proc;
    END IF;
    IF p_product_price = '' OR p_product_price IS NULL THEN
        SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'add_product: No product price';
        LEAVE proc;
    END IF;
    IF p_product_description = '' OR p_product_description IS NULL THEN
        SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = 'add_product: No product description';
        LEAVE proc;
    END IF;
    IF p_product_stock = '' OR p_product_stock IS NULL THEN
        SIGNAL SQLSTATE '45004' SET MESSAGE_TEXT = 'add_product: No product stock';
        LEAVE proc;
    END IF;
    IF p_product_category = '' OR p_product_category IS NULL THEN
        SIGNAL SQLSTATE '45005' SET MESSAGE_TEXT = 'add_product: No product category';
        LEAVE proc;
    END IF;
    SELECT user_id INTO @user_id FROM sessions WHERE session_id = LOWER(p_session_id);
    INSERT INTO `products` (`user_id`, `name`, `price`, `description`, `stock`, `weight`, `category`, `location`, `listed_timestamp`, `pending`, `archived`)
    VALUES (@user_id, p_product_name, p_product_price, p_product_description, p_product_stock, 69420, p_product_category, 'Sample location for product', NOW(), false, false);
    SELECT 0 AS 'status', LAST_INSERT_ID() AS 'product_id', 'add_product' AS 'proc';
END ;;
DELIMITER ;