package daruma.sql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Blob;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import java.io.UnsupportedEncodingException;

import daruma.sql.DatabaseConnectionException;
import daruma.util.CharacerCodeConstant;
import daruma.util.LogWriter;

import daruma.util.Itk; 

public abstract class DatabaseConnection
{
	abstract protected String	getDatabaseURIPrefix();
	abstract protected String	getDatabaseURIPostfix();

//	static final boolean DEBUG_DATABASE = true;
	static final boolean DEBUG_DATABASE = false;

	static	public class QueryResult /* implements java.sql.ResultSet */
	{
		private	java.sql.ResultSet	resultSet;
		private	java.sql.Statement	statement;

		public	QueryResult( java.sql.ResultSet  resultSet ,
				     java.sql.Statement  statement )
		{
			this.resultSet = resultSet;
			this.statement = statement;
		}

		public	boolean	next() throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.next() );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		private	String	bytesToString( byte[]  rawBytes )
					  throws DatabaseConnectionException
		{
			if ( rawBytes == null )
			{
				return( null );
			}

			try
			{
				return( new String( rawBytes ,
						    CharacerCodeConstant
						      .JAVA_UTF8 ) );
			}
			catch( UnsupportedEncodingException  e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}

		}

		public	String	getString( int  columnIndex )
					  throws DatabaseConnectionException
		{
			byte[]	rawBytes;

			try
			{
				rawBytes = this.resultSet.getBytes
							  ( columnIndex );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}

			return( this.bytesToString( rawBytes ) );
		}

		public	String	getString( String  columnName )
					  throws DatabaseConnectionException
		{
			byte[]	rawBytes;

			try
			{
				rawBytes = this.resultSet.getBytes
							  ( columnName );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}

			return( this.bytesToString( rawBytes ) );
		}

		public	Long	getLong( int  columnIndex )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getLong
							( columnIndex ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	Long	getLong( String  columnName )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getLong( columnName ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	Integer	getInt( int  columnIndex )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getInt
							( columnIndex ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	Integer	getInt( String  columnName )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getInt( columnName ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	java.sql.Timestamp  getTimestamp( int  columnIndex )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getTimestamp
							( columnIndex ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	java.sql.Timestamp  getTimestamp( String  columnName )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getTimestamp
							( columnName ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	java.sql.Blob  getBlob( int  columnIndex )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getBlob
							( columnIndex ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}

		public	java.sql.Blob  getBlob( String  columnName )
					  throws DatabaseConnectionException
		{
			try
			{
				return( this.resultSet.getBlob
							( columnName ) );
			}
			catch( SQLException e )
			{
				throw new DatabaseConnectionException
						( e.getMessage() , e );
			}
		}


		public	void	close() throws DatabaseConnectionException
		{
			if ( this.statement != null )
			{
				try
				{
					this.resultSet.close();
					this.statement.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "statement can't close" ,
						    e );
				}
			}
		}


		/**
		   returns a long value from a record.
		   if value is NULL, returns `0'.
		*/
		public	long	getSingleLongValue( int  columnIndex )
					    throws DatabaseConnectionException
		{
			boolean	found = false;

			long	value = 0L;

			try
			{
				while( this.resultSet.next() )
				{
					if ( found )
					{
						throw
						new DatabaseConnectionException
						  ( "found multiple data"
						    + " in column "
						    + columnIndex );
					}

					found = true;

					value = this.resultSet
						      .getLong( columnIndex );
				}
			}
			catch( java.sql.SQLException  e )
			{
				throw new DatabaseConnectionException( e );
			}


			if ( ! found )
			{
				throw new DatabaseConnectionException
					  ( "data not found in column "
					    + columnIndex );
			}

			return value;
		}
	}

	private	static final int	CONNECTION_MAX_TRIALS = 50;
	private	static final int	CONNECTION_TRIAL_WAIT_MSEC = 1000;

	private	java.sql.Connection	connection;

	private String databaseName;
	private String user;
	private String passwd;

	DatabaseConnection()
	{
		this.connection = null;
	}

	public void setReadOnly( boolean readOnly )
					  throws DatabaseConnectionException
	{
	    LogWriter.qwrite( "DEBUG",
			      "set database connection readOnly property to [",
			      readOnly, "]" );

	    try
	    {
		this.connection.setReadOnly( readOnly );
	    }
	    catch( SQLException e )
	    {
		throw new DatabaseConnectionException( e.getMessage(), e );
	    }
	}

	public	void	connect( String  databaseName ,
				 String  user ,  String  passwd )
					  throws DatabaseConnectionException
	{
		this.databaseName = databaseName;
		this.user = user;
		this.passwd = passwd;

		int	n = 1;
		for ( ;  n < CONNECTION_MAX_TRIALS  ;  ++ n )
		{
			try
			{
				String	databaseURI
					= this.getDatabaseURIPrefix()
					  + databaseName;

				String	postfix = this.getDatabaseURIPostfix();

				if ( postfix != null )
				{
					databaseURI += postfix;
				}

				this.connection
				     = java.sql.DriverManager.getConnection
					( databaseURI ,
					  user ,
					  passwd );

				/*
				//
				// do commit/rollback manually
				//
				this.connection.setAutoCommit( false );
				*/
				this.connection.setAutoCommit( true );

				// Connection is properly created.
				// Exit reconnection loop.
				break;
			}
			catch( java.sql.SQLException  e )
			{
				LogWriter.qwrite("WARN", "");
				LogWriter.qwrite("WARN", e.getMessage(),
						 "recconect count = ", n );

				try
				{
					Thread.sleep
						( CONNECTION_TRIAL_WAIT_MSEC );
				}
				catch( InterruptedException  ie )
				{
					ie.printStackTrace();
				}
			}
		}

		if ( n > CONNECTION_MAX_TRIALS )
		{
			throw new DatabaseConnectionException
				  ( "connection not established" );
		}

		if ( n != 1 )
		{
			LogWriter.qwrite("WARN",  "connected!!" );
		}
	}


	public	void	close() throws DatabaseConnectionException
	{
		if ( this.connection != null )
		{
			try
			{
				this.connection.close();
			}
			catch( java.sql.SQLException  e )
			{
				throw new DatabaseConnectionException
				( "connection closing failed,"
				  + " sql error ["
				  + e.getMessage() + "]" , e );
			}
		}
	}

	public void reset() throws DatabaseConnectionException
	{
	    this.close();
	    this.connect( this.databaseName, this.user, this.passwd );
	}

	public void	startTransaction() throws DatabaseConnectionException
	{
		LogWriter.qwrite( "DEBUG",
				  "DatabaseConnection::startTransaction" );

		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "start transaction failed,"
			      + " connection not established" );
		}

		try
		{
			this.executeQuery( "START TRANSACTION" );
		}
		catch( DatabaseConnectionException e )
		{
			throw new DatabaseConnectionException
				  ( "start transaction failed: "
				    + e.getMessage() );
		}
	}

	public void	commit() throws DatabaseConnectionException
	{
		LogWriter.qwrite( "DEBUG",
				  "DatabaseConnection::commit" );


		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "commit transaction failed,"
			      + " connection not established" );
		}

		try
		{
			this.executeQuery( "COMMIT" );
			LogWriter.qwrite("DEBUG",
					 "DatabaseConnection::commit");
		}
		catch( DatabaseConnectionException e )
		{
			throw new DatabaseConnectionException
				  ( "commit failed: " + e.getMessage() );
		}
	}

	public void	rollback() throws DatabaseConnectionException
	{
		LogWriter.qwrite( "DEBUG",
				  "DatabaseConnection::rollback" );

		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "rollback failed,"
			      + " connection not established" );
		}

		try
		{
			this.executeQuery( "ROLLBACK" );
			LogWriter.qwrite("DEBUG",
					 "DatabaseConnection::rollback") ;
		}
		catch( DatabaseConnectionException e )
		{
			throw new DatabaseConnectionException
				  ( "rollback failed: " + e.getMessage() );
		}
	}


	public	java.util.Date	getCurrentTime()
					    throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
				  ( "can't get current time,"
				    + " connection is not established yet" );
		}


		Statement	s = null;

		try
		{
			s = this.connection.createStatement();

			ResultSet	r = s.executeQuery
						( "SELECT NOW() time" );


			java.sql.Timestamp	ret = null;

			if( r.next() )
			{
				ret = r.getTimestamp( "time" );
			}
			else
			{
				throw new DatabaseConnectionException
				  ( "can't get current time,"
				    + " unexpected database response" );
			}

			if ( r.next() )
			{
				throw new DatabaseConnectionException
				  ( "can't get current time,"
				    + " unexpected database response" );
			}

			return( ret );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
			  ( "can't get current time,"
			    + " sql error [" + e.getMessage() + "]" , e );
		}
		finally
		{
			if ( s != null )
			{
				try
				{
					s.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
					  ( "connection closing failed,"
					    + " sql error ["
					    + e.getMessage() + "]" , e );
				}
			}
		}
	}


	public	PreparedStatement	prepareStatement( String  sqlString )
					  throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "prepared statement failed,"
			      + " connection not established" );
		}

		try
		{
			LogWriter.qwrite("DEBUG",
					 "DatabaseConnection::"
					 + "prepareStatement: "
					 + sqlString) ;

			return this.connection.prepareStatement( sqlString );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "prepaerd statement failed: "
				    + e.getMessage() , e );
		}
	}



	public	QueryResult	executeQuery( String  sqlString )
					  throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "query failed, connection not established" );
		}

		Statement	s = null;
		try
		{
		    this.startDebugLog( sqlString );

		    s = this.connection.createStatement();

		    long startTime = System.currentTimeMillis();

		    ResultSet	r = s.executeQuery( sqlString );

		    long endTime = System.currentTimeMillis();

		    this.writeDebugLog( sqlString, startTime, endTime );

		    return( new QueryResult( r , s ) );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "query failed: " + e.getMessage() , e );
		}
	}

	public	QueryResult	executeQuery( PreparedStatement  statement )
					  throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "query failed, connection not established" );
		}

		try
		{
			long	startTime = System.currentTimeMillis();

			ResultSet	r = statement.executeQuery();

			long	endTime = System.currentTimeMillis();

			this.writeDebugLog( statement, startTime, endTime );

			return( new QueryResult( r , statement ) );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "query failed: " + e.getMessage() , e );
		}
	}


	public	int	executeUpdate( String  sqlString )
					  throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
				  ( "connection not established" );
		}

		Statement	s = null;
		try
		{
			s = this.connection.createStatement();

			this.startDebugLog( sqlString );

			long	startTime = System.currentTimeMillis();

			int	count = s.executeUpdate( sqlString );

			long	endTime = System.currentTimeMillis();

			this.writeDebugLog( sqlString, startTime, endTime );

			return( count );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "update failed: " + e.getMessage() , e );
		}
		finally
		{
			if ( s != null )
			{
				try
				{
					s.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "statement can't close" ,
						    e );
				}
			}
		}
	}

	public	int	executeUpdate( PreparedStatement  statement )
					  throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
				  ( "connection not established" );
		}

		try
		{
			long startTime = System.currentTimeMillis();

			int count = statement.executeUpdate();

			long endTime = System.currentTimeMillis();

			this.writeDebugLog( statement, startTime, endTime );

			return( count );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "update failed: " + e.getMessage() , e );
		}
		finally
		{
			if ( statement != null )
			{
				try
				{
					statement.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "statement can't close" ,
						    e );
				}
			}
		}
	}


	/*
	// XXX: should handle table column definition
	public	void	createTableIfNotExists( String  tableName )
					    throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "can't create table [" + tableName + "]"
			      + " because connection is not established yet" );
		}

		Statement	s = null;
		try
		{
			s = this.connection.createStatement();

			// XXX: "IF NOT EXISTS" is MySQL extension

			// XXX
			int	r = s.executeUpdate( "CREATE TABLE"
						     + " IF NOT EXISTS "
						     + tableName
						     + " (abc INTEGER)" );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
			    ( "create table [" + tableName + "] failed" , e );
		}
		finally
		{
			if ( s != null )
			{
				try
				{
					s.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "statement can't close" ,
						    e );
				}
			}
		}
	}
	*/

	public	void	dropAllTables() throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "can't drop all tables"
			      + " because connection is not established yet" );
		}

		Statement	s = null;
		ResultSet	r = null;

		try
		{
			s = this.connection.createStatement();
			r = s.executeQuery( "SHOW TABLES" );

			while( r.next() )
			{
				Statement	d = null;

				try
				{
					d = this.connection.createStatement();

					String	sql = "DROP TABLE"
						      + " IF EXISTS `"
						      + r.getString( 1 )
						      + "`";

					this.startDebugLog( sql );

					d.executeUpdate( "DROP TABLE"
							 + " IF EXISTS `"
							 + r.getString( 1 )
							 + "`" );
					d.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						( "`drop table' failed" , e );
				}
				finally
				{
					if ( d != null )
					{
						try
						{
							d.close();
						}
						catch( java.sql.SQLException  e )
						{
							throw new DatabaseConnectionException
								( "statement can't close" ,
								  e );
						}
					}
				}
			}
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "`drop all tables' failed" , e );
		}
		finally
		{
			if ( s != null )
			{
				try
				{
					s.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "statement can't close" ,
						    e );
				}
			}
		}
	}


	public	void	dropTableIfExists( String  tableName )
					   throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "can't drop table [" + tableName + "]"
			      + " because connection is not established yet" );
		}


		Statement	d = null;

		try
		{
			d = this.connection.createStatement();

			// XXX: "IF EXISTS" is MySQL extention,
			//      should move to MySQLDatabaseConnection

			String	sql = "DROP TABLE IF EXISTS "
				      + "`" + tableName + "`";
			this.startDebugLog( sql );

			d.executeUpdate( "DROP TABLE IF EXISTS "
					 + "`" + tableName + "`" );
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				( "`drop table' failed" , e );
		}
		finally
		{
			if ( d != null )
			{
				try
				{
					d.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						( "statement can't close" ,
						  e );
				}
			}
		}
	}


	public	List<String>	showTables() throws DatabaseConnectionException
	{
		if ( this.connection == null )
		{
			throw new DatabaseConnectionException
			    ( "can't show tables"
			      + " because connection is not established yet" );
		}


		List<String>	ret = new ArrayList<String>();

		Statement	s = null;
		try
		{
			s = this.connection.createStatement();

			ResultSet	r = s.executeQuery( "SHOW TABLES" );

			while( r.next() )
			{
				ret.add( r.getString( /*column*/ 1 ) );
			}
		}
		catch( java.sql.SQLException  e )
		{
			throw new DatabaseConnectionException
				  ( "show tables failed" , e );
		}
		finally
		{
			if ( s != null )
			{
				try
				{
					s.close();
				}
				catch( java.sql.SQLException  e )
				{
					throw new DatabaseConnectionException
						  ( "can't close statement" ,
						    e );
				}
			}
		}

		return( ret );
	}


	/**
	   returns maximum long value from a table column.
	   if no records found, returns `0'.
	 */
	public	long	getMaxLongValueFromTable( String  tableName ,
						  String  columnName )
					throws DatabaseConnectionException
	{
		QueryResult	r = null;

		try
		{
			r = this.executeQuery
				( "SELECT MAX(`" + columnName + "`)"
				  + " FROM `" + tableName + "`" );

			return( r.getSingleLongValue( 1 ) );
		}
		finally
		{
			if ( r != null )
			{
				r.close();
			}
		}
	}


	public	long	getSingleLongValueFromPreparedStatement
				( PreparedStatement  statement ,  int  column )
					throws DatabaseConnectionException
	{
		QueryResult	r = null;

		try
		{
			r = this.executeQuery( statement );

			return( r.getSingleLongValue( 1 ) );
		}
		finally
		{
			if ( r != null )
			{
				r.close();
			}
		}
	}

	private void startDebugLog( String sqlString )
	{
	    LogWriter.qwrite( "DEBUG",
			      "#", sqlString );

	    LogWriter.qwrite( "DEBUG",
			      "free/max memory = ",
			      Runtime.getRuntime().freeMemory(),
			      "/",
			      Runtime.getRuntime().maxMemory() );
	}

	private void writeDebugLog( PreparedStatement statement )
	{
	    LogWriter.qwrite( "DEBUG",
			      "#", statement );

	    LogWriter.qwrite( "DEBUG",
			      "free/max memory = ",
			      Runtime.getRuntime().freeMemory(),
			      "/",
			      Runtime.getRuntime().maxMemory() );
	}

	private void writeDebugLog( PreparedStatement statement,
				    long startTime, long endTime )
	{
	    String debugLevel = "DEBUG";

	    if ( DEBUG_DATABASE )
	    {
		debugLevel = "INFO";
	    }

	    LogWriter.qwrite(debugLevel,
			     "! ", (endTime - startTime),
			     " msec", "\t", '#', statement );
	}

	private void writeDebugLog( String sqlString,
				    long startTime, long endTime )
	{
	    String debugLevel = "DEBUG";

	    if ( DEBUG_DATABASE )
	    {
		debugLevel = "INFO";
	    }

	    LogWriter.qwrite(debugLevel,
			     "! ", (endTime - startTime),
			     " msec", "\t", '#', sqlString );
	}
}
