//
//  GroupDatabase.swift
//  PhoneManager
//
//  Created by edy on 2025/5/12.
//

import Foundation
import SQLite3

class GroupDatabase {
    
    static let shared = GroupDatabase()
    
    private var db: OpaquePointer?
    private let dbPath: String
    
    private init() {
        let fileURL = try! FileManager.default
            .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("maintaiGroup.sqlite")
        
        dbPath = fileURL.path
        
        if sqlite3_open(dbPath, &db) != SQLITE_OK {
            print("无法打开数据库")
            return
        }
        
        createTable()
    }
    
    private func createTable() {
        let createTableString = """
            CREATE TABLE IF NOT EXISTS groups(
                localIdentifier TEXT PRIMARY KEY,
                assetSize DOUBLE,
                createDate DOUBLE,
                mediaType INTEGER,
                groupId TEXT
            );
        """
        
        var createTableStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("成功创建groups表")
            } else {
                print("创建表失败")
            }
        } else {
            print("创建表语句准备失败")
        }
        
        sqlite3_finalize(createTableStatement)
    }
    
    // 插入数据（带去重检查）
    func insert(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String) -> Bool {
        // 先检查是否已存在相同的 localIdentifier
        if let _ = query(localIdentifier: localIdentifier) {
            // 如果已存在，则执行更新操作
            return update(localIdentifier: localIdentifier, assetSize: assetSize, createDate: createDate, mediaType: mediaType, groupId: groupId)
        }
        
        // 如果不存在，执行插入操作
        let insertStatementString = "INSERT INTO groups (localIdentifier, assetSize, createDate, mediaType, groupId) VALUES (?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (localIdentifier as NSString).utf8String, -1, nil)
            sqlite3_bind_double(insertStatement, 2, assetSize)
            sqlite3_bind_double(insertStatement, 3, createDate.timeIntervalSince1970)
            sqlite3_bind_int(insertStatement, 4, Int32(mediaType))
            sqlite3_bind_text(insertStatement, 5, (groupId as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("成功插入数据")
                sqlite3_finalize(insertStatement)
                return true
            } else {
                print("插入数据失败")
            }
        }
        sqlite3_finalize(insertStatement)
        return false
    }
    
    // 删除数据
    func delete(localIdentifier: String) -> Bool {
        let deleteStatementString = "DELETE FROM groups WHERE localIdentifier = ?;"
        var deleteStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(deleteStatement, 1, (localIdentifier as NSString).utf8String, -1, nil)
            
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("成功删除数据")
                sqlite3_finalize(deleteStatement)
                return true
            } else {
                print("删除数据失败")
            }
        }
        sqlite3_finalize(deleteStatement)
        return false
    }
    
    // 更新数据
    func update(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String) -> Bool {
        let updateStatementString = "UPDATE groups SET assetSize = ?, createDate = ?, mediaType = ?, groupId = ? WHERE localIdentifier = ?;"
        var updateStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, updateStatementString, -1, &updateStatement, nil) == SQLITE_OK {
            sqlite3_bind_double(updateStatement, 1, assetSize)
            sqlite3_bind_double(updateStatement, 2, createDate.timeIntervalSince1970)
            sqlite3_bind_int(updateStatement, 3, Int32(mediaType))
            sqlite3_bind_text(updateStatement, 4, (groupId as NSString).utf8String, -1, nil)
            sqlite3_bind_text(updateStatement, 5, (localIdentifier as NSString).utf8String, -1, nil)
            
            if sqlite3_step(updateStatement) == SQLITE_DONE {
                print("成功更新数据")
                sqlite3_finalize(updateStatement)
                return true
            } else {
                print("更新数据失败")
            }
        }
        
        sqlite3_finalize(updateStatement)
        return false
    }
    
    // 查询所有数据
    func queryAll() -> [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] {
        
        if PhotoManager.shared.permissionStatus != .authorized {
            return []
        }
        
        let queryStatementString = "SELECT * FROM groups ORDER BY rowid DESC;"
        var queryStatement: OpaquePointer?
        var result: [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] = []
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let localIdentifier = String(cString: sqlite3_column_text(queryStatement, 0))
                let assetSize = sqlite3_column_double(queryStatement, 1)
                let createDate = Date(timeIntervalSince1970: sqlite3_column_double(queryStatement, 2))
                let mediaType = Int(sqlite3_column_int(queryStatement, 3))
                let groupId = String(cString: sqlite3_column_text(queryStatement, 4))
                
                result.append((localIdentifier, assetSize, createDate, mediaType, groupId))
            }
        }
        
        sqlite3_finalize(queryStatement)
        return result
    }
    
    // 根据localIdentifier查询单条数据
    func query(localIdentifier: String) -> (localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)? {
        let queryStatementString = "SELECT * FROM groups WHERE localIdentifier = ?;"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(queryStatement, 1, (localIdentifier as NSString).utf8String, -1, nil)
            
            if sqlite3_step(queryStatement) == SQLITE_ROW {
                let localIdentifier = String(cString: sqlite3_column_text(queryStatement, 0))
                let assetSize = sqlite3_column_double(queryStatement, 1)
                let createDate = Date(timeIntervalSince1970: sqlite3_column_double(queryStatement, 2))
                let mediaType = Int(sqlite3_column_int(queryStatement, 3))
                let groupId = String(cString: sqlite3_column_text(queryStatement, 4))
                
                sqlite3_finalize(queryStatement)
                return (localIdentifier, assetSize, createDate, mediaType, groupId)
            }
        }
        
        sqlite3_finalize(queryStatement)
        return nil
    }
    
    // 根据mediaType查询数据
    func queryByMediaType(_ mediaType: Int) -> [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] {
        if PhotoManager.shared.permissionStatus != .authorized {
            return []
        }
        let queryStatementString = "SELECT * FROM groups WHERE mediaType = ?;"
        var queryStatement: OpaquePointer?
        var result: [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] = []
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            sqlite3_bind_int(queryStatement, 1, Int32(mediaType))
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let localIdentifier = String(cString: sqlite3_column_text(queryStatement, 0))
                let assetSize = sqlite3_column_double(queryStatement, 1)
                let createDate = Date(timeIntervalSince1970: sqlite3_column_double(queryStatement, 2))
                let mediaType = Int(sqlite3_column_int(queryStatement, 3))
                let groupId = String(cString: sqlite3_column_text(queryStatement, 4))
                
                result.append((localIdentifier, assetSize, createDate, mediaType, groupId))
            }
        }
        
        sqlite3_finalize(queryStatement)
        return result
    }
    
    // 根据groupId查询数据
    func queryByGroupId(_ groupId: String) -> [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] {
        let queryStatementString = "SELECT * FROM groups WHERE groupId = ?;"
        var queryStatement: OpaquePointer?
        var result: [(localIdentifier: String, assetSize: Double, createDate: Date, mediaType: Int, groupId: String)] = []
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(queryStatement, 1, (groupId as NSString).utf8String, -1, nil)
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let localIdentifier = String(cString: sqlite3_column_text(queryStatement, 0))
                let assetSize = sqlite3_column_double(queryStatement, 1)
                let createDate = Date(timeIntervalSince1970: sqlite3_column_double(queryStatement, 2))
                let mediaType = Int(sqlite3_column_int(queryStatement, 3))
                let groupId = String(cString: sqlite3_column_text(queryStatement, 4))
                
                result.append((localIdentifier, assetSize, createDate, mediaType, groupId))
            }
        }
        
        sqlite3_finalize(queryStatement)
        return result
    }
    
    // 批量删除数据
    func batchDelete(localIdentifiers: [String]) -> Bool {
        // 使用事务来确保原子性
        let beginTransaction = "BEGIN TRANSACTION;"
        let commitTransaction = "COMMIT TRANSACTION;"
        let rollbackTransaction = "ROLLBACK TRANSACTION;"
        
        // 准备删除语句
        let deleteStatementString = "DELETE FROM groups WHERE localIdentifier = ?;"
        var deleteStatement: OpaquePointer?
        
        // 开始事务
        if sqlite3_exec(db, beginTransaction, nil, nil, nil) != SQLITE_OK {
            print("开始事务失败")
            return false
        }
        
        // 准备删除语句
        if sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) == SQLITE_OK {
            // 遍历所有需要删除的标识符
            for identifier in localIdentifiers {
                // 重置语句以重新使用
                sqlite3_reset(deleteStatement)
                
                // 绑定参数
                sqlite3_bind_text(deleteStatement, 1, (identifier as NSString).utf8String, -1, nil)
                
                // 执行删除
                if sqlite3_step(deleteStatement) != SQLITE_DONE {
                    print("删除数据失败: \(identifier)")
                    // 如果有任何失败，回滚事务
                    sqlite3_exec(db, rollbackTransaction, nil, nil, nil)
                    sqlite3_finalize(deleteStatement)
                    return false
                }
            }
            
            // 完成后释放语句
            sqlite3_finalize(deleteStatement)
            
            // 提交事务
            if sqlite3_exec(db, commitTransaction, nil, nil, nil) == SQLITE_OK {
                print("成功批量删除数据")
                return true
            } else {
                print("提交事务失败")
                sqlite3_exec(db, rollbackTransaction, nil, nil, nil)
                return false
            }
        }
        
        // 如果准备语句失败
        print("准备删除语句失败")
        sqlite3_exec(db, rollbackTransaction, nil, nil, nil)
        return false
    }

    deinit {
        sqlite3_close(db)
    }
}
