history_repo_test.go RawHistoryFolder

1package sqlite_test
2
3import (
4	"context"
5	"database/sql"
6	"fmt"
7	"path/filepath"
8	"strings"
9	"testing"
10	"time"
11
12	"github.com/bnema/dumber/internal/domain/entity"
13	repository "github.com/bnema/dumber/internal/domain/repository"
14	"github.com/bnema/dumber/internal/infrastructure/persistence/sqlite"
15	"github.com/bnema/dumber/internal/infrastructure/persistence/sqlite/sqlc"
16	"github.com/bnema/dumber/internal/logging"
17	"github.com/stretchr/testify/assert"
18	"github.com/stretchr/testify/require"
19)
20
21func historyTestCtx() context.Context {
22	logger := logging.NewFromConfigValues("debug", "console")
23	return logging.WithContext(context.Background(), logger)
24}
25
26func TestHistoryRepository_GetRecentSince(t *testing.T) {
27	ctx := historyTestCtx()
28	dbPath := filepath.Join(t.TempDir(), "dumber.db")
29
30	db, err := sqlite.NewConnection(ctx, dbPath)
31	require.NoError(t, err)
32	t.Cleanup(func() { _ = db.Close() })
33
34	repo := sqlite.NewHistoryRepository(db)
35
36	// Save some history entries
37	entries := []*entity.HistoryEntry{
38		{URL: "https://example.com", Title: "Example"},
39		{URL: "https://github.com", Title: "GitHub"},
40		{URL: "https://google.com", Title: "Google"},
41	}
42
43	for _, e := range entries {
44		require.NoError(t, repo.Save(ctx, e))
45	}
46
47	// Get recent since 30 days (all should be included)
48	results, err := repo.GetRecentSince(ctx, 30)
49	require.NoError(t, err)
50	require.Len(t, results, 3)
51
52	// Results should be sorted by last_visited DESC
53	// Since they were all just inserted, order may vary
54	urls := make(map[string]bool)
55	for _, r := range results {
56		urls[r.URL] = true
57	}
58	assert.True(t, urls["https://example.com"])
59	assert.True(t, urls["https://github.com"])
60	assert.True(t, urls["https://google.com"])
61}
62
63func TestHistoryRepository_GetRecentSince_EmptyResult(t *testing.T) {
64	ctx := historyTestCtx()
65	dbPath := filepath.Join(t.TempDir(), "dumber.db")
66
67	db, err := sqlite.NewConnection(ctx, dbPath)
68	require.NoError(t, err)
69	t.Cleanup(func() { _ = db.Close() })
70
71	repo := sqlite.NewHistoryRepository(db)
72
73	// Get recent with no data
74	results, err := repo.GetRecentSince(ctx, 30)
75	require.NoError(t, err)
76	assert.Empty(t, results)
77}
78
79func TestHistoryRepository_GetMostVisited(t *testing.T) {
80	ctx := historyTestCtx()
81	dbPath := filepath.Join(t.TempDir(), "dumber.db")
82
83	db, err := sqlite.NewConnection(ctx, dbPath)
84	require.NoError(t, err)
85	t.Cleanup(func() { _ = db.Close() })
86
87	repo := sqlite.NewHistoryRepository(db)
88
89	// Save entries with different visit counts
90	entry1 := &entity.HistoryEntry{URL: "https://example.com", Title: "Example"}
91	entry2 := &entity.HistoryEntry{URL: "https://github.com", Title: "GitHub"}
92	entry3 := &entity.HistoryEntry{URL: "https://google.com", Title: "Google"}
93
94	require.NoError(t, repo.Save(ctx, entry1))
95	require.NoError(t, repo.Save(ctx, entry2))
96	require.NoError(t, repo.Save(ctx, entry3))
97
98	// Increment visit counts to create different popularities
99	// github: 5 visits, google: 3 visits, example: 1 visit
100	for range 4 {
101		require.NoError(t, repo.IncrementVisitCount(ctx, "https://github.com"))
102	}
103	for range 2 {
104		require.NoError(t, repo.IncrementVisitCount(ctx, "https://google.com"))
105	}
106
107	// Get most visited
108	results, err := repo.GetMostVisited(ctx, 30)
109	require.NoError(t, err)
110	require.Len(t, results, 3)
111
112	// First result should be github (5 visits)
113	assert.Equal(t, "https://github.com", results[0].URL)
114	assert.Equal(t, int64(5), results[0].VisitCount)
115
116	// Second should be google (3 visits)
117	assert.Equal(t, "https://google.com", results[1].URL)
118	assert.Equal(t, int64(3), results[1].VisitCount)
119
120	// Third should be example (1 visit)
121	assert.Equal(t, "https://example.com", results[2].URL)
122	assert.Equal(t, int64(1), results[2].VisitCount)
123}
124
125func TestHistoryRepository_GetMostVisited_EmptyResult(t *testing.T) {
126	ctx := historyTestCtx()
127	dbPath := filepath.Join(t.TempDir(), "dumber.db")
128
129	db, err := sqlite.NewConnection(ctx, dbPath)
130	require.NoError(t, err)
131	t.Cleanup(func() { _ = db.Close() })
132
133	repo := sqlite.NewHistoryRepository(db)
134
135	// Get most visited with no data
136	results, err := repo.GetMostVisited(ctx, 30)
137	require.NoError(t, err)
138	assert.Empty(t, results)
139}
140
141func TestHistoryRepository_GetRecentSince_RespectsTimeFilter(t *testing.T) {
142	ctx := historyTestCtx()
143	dbPath := filepath.Join(t.TempDir(), "dumber.db")
144
145	db, err := sqlite.NewConnection(ctx, dbPath)
146	require.NoError(t, err)
147	t.Cleanup(func() { _ = db.Close() })
148
149	repo := sqlite.NewHistoryRepository(db)
150
151	// Save an entry
152	entry := &entity.HistoryEntry{URL: "https://example.com", Title: "Example"}
153	require.NoError(t, repo.Save(ctx, entry))
154
155	// Query with 1 day filter - should include recently added entry
156	results, err := repo.GetRecentSince(ctx, 1)
157	require.NoError(t, err)
158	require.Len(t, results, 1)
159
160	// Query with very large days filter - should still work
161	results, err = repo.GetRecentSince(ctx, 365)
162	require.NoError(t, err)
163	require.Len(t, results, 1)
164}
165
166func TestHistoryRepository_CRUD(t *testing.T) {
167	ctx := historyTestCtx()
168	dbPath := filepath.Join(t.TempDir(), "dumber.db")
169
170	db, err := sqlite.NewConnection(ctx, dbPath)
171	require.NoError(t, err)
172	t.Cleanup(func() { _ = db.Close() })
173
174	repo := sqlite.NewHistoryRepository(db)
175
176	// Create
177	entry := &entity.HistoryEntry{URL: "https://example.com", Title: "Example"}
178	require.NoError(t, repo.Save(ctx, entry))
179
180	// Read
181	found, err := repo.FindByURL(ctx, "https://example.com")
182	require.NoError(t, err)
183	require.NotNil(t, found)
184	assert.Equal(t, "https://example.com", found.URL)
185	assert.Equal(t, "Example", found.Title)
186	assert.Equal(t, int64(1), found.VisitCount)
187
188	// Update (save again increments visit count)
189	require.NoError(t, repo.Save(ctx, entry))
190	found2, err := repo.FindByURL(ctx, "https://example.com")
191	require.NoError(t, err)
192	assert.Equal(t, int64(2), found2.VisitCount)
193
194	// Get recent
195	recent, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{})
196	require.NoError(t, err)
197	require.Len(t, recent, 1)
198
199	allRecent, err := repo.GetRecent(ctx, 0, 0, repository.HistoryScope{})
200	require.NoError(t, err)
201	require.Len(t, allRecent, 1)
202
203	// Delete
204	require.NoError(t, repo.Delete(ctx, found.ID))
205
206	// Verify deleted
207	deleted, err := repo.FindByURL(ctx, "https://example.com")
208	require.NoError(t, err)
209	assert.Nil(t, deleted)
210}
211
212// Ensure the test uses an isolated timestamp for time-sensitive queries
213func TestHistoryRepository_TimeFilterIntegrity(t *testing.T) {
214	ctx := historyTestCtx()
215	dbPath := filepath.Join(t.TempDir(), "dumber.db")
216
217	db, err := sqlite.NewConnection(ctx, dbPath)
218	require.NoError(t, err)
219	t.Cleanup(func() { _ = db.Close() })
220
221	repo := sqlite.NewHistoryRepository(db)
222
223	// Save entry
224	entry := &entity.HistoryEntry{URL: "https://test.com", Title: "Test"}
225	require.NoError(t, repo.Save(ctx, entry))
226
227	// Verify entry was saved with recent timestamp
228	found, err := repo.FindByURL(ctx, "https://test.com")
229	require.NoError(t, err)
230	require.NotNil(t, found)
231
232	// LastVisited should be within the last minute
233	timeDiff := time.Since(found.LastVisited)
234	assert.Less(t, timeDiff, time.Minute, "LastVisited should be recent, got %v ago", timeDiff)
235}
236
237func TestHistoryRepository_GetRecentSince_RejectsNonPositiveDays(t *testing.T) {
238	ctx := historyTestCtx()
239	dbPath := filepath.Join(t.TempDir(), "dumber.db")
240
241	db, err := sqlite.NewConnection(ctx, dbPath)
242	require.NoError(t, err)
243	t.Cleanup(func() { _ = db.Close() })
244
245	repo := sqlite.NewHistoryRepository(db)
246
247	// days = 0 should return error
248	_, err = repo.GetRecentSince(ctx, 0)
249	require.Error(t, err)
250	assert.Contains(t, err.Error(), "days must be positive")
251
252	// days < 0 should return error
253	_, err = repo.GetRecentSince(ctx, -5)
254	require.Error(t, err)
255	assert.Contains(t, err.Error(), "days must be positive")
256}
257
258func TestHistoryRepository_GetMostVisited_RejectsNonPositiveDays(t *testing.T) {
259	ctx := historyTestCtx()
260	dbPath := filepath.Join(t.TempDir(), "dumber.db")
261
262	db, err := sqlite.NewConnection(ctx, dbPath)
263	require.NoError(t, err)
264	t.Cleanup(func() { _ = db.Close() })
265
266	repo := sqlite.NewHistoryRepository(db)
267
268	// days = 0 should return error
269	_, err = repo.GetMostVisited(ctx, 0)
270	require.Error(t, err)
271	assert.Contains(t, err.Error(), "days must be positive")
272
273	// days < 0 should return error
274	_, err = repo.GetMostVisited(ctx, -5)
275	require.Error(t, err)
276	assert.Contains(t, err.Error(), "days must be positive")
277}
278
279func TestHistoryRepository_GetAllRecentHistory(t *testing.T) {
280	ctx := historyTestCtx()
281	dbPath := filepath.Join(t.TempDir(), "dumber.db")
282
283	db, err := sqlite.NewConnection(ctx, dbPath)
284	require.NoError(t, err)
285	t.Cleanup(func() { _ = db.Close() })
286
287	repo := sqlite.NewHistoryRepository(db)
288
289	// Save some entries
290	entries := []*entity.HistoryEntry{
291		{URL: "https://example.com", Title: "Example"},
292		{URL: "https://github.com", Title: "GitHub"},
293	}
294	for _, e := range entries {
295		require.NoError(t, repo.Save(ctx, e))
296	}
297
298	// Get all recent history
299	results, err := repo.GetAllRecentHistory(ctx)
300	require.NoError(t, err)
301	require.Len(t, results, 2)
302}
303
304func TestHistoryRepository_GetAllMostVisited(t *testing.T) {
305	ctx := historyTestCtx()
306	dbPath := filepath.Join(t.TempDir(), "dumber.db")
307
308	db, err := sqlite.NewConnection(ctx, dbPath)
309	require.NoError(t, err)
310	t.Cleanup(func() { _ = db.Close() })
311
312	repo := sqlite.NewHistoryRepository(db)
313
314	// Save entries with different visit counts
315	entry1 := &entity.HistoryEntry{URL: "https://example.com", Title: "Example"}
316	entry2 := &entity.HistoryEntry{URL: "https://github.com", Title: "GitHub"}
317
318	require.NoError(t, repo.Save(ctx, entry1))
319	require.NoError(t, repo.Save(ctx, entry2))
320
321	// Increment github visits
322	for range 3 {
323		require.NoError(t, repo.IncrementVisitCount(ctx, "https://github.com"))
324	}
325
326	// Get all most visited
327	results, err := repo.GetAllMostVisited(ctx)
328	require.NoError(t, err)
329	require.Len(t, results, 2)
330
331	// First should be github (4 visits)
332	assert.Equal(t, "https://github.com", results[0].URL)
333	assert.Equal(t, int64(4), results[0].VisitCount)
334}
335
336func TestHistoryRepository_Search_SingleWord(t *testing.T) {
337	ctx := historyTestCtx()
338	dbPath := filepath.Join(t.TempDir(), "dumber.db")
339
340	db, err := sqlite.NewConnection(ctx, dbPath)
341	require.NoError(t, err)
342	t.Cleanup(func() { _ = db.Close() })
343
344	repo := sqlite.NewHistoryRepository(db)
345
346	// Save entries
347	entries := []*entity.HistoryEntry{
348		{URL: "https://github.com/user/repo", Title: "My GitHub Repository"},
349		{URL: "https://gitlab.com/project", Title: "GitLab Project"},
350		{URL: "https://example.com", Title: "Example Site"},
351	}
352	for _, e := range entries {
353		require.NoError(t, repo.Save(ctx, e))
354	}
355
356	// Search for "github" - should find the github entry
357	results, err := repo.Search(ctx, "github", 10, repository.HistoryScope{})
358	require.NoError(t, err)
359	require.Len(t, results, 1)
360	assert.Equal(t, "https://github.com/user/repo", results[0].Entry.URL)
361}
362
363func TestHistoryRepository_Search_MultiWord(t *testing.T) {
364	ctx := historyTestCtx()
365	dbPath := filepath.Join(t.TempDir(), "dumber.db")
366
367	db, err := sqlite.NewConnection(ctx, dbPath)
368	require.NoError(t, err)
369	t.Cleanup(func() { _ = db.Close() })
370
371	repo := sqlite.NewHistoryRepository(db)
372
373	// Save entries
374	entries := []*entity.HistoryEntry{
375		{URL: "https://github.com/issues", Title: "GitHub Issues"},
376		{URL: "https://github.com/pulls", Title: "GitHub Pull Requests"},
377		{URL: "https://jira.com/issues", Title: "Jira Issues"},
378	}
379	for _, e := range entries {
380		require.NoError(t, repo.Save(ctx, e))
381	}
382
383	// Search for "github issues" - should find only the github issues entry
384	results, err := repo.Search(ctx, "github issues", 10, repository.HistoryScope{})
385	require.NoError(t, err)
386	require.Len(t, results, 1)
387	assert.Equal(t, "https://github.com/issues", results[0].Entry.URL)
388}
389
390func TestHistoryRepository_Search_MatchesTitle(t *testing.T) {
391	ctx := historyTestCtx()
392	dbPath := filepath.Join(t.TempDir(), "dumber.db")
393
394	db, err := sqlite.NewConnection(ctx, dbPath)
395	require.NoError(t, err)
396	t.Cleanup(func() { _ = db.Close() })
397
398	repo := sqlite.NewHistoryRepository(db)
399
400	// Save entry with specific title that doesn't appear in URL
401	entry := &entity.HistoryEntry{
402		URL:   "https://docs.example.com/guide",
403		Title: "Comprehensive Documentation Guide",
404	}
405	require.NoError(t, repo.Save(ctx, entry))
406
407	// Search for "Documentation" - should find by title
408	results, err := repo.Search(ctx, "Documentation", 10, repository.HistoryScope{})
409	require.NoError(t, err)
410	require.Len(t, results, 1)
411	assert.Equal(t, "https://docs.example.com/guide", results[0].Entry.URL)
412}
413
414func TestHistoryRepository_Search_EmptyQuery(t *testing.T) {
415	ctx := historyTestCtx()
416	dbPath := filepath.Join(t.TempDir(), "dumber.db")
417
418	db, err := sqlite.NewConnection(ctx, dbPath)
419	require.NoError(t, err)
420	t.Cleanup(func() { _ = db.Close() })
421
422	repo := sqlite.NewHistoryRepository(db)
423
424	// Save an entry
425	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
426		URL:   "https://example.com",
427		Title: "Example",
428	}))
429
430	// Empty query should return empty results, no error
431	results, err := repo.Search(ctx, "", 10, repository.HistoryScope{})
432	require.NoError(t, err)
433	assert.Empty(t, results)
434
435	// Whitespace-only query should also return empty results
436	results, err = repo.Search(ctx, "   ", 10, repository.HistoryScope{})
437	require.NoError(t, err)
438	assert.Empty(t, results)
439}
440
441func TestHistoryRepository_Search_NoValidTokens(t *testing.T) {
442	ctx := historyTestCtx()
443	dbPath := filepath.Join(t.TempDir(), "dumber.db")
444
445	db, err := sqlite.NewConnection(ctx, dbPath)
446	require.NoError(t, err)
447	t.Cleanup(func() { _ = db.Close() })
448
449	repo := sqlite.NewHistoryRepository(db)
450
451	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
452		URL:   "https://example.com",
453		Title: "Example",
454	}))
455
456	invalidQueries := []string{
457		"!!!",
458		"___",
459		"AND OR NOT NEAR",
460		`"***"`,
461	}
462
463	for _, q := range invalidQueries {
464		results, err := repo.Search(ctx, q, 10, repository.HistoryScope{})
465		require.NoError(t, err, "query %q should return early without SQL errors", q)
466		assert.Empty(t, results, "query %q should have no valid tokens", q)
467	}
468}
469
470func TestHistoryRepository_Search_NoResults(t *testing.T) {
471	ctx := historyTestCtx()
472	dbPath := filepath.Join(t.TempDir(), "dumber.db")
473
474	db, err := sqlite.NewConnection(ctx, dbPath)
475	require.NoError(t, err)
476	t.Cleanup(func() { _ = db.Close() })
477
478	repo := sqlite.NewHistoryRepository(db)
479
480	// Save an entry
481	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
482		URL:   "https://example.com",
483		Title: "Example",
484	}))
485
486	// Search for something that doesn't exist
487	results, err := repo.Search(ctx, "nonexistent", 10, repository.HistoryScope{})
488	require.NoError(t, err)
489	assert.Empty(t, results)
490}
491
492func TestHistoryRepository_Search_PrefixMatching(t *testing.T) {
493	ctx := historyTestCtx()
494	dbPath := filepath.Join(t.TempDir(), "dumber.db")
495
496	db, err := sqlite.NewConnection(ctx, dbPath)
497	require.NoError(t, err)
498	t.Cleanup(func() { _ = db.Close() })
499
500	repo := sqlite.NewHistoryRepository(db)
501
502	// Save entries
503	entries := []*entity.HistoryEntry{
504		{URL: "https://github.com", Title: "GitHub"},
505		{URL: "https://gitlab.com", Title: "GitLab"},
506		{URL: "https://example.com", Title: "Example"},
507	}
508	for _, e := range entries {
509		require.NoError(t, repo.Save(ctx, e))
510	}
511
512	// Search for "git" - should find both github and gitlab (prefix match)
513	results, err := repo.Search(ctx, "git", 10, repository.HistoryScope{})
514	require.NoError(t, err)
515	require.Len(t, results, 2)
516
517	// Verify both git* entries are found
518	urls := make(map[string]bool)
519	for _, r := range results {
520		urls[r.Entry.URL] = true
521	}
522	assert.True(t, urls["https://github.com"])
523	assert.True(t, urls["https://gitlab.com"])
524}
525
526func TestHistoryRepository_Search_DomainLikeQuery(t *testing.T) {
527	ctx := historyTestCtx()
528	dbPath := filepath.Join(t.TempDir(), "dumber.db")
529
530	db, err := sqlite.NewConnection(ctx, dbPath)
531	require.NoError(t, err)
532	t.Cleanup(func() { _ = db.Close() })
533
534	repo := sqlite.NewHistoryRepository(db)
535
536	// Save entries with domain-like URLs
537	entries := []*entity.HistoryEntry{
538		{URL: "https://gordon.bnema.dev/", Title: "Gordon - Self-hosted Deployment Platform"},
539		{URL: "https://example.com", Title: "Example Site"},
540		{URL: "https://other.gordon.io", Title: "Another Gordon Site"},
541	}
542	for _, e := range entries {
543		require.NoError(t, repo.Save(ctx, e))
544	}
545
546	// Search with period in query (domain-like) - periods should be treated as separators
547	// "gordon.bnem" should match "gordon.bnema.dev" (tokens: gordon, bnem -> gordon, bnema)
548	results, err := repo.Search(ctx, "gordon.bnem", 10, repository.HistoryScope{})
549	require.NoError(t, err)
550	require.NotEmpty(t, results, "domain-like query 'gordon.bnem' should match gordon.bnema.dev")
551
552	// Verify the gordon.bnema.dev entry is found
553	found := false
554	for _, r := range results {
555		if r.Entry.URL == "https://gordon.bnema.dev/" {
556			found = true
557			break
558		}
559	}
560	assert.True(t, found, "gordon.bnema.dev should be in results")
561}
562
563func TestHistoryRepository_Search_SpecialCharacters(t *testing.T) {
564	ctx := historyTestCtx()
565	dbPath := filepath.Join(t.TempDir(), "dumber.db")
566
567	db, err := sqlite.NewConnection(ctx, dbPath)
568	require.NoError(t, err)
569	t.Cleanup(func() { _ = db.Close() })
570
571	repo := sqlite.NewHistoryRepository(db)
572
573	// Save entry
574	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
575		URL:   "https://example.com/path",
576		Title: "Example Page",
577	}))
578
579	// Search with FTS5 special characters - should not cause errors
580	specialQueries := []string{
581		"example:",     // colon
582		"example*",     // asterisk
583		"example()",    // parentheses
584		`example"test`, // quotes
585		"example^",     // caret
586		"example-test", // hyphen
587		"example/",     // slash
588	}
589
590	for _, q := range specialQueries {
591		results, err := repo.Search(ctx, q, 10, repository.HistoryScope{})
592		require.NoError(t, err, "query %q should not cause error", q)
593		// May or may not find results, but should not error
594		_ = results
595	}
596}
597
598func TestHistoryRepository_Search_IgnoresFTSOperatorsAsTokens(t *testing.T) {
599	ctx := historyTestCtx()
600	dbPath := filepath.Join(t.TempDir(), "dumber.db")
601
602	db, err := sqlite.NewConnection(ctx, dbPath)
603	require.NoError(t, err)
604	t.Cleanup(func() { _ = db.Close() })
605
606	repo := sqlite.NewHistoryRepository(db)
607
608	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
609		URL:   "https://github.com/bnema/dumber",
610		Title: "Dumber",
611	}))
612
613	results, err := repo.Search(ctx, "AND github OR", 10, repository.HistoryScope{})
614	require.NoError(t, err)
615	require.Len(t, results, 1)
616	assert.Equal(t, "https://github.com/bnema/dumber", results[0].Entry.URL)
617}
618
619func TestHistoryRepository_Search_SlashSeparatedQuery(t *testing.T) {
620	ctx := historyTestCtx()
621	dbPath := filepath.Join(t.TempDir(), "dumber.db")
622
623	db, err := sqlite.NewConnection(ctx, dbPath)
624	require.NoError(t, err)
625	t.Cleanup(func() { _ = db.Close() })
626
627	repo := sqlite.NewHistoryRepository(db)
628
629	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
630		URL:   "https://github.com/bnema/dumber",
631		Title: "Dumber",
632	}))
633
634	results, err := repo.Search(ctx, "github.com/bnema", 10, repository.HistoryScope{})
635	require.NoError(t, err)
636	require.NotEmpty(t, results, "slash-separated query should match history")
637}
638
639func TestHistoryRepository_Search_PrefersURLMatchOverTitleOnlyMatch(t *testing.T) {
640	ctx := historyTestCtx()
641	dbPath := filepath.Join(t.TempDir(), "dumber.db")
642
643	db, err := sqlite.NewConnection(ctx, dbPath)
644	require.NoError(t, err)
645	t.Cleanup(func() { _ = db.Close() })
646
647	repo := sqlite.NewHistoryRepository(db)
648
649	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
650		URL:   "https://github.com/bnema/dumber/issues",
651		Title: "Project board",
652	}))
653	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
654		URL:   "https://tracker.example.com/board",
655		Title: "GitHub Issues Mirror",
656	}))
657
658	results, err := repo.Search(ctx, "github issues", 10, repository.HistoryScope{})
659	require.NoError(t, err)
660	require.Len(t, results, 2)
661	assert.Equal(t, "https://github.com/bnema/dumber/issues", results[0].Entry.URL)
662}
663
664func TestHistoryRepository_Search_PrefersHostPrefixOverTitleContains(t *testing.T) {
665	ctx := historyTestCtx()
666	dbPath := filepath.Join(t.TempDir(), "dumber.db")
667
668	db, err := sqlite.NewConnection(ctx, dbPath)
669	require.NoError(t, err)
670	t.Cleanup(func() { _ = db.Close() })
671
672	repo := sqlite.NewHistoryRepository(db)
673
674	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
675		URL:   "https://github.com",
676		Title: "GitHub",
677	}))
678	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
679		URL:   "https://example.com/landing",
680		Title: "GitHub documentation mirror",
681	}))
682
683	results, err := repo.Search(ctx, "git", 10, repository.HistoryScope{})
684	require.NoError(t, err)
685	require.Len(t, results, 2)
686	assert.Equal(t, "https://github.com", results[0].Entry.URL)
687}
688
689func TestHistoryRepository_Search_PrefersShorterRootURLForHostQuery(t *testing.T) {
690	ctx := historyTestCtx()
691	dbPath := filepath.Join(t.TempDir(), "dumber.db")
692
693	db, err := sqlite.NewConnection(ctx, dbPath)
694	require.NoError(t, err)
695	t.Cleanup(func() { _ = db.Close() })
696
697	repo := sqlite.NewHistoryRepository(db)
698
699	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
700		URL:   "https://github.com",
701		Title: "GitHub",
702	}))
703	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
704		URL:   "https://github.com/bnema/dumber/issues/123",
705		Title: "Issue 123",
706	}))
707
708	results, err := repo.Search(ctx, "github", 10, repository.HistoryScope{})
709	require.NoError(t, err)
710	require.Len(t, results, 2)
711	assert.Equal(t, "https://github.com", results[0].Entry.URL)
712}
713
714func TestHistoryRepository_UpdateMetadata_DoesNotIncrementVisitCount(t *testing.T) {
715	ctx := historyTestCtx()
716	dbPath := filepath.Join(t.TempDir(), "dumber.db")
717
718	db, err := sqlite.NewConnection(ctx, dbPath)
719	require.NoError(t, err)
720	t.Cleanup(func() { _ = db.Close() })
721
722	repoIface := sqlite.NewHistoryRepository(db)
723	repo, ok := repoIface.(interface {
724		Save(context.Context, *entity.HistoryEntry) error
725		FindByURL(context.Context, string) (*entity.HistoryEntry, error)
726		UpdateMetadata(context.Context, *entity.HistoryEntry) error
727	})
728	require.True(t, ok)
729
730	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
731		URL:   "https://github.com/bnema/dumber",
732		Title: "Old title",
733	}))
734
735	before, err := repo.FindByURL(ctx, "https://github.com/bnema/dumber")
736	require.NoError(t, err)
737	require.NotNil(t, before)
738	require.Equal(t, int64(1), before.VisitCount)
739
740	require.NoError(t, repo.UpdateMetadata(ctx, &entity.HistoryEntry{
741		URL:   "https://github.com/bnema/dumber",
742		Title: "New title",
743	}))
744
745	after, err := repo.FindByURL(ctx, "https://github.com/bnema/dumber")
746	require.NoError(t, err)
747	require.NotNil(t, after)
748	assert.Equal(t, int64(1), after.VisitCount)
749	assert.Equal(t, "New title", after.Title)
750}
751
752func TestHistoryRepository_AboutBlank_CappedVisitCount(t *testing.T) {
753	ctx := historyTestCtx()
754	dbPath := filepath.Join(t.TempDir(), "dumber.db")
755
756	db, err := sqlite.NewConnection(ctx, dbPath)
757	require.NoError(t, err)
758	t.Cleanup(func() { _ = db.Close() })
759
760	repo := sqlite.NewHistoryRepository(db)
761
762	// Save about:blank multiple times - visit count should stay at 1
763	for range 5 {
764		require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
765			URL:   "about:blank",
766			Title: "New Tab",
767		}))
768	}
769
770	// Verify about:blank has visit_count = 1
771	found, err := repo.FindByURL(ctx, "about:blank")
772	require.NoError(t, err)
773	require.NotNil(t, found)
774	assert.Equal(t, int64(1), found.VisitCount, "about:blank should always have visit_count = 1")
775
776	// Try incrementing visit count directly - should be ignored
777	require.NoError(t, repo.IncrementVisitCount(ctx, "about:blank"))
778
779	found2, err := repo.FindByURL(ctx, "about:blank")
780	require.NoError(t, err)
781	assert.Equal(t, int64(1), found2.VisitCount, "about:blank increment should be ignored")
782}
783
784func TestHistoryRepository_AboutBlank_NotDominant(t *testing.T) {
785	ctx := historyTestCtx()
786	dbPath := filepath.Join(t.TempDir(), "dumber.db")
787
788	db, err := sqlite.NewConnection(ctx, dbPath)
789	require.NoError(t, err)
790	t.Cleanup(func() { _ = db.Close() })
791
792	repo := sqlite.NewHistoryRepository(db)
793
794	// Save about:blank many times
795	for range 10 {
796		require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
797			URL:   "about:blank",
798			Title: "New Tab",
799		}))
800	}
801
802	// Save a regular entry with multiple visits
803	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
804		URL:   "https://example.com",
805		Title: "Example",
806	}))
807	require.NoError(t, repo.IncrementVisitCount(ctx, "https://example.com"))
808	require.NoError(t, repo.IncrementVisitCount(ctx, "https://example.com"))
809
810	// Get most visited - example.com should be first (3 visits vs 1)
811	results, err := repo.GetAllMostVisited(ctx)
812	require.NoError(t, err)
813	require.Len(t, results, 2)
814
815	assert.Equal(t, "https://example.com", results[0].URL, "example.com should be first with higher visit count")
816	assert.Equal(t, int64(3), results[0].VisitCount)
817
818	assert.Equal(t, "about:blank", results[1].URL, "about:blank should be second with capped visit count")
819	assert.Equal(t, int64(1), results[1].VisitCount)
820}
821
822func TestHistoryRepository_Search_ReturnsEmptyForNonPositiveLimit(t *testing.T) {
823	ctx := historyTestCtx()
824	dbPath := filepath.Join(t.TempDir(), "dumber.db")
825
826	db, err := sqlite.NewConnection(ctx, dbPath)
827	require.NoError(t, err)
828	t.Cleanup(func() { _ = db.Close() })
829
830	repo := sqlite.NewHistoryRepository(db)
831	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{
832		URL:   "https://github.com",
833		Title: "GitHub",
834	}))
835
836	results, err := repo.Search(ctx, "git", 0, repository.HistoryScope{})
837	require.NoError(t, err)
838	assert.Empty(t, results)
839
840	results, err = repo.Search(ctx, "git", -1, repository.HistoryScope{})
841	require.NoError(t, err)
842	assert.Empty(t, results)
843}
844
845func TestHistoryRepository_RejectsEmptyCanonicalDomain(t *testing.T) {
846	ctx := historyTestCtx()
847	dbPath := filepath.Join(t.TempDir(), "dumber.db")
848
849	db, err := sqlite.NewConnection(ctx, dbPath)
850	require.NoError(t, err)
851	t.Cleanup(func() { _ = db.Close() })
852
853	repo := sqlite.NewHistoryRepository(db)
854
855	entries, err := repo.GetRecentByDomain(ctx, " ", 10, 0)
856	require.Error(t, err)
857	assert.Nil(t, entries)
858	assert.Error(t, repo.DeleteByDomain(ctx, "/path"))
859}
860
861func TestHistoryRepository_DomainColumnPowersFilteringStatsAndDelete(t *testing.T) {
862	ctx := historyTestCtx()
863	dbPath := filepath.Join(t.TempDir(), "dumber.db")
864
865	db, err := sqlite.NewConnection(ctx, dbPath)
866	require.NoError(t, err)
867	t.Cleanup(func() { _ = db.Close() })
868
869	repo := sqlite.NewHistoryRepository(db)
870	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{URL: "https://www.example.com/a", Title: "Example A"}))
871	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{URL: "https://example.com/b", Title: "Example B"}))
872	require.NoError(t, repo.Save(ctx, &entity.HistoryEntry{URL: "https://other.test", Title: "Other"}))
873
874	exampleEntries, err := repo.GetRecentByDomain(ctx, "www.example.com", 0, 0)
875	require.NoError(t, err)
876	require.Len(t, exampleEntries, 2)
877	for _, entry := range exampleEntries {
878		assert.Contains(t, entry.URL, "example.com")
879	}
880
881	stats, err := repo.GetDomainStats(ctx, 10)
882	require.NoError(t, err)
883	require.NotEmpty(t, stats)
884	assert.Equal(t, "example.com", stats[0].Domain)
885	assert.Equal(t, int64(2), stats[0].PageCount)
886
887	require.NoError(t, repo.DeleteByDomain(ctx, "example.com"))
888
889	exampleEntries, err = repo.GetRecentByDomain(ctx, "example.com", 10, 0)
890	require.NoError(t, err)
891	assert.Empty(t, exampleEntries)
892
893	remaining, err := repo.GetAllRecentHistory(ctx)
894	require.NoError(t, err)
895	require.Len(t, remaining, 1)
896	assert.Equal(t, "https://other.test", remaining[0].URL)
897}
898func TestHistoryRepository_GetRecentWindow_StableCursorWithTimestampTies(t *testing.T) {
899	ctx := historyTestCtx()
900	dbPath := filepath.Join(t.TempDir(), "dumber.db")
901
902	db, err := sqlite.NewConnection(ctx, dbPath)
903	require.NoError(t, err)
904	t.Cleanup(func() { _ = db.Close() })
905
906	repo := sqlite.NewHistoryRepository(db)
907	tied := time.Date(2026, 6, 24, 10, 0, 0, 0, time.UTC)
908	older := tied.Add(-time.Hour)
909	for i := 1; i <= 5; i++ {
910		_, err = db.ExecContext(ctx, `INSERT INTO history (url, title, domain, last_visited, created_at) VALUES (?, ?, ?, ?, ?)`,
911			fmt.Sprintf("https://example.com/%d", i), fmt.Sprintf("Entry %d", i), "example.com", tied, tied)
912		require.NoError(t, err)
913	}
914	_, err = db.ExecContext(ctx, `INSERT INTO history (url, title, domain, last_visited, created_at) VALUES (?, ?, ?, ?, ?)`,
915		"https://example.com/older", "Older", "example.com", older, older)
916	require.NoError(t, err)
917
918	page1, err := repo.GetRecentWindow(ctx, tied.Add(time.Second), 0, 3)
919	require.NoError(t, err)
920	require.Len(t, page1, 3)
921	assert.Equal(t, []int64{5, 4, 3}, historyIDs(page1))
922
923	page2, err := repo.GetRecentWindow(ctx, page1[len(page1)-1].LastVisited, page1[len(page1)-1].ID, 3)
924	require.NoError(t, err)
925	require.Len(t, page2, 3)
926	assert.Equal(t, []int64{2, 1, 6}, historyIDs(page2))
927
928	seen := map[int64]bool{}
929	for _, entry := range append(page1, page2...) {
930		if seen[entry.ID] {
931			t.Fatalf("duplicate history id %d", entry.ID)
932		}
933		seen[entry.ID] = true
934	}
935}
936
937func historyIDs(entries []*entity.HistoryEntry) []int64 {
938	ids := make([]int64, len(entries))
939	for i, entry := range entries {
940		ids[i] = entry.ID
941	}
942	return ids
943}
944
945// insertHistoryRow seeds a history row with an explicit visit count and
946// last_visited so age-scope tests can use stable, non-now timestamps. The
947// timestamp is written in the same UTC second-precision format that SQLite's
948// CURRENT_TIMESTAMP uses in production.
949func insertHistoryRow(
950	t *testing.T,
951	ctx context.Context,
952	db *sql.DB,
953	url, title string,
954	visitCount int,
955	lastVisited time.Time,
956) {
957	t.Helper()
958	timestamp := sqliteTimestamp(lastVisited)
959	_, err := db.ExecContext(ctx,
960		`INSERT INTO history (url, title, last_visited, created_at, visit_count) VALUES (?, ?, ?, ?, ?)`,
961		url, title, timestamp, timestamp, visitCount)
962	require.NoError(t, err)
963}
964
965// sqliteTimestamp formats a time the way SQLite's CURRENT_TIMESTAMP stores it:
966// UTC at second precision without a timezone suffix.
967func sqliteTimestamp(t time.Time) string {
968	return t.UTC().Format("2006-01-02 15:04:05")
969}
970
971func TestHistoryRepository_GetMostVisitedWithin_ExcludesOldHighCountURL(t *testing.T) {
972	ctx := historyTestCtx()
973	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
974	require.NoError(t, err)
975	t.Cleanup(func() { _ = db.Close() })
976	repo := sqlite.NewHistoryRepository(db)
977
978	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
979	cutoff := now.AddDate(0, 0, -30)
980	insertHistoryRow(t, ctx, db, "https://old.example.com", "Old Popular", 50, now.AddDate(0, 0, -40))
981	insertHistoryRow(t, ctx, db, "https://recent.example.com", "Recent", 2, now.AddDate(0, 0, -1))
982
983	results, err := repo.GetMostVisitedWithin(ctx, 10, repository.HistoryScope{Cutoff: cutoff})
984	require.NoError(t, err)
985	require.Len(t, results, 1)
986	assert.Equal(t, "https://recent.example.com", results[0].URL)
987
988	// A zero cutoff means all stored history, so the old popular URL returns.
989	all, err := repo.GetMostVisitedWithin(ctx, 10, repository.HistoryScope{})
990	require.NoError(t, err)
991	require.Len(t, all, 2)
992	assert.Equal(t, "https://old.example.com", all[0].URL)
993	assert.Equal(t, int64(50), all[0].VisitCount)
994}
995
996func TestHistoryRepository_GetMostVisitedWithin_BoundsResults(t *testing.T) {
997	ctx := historyTestCtx()
998	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
999	require.NoError(t, err)
1000	t.Cleanup(func() { _ = db.Close() })
1001	repo := sqlite.NewHistoryRepository(db)
1002
1003	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1004	insertHistoryRow(t, ctx, db, "https://one.example.com", "One", 5, now.Add(-time.Hour))
1005	insertHistoryRow(t, ctx, db, "https://two.example.com", "Two", 3, now.Add(-2*time.Hour))
1006	insertHistoryRow(t, ctx, db, "https://three.example.com", "Three", 1, now.Add(-3*time.Hour))
1007
1008	results, err := repo.GetMostVisitedWithin(ctx, 2, repository.HistoryScope{})
1009	require.NoError(t, err)
1010	require.Len(t, results, 2)
1011	assert.Equal(t, []string{"https://one.example.com", "https://two.example.com"}, historyURLs(results))
1012}
1013
1014func TestHistoryRepository_GetRecent_ScopeExcludesOldEntries(t *testing.T) {
1015	ctx := historyTestCtx()
1016	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1017	require.NoError(t, err)
1018	t.Cleanup(func() { _ = db.Close() })
1019	repo := sqlite.NewHistoryRepository(db)
1020
1021	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1022	cutoff := now.AddDate(0, 0, -30)
1023	insertHistoryRow(t, ctx, db, "https://old.example.com", "Old", 1, now.AddDate(0, 0, -40))
1024	insertHistoryRow(t, ctx, db, "https://recent.example.com", "Recent", 1, now.AddDate(0, 0, -1))
1025
1026	scoped, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{Cutoff: cutoff})
1027	require.NoError(t, err)
1028	require.Len(t, scoped, 1)
1029	assert.Equal(t, "https://recent.example.com", scoped[0].URL)
1030
1031	all, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{})
1032	require.NoError(t, err)
1033	require.Len(t, all, 2)
1034
1035	bounded, err := repo.GetRecent(ctx, 1, 0, repository.HistoryScope{})
1036	require.NoError(t, err)
1037	require.Len(t, bounded, 1)
1038}
1039
1040func TestHistoryRepository_Search_ScopeExcludesOldTitleOnlyMatch(t *testing.T) {
1041	ctx := historyTestCtx()
1042	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1043	require.NoError(t, err)
1044	t.Cleanup(func() { _ = db.Close() })
1045	repo := sqlite.NewHistoryRepository(db)
1046
1047	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1048	cutoff := now.AddDate(0, 0, -30)
1049	// Matches only in the title, but is older than the window.
1050	insertHistoryRow(t, ctx, db, "https://old.example.com/ancient", "Widget Old Guide", 1, now.AddDate(0, 0, -40))
1051	// Matches in the title and is inside the window.
1052	insertHistoryRow(t, ctx, db, "https://recent.example.com/guide", "Widget Recent Guide", 1, now.AddDate(0, 0, -1))
1053	// Matches in the URL, but is older than the window.
1054	insertHistoryRow(t, ctx, db, "https://widget.example.com/page", "No match here", 1, now.AddDate(0, 0, -40))
1055
1056	scoped, err := repo.Search(ctx, "widget", 10, repository.HistoryScope{Cutoff: cutoff})
1057	require.NoError(t, err)
1058	require.Len(t, scoped, 1)
1059	assert.Equal(t, "https://recent.example.com/guide", scoped[0].Entry.URL)
1060
1061	// A zero cutoff keeps the general, unrestricted search behavior.
1062	all, err := repo.Search(ctx, "widget", 10, repository.HistoryScope{})
1063	require.NoError(t, err)
1064	require.Len(t, all, 3)
1065}
1066
1067func historyURLs(entries []*entity.HistoryEntry) []string {
1068	urls := make([]string, len(entries))
1069	for i, entry := range entries {
1070		urls[i] = entry.URL
1071	}
1072	return urls
1073}
1074
1075func TestHistoryRepository_ScopeCutoffIsInclusiveAtSecondPrecision(t *testing.T) {
1076	ctx := historyTestCtx()
1077	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1078	require.NoError(t, err)
1079	t.Cleanup(func() { _ = db.Close() })
1080	repo := sqlite.NewHistoryRepository(db)
1081
1082	cutoff := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1083	insertHistoryRow(t, ctx, db, "https://exact.example.com", "Exact", 1, cutoff)
1084	insertHistoryRow(t, ctx, db, "https://before.example.com", "Before", 1, cutoff.Add(-time.Second))
1085
1086	results, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{Cutoff: cutoff})
1087	require.NoError(t, err)
1088	require.Len(t, results, 1, "the entry exactly on the cutoff must be included")
1089	assert.Equal(t, "https://exact.example.com", results[0].URL)
1090}
1091
1092func TestHistoryRepository_UnboundedScopePreservesNullLastVisited(t *testing.T) {
1093	ctx := historyTestCtx()
1094	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1095	require.NoError(t, err)
1096	t.Cleanup(func() { _ = db.Close() })
1097	repo := sqlite.NewHistoryRepository(db)
1098
1099	_, err = db.ExecContext(
1100		ctx,
1101		`INSERT INTO history (url, title, last_visited, visit_count) VALUES (?, ?, NULL, ?)`,
1102		"https://null-last-visited.example.com",
1103		"Legacy Null Row",
1104		1,
1105	)
1106	require.NoError(t, err)
1107
1108	cutoff := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1109
1110	recent, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{})
1111	require.NoError(t, err)
1112	require.Len(t, recent, 1, "unbounded recent history must keep NULL last_visited rows")
1113
1114	mostVisited, err := repo.GetMostVisitedWithin(ctx, 10, repository.HistoryScope{})
1115	require.NoError(t, err)
1116	require.Len(t, mostVisited, 1, "unbounded most-visited must keep NULL last_visited rows")
1117
1118	matches, err := repo.Search(ctx, "legacy", 10, repository.HistoryScope{})
1119	require.NoError(t, err)
1120	require.Len(t, matches, 1, "unbounded search must keep NULL last_visited rows")
1121
1122	scoped, err := repo.GetRecent(ctx, 10, 0, repository.HistoryScope{Cutoff: cutoff})
1123	require.NoError(t, err)
1124	assert.Empty(t, scoped, "a bounded window must not include rows without a last_visited timestamp")
1125}
1126
1127func TestHistoryRepository_Search_FiltersBeforeLimitOnURLMatch(t *testing.T) {
1128	ctx := historyTestCtx()
1129	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1130	require.NoError(t, err)
1131	t.Cleanup(func() { _ = db.Close() })
1132	repo := sqlite.NewHistoryRepository(db)
1133
1134	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1135	cutoff := now.AddDate(0, 0, -30)
1136	// The old row outranks the recent row (higher visit count and domain boost),
1137	// so it would consume the single LIMIT slot if filtering happened after LIMIT.
1138	insertHistoryRow(t, ctx, db, "https://urlsecret.example.com/", "Old", 50, now.AddDate(0, 0, -40))
1139	insertHistoryRow(t, ctx, db, "https://urlsecret-recent.example.com/", "Recent", 1, now.AddDate(0, 0, -1))
1140
1141	results, err := repo.Search(ctx, "urlsecret", 1, repository.HistoryScope{Cutoff: cutoff})
1142	require.NoError(t, err)
1143	require.Len(t, results, 1, "URL FTS must filter by age before applying LIMIT")
1144	assert.Equal(t, "https://urlsecret-recent.example.com/", results[0].Entry.URL)
1145
1146	// Without a scope the old high-priority row wins the same query.
1147	all, err := repo.Search(ctx, "urlsecret", 10, repository.HistoryScope{})
1148	require.NoError(t, err)
1149	require.Len(t, all, 2)
1150	assert.Equal(t, "https://urlsecret.example.com/", all[0].Entry.URL)
1151}
1152
1153func TestHistoryRepository_Search_FiltersBeforeLimitOnTitleMatch(t *testing.T) {
1154	ctx := historyTestCtx()
1155	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1156	require.NoError(t, err)
1157	t.Cleanup(func() { _ = db.Close() })
1158	repo := sqlite.NewHistoryRepository(db)
1159
1160	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1161	cutoff := now.AddDate(0, 0, -30)
1162	// The query only matches titles, so only the title FTS path is exercised.
1163	// The old row has the higher visit count and would win an unfiltered LIMIT.
1164	insertHistoryRow(t, ctx, db, "https://old.example.com/ancient", "Titlesecret Old Guide", 50, now.AddDate(0, 0, -40))
1165	insertHistoryRow(t, ctx, db, "https://recent.example.com/entry", "Titlesecret Recent Guide", 1, now.AddDate(0, 0, -1))
1166
1167	results, err := repo.Search(ctx, "titlesecret", 1, repository.HistoryScope{Cutoff: cutoff})
1168	require.NoError(t, err)
1169	require.Len(t, results, 1, "title FTS must filter by age before applying LIMIT")
1170	assert.Equal(t, "https://recent.example.com/entry", results[0].Entry.URL)
1171
1172	all, err := repo.Search(ctx, "titlesecret", 10, repository.HistoryScope{})
1173	require.NoError(t, err)
1174	require.Len(t, all, 2)
1175}
1176
1177func TestHistoryRepository_GetRecent_BoundedLimitOffsetAndCutoff(t *testing.T) {
1178	ctx := historyTestCtx()
1179	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1180	require.NoError(t, err)
1181	t.Cleanup(func() { _ = db.Close() })
1182	repo := sqlite.NewHistoryRepository(db)
1183
1184	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1185	cutoff := now.AddDate(0, 0, -30)
1186	insertHistoryRow(t, ctx, db, "https://one.example.com", "One", 1, now.Add(-time.Hour))
1187	insertHistoryRow(t, ctx, db, "https://two.example.com", "Two", 1, now.Add(-2*time.Hour))
1188	insertHistoryRow(t, ctx, db, "https://three.example.com", "Three", 1, now.Add(-3*time.Hour))
1189	insertHistoryRow(t, ctx, db, "https://stale.example.com", "Stale", 1, now.AddDate(0, 0, -40))
1190
1191	scope := repository.HistoryScope{Cutoff: cutoff}
1192
1193	page, err := repo.GetRecent(ctx, 1, 1, scope)
1194	require.NoError(t, err)
1195	require.Len(t, page, 1)
1196	assert.Equal(t, "https://two.example.com", page[0].URL)
1197
1198	// A non-positive limit means all eligible rows, with the offset applied and
1199	// the stale row excluded by the cutoff.
1200	rest, err := repo.GetRecent(ctx, 0, 1, scope)
1201	require.NoError(t, err)
1202	require.Len(t, rest, 2)
1203	assert.Equal(t, []string{"https://two.example.com", "https://three.example.com"}, historyURLs(rest))
1204}
1205
1206func TestHistoryRepository_Search_BoundedScopeEmptyResult(t *testing.T) {
1207	ctx := historyTestCtx()
1208	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1209	require.NoError(t, err)
1210	t.Cleanup(func() { _ = db.Close() })
1211	repo := sqlite.NewHistoryRepository(db)
1212
1213	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1214	cutoff := now.AddDate(0, 0, -30)
1215	insertHistoryRow(t, ctx, db, "https://old.example.com/ancient", "Ephemeral Secret", 1, now.AddDate(0, 0, -40))
1216
1217	scoped, err := repo.Search(ctx, "ephemeral", 10, repository.HistoryScope{Cutoff: cutoff})
1218	require.NoError(t, err)
1219	assert.Empty(t, scoped, "a bounded search must not return age-excluded matches")
1220
1221	all, err := repo.Search(ctx, "ephemeral", 10, repository.HistoryScope{})
1222	require.NoError(t, err)
1223	require.Len(t, all, 1)
1224}
1225
1226func TestHistoryRepository_GetMostVisitedWithin_NonPositiveLimitIsEmpty(t *testing.T) {
1227	ctx := historyTestCtx()
1228	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1229	require.NoError(t, err)
1230	t.Cleanup(func() { _ = db.Close() })
1231	repo := sqlite.NewHistoryRepository(db)
1232
1233	now := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1234	insertHistoryRow(t, ctx, db, "https://example.com", "Example", 5, now.Add(-time.Hour))
1235
1236	unbounded, err := repo.GetMostVisitedWithin(ctx, 0, repository.HistoryScope{})
1237	require.NoError(t, err)
1238	assert.Empty(t, unbounded)
1239
1240	bounded, err := repo.GetMostVisitedWithin(ctx, -5, repository.HistoryScope{Cutoff: now.AddDate(0, 0, -30)})
1241	require.NoError(t, err)
1242	assert.Empty(t, bounded)
1243}
1244
1245// queryPlan records the actual driver's EXPLAIN QUERY PLAN output for a query.
1246func queryPlan(t *testing.T, ctx context.Context, db *sql.DB, query string, args ...any) []string {
1247	t.Helper()
1248
1249	rows, err := db.QueryContext(ctx, "EXPLAIN QUERY PLAN "+query, args...)
1250	require.NoError(t, err)
1251	defer func() { _ = rows.Close() }()
1252
1253	var details []string
1254	for rows.Next() {
1255		var id, parent, notUsed int
1256		var detail string
1257		require.NoError(t, rows.Scan(&id, &parent, &notUsed, &detail))
1258		details = append(details, detail)
1259	}
1260	require.NoError(t, rows.Err())
1261	return details
1262}
1263
1264func TestHistoryRepository_BoundedRecentPlanUsesCutoffIndex(t *testing.T) {
1265	ctx := historyTestCtx()
1266	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1267	require.NoError(t, err)
1268	t.Cleanup(func() { _ = db.Close() })
1269
1270	// Exercise the exact generated statement that historyRepo.GetRecent runs for
1271	// a bounded scope (see GetRecentHistorySinceCutoff), passed verbatim including
1272	// sqlc's leading `-- name:` comment. This fails to compile if the bounded
1273	// variant disappears, and the plan assertions fail if it regresses to a
1274	// nullable `(? IS NULL OR last_visited >= ?)` predicate, which plans as a
1275	// SCAN using idx_history_last_visited rather than a SEARCH.
1276	cutoff := time.Date(2026, 6, 24, 12, 0, 0, 0, time.UTC)
1277	plan := queryPlan(t, ctx, db, sqlc.GetRecentHistorySinceCutoff, sql.NullTime{Time: cutoff.UTC(), Valid: true}, 0, 50)
1278
1279	joined := strings.Join(plan, "\n")
1280	require.Contains(t, joined, "SEARCH", "bounded recent query must use an index search")
1281	require.Contains(t, joined, "idx_history_last_visited", "bounded recent query must use the last_visited index")
1282}
1283
1284func TestHistoryRepository_ScopeCutoffSubSecondNormalization(t *testing.T) {
1285	ctx := historyTestCtx()
1286	db, err := sqlite.NewConnection(ctx, filepath.Join(t.TempDir(), "dumber.db"))
1287	require.NoError(t, err)
1288	t.Cleanup(func() { _ = db.Close() })
1289	repo := sqlite.NewHistoryRepository(db)
1290
1291	// Stored at exactly 12:00:05 with no fractional part, matching
1292	// CURRENT_TIMESTAMP.
1293	stored := time.Date(2026, 6, 24, 12, 0, 5, 0, time.UTC)
1294	insertHistoryRow(t, ctx, db, "https://subsecond.example.com/marker", "Subsecond Marker", 1, stored)
1295	scope := repository.HistoryScope{Cutoff: stored.Add(500 * time.Millisecond)}
1296
1297	recent, err := repo.GetRecent(ctx, 10, 0, scope)
1298	require.NoError(t, err)
1299	require.Len(t, recent, 1, "a sub-second cutoff must normalize to the stored second and include the row")
1300	assert.Equal(t, "https://subsecond.example.com/marker", recent[0].URL)
1301
1302	mostVisited, err := repo.GetMostVisitedWithin(ctx, 10, scope)
1303	require.NoError(t, err)
1304	require.Len(t, mostVisited, 1)
1305
1306	matches, err := repo.Search(ctx, "subsecond", 10, scope)
1307	require.NoError(t, err)
1308	require.Len(t, matches, 1, "the shared FTS cutoff helper must apply the same normalization")
1309	assert.Equal(t, "https://subsecond.example.com/marker", matches[0].Entry.URL)
1310}