// PulseUpdates Database
// Based on expo-updates UpdatesDatabase

import Foundation
import SQLite3

// MARK: - Database Models

/// Update model for database operations
struct PulseUpdate {
    let updateId: String
    let scopeKey: String
    let runtimeVersion: String
    let commitTime: Date
    var status: PulseUpdateStatus
    var manifest: [String: Any]?
    var bundleHash: String?
    var lastAccessed: Date?
    var successfulLaunchCount: Int
    var failedLaunchCount: Int

    var isEmbedded: Bool {
        return status == .embedded || updateId.hasPrefix("embedded:")
    }
}

/// Asset model for database operations
struct PulseAsset {
    var id: Int64?
    let key: String?
    let hash: String
    var url: URL?
    var type: String?
    var metadata: [String: Any]?
    var downloadTime: Date?
    var relativePath: String?
    var expectedHash: String?
    var extraRequestHeaders: [String: String]?
    var markedForDeletion: Bool
    // Embedded asset info
    var nsBundleDir: String?
    var nsBundleFilename: String?
    var isLaunchAsset: Bool

    init(key: String?, hash: String) {
        self.key = key
        self.hash = hash
        self.markedForDeletion = false
        self.isLaunchAsset = false
    }
}

// MARK: - Main Database Class

final class PulseDatabase {
    private var db: OpaquePointer?
    private let directory: URL

    /// Serial queue for database operations
    let queue = DispatchQueue(label: "app.pulse.database")

    init(directory: URL) {
        self.directory = directory
    }

    deinit {
        close()
    }

    // MARK: - Connection Management

    func open() throws {
        try queue.sync {
            guard db == nil else { return }
            db = try PulseDatabaseInitialization.initializeDatabase(inDirectory: directory)
        }
    }

    func close() {
        queue.sync {
            if let db = db {
                sqlite3_close(db)
            }
            db = nil
        }
    }

    // MARK: - Updates Operations

    /// Add a new update to the database
    func addUpdate(_ update: PulseUpdate) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                INSERT INTO updates (update_id, scope_key, runtime_version, commit_time, status, manifest, bundle_hash, last_accessed, successful_launch_count, failed_launch_count)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """

            var manifestJson: String? = nil
            if let manifest = update.manifest,
               let data = try? JSONSerialization.data(withJSONObject: manifest),
               let str = String(data: data, encoding: .utf8) {
                manifestJson = str
            }

            try PulseDatabaseUtils.executeUpdate(
                sql: sql,
                args: [
                    update.updateId,
                    update.scopeKey,
                    update.runtimeVersion,
                    update.commitTime,
                    update.status.rawValue,
                    manifestJson,
                    update.bundleHash,
                    update.lastAccessed,
                    update.successfulLaunchCount,
                    update.failedLaunchCount
                ],
                db: db
            )
        }
    }

    /// Get all updates
    func allUpdates() throws -> [PulseUpdate] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                SELECT u.*,
                       COALESCE(h.successful_launch_count, u.successful_launch_count) as health_success,
                       COALESCE(h.failed_launch_count, u.failed_launch_count) as health_fail
                FROM updates u
                LEFT JOIN health h ON u.update_id = h.update_id
                ORDER BY u.commit_time DESC
            """

            let rows = try PulseDatabaseUtils.execute(sql: sql, db: db)
            return rows.compactMap { rowToUpdate($0) }
        }
    }

    /// Get updates for a specific runtime version
    func updates(forRuntimeVersion runtimeVersion: String) throws -> [PulseUpdate] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                SELECT u.*,
                       COALESCE(h.successful_launch_count, u.successful_launch_count) as health_success,
                       COALESCE(h.failed_launch_count, u.failed_launch_count) as health_fail
                FROM updates u
                LEFT JOIN health h ON u.update_id = h.update_id
                WHERE u.runtime_version = ?
                ORDER BY u.commit_time DESC
            """

            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [runtimeVersion], db: db)
            return rows.compactMap { rowToUpdate($0) }
        }
    }

    /// Get launchable updates (ready or embedded status)
    func launchableUpdates(forRuntimeVersion runtimeVersion: String) throws -> [PulseUpdate] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                SELECT u.*,
                       COALESCE(h.successful_launch_count, u.successful_launch_count) as health_success,
                       COALESCE(h.failed_launch_count, u.failed_launch_count) as health_fail
                FROM updates u
                LEFT JOIN health h ON u.update_id = h.update_id
                WHERE u.runtime_version = ? AND u.status IN ('ready', 'embedded')
                ORDER BY u.commit_time DESC
            """

            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [runtimeVersion], db: db)
            return rows.compactMap { rowToUpdate($0) }
        }
    }

    /// Get embedded updates
    func embeddedUpdates() throws -> [PulseUpdate] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                SELECT * FROM updates WHERE update_id LIKE 'embedded:%'
            """

            let rows = try PulseDatabaseUtils.execute(sql: sql, db: db)
            return rows.compactMap { rowToUpdate($0) }
        }
    }

    /// Get update by ID
    func update(withId updateId: String) throws -> PulseUpdate? {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "SELECT * FROM updates WHERE update_id = ?"
            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [updateId], db: db)
            return rows.first.flatMap { rowToUpdate($0) }
        }
    }

    /// Update status of an update
    func setStatus(_ status: PulseUpdateStatus, forUpdateId updateId: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "UPDATE updates SET status = ? WHERE update_id = ?"
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [status.rawValue, updateId], db: db)
        }
    }

    /// Mark update as accessed
    func markUpdateAccessed(_ updateId: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "UPDATE updates SET last_accessed = ? WHERE update_id = ?"
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [Date(), updateId], db: db)
        }
    }

    /// Delete updates
    func deleteUpdates(_ updateIds: [String]) throws {
        guard !updateIds.isEmpty else { return }

        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let placeholders = updateIds.map { _ in "?" }.joined(separator: ",")
            let sql = "DELETE FROM updates WHERE update_id IN (\(placeholders))"
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: updateIds, db: db)
        }
    }

    // MARK: - Assets Operations

    /// Add an asset to the database
    func addAsset(_ asset: PulseAsset) throws -> Int64 {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                INSERT INTO assets (asset_key, asset_hash, url, type, metadata, download_time, relative_path, expected_hash, extra_request_headers, marked_for_deletion, ns_bundle_dir, ns_bundle_filename)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """

            var metadataJson: String? = nil
            if let metadata = asset.metadata,
               let data = try? JSONSerialization.data(withJSONObject: metadata),
               let str = String(data: data, encoding: .utf8) {
                metadataJson = str
            }

            var headersJson: String? = nil
            if let headers = asset.extraRequestHeaders,
               let data = try? JSONSerialization.data(withJSONObject: headers),
               let str = String(data: data, encoding: .utf8) {
                headersJson = str
            }

            try PulseDatabaseUtils.executeUpdate(
                sql: sql,
                args: [
                    asset.key,
                    asset.hash,
                    asset.url?.absoluteString,
                    asset.type,
                    metadataJson,
                    asset.downloadTime,
                    asset.relativePath,
                    asset.expectedHash,
                    headersJson,
                    asset.markedForDeletion ? 1 : 0,
                    asset.nsBundleDir,
                    asset.nsBundleFilename
                ],
                db: db
            )

            return sqlite3_last_insert_rowid(db)
        }
    }

    /// Link asset to update
    func linkAsset(assetId: Int64, toUpdate updateId: String, assetKey: String?, assetHash: String, isLaunchAsset: Bool) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                INSERT OR REPLACE INTO update_assets (update_id, asset_id, asset_key, asset_hash, is_launch_asset)
                VALUES (?, ?, ?, ?, ?)
            """

            try PulseDatabaseUtils.executeUpdate(
                sql: sql,
                args: [updateId, assetId, assetKey, assetHash, isLaunchAsset ? 1 : 0],
                db: db
            )
        }
    }

    /// Get assets for an update
    func assets(forUpdateId updateId: String) throws -> [PulseAsset] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            // Explicit column selection to avoid conflicts with update_assets.asset_hash
            let sql = """
                SELECT a.id, a.asset_key, a.asset_hash, a.url, a.type, a.metadata,
                       a.download_time, a.relative_path, a.expected_hash,
                       a.extra_request_headers, a.marked_for_deletion,
                       a.ns_bundle_dir, a.ns_bundle_filename,
                       ua.asset_key as link_key, ua.is_launch_asset
                FROM assets a
                JOIN update_assets ua ON a.id = ua.asset_id
                WHERE ua.update_id = ?
            """

            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [updateId], db: db)
            return rows.compactMap { rowToAsset($0) }
        }
    }

    /// Get asset by hash
    func asset(withHash hash: String) throws -> PulseAsset? {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "SELECT * FROM assets WHERE asset_hash = ? LIMIT 1"
            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [hash], db: db)
            return rows.first.flatMap { rowToAsset($0) }
        }
    }

    /// Delete unused assets (not linked to any update)
    func deleteUnusedAssets() throws -> [PulseAsset] {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            // Find orphaned assets
            let findSql = """
                SELECT * FROM assets
                WHERE id NOT IN (SELECT DISTINCT asset_id FROM update_assets)
            """
            let rows = try PulseDatabaseUtils.execute(sql: findSql, db: db)
            let orphanedAssets = rows.compactMap { rowToAsset($0) }

            // Delete them
            let deleteSql = """
                DELETE FROM assets
                WHERE id NOT IN (SELECT DISTINCT asset_id FROM update_assets)
            """
            try PulseDatabaseUtils.executeUpdate(sql: deleteSql, db: db)

            // Also clean up orphaned links
            let cleanupSql = """
                DELETE FROM update_assets
                WHERE update_id NOT IN (SELECT update_id FROM updates)
            """
            try PulseDatabaseUtils.executeUpdate(sql: cleanupSql, db: db)

            return orphanedAssets
        }
    }

    // MARK: - Health Operations

    /// Record launch start
    func recordLaunchStart(updateId: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                INSERT INTO health (update_id, launch_in_progress_at, consecutive_failures)
                VALUES (?, ?, 0)
                ON CONFLICT(update_id) DO UPDATE SET
                    launch_in_progress_at = excluded.launch_in_progress_at,
                    app_ready_at = NULL
            """
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [updateId, Date()], db: db)
        }
    }

    /// Record successful launch
    func recordSuccessfulLaunch(updateId: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                UPDATE health SET
                    app_ready_at = ?,
                    last_launch_at = ?,
                    successful_launch_count = successful_launch_count + 1,
                    consecutive_failures = 0
                WHERE update_id = ?
            """
            let now = Date()
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [now, now, updateId], db: db)
        }
    }

    /// Record failed launch
    func recordFailedLaunch(updateId: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = """
                UPDATE health SET
                    failed_launch_count = failed_launch_count + 1,
                    consecutive_failures = consecutive_failures + 1,
                    launch_in_progress_at = NULL
                WHERE update_id = ?
            """
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [updateId], db: db)
        }
    }

    /// Get health info for update
    func healthInfo(forUpdateId updateId: String) throws -> (consecutiveFailures: Int, isLaunching: Bool)? {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "SELECT consecutive_failures, launch_in_progress_at FROM health WHERE update_id = ?"
            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [updateId], db: db)

            guard let row = rows.first else { return nil }

            let failures = (row["consecutive_failures"] as? Int64) ?? 0
            let isLaunching = row["launch_in_progress_at"] != nil

            return (Int(failures), isLaunching)
        }
    }

    // MARK: - JSON Data Operations

    /// Set JSON data
    func setJsonData(key: String, value: [String: Any], scopeKey: String) throws {
        try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            guard let data = try? JSONSerialization.data(withJSONObject: value),
                  let jsonString = String(data: data, encoding: .utf8) else {
                return
            }

            let sql = """
                INSERT INTO json_data (scope_key, key, value, last_updated)
                VALUES (?, ?, ?, ?)
                ON CONFLICT(scope_key, key) DO UPDATE SET
                    value = excluded.value,
                    last_updated = excluded.last_updated
            """
            try PulseDatabaseUtils.executeUpdate(sql: sql, args: [scopeKey, key, jsonString, Date()], db: db)
        }
    }

    /// Get JSON data
    func jsonData(key: String, scopeKey: String) throws -> [String: Any]? {
        return try queue.sync {
            guard let db = db else { throw PulseDatabaseError.openFailed(code: 0, message: "Database not open") }

            let sql = "SELECT value FROM json_data WHERE scope_key = ? AND key = ?"
            let rows = try PulseDatabaseUtils.execute(sql: sql, args: [scopeKey, key], db: db)

            guard let row = rows.first,
                  let jsonString = row["value"] as? String,
                  let data = jsonString.data(using: .utf8),
                  let json = try? JSONSerialization.jsonObject(with: data) as? [String: Any] else {
                return nil
            }

            return json
        }
    }

    // MARK: - Row Conversion Helpers

    private func rowToUpdate(_ row: [String: Any?]) -> PulseUpdate? {
        guard let updateId = row["update_id"] as? String,
              let scopeKey = row["scope_key"] as? String,
              let runtimeVersion = row["runtime_version"] as? String,
              let commitTimeMs = row["commit_time"] as? Int64,
              let statusStr = row["status"] as? String else {
            return nil
        }

        var manifest: [String: Any]? = nil
        if let manifestStr = row["manifest"] as? String,
           let data = manifestStr.data(using: .utf8),
           let json = try? JSONSerialization.jsonObject(with: data) as? [String: Any] {
            manifest = json
        }

        let successCount = (row["health_success"] as? Int64) ?? (row["successful_launch_count"] as? Int64) ?? 0
        let failCount = (row["health_fail"] as? Int64) ?? (row["failed_launch_count"] as? Int64) ?? 0

        return PulseUpdate(
            updateId: updateId,
            scopeKey: scopeKey,
            runtimeVersion: runtimeVersion,
            commitTime: PulseDatabaseUtils.dateFromMillis(commitTimeMs),
            status: PulseUpdateStatus(rawValue: statusStr) ?? .pending,
            manifest: manifest,
            bundleHash: row["bundle_hash"] as? String,
            lastAccessed: (row["last_accessed"] as? Int64).map { PulseDatabaseUtils.dateFromMillis($0) },
            successfulLaunchCount: Int(successCount),
            failedLaunchCount: Int(failCount)
        )
    }

    private func rowToAsset(_ row: [String: Any?]) -> PulseAsset? {
        guard let hash = row["asset_hash"] as? String else {
            return nil
        }

        var asset = PulseAsset(key: row["asset_key"] as? String ?? row["link_key"] as? String, hash: hash)
        asset.id = row["id"] as? Int64
        asset.type = row["type"] as? String
        asset.relativePath = row["relative_path"] as? String
        asset.expectedHash = row["expected_hash"] as? String
        asset.nsBundleDir = row["ns_bundle_dir"] as? String
        asset.nsBundleFilename = row["ns_bundle_filename"] as? String
        asset.markedForDeletion = (row["marked_for_deletion"] as? Int64) == 1
        asset.isLaunchAsset = (row["is_launch_asset"] as? Int64) == 1

        if let urlStr = row["url"] as? String {
            asset.url = URL(string: urlStr)
        }

        if let downloadTimeMs = row["download_time"] as? Int64 {
            asset.downloadTime = PulseDatabaseUtils.dateFromMillis(downloadTimeMs)
        }

        if let metadataStr = row["metadata"] as? String,
           let data = metadataStr.data(using: .utf8),
           let json = try? JSONSerialization.jsonObject(with: data) as? [String: Any] {
            asset.metadata = json
        }

        if let headersStr = row["extra_request_headers"] as? String,
           let data = headersStr.data(using: .utf8),
           let json = try? JSONSerialization.jsonObject(with: data) as? [String: String] {
            asset.extraRequestHeaders = json
        }

        return asset
    }
}