DROP   PROCEDURE Base_Users_Admin;
DELIMITER //
CREATE PROCEDURE Base_Users_Admin
(
    $sid   TEXT,
    $USER  INT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    CALL _Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

    IF NOT( 'ADMIN' = @idtype ) THEN

        CALL Notice( 'Base_Users_Admin: INVALID_AUTHORISATION' );

    ELSEIF $USER THEN

        SELECT *
        FROM  view_base_users_admin
        WHERE USER = $USER;

    ELSE

        SELECT   *
        FROM     view_base_users_admin
        ORDER BY given_name, family_name, email;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Admin_Update;
DELIMITER //
CREATE PROCEDURE Base_Users_Admin_Update
(
    $Sid            TEXT,
    $USER           INT,
    $user_mfa_type  TEXT,
    $given_name     TEXT,
    $family_name    TEXT,
    $email          TEXT,
    $mobile         TEXT,
    $password       TEXT,
    $type           TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    CALL _Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT( 'ADMIN' = @idtype ) THEN

        CALL Notice( 'Base_Users_Admin_Update: INVALID_AUTHORISATION' );

    ELSE

        UPDATE base_users
        SET
            user_mfa_type = OrNull( $user_mfa_type            ), 
            given_name    = IFNULL( $given_name , given_name  ),
            family_name   = IFNULL( $family_name, family_name ),
            email         = IFNULL( $email      , email       ),
            mobile        = IFNULL( $mobile     , mobile      )

        WHERE USER = $USER;

        IF NOT ISNULL($password) AND "" != $password THEN
            CALL _Base_Users_Set_Password( $USER, $password );
        END IF;

        IF NOT ISNULL($type)     AND "" != $type THEN
            UPDATE base_users_uids
            SET    type = $type
            WHERE  USER = $USER;
        END IF;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Create_Admin;
DELIMITER //
CREATE PROCEDURE Base_Users_Create_Admin
(
  $password                      CHAR(99)
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $send_email INT DEFAULT 0;

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT IsLocalCaller() THEN

        CALL Notice( 'Base_Users_Create_Admin: INVALID_INVOCATION' );

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

        CALL Notice( 'Base_Users_Create_Admin: INVALID_AUTHORISATION' );

    ELSE

        CALL Base_Users_Create_Quietly( 'admin', $password, 'Admin', 'Account', 'ADMIN', $send_email );

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Admin_Reset_Password;
DELIMITER //
CREATE PROCEDURE Base_Users_Admin_Reset_Password
(
  $email        CHAR(99),
  $new_password CHAR(99)
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $USER  INT  DEFAULT 0;

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT IsLocalCaller() THEN

        CALL Notice( 'Base_Users_Admin_Reset_Password: INVALID_INVOCATION' );

    ELSEIF NOT IsRootCaller() THEN

        CALL Notice( 'Base_Users_Admin_Reset_Password: INVALID_AUTHORISATION' );

    ELSE

        SELECT  USER
        INTO   $USER
        FROM    base_users
        WHERE   user_deleted = 0
        AND     email        = $email;

        IF $USER THEN

            CALL _Base_Users_Set_Password( $USER, $new_password );

        END IF;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Admin_Set_Password;
DELIMITER //
CREATE PROCEDURE Base_Users_Admin_Set_Password
(
  $email_provisional  TEXT,
  $new_password       TEXT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT IsLocalCaller() THEN

        CALL Error( 'Base_Users_Admin_Reset_Password: INVALID_INVOCATION' );

    ELSEIF NOT $USER THEN

        CALL Warning( "Base_Users_Admin_Set_Password: INVALID_PARAMETERS(email_provisional)" );

    ELSE BEGIN

        DECLARE $USER  INT  DEFAULT 0;

        SET $USER = Base_Users_User_From_EmailProvisional( $email_provisional );

        CALL Base_Users_Activations_Create_Out
        (
            $USER,
            $email_provisional,
            $token  # OUT
        );

        IF NOT Empty( $token ) THEN
            CALL _Base_Users_Activations_Confirm_Account( $token, $new_password  );
        END IF;

    END; END IF;

END
//
DELIMITER ;

DROP   PROCEDURE base_users_reset_password;
DELIMITER //
CREATE PROCEDURE base_users_reset_password
(
  $Sid          CHAR(64),
  $USER         INT(11),
  $new_password CHAR(99)
)
SQL SECURITY INVOKER
BEGIN

    CALL _Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF "ADMIN" = @idtype THEN

        CALL base_users_set_password( $USER, $new_password );

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Users_Retrieve_All;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_All
(
$Sid                           CHAR(64)
)
SQL SECURITY INVOKER
BEGIN

    CALL Error( "Base_Users_Retrieve_All renamed to _Admin_Base_Users_Retrieve_All" );

END
//
DELIMITER ;

DROP   PROCEDURE _Admin_Base_Users_Retrieve_All;
DELIMITER //
CREATE PROCEDURE _Admin_Base_Users_Retrieve_All
(
$Sid                           TEXT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSE

        CALL _Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

        IF "ADMIN" != @idtype THEN

            CALL Notice( '_Base_Users_Retrieve_All: INVALID_AUTHORISATION' );

        ELSE

            SELECT   *
            FROM     view_base_users
            ORDER BY USER DESC;

        END IF;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Admin_Tables;
DELIMITER //
CREATE PROCEDURE Admin_Tables
()
SQL SECURITY INVOKER
BEGIN

    DECLARE $current_db TEXT DEFAULT '';

    SET $current_db = DATABASE();

    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        TABLE_ROWS,
        DATA_LENGTH,
        FLOOR(DATA_LENGTH /    1000) As KB,
        FLOOR(DATA_LENGTH / 1000000) As MB

    FROM     information_schema.TABLES
    WHERE    TABLE_SCHEMA = $current_db
    AND      NOT ISNULL(TABLE_ROWS)
    ORDER BY DATA_LENGTH;

END
//
DELIMITER ;

DROP   PROCEDURE Activate;
DELIMITER //
CREATE PROCEDURE Activate
()
SQL SECURITY INVOKER
BEGIN

    UPDATE mysql.proc
    SET    security_type = 'DEFINER'
    WHERE  db            =  DATABASE()
    AND    type          = 'FUNCTION'
    AND    name          = 'RetrieveParametersFor'
    ;

END
//
DELIMITER ;

DROP   PROCEDURE Deactivate;
DELIMITER //
CREATE PROCEDURE Deactivate
()
SQL SECURITY INVOKER
BEGIN

    UPDATE mysql.proc
    SET    security_type = 'INVOKER'
    WHERE  db            =  DATABASE()
    AND    type          = 'FUNCTION'
    AND    name          = 'RetrieveParametersFor'
    ;

END
//
DELIMITER ;