import HealthKit

import SQLite3

import XCTest

@testable import HealthProbe

final class SQLiteHealthArchiveStoreTests: XCTestCase {

        var result: Result<T, Error>?

    }

    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)

        XCTAssertEqual(secondWrite.updatedCount, 0)

        XCTAssertEqual(secondWrite.unchangedCount, 1)

        XCTAssertEqual(records.count, 1)

        XCTAssertEqual(records.first?.displayValue, "42.0 count")

        XCTAssertTrue(report.passed)

    }

        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 WHERE observation_id = \(observationIDs[0]) AND inserted_event_count = 1", at: url), 1)

        XCTAssertFalse(try tableExists("archive_samples", at: url))

    }

        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)

    }

        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))

        let observationID = try await store.beginObservation(

            observedAt: Date(timeIntervalSince1970: 3_060),

            triggerReason: "manual",

            selectedTypeSetHash: "selected-types"

        )

        _ = try await store.upsertSamples(

            [secondSample],

            observedAt: Date(timeIntervalSince1970: 3_060),

            observationID: observationID

        )

        try await store.recordDisappearance(

            sampleUUIDHash: HashService.sampleUUIDHash(firstSample.uuid.uuidString),

            sampleTypeIdentifier: typeIdentifier,

            observedMissingAt: Date(timeIntervalSince1970: 3_060),

            observationID: observationID

        )

        try await store.markVerification(

            sampleType: secondSample.sampleType,

            verifiedAt: Date(timeIntervalSince1970: 3_060),

            observationID: observationID

        )

        try await store.finishObservation(

            observationID: observationID,

            status: "completed",

            endedAt: Date(timeIntervalSince1970: 3_070)

        )

        let observationIDs = try observationIDs(at: url)

        let summary = try await store.diffSummary(HealthArchiveDiffRequest(

            fromObservationID: observationIDs[0],

            toObservationID: observationID,

            sampleTypeIdentifier: typeIdentifier

        ))

        XCTAssertEqual(observationIDs.count, 2)

        XCTAssertEqual(summary.appearedCount, 1)

        XCTAssertEqual(summary.disappearedCount, 1)

        XCTAssertEqual(try countRows(in: "observations WHERE id = \(observationID) AND status = 'completed' AND selected_type_set_hash = 'selected-types'", at: url), 1)

        XCTAssertEqual(try countRows(in: "sample_observation_events WHERE observation_id = \(observationID)", at: url), 2)

        XCTAssertEqual(try countRows(in: "observation_type_runs WHERE observation_id = \(observationID) AND inserted_event_count = 1 AND deleted_event_count = 1 AND verified_visible_count = 1 AND status = 'completed'", at: url), 1)

    }

        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)

    }

        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)")

            }

        }

    }

        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)

    }

        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)

    }

        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 {

    }

        let startDate = Date(timeIntervalSince1970: start)

        (0..<count).map { offset in

            let index = startIndex + offset

            return makeStepCountSample(

                value: Double((index % 97) + 1),

                start: 10_000 + Double(index * 600)

            )

        }

    }

        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()

    }

        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))

    }

        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

    }

        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

    }

        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")

    }

}