import Foundation
import HealthKit
import SQLite3

private enum SQLiteHealthArchiveStoreError: Error {
    case openFailed(String)
    case prepareFailed(String)
    case stepFailed(String)
    case exportEncodingFailed
}

// Interface updated 2026-05-18 — see AGENTS.md
actor SQLiteHealthArchiveStore: HealthArchiveStore {
    static let shared = SQLiteHealthArchiveStore()

    private let databaseURL: URL
    private var didPrepareSchema = false

    init(databaseURL: URL? = nil) {
        let supportURL = URL.applicationSupportDirectory
        self.databaseURL = databaseURL ?? supportURL.appending(path: "HealthProbeArchive.sqlite")
    }

    func upsertSamples(_ samples: [HKSample], observedAt: Date) async throws -> HealthArchiveWriteSummary {
        guard !samples.isEmpty else {
            return HealthArchiveWriteSummary(insertedCount: 0, updatedCount: 0, unchangedCount: 0)
        }

        let db = try openDatabase()
        defer { sqlite3_close(db) }
        try prepareSchemaIfNeeded(db)
        try execute("BEGIN IMMEDIATE TRANSACTION", db: db)
        do {
            let summary = try upsertSamples(samples, observedAt: observedAt, db: db)
            try execute("COMMIT", db: db)
            return summary
        } catch {
            try? execute("ROLLBACK", db: db)
            throw error
        }
    }

    func markVerification(sampleType: HKSampleType, verifiedAt: Date) async throws {
        let db = try openDatabase()
        defer { sqlite3_close(db) }
        try prepareSchemaIfNeeded(db)

        let sql = """
        UPDATE archive_samples
        SET last_verified_at = ?, last_seen_at = COALESCE(last_seen_at, ?)
        WHERE type_identifier = ? AND disappeared_at IS NULL
        """
        try withStatement(sql, db: db) { statement in
            sqlite3_bind_double(statement, 1, verifiedAt.timeIntervalSinceReferenceDate)
            sqlite3_bind_double(statement, 2, verifiedAt.timeIntervalSinceReferenceDate)
            bindText(sampleType.identifier, to: 3, in: statement)
            guard sqlite3_step(statement) == SQLITE_DONE else {
                throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
            }
        }
    }

    func recordDisappearance(sampleUUIDHash: String, sampleTypeIdentifier: String, observedMissingAt: Date) async throws {
        let db = try openDatabase()
        defer { sqlite3_close(db) }
        try prepareSchemaIfNeeded(db)

        let sql = """
        UPDATE archive_samples
        SET disappeared_at = ?, last_verified_at = ?
        WHERE sample_uuid_hash = ? AND type_identifier = ?
        """
        try withStatement(sql, db: db) { statement in
            sqlite3_bind_double(statement, 1, observedMissingAt.timeIntervalSinceReferenceDate)
            sqlite3_bind_double(statement, 2, observedMissingAt.timeIntervalSinceReferenceDate)
            bindText(sampleUUIDHash, to: 3, in: statement)
            bindText(sampleTypeIdentifier, to: 4, in: statement)
            guard sqlite3_step(statement) == SQLITE_DONE else {
                throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
            }
        }
    }

    func records(for request: HealthArchiveRecordRequest) async throws -> [ArchivedHealthRecord] {
        let db = try openDatabase()
        defer { sqlite3_close(db) }
        try prepareSchemaIfNeeded(db)

        var clauses: [String] = []
        if request.sampleTypeIdentifier != nil {
            clauses.append("type_identifier = ?")
        }
        if !request.fingerprints.isEmpty {
            clauses.append("strict_fingerprint IN (\(Array(repeating: "?", count: request.fingerprints.count).joined(separator: ",")))")
        }
        let whereClause = clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))"
        let limitClause = request.limit.map { "LIMIT \(max($0, 0))" } ?? ""
        let sql = """
        SELECT sample_uuid_hash, type_identifier, strict_fingerprint, semantic_fingerprint,
               start_date, end_date, first_seen_at, last_seen_at, last_verified_at, disappeared_at
        FROM archive_samples
        \(whereClause)
        ORDER BY start_date ASC, strict_fingerprint ASC
        \(limitClause)
        """

        return try withStatement(sql, db: db) { statement in
            var index: Int32 = 1
            if let typeIdentifier = request.sampleTypeIdentifier {
                bindText(typeIdentifier, to: index, in: statement)
                index += 1
            }
            for fingerprint in request.fingerprints.sorted() {
                bindText(fingerprint, to: index, in: statement)
                index += 1
            }

            var records: [ArchivedHealthRecord] = []
            while sqlite3_step(statement) == SQLITE_ROW {
                records.append(ArchivedHealthRecord(
                    id: columnText(statement, 0) ?? "",
                    sampleTypeIdentifier: columnText(statement, 1) ?? "",
                    strictFingerprint: columnText(statement, 2) ?? "",
                    semanticFingerprint: columnText(statement, 3),
                    healthKitUUIDHash: columnText(statement, 0),
                    startDate: columnDate(statement, 4) ?? Date(timeIntervalSinceReferenceDate: 0),
                    endDate: columnDate(statement, 5) ?? Date(timeIntervalSinceReferenceDate: 0),
                    firstSeenAt: columnDate(statement, 6) ?? Date(timeIntervalSinceReferenceDate: 0),
                    lastSeenAt: columnDate(statement, 7),
                    lastVerifiedAt: columnDate(statement, 8),
                    disappearedAt: columnDate(statement, 9)
                ))
            }
            return records
        }
    }

    func exportReport(_ request: HealthArchiveReportRequest) async throws -> URL {
        let records = try await records(for: HealthArchiveRecordRequest(
            sampleTypeIdentifier: nil,
            fingerprints: request.includedFingerprints,
            limit: nil
        ))
        let payload = HealthArchiveReportPayload(
            reportID: request.reportID,
            title: request.title,
            exportedAt: Date(),
            records: records
        )
        let data = try JSONEncoder.healthArchive.encode(payload)
        let exportURL = URL.temporaryDirectory
            .appending(path: "HealthProbe-\(request.reportID.uuidString)")
            .appendingPathExtension("json")
        try data.write(to: exportURL, options: [.atomic])
        return exportURL
    }

    private func openDatabase() throws -> OpaquePointer? {
        try FileManager.default.createDirectory(
            at: databaseURL.deletingLastPathComponent(),
            withIntermediateDirectories: true
        )
        var db: OpaquePointer?
        guard sqlite3_open_v2(databaseURL.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            let message = db.map(lastErrorMessage) ?? "unable to open archive database"
            sqlite3_close(db)
            throw SQLiteHealthArchiveStoreError.openFailed(message)
        }
        return db
    }

    private func prepareSchemaIfNeeded(_ db: OpaquePointer?) throws {
        guard !didPrepareSchema else { return }
        try execute("PRAGMA journal_mode = WAL", db: db)
        try execute("PRAGMA foreign_keys = ON", db: db)
        try execute("""
        CREATE TABLE IF NOT EXISTS archive_samples (
            sample_uuid_hash TEXT PRIMARY KEY NOT NULL,
            type_identifier TEXT NOT NULL,
            strict_fingerprint TEXT NOT NULL,
            semantic_fingerprint TEXT,
            start_date REAL NOT NULL,
            end_date REAL NOT NULL,
            first_seen_at REAL NOT NULL,
            last_seen_at REAL,
            last_verified_at REAL,
            disappeared_at REAL,
            observed_count INTEGER NOT NULL DEFAULT 1,
            value_kind TEXT,
            value REAL,
            unit TEXT,
            category_value INTEGER,
            workout_activity_type INTEGER,
            duration_seconds REAL,
            source_name TEXT,
            source_bundle_identifier TEXT,
            source_product_type TEXT,
            source_version TEXT,
            source_operating_system_version TEXT,
            device_name TEXT,
            device_manufacturer TEXT,
            device_model TEXT,
            device_hardware_version TEXT,
            device_firmware_version TEXT,
            device_software_version TEXT,
            device_local_identifier TEXT,
            device_udi_device_identifier TEXT,
            metadata_json TEXT,
            archived_at REAL NOT NULL
        )
        """, db: db)
        try execute("CREATE INDEX IF NOT EXISTS idx_archive_samples_type_date ON archive_samples(type_identifier, start_date)", db: db)
        try execute("CREATE INDEX IF NOT EXISTS idx_archive_samples_strict_fingerprint ON archive_samples(strict_fingerprint)", db: db)
        didPrepareSchema = true
    }

    private func upsertSamples(_ samples: [HKSample], observedAt: Date, db: OpaquePointer?) throws -> HealthArchiveWriteSummary {
        let sql = """
        INSERT INTO archive_samples (
            sample_uuid_hash, type_identifier, strict_fingerprint, semantic_fingerprint,
            start_date, end_date, first_seen_at, last_seen_at, last_verified_at,
            disappeared_at, observed_count, value_kind, value, unit, category_value,
            workout_activity_type, duration_seconds, source_name, source_bundle_identifier,
            source_product_type, source_version, source_operating_system_version,
            device_name, device_manufacturer, device_model, device_hardware_version,
            device_firmware_version, device_software_version, device_local_identifier,
            device_udi_device_identifier, metadata_json, archived_at
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
        ON CONFLICT(sample_uuid_hash) DO UPDATE SET
            strict_fingerprint = excluded.strict_fingerprint,
            semantic_fingerprint = excluded.semantic_fingerprint,
            start_date = excluded.start_date,
            end_date = excluded.end_date,
            last_seen_at = excluded.last_seen_at,
            last_verified_at = excluded.last_verified_at,
            disappeared_at = NULL,
            observed_count = archive_samples.observed_count + 1,
            value_kind = excluded.value_kind,
            value = excluded.value,
            unit = excluded.unit,
            category_value = excluded.category_value,
            workout_activity_type = excluded.workout_activity_type,
            duration_seconds = excluded.duration_seconds,
            source_name = excluded.source_name,
            source_bundle_identifier = excluded.source_bundle_identifier,
            source_product_type = excluded.source_product_type,
            source_version = excluded.source_version,
            source_operating_system_version = excluded.source_operating_system_version,
            device_name = excluded.device_name,
            device_manufacturer = excluded.device_manufacturer,
            device_model = excluded.device_model,
            device_hardware_version = excluded.device_hardware_version,
            device_firmware_version = excluded.device_firmware_version,
            device_software_version = excluded.device_software_version,
            device_local_identifier = excluded.device_local_identifier,
            device_udi_device_identifier = excluded.device_udi_device_identifier,
            metadata_json = excluded.metadata_json,
            archived_at = excluded.archived_at
        """

        return try withStatement(sql, db: db) { statement in
            var inserted = 0
            var updated = 0
            for sample in samples {
                sqlite3_reset(statement)
                sqlite3_clear_bindings(statement)
                let row = ArchiveSampleRow(sample: sample, observedAt: observedAt)
                bind(row, to: statement)
                guard sqlite3_step(statement) == SQLITE_DONE else {
                    throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
                }
                if sqlite3_changes(db) == 1 {
                    inserted += 1
                } else {
                    updated += 1
                }
            }
            return HealthArchiveWriteSummary(
                insertedCount: inserted,
                updatedCount: updated,
                unchangedCount: max(0, samples.count - inserted - updated)
            )
        }
    }

    private func execute(_ sql: String, db: OpaquePointer?) throws {
        guard sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK else {
            throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
        }
    }

    private func withStatement<T>(_ sql: String, db: OpaquePointer?, body: (OpaquePointer?) throws -> T) throws -> T {
        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            throw SQLiteHealthArchiveStoreError.prepareFailed(lastErrorMessage(db))
        }
        defer { sqlite3_finalize(statement) }
        return try body(statement)
    }

    private func bind(_ row: ArchiveSampleRow, to statement: OpaquePointer?) {
        bindText(row.sampleUUIDHash, to: 1, in: statement)
        bindText(row.typeIdentifier, to: 2, in: statement)
        bindText(row.strictFingerprint, to: 3, in: statement)
        bindText(row.semanticFingerprint, to: 4, in: statement)
        sqlite3_bind_double(statement, 5, row.startDate.timeIntervalSinceReferenceDate)
        sqlite3_bind_double(statement, 6, row.endDate.timeIntervalSinceReferenceDate)
        sqlite3_bind_double(statement, 7, row.observedAt.timeIntervalSinceReferenceDate)
        sqlite3_bind_double(statement, 8, row.observedAt.timeIntervalSinceReferenceDate)
        sqlite3_bind_double(statement, 9, row.observedAt.timeIntervalSinceReferenceDate)
        bindText(row.valueKind, to: 10, in: statement)
        bindDouble(row.value, to: 11, in: statement)
        bindText(row.unit, to: 12, in: statement)
        bindInt(row.categoryValue, to: 13, in: statement)
        bindInt(row.workoutActivityType, to: 14, in: statement)
        bindDouble(row.durationSeconds, to: 15, in: statement)
        bindText(row.sourceName, to: 16, in: statement)
        bindText(row.sourceBundleIdentifier, to: 17, in: statement)
        bindText(row.sourceProductType, to: 18, in: statement)
        bindText(row.sourceVersion, to: 19, in: statement)
        bindText(row.sourceOperatingSystemVersion, to: 20, in: statement)
        bindText(row.deviceName, to: 21, in: statement)
        bindText(row.deviceManufacturer, to: 22, in: statement)
        bindText(row.deviceModel, to: 23, in: statement)
        bindText(row.deviceHardwareVersion, to: 24, in: statement)
        bindText(row.deviceFirmwareVersion, to: 25, in: statement)
        bindText(row.deviceSoftwareVersion, to: 26, in: statement)
        bindText(row.deviceLocalIdentifier, to: 27, in: statement)
        bindText(row.deviceUDI, to: 28, in: statement)
        bindText(row.metadataJSON, to: 29, in: statement)
        sqlite3_bind_double(statement, 30, row.observedAt.timeIntervalSinceReferenceDate)
    }
}

private struct ArchiveSampleRow {
    let sampleUUIDHash: String
    let typeIdentifier: String
    let strictFingerprint: String
    let semanticFingerprint: String
    let startDate: Date
    let endDate: Date
    let observedAt: Date
    let valueKind: String?
    let value: Double?
    let unit: String?
    let categoryValue: Int?
    let workoutActivityType: Int?
    let durationSeconds: Double?
    let sourceName: String?
    let sourceBundleIdentifier: String?
    let sourceProductType: String?
    let sourceVersion: String?
    let sourceOperatingSystemVersion: String?
    let deviceName: String?
    let deviceManufacturer: String?
    let deviceModel: String?
    let deviceHardwareVersion: String?
    let deviceFirmwareVersion: String?
    let deviceSoftwareVersion: String?
    let deviceLocalIdentifier: String?
    let deviceUDI: String?
    let metadataJSON: String?

    nonisolated init(sample: HKSample, observedAt: Date) {
        let sampleUUID = sample.uuid.uuidString
        let typeIdentifier = sample.sampleType.identifier
        let quantity = ArchiveSampleRow.quantityPayload(sample)
        let category = sample as? HKCategorySample
        let workout = sample as? HKWorkout
        let sourceRevision = sample.sourceRevision
        let device = sample.device

        self.sampleUUIDHash = HashService.sampleUUIDHash(sampleUUID)
        self.typeIdentifier = typeIdentifier
        self.strictFingerprint = HashService.sampleFingerprint(
            typeIdentifier: typeIdentifier,
            sampleUUID: sampleUUID,
            startDate: sample.startDate,
            endDate: sample.endDate
        )
        self.semanticFingerprint = HashService.archiveSemanticFingerprint(
            typeIdentifier: typeIdentifier,
            startDate: sample.startDate,
            endDate: sample.endDate,
            value: quantity?.value,
            unit: quantity?.unit,
            categoryValue: category?.value,
            workoutActivityType: workout?.workoutActivityType.rawValue,
            sourceBundleIdentifier: sourceRevision.source.bundleIdentifier
        )
        self.startDate = sample.startDate
        self.endDate = sample.endDate
        self.observedAt = observedAt
        self.valueKind = quantity?.kind ?? (category == nil ? (workout == nil ? nil : "workout") : "category")
        self.value = quantity?.value
        self.unit = quantity?.unit
        self.categoryValue = category?.value
        self.workoutActivityType = workout.map { Int($0.workoutActivityType.rawValue) }
        self.durationSeconds = workout?.duration
        self.sourceName = sourceRevision.source.name
        self.sourceBundleIdentifier = sourceRevision.source.bundleIdentifier
        self.sourceProductType = sourceRevision.productType
        self.sourceVersion = sourceRevision.version
        self.sourceOperatingSystemVersion = ArchiveSampleRow.operatingSystemVersionString(sourceRevision.operatingSystemVersion)
        self.deviceName = device?.name
        self.deviceManufacturer = device?.manufacturer
        self.deviceModel = device?.model
        self.deviceHardwareVersion = device?.hardwareVersion
        self.deviceFirmwareVersion = device?.firmwareVersion
        self.deviceSoftwareVersion = device?.softwareVersion
        self.deviceLocalIdentifier = device?.localIdentifier
        self.deviceUDI = device?.udiDeviceIdentifier
        self.metadataJSON = ArchiveSampleRow.metadataJSONString(sample.metadata)
    }

    nonisolated private static func quantityPayload(_ sample: HKSample) -> (kind: String, value: Double, unit: String)? {
        guard let sample = sample as? HKQuantitySample else { return nil }
        let identifier = sample.quantityType.identifier
        switch identifier {
        case HKQuantityTypeIdentifier.heartRate.rawValue,
             HKQuantityTypeIdentifier.restingHeartRate.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: HKUnit.count().unitDivided(by: .minute())), "count/min")
        case HKQuantityTypeIdentifier.respiratoryRate.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: HKUnit.count().unitDivided(by: .minute())), "count/min")
        case HKQuantityTypeIdentifier.activeEnergyBurned.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: .kilocalorie()), "kcal")
        case HKQuantityTypeIdentifier.distanceWalkingRunning.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: .meter()), "m")
        case HKQuantityTypeIdentifier.appleExerciseTime.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: .minute()), "min")
        case HKQuantityTypeIdentifier.environmentalAudioExposure.rawValue,
             HKQuantityTypeIdentifier.headphoneAudioExposure.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: .decibelAWeightedSoundPressureLevel()), "dBASPL")
        case HKQuantityTypeIdentifier.bodyMass.rawValue:
            return ("quantity", sample.quantity.doubleValue(for: HKUnit.gramUnit(with: .kilo)), "kg")
        case HKQuantityTypeIdentifier.vo2Max.rawValue:
            let unit = HKUnit.literUnit(with: .milli)
                .unitDivided(by: HKUnit.gramUnit(with: .kilo))
                .unitDivided(by: .minute())
            return ("quantity", sample.quantity.doubleValue(for: unit), "mL/kg/min")
        default:
            return ("quantity", sample.quantity.doubleValue(for: .count()), "count")
        }
    }

    nonisolated private static func metadataJSONString(_ metadata: [String: Any]?) -> String? {
        guard let metadata, !metadata.isEmpty else { return nil }
        let sanitized = metadata.mapValues(sanitize)
        guard JSONSerialization.isValidJSONObject(sanitized),
              let data = try? JSONSerialization.data(withJSONObject: sanitized, options: [.sortedKeys]) else {
            return nil
        }
        return String(data: data, encoding: .utf8)
    }

    nonisolated private static func sanitize(_ value: Any) -> Any {
        switch value {
        case let value as String:
            return value
        case let value as NSNumber:
            return value
        case let value as Date:
            return ISO8601DateFormatter().string(from: value)
        case let value as UUID:
            return value.uuidString
        case let values as [Any]:
            return values.map(sanitize)
        case let values as [String: Any]:
            return values.mapValues(sanitize)
        default:
            return String(describing: value)
        }
    }

    nonisolated private static func operatingSystemVersionString(_ version: OperatingSystemVersion) -> String {
        "\(version.majorVersion).\(version.minorVersion).\(version.patchVersion)"
    }
}

nonisolated private struct HealthArchiveReportPayload: Encodable {
    let reportID: UUID
    let title: String
    let exportedAt: Date
    let records: [ArchivedHealthRecord]
}

nonisolated private extension JSONEncoder {
    static var healthArchive: JSONEncoder {
        let encoder = JSONEncoder()
        encoder.dateEncodingStrategy = .iso8601
        encoder.outputFormatting = [.prettyPrinted, .sortedKeys]
        return encoder
    }
}

nonisolated private func bindText(_ value: String?, to index: Int32, in statement: OpaquePointer?) {
    guard let value else {
        sqlite3_bind_null(statement, index)
        return
    }
    sqlite3_bind_text(statement, index, value, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))
}

nonisolated private func bindDouble(_ value: Double?, to index: Int32, in statement: OpaquePointer?) {
    guard let value else {
        sqlite3_bind_null(statement, index)
        return
    }
    sqlite3_bind_double(statement, index, value)
}

nonisolated private func bindInt(_ value: Int?, to index: Int32, in statement: OpaquePointer?) {
    guard let value else {
        sqlite3_bind_null(statement, index)
        return
    }
    sqlite3_bind_int64(statement, index, sqlite3_int64(value))
}

nonisolated private func columnText(_ statement: OpaquePointer?, _ index: Int32) -> String? {
    guard sqlite3_column_type(statement, index) != SQLITE_NULL,
          let pointer = sqlite3_column_text(statement, index) else {
        return nil
    }
    return String(cString: pointer)
}

nonisolated private func columnDate(_ statement: OpaquePointer?, _ index: Int32) -> Date? {
    guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
    return Date(timeIntervalSinceReferenceDate: sqlite3_column_double(statement, index))
}

nonisolated private func lastErrorMessage(_ db: OpaquePointer?) -> String {
    guard let message = sqlite3_errmsg(db) else { return "unknown SQLite error" }
    return String(cString: message)
}
