<?php
namespace Pay4All\Pro\Admin\Tickets;

use Pay4All\Form\Admin\Menu;
use Pay4All\Form\Admin\Orders\Statuses;
use Pay4All\Form\Domain\Order;
use Pay4All\Pro\Domain\Ticket;

if ( ! defined( 'ABSPATH' ) ) { exit; }

/**
 * Admin Tickets page: list / search / manual-validate / CSV export.
 * Reachable from the Pay4All FORM menu — Menu::register() adds the submenu.
 */
final class Page {

	public const SLUG              = 'pay4all-form-tickets';
	public const NONCE_ACTION      = 'p4all_tickets_action';
	public const EXPORT_ACTION     = 'p4all_tickets_export';
	public const EXPORT_XLS_ACTION = 'p4all_tickets_export_xls';

	public static function register() : void {
		add_action( 'admin_menu',                              [ self::class, 'add_submenu' ], 20 );
		add_action( 'admin_post_' . self::EXPORT_ACTION,       [ self::class, 'download_csv' ] );
		add_action( 'admin_post_' . self::EXPORT_XLS_ACTION,   [ self::class, 'download_xls' ] );
		add_action( 'admin_post_p4all_tickets_validate',       [ self::class, 'handle_admin_validate' ] );
		add_action( 'admin_post_p4all_tickets_release',        [ self::class, 'handle_admin_release' ] );
		add_action( 'admin_post_p4all_tickets_delete',         [ self::class, 'handle_admin_delete' ] );
	}

	public static function add_submenu() : void {
		add_submenu_page(
			Menu::SLUG_TOP,
			__( 'Billets', 'pay4all-pro' ),
			__( 'Billets', 'pay4all-pro' ),
			'manage_options',
			self::SLUG,
			[ self::class, 'render' ]
		);
	}

	public static function render() : void {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_die( esc_html__( 'Permission refusée.', 'pay4all-pro' ) );
		}
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only search form on a GET.
		$search     = isset( $_GET['s'] ) ? sanitize_text_field( wp_unslash( (string) $_GET['s'] ) ) : '';
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only filter.
		$product_id = isset( $_GET['product_id'] ) ? absint( wp_unslash( $_GET['product_id'] ) ) : 0;
		$rows       = self::query( $search, $product_id, 200 );
		$products   = self::ticket_products();

		$export_args_csv = [ 'action' => self::EXPORT_ACTION ];
		$export_args_xls = [ 'action' => self::EXPORT_XLS_ACTION ];
		if ( $search     !== '' ) { $export_args_csv['s']          = $search; $export_args_xls['s']          = $search; }
		if ( $product_id  >  0 )  { $export_args_csv['product_id'] = $product_id; $export_args_xls['product_id'] = $product_id; }
		$export_url     = wp_nonce_url( add_query_arg( $export_args_csv, admin_url( 'admin-post.php' ) ), self::NONCE_ACTION );
		$export_url_xls = wp_nonce_url( add_query_arg( $export_args_xls, admin_url( 'admin-post.php' ) ), self::NONCE_ACTION );
		?>
		<div class="wrap">
			<h1><?php esc_html_e( 'Billets', 'pay4all-pro' ); ?></h1>

			<form method="get" style="margin:12px 0;">
				<input type="hidden" name="page" value="<?php echo esc_attr( self::SLUG ); ?>">
				<select name="product_id">
					<option value="0"><?php esc_html_e( 'Tous les produits', 'pay4all-pro' ); ?></option>
					<?php foreach ( $products as $p ) : ?>
						<option value="<?php echo (int) $p['id']; ?>" <?php selected( $product_id, (int) $p['id'] ); ?>>
							<?php echo esc_html( $p['title'] ); ?>
						</option>
					<?php endforeach; ?>
				</select>
				<input type="search" name="s" value="<?php echo esc_attr( $search ); ?>"
				       placeholder="<?php esc_attr_e( 'Rechercher (nom, prénom, localité, e-mail…)', 'pay4all-pro' ); ?>"
				       style="min-width:320px;">
				<button class="button"><?php esc_html_e( 'Filtrer', 'pay4all-pro' ); ?></button>
				<a href="<?php echo esc_url( $export_url ); ?>" class="button button-secondary" style="margin-left:8px;">
					<?php esc_html_e( 'Exporter CSV', 'pay4all-pro' ); ?>
				</a>
				<a href="<?php echo esc_url( $export_url_xls ); ?>" class="button button-secondary">
					<?php esc_html_e( 'Exporter XLS', 'pay4all-pro' ); ?>
				</a>
			</form>

			<table class="widefat striped">
				<thead><tr>
					<th><?php esc_html_e( 'Date', 'pay4all-pro' ); ?></th>
					<th><?php esc_html_e( 'Commande', 'pay4all-pro' ); ?></th>
					<th><?php esc_html_e( 'Produit', 'pay4all-pro' ); ?></th>
					<th><?php esc_html_e( 'Client', 'pay4all-pro' ); ?></th>
					<th><?php esc_html_e( 'Paiement', 'pay4all-pro' ); ?></th>
					<th><?php esc_html_e( 'Statut', 'pay4all-pro' ); ?></th>
					<th></th>
				</tr></thead>
				<tbody>
					<?php if ( empty( $rows ) ) : ?>
						<tr><td colspan="7"><em><?php esc_html_e( 'Aucun billet.', 'pay4all-pro' ); ?></em></td></tr>
					<?php else : foreach ( $rows as $row ) :
						$cust    = is_string( $row['customer_data'] ) ? (array) json_decode( $row['customer_data'], true ) : [];
						$name    = trim( ( $cust['firstname'] ?? '' ) . ' ' . ( $cust['lastname'] ?? '' ) );
						$loc     = trim( ( $cust['city'] ?? '' ) . ' ' . ( $cust['zip'] ?? '' ) );
						$email   = (string) ( $cust['email'] ?? '' );
						$prod    = get_post( (int) $row['product_id'] );
						$order   = get_post( (int) $row['order_id'] );
						$is_val  = ! empty( $row['validated_at'] );
						$val_url = Ticket::validation_url( $row );
						$pay     = $order ? Order::payment( (int) $row['order_id'] ) : [];
						$pay_st  = (string) ( $pay['status'] ?? '' );
						$pay_lbl = $pay_st !== '' ? Statuses::payment_label( $pay_st ) : '—';
						$pay_pal = Statuses::payment_palette( $pay_st );
						?>
						<tr>
							<td><?php echo esc_html( mysql2date( get_option( 'date_format' ), (string) $row['created_at'] ) ); ?></td>
							<td>
								<?php if ( $order ) : ?>
									<a href="<?php echo esc_url( (string) get_edit_post_link( $order->ID ) ); ?>">
										<?php echo esc_html( get_the_title( $order ) ); ?>
									</a>
								<?php else : ?>—<?php endif; ?>
							</td>
							<td><?php echo esc_html( $prod ? get_the_title( $prod ) : (string) $row['product_slug'] ); ?></td>
							<td>
								<?php echo esc_html( $name ?: '—' ); ?>
								<?php if ( $email !== '' ) : ?><br><small><?php echo esc_html( $email ); ?></small><?php endif; ?>
								<?php if ( $loc  !== '' ) : ?><br><small><?php echo esc_html( $loc ); ?></small><?php endif; ?>
							</td>
							<td>
								<span class="p4all-list-pill" style="background:<?php echo esc_attr( $pay_pal['bg'] ); ?>;color:<?php echo esc_attr( $pay_pal['text'] ); ?>;">
									<?php echo esc_html( $pay_lbl ); ?>
								</span>
							</td>
							<td>
								<?php if ( $is_val ) : ?>
									<span style="color:#a00;font-weight:600;">
										<?php
										/* translators: %s = date/time of the previous scan */
										printf( esc_html__( 'Validé le %s', 'pay4all-pro' ), esc_html( mysql2date( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), (string) $row['validated_at'] ) ) );
										?>
									</span>
								<?php else : ?>
									<span style="color:#1f8a39;font-weight:600;"><?php esc_html_e( 'Disponible', 'pay4all-pro' ); ?></span>
								<?php endif; ?>
							</td>
							<td>
								<a class="button button-small" href="<?php echo esc_url( $val_url ); ?>" target="_blank" rel="noopener">
									<?php esc_html_e( 'Scan', 'pay4all-pro' ); ?>
								</a>
								<?php if ( ! $is_val ) : ?>
									<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" style="display:inline;"
									      data-confirm="<?php esc_attr_e( 'Marquer ce billet comme validé ?', 'pay4all-pro' ); ?>">
										<?php wp_nonce_field( self::NONCE_ACTION, '_p4all_nonce' ); ?>
										<input type="hidden" name="action" value="p4all_tickets_validate">
										<input type="hidden" name="ticket_id" value="<?php echo (int) $row['id']; ?>">
										<button class="button button-primary button-small">
											<?php esc_html_e( 'Valider', 'pay4all-pro' ); ?>
										</button>
									</form>
								<?php else : ?>
									<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" style="display:inline;"
									      data-confirm="<?php esc_attr_e( 'Libérer ce billet ? Il pourra être rescanné.', 'pay4all-pro' ); ?>">
										<?php wp_nonce_field( self::NONCE_ACTION, '_p4all_nonce' ); ?>
										<input type="hidden" name="action" value="p4all_tickets_release">
										<input type="hidden" name="ticket_id" value="<?php echo (int) $row['id']; ?>">
										<button class="button button-small p4all-button-danger">
											<?php esc_html_e( 'Libérer', 'pay4all-pro' ); ?>
										</button>
									</form>
								<?php endif; ?>
								<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" style="display:inline;"
								      data-confirm="<?php esc_attr_e( 'Supprimer définitivement ce billet ? Cette action est irréversible.', 'pay4all-pro' ); ?>">
									<?php wp_nonce_field( self::NONCE_ACTION, '_p4all_nonce' ); ?>
									<input type="hidden" name="action" value="p4all_tickets_delete">
									<input type="hidden" name="ticket_id" value="<?php echo (int) $row['id']; ?>">
									<button class="button button-small p4all-button-danger">
										<?php esc_html_e( 'Supprimer', 'pay4all-pro' ); ?>
									</button>
								</form>
							</td>
						</tr>
					<?php endforeach; endif; ?>
				</tbody>
			</table>
		</div>
		<?php
	}

	public static function handle_admin_validate() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( 'forbidden', '', [ 'response' => 403 ] ); }
		check_admin_referer( self::NONCE_ACTION, '_p4all_nonce' );
		$id = isset( $_POST['ticket_id'] ) ? absint( wp_unslash( $_POST['ticket_id'] ) ) : 0;
		if ( $id > 0 ) {
			Ticket::mark_validated( $id, 'admin' );
		}
		wp_safe_redirect( admin_url( 'admin.php?page=' . self::SLUG ) );
		exit;
	}

	public static function handle_admin_release() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( 'forbidden', '', [ 'response' => 403 ] ); }
		check_admin_referer( self::NONCE_ACTION, '_p4all_nonce' );
		$id = isset( $_POST['ticket_id'] ) ? absint( wp_unslash( $_POST['ticket_id'] ) ) : 0;
		if ( $id > 0 ) {
			Ticket::mark_unvalidated( $id );
		}
		wp_safe_redirect( admin_url( 'admin.php?page=' . self::SLUG ) );
		exit;
	}

	public static function handle_admin_delete() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( 'forbidden', '', [ 'response' => 403 ] ); }
		check_admin_referer( self::NONCE_ACTION, '_p4all_nonce' );
		$id = isset( $_POST['ticket_id'] ) ? absint( wp_unslash( $_POST['ticket_id'] ) ) : 0;
		if ( $id > 0 ) {
			Ticket::delete( $id );
		}
		wp_safe_redirect( admin_url( 'admin.php?page=' . self::SLUG ) );
		exit;
	}

	public static function download_csv() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( 'forbidden', '', [ 'response' => 403 ] ); }
		check_admin_referer( self::NONCE_ACTION );
		[ $headers, $rows ] = self::export_dataset();

		nocache_headers();
		header( 'Content-Type: text/csv; charset=UTF-8' );
		header( 'Content-Disposition: attachment; filename="pay4all-tickets-' . gmdate( 'Y-m-d' ) . '.csv"' );
		echo "\xEF\xBB\xBF";
		$out = fopen( 'php://output', 'w' );
		fputcsv( $out, $headers );
		foreach ( $rows as $row ) {
			fputcsv( $out, $row );
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- php://output stream.
		fclose( $out );
		exit;
	}

	/**
	 * Excel export — real XLSX (ZIP + Open XML) so Excel opens the file
	 * without the "format et extension ne correspondent pas" warning that
	 * pops up with SpreadsheetML 2003 or HTML-in-xls hacks.
	 */
	public static function download_xls() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( 'forbidden', '', [ 'response' => 403 ] ); }
		check_admin_referer( self::NONCE_ACTION );
		[ $headers, $rows ] = self::export_dataset();

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="pay4all-tickets-' . gmdate( 'Y-m-d' ) . '.xlsx"' );
		self::stream_xlsx( __( 'Billets', 'pay4all-pro' ), $headers, $rows );
		exit;
	}

	/**
	 * Shared CSV/XLS data source: reads the filtered ticket set from $_GET
	 * and returns [ header row, data rows ]. Extracted so both formats stay
	 * strictly aligned (same columns, same row order).
	 *
	 * @return array{0:array<string>,1:array<array<int|string,mixed>>}
	 */
	private static function export_dataset() : array {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- search term is read-only.
		$search     = isset( $_GET['s'] ) ? sanitize_text_field( wp_unslash( (string) $_GET['s'] ) ) : '';
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only filter.
		$product_id = isset( $_GET['product_id'] ) ? absint( wp_unslash( $_GET['product_id'] ) ) : 0;
		$src        = self::query( $search, $product_id, 10000 );

		$headers = [ 'date', 'order', 'product', 'customer_firstname', 'customer_lastname', 'customer_email', 'customer_city', 'customer_zip', 'payment_status', 'validated_at', 'token' ];
		$rows    = [];
		foreach ( $src as $row ) {
			$cust    = is_string( $row['customer_data'] ) ? (array) json_decode( $row['customer_data'], true ) : [];
			$pay     = Order::payment( (int) $row['order_id'] );
			$pay_st  = (string) ( $pay['status'] ?? '' );
			$rows[] = [
				$row['created_at'],
				(int) $row['order_id'],
				get_the_title( (int) $row['product_id'] ) ?: $row['product_slug'],
				$cust['firstname'] ?? '',
				$cust['lastname']  ?? '',
				$cust['email']     ?? '',
				$cust['city']      ?? '',
				$cust['zip']       ?? '',
				$pay_st,
				$row['validated_at'] ?? '',
				$row['token'],
			];
		}
		return [ $headers, $rows ];
	}

	/**
	 * Streams a valid Open XML .xlsx workbook to stdout. Built from scratch
	 * with ZipArchive so we don't need PhpSpreadsheet (kept the plugin
	 * dependency-free). All cells are inline strings — no shared strings
	 * table, no column-type inference — which keeps leading zeros in codes
	 * postaux, IBAN prefixes, phone numbers…
	 *
	 * The header row uses a bold + light-grey fill style so the sheet reads
	 * like a report as soon as it opens.
	 *
	 * @param array<string>                       $headers
	 * @param array<array<int|string,mixed>>      $rows
	 */
	public static function stream_xlsx( string $sheet_name, array $headers, array $rows ) : void {
		if ( ! class_exists( '\\ZipArchive' ) ) {
			// Extremely rare on shared hosts — but if the zip extension is
			// disabled we degrade to CSV rather than serving a broken file.
			// We stream to `php://output`, a PHP-supplied virtual stream, so
			// WP_Filesystem doesn't apply (it's meant for wp-content writes,
			// not for the response body).
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://output stream, no WP_Filesystem equivalent.
			$out = fopen( 'php://output', 'w' );
			fputcsv( $out, $headers );
			foreach ( $rows as $r ) { fputcsv( $out, $r ); }
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- php://output stream opened above.
			fclose( $out );
			return;
		}

		$sheet_name = $sheet_name !== '' ? $sheet_name : 'Sheet1';
		// Excel restricts sheet names to 31 chars and disallows a few
		// characters — trim/strip so we never emit an invalid workbook.
		$sheet_name = substr( preg_replace( '#[\\\\/\\?\\*\\[\\]:]#', ' ', $sheet_name ), 0, 31 );

		// Assemble the sheet1.xml body (the heaviest chunk).
		$col_letter = static function ( int $n ) : string {
			// 1 => A, 26 => Z, 27 => AA, …
			$s = '';
			while ( $n > 0 ) {
				$m  = ( $n - 1 ) % 26;
				$s  = chr( 65 + $m ) . $s;
				$n  = (int) ( ( $n - $m - 1 ) / 26 );
			}
			return $s;
		};
		$esc = static function ( $v ) : string {
			return htmlspecialchars( (string) $v, ENT_XML1 | ENT_QUOTES, 'UTF-8' );
		};

		$sheet_xml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
		$sheet_xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
		$sheet_xml .= '<sheetData>';
		// Header row (style s="1" = bold + grey fill defined in styles.xml).
		$sheet_xml .= '<row r="1">';
		$col = 0;
		foreach ( $headers as $h ) {
			$col++;
			$ref = $col_letter( $col ) . '1';
			$sheet_xml .= '<c r="' . $ref . '" s="1" t="inlineStr"><is><t xml:space="preserve">' . $esc( $h ) . '</t></is></c>';
		}
		$sheet_xml .= '</row>';
		// Data rows.
		$r_idx = 1;
		foreach ( $rows as $row ) {
			$r_idx++;
			$sheet_xml .= '<row r="' . $r_idx . '">';
			$col = 0;
			foreach ( $row as $cell ) {
				$col++;
				$ref = $col_letter( $col ) . $r_idx;
				$sheet_xml .= '<c r="' . $ref . '" t="inlineStr"><is><t xml:space="preserve">' . $esc( (string) $cell ) . '</t></is></c>';
			}
			$sheet_xml .= '</row>';
		}
		$sheet_xml .= '</sheetData></worksheet>';

		$parts = [
			'[Content_Types].xml' =>
				'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
				. '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
				. '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
				. '<Default Extension="xml" ContentType="application/xml"/>'
				. '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
				. '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
				. '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
				. '</Types>',
			'_rels/.rels' =>
				'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
				. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
				. '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
				. '</Relationships>',
			'xl/_rels/workbook.xml.rels' =>
				'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
				. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
				. '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
				. '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
				. '</Relationships>',
			'xl/workbook.xml' =>
				'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
				. '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
				. '<sheets><sheet name="' . $esc( $sheet_name ) . '" sheetId="1" r:id="rId1"/></sheets>'
				. '</workbook>',
			'xl/styles.xml' =>
				'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
				. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
				. '<fonts count="2">'
					. '<font><sz val="11"/><color rgb="FF000000"/><name val="Calibri"/></font>'
					. '<font><b/><sz val="11"/><color rgb="FF000000"/><name val="Calibri"/></font>'
				. '</fonts>'
				. '<fills count="3">'
					. '<fill><patternFill patternType="none"/></fill>'
					. '<fill><patternFill patternType="gray125"/></fill>'
					. '<fill><patternFill patternType="solid"><fgColor rgb="FFF0F0F1"/><bgColor indexed="64"/></patternFill></fill>'
				. '</fills>'
				. '<borders count="1"><border/></borders>'
				. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
				. '<cellXfs count="2">'
					. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
					. '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/>'
				. '</cellXfs>'
				. '</styleSheet>',
			'xl/worksheets/sheet1.xml' => $sheet_xml,
		];

		// Write the zip to a temp file, then stream it out. ZipArchive
		// operates on a filesystem path — php://output isn't a seekable
		// stream so we can't hand it directly.
		$tmp = wp_tempnam( 'p4all-xlsx-' );
		if ( ! $tmp ) {
			return;
		}
		$zip = new \ZipArchive();
		if ( $zip->open( $tmp, \ZipArchive::OVERWRITE ) !== true ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink -- temp file cleanup.
			@unlink( $tmp );
			return;
		}
		foreach ( $parts as $path => $content ) {
			$zip->addFromString( $path, $content );
		}
		$zip->close();
		// Stream the freshly-built XLSX bytes to the HTTP response body.
		// `WP_Filesystem` cannot write to the response, so `readfile()` is
		// the correct primitive here.
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile -- streaming to HTTP response body, not touching wp-content.
		readfile( $tmp );
		// phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink -- temp file cleanup.
		@unlink( $tmp );
	}

	private static function query( string $search, int $product_id, int $limit ) : array {
		global $wpdb;
		$table   = Ticket::table();
		$clauses = [ '1=1' ];
		$args    = [];
		if ( $search !== '' ) {
			$clauses[] = 'customer_data LIKE %s';
			$args[]    = '%' . $wpdb->esc_like( $search ) . '%';
		}
		if ( $product_id > 0 ) {
			$clauses[] = 'product_id = %d';
			$args[]    = $product_id;
		}
		$where  = implode( ' AND ', $clauses );
		$args[] = $limit;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table from $wpdb->prefix + hardcoded literal; $where is composed from hardcoded clauses only; values prepared via %s / %d.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE {$where} ORDER BY id DESC LIMIT %d", $args ), ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * List of products that have at least one ticket, for the filter dropdown.
	 * Returns [ [ 'id' => int, 'title' => string ], ... ] ordered alphabetically.
	 */
	private static function ticket_products() : array {
		global $wpdb;
		$table = Ticket::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table from $wpdb->prefix + hardcoded literal; no user input in the query.
		$ids = $wpdb->get_col( "SELECT DISTINCT product_id FROM {$table} WHERE product_id > 0" );
		$out = [];
		foreach ( (array) $ids as $id ) {
			$pid   = (int) $id;
			$title = get_the_title( $pid );
			if ( $title === '' ) { continue; }
			$out[] = [ 'id' => $pid, 'title' => $title ];
		}
		usort( $out, static function ( $a, $b ) {
			return strcasecmp( (string) $a['title'], (string) $b['title'] );
		} );
		return $out;
	}
}
