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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
package store
import (
"database/sql"
"time"
"task-dashboard/internal/models"
)
// SaveGoogleTasks replaces all cached Google Tasks
func (s *Store) SaveGoogleTasks(tasks []models.GoogleTask) error {
tx, err := s.db.Begin()
if err != nil {
return err
}
defer func() { _ = tx.Rollback() }()
if _, err := tx.Exec(`DELETE FROM google_tasks`); err != nil {
return err
}
stmt, err := tx.Prepare(`
INSERT INTO google_tasks (id, title, notes, status, completed, due_date, updated_at, list_id, url)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`)
if err != nil {
return err
}
defer func() { _ = stmt.Close() }()
for _, t := range tasks {
_, err = stmt.Exec(t.ID, t.Title, t.Notes, t.Status, t.Completed, t.DueDate, t.UpdatedAt, t.ListID, t.URL)
if err != nil {
return err
}
}
return tx.Commit()
}
// parseGoogleTaskTime parses a TEXT-column timestamp written by mattn/go-sqlite3's
// default time.Time binding. The google_tasks.due_date/updated_at columns are declared
// TEXT (not DATETIME), so the driver never auto-converts them back to time.Time on read.
func parseGoogleTaskTime(s string) (time.Time, bool) {
for _, layout := range []string{
"2006-01-02 15:04:05.999999999-07:00",
time.RFC3339Nano,
time.RFC3339,
"2006-01-02 15:04:05",
"2006-01-02",
} {
if parsed, err := time.Parse(layout, s); err == nil {
return parsed, true
}
}
return time.Time{}, false
}
func scanGoogleTasks(rows *sql.Rows) ([]models.GoogleTask, error) {
var tasks []models.GoogleTask
for rows.Next() {
var t models.GoogleTask
var dueDate, updatedAt sql.NullString
err := rows.Scan(&t.ID, &t.Title, &t.Notes, &t.Status, &t.Completed, &dueDate, &updatedAt, &t.ListID, &t.URL)
if err != nil {
return nil, err
}
if dueDate.Valid {
if parsed, ok := parseGoogleTaskTime(dueDate.String); ok {
t.DueDate = &parsed
}
}
if updatedAt.Valid {
if parsed, ok := parseGoogleTaskTime(updatedAt.String); ok {
t.UpdatedAt = parsed
}
}
tasks = append(tasks, t)
}
return tasks, rows.Err()
}
// GetGoogleTasks retrieves all cached Google Tasks
func (s *Store) GetGoogleTasks() ([]models.GoogleTask, error) {
rows, err := s.db.Query(`
SELECT id, title, notes, status, completed, due_date, updated_at, list_id, url
FROM google_tasks
ORDER BY completed ASC, CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC
`)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
return scanGoogleTasks(rows)
}
// GetGoogleTasksByDateRange retrieves cached Google Tasks due before end, including overdue
// tasks (due before start) so they keep appearing until completed.
func (s *Store) GetGoogleTasksByDateRange(start, end time.Time) ([]models.GoogleTask, error) {
rows, err := s.db.Query(`
SELECT id, title, notes, status, completed, due_date, updated_at, list_id, url
FROM google_tasks
WHERE due_date IS NULL OR due_date < ?
ORDER BY completed ASC, CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC
`, end)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
return scanGoogleTasks(rows)
}
|
