DROP PROCEDURE IF EXISTS `update_product`;
DELIMITER ;;
CREATE PROCEDURE `update_product` (
    IN p_name VARCHAR(100),
    IN p_price INT,
    IN p_description VARCHAR(1000),
    IN p_stock INT,
    IN p_weight INT,
    IN p_category INT,
    IN p_location VARCHAR(200),
    IN p_product_id INT,
    IN p_session_id CHAR(64)
)
proc:BEGIN
    IF (SELECT COUNT(session_id) FROM sessions WHERE session_id = LOWER(p_session_id)) = 0 OR p_session_id IS NULL THEN
        -- SELECT -1 AS 'status', 'No user logged in' AS 'msg', 'update_product' AS 'proc';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'update_product: No user logged in';
        LEAVE proc;
    END IF;
    SELECT user_id INTO @user_id FROM sessions WHERE session_id = LOWER(p_session_id);
    IF (SELECT COUNT(id) FROM products WHERE user_id = @user_id AND id = p_product_id) = 0 OR p_product_id IS NULL THEN
        -- SELECT -2 AS 'status', 'Product does not exist' AS 'msg', 'update_product' AS 'proc';
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'update_product: Product does not exist';
        LEAVE proc;
    END IF;
    CALL update_product_name(p_name, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'update_product: update_product_name';
        LEAVE proc;
    END IF;
    CALL update_product_price(p_price, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = 'update_product: update_product_price';
        LEAVE proc;
    END IF;
    CALL update_product_description(p_description, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45004' SET MESSAGE_TEXT = 'update_product: update_product_description';
        LEAVE proc;
    END IF;
    CALL update_product_stock(p_stock, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45005' SET MESSAGE_TEXT = 'update_product: update_product_stock';
        LEAVE proc;
    END IF;
    CALL update_product_weight(p_weight, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45006' SET MESSAGE_TEXT = 'update_product: update_product_weight';
        LEAVE proc;
    END IF;
    CALL update_product_category(p_category, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = 'update_product: update_product_category';
        LEAVE proc;
    END IF;
    CALL update_product_location(p_location, p_product_id, @user_id, @status);
    IF @status != 0 THEN
        SIGNAL SQLSTATE '45008' SET MESSAGE_TEXT = 'update_product: update_product_location';
        LEAVE proc;
    END IF;
    SELECT 0 AS 'status', 'update_product' AS 'proc';
END ;;
DELIMITER ;