DROP   PROCEDURE Base_Organisations;
DELIMITER //
CREATE PROCEDURE Base_Organisations
(
    $Sid       TEXT,
    $ORG_ID    INT,
    $org_name  TEXT,
    $limit     INT,
    $offset    INT,
    $order     TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    CALL CheckLimitOffset( $limit, $offset );

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

    IF NOT ( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN 

        CALL Notice( 'Base_Organisations: INVALID_AUTHORISATION' );

    ELSEIF @idtype LIKE '%ADMIN%' THEN

        SELECT    *
        FROM      base_organisations
        WHERE     org_deleted = 0
        AND      (ORG_ID      = $ORG_ID   OR $ORG_ID   =  0)
        AND      (org_name    = $org_name OR $org_name = '')
        ORDER BY  org_name
        LIMIT    $limit
        OFFSET   $offset;

    ELSE

        SELECT    *
        FROM      base_organisations
        WHERE     org_deleted = 0
        AND      (ORG_ID      = $ORG_ID   OR $ORG_ID   =  0)
        AND      (org_name    = $org_name OR $org_name = '')
        AND       ORG_ID IN
        (
            SELECT ORG_ID
            FROM   base_organisations_users
            WHERE  USER = @USER
        )
        ORDER BY  org_name
        LIMIT    $limit
        OFFSET   $offset;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Organisations_Replace;
DELIMITER //
CREATE PROCEDURE Base_Organisations_Replace
(
    $Sid       TEXT,
    $ORG_ID    INT,
    $org_name  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 @idtype LIKE '%ADMIN%' THEN

        CALL Error( 'Base_Organisations_Replace: Admin should call Admin_Base_Organisations_Replace' );

    ELSEIF NOT @USER THEN

        CALL Notice( 'Base_Organisations_Replace: INVALID_AUTHORISATION' );

    ELSEIF $ORG_ID AND NOT EXISTS
    (
        SELECT *
        FROM   base_organisations_users
        WHERE  ORG_ID = $ORG_ID
        AND    USER   = @USER
        AND    org_user_role LIKE '%[OWNER]%'
    )
    THEN

        CALL Warning( 'Base_Organisations_Replace: INVALID_PARAMTERS(ORG_ID)' );

    ELSE BEGIN

        DECLARE $org_guid  TEXT  DEFAULT NULL;

        IF NOT $ORG_ID THEN
            CALL Base_Guids_Create( 'Organisation', 0, $org_guid );
        END IF;

        CALL spgen_base_organisations_replace
        (
            $ORG_ID,
            $org_guid,
            $org_name,
            $org_code
        );

        #
        #   $org_guid is only non null for new organisation
        #

        IF NOT ISNULL( $org_guid ) THEN
            CALL Base_Guids_Update( $org_guid, $ORG_ID );

            REPLACE INTO base_organisations_users
            (  USER,  ORG_ID,  org_user_role )
            VALUES
            ( @USER, $ORG_ID, '[OWNER]' );

        END IF;

        DO LAST_INSERT_ID( $ORG_ID );

    END; END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Organisations_Delete;
DELIMITER //
CREATE PROCEDURE Base_Organisations_Delete
(
    $Sid       TEXT,
    $ORG_ID    INT
)
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 ( @idtype LIKE '%ADMIN%' ) THEN 

        CALL Notice( 'Base_Organisations_Delete: INVALID_AUTHORISATION' );

    ELSEIF NOT EXISTS
    (
        SELECT *
        FROM   base_organisations
        WHERE  ORG_ID = $ORG_ID
    )
    THEN

        CALL Warning( 'Base_Organisations_Delete: INVALID_PARAMTERS(ORG_ID)' );

    ELSE

        UPDATE base_organisations
        SET
            org_deleted = NOW()

        WHERE ORG_ID = $ORG_ID;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Organisations_Users;
DELIMITER //
CREATE PROCEDURE Base_Organisations_Users
(
    $Sid                              TEXT,
    $apikey                           TEXT,
    $ORG_ID                           INT,
    $ORG_USER_ID                      INT,
    $order                            TEXT,
    $limit                            INT,
    $offset                           INT
)
SQL SECURITY INVOKER
BEGIN

    CALL CheckLimitOffset( $limit, $offset );

    CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ORG_ID, @email, @USER, @idtype, @role );

    IF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN

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

    ELSEIF NOT( @role LIKE '%SELECT%' ) THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Base_Organisations_Users';

    ELSEIF 'org_user_role' = $order OR '' = $order THEN

        SELECT
            USER,
            ORG_ID,
            ORG_USER_ID,
            org_user_role,
            given_name,
            family_name,
            email

        FROM      base_organisations_users
        LEFT JOIN base_users USING (USER)
        WHERE     ORG_ID                          = $ORG_ID
        AND       base_organisations_user_deleted = 0
        AND       (0  = $ORG_ID                         OR ORG_ID                          = $ORG_ID)
        AND       (0  = $ORG_USER_ID                    OR ORG_USER_ID                     = $ORG_USER_ID)
        ORDER BY  org_user_role
        LIMIT     $limit
        OFFSET    $offset;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Organisations_Users_Replace;
DELIMITER //
CREATE PROCEDURE Base_Organisations_Users_Replace
(
    $Sid                              TEXT,
    $apikey                           TEXT,
    $USER                             INT,
    $ORG_ID                           INT,
    $org_user_role                    TEXT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $ALT_ORG_ID  INT  DEFAULT 0;

    CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ALT_ORG_ID, @email, @USER, @idtype, @role );

    IF NOT @idtype = "ADMIN" THEN
        SET $ORG_ID = $ALT_ORG_ID;
    END IF;

    IF @@read_only THEN

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

    ELSEIF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN

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

    ELSEIF NOT( @role LIKE '%REPLACE%' ) THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Base_Organisations_Users_Replace';

    ELSEIF NOT $USER THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_PARAMETERS IN Base_Organisations_Users_Replace';

    ELSE

        IF NOT EXISTS( SELECT * FROM base_organisations_users WHERE ORG_ID=$ORG_ID AND USER=$USER ) THEN

            REPLACE INTO base_organisations_users
            (  USER,  ORG_ID )
            VALUES
            ( $USER, $ORG_ID );

        END IF;

        UPDATE base_organisations_users
        SET
            org_user_role = $org_user_role

        WHERE org_user_deleted = 0
        AND   ORG_ID = $ORG_ID
        AND   USER   = $USER;

    END IF;

END
//
DELIMITER ;