//
//  DBManager.m
//  SQLite Handler Class - to communicate with database.
//
//  Created by Ganpat on 24/07/2017.
//  Copyright © 2016. All rights reserved.
//

#import "DBManager.h"

static DBManager *sharedInstance=nil;
static sqlite3_stmt *statement=nil;

@implementation DBManager

+(DBManager*)getSharedInstance {
    if(!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}

-(BOOL)createDB {
    NSString *docDir;
    NSArray *dirPaths;
    
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docDir = dirPaths[0];
    databasePath = [[NSString alloc]initWithString:[docDir stringByAppendingPathComponent: @"foc.db"]];
    
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath:databasePath] == NO) {
        [self createAllTables];
    } else {
        // Check Table is Exist or Not
        int intCount = 0;
        sqlite3 *dbConnection;
        NSString *strTables = @"SELECT name FROM sqlite_master WHERE type='table' AND name='tblUser';";
        if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
            const char *sqlStatement = [strTables UTF8String];
            if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
                if (sqlite3_step(statement) == SQLITE_ROW) {
                    intCount = sqlite3_data_count(statement);
                }
            }
            sqlite3_finalize(statement);
            sqlite3_close(dbConnection);
        }
        if (intCount == 0) {
            [self createAllTables];
        }
    }
    return isSuccess;
}

-(BOOL)createAllTables {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        
        NSString *strCreateUserTable = @"CREATE TABLE tblUser ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "userId INTEGER, "
        "firstName TEXT, "
        "lastName TEXT, "
        "schoolEmail TEXT, "
        "secondEmail TEXT, "
        "gender TEXT, "
        "status TEXT, "
        "phone TEXT, "
        "version INTEGER)";
        
        NSString *strCreatePhotoTable = @"CREATE TABLE tblPhoto ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "userId INTEGER, "
        "size INTEGER, "
        "type TEXT, "
        "info TEXT, "
        "key TEXT, "
        "version INTEGER, "
        "photo BLOB)";
        
        NSString *strCreateRequestTable = @"CREATE TABLE tblFriendRequest ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "requesterId INTEGER, "
        "requested INTEGER)";
        
        NSString *strCreateMyFriendTable = @"CREATE TABLE tblMyFriend ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "friendId INTEGER, "
        "favorite INTEGER)";
        
        NSString *strCreateBlockTable = @"CREATE TABLE tblBlock ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "blockId INTEGER, "
        "blocking INTEGER)";
        
        // Unique Chat
        NSString *strCreateUChatDetailTable = @"CREATE TABLE tblUChatDetail ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "type INTEGER, "
        "hangoutId INTEGER, "
        "title TEXT)";
        
        NSString *strCreateUChatMemberTable = @"CREATE TABLE tblUChatMember ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "userId INTEGER)";
        
        NSString *strCreateUChatSettingTable = @"CREATE TABLE tblUChatSetting ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "noti INTEGER)";
        
        // Multiple Chat
        NSString *strCreateMChatDetailTable = @"CREATE TABLE tblMChatDetail ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "type INTEGER, "
        "start INTEGER, "
        "title TEXT)";
        
        NSString *strCreateMChatMemberTable = @"CREATE TABLE tblMChatMember ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "userId INTEGER)";
        
        NSString *strCreateMChatSettingTable = @"CREATE TABLE tblMChatSetting ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "noti INTEGER)";
        
        // Hangout
        NSString *strCreateHangoutDetailTable = @"CREATE TABLE tblHangoutDetail ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "hangoutId INTEGER, "
        "ownerId INTEGER, "
        "category TEXT, "
        "activity TEXT, "
        "place TEXT, "
        "hide INTEGER, "
        "hangoutTime INTEGER, "
        "latitude FLOAT, "
        "longitude FLOAT, "
        "multichatId INTEGER, "
        "goMember INTEGER, "
        "ngMember INTEGER, "
        "isNoti INTEGER, "
        "isCancelled INTEGER, "
        "leave INTEGER, "
        "remove INTEGER, "
        "details TEXT, "
        "scope INTEGER, "
        "start INTEGER)";
        
        NSString *strCreateHangoutMemberTable = @"CREATE TABLE tblHangoutMember ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "hangoutId INTEGER, "
        "userId INTEGER, "
        "going INTEGER, "
        "notgoing INTEGER)";
        
        NSString *strCreateHChatDetailTable = @"CREATE TABLE tblHChatDetail ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "chatId INTEGER, "
        "type INTEGER, "
        "hangoutId INTEGER, "
        "title TEXT)";
        
        NSString *strCreateSyncVersionTable = @"CREATE TABLE tblSyncVersion ("
        "userId INTEGER PRIMARY KEY, "
        "confSvr INTEGER, "
        "profileVersion INTEGER, "
        "photoVersion INTEGER, "
        "settingVersion INTEGER, "
        "blockUserVersion REAL, "
        "reqFriendVersion REAL, "
        "friendVersion REAL, "
        "onCampusVersion REAL, "
        "ucListVersion REAL, "
        "mcListVersion REAL, "
        "hcListVersion REAL)";
        
        NSString *strCreateOnCampusTable = @"CREATE TABLE tblOnCampus ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "userId INTEGER, "
        "time INTEGER, "
        "isOn INTEGER)";
        
        NSString *strCreateChatVersionTable = [NSString stringWithFormat:@"CREATE TABLE %@ (id INTEGER PRIMARY KEY AUTOINCREMENT, chatId INTEGER, chatType INTEGER, detailVersion REAL, memberVersion INTEGER, hMemberVersion REAL, messageVersion INTEGER, readMsgVersion INTEGER)", TBL_CHAT_VERSION];
        
        NSString *strCreateProfileVersionTable = [NSString stringWithFormat:@"CREATE TABLE %@ (id INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER, photoVersion INTEGER, serverPhotoVersion INTEGER, profileVersion INTEGER)", TBL_PROFILE_PHOTO_VERSION];
        
        const char *sql_stmt = [[NSString stringWithFormat:@"%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@;%@", strCreateUserTable, strCreatePhotoTable, strCreateRequestTable, strCreateMyFriendTable, strCreateBlockTable, strCreateUChatDetailTable, strCreateUChatMemberTable, strCreateUChatSettingTable, strCreateMChatDetailTable, strCreateMChatMemberTable, strCreateMChatSettingTable, strCreateHangoutDetailTable, strCreateHangoutMemberTable, strCreateHChatDetailTable, strCreateSyncVersionTable, strCreateOnCampusTable, strCreateChatVersionTable, strCreateProfileVersionTable] UTF8String];
        
        char *err_msg;
        if (sqlite3_exec(dbConnection, sql_stmt, NULL, NULL, &err_msg) != SQLITE_OK) {
            isSuccess = NO;
            NSLog(@"%s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_close(dbConnection);
        return isSuccess;
    } else {
        isSuccess=NO;
        NSLog(@"%s", sqlite3_errmsg(dbConnection));
    }
    
    return isSuccess;
}

-(BOOL)deleteAllTables {
    BOOL isSuccess = false;
    NSString *strTables = @"select 'drop table ' || name || ';' from sqlite_master where type = 'table';";
    sqlite3 *dbConnection;
    NSMutableArray *aryTables = [[NSMutableArray alloc] init];
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        const char *sqlStatement = [strTables UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                [aryTables addObject:[NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 0)]];
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    for (int i=0; i<aryTables.count; i++) {
        isSuccess = [self executeQuery: aryTables[i]];
    }
    return isSuccess;
}

-(BOOL)executeQuery:(NSString *)strSQL {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        const char *query_stmt = [strSQL UTF8String];
        char* errmsg;
        sqlite3_exec(dbConnection, query_stmt, NULL, NULL, &errmsg);
        if (errmsg != nil) {
            NSLog(@"%s", query_stmt);
            NSLog(@"%s", errmsg);
        } else {
            isSuccess = true;
        }
        sqlite3_close(dbConnection);
    }
    return isSuccess;
}

-(int)getRecordCountForTable:(NSString *)table Column:(NSString *)column WhereClause:(NSString *)where {
    sqlite3 *dbConnection;
    int intCount = 0;
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if ([where isEqualToString:@""]) {
            querySql = [NSString stringWithFormat:@"SELECT %@ FROM %@", column, table];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT %@ FROM %@ WHERE %@", column, table, where];
        }
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            if (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                intCount = sqlite3_data_count(compiledStatement);
            } else {
                NSLog(@"getRecordCountForTable %@ - Error: %s", table, sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"getRecordCountForTable %@ - Error: %s", table, sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return intCount;
}

-(NSDictionary *)isRecordSavedForUserId:(int)userId Table:(NSString *)table Column:(NSString *)column {
    sqlite3 *dbConnection;
    int intCount = 0;
    int intVer   = 0;
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT %@ FROM %@ WHERE userId=%d", column, table, userId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            if(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                intCount = sqlite3_data_count(compiledStatement);
                intVer = sqlite3_column_int(compiledStatement, 0);
            } else {
                NSLog(@"isRecordSavedForUserId: %d Column:%@ Table:%@ - Step Error: %s", userId, column, table, sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"isRecordSavedForUserId %d Column:%@ Table:%@ - PrepareV2 Error: %s", userId, column, table, sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return @{@"count":@(intCount), @"version":@(intVer)};
}

#pragma mark
#pragma mark User Profile

-(void)setUserProfile:(UserProfile *)userProfile {
    NSDictionary *dicResult = [self isRecordSavedForUserId:[userProfile.userId intValue] Table:@"tblUser" Column:@"version"];
    int intCount = [dicResult[@"count"] intValue];
    //int intVer = [dicResult[@"version"] intValue];
    
    if (intCount <= 0) {
        // Add User Profile
        [self addUserProfile:userProfile];
    } else {
        //if ([userProfile.version intValue] > intVer) {
            // Update User Profile
            [self updateUserProfile:userProfile];
        //}
    }
}

-(BOOL)addUserProfile:(UserProfile *)userProfile {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    //int intId = [self getRecordCountForTable:@"tblUser" Column:@"id"];
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *sqlInsert = @"insert into tblUser (userId, firstName, lastName, schoolEmail, secondEmail, gender, status, phone, version) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        const char *insert_stmt = [sqlInsert UTF8String];
        
        if (sqlite3_prepare_v2(dbConnection, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            //sqlite3_bind_int(statement,  1, intId+1);
            sqlite3_bind_int(statement,  1, [userProfile.userId intValue]);
            sqlite3_bind_text(statement, 2, [userProfile.firstName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [userProfile.lastName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 4, [userProfile.schoolEmail UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 5, [userProfile.secondlEmail UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 6, [userProfile.gender UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 7, [userProfile.status UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 8, [userProfile.phone UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement,  9, [userProfile.version intValue]);
            
            if (sqlite3_step(statement) == SQLITE_DONE) {
                isSuccess = true;
            } else {
                NSLog(@"addUserProfile - error %s",sqlite3_errmsg(dbConnection));
            }
            
            // add User photo
            if (userProfile.photo != nil) {
                [self addUserPhoto:userProfile.photo];
                [self setProfileAndPhotoVersion:[userProfile.userId intValue] ProfileVersion:[userProfile.version intValue] PhotoVersion:[userProfile.photo.v intValue] DoUpdate:true];
            } else {
                [self setProfileAndPhotoVersion:[userProfile.userId intValue] ProfileVersion:[userProfile.version intValue] PhotoVersion:0 DoUpdate:true];
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(dbConnection);
    }
    return isSuccess;
}

-(BOOL)updateUserProfile:(UserProfile *)userProfile {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE tblUser SET schoolEmail=\"%@\", gender=\"%@\", version=%d", userProfile.schoolEmail, userProfile.gender, [userProfile.version intValue]];
    if ((userProfile.firstName != nil) && (![userProfile.firstName isEqualToString:@""])) {
        strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@", firstName=\"%@\"", userProfile.firstName]];
    }
    if ((userProfile.lastName != nil) && (![userProfile.lastName isEqualToString:@""])) {
        strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@", lastName=\"%@\"", userProfile.lastName]];
    }
    if ((userProfile.secondlEmail != nil) && (![userProfile.secondlEmail isEqualToString:@""])) {
        strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@", secondEmail=\"%@\"", userProfile.secondlEmail]];
    }
    if ((userProfile.status != nil) && (![userProfile.status isEqualToString:@""])) {
        strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@", status=\"%@\"", userProfile.status]];
    }
    if ((userProfile.phone != nil) && (![userProfile.phone isEqualToString:@""])) {
        strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@", phone=\"%@\"", userProfile.phone]];
    }
    strSQL = [strSQL stringByAppendingString:[NSString stringWithFormat:@" WHERE userId=%d", [userProfile.userId intValue]]];
    
    isSuccess = [self executeQuery:strSQL];
    
    if (userProfile.photo != nil) {
        [self setProfileAndPhotoVersion:[userProfile.userId intValue] ProfileVersion:[userProfile.version intValue] PhotoVersion:[userProfile.photo.v intValue] DoUpdate:true];
    } else {
        [self setProfileAndPhotoVersion:[userProfile.userId intValue] ProfileVersion:[userProfile.version intValue] PhotoVersion:0 DoUpdate:true];
    }
    
    return isSuccess;
}

-(UserProfile *)getUserProfileForUserId:(int)userId {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    UserProfile *userProfile = [[UserProfile alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM tblUser WHERE userId=%d", userId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            if (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                userProfile.userId = @(sqlite3_column_int(compiledStatement, 1));
                userProfile.firstName = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 2)];
                userProfile.lastName = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                userProfile.schoolEmail = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                if (sqlite3_column_text(compiledStatement, 5) != nil) {
                    userProfile.secondlEmail = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 5)];
                } else {
                    userProfile.secondlEmail = @"";
                }
                userProfile.gender = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 6)];
                if (sqlite3_column_text(compiledStatement, 7) != nil) {
                    userProfile.status = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 7)];
                } else {
                    userProfile.status = @"";
                }
                if (sqlite3_column_text(compiledStatement, 8) != nil) {
                    userProfile.phone = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 8)];
                } else {
                    userProfile.phone = @"";
                }
                userProfile.version = @(sqlite3_column_int(compiledStatement, 9));
                userProfile.photo = [self getUserPhotoForUserId:userId];
                isSuccess = true;
            } else {
                NSLog(@"getUserProfileForUserId - Error: %s", sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"getUserProfileForUserId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return userProfile;
    } else {
        return nil;
    }
}

-(NSArray *)getAllUserProfile {
    sqlite3 *dbConnection;
    NSMutableArray *aryProfile = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM tblUser"];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                UserProfile *userProfile = [[UserProfile alloc] init];
                
                userProfile.userId = @(sqlite3_column_int(compiledStatement, 1));
                userProfile.firstName = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 2)];
                userProfile.lastName = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                userProfile.schoolEmail = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                if (sqlite3_column_text(compiledStatement, 5) != nil) {
                    userProfile.secondlEmail = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 5)];
                } else {
                    userProfile.secondlEmail = @"";
                }
                userProfile.gender = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 6)];
                if (sqlite3_column_text(compiledStatement, 7) != nil) {
                    userProfile.status = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 7)];
                } else {
                    userProfile.status = @"";
                }
                if (sqlite3_column_text(compiledStatement, 8) != nil) {
                    userProfile.phone = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 8)];
                } else {
                    userProfile.phone = @"";
                }
                userProfile.version = @(sqlite3_column_int(compiledStatement, 9));
                userProfile.photo = [self getUserPhotoForUserId:[userProfile.userId intValue]];
                
                [aryProfile addObject:userProfile];
            }
        } else {
            NSLog(@"getAllUserProfile - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    if (aryProfile.count > 0) {
        return aryProfile;
    } else {
        return nil;
    }
}

#pragma mark
#pragma mark User Photo

-(void)setUserPhoto:(UserPhoto *)userPhoto {
    NSDictionary *dicResult = [self isRecordSavedForUserId:[userPhoto.userId intValue] Table:@"tblPhoto" Column:@"version"];
    int intCount = [dicResult[@"count"] intValue];
    int intVer = [dicResult[@"version"] intValue];
    
    if (intCount <= 0) {
        // Add User Photo
        [self addUserPhoto:userPhoto];
    } else {
        if ([userPhoto.v intValue] > intVer) {
            // Update User Photo
            [self updateUserPhoto:userPhoto];
        }
    }
}

-(BOOL)addUserPhoto:(UserPhoto *)userPhoto {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    //int intId = [self getRecordCountForTable:@"tblPhoto" Column:@"id"];
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *sqlInsert = @"insert into tblPhoto (userId, size, type, info, key, version, photo) VALUES (?, ?, ?, ?, ?, ?, ?)";
        const char *insert_stmt = [sqlInsert UTF8String];
        
        if (sqlite3_prepare_v2(dbConnection, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            //sqlite3_bind_int(statement,  1, intId+1);
            sqlite3_bind_int(statement,  1, [userPhoto.userId intValue]);
            sqlite3_bind_int(statement,  2, [userPhoto.size intValue]);
            sqlite3_bind_text(statement, 3, [userPhoto.type UTF8String], -1, SQLITE_TRANSIENT);
            if (userPhoto.info != nil) {
                sqlite3_bind_text(statement, 4, [userPhoto.info UTF8String], -1, SQLITE_TRANSIENT);
            } else {
                sqlite3_bind_text(statement, 4, "", -1, SQLITE_TRANSIENT);
            }
            if (userPhoto.key != nil) {
                sqlite3_bind_text(statement, 5, [userPhoto.key UTF8String], -1, SQLITE_TRANSIENT);
            } else {
                sqlite3_bind_text(statement, 5, "", -1, SQLITE_TRANSIENT);
            }
            sqlite3_bind_int(statement,  6, [userPhoto.v intValue]);
            sqlite3_bind_blob(statement, 7, [userPhoto.dataImage bytes], [userPhoto.dataImage length], SQLITE_TRANSIENT);
            
            if (sqlite3_step(statement) == SQLITE_DONE) {
                isSuccess = true;
            } else {
                NSLog(@"addUserPhoto - error %s",sqlite3_errmsg(dbConnection));
            }
        }
        sqlite3_close(dbConnection);
    }
    return isSuccess;
}

-(BOOL)updateUserPhoto:(UserPhoto *)userPhoto {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *sqlInsert = @"update tblPhoto set size=?, type=?, info=?, key=?, version=?, photo=? where userId=?";
        const char *insert_stmt = [sqlInsert UTF8String];
        
        if (sqlite3_prepare_v2(dbConnection, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            
            sqlite3_bind_int(statement,  1, [userPhoto.size intValue]);
            sqlite3_bind_text(statement, 2, [userPhoto.type UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [userPhoto.info UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 4, [userPhoto.key UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement,  5, [userPhoto.v intValue]);
            sqlite3_bind_blob(statement, 6, [userPhoto.dataImage bytes], [userPhoto.dataImage length], SQLITE_TRANSIENT);
            sqlite3_bind_int(statement,  7, [userPhoto.userId intValue]);
            
            if (sqlite3_step(statement) == SQLITE_DONE) {
                isSuccess = true;
            } else {
                NSLog(@"updateUserPhoto - error %s",sqlite3_errmsg(dbConnection));
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return isSuccess;
}

-(UserPhoto *)getUserPhotoForUserId:(int)userId {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    UserPhoto *userPhoto = [[UserPhoto alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT userId, size, type, info, key, version, photo FROM tblPhoto WHERE userId=%d", userId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            if (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                userPhoto.userId = @(sqlite3_column_int(compiledStatement, 0));
                userPhoto.size = @(sqlite3_column_int(compiledStatement, 1));
                userPhoto.type = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 2)];
                if (sqlite3_column_text(compiledStatement, 3) != nil) {
                    userPhoto.info = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                } else {
                    userPhoto.info = @"";
                }
                if (sqlite3_column_text(compiledStatement, 4) != nil) {
                    userPhoto.key = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                } else {
                    userPhoto.key = @"";
                }
                userPhoto.v = @(sqlite3_column_int(compiledStatement, 5));
                if (sqlite3_column_blob(compiledStatement, 6) != nil) {
                    const void *bytes = sqlite3_column_blob(compiledStatement, 6);
                    int length = sqlite3_column_bytes(compiledStatement, 6);
                    userPhoto.dataImage = [[NSData alloc] initWithBytes:bytes length:length];
                } else {
                    userPhoto.dataImage = nil;
                }
                
                isSuccess = true;
            } else {
                NSLog(@"getUserPhotoForUserId - Error: %s", sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"getUserPhotoForUserId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return userPhoto;
    } else {
        return nil;
    }
}


#pragma mark
#pragma mark Friend Request

-(BOOL)addFriendRequest:(int)requeterId Requested:(int)requested {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblFriendRequest (requesterId, requested) VALUES (%d, %d)", requeterId, requested];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)deleteFriendRequest:(int)requesterId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from tblFriendRequest WHERE requesterId=%d", requesterId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}

-(NSMutableArray *)getFriendRequestIsSent:(int)isRequested {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT requesterId FROM tblFriendRequest WHERE requested=%d", isRequested];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                [aryUserIds addObject:@(sqlite3_column_int(compiledStatement, 0))];
            }
        } else {
            NSLog(@"getFriendRequestIsSent - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}

#pragma mark
#pragma mark My Friend

-(BOOL)addMyFriend:(int)friendId Favorite:(int)favorite {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblMyFriend (friendId, favorite) VALUES (%d, %d)", friendId, favorite];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(NSMutableArray *)getMyFriendIds:(BOOL)favorited {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (favorited) {
            querySql = [NSString stringWithFormat:@"SELECT friendId FROM tblMyFriend WHERE favorite=1"];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT friendId FROM tblMyFriend WHERE favorite=0"];
        }
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                [aryUserIds addObject:@(sqlite3_column_int(compiledStatement, 0))];
            }
        } else {
            NSLog(@"getMyFriendIds - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}

-(BOOL)isMyFriend:(int)friendId {
    sqlite3 *dbConnection;
    BOOL result = false;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        querySql = [NSString stringWithFormat:@"SELECT friendId FROM tblMyFriend WHERE friendId=%d", friendId];
        
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                //result = sqlite3_column_int(compiledStatement, 0);
                result = true;
            }
        } else {
            NSLog(@"isMyFriend - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return result;
}

-(BOOL)isFriendFavorited:(int)friendId {
    sqlite3 *dbConnection;
    BOOL result = false;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        querySql = [NSString stringWithFormat:@"SELECT favorite FROM tblMyFriend WHERE friendId=%d", friendId];
        
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                result = sqlite3_column_int(compiledStatement, 0);
            }
        } else {
            NSLog(@"isFriendFavorited - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return result;
}

-(BOOL)setMyFriendFavoriteUserId:(int)userId Favorite:(int)favorite {
    BOOL isSuccess = false;
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE tblMyFriend SET favorite=%d WHERE friendId=%d", favorite, userId];
    isSuccess = [self executeQuery:updateSQL];
    return true;
}

-(BOOL)removeMyFriendWithId:(int)userId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from tblMyFriend WHERE friendId=%d", userId];
    isSuccess = [self executeQuery:deleteSQL];
    return true;
}

#pragma mark
#pragma mark Block Friend

-(BOOL)blockUser:(int)blockId Blocking:(int)blocking {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblBlock (blockId, blocking) VALUES (%d, %d)", blockId, blocking];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(NSMutableArray *)getBlockedIds:(BOOL)blocking {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (blocking) {
            querySql = [NSString stringWithFormat:@"SELECT blockId FROM tblBlock WHERE blocking=1"];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT blockId FROM tblBlock WHERE blocking=0"];
        }
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                [aryUserIds addObject:@(sqlite3_column_int(compiledStatement, 0))];
            }
        } else {
            NSLog(@"getBlockedIds - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}
/*
-(BOOL)setBlockStatusUserId:(int)userId Blocking:(int)blocking {
    BOOL isSuccess = false;
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE tblBlock SET blocking=%d WHERE blockId=%d", blocking, userId];
    isSuccess = [self executeQuery:updateSQL];
    return isSuccess;
}*/

-(BOOL)unBlockUserWithId:(int)userId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from tblBlock WHERE blockId=%d", userId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}

#pragma mark
#pragma mark Unique Chat

-(BOOL)syncUChatWithChatId:(int)chatId Type:(int)type UserId:(int)userId Noti:(int)noti MemberId:(int)memberId {
    BOOL isSuccess = false;
    
    // CHAT VERSION SYNC
    int intCount = [self getRecordCountForTable:TBL_CHAT_VERSION Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d AND chatType=%d", chatId, type]];
    if (intCount <= 0) {
        // Add new
        NSString *querySql = [NSString stringWithFormat:@"insert into %@ (chatId, chatType, detailVersion, memberVersion, hMemberVersion) values (%d, %d, 0.1, 0, 0.1)", TBL_CHAT_VERSION, chatId, type];
        isSuccess = [self executeQuery:querySql];
    }
    
    // CHAT DETAILS SYNC
    intCount = [self getRecordCountForTable:TBL_UCHAT_DETAIL Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d", chatId]];
    if (intCount > 0) {
        // update
        [self setUChatSettingWithChatId:chatId EnableNotification:noti];
    } else {
        // Add
        [self createUChatWithChatId:chatId Type:type UserId:userId];
    }
    return isSuccess;
}

-(BOOL)createUChatWithChatId:(int)chatId Type:(int)type UserId:(int)userId {
    BOOL isSuccess = false;
    NSString *sqlInsert;
    if (type == 0) {
        sqlInsert = [NSString stringWithFormat:@"insert into tblUChatDetail (chatId, type) VALUES (%d, %d)", chatId, type];
    } else {
        //sqlInsert = [NSString stringWithFormat:@"insert into tblUChatDetail (chatId, type, hangoutid, title) VALUES (%d, %d, %d, %@)", chatId, type, hangoutId, title];
    }
    isSuccess = [self executeQuery:sqlInsert];
    
    // Create Chat Msg Table
    NSString *strCreateSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS tblUChatMsg_%d ("
                                                        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                                        "version INTEGER, "
                                                        "fromId INTEGER, "
                                                        "time INTEGER, "
                                                        "message TEXT)", chatId];
    isSuccess = [self executeQuery:strCreateSQL];
    
    [self addUChatMemberWithChatId:chatId UserId:userId];
    [self addUChatSettingWithChatId:chatId];
    return isSuccess;
}

-(BOOL)deleteUChatWithChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"DELETE from tblUChatDetail WHERE chatId=%d;DROP table tblUChatMsg_%d;DELETE from tblUChatMember WHERE chatId=%d;DELETE from tblUChatSetting WHERE chatId=%d", chatId, chatId, chatId, chatId];
    isSuccess = [self executeQuery:strSQL];
    return isSuccess;
}

-(BOOL)addUChatMemberWithChatId:(int)chatId UserId:(int)userId {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblUChatMember (chatId, userId) VALUES (%d, %d)", chatId, userId];
    isSuccess = [self executeQuery:sqlInsert];
    
    [self setProfileAndPhotoVersion:userId ProfileVersion:0 PhotoVersion:0 DoUpdate:false];
    return isSuccess;
}

-(NSMutableArray *)getUChats {
    sqlite3 *dbConnection;
    NSMutableArray *aryChats = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        //NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM tblUChatDetail"];
        NSString *querySql = [NSString stringWithFormat:@"select cd.chatId, cd.type, cm.userId from tblUChatDetail cd, tblUChatMember cm where cd.chatId=cm.chatId"];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"chatId"] = @(sqlite3_column_int(compiledStatement, 0));
                dic[@"type"]   = @(sqlite3_column_int(compiledStatement, 1));
                dic[@"userProfile"] = [self getUserProfileForUserId:sqlite3_column_int(compiledStatement, 2)];
                /*if (objChat.type == 1) {
                    objChat.hangoutId = sqlite3_column_int(compiledStatement, 3);
                    objChat.title = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                }*/
                [aryChats addObject:dic];
            }
        } else {
            NSLog(@"getUChats - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryChats;
}

-(int) getUChatIdWithFriendId:(int)friendId {
    sqlite3 *dbConnection;
    int chatId = 0;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        //NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM tblUChatDetail"];
        NSString *querySql = [NSString stringWithFormat:@"select chatId from tblUChatMember where userId=%d", friendId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                chatId = sqlite3_column_int(compiledStatement, 0);
            }
        } else {
            NSLog(@"getUChatIdWithFriendId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return chatId;
}

-(NSMutableArray *)getUChatConversationForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableArray *aryChats = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT version, fromId, time, message FROM tblUChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 0));
                int fromId = sqlite3_column_int(compiledStatement, 1);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 2));
                dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
                [aryChats addObject:dic];
            }
        } else {
            NSLog(@"getUChatConversationForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryChats;
}

/*-(NSString *)getLastUChatMessageForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSString *lastMsg = @"";
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT message FROM tblUChatMsg_%d WHERE id = (SELECT MAX(id) FROM tblUChatMsg_%d)", chatId, chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                lastMsg = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 0)];
            }
        } else {
            NSLog(@"getUChatConversationForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return lastMsg;
}*/

-(NSMutableDictionary *)getLastUChatMsgForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT max(rowid), version, fromId, time, message FROM tblUChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 1));
                int fromId = sqlite3_column_int(compiledStatement, 2);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 3));
                if (sqlite3_column_text(compiledStatement, 4) != nil) {
                    dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                    /*NSData *data = [dic[@"message"] dataUsingEncoding:NSUTF8StringEncoding];
                    NSString *goodValue = [[NSString alloc] initWithData:data encoding:NSNonLossyASCIIStringEncoding];
                    dic[@"message"] = goodValue;*/
                }
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
            }
        } else {
            NSLog(@"getLastUChatMsgForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return dic;
}

-(BOOL)saveUChatMsgWithChatId:(int)chatId Version:(int)version FromId:(int)fromId Time:(int)time Message:(NSString *)message {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblUChatMsg_%d (version, fromId, time, message) VALUES (%d, %d, %d, \"%@\")", chatId, version, fromId, time, message];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)addUChatSettingWithChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblUChatSetting (chatId, noti) VALUES (%d, 1)", chatId];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)setUChatSettingWithChatId:(int)chatId EnableNotification:(int)enableNotification {
    BOOL isSuccess = false;
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE tblUChatSetting SET noti=%d WHERE chatId=%d", enableNotification, chatId];
    isSuccess = [self executeQuery:updateSQL];
    return isSuccess;
}

-(BOOL)getUChatNotificationSettingForChatId:(int)chatId {
    sqlite3 *dbConnection;
    BOOL notificationEnabled = false;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT noti FROM tblUChatSetting WHERE chatId=%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                notificationEnabled = sqlite3_column_int(statement, 0);
            }
        } else {
            NSLog(@"getUChatNotificationSettingForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return notificationEnabled;
}

-(int)getUnReadCountForUChatWithChatId:(int)chatId {
    int unreadCount = 0;
    sqlite3 *dbConnection;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT %@, %@ FROM %@ WHERE chatId=%d AND chatType=0", COL_MESSAGE_VERSION, COL_READ_MSG_VERSION, TBL_CHAT_VERSION, chatId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int messageVer = sqlite3_column_int(statement, 0);
                int readMsgVer = sqlite3_column_int(statement, 1);
                unreadCount = messageVer - readMsgVer;
            }
        } else {
            NSLog(@"getUnReadCountForUChatWithChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return  unreadCount;
}

#pragma mark
#pragma mark Multiple Chat

-(BOOL)syncMChatWithChatId:(int)chatId Type:(int)type Members:(NSArray *)members Title:(NSString *)title Noti:(int)noti Start:(int)start {
    BOOL isSuccess = false;
    int intCount = [self getRecordCountForTable:TBL_MCHAT_DETAIL Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d", chatId]];
    if (intCount > 0) {
        // update
        [self setMChatSettingWithChatId:chatId EnableNotification:noti];
        if ((members != nil) && (members.count > 0)) {
            // Delete all chat members
            NSString *strDelete = [NSString stringWithFormat:@"DELETE from %@ WHERE chatId=%d", TBL_MCHAT_MEMBER, chatId];
            [self executeQuery:strDelete];
            // Add chat members
            [self addMChatMemberWithChatId:chatId Members:members];
        }
        // Set chat notification
        [self setMChatSettingWithChatId:chatId EnableNotification:noti];
    } else {
        // Add
        isSuccess = [self createMChatWithChatId:chatId Type:type Members:members Title:title Start:start];
    }
    return isSuccess;
}

-(BOOL)createMChatWithChatId:(int)chatId Type:(int)type Members:(NSArray *)members Title:(NSString *)title Start:(int)start {
    BOOL isSuccess = false;
    NSString *sqlInsert;
    if (type == 0) {
        sqlInsert = [NSString stringWithFormat:@"insert into tblMChatDetail (chatId, type, start) VALUES (%d, %d, %d)", chatId, type, start];
    } else if (type == 1) {
        sqlInsert = [NSString stringWithFormat:@"insert into tblMChatDetail (chatId, type, title, start) VALUES (%d, %d, \"%@\", %d)", chatId, type, title, start];
    } else {
        //sqlInsert = [NSString stringWithFormat:@"insert into tblUChatDetail (chatId, type, hangoutid, title) VALUES (%d, %d, %d, %@)", chatId, type, hangoutId, title];
    }
    [self executeQuery:sqlInsert];
    
    // Create Chat Msg Table
    NSString *strCreateSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS tblMChatMsg_%d ("
                                  "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                  "version INTEGER, "
                                  "fromId INTEGER, "
                                  "time INTEGER, "
                                  "message TEXT)", chatId];
    [self executeQuery:strCreateSQL];
    if (members.count > 0) {
        [self addMChatMemberWithChatId:chatId Members:members];
    }
    [self addMChatSettingWithChatId:chatId];
    
    return isSuccess;
}

-(BOOL)deleteMChatWithChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"DELETE from tblMChatDetail WHERE chatId=%d;DROP table tblMChatMsg_%d;DELETE from tblMChatMember WHERE chatId=%d;DELETE from tblUChatSetting WHERE chatId=%d", chatId, chatId, chatId, chatId];
    isSuccess = [self executeQuery:strSQL];
    return isSuccess;
}

-(BOOL)addMChatMemberWithChatId:(int)chatId Members:(NSArray *)members {
    BOOL isSuccess = false;
    NSMutableString *sqlInsert;
    for (int i=0; i<members.count; i++) {
        int userId = [members[i] intValue];
        int intCount = [self getRecordCountForTable:@"tblMChatMember" Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d AND userId=%d", chatId, userId]];
        if (intCount <= 0) {
            sqlInsert = [NSMutableString stringWithFormat:@"insert into tblMChatMember (chatId, userId) VALUES (%d, %d)", chatId, userId];
            isSuccess = [self executeQuery:sqlInsert];
        }
        /*if (i==0) {
            sqlInsert = [NSMutableString stringWithFormat:@"insert into tblMChatMember (chatId, userId) VALUES (%d, %d)", chatId, userId];
        } else {
            [sqlInsert appendString: [NSString stringWithFormat:@"; insert into tblMChatMember (chatId, userId) VALUES (%d, %d)", chatId, userId]];
        }*/
    }
    return isSuccess;
}

-(NSMutableArray *)getMChats {
    sqlite3 *dbConnection;
    NSMutableArray *aryChats = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {        
        //NSString *querySql = [NSString stringWithFormat:@"select cd.chatId, cd.type, cd.title, cm.userId from tblMChatDetail cd, tblMChatMember cm where cd.chatId=cm.chatId"];
        NSString *querySql = [NSString stringWithFormat:@"select chatId, type, title, start from tblMChatDetail"];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"chatId"] = @(sqlite3_column_int(compiledStatement, 0));
                dic[@"type"]   = @(1); //sqlite3_column_int(compiledStatement, 1));
                dic[@"title"]  = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 2)];
                dic[@"start"]  = @(sqlite3_column_int(compiledStatement, 3));
                /*if (objChat.type == 1) {
                 objChat.hangoutId = sqlite3_column_int(compiledStatement, 3);
                 objChat.title = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                 }*/
                [aryChats addObject:dic];
            }
        } else {
            NSLog(@"getMChats - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
        
        for (int i=0; i<aryChats.count; i++) {
            NSMutableDictionary *dic = aryChats[i];
            dic[@"userProfile"] =  [self getMChatMembersForChatId:[dic[@"chatId"] intValue]];
            [aryChats replaceObjectAtIndex:i withObject:dic];
        }
    }
    return aryChats;
}

-(NSMutableArray *)getMChatConversationForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableArray *aryChats = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT version, fromId, time, message FROM tblMChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 0));
                int fromId = sqlite3_column_int(compiledStatement, 1);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 2));
                dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
                [aryChats addObject:dic];
            }
        } else {
            NSLog(@"getMChatConversationForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryChats;
}

-(NSMutableDictionary *)getLastMChatMsgForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT max(rowid), version, fromId, time, message FROM tblMChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 1));
                int fromId = sqlite3_column_int(compiledStatement, 2);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 3));
                if (sqlite3_column_text(compiledStatement, 4) != nil) {
                    dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                }
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
                
            }
        } else {
            NSLog(@"getLastMChatMsgForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return dic;
}

-(BOOL)saveMChatMsgWithChatId:(int)chatId Version:(int)version FromId:(int)fromId Time:(int)time Message:(NSString *)message {
    BOOL isSuccess = false;
    
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblMChatMsg_%d (version, fromId, time, message) VALUES (%d, %d, %d, \"%@\")", chatId, version, fromId, time, message];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)addMChatSettingWithChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblMChatSetting (chatId, noti) VALUES (%d, 1)", chatId];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)setMChatSettingWithChatId:(int)chatId EnableNotification:(int)enableNotification {
    BOOL isSuccess = false;
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE tblMChatSetting SET noti=%d WHERE chatId=%d", enableNotification, chatId];
    isSuccess = [self executeQuery:updateSQL];
    return isSuccess;
}

-(BOOL)getMChatNotificationSettingForChatId:(int)chatId {
    sqlite3 *dbConnection;
    BOOL notificationEnabled = false;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT noti FROM tblMChatSetting WHERE chatId=%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                notificationEnabled = sqlite3_column_int(statement, 0);
            }
        } else {
            NSLog(@"getMChatNotificationSettingForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return notificationEnabled;
}

-(BOOL)removeMChatMemberFromChatId:(int)chatId UserId:(int)userId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from tblMChatMember WHERE userId=%d AND chatId=%d", userId, chatId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}

-(BOOL)removeALLMChatMemberFromChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from tblMChatMember WHERE chatId=%d", chatId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}

-(NSMutableArray *)getMChatMembersForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    NSMutableArray *aryUserProfile = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        querySql = [NSString stringWithFormat:@"SELECT userId FROM tblMChatMember WHERE chatId=%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                [aryUserIds addObject:@(sqlite3_column_int(compiledStatement, 0))];
            }
        } else {
            NSLog(@"getMChatMembersForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
        
        // Get user profiles
        for (int i=0; i<aryUserIds.count; i++) {
            int uId = [aryUserIds[i] intValue];
            UserProfile *uProfile = [self getUserProfileForUserId:uId];
            if (uProfile != nil) {
                [aryUserProfile addObject: uProfile];
            }
        }
    }
    return aryUserProfile;
}

-(BOOL)setMChatTitleWithChatId:(int)chatId Title:(NSString *)title {
    BOOL isSuccess = false;
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE tblMChatDetail SET title=\"%@\" WHERE chatId=%d", title, chatId];
    isSuccess = [self executeQuery:updateSQL];
    return isSuccess;
}

-(NSString *)getMChatTitleForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSString *strTitle;
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        querySql = [NSString stringWithFormat:@"SELECT title FROM tblMChatDetail WHERE chatId=%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                strTitle = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 0)];
            }
        } else {
            NSLog(@"getMChatTitleForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return strTitle;
}

-(int)getUnReadCountForMChatWithChatId:(int)chatId {
    int unreadCount = 0;
    sqlite3 *dbConnection;
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT %@, %@ FROM %@ WHERE chatId=%d AND chatType=1", COL_MESSAGE_VERSION, COL_READ_MSG_VERSION, TBL_CHAT_VERSION, chatId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int messageVer = sqlite3_column_int(statement, 0);
                int readMsgVer = sqlite3_column_int(statement, 1);
                unreadCount = messageVer - readMsgVer;
            }
        } else {
            NSLog(@"getUnReadCountForMChatWithChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return  unreadCount;
}

#pragma mark
#pragma mark Hangout

-(BOOL)syncHangoutWithChatId:(int)chatId Noti:(int)noti Start:(int)start Hide:(int)hide Cancel:(int)cancel {
    BOOL isSuccess = false;
    int intCount = [self getRecordCountForTable:@"tblHangoutDetail" Column:@"hangoutId" WhereClause: [NSString stringWithFormat:@"hangoutId=%d", chatId]];
    if (intCount > 0) {
        // Update
        NSString *sqlUpdate = [NSString stringWithFormat:@"UPDATE %@ SET hide=%d, isNoti=%d, start=%d isCancelled=%d WHERE hangoutId=%d", TBL_HANGOUT_DETAIL, hide, noti, start, cancel, chatId];
        isSuccess = [self executeQuery:sqlUpdate];
    } else {
        // Add hangout
        NSString *sqlInsert = [NSString stringWithFormat:@"insert into %@ (hangoutId, hide, isNoti, start, remove, isCancelled) values (%d, %d, %d, %d, 0, %d)", TBL_HANGOUT_DETAIL, chatId, hide, noti, start, cancel];
        isSuccess = [self executeQuery:sqlInsert];
    }
    return isSuccess;
}

-(BOOL)setHangout:(Hangout *)hangout {
    int intCount = [self getRecordCountForTable:@"tblHangoutDetail" Column:@"hangoutId" WhereClause: [NSString stringWithFormat:@"hangoutId=%d", [hangout.hangoutId intValue]]];
    if (intCount <= 0) {
        // Add Hangout
        [self addHangout: hangout];
    } else {
        // Update Hangout
        [self updateHangout: hangout];
    }
    return true;
}

-(BOOL)addHangout:(Hangout *)hangout {
    BOOL isSuccess = false;
    if (hangout.detail == nil) { hangout.detail = @"";}
    NSString *sqlInsert;
    if ([hangout.scope intValue] == 5) {
        if (hangout.where == nil) {
            sqlInsert = [NSString stringWithFormat:@"insert into tblHangoutDetail (hangoutId, ownerId, category, activity, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope) VALUES (%d, %d, \"%@\", \"%@\", %d, %d, %f, %f, %d, %d, %d, %d, %d, %d, %d, \"%@\", %d)", [hangout.hangoutId intValue], [hangout.ownerId intValue], hangout.category, hangout.activity, hangout.hide, [hangout.time intValue], [hangout.latitude floatValue], [hangout.longitude floatValue], [hangout.multiChatId intValue], [hangout.goMember intValue], [hangout.ngMember intValue], hangout.isNoti, hangout.isCancelled, hangout.leave, hangout.remove, hangout.detail, [hangout.scope intValue]];
        } else {
            sqlInsert = [NSString stringWithFormat:@"insert into tblHangoutDetail (hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope) VALUES (%d, %d, \"%@\", \"%@\", \"%@\", %d, %d, %f, %f, %d, %d, %d, %d, %d, %d, %d, \"%@\", %d)", [hangout.hangoutId intValue], [hangout.ownerId intValue], hangout.category, hangout.activity, hangout.where, hangout.hide, [hangout.time intValue], [hangout.latitude floatValue], [hangout.longitude floatValue], [hangout.multiChatId intValue], [hangout.goMember intValue], [hangout.ngMember intValue], hangout.isNoti, hangout.isCancelled, hangout.leave, hangout.remove, hangout.detail, [hangout.scope intValue]];
        }
    } else {
        if (hangout.where == nil) {
            sqlInsert = [NSString stringWithFormat:@"insert into tblHangoutDetail (hangoutId, ownerId, category, activity, hide, hangoutTime, latitude, longitude, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope) VALUES (%d, %d, \"%@\", \"%@\", %d, %d, %f, %f,  %d, %d, %d, %d, %d, %d, \"%@\", %d)", [hangout.hangoutId intValue], [hangout.ownerId intValue], hangout.category, hangout.activity, hangout.hide, [hangout.time intValue], [hangout.latitude floatValue], [hangout.longitude floatValue], [hangout.goMember intValue], [hangout.ngMember intValue], hangout.isNoti, hangout.isCancelled, hangout.leave, hangout.remove, hangout.detail, [hangout.scope intValue]];
        } else {
            sqlInsert = [NSString stringWithFormat:@"insert into tblHangoutDetail (hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope) VALUES (%d, %d, \"%@\", \"%@\", \"%@\", %d, %d, %f, %f,  %d, %d, %d, %d, %d, %d, \"%@\", %d)", [hangout.hangoutId intValue], [hangout.ownerId intValue], hangout.category, hangout.activity, hangout.where, hangout.hide, [hangout.time intValue], [hangout.latitude floatValue], [hangout.longitude floatValue], [hangout.goMember intValue], [hangout.ngMember intValue], hangout.isNoti, hangout.isCancelled, hangout.leave, hangout.remove, hangout.detail, [hangout.scope intValue]];
        }
    }
    isSuccess = [self executeQuery:sqlInsert];
    
    [self addHangoutMemberWithHangoutId: [hangout.hangoutId intValue] UserId: [hangout.ownerId intValue] Going: 1 NotGoing: 0];
    [self createHChatWithChatId:[hangout.hangoutId intValue]];
    return isSuccess;
}

-(BOOL)updateHangout:(Hangout *)hangout {
    BOOL isSuccess = false;
    NSString *sqlUpdate = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET ownerId=%d, category=\"%@\", activity=\"%@\", hide=%d", [hangout.ownerId intValue], hangout.category, hangout.activity, hangout.hide];
    sqlUpdate = [NSString stringWithFormat:@"%@, hangoutTime=%d", sqlUpdate, [hangout.time intValue]];
    sqlUpdate = [NSString stringWithFormat:@"%@, latitude=%f, longitude=%f", sqlUpdate, [hangout.latitude floatValue], [hangout.longitude floatValue]];
    if ([hangout.scope intValue] == 5) {
        sqlUpdate = [NSString stringWithFormat:@"%@, multichatId=%d", sqlUpdate, [hangout.multiChatId intValue]];
    }
    sqlUpdate = [NSString stringWithFormat:@"%@, goMember=%d, ngMember=%d,isNoti=%d, leave=%d", sqlUpdate, [hangout.goMember intValue], [hangout.ngMember intValue], hangout.isNoti, hangout.leave];
    if (hangout.where != nil) {
        sqlUpdate = [NSString stringWithFormat:@"%@, place=\"%@\"", sqlUpdate, hangout.where];
    }
    if ((hangout.detail != nil) && (![hangout.detail isEqualToString:@""])) {
        sqlUpdate = [NSString stringWithFormat:@"%@, details=\"%@\"", sqlUpdate, hangout.detail];
    }
    sqlUpdate = [NSString stringWithFormat:@"%@, scope=%d WHERE hangoutId=%d", sqlUpdate, [hangout.scope intValue], [hangout.hangoutId intValue]];
    
    isSuccess = [self executeQuery:sqlUpdate];
    return isSuccess;
}

/*
 @"CREATE TABLE tblHangoutDetail ("
 "id INTEGER PRIMARY KEY AUTOINCREMENT, "
 "hangoutId INTEGER, "
 "ownerId INTEGER, "
 "category TEXT, "
 "activity TEXT, "
 "place TEXT, "
 "hide INTEGER, "
 "hangoutTime INTEGER, "
 "latitude FLOAT, "
 "longitude FLOAT, "
 "multichatId INTEGER, "
 "goMember INTEGER, "
 "ngMember INTEGER, "
 "isNoti INTEGER, "
 "isCancelled INTEGER, "
 "leave INTEGER, "
 "remove INTEGER, "
 "details TEXT, "
 "scope INTEGER)";
 */
-(NSMutableArray *)getAllHangout:(BOOL)isPublic {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (isPublic) {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE leave=0 AND remove=0"];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE scope<>3 AND leave=0 AND remove=0"];
        }
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
            //NSLog(@"%s", sqlite3_errmsg(dbConnection));
        } else {
            NSLog(@"getAllHangout - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    
    return aryUserIds;
}

-(Hangout *)getHangoutWithHangoutId:(int)hangoutId {
    sqlite3 *dbConnection;
    Hangout *hangout = [[Hangout alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE hangoutId=%d", hangoutId];
        // AND isCancelled=0 AND leave=0 AND remove=0
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
            }
        } else {
            NSLog(@"getHangoutsWithHangoutId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return hangout;
}

-(NSMutableArray *)getOwnHangoutsWithCategory:(NSString *)category IsPublic:(BOOL)isPublic {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (isPublic) {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE category=\"%@\" AND leave=0 AND remove=0", category];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE category=\"%@\" scope<>3 AND leave=0 AND remove=0", category];
        }
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
        } else {
            NSLog(@"getOwnHangoutsWithCategory - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    
    return aryUserIds;
}

-(NSMutableArray *)getOwnHangoutsWithOwnerId:(int)ownerId IsPublic:(BOOL)isPublic {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (isPublic) {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE ownerId=%d AND leave=0 AND remove=0", ownerId];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE ownerId=%d AND leave=0 AND remove=0 AND scope<>3", ownerId];
        }
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
        } else {
            NSLog(@"getOwnHangouts - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    
    return aryUserIds;
}

-(NSMutableArray *)getOtherHangouts:(int)going IsPublic:(BOOL)isPublic {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        int userId = [[AppUtils GetUserId] intValue];
        NSString *querySql;
        if (isPublic) {
            querySql= [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE leave=0 AND remove=0 AND ownerId<>%d AND hangoutId IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d AND going=%d)", userId, userId, going];
        } else {
            querySql= [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE leave=0 AND remove=0 AND ownerId<>%d AND scope<>3 AND hangoutId IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d AND going=%d)", userId, userId, going];
        }
        const char *sqlStatement = [querySql UTF8String];
        if (sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
        } else {
            NSLog(@"getOtherHangouts - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}

-(NSMutableArray *)getCancelledRestOfHangoutsIsPublic:(BOOL)isPublic; {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        int userId = [[AppUtils GetUserId] intValue];
        NSString *querySql;
        if (isPublic) {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE isCancelled=1 AND leave=0 AND remove=0 AND ownerId<>%d AND hangoutId NOT IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d)", userId, userId];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE isCancelled=1 AND leave=0 AND remove=0 AND ownerId<>%d AND scope<>3 AND hangoutId NOT IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d)", userId, userId];
        }
        const char *sqlStatement = [querySql UTF8String];
        if (sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
        } else {
            NSLog(@"getRestOfHangouts - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}

-(NSMutableArray *)getRestOfHangoutsIsPublic:(BOOL)isPublic {
    sqlite3 *dbConnection;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        int userId = [[AppUtils GetUserId] intValue];
        NSString *querySql;
        if (isPublic) {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE isCancelled=0 AND leave=0 AND remove=0 AND ownerId<>%d AND hangoutId NOT IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d)", userId, userId];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT hangoutId, ownerId, category, activity, place, hide, hangoutTime, latitude, longitude, multichatId, goMember, ngMember, isNoti, isCancelled, leave, remove, details, scope FROM tblHangoutDetail WHERE isCancelled=0 AND leave=0 AND remove=0 AND ownerId<>%d AND scope<>3 AND hangoutId NOT IN (SELECT hangoutId FROM tblHangoutMember WHERE userId=%d)", userId, userId];
        }
        const char *sqlStatement = [querySql UTF8String];
        if (sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                Hangout *hangout = [[Hangout alloc] init];
                hangout.hangoutId   = @(sqlite3_column_int(statement, 0));
                hangout.ownerId     = @(sqlite3_column_int(statement, 1));
                if (sqlite3_column_text(statement, 2) != nil) {
                    hangout.category = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
                }
                if (sqlite3_column_text(statement, 3) != nil) {
                    hangout.activity = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
                }
                if (sqlite3_column_text(statement, 4) != nil) {
                    hangout.where   = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 4)];
                } else {
                    hangout.where = @"";
                }
                hangout.hide        = sqlite3_column_int(statement, 5);
                hangout.time        = @(sqlite3_column_int(statement, 6));
                hangout.latitude    = @(sqlite3_column_double(statement, 7));
                hangout.longitude   = @(sqlite3_column_double(statement, 8));
                hangout.multiChatId = @(sqlite3_column_int(statement, 9));
                hangout.goMember    = @(sqlite3_column_int(statement, 10));
                hangout.ngMember    = @(sqlite3_column_int(statement, 11));
                hangout.isNoti      = sqlite3_column_int(statement, 12);
                hangout.isCancelled = sqlite3_column_int(statement, 13);
                hangout.leave       = sqlite3_column_int(statement, 14);
                hangout.remove      = sqlite3_column_int(statement, 15);
                if (sqlite3_column_text(statement, 16) != nil) {
                    hangout.detail  = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 16)];
                }
                hangout.scope       = @(sqlite3_column_int(statement, 17));
                
                [aryUserIds addObject:hangout];
            }
        } else {
            NSLog(@"getRestOfHangouts - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return aryUserIds;
}

-(BOOL)deleteHangoutWithHangoutId:(int)hangoutId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from tblHangoutDetail WHERE hangoutId=%d;DELETE from tblHangoutMember WHERE hangoutId=%d;DELETE from tblHChatDetail WHERE hangoutId=%d", hangoutId, hangoutId, hangoutId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}

-(BOOL)deleteExpiredHangout {
    BOOL isSuccess = false;
    /*sqlite3 *dbConnection;
    const char *dbpath = [databasePath UTF8String];
    if(sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *strToday = [AppUtils convertNSDateToString:[NSDate date] Format:@"yyyy-MM-dd"];
        NSString *deleteSQL = [NSString stringWithFormat:@"delete from tblHangoutDetail WHERE DATETIME(hangoutTime, 'unixepoch') < '%@'", strToday];
        const char *delete_stmt = [deleteSQL UTF8String];
        sqlite3_prepare_v2(dbConnection, delete_stmt, -1, &statement, NULL);
        
        if(sqlite3_step(statement)==SQLITE_DONE) {
            isSuccess = true;
        } else {
            NSLog(@"deleteExpiredHangout - error %s",sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }*/
    return isSuccess;
}


/*
 NSString *strCreateHangoutMemberTable = @"CREATE TABLE tblHangoutMember ("
 "id INTEGER PRIMARY KEY AUTOINCREMENT, "
 "hangoutId INTEGER, "
 "userId INTEGER, "
 "going INTEGER, "
 "notgoing INTEGER)";
 */
-(BOOL)addHangoutMemberWithHangoutId:(int)hangoutId UserId:(int)userId Going:(int)going NotGoing:(int)notGoing {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblHangoutMember (hangoutId, userId, going, notgoing) VALUES (%d, %d, %d, %d)", hangoutId, userId, going, notGoing];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)removeALLHangoutMemberWithHangoutId:(int)hangoutId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from tblHangoutMember WHERE hangoutId=%d", hangoutId];
    isSuccess = [self executeQuery:deleteSQL];
    return isSuccess;
}


-(BOOL)isGoingForHangoutId:(int)hangoutId UserId:(int)userId {
    BOOL result = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT going FROM tblHangoutMember WHERE hangoutId=%d AND userId=%d", hangoutId, userId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int isGoing = sqlite3_column_int(statement, 0);
                result = isGoing;
            }
        } else {
            NSLog(@"isGoingForHangoutId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return result;
}

-(BOOL)isNotGoingForHangoutId:(int)hangoutId UserId:(int)userId {
    BOOL result = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT notgoing FROM tblHangoutMember WHERE hangoutId=%d AND userId=%d", hangoutId, userId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int isGoing = sqlite3_column_int(statement, 0);
                result = isGoing;
            }
        } else {
            NSLog(@"isNotGoingForHangoutId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return result;
}

-(BOOL)isJoinedForHangoutId:(int)hangoutId UserId:(int)userId {
    BOOL result = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT going FROM tblHangoutMember WHERE hangoutId=%d AND userId=%d AND (going=1 OR notgoing=1)", hangoutId, userId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                int intCount = sqlite3_data_count(statement);
                result = (intCount > 0);
                /*while (sqlite3_step(statement) == SQLITE_ROW) {
                    int isGoing = sqlite3_column_int(statement, 0);
                    result = true;
                }*/
            } else {
                NSLog(@"isGoingForHangoutId - Step Error: %s", sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"isGoingForHangoutId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return result;
}

-(BOOL)createHChatWithChatId:(int)chatId {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblHChatDetail (chatId, type) VALUES (%d, 0)", chatId];
    isSuccess = [self executeQuery:sqlInsert];
        
    // Create Chat Msg Table
    NSString *strCreateSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS tblHChatMsg_%d ("
                              "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                              "version INTEGER, "
                              "fromId INTEGER, "
                              "time INTEGER, "
                              "message TEXT)", chatId];
    [self executeQuery:strCreateSQL];
    return isSuccess;
}

-(BOOL)saveHChatMsgWithChatId:(int)chatId Version:(int)version FromId:(int)fromId Time:(int)time Message:(NSString *)message {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into tblHChatMsg_%d (version, fromId, time, message) VALUES (%d, %d, %d, \"%@\")", chatId, version, fromId, time, message];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(NSMutableDictionary *)getLastHChatMsgForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT max(rowid), version, fromId, time, message FROM tblHChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 1));
                int fromId = sqlite3_column_int(compiledStatement, 2);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 3));
                if (sqlite3_column_text(compiledStatement, 4) != nil) {
                    dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 4)];
                }
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
                
            }
        } else {
            NSLog(@"getLastHChatMsgForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return dic;
}

-(NSMutableArray *)getHChatConversationForChatId:(int)chatId {
    sqlite3 *dbConnection;
    NSMutableArray *aryChats = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT version, fromId, time, message FROM tblHChatMsg_%d", chatId];
        const char *sqlStatement = [querySql UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            int userId = [[AppUtils GetUserId] intValue];
            while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"v"] = @(sqlite3_column_int(compiledStatement, 0));
                int fromId = sqlite3_column_int(compiledStatement, 1);
                dic[@"fromId"]  = @(fromId);
                dic[@"time"]    = @(sqlite3_column_int(compiledStatement, 2));
                dic[@"message"] = [NSString stringWithFormat:@"%s", sqlite3_column_text(compiledStatement, 3)];
                if (fromId == userId) {
                    dic[@"type"] = @"1";
                } else {
                    dic[@"type"] = @"2";
                }
                [aryChats addObject:dic];
            }
        } else {
            NSLog(@"getHChatConversationForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(compiledStatement);
        sqlite3_close(dbConnection);
    }
    return aryChats;
}

-(BOOL)isEntryFoundInHangoutWithUserId:(int)userId HangoutId:(int)hangoutId {
    BOOL isSuccess = false;
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT going FROM tblHangoutMember WHERE hangoutId=%d AND userId=%d", hangoutId, userId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                int intCount = sqlite3_data_count(statement);
                isSuccess = (intCount > 0);
            } else {
                NSLog(@"isEntryFoundInHangoutWithUserId - Step Error: %s", sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"isEntryFoundInHangoutWithUserId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return isSuccess;
}

-(BOOL)setJoinHangoutWithUserId:(int)userId ChatId:(int)chatId IsGoing:(BOOL)isGoing {
    BOOL isSuccess = false;
    BOOL isFound = [self isEntryFoundInHangoutWithUserId:userId HangoutId:chatId];
    NSString *sql;
    if (isFound) {
        sql = [NSString stringWithFormat:@"UPDATE tblHangoutMember SET going=%d, notgoing=%d WHERE hangoutId=%d AND userId=%d", isGoing, (!isGoing), chatId, userId];
    } else {
        sql = [NSString stringWithFormat:@"insert into tblHangoutMember (hangoutId, userId, going, notgoing) VALUES (%d, %d, %d, %d)", chatId, userId, isGoing, (!isGoing)];
    }
    isSuccess = [self executeQuery:sql];
    return isSuccess;
}

-(BOOL)setGoingNotGoingHangoutWithUserId:(int)userId ChatId:(int)chatId Going:(BOOL)isGoing NotGoing:(BOOL)notGoing {
    BOOL isSuccess = false;
    NSString *sql = [NSString stringWithFormat:@"UPDATE tblHangoutMember SET going=%d, notgoing=%d WHERE hangoutId=%d AND userId=%d", isGoing, notGoing, chatId, userId];
    isSuccess = [self executeQuery:sql];
    return isSuccess;
}

-(BOOL)setCancelHangoutWithHangoutId:(int)hangoutId IsCancel:(BOOL)isCancel {
    BOOL isSuccess = false;
    NSString *sqlUpdate = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET isCancelled=%d WHERE hangoutId=%d", isCancel, hangoutId];
    isSuccess = [self executeQuery:sqlUpdate];
    return isSuccess;
}

-(BOOL)setHideHangoutWithHangoutId:(int)hangoutId DoHide:(BOOL)doHide {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET hide=%d WHERE hangoutId=%d", doHide, hangoutId];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)setNotificationHangoutWithHangoutId:(int)hangoutId DoEnable:(BOOL)doEnable {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET isNoti=%d WHERE hangoutId=%d", doEnable, hangoutId];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)setLeaveHangoutWithHangoutId:(int)hangoutId DoLeave:(BOOL)doLeave {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET leave=%d WHERE hangoutId=%d", doLeave, hangoutId];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)setRemoveHangoutWithHangoutId:(int)hangoutId DoRemove:(BOOL)doRemove {
    BOOL isSuccess = false;
    NSString *sqlUpdate = [NSString stringWithFormat:@"UPDATE tblHangoutDetail SET remove=%d WHERE hangoutId=%d", doRemove, hangoutId];
    isSuccess = [self executeQuery:sqlUpdate];
    return isSuccess;
}

-(BOOL)isMuteHangoutForHangoutId:(int)hangoutId {
    BOOL isSuccess = false;
    NSString *querySql = [NSString stringWithFormat:@"SELECT isNoti FROM tblHangoutDetail WHERE hangoutId=%d", hangoutId];
    isSuccess = [self executeQuery:querySql];
    return isSuccess;
}

-(NSMutableArray *)getMembersForHangoutId:(int)hangoutId {
    NSMutableArray *aryResult = [[NSMutableArray alloc] init];
    sqlite3 *dbConnection;
    const char *dbpath=[databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM tblHangoutMember WHERE hangoutId=%d", hangoutId];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"userId"] = @(sqlite3_column_int(statement, 2));
                dic[@"going"]  = @(sqlite3_column_int(statement, 3));
                dic[@"notGoing"] = @(sqlite3_column_int(statement, 4));
                [aryResult addObject:dic];
            }
        } else {
            NSLog(@"getMembersForHangoutId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return aryResult;
}

#pragma mark
//MARK: Sync Version

-(BOOL)addSyncVersion {
    int userId = [[AppUtils GetUserId] intValue];
    NSDictionary *dicResult = [self isRecordSavedForUserId:userId Table:TBL_SYNC_VERSION Column:@"*"];
    int intCount = [dicResult[@"count"] intValue];
    //int intVer = [dicResult[@"version"] intValue];
    
    BOOL isSuccess = false;
    if (intCount <= 0) {
        // Add Sync Version details
        NSString *sqlInsert = [NSString stringWithFormat:@"insert into %@ (userId, confSvr, profileVersion, photoVersion, settingVersion, blockUserVersion, reqFriendVersion, friendVersion, onCampusVersion, ucListVersion, mcListVersion, hcListVersion) VALUES (%d, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)", TBL_SYNC_VERSION, [[AppUtils GetUserId] intValue]];
        isSuccess = [self executeQuery:sqlInsert];
    }
    return isSuccess;
}

-(BOOL)updateVersionOfColumn:(NSString *)column Version:(int)version {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE %@ SET %@=%d WHERE userId=%d", TBL_SYNC_VERSION, column, version, [[AppUtils GetUserId] intValue]];
    isSuccess = [self executeQuery:strSQL];
    return isSuccess;
}

-(BOOL)updateVersionOfColumn:(NSString *)column FloatVersion:(float)version {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE %@ SET %@=%f WHERE userId=%d", TBL_SYNC_VERSION, column, version, [[AppUtils GetUserId] intValue]];
    isSuccess =[self executeQuery:strSQL];
    return isSuccess;
}

-(Sync *)getSyncVersion {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    Sync *sync = [[Sync alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE userId=%d", TBL_SYNC_VERSION, [[AppUtils GetUserId] intValue]];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                sync.userId     = @(sqlite3_column_int(statement, 0));
                sync.confSvr    = @(sqlite3_column_int(statement, 1));
                sync.profile    = @(sqlite3_column_int(statement, 2));
                sync.photo      = @(sqlite3_column_int(statement, 3));
                sync.setting    = @(sqlite3_column_int(statement, 4));
                sync.blockUser  = @(sqlite3_column_double(statement, 5));
                sync.reqFriend  = @(sqlite3_column_double(statement, 6));
                sync.friendVer  = @(sqlite3_column_double(statement, 7));
                sync.onCampus   = @(sqlite3_column_double(statement, 8));
                sync.ucList     = @(sqlite3_column_double(statement, 9));
                sync.mcList     = @(sqlite3_column_double(statement, 10));
                sync.hcList     = @(sqlite3_column_double(statement, 11));
                isSuccess = true;
            } else {
                NSLog(@"getSyncVersion - Error: %s", sqlite3_errmsg(dbConnection));
            }
        } else {
            NSLog(@"getSyncVersion - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return sync;
    } else {
        return nil;
    }
}
/*
-(SyncMore *)getSyncMoreWithChatIds:(NSArray *)chatIds {
    SyncMore *syncMore = [[SyncMore alloc] init];
    syncMore.userId = @([[AppUtils GetUserId] intValue]);
    syncMore.aryUcs = [self getChatsVersionWithType:0 ChatIds:chatIds];
    syncMore.aryMcs = [self getChatsVersionWithType:1 ChatIds:chatIds];
    syncMore.aryHcs = [self getChatsVersionWithType:2 ChatIds:chatIds];
    return syncMore;
}*/

-(NSMutableArray *)getChatsVersionWithType:(int)type ChatIds:(NSArray *)chatIds { // Type 0-UChat, 1-MChat, 2-HChat
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    NSString *strChatIds = (NSString *)chatIds;
    for (int i=0; i<chatIds.count; i++) {
        if (i==0) {
            strChatIds = [NSString stringWithFormat:@"%d", [chatIds[i] intValue]];
        } else {
            strChatIds = [NSString stringWithFormat:@"%@, %d", strChatIds, [chatIds[i] intValue]];
        }
    }
    NSMutableArray *ary = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT chatId, detailVersion, memberVersion, hMemberVersion, messageVersion FROM %@ WHERE chatType=%d AND chatId IN (%@)", TBL_CHAT_VERSION, type, strChatIds];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int chatId = sqlite3_column_int(statement, 0);
                double detailVer = sqlite3_column_double(statement, 1);
                int memberVer = sqlite3_column_int(statement, 2);
                double hMemberVer = sqlite3_column_double(statement, 3);
                int msgBoxVer = sqlite3_column_int(statement, 4);
                NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
                dic[@"chatId"] = @(chatId);
                dic[@"vd"] = @(detailVer);
                if (type != 2) {
                    dic[@"vm"] = @(memberVer);
                } else {
                    dic[@"vh"] = @(hMemberVer); // if hangout
                }
                dic[@"vb"] = @(msgBoxVer);
                [ary addObject:dic];
                isSuccess = true;
            }
        } else {
            NSLog(@"getChatsVersionWithType - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return ary;
    } else {
        for (int i = 0; i<chatIds.count; i++) {
            if (type != 2) {
                [ary addObject:@{@"chatId":chatIds[i], @"vd":@(0.1), @"vm":@(1), @"vb":@(1)}];
            } else {
                [ary addObject:@{@"chatId":chatIds[i], @"vd":@(0.1), @"vh":@(0.1), @"vb":@(1)}];
            }
        }
        return ary;
    }
}

-(BOOL)setChatVersionForChatId:(int)chatId ChatType:(int)type Column:(NSString *)column Version:(double)version {
    BOOL isSuccess = false;
    NSString *querySql = [NSString stringWithFormat:@"UPDATE %@ SET %@=%f WHERE chatId=%d AND chatType=%d", TBL_CHAT_VERSION, column, version, chatId, type];
    isSuccess = [self executeQuery:querySql];
    return isSuccess;
}

-(id)getLastMessageVersionForChatId:(int)chatId ChatType:(int)type {
    sqlite3 *dbConnection;
    id msgVer = 0;
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql;
        if (type == 2) { // Hangout
            querySql = [NSString stringWithFormat:@"SELECT hMessageVersion FROM %@ WHERE chatId=%d AND chatType=%d", TBL_CHAT_VERSION, chatId, type];
        } else {
            querySql = [NSString stringWithFormat:@"SELECT messageVersion FROM %@ WHERE chatId=%d AND chatType=%d", TBL_CHAT_VERSION, chatId, type];
        }
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                if (type == 2) {
                    msgVer = @(sqlite3_column_double(statement, 0));
                } else {
                    msgVer = @(sqlite3_column_int(statement, 0));
                }
            }
        } else {
            NSLog(@"getLastMessageVersionForChatId - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    return msgVer;
}


-(BOOL)syncMChatsVersionWithType:(int)type ChatId:(int)chatId VD:(double)vd VM:(int)vm Title:(NSString *)title MemberIds:(NSArray *)memberIds { // Type 0-UChat, 1-MChat, 2-HChat
    BOOL isSuccess = false;
    
    // CHAT VERSION SYNC
    int intCount = [self getRecordCountForTable:TBL_CHAT_VERSION Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d AND chatType=%d", chatId, type]];
    if (intCount > 0) {
        // Update
        NSString *querySql = [NSString stringWithFormat:@"UPDATE %@ SET detailVersion=%f, memberVersion=%d WHERE chatId=%d AND chatType=%d", TBL_CHAT_VERSION, vd, vm, chatId, type];
        isSuccess = [self executeQuery:querySql];
    } else {
        // Add new
        NSString *querySql = [NSString stringWithFormat:@"insert into %@ (chatId, chatType, detailVersion, memberVersion) values (%d, %d, %f, %d)", TBL_CHAT_VERSION, chatId, type, vd, vm];
        isSuccess = [self executeQuery:querySql];
    }
    
    
    // M CHAT DETAIL SYNC
    // "chatId INTEGER, " "type INTEGER, " "start INTEGER, " "title TEXT)"
    intCount = [self getRecordCountForTable:TBL_MCHAT_DETAIL Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d", chatId]];
    if (intCount > 0) {
        // Update
        NSString *querySql = [NSString stringWithFormat:@"UPDATE %@ SET title=\"%@\" WHERE chatId=%d", TBL_MCHAT_DETAIL, title, chatId];
        isSuccess = [self executeQuery:querySql];
        isSuccess = [self removeALLMChatMemberFromChatId:chatId];
        isSuccess = [self addMChatMemberWithChatId:chatId Members:memberIds];
    } else {        
        // Add
        isSuccess = [self createMChatWithChatId:chatId Type:1 Members:memberIds Title:title Start:0];
        //NSString *querySql = [NSString stringWithFormat:@"insert into %@ (chatId, type, title) values (%d, 1, \"%@\")", TBL_MCHAT_DETAIL, chatId, title];
        //isSuccess = [self executeQuery:querySql];
    }
    return isSuccess;
}

-(BOOL)syncHChatsVersionWithType:(int)type Hangout:(Hangout *)hangout VD:(double)vd VH:(double)vh { // Type 0-UChat, 1-MChat, 2-HChat
    BOOL isSuccess = false;
    int hangoutId = [hangout.hangoutId intValue];
    
    // CHAT VERSION SYNC
    int intCount = [self getRecordCountForTable:TBL_CHAT_VERSION Column:@"chatId" WhereClause: [NSString stringWithFormat:@"chatId=%d AND chatType=%d", hangoutId, type]];
    if (intCount > 0) {
        // Update
        NSString *querySql = [NSString stringWithFormat:@"UPDATE %@ SET detailVersion=%f, hMemberVersion=%f WHERE chatId=%d AND chatType=%d", TBL_CHAT_VERSION, vd, vh, hangoutId, type];
        isSuccess = [self executeQuery:querySql];
    } else {
        // Add new
        NSString *querySql = [NSString stringWithFormat:@"insert into %@ (chatId, chatType, detailVersion, hMemberVersion) values (%d, %d, %f, %f)", TBL_CHAT_VERSION, hangoutId, type, vd, vh];
        isSuccess = [self executeQuery:querySql];
    }
    
    // HANGOUT DETAIL SYNC
    Hangout *temp = [[DBManager getSharedInstance] getHangoutWithHangoutId:hangoutId];
    hangout.hide = temp.hide;
    hangout.isNoti = temp.isNoti;
    hangout.isCancelled = temp.isCancelled;
    hangout.remove = temp.remove;
    hangout.leave = temp.leave;
    [[DBManager getSharedInstance] setHangout:hangout];
    
    // HANGOUT MEMBER
    [self removeALLHangoutMemberWithHangoutId:hangoutId];
    for (int i=0; i<hangout.aryGoMemberIds.count; i++) {
        [self addHangoutMemberWithHangoutId:hangoutId UserId:[hangout.aryGoMemberIds[i] intValue] Going:1 NotGoing:0];
    }
    for (int i=0; i<hangout.aryNgMemberIds.count; i++) {
        [self addHangoutMemberWithHangoutId:hangoutId UserId:[hangout.aryNgMemberIds[i] intValue] Going:0 NotGoing:1];
    }
    
    return isSuccess;
}

-(BOOL)syncUChatMsgWithChatId:(int)chatId Messages:(NSArray *)messages ReadMsgVer:(int)readMsgVer {
    BOOL isSuccess = false;
    
    // CHAT MSG SYNC
    // tblUChatMsg_%d (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "version INTEGER, " "fromId INTEGER, " "time INTEGER, " "message TEXT)
    
    for (int i=0; i<messages.count; i++) {
        NSDictionary *dic = messages[i];
        int ver = [dic[@"seq"] intValue];
        int from = [dic[@"from"] intValue];
        int when = [dic[@"when"]  intValue];
        NSString *msg = dic[@"text"];
        int lastMsgVer = [[[DBManager getSharedInstance] getLastMessageVersionForChatId:chatId ChatType:0] intValue];
        if (ver > lastMsgVer) {
            isSuccess = [self saveUChatMsgWithChatId:chatId Version:ver FromId:from Time:when Message:msg];
            [self setChatVersionForChatId:chatId ChatType:0 Column:COL_MESSAGE_VERSION Version:ver];
            [self setChatVersionForChatId:chatId ChatType:0 Column:COL_READ_MSG_VERSION Version:readMsgVer];
        }
    }
    return isSuccess;
}

-(BOOL)syncMChatMsgWithChatId:(int)chatId Messages:(NSArray *)messages {
    BOOL isSuccess = false;
    
    // CHAT MSG SYNC
    // tblMChatMsg_%d (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "version INTEGER, " "fromId INTEGER, " "time INTEGER, " "message TEXT)
    
    for (int i=0; i<messages.count; i++) {
        NSDictionary *dic = messages[i];
        int ver = [dic[@"seq"] intValue];
        int from = [dic[@"from"] intValue];
        int when = [dic[@"when"]  intValue];
        NSString *msg = dic[@"text"];
        int lastMsgVer = [[[DBManager getSharedInstance] getLastMessageVersionForChatId:chatId ChatType:1] intValue];
        if (ver > lastMsgVer) {
            isSuccess = [self saveMChatMsgWithChatId:chatId Version:ver FromId:from Time:when Message:msg];
        }
    }
    return isSuccess;
}

-(BOOL)syncHChatMsgWithChatId:(int)chatId Messages:(NSArray *)messages {
    BOOL isSuccess = false;
    
    // CHAT MSG SYNC
    // tblHChatMsg_%d (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "version INTEGER, " "fromId INTEGER, " "time INTEGER, " "message TEXT)
    
    for (int i=0; i<messages.count; i++) {
        NSDictionary *dic = messages[i];
        int ver = [dic[@"seq"] intValue];
        int from = [dic[@"from"] intValue];
        int when = [dic[@"when"]  intValue];
        NSString *msg = dic[@"text"];
        double lastMsgVer = [[[DBManager getSharedInstance] getLastMessageVersionForChatId:chatId ChatType:2] doubleValue];
        if (ver > lastMsgVer) {
            isSuccess = [self saveHChatMsgWithChatId:chatId Version:ver FromId:from Time:when Message:msg];
        }
    }
    return isSuccess;
}

-(BOOL)setProfileAndPhotoVersion:(int)userId ProfileVersion:(int)profileVersion PhotoVersion:(int)photoVersion DoUpdate:(BOOL)doUpdate {
    BOOL isSuccess = false;
    
    int intCount = [self getRecordCountForTable:TBL_PROFILE_PHOTO_VERSION Column:@"userId" WhereClause: [NSString stringWithFormat:@"userId=%d", userId]];
    NSString *sqlQuery = @"";
    if (intCount > 0) {
        if (doUpdate) {
            sqlQuery = [NSString stringWithFormat:@"UPDATE %@ SET photoVersion=%d, profileVersion=%d WHERE userId=%d", TBL_PROFILE_PHOTO_VERSION, photoVersion, profileVersion, userId];
        }
    } else {
        sqlQuery = [NSString stringWithFormat:@"insert into %@ (userId, photoVersion, profileVersion) VALUES (%d, %d, %d)", TBL_PROFILE_PHOTO_VERSION, userId, photoVersion, profileVersion];
    }
    if (![sqlQuery isEqualToString:@""]) {
        isSuccess = [self executeQuery:sqlQuery];
    }
    return isSuccess;
}

-(BOOL)setServerPhotoVersion:(int)userId ServerPhotoVersion:(int)serverPhotoVersion {
    BOOL isSuccess = false;
    
    int intCount = [self getRecordCountForTable:TBL_PROFILE_PHOTO_VERSION Column:@"userId" WhereClause: [NSString stringWithFormat:@"userId=%d", userId]];
    NSString *sqlQuery = @"";
    if (intCount > 0) {
        sqlQuery = [NSString stringWithFormat:@"UPDATE %@ SET serverPhotoVersion=%d WHERE userId=%d", TBL_PROFILE_PHOTO_VERSION, serverPhotoVersion, userId];
    } else {
        sqlQuery = [NSString stringWithFormat:@"insert into %@ (userId, serverPhotoVersion) VALUES (%d, %d)", TBL_PROFILE_PHOTO_VERSION, userId, serverPhotoVersion];
    }
    if (![sqlQuery isEqualToString:@""]) {
        isSuccess = [self executeQuery:sqlQuery];
    }
    return isSuccess;
}

-(NSMutableArray *)getSyncUsers {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    NSMutableArray *ary = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT userId, profileVersion, photoVersion FROM %@", TBL_PROFILE_PHOTO_VERSION];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int userId = sqlite3_column_int(statement, 0);
                int profile = sqlite3_column_int(statement, 1);
                int photo = sqlite3_column_int(statement, 2);
                NSDictionary *dic = @{@"userId":@(userId), @"profile":@(profile), @"photo":@(photo)};
                [ary addObject:dic];
                isSuccess = true;
            }
        } else {
            NSLog(@"getSyncUser - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return ary;
    } else {
        return nil;
    }
}

-(NSMutableArray *)getUserPhotoUpdating {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    NSMutableArray *ary = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT userId FROM %@ WHERE serverPhotoVersion>photoVersion", TBL_PROFILE_PHOTO_VERSION];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int userId = sqlite3_column_int(statement, 0);
                [ary addObject:@(userId)];
                isSuccess = true;
            }
        } else {
            NSLog(@"getUserPhotoUpdating - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return ary;
    } else {
        return nil;
    }
}


#pragma mark
//MARK: On Campus

-(BOOL)setOnCampusWithUserId:(int)userId When:(int)when IsOn:(int)isOn {
    BOOL isSuccess = false;
    NSDictionary *dicResult = [self isRecordSavedForUserId:userId Table:TBL_ON_CAMPUS Column:@"userId"];
    int intCount = [dicResult[@"count"] intValue];
    //int intVer = [dicResult[@"version"] intValue];
    
    if (intCount > 0) {
        // Update
        isSuccess = [self updateOnCampusWithUserId:userId When:when IsOn:isOn];
    } else {
        // Add
        isSuccess = [self addOnCampusWithUserId:userId When:when IsOn:isOn];
    }
    return isSuccess;
}

-(BOOL)addOnCampusWithUserId:(int)userId When:(int)when IsOn:(int)isOn {
    BOOL isSuccess = false;
    NSString *sqlInsert = [NSString stringWithFormat:@"insert into %@ (userId, time, isOn) VALUES (%d, %d, %d)", TBL_ON_CAMPUS, userId, when, isOn];
    isSuccess = [self executeQuery:sqlInsert];
    return isSuccess;
}

-(BOOL)updateOnCampusWithUserId:(int)userId When:(int)when IsOn:(int)isOn {
    BOOL isSuccess = false;
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE %@ SET time=%d, isOn=%d WHERE userId=%d", TBL_ON_CAMPUS, when, isOn, userId];
    isSuccess = [self executeQuery:strSQL];
    return isSuccess;
}

-(NSMutableArray *)getOnCampusFriends {
    sqlite3 *dbConnection;
    BOOL isSuccess = false;
    NSMutableArray *aryUserIds = [[NSMutableArray alloc] init];
    
    if (sqlite3_open([databasePath UTF8String], &dbConnection) == SQLITE_OK) {
        NSString *querySql = [NSString stringWithFormat:@"SELECT userId, time FROM %@ WHERE isOn=1", TBL_ON_CAMPUS];
        const char *sqlStatement = [querySql UTF8String];
        if(sqlite3_prepare_v2(dbConnection, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int userId = sqlite3_column_int(statement, 0);
                int time = sqlite3_column_int(statement, 1);
                [aryUserIds addObject:@{@"userId":@(userId), @"time":@(time)}];
                isSuccess = true;
            }
        } else {
            NSLog(@"getUserPhotoUpdating - Error: %s", sqlite3_errmsg(dbConnection));
        }
        sqlite3_finalize(statement);
        sqlite3_close(dbConnection);
    }
    if (isSuccess) {
        return aryUserIds;
    } else {
        return nil;
    }
}

-(BOOL)removeOnCampusFriendWithId:(int)userId {
    BOOL isSuccess = false;
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from %@ WHERE userId=%d", TBL_ON_CAMPUS, userId];
    isSuccess = [self executeQuery:deleteSQL];
    return true;
}

@end
