DROP FUNCTION IF EXISTS CheckUsernameMac;
DELIMITER //

CREATE FUNCTION CheckUsernameMac(pUsername VARCHAR(20), pMac VARCHAR(20))
    RETURNS INT
BEGIN
    DECLARE vResult         INT DEFAULT 1;
p:BEGIN
    /** -1 = Deny access    *
     *   0 = Allow access   *
     *   1 = Bann user     **/
     DECLARE vUserHasMacs BIT;
     DECLARE vMacAllowed BIT;
     DECLARE vUsersMac BIT;
     DECLARE vBlacklisted BIT;
     DECLARE vWhitelisted BIT;

     SELECT
        UserHasMacs(pUsername) INTO vUserHasMacs;
     SELECT
        MacAllowed(pMac) INTO vMacAllowed;
     SELECT
        MacRegisteredForUser(pUsername, pMac)
            INTO vUsersMac;
     SELECT
        UserBlacklisted(pUsername) INTO vBlacklisted;
     SELECT
        UserWhitelisted(pUsername) INTO vWhitelisted;

    IF(vWhitelisted)
    THEN
        SET vResult = 0;
        LEAVE p;
    END IF;
    IF(vBlacklisted)
    THEN
        SET vResult = -1;
        LEAVE p;
    END IF;

    IF
    NOT vMacAllowed
    THEN
        SET vResult = 1;
        LEAVE p;
    END IF;
    IF
    NOT vUsersMac
    AND vUserHasMacs
    THEN
        CALL BannMac(pMac);
        SET vResult = 1;
        LEAVE p;
    END IF;
    IF
            vMacAllowed
    AND NOT vUserHasMacs
    THEN
        CALL RegisterMac(pUsername, pMac);
        SET vResult = 0;
        LEAVE p;
    END IF;
    IF
    NOT vMacAllowed
    THEN
        SET vResult = 1;
        LEAVE p;
    END IF;
END; # p:END
    RETURN vResult;
END //