package daruma.sql;

import java.lang.StringBuilder;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Date;
import java.util.List;

import daruma.global_switch.ImplementationSwitches;
import daruma.geometry.DrmGeometry;
import daruma.sql.TableColumn;
import daruma.sql.DatabaseConnection;
import daruma.sql.DatabaseCalendarConfig;
import daruma.storage_manager.DBMSStorageManager;
import daruma.storage_manager.type_definition.ColumnNameFactory;
import daruma.storage_manager.type_definition.TypedInstance;
import daruma.util.ISO8601DateFormat;
import daruma.util.LogWriter;


public class ElementInserter
{
	private ElementInserter()
	{
	}

	public static void executeInsert( DatabaseConnection db,
					  String tableName,
					  List<TableColumn> columns,
					  long transactionSN )
					  throws DatabaseConnectionException
	{
	    //
	    // create template
	    //
	    PreparedStatement st = ElementInserter.getPreparedStatement
				   ( db, tableName, columns );

	    //
	    // get new object ID
	    //
	    long objectID = db.getMaxLongValueFromTable( tableName, "_id_" );
	    objectID ++;


	    //
	    // set values
	    //
	    ElementInserter.setValuesToPreparedStatement
				( st, transactionSN, objectID, columns );


	    //
	    // update table
	    //
	    try
	    {
		db.executeUpdate( st );
		st.close();
	    }
	    catch( SQLException  e )
	    {
		throw new DatabaseConnectionException( e.getMessage(), e );
	    }


	    //
	    // update sub table
	    //
	    executeInsertToSubTable( db, columns, objectID );
	}


	public static void executeBulkInsert( DatabaseConnection db,
					      String tableName,
					      List<TypedInstance> objs,
					      long transactionSN )
					  throws DatabaseConnectionException
	{
	    //
	    // create template
	    //
	    PreparedStatement st = ElementInserter.getPreparedStatement
				   ( db, tableName, objs.get(0).getColumns() );

	    //
	    // get new object ID
	    //
	    long objectID = db.getMaxLongValueFromTable( tableName, "_id_" );
	    objectID ++;

	    for ( TypedInstance obj : objs )
	    {
		List<TableColumn> columns = obj.getColumns();

		//
		// set values
		//
		ElementInserter.setValuesToPreparedStatement
				( st, transactionSN, objectID, columns );


		//
		// add to prepared statement
		//
		try
		{
		    st.addBatch();
		    st.clearParameters();
		}
		catch( SQLException  e )
		{
		    LogWriter.qwrite( "DEBUG",
				      "add batch failed, ",
				      e.getMessage() );

		    if ( e.getNextException() != null )
		    {
			LogWriter.qwrite( "DEBUG",
					  "detailed exception is [",
					  e.getNextException().getMessage(),
					  "]" );
		    }

		    throw new DatabaseConnectionException( e.getMessage(), e );
		}

		//
		// update sub table
		//
		executeInsertToSubTable( db, columns, objectID );
		objectID ++;
	    }

	    try
	    {
		// db.executeBatch( st );
		st.executeBatch();
		st.close();
	    }
	    catch( SQLException  e )
	    {
		LogWriter.qwrite( "DEBUG",
				  "insert failed, ",
				  e.getMessage() );

		if ( e.getNextException() != null )
		{
		    LogWriter.qwrite( "DEBUG",
				      "detailed exception is [",
				      e.getNextException().getMessage(),
				      "]" );
		}

		throw new DatabaseConnectionException( e.getMessage(), e );
	    }
	}

	private static PreparedStatement getPreparedStatement
					    ( DatabaseConnection db,
					      String tableName,
					      List<TableColumn> columns )
					  throws DatabaseConnectionException
	{
		StringBuilder	s = new StringBuilder();

		s.append( "INSERT INTO " + tableName + " (" );


		//
		// add column name list
		//

		// transaction id
		s.append( DBMSStorageManager.ColName_SystemTransactionID );
		s.append( ",");

		// create time
		s.append( DBMSStorageManager.ColName_SystemCreateTime );
		s.append( "," );

		// update time
		s.append( DBMSStorageManager.ColName_SystemUpdateTime );
		s.append( "," );

		// object id
		s.append( DBMSStorageManager.ColName_SystemID );

		// add property columns
		for ( TableColumn c : columns )
		{
			s.append( "," );
			s.append( c.getDefinition().getColumnName() );
		}

		s.append( ") " );


		//
		// add values wildcards
		//
		s.append( "VALUES (" );

		int wildcardCount = 0;

		// transaction id
		s.append( "?" );
		wildcardCount ++;
		s.append( "," );

		// create time
		s.append( "NULL" );
		s.append( "," );

		// update time
		s.append( "NULL" );
		s.append( "," );

		// object id
		s.append( "?" );
		wildcardCount ++;

		// each property
		for ( TableColumn c : columns )
		{
			s.append( "," );

			Class	javaClass = c.getDefinition()
					      .getSQLDataType()
					      .getJavaClass();

			if ( DrmGeometry.class.isAssignableFrom( javaClass ) )
			{
				wildcardCount ++;
				s.append( "GeomFromText(?)" );
			}
			else if ( Date.class.isAssignableFrom( javaClass ) )
			{
				wildcardCount ++;
				s.append( "?" );
			}
			else
			{
				wildcardCount ++;
				s.append( "?" );
			}
		}

		s.append( ")" );

		LogWriter.qwrite("DEBUG", "#", s );

		//
		// create prepare stetement
		//
		PreparedStatement st = null;

		try
		{
			st = db.prepareStatement( s.toString() );
		}
		catch( DatabaseConnectionException  e )
		{
			throw e;
		}

		return st;
	}

	private static void setValuesToPreparedStatement
				     ( PreparedStatement st,
				       long transactionSerialNumber,
				       long objectID,
				       List<TableColumn> columns )
					 throws DatabaseConnectionException
	{
	    try
	    {
		int wildcardIndex = 1;

		// transaction id
		st.setLong( wildcardIndex,
			    new Long( transactionSerialNumber ) );
		wildcardIndex ++;

		// object id
		st.setLong( wildcardIndex, new Long( objectID ) );
		wildcardIndex ++;

		for ( int  i = 0  ;  i < columns.size()  ;  ++ i )
		{
		    TableColumn	col = columns.get(i);

		    if ( col.isEmptyColumn()
			|| col.getValue() == null )
		    {
			st.setNull( wildcardIndex, java.sql.Types.NULL );
			wildcardIndex ++;
			continue;
		    }

		    Class c = col.getDefinition()
				 .getSQLDataType().getJavaClass();

		    if ( c.equals( String.class ) )
		    {
			st.setString( wildcardIndex, col.getValue() );

			wildcardIndex ++;
		    }
		    else if ( c.equals( Boolean.class ) )
		    {
			boolean value = Boolean.parseBoolean( col.getValue() );

			st.setBoolean( wildcardIndex, value );

			wildcardIndex ++;
		    }
		    else if ( c.equals( Double.class ) )
		    {
			double value = Double.parseDouble( col.getValue() );

			st.setDouble( wildcardIndex, value );

			wildcardIndex ++;
		    }
		    else if ( c.equals( Float.class ) )
		    {
			float value = Float.parseFloat( col.getValue() );

			st.setFloat( wildcardIndex, value );

			wildcardIndex ++;
		    }
		    else if ( c.equals( Long.class ) )
		    {
			long value = Long.parseLong( col.getValue() );
			st.setLong( wildcardIndex, value );

			wildcardIndex ++;
		    }
		    else if ( c.equals( Integer.class ) )
		    {
			int value = Integer.parseInt( col.getValue() );
			st.setInt( wildcardIndex, value );

			wildcardIndex ++;
		    }
		    else if ( Date.class.isAssignableFrom( c ) )
		    {
			ISO8601DateFormat f = new ISO8601DateFormat();

			Date dateTime;

			try
			{
			    dateTime = f.parse( col.getValue() );

			    // XXX: should not drop
			    //      nanoseconds information
			}
			catch( ParseException  e )
			{
			    throw new DatabaseConnectionException( e );
			}

			if ( ImplementationSwitches.instance()
			     .isMySQLBackend() )
			{
			    st.setString( wildcardIndex,
					  SQLTimeFormatConverter
					  .convertDateToString( dateTime ) );
			}
			else
			{
			    st.setTimestamp
				( wildcardIndex,
				  new Timestamp( dateTime.getTime() ),
				  DatabaseCalendarConfig.getCalendar() );
			}

			wildcardIndex ++;
		    }
		    else if ( DrmGeometry.class.isAssignableFrom( c ) )
		    {
			st.setString( wildcardIndex, col.getValue() );
			wildcardIndex ++;
		    }
		    else
		    {
			st.setString( wildcardIndex, col.getValue() );

			wildcardIndex ++;
		    }
		}
	    }
	    catch( SQLException  e )
	    {
		throw new DatabaseConnectionException( e.getMessage(), e );
	    }
	}


	private static void executeInsertToSubTable( DatabaseConnection  db,
						     List<TableColumn> columns,
						     long  parentID )
					  throws DatabaseConnectionException
	{
		for ( TableColumn c : columns )
		{
			if ( c.isEmptyColumn() )
			{
				continue;
			}

			if ( c.getDefinition().isExternalTable() )
			{
				String	tableName = c.getValue();

				LogWriter.qwrite("DEBUG",  "tableName = [",
						 tableName, "]" );

				long	id = db.getMaxLongValueFromTable
					( tableName, "_id_" );
				id ++;

				for ( String  v : c.getSubValues() )
				{
					LogWriter.qwrite("DEBUG", "value = [",
							 v, "]" );

					String sql
					    = "INSERT INTO "
					    + c.getValue()
					    + " (_id_,_parent_id_, "
					    + (ColumnNameFactory
					       .getTopLevelElementColumnName())
					    + ") VALUES ("
					    + id + ","
					    + parentID
					    + ",?)";

					PreparedStatement	st = null;

					try
					{
						st = db.prepareStatement
								    ( sql );
					}
					catch( DatabaseConnectionException  e )
					{
						throw e;
					}

					LogWriter.qwrite("DEBUG",  "#", sql );

					try
					{
						st.setString( 1, v );

						db.executeUpdate( st );

						st.close();
					}
					catch( SQLException  e )
					{
						throw
						new DatabaseConnectionException
						    ( e.getMessage(), e );
					}
				}
			}
		}
	}
}
