/*
 * 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.apache.commons.lang.StringUtils;
import org.wso2.carbon.database.utils.jdbc.NamedJdbcTemplate;
import org.wso2.carbon.database.utils.jdbc.exceptions.TransactionException;
import org.wso2.carbon.identity.api.resource.mgt.constant.SQLConstants;
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.Application;

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.stream.Collectors;

import static org.wso2.carbon.identity.role.v2.mgt.core.RoleConstants.ORACLE;

public class ApplicationDAO {

    public static final String SCOPE_LIST_PLACEHOLDER = "_SCOPES_LIST_";
    private static final String GET_APPLICATIONS_EXCEPT_SYSTEM_APPS = "SELECT SP_APP.ID, UUID, SP_APP.APP_NAME, " +
            "GRANT_TYPES FROM SP_APP INNER JOIN IDN_OAUTH_CONSUMER_APPS " +
            "ON SP_APP.APP_NAME = IDN_OAUTH_CONSUMER_APPS.APP_NAME WHERE " +
            "SP_APP.TENANT_ID = IDN_OAUTH_CONSUMER_APPS.TENANT_ID AND SP_APP.TENANT_ID = ? AND " +
            "SP_APP.APP_NAME NOT IN ('Console', 'My Account')";
    private static final String AUTHORIZE_API_TO_APPLICATION = "INSERT INTO AUTHORIZED_API(APP_ID, API_ID, POLICY_ID)" +
            " VALUES (?, ?, ?)";
    private static final String AUTHORIZE_SCOPES_TO_APPLICATION = "INSERT INTO AUTHORIZED_SCOPE(APP_ID, API_ID, " +
            "SCOPE_ID) VALUES (?, ?, ?)";
    private static final String DELETE_AUTHORIZED_SCOPE = "DELETE FROM AUTHORIZED_SCOPE WHERE API_ID = ? ";
    private static final String DELETE_AUTHORIZED_API = "DELETE FROM AUTHORIZED_API WHERE API_ID = ?";
    private static final String ASSIGN_ROLE_TO_APPLICATION = "INSERT INTO APP_ROLE_ASSOCIATION(APP_ID, ROLE_ID) VALUES (?, ?)";
    private static final String DELETE_ROLE_FROM_APPLICATION = "DELETE FROM APP_ROLE_ASSOCIATION WHERE APP_ID = ?";
    private static final String GET_APPLICATION_PROPERTY = "SELECT VALUE FROM SP_METADATA WHERE SP_ID = ? AND NAME = ?";
    private static final String GET_APPLICATION_PROPERTY_H2 = "SELECT \"VALUE\" FROM SP_METADATA WHERE SP_ID = ? AND NAME = ?";

    private static final String INSERT_APPLICATION_PROPERTY = "INSERT INTO SP_METADATA(SP_ID, NAME, VALUE, TENANT_ID) " +
            "VALUES (?, ?, ?, ?)";
    private static final String INSERT_APPLICATION_PROPERTY_WITH_DISPLAY_NAME = "INSERT INTO SP_METADATA(SP_ID, NAME, VALUE, " +
            "TENANT_ID, DISPLAY_NAME) VALUES (?, ?, ?, ?, ?)";
    private static final String UPDATE_APPLICATION_PROPERTY = "UPDATE SP_METADATA SET VALUE = ? WHERE SP_ID = ? AND " +
            "NAME = ?";
    private static final String DELETE_APPLICATION_AUDIENCE = "DELETE FROM SP_METADATA WHERE SP_ID = ?";
    private static final String GET_SP_ID_FROM_APP_ID = "SELECT ID FROM SP_APP WHERE UUID = ? AND TENANT_ID = ?";
    private static final String GET_SP_ID_BY_NAME = "SELECT ID FROM SP_APP WHERE TENANT_ID = ? AND APP_NAME = ?";
    private static final String FETCH_SP_CALL_BACK_URL = "SELECT CALLBACK_URL FROM IDN_OAUTH_CONSUMER_APPS WHERE " +
            "TENANT_ID = ? AND APP_NAME = ?";
    private static final String FETCH_ALLOWED_GRANT_TYPES = "SELECT GRANT_TYPES FROM IDN_OAUTH_CONSUMER_APPS WHERE " +
            "TENANT_ID = ? AND APP_NAME = ?";
    private static final String UPDATE_ALLOWED_GRANT_TYPES = "UPDATE IDN_OAUTH_CONSUMER_APPS SET GRANT_TYPES = ? " +
            "WHERE TENANT_ID = ? AND APP_NAME = ?";
    private static final String GET_APP_UUID_BY_APP_NAME = "SELECT UUID FROM SP_APP WHERE TENANT_ID = ? AND " +
            "APP_NAME = ?";
    private static final String GET_CUSTOM_SYSTEM_APP_CREATED_TENANTS = "SELECT SP_APP.TENANT_ID FROM SP_APP " +
            "INNER JOIN IDN_OAUTH_CONSUMER_APPS ON SP_APP.APP_NAME = IDN_OAUTH_CONSUMER_APPS.APP_NAME WHERE " +
            "SP_APP.TENANT_ID = IDN_OAUTH_CONSUMER_APPS.TENANT_ID AND IDN_OAUTH_CONSUMER_APPS.CONSUMER_KEY != ? " +
            "AND SP_APP.APP_NAME = ?";
    private static final String IS_APP_AUTHORIZED_TO_APIS = "SELECT COUNT(API_ID) AS COUNT FROM AUTHORIZED_API " +
            "WHERE APP_ID = ?";
    private static final String SUBSCRIBE_ALL_SYSTEM_APIS_TO_APP = "INSERT INTO AUTHORIZED_API " +
            "(APP_ID, API_ID, POLICY_ID) SELECT ?, ID, 'RBAC' FROM API_RESOURCE WHERE TENANT_ID IS NULL";
    private static final String SUBSCRIBE_SUPER_TENANT_SYSTEM_APIS_TO_APP = "INSERT INTO AUTHORIZED_API " +
            "(APP_ID, API_ID, POLICY_ID) SELECT ?, ID, 'RBAC' FROM API_RESOURCE WHERE TENANT_ID = -1234 AND " +
            "IDENTIFIER IN ('/api/server/v1/tenants', '/api/server/v1/admin-advisory-management/banner')";
    private static final String AUTHORIZE_ALL_SYSTEM_API_RESOURCE_SCOPES_TO_APP = "INSERT INTO AUTHORIZED_SCOPE " +
            "(APP_ID, API_ID, SCOPE_ID) SELECT ?, API_ID, ID FROM SCOPE WHERE TENANT_ID IS NULL";
    private static final String AUTHORIZE_SUPER_TENANT_SYSTEM_API_RESOURCE_SCOPES_TO_APP = "INSERT INTO AUTHORIZED_SCOPE " +
            "(APP_ID, API_ID, SCOPE_ID) SELECT ?, API_ID, SCOPE.ID FROM SCOPE INNER JOIN API_RESOURCE " +
            "ON SCOPE.API_ID = API_RESOURCE.ID WHERE SCOPE.TENANT_ID = -1234 AND " +
            "IDENTIFIER IN ('/api/server/v1/tenants', '/api/server/v1/admin-advisory-management/banner')";
    private static final String SUBSCRIBE_SET_OF_SYSTEM_APIS_TO_APP = "INSERT INTO AUTHORIZED_API (APP_ID, API_ID, POLICY_ID) " +
            "SELECT ?, API_ID, 'RBAC' FROM (SELECT DISTINCT API_ID FROM SCOPE WHERE (TENANT_ID IS NULL OR TENANT_ID = -1234) AND " +
            "NAME IN (" + SCOPE_LIST_PLACEHOLDER + " ) ) AS T";
    private static final String SUBSCRIBE_SET_OF_SYSTEM_APIS_TO_APP_ORACLE = "INSERT INTO AUTHORIZED_API (APP_ID, API_ID, POLICY_ID) " +
            "SELECT ?, API_ID, 'RBAC' FROM (SELECT DISTINCT API_ID FROM SCOPE WHERE (TENANT_ID IS NULL OR TENANT_ID = -1234) AND " +
            "NAME IN (" + SCOPE_LIST_PLACEHOLDER + " ) )";
    private static final String AUTHORIZE_SET_OF_SYSTEM_API_RESOURCE_SCOPES_TO_APP = "INSERT INTO AUTHORIZED_SCOPE " +
            "(APP_ID, API_ID, SCOPE_ID) SELECT ?, API_ID, ID " +
            "FROM SCOPE WHERE (TENANT_ID IS NULL OR TENANT_ID = -1234) AND NAME IN (" + SCOPE_LIST_PLACEHOLDER + ")";

    /**
     * Get apps with RBAC scope validator.
     *
     * @param tenantId Tenant ID.
     * @return List of apps.
     * @throws MigrationClientException If an error occurs while retrieving apps.
     */
    public List<Application> getApplicationsExceptSystemApps(int tenantId) throws MigrationClientException {

        List<Application> applications = new ArrayList<>();
        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(GET_APPLICATIONS_EXCEPT_SYSTEM_APPS)) {
            prepStmt.setInt(1, tenantId);
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                Application application = new Application();
                application.setAppID(resultSet.getInt("ID"));
                application.setAppName(resultSet.getString("APP_NAME"));
                application.setUUID(resultSet.getString("UUID"));
                application.setAllowedGrantTypes(resultSet.getString("GRANT_TYPES"));
                applications.add(application);
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving apps", e);
        }
        return applications;
    }

    /**
     * Authorize API to application.
     *
     * @param appUUID    Application ID.
     * @param apiId    API ID.
     * @param policyId Policy ID.
     * @throws MigrationClientException If an error occurs while authorizing APIs to application.
     */
    public void authorizeAPIToApplication(String appUUID, String apiId, String policyId)
            throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(AUTHORIZE_API_TO_APPLICATION)) {
            prepStmt.setString(1, appUUID);
            prepStmt.setString(2, apiId);
            prepStmt.setString(3, policyId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing APIs to application", e);
        }
    }

    /**
     * Authorize scopes to application.
     *
     * @param appId      Application ID.
     * @param apiId      API ID.
     * @param scopeIDs List of scope IDs.
     * @throws MigrationClientException If an error occurs while authorizing scopes to application.
     */
    public void authorizeScopesToApplication(String appId, String apiId, List<String> scopeIDs)
            throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(AUTHORIZE_SCOPES_TO_APPLICATION)) {
            for (String scopeID : scopeIDs) {
                prepStmt.setString(1, appId);
                prepStmt.setString(2, apiId);
                prepStmt.setString(3, scopeID);
                prepStmt.addBatch();
            }
            prepStmt.executeBatch();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing scopes to application", e);
        }
    }

    /**
     * Delete authorized scopes.
     *
     * @param apiId    API ID.
     * @throws MigrationClientException If an error occurs while deleting authorized scopes.
     */
    public void deleteAuthorizedScope(String apiId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(DELETE_AUTHORIZED_SCOPE)) {
            prepStmt.setString(1, apiId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting authorized scopes", e);
        }
    }

    /**
     * Delete authorized APIs.
     *
     * @param apiId API ID.
     * @throws MigrationClientException If an error occurs while deleting authorized APIs.
     */
    public void deleteAuthorizedAPI(String apiId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(DELETE_AUTHORIZED_API)) {
            prepStmt.setString(1, apiId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting authorized APIs", e);
        }
    }

    /**
     * Assign role to application.
     *
     * @param appId  Application ID.
     * @param roleId Role ID.
     * @throws MigrationClientException If an error occurs while assigning role to application.
     */
    public void assignRoleToApplication(String appId, String roleId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(ASSIGN_ROLE_TO_APPLICATION)) {
            prepStmt.setString(1, appId);
            prepStmt.setString(2, roleId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while assigning role to application", e);
        }
    }

    /**
     * Delete role from application.
     *
     * @param appId Application ID.
     * @throws MigrationClientException If an error occurs while deleting role from application.
     */
    public void deleteRoleFromApplication(String appId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(DELETE_ROLE_FROM_APPLICATION)) {
            prepStmt.setString(1, appId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting role from application", e);
        }
    }

    /**
     * Insert application property.
     * @param spId
     * @param tenantId
     * @param name
     * @param value
     * @param displayName
     * @throws MigrationClientException
     */
    public void insertApplicationProperty(int spId, int tenantId, String name, String value, String displayName) throws MigrationClientException {

        String sql = INSERT_APPLICATION_PROPERTY;
        if (displayName != null) {
            sql = INSERT_APPLICATION_PROPERTY_WITH_DISPLAY_NAME;
        }
        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(sql)) {
            prepStmt.setInt(1, spId);
            prepStmt.setString(2, name);
            prepStmt.setString(3, value);
            prepStmt.setInt(4, tenantId);
            if (displayName != null) {
                prepStmt.setString(5, displayName);
            }
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while inserting application property", e);
        }
    }

    public void updateApplicationProperty(int spId, String name, String value) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(UPDATE_APPLICATION_PROPERTY)) {
            prepStmt.setString(1, value);
            prepStmt.setInt(2, spId);
            prepStmt.setString(3, name);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while updating application property", e);
        }
    }
    public String getApplicationProperty(int spId, String name) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
            String databaseName = connection.getMetaData().getDatabaseProductName();
            String sql =  databaseName.contains(SQLConstants.H2) ? GET_APPLICATION_PROPERTY_H2 : GET_APPLICATION_PROPERTY;
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setInt(1, spId);
                preparedStatement.setString(2, name);
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    return resultSet.getString("VALUE");
                }
                return null;
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving application property.", e);
        }
    }

    /**
     * Delete application audience.
     *
     * @param spId Application ID.
     * @throws MigrationClientException If an error occurs while deleting application audience.
     */
    public void deleteApplicationAudience(int spId) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(DELETE_APPLICATION_AUDIENCE)) {
            prepStmt.setInt(1, spId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting application audience", e);
        }
    }

    public int getSPIdFromAppName(int tenantId, String appName) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(GET_SP_ID_BY_NAME)) {
                preparedStatement.setInt(1, tenantId);
                preparedStatement.setString(2, appName);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return resultSet.getInt("ID");
                    }
                    return -1;
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving application ID.", e);
        }
    }

    public String getAppUUIDFromAppName(String appName, int tenantID) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(GET_APP_UUID_BY_APP_NAME)) {
                preparedStatement.setInt(1, tenantID);
                preparedStatement.setString(2, appName);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return resultSet.getString("UUID");
                    }
                    return StringUtils.EMPTY;
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving application UUID by application name.", e);
        }
    }

    public String getCallBackURL(int tenantID, String appName) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(FETCH_SP_CALL_BACK_URL)) {
                preparedStatement.setInt(1, tenantID);
                preparedStatement.setString(2, appName);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return resultSet.getString("CALLBACK_URL");
                    }
                    return StringUtils.EMPTY;
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving the callback URL of application " + appName, e);
        }
    }

    public String getAllowedGrantTypesOfApp(int tenantID, String appName) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(FETCH_ALLOWED_GRANT_TYPES)) {
                preparedStatement.setInt(1, tenantID);
                preparedStatement.setString(2, appName);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return resultSet.getString("GRANT_TYPES");
                    }
                    return StringUtils.EMPTY;
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving the allowed grant types of application " + appName, e);
        }
    }

    public void updateAllowedGrantTypesOfApp(int tenantID, String appName, String grantTypes) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ALLOWED_GRANT_TYPES)) {
                preparedStatement.setString(1, grantTypes);
                preparedStatement.setInt(2, tenantID);
                preparedStatement.setString(3, appName);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while updating the allowed grant types of the app.", e);
        }
    }

    public boolean isApplicationExist(String appName, int tenantID) {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(GET_APP_UUID_BY_APP_NAME)) {
                preparedStatement.setInt(1, tenantID);
                preparedStatement.setString(2, appName);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return true;
                    }
                }
            }
        } catch (SQLException e) {
            return false;
        }
        return false;
    }

    public List<String> customSystemAppExistTenants(String consumerKey, String appName)
            throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(GET_CUSTOM_SYSTEM_APP_CREATED_TENANTS)) {
                preparedStatement.setString(1, consumerKey);
                preparedStatement.setString(2, appName);
                List<String> tenants = new ArrayList<>();
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        tenants.add(resultSet.getString("TENANT_ID"));
                    }
                    return tenants;
                }
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while checking the custom system apps created tenants.", e);
        }
    }

    public boolean isAppAuthorizedForAPIs(String appUUID) {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(IS_APP_AUTHORIZED_TO_APIS)) {
                preparedStatement.setString(1, appUUID);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        return resultSet.getInt("COUNT") > 0;
                    }
                }
            }
        } catch (SQLException e) {
            return false;
        }
        return false;
    }

    public void authorizeAllSystemAPIsWithScopes(String appUUID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = new NamedJdbcTemplate(IdentityDatabaseUtil.getDataSource());
        try {
            namedJdbcTemplate.withTransaction(template -> {
                subscribeAllSystemAPIsToApp(appUUID);
                authorizeAllSystemAPIScopesToApp(appUUID);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException(
                    "Error while authorizing all the systems APIs with scopes to the application.", e);
        }
    }

    public void authorizeSuperTenantSystemAPIsWithScopes(String appUUID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = new NamedJdbcTemplate(IdentityDatabaseUtil.getDataSource());
        try {
            namedJdbcTemplate.withTransaction(template -> {
                subscribeSuperTenantSystemAPIsToApp(appUUID);
                authorizeSuperTenantSystemAPIScopesToApp(appUUID);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException(
                    "Error while authorizing super tenant systems APIs with scopes to the application.", e);
        }
    }

    private void subscribeAllSystemAPIsToApp(String appUUID) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(SUBSCRIBE_ALL_SYSTEM_APIS_TO_APP)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing all system APIs to the app.", e);
        }
    }

    private void subscribeSuperTenantSystemAPIsToApp(String appUUID) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    SUBSCRIBE_SUPER_TENANT_SYSTEM_APIS_TO_APP)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing super tenant system APIs to the app.", e);
        }
    }

    private void authorizeAllSystemAPIScopesToApp(String appUUID) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    AUTHORIZE_ALL_SYSTEM_API_RESOURCE_SCOPES_TO_APP)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing all system API scopes to the app.", e);
        }
    }

    private void authorizeSuperTenantSystemAPIScopesToApp(String appUUID) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    AUTHORIZE_SUPER_TENANT_SYSTEM_API_RESOURCE_SCOPES_TO_APP)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing all system API scopes to the app.", e);
        }
    }

    public void authorizeSetOfSystemAPIsWithScopes(String appUUID, List<String> scopesList) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = new NamedJdbcTemplate(IdentityDatabaseUtil.getDataSource());
        String scopesString = scopesList.stream().map(s -> "'" + s + "'").collect(Collectors.joining(", "));
        try {
            namedJdbcTemplate.withTransaction(template -> {
                subscribeSetOfSystemAPIsToApp(appUUID, scopesString);
                authorizeSetOfSystemAPIScopesToApp(appUUID, scopesString);
                return null;
            });
        } catch (TransactionException e) {
            throw new MigrationClientException("Error while authorizing set of system APIs with scopes to the application.", e);
        }
    }

    private void subscribeSetOfSystemAPIsToApp(String appUUID, String scopesList) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            String databaseProductName = connection.getMetaData().getDatabaseProductName();
            String sql = SUBSCRIBE_SET_OF_SYSTEM_APIS_TO_APP.replace(SCOPE_LIST_PLACEHOLDER, scopesList);
            if (ORACLE.equals(databaseProductName)) {
                sql = SUBSCRIBE_SET_OF_SYSTEM_APIS_TO_APP_ORACLE.replace(SCOPE_LIST_PLACEHOLDER, scopesList);
            }
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing set of system APIs to the app.", e);
        }
    }

    private void authorizeSetOfSystemAPIScopesToApp(String appUUID, String scopesList) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(true)) {
            String sql = AUTHORIZE_SET_OF_SYSTEM_API_RESOURCE_SCOPES_TO_APP.replace(SCOPE_LIST_PLACEHOLDER, scopesList);
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, appUUID);
                preparedStatement.execute();
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while authorizing set of API scopes to the app.", e);
        }
    }
}
