IM-ios/AIIM/Common/Network/GDBManager.m

//
//  GDBManager.m
//  TUIContact
//
//  Created by gan on 2025/3/24.
//

#import <Foundation/Foundation.h>
#import "GDBManager.h"
#import "UDManager.h"
#import "FMDB.h"
#import "config.h"

@interface GDBManager ()

@property (nonatomic, strong) FMDatabase *db;
@property (nonatomic) BOOL isOpen;

@end

@implementation GDBManager

+ (GDBManager *_Nonnull)shareInstance{
    static id gShareInstance = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
      gShareInstance = [[self alloc] init];
    });
    return gShareInstance;
}

- (instancetype)init {
    if (self = [super init]) {
        self.isOpen = false;
    }
    return self;
}


#pragma mark - Public -

-(void)createDatabase:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 数据库文件路径，这里使用文档目录下的数据库文件，确保数据持久化
    NSString *docsDir;
    NSArray *dirPaths;
    // 获取文档目录路径
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    NSString *dbPath = [[docsDir stringByAppendingPathComponent:locateDB] stringByStandardizingPath];
    
    // 创建数据库实例，如果数据库文件不存在，则会自动创建该文件
    self.db = [FMDatabase databaseWithPath:dbPath];
    if (![self.db open]) {
        NSLog(@"数据库打开失败");
        if(fail){
            fail([NSString stringWithFormat:@"数据库打开失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        NSLog(@"数据库打开成功");
        if(succ){
            succ(nil);
        }
        self.isOpen = true;
    }
}


-(void)closeDB{
    [self.db close];
    self.isOpen = false;
}

-(void)deleteDB{
    // 数据库文件路径，这里使用文档目录下的数据库文件，确保数据持久化
    [self closeDB];
    NSString *docsDir;
    NSArray *dirPaths;
    // 获取文档目录路径
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    NSString *dbPath = [[docsDir stringByAppendingPathComponent:locateDB] stringByStandardizingPath];
    NSError *error;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL success = [fileManager removeItemAtPath:dbPath error:&error];
    if (success) {
        NSLog(@"文件删除成功");
        self.isOpen = false;
    } else {
        NSLog(@"文件删除失败: %@", error.localizedDescription);
    }
}


-(void)replayAllData{
    int lowerBound = 10;
    int upperBound = 30;
    int randomInt = lowerBound + arc4random() % (upperBound - lowerBound + 1);
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    
    for (NSInteger i = 0; i<randomInt; i++) {
        [self delLocalmsglistTable:userid];
        [self createLocalMSGtable:nil fail:nil];
        NSUUID *uid = [NSUUID UUID];
        NSDate *now = [NSDate date];
        NSTimeInterval trt = [now timeIntervalSince1970];
        NSInteger time = trt*1000;
        NSString *strtime = [NSString stringWithFormat:@"%ld",(long)time];
        
        NSDictionary *dic =@{
            @"id":strtime,
            @"messageType":MessageType_image,
            @"chatId":[NSString stringWithFormat:@"%d",randomInt],
            @"fromId":userid,
            @"fromName":userid,
            @"fromAvatar":@"Avatar",
            @"reSend":[NSNumber numberWithBool:false],
            @"content":uid.UUIDString,
            @"type":@"0",
            @"localtime":strtime,
            @"timestamp":strtime,
            @"mine":[NSNumber numberWithBool:YES],
            @"extend":@{
                @"url":uid.UUIDString,
                @"time":strtime,
                @"localurl":uid.UUIDString,
            }
        };
        [self insertLocalmsg:dic succ:nil fail:nil];
        NSLog(@"delLocalmsglistTable:%ld",(long)i);
    }
    
    randomInt = lowerBound + arc4random() % (upperBound - lowerBound + 1);
    for (NSInteger i = 0; i<randomInt; i++) {
        [self delchatlistTable:nil fail:nil];
        [self createChattable:nil fail:nil];
        
        NSUUID *uid = [NSUUID UUID];
        NSDate *now = [NSDate date];
        NSTimeInterval trt = [now timeIntervalSince1970];
        NSInteger time = trt*1000;
        NSString *strtime = [NSString stringWithFormat:@"%ld",(long)time];
        
        NSDictionary *chatD=@{
            @"id":strtime,
            @"name":userid,
            @"avatar":@"avatar",
            @"type":@"0",
            @"lastMessage":uid.UUIDString,
            @"lastTime":strtime,
            @"unreadCount":[NSString stringWithFormat:@"%ld",(long)i],
            @"loaded":@"",
            @"loading":@"",
            @"top":@"true",
        };
        
        [self insertLocalchat:chatD succ:nil fail:nil];
        NSLog(@"delchatlistTable:%ld",(long)i);
    }

    randomInt = lowerBound + arc4random() % (upperBound - lowerBound + 1);
    for (NSInteger i = 0; i<randomInt; i++) {
        [self createLastreadtimetable:nil fail:nil];
        [self deleatLastreadtimetable];
        NSLog(@"deleatLastreadtimetable:%ld",(long)i);
    }
    
    randomInt = lowerBound + arc4random() % (upperBound - lowerBound + 1);
    for (NSInteger i = 0; i<randomInt; i++) {
        [self deleteDB];
        [self createDatabase:nil fail:nil];
        NSLog(@"deleteDB:%ld",(long)i);
    }
    
    
}

//创建消息表
-(void)createLocalMSGtable:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail;{
   
    // 创建表语句
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *sql1=@"create table if not exists lcmsglist_";
    NSString *tablejg = @"(id CHAR PRIMARY KEY,chatId CHAR,type CHAR,messageType CHAR,timestamp BIGINT,localtime BIGINT,Message TEXT)";
    NSString *sql = [NSString stringWithFormat:@"%@%@ %@",sql1,userid,tablejg];
    //NSLog(@"sql:%@",sql);
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"创建消息表失败: %@", [self.db lastErrorMessage]);
        if(fail){
            fail([NSString stringWithFormat:@"创建消息表失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        NSLog(@"创建消息表成功");
        if (succ) {
            succ(nil);
        }
    }
}

-(void)delLocalmsglistTable:(NSString *)userid{//删除消息表
    NSString *sql1=@"DROP TABLE lcmsglist_";
    NSString *sql = [NSString stringWithFormat:@"%@%@",sql1,userid];
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"删除消息表失败: %@", [self.db lastErrorMessage]);
    } else {
        NSLog(@"删除消息表成功");
    }
}
-(void)insertLocalmsg:(NSDictionary *_Nonnull)msg succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//插入消息
    NSString *vstr = @"insert into lcmsglist_";
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSDictionary * safeDict = [self replaceNullsWithEmptyStringInDictionary:msg];
    
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" ('id','chatId','type','messageType','timestamp','localtime','Message') values ("];
    
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"id"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"chatId"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"type"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"messageType"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"timestamp"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"localtime"]];
    
    NSData *jsonData = [NSJSONSerialization dataWithJSONObject:msg options:0 error:nil];
    NSString *jsonString = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
    
    vstr=[NSString stringWithFormat:@"%@'%@'%@",vstr,jsonString,@")"];
    
//    NSLog(@"vstr:%@",vstr);
    
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        if(fail){
            fail([NSString stringWithFormat:@"插入消息表失败: %@", [self.db lastErrorMessage]]);
        }
        NSLog(@"插入消息表失败: %@", [self.db lastErrorMessage]);
    } else {
        NSLog(@"插入消息表成功");
        if (succ) {
            succ(nil);
        }
    }
}


-(void)insertRplaceLocalmsg:(NSDictionary *_Nonnull)msg succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//插入消息
    
    if (![self.db isOpen]) {
        return;
    }
    
    // 1. 参数校验
    if (!msg || msg.count == 0) {
        if (fail) fail(@"消息内容不能为空");
        return;
    }
    
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    if (userid.length == 0) {
        if (fail) fail(@"用户ID获取失败");
        return;
    }
    
    NSString *tableName = [NSString stringWithFormat:@"lcmsglist_%@", userid];
    NSDictionary *safeDict = [self replaceNullsWithEmptyStringInDictionary:msg];
    
    // 2. 处理localtime
    NSString *localtime = [NSString stringWithFormat:@"%@", safeDict[@"localtime"]];
//    if (localtime.length == 0) {
//        localtime = safeDict[@"timestamp"];
//    }
    // 2. 处理图片文件视频消息可能存在乱序问题
    NSString *msgtype = safeDict[@"messageType"];
    BOOL urlMark = true;
    if([msgtype isEqualToString:MessageType_image]||[msgtype isEqualToString:MessageType_file]||[msgtype isEqualToString:MessageType_video]){
        // 文件消息
        NSDictionary * extend = [safeDict[@"extend"] isKindOfClass:NSDictionary.class]?safeDict[@"extend"]:@{};
        NSString *url=extend[@"url"] ?: @"";
        if(url.length==0){
            urlMark=false;//不是正式真实消息尝试插入,不能删除已有的信息
        }
        NSString *fileEER = extend[@"fileError"] ?: @"";
        if(fileEER.intValue==1){
            urlMark=true;//不是正式真实消息尝试插入,不能删除已有的信息
        }
    }
    
    // 3. 开启事务
    [self.db beginTransaction];
    BOOL shouldRollback = NO;
    @try {
//        NSLog(@"删除旧数据(localtime=%@)", localtime);
        
        NSString *selctSQL = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE localtime = %@ AND (messageType='11' OR messageType='12')",tableName,localtime];
        FMResultSet *rs = [self.db executeQuery:selctSQL];
        BOOL markesel=NO;
        while ([rs next]) {
            //NSLog(@"rs：%@",rs);
            NSString *msg =[rs stringForColumn:@"Message"];
            NSLog(@"--------rsmsg：%@",msg);
            markesel=YES;
            break;
        }
        [rs close]; // 关闭结果集
        if(markesel){
            [self.db commit];
            return;
        }
        
        if(urlMark){//删除旧数据，以便更新信息
            NSLog(@"删除旧数据(2localtime=%@)", localtime);
            // 4. 删除相同localtime的旧数据
            NSString *deleteSQL = [NSString stringWithFormat:
                                   @"DELETE FROM %@ WHERE localtime = ?",
                                   tableName];
            
            if (![self.db executeUpdate:deleteSQL, localtime]) {
                NSLog(@"删除旧数据失败(localtime=%@): %@", localtime, [self.db lastErrorMessage]);
                // 不终止，继续尝试插入
            }
        }
        // 5. 准备插入数据
        NSData *jsonData = [NSJSONSerialization dataWithJSONObject:safeDict options:0 error:nil];
        NSString *jsonString = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
        
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO %@ (id,chatId,type,messageType,timestamp,localtime,Message) VALUES (?,?,?,?,?,?,?)",
                               tableName];
        
        // 6. 执行插入
        BOOL success = [self.db executeUpdate:insertSQL,
                        safeDict[@"id"],
                        safeDict[@"chatId"],
                        safeDict[@"type"],
                        safeDict[@"messageType"],
                        safeDict[@"timestamp"],
                        localtime,
                        jsonString];
        
        if (!success) {
            shouldRollback = YES;
            NSString *error = [NSString stringWithFormat:@"插入消息失败: %@", [self.db lastErrorMessage]];
            if (fail) fail(error);
            
        } else {
            [self.db commit];
            if (succ) succ(nil);
//            NSLog(@"消息插入成功(ID=%@)", safeDict);
        }
    }
    @catch (NSException *exception) {
        shouldRollback = YES;
        NSString *error = [NSString stringWithFormat:@"操作异常: %@", exception.reason];
        if (fail) fail(error);
        
    }
    @finally {
        if (shouldRollback) {
            [self.db rollback];
        }
    }
}

- (void)batchInsertLocalMessages:(NSArray<NSDictionary *> *)messages
                         success:(DBSucc _Nullable)succ
                         failure:(DBFail _Nullable)fail {
    
    if (![self.db isOpen]) {
        return;
    }
    
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *tableName = [NSString stringWithFormat:@"lcmsglist_%@", userid];
    
    // 开启事务
    [self.db beginTransaction];
    NSMutableArray *failedMessages = [NSMutableArray array];
    BOOL hasErrors = NO;
    
    @try {
        // 准备SQL语句（使用参数化查询，避免SQL注入）
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO %@ ('id','chatId','type','messageType','timestamp','localtime','Message') VALUES (?,?,?,?,?,?,?)",
                               tableName];
        
        for (NSDictionary *msg in messages) {
            NSDictionary *safeDict = [self replaceNullsWithEmptyStringInDictionary:msg];
            NSString *localtime = [NSString stringWithFormat:@"%@", safeDict[@"localtime"]];

            
            NSString *selctSQL = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE localtime = %@ AND (messageType='11' OR messageType='12')",tableName,localtime];
            FMResultSet *rs = [self.db executeQuery:selctSQL];
            BOOL markesel=NO;
            while ([rs next]) {
                //NSLog(@"rs：%@",rs);
                NSString *msg =[rs stringForColumn:@"Message"];
//                NSLog(@"--------rsmsg：%@",msg);
                markesel=YES;
                break;
            }
            [rs close]; // 关闭结果集
            
            if(markesel){
                continue;
            }
            
            NSString *deleteSQL = [NSString stringWithFormat:
                                   @"DELETE FROM %@ WHERE localtime = ?",
                                   tableName];
            
            if (![self.db executeUpdate:deleteSQL, localtime]) {
//                NSLog(@"batchInsertLocalMessages 删除旧数据失败(localtime=%@): %@", localtime, [self.db lastErrorMessage]);
                // 不终止，继续尝试插入
            }
            else{
//                NSLog(@"batchInsertLocalMessages 删除旧数据(localtime=%@): %@", localtime, [self.db lastErrorMessage]);
            }
            
            
            // 准备Message字段的JSON字符串
            NSData *jsonData = [NSJSONSerialization dataWithJSONObject:safeDict options:0 error:nil];
            NSString *jsonString = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
            
            // 执行插入（使用参数化查询）
            BOOL success = [self.db executeUpdate:insertSQL,
                            safeDict[@"id"],
                            safeDict[@"chatId"],
                            safeDict[@"type"],
                            safeDict[@"messageType"],
                            safeDict[@"timestamp"],
                            safeDict[@"localtime"],
                            jsonString];
            
            if (!success) {
                hasErrors = YES;
                // 记录失败的消息
                
                // 继续下一条
                continue;
            }
        }
        
        // 提交事务
        [self.db commit];
        
        NSLog(@"批量插入消息完成，成功 %lu 条，失败 %lu 条",
              (unsigned long)(messages.count - failedMessages.count),
              (unsigned long)failedMessages.count);
        
        if (succ) {
            // 如果有失败记录，返回失败信息
            if (failedMessages.count > 0) {
                succ(@[@{
                    @"successCount": @(messages.count - failedMessages.count),
                    @"failedCount": @(failedMessages.count),
                    @"failedMessages": failedMessages
                }]);
            } else {
                succ(nil);
            }
        }
    }
    @catch (NSException *exception) {
        [self.db rollback];
        NSString *error = [NSString stringWithFormat:@"批量插入本地异常: %@", exception.reason];
        if (fail) {
            fail(error);
        }
        NSLog(@"%@", error);
    }
}

-(void)selectLocalmsg:(NSString *_Nonnull)chatId page:(NSInteger)page succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//查询消息
    
    // 查询数据
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"select * from lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" where chatId = "];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,chatId];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"ORDER BY timestamp DESC LIMIT 16;"];
    
//    NSLog(@"vstr:%@",vstr);
    
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        //NSLog(@"rs：%@",rs);
        NSString *msg =[rs stringForColumn:@"Message"];
        //NSLog(@"msg：%@",msg);
        NSData *data = [msg dataUsingEncoding:NSUTF8StringEncoding];
        NSDictionary *jsonDict = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
//        NSLog(@"%@",jsonDict);
//        Message *msgobj = [[Message alloc] init];
//        [msgobj setValuesWithDictionary:jsonDict];
        [array addObject:jsonDict];
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}

-(void)selectnextmsg:(NSString *_Nonnull)chatId timestp:(NSInteger)timestp succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//查询消息
    // 查询数据
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"select * from lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" where chatId = "];
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,chatId,@" AND timestamp < "];
    vstr=[NSString stringWithFormat:@"%@%ld",vstr,(long)timestp];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"ORDER BY timestamp DESC LIMIT 16;"];
    
//    NSLog(@"vstr:%@",vstr);
    
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        //NSLog(@"rs：%@",rs);
        NSString *msg =[rs stringForColumn:@"Message"];
        //NSLog(@"msg：%@",msg);
        NSData *data = [msg dataUsingEncoding:NSUTF8StringEncoding];
        NSDictionary *jsonDict = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
//        NSLog(@"%@",jsonDict);
//        Message *msgobj = [[Message alloc] init];
//        [msgobj setValuesWithDictionary:jsonDict];
        [array addObject:jsonDict];
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}

-(void)deleteMyLocalmsg:(NSDictionary *)msg{//删除单个消息 DELETE FROM users WHERE id IN (100, 101);
    
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"DELETE FROM lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" WHERE id=="];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,msg[@"localtime"]];
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        NSLog(@"删除消息表失败: %@", [self.db lastErrorMessage]);
    } else {
        NSLog(@"删除消息表成功");
    }
}

-(void)deleteLocalmsg:(NSDictionary *_Nullable)msg{
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"DELETE FROM lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" WHERE id=="];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,msg[@"id"]];
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        NSLog(@"删除指定消息: %@", [self.db lastErrorMessage]);
    } else {
        NSLog(@"删除指定消息成功");
    }
}

//删除数据指定聊天窗消息
-(void)deletechatLocalmsg:(NSString *_Nullable)chatId{
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    // 开启事务
    [self.db beginTransaction];
    @try {
        NSString *updataSQL =[NSString stringWithFormat:@"UPDATE lcmsglist_%@ SET Message=? WHERE chatId = ?",userid];
        // 执行插入（使用参数化查询）
        NSUUID *uid = [NSUUID UUID];
        BOOL success = [self.db executeUpdate:updataSQL,
                        uid.UUIDString,
                        chatId];
        // 提交事务
        [self.db commit];
        if (!success) {
            NSLog(@"更新数据失败: %@", [self.db lastErrorMessage]);
        } else {
            NSLog(@"更新数据成功");
        }
        NSString *vstr = [NSString stringWithFormat:@"DELETE FROM lcmsglist_%@ WHERE chatId=%@",userid,chatId];
        success = [self.db executeUpdate:vstr];
        if (!success) {
            NSLog(@"删除指定消息: %@", [self.db lastErrorMessage]);
        } else {
            NSLog(@"删除指定消息成功");
        }
    }
    @catch (NSException *exception) {
        [self.db rollback];
        NSString *error = [NSString stringWithFormat:@"lcUserServer插入本地异常: %@", exception.reason];
        NSLog(@"%@", error);
    }
}


-(void)selectLocalmsgType:(NSString *)chatId messageType:(NSString *)messageType succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//按分类查询消息
    
    // 查询数据
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"select * from lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" where chatId = "];
    vstr=[NSString stringWithFormat:@"%@%@ AND messageType=",vstr,chatId];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,messageType];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"ORDER BY timestamp DESC LIMIT 16;"];
    
    //NSLog(@"vstr:%@",vstr);
    
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        //NSLog(@"rs：%@",rs);
        NSString *msg =[rs stringForColumn:@"Message"];
        //NSLog(@"msg：%@",msg);
        NSData *data = [msg dataUsingEncoding:NSUTF8StringEncoding];
        if (data) {
            NSDictionary *jsonDict = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
            [array addObject:jsonDict];
        }
        
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}

-(void)selectNextmsgType:(NSString *_Nullable)chatId messageType:(NSString *_Nullable)messageType timestp:(NSInteger)timestp succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 查询数据
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"select * from lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" where chatId = "];
    vstr=[NSString stringWithFormat:@"%@%@ AND messageType=",vstr,chatId];
    vstr=[NSString stringWithFormat:@"%@%@  AND timestamp < ",vstr,messageType];
    vstr=[NSString stringWithFormat:@"%@%ld",vstr,(long)timestp];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"ORDER BY timestamp DESC LIMIT 16;"];
    
    NSLog(@"vstr:%@",vstr);
    
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        //NSLog(@"rs：%@",rs);
        NSString *msg =[rs stringForColumn:@"Message"];
        //NSLog(@"msg：%@",msg);
        NSData *data = [msg dataUsingEncoding:NSUTF8StringEncoding];
        if (data) {
            NSDictionary *jsonDict = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
            [array addObject:jsonDict];
        }
        
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}

//根据本地时间查询本地消息
-(void)selectLocalmsgWithLocaltime:(NSString *_Nullable)localtime succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 查询数据
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"select * from lcmsglist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" where localtime = "];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,localtime];;
    
    NSLog(@"vstr:%@",vstr);
    
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        //NSLog(@"rs：%@",rs);
        NSString *msg =[rs stringForColumn:@"Message"];
        //NSLog(@"msg：%@",msg);
        NSData *data = [msg dataUsingEncoding:NSUTF8StringEncoding];
        if (data) {
            NSDictionary *jsonDict = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
            [array addObject:jsonDict];
        }
        
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}


//聊天窗表lcChatlist_
-(void)createChattable:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 创建表语句
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *sql1=@"create table if not exists lcChatlist_";
    NSString *tablejg = @"(id CHAR PRIMARY KEY,name CHAR,avatar CHAR,type CHAR,lastMessage TEXT,lastTime BIGINT,unreadCount int,loaded int,loading int,top int)";
    NSString *sql = [NSString stringWithFormat:@"%@%@ %@",sql1,userid,tablejg];
    //NSLog(@"sql:%@",sql);
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"创建lcChatlist_表失败: %@", [self.db lastErrorMessage]);
        if(fail){
            fail([NSString stringWithFormat:@"创建消息表失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        NSLog(@"创建lcChatlist_表成功");
        if (succ) {
            succ(nil);
        }
    }
}

-(void)delchatlistTable:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{//删除聊天窗表lcChatlist_
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *sql1=@"DROP TABLE lcChatlist_";
    NSString *sql = [NSString stringWithFormat:@"%@%@",sql1,userid];
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"删除lcChatlist_表失败: %@", [self.db lastErrorMessage]);
        if(fail){
            fail([NSString stringWithFormat:@"创建消息表失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        NSLog(@"删除lcChatlist_表成功");
        if (succ) {
            succ(nil);
        }
    }
}

-(void)insertLocalchat:(NSDictionary *_Nonnull)msg succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    
    
    NSString *vstr = @"INSERT OR REPLACE INTO lcChatlist_";
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" ('id','name','avatar','type','lastMessage','lastTime','unreadCount','loaded','loading','top') values ("];
    NSDictionary * safeDict = [self replaceNullsWithEmptyStringInDictionary:msg];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"id"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"name"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"avatar"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"type"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"lastMessage"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"lastTime"]];
    vstr=[NSString stringWithFormat:@"%@%@,",vstr,safeDict[@"unreadCount"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"loaded"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,safeDict[@"loading"]];
    vstr=[NSString stringWithFormat:@"%@'%@')",vstr,safeDict[@"top"]];
    
//    NSLog(@"vstr:%@",vstr);
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        if(fail){
            fail([NSString stringWithFormat:@"插入消息表失败: %@", [self.db lastErrorMessage]]);
        }
        //NSLog(@"插入消息表失败: %@", [self.db lastErrorMessage]);
    } else {
//        NSLog(@"插入消息表成功 insertLocalchat");
        if (succ) {
            succ(nil);
        }
    }
}


-(void)selectLocalchats:(NSString *)top succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 查询数据
    NSString *vstr = @"select * from lcChatlist_";
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,userid];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"where top="];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,top];
    vstr=[NSString stringWithFormat:@"%@ %@",vstr,@"ORDER BY 'lastTime' ASC"];
    
    NSLog(@"vstr:%@",vstr);
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        NSDictionary *chatD=@{
            @"id":[rs stringForColumn:@"id"],
            @"name":[rs stringForColumn:@"name"],
            @"avatar":[rs stringForColumn:@"avatar"],
            @"type":[rs stringForColumn:@"type"],
            @"lastMessage":[rs stringForColumn:@"lastMessage"],
            @"lastTime":[rs stringForColumn:@"lastTime"],
            @"unreadCount":[rs stringForColumn:@"unreadCount"],
            @"loaded":[rs stringForColumn:@"loaded"],
            @"loading":[rs stringForColumn:@"loading"],
            @"top":[rs stringForColumn:@"top"]
        };
        [array addObject:chatD];
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}


- (void)deleteChatListItem:(NSDictionary *_Nonnull)item succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *vstr = @"DELETE FROM lcChatlist_";
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" WHERE id="];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,item[@"id"]];
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        NSLog(@"删除消息表失败: %@", [self.db lastErrorMessage]);
        if(fail){
            fail([NSString stringWithFormat:@"删除消息表失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        
        int changes = [self.db changes];
        if (changes > 0) {
            NSLog(@"成功删除 %d 条记录", changes);
            if (succ) {
                succ(nil);
            }
        } else {
            NSLog(@"没有匹配的记录被删除");
        }
    }
}

//聊天窗最后已读时间表
-(void)createLastreadtimetable:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *sql1=@"create table if not exists lcChatlasttime_";
    NSString *tablejg = @"(chatId CHAR PRIMARY KEY,userId CHAR,timestamp BIGINT,type CHAR)";
    NSString *sql = [NSString stringWithFormat:@"%@%@ %@",sql1,userid,tablejg];
    //NSLog(@"sql:%@",sql);
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"创建lcChatlasttime_表失败: %@", [self.db lastErrorMessage]);
        if(fail){
            fail([NSString stringWithFormat:@"创建消息表失败: %@", [self.db lastErrorMessage]]);
        }
    } else {
        NSLog(@"创建lcChatlasttime_表成功");
        if (succ) {
            succ(nil);
        }
    }
}


-(void)deleatLastreadtimetable{
    
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    NSString *sql1=@"DROP TABLE lcChatlasttime_";
    NSString *sql = [NSString stringWithFormat:@"%@%@",sql1,userid];
    BOOL success = [self.db executeUpdate:sql];
    if (!success) {
        NSLog(@"删除lcChatlasttime_表失败: %@", [self.db lastErrorMessage]);
        
    } else {
        NSLog(@"删除lcChatlasttime_表成功");
        
    }
}


-(void)insertLastreadtime:(NSDictionary *_Nonnull)msg succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    NSString *vstr = @"INSERT OR REPLACE INTO lcChatlasttime_";
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    
    vstr=[NSString stringWithFormat:@"%@%@%@",vstr,userid,@" ('chatId','userId','timestamp','type') values ("];
    
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,msg[@"chatId"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,msg[@"userId"]];
    vstr=[NSString stringWithFormat:@"%@'%@',",vstr,msg[@"timestamp"]];
    vstr=[NSString stringWithFormat:@"%@'%@')",vstr,msg[@"type"]];
    
    NSLog(@"vstr:%@",vstr);
    BOOL success = [self.db executeUpdate:vstr];
    if (!success) {
        if(fail){
            fail([NSString stringWithFormat:@"插入Lastreadtime表失败: %@", [self.db lastErrorMessage]]);
        }
        NSLog(@"插入消息表失败: %@", [self.db lastErrorMessage]);
    } else {
        NSLog(@"插入Lastreadtime表成功 insertLocalchat");
        if (succ) {
            succ(nil);
        }
    }
}
-(void)selectchatLastreadtime:(NSString *_Nonnull)chatId succ:(DBSucc _Nullable )succ fail:(DBFail _Nullable )fail{
    // 查询数据
    NSString *vstr = @"select * from lcChatlasttime_";
    NSString *userid = [UDManager.shareInstance getSDManager:dkuserId];
    vstr=[NSString stringWithFormat:@"%@%@",vstr,userid];
    vstr=[NSString stringWithFormat:@"%@ %@%@",vstr,@"where userId=",chatId];
    
    NSLog(@"vstr:%@",vstr);
    FMResultSet *rs = [self.db executeQuery:vstr];
    NSMutableArray *array=[[NSMutableArray alloc] init];
    while ([rs next]) {
        NSDictionary *chatD=@{
            @"chatId":[rs stringForColumn:@"chatId"],
            @"userId":[rs stringForColumn:@"userId"],
            @"timestamp":[rs stringForColumn:@"timestamp"],
            @"type":[rs stringForColumn:@"type"]
        };
        [array addObject:chatD];
    }
    [rs close]; // 关闭结果集
    if(succ){
        succ(array);
    }
}



// 递归替换字典中的 NSNull 为 @""
- (NSDictionary *)replaceNullsWithEmptyStringInDictionary:(NSDictionary *)dictionary {
    NSMutableDictionary *mutableDict = [NSMutableDictionary dictionaryWithDictionary:dictionary];
    
    for (id key in [mutableDict allKeys]) {
        id value = [mutableDict objectForKey:key];
        
        if ([value isKindOfClass:[NSNull class]]) {
            [mutableDict setObject:@"" forKey:key];
        } else if ([value isKindOfClass:[NSDictionary class]]) {
            // 递归处理子字典
            [mutableDict setObject:[self replaceNullsWithEmptyStringInDictionary:value] forKey:key];
        } else if ([value isKindOfClass:[NSArray class]]) {
            // 递归处理数组
            [mutableDict setObject:[self replaceNullsWithEmptyStringInArray:value] forKey:key];
        }
    }
    
    return [NSDictionary dictionaryWithDictionary:mutableDict];
}

// 递归替换数组中的 NSNull 为 @""
- (NSArray *)replaceNullsWithEmptyStringInArray:(NSArray *)array {
    NSMutableArray *mutableArray = [NSMutableArray arrayWithArray:array];
    
    for (NSInteger i = 0; i < [mutableArray count]; i++) {
        id value = [mutableArray objectAtIndex:i];
        
        if ([value isKindOfClass:[NSNull class]]) {
            [mutableArray replaceObjectAtIndex:i withObject:@""];
        } else if ([value isKindOfClass:[NSDictionary class]]) {
            // 递归处理子字典
            [mutableArray replaceObjectAtIndex:i withObject:[self replaceNullsWithEmptyStringInDictionary:value]];
        } else if ([value isKindOfClass:[NSArray class]]) {
            // 递归处理子数组
            [mutableArray replaceObjectAtIndex:i withObject:[self replaceNullsWithEmptyStringInArray:value]];
        }
    }
    
    return [NSArray arrayWithArray:mutableArray];
}

@end