DROP PROCEDURE IF EXISTS `get_unread_notifications`;
DELIMITER ;;
CREATE PROCEDURE `get_unread_notifications` (
    IN p_session_id CHAR(64)
)
proc:BEGIN
    IF (SELECT COUNT(user.id) FROM users AS user INNER JOIN sessions AS sess ON user.id = sess.user_id WHERE sess.session_id = p_session_id) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'get_notifications: Login Required';
        LEAVE proc;
    END IF;
    SELECT * FROM notifications notif INNER JOIN  products prod ON prod.id = notif.product_id INNER JOIN sessions sess ON sess.user_id = notif.user_id WHERE sess.session_id = p_session_id AND notif.is_seen = false;
END ;;
DELIMITER ;
