import Foundation
import GRDB

public struct DatabaseBootstrapService: Sendable {
    public let applicationSupportDirectoryURL: URL
    public let databaseURL: URL

    /// `applicationSupportRoot` is injectable for tests. When omitted it is
    /// the user's Application Support directory; the service always creates a
    /// dedicated `PromptGarage` child directory below it.
    public init(applicationSupportRoot: URL? = nil) throws {
        let fileManager = FileManager.default
        let root: URL
        if let applicationSupportRoot {
            root = applicationSupportRoot
        } else {
            root = try fileManager.url(
                for: .applicationSupportDirectory,
                in: .userDomainMask,
                appropriateFor: nil,
                create: true
            )
        }

        let directory = root.appendingPathComponent(
            "PromptGarage",
            isDirectory: true
        )
        try fileManager.createDirectory(
            at: directory,
            withIntermediateDirectories: true
        )

        applicationSupportDirectoryURL = directory
        databaseURL = directory.appendingPathComponent("prompts.db")
    }

    public func inspect() -> DatabaseBootstrapState {
        let fileManager = FileManager.default
        var isDirectory: ObjCBool = false
        guard fileManager.fileExists(
            atPath: databaseURL.path,
            isDirectory: &isDirectory
        ) else {
            return .needsDatabase
        }
        guard !isDirectory.boolValue else {
            return .failed("Am Datenbankpfad liegt ein Ordner statt einer Datei.")
        }
        guard fileManager.isReadableFile(atPath: databaseURL.path) else {
            return .failed("Die vorhandene PromptGarage-Datenbank ist nicht lesbar.")
        }

        do {
            if fileManager.isWritableFile(atPath: databaseURL.path) {
                // This is the app-owned destination. A structurally valid,
                // writable copy may receive the nullable migration and repair
                // a stale FTS index before it is reported ready.
                return .ready(try validateDatabaseWritable(at: databaseURL))
            }
            // A read-only app copy cannot be repaired. Full validation runs on
            // a transient SQLite backup so a stale FTS index is surfaced as
            // invalid instead of being opened as ready/read-only.
            return .readOnly(try validateDatabaseReadOnly(at: databaseURL))
        } catch let error as DatabaseValidationError {
            return .invalid(error)
        } catch {
            return .failed(error.localizedDescription)
        }
    }

    public func openExisting() throws -> PromptRepository? {
        switch inspect() {
        case .needsDatabase:
            return nil
        case .validating:
            return nil
        case .ready:
            return try PromptRepository(databaseURL: databaseURL)
        case .readOnly:
            return try PromptRepository(databaseURL: databaseURL, readOnly: true)
        case let .invalid(error):
            throw DatabaseBootstrapError.invalidExistingDatabase(error)
        case let .failed(message):
            throw DatabaseBootstrapError.operationFailed(message)
        }
    }

    /// Creates the native database when none exists. Existing data is never
    /// erased: a valid database is opened, and an invalid one is surfaced.
    public func startEmpty() throws -> PromptRepository {
        if let repository = try openExisting() {
            return repository
        }
        return try PromptRepository(databaseURL: databaseURL)
    }

    /// Validates a selected legacy or native database through a read-only
    /// connection. Legacy databases are allowed to omit `prompted_at`.
    public func validateSource(at sourceURL: URL) throws -> DatabaseValidationReport {
        try validateSourceLocation(sourceURL)
        return try validateDatabaseReadOnly(at: sourceURL)
    }

    /// Makes a consistent SQLite online backup into the application directory,
    /// migrates only that temporary copy, validates it, and atomically installs
    /// it. The selected source database is never opened for writing.
    public func installDatabase(from sourceURL: URL) throws -> PromptRepository {
        // Installing is a first-launch-only operation. Never use a selected
        // source as an automatic recovery/replacement for an app database: an
        // existing destination may contain the user's only current Mac data,
        // even when it is structurally invalid. Refuse before opening or
        // backing up the source so recovery remains an explicit filesystem
        // action owned by the user.
        try refuseInstallWhenDestinationExists()
        try validateSourceLocation(sourceURL)

        let fileManager = FileManager.default
        let temporaryURL = applicationSupportDirectoryURL.appendingPathComponent(
            ".prompts-import-\(UUID().uuidString).db"
        )
        defer { Self.removeDatabaseFiles(at: temporaryURL) }

        var sourceConfiguration = Configuration()
        sourceConfiguration.label = "PromptGarage.bootstrap.source"
        sourceConfiguration.readonly = true
        sourceConfiguration.busyMode = .timeout(5)

        let source = try DatabaseQueue(
            path: sourceURL.path,
            configuration: sourceConfiguration
        )
        defer { try? source.close() }

        var destinationConfiguration = Configuration()
        destinationConfiguration.label = "PromptGarage.bootstrap.temporary"
        destinationConfiguration.busyMode = .timeout(5)
        let temporary = try DatabaseQueue(
            path: temporaryURL.path,
            configuration: destinationConfiguration
        )
        defer { try? temporary.close() }

        // data_version changes on this connection whenever another connection
        // commits to the selected source. Holding it stable from validation
        // through the final pre-swap gate proves that our dynamic row counts
        // describe the same source snapshot that was backed up.
        let sourceVersion = try Self.dataVersion(of: source)
        let sourceReport = try DatabaseSchema.validateStructureAndCounts(source)
        try source.backup(to: temporary)

        guard try Self.dataVersion(of: source) == sourceVersion else {
            throw DatabaseBootstrapError.sourceChangedDuringCopy
        }

        // Validate the exact source snapshot before any native migration or
        // repair. This catches a stale external-content FTS index that plain
        // COUNT(*) parity cannot see, without ever writing to the source.
        let copiedSourceReport = try DatabaseSchema.validate(temporary)
        try Self.validateCopyParity(
            source: sourceReport,
            destination: copiedSourceReport
        )

        try temporary.write { db in
            // The source already passed strict legacy validation. Only the
            // app-owned copy receives the forward-only nullable column.
            try DatabaseSchema.migrateDestinationIfNeeded(in: db)
            try DatabaseSchema.repairSearchIndexIfNeeded(in: db)
        }
        let stagedReport = try DatabaseSchema.validate(temporary)
        try Self.validateCopyParity(
            source: sourceReport,
            destination: stagedReport
        )
        guard stagedReport.hasPromptedAt else {
            throw DatabaseBootstrapError.operationFailed(
                "Die App-Kopie konnte nicht auf prompted_at migriert werden."
            )
        }

        // Apply destination permissions before the atomic swap. If this fails,
        // the currently installed database remains untouched.
        try fileManager.setAttributes(
            [.posixPermissions: 0o600],
            ofItemAtPath: temporaryURL.path
        )

        // Reject a moving source instead of installing a copy whose displayed
        // source counts no longer match. The user can retry against the next
        // stable dynamic snapshot; no hard-coded row count is involved.
        guard try Self.dataVersion(of: source) == sourceVersion else {
            throw DatabaseBootstrapError.sourceChangedDuringCopy
        }

        // Close both handles before the rename so that neither SQLite nor
        // Foundation keeps a file descriptor to the staging path.
        try temporary.close()
        try source.close()

        do {
            // Re-check immediately before the move to close the race between
            // first-launch validation and installation. `moveItem` is the
            // only allowed install primitive; there is intentionally no
            // replacement path.
            try refuseInstallWhenDestinationExists()
            try fileManager.moveItem(at: temporaryURL, to: databaseURL)
            Self.removeSQLiteSidecars(for: databaseURL)
        } catch let error as DatabaseBootstrapError {
            throw error
        } catch {
            throw DatabaseBootstrapError.couldNotInstallDestination(
                error.localizedDescription
            )
        }

        do {
            return try PromptRepository(databaseURL: databaseURL)
        } catch {
            throw DatabaseBootstrapError.installedDatabaseInvalid(
                error.localizedDescription
            )
        }
    }

    private func validateSourceLocation(_ sourceURL: URL) throws {
        let fileManager = FileManager.default
        var isDirectory: ObjCBool = false
        guard fileManager.fileExists(
            atPath: sourceURL.path,
            isDirectory: &isDirectory
        ) else {
            throw DatabaseBootstrapError.sourceMissing(sourceURL)
        }
        guard !isDirectory.boolValue else {
            throw DatabaseBootstrapError.sourceIsDirectory(sourceURL)
        }
        guard fileManager.isReadableFile(atPath: sourceURL.path) else {
            throw DatabaseBootstrapError.sourceUnreadable(sourceURL)
        }

        let source = sourceURL.standardizedFileURL.resolvingSymlinksInPath()
        let destination = databaseURL.standardizedFileURL.resolvingSymlinksInPath()
        guard source != destination else {
            throw DatabaseBootstrapError.sourceMatchesDestination
        }
    }

    private func refuseInstallWhenDestinationExists() throws {
        guard !FileManager.default.fileExists(atPath: databaseURL.path) else {
            throw DatabaseBootstrapError.destinationAlreadyExists(databaseURL)
        }
    }

    private func validateDatabaseReadOnly(
        at url: URL
    ) throws -> DatabaseValidationReport {
        var configuration = Configuration()
        configuration.label = "PromptGarage.validation"
        configuration.readonly = true
        configuration.busyMode = .timeout(5)
        let database = try DatabaseQueue(path: url.path, configuration: configuration)
        defer { try? database.close() }
        return try DatabaseSchema.validateReadOnly(database)
    }

    private func validateDatabaseWritable(
        at url: URL
    ) throws -> DatabaseValidationReport {
        var configuration = Configuration()
        configuration.label = "PromptGarage.writable-validation"
        configuration.busyMode = .timeout(5)
        let database = try DatabaseQueue(path: url.path, configuration: configuration)
        defer { try? database.close() }

        _ = try DatabaseSchema.validateStructureAndCounts(database)
        try database.write { db in
            try DatabaseSchema.migrateDestinationIfNeeded(in: db)
            try DatabaseSchema.repairSearchIndexIfNeeded(in: db)
        }
        return try DatabaseSchema.validate(database)
    }

    static func validateCopyParity(
        source: DatabaseValidationReport,
        destination: DatabaseValidationReport
    ) throws {
        let counts = [
            source.promptCount,
            source.ftsCount,
            destination.promptCount,
            destination.ftsCount,
        ]
        guard Set(counts).count == 1 else {
            throw DatabaseBootstrapError.copyParityMismatch(
                sourcePrompts: source.promptCount,
                sourceFTS: source.ftsCount,
                destinationPrompts: destination.promptCount,
                destinationFTS: destination.ftsCount
            )
        }
    }

    private static func dataVersion(of queue: DatabaseQueue) throws -> Int64 {
        try queue.read { db in
            try Int64.fetchOne(db, sql: "PRAGMA data_version") ?? -1
        }
    }

    private static func removeDatabaseFiles(at url: URL) {
        let fileManager = FileManager.default
        if fileManager.fileExists(atPath: url.path) {
            try? fileManager.removeItem(at: url)
        }
        removeSQLiteSidecars(for: url)
    }

    private static func removeSQLiteSidecars(for url: URL) {
        let fileManager = FileManager.default
        for suffix in ["-wal", "-shm", "-journal"] {
            let sidecar = URL(fileURLWithPath: url.path + suffix)
            if fileManager.fileExists(atPath: sidecar.path) {
                try? fileManager.removeItem(at: sidecar)
            }
        }
    }
}

public enum DatabaseBootstrapState: Equatable, Sendable {
    case needsDatabase
    case validating
    case ready(DatabaseValidationReport)
    case invalid(DatabaseValidationError)
    case readOnly(DatabaseValidationReport)
    case failed(String)
}

public enum DatabaseBootstrapError: LocalizedError, Equatable, Sendable {
    case sourceMissing(URL)
    case sourceIsDirectory(URL)
    case sourceUnreadable(URL)
    case sourceMatchesDestination
    case destinationAlreadyExists(URL)
    case sourceChangedDuringCopy
    case copyParityMismatch(
        sourcePrompts: Int,
        sourceFTS: Int,
        destinationPrompts: Int,
        destinationFTS: Int
    )
    case invalidExistingDatabase(DatabaseValidationError)
    case couldNotInstallDestination(String)
    case installedDatabaseInvalid(String)
    case operationFailed(String)

    public var errorDescription: String? {
        switch self {
        case let .sourceMissing(url):
            "Die gewählte Datenbank existiert nicht: \(url.path)"
        case let .sourceIsDirectory(url):
            "Bitte eine prompts.db-Datei statt eines Ordners wählen: \(url.path)"
        case let .sourceUnreadable(url):
            "Die gewählte Datenbank ist nicht lesbar: \(url.path)"
        case .sourceMatchesDestination:
            "Die gewählte Quelle ist bereits die aktive PromptGarage-Datenbank."
        case let .destinationAlreadyExists(url):
            "Am App-Ziel existiert bereits eine Datenbank. PromptGarage ersetzt sie niemals automatisch. Beende die App und verschiebe oder benenne \(url.path) manuell um; starte PromptGarage danach erneut."
        case .sourceChangedDuringCopy:
            "Die Quelldatenbank wurde während der Sicherheitskopie geändert. Bitte den Import erneut starten."
        case let .copyParityMismatch(
            sourcePrompts,
            sourceFTS,
            destinationPrompts,
            destinationFTS
        ):
            "Die Vierfach-Prüfung ist fehlgeschlagen (Quelle prompts=\(sourcePrompts), Quelle FTS=\(sourceFTS), App-Kopie prompts=\(destinationPrompts), App-Kopie FTS=\(destinationFTS))."
        case let .invalidExistingDatabase(error):
            error.localizedDescription
        case let .couldNotInstallDestination(message):
            "Die App-Kopie konnte nicht atomar installiert werden: \(message)"
        case let .installedDatabaseInvalid(message):
            "Die installierte App-Kopie konnte nicht geöffnet werden: \(message)"
        case let .operationFailed(message):
            message
        }
    }
}