/*
 * Copyright (c) 2024, 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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.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.BODY;
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_TYPE;
import static org.wso2.carbon.is.migration.service.v710.constants.NotificationSQLConstants.NotificationTableColumns.FOOTER;
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.SUBJECT;
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) {
                    String displayName = notificationTemplate.getDisplayName();
                    String locale = notificationTemplate.getLocale();

                    preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                    preparedStatement.setString(LOCALE, locale);
                    preparedStatement.setString(SUBJECT, notificationTemplate.getSubject());
                    preparedStatement.setString(BODY, notificationTemplate.getBody());
                    preparedStatement.setString(FOOTER, notificationTemplate.getFooter());
                    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();
                }
            }), 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) {
                        String displayName = template.getDisplayName();
                        String locale = template.getLocale();

                        preparedStatement.setString(TEMPLATE_KEY, locale.toLowerCase());
                        preparedStatement.setString(LOCALE, locale);
                        preparedStatement.setString(SUBJECT, template.getSubject());
                        preparedStatement.setString(BODY, template.getBody());
                        preparedStatement.setString(FOOTER, template.getFooter());
                        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();
                    }
                }
            }), 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));
                                    notificationTemplate.setSubject(resultSet.getString(SUBJECT));
                                    notificationTemplate.setBody(resultSet.getString(BODY));
                                    notificationTemplate.setFooter(resultSet.getString(FOOTER));
                                    notificationTemplate.setContentType(resultSet.getString(CONTENT_TYPE));
                                    notificationTemplate.setNotificationChannel(resultSet.getString(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 {}.",
                                    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);
        }
    }
}
