DROP PROCEDURE IF EXISTS `update_order_status`;
DELIMITER ;;
CREATE PROCEDURE `update_order_status` (
    IN p_order_id INT,
    IN p_order_status 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
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'update_order_status: 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 orders WHERE seller_id = @user_id) = 0 THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'update_order_status: Seller has no orders';
        LEAVE proc;
    END IF;
    IF (SELECT COUNT(id) FROM orders WHERE id = p_order_id AND seller_id = @user_id) = 0 OR p_order_id IS NULL THEN
        SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'update_order_status: Order does not exist';
        LEAVE proc;
    END IF;
    IF p_order_status > 3 OR p_order_status < 1 OR p_order_status IS NULL THEN
        SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = 'update_order_status: Invalid order status';
        LEAVE proc;
    END IF;
    UPDATE orders SET status = p_order_status WHERE id = p_order_id AND seller_id = @user_id;
    SELECT 0 AS 'status', 'update_order_status' AS 'proc';
END ;;
DELIMITER ;