//
//  DatabaseUpdater.m
//  BathyScaphe
//
//  Created by Hori,Masaki on 07/02/03.
//  Copyright 2007 BathyScaphe Project. All rights reserved.
//

#import "DatabaseUpdater.h"

@interface DatabaseUpdater(ForSubclasses)
- (BOOL)useProgressPanel;

- (void)setInformationText:(NSString *)information;
@end

/*
 *	Version 0: 初期データベース
 *	Version 1: Version Table を導入
 *	Version 2: BoardInfoHistory 上のインデックスを修正
 *	Version 3: Version Table を廃止。 ThreadInfo Table に IsDatOchi カラムを追加
 *	Version 4: Favorites Table を廃止。 ThreadInfo Table に IsFavorite カラムを追加
 *	Version 5: BoardThreadInfoView を変更。 isCached, isUpdated, isNew, isHeadModified カラムを追加
 *	Version 6: This is mine!
 *	Version 7: ThreadInfo Table の ThreadLabelColumn と ThreadAboneTypeColumn に NOT NULL 制約を追加 
 */

@interface DatabaseUpdaterOneToTow : DatabaseUpdater
@end
@interface DatabaseUpdaterToThree : DatabaseUpdater
@end
@interface DatabaseUpdaterToFour : DatabaseUpdater
@end
@interface DatabaseUpdaterToFive : DatabaseUpdater
@end
@interface DatabaseUpdaterToSeven : DatabaseUpdater
@end


@implementation DatabaseUpdater

- (id)init
{
	[super init];
	
	if([self useProgressPanel]) {
		[NSBundle loadNibNamed:@"DatabaseUpdatePanel" owner:self];
		
		[self setInformationText:@""];
		
		NSRect mainScreenFrame = [[NSScreen mainScreen] visibleFrame];
		NSPoint center = NSMakePoint(NSMidX(mainScreenFrame), NSMidY(mainScreenFrame));
		
		NSRect windowFrame = [window frame];
		NSPoint origin = NSMakePoint(center.x - windowFrame.size.width / 2, center.y - windowFrame.size.height / 2 + 100);
		[window setFrameOrigin:origin];
		
		[window makeKeyAndOrderFront:nil];
		[progress setUsesThreadedAnimation:YES];
		[progress startAnimation:nil];
	}
	
	return self;
}
- (void)dealloc
{
	[progress stopAnimation:nil];
	[window close];
	
	[super dealloc];
}

- (BOOL)backupDatabase
{
	SQLiteDB *db = [[DatabaseManager defaultManager] databaseForCurrentThread];
	NSString *databasePath = [db databasePath];
	if(!databasePath) {
		NSLog(@"Could not open database");
		return NO;
	}
	
	NSString *backupPath = databasePath;
	NSFileManager *fm = [NSFileManager defaultManager];
	BOOL exist = YES;
	do {
		backupPath = [backupPath stringByAppendingString:@"~"];
		exist = [fm fileExistsAtPath:backupPath];
	} while(exist);
	
//	if(![fm copyPath:databasePath toPath:backupPath handler:nil]) {
    if (![fm copyItemAtPath:databasePath toPath:backupPath error:NULL]) {
		NSLog(@"Could not backup database");
		return NO;
	}
	
	return YES;
}

+ (BOOL)updateFrom:(NSInteger)fromVersion to:(NSInteger)toVersion
{
	BOOL result = YES;
	
	if(fromVersion < 0) return YES;
	
	if(fromVersion < 2 && toVersion >= 2) {
		result = [[[[DatabaseUpdaterOneToTow alloc] init] autorelease] updateDB];
	}
	if(!result) return result;
	
	if(fromVersion < 3 && toVersion >= 3) {
		result = [[[[DatabaseUpdaterToThree alloc] init] autorelease] updateDB];
	}
	if(!result) return result;
	
	if(fromVersion < 4 && toVersion >= 4) {
		result = [[[[DatabaseUpdaterToFour alloc] init] autorelease] updateDB];
	}
	if(!result) return result;
	
	if(fromVersion < 5 && toVersion >= 5) {
		result = [[[[DatabaseUpdaterToFive alloc] init] autorelease] updateDB];
	}
	if(!result) return result;
	
	if(fromVersion < 7 && toVersion >= 7) {
		result = [[[[DatabaseUpdaterToSeven alloc] init] autorelease] updateDB];
	}
	if(!result) return result;
	
	return result;
}

- (BOOL)updateVersion:(NSInteger)newVersion usingDB:(SQLiteDB *)db
{
	if (!db) return NO;
	
	[db performQuery : [NSString stringWithFormat: @"PRAGMA user_version = %ld;",
		(long)newVersion]];
	if([db lastErrorID] != noErr) return NO;
	
	return YES;
}

- (void)setInformationText:(NSString *)anInformation
{
	information.stringValue = anInformation;
	[information display];
}
+ (NSString *)localizableStringsTableName
{
	return @"DatabaseUpdater";
}
	
@end


/*	
 *	Version 0 -> 2
 *	Version 1 -> 2
 *	
 *	BoardInfoHistoryTableName 上の BoardIDColumn のインデックスが UNIQUE インデックスになっていたのを
 *	通常のインデックスに変更
 */
@implementation DatabaseUpdaterOneToTow
- (BOOL) updateDB
{
	BOOL isOK = NO;
	
	SQLiteDB *db = [[DatabaseManager defaultManager] databaseForCurrentThread];
	if (!db) return NO;
	
	if ([db beginTransaction]) {
		isOK = [db deleteIndexForColumn : BoardIDColumn inTable : BoardInfoHistoryTableName];
		if (!isOK) goto abort;
		
		isOK = [db createIndexForColumn : BoardIDColumn
								inTable : BoardInfoHistoryTableName
							   isUnique : NO];
		if (!isOK) goto abort;
		
		if(![self updateVersion : 2 usingDB : db]) goto abort;
		
		[db commitTransaction];
		[db save];
	}
	
	return isOK;
	
abort:
	NSLog(@"Fail Database operation. Reason: \n%@", [db lastError]);
	[db rollbackTransaction];
	return NO;
}
@end

/*
 *	Version 2 -> 3
 *	
 *	ThreadInfo Table に IsDatOchiColumn カラムを追加
 */
@implementation DatabaseUpdaterToThree
- (BOOL) updateDB
{
	BOOL isOK = NO;
	
	SQLiteDB *db = [[DatabaseManager defaultManager] databaseForCurrentThread];
	if (!db) return NO;
	
	if ([db beginTransaction]) {
		id query = [NSString stringWithFormat: @"ALTER TABLE %@ ADD COLUMN %@ %@ DEFAULT 0 CHECK(%@ IN (0,1))",
			ThreadInfoTableName, IsDatOchiColumn, INTEGER_NOTNULL, IsDatOchiColumn];
		[db cursorForSQL : query];
		if ([db lastErrorID] != 0) goto abort;
		
		if(![self updateVersion : 3 usingDB : db]) goto abort;
		
		[db commitTransaction];
		[db save];
	}
	
	return isOK;
	
abort:
		NSLog(@"Fail Database operation. Reason: \n%@", [db lastError]);
	[db rollbackTransaction];
	return NO;
}
@end

/*
 *	Version 3 -> 4
 *	
 *	ThreadInfo Table に IsFavoriteColumn カラムを追加
 */
@implementation DatabaseUpdaterToFour

- (BOOL) updateDB
{
	return YES;
}
@end

/*
 *	Version 4 -> 5
 *	
 *	BoardThreadInfoView を変更。 isCached, isUpdated, isNew, isHeadModified カラムを追加
 */
@implementation DatabaseUpdaterToFive
//- (BOOL) updateDB
//{
//	BOOL isOK = NO;
//	
//	SQLiteDB *db = [[DatabaseManager defaultManager] databaseForCurrentThread];
//	if (!db) return NO;
//	
//	if ([db beginTransaction]) {
//		id query = [NSString stringWithFormat: @"DROP VIEW %@;", BoardThreadInfoViewName];
//		[db cursorForSQL : query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		query = [NSMutableString stringWithFormat: @"CREATE VIEW %@ AS\n", BoardThreadInfoViewName];
//		[query appendFormat: @"\tSELECT *, (%@ - %@) AS %@\n",
//		 NumberOfAllColumn, NumberOfReadColumn, NumberOfDifferenceColumn];
//		[query appendFormat: @", NOT(%@ - %ld) AS %@\n",
//		 ThreadStatusColumn, (long)ThreadLogCachedStatus, IsCachedColumn];
//		[query appendFormat: @", NOT(%@ - %ld) AS %@\n",
//		 ThreadStatusColumn, (long)ThreadUpdatedStatus, IsUpdatedColumn];
//		[query appendFormat: @", NOT(%@ - %ld) AS %@\n",
//		 ThreadStatusColumn, (long)ThreadNewCreatedStatus, IsNewColumn];
//		[query appendFormat: @", NOT(%@ - %ld) AS %@\n",
//		 ThreadStatusColumn, (long)ThreadHeadModifiedStatus, IsHeadModifiedColumn];
//		[query appendFormat: @"FROM %@ INNER JOIN %@ USING(%@) ",
//		 ThreadInfoTableName, BoardInfoTableName, BoardIDColumn];
//		
//		[db cursorForSQL : query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		
//		if(![self updateVersion : 5 usingDB : db]) goto abort;
//		
//		[db commitTransaction];
//		[db save];
//	}
//	
//	return isOK;
//	
//abort:
//	NSLog(@"Fail Database operation. Reason: \n%@", [db lastError]);
//	[db rollbackTransaction];
//	return NO;
//}
@end


/*
 *	Version 5 -> 7
 *	
 *	BoardThreadInfoView を変更。 isCached, isUpdated, isNew, isHeadModified カラムを追加
 */
@implementation DatabaseUpdaterToSeven
//- (BOOL)useProgressPanel
//{
//	return YES;
//}
//- (BOOL) updateDB
//{
//	BOOL isOK = NO;
//	
//	[self backupDatabase];
//	[self setInformationText:[self localizedString:@"prepare database"]];
//		
//	SQLiteDB *db = [[DatabaseManager defaultManager] databaseForCurrentThread];
//	if (!db) return NO;
//	
//	if ([db beginTransaction]) {
//		NSString *tempTable = @"temporaryTable";
//		
//		// create table copy on memory
//		id query = [NSString stringWithFormat: @"CREATE TEMP TABLE %@ AS SELECT * FROM %@",
//					tempTable, ThreadInfoTableName];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		// drop original
//		[db cursorForSQL:@"DROP TABLE ThreadInfo"];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		query = [NSString stringWithFormat:
//				 @"CREATE TABLE %@"
//				 "(%@ INTEGER NOT NULL ,"
//				 "%@ INTEGER NOT NULL , "
//				 "%@ TEXT NOT NULL , "
//				 "%@ NUMERIC, "
//				 "%@ NUMERIC , "
//				 "%@ NUMERIC , "
//				 "%@ NUMERIC , "
//				 "%@ NUMERIC , "
//				 "%@ INTEGER NOT NULL DEFAULT 0 CHECK(%@ >= 0), "
//				 "%@ INTEGER NOT NULL DEFAULT 0 CHECK(%@ >= 0), "
//				 "%@ INTEGER NOT NULL DEFAULT 0 CHECK(%@ IN (0,1)))"
//				 ,
//				 ThreadInfoTableName,
//				 BoardIDColumn,
//				 ThreadIDColumn,
//				 ThreadNameColumn,
//				 NumberOfAllColumn,
//				 NumberOfReadColumn,
//				 ModifiedDateColumn,
//				 LastWrittenDateColumn,
//				 ThreadStatusColumn,
//				 ThreadAboneTypeColumn, ThreadAboneTypeColumn,
//				 ThreadLabelColumn, ThreadLabelColumn,
//				 IsDatOchiColumn, IsDatOchiColumn];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		// create indexes
//		query = [NSString stringWithFormat:
//				 @"CREATE INDEX ThreadInfo_boardID_IDX ON %@ (%@)",
//				 ThreadInfoTableName, BoardIDColumn];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		query = [NSString stringWithFormat:
//				 @"CREATE UNIQUE INDEX ThreadInfo_boardID_threadID_IDX ON %@ (%@,%@)",
//				 ThreadInfoTableName, BoardIDColumn, ThreadIDColumn];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		query = [NSString stringWithFormat:
//				 @"CREATE INDEX ThreadInfo_threadID_IDX ON %@ (%@)",
//				 ThreadInfoTableName, ThreadIDColumn];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		
//		// register original data
//		[self setInformationText:[self localizedString:@"register to new database"]];
//		id <SQLiteMutableCursor> result;
//		
//		query = [NSString stringWithFormat:@"SELECT count(*) AS c FROM %@", tempTable];
//		result = [db cursorForSQL:query];
//		if ([db lastErrorID] != 0) goto abort;
//		[progress setMaxValue:[[result valueForColumn:@"c" atRow:0] integerValue]];
//		progress.doubleValue = 0;
//		[progress setIndeterminate:NO];
//		
//		query = [NSString stringWithFormat:
//				 @"SELECT * FROM %@", tempTable];
//		result = [db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		query = [[NSString alloc] initWithFormat:
//				 @"INSERT INTO %@"
//				 "(%@, %@, %@, "
//				 "%@, %@, "
//				 "%@, %@, "
//				 "%@, %@,  "
//				 "%@, %@) "
//				 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
//				 ,
//				 ThreadInfoTableName,
//				 BoardIDColumn, ThreadIDColumn, ThreadNameColumn,
//				 NumberOfAllColumn, NumberOfReadColumn,
//				 ModifiedDateColumn, LastWrittenDateColumn,
//				 ThreadStatusColumn, ThreadAboneTypeColumn,
//				 ThreadLabelColumn, IsDatOchiColumn];
//		SQLiteReservedQuery *rQuery = [db reservedQuery:query];
//		
//		id aNull = [NSNull null];
//		NSUInteger i, count;
//		for(i = 0, count = [result rowCount]; i < count; i++) {
//			id <SQLiteRow> row = [result rowAtIndex:i];
//			
//			[rQuery cursorWithFormat:"jjsjjjjjjjj",
//			 [row valueForColumn:BoardIDColumn],
//			 [row valueForColumn:ThreadIDColumn],
//			 [row valueForColumn:ThreadNameColumn],
//			 [row valueForColumn:NumberOfAllColumn],
//			 [row valueForColumn:NumberOfReadColumn],
//			 [row valueForColumn:ModifiedDateColumn],
//			 [row valueForColumn:LastWrittenDateColumn],
//			 [row valueForColumn:ThreadStatusColumn],
//			 [row valueForColumn:ThreadAboneTypeColumn] == aNull ? [NSNumber numberWithInt:0] : [row valueForColumn:ThreadAboneTypeColumn],
//			 [row valueForColumn:ThreadLabelColumn] == aNull ? [NSNumber numberWithInt:0] : [row valueForColumn:ThreadLabelColumn],
//			 [row valueForColumn:IsDatOchiColumn]
//			 ];
//			
//			[NSApp nextEventMatchingMask:NSAnyEventMask
//							   untilDate:[NSDate dateWithTimeIntervalSinceNow:0.000001]
//								  inMode:NSDefaultRunLoopMode
//								 dequeue:NO];
//			progress.doubleValue += 1;
//		}
//		
//		// drop temporary table
//		query = [NSString stringWithFormat: @"DROP TABLE %@", tempTable];
//		[db cursorForSQL: query];
//		if ([db lastErrorID] != 0) goto abort;
//		
//		if(![self updateVersion: 7 usingDB: db]) goto abort;
//		
//		[db commitTransaction];
//		[db save];
//	}
//	
//	// vacuum
//	[self setInformationText:[self localizedString:@"finalize database"]];
//	[progress setIndeterminate:YES];
//	[progress startAnimation:nil];
//	[db cursorForSQL:@"VACUUM"];
//	if ([db lastErrorID] != 0) goto abort;
//		
//	return isOK;
//	
//abort:
//	NSLog(@"Fail Database operation. Reason: \n%@", [db lastError]);
//	[db rollbackTransaction];
//	
//	return NO;
//}
@end
