<?php
/**
 * Email status admin page.
 *
 * @package SubmissionInbox
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Email Status reads plugin-owned mail event tables for admin analytics; WordPress has no native API for these aggregate reports.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this page.

/**
 * Renders email status analytics.
 */
class SUBIN_EmailStatusPage {
	const PAGE_SLUG = 'subin-email-status';

	/**
	 * Email error classifier.
	 *
	 * @var \SUBIN\Mail\EmailErrorClassifier
	 */
	private $error_classifier;

	/**
	 * Constructor.
	 */
	public function __construct() {
		$this->error_classifier = new \SUBIN\Mail\EmailErrorClassifier();
	}

	/**
	 * Render page.
	 *
	 * @return void
	 */
	public function render() {
		if ( ! current_user_can( SUBIN_AdminMenu::CAPABILITY ) ) {
			wp_die( esc_html__( 'You do not have permission to perform this action.', 'submission-inbox' ) );
		}

		$filters = $this->filters_from_request();
		$summary = $this->summary_counts( $filters );
		$sources = $this->source_summary( $filters );
		$issues  = $this->recent_issue_groups( $filters );
		$problem = $this->most_recent_failed_error( $filters );
		$latest  = $this->latest_email_event( $filters );

		SUBIN_View::page_header(
			__( 'Email Status', 'submission-inbox' ),
			__( 'Review email sending issues and notification status across forms, WordPress, and other sources.', 'submission-inbox' ),
			array(
				'primary_action'    => array(
					'label' => __( 'View Email Log', 'submission-inbox' ),
					'url'   => admin_url( 'admin.php?page=subin-email-log' ),
				),
				'secondary_actions' => array(
					array(
						'label' => __( 'SMTP Settings', 'submission-inbox' ),
						'url'   => admin_url( 'admin.php?page=subin-settings&subin_settings_tab=sending' ),
					),
				),
				'wrapper_class'     => 'subin-email-status-page',
			)
		);

		$this->render_filters( $filters );
		if ( empty( $summary['total'] ) ) {
			SUBIN_View::empty_state( __( 'No email events yet', 'submission-inbox' ), __( 'Emails sent through WordPress will appear here after logging starts.', 'submission-inbox' ) );
			SUBIN_View::page_footer();
			return;
		}

		$this->render_problem_alert( $summary, $problem, $latest );
		$this->render_summary_cards( $summary );
		$this->render_source_cards( $sources );
		$this->render_recent_issues( $issues, $latest );
		SUBIN_View::page_footer();
	}

	/**
	 * Read request filters.
	 *
	 * @return array{source:string,date_range:string}
	 */
	private function filters_from_request() {
		$source     = SUBIN_Request::get_key( 'subin_source' );
		$date_range = SUBIN_Request::get_key( 'subin_date_range', '30' );

		return array(
			'source'     => in_array( $source, array_keys( $this->source_labels() ), true ) ? $source : '',
			'date_range' => in_array( $date_range, array( '7', '30', 'all' ), true ) ? $date_range : '30',
		);
	}

	/**
	 * Render filters.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return void
	 */
	private function render_filters( array $filters ) {
		?>
		<div class="subin-filter-panel">
			<form method="get" action="" class="subin-filter-form">
				<input type="hidden" name="page" value="<?php echo esc_attr( self::PAGE_SLUG ); ?>" />
				<div class="subin-filter-main subin-filter-main--status">
					<div class="subin-filter-field">
						<label for="subin-email-status-source"><?php esc_html_e( 'Source', 'submission-inbox' ); ?></label>
						<select id="subin-email-status-source" name="subin_source">
							<option value=""><?php esc_html_e( 'All sources', 'submission-inbox' ); ?></option>
							<?php foreach ( $this->source_labels() as $key => $label ) : ?>
								<option value="<?php echo esc_attr( $key ); ?>" <?php selected( $filters['source'], $key ); ?>><?php echo esc_html( $label ); ?></option>
							<?php endforeach; ?>
						</select>
					</div>
					<div class="subin-filter-field">
						<label for="subin-email-status-date-range"><?php esc_html_e( 'Date range', 'submission-inbox' ); ?></label>
						<select id="subin-email-status-date-range" name="subin_date_range">
							<option value="7" <?php selected( $filters['date_range'], '7' ); ?>><?php esc_html_e( 'Last 7 days', 'submission-inbox' ); ?></option>
							<option value="30" <?php selected( $filters['date_range'], '30' ); ?>><?php esc_html_e( 'Last 30 days', 'submission-inbox' ); ?></option>
							<option value="all" <?php selected( $filters['date_range'], 'all' ); ?>><?php esc_html_e( 'All time', 'submission-inbox' ); ?></option>
						</select>
					</div>
					<div class="subin-filter-actions">
						<button type="submit" class="button subin-btn subin-btn-primary"><?php esc_html_e( 'Filter', 'submission-inbox' ); ?></button>
						<a class="button subin-btn" href="<?php echo esc_url( admin_url( 'admin.php?page=' . self::PAGE_SLUG ) ); ?>"><?php esc_html_e( 'Reset', 'submission-inbox' ); ?></a>
					</div>
				</div>
			</form>
		</div>
		<?php
	}

	/**
	 * Render top problem alert.
	 *
	 * @param array<string,int> $summary Summary.
	 * @param object|null       $problem Latest problem.
	 * @param object|null       $latest Latest event.
	 * @return void
	 */
	private function render_problem_alert( array $summary, $problem, $latest ) {
		$failed_count = absint( $summary['failed'] ?? 0 );
		if ( $failed_count <= 0 ) {
			?>
			<div class="subin-status-alert subin-status-alert--success">
				<h2><?php esc_html_e( 'No email sending issues detected', 'submission-inbox' ); ?></h2>
				<p><?php esc_html_e( 'No failed email events were found for the selected period.', 'submission-inbox' ); ?></p>
			</div>
			<?php
			return;
		}
		$latest_status = sanitize_key( $latest->status ?? '' );
		if ( 'sent' === $latest_status ) {
			?>
			<div class="subin-status-alert subin-status-alert--success">
				<h2><?php esc_html_e( 'Email sending looks healthy now', 'submission-inbox' ); ?></h2>
				<p>
					<?php
					echo esc_html(
						sprintf(
							/* translators: %d: failed email count. */
							_n( '%d older email failed in the selected period, but the latest email event was sent successfully.', '%d older emails failed in the selected period, but the latest email event was sent successfully.', $failed_count, 'submission-inbox' ),
							$failed_count
						)
					);
					?>
				</p>
				<div class="subin-status-alert__actions">
					<a class="subin-action-chip" href="<?php echo esc_url( admin_url( 'admin.php?page=subin-email-log&subin_status=failed' ) ); ?>"><?php esc_html_e( 'Review older failures', 'submission-inbox' ); ?></a>
				</div>
			</div>
			<?php
			return;
		}
		$error = $problem ? sanitize_textarea_field( $problem->error_message ?? '' ) : '';
		$issue = '' !== $error ? $this->error_classifier->classify( $error ) : array();
		?>
		<div class="subin-status-alert subin-status-alert--danger">
			<h2><?php esc_html_e( 'Email sending problem detected', 'submission-inbox' ); ?></h2>
			<p>
				<?php
				echo esc_html(
					sprintf(
						/* translators: %d: failed email count. */
						_n( '%d email failed in the selected period. The most recent error points to an email sending configuration issue.', '%d emails failed in the selected period. The most recent error points to an email sending configuration issue.', $failed_count, 'submission-inbox' ),
						$failed_count
					)
				);
				?>
			</p>
			<?php if ( ! empty( $issue ) ) : ?>
				<p><strong><?php echo esc_html( $issue['title'] ); ?></strong></p>
				<p><?php echo esc_html( $issue['explanation'] ); ?></p>
				<p><?php echo esc_html( $issue['recommended_action'] ); ?></p>
				<pre class="subin-status-alert__code"><?php echo esc_html( $issue['technical_error'] ); ?></pre>
			<?php endif; ?>
			<div class="subin-status-alert__actions">
				<a class="subin-btn subin-btn-primary" href="<?php echo esc_url( admin_url( 'admin.php?page=subin-settings&subin_settings_tab=sending' ) ); ?>"><?php esc_html_e( 'Open SMTP Settings', 'submission-inbox' ); ?></a>
				<a class="subin-action-chip" href="<?php echo esc_url( admin_url( 'admin.php?page=subin-email-log&subin_status=failed' ) ); ?>"><?php esc_html_e( 'View failed emails', 'submission-inbox' ); ?></a>
			</div>
		</div>
		<?php
	}

	/**
	 * Render summary cards.
	 *
	 * @param array<string,int> $summary Summary.
	 * @return void
	 */
	private function render_summary_cards( array $summary ) {
		$cards = array(
			'total'              => array(
				'label' => __( 'Total email events', 'submission-inbox' ),
				'icon'  => 'dashicons-email-alt2',
			),
			'sent'               => array(
				'label' => __( 'Sent by WordPress', 'submission-inbox' ),
				'icon'  => 'dashicons-yes-alt',
			),
			'failed'             => array(
				'label' => __( 'Failed', 'submission-inbox' ),
				'icon'  => 'dashicons-warning',
			),
			'no_email_generated' => array(
				'label' => __( 'No email generated', 'submission-inbox' ),
				'icon'  => 'dashicons-dismiss',
			),
			'unknown'            => array(
				'label' => __( 'Unknown', 'submission-inbox' ),
				'icon'  => 'dashicons-info-outline',
			),
		);
		?>
		<div class="subin-status-summary-grid">
			<?php foreach ( $cards as $key => $card ) : ?>
				<div class="subin-status-summary-card">
					<span class="<?php echo esc_attr( 'subin-status-icon subin-status-icon--' . sanitize_html_class( $key ) . ' dashicons ' . $card['icon'] ); ?>" aria-hidden="true"></span>
					<span class="subin-summary-value"><?php echo esc_html( (string) absint( $summary[ $key ] ?? 0 ) ); ?></span>
					<span class="subin-summary-label"><?php echo esc_html( $card['label'] ); ?></span>
				</div>
			<?php endforeach; ?>
		</div>
		<?php
	}

	/**
	 * Render source summary cards.
	 *
	 * @param array<string,array<string,int>> $sources Sources.
	 * @return void
	 */
	private function render_source_cards( array $sources ) {
		?>
		<div class="subin-section-heading">
			<h2>
				<?php esc_html_e( 'Status by source', 'submission-inbox' ); ?>
				<?php SUBIN_View::help_tooltip( __( 'Shows which part of the site produced the email events.', 'submission-inbox' ) ); ?>
			</h2>
		</div>
		<div class="subin-source-grid">
			<?php foreach ( $this->source_labels() as $key => $label ) : ?>
				<?php
				$row       = $sources[ $key ] ?? array();
				$failed    = absint( $row['failed'] ?? 0 );
				$missing   = absint( $row['no_email_generated'] ?? 0 );
				$unknown   = absint( $row['unknown'] ?? 0 );
				$attention = $failed + $missing + $unknown;
				$latest_status = sanitize_key( $row['latest_status'] ?? '' );
				$has_current_issue = $attention > 0 && 'sent' !== $latest_status;
				?>
				<article class="subin-source-card<?php echo $has_current_issue ? ' subin-source-card--danger' : ''; ?>">
					<div class="subin-source-card__head">
						<div class="subin-source-card__title">
							<span class="<?php echo esc_attr( 'subin-status-icon subin-status-icon--source dashicons ' . $this->source_icon( $key ) ); ?>" aria-hidden="true"></span>
							<h3><?php echo esc_html( $label ); ?></h3>
						</div>
						<span>
							<?php SUBIN_View::badge( $has_current_issue ? __( 'Needs attention', 'submission-inbox' ) : __( 'No current issues', 'submission-inbox' ), $has_current_issue ? 'warning' : 'success' ); ?>
							<?php if ( $has_current_issue ) : ?>
								<?php SUBIN_View::help_tooltip( __( 'Failed emails and form submissions where no notification email was generated.', 'submission-inbox' ) ); ?>
							<?php endif; ?>
						</span>
					</div>
					<div class="subin-source-metrics">
						<span><?php esc_html_e( 'Sent', 'submission-inbox' ); ?> <strong><?php echo esc_html( (string) absint( $row['sent'] ?? 0 ) ); ?></strong></span>
						<span><?php esc_html_e( 'Failed', 'submission-inbox' ); ?> <strong><?php echo esc_html( (string) $failed ); ?></strong></span>
						<span><?php esc_html_e( 'No email generated', 'submission-inbox' ); ?> <strong><?php echo esc_html( (string) $missing ); ?></strong></span>
						<span><?php esc_html_e( 'Unknown', 'submission-inbox' ); ?> <strong><?php echo esc_html( (string) $unknown ); ?></strong></span>
					</div>
					<?php if ( $has_current_issue && ! empty( $row['latest_error'] ) ) : ?>
						<?php $source_issue = $this->error_classifier->classify( (string) $row['latest_error'] ); ?>
						<div class="subin-source-error">
							<span><?php esc_html_e( 'Most recent issue', 'submission-inbox' ); ?></span>
							<strong><?php echo esc_html( $source_issue['title'] ); ?></strong>
							<code><?php echo esc_html( $source_issue['technical_error'] ); ?></code>
						</div>
					<?php else : ?>
						<p class="subin-muted">
							<?php
							echo esc_html(
								$attention > 0
									? __( 'Older issues exist in the selected period, but the latest email event for this source succeeded.', 'submission-inbox' )
									: __( 'No email issues were detected for this source in the selected period.', 'submission-inbox' )
							);
							?>
						</p>
					<?php endif; ?>
					<div class="subin-status-links">
						<a href="<?php echo esc_url( $this->email_log_url_for_source( $key ) ); ?>"><?php echo esc_html( $attention > 0 ? __( 'View in Email Log', 'submission-inbox' ) : __( 'View related emails', 'submission-inbox' ) ); ?></a>
						<a href="<?php echo esc_url( $this->fix_url_for_source( $key ) ); ?>"><?php echo esc_html( $this->fix_label_for_source( $key ) ); ?></a>
					</div>
				</article>
			<?php endforeach; ?>
		</div>
		<?php
	}

	/**
	 * Render recent issues.
	 *
	 * @param array<int,object> $issues Issues.
	 * @param object|null       $latest Latest event.
	 * @return void
	 */
	private function render_recent_issues( array $issues, $latest = null ) {
		$latest_status       = sanitize_key( $latest->status ?? '' );
		$has_current_problem = ! in_array( $latest_status, array( '', 'sent' ), true );
		?>
		<div class="subin-section-heading">
			<h2><?php echo esc_html( $has_current_problem ? __( 'Current issue groups', 'submission-inbox' ) : __( 'Older issue groups', 'submission-inbox' ) ); ?></h2>
			<p class="subin-muted">
				<?php
				echo esc_html(
					$has_current_problem
						? __( 'Repeated active errors are grouped so you can identify the main problem faster.', 'submission-inbox' )
						: __( 'These issues were recorded earlier in the selected period, but the latest email event succeeded.', 'submission-inbox' )
				);
				?>
			</p>
		</div>
		<?php if ( empty( $issues ) ) : ?>
			<?php SUBIN_View::empty_state( __( 'No email issues detected', 'submission-inbox' ), __( 'No failed or missing notification events were found for the selected period.', 'submission-inbox' ) ); ?>
			<?php return; ?>
		<?php endif; ?>
		<div class="subin-issue-list">
			<?php foreach ( $issues as $issue ) : ?>
				<?php
				$issue_data = $this->issue_data_for_group( $issue );
				$error      = sanitize_textarea_field( $issue_data['technical_error'] ?? '' );
				$title      = sanitize_text_field( $issue_data['title'] ?? '' );
				$status     = sanitize_key( $issue->primary_status ?? 'unknown' );
				?>
				<article class="subin-issue-card subin-issue-card--danger">
					<div class="subin-issue-card__head">
						<div>
							<h3>
								<?php echo esc_html( $title ); ?>
								<?php if ( 'invalid_from_email' === ( $issue_data['code'] ?? '' ) ) : ?>
									<?php SUBIN_View::help_tooltip( __( 'This usually means the configured From Email address is not valid for sending.', 'submission-inbox' ) ); ?>
								<?php endif; ?>
							</h3>
							<p>
								<?php
								echo esc_html(
									sprintf(
										/* translators: %d: occurrence count. */
										_n( '%d occurrence', '%d occurrences', absint( $issue->occurrences ?? 0 ), 'submission-inbox' ),
										absint( $issue->occurrences ?? 0 )
									)
								);
								?>
							</p>
						</div>
						<?php SUBIN_View::badge( $this->status_label( $status ), 'failed' === $status ? 'danger' : ( 'no_email_generated' === $status ? 'warning' : 'muted' ) ); ?>
					</div>
					<p><?php echo esc_html( $issue_data['explanation'] ); ?></p>
					<p><?php echo esc_html( $issue_data['recommended_action'] ); ?></p>
					<?php if ( '' !== $error ) : ?>
						<pre class="subin-status-alert__code"><?php echo esc_html( $error ); ?></pre>
					<?php endif; ?>
					<div class="subin-issue-meta">
						<span><?php esc_html_e( 'Sources:', 'submission-inbox' ); ?> <strong><?php echo esc_html( $this->source_names_from_csv( $issue->sources ?? '' ) ); ?></strong></span>
						<span><?php esc_html_e( 'Latest:', 'submission-inbox' ); ?> <strong><?php echo esc_html( $this->format_date( $issue->latest_at ?? '' ) ); ?></strong></span>
						<?php if ( ! empty( $issue->recipients ) ) : ?>
							<span><?php esc_html_e( 'Recipient examples:', 'submission-inbox' ); ?> <strong><?php echo esc_html( $issue->recipients ); ?></strong></span>
						<?php endif; ?>
					</div>
					<div class="subin-status-links">
						<a href="<?php echo esc_url( $this->issue_action_url( $issue_data ) ); ?>"><?php echo esc_html( $this->issue_action_label( $issue_data ) ); ?></a>
						<a href="<?php echo esc_url( admin_url( 'admin.php?page=subin-email-log&subin_status=' . rawurlencode( $status ) . '&s=' . rawurlencode( $error ) ) ); ?>"><?php esc_html_e( 'View affected emails', 'submission-inbox' ); ?></a>
					</div>
				</article>
			<?php endforeach; ?>
		</div>
		<?php
	}

	/**
	 * Summary counts.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,int>
	 */
	private function summary_counts( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$sql         = "SELECT
				COUNT(*) AS total,
				SUM(CASE WHEN e.status = 'sent' THEN 1 ELSE 0 END) AS sent,
				SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END) AS failed,
				SUM(CASE WHEN e.status = 'no_email_generated' THEN 1 ELSE 0 END) AS no_email_generated,
				SUM(CASE WHEN e.status IN ('unknown','pending') THEN 1 ELSE 0 END) AS unknown
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}";
		$row         = $wpdb->get_row( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return array_map( 'absint', is_array( $row ) ? $row : array() );
	}

	/**
	 * Source summary.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,array<string,int>>
	 */
	private function source_summary( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$source_expr = $this->source_group_sql();
		$sql         = "SELECT {$source_expr} AS source_group,
				SUM(CASE WHEN e.status = 'sent' THEN 1 ELSE 0 END) AS sent,
				SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END) AS failed,
				SUM(CASE WHEN e.status = 'no_email_generated' THEN 1 ELSE 0 END) AS no_email_generated,
				SUM(CASE WHEN e.status IN ('unknown','pending') THEN 1 ELSE 0 END) AS unknown
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			GROUP BY source_group";
		$rows        = $wpdb->get_results( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$summary       = array();
		$latest_errors = $this->latest_failed_errors_by_source( $filters );
		$latest_events = $this->latest_events_by_source( $filters );

		foreach ( $this->source_labels() as $key => $label ) {
			$summary[ $key ] = array();
		}

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$key = sanitize_key( $row['source_group'] ?? 'unknown' );
			if ( ! isset( $summary[ $key ] ) ) {
				$key = 'unknown';
			}
			$summary[ $key ] = array(
				'sent'               => absint( $row['sent'] ?? 0 ),
				'failed'             => absint( $row['failed'] ?? 0 ),
				'no_email_generated' => absint( $row['no_email_generated'] ?? 0 ),
				'unknown'            => absint( $row['unknown'] ?? 0 ),
				'latest_error'       => sanitize_textarea_field( $latest_errors[ $key ] ?? '' ),
				'latest_status'      => sanitize_key( $latest_events[ $key ]['status'] ?? '' ),
				'latest_event_error' => sanitize_textarea_field( $latest_events[ $key ]['error_message'] ?? '' ),
			);
		}

		return $summary;
	}

	/**
	 * Latest failed errors keyed by source group.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,string>
	 */
	private function latest_failed_errors_by_source( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$where['sql'] = $where['sql'] ? $where['sql'] . " AND e.status = 'failed' AND e.error_message IS NOT NULL AND e.error_message != ''" : "WHERE e.status = 'failed' AND e.error_message IS NOT NULL AND e.error_message != ''";
		$source_expr = $this->source_group_sql();
		$sql         = "SELECT {$source_expr} AS source_group, e.error_message
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT 100";
		$rows        = $wpdb->get_results( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$errors      = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$key = sanitize_key( $row['source_group'] ?? 'unknown' );
			if ( ! isset( $errors[ $key ] ) ) {
				$errors[ $key ] = sanitize_textarea_field( $row['error_message'] ?? '' );
			}
		}

		return $errors;
	}

	/**
	 * Latest email events keyed by source group.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,array{status:string,error_message:string}>
	 */
	private function latest_events_by_source( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$source_expr = $this->source_group_sql();
		$sql         = "SELECT {$source_expr} AS source_group, e.status, e.error_message
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT 200";
		$rows        = $wpdb->get_results( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$events      = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$key = sanitize_key( $row['source_group'] ?? 'unknown' );
			if ( isset( $events[ $key ] ) ) {
				continue;
			}
			$events[ $key ] = array(
				'status'        => sanitize_key( $row['status'] ?? '' ),
				'error_message' => sanitize_textarea_field( $row['error_message'] ?? '' ),
			);
		}

		return $events;
	}

	/**
	 * Recent issue groups.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<int,object>
	 */
	private function recent_issue_groups( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$where['sql'] = $where['sql'] ? $where['sql'] . " AND e.status IN ('failed','no_email_generated','unknown','pending')" : "WHERE e.status IN ('failed','no_email_generated','unknown','pending')";
		$source_expr = $this->source_group_sql();
		$sql         = "SELECT
				e.error_message,
				e.status,
				e.created_at,
				e.to_email,
				{$source_expr} AS source_group
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT 500";

		$rows   = $wpdb->get_results( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$groups = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$status = sanitize_key( $row['status'] ?? 'unknown' );
			$error  = sanitize_textarea_field( $row['error_message'] ?? '' );

			if ( 'failed' === $status && '' !== $error ) {
				$issue = $this->error_classifier->classify( $error );
				$key   = 'failed:' . $issue['code'];
			} elseif ( 'no_email_generated' === $status ) {
				$issue = $this->missing_notification_issue( $error );
				$key   = 'no_email_generated';
			} else {
				$issue = $this->unknown_status_issue( $error );
				$key   = 'unknown:' . ( '' !== $error ? md5( $error ) : $status );
			}

			if ( ! isset( $groups[ $key ] ) ) {
				$groups[ $key ] = array(
					'error_message'       => $issue['technical_error'],
					'issue_code'          => $issue['code'],
					'issue_title'         => $issue['title'],
					'issue_explanation'   => $issue['explanation'],
					'recommended_action'  => $issue['recommended_action'],
					'action_type'         => $issue['action_type'],
					'technical_error'     => $issue['technical_error'],
					'occurrences'         => 0,
					'latest_at'           => sanitize_text_field( $row['created_at'] ?? '' ),
					'primary_status'      => $status,
					'sources'             => array(),
					'recipients'          => array(),
				);
			}

			$groups[ $key ]['occurrences']++;
			if ( 'failed' === $status ) {
				$groups[ $key ]['primary_status'] = 'failed';
			}
			$source_group = sanitize_key( $row['source_group'] ?? 'unknown' );
			if ( '' !== $source_group ) {
				$groups[ $key ]['sources'][] = $source_group;
			}
			$recipient = sanitize_email( $row['to_email'] ?? '' );
			if ( '' !== $recipient ) {
				$groups[ $key ]['recipients'][] = $recipient;
			}
		}

		usort(
			$groups,
			static function ( array $a, array $b ): int {
				if ( $a['occurrences'] === $b['occurrences'] ) {
					return strcmp( (string) $b['latest_at'], (string) $a['latest_at'] );
				}

				return $b['occurrences'] <=> $a['occurrences'];
			}
		);

		$groups = array_slice( $groups, 0, 8 );

		return array_map(
			static function ( array $group ): object {
				$group['sources']    = implode( ',', array_unique( $group['sources'] ) );
				$group['recipients'] = implode( ', ', array_slice( array_unique( $group['recipients'] ), 0, 3 ) );

				return (object) $group;
			},
			$groups
		);
	}

	/**
	 * Most recent failed error in current filters.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return object|null
	 */
	private function most_recent_failed_error( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$where['sql'] = $where['sql'] ? $where['sql'] . " AND e.status = 'failed'" : "WHERE e.status = 'failed'";
		$sql         = "SELECT e.error_message, e.created_at
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT 1";

		return $wpdb->get_row( $this->prepare_sql( $sql, $where['params'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Latest email event in current filters.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return object|null
	 */
	private function latest_email_event( array $filters ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$sql         = "SELECT e.status, e.error_message, e.created_at
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT 1";

		return $wpdb->get_row( $this->prepare_sql( $sql, $where['params'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Build WHERE clause for filters.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function where_for_filters( array $filters ) {
		$where  = array();
		$params = array();

		if ( ! empty( $filters['source'] ) ) {
			$where[]  = $this->source_group_sql() . ' = %s';
			$params[] = sanitize_key( $filters['source'] );
		}

		if ( '7' === ( $filters['date_range'] ?? '' ) || '30' === ( $filters['date_range'] ?? '' ) ) {
			$where[]  = 'e.created_at >= %s';
			$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( '-' . absint( $filters['date_range'] ) . ' days' ) );
		}

		return array(
			'sql'    => $where ? 'WHERE ' . implode( ' AND ', $where ) : '',
			'params' => $params,
		);
	}

	/**
	 * Source grouping SQL.
	 *
	 * @return string
	 */
	private function source_group_sql( $event_alias = 'e', $lead_alias = 'leads' ) {
		$event_alias = preg_replace( '/[^a-zA-Z0-9_]/', '', (string) $event_alias );
		$lead_alias  = preg_replace( '/[^a-zA-Z0-9_]/', '', (string) $lead_alias );

		return "CASE
			WHEN {$lead_alias}.provider = 'submission_inbox_form' OR {$event_alias}.source = 'submission_inbox_form' OR ({$event_alias}.source = 'submission_inbox_auto_reply' AND ({$lead_alias}.provider IS NULL OR {$lead_alias}.provider = '' OR {$lead_alias}.provider = 'submission_inbox_form')) THEN 'submission_inbox_forms'
			WHEN {$lead_alias}.provider = 'contact_form_7' OR {$event_alias}.source = 'contact_form_7' THEN 'contact_form_7'
			WHEN {$lead_alias}.provider = 'wpforms' OR {$event_alias}.source = 'wpforms' THEN 'wpforms'
			WHEN {$lead_alias}.provider = 'fluent_forms' OR {$event_alias}.source = 'fluent_forms' THEN 'fluent_forms'
			WHEN {$lead_alias}.provider = 'forminator' OR {$event_alias}.source = 'forminator' THEN 'forminator'
			WHEN {$lead_alias}.provider = 'ninja_forms' OR {$event_alias}.source = 'ninja_forms' THEN 'ninja_forms'
			WHEN {$event_alias}.source IN ('wp_mail','wordpress') THEN 'wordpress_core'
			WHEN {$event_alias}.source = 'plugin' THEN 'other_plugins'
			WHEN {$event_alias}.source IS NOT NULL AND {$event_alias}.source != '' AND {$event_alias}.source != 'unknown' THEN 'other_plugins'
			ELSE 'unknown'
		END";
	}

	/**
	 * Prepare SQL when params exist.
	 *
	 * @param string           $sql SQL.
	 * @param array<int,mixed> $params Params.
	 * @return string
	 */
	private function prepare_sql( $sql, array $params ) {
		global $wpdb;

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

	/**
	 * Source labels.
	 *
	 * @return array<string,string>
	 */
	private function source_labels() {
		return array(
			'submission_inbox_forms' => __( 'Submission Inbox Forms', 'submission-inbox' ),
			'contact_form_7'     => __( 'Contact Form 7', 'submission-inbox' ),
			'wpforms'            => __( 'WPForms', 'submission-inbox' ),
			'fluent_forms'       => __( 'Fluent Forms', 'submission-inbox' ),
			'forminator'         => __( 'Forminator', 'submission-inbox' ),
			'ninja_forms'        => __( 'Ninja Forms', 'submission-inbox' ),
			'wordpress_core'     => __( 'WordPress Core', 'submission-inbox' ),
			'other_plugins'      => __( 'Other plugins', 'submission-inbox' ),
			'unknown'            => __( 'Unknown', 'submission-inbox' ),
		);
	}

	/**
	 * Dashicon class for source cards.
	 *
	 * @param string $source Source key.
	 * @return string
	 */
	private function source_icon( $source ) {
		$icons = array(
			'submission_inbox_forms' => 'dashicons-feedback',
			'contact_form_7'     => 'dashicons-email',
			'wpforms'            => 'dashicons-forms',
			'fluent_forms'       => 'dashicons-forms',
			'forminator'         => 'dashicons-forms',
			'ninja_forms'        => 'dashicons-forms',
			'wordpress_core'     => 'dashicons-wordpress',
			'other_plugins'      => 'dashicons-admin-plugins',
			'unknown'            => 'dashicons-editor-help',
		);

		$source = sanitize_key( $source );

		return $icons[ $source ] ?? $icons['unknown'];
	}

	/**
	 * Status label.
	 *
	 * @param string $status Status.
	 * @return string
	 */
	private function status_label( $status ) {
		$labels = array(
			'sent'               => __( 'Sent by WordPress', 'submission-inbox' ),
			'failed'             => __( 'Failed', 'submission-inbox' ),
			'no_email_generated' => __( 'No email generated', 'submission-inbox' ),
			'pending'            => __( 'Unknown', 'submission-inbox' ),
			'unknown'            => __( 'Unknown', 'submission-inbox' ),
		);

		return $labels[ $status ] ?? __( 'Unknown', 'submission-inbox' );
	}

	/**
	 * Email Log URL for a source group.
	 *
	 * @param string $source_group Source group.
	 * @return string
	 */
	private function email_log_url_for_source( $source_group ) {
		$source_map = array(
			'submission_inbox_forms' => 'submission_inbox_form',
			'contact_form_7'     => 'contact_form_7',
			'wpforms'            => 'wpforms',
			'fluent_forms'       => 'fluent_forms',
			'forminator'         => 'forminator',
			'ninja_forms'        => 'ninja_forms',
			'wordpress_core'     => 'wp_mail',
			'other_plugins'      => 'plugin',
			'unknown'            => 'unknown',
		);
		$args       = array( 'page' => 'subin-email-log' );

		if ( isset( $source_map[ $source_group ] ) ) {
			$args['subin_source'] = $source_map[ $source_group ];
		}

		return add_query_arg( $args, admin_url( 'admin.php' ) );
	}

	/**
	 * Fix URL for a source group.
	 *
	 * @param string $source_group Source group.
	 * @return string
	 */
	private function fix_url_for_source( $source_group ) {
		if ( 'submission_inbox_forms' === $source_group ) {
			return admin_url( 'admin.php?page=submission-inbox-forms' );
		}

		return admin_url( 'admin.php?page=subin-settings&subin_settings_tab=sending' );
	}

	/**
	 * Fix action label for a source group.
	 *
	 * @param string $source_group Source group.
	 * @return string
	 */
	private function fix_label_for_source( $source_group ) {
		return 'submission_inbox_forms' === $source_group ? __( 'Forms', 'submission-inbox' ) : __( 'SMTP Settings', 'submission-inbox' );
	}

	/**
	 * Issue data from an issue group.
	 *
	 * @param object $issue Issue group.
	 * @return array{code:string,title:string,explanation:string,recommended_action:string,action_type:string,technical_error:string}
	 */
	private function issue_data_for_group( $issue ) {
		if ( ! empty( $issue->issue_title ) ) {
			return array(
				'code'               => sanitize_key( $issue->issue_code ?? 'unknown_email_error' ),
				'title'              => sanitize_text_field( $issue->issue_title ),
				'explanation'        => sanitize_text_field( $issue->issue_explanation ?? '' ),
				'recommended_action' => sanitize_text_field( $issue->recommended_action ?? '' ),
				'action_type'        => sanitize_key( $issue->action_type ?? 'email_log' ),
				'technical_error'    => $this->error_classifier->sanitize_technical_error( (string) ( $issue->technical_error ?? $issue->error_message ?? '' ) ),
			);
		}

		$error  = sanitize_textarea_field( $issue->error_message ?? '' );
		$status = sanitize_key( $issue->primary_status ?? 'unknown' );

		if ( 'no_email_generated' === $status ) {
			return $this->missing_notification_issue( $error );
		}

		return $this->error_classifier->classify( $error );
	}

	/**
	 * Issue data for missing notification events.
	 *
	 * @param string $technical_error Technical error.
	 * @return array{code:string,title:string,explanation:string,recommended_action:string,action_type:string,technical_error:string}
	 */
	private function missing_notification_issue( $technical_error = '' ) {
		return array(
			'code'               => 'no_email_generated',
			'title'              => __( 'No email generated', 'submission-inbox' ),
			'explanation'        => __( 'A form submission was saved, but no notification email was created.', 'submission-inbox' ),
			'recommended_action' => __( 'Check the form notification settings.', 'submission-inbox' ),
			'action_type'        => 'form_notification_settings',
			'technical_error'    => $this->error_classifier->sanitize_technical_error( (string) $technical_error ),
		);
	}

	/**
	 * Issue data for unknown event statuses.
	 *
	 * @param string $technical_error Technical error.
	 * @return array{code:string,title:string,explanation:string,recommended_action:string,action_type:string,technical_error:string}
	 */
	private function unknown_status_issue( $technical_error = '' ) {
		return array(
			'code'               => 'unknown_email_error',
			'title'              => __( 'Email status is unknown', 'submission-inbox' ),
			'explanation'        => __( 'Submission Inbox could not determine the email notification status.', 'submission-inbox' ),
			'recommended_action' => __( 'Review the related email events in Email Log.', 'submission-inbox' ),
			'action_type'        => 'email_log',
			'technical_error'    => $this->error_classifier->sanitize_technical_error( (string) $technical_error ),
		);
	}

	/**
	 * Action URL for classified issue.
	 *
	 * @param array<string,string> $issue Issue data.
	 * @return string
	 */
	private function issue_action_url( array $issue ) {
		if ( 'form_notification_settings' === ( $issue['action_type'] ?? '' ) ) {
			return admin_url( 'admin.php?page=submission-inbox-forms' );
		}

		if ( 'settings_sending' === ( $issue['action_type'] ?? '' ) ) {
			return admin_url( 'admin.php?page=subin-settings&subin_settings_tab=sending' );
		}

		return admin_url( 'admin.php?page=subin-email-log&subin_status=failed' );
	}

	/**
	 * Action label for classified issue.
	 *
	 * @param array<string,string> $issue Issue data.
	 * @return string
	 */
	private function issue_action_label( array $issue ) {
		if ( 'form_notification_settings' === ( $issue['action_type'] ?? '' ) ) {
			return __( 'Check form settings', 'submission-inbox' );
		}

		if ( 'settings_sending' === ( $issue['action_type'] ?? '' ) ) {
			return __( 'Open SMTP Settings', 'submission-inbox' );
		}

		return __( 'View Email Log', 'submission-inbox' );
	}

	/**
	 * Readable issue title.
	 *
	 * @param string $error Error.
	 * @return string
	 */
	private function issue_title_for_error( $error ) {
		$error_lower = strtolower( $error );

		if ( false !== strpos( $error_lower, 'invalid address: (from)' ) ) {
			return __( 'Invalid From Email address', 'submission-inbox' );
		}

		if ( false !== strpos( $error_lower, 'smtp connect' ) ) {
			return __( 'SMTP connection failed', 'submission-inbox' );
		}

		if ( false !== strpos( $error_lower, 'authenticate' ) ) {
			return __( 'SMTP authentication failed', 'submission-inbox' );
		}

		if ( 'no_email_generated' === $error ) {
			return __( 'No email generated', 'submission-inbox' );
		}

		return __( 'Email sending error', 'submission-inbox' );
	}

	/**
	 * Readable issue description.
	 *
	 * @param string $error Error.
	 * @return string
	 */
	private function issue_description_for_error( $error ) {
		$error_lower = strtolower( $error );

		if ( false !== strpos( $error_lower, 'invalid address: (from)' ) ) {
			return __( 'WordPress could not send emails because the From Email address appears to be invalid.', 'submission-inbox' );
		}

		if ( false !== strpos( $error_lower, 'smtp connect' ) ) {
			return __( 'WordPress could not connect to the configured SMTP server.', 'submission-inbox' );
		}

		if ( false !== strpos( $error_lower, 'authenticate' ) ) {
			return __( 'WordPress could not authenticate with the configured SMTP account.', 'submission-inbox' );
		}

		if ( 'no_email_generated' === $error ) {
			return __( 'A lead was saved, but no notification email was generated.', 'submission-inbox' );
		}

		return __( 'WordPress reported a repeated email sending error.', 'submission-inbox' );
	}

	/**
	 * Source names from CSV source groups.
	 *
	 * @param string $sources CSV source groups.
	 * @return string
	 */
	private function source_names_from_csv( $sources ) {
		$labels = $this->source_labels();
		$names  = array();

		foreach ( explode( ',', (string) $sources ) as $source ) {
			$source = sanitize_key( $source );
			if ( isset( $labels[ $source ] ) ) {
				$names[] = $labels[ $source ];
			}
		}

		return implode( ', ', array_unique( $names ) );
	}

	/**
	 * Lead/form label.
	 *
	 * @param object $issue Issue.
	 * @return string
	 */
	private function issue_lead_label( $issue ) {
		$name = sanitize_text_field( $issue->lead_form_name ?? '' );
		if ( '' !== $name ) {
			return $name;
		}

		return ! empty( $issue->lead_id ) ? '#' . absint( $issue->lead_id ) : '';
	}

	/**
	 * Format date.
	 *
	 * @param string $date Date.
	 * @return string
	 */
	private function format_date( $date ) {
		return $date ? mysql2date( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), $date ) : '';
	}
}
