1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "net/url"
9 "sort"
10 "strings"
11 "sync/atomic"
12 "time"
13 "unicode"
14
15 "github.com/bnema/dumber/internal/domain/entity"
16 "github.com/bnema/dumber/internal/domain/repository"
17 domainurl "github.com/bnema/dumber/internal/domain/url"
18 "github.com/bnema/dumber/internal/infrastructure/persistence/sqlite/sqlc"
19 "github.com/bnema/dumber/internal/logging"
20)
21
22const logURLMaxLen = 60
23
24const (
25 historyURLMatchScore = 300.0
26 historyTitleMatchScore = 120.0
27 historyExactHostScore = 220.0
28 historyTrimmedHostScore = 215.0
29 historyHostPrefixScore = 180.0
30 historyURLPrefixScore = 170.0
31 historyURLContainsScore = 80.0
32 historyTitlePrefixScore = 90.0
33 historyTitleContainsScore = 40.0
34 historyWordHostPrefixScore = 40.0
35 historyWordURLContainsScore = 15.0
36 historyWordTitlePrefixScore = 20.0
37 historyWordTitleScore = 8.0
38 historyPathDepthPenalty = 18
39 historyRootPathBonus = 20.0
40 historyDomainBoostScore = 35.0
41 historyVisitCountCap = 50.0
42 historyRecentVisitScore = 10.0
43)
44
45var expectedFTSFailureCount atomic.Uint64
46
47// boundedHistoryCutoff normalizes a real cutoff to UTC at the second precision
48// SQLite stores in last_visited, so the inclusive `>=` comparison matches an
49// entry exactly on the cutoff.
50//
51// sqlc models the nullable last_visited column comparison as sql.NullTime, so
52// Valid is always true here: only bounded callers use this helper. Unbounded
53// recent/most-visited lists use separate queries with no cutoff predicate, and
54// the FTS path uses historyCutoffParam.
55func boundedHistoryCutoff(cutoff time.Time) sql.NullTime {
56 return sql.NullTime{Time: cutoff.UTC().Truncate(time.Second), Valid: true}
57}
58
59// historyCutoffParam returns nil for an unbounded scope and a concrete cutoff
60// otherwise. It feeds the FTS queries, where MATCH already narrows the
61// candidate set and a single nullable predicate avoids duplicating the URL and
62// title variants across bounded and unbounded windows. sqlc types that
63// predicate as any because the `IS NULL` branch prevents a concrete inference.
64func historyCutoffParam(scope repository.HistoryScope) any {
65 if scope.Cutoff.IsZero() {
66 return nil
67 }
68 return boundedHistoryCutoff(scope.Cutoff)
69}
70
71type historyRepo struct {
72 db *sql.DB
73 queries *sqlc.Queries
74}
75
76// NewHistoryRepository creates a new SQLite-backed history repository.
77func NewHistoryRepository(db *sql.DB) repository.HistoryRepository {
78 return &historyRepo{db: db, queries: sqlc.New(db)}
79}
80
81// aboutBlankURL is the special URL for blank pages that should not accumulate visit counts.
82const aboutBlankURL = "about:blank"
83
84func (r *historyRepo) Save(ctx context.Context, entry *entity.HistoryEntry) error {
85 log := logging.FromContext(ctx)
86 log.Debug().Str("url", logging.TruncateURL(entry.URL, logURLMaxLen)).Msg("saving history entry")
87
88 domain := domainurl.CanonicalDomain(entry.URL)
89 err := r.queries.UpsertHistory(ctx, sqlc.UpsertHistoryParams{
90 Url: entry.URL,
91 Title: sql.NullString{String: entry.Title, Valid: entry.Title != ""},
92 FaviconUrl: sql.NullString{String: entry.FaviconURL, Valid: entry.FaviconURL != ""},
93 Domain: sql.NullString{String: domain, Valid: domain != ""},
94 })
95 if err != nil {
96 return err
97 }
98
99 // Cap about:blank visit count to 1 so it exists but never dominates suggestions
100 if entry.URL == aboutBlankURL {
101 capErr := r.queries.CapVisitCount(ctx, sqlc.CapVisitCountParams{
102 VisitCount: sql.NullInt64{Int64: 1, Valid: true},
103 Url: aboutBlankURL,
104 VisitCount_2: sql.NullInt64{Int64: 1, Valid: true},
105 })
106 if capErr != nil {
107 log.Debug().Err(capErr).Msg("failed to cap about:blank visit count")
108 }
109 }
110
111 return nil
112}
113
114func (r *historyRepo) FindByURL(ctx context.Context, rawURL string) (*entity.HistoryEntry, error) {
115 row, err := r.queries.GetHistoryByURL(ctx, rawURL)
116 if err != nil {
117 if errors.Is(err, sql.ErrNoRows) {
118 return nil, nil
119 }
120 return nil, err
121 }
122 return historyFromRow(row), nil
123}
124
125func (r *historyRepo) UpdateMetadata(ctx context.Context, entry *entity.HistoryEntry) error {
126 if entry == nil || entry.URL == "" {
127 return fmt.Errorf("history entry URL cannot be empty")
128 }
129 _, err := r.db.ExecContext(
130 ctx,
131 `UPDATE history
132 SET title = COALESCE(?, title),
133 favicon_url = COALESCE(?, favicon_url)
134 WHERE url = ?`,
135 nullString(entry.Title),
136 nullString(entry.FaviconURL),
137 entry.URL,
138 )
139 return err
140}
141
142func (r *historyRepo) Search(ctx context.Context, query string, limit int, scope repository.HistoryScope) ([]entity.HistoryMatch, error) {
143 if limit <= 0 {
144 return []entity.HistoryMatch{}, nil
145 }
146
147 words := sanitizeFTS5QueryTokens(query)
148 if len(words) == 0 {
149 return []entity.HistoryMatch{}, nil
150 }
151
152 // Build FTS5 query: "word1* word2*" (implicit AND between terms)
153 parts := make([]string, len(words))
154 for i, word := range words {
155 parts[i] = word + "*"
156 }
157 ftsQuery := strings.Join(parts, " ")
158
159 // Search both URL and title columns, then merge results
160 // Use domain-boosted query for URL search to prioritize domain matches
161 // Use sanitized first word for domain boost (intentional: domain boost targets first search term)
162 urlRows, urlErr := r.queries.SearchHistoryFTSUrlWithDomainBoost(ctx, sqlc.SearchHistoryFTSUrlWithDomainBoostParams{
163 Term: sql.NullString{String: words[0], Valid: true},
164 Query: ftsQuery,
165 Cutoff: historyCutoffParam(scope),
166 Limit: int64(limit),
167 })
168 if urlErr != nil {
169 logFTSSearchError(ctx, "url", query, urlErr)
170 urlRows = []sqlc.SearchHistoryFTSUrlWithDomainBoostRow{}
171 }
172
173 titleRows, titleErr := r.queries.SearchHistoryFTSTitle(ctx, sqlc.SearchHistoryFTSTitleParams{
174 Query: ftsQuery,
175 Cutoff: historyCutoffParam(scope),
176 Limit: int64(limit),
177 })
178 if titleErr != nil {
179 logFTSSearchError(ctx, "title", query, titleErr)
180 titleRows = []sqlc.History{}
181 }
182
183 ranked := rankHistoryMatches(query, words, urlRows, titleRows)
184 if limit < len(ranked) {
185 ranked = ranked[:limit]
186 }
187 return ranked, nil
188}
189
190func historyFromDomainBoostRow(row sqlc.SearchHistoryFTSUrlWithDomainBoostRow) *entity.HistoryEntry {
191 return &entity.HistoryEntry{
192 ID: row.ID,
193 URL: row.Url,
194 Title: row.Title.String,
195 FaviconURL: row.FaviconUrl.String,
196 VisitCount: row.VisitCount.Int64,
197 LastVisited: row.LastVisited.Time,
198 CreatedAt: row.CreatedAt.Time,
199 }
200}
201
202func nullString(value string) sql.NullString {
203 return sql.NullString{String: value, Valid: value != ""}
204}
205
206type historySearchCandidate struct {
207 entry *entity.HistoryEntry
208 score float64
209 urlMatch bool
210 titleMatch bool
211 domainBoost int64
212 exactURLMatch bool
213 prefixURL bool
214 prefixTitle bool
215}
216
217func rankHistoryMatches(
218 query string,
219 words []string,
220 urlRows []sqlc.SearchHistoryFTSUrlWithDomainBoostRow,
221 titleRows []sqlc.History,
222) []entity.HistoryMatch {
223 candidates := make(map[int64]*historySearchCandidate, len(urlRows)+len(titleRows))
224
225 for i := range urlRows {
226 row := urlRows[i]
227 entry := historyFromDomainBoostRow(row)
228 candidate := ensureHistoryCandidate(candidates, entry)
229 candidate.urlMatch = true
230 candidate.domainBoost = maxInt64(candidate.domainBoost, row.DomainBoost)
231 }
232
233 for i := range titleRows {
234 row := titleRows[i]
235 entry := historyFromRow(row)
236 candidate := ensureHistoryCandidate(candidates, entry)
237 candidate.titleMatch = true
238 }
239
240 queryLower := strings.ToLower(strings.TrimSpace(query))
241 ranked := make([]rankedHistoryMatch, 0, len(candidates))
242 for _, candidate := range candidates {
243 candidate.score = computeHistoryCandidateScore(candidate, queryLower, words)
244 ranked = append(ranked, rankedHistoryMatch{
245 match: entity.HistoryMatch{
246 Entry: candidate.entry,
247 Score: candidate.score,
248 },
249 score: candidate.score,
250 exactURLMatch: candidate.exactURLMatch,
251 prefixURL: candidate.prefixURL,
252 urlMatch: candidate.urlMatch,
253 prefixTitle: candidate.prefixTitle,
254 })
255 }
256
257 sortRankedHistoryMatches(ranked)
258
259 matches := make([]entity.HistoryMatch, len(ranked))
260 for i := range ranked {
261 matches[i] = ranked[i].match
262 }
263 return matches
264}
265
266type rankedHistoryMatch struct {
267 match entity.HistoryMatch
268 score float64
269 exactURLMatch bool
270 prefixURL bool
271 urlMatch bool
272 prefixTitle bool
273}
274
275func sortRankedHistoryMatches(items []rankedHistoryMatch) {
276 sort.Slice(items, func(i, j int) bool {
277 li, ri := &items[i], &items[j]
278 if li.score != ri.score {
279 return li.score > ri.score
280 }
281 if li.exactURLMatch != ri.exactURLMatch {
282 return li.exactURLMatch
283 }
284 if li.prefixURL != ri.prefixURL {
285 return li.prefixURL
286 }
287 if li.urlMatch != ri.urlMatch {
288 return li.urlMatch
289 }
290 if li.prefixTitle != ri.prefixTitle {
291 return li.prefixTitle
292 }
293 if li.match.Entry.VisitCount != ri.match.Entry.VisitCount {
294 return li.match.Entry.VisitCount > ri.match.Entry.VisitCount
295 }
296 if !li.match.Entry.LastVisited.Equal(ri.match.Entry.LastVisited) {
297 return li.match.Entry.LastVisited.After(ri.match.Entry.LastVisited)
298 }
299 return li.match.Entry.URL < ri.match.Entry.URL
300 })
301}
302
303func ensureHistoryCandidate(candidates map[int64]*historySearchCandidate, entry *entity.HistoryEntry) *historySearchCandidate {
304 candidate, ok := candidates[entry.ID]
305 if ok {
306 return candidate
307 }
308 candidate = &historySearchCandidate{entry: entry}
309 candidates[entry.ID] = candidate
310 return candidate
311}
312
313func computeHistoryCandidateScore(candidate *historySearchCandidate, queryLower string, words []string) float64 {
314 if candidate == nil || candidate.entry == nil {
315 return 0
316 }
317
318 urlLower := strings.ToLower(candidate.entry.URL)
319 titleLower := strings.ToLower(candidate.entry.Title)
320 hostLower := strings.ToLower(domainurl.ExtractDomain(candidate.entry.URL))
321
322 score := 0.0
323 if candidate.urlMatch {
324 score += historyURLMatchScore
325 }
326 if candidate.titleMatch {
327 score += historyTitleMatchScore
328 }
329
330 score += scoreQueryMatch(candidate, queryLower, hostLower, urlLower, titleLower)
331 score += scoreHistoryWords(words, hostLower, urlLower, titleLower)
332
333 if looksLikeHostStyleQuery(queryLower) {
334 pathDepth := historyPathDepth(candidate.entry.URL)
335 score -= float64(pathDepth * historyPathDepthPenalty)
336 if pathDepth == 0 {
337 score += historyRootPathBonus
338 }
339 }
340
341 score += float64(candidate.domainBoost) * historyDomainBoostScore
342 score += minFloat64(float64(candidate.entry.VisitCount), historyVisitCountCap)
343 if time.Since(candidate.entry.LastVisited) <= 7*24*time.Hour {
344 score += historyRecentVisitScore
345 }
346 return score
347}
348
349func scoreQueryMatch(
350 candidate *historySearchCandidate,
351 queryLower, hostLower, urlLower, titleLower string,
352) float64 {
353 if queryLower == "" {
354 return 0
355 }
356
357 switch {
358 case hostLower == queryLower:
359 candidate.exactURLMatch = true
360 return historyExactHostScore + scoreTitleQueryMatch(candidate, queryLower, titleLower)
361 case strings.TrimPrefix(hostLower, "www.") == queryLower:
362 candidate.exactURLMatch = true
363 return historyTrimmedHostScore + scoreTitleQueryMatch(candidate, queryLower, titleLower)
364 case strings.HasPrefix(hostLower, queryLower):
365 candidate.prefixURL = true
366 return historyHostPrefixScore + scoreTitleQueryMatch(candidate, queryLower, titleLower)
367 case strings.HasPrefix(urlLower, queryLower):
368 candidate.prefixURL = true
369 return historyURLPrefixScore + scoreTitleQueryMatch(candidate, queryLower, titleLower)
370 case strings.Contains(urlLower, queryLower):
371 return historyURLContainsScore + scoreTitleQueryMatch(candidate, queryLower, titleLower)
372 default:
373 return scoreTitleQueryMatch(candidate, queryLower, titleLower)
374 }
375}
376
377func scoreTitleQueryMatch(candidate *historySearchCandidate, queryLower, titleLower string) float64 {
378 switch {
379 case strings.HasPrefix(titleLower, queryLower):
380 candidate.prefixTitle = true
381 return historyTitlePrefixScore
382 case strings.Contains(titleLower, queryLower):
383 return historyTitleContainsScore
384 default:
385 return 0
386 }
387}
388
389func scoreHistoryWords(words []string, hostLower, urlLower, titleLower string) float64 {
390 score := 0.0
391 for _, word := range words {
392 wordLower := strings.ToLower(word)
393 if wordLower == "" {
394 continue
395 }
396 if strings.HasPrefix(hostLower, wordLower) {
397 score += historyWordHostPrefixScore
398 } else if strings.Contains(urlLower, wordLower) {
399 score += historyWordURLContainsScore
400 }
401 if strings.HasPrefix(titleLower, wordLower) {
402 score += historyWordTitlePrefixScore
403 } else if strings.Contains(titleLower, wordLower) {
404 score += historyWordTitleScore
405 }
406 }
407 return score
408}
409
410func looksLikeHostStyleQuery(query string) bool {
411 return query != "" && !strings.ContainsAny(query, " /?#=&")
412}
413
414func historyPathDepth(rawURL string) int {
415 parsed, err := url.Parse(rawURL)
416 if err != nil {
417 return 0
418 }
419 trimmed := strings.Trim(parsed.Path, "/")
420 if trimmed == "" {
421 return 0
422 }
423 return len(strings.Split(trimmed, "/"))
424}
425
426func maxInt64(left, right int64) int64 {
427 if left > right {
428 return left
429 }
430 return right
431}
432
433func minFloat64(left, right float64) float64 {
434 if left < right {
435 return left
436 }
437 return right
438}
439
440// sanitizeFTS5QueryTokens normalizes separators and returns only valid FTS tokens.
441func sanitizeFTS5QueryTokens(query string) []string {
442 normalized := normalizeFTSSeparators(query)
443 rawTokens := strings.Fields(normalized)
444 if len(rawTokens) == 0 {
445 return nil
446 }
447
448 tokens := make([]string, 0, len(rawTokens))
449 for _, token := range rawTokens {
450 sanitized := sanitizeFTS5Word(token)
451 if sanitized == "" {
452 continue
453 }
454 upper := strings.ToUpper(sanitized)
455 if upper == "AND" || upper == "OR" || upper == "NOT" || upper == "NEAR" {
456 continue
457 }
458 tokens = append(tokens, sanitized)
459 }
460 return tokens
461}
462
463// normalizeFTSSeparators replaces non-alphanumeric separators with spaces.
464func normalizeFTSSeparators(s string) string {
465 var b strings.Builder
466 b.Grow(len(s))
467 for _, r := range s {
468 if unicode.IsLetter(r) || unicode.IsNumber(r) {
469 b.WriteRune(r)
470 } else {
471 b.WriteByte(' ')
472 }
473 }
474 return b.String()
475}
476
477// sanitizeFTS5Word keeps only alphanumeric runes in a single token.
478func sanitizeFTS5Word(word string) string {
479 var result strings.Builder
480 result.Grow(len(word))
481 for _, r := range word {
482 if unicode.IsLetter(r) || unicode.IsNumber(r) {
483 result.WriteRune(r)
484 }
485 }
486 return result.String()
487}
488
489func logFTSSearchError(ctx context.Context, part, query string, err error) {
490 log := logging.FromContext(ctx)
491 if isExpectedFTSError(err) {
492 count := expectedFTSFailureCount.Add(1)
493 if count == 1 || count%100 == 0 {
494 log.Debug().
495 Uint64("expected_error_count", count).
496 Str("part", part).
497 Msg("expected FTS errors observed")
498 }
499 return
500 }
501 log.Debug().Err(err).Str("part", part).Str("query", query).Msg("FTS search failed")
502}
503
504func isExpectedFTSError(err error) bool {
505 msg := strings.ToLower(err.Error())
506 return strings.Contains(msg, "malformed match") ||
507 strings.Contains(msg, "fts5: syntax error") ||
508 strings.Contains(msg, "unterminated string")
509}
510
511func (r *historyRepo) GetRecent(ctx context.Context, limit, offset int, scope repository.HistoryScope) ([]*entity.HistoryEntry, error) {
512 // SQLite treats a negative LIMIT as "no limit", which preserves the
513 // historical zero-limit-means-all behavior while applying the age scope.
514 sqlLimit := int64(limit)
515 if limit <= 0 {
516 sqlLimit = -1
517 }
518
519 // Bounded and unbounded windows use separate queries so the bounded path
520 // compares last_visited directly and keeps the index usable.
521 var (
522 rows []sqlc.History
523 err error
524 )
525 if scope.Cutoff.IsZero() {
526 rows, err = r.queries.GetRecentHistory(ctx, sqlc.GetRecentHistoryParams{
527 Limit: sqlLimit,
528 Offset: int64(offset),
529 })
530 } else {
531 rows, err = r.queries.GetRecentHistorySinceCutoff(ctx, sqlc.GetRecentHistorySinceCutoffParams{
532 Cutoff: boundedHistoryCutoff(scope.Cutoff),
533 Limit: sqlLimit,
534 Offset: int64(offset),
535 })
536 }
537 if err != nil {
538 return nil, err
539 }
540 return historyEntriesFromRows(rows), nil
541}
542
543func (r *historyRepo) GetRecentByDomain(ctx context.Context, domain string, limit, offset int) ([]*entity.HistoryEntry, error) {
544 rawDomain := domain
545 domain = domainurl.CanonicalDomain(domain)
546 if domain == "" {
547 return nil, fmt.Errorf("history domain is required: %q", rawDomain)
548 }
549 if limit <= 0 {
550 rows, err := r.queries.GetAllRecentHistoryByDomain(ctx, sql.NullString{String: domain, Valid: true})
551 if err != nil {
552 return nil, err
553 }
554 return historyEntriesFromRows(rows), nil
555 }
556 rows, err := r.queries.GetRecentHistoryByDomain(ctx, sqlc.GetRecentHistoryByDomainParams{
557 Domain: sql.NullString{String: domain, Valid: true},
558 Limit: int64(limit),
559 Offset: int64(offset),
560 })
561 if err != nil {
562 return nil, err
563 }
564 return historyEntriesFromRows(rows), nil
565}
566
567func (r *historyRepo) GetRecentWindow(ctx context.Context, before time.Time, beforeID int64, limit int) ([]*entity.HistoryEntry, error) {
568 if limit <= 0 {
569 return []*entity.HistoryEntry{}, nil
570 }
571 rows, err := r.queries.GetRecentHistoryWindow(ctx, sqlc.GetRecentHistoryWindowParams{
572 Before: sql.NullTime{Time: before, Valid: true},
573 BeforeID: beforeID,
574 Limit: int64(limit),
575 })
576 if err != nil {
577 return nil, err
578 }
579 return historyEntriesFromRows(rows), nil
580}
581
582func (r *historyRepo) GetRecentWindowByDomain(
583 ctx context.Context,
584 domain string,
585 before time.Time,
586 beforeID int64,
587 limit int,
588) ([]*entity.HistoryEntry, error) {
589 rawDomain := domain
590 domain = domainurl.CanonicalDomain(domain)
591 if domain == "" {
592 return nil, fmt.Errorf("history domain is required: %q", rawDomain)
593 }
594 if limit <= 0 {
595 return []*entity.HistoryEntry{}, nil
596 }
597 rows, err := r.queries.GetRecentHistoryWindowByDomain(ctx, sqlc.GetRecentHistoryWindowByDomainParams{
598 Domain: sql.NullString{String: domain, Valid: true},
599 Before: sql.NullTime{Time: before, Valid: true},
600 BeforeID: beforeID,
601 Limit: int64(limit),
602 })
603 if err != nil {
604 return nil, err
605 }
606 return historyEntriesFromRows(rows), nil
607}
608
609func (r *historyRepo) GetRecentSince(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
610 if days <= 0 {
611 return nil, fmt.Errorf("days must be positive, got %d", days)
612 }
613 // Format: "-N days" for SQLite datetime modifier
614 daysModifier := fmt.Sprintf("-%d days", days)
615 rows, err := r.queries.GetRecentHistorySince(ctx, daysModifier)
616 if err != nil {
617 return nil, err
618 }
619
620 return historyEntriesFromRows(rows), nil
621}
622
623func (r *historyRepo) GetMostVisited(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
624 if days <= 0 {
625 return nil, fmt.Errorf("days must be positive, got %d", days)
626 }
627 // Format: "-N days" for SQLite datetime modifier
628 daysModifier := fmt.Sprintf("-%d days", days)
629 rows, err := r.queries.GetMostVisited(ctx, daysModifier)
630 if err != nil {
631 return nil, err
632 }
633
634 return historyEntriesFromRows(rows), nil
635}
636
637func (r *historyRepo) GetAllRecentHistory(ctx context.Context) ([]*entity.HistoryEntry, error) {
638 rows, err := r.queries.GetAllRecentHistory(ctx)
639 if err != nil {
640 return nil, err
641 }
642
643 return historyEntriesFromRows(rows), nil
644}
645
646func (r *historyRepo) GetAllMostVisited(ctx context.Context) ([]*entity.HistoryEntry, error) {
647 rows, err := r.queries.GetAllMostVisited(ctx)
648 if err != nil {
649 return nil, err
650 }
651
652 return historyEntriesFromRows(rows), nil
653}
654
655func (r *historyRepo) GetMostVisitedWithin(
656 ctx context.Context,
657 limit int,
658 scope repository.HistoryScope,
659) ([]*entity.HistoryEntry, error) {
660 if limit <= 0 {
661 return []*entity.HistoryEntry{}, nil
662 }
663
664 // Bounded and unbounded windows use separate queries so the bounded path
665 // compares last_visited directly and keeps the index usable.
666 var (
667 rows []sqlc.History
668 err error
669 )
670 if scope.Cutoff.IsZero() {
671 rows, err = r.queries.GetMostVisitedHistory(ctx, int64(limit))
672 } else {
673 rows, err = r.queries.GetMostVisitedHistorySinceCutoff(ctx, sqlc.GetMostVisitedHistorySinceCutoffParams{
674 Cutoff: boundedHistoryCutoff(scope.Cutoff),
675 Limit: int64(limit),
676 })
677 }
678 if err != nil {
679 return nil, err
680 }
681
682 return historyEntriesFromRows(rows), nil
683}
684
685func (r *historyRepo) IncrementVisitCount(ctx context.Context, rawURL string) error {
686 // Skip incrementing about:blank - it should always have visit_count = 1
687 if rawURL == aboutBlankURL {
688 return nil
689 }
690 return r.queries.IncrementVisitCount(ctx, rawURL)
691}
692
693func (r *historyRepo) IncrementVisitCountBy(ctx context.Context, rawURL string, delta int) error {
694 // Skip incrementing about:blank - it should always have visit_count = 1
695 if rawURL == aboutBlankURL {
696 return nil
697 }
698 if delta <= 0 {
699 return fmt.Errorf("visit delta must be > 0, got %d", delta)
700 }
701 return r.queries.IncrementVisitCountByDelta(ctx, sqlc.IncrementVisitCountByDeltaParams{
702 VisitCount: sql.NullInt64{Int64: int64(delta), Valid: true},
703 Url: rawURL,
704 })
705}
706
707func (r *historyRepo) Delete(ctx context.Context, id int64) error {
708 return r.queries.DeleteHistoryByID(ctx, id)
709}
710
711func (r *historyRepo) DeleteOlderThan(ctx context.Context, before time.Time) error {
712 return r.queries.DeleteHistoryOlderThan(ctx, sql.NullTime{Time: before, Valid: true})
713}
714
715func (r *historyRepo) DeleteSince(ctx context.Context, since time.Time) error {
716 return r.queries.DeleteHistorySince(ctx, sql.NullTime{Time: since, Valid: true})
717}
718
719func (r *historyRepo) DeleteAll(ctx context.Context) error {
720 return r.queries.DeleteAllHistory(ctx)
721}
722
723func (r *historyRepo) DeleteByDomain(ctx context.Context, domain string) error {
724 rawDomain := domain
725 domain = domainurl.CanonicalDomain(domain)
726 if domain == "" {
727 return fmt.Errorf("history domain is required: %q", rawDomain)
728 }
729 return r.queries.DeleteHistoryByDomain(ctx, sql.NullString{String: domain, Valid: true})
730}
731
732func (r *historyRepo) GetStats(ctx context.Context) (*entity.HistoryStats, error) {
733 row, err := r.queries.GetHistoryStats(ctx)
734 if err != nil {
735 return nil, err
736 }
737
738 // Handle the interface{} type for total_visits
739 var totalVisits int64
740 switch v := row.TotalVisits.(type) {
741 case int64:
742 totalVisits = v
743 case float64:
744 totalVisits = int64(v)
745 }
746
747 return &entity.HistoryStats{
748 TotalEntries: row.TotalEntries,
749 TotalVisits: totalVisits,
750 UniqueDays: row.UniqueDays,
751 }, nil
752}
753
754func (r *historyRepo) GetDomainStats(ctx context.Context, limit int) ([]*entity.DomainStat, error) {
755 rows, err := r.queries.GetDomainStats(ctx, int64(limit))
756 if err != nil {
757 return nil, err
758 }
759
760 stats := make([]*entity.DomainStat, len(rows))
761 for i, row := range rows {
762 var totalVisits int64
763 if row.TotalVisits.Valid {
764 totalVisits = int64(row.TotalVisits.Float64)
765 }
766
767 var lastVisit time.Time
768 if v, ok := row.LastVisit.(string); ok && v != "" {
769 lastVisit, _ = time.Parse("2006-01-02 15:04:05", v)
770 }
771
772 stats[i] = &entity.DomainStat{
773 Domain: row.Domain,
774 PageCount: row.PageCount,
775 TotalVisits: totalVisits,
776 LastVisit: lastVisit,
777 }
778 }
779 return stats, nil
780}
781
782func (r *historyRepo) GetHourlyDistribution(ctx context.Context) ([]*entity.HourlyDistribution, error) {
783 rows, err := r.queries.GetHourlyDistribution(ctx)
784 if err != nil {
785 return nil, err
786 }
787
788 dist := make([]*entity.HourlyDistribution, len(rows))
789 for i, row := range rows {
790 dist[i] = &entity.HourlyDistribution{
791 Hour: int(row.Hour),
792 VisitCount: row.VisitCount,
793 }
794 }
795 return dist, nil
796}
797
798func (r *historyRepo) GetDailyVisitCount(ctx context.Context, daysAgo string) ([]*entity.DailyVisitCount, error) {
799 rows, err := r.queries.GetDailyVisitCount(ctx, daysAgo)
800 if err != nil {
801 return nil, err
802 }
803
804 counts := make([]*entity.DailyVisitCount, len(rows))
805 for i, row := range rows {
806 var day string
807 if v, ok := row.Day.(string); ok {
808 day = v
809 }
810
811 var visits int64
812 if row.Visits.Valid {
813 visits = int64(row.Visits.Float64)
814 }
815
816 counts[i] = &entity.DailyVisitCount{
817 Day: day,
818 Entries: row.Entries,
819 Visits: visits,
820 }
821 }
822 return counts, nil
823}
824
825func historyEntriesFromRows(rows []sqlc.History) []*entity.HistoryEntry {
826 entries := make([]*entity.HistoryEntry, len(rows))
827 for i := range rows {
828 entries[i] = historyFromRow(rows[i])
829 }
830 return entries
831}
832
833func historyFromRow(row sqlc.History) *entity.HistoryEntry {
834 return &entity.HistoryEntry{
835 ID: row.ID,
836 URL: row.Url,
837 Title: row.Title.String,
838 FaviconURL: row.FaviconUrl.String,
839 VisitCount: row.VisitCount.Int64,
840 LastVisited: row.LastVisited.Time,
841 CreatedAt: row.CreatedAt.Time,
842 }
843}