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

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

import org.wso2.carbon.identity.core.migrate.MigrationClientException;
import org.wso2.carbon.identity.organization.management.service.model.Organization;
import org.wso2.carbon.user.api.Tenant;
import org.wso2.carbon.utils.multitenancy.MultitenantConstants;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Set;
import java.util.TimeZone;

import javax.sql.DataSource;

import static java.time.ZoneOffset.UTC;

/**
 * DAO for organization related operations.
 */
public class OrganizationDAO {

    private static final String GET_TENANTS_WITHOUT_ORG = "SELECT UM_DOMAIN_NAME, UM_EMAIL, UM_ACTIVE FROM UM_TENANT " +
            "WHERE UM_ORG_UUID IS NULL";
    private static final String GET_SUPER_TENANT_ID = "SELECT UM_ID FROM UM_ORG WHERE UM_ORG_NAME='Super'";
    private static final String INSERT_ORG_DETAILS = "INSERT INTO UM_ORG(UM_ID, UM_ORG_NAME, " +
            "UM_ORG_DESCRIPTION, UM_CREATED_TIME, UM_LAST_MODIFIED, UM_STATUS,UM_PARENT_ID, UM_ORG_TYPE) " +
            "values(?,?,?,?,?,?,?,?)";
    private static final String INSERT_ORG_HIERARCHY = "INSERT INTO UM_ORG_HIERARCHY( UM_PARENT_ID, UM_ID, DEPTH) " +
            "VALUES(?,?,?)";
    private static final String INSERT_ORG_UUID_TO_TENANT = "UPDATE UM_TENANT SET UM_ORG_UUID=? WHERE UM_DOMAIN_NAME=?";
    private static final String DELETE_ORG_DETAILS = "DELETE FROM UM_ORG WHERE UM_ID=?";
    private static final String DELETE_ORG_HIERARCHY = "DELETE FROM UM_ORG_HIERARCHY WHERE UM_PARENT_ID=? AND UM_ID=?";
    private static final String DELETE_ORG_UUID_FROM_TENANT = "UPDATE UM_TENANT SET UM_ORG_UUID=NULL WHERE" +
            " UM_DOMAIN_NAME=?";
    private static final String GET_ORGANIZATIONS = "SELECT UM_ID, UM_ORG_NAME FROM UM_ORG WHERE UM_ORG_TYPE='TENANT'";
    private static final String GET_ORG_ID_BY_NAME = "SELECT UM_ORG_UUID FROM UM_TENANT WHERE UM_DOMAIN_NAME = ?";
    private static final Calendar CALENDAR = Calendar.getInstance(TimeZone.getTimeZone(UTC));

    private DataSource dataSource;

    /**
     * Get tenants without organization.
     *
     * @return Set of tenants without organization.
     * @throws MigrationClientException if error occurred while retrieving tenants without organization.
     */
    public Set<Tenant> getTenantsWithoutOrganization(Connection connection) throws MigrationClientException {

        Set<Tenant> tenants = new HashSet<>();
        try (PreparedStatement prepStmt = connection.prepareStatement(GET_TENANTS_WITHOUT_ORG)) {
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                Tenant tenant = new Tenant();
                tenant.setDomain(resultSet.getString("UM_DOMAIN_NAME"));
                tenant.setEmail(resultSet.getString("UM_EMAIL"));
                tenant.setActive(resultSet.getBoolean("UM_ACTIVE"));
                tenants.add(tenant);
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving tenants without organization", e);
        }
        return tenants;
    }

    /**
     * Get super tenant id.
     *
     * @param connection connection.
     * @return super tenant id.
     * @throws MigrationClientException if error occurred while retrieving super tenant id.
     */
    public String getSuperTenantId(Connection connection) throws MigrationClientException {

        String superTenantId = "";
        try (PreparedStatement prepStmt = connection.prepareStatement(GET_SUPER_TENANT_ID)) {
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                superTenantId = resultSet.getString("UM_ID");
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving super tenant id", e);
        }
        return superTenantId;
    }

    /**
     * Insert organization details.
     *
     * @param connection     connection.
     * @param orgId          organization id.
     * @param orgName        organization name.
     * @param orgDescription organization description.
     * @param parentId       parent organization id.
     * @param status         status.
     * @param orgType        organization type.
     * @throws MigrationClientException if error occurred while inserting organization details.
     */
    public void insertOrganizationDetails(Connection connection, String orgId, String orgName, String orgDescription,
                                          String parentId,
                                          String status, String orgType) throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(INSERT_ORG_DETAILS)) {
            prepStmt.setString(1, orgId);
            prepStmt.setString(2, orgName);
            prepStmt.setString(3, orgDescription);
            prepStmt.setTimestamp(4, Timestamp.from(Instant.now()), CALENDAR);
            prepStmt.setTimestamp(5, Timestamp.from(Instant.now()), CALENDAR);
            prepStmt.setString(6, status);
            prepStmt.setString(7, parentId);
            prepStmt.setString(8, orgType);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while inserting organization details", e);
        }
    }

    /**
     * Insert organization hierarchy.
     *
     * @param connection connection.
     * @param parentId   parent organization id.
     * @param orgId      organization id.
     * @param depth      organization depth.
     * @throws MigrationClientException if error occurred while inserting organization hierarchy.
     */
    public void insertOrganizationHierarchy(Connection connection, String parentId, String orgId, int depth)
            throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(INSERT_ORG_HIERARCHY)) {
            prepStmt.setString(1, parentId);
            prepStmt.setString(2, orgId);
            prepStmt.setInt(3, depth);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while inserting organization hierarchy", e);
        }
    }

    /**
     * Insert organization uuid to tenant.
     *
     * @param connection   connection.
     * @param orgId        organization id.
     * @param tenantDomain tenant domain.
     * @throws MigrationClientException if error occurred while inserting organization uuid to tenant.
     */
    public void insertOrganizationUUIDToTenant(Connection connection, String orgId, String tenantDomain)
            throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(INSERT_ORG_UUID_TO_TENANT)) {
            prepStmt.setString(1, orgId);
            prepStmt.setString(2, tenantDomain);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while inserting organization uuid to tenant", e);
        }
    }

    /**
     * Delete organization hierarchy.
     *
     * @param connection   connection.
     * @param parentId parent organization id.
     * @param orgId    organization id.
     * @throws MigrationClientException if error occurred while deleting organization hierarchy.
     */
    public void deleteOrganizationHierarchy(Connection connection, String parentId, String orgId) throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(DELETE_ORG_HIERARCHY)) {
            prepStmt.setString(1, parentId);
            prepStmt.setString(2, orgId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting organization hierarchy", e);
        }
    }

    /**
     * Delete organization details.
     *
     * @param connection   connection.
     * @param orgId organization id.
     * @throws MigrationClientException if error occurred while deleting organization details.
     */
    public void deleteOrganizationDetails(Connection connection, String orgId) throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(DELETE_ORG_DETAILS)) {
            prepStmt.setString(1, orgId);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting organization details", e);
        }
    }

    /**
     * Delete organization uuid from tenant.
     *
     * @param connection   connection.
     * @param tenantDomain tenant domain.
     * @throws MigrationClientException if error occurred while deleting organization uuid from tenant.
     */
    public void deleteOrganizationUUIDFromTenant(Connection connection, String tenantDomain) throws MigrationClientException {

        try (PreparedStatement prepStmt = connection.prepareStatement(DELETE_ORG_UUID_FROM_TENANT)) {
            prepStmt.setString(1, tenantDomain);
            prepStmt.execute();
        } catch (SQLException e) {
            throw new MigrationClientException("Error while deleting organization uuid from tenant", e);
        }
    }

    /**
     * Get organizations.
     *
     * @param connection connection.
     * @return Set of organizations.
     * @throws MigrationClientException if error occurred while retrieving organizations.
     */
    public Set<Organization> getOrganizations(Connection connection) throws MigrationClientException {

        Set<Organization> organizations = new HashSet<>();
        try (PreparedStatement prepStmt = connection.prepareStatement(GET_ORGANIZATIONS)) {
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                Organization organization = new Organization();
                organization.setId(resultSet.getString("UM_ID"));
                organization.setName(resultSet.getString("UM_ORG_NAME"));
                organizations.add(organization);
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving organizations", e);
        }
        return organizations;
    }

    /**
     * Get organization id by name.
     *
     * @param orgName organization name.
     * @return organization id.
     * @throws MigrationClientException if error occurred while retrieving organization id.
     */
    public String getOrganizationIdByName(Connection connection, String orgName) throws MigrationClientException {

        if (MultitenantConstants.SUPER_TENANT_DOMAIN_NAME.equalsIgnoreCase(orgName)) {
            return getSuperTenantId(connection);
        }
        try (PreparedStatement prepStmt = connection.prepareStatement(GET_ORG_ID_BY_NAME)) {
            prepStmt.setString(1, orgName);
            ResultSet resultSet = prepStmt.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString("UM_ORG_UUID");
            }
        } catch (SQLException e) {
            throw new MigrationClientException("Error while retrieving organization id", e);
        }
        return null;
    }
}
