<?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.

class SEATT_Activator {

	public static function activate() {
		delete_option( 'seatt_rewrite_ver' ); // rebuild /events/ permalinks on the next load
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		$table_charts        = $wpdb->prefix . 'seatt_charts';
		$table_seats         = $wpdb->prefix . 'seatt_seats';
		$table_bookings      = $wpdb->prefix . 'seatt_bookings';
		$table_booking_seats = $wpdb->prefix . 'seatt_booking_seats';
		$table_templates     = $wpdb->prefix . 'seatt_templates';
		$table_zones         = $wpdb->prefix . 'seatt_zones';
		$table_zone_holds    = $wpdb->prefix . 'seatt_zone_holds';
		$table_booking_zones = $wpdb->prefix . 'seatt_booking_zones';
		$table_waitlist      = $wpdb->prefix . 'seatt_waitlist';
		$table_activity_log  = $wpdb->prefix . 'seatt_activity_log';

		// One chart belongs to one event (post_id of the 'seatt_event' CPT).
		$sql_charts = "CREATE TABLE $table_charts (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_id BIGINT UNSIGNED NOT NULL,
			name VARCHAR(191) NOT NULL DEFAULT 'Main Chart',
			layout_json LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY event_id (event_id)
		) $charset_collate;";

		// Individual bookable seats. type: 'seat' | 'table_seat'.
		// status: available | held | sold. held_by / held_until back a
		// session-scoped cart hold (see the seat-locking design notes).
		// is_active marks a seat that no longer appears in the chart's
		// layout. Such rows are NOT deleted — a sold seat must survive so
		// its booking still resolves — but they must stop being offered.
		$sql_seats = "CREATE TABLE $table_seats (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			chart_id BIGINT UNSIGNED NOT NULL,
			seat_key VARCHAR(64) NOT NULL,
			label VARCHAR(64) NULL,
			row_name VARCHAR(191) NULL,
			category VARCHAR(64) NOT NULL DEFAULT 'standard',
			is_active TINYINT(1) NOT NULL DEFAULT 1,
			color VARCHAR(20) NULL,
			price DECIMAL(12,2) NOT NULL DEFAULT 0,
			pos_x FLOAT NOT NULL DEFAULT 0,
			pos_y FLOAT NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL DEFAULT 'available',
			held_by VARCHAR(64) NULL,
			held_until DATETIME NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY chart_id (chart_id),
			KEY status (status),
			UNIQUE KEY chart_seat (chart_id, seat_key)
		) $charset_collate;";

		// One booking = one cart / one checkout by one usher, can contain
		// several seats each with their own attendee name.
		$sql_bookings = "CREATE TABLE $table_bookings (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_id BIGINT UNSIGNED NOT NULL,
			session_id VARCHAR(64) NOT NULL,
			usher_user_id BIGINT UNSIGNED NULL,
			buyer_name VARCHAR(191) NULL,
			buyer_phone VARCHAR(64) NULL,
			buyer_email VARCHAR(191) NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			total DECIMAL(12,2) NOT NULL DEFAULT 0,
			subtotal DECIMAL(12,2) NULL,
			discount DECIMAL(12,2) NOT NULL DEFAULT 0,
			fee DECIMAL(12,2) NOT NULL DEFAULT 0,
			tax DECIMAL(12,2) NOT NULL DEFAULT 0,
			promo_code VARCHAR(64) NULL,
			reminder_sent_at DATETIME NULL,
			refunded_at DATETIME NULL,
			refund_note VARCHAR(255) NULL,
			refund_requested_at DATETIME NULL,
			custom_data LONGTEXT NULL,
			payment_ref VARCHAR(191) NULL,
			payment_method VARCHAR(64) NULL,
			checkin_token VARCHAR(64) NULL,
			checked_in_at DATETIME NULL,
			checked_in_by BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY session_id (session_id),
			KEY status (status),
			KEY checkin_token (checkin_token)
		) $charset_collate;";

		$sql_booking_seats = "CREATE TABLE $table_booking_seats (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			booking_id BIGINT UNSIGNED NOT NULL,
			seat_id BIGINT UNSIGNED NOT NULL,
			attendee_name VARCHAR(191) NOT NULL,
			attendee_email VARCHAR(191) NULL,
			attendee_birthdate DATE NULL,
			attendee_note VARCHAR(191) NULL,
			price DECIMAL(12,2) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY booking_id (booking_id),
			KEY seat_id (seat_id)
		) $charset_collate;";

		// Reusable Chart Builder layouts — either the built-in ones (like
		// "Auditorium 1") or ones an admin saved themselves via "Save as
		// Template". Not tied to any one event: the whole point is being
		// able to drop the same layout into a different event's chart.
		// layout_json holds the same per-object array shape a chart's own
		// layout_json does (seat-row/table/icon/label entries) — just not
		// wrapped in a specific event's seats table rows until it's
		// actually placed onto a chart and saved there.
		$sql_templates = "CREATE TABLE $table_templates (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(191) NOT NULL,
			layout_json LONGTEXT NOT NULL,
			thumbnail LONGTEXT NULL,
			created_by BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id)
		) $charset_collate;";

		// --- General Admission (GA) zones -----------------------------
		// A Zone is a whole AREA sold by capacity + price, not individual
		// numbered seats — "Festival Floor", "Standing Zone A", etc.
		// Materialized from the chart's layout_json the same way
		// seatt_seats is: drawing data lives in layout_json, this table is
		// the actual per-event bookable inventory synced on chart save.
		$sql_zones = "CREATE TABLE $table_zones (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_id BIGINT UNSIGNED NOT NULL,
			zone_key VARCHAR(64) NOT NULL,
			name VARCHAR(191) NOT NULL,
			category VARCHAR(20) NOT NULL DEFAULT 'standard',
			color VARCHAR(20) NULL,
			price DECIMAL(12,2) NOT NULL DEFAULT 0,
			capacity INT UNSIGNED NOT NULL DEFAULT 0,
			is_active TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			UNIQUE KEY event_zone (event_id, zone_key)
		) $charset_collate;";

		// Zones don't have individual seat identity, so a hold here is
		// "N tickets in this zone, held by this session" rather than a
		// single row flipping to 'held' the way seatt_seats works — several
		// sessions can each be mid-checkout on the same zone at once, so
		// remaining availability is capacity minus the SUM of every
		// still-live row below (plus already-sold quantities).
		$sql_zone_holds = "CREATE TABLE $table_zone_holds (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			zone_id BIGINT UNSIGNED NOT NULL,
			session_id VARCHAR(64) NOT NULL,
			quantity INT UNSIGNED NOT NULL,
			held_until DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY zone_id (zone_id),
			KEY session_id (session_id)
		) $charset_collate;";

		// The zone-ticket equivalent of seatt_booking_seats — a booking can
		// mix regular numbered seats AND GA zone tickets together in one
		// cart/checkout, so this is a parallel line-items table rather
		// than trying to force zone tickets into the seat-shaped one.
		// One QR per ticket: each seat / each unit of a quantity ticket.
		$table_tickets = $wpdb->prefix . 'seatt_tickets';
		$sql_tickets = "CREATE TABLE $table_tickets (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			booking_id BIGINT UNSIGNED NOT NULL,
			ticket_no INT UNSIGNED NOT NULL DEFAULT 1,
			seat_id BIGINT UNSIGNED NULL,
			zone_id BIGINT UNSIGNED NULL,
			line_no INT UNSIGNED NOT NULL DEFAULT 1,
			holder_name VARCHAR(191) NOT NULL DEFAULT '',
			holder_email VARCHAR(191) NULL,
			token VARCHAR(64) NOT NULL,
			checked_in_at DATETIME NULL,
			checked_in_by BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY booking_id (booking_id)
		) $charset_collate;";

		// One row per named holder of a quantity ticket (ticket-only mode
		// and GA zones) when an order has more than one ticket.
		$table_booking_attendees = $wpdb->prefix . 'seatt_booking_attendees';
		$sql_booking_attendees = "CREATE TABLE $table_booking_attendees (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			booking_id BIGINT UNSIGNED NOT NULL,
			zone_id BIGINT UNSIGNED NOT NULL,
			line_no INT UNSIGNED NOT NULL DEFAULT 1,
			name VARCHAR(191) NOT NULL,
			email VARCHAR(191) NULL,
			birthdate DATE NULL,
			PRIMARY KEY  (id),
			KEY booking_id (booking_id)
		) $charset_collate;";

		$sql_booking_zones = "CREATE TABLE $table_booking_zones (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			booking_id BIGINT UNSIGNED NOT NULL,
			zone_id BIGINT UNSIGNED NOT NULL,
			quantity INT UNSIGNED NOT NULL,
			price_each DECIMAL(12,2) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY booking_id (booking_id),
			KEY zone_id (zone_id)
		) $charset_collate;";

		// A customer's request to be told if a spot opens up — not tied
		// to any specific seat/zone hold the way a real booking is, just
		// a name/contact + how many tickets they're hoping for. Status
		// starts 'waiting' and moves to 'notified' once an admin sends
		// the "spots opened up" email (see SEATT_Waitlist::notify_all()).
		$sql_waitlist = "CREATE TABLE $table_waitlist (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_id BIGINT UNSIGNED NOT NULL,
			name VARCHAR(191) NOT NULL,
			email VARCHAR(191) NOT NULL,
			phone VARCHAR(50) NOT NULL DEFAULT '',
			quantity INT UNSIGNED NOT NULL DEFAULT 1,
			note VARCHAR(255) NOT NULL DEFAULT '',
			status VARCHAR(20) NOT NULL DEFAULT 'waiting',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY status (status)
		) $charset_collate;";

		// Every meaningful thing that happens to a booking, in order —
		// not just "who most recently checked this guest in" (which
		// bookings.checked_in_by/checked_in_at already covered, but only
		// ever remembers the LATEST state, with no way to see what
		// happened before it or by whom). booking_id is nullable since a
		// couple of action types (e.g. a waitlist notification blast)
		// aren't tied to one specific booking.
		// NOTE: no SQL comments inside these strings. dbDelta() reads the
		// first word of every line as a column name, so a `--` comment
		// silently corrupts the parse and the columns below it are never
		// created — with no error, and with the upgrade still marked
		// complete. ip_address is 45 chars to hold an IPv6 address in
		// full; user_agent is kept raw and summarised only for display.
		$sql_activity_log = "CREATE TABLE $table_activity_log (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_id BIGINT UNSIGNED NOT NULL,
			booking_id BIGINT UNSIGNED NULL,
			user_id BIGINT UNSIGNED NULL,
			action VARCHAR(40) NOT NULL,
			details VARCHAR(255) NOT NULL DEFAULT '',
			ip_address VARCHAR(45) NOT NULL DEFAULT '',
			user_agent VARCHAR(255) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY booking_id (booking_id),
			KEY action (action)
		) $charset_collate;";

		dbDelta( $sql_charts );
		dbDelta( $sql_seats );
		dbDelta( $sql_bookings );
		dbDelta( $sql_booking_seats );
		dbDelta( $sql_templates );
		dbDelta( $sql_zones );
		dbDelta( $sql_zone_holds );
		dbDelta( $sql_booking_zones );
		dbDelta( $sql_booking_attendees );
		dbDelta( $sql_tickets );
		dbDelta( $sql_waitlist );
		dbDelta( $sql_activity_log );
		$sql_promos = 'CREATE TABLE ' . $wpdb->prefix . "seatt_promos (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			code VARCHAR(64) NOT NULL,
			type VARCHAR(10) NOT NULL DEFAULT 'percent',
			value DECIMAL(12,2) NOT NULL DEFAULT 0,
			event_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			category VARCHAR(40) NOT NULL DEFAULT '',
			max_uses INT UNSIGNED NOT NULL DEFAULT 0,
			per_email INT UNSIGNED NOT NULL DEFAULT 0,
			min_tickets INT UNSIGNED NOT NULL DEFAULT 0,
			starts_at DATETIME NULL,
			ends_at DATETIME NULL,
			active TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY code (code)
		) $charset_collate;";
		dbDelta( $sql_promos );

		SEATT_Roles::add_roles();

		self::ensure_columns();
		self::deactivate_orphaned_seats();
		self::release_unpaid_seats();
		if ( class_exists( 'SEATT_Security' ) ) {
			SEATT_Security::migrate_plaintext_secrets();
		}
		// WhatsApp reminders were removed in 3.2.0: drop their settings (incl. the stored Fonnte token).
		delete_option( 'seatt_whatsapp_provider' );
		delete_option( 'seatt_fonnte_token' );
		update_option( 'seatt_db_version', SEATT_VERSION );
		// First install only (no events yet): open the setup wizard once.
		if ( class_exists( 'SEATT_Setup_Wizard' ) && ! get_posts( array( 'post_type' => 'seatt_event', 'post_status' => 'any', 'numberposts' => 1, 'fields' => 'ids' ) ) ) {
			SEATT_Setup_Wizard::flag_redirect();
		}
	}

	public static function deactivate() {
		// Intentionally not dropping tables or roles on deactivation so
		// data survives a temporary disable. Provide a separate "uninstall"
		// routine (uninstall.php) if full cleanup is ever wanted.
	}

	/**
	 * Adds any column dbDelta failed to create.
	 *
	 * dbDelta is quiet about failure. When it could not parse the
	 * activity-log statement, the two new columns were simply never
	 * created — and because the routine still wrote the new version
	 * number, nothing ever tried again. Every insert that named those
	 * columns then failed, and the log stopped recording, all without a
	 * single error surfacing anywhere.
	 *
	 * This checks the columns that actually exist and issues a plain
	 * ALTER for the ones that do not. It is safe to run on every
	 * upgrade: with nothing missing it does no work at all.
	 */
	private static function ensure_columns() {
		global $wpdb;

		$expected = array(
			$wpdb->seatt_activity_log => array(
				'ip_address' => "VARCHAR(45) NOT NULL DEFAULT ''",
				'user_agent' => "VARCHAR(255) NOT NULL DEFAULT ''",
			),
			$wpdb->seatt_seats => array(
				'is_active' => 'TINYINT(1) NOT NULL DEFAULT 1',
			),
			$wpdb->seatt_zones => array(
				'is_active' => 'TINYINT(1) NOT NULL DEFAULT 1',
			),
			$wpdb->seatt_bookings => array(
				'subtotal'         => 'DECIMAL(12,2) NULL',
				'discount'         => 'DECIMAL(12,2) NOT NULL DEFAULT 0',
				'fee'              => 'DECIMAL(12,2) NOT NULL DEFAULT 0',
				'tax'              => 'DECIMAL(12,2) NOT NULL DEFAULT 0',
				'promo_code'       => 'VARCHAR(64) NULL',
				'reminder_sent_at' => 'DATETIME NULL',
				'refunded_at'      => 'DATETIME NULL',
				'refund_note'      => 'VARCHAR(255) NULL',
				'refund_requested_at' => 'DATETIME NULL',
				'custom_data'      => 'LONGTEXT NULL',
			),
		);

		foreach ( $expected as $table => $columns ) {
			// A table that does not exist yet is about to be created by
			// dbDelta with these columns already in it.
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				continue;
			}
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange -- schema upkeep on activation; table/column names are fixed strings from this file
			$existing = (array) $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
			foreach ( $columns as $name => $definition ) {
				if ( in_array( $name, $existing, true ) ) {
					continue;
				}
				// Table and column names come from this file, never from
				// a request, so there is nothing here to parameterise.
				// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange -- schema upkeep on activation; table/column names are fixed strings from this file
				$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN %i ' . $definition, $table, $name ) );
				// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
			}
		}
	}

	/**
	 * Flags seat rows that are no longer in their chart's layout.
	 *
	 * sync_seats() only ever updated and inserted; it never marked
	 * anything removed, despite save_chart()'s own docblock promising
	 * that it did. So every seat ever created for a chart stayed in the
	 * table and stayed bookable. Rebuild a chart with a new layout and
	 * the buyer page received BOTH sets — the new seats and every
	 * superseded one, drawn at their old coordinates in their old
	 * colours, on top of each other.
	 *
	 * Run once here so existing sites are corrected without having to
	 * re-save every chart by hand. Nothing is deleted: a seat attached
	 * to a booking has to survive for that booking to still resolve.
	 */
	private static function deactivate_orphaned_seats() {
		global $wpdb;

		$charts_table = $wpdb->seatt_charts;
		$seats_table  = $wpdb->seatt_seats;
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $seats_table ) ) !== $seats_table ) {
			return;
		}

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange -- schema upkeep on activation; table/column names are fixed strings from this file
		$charts = $wpdb->get_results( $wpdb->prepare( 'SELECT id, layout_json FROM %i', $charts_table ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
		foreach ( (array) $charts as $chart ) {
			$layout = json_decode( (string) $chart->layout_json, true );
			if ( ! is_array( $layout ) ) {
				continue;
			}

			$keys = array();
			foreach ( $layout as $obj ) {
				if ( ! empty( $obj['type'] ) && in_array( $obj['type'], array( 'seat', 'table_seat' ), true ) && ! empty( $obj['id'] ) ) {
					$keys[] = (string) $obj['id'];
				}
			}

			// An empty layout means every seat is orphaned — but that is
			// also what a chart mid-edit looks like, so leave it alone
			// rather than silently emptying a venue.
			if ( ! $keys ) {
				continue;
			}

			$placeholders = implode( ',', array_fill( 0, count( $keys ), '%s' ) );
			$args         = array_merge( array( $chart->id ), $keys );

			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange -- schema upkeep on activation; table/column names are fixed strings from this file
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 0 WHERE chart_id = %d AND seat_key NOT IN ( ' . $placeholders . ' )', array_merge( array( $seats_table ), (array) $args ) ) );
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 1 WHERE chart_id = %d AND seat_key IN ( ' . $placeholders . ' )', array_merge( array( $seats_table ), (array) $args ) ) );
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
		}
	}

	/**
	 * Frees seats locked by a booking that was never paid for.
	 *
	 * create_booking() used to mark seats 'sold' the moment the booking
	 * row was written — correct for the cash-at-the-door flow it was
	 * written for, wrong once an online gateway sat between the booking
	 * and the money. Every abandoned or refused checkout left its seats
	 * sold, with no hold to expire and nothing to release them.
	 *
	 * Deliberately narrow. Only seats belonging to a booking that is
	 * still pending_payment are touched, and only those with no other
	 * booking claiming them. A seat on a paid or cancelled booking is
	 * left exactly as it is — freeing a seat somebody actually paid for
	 * would be far worse than leaving one locked.
	 */
	private static function release_unpaid_seats() {
		global $wpdb;

		$seats    = $wpdb->seatt_seats;
		$bookings = $wpdb->seatt_bookings;
		$bseats   = $wpdb->seatt_booking_seats;

		foreach ( array( $seats, $bookings, $bseats ) as $table ) {
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				return;
			}
		}

		// With the Cash gateway a pending booking's seats are sold on
		// purpose (the buyer pays at the desk, admin confirms later).
		// Running this sweep there would put every unconfirmed cash seat
		// back on sale on each plugin update.
		if ( 'manual' === get_option( 'seatt_payment_gateway', 'manual' ) ) {
			SEATT_DB::repair_paid_seats();
			return;
		}

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange -- schema upkeep on activation; table/column names are fixed strings from this file
		$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 = \'available\', s.held_by = NULL, s.held_until = NULL
			 WHERE s.status = \'sold\'
			   AND b.status = \'pending_payment\'
			   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.status = \'paid\'
			   )', $seats, $bseats, $bookings, $bseats, $bookings ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
		SEATT_DB::repair_paid_seats();
	}
}