/*
    * 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.common.database;
  
  import org.waarp.common.database.exception.WaarpDatabaseNoConnectionException;
  import org.waarp.common.database.exception.WaarpDatabaseNoDataException;
  import org.waarp.common.database.exception.WaarpDatabaseSqlException;
  import org.waarp.common.logging.WaarpLogger;
  import org.waarp.common.logging.WaarpLoggerFactory;
  
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  /**
   * Class to handle request
   */
  public class DbRequest {
    /**
     * Internal Logger
     */
    private static final WaarpLogger logger =
        WaarpLoggerFactory.getLogger(DbRequest.class);
    private static final String SQL_EXCEPTION_REQUEST = "SQL Exception Request:";
    private static final String SQL_EXCEPTION_REQUEST1 = "SQL Exception Request:";
  
    /**
     * Internal Statement
     */
    private Statement stmt;
  
    /**
     * Internal Result Set
     */
    private ResultSet rs;
  
    /**
     * Internal DB Session
     */
    private final DbSession ls;
  
    /**
     * Create a new request from the DbSession
     *
     * @param ls
     *
     * @throws WaarpDatabaseNoConnectionException
     */
    public DbRequest(final DbSession ls)
        throws WaarpDatabaseNoConnectionException {
      if (ls.isDisActive()) {
        ls.checkConnection();
      }
      this.ls = ls;
    }
  
    /**
     * Create a statement with some particular options
     *
     * @return the new Statement
     *
     * @throws WaarpDatabaseNoConnectionException
     * @throws WaarpDatabaseSqlException
     */
    private Statement createStatement()
        throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
      if (ls == null) {
        throw new WaarpDatabaseNoConnectionException("No connection");
      }
      if (ls.getConn() == null) {
        throw new WaarpDatabaseNoConnectionException("No connection");
      }
      if (ls.isDisActive()) {
        ls.checkConnection();
      }
      try {
        return ls.getConn().createStatement();
      } catch (final SQLException e) {
        ls.checkConnection();
        try {
          return ls.getConn().createStatement();
        } catch (final SQLException e1) {
         throw new WaarpDatabaseSqlException("Error while Create Statement", e);
       }
     }
   }
 
   /**
    * Execute a SELECT statement and set of Result. The statement must not be
    * an
    * update/insert/delete. The
    * previous statement and resultSet are closed.
    *
    * @param select
    *
    * @throws WaarpDatabaseSqlException
    * @throws WaarpDatabaseNoConnectionException
    */
   public final void select(final String select)
       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
     close();
     stmt = createStatement();
     // or alternatively, if you don't know ahead of time that
     // the query will be a SELECT...
     try {
       if (stmt.execute(select)) {
         rs = stmt.getResultSet();
       }
     } catch (final SQLException e) {
       logger.error(SQL_EXCEPTION_REQUEST + select + ' ' + e.getMessage());
       DbConstant.error(e);
       ls.checkConnectionNoException();
       throw new WaarpDatabaseSqlException(SQL_EXCEPTION_REQUEST1 + select, e);
     }
   }
 
   /**
    * Execute a SELECT statement and set of Result. The statement must not be
    * an
    * update/insert/delete. The
    * previous statement and resultSet are closed. The timeout is applied if >
    * 0.
    *
    * @param select
    * @param timeout in seconds
    *
    * @throws WaarpDatabaseSqlException
    * @throws WaarpDatabaseNoConnectionException
    */
   public final void select(final String select, final int timeout)
       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
     close();
     stmt = createStatement();
     if (timeout > 0) {
       try {
         stmt.setQueryTimeout(timeout);
       } catch (final SQLException e1) {
         // ignore
       }
     }
     // or alternatively, if you don't know ahead of time that
     // the query will be a SELECT...
     try {
       if (stmt.execute(select)) {
         rs = stmt.getResultSet();
       }
     } catch (final SQLException e) {
       logger.error(SQL_EXCEPTION_REQUEST1 + select + ' ' + e.getMessage());
       DbConstant.error(e);
       ls.checkConnectionNoException();
       throw new WaarpDatabaseSqlException(SQL_EXCEPTION_REQUEST1 + select, e);
     }
   }
 
   /**
    * Execute a UPDATE/INSERT/DELETE statement and returns the number of row.
    * The
    * previous statement and
    * resultSet are closed.
    *
    * @param query
    *
    * @return the number of row in the query
    *
    * @throws WaarpDatabaseSqlException
    * @throws WaarpDatabaseNoConnectionException
    */
   public final int query(final String query)
       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
     close();
     stmt = createStatement();
     try {
       final int rowcount = stmt.executeUpdate(query);
       logger.debug("QUERY({}): {}", rowcount, query);
       return rowcount;
     } catch (final SQLException e) {
       logger.error(SQL_EXCEPTION_REQUEST1 + query + ' ' + e.getMessage());
       DbConstant.error(e);
       ls.checkConnectionNoException();
       throw new WaarpDatabaseSqlException(SQL_EXCEPTION_REQUEST1 + query, e);
     }
   }
 
   /**
    * Finished a Request (ready for a new one)
    */
   public final void close() {
     // it is a good idea to release
     // resources in a finally{} block
     // in reverse-order of their creation
     // if they are no-longer needed
     if (rs != null) {
       try {
         rs.close();
       } catch (final SQLException sqlEx) {
         ls.checkConnectionNoException();
       } // ignore
       rs = null;
     }
     if (stmt != null) {
       try {
         stmt.close();
       } catch (final SQLException sqlEx) {
         ls.checkConnectionNoException();
       } // ignore
       stmt = null;
     }
   }
 
   /**
    * Get the last ID autoincrement from the last request
    *
    * @return the long Id or DbConstant.ILLEGALVALUE (Long.MIN_VALUE) if an
    *     error
    *     occurs.
    *
    * @throws WaarpDatabaseNoDataException
    */
   public final long getLastId() throws WaarpDatabaseNoDataException {
     ResultSet rstmp = null;
     long result = DbConstant.ILLEGALVALUE;
     try {
       rstmp = stmt.getGeneratedKeys();
       if (rstmp.next()) {
         result = rstmp.getLong(1);
       }
     } catch (final SQLException e) {
       DbConstant.error(e);
       ls.checkConnectionNoException();
       throw new WaarpDatabaseNoDataException("No data found", e);
     } finally {
       if (rstmp != null) {
         try {
           rstmp.close();
         } catch (final SQLException e) {
           // nothing
         }
       }
     }
     return result;
   }
 
   /**
    * Move the cursor to the next result
    *
    * @return True if there is a next result, else False
    *
    * @throws WaarpDatabaseNoConnectionException
    * @throws WaarpDatabaseSqlException
    */
   public final boolean getNext()
       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
     if (rs == null) {
       logger.error("SQL ResultSet is Null into getNext");
       throw new WaarpDatabaseNoConnectionException(
           "SQL ResultSet is Null into getNext");
     }
     if (ls.isDisActive()) {
       ls.checkConnection();
       throw new WaarpDatabaseSqlException(
           "Request cannot be executed since connection was recreated between");
     }
     try {
       return rs.next();
     } catch (final SQLException e) {
       logger.warn("SQL Exception to getNextRow" + ' ' + e.getMessage());
       DbConstant.error(e);
       ls.checkConnectionNoException();
       throw new WaarpDatabaseSqlException("SQL Exception to getNextRow", e);
     }
   }
 
   /**
    * @return The resultSet (can be used in conjunction of getNext())
    *
    * @throws WaarpDatabaseNoConnectionException
    */
   public final ResultSet getResultSet()
       throws WaarpDatabaseNoConnectionException {
     if (rs == null) {
       throw new WaarpDatabaseNoConnectionException(
           "SQL ResultSet is Null into getResultSet");
     }
     return rs;
   }
 
   /**
    * Test if value is null and create the string for insert/update
    *
    * @param value
    *
    * @return the string as result
    */
   public static String getIsNull(final String value) {
     return value == null? " is NULL" : " = '" + value + '\'';
   }
 }