<?php
defined( 'ABSPATH' ) || exit;

/**
 * Storage for visitor journeys.
 *
 * Three levels of attention, only two of which reach this table:
 *
 *   BROWSING  the journey exists only in the visitor's own browser. Nothing here.
 *   HELD      we keep a copy provisionally, because something they did suggested
 *             they may be about to convert somewhere we cannot observe — typically
 *             a form on another domain. Expires on its own unless promoted.
 *   SAVED     they did the thing that matters. Kept for the retention period.
 *
 * Two clocks, deliberately different. The HELD lifetime is an abuse control and
 * stays short — it is what stops the create endpoint being free permanent storage
 * for anyone who finds it. The SAVED retention is a business setting and may be
 * years, or unlimited.
 *
 * A journey submitted through a form on THIS site is promoted by the submission
 * itself — BW_Lead_AI_Identity calls the same save() the cross-domain confirm
 * endpoint calls, so both conversions are one code path. Holding exists because
 * a conversion, on-site or off, happens after the journey is captured.
 *
 * This is the plugin's only custom table, and it is created lazily on first use
 * rather than on activation — which, since journey storage stopped being gated
 * on cross-domain handoff, is the first request after the plugin can store at
 * all (most sites), not the day handoff is switched on.
 */
class BW_Lead_AI_Handoff_Store {

	/** Bumped when the schema changes; drives dbDelta on upgrade. */
	const SCHEMA_VERSION = 6;

	/**
	 * Ceiling on how many conversions one record keeps.
	 *
	 * **The FIRST is never trimmed.** It is the one the reports credit and the
	 * one the lead's date comes from, so a person who uses a contact form twenty
	 * times to ask follow-up questions must not push the enquiry that actually
	 * won them off the front of their own record. Trimming keeps entry zero and
	 * drops from the middle — the same shape as the visit history's first-five
	 * plus last-N.
	 */
	const MAX_CONVERSIONS = 20;

	const SCHEMA_OPTION = 'bw_lead_ai_handoff_schema';

	/** Expiry stand-in for "unlimited retention". Comparisons stay simple. */
	const NEVER = '2099-12-31 23:59:59';

	/**
	 * The two stored states.
	 *
	 * HELD  — we are keeping a copy provisionally, because something the visitor
	 *         did suggested they might be about to convert somewhere we cannot
	 *         see. Expires on its own unless something promotes it.
	 * SAVED — they did the thing that matters. Kept for the retention period.
	 *
	 * A journey that is neither is simply *browsing*: it exists only in the
	 * visitor's own browser and this table knows nothing about it.
	 *
	 * These were `pending` / `confirmed` up to schema 2, which named them after the
	 * cross-domain handshake rather than after what they mean. Existing rows are
	 * migrated in maybe_create_table().
	 */
	const STATUS_HELD  = 'held';
	const STATUS_SAVED = 'saved';

	/**
	 * A record whose journey now lives on another row.
	 *
	 * When the same person enquires twice, the second submission joins the
	 * journey it belongs to rather than becoming a second lead. Its own row is
	 * not deleted, because **a journey link is written into somebody else's CRM
	 * the moment a visitor converts** — built from that submission's token, and
	 * outliving everything else here. So the row stays as an alias: its token
	 * still resolves, `merged_into` says where to, and the payload is cleared
	 * because the surviving record holds it.
	 *
	 * Its own status is what keeps it out of every count. Every read that means
	 * "a lead" already filters `status = saved`, so a third status excludes
	 * aliases everywhere at once — where a `merged_into IS NULL` clause would
	 * have had to be remembered in each of them, and eventually would not be.
	 */
	const STATUS_MERGED = 'merged';

	/**
	 * How a conversion was reported to us.
	 *
	 * CONFIRM    — a destination on another domain told us its form was sent. It
	 *              is the only witness: nothing on this site sees that submission.
	 * SUBMISSION — a form on this site was submitted, and the server saw it.
	 *
	 * Same values as the `journey_save_mode` setting, and deliberately so: the
	 * setting names which of these a site treats as its conversion signal.
	 */
	const VIA_CONFIRM    = 'confirm';
	const VIA_SUBMISSION = 'submission';

	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'bw_lead_ai_handoffs';
	}

	/**
	 * Lifecycle wiring: create the table when the feature is switched on, and keep
	 * the prune cron in step with whether it is enabled.
	 */
	public static function register() {
		add_action( 'bw_lead_ai_handoff_prune', array( __CLASS__, 'prune' ) );
		add_action( 'update_option_' . BW_LEAD_AI_OPTION, array( __CLASS__, 'sync_lifecycle' ), 10, 0 );
		add_action( 'admin_init', array( __CLASS__, 'sync_lifecycle' ) );
	}

	public static function sync_lifecycle() {
		$scheduled = wp_next_scheduled( 'bw_lead_ai_handoff_prune' );

		// Journey storage, not cross-domain handoff, is what needs the table and
		// the prune: a site saving its own submissions stores rows exactly as a
		// handoff site does.
		if ( ! BW_Lead_AI_Settings::journey_storage_enabled() ) {
			if ( $scheduled ) {
				wp_unschedule_event( $scheduled, 'bw_lead_ai_handoff_prune' );
			}
			return;
		}

		self::maybe_create_table();
		if ( ! $scheduled ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'hourly', 'bw_lead_ai_handoff_prune' );
		}
	}

	/**
	 * Create or upgrade the table. Called on first enable of the feature and on
	 * schema version bumps — never on plugin activation. dbDelta adds new columns
	 * in place, so an existing install picks up a schema bump without losing rows.
	 *
	 * @return bool Whether the table exists afterwards.
	 */
	public static function maybe_create_table() {
		// Fast path trusts the schema option: verifying with SHOW TABLES on every
		// request would put a query on every page load for no benefit.
		if ( (int) get_option( self::SCHEMA_OPTION ) === self::SCHEMA_VERSION ) {
			return true;
		}

		global $wpdb;
		$table   = self::table_name();
		$collate = $wpdb->get_charset_collate();

		// Note: dbDelta is whitespace- and format-sensitive. Two spaces after PRIMARY
		// KEY, lowercase `key`, one field per line.
		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			token_hash char(64) NOT NULL,
			payload longtext NOT NULL,
			status varchar(12) NOT NULL DEFAULT 'held',
			data_claimed_at datetime DEFAULT NULL,
			confirmed_at datetime DEFAULT NULL,
			context varchar(200) DEFAULT NULL,
			visitor_key_hash char(64) DEFAULT NULL,
			lead_id bigint(20) unsigned DEFAULT NULL,
			shared_flag tinyint(1) NOT NULL DEFAULT 0,
			link_provenance varchar(20) DEFAULT NULL,
			last_seen_at datetime DEFAULT NULL,
			identity longtext DEFAULT NULL,
			submission longtext DEFAULT NULL,
			conversions longtext DEFAULT NULL,
			merged_into bigint(20) unsigned DEFAULT NULL,
			created_at datetime NOT NULL,
			expires_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY token_hash (token_hash),
			KEY expires_at (expires_at),
			KEY visitor_key_hash (visitor_key_hash),
			KEY lead_id (lead_id),
			KEY merged_into (merged_into)
		) {$collate};";

		// Leads — the person half of the model. See BW_Lead_AI_Leads.
		$leads_table = BW_Lead_AI_Leads::table_name();
		$leads_sql   = "CREATE TABLE {$leads_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hmac char(64) NOT NULL,
			first_name varchar(200) DEFAULT NULL,
			last_name varchar(200) DEFAULT NULL,
			email varchar(200) DEFAULT NULL,
			phone varchar(50) DEFAULT NULL,
			lead_status varchar(40) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY email_hmac (email_hmac),
			KEY lead_status (lead_status)
		) {$collate};";

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

		if ( ! self::table_exists() ) {
			return false;
		}

		// Schema 3 renamed the statuses. Migrate in place so an existing install
		// keeps its records rather than appearing to lose them.
		$table_esc = self::table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
		$wpdb->query( $wpdb->prepare( "UPDATE {$table_esc} SET status = %s WHERE status = %s", self::STATUS_HELD, 'pending' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
		$wpdb->query( $wpdb->prepare( "UPDATE {$table_esc} SET status = %s WHERE status = %s", self::STATUS_SAVED, 'confirmed' ) );

		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION );
		return true;
	}

	public static function table_exists() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is built from $wpdb->prefix.
		return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
	}

	public static function drop_table() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is built from $wpdb->prefix.
		$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
		delete_option( self::SCHEMA_OPTION );
	}

	/**
	 * A fresh 128-bit bearer token, hex encoded.
	 *
	 * This addresses someone's browsing history, so it comes from a CSPRNG and
	 * nothing else. Stored only as a sha256 hash, so a database dump or backup leak
	 * contains no usable tokens.
	 */
	public static function generate_token() {
		return bin2hex( random_bytes( 16 ) );
	}

	public static function hash_token( $token ) {
		return hash( 'sha256', (string) $token );
	}

	/**
	 * Whether a string is shaped like one of our tokens. Cheap rejection before
	 * touching the database.
	 */
	public static function is_token_shaped( $token ) {
		return is_string( $token ) && 1 === preg_match( '/^[a-f0-9]{32}$/', $token );
	}

	/**
	 * Create a record.
	 *
	 * @param array $payload    Already filtered to the enabled datapoints.
	 * @param int   $ttl_minutes Pending lifetime.
	 * @return string|WP_Error The plain token.
	 */
	public static function mint( $payload, $ttl_minutes ) {
		if ( ! self::table_exists() ) {
			return new WP_Error( 'bw_lead_ai_no_table', __( 'Handoff storage is unavailable.', 'bw-lead-ai' ) );
		}

		global $wpdb;
		$token = self::generate_token();
		$now   = current_time( 'mysql', true );

		$inserted = $wpdb->insert(
			self::table_name(),
			array(
				'token_hash' => self::hash_token( $token ),
				'payload'    => wp_json_encode( $payload ),
				'status'     => self::STATUS_HELD,
				'created_at' => $now,
				'expires_at' => gmdate( 'Y-m-d H:i:s', time() + ( absint( $ttl_minutes ) * MINUTE_IN_SECONDS ) ),
			),
			array( '%s', '%s', '%s', '%s', '%s' )
		);

		if ( ! $inserted ) {
			return new WP_Error( 'bw_lead_ai_insert_failed', __( 'Could not store the handoff record.', 'bw-lead-ai' ) );
		}
		return $token;
	}

	/**
	 * Replace the payload on a live pending record.
	 *
	 * Used when the visitor actually clicks the outbound link, so the destination
	 * gets the journey as of the click rather than as of page load. Deliberately
	 * does NOT extend expiry — a refresh loop must not keep a record alive forever.
	 */
	public static function refresh( $token, $payload ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		$table = self::table_name();
		$hash  = self::hash_token( $token );
		$now   = current_time( 'mysql', true );

		// Existence is checked separately rather than inferred from affected rows.
		// MySQL reports 0 changed rows when an UPDATE writes values identical to
		// the ones already there, so affected-rows cannot tell "no such record"
		// apart from "same payload as last time" — and a false negative here makes
		// the caller mint a second token while the page's links still carry the
		// first. See claim() for the one place affected-rows IS the right test.
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				// data_claimed_at IS NULL: once a destination has claimed the data,
				// the token is burned for claiming — refreshing it would keep the
				// browser decorating links with a token whose claim can only 404.
				// Found live: a visitor's SECOND trip to a destination form in one
				// browsing session filled nothing, with no error anywhere, because
				// the claim is single-use and the client reused the token for the
				// whole session. Declining the refresh here makes create() fall
				// through and mint a fresh token instead; the client picks the new
				// one up from the response and re-decorates.
				"SELECT id FROM {$table} WHERE token_hash = %s AND status = %s AND expires_at > %s AND data_claimed_at IS NULL",
				$hash,
				self::STATUS_HELD,
				$now
			)
		);
		if ( ! $exists ) {
			return false;
		}

		$wpdb->update(
			$table,
			array( 'payload' => wp_json_encode( $payload ) ),
			array( 'id' => (int) $exists ),
			array( '%s' ),
			array( '%d' )
		);
		return true;
	}

	/**
	 * Claim a record's payload — once.
	 *
	 * Burn-on-read: the token stops working cross-origin after the first successful
	 * claim. It will have been written into the destination's URL bar, browser
	 * history, access logs and any third-party script on their page, so shrinking
	 * the exposure window from the full TTL to a single round trip is worth more
	 * than the convenience of a replayable read. Retry-on-404 on the destination
	 * side still works, because retries only matter when the first read failed.
	 *
	 * The claim is an atomic conditional UPDATE, so two parallel requests cannot
	 * both be served.
	 *
	 * @return array|null Payload, or null when missing/expired/already claimed.
	 */
	public static function claim( $token ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return null;
		}
		global $wpdb;
		$table = self::table_name();
		$hash  = self::hash_token( $token );
		$now   = current_time( 'mysql', true );

		$claimed = $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				"UPDATE {$table} SET data_claimed_at = %s WHERE token_hash = %s AND data_claimed_at IS NULL AND expires_at > %s",
				$now,
				$hash,
				$now
			)
		);
		if ( ! $claimed ) {
			return null;
		}

		$payload = $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				"SELECT payload FROM {$table} WHERE token_hash = %s",
				$hash
			)
		);
		$decoded = json_decode( (string) $payload, true );
		return is_array( $decoded ) ? $decoded : null;
	}

	/**
	 * Promote a record to confirmed and extend its expiry to the retention setting.
	 *
	 * This is what the destination fires on its form-submit trigger. Idempotent:
	 * re-confirming an already-confirmed record succeeds without changing anything
	 * except an updated context, because networks retry.
	 *
	 * @param string   $token
	 * @param string   $context   Optional short label, e.g. "Enrolment form".
	 * @param int|null $retention Days to keep, or null for unlimited.
	 * @param string   $via       Which path reported it: 'confirm' (a destination
	 *                            told us) or 'submission' (a form on this site).
	 */
	public static function save( $token, $context, $retention_days, $via = self::VIA_CONFIRM ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		$table = self::table_name();
		$hash  = self::hash_token( $token );
		$now   = current_time( 'mysql', true );

		// Same reasoning as refresh(): a repeat confirm writes identical values, so
		// affected-rows would be 0 and the caller would see a spurious 404. Confirm
		// has to be idempotent — the destination's tag manager will retry.
		//
		// The payload is read here and not only the id, because this is the one
		// moment the record holds the journey AS IT WAS WHEN THEY CONVERTED. See
		// conversion_snapshot().
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				"SELECT id, payload, confirmed_at, conversions, visitor_key_hash, merged_into FROM {$table} WHERE token_hash = %s AND expires_at > %s",
				$hash,
				$now
			),
			ARRAY_A
		);
		if ( ! $row ) {
			return false;
		}

		// Same person, enquiring again? Then this is not a new lead — it joins the
		// journey it belongs to. Checked before the promotion below, because a
		// merged submission must never become a SAVED record of its own.
		$anchor = self::merge_target( $row );
		if ( $anchor ) {
			return self::merge_conversion( $anchor, $row, $context, $via, $now, $retention_days );
		}

		$expires = ( null === $retention_days )
			? self::NEVER
			: gmdate( 'Y-m-d H:i:s', time() + ( absint( $retention_days ) * DAY_IN_SECONDS ) );

		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				"UPDATE {$table} SET status = %s, confirmed_at = COALESCE(confirmed_at, %s), context = %s, expires_at = %s WHERE id = %d",
				self::STATUS_SAVED,
				$now,
				( '' === $context ) ? null : $context,
				$expires,
				(int) $row['id']
			)
		);

		self::record_conversion( $row, $context, $via, $now );
		return true;
	}

	/**
	 * The journey this submission belongs to, if the person already has one.
	 *
	 * **Identity here is the visitor key** — the cookie already on the device —
	 * and not the email address. The email is the honest answer to "same person",
	 * but it is only known when identity capture is on and it arrives AFTER the
	 * record is saved, so using it would mean retro-merging two journeys that
	 * already exist and rewriting a lead somebody may have read. The key is known
	 * at exactly the moment the decision has to be made. The cost is a shared
	 * device: two people on one laptop become one journey.
	 *
	 * **Only a SAVED record is a merge target.** A held record is a maybe, not a
	 * lead. And a row already merged is never a target, so an alias cannot point
	 * at an alias.
	 *
	 * @return array|null id, conversions, expires_at of the surviving record.
	 */
	private static function merge_target( $row ) {
		if ( empty( $row['visitor_key_hash'] ) || ! empty( $row['merged_into'] ) ) {
			return null;
		}
		// Already a lead in its own right — a retry of the same submission, not a
		// new one. record_conversion() handles that case.
		if ( ! empty( $row['confirmed_at'] ) ) {
			return null;
		}

		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT id, conversions, expires_at FROM ' . self::table_name()
					. ' WHERE visitor_key_hash = %s AND status = %s AND id <> %d AND expires_at > %s'
					. ' ORDER BY id ASC LIMIT 1',
				$row['visitor_key_hash'],
				self::STATUS_SAVED,
				(int) $row['id'],
				current_time( 'mysql', true )
			),
			ARRAY_A
		);
	}

	/**
	 * Attach this submission to the journey the person already has.
	 *
	 * What the reports see does not change: the anchor was already one lead and
	 * is still one lead. **Credit does not move** — the first conversion is the
	 * one that won them, and a later enquiry has the standing of a pageview.
	 * Somebody using a contact form to ask three follow-up questions has not been
	 * acquired four times, and crediting whatever brought them back for each
	 * would inflate precisely the paid channels this plugin exists to keep honest.
	 *
	 * **The browsing history follows the update mode; the conversion does not.**
	 * A site set to never update a saved journey keeps its frozen pageviews — but
	 * still learns that the person enquired again, because those are two different
	 * questions and only the first is what that setting answers.
	 *
	 * The submission's own row is retired as an alias rather than deleted; see
	 * STATUS_MERGED for why the token has to keep resolving.
	 */
	private static function merge_conversion( $anchor, $row, $context, $via, $now, $retention_days ) {
		global $wpdb;
		$table     = self::table_name();
		$anchor_id = (int) $anchor['id'];

		$stored = json_decode( (string) $anchor['conversions'], true );
		$list   = is_array( $stored ) ? $stored : array();
		$list[] = self::conversion_snapshot( $row['payload'], $context, $via, $now );

		$wpdb->update(
			$table,
			array( 'conversions' => wp_json_encode( self::trim_conversions( $list ) ), 'last_seen_at' => $now ),
			array( 'id' => $anchor_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		// The browser's copy is cumulative, so this payload is a superset of the
		// anchor's — everything it knew plus everything since.
		if ( BW_Lead_AI_Settings::journey_updates_enabled() ) {
			self::append_to_anchor( $anchor_id, json_decode( (string) $row['payload'], true ) );
		}
		self::extend_retention( $anchor_id, $retention_days );

		// Retire the submission's own row. Its expiry is pinned to the anchor's so
		// the link handed to a CRM outlives nothing the journey itself outlives.
		$expires = $wpdb->get_var( $wpdb->prepare( "SELECT expires_at FROM {$table} WHERE id = %d", $anchor_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
		$wpdb->update(
			$table,
			array(
				'status'      => self::STATUS_MERGED,
				'merged_into' => $anchor_id,
				'payload'     => '',
				'expires_at'  => $expires ? $expires : $anchor['expires_at'],
			),
			array( 'id' => (int) $row['id'] ),
			array( '%s', '%d', '%s', '%s' ),
			array( '%d' )
		);

		return true;
	}

	/**
	 * Bound the list without losing the one entry that matters.
	 *
	 * Entry zero is what the reports credit and where the lead's date comes from,
	 * so trimming from the front — the obvious keep-the-last-N slice — would
	 * quietly re-attribute a lead the twenty-first time somebody used the contact
	 * form. The first is kept and the oldest of the rest goes.
	 */
	private static function trim_conversions( $list ) {
		$list = array_values( $list );
		if ( count( $list ) <= self::MAX_CONVERSIONS ) {
			return $list;
		}
		return array_merge( array( $list[0] ), array_slice( $list, - ( self::MAX_CONVERSIONS - 1 ) ) );
	}

	/**
	 * Write down that a submission happened, and what had brought them here when
	 * it did.
	 *
	 * **Its own column, not the payload, and that is the whole point.** The update
	 * beacon replaces `payload` wholesale with whatever the browser currently
	 * holds (append_to_anchor()), so anything the server writes into it survives
	 * only until the visitor reads their next page. A journey in `growing` mode is
	 * therefore the LEAST able to remember its own conversion, which is exactly
	 * backwards. This column the browser cannot reach at all.
	 *
	 * Recorded once, on the first save. A confirm is idempotent because tag
	 * managers retry, and a retry is the same submission — `confirmed_at` having
	 * been NULL is what tells the two apart, since the UPDATE above only ever
	 * COALESCEs into it. A retry that carries a context the first attempt lacked
	 * still fills that in, because that is new information rather than a new event.
	 *
	 * @param array  $row     id, payload, confirmed_at, conversions, as read in save().
	 * @param string $context The form's name, where the reporter gave us one.
	 * @param string $via     self::VIA_CONFIRM or self::VIA_SUBMISSION.
	 * @param string $now     GMT mysql datetime.
	 */
	private static function record_conversion( $row, $context, $via, $now ) {
		$stored = json_decode( (string) $row['conversions'], true );
		$list   = is_array( $stored ) ? $stored : array();

		if ( ! empty( $row['confirmed_at'] ) ) {
			// Already converted. A retry, or a record saved before this column
			// existed — reprocess is what gives the second kind its conversion.
			$at = count( $list ) - 1;
			if ( $at < 0 || '' === $context || '' !== (string) $list[ $at ]['context'] ) {
				return;
			}
			$list[ $at ]['context'] = $context;
		} else {
			$list[] = self::conversion_snapshot( $row['payload'], $context, $via, $now );
			$list   = self::trim_conversions( $list );
		}

		global $wpdb;
		$wpdb->update(
			self::table_name(),
			array( 'conversions' => wp_json_encode( array_values( $list ) ) ),
			array( 'id' => (int) $row['id'] ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * The journey as it stood at the moment of the submission.
	 *
	 * Two things are frozen here, and both exist because a journey does not stop
	 * when the form is sent:
	 *
	 * **Where to put it in the timeline.** `visit_ts` is the browser clock of the
	 * visit that was in progress, taken from the journey's own `last`, and
	 * `after_ts` the last thing the visitor did before it. Anchoring to the
	 * visitor's own record rather than comparing the server's clock to the
	 * browser's is deliberate — the two are not the same clock, and a visitor
	 * whose device is an hour out would otherwise have their submission filed
	 * under the wrong visit entirely. The server's own time is kept alongside,
	 * for display.
	 *
	 * `after_ts` is what puts the submission in the MIDDLE of a visit rather than
	 * always at the end of it, which is the case worth seeing: someone who leaves
	 * for the form, comes back, submits, and then keeps reading.
	 *
	 * **Who gets the credit.** The last-click triple, campaign and term as they
	 * were. Without this, a visitor who submits on a social visit and comes back
	 * a week later through a paid ad hands that ad the credit for a conversion
	 * that had already happened — last click means the last touch BEFORE the
	 * conversion, and a visit that came after it cannot have caused it. First
	 * click is not snapshotted: the first visit cannot change.
	 *
	 * @param string $payload_json The stored payload column, still encoded.
	 */
	private static function conversion_snapshot( $payload_json, $context, $via, $now ) {
		$payload = json_decode( (string) $payload_json, true );
		$payload = is_array( $payload ) ? $payload : array();

		$journey = array();
		if ( ! empty( $payload['journey'] ) ) {
			$decoded = json_decode( (string) $payload['journey'], true );
			$journey = is_array( $decoded ) ? $decoded : array();
		}
		$last = ( isset( $journey['last'] ) && is_array( $journey['last'] ) ) ? $journey['last'] : array();

		// The last thing the visitor did that we know about. Everything in the
		// journey happened before this submission, by definition — the browser
		// sent the blob and then the form was sent. Pages AND interactions,
		// because the last thing before a cross-domain submission is usually the
		// click that left for the form, not the page it was clicked on.
		$after_ts = 0;
		foreach ( array( 'views', 'events' ) as $bag ) {
			if ( empty( $journey[ $bag ] ) || ! is_array( $journey[ $bag ] ) ) {
				continue;
			}
			foreach ( $journey[ $bag ] as $entry ) {
				$ts = ( is_array( $entry ) && isset( $entry['ts'] ) ) ? (int) $entry['ts'] : 0;
				if ( $ts > $after_ts ) {
					$after_ts = $ts;
				}
			}
		}

		$out = array(
			'ts'       => (string) $now,
			'visit_ts' => isset( $last['ts'] ) ? (int) $last['ts'] : 0,
			'after_ts' => $after_ts,
			'context'  => (string) $context,
			'via'      => ( self::VIA_SUBMISSION === $via ) ? self::VIA_SUBMISSION : self::VIA_CONFIRM,
		);
		foreach ( array( 'channel', 'source', 'medium', 'campaign', 'term' ) as $key ) {
			$out[ $key ] = self::attribution_value( $payload, $last, $key );
		}
		return $out;
	}

	/**
	 * One attribution field, read the way the reports read it: the flat datapoint
	 * if the site stores it, otherwise the journey's own last visit. Which flat
	 * keys exist is a per-site setting, so neither source can be assumed.
	 */
	private static function attribution_value( $payload, $last, $key ) {
		foreach ( array( $payload, $last ) as $source ) {
			if ( isset( $source[ $key ] ) && ! is_array( $source[ $key ] ) && '' !== (string) $source[ $key ] ) {
				return substr( (string) $source[ $key ], 0, 200 );
			}
		}
		return '';
	}

	/**
	 * Attach who the visitor turned out to be, plus optionally what they submitted.
	 *
	 * **Deliberately stored in dedicated columns, not in `payload`.** The claim
	 * endpoint only ever reads `payload`, so keeping identity out of it makes it
	 * structurally impossible to hand a name, email or form answer to a third-party
	 * destination — it is not a setting anyone can mis-tick, it simply is not
	 * reachable from that code path.
	 *
	 * Both arguments are already-sanitised arrays; see BW_Lead_AI_Identity.
	 *
	 * @param string     $token
	 * @param array      $identity   first, last, email, phone.
	 * @param array|null $submission List of label/value pairs, or null to skip.
	 */
	public static function attach_identity( $token, $identity, $submission = null ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		$table = self::table_name();

		// Through the alias, not onto it. Identity arrives after save() — from the
		// GF hook, or from the destination's confirm — carrying the token of a
		// submission that may just have been merged into an earlier journey, and
		// a name written to the retired row is a name nobody ever sees.
		$id = self::live_id_for_token( $token );
		if ( ! $id ) {
			return false;
		}

		$data   = array();
		$format = array();
		if ( ! empty( $identity ) ) {
			$data['identity'] = wp_json_encode( $identity );
			$format[]         = '%s';
		}
		if ( null !== $submission ) {
			$data['submission'] = wp_json_encode( $submission );
			$format[]           = '%s';
		}
		if ( empty( $data ) ) {
			return false;
		}

		$wpdb->update( $table, $data, array( 'id' => (int) $id ), $format, array( '%d' ) );
		return true;
	}

	/**
	 * Decode the JSON columns on a row read from the database.
	 */
	/**
	 * Follow a merged record to the one that now holds its journey.
	 *
	 * A single hop, never a chain: a merge always targets a SAVED record and a
	 * merged row is no longer SAVED, so a target cannot itself have been merged.
	 * The single `if` is deliberate rather than a loop — if that invariant ever
	 * broke, a loop would hang the request and this returns the alias instead,
	 * which is visibly wrong on screen and harmless.
	 */
	private static function follow_alias( $row ) {
		if ( empty( $row['merged_into'] ) ) {
			return $row;
		}
		global $wpdb;
		$target = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT * FROM ' . self::table_name() . ' WHERE id = %d AND expires_at > %s',
				(int) $row['merged_into'],
				current_time( 'mysql', true )
			),
			ARRAY_A
		);
		return $target ? $target : $row;
	}

	/**
	 * The id anything written after a save should land on.
	 *
	 * Identity arrives AFTER save() — from the GF hook, or from the destination's
	 * confirm — carrying the token of the submission that has just been merged
	 * away. Written to that row it would attach a name to an alias nobody reads.
	 */
	private static function live_id_for_token( $token ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT id, merged_into FROM ' . self::table_name() . ' WHERE token_hash = %s AND expires_at > %s',
				self::hash_token( $token ),
				current_time( 'mysql', true )
			),
			ARRAY_A
		);
		if ( ! $row ) {
			return 0;
		}
		return empty( $row['merged_into'] ) ? (int) $row['id'] : (int) $row['merged_into'];
	}

	private static function hydrate( $row ) {
		foreach ( array( 'payload', 'identity', 'submission', 'conversions' ) as $key ) {
			if ( ! isset( $row[ $key ] ) || null === $row[ $key ] || '' === $row[ $key ] ) {
				$row[ $key ] = array();
				continue;
			}
			$decoded     = json_decode( (string) $row[ $key ], true );
			$row[ $key ] = is_array( $decoded ) ? $decoded : array();
		}
		return $row;
	}

	/**
	 * Full record for the logged-in viewer. Unlike claim(), this does not burn the
	 * token — a marketer may open the same link repeatedly, and the data never
	 * leaves the site here.
	 */
	public static function get_record( $token ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return null;
		}
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT * FROM ' . self::table_name() . ' WHERE token_hash = %s AND expires_at > %s',
				self::hash_token( $token ),
				current_time( 'mysql', true )
			),
			ARRAY_A
		);
		if ( ! $row ) {
			return null;
		}
		// A link handed out for a submission that has since been merged still
		// opens the journey it became part of. See STATUS_MERGED.
		return self::hydrate( self::follow_alias( $row ) );
	}

	public static function delete( $token ) {
		if ( ! self::is_token_shaped( $token ) || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		return (bool) $wpdb->delete( self::table_name(), array( 'token_hash' => self::hash_token( $token ) ), array( '%s' ) );
	}

	/**
	 * How many pending records exist. Used as a global abuse ceiling so a flood
	 * cannot fill the table even if per-IP limits are evaded across many addresses.
	 */
	public static function held_count() {
		if ( ! self::table_exists() ) {
			return 0;
		}
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT COUNT(*) FROM ' . self::table_name() . ' WHERE status = %s AND expires_at > %s',
				self::STATUS_HELD,
				current_time( 'mysql', true )
			)
		);
	}

	/**
	 * Delete expired records. Expiry is ALSO enforced at read time in every lookup
	 * above, so a late or missing cron run is a housekeeping problem, never a
	 * security one.
	 */
	public static function prune() {
		if ( ! self::table_exists() ) {
			return 0;
		}
		global $wpdb;
		return (int) $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'DELETE FROM ' . self::table_name() . ' WHERE expires_at <= %s',
				current_time( 'mysql', true )
			)
		);
	}

	public static function purge_all() {
		if ( ! self::table_exists() ) {
			return 0;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
		return (int) $wpdb->query( 'DELETE FROM ' . self::table_name() );
	}

	/**
	 * Recent records for the browse list.
	 *
	 * Returns the token_hash rather than a token — the plain token exists only in
	 * the destination's stored link and is never recoverable from here, by design.
	 * The list therefore links by hash and the viewer accepts either form.
	 *
	 * @param array $args status ('any'|'confirmed'|'pending'), per_page, page, search.
	 */
	public static function list_records( $args = array() ) {
		if ( ! self::table_exists() ) {
			return array( 'rows' => array(), 'total' => 0 );
		}
		$args = wp_parse_args(
			$args,
			array( 'status' => self::STATUS_SAVED, 'per_page' => 25, 'page' => 1, 'search' => '' )
		);

		global $wpdb;
		$table  = self::table_name();
		$now    = current_time( 'mysql', true );
		$where  = array( 'expires_at > %s' );
		$params = array( $now );

		if ( in_array( $args['status'], array( self::STATUS_SAVED, self::STATUS_HELD ), true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}
		if ( '' !== $args['search'] ) {
			$where[]  = '( payload LIKE %s OR context LIKE %s )';
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$params[] = $like;
			$params[] = $like;
		}
		$where_sql = 'WHERE ' . implode( ' AND ', $where );

		$total = (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix; values are prepared.
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} {$where_sql}", $params )
		);

		$per_page = max( 1, min( 200, (int) $args['per_page'] ) );
		$offset   = max( 0, ( max( 1, (int) $args['page'] ) - 1 ) * $per_page );

		$rows = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix; values are prepared.
			$wpdb->prepare(
				"SELECT id, token_hash, payload, identity, status, context, created_at, confirmed_at, expires_at, data_claimed_at
				 FROM {$table} {$where_sql}
				 ORDER BY COALESCE(confirmed_at, created_at) DESC
				 LIMIT %d OFFSET %d",
				array_merge( $params, array( $per_page, $offset ) )
			),
			ARRAY_A
		);

		foreach ( $rows as $i => $row ) {
			$rows[ $i ] = self::hydrate( $row );
		}

		return array( 'rows' => $rows, 'total' => $total );
	}

	/**
	 * Look a record up by its stored hash — used by the browse list, where the
	 * plain token isn't available.
	 */
	public static function get_record_by_hash( $hash ) {
		if ( ! preg_match( '/^[a-f0-9]{64}$/', (string) $hash ) || ! self::table_exists() ) {
			return null;
		}
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT * FROM ' . self::table_name() . ' WHERE token_hash = %s AND expires_at > %s',
				$hash,
				current_time( 'mysql', true )
			),
			ARRAY_A
		);
		if ( ! $row ) {
			return null;
		}
		return self::hydrate( self::follow_alias( $row ) );
	}

	// --- visitor key (continuity across sessions) -------------------------

	/**
	 * Find the anchor for a visitor key.
	 *
	 * The key is stored hashed for the same reason the handoff token is: a database
	 * dump should not hand anyone a working key.
	 *
	 * A SAVED record wins over a newer held one. The same browser routinely holds
	 * both: right after an on-site save, the next page's revalidate call declines
	 * to refresh the now-saved token and mints a fresh held record, binding it to
	 * the same key. Newest-first would hand the beacon that provisional record —
	 * which it refuses to grow — and the post-submission pageviews this exists to
	 * capture would be dropped on the floor. The journey someone decided to keep
	 * is the one their later browsing belongs to.
	 */
	public static function anchor_for_key( $visitor_key ) {
		if ( ! self::is_token_shaped( $visitor_key ) || ! self::table_exists() ) {
			return null;
		}
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'SELECT * FROM ' . self::table_name() . ' WHERE visitor_key_hash = %s AND expires_at > %s ORDER BY CASE WHEN status = %s THEN 1 ELSE 0 END DESC, id DESC LIMIT 1',
				self::hash_token( $visitor_key ),
				current_time( 'mysql', true ),
				self::STATUS_SAVED
			),
			ARRAY_A
		);
		return $row ? self::hydrate( $row ) : null;
	}

	/** Bind a visitor key to a record, so later visits find it again. */
	public static function attach_visitor_key( $token, $visitor_key ) {
		if ( ! self::is_token_shaped( $token ) || ! self::is_token_shaped( $visitor_key ) || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		return (bool) $wpdb->update(
			self::table_name(),
			array( 'visitor_key_hash' => self::hash_token( $visitor_key ), 'last_seen_at' => current_time( 'mysql', true ) ),
			array( 'token_hash' => self::hash_token( $token ) ),
			array( '%s', '%s' ),
			array( '%s' )
		);
	}

	/**
	 * Append newer journey data to an existing record.
	 *
	 * This is what the return-visit beacon calls. It replaces the stored payload with
	 * the fuller one the browser now holds — the browser's copy is cumulative, so the
	 * newer payload is a superset rather than a delta to splice.
	 *
	 * Deliberately returns nothing about the record. The visitor key authorises
	 * appending to your own journey and nothing else; if this ever returned journey or
	 * identity data it would quietly become a read token that any script on the page
	 * could exploit.
	 */
	public static function append_to_anchor( $anchor_id, $payload ) {
		if ( ! $anchor_id || ! self::table_exists() ) {
			return false;
		}
		global $wpdb;
		$wpdb->update(
			self::table_name(),
			array( 'payload' => wp_json_encode( $payload ), 'last_seen_at' => current_time( 'mysql', true ) ),
			array( 'id' => (int) $anchor_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		return true;
	}

	/**
	 * Slide a saved record's expiry forward from now.
	 *
	 * Retention is measured from last contact, not from the original save, so an
	 * actively-returning visitor is not deleted mid-relationship.
	 */
	public static function extend_retention( $anchor_id, $retention_days ) {
		if ( ! $anchor_id || ! self::table_exists() ) {
			return false;
		}
		$expires = ( null === $retention_days )
			? self::NEVER
			: gmdate( 'Y-m-d H:i:s', time() + ( absint( $retention_days ) * DAY_IN_SECONDS ) );

		global $wpdb;
		$wpdb->update( self::table_name(), array( 'expires_at' => $expires ), array( 'id' => (int) $anchor_id ), array( '%s' ), array( '%d' ) );
		return true;
	}

	/**
	 * Opportunistic pruning, run on write rather than only from wp-cron.
	 *
	 * wp-cron is unreliable — it is disabled or rewired on plenty of hosts — and a
	 * prune that silently never runs turns a bounded table into an unbounded one.
	 * Bounded per call so it never becomes the slow part of a request.
	 */
	public static function prune_inline( $limit = 50 ) {
		if ( ! self::table_exists() ) {
			return 0;
		}
		global $wpdb;
		return (int) $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
				'DELETE FROM ' . self::table_name() . ' WHERE expires_at <= %s LIMIT %d',
				current_time( 'mysql', true ),
				(int) $limit
			)
		);
	}

	public static function stats() {
		if ( ! self::table_exists() ) {
			return array( 'held' => 0, 'saved' => 0 );
		}
		global $wpdb;
		$table = self::table_name();
		$now   = current_time( 'mysql', true );
		return array(
			'held'      => self::held_count(),
			'saved'     => (int) $wpdb->get_var(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
					"SELECT COUNT(*) FROM {$table} WHERE status = %s AND expires_at > %s",
					self::STATUS_SAVED,
					$now
				)
			),
		);
	}
}