/*
 * Copyright (c) 2024-2025, WSO2 LLC. (http://www.wso2.com).
 *
 * WSO2 LLC. licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

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

import org.apache.commons.lang3.tuple.Pair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.util.JdbcUtils;
import org.wso2.carbon.identity.governance.exceptions.notiification.NotificationTemplateManagerServerException;
import org.wso2.carbon.identity.governance.model.NotificationTemplate;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.wso2.carbon.email.mgt.util.I18nEmailUtil.getContentByteArray;
import static org.wso2.carbon.email.mgt.util.I18nEmailUtil.setContent;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.COUNT_ALL_APP_NOTIFICATION_TYPES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.COUNT_ALL_NOTIFICATION_TYPES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.COUNT_ALL_ORG_NOTIFICATION_TYPES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.DELETE_ALL_APP_NOTIFICATION_TEMPLATES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.DELETE_ALL_NOTIFICATION_SCENARIOS_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.DELETE_ALL_ORG_NOTIFICATION_TEMPLATES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.DELETE_ORG_NOTIFICATION_TEMPLATE_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.GET_NOTIFICATION_TYPE_COUNT_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.INSERT_APP_NOTIFICATION_TEMPLATE_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.INSERT_NOTIFICATION_TYPE_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.INSERT_ORG_NOTIFICATION_TEMPLATE_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.LIST_APP_NOTIFICATION_TEMPLATE_KEYS_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.LIST_NOTIFICATION_TYPES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.LIST_ORG_NOTIFICATION_TEMPLATES_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.LIST_ORG_NOTIFICATION_TEMPLATE_KEYS_SQL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.APP_ID;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.CHANNEL;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.CONTENT;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.CONTENT_TYPE;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.COUNT;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.LOCALE;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.NAME;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.TEMPLATE_KEY;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.TENANT_ID;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.TYPE_KEY;

/**
 * This class is to perform batch insert operations for Notification Types & Notification Templates.
 */
public class NotificationDAO {

    private static final Logger LOG = LoggerFactory.getLogger(NotificationDAO.class);

    public void migrateNotificationScenarios(List<String> scenarioNames, String channel, boolean isDryRun,
                                             int tenantId) throws NotificationTemplateManagerServerException {

        if (scenarioNames == null || scenarioNames.isEmpty()) {
            LOG.info("RDM-NT>> No notification {} template types to migrate in tenant: {}.", channel, tenantId);
            return;
        }

        LOG.info("RDM-NT>> {} notification {} template types selected for the migration from Registry in tenant: {}.",
                scenarioNames.size(), channel, tenantId);

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        int noOfRecordsAdded;

        try {
            // Perform the migration in a transaction
            noOfRecordsAdded = namedJdbcTemplate.withTransaction(template -> {
                // Get all existing scenarios in the DB
                List<String> existingScenarioTypes = template.executeQuery(LIST_NOTIFICATION_TYPES_SQL,
                        (resultSet, rowNumber) -> resultSet.getString(TYPE_KEY),
                        preparedStatement -> {
                            preparedStatement.setString(CHANNEL, channel);
                            preparedStatement.setInt(TENANT_ID, tenantId);
                        });
                LOG.info("RDM-NT>> {} notification {} template types already exists in Database in tenant: {}.",
                        existingScenarioTypes.size(), channel, tenantId);

                List<String> scenarioNamesToAdd = new ArrayList<>();
                if (!existingScenarioTypes.isEmpty()) {
                    // Filter out the scenarios that are already in the DB
                    for (String scenarioName : scenarioNames) {
                        if (scenarioName != null && existingScenarioTypes.contains(scenarioName.toLowerCase())) {
                            LOG.info("RDM-NT>> Scenario type {} already exists in the DB.", scenarioName);
                        } else {
                            scenarioNamesToAdd.add(scenarioName);
                        }
                    }
                } else {
                    scenarioNamesToAdd = scenarioNames;
                }

                int size = scenarioNamesToAdd.size();
                LOG.info("RDM-NT>> {} notification {} template types selected for migration in tenant: {}.", size,
                        channel, tenantId);

                // Add the filtered scenarios to the DB
                if (!isDryRun) {
                    storeNotificationScenarios(scenarioNamesToAdd, channel, tenantId);
                } else {
                    LOG.info("RDM-NT>> Notification {} template types migration skipped due to dry run in tenant: {}.",
                            channel, tenantId);
                }
                return size;
            });
        } catch (TransactionException e) {
            String error =
                    String.format("Error while migrating the %s template types in tenant: %s.", channel, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
        LOG.info("RDM-NT>> {} notification {} template types successfully migrated in tenant: {}.", noOfRecordsAdded,
                channel, tenantId);
    }

    private void storeNotificationScenarios(List<String> templateTypeDisplayNames, String notificationChannel,
                                            int tenantId) throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            namedJdbcTemplate.executeBatchInsert(INSERT_NOTIFICATION_TYPE_SQL, (preparedStatement -> {
                for (String templateTypeDisplayName : templateTypeDisplayNames) {
                    preparedStatement.setString(TYPE_KEY, templateTypeDisplayName.toLowerCase());
                    preparedStatement.setString(NAME, templateTypeDisplayName);
                    preparedStatement.setString(CHANNEL, notificationChannel);
                    preparedStatement.setInt(TENANT_ID, tenantId);
                    preparedStatement.addBatch();
                }
            }), notificationChannel);
        } catch (DataAccessException e) {
            String error = String.format("Error while adding the %s template types to database in %s tenant.",
                    notificationChannel, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    public void migrateOrgNotificationTemplates(List<NotificationTemplate> orgTemplates, String channel,
                                                boolean isDryRun, int tenantId)
            throws NotificationTemplateManagerServerException {

        if (orgTemplates == null || orgTemplates.isEmpty()) {
            LOG.info("RDM-NT>> No {} org templates to migrate in tenant: {}.", channel, tenantId);
            return;
        }

        LOG.info("RDM-NT>> {} {} org template picked for migration from Registry in tenant: {}.",
                orgTemplates.size(), channel, tenantId);

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        int noOfRecordsAdded;

        try {
            // Perform the migration in a transaction
            noOfRecordsAdded = namedJdbcTemplate.withTransaction(template -> {
                // Get all existing org template keys in the DB
                List<Pair<String, String>> existingTemplateKeys =
                        namedJdbcTemplate.executeQuery(LIST_ORG_NOTIFICATION_TEMPLATE_KEYS_SQL,
                                (resultSet, rowNumber) -> {
                                    String templateKey = resultSet.getString(TEMPLATE_KEY);
                                    String scenarioKey = resultSet.getString(TYPE_KEY);
                                    return Pair.of(templateKey, scenarioKey);
                                }, preparedStatement -> {
                                    preparedStatement.setString(CHANNEL, channel);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                });
                LOG.info("RDM-NT>> {} {} org templates already exists in Database in tenant: {}.",
                        existingTemplateKeys.size(), channel, tenantId);

                List<NotificationTemplate> orgTemplatesToAdd = new ArrayList<>();

                if (!existingTemplateKeys.isEmpty()) {
                    // Filter out the scenarios that are already in the DB
                    for (NotificationTemplate notificationTemplate : orgTemplates) {
                        String templateKey = notificationTemplate.getLocale().toLowerCase();
                        String scenarioKey = notificationTemplate.getType().toLowerCase();
                        if (existingTemplateKeys.contains(Pair.of(templateKey, scenarioKey))) {
                            LOG.warn("RDM-NT>> Org template already exists in the DB. Skipping template: {} {}.",
                                    templateKey, scenarioKey);
                        } else {
                            orgTemplatesToAdd.add(notificationTemplate);
                        }
                    }
                } else {
                    orgTemplatesToAdd = orgTemplates;
                }

                int size = orgTemplatesToAdd.size();
                LOG.info("RDM-NT>> {} {} org templates selected for migration in tenant: {}.", size, channel, tenantId);

                // Add the filtered scenarios to the DB
                if (!isDryRun) {
                    storeOrgNotificationTemplates(orgTemplatesToAdd, tenantId);
                } else {
                    LOG.info("RDM-NT>> Org notification {} templates migration skipped due to dry run in tenant: {}.",
                            channel, tenantId);
                }
                return size;
            });
        } catch (TransactionException e) {
            String error =
                    String.format("Error while migrating the %s org templates in tenant: %s.", channel, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
        LOG.info("RDM-NT>> {} {} org templates successfully migrated in tenant: {}.", noOfRecordsAdded, channel,
                tenantId);
    }

    private void storeOrgNotificationTemplates(List<NotificationTemplate> orgTemplates, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            namedJdbcTemplate.executeBatchInsert(INSERT_ORG_NOTIFICATION_TEMPLATE_SQL, (preparedStatement -> {
                for (NotificationTemplate notificationTemplate : orgTemplates) {
                    byte[] contentByteArray = getContentByteArray(notificationTemplate);
                    int contentLength = contentByteArray.length;
                    try (InputStream contentStream = new ByteArrayInputStream(contentByteArray)) {
                        String displayName = notificationTemplate.getDisplayName();
                        String locale = notificationTemplate.getLocale();

                        preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                        preparedStatement.setString(LOCALE, locale);
                        preparedStatement.setBinaryStream(CONTENT, contentStream, contentLength);
                        preparedStatement.setString(CONTENT_TYPE, notificationTemplate.getContentType());
                        preparedStatement.setString(TYPE_KEY, displayName.toLowerCase());
                        preparedStatement.setString(CHANNEL, notificationTemplate.getNotificationChannel());
                        preparedStatement.setInt(TENANT_ID, tenantId);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                        preparedStatement.addBatch();
                    } catch (IOException e) {
                        throw new SQLException("Error while processing content stream.", e);
                    }
                }
            }), orgTemplates);
        } catch (DataAccessException e) {
            String error = String.format("Error while adding org templates in tenant: %s.", tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    public void migrateAppNotificationTemplates(Map<String, List<NotificationTemplate>> appTemplatesMap, String channel,
                                                boolean isDryRun, int tenantId)
            throws NotificationTemplateManagerServerException {

        if (appTemplatesMap == null || appTemplatesMap.isEmpty()) {
            LOG.info("RDM-NT>> No {} app templates to migrate in tenant: {}.", channel, tenantId);
            return;
        }

        LOG.info("RDM-NT>> {} {} app templates picked for migration from Registry in tenant: {}.",
                appTemplatesMap.size(), channel, tenantId);

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        int noOfRecordsAdded;

        try {
            // Perform the migration in a transaction
            noOfRecordsAdded = namedJdbcTemplate.withTransaction(template -> {
                // Get all existing app template keys in the DB
                List<Pair<Pair<String, String>, String>> existingTemplateKeys =
                        namedJdbcTemplate.executeQuery(LIST_APP_NOTIFICATION_TEMPLATE_KEYS_SQL,
                                (resultSet, rowNumber) -> {
                                    String template_key = resultSet.getString(TEMPLATE_KEY);
                                    String appUuid = resultSet.getString(APP_ID);
                                    String scenario_key = resultSet.getString(TYPE_KEY);
                                    return Pair.of(Pair.of(template_key, appUuid), scenario_key);
                                }, preparedStatement -> {
                                    preparedStatement.setString(CHANNEL, channel);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                });
                LOG.info("RDM-NT>> {} {} app templates already exists in Database in tenant: {}.",
                        existingTemplateKeys.size(), channel, tenantId);

                Map<String, List<NotificationTemplate>> appTemplatesToAdd = new HashMap<>();

                if (!existingTemplateKeys.isEmpty()) {
                    // Filter out the scenarios that are already in the DB
                    for (Map.Entry<String, List<NotificationTemplate>> entry : appTemplatesMap.entrySet()) {
                        List<NotificationTemplate> appTemplatesFiltered = new ArrayList<>();
                        String applicationUuid = entry.getKey();
                        List<NotificationTemplate> appTemplates = entry.getValue();
                        for (NotificationTemplate notificationTemplate : appTemplates) {
                            String template_key = notificationTemplate.getLocale().toLowerCase();
                            String scenario_key = notificationTemplate.getType().toLowerCase();
                            if (existingTemplateKeys.contains(
                                    Pair.of(Pair.of(template_key, applicationUuid), scenario_key))) {
                                LOG.warn("RDM-NT>> App template already exists in the DB. Skipping template: {} {} {}.",
                                        template_key, applicationUuid, scenario_key);
                            } else {
                                appTemplatesFiltered.add(notificationTemplate);
                            }
                        }
                        appTemplatesToAdd.put(applicationUuid, appTemplatesFiltered);
                    }
                } else {
                    appTemplatesToAdd = appTemplatesMap;
                }

                int size = appTemplatesToAdd.size();
                LOG.info("RDM-NT>> {} {} app templates selected for migration in tenant: {}.", size, channel, tenantId);

                // Add the filtered scenarios to the DB
                if (!isDryRun) {
                    storeAppNotificationTemplates(appTemplatesToAdd, tenantId);
                } else {
                    LOG.info("RDM-NT>> App notification {} templates migration skipped due to dry run in tenant: {}.",
                            channel, tenantId);
                }
                return size;
            });
        } catch (TransactionException e) {
            String error =
                    String.format("Error while migrating the %s app templates in tenant: %s.", channel, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
        LOG.info("RDM-NT>> {} {} app templates successfully migrated in tenant: {}.", noOfRecordsAdded, channel,
                tenantId);
    }

    private void storeAppNotificationTemplates(Map<String, List<NotificationTemplate>> appTemplatesMap, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            namedJdbcTemplate.executeBatchInsert(INSERT_APP_NOTIFICATION_TEMPLATE_SQL, (preparedStatement -> {
                for (Map.Entry<String, List<NotificationTemplate>> entry : appTemplatesMap.entrySet()) {
                    String applicationUuid = entry.getKey();
                    List<NotificationTemplate> notificationTemplates = entry.getValue();

                    for (NotificationTemplate template : notificationTemplates) {
                        byte[] contentByteArray = getContentByteArray(template);
                        int contentLength = contentByteArray.length;
                        try (InputStream contentStream = new ByteArrayInputStream(contentByteArray)) {
                            String displayName = template.getDisplayName();
                            String locale = template.getLocale();

                            preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                            preparedStatement.setString(LOCALE, locale);
                            preparedStatement.setBinaryStream(CONTENT, contentStream, contentLength);
                            preparedStatement.setString(CONTENT_TYPE, template.getContentType());
                            preparedStatement.setString(TYPE_KEY, displayName.toLowerCase());
                            preparedStatement.setString(CHANNEL, template.getNotificationChannel());
                            preparedStatement.setInt(TENANT_ID, tenantId);
                            preparedStatement.setString(APP_ID, applicationUuid);
                            preparedStatement.setInt(TENANT_ID, tenantId);
                            preparedStatement.addBatch();
                        } catch (IOException e) {
                            throw new SQLException("Error while processing content stream.", e);
                        }
                    }
                }
            }), appTemplatesMap);
        } catch (DataAccessException e) {
            String error = String.format("Error while adding app templates in tenant: %s.", tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    public void deleteSystemDefaultNotificationTemplates(List<NotificationTemplate> systemDefaultNotificationTemplates,
                                                         String channel, boolean isDryRun, int tenantId)
            throws NotificationTemplateManagerServerException {

        if (systemDefaultNotificationTemplates == null || systemDefaultNotificationTemplates.isEmpty()) {
            LOG.info("RDM-NT>> No system default {} templates to cleanup in tenant: {}.", channel, tenantId);
            return;
        }

        LOG.info("RDM-NT>> {} system default {} template picked for cleanup from database in tenant: {}.",
                systemDefaultNotificationTemplates.size(), channel, tenantId);

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        int noOfRecordsCleaned;

        try {
            // Perform the migration in a transaction
            noOfRecordsCleaned = namedJdbcTemplate.withTransaction(template -> {
                // Get all existing org template keys in the DB
                List<NotificationTemplate> existingNotificationTemplates =
                        namedJdbcTemplate.executeQuery(LIST_ORG_NOTIFICATION_TEMPLATES_SQL,
                                (resultSet, rowNumber) -> {
                                    NotificationTemplate notificationTemplate = new NotificationTemplate();
                                    notificationTemplate.setType(resultSet.getString(TYPE_KEY));
                                    notificationTemplate.setDisplayName(resultSet.getString(NAME));
                                    notificationTemplate.setLocale(resultSet.getString(LOCALE));
                                    setContent(resultSet.getBinaryStream(CONTENT), notificationTemplate);
                                    notificationTemplate.setContentType(resultSet.getString(CONTENT_TYPE));
                                    notificationTemplate.setNotificationChannel(channel);
                                    return notificationTemplate;
                                }, preparedStatement -> {
                                    preparedStatement.setString(CHANNEL, channel);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                    preparedStatement.setInt(TENANT_ID, tenantId);
                                });
                LOG.info("RDM-NT>> {} {} org templates exists in Database in tenant: {}.",
                        existingNotificationTemplates.size(), channel, tenantId);

                List<NotificationTemplate> orgTemplatesToRemove = new ArrayList<>();

                if (!existingNotificationTemplates.isEmpty()) {
                    // Filter out the scenarios that are already in the DB
                    for (NotificationTemplate existingNotificationTemplate : existingNotificationTemplates) {
                        if (systemDefaultNotificationTemplates.contains(existingNotificationTemplate)) {
                            orgTemplatesToRemove.add(existingNotificationTemplate);
                            LOG.info(
                                    "RDM-NT>> System default template found in db for {} template: {} {} in tenant {}.",
                                    channel, existingNotificationTemplate.getLocale(),
                                    existingNotificationTemplate.getType(), tenantId);
                        }
                    }
                }

                int size = orgTemplatesToRemove.size();
                LOG.info("RDM-NT>> {} {} org templates selected for cleanup in tenant: {}.", size, channel, tenantId);

                // Add the filtered scenarios to the DB
                if (!isDryRun) {
                    removeOrgTemplates(orgTemplatesToRemove, tenantId);
                } else {
                    LOG.info("RDM-NT>> System default {} templates cleanup skipped due to dry run in tenant: {}.",
                            channel, tenantId);
                }
                return size;
            });
        } catch (TransactionException e) {
            String error =
                    String.format("Error while cleaning default %s org templates in tenant: %s.", channel, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
        LOG.info("RDM-NT>> {} {} default templates successfully cleaned in tenant: {}.", noOfRecordsCleaned, channel,
                tenantId);
    }

    private void removeOrgTemplates(List<NotificationTemplate> orgTemplates, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            namedJdbcTemplate.executeBatchInsert(DELETE_ORG_NOTIFICATION_TEMPLATE_SQL, (preparedStatement -> {
                for (NotificationTemplate notificationTemplate : orgTemplates) {
                    preparedStatement.setString(TEMPLATE_KEY, notificationTemplate.getLocale().toLowerCase());
                    preparedStatement.setString(TYPE_KEY, notificationTemplate.getType().toLowerCase());
                    preparedStatement.setString(CHANNEL, notificationTemplate.getNotificationChannel());
                    preparedStatement.setInt(TENANT_ID, tenantId);
                    preparedStatement.setInt(TENANT_ID, tenantId);
                    preparedStatement.addBatch();
                }
            }), orgTemplates);
        } catch (DataAccessException e) {
            String error = String.format("Error while cleaning system default org templates in tenant: %s.", tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    /**
     * Remove all the notification templates for a given tenant.
     *
     * @param tenantId
     * @throws NotificationTemplateManagerServerException
     */
    public void removeTemplatesForTenant(boolean isDryRun, int tenantId) throws NotificationTemplateManagerServerException {

        cleanupNotificationTemplates("app", COUNT_ALL_APP_NOTIFICATION_TYPES_SQL,
                DELETE_ALL_APP_NOTIFICATION_TEMPLATES_SQL, isDryRun, tenantId);
        cleanupNotificationTemplates("org", COUNT_ALL_ORG_NOTIFICATION_TYPES_SQL,
                DELETE_ALL_ORG_NOTIFICATION_TEMPLATES_SQL, isDryRun, tenantId);
        cleanupNotificationTemplates("template types", COUNT_ALL_NOTIFICATION_TYPES_SQL,
                DELETE_ALL_NOTIFICATION_SCENARIOS_SQL, isDryRun, tenantId);
    }

    private void cleanupNotificationTemplates(String type, String countSql, String deleteSql, boolean isDryRun,
                                              int tenantId) throws NotificationTemplateManagerServerException {

        try {
            NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
            List<Integer> countList = namedJdbcTemplate.executeQuery(countSql,
                    (resultSet, rowNumber) -> resultSet.getInt(COUNT),
                    preparedStatement -> preparedStatement.setInt(TENANT_ID, tenantId));
            int notificationRecordCount = countList.get(0);
            LOG.info("RDM-NT>> Notification {} {} templates exists in Database in org: {}.",
                    notificationRecordCount, type, tenantId);

            if (!isDryRun && notificationRecordCount > 0) {
                NamedJdbcTemplate namedJdbcTemplate2 = JdbcUtils.getNewNamedJdbcTemplate();
                namedJdbcTemplate2.executeUpdate(deleteSql,
                        preparedStatement -> preparedStatement.setInt(TENANT_ID, tenantId));
                LOG.info("RDM-NT>> Deleted all {} notification {} templates for tenant: {}.", notificationRecordCount,
                        type, tenantId);
            }
        } catch (DataAccessException e) {
            String errorMsg = String.format("Error while deleting %s template data from %s tenant.", type, tenantId);
            throw new NotificationTemplateManagerServerException(errorMsg, e);
        }
    }

    /**
     * Remove the given org notification template from the database.
     *
     * @param notificationTemplate Org notification template to be removed.
     * @param tenantId             Tenant ID.
     * @throws NotificationTemplateManagerServerException If an error occurred while removing the notification template.
     */
    public void removeOrgNotificationTemplate(NotificationTemplate notificationTemplate, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        String channelName = notificationTemplate.getNotificationChannel();
        String locale = notificationTemplate.getLocale();
        String templateType = notificationTemplate.getType();
        try {
            namedJdbcTemplate.executeUpdate(DELETE_ORG_NOTIFICATION_TEMPLATE_SQL,
                    preparedStatement -> {
                        preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                        preparedStatement.setString(TYPE_KEY, templateType.toLowerCase());
                        preparedStatement.setString(CHANNEL, channelName);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                    });
        } catch (DataAccessException e) {
            String error =
                    String.format("Error while deleting %s template %s of type %s from %s tenant.", channelName, locale,
                            templateType, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    /**
     * Check whether the given notification template type exists in the database.
     *
     * @param type        Notification template type.
     * @param channelName Notification channel name.
     * @param tenantId    Tenant ID.
     * @return True if the notification template type exists, false otherwise.
     * @throws NotificationTemplateManagerServerException If an error occurred while checking the existence.
     */
    public boolean isExistNotificationTemplateType(String type, String channelName, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            return namedJdbcTemplate.fetchSingleRecord(GET_NOTIFICATION_TYPE_COUNT_SQL,
                    (resultSet, rowNumber) -> resultSet.getInt(1) > 0,
                    preparedStatement -> {
                        preparedStatement.setString(TYPE_KEY, type);
                        preparedStatement.setString(CHANNEL, channelName);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                    });
        } catch (DataAccessException e) {
            String error = String.format("Error while checking the existence of %s template type %s for tenant %s.",
                    channelName, type, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    /**
     * Add a new notification template type to the database.
     *
     * @param type        Notification template type.
     * @param displayName Display name of the notification template type.
     * @param channelName Notification channel name.
     * @param tenantId    Tenant ID.
     * @throws NotificationTemplateManagerServerException If an error occurred while adding the notification template
     *                                                    type.
     */
    public void addNotificationTemplateType(String type, String displayName, String channelName, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        try {
            namedJdbcTemplate.executeInsert(INSERT_NOTIFICATION_TYPE_SQL, (preparedStatement -> {
                preparedStatement.setString(TYPE_KEY, type);
                preparedStatement.setString(NAME, displayName);
                preparedStatement.setString(CHANNEL, channelName);
                preparedStatement.setInt(TENANT_ID, tenantId);
            }), displayName, false);
        } catch (DataAccessException e) {
            String error =
                    String.format("Error while adding the %s template type %s to %s tenant.", channelName, type,
                            tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
    }

    /**
     * Add a new notification template to the database.
     *
     * @param notificationTemplate Notification template to be added.
     * @param tenantId             Tenant ID.
     * @throws NotificationTemplateManagerServerException If an error occurred while adding the notification template.
     */
    public void addNotificationTemplate(NotificationTemplate notificationTemplate, int tenantId)
            throws NotificationTemplateManagerServerException {

        String displayName = notificationTemplate.getDisplayName();
        String locale = notificationTemplate.getLocale();
        String channelName = notificationTemplate.getNotificationChannel();

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        byte[] contentByteArray = getContentByteArray(notificationTemplate);
        int contentLength = contentByteArray.length;
        try (InputStream contentStream = new ByteArrayInputStream(contentByteArray)) {
            namedJdbcTemplate.executeInsert(INSERT_ORG_NOTIFICATION_TEMPLATE_SQL, (preparedStatement -> {
                preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                preparedStatement.setString(LOCALE, locale);
                preparedStatement.setBinaryStream(CONTENT, contentStream, contentLength);
                preparedStatement.setString(CONTENT_TYPE, notificationTemplate.getContentType());
                preparedStatement.setString(TYPE_KEY, displayName.toLowerCase());
                preparedStatement.setString(CHANNEL, channelName);
                preparedStatement.setInt(TENANT_ID, tenantId);
                preparedStatement.setInt(TENANT_ID, tenantId);
            }), notificationTemplate, false);
        } catch (DataAccessException e) {
            String error =
                    String.format("Error while adding %s template %s of type %s to %s tenant.", channelName,
                            locale, displayName, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        } catch (IOException e) {
            throw new NotificationTemplateManagerServerException("Error while processing content stream.", e);
        }
    }

    /**
     * List all the notification templates for a given channel and tenant.
     *
     * @param channelName Notification channel name.
     * @param tenantId    Tenant ID.
     * @return List of notification templates.
     * @throws NotificationTemplateManagerServerException If an error occurred while listing the notification templates.
     */
    public List<NotificationTemplate> listOrgNotificationTemplates(String channelName, int tenantId)
            throws NotificationTemplateManagerServerException {

        NamedJdbcTemplate namedJdbcTemplate = JdbcUtils.getNewNamedJdbcTemplate();
        List<NotificationTemplate> notificationTemplates;
        try {
            notificationTemplates = namedJdbcTemplate.executeQuery(LIST_ORG_NOTIFICATION_TEMPLATES_SQL,
                    (resultSet, rowNumber) -> {
                        NotificationTemplate notificationTemplateResult = new NotificationTemplate();
                        notificationTemplateResult.setType(resultSet.getString(TYPE_KEY));
                        notificationTemplateResult.setDisplayName(resultSet.getString(NAME));
                        notificationTemplateResult.setLocale(resultSet.getString(LOCALE));
                        setContent(resultSet.getBinaryStream(CONTENT), notificationTemplateResult);
                        notificationTemplateResult.setContentType(resultSet.getString(CONTENT_TYPE));
                        notificationTemplateResult.setNotificationChannel(channelName);
                        return notificationTemplateResult;
                    },
                    preparedStatement -> {
                        preparedStatement.setString(CHANNEL, channelName);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                        preparedStatement.setInt(TENANT_ID, tenantId);
                    });
        } catch (DataAccessException e) {
            String error =
                    String.format("Error while listing %s templates in DB from %s tenant.", channelName, tenantId);
            throw new NotificationTemplateManagerServerException(error, e);
        }
        return notificationTemplates;
    }
}
