package com.philmcrew.utility.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import java.util.HashSet;

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

/**
 * Overrides some methods in a Microsoft Server specific way.  Particular problems fixed here include:
 * - Erroring on dates and timestamp earlier than a particular date
 * - Not allowing reserved words to be used as column names
 * - Limitation of var style columns to 8000 characters
 *
 * TODO: will need to implement getStatisticsQuery
 * TODO: will have a problem if two columns related to reserved word appear say between and between0
 *
 * @author Mitchell J. Friedman
 */
public class DbSpecificMicrosoftSqlServer extends DbSpecificBase 
{
	private final static Log LOG = LogFactory.getLog( DbSpecificMicrosoftSqlServer.class );

	/**
	 * The minimum timestamp that SQL Server will accept.
	 * TODO: change obsolete Timestamp constructor to something non-obsolete
	 */
	static private Timestamp minTimestamp = new Timestamp( 0, 0, 1, 0, 0, 0, 0 );

	/**
	 * The minimum date that SQL Server will accept.
	 * TODO: chance obsolete Date constructor to something non-obsolete
	 */
	static private Date minDate = new Date( 0, 0, 1 );

	public DbSpecificMicrosoftSqlServer( Connection connection ) throws SQLException 
	{
		super( connection );
	}

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

		String className = "";

		if( null != object ) 
		{
			className = object.getClass().getName();
		}

		if( null == object ) 
		{
			preparedStatement.setNull( parameterIndex, destColumnInfo.getDataType() );
		}
		else if( destColumnInfo.getDataType() == Types.TIMESTAMP /* && object instanceof Timestamp */ ) 
		{

			if( object instanceof Timestamp ) 
			{
				Timestamp timestamp = (Timestamp) object;
				if( timestamp.compareTo( minTimestamp ) > 0 ) 
				{
					LOG.info( "Using " + minTimestamp + " instead of " + object );
					object = minTimestamp;
				}
			}
			else if( object instanceof Date ) 
			{
				Date date = (Date) object;
				if( date.compareTo( minDate ) < 0 ) 
				{
					object = minDate;
				}
			}
			else 
			{
				LOG.info( "date class=" + object.getClass().getName() );
			}

			preparedStatement.setObject( parameterIndex, object );
		}
		else if( null != object && object instanceof String ) 
		{
			String string = (String) object;
			int length = string.length();

			if( destColumnInfo.getColumnSize() < length ) 
			{
				LOG.warn( "truncating string - columnName=" + destColumnInfo.getColumnName()
						+ ";sourceColumnType=" + sourceColumnType + ";destColumnType=" + destColumnInfo.getDataType()
						+ ";len=" + length + ";columnSize=" + destColumnInfo.getColumnSize() + ";className=" + className );

				String shorterString = string.substring( 0, length );
				preparedStatement.setObject( parameterIndex, shorterString );
			}
			else 
			{
				preparedStatement.setObject( parameterIndex, object );
			}
		}
		else if( className.equals( "[B" ) ) 
		{
			byte[] byteArray = (byte[]) object;

			// preparedStatement.setNull( parameterIndex, Types.VARCHAR);
			if( destColumnInfo.getColumnSize() < byteArray.length ) 
			{
				LOG.warn( "Nulling byte array instead of truncating - columnName=" + destColumnInfo.getColumnName()
						+ ";sourceColumnType=" + sourceColumnType + ";destColumnType=" + destColumnInfo.getDataType()
						+ ";len=" + byteArray.length + ";columnSize=" + destColumnInfo.getColumnSize() + ";className=" + className );
				preparedStatement.setNull( parameterIndex, destColumnInfo.getDataType() );
			}
			else 
			{
				preparedStatement.setObject( parameterIndex, object );
			}
		}

		else 
		{
			preparedStatement.setObject( parameterIndex, object );
		}
	}

	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( "datetime" );
				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.getColumnSize() );
				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( "datetime" );
				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.VARCHAR:
				if( dbColumnInfo.getColumnSize() <= 8000 && dbColumnInfo.getColumnSize() > 0 ) {
					buf.append( "varchar(" );
					buf.append( dbColumnInfo.getColumnSize() );
					buf.append( ")" );
				}
				else {
					buf.append( "text" );
				}
				break;

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

			case Types.BINARY:
				if( dbColumnInfo.getCharOctetLength() < 0 ) {
					buf.append( "varbinary(100)" );  // mjf 2005-08-12 09:32 was varchar(8000), max row=8060!
					break;
				}
				else 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:


				if( dbColumnInfo.getCharOctetLength() < 0 ) {
					buf.append( "image" );
					break;
				}
				else if( dbColumnInfo.getCharOctetLength() <= 8000 ) {
					buf.append( "varbinary" );
					buf.append( "(" );
					buf.append( dbColumnInfo.getCharOctetLength() );
					buf.append( ")" );
				}
				else {
					buf.append( "image" );
				}
				break;

			case Types.LONGVARBINARY:
				buf.append( "varchar(8000)" );
				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( "varchar(8000)" );
				break;
		}

		return buf.toString();
	}

	// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_reservedwords.asp
	static final HashSet reservedWords = new HashSet( Arrays.asList( new String[]{
		"authorization", "avg",
		"backup", "begin", "between", "break", "browse", "bulk", "by",
		"cascade", "case", "check", "checkpoint", "close", "clustered", "coalesce", "collate", "column", "commit",
		"compute", "constraint", "contains", "containstable", "continue", "convert", "count", "create", "cross",
		"current", "current_date", "current_time", "current_timestamp", "current_user", "cursor",
		"database", "databasepassword", "dateadd", "datediff", "datename", "datepart", "dbcc", "deallocate",
		"declare", "default", "delete", "deny", "desc", "disk", "distinct", "distributed", "double", "drop", "dump",
		"else", "encryption", "end", "errlvl", "escape", "except", "exec", "execute", "exists", "exit", "expression",
		"key"} ) );

	public String buildColumnName( String columnName ) {
		columnName = columnName.toLowerCase();

		if( reservedWords.contains( columnName ) ) {
			columnName += "0";
		}

		return columnName;
	}
}