/*
 * 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.DataAccessException;
import org.wso2.carbon.database.utils.jdbc.exceptions.TransactionException;
import org.wso2.carbon.identity.core.migrate.MigrationClientException;
import org.wso2.carbon.identity.core.util.IdentityDatabaseUtil;
import org.wso2.carbon.is.migration.service.v700.model.Group;
import org.wso2.carbon.user.core.UserCoreConstants;

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 static org.wso2.carbon.is.migration.service.v700.util.Utils.getNewTemplateForSharedDB;

/**
 * DAO implementation group.
 */
public class GroupDAO {

    private static final String GET_ROLE_NAMES = "SELECT UM_ROLE_NAME, UM_TENANT_ID from UM_ROLE";
    private static final String GET_ATTRIBUTES = "SELECT ATTR_NAME, ATTR_VALUE FROM IDN_SCIM_GROUP WHERE " +
            "ROLE_NAME = ? AND TENANT_ID = ? AND AUDIENCE_REF_ID = -1";
    private static final String GET_CURRENT_ATTRIBUTES = "SELECT UM_ROLE_UUID, UM_CREATED_TIME, UM_LAST_MODIFIED" +
            " FROM UM_ROLE WHERE UM_ROLE_NAME = ?";
    private static final String GET_OTHER_ROLE_NAMES = "SELECT DISTINCT ROLE_NAME FROM IDN_SCIM_GROUP WHERE " +
            "LOWER(ROLE_NAME) NOT LIKE 'internal/%' AND ROLE_NAME NOT LIKE 'PRIMARY/%'";
    private static final String UPDATE_UM_ROLE_UUID = "UPDATE UM_ROLE SET UM_ROLE_UUID = ? WHERE UM_ROLE_NAME = ?";
    private static final String UPDATE_UM_CREATED_TIME = "UPDATE UM_ROLE SET UM_CREATED_TIME = ? " +
            "WHERE UM_ROLE_NAME = ?";
    private static final String UPDATE_UM_LAST_MODIFIED = "UPDATE UM_ROLE SET UM_LAST_MODIFIED = ? " +
            "WHERE UM_ROLE_NAME = ?";
    private final String UM_ROLE_NAME = "UM_ROLE_NAME";
    private final String UM_ROLE_UUID = "UM_ROLE_UUID";
    private final String UM_CREATED_TIME = "UM_CREATED_TIME";
    private final String UM_LAST_MODIFIED = "UM_LAST_MODIFIED";
    private final String UM_TENANT_ID = "UM_TENANT_ID";
    private final String ATTR_NAME = "ATTR_NAME";
    private final String ATTR_VALUE = "ATTR_VALUE";
    private final String ROLE_NAME = "ROLE_NAME";

    /**
     * Get group data.
     *
     * @return Group data list.
     * @throws MigrationClientException If error occurred while getting the groups.
     */
    public List<Group> getGroups() throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        try {
            return namedJdbcTemplate.executeQuery(GET_ROLE_NAMES,
                    (resultSet, rowNumber) -> new Group(resultSet.getString(UM_ROLE_NAME),
                            resultSet.getInt(UM_TENANT_ID)));
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving roles data.");
        }
    }

    /**
     * Updating the group object attributes.
     *
     * @param group Group data.
     * @throws MigrationClientException If error occurred while getting the attributes.
     */
    public void resolveAttributesFromScimData(Group group) throws MigrationClientException {

        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(GET_ATTRIBUTES)) {
            prepStmt.setString(1,
                    UserCoreConstants.PRIMARY_DEFAULT_DOMAIN_NAME + UserCoreConstants.DOMAIN_SEPARATOR +
                            group.getRoleName());
            prepStmt.setInt(2, group.getTenantID());
            ResultSet resultSet = prepStmt.executeQuery();

            while (resultSet.next()) {
                String attributeName = resultSet.getString(ATTR_NAME);
                String attributeValue = resultSet.getString(ATTR_VALUE);
                group.setAttribute(attributeName, attributeValue);
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving the attribute values.", e);
        }
    }

    /**
     * Get the role names except primary and internal from IDN_SCIM_GROUP table.
     *
     * @return List of domains except primary and internal.
     * @throws MigrationClientException If error occurred while retrieving the role names.
     */
    public List<String> getOtherRoleNames() throws MigrationClientException {

        List<String> roleNames = new ArrayList<>();
        try (Connection connection = IdentityDatabaseUtil.getDBConnection(false);
             PreparedStatement prepStmt = connection.prepareStatement(GET_OTHER_ROLE_NAMES)) {
            ResultSet resultSet = prepStmt.executeQuery();

            while (resultSet.next()) {
                String roleName = resultSet.getString(ROLE_NAME);
                roleNames.add(roleName);
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving the role names.", e);
        }
        return roleNames;
    }

    /**
     * Update the attributes in the role name table if value is not already updated.
     *
     * @param group group data.
     * @throws MigrationClientException If error occurred while updating group.
     */
    public void updateGroupAttributesInUserStore(Group group) throws MigrationClientException {

        group.setDefaultIfNull();
        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();

        Group existingGroup = getCurrentAttributesFromUserStore(group.getRoleName(), group.getTenantID());

        try {
            if (!StringUtils.equals(existingGroup.getRoleUUID(), group.getRoleUUID())) {
                namedJdbcTemplate.withTransaction(template -> {
                    template.executeUpdate(UPDATE_UM_ROLE_UUID,
                            namedPreparedStatement -> {
                                namedPreparedStatement.setString(1, group.getRoleUUID());
                                namedPreparedStatement.setString(2, group.getRoleName());
                            });
                    return null;
                });
            }

            if (group.getCreated().compareTo(existingGroup.getCreated()) != 0) {
                namedJdbcTemplate.withTransaction(template -> {
                    template.executeUpdate(UPDATE_UM_CREATED_TIME,
                            namedPreparedStatement -> {
                                namedPreparedStatement.setTimestamp(1, group.getCreated());
                                namedPreparedStatement.setString(2, group.getRoleName());
                            });
                    return null;
                });
            }

            if (group.getModified().compareTo(existingGroup.getModified()) != 0) {
                namedJdbcTemplate.withTransaction(template -> {
                    template.executeUpdate(UPDATE_UM_LAST_MODIFIED,
                            namedPreparedStatement -> {
                                namedPreparedStatement.setTimestamp(1, group.getModified());
                                namedPreparedStatement.setString(2, group.getRoleName());
                            });
                    return null;
                });
            }

        } catch (TransactionException e) {
            throw new MigrationClientException("Error while updating the attribute fields.", e);
        }
    }

    /**
     * Get the existing data from the UM_ROLE table.
     *
     * @param roleName role Name.
     * @param tenantID tenant ID.
     * @return Existing group data.
     * @throws MigrationClientException If error occurred while retrieving the group data.
     */
    private Group getCurrentAttributesFromUserStore(String roleName, int tenantID) throws MigrationClientException {

        NamedJdbcTemplate namedJdbcTemplate = getNewTemplateForSharedDB();
        Group group = new Group(roleName, tenantID);
        try {
            namedJdbcTemplate.executeQuery(GET_CURRENT_ATTRIBUTES,
                    (resultSet, rowNumber) -> {
                        group.setRoleUUID(resultSet.getString(UM_ROLE_UUID));
                        group.setCreated(resultSet.getTimestamp(UM_CREATED_TIME));
                        group.setModified(resultSet.getTimestamp(UM_LAST_MODIFIED));
                        return null;
                    },
                    namedPreparedStatement -> {
                        namedPreparedStatement.setString(1, roleName);
                    });
            return group;
        } catch (DataAccessException e) {
            throw new MigrationClientException("Error while retrieving the attributes from the UM_ROLE table.", e);
        }
    }
}
