DROP PROCEDURE IF EXISTS `get_buyer_orders`;
DELIMITER ;;
CREATE PROCEDURE `get_buyer_orders` (
    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 = 'get_buyer_orders: No user logged in';
        LEAVE proc;
    END IF;
    SELECT user_id INTO @user_id FROM sessions WHERE session_id = LOWER(p_session_id);
    SELECT O.id, O.seller_id, U.name AS 'seller_name', U.phone_number, O.status, OP.product_id, P.name, OP.quantity, OP.amount, 'get_buyer_orders' AS 'proc' FROM orders O
    JOIN order_products OP
    ON O.id = OP.order_id
    JOIN products P
    ON OP.product_id = P.id
    JOIN users U
    ON O.seller_id = U.id
    WHERE O.buyer_id = @user_id;
END ;;
DELIMITER ;
