photoplaces/backend/internal/repository/bookings.go

package repository

import (
	"context"
	"fmt"
	"time"

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

type BookingRepo struct {
	pool *pgxpool.Pool
}

func NewBookingRepo(pool *pgxpool.Pool) *BookingRepo {
	return &BookingRepo{pool: pool}
}

func (r *BookingRepo) Create(ctx context.Context, b *models.Booking) error {
	return r.pool.QueryRow(ctx,
		`INSERT INTO bookings (place_id, user_id, start_time, end_time, status, total_price, currency, comment)
		 VALUES ($1, $2, $3, $4, 'pending', $5, $6, $7)
		 RETURNING id, created_at, updated_at`,
		b.PlaceID, b.UserID, b.StartTime, b.EndTime, b.TotalPrice, b.Currency, b.Comment,
	).Scan(&b.ID, &b.CreatedAt, &b.UpdatedAt)
}

func (r *BookingRepo) IsTimeSlotAvailable(ctx context.Context, placeID string, start, end time.Time) (bool, error) {
	var count int
	err := r.pool.QueryRow(ctx,
		`SELECT COUNT(*) FROM bookings
		 WHERE place_id = $1 AND status != 'cancelled'
		 AND tsrange(start_time, end_time) && tsrange($2, $3)`,
		placeID, start, end).Scan(&count)
	if err != nil {
		return false, err
	}
	return count == 0, nil
}

func (r *BookingRepo) GetByID(ctx context.Context, id string) (*models.Booking, error) {
	row := r.pool.QueryRow(ctx,
		`SELECT id, place_id, user_id, start_time, end_time, status, total_price, currency, comment,
		        created_at, updated_at
		 FROM bookings WHERE id = $1`, id)

	var b models.Booking
	err := row.Scan(&b.ID, &b.PlaceID, &b.UserID, &b.StartTime, &b.EndTime,
		&b.Status, &b.TotalPrice, &b.Currency, &b.Comment, &b.CreatedAt, &b.UpdatedAt)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &b, nil
}

func (r *BookingRepo) ListByUser(ctx context.Context, userID string) ([]*models.Booking, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, place_id, user_id, start_time, end_time, status, total_price, currency, comment,
		        created_at, updated_at
		 FROM bookings WHERE user_id = $1 ORDER BY start_time DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	return scanBookings(rows)
}

func (r *BookingRepo) ListByPlace(ctx context.Context, placeID string) ([]*models.Booking, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, place_id, user_id, start_time, end_time, status, total_price, currency, comment,
		        created_at, updated_at
		 FROM bookings WHERE place_id = $1 ORDER BY start_time DESC`, placeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	return scanBookings(rows)
}

func (r *BookingRepo) UpdateStatus(ctx context.Context, id, status string) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE bookings SET status=$1, updated_at=now() WHERE id=$2`, status, id)
	return err
}

func scanBookings(rows pgx.Rows) ([]*models.Booking, error) {
	var bookings []*models.Booking
	for rows.Next() {
		var b models.Booking
		if err := rows.Scan(&b.ID, &b.PlaceID, &b.UserID, &b.StartTime, &b.EndTime,
			&b.Status, &b.TotalPrice, &b.Currency, &b.Comment, &b.CreatedAt, &b.UpdatedAt); err != nil {
			return nil, err
		}
		bookings = append(bookings, &b)
	}
	return bookings, nil
}

func (r *BookingRepo) CancelOverlapping(ctx context.Context, placeID string, start, end time.Time) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE bookings SET status='cancelled', updated_at=now()
		 WHERE place_id=$1 AND status='pending'
		 AND tsrange(start_time, end_time) && tsrange($2, $3)`,
		placeID, start, end)
	return err
}

func (r *BookingRepo) CalculateTotalPrice(ctx context.Context, placeID string, start, end time.Time) (*int, error) {
	var hourlyRate *int
	err := r.pool.QueryRow(ctx,
		`SELECT hourly_rate FROM places WHERE id=$1 AND deleted_at IS NULL AND type='studio'`,
		placeID).Scan(&hourlyRate)
	if err != nil {
		return nil, fmt.Errorf("get studio rate: %w", err)
	}
	if hourlyRate == nil {
		return nil, nil
	}

	hours := int(end.Sub(start).Hours())
	if hours < 1 {
		hours = 1
	}
	total := *hourlyRate * hours
	return &total, nil
}