MO_Chat/bugu/bugu/HXCore/DataBase/Moment+Database.swift

//
//  Moment+Database.swift
//  bugu
//
//  Created by Bugu on 2022/3/20.
//

import Foundation
import SQLite
import struct SQLite.Expression

enum MomentDataHelperSource: Int, CaseIterable {
    case timeline = 1
    case my = 2
    case others = 3
}

struct MomentDataHelper: DataHelperProtocol {
    
    typealias T = Moment
    
    static let table = Table("hx_moment")
    static var id = Expression<Int>("moment_id")
    static let uid = Expression<Int>("moment_account_id")
    static let text = Expression<String>("moment_text")
    static let content = Expression<String?>("moment_content")
    static let type = Expression<String>("moment_type")
    static let metadata = Expression<String?>("moment_meta_data")
    static let timestamp = Expression<Int64>("moment_timestamp")
    static let source = Expression<Int>("moment_source")
    
    static func createTable() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        do {
            let _ = try db.run(table.create(ifNotExists: true) { t in
                t.column(id, defaultValue: nil)
                t.column(uid, defaultValue: 0)
                t.column(text, defaultValue: nil)
                t.column(content, defaultValue: nil)
                t.column(type, defaultValue: nil)
                t.column(metadata, defaultValue: nil)
                t.column(timestamp, defaultValue: 0)
                t.column(source, defaultValue: 0)
                t.primaryKey(id, source)
            })
        } catch  {
            printLog("create hx_moment database error:\(error)")
            throw DBError.connectionErr
        }
    }
    
    static func insert(i: Moment, source: MomentDataHelperSource) throws -> Int64 {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let insert = table.insert(
            or: .replace,
            id <- i.id,
            uid <- i.uid,
            text <- i.text,
            content <- i.content,
            type <- i.type.rawValue,
            metadata <- i.metadata?.toJSONString(),
            timestamp <- i.createTime,
            self.source <- source.rawValue
        )
        do {
            // 使用事务来执行
            var rowId: Int64 = 0
            try db.transaction {
                rowId = try db.run(insert)
                
                for comment in i.commentList {
                    let _ = try MomentCommentDataHelper.insert(i: comment)
                }
            }
            guard rowId > 0 else {
                throw DBError.insertErr
            }
            return rowId
        } catch {
            throw DBError.insertErr
        }
    }
    
    static func deleteAndReplace(i: Moment) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        do {
            for source in MomentDataHelperSource.allCases {
                // 生成查询条件
                let query = table.filter(id == i.id && source.rawValue == self.source)
                // 检查查询结果是否存在
                let exists = try db.scalar(query.exists)
                
                if exists  {
                    try deleteMoment(momentId: i.id, source: source)
                }
                let _ = try insert(i: i, source: source)
            }
        } catch {
            print("Error checking existence: \(error)")
            throw DBError.updateErr
        }
    }
    
    static func deleteAllMoments(source: MomentDataHelperSource, userId: Int?) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        var query = table.filter(self.source == source.rawValue)
        if source == .others, let userId = userId {
            query = query.filter(userId == self.uid)
        }
        
        do {
            // 开始事务，以确保删除操作是原子的
            try db.transaction {
                try db.run(query.delete())
            }
        } catch {
            throw DBError.deleteErr
        }
    }
    
    static func deleteMoment(momentId: Int, source: MomentDataHelperSource? = nil) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        var query = table.filter(self.id == momentId)
        if let source = source {
            query = query.filter(self.source == source.rawValue)
        }
        
        do {
            // 开始事务，以确保删除操作是原子的
            try db.transaction {
                try db.run(query.delete())
            }
        } catch {
            throw DBError.deleteErr
        }
    }
    
    // MARK: - timeline
    static func findTimeLineMomentList(lastMoment: Moment? = nil, count: Int = 20) throws -> [Moment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        

        /// lastMoment 传入了
        if let lastMoment = lastMoment {
            
            var query = table
                .filter(self.source == MomentDataHelperSource.timeline.rawValue)
                .filter(timestamp < lastMoment.createTime)
                .order(timestamp.desc)
            
            if count > 0 {
                query = query.limit(count)
            }
            
            let items = try db.prepare(query)
            var arr = [Moment]()
            for i in items {
                arr.append(combineMoment(i: i))
            }
            return arr
        } else {
            var query = table
                .filter(self.source == MomentDataHelperSource.timeline.rawValue)
                .order(timestamp.desc)
            
            if count > 0 {
                query = query.limit(count)
            }
            
            let items = try db.prepare(query)
            var arr = [Moment]()
            for i in items {
                arr.append(combineMoment(i: i))
            }
            return arr
        }
    }
    
    static func findTimeLineLastMoment(desc: Bool) throws -> Moment? {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.source == MomentDataHelperSource.timeline.rawValue)
            .order(desc ? timestamp.desc : timestamp.asc)
            .limit(1)
        
      
        let items = try db.prepare(query)
        for i in items {
            return combineMoment(i: i)
        }
        return nil
    }
    
    
    // MARK: - My
    static func findMyMomentList(lastMoment: Moment? = nil, count: Int = 20) throws -> [Moment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }

        /// lastMoment 传入了
        if let lastMoment = lastMoment {
            
            var query = table
                .filter(self.source == MomentDataHelperSource.my.rawValue)
                .filter(timestamp < lastMoment.createTime)
                .order(timestamp.desc)
            
            if count > 0 {
                query = query.limit(count)
            }
            
            let items = try db.prepare(query)
            var arr = [Moment]()
            for i in items {
                arr.append(combineMoment(i: i))
            }
            return arr
        } else {
            var query = table
                .filter(self.source == MomentDataHelperSource.my.rawValue)
                .order(timestamp.desc)
            
            if count > 0 {
                query = query.limit(count)
            }
            
            let items = try db.prepare(query)
            var arr = [Moment]()
            for i in items {
                arr.append(combineMoment(i: i))
            }
            return arr
        }
    }
    
    static func findMyLastMoment(desc: Bool) throws -> Moment? {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.source == MomentDataHelperSource.my.rawValue)
            .order(desc ? timestamp.desc : timestamp.asc)
            .limit(1)
        
      
        let items = try db.prepare(query)
        for i in items {
            return combineMoment(i: i)
        }
        return nil
    }
    
    // MARK: - others
    static func findOthersMomentList(userId: Int) throws -> [Moment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.source == MomentDataHelperSource.others.rawValue && uid == userId)
            .order(timestamp.desc)
            .limit(20)
        
        var arr = [Moment]()
        let items = try db.prepare(query)
        for i in items {
            arr.append(combineMoment(i: i))
        }
        return arr
    }
    
    static func findOthersLastMoment(userId: Int, desc: Bool) throws -> Moment? {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.source == MomentDataHelperSource.others.rawValue && uid == userId)
            .order(desc ? timestamp.desc : timestamp.asc)
            .limit(1)
        
      
        let items = try db.prepare(query)
        for i in items {
            return combineMoment(i: i)
        }
        return nil
    }
    
    static func findLastFiveMediaMoments(userId: Int) throws -> [Moment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }

        var query: QueryType
        if userId == AppStorage.user.userId {
            query = table.filter(self.source == MomentDataHelperSource.my.rawValue)
        } else {
            query = table.filter(self.source == MomentDataHelperSource.others.rawValue && uid == userId)
        }
        
        query = query.filter(self.type == MomentTypeEnum.simplePicture.rawValue || self.type == MomentTypeEnum.multiPicture.rawValue || self.type == MomentTypeEnum.video.rawValue)
            .order(timestamp.desc)
            .limit(5)
        
        var arr = [Moment]()
        let items = try db.prepare(query)
        for i in items {
            arr.append(combineMoment(i: i))
        }
        return arr
        
    }
    
    static func findMoment(momentId: Int) throws -> Moment? {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let query = table.filter(id == momentId)
            .limit(1)
        do {
            let items = try db.prepare(query)
            for i in items {
                return combineMoment(i: i)
            }
        } catch {
            throw DBError.deleteErr
        }
        return nil
    }
    
    static func filterMoment(filter: String) throws -> [Moment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table
            .filter(text.like("%\(filter)%"))
            .order(timestamp.desc)
        
        let items = try db.prepare(query)
        var array: [Moment] = []
        for i in items {
            let moment = combineMoment(i: i)
            array.append(moment)
        }
        return array
    }
    
    
}

extension MomentDataHelper {
    
    static func combineMoment(i: Row) -> Moment {
        let momentId = i[id]
        let comments = try? MomentCommentDataHelper.find(momentId: momentId)
        
        let moment = Moment(id: momentId,
                            uid: i[uid],
                            text: i[text],
                            content: i[content],
                            type: MomentTypeEnum(rawValue: i[type]) ?? .text,
                            commentList: comments ?? [],
                            metadata: MomentAddition.deserialize(from: i[metadata]),
                            createTime: i[timestamp])
        return moment
    }
}

struct MomentCommentDataHelper: DataHelperProtocol {
    typealias T = MomentComment
    
    static let table = Table("hx_moment_comment")
    static var id = Expression<Int>("moment_comment_id")
    static let parentId = Expression<Int>("moment_parent_id")
    static let momentId = Expression<Int>("moment_id")
    static let uid = Expression<Int>("moment_account_id")
    static let type = Expression<Int>("moment_comment_type")
    static let content = Expression<String?>("moment_comment_content")
    static let deleted = Expression<Bool>("moment_comment_deleted")
    static let timestamp = Expression<Int>("moment_comment_timestamp")
    
    static func createTable() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        do {
            let _ = try db.run(table.create(ifNotExists: true) { t in
                t.column(id, primaryKey: true)
                t.column(parentId, defaultValue: 0)
                t.column(momentId, defaultValue: 0)
                t.column(uid, defaultValue: 0)
                t.column(type, defaultValue: 0)
                t.column(content, defaultValue: nil)
                t.column(deleted, defaultValue: false)
                t.column(timestamp, defaultValue: 0)
            })
        } catch  {
            printLog("create hx_moment_comment database error:\(error)")
            throw DBError.connectionErr
        }
    }
    
    static func insert(i: MomentComment) throws -> Int64 {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let insert = table.insert(
            or: .replace,
            id <- i.id,
            parentId <- i.parentId,
            momentId <- i.momentId,
            uid <- i.uid,
            type <- i.type,
            content <- i.content,
            deleted <- i.deleted,
            timestamp <- i.createTime
        )
        do {
            let rowId = try db.run(insert)
            guard rowId > 0 else {
                throw DBError.insertErr
            }
            return rowId
        } catch {
            throw DBError.insertErr
        }
    }
    
    static func find(commentId: Int) throws -> MomentComment? {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let query = table.filter(id == commentId && deleted == false)
            .limit(1)
        do {
            let items = try db.prepare(query)
            for i in items {
                return combineMomentComment(i: i)
            }
        } catch {
            throw DBError.deleteErr
        }
        return nil
    }
    
    static func find(momentId: Int) throws -> [MomentComment] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(momentId == self.momentId && deleted == false)
            .order(timestamp.asc)
        
        var arr = [MomentComment]()
        let items = try db.prepare(query)
        for i in items {
            arr.append(combineMomentComment(i: i))
        }
        return arr
    }
    
    static func delete(commentId: Int) throws  {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.id == commentId).update(deleted <- true)
        try db.run(query)
    }
}


extension MomentCommentDataHelper {
    
    static func combineMomentComment(i: Row) -> MomentComment {
        let comment = MomentComment(id: i[id],
                                    parentId: i[parentId],
                                    momentId: i[momentId],
                                    uid: i[uid],
                                    type: i[type],
                                    content: i[content] ?? "",
                                    deleted: i[deleted],
                                    createTime: i[timestamp])
        return comment
        
    }
}

struct MomentRuleDataHelper: DataHelperProtocol {
    typealias T = MomentRule

    static let table = Table("hx_moment_rule")
    static var id = Expression<Int>("id")
    static let uid = Expression<Int>("account_id")
    static let targetId = Expression<Int>("target_id")
    static let type = Expression<Int>("type")

    static func createTable() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        do {
            let _ = try db.run(table.create(ifNotExists: true) { t in
                t.column(id, primaryKey: .autoincrement)
                t.column(uid, defaultValue: 0)
                t.column(targetId, defaultValue: 0)
                t.column(type, defaultValue: 0)
            })
        } catch  {
            printLog("create hx_moment_rule database error:\(error)")
            throw DBError.connectionErr
        }
    } // end createTable
    
    static func rebuildTable() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        do {
            let _ = try db.run(table.drop(ifExists: true))
            try createTable()
            
        } catch {
            printLog("update hx_group_call database error:\(error)")
            throw DBError.updateTablesErr
        }
    }
    
    static func deleteAndInsert(list: [MomentRule], type: MomentRuleType) throws {
        do {
            try deleteAll(type: type)
            
            for i in list {
               let _ = try insert(i: i)
            }
        } catch {
            printLog("deleteAndInsertMomentRule error\(error)")
            throw error
        }
    }
    
    static func insert(i: MomentRule) throws -> Int64 {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
                
        let insert = table.insert(
            or: .replace,
            uid <- i.uid,
            type <- i.type.rawValue,
            targetId <- i.targetId
        )
        do {
            let rowId = try db.run(insert)
            guard rowId > 0 else {
                throw DBError.insertErr
            }
            return rowId
        } catch {
            throw DBError.insertErr
        }
    } // end insert
    
    static func delete(userId: Int, permission: MomentRuleType) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let query = table.filter(uid == AppStorage.user.userId! && targetId == userId && type == permission.rawValue)
        do {
            try db.run(query.delete())
        } catch {
            throw DBError.deleteErr
        }
    } // end delete
    
    static func findAll() throws -> [MomentRule] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        var arr = [MomentRule]()
        let items = try db.prepare(table)
        for i in items {
            arr.append(MomentRule(
                id: i[id],
                uid: i[uid],
                targetId: i[targetId],
                type: MomentRuleType(rawValue: i[type]) ?? .noLookMe)
            )
        }
        return arr
    } // end find all
    
    static func find(targetUserId: Int) throws -> [MomentRule] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        var arr = [MomentRule]()
        let query = table.filter(targetId == targetUserId)
        let items = try db.prepare(query)
        for i in items {
            arr.append(MomentRule(
                id: i[id],
                uid: i[uid],
                targetId: i[targetId],
                type: MomentRuleType(rawValue: i[type]) ?? .noLookMe)
            )
        }
        return arr
    }
    
    static func deleteAll(type: MomentRuleType) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        do {
            // 开始事务，以确保删除操作是原子的
            let query = table.filter(self.type == type.rawValue)
            try db.transaction {
                try db.run(query.delete())
            }
        } catch {
            printLog("deleteAllMomentRules error\(error)")
            throw DBError.deleteErr
        }
    }
    
}

struct MomentMessageDataHelper: DataHelperProtocol {
    
    typealias T = MomentMessage
    
    static let table = Table("hx_moment_message")
    static var messageId = Expression<Int>("moment_message_id")
    static var momentId = Expression<Int>("moment_id")
    static let userId = Expression<Int>("moment_account_id")
    static let parentCommentUserId = Expression<Int?>("moment_parent_account_id")
    static let commentId = Expression<Int?>("moment_comment_id")
    static let messageType = Expression<String?>("moment_message_type")
    static let isRead = Expression<Bool>("moment_message_is_read")
    static let timestamp = Expression<Int64>("moment_timestamp")
    
    static func createTable() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        do {
            let _ = try db.run(table.create(ifNotExists: true) { t in
                t.column(messageId, primaryKey: true)
                t.column(momentId, defaultValue: 0)
                t.column(userId, defaultValue: 0)
                t.column(parentCommentUserId, defaultValue: 0)
                t.column(commentId, defaultValue: 0)
                t.column(messageType, defaultValue: nil)
                t.column(isRead, defaultValue: false)
                t.column(timestamp, defaultValue: 0)
            })
        } catch  {
            printLog("create hx_moment_message database error:\(error)")
            throw DBError.connectionErr
        }
    }
    
    static func insert(i: MomentMessage, type: HXMessageAction.MomentLine) throws -> Int64 {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        let insert = table.insert(
            or: .replace,
            messageId <- i.messageId,
            momentId <- i.momentId,
            userId <- i.userId,
            parentCommentUserId <- i.parentCommentUserId,
            commentId <- i.commentId,
            messageType <- type.rawValue,
            isRead <- false,
            timestamp <- i.createTime
        )
        do {
            let rowId = try db.run(insert)
            guard rowId > 0 else {
                throw DBError.insertErr
            }
            return rowId
        } catch {
            throw DBError.insertErr
        }
    }
    
    static func findNewMomentMessage() throws -> [MomentMessage] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(messageType == HXMessageAction.MomentLine.commented.rawValue)
            .filter(isRead == false)
            .order(timestamp.desc)
        
        var arr = [MomentMessage]()
        let items = try db.prepare(query)
        for i in items {
            arr.append(combineMomentMessage(i: i))
        }
        return arr
    }
    
    
    static func findMomentMessages() throws -> [MomentMessage] {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(messageType == HXMessageAction.MomentLine.commented.rawValue)
            .order(timestamp.desc)
        
        var arr = [MomentMessage]()
        let items = try db.prepare(query)
        for i in items {
            arr.append(combineMomentMessage(i: i))
        }
        return arr
    }
    
    static func deleteMomentMessage(messageId: Int) throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }

        let query = table.filter(self.messageId == messageId)
        do {
            // 开始事务，以确保删除操作是原子的
            try db.transaction {
                try db.run(query.delete())
            }
        } catch {
            throw DBError.deleteErr
        }
    }
    
    static func deleteMomentMessage(commentId: Int) throws  {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        let query = table.filter(self.commentId == commentId)
        try db.run(query.delete())
    }
    
    static func deleteAll() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        do {
            // 开始事务，以确保删除操作是原子的
            try db.transaction {
                try db.run(table.delete())
            }
        } catch {
            throw DBError.deleteErr
        }
    }
    
    static func readAll() throws {
        guard let db = Database.shared.connection else {
            throw DBError.connectionErr
        }
        
        try db.run(table.update(isRead <- true))
    }
    
}

extension MomentMessageDataHelper {
    
    static func combineMomentMessage(i: Row) -> MomentMessage {
        let message = MomentMessage(messageId: i[messageId],
                                    momentId: i[momentId],
                                    userId: i[userId],
                                    commentId: i[commentId],
                                    parentCommentUserId: i[parentCommentUserId],
                                    createTime: i[timestamp])
        return message
    }
}