DROP PROCEDURE IF EXISTS `get_audit_trails`;
DELIMITER ;;
CREATE PROCEDURE `get_audit_trails` (
    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 user.type = 0 AND sess.session_id = p_session_id) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'get_audit_trails: No Access';
        LEAVE proc;
    END IF;
    SELECT user.id, user_type.description AS user_description,user.username,user.email,audit.action_timestamp, act_type.description AS type_description FROM audit_trail AS audit INNER JOIN users AS user ON audit.user_id = user.id INNER JOIN action_type_t as act_type ON audit.action_type = act_type.id INNER JOIN user_type_t AS user_type ON user_type.user_type = user.type ORDER BY audit.action_timestamp DESC;
END ;;
DELIMITER ;