<?php
/**
 * Mail event repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\MailLog;

use SUBIN\Forms\FormSubmissionContext;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom mail event tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes mail event records.
 */
class MailEventRepository {
	const STATUS_SENT    = 'sent';
	const STATUS_FAILED  = 'failed';
	const STATUS_PENDING = 'pending';
	const STATUS_UNKNOWN = 'unknown';
	const STATUS_NO_EMAIL_GENERATED = 'no_email_generated';

	const EVENT_KIND_GENERIC              = 'generic';
	const EVENT_KIND_ADMIN_NOTIFICATION   = 'admin_notification';
	const EVENT_KIND_VISITOR_CONFIRMATION = 'visitor_confirmation';
	const EVENT_KIND_AUTO_REPLY_TEST      = 'auto_reply_test';
	const EVENT_KIND_HEALTH_VERIFICATION  = 'health_verification';

	const STALLED_AFTER_SECONDS = 600;

	/**
	 * Cached mail-event column names for rolling-upgrade compatibility.
	 *
	 * @var array<string,bool>|null
	 */
	private $event_columns;

	/**
	 * Create pending mail event.
	 *
	 * @param array<string,mixed> $data Event data.
	 * @return int
	 */
	public function create_pending( array $data ) {
		global $wpdb;

		$table    = $wpdb->prefix . 'subin_mail_events';
		$now      = current_time( 'mysql' );
		$metadata = $this->metadata_for_storage( $data );

		$row = array_merge(
			array(
				'lead_id'        => ! empty( $data['lead_id'] ) ? absint( $data['lead_id'] ) : null,
				'correlation_id' => $this->sanitize_correlation_id( $data['correlation_id'] ?? '' ),
				'source'         => sanitize_key( $data['source'] ?? 'unknown' ),
			),
			$metadata,
			array(
				'to_email'      => sanitize_textarea_field( $data['to_email'] ?? '' ),
				'from_email'    => sanitize_email( $data['from_email'] ?? '' ),
				'subject'       => sanitize_text_field( $data['subject'] ?? '' ),
				'message_hash'  => sanitize_text_field( $data['message_hash'] ?? '' ),
				'status'        => self::STATUS_PENDING,
				'error_message' => null,
				'created_at'    => $now,
				'updated_at'    => $now,
			)
		);
		if ( $this->has_event_column( 'completed_at' ) ) {
			$row['completed_at'] = null;
		}

		$inserted = $wpdb->insert( $table, $row, $this->formats_for_row( $row ) );

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Create an event for a message that was not generated.
	 *
	 * @param array<string,mixed> $data Event data.
	 * @return int
	 */
	public function create_no_email_generated( array $data ) {
		global $wpdb;

		$table    = $wpdb->prefix . 'subin_mail_events';
		$now      = current_time( 'mysql' );
		$metadata = $this->metadata_for_storage( $data );

		$row = array_merge(
			array(
				'lead_id'        => ! empty( $data['lead_id'] ) ? absint( $data['lead_id'] ) : null,
				'correlation_id' => $this->sanitize_correlation_id( $data['correlation_id'] ?? '' ),
				'source'         => sanitize_key( $data['source'] ?? 'unknown' ),
			),
			$metadata,
			array(
				'to_email'      => sanitize_textarea_field( $data['to_email'] ?? '' ),
				'from_email'    => '',
				'subject'       => sanitize_text_field( $data['subject'] ?? '' ),
				'message_hash'  => '',
				'status'        => self::STATUS_NO_EMAIL_GENERATED,
				'error_message' => sanitize_textarea_field( $data['error_message'] ?? '' ),
				'created_at'    => $now,
				'updated_at'    => $now,
			)
		);
		if ( $this->has_event_column( 'completed_at' ) ) {
			$row['completed_at'] = $now;
		}

		$inserted = $wpdb->insert( $table, $row, $this->formats_for_row( $row ) );

		$event_id = $inserted ? absint( $wpdb->insert_id ) : 0;
		if ( $event_id > 0 ) {
			do_action( 'subin_mail_event_no_email_generated', $event_id );
		}

		return $event_id;
	}

	/**
	 * Add or correct delivery metadata after an event was created.
	 *
	 * This is primarily useful when a transport hook creates the event before a
	 * durable queue row id is available. Only recognized, valid metadata fields
	 * are updated; existing event content and status remain untouched.
	 *
	 * @param int                 $event_id Event id.
	 * @param array<string,mixed> $metadata Delivery metadata.
	 * @return bool
	 */
	public function update_metadata( $event_id, array $metadata ) {
		global $wpdb;

		$event_id = absint( $event_id );
		$metadata = $this->sanitize_event_metadata( $metadata, $event_id );
		if ( $event_id <= 0 || empty( $metadata ) ) {
			return false;
		}

		$format_map = array(
			'job_id'        => '%d',
			'rule_id'       => '%d',
			'template_id'   => '%d',
			'rule_version'  => '%d',
			'provider'      => '%s',
			'form_id'       => '%s',
			'form_name'     => '%s',
			'rule_name'     => '%s',
			'template_name' => '%s',
			'delivery_mode' => '%s',
			'scheduled_at'  => '%s',
			'reason_code'   => '%s',
			'event_kind'    => '%s',
			'parent_event_id' => '%d',
			'retry_of_event_id' => '%d',
		);
		$formats = array();
		foreach ( array_keys( $metadata ) as $key ) {
			$formats[] = $format_map[ $key ] ?? '%s';
		}

		$metadata['updated_at'] = current_time( 'mysql' );
		$formats[]               = '%s';
		$updated = $wpdb->update(
			$wpdb->prefix . 'subin_mail_events',
			$metadata,
			array( 'id' => $event_id ),
			$formats,
			array( '%d' )
		);
		if ( false !== $updated ) {
			do_action( 'subin_mail_event_metadata_updated', $event_id );
		}

		return false !== $updated;
	}

	/**
	 * Mark event sent.
	 *
	 * @param int $event_id Event id.
	 * @return bool
	 */
	public function mark_sent( $event_id ) {
		return $this->update_status( $event_id, 'sent', '' );
	}

	/**
	 * Mark event failed.
	 *
	 * @param int    $event_id Event id.
	 * @param string $error Error message.
	 * @return bool
	 */
	public function mark_failed( $event_id, $error ) {
		return $this->update_status( $event_id, 'failed', $error );
	}

	/**
	 * Close a pending event when WordPress emitted no terminal mail hook.
	 *
	 * Unknown is a completed observation with an unverified transport outcome; it
	 * is not the same state as a request that is still being processed.
	 *
	 * @param int    $event_id Event id.
	 * @param string $error Safe technical context.
	 * @param string $reason_code Stable machine-readable reason.
	 * @return bool
	 */
	public function mark_unknown( $event_id, $error = '', $reason_code = 'terminal_hook_missing' ) {
		global $wpdb;

		$event_id   = absint( $event_id );
		$reason_code = substr( sanitize_key( (string) $reason_code ), 0, 100 );
		if ( $event_id <= 0 || '' === $reason_code ) {
			return false;
		}

		$table = $wpdb->prefix . 'subin_mail_events';
		$now   = current_time( 'mysql' );
		$set   = 'status = %s, error_message = NULLIF(%s, \'\'), reason_code = %s, updated_at = %s';
		$args  = array(
			self::STATUS_UNKNOWN,
			'' !== $error ? sanitize_textarea_field( $error ) : '',
			$reason_code,
			$now,
		);
		if ( $this->has_event_column( 'completed_at' ) ) {
			$set   .= ', completed_at = %s';
			$args[] = $now;
		}
		$args[] = $event_id;
		$args[] = self::STATUS_PENDING;

		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET {$set} WHERE id = %d AND status = %s",
				$args
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		if ( false === $updated ) {
			return false;
		}
		if ( $updated > 0 ) {
			do_action( 'subin_mail_event_status_updated', $event_id, self::STATUS_UNKNOWN );
			return true;
		}

		$current_status = sanitize_key(
			(string) $wpdb->get_var(
				$wpdb->prepare( "SELECT status FROM {$table} WHERE id = %d LIMIT 1", $event_id )
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return self::STATUS_UNKNOWN === $current_status;
	}

	/**
	 * Get event by id.
	 *
	 * @param int $event_id Event id.
	 * @return object|null
	 */
	public function get( $event_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $event_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @param int $limit Optional result limit; zero returns all events.
	 * @return array<int,object>
	 */
	public function get_by_lead_id( $lead_id, $limit = 0 ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';
		$limit = absint( $limit );
		$sql   = "SELECT * FROM {$table} WHERE lead_id = %d ORDER BY created_at DESC, id DESC";
		$args  = array( absint( $lead_id ) );
		if ( $limit > 0 ) {
			$sql   .= ' LIMIT %d';
			$args[] = min( 100, $limit );
		}

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

	/**
	 * Find a completed event for one deterministic notification delivery.
	 *
	 * A body hash selects the sent state; an empty hash selects a deliberate
	 * no-email-generated outcome. Correlation/source are the completion identity:
	 * other wp_mail filters may legitimately transform the body after acceptance.
	 * Database errors fail closed so a worker does not resend while history is unavailable.
	 *
	 * @param string $correlation_id Durable delivery key.
	 * @param string $source Mail source.
	 * @param string $message_hash Expected body hash, or empty for no-email outcomes.
	 * @return object|null|\WP_Error
	 */
	public function find_completed_delivery( $correlation_id, $source, $message_hash = '' ) {
		global $wpdb;

		$correlation_id = $this->sanitize_correlation_id( $correlation_id );
		$source         = sanitize_key( (string) $source );
		$message_hash   = strtolower( sanitize_text_field( (string) $message_hash ) );
		if ( '' === $correlation_id || '' === $source || ( '' !== $message_hash && ! preg_match( '/^[a-f0-9]{64}$/', $message_hash ) ) ) {
			return new \WP_Error(
				'subin_notification_delivery_identity_invalid',
				__( 'The notification delivery identity is invalid.', 'submission-inbox' ),
				array( 'retryable' => false )
			);
		}

		$table  = $wpdb->prefix . 'subin_mail_events';
		$status = '' === $message_hash ? self::STATUS_NO_EMAIL_GENERATED : self::STATUS_SENT;
		$sql    = "SELECT * FROM {$table} WHERE correlation_id = %s AND source = %s AND status = %s";
		$params = array( $correlation_id, $source, $status );
		$sql .= ' ORDER BY id DESC LIMIT 1';

		$wpdb->last_error = '';
		$event = $wpdb->get_row( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error(
				'subin_notification_delivery_lookup_failed',
				__( 'Notification delivery history could not be verified.', 'submission-inbox' ),
				array( 'retryable' => true )
			);
		}

		return $event ?: null;
	}

	/**
	 * Find the latest event for one deterministic notification delivery.
	 *
	 * This includes pending and failed attempts so the worker can complete a
	 * fallback event when wp_mail hooks were bypassed.
	 *
	 * @param string $correlation_id Durable delivery key.
	 * @param string $source Mail source.
	 * @return object|null|\WP_Error
	 */
	public function find_delivery_event( $correlation_id, $source ) {
		global $wpdb;

		$correlation_id = $this->sanitize_correlation_id( $correlation_id );
		$source         = sanitize_key( (string) $source );
		if ( '' === $correlation_id || '' === $source ) {
			return new \WP_Error(
				'subin_notification_delivery_identity_invalid',
				__( 'The notification delivery identity is invalid.', 'submission-inbox' ),
				array( 'retryable' => false )
			);
		}

		$table = $wpdb->prefix . 'subin_mail_events';
		$wpdb->last_error = '';
		$event = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE correlation_id = %s AND source = %s ORDER BY id DESC LIMIT 1",
				$correlation_id,
				$source
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error(
				'subin_notification_delivery_lookup_failed',
				__( 'Notification delivery history could not be verified.', 'submission-inbox' ),
				array( 'retryable' => true )
			);
		}

		return $event ?: null;
	}

	/**
	 * Get events grouped by lead id.
	 *
	 * @param array<int,int> $lead_ids Lead ids.
	 * @return array<int,array<int,object>>
	 */
	public function get_grouped_by_lead_ids( array $lead_ids ) {
		global $wpdb;

		$lead_ids = array_values( array_filter( array_map( 'absint', $lead_ids ) ) );
		if ( empty( $lead_ids ) ) {
			return array();
		}

		$table        = $wpdb->prefix . 'subin_mail_events';
		$placeholders = implode( ',', array_fill( 0, count( $lead_ids ), '%d' ) );
		$rows         = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE lead_id IN ({$placeholders}) ORDER BY lead_id ASC, created_at DESC, id DESC",
				$lead_ids
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		);

		$grouped = array();
		foreach ( $rows as $row ) {
			$lead_id = absint( $row->lead_id ?? 0 );
			if ( ! isset( $grouped[ $lead_id ] ) ) {
				$grouped[ $lead_id ] = array();
			}

			$grouped[ $lead_id ][] = $row;
		}

		return $grouped;
	}

	/**
	 * Get paginated email events.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_paginated_events( array $args ) {
		global $wpdb;

		$context    = $this->query_context();
		$where      = $this->build_where( $args );
		$limit      = max( 1, absint( $args['per_page'] ?? 20 ) );
		$keyset     = $this->keyset_direction( $args['keyset_order'] ?? '' );
		$offset     = $keyset ? 0 : max( 0, absint( $args['offset'] ?? 0 ) );
		$direction  = $keyset ?: $this->sort_direction( $args['sort'] ?? 'newest' );
		$order_by   = $keyset ? "e.id {$direction}" : "e.created_at {$direction}, e.id {$direction}";
		$sql        = "SELECT e.*, {$context['select']}
			FROM {$context['table']} e
			{$context['joins']}
			{$where['sql']}
			ORDER BY {$order_by}
			LIMIT %d OFFSET %d";
		$params  = array_merge( $where['params'], array( $limit, $offset ) );

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

	/**
	 * Count email events.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @return int
	 */
	public function count_events( array $filters = array() ) {
		global $wpdb;

		$context    = $this->query_context();
		$where      = $this->build_where( $filters );
		$sql        = "SELECT COUNT(*)
			FROM {$context['table']} e
			{$context['joins']}
			{$where['sql']}";

		if ( ! empty( $where['params'] ) ) {
			return absint( $wpdb->get_var( $wpdb->prepare( $sql, $where['params'] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return absint( $wpdb->get_var( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Count events without converting a failed SELECT into zero.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @return int|\WP_Error
	 */
	public function count_events_checked( array $filters = array() ) {
		global $wpdb;

		$wpdb->last_error = '';
		$count = $this->count_events( $filters );
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error( 'subin_mail_event_count_failed', __( 'Email Log events could not be counted.', 'submission-inbox' ) );
		}

		return absint( $count );
	}

	/**
	 * Check whether a provider has authoritatively observed an exact event.
	 *
	 * Retry workers use this immediately before transport so an authenticated
	 * provider signal which arrived while a job was queued fails closed.
	 *
	 * @param int $event_id Mail event id.
	 * @return bool|\WP_Error
	 */
	public function has_authoritative_provider_evidence( $event_id ) {
		global $wpdb;

		$event_id = absint( $event_id );
		if ( $event_id <= 0 ) {
			return new \WP_Error( 'subin_mail_event_evidence_identity_invalid', __( 'The retry event identity is invalid.', 'submission-inbox' ) );
		}
		foreach ( array( 'provider_status', 'provider_message_id', 'provider_evidence_source', 'provider_evidence_at' ) as $column ) {
			if ( ! $this->has_event_column( $column ) ) {
				return false;
			}
		}

		$table = $wpdb->prefix . 'subin_mail_events';
		$wpdb->last_error = '';
		$event = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT provider_status, provider_message_id, provider_evidence_source, provider_evidence_at
				FROM {$table} WHERE id = %d LIMIT 1",
				$event_id
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error( 'subin_mail_event_evidence_read_failed', __( 'Provider evidence could not be verified before retrying.', 'submission-inbox' ) );
		}
		if ( ! $event ) {
			return new \WP_Error( 'subin_mail_event_evidence_event_missing', __( 'The original email event no longer exists.', 'submission-inbox' ) );
		}

		$status     = sanitize_key( (string) ( $event->provider_status ?? '' ) );
		$message_id = sanitize_text_field( (string) ( $event->provider_message_id ?? '' ) );
		$source     = sanitize_key( (string) ( $event->provider_evidence_source ?? '' ) );
		$occurred   = sanitize_text_field( (string) ( $event->provider_evidence_at ?? '' ) );

		return in_array( $status, array( 'accepted', 'queued', 'delivered', 'bounced', 'deferred', 'dropped', 'complained', 'opened' ), true )
			&& '' !== $message_id
			&& '' !== $source
			&& 1 === preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $occurred );
	}

	/**
	 * Summarize all outcomes in the current non-outcome scope with one query.
	 *
	 * Status/outcome are deliberately removed so every clickable outcome card is
	 * comparable while search, origin, form, rule, type, and date stay in scope.
	 *
	 * @param array<string,mixed> $filters Scope filters.
	 * @return array<string,int>
	 */
	public function get_summary( array $filters = array() ) {
		global $wpdb;

		unset( $filters['status'], $filters['outcome'] );
		$context = $this->query_context();
		$where   = $this->build_where( $filters );
		$outcome = $this->effective_outcome_expression();
		$sql     = "SELECT
					COUNT(*) AS total,
					COALESCE(SUM(CASE WHEN {$outcome} = 'accepted' THEN 1 ELSE 0 END), 0) AS accepted,
					COALESCE(SUM(CASE WHEN {$outcome} = 'failed' THEN 1 ELSE 0 END), 0) AS failed,
					COALESCE(SUM(CASE WHEN {$outcome} = 'not_generated' THEN 1 ELSE 0 END), 0) AS no_email_generated,
					COALESCE(SUM(CASE WHEN {$outcome} IN ('processing', 'stalled') THEN 1 ELSE 0 END), 0) AS pending,
					COALESCE(SUM(CASE WHEN {$outcome} = 'awaiting' THEN 1 ELSE 0 END), 0) AS unknown,
					COALESCE(SUM(CASE WHEN {$outcome} = 'processing' THEN 1 ELSE 0 END), 0) AS processing,
					COALESCE(SUM(CASE WHEN {$outcome} = 'stalled' THEN 1 ELSE 0 END), 0) AS stalled,
					COALESCE(SUM(CASE WHEN {$outcome} = 'queued' THEN 1 ELSE 0 END), 0) AS queued,
					COALESCE(SUM(CASE WHEN {$outcome} = 'delivered' THEN 1 ELSE 0 END), 0) AS delivered,
					COALESCE(SUM(CASE WHEN {$outcome} = 'bounced' THEN 1 ELSE 0 END), 0) AS bounced,
					COALESCE(SUM(CASE WHEN {$outcome} = 'deferred' THEN 1 ELSE 0 END), 0) AS deferred,
					COALESCE(SUM(CASE WHEN {$outcome} = 'dropped' THEN 1 ELSE 0 END), 0) AS dropped,
					COALESCE(SUM(CASE WHEN {$outcome} = 'complained' THEN 1 ELSE 0 END), 0) AS complained,
					COALESCE(SUM(CASE WHEN {$outcome} = 'opened' THEN 1 ELSE 0 END), 0) AS opened
				FROM {$context['table']} e
				{$context['joins']}
				{$where['sql']}";
		$prepared_sql = $sql;
		if ( ! empty( $where['params'] ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- build_where() returns generated clauses with a separate placeholder value list.
			$prepared_sql = $wpdb->prepare( $sql, $where['params'] );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is either placeholder-free or prepared immediately above; identifiers are internal.
		$row = $wpdb->get_row( $prepared_sql, ARRAY_A );
		$row     = is_array( $row ) ? $row : array();

		$summary = array();
		foreach ( array( 'total', 'accepted', 'failed', 'no_email_generated', 'pending', 'unknown', 'processing', 'stalled', 'queued', 'delivered', 'bounced', 'deferred', 'dropped', 'complained', 'opened' ) as $key ) {
			$summary[ $key ] = absint( $row[ $key ] ?? 0 );
		}
		$summary['sent']     = $summary['accepted'];
		$summary['awaiting'] = $summary['pending'] + $summary['unknown'];

		return $summary;
	}

	/**
	 * Read summary metrics without masking a failed aggregate query.
	 *
	 * @param array<string,mixed> $filters Scope filters.
	 * @return array<string,int>|\WP_Error
	 */
	public function get_summary_checked( array $filters = array() ) {
		global $wpdb;

		$wpdb->last_error = '';
		$summary = $this->get_summary( $filters );
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error( 'subin_mail_event_summary_failed', __( 'Email Log summary metrics could not be read.', 'submission-inbox' ) );
		}

		return $summary;
	}

	/**
	 * Get one exact event enriched with its immutable delivery context.
	 *
	 * @param int $event_id Event id.
	 * @return object|null
	 */
	public function get_event_with_context( $event_id ) {
		global $wpdb;

		$event_id = absint( $event_id );
		if ( $event_id <= 0 ) {
			return null;
		}

		$context = $this->query_context();
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT e.*, {$context['select']}
				FROM {$context['table']} e
				{$context['joins']}
				WHERE e.id = %d
				LIMIT 1",
				$event_id
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Read one detail record without masking a database outage as "not found".
	 *
	 * @param int $event_id Event id.
	 * @return object|null|\WP_Error
	 */
	public function get_event_with_context_checked( $event_id ) {
		global $wpdb;

		$wpdb->last_error = '';
		$event = $this->get_event_with_context( $event_id );
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error( 'subin_mail_event_detail_failed', __( 'The Email Log event detail could not be read.', 'submission-inbox' ) );
		}

		return $event;
	}

	/**
	 * Return immutable attempts related by correlation, queue job, or lineage.
	 *
	 * @param int|object $event Event id or a previously loaded event row.
	 * @param int        $limit Maximum related attempts.
	 * @return array<int,object>
	 */
	public function get_related_attempts( $event, $limit = 20 ) {
		global $wpdb;

		if ( ! is_object( $event ) ) {
			$event = $this->get_event_with_context( $event );
		}
		$event_id = is_object( $event ) ? absint( $event->id ?? 0 ) : 0;
		if ( $event_id <= 0 ) {
			return array();
		}

		$context = $this->query_context();
		$where   = array( 'e.id = %d' );
		$params  = array( $event_id );
		$ids     = array( $event_id );
		foreach ( array( 'parent_event_id', 'retry_of_event_id' ) as $key ) {
			$related_id = absint( $event->{$key} ?? 0 );
			if ( $related_id > 0 ) {
				$ids[] = $related_id;
			}
		}
		$ids = array_values( array_unique( $ids ) );

		$job_id = absint( $event->job_id ?? 0 );
		if ( $job_id > 0 ) {
			$where[]  = 'e.job_id = %d';
			$params[] = $job_id;
		} else {
			$correlation_id = $this->sanitize_correlation_id( $event->correlation_id ?? '' );
			$source         = sanitize_key( (string) ( $event->source ?? '' ) );
			if ( '' !== $correlation_id && '' !== $source ) {
				$identity = array( 'e.correlation_id = %s', 'e.source = %s' );
				$identity_params = array( $correlation_id, $source );
				$message_hash = strtolower( sanitize_text_field( (string) ( $event->message_hash ?? '' ) ) );
				if ( preg_match( '/^[a-f0-9]{64}$/', $message_hash ) ) {
					$identity[]        = 'e.message_hash = %s';
					$identity_params[] = $message_hash;
				} else {
					$identity[]        = 'e.to_email = %s';
					$identity[]        = 'e.subject = %s';
					$identity_params[] = sanitize_textarea_field( (string) ( $event->to_email ?? '' ) );
					$identity_params[] = sanitize_text_field( (string) ( $event->subject ?? '' ) );
				}
				$where[] = '(' . implode( ' AND ', $identity ) . ')';
				$params  = array_merge( $params, $identity_params );
			}
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		if ( $this->has_event_column( 'parent_event_id' ) ) {
			$where[] = "e.parent_event_id IN ({$placeholders})";
			$params  = array_merge( $params, $ids );
		}
		if ( $this->has_event_column( 'retry_of_event_id' ) ) {
			$where[] = "e.retry_of_event_id IN ({$placeholders})";
			$params  = array_merge( $params, $ids );
		}

		$limit    = min( 100, max( 1, absint( $limit ) ) );
		$where_sql = '(' . implode( ' OR ', $where ) . ')';
		$sql       = "SELECT e.*, {$context['select']}
			FROM {$context['table']} e
			{$context['joins']}
			WHERE {$where_sql}
			ORDER BY e.created_at DESC, e.id DESC
			LIMIT %d";
		$params[] = $limit;

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

	/**
	 * Read related attempts without masking a database failure as no attempts.
	 *
	 * @param int|object $event Event id or event row.
	 * @param int        $limit Maximum related attempts.
	 * @return array<int,object>|\WP_Error
	 */
	public function get_related_attempts_checked( $event, $limit = 20 ) {
		global $wpdb;

		$wpdb->last_error = '';
		$attempts = $this->get_related_attempts( $event, $limit );
		if ( '' !== (string) $wpdb->last_error || ! is_array( $attempts ) ) {
			return new \WP_Error( 'subin_mail_event_attempts_failed', __( 'Related Email Log attempts could not be read.', 'submission-inbox' ) );
		}

		return $attempts;
	}

	/**
	 * Get a page without converting a failed SELECT into an empty export batch.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>|\WP_Error
	 */
	public function get_paginated_events_checked( array $args ) {
		global $wpdb;

		$wpdb->last_error = '';
		$rows = $this->get_paginated_events( $args );
		if ( '' !== (string) $wpdb->last_error || ! is_array( $rows ) ) {
			return new \WP_Error( 'subin_mail_event_query_failed', __( 'Email Log events could not be read.', 'submission-inbox' ) );
		}

		return $rows;
	}

	/**
	 * Get distinct sources.
	 *
	 * @return array<int,string>
	 */
	public function get_distinct_sources() {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return array_map(
			'sanitize_text_field',
			$wpdb->get_col( "SELECT DISTINCT source FROM {$table} WHERE source IS NOT NULL AND source != '' ORDER BY source ASC" ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get email events for CSV export.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @param int                 $limit Export limit.
	 * @return array<int,object>
	 */
	public function get_events_for_export( array $filters, $limit = 5000 ) {
		global $wpdb;

		$context    = $this->query_context();
		$where      = $this->build_where( $filters );
		$limit      = max( 1, absint( $limit ) );
		$direction  = $this->sort_direction( $filters['sort'] ?? 'newest' );
		$sql        = "SELECT e.*, {$context['select']}
			FROM {$context['table']} e
			{$context['joins']}
			{$where['sql']}
			ORDER BY e.created_at {$direction}, e.id {$direction}
			LIMIT %d";
		$params  = array_merge( $where['params'], array( $limit ) );

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

	/**
	 * Count events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return int
	 */
	public function count_by_lead_id( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return absint(
			$wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE lead_id = %d", absint( $lead_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			)
		);
	}

	/**
	 * Get related events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_related_events_for_lead( $lead_id, $limit = 0 ) {
		return $this->get_by_lead_id( $lead_id, $limit );
	}

	/**
	 * Attach recent unlinked events for a source to a lead.
	 *
	 * @param int               $lead_id Lead id.
	 * @param string            $source Event source.
	 * @param string            $created_after Earliest event datetime.
	 * @param array<int,string> $recipients Optional recipient emails.
	 * @param string            $correlation_id Exact request-local correlation id.
	 * @return int|false
	 */
	public function attach_recent_unlinked_events( $lead_id, $source, $created_after = '', array $recipients = array(), $correlation_id = '' ) {
		global $wpdb;

		$lead_id        = absint( $lead_id );
		$source         = sanitize_key( $source );
		$correlation_id = $this->sanitize_correlation_id( $correlation_id );
		if ( $lead_id <= 0 || '' === $source ) {
			return false;
		}

		$table  = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';
		$lead_context = $wpdb->get_row(
			$wpdb->prepare( "SELECT provider, form_id, form_name FROM {$lead_table} WHERE id = %d LIMIT 1", $lead_id )
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$event_provider = sanitize_key( (string) ( $lead_context->provider ?? $source ) );
		$event_form_id  = substr( sanitize_text_field( (string) ( $lead_context->form_id ?? '' ) ), 0, 190 );
		$event_form_name = substr( sanitize_text_field( (string) ( $lead_context->form_name ?? '' ) ), 0, 255 );
		$where  = array( '(lead_id IS NULL OR lead_id = 0)', 'source = %s' );
		$params = array( $source );

		if ( '' !== $correlation_id ) {
			$where[]  = 'correlation_id = %s';
			$params[] = $correlation_id;
		} else {
			if ( '' !== $created_after ) {
				$where[]  = 'created_at >= %s';
				$params[] = sanitize_text_field( $created_after );
			}

			$recipients = array_values( array_filter( array_map( 'sanitize_email', $recipients ) ) );
			if ( ! empty( $recipients ) ) {
				$recipient_where = array();
				foreach ( $recipients as $recipient ) {
					$recipient_where[] = 'to_email LIKE %s';
					$params[]          = '%' . $wpdb->esc_like( $recipient ) . '%';
				}
				$where[] = '(' . implode( ' OR ', $recipient_where ) . ')';
			}
		}

		$params    = array_merge( array( current_time( 'mysql' ), $lead_id, $event_provider, $event_form_id, $event_form_name ), $params );
		$where_sql = implode( ' AND ', $where );
		$query     = "UPDATE {$table}
			SET updated_at = %s,
				lead_id = %d,
					provider = COALESCE(NULLIF(provider, ''), NULLIF(%s, '')),
					form_id = COALESCE(NULLIF(form_id, ''), NULLIF(%s, '')),
					form_name = COALESCE(NULLIF(form_name, ''), NULLIF(%s, ''))
			WHERE {$where_sql}";

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

	/**
	 * Detach mail events from a lead while preserving email log history.
	 *
	 * @param int $lead_id Lead id.
	 * @return bool
	 */
	public function detach_lead( $lead_id ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';

		return false !== $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} events
				LEFT JOIN {$lead_table} leads ON leads.id = events.lead_id
					SET events.provider = COALESCE(NULLIF(events.provider, ''), NULLIF(leads.provider, ''), NULLIF(events.source, '')),
						events.form_id = COALESCE(NULLIF(events.form_id, ''), NULLIF(leads.form_id, '')),
						events.form_name = COALESCE(NULLIF(events.form_name, ''), NULLIF(leads.form_name, '')),
						events.lead_id = NULL
					WHERE events.lead_id = %d",
					absint( $lead_id )
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get bounded provider/form choices observed in immutable mail events.
	 *
	 * Event metadata wins over a linked lead. This keeps filters useful after a
	 * lead is detached or deleted, while legacy events still inherit lead context.
	 *
	 * @param int $limit Maximum contexts.
	 * @return array<int,object>
	 */
	public function get_distinct_form_contexts( $limit = 200 ) {
		global $wpdb;

		$context       = $this->query_context();
		$provider_expr = $this->effective_provider_expression();
		$form_expr     = $this->effective_form_expression();
		$limit         = min( 500, max( 1, absint( $limit ) ) );
		$sql           = "SELECT
				{$provider_expr} AS provider,
				{$form_expr} AS form_id,
					MAX(COALESCE(NULLIF(e.form_name, ''), '')) AS form_name,
				COUNT(*) AS event_count
			FROM {$context['table']} e
			{$context['joins']}
			WHERE {$form_expr} != ''
			GROUP BY {$provider_expr}, {$form_expr}
			ORDER BY form_name ASC, provider ASC, form_id ASC
			LIMIT %d";

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $limit ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Get dynamically observed email types for filter construction.
	 *
	 * @param int $limit Maximum values.
	 * @return array<int,string>
	 */
	public function get_distinct_email_types( $limit = 50 ) {
		global $wpdb;

		$context = $this->query_context();
		$expr    = $this->email_type_expression();
		$limit   = min( 100, max( 1, absint( $limit ) ) );
		$sql     = "SELECT DISTINCT {$expr} AS email_type
			FROM {$context['table']} e
			{$context['joins']}
			WHERE {$expr} != ''
			ORDER BY email_type ASC
			LIMIT %d";
		$values  = $wpdb->get_col( $wpdb->prepare( $sql, $limit ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return array_values( array_filter( array_map( 'sanitize_key', is_array( $values ) ? $values : array() ) ) );
	}

	/**
	 * Build the complete nullable metadata row used by inserts.
	 *
	 * Keeping every column in the insert makes pending and no-email events share
	 * one stable schema while omitted metadata remains SQL NULL for legacy calls.
	 *
	 * @param array<string,mixed> $data Event data.
	 * @return array<string,mixed>
	 */
	private function metadata_for_storage( array $data ) {
		$metadata = $this->sanitize_event_metadata( $data );
		$storage  = array(
			'job_id'        => $metadata['job_id'] ?? null,
			'rule_id'       => $metadata['rule_id'] ?? null,
			'template_id'   => $metadata['template_id'] ?? null,
			'rule_version'  => $metadata['rule_version'] ?? null,
			'provider'      => $metadata['provider'] ?? null,
			'form_id'       => $metadata['form_id'] ?? null,
			'form_name'     => $metadata['form_name'] ?? null,
			'rule_name'     => $metadata['rule_name'] ?? null,
			'template_name' => $metadata['template_name'] ?? null,
			'delivery_mode' => $metadata['delivery_mode'] ?? null,
			'scheduled_at'  => $metadata['scheduled_at'] ?? null,
			'reason_code'   => $metadata['reason_code'] ?? null,
		);

		foreach ( array( 'event_kind', 'parent_event_id', 'retry_of_event_id' ) as $key ) {
			if ( $this->has_event_column( $key ) ) {
				$storage[ $key ] = $metadata[ $key ] ?? ( 'event_kind' === $key ? self::EVENT_KIND_GENERIC : null );
			}
		}

		return $storage;
	}

	/**
	 * Sanitize delivery metadata plus explicit event classification and lineage.
	 *
	 * Provider delivery evidence is intentionally excluded. It is authoritative
	 * external evidence and must only enter through its dedicated service.
	 *
	 * @param array<string,mixed> $data Event metadata.
	 * @param int                 $event_id Current event id when updating.
	 * @return array<string,mixed>
	 */
	private function sanitize_event_metadata( array $data, $event_id = 0 ) {
		$metadata = FormSubmissionContext::sanitize_delivery_metadata( $data );
		$event_id = absint( $event_id );

		if ( $this->has_event_column( 'event_kind' ) ) {
			$event_kind = sanitize_key( $data['event_kind'] ?? ( $metadata['event_kind'] ?? '' ) );
			if ( in_array( $event_kind, $this->get_event_kinds(), true ) ) {
				$metadata['event_kind'] = $event_kind;
			} elseif ( 0 === $event_id ) {
				$metadata['event_kind'] = self::EVENT_KIND_GENERIC;
			}
		}

		foreach ( array( 'parent_event_id', 'retry_of_event_id' ) as $key ) {
			if ( ! $this->has_event_column( $key ) || ! array_key_exists( $key, $data ) ) {
				unset( $metadata[ $key ] );
				continue;
			}

			$related_id = absint( $data[ $key ] );
			if ( $related_id > 0 && $related_id !== $event_id ) {
				$metadata[ $key ] = $related_id;
			} else {
				unset( $metadata[ $key ] );
			}
		}

		$allowed = array(
			'job_id',
			'rule_id',
			'template_id',
			'rule_version',
			'provider',
			'form_id',
			'form_name',
			'rule_name',
			'template_name',
			'delivery_mode',
			'scheduled_at',
			'reason_code',
			'event_kind',
			'parent_event_id',
			'retry_of_event_id',
		);

		return array_intersect_key( $metadata, array_fill_keys( $allowed, true ) );
	}

	/**
	 * Sanitize a request-local mail correlation id for storage and lookup.
	 *
	 * @param mixed $correlation_id Correlation id.
	 * @return string
	 */
	private function sanitize_correlation_id( $correlation_id ) {
		$correlation_id = preg_replace( '/[^a-zA-Z0-9_-]/', '', (string) $correlation_id );

		return substr( (string) $correlation_id, 0, 64 );
	}

	/**
	 * Update event status.
	 *
	 * @param int    $event_id Event id.
	 * @param string $status Status.
	 * @param string $error Error message.
	 * @return bool
	 */
	private function update_status( $event_id, $status, $error ) {
		global $wpdb;

		$event_id = absint( $event_id );
		$status   = sanitize_key( (string) $status );
		if ( $event_id <= 0 || ! in_array( $status, array( self::STATUS_SENT, self::STATUS_FAILED ), true ) ) {
			return false;
		}

		$table   = $wpdb->prefix . 'subin_mail_events';
		$now     = current_time( 'mysql' );
		$set     = 'status = %s, error_message = NULLIF(%s, \'\'), updated_at = %s';
		$params  = array(
			$status,
			'' !== $error ? sanitize_textarea_field( $error ) : '',
			$now,
		);
		if ( $this->has_event_column( 'completed_at' ) ) {
			$set      .= ', completed_at = %s';
			$params[] = $now;
		}
		$params = array_merge(
			$params,
			array( $event_id, self::STATUS_PENDING, self::STATUS_UNKNOWN )
		);
		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET {$set} WHERE id = %d AND status IN (%s, %s)",
				$params
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( false === $updated ) {
			return false;
		}
		if ( $updated > 0 ) {
			do_action( 'subin_mail_event_status_updated', $event_id, $status );
			return true;
		}

		$completed_at_select = $this->has_event_column( 'completed_at' ) ? ', completed_at' : '';
		$current = $wpdb->get_row(
			$wpdb->prepare( "SELECT status{$completed_at_select} FROM {$table} WHERE id = %d LIMIT 1", $event_id )
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$current_status = sanitize_key( (string) ( $current->status ?? '' ) );
		if ( $status === $current_status && $this->has_event_column( 'completed_at' ) && empty( $current->completed_at ) ) {
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table} SET completed_at = %s WHERE id = %d AND status = %s AND completed_at IS NULL",
					$now,
					$event_id,
					$status
				)
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		return $status === $current_status;
	}

	/**
	 * Build WHERE clause.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function build_where( array $args ) {
		global $wpdb;

		$where         = array();
		$params        = array();
		$provider_expr = $this->effective_provider_expression();
		$form_expr     = $this->effective_form_expression();
		$snapshot_max  = absint( $args['snapshot_max_id'] ?? 0 );
		$cursor_id     = absint( $args['cursor_id'] ?? 0 );
		$keyset        = $this->keyset_direction( $args['keyset_order'] ?? '' );
		if ( $snapshot_max > 0 ) {
			$where[]  = 'e.id <= %d';
			$params[] = $snapshot_max;
		}
		if ( $cursor_id > 0 && '' !== $keyset ) {
			$where[]  = 'ASC' === $keyset ? 'e.id > %d' : 'e.id < %d';
			$params[] = $cursor_id;
		}

		$provider    = sanitize_key( $args['provider'] ?? '' );
		$raw_form_id = $args['form_id'] ?? '';
		$form_id     = is_scalar( $raw_form_id ) ? trim( sanitize_text_field( (string) $raw_form_id ) ) : '';
		$form_id  = substr( $form_id, 0, 190 );
		if ( '' !== $provider ) {
			$where[]  = "{$provider_expr} = %s";
			$params[] = $provider;
		}
		if ( '' !== $form_id && '0' !== $form_id ) {
			if ( '' === $provider ) {
				$where[]  = "{$provider_expr} = %s";
				$params[] = 'submission_inbox_form';
			}
			$where[]  = "{$form_expr} = %s";
			$params[] = $form_id;
		}

		$outcome = sanitize_key( $args['outcome'] ?? '' );
		if ( '' !== $outcome ) {
			$effective_outcome = $this->effective_outcome_expression();
			switch ( $outcome ) {
				case 'accepted':
				case 'sent':
					$where[] = "{$effective_outcome} = 'accepted'";
					break;
				case 'handled':
					$where[] = "{$effective_outcome} = 'handled'";
					break;
				case 'failed':
					$where[] = "{$effective_outcome} = 'failed'";
					break;
				case 'not_generated':
				case 'no_email_generated':
					$where[] = "{$effective_outcome} = 'not_generated'";
					break;
				case 'awaiting':
					$where[] = "{$effective_outcome} IN ('processing', 'stalled', 'awaiting')";
					break;
				case 'processing':
					$where[] = "{$effective_outcome} = 'processing'";
					break;
				case 'stalled':
					$where[] = "{$effective_outcome} = 'stalled'";
					break;
				case 'needs_attention':
					$where[] = "(
						{$effective_outcome} IN ('failed', 'awaiting', 'stalled', 'bounced', 'deferred', 'dropped', 'complained')
						OR (
							{$effective_outcome} = 'not_generated'
							AND COALESCE(e.reason_code, '') NOT IN ('conditions_not_met', 'rule_conditions_not_met', 'no_matching_rule', 'rule_not_matched', 'auto_reply_disabled', 'rule_disabled', 'notification_disabled', 'not_configured', 'intentional_skip')
						)
					)";
					break;
				case 'queued':
				case 'delivered':
				case 'bounced':
				case 'deferred':
				case 'dropped':
				case 'complained':
				case 'opened':
					$where[]  = "{$effective_outcome} = %s";
					$params[] = $outcome;
					break;
			}
		} else {
			$status = sanitize_key( $args['status'] ?? '' );
			if ( in_array( $status, $this->get_statuses(), true ) ) {
				$where[]  = 'e.status = %s';
				$params[] = $status;
			}
		}

		$source = sanitize_key( $args['source'] ?? '' );
		if ( '' !== $source ) {
			$where[]  = "(e.source = %s OR {$provider_expr} = %s)";
			$params[] = $source;
			$params[] = $source;
		}

		$email_type = sanitize_key( $args['email_type'] ?? '' );
		if ( '' !== $email_type ) {
			$email_type_expr = $this->email_type_expression();
			if ( 'auto_reply' === $email_type || 'auto_replies' === $email_type ) {
				$where[] = "{$email_type_expr} IN ('visitor_auto_reply', 'visitor_confirmation', 'auto_reply_test')";
			} else {
				$where[]  = "{$email_type_expr} = %s";
				$params[] = $email_type;
			}
		}

		$event_kind = sanitize_key( $args['event_kind'] ?? '' );
		if ( '' !== $event_kind && in_array( $event_kind, $this->get_event_kinds(), true ) ) {
			$where[]  = $this->event_kind_expression() . ' = %s';
			$params[] = $event_kind;
		}

		$rule_id = absint( $args['rule_id'] ?? 0 );
		if ( $rule_id > 0 ) {
			$where[]  = 'e.rule_id = %d';
			$params[] = $rule_id;
		}

		$date_from = $this->normalize_date( $args['date_from'] ?? '' );
		$date_to   = $this->normalize_date( $args['date_to'] ?? '' );
		if ( '' === $date_from && '' === $date_to ) {
			$range     = $this->date_range_bounds( $args['date_range'] ?? '' );
			$date_from = $range['from'];
			$date_to   = $range['to'];
		}
		if ( '' !== $date_from ) {
			$where[]  = 'e.created_at >= %s';
			$params[] = $date_from . ' 00:00:00';
		}
		if ( '' !== $date_to ) {
			$where[]  = 'e.created_at <= %s';
			$params[] = $date_to . ' 23:59:59';
		}

		$raw_search = $args['search'] ?? '';
		$search     = is_scalar( $raw_search ) ? trim( sanitize_text_field( (string) $raw_search ) ) : '';
		if ( '' !== $search ) {
			if ( preg_match( '/^(?:event(?:_id)?\s*[:#]\s*|#)(\d+)$/i', $search, $match ) ) {
				$where[]  = 'e.id = %d';
				$params[] = absint( $match[1] );
			} elseif ( preg_match( '/^lead(?:_id)?\s*[:#]\s*(\d+)$/i', $search, $match ) ) {
				$where[]  = 'e.lead_id = %d';
				$params[] = absint( $match[1] );
			} elseif ( preg_match( '/^job(?:_id)?\s*[:#]\s*(\d+)$/i', $search, $match ) ) {
				$where[]  = 'e.job_id = %d';
				$params[] = absint( $match[1] );
			} elseif ( preg_match( '/^rule(?:_id)?\s*[:#]\s*(\d+)$/i', $search, $match ) ) {
				$where[]  = 'e.rule_id = %d';
				$params[] = absint( $match[1] );
			} elseif ( ctype_digit( $search ) ) {
				$where[]  = '(e.id = %d OR e.lead_id = %d)';
				$params[] = absint( $search );
				$params[] = absint( $search );
			} else {
				$like     = '%' . $wpdb->esc_like( $search ) . '%';
				$where[]  = '(e.to_email LIKE %s OR e.from_email LIKE %s OR e.subject LIKE %s OR e.error_message LIKE %s OR e.correlation_id LIKE %s)';
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
			}
		}

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

	/**
	 * Resolve an explicit primary-key order for stable bounded scans.
	 *
	 * @param mixed $value Requested keyset order.
	 * @return string ASC, DESC, or an empty string when normal list ordering applies.
	 */
	private function keyset_direction( $value ) {
		$value = sanitize_key( (string) $value );
		if ( 'id_asc' === $value ) {
			return 'ASC';
		}
		if ( 'id_desc' === $value ) {
			return 'DESC';
		}

		return '';
	}

	/**
	 * Shared joins and normalized context fields for list, detail, and export.
	 *
	 * @return array{table:string,joins:string,select:string}
	 */
	private function query_context() {
		global $wpdb;

		$table          = $wpdb->prefix . 'subin_mail_events';
		$lead_table     = $wpdb->prefix . 'subin_leads';
		$job_table      = $wpdb->prefix . 'subin_notification_jobs';
		$rule_table     = $wpdb->prefix . 'subin_auto_reply_rules';
		$template_table = $wpdb->prefix . 'subin_auto_reply_templates';
		$optional       = array();
		foreach ( array( 'provider_status', 'provider_message_id', 'provider_evidence_source', 'provider_evidence_at' ) as $column ) {
			$optional[] = $this->has_event_column( $column ) ? "e.{$column} AS {$column}" : "NULL AS {$column}";
		}

		$select = array_merge(
			array(
				'leads.id AS linked_lead_id',
				'leads.provider AS lead_provider',
				'leads.form_id AS lead_form_id',
				'leads.form_name AS lead_form_name',
				'jobs.email_type AS job_email_type',
				'jobs.status AS job_status',
				'jobs.attempts AS job_attempts',
				'jobs.available_at AS job_available_at',
				'jobs.sent_at AS job_sent_at',
					'rules.name AS live_rule_name',
					'rules.form_name AS rule_form_name',
					'rules.status AS rule_status',
					'templates.name AS live_template_name',
				$this->effective_provider_expression() . ' AS effective_provider',
				$this->effective_form_expression() . ' AS effective_form_id',
				$this->email_type_expression() . ' AS email_type',
				$this->event_kind_expression() . ' AS effective_event_kind',
			),
			$optional
		);

		return array(
			'table'  => $table,
			'joins'  => "LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
				LEFT JOIN {$job_table} jobs ON jobs.id = e.job_id
				LEFT JOIN {$rule_table} rules ON rules.id = e.rule_id
				LEFT JOIN {$template_table} templates ON templates.id = e.template_id",
			'select' => implode( ",\n\t\t\t", $select ),
		);
	}

	/** @return string */
	private function effective_provider_expression() {
		return "COALESCE(NULLIF(e.provider, ''), NULLIF(leads.provider, ''), '')";
	}

	/** @return string */
	private function effective_form_expression() {
		return "COALESCE(NULLIF(e.form_id, ''), NULLIF(leads.form_id, ''), '')";
	}

	/** @return string */
	private function event_kind_expression() {
		$stored = $this->has_event_column( 'event_kind' ) ? "NULLIF(e.event_kind, '')" : 'NULL';

		return "COALESCE({$stored}, CASE
			WHEN jobs.email_type = 'visitor_auto_reply' OR e.source = 'submission_inbox_auto_reply' THEN 'visitor_confirmation'
			WHEN jobs.email_type = 'admin_notification' OR e.source = 'submission_inbox_form' THEN 'admin_notification'
			ELSE 'generic'
		END)";
	}

	/** @return string */
	private function email_type_expression() {
		return "COALESCE(NULLIF(jobs.email_type, ''), CASE " . $this->event_kind_expression() . "
			WHEN 'visitor_confirmation' THEN 'visitor_auto_reply'
			WHEN 'auto_reply_test' THEN 'auto_reply_test'
			WHEN 'health_verification' THEN 'health_verification'
			WHEN 'admin_notification' THEN 'admin_notification'
			ELSE 'wordpress_email'
		END)";
	}

	/**
	 * Resolve the exact outcome shown by MailEventPresenter in SQL.
	 *
	 * Provider evidence wins only when the complete authenticated tuple exists;
	 * otherwise the immutable WordPress handoff state remains authoritative.
	 *
	 * @return string Safe internal SQL CASE expression.
	 */
	private function effective_outcome_expression() {
		$cutoff = str_replace( "'", '', $this->stalled_cutoff() );
		$provider_columns = array( 'provider_status', 'provider_message_id', 'provider_evidence_source', 'provider_evidence_at' );
		$has_provider_evidence = true;
		foreach ( $provider_columns as $column ) {
			if ( ! $this->has_event_column( $column ) ) {
				$has_provider_evidence = false;
				break;
			}
		}

		$provider_when = $has_provider_evidence
			? "WHEN e.provider_status IN ('accepted', 'queued', 'delivered', 'bounced', 'deferred', 'dropped', 'complained', 'opened')
				AND NULLIF(e.provider_message_id, '') IS NOT NULL
				AND NULLIF(e.provider_evidence_source, '') IS NOT NULL
				AND e.provider_evidence_at IS NOT NULL
				THEN e.provider_status"
			: '';

		return "(CASE
			{$provider_when}
			WHEN e.status = 'sent' AND e.reason_code = 'pre_wp_mail_short_circuit' THEN 'handled'
			WHEN e.status = 'sent' THEN 'accepted'
			WHEN e.status = 'failed' THEN 'failed'
			WHEN e.status = 'no_email_generated' THEN 'not_generated'
			WHEN e.status = 'pending' AND e.updated_at >= '{$cutoff}' THEN 'processing'
			WHEN e.status = 'pending' THEN 'stalled'
			ELSE 'awaiting'
		END)";
	}

	/**
	 * Normalize a date without allowing PHP's overflow correction.
	 *
	 * @param mixed $value Date input.
	 * @return string
	 */
	private function normalize_date( $value ) {
		$value = is_scalar( $value ) ? trim( sanitize_text_field( (string) $value ) ) : '';
		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
			return '';
		}

		$date   = \DateTimeImmutable::createFromFormat( '!Y-m-d', $value, wp_timezone() );
		$errors = \DateTimeImmutable::getLastErrors();
		if ( ! $date || ( is_array( $errors ) && ( $errors['warning_count'] > 0 || $errors['error_count'] > 0 ) ) ) {
			return '';
		}

		return $date->format( 'Y-m-d' ) === $value ? $value : '';
	}

	/**
	 * Resolve relative periods in the WordPress site timezone.
	 *
	 * @param mixed $range Range key.
	 * @return array{from:string,to:string}
	 */
	private function date_range_bounds( $range ) {
		$range = is_scalar( $range ) ? sanitize_key( (string) $range ) : '';
		$today = $this->site_now()->setTime( 0, 0, 0 );
		if ( 'today' === $range ) {
			$date = $today->format( 'Y-m-d' );
			return array( 'from' => $date, 'to' => $date );
		}
		if ( 'yesterday' === $range ) {
			$date = $today->modify( '-1 day' )->format( 'Y-m-d' );
			return array( 'from' => $date, 'to' => $date );
		}

		$days = absint( $range );
		if ( ! in_array( $days, array( 7, 30, 90 ), true ) ) {
			return array( 'from' => '', 'to' => '' );
		}

		return array(
			'from' => $today->modify( '-' . ( $days - 1 ) . ' days' )->format( 'Y-m-d' ),
			'to'   => $today->format( 'Y-m-d' ),
		);
	}

	/** @return \DateTimeImmutable */
	private function site_now() {
		$now = current_datetime();

		return $now instanceof \DateTimeImmutable ? $now : \DateTimeImmutable::createFromMutable( $now );
	}

	/** @return string */
	private function stalled_cutoff() {
		return $this->site_now()->modify( '-' . self::STALLED_AFTER_SECONDS . ' seconds' )->format( 'Y-m-d H:i:s' );
	}

	/**
	 * Resolve a user-facing sort key to a non-user-controlled SQL direction.
	 *
	 * @param mixed $sort Sort key.
	 * @return string
	 */
	private function sort_direction( $sort ) {
		return 'oldest' === sanitize_key( is_scalar( $sort ) ? (string) $sort : '' ) ? 'ASC' : 'DESC';
	}

	/**
	 * Check a versioned event column without assuming an upgrade already ran.
	 *
	 * @param string $column Column name from the fixed internal allowlist.
	 * @return bool
	 */
	private function has_event_column( $column ) {
		global $wpdb;

		$column = sanitize_key( (string) $column );
		if ( null === $this->event_columns ) {
			$core = array(
				'id', 'lead_id', 'correlation_id', 'source', 'job_id', 'rule_id',
				'template_id', 'rule_version', 'provider', 'form_id', 'delivery_mode',
				'scheduled_at', 'reason_code', 'to_email', 'from_email', 'subject',
				'message_hash', 'status', 'error_message', 'created_at', 'updated_at',
			);
			$table   = $wpdb->prefix . 'subin_mail_events';
			$columns = method_exists( $wpdb, 'get_col' )
				? $wpdb->get_col( "SHOW COLUMNS FROM {$table}" ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				: array();
			$columns = is_array( $columns ) && ! empty( $columns ) ? array_map( 'sanitize_key', $columns ) : $core;
			$this->event_columns = array_fill_keys( $columns, true );
		}

		return isset( $this->event_columns[ $column ] );
	}

	/**
	 * Build an exact wpdb format vector for a dynamic schema-compatible row.
	 *
	 * @param array<string,mixed> $row Database row.
	 * @return array<int,string>
	 */
	private function formats_for_row( array $row ) {
		$integers = array_fill_keys(
			array( 'lead_id', 'job_id', 'rule_id', 'template_id', 'rule_version', 'parent_event_id', 'retry_of_event_id' ),
			true
		);
		$formats  = array();
		foreach ( array_keys( $row ) as $key ) {
			$formats[] = isset( $integers[ $key ] ) ? '%d' : '%s';
		}

		return $formats;
	}

	/**
	 * Get allowed email event statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_statuses() {
		return array( self::STATUS_SENT, self::STATUS_FAILED, self::STATUS_PENDING, self::STATUS_NO_EMAIL_GENERATED, self::STATUS_UNKNOWN );
	}

	/** @return array<int,string> */
	private function get_event_kinds() {
		return array(
			self::EVENT_KIND_GENERIC,
			self::EVENT_KIND_ADMIN_NOTIFICATION,
			self::EVENT_KIND_VISITOR_CONFIRMATION,
			self::EVENT_KIND_AUTO_REPLY_TEST,
			self::EVENT_KIND_HEALTH_VERIFICATION,
		);
	}
}
