// PulseUpdates Database Schema and Initialization
// Based on expo-updates UpdatesDatabaseInitialization

import Foundation
import SQLite3

// MARK: - Schema Definition

struct PulseDatabaseSchema {
    static let version = 1
    static let filename = "pulse-v\(version).db"

    /// Current database schema
    static let schema = """
        -- Updates table: stores update metadata
        CREATE TABLE IF NOT EXISTS updates (
            update_id TEXT PRIMARY KEY NOT NULL,
            scope_key TEXT NOT NULL,
            runtime_version TEXT NOT NULL,
            commit_time INTEGER NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending',
            keep INTEGER NOT NULL DEFAULT 1,
            manifest TEXT,
            bundle_hash TEXT,
            last_accessed INTEGER,
            successful_launch_count INTEGER NOT NULL DEFAULT 0,
            failed_launch_count INTEGER NOT NULL DEFAULT 0
        );

        -- Assets table: stores asset metadata
        CREATE TABLE IF NOT EXISTS assets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_key TEXT,
            asset_hash TEXT NOT NULL,
            url TEXT,
            type TEXT,
            metadata TEXT,
            download_time INTEGER,
            relative_path TEXT,
            expected_hash TEXT,
            extra_request_headers TEXT,
            marked_for_deletion INTEGER NOT NULL DEFAULT 0,
            -- Embedded asset info (for fallback)
            ns_bundle_dir TEXT,
            ns_bundle_filename TEXT
        );

        -- Junction table: links updates to assets
        CREATE TABLE IF NOT EXISTS update_assets (
            update_id TEXT NOT NULL,
            asset_id INTEGER NOT NULL,
            asset_key TEXT,
            asset_hash TEXT NOT NULL,
            is_launch_asset INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY (update_id) REFERENCES updates(update_id) ON DELETE CASCADE,
            FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
            PRIMARY KEY (update_id, asset_id)
        );

        -- Health table: tracks update launch health
        CREATE TABLE IF NOT EXISTS health (
            update_id TEXT PRIMARY KEY NOT NULL,
            launch_in_progress_at INTEGER,
            app_ready_at INTEGER,
            last_launch_at INTEGER,
            successful_launch_count INTEGER NOT NULL DEFAULT 0,
            failed_launch_count INTEGER NOT NULL DEFAULT 0,
            consecutive_failures INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY (update_id) REFERENCES updates(update_id) ON DELETE CASCADE
        );

        -- JSON data table: stores manifest filters and other metadata
        CREATE TABLE IF NOT EXISTS json_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            scope_key TEXT NOT NULL,
            key TEXT NOT NULL,
            value TEXT NOT NULL,
            last_updated INTEGER NOT NULL,
            UNIQUE(scope_key, key)
        );

        -- Indexes for performance
        CREATE INDEX IF NOT EXISTS idx_updates_scope_runtime ON updates(scope_key, runtime_version);
        CREATE INDEX IF NOT EXISTS idx_updates_commit_time ON updates(commit_time DESC);
        CREATE INDEX IF NOT EXISTS idx_assets_hash ON assets(asset_hash);
        CREATE INDEX IF NOT EXISTS idx_update_assets_update ON update_assets(update_id);
        CREATE INDEX IF NOT EXISTS idx_update_assets_hash ON update_assets(asset_hash);
        CREATE INDEX IF NOT EXISTS idx_json_data_scope ON json_data(scope_key);
    """
}

// MARK: - Database Initialization

final class PulseDatabaseInitialization {

    /// Initialize or open the database with the latest schema
    static func initializeDatabase(inDirectory directory: URL) throws -> OpaquePointer {
        let dbPath = directory.appendingPathComponent(PulseDatabaseSchema.filename)
        let isNewDatabase = !FileManager.default.fileExists(atPath: dbPath.path)

        // Try to migrate from older versions if needed
        if !isNewDatabase {
            // Database exists, no migration needed for v1
        } else {
            // Check for legacy database and migrate if needed
            migrateLegacyDatabaseIfNeeded(inDirectory: directory, toPath: dbPath)
        }

        // Open database
        var db: OpaquePointer?
        let result = sqlite3_open(dbPath.path, &db)

        guard result == SQLITE_OK, let database = db else {
            let error = db != nil ? String(cString: sqlite3_errmsg(db)) : "Unknown error"
            sqlite3_close(db)

            // If database is corrupt, try to recover
            if result == SQLITE_CORRUPT || result == SQLITE_NOTADB {
                try handleCorruptDatabase(at: dbPath, inDirectory: directory)
                return try initializeDatabase(inDirectory: directory)
            }

            throw PulseDatabaseError.openFailed(code: result, message: error)
        }

        // Enable foreign keys
        sqlite3_exec(database, "PRAGMA foreign_keys=ON;", nil, nil, nil)

        // Initialize schema if new database
        if isNewDatabase || !hasRequiredTables(db: database) {
            try initializeSchema(db: database)
        }

        return database
    }

    /// Check if database has all required tables
    private static func hasRequiredTables(db: OpaquePointer) -> Bool {
        let tables = ["updates", "assets", "update_assets", "health", "json_data"]

        for table in tables {
            let sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
            do {
                let rows = try PulseDatabaseUtils.execute(sql: sql, args: [table], db: db)
                if rows.isEmpty {
                    return false
                }
            } catch {
                return false
            }
        }

        return true
    }

    /// Initialize database schema
    private static func initializeSchema(db: OpaquePointer) throws {
        guard sqlite3_exec(db, PulseDatabaseSchema.schema, nil, nil, nil) == SQLITE_OK else {
            throw PulseDatabaseError.schemaInitFailed
        }
    }

    /// Handle corrupt database by archiving and creating new
    private static func handleCorruptDatabase(at dbPath: URL, inDirectory directory: URL) throws {
        let archiveName = String(format: "corrupt-%.0f-%@", Date().timeIntervalSince1970, PulseDatabaseSchema.filename)
        let archivePath = directory.appendingPathComponent(archiveName)

        do {
            try FileManager.default.moveItem(at: dbPath, to: archivePath)
            print("[PulseUpdates] Archived corrupt database to \(archiveName)")
        } catch {
            // If we can't move, try to delete
            try? FileManager.default.removeItem(at: dbPath)
        }
    }

    /// Migrate from legacy pulse.db if exists
    private static func migrateLegacyDatabaseIfNeeded(inDirectory directory: URL, toPath newPath: URL) {
        let legacyPath = directory.appendingPathComponent("pulse.db")

        if FileManager.default.fileExists(atPath: legacyPath.path) {
            do {
                // Open legacy database
                var legacyDb: OpaquePointer?
                guard sqlite3_open(legacyPath.path, &legacyDb) == SQLITE_OK else {
                    return
                }
                defer { sqlite3_close(legacyDb) }

                // Copy to new location
                try FileManager.default.copyItem(at: legacyPath, to: newPath)

                // Archive legacy database
                let archiveName = "legacy-pulse.db"
                let archivePath = directory.appendingPathComponent(archiveName)
                try? FileManager.default.moveItem(at: legacyPath, to: archivePath)

                print("[PulseUpdates] Migrated legacy database")
            } catch {
                print("[PulseUpdates] Failed to migrate legacy database: \(error)")
            }
        }
    }
}

// MARK: - Database Migrations

/// Protocol for database migrations
protocol PulseDatabaseMigration {
    var fromVersion: Int { get }
    var toVersion: Int { get }
    func run(db: OpaquePointer) throws
}

/// Migration registry
final class PulseDatabaseMigrationRegistry {
    static let migrations: [PulseDatabaseMigration] = [
        // Add migrations here as needed
        // PulseMigration1To2(),
    ]

    static func migrate(db: OpaquePointer, fromVersion: Int, toVersion: Int) throws {
        for migration in migrations {
            if migration.fromVersion >= fromVersion && migration.toVersion <= toVersion {
                try migration.run(db: db)
            }
        }
    }
}