import Foundation import GRDB import XCTest @testable import PromptGarageCore final class PromptRepositoryTests: XCTestCase { func testOffsetPaginationUsesDatabaseLimitAndOffset() throws { let repository = try PromptRepository.inMemory() for title in ["Alpha", "Bravo", "Charlie", "Delta", "Echo"] { _ = try repository.create(PromptDraft( title: title, content: "Unique content for the \(title) pagination fixture" )) } let middle = try repository.page(matching: PromptQuery( sort: .titleAscending, limit: 2, offset: 2 )) XCTAssertEqual(middle.total, 5) XCTAssertEqual(middle.prompts.map(\.title), ["Charlie", "Delta"]) let tail = try repository.page(matching: PromptQuery( sort: .titleAscending, limit: 2, offset: 4 )) XCTAssertEqual(tail.total, 5) XCTAssertEqual(tail.prompts.map(\.title), ["Echo"]) XCTAssertTrue(Set(middle.prompts.map(\.id)).isDisjoint(with: tail.prompts.map(\.id))) } func testStarredUsedAndSourceFamilyFiltersComposeAgainstStoredRows() throws { let repository = try PromptRepository.inMemory() let claudeStarredUsed = try repository.create(PromptDraft( title: "Claude starred used", content: "First unique content for filter coverage", source: "claude-raw:111111111111", isStarred: true )) _ = try repository.create(PromptDraft( title: "Claude plain", content: "Second unique content for filter coverage", source: "claude-raw:222222222222" )) let codexStarredUsed = try repository.create(PromptDraft( title: "Codex starred used", content: "Third unique content for filter coverage", source: "codex-raw:333333333333", isStarred: true )) let unknown = try repository.create(PromptDraft( title: "Unknown source", content: "Fourth unique content for filter coverage", source: "" )) _ = try repository.markUsed(id: claudeStarredUsed.id) _ = try repository.markUsed(id: codexStarredUsed.id) let starred = try repository.page(matching: PromptQuery(starredOnly: true)) XCTAssertEqual(starred.total, 2) XCTAssertEqual(Set(starred.prompts.map(\.id)), [claudeStarredUsed.id, codexStarredUsed.id]) let used = try repository.page(matching: PromptQuery(usedOnly: true)) XCTAssertEqual(used.total, 2) XCTAssertEqual(Set(used.prompts.map(\.id)), [claudeStarredUsed.id, codexStarredUsed.id]) let claude = try repository.page(matching: PromptQuery(sourceFamily: "claude-raw")) XCTAssertEqual(claude.total, 2) XCTAssertTrue(claude.prompts.allSatisfy { $0.sourceFamily == "claude-raw" }) let combined = try repository.page(matching: PromptQuery( sourceFamily: "claude-raw", starredOnly: true, usedOnly: true )) XCTAssertEqual(combined.prompts.map(\.id), [claudeStarredUsed.id]) XCTAssertEqual(combined.total, 1) let unknownSource = try repository.page(matching: PromptQuery(sourceFamily: "unknown")) XCTAssertEqual(unknownSource.prompts.map(\.id), [unknown.id]) XCTAssertEqual(unknownSource.total, 1) } func testStatisticsIncludeExactTotalsSourcesModelsTopicsAndRatings() throws { let repository = try PromptRepository.inMemory() let first = try repository.create(PromptDraft( title: "Manual unrated", content: "Statistics content zero", source: "manual", tags: "swift,testing", model: "gpt-5", rating: nil, isStarred: true )) let second = try repository.create(PromptDraft( title: "Manual one", content: "Statistics content one", source: "manual", tags: "swift", model: "gpt-5", rating: 1 )) _ = try repository.create(PromptDraft( title: "Manual two", content: "Statistics content two", source: "manual", rating: 2 )) _ = try repository.create(PromptDraft( title: "Claude three", content: "Statistics content three", source: "claude-raw:111111111111", model: "opus", rating: 3, isStarred: true )) _ = try repository.create(PromptDraft( title: "Claude four", content: "Statistics content four", source: "claude-raw:222222222222", model: "opus", rating: 4 )) _ = try repository.create(PromptDraft( title: "Codex five", content: "Statistics content five", source: "codex-raw:333333333333", rating: 5 )) _ = try repository.markUsed(id: first.id) _ = try repository.markUsed(id: first.id) _ = try repository.markUsed(id: second.id) let statistics = try repository.statistics() XCTAssertEqual(statistics.total, 6) XCTAssertEqual(statistics.starred, 2) XCTAssertEqual(statistics.totalUses, 3) XCTAssertEqual( statistics.sourceFamilies, [ CountedValue(name: "manual", count: 3), CountedValue(name: "claude-raw", count: 2), CountedValue(name: "codex-raw", count: 1), ] ) XCTAssertEqual( statistics.models, [CountedValue(name: "gpt-5", count: 2), CountedValue(name: "opus", count: 2)] ) XCTAssertEqual( statistics.tags, [CountedValue(name: "swift", count: 2), CountedValue(name: "testing", count: 1)] ) XCTAssertEqual(statistics.ratingDistribution, [0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1]) } func testImportBatchRollsBackAtomicallyWhenTriggerAbortsMidBatch() throws { let root = try makeTemporaryDirectory(prefix: "PromptGarageAtomicImport") let databaseURL = root.appendingPathComponent("prompts.db") let setup = try DatabaseQueue(path: databaseURL.path) try setup.write { db in try DatabaseSchema.createIfNeeded(in: db) try db.execute(sql: """ CREATE TRIGGER abort_test_candidate BEFORE INSERT ON prompts WHEN new.title = 'Abort candidate' BEGIN SELECT RAISE(ABORT, 'forced test rollback'); END; """) } try setup.close() let repository = try PromptRepository(databaseURL: databaseURL) XCTAssertThrowsError(try repository.importCandidates([ ImportCandidate( title: "First candidate", content: "First unique import candidate that must roll back with the batch", source: "codex-raw:aaaaaaaaaaaa", tags: "codex", model: nil, promptedAt: "2026-07-15T20:00:00.000Z" ), ImportCandidate( title: "Abort candidate", content: "Second unique import candidate deliberately aborts the transaction", source: "codex-raw:bbbbbbbbbbbb", tags: "codex", model: nil, promptedAt: "2026-07-15T20:01:00.000Z" ), ])) XCTAssertEqual(try repository.count(), 0) let validationQueue = try DatabaseQueue(path: databaseURL.path) defer { try? validationQueue.close() } let report = try DatabaseSchema.validate(validationQueue) XCTAssertEqual(report.promptCount, 0) XCTAssertEqual(report.ftsCount, 0) } func testDirectImportBatchDeduplicatesIdenticalContentTransactionLocally() throws { let root = try makeTemporaryDirectory(prefix: "PromptGarageBatchDedup") let databaseURL = root.appendingPathComponent("prompts.db") let setup = try DatabaseQueue(path: databaseURL.path) try setup.write { db in try DatabaseSchema.createIfNeeded(in: db) try db.execute( sql: """ INSERT INTO prompts (title, content, source, prompted_at) VALUES (?, ?, ?, NULL) """, arguments: [ "Legacy undated prompt", "This legacy row must remain undated when a new import batch commits", "legacy", ] ) } try setup.close() let repository = try PromptRepository(databaseURL: databaseURL) let duplicateContent = "Explain transaction local content deduplication for a direct native import batch" let statistics = try repository.importCandidates([ ImportCandidate( title: "First metadata wins", content: duplicateContent, source: "direct:aaaaaaaaaaaa", tags: "first", model: "model-a", promptedAt: nil ), ImportCandidate( title: "Different duplicate metadata", content: duplicateContent, source: "direct:bbbbbbbbbbbb", tags: "second", model: "model-b", promptedAt: "2025-01-02T03:04:05.000Z" ), ]) XCTAssertEqual(statistics.imported, 1) XCTAssertEqual(statistics.skippedDuplicate, 1) XCTAssertEqual(try repository.count(), 2) let persistedMatches = try repository.page().prompts.filter { $0.content == duplicateContent } let imported = try XCTUnwrap(persistedMatches.first) XCTAssertEqual(persistedMatches.count, 1) XCTAssertEqual(imported.title, "First metadata wins") XCTAssertEqual(imported.source, "direct:aaaaaaaaaaaa") XCTAssertEqual(imported.tags, "first") XCTAssertEqual(imported.model, "model-a") let promptedAt = try XCTUnwrap(imported.promptedAt) XCTAssertEqual(ImportTimestamp.normalize(promptedAt), promptedAt) XCTAssertNil(try repository.prompt(id: 1)?.promptedAt) XCTAssertEqual(try repository.legacyUndatedCount(), 1) } func testCRUDSearchAndManualChronology() throws { let repository = try PromptRepository.inMemory() let created = try repository.create(PromptDraft( title: "SQLite migration", content: "Design a safe SQLite migration for a native macOS prompt library", source: "manual", tags: "swift, sqlite", model: "gpt-test", rating: 4, isStarred: true )) XCTAssertNotNil(created.promptedAt) XCTAssertEqual(try repository.count(), 1) XCTAssertEqual(try repository.page(matching: PromptQuery(text: "migrat")).total, 1) XCTAssertEqual(try repository.page(matching: PromptQuery(tag: "sqlite")).total, 1) let updated = try repository.update( id: created.id, with: PromptDraft( title: "SQLite migration updated", content: created.content, source: created.source ?? "manual", tags: created.tags, model: created.model, rating: 5, isStarred: false ) ) XCTAssertEqual(updated.promptedAt, created.promptedAt) XCTAssertEqual(updated.rating, 5) let used = try repository.markUsed(id: created.id) XCTAssertEqual(used.useCount, 1) XCTAssertTrue(try repository.delete(id: created.id)) XCTAssertEqual(try repository.count(), 0) } func testTopicBrowsingReadsExistingCommaSeparatedTagsWithoutRewritingThem() throws { let repository = try PromptRepository.inMemory() let first = try repository.create(PromptDraft( title: "First", content: "A sufficiently long first prompt about local Swift database testing", tags: "Swift, testing" )) _ = try repository.create(PromptDraft( title: "Second", content: "A sufficiently long second prompt about local Swift interface design", tags: "swift, ui,swift" )) _ = try repository.create(PromptDraft( title: "Untagged", content: "A sufficiently long prompt that deliberately has no stored topic tags", tags: " , " )) let groups = try repository.topicGroups() XCTAssertEqual(groups.first(where: { $0.name == "Swift" })?.count, 2) XCTAssertEqual(groups.first(where: { $0.name == "testing" })?.count, 1) XCTAssertEqual(try repository.page(matching: PromptQuery(tag: "swift")).total, 2) XCTAssertEqual(try repository.page(matching: PromptQuery(tag: "ui")).total, 1) XCTAssertEqual(try repository.page(matching: PromptQuery(untaggedOnly: true)).total, 1) XCTAssertEqual(try repository.browsingTopicGroups().last?.displayName, "Ohne Tags") XCTAssertEqual(try repository.browsingTopicGroups().last?.count, 1) XCTAssertEqual(try repository.prompt(id: first.id)?.tags, "Swift, testing") } func testUnicodeTopicGroupingFilteringAndPaginationShareOneSemantics() throws { let repository = try PromptRepository.inMemory() let originalTags = "Straße, ÄPFEL" let first = try repository.create(PromptDraft( title: "Unicode one", content: "A first sufficiently descriptive prompt for Unicode topic matching", tags: originalTags )) _ = try repository.create(PromptDraft( title: "Unicode two", content: "A second sufficiently descriptive prompt for Unicode topic matching", tags: "STRASSE, äpfel" )) _ = try repository.create(PromptDraft( title: "Unicode three", content: "A third sufficiently descriptive prompt for Unicode topic matching", tags: "straße" )) _ = try repository.create(PromptDraft( title: "Different topic", content: "A fourth sufficiently descriptive prompt with another stored topic", tags: "macOS" )) let groups = try repository.topicGroups() XCTAssertEqual(groups.first(where: { $0.name == "Straße" })?.count, 3) XCTAssertEqual(groups.first(where: { $0.name == "ÄPFEL" })?.count, 2) XCTAssertEqual(try repository.count(matching: PromptQuery(tag: "strasse")), 3) XCTAssertEqual(try repository.count(matching: PromptQuery(tag: "äPFEL")), 2) let page = try repository.page(matching: PromptQuery( tag: "STRASSE", limit: 1, offset: 1 )) XCTAssertEqual(page.total, 3) XCTAssertEqual(page.prompts.count, 1) XCTAssertEqual(try repository.prompt(id: first.id)?.tags, originalTags) } func testMissingEmptyAndInvalidImportedTimestampsShareCommitFallback() throws { let repository = try PromptRepository.inMemory() let startedAt = Date() let statistics = try repository.importCandidates([ ImportCandidate( title: "Missing chronology", content: "This newly imported prompt deliberately has no source timestamp available", source: "test:111111111111", tags: "chronology", model: nil, promptedAt: nil ), ImportCandidate( title: "Empty chronology", content: "This newly imported prompt deliberately carries an empty source timestamp", source: "test:222222222222", tags: "chronology", model: nil, promptedAt: " " ), ImportCandidate( title: "Invalid chronology", content: "This newly imported prompt deliberately carries an invalid source timestamp", source: "test:333333333333", tags: "chronology", model: nil, promptedAt: "definitely-not-a-date" ), ]) let finishedAt = Date() XCTAssertEqual(statistics.imported, 3) let imported = try repository.page().prompts XCTAssertEqual(imported.count, 3) let promptedAts = try imported.map { try XCTUnwrap($0.promptedAt) } XCTAssertEqual(Set(promptedAts).count, 1) let fallback = try XCTUnwrap(promptedAts.first) XCTAssertEqual(ImportTimestamp.normalize(fallback), fallback) let formatter = ISO8601DateFormatter() formatter.formatOptions = [.withInternetDateTime, .withFractionalSeconds] let fallbackDate = try XCTUnwrap(formatter.date(from: fallback)) XCTAssertGreaterThanOrEqual( fallbackDate.timeIntervalSince1970, startedAt.timeIntervalSince1970 - 0.001 ) XCTAssertLessThanOrEqual( fallbackDate.timeIntervalSince1970, finishedAt.timeIntervalSince1970 + 0.001 ) XCTAssertEqual(try repository.legacyUndatedCount(), 0) XCTAssertEqual(try repository.dayGroups().reduce(0) { $0 + $1.count }, 3) } func testPreviewDedupHashesManualContentAndCommitCountsStayAligned() throws { let repository = try PromptRepository.inMemory() let existingContent = "Explain how a manual prompt already in the library is excluded from import preview" _ = try repository.create(PromptDraft( title: "Existing manual prompt", content: existingContent, source: "manual" )) let newContent = "Explain how a genuinely new prompt remains eligible through preview and commit" let root = try makeTemporaryDirectory(prefix: "PromptGarageManualDedup") let sourceURL = root.appendingPathComponent("analysis.json") let source = [ "cluster": "Code/Testing", "quotes_with_source": [ ["quote": existingContent], ["quote": newContent], ], ] as [String: Any] let data = try JSONSerialization.data(withJSONObject: source) try data.write(to: sourceURL) let dedupIndex = try repository.makeDedupIndex() XCTAssertTrue(dedupIndex.hashes.contains(ImportText.contentHash(existingContent))) let preview = try ImportEngine().preview( format: .chatGPTAnalysisJSON, sourceURL: sourceURL, dedupIndex: dedupIndex ) XCTAssertEqual(preview.candidates.map(\.content), [newContent]) XCTAssertEqual(preview.statistics.scanned, 2) XCTAssertEqual(preview.statistics.imported, 1) XCTAssertEqual(preview.statistics.skippedDuplicate, 1) let committed = try repository.importCandidates( preview.candidates, baseStatistics: preview.statistics ) XCTAssertEqual(committed.imported, preview.statistics.imported) XCTAssertEqual(committed.skippedDuplicate, preview.statistics.skippedDuplicate) XCTAssertEqual(try repository.count(), 2) } func testDedupDoesNotTreatHashSubstringInUnrelatedContentAsDuplicate() throws { let repository = try PromptRepository.inMemory() let incomingContent = "Describe an exact hash membership policy without substring based false positives" let incomingHash = ImportText.contentHash(incomingContent) _ = try repository.create(PromptDraft( title: "Unrelated checksum note", content: "This unrelated diagnostic note merely mentions checksum \(incomingHash) in its text", source: "manual" )) let root = try makeTemporaryDirectory(prefix: "PromptGarageHashSubstring") let sourceURL = root.appendingPathComponent("analysis.json") let source = [ "cluster": "Code/Testing", "quotes_with_source": [["quote": incomingContent]], ] as [String: Any] try JSONSerialization.data(withJSONObject: source).write(to: sourceURL) let preview = try ImportEngine().preview( format: .chatGPTAnalysisJSON, sourceURL: sourceURL, dedupIndex: repository.makeDedupIndex() ) XCTAssertEqual(preview.candidates.map(\.content), [incomingContent]) XCTAssertEqual(preview.statistics.imported, 1) XCTAssertEqual(preview.statistics.skippedDuplicate, 0) let committed = try repository.importCandidates( preview.candidates, baseStatistics: preview.statistics ) XCTAssertEqual(committed.imported, 1) XCTAssertEqual(committed.skippedDuplicate, 0) XCTAssertEqual(try repository.count(), 2) } func testDedupTrimsExistingContentForPreviewAndCommit() throws { let repository = try PromptRepository.inMemory() let canonicalContent = "Explain why canonical trimming must make legacy and incoming prompt content identical" _ = try repository.create(PromptDraft( title: "Whitespace legacy prompt", content: " \n\(canonicalContent)\t ", source: "manual" )) let root = try makeTemporaryDirectory(prefix: "PromptGarageWhitespaceDedup") let sourceURL = root.appendingPathComponent("analysis.json") let source = [ "cluster": "Code/Testing", "quotes_with_source": [["quote": canonicalContent]], ] as [String: Any] try JSONSerialization.data(withJSONObject: source).write(to: sourceURL) let preview = try ImportEngine().preview( format: .chatGPTAnalysisJSON, sourceURL: sourceURL, dedupIndex: repository.makeDedupIndex() ) XCTAssertTrue(preview.candidates.isEmpty) XCTAssertEqual(preview.statistics.imported, 0) XCTAssertEqual(preview.statistics.skippedDuplicate, 1) let committed = try repository.importCandidates([ ImportCandidate( title: "Trimmed incoming prompt", content: canonicalContent, source: "manual-import", tags: nil, model: nil ), ]) XCTAssertEqual(committed.imported, 0) XCTAssertEqual(committed.skippedDuplicate, 1) XCTAssertEqual(try repository.count(), 1) } func testExistingUnparseableTimestampRemainsStoredButCountsAsUnknown() throws { let root = try makeTemporaryDirectory(prefix: "PromptGarageInvalidDate") let url = root.appendingPathComponent("prompts.db") let queue = try DatabaseQueue(path: url.path) try queue.write { db in try DatabaseSchema.createIfNeeded(in: db) try db.execute( sql: """ INSERT INTO prompts (title, content, tags, prompted_at) VALUES (?, ?, ?, ?) """, arguments: [ "Legacy invalid timestamp", "This existing prompt must not have its chronology metadata rewritten", "legacy", "not-a-real-timestamp", ] ) } try queue.close() let repository = try PromptRepository(databaseURL: url) let prompt = try XCTUnwrap(repository.page().prompts.first) XCTAssertEqual(prompt.promptedAt, "not-a-real-timestamp") XCTAssertEqual(try repository.legacyUndatedCount(), 1) XCTAssertTrue(try repository.dayGroups().isEmpty) } private func makeTemporaryDirectory(prefix: String) throws -> URL { let directory = FileManager.default.temporaryDirectory .appendingPathComponent("\(prefix)-\(UUID().uuidString)", isDirectory: true) try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true) addTeardownBlock { try? FileManager.default.removeItem(at: directory) } return directory } }