DROP   PROCEDURE Base_Projects;
DELIMITER //
CREATE PROCEDURE Base_Projects
(
    $Sid           TEXT,
    $PROJECT_ID    INT,
    $project_guid  TEXT,
    $order         TEXT,
    $limit         INT,
    $offset        INT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN

    IF NOT $PROJECT_ID AND NOT "" = TRIM( $project_guid ) THEN

        SELECT PROJECT_ID
        INTO  $PROJECT_ID
        FROM   base_projects
        WHERE  project_guid = $project_guid;

    END IF;

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

    IF NOT @USER THEN

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

    ELSE

        SELECT    *
        FROM      base_projects
        LEFT JOIN base_organisations USING (ORG_ID)
        WHERE     PROJECT_ID IN
        (
            SELECT PROJECT_ID FROM base_projects_users
            WHERE  USER = @USER
            AND   (0 = $PROJECT_ID OR PROJECT_ID=$PROJECT_ID)
            AND   project_roles LIKE '%QUERY%'
        );

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Projects_Replace;
DELIMITER //
CREATE PROCEDURE Base_Projects_Replace
(
    $Sid           TEXT,
    $ORG_ID        INT,
    $GROUP_ID      INT,
    $PROJECT_ID    INT,
    $project_guid  TEXT,
    $project_name  TEXT,
    $project_code  TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN

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

    IF NOT $PROJECT_ID AND NOT "" = $project_guid THEN
        SELECT PROJECT_ID INTO $PROJECT_ID
        FROM   base_projects
        WHERE  project_guid = $project_guid;
    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_Projects_Replace';

    ELSEIF $ORG_ID AND NOT EXISTS
    (
        SELECT    *
        FROM      base_organisations_users
        LEFT JOIN base_organisations USING (ORG_ID)
        WHERE     org_user_deleted =     0
        AND       org_deleted      =     0
        AND       ORG_ID           =     $ORG_ID
        AND       USER             =     @USER
        AND       org_user_role    LIKE '%REPLACE%'
    )
    THEN

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

    ELSEIF $GROUP_ID AND NOT EXISTS
    (
        SELECT    *
        FROM      base_groups
        LEFT JOIN base_groups_members USING (GROUP_ID)
        WHERE
        (
            USER = @USER OR GROUP_OWNER = @USER
        )
    )
    THEN

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

    ELSE

        IF NOT $PROJECT_ID THEN

            REPLACE INTO base_projects
            (  ORG_ID,  GROUP_ID,  PROJECT_ID )
            VALUES
            ( $ORG_ID, $GROUP_ID, $PROJECT_ID );

            SET $PROJECT_ID = LAST_INSERT_ID();

            REPLACE INTO base_projects_users
            (  USER,  ORG_ID,  GROUP_ID,  PROJECT_ID,  project_roles                    )
            VALUES
            ( @USER, $ORG_ID, $GROUP_ID, $PROJECT_ID, 'DELETE.INSERT.REPLACE.SAVE.SELECT.UPDATE.OWNER' );

        END IF;

        UPDATE base_projects
        SET
            project_name = $project_name,
            project_code = $project_code

        WHERE PROJECT_ID = $PROJECT_ID
        AND   @USER IN
        (
            SELECT USER FROM base_projects_users WHERE PROJECT_ID = $PROJECT_ID AND project_roles LIKE '%SAVE%'
        );

        DO LAST_INSERT_ID( $PROJECT_ID );

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Selects_Base_Organisations;
DELIMITER //
CREATE PROCEDURE Selects_Base_Organisations
(
  $Sid                                   TEXT,
  $id                                    TEXT,
  $value                                 TEXT,
  $filter                                TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN

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

    IF NOT @USER THEN 

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

    ELSE

        SELECT
            ORG_ID   AS name,
            org_name AS text,
            $id      AS id,
            $value   AS value

        FROM      base_organisations
        LEFT JOIN base_organisations_users USING (ORG_ID)
        WHERE     USER = @USER
        AND       org_user_role LIKE '%query%';

    END IF;

END
//
DELIMITER ;