photoplaces/backend/internal/repository/places.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 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,
		        version, 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) GetByIDWithDetails(ctx context.Context, id string) (*models.Place, error) {
	query := `
		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.version, p.created_at, p.updated_at, p.published_at, p.deleted_at,
	       COALESCE(json_agg(DISTINCT jsonb_build_object('id', t.id, 'name', t.name, 'category', t.category))
	                FILTER (WHERE t.id IS NOT NULL), '[]'::json) as tags,
	       COALESCE(json_agg(DISTINCT jsonb_build_object('id', f.id, 'name', f.name, 'category', f.category, 'icon', f.icon))
	                FILTER (WHERE f.id IS NOT NULL), '[]'::json) as features
		FROM places p
		LEFT JOIN place_tags pt ON pt.place_id = p.id
		LEFT JOIN tags t ON t.id = pt.tag_id
		LEFT JOIN place_features pf ON pf.place_id = p.id
		LEFT JOIN features f ON f.id = pf.feature_id
		WHERE p.id = $1 AND p.deleted_at IS NULL
		GROUP BY p.id`

	row := r.pool.QueryRow(ctx, query, id)

	var p models.Place
	var tagsJSON, featuresJSON []byte
	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.Version,
		&p.CreatedAt, &p.UpdatedAt, &p.PublishedAt, &p.DeletedAt,
		&tagsJSON, &featuresJSON,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get place with details: %w", err)
	}

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

	return &p, nil
}

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.version, 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`
	}

	// Если есть JOIN-ы, добавляем DISTINCT, чтобы избежать дубликатов мест при нескольких совпадениях
	if joins != "" {
		q = "SELECT DISTINCT" + q[6:] // заменяем "SELECT" на "SELECT DISTINCT"
	}
	if filter.Status != "deleted" {
		q += ` WHERE p.deleted_at IS NULL`
	} else {
		q += ` WHERE p.deleted_at IS NOT 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.OwnerID != "" { q += ` AND p.owner_id = @owner_id`; args["owner_id"] = filter.OwnerID }
	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 }

	// Cursor-based pagination
	if filter.Cursor != "" && filter.CursorCreatedAt != nil {
		if filter.Sort == "rating" && filter.CursorRating != nil {
			q += ` AND (p.rating, p.created_at, p.id) < (@cursor_rating, @cursor_created_at, @cursor_id)`
			args["cursor_rating"] = *filter.CursorRating
			args["cursor_created_at"] = *filter.CursorCreatedAt
			args["cursor_id"] = filter.Cursor
		} else {
			q += ` AND (p.created_at, p.id) < (@cursor_created_at, @cursor_id)`
			args["cursor_created_at"] = *filter.CursorCreatedAt
			args["cursor_id"] = filter.Cursor
		}
	}

	if filter.Sort == "rating" {
		q += ` ORDER BY p.rating DESC, p.created_at DESC, p.id`
	} 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), p.created_at DESC, p.id`
		args["user_lng"] = *filter.UserLng; args["user_lat"] = *filter.UserLat
	} else {
		q += ` ORDER BY p.created_at DESC, p.id`
	}
	q += ` LIMIT @lim`
	args["lim"] = filter.Limit() + 1 // fetch one extra to detect hasMore

	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(), version=version+1
		 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 $3 THEN now() ELSE published_at END
		 WHERE id=$4 AND deleted_at IS NULL`,
		status, comment, status == "published", id)
	return err
}

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

func (r *PlaceRepo) HardDelete(ctx context.Context, id string) error {
	_, err := r.pool.Exec(ctx, `DELETE FROM places WHERE id=$1`, id)
	return err
}

func (r *PlaceRepo) Restore(ctx context.Context, id string) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE places SET status='published', deleted_at=NULL, published_at=now(), updated_at=now()
		 WHERE id=$1 AND deleted_at IS NOT NULL`, id)
	return err
}

func (r *PlaceRepo) GetByIDRaw(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,
		        version, created_at, updated_at, published_at, deleted_at
		 FROM places WHERE id = $1`, id)
	return scanPlace(row)
}

func (r *PlaceRepo) GetPlaceImages(ctx context.Context, placeID string) ([]models.PlaceImage, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, place_id, url, alt, sort_order, is_cover FROM place_images WHERE place_id = $1`,
		placeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var images []models.PlaceImage
	for rows.Next() {
		var img models.PlaceImage
		if err := rows.Scan(&img.ID, &img.PlaceID, &img.URL, &img.Alt, &img.SortOrder, &img.IsCover); err != nil {
			return nil, err
		}
		images = append(images, img)
	}
	return images, nil
}

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 (r *PlaceRepo) GetTagsBatch(ctx context.Context, placeIDs []string) (map[string][]models.Tag, error) {
	if len(placeIDs) == 0 {
		return map[string][]models.Tag{}, nil
	}
	rows, err := r.pool.Query(ctx,
		`SELECT pt.place_id, t.id, t.name, t.category FROM tags t
		 JOIN place_tags pt ON pt.tag_id = t.id
		 WHERE pt.place_id = ANY($1)`, placeIDs)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

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

func (r *PlaceRepo) GetFeaturesBatch(ctx context.Context, placeIDs []string) (map[string][]models.Feature, error) {
	if len(placeIDs) == 0 {
		return map[string][]models.Feature{}, nil
	}
	rows, err := r.pool.Query(ctx,
		`SELECT pf.place_id, 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 = ANY($1)`, placeIDs)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := make(map[string][]models.Feature)
	for rows.Next() {
		var placeID string
		var f models.Feature
		if err := rows.Scan(&placeID, &f.ID, &f.Name, &f.Category, &f.Icon); err != nil {
			return nil, err
		}
		result[placeID] = append(result[placeID], f)
	}
	return result, 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.Version,
		&p.CreatedAt, &p.UpdatedAt, &p.PublishedAt, &p.DeletedAt,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &p, nil
}