sno-quiz/backend/internal/repository/quiz_repository.go

package repository

import (
	"context"
	"sno/internal/models"

	"github.com/jackc/pgx/v5/pgxpool"
)

// QuizRepository defines the interface for quiz data operations.
type QuizRepository interface {
	GetAllActive(ctx context.Context) ([]models.Quiz, error)
	Create(ctx context.Context, quiz *models.Quiz) (*models.Quiz, error)
	GetByID(ctx context.Context, id int) (*models.Quiz, error)
	Update(ctx context.Context, id int, quiz *models.Quiz) (*models.Quiz, error)
	Delete(ctx context.Context, id int) error
}

// postgresQuizRepository implements the QuizRepository interface for PostgreSQL.
type postgresQuizRepository struct {
	db *pgxpool.Pool
}

// NewQuizRepository creates a new instance of a quiz repository.
func NewQuizRepository(db *pgxpool.Pool) QuizRepository {
	return &postgresQuizRepository{db: db}
}

// GetAllActive retrieves all quizzes where is_active is true.
func (r *postgresQuizRepository) GetAllActive(ctx context.Context) ([]models.Quiz, error) {
	query := `
		SELECT 
			id, title, description, image_url, reward_stars, 
			has_timer, timer_per_question, can_repeat, repeat_cooldown_hours, 
			is_active, created_by, created_at
		FROM quizzes 
		WHERE is_active = true 
		ORDER BY created_at DESC
	`

	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var quizzes []models.Quiz
	for rows.Next() {
		var q models.Quiz
		if err := rows.Scan(
			&q.ID, &q.Title, &q.Description, &q.ImageURL, &q.RewardStars,
			&q.HasTimer, &q.TimerPerQuestion, &q.CanRepeat, &q.RepeatCooldownHours,
			&q.IsActive, &q.CreatedBy, &q.CreatedAt,
		); err != nil {
			return nil, err
		}
		quizzes = append(quizzes, q)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	if quizzes == nil {
		return []models.Quiz{}, nil
	}

	return quizzes, nil
}

// Create inserts a new quiz into the database.
func (r *postgresQuizRepository) Create(ctx context.Context, quiz *models.Quiz) (*models.Quiz, error) {
	query := `
		INSERT INTO quizzes (title, description, image_url, reward_stars, has_timer, timer_per_question, can_repeat, repeat_cooldown_hours, created_by)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id, is_active, created_at
	`
	err := r.db.QueryRow(ctx, query,
		quiz.Title, quiz.Description, quiz.ImageURL, quiz.RewardStars, quiz.HasTimer, quiz.TimerPerQuestion, quiz.CanRepeat, quiz.RepeatCooldownHours, quiz.CreatedBy,
	).Scan(&quiz.ID, &quiz.IsActive, &quiz.CreatedAt)

	if err != nil {
		return nil, err
	}

	return quiz, nil
}

// GetByID retrieves a single quiz by its ID.
func (r *postgresQuizRepository) GetByID(ctx context.Context, id int) (*models.Quiz, error) {
	query := `
		SELECT 
			id, title, description, image_url, reward_stars, 
			has_timer, timer_per_question, can_repeat, repeat_cooldown_hours, 
			is_active, created_by, created_at
		FROM quizzes 
		WHERE id = $1
	`

	var q models.Quiz
	err := r.db.QueryRow(ctx, query, id).Scan(
		&q.ID, &q.Title, &q.Description, &q.ImageURL, &q.RewardStars,
		&q.HasTimer, &q.TimerPerQuestion, &q.CanRepeat, &q.RepeatCooldownHours,
		&q.IsActive, &q.CreatedBy, &q.CreatedAt,
	)

	if err != nil {
		return nil, err
	}

	return &q, nil
}

// Update updates an existing quiz
func (r *postgresQuizRepository) Update(ctx context.Context, id int, quiz *models.Quiz) (*models.Quiz, error) {
	query := `
		UPDATE quizzes
		SET title = $2, description = $3, image_url = $4, reward_stars = $5,
			has_timer = $6, timer_per_question = $7, can_repeat = $8, repeat_cooldown_hours = $9,
			is_active = $10
		WHERE id = $1
		RETURNING id, title, description, image_url, reward_stars,
				 has_timer, timer_per_question, can_repeat, repeat_cooldown_hours,
				 is_active, created_by, created_at
	`

	var q models.Quiz
	err := r.db.QueryRow(ctx, query,
		id, quiz.Title, quiz.Description, quiz.ImageURL, quiz.RewardStars,
		quiz.HasTimer, quiz.TimerPerQuestion, quiz.CanRepeat, quiz.RepeatCooldownHours,
		quiz.IsActive,
	).Scan(
		&q.ID, &q.Title, &q.Description, &q.ImageURL, &q.RewardStars,
		&q.HasTimer, &q.TimerPerQuestion, &q.CanRepeat, &q.RepeatCooldownHours,
		&q.IsActive, &q.CreatedBy, &q.CreatedAt,
	)

	if err != nil {
		return nil, err
	}

	return &q, nil
}

// Delete deletes a quiz and its associated questions
func (r *postgresQuizRepository) Delete(ctx context.Context, id int) error {
	// Start transaction
	tx, err := r.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// Delete associated questions first
	_, err = tx.Exec(ctx, "DELETE FROM questions WHERE quiz_id = $1", id)
	if err != nil {
		return err
	}

	// Delete the quiz
	_, err = tx.Exec(ctx, "DELETE FROM quizzes WHERE id = $1", id)
	if err != nil {
		return err
	}

	// Commit transaction
	return tx.Commit(ctx)
}