/*
    * Copyright (c) 2002-2017, Mairie de Paris
    * All rights reserved.
    *
    * Redistribution and use in source and binary forms, with or without
    * modification, are permitted provided that the following conditions
    * are met:
    *
    *  1. Redistributions of source code must retain the above copyright notice
   *     and the following disclaimer.
   *
   *  2. Redistributions in binary form must reproduce the above copyright notice
   *     and the following disclaimer in the documentation and/or other materials
   *     provided with the distribution.
   *
   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
   *     contributors may be used to endorse or promote products derived from
   *     this software without specific prior written permission.
   *
   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
   * POSSIBILITY OF SUCH DAMAGE.
   *
   * License 1.0
   */
  package fr.paris.lutece.plugins.adminsql.business;
  
  import fr.paris.lutece.portal.service.database.PluginConnectionService;
  import fr.paris.lutece.portal.service.plugin.Plugin;
  import fr.paris.lutece.portal.service.plugin.PluginService;
  import fr.paris.lutece.portal.service.util.AppException;
  import fr.paris.lutece.portal.service.util.AppLogService;
  import fr.paris.lutece.util.sql.DAOUtil;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  
  import java.util.ArrayList;
  import java.util.List;
  import java.util.StringTokenizer;
  
  
  /**
   * Creates a new instance of DataDAO
   */
  public class DataDAO implements IDataDAO
  {
      //Constants
      private static final String WHERE = " WHERE ";
      private static final String VALUES = " VALUES ";
      private static final String SELECT = " SELECT ";
      private static final String FROM = " FROM ";
      private static final String AND = " AND ";
      private static final String UPDATE = "UPDATE ";
      private static final String SET = " SET ";
      private static final String STR_SQL_ERROR = "SQL Error executing command : ";
      private static final String DELETE_FROM = "DELETE FROM ";
  
      //Requêtes
      private static final String SQL_QUERY_SELECT_ALL_DATA = "SELECT * FROM ";
      private static final String SQL_QUERY_SELECT_A_ROW = "SELECT * FROM ";
      private static final String SQL_QUERY_INSERT_VALUES = "INSERT INTO ";
  
      /**
       * Selects and loads into the Data object all the data from the database
       * @param strTableName the name of the table
       * @param connectionService PluginConnectionService Object
       * @return list of data
       */
      public List<List<String>> selectDataList( String strTableName, PluginConnectionService connectionService )
      {
          List<List<String>> listData = new ArrayList<List<String>>(  );
  
          try
          {
              listData = selectSqlQuery( SQL_QUERY_SELECT_ALL_DATA + strTableName, connectionService );
          }
          catch ( SQLException sql )
          {
              AppLogService.error( sql.getMessage(  ), sql );
          }
  
          return listData;
      }
  
      /**
       * Selects and loads into the Data object all the data from the database in user request
       * @param strPoolName the name of the pool
      * @param strUserRequest user request
      * @param connectionService PluginConnectionService Object
      * @return list of data
      */
     public List<List<String>> selectDataListUserRequest( String strPoolName, String strUserRequest,
         PluginConnectionService connectionService )
     {
         List<List<String>> listData = new ArrayList<List<String>>(  );
         connectionService = new PluginConnectionService( strPoolName );
         connectionService.setPool( strPoolName );
 
         try
         {
             listData = selectSqlQuery( strUserRequest, connectionService );
         }
         catch ( SQLException sql )
         {
             AppLogService.error( sql.getMessage(  ), sql );
         }
 
         return listData;
     }
 
     /**
      * Insert data in table
      * @param strPoolName the name of the pool
      * @param strTableName the name of the table
      * @param listData the list of data
      * @param listFieldsNames list of fields names
      * @param connectionService PluginConnectionService object
      */
     public void insert( String strPoolName, String strTableName, List<String> listData, List<String> listFieldsNames,
         PluginConnectionService connectionService )
     {
         String strFieldsNames = "";
         String strDataValues = "";
 
         for ( String strFieldName : listFieldsNames )
         {
             strFieldsNames = strFieldsNames + strFieldName + ", ";
         }
 
         strFieldsNames = strFieldsNames.substring( 0, strFieldsNames.length(  ) - 2 );
 
         String strFieldsNameFinal = " (" + strFieldsNames + ") ";
 
         for ( String strDataValue : listData )
         {
             strDataValues += ( "'" + strDataValue + "'" + ", " );
         }
 
         strDataValues = strDataValues.substring( 0, strDataValues.length(  ) - 2 );
 
         String strDataValuesFinal = " (" + strDataValues + ") ";
         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VALUES + strTableName + " " + strFieldsNameFinal + VALUES +
                 strDataValuesFinal, changePool( strPoolName ) );
         daoUtil.executeUpdate(  );
         daoUtil.free(  );
     }
 
     /**
      * Store the data in tha table in a database
      * @param strPoolName the name of the pool
      * @param strTableName the name of the table
      * @param listFieldValues the list fields values
      * @param connectionService PluginConnectionService object
      */
     public void store( String strPoolName, String strTableName, List<FieldValues> listFieldValues,
         PluginConnectionService connectionService )
     {
         String strAllDataToModify = "";
         String strAllDataModified = "";
 
         for ( FieldValues fieldValues : listFieldValues )
         {
             strAllDataModified = strAllDataModified + "," + fieldValues.getFieldName(  ) + "=" + "'" +
                 fieldValues.getNewValue(  ) + "'";
             strAllDataToModify = strAllDataToModify + AND + fieldValues.getFieldName(  ) + "=" + "'" +
                 fieldValues.getOldValue(  ) + "'";
         }
 
         strAllDataModified = strAllDataModified.substring( 1 );
         strAllDataToModify = strAllDataToModify.substring( 4 );
 
         DAOUtil daoUtil = new DAOUtil( UPDATE + strTableName + SET + strAllDataModified + WHERE + strAllDataToModify,
                 changePool( strPoolName ) );
         daoUtil.executeUpdate(  );
         daoUtil.free(  );
     }
 
     /**
      * Delete a row in a table
      * @param strPoolName the name of the pool
      * @param strTableName the name of the table
      * @param listConcatFieldNameAndData the list of concatenation of fields name and data
      * @param connectionService PluginConnectionService object
      */
     public void delete( String strPoolName, String strTableName, List<String> listConcatFieldNameAndData,
         PluginConnectionService connectionService )
     {
         String strAllDataToDelete = "";
 
         for ( String strConcatFieldNameAndData : listConcatFieldNameAndData )
         {
             strAllDataToDelete = strAllDataToDelete + AND + strConcatFieldNameAndData;
         }
 
         strAllDataToDelete = strAllDataToDelete.substring( 4 );
 
         DAOUtil daoUtil = new DAOUtil( DELETE_FROM + strTableName + WHERE + strAllDataToDelete,
                 changePool( strPoolName ) );
         daoUtil.executeUpdate(  );
         daoUtil.free(  );
     }
 
     /**
      * Insert the data with user request
      * @param strUserRequest user request
      * @param connectionService PluginConnectionService object
      * @param strPoolName the name of the pool
      */
     public void insertWithUserRequest( String strUserRequest, PluginConnectionService connectionService,
         String strPoolName )
     {
         DAOUtil daoUtil = new DAOUtil( strUserRequest, changePool( strPoolName ) );
         daoUtil.executeUpdate(  );
         daoUtil.free(  );
     }
 
     /**
      * Find the list of data of a table
      * @param strTableName the name of the table
      * @param strContatFieldNameAndData the list of concatenation of fields name and data
      * @param listFields list of fields
      * @param connectionService PluginConnectionService object
      * @return list of the data
      */
     public List<List<String>> load( String strTableName, String strContatFieldNameAndData, List<Field> listFields,
         PluginConnectionService connectionService )
     {
         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_A_ROW + strTableName + WHERE + strContatFieldNameAndData );
         daoUtil.executeQuery(  );
         List<List<String>> listDataValueSelectedByPK = new ArrayList(  );
         return listDataValueSelectedByPK;
     }
 
     /**
      * Find the answer of the user query
      * @param strRequest the request
      * @param connectionService PluginConnectionService object
      * @return list of the data
      */
     private List<List<String>> selectSqlQuery( String strRequest, PluginConnectionService connectionService )
         throws SQLException
     {
         Connection connection = null;
         PreparedStatement statement = null;
 
         try
         {
             connection = connectionService.getConnection(  );
             statement = connection.prepareStatement( strRequest );
 
             ResultSet resultSet = statement.executeQuery(  );
             ResultSetMetaData rsmd = resultSet.getMetaData(  );
 
             ArrayList listRow = new ArrayList(  );
 
             while ( resultSet.next(  ) )
             {
                 String strValue = null;
                 ArrayList listLine = new ArrayList(  );
 
                 for ( int i = 1; i <= rsmd.getColumnCount(  ); i++ )
                 {
                     if ( resultSet.getObject( rsmd.getColumnName( i ) ) != null )
                     {
                         strValue = resultSet.getObject( rsmd.getColumnName( i ) ).toString(  );
                     }
                     else
                     {
                         strValue = " ";
                     }
 
                     listLine.add( strValue );
                 }
 
                 listRow.add( listLine );
             }
 
             statement.close(  );
             statement = null;
 
             return listRow;
         }
         finally
         {
             try
             {
                 if ( statement != null )
                 {
                     statement.close(  );
                 }
             }
             catch ( SQLException e )
             {
                 throw new AppException( "SQL Error executing command : " + e.toString(  ) );
             }
 
             connectionService.freeConnection( connection );
         }
     }
 
     /**
      * Result of user request
      * @param strUserRequest user request
      * @param connectionService PluginConnectionService object
      * @return  result of user request
      */
     public List<List<String>> findResultOfUserRequest( String strUserRequest, PluginConnectionService connectionService )
     {
         List<List<String>> listData = new ArrayList<List<String>>(  );
         List<String> listFields = new ArrayList<String>(  );
 
         try
         {
             listData = selectSqlQuery( strUserRequest, connectionService );
         }
         catch ( SQLException sql )
         {
             AppLogService.error( sql.getMessage(  ), sql );
         }
 
         return listData;
     }
 
     /**
      * Find the names of all columns
      * @param strUserRequest user request
      * @param connectionService PluginConnectionService object
      * @param strPoolName the name of the pool
      * @return list of all names of the columns
      */
     public List<String> selectColumnNames( String strUserRequest, PluginConnectionService connectionService,
         String strPoolName )
     {
         List<String> listFields = new ArrayList(  );
         DAOUtil daoUtil = new DAOUtil( strUserRequest, changePool( strPoolName ) );
         daoUtil.executeQuery(  );
 
         for ( int i = 1; i <= getColumnCount( daoUtil ); i++ )
         {
             String columnName = getColumnName( i, daoUtil );
             listFields.add( columnName );
         }
 
         daoUtil.free(  );
 
         return listFields;
     }
 
     private String getColumnName( int nColumn, DAOUtil daoUtil )
     {
         try
         {
             ResultSetMetaData rsmd = daoUtil.getResultSet(  ).getMetaData(  );
 
             return rsmd.getColumnName( nColumn );
         }
         catch ( SQLException e )
         {
             daoUtil.free(  );
             throw new AppException( STR_SQL_ERROR + e.toString(  ) );
         }
     }
 
     private int getColumnCount( DAOUtil daoUtil )
     {
         try
         {
             ResultSetMetaData rsmd = daoUtil.getResultSet(  ).getMetaData(  );
 
             return rsmd.getColumnCount(  );
         }
         catch ( SQLException e )
         {
             daoUtil.free(  );
             throw new AppException( STR_SQL_ERROR + e.toString(  ) );
         }
     }
 
     private Plugin changePool( String strPoolName )
     {
         Plugin plugin = PluginService.getPlugin( "adminsql" );
         PluginConnectionService connectionService = new PluginConnectionService( strPoolName );
         connectionService.setPool( strPoolName );
         plugin.setConnectionService( connectionService );
 
         return plugin;
     }
 }