/*
 * Copyright (c) 2024, WSO2 LLC. (http://www.wso2.com). All Rights Reserved.
 *
 * This software is the property of WSO2 LLC. and its suppliers, if any.
 * Dissemination of any information or reproduction of any material contained
 * herein in any form is strictly forbidden, unless permitted by WSO2 expressly.
 * You may not alter or remove any copyright or other notice from copies of this content.
 */

package org.wso2.carbon.is.migration.service.v700.dao;

import org.wso2.carbon.database.utils.jdbc.NamedJdbcTemplate;
import org.wso2.carbon.database.utils.jdbc.exceptions.DataAccessException;
import org.wso2.carbon.database.utils.jdbc.exceptions.TransactionException;
import org.wso2.carbon.identity.core.migrate.MigrationClientException;
import org.wso2.carbon.identity.core.util.IdentityDatabaseUtil;
import org.wso2.carbon.is.migration.service.v700.model.RoleV2;
import org.wso2.carbon.user.core.util.UserCoreUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;

import static org.wso2.carbon.is.migration.service.v700.util.Utils.getNewTemplateForSharedDB;

/**
 * DAO implementation for role V2.
 */
public class RoleV2DAO {

    public static final String SCOPE_LIST_PLACEHOLDER = "_SCOPES_LIST_";
    private static final String GET_V2_ROLES = "SELECT UM_ROLE_NAME FROM UM_HYBRID_ROLE WHERE UM_TENANT_ID = ? AND " +
            "UM_AUDIENCE_REF_ID <> -1";
    private static final String CREATE_ROLE_AUDIENCE = "INSERT INTO UM_HYBRID_ROLE_AUDIENCE " +
            "(UM_AUDIENCE, UM_AUDIENCE_ID) VALUES (?, ?)";
    private static final String GET_HYBRID_ROLE_AUDIENCE_BY_AUDIENCE_ID = "SELECT UM_ID FROM UM_HYBRID_ROLE_AUDIENCE " +
            "WHERE UM_AUDIENCE_ID = ?";
    private static final String GET_SCIM2_GROUP_ID = "SELECT ATTR_VALUE FROM IDN_SCIM_GROUP WHERE ATTR_NAME = " +
            "'urn:ietf:params:scim:schemas:core:2.0:id' AND ROLE_NAME = ? AND TENANT_ID = ? AND AUDIENCE_REF_ID = ?";
    private static final String UPDATE_HYBRID_ROLE = "UPDATE UM_HYBRID_ROLE SET UM_AUDIENCE_REF_ID = ?, UM_UUID = ?" +
            " WHERE UM_ROLE_NAME = ? AND UM_TENANT_ID = ?";
    private static final String INSERT_IDN_SCIM_GROUP = "INSERT INTO IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, " +
            "ATTR_NAME, ATTR_VALUE, AUDIENCE_REF_ID) VALUES (?, ?, ?, ?, ?)";
    private static final String CREATE_HYBRID_ROLE = "INSERT INTO UM_HYBRID_ROLE (UM_ROLE_NAME, UM_TENANT_ID, " +
            "UM_AUDIENCE_REF_ID, UM_UUID) VALUES (?, ?, ?, ?)";
    private static final String GET_ROLE_UUID_BY_NAME = "SELECT UM_UUID FROM UM_HYBRID_ROLE WHERE UM_ROLE_NAME = ? AND " +
            "UM_TENANT_ID = ?";
    private static final String ASSIGN_PERMISSION_TO_ROLE = "INSERT INTO ROLE_SCOPE (ROLE_ID, SCOPE_ID) " +
            "SELECT ?, ID FROM SCOPE WHERE TENANT_ID = ? AND NAME IN (" + SCOPE_LIST_PLACEHOLDER + ")";
    private static final String GET_SYSTEM_API_ACCESSIBLE_ORG_ROLES = "SELECT DISTINCT UM_HYBRID_ROLE.UM_ROLE_NAME, " +
            "UM_HYBRID_ROLE.UM_UUID FROM UM_HYBRID_ROLE " +
            "INNER JOIN UM_ROLE_PERMISSION ON UM_HYBRID_ROLE.UM_ROLE_NAME = UM_ROLE_PERMISSION.UM_ROLE_NAME " +
            "INNER JOIN UM_PERMISSION ON UM_ROLE_PERMISSION.UM_PERMISSION_ID = UM_PERMISSION.UM_ID " +
            "INNER JOIN UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_HYBRID_ROLE.UM_TENANT_ID = UM_ROLE_PERMISSION.UM_TENANT_ID AND " +
            "UM_PERMISSION.UM_RESOURCE_ID LIKE '/permission/admin%' AND UM_HYBRID_ROLE.UM_TENANT_ID = ? AND " +
            "UM_HYBRID_ROLE_AUDIENCE.UM_AUDIENCE = 'organization'";
    private static final String IS_ROLE_AUDIENCE_EXIST = "SELECT COUNT(UM_ID) AS COUNT FROM UM_HYBRID_ROLE_AUDIENCE " +
            "WHERE UM_AUDIENCE = ? AND UM_AUDIENCE_ID = ?";
    private static final String GET_ROLE_UUID = "SELECT UM_UUID FROM UM_HYBRID_ROLE INNER JOIN " +
            "UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_TENANT_ID = ? AND UM_ROLE_NAME = ? AND UM_AUDIENCE_ID = ? AND UM_AUDIENCE = ?";
    private static final String GET_ROLE_ID = "SELECT UM_HYBRID_ROLE.UM_ID AS UM_ID FROM UM_HYBRID_ROLE INNER JOIN " +
            "UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_TENANT_ID = ? AND UM_ROLE_NAME = ? AND UM_AUDIENCE_ID = ? AND UM_AUDIENCE = ?";
    private static final String IS_ROLE_EXIST = "SELECT COUNT(UM_HYBRID_ROLE.UM_ID) AS COUNT FROM UM_HYBRID_ROLE INNER JOIN " +
            "UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_TENANT_ID = ? AND UM_ROLE_NAME = ? AND UM_AUDIENCE_ID = ? AND UM_AUDIENCE = ?";
    private static final String GET_APPLICATION_ROLES_OF_APP = "SELECT UM_ROLE_NAME, UM_HYBRID_ROLE.UM_UUID " +
            "FROM UM_HYBRID_ROLE INNER JOIN UM_HYBRID_ROLE_AUDIENCE " +
            "ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID WHERE UM_AUDIENCE = 'application' " +
            "AND UM_TENANT_ID = ? AND UM_HYBRID_ROLE_AUDIENCE.UM_AUDIENCE_ID = ?";
    private static final String ASSIGN_V1_ROLE_USERS_TO_V2_ROLE = "INSERT INTO UM_HYBRID_USER_ROLE " +
            "(UM_TENANT_ID, UM_ROLE_ID, UM_USER_NAME, UM_DOMAIN_ID) SELECT ?, ?, UM_USER_NAME, UM_DOMAIN_ID " +
            "FROM UM_HYBRID_ROLE INNER JOIN UM_HYBRID_USER_ROLE ON UM_HYBRID_ROLE.UM_ID = UM_HYBRID_USER_ROLE.UM_ROLE_ID " +
            "INNER JOIN UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_HYBRID_ROLE.UM_TENANT_ID = ? AND UM_ROLE_NAME = ? AND UM_HYBRID_ROLE_AUDIENCE.UM_AUDIENCE = 'organization'";
    private static final String GET_ALL_ORGANIZATION_AUDIENCE_ROLE_IN_TENANT = "SELECT UM_ROLE_NAME, UM_UUID FROM UM_HYBRID_ROLE " +
            "INNER JOIN UM_HYBRID_ROLE_AUDIENCE ON UM_HYBRID_ROLE.UM_AUDIENCE_REF_ID = UM_HYBRID_ROLE_AUDIENCE.UM_ID " +
            "WHERE UM_AUDIENCE = 'organization' AND UM_TENANT_ID = ?";
    private static final String GET_SCOPES_ASSIGNED_TO_ROLE = "SELECT SCOPE.NAME FROM ROLE_SCOPE INNER JOIN SCOPE " +
            "ON ROLE_SCOPE.SCOPE_ID = SCOPE.ID WHERE ROLE_ID = ?";
    private static final String REGISTER_SYSTEM_SCOPES_TO_ROLE = "INSERT INTO ROLE_SCOPE " +
            "(ROLE_ID, SCOPE_ID) SELECT ?, ID FROM SCOPE WHERE (TENANT_ID IS NULL OR TENANT_ID = -1234) AND " +
            "NAME IN (" + SCOPE_LIST_PLACEHOLDER + ")";
    private static final String ASSIGN_ROLE_TO_GROUP_OF_PRIMARY_DOMAIN = "INSERT INTO UM_HYBRID_GROUP_ROLE " +
            "(UM_GROUP_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID) SELECT ?, ?, ?, UM_DOMAIN_ID FROM UM_DOMAIN " +
            "WHERE UM_DOMAIN_NAME = 'PRIMARY' AND UM_TENANT_ID = ?";
    private static final String IS_ROLE_ASSIGNED_TO_GROUP = "SELECT COUNT(*) FROM UM_HYBRID_GROUP_ROLE INNER JOIN " +
            "UM_DOMAIN ON UM_DOMAIN.UM_DOMAIN_ID = UM_HYBRID_GROUP_ROLE.UM_DOMAIN_ID WHERE UM_GROUP_NAME = ? AND " +
            "UM_ROLE_ID = ? AND UM_HYBRID_GROUP_ROLE.UM_TENANT_ID = ? AND UM_DOMAIN_NAME = 'PRIMARY'";
    private static final String IS_GROUP_EXIST = "SELECT COUNT(*) FROM UM_ROLE WHERE UM_ROLE_NAME = ? AND " +
            "UM_TENANT_ID = ?";



    public List<String> getV2Roles(int tenantId) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.executeQuery(GET_V2_ROLES,
                    (resultSet, rowNumber) -> resultSet.getString("UM_ROLE_NAME"),
                    namedPreparedStatement -> namedPreparedStatement.setInt(1, tenantId));
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving V2 roles.", e);
        }
    }

    /**
     * Create role audience.
     *
     * @param audienceType   audience.
     * @param audienceId audience id.
     * @throws MigrationClientException if error occurred while inserting hybrid role audience.
     */
    public void createRoleAudience(String audienceType, String audienceId) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            namedJdbcTemplate.withTransaction(template -> template.executeInsert(CREATE_ROLE_AUDIENCE,
                    preparedStatement -> {
                        preparedStatement.setString(1, audienceType);
                        preparedStatement.setString(2, audienceId);
                    }, null, false));
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while creating the role audience.", e);
        }
    }

    /**
     * Get SCIM2 group id.
     *
     * @param roleName role name.
     * @param tenantId tenant id.
     * @return SCIM2 group id.
     * @throws MigrationClientException if error occurred while retrieving SCIM2 group id.
     */
    public String getSCIM2GroupId(String roleName, int tenantId, int audRefId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(GET_SCIM2_GROUP_ID)) {
            prepStmt.setString(1, "Internal/" + roleName);
            prepStmt.setInt(2, tenantId);
            prepStmt.setInt(3, audRefId);
            ResultSet resultSet = prepStmt.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString("ATTR_VALUE");
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving SCIM2 group id", e);
        }
        return null;
    }

    /**
     * Update hybrid role.
     *
     * @param audienceRefId audience ref id.
     * @param uuid          uuid.
     * @param roleName      role name.
     * @param tenantId      tenant id.
     * @throws MigrationClientException if error occurred while updating hybrid role.
     */
    public void updateHybridRole(Connection connection, int audienceRefId, String uuid, String roleName,
                                 int tenantId) throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(UPDATE_HYBRID_ROLE)) {
            prepStmt.setInt(1, audienceRefId);
            prepStmt.setString(2, uuid);
            prepStmt.setString(3, roleName);
            prepStmt.setInt(4, tenantId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while updating hybrid role", e);
        }
    }

    /**
     * Get hybrid role audience by organization.
     *
     * @param audienceID organization id.
     * @return hybrid role audience.
     * @throws MigrationClientException if error occurred while retrieving hybrid role audience.
     */
    public int getRoleAudienceByAudienceID(String audienceID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(GET_HYBRID_ROLE_AUDIENCE_BY_AUDIENCE_ID,
                    (resultSet, rowNumber) -> resultSet.getInt("UM_ID"),
                    namedPreparedStatement -> namedPreparedStatement.setString(1, audienceID));
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving role audience", e);
        }
    }

    /**
     * Insert SCIM2 group Id.
     *
     * @param tenantId tenant id.
     * @param roleName role name.
     * @param uuid     uuid.
     * @throws MigrationClientException if error occurred while inserting SCIM2 group.
     */
    public void insertSCIM2GroupId(int tenantId, String roleName, String uuid, int audRefId)
            throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(INSERT_IDN_SCIM_GROUP)) {
            prepStmt.setInt(1, tenantId);
            prepStmt.setString(2, "Internal/" + roleName);
            prepStmt.setString(3, "urn:ietf:params:scim:schemas:core:2.0:id");
            prepStmt.setString(4, uuid);
            prepStmt.setInt(5, audRefId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while inserting SCIM2 group", e);
        }
    }

    /**
     * Create hybrid role.
     *
     * @param roleName    role name.
     * @param tenantId    tenant id.
     * @param audienceRefId audience ref id.
     * @throws MigrationClientException if error occurred while creating hybrid role.
     */
    public void createHybridRole(String roleName, int tenantId, int audienceRefId) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            namedJdbcTemplate.withTransaction(template -> {template.executeInsert(CREATE_HYBRID_ROLE,
                        preparedStatement -> {
                            preparedStatement.setString(1, roleName);
                            preparedStatement.setInt(2, tenantId);
                            preparedStatement.setInt(3, audienceRefId);
                            preparedStatement.setString(4, UUID.randomUUID().toString());
                        }, null, false);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while creating role.", e);
        }
    }

    /**
     * Assign permission to role.
     *
     * @param roleId role id.
     * @param scopes scope names.
     * @throws MigrationClientException if error occurred while assigning permission to role.
     */
    public void assignPermissionToRole(String roleId, List<String> scopes, int tenantId)
            throws MigrationClientException {

        String scopesString = scopes.stream().map(s -> "'" + s + "'").collect(Collectors.joining(", "));
        String sql = ASSIGN_PERMISSION_TO_ROLE.replace(SCOPE_LIST_PLACEHOLDER, scopesString);

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, roleId);
            preparedStatement.setInt(2, tenantId);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while adding set of scopes to the role.", e);
        }
    }

    /**
     * Get role UUID by name.
     *
     * @param roleName role name.
     * @param tenantId tenant id.
     * @return role UUID.
     * @throws MigrationClientException if error occurred while retrieving role UUID.
     */
    public String getRoleUUIDByName(String roleName, int tenantId) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(GET_ROLE_UUID_BY_NAME,
                    (resultSet, rowNumber) -> resultSet.getString("UM_UUID"),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setString(1, roleName);
                        namedPreparedStatement.setInt(2, tenantId);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving role id.", e);
        }
    }



    public void createV2Role(String role, String audienceType, int tenantID, String appUUID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            namedJdbcTemplate.withTransaction(template -> {
                if (!isRoleAudienceExist(audienceType, appUUID)) {
                    createRoleAudience(audienceType, appUUID);
                }
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while creating the application role.", e);
        }
        int roleAudienceRefID = getRoleAudienceByAudienceID(appUUID);
        createHybridRole(role, tenantID, roleAudienceRefID);
    }

    public List<String> getSystemAPIAccessibleRoles(int tenantID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.executeQuery(GET_SYSTEM_API_ACCESSIBLE_ORG_ROLES,
                    (resultSet, rowNumber) -> resultSet.getString(1),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setInt(1, tenantID);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving all the permissions of the role.", e);
        }
    }

    public boolean isRoleAudienceExist(String audienceType, String audienceID) throws MigrationClientException {

        int count;
        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            count = namedJdbcTemplate.fetchSingleRecord(IS_ROLE_AUDIENCE_EXIST,
                    (resultSet, rowNumber) -> resultSet.getInt("COUNT"),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setString(1, audienceType);
                        namedPreparedStatement.setString(2, audienceID);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while checking role audience.", e);
        }
        return count > 0;
    }

    public String getGetRoleUUID(int tenantId, String roleName, String audienceID, String audienceType) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(GET_ROLE_UUID,
                    (resultSet, rowNumber) -> resultSet.getString("UM_UUID"),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setInt(1, tenantId);
                        namedPreparedStatement.setString(2, roleName);
                        namedPreparedStatement.setString(3, audienceID);
                        namedPreparedStatement.setString(4, audienceType);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while getting role UUID.", e);
        }
    }

    public int getRoleID(int tenantId, String roleName, String audienceID, String audienceType) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(GET_ROLE_ID, (resultSet, rowNumber) -> resultSet.getInt("UM_ID"),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setInt(1, tenantId);
                        namedPreparedStatement.setString(2, UserCoreUtil.removeDomainFromName(roleName));
                        namedPreparedStatement.setString(3, audienceID);
                        namedPreparedStatement.setString(4, audienceType);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving v2 administrator role ID.", e);
        }
    }

    public List<RoleV2> getApplicationRolesOfApp(int tenantID, String appUUID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.withTransaction(template -> template.executeQuery(GET_APPLICATION_ROLES_OF_APP,
                    (resultSet, rowNumber) -> new RoleV2(resultSet.getString(1), resultSet.getString(2)),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setInt(1, tenantID);
                        namedPreparedStatement.setString(2, appUUID);
                    }));
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while retrieving the application roles bound to the app.", e);
        }
    }

    public void migrateUserOfRoleToNewRole(int roleID, int tenantID, String role) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            namedJdbcTemplate.withTransaction(template -> {
                template.executeInsert(ASSIGN_V1_ROLE_USERS_TO_V2_ROLE,
                        preparedStatement -> {
                            preparedStatement.setInt(1, tenantID);
                            preparedStatement.setInt(2, roleID);
                            preparedStatement.setInt(3, tenantID);
                            preparedStatement.setString(4, role);
                        }, null, false);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while assigning V1 role users to the V2 role.", e);
        }
    }

    public List<RoleV2> getAllOrganizationAudienceRoles(int tenantID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.withTransaction(template -> template.executeQuery(GET_ALL_ORGANIZATION_AUDIENCE_ROLE_IN_TENANT,
                    (resultSet, rowNumber) -> new RoleV2(resultSet.getString(1), resultSet.getString(2)),
                    namedPreparedStatement -> namedPreparedStatement.setInt(1, tenantID)));
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while retrieving application role audience ID of application.", e);
        }
    }

    public List<String> getAssignedScopesForRole(String roleUUID) throws MigrationClientException {

        List<String> scopesList = new ArrayList<>();
        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(GET_SCOPES_ASSIGNED_TO_ROLE)) {
                preparedStatement.setString(1, roleUUID);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        scopesList.add(resultSet.getString("NAME"));
                    }
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while checking the assigned scopes count of the role.", e);
        }
        return scopesList;
    }



    public void assignInternalSystemScopesToRole(String roleUUID, List<String> scopesList) throws MigrationClientException {

        if (scopesList.isEmpty()) {
            return;
        }
        String scopesString = scopesList.stream().map(s -> "'" + s + "'").collect(Collectors.joining(", "));
        String sql = REGISTER_SYSTEM_SCOPES_TO_ROLE.replace(SCOPE_LIST_PLACEHOLDER, scopesString);
        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, roleUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while assigning internal system scopes to the role.", e);
        }
    }

    public boolean isGroupToRoleMapped(String groupName, int roleID, int tenantID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(IS_ROLE_ASSIGNED_TO_GROUP,
                    (resultSet, rowNumber) -> resultSet.getInt(1) > 0,
                    namedPreparedStatement -> {
                        namedPreparedStatement.setString(1, groupName);
                        namedPreparedStatement.setInt(2, roleID);
                        namedPreparedStatement.setInt(3, tenantID);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while checking group to role mapped.", e);
        }
    }

    public boolean isGroupExist(String groupName, int tenantID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.fetchSingleRecord(IS_GROUP_EXIST,
                    (resultSet, rowNumber) -> resultSet.getInt(1) > 0,
                    namedPreparedStatement -> {
                        namedPreparedStatement.setString(1, groupName);
                        namedPreparedStatement.setInt(2, tenantID);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while checking group exist in tenant with ID: " + tenantID, e);
        }
    }

    public void assignRoleToGroupOfPrimaryGroup(String groupName, int roleID, int tenantID)
            throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            namedJdbcTemplate.withTransaction(template -> {
                template.executeInsert(ASSIGN_ROLE_TO_GROUP_OF_PRIMARY_DOMAIN,
                        preparedStatement -> {
                            preparedStatement.setString(1, groupName);
                            preparedStatement.setInt(2, roleID);
                            preparedStatement.setInt(3, tenantID);
                            preparedStatement.setInt(4, tenantID);
                        }, null, false);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while assigning role to primary domain group.", e);
        }
    }

    public boolean isRoleExist(int tenantId, String roleName, String audienceID, String audienceType) throws MigrationClientException {

        int count;
        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            count = namedJdbcTemplate.fetchSingleRecord(IS_ROLE_EXIST,
                    (resultSet, rowNumber) -> resultSet.getInt("COUNT"),
                    namedPreparedStatement -> {
                        namedPreparedStatement.setInt(1, tenantId);
                        namedPreparedStatement.setString(2, UserCoreUtil.removeDomainFromName(roleName));
                        namedPreparedStatement.setString(3, audienceID);
                        namedPreparedStatement.setString(4, audienceType);
                    });
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while checking role exist.", e);
        }
        return count > 0;
    }
}
