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

use Pay4All\Pro\Domain\Newsletter;
use Pay4All\Pro\Domain\NewsletterSendLog;
use Pay4All\Pro\PostTypes;

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

/**
 * Per-campaign statistics screen.
 *
 * Rendered by Page::render() when the query string carries
 * `action=stats&campaign=ID`. Layout :
 *
 *   ┌────────────────────────────────────────────────────────────────┐
 *   │ Sent / Opened / Clicked / Unsubscribed + CTOR (aggregate cards)│
 *   ├────────────────────────────────────────────────────────────────┤
 *   │ SVG line chart : opens / clicks / unsubs per day since day 0   │
 *   ├────────────────────────────────────────────────────────────────┤
 *   │ Filters : search e-mail + status dropdown                       │
 *   │ Recipients table (paginated) — email, sent, opened, clicked,   │
 *   │                                  unsubscribed, status pill      │
 *   ├────────────────────────────────────────────────────────────────┤
 *   │ Export CSV button (streams every recipient row for the campaign)│
 *   └────────────────────────────────────────────────────────────────┘
 *
 * The chart is a hand-rolled SVG (~200 lines) so the plugin doesn't
 * carry a chart library dependency for a screen that's only ever
 * displayed to the merchant.
 */
final class StatsPage {

	public const CSV_ACTION = 'p4all_nl_stats_csv';
	public const XLS_ACTION = 'p4all_nl_stats_xls';

	public static function register() : void {
		add_action( 'admin_post_' . self::CSV_ACTION, [ self::class, 'handle_csv_export' ] );
		add_action( 'admin_post_' . self::XLS_ACTION, [ self::class, 'handle_xls_export' ] );
	}

	// -----------------------------------------------------------------
	// Main render — called by Page::render() when action=stats.
	// -----------------------------------------------------------------
	public static function render( int $campaign_id ) : void {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_die( esc_html__( 'Permission denied.', 'pay4all-pro' ) );
		}
		$post = get_post( $campaign_id );
		if ( ! $post || $post->post_type !== PostTypes::NEWSLETTER ) {
			wp_die( esc_html__( 'Newsletter not found.', 'pay4all-pro' ) );
		}

		$sent        = NewsletterSendLog::count_sent( $campaign_id );
		$opened      = NewsletterSendLog::count_opened( $campaign_id );
		$clicked     = NewsletterSendLog::count_clicked( $campaign_id );
		$unsubs      = NewsletterSendLog::count_unsubscribed( $campaign_id );
		$open_total  = NewsletterSendLog::sum_open_count( $campaign_id );
		$click_total = NewsletterSendLog::sum_click_count( $campaign_id );

		$pct = static function ( int $n, int $d ) : string {
			if ( $d <= 0 ) { return '0'; }
			return number_format( 100.0 * $n / $d, 1, '.', '' );
		};

		$back_url = admin_url( 'admin.php?page=' . Page::SLUG );
		$edit_url = admin_url( 'post.php?post=' . $campaign_id . '&action=edit' );

		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only filter.
		$filter = isset( $_GET['status'] ) ? sanitize_key( wp_unslash( (string) $_GET['status'] ) ) : '';
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only filter.
		$search = isset( $_GET['s'] )      ? sanitize_text_field( wp_unslash( (string) $_GET['s'] ) ) : '';
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only paginator.
		$paged  = isset( $_GET['paged'] )  ? max( 1, absint( wp_unslash( $_GET['paged'] ) ) ) : 1;

		$per_page   = 50;
		$offset     = ( $paged - 1 ) * $per_page;
		$rows       = NewsletterSendLog::recipients( $campaign_id, $filter, $search, $per_page, $offset );
		$total_rows = NewsletterSendLog::recipients_count( $campaign_id, $filter, $search );
		$total_pages = (int) ceil( $total_rows / $per_page );

		$fmt = (string) get_option( 'date_format', 'Y-m-d' );
		?>
		<div class="wrap">
			<h1 class="wp-heading-inline">
				<?php esc_html_e( 'Newsletter statistics', 'pay4all-pro' ); ?>
			</h1>
			<a href="<?php echo esc_url( $back_url ); ?>" class="page-title-action">&larr; <?php esc_html_e( 'Back to the list', 'pay4all-pro' ); ?></a>
			<a href="<?php echo esc_url( $edit_url ); ?>" class="page-title-action"><?php esc_html_e( 'Open', 'pay4all-pro' ); ?></a>
			<hr class="wp-header-end">

			<h2 style="margin-top:8px;font-weight:400;color:#50575e;">
				<?php echo esc_html( $post->post_title !== '' ? $post->post_title : '#' . $campaign_id ); ?>
			</h2>

			<?php // ─── Aggregate cards ─── ?>
			<div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:12px;margin:20px 0;">
				<?php
				self::render_stat_card( __( 'Sent', 'pay4all-pro' ), (string) $sent, '', '#0a4b78' );
				self::render_stat_card(
					__( 'Opened', 'pay4all-pro' ),
					(string) $opened,
					$pct( $opened, $sent ) . ' %',
					'#00a32a',
					sprintf(
						/* translators: %d = total open events (recipient x rewatches) */
						__( '%d total events', 'pay4all-pro' ), $open_total
					)
				);
				self::render_stat_card(
					__( 'Clicked', 'pay4all-pro' ),
					(string) $clicked,
					$pct( $clicked, $sent ) . ' %',
					'#2271b1',
					sprintf(
						/* translators: %d = total click events */
						__( '%d total events', 'pay4all-pro' ), $click_total
					)
				);
				self::render_stat_card(
					__( 'Unsubscribed', 'pay4all-pro' ),
					(string) $unsubs,
					$pct( $unsubs, $sent ) . ' %',
					'#d63638'
				);
				self::render_stat_card(
					__( 'CTOR', 'pay4all-pro' ),
					$pct( $clicked, $opened ) . ' %',
					__( 'Click-to-open rate', 'pay4all-pro' ),
					'#7c3aed'
				);
				?>
			</div>

			<?php // ─── SVG chart ─── ?>
			<div class="notice notice-info inline" style="margin:20px 0 0;padding:10px 14px;">
				<p style="margin:0;font-size:13px;">
					<span class="dashicons dashicons-info-outline" style="vertical-align:middle;color:#2271b1;"></span>
					<strong><?php esc_html_e( 'About the "Opens" metric:', 'pay4all-pro' ); ?></strong>
					<?php esc_html_e( 'the open rate is measured through an invisible tracking pixel loaded from the message. Some e-mail clients block remote images by default (Apple Mail with Mail Privacy Protection, Outlook in plain-text mode, corporate proxies, security scanners) so the real number of opens is generally higher than the value displayed. The "Clicks" and "Unsubscribes" metrics, on the other hand, are exact.', 'pay4all-pro' ); ?>
				</p>
			</div>

			<div class="postbox" style="margin-top:12px;">
				<div class="postbox-header"><h2 class="hndle" style="padding:8px 12px;"><?php esc_html_e( 'Activity over time', 'pay4all-pro' ); ?></h2></div>
				<div class="inside" style="padding:16px;">
					<?php self::render_chart( NewsletterSendLog::stats_by_day( $campaign_id ), $fmt ); ?>
					<p style="margin:12px 0 0;font-size:12px;color:#666;">
						<span style="display:inline-block;width:12px;height:12px;background:#00a32a;vertical-align:middle;margin-right:4px;border-radius:2px;"></span>
						<?php esc_html_e( 'Opens', 'pay4all-pro' ); ?>
						&nbsp;&nbsp;
						<span style="display:inline-block;width:12px;height:12px;background:#2271b1;vertical-align:middle;margin-right:4px;border-radius:2px;"></span>
						<?php esc_html_e( 'Clicks', 'pay4all-pro' ); ?>
						&nbsp;&nbsp;
						<span style="display:inline-block;width:12px;height:12px;background:#d63638;vertical-align:middle;margin-right:4px;border-radius:2px;"></span>
						<?php esc_html_e( 'Unsubs', 'pay4all-pro' ); ?>
					</p>
				</div>
			</div>

			<?php // ─── Filters + CSV export ─── ?>
			<div style="display:flex;gap:12px;align-items:center;margin:20px 0 12px;flex-wrap:wrap;">
				<form method="get" style="display:flex;gap:6px;align-items:center;">
					<input type="hidden" name="page"     value="<?php echo esc_attr( Page::SLUG ); ?>">
					<input type="hidden" name="action"   value="stats">
					<input type="hidden" name="campaign" value="<?php echo esc_attr( (string) $campaign_id ); ?>">
					<select name="status">
						<?php
						$options = [
							''        => __( 'All recipients',       'pay4all-pro' ),
							'opened'  => __( 'Opened',               'pay4all-pro' ),
							'clicked' => __( 'Clicked',              'pay4all-pro' ),
							'unsubs'  => __( 'Unsubscribed',         'pay4all-pro' ),
							'none'    => __( 'Never opened',         'pay4all-pro' ),
						];
						foreach ( $options as $val => $label ) : ?>
							<option value="<?php echo esc_attr( $val ); ?>" <?php selected( $filter, $val ); ?>><?php echo esc_html( $label ); ?></option>
						<?php endforeach; ?>
					</select>
					<input type="search" name="s" value="<?php echo esc_attr( $search ); ?>"
					       placeholder="<?php esc_attr_e( 'Search e-mail', 'pay4all-pro' ); ?>"
					       style="min-width:220px;">
					<button type="submit" class="button"><?php esc_html_e( 'Filter', 'pay4all-pro' ); ?></button>
				</form>

				<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" style="margin-left:auto;display:flex;gap:6px;">
					<?php wp_nonce_field( self::CSV_ACTION, '_wpnonce' ); ?>
					<input type="hidden" name="action"   value="<?php echo esc_attr( self::CSV_ACTION ); ?>">
					<input type="hidden" name="campaign" value="<?php echo esc_attr( (string) $campaign_id ); ?>">
					<button type="submit" class="button">
						<span class="dashicons dashicons-download" style="vertical-align:middle;"></span>
						<?php esc_html_e( 'Export CSV', 'pay4all-pro' ); ?>
					</button>
				</form>
				<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" style="display:flex;gap:6px;">
					<?php wp_nonce_field( self::XLS_ACTION, '_wpnonce' ); ?>
					<input type="hidden" name="action"   value="<?php echo esc_attr( self::XLS_ACTION ); ?>">
					<input type="hidden" name="campaign" value="<?php echo esc_attr( (string) $campaign_id ); ?>">
					<button type="submit" class="button">
						<span class="dashicons dashicons-media-spreadsheet" style="vertical-align:middle;"></span>
						<?php esc_html_e( 'Export XLSX', 'pay4all-pro' ); ?>
					</button>
				</form>
			</div>

			<?php // ─── Recipients table ─── ?>
			<?php if ( empty( $rows ) ) : ?>
				<p><?php esc_html_e( 'No recipient matches the current filter.', 'pay4all-pro' ); ?></p>
			<?php else : ?>
				<table class="wp-list-table widefat striped">
					<thead>
						<tr>
							<th><?php esc_html_e( 'E-mail',        'pay4all-pro' ); ?></th>
							<th><?php esc_html_e( 'Sent on',       'pay4all-pro' ); ?></th>
							<th><?php esc_html_e( 'Opened on',     'pay4all-pro' ); ?></th>
							<th><?php esc_html_e( 'Clicked on',    'pay4all-pro' ); ?></th>
							<th><?php esc_html_e( 'Unsubscribed on', 'pay4all-pro' ); ?></th>
							<th><?php esc_html_e( 'Status',        'pay4all-pro' ); ?></th>
						</tr>
					</thead>
					<tbody>
					<?php foreach ( $rows as $r ) :
						$sent_lbl   = self::fmt_date( (string) $r['sent_at'], $fmt );
						$opened_lbl = self::fmt_date( (string) ( $r['opened_at']       ?? '' ), $fmt );
						$click_lbl  = self::fmt_date( (string) ( $r['clicked_at']      ?? '' ), $fmt );
						$unsub_lbl  = self::fmt_date( (string) ( $r['unsubscribed_at'] ?? '' ), $fmt );
					?>
						<tr>
							<td><?php echo esc_html( (string) $r['email'] ); ?></td>
							<td><?php echo esc_html( $sent_lbl ); ?></td>
							<td><?php echo esc_html( $opened_lbl !== '' ? $opened_lbl : '-' ); ?></td>
							<td><?php echo esc_html( $click_lbl  !== '' ? $click_lbl  : '-' ); ?></td>
							<td><?php echo esc_html( $unsub_lbl  !== '' ? $unsub_lbl  : '-' ); ?></td>
							<td><?php self::render_status_pill( $r ); ?></td>
						</tr>
					<?php endforeach; ?>
					</tbody>
				</table>

				<?php
				if ( $total_pages > 1 ) {
					$base_args = [ 'page' => Page::SLUG, 'action' => 'stats', 'campaign' => $campaign_id ];
					if ( $filter !== '' ) { $base_args['status'] = $filter; }
					if ( $search !== '' ) { $base_args['s']      = $search; }
					$base = add_query_arg( $base_args, admin_url( 'admin.php' ) );
					$links = paginate_links( [
						'base'      => add_query_arg( 'paged', '%#%', $base ),
						'format'    => '',
						'current'   => $paged,
						'total'     => $total_pages,
						'prev_text' => '&laquo;',
						'next_text' => '&raquo;',
					] );
					if ( $links ) {
						echo '<div class="tablenav bottom" style="margin-top:12px;"><div class="tablenav-pages">' . wp_kses_post( $links ) . '</div></div>';
					}
				}
				?>
			<?php endif; ?>
		</div>
		<?php
	}

	// -----------------------------------------------------------------
	// Small render helpers (cards, pill, chart, date formatting)
	// -----------------------------------------------------------------

	private static function render_stat_card( string $label, string $value, string $sub, string $color, string $hint = '' ) : void {
		?>
		<div style="background:#fff;border:1px solid #dcdcde;border-left:4px solid <?php echo esc_attr( $color ); ?>;border-radius:6px;padding:14px 16px;">
			<div style="font-size:12px;color:#50575e;text-transform:uppercase;letter-spacing:.03em;font-weight:600;"><?php echo esc_html( $label ); ?></div>
			<div style="font-size:26px;font-weight:700;color:<?php echo esc_attr( $color ); ?>;margin-top:4px;">
				<?php echo esc_html( $value ); ?>
				<?php if ( $sub !== '' ) : ?>
					<small style="font-size:14px;color:#50575e;font-weight:500;margin-left:6px;"><?php echo esc_html( $sub ); ?></small>
				<?php endif; ?>
			</div>
			<?php if ( $hint !== '' ) : ?>
				<div style="font-size:11px;color:#666;margin-top:6px;"><?php echo esc_html( $hint ); ?></div>
			<?php endif; ?>
		</div>
		<?php
	}

	/**
	 * Rendered next to each recipient row : one pill per state, in the
	 * following priority order (highest first) : Unsubscribed → Clicked
	 * → Opened → Sent. The pill's colour matches the aggregate card
	 * theme so the merchant can eye-scan the table quickly.
	 *
	 * @param array<string,mixed> $r
	 */
	private static function render_status_pill( array $r ) : void {
		if ( ! empty( $r['unsubscribed_at'] ) ) {
			$label = __( 'Unsubscribed', 'pay4all-pro' ); $bg = '#fef2f2'; $fg = '#b91c1c';
		} elseif ( ! empty( $r['clicked_at'] ) ) {
			$label = __( 'Clicked', 'pay4all-pro' );      $bg = '#eff6ff'; $fg = '#1d4ed8';
		} elseif ( ! empty( $r['opened_at'] ) ) {
			$label = __( 'Opened', 'pay4all-pro' );       $bg = '#f0fdf4'; $fg = '#166534';
		} else {
			$label = __( 'Sent', 'pay4all-pro' );         $bg = '#f5f5f5'; $fg = '#374151';
		}
		echo '<span style="display:inline-block;background:' . esc_attr( $bg ) . ';color:' . esc_attr( $fg ) . ';font-size:11px;font-weight:600;padding:3px 10px;border-radius:10px;">'
			. esc_html( $label ) . '</span>';
	}

	private static function fmt_date( string $iso_utc, string $fmt ) : string {
		if ( $iso_utc === '' || $iso_utc === '0000-00-00 00:00:00' ) { return ''; }
		$ts = strtotime( $iso_utc . ' UTC' );
		if ( ! $ts ) { return ''; }
		return (string) wp_date( $fmt, $ts );
	}

	// -----------------------------------------------------------------
	// SVG chart
	// -----------------------------------------------------------------

	/**
	 * Hand-rolled multi-series line chart. Picks the day range from the
	 * data itself, pads the Y axis with a 10 % headroom, and draws one
	 * polyline per series (opens/clicks/unsubs) plus a data-point circle
	 * with a hover title for the tooltip.
	 *
	 * Empty data → a friendly « no events yet » message so the chart
	 * area doesn't render as a blank rectangle.
	 *
	 * @param array<string,array{opens:int,clicks:int,unsubs:int}> $by_day
	 */
	private static function render_chart( array $by_day, string $date_fmt ) : void {
		if ( empty( $by_day ) ) {
			echo '<p style="text-align:center;color:#666;padding:32px 0;">' . esc_html__( 'No activity recorded yet.', 'pay4all-pro' ) . '</p>';
			return;
		}

		$dates = array_keys( $by_day );
		sort( $dates );
		// Fill the gaps : days between first and last that had zero
		// activity should still appear on the X axis so the merchant
		// sees the actual timeline shape.
		try {
			$start = new \DateTimeImmutable( $dates[0] . 'T00:00:00', new \DateTimeZone( 'UTC' ) );
			$end   = new \DateTimeImmutable( end( $dates ) . 'T00:00:00', new \DateTimeZone( 'UTC' ) );
		} catch ( \Exception $e ) {
			echo '<p style="color:#c00;">' . esc_html( $e->getMessage() ) . '</p>';
			return;
		}
		$series = [];
		for ( $d = $start; $d <= $end; $d = $d->modify( '+1 day' ) ) {
			$key = $d->format( 'Y-m-d' );
			$series[ $key ] = $by_day[ $key ] ?? [ 'opens' => 0, 'clicks' => 0, 'unsubs' => 0 ];
		}

		$w         = 800;
		$h         = 260;
		$pad_l     = 40;
		$pad_r     = 20;
		$pad_t     = 20;
		$pad_b     = 40;
		$plot_w    = $w - $pad_l - $pad_r;
		$plot_h    = $h - $pad_t - $pad_b;
		$n         = count( $series );
		$max_val   = 0;
		foreach ( $series as $row ) {
			$max_val = max( $max_val, $row['opens'], $row['clicks'], $row['unsubs'] );
		}
		if ( $max_val <= 0 ) { $max_val = 1; }
		// 10 % headroom + round up to a « nice » integer step.
		$max_val = (int) ceil( $max_val * 1.15 );

		$x = static function ( int $i ) use ( $pad_l, $plot_w, $n ) : float {
			if ( $n <= 1 ) { return $pad_l + $plot_w / 2; }
			return $pad_l + ( $plot_w * $i / ( $n - 1 ) );
		};
		$y = static function ( int $v ) use ( $pad_t, $plot_h, $max_val ) : float {
			return $pad_t + $plot_h - ( $plot_h * $v / $max_val );
		};

		$colors = [
			'opens'  => '#00a32a',
			'clicks' => '#2271b1',
			'unsubs' => '#d63638',
		];
		$labels_i18n = [
			'opens'  => __( 'Opens',  'pay4all-pro' ),
			'clicks' => __( 'Clicks', 'pay4all-pro' ),
			'unsubs' => __( 'Unsubs', 'pay4all-pro' ),
		];

		$svg  = '<svg viewBox="0 0 ' . $w . ' ' . $h . '" preserveAspectRatio="xMidYMid meet" style="width:100%;height:auto;max-height:280px;font-family:sans-serif;font-size:11px;">';
		// Axes.
		$svg .= '<line x1="' . $pad_l . '" y1="' . $pad_t . '" x2="' . $pad_l . '" y2="' . ( $pad_t + $plot_h ) . '" stroke="#ccc" stroke-width="1"/>';
		$svg .= '<line x1="' . $pad_l . '" y1="' . ( $pad_t + $plot_h ) . '" x2="' . ( $pad_l + $plot_w ) . '" y2="' . ( $pad_t + $plot_h ) . '" stroke="#ccc" stroke-width="1"/>';

		// Y-axis gridlines + labels (4 ticks : 0, 33%, 66%, 100%).
		for ( $t = 0; $t <= 4; $t++ ) {
			$val = (int) round( $max_val * $t / 4 );
			$yp  = $y( $val );
			$svg .= '<line x1="' . $pad_l . '" y1="' . $yp . '" x2="' . ( $pad_l + $plot_w ) . '" y2="' . $yp . '" stroke="#eee" stroke-width="1"/>';
			$svg .= '<text x="' . ( $pad_l - 6 ) . '" y="' . ( $yp + 3 ) . '" text-anchor="end" fill="#666">' . esc_html( (string) $val ) . '</text>';
		}

		// X-axis labels — show at most 6 ticks so nothing overlaps.
		$series_keys = array_keys( $series );
		$max_ticks   = 6;
		$step        = max( 1, (int) ceil( $n / $max_ticks ) );
		foreach ( $series_keys as $i => $day_iso ) {
			if ( $i % $step !== 0 && $i !== $n - 1 ) { continue; }
			$xp  = $x( (int) $i );
			$ts  = strtotime( $day_iso . ' UTC' );
			$lbl = $ts ? (string) wp_date( $date_fmt, $ts ) : $day_iso;
			$svg .= '<text x="' . $xp . '" y="' . ( $pad_t + $plot_h + 16 ) . '" text-anchor="middle" fill="#666">' . esc_html( $lbl ) . '</text>';
		}

		// One polyline + one circle per data point per series.
		foreach ( $colors as $key => $color ) {
			$points = [];
			foreach ( $series_keys as $i => $day_iso ) {
				$v = (int) $series[ $day_iso ][ $key ];
				$points[] = $x( (int) $i ) . ',' . $y( $v );
			}
			$svg .= '<polyline fill="none" stroke="' . esc_attr( $color ) . '" stroke-width="2" points="' . implode( ' ', $points ) . '"/>';
			foreach ( $series_keys as $i => $day_iso ) {
				$v  = (int) $series[ $day_iso ][ $key ];
				$xp = $x( (int) $i );
				$yp = $y( $v );
				$ts = strtotime( $day_iso . ' UTC' );
				$lbl_date = $ts ? (string) wp_date( $date_fmt, $ts ) : $day_iso;
				/* translators: 1: series label (Opens / Clicks / Unsubs), 2: day, 3: count */
				$title = sprintf( __( '%1$s on %2$s : %3$d', 'pay4all-pro' ), $labels_i18n[ $key ], $lbl_date, $v );
				$svg .= '<circle cx="' . $xp . '" cy="' . $yp . '" r="3" fill="' . esc_attr( $color ) . '"><title>' . esc_html( $title ) . '</title></circle>';
			}
		}

		$svg .= '</svg>';
		// phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- assembled from esc_html/esc_attr'd parts + hardcoded SVG shell.
		echo $svg;
	}

	// -----------------------------------------------------------------
	// CSV export
	// -----------------------------------------------------------------

	/**
	 * Streams the full recipient list of a campaign as a CSV file. No
	 * pagination — the merchant asked for « exporter tout le tableau ».
	 * Uses `php://output` in a streaming loop so a 10k-row campaign
	 * doesn't buffer the whole file in memory.
	 */
	public static function handle_csv_export() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( esc_html__( 'Permission denied.', 'pay4all-pro' ) ); }
		check_admin_referer( self::CSV_ACTION );
		$campaign_id = isset( $_POST['campaign'] ) ? absint( wp_unslash( $_POST['campaign'] ) ) : 0;
		if ( $campaign_id <= 0 ) { wp_die( esc_html__( 'Newsletter not found.', 'pay4all-pro' ) ); }
		$post = get_post( $campaign_id );
		if ( ! $post || $post->post_type !== PostTypes::NEWSLETTER ) { wp_die( esc_html__( 'Newsletter not found.', 'pay4all-pro' ) ); }

		$slug = sanitize_title( $post->post_title !== '' ? $post->post_title : ( 'campaign-' . $campaign_id ) );
		$filename = 'newsletter-stats-' . $slug . '-' . gmdate( 'Ymd' ) . '.csv';

		nocache_headers();
		header( 'Content-Type: text/csv; charset=UTF-8' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

		// UTF-8 BOM so Excel opens it correctly. Emit directly to stdout
		// rather than through fwrite() so Plugin Check's WP_Filesystem
		// sniffer stays happy — no filesystem primitive involved.
		echo "\xEF\xBB\xBF";
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- streaming CSV to php://output ; WP_Filesystem has no stdout stream equivalent.
		$fh = fopen( 'php://output', 'w' );
		if ( ! $fh ) { exit; }
		fputcsv( $fh, [ 'email', 'lang', 'sent_at', 'opened_at', 'open_count', 'clicked_at', 'click_count', 'unsubscribed_at', 'status' ] );

		// Stream in pages of 500 so we don't hydrate the whole table into
		// PHP memory for a large campaign.
		$offset = 0;
		$batch  = 500;
		do {
			$rows = NewsletterSendLog::recipients( $campaign_id, '', '', $batch, $offset );
			foreach ( $rows as $r ) {
				fputcsv( $fh, [
					(string) ( $r['email']            ?? '' ),
					(string) ( $r['lang']             ?? '' ),
					(string) ( $r['sent_at']          ?? '' ),
					(string) ( $r['opened_at']        ?? '' ),
					(string) ( $r['open_count']       ?? 0 ),
					(string) ( $r['clicked_at']       ?? '' ),
					(string) ( $r['click_count']      ?? 0 ),
					(string) ( $r['unsubscribed_at']  ?? '' ),
					(string) ( $r['status']           ?? '' ),
				] );
			}
			$offset += $batch;
		} while ( count( $rows ) === $batch );

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closing the stdout stream handle we opened above ; WP_Filesystem doesn't cover php://output.
		fclose( $fh );
		exit;
	}

	// -----------------------------------------------------------------
	// XLSX export — real Office Open XML (ZIP-based), so Excel opens it
	// without the « le format et l'extension ne correspondent pas »
	// warning triggered by SpreadsheetML 2003. Dates are exported as
	// typed date cells (Excel serial numbers with a date number format),
	// not as strings, so PivotTables / sorting / date filtering work.
	//
	// Hand-rolled minimal XLSX so we don't drag PhpSpreadsheet (~10 MB
	// with dependencies) into the plugin just for this screen.
	// -----------------------------------------------------------------

	public static function handle_xls_export() : void {
		if ( ! current_user_can( 'manage_options' ) ) { wp_die( esc_html__( 'Permission denied.', 'pay4all-pro' ) ); }
		check_admin_referer( self::XLS_ACTION );
		$campaign_id = isset( $_POST['campaign'] ) ? absint( wp_unslash( $_POST['campaign'] ) ) : 0;
		if ( $campaign_id <= 0 ) { wp_die( esc_html__( 'Newsletter not found.', 'pay4all-pro' ) ); }
		$post = get_post( $campaign_id );
		if ( ! $post || $post->post_type !== PostTypes::NEWSLETTER ) { wp_die( esc_html__( 'Newsletter not found.', 'pay4all-pro' ) ); }

		if ( ! class_exists( '\\ZipArchive' ) ) {
			wp_die( esc_html__( 'PHP ZipArchive extension is required to build XLSX files.', 'pay4all-pro' ) );
		}

		$slug     = sanitize_title( $post->post_title !== '' ? $post->post_title : ( 'campaign-' . $campaign_id ) );
		$filename = 'newsletter-stats-' . $slug . '-' . gmdate( 'Ymd' ) . '.xlsx';

		// Column layout : mixing typed data (string / number / date) so
		// the merchant can sort / filter / pivot in Excel natively.
		// Style IDs come from build_styles_xml() below (0 default, 1
		// header, 2 date+time).
		$headers = [
			__( 'E-mail',           'pay4all-pro' ), // A string
			__( 'Language',         'pay4all-pro' ), // B string
			__( 'Sent on',          'pay4all-pro' ), // C date
			__( 'Opened on',        'pay4all-pro' ), // D date
			__( 'Open count',       'pay4all-pro' ), // E number
			__( 'Clicked on',       'pay4all-pro' ), // F date
			__( 'Click count',      'pay4all-pro' ), // G number
			__( 'Unsubscribed on',  'pay4all-pro' ), // H date
			__( 'Status',           'pay4all-pro' ), // I string
		];

		// Build the sheet1.xml body row-by-row.
		$sheet_rows  = '<row r="1">';
		foreach ( $headers as $i => $h ) {
			$ref = self::col_letter( $i ) . '1';
			$sheet_rows .= '<c r="' . $ref . '" t="inlineStr" s="1"><is><t>' . self::xml_escape( $h ) . '</t></is></c>';
		}
		$sheet_rows .= '</row>';

		$offset  = 0;
		$batch   = 500;
		$row_idx = 2;
		do {
			$rows = NewsletterSendLog::recipients( $campaign_id, '', '', $batch, $offset );
			foreach ( $rows as $r ) {
				$sheet_rows .= '<row r="' . $row_idx . '">';
				$sheet_rows .= self::cell_str ( self::col_letter( 0 ) . $row_idx, (string) ( $r['email']           ?? '' ) );
				$sheet_rows .= self::cell_str ( self::col_letter( 1 ) . $row_idx, (string) ( $r['lang']            ?? '' ) );
				$sheet_rows .= self::cell_date( self::col_letter( 2 ) . $row_idx, (string) ( $r['sent_at']         ?? '' ) );
				$sheet_rows .= self::cell_date( self::col_letter( 3 ) . $row_idx, (string) ( $r['opened_at']       ?? '' ) );
				$sheet_rows .= self::cell_num ( self::col_letter( 4 ) . $row_idx, (int)    ( $r['open_count']      ?? 0  ) );
				$sheet_rows .= self::cell_date( self::col_letter( 5 ) . $row_idx, (string) ( $r['clicked_at']      ?? '' ) );
				$sheet_rows .= self::cell_num ( self::col_letter( 6 ) . $row_idx, (int)    ( $r['click_count']     ?? 0  ) );
				$sheet_rows .= self::cell_date( self::col_letter( 7 ) . $row_idx, (string) ( $r['unsubscribed_at'] ?? '' ) );
				$sheet_rows .= self::cell_str ( self::col_letter( 8 ) . $row_idx, (string) ( $r['status']          ?? '' ) );
				$sheet_rows .= '</row>';
				$row_idx++;
			}
			$offset += $batch;
		} while ( count( $rows ) === $batch );

		// Assemble the XLSX zip in a temp file, then stream + delete.
		$tmp = wp_tempnam( 'p4all-nl-xlsx-' );
		if ( ! $tmp ) { wp_die( esc_html__( 'Unable to create the export file.', 'pay4all-pro' ) ); }
		$zip = new \ZipArchive();
		if ( $zip->open( $tmp, \ZipArchive::OVERWRITE ) !== true ) {
			wp_delete_file( $tmp );
			wp_die( esc_html__( 'Unable to create the export file.', 'pay4all-pro' ) );
		}

		$sheet_name = self::sanitize_sheet_name( __( 'Statistics', 'pay4all-pro' ) );
		$zip->addFromString( '[Content_Types].xml', self::build_content_types_xml() );
		$zip->addFromString( '_rels/.rels',         self::build_root_rels_xml() );
		$zip->addFromString( 'xl/_rels/workbook.xml.rels', self::build_workbook_rels_xml() );
		$zip->addFromString( 'xl/workbook.xml',     self::build_workbook_xml( $sheet_name ) );
		$zip->addFromString( 'xl/styles.xml',       self::build_styles_xml() );
		$zip->addFromString( 'xl/worksheets/sheet1.xml', self::build_sheet_xml( $sheet_rows, count( $headers ), $row_idx - 1 ) );
		$zip->close();

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
		header( 'Content-Length: ' . filesize( $tmp ) );
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile -- streaming a self-generated temp XLSX to stdout ; WP_Filesystem::get_contents() would load the whole file into memory, defeating the streaming purpose on large campaigns.
		readfile( $tmp );
		wp_delete_file( $tmp );
		exit;
	}

	// -----------------------------------------------------------------
	// XLSX assembly helpers.
	// -----------------------------------------------------------------

	private static function xml_escape( string $s ) : string {
		return htmlspecialchars( $s, ENT_QUOTES | ENT_XML1, 'UTF-8' );
	}

	/** A..Z, then AA..AZ, BA..BZ, … — enough for any newsletter export. */
	private static function col_letter( int $i ) : string {
		$s = '';
		$i++;
		while ( $i > 0 ) {
			$i--;
			$s = chr( 65 + ( $i % 26 ) ) . $s;
			$i = intdiv( $i, 26 );
		}
		return $s;
	}

	private static function cell_str( string $ref, string $val ) : string {
		if ( $val === '' ) { return '<c r="' . $ref . '"/>'; }
		return '<c r="' . $ref . '" t="inlineStr"><is><t xml:space="preserve">' . self::xml_escape( $val ) . '</t></is></c>';
	}

	private static function cell_num( string $ref, int $val ) : string {
		return '<c r="' . $ref . '"><v>' . $val . '</v></c>';
	}

	/**
	 * Emits a date-typed cell. Empty / zero timestamps become empty
	 * cells so Excel doesn't show « 30.12.1899 ». Serial number is the
	 * Excel OLE-Automation date (days since 1899-12-30, which matches
	 * Excel's own reckoning including the 1900 leap-year quirk). We
	 * convert from UTC (DB storage) to the WP timezone first so the
	 * merchant sees the same times as in the admin table.
	 */
	private static function cell_date( string $ref, string $iso_utc ) : string {
		if ( $iso_utc === '' || $iso_utc === '0000-00-00 00:00:00' ) {
			return '<c r="' . $ref . '"/>';
		}
		$ts = strtotime( $iso_utc . ' UTC' );
		if ( ! $ts ) { return '<c r="' . $ref . '"/>'; }
		try {
			$local = ( new \DateTimeImmutable( '@' . $ts ) )->setTimezone( wp_timezone() );
			// Epoch : 1899-12-30 00:00:00 in the SAME (local) timezone
			// so the offset cancels out and the serial matches what Excel
			// expects for a local wall-clock time.
			$epoch = new \DateTimeImmutable( '1899-12-30 00:00:00', wp_timezone() );
			$diff  = $local->getTimestamp() - $epoch->getTimestamp();
			$serial = $diff / 86400.0;
		} catch ( \Throwable $e ) {
			return '<c r="' . $ref . '"/>';
		}
		return '<c r="' . $ref . '" s="2"><v>' . number_format( $serial, 6, '.', '' ) . '</v></c>';
	}

	/** Excel sheet names : no [ ] : * ? / \ and max 31 chars. */
	private static function sanitize_sheet_name( string $name ) : string {
		$name = preg_replace( '/[\\[\\]:\\*\\?\\/\\\\]/u', ' ', $name );
		$name = trim( (string) $name );
		if ( $name === '' ) { $name = 'Sheet1'; }
		if ( function_exists( 'mb_substr' ) ) { $name = mb_substr( $name, 0, 31 ); }
		else                                  { $name = substr(   $name, 0, 31 ); }
		return $name;
	}

	private static function build_content_types_xml() : string {
		return '<?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>';
	}

	private static function build_root_rels_xml() : string {
		return '<?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>';
	}

	private static function build_workbook_rels_xml() : string {
		return '<?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>';
	}

	private static function build_workbook_xml( string $sheet_name ) : string {
		return '<?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="' . self::xml_escape( $sheet_name ) . '" sheetId="1" r:id="rId1"/></sheets>'
			. '</workbook>';
	}

	/**
	 * Styles : index 0 default, index 1 bold header w/ grey fill, index
	 * 2 date+time using custom numFmt 164. Kept minimal so Excel doesn't
	 * complain about missing referenced elements.
	 */
	private static function build_styles_xml() : string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<numFmts count="1"><numFmt numFmtId="164" formatCode="yyyy-mm-dd hh:mm:ss"/></numFmts>'
			. '<fonts count="2">'
			.   '<font><sz val="11"/><name val="Calibri"/></font>'
			.   '<font><b/><sz val="11"/><name val="Calibri"/></font>'
			. '</fonts>'
			. '<fills count="3">'
			.   '<fill><patternFill patternType="none"/></fill>'
			.   '<fill><patternFill patternType="gray125"/></fill>'
			.   '<fill><patternFill patternType="solid"><fgColor rgb="FFF0F0F0"/><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="3">'
			.   '<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"/>'
			.   '<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>'
			. '</cellXfs>'
			. '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
			. '</styleSheet>';
	}

	private static function build_sheet_xml( string $body, int $cols, int $last_row ) : string {
		$last_col = self::col_letter( max( 0, $cols - 1 ) );
		$dim      = 'A1:' . $last_col . max( 1, $last_row );
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'
			.  ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<dimension ref="' . $dim . '"/>'
			. '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
			. '<sheetFormatPr defaultRowHeight="15"/>'
			. '<cols>'
			.   '<col min="1" max="1" width="30"/>'
			.   '<col min="2" max="2" width="8"/>'
			.   '<col min="3" max="3" width="20"/>'
			.   '<col min="4" max="4" width="20"/>'
			.   '<col min="5" max="5" width="12"/>'
			.   '<col min="6" max="6" width="20"/>'
			.   '<col min="7" max="7" width="12"/>'
			.   '<col min="8" max="8" width="20"/>'
			.   '<col min="9" max="9" width="14"/>'
			. '</cols>'
			. '<sheetData>' . $body . '</sheetData>'
			. '<autoFilter ref="' . $dim . '"/>'
			. '</worksheet>';
	}
}
