package com.hfg.sql;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.hfg.datetime.DateUtil;
import com.hfg.sql.jdbc.JDBCException;
import com.hfg.sql.jdbc.JDBCResultSet;
import com.hfg.sql.jdbc.SQLStatementOptions;
import com.hfg.sql.table.DatabaseCol;
import com.hfg.sql.table.DatabaseTable;
import com.hfg.util.StackTraceElementFilter;
import com.hfg.util.StackTraceUtil;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.CollectionUtil;

//------------------------------------------------------------------------------
/**
 SQL utility functions.
 <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 class SQLUtil
{
   public static Level sSQLLoggingLevel = Level.FINE;

   private final static Logger LOGGER = Logger.getLogger(SQLUtil.class.getName());

   private static List<StackTraceElementFilter> sExclusions = new ArrayList<>(4);

   static
   {
      LOGGER.setUseParentHandlers(false);

      sExclusions.add(new StackTraceElementFilter().setPackage(SQLUtil.class.getPackage()));
      sExclusions.add(new StackTraceElementFilter().setPackage(DatabaseTable.class.getPackage()));
   }

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

   //---------------------------------------------------------------------------
   public static String sqlString(Object inValue)
   {
      return inValue != null ? sqlString(inValue.toString()) : "null";
   }

   //---------------------------------------------------------------------------
   public static String sqlString(String inValue)
   {
      return inValue != null ? "'" + StringUtil.replaceAll(inValue, "\'", "\'\'") + "'" : "null";
   }

   //---------------------------------------------------------------------------
   /**
    All single and double quotes internal to the string are doubled for escaping.
    @param inValue the value string to be escaped for single and double quotes
    @return the escaped value
    */
   public static String sqlStringWithDoubleQuotes(String inValue)
   {
      return inValue != null ? "\"" + StringUtil.replaceAll(StringUtil.replaceAll(inValue, "\"", "\"\""), "\'", "\'\'") + "\"" : "null";
   }

   //---------------------------------------------------------------------------
   public static String sqlStringList(Collection<? extends Object> inValues)
   {
      String listString = "";

      if (CollectionUtil.hasValues(inValues))
      {
         List<String> sqlStrings = new ArrayList<>(inValues.size());
         for (Object object : inValues)
         {
            sqlStrings.add(sqlString(object));
         }

         listString = StringUtil.join(sqlStrings, ", ");
      }

      return listString;
   }

   //---------------------------------------------------------------------------
   public static boolean execute(Connection inConn, String inSQL)
         throws SQLException
   {
      boolean result;

      Statement stmt = null;
      try
      {
         stmt = inConn.createStatement();
         result = execute(stmt, inSQL);
      }
      finally
      {
         SQLUtil.close(stmt);
      }

      return result;
   }

   //---------------------------------------------------------------------------
   public static boolean execute(PreparedStatement inPreparedStatement)
         throws JDBCException
   {
      return execute(inPreparedStatement, null);
   }

   //---------------------------------------------------------------------------
   public static boolean execute(Statement inStatement, String inSQL)
         throws JDBCException
   {
      boolean result;

      long startTime = System.currentTimeMillis();

      try
      {
         if (inStatement instanceof PreparedStatement)
         {
            PreparedStatement ps = (PreparedStatement) inStatement;
            inSQL = ps.toString();
            result = ((PreparedStatement) inStatement).execute();
         }
         else
         {
            result = inStatement.execute(inSQL);
         }
      }
      catch (Exception e)
      {
         throw new JDBCException("Error executing SQL:\n" + inSQL, e);
      }

      if (LOGGER.isLoggable(sSQLLoggingLevel))
      {
         // Generate debugging info w/ the SQL run and its execution time
         StringBuilderPlus msg = new StringBuilderPlus()
               .appendln("[" + StackTraceUtil.getCallingStackTraceElement(sExclusions) + "] SQL:\n")
               .appendln(inSQL)
               .appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));
         
         LOGGER.log(sSQLLoggingLevel, msg.toString());
      }

      return result;
   }

   //---------------------------------------------------------------------------
   public static int executeUpdate(Connection inConn, String inSQL)
         throws SQLException
   {
      int rowsUpdated;

      Statement stmt = null;
      try
      {
         stmt = inConn.createStatement();
         rowsUpdated = executeUpdate(stmt, inSQL);
      }
      finally
      {
         SQLUtil.close(stmt);
      }

      return rowsUpdated;
   }

   //---------------------------------------------------------------------------
   public static int executeUpdate(PreparedStatement inPreparedStatement)
         throws SQLException
   {
      return executeUpdate(inPreparedStatement, null);
   }

   //---------------------------------------------------------------------------
   public static int executeUpdate(Statement inStatement, CharSequence inSQL)
         throws SQLException
   {
      int result;

      long startTime = System.currentTimeMillis();

      try
      {
         if (inStatement instanceof PreparedStatement)
         {
            PreparedStatement ps = (PreparedStatement) inStatement;
            inSQL = ps.toString();
            result = ((PreparedStatement) inStatement).executeUpdate();
         }
         else
         {
            result = inStatement.executeUpdate(inSQL.toString());
         }
      }
      catch (Exception e)
      {
         throw new JDBCException("Error executing SQL:\n" + inSQL, e);
      }

      if (LOGGER.isLoggable(sSQLLoggingLevel))
      {
         // Generate debugging info w/ the SQL run and its execution time
         StringBuilderPlus msg = new StringBuilderPlus()
               .appendln("[" + StackTraceUtil.getCallingStackTraceElement(sExclusions) + "] SQL:")
               .appendln(inSQL)
               .appendln("Rows " + (isDelete(inSQL) ? "Deleted" : "Updated") + ": " + result)
               .appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));

         LOGGER.log(sSQLLoggingLevel, msg.toString());
      }

      return result;
   }

   //---------------------------------------------------------------------------
   public static ResultSet executeQuery(Connection inConn, String inSQL)
         throws SQLException
   {
      return executeQuery(inConn, inSQL, null);
   }

   //---------------------------------------------------------------------------
   public static ResultSet executeQuery(Connection inConn, String inSQL, SQLStatementOptions inOptions)
         throws SQLException
   {
      Statement stmt = null;
      ResultSet rs;

      try
      {
         stmt = inConn.createStatement();

         if (inOptions != null)
         {
            inOptions.configureStmt(stmt);
         }

         rs = executeQuery(stmt, inSQL);
      }
      catch (SQLException e)
      {
         close(stmt);
         throw e;
      }

      return rs;
   }

   //---------------------------------------------------------------------------
   public static int executeQuery(PreparedStatement inPreparedStatement)
         throws SQLException
   {
      return executeUpdate(inPreparedStatement, null);
   }

   //---------------------------------------------------------------------------
   public static ResultSet executeQuery(Statement inStatement, String inSQL)
         throws JDBCException
   {
      ResultSet rs;

      long startTime = System.currentTimeMillis();

      try
      {
         if (inStatement instanceof PreparedStatement)
         {
            PreparedStatement ps = (PreparedStatement) inStatement;
            inSQL = ps.toString();
            rs = ((PreparedStatement) inStatement).executeQuery();
         }
         else
         {
            rs = inStatement.executeQuery(inSQL);
         }
      }
      catch (Exception e)
      {
         throw new JDBCException("Error executing SQL:\n" + inSQL, e);
      }

      if (LOGGER.isLoggable(sSQLLoggingLevel))
      {
         // Generate debugging info w/ the SQL run and its execution time
         StringBuilderPlus msg = new StringBuilderPlus()
               .appendln("[" + StackTraceUtil.getCallingStackTraceElement(sExclusions) + "] SQL:\n")
               .appendln(inSQL)
               .appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));

         LOGGER.log(sSQLLoggingLevel, msg.toString());
      }

      return new JDBCResultSet(rs);
   }

   //---------------------------------------------------------------------------
   public static void closeResultSetAndStatement(ResultSet inResultSet)
      throws JDBCException
   {
      try
      {
         if (inResultSet != null)
         {
            Statement stmt = inResultSet.getStatement();
            stmt.close();
            inResultSet.close();
         }
      } catch (SQLException e)
      {
         throw new JDBCException(e);
      }
   }

   //---------------------------------------------------------------------------
   public static void close(ResultSet inResultSet)
      throws JDBCException
   {
      try
      {
         if (inResultSet != null)
         {
            inResultSet.close();
         }
      } catch (SQLException e)
      {
         throw new JDBCException(e);
      }
   }

   //---------------------------------------------------------------------------
   public static void close(Statement inStmt)
      throws JDBCException
   {
      try
      {
         if (inStmt != null)
         {
            inStmt.close();
         }
      }
      catch (SQLException e)
      {
         throw new JDBCException(e);
      }
   }

   //---------------------------------------------------------------------------
   public static void close(Connection inConn)
      throws JDBCException
   {
      try
      {
         if (inConn != null)
         {
            inConn.close();
         }
      }
      catch (SQLException e)
      {
         throw new JDBCException(e);
      }
   }

   //---------------------------------------------------------------------------
   public static List<String> splitBatchSQL(Reader inSQL)
      throws IOException
   {
      List<String> sqlCommands = new ArrayList<String>();

      BufferedReader bufferedReader;
      if (inSQL instanceof BufferedReader)
      {
         bufferedReader = (BufferedReader) inSQL;
      }
      else
      {
         bufferedReader = new BufferedReader(inSQL);
      }

      StringBuilderPlus currentSQLCommand = new StringBuilderPlus();

      // In-line SQL comments start with two dashes and continue to the end of the line. Ex: '-- my comment'
      // Block SQL comments look like block comments in C or Java. Ex: '/* my comment */'
      boolean inInlineComment = false;
      boolean inBlockComment  = false;
      boolean inQuotedContent = false;

      String line;
      while ((line = bufferedReader.readLine()) != null)
      {
         inInlineComment = false;
         char[] lineChars = line.toCharArray();

         for (int i = 0; i < line.length(); i++)
         {
            char theChar = lineChars[i];
            if (theChar == ';'
                && ! inInlineComment
                && ! inBlockComment
                && ! inQuotedContent)
            {
               String sql = currentSQLCommand.toString().trim();
               if (StringUtil.isSet(sql))
               {
                  sqlCommands.add(sql);
               }

               currentSQLCommand.setLength(0);
            }
            else
            {
               currentSQLCommand.append(theChar);

               // Is this the beginning of an inline comment?
               if (theChar == ' '
                   && i > 1
                   && lineChars[i - 1] == '-'
                   && lineChars[i - 2] == '-')
               {
                  inInlineComment = true;
               }
               else if (inBlockComment   // Is it the end of a block comment?
                        && theChar == '/'
                        && i > 0
                        && lineChars[i - 1] == '*')
               {
                  inBlockComment = false;
               }
               else if (! inBlockComment   // Is it the beginning of a block comment?
                        && theChar == '*'
                        && i > 0
                        && lineChars[i - 1] == '/')
               {
                  inBlockComment = true;
               }
               else if (inQuotedContent   // Is it end of some quoted content?
                        && theChar == '\''
                        && (0 == i || lineChars[i - 1] != '\\'))
               {
                  inQuotedContent = false;
               }
               else if (! inQuotedContent   // Is it start of some quoted content?
                        && theChar == '\''
                        && (0 == i || lineChars[i - 1] != '\\'))
               {
                  inQuotedContent = true;
               }
            }
         }

         currentSQLCommand.appendln();
      }

      // Process remaining content
      String sql = currentSQLCommand.toString().trim();
      if (StringUtil.isSet(sql))
      {
         sqlCommands.add(sql);
      }

      bufferedReader.close();

      return sqlCommands;
   }


   //---------------------------------------------------------------------------
   public static Integer getInteger(ResultSet inResultSet, String inColumnLabel)
      throws SQLException
   {
      Integer value = inResultSet.getInt(inColumnLabel);
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Integer getInteger(ResultSet inResultSet, DatabaseCol inColumn)
      throws SQLException
   {
      Integer value = inResultSet.getInt(inColumn.name());
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Integer getInteger(ResultSet inResultSet, int inColumnIndex)
      throws SQLException
   {
      Integer value = inResultSet.getInt(inColumnIndex);
      return (inResultSet.wasNull() ? null : value);
   }


   //---------------------------------------------------------------------------
   public static Long getLong(ResultSet inResultSet, String inColumnLabel)
      throws SQLException
   {
      Long value = inResultSet.getLong(inColumnLabel);
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Long getLong(ResultSet inResultSet, DatabaseCol inColumn)
      throws SQLException
   {
      Long value = inResultSet.getLong(inColumn.name());
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Long getLong(ResultSet inResultSet, int inColumnIndex)
      throws SQLException
   {
      Long value = inResultSet.getLong(inColumnIndex);
      return (inResultSet.wasNull() ? null : value);
   }


   //---------------------------------------------------------------------------
   public static Float getFloat(ResultSet inResultSet, String inColumnLabel)
      throws SQLException
   {
      Float value = inResultSet.getFloat(inColumnLabel);
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Float getFloat(ResultSet inResultSet, DatabaseCol inColumn)
      throws SQLException
   {
      Float value = inResultSet.getFloat(inColumn.name());
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Float getFloat(ResultSet inResultSet, int inColumnIndex)
      throws SQLException
   {
      Float value = inResultSet.getFloat(inColumnIndex);
      return (inResultSet.wasNull() ? null : value);
   }


   //---------------------------------------------------------------------------
   public static Double getDouble(ResultSet inResultSet, String inColumnLabel)
      throws SQLException
   {
      Double value = inResultSet.getDouble(inColumnLabel);
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Double getDouble(ResultSet inResultSet, DatabaseCol inColumn)
      throws SQLException
   {
      Double value = inResultSet.getDouble(inColumn.name());
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   public static Double getDouble(ResultSet inResultSet, int inColumnIndex)
      throws SQLException
   {
      Double value = inResultSet.getDouble(inColumnIndex);
      return (inResultSet.wasNull() ? null : value);
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setString().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setString(PreparedStatement inPreparedStatement, int inIndex, String inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setString(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.VARCHAR);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setFloat().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setFloat(PreparedStatement inPreparedStatement, int inIndex, Float inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setFloat(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.FLOAT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setDouble().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setDouble(PreparedStatement inPreparedStatement, int inIndex, Double inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setDouble(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.FLOAT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setInt().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setInt(PreparedStatement inPreparedStatement, int inIndex, Integer inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setInt(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.BIGINT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setShort().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setShort(PreparedStatement inPreparedStatement, int inIndex, Short inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setShort(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.SMALLINT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setLong().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setLong(PreparedStatement inPreparedStatement, int inIndex, Long inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setLong(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.BIGINT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setDate().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setDate(PreparedStatement inPreparedStatement, int inIndex, java.util.Date inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setDate(inIndex, new Date(inValue.getTime()));
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.BIGINT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setDate().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setDate(PreparedStatement inPreparedStatement, int inIndex, Date inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setDate(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.BIGINT);
      }
   }

   //---------------------------------------------------------------------------
   /**
    A null-tolerant wrapper for PreparedStatement's setTimestamp().
    @param inPreparedStatement the PreparedStatement in which to set the value
    @param inIndex the parameter index where the value should be placed
    @param inValue the value to insert into the PreparedStatement
    @throws SQLException
    */
   public static void setTimestamp(PreparedStatement inPreparedStatement, int inIndex, Timestamp inValue)
         throws SQLException
   {
      if (inValue != null)
      {
         inPreparedStatement.setTimestamp(inIndex, inValue);
      }
      else
      {
         inPreparedStatement.setNull(inIndex, Types.BIGINT);
      }
   }


   //---------------------------------------------------------------------------
   /**
    A convenience method for getting a count of rows that meet a specific criteria.
    @param inConn the database connection
    @param inTable the database table
    @param inWhereClause the where clause to use
    */
   public static int getRowCount(Connection inConn, DatabaseTable inTable, WhereClause inWhereClause)
         throws SQLException
   {
      List<SQLClause> clauses = new ArrayList<>(1);
      clauses.add(inWhereClause);

      return getRowCount(inConn, inTable, clauses);
   }


   //---------------------------------------------------------------------------
   /**
    A convenience method for getting a count of rows that meet a specific criteria.
    @param inConn the database connection
    @param inTable the database table
    @param inClauses additional clauses to use
    */
   public static int getRowCount(Connection inConn, DatabaseTable inTable, Collection<SQLClause> inClauses)
         throws SQLException
   {
      int count = 0;

      SQLQuery query = new SQLQuery()
            .addSelect("count('x')")
            .addFrom(inTable);

      if (CollectionUtil.hasValues(inClauses))
      {
         query.addClauses(inClauses);
      }

      ResultSet rs = null;
      try
      {
         rs = query.execute(inConn);
         if (rs.next())
         {
            count = rs.getInt(1);
         }
      }
      finally
      {
         SQLUtil.close(rs);
      }

      return count;
   }

   //---------------------------------------------------------------------------
   private static boolean isDelete(CharSequence inSQL)
   {
      boolean isDelete = false;
      
      String[] lines = StringUtil.lines(inSQL); 
      for (String line : lines)
      {
         line = line.trim();
         if (! line.startsWith("-- ")) // Skip any comment lines
         {
            isDelete = line.toUpperCase().startsWith("DELETE");
            break;
         }
      }
      
      return isDelete;
   }
}