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