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

class RT_WA_Analytics_DB {

	private static $instance = null;

	private $visitors_table;
	private $sessions_table;
	private $events_table;
	private $api_keys_table;
	private $page_views_table;
	private $utm_links_table;

	public static function instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	public function __construct() {
		global $wpdb;
		$this->visitors_table   = $wpdb->prefix . 'rt_wa_visitors';
		$this->sessions_table   = $wpdb->prefix . 'rt_wa_sessions';
		$this->events_table     = $wpdb->prefix . 'rt_wa_events';
		$this->api_keys_table   = $wpdb->prefix . 'rt_wa_api_keys';
		$this->page_views_table = $wpdb->prefix . 'rt_wa_page_views';
		$this->utm_links_table  = $wpdb->prefix . 'rt_wa_utm_links';
	}

	public function visitors_table() {
		return $this->visitors_table;
	}

	public function sessions_table() {
		return $this->sessions_table;
	}

	public function events_table() {
		return $this->events_table;
	}

	public function api_keys_table() {
		return $this->api_keys_table;
	}

	public function page_views_table() {
		return $this->page_views_table;
	}

	public function utm_links_table() {
		return $this->utm_links_table;
	}

	public function get_visitor( $uuid ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE uuid = %s', $this->visitors_table, $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	private function build_date_clause( $column, $date_from, $date_to ) {
		$clause = '';
		$params = array();

		if ( ! empty( $date_from ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date_from ) ) {
			$clause  .= " AND {$column} >= %s";
			$params[] = $date_from;
		}
		if ( ! empty( $date_to ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date_to ) ) {
			$clause  .= " AND {$column} <= %s";
			$params[] = $date_to . ' 23:59:59';
		}

		return array( $clause, $params );
	}

	public function get_visitors( $limit = 20, $offset = 0, $orderby = 'last_seen', $order = 'DESC', $filters = array() ) {
		global $wpdb;
		$allowed_order = array( 'last_seen', 'first_seen', 'sessions_count', 'wa_clicks' );
		if ( ! in_array( $orderby, $allowed_order, true ) ) {
			$orderby = 'last_seen';
		}
		$order = strtoupper( $order ) === 'ASC' ? 'ASC' : 'DESC';

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['date_from'] ) ) {
			$where[]  = 'last_seen >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[]  = 'last_seen <= %s';
			$params[] = $filters['date_to'];
		}
		if ( ! empty( $filters['source'] ) ) {
			$where[]  = 'first_source = %s';
			$params[] = $filters['source'];
		}
		if ( ! empty( $filters['country'] ) ) {
			$where[]  = 'country = %s';
			$params[] = $filters['country'];
		}
		if ( ! empty( $filters['device'] ) ) {
			$where[]  = 'device = %s';
			$params[] = $filters['device'];
		}
		if ( ! empty( $filters['audience'] ) ) {
			$uuids = $this->get_audience_uuids();
			if ( ! empty( $uuids ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $uuids ), '%s' ) );
				$where[]      = "uuid IN ({$placeholders})";
				$params       = array_merge( $params, $uuids );
			} else {
				$where[] = '1=0';
			}
		}

		$where_sql = implode( ' AND ', $where );
		$params[]  = $limit;
		$params[]  = $offset;

		$sql = "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_visitors_count( $filters = array() ) {
		global $wpdb;
		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['date_from'] ) ) {
			$where[]  = 'last_seen >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[]  = 'last_seen <= %s';
			$params[] = $filters['date_to'];
		}
		if ( ! empty( $filters['source'] ) ) {
			$where[]  = 'first_source = %s';
			$params[] = $filters['source'];
		}
		if ( ! empty( $filters['country'] ) ) {
			$where[]  = 'country = %s';
			$params[] = $filters['country'];
		}
		if ( ! empty( $filters['device'] ) ) {
			$where[]  = 'device = %s';
			$params[] = $filters['device'];
		}
		if ( ! empty( $filters['audience'] ) ) {
			$uuids = $this->get_audience_uuids();
			if ( ! empty( $uuids ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $uuids ), '%s' ) );
				$where[]      = "uuid IN ({$placeholders})";
				$params       = array_merge( $params, $uuids );
			} else {
				$where[] = '1=0';
			}
		}

		$where_sql = implode( ' AND ', $where );
		$sql       = "SELECT COUNT(*) FROM %i WHERE {$where_sql}";
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function toggle_audience( $uuid ) {
		$audience = get_option( 'rt_wa_audience', array() );
		if ( ! is_array( $audience ) ) {
			$audience = array();
		}

		if ( in_array( $uuid, $audience, true ) ) {
			$audience = array_values( array_diff( $audience, array( $uuid ) ) );
			$added    = false;
		} else {
			$audience[] = $uuid;
			$added      = true;
		}

		update_option( 'rt_wa_audience', $audience );
		return array(
			'added' => $added,
			'count' => count( $audience ),
		);
	}

	public function get_audience_uuids() {
		$audience = get_option( 'rt_wa_audience', array() );
		return is_array( $audience ) ? $audience : array();
	}

	public function get_distinct_sources() {
		global $wpdb;
		return $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT first_source FROM %i WHERE first_source IS NOT NULL AND first_source != %s ORDER BY first_source ASC', $this->visitors_table, '' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function get_distinct_countries() {
		global $wpdb;
		return $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT country FROM %i WHERE country IS NOT NULL AND country != %s ORDER BY country ASC', $this->visitors_table, '' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function get_distinct_devices() {
		global $wpdb;
		return $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT device FROM %i WHERE device IS NOT NULL AND device != %s ORDER BY device ASC', $this->visitors_table, '' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function get_session( $session_id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE session_id = %s', $this->sessions_table, $session_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function get_sessions( $limit = 20, $offset = 0, $orderby = 'start_time', $order = 'DESC', $filters = array() ) {
		global $wpdb;
		$allowed_order = array( 'start_time', 'end_time', 'duration' );
		if ( ! in_array( $orderby, $allowed_order, true ) ) {
			$orderby = 'start_time';
		}
		$order = strtoupper( $order ) === 'ASC' ? 'ASC' : 'DESC';

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['date_from'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_from'] ) ) {
			$where[]  = 'start_time >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_to'] ) ) {
			$where[]  = 'start_time <= %s';
			$params[] = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );
		$params[]  = $limit;
		$params[]  = $offset;

		$sql = "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->sessions_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_sessions_count( $filters = array() ) {
		global $wpdb;
		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['date_from'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_from'] ) ) {
			$where[]  = 'start_time >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_to'] ) ) {
			$where[]  = 'start_time <= %s';
			$params[] = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );
		$sql       = "SELECT COUNT(*) FROM %i WHERE {$where_sql}";
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->sessions_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_visitor_sessions( $visitor_uuid, $limit = 50 ) {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				'SELECT * FROM %i WHERE visitor_uuid = %s ORDER BY start_time DESC LIMIT %d',
				$this->sessions_table,
				$visitor_uuid,
				$limit
			)
		);
	}

	public function get_event( $id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $this->events_table, $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function get_events( $limit = 20, $offset = 0, $filters = array() ) {
		global $wpdb;
		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['event_type'] ) ) {
			$where[]  = 'event_type = %s';
			$params[] = $filters['event_type'];
		}
		if ( ! empty( $filters['visitor_uuid'] ) ) {
			$where[]  = 'visitor_uuid = %s';
			$params[] = $filters['visitor_uuid'];
		}
		if ( ! empty( $filters['session_id'] ) ) {
			$where[]  = 'session_id = %s';
			$params[] = $filters['session_id'];
		}
		if ( ! empty( $filters['date_from'] ) ) {
			$where[]  = 'timestamp >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[]  = 'timestamp <= %s';
			$params[] = $filters['date_to'];
		}
		if ( ! empty( $filters['source'] ) ) {
			$where[]  = 'utm LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $filters['source'] ) . '%';
		}
		if ( ! empty( $filters['device'] ) ) {
			$where[]  = 'device = %s';
			$params[] = $filters['device'];
		}
		if ( ! empty( $filters['browser'] ) ) {
			$where[]  = 'browser = %s';
			$params[] = $filters['browser'];
		}

		$where_sql = implode( ' AND ', $where );
		$params[]  = $limit;
		$params[]  = $offset;

		$sql = "SELECT * FROM %i WHERE {$where_sql} ORDER BY timestamp DESC LIMIT %d OFFSET %d";
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_events_count( $filters = array() ) {
		global $wpdb;
		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $filters['event_type'] ) ) {
			$where[]  = 'event_type = %s';
			$params[] = $filters['event_type'];
		}
		if ( ! empty( $filters['date_from'] ) ) {
			$where[]  = 'timestamp >= %s';
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[]  = 'timestamp <= %s';
			$params[] = $filters['date_to'];
		}

		$where_sql = implode( ' AND ', $where );
		$sql       = "SELECT COUNT(*) FROM %i WHERE {$where_sql}";
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	/**
	 * Build the shared WHERE clause + params for WhatsApp signal queries.
	 *
	 * @param array  $filters Filter values (date_from, date_to, source, utm_medium, campaign, page, visitor).
	 * @param string $alias   Optional table alias (e.g. 'e.'). Keys are whitelisted, so interpolation is safe.
	 * @return array{0: string, 1: array}
	 */
	private function build_whatsapp_where( $filters, $alias = '' ) {
		global $wpdb;
		$where  = array( "{$alias}event_type = 'whatsapp_click'" );
		$params = array();

		if ( ! empty( $filters['date_from'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_from'] ) ) {
			$where[]  = "{$alias}timestamp >= %s";
			$params[] = $filters['date_from'];
		}
		if ( ! empty( $filters['date_to'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $filters['date_to'] ) ) {
			$where[]  = "{$alias}timestamp <= %s";
			$params[] = $filters['date_to'] . ' 23:59:59';
		}
		if ( ! empty( $filters['source'] ) ) {
			if ( 'Direct' === $filters['source'] ) {
				$where[] = "(NULLIF(TRIM({$alias}utm), '') IS NULL OR JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM({$alias}utm), ''), '$.utm_source')) IS NULL OR JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM({$alias}utm), ''), '$.utm_source')) = '')";
			} else {
				$where[]  = "JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM({$alias}utm), ''), '$.utm_source')) = %s";
				$params[] = $filters['source'];
			}
		}
		if ( ! empty( $filters['utm_medium'] ) ) {
			$where[]  = "JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM({$alias}utm), ''), '$.utm_medium')) = %s";
			$params[] = $filters['utm_medium'];
		}
		if ( ! empty( $filters['campaign'] ) ) {
			$where[]  = "JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM({$alias}utm), ''), '$.utm_campaign')) = %s";
			$params[] = $filters['campaign'];
		}
		if ( ! empty( $filters['page'] ) ) {
			$where[]  = "{$alias}page_url LIKE %s";
			$params[] = '%' . $wpdb->esc_like( $filters['page'] ) . '%';
		}
		if ( ! empty( $filters['visitor'] ) ) {
			$where[]  = "{$alias}visitor_uuid LIKE %s";
			$params[] = '%' . $wpdb->esc_like( $filters['visitor'] ) . '%';
		}

		return array( implode( ' AND ', $where ), $params );
	}

	public function get_whatsapp_clicks( $limit = 20, $offset = 0, $filters = array() ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters, 'e.' );
		$params[]                 = $limit;
		$params[]                 = $offset;

		$sql = "SELECT e.*, v.first_seen AS visitor_first_seen, v.ip AS visitor_ip
                FROM %i AS e
                LEFT JOIN %i AS v ON v.uuid = e.visitor_uuid
                WHERE {$where_sql}
                ORDER BY e.timestamp DESC
                LIMIT %d OFFSET %d";
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->events_table, $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_whatsapp_clicks_count( $filters = array() ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );
		$sql                      = "SELECT COUNT(*) FROM %i WHERE {$where_sql}";
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	/**
	 * KPI aggregation for the WhatsApp Signals page.
	 *
	 * @param array $filters
	 * @return array{total_signals:int, unique_visitors:int, repeat_visitors:int, top_source:string|null, top_source_count:int, top_page:string|null, top_page_count:int}
	 */
	public function get_whatsapp_kpis( $filters = array() ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );

		$sql = "SELECT COUNT(*) AS total_signals,
                       COUNT(DISTINCT visitor_uuid) AS unique_visitors
                FROM %i AS e WHERE {$where_sql}";
		$row = $wpdb->get_row( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		$repeat = (int) $wpdb->get_var( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT COUNT(*) FROM (
                    SELECT visitor_uuid FROM %i WHERE {$where_sql} GROUP BY visitor_uuid HAVING COUNT(*) > 1
                ) AS repeaters",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		$kpis = array(
			'total_signals'    => $row ? (int) $row->total_signals : 0,
			'unique_visitors'  => $row ? (int) $row->unique_visitors : 0,
			'repeat_visitors'  => $repeat,
			'top_source'       => null,
			'top_source_count' => 0,
			'top_page'         => null,
			'top_page_count'   => 0,
		);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		$top_source = $wpdb->get_row( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) AS name, COUNT(*) AS total
                FROM %i WHERE {$where_sql}
                  AND NULLIF(TRIM(utm), '') IS NOT NULL
                  AND JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) IS NOT NULL
                  AND JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) != ''
                GROUP BY name ORDER BY total DESC LIMIT 1",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		if ( $top_source && $top_source->name !== null ) {
			$kpis['top_source']       = $top_source->name;
			$kpis['top_source_count'] = (int) $top_source->total;
		}

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		$top_page = $wpdb->get_row( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT page_url AS name, COUNT(*) AS total
                FROM %i WHERE {$where_sql} AND page_url IS NOT NULL AND page_url != ''
                GROUP BY name ORDER BY total DESC LIMIT 1",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		if ( $top_page && $top_page->name !== null ) {
			$kpis['top_page']       = $top_page->name;
			$kpis['top_page_count'] = (int) $top_page->total;
		}

		return $kpis;
	}

	/**
	 * Signal counts grouped by UTM source (Direct fallback when untracked).
	 *
	 * @param array $filters
	 * @param int   $limit
	 * @return array
	 */
	public function get_whatsapp_signals_by_source( $filters = array(), $limit = 8 ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );
		$params[]                 = max( 1, (int) $limit );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		return $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT CASE WHEN NULLIF(TRIM(utm), '') IS NULL
                                 OR JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) IS NULL
                                 OR JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) = ''
                            THEN 'Direct' ELSE JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) END AS source,
                        COUNT(*) AS total
                FROM %i WHERE {$where_sql}
                GROUP BY source
                ORDER BY total DESC
                LIMIT %d",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
	}

	/**
	 * Signal counts grouped by page URL.
	 *
	 * @param array $filters
	 * @param int   $limit
	 * @return array
	 */
	public function get_whatsapp_signals_by_page( $filters = array(), $limit = 8 ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );
		$params[]                 = max( 1, (int) $limit );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		return $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT page_url AS page, COUNT(*) AS total
                FROM %i WHERE {$where_sql} AND page_url IS NOT NULL AND page_url != ''
                GROUP BY page_url
                ORDER BY total DESC
                LIMIT %d",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
	}

	/**
	 * Per-visitor signal counts for a set of visitor UUIDs (used to mark returning visitors).
	 *
	 * @param array $filters
	 * @param array $uuids   Visitor UUIDs to scope the query to.
	 * @return array UUID => count
	 */
	public function get_whatsapp_visitor_counts( $filters = array(), $uuids = array() ) {
		global $wpdb;
		$uuids = array_values( array_unique( array_filter( array_map( 'trim', (array) $uuids ) ) ) );
		if ( empty( $uuids ) ) {
			return array();
		}
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );
		$placeholders             = implode( ',', array_fill( 0, count( $uuids ), '%s' ) );
		$where_sql               .= ' AND visitor_uuid IN (' . $placeholders . ')';
		$params                   = array_merge( $params, $uuids );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		$rows = $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT visitor_uuid, COUNT(*) AS total FROM %i WHERE {$where_sql} GROUP BY visitor_uuid",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		$counts = array();
		foreach ( $rows as $row ) {
			$counts[ $row->visitor_uuid ] = (int) $row->total;
		}
		return $counts;
	}

	/**
	 * All rows matching the current filters for CSV export (respects filters, capped).
	 *
	 * @param array $filters
	 * @param int   $limit
	 * @return array
	 */
	public function get_whatsapp_export_rows( $filters = array(), $limit = 10000 ) {
		global $wpdb;
		list($where_sql, $params) = $this->build_whatsapp_where( $filters );
		$params[]                 = max( 1, (int) $limit );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		return $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT id, timestamp, visitor_uuid, page_url,
                    JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_source')) AS source,
                    JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_medium')) AS medium,
                    JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_campaign')) AS campaign,
                    JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_term')) AS term,
                    JSON_UNQUOTE(JSON_EXTRACT(NULLIF(TRIM(utm), ''), '$.utm_content')) AS content,
                    referrer, whatsapp_number, device, browser, os, country
                FROM %i
                WHERE {$where_sql}
                ORDER BY timestamp DESC LIMIT %d",
				array_merge( array( $this->events_table ), $params )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
	}

	public function get_whatsapp_clicks_distinct( $key ) {
		global $wpdb;
		$allowed = array( 'utm_source', 'utm_medium', 'utm_campaign' );
		if ( ! in_array( $key, $allowed, true ) ) {
			return array();
		}
		$like = '%' . $wpdb->esc_like( $key ) . '%';
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic fragments hold placeholders only (or allowlisted key); values bound by prepare().
		return $wpdb->get_col( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted; dynamic $sql assembled from constant allowlisted fragments; all values bound by prepare()
			$wpdb->prepare(
				"SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(utm, '$.{$key}')) AS value
                FROM %i
                WHERE event_type = 'whatsapp_click'
                  AND utm LIKE %s
                  AND JSON_UNQUOTE(JSON_EXTRACT(utm, '$.{$key}')) IS NOT NULL
                  AND JSON_UNQUOTE(JSON_EXTRACT(utm, '$.{$key}')) != %s
                ORDER BY value ASC",
				$this->events_table,
				$like,
				''
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
	}

	public function get_visitor_events( $visitor_uuid, $limit = 50 ) {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				'SELECT * FROM %i WHERE visitor_uuid = %s ORDER BY timestamp DESC LIMIT %d',
				$this->events_table,
				$visitor_uuid,
				$limit
			)
		);
	}

	public function get_session_events( $session_id, $limit = 500 ) {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				'SELECT * FROM %i WHERE session_id = %s ORDER BY timestamp ASC, id ASC LIMIT %d',
				$this->events_table,
				$session_id,
				$limit
			)
		);
	}

	public function insert_visitor( $data ) {
		global $wpdb;
		$result = $wpdb->insert( $this->visitors_table, $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- volatile analytics custom table; caching intentionally omitted
		if ( $result === false ) {
			return false;
		}
		return $wpdb->insert_id;
	}

	public function update_visitor( $uuid, $data ) {
		global $wpdb;
		return $wpdb->update( $this->visitors_table, $data, array( 'uuid' => $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function insert_session( $data ) {
		global $wpdb;
		$result = $wpdb->insert( $this->sessions_table, $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- volatile analytics custom table; caching intentionally omitted
		if ( $result === false ) {
			return false;
		}
		return $wpdb->insert_id;
	}

	public function update_session( $session_id, $data ) {
		global $wpdb;
		return $wpdb->update( $this->sessions_table, $data, array( 'session_id' => $session_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function insert_event( $data ) {
		global $wpdb;
		$result = $wpdb->insert( $this->events_table, $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- volatile analytics custom table; caching intentionally omitted
		if ( $result === false ) {
			return false;
		}
		return $wpdb->insert_id;
	}

	public static function generate_uuid() {
		$data    = random_bytes( 16 );
		$data[6] = chr( ord( $data[6] ) & 0x0f | 0x40 );
		$data[8] = chr( ord( $data[8] ) & 0x3f | 0x80 );
		return vsprintf( '%s%s-%s-%s-%s-%s%s%s', str_split( bin2hex( $data ), 4 ) );
	}

	/**
	 * Shared sliding-window (per-minute) rate limiter backed by transients.
	 *
	 * @param string $key   Unique transient key (already namespaced).
	 * @param int    $limit Max requests per 60 seconds.
	 * @return bool True when the caller is over the limit and should be refused.
	 */
	public static function rate_limit_hit( $key, $limit ) {
		$limit = max( 1, (int) $limit );
		$count = get_transient( $key );
		if ( $count === false ) {
			set_transient( $key, 1, 60 );
			return false;
		}
		if ( (int) $count >= $limit ) {
			return true;
		}
		set_transient( $key, (int) $count + 1, 60 );
		return false;
	}

	/**
	 * Validate and normalize a Y-m-d date range: blank/legacy values fall back
	 * to the earliest record / today, and an inverted range is swapped.
	 *
	 * @param string $date_from
	 * @param string $date_to
	 * @return array{0:string,1:string}
	 */
	public static function normalize_date_range( $date_from, $date_to ) {
		$date_from = is_string( $date_from ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date_from ) ? $date_from : '';
		$date_to   = is_string( $date_to ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date_to ) ? $date_to : '';

		if ( $date_from === '' ) {
			$date_from = self::instance()->get_earliest_record_date();
		}
		if ( $date_to === '' ) {
			$date_to = current_time( 'Y-m-d' );
		}

		if ( $date_from > $date_to ) {
			$tmp       = $date_from;
			$date_from = $date_to;
			$date_to   = $tmp;
		}

		return array( $date_from, $date_to );
	}

	public static function get_client_ip() {
		$remote_addr = '';
		if ( isset( $_SERVER['REMOTE_ADDR'] ) ) {
			$remote_addr = sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) );
		}
		if ( $remote_addr === '' || ! filter_var( $remote_addr, FILTER_VALIDATE_IP ) ) {
			$remote_addr = '';
		}

		$trusted_proxies = apply_filters( 'rt_wa_trusted_proxies', array() );

		if ( $remote_addr !== '' && self::is_trusted_proxy( $remote_addr, $trusted_proxies ) ) {
			$forwarded = self::get_forwarded_ip();
			if ( $forwarded !== '' ) {
				return $forwarded;
			}
		}

		return $remote_addr;
	}

	private static function get_forwarded_ip() {
		$candidates = array();
		if ( isset( $_SERVER['HTTP_CF_CONNECTING_IP'] ) ) {
			$candidates[] = sanitize_text_field( wp_unslash( $_SERVER['HTTP_CF_CONNECTING_IP'] ) );
		}
		if ( isset( $_SERVER['HTTP_X_FORWARDED_FOR'] ) ) {
			$xff = sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_FORWARDED_FOR'] ) );
			foreach ( explode( ',', $xff ) as $part ) {
				$candidates[] = trim( $part );
			}
		}
		if ( isset( $_SERVER['HTTP_X_REAL_IP'] ) ) {
			$candidates[] = sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_REAL_IP'] ) );
		}

		foreach ( $candidates as $candidate ) {
			if ( filter_var( $candidate, FILTER_VALIDATE_IP ) ) {
				return $candidate;
			}
		}
		return '';
	}

	private static function is_trusted_proxy( $ip, $proxies ) {
		foreach ( (array) $proxies as $proxy ) {
			$proxy = trim( (string) $proxy );
			if ( $proxy === '' ) {
				continue;
			}
			if ( strpos( $proxy, '/' ) !== false ) {
				if ( self::ip_in_cidr( $ip, $proxy ) ) {
					return true;
				}
			} elseif ( $ip === $proxy ) {
				return true;
			}
		}
		return false;
	}

	private static function ip_in_cidr( $ip, $cidr ) {
		$parts  = explode( '/', $cidr, 2 );
		$subnet = $parts[0];
		$bits   = isset( $parts[1] ) ? (int) $parts[1] : null;

		if ( $bits === null ) {
			return $ip === $subnet;
		}

		if ( strpos( $ip, ':' ) !== false ) {
			$ip_bin  = self::ip_to_bin( $ip, 16 );
			$sub_bin = self::ip_to_bin( $subnet, 16 );
			if ( $ip_bin === false || $sub_bin === false ) {
				return false;
			}
			return substr( $ip_bin, 0, $bits ) === substr( $sub_bin, 0, $bits );
		}

		$ip_long  = ip2long( $ip );
		$sub_long = ip2long( $subnet );
		if ( $ip_long === false || $sub_long === false ) {
			return false;
		}
		$mask = ( $bits === 0 ) ? 0 : ( -1 << ( 32 - $bits ) ) & 0xFFFFFFFF;
		return ( ( $ip_long & $mask ) === ( $sub_long & $mask ) );
	}

	private static function ip_to_bin( $ip, $length ) {
		$packed = @inet_pton( $ip );
		if ( $packed === false ) {
			return false;
		}
		$bin = '';
		for ( $i = 0; $i < $length; $i++ ) {
			$bin .= sprintf( '%08b', ord( $packed[ $i ] ) );
		}
		return $bin;
	}

	public function hash_api_key( $api_key ) {
		return hash( 'sha256', $api_key );
	}

	public function is_hashed_key( $stored_key ) {
		return strlen( $stored_key ) === 64 && ctype_xdigit( $stored_key );
	}

	public function insert_api_key( $data ) {
		global $wpdb;
		$data['api_key'] = $this->hash_api_key( $data['api_key'] );
		$wpdb->insert( $this->api_keys_table, $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->insert_id;
	}

	public function get_api_key( $api_key ) {
		global $wpdb;

		$hashed = $this->hash_api_key( $api_key );
		$row    = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE api_key = %s AND status = %s', $this->api_keys_table, $hashed, 'active' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( $row ) {
			return $row;
		}

		// Legacy fallback: keys created before v1.0.1 are stored in plaintext.
		// Only accept the raw value when it is not itself a 64-char hex hash,
		// otherwise the stored SHA-256 digest would be usable as a credential.
		if ( ! $this->is_hashed_key( $api_key ) ) {
			return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE api_key = %s AND status = %s', $this->api_keys_table, $api_key, 'active' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
		}

		return null;
	}

	public function get_api_keys() {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC', $this->api_keys_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function revoke_api_key( $id ) {
		global $wpdb;
		return $wpdb->update( $this->api_keys_table, array( 'status' => 'revoked' ), array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function delete_api_key( $id ) {
		global $wpdb;
		return $wpdb->delete( $this->api_keys_table, array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function delete_revoked_api_keys() {
		global $wpdb;
		return $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE status = %s', $this->api_keys_table, 'revoked' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function delete_visitor_data( $uuid ) {
		global $wpdb;
		$wpdb->delete( $this->events_table, array( 'visitor_uuid' => $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
		$wpdb->delete( $this->sessions_table, array( 'visitor_uuid' => $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
		$wpdb->delete( $this->page_views_table, array( 'visitor_uuid' => $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
		return $wpdb->delete( $this->visitors_table, array( 'uuid' => $uuid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
	}

	public function cleanup_old_data( $retention_days = 365 ) {
		global $wpdb;
		$retention_days = max( 1, absint( $retention_days ) );
		$cutoff         = wp_date( 'Y-m-d H:i:s', time() - ( $retention_days * DAY_IN_SECONDS ) );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE timestamp < %s', $this->events_table, $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE start_time < %s', $this->sessions_table, $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE timestamp < %s', $this->page_views_table, $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE last_seen < %s', $this->visitors_table, $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
	}

	public function get_total_events( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_total_visitors( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_total_sessions( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		list($clause, $params) = $this->build_date_clause( 'start_time', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->sessions_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_total_wa_clicks( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                        = 'SELECT COUNT(*) FROM %i WHERE event_type = %s';
		$params                     = array( $this->events_table, 'whatsapp_click' );
		list($clause, $date_params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                       .= $clause;
		$params                     = array_merge( $params, $date_params );
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_earliest_record_date() {
		global $wpdb;
		$visitors = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(first_seen) FROM %i', $this->visitors_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$sessions = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(start_time) FROM %i', $this->sessions_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$events = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(timestamp) FROM %i', $this->events_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$dates = array_filter( array( $visitors, $sessions, $events ) );
		if ( empty( $dates ) ) {
			return current_time( 'Y-m-d' );
		}
		$earliest = min( $dates );
		return substr( $earliest, 0, 10 );
	}

	public function get_wa_clicks_chart( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql    = 'SELECT DATE(timestamp) as date, COUNT(*) as count
                FROM %i
                WHERE event_type = %s';
		$params = array( $this->events_table, 'whatsapp_click' );

		if ( $date_from ) {
			$sql     .= ' AND timestamp >= %s';
			$params[] = $date_from;
		}
		if ( $date_to ) {
			$sql     .= ' AND timestamp <= %s';
			$params[] = $date_to . ' 23:59:59';
		}
		$sql .= ' GROUP BY DATE(timestamp) ORDER BY date ASC';

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_visitors_chart( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql    = 'SELECT DATE(first_seen) as date, COUNT(*) as count
                FROM %i
                WHERE 1=1';
		$params = array( $this->visitors_table );

		if ( $date_from ) {
			$sql     .= ' AND first_seen >= %s';
			$params[] = $date_from;
		}
		if ( $date_to ) {
			$sql     .= ' AND first_seen <= %s';
			$params[] = $date_to . ' 23:59:59';
		}
		$sql .= ' GROUP BY DATE(first_seen) ORDER BY date ASC';

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_sessions_chart( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql    = 'SELECT DATE(start_time) as date, COUNT(*) as count
                FROM %i
                WHERE 1=1';
		$params = array( $this->sessions_table );

		if ( $date_from ) {
			$sql     .= ' AND start_time >= %s';
			$params[] = $date_from;
		}
		if ( $date_to ) {
			$sql     .= ' AND start_time <= %s';
			$params[] = $date_to . ' 23:59:59';
		}
		$sql .= ' GROUP BY DATE(start_time) ORDER BY date ASC';

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_conversion_rate( $date_from = null, $date_to = null ) {
		$total_sessions = $this->get_total_sessions( $date_from, $date_to );
		if ( $total_sessions === 0 ) {
			return 0;
		}
		$wa_clicks = $this->get_total_wa_clicks( $date_from, $date_to );
		return round( ( $wa_clicks / $total_sessions ) * 100, 2 );
	}

	public function get_top_landing_pages( $limit = 10, $date_from = null, $date_to = null, $offset = 0 ) {
		global $wpdb;
		$sql                   = "SELECT landing_page, COUNT(*) as visitors,
                SUM(wa_clicks) as wa_clicks
                FROM %i
                WHERE landing_page IS NOT NULL AND landing_page != ''";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY landing_page
                ORDER BY visitors DESC
                LIMIT %d OFFSET %d';
		$params                = array_merge( array( $this->visitors_table ), $params );
		$params[]              = $limit;
		$params[]              = $offset;
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_top_landing_pages_count( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT COUNT(DISTINCT landing_page) FROM %i
                WHERE landing_page IS NOT NULL AND landing_page != ''";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_top_sources( $limit = 10, $date_from = null, $date_to = null, $offset = 0 ) {
		global $wpdb;
		$sql                   = "SELECT first_source as source, COUNT(*) as visitors,
                SUM(wa_clicks) as wa_clicks
                FROM %i
                WHERE first_source IS NOT NULL AND first_source != ''";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY first_source
                ORDER BY visitors DESC
                LIMIT %d OFFSET %d';
		$params                = array_merge( array( $this->visitors_table ), $params );
		$params[]              = $limit;
		$params[]              = $offset;
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_top_sources_count( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT COUNT(DISTINCT first_source) FROM %i
                WHERE first_source IS NOT NULL AND first_source != ''";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_top_campaigns( $limit = 10, $date_from = null, $date_to = null, $offset = 0 ) {
		global $wpdb;
		$sql                   = "SELECT
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign')) as campaign,
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_source')) as source,
                COUNT(DISTINCT visitor_uuid) as visitors,
                COUNT(*) as clicks
                FROM %i
                WHERE event_type = 'whatsapp_click'
                AND utm IS NOT NULL AND utm != ''
                AND utm LIKE '%utm_campaign%'";
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= " GROUP BY JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign')), JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_source'))
                ORDER BY clicks DESC
                LIMIT %d OFFSET %d";
		$params                = array_merge( array( $this->events_table ), $params );
		$params[]              = $limit;
		$params[]              = $offset;
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_top_campaigns_count( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT COUNT(*) FROM (
                SELECT 1 FROM %i
                WHERE event_type = 'whatsapp_click'
                AND utm IS NOT NULL AND utm != ''
                AND utm LIKE '%utm_campaign%'";
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= " GROUP BY JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign')), JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_source'))
                ) t";
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_recent_events( $limit = 10, $date_from = null, $date_to = null, $offset = 0 ) {
		global $wpdb;
		$sql                   = "SELECT e.*, v.first_source as visitor_source, v.country
                FROM %i e
                LEFT JOIN %i v ON e.visitor_uuid = v.uuid
                WHERE e.event_type = 'whatsapp_click'";
		list($clause, $params) = $this->build_date_clause( 'e.timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' ORDER BY e.timestamp DESC
                LIMIT %d OFFSET %d';
		$params                = array_merge( array( $this->events_table, $this->visitors_table ), $params );
		$params[]              = $limit;
		$params[]              = $offset;
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_recent_events_count( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT COUNT(*) FROM %i e
                WHERE e.event_type = 'whatsapp_click'";
		list($clause, $params) = $this->build_date_clause( 'e.timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().
	}

	public function get_sources_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT
                CASE WHEN first_source IS NULL OR first_source = '' THEN 'Direct' ELSE first_source END as source,
                COUNT(*) as visitors,
                SUM(wa_clicks) as clicks,
                ROUND((SUM(wa_clicks) / NULLIF(COUNT(*), 0)) * 100, 2) as conversion
            FROM %i
            WHERE 1=1";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY source
            ORDER BY visitors DESC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_campaign_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                   = "SELECT
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign')) as campaign,
                COUNT(DISTINCT visitor_uuid) as visitors,
                COUNT(*) as clicks,
                ROUND((COUNT(*) / NULLIF((SELECT COUNT(*) FROM %i WHERE event_type = 'whatsapp_click'{$clause}), 0)) * 100, 2) as conversion
            FROM %i
            WHERE event_type = 'whatsapp_click'
            AND utm IS NOT NULL AND utm != ''
            AND utm LIKE '%utm_campaign%'{$clause}
            GROUP BY JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign'))
            ORDER BY clicks DESC";
		$params                = array_merge( array( $this->events_table ), $params, array( $this->events_table ), $params );
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	/**
	 * Top WhatsApp-signal pages with clicks, unique visitors, and share of total.
	 *
	 * @param string|null $date_from
	 * @param string|null $date_to
	 * @param int         $limit
	 * @return array
	 */
	public function get_dashboard_top_pages( $date_from = null, $date_to = null, $limit = 5 ) {
		global $wpdb;
		$sql                        = "SELECT page_url AS page,
                          COUNT(*) AS clicks,
                          COUNT(DISTINCT visitor_uuid) AS visitors
                   FROM %i
                   WHERE event_type = %s
                   AND page_url IS NOT NULL AND page_url != ''";
		$params                     = array( $this->events_table, 'whatsapp_click' );
		list($clause, $date_params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                       .= $clause;
		$params                     = array_merge( $params, $date_params );
		$sql                       .= ' GROUP BY page_url ORDER BY clicks DESC LIMIT %d';
		$params[]                   = max( 1, (int) $limit );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	/**
	 * WhatsApp-signal campaigns (UTM) with source, medium, clicks, and unique visitors.
	 *
	 * @param string|null $date_from
	 * @param string|null $date_to
	 * @param int         $limit
	 * @return array
	 */
	public function get_dashboard_campaigns( $date_from = null, $date_to = null, $limit = 5 ) {
		global $wpdb;
		$sql                        = "SELECT
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_campaign')) AS campaign,
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_source')) AS source,
                JSON_UNQUOTE(JSON_EXTRACT(utm, '$.utm_medium')) AS medium,
                COUNT(DISTINCT visitor_uuid) AS visitors,
                COUNT(*) AS clicks
                FROM %i
                WHERE event_type = %s
                AND utm IS NOT NULL AND utm != ''
                AND utm LIKE %s";
		$params                     = array( $this->events_table, 'whatsapp_click', '%utm_campaign%' );
		list($clause, $date_params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                       .= $clause;
		$params                     = array_merge( $params, $date_params );
		$sql                       .= ' GROUP BY campaign, source, medium
                ORDER BY clicks DESC
                LIMIT %d';
		$params[]                   = max( 1, (int) $limit );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_landing_pages_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT landing_page as page,
                COUNT(*) as visitors,
                SUM(wa_clicks) as wa_clicks,
                ROUND((SUM(wa_clicks) / NULLIF(COUNT(*), 0)) * 100, 2) as rate
            FROM %i
            WHERE landing_page IS NOT NULL AND landing_page != ''";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY landing_page
            ORDER BY visitors DESC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_device_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT
                CASE WHEN device IS NULL OR device = '' THEN 'Unknown' ELSE device END as device,
                COUNT(*) as count
            FROM %i
            WHERE 1=1";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY device
            ORDER BY count DESC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_browser_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT
                CASE WHEN browser IS NULL OR browser = '' THEN 'Unknown' ELSE browser END as browser,
                COUNT(*) as count
            FROM %i
            WHERE 1=1";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY browser
            ORDER BY count DESC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_os_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT
                CASE WHEN os IS NULL OR os = '' THEN 'Unknown' ELSE os END as os,
                COUNT(*) as count
            FROM %i
            WHERE 1=1";
		list($clause, $params) = $this->build_date_clause( 'first_seen', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY os
            ORDER BY count DESC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->visitors_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_hourly_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT HOUR(timestamp) as hour, COUNT(*) as count
            FROM %i
            WHERE event_type = 'whatsapp_click'";
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY HOUR(timestamp)
            ORDER BY hour ASC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_weekly_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT DAYOFWEEK(timestamp) as day, COUNT(*) as count
            FROM %i
            WHERE event_type = 'whatsapp_click'";
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= ' GROUP BY DAYOFWEEK(timestamp)
            ORDER BY day ASC';
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_monthly_report( $date_from = null, $date_to = null ) {
		global $wpdb;
		$sql                   = "SELECT DATE_FORMAT(timestamp, '%Y-%m') as month, COUNT(*) as count
            FROM %i
            WHERE event_type = 'whatsapp_click'";
		list($clause, $params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$sql                  .= $clause;
		$sql                  .= " GROUP BY DATE_FORMAT(timestamp, '%Y-%m')
            ORDER BY month ASC";
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $this->events_table ), $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql assembled from allowlisted fragments; values bound by prepare().; volatile analytics custom table; caching intentionally omitted
	}

	public function get_funnel_data( $date_from = null, $date_to = null ) {
		global $wpdb;
		$visitors = $this->get_total_visitors( $date_from, $date_to );
		$sessions = $this->get_total_sessions( $date_from, $date_to );

		$page_views_sql                  = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		list($page_clause, $page_params) = $this->build_date_clause( 'timestamp', $date_from, $date_to );
		$page_views_sql                 .= $page_clause;
		$page_views                      = (int) $wpdb->get_var( $wpdb->prepare( $page_views_sql, array_merge( array( $this->page_views_table ), $page_params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- dynamic $sql assembled from allowlisted fragments; values bound by prepare().

		$wa_clicks = $this->get_total_wa_clicks( $date_from, $date_to );

		return array(
			'visitors'   => $visitors,
			'sessions'   => $sessions,
			'page_views' => $page_views,
			'wa_clicks'  => $wa_clicks,
		);
	}

	public function get_visitor_wa_clicks( $visitor_uuid ) {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				"SELECT * FROM %i
                WHERE visitor_uuid = %s AND event_type = 'whatsapp_click'
                ORDER BY timestamp DESC",
				$this->events_table,
				$visitor_uuid
			)
		);
	}

	/**
	 * Get saved UTM links, newest first.
	 *
	 * @param int $limit Maximum number of links to return.
	 * @return array List of UTM link rows.
	 */
	public function get_utm_links( $limit = 200 ) {
		global $wpdb;
		$limit = max( 1, min( 500, (int) $limit ) );
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d',
				$this->utm_links_table,
				$limit
			)
		);
	}

	/**
	 * Check whether a final URL already exists as a saved link.
	 *
	 * @param string $final_url The final URL to check.
	 * @return int Matching link ID, or 0 when not found.
	 */
	public function utm_link_exists( $final_url ) {
		global $wpdb;
		if ( ! is_string( $final_url ) || trim( $final_url ) === '' ) {
			return 0;
		}
		$id = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare(
				'SELECT id FROM %i WHERE final_url = %s LIMIT 1',
				$this->utm_links_table,
				trim( $final_url )
			)
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return (int) $id;
	}

	/**
	 * Insert a new UTM link.
	 *
	 * @param array $data Sanitized link data (name, base_url, wa_number, utm_*, final_url).
	 * @return int New link ID or 0 on failure.
	 */
	public function insert_utm_link( $data ) {
		global $wpdb;
		$defaults = array(
			'name'         => '',
			'base_url'     => '',
			'wa_number'    => '',
			'utm_source'   => '',
			'utm_medium'   => '',
			'utm_campaign' => '',
			'utm_term'     => '',
			'utm_content'  => '',
			'final_url'    => '',
		);
		$data     = array_merge( $defaults, array_intersect_key( $data, $defaults ) );

		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->utm_links_table,
			array(
				'name'         => $data['name'],
				'base_url'     => $data['base_url'],
				'wa_number'    => $data['wa_number'],
				'utm_source'   => $data['utm_source'],
				'utm_medium'   => $data['utm_medium'],
				'utm_campaign' => $data['utm_campaign'],
				'utm_term'     => $data['utm_term'],
				'utm_content'  => $data['utm_content'],
				'final_url'    => $data['final_url'],
				'created_at'   => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $result ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Delete a saved UTM link by ID.
	 *
	 * @param int $id Link ID.
	 * @return bool Whether the link was deleted.
	 */
	public function delete_utm_link( $id ) {
		global $wpdb;
		$id = (int) $id;
		if ( $id <= 0 ) {
			return false;
		}
		$deleted = $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->utm_links_table,
			array( 'id' => $id ),
			array( '%d' )
		);
		return false !== $deleted;
	}
}