DROP PROCEDURE IF EXISTS `logout`;
DELIMITER ;;
CREATE PROCEDURE `logout`(
    IN p_session_id CHAR(64)
)
proc:BEGIN
    DECLARE user_id_var INT;
    DECLARE user_type_var INT;
    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', 'Invalid token' AS 'msg', 'logout' AS 'proc';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'logout: Invalid token';
        LEAVE proc;
    END IF;
    SELECT user.id, user.type INTO user_id_var, user_type_var FROM sessions AS session INNER JOIN users AS user ON user.id = session.user_id WHERE session_id = LOWER(p_session_id);
    INSERT INTO `audit_trail` (`action_type`,`user_id`,`user_type`,`action_timestamp`) VALUES (2,user_id_var,user_type_var,NOW());
    SELECT 0 AS 'status', 'logged out' AS 'proc';
END ;;
DELIMITER ;