1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "strings"
9 "sync/atomic"
10 "time"
11 "unicode"
12
13 "github.com/bnema/dumber/internal/domain/entity"
14 "github.com/bnema/dumber/internal/domain/repository"
15 "github.com/bnema/dumber/internal/infrastructure/persistence/sqlite/sqlc"
16 "github.com/bnema/dumber/internal/logging"
17)
18
19const logURLMaxLen = 60
20
21var expectedFTSFailureCount atomic.Uint64
22
23type historyRepo struct {
24 queries *sqlc.Queries
25}
26
27// NewHistoryRepository creates a new SQLite-backed history repository.
28func NewHistoryRepository(db *sql.DB) repository.HistoryRepository {
29 return &historyRepo{queries: sqlc.New(db)}
30}
31
32// aboutBlankURL is the special URL for blank pages that should not accumulate visit counts.
33const aboutBlankURL = "about:blank"
34
35func (r *historyRepo) Save(ctx context.Context, entry *entity.HistoryEntry) error {
36 log := logging.FromContext(ctx)
37 log.Debug().Str("url", logging.TruncateURL(entry.URL, logURLMaxLen)).Msg("saving history entry")
38
39 err := r.queries.UpsertHistory(ctx, sqlc.UpsertHistoryParams{
40 Url: entry.URL,
41 Title: sql.NullString{String: entry.Title, Valid: entry.Title != ""},
42 FaviconUrl: sql.NullString{String: entry.FaviconURL, Valid: entry.FaviconURL != ""},
43 })
44 if err != nil {
45 return err
46 }
47
48 // Cap about:blank visit count to 1 so it exists but never dominates suggestions
49 if entry.URL == aboutBlankURL {
50 capErr := r.queries.CapVisitCount(ctx, sqlc.CapVisitCountParams{
51 VisitCount: sql.NullInt64{Int64: 1, Valid: true},
52 Url: aboutBlankURL,
53 VisitCount_2: sql.NullInt64{Int64: 1, Valid: true},
54 })
55 if capErr != nil {
56 log.Debug().Err(capErr).Msg("failed to cap about:blank visit count")
57 }
58 }
59
60 return nil
61}
62
63func (r *historyRepo) FindByURL(ctx context.Context, url string) (*entity.HistoryEntry, error) {
64 row, err := r.queries.GetHistoryByURL(ctx, url)
65 if err != nil {
66 if errors.Is(err, sql.ErrNoRows) {
67 return nil, nil
68 }
69 return nil, err
70 }
71 return historyFromRow(row), nil
72}
73
74func (r *historyRepo) Search(ctx context.Context, query string, limit int) ([]entity.HistoryMatch, error) {
75 words := sanitizeFTS5QueryTokens(query)
76 if len(words) == 0 {
77 return []entity.HistoryMatch{}, nil
78 }
79
80 // Build FTS5 query: "word1* word2*" (implicit AND between terms)
81 parts := make([]string, len(words))
82 for i, word := range words {
83 parts[i] = word + "*"
84 }
85 ftsQuery := strings.Join(parts, " ")
86
87 // Search both URL and title columns, then merge results
88 // Use domain-boosted query for URL search to prioritize domain matches
89 // Use sanitized first word for domain boost (intentional: domain boost targets first search term)
90 urlRows, urlErr := r.queries.SearchHistoryFTSUrlWithDomainBoost(ctx, sqlc.SearchHistoryFTSUrlWithDomainBoostParams{
91 Term: sql.NullString{String: words[0], Valid: true},
92 Query: ftsQuery,
93 Limit: int64(limit),
94 })
95 if urlErr != nil {
96 logFTSSearchError(ctx, "url", query, urlErr)
97 urlRows = []sqlc.SearchHistoryFTSUrlWithDomainBoostRow{}
98 }
99
100 titleRows, titleErr := r.queries.SearchHistoryFTSTitle(ctx, sqlc.SearchHistoryFTSTitleParams{
101 Query: ftsQuery,
102 Limit: int64(limit),
103 })
104 if titleErr != nil {
105 logFTSSearchError(ctx, "title", query, titleErr)
106 titleRows = []sqlc.History{}
107 }
108
109 // Merge results by interleaving URL and title matches for balanced results
110 seen := make(map[int64]bool)
111 var matches []entity.HistoryMatch
112
113 i, j := 0, 0
114 for len(matches) < limit && (i < len(urlRows) || j < len(titleRows)) {
115 // Take next URL match if available
116 if i < len(urlRows) {
117 row := urlRows[i]
118 i++
119 if !seen[row.ID] {
120 seen[row.ID] = true
121 matches = append(matches, entity.HistoryMatch{
122 Entry: historyFromDomainBoostRow(row),
123 Score: 1.0,
124 })
125 }
126 }
127
128 // Take next title match if available
129 if j < len(titleRows) && len(matches) < limit {
130 row := titleRows[j]
131 j++
132 if !seen[row.ID] {
133 seen[row.ID] = true
134 matches = append(matches, entity.HistoryMatch{
135 Entry: historyFromRow(row),
136 Score: 1.0,
137 })
138 }
139 }
140 }
141
142 return matches, nil
143}
144
145func historyFromDomainBoostRow(row sqlc.SearchHistoryFTSUrlWithDomainBoostRow) *entity.HistoryEntry {
146 return &entity.HistoryEntry{
147 ID: row.ID,
148 URL: row.Url,
149 Title: row.Title.String,
150 FaviconURL: row.FaviconUrl.String,
151 VisitCount: row.VisitCount.Int64,
152 LastVisited: row.LastVisited.Time,
153 CreatedAt: row.CreatedAt.Time,
154 }
155}
156
157// sanitizeFTS5QueryTokens normalizes separators and returns only valid FTS tokens.
158func sanitizeFTS5QueryTokens(query string) []string {
159 normalized := normalizeFTSSeparators(query)
160 rawTokens := strings.Fields(normalized)
161 if len(rawTokens) == 0 {
162 return nil
163 }
164
165 tokens := make([]string, 0, len(rawTokens))
166 for _, token := range rawTokens {
167 sanitized := sanitizeFTS5Word(token)
168 if sanitized == "" {
169 continue
170 }
171 upper := strings.ToUpper(sanitized)
172 if upper == "AND" || upper == "OR" || upper == "NOT" || upper == "NEAR" {
173 continue
174 }
175 tokens = append(tokens, sanitized)
176 }
177 return tokens
178}
179
180// normalizeFTSSeparators replaces non-alphanumeric separators with spaces.
181func normalizeFTSSeparators(s string) string {
182 var b strings.Builder
183 b.Grow(len(s))
184 for _, r := range s {
185 if unicode.IsLetter(r) || unicode.IsNumber(r) {
186 b.WriteRune(r)
187 } else {
188 b.WriteByte(' ')
189 }
190 }
191 return b.String()
192}
193
194// sanitizeFTS5Word keeps only alphanumeric runes in a single token.
195func sanitizeFTS5Word(word string) string {
196 var result strings.Builder
197 result.Grow(len(word))
198 for _, r := range word {
199 if unicode.IsLetter(r) || unicode.IsNumber(r) {
200 result.WriteRune(r)
201 }
202 }
203 return result.String()
204}
205
206func logFTSSearchError(ctx context.Context, part, query string, err error) {
207 log := logging.FromContext(ctx)
208 if isExpectedFTSError(err) {
209 count := expectedFTSFailureCount.Add(1)
210 if count == 1 || count%100 == 0 {
211 log.Debug().
212 Uint64("expected_error_count", count).
213 Str("part", part).
214 Msg("expected FTS errors observed")
215 }
216 return
217 }
218 log.Debug().Err(err).Str("part", part).Str("query", query).Msg("FTS search failed")
219}
220
221func isExpectedFTSError(err error) bool {
222 msg := strings.ToLower(err.Error())
223 return strings.Contains(msg, "malformed match") ||
224 strings.Contains(msg, "fts5: syntax error") ||
225 strings.Contains(msg, "unterminated string")
226}
227
228func (r *historyRepo) GetRecent(ctx context.Context, limit, offset int) ([]*entity.HistoryEntry, error) {
229 rows, err := r.queries.GetRecentHistory(ctx, sqlc.GetRecentHistoryParams{
230 Limit: int64(limit),
231 Offset: int64(offset),
232 })
233 if err != nil {
234 return nil, err
235 }
236
237 entries := make([]*entity.HistoryEntry, len(rows))
238 for i := range rows {
239 entries[i] = historyFromRow(rows[i])
240 }
241 return entries, nil
242}
243
244func (r *historyRepo) GetRecentSince(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
245 if days <= 0 {
246 return nil, fmt.Errorf("days must be positive, got %d", days)
247 }
248 // Format: "-N days" for SQLite datetime modifier
249 daysModifier := fmt.Sprintf("-%d days", days)
250 rows, err := r.queries.GetRecentHistorySince(ctx, daysModifier)
251 if err != nil {
252 return nil, err
253 }
254
255 entries := make([]*entity.HistoryEntry, len(rows))
256 for i := range rows {
257 entries[i] = historyFromRow(rows[i])
258 }
259 return entries, nil
260}
261
262func (r *historyRepo) GetMostVisited(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
263 if days <= 0 {
264 return nil, fmt.Errorf("days must be positive, got %d", days)
265 }
266 // Format: "-N days" for SQLite datetime modifier
267 daysModifier := fmt.Sprintf("-%d days", days)
268 rows, err := r.queries.GetMostVisited(ctx, daysModifier)
269 if err != nil {
270 return nil, err
271 }
272
273 entries := make([]*entity.HistoryEntry, len(rows))
274 for i := range rows {
275 entries[i] = historyFromRow(rows[i])
276 }
277 return entries, nil
278}
279
280func (r *historyRepo) GetAllRecentHistory(ctx context.Context) ([]*entity.HistoryEntry, error) {
281 rows, err := r.queries.GetAllRecentHistory(ctx)
282 if err != nil {
283 return nil, err
284 }
285
286 entries := make([]*entity.HistoryEntry, len(rows))
287 for i := range rows {
288 entries[i] = historyFromRow(rows[i])
289 }
290 return entries, nil
291}
292
293func (r *historyRepo) GetAllMostVisited(ctx context.Context) ([]*entity.HistoryEntry, error) {
294 rows, err := r.queries.GetAllMostVisited(ctx)
295 if err != nil {
296 return nil, err
297 }
298
299 entries := make([]*entity.HistoryEntry, len(rows))
300 for i := range rows {
301 entries[i] = historyFromRow(rows[i])
302 }
303 return entries, nil
304}
305
306func (r *historyRepo) IncrementVisitCount(ctx context.Context, url string) error {
307 // Skip incrementing about:blank - it should always have visit_count = 1
308 if url == aboutBlankURL {
309 return nil
310 }
311 return r.queries.IncrementVisitCount(ctx, url)
312}
313
314func (r *historyRepo) IncrementVisitCountBy(ctx context.Context, url string, delta int) error {
315 // Skip incrementing about:blank - it should always have visit_count = 1
316 if url == aboutBlankURL {
317 return nil
318 }
319 if delta <= 0 {
320 return fmt.Errorf("visit delta must be > 0, got %d", delta)
321 }
322 return r.queries.IncrementVisitCountByDelta(ctx, sqlc.IncrementVisitCountByDeltaParams{
323 VisitCount: sql.NullInt64{Int64: int64(delta), Valid: true},
324 Url: url,
325 })
326}
327
328func (r *historyRepo) Delete(ctx context.Context, id int64) error {
329 return r.queries.DeleteHistoryByID(ctx, id)
330}
331
332func (r *historyRepo) DeleteOlderThan(ctx context.Context, before time.Time) error {
333 return r.queries.DeleteHistoryOlderThan(ctx, sql.NullTime{Time: before, Valid: true})
334}
335
336func (r *historyRepo) DeleteAll(ctx context.Context) error {
337 return r.queries.DeleteAllHistory(ctx)
338}
339
340func (r *historyRepo) DeleteByDomain(ctx context.Context, domain string) error {
341 return r.queries.DeleteHistoryByDomain(ctx, sqlc.DeleteHistoryByDomainParams{
342 Column1: sql.NullString{String: domain, Valid: true},
343 Column2: sql.NullString{String: domain, Valid: true},
344 Column3: sql.NullString{String: domain, Valid: true},
345 Column4: sql.NullString{String: domain, Valid: true},
346 })
347}
348
349func (r *historyRepo) GetStats(ctx context.Context) (*entity.HistoryStats, error) {
350 row, err := r.queries.GetHistoryStats(ctx)
351 if err != nil {
352 return nil, err
353 }
354
355 // Handle the interface{} type for total_visits
356 var totalVisits int64
357 switch v := row.TotalVisits.(type) {
358 case int64:
359 totalVisits = v
360 case float64:
361 totalVisits = int64(v)
362 }
363
364 return &entity.HistoryStats{
365 TotalEntries: row.TotalEntries,
366 TotalVisits: totalVisits,
367 UniqueDays: row.UniqueDays,
368 }, nil
369}
370
371func (r *historyRepo) GetDomainStats(ctx context.Context, limit int) ([]*entity.DomainStat, error) {
372 rows, err := r.queries.GetDomainStats(ctx, int64(limit))
373 if err != nil {
374 return nil, err
375 }
376
377 stats := make([]*entity.DomainStat, len(rows))
378 for i, row := range rows {
379 var totalVisits int64
380 if row.TotalVisits.Valid {
381 totalVisits = int64(row.TotalVisits.Float64)
382 }
383
384 var lastVisit time.Time
385 if v, ok := row.LastVisit.(string); ok && v != "" {
386 lastVisit, _ = time.Parse("2006-01-02 15:04:05", v)
387 }
388
389 stats[i] = &entity.DomainStat{
390 Domain: row.Domain,
391 PageCount: row.PageCount,
392 TotalVisits: totalVisits,
393 LastVisit: lastVisit,
394 }
395 }
396 return stats, nil
397}
398
399func (r *historyRepo) GetHourlyDistribution(ctx context.Context) ([]*entity.HourlyDistribution, error) {
400 rows, err := r.queries.GetHourlyDistribution(ctx)
401 if err != nil {
402 return nil, err
403 }
404
405 dist := make([]*entity.HourlyDistribution, len(rows))
406 for i, row := range rows {
407 dist[i] = &entity.HourlyDistribution{
408 Hour: int(row.Hour),
409 VisitCount: row.VisitCount,
410 }
411 }
412 return dist, nil
413}
414
415func (r *historyRepo) GetDailyVisitCount(ctx context.Context, daysAgo string) ([]*entity.DailyVisitCount, error) {
416 rows, err := r.queries.GetDailyVisitCount(ctx, daysAgo)
417 if err != nil {
418 return nil, err
419 }
420
421 counts := make([]*entity.DailyVisitCount, len(rows))
422 for i, row := range rows {
423 var day string
424 if v, ok := row.Day.(string); ok {
425 day = v
426 }
427
428 var visits int64
429 if row.Visits.Valid {
430 visits = int64(row.Visits.Float64)
431 }
432
433 counts[i] = &entity.DailyVisitCount{
434 Day: day,
435 Entries: row.Entries,
436 Visits: visits,
437 }
438 }
439 return counts, nil
440}
441
442func historyFromRow(row sqlc.History) *entity.HistoryEntry {
443 return &entity.HistoryEntry{
444 ID: row.ID,
445 URL: row.Url,
446 Title: row.Title.String,
447 FaviconURL: row.FaviconUrl.String,
448 VisitCount: row.VisitCount.Int64,
449 LastVisited: row.LastVisited.Time,
450 CreatedAt: row.CreatedAt.Time,
451 }
452}