// PulseUpdates Database Utilities
// Based on expo-updates UpdatesDatabaseUtils

import Foundation
import SQLite3

// MARK: - Error Types

enum PulseDatabaseError: Error, LocalizedError {
    case openFailed(code: Int32, message: String)
    case prepareFailed(code: Int32, message: String)
    case executeFailed(code: Int32, message: String)
    case bindFailed(code: Int32, message: String)
    case migrationFailed(message: String)
    case corruptDatabase
    case schemaInitFailed

    var errorDescription: String? {
        switch self {
        case .openFailed(let code, let message):
            return "Failed to open database (code \(code)): \(message)"
        case .prepareFailed(let code, let message):
            return "Failed to prepare statement (code \(code)): \(message)"
        case .executeFailed(let code, let message):
            return "Failed to execute statement (code \(code)): \(message)"
        case .bindFailed(let code, let message):
            return "Failed to bind parameter (code \(code)): \(message)"
        case .migrationFailed(let message):
            return "Migration failed: \(message)"
        case .corruptDatabase:
            return "Database is corrupt"
        case .schemaInitFailed:
            return "Failed to initialize database schema"
        }
    }
}

// MARK: - SQLite Constants

let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

// MARK: - Database Utils

final class PulseDatabaseUtils {

    /// Execute a SQL query with optional arguments and return results
    static func execute(
        sql: String,
        args: [Any?]? = nil,
        db: OpaquePointer
    ) throws -> [[String: Any?]] {
        var stmt: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else {
            throw PulseDatabaseError.prepareFailed(
                code: sqlite3_errcode(db),
                message: String(cString: sqlite3_errmsg(db))
            )
        }

        defer { sqlite3_finalize(stmt) }

        // Bind arguments if provided
        if let args = args {
            try bindArgs(args, to: stmt!, db: db)
        }

        // Execute and collect results
        var rows: [[String: Any?]] = []
        var columnNames: [String]?

        while true {
            let result = sqlite3_step(stmt)

            switch result {
            case SQLITE_ROW:
                // Fetch column names on first row
                if columnNames == nil {
                    columnNames = getColumnNames(stmt!)
                }

                let row = getRowValues(stmt!, columnNames: columnNames!)
                rows.append(row)

            case SQLITE_DONE:
                return rows

            default:
                throw PulseDatabaseError.executeFailed(
                    code: sqlite3_errcode(db),
                    message: String(cString: sqlite3_errmsg(db))
                )
            }
        }
    }

    /// Execute a SQL statement without returning results
    static func executeUpdate(
        sql: String,
        args: [Any?]? = nil,
        db: OpaquePointer
    ) throws {
        _ = try execute(sql: sql, args: args, db: db)
    }

    /// Execute raw SQL (for schema creation)
    static func executeRaw(sql: String, db: OpaquePointer) throws {
        guard sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK else {
            throw PulseDatabaseError.executeFailed(
                code: sqlite3_errcode(db),
                message: String(cString: sqlite3_errmsg(db))
            )
        }
    }

    // MARK: - Private Helpers

    private static func bindArgs(_ args: [Any?], to stmt: OpaquePointer, db: OpaquePointer) throws {
        for (index, arg) in args.enumerated() {
            let bindIdx = Int32(index + 1)
            var result: Int32 = SQLITE_OK

            switch arg {
            case nil:
                result = sqlite3_bind_null(stmt, bindIdx)

            case let value as Int:
                result = sqlite3_bind_int64(stmt, bindIdx, Int64(value))

            case let value as Int64:
                result = sqlite3_bind_int64(stmt, bindIdx, value)

            case let value as Double:
                result = sqlite3_bind_double(stmt, bindIdx, value)

            case let value as String:
                result = sqlite3_bind_text(stmt, bindIdx, value, -1, SQLITE_TRANSIENT)

            case let value as Data:
                result = value.withUnsafeBytes { ptr in
                    sqlite3_bind_blob(stmt, bindIdx, ptr.baseAddress, Int32(value.count), SQLITE_TRANSIENT)
                }

            case let value as Date:
                let timestamp = Int64(value.timeIntervalSince1970 * 1000)
                result = sqlite3_bind_int64(stmt, bindIdx, timestamp)

            case let value as Bool:
                result = sqlite3_bind_int(stmt, bindIdx, value ? 1 : 0)

            case let value as [String: Any]:
                if let jsonData = try? JSONSerialization.data(withJSONObject: value),
                   let jsonString = String(data: jsonData, encoding: .utf8) {
                    result = sqlite3_bind_text(stmt, bindIdx, jsonString, -1, SQLITE_TRANSIENT)
                } else {
                    result = sqlite3_bind_null(stmt, bindIdx)
                }

            default:
                // Try to convert to string
                let stringValue = String(describing: arg!)
                result = sqlite3_bind_text(stmt, bindIdx, stringValue, -1, SQLITE_TRANSIENT)
            }

            guard result == SQLITE_OK else {
                throw PulseDatabaseError.bindFailed(
                    code: sqlite3_errcode(db),
                    message: String(cString: sqlite3_errmsg(db))
                )
            }
        }
    }

    private static func getColumnNames(_ stmt: OpaquePointer) -> [String] {
        let count = sqlite3_column_count(stmt)
        var names: [String] = []

        for i in 0..<count {
            if let name = sqlite3_column_name(stmt, i) {
                names.append(String(cString: name))
            } else {
                names.append("column_\(i)")
            }
        }

        return names
    }

    private static func getRowValues(_ stmt: OpaquePointer, columnNames: [String]) -> [String: Any?] {
        var row: [String: Any?] = [:]

        for (i, name) in columnNames.enumerated() {
            let col = Int32(i)
            let type = sqlite3_column_type(stmt, col)

            switch type {
            case SQLITE_INTEGER:
                row[name] = sqlite3_column_int64(stmt, col)

            case SQLITE_FLOAT:
                row[name] = sqlite3_column_double(stmt, col)

            case SQLITE_TEXT:
                if let text = sqlite3_column_text(stmt, col) {
                    row[name] = String(cString: text)
                } else {
                    row[name] = nil
                }

            case SQLITE_BLOB:
                let bytes = sqlite3_column_bytes(stmt, col)
                if bytes > 0, let blob = sqlite3_column_blob(stmt, col) {
                    row[name] = Data(bytes: blob, count: Int(bytes))
                } else {
                    row[name] = nil
                }

            case SQLITE_NULL:
                row[name] = nil

            default:
                row[name] = nil
            }
        }

        return row
    }

    /// Get error info from database
    static func errorInfo(db: OpaquePointer?) -> (code: Int32, message: String) {
        guard let db = db else {
            return (0, "No database connection")
        }
        return (sqlite3_errcode(db), String(cString: sqlite3_errmsg(db)))
    }

    /// Convert milliseconds timestamp to Date
    static func dateFromMillis(_ millis: Int64) -> Date {
        return Date(timeIntervalSince1970: Double(millis) / 1000.0)
    }

    /// Convert Date to milliseconds timestamp
    static func millisFromDate(_ date: Date) -> Int64 {
        return Int64(date.timeIntervalSince1970 * 1000)
    }
}