1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
package store
import "time"
type UserShoppingItem struct {
ID int64
Name string
Store string
Checked bool
CreatedAt time.Time
}
// SaveUserShoppingItem saves a new user shopping item
func (s *Store) SaveUserShoppingItem(name, store string) error {
_, err := s.db.Exec(`INSERT INTO user_shopping_items (name, store) VALUES (?, ?)`, name, store)
return err
}
// GetUserShoppingItems retrieves all user shopping items
func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
rows, err := s.db.Query(`SELECT id, name, store, checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
var items []UserShoppingItem
for rows.Next() {
var item UserShoppingItem
if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Checked, &item.CreatedAt); err != nil {
return nil, err
}
items = append(items, item)
}
return items, rows.Err()
}
// ToggleUserShoppingItem toggles the checked state of a user shopping item
func (s *Store) ToggleUserShoppingItem(id int64, checked bool) error {
_, err := s.db.Exec(`UPDATE user_shopping_items SET checked = ? WHERE id = ?`, checked, id)
return err
}
// DeleteUserShoppingItem removes a user shopping item
func (s *Store) DeleteUserShoppingItem(id int64) error {
_, err := s.db.Exec(`DELETE FROM user_shopping_items WHERE id = ?`, id)
return err
}
// SetShoppingItemChecked sets the checked state for an external shopping item
func (s *Store) SetShoppingItemChecked(source, itemID string, checked bool) error {
checkedInt := 0
if checked {
checkedInt = 1
}
_, err := s.db.Exec(`
INSERT INTO shopping_item_checks (source, item_id, checked, updated_at)
VALUES (?, ?, ?, CURRENT_TIMESTAMP)
ON CONFLICT(source, item_id) DO UPDATE SET checked = ?, updated_at = CURRENT_TIMESTAMP
`, source, itemID, checkedInt, checkedInt)
return err
}
// GetShoppingItemChecks returns a map of item_id -> checked for a given source
func (s *Store) GetShoppingItemChecks(source string) (map[string]bool, error) {
rows, err := s.db.Query(`SELECT item_id, checked FROM shopping_item_checks WHERE source = ?`, source)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
checks := make(map[string]bool)
for rows.Next() {
var itemID string
var checked int
if err := rows.Scan(&itemID, &checked); err != nil {
return nil, err
}
checks[itemID] = checked == 1
}
return checks, rows.Err()
}
|
