package pt.iscte.ci.mail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import net.sourceforge.fenixedu._development.PropertiesManager;
import net.sourceforge.fenixedu.applicationTier.security.PasswordEncryptor;
import net.sourceforge.fenixedu.domain.ExecutionYear;
import net.sourceforge.fenixedu.domain.Person;
import net.sourceforge.fenixedu.domain.candidacy.DegreeCandidacy;
import net.sourceforge.fenixedu.domain.exceptions.DomainException;

/**
 * 
 * FIXME: Remove the code to directly access the database from here. Use neftis
 * instead.
 * 
 * @author Paulo Zenida
 * 
 */
public class EmailServerUtils {

    private static final String CHECK_EMAIL_SERVER;

    // GENERAL PROPERTIES
    private static final String USERNAME = "username";

    private static final String CREATED_DATE = "create_date";

    private static final String CHANGE_DATE = "change_date";

    private static final String ACTIVE = "active";

    private static final String DOMAIN = "domain";

    // ALIAS PROPERTIES
    private static final String ALIAS_ADDRESS = "address";

    private static final String ALIAS_GOTO = "goto";

    @SuppressWarnings("unused")
    private static final String ALIAS_DOMAIN = "address";

    private static final String ALIAS_RECORD_TYPE = "record_type";

    // MAILBOX PROPERTIES
    private static final String MAILBOX_NAME = "name";

    private static final String MAILBOX_HOME = "home";

    private static final String MAILBOX_MAILDIR = "maildir";

    private static final String MAILBOX_QUOTA = "quota";

    private static final String MAILBOX_IS_STUDENT = "is_student";

    private static final String MAILBOX_IS_SHARED = "is_shared";

    private static final String MAILBOX_IS_ACCESS_TO_SHARE = "is_access_to_share";

    private static final String ACCESS_TO_SHARE_ACCOUNT_TYPE = "ACCESS_TO_SHARE";

    private static final String MAILBOX_SHARE = "share";

    private static final String MAILBOX_ACCESSED_BY = "accessed_by";

    private static final String MAILBOX_ACCOUNT_TYPE = "account_type";

    private static final String MAILBOX_IS_ARCHIVED = "is_archived";

    @SuppressWarnings("unused")
    private static final String MAILBOX_STATE_CHANGE_DATE = "state_change_date";

    private final static String EMAIL_SERVER_DATABASE_URL = "email.server.database.url";

    private final static String EMAIL_SERVER_DATABASE_USERNAME = "email.server.database.username";

    private final static String EMAIL_SERVER_DATABASE_PASSWORD = "email.server.database.password";

    private final static String EMAIL_SERVER_DATABASE_DRIVER = "email.server.database.driver";

    private static String databaseDriver;

    private static String databaseUrl;

    private static String databaseUsername;

    private static String databasePassword;

    private static final String GET_EMAIL_BY_FORWARD_QUERY = "select * from alias where goto LIKE ?";

    private static final String GET_EMAIL_QUERY = "select * from alias where address = ?";

    private static final String GET_ALIASES_QUERY = "select * from alias where username = ? or goto = ?";

    private static final String GET_ALIAS_QUERY = "select * from alias where username = ? and record_type = '" + AliasType.ALIAS
	    + "'";

    private static final String GET_FORWARD_QUERY = "select * from alias where address = ? and record_type = '"
	    + AliasType.FORWARD + "'";

    private static final String GET_ACCOUNT_QUERY = "select * from mailbox where username = ?";

    private static final String GET_ACTIVE_ACCOUNT_QUERY = "select * from mailbox where username = ? and active = 1";

    private static final String DELETE_ACCOUNT_QUERY = "delete from mailbox where username = ?";

    private static final String DELETE_ALIASES_QUERY = "delete from alias where username = ?";

    private static final String DELETE_ALIAS_BY_ADDRESS_AND_GOTO_AND_USERNAME = "delete from alias where address = ? and goto = ? and username = ?";

    private static final String REGISTER_MAILBOX_QUERY = "insert into mailbox (username, password, name, home, maildir, quota, domain, is_student, "
	    + "is_shared, is_access_to_share, share, accessed_by, create_date, change_date, state_change_date) values (?, ?, ?, '/postfix/', concat('iscte.pt/', ?, '/Maildir/'), "
	    + "'1000000000S', 'iscte.pt', ?, 0, 0, '', '', NOW(), NOW(), NOW());";

    private static final String UPDATE_MAILBOX_QUERY = "update mailbox set username = ? where username = ?";

    private static final String REGISTER_ALIAS_QUERY = "insert into alias (address, goto, domain, create_date, "
	    + "change_date, record_type, username) values (?, ?, 'iscte.pt', NOW(), NOW(), ?, ?);";

    private static final String CHANGE_PASSWORD_QUERY = "update mailbox set password = ? where username = ?;";

    private static final String ACCESS_TO_SHARE_QUERY = "select * from mailbox where account_type=? and accessed_by=?";

    private static final String CHANGE_PASSWORD_FOR_ACCESS_TO_SHARE = "update mailbox set password = ? where account_type=? and accessed_by=?";

    private static final String ALIAS_UPDATE_ADDRESS = "update alias set address = ? where username = ? and address = ?;";

    private static final String ALIAS_UPDATE_FORWARD_ADDRESS = "update alias set goto = ? where username = ? and address = ?;";

    static {
	databaseDriver = PropertiesManager.getProperty(EMAIL_SERVER_DATABASE_DRIVER);
	databaseUrl = PropertiesManager.getProperty(EMAIL_SERVER_DATABASE_URL);
	databaseUsername = PropertiesManager.getProperty(EMAIL_SERVER_DATABASE_USERNAME);
	databasePassword = PropertiesManager.getProperty(EMAIL_SERVER_DATABASE_PASSWORD);
	CHECK_EMAIL_SERVER = PropertiesManager.getProperty("email.server.check.enabled");

	try {
	    Class.forName(databaseDriver);
	} catch (ClassNotFoundException e) {
	    System.out.println("ClassNotFoundException " + e);
	}
    }

    public static void registerAccountAndAlias(final Person person, final int isStudent) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    if (isEmailAlreadyRegistered(connection, person.getInstitutionalEmail())) {
		throw new DomainException("error.email.already.registered");
	    }
	    if (!isAccountRegistered(person.getIstUsername())) {
		registerAccount(person, isStudent, connection);
		registerAliases(person, connection);
		connection.commit();
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	    rollbackConnection(connection, e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static void updateAccount(final String oldUsername, final String newUsername) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    if (isAccountRegistered(oldUsername)) {
		updateAccount(connection, oldUsername, newUsername);
		connection.commit();
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	    rollbackConnection(connection, e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static void changeEMailPassword(final String username, final String newPassword) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    PreparedStatement preparedStatement = connection.prepareStatement(CHANGE_PASSWORD_QUERY);
	    preparedStatement.setString(1, PasswordEncryptor.encryptPassword(newPassword));
	    preparedStatement.setString(2, username);
	    preparedStatement.executeUpdate();

	    System.out.println("E-mail password changed successfully for username " + username);

	    if (userNameHasAccessToShareAccounts(username, connection)) {
		preparedStatement = connection.prepareStatement(CHANGE_PASSWORD_FOR_ACCESS_TO_SHARE);
		preparedStatement.setString(1, PasswordEncryptor.encryptPassword(newPassword));
		preparedStatement.setString(2, ACCESS_TO_SHARE_ACCOUNT_TYPE);
		preparedStatement.setString(3, username);
		preparedStatement.executeUpdate();

		System.out.println("E-mail password changed successfully for access to share accounts for user " + username);
	    }

	    connection.commit();
	} catch (SQLException e) {
	    e.printStackTrace();
	    rollbackConnection(connection, e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static boolean userNameHasAccessToShareAccounts(final String username, final Connection connection)
	    throws SQLException {
	boolean userNameHasAccessToShareAccounts = false;
	final PreparedStatement preparedStatement = connection.prepareStatement(ACCESS_TO_SHARE_QUERY);

	preparedStatement.setString(1, ACCESS_TO_SHARE_ACCOUNT_TYPE);
	preparedStatement.setString(2, username);

	final ResultSet rs = preparedStatement.executeQuery();

	if (rs.next()) {
	    userNameHasAccessToShareAccounts = true;
	}

	rs.close();
	return userNameHasAccessToShareAccounts;
    }

    private static String getUsername(final Person person) {
	String username = person.getIstUsername();
	DegreeCandidacy activeDegreeCandidacyForExecutionYear = person.getActiveDegreeCandidacyForExecutionYear(ExecutionYear
		.readCurrentExecutionYear());
	if (activeDegreeCandidacyForExecutionYear != null) {
	    username = activeDegreeCandidacyForExecutionYear.getExpectedUsername();
	}
	return username;
    }

    public static boolean isEmailAlreadyRegistered(final String email) {
	if (!checkEmailServer()) {
	    return false;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return isEmailAlreadyRegistered(connection, email);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static String getRegisteredEmail(final Connection connection, final String email) throws SQLException {
	String result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_EMAIL_QUERY);
	preparedStatement.setString(1, email);
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    result = rs.getString(ALIAS_ADDRESS);
	}
	rs.close();
	return result;
    }

    public static String getRegisteredEmail(final String username) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredEmail(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    /*
     * public static List<String> getRegisteredAliasesByUsername(final String
     * username) { Connection connection = null; try { connection =
     * openAndConfigureConnection(); return
     * getRegisteredAliasesByUsername(connection, username); } catch
     * (SQLException e) { e.printStackTrace(); throw new
     * DomainException("error.problems.with.email.server.connection", e); }
     * finally { closeConnection(connection); } }
     */

    // Added by jpps@iscte.pt
    public static List<Alias> getRegisteredAliasesByUsername(final String username) {
	if (!checkEmailServer()) {
	    return Collections.EMPTY_LIST;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredAliasesByUsername(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    /*
     * private static List<String> getRegisteredAliasesByUsername(final
     * Connection connection, final String username) throws SQLException { final
     * List<String> result = new ArrayList<String>();
     * 
     * final PreparedStatement preparedStatement =
     * connection.prepareStatement(GET_ALIASES_QUERY);
     * preparedStatement.setString(1, username); preparedStatement.setString(2,
     * username); final ResultSet rs = preparedStatement.executeQuery(); while
     * (rs.next()) { result.add(rs.getString(ALIAS_ADDRESS)); } rs.close();
     * return result;
     * 
     * public Alias(String address, String goTo, String domain, String
     * createDate, String changeDate, AliasType recordType, String username) { }
     */

    private static List<Alias> getRegisteredAliasesByUsername(final Connection connection, final String username)
	    throws SQLException {
	final List<Alias> result = new ArrayList<Alias>(0);

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_ALIASES_QUERY);
	preparedStatement.setString(1, username);
	preparedStatement.setString(2, username);
	final ResultSet rs = preparedStatement.executeQuery();

	while (rs.next()) {
	    result.add(new Alias(rs.getString(ALIAS_ADDRESS), rs.getString(ALIAS_GOTO), rs.getString(DOMAIN), rs.getString(
		    ALIAS_RECORD_TYPE).equals(AliasType.ALIAS.getName()) ? AliasType.ALIAS : AliasType.FORWARD, username));
	}

	rs.close();
	return result;
    }

    private static String getRegisteredAliasByUsername(final Connection connection, final String username) throws SQLException {
	String result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_ALIAS_QUERY);
	preparedStatement.setString(1, username);
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    result = rs.getString(ALIAS_ADDRESS);
	}
	rs.close();
	return result;
    }

    public static String getRegisteredAliasOrForwardByUsername(final String username) {
	final String registeredAlias = getRegisteredAliasByUsername(username);
	return (registeredAlias == null) ? getRegisteredForward(username) : registeredAlias;
    }

    public static String getRegisteredAliasByUsername(final String username) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredAliasByUsername(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static String getRegisteredForward(final Connection connection, final String email) throws SQLException {
	String result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_FORWARD_QUERY);
	preparedStatement.setString(1, email);
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    result = rs.getString(ALIAS_ADDRESS);
	}
	rs.close();
	return result;
    }

    public static String getRegisteredForward(final String username) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredForward(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static boolean isAliasRegisteredForUsername(final String username) {
	if (!checkEmailServer()) {
	    return false;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return (getRegisteredAliasByUsername(connection, username) != null);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static boolean isForwardRegistered(final String username) {
	if (!checkEmailServer()) {
	    return false;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return (getRegisteredForward(connection, username) != null);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static boolean hasAnyAliases(final String username) {
	return isAliasRegisteredForUsername(username) || isForwardRegistered(username);
    }

    public static boolean hasAllAliases(final String username) {
	return isAliasRegisteredForUsername(username) && isForwardRegistered(username);
    }

    public static boolean isAccountRegistered(final String username) {
	if (!checkEmailServer()) {
	    return false;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return (getRegisteredAccount(connection, username) != null);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static boolean isAccountRegisteredAndActive(final String username) {
	if (!checkEmailServer()) {
	    return false;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return (getRegisteredAccountAndActive(connection, username) != null);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    /*
     * public static String getRegisteredAccount(final String username) {
     * Connection connection = null; try { connection =
     * openAndConfigureConnection(); return getRegisteredAccount(connection,
     * username); } catch (SQLException e) { e.printStackTrace(); throw new
     * DomainException("error.problems.with.email.server.connection", e); }
     * finally { closeConnection(connection); } }
     */

    // Added by jpps@iscte.pt
    public static Account getRegisteredAccount(final String username) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredAccount(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static Connection openAndConfigureConnection() throws SQLException {
	Connection connection;
	connection = DriverManager.getConnection(databaseUrl, databaseUsername, databasePassword);
	connection.setAutoCommit(false);
	return connection;
    }

    private static void closeConnection(final Connection connection) {
	try {
	    if (connection != null && !connection.isClosed()) {
		connection.close();
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.sql.close.connection.failed", e);
	}
    }

    private static void rollbackConnection(final Connection connection, final SQLException e) {
	try {
	    connection.rollback();
	} catch (SQLException e2) {
	    e.printStackTrace();
	}
	throw new DomainException("error.sql.exception", e);
    }

    private static void registerAliases(final Person person, final Connection connection) throws SQLException {

	String username = getUsername(person);

	if (person.getInstitutionalEmail().equals(username)) {
	    if (person.getPersonalEmail() != null && person.getPersonalEmail().length() != 0) {
		registerAlias(username, person.getPersonalEmail(), AliasType.FORWARD, username, connection);
	    }
	} else {
	    registerAlias(person.getInstitutionalEmail(), username, AliasType.ALIAS, username, connection);
	    if (person.getPersonalEmail() != null && person.getPersonalEmail().length() != 0) {
		registerAlias(username, person.getPersonalEmail(), AliasType.FORWARD, username, connection);
	    }
	}
    }

    private static void registerAlias(final String address, final String goTo, final AliasType recordType, final String username,
	    final Connection connection) throws SQLException {
	final PreparedStatement preparedStatement = connection.prepareStatement(REGISTER_ALIAS_QUERY);
	preparedStatement.setString(1, address);
	preparedStatement.setString(2, goTo);
	preparedStatement.setString(3, recordType.name());
	preparedStatement.setString(4, username);

	preparedStatement.executeUpdate();
	connection.commit();
    }

    private static void registerAccount(final Person person, final int isStudent, final Connection connection)
	    throws SQLException {
	final PreparedStatement preparedStatement = connection.prepareStatement(REGISTER_MAILBOX_QUERY);

	preparedStatement.setString(1, getUsername(person));
	preparedStatement.setString(2, PasswordEncryptor.encryptPassword(person.getPassword()));
	preparedStatement.setString(3, person.getName());
	final String usernameWithouSuffix = person.getIstUsername().contains("@") ? person.getIstUsername().substring(0,
		person.getIstUsername().indexOf("@")) : person.getIstUsername();
	preparedStatement.setString(4, usernameWithouSuffix);
	preparedStatement.setInt(5, isStudent);

	preparedStatement.executeUpdate();
	connection.commit();
    }

    private static void updateAccount(final Connection connection, final String oldUsername, final String newUsername)
	    throws SQLException {
	final PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_MAILBOX_QUERY);

	preparedStatement.setString(1, newUsername);
	preparedStatement.setString(2, oldUsername);

	preparedStatement.executeUpdate();
	connection.commit();
    }

    public static void updateAlias(final String oldAlias, final String newAlias, final String username) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    updateAlias(connection, oldAlias, newAlias, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    rollbackConnection(connection, e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static void updateAlias(final Connection connection, final String oldAlias, final String newAlias,
	    final String username) throws SQLException {
	final PreparedStatement preparedStatement = connection.prepareStatement(ALIAS_UPDATE_ADDRESS);

	preparedStatement.setString(1, newAlias);
	preparedStatement.setString(2, username);
	preparedStatement.setString(3, oldAlias);

	System.out.println(preparedStatement.toString());
	preparedStatement.executeUpdate();
	connection.commit();
    }

    private static boolean isEmailAlreadyRegistered(final Connection connection, final String email) throws SQLException {
	boolean isEmailAlreadyRegistered = false;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_EMAIL_QUERY);
	preparedStatement.setString(1, email);
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    isEmailAlreadyRegistered = true;
	}
	rs.close();
	return isEmailAlreadyRegistered;
    }

    /*
     * private static String getRegisteredAccount(final Connection connection,
     * final String username) throws SQLException { String result = null;
     * 
     * final PreparedStatement preparedStatement =
     * connection.prepareStatement(GET_ACCOUNT_QUERY);
     * preparedStatement.setString(1, username); final ResultSet rs =
     * preparedStatement.executeQuery(); if (rs.next()) { result =
     * rs.getString(USERNAME); } rs.close(); return result; }
     */

    // Added by jpps@iscte.pt
    private static Account getRegisteredAccount(final Connection connection, final String username) throws SQLException {
	Account result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_QUERY);
	preparedStatement.setString(1, username);
	final ResultSet rs = preparedStatement.executeQuery();

	if (rs.next()) {
	    result = new Account(rs.getString(USERNAME), "", rs.getString(MAILBOX_NAME), rs.getString(MAILBOX_HOME), rs
		    .getString(MAILBOX_MAILDIR), rs.getString(MAILBOX_QUOTA), rs.getString(DOMAIN), rs
		    .getBoolean(MAILBOX_IS_STUDENT), rs.getBoolean(MAILBOX_IS_SHARED), rs.getBoolean(MAILBOX_IS_ACCESS_TO_SHARE),
		    rs.getString(MAILBOX_SHARE), rs.getString(MAILBOX_ACCESSED_BY), rs.getBoolean(ACTIVE), rs
			    .getBoolean(MAILBOX_IS_ARCHIVED), rs.getString(MAILBOX_ACCOUNT_TYPE));
	}
	rs.close();
	return result;
    }

    private static Account getRegisteredAccountAndActive(final Connection connection, final String username) throws SQLException {
	Account result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_ACTIVE_ACCOUNT_QUERY);
	preparedStatement.setString(1, username);
	final ResultSet rs = preparedStatement.executeQuery();

	if (rs.next()) {
	    result = new Account(rs.getString(USERNAME), "", rs.getString(MAILBOX_NAME), rs.getString(MAILBOX_HOME), rs
		    .getString(MAILBOX_MAILDIR), rs.getString(MAILBOX_QUOTA), rs.getString(DOMAIN), rs
		    .getBoolean(MAILBOX_IS_STUDENT), rs.getBoolean(MAILBOX_IS_SHARED), rs.getBoolean(MAILBOX_IS_ACCESS_TO_SHARE),
		    rs.getString(MAILBOX_SHARE), rs.getString(MAILBOX_ACCESSED_BY), rs.getBoolean(ACTIVE), rs
			    .getBoolean(MAILBOX_IS_ARCHIVED), rs.getString(MAILBOX_ACCOUNT_TYPE));
	}
	rs.close();
	return result;
    }

    public static void deleteAccountByUsername(final String username) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    deleteAccountByUsername(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    public static void deleteAliasByAddressAndGoToAndUsername(final String address, final String goTo, final String username) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    deleteAliasByAddressAndGoToAndUsername(connection, address, goTo, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static void deleteAliasByAddressAndGoToAndUsername(final Connection connection, final String address,
	    final String goTo, final String username) throws SQLException {

	final PreparedStatement preparedStatement = connection.prepareStatement(DELETE_ALIAS_BY_ADDRESS_AND_GOTO_AND_USERNAME);
	preparedStatement.setString(1, address);
	preparedStatement.setString(2, goTo);
	preparedStatement.setString(3, username);

	System.out.println(preparedStatement.toString());
	preparedStatement.executeUpdate();
	connection.commit();
    }

    private static void deleteAliasesByUsername(final Connection connection, final String username) throws SQLException {

	final PreparedStatement preparedStatement = connection.prepareStatement(DELETE_ALIASES_QUERY);
	preparedStatement.setString(1, username);
	preparedStatement.executeUpdate();
	connection.commit();
    }

    public static void deleteAliasesByUsername(final String username) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    deleteAliasesByUsername(connection, username);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static void deleteAccountByUsername(final Connection connection, final String username) throws SQLException {

	final PreparedStatement preparedStatement = connection.prepareStatement(DELETE_ACCOUNT_QUERY);
	preparedStatement.setString(1, username);
	preparedStatement.executeUpdate();
	connection.commit();
    }

    private static Alias getRegisteredEmailByForward(final Connection connection, final String forward) throws SQLException {
	Alias result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_EMAIL_BY_FORWARD_QUERY);
	preparedStatement.setString(1, "%" + forward + "%");
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    result = new Alias(rs.getString(ALIAS_ADDRESS), rs.getString(ALIAS_GOTO), rs.getString(DOMAIN), rs
		    .getString(CREATED_DATE), rs.getString(CHANGE_DATE), AliasType.createAliasType(rs
		    .getString(ALIAS_RECORD_TYPE)), rs.getString(USERNAME));
	}

	rs.close();
	return result;
    }

    public static Alias getRegisteredEmailByForward(final String forward) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredEmailByForward(connection, forward);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static Alias getRegisteredEmailByAddress(final Connection connection, final String address) throws SQLException {
	Alias result = null;

	final PreparedStatement preparedStatement = connection.prepareStatement(GET_EMAIL_QUERY);
	preparedStatement.setString(1, address);
	final ResultSet rs = preparedStatement.executeQuery();
	if (rs.next()) {
	    result = new Alias(rs.getString(ALIAS_ADDRESS), rs.getString(ALIAS_GOTO), rs.getString(DOMAIN), rs
		    .getString(CREATED_DATE), rs.getString(CHANGE_DATE), AliasType.createAliasType(rs
		    .getString(ALIAS_RECORD_TYPE)), rs.getString(USERNAME));
	}

	rs.close();
	return result;
    }

    public static Alias getRegisteredEmailByAddress(final String address) {
	if (!checkEmailServer()) {
	    return null;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    return getRegisteredEmailByAddress(connection, address);
	} catch (SQLException e) {
	    e.printStackTrace();
	    throw new DomainException("error.problems.with.email.server.connection", e);
	} finally {
	    closeConnection(connection);
	}
    }

    private static void updateAliasGotoAddress(final Connection connection, final Alias alias) throws SQLException {
	final PreparedStatement preparedStatement = connection.prepareStatement(ALIAS_UPDATE_FORWARD_ADDRESS);
	preparedStatement.setString(1, alias.getGoTo());
	preparedStatement.setString(2, alias.getUsername());
	preparedStatement.setString(3, alias.getAddress());
	int countRowsUpdated = preparedStatement.executeUpdate();
	if (countRowsUpdated != 1) {
	    throw new SQLException("Update did not updated any record");
	}
    }

    /**
     * Updates the GOTO field of the ALIAS table.
     * 
     * @param alias
     *            an Alias object with the fields address and goto not null.
     */
    public static void updateAliasGotoAddress(final Alias alias) {
	if (!checkEmailServer()) {
	    return;
	}
	Connection connection = null;
	try {
	    connection = openAndConfigureConnection();
	    updateAliasGotoAddress(connection, alias);
	} catch (SQLException e) {
	    e.printStackTrace();
	    rollbackConnection(connection, e);
	} finally {
	    closeConnection(connection);
	}
    }

    /**
     * 
     * @return Returns true if an access to the email server database should be
     *         performed. It returns false otherwise. This method returns the
     *         property set in the build configuration file, where one specifies
     *         if this should be enabled or not.
     */
    private static boolean checkEmailServer() {
	return CHECK_EMAIL_SERVER == null || "true".equals(CHECK_EMAIL_SERVER);
    }
}