photoplaces/backend/internal/repository/places.go

// Package repository
package repository

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/photoplaces/backend/internal/models"
)

type PlaceRepo struct {
	pool *pgxpool.Pool
}

func NewPlaceRepo(pool *pgxpool.Pool) *PlaceRepo {
	return &PlaceRepo{pool: pool}
}

func (r *PlaceRepo) Create(ctx context.Context, p *models.Place) error {
	err := r.pool.QueryRow(ctx,
		`INSERT INTO places (type, owner_id, title, description, address, coordinates,
		                     cover_image, access_info, status, hourly_rate, currency, min_hours, booking_url)
		 VALUES ($1, $2, $3, $4, $5, ST_SetSRID(ST_MakePoint($6, $7), 4326),
		         $8, $9, $10, $11, $12, $13, $14)
		 RETURNING id, created_at, updated_at`,
		p.Type, p.OwnerID, p.Title, p.Description, p.Address,
		p.Lng, p.Lat,
		p.CoverImage, p.AccessInfo, p.Status,
		p.HourlyRate, p.Currency, p.MinHours, p.BookingURL,
	).Scan(&p.ID, &p.CreatedAt, &p.UpdatedAt)
	if err != nil {
		return fmt.Errorf("create place: %w", err)
	}

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

	return nil
}

func (r *PlaceRepo) GetByID(ctx context.Context, id string) (*models.Place, error) {
	row := r.pool.QueryRow(ctx,
		`SELECT id, type, owner_id, title, description, address,
		        ST_Y(coordinates::geometry), ST_X(coordinates::geometry),
		        cover_image, access_info, status, moderation_comment,
		        rating, reviews_count, hourly_rate, currency, min_hours, booking_url,
		        created_at, updated_at, published_at, deleted_at
		 FROM places WHERE id = $1 AND deleted_at IS NULL`, id)
	return scanPlace(row)
}

func (r *PlaceRepo) List(ctx context.Context, filter models.PlaceFilter) ([]*models.Place, error) {
	q := `SELECT p.id, p.type, p.owner_id, p.title, p.description, p.address,
	             ST_Y(p.coordinates::geometry), ST_X(p.coordinates::geometry),
	             p.cover_image, p.access_info, p.status, p.moderation_comment,
	             p.rating, p.reviews_count, p.hourly_rate, p.currency, p.min_hours, p.booking_url,
	             p.created_at, p.updated_at, p.published_at, p.deleted_at
	      FROM places p`
	args := pgx.NamedArgs{}
	joins := ""

	if len(filter.TagIDs) > 0 {
		joins += ` JOIN place_tags pt ON pt.place_id = p.id`
	}

	if len(filter.FeatureIDs) > 0 {
		joins += ` JOIN place_features pf ON pf.place_id = p.id`
	}

	q += joins + ` WHERE p.deleted_at IS NULL`

	if filter.Status != "" { q += ` AND p.status = @status`; args["status"] = filter.Status }
	if filter.Type != "" { q += ` AND p.type = @type`; args["type"] = filter.Type }
	if filter.MinRating > 0 { q += ` AND p.rating >= @min_rating`; args["min_rating"] = filter.MinRating }
	if filter.PriceMin != nil { q += ` AND p.hourly_rate >= @price_min`; args["price_min"] = *filter.PriceMin }
	if filter.PriceMax != nil { q += ` AND p.hourly_rate <= @price_max`; args["price_max"] = *filter.PriceMax }
	if filter.Bounds != nil {
		q += ` AND ST_Intersects(p.coordinates, ST_MakeEnvelope(@sw_lng, @sw_lat, @ne_lng, @ne_lat, 4326))`
		args["sw_lng"] = filter.Bounds.SWLng; args["sw_lat"] = filter.Bounds.SWLat
		args["ne_lng"] = filter.Bounds.NELng; args["ne_lat"] = filter.Bounds.NELat
	}
	if len(filter.TagIDs) > 0 { q += ` AND pt.tag_id = ANY(@tag_ids)`; args["tag_ids"] = filter.TagIDs }
	if len(filter.FeatureIDs) > 0 { q += ` AND pf.feature_id = ANY(@feature_ids)`; args["feature_ids"] = filter.FeatureIDs }

	if filter.Sort == "rating" {
		q += ` ORDER BY p.rating DESC`
	} else if filter.Sort == "distance" && filter.UserLat != nil && filter.UserLng != nil {
		q += ` ORDER BY p.coordinates <-> ST_SetSRID(ST_MakePoint(@user_lng, @user_lat), 4326)`
		args["user_lng"] = *filter.UserLng; args["user_lat"] = *filter.UserLat
	} else {
		q += ` ORDER BY p.created_at DESC`
	}
	q += ` LIMIT @lim`
	args["lim"] = filter.Limit()

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

	var places []*models.Place
	for rows.Next() {
		p, err := scanPlace(rows)
		if err != nil {
			return nil, err
		}
		places = append(places, p)
	}
	return places, nil
}

func (r *PlaceRepo) Update(ctx context.Context, p *models.Place) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE places SET title=$1, description=$2, address=$3,
		 coordinates=ST_SetSRID(ST_MakePoint($4, $5), 4326),
		 cover_image=$6, access_info=$7, status=$8, hourly_rate=$9, currency=$10, min_hours=$11,
		 booking_url=$12, updated_at=now()
		 WHERE id=$13 AND deleted_at IS NULL`,
		p.Title, p.Description, p.Address,
		p.Lng, p.Lat,
		p.CoverImage, p.AccessInfo, p.Status,
		p.HourlyRate, p.Currency, p.MinHours, p.BookingURL,
		p.ID)
	return err
}

func (r *PlaceRepo) UpdateStatus(ctx context.Context, id, status, comment string) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE places SET status=$1, moderation_comment=$2, updated_at=now(),
		 published_at = CASE WHEN $1 = 'published' THEN now() ELSE published_at END
		 WHERE id=$3 AND deleted_at IS NULL`,
		status, comment, id)
	return err
}

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

func (r *PlaceRepo) SetTags(ctx context.Context, placeID 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 place_tags WHERE place_id = $1`, placeID)
	if err != nil {
		return err
	}

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

	return tx.Commit(ctx)
}

func (r *PlaceRepo) SetFeatures(ctx context.Context, placeID string, features []models.Feature) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	_, err = tx.Exec(ctx, `DELETE FROM place_features WHERE place_id = $1`, placeID)
	if err != nil {
		return err
	}

	for _, f := range features {
		_, err = tx.Exec(ctx,
			`INSERT INTO place_features (place_id, feature_id) VALUES ($1, $2)`,
			placeID, f.ID)
		if err != nil {
			return err
		}
	}

	return tx.Commit(ctx)
}

func (r *PlaceRepo) GetTags(ctx context.Context, placeID string) ([]models.Tag, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT t.id, t.name, t.category FROM tags t
		 JOIN place_tags pt ON pt.tag_id = t.id
		 WHERE pt.place_id = $1`, placeID)
	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 (r *PlaceRepo) GetFeatures(ctx context.Context, placeID string) ([]models.Feature, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT f.id, f.name, f.category, f.icon FROM features f
		 JOIN place_features pf ON pf.feature_id = f.id
		 WHERE pf.place_id = $1`, placeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var features []models.Feature
	for rows.Next() {
		var f models.Feature
		if err := rows.Scan(&f.ID, &f.Name, &f.Category, &f.Icon); err != nil {
			return nil, err
		}
		features = append(features, f)
	}
	return features, nil
}

func scanPlace(row interface{ Scan(dest ...any) error }) (*models.Place, error) {
	var p models.Place
	err := row.Scan(
		&p.ID, &p.Type, &p.OwnerID, &p.Title, &p.Description, &p.Address,
		&p.Lat, &p.Lng,
		&p.CoverImage, &p.AccessInfo, &p.Status, &p.ModerationComment,
		&p.Rating, &p.ReviewsCount, &p.HourlyRate, &p.Currency, &p.MinHours, &p.BookingURL,
		&p.CreatedAt, &p.UpdatedAt, &p.PublishedAt, &p.DeletedAt,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &p, nil
}