DROP   PROCEDURE Base_Users_Activations_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Create
(
    $USER   INT,
    $email  TEXT
)
BEGIN

    DECLARE $token TEXT;

    CALL Base_Users_Activations_Create_Out( $USER, $email, $token );

    SELECT $token AS token;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Activations_Create_Out;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Create_Out
(
    $USER   INT,
    $email  TEXT,
OUT $token  TEXT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

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

    ELSE

        IF $USER THEN

            SET $token = GenerateSalt();

            CALL spgen_base_users_activations_replace
            (
                $USER,
                0,
                $email,
                $token,
                NOW(),
                0,
                0
            );
        END IF;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Activations_Confirm_Account;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Confirm_Account
(
  $token     TEXT,
  $password  TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN

    DECLARE $USER                  INT   DEFAULT  0;
    DECLARE $user_activation_email TEXT  DEFAULT '';
    DECLARE $valid_email           BOOL  DEFAULT  0;

    SELECT USER,  user_activation_email
    INTO  $USER, $user_activation_email
    FROM   base_users_activations
    WHERE  user_activation_completed = 0
    AND    user_activation_token     = TRIM( $token );

    IF @@read_only THEN

        CALL Throw( 'READ_ONLY' );

    ELSEIF NOT $USER THEN

        CALL Throw( 'Base_Users_Activations_Confirm_Account: INVALID_TOKEN' );

    ELSEIF "WEAK" = CalculatePasswordStrength( $password ) THEN

        CALL Throw( 'Sorry, please entere a stronger password.' );

    ELSEIF NOT "" = TRIM( $password ) THEN

        #
        #   Confirm the account
        #
        CALL Base_Users_Confirm_Account( $USER, $user_activation_email, $valid_email );
        
        IF $valid_email THEN

            #
            #   Update password
            #
            CALL base_users_set_password( $USER, $password );
      
            #
            #   Mark activation token as used
            #
            UPDATE base_users_activations
            SET    user_activation_completed = NOW()
            WHERE  user_activation_completed = 0
            AND    user_activation_token     = $token;
        
        END IF;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Activations_Confirm_Account_And_Authenticate;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Confirm_Account_And_Authenticate
(
  $token                           CHAR(64)
)
BEGIN

DECLARE $USER              INT;
DECLARE $email             TEXT;
DECLARE $email_provisional TEXT;
DECLARE $sessionid         TEXT;

IF @@read_only THEN

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

ELSE

    SELECT USER INTO $USER FROM base_users_activations WHERE user_activation_token=$token;

    SET $sessionid = "";

    IF 0 != $USER THEN
        SELECT email, email_provisional INTO $email, $email_provisional
        FROM base_users WHERE USER=$USER;
        
        IF "" != $email_provisional THEN
            SET $email = $email_provisional;
        END IF;

        UPDATE base_users SET email=$email, email_provisional='', user_status='CONFIRMED' WHERE USER=$USER;
        DELETE FROM base_users_activations WHERE user_activation_token=$token;

        SET $sessionid = MD5( concat( $token, NOW() ) );
        REPLACE INTO base_users_sessions
        (        sid,  email, created, updated,                  expiry )
        VALUES
        ( $sessionid, $email,   NOW(),   NOW(), UNIX_TIMESTAMP() + 1000 );

    END IF;

    SELECT $sessionid AS sessionid;

END IF;

END
//
DELIMITER ;