TS_Proto/backend/internal/repository/booking_repo.go

package repository

import (
	"context"
	"crypto/rand"
	"errors"
	"fmt"
	"math/big"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/travelswitch/travelhub/backend/internal/models"
)

// bookingSelect projects a booking together with its flight and the agent who
// sold it.
const bookingSelect = `
	SELECT b.id, b.booking_reference, b.passenger_count, b.total_amount,
	       b.currency, b.status, b.contact_email, b.contact_phone, b.created_at,
	       u.full_name,
	       f.id, f.flight_number, f.departure_time, f.arrival_time, f.aircraft,
	       f.cabin_class, f.base_price, f.currency, f.seats_total,
	       f.seats_available, f.status,
	       al.id, al.iata_code, al.name, COALESCE(al.logo_url, ''),
	       o.id, o.iata_code, o.name, o.city, o.country, o.timezone,
	       d.id, d.iata_code, d.name, d.city, d.country, d.timezone
	FROM   bookings b
	JOIN   users    u  ON u.id  = b.user_id
	JOIN   flights  f  ON f.id  = b.flight_id
	JOIN   airlines al ON al.id = f.airline_id
	JOIN   airports o  ON o.id  = f.origin_airport_id
	JOIN   airports d  ON d.id  = f.destination_airport_id`

func scanBooking(row pgx.Row) (models.Booking, error) {
	var b models.Booking
	f := &b.Flight
	err := row.Scan(
		&b.ID, &b.BookingReference, &b.PassengerCount, &b.TotalAmount,
		&b.Currency, &b.Status, &b.ContactEmail, &b.ContactPhone, &b.CreatedAt,
		&b.BookedBy,
		&f.ID, &f.FlightNumber, &f.DepartureTime, &f.ArrivalTime, &f.Aircraft,
		&f.CabinClass, &f.BasePrice, &f.Currency, &f.SeatsTotal,
		&f.SeatsAvailable, &f.Status,
		&f.Airline.ID, &f.Airline.IATACode, &f.Airline.Name, &f.Airline.LogoURL,
		&f.Origin.ID, &f.Origin.IATACode, &f.Origin.Name, &f.Origin.City,
		&f.Origin.Country, &f.Origin.Timezone,
		&f.Destination.ID, &f.Destination.IATACode, &f.Destination.Name,
		&f.Destination.City, &f.Destination.Country, &f.Destination.Timezone,
	)
	if err != nil {
		return models.Booking{}, err
	}
	f.DurationMinutes = int(f.ArrivalTime.Sub(f.DepartureTime).Minutes())
	b.Passengers = []models.Passenger{}
	return b, nil
}

// pnrAlphabet omits I, O, 0 and 1 because agents read references aloud.
const pnrAlphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789"

func newPNR() (string, error) {
	out := make([]byte, 6)
	for i := range out {
		n, err := rand.Int(rand.Reader, big.NewInt(int64(len(pnrAlphabet))))
		if err != nil {
			return "", err
		}
		out[i] = pnrAlphabet[n.Int64()]
	}
	return string(out), nil
}

// CreateBooking sells seats on a flight.
//
// The whole thing runs in one transaction and takes a row lock on the flight
// (SELECT ... FOR UPDATE) before reading availability, so two agents booking
// the last seats at the same moment cannot oversell: the second waits for the
// first to commit and then sees the decremented count.
func (r *Repository) CreateBooking(ctx context.Context, tenantID, userID uuid.UUID, req models.CreateBookingRequest) (models.Booking, error) {
	if len(req.Passengers) == 0 {
		return models.Booking{}, errors.New("at least one passenger is required")
	}

	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return models.Booking{}, err
	}
	defer tx.Rollback(ctx) //nolint:errcheck // no-op once committed

	var (
		price     float64
		currency  string
		available int
	)
	const lockSQL = `
		SELECT base_price, currency, seats_available
		FROM   flights
		WHERE  tenant_id = $1 AND id = $2 AND status <> 'cancelled'
		FOR UPDATE`

	err = tx.QueryRow(ctx, lockSQL, tenantID, req.FlightID).Scan(&price, &currency, &available)
	if errors.Is(err, pgx.ErrNoRows) {
		return models.Booking{}, ErrNotFound
	}
	if err != nil {
		return models.Booking{}, fmt.Errorf("lock flight: %w", err)
	}

	count := len(req.Passengers)
	if available < count {
		return models.Booking{}, ErrNoSeats
	}

	const decSQL = `UPDATE flights SET seats_available = seats_available - $3
	                WHERE tenant_id = $1 AND id = $2`
	if _, err := tx.Exec(ctx, decSQL, tenantID, req.FlightID, count); err != nil {
		return models.Booking{}, fmt.Errorf("reserve seats: %w", err)
	}

	const insertSQL = `
		INSERT INTO bookings (tenant_id, user_id, flight_id, booking_reference,
		                      passenger_count, total_amount, currency,
		                      contact_email, contact_phone)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id`

	// The PNR is unique per tenant; on the rare collision, draw again.
	var bookingID uuid.UUID
	for attempt := 0; attempt < 5; attempt++ {
		pnr, perr := newPNR()
		if perr != nil {
			return models.Booking{}, perr
		}
		err = tx.QueryRow(ctx, insertSQL,
			tenantID, userID, req.FlightID, pnr, count,
			price*float64(count), currency, req.ContactEmail, req.ContactPhone,
		).Scan(&bookingID)
		if err == nil {
			break
		}
		if !isUniqueViolation(err) {
			return models.Booking{}, fmt.Errorf("insert booking: %w", err)
		}
	}
	if err != nil {
		return models.Booking{}, fmt.Errorf("insert booking: %w", err)
	}

	const paxSQL = `
		INSERT INTO passengers (tenant_id, booking_id, first_name, last_name,
		                        date_of_birth, passport_number)
		VALUES ($1, $2, $3, $4, $5, $6)`

	for _, p := range req.Passengers {
		var dob *time.Time
		if p.DateOfBirth != "" {
			if parsed, perr := time.Parse("2006-01-02", p.DateOfBirth); perr == nil {
				dob = &parsed
			}
		}
		if _, err := tx.Exec(ctx, paxSQL, tenantID, bookingID,
			p.FirstName, p.LastName, dob, p.PassportNumber); err != nil {
			return models.Booking{}, fmt.Errorf("insert passenger: %w", err)
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return models.Booking{}, err
	}

	return r.BookingByID(ctx, tenantID, bookingID)
}

// BookingByID returns one booking with its passengers.
func (r *Repository) BookingByID(ctx context.Context, tenantID, bookingID uuid.UUID) (models.Booking, error) {
	q := bookingSelect + ` WHERE b.tenant_id = $1 AND b.id = $2`

	b, err := scanBooking(r.pool.QueryRow(ctx, q, tenantID, bookingID))
	if errors.Is(err, pgx.ErrNoRows) {
		return models.Booking{}, ErrNotFound
	}
	if err != nil {
		return models.Booking{}, err
	}

	pax, err := r.passengersFor(ctx, tenantID, []uuid.UUID{b.ID})
	if err != nil {
		return models.Booking{}, err
	}
	if list, ok := pax[b.ID]; ok {
		b.Passengers = list
	}
	return b, nil
}

// ListBookings returns a tenant's bookings, newest first, with passengers
// attached. Passengers are fetched in one extra query rather than one per
// booking.
func (r *Repository) ListBookings(ctx context.Context, tenantID uuid.UUID, status string, limit, offset int) ([]models.Booking, int, error) {
	where := "WHERE b.tenant_id = $1"
	args := []any{tenantID}
	if status != "" {
		args = append(args, status)
		where += fmt.Sprintf(" AND b.status = $%d", len(args))
	}

	var total int
	if err := r.pool.QueryRow(ctx, "SELECT count(*) FROM bookings b "+where, args...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("count bookings: %w", err)
	}

	if limit <= 0 || limit > 100 {
		limit = 25
	}
	if offset < 0 {
		offset = 0
	}

	q := fmt.Sprintf("%s %s ORDER BY b.created_at DESC LIMIT $%d OFFSET $%d",
		bookingSelect, where, len(args)+1, len(args)+2)
	args = append(args, limit, offset)

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

	bookings := []models.Booking{}
	ids := []uuid.UUID{}
	for rows.Next() {
		b, err := scanBooking(rows)
		if err != nil {
			return nil, 0, err
		}
		bookings = append(bookings, b)
		ids = append(ids, b.ID)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}

	if len(ids) > 0 {
		pax, err := r.passengersFor(ctx, tenantID, ids)
		if err != nil {
			return nil, 0, err
		}
		for i := range bookings {
			if list, ok := pax[bookings[i].ID]; ok {
				bookings[i].Passengers = list
			}
		}
	}

	return bookings, total, nil
}

// CancelBooking voids a booking and returns its seats to inventory.
func (r *Repository) CancelBooking(ctx context.Context, tenantID, bookingID uuid.UUID) (models.Booking, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return models.Booking{}, err
	}
	defer tx.Rollback(ctx) //nolint:errcheck // no-op once committed

	var (
		flightID uuid.UUID
		count    int
	)
	// Filtering on status here makes the cancel idempotent-safe: a second
	// cancel matches no row and returns ErrNotFound rather than crediting the
	// seats twice.
	const claimSQL = `
		UPDATE bookings SET status = 'cancelled'
		WHERE  tenant_id = $1 AND id = $2 AND status = 'confirmed'
		RETURNING flight_id, passenger_count`

	err = tx.QueryRow(ctx, claimSQL, tenantID, bookingID).Scan(&flightID, &count)
	if errors.Is(err, pgx.ErrNoRows) {
		return models.Booking{}, ErrNotFound
	}
	if err != nil {
		return models.Booking{}, fmt.Errorf("cancel booking: %w", err)
	}

	const restoreSQL = `
		UPDATE flights
		SET    seats_available = LEAST(seats_total, seats_available + $3)
		WHERE  tenant_id = $1 AND id = $2`
	if _, err := tx.Exec(ctx, restoreSQL, tenantID, flightID, count); err != nil {
		return models.Booking{}, fmt.Errorf("restore seats: %w", err)
	}

	if err := tx.Commit(ctx); err != nil {
		return models.Booking{}, err
	}
	return r.BookingByID(ctx, tenantID, bookingID)
}

// passengersFor loads passengers for several bookings at once, keyed by
// booking id.
func (r *Repository) passengersFor(ctx context.Context, tenantID uuid.UUID, bookingIDs []uuid.UUID) (map[uuid.UUID][]models.Passenger, error) {
	const q = `
		SELECT booking_id, id, first_name, last_name, date_of_birth,
		       passport_number, seat_number
		FROM   passengers
		WHERE  tenant_id = $1 AND booking_id = ANY($2)
		ORDER  BY last_name, first_name`

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

	out := map[uuid.UUID][]models.Passenger{}
	for rows.Next() {
		var (
			bookingID uuid.UUID
			p         models.Passenger
		)
		if err := rows.Scan(&bookingID, &p.ID, &p.FirstName, &p.LastName,
			&p.DateOfBirth, &p.PassportNumber, &p.SeatNumber); err != nil {
			return nil, err
		}
		out[bookingID] = append(out[bookingID], p)
	}
	return out, rows.Err()
}