<?php
/**
 * Lead repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Leads;

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

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

/**
 * Reads and writes lead records.
 */
class LeadRepository {
	const MAX_BULK_IDS = 100;

	const STATUS_NEW      = 'new';
	const STATUS_SEEN     = 'seen';
	const STATUS_ARCHIVED = 'archived';

	const EMAIL_STATUS_SENT               = 'sent';
	const EMAIL_STATUS_FAILED             = 'failed';
	const EMAIL_STATUS_NO_EMAIL_GENERATED = 'no_email_generated';
	const EMAIL_STATUS_UNKNOWN            = 'unknown';

	const AUTO_REPLY_STATUS_NOT_CONFIGURED     = 'not_configured';
	const AUTO_REPLY_STATUS_SCHEDULED          = 'scheduled';
	const AUTO_REPLY_STATUS_SENT               = 'sent';
	const AUTO_REPLY_STATUS_FAILED             = 'failed';
	const AUTO_REPLY_STATUS_NO_EMAIL_GENERATED = 'no_email_generated';
	const AUTO_REPLY_STATUS_UNKNOWN            = 'unknown';

	const WORKFLOW_STATUS_OPEN      = 'open';
	const WORKFLOW_STATUS_CONTACTED = 'contacted';
	const WORKFLOW_STATUS_QUALIFIED = 'qualified';
	const WORKFLOW_STATUS_WON       = 'won';
	const WORKFLOW_STATUS_LOST      = 'lost';

	const PRIORITY_LOW    = 'low';
	const PRIORITY_NORMAL = 'normal';
	const PRIORITY_HIGH   = 'high';
	const PRIORITY_URGENT = 'urgent';

	const SORT_NEWEST           = 'newest';
	const SORT_OLDEST           = 'oldest';
	const SORT_RECENTLY_UPDATED = 'recently_updated';
	const SORT_FOLLOW_UP        = 'follow_up';
	const SORT_PRIORITY         = 'priority';

	const QUEUE_UNSEEN          = 'unseen';
	const QUEUE_AWAITING_ACTION = 'awaiting_action';
	const QUEUE_UNASSIGNED      = 'unassigned';
	const QUEUE_MY_LEADS        = 'my_leads';
	const QUEUE_DUE_TODAY       = 'due_today';
	const QUEUE_OVERDUE         = 'overdue';
	const QUEUE_DELIVERY_ISSUES = 'delivery_issues';
	const QUEUE_ARCHIVED        = 'archived';

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

		$table = $wpdb->prefix . 'subin_leads';
		$now   = current_time( 'mysql' );

		$inserted = $wpdb->insert(
			$table,
			array(
				'provider'        => sanitize_text_field( $data['provider'] ?? '' ),
				'form_id'         => sanitize_text_field( $data['form_id'] ?? '' ),
				'form_name'       => sanitize_text_field( $data['form_name'] ?? '' ),
				'source_url'      => esc_url_raw( $data['source_url'] ?? '' ),
				'referrer'        => esc_url_raw( $data['referrer'] ?? '' ),
				'customer_name'   => sanitize_text_field( $data['customer_name'] ?? '' ),
				'customer_email'  => sanitize_email( $data['customer_email'] ?? '' ),
				'customer_phone'  => sanitize_text_field( $data['customer_phone'] ?? '' ),
				'message_preview' => sanitize_textarea_field( $data['message_preview'] ?? '' ),
				'status'          => self::STATUS_NEW,
				'email_status'    => self::EMAIL_STATUS_UNKNOWN,
				'auto_reply_status' => self::AUTO_REPLY_STATUS_NOT_CONFIGURED,
				'workflow_status' => self::WORKFLOW_STATUS_OPEN,
				'assigned_user_id' => ! empty( $data['assigned_user_id'] ) ? absint( $data['assigned_user_id'] ) : null,
				'follow_up_at'    => ! empty( $data['follow_up_at'] ) ? sanitize_text_field( $data['follow_up_at'] ) : null,
				'priority'        => self::PRIORITY_NORMAL,
				'last_activity_at' => $now,
				'submitted_at'    => sanitize_text_field( $data['submitted_at'] ?? $now ),
				'expires_at'      => ! empty( $data['expires_at'] ) ? sanitize_text_field( $data['expires_at'] ) : null,
				'created_at'      => $now,
				'updated_at'      => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

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

	/**
	 * Update lead email status.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Email status.
	 * @return bool
	 */
	public function update_email_status( $lead_id, $status ) {
		$status = sanitize_key( (string) $status );
		if ( ! in_array( $status, $this->get_email_statuses(), true ) ) {
			return false;
		}

		return $this->advance_mail_status(
			$lead_id,
			'email_status',
			'email_status_version',
			$status,
			$this->email_status_predecessors( $status )
		);
	}

	/**
	 * Read the admin notification projection and its CAS generation.
	 *
	 * @param int $lead_id Lead id.
	 * @return array{status:string,version:int}|\WP_Error
	 */
	public function get_email_status_projection( $lead_id ) {
		return $this->get_mail_status_projection( $lead_id, 'email_status', 'email_status_version', 'email' );
	}

	/**
	 * Persist an Email Log-derived admin projection only if its generation is current.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Derived status.
	 * @param mixed  $expected_version Projection generation read before derivation.
	 * @return true|\WP_Error
	 */
	public function update_email_status_if_current( $lead_id, $status, $expected_version ) {
		$status = sanitize_key( (string) $status );
		if ( ! in_array( $status, $this->get_email_statuses(), true ) ) {
			return new \WP_Error( 'subin_email_projection_status_invalid', __( 'The email delivery status is invalid.', 'submission-inbox' ) );
		}

		return $this->persist_mail_status_if_current(
			$lead_id,
			'email_status',
			'email_status_version',
			$status,
			$expected_version,
			'email'
		);
	}

	/**
	 * Mark a durable admin retry as pending without overwriting a newer outcome.
	 *
	 * @param int   $lead_id Lead id.
	 * @param mixed $expected_version Projection generation rendered before retry.
	 * @return true|\WP_Error
	 */
	public function mark_email_retry_pending_if_current( $lead_id, $expected_version ) {
		return $this->persist_mail_status_if_current(
			$lead_id,
			'email_status',
			'email_status_version',
			self::EMAIL_STATUS_UNKNOWN,
			$expected_version,
			'email'
		);
	}

	/**
	 * Update the visitor auto-reply projection without changing admin delivery.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Auto-reply status.
	 * @return bool
	 */
	public function update_auto_reply_status( $lead_id, $status ) {
		$status = sanitize_key( (string) $status );
		if ( ! in_array( $status, $this->get_auto_reply_statuses(), true ) ) {
			return false;
		}

		return $this->advance_mail_status(
			$lead_id,
			'auto_reply_status',
			'auto_reply_status_version',
			$status,
			$this->auto_reply_status_predecessors( $status )
		);
	}

	/**
	 * Read the visitor auto-reply projection and its CAS generation.
	 *
	 * @param int $lead_id Lead id.
	 * @return array{status:string,version:int}|\WP_Error
	 */
	public function get_auto_reply_status_projection( $lead_id ) {
		return $this->get_mail_status_projection( $lead_id, 'auto_reply_status', 'auto_reply_status_version', 'auto_reply' );
	}

	/**
	 * Persist an Email Log-derived visitor projection only if its generation is current.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Derived status.
	 * @param mixed  $expected_version Projection generation read before derivation.
	 * @return true|\WP_Error
	 */
	public function update_auto_reply_status_if_current( $lead_id, $status, $expected_version ) {
		$status = sanitize_key( (string) $status );
		if ( ! in_array( $status, $this->get_auto_reply_statuses(), true ) ) {
			return new \WP_Error( 'subin_auto_reply_projection_status_invalid', __( 'The auto-reply delivery status is invalid.', 'submission-inbox' ) );
		}

		return $this->persist_mail_status_if_current(
			$lead_id,
			'auto_reply_status',
			'auto_reply_status_version',
			$status,
			$expected_version,
			'auto_reply'
		);
	}

	/**
	 * Update lead status.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Lead status.
	 * @return bool
	 */
	public function update_status( $lead_id, $status ) {
		$result = $this->persist_status( $lead_id, $status, null );

		return true === $result;
	}

	/**
	 * Update visibility only when the editor still has the current status version.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status New status.
	 * @param mixed  $expected_version Version rendered with the action.
	 * @return true|\WP_Error
	 */
	public function update_status_if_current( $lead_id, $status, $expected_version ) {
		if ( ! is_scalar( $expected_version ) || 1 !== preg_match( '/^[1-9]\d*$/', (string) $expected_version ) ) {
			return new \WP_Error(
				'subin_status_version_invalid',
				__( 'The lead status version is missing or invalid. Refresh the lead and try again.', 'submission-inbox' )
			);
		}

		return $this->persist_status( $lead_id, $status, absint( $expected_version ) );
	}

	/**
	 * Update every lead with one status to another status.
	 *
	 * @param string $from_status Current status.
	 * @param string $to_status New status.
	 * @return int|false Number of affected rows or false on failure.
	 */
	public function update_status_bulk( $from_status, $to_status ) {
		return $this->update_status_bulk_through_id( $from_status, $to_status, PHP_INT_MAX );
	}

	/**
	 * Update matching leads that existed when a global action was rendered.
	 *
	 * @param string $from_status Current status.
	 * @param string $to_status New status.
	 * @param int    $max_lead_id Highest lead id visible to the action.
	 * @return int|false Number of affected rows or false on failure.
	 */
	public function update_status_bulk_through_id( $from_status, $to_status, $max_lead_id ) {
		global $wpdb;

		$from_status = sanitize_key( (string) $from_status );
		$to_status   = sanitize_key( (string) $to_status );
		$max_lead_id = absint( $max_lead_id );
		if ( $max_lead_id <= 0 || ! in_array( $from_status, $this->get_statuses(), true ) || ! in_array( $to_status, $this->get_statuses(), true ) ) {
			return false;
		}
		if ( $from_status === $to_status ) {
			return 0;
		}

		$table = $wpdb->prefix . 'subin_leads';
		$sql   = "UPDATE {$table}
			SET status = %s, updated_at = %s, status_version = status_version + 1
			WHERE status = %s AND id <= %d";

		return $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Values use placeholders; the table name comes only from the trusted WordPress prefix.
			$wpdb->prepare( $sql, $to_status, current_time( 'mysql' ), $from_status, $max_lead_id )
		); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Update the visibility status of a bounded set of leads atomically.
	 *
	 * @param array<int,int|string> $lead_ids Lead ids.
	 * @param string                $status New status.
	 * @return int|false Number of changed rows or false on failure.
	 */
	public function update_statuses( array $lead_ids, $status ) {
		global $wpdb;

		$lead_ids = $this->normalize_lead_ids( $lead_ids );
		$status   = sanitize_key( (string) $status );
		if ( empty( $lead_ids ) || ! in_array( $status, $this->get_statuses(), true ) ) {
			return false;
		}

		$table        = $wpdb->prefix . 'subin_leads';
		$placeholders = implode( ', ', array_fill( 0, count( $lead_ids ), '%d' ) );
		$sql          = "UPDATE {$table} SET status = %s, updated_at = %s, status_version = status_version + 1 WHERE id IN ({$placeholders}) AND status <> %s";
		$params       = array_merge( array( $status, current_time( 'mysql' ) ), $lead_ids, array( $status ) );

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

	/**
	 * Load a bounded set of leads keyed by id.
	 *
	 * @param array<int,int|string> $lead_ids Lead ids.
	 * @return array<int,object>
	 */
	public function get_by_ids( array $lead_ids ) {
		global $wpdb;

		$lead_ids = $this->normalize_lead_ids( $lead_ids );
		if ( empty( $lead_ids ) ) {
			return array();
		}

		$table        = $wpdb->prefix . 'subin_leads';
		$placeholders = implode( ', ', array_fill( 0, count( $lead_ids ), '%d' ) );
		$rows         = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id IN ({$placeholders})", $lead_ids ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		);
		$leads        = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$lead_id = absint( $row->id ?? 0 );
			if ( $lead_id > 0 ) {
				$leads[ $lead_id ] = $row;
			}
		}

		return $leads;
	}

	/**
	 * Get a lead by id.
	 *
	 * @param int $lead_id Lead id.
	 * @return object|null
	 */
	public function get( $lead_id ) {
		global $wpdb;

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

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

	/**
	 * Find a lead by provider and an external entry id stored in lead fields.
	 *
	 * @param string $provider Provider key.
	 * @param string $external_entry_id External entry/submission id.
	 * @param string $field_key Metadata field key.
	 * @return object|null
	 */
	public function find_by_provider_external_entry_id( $provider, $external_entry_id, $field_key ) {
		global $wpdb;

		$provider          = sanitize_key( $provider );
		$external_entry_id = sanitize_text_field( $external_entry_id );
		$field_key         = sanitize_key( $field_key );

		if ( '' === $provider || '' === $external_entry_id || '' === $field_key ) {
			return null;
		}

		$leads_table  = $wpdb->prefix . 'subin_leads';
		$fields_table = $wpdb->prefix . 'subin_lead_fields';

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT leads.*
				FROM {$leads_table} leads
				INNER JOIN {$fields_table} fields ON fields.lead_id = leads.id
				WHERE leads.provider = %s
					AND fields.field_key = %s
					AND fields.field_value = %s
				ORDER BY leads.id DESC
				LIMIT 1",
				$provider,
				$field_key,
				$external_entry_id
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get paginated leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_paginated( array $args ) {
		return $this->get_paginated_leads( $args );
	}

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

		$table    = $wpdb->prefix . 'subin_leads';
		$where    = $this->build_where( $args );
		$limit    = max( 1, absint( $args['per_page'] ?? 20 ) );
		$offset   = max( 0, absint( $args['offset'] ?? 0 ) );
		$order_by = $this->order_by_clause( $args['sort'] ?? self::SORT_NEWEST );
		$sql      = "SELECT * FROM {$table} {$where['sql']} ORDER BY {$order_by} LIMIT %d OFFSET %d";
		$params   = array_merge( $where['params'], array( $limit, $offset ) );

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

	/**
	 * Count leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return int
	 */
	public function count( array $args = array() ) {
		return $this->count_leads( $args );
	}

	/**
	 * Count leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return int
	 */
	public function count_leads( array $args = array() ) {
		global $wpdb;

		$table   = $wpdb->prefix . 'subin_leads';
		$where   = $this->build_where( $args );
		$sql     = "SELECT COUNT(*) FROM {$table} {$where['sql']}";

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

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

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

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

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

	/**
	 * Get form choices for inbox filters.
	 *
	 * @return array<int,array{provider:string,form_id:string,form_name:string}>
	 */
	public function get_form_filter_options() {
		global $wpdb;

		$forms_table = $wpdb->prefix . 'subin_forms';
		$leads_table = $wpdb->prefix . 'subin_leads';
		$options     = array();
		$seen        = array();

		$built_in_forms = $wpdb->get_results( "SELECT id, title FROM {$forms_table} ORDER BY title ASC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		foreach ( $built_in_forms as $form ) {
			$form_id = (string) absint( $form->id );
			$key     = 'submission_inbox_form|' . $form_id;

			$seen[ $key ] = true;
			$options[]    = array(
				'provider'  => 'submission_inbox_form',
				'form_id'   => $form_id,
				'form_name' => sanitize_text_field( $form->title ),
			);
		}

		$lead_forms = $wpdb->get_results(
			"SELECT provider, form_id, MAX(form_name) AS form_name
			FROM {$leads_table}
			WHERE form_id IS NOT NULL AND form_id != ''
			GROUP BY provider, form_id
			ORDER BY form_name ASC"
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		foreach ( $lead_forms as $form ) {
			$provider = sanitize_key( $form->provider );
			$form_id  = sanitize_text_field( $form->form_id );
			$key      = $provider . '|' . $form_id;

			if ( isset( $seen[ $key ] ) ) {
				continue;
			}

			$seen[ $key ] = true;
			$options[]    = array(
				'provider'  => $provider,
				'form_id'   => $form_id,
				'form_name' => sanitize_text_field( $form->form_name ),
			);
		}

		return $options;
	}

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

		$table    = $wpdb->prefix . 'subin_leads';
		$where    = $this->build_where( $filters );
		$limit    = max( 1, absint( $limit ) );
		$order_by = $this->order_by_clause( $filters['sort'] ?? self::SORT_NEWEST );
		$sql      = "SELECT * FROM {$table} {$where['sql']} ORDER BY {$order_by} LIMIT %d";
		$params   = array_merge( $where['params'], array( $limit ) );

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

	/**
	 * Get summary counts.
	 *
	 * @return array<string,int>
	 */
	public function get_summary_counts() {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';
		$row   = $wpdb->get_row(
			"SELECT
				COUNT(*) AS total,
				COALESCE(SUM(status = 'new'), 0) AS new_count,
				COALESCE(SUM(email_status = 'failed'), 0) AS failed_count,
				COALESCE(SUM(email_status = 'no_email_generated'), 0) AS no_email_generated_count,
				COALESCE(SUM(email_status = 'unknown'), 0) AS unknown_count,
				COALESCE(SUM(auto_reply_status = 'failed'), 0) AS auto_reply_failed_count,
				COALESCE(SUM(auto_reply_status = 'no_email_generated'), 0) AS auto_reply_no_email_generated_count,
				COALESCE(SUM(auto_reply_status = 'scheduled'), 0) AS auto_reply_scheduled_count
			FROM {$table}"
		);

		return array(
			'total'              => absint( $row->total ?? 0 ),
			'new'                => absint( $row->new_count ?? 0 ),
			'failed'             => absint( $row->failed_count ?? 0 ),
			'no_email_generated' => absint( $row->no_email_generated_count ?? 0 ),
			'unknown'            => absint( $row->unknown_count ?? 0 ),
			'auto_reply_failed'   => absint( $row->auto_reply_failed_count ?? 0 ),
			'auto_reply_no_email_generated' => absint( $row->auto_reply_no_email_generated_count ?? 0 ),
			'auto_reply_scheduled' => absint( $row->auto_reply_scheduled_count ?? 0 ),
		);
	}

	/**
	 * Delete a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return bool
	 */
	public function delete( $lead_id ) {
		global $wpdb;

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

		return false !== $wpdb->delete( $table, array( 'id' => absint( $lead_id ) ), array( '%d' ) );
	}

	/**
	 * Get expired lead ids.
	 *
	 * @param int $limit Limit.
	 * @return array<int,int>
	 */
	public function get_expired_lead_ids( $limit = 500 ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';
		$now   = current_time( 'mysql' );

		return array_map(
			'absint',
			$wpdb->get_col(
				$wpdb->prepare(
					"SELECT id FROM {$table} WHERE expires_at IS NOT NULL AND expires_at != '' AND expires_at < %s ORDER BY expires_at ASC LIMIT %d",
					$now,
					max( 1, absint( $limit ) )
				) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			)
		);
	}

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

		$leads_table  = $wpdb->prefix . 'subin_leads';
		$fields_table = $wpdb->prefix . 'subin_lead_fields';
		$where        = array();
		$params       = array();
		$queue        = sanitize_key( (string) ( $args['queue'] ?? '' ) );

		if ( in_array( $queue, $this->get_queues(), true ) ) {
			$today          = \DateTimeImmutable::createFromFormat( '!Y-m-d', current_time( 'Y-m-d' ), wp_timezone() );
			$today          = $today ? $today : new \DateTimeImmutable( 'today', wp_timezone() );
			$today_start    = $today->format( 'Y-m-d H:i:s' );
			$tomorrow_start = $today->modify( '+1 day' )->format( 'Y-m-d H:i:s' );

			switch ( $queue ) {
				case self::QUEUE_UNSEEN:
					$where[]  = 'status = %s';
					$params[] = self::STATUS_NEW;
					break;
				case self::QUEUE_AWAITING_ACTION:
					$where[] = "status != 'archived' AND workflow_status = 'open'";
					break;
				case self::QUEUE_UNASSIGNED:
					$where[] = "status != 'archived' AND workflow_status NOT IN ('won', 'lost') AND (assigned_user_id IS NULL OR assigned_user_id = 0)";
					break;
				case self::QUEUE_MY_LEADS:
					$queue_user_id = absint( $args['queue_user_id'] ?? 0 );
					if ( $queue_user_id <= 0 ) {
						$where[] = '1 = 0';
						break;
					}
					$where[]  = "status != 'archived' AND workflow_status NOT IN ('won', 'lost') AND assigned_user_id = %d";
					$params[] = $queue_user_id;
					break;
				case self::QUEUE_DUE_TODAY:
					$where[]  = "status != 'archived' AND workflow_status NOT IN ('won', 'lost') AND follow_up_at IS NOT NULL AND follow_up_at != '' AND follow_up_at >= %s AND follow_up_at < %s";
					$params[] = $today_start;
					$params[] = $tomorrow_start;
					break;
				case self::QUEUE_OVERDUE:
					$where[]  = "status != 'archived' AND workflow_status NOT IN ('won', 'lost') AND follow_up_at IS NOT NULL AND follow_up_at != '' AND follow_up_at < %s";
					$params[] = $today_start;
					break;
				case self::QUEUE_DELIVERY_ISSUES:
					$where[] = "status != 'archived' AND workflow_status NOT IN ('won', 'lost') AND (email_status IN ('failed', 'no_email_generated') OR auto_reply_status IN ('failed', 'no_email_generated'))";
					break;
				case self::QUEUE_ARCHIVED:
					$where[]  = 'status = %s';
					$params[] = self::STATUS_ARCHIVED;
					break;
			}
		}

		if ( ! empty( $args['status'] ) && in_array( $args['status'], $this->get_statuses(), true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( ! empty( $args['email_status'] ) && in_array( $args['email_status'], $this->get_email_statuses(), true ) ) {
			$where[]  = 'email_status = %s';
			$params[] = $args['email_status'];
		}

		if ( ! empty( $args['auto_reply_status'] ) && in_array( $args['auto_reply_status'], $this->get_auto_reply_statuses(), true ) ) {
			$where[]  = 'auto_reply_status = %s';
			$params[] = $args['auto_reply_status'];
		}

		if ( ! empty( $args['needs_attention'] ) ) {
			$where[] = "(email_status IN ('failed', 'no_email_generated') OR auto_reply_status IN ('failed', 'no_email_generated'))";
		}

		if ( ! empty( $args['provider'] ) && in_array( $args['provider'], $this->get_providers(), true ) ) {
			$where[]  = 'provider = %s';
			$params[] = $args['provider'];
		}

		if ( ! empty( $args['form_provider'] ) && in_array( $args['form_provider'], $this->get_providers(), true ) ) {
			$where[]  = 'provider = %s';
			$params[] = $args['form_provider'];
		}

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

		if ( ! empty( $args['form'] ) ) {
			$where[]  = 'form_name = %s';
			$params[] = sanitize_text_field( $args['form'] );
		}

		if ( ! empty( $args['submitted_after'] ) ) {
			$where[]  = 'submitted_at >= %s';
			$params[] = sanitize_text_field( $args['submitted_after'] );
		}

		if ( ! empty( $args['workflow_status'] ) && in_array( $args['workflow_status'], $this->get_workflow_statuses(), true ) ) {
			$where[]  = 'workflow_status = %s';
			$params[] = $args['workflow_status'];
		}

		if ( ! empty( $args['priority'] ) && in_array( $args['priority'], $this->get_priorities(), true ) ) {
			$where[]  = 'priority = %s';
			$params[] = $args['priority'];
		}

		if ( isset( $args['assigned_user_id'] ) && '' !== (string) $args['assigned_user_id'] ) {
			$assigned_user_id = absint( $args['assigned_user_id'] );
			if ( $assigned_user_id > 0 ) {
				$where[]  = 'assigned_user_id = %d';
				$params[] = $assigned_user_id;
			} elseif ( '0' === (string) $args['assigned_user_id'] ) {
				$where[] = '(assigned_user_id IS NULL OR assigned_user_id = 0)';
			}
		}

		if ( ! empty( $args['overdue_follow_up'] ) ) {
			$where[]  = "workflow_status NOT IN ('won', 'lost') AND follow_up_at IS NOT NULL AND follow_up_at != '' AND follow_up_at < %s";
			$params[] = current_time( 'Y-m-d' ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'submitted_at >= %s';
			$params[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'submitted_at <= %s';
			$params[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		if ( ! empty( $args['search'] ) ) {
			$search   = trim( sanitize_text_field( $args['search'] ) );
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]  = "(
				customer_email LIKE %s
				OR customer_name LIKE %s
				OR customer_phone LIKE %s
				OR form_name LIKE %s
				OR message_preview LIKE %s
				OR CAST({$leads_table}.id AS CHAR) = %s
				OR EXISTS (
					SELECT 1 FROM {$fields_table} search_fields
					WHERE search_fields.lead_id = {$leads_table}.id
						AND search_fields.is_sensitive = 0
						AND search_fields.field_value LIKE %s
				)
			)";
			$params   = array_merge( $params, array( $like, $like, $like, $like, $like, $search, $like ) );
		}

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

	/**
	 * Build a deterministic ORDER BY clause from an allowlist.
	 *
	 * @param string $sort Sort key.
	 * @return string
	 */
	private function order_by_clause( $sort ) {
		switch ( sanitize_key( (string) $sort ) ) {
			case self::SORT_OLDEST:
				return 'submitted_at ASC, id ASC';
			case self::SORT_RECENTLY_UPDATED:
				return 'updated_at DESC, id DESC';
			case self::SORT_FOLLOW_UP:
				return 'follow_up_at IS NULL ASC, follow_up_at ASC, id DESC';
			case self::SORT_PRIORITY:
				return "CASE priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 WHEN 'normal' THEN 3 WHEN 'low' THEN 4 ELSE 5 END ASC, submitted_at DESC, id DESC";
			case self::SORT_NEWEST:
			default:
				return 'submitted_at DESC, id DESC';
		}
	}

	/**
	 * Validate and atomically persist one visibility-status mutation.
	 *
	 * @param int      $lead_id Lead id.
	 * @param string   $status New status.
	 * @param int|null $expected_version Expected status version, or null for legacy callers.
	 * @return true|\WP_Error
	 */
	private function persist_status( $lead_id, $status, $expected_version ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		$status  = sanitize_key( (string) $status );
		if ( $lead_id <= 0 ) {
			return new \WP_Error( 'subin_status_lead_invalid', __( 'A valid lead is required.', 'submission-inbox' ) );
		}
		if ( ! in_array( $status, $this->get_statuses(), true ) ) {
			return new \WP_Error( 'subin_status_value_invalid', __( 'The lead status is invalid.', 'submission-inbox' ) );
		}

		$table   = $wpdb->prefix . 'subin_leads';
		$current = $wpdb->get_row(
			$wpdb->prepare( "SELECT status, status_version FROM {$table} WHERE id = %d LIMIT 1", $lead_id )
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( ! $current ) {
			return new \WP_Error( 'subin_status_lead_missing', __( 'The lead no longer exists.', 'submission-inbox' ) );
		}

		$current_version = max( 1, absint( $current->status_version ?? 1 ) );
		if ( null !== $expected_version && $expected_version !== $current_version ) {
			return $this->status_conflict_error( $current_version );
		}
		if ( $status === sanitize_key( (string) ( $current->status ?? '' ) ) ) {
			return true;
		}

		$result = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = %s, updated_at = %s, status_version = status_version + 1
				WHERE id = %d AND status_version = %d",
				$status,
				current_time( 'mysql' ),
				$lead_id,
				$current_version
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( false === $result ) {
			return new \WP_Error( 'subin_status_update_failed', __( 'The lead status could not be saved.', 'submission-inbox' ) );
		}
		if ( 0 === $result ) {
			$latest = $wpdb->get_row(
				$wpdb->prepare( "SELECT status_version FROM {$table} WHERE id = %d LIMIT 1", $lead_id )
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! $latest ) {
				return new \WP_Error( 'subin_status_lead_missing', __( 'The lead no longer exists.', 'submission-inbox' ) );
			}

			return $this->status_conflict_error( max( 1, absint( $latest->status_version ?? 1 ) ) );
		}

		return true;
	}

	/**
	 * Build a stable visibility-status conflict.
	 *
	 * @param int $current_version Latest status version.
	 * @return \WP_Error
	 */
	private function status_conflict_error( $current_version ) {
		return new \WP_Error(
			'subin_status_conflict',
			__( 'This lead changed after the status action was rendered.', 'submission-inbox' ),
			array( 'current_version' => max( 1, absint( $current_version ) ) )
		);
	}

	/**
	 * Normalize a bounded list of unique positive lead ids.
	 *
	 * @param array<int,int|string> $lead_ids Lead ids.
	 * @return array<int,int>
	 */
	private function normalize_lead_ids( array $lead_ids ) {
		$normalized = array();

		foreach ( $lead_ids as $lead_id ) {
			if ( ! is_scalar( $lead_id ) ) {
				continue;
			}
			$lead_id = absint( $lead_id );
			if ( $lead_id <= 0 || in_array( $lead_id, $normalized, true ) ) {
				continue;
			}

			$normalized[] = $lead_id;
			if ( count( $normalized ) >= self::MAX_BULK_IDS ) {
				break;
			}
		}

		return $normalized;
	}

	/**
	 * Read one delivery projection without exposing dynamic database fields.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status_field Allowlisted status field.
	 * @param string $version_field Allowlisted version field.
	 * @param string $error_prefix Error namespace.
	 * @return array{status:string,version:int}|\WP_Error
	 */
	private function get_mail_status_projection( $lead_id, $status_field, $version_field, $error_prefix ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 ) {
			return new \WP_Error( 'subin_' . $error_prefix . '_projection_lead_invalid', __( 'The lead delivery status could not be read.', 'submission-inbox' ) );
		}

		$table = $wpdb->prefix . 'subin_leads';
		$wpdb->last_error = '';
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT {$status_field} AS status, {$version_field} AS version FROM {$table} WHERE id = %d LIMIT 1",
				$lead_id
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error(
				'subin_' . $error_prefix . '_projection_read_failed',
				__( 'The lead delivery status could not be read.', 'submission-inbox' ),
				array( 'db_error' => sanitize_text_field( (string) $wpdb->last_error ) )
			);
		}
		if ( ! $row ) {
			return new \WP_Error( 'subin_' . $error_prefix . '_projection_lead_missing', __( 'The lead no longer exists.', 'submission-inbox' ) );
		}

		return array(
			'status'  => sanitize_key( (string) ( $row->status ?? '' ) ),
			'version' => max( 1, absint( $row->version ?? 1 ) ),
		);
	}

	/**
	 * Compare-and-set an Email Log-derived projection.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status_field Allowlisted status field.
	 * @param string $version_field Allowlisted version field.
	 * @param string $status New derived status.
	 * @param mixed  $expected_version Expected generation.
	 * @param string $error_prefix Error namespace.
	 * @return true|\WP_Error
	 */
	private function persist_mail_status_if_current( $lead_id, $status_field, $version_field, $status, $expected_version, $error_prefix ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 || ! is_scalar( $expected_version ) || 1 !== preg_match( '/^[1-9]\d*$/', (string) $expected_version ) ) {
			return new \WP_Error(
				'subin_' . $error_prefix . '_projection_version_invalid',
				__( 'The lead delivery status changed. Refresh it and try again.', 'submission-inbox' )
			);
		}

		$table   = $wpdb->prefix . 'subin_leads';
		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET {$status_field} = %s, {$version_field} = {$version_field} + 1, updated_at = %s
				WHERE id = %d AND {$version_field} = %d AND {$status_field} <> %s",
				$status,
				current_time( 'mysql' ),
				$lead_id,
				absint( $expected_version ),
				$status
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( false === $updated ) {
			return new \WP_Error(
				'subin_' . $error_prefix . '_projection_update_failed',
				__( 'The lead delivery status could not be updated.', 'submission-inbox' ),
				array( 'db_error' => sanitize_text_field( (string) $wpdb->last_error ) )
			);
		}
		if ( $updated > 0 ) {
			return true;
		}

		$current = $this->get_mail_status_projection( $lead_id, $status_field, $version_field, $error_prefix );
		if ( is_wp_error( $current ) ) {
			return $current;
		}
		if ( $status === ( $current['status'] ?? '' ) ) {
			return true;
		}

		return new \WP_Error(
			'subin_' . $error_prefix . '_projection_conflict',
			__( 'The lead delivery status changed while it was being recalculated.', 'submission-inbox' )
		);
	}

	/**
	 * Apply a backward-compatible delivery transition without allowing stale regressions.
	 *
	 * @param int               $lead_id Lead id.
	 * @param string            $status_field Allowlisted status field.
	 * @param string            $version_field Allowlisted version field.
	 * @param string            $status Requested status.
	 * @param array<int,string> $predecessors States that may advance to the request.
	 * @return bool
	 */
	private function advance_mail_status( $lead_id, $status_field, $version_field, $status, array $predecessors ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 || empty( $predecessors ) ) {
			return false;
		}

		$table        = $wpdb->prefix . 'subin_leads';
		$placeholders = implode( ', ', array_fill( 0, count( $predecessors ), '%s' ) );
		$params       = array_merge(
			array( $status, current_time( 'mysql' ), $lead_id, $status ),
			$predecessors
		);
		$updated      = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET {$status_field} = %s, {$version_field} = {$version_field} + 1, updated_at = %s
				WHERE id = %d AND {$status_field} <> %s AND {$status_field} IN ({$placeholders})",
				$params
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		if ( false === $updated ) {
			return false;
		}
		if ( $updated > 0 ) {
			return true;
		}

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

	/**
	 * States an unversioned admin caller may safely advance to a requested value.
	 *
	 * @param string $status Requested status.
	 * @return array<int,string>
	 */
	private function email_status_predecessors( $status ) {
		$levels = array(
			self::EMAIL_STATUS_UNKNOWN            => 0,
			self::EMAIL_STATUS_NO_EMAIL_GENERATED => 1,
			self::EMAIL_STATUS_FAILED             => 2,
			self::EMAIL_STATUS_SENT               => 3,
		);

		return $this->status_predecessors_at_or_below( $status, $levels );
	}

	/**
	 * States an unversioned visitor caller may safely advance to a requested value.
	 *
	 * @param string $status Requested status.
	 * @return array<int,string>
	 */
	private function auto_reply_status_predecessors( $status ) {
		$levels = array(
			self::AUTO_REPLY_STATUS_NOT_CONFIGURED     => 0,
			self::AUTO_REPLY_STATUS_UNKNOWN            => 0,
			self::AUTO_REPLY_STATUS_SCHEDULED          => 1,
			self::AUTO_REPLY_STATUS_NO_EMAIL_GENERATED => 2,
			self::AUTO_REPLY_STATUS_FAILED             => 3,
			self::AUTO_REPLY_STATUS_SENT               => 4,
		);

		return $this->status_predecessors_at_or_below( $status, $levels );
	}

	/**
	 * Select statuses no stronger than a requested delivery outcome.
	 *
	 * @param string            $status Requested status.
	 * @param array<string,int> $levels Ordered status strengths.
	 * @return array<int,string>
	 */
	private function status_predecessors_at_or_below( $status, array $levels ) {
		if ( ! isset( $levels[ $status ] ) ) {
			return array();
		}

		$requested_level = $levels[ $status ];

		return array_keys(
			array_filter(
				$levels,
				static function ( $level ) use ( $requested_level ) {
					return $level <= $requested_level;
				}
			)
		);
	}

	/**
	 * Get allowed lead statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_statuses() {
		return array( self::STATUS_NEW, self::STATUS_SEEN, self::STATUS_ARCHIVED );
	}

	/**
	 * Get allowed email statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_email_statuses() {
		return array(
			self::EMAIL_STATUS_SENT,
			self::EMAIL_STATUS_FAILED,
			self::EMAIL_STATUS_NO_EMAIL_GENERATED,
			self::EMAIL_STATUS_UNKNOWN,
		);
	}

	/**
	 * Supported visitor auto-reply projection values.
	 *
	 * @return array<int,string>
	 */
	private function get_auto_reply_statuses() {
		return array(
			self::AUTO_REPLY_STATUS_NOT_CONFIGURED,
			self::AUTO_REPLY_STATUS_SCHEDULED,
			self::AUTO_REPLY_STATUS_SENT,
			self::AUTO_REPLY_STATUS_FAILED,
			self::AUTO_REPLY_STATUS_NO_EMAIL_GENERATED,
			self::AUTO_REPLY_STATUS_UNKNOWN,
		);
	}

	/**
	 * Highest lead id currently carrying one visibility status.
	 *
	 * @param string $status Visibility status.
	 * @return int
	 */
	public function max_id_for_status( $status ) {
		global $wpdb;

		$status = sanitize_key( (string) $status );
		if ( ! in_array( $status, $this->get_statuses(), true ) ) {
			return 0;
		}

		return absint(
			$wpdb->get_var(
				$wpdb->prepare( "SELECT MAX(id) FROM {$wpdb->prefix}subin_leads WHERE status = %s", $status )
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Supported mutually exclusive Inbox work queues.
	 *
	 * @return array<int,string>
	 */
	private function get_queues() {
		return array(
			self::QUEUE_UNSEEN,
			self::QUEUE_AWAITING_ACTION,
			self::QUEUE_UNASSIGNED,
			self::QUEUE_MY_LEADS,
			self::QUEUE_DUE_TODAY,
			self::QUEUE_OVERDUE,
			self::QUEUE_DELIVERY_ISSUES,
			self::QUEUE_ARCHIVED,
		);
	}

	/**
	 * Get allowed providers.
	 *
	 * @return array<int,string>
	 */
	private function get_providers() {
		return ( new \SUBIN\Integrations\ProviderRegistry() )->active_keys();
	}

	/**
	 * Get allowed workflow statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_workflow_statuses() {
		return array(
			self::WORKFLOW_STATUS_OPEN,
			self::WORKFLOW_STATUS_CONTACTED,
			self::WORKFLOW_STATUS_QUALIFIED,
			self::WORKFLOW_STATUS_WON,
			self::WORKFLOW_STATUS_LOST,
		);
	}

	/**
	 * Get allowed priorities.
	 *
	 * @return array<int,string>
	 */
	private function get_priorities() {
		return array(
			self::PRIORITY_LOW,
			self::PRIORITY_NORMAL,
			self::PRIORITY_HIGH,
			self::PRIORITY_URGENT,
		);
	}
}
