import Foundation
import GRDB

public final class PromptRepository: Sendable {
    public let databaseURL: URL?
    public let isReadOnly: Bool

    private let database: DatabaseQueue
    private let activityLogger: ActivityLogger?
    private let supportsPromptedAt: Bool

    public init(
        databaseURL: URL,
        readOnly: Bool = false,
        activityLogger: ActivityLogger? = nil
    ) throws {
        let databaseAlreadyExists = FileManager.default.fileExists(
            atPath: databaseURL.path
        )
        if !readOnly {
            try FileManager.default.createDirectory(
                at: databaseURL.deletingLastPathComponent(),
                withIntermediateDirectories: true
            )
        }

        var configuration = Configuration()
        configuration.label = "PromptGarage"
        configuration.readonly = readOnly
        configuration.busyMode = .timeout(5)
        if !readOnly {
            configuration.journalMode = .wal
        }

        let database = try DatabaseQueue(
            path: databaseURL.path,
            configuration: configuration
        )

        if !readOnly {
            if databaseAlreadyExists {
                // Do not silently turn an arbitrary/invalid existing SQLite
                // file into an app database. Existing app copies may receive
                // the nullable native migration and an FTS rebuild only after
                // their legacy structure and row parity have passed.
                _ = try DatabaseSchema.validateStructureAndCounts(database)
                try database.write { db in
                    try DatabaseSchema.migrateDestinationIfNeeded(in: db)
                    try DatabaseSchema.repairSearchIndexIfNeeded(in: db)
                }
            } else {
                try database.write { db in
                    try DatabaseSchema.createIfNeeded(in: db)
                }
            }
        }

        let validation = readOnly
            ? try DatabaseSchema.validateReadOnly(database)
            : try DatabaseSchema.validate(database)
        self.databaseURL = databaseURL
        self.isReadOnly = readOnly
        self.database = database
        self.supportsPromptedAt = validation.hasPromptedAt

        if let activityLogger {
            self.activityLogger = activityLogger
        } else if !readOnly {
            self.activityLogger = try? ActivityLogger(
                logURL: databaseURL
                    .deletingLastPathComponent()
                    .appendingPathComponent("activity.jsonl")
            )
        } else {
            self.activityLogger = nil
        }
    }

    private init(
        database: DatabaseQueue,
        databaseURL: URL?,
        isReadOnly: Bool,
        activityLogger: ActivityLogger?
    ) throws {
        if !isReadOnly {
            try database.write { db in
                try DatabaseSchema.createIfNeeded(in: db)
            }
        }
        let validation = isReadOnly
            ? try DatabaseSchema.validateReadOnly(database)
            : try DatabaseSchema.validate(database)

        self.database = database
        self.databaseURL = databaseURL
        self.isReadOnly = isReadOnly
        self.activityLogger = activityLogger
        self.supportsPromptedAt = validation.hasPromptedAt
    }

    public static func inMemory() throws -> PromptRepository {
        var configuration = Configuration()
        configuration.label = "PromptGarage.inMemory"
        let database = try DatabaseQueue(configuration: configuration)
        return try PromptRepository(
            database: database,
            databaseURL: nil,
            isReadOnly: false,
            activityLogger: nil
        )
    }

    public func prompt(id: Int64) throws -> PromptRecord? {
        try database.read { db in
            try Self.fetchPrompt(id: id, in: db)
        }
    }

    public func page(matching query: PromptQuery = PromptQuery()) throws -> PromptPage {
        try database.read { db in
            let topicKey = try Self.requestedTopicKey(query.tag)
            var databaseQuery = query
            databaseQuery.tag = nil
            let built = try buildQuery(databaseQuery)

            if let topicKey {
                let prompts = try Self.fetchAll(in: db, built: built)
                    .filter { Self.hasTopic($0.tags, matching: topicKey) }
                let page = Array(
                    prompts
                        .dropFirst(query.offset)
                        .prefix(query.limit)
                )
                return PromptPage(prompts: page, total: prompts.count)
            }

            let total = try Self.fetchCount(in: db, built: built)

            var arguments = built.arguments
            _ = arguments.append(contentsOf: [query.limit, query.offset])
            let sql = """
                SELECT p.*
                FROM prompts AS p
                \(built.joinSQL)
                \(built.whereSQL)
                ORDER BY \(built.orderSQL)
                LIMIT ? OFFSET ?
                """
            let prompts = try PromptRecord.fetchAll(db, sql: sql, arguments: arguments)
            return PromptPage(prompts: prompts, total: total)
        }
    }

    public func page(
        matching query: PromptQuery = PromptQuery(),
        promptedOn day: String?
    ) throws -> PromptPage {
        var query = query
        query.promptedDay = day
        return try page(matching: query)
    }

    public func count(matching query: PromptQuery = PromptQuery()) throws -> Int {
        try database.read { db in
            let topicKey = try Self.requestedTopicKey(query.tag)
            var databaseQuery = query
            databaseQuery.tag = nil
            let built = try buildQuery(databaseQuery)
            if let topicKey {
                return try Self.fetchAll(in: db, built: built)
                    .lazy
                    .filter { Self.hasTopic($0.tags, matching: topicKey) }
                    .count
            }
            return try Self.fetchCount(in: db, built: built)
        }
    }

    public func count(
        matching query: PromptQuery = PromptQuery(),
        promptedOn day: String?
    ) throws -> Int {
        var query = query
        query.promptedDay = day
        return try count(matching: query)
    }

    public func create(_ draft: PromptDraft) throws -> PromptRecord {
        try ensureWritable()
        try validate(draft)

        return try database.write { db in
            try db.execute(
                sql: """
                    INSERT INTO prompts (
                        title, content, source, tags, model, rating, is_starred,
                        prompted_at
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))
                    """,
                arguments: [
                    draft.title,
                    draft.content,
                    draft.source,
                    draft.tags,
                    draft.model,
                    draft.rating,
                    draft.isStarred,
                ]
            )
            return try Self.requirePrompt(id: db.lastInsertedRowID, in: db)
        }
    }

    public func update(id: Int64, with draft: PromptDraft) throws -> PromptRecord {
        try ensureWritable()
        try validate(draft)

        return try database.write { db in
            try db.execute(
                sql: """
                    UPDATE prompts
                    SET title = ?,
                        content = ?,
                        source = ?,
                        tags = ?,
                        model = ?,
                        rating = ?,
                        is_starred = ?,
                        updated_at = datetime('now')
                    WHERE id = ?
                    """,
                arguments: [
                    draft.title,
                    draft.content,
                    draft.source,
                    draft.tags,
                    draft.model,
                    draft.rating,
                    draft.isStarred,
                    id,
                ]
            )
            guard db.changesCount > 0 else {
                throw PromptRepositoryError.promptNotFound(id)
            }
            return try Self.requirePrompt(id: id, in: db)
        }
    }

    @discardableResult
    public func delete(id: Int64) throws -> Bool {
        try ensureWritable()

        let deletedSourceFamily: String? = try database.write { db in
            guard let prompt = try Self.fetchPrompt(id: id, in: db) else {
                return nil
            }
            try db.execute(sql: "DELETE FROM prompts WHERE id = ?", arguments: [id])
            return prompt.sourceFamily
        }

        guard let deletedSourceFamily else { return false }
        // Audit logging is intentionally best-effort after the database commit:
        // a log I/O failure must not make a successful delete look rolled back.
        try? activityLogger?.logDeletion(
            promptID: id,
            sourceFamily: deletedSourceFamily
        )
        return true
    }

    public func setStarred(_ isStarred: Bool, for id: Int64) throws -> PromptRecord {
        try ensureWritable()
        return try mutatePrompt(
            id: id,
            sql: """
                UPDATE prompts
                SET is_starred = ?, updated_at = datetime('now')
                WHERE id = ?
                """,
            arguments: [isStarred, id]
        )
    }

    public func toggleStar(id: Int64) throws -> PromptRecord {
        try ensureWritable()
        return try mutatePrompt(
            id: id,
            sql: """
                UPDATE prompts
                SET is_starred = 1 - is_starred, updated_at = datetime('now')
                WHERE id = ?
                """,
            arguments: [id]
        )
    }

    public func setRating(_ rating: Int?, for id: Int64) throws -> PromptRecord {
        try ensureWritable()
        if let rating, !(1...5).contains(rating) {
            throw PromptRepositoryError.invalidRating(rating)
        }
        return try mutatePrompt(
            id: id,
            sql: """
                UPDATE prompts
                SET rating = ?, updated_at = datetime('now')
                WHERE id = ?
                """,
            arguments: [rating, id]
        )
    }

    public func markUsed(id: Int64) throws -> PromptRecord {
        try ensureWritable()
        return try mutatePrompt(
            id: id,
            sql: """
                UPDATE prompts
                SET use_count = use_count + 1, updated_at = datetime('now')
                WHERE id = ?
                """,
            arguments: [id]
        )
    }

    public func statistics() throws -> PromptStatistics {
        try database.read { db in
            let totals = try Row.fetchOne(
                db,
                sql: """
                    SELECT COUNT(*) AS total,
                           COALESCE(SUM(CASE WHEN is_starred = 1 THEN 1 ELSE 0 END), 0) AS starred,
                           COALESCE(SUM(use_count), 0) AS total_uses
                    FROM prompts
                    """
            )

            let sourceFamilies = try Self.fetchSourceFamilies(in: db)
            let models = try Self.fetchModels(in: db)
            let tags = try Self.fetchTopics(in: db)
            let ratingRows = try Row.fetchAll(
                db,
                sql: """
                    SELECT COALESCE(rating, 0) AS rating, COUNT(*) AS count
                    FROM prompts
                    GROUP BY COALESCE(rating, 0)
                    """
            )

            var ratingDistribution = Dictionary(
                uniqueKeysWithValues: (0...5).map { ($0, 0) }
            )
            for row in ratingRows {
                let rating: Int = row["rating"]
                let count: Int = row["count"]
                ratingDistribution[rating] = count
            }

            return PromptStatistics(
                total: totals?["total"] ?? 0,
                starred: totals?["starred"] ?? 0,
                totalUses: totals?["total_uses"] ?? 0,
                sourceFamilies: sourceFamilies,
                models: models,
                tags: tags,
                ratingDistribution: ratingDistribution
            )
        }
    }

    public func allTags() throws -> [CountedValue] {
        try topicGroups()
    }

    public func topicGroups() throws -> [CountedValue] {
        try database.read { db in
            try Self.fetchTopics(in: db)
        }
    }

    public func browsingTopicGroups() throws -> [PromptTopicSummary] {
        try database.read { db in
            var groups = try Self.fetchTopics(in: db).map {
                PromptTopicSummary(topic: $0.name, count: $0.count)
            }
            let untaggedCount = try Self.fetchUntaggedCount(in: db)
            if untaggedCount > 0 {
                groups.append(PromptTopicSummary(topic: nil, count: untaggedCount))
            }
            return groups
        }
    }

    public func sourceFamilies() throws -> [CountedValue] {
        try database.read { db in
            try Self.fetchSourceFamilies(in: db)
        }
    }

    public func dayGroups() throws -> [CountedValue] {
        guard supportsPromptedAt else { return [] }
        return try database.read { db in
            let rows = try Row.fetchAll(
                db,
                sql: """
                    SELECT date(prompted_at, 'localtime') AS name, COUNT(*) AS count
                    FROM prompts
                    WHERE prompted_at IS NOT NULL AND trim(prompted_at) != ''
                      AND date(prompted_at, 'localtime') IS NOT NULL
                    GROUP BY date(prompted_at, 'localtime')
                    ORDER BY name DESC
                    """
            )
            return rows.map {
                CountedValue(name: $0["name"], count: $0["count"])
            }
        }
    }

    public func legacyUndatedCount() throws -> Int {
        guard supportsPromptedAt else {
            return try database.read { db in
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? 0
            }
        }
        return try database.read { db in
            try Int.fetchOne(
                db,
                sql: """
                    SELECT COUNT(*)
                    FROM prompts
                    WHERE prompted_at IS NULL
                       OR trim(prompted_at) = ''
                       OR date(prompted_at, 'localtime') IS NULL
                    """
            ) ?? 0
        }
    }

    public func makeDedupIndex() throws -> DedupIndex {
        try database.read { db in
            try Self.fetchDedupIndex(in: db)
        }
    }

    public func importCandidates(
        _ candidates: [ImportCandidate],
        baseStatistics: ImportStatistics = ImportStatistics()
    ) throws -> ImportStatistics {
        try ensureWritable()

        let result: (statistics: ImportStatistics, sourceFamilies: Set<String>) =
            try database.write { db in
                var statistics = baseStatistics
                // Preview parsers use `imported` as "eligible candidates".
                // The commit report must instead contain the number actually
                // inserted after the final, transaction-local dedup check.
                statistics.imported = 0
                var dedup = try Self.fetchDedupIndex(in: db)
                var exactContents = Set(dedup.existingContents.map(ImportText.trimmed))
                var sourceFamilies = Set<String>()
                // One fallback per transaction keeps every new import dated
                // while giving candidates without a usable source timestamp
                // one stable commit time. Existing rows are never updated.
                let fallbackPromptedAt = ImportTimestamp.now()

                for candidate in candidates {
                    let normalizedContent = ImportText.trimmed(candidate.content)
                    guard !candidate.title.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty,
                          !normalizedContent.isEmpty
                    else {
                        statistics.skippedMalformed += 1
                        continue
                    }

                    let hash = Self.dedupHash(from: candidate.source)
                    let isDuplicate = exactContents.contains(normalizedContent)
                        || hash.map { dedup.contains($0) } == true
                    if isDuplicate {
                        statistics.skippedDuplicate += 1
                        continue
                    }

                    // Parsers normally provide a normalized ISO-8601 value.
                    // Direct callers may provide nil, blank, or malformed
                    // values; all of those use the transaction-stable fallback
                    // so newly committed imports are never undated.
                    let promptedAt = candidate.promptedAt
                        .flatMap { ImportTimestamp.normalize($0) }
                        ?? fallbackPromptedAt
                    try db.execute(
                        sql: """
                            INSERT INTO prompts (
                                title, content, source, tags, model, prompted_at
                            )
                            VALUES (?, ?, ?, ?, ?, ?)
                            """,
                        arguments: [
                            candidate.title,
                            candidate.content,
                            candidate.source,
                            candidate.tags,
                            candidate.model,
                            promptedAt,
                        ]
                    )

                    statistics.imported += 1
                    exactContents.insert(normalizedContent)
                    if let hash {
                        dedup.hashes.insert(hash)
                    }
                    sourceFamilies.insert(Self.sourceFamily(of: candidate.source))
                }

                return (statistics, sourceFamilies)
            }

        try? activityLogger?.logImport(
            statistics: result.statistics,
            sourceFamilies: Array(result.sourceFamilies)
        )
        return result.statistics
    }

    private func mutatePrompt(
        id: Int64,
        sql: String,
        arguments: StatementArguments
    ) throws -> PromptRecord {
        try database.write { db in
            try db.execute(sql: sql, arguments: arguments)
            guard db.changesCount > 0 else {
                throw PromptRepositoryError.promptNotFound(id)
            }
            return try Self.requirePrompt(id: id, in: db)
        }
    }

    private func buildQuery(_ query: PromptQuery) throws -> BuiltQuery {
        var clauses: [String] = []
        var arguments: [(any DatabaseValueConvertible)?] = []

        let ftsQuery = Self.ftsPrefixQuery(query.text)
        let hasText = !ftsQuery.isEmpty
        if hasText {
            clauses.append("prompts_fts MATCH ?")
            arguments.append(ftsQuery)
        }

        if query.untaggedOnly {
            clauses.append("""
                trim(
                    replace(COALESCE(p.tags, ''), ',', ''),
                    char(9) || char(10) || char(13) || ' '
                ) = ''
                """)
        }

        if let rawFamily = query.sourceFamily?
            .trimmingCharacters(in: .whitespacesAndNewlines),
           !rawFamily.isEmpty
        {
            if rawFamily.caseInsensitiveCompare("unknown") == .orderedSame {
                clauses.append("(p.source IS NULL OR trim(p.source) = '')")
            } else {
                clauses.append("""
                    (lower(p.source) = lower(?) OR lower(p.source) LIKE lower(?) ESCAPE '\\')
                    """)
                arguments.append(rawFamily)
                arguments.append("\(Self.escapeLike(rawFamily)):%")
            }
        }

        if let rawDay = query.promptedDay?
            .trimmingCharacters(in: .whitespacesAndNewlines),
           !rawDay.isEmpty
        {
            let day = try Self.validatedDay(rawDay)
            if supportsPromptedAt {
                clauses.append("date(p.prompted_at, 'localtime') = ?")
                arguments.append(day)
            } else {
                clauses.append("0 = 1")
            }
        }

        if query.starredOnly {
            clauses.append("p.is_starred = 1")
        }
        if query.usedOnly {
            clauses.append("p.use_count > 0")
        }

        let requestedOrder: String
        switch query.sort {
        case .updatedDescending:
            requestedOrder = "p.updated_at DESC, p.id DESC"
        case .titleAscending:
            requestedOrder = "p.title COLLATE NOCASE ASC, p.id ASC"
        case .mostUsed:
            requestedOrder = "p.use_count DESC, p.updated_at DESC, p.id DESC"
        }

        return BuiltQuery(
            joinSQL: hasText ? "JOIN prompts_fts ON prompts_fts.rowid = p.id" : "",
            whereSQL: clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))",
            orderSQL: hasText
                ? "bm25(prompts_fts) ASC, \(requestedOrder)"
                : requestedOrder,
            arguments: StatementArguments(arguments)
        )
    }

    private func validate(_ draft: PromptDraft) throws {
        if let message = draft.validationMessage {
            throw PromptRepositoryError.invalidDraft(message)
        }
    }

    private func ensureWritable() throws {
        guard !isReadOnly else { throw PromptRepositoryError.readOnly }
    }

    private static func fetchPrompt(id: Int64, in db: Database) throws -> PromptRecord? {
        try PromptRecord.fetchOne(
            db,
            sql: "SELECT * FROM prompts WHERE id = ?",
            arguments: [id]
        )
    }

    private static func requirePrompt(id: Int64, in db: Database) throws -> PromptRecord {
        guard let prompt = try fetchPrompt(id: id, in: db) else {
            throw PromptRepositoryError.promptNotFound(id)
        }
        return prompt
    }

    private static func fetchCount(in db: Database, built: BuiltQuery) throws -> Int {
        try Int.fetchOne(
            db,
            sql: """
                SELECT COUNT(*)
                FROM prompts AS p
                \(built.joinSQL)
                \(built.whereSQL)
                """,
            arguments: built.arguments
        ) ?? 0
    }

    private static func fetchAll(
        in db: Database,
        built: BuiltQuery
    ) throws -> [PromptRecord] {
        try PromptRecord.fetchAll(
            db,
            sql: """
                SELECT p.*
                FROM prompts AS p
                \(built.joinSQL)
                \(built.whereSQL)
                ORDER BY \(built.orderSQL)
                """,
            arguments: built.arguments
        )
    }

    private static func fetchSourceFamilies(in db: Database) throws -> [CountedValue] {
        let rows = try Row.fetchAll(
            db,
            sql: """
                SELECT CASE
                           WHEN source IS NULL OR trim(source) = '' THEN 'unknown'
                           WHEN instr(source, ':') > 0
                               THEN substr(source, 1, instr(source, ':') - 1)
                           ELSE source
                       END AS name,
                       COUNT(*) AS count
                FROM prompts
                GROUP BY name COLLATE NOCASE
                ORDER BY count DESC, name COLLATE NOCASE ASC
                """
        )
        return rows.map {
            CountedValue(name: $0["name"], count: $0["count"])
        }
    }

    private static func fetchModels(in db: Database) throws -> [CountedValue] {
        let rows = try Row.fetchAll(
            db,
            sql: """
                SELECT trim(model) AS name, COUNT(*) AS count
                FROM prompts
                WHERE model IS NOT NULL AND trim(model) != ''
                GROUP BY trim(model) COLLATE NOCASE
                ORDER BY count DESC, name COLLATE NOCASE ASC
                """
        )
        return rows.map {
            CountedValue(name: $0["name"], count: $0["count"])
        }
    }

    private static func fetchTopics(in db: Database) throws -> [CountedValue] {
        let rows = try String.fetchAll(
            db,
            sql: "SELECT tags FROM prompts WHERE tags IS NOT NULL AND trim(tags) != ''"
        )
        let locale = Locale(identifier: "en_US_POSIX")
        var counts: [String: (displayName: String, count: Int)] = [:]
        for tags in rows {
            var seenInPrompt = Set<String>()
            for topic in tags.split(separator: ",") {
                let name = topic.trimmingCharacters(in: .whitespacesAndNewlines)
                guard !name.isEmpty else { continue }
                guard let key = topicKey(name, locale: locale) else { continue }
                guard seenInPrompt.insert(key).inserted else { continue }
                if let existing = counts[key] {
                    counts[key] = (existing.displayName, existing.count + 1)
                } else {
                    counts[key] = (name, 1)
                }
            }
        }
        return counts
            .map { CountedValue(name: $0.value.displayName, count: $0.value.count) }
            .sorted {
                if $0.count != $1.count { return $0.count > $1.count }
                return $0.name.localizedCaseInsensitiveCompare($1.name) == .orderedAscending
            }
    }

    private static func fetchUntaggedCount(in db: Database) throws -> Int {
        let rows = try Row.fetchAll(db, sql: "SELECT tags FROM prompts")
        return rows.reduce(into: 0) { count, row in
            let tags: String? = row["tags"]
            let hasTopic = tags?
                .split(separator: ",")
                .contains { !$0.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty }
                ?? false
            if !hasTopic { count += 1 }
        }
    }

    /// Topic grouping and filtering share this exact Unicode normalization.
    /// This runs entirely in memory and never rewrites legacy tag strings.
    private static func topicKey(
        _ raw: String,
        locale: Locale = Locale(identifier: "en_US_POSIX")
    ) -> String? {
        let name = raw.trimmingCharacters(in: .whitespacesAndNewlines)
        guard !name.isEmpty else { return nil }
        return name
            .folding(options: [.caseInsensitive], locale: locale)
            .lowercased()
    }

    private static func requestedTopicKey(_ raw: String?) throws -> String? {
        guard let raw else { return nil }
        let trimmed = raw.trimmingCharacters(in: .whitespacesAndNewlines)
        guard !trimmed.isEmpty else { return nil }
        guard !trimmed.contains(",") else {
            throw PromptRepositoryError.invalidQuery(
                "Ein Topic-Filter darf kein Komma enthalten."
            )
        }
        return topicKey(trimmed)
    }

    private static func hasTopic(_ rawTags: String?, matching key: String) -> Bool {
        guard let rawTags else { return false }
        return rawTags
            .split(separator: ",", omittingEmptySubsequences: false)
            .contains { topicKey(String($0)) == key }
    }

    private static func fetchDedupIndex(in db: Database) throws -> DedupIndex {
        let rows = try Row.fetchAll(db, sql: "SELECT source, content FROM prompts")
        var hashes = Set<String>()
        var contents: [String] = []
        contents.reserveCapacity(rows.count)

        for row in rows {
            let source: String? = row["source"]
            let content: String = row["content"]
            if let source, let hash = dedupHash(from: source) {
                hashes.insert(hash)
            }
            hashes.insert(ImportText.contentHash(ImportText.trimmed(content)))
            contents.append(content)
        }
        return DedupIndex(hashes: hashes, existingContents: contents)
    }

    private static func ftsPrefixQuery(_ input: String) -> String {
        input
            .split(whereSeparator: { $0.isWhitespace })
            .map { token in
                let escaped = token.replacingOccurrences(of: "\"", with: "\"\"")
                return "\"\(escaped)\"*"
            }
            .joined(separator: " AND ")
    }

    private static func escapeLike(_ input: String) -> String {
        input
            .replacingOccurrences(of: "\\", with: "\\\\")
            .replacingOccurrences(of: "%", with: "\\%")
            .replacingOccurrences(of: "_", with: "\\_")
    }

    private static func validatedDay(_ input: String) throws -> String {
        let parts = input.split(separator: "-", omittingEmptySubsequences: false)
        guard parts.count == 3,
              parts[0].count == 4,
              parts[1].count == 2,
              parts[2].count == 2,
              let year = Int(parts[0]),
              let month = Int(parts[1]),
              let day = Int(parts[2])
        else {
            throw PromptRepositoryError.invalidQuery(
                "Der Tagesfilter muss das Format YYYY-MM-DD haben."
            )
        }

        var calendar = Calendar(identifier: .gregorian)
        guard let utc = TimeZone(secondsFromGMT: 0) else {
            throw PromptRepositoryError.invalidQuery("UTC ist auf diesem System nicht verfügbar.")
        }
        calendar.timeZone = utc
        let components = DateComponents(
            calendar: calendar,
            timeZone: calendar.timeZone,
            year: year,
            month: month,
            day: day
        )
        guard let date = calendar.date(from: components) else {
            throw PromptRepositoryError.invalidQuery("Der Tagesfilter ist ungültig.")
        }
        let checked = calendar.dateComponents([.year, .month, .day], from: date)
        guard checked.year == year, checked.month == month, checked.day == day else {
            throw PromptRepositoryError.invalidQuery("Der Tagesfilter ist ungültig.")
        }
        return String(format: "%04d-%02d-%02d", year, month, day)
    }

    private static func dedupHash(from source: String) -> String? {
        guard let candidate = source.split(separator: ":").last else { return nil }
        let hash = candidate.lowercased()
        guard hash.utf8.count == 12,
              hash.utf8.allSatisfy({ byte in
                  (48...57).contains(byte) || (97...102).contains(byte)
              })
        else {
            return nil
        }
        return hash
    }

    private static func sourceFamily(of source: String) -> String {
        let family = source.split(separator: ":", maxSplits: 1).first.map(String.init)
        return family?.isEmpty == false ? family! : "unknown"
    }
}

public enum PromptRepositoryError: LocalizedError, Equatable, Sendable {
    case invalidDraft(String)
    case promptNotFound(Int64)
    case invalidRating(Int?)
    case invalidQuery(String)
    case readOnly

    public var errorDescription: String? {
        switch self {
        case let .invalidDraft(message):
            message
        case let .promptNotFound(id):
            "Prompt #\(id) wurde nicht gefunden."
        case let .invalidRating(rating):
            "Ungültige Bewertung \(rating.map(String.init) ?? "nil"); erlaubt sind 1 bis 5 oder keine Bewertung."
        case let .invalidQuery(message):
            message
        case .readOnly:
            "Die PromptGarage-Datenbank ist schreibgeschützt."
        }
    }
}

private struct BuiltQuery {
    let joinSQL: String
    let whereSQL: String
    let orderSQL: String
    let arguments: StatementArguments
}