DELIMITER //
  DROP PROCEDURE IF EXISTS `authchecktest` //
  CREATE PROCEDURE `authchecktest` (IN client VARCHAR(64) , IN callingid VARCHAR(17), IN hidden VARCHAR(64))

BEGIN

DECLARE bypass_tmp VARCHAR(17);
DECLARE macuse_tmp VARCHAR(17);
DECLARE free_tmp INT(1);

  IF (SELECT EXISTS (SELECT active FROM isp.users WHERE address = client)) THEN
    SELECT locknext INTO free_tmp FROM isp.pppoeoptions WHERE address = client ;
    SELECT phone INTO bypass_tmp FROM isp.phonewhitelist WHERE phone = callingid ;
      IF (free_tmp > 0) THEN
        UPDATE isp.pppoeoptions SET origin = callingid WHERE address = client ;
        UPDATE isp.pppoeoptions SET locknext = 0 WHERE address = client ;
        SELECT callingid INTO macuse_tmp ;
      ELSE
        IF ( bypass_tmp ) THEN
          UPDATE isp.pppoeoptions SET origin = bypass_tmp WHERE address = client ;
          SELECT bypass_tmp INTO macuse_tmp ;
        ELSE
          SELECT origin INTO macuse_tmp FROM pppoeoptions WHERE address = client AND origin IS NOT NULL AND TRIM(origin) <> "" ;
        END IF;
      END IF;
      ( SELECT 1 as 'id', client as username, 'Crypt-Password' as attribute, crypt as value, '==' as op FROM users WHERE address = client ) UNION ALL
      -- ( SELECT 2 as 'id', client as username, 'Pool-Name' as attribute, pool as value, ':=' as op FROM pppoeoptions WHERE address = client AND pool IS NOT NULL ) UNION ALL
      ( SELECT 3 as 'id', client as username, 'Auth-Type' as attribute, 'Accept' as value, '==' as op ) UNION ALL
      ( SELECT 4 as 'id', client as username, 'Calling-Station-Id' as attribute, COALESCE ( macuse_tmp , callingid ) as value, '==' as op );
      -- UPDATE isp.radippool SET nasipaddress = '', callingstationid = '', expiry_time = NULL, username = '', pool_key = '' WHERE username = client ;
  ELSE
    ( SELECT 1 as 'id', client as username, 'Auth-Type' as attribute, 'Reject' as value, ':=' as op ) UNION ALL
    ( SELECT 2 as 'id', client as username, 'Simultaneous-Use' as attribute, 0 as value, ':=' as op );
  END IF;

END //
DELIMITER ;