photoplaces/backend/internal/repository/services.go

// Package repository
package repository

import (
	"context"
	"encoding/json"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"gogs.fxtmmsk.ru/foxtime/photoplaces/backend/internal/models"
)

type ServiceRepo struct {
	pool *pgxpool.Pool
}

func NewServiceRepo(pool *pgxpool.Pool) *ServiceRepo {
	return &ServiceRepo{pool: pool}
}

func (r *ServiceRepo) Create(ctx context.Context, s *models.Service) error {
	err := r.pool.QueryRow(ctx,
		`INSERT INTO services (executor_id, title, description, price, currency, duration_minutes, status)
		 VALUES ($1, $2, $3, $4, $5, $6, $7)
		 RETURNING id, created_at, updated_at`,
		s.ExecutorID, s.Title, s.Description, s.Price, s.Currency, s.DurationMinutes, s.Status,
	).Scan(&s.ID, &s.CreatedAt, &s.UpdatedAt)
	if err != nil {
		return fmt.Errorf("create service: %w", err)
	}

	if len(s.Tags) > 0 {
		if err := r.SetTags(ctx, s.ID, s.Tags); err != nil {
			return err
		}
	}

	return nil
}

func (r *ServiceRepo) GetByID(ctx context.Context, id string) (*models.Service, error) {
	row := r.pool.QueryRow(ctx,
		`SELECT id, executor_id, title, description, price, currency, duration_minutes,
		        status, rating, reviews_count, created_at, updated_at, deleted_at
		 FROM services WHERE id = $1 AND deleted_at IS NULL`, id)
	return scanService(row)
}

func (r *ServiceRepo) GetByIDWithTags(ctx context.Context, id string) (*models.Service, error) {
	row := r.pool.QueryRow(ctx,
		`SELECT s.id, s.executor_id, s.title, s.description, s.price, s.currency,
		        s.duration_minutes, s.status, s.rating, s.reviews_count,
		        s.created_at, s.updated_at, s.deleted_at,
		        COALESCE(json_agg(json_build_object('id', t.id, 'name', t.name, 'category', t.category))
		                 FILTER (WHERE t.id IS NOT NULL), '[]'::json) AS tags
		 FROM services s
		 LEFT JOIN service_tags st ON st.service_id = s.id
		 LEFT JOIN tags t ON t.id = st.tag_id
		 WHERE s.id = $1 AND s.deleted_at IS NULL
		 GROUP BY s.id`, id)

	var s models.Service
	var tagsJSON []byte
	err := row.Scan(
		&s.ID, &s.ExecutorID, &s.Title, &s.Description, &s.Price, &s.Currency,
		&s.DurationMinutes, &s.Status, &s.Rating, &s.ReviewsCount,
		&s.CreatedAt, &s.UpdatedAt, &s.DeletedAt, &tagsJSON,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get service with tags: %w", err)
	}

	if err := json.Unmarshal(tagsJSON, &s.Tags); err != nil {
		return nil, fmt.Errorf("unmarshal service tags: %w", err)
	}

	return &s, nil
}

func (r *ServiceRepo) List(ctx context.Context, filter models.ServiceFilter) ([]*models.Service, error) {
	q := `SELECT s.id, s.executor_id, s.title, s.description, s.price, s.currency,
	             s.duration_minutes, s.status, s.rating, s.reviews_count,
	             s.created_at, s.updated_at, s.deleted_at
	      FROM services s`
	args := pgx.NamedArgs{}
	joins := ""

	if len(filter.Tags) > 0 {
		joins += ` JOIN service_tags st ON st.service_id = s.id`
	}

	// Если есть JOIN-ы, добавляем DISTINCT, чтобы избежать дубликатов услуг при нескольких совпадениях
	if joins != "" {
		q = "SELECT DISTINCT" + q[6:] // заменяем "SELECT" на "SELECT DISTINCT"
	}
	q += joins + ` WHERE s.deleted_at IS NULL`

	if filter.Status != "" { q += ` AND s.status = @status`; args["status"] = filter.Status }
	if filter.MinRating > 0 { q += ` AND s.rating >= @min_rating`; args["min_rating"] = filter.MinRating }
	if filter.PriceMin != nil { q += ` AND s.price >= @price_min`; args["price_min"] = *filter.PriceMin }
	if filter.PriceMax != nil { q += ` AND s.price <= @price_max`; args["price_max"] = *filter.PriceMax }
	if len(filter.Tags) > 0 { q += ` AND st.tag_id = ANY(@tag_ids)`; args["tag_ids"] = filter.Tags }

	q += ` ORDER BY s.created_at DESC LIMIT @lim`
	args["lim"] = filter.Limit()

	rows, err := r.pool.Query(ctx, q, args)
	if err != nil {
		return nil, fmt.Errorf("list services: %w", err)
	}
	defer rows.Close()

	var services []*models.Service
	for rows.Next() {
		s, err := scanService(rows)
		if err != nil {
			return nil, err
		}
		services = append(services, s)
	}
	return services, nil
}

func (r *ServiceRepo) Update(ctx context.Context, s *models.Service) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE services SET title=$1, description=$2, price=$3, currency=$4,
		 duration_minutes=$5, status=$6, updated_at=now()
		 WHERE id=$7 AND deleted_at IS NULL`,
		s.Title, s.Description, s.Price, s.Currency, s.DurationMinutes, s.Status, s.ID)
	return err
}

func (r *ServiceRepo) SoftDelete(ctx context.Context, id string) error {
	_, err := r.pool.Exec(ctx, `UPDATE services SET deleted_at=now() WHERE id=$1`, id)
	return err
}

func (r *ServiceRepo) SetTags(ctx context.Context, serviceID string, tags []models.Tag) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	_, err = tx.Exec(ctx, `DELETE FROM service_tags WHERE service_id = $1`, serviceID)
	if err != nil {
		return err
	}

	for _, tag := range tags {
		_, err = tx.Exec(ctx,
			`INSERT INTO service_tags (service_id, tag_id) VALUES ($1, $2)`,
			serviceID, tag.ID)
		if err != nil {
			return err
		}
	}

	return tx.Commit(ctx)
}

func (r *ServiceRepo) GetTags(ctx context.Context, serviceID string) ([]models.Tag, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT t.id, t.name, t.category FROM tags t
		 JOIN service_tags st ON st.tag_id = t.id
		 WHERE st.service_id = $1`, serviceID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []models.Tag
	for rows.Next() {
		var t models.Tag
		if err := rows.Scan(&t.ID, &t.Name, &t.Category); err != nil {
			return nil, err
		}
		tags = append(tags, t)
	}
	return tags, nil
}

func scanService(row interface{ Scan(dest ...any) error }) (*models.Service, error) {
	var s models.Service
	err := row.Scan(
		&s.ID, &s.ExecutorID, &s.Title, &s.Description, &s.Price, &s.Currency,
		&s.DurationMinutes, &s.Status, &s.Rating, &s.ReviewsCount,
		&s.CreatedAt, &s.UpdatedAt, &s.DeletedAt,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &s, nil
}