import HealthKit
import SQLite3
import XCTest
@testable import HealthProbe

final class SQLiteHealthArchiveStoreTests: XCTestCase {
    private final class AsyncResultBox<T>: @unchecked Sendable {
        var result: Result<T, Error>?
    }

    private var temporaryDirectory: URL!

    override func setUpWithError() throws {
        temporaryDirectory = FileManager.default.temporaryDirectory
            .appending(path: "HealthProbeTests-\(UUID().uuidString)", directoryHint: .isDirectory)
        try FileManager.default.createDirectory(at: temporaryDirectory, withIntermediateDirectories: true)
    }

    override func tearDownWithError() throws {
        if let temporaryDirectory {
            try? FileManager.default.removeItem(at: temporaryDirectory)
        }
        temporaryDirectory = nil
    }

    func testFreshArchiveInitializesSchemaAndPassesIntegrity() async throws {
        let store = SQLiteHealthArchiveStore(databaseURL: databaseURL())

        let report = try await store.checkIntegrity()
        let records = try await store.records(for: HealthArchiveRecordRequest(
            sampleTypeIdentifier: HKQuantityTypeIdentifier.stepCount.rawValue
        ))

        XCTAssertTrue(report.passed)
        XCTAssertEqual(report.schemaVersion, 2)
        XCTAssertEqual(report.sqliteIntegrityStatus, "ok")
        XCTAssertEqual(report.foreignKeyIssueCount, 0)
        XCTAssertTrue(report.missingTableNames.isEmpty)
        XCTAssertTrue(report.requiredTableNames.contains("sample_visibility_ranges"))
        XCTAssertTrue(report.requiredTableNames.contains("daily_type_aggregates"))
        XCTAssertTrue(records.isEmpty)
    }

    func testPrototypeArchiveIsResetAndReinitializedAsV2() async throws {
        let url = databaseURL()
        try createPrototypeDatabase(at: url)
        let store = SQLiteHealthArchiveStore(databaseURL: url)

        let report = try await store.checkIntegrity()

        XCTAssertTrue(report.passed)
        XCTAssertEqual(report.schemaVersion, 2)
        XCTAssertTrue(report.missingTableNames.isEmpty)
    }

    func testRepeatedSamplePageDoesNotDuplicateIdentityOrVersion() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let sample = makeStepCountSample()

        let firstWrite = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 2_000))
        let secondWrite = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 2_060))
        let records = try await store.records(for: HealthArchiveRecordRequest(
            sampleTypeIdentifier: sample.sampleType.identifier
        ))
        let report = try await store.checkIntegrity()
        let versionDebugRows = try sampleVersionDebugRows(at: url)

        XCTAssertEqual(firstWrite.insertedCount, 1)
        XCTAssertEqual(firstWrite.updatedCount, 0)
        XCTAssertEqual(firstWrite.unchangedCount, 0)
        XCTAssertEqual(try countRows(in: "samples", at: url), 1)
        XCTAssertEqual(try countRows(in: "sample_versions", at: url), 1, versionDebugRows)
        XCTAssertEqual(try countRows(in: "sample_visibility_ranges", at: url), 1)
        XCTAssertEqual(try countRows(in: "source_revisions", at: url), 1)
        XCTAssertFalse(try tableExists("archive_samples", at: url))
        XCTAssertEqual(secondWrite.insertedCount, 0)
        XCTAssertEqual(secondWrite.updatedCount, 0)
        XCTAssertEqual(secondWrite.unchangedCount, 1)
        XCTAssertEqual(records.count, 1)
        XCTAssertEqual(records.first?.displayValue, "42.0 count")
        XCTAssertTrue(report.passed)
    }

    func testVerificationUsesArchiveV2TablesWithoutLegacyMirror() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let sample = makeStepCountSample()

        _ = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 2_000))
        try await store.markVerification(sampleType: sample.sampleType, verifiedAt: Date(timeIntervalSince1970: 2_060))
        let observationIDs = try observationIDs(at: url)

        XCTAssertEqual(observationIDs.count, 2)
        XCTAssertEqual(try countRows(in: "observation_type_runs", at: url), 1)
        XCTAssertEqual(try countRows(in: "observation_type_summaries WHERE observation_id = \(observationIDs[1]) AND visible_record_count = 1", at: url), 1)
        XCTAssertFalse(try tableExists("archive_samples", at: url))
    }

    func testDiffSummaryAndRecordsBetweenObservationsUseSQLVisibility() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let firstSample = makeStepCountSample(value: 42, start: 1_000)
        let secondSample = makeStepCountSample(value: 7, start: 2_000)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue

        _ = try await store.upsertSamples([firstSample], observedAt: Date(timeIntervalSince1970: 3_000))
        _ = try await store.upsertSamples([firstSample, secondSample], observedAt: Date(timeIntervalSince1970: 3_060))
        try await store.recordDisappearance(
            sampleUUIDHash: HashService.sampleUUIDHash(firstSample.uuid.uuidString),
            sampleTypeIdentifier: typeIdentifier,
            observedMissingAt: Date(timeIntervalSince1970: 3_120)
        )
        let observationIDs = try observationIDs(at: url)
        XCTAssertEqual(observationIDs.count, 3)

        let appearedSummary = try await store.diffSummary(HealthArchiveDiffRequest(
            fromObservationID: observationIDs[0],
            toObservationID: observationIDs[1],
            sampleTypeIdentifier: typeIdentifier
        ))
        let appearedRecords = try await store.diffRecords(HealthArchiveDiffRecordRequest(
            fromObservationID: observationIDs[0],
            toObservationID: observationIDs[1],
            sampleTypeIdentifier: typeIdentifier,
            kind: .appeared,
            limit: 10
        ))
        let disappearedSummary = try await store.diffSummary(HealthArchiveDiffRequest(
            fromObservationID: observationIDs[1],
            toObservationID: observationIDs[2],
            sampleTypeIdentifier: typeIdentifier
        ))
        let disappearedRecords = try await store.diffRecords(HealthArchiveDiffRecordRequest(
            fromObservationID: observationIDs[1],
            toObservationID: observationIDs[2],
            sampleTypeIdentifier: typeIdentifier,
            kind: .disappeared,
            limit: 10
        ))

        XCTAssertEqual(appearedSummary.appearedCount, 1)
        XCTAssertEqual(appearedSummary.disappearedCount, 0)
        XCTAssertEqual(appearedSummary.representationChangedCount, 0)
        XCTAssertEqual(appearedRecords.map(\.displayValue), ["7.0 count"])
        XCTAssertEqual(disappearedSummary.appearedCount, 0)
        XCTAssertEqual(disappearedSummary.disappearedCount, 1)
        XCTAssertEqual(disappearedSummary.representationChangedCount, 0)
        XCTAssertEqual(disappearedRecords.map(\.displayValue), ["42.0 count"])
        XCTAssertNotNil(disappearedRecords.first?.disappearedAt)
    }

    func testLargeSyntheticDiffReturnsCountsAndBoundedPages() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue
        let initialCount = 1_200
        let appearedCount = 180
        let disappearedCount = 160
        let pageSize = 25
        let initialSamples = makeStepCountSamples(count: initialCount, startIndex: 0)
        let appearedSamples = makeStepCountSamples(count: appearedCount, startIndex: initialCount)

        _ = try await store.upsertSamples(initialSamples, observedAt: Date(timeIntervalSince1970: 1_000_000))
        _ = try await store.upsertSamples(initialSamples + appearedSamples, observedAt: Date(timeIntervalSince1970: 1_000_060))
        for (offset, sample) in initialSamples.prefix(disappearedCount).enumerated() {
            try await store.recordDisappearance(
                sampleUUIDHash: HashService.sampleUUIDHash(sample.uuid.uuidString),
                sampleTypeIdentifier: typeIdentifier,
                observedMissingAt: Date(timeIntervalSince1970: 1_000_120 + Double(offset))
            )
        }

        let observationIDs = try observationIDs(at: url)
        let firstObservationID = try XCTUnwrap(observationIDs.first)
        let lastObservationID = try XCTUnwrap(observationIDs.last)
        let queryStartedAt = Date()
        let summary = try await store.diffSummary(HealthArchiveDiffRequest(
            fromObservationID: firstObservationID,
            toObservationID: lastObservationID,
            sampleTypeIdentifier: typeIdentifier
        ))
        let firstPage = try await store.diffRecords(HealthArchiveDiffRecordRequest(
            fromObservationID: firstObservationID,
            toObservationID: lastObservationID,
            sampleTypeIdentifier: typeIdentifier,
            kind: .appeared,
            limit: pageSize
        ))
        let firstPageLastRecord = try XCTUnwrap(firstPage.last)
        let secondPage = try await store.diffRecords(HealthArchiveDiffRecordRequest(
            fromObservationID: firstObservationID,
            toObservationID: lastObservationID,
            sampleTypeIdentifier: typeIdentifier,
            kind: .appeared,
            afterCursor: RecordCursor(
                startDate: firstPageLastRecord.startDate,
                strictFingerprint: firstPageLastRecord.strictFingerprint
            ),
            limit: pageSize
        ))
        let queryElapsedSeconds = Date().timeIntervalSince(queryStartedAt)

        XCTAssertEqual(summary.appearedCount, appearedCount)
        XCTAssertEqual(summary.disappearedCount, disappearedCount)
        XCTAssertEqual(summary.representationChangedCount, 0)
        XCTAssertEqual(firstPage.count, pageSize)
        XCTAssertEqual(secondPage.count, pageSize)
        XCTAssertTrue(Set(firstPage.map(\.id)).isDisjoint(with: Set(secondPage.map(\.id))))
        XCTAssertLessThan(queryElapsedSeconds, 10)
    }

    func testLargeSyntheticDiffQueryMetrics() throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue
        let initialCount = 900
        let appearedCount = 120
        let disappearedCount = 100
        let pageSize = 25
        let initialSamples = makeStepCountSamples(count: initialCount, startIndex: 0)
        let appearedSamples = makeStepCountSamples(count: appearedCount, startIndex: initialCount)

        try waitForArchiveOperation {
            _ = try await store.upsertSamples(initialSamples, observedAt: Date(timeIntervalSince1970: 1_200_000))
            _ = try await store.upsertSamples(initialSamples + appearedSamples, observedAt: Date(timeIntervalSince1970: 1_200_060))
            for (offset, sample) in initialSamples.prefix(disappearedCount).enumerated() {
                try await store.recordDisappearance(
                    sampleUUIDHash: HashService.sampleUUIDHash(sample.uuid.uuidString),
                    sampleTypeIdentifier: typeIdentifier,
                    observedMissingAt: Date(timeIntervalSince1970: 1_200_120 + Double(offset))
                )
            }
        }

        let observationIDs = try observationIDs(at: url)
        let firstObservationID = try XCTUnwrap(observationIDs.first)
        let lastObservationID = try XCTUnwrap(observationIDs.last)
        let options = XCTMeasureOptions()
        options.iterationCount = 3

        measure(metrics: [XCTClockMetric(), XCTMemoryMetric()], options: options) {
            do {
                let result = try waitForArchiveOperation {
                    let summary = try await store.diffSummary(HealthArchiveDiffRequest(
                        fromObservationID: firstObservationID,
                        toObservationID: lastObservationID,
                        sampleTypeIdentifier: typeIdentifier
                    ))
                    let records = try await store.diffRecords(HealthArchiveDiffRecordRequest(
                        fromObservationID: firstObservationID,
                        toObservationID: lastObservationID,
                        sampleTypeIdentifier: typeIdentifier,
                        kind: .appeared,
                        limit: pageSize
                    ))
                    return (summary, records)
                }
                XCTAssertEqual(result.0.appearedCount, appearedCount)
                XCTAssertEqual(result.0.disappearedCount, disappearedCount)
                XCTAssertEqual(result.1.count, pageSize)
            } catch {
                XCTFail("Measured archive query failed: \(error)")
            }
        }
    }

    func testAggregateComparisonUsesMaterializedDailyAggregates() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let firstSample = makeStepCountSample(value: 42, start: 1_000)
        let secondSample = makeStepCountSample(value: 7, start: 2_000)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue

        _ = try await store.upsertSamples([firstSample], observedAt: Date(timeIntervalSince1970: 3_000))
        _ = try await store.upsertSamples([firstSample, secondSample], observedAt: Date(timeIntervalSince1970: 3_060))
        let observationIDs = try observationIDs(at: url)
        XCTAssertEqual(observationIDs.count, 2)

        let rows = try await store.aggregateComparison(HealthArchiveAggregateComparisonRequest(
            fromObservationID: observationIDs[0],
            toObservationID: observationIDs[1],
            sampleTypeIdentifier: typeIdentifier,
            limit: 10
        ))

        XCTAssertEqual(rows.count, 1)
        XCTAssertEqual(rows.first?.sampleTypeIdentifier, typeIdentifier)
        XCTAssertEqual(rows.first?.fromVisibleRecordCount, 1)
        XCTAssertEqual(rows.first?.toVisibleRecordCount, 2)
        XCTAssertEqual(rows.first?.visibleRecordDelta, 1)
        XCTAssertEqual(rows.first?.fromValueSum, 42)
        XCTAssertEqual(rows.first?.toValueSum, 49)
        XCTAssertEqual(rows.first?.valueSumDelta, 7)
    }

    func testSourceProvenanceBreakdownReturnsVisibleSourceRows() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let sample = makeStepCountSample(value: 42, start: 1_000)
        let typeIdentifier = sample.sampleType.identifier

        _ = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 3_000))

        let rows = try await store.sourceProvenanceBreakdown(HealthArchiveSourceProvenanceRequest(
            visibleAtObservationID: nil,
            sampleTypeIdentifier: typeIdentifier,
            limit: 10
        ))

        XCTAssertEqual(rows.count, 1)
        XCTAssertEqual(rows.first?.sampleTypeIdentifier, typeIdentifier)
        XCTAssertEqual(rows.first?.visibleRecordCount, 1)
        XCTAssertEqual(rows.first?.valueSum, 42)
        XCTAssertEqual(rows.first?.sourceBundleIdentifier, sample.sourceRevision.source.bundleIdentifier)
    }

    func testConsolidationEvidenceClassifiesStableCountDropAsConsolidationLikely() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue

        let firstOldSample = makeStepCountSample(value: 10, start: 1_000, end: 1_500)
        let secondOldSample = makeStepCountSample(value: 20, start: 1_600, end: 2_000)
        let consolidatedSample = makeStepCountSample(value: 30, start: 1_000, end: 2_500)

        _ = try await store.upsertSamples([firstOldSample, secondOldSample], observedAt: Date(timeIntervalSince1970: 3_000))
        _ = try await store.upsertSamples([consolidatedSample], observedAt: Date(timeIntervalSince1970: 3_060))
        try await store.recordDisappearance(sampleUUIDHash: HashService.sampleUUIDHash(firstOldSample.uuid.uuidString), sampleTypeIdentifier: typeIdentifier, observedMissingAt: Date(timeIntervalSince1970: 3_120))
        try await store.recordDisappearance(sampleUUIDHash: HashService.sampleUUIDHash(secondOldSample.uuid.uuidString), sampleTypeIdentifier: typeIdentifier, observedMissingAt: Date(timeIntervalSince1970: 3_180))

        let observationIDs = try observationIDs(at: url)
        XCTAssertEqual(observationIDs.count, 4)

        let rows = try await store.consolidationEvidence(HealthArchiveConsolidationEvidenceRequest(
            fromObservationID: observationIDs[0],
            toObservationID: observationIDs[observationIDs.count - 1],
            sampleTypeIdentifier: typeIdentifier
        ))

        XCTAssertEqual(rows.count, 1)
        XCTAssertEqual(rows.first?.sampleTypeIdentifier, typeIdentifier)
        XCTAssertEqual(rows.first?.disappearedCount, 2)
        XCTAssertEqual(rows.first?.appearedCount, 1)
        XCTAssertEqual(rows.first?.fromVisibleRecordCount, 2)
        XCTAssertEqual(rows.first?.toVisibleRecordCount, 1)
        XCTAssertEqual(rows.first?.fromValueSum, 30)
        XCTAssertEqual(rows.first?.toValueSum, 30)
        XCTAssertEqual(rows.first?.label, "consolidation_likely")
        XCTAssertTrue(rows.first?.sourceCompatible == true)
    }

    func testConsolidationEvidenceClassifiesDenseStableShiftAsAggregateChanged() async throws {
        let url = databaseURL()
        let store = SQLiteHealthArchiveStore(databaseURL: url)
        let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue

        let firstOldSample = makeStepCountSample(value: 10, start: 1_000, end: 1_500)
        let secondOldSample = makeStepCountSample(value: 20, start: 5_000, end: 6_000)
        let denseSample = makeStepCountSample(value: 30, start: 1_000, end: 1_200)

        _ = try await store.upsertSamples([firstOldSample, secondOldSample], observedAt: Date(timeIntervalSince1970: 3_000))
        _ = try await store.upsertSamples([denseSample], observedAt: Date(timeIntervalSince1970: 3_060))
        try await store.recordDisappearance(sampleUUIDHash: HashService.sampleUUIDHash(firstOldSample.uuid.uuidString), sampleTypeIdentifier: typeIdentifier, observedMissingAt: Date(timeIntervalSince1970: 3_120))
        try await store.recordDisappearance(sampleUUIDHash: HashService.sampleUUIDHash(secondOldSample.uuid.uuidString), sampleTypeIdentifier: typeIdentifier, observedMissingAt: Date(timeIntervalSince1970: 3_180))

        let observationIDs = try observationIDs(at: url)
        XCTAssertEqual(observationIDs.count, 4)

        let rows = try await store.consolidationEvidence(HealthArchiveConsolidationEvidenceRequest(
            fromObservationID: observationIDs[0],
            toObservationID: observationIDs[observationIDs.count - 1],
            sampleTypeIdentifier: typeIdentifier
        ))

        XCTAssertEqual(rows.count, 1)
        XCTAssertEqual(rows.first?.label, "aggregate_changed")
        XCTAssertEqual(rows.first?.fromVisibleRecordCount, 2)
        XCTAssertEqual(rows.first?.toVisibleRecordCount, 1)
        XCTAssertEqual(rows.first?.fromValueSum, 30)
        XCTAssertEqual(rows.first?.toValueSum, 30)
    }

    private func databaseURL() -> URL {
        temporaryDirectory.appending(path: "Archive.sqlite")
    }

    private func createPrototypeDatabase(at url: URL) throws {
        var db: OpaquePointer?
        guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            sqlite3_close(db)
            XCTFail("Could not create prototype database")
            return
        }
        defer { sqlite3_close(db) }
        let status = sqlite3_exec(db, "CREATE TABLE archive_samples (id TEXT PRIMARY KEY)", nil, nil, nil)
        XCTAssertEqual(status, SQLITE_OK)
    }

    private func makeStepCountSample() -> HKQuantitySample {
        makeStepCountSample(value: 42, start: 1_000)
    }

    private func makeStepCountSample(value: Double, start: TimeInterval, end: TimeInterval? = nil) -> HKQuantitySample {
        let quantityType = HKQuantityType.quantityType(forIdentifier: .stepCount)!
        let quantity = HKQuantity(unit: .count(), doubleValue: value)
        let startDate = Date(timeIntervalSince1970: start)
        let endDate = Date(timeIntervalSince1970: end ?? (start + 300))
        return HKQuantitySample(type: quantityType, quantity: quantity, start: startDate, end: endDate)
    }

    private func makeStepCountSamples(count: Int, startIndex: Int) -> [HKQuantitySample] {
        (0..<count).map { offset in
            let index = startIndex + offset
            return makeStepCountSample(
                value: Double((index % 97) + 1),
                start: 10_000 + Double(index * 600)
            )
        }
    }

    private func waitForArchiveOperation<T>(_ operation: @escaping () async throws -> T) throws -> T {
        let expectation = expectation(description: "archive operation")
        let box = AsyncResultBox<T>()

        Task {
            do {
                box.result = .success(try await operation())
            } catch {
                box.result = .failure(error)
            }
            expectation.fulfill()
        }

        wait(for: [expectation], timeout: 20)
        return try XCTUnwrap(box.result).get()
    }

    private func countRows(in tableName: String, at url: URL) throws -> Int {
        var db: OpaquePointer?
        guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            sqlite3_close(db)
            XCTFail("Could not open test database")
            return 0
        }
        defer { sqlite3_close(db) }

        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(db, "SELECT COUNT(*) FROM \(tableName)", -1, &statement, nil) == SQLITE_OK else {
            sqlite3_finalize(statement)
            XCTFail("Could not prepare count query")
            return 0
        }
        defer { sqlite3_finalize(statement) }

        guard sqlite3_step(statement) == SQLITE_ROW else {
            return 0
        }
        return Int(sqlite3_column_int(statement, 0))
    }

    private func tableExists(_ tableName: String, at url: URL) throws -> Bool {
        var db: OpaquePointer?
        guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            sqlite3_close(db)
            XCTFail("Could not open test database")
            return false
        }
        defer { sqlite3_close(db) }

        let sql = """
        SELECT 1
        FROM sqlite_master
        WHERE type = 'table' AND name = ?
        LIMIT 1
        """
        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            sqlite3_finalize(statement)
            XCTFail("Could not prepare table existence query")
            return false
        }
        defer { sqlite3_finalize(statement) }

        sqlite3_bind_text(statement, 1, tableName, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))
        return sqlite3_step(statement) == SQLITE_ROW
    }

    private func observationIDs(at url: URL) throws -> [Int64] {
        var db: OpaquePointer?
        guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            sqlite3_close(db)
            XCTFail("Could not open test database")
            return []
        }
        defer { sqlite3_close(db) }

        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(db, "SELECT id FROM observations ORDER BY id", -1, &statement, nil) == SQLITE_OK else {
            sqlite3_finalize(statement)
            XCTFail("Could not prepare observation query")
            return []
        }
        defer { sqlite3_finalize(statement) }

        var ids: [Int64] = []
        while sqlite3_step(statement) == SQLITE_ROW {
            ids.append(sqlite3_column_int64(statement, 0))
        }
        return ids
    }

    private func sampleVersionDebugRows(at url: URL) throws -> String {
        var db: OpaquePointer?
        guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            sqlite3_close(db)
            return "could not open database"
        }
        defer { sqlite3_close(db) }

        let sql = """
        SELECT v.payload_hash, v.start_date, v.end_date, v.value_kind, v.numeric_value, v.unit,
               v.source_revision_id, src.bundle_identifier, sr.product_type, sr.version,
               sr.operating_system_version, v.hk_device_id, v.metadata_id
        FROM sample_versions v
        LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
        LEFT JOIN sources src ON src.id = sr.source_id
        ORDER BY v.id
        """
        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            sqlite3_finalize(statement)
            return "could not prepare version debug query"
        }
        defer { sqlite3_finalize(statement) }

        var rows: [String] = []
        while sqlite3_step(statement) == SQLITE_ROW {
            rows.append((0..<13).map { index in
                if sqlite3_column_type(statement, Int32(index)) == SQLITE_NULL {
                    return "null"
                }
                if let text = sqlite3_column_text(statement, Int32(index)) {
                    return String(cString: text)
                }
                return "\(sqlite3_column_double(statement, Int32(index)))"
            }.joined(separator: "|"))
        }
        return rows.joined(separator: "\n")
    }
}