import CoreData
import Foundation
import SQLite3

enum CoreDataArchiveCacheStoreError: Error {
    case persistentStoreLoadFailed(Error)
    case openArchiveFailed(String)
    case prepareFailed(String)
    case stepFailed(String)
}

struct CoreDataArchiveCacheRebuildSummary: Equatable, Sendable {
    let observationRows: Int
    let typeSummaryRows: Int
    let dailyAggregateRows: Int
    let diffSummaryRows: Int
    let exportManifestRows: Int
    let archiveHealthRows: Int
}

struct CachedArchiveObservationRow: Equatable, Identifiable, Sendable {
    let observationID: Int64
    let observedAt: Date
    let status: String
    let triggerReason: String
    let timeZoneIdentifier: String?
    let trackedTypeCount: Int
    let visibleRecordCount: Int
    let appearedCount: Int
    let disappearedCount: Int
    let representationChangedCount: Int
    let archiveSchemaVersion: Int
    let cacheSchemaVersion: Int
    let computedAt: Date

    var id: Int64 { observationID }
}

struct CachedArchiveTypeSummary: Equatable, Identifiable, Sendable {
    let observationID: Int64
    let sampleTypeIdentifier: String
    let displayName: String?
    let visibleRecordCount: Int
    let appearedCount: Int
    let disappearedCount: Int
    let representationChangedCount: Int
    let earliestStartDate: Date?
    let latestEndDate: Date?
    let valueSum: Double?
    let valueMax: Double?
    let aggregateHash: String?
    let computedAt: Date

    var id: String { "\(observationID)|\(sampleTypeIdentifier)" }
}

struct CachedArchiveDiffSummary: Equatable, Identifiable, Sendable {
    let fromObservationID: Int64
    let toObservationID: Int64
    let sampleTypeIdentifier: String?
    let appearedCount: Int
    let disappearedCount: Int
    let representationChangedCount: Int
    let consolidationLikely: Bool
    let uncertaintyReason: String?
    let computedAt: Date

    var id: String {
        "\(fromObservationID)|\(toObservationID)|\(sampleTypeIdentifier ?? "*")"
    }
}

struct CachedArchiveHealthStatus: Equatable, Sendable {
    let archiveSchemaVersion: Int
    let cacheSchemaVersion: Int
    let lastIntegrityCheckAt: Date
    let lastIntegrityStatus: String
    let lastErrorKind: String?
    let lastErrorMessageHash: String?
    let cacheBuildID: String
    let computedAt: Date
}

// Interface updated 2026-05-24 — see AGENTS.md
final class CoreDataArchiveCacheStore {
    static let cacheSchemaVersion = 1

    let container: NSPersistentContainer

    init(storeURL: URL? = nil, inMemory: Bool = false) throws {
        let model = Self.makeModel()
        container = NSPersistentContainer(name: "HealthProbeCache", managedObjectModel: model)

        let description = NSPersistentStoreDescription()
        if inMemory {
            description.type = NSInMemoryStoreType
        } else {
            let resolvedURL = storeURL ?? URL.applicationSupportDirectory.appending(path: "HealthProbeCache.sqlite")
            description.url = resolvedURL
        }
        description.shouldMigrateStoreAutomatically = true
        description.shouldInferMappingModelAutomatically = true
        container.persistentStoreDescriptions = [description]

        var loadError: Error?
        container.loadPersistentStores { _, error in
            loadError = error
        }
        if let loadError {
            throw CoreDataArchiveCacheStoreError.persistentStoreLoadFailed(loadError)
        }
        container.viewContext.mergePolicy = NSMergeByPropertyObjectTrumpMergePolicy
    }

    func rebuild(fromArchiveAt archiveURL: URL) throws -> CoreDataArchiveCacheRebuildSummary {
        let archive = try openArchive(at: archiveURL)
        defer { sqlite3_close(archive) }

        let archiveSchemaVersion = try archiveSchemaVersion(db: archive)
        let integrityStatus = try firstText("PRAGMA integrity_check", db: archive) ?? "missing"
        let computedAt = Date()
        let context = container.viewContext

        try resetCache(context: context)

        let observationCount = try insertObservationRows(db: archive, context: context, archiveSchemaVersion: archiveSchemaVersion, computedAt: computedAt)
        let typeSummaryCount = try insertTypeSummaryRows(db: archive, context: context, computedAt: computedAt)
        let dailyAggregateCount = try insertDailyAggregateRows(db: archive, context: context, computedAt: computedAt)
        let diffSummaryCount = try insertDiffSummaryRows(db: archive, context: context, computedAt: computedAt)
        let exportManifestCount = try insertExportManifestRows(db: archive, context: context, computedAt: computedAt)
        try insertArchiveHealthRow(
            context: context,
            archiveSchemaVersion: archiveSchemaVersion,
            integrityStatus: integrityStatus,
            computedAt: computedAt
        )

        if context.hasChanges {
            try context.save()
        }

        return CoreDataArchiveCacheRebuildSummary(
            observationRows: observationCount,
            typeSummaryRows: typeSummaryCount,
            dailyAggregateRows: dailyAggregateCount,
            diffSummaryRows: diffSummaryCount,
            exportManifestRows: exportManifestCount,
            archiveHealthRows: 1
        )
    }

    func deleteCache() throws {
        let context = container.viewContext
        try resetCache(context: context)
        if context.hasChanges {
            try context.save()
        }
    }

    func observationRows(limit: Int = 50) throws -> [CachedArchiveObservationRow] {
        let request = NSFetchRequest<NSManagedObject>(entityName: "CachedObservationRow")
        request.sortDescriptors = [NSSortDescriptor(key: "observationID", ascending: false)]
        request.fetchLimit = max(limit, 0)
        return try container.viewContext.fetch(request).map(Self.observationRow)
    }

    func observationCount() throws -> Int {
        let request = NSFetchRequest<NSManagedObject>(entityName: "CachedObservationRow")
        return try container.viewContext.count(for: request)
    }

    func latestObservationRow() throws -> CachedArchiveObservationRow? {
        try observationRows(limit: 1).first
    }

    func typeSummaries(observationID: Int64, limit: Int? = nil) throws -> [CachedArchiveTypeSummary] {
        let request = NSFetchRequest<NSManagedObject>(entityName: "CachedTypeSummary")
        request.predicate = NSPredicate(format: "observationID == %lld", observationID)
        request.sortDescriptors = [
            NSSortDescriptor(key: "displayName", ascending: true),
            NSSortDescriptor(key: "sampleTypeIdentifier", ascending: true)
        ]
        if let limit {
            request.fetchLimit = max(limit, 0)
        }
        return try container.viewContext.fetch(request).map(Self.typeSummary)
    }

    func diffSummary(
        fromObservationID: Int64,
        toObservationID: Int64,
        sampleTypeIdentifier: String?
    ) throws -> CachedArchiveDiffSummary? {
        let request = NSFetchRequest<NSManagedObject>(entityName: "CachedDiffSummary")
        if let sampleTypeIdentifier {
            request.predicate = NSPredicate(
                format: "fromObservationID == %lld AND toObservationID == %lld AND sampleTypeIdentifier == %@",
                fromObservationID,
                toObservationID,
                sampleTypeIdentifier
            )
        } else {
            request.predicate = NSPredicate(
                format: "fromObservationID == %lld AND toObservationID == %lld AND sampleTypeIdentifier == nil",
                fromObservationID,
                toObservationID
            )
        }
        request.fetchLimit = 1
        return try container.viewContext.fetch(request).first.map(Self.diffSummary)
    }

    func latestArchiveHealthStatus() throws -> CachedArchiveHealthStatus? {
        let request = NSFetchRequest<NSManagedObject>(entityName: "CachedArchiveHealth")
        request.sortDescriptors = [NSSortDescriptor(key: "computedAt", ascending: false)]
        request.fetchLimit = 1
        return try container.viewContext.fetch(request).first.map(Self.archiveHealthStatus)
    }

    private func resetCache(context: NSManagedObjectContext) throws {
        for entityName in Self.cacheEntityNames {
            let request = NSFetchRequest<NSManagedObject>(entityName: entityName)
            let objects = try context.fetch(request)
            for object in objects {
                context.delete(object)
            }
        }
    }

    private func insertObservationRows(
        db: OpaquePointer?,
        context: NSManagedObjectContext,
        archiveSchemaVersion: Int,
        computedAt: Date
    ) throws -> Int {
        let sql = """
        SELECT
            o.id,
            o.observed_at,
            o.status,
            o.trigger_reason,
            o.time_zone_identifier,
            COUNT(s.sample_type_id),
            COALESCE(SUM(s.visible_record_count), 0),
            COALESCE(SUM(s.appeared_count), 0),
            COALESCE(SUM(s.disappeared_count), 0),
            COALESCE(SUM(s.representation_changed_count), 0),
            COALESCE(GROUP_CONCAT(s.aggregate_hash, '|'), '')
        FROM observations o
        LEFT JOIN observation_type_summaries s ON s.observation_id = o.id
        GROUP BY o.id, o.observed_at, o.status, o.trigger_reason, o.time_zone_identifier
        ORDER BY o.id
        """
        return try withStatement(sql, db: db) { statement in
            var count = 0
            while sqlite3_step(statement) == SQLITE_ROW {
                let observationID = sqlite3_column_int64(statement, 0)
                let sourceHash = HashService.archiveContentHash(
                    domain: "hp:cache:observation_row",
                    parts: [
                        String(observationID),
                        columnText(statement, 10)
                    ]
                )
                let object = NSEntityDescription.insertNewObject(forEntityName: "CachedObservationRow", into: context)
                object.setValue(observationID, forKey: "observationID")
                object.setValue(columnUnixDate(statement, 1), forKey: "observedAt")
                object.setValue(columnText(statement, 2) ?? "unknown", forKey: "status")
                object.setValue(columnText(statement, 3) ?? "unknown", forKey: "triggerReason")
                object.setValue(columnText(statement, 4), forKey: "timeZoneIdentifier")
                object.setValue(Int64(sqlite3_column_int64(statement, 5)), forKey: "trackedTypeCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 6)), forKey: "visibleRecordCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 7)), forKey: "appearedCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 8)), forKey: "disappearedCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 9)), forKey: "representationChangedCount")
                object.setValue(Int64(archiveSchemaVersion), forKey: "archiveSchemaVersion")
                object.setValue(Int64(Self.cacheSchemaVersion), forKey: "cacheSchemaVersion")
                object.setValue(sourceHash, forKey: "sourceAggregateHash")
                object.setValue(computedAt, forKey: "computedAt")
                count += 1
            }
            return count
        }
    }

    private func insertTypeSummaryRows(
        db: OpaquePointer?,
        context: NSManagedObjectContext,
        computedAt: Date
    ) throws -> Int {
        let sql = """
        SELECT
            s.observation_id,
            t.type_identifier,
            t.display_name,
            s.visible_record_count,
            s.appeared_count,
            s.disappeared_count,
            s.representation_changed_count,
            s.earliest_start_date,
            s.latest_end_date,
            s.value_sum,
            s.value_max,
            s.aggregate_hash
        FROM observation_type_summaries s
        JOIN sample_types t ON t.id = s.sample_type_id
        ORDER BY s.observation_id, t.type_identifier
        """
        return try withStatement(sql, db: db) { statement in
            var count = 0
            while sqlite3_step(statement) == SQLITE_ROW {
                let object = NSEntityDescription.insertNewObject(forEntityName: "CachedTypeSummary", into: context)
                object.setValue(sqlite3_column_int64(statement, 0), forKey: "observationID")
                object.setValue(columnText(statement, 1) ?? "", forKey: "sampleTypeIdentifier")
                object.setValue(columnText(statement, 2), forKey: "displayName")
                object.setValue(Int64(sqlite3_column_int64(statement, 3)), forKey: "visibleRecordCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 4)), forKey: "appearedCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 5)), forKey: "disappearedCount")
                object.setValue(Int64(sqlite3_column_int64(statement, 6)), forKey: "representationChangedCount")
                object.setValue(columnUnixDate(statement, 7), forKey: "earliestStartDate")
                object.setValue(columnUnixDate(statement, 8), forKey: "latestEndDate")
                object.setValue(columnDouble(statement, 9), forKey: "valueSum")
                object.setValue(columnDouble(statement, 10), forKey: "valueMax")
                object.setValue(columnText(statement, 11), forKey: "aggregateHash")
                object.setValue(computedAt, forKey: "computedAt")
                count += 1
            }
            return count
        }
    }

    private func insertDailyAggregateRows(
        db: OpaquePointer?,
        context: NSManagedObjectContext,
        computedAt: Date
    ) throws -> Int {
        let sql = """
        SELECT
            d.observation_id,
            t.type_identifier,
            d.bucket_start,
            d.bucket_end,
            o.time_zone_identifier,
            d.visible_record_count,
            d.value_sum,
            d.value_max,
            sr.id,
            d.aggregate_hash
        FROM daily_type_aggregates d
        JOIN sample_types t ON t.id = d.sample_type_id
        JOIN observations o ON o.id = d.observation_id
        LEFT JOIN source_revisions sr ON sr.id = d.source_revision_id
        ORDER BY d.observation_id, t.type_identifier, d.bucket_start, sr.id
        """
        return try withStatement(sql, db: db) { statement in
            var count = 0
            while sqlite3_step(statement) == SQLITE_ROW {
                let sourceRevisionHash = columnInt64(statement, 8).map {
                    HashService.archiveContentHash(domain: "hp:cache:source_revision", parts: [String($0)])
                }
                let object = NSEntityDescription.insertNewObject(forEntityName: "CachedDailyAggregate", into: context)
                object.setValue(sqlite3_column_int64(statement, 0), forKey: "observationID")
                object.setValue(columnText(statement, 1) ?? "", forKey: "sampleTypeIdentifier")
                object.setValue(columnUnixDate(statement, 2), forKey: "bucketStart")
                object.setValue(columnUnixDate(statement, 3), forKey: "bucketEnd")
                object.setValue(columnText(statement, 4), forKey: "timeZoneIdentifier")
                object.setValue(Int64(sqlite3_column_int64(statement, 5)), forKey: "visibleRecordCount")
                object.setValue(columnDouble(statement, 6), forKey: "valueSum")
                object.setValue(columnDouble(statement, 7), forKey: "valueMax")
                object.setValue(sourceRevisionHash, forKey: "sourceRevisionDisplayHash")
                object.setValue(columnText(statement, 9), forKey: "aggregateHash")
                object.setValue(computedAt, forKey: "computedAt")
                count += 1
            }
            return count
        }
    }

    private func insertDiffSummaryRows(
        db: OpaquePointer?,
        context: NSManagedObjectContext,
        computedAt: Date
    ) throws -> Int {
        let sql = """
        WITH pairs AS (
            SELECT
                o.id AS to_id,
                (SELECT MAX(p.id) FROM observations p WHERE p.id < o.id) AS from_id
            FROM observations o
        )
        SELECT
            pairs.from_id,
            pairs.to_id,
            t.type_identifier,
            SUM(CASE WHEN e.event_kind = 'appeared' THEN 1 ELSE 0 END),
            SUM(CASE WHEN e.event_kind = 'disappeared' THEN 1 ELSE 0 END),
            SUM(CASE WHEN e.event_kind = 'representationChanged' THEN 1 ELSE 0 END)
        FROM pairs
        JOIN sample_observation_events e ON e.observation_id = pairs.to_id
        JOIN samples s ON s.id = e.sample_id
        JOIN sample_types t ON t.id = s.sample_type_id
        WHERE pairs.from_id IS NOT NULL
        GROUP BY pairs.from_id, pairs.to_id, t.type_identifier
        ORDER BY pairs.from_id, pairs.to_id, t.type_identifier
        """
        return try withStatement(sql, db: db) { statement in
            var count = 0
            while sqlite3_step(statement) == SQLITE_ROW {
                let fromObservationID = sqlite3_column_int64(statement, 0)
                let toObservationID = sqlite3_column_int64(statement, 1)
                let sampleTypeIdentifier = columnText(statement, 2) ?? ""
                let appearedCount = Int64(sqlite3_column_int64(statement, 3))
                let disappearedCount = Int64(sqlite3_column_int64(statement, 4))
                let representationChangedCount = Int64(sqlite3_column_int64(statement, 5))
                let sourceHash = HashService.archiveContentHash(
                    domain: "hp:cache:diff_summary",
                    parts: [
                        String(fromObservationID),
                        String(toObservationID),
                        sampleTypeIdentifier,
                        String(appearedCount),
                        String(disappearedCount),
                        String(representationChangedCount)
                    ]
                )
                let object = NSEntityDescription.insertNewObject(forEntityName: "CachedDiffSummary", into: context)
                object.setValue(fromObservationID, forKey: "fromObservationID")
                object.setValue(toObservationID, forKey: "toObservationID")
                object.setValue(sampleTypeIdentifier, forKey: "sampleTypeIdentifier")
                object.setValue(appearedCount, forKey: "appearedCount")
                object.setValue(disappearedCount, forKey: "disappearedCount")
                object.setValue(representationChangedCount, forKey: "representationChangedCount")
                object.setValue(false, forKey: "consolidationLikely")
                object.setValue(nil, forKey: "uncertaintyReason")
                object.setValue(sourceHash, forKey: "sourceAggregateHash")
                object.setValue(computedAt, forKey: "computedAt")
                count += 1
            }
            return count
        }
    }

    private func insertExportManifestRows(
        db: OpaquePointer?,
        context: NSManagedObjectContext,
        computedAt: Date
    ) throws -> Int {
        let sql = """
        SELECT
            export_id,
            export_kind,
            created_at,
            from_observation_id,
            to_observation_id,
            filter_json,
            record_count,
            manifest_hash
        FROM export_manifests
        ORDER BY created_at, id
        """
        return try withStatement(sql, db: db) { statement in
            var count = 0
            while sqlite3_step(statement) == SQLITE_ROW {
                let object = NSEntityDescription.insertNewObject(forEntityName: "CachedExportManifest", into: context)
                object.setValue(columnText(statement, 0) ?? "", forKey: "exportID")
                object.setValue(columnText(statement, 1) ?? "unknown", forKey: "exportKind")
                object.setValue(columnUnixDate(statement, 2), forKey: "createdAt")
                object.setValue(columnInt64(statement, 3), forKey: "fromObservationID")
                object.setValue(columnInt64(statement, 4), forKey: "toObservationID")
                object.setValue(columnText(statement, 5), forKey: "filterSummary")
                object.setValue(Int64(sqlite3_column_int64(statement, 6)), forKey: "recordCount")
                object.setValue(columnText(statement, 7) ?? "", forKey: "manifestHash")
                object.setValue(nil, forKey: "fileURLBookmarkData")
                object.setValue("available", forKey: "status")
                object.setValue(computedAt, forKey: "computedAt")
                count += 1
            }
            return count
        }
    }

    private func insertArchiveHealthRow(
        context: NSManagedObjectContext,
        archiveSchemaVersion: Int,
        integrityStatus: String,
        computedAt: Date
    ) throws {
        let object = NSEntityDescription.insertNewObject(forEntityName: "CachedArchiveHealth", into: context)
        object.setValue(Int64(archiveSchemaVersion), forKey: "archiveSchemaVersion")
        object.setValue(Int64(Self.cacheSchemaVersion), forKey: "cacheSchemaVersion")
        object.setValue(computedAt, forKey: "lastIntegrityCheckAt")
        object.setValue(integrityStatus, forKey: "lastIntegrityStatus")
        object.setValue(nil, forKey: "lastErrorKind")
        object.setValue(nil, forKey: "lastErrorMessageHash")
        object.setValue(UUID().uuidString, forKey: "cacheBuildID")
        object.setValue(computedAt, forKey: "computedAt")
    }
}

private extension CoreDataArchiveCacheStore {
    nonisolated static func observationRow(_ object: NSManagedObject) -> CachedArchiveObservationRow {
        CachedArchiveObservationRow(
            observationID: object.value(forKey: "observationID") as? Int64 ?? 0,
            observedAt: object.value(forKey: "observedAt") as? Date ?? Date(timeIntervalSince1970: 0),
            status: object.value(forKey: "status") as? String ?? "unknown",
            triggerReason: object.value(forKey: "triggerReason") as? String ?? "unknown",
            timeZoneIdentifier: object.value(forKey: "timeZoneIdentifier") as? String,
            trackedTypeCount: Int(object.value(forKey: "trackedTypeCount") as? Int64 ?? 0),
            visibleRecordCount: Int(object.value(forKey: "visibleRecordCount") as? Int64 ?? 0),
            appearedCount: Int(object.value(forKey: "appearedCount") as? Int64 ?? 0),
            disappearedCount: Int(object.value(forKey: "disappearedCount") as? Int64 ?? 0),
            representationChangedCount: Int(object.value(forKey: "representationChangedCount") as? Int64 ?? 0),
            archiveSchemaVersion: Int(object.value(forKey: "archiveSchemaVersion") as? Int64 ?? 0),
            cacheSchemaVersion: Int(object.value(forKey: "cacheSchemaVersion") as? Int64 ?? 0),
            computedAt: object.value(forKey: "computedAt") as? Date ?? Date(timeIntervalSince1970: 0)
        )
    }

    nonisolated static func typeSummary(_ object: NSManagedObject) -> CachedArchiveTypeSummary {
        CachedArchiveTypeSummary(
            observationID: object.value(forKey: "observationID") as? Int64 ?? 0,
            sampleTypeIdentifier: object.value(forKey: "sampleTypeIdentifier") as? String ?? "",
            displayName: object.value(forKey: "displayName") as? String,
            visibleRecordCount: Int(object.value(forKey: "visibleRecordCount") as? Int64 ?? 0),
            appearedCount: Int(object.value(forKey: "appearedCount") as? Int64 ?? 0),
            disappearedCount: Int(object.value(forKey: "disappearedCount") as? Int64 ?? 0),
            representationChangedCount: Int(object.value(forKey: "representationChangedCount") as? Int64 ?? 0),
            earliestStartDate: object.value(forKey: "earliestStartDate") as? Date,
            latestEndDate: object.value(forKey: "latestEndDate") as? Date,
            valueSum: object.value(forKey: "valueSum") as? Double,
            valueMax: object.value(forKey: "valueMax") as? Double,
            aggregateHash: object.value(forKey: "aggregateHash") as? String,
            computedAt: object.value(forKey: "computedAt") as? Date ?? Date(timeIntervalSince1970: 0)
        )
    }

    nonisolated static func diffSummary(_ object: NSManagedObject) -> CachedArchiveDiffSummary {
        CachedArchiveDiffSummary(
            fromObservationID: object.value(forKey: "fromObservationID") as? Int64 ?? 0,
            toObservationID: object.value(forKey: "toObservationID") as? Int64 ?? 0,
            sampleTypeIdentifier: object.value(forKey: "sampleTypeIdentifier") as? String,
            appearedCount: Int(object.value(forKey: "appearedCount") as? Int64 ?? 0),
            disappearedCount: Int(object.value(forKey: "disappearedCount") as? Int64 ?? 0),
            representationChangedCount: Int(object.value(forKey: "representationChangedCount") as? Int64 ?? 0),
            consolidationLikely: object.value(forKey: "consolidationLikely") as? Bool ?? false,
            uncertaintyReason: object.value(forKey: "uncertaintyReason") as? String,
            computedAt: object.value(forKey: "computedAt") as? Date ?? Date(timeIntervalSince1970: 0)
        )
    }

    nonisolated static func archiveHealthStatus(_ object: NSManagedObject) -> CachedArchiveHealthStatus {
        CachedArchiveHealthStatus(
            archiveSchemaVersion: Int(object.value(forKey: "archiveSchemaVersion") as? Int64 ?? 0),
            cacheSchemaVersion: Int(object.value(forKey: "cacheSchemaVersion") as? Int64 ?? 0),
            lastIntegrityCheckAt: object.value(forKey: "lastIntegrityCheckAt") as? Date ?? Date(timeIntervalSince1970: 0),
            lastIntegrityStatus: object.value(forKey: "lastIntegrityStatus") as? String ?? "unknown",
            lastErrorKind: object.value(forKey: "lastErrorKind") as? String,
            lastErrorMessageHash: object.value(forKey: "lastErrorMessageHash") as? String,
            cacheBuildID: object.value(forKey: "cacheBuildID") as? String ?? "",
            computedAt: object.value(forKey: "computedAt") as? Date ?? Date(timeIntervalSince1970: 0)
        )
    }
}

private extension CoreDataArchiveCacheStore {
    static let cacheEntityNames = [
        "CachedObservationRow",
        "CachedTypeSummary",
        "CachedDailyAggregate",
        "CachedDiffSummary",
        "CachedExportManifest",
        "CachedArchiveHealth"
    ]

    static func makeModel() -> NSManagedObjectModel {
        let model = NSManagedObjectModel()
        model.entities = [
            cachedObservationRowEntity(),
            cachedTypeSummaryEntity(),
            cachedDailyAggregateEntity(),
            cachedDiffSummaryEntity(),
            cachedExportManifestEntity(),
            cachedArchiveHealthEntity()
        ]
        return model
    }

    static func cachedObservationRowEntity() -> NSEntityDescription {
        entity("CachedObservationRow", attributes: [
            attribute("observationID", .integer64AttributeType),
            attribute("observedAt", .dateAttributeType),
            attribute("status", .stringAttributeType),
            attribute("triggerReason", .stringAttributeType),
            attribute("timeZoneIdentifier", .stringAttributeType, optional: true),
            attribute("trackedTypeCount", .integer64AttributeType),
            attribute("visibleRecordCount", .integer64AttributeType),
            attribute("appearedCount", .integer64AttributeType),
            attribute("disappearedCount", .integer64AttributeType),
            attribute("representationChangedCount", .integer64AttributeType),
            attribute("archiveSchemaVersion", .integer64AttributeType),
            attribute("cacheSchemaVersion", .integer64AttributeType),
            attribute("sourceAggregateHash", .stringAttributeType),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func cachedTypeSummaryEntity() -> NSEntityDescription {
        entity("CachedTypeSummary", attributes: [
            attribute("observationID", .integer64AttributeType),
            attribute("sampleTypeIdentifier", .stringAttributeType),
            attribute("displayName", .stringAttributeType, optional: true),
            attribute("visibleRecordCount", .integer64AttributeType),
            attribute("appearedCount", .integer64AttributeType),
            attribute("disappearedCount", .integer64AttributeType),
            attribute("representationChangedCount", .integer64AttributeType),
            attribute("earliestStartDate", .dateAttributeType, optional: true),
            attribute("latestEndDate", .dateAttributeType, optional: true),
            attribute("valueSum", .doubleAttributeType, optional: true),
            attribute("valueMax", .doubleAttributeType, optional: true),
            attribute("aggregateHash", .stringAttributeType, optional: true),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func cachedDailyAggregateEntity() -> NSEntityDescription {
        entity("CachedDailyAggregate", attributes: [
            attribute("observationID", .integer64AttributeType),
            attribute("sampleTypeIdentifier", .stringAttributeType),
            attribute("bucketStart", .dateAttributeType),
            attribute("bucketEnd", .dateAttributeType),
            attribute("timeZoneIdentifier", .stringAttributeType, optional: true),
            attribute("visibleRecordCount", .integer64AttributeType),
            attribute("valueSum", .doubleAttributeType, optional: true),
            attribute("valueMax", .doubleAttributeType, optional: true),
            attribute("sourceRevisionDisplayHash", .stringAttributeType, optional: true),
            attribute("aggregateHash", .stringAttributeType, optional: true),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func cachedDiffSummaryEntity() -> NSEntityDescription {
        entity("CachedDiffSummary", attributes: [
            attribute("fromObservationID", .integer64AttributeType),
            attribute("toObservationID", .integer64AttributeType),
            attribute("sampleTypeIdentifier", .stringAttributeType, optional: true),
            attribute("appearedCount", .integer64AttributeType),
            attribute("disappearedCount", .integer64AttributeType),
            attribute("representationChangedCount", .integer64AttributeType),
            attribute("consolidationLikely", .booleanAttributeType),
            attribute("uncertaintyReason", .stringAttributeType, optional: true),
            attribute("sourceAggregateHash", .stringAttributeType),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func cachedExportManifestEntity() -> NSEntityDescription {
        entity("CachedExportManifest", attributes: [
            attribute("exportID", .stringAttributeType),
            attribute("exportKind", .stringAttributeType),
            attribute("createdAt", .dateAttributeType),
            attribute("fromObservationID", .integer64AttributeType, optional: true),
            attribute("toObservationID", .integer64AttributeType, optional: true),
            attribute("filterSummary", .stringAttributeType, optional: true),
            attribute("recordCount", .integer64AttributeType),
            attribute("manifestHash", .stringAttributeType),
            attribute("fileURLBookmarkData", .binaryDataAttributeType, optional: true),
            attribute("status", .stringAttributeType),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func cachedArchiveHealthEntity() -> NSEntityDescription {
        entity("CachedArchiveHealth", attributes: [
            attribute("archiveSchemaVersion", .integer64AttributeType),
            attribute("cacheSchemaVersion", .integer64AttributeType),
            attribute("lastIntegrityCheckAt", .dateAttributeType),
            attribute("lastIntegrityStatus", .stringAttributeType),
            attribute("lastErrorKind", .stringAttributeType, optional: true),
            attribute("lastErrorMessageHash", .stringAttributeType, optional: true),
            attribute("cacheBuildID", .stringAttributeType),
            attribute("computedAt", .dateAttributeType)
        ])
    }

    static func entity(_ name: String, attributes: [NSAttributeDescription]) -> NSEntityDescription {
        let entity = NSEntityDescription()
        entity.name = name
        entity.managedObjectClassName = "NSManagedObject"
        entity.properties = attributes
        return entity
    }

    static func attribute(
        _ name: String,
        _ type: NSAttributeType,
        optional: Bool = false
    ) -> NSAttributeDescription {
        let attribute = NSAttributeDescription()
        attribute.name = name
        attribute.attributeType = type
        attribute.isOptional = optional
        return attribute
    }
}

private extension CoreDataArchiveCacheStore {
    func openArchive(at archiveURL: URL) throws -> OpaquePointer? {
        var db: OpaquePointer?
        guard sqlite3_open_v2(archiveURL.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
            let message = db.map(lastErrorMessage) ?? "unable to open archive database"
            sqlite3_close(db)
            throw CoreDataArchiveCacheStoreError.openArchiveFailed(message)
        }
        return db
    }

    func archiveSchemaVersion(db: OpaquePointer?) throws -> Int {
        let sql = "SELECT value FROM archive_metadata WHERE key = 'schema_version' LIMIT 1"
        return try withStatement(sql, db: db) { statement in
            guard sqlite3_step(statement) == SQLITE_ROW,
                  let value = columnText(statement, 0),
                  let version = Int(value) else {
                return 0
            }
            return version
        }
    }

    func firstText(_ sql: String, db: OpaquePointer?) throws -> String? {
        try withStatement(sql, db: db) { statement in
            guard sqlite3_step(statement) == SQLITE_ROW else {
                return nil
            }
            return columnText(statement, 0)
        }
    }

    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 CoreDataArchiveCacheStoreError.prepareFailed(lastErrorMessage(db))
        }
        defer { sqlite3_finalize(statement) }
        return try body(statement)
    }
}

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

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

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

private func columnInt64(_ statement: OpaquePointer?, _ index: Int32) -> Int64? {
    guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
    return sqlite3_column_int64(statement, index)
}

private func columnUnixDate(_ statement: OpaquePointer?, _ index: Int32) -> Date? {
    columnDouble(statement, index).map { Date(timeIntervalSince1970: $0) }
}
