package repository

import (
	"context"
	"errors"
	"fmt"
	"strings"

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

// flightSelect is the projection shared by every flight read, joining the two
// global reference tables so the API can return nested airline/airport objects.
const flightSelect = `
	SELECT 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   flights f
	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`

// scanFlight reads one row of flightSelect.
func scanFlight(row pgx.Row) (models.Flight, error) {
	var f models.Flight
	err := row.Scan(
		&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.Flight{}, err
	}
	f.DurationMinutes = int(f.ArrivalTime.Sub(f.DepartureTime).Minutes())
	return f, nil
}

// searchFilters turns a FlightSearch into WHERE fragments plus their arguments.
// tenantID is always $1, so every generated query is tenant-scoped by
// construction rather than by the caller remembering to add it.
func searchFilters(tenantID uuid.UUID, s models.FlightSearch) ([]string, []any) {
	where := []string{"f.tenant_id = $1", "f.status <> 'cancelled'", "f.departure_time > now()"}
	args := []any{tenantID}

	add := func(clause string, value any) {
		args = append(args, value)
		where = append(where, fmt.Sprintf(clause, len(args)))
	}

	// Airports are matched by IATA code through a subquery so the count query
	// does not need the joins that flightSelect carries.
	if s.Origin != "" {
		add("f.origin_airport_id = (SELECT id FROM airports WHERE iata_code = upper($%d))", s.Origin)
	}
	if s.Destination != "" {
		add("f.destination_airport_id = (SELECT id FROM airports WHERE iata_code = upper($%d))", s.Destination)
	}
	if s.CabinClass != "" {
		add("f.cabin_class = $%d", s.CabinClass)
	}
	if s.Passengers > 0 {
		add("f.seats_available >= $%d", s.Passengers)
	}
	if s.DepartDate != nil {
		// Half-open range keeps the composite index on departure_time usable.
		args = append(args, *s.DepartDate)
		where = append(where, fmt.Sprintf(
			"f.departure_time >= $%d AND f.departure_time < ($%d::timestamptz + interval '1 day')",
			len(args), len(args)))
	}

	return where, args
}

// orderClause whitelists sort options; anything unrecognised falls back to
// price so a hostile sortBy value can never reach the SQL text.
func orderClause(sortBy string) string {
	switch sortBy {
	case "duration":
		return "ORDER BY (f.arrival_time - f.departure_time) ASC, f.base_price ASC"
	case "departure":
		return "ORDER BY f.departure_time ASC, f.base_price ASC"
	default:
		return "ORDER BY f.base_price ASC, f.departure_time ASC"
	}
}

// SearchFlights returns one page of a tenant's inventory plus the total count
// matching the filters.
func (r *Repository) SearchFlights(ctx context.Context, tenantID uuid.UUID, s models.FlightSearch) ([]models.Flight, int, error) {
	where, args := searchFilters(tenantID, s)
	whereSQL := "WHERE " + strings.Join(where, " AND ")

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

	limit := s.Limit
	if limit <= 0 || limit > 100 {
		limit = 25
	}
	offset := s.Offset
	if offset < 0 {
		offset = 0
	}

	pageArgs := append(append([]any{}, args...), limit, offset)
	listSQL := fmt.Sprintf("%s %s %s LIMIT $%d OFFSET $%d",
		flightSelect, whereSQL, orderClause(s.SortBy), len(args)+1, len(args)+2)

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

	flights := []models.Flight{}
	for rows.Next() {
		f, err := scanFlight(rows)
		if err != nil {
			return nil, 0, err
		}
		flights = append(flights, f)
	}
	return flights, total, rows.Err()
}

// FlightByID fetches a single flight belonging to the tenant.
func (r *Repository) FlightByID(ctx context.Context, tenantID, flightID uuid.UUID) (models.Flight, error) {
	q := flightSelect + ` WHERE f.tenant_id = $1 AND f.id = $2`

	f, err := scanFlight(r.pool.QueryRow(ctx, q, tenantID, flightID))
	if errors.Is(err, pgx.ErrNoRows) {
		return models.Flight{}, ErrNotFound
	}
	return f, err
}