<?php

/**
 * The analytics functionality of the plugin.
 *
 * @link       https://pinnerpress.com
 * @since      0.1.0
 *
 * @package    PinnerPress
 * @subpackage PinnerPress/includes
 */

/**
 * The analytics functionality of the plugin.
 *
 * Handles tracking and storing analytics data for PinnerPress interactions.
 *
 * @package    PinnerPress
 * @subpackage PinnerPress/includes
 */
if (!class_exists('PinnerPress_Analytics')) {
class PinnerPress_Analytics {

    /**
     * The table name for analytics data.
     *
     * @since    0.1.0
     * @access   private
     * @var      string    $table_name    The analytics table name.
     */
    private $table_name;

    /**
     * The table name for image analytics data.
     *
     * @since    1.1.0
     * @access   private
     * @var      string    $image_table_name    The image analytics table name.
     */
    private $image_table_name;

    /**
     * The table name for post analytics data.
     *
     * @since    1.2.0
     * @access   private
     * @var      string    $post_table_name    The post analytics table name.
     */
    private $post_table_name;

    /**
     * Initialize the class and set its properties.
     *
     * @since    0.1.0
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'pinnerpress_analytics';
        $this->image_table_name = $wpdb->prefix . 'pinnerpress_image_analytics';
        $this->post_table_name = $wpdb->prefix . 'pinnerpress_post_analytics';
        
        // Register AJAX actions for analytics tracking
        add_action('wp_ajax_pinnerpress_track_event', array($this, 'track_event_ajax'));
        add_action('wp_ajax_nopriv_pinnerpress_track_event', array($this, 'track_event_ajax'));
        
        // Register AJAX actions for analytics data retrieval
        add_action('wp_ajax_pinnerpress_get_analytics_data', array($this, 'get_analytics_data_ajax'));
        
        // Register AJAX action for top posts data retrieval
        add_action('wp_ajax_pinnerpress_get_top_posts', array($this, 'get_top_posts_ajax'));
        
        // Register AJAX actions for image analytics
        add_action('wp_ajax_pinnerpress_get_posts_for_analytics', array($this, 'get_posts_for_analytics_ajax'));
        add_action('wp_ajax_pinnerpress_get_post_images_analytics', array($this, 'get_post_images_analytics_ajax'));
        add_action('wp_ajax_pinnerpress_get_image_detailed_analytics', array($this, 'get_image_detailed_analytics_ajax'));
        add_action('wp_ajax_pinnerpress_get_all_posts', array($this, 'get_all_posts_ajax'));
        add_action('wp_ajax_pinnerpress_get_post_by_id', array($this, 'get_post_by_id_ajax'));

        // Register AJAX action for top images
        add_action('wp_ajax_pinnerpress_get_top_images', array($this, 'get_top_images_ajax'));

        // Register AJAX actions for post analytics
        add_action('wp_ajax_pinnerpress_get_post_analytics_data', array($this, 'get_post_analytics_data_ajax'));
        add_action('wp_ajax_pinnerpress_get_single_post_analytics', array($this, 'get_single_post_analytics_ajax'));
        // DISABLED: Impression tracking removed
        // add_action('wp_ajax_pinnerpress_track_impression', array($this, 'track_impression_ajax'));
        // add_action('wp_ajax_nopriv_pinnerpress_track_impression', array($this, 'track_impression_ajax'));

        // DISABLED: Image impression tracking AJAX handlers
        // add_action('wp_ajax_pinnerpress_track_image_impression', array($this, 'track_image_impression_ajax'));
        // add_action('wp_ajax_nopriv_pinnerpress_track_image_impression', array($this, 'track_image_impression_ajax'));
        
        // Check and create missing tables only when the plugin version changes
        // (avoids 3x SHOW TABLES queries + migration checks on every page load)
        add_action('init', array($this, 'maybe_ensure_tables_exist'));
        
        // Register wp-cron hook for background migrations
        add_action('pinnerpress_run_migrations', array($this, 'run_migrations_background'));
    }

    /**
     * Validate an internal table name before interpolating into SQL.
     * Allows only plugin-owned tables or core posts table with safe characters.
     *
     * @since 1.3.1
     * @param string $table Table name to validate.
     * @return bool True if table name is safe for use, false otherwise.
     */
    private function is_valid_table_name($table) {
        global $wpdb;

        // Only allow our known tables or core posts table
        $allowed = array(
            $this->table_name,
            $this->image_table_name,
            $this->post_table_name,
            $wpdb->posts,
        );

        if (!in_array($table, $allowed, true)) {
            return false;
        }

        // Extra defense-in-depth: enforce safe characters
        return (bool) preg_match('/^[A-Za-z0-9_]+$/', $table);
    }

    /**
     * Check if user has premium access.
     *
     * @since    0.1.0
     * @return   bool    True if premium, false otherwise
     */
    private function is_premium() {
        return function_exists('pinnerpress_can_use_premium_features') ? pinnerpress_can_use_premium_features() : false;
    }

    /**
     * Create the analytics table.
     *
     * @since    0.1.0
     */
    public function create_table() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            event_type varchar(50) NOT NULL,
            event_data text,
            post_id bigint(20),
            user_ip varchar(45),
            user_agent text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY event_type (event_type),
            KEY post_id (post_id),
            KEY created_at (created_at)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Also create image analytics table
        $this->create_image_table();
        
        // Also create post analytics table
        $this->create_post_table();
    }

    /**
     * Create the image analytics table.
     *
     * @since    1.1.0
     */
    public function create_image_table() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->image_table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            image_url TEXT NOT NULL,
            image_id VARCHAR(255),
            attachment_id bigint(20),
            event_type varchar(50) NOT NULL,
            event_count bigint(20) DEFAULT 1,
            date date NOT NULL,
            last_clicked datetime DEFAULT CURRENT_TIMESTAMP,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_image_event_date (post_id, image_id, event_type, date),
            KEY post_id (post_id),
            KEY attachment_id (attachment_id),
            KEY event_type (event_type),
            KEY image_id (image_id),
            KEY date (date)
        ) $charset_collate;";

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

    /**
     * Create the post analytics table.
     *
     * @since    1.2.0
     */
    public function create_post_table() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->post_table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            impressions bigint(20) DEFAULT 0,
            pin_button_clicks bigint(20) DEFAULT 0,
            banner_clicks bigint(20) DEFAULT 0,
            follow_clicks bigint(20) DEFAULT 0,
            call_out_banner_clicks bigint(20) DEFAULT 0,
            date date NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY unique_post_date (post_id, date),
            KEY post_id (post_id),
            KEY date (date)
        ) $charset_collate;";

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

    /**
     * Drop the analytics table.
     *
     * @since    0.1.0
     */
    public function drop_table() {
        global $wpdb;
        // Use direct queries for DROP TABLE with strict validation of identifiers
        if ($this->is_valid_table_name($this->table_name)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Identifier validated, schema change required for plugin cleanup
            $wpdb->query("DROP TABLE IF EXISTS {$this->table_name}");
        }
        if ($this->is_valid_table_name($this->image_table_name)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Identifier validated, schema change required for plugin cleanup
            $wpdb->query("DROP TABLE IF EXISTS {$this->image_table_name}");
        }
        if ($this->is_valid_table_name($this->post_table_name)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Identifier validated, schema change required for plugin cleanup
            $wpdb->query("DROP TABLE IF EXISTS {$this->post_table_name}");
        }
    }

    /**
     * Only run the expensive table/migration checks when the plugin version
     * changes (i.e. after an update or fresh install).  On normal page loads
     * this is a single get_option() comparison — no SHOW TABLES queries.
     *
     * Table creation runs synchronously (essential for plugin function).
     * Data migrations are deferred to a wp-cron background task to avoid
     * blocking page loads with potentially expensive queries.
     *
     * @since    1.2.1
     */
    public function maybe_ensure_tables_exist() {
        $current_version = defined('PINNERPRESS_VERSION') ? PINNERPRESS_VERSION : '0.0.0';
        $stored_version  = get_option('pinnerpress_db_version', '');

        if ($stored_version === $current_version) {
            return; // Tables and migrations already verified for this version
        }

        // Tables created but migrations haven't completed yet — re-schedule
        $tables_version = get_option('pinnerpress_tables_version', '');
        if ($tables_version === $current_version) {
            if (!wp_next_scheduled('pinnerpress_run_migrations')) {
                wp_schedule_single_event(time() + 10, 'pinnerpress_run_migrations');
            }
            if (defined('DISABLE_WP_CRON') && DISABLE_WP_CRON) {
                $this->run_migrations_background();
            }
            return;
        }

        // Version mismatch (upgrade, fresh install, or first run) — ensure tables exist
        $this->ensure_tables_exist_only();

        // Only persist the version if ALL three analytics tables exist now.
        // If any table creation failed (transient DB issue, permissions), we
        // want the next request to retry rather than silently skipping.
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time verification after table creation attempt
        $main_ok  = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        $image_ok = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->image_table_name));
        $post_ok  = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->post_table_name));
        if ($main_ok && $image_ok && $post_ok) {
            // Mark tables as created but migrations still pending.
            // The DB version is only set after migrations complete (in run_migrations_background)
            // so that if cron never fires, the next request retries.
            update_option('pinnerpress_tables_version', $current_version, true);

            // Schedule data migrations as a background task instead of running synchronously.
            // This prevents expensive migration queries from blocking page loads.
            if (!wp_next_scheduled('pinnerpress_run_migrations')) {
                wp_schedule_single_event(time() + 10, 'pinnerpress_run_migrations');
            }

            // Fallback: if WP-Cron is disabled and no external runner is configured,
            // run migrations synchronously to avoid leaving schemas unmigrated.
            if (defined('DISABLE_WP_CRON') && DISABLE_WP_CRON) {
                $this->run_migrations_background();
            }
        }
    }

    /**
     * Ensure tables exist without running migrations (synchronous, fast).
     *
     * @since    1.3.2
     */
    private function ensure_tables_exist_only() {
        global $wpdb;
        
        // Check if main analytics table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check
        $main_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        if (!$main_table_exists) {
            $this->create_table();
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check
            $image_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->image_table_name));
            if (!$image_table_exists) {
                $this->create_image_table();
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check
            $post_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->post_table_name));
            if (!$post_table_exists) {
                $this->create_post_table();
            }
        }
    }

    /**
     * Run data migrations in background (called via wp-cron).
     * Sets pinnerpress_db_version on completion so future requests
     * skip both table creation and migration checks.
     *
     * @since    1.3.2
     */
    public function run_migrations_background() {
        $this->check_and_run_migration();

        // Mark fully complete — tables exist and migrations have run
        $current_version = defined('PINNERPRESS_VERSION') ? PINNERPRESS_VERSION : '0.0.0';
        update_option('pinnerpress_db_version', $current_version, true);
    }

    /**
     * Check if tables exist and create them if they don't.
     *
     * @since    1.2.0
     */
    public function ensure_tables_exist() {
        global $wpdb;
        
        // Check if main analytics table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check, caching would cause issues after table creation/deletion
        $main_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        if (!$main_table_exists) {
            // Main analytics table does not exist, creating it
            // create_table() creates all three tables
            $this->create_table();
        } else {
            // Check if image analytics table exists
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check, caching would cause issues after table creation/deletion
            $image_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->image_table_name));
            if (!$image_table_exists) {
                // Image analytics table does not exist, creating it
                $this->create_image_table();
            }
            
            // Check if post analytics table exists
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check, caching would cause issues after table creation/deletion
            $post_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->post_table_name));
            if (!$post_table_exists) {
                // Post analytics table does not exist, creating it
                $this->create_post_table();
            }
        }
        
        // Always run migrations after ensuring tables exist, even after
        // create_table() — so the DB version is never marked complete
        // before migrations have had a chance to run.
        $this->check_and_run_migration();
    }

    /**
     * Track an analytics event.
     *
     * @since    0.1.0
     * @param    string    $event_type    The type of event to track
     * @param    array     $event_data    Additional data for the event
     * @param    int       $post_id       The post ID (optional)
     */
    public function track_event($event_type, $event_data = array(), $post_id = null) {
        // Check if analytics is enabled
        if (get_option('pinnerpress_analytics_enabled', '1') !== '1') {
            // Tracking disabled in settings
            return false;
        }

        // Check if we should ignore logged-in users
        if (get_option('pinnerpress_analytics_ignore_logged_in', '0') === '1' && is_user_logged_in()) {
            // Ignoring logged-in user tracking
            return false;
        }

        global $wpdb;

        // Sanitize event type
        $event_type = sanitize_text_field($event_type);
        
        // Get user IP (with privacy considerations)
        $user_ip = $this->get_user_ip();
        
        // Get user agent
        $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'])) : '';
        
        // Prepare event data
        $event_data_json = !empty($event_data) ? wp_json_encode($event_data) : null;
        
        // Log for debugging
        // Tracking event
        
        // Insert the event
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Direct insert required for custom analytics table
        $result = $wpdb->insert(
            $this->table_name,
            array(
                'event_type' => $event_type,
                'event_data' => $event_data_json,
                'post_id' => $post_id,
                'user_ip' => $user_ip,
                'user_agent' => $user_agent,
                'created_at' => current_time('mysql')
            ),
            array(
                '%s',
                '%s',
                '%d',
                '%s',
                '%s',
                '%s'
            )
        );

        if ($result === false) {
            // Failed to insert event
        } else {
            // Successfully tracked event
            
            // Update post analytics
            if ($post_id) {
                $this->update_post_analytics($event_type, $post_id);
            }
        }

        return $result !== false;
    }

    /**
     * AJAX handler for tracking events.
     *
     * @since    0.1.0
     */
    public function track_event_ajax() {
        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Rate limiting: max 10 events per IP per minute to prevent bot abuse
        // Uses raw IP (not anonymized) so users behind the same NAT don't share a bucket
        $raw_ip = $this->get_raw_user_ip();
        $rate_key = 'pinnerpress_rate_' . md5($raw_ip);
        $rate_count = (int) get_transient($rate_key);
        if ($rate_count >= 10) {
            wp_send_json_error('Rate limit exceeded');
            return;
        }
        set_transient($rate_key, $rate_count + 1, 60);

        // Get event data
        $event_type = isset($_POST['event_type']) ? sanitize_text_field(wp_unslash($_POST['event_type'])) : '';
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : null;
        $event_data = isset($_POST['event_data']) ? array_map('sanitize_text_field', wp_unslash($_POST['event_data'])) : array();

        // Track the event to main analytics table
        $result = $this->track_event($event_type, $event_data, $post_id);
        
        // Also track image-specific event if image data is present
        $image_result = true; // Default to true for non-image events
        if ($result && !empty($event_data['image_url']) && $post_id && in_array($event_type, ['pin_button_click', 'image_banner_click'])) {
            $image_url = esc_url_raw($event_data['image_url']);
            $image_id = isset($event_data['image_id']) ? sanitize_text_field($event_data['image_id']) : null;
            $attachment_id = isset($event_data['attachment_id']) ? absint($event_data['attachment_id']) : null;
            
            // Track to image analytics table
            $image_result = $this->track_image_event($event_type, $post_id, $image_url, $image_id, $attachment_id);
            
            if (!$image_result) {
                // Image tracking failed
            }
        }

        if ($result) {
            wp_send_json_success('Event tracked successfully');
        } else {
            wp_send_json_error('Failed to track event');
        }
    }

    /**
     * Track an image-specific analytics event.
     *
     * @since    1.1.0
     * @param    string    $event_type      Type of event
     * @param    int       $post_id         Post ID
     * @param    string    $image_url       URL of the image
     * @param    string    $image_id        Optional unique image identifier
     * @param    int       $attachment_id   Optional WordPress attachment ID
     */
    public function track_image_event($event_type, $post_id, $image_url, $image_id = null, $attachment_id = null) {
        // Check if analytics is enabled
        if (get_option('pinnerpress_analytics_enabled', '1') !== '1') {
            // Image tracking disabled in settings
            return false;
        }

        // Check if we should ignore logged-in users
        if (get_option('pinnerpress_analytics_ignore_logged_in', '0') === '1' && is_user_logged_in()) {
            // Ignoring logged-in user image tracking
            return false;
        }

        global $wpdb;

        // Generate image_id if not provided
        if (empty($image_id)) {
            $image_id = $this->generate_image_id($image_url);
        }

        $today = current_time('Y-m-d');

        // Use INSERT ... ON DUPLICATE KEY UPDATE for per-day tracking
        // This matches the unique key: (post_id, image_id, event_type, date)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time analytics tracking
        $result = $wpdb->query($wpdb->prepare(
            "INSERT INTO %i (post_id, image_url, image_id, attachment_id, event_type, event_count, date, last_clicked, created_at)
             VALUES (%d, %s, %s, %d, %s, 1, %s, %s, %s)
             ON DUPLICATE KEY UPDATE
                event_count = event_count + 1,
                last_clicked = %s,
                image_url = %s,
                attachment_id = COALESCE(%d, attachment_id)",
            $this->image_table_name,
            $post_id,
            $image_url,
            $image_id,
            $attachment_id,
            $event_type,
            $today,
            current_time('mysql'),
            current_time('mysql'),
            current_time('mysql'),
            $image_url,
            $attachment_id
        ));

        if ($result === false) {
            // Failed to track image event
            return false;
        }

        return true;
    }

    /**
     * Generate a consistent image ID from URL.
     *
     * @since    1.1.0
     * @param    string    $image_url    The image URL
     * @return   string                  The generated image ID
     */
    private function generate_image_id($image_url) {
        // Remove query parameters and hash
        $clean_url = strtok($image_url, '?');
        $clean_url = strtok($clean_url, '#');
        
        // Get filename
        $filename_with_ext = basename($clean_url);
        
        // Remove WordPress size suffix (e.g., -150x150) if present
        $filename_with_ext = preg_replace('/-\d+x\d+(\.\w+)$/', '$1', $filename_with_ext);
        
        // Get filename without extension
        $filename = pathinfo($filename_with_ext, PATHINFO_FILENAME);
        
        // FIXED: Convert to lowercase for consistent case handling
        $filename = strtolower($filename);
        
        // If filename is too generic, use a hash
        if (strlen($filename) < 3 || in_array($filename, array('image', 'img', 'photo', 'pic'))) {
            return 'img_' . substr(md5($clean_url), 0, 8);
        }
        
        // FIXED: Use strtolower and sanitize_title for consistent results
        return substr(strtolower(sanitize_title($filename)), 0, 255);
    }

    /**
     * Make end date inclusive by adding one day.
     *
     * @since    1.1.0
     * @param    string    $end_date     End date (Y-m-d format)
     * @return   string                  End date + 1 day (Y-m-d format)
     */
    private function make_end_date_inclusive($end_date) {
        // Add 1 day to make the end date inclusive of the full day
        // This way DATE(created_at) < '2025-09-25' includes all of 2025-09-24
        return gmdate('Y-m-d', strtotime($end_date . ' +1 day'));
    }

    /**
     * Get analytics data for images in a specific post.
     *
     * @since    1.1.0
     * @param    int       $post_id       Post ID
     * @param    string    $start_date    Start date (Y-m-d)
     * @param    string    $end_date      End date (Y-m-d)
     * @return   array                    Image analytics data
     */
    public function get_post_image_analytics($post_id, $start_date, $end_date) {
        global $wpdb;

        // First, get all images from the post content
        $post = get_post($post_id);
        if (!$post) {
            return array();
        }

        $all_images = $this->extract_images_from_post_content($post->post_content);

        // Get analytics data for images that have been clicked
        // Filter by date column to match post analytics behavior
        // Include records where date IS NULL for backwards compatibility during migration
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics query with table name, caching not beneficial
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT
                image_id,
                image_url,
                attachment_id,
                event_type,
                SUM(event_count) as total_clicks,
                MIN(created_at) as first_click,
                MAX(last_clicked) as last_click
             FROM %i
             WHERE post_id = %d AND (date IS NULL OR (date >= %s AND date <= %s))
             GROUP BY image_id, image_url, attachment_id, event_type
             HAVING total_clicks > 0
             ORDER BY total_clicks DESC",
            $this->image_table_name,
            $post_id,
            $start_date,
            $end_date
        ));

        // Group analytics by image - Handle both old case-sensitive and new lowercase keys
        $analytics_data = array();
        foreach ($results as $row) {
            // Use both the existing image_id and generate a new one for compatibility
            $existing_key = $row->image_id ?: md5($row->image_url);
            $new_key = $this->generate_image_id($row->image_url) ?: md5($row->image_url);
            
            // Use the new key as primary, but check for existing data under old key
            $image_key = $new_key;
            
            if (!isset($analytics_data[$image_key])) {
                $analytics_data[$image_key] = array(
                    'image_id' => $new_key,
                    'image_url' => $row->image_url,
                    'attachment_id' => $row->attachment_id,
                    'analytics' => array(
                        'total_clicks' => 0,
                        'first_click' => $row->first_click,
                        'last_click' => $row->last_click
                    )
                );
            }
            
            // Add event type data
            $analytics_data[$image_key]['analytics'][$row->event_type] = intval($row->total_clicks);
            $analytics_data[$image_key]['analytics']['total_clicks'] += intval($row->total_clicks);
            
            // Debug logging for total clicks calculation
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // Adding clicks for image
                // Event type and clicks tracked
                // New total clicks updated
            }
            
            // Update first/last click times
            if ($row->first_click < $analytics_data[$image_key]['analytics']['first_click']) {
                $analytics_data[$image_key]['analytics']['first_click'] = $row->first_click;
            }
            if ($row->last_click > $analytics_data[$image_key]['analytics']['last_click']) {
                $analytics_data[$image_key]['analytics']['last_click'] = $row->last_click;
            }
        }

        // Merge all images with analytics data
        // Track added image keys to prevent duplicates (same image can appear multiple times in post content)
        $final_images = array();
        $added_image_keys = array();
        foreach ($all_images as $image) {
            $image_key = $image['image_id'] ?: md5($image['image_url']);

            // Skip if this image has already been added (prevents duplicate counting)
            if (isset($added_image_keys[$image_key])) {
                continue;
            }
            $added_image_keys[$image_key] = true;

            if (isset($analytics_data[$image_key])) {
                // Image has analytics data - ensure we have all event types
                $analytics = $analytics_data[$image_key]['analytics'];
                
                // Ensure all event types are present, even if zero
                if (!isset($analytics['pin_button_click'])) {
                    $analytics['pin_button_click'] = 0;
                }
                if (!isset($analytics['image_banner_click'])) {
                    $analytics['image_banner_click'] = 0;
                }
                if (!isset($analytics['impression'])) {
                    $analytics['impression'] = 0;
                }
                
                $final_images[] = array_merge($image, array(
                    'analytics' => $analytics
                ));
            } else {
                // Image has no analytics data
                $final_images[] = array_merge($image, array(
                    'analytics' => array(
                        'total_clicks' => 0,
                        'pin_button_click' => 0,
                        'image_banner_click' => 0,
                        'impression' => 0,
                        'first_click' => null,
                        'last_click' => null
                    )
                ));
            }
        }

        return $final_images;
    }

    /**
     * Extract all images from post content.
     *
     * @since    1.1.0
     * @param    string    $content    Post content
     * @return   array                Array of image data
     */
    private function extract_images_from_post_content($content) {
        $images = array();
        
        // Match all img tags
        preg_match_all('/<img[^>]+>/i', $content, $img_matches);
        
        foreach ($img_matches[0] as $img_tag) {
            // Extract src attribute
            if (preg_match('/src=["\']([^"\']+)["\']/', $img_tag, $src_match)) {
                $image_url = $src_match[1];
                
                // Extract attachment ID from class
                $attachment_id = null;
                if (preg_match('/class=["\'][^"\']*wp-image-(\d+)[^"\']*["\']/', $img_tag, $class_match)) {
                    $attachment_id = intval($class_match[1]);
                }
                
                // Extract alt attribute
                $alt_text = '';
                if (preg_match('/alt=["\']([^"\']*)["\']/', $img_tag, $alt_match)) {
                    $alt_text = $alt_match[1];
                }
                
                // Generate image ID
                $image_id = $this->generate_image_id($image_url);
                
                // Get thumbnail URL
                $thumbnail_url = $this->get_image_thumbnail_url($image_url, $attachment_id);
                
                $images[] = array(
                    'image_id' => $image_id,
                    'image_url' => $image_url,
                    'attachment_id' => $attachment_id,
                    'thumbnail_url' => $thumbnail_url,
                    'alt_text' => $alt_text
                );
            }
        }
        
        return $images;
    }

    /**
     * Get thumbnail URL for an image with higher resolution.
     *
     * @since    1.1.0
     * @param    string    $image_url        Original image URL
     * @param    int       $attachment_id    WordPress attachment ID
     * @return   string                      Higher resolution image URL
     */
    private function get_image_thumbnail_url($image_url, $attachment_id = null) {
        if ($attachment_id) {
            // Try to get larger image sizes for better quality in analytics
            $image_sizes = array('medium_large', 'large', 'medium');
            
            foreach ($image_sizes as $size) {
                $image_data = wp_get_attachment_image_src($attachment_id, $size);
                if ($image_data && $image_data[1] >= 300) { // Ensure minimum width of 300px
                    return $image_data[0];
                }
            }
            
            // Fallback to any available size if no suitable size found
            $image_data = wp_get_attachment_image_src($attachment_id, 'full');
            if ($image_data) {
                return $image_data[0];
            }
            
            // Last resort - try thumbnail
            $thumbnail = wp_get_attachment_image_src($attachment_id, 'thumbnail');
            if ($thumbnail) {
                return $thumbnail[0];
            }
        }
        
        // Return original URL as fallback
        return $image_url;
    }

    /**
     * Get all posts that have image analytics data.
     *
     * @since    1.1.0
     * @param    string    $start_date    Start date (Y-m-d)
     * @param    string    $end_date      End date (Y-m-d)
     * @param    int       $limit         Maximum number of posts
     * @return   array                    Posts with image analytics
     */
    public function get_posts_with_image_analytics($start_date, $end_date, $limit = 100) {
        global $wpdb;

        $end_date_inclusive = $this->make_end_date_inclusive($end_date);
        $posts_table = $wpdb->posts;
        if (!$this->is_valid_table_name($this->image_table_name) || !$this->is_valid_table_name($posts_table)) {
            return array();
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregation across plugin tables and core posts table; identifiers validated
        return $wpdb->get_results($wpdb->prepare(
            "SELECT DISTINCT
                ia.post_id,
                p.post_title,
                COUNT(DISTINCT ia.image_id) as image_count,
                SUM(ia.event_count) as total_clicks
             FROM %i ia
             INNER JOIN %i p ON ia.post_id = p.ID
             WHERE (
                   (ia.created_at >= %s AND ia.created_at < %s)
                   OR 
                   (ia.last_clicked >= %s AND ia.last_clicked < %s)
               )
               AND p.post_status = %s
             GROUP BY ia.post_id, p.post_title
             ORDER BY total_clicks DESC
             LIMIT %d",
            $this->image_table_name,
            $posts_table,
            $start_date,
            $end_date_inclusive,
            $start_date,
            $end_date_inclusive,
            'publish',
            $limit
        ));
    }

    /**
     * AJAX handler for getting posts for analytics dropdown.
     *
     * @since    1.1.0
     */
    public function get_posts_for_analytics_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Insufficient permissions');
            return;
        }

        // Check nonce for security
        if (!check_ajax_referer('pinnerpress_analytics_nonce', 'nonce', false)) {
            wp_send_json_error('Security check failed');
            return;
        }

        // Support both GET and POST (Select2 uses GET by default)
        $search = isset($_REQUEST['search']) ? sanitize_text_field(wp_unslash($_REQUEST['search'])) : '';
        $page   = isset($_REQUEST['page']) ? absint($_REQUEST['page']) : 1;
        $per_page = 20;

        global $wpdb;
        $posts_table = $wpdb->posts;
        if (!$this->is_valid_table_name($posts_table)) {
            wp_send_json_error('Invalid posts table');
            return;
        }

        try {
            // Build WHERE clause and params once, reuse for count and select
            $search_result = $this->build_post_search_params($search, $posts_table);
            $where_clause  = $search_result['where'];
            $where_params  = $search_result['params'];

            // Count query
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time admin search; prepared query against core posts table. Caching intentionally avoided.
            $count_params = array_merge(array($posts_table), $where_params);
            $total = $wpdb->get_var(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where_clause built from esc_like values only
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM %i WHERE post_type = 'post' AND post_status = 'publish'" . $where_clause,
                    ...$count_params
                )
            );

            if ($wpdb->last_error) {
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only
                    error_log('PinnerPress: Database error getting post count: ' . $wpdb->last_error);
                }
                wp_send_json_error(__('An error occurred while retrieving analytics data. Please try again.', 'pinnerpress'));
                return;
            }

            // Results query with ordering and pagination
            $offset = ($page - 1) * $per_page;
            $order_clause = !empty($search)
                ? " ORDER BY CASE
                        WHEN LOWER(post_title) = LOWER(%s) THEN 1
                        WHEN LOWER(post_title) LIKE LOWER(%s) THEN 2
                        WHEN LOWER(post_title) LIKE LOWER(%s) THEN 3
                        ELSE 4
                    END, post_date DESC"
                : " ORDER BY post_date DESC";

            $select_params = array_merge(array($posts_table), $where_params);
            if (!empty($search)) {
                // Add ORDER BY CASE params: exact, starts-with, contains
                $select_params[] = $search;
                $select_params[] = $search . '%';
                $select_params[] = '%' . $wpdb->esc_like($search) . '%';
            }
            $select_params[] = $per_page;
            $select_params[] = $offset;

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time admin search results; prepared query. Caching intentionally avoided.
            $posts = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where_clause and $order_clause built from safe values
                $wpdb->prepare(
                    "SELECT ID, post_title, post_date FROM %i WHERE post_type = 'post' AND post_status = 'publish'" . $where_clause . $order_clause . " LIMIT %d OFFSET %d",
                    ...$select_params
                )
            );

            if ($wpdb->last_error) {
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only
                    error_log('PinnerPress: Database error getting posts: ' . $wpdb->last_error);
                }
                wp_send_json_error(__('An error occurred while retrieving analytics data. Please try again.', 'pinnerpress'));
                return;
            }

            if ($posts === false) {
                wp_send_json_error(__('Failed to fetch posts. Please try again.', 'pinnerpress'));
                return;
            }

        } catch (Exception $e) {
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only
                error_log('PinnerPress: Exception in post search: ' . $e->getMessage());
            }
            wp_send_json_error(__('An unexpected error occurred. Please try again.', 'pinnerpress'));
            return;
        }

        // Format for Select2
        $formatted_posts = array();
        foreach ($posts as $post) {
            $formatted_posts[] = array(
                'id' => $post->ID,
                'text' => $post->post_title . ' (' . gmdate('M j, Y', strtotime($post->post_date)) . ')'
            );
        }

        $response = array(
            'posts' => $formatted_posts,
            'has_more' => ($total > ($page * $per_page))
        );

        // Include debug info if searching and WP_DEBUG is on
        if (!empty($search) && defined('WP_DEBUG') && WP_DEBUG) {
            $response['debug'] = array(
                'search_term' => $search,
                'total_found' => $total,
                'final_sql' => $wpdb->last_query
            );
        }

        wp_send_json_success($response);
    }

    /**
     * Build WHERE clause and parameters for post search queries.
     *
     * Returns a reusable array with 'where' (SQL fragment) and 'params' (values
     * for $wpdb->prepare).  Handles single-word, multi-word and no-spaces
     * search variants in one place so both count and select queries stay in sync.
     *
     * @since  1.2.0
     * @param  string $search      Sanitized search string (may be empty).
     * @param  string $posts_table The validated posts table name.
     * @return array  { 'where' => string, 'params' => array }
     */
    private function build_post_search_params($search, $posts_table) {
        global $wpdb;

        if (empty($search)) {
            return array('where' => '', 'params' => array());
        }

        $words = array_values(array_filter(explode(' ', $search)));
        $search_no_spaces = str_replace(' ', '', (string) $search);
        $has_no_spaces_variant = ($search_no_spaces !== $search);

        // Build OR conditions for the WHERE clause
        $conditions = array();
        $params     = array();

        // 1. Exact phrase match (always)
        $conditions[] = 'post_title LIKE %s';
        $params[]     = '%' . $wpdb->esc_like($search) . '%';

        // 2. No-spaces variant (e.g. "pin button" also matches "pinbutton")
        if ($has_no_spaces_variant) {
            $conditions[] = 'post_title LIKE %s';
            $params[]     = '%' . $wpdb->esc_like($search_no_spaces) . '%';
        }

        // 3. Individual word AND matching (each word must appear somewhere in title)
        if (count($words) > 1) {
            $word_conditions = array();
            foreach ($words as $word) {
                $word_conditions[] = 'post_title LIKE %s';
                $params[]          = '%' . $wpdb->esc_like($word) . '%';
            }
            $conditions[] = '(' . implode(' AND ', $word_conditions) . ')';
        }

        $where = ' AND (' . implode(' OR ', $conditions) . ')';

        return array('where' => $where, 'params' => $params);
    }

    /**
     * AJAX handler for getting all posts.
     *
     * @since    1.1.0
     */
    public function get_all_posts_ajax() {
        // Check nonce for security
        if (!check_ajax_referer('pinnerpress_analytics_nonce', 'nonce', false)) {
            wp_send_json_error('Invalid nonce');
            return;
        }

        // Check if user has permission
        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
            return;
        }

        // Get all published posts
        $posts = get_posts(array(
            'post_type' => 'post',
            'post_status' => 'publish',
            'posts_per_page' => -1,
            'orderby' => 'date',
            'order' => 'DESC'
        ));

        $formatted_posts = array();
        foreach ($posts as $post) {
            $formatted_posts[] = array(
                'id' => $post->ID,
                'text' => $post->post_title . ' (' . gmdate('M j, Y', strtotime($post->post_date)) . ')'
            );
        }

        wp_send_json_success(array('posts' => $formatted_posts));
    }

    /**
     * AJAX handler for getting a specific post by ID.
     *
     * @since    1.1.0
     */
    public function get_post_by_id_ajax() {
        // Check nonce for security
        if (!check_ajax_referer('pinnerpress_analytics_nonce', 'nonce', false)) {
            wp_send_json_error('Invalid nonce');
            return;
        }

        // Check if user has permission
        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
            return;
        }

        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        if (!$post_id) {
            wp_send_json_error('Invalid post ID');
            return;
        }

        $post = get_post($post_id);
        if (!$post) {
            wp_send_json_error('Post not found');
            return;
        }

        wp_send_json_success(array(
            'post' => array(
                'id' => $post->ID,
                'text' => $post->post_title . ' (' . gmdate('M j, Y', strtotime($post->post_date)) . ')'
            )
        ));
    }

    /**
     * AJAX handler for getting post images analytics.
     *
     * @since    1.1.0
     */
    public function get_post_images_analytics_ajax() {
        // Check nonce for security
        if (!check_ajax_referer('pinnerpress_analytics_nonce', 'nonce', false)) {
            wp_send_json_error('Invalid nonce');
            return;
        }

        // Check if user has permission
        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
            return;
        }

        // Check if analytics is enabled
        if (get_option('pinnerpress_analytics_enabled', '1') !== '1') {
            wp_send_json_error('Analytics is disabled');
            return;
        }

        // Get parameters
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : gmdate('Y-m-d', strtotime('-30 days'));
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : gmdate('Y-m-d');

        if (!$post_id) {
            wp_send_json_error('Invalid post ID');
            return;
        }

        // Get post data
        $post = get_post($post_id);
        if (!$post) {
            wp_send_json_error('Post not found');
            return;
        }

        // Get analytics data
        $images = $this->get_post_image_analytics($post_id, $start_date, $end_date);

        // Calculate summary data
        $total_clicks = 0;
        $best_performing = null;
        foreach ($images as $image) {
            $image_total = $image['analytics']['total_clicks'];
            $total_clicks += $image_total;
            
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG && $image_total > 0) {
                // Image summary calculated
                // Pin clicks counted
                // Banner clicks counted
            }
            
            if (!$best_performing || $image_total > $best_performing['clicks']) {
                $best_performing = array(
                    'image_url' => $image['image_url'],
                    'clicks' => $image_total
                );
            }
        }

        // Get post analytics data for the same date range
        $post_analytics = $this->get_single_post_analytics($post_id, $start_date, $end_date);
        
        // FORCE the total clicks to be calculated from post analytics components
        // This fixes the broken math where individual components don't add up to total
        if ($post_analytics && isset($post_analytics['totals'])) {
            $post_totals = $post_analytics['totals'];
            $calculated_total = ($post_totals['pin_clicks'] ?? 0) + 
                               ($post_totals['banner_clicks'] ?? 0) + 
                               ($post_totals['follow_clicks'] ?? 0) + 
                               ($post_totals['call_out_banner_clicks'] ?? 0);
            
            // Image analytics summary calculated
            // Post analytics total calculated
            // Calculated total determined
            
            $total_clicks = $calculated_total;
            // Final total clicks set
        }

        wp_send_json_success(array(
            'post_id' => $post_id,
            'post_title' => $post->post_title,
            'images' => $images,
            'post_analytics' => $post_analytics,
            'summary' => array(
                'total_images' => count($images),
                'total_clicks' => $total_clicks,
                'best_performing_image' => $best_performing,
                'date_range' => array(
                    'start' => $start_date,
                    'end' => $end_date
                )
            )
        ));
    }

    /**
     * AJAX handler for getting detailed analytics for a specific image.
     *
     * @since    1.1.0
     */
    public function get_image_detailed_analytics_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Check premium access for image analytics
        if (!$this->is_premium()) {
            wp_send_json_error('Image analytics is a premium feature. Please upgrade to access this functionality.');
        }

        $image_id = isset($_POST['image_id']) ? sanitize_text_field(wp_unslash($_POST['image_id'])) : '';
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : gmdate('Y-m-d', strtotime('-30 days'));
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : gmdate('Y-m-d');

        if (empty($image_id)) {
            wp_send_json_error('Invalid image ID');
        }

        global $wpdb;

        $end_date_inclusive = $this->make_end_date_inclusive($end_date);

        // Get daily analytics for the image
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Analytics reporting query with table name
        $daily_data = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                DATE(COALESCE(last_clicked, created_at)) as date,
                event_type,
                SUM(event_count) as clicks
             FROM %i
             WHERE image_id = %s
               AND (
                   (created_at >= %s AND created_at < %s)
                   OR 
                   (last_clicked >= %s AND last_clicked < %s)
               )
             GROUP BY DATE(COALESCE(last_clicked, created_at)), event_type
             ORDER BY date ASC",
            $this->image_table_name,
            $image_id,
            $start_date,
            $end_date_inclusive,
            $start_date,
            $end_date_inclusive
        ));

        wp_send_json_success(array(
            'image_id' => $image_id,
            'daily_data' => $daily_data
        ));
    }

    /**
     * AJAX handler for getting top images by combined clicks.
     *
     * @since    1.1.0
     */
    public function get_top_images_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Check if analytics is enabled
        if (get_option('pinnerpress_analytics_enabled', '1') !== '1') {
            wp_send_json_error('Analytics is disabled');
        }

        // Get parameters
        $page = isset($_POST['page']) ? absint($_POST['page']) : 1;
        $per_page = isset($_POST['per_page']) ? absint($_POST['per_page']) : 25;
        $requested_limit = isset($_POST['limit']) ? absint($_POST['limit']) : 50;

        // Validate limit based on premium status - enforce server-side to prevent bypassing
        $is_premium = function_exists('pinnerpress_is_premium') ? pinnerpress_is_premium() : false;
        $max_limit = $is_premium ? 300 : 50;
        $limit = min($requested_limit, $max_limit);

        // Get top images data
        $data = $this->get_top_images($page, $per_page, $limit);

        wp_send_json_success($data);
    }

    /**
     * Get top images by combined pin button and image banner clicks.
     *
     * @since    1.1.0
     * @param    int    $page       Page number for pagination
     * @param    int    $per_page   Number of items per page
     * @param    int    $limit      Total limit (50 for free, 300 for premium)
     * @return   array              Top images data
     */
    public function get_top_images($page = 1, $per_page = 25, $limit = 50) {
        global $wpdb;

        // Calculate offset
        $offset = ($page - 1) * $per_page;

        // Ensure we don't exceed the limit
        if ($offset >= $limit) {
            return array(
                'images' => array(),
                'total' => 0,
                'page' => $page,
                'per_page' => $per_page,
                'total_pages' => 0
            );
        }

        // Adjust per_page if it would exceed the limit
        $remaining = $limit - $offset;
        $fetch_per_page = min($per_page, $remaining);

        // Query to get top images by combined clicks
        // Group by image_url only to aggregate all events for the same image across posts
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics reporting query
        $images = $wpdb->get_results($wpdb->prepare(
            "SELECT
                MAX(ia.image_id) as image_id,
                ia.image_url,
                MAX(ia.attachment_id) as attachment_id,
                MAX(ia.post_id) as post_id,
                SUM(CASE WHEN ia.event_type = 'pin_button_click' THEN ia.event_count ELSE 0 END) as pin_clicks,
                SUM(CASE WHEN ia.event_type = 'image_banner_click' THEN ia.event_count ELSE 0 END) as banner_clicks,
                SUM(CASE WHEN ia.event_type = 'impression' THEN ia.event_count ELSE 0 END) as impressions,
                SUM(CASE WHEN ia.event_type IN ('pin_button_click', 'image_banner_click') THEN ia.event_count ELSE 0 END) as total_clicks
            FROM %i ia
            WHERE ia.event_type IN ('pin_button_click', 'image_banner_click', 'impression')
            GROUP BY ia.image_url
            HAVING total_clicks > 0
            ORDER BY total_clicks DESC
            LIMIT %d OFFSET %d",
            $this->image_table_name,
            $fetch_per_page,
            $offset
        ));

        // Get total count (limited to the specified limit)
        // Group by image_url to match main query
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics count query
        $total_result = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(*) as total
            FROM (
                SELECT ia.image_url
                FROM %i ia
                WHERE ia.event_type IN ('pin_button_click', 'image_banner_click')
                GROUP BY ia.image_url
                HAVING SUM(ia.event_count) > 0
                LIMIT %d
            ) as subquery",
            $this->image_table_name,
            $limit
        ));

        $total = $total_result ? intval($total_result->total) : 0;
        $total_pages = ceil($total / $per_page);

        // Process images and add post information
        $processed_images = array();
        foreach ($images as $index => $image) {
            // Get all posts where this image URL appears
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics query
            $image_posts = $wpdb->get_results($wpdb->prepare(
                "SELECT DISTINCT ia.post_id
                FROM %i ia
                WHERE ia.image_url = %s
                ORDER BY ia.post_id DESC",
                $this->image_table_name,
                $image->image_url
            ));

            // Build array of all posts containing this image
            $posts = array();
            foreach ($image_posts as $img_post) {
                $post = get_post($img_post->post_id);
                if ($post) {
                    $posts[] = array(
                        'post_id' => $post->ID,
                        'post_title' => $post->post_title,
                        'post_url' => get_permalink($post->ID)
                    );
                }
            }

            // Use first post as primary for backwards compatibility
            $primary_post = !empty($posts) ? $posts[0] : null;

            // Calculate metrics
            $pin_clicks = intval($image->pin_clicks);
            $banner_clicks = intval($image->banner_clicks);
            $impressions = intval($image->impressions);
            $total_clicks = intval($image->total_clicks);

            $pin_ctr = $impressions > 0 ? ($pin_clicks / $impressions) * 100 : 0;
            $banner_ctr = $impressions > 0 ? ($banner_clicks / $impressions) * 100 : 0;
            $conversion_rate = $impressions > 0 ? ($total_clicks / $impressions) * 100 : 0;

            $processed_images[] = array(
                'rank' => $offset + $index + 1,
                'image_id' => $image->image_id,
                'image_url' => $image->image_url,
                'attachment_id' => $image->attachment_id,
                'post_id' => $primary_post ? $primary_post['post_id'] : null,
                'post_title' => $primary_post ? $primary_post['post_title'] : 'Unknown Post',
                'post_url' => $primary_post ? $primary_post['post_url'] : '',
                'posts' => $posts,
                'analytics' => array(
                    'impressions' => $impressions,
                    'pin_button_click' => $pin_clicks,
                    'image_banner_click' => $banner_clicks,
                    'total_clicks' => $total_clicks,
                    'pin_ctr' => round($pin_ctr, 2),
                    'banner_ctr' => round($banner_ctr, 2),
                    'conversion_rate' => round($conversion_rate, 2)
                )
            );
        }

        return array(
            'images' => $processed_images,
            'total' => $total,
            'page' => $page,
            'per_page' => $per_page,
            'total_pages' => $total_pages
        );
    }

    /**
     * Get analytics data for a date range.
     *
     * @since    0.1.0
     * @param    string    $start_date    Start date (Y-m-d format)
     * @param    string    $end_date      End date (Y-m-d format)
     * @param    array     $event_types   Event types to include (optional)
     * @return   array                    Analytics data
     */
    public function get_analytics_data($start_date, $end_date, $event_types = array()) {
        global $wpdb;

        // Apply date range restrictions for free users
        list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);

        // Sanitize dates
        $start_date = sanitize_text_field($start_date);
        $end_date = sanitize_text_field($end_date);

        // Get all daily data within the date range
        $end_date_inclusive = $this->make_end_date_inclusive($end_date);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics reporting query with table name
        $all_daily_data = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                DATE(created_at) as date,
                event_type,
                COUNT(*) as count
            FROM %i
            WHERE created_at >= %s
              AND created_at < %s
            GROUP BY DATE(created_at), event_type
            ORDER BY date ASC, event_type ASC",
            $this->table_name,
            $start_date,
            $end_date_inclusive
        ));

        // Filter by event types if specified
        if (!empty($event_types)) {
            $event_types = array_map('sanitize_text_field', $event_types);
            $filtered_data = array_filter($all_daily_data, function($row) use ($event_types) {
                return in_array($row->event_type, $event_types, true);
            });
        } else {
            $filtered_data = $all_daily_data;
            // If no event types specified, get all available event types
            $event_types = $this->get_all_event_type_keys();
        }

        // Create a complete data structure with all dates and event types
        $complete_data = array();
        $data_by_date_type = array();
        
        // First, organize existing data by date and event type
        foreach ($filtered_data as $row) {
            $key = $row->date . '_' . $row->event_type;
            $data_by_date_type[$key] = $row->count;
        }
        
        // Generate all dates in the range
        $current = new DateTime($start_date);
        $end = new DateTime($end_date);
        $end->modify('+1 day'); // Include end date
        
        while ($current < $end) {
            $date_str = $current->format('Y-m-d');
            
            // Add data for each event type for this date
            foreach ($event_types as $event_type) {
                $key = $date_str . '_' . $event_type;
                $complete_data[] = (object) array(
                    'date' => $date_str,
                    'event_type' => $event_type,
                    'count' => isset($data_by_date_type[$key]) ? $data_by_date_type[$key] : '0'
                );
            }
            
            $current->modify('+1 day');
        }

        // Calculate totals from the filtered daily data
        $totals = array();
        foreach ($filtered_data as $row) {
            if (!isset($totals[$row->event_type])) {
                $totals[$row->event_type] = (object) array(
                    'event_type' => $row->event_type,
                    'total' => 0,
                );
            }
            $totals[$row->event_type]->total += $row->count;
        }

        // Sort totals by count descending
        usort(
            $totals,
            function ($a, $b) {
                return $b->total <=> $a->total;
            }
        );
        
        $result = array(
            'daily_data' => $complete_data,
            'totals' => array_values($totals),
        );
        
        // Filter data based on plan
        return pinnerpress_filter_analytics_data($result, 'chart_data');
    }

    /**
     * AJAX handler for getting analytics data.
     *
     * @since    0.1.0
     */
    public function get_analytics_data_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Get parameters
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : '';
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : '';
        $event_types = isset($_POST['event_types']) ? array_map('sanitize_text_field', wp_unslash($_POST['event_types'])) : array();

        // Apply premium restrictions for date range
        if (function_exists('pinnerpress_filter_analytics_date_range')) {
            list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);
        }

        // Get analytics data
        $data = $this->get_analytics_data($start_date, $end_date, $event_types);

        // Add premium information for frontend
        $data['is_premium'] = $this->is_premium();
        $data['date_range_filtered'] = $data['is_premium'] ? false : true;

        wp_send_json_success($data);
    }

    /**
     * Get top posts by event type for a date range.
     *
     * @since    0.1.0
     * @param    string    $start_date    Start date (Y-m-d format)
     * @param    string    $end_date      End date (Y-m-d format)
     * @param    string    $event_type    Event type to filter by
     * @param    int       $limit         Number of top posts to retrieve (default 10)
     * @return   array                    Top posts data with featured images
     */
    public function get_top_posts_by_event_type($start_date, $end_date, $event_type, $limit = 10) {
        global $wpdb;

        // Apply date range restrictions
        list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);
        
        // For non-truly-premium users (free and trial), limit to top 25 posts
        if (!function_exists('pinnerpress_is_truly_premium') || (!pinnerpress_is_truly_premium() && $limit > 25)) {
            $limit = 25;
        }

        // Sanitize inputs
        $start_date = sanitize_text_field($start_date);
        $end_date = sanitize_text_field($end_date);
        $event_type = sanitize_text_field($event_type);
        $limit = absint($limit);

        // Make end date inclusive for the query
        $end_date_inclusive = $this->make_end_date_inclusive($end_date);

        $posts_table = $wpdb->prefix . 'posts';
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query with table names
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT pa.post_id, p.post_title, COUNT(pa.id) as event_count
             FROM %i pa
             INNER JOIN %i p ON pa.post_id = p.ID
             WHERE pa.event_type = %s
               AND pa.created_at >= %s
               AND pa.created_at < %s
               AND pa.post_id IS NOT NULL
               AND p.post_status = %s 
             GROUP BY pa.post_id, p.post_title
             ORDER BY event_count DESC
             LIMIT %d",
            $this->table_name,
            $posts_table,
            $event_type,
            $start_date,
            $end_date_inclusive,
            'publish',
            $limit
        ));

        // Add featured image data to each post
        foreach ($results as $post) {
            $post->featured_image = $this->get_post_featured_image($post->post_id);
            $post->post_url = get_permalink($post->post_id);
        }

        return $results;
    }

    /**
     * Get featured image for a post with fallback to placeholder.
     *
     * @since    0.3.1
     * @param    int    $post_id    Post ID
     * @return   array              Featured image data
     */
    private function get_post_featured_image($post_id) {
        $featured_image_id = get_post_thumbnail_id($post_id);
        
        if ($featured_image_id) {
            // Try to get larger image sizes for better quality
            $image_sizes = array('large', 'medium_large', 'medium');
            
            foreach ($image_sizes as $size) {
                $image_data = wp_get_attachment_image_src($featured_image_id, $size);
                if ($image_data && $image_data[1] >= 400) { // Ensure minimum width of 400px
                    return array(
                        'url' => $image_data[0],
                        'width' => $image_data[1],
                        'height' => $image_data[2],
                        'has_image' => true,
                        'size_used' => $size
                    );
                }
            }
            
            // Fallback to any available size if no large size found
            $image_data = wp_get_attachment_image_src($featured_image_id, 'full');
            if ($image_data) {
                return array(
                    'url' => $image_data[0],
                    'width' => $image_data[1],
                    'height' => $image_data[2],
                    'has_image' => true,
                    'size_used' => 'full'
                );
            }
        }
        
        // Return placeholder data if no featured image
        return array(
            'url' => '',
            'width' => 300,
            'height' => 200,
            'has_image' => false
        );
    }

    /**
     * AJAX handler for getting top posts data.
     *
     * @since    0.1.0
     */
    public function get_top_posts_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Get parameters
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : gmdate('Y-m-d', strtotime('-30 days'));
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : gmdate('Y-m-d');
        $event_type = isset($_POST['event_type']) ? sanitize_text_field(wp_unslash($_POST['event_type'])) : 'pin_button_click'; // Default to pin_button_click
        $limit = isset($_POST['limit']) ? absint($_POST['limit']) : 10;

        // Apply premium restrictions for date range
        if (function_exists('pinnerpress_filter_analytics_date_range')) {
            list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);
        }

        // Get top posts data
        $data = $this->get_top_posts_by_event_type($start_date, $end_date, $event_type, $limit);

        wp_send_json_success($data);
    }

    /**
     * Get user IP address with privacy considerations.
     *
     * @since    0.1.0
     * @return   string    Anonymized IP address
     */
    private function get_user_ip() {
        $ip = '';
        
        // Check for various IP headers
        $ip_headers = array(
            'HTTP_CF_CONNECTING_IP',
            'HTTP_X_FORWARDED_FOR',
            'HTTP_X_FORWARDED',
            'HTTP_X_CLUSTER_CLIENT_IP',
            'HTTP_FORWARDED_FOR',
            'HTTP_FORWARDED',
            'REMOTE_ADDR'
        );
        
        foreach ($ip_headers as $header) {
            if (!empty($_SERVER[$header])) {
                $ip = sanitize_text_field(wp_unslash($_SERVER[$header]));
                // Take the first IP if there are multiple
                if (!empty($ip) && strpos($ip, ',') !== false) {
                    $ip = trim(explode(',', $ip)[0]);
                }
                break;
            }
        }
        
        // Anonymize IP for privacy (remove last octet for IPv4, last 80 bits for IPv6)
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            $ip_parts = explode('.', $ip);
            $ip_parts[3] = '0';
            $ip = implode('.', $ip_parts);
        } elseif (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
            $ip_parts = explode(':', $ip);
            // Keep only first 48 bits (3 groups of 16 bits)
            $ip = implode(':', array_slice($ip_parts, 0, 3)) . '::';
        }
        
        return $ip;
    }

    /**
     * Get the raw (non-anonymized) user IP for rate limiting.
     * Unlike get_user_ip(), this preserves the full IP so that users
     * behind the same NAT/carrier don't share a rate limit bucket.
     * This value is only used for transient rate-limit keys (hashed),
     * never stored in the database.
     *
     * Only trusts REMOTE_ADDR (set by the web server, not spoofable) and
     * HTTP_CF_CONNECTING_IP (set by Cloudflare's edge, not client-controlled).
     * Other forwarded headers (X-Forwarded-For, etc.) are client-controlled
     * and can be spoofed to bypass rate limiting.
     *
     * @since    1.1.9
     * @return   string    Raw user IP address.
     */
    private function get_raw_user_ip() {
        // Always use REMOTE_ADDR as the primary source — set by the web server,
        // never client-spoofable. When behind Cloudflare, REMOTE_ADDR is the
        // Cloudflare edge IP, but that's still fine for rate limiting since all
        // requests through the same edge share the bucket (Cloudflare has its
        // own bot protection). Using HTTP_CF_CONNECTING_IP without verifying
        // the request actually came from Cloudflare would let attackers spoof it.
        if (!empty($_SERVER['REMOTE_ADDR'])) {
            return sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR']));
        }

        return '';
    }

    /**
     * Get available event types.
     *
     * @since    0.1.0
     * @return   array    Array of event types with labels
     */
    public function get_event_types() {
        return array(
            'pin_button_click' => __('Pin Button Clicks', 'pinnerpress'),
            'follow_button_click' => __('Popup Follow Button Clicks', 'pinnerpress'),
            'image_banner_click' => __('Image Banner Clicks', 'pinnerpress'),
            'call_out_banner_click' => __('Callout Banner Clicks', 'pinnerpress'),
            'block_editor_button_click' => __('Post Block Follow Clicks', 'pinnerpress')
        );
    }

    /**
     * Get all event type keys.
     *
     * @since    0.1.0
     * @return   array    Array of event type keys
     */
    private function get_all_event_type_keys() {
        return array_keys($this->get_event_types());
    }

    /**
     * Clean up old analytics data.
     *
     * @since    0.1.0
     * @param    int    $days_to_keep    Number of days to keep data for
     */
    public function cleanup_old_data($days_to_keep = 90) {
        global $wpdb;

        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-{$days_to_keep} days"));
        if (!$this->is_valid_table_name($this->table_name)) {
            return;
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name validated, direct query for data migration
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM %i WHERE created_at < %s",
                $this->table_name,
                $cutoff_date
            )
        );
    }

    /**
     * Get analytics data for a specific post.
     *
     * @since    0.1.0
     * @param    int       $post_id    Post ID
     * @return   array                 Analytics data
     */
    public function get_post_analytics($post_id) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query required for analytics aggregation
        $analytics_results = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                event_type,
                COUNT(*) as total_clicks,
                MIN(created_at) as first_click,
                MAX(created_at) as last_click
            FROM %i
            WHERE post_id = %d
            GROUP BY event_type",
            $this->table_name,
            $post_id
        ));

        return $analytics_results;
    }

    /**
     * Get analytics data for multiple posts.
     *
     * @since    0.1.0
     * @param    array    $post_ids    Array of post IDs
     * @return   array                 Analytics data
     */
    public function get_posts_analytics($post_ids) {
        global $wpdb;

        if (empty($post_ids)) {
            return array();
        }

        // Sanitize post IDs
        $post_ids = array_map('absint', $post_ids);
        
        // Build query based on number of post IDs
        $num_posts = count($post_ids);
        if ($num_posts === 1) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT
                        post_id,
                        event_type,
                        COUNT(*) as total_clicks,
                        MIN(created_at) as first_click,
                        MAX(created_at) as last_click
                    FROM %i
                    WHERE post_id = %d
                    GROUP BY post_id, event_type",
                    $this->table_name,
                    $post_ids[0]
                )
            );
        } elseif ($num_posts === 2) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT
                        post_id,
                        event_type,
                        COUNT(*) as total_clicks,
                        MIN(created_at) as first_click,
                        MAX(created_at) as last_click
                    FROM %i
                    WHERE post_id IN (%d, %d)
                    GROUP BY post_id, event_type",
                    $this->table_name,
                    $post_ids[0],
                    $post_ids[1]
                )
            );
        } elseif ($num_posts === 3) {
            // Handle 3 post IDs
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT
                        post_id,
                        event_type,
                        COUNT(*) as total_clicks,
                        MIN(created_at) as first_click,
                        MAX(created_at) as last_click
                    FROM %i
                    WHERE post_id IN (%d, %d, %d)
                    GROUP BY post_id, event_type",
                    $this->table_name,
                    $post_ids[0],
                    $post_ids[1],
                    $post_ids[2]
                )
            );
        } elseif ($num_posts === 4) {
            // Handle 4 post IDs
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT
                        post_id,
                        event_type,
                        COUNT(*) as total_clicks,
                        MIN(created_at) as first_click,
                        MAX(created_at) as last_click
                    FROM %i
                    WHERE post_id IN (%d, %d, %d, %d)
                    GROUP BY post_id, event_type",
                    $this->table_name,
                    $post_ids[0],
                    $post_ids[1],
                    $post_ids[2],
                    $post_ids[3]
                )
            );
        } elseif ($num_posts === 5) {
            // Handle 5 post IDs
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT
                        post_id,
                        event_type,
                        COUNT(*) as total_clicks,
                        MIN(created_at) as first_click,
                        MAX(created_at) as last_click
                    FROM %i
                    WHERE post_id IN (%d, %d, %d, %d, %d)
                    GROUP BY post_id, event_type",
                    $this->table_name,
                    $post_ids[0],
                    $post_ids[1],
                    $post_ids[2],
                    $post_ids[3],
                    $post_ids[4]
                )
            );
        } else {
            // For 6+ post IDs, limit to first 10
            $limited_ids = array_slice($post_ids, 0, 10);
            $placeholders = array_fill(0, count($limited_ids), '%d');
            $params = array_merge(array($this->table_name), $limited_ids);
            
            if (count($limited_ids) === 6) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
                return $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT
                            post_id,
                            event_type,
                            COUNT(*) as total_clicks,
                            MIN(created_at) as first_click,
                            MAX(created_at) as last_click
                        FROM %i
                        WHERE post_id IN (%d, %d, %d, %d, %d, %d)
                        GROUP BY post_id, event_type",
                        $this->table_name,
                        $limited_ids[0],
                        $limited_ids[1],
                        $limited_ids[2],
                        $limited_ids[3],
                        $limited_ids[4],
                        $limited_ids[5]
                    )
                );
            } elseif (count($limited_ids) === 7) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
                return $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT
                            post_id,
                            event_type,
                            COUNT(*) as total_clicks,
                            MIN(created_at) as first_click,
                            MAX(created_at) as last_click
                        FROM %i
                        WHERE post_id IN (%d, %d, %d, %d, %d, %d, %d)
                        GROUP BY post_id, event_type",
                        $this->table_name,
                        $limited_ids[0],
                        $limited_ids[1],
                        $limited_ids[2],
                        $limited_ids[3],
                        $limited_ids[4],
                        $limited_ids[5],
                        $limited_ids[6]
                    )
                );
            } elseif (count($limited_ids) === 8) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
                return $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT
                            post_id,
                            event_type,
                            COUNT(*) as total_clicks,
                            MIN(created_at) as first_click,
                            MAX(created_at) as last_click
                        FROM %i
                        WHERE post_id IN (%d, %d, %d, %d, %d, %d, %d, %d)
                        GROUP BY post_id, event_type",
                        $this->table_name,
                        $limited_ids[0],
                        $limited_ids[1],
                        $limited_ids[2],
                        $limited_ids[3],
                        $limited_ids[4],
                        $limited_ids[5],
                        $limited_ids[6],
                        $limited_ids[7]
                    )
                );
            } elseif (count($limited_ids) === 9) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
                return $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT
                            post_id,
                            event_type,
                            COUNT(*) as total_clicks,
                            MIN(created_at) as first_click,
                            MAX(created_at) as last_click
                        FROM %i
                        WHERE post_id IN (%d, %d, %d, %d, %d, %d, %d, %d, %d)
                        GROUP BY post_id, event_type",
                        $this->table_name,
                        $limited_ids[0],
                        $limited_ids[1],
                        $limited_ids[2],
                        $limited_ids[3],
                        $limited_ids[4],
                        $limited_ids[5],
                        $limited_ids[6],
                        $limited_ids[7],
                        $limited_ids[8]
                    )
                );
            } else {
                // 10 post IDs
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
                return $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT
                            post_id,
                            event_type,
                            COUNT(*) as total_clicks,
                            MIN(created_at) as first_click,
                            MAX(created_at) as last_click
                        FROM %i
                        WHERE post_id IN (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d)
                        GROUP BY post_id, event_type",
                        $this->table_name,
                        $limited_ids[0],
                        $limited_ids[1],
                        $limited_ids[2],
                        $limited_ids[3],
                        $limited_ids[4],
                        $limited_ids[5],
                        $limited_ids[6],
                        $limited_ids[7],
                        $limited_ids[8],
                        $limited_ids[9]
                    )
                );
            }
        }
    }

    /**
     * Get analytics data for a specific post with pagination.
     *
     * @since    0.1.0
     * @param    int       $post_id       Post ID
     * @param    int       $per_page      Items per page
     * @param    int       $current_page  Current page number
     * @return   array                    Analytics data with pagination
     */
    public function get_post_analytics_paginated($post_id, $per_page = 10, $current_page = 1) {
        global $wpdb;

        $offset = ($current_page - 1) * $per_page;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time admin analytics listing; query is prepared and table name is internal to the plugin. Caching is intentionally avoided to reflect up-to-the-moment data.
        return $wpdb->get_results($wpdb->prepare(
            "SELECT 
                event_type,
                COUNT(*) as total_clicks,
                MIN(created_at) as first_click,
                MAX(created_at) as last_click
            FROM %i
            WHERE post_id = %d
            GROUP BY event_type
            LIMIT %d OFFSET %d",
            $this->table_name,
            $post_id,
            $per_page,
            $offset
        ));
    }

    /**
     * Get total analytics count for a specific post.
     *
     * @since    0.1.0
     * @param    int       $post_id    Post ID
     * @return   int                   Total count
     */
    public function get_post_analytics_total($post_id) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time analytics total; prepared query and internal table reference. No caching by design to avoid stale counts.
        $result = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(DISTINCT event_type) as total
            FROM %i
            WHERE post_id = %d",
            $this->table_name,
            $post_id
        ));
        return $result ? $result->total : 0;
    }

    /**
     * Migrate existing image analytics records to use consistent lowercase image IDs.
     * This fixes the case sensitivity issue that was causing analytics data mismatches.
     *
     * @since    1.1.1
     */
    public function migrate_image_analytics_case_sensitivity() {
        global $wpdb;

        // Starting image analytics case sensitivity migration
        if (!$this->is_valid_table_name($this->image_table_name)) {
            update_option('pinnerpress_image_analytics_case_migration_done', '1');
            return 0;
        }

        // Get all existing records
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Data migration query with table name
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time migration read. Prepared with validated internal table name.
        $records = $wpdb->get_results(
            $wpdb->prepare("SELECT id, image_url, image_id FROM %i WHERE image_id IS NOT NULL", $this->image_table_name)
        );

        $updated_count = 0;
        foreach ($records as $record) {
            $old_image_id = $record->image_id;
            $new_image_id = $this->generate_image_id($record->image_url);
            
            // Only update if the IDs are different
            if ($old_image_id !== $new_image_id) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time migration update; internal plugin table and formats specified.
                $result = $wpdb->update(
                    $this->image_table_name,
                    array('image_id' => $new_image_id),
                    array('id' => $record->id),
                    array('%s'),
                    array('%d')
                );
                
                if ($result !== false) {
                    $updated_count++;
                    // Updated image ID
                }
            }
        }

        // Image analytics migration completed
        
        // Store migration flag to prevent running again
        update_option('pinnerpress_image_analytics_case_migration_done', '1');
        
        return $updated_count;
    }

    /**
     * Check and run image analytics migration if needed.
     *
     * @since    1.1.1
     */
    private function check_and_run_migration() {
        // Run case sensitivity migration first
        if (get_option('pinnerpress_image_analytics_case_migration_done', '0') !== '1') {
            // Running case sensitivity migration
            $this->migrate_image_analytics_case_sensitivity();
        }
        
        // Run backfill migration second
        if (get_option('pinnerpress_image_analytics_backfill_done', '0') !== '1') {
            // Running backfill migration
            $this->backfill_missing_image_analytics();
        }
        
        // Run consolidation migration to fix duplicate records
        if (get_option('pinnerpress_image_analytics_consolidation_done', '0') !== '1') {
            // Running consolidation migration
            $this->consolidate_duplicate_image_analytics();
        }
        
        // Run image ID migration to fix missing image_id and attachment_id
        if (get_option('pinnerpress_image_analytics_id_migration_done', '0') !== '1') {
            // Running image ID migration
            $this->migrate_missing_image_ids();
        }

        // Run date column migration to add per-day tracking support
        if (get_option('pinnerpress_image_analytics_date_migration_done', '0') !== '1') {
            // Running date column migration
            $this->migrate_add_date_column();
        }
    }

    /**
     * Backfill missing image analytics data from main analytics table.
     * This helps recover analytics data that was tracked to main table but missing from image table.
     *
     * @since    1.1.1
     */
    public function backfill_missing_image_analytics() {
        global $wpdb;
        
        // Starting backfill of missing image analytics data
        if (!$this->is_valid_table_name($this->table_name) || !$this->is_valid_table_name($this->image_table_name)) {
            update_option('pinnerpress_image_analytics_backfill_done', '1');
            return 0;
        }
        
        // Get all events from main analytics that have image data but might be missing from image analytics
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Data migration query for one-time backfill
        $main_events = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, event_type, event_data, post_id, created_at 
                 FROM %i 
                 WHERE event_type IN ('pin_button_click', 'image_banner_click')
                   AND event_data IS NOT NULL
                   AND post_id IS NOT NULL
                 ORDER BY created_at ASC",
                $this->table_name
            )
        );
        
        $processed_count = 0;
        $added_count = 0;
        
        foreach ($main_events as $event) {
            $processed_count++;
            
            // Parse event data JSON
            $event_data = json_decode($event->event_data, true);
            if (!$event_data || !isset($event_data['image_url'])) {
                continue;
            }
            
            $image_url = $event_data['image_url'];
            $image_id = isset($event_data['image_id']) ? $event_data['image_id'] : null;
            $attachment_id = isset($event_data['attachment_id']) ? $event_data['attachment_id'] : null;
            
            // Generate image_id if missing
            if (empty($image_id)) {
                $image_id = $this->generate_image_id($image_url);
            }
            
            // Check if this exact event already exists in image analytics
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Checking before insert for data migration
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Backfill check prior to insert; prepared and scoped to internal table.
            $existing = $wpdb->get_row($wpdb->prepare(
                "SELECT id, event_count FROM %i 
                 WHERE post_id = %d AND image_url = %s AND event_type = %s",
                $this->image_table_name, $event->post_id, $image_url, $event->event_type
            ));
            
            if (!$existing) {
                // Insert new record
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Backfill insert into internal analytics table.
                $result = $wpdb->insert(
                    $this->image_table_name,
                    array(
                        'post_id' => $event->post_id,
                        'image_url' => $image_url,
                        'image_id' => $image_id,
                        'attachment_id' => $attachment_id,
                        'event_type' => $event->event_type,
                        'event_count' => 1,
                        'last_clicked' => $event->created_at,
                        'created_at' => $event->created_at
                    ),
                    array('%d', '%s', '%s', '%d', '%s', '%d', '%s', '%s')
                );
                
                if ($result !== false) {
                    $added_count++;
                    // Backfilled event
                }
            } else {
                // Record exists, but we might need to increment count if this represents multiple events
                // For now, just log that it exists
                // Event already exists in image analytics
            }
        }
        
        // Backfill completed
        
        // Store backfill flag to prevent running again
        update_option('pinnerpress_image_analytics_backfill_done', '1');
        
        return $added_count;
    }

    /**
     * Consolidate duplicate image analytics records.
     * This fixes records that have the same image_id but different image_urls.
     *
     * @since    1.2.1
     */
    public function consolidate_duplicate_image_analytics() {
        global $wpdb;
        
        // Starting consolidation of duplicate image analytics records
        
        // Find duplicates - records with same post_id, image_id, and event_type
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Data cleanup query for one-time consolidation
        $duplicates = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT post_id, image_id, event_type, COUNT(*) as record_count, 
                        SUM(event_count) as total_events,
                        MIN(created_at) as first_created,
                        MAX(last_clicked) as last_clicked,
                        GROUP_CONCAT(id) as record_ids,
                        GROUP_CONCAT(image_url SEPARATOR '|||') as urls
                 FROM %i
                 WHERE image_id IS NOT NULL AND image_id != ''
                 GROUP BY post_id, image_id, event_type
                 HAVING record_count > 1",
                $this->image_table_name
            )
        );
        
        $consolidated_count = 0;
        
        foreach ($duplicates as $duplicate) {
            $record_ids = explode(',', $duplicate->record_ids);
            $urls = explode('|||', $duplicate->urls);
            
            // Keep the first record and update it with consolidated data
            $keep_id = $record_ids[0];
            $primary_url = $urls[0]; // Use the first URL as primary
            
            // Update the record we're keeping with the total counts
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Consolidation update within migration; safe internal table.
            $wpdb->update(
                $this->image_table_name,
                array(
                    'event_count' => $duplicate->total_events,
                    'created_at' => $duplicate->first_created,
                    'last_clicked' => $duplicate->last_clicked,
                    'image_url' => $primary_url
                ),
                array('id' => $keep_id),
                array('%d', '%s', '%s', '%s'),
                array('%d')
            );
            
            // Delete the duplicate records
            for ($i = 1; $i < count($record_ids); $i++) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deleting duplicates as part of one-time consolidation.
                $wpdb->delete(
                    $this->image_table_name,
                    array('id' => $record_ids[$i]),
                    array('%d')
                );
            }
            
            $consolidated_count++;
            // Consolidated duplicate records
        }
        
        // Consolidation completed
        
        // Store consolidation flag to prevent running again
        update_option('pinnerpress_image_analytics_consolidation_done', '1');
        
        return $consolidated_count;
    }

    /**
     * Manually trigger consolidation of duplicate image analytics.
     * This can be called from admin to force consolidation.
     *
     * @since    1.2.1
     * @return   int    Number of consolidated groups
     */
    public function manual_consolidate_duplicates() {
        // Clear the flag to allow re-running
        delete_option('pinnerpress_image_analytics_consolidation_done');
        
        // Run the consolidation
        return $this->consolidate_duplicate_image_analytics();
    }
    
    /**
     * Migrate missing image_id and attachment_id for existing image analytics records.
     * This fixes records that have image_url but missing image_id or attachment_id.
     *
     * @since    1.1.1
     */
    private function migrate_missing_image_ids() {
        global $wpdb;
        
        if (!$this->is_valid_table_name($this->image_table_name)) {
            update_option('pinnerpress_image_analytics_id_migration_done', '1');
            return 0;
        }
        
        // Find records with missing image_id or attachment_id
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Data migration query for one-time update
        $records = $wpdb->get_results(
            $wpdb->prepare("SELECT id, image_url, attachment_id FROM %i 
             WHERE image_id IS NULL OR image_id = '' 
             OR (attachment_id IS NULL AND image_url LIKE %s)", 
             $this->image_table_name,
             '%wp-content/uploads%')
        );
        
        if (!$records) {
            update_option('pinnerpress_image_analytics_id_migration_done', '1');
            return 0;
        }
        
        $updated_count = 0;
        
        foreach ($records as $record) {
            $image_url = $record->image_url;
            $current_attachment_id = $record->attachment_id;
            
            // Generate image_id from URL
            $image_id = $this->generate_image_id($image_url);
            
            // Try to find attachment_id if missing
            $attachment_id = $current_attachment_id;
            if (!$attachment_id) {
                $attachment_id = attachment_url_to_postid($image_url);
            }
            
            // Update the record
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time ID migration update; internal table only.
            $updated = $wpdb->update(
                $this->image_table_name,
                array(
                    'image_id' => $image_id,
                    'attachment_id' => $attachment_id ?: null
                ),
                array('id' => $record->id),
                array('%s', '%d'),
                array('%d')
            );
            
            if ($updated) {
                $updated_count++;
            }
        }
        
        update_option('pinnerpress_image_analytics_id_migration_done', '1');
        // Image ID migration completed

        return $updated_count;
    }

    /**
     * Migrate image analytics table to add date column for per-day tracking.
     * This converts cumulative records to per-day records.
     *
     * @since    1.3.0
     * @return   int    Number of migrated records
     */
    private function migrate_add_date_column() {
        global $wpdb;

        if (!$this->is_valid_table_name($this->image_table_name)) {
            update_option('pinnerpress_image_analytics_date_migration_done', '1');
            return 0;
        }

        // Check if date column already exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema check
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SHOW COLUMNS FROM %i LIKE 'date'",
            $this->image_table_name
        ));

        if (empty($column_exists)) {
            // Add date column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema modification
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD COLUMN date date DEFAULT NULL AFTER event_count",
                $this->image_table_name
            ));

            // Populate date from created_at for existing records
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Data migration
            $wpdb->query($wpdb->prepare(
                "UPDATE %i SET date = DATE(created_at) WHERE date IS NULL",
                $this->image_table_name
            ));

            // Add index on date column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Index creation
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD INDEX date (date)",
                $this->image_table_name
            ));
        }

        // Check if new unique key exists, if not create it
        // First, drop the old unique key if it exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema check
        $old_key = $wpdb->get_results($wpdb->prepare(
            "SHOW INDEX FROM %i WHERE Key_name = 'unique_image_event'",
            $this->image_table_name
        ));

        if (!empty($old_key)) {
            // Drop old unique key
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema modification
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i DROP INDEX unique_image_event",
                $this->image_table_name
            ));
        }

        // Check if new unique key exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema check
        $new_key = $wpdb->get_results($wpdb->prepare(
            "SHOW INDEX FROM %i WHERE Key_name = 'unique_image_event_date'",
            $this->image_table_name
        ));

        if (empty($new_key)) {
            // Create new unique key including date
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema modification
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD UNIQUE KEY unique_image_event_date (post_id, image_id, event_type, date)",
                $this->image_table_name
            ));
        }

        update_option('pinnerpress_image_analytics_date_migration_done', '1');

        return 1;
    }

    /**
     * Track an impression for a post.
     *
     * @since    1.2.0
     * @param    int       $post_id    Post ID
     * @return   bool                  True on success, false on failure
     */
    public function track_impression($post_id) {
        global $wpdb;
        
        $today = current_time('Y-m-d');
        if (!$this->is_valid_table_name($this->post_table_name)) {
            return false;
        }
        
        // Use the same pattern as update_post_analytics which works for clicks
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time impression tracking
        $result = $wpdb->query($wpdb->prepare(
            "INSERT INTO %i (post_id, date, impressions)
             VALUES (%d, %s, 1)
             ON DUPLICATE KEY UPDATE impressions = impressions + 1",
            $this->post_table_name,
            $post_id,
            $today
        ));
        
        return $result !== false;
    }

    /**
     * AJAX handler for tracking impressions.
     *
     * @since    1.2.0
     */
    public function track_impression_ajax() {
        // Check if we should ignore logged-in users
        if (get_option('pinnerpress_analytics_ignore_logged_in', '0') === '1' && is_user_logged_in()) {
            // Ignoring logged-in user impression tracking
            wp_send_json_error('Impression tracking disabled for logged-in users');
        }

        // Check nonce for security, but be more flexible for impression tracking
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        
        // For logged-in users, verify nonce normally
        // For non-logged-in users, allow impression tracking without nonce verification
        // since impressions are basic analytics data that should be tracked for all visitors
        if (is_user_logged_in()) {
            if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
                wp_die('Security check failed');
            }
        } else {
            // For non-logged-in users, we still want to track impressions
            // Add basic security checks to prevent abuse
            if (empty($nonce)) {
                // Non-logged-in user attempting impression tracking without nonce
            }
            
            // Basic rate limiting for non-logged-in users
            $user_ip = $this->get_user_ip();
            $rate_limit_key = 'pinnerpress_impression_rate_limit_' . md5($user_ip);
            $current_count = intval(get_transient($rate_limit_key));
            
            // Allow max 50 impressions per IP per hour
            if ($current_count >= 50) {
                // Rate limit exceeded
                wp_send_json_error('Rate limit exceeded');
            }
            
            // Increment the counter
            set_transient($rate_limit_key, $current_count + 1, HOUR_IN_SECONDS);
        }

        // Get post ID
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        if (!$post_id) {
            wp_send_json_error('Invalid post ID');
        }

        // Track the impression
        $result = $this->track_impression($post_id);
        
        if ($result) {
            wp_send_json_success('Impression tracked');
        } else {
            wp_send_json_error('Failed to track impression');
        }
    }

    /**
     * AJAX handler for tracking image impressions.
     *
     * @since    1.2.0
     */
    public function track_image_impression_ajax() {
        // Check if we should ignore logged-in users
        if (get_option('pinnerpress_analytics_ignore_logged_in', '0') === '1' && is_user_logged_in()) {
            // Ignoring logged-in user image impression tracking
            wp_send_json_error('Image impression tracking disabled for logged-in users');
        }

        // Check nonce for security, but be more flexible for impression tracking
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        
        // For logged-in users, verify nonce normally
        // For non-logged-in users, allow impression tracking without nonce verification
        if (is_user_logged_in()) {
            if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
                wp_die('Security check failed');
            }
        } else {
            // For non-logged-in users, we still want to track impressions
            // Basic rate limiting for non-logged-in users
            $user_ip = $this->get_user_ip();
            $rate_limit_key = 'pinnerpress_image_impression_rate_limit_' . md5($user_ip);
            $current_count = intval(get_transient($rate_limit_key));
            
            // Allow max 100 image impressions per IP per hour (higher than post impressions)
            if ($current_count >= 100) {
                // Image impression rate limit exceeded
                wp_send_json_error('Rate limit exceeded');
            }
            
            // Increment the counter
            set_transient($rate_limit_key, $current_count + 1, HOUR_IN_SECONDS);
        }

        // Get parameters
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        $image_url = isset($_POST['image_url']) ? esc_url_raw(wp_unslash($_POST['image_url'])) : '';
        $image_id = isset($_POST['image_id']) ? sanitize_text_field(wp_unslash($_POST['image_id'])) : '';
        
        if (!$post_id || !$image_url) {
            wp_send_json_error('Invalid parameters');
        }

        // Try to get attachment ID from the image URL if not provided
        $attachment_id = null;
        if (empty($attachment_id)) {
            $attachment_id = attachment_url_to_postid($image_url);
        }

        // Track the image impression using the existing track_image_event method
        $result = $this->track_image_event('impression', $post_id, $image_url, $image_id, $attachment_id);
        
        if ($result) {
            wp_send_json_success('Image impression tracked');
        } else {
            wp_send_json_error('Failed to track image impression');
        }
    }

    /**
     * Update post analytics when an event is tracked.
     *
     * @since    1.2.0
     * @param    string    $event_type    Event type
     * @param    int       $post_id       Post ID
     * @param    string    $date          Optional date (defaults to today)
     */
    public function update_post_analytics($event_type, $post_id, $date = null) {
        if (!$post_id) {
            return;
        }
        
        global $wpdb;
        $target_date = $date ?: current_time('Y-m-d');
        if (!$this->is_valid_table_name($this->post_table_name)) {
            return;
        }
        
        // Map event types to column names
        $column_map = array(
            'pin_button_click' => 'pin_button_clicks',
            'image_banner_click' => 'banner_clicks',
            'follow_button_click' => 'follow_clicks',
            'block_editor_button_click' => 'follow_clicks',
            'call_out_banner_click' => 'call_out_banner_clicks'
        );
        
        if (!isset($column_map[$event_type])) {
            return;
        }
        
        $column = $column_map[$event_type];
        
        // Try to increment existing record
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Real-time analytics tracking, column identifier whitelisted
        $result = $wpdb->query($wpdb->prepare(
            "INSERT INTO %i (post_id, date, %i)
             VALUES (%d, %s, 1)
             ON DUPLICATE KEY UPDATE %i = %i + 1",
            $this->post_table_name,
            $column,
            $post_id,
            $target_date,
            $column,
            $column
        ));
    }

    /**
     * Get post analytics data.
     *
     * @since    1.2.0
     * @param    string    $start_date    Start date
     * @param    string    $end_date      End date
     * @return   array                    Post analytics data
     */
    public function get_post_analytics_data($start_date, $end_date) {
        global $wpdb;
        
        $end_date_inclusive = $this->make_end_date_inclusive($end_date);
        $posts_table = $wpdb->prefix . 'posts';
        
        // Get post analytics data with conversion rates
        // Use LEFT JOIN to include all posts, even those without analytics data
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics aggregation query
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                p.ID as post_id,
                p.post_title,
                p.post_status,
                COALESCE(SUM(pa.impressions), 0) as total_impressions,
                COALESCE(SUM(pa.pin_button_clicks), 0) as total_pin_clicks,
                COALESCE(SUM(pa.banner_clicks), 0) as total_banner_clicks,
                COALESCE(SUM(pa.follow_clicks), 0) as total_follow_clicks,
                COALESCE(SUM(pa.call_out_banner_clicks), 0) as total_call_out_banner_clicks,
                COALESCE(block_clicks.total_block_clicks, 0) as total_block_editor_button_clicks,
                COALESCE(SUM(pa.pin_button_clicks + pa.banner_clicks + pa.follow_clicks + pa.call_out_banner_clicks) + COALESCE(block_clicks.total_block_clicks, 0), 0) as total_clicks,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN (COALESCE(SUM(pa.pin_button_clicks), 0) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as pin_conversion_rate,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN (COALESCE(SUM(pa.banner_clicks), 0) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as banner_conversion_rate,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN (COALESCE(SUM(pa.follow_clicks), 0) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as follow_conversion_rate,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN (COALESCE(SUM(pa.call_out_banner_clicks), 0) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as call_out_banner_conversion_rate,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN (COALESCE(block_clicks.total_block_clicks, 0) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as block_editor_button_conversion_rate,
                ROUND(
                    CASE 
                        WHEN COALESCE(SUM(pa.impressions), 0) > 0 
                        THEN ((COALESCE(SUM(pa.pin_button_clicks + pa.banner_clicks + pa.follow_clicks + pa.call_out_banner_clicks), 0) + COALESCE(block_clicks.total_block_clicks, 0)) * 100.0 / SUM(pa.impressions))
                        ELSE 0 
                    END, 2
                ) as overall_conversion_rate,
                MIN(pa.date) as first_date,
                MAX(pa.date) as last_date
            FROM %i p
            LEFT JOIN %i pa ON p.ID = pa.post_id 
                AND pa.date >= %s AND pa.date < %s
            LEFT JOIN (
                SELECT 
                    post_id,
                    COUNT(*) as total_block_clicks
                FROM %i
                WHERE event_type = 'block_editor_button_click'
                    AND created_at >= %s 
                    AND created_at < %s
                GROUP BY post_id
            ) block_clicks ON p.ID = block_clicks.post_id
            WHERE p.post_status = 'publish'
            AND p.post_type = 'post'
            GROUP BY p.ID, p.post_title, p.post_status, block_clicks.total_block_clicks
            ORDER BY total_impressions DESC, p.post_date DESC",
            $posts_table,
            $this->post_table_name,
            $start_date,
            $end_date_inclusive,
            $this->table_name,
            $start_date,
            $end_date_inclusive
        ));
        
        // Add featured images, URLs, and aggregate image analytics
        foreach ($results as $post) {
            $post->featured_image = $this->get_post_featured_image($post->post_id);
            $post->post_url = get_permalink($post->post_id);
            
            // Add aggregated image analytics
            $image_analytics = $this->get_post_image_analytics_summary($post->post_id, $start_date, $end_date);
            $post->total_images = $image_analytics['total_images'];
            $post->images_with_clicks = $image_analytics['images_with_clicks'];
            $post->total_image_clicks = $image_analytics['total_clicks'];
        }
        
        return $results;
    }

    /**
     * Get summary of image analytics for a post.
     *
     * @since    1.2.0
     * @param    int       $post_id       Post ID
     * @param    string    $start_date    Start date
     * @param    string    $end_date      End date
     * @return   array                    Summary data
     */
    private function get_post_image_analytics_summary($post_id, $start_date, $end_date) {
        global $wpdb;
        
        $end_date_inclusive = $this->make_end_date_inclusive($end_date);
        // Get image analytics summary
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics summary query
        $summary = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(DISTINCT image_id) as total_images,
                COUNT(DISTINCT CASE WHEN event_count > 0 THEN image_id END) as images_with_clicks,
                SUM(event_count) as total_clicks
            FROM %i
            WHERE post_id = %d
                AND (
                    (created_at >= %s AND created_at < %s)
                    OR 
                    (last_clicked >= %s AND last_clicked < %s)
                )",
            $this->image_table_name,
            $post_id,
            $start_date,
            $end_date_inclusive,
            $start_date,
            $end_date_inclusive
        ));
        
        return array(
            'total_images' => $summary ? intval($summary->total_images) : 0,
            'images_with_clicks' => $summary ? intval($summary->images_with_clicks) : 0,
            'total_clicks' => $summary ? intval($summary->total_clicks) : 0
        );
    }

    /**
     * AJAX handler for getting post analytics data.
     *
     * @since    1.2.0
     */
    public function get_post_analytics_data_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Get parameters
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : gmdate('Y-m-d', strtotime('-30 days'));
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : gmdate('Y-m-d');

        // Apply premium restrictions for date range
        if (function_exists('pinnerpress_filter_analytics_date_range')) {
            list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);
        }

        // Get analytics data
        $data = $this->get_post_analytics_data($start_date, $end_date);
        
        // Limit free users to top 25 posts by impressions
        if (!$this->is_premium() || !function_exists('pinnerpress_can_use_premium_features') || !pinnerpress_can_use_premium_features()) {
            // Sort by total impressions descending and limit to 25
            usort($data, function($a, $b) {
                $a_impressions = isset($a->total_impressions) ? (int) $a->total_impressions : 0;
                $b_impressions = isset($b->total_impressions) ? (int) $b->total_impressions : 0;
                return $b_impressions <=> $a_impressions;
            });
            $data = array_slice($data, 0, 25);
        }

        wp_send_json_success(array(
            'posts' => $data,
            'start_date' => $start_date,
            'end_date' => $end_date,
            'is_premium' => $this->is_premium(),
            'is_limited' => !$this->is_premium() || !function_exists('pinnerpress_can_use_premium_features') || !pinnerpress_can_use_premium_features()
        ));
    }

    /**
     * Get single post analytics details.
     *
     * @since    1.2.0
     * @param    int       $post_id       Post ID
     * @param    string    $start_date    Start date
     * @param    string    $end_date      End date
     * @return   array                    Detailed post analytics
     */
    public function get_single_post_analytics($post_id, $start_date, $end_date) {
        global $wpdb;
        
        // Get daily analytics data
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics reporting query
        $daily_data = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                date,
                impressions,
                pin_button_clicks,
                banner_clicks,
                follow_clicks,
                call_out_banner_clicks,
                (pin_button_clicks + banner_clicks + follow_clicks + call_out_banner_clicks) as total_clicks
            FROM %i
            WHERE post_id = %d AND date >= %s AND date <= %s
            ORDER BY date ASC",
            $this->post_table_name,
            $post_id,
            $start_date,
            $end_date
        ));
        
        // Get post info
        $post = get_post($post_id);
        
        // Calculate totals and averages
        $totals = array(
            'impressions' => 0,
            'pin_clicks' => 0,
            'banner_clicks' => 0,
            'follow_clicks' => 0,
            'call_out_banner_clicks' => 0,
            'total_clicks' => 0
        );
        
        foreach ($daily_data as $day) {
            $totals['impressions'] += $day->impressions;
            $totals['pin_clicks'] += $day->pin_button_clicks;
            $totals['banner_clicks'] += $day->banner_clicks;
            $totals['follow_clicks'] += $day->follow_clicks;
            $totals['call_out_banner_clicks'] += $day->call_out_banner_clicks;
            $totals['total_clicks'] += $day->total_clicks;
        }
        
        // RECALCULATE total_clicks to ensure accuracy (fix for broken math)
        $calculated_total = $totals['pin_clicks'] + $totals['banner_clicks'] + $totals['follow_clicks'] + $totals['call_out_banner_clicks'];
        if ($calculated_total !== $totals['total_clicks']) {
            // Post total clicks mismatch detected
            $totals['total_clicks'] = $calculated_total;
        }
        
        // Calculate conversion rates
        $conversion_rates = array(
            'pin' => $totals['impressions'] > 0 ? round(($totals['pin_clicks'] / $totals['impressions']) * 100, 2) : 0,
            'banner' => $totals['impressions'] > 0 ? round(($totals['banner_clicks'] / $totals['impressions']) * 100, 2) : 0,
            'follow' => $totals['impressions'] > 0 ? round(($totals['follow_clicks'] / $totals['impressions']) * 100, 2) : 0,
            'call_out_banner' => $totals['impressions'] > 0 ? round(($totals['call_out_banner_clicks'] / $totals['impressions']) * 100, 2) : 0,
            'overall' => $totals['impressions'] > 0 ? round(($totals['total_clicks'] / $totals['impressions']) * 100, 2) : 0
        );
        
        return array(
            'post' => array(
                'id' => $post->ID,
                'title' => $post->post_title,
                'url' => get_permalink($post->ID),
                'featured_image' => $this->get_post_featured_image($post->ID)
            ),
            'totals' => $totals,
            'conversion_rates' => $conversion_rates,
            'daily_data' => $daily_data
        );
    }

    /**
     * AJAX handler for getting single post analytics.
     *
     * @since    1.2.0
     */
    public function get_single_post_analytics_ajax() {
        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions');
        }

        // Check nonce for security
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'pinnerpress_analytics_nonce')) {
            wp_die('Security check failed');
        }

        // Get parameters
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : gmdate('Y-m-d', strtotime('-30 days'));
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : gmdate('Y-m-d');

        if (!$post_id) {
            wp_send_json_error('Invalid post ID');
        }

        // Apply premium restrictions for date range
        if (function_exists('pinnerpress_filter_analytics_date_range')) {
            list($start_date, $end_date) = pinnerpress_filter_analytics_date_range($start_date, $end_date);
        }

        // Get analytics data
        $data = $this->get_single_post_analytics($post_id, $start_date, $end_date);

        wp_send_json_success($data);
    }

    /**
     * Reset all analytics data by truncating tables.
     *
     * @since    1.3.0
     * @return   bool    True on success, false on failure
     */
    public function reset_analytics_data() {
        global $wpdb;

        // Check if user has proper permissions
        if (!current_user_can('manage_options')) {
            // Unauthorized attempt to reset analytics data
            return false;
        }

        // Starting analytics data reset
        
        // Truncate all analytics tables
        $tables_to_truncate = array(
            $this->table_name => 'main analytics',
            $this->post_table_name => 'post analytics',
            $this->image_table_name => 'image analytics'
        );
        
        $all_success = true;
        
        foreach ($tables_to_truncate as $table => $description) {
            if (!$this->is_valid_table_name($table)) {
                $all_success = false;
                continue;
            }
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery -- Identifier validated; TRUNCATE required for reset
            $result = $wpdb->query("TRUNCATE TABLE {$table}");
            
            if ($result === false) {
                // Failed to truncate table
                $all_success = false;
            } else {
                // Successfully truncated table
            }
        }

        // Remove the auto-population hook
        remove_action('init', array($this, 'ensure_tables_exist'));
        
        if ($all_success) {
            // All analytics data reset completed successfully
        } else {
            // Some tables failed to reset
        }
        
        return $all_success;
    }

    /**
     * Generate realistic test data for the past 30 days.
     *
     * @since    1.3.0
     * @param    array    $post_ids    Array of post IDs to generate data for
     * @return   int                   Number of events generated
     */
    public function generate_test_data($post_ids = array()) {
        global $wpdb;

        // Check if user has proper permissions
        if (!current_user_can('manage_options')) {
            // Unauthorized attempt to generate test data
            return 0;
        }

        // Get available posts if none provided
        if (empty($post_ids)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Fetching recent posts for test data seeding.
            $posts = $wpdb->get_results(
                "SELECT ID FROM {$wpdb->posts} 
                 WHERE post_type = 'post' 
                 AND post_status = 'publish' 
                 ORDER BY post_date DESC 
                 LIMIT 10"
            );
            $post_ids = array_map(function($post) { return $post->ID; }, $posts);
        }

        if (empty($post_ids)) {
            // No posts available for test data generation
            return 0;
        }

        // Starting test data generation

        $events_generated = 0;
        $start_date = strtotime('-30 days');
        $end_date = time();

        // Event types and their relative frequencies (per day max)
        $event_types = array(
            'pin_button_click' => 15,
            'image_banner_click' => 10,
            'follow_button_click' => 5,
            'impression' => 100
        );

        // Generate data for each day in the past 30 days
        for ($date = $start_date; $date <= $end_date; $date += 86400) {
            $day_date = gmdate('Y-m-d', $date);

            foreach ($post_ids as $post_id) {
                // Get real images from this post for more realistic test data
                $post_images = $this->extract_images_from_post_content(get_post_field('post_content', $post_id));
                
                // Generate daily impressions using track_impression which handles post analytics correctly
                $daily_impressions = wp_rand(20, 100);
                for ($i = 0; $i < $daily_impressions; $i++) {
                    $this->track_impression($post_id);
                }

                // Generate click events
                foreach ($event_types as $event_type => $max_daily) {
                    if ($event_type === 'impression') continue; // Already handled above

                    $daily_count = wp_rand(0, min($max_daily, intval($daily_impressions / 8))); // Realistic conversion rate

                    for ($i = 0; $i < $daily_count; $i++) {
                        $event_time = gmdate('Y-m-d H:i:s', $date + wp_rand(0, 86400));
                        
                        // Prepare event data
                        $event_data = array();
                        
                        // Use real images from post content for image-related events
                        if (in_array($event_type, array('pin_button_click', 'image_banner_click')) && !empty($post_images)) {
                            $random_image = $post_images[array_rand($post_images)];
                            $event_data = array(
                                'image_url' => $random_image['image_url'],
                                'image_id' => $random_image['image_id'],
                                'attachment_id' => $random_image['attachment_id']
                            );
                        }

                        // Insert main analytics event
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Test data generator writing to internal analytics table only.
                        $result = $wpdb->insert(
                            $this->table_name,
                            array(
                                'event_type' => $event_type,
                                'event_data' => !empty($event_data) ? wp_json_encode($event_data) : null,
                                'post_id' => $post_id,
                                'user_ip' => '192.168.1.' . wp_rand(1, 254),
                                'user_agent' => 'Test User Agent',
                                'created_at' => $event_time
                            ),
                            array('%s', '%s', '%d', '%s', '%s', '%s')
                        );

                        if ($result !== false) {
                            $events_generated++;

                            // CRITICAL FIX: Update post analytics for click events
                            $this->update_post_analytics($event_type, $post_id, $day_date);

                            // Track image-specific events if we have image data
                            if (!empty($event_data['image_url'])) {
                                $this->track_image_event(
                                    $event_type, 
                                    $post_id, 
                                    $event_data['image_url'], 
                                    $event_data['image_id'], 
                                    $event_data['attachment_id']
                                );
                            }
                        }
                    }
                }
            }
        }

        // Test data generation completed

        return $events_generated;
    }



}
} 
