1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "time"
9
10 "github.com/bnema/dumber/internal/domain/entity"
11 "github.com/bnema/dumber/internal/domain/repository"
12 "github.com/bnema/dumber/internal/infrastructure/persistence/sqlite/sqlc"
13 "github.com/bnema/dumber/internal/logging"
14)
15
16type historyRepo struct {
17 queries *sqlc.Queries
18}
19
20// NewHistoryRepository creates a new SQLite-backed history repository.
21func NewHistoryRepository(db *sql.DB) repository.HistoryRepository {
22 return &historyRepo{queries: sqlc.New(db)}
23}
24
25func (r *historyRepo) Save(ctx context.Context, entry *entity.HistoryEntry) error {
26 log := logging.FromContext(ctx)
27 log.Debug().Str("url", entry.URL).Msg("saving history entry")
28
29 return r.queries.UpsertHistory(ctx, sqlc.UpsertHistoryParams{
30 Url: entry.URL,
31 Title: sql.NullString{String: entry.Title, Valid: entry.Title != ""},
32 FaviconUrl: sql.NullString{String: entry.FaviconURL, Valid: entry.FaviconURL != ""},
33 })
34}
35
36func (r *historyRepo) FindByURL(ctx context.Context, url string) (*entity.HistoryEntry, error) {
37 row, err := r.queries.GetHistoryByURL(ctx, url)
38 if err != nil {
39 if errors.Is(err, sql.ErrNoRows) {
40 return nil, nil
41 }
42 return nil, err
43 }
44 return historyFromRow(row), nil
45}
46
47func (r *historyRepo) Search(ctx context.Context, query string, limit int) ([]entity.HistoryMatch, error) {
48 log := logging.FromContext(ctx)
49
50 // Use FTS5 full-text search for better accuracy
51 // FTS5 query syntax: use * for prefix matching
52 ftsQuery := query + "*"
53
54 rows, err := r.queries.SearchHistoryFTS(ctx, sqlc.SearchHistoryFTSParams{
55 Url: ftsQuery,
56 Limit: int64(limit),
57 })
58 if err != nil {
59 log.Debug().Err(err).Str("query", query).Msg("FTS search failed, no results")
60 // FTS5 returns error on no match or invalid query, return empty results
61 return []entity.HistoryMatch{}, nil
62 }
63
64 matches := make([]entity.HistoryMatch, len(rows))
65 for i := range rows {
66 row := rows[i]
67 matches[i] = entity.HistoryMatch{
68 Entry: historyFromRow(row),
69 Score: 1.0, // FTS5 already ranked by bm25
70 }
71 }
72 return matches, nil
73}
74
75func (r *historyRepo) GetRecent(ctx context.Context, limit, offset int) ([]*entity.HistoryEntry, error) {
76 rows, err := r.queries.GetRecentHistory(ctx, sqlc.GetRecentHistoryParams{
77 Limit: int64(limit),
78 Offset: int64(offset),
79 })
80 if err != nil {
81 return nil, err
82 }
83
84 entries := make([]*entity.HistoryEntry, len(rows))
85 for i := range rows {
86 entries[i] = historyFromRow(rows[i])
87 }
88 return entries, nil
89}
90
91func (r *historyRepo) GetRecentSince(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
92 if days <= 0 {
93 return nil, fmt.Errorf("days must be positive, got %d", days)
94 }
95 // Format: "-N days" for SQLite datetime modifier
96 daysModifier := fmt.Sprintf("-%d days", days)
97 rows, err := r.queries.GetRecentHistorySince(ctx, daysModifier)
98 if err != nil {
99 return nil, err
100 }
101
102 entries := make([]*entity.HistoryEntry, len(rows))
103 for i := range rows {
104 entries[i] = historyFromRow(rows[i])
105 }
106 return entries, nil
107}
108
109func (r *historyRepo) GetMostVisited(ctx context.Context, days int) ([]*entity.HistoryEntry, error) {
110 if days <= 0 {
111 return nil, fmt.Errorf("days must be positive, got %d", days)
112 }
113 // Format: "-N days" for SQLite datetime modifier
114 daysModifier := fmt.Sprintf("-%d days", days)
115 rows, err := r.queries.GetMostVisited(ctx, daysModifier)
116 if err != nil {
117 return nil, err
118 }
119
120 entries := make([]*entity.HistoryEntry, len(rows))
121 for i := range rows {
122 entries[i] = historyFromRow(rows[i])
123 }
124 return entries, nil
125}
126
127func (r *historyRepo) GetAllRecentHistory(ctx context.Context) ([]*entity.HistoryEntry, error) {
128 rows, err := r.queries.GetAllRecentHistory(ctx)
129 if err != nil {
130 return nil, err
131 }
132
133 entries := make([]*entity.HistoryEntry, len(rows))
134 for i := range rows {
135 entries[i] = historyFromRow(rows[i])
136 }
137 return entries, nil
138}
139
140func (r *historyRepo) GetAllMostVisited(ctx context.Context) ([]*entity.HistoryEntry, error) {
141 rows, err := r.queries.GetAllMostVisited(ctx)
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) IncrementVisitCount(ctx context.Context, url string) error {
154 return r.queries.IncrementVisitCount(ctx, url)
155}
156
157func (r *historyRepo) Delete(ctx context.Context, id int64) error {
158 return r.queries.DeleteHistoryByID(ctx, id)
159}
160
161func (r *historyRepo) DeleteOlderThan(ctx context.Context, before time.Time) error {
162 return r.queries.DeleteHistoryOlderThan(ctx, sql.NullTime{Time: before, Valid: true})
163}
164
165func (r *historyRepo) DeleteAll(ctx context.Context) error {
166 return r.queries.DeleteAllHistory(ctx)
167}
168
169func (r *historyRepo) DeleteByDomain(ctx context.Context, domain string) error {
170 return r.queries.DeleteHistoryByDomain(ctx, sqlc.DeleteHistoryByDomainParams{
171 Column1: sql.NullString{String: domain, Valid: true},
172 Column2: sql.NullString{String: domain, Valid: true},
173 Column3: sql.NullString{String: domain, Valid: true},
174 Column4: sql.NullString{String: domain, Valid: true},
175 })
176}
177
178func (r *historyRepo) GetStats(ctx context.Context) (*entity.HistoryStats, error) {
179 row, err := r.queries.GetHistoryStats(ctx)
180 if err != nil {
181 return nil, err
182 }
183
184 // Handle the interface{} type for total_visits
185 var totalVisits int64
186 switch v := row.TotalVisits.(type) {
187 case int64:
188 totalVisits = v
189 case float64:
190 totalVisits = int64(v)
191 }
192
193 return &entity.HistoryStats{
194 TotalEntries: row.TotalEntries,
195 TotalVisits: totalVisits,
196 UniqueDays: row.UniqueDays,
197 }, nil
198}
199
200func (r *historyRepo) GetDomainStats(ctx context.Context, limit int) ([]*entity.DomainStat, error) {
201 rows, err := r.queries.GetDomainStats(ctx, int64(limit))
202 if err != nil {
203 return nil, err
204 }
205
206 stats := make([]*entity.DomainStat, len(rows))
207 for i, row := range rows {
208 var totalVisits int64
209 if row.TotalVisits.Valid {
210 totalVisits = int64(row.TotalVisits.Float64)
211 }
212
213 var lastVisit time.Time
214 if v, ok := row.LastVisit.(string); ok && v != "" {
215 lastVisit, _ = time.Parse("2006-01-02 15:04:05", v)
216 }
217
218 stats[i] = &entity.DomainStat{
219 Domain: row.Domain,
220 PageCount: row.PageCount,
221 TotalVisits: totalVisits,
222 LastVisit: lastVisit,
223 }
224 }
225 return stats, nil
226}
227
228func (r *historyRepo) GetHourlyDistribution(ctx context.Context) ([]*entity.HourlyDistribution, error) {
229 rows, err := r.queries.GetHourlyDistribution(ctx)
230 if err != nil {
231 return nil, err
232 }
233
234 dist := make([]*entity.HourlyDistribution, len(rows))
235 for i, row := range rows {
236 dist[i] = &entity.HourlyDistribution{
237 Hour: int(row.Hour),
238 VisitCount: row.VisitCount,
239 }
240 }
241 return dist, nil
242}
243
244func (r *historyRepo) GetDailyVisitCount(ctx context.Context, daysAgo string) ([]*entity.DailyVisitCount, error) {
245 rows, err := r.queries.GetDailyVisitCount(ctx, daysAgo)
246 if err != nil {
247 return nil, err
248 }
249
250 counts := make([]*entity.DailyVisitCount, len(rows))
251 for i, row := range rows {
252 var day string
253 if v, ok := row.Day.(string); ok {
254 day = v
255 }
256
257 var visits int64
258 if row.Visits.Valid {
259 visits = int64(row.Visits.Float64)
260 }
261
262 counts[i] = &entity.DailyVisitCount{
263 Day: day,
264 Entries: row.Entries,
265 Visits: visits,
266 }
267 }
268 return counts, nil
269}
270
271func historyFromRow(row sqlc.History) *entity.HistoryEntry {
272 return &entity.HistoryEntry{
273 ID: row.ID,
274 URL: row.Url,
275 Title: row.Title.String,
276 FaviconURL: row.FaviconUrl.String,
277 VisitCount: row.VisitCount.Int64,
278 LastVisited: row.LastVisited.Time,
279 CreatedAt: row.CreatedAt.Time,
280 }
281}