/*

  * 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;

     }

 }