/*
    * Copyright 2012-2013 smartics, Kronseder & Reiner GmbH
    *
    * 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 de.smartics.properties.spi.config.ds;
  
  import java.io.PrintStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Map;
  import java.util.Map.Entry;
  
  import org.apache.commons.lang.StringUtils;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  
  import de.smartics.util.lang.Arg;
  
  /**
   * Helper to create database configurations with a data source.
   */
  public class DefaultDataSourceManager extends AbstractDataSourceDescriptor
      implements PropertiesDataSourceManager
  { // NOPMD
    // ********************************* Fields *********************************
  
    // --- constants ------------------------------------------------------------
  
    /**
     * The class version identifier.
     */
    private static final long serialVersionUID = 1L;
  
    /**
     * Reference to the logger for this class.
     */
    private static final Logger LOG = LoggerFactory
        .getLogger(DefaultDataSourceManager.class);
  
    // --- members --------------------------------------------------------------
  
    /**
     * The proxy to provide access to a data source.
     *
     * @serial
     */
    private final DataSourceProxy dataSourceProxy;
  
    /**
     * The properties to fill the database initially. May be <code>null</code>.
     *
     * @serial
     */
    private final Map<String, Map<String, String>> initialProperties;
  
    /**
     * Signals to drop the properties table before creation.
     *
     * @serial
     */
    protected final boolean dropTable;
  
    /**
     * The flag signals that any problems encountered on creating the table should
     * be ignored. Useful if there are no means to check is a database table
     * already exists.
     *
     * @serial
     */
    protected final boolean ignoreTableCreationProblems;
  
    /**
     * The SQL statement to create the configuration table.
     */
    private final String createTableSqlStatement;
  
    /**
     * The SQL statement to insert or update a value in the table, dependent on if
     * the value is already in the database or not.
     */
    private final String insertOrUpdateSqlStatementTemplate;
  
    // ****************************** Initializer *******************************
 
   // ****************************** Constructors ******************************
 
   /**
    * Default constructor.
    *
    * @param builder the builder of manager instance.
    */
   protected DefaultDataSourceManager(final Builder builder)
   {
     super(builder);
 
     this.dataSourceProxy = builder.dataSourceProxy;
     this.initialProperties = builder.initialProperties;
     this.dropTable = builder.dropTable;
     this.ignoreTableCreationProblems = builder.ignoreTableCreationProblems;
     this.createTableSqlStatement =
         filterStatement(builder.createTableSqlStatementTemplate);
     this.insertOrUpdateSqlStatementTemplate =
         filterStatement(builder.insertOrUpdateSqlStatementTemplate);
   }
 
   // ****************************** Inner Classes *****************************
 
   /**
    * The builder of manager instances.
    */
   public static class Builder extends AbstractDataSourceDescriptor.Builder
   {
     // ******************************** Fields ********************************
 
     // --- constants ----------------------------------------------------------
 
     /**
      * The default table template.
      * <p>
      * The value of this constant is {@value}.
      * </p>
      */
     public static final String DEFAULT_CREATE_TABLE_TEMPLATE =
         "CREATE TABLE IF NOT EXISTS ${table}"
             + " (${configColumn} VARCHAR(128) NOT NULL,"
             + " ${nameColumn}    VARCHAR(64) NOT NULL,"
             + " ${valueColumn}   VARCHAR(255),"
             + " CONSTRAINT prime UNIQUE (${configColumn}, ${nameColumn}))";
 
     // --- members ------------------------------------------------------------
 
     /**
      * The proxy to provide access to a data source.
      */
     protected DataSourceProxy dataSourceProxy;
 
     /**
      * The properties to fill the database initially. May be <code>null</code>.
      */
     private Map<String, Map<String, String>> initialProperties;
 
     /**
      * Signals to drop the properties table before creation.
      */
     private boolean dropTable = true;
 
     /**
      * The flag signals that any problems encountered on creating the table
      * should be ignored. Useful if there are no means to check is a database
      * table already exists.
      */
     private boolean ignoreTableCreationProblems = true;
 
     /**
      * The template to create an SQL statement to create the configuration
      * table. Use <code>${table}</code>, <code>${configColumn}</code>,
      * <code>${nameColumn}</code> and <code>${valueColumn}</code> to reference
      * the name of the configuration table, the column that stores the property
      * names and the column storing the property values.
      */
     private String createTableSqlStatementTemplate =
         DEFAULT_CREATE_TABLE_TEMPLATE;
 
     /**
      * The template to create an SQL statement to insert or update a value in
      * the table, dependent on if the value is already in the database or not.
      * Use <code>${table}</code>, <code>${configColumn}</code>,
      * <code>${nameColumn}</code> and <code>${valueColumn}</code> to reference
      * the name of the configuration table, the column that stores the property
      * names and the column storing the property values.
      */
     protected String insertOrUpdateSqlStatementTemplate;
 
     // ***************************** Initializer ******************************
 
     // ***************************** Constructors *****************************
 
     // ***************************** Inner Classes ****************************
 
     // ******************************** Methods *******************************
 
     // --- init ---------------------------------------------------------------
 
     // --- get&set ------------------------------------------------------------
 
     /**
      * Sets the template to create an SQL statement to create the configuration
      * table. Use <code>${table}</code>, <code>${configColumn}</code>,
      * <code>${nameColumn}</code> and <code>${valueColumn}</code> to reference
      * the name of the configuration table, the column that stores the property
      * names and the column storing the property values.
      * <p>
      * Per default the {@link #DEFAULT_CREATE_TABLE_TEMPLATE default table
      * template} is used.
      * </p>
      *
      * @param createTableSqlStatementTemplate the template to create an SQL
      *          statement to create the configuration table.
      * @see #DEFAULT_CREATE_TABLE_TEMPLATE
      */
     public final void setCreateTableSqlStatementTemplate(
         final String createTableSqlStatementTemplate)
     {
       this.createTableSqlStatementTemplate = createTableSqlStatementTemplate;
     }
 
     /**
      * Set the template to insert or update a value in the configuration table.
      *
      * @param insertOrUpdateSqlStatementTemplate the template to set.
      */
     public void setInsertOrUpdateSqlStatementTemplate(
         final String insertOrUpdateSqlStatementTemplate)
     {
       this.insertOrUpdateSqlStatementTemplate =
           insertOrUpdateSqlStatementTemplate;
     }
 
     /**
      * Sets the proxy to provide access to a data source.
      *
      * @param dataSourceProxy the proxy to provide access to a data source.
      * @throws NullPointerException of {@code dataSourceProxy} is
      *           <code>null</code>.
      */
     public final void setDataSourceProxy(final DataSourceProxy dataSourceProxy)
       throws NullPointerException
     {
       this.dataSourceProxy =
           Arg.checkNotNull("dataSourceProxy", dataSourceProxy);
     }
 
     /**
      * Sets the properties to fill the database initially. May be
      * <code>null</code>.
      *
      * @param initialProperties the properties to fill the database initially.
      */
     public final void setInitialProperties(
         final Map<String, Map<String, String>> initialProperties)
     {
       this.initialProperties = initialProperties;
     }
 
     /**
      * Sets the value for dropTable.
      * <p>
      * Signals to drop the properties table before creation.
      *
      * @param dropTable the value for dropTable.
      */
     public final void setDropTable(final boolean dropTable)
     {
       this.dropTable = dropTable;
     }
 
     /**
      * Sets the flag signals that any problems encountered on creating the table
      * should be ignored. Useful if there are no means to check is a database
      * table already exists.
      *
      * @param ignoreTableCreationProblems the flag signals that any problems
      *          encountered on creating the table should be ignored.
      */
     public final void setIgnoreTableCreationProblems(
         final boolean ignoreTableCreationProblems)
     {
       this.ignoreTableCreationProblems = ignoreTableCreationProblems;
     }
 
     // --- business -----------------------------------------------------------
 
     /**
      * Creates the instance.
      *
      * @return the created instance.
      * @throws NullPointerException if no data source proxy has been provided.
      */
     public DefaultDataSourceManager build() throws NullPointerException
     {
       Arg.checkNotNull("dataSourceProxy", dataSourceProxy);
       Arg.checkNotNull("insertOrUpdateSqlStatementTemplate",
           insertOrUpdateSqlStatementTemplate);
 
       return new DefaultDataSourceManager(this);
     }
 
     // --- object basics ------------------------------------------------------
   }
 
   // ********************************* Methods ********************************
 
   // --- init -----------------------------------------------------------------
 
   private String filterStatement(final String template)
   {
     if (template == null)
     {
       return null;
     }
 
     final String sql =
         StringUtils.replaceEach(template, new String[] { "${table}",
                                                         "${configColumn}",
                                                         "${nameColumn}",
                                                         "${valueColumn}" },
             new String[] { getTable(), getConfigColumn(), getNameColumn(),
                           getValueColumn() });
     return sql;
   }
 
   // --- get&set --------------------------------------------------------------
 
   @Override
   public final String getDataSourceId()
   {
     return dataSourceProxy.getDataSourceId();
   }
 
   @Override
   public final DataSourceProxy getDataSourceProxy()
   {
     return dataSourceProxy;
   }
 
   /**
    * Returns the SQL statement to insert or update a value in the table,
    * dependent on if the value is already in the database or not.
    *
    * @return the SQL statement to insert or update a value in the table,
    *         dependent on if the value is already in the database or not.
    */
   public String getInsertOrUpdateSqlStatementTemplate()
   {
     return insertOrUpdateSqlStatementTemplate;
   }
 
   // --- business -------------------------------------------------------------
 
   @Override
   public final void createConfigTable() throws DataSourceException
   {
     try
     {
       initDataSource();
     }
     catch (final SQLException e)
     {
       throw new DataSourceException(new DataSourceMessageBean(
           DataSourceCode.CANNOT_CREATE_TABLE, e, getDataSourceId()));
     }
   }
 
   private void initDataSource() throws SQLException
   {
     if (createTableSqlStatement != null)
     {
       if (dropTable)
       {
         dropConfigTable();
       }
 
       initTables();
     }
   }
 
   private void initTables() throws SQLException
   {
     final Connection connection =
         dataSourceProxy.getDataSource().getConnection();
 
     try
     {
       if (!initializedTableExists(connection))
       {
         initTable(connection);
 
         initProperties(connection);
       }
     }
     finally
     {
       connection.close();
     }
   }
 
   private boolean initializedTableExists(final Connection connection)
     throws SQLException
   {
     final Statement statement = connection.createStatement();
     try
     {
 
       final String querySql = "SELECT COUNT(*) FROM " + getTable();
       final ResultSet resultSet = statement.executeQuery(querySql);
       if (resultSet.next())
       {
         final int count = resultSet.getInt(1);
         return count > 0;
       }
 
       return false;
     }
     catch (final SQLException e)
     {
       return false;
     }
     finally
     {
       statement.close();
     }
   }
 
   private void initTable(final Connection connection) throws SQLException
   {
     try
     {
       execute(connection, createTableSqlStatement);
     }
     catch (final SQLException e)
     {
       if (!ignoreTableCreationProblems)
       {
         throw e;
       }
     }
   }
 
   private void initProperties(final Connection connection) throws SQLException
   {
     if (initialProperties != null && !initialProperties.isEmpty())
     {
       final String sql = "INSERT INTO " + getTable() + " VALUES (?, ?, ?)";
       final PreparedStatement statement = connection.prepareStatement(sql);
       for (final Entry<String, Map<String, String>> configEntry : initialProperties
           .entrySet())
       {
         final String configName = configEntry.getKey();
         final Map<String, String> config = configEntry.getValue();
 
         for (final Entry<String, String> property : config.entrySet())
         {
           statement.setString(1, configName);
           statement.setString(2, property.getKey());
           statement.setString(3, property.getValue());
           statement.execute();
         }
       }
 
       statement.close();
     }
   }
 
   private void execute(final Connection connection, final String createTableSql)
     throws SQLException
   {
     final Statement statement = connection.createStatement();
     try
     {
       statement.executeUpdate(createTableSql);
     }
     finally
     {
       statement.close();
     }
   }
 
   @Override
   public final void dropConfigTable() throws DataSourceException
   {
     try
     {
       final Connection connection =
           dataSourceProxy.getDataSource().getConnection();
 
       try
       {
         final Statement statement = connection.createStatement();
 
         final String sql = "DROP TABLE " + getTable();
         statement.executeUpdate(sql);
       }
       catch (final SQLException e)
       {
         LOG.warn("Dropping failed:  " + e.getMessage());
       }
       finally
       {
         connection.close();
       }
     }
     catch (final SQLException e)
     {
       throw new DataSourceException(new DataSourceMessageBean(
           DataSourceCode.CANNOT_DROP_TABLE, e, getDataSourceId()));
     }
   }
 
   @Override
   public final void print(final PrintStream out) throws DataSourceException
   {
     try
     {
       final Connection connection =
           dataSourceProxy.getDataSource().getConnection();
 
       try
       {
         final Statement statement = connection.createStatement();
 
         final String querySql =
             "SELECT " + getConfigColumn() + ", " + getNameColumn() + ", "
                 + getValueColumn() + " FROM " + getTable();
         final ResultSet resultSet = statement.executeQuery(querySql);
         while (resultSet.next())
         {
           final String config = resultSet.getString(1);
           final String name = resultSet.getString(2);
           final String value = resultSet.getString(3);
 
           out.println("  " + config + " | " + name + "=" + value);
         }
       }
       finally
       {
         connection.close();
       }
     }
     catch (final SQLException e)
     {
       throw new DataSourceException(new DataSourceMessageBean(
           DataSourceCode.CANNOT_PRINT_TABLE, e, getDataSourceId()));
     }
   }
 
   @Override
   public final List<String> getConfigurationKeys() throws DataSourceException
   {
     try
     {
       final Connection connection =
           dataSourceProxy.getDataSource().getConnection();
       final List<String> keys = new ArrayList<String>(32);
 
       try
       {
         final Statement statement = connection.createStatement();
 
         final String sqlKey = getConfigColumn();
         final String querySql =
             "SELECT DISTINCT " + sqlKey + " FROM " + getTable();
         final ResultSet resultSet = statement.executeQuery(querySql);
         while (resultSet.next())
         {
           final String key = resultSet.getString(sqlKey);
           keys.add(key);
         }
       }
       finally
       {
         connection.close();
       }
 
       return keys;
     }
     catch (final SQLException e)
     {
       throw new DataSourceException(
           new DataSourceMessageBean(
               DataSourceCode.CANNOT_ACCESS_CONFIGURATION_KEYS, e,
               getDataSourceId()));
     }
   }
 
   // --- object basics --------------------------------------------------------
 
   /**
    * Returns the string representation of the object.
    *
    * @return the string representation of the object.
    */
   @Override
   public String toString()
   {
     final StringBuilder buffer = new StringBuilder();
 
     buffer.append(dataSourceProxy);
 
     return buffer.toString();
   }
 }