#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

@interface
@property (nonatomic, copy) NSString *dbPath;

@property (nonatomic, strong) FMDatabaseQueue *queue;


@implementation
@synthesize dbPath;

static id instance_;

+ (instancetype)sharedInstanceMethod {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance_ = [[self alloc] init];
    });
    
    return instance_;
}

+ (instancetype)allocWithZone:(struct _NSZone *)zone {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance_ = [super allocWithZone:zone];
    });
    
    return instance_;
}

+ (id)copyWithZone:(struct _NSZone *)zone {
    return instance_;
}

- (instancetype)init {
    self = [super init];
    if (self) {
        NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
        NSString *fileName = [doc stringByAppendingPathComponent:@"數(shù)據(jù)庫名.sqlite"];
        
        self.dbPath = fileName;
        self.queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
        [instance_ creatSqlite];
    }
    
    return self;
}



// 創(chuàng)建、打開數(shù)據(jù)庫
- (void)creatSqlite {
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql_allMessage = @"CREATE TABLE IF NOT EXISTS 表名1 (字段1 integer NOT NULL, 字段2 text NOT NULL, 字段3 text NOT NULL, 字段4 text NOT NULL, 字段5 text, 字段6 text, 字段7 integer NOT NULL, 字段8 text NOT NULL, 字段9 text NOT NULL, 字段10 text NOT NULL, 字段11 text NOT NULL);";
            
            NSString *sql_category = @"CREATE TABLE IF NOT EXISTS 表名2 (字段1 integer NOT NULL, 字段2 text NOT NULL, 字段3 text NOT NULL, 字段4 text NOT NULL, 字段5 text, 字段6 text, 字段7 integer NOT NULL, 字段8 text NOT NULL, 字段9 text NOT NULL, 字段10 integer NOT NULL);";
            
            NSString *sql_account = @"CREATE TABLE IF NOT EXISTS 表名3 (字段1 text NOT NULL);";
            
            BOOL res_allMessage = [db executeUpdate:sql_allMessage];
            BOOL res_category = [db executeUpdate:sql_category];
            BOOL res_account = [db executeUpdate:sql_account];
            if (!res_allMessage || !res_category || !res_account) {
                NSLog(@"創(chuàng)表失敗");
            } else {
                NSLog(@"創(chuàng)表成功");
            }
        } else {
            NSLog(@"打開數(shù)據(jù)庫失敗");
        }
    }];
}

// 向表格中增加數(shù)據(jù)
- (BOOL)addData {
    __block BOOL result = NO;
    
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql = @"INSERT OR REPLACE INTO 表名(字段1, 字段2, 字段3, 字段4, 字段5, 字段6, 字段7, 字段8, 字段9, 字段10, 字段11)""VALUES(?,?,?,?,?,?,?,?,?,?,?);";
            
            BOOL res = [db executeUpdate:sql,
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"", 
                        @"",
                        @""];
            
            if (!res) {
                NSLog(@"向表格中增加數(shù)據(jù)失敗");
                result = NO;
            } else {
                NSLog(@"向表格中增加數(shù)據(jù)成功");
                result = YES;
            }
        } else {
            NSLog(@"打開數(shù)據(jù)庫失敗");
            result = NO;
        }
    }];
    
    return result;
}

// 更新表格中的數(shù)據(jù)
- (BOOL)updateData {
    __block BOOL result = NO;
    
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql = @"UPDATE 表名 SET 字段1 = ?, 字段2 = ?, 字段3 = ?, 字段4 = ?, 字段5 = ?, 字段6 = ? WHERE 字段7 = ? AND 字段8 = ? AND 字段9 = ?";
            
            BOOL res = [db executeUpdate:sql,
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @""];
            
            if (!res) {
                NSLog(@"更新表格中的數(shù)據(jù)失敗");
                result = NO;
            } else {
                NSLog(@"更新表格中的數(shù)據(jù)成功");
                result = YES;
            }
        } else {
            NSLog(@"打開數(shù)據(jù)庫失敗");
            result = NO;
        }
    }];
    
    return result;
}




// 搜索(由于搜索內(nèi)容可能是需要在界面上展示彪笼，為保證效率，我這里一次只搜索20條，可配合下拉或上啦搜索加載更多悬蔽，只需要傳入開始搜索的位置接奈，例如剛開始查詢拂酣，startIndex傳入值為0椅棺，以此類推)
- (NSMutableArray *)searchDBWithKeyWord:(NSString *)keyWord andSatrtDate:(UInt64)startDate andEndDate:(UInt64)endDate withStartIndex:(UInt64)startIndex {
    __block NSMutableArray *timeRegionArray = [[NSMutableArray alloc] init];
    
    [self.queue inDatabase:^(FMDatabase *db) {
        NSString *keyWordSql = @"";
        NSString *startSql = @"";
        NSString *endSql = @"";
        
        if ([db open]) {
            if (![HLStringUtil isNullOrEmpty:keyWord]) {
                keyWordSql = [NSString stringWithFormat:@"%@%@%@", @" AND 字段1 LIKE '%", keyWord, @"%'"];
            }
            if (startDate) {
                startSql = [NSString stringWithFormat:@"%@%lld", @" AND 字段2 >= ",startDate];
            }
            if (endDate) {
                endSql = [NSString stringWithFormat:@"%@%lld", @" AND 字段3 <= ", endDate];
            }
            
            NSString *sql = [NSString stringWithFormat:@"%@%@%@%@%@%@%@%@%@%@%lld%@",
                             @"SELECT 字段4 FROM 表名 WHERE 字段5 ='",
                             @"",
                             @"' AND 字段6 ='",
                             @"",
                             @"' AND 字段7 = 'NO'",
                             keyWordSql,
                             startSql,
                             endSql,
                             @" ORDER BY id DESC",
                             @" LIMIT ",
                             startIndex,
                             @", 20"];
            
            FMResultSet *rs = [db executeQuery:sql];
            while ([rs next]) {
                NSString *jsonStr = [rs stringForColumn:@"字段4"];
                NSDictionary *jsDic = [HLDataTransformer jsonDataString2ArrayOrDictionary:jsonStr];
                Message *message = [[Message alloc] init];
                [message dicTranslatetoMessage:jsDic];
                
                [timeRegionArray addObject:message];
            }
        }
    }];
    
    return timeRegionArray;
}