/*
 *  Copyright 2016 Anyware Services
 *
 *  Licensed 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.ametys.plugins.core.impl.user.directory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.avalon.framework.component.Component;
import org.apache.avalon.framework.context.Context;
import org.apache.avalon.framework.context.ContextException;
import org.apache.avalon.framework.context.Contextualizable;
import org.apache.avalon.framework.service.ServiceException;
import org.apache.avalon.framework.service.ServiceManager;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.excalibur.source.SourceResolver;

import org.ametys.core.ObservationConstants;
import org.ametys.core.cache.Cache;
import org.ametys.core.datasource.ConnectionHelper;
import org.ametys.core.migration.MigrationExtensionPoint;
import org.ametys.core.migration.storage.VersionStorageExtensionPoint;
import org.ametys.core.migration.storage.impl.SqlVersionStorage;
import org.ametys.core.observation.Event;
import org.ametys.core.observation.ObservationManager;
import org.ametys.core.script.SQLScriptHelper;
import org.ametys.core.user.CurrentUserProvider;
import org.ametys.core.user.InvalidModificationException;
import org.ametys.core.user.User;
import org.ametys.core.user.UserIdentity;
import org.ametys.core.user.directory.ModifiableUserDirectory;
import org.ametys.core.user.directory.NotUniqueUserException;
import org.ametys.core.util.mail.SendMailHelper;
import org.ametys.plugins.core.jdbc.JdbcParameterTypeExtensionPoint;
import org.ametys.runtime.i18n.I18nizableText;
import org.ametys.runtime.model.ElementDefinition;
import org.ametys.runtime.model.ModelHelper;
import org.ametys.runtime.model.ModelItem;
import org.ametys.runtime.model.View;
import org.ametys.runtime.model.type.ModelItemType;
import org.ametys.runtime.model.type.ModelItemTypeConstants;
import org.ametys.runtime.parameter.DefaultValidator;
import org.ametys.runtime.parameter.Errors;
import org.ametys.runtime.parameter.Validator;
import org.ametys.runtime.plugin.PluginsManager;

/**
 * Use a jdbc driver for getting the list of users, modifying them and also
 * authenticate them.<br>
 * Passwords need to be encrypted with MD5 and encoded in base64.<br>
 */
public class JdbcUserDirectory extends AbstractCachingUserDirectory implements ModifiableUserDirectory, Component, Contextualizable
{
    /** The base plugin (for i18n key) */
    protected static final String BASE_PLUGIN_NAME = "core";
    
    static final String[] __COLUMNS = new String[] {"login", "password", "firstname", "lastname", "email"};
    static final String[] __ORDERBY_COLUMNS = new String[] {"lastname", "firstname"};
    
    /** Name of the parameter holding the datasource id */
    private static final String __DATASOURCE_PARAM_NAME = "runtime.users.jdbc.datasource";
    /** Name of the parameter holding the table users' name */
    private static final String __USERS_TABLE_PARAM_NAME = "runtime.users.jdbc.table";
    
    private static final String __COLUMN_LOGIN = "login";
    private static final String __COLUMN_PASSWORD = "password";
    private static final String __COLUMN_FIRSTNAME = "firstname";
    private static final String __COLUMN_LASTNAME = "lastname";
    private static final String __COLUMN_EMAIL = "email";
    private static final String __COLUMN_SALT = "salt";
    
    /** The identifier of data source */
    protected String _dataSourceId;
    /** The name of users' SQL table */
    protected String _userTableName;
    
    /** Model */
    protected Map<String, ElementDefinition> _model;
    
    /** The avalon service manager */
    protected ServiceManager _manager;
    
    /** The avalon context */
    protected Context _context;
    
    /** The cocoon source resolver */
    protected SourceResolver _sourceResolver;

    private ObservationManager _observationManager;
    private CurrentUserProvider _currentUserProvider;
    
    private boolean _lazyInitialized;

    private JdbcParameterTypeExtensionPoint _jdbcParameterTypeExtensionPoint;

    private View _view;
    
    private MigrationExtensionPoint _migrationEP;
    
    private SqlVersionStorage _sqlVersionStorage;
    
    @Override
    public void contextualize(Context context) throws ContextException
    {
        _context = context;
    }
    
    @Override
    public void service(ServiceManager manager) throws ServiceException
    {
        super.service(manager);
        
        _manager = manager;
        _sourceResolver = (SourceResolver) manager.lookup(SourceResolver.ROLE);
        _jdbcParameterTypeExtensionPoint = (JdbcParameterTypeExtensionPoint) manager.lookup(JdbcParameterTypeExtensionPoint.ROLE);
        _migrationEP = (MigrationExtensionPoint) manager.lookup(MigrationExtensionPoint.ROLE);
        VersionStorageExtensionPoint versionStorageEP = (VersionStorageExtensionPoint) manager.lookup(VersionStorageExtensionPoint.ROLE);
        _sqlVersionStorage = (SqlVersionStorage) versionStorageEP.getExtension("sql");
    }
    
    public String getFamilyId()
    {
        return JdbcUserDirectory.class.getName();
    }
    
    @Override
    protected String getCacheTypeLabel()
    {
        return "SQL";
    }
    
    @Override
    public void init(String id, String udModelId, Map<String, Object> paramValues, String label)
    {
        super.init(id, udModelId, paramValues, label);
        
        _userTableName = (String) paramValues.get(__USERS_TABLE_PARAM_NAME);
        _dataSourceId = (String) paramValues.get(__DATASOURCE_PARAM_NAME);
        
        _initModelParameters();
        
        createCaches();
    }
    
    private void _initModelParameters()
    {
        _model = new LinkedHashMap<>();
        
        I18nizableText invalidLoginText = new I18nizableText("plugin." + BASE_PLUGIN_NAME, "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_INVALID");
        Validator loginValidator = new DefaultValidator("^[a-zA-Z0-9_\\-\\.@]{3,64}$", invalidLoginText, true);
        _initModelParameter(__COLUMN_LOGIN, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_DESCRIPTION", loginValidator);

        _initModelParameter(__COLUMN_PASSWORD, ModelItemTypeConstants.PASSWORD_ELEMENT_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_PASSWORD_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_PASSWORD_DESCRIPTION", null);
        
        _initModelParameter(__COLUMN_FIRSTNAME, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_FIRSTNAME_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_FIRSTNAME_DESCRIPTION", null);
        
        _initModelParameter(__COLUMN_LASTNAME, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_LASTNAME_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_LASTNAME_DESCRIPTION", null);

        I18nizableText invalidEmailText = new I18nizableText("plugin." + BASE_PLUGIN_NAME, "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_INVALID");
        Validator emailValidator = new DefaultValidator(SendMailHelper.EMAIL_VALIDATION_REGEXP, invalidEmailText, false);
        _initModelParameter(__COLUMN_EMAIL, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_DESCRIPTION", emailValidator);
    }
    
    private void _initModelParameter(String name, String parameterType, String labelKey, String descriptionKey, Validator validator)
    {
        ModelItemType modelItemType = _jdbcParameterTypeExtensionPoint.getExtension(parameterType);

        ElementDefinition parameter = new ElementDefinition<>();
        parameter.setPluginName(BASE_PLUGIN_NAME);
        parameter.setType(modelItemType);
        parameter.setName(name);
        parameter.setLabel(new I18nizableText("plugin." + BASE_PLUGIN_NAME, labelKey));
        parameter.setDescription(new I18nizableText("plugin." + BASE_PLUGIN_NAME, descriptionKey));
        parameter.setValidator(validator != null ? validator : new DefaultValidator(null, true));
        
        _model.put(name, parameter);
    }
    
    /**
     * Lazy lookup the {@link ObservationManager}
     * @return the observation manager
     */
    protected ObservationManager getObservationManager()
    {
        if (_observationManager == null)
        {
            try
            {
                _observationManager = (ObservationManager) _manager.lookup(ObservationManager.ROLE);
            }
            catch (ServiceException e)
            {
                // We may be in safe mode
                if (PluginsManager.getInstance().isSafeMode())
                {
                    getLogger().debug("Unable to lookup ObservationManager component in safe mode", e);
                }
                else
                {
                    getLogger().error("Unable to lookup ObservationManager component", e);
                }
            }
        }
        return _observationManager;
    }
    
    /**
     * Lazy lookup the {@link CurrentUserProvider}
     * @return the current user provider
     */
    protected CurrentUserProvider getCurrentUserProvider()
    {
        if (_currentUserProvider == null)
        {
            try
            {
                _currentUserProvider = (CurrentUserProvider) _manager.lookup(CurrentUserProvider.ROLE);
            }
            catch (ServiceException e)
            {
                throw new RuntimeException("Unable to lookup CurrentUserProvider component", e);
            }
        }
        return _currentUserProvider;
    }
    
    /**
     * Get the connection to the database 
     * @return the SQL connection
     */
    @SuppressWarnings("unchecked")
    protected Connection getSQLConnection()
    {
        Connection connection = ConnectionHelper.getConnection(_dataSourceId);
        
        if (!_lazyInitialized)
        {
            try
            {
                String componentId = "user-directory.jdbc";
                String versionId = "org.ametys.plugins.core.user.directory.Jdbc.upgrade_" + _userTableName;
                SQLScriptHelper.createTableIfNotExists(_dataSourceId, _userTableName, "plugin:core://scripts/%s/jdbc_users.template.sql", _sourceResolver, 
                        (Map) ArrayUtils.toMap(new String[][] {{"%TABLENAME%", _userTableName}}), componentId, versionId, _migrationEP, _sqlVersionStorage);
            }
            catch (Exception e)
            {
                getLogger().error("The tables requires by the " + this.getClass().getName() + " could not be created. A degraded behavior will occur", e);
            }
            
            _lazyInitialized = true;
        }
        
        return connection;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public Collection<User> getUsers()
    {
        return getUsers(Integer.MAX_VALUE, 0, Collections.EMPTY_MAP);
    }
    
    @Override
    public List<User> getUsers(int count, int offset, Map<String, Object> parameters)
    {
        String pattern = StringUtils.defaultIfEmpty((String) parameters.get("pattern"), null);
        int boundedCount = count >= 0 ? count : Integer.MAX_VALUE;
        int boundedOffset = offset >= 0 ? offset : 0;
        
        SelectUsersJdbcQueryExecutor<List<User>> queryExecutor = new SelectUsersJdbcQueryExecutor<>(pattern, boundedCount, boundedOffset) 
        {
            @Override
            protected List<User> processResultSet(ResultSet rs) throws SQLException
            {
                return _getUsersProcessResultSet(rs, true);
            }
        };
        
        return queryExecutor.run();
    }

    @Override
    public User getUser(String login)
    {
        if (isCachingEnabled() && getCacheByLogin().hasKey(login))
        {
            User user = getCacheByLogin().get(login);
            return user;
        }
        
        SelectUserJdbcQueryExecutor<User> queryExecutor = new SelectUserJdbcQueryExecutor<>(login)
        {
            @Override
            protected User processResultSet(ResultSet rs) throws SQLException
            {
                Cache<String, User> cache = isCachingEnabled() ? getCacheByLogin() : null;
                return _getUserProcessResultSet(rs, login, cache);
            }
        };
        
        return queryExecutor.run();
    }
    
    @Override
    public User getUserByEmail(String email) throws NotUniqueUserException
    {
        if (StringUtils.isBlank(email))
        {
            return null;
        }
        
        String lcEmail = email.toLowerCase();
        
        if (isCachingEnabled() && getCacheByMail().hasKey(lcEmail))
        {
            User user = getCacheByMail().get(lcEmail);
            return user;
        }
        
        SelectUserJdbcQueryExecutor<List<User>> queryExecutor = new SelectUserJdbcQueryExecutor<>(email, __COLUMN_EMAIL)
        {
            @Override
            protected List<User> processResultSet(ResultSet rs) throws SQLException
            {
                return _getUsersProcessResultSet(rs, false);
            }
        };
        
        List<User> users = queryExecutor.run();
        if (users.size() == 1)
        {
            return users.get(0);
        }
        else if (users.isEmpty())
        {
            return null;
        }
        else
        {
            throw new NotUniqueUserException("Find " + users.size() + " users matching the email " + email);
        }
    }
    
    @Override
    public boolean checkCredentials(String login, String password)
    {
        boolean updateNeeded = false;
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            // Connect to the database with connection pool
            con = getSQLConnection();

            // Build request for authenticating the user
            String sql = "SELECT " + __COLUMN_LOGIN + ", " + __COLUMN_PASSWORD + ", " + __COLUMN_SALT + " FROM " + _userTableName + " WHERE " + __COLUMN_LOGIN + " = ?";
            if (getLogger().isDebugEnabled())
            {
                getLogger().debug(sql);
            }

            stmt = con.prepareStatement(sql);
            stmt.setString(1, login);

            // Do the request
            rs = stmt.executeQuery();

            if (rs.next()) 
            {
                String storedPassword = rs.getString(__COLUMN_PASSWORD);
                String salt = rs.getString(__COLUMN_SALT);
                
                if (salt == null && _isMD5Encrypted(storedPassword))
                {
                    String encryptedPassword = org.ametys.core.util.StringUtils.md5Base64(password);
                    
                    if (encryptedPassword == null)
                    {
                        getLogger().error("Unable to encrypt password");
                        return false;
                    }
                    
                    if (storedPassword.equals(encryptedPassword))
                    {
                        updateNeeded = true;
                        return true;
                    }
                    
                    return false;
                }
                else
                {
                    String encryptedPassword = DigestUtils.sha512Hex(salt + password);
                    
                    if (encryptedPassword == null)
                    {
                        getLogger().error("Unable to encrypt password");
                        return false;
                    }
                    
                    return storedPassword.equalsIgnoreCase(encryptedPassword);
                }
            }
            
            return false;
        }
        catch (SQLException e)
        {
            getLogger().error("Error during the connection to the database", e);
            return false;
        }
        finally
        {
            // Close connections
            ConnectionHelper.cleanup(rs);
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
            
            if (updateNeeded)
            {
                _updateToSSHAPassword(login, password);
            }
        }
    }

    @Override
    public void add(Map<String, String> userInformation) throws InvalidModificationException
    {
        Connection con = null;
        PreparedStatement stmt = null;

        if (getLogger().isDebugEnabled())
        {
            getLogger().debug("Starting adding a new user");
        }
        
        // Check the presence of all parameters
        Map<String, Errors> errorFields = validate(userInformation);
        
        if (errorFields.size() > 0)
        {
            throw new InvalidModificationException("The creation of user failed because of invalid parameter values", errorFields);
        }
        
        String login = userInformation.get("login");

        try
        {
            // Connect to the database with connection pool
            con = getSQLConnection();

            stmt = createAddStatement(con, userInformation);

            // Do the request and check the result
            if (stmt.executeUpdate() != 1)
            {
                if (getLogger().isWarnEnabled())
                {
                    getLogger().warn("The user to remove '" + login + "' was not removed.");
                }
                throw new InvalidModificationException("Error no user inserted");
            }

            if (getObservationManager() != null)
            {
                // Observation manager can be null in safe mode
                Map<String, Object> eventParams = new HashMap<>();
                eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, getPopulationId()));
                getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_ADDED, getCurrentUserProvider().getUser(), eventParams));
            }
        }
        catch (SQLException e)
        {
            getLogger().error("Error communication with database", e);
            throw new InvalidModificationException("Error during the communication with the database", e);
        }
        finally
        {
            // Close connections
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
        }
        
    }
    
    @Override
    public Map<String, Errors> validate(Map<String, String> userInformation)
    {
        Map<String, Errors> errorFields = new HashMap<>();
        for (ElementDefinition parameter : _model.values())
        {
            Object typedValue = parameter.getType().castValue(userInformation.get(parameter.getName()));
            Errors errors = new Errors();
            
            List<I18nizableText> errorsList = ModelHelper.validateValue(parameter, typedValue);
            for (I18nizableText error : errorsList)
            {
                errors.addError(error);
            }
            
            if (errors.hasErrors())
            {
                if (getLogger().isDebugEnabled())
                {
                    getLogger().debug("The field '" + parameter.getName() + "' is not valid");
                }
                errorFields.put(parameter.getName(), errors);
            }
        }
        return errorFields;
    }

    @Override
    public void update(Map<String, String> userInformation) throws InvalidModificationException
    {
        Connection con = null;
        PreparedStatement stmt = null;
        
        Map<String, Errors> errorFields = new HashMap<>();
        for (String id : userInformation.keySet())
        {
            ElementDefinition parameter = _model.get(id);
            if (parameter != null)
            {
                Object typedValue = parameter.getType().castValue(userInformation.get(parameter.getName()));
                List<I18nizableText> errorsList = ModelHelper.validateValue(parameter, typedValue);
                Errors errors = new Errors();
                for (I18nizableText error : errorsList)
                {
                    errors.addError(error);
                }
                
                if (errors.hasErrors())
                {
                    if (getLogger().isDebugEnabled())
                    {
                        getLogger().debug("The field '" + parameter.getName() + "' is not valid");
                    }
                    errorFields.put(parameter.getName(), errors);
                }
            }
        }
        
        if (errorFields.size() > 0)
        {
            throw new InvalidModificationException("The modification of user failed because of invalid parameter values", errorFields);
        }

        String login = userInformation.get("login");
        if (StringUtils.isEmpty(login))
        {
            throw new InvalidModificationException("Cannot update without login information");
        }

        try
        {
            // Connect to the database with connection pool
            con = getSQLConnection();

            stmt = createModifyStatement(con, userInformation);

            // Do the request
            if (stmt.executeUpdate() != 1)
            {
                throw new InvalidModificationException("Error. User '" + login + "' not updated");
            }

            if (getObservationManager() != null)
            {
                // Observation manager can be null in safe mode
                Map<String, Object> eventParams = new HashMap<>();
                eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, getPopulationId()));
                getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_UPDATED, getCurrentUserProvider().getUser(), eventParams));
            }
            
            if (isCachingEnabled())
            {
                getCacheByLogin().invalidate(login);
                getCacheByMail().invalidateAll();
            }
        }
        catch (SQLException e)
        {
            getLogger().error("Error communication with database", e);
            throw new InvalidModificationException("Error communication with database", e);
        }
        finally
        {
            // Close connections
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
        }
    }

    @Override
    public void remove(String login) throws InvalidModificationException
    {
        Connection con = null;
        PreparedStatement stmt = null;

        try
        {
            // Connect to the database with connection pool
            con = getSQLConnection();

            // Build request for removing the user
            String sqlRequest = "DELETE FROM " + _userTableName + " WHERE " + __COLUMN_LOGIN + " = ?";
            if (getLogger().isDebugEnabled())
            {
                getLogger().debug(sqlRequest);
            }

            stmt = con.prepareStatement(sqlRequest);
            stmt.setString(1, login);

            // Do the request and check the result
            if (stmt.executeUpdate() != 1)
            {
                throw new InvalidModificationException("Error user was not deleted");
            }

            if (getObservationManager() != null)
            {
                // Observation manager can be null in safe mode
                Map<String, Object> eventParams = new HashMap<>();
                eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, getPopulationId()));
                getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_DELETED, getCurrentUserProvider().getUser(), eventParams));
            }
            
            if (isCachingEnabled())
            {
                getCacheByLogin().invalidate(login);
                getCacheByMail().invalidateAll();
            }
        }
        catch (SQLException e)
        {
            throw new InvalidModificationException("Error during the communication with the database", e);
        }
        finally
        {
            // Close connections
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
        }
    }
    
    public Collection< ? extends ModelItem> getModelItems()
    {
        return Collections.unmodifiableCollection(_model.values());
    }
    
    /**
     * Get the mandatory predicate to use when querying users by pattern.
     * @param pattern The pattern to match, can be null.
     * @return a {@link JdbcPredicate}, can be null.
     */
    protected JdbcPredicate _getMandatoryPredicate(String pattern)
    {
        return null;
    }
    
    /**
     * Get the pattern to match user login
     * @param pattern the pattern
     * @return the pattern to match user login
     */
    protected String _getPatternToMatch(String pattern)
    {
        if (pattern != null)
        {
            return "%" + pattern + "%";
        }
        return null;
    }
    
    /**
     * Determines if the password is encrypted with MD5 algorithm
     * @param password The encrypted password
     * @return true if the password is encrypted with MD5 algorithm
     */
    protected boolean _isMD5Encrypted(String password)
    {
        return password.length() == 24;
    }
    
    /**
     * Generate a salt key and encrypt the password with the sha2 algorithm
     * @param login The user login
     * @param password The user pasword
     */
    protected void _updateToSSHAPassword(String login, String password)
    {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try
        {
            con = getSQLConnection();

            String generateSaltKey = RandomStringUtils.randomAlphanumeric(48);
            String newEncryptedPassword = DigestUtils.sha512Hex(generateSaltKey + password);

            String sqlUpdate = "UPDATE " + _userTableName + " SET " + __COLUMN_PASSWORD + " = ?, " + __COLUMN_SALT + " = ? WHERE " + __COLUMN_LOGIN + " = ?";
            if (getLogger().isDebugEnabled())
            {
                getLogger().debug(sqlUpdate);
            }

            stmt = con.prepareStatement(sqlUpdate);
            stmt.setString(1, newEncryptedPassword);
            stmt.setString(2, generateSaltKey);
            stmt.setString(3, login);

            stmt.execute();
        }
        catch (SQLException e)
        {
            getLogger().error("Error during the connection to the database", e);
        }
        finally
        {
            // Close connections
            ConnectionHelper.cleanup(rs);
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
        }
    }
     
     /**
      * Create Add statement
      * @param con The sql connection
      * @param userInformation the user informations
      * @return The statement
      * @throws SQLException if an error occurred
      */
    protected PreparedStatement createAddStatement(Connection con, Map<String, String> userInformation) throws SQLException
    {
        String beginClause = "INSERT INTO " + _userTableName + " (";
        String middleClause = ") VALUES (";
        String endClause = ")";

        StringBuffer intoClause = new StringBuffer();
        StringBuffer valueClause = new StringBuffer();

        intoClause.append(__COLUMN_SALT);
        valueClause.append("?");

        for (String column : __COLUMNS)
        {
            intoClause.append(", " + column);
            valueClause.append(", ?");
        }

        String sqlRequest = beginClause + intoClause.toString() + middleClause + valueClause + endClause;
        if (getLogger().isDebugEnabled())
        {
            getLogger().debug(sqlRequest);
        }

        PreparedStatement stmt = con.prepareStatement(sqlRequest);

        int i = 1;
        boolean clearText = !userInformation.containsKey("clearText") || !"false".equals(userInformation.get("clearText")) || !userInformation.containsKey(__COLUMN_SALT);
        String generatedSaltKey = clearText ? RandomStringUtils.randomAlphanumeric(48) : userInformation.get(__COLUMN_SALT);

        stmt.setString(i++, generatedSaltKey);

        for (String column : __COLUMNS)
        {
            if ("password".equals(column))
            {
                String encryptedPassword;
                if (clearText)
                {
                    encryptedPassword = DigestUtils.sha512Hex(generatedSaltKey + userInformation.get(column));
                    if (encryptedPassword == null)
                    {
                        String message = "Cannot encode password";
                        getLogger().error(message);
                        throw new SQLException(message);
                    }
                }
                else
                {
                    encryptedPassword = userInformation.get(column);
                }
                stmt.setString(i++, encryptedPassword);
            }
            else
            {
                stmt.setString(i++, userInformation.get(column));
            }
        }

        return stmt;
    }
     
     /**
      * Create statement to update database
      * @param con The sql connection
      * @param userInformation The user information
      * @return The statement
      * @throws SQLException if an error occurred
      */
    protected PreparedStatement createModifyStatement(Connection con, Map<String, String> userInformation) throws SQLException
    {
        // Build request for editing the user
        String beginClause = "UPDATE " + _userTableName + " SET ";
        String endClause = " WHERE " + __COLUMN_LOGIN + " = ?";

        StringBuffer columnNames = new StringBuffer("");

        boolean passwordUpdate = false;
        for (String id : userInformation.keySet())
        {
            if (ArrayUtils.contains(__COLUMNS, id) && !"login".equals(id) && !("password".equals(id) && userInformation.get(id) == null))
            {
                if ("password".equals(id))
                {
                    passwordUpdate = true;
                }

                if (columnNames.length() > 0)
                {
                    columnNames.append(", ");
                }
                columnNames.append(id + " = ?");
            }
        }

        if (passwordUpdate)
        {
            columnNames.append(", " + __COLUMN_SALT + " = ?");
        }

        String sqlRequest = beginClause + columnNames.toString() + endClause;
        if (getLogger().isDebugEnabled())
        {
            getLogger().debug(sqlRequest);
        }

        PreparedStatement stmt = con.prepareStatement(sqlRequest);
        _fillModifyStatement(stmt, userInformation);

        return stmt;
    }
     
     /**
      * Fill the statement with the user informations
      * @param stmt The statement of the sql request
      * @param userInformation the user informations
      * @throws SQLException if an error occurred
      */
    protected void _fillModifyStatement(PreparedStatement stmt, Map<String, String> userInformation) throws SQLException
    {
        int index = 1;

        boolean clearText = !userInformation.containsKey("clearText") || !"false".equals(userInformation.get("clearText")) || !userInformation.containsKey(__COLUMN_SALT);
        String generatedSaltKey = clearText ? RandomStringUtils.randomAlphanumeric(48) : userInformation.get(__COLUMN_SALT);

        boolean passwordUpdate = false;

        for (String id : userInformation.keySet())
        {
            if (ArrayUtils.contains(__COLUMNS, id) && !"login".equals(id))
            {
                if ("password".equals(id))
                {
                    if (userInformation.get(id) != null)
                    {
                        String encryptedPassword;
                        if (clearText)
                        {
                            encryptedPassword = DigestUtils.sha512Hex(generatedSaltKey + userInformation.get(id));
                            if (encryptedPassword == null)
                            {
                                String message = "Cannot encrypt password";
                                getLogger().error(message);
                                throw new SQLException(message);
                            }
                        }
                        else
                        {
                            encryptedPassword = userInformation.get(id);
                        }
                        stmt.setString(index++, encryptedPassword);
                        passwordUpdate = true;
                    }
                }
                else
                {
                    stmt.setString(index++, userInformation.get(id));
                }
            }
        }

        if (passwordUpdate)
        {
            stmt.setString(index++, generatedSaltKey);
        }

        stmt.setString(index++, userInformation.get("login"));
    }
     
     /**
      * Populate the user list with the result set
      * @param rs The result set
      * @param isLogin true for login, false for email
      * @return The user list
      * @throws SQLException If an SQL exception occurs
      */
    protected List<User> _getUsersProcessResultSet(ResultSet rs, boolean isLogin) throws SQLException
    {
        List<User> users = new ArrayList<>();

        while (rs.next())
        {
            User user = null;

            // Try to get in cache
            Cache<String, User> cache = null;
            if (isCachingEnabled())
            {
                if (isLogin)
                {
                    cache = getCacheByLogin();
                    String login = rs.getString(__COLUMN_LOGIN);
                    user = cache.hasKey(login) ? cache.get(login) : null;
                }
                else
                {
                    cache = getCacheByMail();
                    String email = rs.getString(__COLUMN_EMAIL).toLowerCase();
                    user = cache.hasKey(email) ? cache.get(email) : null;
                }
            }

            // Or create from result set
            if (user == null)
            {
                user = _createUserFromResultSet(rs);

                if (cache != null)
                {
                    if (isLogin)
                    {
                        cache.put(user.getIdentity().getLogin(), user);
                    }
                    else
                    {
                        cache.put(user.getEmail().toLowerCase(), user); // Email should not be empty here, since user was searched by email
                    }
                }
            }

            users.add(user);
        }

        return users;
    }
     
     /**
      * Create the user implementation from the result set of the request
      * 
      * @param rs The result set where you can use get methods
      * @return The user reflecting the current cursor position in the result set
      * @throws SQLException if an error occurred
      */
    protected User _createUserFromResultSet(ResultSet rs) throws SQLException
    {
        String login = rs.getString(__COLUMN_LOGIN);
        String lastName = rs.getString(__COLUMN_LASTNAME);
        String firstName = rs.getString(__COLUMN_FIRSTNAME);
        String email = rs.getString(__COLUMN_EMAIL);

        return new User(new UserIdentity(login, getPopulationId()), lastName, firstName, email, this);
    }
     
     /**
      * Retrieve an user from a result set
      * @param rs The result set
      * @param login The user login
      * @param cache the cache to use. Is null if caching is not enabled
      * @return The retrieved user or null if not found
      * @throws SQLException If an SQL Exception occurs
      */
    protected User _getUserProcessResultSet(ResultSet rs, String login, Cache<String, User> cache) throws SQLException
    {
        if (rs.next())
        {
            // Retrieve information of the user
            User user = _createUserFromResultSet(rs);

            if (isCachingEnabled())
            {
                cache.put(login, user);
            }

            return user;
        }
        else
        {
            // no user with this login in the database
            return null;
        }
    }
    
    @Override
    public View getView()
    {
        if (_view == null)
        {
            _view = View.of(this, __COLUMNS);
        }
        return _view;
    }
     
     // ------------------------------
     //          INNER CLASSE
     // ------------------------------
     /**
      * An internal query executor.
      * @param <T> The type of the queried object
      */
    protected abstract class AbstractJdbcQueryExecutor<T>
    {
        /**
         * Main function, run the query process. Will not throw exception. Use
         * runWithException to catch non SQL exception thrown by
         * {@link #processResultSet(ResultSet)}
         * @return The queried object or null
         */
        @SuppressWarnings("synthetic-access")
        public T run()
        {
            try
            {
                return runWithException();
            }
            catch (Exception e)
            {
                getLogger().error("Exception during a query execution", e);
                throw new RuntimeException("Exception during a query execution", e);
            }
        }

        /**
         * Main function, run the query process.
         * @return The queried object or null
         * @throws Exception All non SQLException will be thrown
         */
        @SuppressWarnings("synthetic-access")
        public T runWithException() throws Exception
        {
            Connection connection = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;

            try
            {
                connection = getSQLConnection();
                
                String sql = getSqlQuery(connection);

                if (getLogger().isDebugEnabled())
                {
                    getLogger().debug("Executing SQL query: " + sql);
                }

                stmt = prepareStatement(connection, sql);
                rs = executeQuery(stmt);

                return processResultSet(rs);
            }
            catch (SQLException e)
            {
                getLogger().error("Error during the communication with the database", e);
                throw new RuntimeException("Error during the communication with the database", e);
            }
            finally
            {
                ConnectionHelper.cleanup(rs);
                ConnectionHelper.cleanup(stmt);
                ConnectionHelper.cleanup(connection);
            }
        }

        /**
         * Must return the SQL query to execute
         * @param connection The pool connection
         * @return The SQL query
         */
        protected abstract String getSqlQuery(Connection connection);

        /**
         * Prepare the statement to execute
         * @param connection The pool connection
         * @param sql The SQL query
         * @return The prepared statement, ready to be executed
         * @throws SQLException If an SQL Exception occurs
         */
        protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException
        {
            return connection.prepareStatement(sql);
        }

        /**
         * Execute the prepared statement and retrieves the result set.
         * @param stmt The prepared statement
         * @return The result set
         * @throws SQLException If an SQL Exception occurs 
         */
        protected ResultSet executeQuery(PreparedStatement stmt) throws SQLException
        {
            return stmt.executeQuery();
        }

        /**
         * Process the result set
         * @param rs The result set
         * @return The queried object or null
         * @throws SQLException If an SQL exception occurs
         * @throws Exception Other exception will be thrown when using {@link #runWithException()}
         */
        protected T processResultSet(ResultSet rs) throws SQLException, Exception
        {
            return null;
        }
    }

    /**
     * Query executor in order to select an user
     * @param <T> The type of the queried object
     */
    protected class SelectUserJdbcQueryExecutor<T> extends AbstractJdbcQueryExecutor<T>
    {
        /** The user login */
        protected String _value;
        /** The search column */
        protected String _searchColumn;
        
        /** 
         * The constructor
         * @param value The strict value to search for
         */
        protected SelectUserJdbcQueryExecutor(String value)
        {
            _value = value;
            _searchColumn = __COLUMN_LOGIN;
        }
        
        /** 
         * The constructor
         * @param value The strict value to search for
         * @param searchColumn The name of search column
         */
        protected SelectUserJdbcQueryExecutor(String value, String searchColumn)
        {
            _value = value;
            _searchColumn = searchColumn;
        }

        @Override
        protected String getSqlQuery(Connection connection)
        {
            // Build SQL request
            StringBuilder selectClause = new StringBuilder();
            for (String id : __COLUMNS)
            {
                if (selectClause.length() > 0)
                {
                    selectClause.append(", ");
                }
                selectClause.append(id);
            }

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(selectClause).append(" FROM ").append(_userTableName);
            sql.append(" WHERE ").append(_searchColumn).append(" = ?");

            return sql.toString();
        }

        @Override
        protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException
        {
            PreparedStatement stmt = super.prepareStatement(connection, sql);

            stmt.setString(1, _value);
            return stmt;
        }
    }
     
    /**
     * Query executor in order to select users
     * @param <T> The type of the queried object
     */
    protected class SelectUsersJdbcQueryExecutor<T> extends AbstractJdbcQueryExecutor<T>
    {
        /** The pattern to match (none if null) */
        protected String _pattern;
        /** The maximum number of users to select */
        protected int _length;
        /** The offset to start with, first is 0 */
        protected int _offset;

        /** The mandatory predicate to use when querying users by pattern */
        protected JdbcPredicate _mandatoryPredicate;
        /** The pattern to match, extracted from the pattern */
        protected String _patternToMatch;

        /** 
         * The constructor
         * @param pattern The pattern to match (none if null).
         * @param length The maximum number of users to select.
         * @param offset The offset to start with, first is 0.
         */
        protected SelectUsersJdbcQueryExecutor(String pattern, int length, int offset)
        {
            _pattern = pattern;
            _length = length;
            _offset = offset;
        }

        @Override
        protected String getSqlQuery(Connection connection)
        {
            // Build SQL request
            StringBuilder selectClause = new StringBuilder();
            for (String column : __COLUMNS)
            {
                if (selectClause.length() > 0)
                {
                    selectClause.append(", ");
                }
                selectClause.append(column);
            }

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(selectClause).append(" FROM ").append(_userTableName);

            // Add the pattern
            _mandatoryPredicate = _getMandatoryPredicate(_pattern);
            if (_mandatoryPredicate != null)
            {
                sql.append(" WHERE ").append(_mandatoryPredicate.getPredicate());
            }

            _patternToMatch = _getPatternToMatch(_pattern);
            if (_patternToMatch != null)
            {
                if (ConnectionHelper.DATABASE_DERBY.equals(ConnectionHelper.getDatabaseType(connection)))
                {
                    // The LIKE operator in Derby is case sensitive
                    sql.append(_mandatoryPredicate != null ? " AND (" : " WHERE ")
                    .append("UPPER(").append(__COLUMN_LOGIN).append(") LIKE UPPER(?) OR ")
                    .append("UPPER(").append(__COLUMN_LASTNAME).append(") LIKE UPPER(?) OR ")
                    .append("UPPER(").append(__COLUMN_FIRSTNAME).append(") LIKE UPPER(?)");
                }
                else
                {
                    sql.append(_mandatoryPredicate != null ? " AND (" : " WHERE ")
                    .append(__COLUMN_LOGIN).append(" LIKE ? OR ")
                    .append(__COLUMN_LASTNAME).append(" LIKE ? OR ")
                    .append(__COLUMN_FIRSTNAME).append(" LIKE ?");
                }

                if (_mandatoryPredicate != null)
                {
                    sql.append(')');
                }
            }
            
            StringBuilder orderByClause = new StringBuilder();
            for (String column : __ORDERBY_COLUMNS)
            {
                orderByClause.append(orderByClause.length() == 0 ? " ORDER BY " : ", ");
                orderByClause.append(column);
            }
            
            sql.append(orderByClause);

            // Add length filters
            sql = _addQuerySize(_length, _offset, connection, selectClause, sql);

            return sql.toString();
        }

        @SuppressWarnings("synthetic-access")
        private StringBuilder _addQuerySize(int length, int offset, Connection con, StringBuilder selectClause, StringBuilder sql)
        {
            // Do not add anything if not necessary
            if (length == Integer.MAX_VALUE && offset == 0)
            {
                return sql;
            }

            String dbType = ConnectionHelper.getDatabaseType(con);

            if (ConnectionHelper.DATABASE_MYSQL.equals(dbType) || ConnectionHelper.DATABASE_POSTGRES.equals(dbType) || ConnectionHelper.DATABASE_HSQLDB.equals(dbType))
            {
                sql.append(" LIMIT " + length + " OFFSET " + offset);
                return sql;
            }
            else if (ConnectionHelper.DATABASE_ORACLE.equals(dbType))
            {
                return new StringBuilder("select " + selectClause.toString() + " from (select rownum r, " + selectClause.toString() + " from (" + sql.toString()
                        + ")) where r BETWEEN " + (offset + 1) + " AND " + (offset + length));
            }
            else if (ConnectionHelper.DATABASE_DERBY.equals(dbType))
            {
                return new StringBuilder("select ").append(selectClause)
                        .append(" from (select ROW_NUMBER() OVER () AS ROWNUM, ").append(selectClause.toString())
                        .append(" from (").append(sql.toString()).append(") AS TR ) AS TRR where ROWNUM BETWEEN ")
                        .append(offset + 1).append(" AND ").append(offset + length);
            }
            else if (getLogger().isWarnEnabled())
            {
                getLogger().warn("The request will not have the limit and offset set, since its type is unknown");
            }

            return sql;
        }

        @Override
        protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException
        {
            PreparedStatement stmt = super.prepareStatement(connection, sql);

            int i = 1;
            // Value the parameters if there is a pattern
            if (_mandatoryPredicate != null)
            {
                for (String value : _mandatoryPredicate.getValues())
                {
                    stmt.setString(i++, value);
                }
            }

            if (_patternToMatch != null)
            {
                // One for the login, one for the lastname.
                stmt.setString(i++, _patternToMatch);
                stmt.setString(i++, _patternToMatch);
                // FIXME
                //if (_parameters.containsKey("firstname"))
                //{
                stmt.setString(i++, _patternToMatch);
                //}
            }

            return stmt;
        }
    }

    /**
     * Class representing a SQL predicate (to use in a WHERE or HAVING clause),
     * with optional string parameters.
     */
    public class JdbcPredicate
    {

        /** The predicate string with optional "?" placeholders. */
        protected String _predicate;
        /** The predicate parameter values. */
        protected List<String> _predicateParamValues;

        /**
         * Build a JDBC predicate.
         * @param predicate the predicate string.
         * @param values the parameter values.
         */
        public JdbcPredicate(String predicate, String... values)
        {
            this(predicate, Arrays.asList(values));
        }

        /**
         * Build a JDBC predicate.
         * @param predicate the predicate string.
         * @param values the parameter values.
         */
        public JdbcPredicate(String predicate, List<String> values)
        {
            this._predicate = predicate;
            this._predicateParamValues = values;
        }

        /**
         * Get the predicate.
         * @return the predicate
         */
        public String getPredicate()
        {
            return _predicate;
        }

        /**
         * Set the predicate.
         * @param predicate the predicate to set
         */
        public void setPredicate(String predicate)
        {
            this._predicate = predicate;
        }

        /**
         * Get the parameter values.
         * @return the parameter values.
         */
        public List<String> getValues()
        {
            return _predicateParamValues;
        }

        /**
         * Set the parameter values.
         * @param values the parameter values to set.
         */
        public void setValues(List<String> values)
        {
            this._predicateParamValues = values;
        }
    }
}