src/database/exercises.go (view raw)
1package database
2
3import (
4 "encoding/json"
5 "errors"
6 "fmt"
7 "io"
8 "log"
9 "net/http"
10 "strings"
11 "time"
12)
13
14// ImportedExercise represents the JSON structure from the input file
15type importedExercise struct {
16 Name string `json:"name"`
17 Level string `json:"level"`
18 Category string `json:"category"`
19 Force *string `json:"force"`
20 Mechanic *string `json:"mechanic"`
21 Equipment *string `json:"equipment"`
22 PrimaryMuscles []string `json:"primaryMuscles"`
23 SecondaryMuscles []string `json:"secondaryMuscles"`
24 Instructions []string `json:"instructions"`
25 Images []string `json:"images"`
26 ID string `json:"id"`
27}
28
29// upsertLookupEntities creates or updates all lookup table entries and returns lookup maps
30func (db *Database) upsertLookupEntities(exercises []importedExercise) (map[string]uint, error) {
31 // Collect unique values
32 uniqueValues := make(map[string]bool)
33
34 // Extract unique values from exercises
35 for _, exercise := range exercises {
36 for _, muscle := range exercise.PrimaryMuscles {
37 uniqueValues[muscle] = true
38 }
39 for _, muscle := range exercise.SecondaryMuscles {
40 uniqueValues[muscle] = true
41 }
42 }
43
44 // Upsert lookup entities in database
45 if err := db.upsertMuscles(uniqueValues); err != nil {
46 return nil, err
47 }
48
49 // Build lookup map
50 muscleLookupMap, err := db.buildMuscleLookupMap(uniqueValues)
51 if err != nil {
52 return nil, err
53 }
54
55 log.Printf("Upserted lookup entities: %d muscles", len(uniqueValues))
56
57 return muscleLookupMap, nil
58}
59
60func (db *Database) upsertMuscles(muscles map[string]bool) error {
61 for name := range muscles {
62 muscle := Muscle{Name: name}
63 if err := db.Where("name = ?", name).FirstOrCreate(&muscle).Error; err != nil {
64 return fmt.Errorf("failed to upsert muscle %s: %w", name, err)
65 }
66 }
67 return nil
68}
69
70// buildLookupMaps populates the lookup maps with IDs from the database
71func (db *Database) buildMuscleLookupMap(uniqueValues map[string]bool) (map[string]uint, error) {
72 // Build muscles map
73 maps := make(map[string]uint)
74 for name := range uniqueValues {
75 var muscle Muscle
76 if err := db.Where("name = ?", name).First(&muscle).Error; err != nil {
77 return nil, fmt.Errorf("failed to find muscle %s: %w", name, err)
78 }
79 maps[name] = muscle.ID
80 }
81
82 return maps, nil
83}
84
85// upsertExercise creates or updates a single exercise with all its related data
86func (db *Database) upsertExercise(importedExercise importedExercise, lookupMap map[string]uint) (didSave, isUpdate bool, err error) {
87 // First, try to find existing exercise by name
88 var existingExercise Exercise
89 result := db.Where("name = ?", importedExercise.Name).Preload("PrimaryMuscles").Preload("SecondaryMuscles").First(&existingExercise)
90
91 // Create new exercise with basic info
92 exercise := Exercise{
93 Name: importedExercise.Name,
94 Level: importedExercise.Level,
95 Category: importedExercise.Category,
96 }
97
98 if importedExercise.Force != nil && *importedExercise.Force != "" {
99 exercise.Force = importedExercise.Force
100 }
101 if importedExercise.Mechanic != nil && *importedExercise.Mechanic != "" {
102 exercise.Mechanic = importedExercise.Mechanic
103 }
104 if importedExercise.Equipment != nil && *importedExercise.Equipment != "" {
105 exercise.Equipment = importedExercise.Equipment
106 }
107
108 if len(importedExercise.Instructions) > 0 {
109 // Filter out empty instructions
110 var filteredInstructions []string
111 for _, instruction := range importedExercise.Instructions {
112 clean := strings.TrimSpace(instruction)
113 if clean != "" {
114 filteredInstructions = append(filteredInstructions, clean)
115 }
116 }
117 instructions := strings.Join(filteredInstructions, "\n")
118 exercise.Instructions = &instructions
119 }
120
121 var exerciseID uint
122 var exerciseDataChanged bool
123 var muscleAssociationsChanged bool
124
125 if result.Error == nil {
126 // Exercise exists, check if it needs updating
127 isUpdate = true
128 exerciseID = existingExercise.ID
129 exercise.ID = exerciseID
130 exercise.CreatedAt = existingExercise.CreatedAt // Preserve creation time
131
132 // Check if the exercise data has actually changed
133 exerciseDataChanged = db.exerciseDataChanged(existingExercise, exercise)
134
135 // Check if muscle associations have changed
136 muscleAssociationsChanged = db.muscleAssociationsChanged(existingExercise, importedExercise, lookupMap)
137
138 // Only update if something has changed
139 if exerciseDataChanged {
140 if err := db.Save(&exercise).Error; err != nil {
141 return false, false, fmt.Errorf("failed to update exercise: %w", err)
142 }
143 didSave = true
144 }
145 } else {
146 // Exercise doesn't exist, create it
147 isUpdate = false
148 exerciseDataChanged = true // New exercise, so data is "changed"
149 muscleAssociationsChanged = true // New exercise, so associations are "changed"
150
151 if err := db.Create(&exercise).Error; err != nil {
152 return false, false, fmt.Errorf("failed to create exercise: %w", err)
153 }
154 exerciseID = exercise.ID
155 didSave = true
156 }
157
158 // Only update muscle associations if they've changed
159 if muscleAssociationsChanged {
160 if err := db.updateMuscleAssociations(exerciseID, importedExercise, lookupMap); err != nil {
161 return false, false, fmt.Errorf("failed to update muscle associations: %w", err)
162 }
163 didSave = true
164 }
165
166 return
167}
168
169// exerciseDataChanged compares two exercises to see if core data has changed
170func (db *Database) exerciseDataChanged(existing, new Exercise) bool {
171 return existing.Level != new.Level ||
172 existing.Category != new.Category ||
173 !stringPointersEqual(existing.Force, new.Force) ||
174 !stringPointersEqual(existing.Mechanic, new.Mechanic) ||
175 !stringPointersEqual(existing.Equipment, new.Equipment) ||
176 !stringPointersEqual(existing.Instructions, new.Instructions)
177}
178
179// Helper function to compare string pointers
180func stringPointersEqual(a, b *string) bool {
181 if a == nil && b == nil {
182 return true
183 }
184 if a == nil || b == nil {
185 return false
186 }
187 return *a == *b
188}
189
190// muscleAssociationsChanged compares existing muscle associations with imported data
191func (db *Database) muscleAssociationsChanged(existing Exercise, imported importedExercise, lookupMap map[string]uint) bool {
192 // Convert existing muscle associations to sets for comparison
193 existingPrimary := make(map[uint]bool)
194 existingSecondary := make(map[uint]bool)
195
196 for _, muscle := range existing.PrimaryMuscles {
197 existingPrimary[muscle.ID] = true
198 }
199 for _, muscle := range existing.SecondaryMuscles {
200 existingSecondary[muscle.ID] = true
201 }
202
203 // Convert imported muscle names to IDs and create sets
204 importedPrimary := make(map[uint]bool)
205 importedSecondary := make(map[uint]bool)
206
207 for _, muscleName := range imported.PrimaryMuscles {
208 if muscleID, ok := lookupMap[muscleName]; ok {
209 importedPrimary[muscleID] = true
210 }
211 }
212 for _, muscleName := range imported.SecondaryMuscles {
213 if muscleID, ok := lookupMap[muscleName]; ok {
214 importedSecondary[muscleID] = true
215 }
216 }
217
218 // Compare primary muscles
219 if len(existingPrimary) != len(importedPrimary) {
220 return true
221 }
222 for muscleID := range existingPrimary {
223 if !importedPrimary[muscleID] {
224 return true
225 }
226 }
227
228 // Compare secondary muscles
229 if len(existingSecondary) != len(importedSecondary) {
230 return true
231 }
232 for muscleID := range existingSecondary {
233 if !importedSecondary[muscleID] {
234 return true
235 }
236 }
237
238 return false
239}
240
241// updateMuscleAssociations replaces muscle associations for an exercise
242func (db *Database) updateMuscleAssociations(exerciseID uint, importedExercise importedExercise, lookupMap map[string]uint) error {
243 exercise := Exercise{ID: exerciseID}
244
245 // Clear existing associations
246 if err := db.Model(&exercise).Association("PrimaryMuscles").Clear(); err != nil {
247 return fmt.Errorf("failed to clear primary muscles: %w", err)
248 }
249 if err := db.Model(&exercise).Association("SecondaryMuscles").Clear(); err != nil {
250 return fmt.Errorf("failed to clear secondary muscles: %w", err)
251 }
252
253 // Add primary muscles
254 var primaryMuscles []Muscle
255 for _, muscleName := range importedExercise.PrimaryMuscles {
256 if muscleID, ok := lookupMap[muscleName]; ok {
257 primaryMuscles = append(primaryMuscles, Muscle{ID: muscleID})
258 }
259 }
260 if len(primaryMuscles) > 0 {
261 if err := db.Model(&exercise).Association("PrimaryMuscles").Append(&primaryMuscles); err != nil {
262 return fmt.Errorf("failed to add primary muscles: %w", err)
263 }
264 }
265
266 // Add secondary muscles
267 var secondaryMuscles []Muscle
268 for _, muscleName := range importedExercise.SecondaryMuscles {
269 if muscleID, ok := lookupMap[muscleName]; ok {
270 secondaryMuscles = append(secondaryMuscles, Muscle{ID: muscleID})
271 }
272 }
273 if len(secondaryMuscles) > 0 {
274 if err := db.Model(&exercise).Association("SecondaryMuscles").Append(&secondaryMuscles); err != nil {
275 return fmt.Errorf("failed to add secondary muscles: %w", err)
276 }
277 }
278
279 return nil
280}
281
282// downloadExercises downloads exercises from the JSON URL
283func downloadExercises() ([]importedExercise, error) {
284 // Download exercises.json from the URL
285 resp, err := http.Get(jsonURL)
286 if err != nil {
287 return nil, fmt.Errorf("failed to download exercises.json: %w", err)
288 }
289 defer resp.Body.Close()
290
291 // Check if the request was successful
292 if resp.StatusCode != http.StatusOK {
293 return nil, fmt.Errorf("failed to download exercises.json: HTTP status %d", resp.StatusCode)
294 }
295
296 // Read the response body
297 fileData, err := io.ReadAll(resp.Body)
298 if err != nil {
299 return nil, fmt.Errorf("failed to read response body: %w", err)
300 }
301
302 var exercises []importedExercise
303 if err := json.Unmarshal(fileData, &exercises); err != nil {
304 return nil, fmt.Errorf("failed to parse exercises.json: %w", err)
305 }
306
307 return exercises, nil
308}
309
310const (
311 dbDir = "data"
312 dbName = "fitness.sqlite"
313 baseURL = "https://raw.githubusercontent.com/yuhonas/free-exercise-db/main/"
314 jsonURL = baseURL + "dist/exercises.json"
315 imageFormat = baseURL + "exercises/%s/%d.jpg"
316 imageAmount = 2
317)
318
319var (
320 idReplacer = strings.NewReplacer(
321 " ", "_",
322 "/", "_",
323 ",", "",
324 "(", "",
325 ")", "",
326 "-", "-",
327 "'", "",
328 )
329 lastUpdate = time.Date(1970, 1, 1, 0, 0, 0, 0, time.UTC)
330)
331
332func (e Exercise) StringID() string {
333 return idReplacer.Replace(e.Name)
334}
335
336func (e Exercise) Images() (images []string) {
337 id := e.StringID()
338
339 for i := range imageAmount {
340 images = append(images, fmt.Sprintf(imageFormat, id, i))
341 }
342 return
343}
344
345func (db *Database) UpdateExercises() (err error) {
346 // Load exercises
347 exercises, err := downloadExercises()
348 if err != nil {
349 log.Fatalf("Failed to load exercises: %v", err)
350 }
351
352 log.Printf("Successfully loaded %d exercises from JSON", len(exercises))
353
354 // Create/update lookup entities and get maps
355 lookupMaps, err := db.upsertLookupEntities(exercises)
356 if err != nil {
357 return errors.New("Failed to upsert lookup entities: " + err.Error())
358 }
359
360 var successCount, createCount, updateCount int
361
362 // Import/update exercises
363 for i, exercise := range exercises {
364 didSave, isUpdate, err := db.upsertExercise(exercise, lookupMaps)
365 if err != nil {
366 log.Printf("Failed to upsert exercise %d (%s): %v", i+1, exercise.Name, err)
367 continue
368 }
369
370 successCount++
371 if didSave {
372 if isUpdate {
373 updateCount++
374 } else {
375 createCount++
376 }
377 }
378 }
379
380 lastUpdate = time.Now()
381
382 log.Printf("Update completed successfully! Processed %d out of %d exercises (%d created, %d updated)", successCount, len(exercises), createCount, updateCount)
383 return
384}
385
386func (db *Database) GetLastUpdate() time.Time {
387 return lastUpdate
388}