<?php
/**
 * Customers repository.
 *
 * @package Abientot
 */

namespace Abientot\Repositories;

use Abientot\Support\Db;

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

final class CustomersRepository extends BaseRepository {
	protected string $table_suffix = 'customers';

	/**
	 * Returns customers with aggregate booking stats for the Customers admin list.
	 *
	 * @param array<string,mixed> $filters List filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_for_admin( array $filters = array(), int $limit = 25, int $offset = 0 ): array {
		global $wpdb;

		$refunds  = $this->refunds_recorded();
		$limit    = max( 1, min( 6000, $limit ) );
		$offset   = max( 0, $offset );
		$currency = (string) \Abientot\Admin\Settings::values()['currency'];
		$after_id = isset( $filters['after_id'] ) ? max( 0, (int) $filters['after_id'] ) : 0;
		$search   = ! empty( $filters['search'] ) ? (string) $filters['search'] : '';
		$like     = '%' . $wpdb->esc_like( $search ) . '%';
		$id_order = ! empty( $filters['id_order'] ) ? 1 : 0;

		// Batched readers page by id (the primary key); the list shows recent changes first.
		if ( $refunds && $id_order ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-COALESCE(refunded_cents,0) ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.id > %d AND ( %s = '' OR c.display_name LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s )
				GROUP BY c.id
				ORDER BY c.id ASC
				LIMIT %d OFFSET %d",
					$currency,
					$currency,
					$after_id,
					$search,
					$like,
					$like,
					$like,
					$like,
					$like,
					$limit,
					$offset
				),
				ARRAY_A
			);
		} elseif ( $refunds ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-COALESCE(refunded_cents,0) ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.id > %d AND ( %s = '' OR c.display_name LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s )
				GROUP BY c.id
				ORDER BY c.updated_at DESC, c.id DESC
				LIMIT %d OFFSET %d",
					$currency,
					$currency,
					$after_id,
					$search,
					$like,
					$like,
					$like,
					$like,
					$like,
					$limit,
					$offset
				),
				ARRAY_A
			);
		} elseif ( $id_order ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-0 ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.id > %d AND ( %s = '' OR c.display_name LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s )
				GROUP BY c.id
				ORDER BY c.id ASC
				LIMIT %d OFFSET %d",
					$currency,
					$currency,
					$after_id,
					$search,
					$like,
					$like,
					$like,
					$like,
					$like,
					$limit,
					$offset
				),
				ARRAY_A
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-0 ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.id > %d AND ( %s = '' OR c.display_name LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s )
				GROUP BY c.id
				ORDER BY c.updated_at DESC, c.id DESC
				LIMIT %d OFFSET %d",
					$currency,
					$currency,
					$after_id,
					$search,
					$like,
					$like,
					$like,
					$like,
					$like,
					$limit,
					$offset
				),
				ARRAY_A
			);
		}

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

	/**
	 * True when the retained payment records carry refund amounts (read-only compatibility check).
	 */
	private function refunds_recorded(): bool {
		global $wpdb;

		( new PaymentPlanRepository() )->present();
		$columns = Db::col( $wpdb->suppress_errors( true ), $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $wpdb->prefix . 'abientot_payment_records' ) ), 'retained_capacity_storage_unavailable' );

		return in_array( 'refunded_cents', $columns, true );
	}

	/**
	 * Counts customers matching admin filters.
	 *
	 * @param array<string,mixed> $filters List filters.
	 */
	public function count_for_admin( array $filters = array() ): int {
		global $wpdb;

		$after_id = isset( $filters['after_id'] ) ? max( 0, (int) $filters['after_id'] ) : 0;
		$search   = ! empty( $filters['search'] ) ? (string) $filters['search'] : '';
		$like     = '%' . $wpdb->esc_like( $search ) . '%';

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}abientot_customers c WHERE c.id > %d AND ( %s = '' OR c.display_name LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s )",
				$after_id,
				$search,
				$like,
				$like,
				$like,
				$like,
				$like
			)
		);
	}

	/** Stream every matching export row in bounded, stable-ID batches. */
	public function iterate_for_export( array $filters = array() ): \Generator {
		$after = 0;
		do {
			$filters['after_id'] = $after;
			$filters['id_order'] = true;
			$rows                = $this->list_for_admin( $filters, 250 );
			foreach ( $rows as $row ) {
				$after = (int) $row['id'];
				yield $row;
			}
		} while ( count( $rows ) === 250 );
	}

	/**
	 * Returns aggregate booking stats for one customer.
	 *
	 * @return array<string,mixed>
	 */
	public function stats_for_customer( int $customer_id ): array {
		global $wpdb;

		if ( $customer_id <= 0 ) {
			return array();
		}

		$refunds  = $this->refunds_recorded();
		$currency = (string) \Abientot\Admin\Settings::values()['currency'];
		if ( $refunds ) {
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count
				FROM {$wpdb->prefix}abientot_appointments a
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-COALESCE(refunded_cents,0) ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE a.customer_id = %d",
					$currency,
					$currency,
					$customer_id
				),
				ARRAY_A
			);
		} else {
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count
				FROM {$wpdb->prefix}abientot_appointments a
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-0 ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE a.customer_id = %d",
					$currency,
					$currency,
					$customer_id
				),
				ARRAY_A
			);
		}

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

	/**
	 * Returns joined booking history rows for one customer.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function booking_history( int $customer_id, int $limit = 100, int $offset = 0 ): array {
		global $wpdb;

		if ( $customer_id <= 0 ) {
			return array();
		}

		$limit = max( 1, min( 500, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, st.slug AS status_slug, st.label AS status_label
				FROM {$wpdb->prefix}abientot_appointments a
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.customer_id = %d
				ORDER BY a.start_utc DESC, a.id DESC
				LIMIT %d OFFSET %d",
				$customer_id,
				$limit,
				max( 0, $offset )
			),
			ARRAY_A
		);

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

	/**
	 * Finds possible duplicate customers according to enabled identity fields.
	 *
	 * @param string[] $fields Enabled fields, accepting email and phone.
	 * @return array<int,array<string,mixed>>
	 */
	public function duplicate_candidates( array $customer, array $fields, int $limit = 20 ): array {
		global $wpdb;

		$limit = max( 1, min( 100, $limit ) );
		$email = in_array( 'email', $fields, true ) && ! empty( $customer['email'] ) ? (string) $customer['email'] : '';
		$phone = in_array( 'phone', $fields, true ) && ! empty( $customer['phone'] ) ? (string) $customer['phone'] : '';

		if ( '' === $email && '' === $phone ) {
			return array();
		}

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_customers WHERE ( ( %s <> '' AND email = %s ) OR ( %s <> '' AND phone = %s ) ) AND id <> %d ORDER BY updated_at DESC, id DESC LIMIT %d",
				$email,
				$email,
				$phone,
				$phone,
				(int) $customer['id'],
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Exports customer rows with aggregate stats.
	 *
	 * @param array<string,mixed> $filters List filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function export_for_admin( array $filters = array(), int $limit = 5000 ): array {
		return $this->list_for_admin( $filters, max( 1, min( 10000, $limit ) ), 0 );
	}

	/**
	 * Returns customer summary rows changed since a UTC timestamp.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function admin_changed_since( string $since_utc, int $limit = 1000, int $after_id = 0 ): array {
		global $wpdb;

		if ( '' === $since_utc ) {
			return array();
		}

		$refunds  = $this->refunds_recorded();
		$limit    = max( 1, min( 5000, $limit ) );
		$currency = (string) \Abientot\Admin\Settings::values()['currency'];
		if ( $refunds ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-COALESCE(refunded_cents,0) ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.updated_at >= %s AND c.id > %d
				GROUP BY c.id
				ORDER BY c.id ASC
				LIMIT %d",
					$currency,
					$currency,
					$since_utc,
					max( 0, $after_id ),
					$limit
				),
				ARRAY_A
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.*,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN a.currency = %s THEN COALESCE(cash.net_cents,0) ELSE 0 END), 0) AS total_spent_cents,
					COALESCE(AVG(CASE WHEN cash.collected_cents > 0 AND a.currency = %s THEN cash.net_cents ELSE NULL END), 0) AS average_spend_cents,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count,
					MAX(a.start_utc) AS last_booking_utc
				FROM {$wpdb->prefix}abientot_customers c
				LEFT JOIN {$wpdb->prefix}abientot_appointments a ON a.customer_id = c.id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN (SELECT appointment_id,currency,SUM(CASE WHEN status='paid' THEN amount_cents ELSE 0 END) AS collected_cents,SUM(CASE WHEN status='paid' THEN amount_cents-0 ELSE 0 END) AS net_cents FROM {$wpdb->prefix}abientot_payment_records GROUP BY appointment_id,currency) cash ON cash.appointment_id=a.id AND cash.currency=a.currency
				WHERE c.updated_at >= %s AND c.id > %d
				GROUP BY c.id
				ORDER BY c.id ASC
				LIMIT %d",
					$currency,
					$currency,
					$since_utc,
					max( 0, $after_id ),
					$limit
				),
				ARRAY_A
			);
		}

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

	public function appointment_count( int $customer_id ): int {
		global $wpdb;

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

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}abientot_appointments WHERE customer_id = %d", $customer_id )
		);
	}

	public function anonymize( int $customer_id ): bool {
		return ( new PrivacyRepository() )->erase_customer( $customer_id );
	}

	public function delete_if_no_appointments( int $customer_id ): bool {
		if ( $customer_id <= 0 || $this->appointment_count( $customer_id ) > 0 ) {
			return false;
		}

		return $this->delete( $customer_id );
	}

	public function find_by_email( string $email ): ?array {
		return $this->find_by( 'email', $email );
	}

	public function find_by_phone( string $phone ): ?array {
		return $this->find_by( 'phone', $phone );
	}

	/**
	 * Finds and ranks customers for the manual-booking identity autocomplete.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function autocomplete( string $query, string $field = '', int $limit = 8 ): array {
		global $wpdb;

		$query = trim( sanitize_text_field( $query ) );
		$field = in_array( $field, array( 'first_name', 'last_name', 'email', 'phone' ), true ) ? $field : '';
		$limit = max( 1, min( 12, $limit ) );
		if ( strlen( $query ) < 2 ) {
			return array();
		}

		$like       = '%' . $wpdb->esc_like( $query ) . '%';
		$digits     = preg_replace( '/\D+/', '', $query );
		$digit_flag = '';
		$digit_like = '';
		if ( is_string( $digits ) && strlen( $digits ) >= 2 && $digits !== $query ) {
			$digit_flag = $digits;
			$digit_like = '%' . $wpdb->esc_like( $digits ) . '%';
		}
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, first_name, last_name, display_name, email, phone, updated_at FROM {$wpdb->prefix}abientot_customers WHERE (display_name LIKE %s OR first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR phone LIKE %s) OR ( %s <> '' AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '(', ''), ')', ''), '+', '') LIKE %s ) ORDER BY updated_at DESC, id DESC LIMIT %d",
				$like,
				$like,
				$like,
				$like,
				$like,
				$digit_flag,
				$digit_like,
				100
			),
			ARRAY_A
		);
		if ( ! is_array( $rows ) ) {
			return array();
		}

		$lower         = static fn ( string $value ): string => function_exists( 'mb_strtolower' ) ? mb_strtolower( $value ) : strtolower( $value );
		$needle        = $lower( $query );
		$needle_digits = is_string( $digits ) ? $digits : '';
		foreach ( $rows as &$row ) {
			$score = 0;
			foreach ( array( 'first_name', 'last_name', 'email', 'phone', 'display_name' ) as $key ) {
				$value      = $lower( trim( (string) ( $row[ $key ] ?? '' ) ) );
				$comparable = 'phone' === $key ? (string) preg_replace( '/\D+/', '', $value ) : $value;
				$wanted     = 'phone' === $key && '' !== $needle_digits ? $needle_digits : $needle;
				if ( '' === $value || '' === $wanted ) {
					continue;
				}
				if ( $comparable === $wanted ) {
					$score += 120;
				} elseif ( 0 === strpos( $comparable, $wanted ) ) {
					$score += 70;
				} elseif ( false !== strpos( $comparable, $wanted ) ) {
					$score += 35;
				}
				if ( $key === $field ) {
					$score *= 2;
				}
			}
			$row['_score'] = $score;
		}
		unset( $row );
		usort( $rows, static fn ( array $a, array $b ): int => (int) $b['_score'] <=> (int) $a['_score'] );

		return array_slice( $rows, 0, $limit );
	}

	/**
	 * Finds an existing customer by configured identity fields or creates one.
	 *
	 * @param array<string,mixed> $data Customer data.
	 */
	public function find_or_create_for_booking( array $data, bool $update_existing = true ): int {
		$settings = get_option( 'abientot_settings', array() );
		$email    = isset( $data['email'] ) ? sanitize_email( (string) $data['email'] ) : '';
		$phone    = isset( $data['phone'] ) ? sanitize_text_field( (string) $data['phone'] ) : '';
		$existing = null;

		// Public submissions cannot establish ownership through a shared phone number.
		if ( ! $update_existing ) {
			global $wpdb;
			if ( '' !== $email && is_array( $settings ) && ! empty( $settings['match_customer_by_email'] ) ) {
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}abientot_customers WHERE LOWER(email) = %s ORDER BY id LIMIT 2", strtolower( $email ) ), ARRAY_A );
				if ( $wpdb->last_error || ! is_array( $rows ) ) {
					throw new \RuntimeException( 'Customer identity lookup failed.' );
				}
				if ( count( $rows ) === 1 ) {
					return (int) $rows[0]['id'];
				}
			}
			// Ambiguous email or phone-only submissions receive a separate profile.
			$settings = array();
		}

		if ( is_array( $settings ) && ! empty( $settings['match_customer_by_email'] ) && '' !== $email ) {
			$existing = $this->find_by_email( $email );
		}

		if ( ! $existing && is_array( $settings ) && ! empty( $settings['match_customer_by_phone'] ) && '' !== $phone ) {
			$existing = $this->find_by_phone( $phone );
		}

		$now = gmdate( 'Y-m-d H:i:s' );

		if ( $existing ) {
			if ( ! $update_existing ) {
				return (int) $existing['id'];
			}
			$this->update(
				(int) $existing['id'],
				array(
					'first_name'   => (string) $data['first_name'],
					'last_name'    => (string) $data['last_name'],
					'display_name' => (string) $data['display_name'],
					'email'        => $email,
					'phone'        => $phone,
					'updated_at'   => $now,
				)
			);

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

		$created_id = $this->insert(
			array(
				'first_name'       => (string) $data['first_name'],
				'last_name'        => (string) $data['last_name'],
				'display_name'     => (string) $data['display_name'],
				'email'            => $email,
				'phone'            => $phone,
				'notes'            => '',
				'marketing_opt_in' => 0,
				'created_at'       => $now,
				'updated_at'       => $now,
			)
		);
		global $wpdb;
		if ( $created_id <= 0 || $wpdb->last_error ) {
			throw new \RuntimeException( 'Customer creation failed.' );
		}
		return $created_id;
	}
}