secunda_test/internal/repository/mysql/tasks.go

package mysql

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"secunda-test/internal/domain"
	"secunda-test/internal/service"
)

type TaskRepository struct{ db *sql.DB }

func NewTaskRepository(db *sql.DB) *TaskRepository { return &TaskRepository{db: db} }

func (r *TaskRepository) Create(ctx context.Context, task domain.Task) (domain.Task, error) {
	res, err := r.db.ExecContext(ctx, `
		INSERT INTO tasks(team_id,title,description,status,assignee_id,created_by)
		VALUES(?,?,?,?,?,?)`,
		task.TeamID, task.Title, task.Description, valueOrDefault(string(task.Status), string(domain.StatusTodo)), task.AssigneeID, task.CreatedBy)
	if err != nil {
		return domain.Task{}, err
	}
	id, _ := res.LastInsertId()
	return r.Get(ctx, id)
}

func (r *TaskRepository) Get(ctx context.Context, id int64) (domain.Task, error) {
	var t domain.Task
	err := r.db.QueryRowContext(ctx, `
		SELECT id,team_id,title,description,status,assignee_id,created_by,created_at,updated_at
		FROM tasks WHERE id=?`, id).
		Scan(&t.ID, &t.TeamID, &t.Title, &t.Description, &t.Status, &t.AssigneeID, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt)
	if err == sql.ErrNoRows {
		return domain.Task{}, service.ErrNotFound
	}
	return t, err
}

func (r *TaskRepository) Update(ctx context.Context, task domain.Task, changedBy int64) (domain.Task, error) {
	old, err := r.Get(ctx, task.ID)
	if err != nil {
		return domain.Task{}, err
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return domain.Task{}, err
	}
	defer tx.Rollback()
	_, err = tx.ExecContext(ctx, `
		UPDATE tasks SET title=?,description=?,status=?,assignee_id=? WHERE id=?`,
		task.Title, task.Description, task.Status, task.AssigneeID, task.ID)
	if err != nil {
		return domain.Task{}, err
	}
	addHistory := func(field, oldValue, newValue string) error {
		if oldValue == newValue {
			return nil
		}
		_, err := tx.ExecContext(ctx, `INSERT INTO task_history(task_id,changed_by,field_name,old_value,new_value) VALUES(?,?,?,?,?)`,
			task.ID, changedBy, field, oldValue, newValue)
		return err
	}
	if err := addHistory("title", old.Title, task.Title); err != nil {
		return domain.Task{}, err
	}
	if err := addHistory("description", old.Description, task.Description); err != nil {
		return domain.Task{}, err
	}
	if err := addHistory("status", string(old.Status), string(task.Status)); err != nil {
		return domain.Task{}, err
	}
	if err := addHistory("assignee_id", ptrString(old.AssigneeID), ptrString(task.AssigneeID)); err != nil {
		return domain.Task{}, err
	}
	if err := tx.Commit(); err != nil {
		return domain.Task{}, err
	}
	return r.Get(ctx, task.ID)
}

func (r *TaskRepository) List(ctx context.Context, filter domain.TaskFilter) ([]domain.Task, error) {
	args := []any{filter.TeamID}
	where := []string{"team_id=?"}
	if filter.Status != "" {
		where = append(where, "status=?")
		args = append(args, filter.Status)
	}
	if filter.AssigneeID != nil {
		where = append(where, "assignee_id=?")
		args = append(args, *filter.AssigneeID)
	}
	limit, offset := filter.PageSize, (filter.Page-1)*filter.PageSize
	args = append(args, limit, offset)
	query := fmt.Sprintf(`
		SELECT id,team_id,title,description,status,assignee_id,created_by,created_at,updated_at
		FROM tasks WHERE %s ORDER BY created_at DESC LIMIT ? OFFSET ?`, strings.Join(where, " AND "))
	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []domain.Task
	for rows.Next() {
		var t domain.Task
		if err := rows.Scan(&t.ID, &t.TeamID, &t.Title, &t.Description, &t.Status, &t.AssigneeID, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

func (r *TaskRepository) History(ctx context.Context, taskID int64) ([]domain.TaskHistory, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id,task_id,changed_by,field_name,old_value,new_value,created_at
		FROM task_history WHERE task_id=? ORDER BY created_at DESC`, taskID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []domain.TaskHistory
	for rows.Next() {
		var h domain.TaskHistory
		if err := rows.Scan(&h.ID, &h.TaskID, &h.ChangedBy, &h.FieldName, &h.OldValue, &h.NewValue, &h.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, h)
	}
	return out, rows.Err()
}

func (r *TaskRepository) TeamSummary(ctx context.Context) ([]domain.TeamSummary, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT t.id, t.name, COUNT(DISTINCT tm.user_id) AS members_count,
		       COUNT(DISTINCT CASE WHEN ta.status='done' AND ta.updated_at >= NOW() - INTERVAL 7 DAY THEN ta.id END) AS done_last_7_days
		FROM teams t
		LEFT JOIN team_members tm ON tm.team_id=t.id
		LEFT JOIN tasks ta ON ta.team_id=t.id
		GROUP BY t.id, t.name
		ORDER BY t.name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []domain.TeamSummary
	for rows.Next() {
		var s domain.TeamSummary
		if err := rows.Scan(&s.TeamID, &s.TeamName, &s.MembersCount, &s.DoneLast7Days); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

func (r *TaskRepository) TopCreators(ctx context.Context) ([]domain.TopCreator, error) {
	rows, err := r.db.QueryContext(ctx, `
		WITH ranked AS (
		  SELECT t.team_id, te.name AS team_name, t.created_by AS user_id, u.name AS user_name,
		         COUNT(*) AS tasks_created,
		         DENSE_RANK() OVER (PARTITION BY t.team_id ORDER BY COUNT(*) DESC) AS user_rank
		  FROM tasks t
		  JOIN teams te ON te.id=t.team_id
		  JOIN users u ON u.id=t.created_by
		  WHERE t.created_at >= NOW() - INTERVAL 1 MONTH
		  GROUP BY t.team_id, te.name, t.created_by, u.name
		)
		SELECT team_id,team_name,user_id,user_name,tasks_created,user_rank
		FROM ranked WHERE user_rank <= 3 ORDER BY team_id,user_rank`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []domain.TopCreator
	for rows.Next() {
		var c domain.TopCreator
		if err := rows.Scan(&c.TeamID, &c.TeamName, &c.UserID, &c.UserName, &c.TasksCreated, &c.Rank); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

func (r *TaskRepository) InvalidAssignees(ctx context.Context) ([]domain.Task, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT ta.id,ta.team_id,ta.title,ta.description,ta.status,ta.assignee_id,ta.created_by,ta.created_at,ta.updated_at
		FROM tasks ta
		LEFT JOIN team_members tm ON tm.team_id=ta.team_id AND tm.user_id=ta.assignee_id
		WHERE ta.assignee_id IS NOT NULL AND tm.user_id IS NULL`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []domain.Task
	for rows.Next() {
		var t domain.Task
		if err := rows.Scan(&t.ID, &t.TeamID, &t.Title, &t.Description, &t.Status, &t.AssigneeID, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

func valueOrDefault(v, fallback string) string {
	if v == "" {
		return fallback
	}
	return v
}

func ptrString(v *int64) string {
	if v == nil {
		return ""
	}
	return fmt.Sprint(*v)
}