//
//  Database.mm
//
//  Created by Robert on 20/07/2009.
//  Copyright 2009 Electric TopHat Ltd. All rights reserved.
//

#import "Database.h"

#define LOG_DB_ERRORS 1

#pragma mark ---------------------------------------------------------
#pragma mark === Constructor / Destructor Functions  ===
#pragma mark ---------------------------------------------------------

// ---------------------------------------------------------------
// Constructor
// ---------------------------------------------------------------
Database::Database(NSString* _name, NSString * _createString)
{
	m_error		= nil;
	m_database	= nil;
	
	if ( !createWithBundleDatabaseWithName(_name) )
	{
		if ( !createAnEmptyDatabaseWithName(_name, _createString) )
		{
			m_error = @"Error : Failed to create the database.";
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error );
#endif
		}
 	}
}

// ---------------------------------------------------------------
// Destructor
// ---------------------------------------------------------------
Database::~Database()
{	
	if ( m_database )
	{
		// Close the database.
		if (sqlite3_close(m_database) != SQLITE_OK) 
		{
			m_error = [NSString stringWithFormat:@"Error : failed to close database with message '%s'.", sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error);
#endif
		}
		m_database = nil;
	}
}

#pragma mark ---------------------------------------------------------
#pragma mark === End Constructor / Destructor Functions  ===
#pragma mark ---------------------------------------------------------

#pragma mark ---------------------------------------------------------
#pragma mark === Public Functions  ===
#pragma mark ---------------------------------------------------------

// ---------------------------------------------------------------
// execute a single sql statement
// ---------------------------------------------------------------
Boolean Database::execute( NSString * _sql, NSMutableArray * _results )
{
	if ( m_database )
	{
		sqlite3_stmt * sqlStatement = nil;
		if (sqlite3_prepare_v2(m_database, [_sql UTF8String], -1, &sqlStatement, NULL) != SQLITE_OK) 
		{
			m_error = [NSString stringWithFormat:@"Error : in Sql %@, failed to prepare statement with message '%s'.", _sql, sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error);
#endif
		}
		else
		{			
			// step over each of the rows and grab the data.
			while (sqlite3_step(sqlStatement) == SQLITE_ROW) 
			{
				if ( _results )
				{
					int count = sqlite3_data_count(sqlStatement);
					
					// grab the row data and place it into a dictionary.
					NSMutableDictionary * row = [NSMutableDictionary dictionaryWithCapacity:count];
				    for ( int i=0; i<count; i++ )
					{
						NSString * columnName = [NSString stringWithUTF8String:(char *)sqlite3_column_name(sqlStatement, i)];
						switch ( sqlite3_column_type(sqlStatement, i) )
						{
							case SQLITE_INTEGER:
							{
								[row setObject:[NSNumber numberWithInt:sqlite3_column_int(sqlStatement, i)] forKey:columnName];
								break;
							}
							case SQLITE_FLOAT:
							{
								[row setObject:[NSNumber numberWithDouble:sqlite3_column_double(sqlStatement, i)] forKey:columnName];
								break;
							}
							case SQLITE_BLOB:
							{
								const void * blob = sqlite3_column_blob(sqlStatement, i);
								int bytes = sqlite3_column_bytes(sqlStatement, i);
								
								[row setObject:[NSData dataWithBytes:blob length:bytes]  forKey:columnName];
								break;
							}
							case SQLITE_NULL:
							{
								// do nothing as the dictionary will return nil when the column is requested
								break;
							}
							case SQLITE_TEXT:
							{
								[row setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, i)] forKey:columnName];
								break;
							}
						}
					}
					
					// add a row into the results object
					[_results addObject:row];
				}
				else
				{
					break;
				}
			}
			
			// finalize the sql statment to make sure it executed correctly
			if ( sqlite3_finalize(sqlStatement) == SQLITE_OK )
			{
				return TRUE;
			}
			else
			{
				m_error = [NSString stringWithFormat:@"Error : Failed to execute statement with message '%s'.", sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
				NSLog( @"%@", m_error);
#endif
			}
		}
	}
	
	return FALSE;
}

// ---------------------------------------------------------------
// execute a single sql statement
// ---------------------------------------------------------------
Boolean Database::execute( NSString * _sql, NSMutableDictionary * _result )
{
	if ( m_database )
	{
		sqlite3_stmt * sqlStatement = nil;
		if (sqlite3_prepare_v2(m_database, [_sql UTF8String], -1, &sqlStatement, NULL) != SQLITE_OK) 
		{
			m_error = [NSString stringWithFormat:@"Error : in Sql %@, failed to prepare statement with message '%s'.", _sql, sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error);
#endif
		}
		else
		{			
			// step over each the first rows and grab the data.
			while (sqlite3_step(sqlStatement) == SQLITE_ROW) 
			{
				if ( _result )
				{
					int count = sqlite3_data_count(sqlStatement);
					
					// grab the row data and place it into a dictionary.
				    for ( int i=0; i<count; i++ )
					{
						NSString * columnName = [NSString stringWithUTF8String:(char *)sqlite3_column_name(sqlStatement, i)];
						switch ( sqlite3_column_type(sqlStatement, i) )
						{
							case SQLITE_INTEGER:
							{
								[_result setObject:[NSNumber numberWithInt:sqlite3_column_int(sqlStatement, i)] forKey:columnName];
								break;
							}
							case SQLITE_FLOAT:
							{
								[_result setObject:[NSNumber numberWithDouble:sqlite3_column_double(sqlStatement, i)] forKey:columnName];
								break;
							}
							case SQLITE_BLOB:
							{
								const void * blob = sqlite3_column_blob(sqlStatement, i);
								int bytes = sqlite3_column_bytes(sqlStatement, i);
								
								[_result setObject:[NSData dataWithBytes:blob length:bytes]  forKey:columnName];
								break;
							}
							case SQLITE_NULL:
							{
								// do nothing as the dictionary will return nil when the column is requested
								break;
							}
							case SQLITE_TEXT:
							{
								[_result setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, i)] forKey:columnName];
								break;
							}
						}
					}
				}
				break;
			}
			
			// finalize the sql statment to make sure it executed correctly
			if ( sqlite3_finalize(sqlStatement) == SQLITE_OK )
			{
				return TRUE;
			}
			else
			{
				m_error = [NSString stringWithFormat:@"Error : Failed to execute statement with message '%s'.", sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
				NSLog( @"%@", m_error);
#endif
			}
		}
	}
	
	return FALSE;
}

// ---------------------------------------------------------------
// Table has column
// ---------------------------------------------------------------
Boolean Database::hasTableColumn( NSString * _tableName, NSString * _columnName )
{	
	if ( m_database )
	{
		// create the sql statment
		NSString * sql = [NSString stringWithFormat:@"PRAGMA table_info('%@')", _tableName];
		
		// 
		sqlite3_stmt * sqlStatement = nil;
		if (sqlite3_prepare_v2(m_database, [sql UTF8String], -1, &sqlStatement, NULL) != SQLITE_OK) 
		{
			m_error = [NSString stringWithFormat:@"Error : in Sql %@, failed to prepare statement with message '%s'.", sql, sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error);
#endif
		}
		else
		{	
			Boolean hasColumn = FALSE;
			
			// step over each the first rows and grab the data.
			while (sqlite3_step(sqlStatement) == SQLITE_ROW) 
			{
				int count = sqlite3_data_count(sqlStatement);
				
				// grab the row data and place it into a dictionary.
				for ( int i=0; i<count; i++ )
				{
					NSString * columnName = [NSString stringWithUTF8String:(char *)sqlite3_column_name(sqlStatement, i)];
					if ( [columnName compare:@"name"] == NSOrderedSame )
					{
						if ( sqlite3_column_type(sqlStatement, i) == SQLITE_TEXT )
						{
							NSString * name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, i)];
							if ( [name compare:_columnName] == NSOrderedSame )
							{
								hasColumn = TRUE;
							}
						}
						break;
					}
				}
				
				if ( hasColumn )
				{
					break;
				}
			}
			
			// finalize the sql statment to make sure it executed correctly
			if ( sqlite3_finalize(sqlStatement) == SQLITE_OK )
			{
				return hasColumn;
			}
			else
			{
				m_error = [NSString stringWithFormat:@"Error : Failed to execute statement with message '%s'.", sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
				NSLog( @"%@", m_error);
#endif
			}
		}
	}
	
	return FALSE;
}

Boolean Database::tableColumns( NSString * _tableName, NSMutableArray * _results )
{
	if ( m_database )
	{
		// create the sql statment
		NSString * sql = [NSString stringWithFormat:@"PRAGMA table_info('%@')", _tableName];
		
		// prepare the statment
		sqlite3_stmt * sqlStatement = nil;
		if (sqlite3_prepare_v2(m_database, [sql UTF8String], -1, &sqlStatement, NULL) != SQLITE_OK) 
		{
			m_error = [NSString stringWithFormat:@"Error : in Sql %@, failed to prepare statement with message '%s'.", sql, sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
			NSLog( @"%@", m_error);
#endif
		}
		else
		{	
			[_results removeAllObjects];
			
			// step over each the first rows and grab the data.
			while (sqlite3_step(sqlStatement) == SQLITE_ROW) 
			{
				int count = sqlite3_data_count(sqlStatement);
				
				// grab the row data and place it into a dictionary.
				for ( int i=0; i<count; i++ )
				{
					NSString * columnName = [NSString stringWithUTF8String:(char *)sqlite3_column_name(sqlStatement, i)];
					if ( [columnName compare:@"name"] == NSOrderedSame )
					{
						if ( sqlite3_column_type(sqlStatement, i) == SQLITE_TEXT )
						{
							NSString * name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, i)];
							[_results addObject:name];
						}
					}
				}
			}
			
			// finalize the sql statment to make sure it executed correctly
			if ( sqlite3_finalize(sqlStatement) == SQLITE_OK )
			{
				return TRUE;
			}
			else
			{
				m_error = [NSString stringWithFormat:@"Error : Failed to execute statement with message '%s'.", sqlite3_errmsg(m_database)];
#if LOG_DB_ERRORS
				NSLog( @"%@", m_error);
#endif
			}
		}
	}
	
	return FALSE;
}

// ---------------------------------------------------------------
// update the table
// ---------------------------------------------------------------
Boolean Database::updateTable( NSString * create, NSString * insert, NSString * _name )
{
	NSString * renameTable = [NSString stringWithFormat:@"ALTER TABLE '%@' RENAME TO '%@Old'", _name, _name];
	
	execute(renameTable);
	execute(create);
	if ( execute(insert) )
	{
		NSString * dropTable = [NSString stringWithFormat:@"DROP TABLE '%@Old'", _name];
		execute(dropTable);		
	}
	else 
	{
		NSLog(@"FAILED to update table : %@", _name);
		NSString * dropTable		= [NSString stringWithFormat:@"DROP TABLE '%@'", _name];
		NSString * restoreTable		= [NSString stringWithFormat:@"ALTER TABLE '%@Old' RENAME TO '%@'", _name, _name];
		
		execute(dropTable);
		execute(restoreTable);
		
		return FALSE;
	}
	
	return TRUE;
}

#pragma mark ---------------------------------------------------------
#pragma mark === End Public Functions  ===
#pragma mark ---------------------------------------------------------

#pragma mark ---------------------------------------------------------
#pragma mark === Private Functions  ===
#pragma mark ---------------------------------------------------------

// ---------------------------------------------------------------
// copy and open the buddle or local database 
// ---------------------------------------------------------------
Boolean Database::createWithBundleDatabaseWithName(NSString* _name)
{
	// Get the main bundle for the app
	NSString *  bundelDatabaseFileName = [[NSBundle mainBundle] pathForResource:_name ofType:@"d"];
	
	// create the local database file name
	NSArray *	paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *	documentsDirectory = [paths objectAtIndex:0];
    NSString *	localDatabaseFileName = [documentsDirectory stringByAppendingPathComponent:_name];
	
	NSFileManager * fileManager = [NSFileManager defaultManager];
	bool bLocal		= [fileManager fileExistsAtPath:localDatabaseFileName];
	bool bBundle	= [fileManager fileExistsAtPath:bundelDatabaseFileName];
	
	if ( bLocal ||  bBundle )
	{
		if ( !bLocal )
		{
			// copy the bundle database over
			if ( ![fileManager copyItemAtPath:bundelDatabaseFileName toPath:localDatabaseFileName error:nil] )
			{
#if LOG_DB_ERRORS
				NSLog( @"Error : Failed to copy database %@ over to the user directory.", _name );
#endif
				return FALSE;
			}
		}
		
		// Open a connection to the database the database. 
		if (sqlite3_open([localDatabaseFileName UTF8String], &m_database) != SQLITE_OK) 
		{
			// close the database as something has gone wrong.
			sqlite3_close(m_database);
			m_database = nil;
#if LOG_DB_ERRORS
			NSLog( @"Error : Failed to open database %@ with message '%s'.", _name, sqlite3_errmsg(m_database));
#endif
		}
		else
		{
			return TRUE;
		}
	}	
	
	return FALSE;
}

// ---------------------------------------------------------------
// create or open the local database
// ---------------------------------------------------------------
Boolean Database::createAnEmptyDatabaseWithName(NSString* _name, NSString * _createStatement)
{
	// create the database in the sand box's resource directory.
	NSArray *	paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *	documentsDirectory = [paths objectAtIndex:0];
	NSString *	localDatabaseFileName = [documentsDirectory stringByAppendingPathComponent:_name];
	
	// Open a connection to the database the database. 
	if (sqlite3_open([localDatabaseFileName UTF8String], &m_database) != SQLITE_OK) 
	{
		// close the database as something has gone wrong.
		sqlite3_close(m_database);
		m_database = nil;
		
#if LOG_DB_ERRORS
		NSLog( @"Error : Failed to open database with message '%s'.", sqlite3_errmsg(m_database));
#endif
		return FALSE;
	}
	else if ( _createStatement ) // create the database table if one is provided
	{
		// create the sql statement
		sqlite3_stmt * createStatement;
		if (sqlite3_prepare_v2(m_database, [_createStatement UTF8String], -1, &createStatement, NULL) != SQLITE_OK) 
		{
#if LOG_DB_ERRORS
			NSLog( @"Error : Failed to prepare creation statement with message '%s'.", sqlite3_errmsg(m_database));
#endif
			// close the database as something has gone wrong.
			sqlite3_close(m_database);
			m_database = nil;
			
			return FALSE;
		}
		else
		{
			// try to create the table
			int ret = sqlite3_step(createStatement);
			sqlite3_finalize(createStatement);
			
			if ( ret != SQLITE_DONE )
			{
#if LOG_DB_ERRORS
				NSLog( @"Error : Failed to execute statement with message '%s'.", sqlite3_errmsg(m_database));
#endif
				// close the database as something has gone wrong.
				sqlite3_close(m_database);
				m_database = nil;

				return FALSE;
			}
		}
	}
	
	return TRUE;
}

#pragma mark ---------------------------------------------------------
#pragma mark === End Private Functions  ===
#pragma mark ---------------------------------------------------------