package bookings

import (
	"crussell/db"
	"crussell/mw"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"strconv"
	"strings"
	"time"

	"github.com/go-chi/chi/v5"
)

// Booking represents a booking in the system
type Booking struct {
	ID        string    `json:"id"`
	UserID    string    `json:"user_id"`
	StartTime time.Time `json:"start_time"`
	Status    string    `json:"status"`
	Notes     *string   `json:"notes,omitempty"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
	CreatedBy *string   `json:"created_by,omitempty"`

	// Joined fields
	Services []BookingService `json:"services,omitempty"`
	Payments []Payment        `json:"payments,omitempty"`
}

// BookingService represents a service associated with a booking
type BookingService struct {
	BookingID               string   `json:"booking_id"`
	ServiceID               string   `json:"service_id"`
	OverridePrice           *float64 `json:"override_price,omitempty"`
	OverrideDurationMinutes *int     `json:"override_duration_minutes,omitempty"`

	// Service details (joined)
	ServiceName         *string  `json:"service_name,omitempty"`
	ServiceDescription  *string  `json:"service_description,omitempty"`
	BasePrice           *float64 `json:"base_price,omitempty"`
	BaseDurationMinutes *int     `json:"base_duration_minutes,omitempty"`
}

// Payment represents a payment associated with a booking
type Payment struct {
	ID              string    `json:"id"`
	BookingID       string    `json:"booking_id"`
	PaymentType     string    `json:"payment_type"`
	PaymentMethod   string    `json:"payment_method"`
	VendorCode      *string   `json:"vendor_code,omitempty"`
	InvoiceNumber   *int      `json:"invoice_number,omitempty"`
	Status          string    `json:"status"`
	Amount          float64   `json:"amount"`
	IsVATApplicable bool      `json:"is_vat_applicable"`
	VATRate         *float64  `json:"vat_rate,omitempty"`
	VATAmount       *float64  `json:"vat_amount,omitempty"`
	NetAmount       *float64  `json:"net_amount,omitempty"`
	CreatedAt       time.Time `json:"created_at"`
	UpdatedAt       time.Time `json:"updated_at"`
	CreatedBy       *string   `json:"created_by,omitempty"`
}

// CreateBookingRequest represents the request payload for creating a new booking
type CreateBookingRequest struct {
	StartTime  time.Time `json:"start_time" validate:"required"`
	ServiceIDs []string  `json:"service_ids" validate:"required,min=1"`
	Notes      *string   `json:"notes,omitempty"`
}

// EditBookingRequest represents the request payload for editing a booking's start time
type EditBookingRequest struct {
	StartTime time.Time `json:"start_time" validate:"required"`
}

// ProgressBookingRequest represents the request payload for updating a booking's status
type ProgressBookingRequest struct {
	Status string `json:"status" validate:"required,oneof=pending confirmed in_progress completed client_cancelled we_cancelled re-schedule no_show"`
}

// ConfirmBookingRequest represents the request payload for confirming a booking
type ConfirmBookingRequest struct {
	ServiceOverrides []ServiceOverride `json:"service_overrides,omitempty"`
	Notes            *string           `json:"notes,omitempty"`
}

// ServiceOverride represents override values for a specific service in a booking
type ServiceOverride struct {
	ServiceID               string   `json:"service_id" validate:"required"`
	OverridePrice           *float64 `json:"override_price,omitempty"`
	OverrideDurationMinutes *int     `json:"override_duration_minutes,omitempty"`
}

// DeleteBookingRequest represents the request payload for deleting a booking with payment
type DeleteBookingRequest struct {
	Reason string `json:"reason" validate:"required,oneof=client_cancelled we_cancelled re-schedule no_show"`
}

// AdminUserSummary represents a small user summary for admin views
type AdminUserSummary struct {
	FullName      string  `json:"full_name"`
	ProfilePicURL *string `json:"profile_pic_url,omitempty"`
	Notes         *string `json:"notes,omitempty"`
}

// AdminBookingSummary represents a complete booking summary for admin view
type AdminBookingSummary struct {
	Booking         Booking                `json:"booking"`
	User            *AdminUserSummary      `json:"user,omitempty"`
	Services        []BookingServiceDetail `json:"services"`
	Payments        []Payment              `json:"payments"`
	TotalAmount     float64                `json:"total_amount"`
	AmountPaid      float64                `json:"amount_paid"`
	AmountDue       float64                `json:"amount_due"`
	DurationMinutes int                    `json:"duration_minutes"`
}

type UserSummary struct {
	FirstName     string  `json:"first_name"`
	LastName      string  `json:"last_name"`
	FullName      string  `json:"full_name"`
	Email         *string `json:"email,omitempty"`
	Phone         *string `json:"phone,omitempty"`
	DateOfBirth   *string `json:"date_of_birth,omitempty"`
	AccountRole   string  `json:"account_role"`
	LoyaltyStamps int     `json:"loyalty_stamps"`
	ReferralCode  *string `json:"referral_code,omitempty"`
	CreatedAt     string  `json:"created_at"`
}

type BookingServiceDetail struct {
	ServiceName             string   `json:"service_name"`
	ServiceDescription      *string  `json:"service_description,omitempty"`
	BasePrice               float64  `json:"base_price"`
	BaseDurationMinutes     int      `json:"base_duration_minutes"`
	OverridePrice           *float64 `json:"override_price,omitempty"`
	OverrideDurationMinutes *int     `json:"override_duration_minutes,omitempty"`
	IsActive                bool     `json:"is_active"`
	RequiresPatchTest       bool     `json:"requires_patch_test"`
	MinimumAgeRequired      int      `json:"minimum_age_required"`
}

// GetAllBookingsRequest represents query parameters for getting all bookings
type GetAllBookingsRequest struct {
	Status    *string `json:"status,omitempty"`
	StartDate *string `json:"start_date,omitempty"`
	EndDate   *string `json:"end_date,omitempty"`
	Page      int     `json:"page"`
	PerPage   int     `json:"per_page"`
}

// BookingListResponse represents a paginated list of bookings
type BookingListResponse struct {
	Bookings []Booking `json:"bookings"`
	Page     int       `json:"page"`
	PerPage  int       `json:"per_page"`
	Total    int       `json:"total"`
}

// SearchBookingsRequest represents search parameters
type SearchBookingsRequest struct {
	Query   string `json:"query"`
	Page    int    `json:"page"`
	PerPage int    `json:"per_page"`
}

// SearchBookingsResponse represents search results
type SearchBookingsResponse struct {
	Bookings []AdminBookingSummary `json:"bookings"`
	Page     int                   `json:"page"`
	PerPage  int                   `json:"per_page"`
	Total    int                   `json:"total"`
}

// Enhanced booking response for user endpoints
type UserBookingDetail struct {
	Booking         Booking `json:"booking"`
	TotalAmount     float64 `json:"total_amount"`
	AmountPaid      float64 `json:"amount_paid"`
	AmountDue       float64 `json:"amount_due"`
	DurationMinutes int     `json:"duration_minutes"`
}

// Enhanced booking response for admin endpoints
type AdminBookingDetail struct {
	Booking         Booking           `json:"booking"`
	User            *AdminUserSummary `json:"user,omitempty"`
	TotalAmount     float64           `json:"total_amount"`
	AmountPaid      float64           `json:"amount_paid"`
	AmountDue       float64           `json:"amount_due"`
	DurationMinutes int               `json:"duration_minutes"`
}

// Helper function to parse query parameters
func parseGetAllBookingsRequest(r *http.Request) GetAllBookingsRequest {
	req := GetAllBookingsRequest{
		Page:    1,
		PerPage: 20, // default page size
	}

	if status := r.URL.Query().Get("status"); status != "" {
		req.Status = &status
	}

	if startDate := r.URL.Query().Get("start_date"); startDate != "" {
		req.StartDate = &startDate
	}

	if endDate := r.URL.Query().Get("end_date"); endDate != "" {
		req.EndDate = &endDate
	}

	if pageStr := r.URL.Query().Get("page"); pageStr != "" {
		if page, err := strconv.Atoi(pageStr); err == nil && page > 0 {
			req.Page = page
		}
	}

	if perPageStr := r.URL.Query().Get("per_page"); perPageStr != "" {
		if perPage, err := strconv.Atoi(perPageStr); err == nil && perPage > 0 && perPage <= 100 {
			req.PerPage = perPage
		}
	}

	return req
}

// GET /api/bookings
func GetAllUserBookingsHandler(w http.ResponseWriter, r *http.Request) {
	userID, ok := r.Context().Value(mw.UserIDKey).(string)
	if !ok || userID == "" {
		http.Error(w, "Authentication required", http.StatusUnauthorized)
		return
	}

	// Parse query parameters
	req := parseGetAllBookingsRequest(r)

	// Build base query with user filter
	baseQuery := `
		SELECT id, user_id, start_time, status, notes, created_at, updated_at, created_by
		FROM bookings
		WHERE user_id = $1
	`

	countQuery := `SELECT COUNT(*) FROM bookings WHERE user_id = $1`
	var args []interface{}
	args = append(args, userID)
	paramCount := 2

	// Add filters
	if req.Status != nil {
		baseQuery += fmt.Sprintf(" AND status = $%d", paramCount)
		countQuery += fmt.Sprintf(" AND status = $%d", paramCount)
		args = append(args, *req.Status)
		paramCount++
	}

	if req.StartDate != nil {
		baseQuery += fmt.Sprintf(" AND start_time >= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time >= $%d", paramCount)
		startTime, err := time.Parse("2006-01-02", *req.StartDate)
		if err != nil {
			http.Error(w, "Invalid start_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		args = append(args, startTime)
		paramCount++
	}

	if req.EndDate != nil {
		baseQuery += fmt.Sprintf(" AND start_time <= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time <= $%d", paramCount)
		endTime, err := time.Parse("2006-01-02", *req.EndDate)
		if err != nil {
			http.Error(w, "Invalid end_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		// Add end of day
		endTime = endTime.Add(23*time.Hour + 59*time.Minute + 59*time.Second)
		args = append(args, endTime)
		paramCount++
	}

	// Add ordering and pagination
	baseQuery += " ORDER BY start_time DESC"
	if req.PerPage > 0 {
		baseQuery += fmt.Sprintf(" LIMIT $%d OFFSET $%d", paramCount, paramCount+1)
		args = append(args, req.PerPage, (req.Page-1)*req.PerPage)
	}

	// Get total count
	var total int
	err := db.DB.QueryRow(r.Context(), countQuery, args[:1]...).Scan(&total)
	if err != nil {
		log.Printf("Failed to get booking count for user %s: %v", userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Get bookings
	rows, err := db.DB.Query(r.Context(), baseQuery, args...)
	if err != nil {
		log.Printf("Failed to fetch bookings for user %s: %v", userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var bookings []Booking
	for rows.Next() {
		var b Booking
		var createdBy sql.NullString
		err := rows.Scan(&b.ID, &b.UserID, &b.StartTime, &b.Status, &b.Notes, &b.CreatedAt, &b.UpdatedAt, &createdBy)
		if err != nil {
			log.Printf("Failed to scan booking row: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}
		if createdBy.Valid {
			b.CreatedBy = &createdBy.String
		}
		bookings = append(bookings, b)
	}

	response := BookingListResponse{
		Bookings: bookings,
		Page:     req.Page,
		PerPage:  req.PerPage,
		Total:    total,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(response); err != nil {
		log.Printf("Failed to encode response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// GET /api/admin/bookings
func GetAllAdminBookingsHandler(w http.ResponseWriter, r *http.Request) {
	// Parse query parameters
	req := parseGetAllBookingsRequest(r)

	// Build base query without user filter
	baseQuery := `
		SELECT id, user_id, start_time, status, notes, created_at, updated_at, created_by,
		       u.fn, u.profile_pic_url, u.notes as user_notes
		FROM bookings b
		LEFT JOIN users u ON b.user_id = u.id
	`

	countQuery := `SELECT COUNT(*) FROM bookings`
	var args []interface{}
	paramCount := 1

	// Add filters
	if req.Status != nil {
		baseQuery += fmt.Sprintf(" WHERE b.status = $%d", paramCount)
		countQuery += fmt.Sprintf(" WHERE status = $%d", paramCount)
		args = append(args, *req.Status)
		paramCount++
	} else {
		baseQuery += " WHERE 1=1"
	}

	if req.StartDate != nil {
		baseQuery += fmt.Sprintf(" AND b.start_time >= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time >= $%d", paramCount)
		startTime, err := time.Parse("2006-01-02", *req.StartDate)
		if err != nil {
			http.Error(w, "Invalid start_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		args = append(args, startTime)
		paramCount++
	}

	if req.EndDate != nil {
		baseQuery += fmt.Sprintf(" AND b.start_time <= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time <= $%d", paramCount)
		endTime, err := time.Parse("2006-01-02", *req.EndDate)
		if err != nil {
			http.Error(w, "Invalid end_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		endTime = endTime.Add(23*time.Hour + 59*time.Minute + 59*time.Second)
		args = append(args, endTime)
		paramCount++
	}

	// Add ordering and pagination
	baseQuery += " ORDER BY b.start_time DESC"
	if req.PerPage > 0 {
		baseQuery += fmt.Sprintf(" LIMIT $%d OFFSET $%d", paramCount, paramCount+1)
		args = append(args, req.PerPage, (req.Page-1)*req.PerPage)
	}

	// Get total count
	var total int
	err := db.DB.QueryRow(r.Context(), countQuery, args...).Scan(&total)
	if err != nil {
		log.Printf("Failed to get total booking count: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Get bookings
	rows, err := db.DB.Query(r.Context(), baseQuery, args...)
	if err != nil {
		log.Printf("Failed to fetch all bookings: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var bookings []Booking
	for rows.Next() {
		var b Booking
		var createdBy sql.NullString
		var userFN, profilePicURL, userNotes sql.NullString

		err := rows.Scan(&b.ID, &b.UserID, &b.StartTime, &b.Status, &b.Notes, &b.CreatedAt, &b.UpdatedAt, &createdBy,
			&userFN, &profilePicURL, &userNotes)
		if err != nil {
			log.Printf("Failed to scan booking row: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}
		if createdBy.Valid {
			b.CreatedBy = &createdBy.String
		}
		bookings = append(bookings, b)
	}

	response := BookingListResponse{
		Bookings: bookings,
		Page:     req.Page,
		PerPage:  req.PerPage,
		Total:    total,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(response); err != nil {
		log.Printf("Failed to encode response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// GET /api/admin/bookings/user/{user_id}
func GetAllBookingsByUserHandler(w http.ResponseWriter, r *http.Request) {
	userID := chi.URLParam(r, "user_id")
	if userID == "" {
		http.Error(w, "User ID is required", http.StatusBadRequest)
		return
	}

	// Parse query parameters
	req := parseGetAllBookingsRequest(r)

	// Build query with specific user filter
	baseQuery := `
		SELECT id, user_id, start_time, status, notes, created_at, updated_at, created_by,
		       u.fn, u.profile_pic_url, u.notes as user_notes
		FROM bookings b
		LEFT JOIN users u ON b.user_id = u.id
		WHERE b.user_id = $1
	`

	countQuery := `SELECT COUNT(*) FROM bookings WHERE user_id = $1`
	var args []interface{}
	args = append(args, userID)
	paramCount := 2

	// Add filters
	if req.Status != nil {
		baseQuery += fmt.Sprintf(" AND b.status = $%d", paramCount)
		countQuery += fmt.Sprintf(" AND status = $%d", paramCount)
		args = append(args, *req.Status)
		paramCount++
	}

	if req.StartDate != nil {
		baseQuery += fmt.Sprintf(" AND b.start_time >= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time >= $%d", paramCount)
		startTime, err := time.Parse("2006-01-02", *req.StartDate)
		if err != nil {
			http.Error(w, "Invalid start_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		args = append(args, startTime)
		paramCount++
	}

	if req.EndDate != nil {
		baseQuery += fmt.Sprintf(" AND b.start_time <= $%d", paramCount)
		countQuery += fmt.Sprintf(" AND start_time <= $%d", paramCount)
		endTime, err := time.Parse("2006-01-02", *req.EndDate)
		if err != nil {
			http.Error(w, "Invalid end_date format, use YYYY-MM-DD", http.StatusBadRequest)
			return
		}
		endTime = endTime.Add(23*time.Hour + 59*time.Minute + 59*time.Second)
		args = append(args, endTime)
		paramCount++
	}

	// Add ordering and pagination
	baseQuery += " ORDER BY b.start_time DESC"
	if req.PerPage > 0 {
		baseQuery += fmt.Sprintf(" LIMIT $%d OFFSET $%d", paramCount, paramCount+1)
		args = append(args, req.PerPage, (req.Page-1)*req.PerPage)
	}

	// Get total count
	var total int
	err := db.DB.QueryRow(r.Context(), countQuery, args[:1]...).Scan(&total)
	if err != nil {
		log.Printf("Failed to get booking count for user %s: %v", userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Get bookings
	rows, err := db.DB.Query(r.Context(), baseQuery, args...)
	if err != nil {
		log.Printf("Failed to fetch bookings for user %s: %v", userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var bookings []Booking
	for rows.Next() {
		var b Booking
		var createdBy sql.NullString
		var userFN, profilePicURL, userNotes sql.NullString

		err := rows.Scan(&b.ID, &b.UserID, &b.StartTime, &b.Status, &b.Notes, &b.CreatedAt, &b.UpdatedAt, &createdBy,
			&userFN, &profilePicURL, &userNotes)
		if err != nil {
			log.Printf("Failed to scan booking row: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}
		if createdBy.Valid {
			b.CreatedBy = &createdBy.String
		}
		bookings = append(bookings, b)
	}

	response := BookingListResponse{
		Bookings: bookings,
		Page:     req.Page,
		PerPage:  req.PerPage,
		Total:    total,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(response); err != nil {
		log.Printf("Failed to encode response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// GET /api/admin/bookings/{id}/summary
func GetAdminBookingSummaryHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// ----------------------------
	// 1. Fetch booking + user
	// ----------------------------
	var summary AdminBookingSummary
	var booking Booking
	var user AdminUserSummary
	var createdBy sql.NullString
	var profilePicURL, notes sql.NullString

	err := db.DB.QueryRow(r.Context(), `
		SELECT 
			b.id, b.user_id, b.start_time, b.status, b.notes,
			b.created_at, b.updated_at, b.created_by,
			u.fn, u.profile_pic_url, u.notes
		FROM bookings b
		LEFT JOIN users u ON b.user_id = u.id
		WHERE b.id = $1
	`, bookingID).Scan(
		&booking.ID, &booking.UserID, &booking.StartTime, &booking.Status, &booking.Notes,
		&booking.CreatedAt, &booking.UpdatedAt, &createdBy,
		&user.FullName, &profilePicURL, &notes,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to fetch booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	if createdBy.Valid {
		booking.CreatedBy = &createdBy.String
	}

	// Only include profile_pic_url if not null and not empty
	if profilePicURL.Valid && profilePicURL.String != "" {
		user.ProfilePicURL = &profilePicURL.String
	}

	// Only include notes if not null and not empty
	if notes.Valid && notes.String != "" {
		user.Notes = &notes.String
	}

	summary.Booking = booking
	summary.User = &user

	// ----------------------------
	// 2. Fetch services
	// ----------------------------
	serviceRows, err := db.DB.Query(r.Context(), `
		SELECT 
			bs.override_price, bs.override_duration_minutes,
			s.name as service_name, s.description as service_description,
			s.price as base_price, s.duration_minutes as base_duration_minutes,
			s.is_active, s.patch_test_duration_hours, s.minimum_age_required
		FROM booking_services bs
		LEFT JOIN services s ON bs.service_id = s.id
		WHERE bs.booking_id = $1
		ORDER BY s.name
	`, bookingID)
	if err != nil {
		log.Printf("Failed to fetch services for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer serviceRows.Close()

	for serviceRows.Next() {
		var s BookingServiceDetail
		var overridePrice sql.NullFloat64
		var overrideDuration sql.NullInt32
		var patchTestHours sql.NullInt32

		err := serviceRows.Scan(
			&overridePrice, &overrideDuration,
			&s.ServiceName, &s.ServiceDescription,
			&s.BasePrice, &s.BaseDurationMinutes,
			&s.IsActive, &patchTestHours, &s.MinimumAgeRequired,
		)
		if err != nil {
			log.Printf("Failed to scan service row for booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		// Set overrides if they exist
		if overridePrice.Valid {
			s.OverridePrice = &overridePrice.Float64
		}
		if overrideDuration.Valid {
			d := int(overrideDuration.Int32)
			s.OverrideDurationMinutes = &d
		}
		if patchTestHours.Valid {
			s.RequiresPatchTest = patchTestHours.Int32 > 0
		}

		summary.Services = append(summary.Services, s)

		// Compute totals using overrides where they exist
		if s.OverridePrice != nil {
			summary.TotalAmount += *s.OverridePrice
		} else {
			summary.TotalAmount += s.BasePrice
		}

		if s.OverrideDurationMinutes != nil {
			summary.DurationMinutes += *s.OverrideDurationMinutes
		} else {
			summary.DurationMinutes += s.BaseDurationMinutes
		}
	}

	// ----------------------------
	// 3. Fetch payments
	// ----------------------------
	paymentRows, err := db.DB.Query(r.Context(), `
		SELECT
			id, payment_type, payment_method, vendor_code, invoice_number,
			status, amount, is_vat_applicable, vat_rate, vat_amount, net_amount,
			created_at, updated_at, created_by
		FROM payments
		WHERE booking_id = $1
		ORDER BY created_at DESC
	`, bookingID)
	if err != nil {
		log.Printf("Failed to fetch payments for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer paymentRows.Close()

	for paymentRows.Next() {
		var p Payment
		var vendorCode sql.NullString
		var invoiceNumber sql.NullInt32
		var vatRate, vatAmount, netAmount sql.NullFloat64
		var createdBy sql.NullString

		err := paymentRows.Scan(
			&p.ID, &p.PaymentType, &p.PaymentMethod, &vendorCode, &invoiceNumber,
			&p.Status, &p.Amount, &p.IsVATApplicable, &vatRate, &vatAmount, &netAmount,
			&p.CreatedAt, &p.UpdatedAt, &createdBy,
		)
		if err != nil {
			log.Printf("Failed to scan payment row for booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if vendorCode.Valid {
			p.VendorCode = &vendorCode.String
		}
		if invoiceNumber.Valid {
			num := int(invoiceNumber.Int32)
			p.InvoiceNumber = &num
		}
		if vatRate.Valid {
			p.VATRate = &vatRate.Float64
		}
		if vatAmount.Valid {
			p.VATAmount = &vatAmount.Float64
		}
		if netAmount.Valid {
			p.NetAmount = &netAmount.Float64
		}
		if createdBy.Valid {
			p.CreatedBy = &createdBy.String
		}

		summary.Payments = append(summary.Payments, p)

		if p.Status == "completed" {
			summary.AmountPaid += p.Amount
		}
	}

	summary.AmountDue = summary.TotalAmount - summary.AmountPaid

	// ----------------------------
	// Return JSON response
	// ----------------------------
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	if err := json.NewEncoder(w).Encode(summary); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// GET /api/admin/bookings/{id}
func GetAdminBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// ----------------------------
	// 1. Fetch booking + user summary
	// ----------------------------
	var booking Booking
	var createdBy sql.NullString
	var userFN, profilePicURL, userNotes sql.NullString
	var userSummary *AdminUserSummary

	err := db.DB.QueryRow(r.Context(), `
		SELECT 
			b.id, b.user_id, b.start_time, b.status, b.notes,
			b.created_at, b.updated_at, b.created_by,
			u.fn, u.profile_pic_url, u.notes
		FROM bookings b
		LEFT JOIN users u ON b.user_id = u.id
		WHERE b.id = $1
	`, bookingID).Scan(
		&booking.ID, &booking.UserID, &booking.StartTime, &booking.Status,
		&booking.Notes, &booking.CreatedAt, &booking.UpdatedAt, &createdBy,
		&userFN, &profilePicURL, &userNotes,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to fetch admin booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	if createdBy.Valid {
		booking.CreatedBy = &createdBy.String
	}

	// Create user summary if user exists
	if userFN.Valid {
		userSummary = &AdminUserSummary{
			FullName: userFN.String,
		}
		if profilePicURL.Valid && profilePicURL.String != "" {
			userSummary.ProfilePicURL = &profilePicURL.String
		}
		if userNotes.Valid && userNotes.String != "" {
			userSummary.Notes = &userNotes.String
		}
	}

	// ----------------------------
	// 2. Fetch services
	// ----------------------------
	var totalAmount float64
	var durationMinutes int

	serviceRows, err := db.DB.Query(r.Context(), `
        SELECT 
            bs.service_id, bs.override_price, bs.override_duration_minutes,
            s.name, s.description, s.price, s.duration_minutes
        FROM booking_services bs
        LEFT JOIN services s ON bs.service_id = s.id
        WHERE bs.booking_id = $1
        ORDER BY s.name
    `, bookingID)
	if err != nil {
		log.Printf("Failed to fetch services for admin booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer serviceRows.Close()

	for serviceRows.Next() {
		var s BookingService
		var overridePrice sql.NullFloat64
		var overrideDuration sql.NullInt32
		var name, description sql.NullString
		var basePrice sql.NullFloat64
		var baseDuration sql.NullInt32

		err := serviceRows.Scan(
			&s.ServiceID,
			&overridePrice, &overrideDuration,
			&name, &description, &basePrice, &baseDuration,
		)
		if err != nil {
			log.Printf("Failed to scan service row for admin booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if overridePrice.Valid {
			s.OverridePrice = &overridePrice.Float64
			totalAmount += *s.OverridePrice
		} else if basePrice.Valid {
			totalAmount += basePrice.Float64
		}

		if overrideDuration.Valid {
			d := int(overrideDuration.Int32)
			s.OverrideDurationMinutes = &d
			durationMinutes += *s.OverrideDurationMinutes
		} else if baseDuration.Valid {
			durationMinutes += int(baseDuration.Int32)
		}
		if name.Valid {
			s.ServiceName = &name.String
		}
		if description.Valid {
			s.ServiceDescription = &description.String
		}
		if basePrice.Valid {
			s.BasePrice = &basePrice.Float64
		}
		if baseDuration.Valid {
			d := int(baseDuration.Int32)
			s.BaseDurationMinutes = &d
		}

		booking.Services = append(booking.Services, s)
	}

	// ----------------------------
	// 3. Fetch payments
	// ----------------------------
	var amountPaid float64

	paymentRows, err := db.DB.Query(r.Context(), `
        SELECT
            id, payment_type, payment_method, vendor_code, invoice_number,
            status, amount, is_vat_applicable, vat_rate, vat_amount, net_amount,
            created_at, updated_at, created_by
        FROM payments
        WHERE booking_id = $1
        ORDER BY created_at DESC
    `, bookingID)
	if err != nil {
		log.Printf("Failed to fetch payments for admin booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	for paymentRows.Next() {
		var p Payment
		var vendorCode sql.NullString
		var invoiceNumber sql.NullInt32
		var vatRate, vatAmount, netAmount sql.NullFloat64
		var createdBy sql.NullString

		err := paymentRows.Scan(
			&p.ID, &p.PaymentType, &p.PaymentMethod, &vendorCode, &invoiceNumber,
			&p.Status, &p.Amount, &p.IsVATApplicable, &vatRate, &vatAmount, &netAmount,
			&p.CreatedAt, &p.UpdatedAt, &createdBy,
		)
		if err != nil {
			log.Printf("Failed to scan payment row for admin booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if vendorCode.Valid {
			p.VendorCode = &vendorCode.String
		}
		if invoiceNumber.Valid {
			num := int(invoiceNumber.Int32)
			p.InvoiceNumber = &num
		}
		if vatRate.Valid {
			p.VATRate = &vatRate.Float64
		}
		if vatAmount.Valid {
			p.VATAmount = &vatAmount.Float64
		}
		if netAmount.Valid {
			p.NetAmount = &netAmount.Float64
		}
		if createdBy.Valid {
			p.CreatedBy = &createdBy.String
		}

		if p.Status == "completed" {
			amountPaid += p.Amount
		}
		booking.Payments = append(booking.Payments, p)
	}

	amountDue := totalAmount - amountPaid

	// Create enhanced response with totals
	response := struct {
		Booking         Booking           `json:"booking"`
		User            *AdminUserSummary `json:"user,omitempty"`
		TotalAmount     float64           `json:"total_amount"`
		AmountPaid      float64           `json:"amount_paid"`
		AmountDue       float64           `json:"amount_due"`
		DurationMinutes int               `json:"duration_minutes"`
	}{
		Booking:         booking,
		User:            userSummary,
		TotalAmount:     totalAmount,
		AmountPaid:      amountPaid,
		AmountDue:       amountDue,
		DurationMinutes: durationMinutes,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(response); err != nil {
		log.Printf("Failed to encode admin booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// GET /api/admin/bookings/search
func SearchAdminBookingsHandler(w http.ResponseWriter, r *http.Request) {
	query := r.URL.Query().Get("q")
	if query == "" {
		http.Error(w, "Search query 'q' is required", http.StatusBadRequest)
		return
	}

	// Parse pagination
	page := 1
	perPage := 20
	if pageStr := r.URL.Query().Get("page"); pageStr != "" {
		if p, err := strconv.Atoi(pageStr); err == nil && p > 0 {
			page = p
		}
	}
	if perPageStr := r.URL.Query().Get("per_page"); perPageStr != "" {
		if pp, err := strconv.Atoi(perPageStr); err == nil && pp > 0 && pp <= 100 {
			perPage = pp
		}
	}

	// Escape the search query to prevent SQL injection in LIKE patterns
	escapedQuery := strings.ReplaceAll(query, `\`, `\\`)
	escapedQuery = strings.ReplaceAll(escapedQuery, `%`, `\%`)
	escapedQuery = strings.ReplaceAll(escapedQuery, `_`, `\_`)
	searchPattern := "%" + escapedQuery + "%"

	// Build the main search query with JOINs to get all relevant data in one go
	searchQuery := `
		WITH matched_bookings AS (
			SELECT DISTINCT b.id
			FROM bookings b
			LEFT JOIN users u ON b.user_id = u.id
			LEFT JOIN booking_services bs ON b.id = bs.booking_id
			LEFT JOIN services s ON bs.service_id = s.id
			LEFT JOIN payments p ON b.id = p.booking_id
			WHERE 
				b.id ILIKE $1 ESCAPE '\' OR
				b.notes ILIKE $1 ESCAPE '\' OR
				b.status::text ILIKE $1 ESCAPE '\' OR
				u.n_first_name ILIKE $1 ESCAPE '\' OR
				u.n_last_name ILIKE $1 ESCAPE '\' OR
				u.fn ILIKE $1 ESCAPE '\' OR
				u.email ILIKE $1 ESCAPE '\' OR
				u.phone ILIKE $1 ESCAPE '\' OR
				s.name ILIKE $1 ESCAPE '\' OR
				s.description ILIKE $1 ESCAPE '\' OR
				p.vendor_code ILIKE $1 ESCAPE '\' OR
				p.invoice_number::text ILIKE $1 ESCAPE '\'
		)
		SELECT 
			b.id, b.user_id, b.start_time, b.status, b.notes,
			b.created_at, b.updated_at, b.created_by,
			u.fn, u.profile_pic_url, u.notes as user_notes,
			u.n_first_name, u.n_last_name, u.email, u.phone,
			u.date_of_birth, u.account_role, u.loyalty_stamps,
			u.referral_code, u.created_at as user_created_at
		FROM bookings b
		LEFT JOIN users u ON b.user_id = u.id
		INNER JOIN matched_bookings mb ON b.id = mb.id
		ORDER BY b.start_time DESC
		LIMIT $2 OFFSET $3
	`

	countQuery := `
		WITH matched_bookings AS (
			SELECT DISTINCT b.id
			FROM bookings b
			LEFT JOIN users u ON b.user_id = u.id
			LEFT JOIN booking_services bs ON b.id = bs.booking_id
			LEFT JOIN services s ON bs.service_id = s.id
			LEFT JOIN payments p ON b.id = p.booking_id
			WHERE 
				b.id ILIKE $1 ESCAPE '\' OR
				b.notes ILIKE $1 ESCAPE '\' OR
				b.status::text ILIKE $1 ESCAPE '\' OR
				u.n_first_name ILIKE $1 ESCAPE '\' OR
				u.n_last_name ILIKE $1 ESCAPE '\' OR
				u.fn ILIKE $1 ESCAPE '\' OR
				u.email ILIKE $1 ESCAPE '\' OR
				u.phone ILIKE $1 ESCAPE '\' OR
				s.name ILIKE $1 ESCAPE '\' OR
				s.description ILIKE $1 ESCAPE '\' OR
				p.vendor_code ILIKE $1 ESCAPE '\' OR
				p.invoice_number::text ILIKE $1 ESCAPE '\'
		)
		SELECT COUNT(*) FROM matched_bookings
	`

	offset := (page - 1) * perPage

	// Get total count
	var total int
	err := db.DB.QueryRow(r.Context(), countQuery, searchPattern).Scan(&total)
	if err != nil {
		log.Printf("Failed to get search count: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Get bookings
	rows, err := db.DB.Query(r.Context(), searchQuery, searchPattern, perPage, offset)
	if err != nil {
		log.Printf("Failed to search bookings: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var bookingIDs []string
	bookingMap := make(map[string]*AdminBookingSummary)

	for rows.Next() {
		var booking Booking
		var user AdminUserSummary
		var fullUser UserSummary
		var createdBy sql.NullString
		var profilePicURL, userNotes sql.NullString
		var email, phone, referralCode sql.NullString
		var dateOfBirth, userCreatedAt sql.NullTime

		err := rows.Scan(
			&booking.ID, &booking.UserID, &booking.StartTime, &booking.Status, &booking.Notes,
			&booking.CreatedAt, &booking.UpdatedAt, &createdBy,
			&fullUser.FullName, &profilePicURL, &userNotes,
			&fullUser.FirstName, &fullUser.LastName, &email, &phone,
			&dateOfBirth, &fullUser.AccountRole, &fullUser.LoyaltyStamps,
			&referralCode, &userCreatedAt,
		)
		if err != nil {
			log.Printf("Failed to scan search result: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if createdBy.Valid {
			booking.CreatedBy = &createdBy.String
		}

		// Build user summary for response
		if fullUser.FullName != "" {
			user.FullName = fullUser.FullName
			if profilePicURL.Valid && profilePicURL.String != "" {
				user.ProfilePicURL = &profilePicURL.String
			}
			if userNotes.Valid && userNotes.String != "" {
				user.Notes = &userNotes.String
			}
		}

		summary := AdminBookingSummary{
			Booking: booking,
			User:    &user,
		}

		bookingIDs = append(bookingIDs, booking.ID)
		bookingMap[booking.ID] = &summary
	}

	// If no bookings found, return empty result
	if len(bookingIDs) == 0 {
		response := SearchBookingsResponse{
			Bookings: []AdminBookingSummary{},
			Page:     page,
			PerPage:  perPage,
			Total:    total,
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(response)
		return
	}

	// Fetch services for all found bookings
	serviceQuery := `
		SELECT 
			bs.booking_id,
			bs.override_price, bs.override_duration_minutes,
			s.name as service_name, s.description as service_description,
			s.price as base_price, s.duration_minutes as base_duration_minutes,
			s.is_active, s.patch_test_duration_hours, s.minimum_age_required
		FROM booking_services bs
		LEFT JOIN services s ON bs.service_id = s.id
		WHERE bs.booking_id = ANY($1::text[])
		ORDER BY bs.booking_id, s.name
	`

	serviceRows, err := db.DB.Query(r.Context(), serviceQuery, bookingIDs)
	if err != nil {
		log.Printf("Failed to fetch services for search: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer serviceRows.Close()

	for serviceRows.Next() {
		var bookingID string
		var s BookingServiceDetail
		var overridePrice sql.NullFloat64
		var overrideDuration sql.NullInt32
		var patchTestHours sql.NullInt32

		err := serviceRows.Scan(
			&bookingID,
			&overridePrice, &overrideDuration,
			&s.ServiceName, &s.ServiceDescription,
			&s.BasePrice, &s.BaseDurationMinutes,
			&s.IsActive, &patchTestHours, &s.MinimumAgeRequired,
		)
		if err != nil {
			log.Printf("Failed to scan service row for search: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if overridePrice.Valid {
			s.OverridePrice = &overridePrice.Float64
		}
		if overrideDuration.Valid {
			d := int(overrideDuration.Int32)
			s.OverrideDurationMinutes = &d
		}
		if patchTestHours.Valid {
			s.RequiresPatchTest = patchTestHours.Int32 > 0
		}

		summary := bookingMap[bookingID]
		if summary != nil {
			summary.Services = append(summary.Services, s)

			// Calculate totals using overrides
			if s.OverridePrice != nil {
				summary.TotalAmount += *s.OverridePrice
			} else {
				summary.TotalAmount += s.BasePrice
			}

			if s.OverrideDurationMinutes != nil {
				summary.DurationMinutes += *s.OverrideDurationMinutes
			} else {
				summary.DurationMinutes += s.BaseDurationMinutes
			}
		}
	}

	// Fetch payments for all found bookings
	paymentQuery := `
		SELECT
			p.booking_id,
			p.id, p.payment_type, p.payment_method, p.vendor_code, p.invoice_number,
			p.status, p.amount, p.is_vat_applicable, p.vat_rate, p.vat_amount, p.net_amount,
			p.created_at, p.updated_at, p.created_by
		FROM payments p
		WHERE p.booking_id = ANY($1::text[])
		ORDER BY p.booking_id, p.created_at DESC
	`

	paymentRows, err := db.DB.Query(r.Context(), paymentQuery, bookingIDs)
	if err != nil {
		log.Printf("Failed to fetch payments for search: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer paymentRows.Close()

	for paymentRows.Next() {
		var bookingID string
		var p Payment
		var vendorCode sql.NullString
		var invoiceNumber sql.NullInt32
		var vatRate, vatAmount, netAmount sql.NullFloat64
		var createdBy sql.NullString

		err := paymentRows.Scan(
			&bookingID,
			&p.ID, &p.PaymentType, &p.PaymentMethod, &vendorCode, &invoiceNumber,
			&p.Status, &p.Amount, &p.IsVATApplicable, &vatRate, &vatAmount, &netAmount,
			&p.CreatedAt, &p.UpdatedAt, &createdBy,
		)
		if err != nil {
			log.Printf("Failed to scan payment row for search: %v", err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if vendorCode.Valid {
			p.VendorCode = &vendorCode.String
		}
		if invoiceNumber.Valid {
			num := int(invoiceNumber.Int32)
			p.InvoiceNumber = &num
		}
		if vatRate.Valid {
			p.VATRate = &vatRate.Float64
		}
		if vatAmount.Valid {
			p.VATAmount = &vatAmount.Float64
		}
		if netAmount.Valid {
			p.NetAmount = &netAmount.Float64
		}
		if createdBy.Valid {
			p.CreatedBy = &createdBy.String
		}

		summary := bookingMap[bookingID]
		if summary != nil {
			summary.Payments = append(summary.Payments, p)

			if p.Status == "completed" {
				summary.AmountPaid += p.Amount
			}
		}
	}

	// Calculate amount due for each booking
	for _, summary := range bookingMap {
		summary.AmountDue = summary.TotalAmount - summary.AmountPaid
	}

	// Build response array in the same order as bookingIDs
	var results []AdminBookingSummary
	for _, id := range bookingIDs {
		if summary, exists := bookingMap[id]; exists {
			results = append(results, *summary)
		}
	}

	response := SearchBookingsResponse{
		Bookings: results,
		Page:     page,
		PerPage:  perPage,
		Total:    total,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(response); err != nil {
		log.Printf("Failed to encode search response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// POST /api/bookings
func CreateBookingHandler(w http.ResponseWriter, r *http.Request) {
	// Get user ID from context
	userID, ok := r.Context().Value(mw.UserIDKey).(string)
	if !ok || userID == "" {
		http.Error(w, "Authentication required", http.StatusUnauthorized)
		return
	}

	// Parse and validate request
	var req CreateBookingRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode request: %v", err)
		http.Error(w, "Invalid request", http.StatusBadRequest)
		return
	}

	// Basic validation
	if req.StartTime.IsZero() {
		http.Error(w, "Start time is required", http.StatusBadRequest)
		return
	}
	if len(req.ServiceIDs) == 0 {
		http.Error(w, "At least one service is required", http.StatusBadRequest)
		return
	}
	if req.StartTime.Before(time.Now()) {
		http.Error(w, "Start time cannot be in the past", http.StatusBadRequest)
		return
	}

	// Get created by from context (if available)
	var createdBy *string
	if creatorID, ok := r.Context().Value(mw.UserIDKey).(string); ok {
		createdBy = &creatorID
	}

	tx, err := db.DB.Begin(r.Context())
	if err != nil {
		log.Printf("Failed to start transaction: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer tx.Rollback(r.Context())

	// Insert new booking
	bookingQuery := `
		INSERT INTO bookings (user_id, start_time, notes, created_by)
		VALUES ($1, $2, $3, $4)
		RETURNING id, user_id, start_time, status, notes, created_at, updated_at, created_by
	`

	var booking Booking
	err = tx.QueryRow(r.Context(),
		bookingQuery,
		userID,
		req.StartTime,
		req.Notes,
		createdBy,
	).Scan(
		&booking.ID,
		&booking.UserID,
		&booking.StartTime,
		&booking.Status,
		&booking.Notes,
		&booking.CreatedAt,
		&booking.UpdatedAt,
		&booking.CreatedBy,
	)

	if err != nil {
		log.Printf("Failed to create booking for user %s: %v", userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Insert booking services
	serviceQuery := `
		INSERT INTO booking_services (booking_id, service_id)
		VALUES ($1, $2)
	`
	for _, serviceID := range req.ServiceIDs {
		_, err := tx.Exec(r.Context(), serviceQuery, booking.ID, serviceID)
		if err != nil {
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}
	}

	if err := tx.Commit(r.Context()); err != nil {
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Return created booking
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	if err := json.NewEncoder(w).Encode(booking); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// PUT /api/bookings/{id}
func EditBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// Get user ID from context
	userID, ok := r.Context().Value(mw.UserIDKey).(string)
	if !ok || userID == "" {
		http.Error(w, "Authentication required", http.StatusUnauthorized)
		return
	}

	// Parse and validate request
	var req EditBookingRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode request: %v", err)
		http.Error(w, "Invalid request", http.StatusBadRequest)
		return
	}

	if req.StartTime.IsZero() {
		http.Error(w, "Start time is required", http.StatusBadRequest)
		return
	}
	if req.StartTime.Before(time.Now()) {
		http.Error(w, "Start time cannot be in the past", http.StatusBadRequest)
		return
	}

	// Update booking start time (only for user's own bookings)
	query := `
		UPDATE bookings 
		SET start_time = $1, updated_at = NOW()
		WHERE id = $2 AND user_id = $3
		RETURNING id, user_id, start_time, status, notes, created_at, updated_at, created_by
	`

	var booking Booking
	err := db.DB.QueryRow(r.Context(),
		query,
		req.StartTime,
		bookingID,
		userID,
	).Scan(
		&booking.ID,
		&booking.UserID,
		&booking.StartTime,
		&booking.Status,
		&booking.Notes,
		&booking.CreatedAt,
		&booking.UpdatedAt,
		&booking.CreatedBy,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found or access denied", http.StatusNotFound)
			return
		}
		log.Printf("Failed to update booking %s for user %s: %v", bookingID, userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Return updated booking
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	if err := json.NewEncoder(w).Encode(booking); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// PUT /api/bookings/{id}/progress
func ProgressBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// Parse and validate request
	var req ProgressBookingRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode request: %v", err)
		http.Error(w, "Invalid request", http.StatusBadRequest)
		return
	}

	// Update booking status
	query := `
		UPDATE bookings 
		SET status = $1, updated_at = NOW()
		WHERE id = $2
		RETURNING id, user_id, start_time, status, notes, created_at, updated_at, created_by
	`

	var booking Booking
	err := db.DB.QueryRow(r.Context(),
		query,
		req.Status,
		bookingID,
	).Scan(
		&booking.ID,
		&booking.UserID,
		&booking.StartTime,
		&booking.Status,
		&booking.Notes,
		&booking.CreatedAt,
		&booking.UpdatedAt,
		&booking.CreatedBy,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to update booking status for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Return updated booking
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	if err := json.NewEncoder(w).Encode(booking); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// POST /api/bookings/{id}/confirm
func ConfirmBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// Parse and validate request
	var req ConfirmBookingRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode request: %v", err)
		http.Error(w, "Invalid request", http.StatusBadRequest)
		return
	}

	// Validate override values
	for _, override := range req.ServiceOverrides {
		if override.OverridePrice != nil && *override.OverridePrice < 0 {
			http.Error(w, "Override price cannot be negative", http.StatusBadRequest)
			return
		}
		if override.OverrideDurationMinutes != nil && *override.OverrideDurationMinutes <= 0 {
			http.Error(w, "Override duration must be positive", http.StatusBadRequest)
			return
		}
	}

	tx, err := db.DB.Begin(r.Context())
	if err != nil {
		log.Printf("Failed to start transaction: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer tx.Rollback(r.Context())

	// Update booking status and notes
	bookingQuery := `
		UPDATE bookings 
		SET status = 'confirmed', notes = COALESCE($1, notes), updated_at = NOW()
		WHERE id = $2 AND status = 'pending'
		RETURNING id, user_id, start_time, status, notes, created_at, updated_at, created_by
	`

	var booking Booking
	err = tx.QueryRow(r.Context(),
		bookingQuery,
		req.Notes,
		bookingID,
	).Scan(
		&booking.ID,
		&booking.UserID,
		&booking.StartTime,
		&booking.Status,
		&booking.Notes,
		&booking.CreatedAt,
		&booking.UpdatedAt,
		&booking.CreatedBy,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found or already confirmed", http.StatusNotFound)
			return
		}
		log.Printf("Failed to confirm booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Update service overrides individually
	if len(req.ServiceOverrides) > 0 {
		// First, verify all service IDs belong to this booking
		serviceCheckQuery := `
			SELECT COUNT(*) FROM booking_services 
			WHERE booking_id = $1 AND service_id = ANY($2)
		`
		serviceIDs := make([]string, len(req.ServiceOverrides))
		for i, override := range req.ServiceOverrides {
			serviceIDs[i] = override.ServiceID
		}

		var count int
		err = tx.QueryRow(r.Context(), serviceCheckQuery, bookingID, serviceIDs).Scan(&count)
		if err != nil {
			log.Printf("Failed to verify services for booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}
		if count != len(req.ServiceOverrides) {
			http.Error(w, "One or more service IDs do not belong to this booking", http.StatusBadRequest)
			return
		}

		// Update each service override
		serviceUpdateQuery := `
			UPDATE booking_services 
			SET override_price = $1,
			    override_duration_minutes = $2
			WHERE booking_id = $3 AND service_id = $4
		`

		for _, override := range req.ServiceOverrides {
			_, err := tx.Exec(r.Context(),
				serviceUpdateQuery,
				override.OverridePrice,
				override.OverrideDurationMinutes,
				bookingID,
				override.ServiceID,
			)
			if err != nil {
				log.Printf("Failed to update service override for booking %s, service %s: %v",
					bookingID, override.ServiceID, err)
				http.Error(w, "Internal server error", http.StatusInternalServerError)
				return
			}
		}
	}

	if err := tx.Commit(r.Context()); err != nil {
		log.Printf("Failed to commit booking confirmation: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	// Return confirmed booking
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	if err := json.NewEncoder(w).Encode(booking); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}

// DELETE /api/bookings/{id}
func DeleteBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	// Get user ID from context
	userID, ok := r.Context().Value(mw.UserIDKey).(string)
	if !ok || userID == "" {
		http.Error(w, "Authentication required", http.StatusUnauthorized)
		return
	}

	// Check if booking has payments
	var paymentCount int
	paymentCheckQuery := "SELECT COUNT(*) FROM payments WHERE booking_id = $1"
	err := db.DB.QueryRow(r.Context(), paymentCheckQuery, bookingID).Scan(&paymentCount)
	if err != nil {
		log.Printf("Failed to check booking %s for user %s: %v", bookingID, userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	if paymentCount > 0 {
		// Parse delete reason for bookings with payments
		var req DeleteBookingRequest
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			log.Printf("Failed to decode request: %v", err)
			http.Error(w, "Invalid request", http.StatusBadRequest)
			return
		}

		// Update booking status instead of deleting
		query := `
			UPDATE bookings 
			SET status = $1, updated_at = NOW()
			WHERE id = $2 AND user_id = $3
		`
		result, err := db.DB.Exec(r.Context(), query, req.Reason, bookingID, userID)
		if err != nil {
			log.Printf("Failed to cancel booking %s for user %s: %v", bookingID, userID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if result.RowsAffected() == 0 {
			http.Error(w, "Booking not found or access denied", http.StatusNotFound)
			return
		}

		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusOK)
		json.NewEncoder(w).Encode(map[string]interface{}{
			"message": "Booking cancelled successfully",
			"id":      bookingID,
			"status":  req.Reason,
		})
		return
	}

	// Hard delete if no payments exist
	query := "DELETE FROM bookings WHERE id = $1 AND user_id = $2"
	result, err := db.DB.Exec(r.Context(), query, bookingID, userID)
	if err != nil {
		log.Printf("Failed to delete booking %s for user %s: %v", bookingID, userID, err)
		http.Error(w, "Failed to delete booking", http.StatusInternalServerError)
		return
	}

	if result.RowsAffected() == 0 {
		http.Error(w, "Booking not found or access denied", http.StatusNotFound)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	json.NewEncoder(w).Encode(map[string]interface{}{
		"message": "Booking deleted successfully",
		"id":      bookingID,
	})
}

// GET /api/bookings/{id}
func GetBookingHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" {
		http.Error(w, "Booking ID is required", http.StatusBadRequest)
		return
	}

	userID, ok := r.Context().Value(mw.UserIDKey).(string)
	if !ok || userID == "" {
		http.Error(w, "Authentication required", http.StatusUnauthorized)
		return
	}

	// ----------------------------
	// 1. Fetch booking
	// ----------------------------
	var booking Booking
	var createdBy sql.NullString
	err := db.DB.QueryRow(r.Context(), `
		SELECT id, user_id, start_time, status, notes, created_at, updated_at, created_by
		FROM bookings
		WHERE id = $1 AND user_id = $2
	`, bookingID, userID).Scan(
		&booking.ID, &booking.UserID, &booking.StartTime, &booking.Status,
		&booking.Notes, &booking.CreatedAt, &booking.UpdatedAt, &createdBy,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Booking not found or access denied", http.StatusNotFound)
			return
		}
		log.Printf("Failed to fetch booking %s for user %s: %v", bookingID, userID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	if createdBy.Valid {
		booking.CreatedBy = &createdBy.String
	}

	// ----------------------------
	// 2. Fetch services
	// ----------------------------
	var totalAmount float64
	var durationMinutes int

	serviceRows, err := db.DB.Query(r.Context(), `
        SELECT 
            bs.service_id, bs.override_price, bs.override_duration_minutes,
            s.name, s.description, s.price, s.duration_minutes
        FROM booking_services bs
        LEFT JOIN services s ON bs.service_id = s.id
        WHERE bs.booking_id = $1
        ORDER BY s.name
    `, bookingID)
	if err != nil {
		log.Printf("Failed to fetch services for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer serviceRows.Close()

	for serviceRows.Next() {
		var s BookingService
		var overridePrice sql.NullFloat64
		var overrideDuration sql.NullInt32
		var name, description sql.NullString
		var basePrice sql.NullFloat64
		var baseDuration sql.NullInt32

		err := serviceRows.Scan(
			&s.ServiceID,
			&overridePrice, &overrideDuration,
			&name, &description, &basePrice, &baseDuration,
		)
		if err != nil {
			log.Printf("Failed to scan service row for booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if overridePrice.Valid {
			s.OverridePrice = &overridePrice.Float64
			totalAmount += *s.OverridePrice
		} else if basePrice.Valid {
			totalAmount += basePrice.Float64
		}

		if overrideDuration.Valid {
			d := int(overrideDuration.Int32)
			s.OverrideDurationMinutes = &d
			durationMinutes += *s.OverrideDurationMinutes
		} else if baseDuration.Valid {
			durationMinutes += int(baseDuration.Int32)
		}

		if name.Valid {
			s.ServiceName = &name.String
		}
		if description.Valid {
			s.ServiceDescription = &description.String
		}
		if basePrice.Valid {
			s.BasePrice = &basePrice.Float64
		}
		if baseDuration.Valid {
			d := int(baseDuration.Int32)
			s.BaseDurationMinutes = &d
		}

		booking.Services = append(booking.Services, s)
	}

	// ----------------------------
	// 3. Fetch payments
	// ----------------------------
	var amountPaid float64
	paymentRows, err := db.DB.Query(r.Context(), `
        SELECT
            id, payment_type, payment_method, vendor_code, invoice_number,
            status, amount, is_vat_applicable, vat_rate, vat_amount, net_amount,
            created_at, updated_at, created_by
        FROM payments
        WHERE booking_id = $1
        ORDER BY created_at DESC
    `, bookingID)
	if err != nil {
		log.Printf("Failed to fetch payments for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer paymentRows.Close()

	for paymentRows.Next() {
		var p Payment
		var vendorCode sql.NullString
		var invoiceNumber sql.NullInt32
		var vatRate, vatAmount, netAmount sql.NullFloat64
		var createdBy sql.NullString

		err := paymentRows.Scan(
			&p.ID, &p.PaymentType, &p.PaymentMethod, &vendorCode, &invoiceNumber,
			&p.Status, &p.Amount, &p.IsVATApplicable, &vatRate, &vatAmount, &netAmount,
			&p.CreatedAt, &p.UpdatedAt, &createdBy,
		)
		if err != nil {
			log.Printf("Failed to scan payment row for booking %s: %v", bookingID, err)
			http.Error(w, "Internal server error", http.StatusInternalServerError)
			return
		}

		if vendorCode.Valid {
			p.VendorCode = &vendorCode.String
		}
		if invoiceNumber.Valid {
			num := int(invoiceNumber.Int32)
			p.InvoiceNumber = &num
		}
		if vatRate.Valid {
			p.VATRate = &vatRate.Float64
		}
		if vatAmount.Valid {
			p.VATAmount = &vatAmount.Float64
		}
		if netAmount.Valid {
			p.NetAmount = &netAmount.Float64
		}
		if createdBy.Valid {
			p.CreatedBy = &createdBy.String
		}

		if p.Status == "completed" {
			amountPaid += p.Amount
		}

		booking.Payments = append(booking.Payments, p)
	}

	amountDue := totalAmount - amountPaid

	// Create enhanced response with user-friendly totals
	enhancedResponse := struct {
		Booking         Booking `json:"booking"`
		TotalAmount     float64 `json:"total_amount"`
		AmountPaid      float64 `json:"amount_paid"`
		AmountDue       float64 `json:"amount_due"`
		DurationMinutes int     `json:"duration_minutes"`
	}{
		Booking:         booking,
		TotalAmount:     totalAmount,
		AmountPaid:      amountPaid,
		AmountDue:       amountDue,
		DurationMinutes: durationMinutes,
	}

	w.Header().Set("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(enhancedResponse); err != nil {
		log.Printf("Failed to encode booking response: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
}
