1package storage
2
3import (
4	"encoding/json"
5	"fmt"
6	"log"
7	"strings"
8	"time"
9
10	"github.com/nkanaev/yarr/src/content/htmlutil"
11)
12
13type ItemStatus int
14
15const (
16	UNREAD  ItemStatus = 0
17	READ    ItemStatus = 1
18	STARRED ItemStatus = 2
19)
20
21var StatusRepresentations = map[ItemStatus]string{
22	UNREAD:  "unread",
23	READ:    "read",
24	STARRED: "starred",
25}
26
27var StatusValues = map[string]ItemStatus{
28	"unread":  UNREAD,
29	"read":    READ,
30	"starred": STARRED,
31}
32
33func (s ItemStatus) MarshalJSON() ([]byte, error) {
34	return json.Marshal(StatusRepresentations[s])
35}
36
37func (s *ItemStatus) UnmarshalJSON(b []byte) error {
38	var str string
39	if err := json.Unmarshal(b, &str); err != nil {
40		return err
41	}
42	*s = StatusValues[str]
43	return nil
44}
45
46type Item struct {
47	Id       int64      `json:"id"`
48	GUID     string     `json:"guid"`
49	FeedId   int64      `json:"feed_id"`
50	Title    string     `json:"title"`
51	Link     string     `json:"link"`
52	Content  string     `json:"content,omitempty"`
53	Date     time.Time  `json:"date"`
54	Status   ItemStatus `json:"status"`
55	ImageURL *string    `json:"image"`
56	AudioURL *string    `json:"podcast_url"`
57}
58
59type ItemFilter struct {
60	FolderID *int64
61	FeedID   *int64
62	Status   *ItemStatus
63	Search   *string
64	After  *int64
65}
66
67type MarkFilter struct {
68	FolderID *int64
69	FeedID   *int64
70}
71
72func (s *Storage) CreateItems(items []Item) bool {
73	tx, err := s.db.Begin()
74	if err != nil {
75		log.Print(err)
76		return false
77	}
78
79	now := time.Now()
80
81	for _, item := range items {
82		_, err = tx.Exec(`
83			insert into items (
84				guid, feed_id, title, link, date,
85				content, image, podcast_url,
86				date_arrived, status
87			)
88			values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
89			on conflict (feed_id, guid) do nothing`,
90			item.GUID, item.FeedId, item.Title, item.Link, item.Date,
91			item.Content, item.ImageURL, item.AudioURL,
92			now, UNREAD,
93		)
94		if err != nil {
95			log.Print(err)
96			if err = tx.Rollback(); err != nil {
97				log.Print(err)
98				return false
99			}
100			return false
101		}
102	}
103	if err = tx.Commit(); err != nil {
104		log.Print(err)
105		return false
106	}
107	return true
108}
109
110func listQueryPredicate(filter ItemFilter, newestFirst bool) (string, []interface{}) {
111	cond := make([]string, 0)
112	args := make([]interface{}, 0)
113	if filter.FolderID != nil {
114		cond = append(cond, "i.feed_id in (select id from feeds where folder_id = ?)")
115		args = append(args, *filter.FolderID)
116	}
117	if filter.FeedID != nil {
118		cond = append(cond, "i.feed_id = ?")
119		args = append(args, *filter.FeedID)
120	}
121	if filter.Status != nil {
122		cond = append(cond, "i.status = ?")
123		args = append(args, *filter.Status)
124	}
125	if filter.Search != nil {
126		words := strings.Fields(*filter.Search)
127		terms := make([]string, len(words))
128		for idx, word := range words {
129			terms[idx] = word + "*"
130		}
131
132		cond = append(cond, "i.search_rowid in (select rowid from search where search match ?)")
133		args = append(args, strings.Join(terms, " "))
134	}
135	if filter.After != nil {
136		compare := ">"
137		if newestFirst {
138			compare = "<"
139		}
140		cond = append(cond, fmt.Sprintf("(i.date, i.id) %s (select date, id from items where id = ?)", compare))
141		args = append(args, *filter.After)
142	}
143
144	predicate := "1"
145	if len(cond) > 0 {
146		predicate = strings.Join(cond, " and ")
147	}
148
149	return predicate, args
150}
151
152func (s *Storage) ListItems(filter ItemFilter, limit int, newestFirst bool) []Item {
153	predicate, args := listQueryPredicate(filter, newestFirst)
154	result := make([]Item, 0, 0)
155
156	order := "date desc, id desc"
157	if !newestFirst {
158		order = "date asc, id asc"
159	}
160
161	query := fmt.Sprintf(`
162		select
163			i.id, i.guid, i.feed_id,
164			i.title, i.link, i.date,
165			i.status, i.image, i.podcast_url
166		from items i
167		where %s
168		order by %s
169		limit %d
170		`, predicate, order, limit)
171	rows, err := s.db.Query(query, args...)
172	if err != nil {
173		log.Print(err)
174		return result
175	}
176	for rows.Next() {
177		var x Item
178		err = rows.Scan(
179			&x.Id, &x.GUID, &x.FeedId,
180			&x.Title, &x.Link, &x.Date,
181			&x.Status, &x.ImageURL, &x.AudioURL,
182		)
183		if err != nil {
184			log.Print(err)
185			return result
186		}
187		result = append(result, x)
188	}
189	return result
190}
191
192func (s *Storage) GetItem(id int64) *Item {
193	i := &Item{}
194	err := s.db.QueryRow(`
195		select
196			i.id, i.guid, i.feed_id, i.title, i.link, i.content,
197			i.date, i.status, i.image, i.podcast_url
198		from items i
199		where i.id = ?
200	`, id).Scan(
201		&i.Id, &i.GUID, &i.FeedId, &i.Title, &i.Link, &i.Content,
202		&i.Date, &i.Status, &i.ImageURL, &i.AudioURL,
203	)
204	if err != nil {
205		log.Print(err)
206		return nil
207	}
208	return i
209}
210
211func (s *Storage) UpdateItemStatus(item_id int64, status ItemStatus) bool {
212	_, err := s.db.Exec(`update items set status = ? where id = ?`, status, item_id)
213	return err == nil
214}
215
216func (s *Storage) MarkItemsRead(filter MarkFilter) bool {
217	predicate, args := listQueryPredicate(ItemFilter{FolderID: filter.FolderID, FeedID: filter.FeedID}, false)
218	query := fmt.Sprintf(`
219		update items as i set status = %d
220		where %s and i.status != %d
221		`, READ, predicate, STARRED)
222	_, err := s.db.Exec(query, args...)
223	if err != nil {
224		log.Print(err)
225	}
226	return err == nil
227}
228
229type FeedStat struct {
230	FeedId       int64 `json:"feed_id"`
231	UnreadCount  int64 `json:"unread"`
232	StarredCount int64 `json:"starred"`
233}
234
235func (s *Storage) FeedStats() []FeedStat {
236	result := make([]FeedStat, 0)
237	rows, err := s.db.Query(fmt.Sprintf(`
238		select
239			feed_id,
240			sum(case status when %d then 1 else 0 end),
241			sum(case status when %d then 1 else 0 end)
242		from items
243		group by feed_id
244	`, UNREAD, STARRED))
245	if err != nil {
246		log.Print(err)
247		return result
248	}
249	for rows.Next() {
250		stat := FeedStat{}
251		rows.Scan(&stat.FeedId, &stat.UnreadCount, &stat.StarredCount)
252		result = append(result, stat)
253	}
254	return result
255}
256
257func (s *Storage) SyncSearch() {
258	rows, err := s.db.Query(`
259		select id, title, content
260		from items
261		where search_rowid is null;
262	`)
263	if err != nil {
264		log.Print(err)
265		return
266	}
267
268	items := make([]Item, 0)
269	for rows.Next() {
270		var item Item
271		rows.Scan(&item.Id, &item.Title, &item.Content)
272		items = append(items, item)
273	}
274
275	for _, item := range items {
276		result, err := s.db.Exec(`
277			insert into search (title, description, content) values (?, "", ?)`,
278			item.Title, htmlutil.ExtractText(item.Content),
279		)
280		if err != nil {
281			log.Print(err)
282			return
283		}
284		if numrows, err := result.RowsAffected(); err == nil && numrows == 1 {
285			if rowId, err := result.LastInsertId(); err == nil {
286				s.db.Exec(
287					`update items set search_rowid = ? where id = ?`,
288					rowId, item.Id,
289				)
290			}
291		}
292	}
293}
294
295func (s *Storage) DeleteOldItems() {
296	rows, err := s.db.Query(fmt.Sprintf(`
297		select feed_id, count(*) as num_items
298		from items
299		where status != %d
300		group by feed_id
301		having num_items > 50
302	`, STARRED))
303
304	if err != nil {
305		log.Print(err)
306		return
307	}
308
309	feedIds := make([]int64, 0)
310	for rows.Next() {
311		var id int64
312		rows.Scan(&id, nil)
313		feedIds = append(feedIds, id)
314	}
315
316	for _, feedId := range feedIds {
317		result, err := s.db.Exec(`
318			delete from items where feed_id = ? and status != ? and date_arrived < ?`,
319			feedId,
320			STARRED,
321			time.Now().Add(-time.Hour*24*90), // 90 days
322		)
323		if err != nil {
324			log.Print(err)
325			return
326		}
327		num, err := result.RowsAffected()
328		if err != nil {
329			log.Print(err)
330			return
331		}
332		if num > 0 {
333			log.Printf("Deleted %d old items (%d)", num, feedId)
334		}
335	}
336}
337