<?php
/**
 * BitForgeNet Guest Tickets - Order (subscriber) model.
 *
 * An order is one payment. It carries one or more attendees, and each attendee
 * receives their own uniquely-numbered ticket and QR code — this is how a single
 * family payment produces five distinct, individually scannable tickets.
 *
 * @package BitForgeNet_Guest_Tickets
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

// This file talks to the plugin's own tables. WordPress has no API for them --
// they are not posts and not meta -- so $wpdb is the only way in. The rows
// change on every booking and every scan, which makes an object cache wrong
// more often than it is right. Table names are built from $wpdb->prefix and
// never from a request, so interpolating one carries nothing to inject; every
// value goes through $wpdb->prepare(), and the few identifiers that cannot
// (ORDER BY columns) are matched against a whitelist first.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter


/**
 * Data access and state machine for orders.
 */
class BFNET_Order {

	/**
	 * Table name.
	 *
	 * @return string
	 */
	public static function table() {
		return BFNET_Install::table( 'orders' );
	}

	/**
	 * Fetch one order.
	 *
	 * @param int $id Order id.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Fetch one order by reference.
	 *
	 * @param string $reference Order reference.
	 * @return object|null
	 */
	public static function get_by_reference( $reference ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE reference = %s", (string) $reference ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Fetch one order by uuid.
	 *
	 * @param string $uuid Order uuid.
	 * @return object|null
	 */
	public static function get_by_uuid( $uuid ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE uuid = %s", (string) $uuid ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Query orders.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public static function query( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'event_id'       => 0,
				'payment_status' => '',
				'search'         => '',
				'orderby'        => 'created_at',
				'order'          => 'DESC',
				'limit'          => 50,
				'offset'         => 0,
			)
		);

		$table  = self::table();
		$where  = array( '1=1' );
		$params = array();

		if ( $args['event_id'] ) {
			$where[]  = 'event_id = %d';
			$params[] = (int) $args['event_id'];
		}
		if ( $args['payment_status'] ) {
			$where[]  = 'payment_status = %s';
			$params[] = $args['payment_status'];
		}
		if ( '' !== $args['search'] ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR phone LIKE %s OR reference LIKE %s)';
			$params   = array_merge( $params, array( $like, $like, $like, $like, $like ) );
		}

		$allowed = array( 'created_at', 'total_cents', 'quantity', 'last_name', 'id', 'paid_at' );
		$orderby = in_array( $args['orderby'], $allowed, true ) ? $args['orderby'] : 'created_at';
		$order   = ( 'ASC' === strtoupper( $args['order'] ) ) ? 'ASC' : 'DESC';

		$params[] = max( 1, (int) $args['limit'] );
		$params[] = max( 0, (int) $args['offset'] );

		$sql = "SELECT * FROM {$table} WHERE " . implode( ' AND ', $where ) . " ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Count orders matching filters.
	 *
	 * @param array $args Filters (event_id, payment_status, search).
	 * @return int
	 */
	public static function count( $args = array() ) {
		global $wpdb;

		$args   = wp_parse_args( $args, array( 'event_id' => 0, 'payment_status' => '', 'search' => '' ) );
		$table  = self::table();
		$where  = array( '1=1' );
		$params = array();

		if ( $args['event_id'] ) {
			$where[]  = 'event_id = %d';
			$params[] = (int) $args['event_id'];
		}
		if ( $args['payment_status'] ) {
			$where[]  = 'payment_status = %s';
			$params[] = $args['payment_status'];
		}
		if ( '' !== $args['search'] ) {
			$like    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[] = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR phone LIKE %s OR reference LIKE %s)';
			$params  = array_merge( $params, array( $like, $like, $like, $like, $like ) );
		}

		$sql = "SELECT COUNT(*) FROM {$table} WHERE " . implode( ' AND ', $where );

		if ( empty( $params ) ) {
			return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL
		}
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Attendees attached to an order.
	 *
	 * @param int $order_id Order id.
	 * @return array
	 */
	public static function attendees( $order_id ) {
		global $wpdb;
		$table = BFNET_Install::table( 'attendees' );
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE order_id = %d ORDER BY position ASC, id ASC", (int) $order_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Tickets attached to an order.
	 *
	 * @param int $order_id Order id.
	 * @return array
	 */
	public static function tickets( $order_id ) {
		global $wpdb;
		$table = BFNET_Install::table( 'tickets' );
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE order_id = %d ORDER BY id ASC", (int) $order_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/* --------------------------------------------------------------------- */
	/* Creation                                                              */
	/* --------------------------------------------------------------------- */

	/**
	 * Create an order together with its attendees.
	 *
	 * @param array $data {
	 *     @type int    $event_id  Event id.
	 *     @type string $first_name Buyer first name.
	 *     @type string $last_name  Buyer last name.
	 *     @type string $email      Buyer email.
	 *     @type string $phone      Buyer phone.
	 *     @type array  $guests     List of ['first_name','last_name','ticket_type_id','category'].
	 * }
	 * @return int|WP_Error Order id or error.
	 */
	public static function create( array $data ) {
		global $wpdb;

		$event_id = isset( $data['event_id'] ) ? (int) $data['event_id'] : 0;
		$event    = BFNET_Event::get( $event_id );

		if ( ! $event ) {
			return new WP_Error( 'bfnet_no_event', __( 'This event no longer exists.', 'bitforgenet-guest-tickets' ) );
		}

		$first = BFNET_Security::clean_name( isset( $data['first_name'] ) ? $data['first_name'] : '' );
		$last  = BFNET_Security::clean_name( isset( $data['last_name'] ) ? $data['last_name'] : '' );
		$email = BFNET_Security::clean_email( isset( $data['email'] ) ? $data['email'] : '' );
		$phone = BFNET_Security::clean_phone( isset( $data['phone'] ) ? $data['phone'] : '' );

		if ( '' === $first || '' === $last ) {
			return new WP_Error( 'bfnet_name_required', __( 'Please provide your first and last name.', 'bitforgenet-guest-tickets' ) );
		}
		if ( '' === $email ) {
			return new WP_Error( 'bfnet_email_required', __( 'Please provide a valid email address — your tickets are sent there.', 'bitforgenet-guest-tickets' ) );
		}
		if ( (int) BFNET_Settings::get( 'require_phone', 1 ) && '' === $phone ) {
			return new WP_Error( 'bfnet_phone_required', __( 'Please provide a phone number so we can reach you on WhatsApp.', 'bitforgenet-guest-tickets' ) );
		}

		// Normalise the guest list.
		$guests = isset( $data['guests'] ) && is_array( $data['guests'] ) ? array_values( $data['guests'] ) : array();
		$guests = array_slice( $guests, 0, max( 1, (int) $event->max_per_order ) );

		if ( empty( $guests ) ) {
			$guests = array(
				array(
					'first_name' => $first,
					'last_name'  => $last,
					'email'      => $email,
					'phone'      => $phone,
				),
			);
		}

		$types      = BFNET_Offers::tiers( $event_id );
		$type_map   = array();
		foreach ( $types as $type ) {
			$type_map[ (int) $type->id ] = $type;
		}
		$default_type = $types ? $types[0] : null;

		$clean_guests = array();
		$subtotal     = 0;

		$min_age  = (int) $event->min_age;
		$age_mode = isset( $event->age_check_mode ) ? $event->age_check_mode : 'off';

		// The local calendar date of the event is what the age is measured against.
		$event_date = BFNET_Util::event_date( $event, 'starts_at', 'Y-m-d' );
		$event_date = ( '—' === $event_date ) ? '' : $event_date;

		foreach ( $guests as $index => $guest ) {
			$g_first = BFNET_Security::clean_name( isset( $guest['first_name'] ) ? $guest['first_name'] : '' );
			$g_last  = BFNET_Security::clean_name( isset( $guest['last_name'] ) ? $guest['last_name'] : '' );

			if ( 0 === $index ) {
				$g_first = $g_first ? $g_first : $first;
				$g_last  = $g_last ? $g_last : $last;
			}

			if ( '' === $g_first && '' === $g_last ) {
				/* translators: %d: guest number. */
				return new WP_Error( 'bfnet_guest_name', sprintf( __( 'Please provide the name of guest %d.', 'bitforgenet-guest-tickets' ), $index + 1 ) );
			}

			$type_id = isset( $guest['ticket_type_id'] ) ? (int) $guest['ticket_type_id'] : 0;
			$type    = isset( $type_map[ $type_id ] ) ? $type_map[ $type_id ] : $default_type;
			$price   = BFNET_Offers::price( $event, $type );

			// Age gate. The browser checks this too, but the server is the authority.
			$birth_date = '';
			if ( $min_age > 0 && 'dob' === $age_mode ) {
				$birth_date = self::clean_birth_date( isset( $guest['birth_date'] ) ? $guest['birth_date'] : '' );

				if ( ! $birth_date ) {
					/* translators: %s: guest name. */
					return new WP_Error( 'bfnet_dob_required', sprintf( __( 'Please give a valid date of birth for %s.', 'bitforgenet-guest-tickets' ), trim( $g_first . ' ' . $g_last ) ) );
				}

				$age = self::age_on( $birth_date, $event_date );

				if ( null === $age || $age < $min_age ) {
					return new WP_Error(
						'bfnet_under_age',
						sprintf(
							/* translators: 1: guest name, 2: minimum age. */
							__( '%1$s does not meet the minimum age for this event (%2$d and over). Under-age guests cannot be admitted.', 'bitforgenet-guest-tickets' ),
							trim( $g_first . ' ' . $g_last ),
							$min_age
						)
					);
				}
			} elseif ( isset( $guest['birth_date'] ) ) {
				$birth_date = self::clean_birth_date( $guest['birth_date'] );
			}

			$clean_guests[] = array(
				'birth_date'     => $birth_date,
				'first_name'     => $g_first,
				'last_name'      => $g_last,
				'email'          => 0 === $index ? $email : BFNET_Security::clean_email( isset( $guest['email'] ) ? $guest['email'] : '' ),
				'phone'          => 0 === $index ? $phone : BFNET_Security::clean_phone( isset( $guest['phone'] ) ? $guest['phone'] : '' ),
				'ticket_type_id' => $type ? (int) $type->id : 0,
				'category'       => isset( $guest['category'] ) ? sanitize_key( $guest['category'] ) : 'standard',
			);

			$subtotal += $price;
		}

		if ( $min_age > 0 && 'declare' === $age_mode && empty( $data['age_confirmed'] ) ) {
			return new WP_Error(
				'bfnet_age_confirm',
				sprintf(
					/* translators: %d: minimum age. */
					__( 'Please confirm that every guest is %d years or over.', 'bitforgenet-guest-tickets' ),
					$min_age
				)
			);
		}

		$quantity = count( $clean_guests );

		// Capacity guard, re-checked at write time.
		$left = BFNET_Event::seats_left( $event );
		if ( null !== $left && $quantity > $left ) {
			/* translators: %d: number of seats remaining. */
			return new WP_Error( 'bfnet_capacity', sprintf( _n( 'Only %d seat is still available for this event.', 'Only %d seats are still available for this event.', $left, 'bitforgenet-guest-tickets' ), $left ) );
		}

		$reference = BFNET_Util::order_reference( $event_id );
		$now = current_time( 'mysql' );

		// A discount is checked again here, against the basket that was actually
		// built, rather than trusting whatever the form last calculated.
		$discount_cents = 0;
		$discount_code  = '';
		$discount_label = '';
		$discount_row   = null;

		if ( ! empty( $data['discount_code'] ) ) {
			$applied = BFNET_Offers::apply( $event_id, $data['discount_code'], $subtotal, $quantity );

			// A code that cannot be applied has to say so. Swallowing the reason
			// and charging full price is the worst of both: the guest believes
			// they got a reduction, and nobody finds out until they complain.
			if ( is_wp_error( $applied ) ) {
				return new WP_Error(
					'bfnet_discount_rejected',
					sprintf(
						/* translators: 1: the code typed, 2: why it was refused. */
						__( 'The discount code %1$s could not be applied: %2$s Remove it, or correct it, and send the booking again.', 'bitforgenet-guest-tickets' ),
						BFNET_Offers::normalise( $data['discount_code'] ),
						$applied->get_error_message()
					)
				);
			}

			if ( is_array( $applied ) ) {
				$discount_cents = (int) $applied['amount_cents'];
				$discount_row   = $applied;
				$discount_code  = isset( $applied['code'] ) ? (string) $applied['code'] : BFNET_Offers::normalise( $data['discount_code'] );
				$discount_label = $applied['label'];
			}
		}

		$total = max( 0, $subtotal - $discount_cents );

		$row = array(
				'uuid'              => BFNET_Util::uuid(),
				'reference'         => $reference,
				'event_id'          => $event_id,
				'user_id'           => get_current_user_id(),
				'first_name'        => $first,
				'last_name'         => $last,
				'email'             => $email,
				'phone'             => $phone,
				'whatsapp'          => isset( $data['whatsapp'] ) ? BFNET_Security::clean_phone( $data['whatsapp'] ) : $phone,
				'country'           => isset( $data['country'] ) ? sanitize_text_field( $data['country'] ) : '',
				'city'              => isset( $data['city'] ) ? sanitize_text_field( $data['city'] ) : '',
				'quantity'          => $quantity,
				'subtotal_cents'    => $subtotal,
				'discount_cents'    => $discount_cents,
				'discount_code'     => $discount_code,
				'discount_label'    => $discount_label,
				'total_cents'       => $total,
				'currency'          => $event->currency,
				'payment_status'    => 'pending',
				'payment_method'    => (string) BFNET_Settings::get( 'payment_mode', 'manual' ),
				'customer_note'     => isset( $data['customer_note'] ) ? sanitize_textarea_field( $data['customer_note'] ) : '',
				'consent_terms'     => ! empty( $data['consent_terms'] ) ? 1 : 0,
				'consent_marketing' => ! empty( $data['consent_marketing'] ) ? 1 : 0,
				'source'            => isset( $data['source'] ) ? sanitize_key( $data['source'] ) : 'website',
				'ip_hash'           => BFNET_Security::ip_hash(),
				'user_agent'        => BFNET_Security::user_agent(),
				'locale'            => get_locale(),
				'created_at'        => $now,
				'updated_at'        => $now,
		);

		$inserted = $wpdb->insert( self::table(), BFNET_Install::writable( self::table(), $row ) );

		if ( ! $inserted ) {
			return new WP_Error( 'bfnet_order_insert', __( 'Your reservation could not be saved. Please try again.', 'bitforgenet-guest-tickets' ) );
		}

		$order_id  = (int) $wpdb->insert_id;
		$attendees = BFNET_Install::table( 'attendees' );

		if ( $discount_row ) {
			BFNET_Offers::used( $discount_row, $order_id );
		}

		foreach ( $clean_guests as $position => $guest ) {
			$guest_row = array(
					'order_id'       => $order_id,
					'event_id'       => $event_id,
					'ticket_type_id' => $guest['ticket_type_id'],
					'position'       => $position + 1,
					'first_name'     => $guest['first_name'],
					'last_name'      => $guest['last_name'],
					'email'          => $guest['email'],
					'phone'          => $guest['phone'],
					'category'       => $guest['category'],
					'birth_date'     => $guest['birth_date'] ? $guest['birth_date'] : null,
					'created_at'     => $now,
			);

			$wpdb->insert( $attendees, BFNET_Install::writable( $attendees, $guest_row ) );
		}

		BFNET_Log::audit( 'order', $order_id, 'order_created', array( 'reference' => $reference, 'quantity' => $quantity, 'event_id' => $event_id ) );

		do_action( 'bfnet_order_created', $order_id, self::get( $order_id ) );

		return $order_id;
	}

	/**
	 * Validate a date of birth into Y-m-d, or an empty string.
	 *
	 * @param string $value Raw input.
	 * @return string
	 */
	private static function clean_birth_date( $value ) {
		$value = trim( (string) $value );

		if ( ! preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $value, $m ) ) {
			return '';
		}
		if ( ! checkdate( (int) $m[2], (int) $m[3], (int) $m[1] ) ) {
			return '';
		}
		if ( (int) $m[1] < 1900 || (int) $m[1] > (int) gmdate( 'Y' ) ) {
			return '';
		}

		return $value;
	}

	/**
	 * Age a person will be on the night of the event.
	 *
	 * Compared as calendar dates in the event's own timezone. Comparing UTC instants
	 * instead would make a 21:00 event fall on the following UTC day and quietly
	 * age every guest by one, letting someone through the door a day early.
	 *
	 * @param string $birth_date Y-m-d.
	 * @param string $event_date Y-m-d, the local date of the event.
	 * @return int|null
	 */
	private static function age_on( $birth_date, $event_date ) {
		if ( ! $event_date ) {
			$event_date = gmdate( 'Y-m-d' );
		}

		try {
			$utc   = new DateTimeZone( 'UTC' );
			$birth = new DateTimeImmutable( $birth_date . ' 00:00:00', $utc );
			$when  = new DateTimeImmutable( $event_date . ' 00:00:00', $utc );
		} catch ( Exception $e ) {
			return null;
		}

		if ( $when < $birth ) {
			return null;
		}

		return (int) $birth->diff( $when )->y;
	}

	/* --------------------------------------------------------------------- */
	/* State transitions                                                     */
	/* --------------------------------------------------------------------- */

	/**
	 * Mark an order as paid. Idempotent.
	 *
	 * @param int    $order_id  Order id.
	 * @param string $reference Payment reference recorded by the agent.
	 * @param string $method    Payment method slug.
	 * @return true|WP_Error
	 */
	public static function mark_paid( $order_id, $reference = '', $method = '' ) {
		global $wpdb;

		$order = self::get( $order_id );
		if ( ! $order ) {
			return new WP_Error( 'bfnet_no_order', __( 'Order not found.', 'bitforgenet-guest-tickets' ) );
		}
		if ( 'paid' === $order->payment_status ) {
			return true;
		}

		$now = current_time( 'mysql' );

		$wpdb->update(
			self::table(),
			array(
				'payment_status'    => 'paid',
				'payment_reference' => sanitize_text_field( $reference ),
				'payment_method'    => $method ? sanitize_key( $method ) : $order->payment_method,
				'paid_at'           => $now,
				'paid_by'           => get_current_user_id(),
				'updated_at'        => $now,
			),
			array( 'id' => (int) $order_id ),
			null,
			array( '%d' )
		);

		self::refresh_event_counters( (int) $order->event_id );

		BFNET_Log::audit( 'order', $order_id, 'order_marked_paid', array( 'reference' => $order->reference, 'payment_reference' => $reference ) );

		do_action( 'bfnet_order_marked_paid', (int) $order_id, self::get( $order_id ) );

		return true;
	}

	/**
	 * Move an order back to pending and void its tickets.
	 *
	 * @param int    $order_id Order id.
	 * @param string $reason   Reason recorded on the tickets.
	 * @return true|WP_Error
	 */
	public static function mark_unpaid( $order_id, $reason = '' ) {
		global $wpdb;

		$order = self::get( $order_id );
		if ( ! $order ) {
			return new WP_Error( 'bfnet_no_order', __( 'Order not found.', 'bitforgenet-guest-tickets' ) );
		}

		$now = current_time( 'mysql' );

		$wpdb->update(
			self::table(),
			array(
				'payment_status'  => 'pending',
				'paid_at'         => null,
				'tickets_issued'  => 0,
				'tickets_sent_at' => null,
				'updated_at'      => $now,
			),
			array( 'id' => (int) $order_id ),
			null,
			array( '%d' )
		);

		BFNET_Ticket::void_for_order( (int) $order_id, $reason ? $reason : __( 'Payment reverted by an agent', 'bitforgenet-guest-tickets' ) );
		self::refresh_event_counters( (int) $order->event_id );

		BFNET_Log::audit( 'order', $order_id, 'order_marked_unpaid', array( 'reason' => $reason ), 'warning' );

		return true;
	}

	/**
	 * Change an order to an arbitrary status.
	 *
	 * @param int    $order_id Order id.
	 * @param string $status   New status.
	 * @param string $reason   Optional reason.
	 * @return true|WP_Error
	 */
	public static function set_status( $order_id, $status, $reason = '' ) {
		global $wpdb;

		$status = sanitize_key( $status );
		if ( ! array_key_exists( $status, BFNET_Util::payment_statuses() ) ) {
			return new WP_Error( 'bfnet_bad_status', __( 'Unknown order status.', 'bitforgenet-guest-tickets' ) );
		}

		if ( 'paid' === $status ) {
			return self::mark_paid( $order_id, $reason );
		}

		$order = self::get( $order_id );
		if ( ! $order ) {
			return new WP_Error( 'bfnet_no_order', __( 'Order not found.', 'bitforgenet-guest-tickets' ) );
		}

		$wpdb->update(
			self::table(),
			array(
				'payment_status' => $status,
				'updated_at'     => current_time( 'mysql' ),
			),
			array( 'id' => (int) $order_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( in_array( $status, array( 'cancelled', 'refunded', 'expired' ), true ) ) {
			BFNET_Ticket::void_for_order( (int) $order_id, $reason ? $reason : $status );
		}

		self::refresh_event_counters( (int) $order->event_id );
		BFNET_Log::audit( 'order', $order_id, 'order_status_changed', array( 'status' => $status, 'reason' => $reason ) );

		return true;
	}

	/**
	 * Update the free-form admin note on an order.
	 *
	 * @param int    $order_id Order id.
	 * @param string $note     Note text.
	 * @return void
	 */
	public static function set_admin_note( $order_id, $note ) {
		global $wpdb;
		$wpdb->update(
			self::table(),
			array( 'admin_note' => sanitize_textarea_field( $note ), 'updated_at' => current_time( 'mysql' ) ),
			array( 'id' => (int) $order_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Delete an order and its dependants.
	 *
	 * @param int $order_id Order id.
	 * @return bool
	 */
	public static function delete( $order_id ) {
		global $wpdb;

		$order = self::get( $order_id );
		if ( ! $order ) {
			return false;
		}

		BFNET_Ticket::delete_for_order( (int) $order_id );

		$wpdb->delete( BFNET_Install::table( 'attendees' ), array( 'order_id' => (int) $order_id ), array( '%d' ) );
		$wpdb->delete( self::table(), array( 'id' => (int) $order_id ), array( '%d' ) );

		self::refresh_event_counters( (int) $order->event_id );
		BFNET_Log::audit( 'order', $order_id, 'order_deleted', array( 'reference' => $order->reference ), 'warning' );

		return true;
	}

	/**
	 * Recalculate cached counters on the parent event.
	 *
	 * @param int $event_id Event id.
	 * @return void
	 */
	public static function refresh_event_counters( $event_id ) {
		global $wpdb;

		$event_id = (int) $event_id;
		if ( ! $event_id ) {
			return;
		}

		$tickets    = BFNET_Install::table( 'tickets' );
		$checked_in = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tickets} WHERE event_id = %d AND checked_in = 1", $event_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL

		$wpdb->update(
			BFNET_Event::table(),
			array(
				'sold_count'       => BFNET_Event::sold( $event_id ),
				'checked_in_count' => $checked_in,
			),
			array( 'id' => $event_id ),
			array( '%d', '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Aggregate statistics for an event or the whole system.
	 *
	 * @param int $event_id Event id, or 0 for global figures.
	 * @return array
	 */
	public static function stats( $event_id = 0 ) {
		global $wpdb;

		$orders   = self::table();
		$tickets  = BFNET_Install::table( 'tickets' );
		$event_id = (int) $event_id;

		$where  = $event_id ? 'WHERE event_id = %d' : '';
		$params = $event_id ? array( $event_id ) : array();

		$sql = "SELECT
			COUNT(*) AS orders_total,
			COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN 1 ELSE 0 END),0) AS orders_paid,
			COALESCE(SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END),0) AS orders_pending,
			COALESCE(SUM(CASE WHEN payment_status = 'cancelled' THEN 1 ELSE 0 END),0) AS orders_cancelled,
			COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN quantity ELSE 0 END),0) AS seats_paid,
			COALESCE(SUM(CASE WHEN payment_status = 'pending' THEN quantity ELSE 0 END),0) AS seats_pending,
			COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN total_cents ELSE 0 END),0) AS revenue_cents,
			COALESCE(SUM(CASE WHEN payment_status = 'pending' THEN total_cents ELSE 0 END),0) AS pipeline_cents
			FROM {$orders} {$where}";

		$row = $params
			? $wpdb->get_row( $wpdb->prepare( $sql, $params ), ARRAY_A ) // phpcs:ignore WordPress.DB.PreparedSQL
			: $wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL

		$row = $row ? array_map( 'intval', $row ) : array();

		$t_where  = $event_id ? 'WHERE event_id = %d' : '';
		$t_sql    = "SELECT
			COUNT(*) AS tickets_total,
			COALESCE(SUM(CASE WHEN checked_in = 1 THEN 1 ELSE 0 END),0) AS tickets_checked_in,
			COALESCE(SUM(CASE WHEN status = 'void' THEN 1 ELSE 0 END),0) AS tickets_void
			FROM {$tickets} {$t_where}";

		$trow = $params
			? $wpdb->get_row( $wpdb->prepare( $t_sql, $params ), ARRAY_A ) // phpcs:ignore WordPress.DB.PreparedSQL
			: $wpdb->get_row( $t_sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL

		return array_merge(
			array(
				'orders_total'       => 0,
				'orders_paid'        => 0,
				'orders_pending'     => 0,
				'orders_cancelled'   => 0,
				'seats_paid'         => 0,
				'seats_pending'      => 0,
				'revenue_cents'      => 0,
				'pipeline_cents'     => 0,
				'tickets_total'      => 0,
				'tickets_checked_in' => 0,
				'tickets_void'       => 0,
			),
			$row,
			$trow ? array_map( 'intval', $trow ) : array()
		);
	}
}
