Analytics_Schema.php — EmbedPress – PDF Embedder, Embed PDF viewer, YouTube Videos, 3D FlipBook, Social feeds & more 4.4.9

embedpress / EmbedPress / Includes / Classes / Database / Analytics_Schema.php

embedpress / EmbedPress / Includes / Classes / Database Last commit date

Analytics_Schema.php

513 lines

1	<?php
2
3	namespace EmbedPress\Includes\Classes\Database;
4
5	defined('ABSPATH') or die("No direct script access allowed.");
6
7	/**
8	* EmbedPress Analytics Database Schema
9	*
10	* Handles creation and management of analytics database tables
11	*
12	* @package EmbedPress
13	* @author EmbedPress <help@embedpress.com>
14	* @copyright Copyright (C) 2025 WPDeveloper. All rights reserved.
15	* @license GPLv3 or later
16	* @since 4.4.0
17	*/
18	class Analytics_Schema
19	{
20	/**
21	* Database version for schema updates
22	*/
23	const DB_VERSION = '1.0.8';
24
25	/**
26	* Create all analytics tables
27	*
28	* @return void
29	*/
30	public static function create_tables()
31	{
32
33	global $wpdb;
34
35	$charset_collate = $wpdb->get_charset_collate();
36
37	// Check if tables need to be created or updated
38	$current_version = get_option('embedpress_analytics_db_version', '0.0.0');
39
40	// Also check if tables actually exist in database
41	$tables_exist = self::check_tables_exist();
42
43	if (version_compare($current_version, self::DB_VERSION, '<') \|\| !$tables_exist) {
44
45	self::create_content_table($charset_collate);
46	self::create_views_table($charset_collate);
47	self::create_browser_info_table($charset_collate);
48	self::create_milestones_table($charset_collate);
49	self::create_referrers_table($charset_collate);
50	// Run migrations for existing installations
51
52	// self::run_migrations($current_version);
53
54
55
56	// Clean up old referrer visitor options (no longer needed)
57	self::cleanup_old_referrer_visitor_options();
58
59	// Update database version
60	update_option('embedpress_analytics_db_version', self::DB_VERSION);
61
62	// Log table creation for debugging
63	}
64	}
65
66	/**
67	* Check if all required tables exist
68	*
69	* @return bool
70	*/
71	private static function check_tables_exist()
72	{
73	global $wpdb;
74
75	$required_tables = [
76	$wpdb->prefix . 'embedpress_analytics_content',
77	$wpdb->prefix . 'embedpress_analytics_views',
78	$wpdb->prefix . 'embedpress_analytics_browser_info',
79	$wpdb->prefix . 'embedpress_analytics_milestones',
80	$wpdb->prefix . 'embedpress_analytics_referrers'
81	];
82
83	foreach ($required_tables as $table) {
84	$table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
85	if (!$table_exists) {
86	return false;
87	}
88	}
89
90	return true;
91	}
92
93	/**
94	* Force create all tables (for debugging/repair)
95	*
96	* @return void
97	*/
98	public static function force_create_tables()
99	{
100	global $wpdb;
101
102	$charset_collate = $wpdb->get_charset_collate();
103
104
105	self::create_content_table($charset_collate);
106	self::create_views_table($charset_collate);
107	self::create_browser_info_table($charset_collate);
108	self::create_milestones_table($charset_collate);
109	self::create_referrers_table($charset_collate);
110
111	// Update database version
112	update_option('embedpress_analytics_db_version', self::DB_VERSION);
113
114	}
115
116	/**
117	* Create embedpress_analytics_content table
118	* Tracks embedded content by type (Elementor/Gutenberg/Shortcode)
119	*
120	* @param string $charset_collate
121	* @return void
122	*/
123	private static function create_content_table($charset_collate)
124	{
125	global $wpdb;
126
127	$table_name = $wpdb->prefix . 'embedpress_analytics_content';
128
129	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
130	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
131	content_id varchar(255) NOT NULL,
132	content_type varchar(50) NOT NULL DEFAULT 'unknown',
133	embed_type varchar(100) NOT NULL,
134	embed_url text NOT NULL,
135	post_id bigint(20) unsigned DEFAULT NULL,
136	page_url text DEFAULT NULL,
137	title varchar(500) DEFAULT NULL,
138	total_views bigint(20) unsigned DEFAULT 0,
139	total_impressions bigint(20) unsigned DEFAULT 0,
140	total_clicks bigint(20) unsigned DEFAULT 0,
141	created_at datetime DEFAULT CURRENT_TIMESTAMP,
142	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
143	PRIMARY KEY (id),
144	UNIQUE KEY unique_page_embed (page_url(191), embed_type(50)),
145	KEY idx_content_type (content_type),
146	KEY idx_embed_type (embed_type),
147	KEY idx_post_id (post_id),
148	KEY idx_created_at (created_at),
149	KEY idx_total_views (total_views)
150	) $charset_collate;";
151
152	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
153	dbDelta($sql);
154	}
155
156	/**
157	* Create embedpress_analytics_views table
158	* Tracks individual views/interactions with embedded content
159	*
160	* @param string $charset_collate
161	* @return void
162	*/
163	private static function create_views_table($charset_collate)
164	{
165	global $wpdb;
166
167	$table_name = $wpdb->prefix . 'embedpress_analytics_views';
168
169	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
170	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
171	content_id varchar(255) NOT NULL,
172	user_id varchar(255) DEFAULT NULL,
173	session_id varchar(255) NOT NULL,
174	user_ip varchar(45) DEFAULT NULL,
175	user_agent text DEFAULT NULL,
176	referrer_url text DEFAULT NULL,
177	page_url text DEFAULT NULL,
178	interaction_type enum('impression', 'click', 'view', 'play', 'pause', 'complete') NOT NULL DEFAULT 'impression',
179	interaction_data longtext DEFAULT NULL,
180	view_duration int(11) unsigned DEFAULT 0,
181	created_at datetime DEFAULT CURRENT_TIMESTAMP,
182	PRIMARY KEY (id),
183	KEY idx_content_id (content_id(191)),
184	KEY idx_user_id (user_id(191)),
185	KEY idx_session_id (session_id(191)),
186	KEY idx_interaction_type (interaction_type),
187	KEY idx_created_at (created_at),
188	KEY idx_user_ip (user_ip),
189	KEY idx_content_interaction (content_id(191), interaction_type),
190	KEY idx_daily_stats (content_id(191), interaction_type, created_at),
191	KEY idx_user_content_interaction (user_id(100), content_id(100), interaction_type),
192	KEY idx_deduplication (user_id(100), content_id(100), interaction_type, created_at)
193	) $charset_collate;";
194
195	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
196	dbDelta($sql);
197	}
198
199	/**
200	* Create embedpress_analytics_browser_info table
201	* Tracks browser and device information for analytics
202	*
203	* @param string $charset_collate
204	* @return void
205	*/
206	private static function create_browser_info_table($charset_collate)
207	{
208	global $wpdb;
209
210	$table_name = $wpdb->prefix . 'embedpress_analytics_browser_info';
211
212	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
213	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
214	user_id varchar(255) DEFAULT NULL,
215	session_id varchar(255) NOT NULL,
216	browser_fingerprint varchar(64) DEFAULT NULL,
217	browser_name varchar(100) DEFAULT NULL,
218	browser_version varchar(50) DEFAULT NULL,
219	operating_system varchar(100) DEFAULT NULL,
220	device_type enum('desktop', 'mobile', 'tablet', 'unknown') DEFAULT 'unknown',
221	screen_resolution varchar(20) DEFAULT NULL,
222	language varchar(10) DEFAULT NULL,
223	timezone varchar(50) DEFAULT NULL,
224	country varchar(100) DEFAULT NULL,
225	city varchar(100) DEFAULT NULL,
226	user_agent text DEFAULT NULL,
227	created_at datetime DEFAULT CURRENT_TIMESTAMP,
228	PRIMARY KEY (id),
229	UNIQUE KEY unique_user_fingerprint (user_id(191), browser_fingerprint(50)),
230	KEY idx_user_id (user_id(191)),
231	KEY idx_session_id (session_id(191)),
232	KEY idx_browser_fingerprint (browser_fingerprint),
233	KEY idx_browser_name (browser_name),
234	KEY idx_operating_system (operating_system),
235	KEY idx_device_type (device_type),
236	KEY idx_country (country),
237	KEY idx_created_at (created_at)
238	) $charset_collate;";
239
240	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
241	dbDelta($sql);
242	}
243
244	/**
245	* Create embedpress_analytics_milestones table
246	* Tracks milestone achievements for upsell features
247	*
248	* @param string $charset_collate
249	* @return void
250	*/
251	private static function create_milestones_table($charset_collate)
252	{
253	global $wpdb;
254
255	$table_name = $wpdb->prefix . 'embedpress_analytics_milestones';
256
257	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
258	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
259	milestone_type enum('total_views', 'total_embeds', 'daily_views', 'monthly_views') NOT NULL,
260	milestone_value bigint(20) unsigned NOT NULL,
261	achieved_value bigint(20) unsigned NOT NULL,
262	is_notified tinyint(1) DEFAULT 0,
263	achieved_at datetime DEFAULT CURRENT_TIMESTAMP,
264	notified_at datetime DEFAULT NULL,
265	PRIMARY KEY (id),
266	KEY idx_milestone_type (milestone_type),
267	KEY idx_milestone_value (milestone_value),
268	KEY idx_is_notified (is_notified),
269	KEY idx_achieved_at (achieved_at)
270	) $charset_collate;";
271
272	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
273	dbDelta($sql);
274	}
275
276	/**
277	* Create embedpress_analytics_referrers table
278	* Tracks referrer URLs with optimized view and click counting
279	*
280	* @param string $charset_collate
281	* @return void
282	*/
283	private static function create_referrers_table($charset_collate)
284	{
285	global $wpdb;
286
287	$table_name = $wpdb->prefix . 'embedpress_analytics_referrers';
288
289	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
290	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
291	referrer_url text NOT NULL,
292	referrer_domain varchar(255) NOT NULL,
293	referrer_source varchar(100) DEFAULT NULL,
294	utm_source varchar(100) DEFAULT NULL,
295	utm_medium varchar(100) DEFAULT NULL,
296	utm_campaign varchar(255) DEFAULT NULL,
297	utm_term varchar(255) DEFAULT NULL,
298	utm_content varchar(255) DEFAULT NULL,
299	total_views bigint(20) unsigned DEFAULT 0,
300	total_clicks bigint(20) unsigned DEFAULT 0,
301	unique_visitors bigint(20) unsigned DEFAULT 0,
302	first_visit datetime DEFAULT CURRENT_TIMESTAMP,
303	last_visit datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
304	created_at datetime DEFAULT CURRENT_TIMESTAMP,
305	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
306	PRIMARY KEY (id),
307	UNIQUE KEY unique_referrer_url (referrer_url(191)),
308	KEY idx_referrer_domain (referrer_domain(191)),
309	KEY idx_referrer_source (referrer_source),
310	KEY idx_utm_source (utm_source),
311	KEY idx_utm_medium (utm_medium),
312	KEY idx_utm_campaign (utm_campaign(191)),
313	KEY idx_total_views (total_views),
314	KEY idx_total_clicks (total_clicks),
315	KEY idx_unique_visitors (unique_visitors),
316	KEY idx_first_visit (first_visit),
317	KEY idx_last_visit (last_visit),
318	KEY idx_created_at (created_at)
319	) $charset_collate;";
320
321	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
322	dbDelta($sql);
323	}
324
325
326	/**
327	* Run database migrations for version updates
328	* Only new migration: normalize index prefixes to be utf8mb4-safe
329	*
330	* @param string $current_version
331	* @return void
332	*/
333	private static function run_migrations($current_version)
334	{
335	global $wpdb;
336
337	if (version_compare($current_version, '1.0.8', '<')) {
338	// Content table: ensure unique_page_embed uses (page_url(191), embed_type(50))
339	$table = $wpdb->prefix . 'embedpress_analytics_content';
340	$exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
341	if ($exists) {
342	$idx = $wpdb->get_results("SHOW INDEX FROM $table WHERE Key_name = 'unique_page_embed'");
343	$needs_update = true;
344	if (!empty($idx)) {
345	$ok_page = false; $ok_embed = false;
346	foreach ($idx as $r) {
347	if ($r->Column_name === 'page_url' && intval($r->Sub_part) === 191) { $ok_page = true; }
348	if ($r->Column_name === 'embed_type' && intval($r->Sub_part) === 50) { $ok_embed = true; }
349	}
350	$needs_update = !($ok_page && $ok_embed);
351	}
352	if ($needs_update) {
353	$has_key = $wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_page_embed'");
354	if ($has_key) {
355	$wpdb->query("ALTER TABLE $table DROP INDEX unique_page_embed");
356	}
357	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_page_embed (page_url(191), embed_type(50))");
358	}
359	}
360
361	// Views table: normalize index prefixes
362	$table = $wpdb->prefix . 'embedpress_analytics_views';
363	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
364	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_content_id'")) {
365	$wpdb->query("ALTER TABLE $table DROP INDEX idx_content_id");
366	}
367	$wpdb->query("ALTER TABLE $table ADD INDEX idx_content_id (content_id(191))");
368
369	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_id'")) {
370	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_id");
371	}
372	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_id (user_id(191))");
373
374	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_session_id'")) {
375	$wpdb->query("ALTER TABLE $table DROP INDEX idx_session_id");
376	}
377	$wpdb->query("ALTER TABLE $table ADD INDEX idx_session_id (session_id(191))");
378
379	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_content_interaction'")) {
380	$wpdb->query("ALTER TABLE $table DROP INDEX idx_content_interaction");
381	}
382	$wpdb->query("ALTER TABLE $table ADD INDEX idx_content_interaction (content_id(191), interaction_type)");
383
384	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_daily_stats'")) {
385	$wpdb->query("ALTER TABLE $table DROP INDEX idx_daily_stats");
386	}
387	$wpdb->query("ALTER TABLE $table ADD INDEX idx_daily_stats (content_id(191), interaction_type, created_at)");
388
389	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_content_interaction'")) {
390	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_content_interaction");
391	}
392	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_content_interaction (user_id(100), content_id(100), interaction_type)");
393
394	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_deduplication'")) {
395	$wpdb->query("ALTER TABLE $table DROP INDEX idx_deduplication");
396	}
397	$wpdb->query("ALTER TABLE $table ADD INDEX idx_deduplication (user_id(100), content_id(100), interaction_type, created_at)");
398	}
399
400	// Browser info: normalize prefixes
401	$table = $wpdb->prefix . 'embedpress_analytics_browser_info';
402	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
403	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_user_fingerprint'")) {
404	$wpdb->query("ALTER TABLE $table DROP INDEX unique_user_fingerprint");
405	}
406	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_user_fingerprint (user_id(191), browser_fingerprint(50))");
407
408	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_id'")) {
409	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_id");
410	}
411	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_id (user_id(191))");
412
413	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_session_id'")) {
414	$wpdb->query("ALTER TABLE $table DROP INDEX idx_session_id");
415	}
416	$wpdb->query("ALTER TABLE $table ADD INDEX idx_session_id (session_id(191))");
417	}
418
419	// Referrers: normalize prefixes
420	$table = $wpdb->prefix . 'embedpress_analytics_referrers';
421	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
422	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_referrer_url'")) {
423	$wpdb->query("ALTER TABLE $table DROP INDEX unique_referrer_url");
424	}
425	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_referrer_url (referrer_url(191))");
426
427	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_referrer_domain'")) {
428	$wpdb->query("ALTER TABLE $table DROP INDEX idx_referrer_domain");
429	}
430	$wpdb->query("ALTER TABLE $table ADD INDEX idx_referrer_domain (referrer_domain(191))");
431
432	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_utm_campaign'")) {
433	$wpdb->query("ALTER TABLE $table DROP INDEX idx_utm_campaign");
434	}
435	$wpdb->query("ALTER TABLE $table ADD INDEX idx_utm_campaign (utm_campaign(191))");
436	}
437	}
438	}
439
440	/**
441	* Drop all analytics tables
442	* Used for plugin uninstallation
443	*
444	* @return void
445	*/
446	public static function drop_tables()
447	{
448	global $wpdb;
449
450	$tables = [
451	$wpdb->prefix . 'embedpress_analytics_content',
452	$wpdb->prefix . 'embedpress_analytics_views',
453	$wpdb->prefix . 'embedpress_analytics_browser_info',
454	$wpdb->prefix . 'embedpress_analytics_milestones',
455	$wpdb->prefix . 'embedpress_analytics_referrers'
456	];
457
458	foreach ($tables as $table) {
459	$wpdb->query("DROP TABLE IF EXISTS $table");
460	}
461
462	// Remove database version option
463	delete_option('embedpress_analytics_db_version');
464	}
465
466
467
468	/**
469	* Get table names with prefix
470	*
471	* @return array
472	*/
473	public static function get_table_names()
474	{
475	global $wpdb;
476
477	return [
478	'content' => $wpdb->prefix . 'embedpress_analytics_content',
479	'views' => $wpdb->prefix . 'embedpress_analytics_views',
480	'browser_info' => $wpdb->prefix . 'embedpress_analytics_browser_info',
481	'milestones' => $wpdb->prefix . 'embedpress_analytics_milestones',
482	'referrers' => $wpdb->prefix . 'embedpress_analytics_referrers'
483	];
484	}
485
486	/**
487	* Clean up old referrer visitor options that are no longer needed
488	* These were used before we switched to using the views table for tracking
489	*
490	* @return void
491	*/
492	private static function cleanup_old_referrer_visitor_options()
493	{
494	global $wpdb;
495
496	// Get all options that match the old referrer visitor pattern
497	$options = $wpdb->get_results(
498	"SELECT option_name FROM {$wpdb->options}
499	WHERE option_name LIKE 'embedpress_referrer_visitors_%'"
500	);
501
502	// Delete each option
503	foreach ($options as $option) {
504	delete_option($option->option_name);
505	}
506
507	// Log cleanup if any options were found
508	if (!empty($options)) {
509	error_log('EmbedPress: Cleaned up ' . count($options) . ' old referrer visitor options');
510	}
511	}
512	}
513