Tests/PromptGarageCoreTests/RealDatabaseMigrationTests.swift

import CryptoKit
import Foundation
import GRDB
import XCTest
@testable import PromptGarageCore

/// Opt-in integration coverage for Samuel's real legacy database.
///
/// Run on the Mac with:
/// `PROMPTGARAGE_SOURCE_DB=/absolute/path/to/prompts.db swift test --filter RealDatabaseMigrationTests`
///
/// The source is hashed before and after, and all SQLite connections opened by
/// the production bootstrap service are read-only. Only a disposable copy below
/// XCTest's temporary directory is migrated.
final class RealDatabaseMigrationTests: XCTestCase {
    func testRealSourceInstallsToTemporaryDestinationWithoutSourceMutationOrBackfill() throws {
        let environment = ProcessInfo.processInfo.environment
        guard let sourcePath = environment["PROMPTGARAGE_SOURCE_DB"]?
            .trimmingCharacters(in: .whitespacesAndNewlines),
            !sourcePath.isEmpty
        else {
            throw XCTSkip("Set PROMPTGARAGE_SOURCE_DB to opt in to the real legacy DB migration test.")
        }

        let sourceURL = URL(fileURLWithPath: sourcePath).standardizedFileURL
        guard FileManager.default.fileExists(atPath: sourceURL.path) else {
            XCTFail("PROMPTGARAGE_SOURCE_DB does not exist: \(sourceURL.path)")
            return
        }

        let sourceFamilyBefore = try databaseFamilySnapshot(at: sourceURL)
        let sourceQueue = try readOnlyQueue(
            at: sourceURL,
            label: "PromptGarageTests.source.readonly"
        )
        let sourceLegacySnapshot: LegacyRowsSnapshot
        do {
            sourceLegacySnapshot = try legacyRowsSnapshot(in: sourceQueue)
            try sourceQueue.close()
        } catch {
            try? sourceQueue.close()
            throw error
        }

        let root = FileManager.default.temporaryDirectory
            .appendingPathComponent("PromptGarageRealMigration-\(UUID().uuidString)", isDirectory: true)
        try FileManager.default.createDirectory(at: root, withIntermediateDirectories: true)
        addTeardownBlock { try? FileManager.default.removeItem(at: root) }

        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        let sourceReport = try service.validateSource(at: sourceURL)
        XCTAssertFalse(
            sourceReport.hasPromptedAt,
            "The opt-in source must be the untouched legacy DB, not an already migrated app copy."
        )
        XCTAssertGreaterThan(sourceReport.promptCount, 0)

        let repository = try service.installDatabase(from: sourceURL)
        let destinationQueue = try readOnlyQueue(
            at: service.databaseURL,
            label: "PromptGarageTests.destination.readonly"
        )
        defer { try? destinationQueue.close() }
        let destinationReport = try DatabaseSchema.validateReadOnly(destinationQueue)
        let destinationLegacySnapshot = try legacyRowsSnapshot(in: destinationQueue)

        let fourParityCounts = [
            sourceReport.promptCount,
            sourceReport.ftsCount,
            destinationReport.promptCount,
            destinationReport.ftsCount,
        ]
        XCTAssertEqual(
            Set(fourParityCounts).count,
            1,
            "source prompts/source FTS/destination prompts/destination FTS must stay dynamically equal: \(fourParityCounts)"
        )
        XCTAssertTrue(destinationReport.hasPromptedAt)
        XCTAssertEqual(try repository.count(), sourceReport.promptCount)
        XCTAssertEqual(try repository.legacyUndatedCount(), sourceReport.promptCount)
        XCTAssertEqual(sourceLegacySnapshot.rowCount, sourceReport.promptCount)
        XCTAssertEqual(destinationLegacySnapshot.rowCount, destinationReport.promptCount)
        XCTAssertEqual(
            destinationLegacySnapshot.columnDigests,
            sourceLegacySnapshot.columnDigests,
            "Every value in every legacy column must survive the copy exactly."
        )
        XCTAssertEqual(
            destinationLegacySnapshot.orderedRowsDigest,
            sourceLegacySnapshot.orderedRowsDigest,
            "Legacy row/column associations must remain byte-for-byte equivalent in deterministic id order."
        )

        let destinationLegacyNullCount = try destinationQueue.read { db in
            try Int.fetchOne(
                db,
                sql: "SELECT COUNT(*) FROM prompts WHERE prompted_at IS NULL"
            ) ?? -1
        }
        XCTAssertEqual(
            destinationLegacyNullCount,
            sourceReport.promptCount,
            "Adding prompted_at to the app copy must not infer or backfill dates for any legacy row."
        )

        XCTAssertEqual(
            try databaseFamilySnapshot(at: sourceURL),
            sourceFamilyBefore,
            "The selected source DB family (main, WAL, SHM, or journal) changed during a supposedly read-only migration."
        )
    }

    private let legacyColumns = [
        "id", "title", "content", "source", "is_starred", "tags", "model",
        "rating", "use_count", "created_at", "updated_at",
    ]

    private func readOnlyQueue(at url: URL, label: String) throws -> DatabaseQueue {
        var configuration = Configuration()
        configuration.label = label
        configuration.readonly = true
        configuration.busyMode = .timeout(5)
        return try DatabaseQueue(path: url.path, configuration: configuration)
    }

    /// Hashes every SQLite value with an explicit storage-class marker and
    /// length framing. Per-column digests prove column preservation; the full
    /// digest additionally proves that values stayed attached to the same row
    /// in deterministic primary-key order.
    private func legacyRowsSnapshot(in queue: DatabaseQueue) throws -> LegacyRowsSnapshot {
        try queue.read { db in
            let selectedColumns = legacyColumns
                .map { "\"\($0)\"" }
                .joined(separator: ", ")
            let cursor = try Row.fetchCursor(
                db,
                sql: "SELECT \(selectedColumns) FROM prompts ORDER BY id ASC"
            )

            var rowCount = 0
            var orderedRowsHasher = SHA256()
            var columnHashers = legacyColumns.map { _ in SHA256() }

            while let row = try cursor.next() {
                rowCount += 1
                update(&orderedRowsHasher, withUnsignedInteger: UInt64(rowCount))

                for (index, column) in legacyColumns.enumerated() {
                    let value: DatabaseValue = row[column]
                    update(&orderedRowsHasher, with: value)
                    update(&columnHashers[index], with: value)
                }
            }

            let columnDigests = Dictionary(uniqueKeysWithValues: zip(
                legacyColumns,
                columnHashers.map { hasher in
                    var hasher = hasher
                    return hexadecimal(hasher.finalize())
                }
            ))
            return LegacyRowsSnapshot(
                rowCount: rowCount,
                orderedRowsDigest: hexadecimal(orderedRowsHasher.finalize()),
                columnDigests: columnDigests
            )
        }
    }

    private func update(_ hasher: inout SHA256, with value: DatabaseValue) {
        switch value.storage {
        case .null:
            hasher.update(data: Data([0]))
        case let .int64(value):
            hasher.update(data: Data([1]))
            update(&hasher, withUnsignedInteger: UInt64(bitPattern: value))
        case let .double(value):
            hasher.update(data: Data([2]))
            update(&hasher, withUnsignedInteger: value.bitPattern)
        case let .string(value):
            hasher.update(data: Data([3]))
            update(&hasher, withLengthPrefixed: Data(value.utf8))
        case let .blob(value):
            hasher.update(data: Data([4]))
            update(&hasher, withLengthPrefixed: value)
        }
    }

    private func update(_ hasher: inout SHA256, withLengthPrefixed data: Data) {
        update(&hasher, withUnsignedInteger: UInt64(data.count))
        hasher.update(data: data)
    }

    private func update(_ hasher: inout SHA256, withUnsignedInteger value: UInt64) {
        var bigEndian = value.bigEndian
        withUnsafeBytes(of: &bigEndian) { bytes in
            hasher.update(data: Data(bytes))
        }
    }

    private func databaseFamilySnapshot(at mainURL: URL) throws -> [DatabaseFileSnapshot] {
        try ["", "-wal", "-shm", "-journal"].map { suffix in
            let url = URL(fileURLWithPath: mainURL.path + suffix)
            guard FileManager.default.fileExists(atPath: url.path) else {
                return DatabaseFileSnapshot(suffix: suffix, byteCount: nil, sha256: nil)
            }
            let attributes = try FileManager.default.attributesOfItem(atPath: url.path)
            let byteCount = (attributes[.size] as? NSNumber)?.uint64Value
            return DatabaseFileSnapshot(
                suffix: suffix,
                byteCount: byteCount,
                sha256: try sha256(of: url)
            )
        }
    }

    private func sha256(of url: URL) throws -> String {
        let data = try Data(contentsOf: url, options: [.mappedIfSafe])
        return hexadecimal(SHA256.hash(data: data))
    }

    private func hexadecimal<D: Sequence>(_ digest: D) -> String where D.Element == UInt8 {
        digest.map { String(format: "%02x", $0) }.joined()
    }
}

private struct LegacyRowsSnapshot: Equatable {
    let rowCount: Int
    let orderedRowsDigest: String
    let columnDigests: [String: String]
}

private struct DatabaseFileSnapshot: Equatable {
    let suffix: String
    let byteCount: UInt64?
    let sha256: String?
}