<?php
/**
 * Qualified Leads — server-side flagging, rotatable token, cycle query, CSV export,
 * and the token-authenticated standalone leads page.
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 * @since      1.27.0
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
class SiteStaffr_Leads {

	/**
	 * WordPress option holding the rotatable leads-page bearer token.
	 */
	const TOKEN_OPTION = 'sitestaffr_leads_token';

	/**
	 * Option holding the leads cache generation. Bumping it invalidates every
	 * cached completed-cycle result at once (used when historical leads change:
	 * backfill re-run, or a conversation trashed/restored/purged).
	 */
	const CACHE_GEN_OPTION = 'sitestaffr_leads_cache_gen';

	/**
	 * Determine whether captured contact fields qualify as a lead.
	 *
	 * Bar: name non-empty AND (phone non-empty OR valid email). Identical to the
	 * client-side `sitestaffr_qualified_lead` dataLayer event.
	 *
	 * @param array $contact_fields Function-call contact fields.
	 * @return bool
	 */
	public static function is_qualified( $contact_fields ) {
		if ( ! is_array( $contact_fields ) ) {
			return false;
		}

		$name  = isset( $contact_fields['name'] ) ? trim( (string) $contact_fields['name'] ) : '';
		$phone = isset( $contact_fields['phone'] ) ? trim( (string) $contact_fields['phone'] ) : '';
		$email = isset( $contact_fields['email'] ) ? trim( (string) $contact_fields['email'] ) : '';

		if ( '' === $name ) {
			return false;
		}

		$has_phone = ( '' !== $phone );
		$has_email = ( '' !== $email && (bool) is_email( $email ) );

		return $has_phone || $has_email;
	}

	/**
	 * Title-case an AI-captured name without mangling deliberate casing.
	 *
	 * Uppercases the first letter of each word segment (segments start at the
	 * beginning, after whitespace, a hyphen, an apostrophe, or a period) and
	 * collapses runs of whitespace. Never lowercases, so "McDonald", "MIKE",
	 * and "DeShawn" pass through untouched: "mike C." → "Mike C.".
	 *
	 * @param string $name Captured name.
	 * @return string
	 */
	public static function normalize_name( $name ) {
		$collapsed = preg_replace( '/\s+/u', ' ', (string) $name );
		if ( null === $collapsed ) {
			return trim( (string) $name ); // Invalid UTF-8: keep as-entered.
		}
		$name = trim( $collapsed );
		if ( '' === $name ) {
			return '';
		}
		$to_upper = function_exists( 'mb_strtoupper' ) ? 'mb_strtoupper' : 'strtoupper';
		return preg_replace_callback(
			"/(^|[\\s\\-'’.])(\\p{Ll})/u",
			function ( $m ) use ( $to_upper ) {
				return $m[1] . $to_upper( $m[2] );
			},
			$name
		);
	}

	/**
	 * Canonicalize a captured phone number to XXX-XXX-XXXX when unambiguous.
	 *
	 * Ten digits (optionally behind a +1 / leading 1) format as XXX-XXX-XXXX;
	 * anything else — international numbers, extensions, short input — is
	 * returned as entered so no detail is lost to formatting.
	 *
	 * @param string $phone Captured phone number.
	 * @return string
	 */
	public static function normalize_phone( $phone ) {
		$phone  = trim( (string) $phone );
		$digits = preg_replace( '/\D+/', '', $phone );
		if ( 11 === strlen( $digits ) && '1' === $digits[0] ) {
			$digits = substr( $digits, 1 );
		}
		if ( 10 === strlen( $digits ) ) {
			return substr( $digits, 0, 3 ) . '-' . substr( $digits, 3, 3 ) . '-' . substr( $digits, 6 );
		}
		return $phone;
	}

	/**
	 * Flag a completed conversation as a qualified lead when it meets the bar.
	 *
	 * Hooked to `sitestaffr_call_completed`. Excludes onboarding-persona and test
	 * conversations. Stores the authoritative function-call contact fields so the
	 * leads list and CSV never re-parse the AI narrative.
	 *
	 * @param int    $call_id        Saved call record ID.
	 * @param string $status         Final call status (unused; signature parity).
	 * @param array  $contact_fields Function-call contact fields.
	 * @return void
	 */
	public static function maybe_flag_qualified_lead( $call_id, $status = '', $contact_fields = array() ) {
		$call_id = absint( $call_id );
		if ( ! $call_id ) {
			return;
		}
		if ( ! self::is_qualified( $contact_fields ) ) {
			SiteStaffr_Logger::legacy( sprintf(
				'SiteStaffr Leads: skip call %d — not qualified (name=%s phone=%s email=%s)',
				$call_id,
				( ! empty( $contact_fields['name'] ) ) ? 'yes' : 'no',
				( ! empty( $contact_fields['phone'] ) ) ? 'yes' : 'no',
				( ! empty( $contact_fields['email'] ) ) ? 'yes' : 'no'
			) );
			return;
		}

		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		// Exclude onboarding (SiteStaffr's own signup flow) and test calls.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT persona, is_test_call, ai_summary FROM %i WHERE id = %d',
				$calls_table,
				$call_id
			)
		);
		if ( ! $row || 'onboarding' === $row->persona || (int) $row->is_test_call === 1 ) {
			return;
		}

		$email = isset( $contact_fields['email'] ) ? strtolower( trim( (string) $contact_fields['email'] ) ) : '';
		if ( '' !== $email && ! is_email( $email ) ) {
			$email = '';
		}
		$phone    = isset( $contact_fields['phone'] ) ? self::normalize_phone( $contact_fields['phone'] ) : '';
		$name     = isset( $contact_fields['name'] ) ? self::normalize_name( $contact_fields['name'] ) : '';
		// "What they wanted": captured reason, else the recap's intent line.
		$interest = self::derive_interest(
			isset( $contact_fields['reason'] ) ? $contact_fields['reason'] : '',
			isset( $row->ai_summary ) ? $row->ai_summary : ''
		);

		$updated = $wpdb->update(
			$calls_table,
			array(
				'is_qualified_lead' => 1,
				'lead_name'         => sanitize_text_field( $name ),
				'lead_phone'        => substr( sanitize_text_field( $phone ), 0, 64 ),
				'lead_email'        => substr( sanitize_email( $email ), 0, 191 ),
				'lead_interest'     => sanitize_text_field( $interest ),
			),
			array( 'id' => $call_id ),
			array( '%d', '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);

		if ( false === $updated ) {
			SiteStaffr_Logger::legacy( 'SiteStaffr Leads: Failed to flag qualified lead for call ' . $call_id . ' - ' . $wpdb->last_error );
		}
	}

	/**
	 * Get the leads-page bearer token, generating one on first use.
	 *
	 * @return string 64-char hex token.
	 */
	public static function get_or_create_token() {
		$token = get_option( self::TOKEN_OPTION, '' );
		if ( empty( $token ) ) {
			$token = bin2hex( random_bytes( 32 ) );
			update_option( self::TOKEN_OPTION, $token, false );
		}
		return $token;
	}

	/**
	 * Timing-safe verification of a supplied leads token.
	 *
	 * @param string $token Token from the request.
	 * @return bool
	 */
	public static function verify_token( $token ) {
		$token = (string) $token;
		if ( '' === $token ) {
			return false;
		}
		$stored = (string) get_option( self::TOKEN_OPTION, '' );
		if ( '' === $stored ) {
			return false;
		}
		return hash_equals( $stored, $token );
	}

	/**
	 * Rotate the leads token, invalidating all previously distributed links.
	 *
	 * @return string The new token.
	 */
	public static function reset_token() {
		$token = bin2hex( random_bytes( 32 ) );
		update_option( self::TOKEN_OPTION, $token, false );
		return $token;
	}

	/**
	 * Build the public leads-page URL.
	 *
	 * @return string
	 */
	public static function get_page_url() {
		return add_query_arg(
			array(
				'sitestaffr_leads' => 1,
				'token'            => self::get_or_create_token(),
			),
			home_url( '/' )
		);
	}

	/**
	 * Build the CSV-download URL.
	 *
	 * @return string
	 */
	public static function get_csv_url() {
		return add_query_arg( array( 'format' => 'csv' ), self::get_page_url() );
	}

	/**
	 * Fetch all qualified leads, newest first (all-time, not cycle-scoped).
	 *
	 * Showing the full history means the list is never empty just because the
	 * billing cycle rolled over — early in a cycle the owner still sees the
	 * leads they captured previously.
	 *
	 * @return array Row objects.
	 */
	public static function get_all_leads() {
		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, created_at, started_at, lead_name, lead_phone, lead_email, lead_interest, handled_at
				 FROM %i
				 WHERE is_qualified_lead = 1
				   AND is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'
				 ORDER BY created_at DESC",
				$calls_table
			)
		);
	}

	/**
	 * Resolve the current billing-cycle start (MySQL UTC datetime).
	 *
	 * Cascade: Stripe period start in the billing cache → the customer's real
	 * renewal that Blog Agent syncs from middleware (`budget_reset_date` minus one
	 * month) → first of the current month. This anchors the per-cycle navigation.
	 *
	 * @return string MySQL datetime.
	 */
	public static function get_current_cycle_start() {
		if ( class_exists( 'SiteStaffr_Billing_State' ) ) {
			$cache = SiteStaffr_Billing_State::get_cache();
			if ( is_array( $cache ) && ! empty( $cache['subscription_current_period_start'] ) ) {
				$ts = strtotime( (string) $cache['subscription_current_period_start'] );
				if ( false !== $ts ) {
					return gmdate( 'Y-m-d H:i:s', $ts );
				}
			}
		}
		if ( class_exists( 'SiteStaffr_Blog_Manager' ) ) {
			$blog = SiteStaffr_Blog_Manager::get_settings();
			if ( is_array( $blog ) && ! empty( $blog['budget_reset_date'] ) ) {
				$reset_ts = strtotime( (string) $blog['budget_reset_date'] );
				if ( false !== $reset_ts ) {
					$start_ts = strtotime( '-1 month', $reset_ts );
					if ( false !== $start_ts ) {
						return gmdate( 'Y-m-d H:i:s', $start_ts );
					}
				}
			}
		}
		return gmdate( 'Y-m-01 00:00:00' );
	}

	/**
	 * Compute the [start, end) window + display label for the billing cycle
	 * `$offset` cycles before the current one (offset 0 = current cycle).
	 *
	 * @param int $offset Cycles back from the current one.
	 * @return array { start_ts, end_ts, start_dt, end_dt, label }
	 */
	public static function get_cycle_window( $offset = 0 ) {
		$offset    = max( 0, (int) $offset );
		$anchor_ts = strtotime( self::get_current_cycle_start() );
		if ( false === $anchor_ts ) {
			$anchor_ts = strtotime( gmdate( 'Y-m-01 00:00:00' ) );
		}
		$start_ts = strtotime( '-' . $offset . ' month', $anchor_ts );
		$end_ts   = strtotime( '+1 month', $start_ts );
		$display_end = $end_ts - DAY_IN_SECONDS; // End is exclusive; show through the prior day.

		return array(
			'start_ts' => $start_ts,
			'end_ts'   => $end_ts,
			'start_dt' => gmdate( 'Y-m-d H:i:s', $start_ts ),
			'end_dt'   => gmdate( 'Y-m-d H:i:s', $end_ts ),
			'label'    => gmdate( 'M j', $start_ts ) . ' – ' . gmdate( 'M j, Y', $display_end ),
		);
	}

	/**
	 * Fetch qualified leads created within [start_dt, end_dt), newest first.
	 *
	 * @param string $start_dt MySQL datetime (inclusive).
	 * @param string $end_dt   MySQL datetime (exclusive).
	 * @return array Row objects.
	 */
	public static function get_leads_between( $start_dt, $end_dt ) {
		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, created_at, started_at, lead_name, lead_phone, lead_email, lead_interest, handled_at
				 FROM %i
				 WHERE is_qualified_lead = 1
				   AND is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'
				   AND created_at >= %s
				   AND created_at < %s
				 ORDER BY created_at DESC",
				$calls_table,
				$start_dt,
				$end_dt
			)
		);
	}

	/**
	 * Whether a call row counts as a captured lead (the single PHP-side
	 * definition — onboarding-persona conversations are excluded because
	 * their contact capture is SiteStaffr's own signup flow, not a visitor).
	 *
	 * @param object $call Call row (needs is_qualified_lead + persona).
	 * @return bool
	 */
	public static function is_captured( $call ) {
		return ! empty( $call->is_qualified_lead )
			&& ( ! isset( $call->persona ) || 'onboarding' !== sanitize_key( $call->persona ) );
	}

	/**
	 * Whether a lead row has been deliberately marked handled/followed-up.
	 *
	 * `handled_at` is a nullable datetime — non-empty (and not the zero date)
	 * means someone tapped "Mark handled". Distinct from `is_read` ("viewed").
	 *
	 * @param object $lead Lead row (needs handled_at).
	 * @return bool
	 */
	public static function is_handled( $lead ) {
		if ( ! isset( $lead->handled_at ) ) {
			return false;
		}
		$handled_at = trim( (string) $lead->handled_at );
		return '' !== $handled_at && '0000-00-00 00:00:00' !== $handled_at;
	}

	/**
	 * Count qualified leads created within [start_dt, end_dt).
	 *
	 * @param string $start_dt MySQL datetime (inclusive).
	 * @param string $end_dt   MySQL datetime (exclusive).
	 * @return int
	 */
	public static function get_lead_count_between( $start_dt, $end_dt ) {
		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				 WHERE is_qualified_lead = 1
				   AND is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'
				   AND created_at >= %s
				   AND created_at < %s",
				$calls_table,
				$start_dt,
				$end_dt
			)
		);
	}

	/**
	 * Earliest qualified-lead timestamp (Unix), or 0 if none. Used to decide
	 * whether an older billing cycle exists to navigate to.
	 *
	 * @return int
	 */
	public static function get_earliest_lead_ts() {
		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		$dt = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT MIN(created_at) FROM %i
				 WHERE is_qualified_lead = 1
				   AND is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'",
				$calls_table
			)
		);
		$ts = $dt ? strtotime( $dt ) : false;
		return $ts ? (int) $ts : 0;
	}

	/**
	 * Current leads cache generation.
	 *
	 * @return int
	 */
	public static function get_cache_generation() {
		return (int) get_option( self::CACHE_GEN_OPTION, 0 );
	}

	/**
	 * Invalidate all cached completed-cycle results. Cheap (one option write);
	 * caches simply miss and rebuild on next view.
	 *
	 * @return void
	 */
	public static function bump_cache_generation() {
		update_option( self::CACHE_GEN_OPTION, self::get_cache_generation() + 1, false );
	}

	/**
	 * Set (or clear) the deliberate "followed up / handled" flag on a lead.
	 *
	 * The single writer for `handled_at`, shared by the public leads sheet
	 * (token-authed) and the wp-admin "Followed up" control. Deliberately
	 * distinct from `is_read` ("viewed") — opening a transcript never marks a
	 * lead handled. The UPDATE is scoped to real qualified leads so a leaked or
	 * guessed token can never flip arbitrary conversation rows.
	 *
	 * @param int  $call_id Lead (call row) id.
	 * @param bool $handled True to mark handled (now), false to clear.
	 * @return bool False only on a DB error; true otherwise (idempotent no-ops
	 *              on a non-lead id or an already-current state count as success).
	 */
	public static function set_handled( $call_id, $handled ) {
		$call_id = absint( $call_id );
		if ( ! $call_id ) {
			return false;
		}

		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		$scope = "id = %d
			AND is_qualified_lead = 1
			AND is_test_call = 0
			AND deleted_at IS NULL
			AND persona != 'onboarding'";

		if ( $handled ) {
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE %i SET handled_at = %s WHERE {$scope}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $scope is a constant fragment; values are placeholder-bound.
					$calls_table,
					gmdate( 'Y-m-d H:i:s' ),
					$call_id
				)
			);
		} else {
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE %i SET handled_at = NULL WHERE {$scope}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $scope is a constant fragment; value is placeholder-bound.
					$calls_table,
					$call_id
				)
			);
		}

		if ( false === $result ) {
			SiteStaffr_Logger::legacy( 'SiteStaffr Leads: Failed to set handled_at for call ' . $call_id . ' - ' . $wpdb->last_error );
			return false;
		}

		// Following up implies you've seen it: marking handled also marks the
		// conversation read (clears the wp-admin "unread" badge). Undo doesn't
		// un-read. (Mario, 2026-07-20.)
		if ( $handled && class_exists( 'SiteStaffr_Call_Manager' ) ) {
			SiteStaffr_Call_Manager::mark_as_read( array( $call_id ) );
		}

		// Past-cycle results are cached; a toggle on a past lead must invalidate them.
		self::bump_cache_generation();
		return true;
	}

	/**
	 * Fetch a billing cycle's leads, caching completed (past) cycles since their
	 * data never changes again. The current cycle (offset 0) is always live.
	 *
	 * @param array $window Window from get_cycle_window().
	 * @param int   $offset Cycle offset (0 = current).
	 * @return array Row objects.
	 */
	public static function get_cycle_leads_cached( $window, $offset ) {
		if ( (int) $offset <= 0 ) {
			// Current cycle — still accruing leads, always query live.
			return self::get_leads_between( $window['start_dt'], $window['end_dt'] );
		}

		$key    = 'ststf_leads_g' . self::get_cache_generation() . '_' . md5( $window['start_dt'] . '|' . $window['end_dt'] );
		$cached = get_transient( $key );
		if ( false !== $cached && is_array( $cached ) ) {
			return $cached;
		}

		$leads = self::get_leads_between( $window['start_dt'], $window['end_dt'] );
		set_transient( $key, $leads, MONTH_IN_SECONDS );
		return $leads;
	}

	/**
	 * The "what they wanted" string, shown in FULL (no truncation/ellipsis).
	 *
	 * The reason is AI-generated: during the conversation the agent captures a
	 * deliberately short, concrete `reason` field (see the middleware
	 * contact-collection prompt), so the captured value is already concise and is
	 * returned verbatim. For older conversations that predate the reason field we
	 * fall back to the recap's opening narrative sentence (a complete thought).
	 * Stored once at flag/backfill time — never parsed on render.
	 *
	 * @param string $reason      Captured reason field (authoritative when set).
	 * @param string $ai_summary  Stored recap HTML (fallback source).
	 * @return string
	 */
	public static function derive_interest( $reason, $ai_summary = '' ) {
		$reason = trim( (string) $reason );
		if ( '' !== $reason ) {
			return $reason;
		}
		if ( '' === (string) $ai_summary ) {
			return '';
		}

		$text = stripslashes( (string) $ai_summary );
		$text = preg_replace( '/<\s*(br|\/li|\/p|\/tr|\/h[1-6])\s*\/?>/i', "\n", $text );
		$text = wp_strip_all_tags( $text );

		foreach ( preg_split( '/\n/', $text ) as $line ) {
			$line = trim( preg_replace( '/\s+/', ' ', $line ) );
			if ( '' === $line ) {
				continue;
			}
			// Skip pure label lines — we want the narrative intent line.
			if ( preg_match( '/^(Name|Phone|Email|Reason(?:\s+for\s+call)?|Suggested\s+follow[- ]?up)\s*:/i', $line ) ) {
				continue;
			}
			// Return the first complete sentence, else the whole line. No truncation.
			if ( preg_match( '/^(.*?[.!?])(?:\s|$)/u', $line, $m ) && str_word_count( $m[1] ) >= 3 ) {
				return trim( $m[1] );
			}
			return $line;
		}
		return '';
	}

	/**
	 * Guard a CSV cell against spreadsheet formula injection.
	 *
	 * @param string $value Raw cell value.
	 * @return string
	 */
	public static function csv_safe_cell( $value ) {
		$value = (string) $value;
		if ( '' !== $value && in_array( $value[0], array( '=', '+', '-', '@' ), true ) ) {
			return "'" . $value;
		}
		return $value;
	}

	/**
	 * Format a call's date in Eastern Time for display/CSV.
	 *
	 * @param object $lead Lead row with started_at / created_at.
	 * @return string
	 */
	public static function format_lead_date( $lead ) {
		$timestamp = 0;
		if ( ! empty( $lead->started_at ) && (int) $lead->started_at > 0 ) {
			$timestamp = (int) $lead->started_at;
			if ( $timestamp > 9999999999 ) {
				$timestamp = (int) floor( $timestamp / 1000 );
			}
		}
		try {
			if ( $timestamp > 0 ) {
				$date = new DateTime( '@' . $timestamp );
			} elseif ( ! empty( $lead->created_at ) ) {
				$date = new DateTime( $lead->created_at, new DateTimeZone( 'UTC' ) );
			} else {
				return '';
			}
			$date->setTimezone( new DateTimeZone( 'America/New_York' ) );
			return $date->format( 'F j, Y g:i A T' );
		} catch ( Exception $e ) {
			return '';
		}
	}

	/**
	 * Build CSV rows (header + one row per lead).
	 *
	 * @param array $leads Lead row objects.
	 * @return array Array of string arrays.
	 */
	public static function build_csv_rows( $leads ) {
		$rows   = array();
		$rows[] = array( 'Date', 'Name', 'Phone', 'Email', 'Interest', 'Handled', 'Transcript URL' );
		foreach ( (array) $leads as $lead ) {
			$transcript_url = class_exists( 'SiteStaffr_Magic_Link' )
				? SiteStaffr_Magic_Link::generate_transcript_url( (int) $lead->id )
				: '';
			$rows[] = array(
				self::csv_safe_cell( self::format_lead_date( $lead ) ),
				self::csv_safe_cell( (string) $lead->lead_name ),
				self::csv_safe_cell( (string) $lead->lead_phone ),
				self::csv_safe_cell( (string) $lead->lead_email ),
				self::csv_safe_cell( (string) $lead->lead_interest ),
				self::csv_safe_cell( self::is_handled( $lead ) ? 'Yes' : 'No' ),
				self::csv_safe_cell( $transcript_url ),
			);
		}
		return $rows;
	}

	/**
	 * Intercept the public leads-page route, verify the token, and render
	 * either the HTML page or a CSV download. No WordPress login required —
	 * the bearer token serves the anti-tampering role (same model as the
	 * transcript viewer's HMAC link).
	 *
	 * @return void
	 */
	public static function maybe_render_leads_page() {
		// phpcs:disable WordPress.Security.NonceVerification.Recommended
		if ( ! isset( $_GET['sitestaffr_leads'] ) || ! isset( $_GET['token'] ) ) {
			return;
		}

		// Live lead data must never be page-cached. nocache_headers() below covers
		// header-honoring caches; the constant + action opt out of WP page-cache
		// plugins (LiteSpeed et al.) by contract, since aggressive host edges have
		// been seen serving stale plugin pages despite response headers.
		if ( ! defined( 'DONOTCACHEPAGE' ) ) {
			define( 'DONOTCACHEPAGE', true );
		}
		do_action( 'litespeed_control_set_nocache', 'sitestaffr leads viewer' );
		$token  = sanitize_text_field( wp_unslash( $_GET['token'] ) );
		$format = isset( $_GET['format'] ) ? sanitize_key( wp_unslash( $_GET['format'] ) ) : '';
		$want_diag    = isset( $_GET['diag'] );
		$cycle_offset = isset( $_GET['cycle'] ) ? max( 0, (int) wp_unslash( $_GET['cycle'] ) ) : 0;
		// phpcs:enable WordPress.Security.NonceVerification.Recommended

		if ( ! self::verify_token( $token ) ) {
			status_header( 403 );
			wp_die(
				esc_html__( 'This leads link is invalid or has been reset.', 'sitestaffr' ),
				esc_html__( 'Invalid Link', 'sitestaffr' ),
				array( 'response' => 403 )
			);
		}

		// Mark-handled toggle from the sheet. Auth is the (already-verified) leads
		// token — a 64-char secret carried in the link, not a cookie, so it can't be
		// cross-site forged. POST only; the write is scoped to real leads in
		// set_handled(). Responds JSON and exits before any HTML render.
		// phpcs:disable WordPress.Security.NonceVerification.Missing -- Auth is the leads bearer token verified above, not a nonce (no login/session).
		if (
			isset( $_SERVER['REQUEST_METHOD'] ) && 'POST' === $_SERVER['REQUEST_METHOD']
			&& isset( $_POST['sitestaffr_action'] ) && 'toggle_handled' === sanitize_key( wp_unslash( $_POST['sitestaffr_action'] ) )
		) {
			$call_id = isset( $_POST['call_id'] ) ? absint( wp_unslash( $_POST['call_id'] ) ) : 0;
			$handled = ! empty( $_POST['handled'] );
			$ok      = $call_id ? self::set_handled( $call_id, $handled ) : false;
			wp_send_json(
				array(
					'success' => (bool) $ok,
					'call_id' => $call_id,
					'handled' => $handled,
				)
			);
		}
		// phpcs:enable WordPress.Security.NonceVerification.Missing

		// Resolve the selected billing cycle and its leads. Completed (past) cycles
		// are cached; the current cycle is always live.
		$window = self::get_cycle_window( $cycle_offset );
		$leads  = self::get_cycle_leads_cached( $window, $cycle_offset );

		if ( 'csv' === $format ) {
			self::stream_csv( $leads, $window['label'] );
			exit;
		}

		// Standalone HTML page.
		nocache_headers();
		// This standalone report prints wp_footer(); keep the chat widget out of it.
		if ( class_exists( 'SiteStaffr_Widget_Auto_Display_Handler' ) ) {
			SiteStaffr_Widget_Auto_Display_Handler::suppress();
		}
		header( 'X-Robots-Tag: noindex, nofollow', true );

		$settings      = get_option( 'sitestaffr_settings', array() );
		$business_name = ! empty( $settings['business_name'] ) ? stripslashes( $settings['business_name'] ) : '';
		$logo_url      = SITESTAFFR_PLUGIN_URL . 'assets/images/logo.png';
		$leads_diag    = $want_diag ? self::scan_counts() : null;

		// Cycle navigation: older cycle exists if any lead predates this window;
		// newer cycle exists whenever we're viewing a past cycle.
		$page_url      = self::get_page_url();
		$earliest_ts   = self::get_earliest_lead_ts();
		$has_older     = ( $earliest_ts > 0 && $earliest_ts < $window['start_ts'] );
		$has_newer     = ( $cycle_offset > 0 );
		$cycle_label   = $window['label'];
		$prev_cycle_url = $has_older ? add_query_arg( 'cycle', $cycle_offset + 1, $page_url ) : '';
		$next_cycle_url = $has_newer ? add_query_arg( 'cycle', $cycle_offset - 1, $page_url ) : '';
		$csv_url        = add_query_arg(
			array(
				'cycle'  => $cycle_offset,
				'format' => 'csv',
			),
			$page_url
		);

		wp_enqueue_style(
			'sitestaffr-transcript-viewer',
			SITESTAFFR_PLUGIN_URL . 'assets/css/sitestaffr-transcript-viewer.css',
			array(),
			SITESTAFFR_VERSION
		);
		wp_enqueue_style(
			'sitestaffr-leads-viewer',
			SITESTAFFR_PLUGIN_URL . 'assets/css/sitestaffr-leads-viewer.css',
			array( 'sitestaffr-transcript-viewer' ),
			SITESTAFFR_VERSION
		);
		wp_enqueue_script(
			'sitestaffr-leads-viewer',
			SITESTAFFR_PLUGIN_URL . 'assets/js/sitestaffr-leads-viewer.js',
			array(),
			SITESTAFFR_VERSION,
			true
		);

		include SITESTAFFR_PLUGIN_DIR . 'templates/public/leads-viewer.php';
		exit;
	}

	/**
	 * Stream the leads CSV as a file download and exit.
	 *
	 * @param array  $leads       Lead row objects.
	 * @param string $cycle_label Optional billing-cycle label for the filename.
	 * @return void
	 */
	public static function stream_csv( $leads, $cycle_label = '' ) {
		$host     = wp_parse_url( home_url(), PHP_URL_HOST );
		$host     = $host ? preg_replace( '/[^a-z0-9.-]/i', '', $host ) : 'site';
		$cycle_slug = '' !== $cycle_label ? '-' . sanitize_title( $cycle_label ) : '';
		$filename = 'sitestaffr-leads-' . $host . $cycle_slug . '-' . gmdate( 'Y-m-d' ) . '.csv';

		nocache_headers();
		// This standalone report prints wp_footer(); keep the chat widget out of it.
		if ( class_exists( 'SiteStaffr_Widget_Auto_Display_Handler' ) ) {
			SiteStaffr_Widget_Auto_Display_Handler::suppress();
		}
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

		// UTF-8 BOM so Excel detects encoding.
		echo "\xEF\xBB\xBF"; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped

		$output = fopen( 'php://output', 'w' );
		foreach ( self::build_csv_rows( $leads ) as $row ) {
			fputcsv( $output, $row );
		}
		fclose( $output );
	}

	/**
	 * WordPress option storing the completed backfill version. Bumping
	 * BACKFILL_VERSION re-runs the backfill once on the next load (e.g. after
	 * fixing the cycle window or the recap parser).
	 */
	const BACKFILL_OPTION = 'sitestaffr_leads_backfill_done';

	/**
	 * Current backfill version. v2 made recap parsing tag-aware; v3 flags all
	 * historical qualified conversations; v4 adds prose fallbacks (email anywhere,
	 * leading proper-noun name); v5 derives a "what they wanted" interest from the
	 * recap when no explicit reason was captured; v6 backfills that interest onto
	 * already-flagged leads whose interest was left empty by an earlier version;
	 * v7 re-derives interest in full (earlier versions stored a truncated value);
	 * v8 re-scans all unqualified rows to recover voice + text leads whose
	 * contact was captured in the recap but never flagged (P0 HIPAA lead recovery);
	 * v9 normalizes already-stored lead names/phones (title-case, XXX-XXX-XXXX) so
	 * historic rows match the write-time normalization added alongside it.
	 */
	const BACKFILL_VERSION = 9;

	/**
	 * Best-effort extraction of name/phone/email/reason from a stored AI recap.
	 *
	 * Mirrors the label parsing in the transcript viewer. Only used by the
	 * one-time backfill — forward-looking flagging uses authoritative
	 * function-call fields.
	 *
	 * @param string $summary_html Stored ai_summary HTML.
	 * @return array { name, phone, email, reason }
	 */
	public static function parse_contact_from_summary( $summary_html ) {
		$data = array( 'name' => '', 'phone' => '', 'email' => '', 'reason' => '' );
		if ( empty( $summary_html ) ) {
			return $data;
		}

		// Recaps are stored as HTML — either plain "Label: value" lines or list/
		// table markup, and labels are sometimes wrapped in <strong>. Convert
		// block separators to newlines FIRST (so adjacent <li> items don't run
		// together), then strip tags, so "<li><strong>Name:</strong> Jane</li>"
		// becomes the plain line "Name: Jane" the regexes below can read.
		$text = stripslashes( (string) $summary_html );
		$text = preg_replace( '/<\s*(br|\/li|\/p|\/tr|\/h[1-6])\s*\/?>/i', "\n", $text );
		$text = wp_strip_all_tags( $text );

		// Labeled fields first (recaps are generated with these labels).
		if ( preg_match( '/Name:\s*([^\n]+)/i', $text, $m ) ) {
			$data['name'] = trim( $m[1] );
		}
		if ( preg_match( '/Phone:\s*([^\n]+)/i', $text, $m ) ) {
			$data['phone'] = trim( $m[1] );
		}
		if ( preg_match( '/Email:\s*([^\n]+)/i', $text, $m ) ) {
			$data['email'] = trim( $m[1] );
		}
		if ( preg_match( '/Reason(?:\s+for\s+call)?:\s*([^\n]+)/i', $text, $m ) ) {
			$data['reason'] = trim( $m[1] );
		}

		// Fallbacks for recaps that state contact details in prose rather than
		// under explicit labels. Email patterns are unambiguous; phone stays
		// label-only to avoid mistaking dates/IDs in the recap for a number.
		if ( '' === $data['email'] && preg_match( '/[A-Z0-9._%+\-]+@[A-Z0-9.\-]+\.[A-Z]{2,}/i', $text, $m ) ) {
			$data['email'] = $m[0];
		}
		if ( '' === $data['name'] ) {
			$first_line = '';
			foreach ( preg_split( '/\n/', $text ) as $line ) {
				$line = trim( $line );
				if ( '' !== $line ) {
					$first_line = $line;
					break;
				}
			}
			// A leading 1–3 word proper noun ("Jane Doe called about…"), skipping
			// generic openers like "Visitor"/"Caller".
			if ( '' !== $first_line && preg_match( "/^([A-Z][a-z'\\-]+(?:\\s+[A-Z][a-z'\\-]+){0,2})\\b/", $first_line, $m ) ) {
				if ( ! preg_match( '/^(Visitor|Caller|The|A|An|Customer|Client|Unknown|Someone|User|They|He|She|It)$/i', $m[1] ) ) {
					$data['name'] = $m[1];
				}
			}
		}

		// Reduce a labeled email line to just the address token.
		if ( '' !== $data['email'] && preg_match( '/[A-Z0-9._%+\-]+@[A-Z0-9.\-]+\.[A-Z]{2,}/i', $data['email'], $m ) ) {
			$data['email'] = $m[0];
		}

		return $data;
	}

	/**
	 * Diagnostic counts for why the leads list may be empty. Returns ONLY
	 * aggregate counts — never any contact value — so it is safe to surface on
	 * the (token-authenticated) leads page via ?diag=1. Helps pinpoint whether
	 * the gap is recap format (no name/contact detected) vs. a stale flag.
	 *
	 * @return array { scanned, has_name, has_email, has_phone, qualifies, flagged, version }
	 */
	public static function scan_counts() {
		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ai_summary, is_qualified_lead FROM %i
				 WHERE is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'
				   AND ai_summary IS NOT NULL AND ai_summary != ''",
				$calls_table
			)
		);

		$c = array(
			'scanned'   => 0,
			'has_name'  => 0,
			'has_email' => 0,
			'has_phone' => 0,
			'qualifies' => 0,
			'flagged'   => 0,
			'version'   => defined( 'SITESTAFFR_VERSION' ) ? SITESTAFFR_VERSION : '',
		);

		foreach ( (array) $rows as $row ) {
			++$c['scanned'];
			if ( (int) $row->is_qualified_lead === 1 ) {
				++$c['flagged'];
			}
			$f = self::parse_contact_from_summary( $row->ai_summary );
			if ( '' !== $f['name'] ) {
				++$c['has_name'];
			}
			if ( '' !== $f['email'] && is_email( $f['email'] ) ) {
				++$c['has_email'];
			}
			if ( '' !== $f['phone'] ) {
				++$c['has_phone'];
			}
			if ( self::is_qualified( $f ) ) {
				++$c['qualifies'];
			}
		}

		return $c;
	}

	/**
	 * One-time backfill: flag qualified leads among existing conversations that
	 * predate the flag, parsing their stored recaps. All-time (not cycle-scoped),
	 * so the full lead history is available. Runs once per BACKFILL_VERSION.
	 *
	 * @return int Number of conversations newly flagged.
	 */
	public static function backfill_existing() {
		if ( (int) get_option( self::BACKFILL_OPTION, 0 ) >= self::BACKFILL_VERSION ) {
			return 0;
		}
		update_option( self::BACKFILL_OPTION, self::BACKFILL_VERSION, false );

		global $wpdb;
		$calls_table = $wpdb->prefix . 'phoneease_calls';

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, ai_summary FROM %i
				 WHERE is_qualified_lead = 0
				   AND is_test_call = 0
				   AND deleted_at IS NULL
				   AND persona != 'onboarding'
				   AND ai_summary IS NOT NULL AND ai_summary != ''",
				$calls_table
			)
		);

		$flagged = 0;
		foreach ( (array) $rows as $row ) {
			$fields = self::parse_contact_from_summary( $row->ai_summary );
			if ( ! self::is_qualified( $fields ) ) {
				continue;
			}
			$email    = is_email( $fields['email'] ) ? strtolower( $fields['email'] ) : '';
			$interest = self::derive_interest( $fields['reason'], $row->ai_summary );
			$wpdb->update(
				$calls_table,
				array(
					'is_qualified_lead' => 1,
					'lead_name'         => substr( sanitize_text_field( self::normalize_name( $fields['name'] ) ), 0, 191 ),
					'lead_phone'        => substr( sanitize_text_field( self::normalize_phone( $fields['phone'] ) ), 0, 64 ),
					'lead_email'        => substr( sanitize_email( $email ), 0, 191 ),
					'lead_interest'     => sanitize_text_field( $interest ),
				),
				array( 'id' => (int) $row->id ),
				array( '%d', '%s', '%s', '%s', '%s' ),
				array( '%d' )
			);
			++$flagged;
		}

		// Second pass: (re)derive "what they wanted" for already-flagged leads whose
		// interest is empty OR was stored truncated by an earlier version (ends with
		// the ellipsis marker). Re-derives the full intent from the stored recap.
		// Captured short reasons (no ellipsis) are left untouched.
		$ellipsis_like = '%' . $wpdb->esc_like( '…' ) . '%';
		$missing_interest = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, ai_summary FROM %i
				 WHERE is_qualified_lead = 1
				   AND ( lead_interest IS NULL OR lead_interest = '' OR lead_interest LIKE %s )
				   AND ai_summary IS NOT NULL AND ai_summary != ''",
				$calls_table,
				$ellipsis_like
			)
		);
		foreach ( (array) $missing_interest as $row ) {
			$interest = self::derive_interest( '', $row->ai_summary );
			if ( '' === $interest ) {
				continue;
			}
			$wpdb->update(
				$calls_table,
				array( 'lead_interest' => sanitize_text_field( $interest ) ),
				array( 'id' => (int) $row->id ),
				array( '%s' ),
				array( '%d' )
			);
		}

		// Third pass (v9): normalize already-stored lead names/phones so historic
		// rows match write-time normalization. Both helpers are idempotent and
		// conservative (ambiguous phones pass through as-entered), so re-running
		// on clean rows is a no-op; only changed values are written back.
		$flagged_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, lead_name, lead_phone FROM %i
				 WHERE is_qualified_lead = 1
				   AND ( ( lead_name IS NOT NULL AND lead_name != '' )
				      OR ( lead_phone IS NOT NULL AND lead_phone != '' ) )",
				$calls_table
			)
		);
		foreach ( (array) $flagged_rows as $row ) {
			$clean_name  = substr( self::normalize_name( (string) $row->lead_name ), 0, 191 );
			$clean_phone = substr( self::normalize_phone( (string) $row->lead_phone ), 0, 64 );
			if ( $clean_name === (string) $row->lead_name && $clean_phone === (string) $row->lead_phone ) {
				continue;
			}
			$wpdb->update(
				$calls_table,
				array(
					'lead_name'  => $clean_name,
					'lead_phone' => $clean_phone,
				),
				array( 'id' => (int) $row->id ),
				array( '%s', '%s' ),
				array( '%d' )
			);
		}

		// Historical flags/interest changed — drop cached completed-cycle results.
		self::bump_cache_generation();

		if ( $flagged > 0 ) {
			SiteStaffr_Logger::legacy( 'SiteStaffr Leads: Backfilled ' . $flagged . ' qualified lead(s) from conversation history.' );
		}
		return $flagged;
	}
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
