/*

  * 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.plugins.adminsql.util.AdminSqlUtil;

 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.util.ReferenceItem;

 import fr.paris.lutece.util.ReferenceList;

 import fr.paris.lutece.util.sql.DAOUtil;

 import java.util.ArrayList;

 import java.util.List;

 /**

  * Creates a new instance of DataDAO

  */

 public class FieldDAO implements IFieldDAO

 {

     //Constantes

     private static final String NULL_VALUE = "NULL";

     private static final String NOT = "NOT ";

     private static final String CHANGE = "CHANGE";

     private static final String ADD = " ADD ";

     private static final String ADD_KEY = " ,ADD ";

     private static final String PRIMARY_KEY = "PRIMARY KEY";

     private static final String FOREIGN_KEY = "FOREIGN KEY";

     private static final String INDEX_KEY = "INDEX";

     private static final String UNIQUE_KEY = "UNIQUE";

     private static final String DROP = " DROP ";

     private static final String DROP_KEY = " ,DROP ";

     private static final String POOL_ADMINSQL = "adminsql";

     //Requêtes

     private static final String SQL_QUERY_SHOW_TABLE_FIELDS_STRUCTURE = " SHOW COLUMNS FROM ";

     private static final String SQL_QUERY_SELECT_A_FIELD_ON_TABLE = "DESC ";

     private static final String SQL_QUERY_SELECTALL_FIELD_TYPE = " SELECT id_field_type, label_field_type FROM adminsql_field_type";

     private static final String SQL_QUERY_SELECTALL_FIELD_KEY = "SELECT id_field_key, label_field_key FROM adminsql_field_key";

     private static final String SQL_QUERY_SELECTALL_FIELD_NULL = "SELECT id_field_null, label_field_null FROM adminsql_field_null";

     private static final String SQL_QUERY_FIND_FIELD_TYPE_ID = " SELECT id_field_type FROM adminsql_field_type WHERE label_field_type= ?";

     private static final String SQL_QUERY_FIND_FIELD_TYPE_LABEL_BY_ID = " SELECT label_field_type FROM adminsql_field_type WHERE id_field_type= ?";

     private static final String SQL_QUERY_FIND_FIELD_NULL_ID = " SELECT id_field_null FROM adminsql_field_null WHERE label_field_null= ?";

     private static final String SQL_QUERY_FIND_FIELD_NULL_LABEL_BY_ID = " SELECT label_field_null FROM adminsql_field_null WHERE id_field_null= ?";

     private static final String SQL_QUERY_FIND_FIELD_KEY_ID = " SELECT id_field_key FROM adminsql_field_key WHERE label_field_key= ?";

     private static final String SQL_QUERY_FIND_FIELD_KEY_LABEL_BY_ID = " SELECT label_field_key FROM adminsql_field_key WHERE id_field_key= ?";

     private static final String SQL_QUERY_UPDATE = " ALTER TABLE ";

     private static final String SQL_QUERY_DELETE_FIELD = " ALTER TABLE ";

     /**

      * Load a field from the table

      * @param strTableName the name of the table

      * @param strFieldName the name of the field

      * @param plugin adminsql plugin

      * @param strPoolName the name of the pool

      * @return a field object

      */

     public Field load( String strPoolName, String strTableName, String strFieldName, Plugin plugin )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_A_FIELD_ON_TABLE + strTableName + " " + strFieldName, plugin );

         daoUtil.executeQuery(  );

         Field field = new Field(  );

         String[] tabString = { "", "" };

         if ( daoUtil.next(  ) )

         {

             field.setFieldName( daoUtil.getString( 1 ) );

             field.setTypeValue( daoUtil.getString( 2 ) );

             String strFieldType = field.getTypeValue(  );

             if ( strFieldType.equals( "date" ) || strFieldType.equals( "datetime" ) || strFieldType.equals( "time" ) ||

                     strFieldType.equals( "timestamp" ) || strFieldType.equals( "text" ) ||

                     strFieldType.equals( "tinytext" ) || strFieldType.equals( "longtext" ) ||

                     strFieldType.equals( "mediumtext" ) || strFieldType.equals( "blob" ) ||

                     strFieldType.equals( "tinyblob" ) || strFieldType.equals( "longblob" ) ||

                     strFieldType.equals( "mediumblob" ) )

             {

                 field.setLabelTypeValue( strFieldType );

                 field.setLengthTypeValue( "" );

                 field.setIdTypeValue( findFieldTypeIdbyLabel( strFieldType, plugin, strPoolName ) );

             }

             else

             {

                 tabString = AdminSqlUtil.getFieldDetails( field.getTypeValue(  ) );

                 field.setLabelTypeValue( tabString[0] );

                 field.setLengthTypeValue( tabString[1] );

                 field.setIdTypeValue( findFieldTypeIdbyLabel( tabString[0], plugin, strPoolName ) );

             }

             field.setLabelNullValue( daoUtil.getString( 3 ) );

             int nFieldIdNull = findFieldNullIdbyLabel( daoUtil.getString( 3 ), plugin, strPoolName );

             field.setIdNullValue( nFieldIdNull );

             String strLabelKeyValue = daoUtil.getString( 4 );

             if ( strLabelKeyValue.equals( "PRI" ) )

             {

                 strLabelKeyValue = "PRIMAIRE";

             }

             int nFieldIdKey = findFieldKeyIdbyLabel( strLabelKeyValue, plugin, strPoolName );

             field.setIdKeyValue( nFieldIdKey );

             if ( daoUtil.getString( 5 ) == null )

             {

                 field.setDefaultValue( NULL_VALUE );

             }

             else

             {

                 field.setDefaultValue( daoUtil.getString( 5 ) );

             }

         }

         daoUtil.free(  );

         return field;

     }

     /**

      * Store a field in the table

      * @param strTableName the name of the table

      * @param strFieldNameToModify the name of the field to modify

      * @param field Field object

      * @param plugin adminsql plugin

      * @param strPoolName the name of the pool

      * @param nIdOldFieldKey find the key of the field if exists

      */

     public void store( String strPoolName, String strTableName, Field field, String strFieldNameToModify,

         int nIdOldFieldKey, Plugin plugin )

     {

         String strFieldName = field.getFieldName(  );

         field.setLabelTypeValue( findFieldTypeLabelbyId( field.getIdTypeValue(  ), plugin, strPoolName ) );

         String strFieldType = field.getLabelTypeValue(  );

         String strFieldLabelType = "";

         String strLengthTypeValue = field.getLengthTypeValue(  );

         if ( strLengthTypeValue.equals( "" ) || ( strLengthTypeValue == null ) )

         {

             strFieldLabelType = strFieldType;

         }

         else if ( strFieldType.equals( "year" ) )

         {

             strFieldLabelType = strFieldType + "(" + 4 + ")";

         }

         else

         {

             strFieldLabelType = strFieldType + "(" + field.getLengthTypeValue(  ) + ")";

         }

         field.setLabelNullValue( findFieldNullLabelbyId( field.getIdNullValue(), plugin, strPoolName ) );

         String strLabelNullValue = field.getLabelNullValue(  );

         String strFieldLabelNullValue = "";

         if ( strLabelNullValue.equals( "YES" ) )

         {

             strFieldLabelNullValue = NULL_VALUE;

         }

         else

         {

             strFieldLabelNullValue = NOT + NULL_VALUE;

         }

         int nIdKeyValue = field.getIdKeyValue(  );

         String strKey = "";

         String strNewPrimaryKey = "";

         if ( ( nIdOldFieldKey == 1 ) && ( nIdKeyValue == 2 ) )

         {

             strKey = ADD_KEY + PRIMARY_KEY;

             strNewPrimaryKey = "(" + strFieldName + ")";

         }

         else if ( ( nIdOldFieldKey == 2 ) && ( nIdKeyValue == 1 ) )

         {

             strKey = DROP_KEY + PRIMARY_KEY;

         }

         String strFieldLabelDefaultValue = field.getDefaultValue(  );

         String strDEFAULT;

         String strFieldLabelDefaultValueFinal;

         if ( strFieldLabelDefaultValue.equals( "" ) )

         {

             strDEFAULT = "";

             strFieldLabelDefaultValueFinal = "";

         }

         else

         {

             strDEFAULT = "DEFAULT";

             strFieldLabelDefaultValueFinal = " '" + strFieldLabelDefaultValue + "'";

         }

         String strAlterQuery = SQL_QUERY_UPDATE + " " + strTableName + " " + CHANGE + " " + strFieldNameToModify + " " +

             strFieldName + " " + strFieldLabelType + " " + " " + strFieldLabelNullValue + " " + strDEFAULT +

             strFieldLabelDefaultValueFinal + " " + strKey + strNewPrimaryKey;

         DAOUtil daoUtil = new DAOUtil( strAlterQuery, plugin );

         daoUtil.executeUpdate(  );

         daoUtil.free(  );

     }

     /**

      * Insert a field in the table

      * @param strPoolName the name of the pool

      * @param field Field object

      * @param strTableName the name of the table

      * @param plugin adminsql plugin

      */

     public void insert( String strPoolName, String strTableName, Field field, Plugin plugin )

     {

         String strFieldName = field.getFieldName(  );

         field.setLabelTypeValue( findFieldTypeLabelbyId( field.getIdTypeValue(  ), plugin, strPoolName ) );

         String strFieldLabelType = "";

         String strFieldType = field.getLabelTypeValue(  );

         String strLengthTypeValue = field.getLengthTypeValue(  );

         if ( strLengthTypeValue.equals( "" ) || ( strLengthTypeValue == null ) )

         {

             strFieldLabelType = strFieldType;

         }

         else if ( strFieldType.equals( "YEAR" ) )

         {

             strFieldLabelType = strFieldType + "(" + 4 + ")";

         }

         else

         {

             strFieldLabelType = strFieldType + "(" + field.getLengthTypeValue(  ) + ")";

         }

         field.setLabelNullValue( findFieldNullLabelbyId( field.getIdNullValue(  ), plugin, strPoolName ) );

         String strLabelNullValue = field.getLabelNullValue(  );

         String strFieldLabelNullValue = "";

         if ( strLabelNullValue.equals( "YES" ) )

         {

             strFieldLabelNullValue = NULL_VALUE;

         }

         else

         {

             strFieldLabelNullValue = NOT + NULL_VALUE;

         }

         int nIdKeyValue = field.getIdKeyValue(  );

         field.setLabelKeyValue( findFieldKeyLabelbyId( nIdKeyValue, plugin, strPoolName ) );

         String strFieldLabelKeyValue = field.getLabelKeyValue(  );

         String strKey;

         if ( nIdKeyValue == 1 )

         {

             strKey = "";

         }

         else

         {

             strKey = PRIMARY_KEY;

         }

         String strFieldLabelDefaultValue = field.getDefaultValue(  );

         String strDEFAULT = "";

         if ( strFieldLabelDefaultValue.equals( "" ) )

         {

             strDEFAULT = "";

         }

         else

         {

             strDEFAULT = "DEFAULT";

             strFieldLabelDefaultValue = " '" + strFieldLabelDefaultValue + "' ";

         }

         int nAddFieldEndOfTable = field.getFieldEndOfTable(  );

         int nAddFieldBeginningOfTable = field.getFieldBeginningOfTable(  );

         int nAddFieldAfterAField = field.getFieldAfterAField(  );

         int nPlaceOfField = field.getPlaceOfField(  );

         String strFIRST = "";

         if ( nPlaceOfField == 2 )

         {

             strFIRST = "FIRST";

         }

         else

         {

             strFIRST = "";

         }

         String strAfterTheField = "";

         String strAFTER = "";

         if ( nPlaceOfField == 3 )

         {

             strAFTER = "AFTER";

             strAfterTheField = field.getAfterTheField(  );

         }

         else

         {

             strAFTER = "";

         }

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE + strTableName + " " + ADD + " " + strFieldName + " " +

                 strFieldLabelType + " " + strKey + " " + strFieldLabelNullValue + " " + strDEFAULT +

                 strFieldLabelDefaultValue + strFIRST + strAFTER + " " + strAfterTheField, plugin );

         daoUtil.executeUpdate(  );

         daoUtil.free(  );

     }

     /**

      * Delete a field from the table

      * @param strTableName the name of the table

      * @param strFieldName the name of the field

      * @param plugin adminsql plugin

      */

     public void delete( String strTableName, String strFieldName, Plugin plugin )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FIELD + strTableName + DROP + strFieldName );

         daoUtil.executeUpdate(  );

         daoUtil.free(  );

     }

     /**

      * Find the list of fields from a table

      * @param strPoolName the name of the pool

      * @param strTableName the name of the table

      * @param plugin Plugin adminsql

      * @return list of fields

      */

     public List<Field> selectFieldList( String strPoolName, String strTableName, Plugin plugin )

     {

         List<Field> fieldList = new ArrayList<Field>(  );

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SHOW_TABLE_FIELDS_STRUCTURE + strTableName, changePool( strPoolName ) );

         daoUtil.executeQuery(  );

         while ( daoUtil.next(  ) )

         {

             Field field = new Field(  );

             field.setFieldName( daoUtil.getString( 1 ) );

             field.setTypeValue( daoUtil.getString( 2 ) );

             field.setLabelNullValue( daoUtil.getString( 3 ) );

             int nFieldIdNull = findFieldNullIdbyLabel( daoUtil.getString( 3 ), plugin, strPoolName );

             field.setIdNullValue( nFieldIdNull );

             field.setLabelKeyValue( daoUtil.getString( 4 ) );

             int nFieldIdKey = findFieldKeyIdbyLabel( daoUtil.getString( 4 ), plugin, strPoolName );

             field.setIdKeyValue( nFieldIdKey );

             if ( daoUtil.getString( 5 ) == null )

             {

                 field.setDefaultValue( NULL_VALUE );

             }

             else

             {

                 field.setDefaultValue( daoUtil.getString( 5 ) );

             }

             fieldList.add( field );

         }

         daoUtil.free(  );

         return fieldList;

     }

     /**

      * lists the field list possiblilities to create or modify fields form

      * @return Field list

      * @param strPoolName the name of the pool

      * @param plugin plugin adminsql

      */

     public ReferenceList selectFieldTypeList( String strPoolName, Plugin plugin )

     {

         ReferenceList fieldtypeList = new ReferenceList(  );

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_TYPE, getDefaultPool( plugin ) );

         daoUtil.executeQuery(  );

         while ( daoUtil.next(  ) )

         {

             ReferenceItem item = new ReferenceItem(  );

             item.setCode( daoUtil.getString( 1 ) );

             item.setName( daoUtil.getString( 2 ) );

             fieldtypeList.add( item );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return fieldtypeList;

     }

     /**

      * lists the field list possiblilities to create or modify fields form

      * @param strPoolName the name of the pool

      * @param plugin Plugin adminsql

      * @return the keys choice adminsql database

      */

     public ReferenceList selectFieldKeyList( String strPoolName, Plugin plugin )

     {

         ReferenceList fieldkeyList = new ReferenceList(  );

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_KEY, getDefaultPool( plugin ) );

         daoUtil.executeQuery(  );

         while ( daoUtil.next(  ) )

         {

             ReferenceItem item = new ReferenceItem(  );

             item.setCode( daoUtil.getString( 1 ) );

             item.setName( daoUtil.getString( 2 ) );

             fieldkeyList.add( item );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return fieldkeyList;

     }

     /**

      * lists the field list possiblilities to create or modify fields form

      * @param strPoolName the name of the pool

      * @param plugin Plugin adminsql

      * @return the null value choice adminsql database

      */

     public ReferenceList selectFieldNullList( String strPoolName, Plugin plugin )

     {

         ReferenceList fieldnullList = new ReferenceList(  );

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_NULL, getDefaultPool( plugin ) );

         daoUtil.executeQuery(  );

         while ( daoUtil.next(  ) )

         {

             ReferenceItem item = new ReferenceItem(  );

             item.setCode( daoUtil.getString( 1 ) );

             item.setName( daoUtil.getString( 2 ) );

             fieldnullList.add( item );

         }

         daoUtil.free(  );

         return fieldnullList;

     }

     /**

      * Find the field id type by label type from adminsql database

      * @param strLabelType the name of the type that user choose

      * @param plugin Plugin adminsql

      * @param strPoolName the name of the pool

      * @return id type from label type

      */

     public int findFieldTypeIdbyLabel( String strLabelType, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_ID, getDefaultPool( plugin ) );

         daoUtil.setString( 1, strLabelType );

         daoUtil.executeQuery(  );

         int nIdFieldType = 0;

         if ( daoUtil.next(  ) )

         {

             nIdFieldType = daoUtil.getInt( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return nIdFieldType;

     }

     /**

      * Find the field label type by id type from adminsql database

      * @param nId the id of type label

      * @param plugin Plugin adminsql

      * @param strPoolName name of the pool

      * @return the label of the type by id

      */

     public String findFieldTypeLabelbyId( int nId, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_LABEL_BY_ID, getDefaultPool( plugin ) );

         daoUtil.setInt( 1, nId );

         daoUtil.executeQuery(  );

         String strFieldTypeLabel = "";

         if ( daoUtil.next(  ) )

         {

             strFieldTypeLabel = daoUtil.getString( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return strFieldTypeLabel;

     }

     /**

      * Find the field id null by label null from adminsql database

      * @param strLabelNull the label of the null value

      * @param plugin Plugin adminsql

      * @param strPoolName the name of the pool

      * @return id null from null type

      */

     public int findFieldNullIdbyLabel( String strLabelNull, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_ID, getDefaultPool( plugin ) );

         daoUtil.setString( 1, strLabelNull );

         daoUtil.executeQuery(  );

         int nIdFieldNull = 0;

         if ( daoUtil.next(  ) )

         {

             nIdFieldNull = daoUtil.getInt( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return nIdFieldNull;

     }

     /**

      * Find the field id null by label null from adminsql database

      * @param nId the id of the null value

      * @param plugin Plugin adminsql

      * @param strPoolName the name of the pool

      * @return the label of null value by id

      */

     public String findFieldNullLabelbyId( int nId, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_LABEL_BY_ID, getDefaultPool( plugin ) );

         daoUtil.setInt( 1, nId );

         daoUtil.executeQuery(  );

         String strFieldNullLabel = "";

         if ( daoUtil.next(  ) )

         {

             strFieldNullLabel = daoUtil.getString( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return strFieldNullLabel;

     }

     /**

      * Find the field id key by label key from adminsql database

      * @param strLabelKey the label key of the field

      * @param plugin Plugin adminsql

      * @param strPoolName the name of the pool

      * @return id key from label key

      */

     public int findFieldKeyIdbyLabel( String strLabelKey, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_ID, getDefaultPool( plugin ) );

         daoUtil.setString( 1, strLabelKey );

         daoUtil.executeQuery(  );

         int nIdFieldKey = 0;

         if ( daoUtil.next(  ) )

         {

             nIdFieldKey = daoUtil.getInt( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return nIdFieldKey;

     }

     /**

      * Find the field id key by label key from adminsql database

      * @param nId the id of the key

      * @param plugin Plugin adminsql

      * @param strPoolName the name of the pool

      * @return the label of key by id

      */

     public String findFieldKeyLabelbyId( int nId, Plugin plugin, String strPoolName )

     {

         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_LABEL_BY_ID, getDefaultPool( plugin ) );

         daoUtil.setInt( 1, nId );

         daoUtil.executeQuery(  );

         String strFieldKeyLabel = "";

         if ( daoUtil.next(  ) )

         {

             strFieldKeyLabel = daoUtil.getString( 1 );

         }

         daoUtil.free(  );

         plugin = changePool( strPoolName );

         return strFieldKeyLabel;

     }

     private Plugin changePool( String strPoolName )

     {

         Plugin plugin = PluginService.getPlugin( "adminsql" );

         PluginConnectionService connectionService = new PluginConnectionService( strPoolName );

         connectionService.setPool( strPoolName );

         plugin.setConnectionService( connectionService );

         return plugin;

     }

     private Plugin getDefaultPool( Plugin plugin )

     {

         PluginConnectionService connectionService = new PluginConnectionService( plugin.getDbPoolName(  ) );

         connectionService.setPool( "adminsql" );

         plugin.setConnectionService( connectionService );

         return plugin; //TODO in properties

     }

 }