← All changes | includes/class-mxchat-session-store.php +286 -8 3.2.18 → 3.2.24 View file →

		@@ -14,12 +14,16 @@
14	14	* This class replaces those rows with one indexed row per session in a
15	15	* dedicated table, plus a retention sweep that does not depend on WP-Cron
16	16	* (WP-Cron is disabled on some installs; see plan-mxchat-20260805-b64b77).
17	17	*
18		- * Fields owned by this store: owner, originating_page, mode, channel.
19		- * NOT yet owned (still wp_options, deliberately — mxchat-forms and
20		- * mxchat-woo co-own the reads/writes and must move in the same change):
21		- * mxchat_name_, mxchat_email_, mxchat_history_.
	18	+ * Fields owned by this store: owner, originating_page, mode, channel, plus
	19	+ * (since schema v2 / plan 5658f2) the visitor identity keys name, email and
	20	+ * agent_name — moved atomically with the mxchat-forms and mxchat-embed
	21	+ * writers so no plugin half writes options while another reads the table.
	22	+ * Chat history is deliberately NOT here either: plan 839c4c deduplicated the
	23	+ * mxchat_history_ options into the transcripts table (their content was a
	24	+ * second copy of it all along) — MxChat_Utils::get_session_history() is the
	25	+ * accessor.
22	26	*
23	27	* @package MxChat
24	28	*/
25	29
		@@ -31,8 +35,23 @@
31	35
32	36	/** Table name without the wpdb prefix. */
33	37	const TABLE_SUFFIX = 'mxchat_sessions';
34	38
	39	+ /**
	40	+ * Bump when create_table()'s schema changes. ensure_table() compares the
	41	+ * READY option against this, so existing installs re-run dbDelta once and
	42	+ * pick up new columns ('1' = b64b77 original, '2' = 5658f2 identity
	43	+ * columns, '3' = b062c4 consent record).
	44	+ */
	45	+ const SCHEMA_VERSION = '3';
	46	+
	47	+ /**
	48	+ * Bump when $legacy_prefixes gains entries. A stale version in the stored
	49	+ * migration state resets the bookmark so the new prefixes get a full
	50	+ * re-scan (the migration upsert is a no-op on already-moved rows).
	51	+ */
	52	+ const MIGRATION_VERSION = 2;
	53	+
35	54	/** Batched-migration bookmark. Non-autoloaded. */
36	55	const MIGRATION_OPTION = 'mxchat_session_store_migration';
37	56
38	57	/** Timestamp of the last retention sweep. Autoloaded (tiny, read on writes). */
		@@ -64,8 +83,16 @@
64	83	'owner' => 'owner',
65	84	'originating_page' => 'originating_page',
66	85	'mode' => 'mode',
67	86	'channel' => 'channel',
	87	+ 'name' => 'visitor_name',
	88	+ 'email' => 'visitor_email',
	89	+ 'agent_name' => 'agent_name',
	90	+ // Consent record (b062c4). Never lived in wp_options, so no legacy
	91	+ // prefixes: legacy_get() correctly falls through to the default.
	92	+ 'consent' => 'consent_given',
	93	+ 'consent_label' => 'consent_label',
	94	+ 'consent_at' => 'consent_recorded_at',
68	95	);
69	96
70	97	/** Logical field => the wp_options prefix it used to live under. */
71	98	private static $legacy_prefixes = array(
		@@ -72,8 +99,11 @@
72	99	'owner' => 'mxchat_session_owner_',
73	100	'originating_page' => 'mxchat_originating_page_',
74	101	'mode' => 'mxchat_mode_',
75	102	'channel' => 'mxchat_channel_',
	103	+ 'name' => 'mxchat_name_',
	104	+ 'email' => 'mxchat_email_',
	105	+ 'agent_name' => 'mxchat_agent_name_',
76	106	);
77	107
78	108	/** Fields stored as JSON rather than a scalar. */
79	109	private static $json_fields = array('originating_page');
		@@ -129,8 +159,14 @@
129	159	owner varchar(191) DEFAULT NULL,
130	160	originating_page longtext DEFAULT NULL,
131	161	mode varchar(32) DEFAULT NULL,
132	162	channel varchar(191) DEFAULT NULL,
	163	+ visitor_name varchar(191) DEFAULT NULL,
	164	+ visitor_email varchar(191) DEFAULT NULL,
	165	+ agent_name varchar(191) DEFAULT NULL,
	166	+ consent_given varchar(8) DEFAULT NULL,
	167	+ consent_label text DEFAULT NULL,
	168	+ consent_recorded_at datetime DEFAULT NULL,
133	169	created_at datetime DEFAULT NULL,
134	170	updated_at datetime DEFAULT NULL,
135	171	PRIMARY KEY (session_id),
136	172	KEY updated_at (updated_at)
		@@ -140,9 +176,9 @@
140	176	dbDelta($sql);
141	177
142	178	$exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) === $table;
143	179	if ($exists) {
144		- update_option(self::READY_OPTION, '1', 'yes');
	180	+ update_option(self::READY_OPTION, self::SCHEMA_VERSION, 'yes');
145	181	self::$table_ready = true;
146	182	}
147	183
148	184	return $exists;
		@@ -156,9 +192,12 @@
156	192	if (self::$table_ready) {
157	193	return true;
158	194	}
159	195
160		- if (get_option(self::READY_OPTION) === '1') {
	196	+ // The stored value is the schema version the table was last built at.
	197	+ // Anything else (including b64b77's '1') re-runs create_table() once —
	198	+ // dbDelta adds the missing columns to existing installs.
	199	+ if (get_option(self::READY_OPTION) === self::SCHEMA_VERSION) {
161	200	self::$table_ready = true;
162	201	return true;
163	202	}
164	203
		@@ -164,8 +203,18 @@
164	203
165	204	return self::create_table();
166	205	}
167	206
	207	+ /**
	208	+ * Feature probe for add-ons: does this core's store handle $field?
	209	+ * mxchat-forms / mxchat-embed call this before routing identity writes to
	210	+ * the table, falling back to the legacy option write against an older
	211	+ * core whose allowlist would silently reject the field.
	212	+ */
	213	+ public static function supports($field) {
	214	+ return isset(self::$columns[$field]);
	215	+ }
	216	+
168	217	/* ---------------------------------------------------------------------
169	218	* Accessors
170	219	* ------------------------------------------------------------------ */
171	220
		@@ -317,8 +366,14 @@
317	366	foreach (self::$legacy_prefixes as $prefix) {
318	367	delete_option($prefix . $session_id);
319	368	}
320	369
	370	+ // d0cae1: integrations holding per-session state outside WP purge on
	371	+ // this. Fires on every ended session — the retention sweep in trim()
	372	+ // fires it too with reason 'retention'; an integration that only
	373	+ // listens to one path leaks state on the other.
	374	+ do_action('mxchat_session_ended', $session_id, array('reason' => 'deleted'));
	375	+
321	376	return true;
322	377	}
323	378
324	379	/**
		@@ -350,8 +405,169 @@
350	405
351	406	return is_string($session_id) ? $session_id : '';
352	407	}
353	408
	409	+ /**
	410	+ * Sessions whose stored visitor email matches (collation-insensitive, same
	411	+ * as the option sweeps' strtolower compare). The lead-wipe and GDPR
	412	+ * lingering sweeps use this as the table-side twin of their
	413	+ * "option_name LIKE 'mxchat_email_%'" scans (5658f2).
	414	+ *
	415	+ * @param string $email
	416	+ * @return string[] Session ids, possibly empty.
	417	+ */
	418	+ public static function find_by_email($email) {
	419	+ global $wpdb;
	420	+
	421	+ $email = is_scalar($email) ? trim((string) $email) : '';
	422	+ if ($email === '' || !self::ensure_table()) {
	423	+ return array();
	424	+ }
	425	+
	426	+ $table = self::table();
	427	+
	428	+ $sids = $wpdb->get_col(
	429	+ $wpdb->prepare("SELECT session_id FROM `$table` WHERE visitor_email = %s", $email)
	430	+ );
	431	+
	432	+ return is_array($sids) ? $sids : array();
	433	+ }
	434	+
	435	+ /**
	436	+ * Every session row holding a visitor email — the table-side source for
	437	+ * the Leads tab's orphan bucket (pre-chat captures with no transcript
	438	+ * rows). Bounded by lead count, not session count: identity columns are
	439	+ * only ever set by the capture paths.
	440	+ *
	441	+ * @return array[] Rows of [session_id, visitor_email, visitor_name].
	442	+ */
	443	+ /**
	444	+ * Sessions carrying visitor identity.
	445	+ *
	446	+ * @param bool $include_nameless_email Also return sessions that have a
	447	+ * NAME but no email address. Off by default, so every existing
	448	+ * caller sees exactly the rows it always has; the Leads tab opts in
	449	+ * when the owner has made the email field optional (c0cfaf), where
	450	+ * a name-only capture is a real lead rather than a half-filled one.
	451	+ */
	452	+ public static function identity_rows($include_nameless_email = false) {
	453	+ global $wpdb;
	454	+
	455	+ if (!self::ensure_table()) {
	456	+ return array();
	457	+ }
	458	+
	459	+ $table = self::table();
	460	+
	461	+ $where = "visitor_email IS NOT NULL AND visitor_email != ''";
	462	+ if ($include_nameless_email) {
	463	+ $where = "($where) OR (visitor_name IS NOT NULL AND visitor_name != '')";
	464	+ }
	465	+
	466	+ $rows = $wpdb->get_results(
	467	+ "SELECT session_id, visitor_email, visitor_name FROM `$table`
	468	+ WHERE $where",
	469	+ ARRAY_A
	470	+ );
	471	+
	472	+ return is_array($rows) ? $rows : array();
	473	+ }
	474	+
	475	+ /**
	476	+ * Record the lead-capture consent decision in one atomic upsert (b062c4).
	477	+ *
	478	+ * Three facts travel together — whether the box was ticked, when, and the
	479	+ * exact label text the visitor saw — because a "yes" without "to what"
	480	+ * proves nothing once the owner rewrites the label. One query rather than
	481	+ * three set() calls so a crash can never store half a consent record.
	482	+ *
	483	+ * Rows written here always also carry visitor_email (the capture endpoint
	484	+ * stores the email in the same request), so the retention sweep's identity
	485	+ * guard keeps consent records exactly as long as it keeps the lead.
	486	+ *
	487	+ * @param string $session_id
	488	+ * @param bool $given True when the box was ticked.
	489	+ * @param string $label The sanitized label markup as rendered to the visitor.
	490	+ * @return bool True when the write was attempted against a live table.
	491	+ */
	492	+ public static function record_consent($session_id, $given, $label) {
	493	+ global $wpdb;
	494	+
	495	+ $session_id = self::sanitize($session_id);
	496	+ if ($session_id === '' || !self::ensure_table()) {
	497	+ return false;
	498	+ }
	499	+
	500	+ $table = self::table();
	501	+ $now = current_time('mysql');
	502	+
	503	+ $wpdb->query(
	504	+ $wpdb->prepare(
	505	+ "INSERT INTO `$table` (session_id, consent_given, consent_label, consent_recorded_at, created_at, updated_at)
	506	+ VALUES (%s, %s, %s, %s, %s, %s)
	507	+ ON DUPLICATE KEY UPDATE
	508	+ consent_given = VALUES(consent_given),
	509	+ consent_label = VALUES(consent_label),
	510	+ consent_recorded_at = VALUES(consent_recorded_at),
	511	+ updated_at = VALUES(updated_at)",
	512	+ $session_id,
	513	+ $given ? 'yes' : 'no',
	514	+ (string) $label,
	515	+ $now,
	516	+ $now,
	517	+ $now
	518	+ )
	519	+ );
	520	+
	521	+ unset(self::$cache[$session_id]);
	522	+ self::maybe_trim();
	523	+
	524	+ return true;
	525	+ }
	526	+
	527	+ /**
	528	+ * Newest recorded consent for a lead email, across all their sessions.
	529	+ * The Leads tab and CSV export read this — a lead is aggregated by email
	530	+ * while consent is captured per session, so "the lead's consent state" is
	531	+ * the most recent record. NULL result means NOT RECORDED, which the
	532	+ * callers must render as exactly that ("not recorded" and "no consent"
	533	+ * are different facts).
	534	+ *
	535	+ * @param string $email
	536	+ * @return array|null ['given' => 'yes'|'no', 'label' => string, 'recorded_at' => string] or null.
	537	+ */
	538	+ public static function latest_consent($email) {
	539	+ global $wpdb;
	540	+
	541	+ $email = is_scalar($email) ? trim((string) $email) : '';
	542	+ if ($email === '' || !self::ensure_table()) {
	543	+ return null;
	544	+ }
	545	+
	546	+ $table = self::table();
	547	+
	548	+ $row = $wpdb->get_row(
	549	+ $wpdb->prepare(
	550	+ "SELECT consent_given, consent_label, consent_recorded_at
	551	+ FROM `$table`
	552	+ WHERE visitor_email = %s AND consent_given IS NOT NULL AND consent_given != ''
	553	+ ORDER BY consent_recorded_at DESC LIMIT 1",
	554	+ $email
	555	+ ),
	556	+ ARRAY_A
	557	+ );
	558	+
	559	+ if (!is_array($row)) {
	560	+ return null;
	561	+ }
	562	+
	563	+ return array(
	564	+ 'given' => (string) $row['consent_given'],
	565	+ 'label' => is_string($row['consent_label']) ? $row['consent_label'] : '',
	566	+ 'recorded_at' => is_string($row['consent_recorded_at']) ? $row['consent_recorded_at'] : '',
	567	+ );
	568	+ }
	569	+
354	570	private static function row($session_id) {
355	571	if (array_key_exists($session_id, self::$cache)) {
356	572	return self::$cache[$session_id];
357	573	}
		@@ -397,9 +613,9 @@
397	613	if (!is_array($state)) {
398	614	$state = array();
399	615	}
400	616
401		- return wp_parse_args(
	617	+ $state = wp_parse_args(
402	618	$state,
403	619	array(
404	620	'done' => false,
405	621	'last_option_id' => 0,
		@@ -404,10 +620,26 @@
404	620	'done' => false,
405	621	'last_option_id' => 0,
406	622	'moved' => 0,
407	623	'sessions' => 0,
	624	+ 'version' => 1, // states written before versioning are b64b77's v1
408	625	)
409	626	);
	627	+
	628	+ // A version bump means $legacy_prefixes gained entries after this
	629	+ // install finished (or started) migrating — reset the bookmark so the
	630	+ // new prefixes get a full pass. Re-processing already-moved rows is a
	631	+ // no-op (COALESCE upsert), so the re-scan is safe. The reset lives
	632	+ // here rather than in migrate_batch() so is_migrated() flips false at
	633	+ // the same moment, which re-arms the legacy_get() read-through for
	634	+ // rows the re-scan has not reached yet.
	635	+ if ((int) $state['version'] !== self::MIGRATION_VERSION) {
	636	+ $state['version'] = self::MIGRATION_VERSION;
	637	+ $state['done'] = false;
	638	+ $state['last_option_id'] = 0;
	639	+ }
	640	+
	641	+ return $state;
410	642	}
411	643
412	644	public static function is_migrated() {
413	645	$state = self::migration_state();
		@@ -610,11 +842,57 @@
610	842	// current_time('mysql') writes site-local time, so compare against a
611	843	// site-local cutoff.
612	844	$cutoff = get_date_from_gmt($cutoff, 'Y-m-d H:i:s');
613	845
	846	+ // Identity guard (5658f2): rows holding a visitor name/email are LEADS
	847	+ // — the Leads tab lists them and the legacy option rows they replace
	848	+ // were never expired. Retention only sweeps anonymous session state;
	849	+ // identity rows leave via the eraser / lead-delete / wipe paths.
	850	+ //
	851	+ // d0cae1: when something listens for ended sessions the swept ids must
	852	+ // be known, so the delete goes SELECT-then-DELETE-by-id. Unhooked, the
	853	+ // original single-statement bulk delete runs unchanged.
	854	+ if (has_action('mxchat_session_ended')) {
	855	+ $swept_ids = $wpdb->get_col(
	856	+ $wpdb->prepare(
	857	+ "SELECT session_id FROM `$table` WHERE updated_at IS NOT NULL AND updated_at < %s
	858	+ AND (visitor_email IS NULL OR visitor_email = '')
	859	+ AND (visitor_name IS NULL OR visitor_name = '')
	860	+ LIMIT %d",
	861	+ $cutoff,
	862	+ $limit
	863	+ )
	864	+ );
	865	+
	866	+ if (empty($swept_ids)) {
	867	+ return 0;
	868	+ }
	869	+
	870	+ $placeholders = implode(',', array_fill(0, count($swept_ids), '%s'));
	871	+ $deleted = $wpdb->query(
	872	+ $wpdb->prepare(
	873	+ "DELETE FROM `$table` WHERE session_id IN ($placeholders)",
	874	+ $swept_ids
	875	+ )
	876	+ );
	877	+
	878	+ if ($deleted) {
	879	+ self::flush_cache();
	880	+ }
	881	+
	882	+ foreach ($swept_ids as $swept_id) {
	883	+ do_action('mxchat_session_ended', $swept_id, array('reason' => 'retention'));
	884	+ }
	885	+
	886	+ return (int) $deleted;
	887	+ }
	888	+
614	889	$deleted = $wpdb->query(
615	890	$wpdb->prepare(
616		- "DELETE FROM `$table` WHERE updated_at IS NOT NULL AND updated_at < %s LIMIT %d",
	891	+ "DELETE FROM `$table` WHERE updated_at IS NOT NULL AND updated_at < %s
	892	+ AND (visitor_email IS NULL OR visitor_email = '')
	893	+ AND (visitor_name IS NULL OR visitor_name = '')
	894	+ LIMIT %d",
617	895	$cutoff,
618	896	$limit
619	897	)
620	898	);