<?php
/**
 * Database access layer.
 *
 * @package Live_Traffic_Analytics
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
// Justification: every query below targets this plugin's own custom tables (wp_lta_activity,
// wp_lta_sessions, wp_lta_page_stats), which have no WordPress core API to go through. Table
// names are always built from `$wpdb->prefix` + a hardcoded suffix (see the *_table_name()
// helpers above) and never contain user input, so the interpolated identifiers are safe even
// though they cannot be passed through $wpdb->prepare(). All values are placeholders passed via
// $wpdb->prepare(). The data is real-time visitor/session activity that must reflect the latest
// writes on every read, so object-cache caching is intentionally not used.

/**
 * Handles all plugin database reads and writes.
 */
class WPLTA_Repository {

	/**
	 * Return the activity table name.
	 *
	 * @return string
	 */
	public static function get_activity_table_name() {
		global $wpdb;

		return $wpdb->prefix . 'lta_activity';
	}

	/**
	 * Return the sessions table name.
	 *
	 * @return string
	 */
	public static function get_sessions_table_name() {
		global $wpdb;

		return $wpdb->prefix . 'lta_sessions';
	}

	/**
	 * Return the page stats table name.
	 *
	 * @return string
	 */
	public static function get_page_stats_table_name() {
		global $wpdb;

		return $wpdb->prefix . 'lta_page_stats';
	}

	/**
	 * Persist one tracking event.
	 *
	 * @param array $payload Tracking payload.
	 * @param array $context Derived server-side context.
	 * @return array
	 */
	public static function save_tracking_event( $payload, $context ) {
		global $wpdb;

		$activity_table = self::get_activity_table_name();
		$sessions_table = self::get_sessions_table_name();
		$page_stats     = self::get_page_stats_table_name();
		$now            = current_time( 'mysql' );
		$page_entered   = self::normalize_datetime( $payload['page_entered'], $now );
		$page_url_hash  = md5( $payload['page_url'] );
		$referrer_host  = WPLTA_Helpers::get_host_from_url( $payload['referrer'] );
		$is_active      = 'leave' === $payload['action_type'] ? 0 : 1;

		$existing_session = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$sessions_table} WHERE session_id = %s LIMIT 1",
				$payload['session_id']
			),
			ARRAY_A
		);

		$journey_path = array();

		if ( ! empty( $existing_session['journey_path'] ) ) {
			$journey_path = json_decode( $existing_session['journey_path'], true );
			$journey_path = is_array( $journey_path ) ? $journey_path : array();
		}

		$latest_path = end( $journey_path );
		$is_new_step = empty( $latest_path ) || empty( $latest_path['page_url'] ) || $latest_path['page_url'] !== $payload['page_url'];

		if ( $is_new_step ) {
			$journey_path[] = array(
				'page_url'   => $payload['page_url'],
				'page_title' => $payload['page_title'],
				'visited_at' => $now,
			);
		}

		$page_count = count( $journey_path );

		$session_data = array(
			'session_id'       => $payload['session_id'],
			'ip_address'       => $context['ip_address'],
			'country'          => $context['country'],
			'device_type'      => $context['device_type'],
			'browser'          => $context['browser'],
			'entry_page'       => ! empty( $existing_session['entry_page'] ) ? $existing_session['entry_page'] : $payload['page_url'],
			'current_page'     => $payload['page_url'],
			'referrer'         => ! empty( $existing_session['referrer'] ) ? $existing_session['referrer'] : $payload['referrer'],
			'referrer_host'    => ! empty( $existing_session['referrer_host'] ) ? $existing_session['referrer_host'] : $referrer_host,
			'page_count'       => $page_count,
			'journey_path'     => wp_json_encode( $journey_path ),
			'started_at'       => ! empty( $existing_session['started_at'] ) ? $existing_session['started_at'] : $page_entered,
			'last_activity_at' => $now,
			'created_at'       => ! empty( $existing_session['created_at'] ) ? $existing_session['created_at'] : $now,
			'updated_at'       => $now,
		);

		if ( $existing_session ) {
			$wpdb->update(
				$sessions_table,
				$session_data,
				array( 'session_id' => $payload['session_id'] ),
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s' ),
				array( '%s' )
			);
		} else {
			$wpdb->insert(
				$sessions_table,
				$session_data,
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s' )
			);
		}

		$activity_data = array(
			'session_id'      => $payload['session_id'],
			'page_view_id'    => $payload['page_view_id'],
			'ip_address'      => $context['ip_address'],
			'country'         => $context['country'],
			'latitude'        => $context['latitude'],
			'longitude'       => $context['longitude'],
			'page_url'        => $payload['page_url'],
			'page_url_hash'   => $page_url_hash,
			'page_title'      => $payload['page_title'],
			'referrer'        => $payload['referrer'],
			'referrer_host'   => $referrer_host,
			'device_type'     => $context['device_type'],
			'browser'         => $context['browser'],
			'user_agent'      => $context['user_agent'],
			'is_bot'          => ! empty( $context['is_bot'] ) ? 1 : 0,
			'bot_name'        => $context['bot_name'],
			'time_on_page'    => $payload['time_on_page'],
			'page_entered_at' => $page_entered,
			'last_seen_at'    => $now,
			'is_active'       => $is_active,
			'created_at'      => $now,
			'updated_at'      => $now,
		);

		$existing_activity_id = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$activity_table} WHERE page_view_id = %s LIMIT 1",
				$payload['page_view_id']
			)
		);

		if ( $existing_activity_id ) {
			$wpdb->update(
				$activity_table,
				$activity_data,
				array( 'id' => absint( $existing_activity_id ) ),
				array( '%s', '%s', '%s', '%s', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s', '%d', '%s', '%s' ),
				array( '%d' )
			);
		} else {
			$wpdb->insert(
				$activity_table,
				$activity_data,
				array( '%s', '%s', '%s', '%s', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s', '%d', '%s', '%s' )
			);
		}

		$page_stats_row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, total_views FROM {$page_stats} WHERE page_url_hash = %s LIMIT 1",
				$page_url_hash
			),
			ARRAY_A
		);

		if ( 'pageview' === $payload['action_type'] ) {
			if ( $page_stats_row ) {
				$wpdb->update(
					$page_stats,
					array(
						'page_url'        => $payload['page_url'],
						'page_title'      => $payload['page_title'],
						'total_views'     => absint( $page_stats_row['total_views'] ) + 1,
						'last_visited_at' => $now,
						'updated_at'      => $now,
					),
					array( 'id' => absint( $page_stats_row['id'] ) ),
					array( '%s', '%s', '%d', '%s', '%s' ),
					array( '%d' )
				);
			} else {
				$wpdb->insert(
					$page_stats,
					array(
						'page_url'        => $payload['page_url'],
						'page_url_hash'   => $page_url_hash,
						'page_title'      => $payload['page_title'],
						'total_views'     => 1,
						'last_visited_at' => $now,
						'created_at'      => $now,
						'updated_at'      => $now,
					),
					array( '%s', '%s', '%s', '%d', '%s', '%s', '%s' )
				);
			}
		}

		do_action( 'wplta_after_activity_saved', $payload, $context );

		return array(
			'session_id'    => $payload['session_id'],
			'page_view_id'  => $payload['page_view_id'],
			'last_seen_at'  => $now,
			'active_users'  => self::get_active_users_count(),
			'page_views'    => self::get_total_page_views(),
			'session_count' => self::get_total_sessions_count(),
			'is_bot'        => ! empty( $context['is_bot'] ),
			'bot_name'      => $context['bot_name'],
		);
	}

	/**
	 * Return recent IP activity signals.
	 *
	 * @param string $ip_address IP address.
	 * @param int    $window_seconds Lookback window.
	 * @return array
	 */
	public static function get_recent_ip_signals( $ip_address, $window_seconds = 10 ) {
		global $wpdb;

		$table     = self::get_activity_table_name();
		$threshold = wp_date( 'Y-m-d H:i:s', time() - absint( $window_seconds ) );
		$row       = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS recent_hits,
					SUM(CASE WHEN page_entered_at >= %s THEN 1 ELSE 0 END) AS recent_page_views,
					COUNT(DISTINCT page_url_hash) AS unique_pages
				FROM {$table}
				WHERE ip_address = %s
					AND last_seen_at >= %s",
				$threshold,
				$ip_address,
				$threshold
			),
			ARRAY_A
		);

		return array(
			'recent_hits'       => ! empty( $row['recent_hits'] ) ? absint( $row['recent_hits'] ) : 0,
			'recent_page_views' => ! empty( $row['recent_page_views'] ) ? absint( $row['recent_page_views'] ) : 0,
			'unique_pages'      => ! empty( $row['unique_pages'] ) ? absint( $row['unique_pages'] ) : 0,
		);
	}

	/**
	 * Return active users count.
	 *
	 * @return int
	 */
	public static function get_active_users_count() {
		global $wpdb;

		$table     = self::get_activity_table_name();
		$threshold = wp_date( 'Y-m-d H:i:s', time() - WPLTA_Settings::get_active_window_seconds() );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT session_id) FROM {$table} WHERE is_active = 1 AND last_seen_at >= %s",
				$threshold
			)
		);
	}

	/**
	 * Return session count.
	 *
	 * @return int
	 */
	public static function get_total_sessions_count() {
		global $wpdb;

		$table = self::get_sessions_table_name();

		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Return total page views.
	 *
	 * @return int
	 */
	public static function get_total_page_views() {
		global $wpdb;

		$table = self::get_page_stats_table_name();

		return (int) $wpdb->get_var( "SELECT COALESCE(SUM(total_views), 0) FROM {$table}" );
	}

	/**
	 * Return live activity rows.
	 *
	 * @param int $limit Row count.
	 * @return array
	 */
	public static function get_live_activity( $limit = 25 ) {
		global $wpdb;

		$table     = self::get_activity_table_name();
		$threshold = wp_date( 'Y-m-d H:i:s', time() - WPLTA_Settings::get_active_window_seconds() );
		$rows      = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT session_id, ip_address, country, page_url, referrer, device_type, browser, time_on_page, last_seen_at, is_bot, bot_name
				FROM {$table}
				WHERE is_active = 1 AND last_seen_at >= %s
				ORDER BY last_seen_at DESC
				LIMIT %d",
				$threshold,
				absint( $limit )
			),
			ARRAY_A
		);

		return array_map(
			function ( $row ) {
				return array(
					'session_id'    => $row['session_id'],
					'ip_address'    => $row['ip_address'],
					'country'       => $row['country'],
					'page_url'      => $row['page_url'],
					'referrer'      => $row['referrer'],
					'device_type'   => ucfirst( $row['device_type'] ),
					'browser'       => $row['browser'],
					'type'          => ! empty( $row['is_bot'] ) ? 'Bot' : 'User',
					'bot_name'      => $row['bot_name'],
					'time_on_page'  => absint( $row['time_on_page'] ),
					'last_seen_at'  => WPLTA_Helpers::format_datetime( $row['last_seen_at'] ),
				);
			},
			$rows
		);
	}

	/**
	 * Return active visitors for map rendering.
	 *
	 * @param int $limit Row count.
	 * @return array
	 */
	public static function get_live_map_markers( $limit = 100 ) {
		global $wpdb;

		$table     = self::get_activity_table_name();
		$threshold = wp_date( 'Y-m-d H:i:s', time() - WPLTA_Settings::get_active_window_seconds() );
		$rows      = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT session_id, ip_address, country, page_url, latitude, longitude, is_bot, bot_name, last_seen_at
				FROM {$table}
				WHERE is_active = 1
					AND last_seen_at >= %s
					AND latitude IS NOT NULL
					AND longitude IS NOT NULL
				ORDER BY last_seen_at DESC
				LIMIT %d",
				$threshold,
				absint( $limit )
			),
			ARRAY_A
		);

		return array_map(
			function ( $row ) {
				return array(
					'id'          => $row['session_id'],
					'ip_address'  => $row['ip_address'],
					'country'     => $row['country'],
					'page_url'    => $row['page_url'],
					'latitude'    => (float) $row['latitude'],
					'longitude'   => (float) $row['longitude'],
					'type'        => ! empty( $row['is_bot'] ) ? 'Bot' : 'User',
					'bot_name'    => $row['bot_name'],
					'last_seen_at'=> WPLTA_Helpers::format_datetime( $row['last_seen_at'] ),
				);
			},
			$rows
		);
	}

	/**
	 * Return latest session journeys.
	 *
	 * @param int $limit Row count.
	 * @return array
	 */
	public static function get_recent_journeys( $limit = 20 ) {
		global $wpdb;

		$table = self::get_sessions_table_name();
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT session_id, ip_address, country, device_type, browser, referrer, page_count, journey_path, last_activity_at
				FROM {$table}
				ORDER BY last_activity_at DESC
				LIMIT %d",
				absint( $limit )
			),
			ARRAY_A
		);

		return array_map(
			function ( $row ) {
				$journey_path = json_decode( $row['journey_path'], true );
				$journey_path = is_array( $journey_path ) ? $journey_path : array();

				return array(
					'session_id'       => $row['session_id'],
					'ip_address'       => $row['ip_address'],
					'country'          => $row['country'],
					'device_type'      => ucfirst( $row['device_type'] ),
					'browser'          => $row['browser'],
					'referrer'         => $row['referrer'],
					'page_count'       => absint( $row['page_count'] ),
					'journey_path'     => $journey_path,
					'last_activity_at' => WPLTA_Helpers::format_datetime( $row['last_activity_at'] ),
				);
			},
			$rows
		);
	}

	/**
	 * Return top pages.
	 *
	 * @param int $limit Row count.
	 * @return array
	 */
	public static function get_top_pages( $limit = 10 ) {
		global $wpdb;

		$table = self::get_page_stats_table_name();
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT page_url, page_title, total_views, last_visited_at
				FROM {$table}
				ORDER BY total_views DESC, last_visited_at DESC
				LIMIT %d",
				absint( $limit )
			),
			ARRAY_A
		);

		return array_map(
			function ( $row ) {
				return array(
					'page_url'        => $row['page_url'],
					'page_title'      => $row['page_title'],
					'total_views'     => absint( $row['total_views'] ),
					'last_visited_at' => WPLTA_Helpers::format_datetime( $row['last_visited_at'] ),
				);
			},
			$rows
		);
	}

	/**
	 * Return dashboard summary datasets.
	 *
	 * @return array
	 */
	public static function get_dashboard_summary() {
		global $wpdb;

		$activity_table = self::get_activity_table_name();
		$threshold      = wp_date( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );

		$device_rows = $wpdb->get_results(
			"SELECT device_type, COUNT(*) AS total
			FROM {$activity_table}
			GROUP BY device_type
			ORDER BY total DESC",
			ARRAY_A
		);

		$browser_rows = $wpdb->get_results(
			"SELECT browser, COUNT(*) AS total
			FROM {$activity_table}
			GROUP BY browser
			ORDER BY total DESC
			LIMIT 5",
			ARRAY_A
		);

		$recent_views = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(created_at, '%%H:00') AS hour_bucket, COUNT(*) AS total,
					SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) AS bots
				FROM {$activity_table}
				WHERE created_at >= %s
				GROUP BY hour_bucket
				ORDER BY MIN(created_at) ASC",
				$threshold
			),
			ARRAY_A
		);

		// --- KPI sparklines + deltas (dynamic, derived from real data) ---
		$views_series = array_map(
			static function ( $r ) {
				return absint( $r['total'] );
			},
			$recent_views
		);
		$views_delta  = self::series_delta( $views_series );

		$sessions_table = self::get_sessions_table_name();
		$today_start    = wp_date( 'Y-m-d 00:00:00' );
		$sess_hour_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT HOUR(created_at) AS h, COUNT(*) AS total FROM {$sessions_table} WHERE created_at >= %s GROUP BY h",
				$today_start
			),
			ARRAY_A
		);
		$sess_by_hour = array_fill( 0, 24, 0 );
		foreach ( $sess_hour_rows as $r ) {
			$sess_by_hour[ (int) $r['h'] ] = (int) $r['total'];
		}
		$current_hour    = (int) wp_date( 'H' );
		$sessions_series = array();
		for ( $hh = max( 0, $current_hour - 11 ); $hh <= $current_hour; $hh++ ) {
			$sessions_series[] = $sess_by_hour[ $hh ];
		}
		$sessions_delta = self::series_delta( $sessions_series );

		$active_since    = wp_date( 'Y-m-d H:i:s', time() - 15 * MINUTE_IN_SECONDS );
		$active_min_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(last_seen_at, '%%Y-%%m-%%d %%H:%%i') AS mb, COUNT(DISTINCT session_id) AS total
				FROM {$activity_table} WHERE last_seen_at >= %s GROUP BY mb",
				$active_since
			),
			ARRAY_A
		);
		$active_min_map = array();
		foreach ( $active_min_rows as $r ) {
			$active_min_map[ $r['mb'] ] = (int) $r['total'];
		}
		$active_series = array();
		for ( $m = 14; $m >= 0; $m-- ) {
			$slot            = wp_date( 'Y-m-d H:i', time() - $m * MINUTE_IN_SECONDS );
			$active_series[] = isset( $active_min_map[ $slot ] ) ? $active_min_map[ $slot ] : 0;
		}
		$active_delta = self::series_delta( $active_series );

		return array_merge( array(
			'active_users' => self::get_active_users_count(),
			'total_views'  => self::get_total_page_views(),
			'sessions'     => self::get_total_sessions_count(),
			'device_breakdown' => array_map(
				function ( $row ) {
					return array(
						'label' => ucfirst( $row['device_type'] ),
						'value' => absint( $row['total'] ),
					);
				},
				$device_rows
			),
			'browser_breakdown' => array_map(
				function ( $row ) {
					return array(
						'label' => $row['browser'],
						'value' => absint( $row['total'] ),
					);
				},
				$browser_rows
			),
			'views_timeline' => array_map(
				function ( $row ) {
					return array(
						'label' => $row['hour_bucket'],
						'value' => absint( $row['total'] ),
					);
				},
				$recent_views
			),
			'views_delta'        => $views_delta,
			'active_sparkline'   => $active_series,
			'active_delta'       => $active_delta,
			'sessions_sparkline' => $sessions_series,
			'sessions_delta'     => $sessions_delta,
			'bots_timeline'      => array_map(
				function ( $row ) {
					return array(
						'label' => $row['hour_bucket'],
						'value' => absint( $row['bots'] ),
					);
				},
				$recent_views
			),
		), self::get_overview_extras() );
	}

	/**
	 * Extra Overview widgets: unique users, funnel, referrers, countries.
	 *
	 * @return array
	 */
	public static function get_overview_extras() {
		global $wpdb;

		$activity_table = self::get_activity_table_name();
		$sessions_table = self::get_sessions_table_name();

		$unique_users = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT ip_address) FROM {$activity_table}" );

		$uniq_threshold = wp_date( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
		$uniq_hourly    = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H') AS hb, COUNT(DISTINCT ip_address) AS total
				FROM {$activity_table} WHERE created_at >= %s GROUP BY hb ORDER BY hb ASC",
				$uniq_threshold
			),
			ARRAY_A
		);
		$unique_series       = wp_list_pluck( $uniq_hourly, 'total' );
		$unique_users_delta  = self::series_delta( $unique_series );

		// Funnel from session depth.
		$landed    = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table}" );
		$engaged   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE page_count >= 2" );
		$multipage = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE page_count >= 3" );
		$goal      = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE page_count >= 4" );
		$pct       = function ( $v ) use ( $landed ) {
			return $landed > 0 ? (int) round( ( $v / $landed ) * 100 ) : 0;
		};
		$funnel = array(
			array( 'name' => 'Landed', 'value' => $landed, 'pct' => 100 ),
			array( 'name' => 'Engaged', 'value' => $engaged, 'pct' => $pct( $engaged ) ),
			array( 'name' => 'Multi-page', 'value' => $multipage, 'pct' => $pct( $multipage ) ),
			array( 'name' => 'Goal', 'value' => $goal, 'pct' => $pct( $goal ) ),
		);

		// Referrers (empty host = Direct).
		$ref_rows  = $wpdb->get_results(
			"SELECT CASE WHEN referrer_host = '' THEN 'Direct' ELSE referrer_host END AS src, COUNT(*) AS total
			FROM {$sessions_table} GROUP BY src ORDER BY total DESC LIMIT 6",
			ARRAY_A
		);
		$referrers = array_map(
			function ( $row ) {
				return array(
					'label' => self::prettify_referrer( $row['src'] ),
					'value' => absint( $row['total'] ),
				);
			},
			$ref_rows
		);

		// Countries: visitors (distinct sessions) + views, humans only.
		$country_rows = $wpdb->get_results(
			"SELECT country, COUNT(DISTINCT session_id) AS visitors, COUNT(*) AS views
			FROM {$activity_table} WHERE is_bot = 0 AND country <> '' GROUP BY country ORDER BY views DESC LIMIT 6",
			ARRAY_A
		);
		$countries = array_map(
			function ( $row ) {
				return array(
					'label'    => $row['country'],
					'visitors' => absint( $row['visitors'] ),
					'views'    => absint( $row['views'] ),
				);
			},
			$country_rows
		);

		return array(
			'unique_users'       => $unique_users,
			'unique_users_delta' => $unique_users_delta,
			'funnel'             => $funnel,
			'referrers'          => $referrers,
			'countries'          => $countries,
		);
	}

	/**
	 * Turn a referrer host into a friendly source label.
	 *
	 * @param string $host Referrer host or 'Direct'.
	 * @return string
	 */
	private static function prettify_referrer( $host ) {
		if ( 'Direct' === $host ) {
			return 'Direct';
		}

		$map = array(
			'google'    => 'Google',
			'bing'      => 'Bing',
			'duckduckgo'=> 'DuckDuckGo',
			'yahoo'     => 'Yahoo',
			't.co'      => 'Twitter / X',
			'twitter'   => 'Twitter / X',
			'facebook'  => 'Facebook',
			'linkedin'  => 'LinkedIn',
			'instagram' => 'Instagram',
			'reddit'    => 'Reddit',
			'youtube'   => 'YouTube',
		);

		foreach ( $map as $needle => $label ) {
			if ( false !== strpos( $host, $needle ) ) {
				return $label;
			}
		}

		return preg_replace( '/^www\./', '', $host );
	}

	/**
	 * Signed percentage change between the first and second half of a numeric series.
	 *
	 * @param array $series Numeric series.
	 * @return float
	 */
	private static function series_delta( $series ) {
		$series = array_values( array_map( 'intval', (array) $series ) );
		$count  = count( $series );

		if ( $count < 2 ) {
			return 0;
		}

		$half   = intdiv( $count, 2 );
		$first  = array_sum( array_slice( $series, 0, $half ) );
		$second = array_sum( array_slice( $series, $half ) );

		if ( $first <= 0 ) {
			return $second > 0 ? 100.0 : 0.0;
		}

		return round( ( ( $second - $first ) / $first ) * 100, 1 );
	}

	/**
	 * Return today vs. yesterday visitor comparison, with an hourly breakdown.
	 *
	 * @return array
	 */
	public static function get_visitor_comparison() {
		global $wpdb;

		$sessions_table = self::get_sessions_table_name();

		$today_start     = wp_date( 'Y-m-d 00:00:00' );
		$yesterday_start = wp_date( 'Y-m-d 00:00:00', strtotime( '-1 day' ) );

		$today_total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$sessions_table} WHERE created_at >= %s",
				$today_start
			)
		);

		$yesterday_total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$sessions_table} WHERE created_at >= %s AND created_at < %s",
				$yesterday_start,
				$today_start
			)
		);

		$today_hourly = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT HOUR(created_at) AS h, COUNT(*) AS total
				FROM {$sessions_table}
				WHERE created_at >= %s
				GROUP BY h",
				$today_start
			),
			ARRAY_A
		);

		$yesterday_hourly = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT HOUR(created_at) AS h, COUNT(*) AS total
				FROM {$sessions_table}
				WHERE created_at >= %s AND created_at < %s
				GROUP BY h",
				$yesterday_start,
				$today_start
			),
			ARRAY_A
		);

		$today_map     = wp_list_pluck( $today_hourly, 'total', 'h' );
		$yesterday_map = wp_list_pluck( $yesterday_hourly, 'total', 'h' );

		$hourly = array();

		for ( $hour = 0; $hour < 24; $hour++ ) {
			$hourly[] = array(
				'hour'      => sprintf( '%02d:00', $hour ),
				'today'     => isset( $today_map[ $hour ] ) ? (int) $today_map[ $hour ] : 0,
				'yesterday' => isset( $yesterday_map[ $hour ] ) ? (int) $yesterday_map[ $hour ] : 0,
			);
		}

		$change_percent = 0;

		if ( $yesterday_total > 0 ) {
			$change_percent = round( ( ( $today_total - $yesterday_total ) / $yesterday_total ) * 100, 1 );
		} elseif ( $today_total > 0 ) {
			$change_percent = 100;
		}

		return array(
			'today'           => $today_total,
			'yesterday'       => $yesterday_total,
			'change_percent'  => $change_percent,
			'hourly'          => $hourly,
		);
	}

	/**
	 * Lightweight bot ratio summary (total bots/users, bot %). Used both by
	 * the free Overview tab's "Bot Ratio" metric and as the base of the full
	 * Pro-only get_bot_analytics() breakdown, so it must never be Pro-gated.
	 *
	 * @return array
	 */
	public static function get_bot_ratio_summary() {
		global $wpdb;

		$table         = self::get_activity_table_name();
		$total_records = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		$total_bots    = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_bot = 1" );

		return array(
			'total_bots'  => $total_bots,
			'total_users' => max( 0, $total_records - $total_bots ),
			'bot_ratio'   => $total_records > 0 ? round( ( $total_bots / $total_records ) * 100, 2 ) : 0,
		);
	}

	/**
	 * Return bot analytics summary.
	 *
	 * @return array
	 */
	public static function get_bot_analytics() {
		global $wpdb;

		$table    = self::get_activity_table_name();
		$summary  = self::get_bot_ratio_summary();
		$top_bots = $wpdb->get_results(
			"SELECT bot_name, COUNT(*) AS total
			FROM {$table}
			WHERE is_bot = 1
			GROUP BY bot_name
			ORDER BY total DESC
			LIMIT 5",
			ARRAY_A
		);

		// --- hourly bot / user split for KPI sparklines (last 24h) ---
		$bot_threshold = wp_date( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
		$hourly_split  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H') AS hb,
					SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) AS bots,
					COUNT(*) AS total
				FROM {$table} WHERE created_at >= %s GROUP BY hb ORDER BY hb ASC",
				$bot_threshold
			),
			ARRAY_A
		);
		$bots_series  = array();
		$users_series = array();
		$ratio_series = array();
		foreach ( $hourly_split as $row ) {
			$hb_bots  = (int) $row['bots'];
			$hb_total = (int) $row['total'];
			$bots_series[]  = $hb_bots;
			$users_series[] = max( 0, $hb_total - $hb_bots );
			$ratio_series[] = $hb_total > 0 ? (int) round( ( $hb_bots / $hb_total ) * 100 ) : 0;
		}

		return array_merge(
			$summary,
			array(
				'top_bots' => array_map(
					function ( $row ) {
						return array(
							'label' => ! empty( $row['bot_name'] ) ? $row['bot_name'] : __( 'Unknown bot', 'live-traffic-analytics' ),
							'value' => absint( $row['total'] ),
						);
					},
					$top_bots
				),
				'bots_sparkline'  => $bots_series,
				'bots_delta'      => self::series_delta( $bots_series ),
				'users_sparkline' => $users_series,
				'users_delta'     => self::series_delta( $users_series ),
				'ratio_sparkline' => $ratio_series,
				'ratio_delta'     => self::series_delta( $ratio_series ),
			)
		);
	}

	/**
	 * Return the full admin payload for REST/AJAX.
	 *
	 * @return array
	 */
	public static function get_admin_dashboard_payload() {
		$is_pro = function_exists( 'wplta_is_pro' ) && wplta_is_pro();

		$payload = array(
			'is_pro'        => $is_pro,
			'license'       => function_exists( 'wplta_get_license_status' ) ? wplta_get_license_status() : array(),
			'summary'       => self::get_dashboard_summary(),
			'live_activity' => array(),
			'journeys'      => array(),
			'top_pages'     => array(),
			'live_map'      => array(),
			// Bot ratio/counts power the free Overview tab, so they're always
			// real; only the detailed top_bots breakdown is Pro-only (shown
			// solely on the gated Bot Analytics tab).
			'bot_analytics' => array_merge(
				self::get_bot_ratio_summary(),
				array( 'top_bots' => array() )
			),
			'visitor_comparison' => array(
				'today'          => 0,
				'yesterday'      => 0,
				'change_percent' => 0,
				'hourly'         => array(),
			),
			'tables'        => array(
				'activity'  => self::get_activity_table_name(),
				'sessions'  => self::get_sessions_table_name(),
				'pageStats' => self::get_page_stats_table_name(),
			),
		);

		if ( ! $is_pro ) {
			return $payload;
		}

		$payload['live_activity']      = self::get_live_activity( 25 );
		$payload['journeys']           = self::get_recent_journeys( 15 );
		$payload['top_pages']          = self::get_top_pages( 10 );
		$payload['live_map']           = self::get_live_map_markers( 120 );
		$payload['bot_analytics']      = self::get_bot_analytics();
		$payload['visitor_comparison'] = self::get_visitor_comparison();

		return $payload;
	}

	/**
	 * Normalize datetime string to MySQL format.
	 *
	 * @param string $datetime Input datetime.
	 * @param string $fallback Fallback datetime.
	 * @return string
	 */
	private static function normalize_datetime( $datetime, $fallback ) {
		if ( empty( $datetime ) ) {
			return $fallback;
		}

		$timestamp = strtotime( $datetime );

		if ( false === $timestamp ) {
			return $fallback;
		}

		return wp_date( 'Y-m-d H:i:s', $timestamp );
	}

	/**
	 * Return how much real DB storage the plugin's own tables (activity,
	 * sessions, page stats) are using right now, plus total row count.
	 *
	 * @return array
	 */
	public static function get_storage_usage() {
		global $wpdb;

		$table_names = array(
			self::get_activity_table_name(),
			self::get_sessions_table_name(),
			self::get_page_stats_table_name(),
		);

		$total_bytes = 0;
		$total_rows  = 0;

		foreach ( $table_names as $table_name ) {
			// InnoDB's information_schema size/row stats are cached and can
			// be stale (e.g. still showing pre-reset numbers), so refresh
			// them first. These are small dedicated tables, so the cost is
			// negligible and this only runs when the Settings tab loads.
			$wpdb->query( "ANALYZE TABLE {$table_name}" );

			$size_bytes   = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT (DATA_LENGTH + INDEX_LENGTH)
					FROM information_schema.TABLES
					WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
					DB_NAME,
					$table_name
				)
			);
			$total_bytes += $size_bytes;
			$total_rows  += (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" );
		}

		return array(
			'total_bytes'      => $total_bytes,
			'total_size_label' => size_format( $total_bytes, 1 ),
			'total_rows'       => $total_rows,
		);
	}

	/**
	 * Permanently delete all tracked analytics data (activity, sessions, page
	 * stats) and reset the counters back to zero. Irreversible.
	 *
	 * @return void
	 */
	public static function reset_all_data() {
		global $wpdb;

		foreach ( array( self::get_activity_table_name(), self::get_sessions_table_name(), self::get_page_stats_table_name() ) as $table_name ) {
			$wpdb->query( "TRUNCATE TABLE {$table_name}" );
			// Refresh InnoDB's cached size/row statistics immediately so a
			// storage-usage read right after this shows accurate numbers.
			$wpdb->query( "ANALYZE TABLE {$table_name}" );
		}
	}
}
