<?php
if ( ! defined( 'ABSPATH' ) ) exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own custom tables (bookings, seats, tickets…) have no WordPress API; booking and seat data must always be read live, so it is not cached.

/**
 * Central place for every query. Keeping this separate from the REST
 * controller makes the locking logic easy to unit-test / reason about
 * on its own.
 */
class SEATT_DB {

	const HOLD_MINUTES = 12; // Default checkout timer: how long seats/tickets stay reserved in a cart.
	const HOLD_MINUTES_MIN = 2;
	const HOLD_MINUTES_MAX = 60;

	/**
	 * Checkout timer for an event, in minutes: the event's own setting
	 * (Event settings → "Checkout timer"), else the default of 12.
	 */
	public static function hold_minutes( $event_id = 0 ) {
		$m = $event_id ? (int) get_post_meta( (int) $event_id, '_seatt_hold_minutes', true ) : 0;
		if ( $m <= 0 ) {
			$m = self::HOLD_MINUTES;
		}
		$m = (int) apply_filters( 'seatt_hold_minutes', $m, (int) $event_id );
		return max( self::HOLD_MINUTES_MIN, min( self::HOLD_MINUTES_MAX, $m ) );
	}

	/**
	 * Where a buyer goes when the checkout timer runs out: the event's own
	 * page, with a flag so it can say why they are back.
	 */
	public static function expired_url( $event_id ) {
		$url = get_permalink( (int) $event_id );
		$url = (string) apply_filters( 'seatt_hold_expired_url', $url ? $url : home_url( '/' ), (int) $event_id );
		return add_query_arg( 'seatt_expired', 1, $url );
	}

	/** Checkout timer for the event a seating chart belongs to. */
	public static function hold_minutes_for_chart( $chart_id ) {
		global $wpdb;
		$event_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT event_id FROM %i WHERE id = %d', $wpdb->seatt_charts, (int) $chart_id ) );
		return self::hold_minutes( $event_id );
	}

	/** Set while an admin ticket is created: no "order placed" emails. */
	public static $quiet_created = false;

	/** Plugin tables, registered on $wpdb like core's own ($wpdb->seatt_bookings …). */
	const TABLES = array( 'charts', 'seats', 'bookings', 'booking_seats', 'templates', 'zones', 'zone_holds', 'booking_zones', 'waitlist', 'activity_log', 'promos', 'booking_attendees', 'tickets' );

	public static function register_tables() {
		global $wpdb;
		if ( ! is_object( $wpdb ) ) {
			return;
		}
		foreach ( self::TABLES as $t ) {
			$wpdb->{'seatt_' . $t} = $wpdb->prefix . 'seatt_' . $t;
		}
	}

	public static function t_charts() {
		global $wpdb; return $wpdb->prefix . 'seatt_charts';
	}
	public static function t_seats() {
		global $wpdb; return $wpdb->prefix . 'seatt_seats';
	}
	public static function t_bookings() {
		global $wpdb; return $wpdb->prefix . 'seatt_bookings';
	}
	public static function t_booking_seats() {
		global $wpdb; return $wpdb->prefix . 'seatt_booking_seats';
	}
	public static function t_templates() {
		global $wpdb; return $wpdb->prefix . 'seatt_templates';
	}
	public static function t_zones() {
		global $wpdb; return $wpdb->prefix . 'seatt_zones';
	}
	public static function t_zone_holds() {
		global $wpdb; return $wpdb->prefix . 'seatt_zone_holds';
	}
	public static function t_booking_zones() {
		global $wpdb; return $wpdb->prefix . 'seatt_booking_zones';
	}
	public static function t_waitlist() {
		global $wpdb; return $wpdb->prefix . 'seatt_waitlist';
	}
	public static function t_activity_log() {
		global $wpdb; return $wpdb->prefix . 'seatt_activity_log';
	}

	/* ---------------------------------------------------------------
	 * Templates (reusable layouts, not tied to one event)
	 * ------------------------------------------------------------- */

	/** Everything except layout_json — the picker only needs id/name/
	 * thumbnail to render its cards, not the (potentially large) layout
	 * payload of every template up front. */
	public static function get_templates() {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, thumbnail, created_at FROM %i ORDER BY created_at DESC', $wpdb->seatt_templates ) );
	}

	public static function get_template( $id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $wpdb->seatt_templates, $id ) );
	}

	public static function save_template( $name, array $layout_objects, $thumbnail, $user_id ) {
		global $wpdb;
		$wpdb->insert( $wpdb->seatt_templates, array(
			'name'        => $name,
			'layout_json' => wp_json_encode( $layout_objects ),
			'thumbnail'   => $thumbnail ?: null,
			'created_by'  => $user_id ?: null,
			'created_at'  => current_time( 'mysql' ),
		) );
		return $wpdb->insert_id;
	}

	public static function delete_template( $id ) {
		global $wpdb;
		return $wpdb->delete( $wpdb->seatt_templates, array( 'id' => $id ) );
	}

	/* ---------------------------------------------------------------
	 * Charts (layout)
	 * ------------------------------------------------------------- */

	public static function get_chart_for_event( $event_id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE event_id = %d ORDER BY id DESC LIMIT 1', $wpdb->seatt_charts, $event_id ) );
	}

	/**
	 * Save (or create) the layout for an event, then sync the seats
	 * table so bookable seat rows exist for every seat object in the
	 * layout. Existing seat status (available/held/sold) is preserved
	 * for seat_keys that still exist; seats removed from the layout are
	 * left in place (never delete sold seats out from under a booking) but
	 * flagged inactive.
	 */
	public static function save_chart( $event_id, $name, array $layout_objects ) {
		global $wpdb;
		$now = current_time( 'mysql' );

		$chart = self::get_chart_for_event( $event_id );
		$layout_json = wp_json_encode( $layout_objects );

		if ( $chart ) {
			$wpdb->update( $wpdb->seatt_charts, array(
				'name'        => $name,
				'layout_json' => $layout_json,
				'updated_at'  => $now,
			), array( 'id' => $chart->id ) );
			$chart_id = $chart->id;
		} else {
			$wpdb->insert( $wpdb->seatt_charts, array(
				'event_id'    => $event_id,
				'name'        => $name,
				'layout_json' => $layout_json,
				'created_at'  => $now,
				'updated_at'  => $now,
			) );
			$chart_id = $wpdb->insert_id;
		}

		self::sync_seats( $chart_id, $layout_objects );
		self::sync_zones( $event_id, $layout_objects );

		return $chart_id;
	}

	/**
	 * Materializes 'zone' objects from the layout into seatt_zones — the
	 * GA equivalent of sync_seats() above, keyed by event_id directly
	 * (rather than chart_id) since availability/booking queries always
	 * need "this event's zones", never "this chart's zones" specifically.
	 */
	/** GA zones with paid/pending tickets that $layout_objects no longer contains. */
	public static function orphaned_zones( $event_id, array $layout_objects ) {
		global $wpdb;
		$keys = array();
		foreach ( $layout_objects as $obj ) {
			if ( ! empty( $obj['type'] ) && 'zone' === $obj['type'] ) {
				$keys[] = sanitize_text_field( $obj['id'] ?? '' );
			}
		}
		$not_in = $keys ? ' AND z.zone_key NOT IN ( ' . implode( ',', array_fill( 0, count( $keys ), '%s' ) ) . ' )' : '';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $not_in is only a list of %s placeholders; the values go through prepare().
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT z.id, z.name, MAX(b.id) AS booking_id FROM %i z
			 JOIN %i bz ON bz.zone_id = z.id
			 JOIN %i b ON b.id = bz.booking_id
			 WHERE z.event_id = %d AND z.is_active = 1 AND b.status IN ( \'paid\', \'pending_payment\' )' . $not_in . '
			 GROUP BY z.id, z.name', array_merge( array( $wpdb->seatt_zones, $wpdb->seatt_booking_zones, $wpdb->seatt_bookings, $event_id ), $keys ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $rows;
	}

	private static function sync_zones( $event_id, array $layout_objects ) {
		global $wpdb;
		$now = current_time( 'mysql' );
		$live_zone_keys = array();

		foreach ( $layout_objects as $obj ) {
			if ( empty( $obj['type'] ) || 'zone' !== $obj['type'] ) {
				continue;
			}
			$zone_key = sanitize_text_field( $obj['id'] );
			$live_zone_keys[] = $zone_key;
			$existing = $wpdb->get_row( $wpdb->prepare( 'SELECT id FROM %i WHERE event_id = %d AND zone_key = %s', $wpdb->seatt_zones, $event_id, $zone_key ) );

			$data = array(
				'name'     => sanitize_text_field( $obj['name'] ?? 'Zone' ),
				'category' => sanitize_text_field( $obj['category'] ?? 'standard' ),
				'color'    => ! empty( $obj['color'] ) ? sanitize_text_field( $obj['color'] ) : null,
				'price'    => isset( $obj['price'] ) ? floatval( $obj['price'] ) : 0,
				'capacity' => isset( $obj['capacity'] ) ? intval( $obj['capacity'] ) : 0,
				'is_active' => 1,
			);

			if ( $existing ) {
				$wpdb->update( $wpdb->seatt_zones, $data, array( 'id' => $existing->id ) );
			} else {
				$data['event_id']   = $event_id;
				$data['zone_key']   = $zone_key;
				$data['created_at'] = $now;
				$wpdb->insert( $wpdb->seatt_zones, $data );
			}
		}

		// A GA area deleted in the builder used to stay in this table and
		// stay bookable through the API. Retire it (never delete — sold
		// tickets must still resolve to it).
		$not_in = $live_zone_keys ? ' AND zone_key NOT IN ( ' . implode( ',', array_fill( 0, count( $live_zone_keys ), '%s' ) ) . ' )' : '';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $not_in is only a list of %s placeholders; the values go through prepare().
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 0 WHERE event_id = %d' . $not_in, array_merge( array( $wpdb->seatt_zones, $event_id ), $live_zone_keys ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Works out, WITHOUT changing anything, what saving $layout_objects
	 * would do to seats that belong to bookings.
	 *
	 * A seat's identity in the database is the builder's seat id
	 * (seat_key). Several builder edits — resizing a block, changing a
	 * curved row's seat count, rebuilding a group — throw the old seat
	 * objects away and mint new ids for seats that are, to a human,
	 * "the same seat A7". Before this, the old A7 (sold, linked to a
	 * booking) was simply retired and a brand-new A7 appeared as
	 * available: the buyer's ticket pointed at a seat that vanished
	 * from the map, and the same spot could be sold again.
	 *
	 * Returns:
	 *  'renames' => [ seat_row_id => new_seat_key ] — retiring seats that
	 *               match exactly one new seat by row + label; the old row
	 *               (with its status and booking link) simply takes over
	 *               the new id.
	 *  'orphans' => rows of seats with a paid/pending booking that the new
	 *               layout would remove with no match.
	 */
	public static function plan_seat_sync( $chart_id, array $layout_objects ) {
		global $wpdb;
		$norm = function ( $row, $label ) {
			return strtolower( trim( (string) $row ) ) . '|' . strtolower( trim( (string) $label ) );
		};

		$layout_keys = array();
		$new_by_name = array();
		foreach ( $layout_objects as $obj ) {
			if ( empty( $obj['type'] ) || ! in_array( $obj['type'], array( 'seat', 'table_seat' ), true ) ) {
				continue;
			}
			$key = sanitize_text_field( $obj['id'] ?? '' );
			$layout_keys[ $key ] = true;
			$new_by_name[ $norm( $obj['row'] ?? '', $obj['label'] ?? $key ) ][] = $key;
		}

		$rows = $chart_id ? $wpdb->get_results( $wpdb->prepare( 'SELECT s.id, s.seat_key, s.row_name, s.label, s.status, s.is_active,
			        ( SELECT b.id FROM %i bs JOIN %i b ON b.id = bs.booking_id
			          WHERE bs.seat_id = s.id AND b.status IN ( \'paid\', \'pending_payment\' ) ORDER BY b.id DESC LIMIT 1 ) AS booking_id
			 FROM %i s WHERE s.chart_id = %d', $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $wpdb->seatt_seats, $chart_id ) ) : array();

		$existing_keys = array();
		foreach ( (array) $rows as $r ) {
			$existing_keys[ $r->seat_key ] = true;
		}

		// Retiring = in the DB and active (or still owned by a booking),
		// but no longer in the layout.
		$retiring_by_name = array();
		foreach ( (array) $rows as $r ) {
			if ( isset( $layout_keys[ $r->seat_key ] ) ) {
				continue;
			}
			if ( ! (int) $r->is_active && ! $r->booking_id ) {
				continue;
			}
			$retiring_by_name[ $norm( $r->row_name, $r->label ) ][] = $r;
		}

		$renames = array();
		$orphans = array();
		foreach ( $retiring_by_name as $name => $list ) {
			$candidates = array_values( array_filter(
				$new_by_name[ $name ] ?? array(),
				function ( $k ) use ( $existing_keys ) { return ! isset( $existing_keys[ $k ] ); }
			) );
			// Only an unambiguous one-to-one match is carried over; two
			// seats both called "A7" can't be told apart safely.
			if ( 1 === count( $list ) && 1 === count( $candidates ) ) {
				$renames[ (int) $list[0]->id ] = $candidates[0];
				continue;
			}
			foreach ( $list as $r ) {
				if ( $r->booking_id ) {
					$orphans[] = $r;
				}
			}
		}
		return array( 'renames' => $renames, 'orphans' => $orphans );
	}

	/** Seats of an event's chart that belong to a paid/pending booking —
	 * the builder draws these locked. */
	public static function get_locked_seats( $event_id ) {
		global $wpdb;
		$chart = self::get_chart_for_event( $event_id );
		if ( ! $chart ) {
			return array();
		}
		return $wpdb->get_results( $wpdb->prepare( 'SELECT s.seat_key AS `key`, s.row_name AS `row`, s.label, b.id AS booking_id, b.status, b.buyer_name, b.payment_method, bs.attendee_name
			 FROM %i s
			 JOIN %i bs ON bs.seat_id = s.id
			 JOIN %i b ON b.id = bs.booking_id
			 WHERE s.chart_id = %d AND s.is_active = 1 AND b.status IN ( \'paid\', \'pending_payment\' )
			 ORDER BY b.id', $wpdb->seatt_seats, $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $chart->id ) );
	}

	private static function sync_seats( $chart_id, array $layout_objects ) {
		global $wpdb;
		$now = current_time( 'mysql' );

		// Carry booked seats over to their re-generated ids (see
		// plan_seat_sync()) BEFORE the normal insert/update/retire pass,
		// so the old row — status, booking link and all — is simply
		// found under its new key instead of being retired.
		$plan = self::plan_seat_sync( $chart_id, $layout_objects );
		foreach ( $plan['renames'] as $seat_row_id => $new_key ) {
			$wpdb->update( $wpdb->seatt_seats, array( 'seat_key' => $new_key ), array( 'id' => $seat_row_id ) );
		}

		// Every seat_key this layout still contains. Anything else on
		// this chart is a leftover from a previous version of it.
		$live_keys = array();

		foreach ( $layout_objects as $obj ) {
			if ( empty( $obj['type'] ) || ! in_array( $obj['type'], array( 'seat', 'table_seat' ), true ) ) {
				continue;
			}
			$seat_key    = sanitize_text_field( $obj['id'] );
			$live_keys[] = $seat_key;
			$existing = $wpdb->get_row( $wpdb->prepare( 'SELECT id FROM %i WHERE chart_id = %d AND seat_key = %s', $wpdb->seatt_seats, $chart_id, $seat_key ) );

			$data = array(
				'is_active' => 1,
				'label'    => sanitize_text_field( $obj['label'] ?? $seat_key ),
				'row_name' => sanitize_text_field( $obj['row'] ?? '' ),
				'category' => sanitize_text_field( $obj['category'] ?? 'standard' ),
				'color'    => ! empty( $obj['color'] ) ? sanitize_text_field( $obj['color'] ) : null,
				'price'    => isset( $obj['price'] ) ? floatval( $obj['price'] ) : 0,
				'pos_x'    => floatval( $obj['x'] ?? 0 ),
				'pos_y'    => floatval( $obj['y'] ?? 0 ),
			);

			if ( $existing ) {
				$wpdb->update( $wpdb->seatt_seats, $data, array( 'id' => $existing->id ) );
			} else {
				$data['chart_id']   = $chart_id;
				$data['seat_key']   = $seat_key;
				$data['status']     = 'available';
				$data['created_at'] = $now;
				$wpdb->insert( $wpdb->seatt_seats, $data );
			}
		}

		// Retire whatever the layout no longer contains.
		//
		// This is what save_chart()'s docblock always claimed happened
		// and what never actually did. Without it every seat a chart has
		// ever had stayed bookable: rebuild a chart and the buyer page
		// got the new seats AND every superseded one, drawn at their old
		// positions in their old colours.
		//
		// Flagged, never deleted — a seat attached to a booking has to
		// survive so that booking still resolves to a real row.
		if ( $live_keys ) {
			$placeholders = implode( ',', array_fill( 0, count( $live_keys ), '%s' ) );
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 0 WHERE chart_id = %d AND seat_key NOT IN ( ' . $placeholders . ' )', array_merge( array( $wpdb->seatt_seats, $chart_id ), $live_keys ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	public static function get_seats_for_chart( $chart_id ) {
		global $wpdb;
		self::release_expired_holds( $chart_id );
		// Only seats the current layout still has. Retired rows stay in
		// the table for the bookings that reference them, but must never
		// be offered again.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE chart_id = %d AND is_active = 1', $wpdb->seatt_seats, $chart_id ) );
	}

	/* ---------------------------------------------------------------
	 * GA zones — capacity-based, no individual seat identity. See the
	 * sync_zones()/schema comments above for how this differs from the
	 * seat-holding design below (one hold ROW per session+zone holding a
	 * QUANTITY, rather than one row PER unit).
	 * ------------------------------------------------------------- */

	public static function release_expired_zone_holds() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE held_until < %s', $wpdb->seatt_zone_holds, current_time( 'mysql' ) ) );
	}

	public static function get_zones_for_event( $event_id ) {
		global $wpdb;
		self::release_expired_zone_holds();
		$zones = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE event_id = %d AND is_active = 1', $wpdb->seatt_zones, $event_id ) );
		foreach ( $zones as $z ) {
			$sold = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(SUM(bz.quantity),0) FROM %i bz
				 JOIN %i b ON b.id = bz.booking_id
				 WHERE bz.zone_id = %d AND b.status IN (\'paid\',\'pending_payment\')', $wpdb->seatt_booking_zones, $wpdb->seatt_bookings, $z->id ) );
			$held = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(SUM(quantity),0) FROM %i WHERE zone_id = %d', $wpdb->seatt_zone_holds, $z->id ) );
			$z->sold      = $sold;
			$z->held      = $held;
			$z->remaining = max( 0, (int) $z->capacity - $sold - $held );
		}
		return $zones;
	}

	/**
	 * Sets (not adds to) this session's hold quantity for one zone —
	 * called every time the quantity stepper in the cart changes, so
	 * it's a replace rather than an accumulate. A single atomic
	 * capacity check (excluding this session's OWN existing hold, so
	 * adjusting your own quantity up/down doesn't count against
	 * yourself) guards against overselling the same way hold_seats()'s
	 * per-row UPDATE does for numbered seats.
	 */
	/** Tickets currently in one cart: live seat holds + zone holds
	 * (optionally leaving one zone out, for a quantity change). */
	public static function cart_size( $session_id, $exclude_zone_id = 0 ) {
		global $wpdb;
		$now   = current_time( 'mysql' );
		$seats = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = \'held\' AND held_by = %s AND held_until >= %s', $wpdb->seatt_seats, $session_id, $now ) );
		$zones = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(SUM(quantity),0) FROM %i WHERE session_id = %s AND zone_id <> %d AND held_until >= %s', $wpdb->seatt_zone_holds, $session_id, intval( $exclude_zone_id ), $now ) );
		return $seats + $zones;
	}

	public static function hold_zone_tickets( $zone_id, $quantity, $session_id ) {
		global $wpdb;
		self::release_expired_zone_holds();

		$zone = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND is_active = 1', $wpdb->seatt_zones, $zone_id ) );
		if ( ! $zone ) {
			return new WP_Error( 'seatt_zone_not_found', __( 'Zone not found.', 'seathive' ) );
		}
		if ( $quantity < 1 ) {
			self::release_zone_hold( $zone_id, $session_id );
			return true;
		}

		$sold = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(SUM(bz.quantity),0) FROM %i bz
			 JOIN %i b ON b.id = bz.booking_id
			 WHERE bz.zone_id = %d AND b.status IN (\'paid\',\'pending_payment\')', $wpdb->seatt_booking_zones, $wpdb->seatt_bookings, $zone_id ) );
		$held_by_others = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(SUM(quantity),0) FROM %i WHERE zone_id = %d AND session_id != %s', $wpdb->seatt_zone_holds, $zone_id, $session_id ) );

		if ( $quantity > ( (int) $zone->capacity - $sold - $held_by_others ) ) {
			return new WP_Error( 'seatt_zone_full', __( 'Not enough tickets left in this zone.', 'seathive' ) );
		}

		$held_until    = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) + self::hold_minutes( (int) $zone->event_id ) * MINUTE_IN_SECONDS );
		// Fixed a real bug here: this used to be gmdate(...time()...) — UTC
		// — while release_expired_zone_holds() right above compares it
		// against current_time('mysql'), which is the SITE'S OWN LOCAL
		// TIME (WordPress Settings → General → Timezone), not UTC. On a
		// site set to WIB (UTC+7) or any other positive offset, that
		// mismatch made a hold looking "expired" the INSTANT it was
		// created, since the UTC held_until value was always numerically
		// behind the site's own local "now". Using current_time()
		// (WordPress's own site-local-time helper) for BOTH sides is
		// what actually keeps them talking about the same clock.
		$existing_hold = $wpdb->get_row( $wpdb->prepare( 'SELECT id FROM %i WHERE zone_id = %d AND session_id = %s', $wpdb->seatt_zone_holds, $zone_id, $session_id ) );

		if ( $existing_hold ) {
			$wpdb->update( $wpdb->seatt_zone_holds,
				array( 'quantity' => $quantity, 'held_until' => $held_until ),
				array( 'id' => $existing_hold->id )
			);
		} else {
			$wpdb->insert( $wpdb->seatt_zone_holds, array(
				'zone_id'    => $zone_id,
				'session_id' => $session_id,
				'quantity'   => $quantity,
				'held_until' => $held_until,
			) );
		}
		return true;
	}

	public static function release_zone_hold( $zone_id, $session_id ) {
		global $wpdb;
		return $wpdb->delete( $wpdb->seatt_zone_holds, array( 'zone_id' => $zone_id, 'session_id' => $session_id ) );
	}

	/* ---------------------------------------------------------------
	 * Seat holding / cart logic (see seat-locking design discussion)
	 * ------------------------------------------------------------- */

	/**
	 * Auto-release any seat whose hold has expired. Called on every read
	 * so stale holds never block a sale even without a cron job running.
	 * A real WP-Cron job calling this per active chart is recommended too.
	 */
	public static function release_expired_holds( $chart_id ) {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'UPDATE %i
			 SET status = \'available\', held_by = NULL, held_until = NULL
			 WHERE chart_id = %d AND status = \'held\' AND held_until < %s', $wpdb->seatt_seats, $chart_id, current_time( 'mysql' ) ) );
		self::repair_paid_seats( $chart_id );
	}

	/**
	 * Attempt to hold a batch of seats for one session (cart). Each seat
	 * is claimed with a single atomic UPDATE ... WHERE status='available'
	 * so two ushers racing for the same seat can never both succeed —
	 * whichever UPDATE's WHERE clause matches first wins the row.
	 *
	 * Returns array( 'held' => [seat_id...], 'failed' => [seat_id...] ).
	 */
	public static function hold_seats( $chart_id, array $seat_ids, $session_id ) {
		global $wpdb;
		self::release_expired_holds( $chart_id );

		// Fixed a real bug here: this used to be gmdate(...time()...) —
		// UTC — while release_expired_holds() right above compares it
		// against current_time('mysql'), the SITE'S OWN LOCAL TIME
		// (WordPress Settings → General → Timezone), not UTC. On a site
		// set to WIB (UTC+7) or any other positive offset, that mismatch
		// made a freshly-created hold look "expired" INSTANTLY, since the
		// UTC held_until value was always numerically behind the site's
		// own local "now" — this is exactly the bug behind a brand new,
		// never-before-touched seat immediately failing at checkout with
		// "no longer held by this session". current_time() (WordPress's
		// own site-local-time helper) on both sides is what actually
		// keeps them talking about the same clock.
		$held_until = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) + self::hold_minutes_for_chart( $chart_id ) * MINUTE_IN_SECONDS );
		$held   = array();
		$failed = array();

		foreach ( $seat_ids as $seat_id ) {
			$seat_id = intval( $seat_id );

			// Already held by this same session (e.g. re-submitting) counts as success.
			$already_mine = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i
				 WHERE id = %d AND chart_id = %d AND status = \'held\' AND held_by = %s', $wpdb->seatt_seats, $seat_id, $chart_id, $session_id ) );
			if ( $already_mine ) {
				// Refresh the hold timer.
				$wpdb->update( $wpdb->seatt_seats,
					array( 'held_until' => $held_until ),
					array( 'id' => $seat_id )
				);
				$held[] = $seat_id;
				continue;
			}

			$affected = $wpdb->query( $wpdb->prepare( 'UPDATE %i
				 SET status = \'held\', held_by = %s, held_until = %s
				 WHERE id = %d AND chart_id = %d AND status = \'available\' AND category != \'reserved\' AND is_active = 1', $wpdb->seatt_seats, $session_id, $held_until, $seat_id, $chart_id ) );
			// A "Reserved" seat is blocked from sale entirely — this is
			// the actual enforcement point, not just booking.js hiding
			// the click on the front end. Checked here (the WHERE clause
			// above) rather than trusting the browser, since a request
			// straight to this endpoint bypassing the UI could otherwise
			// still hold one even though it looked "available" in the DB.

			if ( $affected === 1 ) {
				$held[] = $seat_id;
			} else {
				$failed[] = $seat_id;
			}
		}

		return array( 'held' => $held, 'failed' => $failed );
	}

	/**
	 * Release one seat from a session's cart back to available (e.g. usher
	 * deselects it before checkout).
	 */
	public static function release_seat( $seat_id, $session_id ) {
		global $wpdb;
		return $wpdb->query( $wpdb->prepare( 'UPDATE %i
			 SET status = \'available\', held_by = NULL, held_until = NULL
			 WHERE id = %d AND held_by = %s AND status = \'held\'', $wpdb->seatt_seats, $seat_id, $session_id ) );
	}

	/**
	 * Turn a session's held seats into a real booking with one attendee
	 * name per seat.
	 *
	 * A seat only becomes 'sold' once the money is in. This used to mark
	 * every seat sold the instant the booking row was written, which was
	 * written for the cash-at-the-door flow and never revisited when
	 * online gateways arrived. With a gateway it meant an abandoned or
	 * refused checkout locked those seats permanently: sold, no hold
	 * expiry, and nothing to release them. A venue would lose its
	 * inventory a few seats at a time to carts nobody ever paid for.
	 *
	 * Now a booking awaiting payment leaves its seats HELD, with the
	 * hold extended to the payment window. Others cannot take them, and
	 * if the buyer never pays they free themselves. mark_booking_paid()
	 * flips them to 'sold'; cancel_booking() releases them.
	 */
	/** Set by create_booking() when the "new" order was a repeat of one this session just placed. */
	public static $last_was_duplicate = false;

	/**
	 * Places an order — one at a time per browser session, so a double
	 * click, a retried request or a second tab can never turn into two
	 * orders. A repeat of the order this session has just placed (same
	 * event, same seats and tickets) returns that order instead of a new one.
	 */
	public static function create_booking( $event_id, $session_id, $attendees, $buyer_name, $buyer_phone, $buyer_email = '', $usher_user_id = null, $zone_items = array(), $promo_code = '' ) {
		global $wpdb;
		self::$last_was_duplicate = false;
		$lock = 'seatt_book_' . md5( (string) $session_id );
		// Waits (up to 15 s) for this session's other request to finish.
		if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK( %s, 15 )', $lock ) ) ) {
			return new WP_Error( 'seatt_busy', __( 'Your order is already being placed. Please wait a moment.', 'seathive' ), array( 'status' => 409 ) );
		}
		try {
			$existing = self::find_repeat_order( $event_id, $session_id, $attendees, $zone_items );
			if ( $existing ) {
				self::$last_was_duplicate = true;
				return $existing;
			}
			return self::create_booking_now( $event_id, $session_id, $attendees, $buyer_name, $buyer_phone, $buyer_email, $usher_user_id, $zone_items, $promo_code );
		} finally {
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK( %s )', $lock ) );
		}
	}

	/**
	 * The order this session placed for the same event in the last
	 * 30 minutes with exactly the same seats and ticket quantities
	 * (still pending or paid) — or 0.
	 */
	public static function find_repeat_order( $event_id, $session_id, $attendees, $zone_items ) {
		global $wpdb;
		if ( '' === (string) $session_id ) {
			return 0;
		}
		$since = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 30 * MINUTE_IN_SECONDS );
		$ids   = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE event_id = %d AND session_id = %s AND status IN ( \'pending_payment\', \'paid\' ) AND created_at >= %s ORDER BY id DESC LIMIT 5', $wpdb->seatt_bookings, (int) $event_id, (string) $session_id, $since ) );
		if ( ! $ids ) {
			return 0;
		}
		$want_seats = array_map( 'intval', wp_list_pluck( (array) $attendees, 'seat_id' ) );
		sort( $want_seats );
		$want_zones = array();
		foreach ( (array) $zone_items as $zi ) {
			$z = intval( $zi['zone_id'] ?? 0 );
			if ( $z ) {
				$want_zones[ $z ] = ( $want_zones[ $z ] ?? 0 ) + max( 0, intval( $zi['quantity'] ?? 0 ) );
			}
		}
		ksort( $want_zones );
		foreach ( $ids as $id ) {
			$have_seats = array_map( 'intval', $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, (int) $id ) ) );
			sort( $have_seats );
			$have_zones = array();
			foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SELECT zone_id, quantity FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_zones, (int) $id ) ) as $r ) {
				$have_zones[ (int) $r->zone_id ] = ( $have_zones[ (int) $r->zone_id ] ?? 0 ) + (int) $r->quantity;
			}
			ksort( $have_zones );
			if ( $have_seats === $want_seats && $have_zones === $want_zones ) {
				return (int) $id;
			}
		}
		return 0;
	}

	private static function create_booking_now( $event_id, $session_id, $attendees, $buyer_name, $buyer_phone, $buyer_email = '', $usher_user_id = null, $zone_items = array(), $promo_code = '' ) {
		global $wpdb;
		$now = current_time( 'mysql' );

		// Sold on the spot only when no online payment stands between
		// the booking and the money: a free event, or a manual/cash
		// gateway where an usher is taking payment in person.
		$booking_type = get_post_meta( $event_id, '_seatt_booking_type', true ) ?: 'paid';
		$gateway      = get_option( 'seatt_payment_gateway', 'manual' );
		$sell_immediately = ( 'free' === $booking_type || 'manual' === $gateway );

		/**
		 * How long a seat stays reserved while the buyer is at the
		 * gateway. Matches the payment window the gateway is given.
		 *
		 * @param int $minutes Minutes to hold for.
		 */
		$payment_window   = (int) apply_filters( 'seatt_payment_hold_minutes', 60 );
		$payment_deadline = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) + $payment_window * MINUTE_IN_SECONDS );

		// A booking can now be seats-only, zone-tickets-only, or a mix of
		// both in one cart — guard the seat-verification query so an
		// empty $attendees array (zone-only booking) doesn't produce
		// invalid SQL ("WHERE id IN ()").
		$seats = array();
		if ( $attendees ) {
			$seat_ids     = wp_list_pluck( $attendees, 'seat_id' );
			$placeholders = implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) );
			$params       = $seat_ids;
			$params[]     = $session_id;

			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$seats = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i
				 WHERE id IN (' . $placeholders . ') AND status = \'held\' AND held_by = %s', array_merge( array( $wpdb->seatt_seats ), (array) $params ) ), OBJECT_K );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( count( $seats ) !== count( $seat_ids ) ) {
				return new WP_Error( 'seatt_seat_mismatch', __( 'One or more seats are no longer held by this session (they may have expired). Please re-select.', 'seathive' ), array( 'status' => 409 ) );
			}
			// After an order the seats stay held by the same session for the
			// payment window, so the check above alone let this session put
			// them into a second order. A seat this session already has in
			// an open order must not go into another one.
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders is only a list of %d placeholders; the values go through prepare().
			$taken = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i bs JOIN %i b ON b.id = bs.booking_id WHERE bs.seat_id IN (' . $placeholders . ') AND b.session_id = %s AND b.status IN ( \'pending_payment\', \'paid\' )', array_merge( array( $wpdb->seatt_booking_seats, $wpdb->seatt_bookings ), (array) $seat_ids, array( (string) $session_id ) ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			if ( $taken ) {
				return new WP_Error( 'seatt_seat_in_order', __( 'One or more of these seats are already in an order. Please check your email or choose other seats.', 'seathive' ), array( 'status' => 409 ) );
			}
		}

		// Same idea for zone tickets: verify THIS session actually holds
		// (at least) the quantity being checked out for each zone —
		// never trust the client's cart contents blindly.
		$zone_rows = array();
		foreach ( $zone_items as $zi ) {
			$zone_id = intval( $zi['zone_id'] ?? 0 );
			$hold = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE zone_id = %d AND session_id = %s', $wpdb->seatt_zone_holds, $zone_id, $session_id ) );
			if ( ! $hold || (int) $hold->quantity < 1 ) {
				return new WP_Error( 'seatt_zone_hold_missing', __( 'One or more zone ticket selections have expired. Please re-select.', 'seathive' ), array( 'status' => 409 ) );
			}
			$zone = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND is_active = 1', $wpdb->seatt_zones, $zone_id ) );
			if ( ! $zone ) {
				return new WP_Error( 'seatt_zone_not_found', __( 'A zone in your cart no longer exists.', 'seathive' ) );
			}
			$zone_rows[] = array( 'zone' => $zone, 'quantity' => (int) $hold->quantity, 'attendees' => array_slice( (array) ( $zi['attendees'] ?? array() ), 0, (int) $hold->quantity ) );
		}

		// All money — early bird, promo code, service fee, tax — comes
		// from SEATT_Pricing::quote(), the same calculation the cart's
		// preview uses, with every price looked up fresh from the DB.
		$lines = array();
		foreach ( $seats as $s ) {
			$lines[] = array( 'category' => (string) $s->category, 'price' => (float) $s->price, 'qty' => 1 );
		}
		foreach ( $zone_rows as $zr ) {
			$lines[] = array( 'category' => (string) $zr['zone']->category, 'price' => (float) $zr['zone']->price, 'qty' => (int) $zr['quantity'] );
		}
		$quote = SEATT_Pricing::quote( $event_id, $lines, (string) $promo_code, sanitize_email( $buyer_email ) );
		if ( '' !== trim( (string) $promo_code ) && '' !== $quote['promo_error'] ) {
			return new WP_Error( 'seatt_promo', $quote['promo_error'] );
		}
		$total = $quote['total'];

		$wpdb->insert( $wpdb->seatt_bookings, array(
			'event_id'      => $event_id,
			'session_id'    => $session_id,
			'usher_user_id' => $usher_user_id,
			'buyer_name'    => sanitize_text_field( $buyer_name ),
			'buyer_phone'   => sanitize_text_field( $buyer_phone ),
			'buyer_email'   => sanitize_email( $buyer_email ),
			'status'        => 'pending_payment',
			'total'         => $total,
			'subtotal'      => $quote['subtotal'],
			'discount'      => $quote['early_bird'] + $quote['discount'],
			'fee'           => $quote['fee'],
			'tax'           => $quote['tax'],
			'promo_code'    => $quote['promo_code'] ? $quote['promo_code'] : null,
			'checkin_token' => wp_generate_password( 24, false ),
			'created_at'    => $now,
			'updated_at'    => $now,
		) );
		$booking_id = $wpdb->insert_id;

		foreach ( $attendees as $a ) {
			$seat = $seats[ intval( $a['seat_id'] ) ];
			$wpdb->insert( $wpdb->seatt_booking_seats, array(
				'booking_id'     => $booking_id,
				'seat_id'        => $seat->id,
				'attendee_name'  => sanitize_text_field( $a['attendee_name'] ),
				'attendee_email' => ! empty( $a['attendee_email'] ) ? sanitize_email( $a['attendee_email'] ) : null,
				'attendee_birthdate' => ! empty( $a['attendee_birthdate'] ) && class_exists( 'SEATT_Attendees' ) && SEATT_Attendees::clean_birthdate( $a['attendee_birthdate'] ) ? $a['attendee_birthdate'] : null,
				'attendee_note'  => sanitize_text_field( $a['attendee_note'] ?? '' ),
				'price'          => $seat->price,
			) );

			if ( $sell_immediately ) {
				$wpdb->update( $wpdb->seatt_seats,
					array( 'status' => 'sold', 'held_by' => null, 'held_until' => null ),
					array( 'id' => $seat->id )
				);
			} else {
				// Held for the length of the payment window rather than
				// the short browsing hold, so the seat survives a trip to
				// the gateway but not an abandoned one.
				$wpdb->update( $wpdb->seatt_seats,
					array(
						'status'     => 'held',
						'held_by'    => $session_id,
						'held_until' => $payment_deadline,
					),
					array( 'id' => $seat->id )
				);
			}
		}

		foreach ( $zone_rows as $zr ) {
			$wpdb->insert( $wpdb->seatt_booking_zones, array(
				'booking_id' => $booking_id,
				'zone_id'    => $zr['zone']->id,
				'quantity'   => $zr['quantity'],
				'price_each' => $zr['zone']->price,
			) );
			if ( ! empty( $zr['attendees'] ) && class_exists( 'SEATT_Attendees' ) ) {
				SEATT_Attendees::save_zone( $booking_id, $zr['zone']->id, $zr['attendees'] );
			}
			// The hold's job is done — it's now a real (pending_payment)
			// line item instead, which get_zones_for_event() counts via
			// seatt_booking_zones directly.
			self::release_zone_hold( $zr['zone']->id, $session_id );
		}

		// Fired for EVERY new booking — paid, free, or still
		// pending_payment — unlike 'seatt_booking_paid' below which only
		// fires once money's actually confirmed. This is what "Client/
		// Admin Order Placed Email" (Settings → Email) hook onto: an
		// acknowledgment that an order came in, separate from the
		// receipt that follows once it's paid.
		if ( ! self::$quiet_created ) {
			do_action( 'seatt_booking_created', $booking_id );
		}

		$log_bits = array();
		if ( $attendees ) { $log_bits[] = count( $attendees ) . ' seat(s)'; }
		foreach ( $zone_rows as $zr ) { $log_bits[] = $zr['quantity'] . ' × ' . $zr['zone']->name; }
		self::log_activity( $event_id, $booking_id, $usher_user_id, 'booking_created', implode( ', ', $log_bits ) );

		return $booking_id;
	}

	public static function mark_booking_paid( $booking_id, $payment_ref = '', $payment_method = '' ) {
		global $wpdb;

		// Only the call that actually flips the row counts. A booking can
		// now be confirmed from several places — DOKU's notification, the
		// active status check on the receipt page, the admin screen, the
		// cron sweep — and DOKU itself retries notifications. Without the
		// status guard every one of those re-ran the "paid" side effects
		// and the buyer got a fresh confirmation email each time.
		$result = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'paid\', payment_ref = %s, payment_method = %s, updated_at = %s WHERE id = %d AND status <> \'paid\'', $wpdb->seatt_bookings, (string) $payment_ref, (string) $payment_method, current_time( 'mysql' ), $booking_id ) );
		if ( ! $result ) {
			return $result;
		}

		// The seats become properly sold only now. Until the money
		// arrived they were merely held, so that an abandoned checkout
		// released them instead of locking them for good.
		$seat_ids = $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, $booking_id ) );
		if ( $seat_ids ) {
			// A payment that lands after the seat hold ran out (buyer paid
			// a virtual account hours later, or a notification arrived
			// late) can find its seat already sold to someone else. The
			// money is real so the booking is still confirmed, but staff
			// need to know two tickets now point at one seat.
			$ph_conf   = implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) );
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$conflicts = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT bs.booking_id FROM %i bs JOIN %i b ON b.id = bs.booking_id
				 WHERE b.status = \'paid\' AND b.id <> %d AND bs.seat_id IN ( ' . $ph_conf . ' )', array_merge( array( $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $booking_id ), array_map( 'intval', $seat_ids ) ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			if ( $conflicts ) {
				$bk = self::get_booking( $booking_id );
				self::log_activity(
					$bk ? $bk->event_id : 0,
					$booking_id,
					null,
					'seat_conflict',
					sprintf( 'Paid after its seat hold expired — seat(s) also sold in booking #%s', implode( ', #', array_map( 'intval', $conflicts ) ) )
				);
			}

			$placeholders = implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) );
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'sold\', held_by = NULL, held_until = NULL WHERE id IN ( ' . $placeholders . ' )', array_merge( array( $wpdb->seatt_seats ), (array) $seat_ids ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// Fired here rather than at each of this function's several call
		// sites (gateway webhooks, admin "Mark as Paid", free bookings) —
		// wherever a booking becomes paid, from now on, ends up sending
		// exactly one confirmation email, with no risk of a call site
		// being added later that forgets to trigger it.
		do_action( 'seatt_booking_paid', $booking_id );

		// $payment_method doubles as "who/what did this" here: a real
		// gateway name (xendit/stripe/paypal) means the customer's own payment
		// went through on its own, while 'free' or an admin's manual
		// "Mark as Paid" has no logged-in actor of its own to credit —
		// there's no $user_id parameter on this function to log against,
		// unlike booking_created/checked_in, since payment confirmation
		// is almost always something a GATEWAY WEBHOOK reports well
		// after the fact, not a staff member sitting at a screen.
		$booking = self::get_booking( $booking_id );
		if ( $booking ) {
			self::log_activity( $booking->event_id, $booking_id, null, 'paid', $payment_method ?: 'free' );
		}
		return $result;
	}

	/**
	 * Frees a booking's seats — but ONLY the seats no other live booking
	 * still owns.
	 *
	 * This used to blindly set every seat of the booking to
	 * 'available'. When two bookings pointed at the same seat (a late
	 * payment landing after the seat had been resold — see the
	 * seat_conflict log in mark_booking_paid()), deleting or cancelling
	 * one of them made the seat look free on the seat map even though
	 * the OTHER booking was paid for it. That is exactly how A7 ended
	 * up "Available" while booking #35 was Paid.
	 */
	private static function release_seats_of_booking( $booking_id, $seat_ids ) {
		global $wpdb;
		if ( ! $seat_ids ) {
			return;
		}
		$placeholders = implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$wpdb->query( $wpdb->prepare(
			"UPDATE %i s SET s.status = 'available', s.held_by = NULL, s.held_until = NULL
			 WHERE s.id IN ( $placeholders )
			   AND NOT EXISTS (
			       SELECT 1 FROM %i bs2 JOIN %i b2 ON b2.id = bs2.booking_id
			       WHERE bs2.seat_id = s.id AND b2.id <> %d AND b2.status IN ( 'paid', 'pending_payment' )
			   )",
			array_merge( array( $wpdb->seatt_seats ), array_map( 'intval', $seat_ids ), array( $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, intval( $booking_id ) ) )
		) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// Anything still owned by a paid booking must read as sold.
		self::repair_paid_seats( 0, $seat_ids );
	}

	/**
	 * Self-healing invariant: a seat that belongs to a PAID booking is
	 * always 'sold'. Cheap single UPDATE, run on every seat-map read
	 * (via release_expired_holds()) so whatever put a paid seat back on
	 * sale — a past bug, a manual DB edit, an old plugin version — the
	 * seat map corrects itself the next time anyone looks at it.
	 */
	/**
	 * Before sending a buyer back to pay for an old pending booking,
	 * make sure its seats are still theirs: seats still held by this
	 * booking (or already free) are re-held for a fresh payment window;
	 * if any seat is sold, or held by a different cart, returns false
	 * and changes nothing.
	 */
	public static function reclaim_booking_seats( $booking ) {
		global $wpdb;
		$seat_ids = array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, $booking->id ) ) );
		if ( ! $seat_ids ) {
			return true; // zone-only booking: capacity is counted from the booking itself
		}
		$ph    = implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$seats = $wpdb->get_results( $wpdb->prepare( 'SELECT id, chart_id, status, held_by, held_until FROM %i WHERE id IN ( ' . $ph . ' )', array_merge( array( $wpdb->seatt_seats ), (array) $seat_ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( $seats ) {
			self::release_expired_holds( (int) $seats[0]->chart_id );
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$seats = $wpdb->get_results( $wpdb->prepare( 'SELECT id, status, held_by FROM %i WHERE id IN ( ' . $ph . ' )', array_merge( array( $wpdb->seatt_seats ), (array) $seat_ids ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
		$gateway = get_option( 'seatt_payment_gateway', 'manual' );
		foreach ( (array) $seats as $seat ) {
			$mine = ( 'held' === $seat->status && $seat->held_by === $booking->session_id )
				|| 'available' === $seat->status
				|| ( 'sold' === $seat->status && 'manual' === $gateway && ! self::seat_owned_by_other( $seat->id, $booking->id ) );
			if ( ! $mine ) {
				return false;
			}
		}
		$minutes  = (int) apply_filters( 'seatt_payment_hold_minutes', 60 );
		$deadline = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) + $minutes * MINUTE_IN_SECONDS );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'held\', held_by = %s, held_until = %s WHERE id IN ( ' . $ph . ' ) AND status <> \'sold\'', array_merge( array( $wpdb->seatt_seats, $booking->session_id, $deadline ), $seat_ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return true;
	}

	private static function seat_owned_by_other( $seat_id, $booking_id ) {
		global $wpdb;
		return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT 1 FROM %i bs JOIN %i b ON b.id = bs.booking_id
			 WHERE bs.seat_id = %d AND b.id <> %d AND b.status IN ( \'paid\', \'pending_payment\' ) LIMIT 1', $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $seat_id, $booking_id ) );
	}

	/**
	 * Complimentary ticket: a real, paid (Rp 0) booking with a working
	 * QR code for a sponsor, guest speaker, VIP or crew member — so they
	 * show up in check-in, the seat is truly taken, and nobody has to
	 * "just remember" that seat A7 is the pastor's.
	 *
	 * $labels are seat names as staff see them ("A7, A8"). Returns the
	 * booking id or a WP_Error naming the seats that couldn't be used.
	 */
	/**
	 * Admin ticket for a tickets-only event (or any quantity tickets):
	 * $items = array( zone_id => quantity ). Same rules as seats: paid
	 * straight away, free ("comp") or cash at the venue ("onsite").
	 */
	public static function create_comp_ticket_booking( $event_id, array $items, $guest_name, $guest_phone = '', $guest_email = '', $user_id = null, $mode = 'comp' ) {
		global $wpdb;
		$mode    = 'onsite' === $mode ? 'onsite' : 'comp';
		$session = 'esm-comp-' . wp_generate_password( 24, false );
		$zone_items = array();
		foreach ( $items as $zone_id => $qty ) {
			$zone_id = (int) $zone_id;
			$qty     = (int) $qty;
			if ( $qty < 1 ) {
				continue;
			}
			$ok = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE id = %d AND event_id = %d AND is_active = 1', $wpdb->seatt_zones, $zone_id, (int) $event_id ) );
			if ( ! $ok ) {
				return new WP_Error( 'seatt_bad_ticket', __( 'That ticket type does not belong to this event.', 'seathive' ) );
			}
			$held = self::hold_zone_tickets( $zone_id, $qty, $session );
			if ( is_wp_error( $held ) ) {
				self::release_session_holds( $session );
				return $held;
			}
			$zone_items[] = array( 'zone_id' => $zone_id, 'quantity' => $qty );
		}
		if ( ! $zone_items ) {
			return new WP_Error( 'seatt_no_tickets', __( 'Choose at least one ticket.', 'seathive' ) );
		}
		self::$quiet_created = true;
		try {
			$booking_id = self::create_booking( $event_id, $session, array(), $guest_name, $guest_phone, $guest_email, $user_id, $zone_items, '' );
		} finally {
			self::$quiet_created = false;
		}
		if ( is_wp_error( $booking_id ) ) {
			self::release_session_holds( $session );
			return $booking_id;
		}
		if ( 'comp' === $mode ) {
			$wpdb->update( $wpdb->seatt_bookings, array( 'total' => 0, 'subtotal' => 0 ), array( 'id' => $booking_id ) );
		}
		self::mark_booking_paid( $booking_id, $mode . '-' . intval( $user_id ), $mode );
		return $booking_id;
	}

	/** Drops every zone hold of a session (used when an admin ticket fails half-way). */
	public static function release_session_holds( $session ) {
		global $wpdb;
		$wpdb->delete( $wpdb->seatt_zone_holds, array( 'session_id' => (string) $session ) );
	}

	public static function create_comp_booking( $event_id, array $labels, $guest_name, $guest_phone = '', $guest_email = '', $user_id = null, $mode = 'comp' ) {
		$mode = 'onsite' === $mode ? 'onsite' : 'comp';
		global $wpdb;
		$chart = self::get_chart_for_event( $event_id );
		if ( ! $chart ) {
			return new WP_Error( 'seatt_no_chart', __( 'This event has no seating chart yet.', 'seathive' ) );
		}
		self::release_expired_holds( $chart->id );
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, row_name, label, status FROM %i WHERE chart_id = %d AND is_active = 1', $wpdb->seatt_seats, $chart->id ) );
		$by_name = array();
		foreach ( (array) $rows as $r ) {
			$by_name[ strtolower( seatt_seat_label( $r->row_name, $r->label ) ) ][] = $r;
			$by_name[ strtolower( trim( $r->label ) ) ][] = $r;
		}
		$picked = array();
		$bad    = array();
		foreach ( $labels as $lab ) {
			$key = strtolower( trim( $lab ) );
			if ( '' === $key ) {
				continue;
			}
			$hits = array_values( array_unique( $by_name[ $key ] ?? array(), SORT_REGULAR ) );
			if ( 1 !== count( $hits ) ) {
				$bad[] = $lab . ( $hits ? ' (' . __( 'ambiguous', 'seathive' ) . ')' : ' (' . __( 'not found', 'seathive' ) . ')' );
				continue;
			}
			if ( 'available' !== $hits[0]->status ) {
				$bad[] = $lab . ' (' . __( 'taken', 'seathive' ) . ')';
				continue;
			}
			$picked[ (int) $hits[0]->id ] = $hits[0];
		}
		if ( $bad ) {
			return new WP_Error( 'seatt_comp_seats', sprintf( /* translators: %s: value */ __( 'Could not use: %s', 'seathive' ), implode( ', ', $bad ) ) );
		}
		if ( ! $picked ) {
			return new WP_Error( 'seatt_comp_seats', __( 'Enter at least one seat.', 'seathive' ) );
		}

		$session = 'esm-comp-' . bin2hex( random_bytes( 12 ) );
		$held    = self::hold_seats( $chart->id, array_keys( $picked ), $session );
		if ( is_wp_error( $held ) || ( is_array( $held ) && ! empty( $held['failed'] ) ) ) {
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'available\', held_by = NULL, held_until = NULL WHERE held_by = %s AND status = \'held\'', $wpdb->seatt_seats, $session ) );
			return new WP_Error( 'seatt_comp_seats', __( 'Someone else just took one of those seats — please try again.', 'seathive' ) );
		}
		$attendees = array();
		foreach ( array_keys( $picked ) as $sid ) {
			$attendees[] = array( 'seat_id' => $sid, 'attendee_name' => $guest_name );
		}
		// Created quietly (no "order placed / please pay" emails — this
		// ticket is approved by an admin), then marked paid straight away.
		self::$quiet_created = true;
		try {
			$booking_id = self::create_booking( $event_id, $session, $attendees, $guest_name, $guest_phone, $guest_email, $user_id );
		} finally {
			self::$quiet_created = false;
		}
		if ( is_wp_error( $booking_id ) ) {
			return $booking_id;
		}
		if ( 'comp' === $mode ) {
			// Free ticket.
			$wpdb->update( $wpdb->seatt_bookings, array( 'total' => 0, 'subtotal' => 0 ), array( 'id' => $booking_id ) );
		}
		// 'onsite' keeps the real seat prices: the guest paid cash at the
		// venue, so it counts as revenue in reports.
		self::mark_booking_paid( $booking_id, $mode . '-' . intval( $user_id ), $mode );
		return $booking_id;
	}

	/** Marks a booking refunded and gives its seats back (only seats no
	 * other live booking owns — see release_seats_of_booking()). */
	public static function refund_booking( $booking_id, $note = '' ) {
		global $wpdb;
		$seat_ids = $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, $booking_id ) );
		$wpdb->update( $wpdb->seatt_bookings, array(
			'status'      => 'refunded',
			'refunded_at' => current_time( 'mysql' ),
			'refund_note' => mb_substr( (string) $note, 0, 250 ),
			'updated_at'  => current_time( 'mysql' ),
		), array( 'id' => $booking_id ) );
		self::release_seats_of_booking( $booking_id, $seat_ids );
	}

	/**
	 * Moves one seat of a booking to another seat on the same chart.
	 * The new seat must be available; it takes over the old one's
	 * status (sold/held), and the old seat is freed unless another live
	 * booking still owns it. Returns true or WP_Error.
	 */
	public static function move_booking_seat( $booking_id, $from_seat_id, $to_seat_id ) {
		global $wpdb;
		$link = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE booking_id = %d AND seat_id = %d', $wpdb->seatt_booking_seats, $booking_id, $from_seat_id ) );
		$from = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $wpdb->seatt_seats, $from_seat_id ) );
		$to   = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND is_active = 1', $wpdb->seatt_seats, $to_seat_id ) );
		if ( ! $link || ! $from || ! $to || (int) $from->chart_id !== (int) $to->chart_id ) {
			return new WP_Error( 'seatt_move', __( 'Seat not found on this event\'s chart.', 'seathive' ) );
		}
		self::release_expired_holds( (int) $to->chart_id );
		// Claim the target atomically — it must still be available.
		$claimed = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = %s, held_by = %s, held_until = %s WHERE id = %d AND status = \'available\'', $wpdb->seatt_seats, $from->status, $from->held_by, $from->held_until, $to_seat_id ) );
		if ( ! $claimed ) {
			return new WP_Error( 'seatt_move', __( 'That seat is not available.', 'seathive' ) );
		}
		$wpdb->update( $wpdb->seatt_booking_seats, array( 'seat_id' => $to_seat_id, 'price' => $link->price ), array( 'id' => $link->id ) );
		self::release_seats_of_booking( $booking_id, array( $from_seat_id ) );
		return true;
	}

	public static function repair_paid_seats( $chart_id = 0, $seat_ids = array() ) {
		global $wpdb;
		$where  = '';
		$params = array();
		if ( $chart_id ) {
			$where   .= ' AND s.chart_id = %d';
			$params[] = intval( $chart_id );
		}
		if ( $seat_ids ) {
			$where .= ' AND s.id IN ( ' . implode( ',', array_fill( 0, count( $seat_ids ), '%d' ) ) . ' )';
			$params = array_merge( $params, array_map( 'intval', $seat_ids ) );
		}
		$params = array_merge( array( $wpdb->seatt_seats, $wpdb->seatt_booking_seats, $wpdb->seatt_bookings ), $params );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where only adds %d placeholders whose values are in $params.
		$done = $wpdb->query( $wpdb->prepare(
			"UPDATE %i s
			JOIN %i bs ON bs.seat_id = s.id
			JOIN %i b ON b.id = bs.booking_id
			SET s.status = 'sold', s.held_by = NULL, s.held_until = NULL
			WHERE b.status = 'paid' AND s.status <> 'sold'" . $where,
			$params
		) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $done;
	}

	/**
	 * Cancel a booking: seats go back to available and booking is voided.
	 * Use when payment fails / times out.
	 */
	public static function cancel_booking( $booking_id ) {
		global $wpdb;
		$seat_ids = $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, $booking_id ) );
		self::release_seats_of_booking( $booking_id, $seat_ids );
		$result = $wpdb->update( $wpdb->seatt_bookings,
			array( 'status' => 'cancelled', 'updated_at' => current_time( 'mysql' ) ),
			array( 'id' => $booking_id )
		);
		$booking = self::get_booking( $booking_id );
		if ( $booking ) {
			self::log_activity( $booking->event_id, $booking_id, get_current_user_id() ?: null, 'cancelled' );
		}
		return $result;
	}

	/**
	 * Permanently remove a booking — regardless of its status, including
	 * 'paid' — and free its seats back to 'available' so they can be
	 * booked again. Unlike cancel_booking() (which keeps a 'cancelled'
	 * record), this deletes the booking and its booking_seats rows
	 * entirely. Use for cleaning up test/mistaken bookings.
	 */
	public static function delete_booking( $booking_id ) {
		global $wpdb;

		$seat_ids = $wpdb->get_col( $wpdb->prepare( 'SELECT seat_id FROM %i WHERE booking_id = %d', $wpdb->seatt_booking_seats, $booking_id ) );
		self::release_seats_of_booking( $booking_id, $seat_ids );

		$wpdb->delete( $wpdb->seatt_booking_seats, array( 'booking_id' => $booking_id ) );
		$wpdb->delete( $wpdb->seatt_booking_zones, array( 'booking_id' => $booking_id ) ); // capacity frees up automatically since get_zones_for_event() only counts rows that still join to a real booking, but delete cleanly rather than leave orphans
		return $wpdb->delete( $wpdb->seatt_bookings, array( 'id' => $booking_id ) );
	}

	/**
	 * Secret for the buyer's receipt link (?seatt_booking_id=…&seatt_rk=…).
	 *
	 * The receipt shows the buyer's name, email, phone and — once paid —
	 * the entry QR code, and used to open for ANY booking id with no
	 * secret at all, so counting 1, 2, 3… exposed every buyer. Derived
	 * from the booking's own checkin_token plus the site salt, so no
	 * schema change is needed, and it is deliberately NOT the
	 * checkin_token itself: a receipt link forwarded around shouldn't
	 * double as the raw door-scan secret.
	 */
	/**
	 * Human-friendly invoice number printed on every receipt and email,
	 * e.g. INV-20260926-00035 (booking date + booking id). Buyers type it
	 * into the refund form if an event is cancelled.
	 */
	public static function invoice_number( $booking ) {
		if ( ! $booking || empty( $booking->id ) ) {
			return '';
		}
		$ts = ! empty( $booking->created_at ) ? strtotime( $booking->created_at ) : time();
		return 'INV-' . gmdate( 'Ymd', $ts ) . '-' . str_pad( (string) intval( $booking->id ), 5, '0', STR_PAD_LEFT );
	}

	/** Booking for a typed invoice number, or null. Tolerates spaces and
	 * lower case; the date part must match too, so guessing ids fails. */
	public static function booking_by_invoice( $code ) {
		$code = strtoupper( preg_replace( '/\s+/', '', (string) $code ) );
		if ( ! preg_match( '/^INV-(\d{8})-(\d+)$/', $code, $m ) ) {
			return null;
		}
		$b = self::get_booking( (int) $m[2] );
		return ( $b && self::invoice_number( $b ) === 'INV-' . $m[1] . '-' . str_pad( (string) (int) $m[2], 5, '0', STR_PAD_LEFT ) ) ? $b : null;
	}

	/**
	 * Admin tickets that got stuck on "pending" (older versions stopped
	 * half-way when an email failed). Marks them paid as intended.
	 */
	public static function repair_stuck_admin_tickets() {
		global $wpdb;
		$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE session_id LIKE %s AND status = %s', $wpdb->seatt_bookings, $wpdb->esc_like( 'esm-comp-' ) . '%', 'pending_payment' ) );
		foreach ( (array) $ids as $id ) {
			$wpdb->update( $wpdb->seatt_bookings, array( 'total' => 0 ), array( 'id' => (int) $id ) );
			self::mark_booking_paid( (int) $id, 'comp-0', 'comp' );
		}
		return count( (array) $ids );
	}

	/** "Complimentary — approved by Ana" / "Paid in cash at the venue — approved by Ana", or ''. */
	public static function admin_ticket_label( $booking ) {
		$method = (string) ( $booking->payment_method ?? '' );
		if ( ! in_array( $method, array( 'comp', 'onsite' ), true ) ) {
			return '';
		}
		$uid  = (int) substr( (string) $booking->payment_ref, strlen( $method ) + 1 );
		$user = $uid ? get_userdata( $uid ) : null;
		$text = 'comp' === $method ? __( 'Complimentary', 'seathive' ) : __( 'Paid in cash at the venue', 'seathive' );
		return $user ? sprintf( /* translators: %1$s: value, %2$s: value */ __( '%1$s — approved by %2$s', 'seathive' ), $text, $user->display_name ) : $text;
	}

	/** seat_id => "A7" style labels, for messages. */
	public static function seat_labels( array $ids ) {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
		if ( ! $ids ) {
			return array();
		}
		$ph   = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, row_name, label FROM %i WHERE id IN ( ' . $ph . ' )', array_merge( array( $wpdb->seatt_seats ), (array) $ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$out  = array();
		foreach ( (array) $rows as $r ) {
			$out[ (int) $r->id ] = seatt_seat_label( $r->row_name, $r->label );
		}
		return $out;
	}

	public static function receipt_key( $booking ) {
		if ( ! $booking || empty( $booking->checkin_token ) ) {
			return '';
		}
		$salt = function_exists( 'wp_salt' ) ? wp_salt( 'auth' ) : '';
		return substr( hash_hmac( 'sha256', 'esm-receipt|' . intval( $booking->id ), $booking->checkin_token . $salt ), 0, 20 );
	}

	public static function receipt_key_valid( $booking, $key ) {
		$expected = self::receipt_key( $booking );
		return '' !== $expected && is_string( $key ) && '' !== $key && hash_equals( $expected, $key );
	}

	/** Query args every buyer-facing receipt link must carry. */
	public static function receipt_args( $booking ) {
		return array( 'seatt_booking_id' => intval( $booking->id ), 'seatt_rk' => self::receipt_key( $booking ) );
	}

	public static function get_booking( $booking_id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $wpdb->seatt_bookings, $booking_id ) );
	}

	/**
	 * Booking + one row per seat (with each attendee's name and the
	 * seat's own label/row/category) — everything the confirmation page
	 * needs to display, in one call.
	 */
	public static function get_booking_with_seats( $booking_id ) {
		global $wpdb;
		$booking = self::get_booking( $booking_id );
		if ( ! $booking ) {
			return null;
		}
		$seats = $wpdb->get_results( $wpdb->prepare( 'SELECT bs.seat_id, bs.attendee_name, bs.attendee_note, bs.price,
			        s.label AS seat_label, s.row_name, s.category
			 FROM %i bs
			 JOIN %i s ON s.id = bs.seat_id
			 WHERE bs.booking_id = %d
			 ORDER BY bs.id ASC', $wpdb->seatt_booking_seats, $wpdb->seatt_seats, $booking_id ) );
		$zones = $wpdb->get_results( $wpdb->prepare( 'SELECT bz.quantity, bz.price_each, z.name AS zone_name, z.category
			 FROM %i bz
			 JOIN %i z ON z.id = bz.zone_id
			 WHERE bz.booking_id = %d
			 ORDER BY bz.id ASC', $wpdb->seatt_booking_zones, $wpdb->seatt_zones, $booking_id ) );
		return array( 'booking' => $booking, 'seats' => $seats, 'zones' => $zones );
	}

	/**
	 * Every booking for one event (or every event, when $event_id is 0),
	 * each with its seat list already attached — everything the admin
	 * "Bookings" screen needs, in two queries total regardless of how
	 * many bookings there are.
	 */
	public static function get_bookings_with_seats( $event_id = 0 ) {
		global $wpdb;

		if ( $event_id ) {
			$bookings = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE event_id = %d ORDER BY created_at DESC', $wpdb->seatt_bookings, $event_id ) );
		} else {
			$bookings = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC', $wpdb->seatt_bookings ) );
		}

		if ( ! $bookings ) {
			return array();
		}

		$booking_ids = wp_list_pluck( $bookings, 'id' );
		$placeholders = implode( ',', array_fill( 0, count( $booking_ids ), '%d' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$all_seats = $wpdb->get_results( $wpdb->prepare( 'SELECT bs.booking_id, bs.seat_id, bs.attendee_name, bs.attendee_email, bs.attendee_birthdate, bs.price,
			        s.label AS seat_label, s.row_name, s.category
			 FROM %i bs
			 JOIN %i s ON s.id = bs.seat_id
			 WHERE bs.booking_id IN (' . $placeholders . ')
			 ORDER BY bs.id ASC', array_merge( array( $wpdb->seatt_booking_seats, $wpdb->seatt_seats ), (array) $booking_ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$seats_by_booking = array();
		foreach ( $all_seats as $s ) {
			$seats_by_booking[ $s->booking_id ][] = $s;
		}

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$all_zones = $wpdb->get_results( $wpdb->prepare( 'SELECT bz.booking_id, bz.zone_id, bz.quantity, bz.price_each, z.name AS zone_name
			 FROM %i bz
			 JOIN %i z ON z.id = bz.zone_id
			 WHERE bz.booking_id IN (' . $placeholders . ')
			 ORDER BY bz.id ASC', array_merge( array( $wpdb->seatt_booking_zones, $wpdb->seatt_zones ), (array) $booking_ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$zones_by_booking = array();
		foreach ( $all_zones as $z ) {
			$zones_by_booking[ $z->booking_id ][] = $z;
		}

		// Named holders of quantity tickets (orders of 2+ tickets).
		$holders_by = array();
		if ( class_exists( 'SEATT_Attendees' ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
			$all_h = $wpdb->get_results( $wpdb->prepare( 'SELECT booking_id, zone_id, line_no, name, email, birthdate FROM %i WHERE booking_id IN (' . $placeholders . ') ORDER BY zone_id, line_no', array_merge( array( $wpdb->seatt_booking_attendees ), (array) $booking_ids ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			foreach ( (array) $all_h as $h ) {
				$holders_by[ $h->booking_id ][ $h->zone_id ][] = $h;
			}
		}

		foreach ( $bookings as $b ) {
			$b->seats = $seats_by_booking[ $b->id ] ?? array();
			$b->zones = $zones_by_booking[ $b->id ] ?? array();
			foreach ( $b->zones as $z ) {
				$z->holders = $holders_by[ $b->id ][ $z->zone_id ] ?? array();
			}
			// Every ticket holder of the booking, for lists and search:
			// array of (object) { ticket, name, email, birthdate }.
			$b->holders = array();
			foreach ( $b->seats as $s ) {
				$b->holders[] = (object) array( 'ticket' => seatt_seat_label( $s->row_name, $s->seat_label ), 'name' => (string) $s->attendee_name, 'email' => (string) ( $s->attendee_email ?? '' ), 'birthdate' => (string) ( $s->attendee_birthdate ?? '' ) );
			}
			foreach ( $b->zones as $z ) {
				foreach ( $z->holders as $h ) {
					$b->holders[] = (object) array( 'ticket' => $z->zone_name . ' #' . (int) $h->line_no, 'name' => (string) $h->name, 'email' => (string) $h->email, 'birthdate' => (string) $h->birthdate );
				}
			}
		}

		return $bookings;
	}

	/* ---------------------------------------------------------------
	 * Check-in (event-day attendance)
	 * ------------------------------------------------------------- */

	/**
	 * Looks up a booking by id AND its check-in token together (never by
	 * id alone) — the token is what makes the QR code unguessable, so a
	 * random booking id in the URL bar can't check someone else in.
	 */
	public static function get_booking_by_checkin_token( $booking_id, $token ) {
		global $wpdb;
		if ( ! $booking_id || ! $token ) {
			return null;
		}
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND checkin_token = %s', $wpdb->seatt_bookings, $booking_id, $token ) );
	}

	/**
	 * Marks a booking as arrived. Idempotent — checking in an
	 * already-checked-in booking just returns its original timestamp
	 * rather than overwriting it, so re-scanning the same QR twice is
	 * harmless and still shows staff when the person actually arrived.
	 */
	public static function check_in_booking( $booking_id, $user_id = null, $method = 'manual', $at = null, $all_tickets = true ) {
		global $wpdb;
		// The booking's own QR / "Mark arrived" admits every ticket of the
		// booking that hasn't arrived yet (a single ticket's QR doesn't).
		if ( $all_tickets && class_exists( 'SEATT_Ticket_Codes' ) ) {
			SEATT_Ticket_Codes::check_in_all( $booking_id, $user_id, $at );
		}
		$existing = $wpdb->get_row( $wpdb->prepare( 'SELECT event_id, checked_in_at FROM %i WHERE id = %d', $wpdb->seatt_bookings, $booking_id ) );
		if ( ! $existing ) {
			return new WP_Error( 'seatt_not_found', __( 'Booking not found.', 'seathive' ) );
		}
		if ( $existing->checked_in_at ) {
			return $existing->checked_in_at; // already checked in — return the original time
		}

		// Offline scans are synced later; record when the guest actually
		// walked in, not when the phone got signal back.
		$now = $at ? $at : current_time( 'mysql' );
		$wpdb->update( $wpdb->seatt_bookings, array(
			'checked_in_at' => $now,
			'checked_in_by' => $user_id,
		), array( 'id' => $booking_id ) );
		// $method distinguishes a guest's own QR scan at the door from a
		// staff member manually clicking "Mark Arrived" in the
		// Confirmation Portal — both end up in the same checked_in_at/
		// checked_in_by columns (which only remember the latest state),
		// but the full log is what actually preserves that difference.
		self::log_activity( $existing->event_id, $booking_id, $user_id, 'checked_in', $method );
		return $now;
	}

	/** Attendance list for one event: every booking with its seats AND
	 * check-in status, for the "who has arrived" overview. */
	public static function get_attendance_for_event( $event_id ) {
		$bookings = self::get_bookings_with_seats( $event_id );
		// Only meaningful for confirmed bookings — a still-pending or
		// cancelled booking was never actually a valid ticket to check in.
		return array_values( array_filter( $bookings, function ( $b ) {
			return 'cancelled' !== $b->status;
		} ) );
	}

	/* ==========================================================
	 * Sales analytics — read-only reporting queries. All revenue
	 * figures only ever count 'paid' bookings (never pending/cancelled),
	 * consistent with how the rest of the plugin treats a booking as
	 * "real money" only once it's actually paid.
	 * ========================================================== */

	/** Top-line summary cards: total revenue, number of paid bookings,
	 * and total tickets sold (seats + GA zone quantities combined). */
	public static function get_sales_summary( $event_id = 0 ) {
		global $wpdb;
		$event_id = (int) $event_id; // 0 = all events

		$revenue  = (float) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(total),0) FROM %i WHERE status = 'paid' AND ( %d = 0 OR event_id = %d )", $wpdb->seatt_bookings, $event_id, $event_id ) );
		$bookings = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'paid' AND ( %d = 0 OR event_id = %d )", $wpdb->seatt_bookings, $event_id, $event_id ) );

		$seat_count = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM %i bs
			 JOIN %i b ON b.id = bs.booking_id
			 WHERE b.status = 'paid' AND ( %d = 0 OR b.event_id = %d )",
			$wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $event_id, $event_id
		) );
		$zone_qty = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(bz.quantity),0) FROM %i bz
			 JOIN %i b ON b.id = bz.booking_id
			 WHERE b.status = 'paid' AND ( %d = 0 OR b.event_id = %d )",
			$wpdb->seatt_booking_zones, $wpdb->seatt_bookings, $event_id, $event_id
		) );

		return (object) array(
			'revenue'        => $revenue,
			'paid_bookings'  => $bookings,
			'tickets_sold'   => $seat_count + $zone_qty,
			'avg_order'      => $bookings ? $revenue / $bookings : 0,
		);
	}

	/** Revenue broken down by seat category (Standard/VIP/Reserved/
	 * Standing) AND by each individually named GA zone — merged into
	 * one list sorted by revenue descending, since an admin comparing
	 * "what's actually selling" wants both kinds of ticket side by side
	 * rather than in two separate, hard-to-compare tables. */
	public static function category_label_any( $id ) {
		if ( in_array( $id, array( 'standard', 'vip', 'reserved', 'standing' ), true ) ) {
			return SEATT_Categories::name( 0, $id );
		}
		global $wpdb;
		$post_id = $wpdb->get_var( $wpdb->prepare( 'SELECT post_id FROM %i WHERE meta_key = %s AND meta_value LIKE %s LIMIT 1', $wpdb->postmeta, SEATT_Categories::META, '%' . $wpdb->esc_like( '"' . $id . '"' ) . '%' ) );
		return $post_id ? SEATT_Categories::name( (int) $post_id, $id ) : ucfirst( (string) $id );
	}

	public static function get_sales_by_category( $event_id = 0 ) {
		global $wpdb;
		$event_id = (int) $event_id; // 0 = all events

		$seat_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT s.category AS label, COUNT(*) AS qty, COALESCE(SUM(bs.price),0) AS revenue
			 FROM %i bs
			 JOIN %i b ON b.id = bs.booking_id
			 JOIN %i s ON s.id = bs.seat_id
			 WHERE b.status = 'paid' AND ( %d = 0 OR b.event_id = %d )
			 GROUP BY s.category",
			$wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $wpdb->seatt_seats, $event_id, $event_id
		) );
		foreach ( $seat_rows as $row ) {
			// The event's own category name (e.g. "Festival"); across all
			// events, the name from any event that uses that id.
			$row->label = class_exists( 'SEATT_Categories' ) ? ( $event_id ? SEATT_Categories::name( $event_id, $row->label ) : self::category_label_any( $row->label ) ) : ucfirst( $row->label );
		}

		$zone_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT z.name AS label, COALESCE(SUM(bz.quantity),0) AS qty, COALESCE(SUM(bz.quantity * bz.price_each),0) AS revenue
			 FROM %i bz
			 JOIN %i b ON b.id = bz.booking_id
			 JOIN %i z ON z.id = bz.zone_id
			 WHERE b.status = 'paid' AND ( %d = 0 OR b.event_id = %d )
			 GROUP BY z.id, z.name",
			$wpdb->seatt_booking_zones, $wpdb->seatt_bookings, $wpdb->seatt_zones, $event_id, $event_id
		) );

		$combined = array_merge( $seat_rows, $zone_rows );
		usort( $combined, fn( $a, $b ) => $b->revenue <=> $a->revenue );
		return $combined;
	}

	/** Bookings + revenue per calendar day, most recent first — capped
	 * at the last 30 days so this stays a quick "recent trend" glance
	 * rather than an ever-growing table for a long-running event. */
	public static function get_sales_by_day( $event_id = 0, $days = 30 ) {
		global $wpdb;
		$event_id = (int) $event_id; // 0 = all events
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT DATE(created_at) AS day, COUNT(*) AS bookings, COALESCE(SUM(total),0) AS revenue
			 FROM %i
			 WHERE status = 'paid' AND ( %d = 0 OR event_id = %d ) AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
			 GROUP BY DATE(created_at)
			 ORDER BY day DESC",
			$wpdb->seatt_bookings, $event_id, $event_id, $days
		) );
	}

	/**
	 * Every one of the last $days days, oldest first, zero-filled —
	 * a chart needs the quiet days too, or a gap looks like a line
	 * running straight from one sale to the next. Dates are WordPress-
	 * local (created_at is stored with current_time()), unlike the
	 * older NOW()-based query above which used the MySQL server clock.
	 */
	public static function get_sales_by_day_filled( $event_id = 0, $days = 30 ) {
		global $wpdb;
		$today = current_time( 'Y-m-d' );
		$start = gmdate( 'Y-m-d', strtotime( $today . ' -' . ( $days - 1 ) . ' days' ) );
		$event_id = (int) $event_id; // 0 = all events
		$rows  = $wpdb->get_results( $wpdb->prepare(
			"SELECT DATE(created_at) AS day, COUNT(*) AS bookings, COALESCE(SUM(total),0) AS revenue
			 FROM %i
			 WHERE status = 'paid' AND ( %d = 0 OR event_id = %d ) AND created_at >= %s
			 GROUP BY DATE(created_at)",
			$wpdb->seatt_bookings, $event_id, $event_id, $start . ' 00:00:00'
		) );
		$by_day = array();
		foreach ( (array) $rows as $r ) {
			$by_day[ $r->day ] = $r;
		}
		$out = array();
		for ( $i = 0; $i < $days; $i++ ) {
			$d     = gmdate( 'Y-m-d', strtotime( $start . ' +' . $i . ' days' ) );
			$out[] = (object) array(
				'day'      => $d,
				'bookings' => isset( $by_day[ $d ] ) ? (int) $by_day[ $d ]->bookings : 0,
				'revenue'  => isset( $by_day[ $d ] ) ? (float) $by_day[ $d ]->revenue : 0,
			);
		}
		return $out;
	}

	/** Booking counts per status (paid / pending_payment / cancelled). */
	public static function get_booking_status_counts( $event_id = 0 ) {
		global $wpdb;
		$event_id = (int) $event_id; // 0 = all events
		$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE ( %d = 0 OR event_id = %d ) GROUP BY status', $wpdb->seatt_bookings, $event_id, $event_id ) );
		$out   = array( 'paid' => 0, 'pending_payment' => 0, 'cancelled' => 0 );
		foreach ( (array) $rows as $r ) {
			$out[ $r->status ] = (int) $r->n;
		}
		return $out;
	}

	/** Seat map fill for one event: active seats by status. */
	public static function get_seat_occupancy( $event_id ) {
		global $wpdb;
		$out   = array( 'sold' => 0, 'held' => 0, 'available' => 0 );
		$chart = self::get_chart_for_event( $event_id );
		if ( ! $chart ) {
			return $out;
		}
		self::release_expired_holds( $chart->id );
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE chart_id = %d AND is_active = 1 GROUP BY status', $wpdb->seatt_seats, $chart->id ) );
		foreach ( (array) $rows as $r ) {
			$out[ $r->status ] = ( $out[ $r->status ] ?? 0 ) + (int) $r->n;
		}
		return $out;
	}

	/** Every active seat of an event's chart with its position, status
	 * and whether it was sold as a complimentary ticket — for the
	 * Analytics sales map. */
	public static function get_seat_map( $event_id ) {
		global $wpdb;
		$chart = self::get_chart_for_event( $event_id );
		if ( ! $chart ) {
			return array();
		}
		self::release_expired_holds( $chart->id );
		return $wpdb->get_results( $wpdb->prepare( 'SELECT s.id, s.row_name, s.label, s.status, s.pos_x, s.pos_y,
			        ( SELECT b.payment_method FROM %i bs JOIN %i b ON b.id = bs.booking_id
			          WHERE bs.seat_id = s.id AND b.status = \'paid\' ORDER BY b.id DESC LIMIT 1 ) AS paid_via
			 FROM %i s WHERE s.chart_id = %d AND s.is_active = 1', $wpdb->seatt_booking_seats, $wpdb->seatt_bookings, $wpdb->seatt_seats, $chart->id ) );
	}

	/** Paid bookings, and how many of them have checked in. */
	public static function get_checkin_counts( $event_id = 0 ) {
		global $wpdb;
		$event_id = (int) $event_id; // 0 = all events
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS paid, SUM(CASE WHEN checked_in_at IS NOT NULL THEN 1 ELSE 0 END) AS arrived FROM %i WHERE status = 'paid' AND ( %d = 0 OR event_id = %d )", $wpdb->seatt_bookings, $event_id, $event_id ) );
		return array( 'paid' => (int) ( $row->paid ?? 0 ), 'arrived' => (int) ( $row->arrived ?? 0 ) );
	}

	/* ==========================================================
	 * Waitlist — "let me know if a spot opens up". Deliberately NOT
	 * tied to any specific seat/zone hold the way a real booking is;
	 * this is just a contact request, resolved manually by an admin
	 * (see SEATT_Waitlist::notify_all()) rather than any automatic
	 * "capacity freed up, auto-notify" trigger, which would need to
	 * reason about race conditions between multiple freed spots and
	 * multiple waiting people in a way that's easy to get subtly wrong.
	 * ========================================================== */

	public static function add_to_waitlist( $event_id, $name, $email, $phone, $quantity, $note = '' ) {
		global $wpdb;
		$wpdb->insert( $wpdb->seatt_waitlist, array(
			'event_id'   => $event_id,
			'name'       => $name,
			'email'      => $email,
			'phone'      => $phone,
			'quantity'   => max( 1, intval( $quantity ) ),
			'note'       => $note,
			'status'     => 'waiting',
			'created_at' => current_time( 'mysql' ),
		) );
		return $wpdb->insert_id;
	}

	public static function get_waitlist_for_event( $event_id ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE event_id = %d ORDER BY created_at ASC', $wpdb->seatt_waitlist, $event_id ) );
	}

	public static function mark_waitlist_notified( $ids ) {
		global $wpdb;
		if ( ! $ids ) {
			return;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'notified\' WHERE id IN (' . $placeholders . ')', array_merge( array( $wpdb->seatt_waitlist ), (array) $ids ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	public static function delete_waitlist_entry( $id ) {
		global $wpdb;
		$wpdb->delete( $wpdb->seatt_waitlist, array( 'id' => $id ) );
	}

	/* ==========================================================
	 * Activity log — a full history of what happened to a booking, not
	 * just its current/latest state (checked_in_by/checked_in_at only
	 * ever remember the LATEST check-in, with no record of anything
	 * before it, or of non-check-in events like payment or cancellation
	 * at all). $booking_id and $user_id are both nullable: a few action
	 * types (e.g. a waitlist notification blast) aren't tied to one
	 * specific booking, and an automated/customer-driven action (a
	 * customer paying via a gateway webhook, or scanning their own QR)
	 * has no staff member to attribute it to.
	 * ========================================================== */
	public static function log_activity( $event_id, $booking_id, $user_id, $action, $details = '' ) {
		global $wpdb;
		$row = array(
			'event_id'   => $event_id,
			'booking_id' => $booking_id ?: null,
			'user_id'    => $user_id ?: null,
			'action'     => $action,
			'details'    => $details,
			'created_at' => current_time( 'mysql' ),
		);

		// Captured here rather than passed in by each caller, so no call
		// site can forget it. Both are empty on WP-Cron and WP-CLI runs,
		// which have no request behind them — an empty value is the
		// honest answer there, not a placeholder.
		$extra = array(
			'ip_address' => seatt_client_ip(),
			'user_agent' => seatt_client_user_agent(),
		);

		$inserted = $wpdb->insert( $wpdb->seatt_activity_log, array_merge( $row, $extra ) );

		// If the two newer columns are missing — a schema upgrade that
		// did not run, or a dbDelta that a host refused — the insert
		// above fails and the entry would simply vanish. An audit log
		// that stops recording without saying so is worse than one
		// missing a column, so fall back to writing the entry without
		// them.
		if ( false === $inserted ) {
			$wpdb->insert( $wpdb->seatt_activity_log, $row );
		}
	}

	/** Most recent first, capped at $limit — this is a running history
	 * that only grows, so an event running for months could otherwise
	 * accumulate thousands of rows on one admin screen. */
	public static function get_activity_log( $event_id = 0, $limit = 300 ) {
		global $wpdb;
		// Account-level entries (sign-ins) are stored with event_id 0
		// because they belong to no single event. They are shown
		// alongside whichever event is selected, since the question
		// "who was in the system around the time this happened" is only
		// answerable with both kinds of entry on one timeline.
		$where = $event_id ? $wpdb->prepare( 'WHERE event_id IN ( %d, 0 )', $event_id ) : '';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the interpolated parts are lists of %d/%s placeholders or WHERE fragments built with $wpdb->prepare()
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ' . $where . ' ORDER BY created_at DESC, id DESC LIMIT %d', array_merge( array( $wpdb->seatt_activity_log ), (array) $limit ) ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}
}

SEATT_DB::register_tables();
if ( function_exists( 'add_action' ) ) {
	add_action( 'switch_blog', array( 'SEATT_DB', 'register_tables' ) );
}