package app.pulse.updates.database

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import org.json.JSONObject
import java.io.File
import java.util.Date

/**
 * PulseUpdates Database
 * Based on expo-updates UpdatesDatabase
 */
class PulseDatabase(context: Context, directory: File) : SQLiteOpenHelper(
    context,
    File(directory, PulseDatabaseSchema.DATABASE_NAME).absolutePath,
    null,
    PulseDatabaseSchema.VERSION
) {

    override fun onCreate(db: SQLiteDatabase) {
        // Create tables
        PulseDatabaseSchema.CREATE_TABLES.split(";")
            .filter { it.isNotBlank() }
            .forEach { sql ->
                db.execSQL(sql.trim() + ";")
            }

        // Create indexes
        PulseDatabaseSchema.CREATE_INDEXES.split(";")
            .filter { it.isNotBlank() }
            .forEach { sql ->
                db.execSQL(sql.trim() + ";")
            }

        // Enable foreign keys
        db.execSQL("PRAGMA foreign_keys=ON;")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Handle migrations here
    }

    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        db.execSQL("PRAGMA foreign_keys=ON;")
    }

    // MARK: - Updates Operations

    fun addUpdate(update: PulseUpdate) {
        val values = ContentValues().apply {
            put("update_id", update.updateId)
            put("scope_key", update.scopeKey)
            put("runtime_version", update.runtimeVersion)
            put("commit_time", update.commitTime.time)
            put("status", update.status.value)
            put("manifest", update.manifest?.toString())
            put("bundle_hash", update.bundleHash)
            put("last_accessed", update.lastAccessed?.time)
            put("successful_launch_count", update.successfulLaunchCount)
            put("failed_launch_count", update.failedLaunchCount)
        }
        writableDatabase.insertWithOnConflict("updates", null, values, SQLiteDatabase.CONFLICT_REPLACE)
    }

    fun allUpdates(): List<PulseUpdate> {
        val 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
        """.trimIndent()

        return readableDatabase.rawQuery(sql, null).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToUpdate(cursor) else null }.toList()
        }
    }

    fun updatesForRuntimeVersion(runtimeVersion: String): List<PulseUpdate> {
        val 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
        """.trimIndent()

        return readableDatabase.rawQuery(sql, arrayOf(runtimeVersion)).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToUpdate(cursor) else null }.toList()
        }
    }

    fun launchableUpdates(runtimeVersion: String): List<PulseUpdate> {
        val 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
        """.trimIndent()

        return readableDatabase.rawQuery(sql, arrayOf(runtimeVersion)).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToUpdate(cursor) else null }.toList()
        }
    }

    fun embeddedUpdates(): List<PulseUpdate> {
        val sql = "SELECT * FROM updates WHERE update_id LIKE 'embedded:%'"
        return readableDatabase.rawQuery(sql, null).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToUpdate(cursor) else null }.toList()
        }
    }

    fun getUpdate(updateId: String): PulseUpdate? {
        val sql = "SELECT * FROM updates WHERE update_id = ?"
        return readableDatabase.rawQuery(sql, arrayOf(updateId)).use { cursor ->
            if (cursor.moveToFirst()) cursorToUpdate(cursor) else null
        }
    }

    fun setStatus(updateId: String, status: PulseUpdateStatus) {
        val values = ContentValues().apply {
            put("status", status.value)
        }
        writableDatabase.update("updates", values, "update_id = ?", arrayOf(updateId))
    }

    fun markUpdateAccessed(updateId: String) {
        val values = ContentValues().apply {
            put("last_accessed", System.currentTimeMillis())
        }
        writableDatabase.update("updates", values, "update_id = ?", arrayOf(updateId))
    }

    fun deleteUpdates(updateIds: List<String>) {
        if (updateIds.isEmpty()) return

        val placeholders = updateIds.joinToString(",") { "?" }
        writableDatabase.delete("updates", "update_id IN ($placeholders)", updateIds.toTypedArray())
    }

    // MARK: - Assets Operations

    fun addAsset(asset: PulseAsset): Long {
        val values = ContentValues().apply {
            put("asset_key", asset.key)
            put("asset_hash", asset.hash)
            put("url", asset.url)
            put("type", asset.type)
            put("metadata", asset.metadata?.toString())
            put("download_time", asset.downloadTime?.time)
            put("relative_path", asset.relativePath)
            put("expected_hash", asset.expectedHash)
            put("extra_request_headers", asset.extraRequestHeaders?.let { JSONObject(it).toString() })
            put("marked_for_deletion", if (asset.markedForDeletion) 1 else 0)
            put("main_bundle_dir", asset.mainBundleDir)
            put("main_bundle_filename", asset.mainBundleFilename)
        }
        return writableDatabase.insert("assets", null, values)
    }

    fun linkAsset(assetId: Long, updateId: String, assetKey: String?, assetHash: String, isLaunchAsset: Boolean) {
        val values = ContentValues().apply {
            put("update_id", updateId)
            put("asset_id", assetId)
            put("asset_key", assetKey)
            put("asset_hash", assetHash)
            put("is_launch_asset", if (isLaunchAsset) 1 else 0)
        }
        writableDatabase.insertWithOnConflict("update_assets", null, values, SQLiteDatabase.CONFLICT_REPLACE)
    }

    fun assetsForUpdate(updateId: String): List<PulseAsset> {
        val sql = """
            SELECT a.*, 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 = ?
        """.trimIndent()

        return readableDatabase.rawQuery(sql, arrayOf(updateId)).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToAsset(cursor) else null }.toList()
        }
    }

    fun getAssetByHash(hash: String): PulseAsset? {
        val sql = "SELECT * FROM assets WHERE asset_hash = ? LIMIT 1"
        return readableDatabase.rawQuery(sql, arrayOf(hash)).use { cursor ->
            if (cursor.moveToFirst()) cursorToAsset(cursor) else null
        }
    }

    fun deleteUnusedAssets(): List<PulseAsset> {
        // Find orphaned assets
        val findSql = """
            SELECT * FROM assets
            WHERE id NOT IN (SELECT DISTINCT asset_id FROM update_assets)
        """.trimIndent()

        val orphanedAssets = readableDatabase.rawQuery(findSql, null).use { cursor ->
            generateSequence { if (cursor.moveToNext()) cursorToAsset(cursor) else null }.toList()
        }

        // Delete them
        writableDatabase.execSQL("""
            DELETE FROM assets
            WHERE id NOT IN (SELECT DISTINCT asset_id FROM update_assets)
        """.trimIndent())

        // Clean up orphaned links
        writableDatabase.execSQL("""
            DELETE FROM update_assets
            WHERE update_id NOT IN (SELECT update_id FROM updates)
        """.trimIndent())

        return orphanedAssets
    }

    // MARK: - Health Operations

    fun recordLaunchStart(updateId: String) {
        val values = ContentValues().apply {
            put("update_id", updateId)
            put("launch_in_progress_at", System.currentTimeMillis())
            put("consecutive_failures", 0)
        }
        writableDatabase.insertWithOnConflict("health", null, values, SQLiteDatabase.CONFLICT_REPLACE)
    }

    fun recordSuccessfulLaunch(updateId: String) {
        val now = System.currentTimeMillis()
        // Upsert: on Android recordLaunchStart() is never called in the launch path, so the health
        // row may not exist yet. A bare UPDATE would be a silent no-op and successful_launch_count
        // would stay 0, which the error-recovery pipeline reads to decide whether to roll back.
        writableDatabase.execSQL(
            "INSERT OR IGNORE INTO health (update_id) VALUES (?)",
            arrayOf(updateId)
        )
        writableDatabase.execSQL("""
            UPDATE health SET
                app_ready_at = ?,
                last_launch_at = ?,
                successful_launch_count = successful_launch_count + 1,
                consecutive_failures = 0
            WHERE update_id = ?
        """.trimIndent(), arrayOf(now, now, updateId))
    }

    fun recordFailedLaunch(updateId: String) {
        // Upsert (see recordSuccessfulLaunch): the health row may not exist because recordLaunchStart
        // is not wired into the Android launch path. Without the INSERT this UPDATE is a silent
        // no-op and failed_launch_count never increments, defeating crash-rollback bookkeeping.
        writableDatabase.execSQL(
            "INSERT OR IGNORE INTO health (update_id) VALUES (?)",
            arrayOf(updateId)
        )
        writableDatabase.execSQL("""
            UPDATE health SET
                failed_launch_count = failed_launch_count + 1,
                consecutive_failures = consecutive_failures + 1,
                launch_in_progress_at = NULL
            WHERE update_id = ?
        """.trimIndent(), arrayOf(updateId))
    }

    fun getHealthInfo(updateId: String): Pair<Int, Boolean>? {
        val sql = "SELECT consecutive_failures, launch_in_progress_at FROM health WHERE update_id = ?"
        return readableDatabase.rawQuery(sql, arrayOf(updateId)).use { cursor ->
            if (cursor.moveToFirst()) {
                val failures = cursor.getInt(cursor.getColumnIndexOrThrow("consecutive_failures"))
                val isLaunching = !cursor.isNull(cursor.getColumnIndexOrThrow("launch_in_progress_at"))
                Pair(failures, isLaunching)
            } else null
        }
    }

    // MARK: - JSON Data Operations

    fun setJsonData(key: String, value: JSONObject, scopeKey: String) {
        val values = ContentValues().apply {
            put("scope_key", scopeKey)
            put("key", key)
            put("value", value.toString())
            put("last_updated", System.currentTimeMillis())
        }
        writableDatabase.insertWithOnConflict("json_data", null, values, SQLiteDatabase.CONFLICT_REPLACE)
    }

    fun getJsonData(key: String, scopeKey: String): JSONObject? {
        val sql = "SELECT value FROM json_data WHERE scope_key = ? AND key = ?"
        return readableDatabase.rawQuery(sql, arrayOf(scopeKey, key)).use { cursor ->
            if (cursor.moveToFirst()) {
                val jsonStr = cursor.getString(0)
                try { JSONObject(jsonStr) } catch (e: Exception) { null }
            } else null
        }
    }

    // MARK: - Cursor Conversion

    private fun cursorToUpdate(cursor: Cursor): PulseUpdate {
        return PulseUpdate(
            updateId = cursor.getString(cursor.getColumnIndexOrThrow("update_id")),
            scopeKey = cursor.getString(cursor.getColumnIndexOrThrow("scope_key")),
            runtimeVersion = cursor.getString(cursor.getColumnIndexOrThrow("runtime_version")),
            commitTime = Date(cursor.getLong(cursor.getColumnIndexOrThrow("commit_time"))),
            status = PulseUpdateStatus.fromValue(cursor.getString(cursor.getColumnIndexOrThrow("status"))),
            manifest = cursor.getStringOrNull("manifest")?.let { try { JSONObject(it) } catch (e: Exception) { null } },
            bundleHash = cursor.getStringOrNull("bundle_hash"),
            lastAccessed = cursor.getLongOrNull("last_accessed")?.let { Date(it) },
            successfulLaunchCount = cursor.getIntOrDefault("health_success", cursor.getIntOrDefault("successful_launch_count", 0)),
            failedLaunchCount = cursor.getIntOrDefault("health_fail", cursor.getIntOrDefault("failed_launch_count", 0))
        )
    }

    private fun cursorToAsset(cursor: Cursor): PulseAsset {
        return PulseAsset(
            id = cursor.getLongOrNull("id"),
            key = cursor.getStringOrNull("asset_key") ?: cursor.getStringOrNull("link_key"),
            hash = cursor.getString(cursor.getColumnIndexOrThrow("asset_hash")),
            url = cursor.getStringOrNull("url"),
            type = cursor.getStringOrNull("type"),
            metadata = cursor.getStringOrNull("metadata")?.let { try { JSONObject(it) } catch (e: Exception) { null } },
            downloadTime = cursor.getLongOrNull("download_time")?.let { Date(it) },
            relativePath = cursor.getStringOrNull("relative_path"),
            expectedHash = cursor.getStringOrNull("expected_hash"),
            extraRequestHeaders = cursor.getStringOrNull("extra_request_headers")?.let {
                try {
                    val json = JSONObject(it)
                    json.keys().asSequence().associateWith { key -> json.getString(key) }
                } catch (e: Exception) { null }
            },
            markedForDeletion = cursor.getIntOrDefault("marked_for_deletion", 0) == 1,
            mainBundleDir = cursor.getStringOrNull("main_bundle_dir"),
            mainBundleFilename = cursor.getStringOrNull("main_bundle_filename"),
            isLaunchAsset = cursor.getIntOrDefault("is_launch_asset", 0) == 1
        )
    }

    // Extension functions for nullable cursor columns
    private fun Cursor.getStringOrNull(column: String): String? {
        val idx = getColumnIndex(column)
        return if (idx >= 0 && !isNull(idx)) getString(idx) else null
    }

    private fun Cursor.getLongOrNull(column: String): Long? {
        val idx = getColumnIndex(column)
        return if (idx >= 0 && !isNull(idx)) getLong(idx) else null
    }

    private fun Cursor.getIntOrDefault(column: String, default: Int): Int {
        val idx = getColumnIndex(column)
        return if (idx >= 0 && !isNull(idx)) getInt(idx) else default
    }
}

// MARK: - Data Models

enum class PulseUpdateStatus(val value: String) {
    PENDING("pending"),
    DOWNLOADING("downloading"),
    READY("ready"),
    EMBEDDED("embedded"),
    DEVELOPMENT("development"),
    FAILED("failed");

    companion object {
        fun fromValue(value: String): PulseUpdateStatus {
            return entries.find { it.value == value } ?: PENDING
        }
    }
}

data class PulseUpdate(
    val updateId: String,
    val scopeKey: String,
    val runtimeVersion: String,
    val commitTime: Date,
    val status: PulseUpdateStatus,
    val manifest: JSONObject? = null,
    val bundleHash: String? = null,
    val lastAccessed: Date? = null,
    val successfulLaunchCount: Int = 0,
    val failedLaunchCount: Int = 0
) {
    val isEmbedded: Boolean
        get() = status == PulseUpdateStatus.EMBEDDED || updateId.startsWith("embedded:")
}

data class PulseAsset(
    val id: Long? = null,
    val key: String?,
    val hash: String,
    val url: String? = null,
    val type: String? = null,
    val metadata: JSONObject? = null,
    val downloadTime: Date? = null,
    val relativePath: String? = null,
    val expectedHash: String? = null,
    val extraRequestHeaders: Map<String, String>? = null,
    val markedForDeletion: Boolean = false,
    val mainBundleDir: String? = null,
    val mainBundleFilename: String? = null,
    val isLaunchAsset: Boolean = false
)