package payments

import (
	"context"
	"crussell/clock"
	"crussell/db"
	"crussell/internal/square"
	"crussell/internal/validators"
	"crussell/mw"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"log/slog"
	"math"
	"net/http"
	"strconv"
	"strings"
	"time"

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

type CreateTerminalPaymentRequest struct {
	Amount         int64   `json:"amount" validate:"required,gt=0"`
	PaymentType    string  `json:"payment_type" validate:"required"`
	OverrideAmount *int64  `json:"override_amount,omitempty"`
	TipEnabled     bool    `json:"tip_enabled"`
	PaymentMethod  *string `json:"payment_method,omitempty"`
	GiftCardID     *string `json:"gift_card_id,omitempty"`
}

type CreateBookingPaymentRequest struct {
	Amount         int64   `json:"amount" validate:"required,gt=0"`
	PaymentType    string  `json:"payment_type" validate:"required"`
	CardID         *string `json:"card_id,omitempty"`
	NewCardToken   *string `json:"new_card_token,omitempty"`
	SaveCard       bool    `json:"save_card"`
	IdempotencyKey string  `json:"idempotency_key" validate:"required"`
}

type RefundRequest struct {
	Amount int64  `json:"amount"`
	Reason string `json:"reason"`
}

type CreateTipPaymentRequest struct {
	Amount    int64  `json:"amount" validate:"required,gt=0"`
	CardToken string `json:"card_token" validate:"required"`
}

type CheckoutResponse struct {
	CheckoutID string `json:"checkout_id"`
	Status     string `json:"status"`
}

type PaymentStatusResponse struct {
	Status     string `json:"status"`
	PaymentID  string `json:"payment_id,omitempty"`
	Amount     int64  `json:"amount,omitempty"`
	CardBrand  string `json:"card_brand,omitempty"`
	CardLast4  string `json:"card_last4,omitempty"`
	ReceiptURL string `json:"receipt_url,omitempty"`
}

type PaymentResponse struct {
	ID          string `json:"id"`
	BookingID   string `json:"booking_id"`
	PaymentType string `json:"payment_type"`
	Status      string `json:"status"`
	Amount      int64  `json:"amount"`
	CardBrand   string `json:"card_brand,omitempty"`
	CardLast4   string `json:"card_last4,omitempty"`
	ReceiptURL  string `json:"receipt_url,omitempty"`
	CreatedAt   string `json:"created_at"`
}

type RefundResponse struct {
	ID        string `json:"id"`
	PaymentID string `json:"payment_id"`
	Amount    int64  `json:"amount"`
	Status    string `json:"status"`
	Reason    string `json:"reason"`
	CreatedAt string `json:"created_at"`
}

type PaymentSummaryResponse struct {
	TotalAmount     int64             `json:"total_amount"`
	PaidAmount      int64             `json:"paid_amount"`
	RefundedAmount  int64             `json:"refunded_amount"`
	RemainingAmount int64             `json:"remaining_amount"`
	TotalVATAmount  int64             `json:"total_vat_amount"`
	TotalNetAmount  int64             `json:"total_net_amount"`
	Payments        []PaymentResponse `json:"payments"`
	Refunds         []RefundResponse  `json:"refunds"`
}

// DiscountPreviewResponse describes eligible discounts for a booking.
type DiscountPreviewResponse struct {
	Eligible        bool              `json:"eligible"`
	Discounts       []DiscountPreview `json:"discounts"`
	OriginalTotal   float64           `json:"original_total"`
	DiscountedTotal float64           `json:"discounted_total"`
}

// DiscountPreview describes a single eligible discount.
type DiscountPreview struct {
	Source  string  `json:"source"`
	Name    string  `json:"name"`
	Percent float64 `json:"percent"`
	Amount  float64 `json:"amount"`
}

// GetDiscountPreviewHandler returns eligible discounts for a booking without applying them.
// GET /api/bookings/{id}/discount-preview
func GetDiscountPreviewHandler(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if !validators.IsValidID(bookingID) {
		http.Error(w, "Invalid booking ID", http.StatusBadRequest)
		return
	}

	userID, ok := mw.GetUserID(r.Context())
	if !ok {
		http.Error(w, "unauthorized", http.StatusUnauthorized)
		return
	}

	preview := calculateDiscountPreview(r.Context(), bookingID, userID)

	_ = json.NewEncoder(w).Encode(preview)
}

// calculateDiscountPreview runs the same queries as applyEligibleCampaignsAtPayment
// but returns the results without inserting any records.
func calculateDiscountPreview(ctx context.Context, bookingID string, userID string) DiscountPreviewResponse {
	resp := DiscountPreviewResponse{
		Discounts: []DiscountPreview{},
	}

	var bookingTotal float64
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = db.Conn.QueryRow(ctx, `
		SELECT total_amount FROM bookings WHERE id = $1
	`, bookingID).Scan(&bookingTotal)

	if bookingTotal <= 0 {
		return resp
	}

	resp.OriginalTotal = bookingTotal
	discountTotal := 0.0

	var campaignID string
	var campaignPercent float64
	var campaignName string
	if err := db.Conn.QueryRow(ctx, `
		SELECT id, discount_percent, name FROM discount_campaigns
		WHERE status = 'active' AND campaign_type = 'time_based'
		AND start_date <= NOW() AND end_date >= NOW()
		AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
		ORDER BY discount_percent DESC LIMIT 1
	`).Scan(&campaignID, &campaignPercent, &campaignName); err == nil && campaignID != "" {
		var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = db.Conn.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, campaignID).Scan(&exists)
		if exists == 0 {
			amount := roundTo2(bookingTotal * campaignPercent / 100)
			resp.Discounts = append(resp.Discounts, DiscountPreview{
				Source:  "campaign",
				Name:    campaignName,
				Percent: campaignPercent,
				Amount:  amount,
			})
			discountTotal += amount
		}
	}

	var userBookingCount int
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = db.Conn.QueryRow(ctx, `SELECT COUNT(*) FROM bookings WHERE user_id = $1 AND status = 'completed'`, userID).Scan(&userBookingCount)

	var milestoneCampaignID string
	var milestonePercent float64
	var milestoneName string
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = db.Conn.QueryRow(ctx, `
		SELECT id, discount_percent, name FROM discount_campaigns
		WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'per_user_booking_count'
		AND milestone_value = $1
		AND NOT EXISTS (SELECT 1 FROM booking_discounts WHERE user_id = $2 AND source_id = discount_campaigns.id)
	`, userBookingCount, userID).Scan(&milestoneCampaignID, &milestonePercent, &milestoneName)

	if milestoneCampaignID != "" {
		var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = db.Conn.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, milestoneCampaignID).Scan(&exists)
		if exists == 0 {
			amount := roundTo2(bookingTotal * milestonePercent / 100)
			resp.Discounts = append(resp.Discounts, DiscountPreview{
				Source:  "campaign",
				Name:    milestoneName,
				Percent: milestonePercent,
				Amount:  amount,
			})
			discountTotal += amount
		}
	}

	var firstVisitDate time.Time
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = db.Conn.QueryRow(ctx, `SELECT MIN(start_time) FROM bookings WHERE user_id = $1 AND status = 'completed'`, userID).Scan(&firstVisitDate)
	if !firstVisitDate.IsZero() {
		type annCamp struct {
			id    string
			pct   float64
			value int
			unit  string
			name  string
		}
		annRows, err := db.Conn.Query(ctx, `
			SELECT id, discount_percent, milestone_value, milestone_unit, name FROM discount_campaigns
			WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'anniversary'
			AND NOT EXISTS (SELECT 1 FROM booking_discounts WHERE user_id = $1 AND source_id = discount_campaigns.id AND milestone_type = 'anniversary')
		`, userID)
		if err == nil {
			var campaigns []annCamp
			for annRows.Next() {
				var c annCamp
				if annRows.Scan(&c.id, &c.pct, &c.value, &c.unit, &c.name) == nil {
					campaigns = append(campaigns, c)
				}
			}
			annRows.Close()

			for _, c := range campaigns {
				var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
				_ = db.Conn.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, c.id).Scan(&exists)
				if exists > 0 {
					continue
				}

				var matches bool
				elapsed := time.Since(firstVisitDate)
				switch c.unit {
				case "months":
					matches = int(elapsed.Hours()/(30*24)) >= c.value
				case "years":
					matches = int(elapsed.Hours()/(365.25*24)) >= c.value
				}
				if matches {
					amount := roundTo2(bookingTotal * c.pct / 100)
					resp.Discounts = append(resp.Discounts, DiscountPreview{
						Source:  "campaign",
						Name:    c.name,
						Percent: c.pct,
						Amount:  amount,
					})
					discountTotal += amount
				}
			}
		}
	}

	// Check for referrer's unused referral discount
	var rdID string
	var rdPercent float64
	if err := db.Conn.QueryRow(ctx, `
		SELECT id, discount_percent FROM referral_discounts
		WHERE user_id = $1 AND used = FALSE
		LIMIT 1
	`, userID).Scan(&rdID, &rdPercent); err == nil && rdID != "" {
		exists := 0
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = db.Conn.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND discount_source = 'referral' AND source_id = $2`, bookingID, rdID).Scan(&exists)
		if exists == 0 {
			amount := roundTo2(bookingTotal * rdPercent / 100)
			resp.Discounts = append(resp.Discounts, DiscountPreview{
				Source:  "referral",
				Name:    "Referral Discount (10%)",
				Percent: rdPercent,
				Amount:  amount,
			})
			discountTotal += amount
		}
	}

	resp.Eligible = len(resp.Discounts) > 0
	resp.DiscountedTotal = roundTo2(bookingTotal - discountTotal)
	return resp
}

func CreateTerminalPayment(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

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

	var req CreateTerminalPaymentRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode terminal payment request: %v", err)
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	if err := validators.Validate.Struct(&req); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	// M8
	// L5

	if err := ValidateAmount(req.Amount); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := ValidatePaymentType(req.PaymentType); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	service := NewPaymentService()

	amount := req.Amount
	if req.OverrideAmount != nil {
		amount = *req.OverrideAmount
	}

	idempotencyKey := bookingID + "-" + req.PaymentType + "-" + strconv.FormatInt(amount, 10)

	// Route based on payment method
	if req.PaymentMethod != nil && (*req.PaymentMethod == "cash" || *req.PaymentMethod == "giftcard") {
		// Start transaction before the status and idempotency checks so they
		// are atomic with the payment insert.
		tx, err := db.Conn.Begin(r.Context())
		if err != nil {
			log.Printf("Failed to begin transaction: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

		// Check booking status inside the transaction.
		var status string
		if err := tx.QueryRow(r.Context(), `SELECT status FROM bookings WHERE id = $1`, bookingID).Scan(&status); err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				http.Error(w, "Booking not found", http.StatusNotFound)
				return
			}
			log.Printf("Failed to get booking status: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		if status != "in_progress" && status != "completed" {
			http.Error(w, "Booking must be in_progress or completed to create payment", http.StatusBadRequest)
			return
		}

		// Check idempotency inside the transaction.
		var existingID string
		var existingStatus string
		if err := tx.QueryRow(r.Context(), `
			SELECT id, status FROM payments WHERE booking_id = $1 AND idempotency_key = $2
		`, bookingID, idempotencyKey).Scan(&existingID, &existingStatus); err == nil {
			_ = json.NewEncoder(w).Encode(CheckoutResponse{
				CheckoutID: existingID,
				Status:     existingStatus,
			})
			return
		} else if !errors.Is(err, pgx.ErrNoRows) {
			log.Printf("Failed to check idempotency: %v", err)
		}

		amountPounds := float64(amount) / 100.0
		var paymentID string

		if *req.PaymentMethod == "cash" {
			err = tx.QueryRow(r.Context(), `
				INSERT INTO payments (
					booking_id, payment_type, payment_method, status, amount, created_by, created_at, updated_at
				) VALUES ($1, $2, 'cash', 'completed', $3, $4, NOW(), NOW())
				RETURNING id
			`, bookingID, req.PaymentType, amountPounds, adminID).Scan(&paymentID)
			if err != nil {
				log.Printf("Failed to create cash payment record: %v", err)
				http.Error(w, "internal server error", http.StatusInternalServerError)
				return
			}
			ApplyVATToBookingPayment(r.Context(), tx, paymentID)
		} else { // giftcard
			var customerID sql.NullString
			err = tx.QueryRow(r.Context(), "SELECT user_id FROM bookings WHERE id = $1", bookingID).Scan(&customerID)
			if err != nil {
				log.Printf("Failed to query booking user: %v", err)
				http.Error(w, "internal server error", http.StatusInternalServerError)
				return
			}

			usedBalance := false
			if customerID.Valid {
				var balance float64
				err = tx.QueryRow(r.Context(), "SELECT balance FROM user_giftcard_balances WHERE user_id = $1 FOR UPDATE", customerID.String).Scan(&balance)
				if err == nil {
					if balance < amountPounds {
						http.Error(w, "Insufficient gift card balance on user account", http.StatusBadRequest)
						return
					}
					// Deduct from account balance
					_, err = tx.Exec(r.Context(), "UPDATE user_giftcard_balances SET balance = balance - $1, updated_at = NOW() WHERE user_id = $2", amountPounds, customerID.String)
					if err != nil {
						log.Printf("Failed to deduct user balance: %v", err)
						http.Error(w, "internal server error", http.StatusInternalServerError)
						return
					}
					usedBalance = true
				} else if !errors.Is(err, pgx.ErrNoRows) {
					log.Printf("Failed to query user balance: %v", err)
					http.Error(w, "internal server error", http.StatusInternalServerError)
					return
				}
			}

			var cardVoucherType string // voucher_type_at_purchase from the gift card
			if !usedBalance {
				// Try direct card redemption (for guests or users without a redeemed balance)
				if req.GiftCardID == nil || *req.GiftCardID == "" {
					http.Error(w, "Gift card ID is required", http.StatusBadRequest)
					return
				}
				cleanCardID := validators.NormalizeGiftCardCode(*req.GiftCardID)

				var gcRemaining float64
				var redeemedBy sql.NullString
				var vtp sql.NullString
				err = tx.QueryRow(r.Context(), "SELECT amount_remaining, redeemed_by, voucher_type_at_purchase FROM gift_cards WHERE id = $1 FOR UPDATE", cleanCardID).Scan(&gcRemaining, &redeemedBy, &vtp)
				if err != nil {
					if errors.Is(err, pgx.ErrNoRows) {
						http.Error(w, "Gift card not found", http.StatusNotFound)
						return
					}
					log.Printf("Failed to query gift card: %v", err)
					http.Error(w, "internal server error", http.StatusInternalServerError)
					return
				}

				if redeemedBy.Valid {
					http.Error(w, "This gift card has already been redeemed to an account. Please pay using the account balance.", http.StatusBadRequest)
					return
				}

				if gcRemaining < amountPounds {
					http.Error(w, "Insufficient balance on gift card", http.StatusBadRequest)
					return
				}

				// Record the voucher_type_at_purchase for later VAT decision.
				// Legacy cards (created before this column existed) have NULL → default to SPV.
				if vtp.Valid {
					cardVoucherType = vtp.String
				} else {
					cardVoucherType = "SPV"
				}

				// Deduct directly from card remaining amount
				_, err = tx.Exec(r.Context(), "UPDATE gift_cards SET amount_remaining = amount_remaining - $1, last_used_at = NOW() WHERE id = $2", amountPounds, cleanCardID)
				if err != nil {
					log.Printf("Failed to deduct gift card amount: %v", err)
					http.Error(w, "internal server error", http.StatusInternalServerError)
					return
				}
			}

			err = tx.QueryRow(r.Context(), `
				INSERT INTO payments (
					booking_id, payment_type, payment_method, status, amount, created_by, created_at, updated_at
				) VALUES ($1, $2, 'giftcard', 'completed', $3, $4, NOW(), NOW())
				RETURNING id
			`, bookingID, req.PaymentType, amountPounds, adminID).Scan(&paymentID)
			if err != nil {
				log.Printf("Failed to create giftcard payment record: %v", err)
				http.Error(w, "internal server error", http.StatusInternalServerError)
				return
			}

			// Apply VAT at redemption only if the gift card was purchased as MPV
			// (VAT deferred to redemption). For SPV, VAT was already paid at sale.
			// For account balance payments (usedBalance=true), VAT was already paid
			// when the original card was purchased.
			if usedBalance {
				// VAT already paid at purchase time — nothing to do here.
			} else if cardVoucherType == "MPV" {
				vatCfg, vatErr := GetVATConfig(r.Context(), tx)
				if vatErr == nil && vatCfg.IsVATRegistered {
					if _, vatExecErr := tx.Exec(r.Context(), "SELECT apply_vat_to_payment($1, $2)", paymentID, vatCfg.DefaultVATRate); vatExecErr != nil {
						log.Printf("Failed to apply VAT to giftcard payment %s: %v", paymentID, vatExecErr)
					}
				}
			}
		}

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

		_ = json.NewEncoder(w).Encode(CheckoutResponse{
			CheckoutID: paymentID,
			Status:     "COMPLETED",
		})
		return
	}

	// For Square checkout (terminal card reader), validate booking status
	// and check idempotency. No DB transaction needed since Square handles
	// the payment — no DB writes occur until GetCheckoutStatus.
	status, err := service.GetBookingStatus(r.Context(), bookingID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to get booking status: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}
	if status != "in_progress" && status != "completed" {
		http.Error(w, "Booking must be in_progress or completed to create payment", http.StatusBadRequest)
		return
	}

	existingPayment, err := service.CheckIdempotency(r.Context(), bookingID, idempotencyKey)
	if err != nil {
		log.Printf("Failed to check idempotency: %v", err)
	}
	if existingPayment != nil {
		_ = json.NewEncoder(w).Encode(CheckoutResponse{
			CheckoutID: existingPayment.ID,
			Status:     existingPayment.Status,
		})
		return
	}

	checkoutReq := square.CreateCheckoutReq{
		Amount:         amount,
		Currency:       "GBP",
		IdempotencyKey: idempotencyKey,
		ReferenceID:    bookingID,
		TipEnabled:     req.TipEnabled,
	}

	checkout, err := SquareClient.CreateCheckout(r.Context(), checkoutReq)
	if err != nil {
		log.Printf("Failed to create checkout: %v", err)
		http.Error(w, "Failed to create payment", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(CheckoutResponse{
		CheckoutID: checkout.ID,
		Status:     checkout.Status,
	})
}

func GetCheckoutStatus(w http.ResponseWriter, r *http.Request) {
	checkoutID := chi.URLParam(r, "checkout_id")
	if checkoutID == "" {
		http.Error(w, "Checkout ID is required", http.StatusBadRequest)
		return
	}
	if !validators.IsValidID(checkoutID) {
		http.Error(w, "not found", http.StatusNotFound)
		return
	}

	bookingID := r.URL.Query().Get("booking_id")
	if bookingID == "" {
		http.Error(w, "booking_id query parameter is required", http.StatusBadRequest)
		return
	}
	if !validators.IsValidID(bookingID) {
		http.Error(w, "Invalid booking ID", http.StatusBadRequest)
		return
	}

	paymentResult, err := SquareClient.GetCheckout(r.Context(), checkoutID)
	if err != nil {
		if err.Error() == "checkout pending" {
			_ = json.NewEncoder(w).Encode(PaymentStatusResponse{Status: "PENDING"})
			return
		}
		log.Printf("Failed to get checkout status: %v", err)
		http.Error(w, "Failed to get checkout status", http.StatusInternalServerError)
		return
	}

	if paymentResult.Status == "COMPLETED" {
		service := NewPaymentService()
		idempotencyKey := bookingID + "-terminal-" + strconv.FormatInt(paymentResult.Amount, 10)

		// Begin the transaction BEFORE the idempotency check so it's atomic
		// with the payment insert.
		tx, err := db.Conn.Begin(r.Context())
		if err != nil {
			log.Printf("Failed to begin transaction: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

		// Check for existing payment inside the transaction.
		var existingID string
		var existingSquarePayID sql.NullString
		if err := tx.QueryRow(r.Context(), `
			SELECT id, COALESCE(square_payment_id, '') FROM payments
			WHERE booking_id = $1 AND idempotency_key = $2
		`, bookingID, idempotencyKey).Scan(&existingID, &existingSquarePayID); err == nil {
			if existingSquarePayID.Valid && existingSquarePayID.String == paymentResult.SquarePayID {
			_ = json.NewEncoder(w).Encode(PaymentStatusResponse{
				Status:     "COMPLETED",
				PaymentID:  existingID,
				Amount:     paymentResult.Amount,
					CardBrand:  paymentResult.CardBrand,
					CardLast4:  paymentResult.CardLast4,
					ReceiptURL: paymentResult.ReceiptURL,
				})
				return
			}
		} else if !errors.Is(err, pgx.ErrNoRows) {
			log.Printf("Failed to check for existing payment: %v", err)
		}

		record := PaymentRecord{
			BookingID:       bookingID,
			PaymentType:     "full",
			PaymentMethod:   "in_person_card",
			Status:          "completed",
			Amount:          float64(paymentResult.Amount) / 100.0,
			SquarePaymentID: &paymentResult.SquarePayID,
			IdempotencyKey:  &idempotencyKey,
			Fees:            float64(paymentResult.Fees) / 100.0,
			CreatedAt:       clock.Now(),
			UpdatedAt:       clock.Now(),
		}

		paymentID, err := service.CreatePaymentRecordTx(r.Context(), tx, record, nil)
		if err != nil {
			log.Printf("Failed to create payment record: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		ApplyVATToBookingPayment(r.Context(), tx, paymentID)

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

		_ = json.NewEncoder(w).Encode(PaymentStatusResponse{
			Status:     "COMPLETED",
			PaymentID:  paymentID,
			Amount:     paymentResult.Amount,
			CardBrand:  paymentResult.CardBrand,
			CardLast4:  paymentResult.CardLast4,
			ReceiptURL: paymentResult.ReceiptURL,
		})
		return
	}

	http.Error(w, "Payment failed", http.StatusPaymentRequired)
}

// IsValidBookingStatusForPayment returns true if the booking status allows
// accepting payments. This guard prevents racing with CleanupExpiredDeposits —
// once a booking's slot has been released (deposit_lapsed, etc.),
// we must reject the payment before hitting Square's API.
func IsValidBookingStatusForPayment(status string) bool {
	switch status {
	case "confirmed", "pending", "pending_release", "in_progress":
		return true
	default:
		return false
	}
}

func CreateBookingPayment(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

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

	var req CreateBookingPaymentRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode booking payment request: %v", err)
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	if err := validators.Validate.Struct(&req); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	// M8
	// L5

	if err := ValidateAmount(req.Amount); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := ValidatePaymentType(req.PaymentType); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := ValidateCardInfo(req.CardID, req.NewCardToken); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	service := NewPaymentService()

	if req.PaymentType == "partial" {
		remainingCents, err := service.GetBookingRemainingBalanceCents(r.Context(), bookingID)
		if err != nil {
			log.Printf("Failed to get remaining balance: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		if err := ValidatePartialAmount(req.Amount, remainingCents); err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	}

	bookingUserID, err := service.GetBookingUserID(r.Context(), bookingID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to get booking user: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if bookingUserID != userID {
		http.Error(w, "Unauthorized", http.StatusForbidden)
		return
	}

	// Serialize payment attempts for this booking to prevent concurrent payments
	// across browser tabs or duplicate requests. Uses a PostgreSQL session-level
	// advisory lock so that only one goroutine processes payment for a given
	// booking at a time, even if two requests pass the optimistic status check below.
	//
	// We acquire a dedicated connection from the pool and hold it for the
	// duration of the handler so that lock and unlock use the same connection.
	// Using db.Conn.Exec() for both would be unsafe — each call may get a
	// different pool connection, and pg_advisory_unlock on a different session
	// is a silent no-op, leaking the lock.
	pinConn, err := db.Conn.Acquire(r.Context())
	if err != nil {
		log.Printf("Failed to acquire connection for payment lock: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer pinConn.Release()

	if _, err := pinConn.Exec(r.Context(), `
		SELECT pg_advisory_lock(hashtext('crussell:payment:' || $1))
	`, bookingID); err != nil {
		log.Printf("Failed to acquire payment serialization lock for %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer func() {
		if _, err := pinConn.Exec(context.Background(), `
			SELECT pg_advisory_unlock(hashtext('crussell:payment:' || $1))
		`, bookingID); err != nil {
			log.Printf("Failed to release payment serialization lock for %s: %v", bookingID, err)
		}
	}()

	// Now that we hold the serialization lock, begin a transaction and re-check
	// the booking status inside it. If another request (e.g. from a different tab)
	// already processed a payment and promoted the booking while we were waiting,
	// we see that here.
	tx, txErr := db.Conn.Begin(r.Context())
	if txErr != nil {
		log.Printf("Failed to begin transaction: %v", txErr)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}
	defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

	var status string
	if err := tx.QueryRow(r.Context(), `SELECT status FROM bookings WHERE id = $1`, bookingID).Scan(&status); err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to get booking status for payment check: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}
	if !IsValidBookingStatusForPayment(status) {
		log.Printf("Payment rejected: booking %s is in status %q (no longer accepting payments)", bookingID, status)
		http.Error(w, "This booking is no longer accepting payments. The slot may have been released.", http.StatusConflict)
		return
	}
	if status == "pending" {
		log.Printf("Payment rejected: booking %s is 'pending' — must be confirmed first", bookingID)
		http.Error(w, "This booking has not been confirmed yet. Please wait for the booking to be confirmed before making a payment.", http.StatusConflict)
		return
	}

	// Check idempotency inside the transaction.
	var existingID sql.NullString
	var existingBookingID sql.NullString
	var existingPaymentType sql.NullString
	var existingStatus sql.NullString
	var existingAmount sql.NullFloat64
	var existingCreatedAt sql.NullTime
	if err := tx.QueryRow(r.Context(), `
		SELECT id, booking_id, payment_type, status, amount, created_at
		FROM payments
		WHERE booking_id = $1 AND idempotency_key = $2
	`, bookingID, req.IdempotencyKey).Scan(&existingID, &existingBookingID, &existingPaymentType, &existingStatus, &existingAmount, &existingCreatedAt); err == nil {
		_ = json.NewEncoder(w).Encode(PaymentResponse{
			ID:          existingID.String,
			BookingID:   existingBookingID.String,
			PaymentType: existingPaymentType.String,
			Status:      existingStatus.String,
			Amount:      int64(existingAmount.Float64 * 100),
			CreatedAt:   existingCreatedAt.Time.Format(time.RFC3339),
		})
		return
	} else if !errors.Is(err, pgx.ErrNoRows) {
		log.Printf("Failed to check idempotency: %v", err)
	}

	// After the idempotency check (which handles same-key retries), verify
	// that no completed payment of the same non-partial type already exists.
	// buildSplitRecords converts 'full' and 'deposit' input types into a
	// 'deposit' PB record, so we also check for an existing deposit when the
	// incoming type is 'full' or 'deposit'. Together with the advisory lock,
	// this prevents the two-tab race where different idempotency keys allow
	// concurrent payments of the same type.
	if req.PaymentType != "partial" {
		var existingCount int
		if err := tx.QueryRow(r.Context(), `
			SELECT COUNT(*) FROM payments
			WHERE booking_id = $1
			  AND status = 'completed'
			  AND payment_method NOT IN ('discount', 'on_the_house')
			  AND (
				  payment_type = $2
				  OR ($2 IN ('full', 'deposit') AND payment_type = 'deposit')
			  )
		`, bookingID, req.PaymentType).Scan(&existingCount); err == nil && existingCount > 0 {
			log.Printf("Payment rejected: booking %s already has a completed %q payment", bookingID, req.PaymentType)
			http.Error(w, "A payment of this type has already been processed for this booking", http.StatusConflict)
			return
		}
	}

	var sourceID string
	var savedCardID *string

	if req.NewCardToken != nil && *req.NewCardToken != "" {
		cardOnFile, err := SquareClient.CreateCardOnFile(r.Context(), userID, *req.NewCardToken)
		if err != nil {
			log.Printf("Failed to create card on file: %v", err)
			http.Error(w, "Failed to process card", http.StatusInternalServerError)
			return
		}

		sourceID = cardOnFile.CardID

		if req.SaveCard {
			cardID, err := service.SaveCardForUser(r.Context(), userID, cardOnFile.CardID, cardOnFile.Brand, cardOnFile.Last4, cardOnFile.ExpMonth, cardOnFile.ExpYear, cardOnFile.Fingerprint)
			if err != nil {
				log.Printf("Failed to save card: %v", err)
			} else {
				savedCardID = &cardID
			}
		}
		if savedCardID == nil && req.SaveCard {
			log.Printf("Card was not saved despite save_card=true for user %s", userID)
		}
	} else if req.CardID != nil {
		card, err := service.GetCardByID(r.Context(), *req.CardID, userID)
		if err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				http.Error(w, "Card not found", http.StatusNotFound)
				return
			}
			log.Printf("Failed to get card: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		sourceID = card.SquareCardID
		savedCardID = req.CardID
	}

	paymentReq := square.CreatePaymentReq{
		Amount:         req.Amount,
		Currency:       "GBP",
		SourceID:       sourceID,
		IdempotencyKey: req.IdempotencyKey,
		ReferenceID:    bookingID,
		Note:           req.PaymentType,
	}

	paymentResult, err := SquareClient.CreatePayment(r.Context(), paymentReq)
	if err != nil {
		log.Printf("Failed to create payment: %v", err)
		http.Error(w, "Payment failed", http.StatusPaymentRequired)
		return
	}

	fees := service.CalculateFees(req.Amount, "online")

	paymentAmount := float64(req.Amount) / 100.0

	// Build payment records — may split a single Square charge into
	// a deposit portion (up to 50% of booking total) plus a balance
	// portion, so the refund system can correctly track deposit vs
	// non-deposit money per the deposit protection policy.
	bookingInfo, bErr := service.GetBookingPaymentInfo(r.Context(), bookingID)
	primaryRecord := PaymentRecord{
		BookingID:       bookingID,
		PaymentType:     req.PaymentType,
		PaymentMethod:   "online_square",
		Status:          "completed",
		Amount:          paymentAmount,
		SquarePaymentID: &paymentResult.SquarePayID,
		IdempotencyKey:  &req.IdempotencyKey,
		Fees:            float64(fees) / 100.0,
		UserSavedCardID: savedCardID,
		CreatedAt:       clock.Now(),
		UpdatedAt:       clock.Now(),
		CreatedBy:       &userID,
	}

	var records []PaymentRecord
	if bErr == nil && bookingInfo != nil {
		records = buildSplitRecords(primaryRecord, req.PaymentType, bookingInfo, paymentAmount)
	} else {
		if bErr != nil {
			log.Printf("Failed to get booking info for split: %v — using single record", bErr)
		}
		records = []PaymentRecord{primaryRecord}
	}

	// Create all payment records for this Square charge inside the transaction
	// so that if any insert fails the entire group rolls back. This prevents
	// a data inconsistency where Square charged the customer but only part of
	// the split is reflected in the DB.

	var primaryPaymentID string
	var paymentIDs []string
	for i, rec := range records {
		pid, cErr := service.CreatePaymentRecordTx(r.Context(), tx, rec, nil)
		if cErr != nil {
			log.Printf("Failed to create payment record %d/%d: %v", i+1, len(records), cErr)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		paymentIDs = append(paymentIDs, pid)
		if i == 0 {
			primaryPaymentID = pid
		}
	}

	// Apply VAT to all split records if the business is VAT-registered.
	// Must be inside the transaction so VAT updates are atomic with inserts.
	vatCfg, vatErr := GetVATConfig(r.Context(), tx)
	if vatErr == nil && vatCfg.IsVATRegistered {
		for _, pid := range paymentIDs {
			if _, execErr := tx.Exec(r.Context(), "SELECT apply_vat_to_payment($1, $2)", pid, vatCfg.DefaultVATRate); execErr != nil {
				log.Printf("Failed to apply VAT to payment %s: %v", pid, execErr)
			}
		}
	}

	// Promote deposit to confirmed if total paid meets the 20% threshold.
	// Check is inside the transaction so it sees the just-inserted payments.
	var depositMet bool
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = tx.QueryRow(r.Context(), `
		WITH booking_total AS (
			SELECT total_amount * 100 AS total_cents FROM bookings WHERE id = $1
		),
		paid_total AS (
			SELECT COALESCE(SUM(amount), 0) * 100 AS paid_cents
			FROM payments
			WHERE booking_id = $1 AND status = 'completed'
		)
		SELECT pt.paid_cents >= ROUND(bt.total_cents * 0.2)
		FROM booking_total bt, paid_total pt
	`, bookingID).Scan(&depositMet)

	if depositMet {
		if _, err := tx.Exec(r.Context(), `
			UPDATE bookings SET status = 'confirmed', updated_at = NOW()
			WHERE id = $1 AND status = 'pending_release'
		`, bookingID); err != nil {
			log.Printf("ALERT: payment recorded but failed to promote booking %s from pending_release: %v", bookingID, err)
		}
	}

	// Apply eligible campaign discounts inside the payment transaction, so
	// atomicity with the payment inserts is guaranteed. The call is idempotent
	// — if discounts were already applied, the duplicate check skips them.
	applyEligibleCampaignsAtPayment(r.Context(), tx, bookingID, userID)

	if cErr := tx.Commit(r.Context()); cErr != nil {
		log.Printf("Failed to commit payment records: %v", cErr)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(PaymentResponse{
		ID:          primaryPaymentID,
		BookingID:   bookingID,
		PaymentType: req.PaymentType,
		Status:      "completed",
		Amount:      req.Amount,
		CardBrand:   paymentResult.CardBrand,
		CardLast4:   paymentResult.CardLast4,
		ReceiptURL:  paymentResult.ReceiptURL,
		CreatedAt:   clock.Now().Format(time.RFC3339),
	})
}

// applyEligibleCampaignsAtPayment checks and applies any eligible discount
// campaigns to the booking. Uses the provided transaction so that discount
// writes are atomic with the caller's payment transaction — if the payment
// commit fails, the discount writes roll back with it.
// Skips if the booking already has 2+ completed non-discount payments — this
// prevents applying new discounts after a customer has already paid, which
// would create a credit balance or require a refund.
func applyEligibleCampaignsAtPayment(ctx context.Context, q db.Querier, bookingID string, userID string) {
	var existingPayment int
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = q.QueryRow(ctx, `
		SELECT COUNT(*) FROM payments
		WHERE booking_id = $1 AND status = 'completed' AND payment_method NOT IN ('discount', 'on_the_house')
	`, bookingID).Scan(&existingPayment)
	// Only block if this is the 2nd+ real payment — the first payment should still
	// trigger discount application (existingPayment counts already-completed payments
	// visible within the transaction, including the just-inserted one).
	if existingPayment >= 2 {
		return
	}

	var bookingTotal float64
	if err := q.QueryRow(ctx, `
		SELECT total_amount FROM bookings WHERE id = $1
	`, bookingID).Scan(&bookingTotal); err != nil {
		log.Printf("Failed to calculate booking total for campaign check: %v", err)
		return
	}
	if bookingTotal <= 0 {
		return
	}

	var campaignID string
	var campaignPercent float64
	if err := q.QueryRow(ctx, `
		SELECT id, discount_percent FROM discount_campaigns
		WHERE status = 'active' AND campaign_type = 'time_based'
		AND start_date <= NOW() AND end_date >= NOW()
		AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
		ORDER BY discount_percent DESC LIMIT 1
	`).Scan(&campaignID, &campaignPercent); err == nil && campaignID != "" {
		var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = q.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, campaignID).Scan(&exists)
		if exists == 0 {
			discountAmount := roundTo2(bookingTotal * campaignPercent / 100)
			if _, err := q.Exec(ctx, `
				INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
				VALUES ($1, $2, 'campaign', $3, 'time_based', NULL, $4, $5, $6)
			`, bookingID, userID, campaignID, campaignPercent, bookingTotal, discountAmount); err != nil {
				log.Printf("Failed to insert time-based campaign discount: %v", err)
			} else {
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
					VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
				`, bookingID, discountAmount, userID)
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
				`, campaignID)
			}
		}
	}

	var userBookingCount int
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = q.QueryRow(ctx, `SELECT COUNT(*) FROM bookings WHERE user_id = $1 AND status = 'completed'`, userID).Scan(&userBookingCount)

	var milestoneCampaignID string
	var milestonePercent float64
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = q.QueryRow(ctx, `
		SELECT id, discount_percent FROM discount_campaigns
		WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'per_user_booking_count'
		AND milestone_value = $1
		AND NOT EXISTS (SELECT 1 FROM booking_discounts WHERE user_id = $2 AND source_id = discount_campaigns.id)
	`, userBookingCount, userID).Scan(&milestoneCampaignID, &milestonePercent)

	if milestoneCampaignID != "" {
		var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = q.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, milestoneCampaignID).Scan(&exists)
		if exists == 0 {
			discountAmount := roundTo2(bookingTotal * milestonePercent / 100)
			if _, err := q.Exec(ctx, `
				INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
				VALUES ($1, $2, 'campaign', $3, 'milestone', 'per_user_booking_count', $4, $5, $6)
			`, bookingID, userID, milestoneCampaignID, milestonePercent, bookingTotal, discountAmount); err != nil {
				log.Printf("Failed to insert per-user milestone discount: %v", err)
			} else {
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
					VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
				`, bookingID, discountAmount, userID)
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
				`, milestoneCampaignID)
			}
		}
	}

	var firstVisitDate time.Time
//nolint:errcheck // zero value is acceptable fallback on scan failure
	_ = q.QueryRow(ctx, `SELECT MIN(start_time) FROM bookings WHERE user_id = $1 AND status = 'completed'`, userID).Scan(&firstVisitDate)
	if !firstVisitDate.IsZero() {
		annRows, err := q.Query(ctx, `
			SELECT id, discount_percent, milestone_value, milestone_unit FROM discount_campaigns
			WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'anniversary'
			AND NOT EXISTS (SELECT 1 FROM booking_discounts WHERE user_id = $1 AND source_id = discount_campaigns.id AND milestone_type = 'anniversary')
		`, userID)
		if err == nil {
			type annCampaign struct {
				id    string
				pct   float64
				value int
				unit  string
			}
			var campaigns []annCampaign
			for annRows.Next() {
				var c annCampaign
				if annRows.Scan(&c.id, &c.pct, &c.value, &c.unit) == nil {
					campaigns = append(campaigns, c)
				}
			}
			annRows.Close()

			for _, c := range campaigns {
				var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
				_ = q.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, c.id).Scan(&exists)
				if exists > 0 {
					continue
				}

				var matches bool
				elapsed := time.Since(firstVisitDate)
				switch c.unit {
				case "months":
					months := int(elapsed.Hours() / (30 * 24))
					matches = months >= c.value
				case "years":
					years := int(elapsed.Hours() / (365.25 * 24))
					matches = years >= c.value
				}
				if matches {
					discountAmount := roundTo2(bookingTotal * c.pct / 100)
					if _, err := q.Exec(ctx, `
						INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
						VALUES ($1, $2, 'campaign', $3, 'milestone', 'anniversary', $4, $5, $6)
					`, bookingID, userID, c.id, c.pct, bookingTotal, discountAmount); err != nil {
						log.Printf("Failed to insert anniversary discount: %v", err)
					} else {
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
						_, _ = q.Exec(ctx, `
							INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
							VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
						`, bookingID, discountAmount, userID)
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
						_, _ = q.Exec(ctx, `
							UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
						`, c.id)
					}
					break
				}
			}
		} else {
			log.Printf("Failed to query anniversary campaigns: %v", err)
		}
	}

	var firstPaymentMethod string
	if err := q.QueryRow(ctx, `
		SELECT payment_method FROM payments WHERE booking_id = $1 AND payment_method NOT IN ('discount', 'on_the_house') ORDER BY created_at ASC LIMIT 1
	`, bookingID).Scan(&firstPaymentMethod); err == nil && firstPaymentMethod == "in_person_card" {
		var globalCount int
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = q.QueryRow(ctx, `SELECT COUNT(*) FROM bookings WHERE status = 'completed'`).Scan(&globalCount)

		var globalCampaignID string
		var globalPercent float64
//nolint:errcheck // zero value is acceptable fallback on scan failure
		_ = q.QueryRow(ctx, `
			SELECT id, discount_percent FROM discount_campaigns
			WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'global_booking_count'
			AND milestone_value <= $1
			AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
			AND NOT EXISTS (SELECT 1 FROM booking_discounts WHERE source_id = discount_campaigns.id AND booking_id = $2)
			ORDER BY milestone_value DESC LIMIT 1
		`, globalCount, bookingID).Scan(&globalCampaignID, &globalPercent)

		if globalCampaignID != "" {
			var exists int
//nolint:errcheck // zero value is acceptable fallback on scan failure
			_ = q.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND source_id = $2`, bookingID, globalCampaignID).Scan(&exists)
			if exists == 0 {
				discountAmount := roundTo2(bookingTotal * globalPercent / 100)
				if _, err := q.Exec(ctx, `
					INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
					VALUES ($1, $2, 'campaign', $3, 'milestone', 'global_booking_count', $4, $5, $6)
				`, bookingID, userID, globalCampaignID, globalPercent, bookingTotal, discountAmount); err != nil {
					log.Printf("Failed to insert global milestone discount: %v", err)
				} else {
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
					VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
				`, bookingID, discountAmount, userID)
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
				_, _ = q.Exec(ctx, `
					UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
				`, globalCampaignID)
				}
			}
		}
	}

	// Apply referrer's referral discount if available
	if bookingTotal > 0 {
		var rdID string
		var rdPercent float64
		if err := q.QueryRow(ctx, `
			SELECT id, discount_percent FROM referral_discounts
			WHERE user_id = $1 AND used = FALSE
			LIMIT 1
		`, userID).Scan(&rdID, &rdPercent); err == nil && rdID != "" {
			exists := 0
//nolint:errcheck // zero value is acceptable fallback on scan failure
			_ = q.QueryRow(ctx, `SELECT 1 FROM booking_discounts WHERE booking_id = $1 AND discount_source = 'referral' AND source_id = $2`, bookingID, rdID).Scan(&exists)
			if exists == 0 {
				discountAmount := roundTo2(bookingTotal * rdPercent / 100)
				if _, err := q.Exec(ctx, `
					INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
					VALUES ($1, $2, 'referral', $3, NULL, NULL, $4, $5, $6)
				`, bookingID, userID, rdID, rdPercent, bookingTotal, discountAmount); err == nil {
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
					_, _ = q.Exec(ctx, `
						INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
						VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
					`, bookingID, discountAmount, userID)
//nolint:errcheck // exec errors are non-critical; best-effort inserts/updates
					_, _ = q.Exec(ctx, `
						UPDATE referral_discounts SET used = TRUE, used_at = NOW() WHERE id = $1
					`, rdID)
				}
			}
		}
	}
}

// buildSplitRecords determines whether to split a single Square charge into
// multiple payment records. Before the booking start time, the first 50% of
// the total is recorded as 'deposit' (protected under the deposit policy) and
//
// The first 50% of the booking total (minus any already deposited) is always
// carved out as a 'deposit' record, regardless of the payment size. The
// remainder first covers the booking balance then overflows into a 'tip' record.
//
// The primary record carries the Square payment ID for refund routing; split
// records share the same SquarePaymentID so the refund loop can avoid duplicate
// Square API calls while still creating audit records.
func buildSplitRecords(primary PaymentRecord, reqPaymentType string, info *BookingPaymentInfo, paymentAmount float64) []PaymentRecord {
	// After the booking starts there is no deposit protection window —
	// record the payment as a single entry with its original type.
	if clock.Now().After(info.StartTime) {
		return []PaymentRecord{primary}
	}

	// 1. Deposit portion: up to 50% of total, minus what's already been paid.
	maxDeposit := info.TotalAmount * ProtectedDepositMaxPct
	remainingDepositRoom := math.Max(0, maxDeposit-info.TotalPaid)
	depositAmount := math.Min(paymentAmount, remainingDepositRoom)
	depositAmount = math.Round(depositAmount*100) / 100

	// 2. Remaining after deposit.
	remainingAfterDeposit := math.Round((paymentAmount-depositAmount)*100) / 100

	// 3. Balance portion: covers whatever is still owed on the booking.
	bookingRemaining := math.Max(0, info.TotalAmount-info.TotalPaid-depositAmount)
	balancePortion := math.Min(remainingAfterDeposit, bookingRemaining)
	balancePortion = math.Round(balancePortion*100) / 100

	// 4. Tip: anything beyond the booking total.
	tipPortion := math.Round((remainingAfterDeposit-balancePortion)*100) / 100

	var records []PaymentRecord
	splitIdx := 0

	// 1. Deposit portion (always present when there's deposit room left).
	if depositAmount > 0.004 {
		dep := primary
		dep.PaymentType = "deposit"
		dep.Amount = depositAmount
		records = append(records, dep)
		splitIdx++
	}

	// 2. Balance / partial / full record — covers the remaining booking total.
	if balancePortion > 0.004 {
		bal := primary
		bal.Amount = balancePortion
		bal.Fees = 0
		if primary.IdempotencyKey != nil {
			k := *primary.IdempotencyKey + fmt.Sprintf("-split-%d", splitIdx)
			bal.IdempotencyKey = &k
		}
		totalPaidAfterBalance := info.TotalPaid + depositAmount + balancePortion
		switch {
		case totalPaidAfterBalance >= info.TotalAmount && totalPaidAfterBalance-balancePortion > 0:
			bal.PaymentType = "balance"
		case totalPaidAfterBalance >= info.TotalAmount:
			bal.PaymentType = "full"
		default:
			bal.PaymentType = "partial"
		}
		records = append(records, bal)
		splitIdx++
	}

	// If neither deposit nor balance was created (deposit exhausted, booking
	// fully paid), the primary is still a valid record — use it directly.
	if len(records) == 0 {
		primary.Fees = 0
		records = append(records, primary)
	}

	// Tip record — overflow beyond the booking total.
	if tipPortion > 0.004 {
		tip := primary
		tip.PaymentType = "tip"
		tip.Amount = tipPortion
		tip.Fees = 0
		splitIdx++
		if primary.IdempotencyKey != nil {
			k := *primary.IdempotencyKey + fmt.Sprintf("-split-%d", splitIdx)
			tip.IdempotencyKey = &k
		}
		records = append(records, tip)
	}

	// If nothing was appended (shouldn't happen given validation upstream),
	// return the primary as a fallback.
	if len(records) == 0 {
		return []PaymentRecord{primary}
	}
	return records
}

func GetUserPaymentMethods(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
	}

	service := NewPaymentService()
	cards, err := service.GetUserPaymentMethods(r.Context(), userID)
	if err != nil {
		log.Printf("Failed to get payment methods: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(cards)
}

func AdminGetUserPaymentMethods(w http.ResponseWriter, r *http.Request) {
	userID := chi.URLParam(r, "id")
	if userID == "" || !validators.IsValidID(userID) {
		http.Error(w, "Invalid user ID", http.StatusBadRequest)
		return
	}

	service := NewPaymentService()
	cards, err := service.GetUserPaymentMethods(r.Context(), userID)
	if err != nil {
		log.Printf("Failed to get payment methods for user %s: %v", userID, err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(cards)
}

func DeletePaymentMethod(w http.ResponseWriter, r *http.Request) {
	cardID := chi.URLParam(r, "id")
	if cardID == "" || !validators.IsValidID(cardID) {
		http.Error(w, "Payment method not found", http.StatusNotFound)
		return
	}

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

	service := NewPaymentService()
	err := service.DeletePaymentMethod(r.Context(), cardID, userID)
	if err != nil {
		log.Printf("Failed to delete payment method: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(map[string]string{"status": "deleted"})
}

type CreatePaymentMethodRequest struct {
	CardNumber string `json:"card_number" validate:"required"`
	Expiry     string `json:"expiry" validate:"required"`
	CVC        string `json:"cvc" validate:"required"`
}

func CreatePaymentMethod(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
	}

	var req CreatePaymentMethodRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	if err := validators.Validate.Struct(&req); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	// M8
	// L5

	if req.CardNumber == "" || req.Expiry == "" || req.CVC == "" {
		http.Error(w, "Card number, expiry, and CVC are required", http.StatusBadRequest)
		return
	}

	service := NewPaymentService()
	card, err := service.CreatePaymentMethodFromDetails(r.Context(), userID, req.CardNumber, req.Expiry, req.CVC)
	if err != nil {
		if strings.Contains(err.Error(), "invalid") || strings.Contains(err.Error(), "expired") {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
		log.Printf("Failed to create payment method: %v", err)
		http.Error(w, "Failed to add card", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(card)
}

func RefundPayment(w http.ResponseWriter, r *http.Request) {
	paymentID := chi.URLParam(r, "payment_id")
	if paymentID == "" || !validators.IsValidID(paymentID) {
		http.Error(w, "Payment not found", http.StatusNotFound)
		return
	}

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

	var req RefundRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode refund request: %v", err)
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	if err := ValidateAmount(req.Amount); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := ValidateRefundReason(req.Reason); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	service := NewPaymentService()

	payment, err := service.GetPaymentByID(r.Context(), paymentID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "Payment not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to get payment: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if payment.Status != "completed" {
		http.Error(w, "Can only refund completed payments", http.StatusBadRequest)
		return
	}

	if payment.SquarePaymentID == nil {
		http.Error(w, "Payment has no Square reference", http.StatusBadRequest)
		return
	}

	alreadyRefunded, err := service.GetAlreadyRefundedAmount(r.Context(), paymentID)
	if err != nil {
		log.Printf("Failed to get already refunded amount: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if req.Amount+alreadyRefunded > int64(payment.Amount*100) {
		http.Error(w, "Refund amount exceeds payment amount", http.StatusBadRequest)
		return
	}

	// Begin a transaction so that the Square refund and the DB record are
	// atomically linked. If the commit fails after Square processes the refund,
	// a CRITICAL log alerts monitoring — the Square refund cannot be reversed,
	// but the DB record can be recreated from the log.
	tx, err := db.Conn.Begin(r.Context())
	if err != nil {
		log.Printf("Failed to begin transaction for refund: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}
	defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

	refundReq := square.RefundPaymentReq{
		PaymentID:      *payment.SquarePaymentID,
		Amount:         req.Amount,
		IdempotencyKey: paymentID + "-" + strconv.FormatInt(req.Amount, 10),
		Reason:         req.Reason,
	}

	refundResult, err := SquareClient.RefundPayment(r.Context(), refundReq)
	if err != nil {
		log.Printf("Failed to refund payment: %v", err)
		http.Error(w, "Refund failed", http.StatusInternalServerError)
		return
	}

	squareRefundID := refundResult.ID

	var refundID string
	err = tx.QueryRow(r.Context(), `
		INSERT INTO refunds (payment_id, booking_id, amount, square_refund_id, status, reason, created_by, created_at)
		VALUES ($1, $2, $3, $4, 'completed', $5, $6, $7)
		RETURNING id
	`,
		paymentID,
		payment.BookingID,
		float64(req.Amount)/100.0,
		squareRefundID,
		req.Reason,
		adminID,
		clock.Now(),
	).Scan(&refundID)
	if err != nil {
		log.Printf("Failed to create refund record: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if err := tx.Commit(r.Context()); err != nil {
		log.Printf("CRITICAL: Refund committed by Square (%s) but DB transaction failed — refund record %s may be missing: %v", squareRefundID, refundID, err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(RefundResponse{
		ID:        refundID,
		PaymentID: paymentID,
		Amount:    req.Amount,
		Status:    "completed",
		Reason:    req.Reason,
		CreatedAt: clock.Now().Format(time.RFC3339),
	})
}

func CreateTipPayment(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

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

	var req CreateTipPaymentRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		log.Printf("Failed to decode tip payment request: %v", err)
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	if err := validators.Validate.Struct(&req); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	// M8
	// L5

	if err := ValidateAmount(req.Amount); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if req.CardToken == "" {
		http.Error(w, "Card token is required", http.StatusBadRequest)
		return
	}

	service := NewPaymentService()

	bookingUserID, err := service.GetBookingUserID(r.Context(), bookingID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "Booking not found", http.StatusNotFound)
			return
		}
		log.Printf("Failed to get booking user: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if bookingUserID != userID {
		http.Error(w, "Unauthorized", http.StatusForbidden)
		return
	}

	hasCompleted, err := service.HasCompletedPayment(r.Context(), bookingID)
	if err != nil {
		log.Printf("Failed to check for completed payments: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	if !hasCompleted {
		http.Error(w, "Booking must have a completed payment before adding tip", http.StatusBadRequest)
		return
	}

	idempotencyKey := bookingID + "-tip-" + strconv.FormatInt(req.Amount, 10)

	// Step 1: Insert payment record in 'pending' state inside a DB transaction.
	// Square is NOT called yet — if the tx fails, no harm done.
	record := PaymentRecord{
		BookingID:      bookingID,
		PaymentType:    "tip",
		PaymentMethod:  "online_square",
		Status:         "pending",
		Amount:         float64(req.Amount) / 100.0,
		IdempotencyKey: &idempotencyKey,
		Fees:           0,
		CreatedAt:      clock.Now(),
		UpdatedAt:      clock.Now(),
		CreatedBy:      &userID,
	}

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

	paymentID, err := service.CreatePaymentRecordTx(r.Context(), tx, record, nil)
	if err != nil {
		_ = tx.Rollback(r.Context())
		log.Printf("Failed to create payment record: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}
	ApplyVATToBookingPayment(r.Context(), tx, paymentID)

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

	// Step 2: DB transaction committed — safe to call Square now.
	// If Square fails, the record stays 'pending' for manual retry.
	paymentReq := square.CreatePaymentReq{
		Amount:         req.Amount,
		Currency:       "GBP",
		SourceID:       req.CardToken,
		IdempotencyKey: idempotencyKey,
		ReferenceID:    bookingID,
		Note:           "tip",
	}

	paymentResult, err := SquareClient.CreatePayment(r.Context(), paymentReq)
	if err != nil {
		log.Printf("Failed to create tip payment: %v", err)
		// Payment record intentionally left as 'pending' for manual retry.
		http.Error(w, "Payment failed", http.StatusPaymentRequired)
		return
	}

	// Step 3: Square succeeded — update the payment record.
	_, upErr := db.Conn.Exec(r.Context(),
		`UPDATE payments SET status = 'completed', square_payment_id = $1 WHERE id = $2`,
		paymentResult.SquarePayID, paymentID,
	)
	if upErr != nil {
		log.Printf("Failed to update payment %s after Square success: %v (square_payment_id=%s)", paymentID, upErr, paymentResult.SquarePayID)
		// Square charge succeeded but status update failed.
		// Record stays 'pending' for manual reconciliation.
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	_ = json.NewEncoder(w).Encode(PaymentResponse{
		ID:          paymentID,
		BookingID:   bookingID,
		PaymentType: "tip",
		Status:      "completed",
		Amount:      req.Amount,
		CardBrand:   paymentResult.CardBrand,
		CardLast4:   paymentResult.CardLast4,
		ReceiptURL:  paymentResult.ReceiptURL,
		CreatedAt:   clock.Now().Format(time.RFC3339),
	})
}

func GetBookingPaymentSummary(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

	userID, _ := r.Context().Value(mw.UserIDKey).(string)
	userRole, _ := r.Context().Value(mw.UserRoleKey).(string)

	service := NewPaymentService()

	if userRole != "admin" && userID != "" {
		bookingUserID, err := service.GetBookingUserID(r.Context(), bookingID)
		if err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				http.Error(w, "Booking not found", http.StatusNotFound)
				return
			}
			log.Printf("Failed to get booking user: %v", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}

		if bookingUserID != userID {
			http.Error(w, "Unauthorized", http.StatusForbidden)
			return
		}
	}

	summary, err := service.GetBookingPaymentSummary(r.Context(), bookingID)
	if err != nil {
		log.Printf("Failed to get payment summary: %v", err)
		http.Error(w, "internal server error", http.StatusInternalServerError)
		return
	}

	payments := make([]PaymentResponse, len(summary.Payments))
	for i, p := range summary.Payments {
		payments[i] = PaymentResponse{
			ID:          p.ID,
			BookingID:   p.BookingID,
			PaymentType: p.PaymentType,
			Status:      p.Status,
			Amount:      int64(p.Amount * 100),
			CardLast4:   p.CardLast4,
			CreatedAt:   p.CreatedAt.Format(time.RFC3339),
		}
	}

	refunds := make([]RefundResponse, len(summary.Refunds))
	for i, rf := range summary.Refunds {
		refunds[i] = RefundResponse{
			ID:        rf.ID,
			PaymentID: rf.PaymentID,
			Amount:    int64(rf.Amount * 100),
			Status:    rf.Status,
			Reason:    rf.Reason,
			CreatedAt: rf.CreatedAt.Format(time.RFC3339),
		}
	}

	_ = json.NewEncoder(w).Encode(PaymentSummaryResponse{
		TotalAmount:     int64(summary.TotalAmount * 100),
		PaidAmount:      int64(summary.PaidAmount * 100),
		RefundedAmount:  int64(summary.RefundedAmount * 100),
		RemainingAmount: int64(summary.RemainingAmount * 100),
		TotalVATAmount:  int64(summary.TotalVATAmount * 100),
		TotalNetAmount:  int64(summary.TotalNetAmount * 100),
		Payments:        payments,
		Refunds:         refunds,
	})
}

// PaymentLockDuration is the TTL for a payment-in-flight lock in minutes.
const PaymentLockDuration = 5

// AcquirePaymentLock creates or extends a 5-minute time_blocker for the
// booking's slot so that pending_release eviction is blocked during card
// entry and Square charge processing.
func AcquirePaymentLock(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

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

	// Verify the user owns this booking.
	var bookingUserID string
	if err := db.Conn.QueryRow(r.Context(),
		"SELECT user_id FROM bookings WHERE id = $1", bookingID,
	).Scan(&bookingUserID); err != nil {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}
	if bookingUserID != userID {
		http.Error(w, "Unauthorized", http.StatusForbidden)
		return
	}

	// Before acquiring the lock, double-check the slot is still available.
	// For confirmed/in_progress bookings this is a formality; for
	// pending_release bookings it catches the eviction race before we
	// create a time_blocker — the NOT EXISTS guard in eviction queries
	// handles the sub-5-minute race, this catches the >5-minute gap.
	var currentStatus string
	var startTime time.Time
	if err := db.Conn.QueryRow(r.Context(),
		"SELECT status, start_time FROM bookings WHERE id = $1", bookingID,
	).Scan(&currentStatus, &startTime); err != nil {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

	// If the booking has been evicted (deposit_lapsed) or reached a terminal
	// state, reject the lock — payment cannot proceed.
	if !IsValidBookingStatusForPayment(currentStatus) || currentStatus == "pending" {
		log.Printf("Payment lock rejected: booking %s is in status %q (no longer accepting payments)", bookingID, currentStatus)
		http.Error(w, "This booking is no longer accepting payments. The slot may have been released.", http.StatusConflict)
		return
	}

	// Upsert the time_blocker atomically: delete old PAYMENT_IN_FLIGHT and insert
	// a fresh one in a single transaction. Prevents lock loss if INSERT fails.
	tx, err := db.Conn.Begin(r.Context())
	if err != nil {
		log.Printf("Failed to start transaction for payment lock: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

	if _, err := tx.Exec(r.Context(), `
		DELETE FROM time_blockers
		WHERE description = 'PAYMENT_IN_FLIGHT:' || $1
	`, bookingID); err != nil {
		log.Printf("Failed to clear previous payment lock for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

	if _, err := tx.Exec(r.Context(), `
		INSERT INTO time_blockers (start_time, duration_minutes, description, created_by)
		VALUES (NOW(), $1, $2, $3)
	`, PaymentLockDuration, "PAYMENT_IN_FLIGHT:"+bookingID, userID); err != nil {
		log.Printf("Failed to acquire payment lock for booking %s: %v", bookingID, err)
		http.Error(w, "Failed to secure payment slot", http.StatusInternalServerError)
		return
	}

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

	_ = json.NewEncoder(w).Encode(map[string]any{
		"status":    "locked",
		"ttl_min":   PaymentLockDuration,
		"bookingID": bookingID,
	})
}

// ReleasePaymentLock removes the PAYMENT_IN_FLIGHT time_blocker for a booking.
func ReleasePaymentLock(w http.ResponseWriter, r *http.Request) {
	bookingID := chi.URLParam(r, "id")
	if bookingID == "" || !validators.IsValidID(bookingID) {
		http.Error(w, "Booking not found", http.StatusNotFound)
		return
	}

	tx, err := db.Conn.Begin(r.Context())
	if err != nil {
		log.Printf("Failed to begin transaction: %v", err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}
	defer func() {
    if err := tx.Rollback(r.Context()); err != nil {
        slog.Error("failed to rollback transaction", "err", err)
    }
}()

	if _, err := tx.Exec(r.Context(), `
		DELETE FROM time_blockers
		WHERE description = 'PAYMENT_IN_FLIGHT:' || $1
	`, bookingID); err != nil {
		log.Printf("Failed to release payment lock for booking %s: %v", bookingID, err)
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		return
	}

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

	w.WriteHeader(http.StatusNoContent)
}
