DROP   PROCEDURE Base_Users_Tokens_Sent;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Sent
(
    $token              CHAR(64)
)
SQL SECURITY DEFINER
BEGIN

UPDATE base_users_tokens SET token_sent = NOW() WHERE token = $token AND 0 = token_sent;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Tokens_Used;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Used
(
    $token              CHAR(64)
)
SQL SECURITY DEFINER
BEGIN

UPDATE base_users_tokens SET token_used = NOW() WHERE token = $token AND 0 = token_used AND NOW() <= token_expiry;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Tokens_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Create
(
    $token_type         CHAR(50),
    $USER               INT,
    $token_expiry_days  INT,
OUT $token              CHAR(64)
)
SQL SECURITY INVOKER
BEGIN

DECLARE $token_expiry  DATETIME  DEFAULT  0;

IF @@read_only THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';

ELSEIF NOT( IsEventScheduler() OR IsRootCaller() ) THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Tokens_Create';

ELSE

    #
    #   Ensure unique token.
    #

    SET $token = GenerateSalt();
    WHILE EXISTS( SELECT * FROM base_users_tokens WHERE token=$token ) DO
        SET $token = GenerateSalt();
    END WHILE;

    #
    #   Set 3 day token expiry
    #

    SET $token_expiry = DATE_ADD( NOW(), INTERVAL $token_expiry_days DAY );

    REPLACE INTO base_users_tokens
    (  TOKEN_ID,  USER,  token_created,  token_expiry_days,  token_expiry,  token_type,  token )
    VALUES
    (         0, $USER,          NOW(), $token_expiry_days, $token_expiry, $token_type, $token );

END IF;

END
//
DELIMITER ;