<?php
/**
 * AuctionForge Site Statistics - AJAX Handlers
 * Handles all frontend tracking AJAX requests and admin data queries.
 */

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

class AF_Stats_Ajax {

    public static function init() {
        // Frontend tracking (both logged-in and non-logged-in)
        add_action('wp_ajax_af_stats_track', array(__CLASS__, 'handle_track'));
        add_action('wp_ajax_nopriv_af_stats_track', array(__CLASS__, 'handle_track'));

        // Admin data endpoints
        add_action('wp_ajax_af_stats_dashboard', array(__CLASS__, 'handle_dashboard'));
        add_action('wp_ajax_af_stats_top_pages', array(__CLASS__, 'handle_top_pages'));
        add_action('wp_ajax_af_stats_referrers', array(__CLASS__, 'handle_referrers'));
        add_action('wp_ajax_af_stats_countries', array(__CLASS__, 'handle_countries'));
        add_action('wp_ajax_af_stats_browsers', array(__CLASS__, 'handle_browsers'));
        add_action('wp_ajax_af_stats_searches', array(__CLASS__, 'handle_searches'));
        add_action('wp_ajax_af_stats_errors', array(__CLASS__, 'handle_errors'));
        add_action('wp_ajax_af_stats_realtime', array(__CLASS__, 'handle_realtime'));
        add_action('wp_ajax_af_stats_export', array(__CLASS__, 'handle_export'));
        add_action('wp_ajax_af_stats_save_settings', array(__CLASS__, 'handle_save_settings'));

        // Lot analytics admin endpoints
        add_action('wp_ajax_af_lot_analytics_data', array(__CLASS__, 'handle_lot_analytics_data'));
        add_action('wp_ajax_af_lot_analytics_backfill', array(__CLASS__, 'handle_lot_analytics_backfill'));

        add_action('wp_ajax_af_lot_analytics_export', array(__CLASS__, 'handle_lot_analytics_export'));

        // Lot analytics frontend endpoints
        add_action('wp_ajax_lot_analytics_record_favorite', array(__CLASS__, 'handle_lot_analytics_favorite'));
        add_action('wp_ajax_nopriv_lot_analytics_record_favorite', array(__CLASS__, 'handle_lot_analytics_favorite'));
        add_action('wp_ajax_lot_analytics_record_share', array(__CLASS__, 'handle_lot_analytics_share'));
        add_action('wp_ajax_nopriv_lot_analytics_record_share', array(__CLASS__, 'handle_lot_analytics_share'));
        add_action('wp_ajax_lot_analytics_record_click_source', array(__CLASS__, 'handle_lot_analytics_click_source'));
        add_action('wp_ajax_nopriv_lot_analytics_record_click_source', array(__CLASS__, 'handle_lot_analytics_click_source'));
    }

    /**
     * Handle frontend tracking request
     */
    public static function handle_track() {
        // Verify nonce
        if (!isset($_POST['_nonce']) || !wp_verify_nonce($_POST['_nonce'], 'af_stats_nonce')) {
            wp_send_json_error('Invalid nonce');
            return;
        }

        $ua = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';

        // Skip bots
        if (AF_Stats_Tracker::is_bot($ua)) {
            wp_send_json_success(array('tracked' => false, 'reason' => 'bot'));
            return;
        }

        $event = sanitize_text_field(isset($_POST['event']) ? $_POST['event'] : 'pageview');
        $visitor_hash = sanitize_text_field(isset($_POST['vid']) ? $_POST['vid'] : '');
        $session_hash = sanitize_text_field(isset($_POST['sid']) ? $_POST['sid'] : '');

        if (empty($visitor_hash) || empty($session_hash)) {
            wp_send_json_error('Missing visitor/session ID');
            return;
        }

        $ip = AF_Stats_Tracker::get_client_ip();
        $ip = AF_Stats_Tracker::maybe_anonymize_ip($ip);
        $ua_data = AF_Stats_Tracker::parse_user_agent($ua);
        $geo = AF_Stats_Tracker::geoip_lookup($ip);

        $screen_w = intval(isset($_POST['sw']) ? $_POST['sw'] : 0);
        $screen_h = intval(isset($_POST['sh']) ? $_POST['sh'] : 0);

        // Identity stitching: if the request is authenticated, record the
        // (visitor_hash, email) pair so anonymous browsing can be resolved
        // to a person downstream. Server-side check only — the client never
        // gets to assert who they are.
        $logged_in_email = '';
        if (is_user_logged_in()) {
            $current_user = wp_get_current_user();
            if ($current_user && !empty($current_user->user_email)) {
                $logged_in_email = strtolower(trim($current_user->user_email));
                AF_Stats_Visitor_Email_Map::record($visitor_hash, $logged_in_email, $ip);
            }
        }

        // Get or create visitor
        $visitor_id = AF_Stats_Tracker::get_or_create_visitor($visitor_hash, $ua_data, $geo, $ip, $screen_w, $screen_h);

        // Common data
        $data = array(
            'url' => isset($_POST['url']) ? esc_url_raw($_POST['url']) : '',
            'title' => isset($_POST['title']) ? sanitize_text_field($_POST['title']) : '',
            'referrer' => isset($_POST['ref']) ? esc_url_raw($_POST['ref']) : '',
            'utm_source' => isset($_POST['utm_source']) ? sanitize_text_field($_POST['utm_source']) : '',
            'utm_medium' => isset($_POST['utm_medium']) ? sanitize_text_field($_POST['utm_medium']) : '',
            'utm_campaign' => isset($_POST['utm_campaign']) ? sanitize_text_field($_POST['utm_campaign']) : '',
            'utm_term' => isset($_POST['utm_term']) ? sanitize_text_field($_POST['utm_term']) : '',
            'utm_content' => isset($_POST['utm_content']) ? sanitize_text_field($_POST['utm_content']) : '',
            'load_time' => isset($_POST['lt']) ? intval($_POST['lt']) : 0,
            'ip' => $ip,
            'country' => $geo['country'],
            'device_type' => $ua_data['device_type'],
            'browser' => $ua_data['browser'],
        );

        // Get or create session
        $session_id = AF_Stats_Tracker::get_or_create_session($session_hash, $visitor_id, $visitor_hash, $data);

        $response = array('tracked' => true);

        switch ($event) {
            case 'pageview':
                $pv_id = AF_Stats_Tracker::record_pageview($session_id, $visitor_id, $visitor_hash, $session_hash, $data);
                $response['pv_id'] = $pv_id;

                // Update time on previous page
                $prev_pv_id = intval(isset($_POST['prev_pv']) ? $_POST['prev_pv'] : 0);
                $time_on_prev = intval(isset($_POST['top']) ? $_POST['top'] : 0);
                if ($prev_pv_id > 0 && $time_on_prev > 0) {
                    AF_Stats_Tracker::update_time_on_page($prev_pv_id, $time_on_prev);
                }

                // Notify listeners (Phase 2: tally lot views into user_interests).
                do_action('af_stats_pageview_recorded', $data['url'], $logged_in_email, $visitor_hash);
                break;

            case 'search':
                AF_Stats_Tracker::record_search($session_id, $visitor_hash, array(
                    'query' => isset($_POST['query']) ? $_POST['query'] : '',
                    'type' => isset($_POST['stype']) ? $_POST['stype'] : 'wordpress',
                    'results' => isset($_POST['results']) ? $_POST['results'] : -1,
                    'url' => $data['url'],
                ));
                break;

            case 'outbound':
                AF_Stats_Tracker::record_outbound($session_id, $visitor_hash, array(
                    'from_url' => $data['url'],
                    'to_url' => isset($_POST['to_url']) ? esc_url_raw($_POST['to_url']) : '',
                    'link_text' => isset($_POST['link_text']) ? $_POST['link_text'] : '',
                ));
                break;

            case 'heartbeat':
                // Just update session ended_at + duration
                global $wpdb;
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$wpdb->prefix}af_stats_sessions SET ended_at = NOW(), duration = TIMESTAMPDIFF(SECOND, started_at, NOW()) WHERE id = %d",
                    $session_id
                ));
                // Update time on current page
                $curr_pv_id = intval(isset($_POST['pv_id']) ? $_POST['pv_id'] : 0);
                $time_on_page = intval(isset($_POST['top']) ? $_POST['top'] : 0);
                if ($curr_pv_id > 0 && $time_on_page > 0) {
                    AF_Stats_Tracker::update_time_on_page($curr_pv_id, $time_on_page);
                }
                break;

            case 'unload':
                // Final update: time on page + session duration
                global $wpdb;
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$wpdb->prefix}af_stats_sessions SET ended_at = NOW(), duration = TIMESTAMPDIFF(SECOND, started_at, NOW()) WHERE id = %d",
                    $session_id
                ));
                $curr_pv_id = intval(isset($_POST['pv_id']) ? $_POST['pv_id'] : 0);
                $time_on_page = intval(isset($_POST['top']) ? $_POST['top'] : 0);
                if ($curr_pv_id > 0 && $time_on_page > 0) {
                    AF_Stats_Tracker::update_time_on_page($curr_pv_id, $time_on_page);
                }
                break;
        }

        wp_send_json_success($response);
    }

    // ===================== ADMIN DATA ENDPOINTS =====================

    private static function admin_check() {
        check_ajax_referer('af_stats_admin', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
            return false;
        }
        return true;
    }

    private static function get_date_range() {
        $from = isset($_POST['from']) ? sanitize_text_field($_POST['from']) : date('Y-m-d', strtotime('-30 days'));
        $to = isset($_POST['to']) ? sanitize_text_field($_POST['to']) : date('Y-m-d');
        $from .= ' 00:00:00';
        $to .= ' 23:59:59';
        return array($from, $to);
    }

    private static function get_comparison_range($from, $to) {
        $from_ts = strtotime($from);
        $to_ts = strtotime($to);
        $diff = $to_ts - $from_ts;
        $prev_to = date('Y-m-d H:i:s', $from_ts - 1);
        $prev_from = date('Y-m-d H:i:s', $from_ts - $diff - 1);
        return array($prev_from, $prev_to);
    }

    /**
     * Dashboard overview data
     */
    public static function handle_dashboard() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        list($prev_from, $prev_to) = self::get_comparison_range($from, $to);

        // Two expressions per table, not one spanning both periods. A single
        // expression scoped from $prev_from would make every current-period
        // query materialise roughly double the rows it needs once the range
        // reaches the archive. Comparison queries must still be scoped from
        // $prev_from or they would silently drop archived rows and report a
        // false trend. af_stats_visitors is never archived — it is a dimension
        // table the dashboard joins for visitors of any age.
        $pv_table = AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $from, $to);
        $sess_table = AF_Stats_Archive::table_expr('af_stats_sessions', 'sess', $from, $to);
        $pv_prev = AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $prev_from, $prev_to);
        $sess_prev = AF_Stats_Archive::table_expr('af_stats_sessions', 'sess', $prev_from, $prev_to);
        $vis_table = $wpdb->prefix . 'af_stats_visitors';

        // Current period stats
        $current = array(
            'pageviews' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $pv_table WHERE created_at BETWEEN %s AND %s", $from, $to
            ))),
            'sessions' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $sess_table WHERE started_at BETWEEN %s AND %s", $from, $to
            ))),
            'visitors' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT visitor_hash) FROM $pv_table WHERE created_at BETWEEN %s AND %s", $from, $to
            ))),
            'bounce_rate' => 0,
            'avg_duration' => 0,
            'avg_pages_per_session' => 0,
        );

        $bounce_data = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(*) as total, SUM(is_bounce) as bounces, AVG(duration) as avg_dur, AVG(pageview_count) as avg_pv
             FROM $sess_table WHERE started_at BETWEEN %s AND %s", $from, $to
        ));
        if ($bounce_data && $bounce_data->total > 0) {
            $current['bounce_rate'] = round(($bounce_data->bounces / $bounce_data->total) * 100, 1);
            $current['avg_duration'] = round($bounce_data->avg_dur);
            $current['avg_pages_per_session'] = round($bounce_data->avg_pv, 1);
        }

        // New visitors = visitors active in the period whose authoritative
        // first_seen (af_stats_visitors) is at/after the period start. Joining
        // pageviews keeps "new" a subset of active visitors, so it can never
        // exceed the Visitors KPI; returning = visitors − new (computed client-side).
        $current['new_visitors'] = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT pv.visitor_hash) FROM $pv_table
             INNER JOIN $vis_table v ON v.visitor_hash = pv.visitor_hash
             WHERE pv.created_at BETWEEN %s AND %s AND v.first_seen >= %s", $from, $to, $from
        )));

        // Previous period stats (for comparison)
        $previous = array(
            'pageviews' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $pv_prev WHERE created_at BETWEEN %s AND %s", $prev_from, $prev_to
            ))),
            'sessions' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $sess_prev WHERE started_at BETWEEN %s AND %s", $prev_from, $prev_to
            ))),
            'visitors' => intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT visitor_hash) FROM $pv_prev WHERE created_at BETWEEN %s AND %s", $prev_from, $prev_to
            ))),
        );
        $prev_bounce = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(*) as total, SUM(is_bounce) as bounces FROM $sess_prev WHERE started_at BETWEEN %s AND %s", $prev_from, $prev_to
        ));
        $previous['bounce_rate'] = ($prev_bounce && $prev_bounce->total > 0)
            ? round(($prev_bounce->bounces / $prev_bounce->total) * 100, 1) : 0;
        $previous['new_visitors'] = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT pv.visitor_hash) FROM $pv_prev
             INNER JOIN $vis_table v ON v.visitor_hash = pv.visitor_hash
             WHERE pv.created_at BETWEEN %s AND %s AND v.first_seen >= %s", $prev_from, $prev_to, $prev_from
        )));

        // Daily chart data
        $daily_pv = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) as date, COUNT(*) as count FROM $pv_table
             WHERE created_at BETWEEN %s AND %s GROUP BY DATE(created_at) ORDER BY date", $from, $to
        ), ARRAY_A);

        $daily_visitors = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) as date, COUNT(DISTINCT visitor_hash) as count FROM $pv_table
             WHERE created_at BETWEEN %s AND %s GROUP BY DATE(created_at) ORDER BY date", $from, $to
        ), ARRAY_A);

        $daily_sessions = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(started_at) as date, COUNT(*) as count FROM $sess_table
             WHERE started_at BETWEEN %s AND %s GROUP BY DATE(started_at) ORDER BY date", $from, $to
        ), ARRAY_A);

        // Hourly distribution by day of week (for current period). DAYOFWEEK():
        // 1 = Sunday … 7 = Saturday. The dashboard consolidates each weekday
        // across every date in the range and lets the user tab between days
        // (plus an "All days" view that sums them).
        $hourly = $wpdb->get_results($wpdb->prepare(
            "SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as count FROM $pv_table
             WHERE created_at BETWEEN %s AND %s
             GROUP BY DAYOFWEEK(created_at), HOUR(created_at) ORDER BY dow, hour", $from, $to
        ), ARRAY_A);

        // Traffic channels
        $channels = $wpdb->get_results($wpdb->prepare(
            "SELECT traffic_channel, COUNT(*) as count FROM $sess_table
             WHERE started_at BETWEEN %s AND %s GROUP BY traffic_channel ORDER BY count DESC", $from, $to
        ), ARRAY_A);

        // Device types
        $devices = $wpdb->get_results($wpdb->prepare(
            "SELECT device_type, COUNT(DISTINCT visitor_hash) as count FROM $pv_table
             WHERE created_at BETWEEN %s AND %s GROUP BY device_type ORDER BY count DESC", $from, $to
        ), ARRAY_A);

        // Top lot pages (individual items) — excludes home, catalogue
        // listings/pagination, profile and other low-signal pages.
        $top_pages = $wpdb->get_results($wpdb->prepare(
            "SELECT url_path, page_title, COUNT(*) as views, COUNT(DISTINCT visitor_hash) as visitors
             FROM $pv_table WHERE created_at BETWEEN %s AND %s
               AND url_path LIKE '%%/lot-%%'
             GROUP BY url_path ORDER BY views DESC LIMIT 5", $from, $to
        ), ARRAY_A);

        // Top 5 referrers
        $top_referrers = $wpdb->get_results($wpdb->prepare(
            "SELECT referrer_domain, COUNT(*) as count FROM $sess_table
             WHERE started_at BETWEEN %s AND %s AND referrer_domain != '' AND traffic_channel != 'internal'
             GROUP BY referrer_domain ORDER BY count DESC LIMIT 5", $from, $to
        ), ARRAY_A);

        // Top 5 countries
        $top_countries = $wpdb->get_results($wpdb->prepare(
            "SELECT country, COUNT(DISTINCT visitor_hash) as visitors FROM $pv_table
             WHERE created_at BETWEEN %s AND %s AND country != ''
             GROUP BY country ORDER BY visitors DESC LIMIT 10", $from, $to
        ), ARRAY_A);
        // Ship the readable name alongside the raw code: "GB" means little to
        // an auctioneer reading their own dashboard.
        $top_countries = AF_Country_Names::decorate($top_countries);

        // Auction dates within the range — rendered as markers on the
        // "Traffic Over Time" chart so spikes can be read against sale events.
        // Stored in term meta 'auction_date' (may carry a time; date part used).
        $auction_events = array();
        $from_date = substr($from, 0, 10);
        $to_date = substr($to, 0, 10);
        $auction_meta = $wpdb->get_results(
            "SELECT t.name AS name, tm.meta_value AS adate
             FROM {$wpdb->termmeta} tm
             INNER JOIN {$wpdb->terms} t ON t.term_id = tm.term_id
             WHERE tm.meta_key = 'auction_date' AND tm.meta_value != ''"
        );
        foreach ((array) $auction_meta as $am) {
            $d = substr($am->adate, 0, 10);
            if ($d < $from_date || $d > $to_date) continue;
            $auction_events[] = array('date' => $d, 'name' => $am->name);
        }

        // Engagement funnel for the period. The first three stages are strictly
        // nested and measured from site pageviews; the Bidders stage comes from
        // synced BidSpirit bid events (some bidders may use the app without a
        // tracked site visit, so treat its rate as indicative).
        $lot_viewers = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT visitor_hash) FROM $pv_table
             WHERE created_at BETWEEN %s AND %s AND url_path LIKE '%%/lot-%%'", $from, $to
        )));
        $engaged_viewers = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM (
                SELECT visitor_hash FROM $pv_table
                WHERE created_at BETWEEN %s AND %s AND url_path LIKE '%%/lot-%%'
                GROUP BY visitor_hash HAVING COUNT(DISTINCT url_path) >= 5
             ) t", $from, $to
        )));
        // Bidders come from bid events captured off Bidspirit webhooks. If that
        // table has never received anything, the stage is UNMEASURED — report
        // that honestly rather than rendering a "0", which next to the engaged
        // -viewer stage reads as "nobody who browsed ever bid" and is usually
        // false (bidding happens on the Bidspirit platform, not on this site).
        $bidders       = 0;
        $bids_measured = false;   // capture has produced data at some point
        $has_alerts    = false;   // some of it is lot-alert derived, not confirmed bids
        $bid_table = $wpdb->prefix . 'af_bid_events';
        if ($wpdb->get_var("SHOW TABLES LIKE '$bid_table'") === $bid_table) {
            // "Measured" must be judged over the SAME window as the count,
            // otherwise a single historical row makes every later empty period
            // render a hard "0" — the exact false zero this replaced.
            $bidders = intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT user_email) FROM $bid_table WHERE occurred_at BETWEEN %s AND %s", $from, $to
            )));
            $bids_measured = $bidders > 0;

            $has_source = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM information_schema.columns
                  WHERE table_schema = %s AND table_name = %s AND column_name = 'source'",
                DB_NAME, $bid_table
            ));
            if ($has_source && $bids_measured) {
                $has_alerts = (bool) $wpdb->get_var($wpdb->prepare(
                    "SELECT EXISTS(SELECT 1 FROM $bid_table
                      WHERE occurred_at BETWEEN %s AND %s AND source = 'alert')", $from, $to
                ));
            }
        }

        // Label only what the data proves. Lot-alert notifications correlate
        // strongly with bidding but are not proof of a bid, so say so rather
        // than presenting watchers as confirmed bidders.
        $bid_label = $has_alerts ? 'Bidders & lot-watchers' : 'Bidders';
        $bid_note  = $has_alerts
            ? 'Unique people Bidspirit notified about a bid or a lot they were watching. Lot-watchers are included, so treat this as an upper bound on bidders.'
            : 'Unique people confirmed to have bid, from Bidspirit outbid notifications.';
        if (!$bids_measured) {
            $bid_note = 'Not tracked for this period — no bidder notifications were received from Bidspirit. Bidding happens on the Bidspirit platform, so this is not a count of zero bids.';
        }

        $funnel = array(
            array('label' => 'Visitors',       'count' => intval($current['visitors']), 'measured' => true, 'nested' => true),
            array('label' => 'Viewed a lot',   'count' => $lot_viewers,                 'measured' => true, 'nested' => true),
            array('label' => 'Viewed 5+ lots', 'count' => $engaged_viewers,             'measured' => true, 'nested' => true),
            array(
                'label'    => $bid_label,
                'count'    => $bidders,
                'measured' => $bids_measured,
                // NOT a subset of Visitors: counted per person (email) rather
                // than per browser, and includes app/live-room bidders who never
                // triggered a tracked pageview. Showing it as a "% of visitors"
                // would be arithmetic on two different populations.
                'nested'   => false,
                'note'     => $bid_note,
            ),
        );

        wp_send_json_success(array(
            'current' => $current,
            'previous' => $previous,
            'charts' => array(
                'daily_pageviews' => $daily_pv,
                'daily_visitors' => $daily_visitors,
                'daily_sessions' => $daily_sessions,
                'hourly' => $hourly,
                'channels' => $channels,
                'devices' => $devices,
            ),
            'top_pages' => $top_pages,
            'top_referrers' => $top_referrers,
            'top_countries' => $top_countries,
            'auction_events' => $auction_events,
            'funnel' => $funnel,
            // Lets the UI explain a slow refresh instead of looking broken:
            // this range reached past the live window into archived history.
            'reads_archive' => AF_Stats_Archive::range_spans_archive($prev_from),
        ));
    }

    /**
     * Top pages detailed view
     */
    public static function handle_top_pages() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $pv_table = AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $from, $to);
        $per_page = intval(isset($_POST['per_page']) ? $_POST['per_page'] : 50);
        $page = intval(isset($_POST['page']) ? $_POST['page'] : 1);
        $offset = ($page - 1) * $per_page;
        $search = isset($_POST['search']) ? sanitize_text_field($_POST['search']) : '';

        $where_extra = '';
        $params = array($from, $to);
        if (!empty($search)) {
            $where_extra = " AND (url_path LIKE %s OR page_title LIKE %s)";
            $like = '%' . $wpdb->esc_like($search) . '%';
            $params[] = $like;
            $params[] = $like;
        }

        $total = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT url_path) FROM $pv_table WHERE created_at BETWEEN %s AND %s $where_extra", $params
        ));

        $params[] = $per_page;
        $params[] = $offset;
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT url_path, MAX(page_title) as page_title,
                    COUNT(*) as views, COUNT(DISTINCT visitor_hash) as visitors,
                    ROUND(AVG(time_on_page)) as avg_time,
                    SUM(is_entry) as entries, SUM(is_exit) as exits
             FROM $pv_table WHERE created_at BETWEEN %s AND %s $where_extra
             GROUP BY url_path ORDER BY views DESC LIMIT %d OFFSET %d", $params
        ), ARRAY_A);

        wp_send_json_success(array(
            'results' => $results,
            'total' => intval($total),
            'page' => $page,
            'pages' => ceil(intval($total) / max($per_page, 1)),
        ));
    }

    /**
     * Referrers detailed view
     */
    public static function handle_referrers() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $sess_table = AF_Stats_Archive::table_expr('af_stats_sessions', 'sess', $from, $to);
        $per_page = intval(isset($_POST['per_page']) ? $_POST['per_page'] : 50);
        $page = intval(isset($_POST['page']) ? $_POST['page'] : 1);
        $offset = ($page - 1) * $per_page;

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT referrer_domain, traffic_channel,
                    COUNT(*) as sessions, SUM(pageview_count) as pageviews,
                    ROUND(AVG(duration)) as avg_duration,
                    ROUND(SUM(is_bounce) / COUNT(*) * 100, 1) as bounce_rate
             FROM $sess_table WHERE started_at BETWEEN %s AND %s AND referrer_domain != '' AND traffic_channel != 'internal'
             GROUP BY referrer_domain ORDER BY sessions DESC LIMIT %d OFFSET %d",
            $from, $to, $per_page, $offset
        ), ARRAY_A);

        $total = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT referrer_domain) FROM $sess_table
             WHERE started_at BETWEEN %s AND %s AND referrer_domain != '' AND traffic_channel != 'internal'",
            $from, $to
        ));

        wp_send_json_success(array(
            'results' => $results,
            'total' => intval($total),
            'page' => $page,
            'pages' => ceil(intval($total) / max($per_page, 1)),
        ));
    }

    /**
     * Countries detailed view
     */
    public static function handle_countries() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $pv_table = AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $from, $to);
        $sess_table = AF_Stats_Archive::table_expr('af_stats_sessions', 'sess', $from, $to);

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT country, COUNT(DISTINCT visitor_hash) as visitors, COUNT(*) as pageviews
             FROM $pv_table WHERE created_at BETWEEN %s AND %s AND country != ''
             GROUP BY country ORDER BY visitors DESC LIMIT 100",
            $from, $to
        ), ARRAY_A);
        $results = AF_Country_Names::decorate($results);

        wp_send_json_success(array('results' => $results));
    }

    /**
     * Browsers / devices detailed view
     */
    public static function handle_browsers() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $vis_table = $wpdb->prefix . 'af_stats_visitors';
        $pv_table = AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $from, $to);

        // Get visitor hashes active in this period
        $browsers = $wpdb->get_results($wpdb->prepare(
            "SELECT v.browser, v.device_type, v.os, COUNT(DISTINCT v.visitor_hash) as visitors
             FROM $vis_table v
             INNER JOIN $pv_table ON v.visitor_hash = pv.visitor_hash
             WHERE pv.created_at BETWEEN %s AND %s
             GROUP BY v.browser, v.device_type, v.os
             ORDER BY visitors DESC LIMIT 50",
            $from, $to
        ), ARRAY_A);

        // Screen resolutions
        $screens = $wpdb->get_results($wpdb->prepare(
            "SELECT CONCAT(v.screen_width, 'x', v.screen_height) as resolution, COUNT(DISTINCT v.visitor_hash) as visitors
             FROM $vis_table v
             INNER JOIN $pv_table ON v.visitor_hash = pv.visitor_hash
             WHERE pv.created_at BETWEEN %s AND %s AND v.screen_width > 0
             GROUP BY resolution ORDER BY visitors DESC LIMIT 20",
            $from, $to
        ), ARRAY_A);

        wp_send_json_success(array(
            'browsers' => $browsers,
            'screens' => $screens,
        ));
    }

    /**
     * Searches view
     */
    public static function handle_searches() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $table = AF_Stats_Archive::table_expr('af_stats_searches', 'sr', $from, $to);

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT search_query, search_type, COUNT(*) as count, MAX(created_at) as last_searched
             FROM $table WHERE created_at BETWEEN %s AND %s
             GROUP BY search_query, search_type ORDER BY count DESC LIMIT 100",
            $from, $to
        ), ARRAY_A);

        wp_send_json_success(array('results' => $results));
    }

    /**
     * 404 Errors view
     */
    public static function handle_errors() {
        if (!self::admin_check()) return;
        global $wpdb;

        list($from, $to) = self::get_date_range();
        $table = $wpdb->prefix . 'af_stats_errors';

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT url_path, referrer_url, hit_count, first_seen, last_seen
             FROM $table WHERE last_seen BETWEEN %s AND %s
             ORDER BY hit_count DESC LIMIT 100",
            $from, $to
        ), ARRAY_A);

        wp_send_json_success(array('results' => $results));
    }

    /**
     * Real-time visitors (last 5 minutes)
     */
    public static function handle_realtime() {
        if (!self::admin_check()) return;
        global $wpdb;

        $pv_table = $wpdb->prefix . 'af_stats_pageviews';
        $vis_table = $wpdb->prefix . 'af_stats_visitors';
        $five_min_ago = date('Y-m-d H:i:s', strtotime('-5 minutes'));

        $active = $wpdb->get_results($wpdb->prepare(
            "SELECT pv.url_path, pv.page_title, pv.created_at,
                    v.country, v.browser, v.device_type, v.ip_address
             FROM $pv_table pv
             LEFT JOIN $vis_table v ON pv.visitor_hash = v.visitor_hash
             WHERE pv.created_at >= %s
             ORDER BY pv.created_at DESC LIMIT 50",
            $five_min_ago
        ), ARRAY_A);
        $active = AF_Country_Names::decorate($active);

        $active_count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT visitor_hash) FROM $pv_table WHERE created_at >= %s",
            $five_min_ago
        ));

        wp_send_json_success(array(
            'active_visitors' => intval($active_count),
            'recent_pageviews' => $active,
        ));
    }

    /**
     * Export CSV
     */
    public static function handle_export() {
        check_ajax_referer('af_stats_admin', 'nonce');
        if (!current_user_can('manage_options')) { wp_die('Unauthorized'); }

        global $wpdb;
        $type = sanitize_text_field(isset($_GET['type']) ? $_GET['type'] : 'pageviews');
        $from = sanitize_text_field(isset($_GET['from']) ? $_GET['from'] : date('Y-m-d', strtotime('-30 days')));
        $to = sanitize_text_field(isset($_GET['to']) ? $_GET['to'] : date('Y-m-d'));
        $from .= ' 00:00:00';
        $to .= ' 23:59:59';

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="af-stats-' . $type . '-' . date('Y-m-d') . '.csv"');
        $output = fopen('php://output', 'w');

        switch ($type) {
            case 'pageviews':
                fputcsv($output, array('URL', 'Title', 'Views', 'Unique Visitors', 'Avg Time (s)'));
                $results = $wpdb->get_results($wpdb->prepare(
                    "SELECT url_path, MAX(page_title) as title, COUNT(*) as views,
                            COUNT(DISTINCT visitor_hash) as visitors, ROUND(AVG(time_on_page)) as avg_time
                     FROM " . AF_Stats_Archive::table_expr('af_stats_pageviews', 'pv', $from, $to) . "
                     WHERE created_at BETWEEN %s AND %s
                     GROUP BY url_path ORDER BY views DESC", $from, $to
                ), ARRAY_A);
                foreach ($results as $r) {
                    fputcsv($output, array($r['url_path'], $r['title'], $r['views'], $r['visitors'], $r['avg_time']));
                }
                break;

            case 'referrers':
                fputcsv($output, array('Referrer', 'Channel', 'Sessions', 'Pageviews', 'Avg Duration', 'Bounce Rate'));
                $results = $wpdb->get_results($wpdb->prepare(
                    "SELECT referrer_domain, traffic_channel, COUNT(*) as sessions, SUM(pageview_count) as pvs,
                            ROUND(AVG(duration)) as dur, ROUND(SUM(is_bounce)/COUNT(*)*100,1) as bounce
                     FROM " . AF_Stats_Archive::table_expr('af_stats_sessions', 'sess', $from, $to) . "
                     WHERE started_at BETWEEN %s AND %s AND referrer_domain != ''
                     GROUP BY referrer_domain ORDER BY sessions DESC", $from, $to
                ), ARRAY_A);
                foreach ($results as $r) {
                    fputcsv($output, array($r['referrer_domain'], $r['traffic_channel'], $r['sessions'], $r['pvs'], $r['dur'], $r['bounce']));
                }
                break;
        }

        fclose($output);
        exit;
    }

    /**
     * Save stats settings
     */
    public static function handle_save_settings() {
        if (!self::admin_check()) return;

        // Recipient(s): a single address or several separated by commas/etc.
        // Normalised to a deduped, validated, comma-separated string.
        $raw_to = isset($_POST['weekly_email_to']) ? wp_unslash($_POST['weekly_email_to']) : '';
        $recipients = class_exists('AF_Stats_Report') ? AF_Stats_Report::parse_recipients($raw_to) : array();
        $weekly_email_to = !empty($recipients) ? implode(', ', $recipients) : get_option('admin_email');

        // Checkboxes are compared to 'on', never merely isset(). The form JS
        // always posts these keys — '' when unticked (dashboard.php ~:1098) — so
        // an isset() test is true either way and the setting can never be turned
        // off. That bug predates this release and left "Collect statistics",
        // "Anonymize IP" and "Track logged-in users" permanently locked on;
        // saving anything on this form silently re-enabled them.
        $settings = array(
            'af_stats_enabled' => isset($_POST['enabled']) && $_POST['enabled'] === 'on' ? 'on' : 'off',
            'af_stats_exclude_ips' => isset($_POST['exclude_ips']) ? sanitize_textarea_field($_POST['exclude_ips']) : '',
            'af_stats_exclude_roles' => isset($_POST['exclude_roles']) ? sanitize_text_field($_POST['exclude_roles']) : '',
            'af_stats_anonymize_ip' => isset($_POST['anonymize_ip']) && $_POST['anonymize_ip'] === 'on' ? 'on' : 'off',
            'af_stats_retention_days' => intval(isset($_POST['retention_days']) ? $_POST['retention_days'] : 0),
            'af_stats_archive_enabled' => isset($_POST['archive_enabled']) && $_POST['archive_enabled'] === 'on' ? 'on' : 'off',
            // Floor of 7 mirrors AF_Stats_Archive::MIN_DAYS — a smaller window
            // would start archiving data the default dashboard view still needs.
            'af_stats_archive_days' => max(7, intval(isset($_POST['archive_days']) ? $_POST['archive_days'] : 90)),
            'af_stats_track_logged_in' => isset($_POST['track_logged_in']) && $_POST['track_logged_in'] === 'on' ? 'on' : 'off',
            'af_stats_weekly_email' => isset($_POST['weekly_email']) && $_POST['weekly_email'] === 'on' ? 'on' : 'off',
            'af_stats_weekly_email_to' => $weekly_email_to,
            'af_stats_weekly_email_day' => max(1, min(7, intval(isset($_POST['weekly_email_day']) ? $_POST['weekly_email_day'] : 1))),
        );

        foreach ($settings as $key => $value) {
            update_option($key, $value, 'no');
        }

        wp_send_json_success(array('message' => 'Settings saved'));
    }

    /**
     * Lot Analytics - get all data for admin dashboard
     */
    public static function handle_lot_analytics_data() {
        if (!self::admin_check()) return;

        if (!class_exists('\auctionforge\inc\model\LotAnalytics')) {
            wp_send_json_error('LotAnalytics class not found');
            return;
        }

        $termId = isset($_POST['term_id']) ? intval($_POST['term_id']) : 0;
        list($paFrom, $paTo) = self::get_date_range(); // global dashboard range

        $auctions = \auctionforge\inc\model\LotAnalytics::getAllAuctionStats(50);
        $mostViewed = \auctionforge\inc\model\LotAnalytics::getMostViewed(20, $termId);
        $mostFavorited = \auctionforge\inc\model\LotAnalytics::getMostFavorited(20, $termId);
        $trending = \auctionforge\inc\model\LotAnalytics::getTrending(20, 7, $termId);
        $conversion = \auctionforge\inc\model\LotAnalytics::getConversionStats($termId, 20);
        $peakTimes = \auctionforge\inc\model\LotAnalytics::getPeakViewingTimes($termId, 30, $paFrom, $paTo);
        $preSaleVsLive = \auctionforge\inc\model\LotAnalytics::getPreSaleVsLive($termId);
        $categories = \auctionforge\inc\model\LotAnalytics::getCategoryStats($termId, 20);
        $shares = \auctionforge\inc\model\LotAnalytics::getShareStats($termId, 20);
        $searchFunnel = \auctionforge\inc\model\LotAnalytics::getSearchFunnel($termId, 20);

        // Resolve category names from auction tags (build master map from all auctions)
        if (!empty($categories)) {
            global $wpdb;
            $masterTags = array();
            if ($termId > 0) {
                $tagTermIds = array($termId);
            } else {
                $tagTermIds = $wpdb->get_col("SELECT DISTINCT term_id FROM {$wpdb->termmeta} WHERE meta_key = '_auction_tags'");
            }
            foreach ($tagTermIds as $tagTid) {
                $raw = get_term_meta($tagTid, '_auction_tags', true);
                $decoded = is_string($raw) ? json_decode($raw, true) : (is_array($raw) ? $raw : null);
                if ($decoded && is_array($decoded)) {
                    $masterTags = $masterTags + $decoded;
                }
            }
            foreach ($categories as &$cat) {
                if (isset($masterTags[$cat->categoryId])) {
                    $cat->category_name = $masterTags[$cat->categoryId];
                }
            }
            unset($cat);
        }

        wp_send_json_success(array(
            'auctions' => $auctions,
            'most_viewed' => $mostViewed,
            'most_favorited' => $mostFavorited,
            'trending' => $trending,
            'conversion' => $conversion,
            'peak_times' => $peakTimes,
            'pre_sale_vs_live' => $preSaleVsLive,
            'categories' => $categories,
            'shares' => $shares,
            'search_funnel' => $searchFunnel,
        ));
    }

    /**
     * Lot Analytics - backfill from existing pageview data
     */
    public static function handle_lot_analytics_backfill() {
        if (!self::admin_check()) return;

        if (!class_exists('\auctionforge\inc\model\LotAnalytics')) {
            wp_send_json_error('LotAnalytics class not found');
            return;
        }

        $count = \auctionforge\inc\model\LotAnalytics::backfillFromPageviews();
        wp_send_json_success(array('message' => "Backfilled {$count} lots from pageview data"));
    }

    /**
     * Lot Analytics - record a favorite (frontend)
     */
    public static function handle_lot_analytics_favorite() {
        check_ajax_referer('bp-ajax-string', 'security');

        $settings = get_option('bidspirit_settings', array());
        if (!is_array($settings)) $settings = array();
        $enabled = isset($settings['enable_lot_analytics']) ? $settings['enable_lot_analytics'] : 'off';
        if ($enabled !== 'on') {
            wp_send_json_error(array('message' => 'Feature disabled'));
            return;
        }

        if (!class_exists('\auctionforge\inc\model\LotAnalytics')) {
            wp_send_json_error('LotAnalytics class not found');
            return;
        }

        $userEmail = isset($_POST['user_email']) ? sanitize_email($_POST['user_email']) : '';
        $postId = isset($_POST['post_id']) ? intval($_POST['post_id']) : 0;
        $termId = isset($_POST['term_id']) ? intval($_POST['term_id']) : 0;
        $isFavorite = isset($_POST['is_favorite']) ? ($_POST['is_favorite'] === 'true' || $_POST['is_favorite'] === '1') : true;

        if (empty($userEmail) || empty($postId)) {
            wp_send_json_error(array('message' => 'Missing required fields'));
            return;
        }

        $result = \auctionforge\inc\model\LotAnalytics::recordFavorite($userEmail, $postId, $termId, $isFavorite);

        // Tally into user_interests when an add (not a remove).
        if ($isFavorite) {
            do_action('af_stats_lot_favorited', $userEmail, $postId);
        }

        $count = \auctionforge\inc\model\LotAnalytics::getFavoriteCount($postId);
        wp_send_json_success(array('favorite_count' => $count));
    }


    /**
     * Lot Analytics - export CSV
     */
    public static function handle_lot_analytics_export() {
        if (!self::admin_check()) return;

        $termId = isset($_POST['term_id']) ? intval($_POST['term_id']) : 0;
        $data = \auctionforge\inc\model\LotAnalytics::getExportData($termId);

        wp_send_json_success(array('rows' => $data));
    }

    /**
     * Lot Analytics - record a share event (frontend)
     */
    public static function handle_lot_analytics_share() {
        check_ajax_referer('bp-ajax-string', 'security');

        $settings = get_option('bidspirit_settings', array());
        if (!is_array($settings)) $settings = array();
        if ((isset($settings['enable_lot_analytics']) ? $settings['enable_lot_analytics'] : 'off') !== 'on') {
            wp_send_json_error('disabled');
            return;
        }

        $postId = isset($_POST['post_id']) ? intval($_POST['post_id']) : 0;
        $termId = isset($_POST['term_id']) ? intval($_POST['term_id']) : 0;
        $channel = isset($_POST['channel']) ? sanitize_text_field($_POST['channel']) : 'unknown';
        $visitorHash = isset($_POST['visitor_hash']) ? sanitize_text_field($_POST['visitor_hash']) : '';

        if (empty($postId)) {
            wp_send_json_error('missing post_id');
            return;
        }

        \auctionforge\inc\model\LotAnalytics::recordShare($postId, $termId, $channel, $visitorHash);
        wp_send_json_success();
    }

    /**
     * Lot Analytics - record click source (catalogue vs external)
     */
    public static function handle_lot_analytics_click_source() {
        check_ajax_referer('bp-ajax-string', 'security');

        $settings = get_option('bidspirit_settings', array());
        if (!is_array($settings)) $settings = array();
        if ((isset($settings['enable_lot_analytics']) ? $settings['enable_lot_analytics'] : 'off') !== 'on') {
            wp_send_json_error('disabled');
            return;
        }

        $postId = isset($_POST['post_id']) ? intval($_POST['post_id']) : 0;
        $termId = isset($_POST['term_id']) ? intval($_POST['term_id']) : 0;
        $fromCatalogue = isset($_POST['from_catalogue']) ? ($_POST['from_catalogue'] === 'true' || $_POST['from_catalogue'] === '1') : false;

        if (empty($postId)) {
            wp_send_json_error('missing post_id');
            return;
        }

        \auctionforge\inc\model\LotAnalytics::recordClickSource($postId, $termId, $fromCatalogue);
        wp_send_json_success();
    }

}
