package com.hfg.sql.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.hfg.datetime.DateUtil;
import com.hfg.security.LoginCredentials;
import com.hfg.sql.SQLUtil;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;

//------------------------------------------------------------------------------
/**
 A JDBC-compatible connection pool.
 <div>
 @author J. Alex Taylor, hairyfatguy.com
 </div>
 */
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library 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
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// jataylor@hairyfatguy.com
//------------------------------------------------------------------------------

public abstract class JDBCConnectionPool<T extends JDBCConnectionPoolSettings> extends JDBCDataSource<T>
{
   private List<PooledJDBCConnection> mPooledConnections;
   private Thread mIdleConnReaperThread;
   private int mNumConnectionRequests = 0;
   private boolean mIsActive = true;
   private Set<String> mBorrowerClassesToIgnore;

   private Integer mDatabaseMajorVersion;
   private Integer mDatabaseMinorVersion;
   private String  mDatabaseVersionString;
   
   private final static Logger LOGGER = Logger.getLogger(JDBCConnectionPool.class.getName());

   static
   {
      LOGGER.setLevel(Level.INFO);
   }

   //###########################################################################
   // CONSTRUCTORS
   //###########################################################################

   //---------------------------------------------------------------------------
   public JDBCConnectionPool(JDBCServer inServer, String inDatabaseName)
   {
      super(inServer, inDatabaseName);
   }

   //---------------------------------------------------------------------------
   public JDBCConnectionPool(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials)
   {
      super(inServer, inDatabaseName);
      setCredentials(inCredentials);
   }


   //###########################################################################
   // PUBLIC METHODS
   //###########################################################################

   //---------------------------------------------------------------------------
   public synchronized JDBCConnection getConnection()
      throws SQLException, NoAvailableConnectionException
   {
      JDBCConnection conn = null;

      long startTime = System.currentTimeMillis();

      StackTraceElement borrower = getBorrower();

      while (null == conn)
      {
         try
         {
            conn = getPoolConnection(borrower);
         }
         catch (NoAvailableConnectionException e)
         {
            if (getSettings().getAvailabilityTimeoutSec() != null
                  && System.currentTimeMillis() - startTime > (getSettings().getAvailabilityTimeoutSec() * 1000))
            {
               throw e;
            }
            else
            {
               try
               {
                  Thread.sleep(250);
               }
               catch (InterruptedException e2)
               {
                  // Ignore
               }
            }
         }
      }

      mNumConnectionRequests++;

      return conn;
   }

   //---------------------------------------------------------------------------
   public synchronized void destroy()
      throws SQLException
   {
      mIsActive = false;

      if (CollectionUtil.hasValues(mPooledConnections))
      {
         for (PooledJDBCConnection conn : mPooledConnections)
         {
            conn.destroy();
         }

         mPooledConnections.clear();
      }

      mIdleConnReaperThread.interrupt();
   }

   //---------------------------------------------------------------------------
   /**
    Specifies a class to be ignored when tracking connection lending. When examining
    the stacktrace to see who is requesting a connection, any elements from this class
    will be skipped and a subsequent element will be used to identify the borrower.
    * @param inValue a class to be ignored when tracking connection lending
    * @return this connection pool object to enable method chaining
    */
   public JDBCConnectionPool addIgnorableBorrowerClass(Class inValue)
   {
      if (inValue != null)
      {
         if (null == mBorrowerClassesToIgnore)
         {
            mBorrowerClassesToIgnore = new HashSet<>(5);
         }

         mBorrowerClassesToIgnore.add(inValue.getName());
      }

      return this;
   }

   //---------------------------------------------------------------------------
   public int getDatabaseMajorVersion()
         throws SQLException
   {
      if (null == mDatabaseMajorVersion)
      {
         Connection conn = null;
         try
         {
            conn = getConnection();

            DatabaseMetaData meta = conn.getMetaData();
            // Oracle (and some other vendors) do not support
            // some the following methods; therefore, we need
            // to use try-catch block.
            try 
            {
               mDatabaseMajorVersion = meta.getDatabaseMajorVersion();
            } 
            catch (Exception e) 
            {
               System.err.println("Database major version: unsupported feature");
            }
         }
         finally
         {
            SQLUtil.close(conn);
         }

      }

      return mDatabaseMajorVersion;
   }

   //---------------------------------------------------------------------------
   public int getDatabaseMinorVersion()
         throws SQLException
   {
      if (null == mDatabaseMinorVersion)
      {
         Connection conn = null;
         try
         {
            conn = getConnection();

            DatabaseMetaData meta = conn.getMetaData();
            // Oracle (and some other vendors) do not support
            // some the following methods; therefore, we need
            // to use try-catch block.
            try 
            {
               mDatabaseMinorVersion = meta.getDatabaseMinorVersion();
            } 
            catch (Exception e) 
            {
               System.err.println("Database minor version: unsupported feature");
            }
         }
         finally
         {
            SQLUtil.close(conn);
         }

      }

      return mDatabaseMajorVersion;
   }

   //---------------------------------------------------------------------------
   public String getDatabaseVersionString()
   {
      if (null == mDatabaseVersionString)
      {
         Connection conn = null;
         try
         {
            conn = getConnection();

            DatabaseMetaData meta = conn.getMetaData();
            mDatabaseVersionString = meta.getDatabaseProductVersion();
         }
         catch (SQLException e)
         {
            throw new JDBCException("Error retrieving database version string!", e);
         }
         finally
         {
            SQLUtil.close(conn);
         }

      }

      return mDatabaseVersionString;
   }
   
   //---------------------------------------------------------------------------
   public String getStatusReport()
   {
      StringBuilderPlus buffer = new StringBuilderPlus()
            .appendln("Connection Pool Status Report " + DateUtil.getYYYY_MM_DD_HH_mm_ss(new Date()))
            .appendln()
            .appendln(String.format("  %s %s", getServer().getRDBMS().name(), getDatabaseVersionString()))
            .appendln("  Server: " + getServer().getConnectString(getDatabaseName()))
            .appendln("  Num Connection Requests: " + mNumConnectionRequests)
            .appendln(String.format("  Connections (Min/Max/Avail.): %d / %d / %d",
                      getSettings().getMinConnections(), getSettings().getMaxConnections(), getNumAvailableConnections()));

      StringBuilderPlus activeConnBuffer = new StringBuilderPlus();
      if (CollectionUtil.hasValues(mPooledConnections))
      {
         for (int i = 0; i < mPooledConnections.size(); i++)
         {
            PooledJDBCConnection poolConn = mPooledConnections.get(i);

            if (! poolConn.isAvailable())
            {
               activeConnBuffer.appendln(String.format("    %2d. %s [%s]", (i + 1), poolConn.getBorrower().toString(), DateUtil.generateElapsedTimeString(poolConn.getCheckoutTime())));
            }
         }
      }
      
      if (activeConnBuffer.length() > 0)
      {
         buffer.appendln("  Active Connections:")
               .append(activeConnBuffer);
      }
      else 
      {
         buffer.appendln("  No Active Connections.");
      }

      return buffer.toString();
   }

   //---------------------------------------------------------------------------
   public static Logger getLogger()
   {
      return LOGGER;
   }

   //###########################################################################
   // PROTECTED METHODS
   //###########################################################################

   //---------------------------------------------------------------------------
   protected JDBCConnection getPoolConnection(StackTraceElement inBorrower)
         throws SQLException, NoAvailableConnectionException
   {
      PooledJDBCConnection conn = null;

      if (null == mPooledConnections)
      {
         initializePool();
      }

      for (int i = 0; i < mPooledConnections.size(); i++)
      {
         PooledJDBCConnection poolConn = mPooledConnections.get(i);

         if (poolConn.isAvailable())
         {
            try
            {
               conn = poolConn.checkout(inBorrower);
               if (null == conn)
               {  // The connection must not be valid anymore
                  poolConn.destroy();
                  mPooledConnections.remove(i--);
               }
               else
               {
                  // Connection is good to go
                  break;
               }
            }
            catch (Exception e)
            {
               e.printStackTrace();
               poolConn.destroy();
               mPooledConnections.remove(i--);
            }
         }
      }

      if (null == conn)
      {
         // No available connections were found in the pool. Can we create a new one?
         if (mPooledConnections.size() < getSettings().getMaxConnections())
         {
            conn = new PooledJDBCConnection(createConnection());
            LOGGER.fine("Creating new connection: " + conn.name());
            conn = conn.checkout(inBorrower);
            if (conn != null)
            {
               mPooledConnections.add(conn);
            }
         }
         else
         {
            throw new NoAvailableConnectionException();
         }
      }

      return conn;
   }

   //---------------------------------------------------------------------------
   protected T initSettings()
   {
      return (T) new JDBCConnectionPoolSettings();
   }

   //---------------------------------------------------------------------------
   protected abstract JDBCConnection createConnection();

   //###########################################################################
   // PRIVATE METHODS
   //###########################################################################

   //---------------------------------------------------------------------------
   private void initializePool()
   {
      mPooledConnections = new ArrayList<>(getSettings().getMaxConnections());

      for (int i = 0; i < getSettings().getMinConnections(); i++)
      {
         JDBCConnection conn = createConnection();

         mPooledConnections.add(new PooledJDBCConnection(conn));
      }

      mIdleConnReaperThread = new Thread(new IdleConnReaper());
      mIdleConnReaperThread.setDaemon(true);
      mIdleConnReaperThread.setPriority(Thread.MIN_PRIORITY);
      mIdleConnReaperThread.setName(generateIdleRepearThreadName());
      mIdleConnReaperThread.start();
   }


   //---------------------------------------------------------------------------
   private String generateIdleRepearThreadName()
   {
      StringBuilderPlus name = new StringBuilderPlus().setDelimiter(" ");
      if (StringUtil.isSet(name()))
      {
         name.append(name());
      }

      if (StringUtil.isSet(getServer().name()))
      {
         name.delimitedAppend(getServer().name());
      }

      if (StringUtil.isSet(getDatabaseName()))
      {
         name.delimitedAppend(getDatabaseName());
      }

      name.delimitedAppend(" Idle Conn Reaper");

      return name.toString();
   }

   //---------------------------------------------------------------------------
   private StackTraceElement getBorrower()
   {
      StackTraceElement borrower = null;

      StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
      for (int i = 3; i < stackTrace.length; i++)
      {
         StackTraceElement stackTraceElement = stackTrace[i];
         if (! CollectionUtil.hasValues(mBorrowerClassesToIgnore)
             || ! mBorrowerClassesToIgnore.contains(stackTraceElement.getClassName()))
         {
            borrower = stackTraceElement;
            break;
         }
      }

      return borrower;
   }

   //---------------------------------------------------------------------------
   private synchronized void removeConnectionFromPool(PooledJDBCConnection inConn)
   {
      mPooledConnections.remove(inConn);
   }

   //---------------------------------------------------------------------------
   private synchronized void removeIdledConnections()
      throws SQLException
   {
      if (poolContainsMoreThanTheMinNumOfConnections())
      {
         for (int i = 0; i < getSettings().getMinConnections(); i++)
         {
            PooledJDBCConnection poolConn = mPooledConnections.get(i);
            if (poolConn.isAvailable()
                && poolConn.getIdleTimeSec() > getSettings().getMaxIdleTimeSec())
            {
               poolConn.destroy();
               mPooledConnections.remove(i--);

               if (! poolContainsMoreThanTheMinNumOfConnections())
               {
                  break;
               }
            }
         }
      }
   }

   //---------------------------------------------------------------------------
   private boolean poolContainsMoreThanTheMinNumOfConnections()
   {
      return (mPooledConnections.size() > 0
              && (null == getSettings().getMinConnections()
                  || mPooledConnections.size() > getSettings().getMinConnections()));
   }

   //---------------------------------------------------------------------------
   private int getNumAvailableConnections()
   {
      int numAvailableConns = 0;

      if (mPooledConnections != null)
      {
         for (int i = 0; i < mPooledConnections.size(); i++)
         {
            PooledJDBCConnection poolConn = mPooledConnections.get(i);

            if (poolConn.isAvailable())
            {
               numAvailableConns++;
            }
         }
      }

      return numAvailableConns;
   }

   //###########################################################################
   // INNER CLASS
   //###########################################################################

   private class PooledJDBCConnection extends JDBCConnection
   {
      private boolean           mIsAvailable;
      private Long              mCheckoutTime;
      private StackTraceElement mBorrower;
      private Long              mIdleStartTime;

      //------------------------------------------------------------------------
      public PooledJDBCConnection(JDBCConnection inConn)
      {
         super(inConn);
         initPooledConnection();
      }

      //------------------------------------------------------------------------
      private void initPooledConnection()
      {
         mCheckoutTime  = null;
         mBorrower      = null;
         mIdleStartTime = System.currentTimeMillis();
         mIsAvailable   = true;
      }

      //------------------------------------------------------------------------
      public synchronized boolean isAvailable()
      {
         return mIsAvailable;
      }

      //------------------------------------------------------------------------
      public synchronized PooledJDBCConnection checkout(StackTraceElement inBorrower)
         throws SQLException
      {
         mIsAvailable   = false;
         mCheckoutTime  = System.currentTimeMillis();
         mBorrower      = inBorrower;
         mIdleStartTime = null;

         if (! getAutoCommit())
         {
            setAutoCommit(true);
         }
         
         PooledJDBCConnection conn = this;

         if (getServer().getRDBMS().getConnTestQuery() != null)
         {
            try
            {
               execute(getServer().getRDBMS().getConnTestQuery().toSQL());
            }
            catch (JDBCException e)
            {
               conn = null;
            }
         }

         if (conn != null)
         {
            LOGGER.fine(name() + " checked out to " + inBorrower);
         }

         return conn;
      }

      //------------------------------------------------------------------------
      public Long getCheckoutTime()
      {
         return mCheckoutTime;
      }

      //------------------------------------------------------------------------
      public StackTraceElement getBorrower()
      {
         return mBorrower;
      }

      //------------------------------------------------------------------------
      private int getIdleTimeSec()
      {
         int idleTimeSec = 0;
         if (mIdleStartTime != null)
         {
            idleTimeSec = (int) (System.currentTimeMillis() - mIdleStartTime)/1000;
         }

         return idleTimeSec;
      }

      //------------------------------------------------------------------------
      // Don't actually close the underlying connection. It goes back into the pool.
      public void close()
         throws SQLException
      {
         LOGGER.fine(name() + " returned from " + mBorrower);

         if (isClosed())
         {
            removeConnectionFromPool(this);
         }
         else
         {
            clean();

            initPooledConnection();
         }
      }

      //------------------------------------------------------------------------
      public void destroy()
         throws SQLException
      {
         if (! isClosed())
         {
            unwrap(Connection.class).close();
         }
      }

      //------------------------------------------------------------------------
      private void clean()
         throws SQLException
      {
         if (! getAutoCommit())
         {
            boolean returnedInUncommittedState = false;
            try
            {
               setAutoCommit(true);
            }
            catch (SQLException e)
            {
               returnedInUncommittedState = true;

               // Try rolling back the transaction.
               try
               {
                  rollback();
                  setAutoCommit(true);
               }
               catch (SQLException e2)
               {
                  destroy();
                  removeConnectionFromPool(this);
               }
            }

            if (returnedInUncommittedState)
            {
               throw new JDBCException("Connection returned in an uncommitted state!");
            }
         }
      }


      //------------------------------------------------------------------------
      @Override
      public String getCurrentUser()
            throws SQLException
      {
         return ((JDBCConnection) getInnerConnection()).getCurrentUser();
      }

   }

   //###########################################################################
   // INNER CLASS
   //###########################################################################

   private class IdleConnReaper implements Runnable
   {
      private int mExecutionFreqSec = 60;

      //------------------------------------------------------------------------
      public void run()
      {
         while (mIsActive)
         {
            try
            {
               Thread.sleep(mExecutionFreqSec * 1000);
            }
            catch (InterruptedException e2)
            {
               // Ignore
            }

            try
            {
               removeIdledConnections();
            }
            catch (Exception e)
            {
               // Ignore
               e.printStackTrace();
            }
         }
      }

   }
}