//
//  DatabaseManager.swift
//  SwiftWarplyFramework
//
//  Created by Manos Chorianopoulos on 24/6/25.
//

import Foundation
import SQLite

// MARK: - Import Security Components
// Import FieldEncryption for token encryption capabilities
// This enables optional field-level encryption for sensitive token data

/// DatabaseManager handles all SQLite database operations for the Warply framework
/// This includes token storage, event queuing, and geofencing data management
actor DatabaseManager {
    
    // MARK: - Singleton
    static let shared = DatabaseManager()
    
    // MARK: - Database Connection
    private var db: Connection?
    
    // MARK: - Encryption Configuration
    private var fieldEncryption: FieldEncryption?
    private var databaseConfig: WarplyDatabaseConfig = WarplyDatabaseConfig()
    private var encryptionEnabled: Bool = false
    
    // MARK: - Database Path
    private var dbPath: String {
        let documentsPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
        let bundleId = Bundle.main.bundleIdentifier ?? "unknown"
        return "\(documentsPath)/WarplyCache_\(bundleId).db"
    }
    
    // MARK: - Table Definitions (matches original Objective-C schema)
    
    // requestVariables table for token storage
    private let requestVariables = Table("requestVariables")
    private let id = Expression<Int64>(value: "id")
    private let clientId = Expression<String?>(value: "client_id")
    private let clientSecret = Expression<String?>(value: "client_secret")
    private let accessToken = Expression<String?>(value: "access_token")
    private let refreshToken = Expression<String?>(value: "refresh_token")
    
    // events table for analytics queuing
    private let events = Table("events")
    private let eventId = Expression<Int64>(value: "_id")
    private let eventType = Expression<String>(value: "type")
    private let eventTime = Expression<String>(value: "time")
    private let eventData = Expression<Data>(value: "data")
    private let eventPriority = Expression<Int>(value: "priority")
    
    // pois table for geofencing
    private let pois = Table("pois")
    private let poiId = Expression<Int64>(value: "id")
    private let latitude = Expression<Double>(value: "lat")
    private let longitude = Expression<Double>(value: "lon")
    private let radius = Expression<Double>(value: "radius")
    
    // schema_version table for database migration management
    private let schemaVersion = Table("schema_version")
    private let versionId = Expression<Int64>(value: "id")
    private let versionNumber = Expression<Int>(value: "version")
    private let versionCreatedAt = Expression<Date>(value: "created_at")
    
    // MARK: - Database Version Management
    private static let currentDatabaseVersion = 1
    private static let supportedVersions = [1] // Add new versions here as schema evolves
    
    // MARK: - Initialization
    private init() {
        Task {
            await initializeDatabase()
        }
    }
    
    // MARK: - Database Initialization
    private func initializeDatabase() async {
        do {
            print("🗄️ [DatabaseManager] Initializing database at: \(dbPath)")
            
            // Create connection
            db = try Connection(dbPath)
            
            // Create tables if they don't exist
            try await createTables()
            
            print("✅ [DatabaseManager] Database initialized successfully")
            
        } catch {
            print("❌ [DatabaseManager] Failed to initialize database: \(error)")
        }
    }
    
    // MARK: - Table Creation and Migration
    private func createTables() async throws {
        guard db != nil else {
            throw DatabaseError.connectionNotAvailable
        }
        
        // First, create schema version table if it doesn't exist
        try await createSchemaVersionTable()
        
        // Check current database version
        let currentVersion = try await getCurrentDatabaseVersion()
        print("🔍 [DatabaseManager] Current database version: \(currentVersion)")
        
        // Perform migration if needed
        if currentVersion < Self.currentDatabaseVersion {
            try await migrateDatabase(from: currentVersion, to: Self.currentDatabaseVersion)
        } else if currentVersion == 0 {
            // Fresh installation - create all tables
            try await createAllTables()
            try await setDatabaseVersion(Self.currentDatabaseVersion)
        } else {
            // Database is up to date, validate schema
            try await validateDatabaseSchema()
        }
        
        print("✅ [DatabaseManager] Database schema ready (version \(Self.currentDatabaseVersion))")
    }
    
    /// Create schema version table for migration tracking
    private func createSchemaVersionTable() async throws {
        guard let database = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        try database.run(schemaVersion.create(ifNotExists: true) { t in
            t.column(versionId, primaryKey: .autoincrement)
            t.column(versionNumber, unique: true)
            t.column(versionCreatedAt)
        })
        
        print("✅ [DatabaseManager] Schema version table ready")
    }
    
    /// Create all application tables (for fresh installations)
    private func createAllTables() async throws {
        guard db != nil else {
            throw DatabaseError.connectionNotAvailable
        }
        
        print("🏗️ [DatabaseManager] Creating all tables for fresh installation...")
        
        // Create requestVariables table
        try await createTableIfNotExists(requestVariables, "requestVariables") { t in
            t.column(id, primaryKey: .autoincrement)
            t.column(clientId)
            t.column(clientSecret)
            t.column(accessToken)
            t.column(refreshToken)
        }
        
        // Create events table
        try await createTableIfNotExists(events, "events") { t in
            t.column(eventId, primaryKey: .autoincrement)
            t.column(eventType)
            t.column(eventTime)
            t.column(eventData)
            t.column(eventPriority)
        }
        
        // Create pois table
        try await createTableIfNotExists(pois, "pois") { t in
            t.column(poiId, primaryKey: true)
            t.column(latitude)
            t.column(longitude)
            t.column(radius)
        }
        
        print("✅ [DatabaseManager] All tables created successfully")
    }
    
    /// Create table with existence check and validation
    private func createTableIfNotExists(_ table: Table, _ tableName: String, creation: (TableBuilder) -> Void) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        let exists = try await tableExists(tableName)
        if !exists {
            print("🏗️ [DatabaseManager] Creating table: \(tableName)")
            try db.run(table.create(ifNotExists: true) { t in
                creation(t)
            })
            print("✅ [DatabaseManager] Table \(tableName) created successfully")
        } else {
            print("ℹ️ [DatabaseManager] Table \(tableName) already exists")
            try await validateTableSchema(table, tableName)
        }
    }
    
    /// Check if a table exists in the database
    private func tableExists(_ tableName: String) async throws -> Bool {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            let count = try db.scalar(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?",
                tableName
            ) as! Int64
            return count > 0
        } catch {
            print("❌ [DatabaseManager] Failed to check table existence for \(tableName): \(error)")
            throw DatabaseError.queryFailed("tableExists")
        }
    }
    
    /// Validate table schema integrity
    private func validateTableSchema(_ table: Table, _ tableName: String) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            // Basic validation - try to query the table structure
            let _ = try db.prepare("PRAGMA table_info(\(tableName))")
            print("✅ [DatabaseManager] Table \(tableName) schema validation passed")
        } catch {
            print("⚠️ [DatabaseManager] Table \(tableName) schema validation failed: \(error)")
            throw DatabaseError.tableCreationFailed
        }
    }
    
    /// Validate entire database schema
    private func validateDatabaseSchema() async throws {
        print("🔍 [DatabaseManager] Validating database schema...")
        
        try await validateTableSchema(requestVariables, "requestVariables")
        try await validateTableSchema(events, "events")
        try await validateTableSchema(pois, "pois")
        
        print("✅ [DatabaseManager] Database schema validation completed")
    }
    
    // MARK: - Database Version Management
    
    /// Get current database version
    private func getCurrentDatabaseVersion() async throws -> Int {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            // Check if schema_version table exists
            let tableExists = try await self.tableExists("schema_version")
            if !tableExists {
                return 0 // Fresh installation
            }
            
            // Get the latest version
            if let row = try db.pluck(schemaVersion.order(versionNumber.desc).limit(1)) {
                return row[versionNumber]
            } else {
                return 0 // No version recorded yet
            }
        } catch {
            print("❌ [DatabaseManager] Failed to get database version: \(error)")
            return 0 // Assume fresh installation on error
        }
    }
    
    /// Set database version
    private func setDatabaseVersion(_ version: Int) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            try db.run(schemaVersion.insert(
                versionNumber <- version,
                versionCreatedAt <- Date()
            ))
            print("✅ [DatabaseManager] Database version set to \(version)")
        } catch {
            print("❌ [DatabaseManager] Failed to set database version: \(error)")
            throw DatabaseError.queryFailed("setDatabaseVersion")
        }
    }
    
    /// Migrate database from one version to another
    private func migrateDatabase(from oldVersion: Int, to newVersion: Int) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        print("🔄 [DatabaseManager] Migrating database from version \(oldVersion) to \(newVersion)")
        
        // Validate migration path
        guard Self.supportedVersions.contains(newVersion) else {
            throw DatabaseError.queryFailed("Unsupported database version: \(newVersion)")
        }
        
        // Begin transaction for atomic migration
        try db.transaction {
            // Perform version-specific migrations
            for version in (oldVersion + 1)...newVersion {
                try self.performMigration(to: version)
            }
            
            // Update version
            try db.run(schemaVersion.insert(
                versionNumber <- newVersion,
                versionCreatedAt <- Date()
            ))
        }
        
        print("✅ [DatabaseManager] Database migration completed successfully")
    }
    
    /// Perform migration to specific version
    private func performMigration(to version: Int) throws {
        guard db != nil else {
            throw DatabaseError.connectionNotAvailable
        }
        
        print("🔄 [DatabaseManager] Performing migration to version \(version)")
        
        switch version {
        case 1:
            // Version 1: Initial schema creation
            try performMigrationToV1()
            
        // Add future migrations here:
        // case 2:
        //     try performMigrationToV2()
        
        default:
            throw DatabaseError.queryFailed("Unknown migration version: \(version)")
        }
        
        print("✅ [DatabaseManager] Migration to version \(version) completed")
    }
    
    /// Migration to version 1 (initial schema)
    private func performMigrationToV1() throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        print("🔄 [DatabaseManager] Performing migration to V1 (initial schema)")
        
        // Create requestVariables table
        try db.run(requestVariables.create(ifNotExists: true) { t in
            t.column(id, primaryKey: .autoincrement)
            t.column(clientId)
            t.column(clientSecret)
            t.column(accessToken)
            t.column(refreshToken)
        })
        
        // Create events table
        try db.run(events.create(ifNotExists: true) { t in
            t.column(eventId, primaryKey: .autoincrement)
            t.column(eventType)
            t.column(eventTime)
            t.column(eventData)
            t.column(eventPriority)
        })
        
        // Create pois table
        try db.run(pois.create(ifNotExists: true) { t in
            t.column(poiId, primaryKey: true)
            t.column(latitude)
            t.column(longitude)
            t.column(radius)
        })
        
        print("✅ [DatabaseManager] V1 migration completed")
    }
    
    // MARK: - Database Integrity and Recovery
    
    /// Check database integrity
    func checkDatabaseIntegrity() async throws -> Bool {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🔍 [DatabaseManager] Checking database integrity...")
            
            let result = try db.scalar("PRAGMA integrity_check") as! String
            let isIntact = result == "ok"
            
            if isIntact {
                print("✅ [DatabaseManager] Database integrity check passed")
            } else {
                print("❌ [DatabaseManager] Database integrity check failed: \(result)")
            }
            
            return isIntact
        } catch {
            print("❌ [DatabaseManager] Database integrity check error: \(error)")
            throw DatabaseError.queryFailed("checkDatabaseIntegrity")
        }
    }
    
    /// Get database version information
    func getDatabaseVersionInfo() async throws -> (currentVersion: Int, supportedVersions: [Int]) {
        let currentVersion = try await getCurrentDatabaseVersion()
        return (currentVersion, Self.supportedVersions)
    }
    
    /// Force database recreation (emergency recovery)
    func recreateDatabase() async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        print("🚨 [DatabaseManager] Recreating database (emergency recovery)")
        
        // Close current connection
        self.db = nil
        
        // Remove database file
        let fileManager = FileManager.default
        if fileManager.fileExists(atPath: dbPath) {
            try fileManager.removeItem(atPath: dbPath)
            print("🗑️ [DatabaseManager] Old database file removed")
        }
        
        // Reinitialize database
        await initializeDatabase()
        
        print("✅ [DatabaseManager] Database recreated successfully")
    }
    
    // MARK: - Token Management Methods
    
    /// Store authentication tokens (UPSERT operation)
    func storeTokens(accessTokenValue: String, refreshTokenValue: String, clientIdValue: String? = nil, clientSecretValue: String? = nil) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🔐 [DatabaseManager] Storing tokens...")
            
            // Check if tokens already exist
            let existingCount = try db.scalar(requestVariables.count)
            
            if existingCount > 0 {
                // Update existing tokens
                try db.run(requestVariables.update(
                    accessToken <- accessTokenValue,
                    refreshToken <- refreshTokenValue,
                    clientId <- clientIdValue,
                    clientSecret <- clientSecretValue
                ))
                print("✅ [DatabaseManager] Tokens updated successfully")
            } else {
                // Insert new tokens
                try db.run(requestVariables.insert(
                    accessToken <- accessTokenValue,
                    refreshToken <- refreshTokenValue,
                    clientId <- clientIdValue,
                    clientSecret <- clientSecretValue
                ))
                print("✅ [DatabaseManager] Tokens inserted successfully")
            }
            
        } catch {
            print("❌ [DatabaseManager] Failed to store tokens: \(error)")
            throw DatabaseError.queryFailed("storeTokens")
        }
    }
    
    /// Retrieve access token
    func getAccessToken() async throws -> String? {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            if let row = try db.pluck(requestVariables) {
                let token = row[accessToken]
                print("🔐 [DatabaseManager] Retrieved access token: \(token != nil ? "✅" : "❌")")
                return token
            }
            return nil
        } catch {
            print("❌ [DatabaseManager] Failed to get access token: \(error)")
            throw DatabaseError.queryFailed("getAccessToken")
        }
    }
    
    /// Retrieve refresh token
    func getRefreshToken() async throws -> String? {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            if let row = try db.pluck(requestVariables) {
                let token = row[refreshToken]
                print("🔐 [DatabaseManager] Retrieved refresh token: \(token != nil ? "✅" : "❌")")
                return token
            }
            return nil
        } catch {
            print("❌ [DatabaseManager] Failed to get refresh token: \(error)")
            throw DatabaseError.queryFailed("getRefreshToken")
        }
    }
    
    /// Retrieve client credentials
    func getClientCredentials() async throws -> (clientId: String?, clientSecret: String?) {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            if let row = try db.pluck(requestVariables) {
                let id = row[clientId]
                let secret = row[clientSecret]
                print("🔐 [DatabaseManager] Retrieved client credentials: \(id != nil ? "✅" : "❌")")
                return (id, secret)
            }
            return (nil, nil)
        } catch {
            print("❌ [DatabaseManager] Failed to get client credentials: \(error)")
            throw DatabaseError.queryFailed("getClientCredentials")
        }
    }
    
    /// Clear all tokens
    func clearTokens() async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🗑️ [DatabaseManager] Clearing all tokens...")
            try db.run(requestVariables.delete())
            print("✅ [DatabaseManager] All tokens cleared successfully")
        } catch {
            print("❌ [DatabaseManager] Failed to clear tokens: \(error)")
            throw DatabaseError.queryFailed("clearTokens")
        }
    }
    
    /// Get TokenModel synchronously (for use in synchronous contexts)
    /// - Returns: TokenModel if available, nil otherwise
    /// - Throws: DatabaseError if database access fails
    func getTokenModelSync() throws -> TokenModel? {
        print("🔍 [DatabaseManager] Retrieving TokenModel synchronously from database")
        
        guard let db = db else {
            print("❌ [DatabaseManager] Database not initialized")
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            // Query the requestVariables table for tokens
            if let row = try db.pluck(requestVariables) {
                let storedAccessToken = row[accessToken]
                let storedRefreshToken = row[refreshToken]
                let storedClientId = row[clientId]
                let storedClientSecret = row[clientSecret]
                
                guard let accessTokenValue = storedAccessToken,
                      let refreshTokenValue = storedRefreshToken else {
                    print("ℹ️ [DatabaseManager] No complete tokens found in database")
                    return nil
                }
                
                // Decrypt tokens if encryption is enabled
                let decryptedAccessToken: String
                let decryptedRefreshToken: String
                
                if encryptionEnabled, let fieldEncryption = fieldEncryption {
                    // For synchronous operation, we need to handle encryption differently
                    // Since FieldEncryption methods are async, we'll use a simplified approach
                    // This is a fallback - ideally use async methods when possible
                    print("⚠️ [DatabaseManager] Encryption enabled but using synchronous access - tokens may be encrypted")
                    decryptedAccessToken = accessTokenValue
                    decryptedRefreshToken = refreshTokenValue
                } else {
                    decryptedAccessToken = accessTokenValue
                    decryptedRefreshToken = refreshTokenValue
                }
                
                let tokenModel = TokenModel(
                    accessToken: decryptedAccessToken,
                    refreshToken: decryptedRefreshToken,
                    clientId: storedClientId,
                    clientSecret: storedClientSecret
                )
                
                print("✅ [DatabaseManager] TokenModel retrieved synchronously")
                if let tokenModel = tokenModel {
                    print("   Token Status: \(tokenModel.statusDescription)")
                    print("   Expiration: \(tokenModel.expirationInfo)")
                }
                
                return tokenModel
            } else {
                print("ℹ️ [DatabaseManager] No tokens found in database")
                return nil
            }
        } catch {
            print("❌ [DatabaseManager] Failed to retrieve TokenModel synchronously: \(error)")
            throw DatabaseError.queryFailed(error.localizedDescription)
        }
    }
    
    // MARK: - Event Queue Management Methods
    
    /// Store analytics event for offline queuing
    func storeEvent(type: String, data: Data, priority: Int = 1) async throws -> Int64 {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            let timestamp = ISO8601DateFormatter().string(from: Date())
            print("📊 [DatabaseManager] Storing event: \(type)")
            
            let eventRowId = try db.run(events.insert(
                eventType <- type,
                eventTime <- timestamp,
                eventData <- data,
                eventPriority <- priority
            ))
            
            print("✅ [DatabaseManager] Event stored with ID: \(eventRowId)")
            return eventRowId
        } catch {
            print("❌ [DatabaseManager] Failed to store event: \(error)")
            throw DatabaseError.queryFailed("storeEvent")
        }
    }
    
    /// Retrieve pending events (ordered by priority and time)
    func getPendingEvents(limit: Int = 100) async throws -> [(id: Int64, type: String, data: Data, priority: Int, time: String)] {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            var pendingEvents: [(id: Int64, type: String, data: Data, priority: Int, time: String)] = []
            
            // Order by priority (higher first), then by time (older first)
            let query = events.order(eventPriority.desc, eventTime.asc).limit(limit)
            
            for row in try db.prepare(query) {
                pendingEvents.append((
                    id: row[eventId],
                    type: row[eventType],
                    data: row[eventData],
                    priority: row[eventPriority],
                    time: row[eventTime]
                ))
            }
            
            print("📊 [DatabaseManager] Retrieved \(pendingEvents.count) pending events")
            return pendingEvents
        } catch {
            print("❌ [DatabaseManager] Failed to get pending events: \(error)")
            throw DatabaseError.queryFailed("getPendingEvents")
        }
    }
    
    /// Remove processed event
    func removeEvent(eventId: Int64) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🗑️ [DatabaseManager] Removing event ID: \(eventId)")
            let deletedCount = try db.run(events.filter(self.eventId == eventId).delete())
            
            if deletedCount > 0 {
                print("✅ [DatabaseManager] Event removed successfully")
            } else {
                print("⚠️ [DatabaseManager] Event not found")
            }
        } catch {
            print("❌ [DatabaseManager] Failed to remove event: \(error)")
            throw DatabaseError.queryFailed("removeEvent")
        }
    }
    
    /// Clear all events
    func clearAllEvents() async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🗑️ [DatabaseManager] Clearing all events...")
            let deletedCount = try db.run(events.delete())
            print("✅ [DatabaseManager] Cleared \(deletedCount) events")
        } catch {
            print("❌ [DatabaseManager] Failed to clear events: \(error)")
            throw DatabaseError.queryFailed("clearAllEvents")
        }
    }
    
    // MARK: - Geofencing (POI) Management Methods
    
    /// Store Point of Interest for geofencing
    func storePOI(id: Int64, latitude: Double, longitude: Double, radius: Double) async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("📍 [DatabaseManager] Storing POI ID: \(id)")
            
            // Use INSERT OR REPLACE for UPSERT behavior
            try db.run(pois.insert(or: .replace,
                poiId <- id,
                self.latitude <- latitude,
                self.longitude <- longitude,
                self.radius <- radius
            ))
            
            print("✅ [DatabaseManager] POI stored successfully")
        } catch {
            print("❌ [DatabaseManager] Failed to store POI: \(error)")
            throw DatabaseError.queryFailed("storePOI")
        }
    }
    
    /// Retrieve all POIs
    func getPOIs() async throws -> [(id: Int64, latitude: Double, longitude: Double, radius: Double)] {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            var poisList: [(id: Int64, latitude: Double, longitude: Double, radius: Double)] = []
            
            for row in try db.prepare(pois) {
                poisList.append((
                    id: row[poiId],
                    latitude: row[latitude],
                    longitude: row[longitude],
                    radius: row[radius]
                ))
            }
            
            print("📍 [DatabaseManager] Retrieved \(poisList.count) POIs")
            return poisList
        } catch {
            print("❌ [DatabaseManager] Failed to get POIs: \(error)")
            throw DatabaseError.queryFailed("getPOIs")
        }
    }
    
    /// Clear all POIs
    func clearPOIs() async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🗑️ [DatabaseManager] Clearing all POIs...")
            let deletedCount = try db.run(pois.delete())
            print("✅ [DatabaseManager] Cleared \(deletedCount) POIs")
        } catch {
            print("❌ [DatabaseManager] Failed to clear POIs: \(error)")
            throw DatabaseError.queryFailed("clearPOIs")
        }
    }
    
    // MARK: - Database Maintenance Methods
    
    /// Get database statistics
    func getDatabaseStats() async throws -> (tokensCount: Int, eventsCount: Int, poisCount: Int) {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            let tokensCount = try db.scalar(requestVariables.count)
            let eventsCount = try db.scalar(events.count)
            let poisCount = try db.scalar(pois.count)
            
            print("📊 [DatabaseManager] Stats - Tokens: \(tokensCount), Events: \(eventsCount), POIs: \(poisCount)")
            return (tokensCount, eventsCount, poisCount)
        } catch {
            print("❌ [DatabaseManager] Failed to get database stats: \(error)")
            throw DatabaseError.queryFailed("getDatabaseStats")
        }
    }
    
    /// Vacuum database to reclaim space
    func vacuumDatabase() async throws {
        guard let db = db else {
            throw DatabaseError.connectionNotAvailable
        }
        
        do {
            print("🧹 [DatabaseManager] Vacuuming database...")
            try db.execute("VACUUM")
            print("✅ [DatabaseManager] Database vacuumed successfully")
        } catch {
            print("❌ [DatabaseManager] Failed to vacuum database: \(error)")
            throw DatabaseError.queryFailed("vacuumDatabase")
        }
    }
    
    // MARK: - TokenModel Integration Methods
    
    /// Store complete TokenModel with automatic JWT parsing and validation
    func storeTokenModel(_ tokenModel: TokenModel) async throws {
        print("🔐 [DatabaseManager] Storing TokenModel - \(tokenModel.statusDescription)")
        
        let values = tokenModel.databaseValues
        try await storeTokens(
            accessTokenValue: values.accessToken,
            refreshTokenValue: values.refreshToken,
            clientIdValue: values.clientId,
            clientSecretValue: values.clientSecret
        )
        
        // Clear cache after storing
        await clearTokenCache()
        
        print("✅ [DatabaseManager] TokenModel stored successfully - \(tokenModel.expirationInfo)")
    }
    
    /// Retrieve complete TokenModel with automatic JWT parsing
    func getTokenModel() async throws -> TokenModel? {
        print("🔍 [DatabaseManager] Retrieving TokenModel from database")
        
        let accessToken = try await getAccessToken()
        let refreshToken = try await getRefreshToken()
        let credentials = try await getClientCredentials()
        
        guard let tokenModel = TokenModel(
            accessToken: accessToken,
            refreshToken: refreshToken,
            clientId: credentials.clientId,
            clientSecret: credentials.clientSecret
        ) else {
            print("⚠️ [DatabaseManager] No valid tokens found in database")
            return nil
        }
        
        print("✅ [DatabaseManager] TokenModel retrieved - \(tokenModel.statusDescription)")
        return tokenModel
    }
    
    /// Get valid TokenModel (returns nil if expired)
    func getValidTokenModel() async throws -> TokenModel? {
        guard let tokenModel = try await getTokenModel() else {
            print("⚠️ [DatabaseManager] No tokens found in database")
            return nil
        }
        
        if tokenModel.isExpired {
            print("🔴 [DatabaseManager] Stored token is expired - \(tokenModel.expirationInfo)")
            return nil
        }
        
        if tokenModel.shouldRefresh {
            print("🟡 [DatabaseManager] Stored token should be refreshed - \(tokenModel.expirationInfo)")
        } else {
            print