| 1 | package main |
| 2 | |
| 3 | import ( |
| 4 | "database/sql" |
| 5 | "errors" |
| 6 | "strings" |
| 7 | "time" |
| 8 | |
| 9 | "github.com/stevedylandev/andromeda/pkg/auth" |
| 10 | ) |
| 11 | |
| 12 | const habbitsSchema = ` |
| 13 | CREATE TABLE IF NOT EXISTS habits ( |
| 14 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 15 | short_id TEXT NOT NULL UNIQUE, |
| 16 | name TEXT NOT NULL, |
| 17 | value_type TEXT NOT NULL CHECK(value_type IN ('int','float','bool','string')), |
| 18 | unit TEXT, |
| 19 | description TEXT, |
| 20 | created_at INTEGER NOT NULL, |
| 21 | updated_at INTEGER NOT NULL |
| 22 | ); |
| 23 | CREATE TABLE IF NOT EXISTS records ( |
| 24 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 25 | short_id TEXT NOT NULL UNIQUE, |
| 26 | habit_id INTEGER NOT NULL REFERENCES habits(id) ON DELETE CASCADE, |
| 27 | value TEXT NOT NULL, |
| 28 | recorded_at INTEGER NOT NULL, |
| 29 | created_at INTEGER NOT NULL, |
| 30 | updated_at INTEGER NOT NULL |
| 31 | ); |
| 32 | CREATE INDEX IF NOT EXISTS idx_records_habit ON records(habit_id, recorded_at DESC); |
| 33 | ` |
| 34 | |
| 35 | type Habit struct { |
| 36 | ID int64 `json:"id"` |
| 37 | ShortID string `json:"short_id"` |
| 38 | Name string `json:"name"` |
| 39 | ValueType string `json:"value_type"` |
| 40 | Unit *string `json:"unit,omitempty"` |
| 41 | Description *string `json:"description,omitempty"` |
| 42 | CreatedAt int64 `json:"created_at"` |
| 43 | UpdatedAt int64 `json:"updated_at"` |
| 44 | } |
| 45 | |
| 46 | type Record struct { |
| 47 | ID int64 `json:"id"` |
| 48 | ShortID string `json:"short_id"` |
| 49 | HabitID int64 `json:"habit_id"` |
| 50 | Value string `json:"value"` |
| 51 | RecordedAt int64 `json:"recorded_at"` |
| 52 | CreatedAt int64 `json:"created_at"` |
| 53 | UpdatedAt int64 `json:"updated_at"` |
| 54 | // Populated by joined queries; not stored on the records table. |
| 55 | HabitShortID string `json:"habit_short_id,omitempty"` |
| 56 | HabitName string `json:"habit_name,omitempty"` |
| 57 | ValueType string `json:"value_type,omitempty"` |
| 58 | Unit string `json:"unit,omitempty"` |
| 59 | } |
| 60 | |
| 61 | const habitCols = `id, short_id, name, value_type, unit, description, created_at, updated_at` |
| 62 | |
| 63 | func scanHabit(s interface{ Scan(...any) error }) (*Habit, error) { |
| 64 | var h Habit |
| 65 | var unit, desc sql.NullString |
| 66 | err := s.Scan(&h.ID, &h.ShortID, &h.Name, &h.ValueType, &unit, &desc, &h.CreatedAt, &h.UpdatedAt) |
| 67 | if errors.Is(err, sql.ErrNoRows) { |
| 68 | return nil, nil |
| 69 | } |
| 70 | if err != nil { |
| 71 | return nil, err |
| 72 | } |
| 73 | if unit.Valid { |
| 74 | v := unit.String |
| 75 | h.Unit = &v |
| 76 | } |
| 77 | if desc.Valid { |
| 78 | v := desc.String |
| 79 | h.Description = &v |
| 80 | } |
| 81 | return &h, nil |
| 82 | } |
| 83 | |
| 84 | // listHabits returns all habits ordered by name, each annotated with its record |
| 85 | // count via a LEFT JOIN so habits with zero records still appear. |
| 86 | func listHabits(db *sql.DB) ([]Habit, map[int64]int, error) { |
| 87 | rows, err := db.Query(` |
| 88 | SELECT ` + prefixCols("h", habitCols) + `, COUNT(r.id) |
| 89 | FROM habits h |
| 90 | LEFT JOIN records r ON r.habit_id = h.id |
| 91 | GROUP BY h.id |
| 92 | ORDER BY h.name COLLATE NOCASE ASC`) |
| 93 | if err != nil { |
| 94 | return nil, nil, err |
| 95 | } |
| 96 | defer rows.Close() |
| 97 | var out []Habit |
| 98 | counts := map[int64]int{} |
| 99 | for rows.Next() { |
| 100 | var h Habit |
| 101 | var unit, desc sql.NullString |
| 102 | var count int |
| 103 | if err := rows.Scan(&h.ID, &h.ShortID, &h.Name, &h.ValueType, &unit, &desc, &h.CreatedAt, &h.UpdatedAt, &count); err != nil { |
| 104 | return nil, nil, err |
| 105 | } |
| 106 | if unit.Valid { |
| 107 | v := unit.String |
| 108 | h.Unit = &v |
| 109 | } |
| 110 | if desc.Valid { |
| 111 | v := desc.String |
| 112 | h.Description = &v |
| 113 | } |
| 114 | out = append(out, h) |
| 115 | counts[h.ID] = count |
| 116 | } |
| 117 | return out, counts, rows.Err() |
| 118 | } |
| 119 | |
| 120 | func getHabitByShortID(db *sql.DB, shortID string) (*Habit, error) { |
| 121 | return scanHabit(db.QueryRow(`SELECT `+habitCols+` FROM habits WHERE short_id = ?`, shortID)) |
| 122 | } |
| 123 | |
| 124 | func insertHabit(db *sql.DB, name, valueType, unit, description string) (int64, error) { |
| 125 | shortID, err := auth.GenerateShortID(10) |
| 126 | if err != nil { |
| 127 | return 0, err |
| 128 | } |
| 129 | now := time.Now().UTC().Unix() |
| 130 | res, err := db.Exec( |
| 131 | `INSERT INTO habits (short_id, name, value_type, unit, description, created_at, updated_at) |
| 132 | VALUES (?, ?, ?, ?, ?, ?, ?)`, |
| 133 | shortID, name, valueType, nullify(unit), nullify(description), now, now, |
| 134 | ) |
| 135 | if err != nil { |
| 136 | return 0, err |
| 137 | } |
| 138 | return res.LastInsertId() |
| 139 | } |
| 140 | |
| 141 | func updateHabit(db *sql.DB, shortID, name, valueType, unit, description string) error { |
| 142 | _, err := db.Exec( |
| 143 | `UPDATE habits SET name = ?, value_type = ?, unit = ?, description = ?, updated_at = ? |
| 144 | WHERE short_id = ?`, |
| 145 | name, valueType, nullify(unit), nullify(description), time.Now().UTC().Unix(), shortID, |
| 146 | ) |
| 147 | return err |
| 148 | } |
| 149 | |
| 150 | func deleteHabitByShortID(db *sql.DB, shortID string) error { |
| 151 | _, err := db.Exec(`DELETE FROM habits WHERE short_id = ?`, shortID) |
| 152 | return err |
| 153 | } |
| 154 | |
| 155 | const recordJoin = ` |
| 156 | SELECT r.id, r.short_id, r.habit_id, r.value, r.recorded_at, r.created_at, r.updated_at, |
| 157 | h.short_id, h.name, h.value_type, IFNULL(h.unit,'') |
| 158 | FROM records r JOIN habits h ON h.id = r.habit_id` |
| 159 | |
| 160 | func scanRecordJoined(s interface{ Scan(...any) error }) (*Record, error) { |
| 161 | var r Record |
| 162 | err := s.Scan(&r.ID, &r.ShortID, &r.HabitID, &r.Value, &r.RecordedAt, &r.CreatedAt, &r.UpdatedAt, |
| 163 | &r.HabitShortID, &r.HabitName, &r.ValueType, &r.Unit) |
| 164 | if errors.Is(err, sql.ErrNoRows) { |
| 165 | return nil, nil |
| 166 | } |
| 167 | if err != nil { |
| 168 | return nil, err |
| 169 | } |
| 170 | return &r, nil |
| 171 | } |
| 172 | |
| 173 | func queryRecords(db *sql.DB, where string, args ...any) ([]Record, error) { |
| 174 | rows, err := db.Query(recordJoin+" "+where, args...) |
| 175 | if err != nil { |
| 176 | return nil, err |
| 177 | } |
| 178 | defer rows.Close() |
| 179 | var out []Record |
| 180 | for rows.Next() { |
| 181 | rec, err := scanRecordJoined(rows) |
| 182 | if err != nil { |
| 183 | return nil, err |
| 184 | } |
| 185 | out = append(out, *rec) |
| 186 | } |
| 187 | return out, rows.Err() |
| 188 | } |
| 189 | |
| 190 | func listRecords(db *sql.DB, limit int) ([]Record, error) { |
| 191 | return queryRecords(db, `ORDER BY r.recorded_at DESC, r.id DESC LIMIT ?`, limit) |
| 192 | } |
| 193 | |
| 194 | func listRecordsForHabit(db *sql.DB, habitID int64) ([]Record, error) { |
| 195 | return queryRecords(db, `WHERE r.habit_id = ? ORDER BY r.recorded_at DESC, r.id DESC`, habitID) |
| 196 | } |
| 197 | |
| 198 | func getRecordByShortID(db *sql.DB, shortID string) (*Record, error) { |
| 199 | return scanRecordJoined(db.QueryRow(recordJoin+` WHERE r.short_id = ?`, shortID)) |
| 200 | } |
| 201 | |
| 202 | // recordsForExport returns records in chronological order for CSV export. When |
| 203 | // habitShortID is non-empty, only that habit's records are returned. |
| 204 | func recordsForExport(db *sql.DB, habitShortID string) ([]Record, error) { |
| 205 | if habitShortID != "" { |
| 206 | return queryRecords(db, `WHERE h.short_id = ? ORDER BY r.recorded_at ASC, r.id ASC`, habitShortID) |
| 207 | } |
| 208 | return queryRecords(db, `ORDER BY r.recorded_at ASC, r.id ASC`) |
| 209 | } |
| 210 | |
| 211 | func insertRecord(db *sql.DB, habitID int64, value string, recordedAt int64) (int64, error) { |
| 212 | shortID, err := auth.GenerateShortID(10) |
| 213 | if err != nil { |
| 214 | return 0, err |
| 215 | } |
| 216 | now := time.Now().UTC().Unix() |
| 217 | res, err := db.Exec( |
| 218 | `INSERT INTO records (short_id, habit_id, value, recorded_at, created_at, updated_at) |
| 219 | VALUES (?, ?, ?, ?, ?, ?)`, |
| 220 | shortID, habitID, value, recordedAt, now, now, |
| 221 | ) |
| 222 | if err != nil { |
| 223 | return 0, err |
| 224 | } |
| 225 | return res.LastInsertId() |
| 226 | } |
| 227 | |
| 228 | func updateRecord(db *sql.DB, shortID, value string, recordedAt int64) error { |
| 229 | _, err := db.Exec( |
| 230 | `UPDATE records SET value = ?, recorded_at = ?, updated_at = ? WHERE short_id = ?`, |
| 231 | value, recordedAt, time.Now().UTC().Unix(), shortID, |
| 232 | ) |
| 233 | return err |
| 234 | } |
| 235 | |
| 236 | func deleteRecordByShortID(db *sql.DB, shortID string) error { |
| 237 | _, err := db.Exec(`DELETE FROM records WHERE short_id = ?`, shortID) |
| 238 | return err |
| 239 | } |
| 240 | |
| 241 | // nullify returns nil for empty/whitespace strings so they store as SQL NULL. |
| 242 | func nullify(s string) any { |
| 243 | if strings.TrimSpace(s) == "" { |
| 244 | return nil |
| 245 | } |
| 246 | return s |
| 247 | } |
| 248 | |
| 249 | // prefixCols rewrites a comma column list like "id, short_id" into |
| 250 | // "h.id, h.short_id" for use in JOIN selects. |
| 251 | func prefixCols(alias, cols string) string { |
| 252 | parts := strings.Split(cols, ",") |
| 253 | for i, p := range parts { |
| 254 | parts[i] = " " + alias + "." + strings.TrimSpace(p) |
| 255 | } |
| 256 | return strings.Join(parts, ",") |
| 257 | } |