/*
    * This file is part of Waarp Project (named also Waarp or GG).
    *
    *  Copyright (c) 2019, Waarp SAS, and individual contributors by the @author
    *  tags. See the COPYRIGHT.txt in the distribution for a full listing of
    * individual contributors.
    *
    *  All Waarp Project is free software: you can redistribute it and/or
    * modify it under the terms of the GNU General Public License as published by
   * the Free Software Foundation, either version 3 of the License, or (at your
   * option) any later version.
   *
   * Waarp is distributed in the hope that it will be useful, but WITHOUT ANY
   * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
   * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
   *
   *  You should have received a copy of the GNU General Public License along with
   * Waarp . If not, see <http://www.gnu.org/licenses/>.
   */
  
  package org.waarp.openr66.dao.database;
  
  import org.waarp.common.database.exception.WaarpDatabaseSqlException;
  import org.waarp.common.logging.WaarpLogger;
  import org.waarp.common.logging.WaarpLoggerFactory;
  import org.waarp.common.lru.SynchronizedLruCache;
  import org.waarp.openr66.dao.AbstractDAO;
  import org.waarp.openr66.dao.DAOFactory;
  import org.waarp.openr66.dao.Filter;
  import org.waarp.openr66.dao.database.DBDAOFactory.FakeConnection;
  import org.waarp.openr66.dao.exception.DAOConnectionException;
  import org.waarp.openr66.dao.exception.DAONoDataException;
  
  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;
  
  public abstract class StatementExecutor<E> implements AbstractDAO<E> {
  
    private static final WaarpLogger logger =
        WaarpLoggerFactory.getLogger(StatementExecutor.class);
    protected static final String WHERE = " WHERE ";
    protected static final String PARAMETER = " = ?";
    protected static final String PARAMETER_COMMA = " = ?, ";
    protected static final String SQL_COUNT_ALL_PREFIX =
        "SELECT COUNT(*) AS total FROM ";
    protected Connection connection;
  
    protected abstract boolean isDbTransfer();
  
    public abstract E getFromResultSet(ResultSet set)
        throws SQLException, DAOConnectionException;
  
    protected abstract boolean isCachedEnable();
  
    protected SynchronizedLruCache<String, E> getCache() {
      return null;
    }
  
    protected final void clearCache() {
      if (isCachedEnable()) {
        getCache().clear();
      }
    }
  
    protected final void addToCache(final String key, final E elt) {
      if (isCachedEnable()) {
        getCache().put(key, elt);
      }
    }
  
    protected final E getFromCache(final String key) {
      if (isCachedEnable()) {
        final E value = getCache().get(key);
        if (value != null) {
          getCache().updateTtl(key);
        }
        return value;
      }
      return null;
    }
  
    protected final void removeFromCache(final String key) {
      if (isCachedEnable()) {
        getCache().remove(key);
      }
    }
  
    protected final boolean isInCache(final String key) {
      if (isCachedEnable()) {
        return getCache().contains(key);
      }
      return false;
    }
  
   protected StatementExecutor(final Connection con) {
     connection = con;
   }
 
   public Connection getConnection() {
     return connection;
   }
 
   protected final void setParameters(final PreparedStatement stm,
                                      final Object... values)
       throws SQLException {
     if (values == null) {
       return;
     }
     for (int i = 0; i < values.length; i++) {
       stm.setObject(i + 1, values[i]);
     }
   }
 
   protected final void executeUpdate(final PreparedStatement stm)
       throws SQLException {
     final int res;
     res = stm.executeUpdate();
     if (res < 1) {
       logger.error("Update failed, no record updated.");
       //FIXME should be throw new SQLDataException("Update failed, no record
       // updated.");
     } else {
       logger.debug("{} records updated.", res);
     }
   }
 
   protected final int executeAction(final PreparedStatement stm)
       throws SQLException {
     return stm.executeUpdate();
   }
 
   protected final ResultSet executeQuery(final PreparedStatement stm)
       throws SQLException {
     return stm.executeQuery();
   }
 
   protected final void closeStatement(final Statement stm) {
     if (stm == null) {
       return;
     }
     try {
       stm.close();
     } catch (final SQLException e) {
       logger.warn("An error occurs while closing the statement." + " : {}",
                   e.getMessage());
     }
   }
 
   protected final void closeResultSet(final ResultSet rs) {
     if (rs == null) {
       return;
     }
     try {
       rs.close();
     } catch (final SQLException e) {
       logger.warn("An error occurs while closing the resultSet." + " : {}",
                   e.getMessage());
     }
   }
 
   public final void close() {
     try {
       connection.close();
     } catch (final SQLException e) {
       logger.warn("Cannot properly close the database connection" + " : {}",
                   e.getMessage());
     }
   }
 
   protected abstract String getId(E e1);
 
   protected abstract String getTable();
 
   protected abstract String getSelectRequest();
 
   protected abstract String getCountRequest();
 
   protected abstract String getGetAllRequest();
 
   protected abstract String getExistRequest();
 
   protected abstract Object[] getInsertValues(E e1)
       throws WaarpDatabaseSqlException;
 
   protected abstract String getInsertRequest();
 
   protected abstract Object[] getUpdateValues(E e1)
       throws WaarpDatabaseSqlException;
 
   protected abstract String getUpdateRequest();
 
   protected abstract String getDeleteRequest();
 
   protected abstract String getDeleteAllRequest();
 
   @Override
   public void delete(final E e1)
       throws DAOConnectionException, DAONoDataException {
     if (isCachedEnable()) {
       // Need to check since all does not accept getId
       removeFromCache(getId(e1));
     }
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(getDeleteRequest());
       setParameters(stm, getId(e1));
       try {
         executeAction(stm);
       } catch (final SQLException e2) {
         throw new DAONoDataException(e2);
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
   @Override
   public final void deleteAll() throws DAOConnectionException {
     clearCache();
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(getDeleteAllRequest());
       executeAction(stm);
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
   protected final String prepareDeleteQuery(final List<Filter> filters,
                                             final Object[] params) {
     final StringBuilder query = new StringBuilder(getDeleteAllRequest());
     return getFullRequest(filters, params, query);
   }
 
   private final String getFullRequest(final List<Filter> filters,
                                       final Object[] params,
                                       final StringBuilder query) {
     if (filters.isEmpty()) {
       return query.toString();
     }
     query.append(WHERE);
     String prefix = "";
     int i = 0;
     for (final Filter filter : filters) {
       query.append(prefix);
       if (filter.nbAdditionnalParams() > 1) {
         final Object[] objects = (Object[]) filter.append(query);
         for (final Object o : objects) {
           params[i++] = o;
         }
       } else if (filter.nbAdditionnalParams() == 1) {
         params[i] = filter.append(query);
         i++;
       } else {
         filter.append(query);
       }
       prefix = " AND ";
     }
     return query.toString();
   }
 
   /**
    * Delete all items according to filter
    *
    * @param filters
    *
    * @return the number of deleted items
    *
    * @throws DAOConnectionException
    */
   public final int delete(final List<Filter> filters)
       throws DAOConnectionException {
     final ArrayList<E> es = new ArrayList<E>();
     // Create the SQL query
     final Object[] params = prepareFindParams(filters);
     final String query = prepareDeleteQuery(filters, params);
     // Execute query
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(query);
       setParameters(stm, params);
       return executeAction(stm);
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
   @Override
   public final List<E> getAll() throws DAOConnectionException {
     final ArrayList<E> es = new ArrayList<E>();
     PreparedStatement stm = null;
     ResultSet res = null;
     try {
       stm = connection.prepareStatement(getGetAllRequest());
       res = executeQuery(stm);
       while (res.next()) {
         es.add(getFromResultSet(res));
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeResultSet(res);
       closeStatement(stm);
     }
     return es;
   }
 
   protected final String prepareFindQuery(final List<Filter> filters,
                                           final Object[] params) {
     final StringBuilder query = new StringBuilder(getGetAllRequest());
     return getFullRequest(filters, params, query);
   }
 
   protected final Object[] prepareFindParams(final List<Filter> filters) {
     Object[] params = new Object[0];
     if (filters != null) {
       int len = filters.size();
       for (final Filter filter : filters) {
         // Already got 1
         len += filter.nbAdditionnalParams() - 1;
       }
       params = new Object[len];
     }
     return params;
   }
 
   @Override
   public final List<E> find(final List<Filter> filters)
       throws DAOConnectionException {
     return find(filters, null, false, -1);
   }
 
   @Override
   public final List<E> find(final List<Filter> filters, final int limit)
       throws DAOConnectionException {
     return find(filters, null, false, limit);
   }
 
   @Override
   public final List<E> find(final List<Filter> filters, final String field,
                             final boolean asc) throws DAOConnectionException {
     return find(filters, field, asc, -1);
   }
 
   @Override
   public final List<E> find(final List<Filter> filters, final String field,
                             final boolean asc, final int limit)
       throws DAOConnectionException {
     return find(filters, field, asc, limit, -1);
   }
 
   @Override
   public final List<E> find(final List<Filter> filters, final String field,
                             final boolean asc, final int limit,
                             final int offset) throws DAOConnectionException {
     final ArrayList<E> es = new ArrayList<E>();
     // Create the SQL query
     final Object[] params = prepareFindParams(filters);
     final StringBuilder query =
         new StringBuilder(prepareFindQuery(filters, params));
     // Execute query
     PreparedStatement stm = null;
     ResultSet res = null;
     try {
       if (field != null) {
         query.append(" ORDER BY ").append(field);
         if (!asc) {
           query.append(" DESC ");
         }
       }
       String squery = query.toString();
       if (limit > 0 || offset > 0) {
         squery =
             DAOFactory.getInstance().getLimitRequest(squery, limit, offset);
       }
       logger.debug("DEBUG {} {} {}", squery,
                    params != null && params.length > 0? params[0] : "No first",
                    params.length >= 2? params[1] : " No 2nd");
       stm = connection.prepareStatement(squery);
       setParameters(stm, params);
       res = executeQuery(stm);
       while (res.next()) {
         es.add(getFromResultSet(res));
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeResultSet(res);
       closeStatement(stm);
     }
     return es;
   }
 
   @Override
   public final void update(final List<Filter> filters, final String fieldToSet)
       throws DAOConnectionException {
     final ArrayList<E> es = new ArrayList<E>();
     // Create the SQL query
     final Object[] params = prepareFindParams(filters);
     if (isCachedEnable()) {
       final String query = prepareFindQuery(filters, params);
       // Execute query
       PreparedStatement stm = null;
       ResultSet res = null;
       try {
         stm = connection.prepareStatement(query);
         setParameters(stm, params);
         res = executeQuery(stm);
         while (res.next()) {
           final E e = getFromResultSet(res);
           removeFromCache(getId(e));
         }
       } catch (final SQLException e) {
         throw new DAOConnectionException(e);
       } finally {
         closeResultSet(res);
         closeStatement(stm);
       }
     }
     final StringBuilder query = new StringBuilder("UPDATE " + getTable());
     query.append(" SET ").append(fieldToSet);
     final String squery = getFullRequest(filters, params, query);
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(squery);
       setParameters(stm, params);
       executeUpdate(stm);
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
   protected final String prepareCountQuery(final List<Filter> filters,
                                            final Object[] params) {
     final StringBuilder query = new StringBuilder(getCountRequest());
     return getFullRequest(filters, params, query);
   }
 
   @Override
   public final long count(final List<Filter> filters)
       throws DAOConnectionException {
     // Create the SQL query
     final Object[] params = prepareFindParams(filters);
     final String query = prepareCountQuery(filters, params);
     // Execute query
     PreparedStatement stm = null;
     ResultSet res = null;
     long total = -1;
     try {
       stm = connection.prepareStatement(query);
       setParameters(stm, params);
       res = executeQuery(stm);
       if (res.next()) {
         total = res.getLong("total");
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeResultSet(res);
       closeStatement(stm);
     }
     if (total < 0) {
       throw new DAOConnectionException("Count cannot be retrieved");
     }
     return total;
   }
 
   @Override
   public final boolean exist(final String id) throws DAOConnectionException {
     if (isDbTransfer()) {
       throw new UnsupportedOperationException();
     }
     if (isInCache(id)) {
       return true;
     }
     if (connection instanceof FakeConnection) {
       try {
         connection = ((FakeConnection) connection).getRealConnection();
       } catch (final SQLException throwables) {
         throw new DAOConnectionException(throwables);
       }
     }
     PreparedStatement stm = null;
     ResultSet res = null;
     try {
       stm = connection.prepareStatement(getExistRequest());
       setParameters(stm, id);
       res = executeQuery(stm);
       return res.next();
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeResultSet(res);
       closeStatement(stm);
     }
   }
 
   @Override
   public final E select(final String id)
       throws DAOConnectionException, DAONoDataException {
     if (isDbTransfer()) {
       throw new UnsupportedOperationException();
     }
     if (isCachedEnable()) {
       final E found = getFromCache(id);
       if (found != null) {
         return found;
       }
     }
     if (connection instanceof FakeConnection) {
       try {
         connection = ((FakeConnection) connection).getRealConnection();
       } catch (final SQLException throwables) {
         throw new DAOConnectionException(throwables);
       }
     }
     PreparedStatement stm = null;
     ResultSet res = null;
     try {
       stm = connection.prepareStatement(getSelectRequest());
       setParameters(stm, id);
       res = executeQuery(stm);
       if (res.next()) {
         final E found = getFromResultSet(res);
         addToCache(id, found);
         return found;
       } else {
         throw new DAONoDataException("No " + getClass().getName() + " found");
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeResultSet(res);
       closeStatement(stm);
     }
   }
 
   @Override
   public void insert(final E e1) throws DAOConnectionException {
     if (isCachedEnable()) {
       // Need to check since all does not accept getId
       addToCache(getId(e1), e1);
     }
     final Object[] params;
     try {
       params = getInsertValues(e1);
     } catch (final WaarpDatabaseSqlException e) {
       throw new DAOConnectionException(e);
     }
 
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(getInsertRequest());
       setParameters(stm, params);
       executeUpdate(stm);
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
   @Override
   public final void update(final E e1)
       throws DAOConnectionException, DAONoDataException {
     if (isCachedEnable()) {
       // Need to check since all does not accept getId
       addToCache(getId(e1), e1);
     }
     final Object[] params;
     try {
       params = getUpdateValues(e1);
     } catch (final WaarpDatabaseSqlException e) {
       throw new DAOConnectionException(e);
     }
 
     PreparedStatement stm = null;
     try {
       stm = connection.prepareStatement(getUpdateRequest());
       setParameters(stm, params);
       try {
         executeUpdate(stm);
       } catch (final SQLException e2) {
         throw new DAONoDataException(e2);
       }
     } catch (final SQLException e) {
       throw new DAOConnectionException(e);
     } finally {
       closeStatement(stm);
     }
   }
 
 }