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