package repository

import (
	"context"

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

// ListAirports returns the airports this tenant actually flies to or from,
// which is what the search form's dropdowns should offer. Airports themselves
// are global, but the filter keeps each agency's UI limited to its network.
func (r *Repository) ListAirports(ctx context.Context, tenantID uuid.UUID) ([]models.Airport, error) {
	const q = `
		SELECT DISTINCT a.id, a.iata_code, a.name, a.city, a.country, a.timezone
		FROM   airports a
		WHERE  EXISTS (
		           SELECT 1 FROM flights f
		           WHERE  f.tenant_id = $1
		             AND (f.origin_airport_id = a.id OR f.destination_airport_id = a.id)
		       )
		ORDER  BY a.city, a.iata_code`

	rows, err := r.pool.Query(ctx, q, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	airports := []models.Airport{}
	for rows.Next() {
		var a models.Airport
		if err := rows.Scan(&a.ID, &a.IATACode, &a.Name, &a.City, &a.Country, &a.Timezone); err != nil {
			return nil, err
		}
		airports = append(airports, a)
	}
	return airports, rows.Err()
}

// ListAirlines returns the carriers present in this tenant's inventory.
func (r *Repository) ListAirlines(ctx context.Context, tenantID uuid.UUID) ([]models.Airline, error) {
	const q = `
		SELECT DISTINCT al.id, al.iata_code, al.name, COALESCE(al.logo_url, '')
		FROM   airlines al
		JOIN   flights f ON f.airline_id = al.id AND f.tenant_id = $1
		ORDER  BY al.name`

	rows, err := r.pool.Query(ctx, q, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	airlines := []models.Airline{}
	for rows.Next() {
		var a models.Airline
		if err := rows.Scan(&a.ID, &a.IATACode, &a.Name, &a.LogoURL); err != nil {
			return nil, err
		}
		airlines = append(airlines, a)
	}
	return airlines, rows.Err()
}

// DashboardStats aggregates one tenant's trading position.
func (r *Repository) DashboardStats(ctx context.Context, tenantID uuid.UUID) (models.DashboardStats, error) {
	var s models.DashboardStats
	s.Currency = "USD"

	const totalsSQL = `
		SELECT COUNT(*),
		       COUNT(*) FILTER (WHERE status = 'confirmed'),
		       COUNT(*) FILTER (WHERE status = 'cancelled'),
		       COALESCE(SUM(total_amount)    FILTER (WHERE status = 'confirmed'), 0),
		       COALESCE(SUM(passenger_count) FILTER (WHERE status = 'confirmed'), 0)
		FROM   bookings
		WHERE  tenant_id = $1`

	if err := r.pool.QueryRow(ctx, totalsSQL, tenantID).Scan(
		&s.TotalBookings, &s.ConfirmedBookings, &s.CancelledBookings,
		&s.TotalRevenue, &s.SeatsSold,
	); err != nil {
		return s, err
	}

	const stockSQL = `
		SELECT COUNT(*) FROM flights
		WHERE tenant_id = $1 AND departure_time > now() AND status <> 'cancelled'`
	if err := r.pool.QueryRow(ctx, stockSQL, tenantID).Scan(&s.FlightsInStock); err != nil {
		return s, err
	}

	const routesSQL = `
		SELECT o.iata_code || ' to ' || d.iata_code AS route,
		       COUNT(*)                             AS bookings,
		       COALESCE(SUM(b.total_amount), 0)     AS revenue
		FROM   bookings b
		JOIN   flights  f ON f.id = b.flight_id
		JOIN   airports o ON o.id = f.origin_airport_id
		JOIN   airports d ON d.id = f.destination_airport_id
		WHERE  b.tenant_id = $1 AND b.status = 'confirmed'
		GROUP  BY route
		ORDER  BY revenue DESC
		LIMIT  5`

	rows, err := r.pool.Query(ctx, routesSQL, tenantID)
	if err != nil {
		return s, err
	}
	defer rows.Close()

	for rows.Next() {
		var entry struct {
			Route    string  `json:"route"`
			Bookings int     `json:"bookings"`
			Revenue  float64 `json:"revenue"`
		}
		if err := rows.Scan(&entry.Route, &entry.Bookings, &entry.Revenue); err != nil {
			return s, err
		}
		s.TopRoutes = append(s.TopRoutes, entry)
	}
	if err := rows.Err(); err != nil {
		return s, err
	}

	recent, _, err := r.ListBookings(ctx, tenantID, "", 5, 0)
	if err != nil {
		return s, err
	}
	s.RecentBookings = recent

	return s, nil
}