package com.philmcrew.utility.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Implements a number of more complicated database actions than what is supported by jdbc
 * in a way that will work for most datbases.  To override a for a particular database you
 * just extend this class, and change DbTransfer.getDbSpecific in order to pick which DbSpecific class
 * to use.
 *
 * @author Mitchell J. Friedman
 */
public class DbSpecificBase implements DbSpecific 
{
	private final static Log LOG = LogFactory.getLog( DbSpecificBase.class );

	private Connection connection;
	private DatabaseMetaData metaData;

	public DbSpecificBase( Connection connection ) throws SQLException 
	{
		this.setConnection( connection );
		this.setMetaData( connection.getMetaData() );
	}

	public DatabaseMetaData getMetaData() 
	{
		return metaData;
	}
	/*
	 *  <OL>
	 *	<LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
	 *	<LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
	 *	<LI><B>TABLE_NAME</B> String => table name
	 *	<LI><B>COLUMN_NAME</B> String => column name
	 *	<LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types
	 *	<LI><B>TYPE_NAME</B> String => Data source dependent type name,
	 *  for a UDT the type name is fully qualified
	 *	<LI><B>COLUMN_SIZE</B> int => column size.  For char or date
	 *	    types this is the maximum number of characters, for numeric or
	 *	    decimal types this is precision.
	 *	<LI><B>BUFFER_LENGTH</B> is not used.
	 *	<LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
	 *	<LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
	 *	<LI><B>NULLABLE</B> int => is NULL allowed.
	 *      <UL>
	 *      <LI> columnNoNulls - might not allow <code>NULL</code> values
	 *      <LI> columnNullable - definitely allows <code>NULL</code> values
	 *      <LI> columnNullableUnknown - nullability unknown
	 *      </UL>
	 *	<LI><B>REMARKS</B> String => comment describing column (may be <code>null</code>)
	 * 	<LI><B>COLUMN_DEF</B> String => default value (may be <code>null</code>)
	 *	<LI><B>SQL_DATA_TYPE</B> int => unused
	 *	<LI><B>SQL_DATETIME_SUB</B> int => unused
	 *	<LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
	 *       maximum number of bytes in the column
	 *	<LI><B>ORDINAL_POSITION</B> int	=> index of column in table
	 *      (starting at 1)
	 *	<LI><B>IS_NULLABLE</B> String => "NO" means column definitely
	 *      does not allow NULL values; "YES" means the column might
	 *      allow NULL values.  An empty string means nobody knows.
	 *  <LI><B>SCOPE_CATLOG</B> String => catalog of table that is the scope
	 *      of a reference attribute (<code>null</code> if DATA_TYPE isn't REF)
	 *  <LI><B>SCOPE_SCHEMA</B> String => schema of table that is the scope
	 *      of a reference attribute (<code>null</code> if the DATA_TYPE isn't REF)
	 *  <LI><B>SCOPE_TABLE</B> String => table name that this the scope
	 *      of a reference attribure (<code>null</code> if the DATA_TYPE isn't REF)
	 *  <LI><B>SOURCE_DATA_TYPE</B> short => source type of a distinct type or user-generated
	 *      Ref type, SQL type from java.sql.Types (<code>null</code> if DATA_TYPE
	 *      isn't DISTINCT or user-generated REF)
	 *  </OL>




	 */

	public void close( Statement statement ) 
	{
		if( null != statement ) 
		{
			try 
			{
				statement.close();
			}
			catch( SQLException e ) 
			{
				LOG.error( "Error closing statement", e );
			}
		}
	}

	public void close( ResultSet resultSet ) 
	{
		if( null != resultSet ) 
		{
			try 
			{
				resultSet.close();
			}
			catch( SQLException e ) 
			{
				LOG.error( "Error closing result set", e );
			}
		}
	}


	public int createTableQuery( ResultSet columnMetaData, StringBuffer createQuery,
			HashMap<String, DbColumnInfo> columnMap ) throws SQLException 
			{
		int col = 0;

		DbColumnInfo dbColumnInfo;

		// call with source and with dest - perhaps overriding cat or schema
		// then compare two schemas if different than drop dest table and build from source

		if( null != columnMetaData ) {


			String columnName;

			for( col = 0; columnMetaData.next(); col++ ) {

				dbColumnInfo = new DbColumnInfo( columnMetaData );

				if( 0 == col ) {
					createQuery.append( "CREATE TABLE " );
/*
					buf.append( columnMetaData.getString( "TABLE_CAT" ) );
					buf.append( "." );
					buf.append( columnMetaData.getString( "TABLE_SCHEM" ) );
					buf.append( "." );
*/
					createQuery.append( dbColumnInfo.getTableName().toLowerCase() );
					createQuery.append( "\n(\n" );
				}
				else {
					createQuery.append( ", \n" );
				}

				createQuery.append( "\t" );
				columnName = dbColumnInfo.getColumnName().toLowerCase();

				createQuery.append( columnName );
				createQuery.append( " " );


				createQuery.append( buildDeclaration( dbColumnInfo ) );
				createQuery.append( buildNullable( dbColumnInfo ) );

				if( null != columnMap ) {
					columnMap.put( columnName, dbColumnInfo );
				}
			}

			if( col > 0 )
				createQuery.append( "\n);\n" );
		}

		return col;
	}

	/**
	 * By default lower-casing column name.  Probably this will need to be removed from base
	 * at some time.  Microsoft SQL Server requires that certain column names be avoided - the
	 * implementation in DbSpecificMicrosoftSqlServer appends a value to the column name.
	 *
	 * @param columnName
	 * @return column name converted if necessary
	 */
	public String buildColumnName( String columnName ) {
		columnName = columnName.toLowerCase();
		return columnName;
	}

	/**
	 * Creates nullable phrase for use in create using column information
	 *
	 * @param dbColumnInfo
	 * @return nullable phrase suitable for using in create
	 */
	public String buildNullable( DbColumnInfo dbColumnInfo ) {
		String ret;
		if( dbColumnInfo.getNullable() != DatabaseMetaData.columnNullable
				&& dbColumnInfo.getIsNullable().equals( "NO" ) ) {
			ret = " not null";
		}
		else
			ret = "";

		return ret;
	}

	public String buildDeclaration( DbColumnInfo dbColumnInfo ) {
		StringBuffer buf = new StringBuffer();

		switch( dbColumnInfo.getDataType() ) {
			case Types.BIGINT:
				buf.append( "bigint" );
				break;

			case Types.BIT:
				buf.append( "bit" );
				break;

			case Types.BLOB:
				buf.append( "image" );
				break;

			case Types.BOOLEAN:
				buf.append( "boolean" );
				break;

			case Types.CLOB:
				buf.append( "text" );
				break;

			case Types.DATE:
				buf.append( "date" );
				break;

			case Types.DECIMAL:
				buf.append( "decimal(" );
				buf.append( dbColumnInfo.getColumnSize() );
				buf.append( ", " );
				buf.append( dbColumnInfo.getDecimalDigits() );
				buf.append( ")" );
				break;

			case Types.DOUBLE:
				buf.append( "float" );
				break;

			case Types.INTEGER:
				buf.append( "int" );
				break;

			case Types.FLOAT:
				buf.append( "float" );
				break;

			case Types.NUMERIC:
				buf.append( "numeric(" );
				buf.append( dbColumnInfo.getNumPrecRadix() );
				buf.append( ", " );
				buf.append( dbColumnInfo.getDecimalDigits() );
				buf.append( ")" );
				break;

			case Types.REAL:
				buf.append( "real" );
				break;

			case Types.SMALLINT:
				buf.append( "smallint" );
				break;

			case Types.TIME:
				buf.append( "time" );
				break;

			case Types.TIMESTAMP:
				buf.append( "timestamp" );
				break;

			case Types.TINYINT:
				buf.append( "tinyint" );
				break;

			case Types.CHAR:
			case Types.NULL:
				if( dbColumnInfo.getColumnSize() < 8000 ) {
					buf.append( "char(" );
					buf.append( dbColumnInfo.getColumnSize() );
					buf.append( ")" );
					break;
				}
				// drop through if too big for char

			case Types.LONGVARCHAR:
			case Types.VARCHAR:
				if( dbColumnInfo.getColumnSize() <= 8000 ) {
					buf.append( "varchar(" );
					buf.append( dbColumnInfo.getColumnSize() );
					buf.append( ")" );
				}
				else {
					buf.append( "text" );
				}
				break;

			case Types.BINARY:
				if( dbColumnInfo.getCharOctetLength() <= 8000 ) {
					buf.append( "binary" );
					if( dbColumnInfo.getCharOctetLength() > 1 ) {
						buf.append( "(" );
						buf.append( dbColumnInfo.getCharOctetLength() );
						buf.append( ")" );
					}
					break;
				}
				// drop through if too big for char
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
				if( dbColumnInfo.getCharOctetLength() <= 8000 ) {
					buf.append( "varbinary" );
					if( dbColumnInfo.getCharOctetLength() > 1 ) {
						buf.append( "(" );
						buf.append( dbColumnInfo.getCharOctetLength() );
						buf.append( ")" );
					}
				}
				else {
					buf.append( "image" );
				}
				break;

			case Types.ARRAY:
			case Types.DATALINK:
			case Types.DISTINCT:
			case Types.JAVA_OBJECT:
			case Types.OTHER:
			case Types.STRUCT:
			case Types.REF:
			default:
				buf.append( "image" );
				break;
		}

		return buf.toString();
	}

	public boolean drop( String catTableName, String schemaTableName, String tableName ) throws SQLException {
		boolean ret = false;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			statement = getConnection().createStatement();
			resultSet = query( statement, "drop table " + tableName );
			ret = null != resultSet && resultSet.next();
		}
		catch( SQLException e ) {
			LOG.error( "Dropping " + tableName, e );
		}
		finally {
			close( resultSet );
			close( statement );
		}

		return ret;
	}

	public boolean delete( String catTableName, String schemaTableName, String tableName ) throws SQLException {
		boolean ret = false;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			statement = getConnection().createStatement();
			resultSet = query( statement, "delete from  " + tableName );
			ret = null != resultSet && resultSet.next();
		}
		catch( SQLException e ) {
			LOG.error( "Deleting " + tableName, e );
		}
		finally {
			close( resultSet );
			close( statement );
		}

		return ret;
	}

	public ResultSet query( Statement statement, String query ) throws SQLException {
		ResultSet resultSet = null;

		try {

			if( statement.execute( query ) )
				resultSet = statement.getResultSet();
		}
		catch( SQLException e ) {
			LOG.error( "Error running query " + query, e );
			Exception e2 = e.getNextException();
			if( null != e2 ) {
				LOG.error( "Query Error: cause=" + e2.getCause() + ";msg=" + e2.getMessage(), e2 );
			}
			throw e;
		}

		return resultSet;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection( Connection connection ) {
		this.connection = connection;
	}

	public void setMetaData( DatabaseMetaData metaData ) {
		this.metaData = metaData;
	}

	public String connectMessage() {
		DatabaseMetaData dmd = getMetaData();

		String tmp = "";
		int tmpi;

		StringBuffer buf = new StringBuffer();
		buf.append( "Connected to " );

		try {
			tmp = dmd.getConnection().getCatalog();
			buf.append( tmp );
		}
		catch( SQLException e ) {

		}

		try {
			tmp = dmd.getURL();
			buf.append( " " );
			buf.append( tmp );
		}
		catch( SQLException e ) {
		}

		buf.append( " - " );

		try {
			tmp = dmd.getDatabaseProductName();
			buf.append( tmp );
		}
		catch( SQLException e ) {
		}

		try {
			tmp = dmd.getDatabaseProductVersion();
			buf.append( " " );
			buf.append( tmp );
		}
		catch( SQLException e ) {
		}

		try 
		{
			tmpi = dmd.getDatabaseMajorVersion();
			buf.append( " " );
			buf.append( tmpi );
		}
		catch( SQLException e ) 
		{
		}
		catch( AbstractMethodError e ) 
		{
			// happened in sql server - pretty weird
		}

		try 
		{
			tmpi = dmd.getDatabaseMinorVersion();
			buf.append( "." );
			buf.append( tmpi );
		}
		catch( SQLException e ) 
		{
		}
		catch( AbstractMethodError e ) 
		{
			// happened in sql server - pretty weird
		}

		try 
		{
			buf.append( " using " );
			tmp = dmd.getDriverName();
			buf.append( tmp );
		}
		catch( SQLException e ) {
		}

		try 
		{
			tmp = dmd.getDriverVersion();
			buf.append( " " );
			buf.append( tmp );
		}
		catch( SQLException e ) {
		}


		tmpi = dmd.getDriverMajorVersion();
		buf.append( " " );
		buf.append( tmpi );

		tmpi = dmd.getDriverMinorVersion();
		buf.append( "." );
		buf.append( tmp );

		return buf.toString();
	}

	public void setObject( PreparedStatement preparedStatement, int parameterIndex,
			Object object, int sourceColumnType, DbColumnInfo destColumnInfo ) throws SQLException 
	{
		preparedStatement.setObject( parameterIndex, object );
	}

	/**
	 * Build Statistical query for column info.  This query is probably not suitable for any database
	 * since getting a min, max, avg and stddev don't typically work on all field types.
	 *
	 * @param dbColumnInfo
	 * @return query to get statistical information.
	 */
	public String getStatisticsQuery( DbColumnInfo dbColumnInfo ) 
	{

		String rowName = dbColumnInfo.getColumnName();

		StringBuffer buf = new StringBuffer();
		buf.append( "select count(distinct " );
		buf.append( rowName );
		buf.append( "), min(" );
		buf.append( rowName );
		buf.append( "), max(" );
		buf.append( rowName );
		buf.append( "), avg(" );
		buf.append( rowName );
		buf.append( "), stddev(" );
		buf.append( rowName );
		buf.append( ") " );

		return buf.toString();
	}
}