<?php
/**
 * Auto-reply policy repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\AutoReplies;

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

if ( ! class_exists( AutoReplyPreheader::class ) ) {
	require_once __DIR__ . '/AutoReplyPreheader.php';
}

if ( ! class_exists( AutoReplyDeliverySnapshot::class ) ) {
	require_once __DIR__ . '/AutoReplyDeliverySnapshot.php';
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom policy tables directly; WordPress has no native API for these records.
// 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 repository.

/**
 * Reads and writes versioned auto-reply policies.
 */
class AutoReplyRuleRepository {
	const PROVIDER_SUBMISSION_INBOX_FORM = 'submission_inbox_form';

	const STATUS_DRAFT     = 'draft';
	const STATUS_PUBLISHED = 'published';

	const MATCH_ALL = 'all';
	const MATCH_ANY = 'any';

	const DELIVERY_IMMEDIATE = 'immediate';
	const DELIVERY_DELAY     = 'delay';

	const DEFAULT_PRIORITY  = 100;
	const MAX_PRIORITY      = 10000;
	const MAX_DELAY_SECONDS = 604800;
	const READINESS_PREVIEW_CHARACTERS = 4096;
	const MAX_ADMIN_LIST_ROWS = 500;

	/**
	 * Condition engine.
	 *
	 * @var AutoReplyConditionEngine
	 */
	private $conditions;

	/**
	 * Constructor.
	 *
	 * @param AutoReplyConditionEngine|null $conditions Condition engine.
	 */
	public function __construct( ?AutoReplyConditionEngine $conditions = null ) {
		$this->conditions = $conditions ?: new AutoReplyConditionEngine();
	}

	/**
	 * Create a policy.
	 *
	 * @param array<string,mixed> $data Policy data.
	 * @return int
	 */
	public function create( array $data ) {
		global $wpdb;

		$data = $this->sanitize_data( $data );
		if ( '' === $data['name'] || '' === $data['provider'] || '' === $data['form_id'] ) {
			return 0;
		}

		$now                  = current_time( 'mysql' );
		$data['rule_version'] = 1;
		$data['created_at']   = $now;
		$data['updated_at']   = $now;

		$inserted = $wpdb->insert( $this->table_name(), $data, $this->storage_formats( true ) );

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

	/**
	 * Update a policy and advance its immutable revision number.
	 *
	 * @param int                 $rule_id Rule id.
	 * @param array<string,mixed> $data Policy data.
	 * @return bool
	 */
	public function update( $rule_id, array $data ) {
		global $wpdb;

		$rule_id = absint( $rule_id );
		$current = $this->find( $rule_id );
		if ( $rule_id <= 0 || ! $current ) {
			return false;
		}

		$merged = array_merge( $current, $data );
		if ( array_key_exists( 'conditions_json', $data ) && ! array_key_exists( 'conditions', $data ) ) {
			unset( $merged['conditions'], $merged['conditions_document'] );
		}
		$data = $this->sanitize_data( $merged );
		if ( '' === $data['name'] || '' === $data['provider'] || '' === $data['form_id'] ) {
			return false;
		}

		$data['rule_version'] = max( 1, absint( $current['rule_version'] ?? 1 ) + 1 );
		$data['updated_at']   = current_time( 'mysql' );

		$updated = $wpdb->update(
			$this->table_name(),
			$data,
			array( 'id' => $rule_id ),
			$this->storage_formats( false ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Atomically update a policy only when its revision still matches.
	 *
	 * @param int                 $rule_id Rule id.
	 * @param int                 $expected_version Client revision.
	 * @param array<string,mixed> $data Complete policy data.
	 * @return true|\WP_Error
	 */
	public function update_if_version( $rule_id, $expected_version, array $data ) {
		global $wpdb;

		$rule_id          = absint( $rule_id );
		$expected_version = absint( $expected_version );
		$current          = $this->find( $rule_id );
		if ( $rule_id <= 0 || ! $current ) {
			return new \WP_Error( 'rule_not_found', __( 'Auto-reply rule not found.', 'submission-inbox' ) );
		}
		if ( $expected_version <= 0 || $expected_version !== absint( $current['rule_version'] ?? 1 ) ) {
			return new \WP_Error( 'rule_conflict', __( 'The auto-reply rule changed before it could be saved.', 'submission-inbox' ) );
		}

		$merged = array_merge( $current, $data );
		if ( array_key_exists( 'conditions_json', $data ) && ! array_key_exists( 'conditions', $data ) ) {
			unset( $merged['conditions'], $merged['conditions_document'] );
		}
		$clean = $this->sanitize_data( $merged );
		if ( '' === $clean['name'] || '' === $clean['provider'] || '' === $clean['form_id'] ) {
			return new \WP_Error( 'rule_not_saved', __( 'Auto-reply rule data is incomplete.', 'submission-inbox' ) );
		}

		$clean['rule_version'] = $expected_version + 1;
		$clean['updated_at']   = current_time( 'mysql' );
		$updated = $wpdb->update(
			$this->table_name(),
			$clean,
			array(
				'id'           => $rule_id,
				'rule_version' => $expected_version,
			),
			$this->storage_formats( false ),
			array( '%d', '%d' )
		);
		if ( 1 === $updated ) {
			return true;
		}
		if ( false === $updated ) {
			return new \WP_Error( 'rule_not_saved', __( 'Auto-reply rule could not be saved.', 'submission-inbox' ) );
		}

		return new \WP_Error( 'rule_conflict', __( 'The auto-reply rule changed before it could be saved.', 'submission-inbox' ) );
	}

	/**
	 * Delete a policy.
	 *
	 * @param int $rule_id Rule id.
	 * @return bool
	 */
	public function delete( $rule_id ) {
		global $wpdb;

		$rule_id = absint( $rule_id );
		if ( $rule_id <= 0 ) {
			return false;
		}

		return false !== $wpdb->delete( $this->table_name(), array( 'id' => $rule_id ), array( '%d' ) );
	}

	/**
	 * Delete all policies for one provider/form pair.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @return bool
	 */
	public function delete_for_form( $provider, $form_id ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return false;
		}

		$deleted = $wpdb->delete(
			$this->table_name(),
			array(
				'provider' => $provider,
				'form_id'  => $form_id,
			),
			array( '%s', '%s' )
		);

		return false !== $deleted;
	}

	/**
	 * Duplicate a policy as a safe draft.
	 *
	 * @param int $rule_id Rule id.
	 * @return int
	 */
	public function duplicate( $rule_id ) {
		$rule = $this->find( $rule_id );
		if ( ! $rule ) {
			return 0;
		}

		$rule['name'] = sprintf(
			/* translators: %s: Original rule name. */
			__( '%s Copy', 'submission-inbox' ),
			$rule['name']
		);
		$rule['enabled'] = 0;
		$rule['status']  = self::STATUS_DRAFT;

		return $this->create( $rule );
	}

	/**
	 * Find a policy by id.
	 *
	 * @param int $rule_id Rule id.
	 * @return array<string,mixed>|null
	 */
	public function find( $rule_id ) {
		global $wpdb;

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

		$table = $this->table_name();
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $rule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return is_array( $row ) ? $this->normalize_row( $row ) : null;
	}

	/**
	 * Find the primary policy for a provider/form pair.
	 *
	 * Published enabled rules win, then other published rules, then drafts. Within
	 * each state, the product ordering is priority ascending and id ascending.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @return array<string,mixed>|null
	 */
	public function find_rule( $provider, $form_id ) {
		return $this->find_for_form( $provider, $form_id, false );
	}

	/**
	 * Find the first published enabled policy for a provider/form pair.
	 *
	 * This compatibility method does not evaluate conditions. Delivery code should
	 * call find_matching_rule() with the accepted submission values.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @return array<string,mixed>|null
	 */
	public function find_enabled_rule( $provider, $form_id ) {
		return $this->find_for_form( $provider, $form_id, true );
	}

	/**
	 * Select the first matching policy deterministically.
	 *
	 * @param string              $provider Provider key.
	 * @param string              $form_id Form id.
	 * @param array<int|string,mixed> $submitted_fields Accepted values or field rows.
	 * @return array<string,mixed>|null
	 */
	public function find_matching_rule( $provider, $form_id, array $submitted_fields ) {
		foreach ( $this->list_for_form( $provider, $form_id, true ) as $rule ) {
			$document = $rule['conditions_document'] ?? array(
				'version'    => AutoReplyConditionEngine::VERSION,
				'mode'       => $rule['condition_match'] ?? self::MATCH_ALL,
				'conditions' => $rule['conditions'] ?? array(),
			);
			if ( $this->conditions->matches( $document, $submitted_fields ) ) {
				return $rule;
			}
		}

		return null;
	}

	/**
	 * List policies for one form in deterministic delivery order.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @param bool   $enabled_only Restrict to effective policies.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_for_form( $provider, $form_id, $enabled_only = false ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return array();
		}

		$table = $this->table_name();
		$args  = array();
		if ( $enabled_only ) {
			$sql = "SELECT * FROM {$table} WHERE provider = %s AND form_id = %s";
		} else {
			$projection = $this->readiness_projection();
			$sql        = "SELECT {$projection['sql']} FROM {$table} WHERE provider = %s AND form_id = %s";
			$args       = $projection['params'];
		}
		$args[] = $provider;
		$args[] = $form_id;
		if ( $enabled_only ) {
			$sql .= ' AND enabled = 1 AND status = %s';
			$args[] = self::STATUS_PUBLISHED;
		}
		$sql .= ' ORDER BY priority ASC, id ASC';

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $this->normalize_rows( $rows );
	}

	/**
	 * List policies for admin screens.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_rules( array $args = array() ) {
		global $wpdb;

		$table  = $this->table_name();
		$where  = $this->build_where( $args );
		$limit  = min( self::MAX_ADMIN_LIST_ROWS, max( 1, absint( $args['per_page'] ?? 100 ) ) );
		$offset = max( 0, absint( $args['offset'] ?? 0 ) );
		$order  = ! empty( $args['delivery_order'] )
			? 'priority ASC, id ASC'
			: 'updated_at DESC, id DESC';
		$projection = $this->readiness_projection();
		$sql    = "SELECT {$projection['sql']} FROM {$table} {$where['sql']} ORDER BY {$order} LIMIT %d OFFSET %d";
		$params = array_merge( $projection['params'], $where['params'], array( $limit, $offset ) );
		$rows   = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $this->normalize_rows( $rows );
	}

	/**
	 * Load one inline rule body for an exact readiness check without selecting
	 * unrelated policy columns or accepting an unbounded legacy value.
	 *
	 * @param int $rule_id Rule id.
	 * @param int $max_body_bytes Maximum supported body bytes.
	 * @return array<string,mixed>|null
	 */
	public function get_readiness_content( $rule_id, $max_body_bytes ) {
		global $wpdb;

		$rule_id       = absint( $rule_id );
		$bounded_bytes = max( 1, absint( $max_body_bytes ) ) + 1;
		if ( $rule_id <= 0 ) {
			return null;
		}

		$table = $this->table_name();
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, LEFT(CAST(body AS BINARY), %d) AS body FROM {$table} WHERE id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$bounded_bytes,
				$rule_id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Count policies for an admin-list filter.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return int
	 */
	public function count_rules( array $args = array() ) {
		global $wpdb;

		$table = $this->table_name();
		$where = $this->build_where( $args );
		$sql   = "SELECT COUNT(*) FROM {$table} {$where['sql']}";
		if ( ! empty( $where['params'] ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- build_where() returns a fixed clause with placeholders and a separate value list.
			$sql = $wpdb->prepare( $sql, $where['params'] );
		}

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

	/**
	 * Whether a policy exists for a provider/form pair.
	 *
	 * @param string   $provider Provider key.
	 * @param string   $form_id Form id.
	 * @param int|null $exclude_id Rule id to exclude.
	 * @return bool
	 */
	public function exists_for_form( $provider, $form_id, $exclude_id = null ) {
		return $this->exists_for_form_with_state( $provider, $form_id, $exclude_id, false );
	}

	/**
	 * Whether another effective policy exists for a provider/form pair.
	 *
	 * Kept for backwards-compatible callers. Multiple enabled policies are now
	 * valid; new UI must not use this method to prohibit them.
	 *
	 * @param string   $provider Provider key.
	 * @param string   $form_id Form id.
	 * @param int|null $exclude_id Rule id to exclude.
	 * @return bool
	 */
	public function exists_enabled_for_form( $provider, $form_id, $exclude_id = null ) {
		return $this->exists_for_form_with_state( $provider, $form_id, $exclude_id, true );
	}

	/**
	 * Supported providers.
	 *
	 * @return array<int,string>
	 */
	public function allowed_providers() {
		return ( new \SUBIN\Integrations\ProviderRegistry() )->active_keys();
	}

	/**
	 * Sanitize a provider.
	 *
	 * @param string $provider Provider key.
	 * @return string
	 */
	public function sanitize_provider( $provider ) {
		$provider = sanitize_key( (string) $provider );

		return in_array( $provider, $this->allowed_providers(), true ) ? $provider : '';
	}

	/**
	 * Normalize policy input to the storage schema.
	 *
	 * @param array<string,mixed> $data Raw policy data.
	 * @return array<string,mixed>
	 */
	public function sanitize_data( array $data ) {
		$status = sanitize_key( (string) ( $data['status'] ?? self::STATUS_PUBLISHED ) );
		if ( ! in_array( $status, array( self::STATUS_DRAFT, self::STATUS_PUBLISHED ), true ) ) {
			$status = self::STATUS_DRAFT;
		}

		$condition_match = sanitize_key( (string) ( $data['condition_match'] ?? $data['conditions_mode'] ?? self::MATCH_ALL ) );
		if ( ! in_array( $condition_match, array( self::MATCH_ALL, self::MATCH_ANY ), true ) ) {
			$condition_match = self::MATCH_ALL;
		}

		$conditions = $data['conditions'] ?? null;
		if ( ! is_array( $conditions ) ) {
			if ( array_key_exists( 'conditions_json', $data ) ) {
				$decoded    = json_decode( (string) $data['conditions_json'], true );
				$conditions = is_array( $decoded )
					? $decoded
					: array(
						'version'    => AutoReplyConditionEngine::VERSION,
						'mode'       => $condition_match,
						'conditions' => array(),
						'valid'      => false,
					);
			} else {
				$conditions = array();
			}
		}
		$conditions = $this->conditions->sanitize_conditions( $conditions, $condition_match );
		$condition_match = $conditions['mode'];
		$stored_conditions = $conditions;
		if ( ! empty( $stored_conditions['valid'] ) ) {
			unset( $stored_conditions['valid'] );
		}
		$conditions_json = wp_json_encode( $stored_conditions );

		$delivery_mode = sanitize_key( (string) ( $data['delivery_mode'] ?? self::DELIVERY_IMMEDIATE ) );
		$delay_seconds = min( self::MAX_DELAY_SECONDS, absint( $data['delay_seconds'] ?? 0 ) );
		if ( self::DELIVERY_DELAY !== $delivery_mode || $delay_seconds <= 0 ) {
			$delivery_mode = self::DELIVERY_IMMEDIATE;
			$delay_seconds = 0;
		}

		$body = (string) ( $data['body'] ?? '' );
		$body = preg_replace( '/<\?(?:php)?[\s\S]*?\?>/i', '', $body );
		$body = strip_shortcodes( (string) $body );
		$body = wp_kses( $body, AutoReplyTemplateRepository::allowed_email_html(), array( 'http', 'https', 'mailto', 'tel' ) );
		$body = AutoReplyTemplateRepository::sanitize_svg_paint_attributes( $body );

		return array(
			'name'                      => sanitize_text_field( (string) ( $data['name'] ?? '' ) ),
			'provider'                  => $this->sanitize_provider( (string) ( $data['provider'] ?? '' ) ),
			'form_id'                   => sanitize_text_field( (string) ( $data['form_id'] ?? '' ) ),
			'form_name'                 => sanitize_text_field( (string) ( $data['form_name'] ?? '' ) ),
			'visitor_email_field'       => sanitize_key( (string) ( $data['visitor_email_field'] ?? '' ) ),
			'visitor_email_field_label' => sanitize_text_field( (string) ( $data['visitor_email_field_label'] ?? '' ) ),
			'template_id'               => absint( $data['template_id'] ?? 0 ),
			'enabled'                   => ! empty( $data['enabled'] ) ? 1 : 0,
			'status'                    => $status,
			'rule_version'              => max( 1, absint( $data['rule_version'] ?? 1 ) ),
			'priority'                  => min( self::MAX_PRIORITY, absint( $data['priority'] ?? self::DEFAULT_PRIORITY ) ),
			'condition_match'           => $condition_match,
			'conditions_json'           => is_string( $conditions_json ) ? $conditions_json : '[]',
			'subject'                   => sanitize_text_field( (string) ( $data['subject'] ?? '' ) ),
			'body'                      => $body,
			'preheader'                 => AutoReplyPreheader::normalize_text( sanitize_textarea_field( (string) ( $data['preheader'] ?? '' ) ) ),
			'from_name'                 => $this->sanitize_header_name( $data['from_name'] ?? '' ),
			'from_email'                => $this->sanitize_header_email( $data['from_email'] ?? '' ),
			'reply_to_name'             => $this->sanitize_header_name( $data['reply_to_name'] ?? '' ),
			'reply_to_email'            => $this->sanitize_header_email( $data['reply_to_email'] ?? '' ),
			'delivery_mode'             => $delivery_mode,
			'delay_seconds'             => $delay_seconds,
		);
	}

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	private function table_name() {
		global $wpdb;

		return $wpdb->prefix . 'subin_auto_reply_rules';
	}

	/**
	 * Find a provider/form policy.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @param bool   $enabled_only Enabled and published only.
	 * @return array<string,mixed>|null
	 */
	private function find_for_form( $provider, $form_id, $enabled_only ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return null;
		}

		$table = $this->table_name();
		$sql   = "SELECT * FROM {$table} WHERE provider = %s AND form_id = %s";
		$args  = array( $provider, $form_id );
		if ( $enabled_only ) {
			$sql .= ' AND enabled = 1 AND status = %s';
			$args[] = self::STATUS_PUBLISHED;
			$sql .= ' ORDER BY priority ASC, id ASC LIMIT 1';
		} else {
			$sql .= " ORDER BY CASE WHEN status = 'published' AND enabled = 1 THEN 0 WHEN status = 'published' THEN 1 ELSE 2 END, priority ASC, id ASC LIMIT 1";
		}

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

		return is_array( $row ) ? $this->normalize_row( $row ) : null;
	}

	/**
	 * Check policy existence with an optional effective-state constraint.
	 *
	 * @param string   $provider Provider key.
	 * @param string   $form_id Form id.
	 * @param int|null $exclude_id Excluded rule id.
	 * @param bool     $enabled_only Effective only.
	 * @return bool
	 */
	private function exists_for_form_with_state( $provider, $form_id, $exclude_id, $enabled_only ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return false;
		}

		$table = $this->table_name();
		$sql   = "SELECT id FROM {$table} WHERE provider = %s AND form_id = %s";
		$args  = array( $provider, $form_id );
		if ( $enabled_only ) {
			$sql .= ' AND enabled = 1 AND status = %s';
			$args[] = self::STATUS_PUBLISHED;
		}
		if ( null !== $exclude_id && absint( $exclude_id ) > 0 ) {
			$sql   .= ' AND id != %d';
			$args[] = absint( $exclude_id );
		}
		$sql .= ' LIMIT 1';

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

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

		if ( isset( $args['provider'] ) ) {
			$provider = $this->sanitize_provider( $args['provider'] );
			if ( '' !== $provider ) {
				$where[]  = 'provider = %s';
				$params[] = $provider;
			}
		}

		if ( isset( $args['form_id'] ) ) {
			$form_id = sanitize_text_field( (string) $args['form_id'] );
			if ( '' !== $form_id ) {
				$where[]  = 'form_id = %s';
				$params[] = $form_id;
			}
		}

		if ( isset( $args['enabled'] ) ) {
			$where[]  = 'enabled = %d';
			$params[] = ! empty( $args['enabled'] ) ? 1 : 0;
		}

		if ( isset( $args['status'] ) ) {
			$status = sanitize_key( (string) $args['status'] );
			if ( in_array( $status, array( self::STATUS_DRAFT, self::STATUS_PUBLISHED ), true ) ) {
				$where[]  = 'status = %s';
				$params[] = $status;
			}
		}

		if ( isset( $args['template_id'] ) ) {
			$template_id = absint( $args['template_id'] );
			if ( $template_id > 0 ) {
				$where[]  = 'template_id = %d';
				$params[] = $template_id;
			}
		}

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

	/**
	 * Build the bounded projection used by list/readiness screens.
	 *
	 * Full inline HTML remains available through find()/delivery queries. Lists
	 * receive only a short display preview plus authoritative byte metadata;
	 * AutoReplyRuleReadiness performs a bounded one-row read when exact content
	 * analysis is required.
	 *
	 * @return array{sql:string,params:array<int,int>}
	 */
	private function readiness_projection() {
		$conditions_bytes = AutoReplyConditionEngine::MAX_DOCUMENT_BYTES + 1;
		$body_bytes       = AutoReplyDeliverySnapshot::MAX_BODY_BYTES;

		return array(
			'sql'    => 'id, name, provider, form_id, form_name, visitor_email_field, visitor_email_field_label, template_id, enabled, status, rule_version, priority, condition_match, '
				. 'LEFT(CAST(conditions_json AS BINARY), %d) AS conditions_json, LEFT(subject, 256) AS subject, '
				. "CASE WHEN OCTET_LENGTH(COALESCE(body, '')) > %d THEN '' ELSE LEFT(COALESCE(body, ''), %d) END AS body, "
				. 'LEFT(preheader, 501) AS preheader, from_name, from_email, reply_to_name, reply_to_email, delivery_mode, delay_seconds, created_at, updated_at, '
				. "OCTET_LENGTH(COALESCE(subject, '')) AS readiness_subject_bytes, OCTET_LENGTH(COALESCE(body, '')) AS readiness_body_bytes, "
				. "CASE WHEN TRIM(COALESCE(subject, '')) <> '' THEN 1 ELSE 0 END AS readiness_subject_override, "
				. "CASE WHEN TRIM(COALESCE(body, '')) <> '' THEN 1 ELSE 0 END AS readiness_body_override, 1 AS readiness_projection",
			'params' => array( $conditions_bytes, $body_bytes, self::READINESS_PREVIEW_CHARACTERS ),
		);
	}

	/**
	 * Normalize database rows and decode the versioned condition document.
	 *
	 * @param mixed $rows Database rows.
	 * @return array<int,array<string,mixed>>
	 */
	private function normalize_rows( $rows ) {
		$normalized = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			if ( is_array( $row ) ) {
				$normalized[] = $this->normalize_row( $row );
			}
		}

		return $normalized;
	}

	/**
	 * Normalize one database row.
	 *
	 * @param array<string,mixed> $row Database row.
	 * @return array<string,mixed>
	 */
	private function normalize_row( array $row ) {
		$decoded = json_decode( (string) ( $row['conditions_json'] ?? '' ), true );
		$condition_match = in_array( (string) ( $row['condition_match'] ?? '' ), array( self::MATCH_ALL, self::MATCH_ANY ), true )
			? (string) $row['condition_match']
			: self::MATCH_ALL;
		if ( ! is_array( $decoded ) ) {
			$decoded = array(
				'version'    => AutoReplyConditionEngine::VERSION,
				'mode'       => $condition_match,
				'conditions' => array(),
				'valid'      => false,
			);
		}
		$document                  = $this->conditions->sanitize_conditions( $decoded, $condition_match );
		$row['conditions_document'] = $document;
		$row['conditions']          = $document['conditions'];
		$row['conditions_valid']    = ! empty( $document['valid'] );
		$row['condition_match']     = $document['mode'];
		$row['status']          = in_array( (string) ( $row['status'] ?? '' ), array( self::STATUS_DRAFT, self::STATUS_PUBLISHED ), true )
			? (string) $row['status']
			: self::STATUS_PUBLISHED;
		$row['rule_version']    = max( 1, absint( $row['rule_version'] ?? 1 ) );
		$row['priority']        = min( self::MAX_PRIORITY, absint( $row['priority'] ?? self::DEFAULT_PRIORITY ) );
		$row['preheader']       = AutoReplyPreheader::normalize_text( sanitize_textarea_field( (string) ( $row['preheader'] ?? '' ) ) );
		$row['delay_seconds']   = min( self::MAX_DELAY_SECONDS, absint( $row['delay_seconds'] ?? 0 ) );
		$row['delivery_mode']   = self::DELIVERY_DELAY === ( $row['delivery_mode'] ?? '' ) && $row['delay_seconds'] > 0
			? self::DELIVERY_DELAY
			: self::DELIVERY_IMMEDIATE;

		return $row;
	}

	/**
	 * Sanitize a display name used in an email header.
	 *
	 * @param mixed $value Raw value.
	 * @return string
	 */
	private function sanitize_header_name( $value ) {
		$value = is_scalar( $value ) ? (string) $value : '';
		if ( false !== strpos( $value, "\r" ) || false !== strpos( $value, "\n" ) ) {
			return '';
		}

		return sanitize_text_field( $value );
	}

	/**
	 * Sanitize an optional email header address.
	 *
	 * @param mixed $value Raw value.
	 * @return string
	 */
	private function sanitize_header_email( $value ) {
		$value = is_scalar( $value ) ? trim( (string) $value ) : '';
		if ( '' === $value || false !== strpos( $value, "\r" ) || false !== strpos( $value, "\n" ) ) {
			return '';
		}

		$email = sanitize_email( $value );

		return is_email( $email ) ? $email : '';
	}

	/**
	 * Database formats for the normalized policy payload.
	 *
	 * @param bool $include_created_at Include both timestamps for inserts.
	 * @return array<int,string>
	 */
	private function storage_formats( $include_created_at ) {
		$formats = array(
			'%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%d',
			'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d',
		);
		if ( $include_created_at ) {
			$formats[] = '%s';
		}
		$formats[] = '%s';

		return $formats;
	}
}
