/*
 * Copyright (C) The Apache Software Foundation. All rights reserved.
 *
 * This software is published under the terms of the Apache Software License
 * version 1.1, a copy of which has been included with this distribution in
 * the LICENSE file.
 */
package org.apache.james.userrepository;

import org.apache.avalon.cornerstone.services.datasource.DataSourceSelector;
import org.apache.avalon.excalibur.datasource.DataSourceComponent;
import org.apache.avalon.framework.CascadingRuntimeException;
import org.apache.avalon.framework.activity.Initializable;
import org.apache.avalon.framework.component.Component;
import org.apache.avalon.framework.component.ComponentException;
import org.apache.avalon.framework.component.ComponentManager;
import org.apache.avalon.framework.component.Composable;
import org.apache.avalon.framework.configuration.Configurable;
import org.apache.avalon.framework.configuration.Configuration;
import org.apache.avalon.framework.configuration.ConfigurationException;
import org.apache.avalon.framework.context.Context;
import org.apache.avalon.framework.context.ContextException;
import org.apache.avalon.framework.context.Contextualizable;
import org.apache.james.context.AvalonContextConstants;
import org.apache.james.services.User;
import org.apache.james.util.JDBCUtil;
import org.apache.james.util.SqlResources;

import java.io.File;
import java.sql.*;
import java.util.*;

/**
 * An abstract base class for creating UserRepository implementation
 * which use a database for persistence.
 * 
 * To implement a new UserRepository using by extending this class,
 * you need to implement the 3 abstract methods defined below,
 * and define the required SQL statements in an SQLResources
 * file.
 * 
 * The SQL statements used by this implementation are:
 * <TABLE>
 * <TH><TD><B>Required</B></TD></TH>
 * <TR><TD>select</TD><TD>Select all users.</TD></TR>
 * <TR><TD>insert</TD><TD>Insert a user.</TD></TR>
 * <TR><TD>update</TD><TD>Update a user.</TD></TR>
 * <TR><TD>delete</TD><TD>Delete a user by name.</TD></TR>
 * <TR><TD>createTable</TD><TD>Create the users table.</TD></TR>
 * <TH><TD><B>Optional</B></TD></TH>
 * <TR><TD>selectByLowercaseName</TD><TD>Select a user by name (case-insensitive lowercase).</TD></TR>
 * </TABLE>
 * 
 * @author Darrell DeBoer <dd@bigdaz.com>
 */
public abstract class AbstractJdbcUsersRepository extends AbstractUsersRepository
    implements Component, Contextualizable, Composable, Configurable, Initializable
{
    protected Context context;
    protected Map m_sqlParameters;
    private String m_sqlFileName;
    private String m_datasourceName;
    private DataSourceSelector m_datasources;
    private DataSourceComponent m_datasource;

    // Fetches all Users from the db.
    private String m_getUsersSql;
    
    // This fetch a user by name, ensuring case-insensitive matching.
    private String m_userByNameCaseInsensitiveSql;

    // Insert, update and delete sql statements are not guaranteed 
    //  to be case-insensitive; this is handled in code.
    private String m_insertUserSql;
    private String m_updateUserSql;
    private String m_deleteUserSql;

    // Creates a single table with "username" the Primary Key.
    private String m_createUserTableSql;

    // The JDBCUtil helper class
    private JDBCUtil theJDBCUtil;

    public void contextualize(final Context context)
            throws ContextException {
        this.context = context;
    }

    /**
     * Compose the repository with the DataSourceSelector component.
     */
    public void compose( final ComponentManager componentManager )
        throws ComponentException
    {
        StringBuffer logBuffer = null;
        if (getLogger().isDebugEnabled())
        {
            logBuffer =
                new StringBuffer(64)
                        .append(this.getClass().getName())
                        .append(".compose()");
            getLogger().debug( logBuffer.toString() );
        }

        m_datasources = 
            (DataSourceSelector)componentManager.lookup( DataSourceSelector.ROLE );
    }

    /**
     * Configures the UserRepository for JDBC access.
     * 
     * Requires a configuration element in the .conf.xml file of the form:
     * 
     *  <repository name="LocalUsers"
     *              class="org.apache.james.userrepository.JamesUsersJdbcRepository">
     *      <!-- Name of the datasource to use -->
     *      <data-source>MailDb</data-source>
     *      <!-- File to load the SQL definitions from -->
     *      <sqlFile>dist/conf/sqlResources.xml</sqlFile>
     *      <!-- replacement parameters for the sql file -->
     *      <sqlParameters table="JamesUsers"/>
     *  </repository>
     */
    public void configure(Configuration configuration) throws ConfigurationException 
    {
        StringBuffer logBuffer = null;
        if (getLogger().isDebugEnabled()) {
            logBuffer =
                new StringBuffer(64)
                        .append(this.getClass().getName())
                        .append(".configure()");
            getLogger().debug( logBuffer.toString() );
        }

        // Parse the DestinationURL for the name of the datasource, 
        // the table to use, and the (optional) repository Key.
        String destUrl = configuration.getAttribute("destinationURL");
        // normalise the destination, to simplify processing.
        if ( ! destUrl.endsWith("/") ) {
            destUrl += "/";
        }
        // Split on "/", starting after "db://"
        List urlParams = new LinkedList();
        int start = 5;
        int end = destUrl.indexOf('/', start);
        while ( end > -1 ) {
            urlParams.add(destUrl.substring(start, end));
            start = end + 1;
            end = destUrl.indexOf('/', start);
        }

        // Build SqlParameters and get datasource name from URL parameters
        m_sqlParameters = new HashMap();
        switch ( urlParams.size() ) {
        case 3:
            m_sqlParameters.put("key", urlParams.get(2));
        case 2:
            m_sqlParameters.put("table", urlParams.get(1));
        case 1:
            m_datasourceName = (String)urlParams.get(0);
            break;
        default:
            throw new ConfigurationException
                ("Malformed destinationURL - " +
                 "Must be of the format \"db://<data-source>[/<table>[/<key>]]\".");
        }

        if (getLogger().isDebugEnabled()) {
            logBuffer =
                new StringBuffer(128)
                        .append("Parsed URL: table = '")
                        .append(m_sqlParameters.get("table"))
                        .append("', key = '")
                        .append(m_sqlParameters.get("key"))
                        .append("'");
            getLogger().debug(logBuffer.toString());
        }
        
        // Get the SQL file location
        m_sqlFileName = configuration.getChild("sqlFile", true).getValue();
        if (!m_sqlFileName.startsWith("file://")) {
            throw new ConfigurationException
                ("Malformed sqlFile - Must be of the format \"file://<filename>\".");
        }

        // Get other sql parameters from the configuration object,
        // if any.
        Configuration sqlParamsConfig = configuration.getChild("sqlParameters");
        String[] paramNames = sqlParamsConfig.getAttributeNames();
        for (int i = 0; i < paramNames.length; i++ ) {
            String paramName = paramNames[i];
            String paramValue = sqlParamsConfig.getAttribute(paramName);
            m_sqlParameters.put(paramName, paramValue);
        }
    }

    /**
     * Initialises the JDBC repository.
     * 1) Tests the connection to the database.
     * 2) Loads SQL strings from the SQL definition file,
     *     choosing the appropriate SQL for this connection, 
     *     and performing paramter substitution,
     * 3) Initialises the database with the required tables, if necessary.
     * 
     */
    public void initialize() throws Exception 
    {
        StringBuffer logBuffer = null;
        if (getLogger().isDebugEnabled()) {
            logBuffer =
                new StringBuffer(128)
                        .append(this.getClass().getName())
                        .append(".initialize()");
            getLogger().debug( logBuffer.toString() );
        }

        theJDBCUtil =
            new JDBCUtil() {
                protected void delegatedLog(String logString) {
                    AbstractJdbcUsersRepository.this.getLogger().warn("AbstractJdbcUsersRepository: " + logString);
                }
            };

        // Get the data-source required.
        m_datasource = (DataSourceComponent)m_datasources.select(m_datasourceName);

        // Test the connection to the database, by getting the DatabaseMetaData.
        Connection conn = openConnection();
        try{
            DatabaseMetaData dbMetaData = conn.getMetaData();

            // Initialise the sql strings.
            String fileName = m_sqlFileName.substring("file://".length());
            if (!(fileName.startsWith("/"))) {
                String baseDirectory = "";
                try {
                    File applicationHome =
                        (File)context.get(AvalonContextConstants.APPLICATION_HOME);
                    baseDirectory = applicationHome.toString();
                } catch (ContextException ce) {
                    getLogger().fatalError("Encountered exception when resolving application home in Avalon context.", ce);
                    throw ce;
                } catch (ClassCastException cce) {
                    getLogger().fatalError("Application home object stored in Avalon context was not of type java.io.File.", cce);
                    throw cce;
                }
                StringBuffer fileNameBuffer =
                    new StringBuffer(128)
                            .append(baseDirectory)
                            .append(File.separator)
                            .append(fileName);
                fileName = fileNameBuffer.toString();
            }
            File sqlFile = (new File(fileName)).getCanonicalFile();
            
            if (getLogger().isDebugEnabled()) {
                logBuffer =
                    new StringBuffer(256)
                            .append("Reading SQL resources from file: ")
                            .append(sqlFile.getAbsolutePath())
                            .append(", section ")
                            .append(this.getClass().getName())
                            .append(".");
                getLogger().debug(logBuffer.toString());
            }

            SqlResources sqlStatements = new SqlResources();
            sqlStatements.init(sqlFile, this.getClass().getName(), 
                               conn, m_sqlParameters);

            // Create the SQL Strings to use for this table.
            // Fetches all Users from the db.
            m_getUsersSql = sqlStatements.getSqlString("select", true);

            // Get a user by lowercase name. (optional)
            // If not provided, the entire list is iterated to find a user.
            m_userByNameCaseInsensitiveSql = 
                sqlStatements.getSqlString("selectByLowercaseName");

            // Insert, update and delete are not guaranteed to be case-insensitive
            // Will always be called with correct case in username..
            m_insertUserSql = sqlStatements.getSqlString("insert", true);
            m_updateUserSql = sqlStatements.getSqlString("update", true);
            m_deleteUserSql = sqlStatements.getSqlString("delete", true);

            // Creates a single table with "username" the Primary Key.
            m_createUserTableSql = sqlStatements.getSqlString("createTable", true);

            // Check if the required table exists. If not, create it.
            // The table name is defined in the SqlResources.
            String tableName = sqlStatements.getSqlString("tableName", true);
            
            // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
            // NB this should work, but some drivers (eg mm MySQL) 
            // don't return the right details, hence the hackery below.
            /*
            String tableName = m_tableName;
            if ( dbMetaData.storesLowerCaseIdentifiers() ) {
                tableName = tableName.toLowerCase(Locale.US);
            }
            else if ( dbMetaData.storesUpperCaseIdentifiers() ) {
                tableName = tableName.toUpperCase(Locale.US);
            }
            */

            // Try UPPER, lower, and MixedCase, to see if the table is there.
            if (! theJDBCUtil.tableExists(dbMetaData, tableName)) 
            {
                // Users table doesn't exist - create it.
                PreparedStatement createStatement = null;
                try {
                    createStatement =
                        conn.prepareStatement(m_createUserTableSql);
                    createStatement.execute();
                } finally {
                    theJDBCUtil.closeJDBCStatement(createStatement);
                }

                logBuffer =
                    new StringBuffer(128)
                            .append(this.getClass().getName())
                            .append(": Created table \'")
                            .append(tableName)
                            .append("\'.");
                getLogger().info(logBuffer.toString());
            }
            else {
                if (getLogger().isDebugEnabled()) {
                    getLogger().debug("Using table: " + tableName);
                }
            }
        
        }
        finally {
            theJDBCUtil.closeJDBCConnection( conn );
        }
    }

    //
    // Superclass methods - overridden in AbstractUsersRepository
    //
    /**
     * Returns a list populated with all of the Users in the repository.
     * @return an <code>Iterator</code> of <code>JamesUser</code>s.
     */
    protected Iterator listAllUsers() {
        List userList = new LinkedList(); // Build the users into this list.

        Connection conn = openConnection();
        PreparedStatement getUsersStatement = null;
        ResultSet rsUsers = null;
        try {
            // Get a ResultSet containing all users.
            getUsersStatement = 
                conn.prepareStatement(m_getUsersSql);
            rsUsers = getUsersStatement.executeQuery();

            // Loop through and build a User for every row.
            while ( rsUsers.next() ) {
                User user = readUserFromResultSet(rsUsers);
                userList.add(user);
            }
        }
        catch ( SQLException sqlExc) {
            sqlExc.printStackTrace();
            throw new CascadingRuntimeException("Error accessing database", sqlExc);
        }
        finally {
            theJDBCUtil.closeJDBCResultSet(rsUsers);
            theJDBCUtil.closeJDBCStatement(getUsersStatement);
            theJDBCUtil.closeJDBCConnection(conn);
        }

        return userList.iterator();
    }

    /**
     * Adds a user to the underlying Repository.
     * The user name must not clash with an existing user.
     */
    protected void doAddUser(User user) {
        Connection conn = openConnection();
        PreparedStatement addUserStatement = null;

        // Insert into the database.
        try {
            // Get a PreparedStatement for the insert.
            addUserStatement = 
                conn.prepareStatement(m_insertUserSql);

            setUserForInsertStatement(user, addUserStatement);

            addUserStatement.execute();
        }
        catch ( SQLException sqlExc) {
            sqlExc.printStackTrace();
            throw new CascadingRuntimeException("Error accessing database", sqlExc);
        } finally {
            theJDBCUtil.closeJDBCStatement(addUserStatement);
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }

    /**
     * Removes a user from the underlying repository.
     * If the user doesn't exist, returns ok.
     */
    protected void doRemoveUser(User user) {
        String username = user.getUserName();

        Connection conn = openConnection();
        PreparedStatement removeUserStatement = null;

        // Delete from the database.
        try {
            removeUserStatement = conn.prepareStatement(m_deleteUserSql);
            removeUserStatement.setString(1, username);
            removeUserStatement.execute();
        }
        catch ( SQLException sqlExc ) {
            sqlExc.printStackTrace();
            throw new CascadingRuntimeException("Error accessing database", sqlExc);
        } finally {
            theJDBCUtil.closeJDBCStatement(removeUserStatement);
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }

    /**
     * Updates a user record to match the supplied User.
     */
    protected void doUpdateUser(User user)
    {
        Connection conn = openConnection();
        PreparedStatement updateUserStatement = null;

        // Update the database.
        try {
            updateUserStatement = conn.prepareStatement(m_updateUserSql);
            setUserForUpdateStatement(user, updateUserStatement);
            updateUserStatement.execute();
        }
        catch ( SQLException sqlExc ) {
            sqlExc.printStackTrace();
            throw new CascadingRuntimeException("Error accessing database", sqlExc);
        } finally {
            theJDBCUtil.closeJDBCStatement(updateUserStatement);
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }

    /**
     * Gets a user by name, ignoring case if specified.
     * If the specified SQL statement has been defined, this method
     * overrides the basic implementation in AbstractUsersRepository
     * to increase performance.
     */
    protected User getUserByName(String name, boolean ignoreCase)
    {
        // See if this statement has been set, if not, use
        // simple superclass method.
        if ( m_userByNameCaseInsensitiveSql == null ) {
            return super.getUserByName(name, ignoreCase);
        }

        // Always get the user via case-insensitive SQL,
        // then check case if necessary.
        Connection conn = openConnection();
        try {
            // Get a ResultSet containing all users.
            String sql = m_userByNameCaseInsensitiveSql;
            PreparedStatement getUsersStatement = 
                conn.prepareStatement(sql);

            getUsersStatement.setString(1, name.toLowerCase(Locale.US));

            ResultSet rsUsers = getUsersStatement.executeQuery();

            // For case-insensitive matching, the first matching user will be returned.
            User user = null;
            while ( rsUsers.next() ) {
                User rowUser = readUserFromResultSet(rsUsers);
                String actualName = rowUser.getUserName();
                    
                // Check case before we assume it's the right one.
                if ( ignoreCase || actualName.equals(name) ) {
                    user = rowUser;
                    break;
                }
            }
            return user;
        }
        catch ( SQLException sqlExc ) {
            sqlExc.printStackTrace();
            throw new CascadingRuntimeException("Error accessing database", sqlExc);
        }
        finally {
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }


    /**
     * Reads properties for a User from an open ResultSet.
     * Subclass implementations of this method must have knowledge of the fields
     * presented by the "select" and "selectByLowercaseName" SQL statements.
     * These implemenations may generate a subclass-specific User instance.
     * 
     * @param rsUsers A ResultSet with a User record in the current row.
     * @return A User instance
     * @exception SQLException
     *                   if an exception occurs reading from the ResultSet
     */
    protected abstract User readUserFromResultSet(ResultSet rsUsers)
        throws SQLException;

    /**
     * Set parameters of a PreparedStatement object with 
     * property values from a User instance.
     * Implementations of this method have knowledge of the parameter
     * ordering of the "insert" SQL statement definition.
     * 
     * @param user       a User instance, which should be an implementation class which
     *                   is handled by this Repostory implementation.
     * @param userInsert a PreparedStatement initialised with SQL taken from the "insert" SQL definition.
     * @exception SQLException
     *                   if an exception occurs while setting parameter values.
     */
    protected abstract void setUserForInsertStatement(User user, 
                                                      PreparedStatement userInsert)
        throws SQLException;

    /**
     * Set parameters of a PreparedStatement object with
     * property values from a User instance.
     * Implementations of this method have knowledge of the parameter
     * ordering of the "update" SQL statement definition.
     * 
     * @param user       a User instance, which should be an implementation class which
     *                   is handled by this Repostory implementation.
     * @param userUpdate a PreparedStatement initialised with SQL taken from the "update" SQL definition.
     * @exception SQLException
     *                   if an exception occurs while setting parameter values.
     */
    protected abstract void setUserForUpdateStatement(User user, 
                                                      PreparedStatement userUpdate)
        throws SQLException;

    /**
     * Opens a connection, handling exceptions.
     */
    private Connection openConnection()
    {
        try {
            return m_datasource.getConnection();
        }
        catch (SQLException sqle) {
            throw new CascadingRuntimeException(
                "An exception occurred getting a database connection.", sqle);
        }
    }
}