<?php
/**
 * Cart Recovery — Repository
 *
 * Persistence layer for the Cart Recovery module. Owns two tables:
 *
 *   {prefix}niwa_ai_cart_recovery             — one row per abandoned cart
 *                                                candidate (session + email
 *                                                combo).
 *   {prefix}niwa_ai_cart_recovery_suppression — permanent opt-out list
 *                                                keyed by email (one-way;
 *                                                removal requires admin UI).
 *
 * A row starts `pending` as soon as we observe cart activity. It moves to
 * `scheduled` when the service decides mail_1 should fire (after the
 * 15-minute inactivity window), then `sent_mail1` / `sent_mail2`, and
 * finally `recovered` on `woocommerce_thankyou` or `expired` on the
 * 72-hour inactivity cap.
 *
 * Prepared statements are used everywhere. `dbDelta` is idempotent so
 * the schema ensurer can run safely on every boot.
 *
 * @package Niwa_AI
 * @since   0.12.0
 */

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

class Niwa_AI_Cart_Recovery_Repository {

	const STATUS_PENDING    = 'pending';
	const STATUS_SCHEDULED  = 'scheduled';
	const STATUS_SENT_MAIL1 = 'sent_mail1';
	const STATUS_SENT_MAIL2 = 'sent_mail2';
	const STATUS_RECOVERED  = 'recovered';
	const STATUS_EXPIRED    = 'expired';
	const STATUS_FAILED     = 'failed';

	public function table_recovery(): string {
		global $wpdb;
		return $wpdb->prefix . Niwa_AI_Constants::TABLE_CART_RECOVERY;
	}

	public function table_suppression(): string {
		global $wpdb;
		return $wpdb->prefix . Niwa_AI_Constants::TABLE_CART_RECOVERY_SUPPRESSION;
	}

	public function normalize_phone( string $phone, string $country = '' ): string {
		return class_exists( 'Niwa_AI_Phone_Normalizer' )
			? (string) Niwa_AI_Phone_Normalizer::normalize( $phone, $country )
			: '';
	}

	/**
	 * Create / migrate schema via dbDelta. Idempotent.
	 */
	public function ensure_table_schema(): bool {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();
		$recovery        = $this->table_recovery();
		$suppression     = $this->table_suppression();

		$sql_recovery = "CREATE TABLE {$recovery} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id varchar(64) NOT NULL DEFAULT '',
			email varchar(191) NOT NULL DEFAULT '',
			phone varchar(32) NOT NULL DEFAULT '',
			contact_id bigint(20) unsigned NOT NULL DEFAULT 0,
			customer_name varchar(191) NOT NULL DEFAULT '',
			locale varchar(16) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'pending',
			cart_snapshot_json longtext NULL,
			items_hash varchar(32) NOT NULL DEFAULT '',
			item_count smallint(5) unsigned NOT NULL DEFAULT 0,
			subtotal decimal(12,4) NOT NULL DEFAULT 0,
			total decimal(12,4) NOT NULL DEFAULT 0,
			currency varchar(8) NOT NULL DEFAULT '',
			first_seen_at datetime NOT NULL,
			last_activity_at datetime NOT NULL,
			mail1_scheduled_at datetime NULL DEFAULT NULL,
			mail1_sent_at datetime NULL DEFAULT NULL,
			mail1_email_sent_at datetime NULL DEFAULT NULL,
			mail1_whatsapp_queued_at datetime NULL DEFAULT NULL,
			mail2_scheduled_at datetime NULL DEFAULT NULL,
			mail2_sent_at datetime NULL DEFAULT NULL,
			mail2_email_sent_at datetime NULL DEFAULT NULL,
			mail2_whatsapp_queued_at datetime NULL DEFAULT NULL,
			recovered_at datetime NULL DEFAULT NULL,
			recovered_order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			coupon_code varchar(64) NOT NULL DEFAULT '',
			coupon_expires_at datetime NULL DEFAULT NULL,
			opens smallint(5) unsigned NOT NULL DEFAULT 0,
			clicks smallint(5) unsigned NOT NULL DEFAULT 0,
			send_attempts smallint(5) unsigned NOT NULL DEFAULT 0,
			last_send_error varchar(255) NOT NULL DEFAULT '',
			unsubscribe_token varchar(64) NOT NULL DEFAULT '',
			created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY session_email (session_id, email),
			KEY status_activity (status, last_activity_at),
			KEY email (email),
			KEY phone (phone),
			KEY mail1_scheduled (mail1_scheduled_at),
			KEY mail2_scheduled (mail2_scheduled_at)
		) {$charset_collate};";

		$sql_suppression = "CREATE TABLE {$suppression} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email varchar(191) NOT NULL DEFAULT '',
			reason varchar(32) NOT NULL DEFAULT 'unsubscribe',
			created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY email (email)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql_recovery );
		dbDelta( $sql_suppression );

		foreach ( array( 'phone', 'contact_id', 'mail1_email_sent_at', 'mail1_whatsapp_queued_at', 'mail2_email_sent_at', 'mail2_whatsapp_queued_at' ) as $column ) {
			$exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$recovery} LIKE %s", $column ) );
			if ( empty( $exists ) ) {
				return false;
			}
		}

		// Existing installs used the aggregate stage markers for email-only
		// delivery. Backfill them once so cooldowns and email analytics keep
		// their pre-upgrade history after channel-specific columns are added.
		$wpdb->query( "UPDATE {$recovery} SET mail1_email_sent_at = mail1_sent_at WHERE mail1_sent_at IS NOT NULL AND mail1_email_sent_at IS NULL" );
		$wpdb->query( "UPDATE {$recovery} SET mail2_email_sent_at = mail2_sent_at WHERE mail2_sent_at IS NOT NULL AND mail2_email_sent_at IS NULL" );

		return true;
	}

	/**
	 * Upsert a cart recovery row keyed by (session_id, email).
	 *
	 * Shadow rows (email == '' and phone == '') are allowed for pre-contact
	 * carts and are later linked via `attach_contact()`. Updates re-set
	 * `last_activity_at` and replace the snapshot JSON / hash. A row that
	 * already reached a terminal status (recovered / expired / sent_mail2)
	 * is NOT re-opened by a later activity — that's a fresh cart cycle
	 * governed by the 7-day cooldown in the service.
	 *
	 * @param string                              $session_id Session token.
	 * @param string                              $email      Email or ''.
	 * @param Niwa_AI_Cart_Recovery_Cart_Snapshot $snapshot   Snapshot.
	 * @param string                              $locale     WP locale.
	 * @param string                              $customer_name Optional name.
	 * @param string                              $phone      Normalized or display phone.
	 * @param int                                 $contact_id Contact table row id.
	 * @return int|null Row id, or null on failure.
	 */
	public function upsert_snapshot(
		string $session_id,
		string $email,
		Niwa_AI_Cart_Recovery_Cart_Snapshot $snapshot,
		string $locale = '',
		string $customer_name = '',
		string $phone = '',
		int $contact_id = 0
	): ?int {
		global $wpdb;

		$session_id = substr( $session_id, 0, 64 );
		$email      = strtolower( trim( $email ) );
		$email      = substr( $email, 0, 191 );
		$phone      = $this->normalize_phone( $phone );
		$contact_id = max( 0, $contact_id );

		if ( '' === $session_id ) {
			return null;
		}

		$existing = $this->find_by_session_and_email( $session_id, $email );
		$now      = current_time( 'mysql' );
		$json     = $snapshot->to_json();

		if ( is_array( $existing ) ) {
			if ( in_array(
				(string) ( $existing['status'] ?? '' ),
				array( self::STATUS_RECOVERED, self::STATUS_EXPIRED, self::STATUS_SENT_MAIL2, self::STATUS_FAILED ),
				true
			) ) {
				return (int) $existing['id'];
			}

			$data = array(
				'cart_snapshot_json' => $json,
				'items_hash'         => $snapshot->items_hash,
				'item_count'         => $snapshot->item_count(),
				'subtotal'           => $snapshot->subtotal,
				'total'              => $snapshot->total,
				'currency'           => $snapshot->currency,
				'last_activity_at'   => $now,
				'updated_at'         => $now,
			);
			$formats = array( '%s', '%s', '%d', '%f', '%f', '%s', '%s', '%s' );

			if ( '' !== trim( $locale ) ) {
				$data['locale'] = substr( $locale, 0, 16 );
				$formats[]       = '%s';
			}
			if ( '' !== trim( $customer_name ) ) {
				$data['customer_name'] = substr( $customer_name, 0, 191 );
				$formats[]              = '%s';
			}
			if ( '' !== $phone ) {
				$data['phone'] = $phone;
				$formats[]     = '%s';
			}
			if ( $contact_id > 0 ) {
				$data['contact_id'] = $contact_id;
				$formats[]          = '%d';
			}

			$wpdb->update(
				$this->table_recovery(),
				$data,
				array( 'id' => (int) $existing['id'] ),
				$formats,
				array( '%d' )
			);

			return (int) $existing['id'];
		}

		$ok = $wpdb->insert(
			$this->table_recovery(),
			array(
				'session_id'         => $session_id,
				'email'              => $email,
				'phone'              => $phone,
				'contact_id'         => $contact_id,
				'customer_name'      => substr( $customer_name, 0, 191 ),
				'locale'             => substr( $locale, 0, 16 ),
				'status'             => self::STATUS_PENDING,
				'cart_snapshot_json' => $json,
				'items_hash'         => $snapshot->items_hash,
				'item_count'         => $snapshot->item_count(),
				'subtotal'           => $snapshot->subtotal,
				'total'              => $snapshot->total,
				'currency'           => $snapshot->currency,
				'first_seen_at'      => $now,
				'last_activity_at'   => $now,
				'unsubscribe_token'  => $this->generate_token(),
				'created_at'         => $now,
				'updated_at'         => $now,
			),
			array( '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $ok ) {
			return null;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Create a minimal session/contact seed before cart activity exists.
	 * A later snapshot upsert reuses the same row, including phone-only
	 * contacts whose email remains empty.
	 */
	public function ensure_session_contact_seed(
		string $session_id,
		string $email,
		string $phone,
		string $customer_name = '',
		int $contact_id = 0
	): ?int {
		global $wpdb;

		$session_id = substr( trim( $session_id ), 0, 64 );
		$email      = strtolower( trim( $email ) );
		$email      = substr( $email, 0, 191 );
		$phone      = $this->normalize_phone( $phone );
		$contact_id = max( 0, $contact_id );

		if ( '' === $session_id || ( '' === $email && '' === $phone ) ) {
			return null;
		}

		$existing = $this->find_by_session_and_email( $session_id, $email );
		if ( is_array( $existing ) ) {
			$this->update_contact_fields( (int) $existing['id'], '', $phone, $customer_name, $contact_id );
			return (int) $existing['id'];
		}

		$now = current_time( 'mysql' );
		$ok  = $wpdb->insert(
			$this->table_recovery(),
			array(
				'session_id'         => $session_id,
				'email'              => $email,
				'phone'              => $phone,
				'contact_id'         => $contact_id,
				'customer_name'      => substr( $customer_name, 0, 191 ),
				'locale'             => '',
				'status'             => self::STATUS_PENDING,
				'cart_snapshot_json' => '',
				'items_hash'         => '',
				'item_count'         => 0,
				'subtotal'           => 0.0,
				'total'              => 0.0,
				'currency'           => '',
				'first_seen_at'      => $now,
				'last_activity_at'   => $now,
				'unsubscribe_token'  => $this->generate_token(),
				'created_at'         => $now,
				'updated_at'         => $now,
			),
			array( '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $ok ) {
			return null;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Attach newly captured contact coordinates to the current session row.
	 * Email and phone may arrive in separate lead-form steps; both update the
	 * same latest row instead of creating unrelated recovery candidates.
	 */
	public function attach_contact(
		string $session_id,
		string $email,
		string $phone,
		string $customer_name = '',
		int $contact_id = 0
	): ?int {
		global $wpdb;

		$session_id = substr( trim( $session_id ), 0, 64 );
		$email      = strtolower( trim( $email ) );
		$email      = substr( $email, 0, 191 );
		$phone      = $this->normalize_phone( $phone );
		$contact_id = max( 0, $contact_id );

		if ( '' === $session_id || ( '' === $email && '' === $phone ) ) {
			return null;
		}

		$shadow = $this->find_by_session_and_email( $session_id, '' );
		$existing = '' !== $email ? $this->find_by_session_and_email( $session_id, $email ) : null;
		if ( is_array( $existing ) ) {
			$resolved_phone = '' !== $phone ? $phone : (string) ( $shadow['phone'] ?? '' );
			$resolved_name  = '' !== trim( $customer_name ) ? $customer_name : (string) ( $shadow['customer_name'] ?? '' );
			$resolved_id    = $contact_id > 0 ? $contact_id : (int) ( $shadow['contact_id'] ?? 0 );
			if ( is_array( $shadow ) && (int) $shadow['id'] !== (int) $existing['id'] && ! empty( $shadow['cart_snapshot_json'] ) ) {
				$snapshot = Niwa_AI_Cart_Recovery_Cart_Snapshot::from_json( (string) $shadow['cart_snapshot_json'] );
				if ( $snapshot ) {
					$this->upsert_snapshot(
						$session_id,
						$email,
						$snapshot,
						(string) ( $shadow['locale'] ?? '' ),
						$resolved_name,
						$resolved_phone,
						$resolved_id
					);
				}
			}
			$this->update_contact_fields( (int) $existing['id'], '', $resolved_phone, $resolved_name, $resolved_id );
			if ( is_array( $shadow ) && (int) $shadow['id'] !== (int) $existing['id'] ) {
				$wpdb->delete( $this->table_recovery(), array( 'id' => (int) $shadow['id'] ), array( '%d' ) );
			}
			return (int) $existing['id'];
		}

		$target = is_array( $shadow ) ? $shadow : $this->find_latest_by_session( $session_id );
		if ( ! is_array( $target ) ) {
			return null;
		}

		$this->update_contact_fields( (int) $target['id'], $email, $phone, $customer_name, $contact_id );
		return (int) $target['id'];
	}

	private function update_contact_fields(
		int $id,
		string $email,
		string $phone,
		string $customer_name,
		int $contact_id
	): void {
		global $wpdb;

		if ( $id <= 0 ) {
			return;
		}

		$data    = array( 'updated_at' => current_time( 'mysql' ) );
		$formats = array( '%s' );
		if ( '' !== $email ) {
			$data['email'] = substr( strtolower( trim( $email ) ), 0, 191 );
			$formats[]     = '%s';
		}
		$phone = $this->normalize_phone( $phone );
		if ( '' !== $phone ) {
			$data['phone'] = $phone;
			$formats[]     = '%s';
		}
		if ( '' !== trim( $customer_name ) ) {
			$data['customer_name'] = substr( $customer_name, 0, 191 );
			$formats[]              = '%s';
		}
		if ( $contact_id > 0 ) {
			$data['contact_id'] = $contact_id;
			$formats[]          = '%d';
		}

		$wpdb->update(
			$this->table_recovery(),
			$data,
			array( 'id' => $id ),
			$formats,
			array( '%d' )
		);
	}

	public function find_by_session_and_email( string $session_id, string $email ): ?array {
		global $wpdb;
		$table = $this->table_recovery();
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE session_id = %s AND email = %s LIMIT 1",
				$session_id,
				$email
			),
			ARRAY_A
		);
		return is_array( $row ) ? $row : null;
	}

	public function find_by_id( int $id ): ?array {
		global $wpdb;
		$table = $this->table_recovery();
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $id ),
			ARRAY_A
		);
		return is_array( $row ) ? $row : null;
	}

	/**
	 * All pending shadow rows (no email and no phone) — used by the dev "force link"
	 * tool to bulk-attach an operator email for local end-to-end testing.
	 * Terminal statuses are excluded so we don't reopen old recovered /
	 * expired carts.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_pending_shadow_rows( int $limit = 100 ): array {
		global $wpdb;
		$table = $this->table_recovery();
		$limit = max( 1, min( 500, $limit ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table}
				 WHERE email = ''
				   AND phone = ''
				   AND status = %s
				 ORDER BY id DESC
				 LIMIT %d",
				self::STATUS_PENDING,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function count_pending_shadow_rows(): int {
		global $wpdb;
		$table = $this->table_recovery();
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE email = '' AND phone = '' AND status = %s",
				self::STATUS_PENDING
			)
		);
	}

	public function count_pending_with_contact(): int {
		global $wpdb;
		$table = $this->table_recovery();
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE (email != '' OR phone != '') AND status = %s",
				self::STATUS_PENDING
			)
		);
	}

	/**
	 * Bulk-attach `$email` to every shadow row currently in pending. For
	 * each target row we reuse `attach_contact()` so the merge-into-existing
	 * behavior (delete-shadow-if-linked-row-already-exists) stays correct.
	 * Returns the number of rows successfully upgraded.
	 */
	public function attach_email_to_all_pending_shadows( string $email ): int {
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return 0;
		}

		$rows    = $this->get_pending_shadow_rows( 500 );
		$touched = 0;

		foreach ( $rows as $row ) {
			$session_id = (string) ( $row['session_id'] ?? '' );
			if ( '' === $session_id ) {
				continue;
			}
			$linked = $this->attach_contact( $session_id, $email, '', (string) ( $row['customer_name'] ?? '' ) );
			if ( is_int( $linked ) && $linked > 0 ) {
				$touched++;
			}
		}

		return $touched;
	}

	/**
	 * Most recent row for a session, regardless of email state. Used by
	 * the Service's email-resolver to check whether any earlier upsert
	 * for this visitor already captured a non-empty email (including
	 * shadow → linked transitions).
	 */
	public function find_latest_by_session( string $session_id ): ?array {
		global $wpdb;
		$session_id = substr( trim( $session_id ), 0, 64 );
		if ( '' === $session_id ) {
			return null;
		}

		$table = $this->table_recovery();
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE session_id = %s ORDER BY id DESC LIMIT 1",
				$session_id
			),
			ARRAY_A
		);

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

	public function find_latest_by_email( string $email ): ?array {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		$table = $this->table_recovery();
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE email = %s ORDER BY id DESC LIMIT 1",
				$email
			),
			ARRAY_A
		);
		return is_array( $row ) ? $row : null;
	}

	public function update_status( int $id, string $status, array $extra = array() ): void {
		global $wpdb;

		$data    = array( 'status' => $status, 'updated_at' => current_time( 'mysql' ) );
		$formats = array( '%s', '%s' );

		foreach ( $extra as $column => $value ) {
			$data[ $column ] = $value;
			$formats[]       = is_int( $value ) ? '%d' : '%s';
		}

		$wpdb->update(
			$this->table_recovery(),
			$data,
			array( 'id' => $id ),
			$formats,
			array( '%d' )
		);
	}

	public function mark_recovered( int $id, int $order_id ): void {
		$this->update_status(
			$id,
			self::STATUS_RECOVERED,
			array(
				'recovered_at'       => current_time( 'mysql' ),
				'recovered_order_id' => (int) $order_id,
			)
		);
	}

	/**
	 * Mark the newest active recovery row matched by a real email or a
	 * normalized billing phone. Phone matching covers WhatsApp-only carts.
	 */
	public function mark_recovered_by_contact( string $email, string $phone, int $order_id ): ?int {
		global $wpdb;

		$email = strtolower( trim( $email ) );
		$phone = $this->normalize_phone( $phone );
		if ( '' === $email && '' === $phone ) {
			return null;
		}

		$table   = $this->table_recovery();
		$pending = array( self::STATUS_PENDING, self::STATUS_SCHEDULED, self::STATUS_SENT_MAIL1, self::STATUS_SENT_MAIL2 );
		$ph      = implode( ',', array_fill( 0, count( $pending ), '%s' ) );

		$id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table}
				 WHERE ((%s != '' AND email = %s) OR (%s != '' AND phone = %s))
				   AND status IN ({$ph})
				 ORDER BY CASE
				   WHEN %s != '' AND %s != '' AND email = %s AND phone = %s THEN 0
				   WHEN %s != '' AND email = %s THEN 1
				   ELSE 2
				 END, id DESC LIMIT 1",
				array_merge(
					array( $email, $email, $phone, $phone ),
					$pending,
					array( $email, $phone, $email, $phone, $email, $email )
				)
			)
		);

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

		$this->mark_recovered( $id, $order_id );
		return $id;
	}

	/**
	 * Return rows that are due for a given mail stage (`mail1` / `mail2`).
	 * Used by the Action Scheduler tick to drive the drip.
	 *
	 * @param string $stage 'mail1' | 'mail2'
	 * @param int    $limit Batch size.
	 * @return array<int, array<string, mixed>>
	 */
	public function get_due_for_stage( string $stage, int $limit = 50 ): array {
		global $wpdb;
		$table = $this->table_recovery();
		$now   = current_time( 'mysql' );

		if ( 'mail1' === $stage ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table}
					 WHERE status = %s
					   AND (email != '' OR phone != '')
					   AND item_count > 0
					   AND mail1_scheduled_at IS NOT NULL
					   AND mail1_scheduled_at <= %s
					 ORDER BY mail1_scheduled_at ASC
					 LIMIT %d",
					self::STATUS_SCHEDULED,
					$now,
					$limit
				),
				ARRAY_A
			);
		} elseif ( 'mail2' === $stage ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table}
					 WHERE status = %s
					   AND (email != '' OR phone != '')
					   AND item_count > 0
					   AND mail2_scheduled_at IS NOT NULL
					   AND mail2_scheduled_at <= %s
					 ORDER BY mail2_scheduled_at ASC
					 LIMIT %d",
					self::STATUS_SENT_MAIL1,
					$now,
					$limit
				),
				ARRAY_A
			);
		} else {
			return array();
		}

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Rows whose `last_activity_at` is older than the inactivity cap and
	 * have not yet reached a terminal status — to be marked `expired`.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_expired( int $cap_hours, int $limit = 100 ): array {
		global $wpdb;
		$table = $this->table_recovery();
		// Threshold must be in site timezone — `last_activity_at` is stored via
		// `current_time('mysql')` (local time). Using `gmdate()` here used to
		// bake in the UTC offset, so for TZ ahead of UTC the comparison never
		// matched until the delay exceeded the offset.
		$threshold = $this->local_mysql_ts( -( $cap_hours * HOUR_IN_SECONDS ) );
		$active    = array( self::STATUS_PENDING, self::STATUS_SCHEDULED, self::STATUS_SENT_MAIL1 );
		$ph        = implode( ',', array_fill( 0, count( $active ), '%s' ) );
		$args      = array_merge( $active, array( $threshold, $limit ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table}
				 WHERE status IN ({$ph})
				   AND last_activity_at <= %s
				 ORDER BY last_activity_at ASC
				 LIMIT %d",
				$args
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function add_suppression( string $email, string $reason = 'unsubscribe' ): bool {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return false;
		}

		$ok = $wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO {$this->table_suppression()} (email, reason, created_at) VALUES (%s, %s, %s)",
				substr( $email, 0, 191 ),
				substr( $reason, 0, 32 ),
				current_time( 'mysql' )
			)
		);

		return false !== $ok;
	}

	public function is_suppressed( string $email ): bool {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return false;
		}

		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$this->table_suppression()} WHERE email = %s LIMIT 1",
				$email
			)
		);

		return ! empty( $exists );
	}

	public function add_phone_suppression( string $phone, string $reason = 'unsubscribe' ): bool {
		global $wpdb;
		$hash = $this->phone_suppression_hash( $phone );
		if ( '' === $hash ) {
			return false;
		}

		$ok = $wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO {$this->table_suppression()} (email, reason, created_at) VALUES (%s, %s, %s)",
				'phone:' . $hash,
				substr( $reason, 0, 32 ),
				current_time( 'mysql' )
			)
		);

		return false !== $ok;
	}

	public function is_phone_suppressed( string $phone ): bool {
		global $wpdb;
		$hash = $this->phone_suppression_hash( $phone );
		if ( '' === $hash ) {
			return false;
		}

		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$this->table_suppression()} WHERE email = %s LIMIT 1",
				'phone:' . $hash
			)
		);
		if ( ! empty( $exists ) ) {
			return true;
		}

		return false;
	}

	/**
	 * Whether a recovery sequence was already sent to `$email` within the
	 * last `$days` days. Guards against spamming the same address when a
	 * visitor starts a fresh cart mid-cooldown. Looks at mail1_sent_at —
	 * the first point at which we physically emailed them.
	 */
	public function is_in_cooldown( string $email, int $days ): bool {
		global $wpdb;

		$email = strtolower( trim( $email ) );
		if ( '' === $email || $days <= 0 ) {
			return false;
		}

		// Local-time threshold — the email marker is written via `current_time('mysql')`.
		$threshold = $this->local_mysql_ts( -( $days * DAY_IN_SECONDS ) );
		$table     = $this->table_recovery();

		$found = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table}
				 WHERE email = %s
				   AND mail1_email_sent_at IS NOT NULL
				   AND mail1_email_sent_at >= %s
				 LIMIT 1",
				$email,
				$threshold
			)
		);

		return ! empty( $found );
	}

	public function is_phone_in_cooldown( string $phone, int $days ): bool {
		global $wpdb;

		$phone = $this->normalize_phone( $phone );
		if ( '' === $phone || $days <= 0 ) {
			return false;
		}

		$threshold = $this->local_mysql_ts( -( $days * DAY_IN_SECONDS ) );
		$table     = $this->table_recovery();
		$found     = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table}
				 WHERE phone = %s
				   AND mail1_whatsapp_queued_at IS NOT NULL
				   AND mail1_whatsapp_queued_at >= %s
				 LIMIT 1",
				$phone,
				$threshold
			)
		);

		return ! empty( $found );
	}

	/**
	 * Rows in `pending` status whose `last_activity_at` is older than the
	 * mail_1 delay AND which have an email or phone attached. Ordered by
	 * last_activity_at ASC so the oldest abandoned
	 * cart goes first.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_pending_due_for_promotion( int $delay_minutes, int $limit = 50 ): array {
		global $wpdb;

		// Local-time threshold — `last_activity_at` is written via `current_time('mysql')`.
		$threshold = $this->local_mysql_ts( -( max( 0, $delay_minutes ) * MINUTE_IN_SECONDS ) );
		$table     = $this->table_recovery();

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table}
				 WHERE status = %s
				   AND (email != '' OR phone != '')
				   AND item_count > 0
				   AND last_activity_at <= %s
				 ORDER BY last_activity_at ASC
				 LIMIT %d",
				self::STATUS_PENDING,
				$threshold,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Rows that already received mail_1 and are now old enough for mail_2.
	 * Additional filter: `mail2_scheduled_at IS NULL` so a row only gets
	 * promoted once per cycle.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_sent_mail1_due_for_mail2( int $delay_hours, int $limit = 50 ): array {
		global $wpdb;

		// Local-time threshold — `mail1_sent_at` is written via `current_time('mysql')`.
		$threshold = $this->local_mysql_ts( -( max( 0, $delay_hours ) * HOUR_IN_SECONDS ) );
		$table     = $this->table_recovery();

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table}
				 WHERE status = %s
				   AND (email != '' OR phone != '')
				   AND item_count > 0
				   AND mail1_sent_at IS NOT NULL
				   AND mail1_sent_at <= %s
				   AND mail2_scheduled_at IS NULL
				 ORDER BY mail1_sent_at ASC
				 LIMIT %d",
				self::STATUS_SENT_MAIL1,
				$threshold,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Atomically stamp one accepted delivery channel for a stage.
	 * Returns true only when this call changed the marker from NULL.
	 */
	public function mark_delivery_channel_queued( int $id, string $stage, string $channel ): bool {
		global $wpdb;

		$columns = array(
			'mail1:email'    => 'mail1_email_sent_at',
			'mail1:whatsapp' => 'mail1_whatsapp_queued_at',
			'mail2:email'    => 'mail2_email_sent_at',
			'mail2:whatsapp' => 'mail2_whatsapp_queued_at',
		);
		$key = $stage . ':' . $channel;
		if ( $id <= 0 || ! isset( $columns[ $key ] ) ) {
			return false;
		}

		$column = $columns[ $key ];
		$table  = $this->table_recovery();
		$result = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET {$column} = %s, updated_at = %s WHERE id = %d AND {$column} IS NULL",
				current_time( 'mysql' ),
				current_time( 'mysql' ),
				$id
			)
		);

		return 1 === (int) $result;
	}

	public function acquire_send_lock( int $id, string $stage ): bool {
		global $wpdb;
		if ( $id <= 0 || ! in_array( $stage, array( 'mail1', 'mail2' ), true ) ) {
			return false;
		}

		$name = 'niwa_cr_' . $id . '_' . $stage;
		return 1 === (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $name ) );
	}

	public function release_send_lock( int $id, string $stage ): void {
		global $wpdb;
		if ( $id <= 0 || ! in_array( $stage, array( 'mail1', 'mail2' ), true ) ) {
			return;
		}

		$name = 'niwa_cr_' . $id . '_' . $stage;
		$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $name ) );
	}

	/**
	 * Mark row as having completed the mail_1 send step. Flips status
	 * to `sent_mail1` and stamps `mail1_sent_at`. The mailer calls
	 * this only after `wp_mail` returned true — so the scheduler's
	 * next tick won't re-fire the same send.
	 */
	public function mark_sent_mail1( int $id ): void {
		$this->update_status(
			$id,
			self::STATUS_SENT_MAIL1,
			array(
				'mail1_sent_at'   => current_time( 'mysql' ),
				'send_attempts'   => 0,
				'last_send_error' => '',
			)
		);
	}

	/**
	 * Mark row as having completed the mail_2 send step. Final
	 * transition — rows in `sent_mail2` are terminal for the drip
	 * (recovery still happens via order-status listener).
	 */
	public function mark_sent_mail2( int $id ): void {
		$this->update_status(
			$id,
			self::STATUS_SENT_MAIL2,
			array(
				'mail2_sent_at'   => current_time( 'mysql' ),
				'send_attempts'   => 0,
				'last_send_error' => '',
			)
		);
	}

	/**
	 * Atomic `send_attempts + 1` + optional error note. Mailer calls
	 * this on every `wp_mail === false` so the scheduler can eventually
	 * escalate to `STATUS_FAILED` rather than retrying forever.
	 *
	 * Returns the new attempt count so the mailer can decide whether
	 * to escalate in the same call (saves one SELECT).
	 */
	public function increment_send_attempts( int $id, string $error = '' ): int {
		global $wpdb;
		if ( $id <= 0 ) {
			return 0;
		}
		$table = $this->table_recovery();
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET send_attempts = send_attempts + 1, last_send_error = %s, updated_at = %s WHERE id = %d",
				substr( $error, 0, 255 ),
				current_time( 'mysql' ),
				$id
			)
		);

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT send_attempts FROM {$table} WHERE id = %d", $id )
		);
	}

	/**
	 * Terminal failure — row is moved to `STATUS_FAILED`. The scheduler
	 * never re-picks it via `get_due_for_stage`, so this breaks the
	 * retry loop cleanly.
	 */
	public function mark_send_failed( int $id, string $error = '' ): void {
		$extra = array();
		if ( '' !== $error ) {
			$extra['last_send_error'] = substr( $error, 0, 255 );
		}
		$this->update_status( $id, self::STATUS_FAILED, $extra );
	}

	/**
	 * Admin / GDPR support: delete every cart-recovery row (and
	 * suppression record) for a given email. Returns the count of
	 * rows removed — useful for the eraser's `items_removed` return.
	 */
	public function purge_for_email( string $email ): int {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return 0;
		}

		$recovery    = (int) $wpdb->delete( $this->table_recovery(), array( 'email' => $email ), array( '%s' ) );
		$suppression = (int) $wpdb->delete( $this->table_suppression(), array( 'email' => $email ), array( '%s' ) );

		return $recovery + $suppression;
	}

	/**
	 * GDPR export helper: all rows for the email (recovery + the
	 * suppression record if present). Column subset kept small — only
	 * fields the visitor conceptually owns (cart snapshot excluded as
	 * it contains merchandising data, not PII).
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function export_for_email( string $email ): array {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return array();
		}

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, session_id, email, phone, contact_id, customer_name, locale, status, first_seen_at, last_activity_at,
				        mail1_sent_at, mail1_email_sent_at, mail1_whatsapp_queued_at,
				        mail2_sent_at, mail2_email_sent_at, mail2_whatsapp_queued_at,
				        recovered_at, recovered_order_id, opens, clicks
				 FROM {$this->table_recovery()} WHERE email = %s ORDER BY id DESC",
				$email
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Mark the row as opened. Idempotent per row: the first pixel hit
	 * wins, subsequent loads (re-opens of the same mail in Gmail/Outlook,
	 * image-proxy re-fetches, forwarded copies, …) are silent no-ops.
	 *
	 * We keep `opens` as the "has this recipient opened any recovery
	 * mail on this row?" flag (0 or 1) rather than a total-views counter
	 * — which matches the admin's expectation of unique opens and keeps
	 * `SUM(opens)` in metrics queries equivalent to unique-open-count.
	 */
	public function increment_opens( int $id ): void {
		global $wpdb;
		if ( $id <= 0 ) {
			return;
		}
		$table = $this->table_recovery();
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET opens = 1, updated_at = %s WHERE id = %d AND opens = 0",
				current_time( 'mysql' ),
				$id
			)
		);
	}

	/**
	 * Bump the clicks counter. Same atomic-increment approach as opens.
	 */
	public function increment_clicks( int $id ): void {
		global $wpdb;
		if ( $id <= 0 ) {
			return;
		}
		$table = $this->table_recovery();
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET clicks = clicks + 1, updated_at = %s WHERE id = %d",
				current_time( 'mysql' ),
				$id
			)
		);
	}

	/**
	 * Attach a coupon code + expiry timestamp to a row. Called by the
	 * Coupon_Service right after `WC_Coupon` creation, so subsequent
	 * mail sends + cleanup passes know which coupon belongs to which row.
	 */
	public function attach_coupon( int $id, string $code, string $expires_at_mysql ): void {
		global $wpdb;

		if ( $id <= 0 || '' === $code ) {
			return;
		}

		$wpdb->update(
			$this->table_recovery(),
			array(
				'coupon_code'       => substr( $code, 0, 64 ),
				'coupon_expires_at' => $expires_at_mysql,
				'updated_at'        => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Clear a stale coupon reference (e.g. for regeneration after
	 * expiry). Does NOT delete the WC_Coupon post — that's the
	 * coupon service's concern.
	 */
	public function clear_coupon( int $id ): void {
		global $wpdb;
		if ( $id <= 0 ) {
			return;
		}
		$wpdb->update(
			$this->table_recovery(),
			array(
				'coupon_code'       => '',
				'coupon_expires_at' => null,
				'updated_at'        => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Reverse lookup: find the recovery row that owns a given coupon
	 * code. Used by the order-status listener for attribution — if a
	 * customer redeems our coupon on an order, we want to mark the
	 * corresponding cart-recovery row as recovered with certainty
	 * (not just "email match, maybe from this sequence").
	 */
	public function find_by_coupon_code( string $code ): ?array {
		global $wpdb;
		$code = trim( $code );
		if ( '' === $code ) {
			return null;
		}

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table_recovery()} WHERE coupon_code = %s ORDER BY id DESC LIMIT 1",
				$code
			),
			ARRAY_A
		);

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

	/**
	 * Rows whose coupon expired more than `$grace_days` ago. Used by
	 * the scheduler's cleanup sub-phase to trash the underlying
	 * WC_Coupon posts once we're sure they won't be used.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_rows_with_expired_coupons( int $grace_days, int $limit = 100 ): array {
		global $wpdb;

		$grace = max( 0, $grace_days );
		// Unlike the other timestamp columns on this row, `coupon_expires_at`
		// is written as UTC by Coupon_Service (via `gmdate`), so this
		// threshold stays in UTC to stay consistent with the stored value.
		$threshold = gmdate( 'Y-m-d H:i:s', time() - ( $grace * DAY_IN_SECONDS ) );
		$table     = $this->table_recovery();

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, coupon_code, coupon_expires_at FROM {$table}
				 WHERE coupon_code != ''
				   AND coupon_expires_at IS NOT NULL
				   AND coupon_expires_at <= %s
				 ORDER BY coupon_expires_at ASC
				 LIMIT %d",
				$threshold,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function find_by_unsubscribe_token( string $token ): ?array {
		global $wpdb;
		$token = trim( $token );
		if ( '' === $token ) {
			return null;
		}

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table_recovery()} WHERE unsubscribe_token = %s LIMIT 1",
				$token
			),
			ARRAY_A
		);

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

	private function phone_suppression_hash( string $phone ): string {
		$phone = $this->normalize_phone( $phone );
		if ( '' === $phone ) {
			return '';
		}
		$key = (string) get_option( Niwa_AI_Constants::OPT_CART_RECOVERY_PHONE_HASH_KEY, '' );
		if ( '' === $key ) {
			$generated = function_exists( 'wp_generate_password' )
				? wp_generate_password( 64, false, false )
				: bin2hex( random_bytes( 32 ) );
			add_option( Niwa_AI_Constants::OPT_CART_RECOVERY_PHONE_HASH_KEY, $generated, '', false );
			$key = (string) get_option( Niwa_AI_Constants::OPT_CART_RECOVERY_PHONE_HASH_KEY, '' );
			if ( '' === $key ) {
				return '';
			}
		}
		return hash_hmac( 'sha256', $phone, $key );
	}

	private function generate_token(): string {
		if ( function_exists( 'wp_generate_password' ) ) {
			return strtolower( wp_generate_password( 32, false, false ) );
		}
		return bin2hex( random_bytes( 16 ) );
	}

	/* ===============================================================
	 * Admin UI queries (Phase 6)
	 * ============================================================ */

	/**
	 * Aggregate metrics for the admin dashboard over the last `$days`.
	 * All counters defensive-cast to int — missing table / empty DB
	 * yields a zero-filled shape so the UI never breaks on a fresh
	 * install.
	 *
	 * @return array<string, int|float>
	 */
	public function get_metrics( int $days = 30 ): array {
		global $wpdb;

		$days     = max( 1, $days );
		$table    = $this->table_recovery();
		// All `*_at` columns are local-time; threshold must match.
		$threshold = $this->local_mysql_ts( -( $days * DAY_IN_SECONDS ) );

		$sent_m1     = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE mail1_sent_at IS NOT NULL AND mail1_sent_at >= %s", $threshold ) );
		$sent_m2     = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE mail2_sent_at IS NOT NULL AND mail2_sent_at >= %s", $threshold ) );
		$email_m1    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE mail1_email_sent_at IS NOT NULL AND mail1_email_sent_at >= %s", $threshold ) );
		$email_m2    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE mail2_email_sent_at IS NOT NULL AND mail2_email_sent_at >= %s", $threshold ) );
		$opens       = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(opens),0) FROM {$table} WHERE updated_at >= %s", $threshold ) );
		$clicks      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(clicks),0) FROM {$table} WHERE updated_at >= %s", $threshold ) );
		$recovered   = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s AND recovered_at >= %s", self::STATUS_RECOVERED, $threshold ) );
		$order_ids   = $wpdb->get_col( $wpdb->prepare( "SELECT recovered_order_id FROM {$table} WHERE status = %s AND recovered_at >= %s AND recovered_order_id > 0", self::STATUS_RECOVERED, $threshold ) );
		$pending     = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s AND first_seen_at >= %s", self::STATUS_PENDING, $threshold ) );
		$suppression = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table_suppression()}" );

		$revenue = $this->sum_order_totals( is_array( $order_ids ) ? $order_ids : array() );
		$sent    = $sent_m1 + $sent_m2;
		$email_sent = $email_m1 + $email_m2;

		$open_rate      = $email_sent > 0 ? round( ( $opens / $email_sent ) * 100, 1 ) : 0.0;
		$click_rate     = $sent > 0 ? round( ( $clicks / $sent ) * 100, 1 ) : 0.0;
		$recovered_rate = $sent > 0 ? round( ( $recovered / $sent ) * 100, 1 ) : 0.0;

		return array(
			'days'             => $days,
			'sent'             => $sent,
			'sent_mail1'       => $sent_m1,
			'sent_mail2'       => $sent_m2,
			'email_sent'       => $email_sent,
			'opens'            => $opens,
			'clicks'           => $clicks,
			'recovered'        => $recovered,
			'pending'          => $pending,
			'suppression'      => $suppression,
			'open_rate'        => $open_rate,
			'click_rate'       => $click_rate,
			'recovered_rate'   => $recovered_rate,
			'recovered_revenue' => $revenue,
		);
	}

	/**
	 * Time-series rollup for the metrics chart. Returns one bucket per
	 * day over the window, each carrying sent/open/click/recovered.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_time_series_metrics( int $days = 30 ): array {
		global $wpdb;

		$days     = max( 1, min( 90, $days ) );
		$table    = $this->table_recovery();
		// Bucket day-boundary must be in site TZ so "today" includes today's
		// rows even near midnight in a TZ ahead of UTC.
		$threshold = $this->local_mysql_date( -( ( $days - 1 ) * DAY_IN_SECONDS ) ) . ' 00:00:00';

		$sent_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(mail1_sent_at) d, COUNT(*) c FROM {$table} WHERE mail1_sent_at IS NOT NULL AND mail1_sent_at >= %s GROUP BY DATE(mail1_sent_at)
				 UNION ALL
				 SELECT DATE(mail2_sent_at) d, COUNT(*) c FROM {$table} WHERE mail2_sent_at IS NOT NULL AND mail2_sent_at >= %s GROUP BY DATE(mail2_sent_at)",
				$threshold,
				$threshold
			),
			ARRAY_A
		);

		$recovered_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(recovered_at) d, COUNT(*) c FROM {$table} WHERE status = %s AND recovered_at >= %s GROUP BY DATE(recovered_at)",
				self::STATUS_RECOVERED,
				$threshold
			),
			ARRAY_A
		);

		$buckets = array();
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			// Bucket keys must be site-TZ dates to match `DATE(mail1_sent_at)`
			// (which MySQL resolves in its own session TZ — WordPress keeps
			// rows in local-time strings, so the DATE() slices agree with
			// `wp_date('Y-m-d')` for the current moment).
			$d                = $this->local_mysql_date( -( $i * DAY_IN_SECONDS ) );
			$buckets[ $d ] = array( 'date' => $d, 'sent' => 0, 'recovered' => 0 );
		}

		if ( is_array( $sent_rows ) ) {
			foreach ( $sent_rows as $r ) {
				$d = (string) ( $r['d'] ?? '' );
				if ( isset( $buckets[ $d ] ) ) {
					$buckets[ $d ]['sent'] += (int) ( $r['c'] ?? 0 );
				}
			}
		}
		if ( is_array( $recovered_rows ) ) {
			foreach ( $recovered_rows as $r ) {
				$d = (string) ( $r['d'] ?? '' );
				if ( isset( $buckets[ $d ] ) ) {
					$buckets[ $d ]['recovered'] = (int) ( $r['c'] ?? 0 );
				}
			}
		}

		return array_values( $buckets );
	}

	/**
	 * Paginated suppression list for the admin table. Matches email
	 * substring when `$search` is non-empty.
	 *
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function get_suppression_list( string $search = '', int $limit = 25, int $offset = 0 ): array {
		global $wpdb;
		$table = $this->table_suppression();

		$limit  = max( 1, min( 200, $limit ) );
		$offset = max( 0, $offset );

		if ( '' !== $search ) {
			$like  = '%' . $wpdb->esc_like( $search ) . '%';
			$items = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table} WHERE email NOT LIKE 'phone:%%' AND email LIKE %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$like,
					$limit,
					$offset
				),
				ARRAY_A
			);
			$total = (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE email NOT LIKE 'phone:%%' AND email LIKE %s", $like )
			);
		} else {
			$items = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table} WHERE email NOT LIKE 'phone:%%' ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$limit,
					$offset
				),
				ARRAY_A
			);
			$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE email NOT LIKE 'phone:%'" );
		}

		return array(
			'items' => is_array( $items ) ? $items : array(),
			'total' => $total,
		);
	}

	/**
	 * Manual removal from the suppression list (admin "oops, unblock me").
	 */
	public function remove_suppression( string $email ): bool {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( '' === $email ) {
			return false;
		}
		$deleted = $wpdb->delete( $this->table_suppression(), array( 'email' => $email ), array( '%s' ) );
		return (bool) $deleted;
	}

	/**
	 * Recent rows for the preview dropdown. Skips rows without a
	 * snapshot JSON (we need one to render a meaningful preview).
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function recent_rows_for_preview( int $limit = 20 ): array {
		global $wpdb;
		$table = $this->table_recovery();
		$limit = max( 1, min( 100, $limit ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, email, phone, contact_id, customer_name, locale, status, first_seen_at, last_activity_at FROM {$table}
				 WHERE cart_snapshot_json IS NOT NULL AND cart_snapshot_json != ''
				 ORDER BY id DESC LIMIT %d",
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}


	/**
	 * Sum `_order_total` across a set of order ids via `wc_get_order`.
	 * Returns 0.0 when WC is unavailable — the metrics card simply
	 * shows 0 revenue rather than erroring.
	 *
	 * @param array<int, mixed> $order_ids
	 */
	/**
	 * Format a timestamp offset (in seconds, positive or negative) from "now"
	 * as a MySQL datetime string in the site's timezone. Matches the format
	 * written by `current_time('mysql')` so the result can be compared
	 * directly against columns like `last_activity_at`, `mail1_sent_at`, etc.
	 *
	 * `wp_date()` was added in WP 5.3 — the plugin's baseline is higher.
	 *
	 * @param int $offset_seconds Seconds relative to now (e.g. `-900` = 15 min ago).
	 */
	private function local_mysql_ts( int $offset_seconds ): string {
		if ( function_exists( 'wp_date' ) ) {
			return (string) wp_date( 'Y-m-d H:i:s', time() + $offset_seconds );
		}
		// Fallback: manual offset against `current_time('timestamp')`.
		$ts = function_exists( 'current_time' )
			? (int) current_time( 'timestamp' )
			: time();
		return gmdate( 'Y-m-d H:i:s', $ts + $offset_seconds );
	}

	/**
	 * Date-only counterpart of `local_mysql_ts()` — returns `Y-m-d` in the
	 * site's timezone. Used for day-bucket keys in the time-series metrics.
	 */
	private function local_mysql_date( int $offset_seconds ): string {
		if ( function_exists( 'wp_date' ) ) {
			return (string) wp_date( 'Y-m-d', time() + $offset_seconds );
		}
		$ts = function_exists( 'current_time' )
			? (int) current_time( 'timestamp' )
			: time();
		return gmdate( 'Y-m-d', $ts + $offset_seconds );
	}

	private function sum_order_totals( array $order_ids ): float {
		if ( empty( $order_ids ) || ! function_exists( 'wc_get_order' ) ) {
			return 0.0;
		}
		$sum = 0.0;
		foreach ( $order_ids as $oid ) {
			$oid = (int) $oid;
			if ( $oid <= 0 ) {
				continue;
			}
			$order = wc_get_order( $oid );
			if ( is_object( $order ) && method_exists( $order, 'get_total' ) ) {
				$sum += (float) $order->get_total();
			}
		}
		return round( $sum, 2 );
	}
}
