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