Add GetChoreDetail endpoint to retrieve detailed chore information

1 files changed, 36 insertions, 0 deletions

diff --git a/internal/chore/repo/repository.go b/internal/chore/repo/repository.go
index 1ab0f0b..52fa4ab 100644
--- a/internal/chore/repo/repository.go
+++ b/internal/chore/repo/repository.go
@@ -214,3 +214,39 @@ func (r *ChoreRepository) SetDueDate(c context.Context, choreID int, dueDate tim
 func (r *ChoreRepository) SetDueDateIfNotExisted(c context.Context, choreID int, dueDate time.Time) error {
 	return r.db.WithContext(c).Model(&chModel.Chore{}).Where("id = ? and next_due_date is null", choreID).Update("next_due_date", dueDate).Error
 }
+
+func (r *ChoreRepository) GetChoreDetailByID(c context.Context, choreID int, circleID int) (*chModel.ChoreDetail, error) {
+	var choreDetail chModel.ChoreDetail
+	if err := r.db.WithContext(c).
+		Table("chores").
+		Select(`
+        chores.id, 
+        chores.name, 
+        chores.frequency_type, 
+        chores.next_due_date, 
+        chores.assigned_to,
+        chores.created_by,
+        recent_history.last_completed_date,
+        recent_history.last_assigned_to as last_completed_by,
+        COUNT(chore_histories.id) as total_completed`).
+		Joins("LEFT JOIN chore_histories ON chores.id = chore_histories.chore_id").
+		Joins(`LEFT JOIN (
+        SELECT 
+            chore_id, 
+            assigned_to AS last_assigned_to, 
+            completed_at AS last_completed_date
+        FROM chore_histories
+        WHERE (chore_id, completed_at) IN (
+            SELECT chore_id, MAX(completed_at)
+            FROM chore_histories
+            GROUP BY chore_id
+        )
+    ) AS recent_history ON chores.id = recent_history.chore_id`).
+		Where("chores.id = ? and chores.circle_id = ?", choreID, circleID).
+		Group("chores.id").
+		First(&choreDetail).Error; err != nil {
+		return nil, err
+
+	}
+	return &choreDetail, nil
+}