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