import HealthKit
import SQLite3
import XCTest
@testable import HealthProbe

final class SQLiteHealthArchiveStoreTests: XCTestCase {
    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)
        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 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 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)
    }

    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) -> HKQuantitySample {
        let quantityType = HKQuantityType.quantityType(forIdentifier: .stepCount)!
        let quantity = HKQuantity(unit: .count(), doubleValue: value)
        let startDate = Date(timeIntervalSince1970: start)
        let endDate = Date(timeIntervalSince1970: start + 300)
        return HKQuantitySample(type: quantityType, quantity: quantity, start: startDate, end: endDate)
    }

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