Crussell/backend/handlers/webhooks/square.go

package webhooks

import (
	"context"
	"crypto/hmac"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log"
	"math"
	"net/http"
	"os"
	"strconv"
	"strings"
	"sync"
	"time"

	"crussell/clock"
	"crussell/db"
	"crussell/handlers/payments"
	"crussell/internal/adminnotify"

	"github.com/jackc/pgx/v5"
)

type SquareWebhookEvent struct {
	Type       string          `json:"type"`
	EventID    string          `json:"event_id"`
	CreatedAt  string          `json:"created_at"`
	Data       json.RawMessage `json:"data"`
	LocationID string          `json:"location_id"`
}

// squareWebhookDedup is a bounded, mutex-guarded set of recently handled
// event IDs. It is a FAST-PATH cache only: the persistent source of truth is
// the square_webhook_events table (see HandleSquareWebhook). It lets a replayed
// delivery be dropped without a DB round-trip, but a restart clears it — the DB
// row is what keeps delivery at-least-once across restarts (a replay after a
// crash is re-dispatched and absorbed by the idempotent handlers, per the
// dispatch-first ordering in HandleSquareWebhook).
type squareWebhookDedup struct {
	mu    sync.Mutex
	seen  map[string]struct{}
	order []string
	max   int
}

func newSquareWebhookDedup(max int) *squareWebhookDedup {
	return &squareWebhookDedup{
		seen:  make(map[string]struct{}),
		order: make([]string, 0, max),
		max:   max,
	}
}

// has reports whether id is in the set WITHOUT recording it. Used as the
// fast-path short-circuit before the DB dedup insert.
func (d *squareWebhookDedup) has(id string) bool {
	d.mu.Lock()
	defer d.mu.Unlock()
	_, ok := d.seen[id]
	return ok
}

// register records id, reporting whether it was already present (set untouched
// on a replay, preserving insertion order). Mutex-guarded — the handler may be
// hit concurrently. Called only after the DB insert has confirmed the event's
// fate, so a failed DB write never leaves a stale entry that would drop a retry.
func (d *squareWebhookDedup) register(id string) bool {
	d.mu.Lock()
	defer d.mu.Unlock()
	if _, ok := d.seen[id]; ok {
		return true
	}
	d.seen[id] = struct{}{}
	d.order = append(d.order, id)
	if len(d.order) > d.max {
		oldest := d.order[0]
		d.order = d.order[1:]
		delete(d.seen, oldest)
	}
	return false
}

// 500 IDs far exceeds the latency payoff of the fast-path cache; the DB row
// (square_webhook_events) is the unbounded, restart-safe source of truth.
var squareWebhookEventsSeen = newSquareWebhookDedup(500)

// webhookRetryFirstSeenMu guards webhookRetryFirstSeen.
var webhookRetryFirstSeenMu sync.Mutex

// webhookRetryFirstSeen records the first delivery time of a retryable 503
// response per event_id. Round-3 fix 2a/2b: a 503 (unknown COMPLETED payment,
// refund-before-row) makes Square retry for ~24h and then silently drop the
// event — this map lets the handler raise a critical admin notification once
// the retry budget is exhausted. Bounded like the dedup cache: entries are
// useful for at most 24h, so a capped sweep drops stale entries whenever the
// map overflows. Restart loses the map — conservative: the 24h clock restarts,
// so a notification may be missed but never falsely raised. The DB
// square_webhook_events table is deliberately NOT used because the existing
// 503 semantics promise NO dedup row on a rejected event.
var webhookRetryFirstSeen = make(map[string]time.Time)

// webhookRetryExceededBudget records eventID's first retryable-503 delivery
// and reports whether it has been retrying for more than webhookRetryBudget.
// The first 503 just records the timestamp (returns false); a re-delivery
// whose first-seen is older than the budget returns true exactly once per
// event (the caller's notification is deduped by the event_id-derived id, so
// repeated true returns stay a single notification row).
func webhookRetryExceededBudget(eventID string) bool {
	webhookRetryFirstSeenMu.Lock()
	defer webhookRetryFirstSeenMu.Unlock()
	now := clock.Now()
	// Lazy eviction: only stale (>24h) entries are ever removed, so the map
	// stays bounded to events still within the retry window.
	if len(webhookRetryFirstSeen) > 500 {
		for id, ts := range webhookRetryFirstSeen {
			if now.Sub(ts) > 24*time.Hour {
				delete(webhookRetryFirstSeen, id)
			}
		}
	}
	first, ok := webhookRetryFirstSeen[eventID]
	if !ok {
		webhookRetryFirstSeen[eventID] = now
		return false
	}
	return now.Sub(first) > 24*time.Hour
}

// errWebhookParseFailure marks a dispatch error caused by a KNOWN money event
// whose payload could not be parsed or extracted (as opposed to a DB failure).
// HandleSquareWebhook distinguishes it from other dispatch errors to log the
// failure at ERROR level with the event_id and type — and, like every dispatch
// error, it returns 5xx WITHOUT committing the dedup row, so Square re-delivers
// the event instead of the money state being lost forever.
var errWebhookParseFailure = errors.New("webhook payload parse failure")

// isSquareMoneyFamily reports whether an event type belongs to a money-state
// family (payment.*, refund.*, dispute.*, terminal.*, plus money-adjacent
// cash_drawer.*, gift_card.*, transaction.* and invoice.*). invoice.* is
// deliberately included even though this app does not use Square Invoices: an
// invoice event is money-adjacent (Invoice carries amounts and payment state),
// so if Square Invoices are ever used the funds must surface as unknown-money
// and stay retried rather than being acked 200 + dedup'd permanently (M11).
// Unknown events in these families MUST NOT be 200-acked — see the
// default-branch split in HandleSquareWebhook.
func isSquareMoneyFamily(eventType string) bool {
	for _, prefix := range []string{
		"payment.", "refund.", "dispute.", "terminal.",
		"cash_drawer.", "gift_card.", "transaction.", "invoice.",
	} {
		if strings.HasPrefix(eventType, prefix) {
			return true
		}
	}
	return false
}

// isSquareNonMoneyFamily reports whether an event type belongs to a known
// non-money family this app will never process (customer.*, card.*, order.*,
// booking.* and the other Square families below, none of which carry money
// state this app tracks). These are deliberately acked 200 WITH a dedup row so
// Square stops retrying them — see the default-branch split in
// HandleSquareWebhook.
func isSquareNonMoneyFamily(eventType string) bool {
	for _, prefix := range []string{
		"customer.", "card.", "order.", "booking.",
		"appointment.", "availability.", "loyalty.", "merchant.",
		"location.", "labor.", "inventory.", "site.", "device.",
		"team_member.", "subscription.", "webhook.",
	} {
		if strings.HasPrefix(eventType, prefix) {
			return true
		}
	}
	return false
}

// webhookDBTimeout bounds the DB work performed while dispatching a webhook.
// The work runs on a Background-derived context — so a client disconnect cannot
// cancel it (the at-least-once delivery contract must survive) — but is
// timeout-bound so a hung DB call cannot hold a pgx pool connection forever;
// repeated hangs would otherwise exhaust the pool. 30s is the same
// post-request DB budget used elsewhere in the backend (handlers/user).
const webhookDBTimeout = 30 * time.Second

// webhookDBContext returns a timeout-bound, Background-derived context for
// webhook dispatch DB work.
func webhookDBContext() (context.Context, context.CancelFunc) {
	return context.WithTimeout(context.Background(), webhookDBTimeout)
}

// squareEnvironmentMismatch reports whether the webhook's square-environment
// header conflicts with the deployment's configured SQUARE_ENVIRONMENT.
//
// The check is enforced for every non-dev/mock deployment: configured
// production and sandbox are compared directly, and an EMPTY or UNKNOWN
// configured SQUARE_ENVIRONMENT is treated as PRODUCTION (LOW-4) — matching how
// the rest of the backend treats empty/unknown env fail-closed (main.go:214
// and payments twofa.go:40-42) — so a sandbox subscription mis-pointed at an
// unconfigured production URL cannot process sandbox events against production
// state. In dev/mock deployments the header is informational and a mismatch is
// not rejectable; the dev/mock determination delegates to the shared
// payments.IsExplicitDevOrMockEnv (handlers/payments/twofa.go) rather than
// re-implementing the env-value list, so this check and the 2FA gate can never
// diverge on what counts as the dev/mock stack.
//
// An absent header is allowed through: real Square deliveries always send it,
// so a missing header in an enforced deployment is a non-Square client (which
// already failed signature verification) or a local mock/dev poster — both
// safely handled downstream. Rejection is 403. NOTE: Square retries ANY
// non-2xx response (4xx included) with exponential backoff for up to ~24h, so
// the 403 does not by itself stop the retry loop — but a square-environment
// mismatch is a PERMANENT configuration error that no retry can resolve, the
// handler is intentionally fail-closed (every replay is rejected identically
// before any state change), and the DB-level dedup (square_webhook_events +
// ON CONFLICT) makes the replayed deliveries replay-safe. The 403 forces the
// operator to fix the subscription or the environment setting.
func squareEnvironmentMismatch(headerEnv string) bool {
	headerEnv = strings.ToLower(strings.TrimSpace(headerEnv))
	if headerEnv == "" {
		return false
	}
	// Dev/mock deployments (mock/dev/development/test) are never enforced —
	// the shared predicate is the single source of truth for that
	// classification, so the webhook's interpretation matches the 2FA gate
	// exactly (handlers/payments/twofa.go:IsExplicitDevOrMockEnv).
	if payments.IsExplicitDevOrMockEnv() {
		return false
	}
	configured := strings.ToLower(strings.TrimSpace(os.Getenv("SQUARE_ENVIRONMENT")))
	if configured != "sandbox" {
		// Empty/unknown configured environment is treated as PRODUCTION for the
		// env check (LOW-4), matching the fail-closed production default the
		// rest of the backend applies to empty/unknown SQUARE_ENVIRONMENT.
		configured = "production"
	}
	return headerEnv != configured
}

// HandleSquareWebhook verifies and dispatches Square webhook events.
//
// Fail-closed chain: 503 when the signing key is unset, 403 on a missing/bad
// signature, 400 on malformed JSON or an empty event_id (which cannot be
// deduplicated — Square always sends one, so this is defensive). A correctly
// signed, well-formed event is deduplicated by event_id before dispatch and
// acknowledged 200. Unknown event types are split by family: money-state
// families (payment.*, refund.*, dispute.*, terminal.* and money-adjacent
// prefixes including invoice.*) and truly unknown prefixes get 501 (Square
// retries, no dedup row), while known non-money families (customer.*, card.*,
// order.*, booking.*, ...) are acked 200 WITH the dedup row so the subscription
// is never flooded into suspension.
func HandleSquareWebhook(w http.ResponseWriter, r *http.Request) {
	r.Body = http.MaxBytesReader(w, r.Body, 512*1024)
	body, err := io.ReadAll(r.Body)
	if err != nil {
		log.Printf("Failed to read webhook body: %v", err)
		http.Error(w, "request body too large or unreadable", http.StatusRequestEntityTooLarge)
		return
	}
	defer r.Body.Close()

	// Verification logic per Square spec (HMAC-SHA256, base64, notificationURL + body).
	// Production setup: set SQUARE_WEBHOOK_SIGNATURE_KEY and SQUARE_WEBHOOK_NOTIFICATION_URL
	// in env vars (see Square Developer Console → Webhooks → Subscription).
	// Reference: https://developer.squareup.com/docs/webhooks/step3validate

	// Fail closed: a missing signing key means the webhook cannot be verified,
	// so reject rather than process unauthenticated events (S-4). Square
	// always sends the signature header, so an unset key in production is a
	// misconfiguration that must not silently accept forged events.
	signingKey := os.Getenv("SQUARE_WEBHOOK_SIGNATURE_KEY")
	notificationURL := os.Getenv("SQUARE_WEBHOOK_NOTIFICATION_URL")
	if notificationURL == "" {
		// Fail-closed fallback (Round 2 Loop A finding 6): an unset URL falls
		// back to the public dev default so the handler always has a string to
		// HMAC against. The subscription is fail-closed — the key AND the URL
		// must exactly match the Square Dashboard configuration, so with the
		// URL unset every GENUINE Square event fails signature verification
		// here (403) and no event is ever processed; only the operator can fix
		// the config. main.go's startup check (checkWebhookSignatureKey) warns
		// when the key is set but the URL is unset — the reverse
		// misconfiguration — so the breakage is visible at boot, not silent.
		notificationURL = "http://localhost:8080/webhooks/square"
	}
	if signingKey == "" {
		log.Printf("SQUARE_WEBHOOK_SIGNATURE_KEY is not set — rejecting webhook (fail-closed)")
		http.Error(w, "webhook signature verification unavailable", http.StatusServiceUnavailable)
		return
	}
	signature := r.Header.Get("x-square-hmacsha256-signature")
	if signature == "" {
		log.Printf("Missing Square webhook signature header")
		http.Error(w, "Invalid signature", http.StatusForbidden)
		return
	}
	if !verifySquareSignature(body, signature, signingKey, notificationURL) {
		log.Printf("Invalid Square webhook signature")
		http.Error(w, "Invalid signature", http.StatusForbidden)
		return
	}

	// Fail-closed environment check: a sandbox subscription mis-pointed at the
	// production URL + key would otherwise process sandbox events against
	// production state. 403 is correct because a square-environment mismatch is
	// a permanent config error no retry can fix — note that Square retries ANY
	// non-2xx (4xx included) for up to ~24h, so the 403 does not stop the retry
	// loop by itself; the handler rejects every replay identically (fail-closed,
	// before any state change) and the DB-level dedup (square_webhook_events +
	// ON CONFLICT) makes the replayed deliveries replay-safe. See
	// squareEnvironmentMismatch for the exact enforcement conditions.
	if squareEnvironmentMismatch(r.Header.Get("square-environment")) {
		log.Printf("[SQUARE-WEBHOOK] Rejecting event: square-environment header %q does not match configured SQUARE_ENVIRONMENT %q (403)",
			r.Header.Get("square-environment"), os.Getenv("SQUARE_ENVIRONMENT"))
		http.Error(w, "square environment mismatch", http.StatusForbidden)
		return
	}

	var event SquareWebhookEvent
	if err := json.Unmarshal(body, &event); err != nil {
		log.Printf("Failed to parse webhook event: %v", err)
		http.Error(w, "Invalid event", http.StatusBadRequest)
		return
	}

	// An empty event_id cannot be deduplicated. Square always sends event_id,
	// so this is defensive — but once handlers mutate state, a duplicate
	// empty-ID event would double-apply. Reject with 400 (fail-safe, no
	// dispatch, no dedup row). NOTE: Square retries ANY non-2xx response (4xx
	// included) with exponential backoff for up to ~24h, so the 400 does NOT by
	// itself stop the retry loop — but the handler is fail-closed: every replay
	// is rejected identically BEFORE any state change or dedup insert, so no
	// side effect can ever be applied, and the DB-level dedup
	// (square_webhook_events + ON CONFLICT) keeps the repeated deliveries
	// replay-safe.
	if event.EventID == "" {
		log.Printf("[SQUARE-WEBHOOK] Rejecting event with empty event_id (400)")
		http.Error(w, "Invalid event", http.StatusBadRequest)
		return
	}

	// Fast-path dedup: a correctly signed replay of a recently handled event is
	// dropped in memory without a DB round-trip. The persistent source of truth
	// is the square_webhook_events row committed AFTER dispatch below, so this
	// cache never hides an event whose dedup row is not yet persisted — a crash
	// before that commit simply replays the event, which the idempotent handlers
	// absorb.
	if squareWebhookEventsSeen.has(event.EventID) {
		log.Printf("[SQUARE-WEBHOOK] Duplicate event_id %s; skipping (already processed)", event.EventID)
		w.WriteHeader(http.StatusOK)
		_, _ = w.Write([]byte("ok"))
		return
	}
	if db.Conn == nil {
		log.Printf("[SQUARE-WEBHOOK] DB unavailable — rejecting event_id %s (fail-closed)", event.EventID)
		http.Error(w, "webhook processing unavailable", http.StatusServiceUnavailable)
		return
	}

	log.Printf("[SQUARE-WEBHOOK] Received event: %s", event.Type)

	// Dispatch FIRST, then commit the dedup row. The handlers mutate state, so a
	// dedup row committed before dispatch would permanently drop the event on a
	// crash between the insert and the dispatch (Square's retry would be
	// 200-skipped, and dispute.created has no sweep fallback). Committing after
	// a successful dispatch keeps delivery at-least-once: on any dispatch error
	// NO dedup row is written and we return 5xx so Square retries. The handlers
	// are idempotent (status='pending'-guarded UPDATEs keyed on the Square id),
	// so a retry — or two retries dispatching concurrently — applies each state
	// change at most once and is otherwise a no-op.
	var dispatchErr error
	switch event.Type {
	// Square fires payment.completed and payment.canceled as SEPARATE event
	// types from payment.updated, but all of them carry the same full Payment
	// object in data.object.payment, and handlePaymentUpdated reconciles purely
	// from the payload's id/status (squarePaymentStatusToLocal maps COMPLETED/
	// CANCELED/FAILED). Routing all four here closes the silent gap where a
	// completed or canceled charge was previously acked 200 and never
	// reconciled against the local pending row.
	case "payment.updated", "payment.created", "payment.completed", "payment.canceled":
		dispatchErr = handlePaymentUpdated(event.Data)
	// Same reasoning for refunds: refund.completed/refund.canceled are distinct
	// event types carrying the full PaymentRefund object in data.object.refund,
	// which handleRefundUpdated reconciles by status.
	case "refund.updated", "refund.created", "refund.completed", "refund.canceled":
		dispatchErr = handleRefundUpdated(event.Data)
	case "dispute.created":
		dispatchErr = handleDisputeCreated(event.Data)
	case "dispute.state.updated":
		dispatchErr = handleDisputeStateUpdated(event.Data)
	case "dispute.evidence.created", "dispute.evidence.deleted":
		dispatchErr = handleDisputeEvidence(event.Data)
	case "terminal.checkout.created", "terminal.checkout.updated":
		dispatchErr = handleTerminalCheckout(event.Data)
	default:
		// Unknown-type handling is SPLIT by event family so Square's retry
		// policy (it re-delivers 5xx responses) can never flood the
		// subscription into suspension — a suspended subscription silently
		// kills money-event delivery (payment.created/updated).
		//
		//  • MONEY-STATE families (any payment.*, refund.*, dispute.*,
		//    terminal.*, cash_drawer.*, gift_card.*, transaction.*, invoice.*
		//    not explicitly handled above, plus future money-adjacent
		//    prefixes): keep 501 so Square retries. A money event this app does
		//    not yet handle is NEVER a safe 200-ack (the caller would commit the
		//    dedup row and the event would be dropped forever); no dedup row is
		//    written and a later retry re-dispatches idempotently if code
		//    support for the type lands before the retry window closes.
		//    invoice.* is treated as money-adjacent even though Square Invoices
		//    are unused today: an invoice carries amounts and payment state, so
		//    acked invoice funds would be invisible to the app (M11).
		//  • KNOWN NON-MONEY families (customer.*, card.*, order.*,
		//    booking.* — plus appointment.*, availability.*, loyalty.*,
		//    merchant.*, location.*, labor.*, inventory.*, site.*, device.*,
		//    team_member.*, subscription.*, webhook.* — and anything else
		//    Square can emit that this app will never process): deliberately
		//    acked 200 WITH the dedup row committed so Square stops retrying.
		//    They carry no money state this app tracks, so acking loses
		//    nothing — and retrying them at any regularity would fill the
		//    subscription's retry queue until Square suspends it, silently
		//    killing money-event delivery. Logged at WARN.
		//  • TRULY unknown prefixes (matching neither list): conservative
		//    501 — the type could be a new money family Square just added.
		switch {
		case isSquareMoneyFamily(event.Type):
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: unhandled money-family Square event type %q (event_id=%s) — not acknowledged; returning 501 so Square retries", event.Type, event.EventID)
			// A19: raise the operator-facing admin notification BEFORE the 501.
			// Without it the event is retried by Square for ~24h and then
			// silently dropped with only a log line. The notification is a
			// SEPARATE table — no square_webhook_events dedup row is written
			// here, so Square keeps retrying the event exactly as before.
			notifCtx, notifCancel := webhookDBContext()
			insertUnknownEventNotification(notifCtx, event.Type, event.EventID)
			notifCancel()
			http.Error(w, "unhandled webhook event type", http.StatusNotImplemented)
			return
		case isSquareNonMoneyFamily(event.Type):
			log.Printf("[SQUARE-WEBHOOK] WARNING: acknowledged unhandled non-money event %q (event_id=%s) — committed dedup row; Square stops retrying", event.Type, event.EventID)
		default:
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: unhandled Square event type %q (event_id=%s) — not acknowledged; returning 501 so Square retries", event.Type, event.EventID)
			notifCtx, notifCancel := webhookDBContext()
			insertUnknownEventNotification(notifCtx, event.Type, event.EventID)
			notifCancel()
			http.Error(w, "unhandled webhook event type", http.StatusNotImplemented)
			return
		}
	}
	if dispatchErr != nil {
		// A parse failure on a known money event is logged at ERROR with the
		// event_id and type so operators can see exactly which delivery was
		// unparseable and is being retried (rather than silently lost). Every
		// dispatch error — parse failure or DB failure — returns 5xx WITHOUT
		// committing the dedup row, so Square re-delivers the event.
		if errors.Is(dispatchErr, errWebhookParseFailure) {
			log.Printf("[SQUARE-WEBHOOK] ERROR: known money event %s (event_id=%s) payload failed to parse: %v — NOT recording dedup row; Square will retry", event.Type, event.EventID, dispatchErr)
		} else {
			log.Printf("[SQUARE-WEBHOOK] Event %s (%s) dispatch failed: %v — NOT recording dedup row; Square will retry", event.Type, event.EventID, dispatchErr)
		}
		// Round-3 fix 2a/2b: track the event's first-seen time so a retryable
		// 503 that survives Square's ~24h retry budget raises a critical admin
		// notification instead of being silently dropped. The map keeps the
		// first delivery time per event_id; re-deliveries past 24h raise the
		// notification (deduped by the event_id-derived notification id).
		if webhookRetryExceededBudget(event.EventID) {
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: event %s (event_id=%s) has been retrying for over 24h — Square retry budget exhausted; raising admin notification", event.Type, event.EventID)
			notifCtx, notifCancel := webhookDBContext()
			insertUnknownEventNotification(notifCtx, event.Type, event.EventID)
			notifCancel()
		}
		http.Error(w, "webhook processing failed", http.StatusServiceUnavailable)
		return
	}

	// Commit the dedup row AFTER successful dispatch. Fail closed on a write
	// error: without a persisted row we cannot prove the event was handled, so
	// reject and let Square retry (the retry re-dispatches idempotently and
	// retries the insert). event_id is not PII, so logging it is safe.
	// Commit the dedup row AFTER successful dispatch. Fail closed on a write
	// error: without a persisted row we cannot prove the event was handled, so
	// reject and let Square retry (the retry re-dispatches idempotently and
	// retries the insert). event_id is not PII, so logging it is safe. A
	// bounded Background context keeps this post-dispatch write alive across a
	// client disconnect without letting a hung insert hold the pool forever.
	dedupCtx, dedupCancel := webhookDBContext()
	defer dedupCancel()
	tag, err := db.Conn.Exec(dedupCtx,
		"INSERT INTO square_webhook_events (event_id) VALUES ($1) ON CONFLICT (event_id) DO NOTHING", event.EventID)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to record event_id %s (dedup write failed): %v", event.EventID, err)
		http.Error(w, "webhook processing unavailable", http.StatusServiceUnavailable)
		return
	}
	// Record in the fast-path cache only after the DB write succeeds, so a
	// failed write never leaves a stale entry that would drop a retry.
	squareWebhookEventsSeen.register(event.EventID)
	if tag.RowsAffected() == 0 {
		// A concurrent duplicate delivery already committed this event's row.
		log.Printf("[SQUARE-WEBHOOK] Duplicate event_id %s; acknowledging (already processed)", event.EventID)
		w.WriteHeader(http.StatusOK)
		_, _ = w.Write([]byte("ok"))
		return
	}

	w.WriteHeader(http.StatusOK)
	_, _ = w.Write([]byte("ok"))
}

func verifySquareSignature(body []byte, signature, signingKey, notificationURL string) bool {
	mac := hmac.New(sha256.New, []byte(signingKey))
	mac.Write([]byte(notificationURL))
	mac.Write(body)
	expected := base64.StdEncoding.EncodeToString(mac.Sum(nil))
	return hmac.Equal([]byte(signature), []byte(expected))
}

// squareWebhookData is the `data` envelope of a Square webhook v1 event. The
// affected object's id is at data.id; the full resource is nested at
// data.object.<type> (e.g. data.object.payment). Only the id is logged — the
// nested object can contain PII and is never echoed to the log.
type squareWebhookData struct {
	ID     string          `json:"id"`
	Type   string          `json:"type"`
	Object json.RawMessage `json:"object"`
}

// squareDisputePayload maps the Square Dispute fields this app records.
// Reference: https://developer.squareup.com/reference/square/objects/Dispute
type squareDisputePayload struct {
	ID              string                      `json:"id"`
	State           string                      `json:"state"`
	AmountMoney     *squareMoneyPayload         `json:"amount_money"`
	Reason          string                      `json:"reason"`
	DisputedPayment *squareDisputedPaymentField `json:"disputed_payment"`
}

type squareMoneyPayload struct {
	Amount   int64  `json:"amount"` // minor units (pence for GBP)
	Currency string `json:"currency"`
}

type squareDisputedPaymentField struct {
	PaymentID string `json:"payment_id"`
}

// squarePaymentPayload maps the Square Payment fields this app consumes.
type squarePaymentPayload struct {
	ID     string `json:"id"`
	Status string `json:"status"` // "APPROVED", "COMPLETED", "CANCELED", "FAILED", "PENDING"
	// ReferenceID / IdempotencyKey / AmountMoney feed the B1-support orphan
	// detection (detectOrphanedReplayCharge): Square's Payment object carries
	// them, and a sweep-minted duplicate charge preserves the origin row's
	// idempotency key / reference_id / amount (the sweep replays the stored
	// request verbatim).
	ReferenceID    string              `json:"reference_id"`
	IdempotencyKey string              `json:"idempotency_key"`
	AmountMoney    *squareMoneyPayload `json:"amount_money"`
}

// squareRefundPayload maps the Square Refund (PaymentRefund) fields this app
// consumes.
type squareRefundPayload struct {
	ID     string `json:"id"`
	Status string `json:"status"` // "PENDING", "COMPLETED", "FAILED"
}

// parseSquareObject unmarshals data.object.<type> into out. Returns false when
// the nested resource is absent (legacy envelope carrying only data.id).
func parseSquareObject(object json.RawMessage, key string, out any) bool {
	if len(object) == 0 {
		return false
	}
	var wrapper map[string]json.RawMessage
	if err := json.Unmarshal(object, &wrapper); err != nil {
		return false
	}
	raw, ok := wrapper[key]
	if !ok || len(raw) == 0 {
		return false
	}
	if err := json.Unmarshal(raw, out); err != nil {
		return false
	}
	return true
}

// squareMoneyToAmount converts a Square Money object (minor units) to an exact
// two-decimal string for the NUMERIC(10,2) columns. String formatting avoids
// float64 rounding artifacts for money.
func squareMoneyToAmount(m *squareMoneyPayload) string {
	if m == nil || m.Amount <= 0 {
		return "0.00"
	}
	return fmt.Sprintf("%d.%02d", m.Amount/100, m.Amount%100)
}

// squarePaymentStatusToLocal maps Square's payment state machine to the local
// payment_status enum. APPROVED/PENDING are NON-terminal (Square may still
// complete or void them), so they map to a zero local status and the caller
// leaves the row untouched — the same classification the stale-pending sweeps
// use (handlers/payments/sweep.go).
func squarePaymentStatusToLocal(status string) (string, bool) {
	switch status {
	case "COMPLETED":
		return "completed", true
	case "CANCELED", "FAILED":
		return "failed", true
	case "APPROVED", "PENDING":
		return "", false
	default:
		return "", false
	}
}

// squareDisputeStateToLocal maps Square's dispute state to the local
// disputes.status. Only the terminal resolutions move the row to won/lost;
// ACCEPTED (seller accepted the dispute) is a loss — the money is gone.
// Everything else (inquiries, evidence required, processing) stays open.
func squareDisputeStateToLocal(state string) string {
	switch state {
	case "WON":
		return "won"
	case "LOST", "ACCEPTED":
		return "lost"
	default:
		return "open"
	}
}

// findPaymentBySquareID resolves the local payment id and booking id for a
// Square payment id. Multiple local rows can share one Square charge id (e.g.
// a deposit + balance split); the most recent is used. The caller supplies a
// bounded context (webhookDBContext) so this post-dispatch DB work survives a
// client disconnect without holding a pool connection forever.
func findPaymentBySquareID(ctx context.Context, squarePaymentID string) (paymentID, bookingID string, ok bool) {
	if squarePaymentID == "" {
		return "", "", false
	}
	var pid string
	var bid *string
	err := db.Conn.QueryRow(ctx, `
		SELECT id, booking_id FROM payments
		WHERE square_payment_id = $1
		ORDER BY created_at DESC, id DESC
		LIMIT 1
	`, squarePaymentID).Scan(&pid, &bid)
	if err != nil {
		return "", "", false
	}
	if bid != nil {
		bookingID = *bid
	}
	return pid, bookingID, true
}

// findPaymentByDisputeID resolves the local payment (and its booking) recorded
// for a dispute row. Used by dispute.state.updated when the dispute row already
// exists but the webhook payload carries no resolvable Square payment id. The
// caller supplies a bounded context (webhookDBContext).
func findPaymentByDisputeID(ctx context.Context, squareDisputeID string) (paymentID, bookingID string) {
	var pid string
	var bid *string
	err := db.Conn.QueryRow(ctx, `
		SELECT d.payment_id, p.booking_id
		FROM disputes d
		JOIN payments p ON p.id = d.payment_id
		WHERE d.square_dispute_id = $1
	`, squareDisputeID).Scan(&pid, &bid)
	if err != nil {
		return "", ""
	}
	if bid != nil {
		bookingID = *bid
	}
	return pid, bookingID
}

// disputeNotificationID derives the deterministic admin_notifications id for an
// untracked dispute's critical_payment_log notification: 'D' + 11 lowercase hex
// chars of a SHA-256 over 'dispute-<square_dispute_id>'. generate_short_id
// (init-script.sql) only ever emits 12 lowercase hex chars
// (substr(encode(gen_random_bytes(6),'hex'),1,12)), so the uppercase 'D' prefix
// guarantees this can never collide with a DB-generated id. The id is stable
// per dispute, giving ON CONFLICT (id) DO NOTHING per-dispute idempotency.
func disputeNotificationID(squareDisputeID string) string {
	sum := sha256.Sum256([]byte("dispute-" + squareDisputeID))
	return "D" + hex.EncodeToString(sum[:])[:11]
}

// insertCriticalPaymentNotification surfaces a money event in the admin
// notification centre (reason='critical_payment_log'), the DB-backed stand-in
// for un-watched CRITICAL log lines (see ScanCriticalPaymentLogs in
// internal/jobs/cleanup.go). Dedup: one unacknowledged row per (reason,
// booking_id) — acknowledging re-arms it.
//
// NOTE: this is the WEBHOOK-specific variant of a same-named helper in the
// payments package (handlers/payments/sweep.go, insertCriticalPaymentNotification)
// with DIFFERENT semantics: that one has the signature (ctx, bookingID,
// userID *string), writes the user_id column, and dedups on (reason,
// booking_id, user_id); this one takes (ctx, bookingID, disputeID string),
// never writes user_id, and dedups on (reason, booking_id) or the
// deterministic per-dispute id. They share the admin_notifications table and
// the 'critical_payment_log' reason but serve different call paths (sweep vs
// webhook) — do not merge them.
//
// AUTHORITATIVE NOT EXISTS GUARD: the booking-scoped branch below is the
// canonical form of this dedup — `an.reason = 'critical_payment_log' AND
// an.booking_id IS NOT DISTINCT FROM $1 AND an.acknowledged_at IS NULL` (a
// notification blocks a re-notify until the admin acknowledges it, then a NEW
// event re-arms). sweep.go's insertCriticalPaymentNotification mirrors this
// predicate in shape (its copy applies the same NOT EXISTS/acknowledged_at
// IS NULL guard over (reason, booking_id, user_id) — an intentional
// user-scoping addition for sweep-originated events, documented on the sweep
// copy itself); if the guard ever changes, sweep.go must be updated to match.
//
// Untracked disputes (no local payment row, booking_id NULL) pass disputeID
// instead: each DISTINCT square dispute gets its OWN notification under the
// deterministic id (disputeNotificationID), so a second distinct chargeback is
// never suppressed by the first's (reason, NULL booking) row — and re-delivery
// of the same dispute is a no-op (ON CONFLICT (id) DO NOTHING). The
// booking-scoped NOT EXISTS guard does NOT apply to this path: it would
// collapse every untracked dispute onto one unacknowledged NULL-booking row.
// The caller supplies a bounded context (webhookDBContext).
func insertCriticalPaymentNotification(ctx context.Context, bookingID, disputeID string) {
	// Round 2 Loop B finding 1: apply the global cap to this insert site too
	// (the pre-check logs the suppression; the fold inside each INSERT enforces
	// it atomically so concurrent events cannot overshoot together). The
	// unacknowledged 'critical_payment_log' queue is shared by every insert
	// site in the codebase (sweep, webhook, account-erasure, jobs), so a flood
	// at any of them must not bury the single-operator notification centre.
	if adminnotify.CriticalLogsCapExceeded(ctx, db.Conn, "critical_payment_log") {
		log.Printf("[SQUARE-WEBHOOK] critical_payment_log admin notification suppressed (booking=%q dispute=%q) — %d unacknowledged rows at the cap; acknowledge outstanding notifications to re-arm", bookingID, disputeID, adminnotify.MaxUnacknowledgedCriticalLogs)
		return
	}
	if disputeID != "" {
		id := disputeNotificationID(disputeID)
		tag, err := db.Conn.Exec(ctx, `
			INSERT INTO admin_notifications (id, reason, booking_id, created_at)
			SELECT $1, 'critical_payment_log'::admin_notification_reason, NULL, NOW()
			WHERE (SELECT COUNT(*) FROM admin_notifications _an
			       WHERE _an.reason = 'critical_payment_log'
			         AND _an.acknowledged_at IS NULL) < $2
			ON CONFLICT (id) DO NOTHING
		`, id, adminnotify.MaxUnacknowledgedCriticalLogs)
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to insert critical_payment_log admin notification: %v", err)
			return
		}
		if tag.RowsAffected() > 0 {
			log.Printf("[SQUARE-WEBHOOK] Inserted critical_payment_log admin notification (dispute_id=%s, booking_id=NULL)", disputeID)
		}
		return
	}
	var bid any
	if bookingID != "" {
		bid = bookingID
	}
	tag, err := db.Conn.Exec(ctx, `
		INSERT INTO admin_notifications (reason, booking_id, created_at)
		SELECT 'critical_payment_log'::admin_notification_reason, $1, NOW()
		WHERE NOT EXISTS (
			SELECT 1 FROM admin_notifications an
			WHERE an.reason = 'critical_payment_log'
			  AND an.booking_id IS NOT DISTINCT FROM $1
			  AND an.acknowledged_at IS NULL
		)
		AND (SELECT COUNT(*) FROM admin_notifications _an
		     WHERE _an.reason = 'critical_payment_log'
		       AND _an.acknowledged_at IS NULL) < $2
	`, bid, adminnotify.MaxUnacknowledgedCriticalLogs)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to insert critical_payment_log admin notification: %v", err)
		return
	}
	if tag.RowsAffected() > 0 {
		log.Printf("[SQUARE-WEBHOOK] Inserted critical_payment_log admin notification (booking_id=%s)", bookingID)
	}
}

// unknownEventNotificationID derives the deterministic admin_notifications id
// for an unhandled money-family/truly-unknown webhook event's
// critical_payment_log notification: 'U' + 11 lowercase hex chars of a SHA-256
// over 'unknown-event-<event_id>'. Mirrors disputeNotificationID (which uses an
// uppercase 'D' prefix): the two are intentionally parallel — same
// prefix + hex(sha256(input))[:11] scheme (44 bits), DIFFERENT prefixes, so a
// dispute id and an unknown-event id can never collide even on identical hash
// input. generate_*_id (init-script.sql) only ever emits 12
// lowercase hex chars, so the uppercase 'U' prefix guarantees no collision with
// a DB-generated id. The id is stable per event_id, giving
// ON CONFLICT (id) DO NOTHING per-event idempotency across redeliveries.
func unknownEventNotificationID(eventID string) string {
	sum := sha256.Sum256([]byte("unknown-event-" + eventID))
	return "U" + hex.EncodeToString(sum[:])[:11]
}

// insertUnknownEventNotification raises a critical_payment_log admin
// notification for a money-family/truly-unknown webhook event the handler
// refuses to ack (501). Without it the event is retried by Square for ~24h and
// then silently dropped with only a log line. The notification is deduped by
// its deterministic id (unknownEventNotificationID) and lives in a SEPARATE
// table — no square_webhook_events dedup row is written on the 501 path, so
// Square keeps retrying the event exactly as before. Best-effort: an insert
// failure is logged, never a dispatch error (the 501 is already the response).
// The caller supplies a bounded context (webhookDBContext).
func insertUnknownEventNotification(ctx context.Context, eventType, eventID string) {
	// Round 2 Loop B finding 1: same atomic global cap as every other
	// 'critical_payment_log' insert site — the pre-check logs the suppression,
	// the INSERT fold enforces it atomically.
	if adminnotify.CriticalLogsCapExceeded(ctx, db.Conn, "critical_payment_log") {
		log.Printf("[SQUARE-WEBHOOK] critical_payment_log admin notification suppressed for unhandled event %q (event_id=%s) — %d unacknowledged rows at the cap; acknowledge outstanding notifications to re-arm", eventType, eventID, adminnotify.MaxUnacknowledgedCriticalLogs)
		return
	}
	id := unknownEventNotificationID(eventID)
	tag, err := db.Conn.Exec(ctx, `
		INSERT INTO admin_notifications (id, reason, booking_id, created_at)
		SELECT $1, 'critical_payment_log'::admin_notification_reason, NULL, NOW()
		WHERE (SELECT COUNT(*) FROM admin_notifications _an
		       WHERE _an.reason = 'critical_payment_log'
		         AND _an.acknowledged_at IS NULL) < $2
		ON CONFLICT (id) DO NOTHING
	`, id, adminnotify.MaxUnacknowledgedCriticalLogs)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to insert critical_payment_log admin notification for unhandled event %q (event_id=%s): %v", eventType, eventID, err)
		return
	}
	if tag.RowsAffected() > 0 {
		log.Printf("[SQUARE-WEBHOOK] Inserted critical_payment_log admin notification for unhandled event type %q (event_id=%s)", eventType, eventID)
	}
}

// markPaymentFailed flips a payment to 'failed' after a lost dispute — the
// money was charged back, so the row must not read as collected. 'refunded'
// rows are left alone (the money was returned by refund, not charged back).
// The caller supplies a bounded context (webhookDBContext).
func markPaymentFailed(ctx context.Context, paymentID string) error {
	if paymentID == "" {
		return nil
	}
	_, err := db.Conn.Exec(ctx,
		"UPDATE payments SET status = 'failed', updated_at = NOW() WHERE id = $1 AND status IN ('pending', 'completed')",
		paymentID)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to mark payment %s failed after lost dispute: %v", paymentID, err)
		return err
	}
	return nil
}

// squareChargeKnown reports whether ANY local row — a payments row OR a
// till_sales row — carries the Square payment id, whatever its status. The
// pending-only reconcile in reconcileCompletedPayments matches zero payments
// rows both when no row exists at all and when the row already settled (e.g. a
// completed webhook replay of an already-'completed' charge); a till_sales row
// counts as known too because a gift-card/retail till charge has no payments
// row at all and its completion event must still be acked (the till_sales
// reconcile runs after). This existence check distinguishes "known — a plain
// no-op replay" from "no row at all — the signature of an unresolved unknown
// charge". A DB failure here propagates so the caller rejects the event and
// Square retries.
func squareChargeKnown(ctx context.Context, squarePaymentID string) (bool, error) {
	var known bool
	err := db.Conn.QueryRow(ctx, `
		SELECT EXISTS(SELECT 1 FROM payments WHERE square_payment_id = $1)
		    OR EXISTS(SELECT 1 FROM till_sales WHERE square_payment_id = $1)
	`, squarePaymentID).Scan(&known)
	if err != nil {
		return false, err
	}
	return known, nil
}

// squareRefundKnown reports whether ANY local refunds row carries the Square
// refund id, whatever its status. Used by handleRefundUpdated to distinguish a
// plain no-op replay (row exists, already in the target status) from a refund
// event arriving before the local row was created — the latter must NOT be
// acked (the refund row can be created in the same transaction as the charge,
// and acking would drop the terminal settlement events forever). A DB failure
// propagates so the caller rejects the event and Square retries.
func squareRefundKnown(ctx context.Context, squareRefundID string) (bool, error) {
	var known bool
	err := db.Conn.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM refunds WHERE square_refund_id = $1)`, squareRefundID).Scan(&known)
	if err != nil {
		return false, err
	}
	return known, nil
}

// findPendingByOrphanKeys locates the pending ORIGIN row of a likely
// sweep-minted duplicate charge: the row that shares the replayed charge's
// idempotency key (primary — exact, because Square returns the key on the
// Payment object and payments.idempotency_key is UNIQUE) or, failing that,
// the pending row whose booking/gift-card reference AND amount match the
// payload's reference_id/amount_money (the sweep replays the stored snapshot
// verbatim, preserving both). 'pending' rows WITHOUT a square_payment_id are
// the population the keyed stale-pending sweep replays (sweep.go), so a match
// is the sweep-minted duplicate's origin. 'failed' rows are ALSO matched on
// the idempotency-key path: a prior webhook delivery (or the sweep's B1
// blind-fail) already marked the origin failed, and a re-delivery of the same
// orphan event must find it again to ack idempotently instead of treating the
// resolved orphan as a fresh unknown charge (round-8 fix 3).
//
// AGE GATE (webhook-vs-response race): the candidates are additionally limited
// to rows old enough to have actually been replayed by the sweep
// (created_at <= NOW() - payments.SweepKeyedReplayAge()). The sweep only
// replays keyed rows past that age (sweep.go stalePendingKeyedAge), so a fresh
// pending row can never be a sweep-minted duplicate's origin — it is a legit
// charge whose completion webhook raced the handler's own square_payment_id
// write (response loss), and marking it failed would kill the customer's real
// payment. When the gate blocks a match the caller leaves the row pending
// (critical-log), never fails it — the sweep will reconcile it later.
func findPendingByOrphanKeys(ctx context.Context, payment squarePaymentPayload) (paymentID, bookingID string, found bool, err error) {
	replayEligibleSince := clock.Now().Add(-payments.SweepKeyedReplayAge())
	if payment.IdempotencyKey != "" {
		var pid string
		var bid *string
		err := db.Conn.QueryRow(ctx, `
			SELECT id, booking_id FROM payments
			WHERE status IN ('pending', 'failed') AND idempotency_key = $1 AND square_payment_id IS NULL
			  AND created_at <= $2
			ORDER BY created_at DESC, id DESC
			LIMIT 1
		`, payment.IdempotencyKey, replayEligibleSince).Scan(&pid, &bid)
		if err == nil {
			if bid != nil {
				bookingID = *bid
			}
			return pid, bookingID, true, nil
		}
		if !errors.Is(err, pgx.ErrNoRows) {
			return "", "", false, err
		}
	}
	// reference_id fallback: booking charges carry the booking id as
	// reference_id; a gift-card-linked payment carries the card code. The
	// amount guard stops a coincidental reference match (a pending row for the
	// same booking at a different amount) from being mistaken for the origin.
	if payment.ReferenceID != "" && payment.AmountMoney != nil && payment.AmountMoney.Amount > 0 {
		amount := float64(payment.AmountMoney.Amount) / 100.0
		var pid string
		var bid *string
		err := db.Conn.QueryRow(ctx, `
			SELECT id, booking_id FROM payments
			WHERE status = 'pending' AND square_payment_id IS NULL
			  AND ABS(amount - $2) < 0.005
			  AND (booking_id = $1 OR gift_card_id = $1)
			  AND created_at <= $3
			ORDER BY created_at DESC, id DESC
			LIMIT 1
		`, payment.ReferenceID, amount, replayEligibleSince).Scan(&pid, &bid)
		if err == nil {
			if bid != nil {
				bookingID = *bid
			}
			return pid, bookingID, true, nil
		}
		if !errors.Is(err, pgx.ErrNoRows) {
			return "", "", false, err
		}
	}
	return "", "", false, nil
}

// orphanReplayChargeNotificationID derives the deterministic admin_notifications
// id for an orphaned sweep-minted duplicate charge's critical_payment_log
// notification: 'O' + 11 lowercase hex chars of a SHA-256 over
// 'orphan-replay-<payment_id>'. Mirrors disputeNotificationID ('D') and
// unknownEventNotificationID ('U'): same prefix + hex(sha256(input))[:11]
// scheme with a distinct uppercase prefix, so the three id spaces can never
// collide and the uppercase prefix guarantees no collision with a DB-generated
// id (generate_*_id emits 12 lowercase hex chars). The id is stable per ORIGIN
// payment row, so ON CONFLICT (id) DO NOTHING keeps re-deliveries and
// re-notifications of the same orphan charge to one row.
func orphanReplayChargeNotificationID(paymentID string) string {
	sum := sha256.Sum256([]byte("orphan-replay-" + paymentID))
	return "O" + hex.EncodeToString(sum[:])[:11]
}

// insertOrphanReplayChargeNotification surfaces an orphaned sweep-minted
// duplicate charge in the admin notification centre (reason
// 'critical_payment_log'), deduped by the deterministic per-origin-row id so
// repeated deliveries of the same orphan charge's event never add a second
// row. booking_id is set when the origin payment row has one, giving the owner
// a booking to act from. Best-effort: an insert failure is logged, never a
// dispatch error.
func insertOrphanReplayChargeNotification(ctx context.Context, paymentID, bookingID string) {
	if paymentID == "" {
		return
	}
	// Round 2 Loop B finding 1: same atomic global cap as every other
	// 'critical_payment_log' insert site.
	if adminnotify.CriticalLogsCapExceeded(ctx, db.Conn, "critical_payment_log") {
		log.Printf("[SQUARE-WEBHOOK] orphaned-replay admin notification suppressed (origin payment=%s) — %d unacknowledged rows at the cap; acknowledge outstanding notifications to re-arm", paymentID, adminnotify.MaxUnacknowledgedCriticalLogs)
		return
	}
	id := orphanReplayChargeNotificationID(paymentID)
	var bid any
	if bookingID != "" {
		bid = bookingID
	}
	tag, err := db.Conn.Exec(ctx, `
		INSERT INTO admin_notifications (id, reason, booking_id, created_at)
		SELECT $1, 'critical_payment_log'::admin_notification_reason, $2, NOW()
		WHERE (SELECT COUNT(*) FROM admin_notifications _an
		       WHERE _an.reason = 'critical_payment_log'
		         AND _an.acknowledged_at IS NULL) < $3
		ON CONFLICT (id) DO NOTHING
	`, id, bid, adminnotify.MaxUnacknowledgedCriticalLogs)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to insert orphaned-replay admin notification: %v", err)
		return
	}
	if tag.RowsAffected() > 0 {
		log.Printf("[SQUARE-WEBHOOK] Inserted orphaned-replay-charge admin notification (origin payment=%s, booking=%q)", paymentID, bookingID)
	}
}

// detectOrphanedReplayCharge handles a COMPLETED payment.updated event whose
// square_payment_id matches NO local payments row — the signature of an
// ORPHANED SWEEP-MINTED duplicate charge (B1-support). When the stale-pending
// sweep replays a pending row's stored idempotency key against Square and the
// key has expired, Square creates a NEW charge under that same key; the new
// charge's payment.updated event arrives here with no local row of its own,
// while the pending ORIGIN row (the one the sweep replayed) still exists and
// shares the replayed idempotency key — and, for booking/gift-card charges,
// the same reference_id and amount.
//
// Action (the webhook half of B1): mark the origin row 'failed' — it is NOT
// the charge that completed, so rescuing it to 'completed' would hide the
// duplicate behind the original and rescuing by square_payment_id is
// impossible (the orphan has no row) — and surface a deduped 'orphaned replay
// charge detected' admin notification. The AUTO-REFUND of the orphan charge is
// the SWEEP's job (sweep.go, the money agent's B1 fix): this path NEVER issues
// a refund and must never be turned into one; it detects + notifies + settles
// the origin row so the sweep does not blind-fail or double-rescue it later.
func detectOrphanedReplayCharge(ctx context.Context, payment squarePaymentPayload) error {
	originID, bookingID, found, err := findPendingByOrphanKeys(ctx, payment)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Orphan-replay origin lookup failed for square payment %s: %v", payment.ID, err)
		return err
	}
	if !found {
		// Round-8 fix 3: a COMPLETED payment that matches NO local row and NO
		// pending origin row by idempotency key/reference_id is a genuinely
		// unknown charge this server never created — possibly a duplicate minted
		// outside the sweep's replay, or a charge whose local row was erased.
		// Acking it (200 + dedup row) would drop the event forever, hiding money
		// the merchant holds. Return a retryable error so the caller responds
		// 503: Square re-delivers with backoff, and its retry budget bounds the
		// retries (the stale-pending sweeps remain the eventual backstop).
		log.Printf("[SQUARE-WEBHOOK] payment.updated: COMPLETED square payment %s matches no local row and no pending origin row by idempotency key/reference_id — returning 503 so Square retries (unresolved unknown money event)", payment.ID)
		return fmt.Errorf("COMPLETED square payment %s matches no local row and no pending origin row — unresolved unknown money event, rejecting so Square retries", payment.ID)
	}
	// B1-EVIDENCE GATE: only treat the origin as a sweep-minted duplicate when
	// the sweep actually minted+attempted to refund it (b1_attempts > 0 or a
	// refunds row with the sweep-duplicate reason). Without that evidence a
	// COMPLETED payment is far more likely the ORIGINAL charge whose completion
	// webhook was delayed past the age gate — marking the origin failed would
	// kill a customer's real payment. Leave it pending: the keyed sweep rescues
	// the original 'completed' or triggers B1 if a duplicate was truly minted.
	var b1Evidence bool
	if err := db.Conn.QueryRow(ctx, `
		SELECT EXISTS(
			SELECT 1 FROM payments
			WHERE id = $1 AND b1_attempts > 0
			UNION ALL
			SELECT 1 FROM refunds
			WHERE payment_id = $1 AND reason = 'duplicate charge — sweep replay'
		)
	`, originID).Scan(&b1Evidence); err != nil {
		log.Printf("[SQUARE-WEBHOOK] Orphan-replay B1-evidence lookup failed for origin payment %s: %v", originID, err)
		return err
	}
	if !b1Evidence {
		log.Printf("[SQUARE-WEBHOOK] payment.updated: COMPLETED square payment %s matches pending origin %s by idempotency key but NO B1 evidence (b1_attempts=0, no sweep-duplicate refund) — leaving origin pending for the sweep to reconcile (delayed legit completion or the sweep has not replayed yet); not marking failed", payment.ID, originID)
		return nil
	}
	tag, err := db.Conn.Exec(ctx,
		`UPDATE payments SET status = 'failed', updated_at = NOW() WHERE id = $1 AND status = 'pending'`,
		originID)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to mark orphaned-replay origin payment %s failed: %v", originID, err)
		return err
	}
	if tag.RowsAffected() > 0 {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: COMPLETED square payment %s (orphaned sweep-minted duplicate) — origin pending payment %s marked failed; admin notified", payment.ID, originID)
	} else {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: COMPLETED square payment %s (orphaned sweep-minted duplicate) — origin payment %s already resolved; admin notified", payment.ID, originID)
	}
	insertOrphanReplayChargeNotification(ctx, originID, bookingID)
	return nil
}

// handlePaymentUpdated reconciles a Square Payment state change against the
// local payments row (real-time counterpart to the stale-pending sweep). The
// Square id is logged, never the payload (PII). Idempotent: the UPDATE is a
// no-op when the local status already matches, and event_id dedup prevents
// re-entry at the handler level. A non-nil error means dispatch failed and the
// caller must NOT commit the dedup row (Square retries). All DB work runs on a
// bounded Background context (webhookDBContext): a client disconnect must not
// cancel the state mutation, and a hung DB call must not hold the pool forever.
func handlePaymentUpdated(data json.RawMessage) error {
	ctx, cancel := webhookDBContext()
	defer cancel()

	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		// Parse failure on a known money event: the caller returns 5xx without
		// committing the dedup row (errWebhookParseFailure), so Square
		// re-delivers and the payment state change is not permanently lost.
		return fmt.Errorf("payment event data envelope: %v: %w", err, errWebhookParseFailure)
	}
	if len(env.Object) == 0 {
		// Legacy envelope carrying only data.id — no nested payment object to
		// reconcile, so there is nothing to apply; acknowledge as received.
		log.Printf("[SQUARE-WEBHOOK] payment.updated received (data.id=%s)", env.ID)
		return nil
	}
	var payment squarePaymentPayload
	if !parseSquareObject(env.Object, "payment", &payment) || payment.ID == "" || payment.Status == "" {
		// A payment object is present but unusable (malformed, or missing the
		// id/status reconciliation needs): cannot apply money state — retry.
		return fmt.Errorf("payment.updated payload for data.id=%q missing/invalid payment object (id=%q status=%q): %w", env.ID, payment.ID, payment.Status, errWebhookParseFailure)
	}
	localStatus, terminal := squarePaymentStatusToLocal(payment.Status)
	if !terminal {
		log.Printf("[SQUARE-WEBHOOK] payment.updated: square payment %s status %q is non-terminal — no local state change", payment.ID, payment.Status)
		return nil
	}
	if localStatus == "completed" {
		// CRITICAL (round-8): a COMPLETED charge must be gated against the
		// booking BEFORE any pending row is promoted. reconcileCompletedPayments
		// re-reads every pending payments row carrying this square payment id
		// inside a transaction and, per row, re-checks the booking status FOR
		// UPDATE: a cancelled/lapsed/no-show booking refuses the completion (row
		// marked failed + automatic cancellation refund row + critical
		// notification), a payable booking is completed with the split/VAT/
		// deposit-promotion/fully-paid-completion side-effects the sweep rescue
		// applies, and a booking-less row (gift-card purchase) is left pending
		// for the same-key retry that delivers the card (C6). A COMPLETED event
		// matching no local row at all is the unresolved-unknown case: the
		// orphaned-replay detection runs (B1) and a genuinely unknown charge is
		// NOT acked — the returned error makes the caller respond 503 so Square
		// retries instead of the event being dropped forever.
		if err := reconcileCompletedPayments(ctx, payment); err != nil {
			return err
		}
	} else {
		// A Square FAILED/CANCELED event on a still-pending row. Only 'pending'
		// rows are candidates for a terminal transition — the same conservative
		// rule the stale-pending sweeps use. A webhook for an already settled
		// row (Square fires payment.updated for ANY field change, e.g. fee
		// recalculation on a fully-refunded charge) must never revert a terminal
		// status like 'refunded' back to 'completed'.
		tag, err := db.Conn.Exec(ctx,
			`UPDATE payments SET status = $1, updated_at = NOW() WHERE square_payment_id = $2 AND status = 'pending'`,
			localStatus, payment.ID)
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to update payment %s to status %s: %v", payment.ID, localStatus, err)
			return err
		}
		if tag.RowsAffected() > 0 {
			log.Printf("[SQUARE-WEBHOOK] payment.updated: square payment %s → local status %s", payment.ID, localStatus)
		}
		// A definitively failed charge (Square FAILED/CANCELED) claws back the
		// gift-card funding those pending sales added, exactly like the
		// stale-pending sweep (handlers/payments/sweep.go); an ambiguous status
		// never reaches here.
		if localStatus == "failed" {
			return clawbackFailedTillSales(ctx, payment.ID)
		}
	}
	// A Square charge can also map to a till_sales row (online gift-card
	// purchase, retail at the till) — reconcile those too. Same pending-only
	// guard: never revert a terminal till-sale status.
	tsTag, err := db.Conn.Exec(ctx,
		`UPDATE till_sales SET status = $1, updated_at = NOW() WHERE square_payment_id = $2 AND status = 'pending'`,
		localStatus, payment.ID)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to reconcile till_sales for square payment %s: %v", payment.ID, err)
		return err
	}
	if tsTag.RowsAffected() > 0 {
		log.Printf("[SQUARE-WEBHOOK] payment.updated: reconciled %d till_sale(s) for square payment %s → status %s", tsTag.RowsAffected(), payment.ID, localStatus)
	}
	return nil
}

// webhookPendingCharge is a local pending payments row awaiting a COMPLETED
// Square reconcile.
type webhookPendingCharge struct {
	id            string
	bookingID     *string
	amountPence   int64
	paymentType   string
	paymentMethod string
	idemKey       sql.NullString
	createdBy     *string
}

// reconcileCompletedPayments applies a COMPLETED Square payment to every local
// pending payments row carrying that square_payment_id. It mirrors the
// stale-pending sweep's gateStalePaymentRescueOnBooking +
// rescueStaleRowCompletedTx (handlers/payments/sweep.go): each pending row's
// booking is re-read FOR UPDATE inside one transaction, and
//
//   - a cancelled/lapsed/no-show booking refuses the completion: the row is
//     marked FAILED (never completed — the cancellation refund path computes
//     refunds from completed payments and would miss it, charging a customer
//     with NO automatic refund, F3), an M2 auto-refund row for the full
//     stranded charge is inserted (same shape and origin 'cancellation' as
//     ProcessCancellationRefundTx / the sweep's gate) so the pending-refund
//     sweep issues the Square refund, and a critical admin notification is
//     raised;
//   - a payable booking is completed exactly like the sweep rescue / live path:
//     the row is promoted to 'completed', re-split (any overflow carved as a
//     tip), VAT applied, a deposit-paid pending_release booking promoted to
//     'confirmed', and a fully-paid booking completed with the
//     loyalty/campaign side-effects (ApplyBookingCompletionSideEffects);
//   - a booking-less row (gift-card purchase) is LEFT pending — the same-key
//     retry delivers the card, never the webhook (C6).
//
// A COMPLETED event matching no local pending row at all is the unknown-event
// case: a settled row existing (payments or till_sales) means a plain no-op
// replay and is acked, otherwise the orphaned-replay detection runs (B1) and a
// genuinely unknown charge returns a retryable error (the caller responds 503,
// no dedup row, so Square re-delivers instead of the event being dropped
// forever). A non-nil error always means the caller must NOT commit the dedup
// row. Idempotent: every UPDATE is status='pending'-guarded and the refund row
// uses the deterministic paymentID+"-square-"+pence key.
func reconcileCompletedPayments(ctx context.Context, payment squarePaymentPayload) error {
	tx, err := db.Conn.Begin(ctx)
	if err != nil {
		return err
	}
	defer func() {
		if err := tx.Rollback(ctx); err != nil && !errors.Is(err, pgx.ErrTxClosed) {
			log.Printf("[SQUARE-WEBHOOK] Failed to roll back completed-payment reconcile tx for square payment %s: %v", payment.ID, err)
		}
	}()

	rows, err := tx.Query(ctx, `
		SELECT id, booking_id, amount, payment_type, payment_method, idempotency_key, created_by
		FROM payments
		WHERE square_payment_id = $1 AND status = 'pending'
		ORDER BY created_at DESC, id DESC
	`, payment.ID)
	if err != nil {
		return err
	}
	// refusedBookings collects the bookings whose stranded charges were refused
	// (row failed + auto-refund inserted) so their critical admin notifications
	// can be raised AFTER the tx commits — an in-tx notification's
	// admin_notifications FK check on the still-locked booking row would wait on
	// our own uncommitted xmax (the sweep's gate notifies after commit for the
	// same reason).
	var refusedBookings []string
	var pending []webhookPendingCharge
	for rows.Next() {
		var pr webhookPendingCharge
		var amount float64
		if err := rows.Scan(&pr.id, &pr.bookingID, &amount, &pr.paymentType, &pr.paymentMethod, &pr.idemKey, &pr.createdBy); err != nil {
			rows.Close()
			return err
		}
		pr.amountPence = int64(math.Round(amount * 100))
		pending = append(pending, pr)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return err
	}

	if len(pending) == 0 {
		// No local pending payments row carries this square payment id.
		// Commit the read-only tx before the out-of-tx existence checks.
		if err := tx.Commit(ctx); err != nil {
			return err
		}
		known, kErr := squareChargeKnown(ctx, payment.ID)
		if kErr != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to check whether square payment %s is known: %v", payment.ID, kErr)
			return kErr
		}
		if known {
			// A settled row (payments or till_sales) exists — a plain no-op
			// replay of a known charge, left untouched.
			return nil
		}
		// B1-support: hunt for an ORPHANED SWEEP-MINTED duplicate (the
		// stale-pending sweep replayed a stored idempotency key against an
		// expired key and Square landed a NEW charge with no local row). A
		// resolved replay (origin found + B1 evidence) returns nil; a
		// genuinely unknown payment returns a retryable error.
		return detectOrphanedReplayCharge(ctx, payment)
	}

	for i := range pending {
		pr := pending[i]
		if pr.bookingID == nil {
			// C6: a booking-less row is a gift-card purchase. The same-key
			// retry delivers the card through the synchronous purchase path —
			// never auto-complete it here (the charge would be recorded while
			// the card was never minted). Leave pending; the dedup row still
			// commits and the sync retry flips the row when it delivers.
			log.Printf("[SQUARE-WEBHOOK] payment.updated: square payment %s COMPLETED for booking-less row %s (gift-card purchase) — leaving pending for the same-key retry to deliver the card (C6)", payment.ID, pr.id)
			continue
		}
		// Re-read the booking status FOR UPDATE so the decision serializes
		// against a concurrent cancellation (mirrors gateStalePaymentRescueOnBooking).
		var status string
		if err := tx.QueryRow(ctx, `SELECT status FROM bookings WHERE id = $1 FOR UPDATE`, *pr.bookingID).Scan(&status); err != nil {
			// Booking gone or unreadable: the money state is unknown. Never
			// complete on an unknown state — a completed payment on a vanished
			// booking would strand the charge outside the refund system.
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: Square reports payment %s COMPLETED but re-reading booking %s status failed (%v) — leaving row %s pending — MANUAL RECONCILIATION REQUIRED", payment.ID, *pr.bookingID, err, pr.id)
			continue
		}
		if !webhookBookingStatusAllowsCompleted(status) {
			// Cancelled / lapsed / no-show booking: completing the payment
			// would charge a customer for a booking the cancellation flow
			// already closed, with NO automatic refund (F3). Mark the row
			// FAILED so the same-key retry can never reuse it, and auto-create
			// the M2 refund row for the full stranded charge. The critical
			// notification is raised AFTER the tx commits (it cannot run inside
			// the tx: its admin_notifications FK check on the locked booking row
			// would wait on our own uncommitted xmax — the sweep's gate notifies
			// after commit for the same reason, sweep.go).
			if webhookFailStrandedCharge(ctx, tx, pr, status, payment.ID) {
				log.Printf("[SQUARE-WEBHOOK] CRITICAL: square payment %s is COMPLETED but booking %s is %q — payment %s marked FAILED instead of completed; an automatic refund row was created for the stranded charge — verify the Square refund settles", payment.ID, *pr.bookingID, status, pr.id)
				refusedBookings = append(refusedBookings, *pr.bookingID)
			}
			continue
		}
		// Payable booking — promote the pending row to completed and run the
		// split/VAT/completion side-effects exactly like the sweep rescue.
		//
		// R9 ordering guarantee (webhook vs. the synchronous saved-card path,
		// handlers.go CreateBookingPayment): both paths contend on the SAME
		// booking row — this reconcile re-reads the booking FOR UPDATE above,
		// the sync path's postChargeRecheck takes the identical lock — and the
		// payment row's status='pending' is the single mutual-exclusion point.
		// Exactly one of them can win the guarded flip:
		//
		//   - sync path commits first: this SELECT (status='pending' filter
		//     above) finds no row at all and the event is acked as a plain
		//     known-charge replay (squareChargeKnown); even if the row were
		//     somehow still selected, this guarded UPDATE returns
		//     RowsAffected()==0 and the side-effects are skipped;
		//   - webhook wins the flip: the guarded UPDATE succeeds and the
		//     side-effects run HERE, inside this tx; the sync path's own
		//     status='pending'-guarded completion (added in the same fix) then
		//     no-ops and never re-runs its side-effects.
		//
		// The side-effects therefore run at most once, by whichever path won the
		// flip — never twice — so the split rows (UNIQUE idempotency_key, e.g.
		// "<key>-split-1") can never be minted by both paths.
		tag, err := tx.Exec(ctx, `
			UPDATE payments SET status = 'completed', updated_at = NOW()
			WHERE id = $1 AND status = 'pending'
		`, pr.id)
		if err != nil {
			return err
		}
		if tag.RowsAffected() == 0 {
			// Already resolved concurrently (the sync path won the flip) —
			// nothing to do.
			continue
		}
		log.Printf("[SQUARE-WEBHOOK] payment.updated: square payment %s → local status completed (row %s)", payment.ID, pr.id)
		webhookApplyCompletedPaymentRecords(ctx, tx, pr, payment.ID)
		// The webhook won the flip, so it owns the side-effects: verify they
		// actually landed inside this tx instead of silently claiming a
		// completion whose bookkeeping was skipped.
		webhookVerifyCompletionSideEffects(ctx, tx, pr)
	}

	if err := tx.Commit(ctx); err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to commit completed-payment reconcile for square payment %s: %v", payment.ID, err)
		return err
	}
	// Raise the refused-booking critical notifications AFTER the commit — the
	// booking row lock is released now, so the notification's FK check on it
	// cannot wait on our own transaction.
	for _, bookingID := range refusedBookings {
		insertCriticalPaymentNotification(ctx, bookingID, "")
	}
	return nil
}

// webhookBookingStatusAllowsCompleted reports whether a charge that already
// went through Square can still be recorded as a completed payment. It mirrors
// the payments package's bookingStatusAllowsCompletedPayment (handlers.go) —
// the exact predicate the sweep's gate uses — replicated here because the
// payments package is mid-edit by another agent and must not be modified. A
// booking that legitimately completed ('completed') must still accept the
// recorded payment; a cancelled, lapsed, or no-show booking must NOT — the
// money would bypass the cancellation refund system.
func webhookBookingStatusAllowsCompleted(status string) bool {
	switch status {
	case "confirmed", "pending", "pending_release", "in_progress", "completed":
		return true
	default:
		return false
	}
}

// webhookFailStrandedCharge marks a pending payment row FAILED on a
// cancelled/lapsed/no-show booking whose charge already completed at Square and
// auto-creates the M2 cancellation refund row for the full stranded charge.
// Mirrors gateStalePaymentRescueOnBooking's refused branch (sweep.go). Returns
// true when the row was actually refused (still pending); false when it was
// already resolved concurrently. The critical admin notification is NOT raised
// here — the caller raises it after the tx commits (an in-tx notification's
// admin_notifications FK check on the locked booking row would wait on our own
// uncommitted xmax).
func webhookFailStrandedCharge(ctx context.Context, tx pgx.Tx, pr webhookPendingCharge, bookingStatus, squarePaymentID string) bool {
	tag, err := tx.Exec(ctx, `
		UPDATE payments SET status = 'failed', updated_at = NOW()
		WHERE id = $1 AND status = 'pending'
	`, pr.id)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: Square payment %s is COMPLETED but booking %s is %q — marking the row failed errored (%v) — MANUAL RECONCILIATION REQUIRED", squarePaymentID, *pr.bookingID, bookingStatus, err)
		return false
	}
	if int(tag.RowsAffected()) == 0 {
		// Already resolved concurrently — nothing left to refuse.
		return false
	}
	if pr.amountPence > 0 {
		// M2: a refund row for the full stranded charge. Same shape and origin
		// ('cancellation') ProcessCancellationRefundTx records, so the pending
		// Square refund sweep aggregates and issues it at Square. The
		// deterministic key mirrors the cancellation path (paymentID + "-square-"
		// + amount pence) and the UNIQUE conflict guard makes a re-run
		// idempotent.
		if _, rfErr := tx.Exec(ctx, `
			INSERT INTO refunds (payment_id, booking_id, amount, status, reason, idempotency_key, created_by, created_at, origin)
			VALUES ($1, $2, $3, 'pending', $4, $5, $6, NOW(), 'cancellation')
			ON CONFLICT (idempotency_key) DO NOTHING
		`, pr.id, *pr.bookingID, float64(pr.amountPence)/100.0,
			"Stranded charge on cancelled booking "+bookingStatus+" — auto-refunded by webhook",
			pr.id+"-square-"+strconv.FormatInt(pr.amountPence, 10), pr.createdBy); rfErr != nil {
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: square payment %s (row %s) is COMPLETED but creating its auto-refund row errored (%v) — MANUAL RECONCILIATION REQUIRED: refund the charge manually", squarePaymentID, pr.id, rfErr)
		}
	}
	return true
}

// webhookApplyCompletedPaymentRecords runs the post-completion bookkeeping for
// a payable-booking payment the webhook just promoted to 'completed': re-split
// the charge (any overflow carved as its own tip record), apply VAT, promote a
// deposit-paid pending_release booking to 'confirmed', and complete a fully-paid
// booking with the loyalty/campaign side-effects. It mirrors the sweep rescue's
// buildStaleRescueRecords + applyStaleRescueRecords (sweep.go) and the live
// post-charge path (handlers.go). All work runs inside the caller's reconcile
// transaction; a bookkeeping failure is logged and never aborts the money
// mutation (the charge already completed at Square — never worse than the
// pre-fix minimal flip).
func webhookApplyCompletedPaymentRecords(ctx context.Context, tx pgx.Tx, pr webhookPendingCharge, squarePaymentID string) {
	// A tip-type row is never re-split (a tip can never fully pay a booking).
	if pr.paymentType == "tip" {
		return
	}
	info, err := payments.NewPaymentService().GetBookingPaymentInfo(ctx, *pr.bookingID)
	if err != nil || info == nil {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: failed to load booking info for completion of payment %s (booking %s): %v — row completed un-split; manual reconciliation recommended", pr.id, *pr.bookingID, err)
		return
	}
	amount := float64(pr.amountPence) / 100.0
	spID := squarePaymentID
	primary := payments.PaymentRecord{
		BookingID:       *pr.bookingID,
		PaymentType:     pr.paymentType,
		PaymentMethod:   pr.paymentMethod,
		Status:          "completed",
		Amount:          amount,
		SquarePaymentID: &spID,
		CreatedBy:       pr.createdBy,
		CreatedAt:       clock.Now(),
		UpdatedAt:       clock.Now(),
	}
	if pr.idemKey.Valid {
		k := pr.idemKey.String
		primary.IdempotencyKey = &k
	}
	records, splitErr := webhookBuildSplitRecords(primary, pr.paymentType, info, amount)
	if splitErr != nil {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: buildSplitRecords rejected the completion split for payment %s (booking %s): %v — row completed un-split; manual reconciliation recommended", pr.id, *pr.bookingID, splitErr)
		return
	}
	if len(records) == 0 {
		return
	}
	// Align the primary row to records[0] (deposit/balance/full portion).
	// The align UPDATE clears the VAT fields exactly like the sweep rescue
	// (sweep.go:976-987) and the live post-charge path (handlers.go:2934-2937).
	// WITHOUT the clearing the pending row's VAT — computed at insert time on
	// the FULL pre-split charge — survives the align, and the re-apply below
	// is a silent no-op because apply_vat_to_payment is guarded on
	// vat_amount IS NULL: the primary row keeps VAT on the wrong (larger)
	// base. Clearing first makes the recompute effective on the split amount.
	if _, upErr := tx.Exec(ctx, `
		UPDATE payments SET
			amount = $1,
			payment_type = $2,
			fees = $3,
			is_vat_applicable = FALSE,
			vat_rate = NULL,
			vat_amount = NULL,
			net_amount = NULL,
			updated_at = NOW()
		WHERE id = $4
	`, records[0].Amount, records[0].PaymentType, records[0].Fees, pr.id); upErr != nil {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: failed to align completed payment %s to its split primary (%v) — manual reconciliation recommended", pr.id, upErr)
		return
	}
	// apply_vat_to_payment is idempotent (guarded on vat_amount IS NULL) and
	// skips discount/on_the_house/tip rows internally.
	payments.ApplyVATToBookingPayment(ctx, tx, pr.id)
	svc := payments.NewPaymentService()
	for _, rec := range records[1:] {
		pid, cErr := svc.CreatePaymentRecordTx(ctx, tx, rec, nil)
		if cErr != nil {
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: failed to insert completion split record for payment %s (%v) — manual reconciliation recommended", pr.id, cErr)
			return
		}
		payments.ApplyVATToBookingPayment(ctx, tx, pid)
	}
	webhookPromoteAndCompleteBooking(ctx, tx, *pr.bookingID)
}

// webhookVerifyCompletionSideEffects is the R9 belt-and-braces backstop: once
// the webhook wins the guarded flip it OWNS the post-completion bookkeeping, so
// before the reconcile commits it re-checks, inside the same tx, that a
// fully-paid booking actually ended up 'completed'. Every failure inside
// webhookApplyCompletedPaymentRecords already raises its own CRITICAL log, but
// this cross-check means a skipped side-effect chain can never be silently
// claimed as a completion. Log-only and never aborting: the charge already
// completed at Square, so the money mutation stands either way and the operator
// is told to reconcile.
func webhookVerifyCompletionSideEffects(ctx context.Context, tx pgx.Tx, pr webhookPendingCharge) {
	var status string
	if err := tx.QueryRow(ctx, `SELECT status FROM bookings WHERE id = $1`, *pr.bookingID).Scan(&status); err != nil {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: payment %s completed on booking %s but verifying the booking state failed (%v) — MANUAL RECONCILIATION REQUIRED", pr.id, *pr.bookingID, err)
		return
	}
	if status != "completed" && webhookBookingIsFullyPaid(ctx, tx, *pr.bookingID) {
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: payment %s made booking %s fully paid but the booking is %q, not 'completed' — the completion side-effects were skipped — MANUAL RECONCILIATION REQUIRED", pr.id, *pr.bookingID, status)
	}
}

// webhookBuildSplitRecords partitions a completed webhook-reconciled charge
// into its deposit/balance/tip payment records, mirroring the payments
// package's buildSplitRecords (handlers.go) — the exact split the sweep rescue
// and the live post-charge path apply. The records always partition
// paymentAmount exactly (booking portion + any tip). Kept in sync by hand: the
// payments package is mid-edit by another agent and must not be modified.
func webhookBuildSplitRecords(primary payments.PaymentRecord, reqPaymentType string, info *payments.BookingPaymentInfo, paymentAmount float64) ([]payments.PaymentRecord, error) {
	// After the booking starts there is no deposit protection window, but an
	// overpayment beyond the remaining booking value is still gratuity and must
	// be carved out as its own payment_type='tip' record (F3) — mirroring
	// buildTerminalSplitRecords' post-start carve.
	if clock.Now().After(info.StartTime) {
		remaining := math.Max(0, info.TotalAmount-info.TotalPaid)
		bookingPortion := math.Min(paymentAmount, remaining)
		bookingPortion = math.Round(bookingPortion*100) / 100
		tipPortion := math.Round((paymentAmount-bookingPortion)*100) / 100
		if tipPortion > 0.004 { // payments.roundingEpsilon (errors.go)
			// Belt-and-braces: the online tip bound (payments.maxOnlineTipPence,
			// £250) applies to every tip row. Returning an error (never
			// clamping) preserves the partition invariant.
			if tipPence := int64(math.Round(tipPortion * 100)); tipPence > 25000 {
				return nil, fmt.Errorf("webhookBuildSplitRecords: post-start carve for booking %s would mint a tip of %d pence, exceeding the £250 online tip cap (charge %.2f)", primary.BookingID, tipPence, paymentAmount)
			}
			records := []payments.PaymentRecord{primary}
			records[0].Amount = bookingPortion
			tip := primary
			tip.PaymentType = "tip"
			tip.Amount = tipPortion
			tip.Fees = 0
			if primary.IdempotencyKey != nil {
				k := *primary.IdempotencyKey + "-split-tip"
				tip.IdempotencyKey = &k
			}
			return append(records, tip), nil
		}
		return []payments.PaymentRecord{primary}, nil
	}

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

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

	// Tip: anything beyond the booking total.
	tipPortion := math.Round((remainingAfterDeposit-balancePortion)*100) / 100
	if tipPortion > 0.004 {
		if tipPence := int64(math.Round(tipPortion * 100)); tipPence > 25000 {
			return nil, fmt.Errorf("webhookBuildSplitRecords: pre-start carve for booking %s would mint a tip of %d pence, exceeding the £250 online tip cap (charge %.2f)", primary.BookingID, tipPence, paymentAmount)
		}
	}

	var records []payments.PaymentRecord
	splitIdx := 0

	if depositAmount > 0.004 {
		dep := primary
		dep.PaymentType = "deposit"
		dep.Amount = depositAmount
		records = append(records, dep)
		splitIdx++
	}

	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 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)
	}

	// Defensive fallback: nothing was appended (deposit, balance, AND tip all
	// zero — impossible given paymentAmount is validated > 0 upstream, so this
	// is a pure safety net).
	if len(records) == 0 {
		primary.Fees = 0
		records = append(records, primary)
	}
	return records, nil
}

// webhookPromoteAndCompleteBooking runs the booking-state transitions the live
// post-charge path applies after a payment is recorded (handlers.go): a
// deposit-paid pending_release booking is promoted to 'confirmed' (payment
// covers >= 20% of the total), and a fully-paid booking is completed with the
// loyalty/campaign/name_history side-effects (completeActiveBookingFromPayment +
// ApplyBookingCompletionSideEffects). Runs inside the caller's reconcile tx so
// the state changes and the payment flip commit atomically.
func webhookPromoteAndCompleteBooking(ctx context.Context, tx pgx.Tx, bookingID string) {
	// Deposit promotion: paid >= 20% of the booking total promotes a
	// pending_release booking to confirmed (handlers.go depositPromotionMinPct).
	var depositMet bool
	if err := tx.QueryRow(ctx, fmt.Sprintf(`
		WITH booking_total AS (
			SELECT total_amount * 100 AS total_pence FROM bookings WHERE id = $1
		),
		paid_total AS (
			SELECT COALESCE(SUM(amount), 0) * 100 AS paid_pence
			FROM payments
			WHERE booking_id = $1 AND status = 'completed'
			  AND payment_type != 'tip'
			  AND payment_method NOT IN ('discount', 'on_the_house')
		)
		SELECT pt.paid_pence >= ROUND(bt.total_pence * %f)
		FROM booking_total bt, paid_total pt
	`, 0.2), bookingID).Scan(&depositMet); err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to check deposit threshold for booking %s: %v", bookingID, err)
	}
	if depositMet {
		if _, err := tx.Exec(ctx, `
			UPDATE bookings SET status = 'confirmed', updated_at = NOW()
			WHERE id = $1 AND status = 'pending_release'
		`, bookingID); err != nil {
			log.Printf("[SQUARE-WEBHOOK] ALERT: payment completed but failed to promote booking %s from pending_release: %v", bookingID, err)
		}
	}

	if webhookBookingIsFullyPaid(ctx, tx, bookingID) {
		webhookCompleteActiveBooking(ctx, tx, bookingID)
	}
}

// webhookBookingIsFullyPaid reports whether completed payments toward the
// booking (excluding tips and on-the-house rows, but INCLUDING discount rows)
// cover 100% of the booking total. Mirrors the payments package's
// bookingIsFullyPaid (completion.go).
func webhookBookingIsFullyPaid(ctx context.Context, q db.Querier, bookingID string) bool {
	var fullyPaid bool
	if err := q.QueryRow(ctx, `
		WITH booking_total AS (
			SELECT total_amount * 100 AS total_pence FROM bookings WHERE id = $1
		),
		paid_total AS (
			SELECT COALESCE(SUM(amount), 0) * 100 AS paid_pence
			FROM payments
			WHERE booking_id = $1 AND status = 'completed'
			  AND payment_type != 'tip'
			  AND payment_method NOT IN ('on_the_house')
		)
		SELECT pt.paid_pence >= bt.total_pence AND bt.total_pence > 0
		FROM booking_total bt, paid_total pt
	`, bookingID).Scan(&fullyPaid); err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to check full-payment threshold for booking %s: %v", bookingID, err)
	}
	return fullyPaid
}

// webhookCompleteActiveBooking transitions an active booking to 'completed' and
// runs the completion side-effects, all within tx. It is a no-op if the booking
// is not in an active (completable) status, so cancelled, no-show and
// deposit-lapsed bookings are never auto-completed — and once completed it can
// never re-fire, because the status filter no longer matches. Mirrors
// completeActiveBookingFromPayment (completion.go).
func webhookCompleteActiveBooking(ctx context.Context, tx pgx.Tx, bookingID string) {
	var completedID string
	err := tx.QueryRow(ctx, `
		UPDATE bookings SET status = 'completed', updated_at = NOW()
		WHERE id = $1 AND status IN ('pending', 'confirmed', 'in_progress', 'pending_release')
		RETURNING id
	`, bookingID).Scan(&completedID)
	if err != nil {
		if !errors.Is(err, pgx.ErrNoRows) {
			log.Printf("[SQUARE-WEBHOOK] ALERT: failed to complete fully-paid booking %s: %v", bookingID, err)
		}
		return
	}
	var userID string
	if uErr := tx.QueryRow(ctx, `SELECT user_id FROM bookings WHERE id = $1`, bookingID).Scan(&userID); uErr != nil {
		log.Printf("[SQUARE-WEBHOOK] ALERT: booking %s completed by payment but failed to load user for side-effects: %v", bookingID, uErr)
		return
	}
	payments.ApplyBookingCompletionSideEffects(ctx, tx, bookingID, userID)
}

// clawbackFailedTillSales reverts the gift-card funding of every still-pending
// till sale funded by a Square charge that is DEFINITIVELY failed (Square
// FAILED/CANCELED — never ambiguous). It mirrors the stale-pending sweep's
// clawbackTillSaleFunding + revertGiftCardFunding (handlers/payments/sweep.go,
// till.go): each sale is claimed with a status='pending' guard so an
// already-resolved row is skipped without error, and the failed mark + funding
// revert commit atomically. A non-nil error means a DB failure left a pending
// sale's funding unreverted — the caller rejects the webhook so Square retries
// the clawback (the sweep is the eventual backstop).
func clawbackFailedTillSales(ctx context.Context, squarePaymentID string) error {
	rows, err := db.Conn.Query(ctx, `
		SELECT ts.id, ts.item_type, ts.item_id, ts.total_amount, gc.redeemed_by,
		       (ts.created_at = gc.created_at) AS is_create
		FROM till_sales ts
		LEFT JOIN gift_cards gc ON gc.id = ts.item_id
		WHERE ts.square_payment_id = $1 AND ts.status = 'pending'
	`, squarePaymentID)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to read pending till_sales for funding clawback (square payment %s): %v", squarePaymentID, err)
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var (
			saleID      string
			itemType    string
			itemID      sql.NullString
			totalAmount float64
			redeemedBy  sql.NullString
			isCreate    *bool
		)
		if err := rows.Scan(&saleID, &itemType, &itemID, &totalAmount, &redeemedBy, &isCreate); err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to scan pending till_sale for funding clawback (square payment %s): %v", squarePaymentID, err)
			return err
		}
		if err := clawbackOneTillSale(ctx, saleID, itemType, itemID, totalAmount, redeemedBy, isCreate); err != nil {
			return err
		}
	}
	return rows.Err()
}

// clawbackOneTillSale resolves one pending till sale of a definitively failed
// charge. A gift-card sale has its funding reverted atomically with the failed
// mark; a sale with no gift card (future retail product / orphaned item) is
// only marked failed. An already-resolved sale is skipped, not an error.
func clawbackOneTillSale(ctx context.Context, saleID, itemType string, itemID sql.NullString, totalAmount float64, redeemedBy sql.NullString, isCreate *bool) error {
	if itemType != "gift_card" || !itemID.Valid || itemID.String == "" || isCreate == nil {
		// No gift card to claw back — mark the sale failed without touching
		// any card (mirrors the sweep's non-gift-card branch).
		tag, err := db.Conn.Exec(ctx, `
			UPDATE till_sales SET status = 'failed', updated_at = NOW()
			WHERE id = $1 AND status = 'pending'
		`, saleID)
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to mark till sale %s failed: %v", saleID, err)
			return err
		}
		if tag.RowsAffected() > 0 {
			log.Printf("[SQUARE-WEBHOOK] payment.updated: marked till sale %s failed (no gift card to claw back)", saleID)
		}
		return nil
	}
	action := "topup"
	if *isCreate {
		action = "create"
	}
	var redeem *string
	if redeemedBy.Valid && redeemedBy.String != "" {
		redeem = &redeemedBy.String
	}
	if err := revertTillSaleGiftCardFunding(ctx, action, itemID.String, totalAmount, redeem, saleID); err != nil {
		if payments.IsTillSaleNotPending(err) {
			log.Printf("[SQUARE-WEBHOOK] Till sale %s was already resolved (not pending) — skipping funding clawback", saleID)
			return nil
		}
		log.Printf("CRITICAL: [SQUARE-WEBHOOK] failed to claw back gift card %s funding for failed till sale %s: %v — MANUAL RECONCILIATION REQUIRED: gift card may still be funded", itemID.String, saleID, err)
		return err
	}
	return nil
}

// revertTillSaleGiftCardFunding undoes the gift-card funding of a till sale
// whose charge definitively failed. It delegates to the single shared clawback
// implementation, payments.RevertGiftCardFunding (handlers/payments/
// giftcard_clawback.go) — the same helper the till handler and the stale-pending
// sweep use — so the webhook's money reversal can never drift from theirs. The
// claim-first status='pending' guard, the create/top-up branches, the
// redeem-to-user reversal, the CRITICAL reconciliation log lines and the
// errTillSaleNotPending sentinel all live in that one place.
func revertTillSaleGiftCardFunding(ctx context.Context, action, giftCardID string, amount float64, redeemToUserID *string, tillSaleID string) error {
	return payments.RevertGiftCardFunding(ctx, action, giftCardID, amount, redeemToUserID, tillSaleID)
}

// handleRefundUpdated reconciles a Square Refund state change against the local
// refunds row. Idempotent (status-guarded UPDATE + event_id dedup). A non-nil
// error means dispatch failed and the caller must NOT commit the dedup row. DB
// work runs on a bounded Background context (webhookDBContext).
func handleRefundUpdated(data json.RawMessage) error {
	ctx, cancel := webhookDBContext()
	defer cancel()

	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		// Parse failure on a known money event: retry via 5xx, no dedup row.
		return fmt.Errorf("refund event data envelope: %v: %w", err, errWebhookParseFailure)
	}
	if len(env.Object) == 0 {
		// Legacy envelope carrying only data.id — no nested refund object to
		// reconcile; acknowledge as received.
		log.Printf("[SQUARE-WEBHOOK] refund.updated received (data.id=%s)", env.ID)
		return nil
	}
	var refund squareRefundPayload
	if !parseSquareObject(env.Object, "refund", &refund) || refund.ID == "" || refund.Status == "" {
		// A refund object is present but unusable: cannot apply money state — retry.
		return fmt.Errorf("refund.updated payload for data.id=%q missing/invalid refund object (id=%q status=%q): %w", env.ID, refund.ID, refund.Status, errWebhookParseFailure)
	}
	// Single shared Square → local refund-status mapping (payments package) so
	// the webhook and the synchronous refund handlers can never drift. One
	// deliberate webhook-only override: APPROVED. The shared mapping maps
	// APPROVED→('completed', true) for the SYNCHRONOUS refund handlers, whose
	// blocking APPROVED result is final. The webhook is event-driven — an
	// APPROVED refund is still in flight at Square and may settle COMPLETED or
	// FAILED afterwards. Promoting on APPROVED would stick the row at
	// 'completed', and the FAILED demotion below only demotes 'pending' rows
	// (the over-refund guard counts completed refunds — demoting would exclude
	// money that already moved). Leave the row 'pending'; the Square status is
	// logged for the audit trail.
	if refund.Status == "APPROVED" {
		// Round-8 fix 4: an APPROVED refund arriving BEFORE the local refund row
		// exists must not be acked-and-dropped — the refund row can be created
		// in the same transaction as the charge in some paths, and acking here
		// would lose the terminal COMPLETED/FAILED settlement events forever (no
		// sweep fallback re-discovers an APPROVED-only trail). Return 503 so
		// Square re-delivers once the row exists.
		known, kErr := squareRefundKnown(ctx, refund.ID)
		if kErr != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to check whether square refund %s is known: %v", refund.ID, kErr)
			return kErr
		}
		if !known {
			log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s status APPROVED arrived with NO local refund row yet — returning 503 so Square retries (the refund row may be inserted in the same transaction as the charge)", refund.ID)
			return fmt.Errorf("refund.updated APPROVED for square refund %s with no local refund row yet — rejecting so Square retries", refund.ID)
		}
		log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s status APPROVED is non-terminal for the webhook (Square may still settle COMPLETED or FAILED) — leaving the local row pending", refund.ID)
		return nil
	}
	localStatus, terminal := payments.SquareRefundStatusToLocal(refund.Status)
	if !terminal {
		log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s status %q is non-terminal — no local state change", refund.ID, refund.Status)
		return nil
	}
	// COMPLETED may promote any non-completed row (incl. a sweep-failed refund
	// Square later shows complete) — the over-refund guard counts completed
	// refunds, so this only tightens it. FAILED only demotes a 'pending' row:
	// demoting 'completed' would let the guard exclude money that already moved
	// (the exact risk refunds.go documents for failed refunds).
	switch localStatus {
	case "completed":
		tag, err := db.Conn.Exec(ctx, `UPDATE refunds SET status = 'completed' WHERE square_refund_id = $1 AND status <> 'completed'`, refund.ID)
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to update refund %s to status %s: %v", refund.ID, localStatus, err)
			return err
		}
		if tag.RowsAffected() > 0 {
			log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s → local status %s", refund.ID, localStatus)
		} else {
			// Zero rows: the refund is already 'completed' (a plain no-op
			// replay) OR the local row does not exist yet. Round-8 fix 4: a
			// genuinely absent row must not be acked — the settlement would be
			// dropped forever. Return 503 so Square re-delivers once the row
			// appears (the refund row can be created in the same transaction as
			// the charge in some paths).
			known, kErr := squareRefundKnown(ctx, refund.ID)
			if kErr != nil {
				log.Printf("[SQUARE-WEBHOOK] Failed to check whether square refund %s is known: %v", refund.ID, kErr)
				return kErr
			}
			if !known {
				log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s status COMPLETED arrived with NO local refund row yet — returning 503 so Square retries (the refund row may be inserted in the same transaction as the charge)", refund.ID)
				return fmt.Errorf("refund.updated COMPLETED for square refund %s with no local refund row yet — rejecting so Square retries", refund.ID)
			}
		}
		// B1 sweep-dup refunds: a COMPLETED promotion must ALSO resolve the
		// parent payment/till_sale row. The B1 re-poll pass (sweepPendingB1Refunds,
		// refunds.go) only queries refunds rows still 'pending' — once this
		// promotion flips the row to 'completed' the re-poll can never see it
		// again, so the parent would stay pending forever and the stale-pending
		// sweep would re-replay the expired idempotency key each run, minting a
		// new charge every 5 minutes (HIGH, B1). Runs on every COMPLETED event
		// so a row stranded by an earlier path is healed too; idempotent — the
		// parent UPDATE is status='pending'-guarded and the clawback is claim-first.
		if err := resolveSweepDupRefundParent(ctx, refund.ID); err != nil {
			return err
		}
		return nil
	case "failed":
		// A5d: this webhook demotes a 'pending' row to 'failed' BEFORE the sweep
		// ever sees it — the sweep only processes 'pending' rows, so its
		// failed-refund admin notification would be permanently lost. Raise the
		// same 'refund_failed' notification here (via the shared exported
		// payments.InsertRefundFailedNotifications — the single source for the
		// SQL and its (reason='refund_failed', booking_id) dedup guard), guarded
		// to the actually-demoted row.
		var rowID string
		err := db.Conn.QueryRow(ctx,
			`UPDATE refunds SET status = 'failed' WHERE square_refund_id = $1 AND status = 'pending' RETURNING id`,
			refund.ID).Scan(&rowID)
		if errors.Is(err, pgx.ErrNoRows) {
			// No pending row matched — the refund is already resolved; a late
			// FAILED/REJECTED replay must not demote or notify anything.
			return nil
		}
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to update refund %s to status %s: %v", refund.ID, localStatus, err)
			return err
		}
		log.Printf("[SQUARE-WEBHOOK] refund.updated: square refund %s → local status failed (row %s)", refund.ID, rowID)
		payments.InsertRefundFailedNotifications(ctx, []string{rowID})
		return nil
	}
	return nil
}

// resolveSweepDupRefundParent resolves the parent row of a B1 sweep auto-refund
// of a replay-induced duplicate charge when the webhook promotes the refund to
// COMPLETED. The B1 re-poll pass (sweepPendingB1Refunds, refunds.go) only
// processes refunds rows still 'pending', so once THIS handler promotes the
// row to 'completed' the re-poll can never resolve the parent again — and a
// still-pending parent lets the stale-pending sweep re-replay its expired
// idempotency key every 5-minute run, minting a NEW charge each time (HIGH).
// This replicates the re-poll's resolveB1ParentFailed semantics here:
//
//   - a payments-table refund (reason exactly "duplicate charge — sweep replay")
//     is attached to the still-pending parent payment row — payment_id IS that
//     row, so it is marked failed;
//   - a till_sale refund carries the parent sale id in its reason ("(till_sale
//     <id>)"), so the sale's funded gift card is clawed back and the sale marked
//     failed, exactly like clawbackFailedTillSales.
//
// Idempotent: the parent UPDATE is status='pending'-guarded and the clawback is
// claim-first (payments.RevertGiftCardFunding), so a concurrent resolution by
// the sweep's own re-poll is a no-op. A non-nil error means a DB failure left
// the parent unresolved — the caller rejects the webhook so Square retries.
func resolveSweepDupRefundParent(ctx context.Context, squareRefundID string) error {
	var paymentID, reason string
	err := db.Conn.QueryRow(ctx, `
		SELECT payment_id, reason FROM refunds WHERE square_refund_id = $1
	`, squareRefundID).Scan(&paymentID, &reason)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			// Refund row already gone (deleted) — nothing to resolve.
			return nil
		}
		log.Printf("[SQUARE-WEBHOOK] Failed to read refund %s for parent resolution: %v", squareRefundID, err)
		return err
	}
	if !strings.HasPrefix(reason, "duplicate charge — sweep replay") {
		return nil
	}
	if reason == "duplicate charge — sweep replay" {
		tag, err := db.Conn.Exec(ctx, `
			UPDATE payments SET status = 'failed', updated_at = NOW()
			WHERE id = $1 AND status = 'pending'
		`, paymentID)
		if err != nil {
			log.Printf("[SQUARE-WEBHOOK] Failed to mark B1 parent payment %s failed: %v", paymentID, err)
			return err
		}
		if tag.RowsAffected() > 0 {
			log.Printf("[SQUARE-WEBHOOK] B1 sweep-dup refund %s COMPLETED — marked parent payment %s failed", squareRefundID, paymentID)
		}
		return nil
	}
	if idx := strings.Index(reason, "(till_sale "); idx >= 0 {
		tillSaleID := strings.TrimSuffix(reason[idx+len("(till_sale "):], ")")
		return clawbackOneTillSaleByID(ctx, tillSaleID)
	}
	log.Printf("[SQUARE-WEBHOOK] B1 sweep-dup refund %s COMPLETED but parent could not be identified from reason %q — MANUAL RECONCILIATION REQUIRED", squareRefundID, reason)
	return nil
}

// clawbackOneTillSaleByID loads a pending till sale by id and resolves it like
// a definitively failed charge: a gift-card sale has its funding reverted
// atomically with the failed mark; a sale with no gift card is only marked
// failed. Shares clawbackOneTillSale with clawbackFailedTillSales so the B1
// parent resolution and the failed-payment clawback can never drift.
func clawbackOneTillSaleByID(ctx context.Context, saleID string) error {
	var (
		itemType    string
		itemID      sql.NullString
		totalAmount float64
		redeemedBy  sql.NullString
		isCreate    *bool
	)
	err := db.Conn.QueryRow(ctx, `
		SELECT ts.item_type, ts.item_id, ts.total_amount, gc.redeemed_by,
		       (ts.created_at = gc.created_at) AS is_create
		FROM till_sales ts
		LEFT JOIN gift_cards gc ON gc.id = ts.item_id
		WHERE ts.id = $1
	`, saleID).Scan(&itemType, &itemID, &totalAmount, &redeemedBy, &isCreate)
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to load till sale %s for B1 parent clawback: %v", saleID, err)
		return err
	}
	return clawbackOneTillSale(ctx, saleID, itemType, itemID, totalAmount, redeemedBy, isCreate)
}

// truncateDisputeReason caps a Square dispute reason at the disputes.reason
// VARCHAR(192) column width. An over-long reason would fail the disputes
// INSERT; the handler treats that as a dispatch error (no dedup row, 5xx), so
// Square would retry forever — truncating lets the event succeed instead.
// VARCHAR(192) counts CHARACTERS, not bytes, so truncation must slice on a rune
// boundary: byte-slicing (reason[:192]) can split a multi-byte UTF-8 rune and
// store invalid UTF-8 (which Postgres rejects), failing the INSERT the same way
// an over-long reason would. Slicing the []rune form keeps the stored reason a
// valid, at-most-192-character UTF-8 string.
func truncateDisputeReason(reason string) string {
	runes := []rune(reason)
	if len(runes) > 192 {
		return string(runes[:192])
	}
	return reason
}

// handleDisputeCreated records a newly opened dispute: inserts the disputes row
// and surfaces a critical_payment_log admin notification so the owner sees the
// chargeback in-app. A disputed Square payment with NO local payments row (a
// Dashboard-initiated charge, a mismatched Square id, or a deleted/erased row)
// cannot be reconciled to a booking — no disputes row is written — but the
// admin notification is STILL raised with booking_id NULL, because there is no
// sweep fallback for disputes and a chargeback the app cannot see is a silent
// money-loss path the owner must always be told about. Idempotent via
// ON CONFLICT (square_dispute_id) DO NOTHING plus the event_id dedup. A non-nil
// error means dispatch failed (no dedup row committed — Square retries). DB
// work runs on a bounded Background context (webhookDBContext).
func handleDisputeCreated(data json.RawMessage) error {
	ctx, cancel := webhookDBContext()
	defer cancel()

	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		// Parse failure on a known money event (chargeback): retry via 5xx, no
		// dedup row — disputes have no sweep fallback, so a lost event is a
		// silent money-loss path.
		return fmt.Errorf("dispute event data envelope: %v: %w", err, errWebhookParseFailure)
	}
	if len(env.Object) == 0 {
		// Legacy envelope carrying only data.id — no nested dispute object to
		// record; acknowledge as received.
		log.Printf("[SQUARE-WEBHOOK] dispute.created received (data.id=%s)", env.ID)
		return nil
	}
	var dispute squareDisputePayload
	if !parseSquareObject(env.Object, "dispute", &dispute) || dispute.ID == "" {
		// A dispute object is present but unusable: cannot record the
		// chargeback — retry.
		return fmt.Errorf("dispute.created payload for data.id=%q missing/invalid dispute object (id=%q): %w", env.ID, dispute.ID, errWebhookParseFailure)
	}
	squarePaymentID := ""
	if dispute.DisputedPayment != nil {
		squarePaymentID = dispute.DisputedPayment.PaymentID
	}
	paymentID, bookingID, paymentFound := findPaymentBySquareID(ctx, squarePaymentID)
	if !paymentFound {
		// Untracked chargeback: no local payments row for this Square charge
		// (Dashboard-initiated, mismatched Square payment id, or a deleted/erased
		// row). There is NO sweep fallback for disputes — this notification is
		// the only in-app trace the owner gets that Square is clawing back funds,
		// so it must never be skipped. booking_id stays NULL; each DISTINCT
		// dispute gets its OWN deterministic-id notification (the booking-scoped
		// dedup would collapse separate chargebacks into one suppressed row).
		// Still return nil so the dedup row commits and Square's retry is
		// acknowledged.
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: dispute %s created for square payment %q with NO local payment row — chargeback cannot be reconciled in-app — admin notified (booking_id NULL)", dispute.ID, squarePaymentID)
		insertCriticalPaymentNotification(ctx, "", dispute.ID)
		return nil
	}
	amount := squareMoneyToAmount(dispute.AmountMoney)
	tag, err := db.Conn.Exec(ctx, `
		INSERT INTO disputes (square_dispute_id, payment_id, status, amount, reason, created_at, updated_at)
		VALUES ($1, $2, 'open', $3, NULLIF($4, ''), NOW(), NOW())
		ON CONFLICT (square_dispute_id) DO NOTHING
	`, dispute.ID, paymentID, amount, truncateDisputeReason(dispute.Reason))
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to insert dispute %s: %v", dispute.ID, err)
		return err
	}
	_ = tag
	insertCriticalPaymentNotification(ctx, bookingID, "")
	log.Printf("[SQUARE-WEBHOOK] CRITICAL: dispute %s created (amount %s, reason %q) for square payment %s — admin notified", dispute.ID, amount, truncateDisputeReason(dispute.Reason), squarePaymentID)
	return nil
}

// handleDisputeStateUpdated applies a Square dispute state change to the local
// disputes row (upsert — a state.updated may arrive before the created event),
// and on a terminal loss marks the payment failed + raises CRITICAL. Won is
// logged only. Idempotent: the upsert converges to the same row. A non-nil
// error means dispatch failed (no dedup row committed — Square retries). DB
// work runs on a bounded Background context (webhookDBContext).
func handleDisputeStateUpdated(data json.RawMessage) error {
	ctx, cancel := webhookDBContext()
	defer cancel()

	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		// Parse failure on a known money event (chargeback): retry via 5xx, no
		// dedup row — disputes have no sweep fallback.
		return fmt.Errorf("dispute event data envelope: %v: %w", err, errWebhookParseFailure)
	}
	if len(env.Object) == 0 {
		// Legacy envelope carrying only data.id — no nested dispute object;
		// acknowledge as received.
		log.Printf("[SQUARE-WEBHOOK] dispute.state.updated received (data.id=%s)", env.ID)
		return nil
	}
	var dispute squareDisputePayload
	if !parseSquareObject(env.Object, "dispute", &dispute) || dispute.ID == "" {
		// A dispute object is present but unusable: cannot apply the state
		// change — retry.
		return fmt.Errorf("dispute.state.updated payload for data.id=%q missing/invalid dispute object (id=%q): %w", env.ID, dispute.ID, errWebhookParseFailure)
	}
	localStatus := squareDisputeStateToLocal(dispute.State)
	amount := squareMoneyToAmount(dispute.AmountMoney)

	squarePaymentID := ""
	if dispute.DisputedPayment != nil {
		squarePaymentID = dispute.DisputedPayment.PaymentID
	}
	paymentID, bookingID, paymentFound := findPaymentBySquareID(ctx, squarePaymentID)
	if !paymentFound {
		// Row may already exist from dispute.created — recover its payment.
		paymentID, bookingID = findPaymentByDisputeID(ctx, dispute.ID)
		if paymentID == "" {
			// Untracked chargeback: no local payments row for this Square
			// charge AND no dispute row to recover one from. Mirror the
			// dispute.created untracked branch — there is NO sweep fallback for
			// disputes, so this critical notification is the only in-app trace
			// the owner gets that Square is clawing back funds; a state.updated
			// arriving without a prior created event must surface it too, never
			// drop it silently. booking_id stays NULL; disputeNotificationID
			// gives each DISTINCT dispute its own deterministic-id notification
			// and makes this insert a no-op if dispute.created already raised
			// it. Still return nil so the dedup row commits and Square's retry
			// is acknowledged.
			log.Printf("[SQUARE-WEBHOOK] CRITICAL: dispute %s state change (state %s) for square payment %q with NO local payment row — chargeback cannot be reconciled in-app — admin notified (booking_id NULL)", dispute.ID, dispute.State, squarePaymentID)
			insertCriticalPaymentNotification(ctx, "", dispute.ID)
			return nil
		}
	}

	_, err := db.Conn.Exec(ctx, `
		INSERT INTO disputes (square_dispute_id, payment_id, status, amount, reason, created_at, updated_at)
		VALUES ($1, $2, $3, $4, NULLIF($5, ''), NOW(), NOW())
		ON CONFLICT (square_dispute_id) DO UPDATE
		SET status = EXCLUDED.status, amount = EXCLUDED.amount,
		    reason = EXCLUDED.reason, updated_at = NOW()
	`, dispute.ID, paymentID, localStatus, amount, truncateDisputeReason(dispute.Reason))
	if err != nil {
		log.Printf("[SQUARE-WEBHOOK] Failed to update dispute %s to state %s: %v", dispute.ID, dispute.State, err)
		return err
	}

	switch localStatus {
	case "lost":
		if err := markPaymentFailed(ctx, paymentID); err != nil {
			return err
		}
		insertCriticalPaymentNotification(ctx, bookingID, "")
		log.Printf("[SQUARE-WEBHOOK] CRITICAL: dispute %s LOST — payment %s marked failed; admin notified", dispute.ID, paymentID)
	case "won":
		log.Printf("[SQUARE-WEBHOOK] dispute %s WON — resolved in seller's favour; no action", dispute.ID)
	default:
		log.Printf("[SQUARE-WEBHOOK] dispute %s state → %s (status %s)", dispute.ID, dispute.State, localStatus)
	}
	return nil
}

// handleDisputeEvidence logs evidence submissions/removals. Evidence does not
// change the dispute's local status, so it is informational only.
func handleDisputeEvidence(data json.RawMessage) error {
	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		log.Printf("[SQUARE-WEBHOOK] dispute evidence event received (payload length=%d)", len(data))
		return nil
	}
	var dispute squareDisputePayload
	if !parseSquareObject(env.Object, "dispute", &dispute) || dispute.ID == "" {
		log.Printf("[SQUARE-WEBHOOK] dispute evidence event received (data.id=%s)", env.ID)
		return nil
	}
	log.Printf("[SQUARE-WEBHOOK] dispute evidence event for dispute %s (state %s)", dispute.ID, dispute.State)
	return nil
}

// handleTerminalCheckout logs terminal checkout lifecycle events. Terminal
// checkout state is owned by the poll/sweep handlers (handlers/payments/),
// which fetch the authoritative status from Square — no state mutation here.
func handleTerminalCheckout(data json.RawMessage) error {
	var env squareWebhookData
	if err := json.Unmarshal(data, &env); err != nil {
		log.Printf("[SQUARE-WEBHOOK] terminal.checkout event received (payload length=%d)", len(data))
		return nil
	}
	log.Printf("[SQUARE-WEBHOOK] terminal.checkout event received (data.id=%s)", env.ID)
	return nil
}