<?php

/**
 * Analytics Calculation Functions
 * Retrieves and processes analytics data from database
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/* ============================================================================
 * Bot / forged-traffic filtering (read-time, flag-not-drop)
 *
 * Nothing is deleted. Human-facing aggregates simply exclude sessions matching a
 * bot signature at query time, so the rule stays tunable and any already-stored
 * junk (e.g. the Pure Golf spoofed-Google spike) falls out of every metric the
 * moment this ships — no backfill. Admins can flip the toggle off to audit the
 * raw data, or tighten the predicate via the filters below.
 *
 * Signature: a sub-2-second bounce. A "visit" that bounced in <=1s did nothing —
 * whether it's a forged POST (duration always exactly 1s in the observed attack)
 * or a real instant-back, it carries no engagement and only distorts the numbers.
 * ========================================================================== */

/**
 * SQL boolean matching a suspected-bot session in the kantan_analytics table.
 * Contains no user input — safe to splice into a prepared query string.
 *
 * @param string $alias Table alias (e.g. 'a'), or '' for an unaliased table.
 * @return string
 */
function kantan_bot_session_sql($alias = '') {
    $p    = $alias !== '' ? $alias . '.' : '';
    $expr = "({$p}is_bounce = 1 AND {$p}visit_duration <= 1)";
    return apply_filters('kantan_bot_session_sql', $expr, $alias);
}

/** Whether human-facing aggregates exclude suspected bots (default: on). */
function kantan_bot_filter_enabled() {
    return (bool) apply_filters('kantan_filter_bots', get_option('kantan_filter_bots', 1));
}

/**
 * " AND NOT (<bot predicate>)" for appending to a WHERE clause, or '' when the
 * filter is disabled. Splice AFTER the last placeholder — it carries none.
 *
 * @param string $alias Table alias for kantan_analytics.
 * @return string
 */
function kantan_human_and($alias = '') {
    return kantan_bot_filter_enabled() ? ' AND NOT ' . kantan_bot_session_sql($alias) : '';
}

/**
 * How many suspected-bot sessions are in the window — powers the dashboard's
 * "N bot sessions filtered" note. Ignores the enabled toggle (it always counts).
 *
 * @param int $days
 * @return int
 */
function kantan_get_bot_session_count($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_analytics';
    $date  = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    return (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id) FROM $table WHERE visit_date >= %s AND " . kantan_bot_session_sql(),
        $date
    ));
}

/* Get total visits for a date range */
function kantan_get_total_visits($days = 30) {
    $bounds = kantan_long_range_bounds($days);
    if ($bounds['needs_hybrid']) {
        $total = 0;
        foreach (kantan_long_range_daily_metrics($days) as $row) {
            $total += intval($row['sessions']);
        }
        return $total;
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id) FROM $table_name WHERE visit_date >= %s" . kantan_human_and(),
        $date
    ));
}

/* Get average session duration in seconds */
function kantan_get_avg_duration($days = 30) {
    $bounds = kantan_long_range_bounds($days);
    if ($bounds['needs_hybrid']) {
        $sessions = 0; $duration = 0;
        foreach (kantan_long_range_daily_metrics($days) as $row) {
            $sessions += intval($row['sessions']);
            $duration += intval($row['total_duration']);
        }
        return $sessions ? round($duration / $sessions) : 0;
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $avg = $wpdb->get_var($wpdb->prepare(
        "SELECT AVG(LEAST(visit_duration, 1800)) FROM $table_name WHERE visit_date >= %s AND visit_duration > 0" . kantan_human_and(),
        $date
    ));

    return round($avg ?: 0);
}

/* Get bounce rate percentage */
function kantan_get_bounce_rate($days = 30) {
    $bounds = kantan_long_range_bounds($days);
    if ($bounds['needs_hybrid']) {
        $sessions = 0; $bounces = 0;
        foreach (kantan_long_range_daily_metrics($days) as $row) {
            $sessions += intval($row['sessions']);
            $bounces  += intval($row['bounces']);
        }
        return $sessions ? round(($bounces / $sessions) * 100, 1) : 0;
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $total = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id) FROM $table_name WHERE visit_date >= %s" . kantan_human_and(),
        $date
    ));

    $bounces = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id) FROM $table_name WHERE visit_date >= %s AND is_bounce = 1" . kantan_human_and(),
        $date
    ));

    if ($total == 0) return 0;

    return round(($bounces / $total) * 100, 1);
}

/**
 * AI assistants / answer engines, mapped from a host (or host+path) fragment to a
 * display label. Single source of truth — matched case-insensitively as a
 * substring against the referrer URL and/or utm_source, so it covers both the
 * referrer-tagged and empty-referrer-but-UTM-tagged cases. Easy to extend.
 *
 * @return array fragment => label
 */
function kantan_ai_assistants() {
    return array(
        'chatgpt.com'           => 'ChatGPT',
        'chat.openai.com'       => 'ChatGPT',
        'openai.com'            => 'ChatGPT',
        'gemini.google.com'     => 'Gemini',
        'bard.google.com'       => 'Gemini',
        'perplexity.ai'         => 'Perplexity',
        'claude.ai'             => 'Claude',
        'copilot.microsoft.com' => 'Copilot',
        'bing.com/chat'         => 'Copilot',
        'you.com'               => 'You.com',
        'phind.com'             => 'Phind',
        'poe.com'               => 'Poe',
        'chat.deepseek.com'     => 'DeepSeek',
        'deepseek.com'          => 'DeepSeek',
        'meta.ai'               => 'Meta AI',
        'chat.mistral.ai'       => 'Mistral',
        'grok.com'              => 'Grok',
    );
}

/**
 * Identify the AI assistant behind a visit from EITHER the referrer URL or the
 * utm_source. Returns the assistant label (e.g. "ChatGPT", "Gemini") or '' if
 * neither signal matches.
 *
 * @param string $referrer   Full referrer URL (may be empty).
 * @param string $utm_source utm_source value (may be empty).
 * @return string
 */
function kantan_classify_ai_assistant($referrer, $utm_source = '') {
    $hay = strtolower(trim((string) $referrer . ' ' . (string) $utm_source));
    if ($hay === '') {
        return '';
    }
    foreach (kantan_ai_assistants() as $needle => $label) {
        if (strpos($hay, $needle) !== false) {
            return $label;
        }
    }
    return '';
}

/**
 * Whether a referrer URL is an AI assistant. Back-compat wrapper.
 *
 * @param string $referrer
 * @return bool
 */
function kantan_is_ai_referrer($referrer) {
    return kantan_classify_ai_assistant($referrer) !== '';
}

/**
 * Match fragments (back-compat for the 2.13.0 referrer backfill).
 *
 * @return array
 */
function kantan_ai_referrer_hosts() {
    return array_keys(kantan_ai_assistants());
}

/**
 * Per-assistant visit counts within the 'ai' bucket for the window.
 *
 * @param int $days
 * @return array of objects { ai_assistant, visits }
 */
function kantan_get_ai_assistant_breakdown($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_analytics';
    $date  = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT ai_assistant, COUNT(DISTINCT session_id) AS visits
         FROM $table
         WHERE visit_date >= %s AND traffic_source = 'ai' AND ai_assistant IS NOT NULL AND ai_assistant <> ''
         GROUP BY ai_assistant
         ORDER BY visits DESC",
        $date
    ));
}

/**
 * Known AI crawlers, mapped from a User-Agent fragment to [label, type].
 *
 * Unlike AI *assistants* (which send human referral traffic and are detected
 * from the referrer/utm), AI *crawlers* are bots that fetch pages server-side —
 * they never run the JS beacon, so they are detected from the User-Agent on
 * page load (see kantan_log_ai_crawler_hit()). Type is either:
 *   - 'crawl' : indexing / model-training crawl (GPTBot, ClaudeBot, CCBot, …)
 *   - 'fetch' : live, user-triggered retrieval (ChatGPT-User, Perplexity-User)
 *
 * Matched case-insensitively as a substring against the lowercased UA. Order
 * matters: list the more specific 'fetch' agents before their crawler siblings
 * so a fetch UA is never shadowed by a broader needle. Filterable so sites can
 * extend it as new crawlers appear.
 *
 * @return array fragment => array(label, type)
 */
function kantan_ai_crawlers() {
    return apply_filters('kantan_ai_crawlers', array(
        'chatgpt-user'       => array('ChatGPT', 'fetch'),
        'oai-searchbot'      => array('OpenAI Search', 'crawl'),
        'gptbot'             => array('GPTBot', 'crawl'),
        'perplexity-user'    => array('Perplexity', 'fetch'),
        'perplexitybot'      => array('PerplexityBot', 'crawl'),
        'claude-user'        => array('Claude', 'fetch'),
        'claude-searchbot'   => array('Claude Search', 'crawl'),
        'claudebot'          => array('ClaudeBot', 'crawl'),
        'anthropic-ai'       => array('Anthropic', 'crawl'),
        'bytespider'         => array('Bytespider', 'crawl'),
        'ccbot'              => array('CCBot (Common Crawl)', 'crawl'),
        'meta-externalagent' => array('Meta AI', 'crawl'),
        'amazonbot'          => array('Amazonbot', 'crawl'),
        'youbot'             => array('YouBot', 'crawl'),
        'cohere-ai'          => array('Cohere', 'crawl'),
        'diffbot'            => array('Diffbot', 'crawl'),
        'timpibot'           => array('Timpibot', 'crawl'),
    ));
}

/**
 * Identify the AI crawler behind a request from its User-Agent.
 *
 * @param string $user_agent Full User-Agent string (may be empty).
 * @return array|null array('crawler' => label, 'type' => 'crawl'|'fetch') or null.
 */
function kantan_classify_ai_crawler($user_agent) {
    $ua = strtolower(trim((string) $user_agent));
    if ($ua === '') {
        return null;
    }
    foreach (kantan_ai_crawlers() as $needle => $meta) {
        if (strpos($ua, $needle) !== false) {
            return array('crawler' => $meta[0], 'type' => $meta[1]);
        }
    }
    return null;
}

/**
 * Per-crawler hit counts within the window, from the separate crawler-hits
 * table (never mixed with human visitor metrics).
 *
 * @param int $days
 * @return array of objects { crawler, crawler_type, hits, pages, last_seen }
 */
function kantan_get_ai_crawler_breakdown($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_crawler_hits';
    $date  = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT crawler, crawler_type,
                COUNT(*) AS hits,
                COUNT(DISTINCT page_url) AS pages,
                MAX(hit_time) AS last_seen
         FROM $table
         WHERE hit_time >= %s
         GROUP BY crawler, crawler_type
         ORDER BY hits DESC",
        $date
    ));
}

/**
 * Headline AI-crawler totals for the window. Nulls (empty table) normalize to 0.
 *
 * @param int $days
 * @return array { hits, crawlers, pages, crawl_hits, fetch_hits }
 */
function kantan_get_ai_crawler_totals($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_crawler_hits';
    $date  = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    $row = $wpdb->get_row($wpdb->prepare(
        "SELECT COUNT(*) AS hits,
                COUNT(DISTINCT crawler) AS crawlers,
                COUNT(DISTINCT page_url) AS pages,
                SUM(CASE WHEN crawler_type = 'crawl' THEN 1 ELSE 0 END) AS crawl_hits,
                SUM(CASE WHEN crawler_type = 'fetch' THEN 1 ELSE 0 END) AS fetch_hits
         FROM $table
         WHERE hit_time >= %s",
        $date
    ));
    return array(
        'hits'       => (int) ($row->hits ?? 0),
        'crawlers'   => (int) ($row->crawlers ?? 0),
        'pages'      => (int) ($row->pages ?? 0),
        'crawl_hits' => (int) ($row->crawl_hits ?? 0),
        'fetch_hits' => (int) ($row->fetch_hits ?? 0),
    );
}

/**
 * The pages AI crawlers hit most within the window.
 *
 * @param int $days
 * @param int $limit
 * @return array of objects { page_url, hits, crawlers, last_seen }
 */
function kantan_get_top_crawled_pages($days = 30, $limit = 10) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_crawler_hits';
    $date  = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    $limit = max(1, intval($limit));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT page_url,
                COUNT(*) AS hits,
                COUNT(DISTINCT crawler) AS crawlers,
                MAX(hit_time) AS last_seen
         FROM $table
         WHERE hit_time >= %s
         GROUP BY page_url
         ORDER BY hits DESC
         LIMIT %d",
        $date,
        $limit
    ));
}

/**
 * Human-friendly label for a traffic-source key. The dashboard applies
 * CSS `capitalize`, so "AI assistants" renders as "AI Assistants".
 *
 * @param string $src
 * @return string
 */
function kantan_traffic_source_label($src) {
    $src = (string) $src;
    if ($src === 'ai') {
        return __('AI assistants', 'kantan-analytics');
    }
    return $src !== '' ? $src : 'unknown';
}

/* Get traffic sources breakdown */
function kantan_get_traffic_sources($days = 30) {
    $bounds = kantan_long_range_bounds($days);
    if ($bounds['needs_hybrid']) {
        $totals = array('direct' => 0, 'organic' => 0, 'social' => 0, 'referral' => 0, 'ai' => 0);
        foreach (kantan_long_range_daily_metrics($days) as $row) {
            $totals['direct']   += intval($row['source_direct']);
            $totals['organic']  += intval($row['source_organic']);
            $totals['social']   += intval($row['source_social']);
            $totals['referral'] += intval($row['source_referral']);
            $totals['ai']       += intval($row['source_ai'] ?? 0);
        }
        arsort($totals);
        $out = array();
        foreach ($totals as $src => $n) {
            if ($n > 0) {
                $out[] = (object) array('traffic_source' => $src, 'visits' => $n);
            }
        }
        return $out;
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT traffic_source, COUNT(DISTINCT session_id) as visits
        FROM $table_name
        WHERE visit_date >= %s" . kantan_human_and() . "
        GROUP BY traffic_source
        ORDER BY visits DESC",
        $date
    ));
}

/**
 * Visitor country breakdown (sessions per ISO country code), bot-filtered like
 * the other human-facing aggregates. Rows with no resolved country are omitted.
 */
function kantan_get_country_breakdown($days = 30, $limit = 250) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';
    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT country, COUNT(DISTINCT session_id) as visits
        FROM $table_name
        WHERE visit_date >= %s
        AND country IS NOT NULL
        AND country <> ''" . kantan_human_and() . "
        GROUP BY country
        ORDER BY visits DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get top referrers */
function kantan_get_top_referrers($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT referrer, COUNT(DISTINCT session_id) as visits
        FROM $table_name
        WHERE visit_date >= %s
        AND referrer != ''
        AND referrer IS NOT NULL" . kantan_human_and() . "
        GROUP BY referrer
        ORDER BY visits DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get visits by day for chart */
function kantan_get_visits_by_day($days = 30) {
    $bounds = kantan_long_range_bounds($days);
    if ($bounds['needs_hybrid']) {
        return kantan_long_range_visits_by_day($days);
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT DATE(visit_date) as date, COUNT(DISTINCT session_id) as visits
        FROM $table_name
        WHERE visit_date >= %s" . kantan_human_and() . "
        GROUP BY DATE(visit_date)
        ORDER BY date ASC",
        $date
    ));
}

/* Get most visited pages */
function kantan_get_top_pages($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, page_title, COUNT(*) as views
        FROM $table_name
        WHERE visit_date >= %s
        GROUP BY page_url
        ORDER BY views DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get sparkline data (last 7 daily values) for overview stat cards */
function kantan_get_sparkline_data($days = 30) {
	global $wpdb;
	$table = $wpdb->prefix . 'kantan_analytics';
	$date = gmdate('Y-m-d', strtotime("-$days days"));

	// Visits per day
	$visits = $wpdb->get_results($wpdb->prepare(
		"SELECT DATE(visit_date) as d, COUNT(DISTINCT session_id) as v
		FROM $table WHERE visit_date >= %s
		GROUP BY d ORDER BY d ASC", $date
	));

	// Duration per day
	$durations = $wpdb->get_results($wpdb->prepare(
		"SELECT DATE(visit_date) as d, ROUND(AVG(visit_duration)) as v
		FROM $table WHERE visit_date >= %s AND visit_duration > 0
		GROUP BY d ORDER BY d ASC", $date
	));

	// Bounce rate per day
	$bounces = $wpdb->get_results($wpdb->prepare(
		"SELECT DATE(visit_date) as d,
			ROUND(SUM(is_bounce) / COUNT(DISTINCT session_id) * 100, 1) as v
		FROM $table WHERE visit_date >= %s
		GROUP BY d ORDER BY d ASC", $date
	));

	// Organic per day
	$organic = $wpdb->get_results($wpdb->prepare(
		"SELECT DATE(visit_date) as d, COUNT(DISTINCT session_id) as v
		FROM $table WHERE visit_date >= %s AND traffic_source = 'organic'
		GROUP BY d ORDER BY d ASC", $date
	));

	// AI assistants per day
	$ai = $wpdb->get_results($wpdb->prepare(
		"SELECT DATE(visit_date) as d, COUNT(DISTINCT session_id) as v
		FROM $table WHERE visit_date >= %s AND traffic_source = 'ai'
		GROUP BY d ORDER BY d ASC", $date
	));

	// Convert each to simple arrays of values (take last 7)
	$extract = function($rows) {
		$vals = array_map(function($r) { return floatval($r->v); }, $rows);
		return array_slice($vals, -7);
	};

	return array(
		'visits'   => $extract($visits),
		'duration' => $extract($durations),
		'bounce'   => $extract($bounces),
		'organic'  => $extract($organic),
		'ai'       => $extract($ai),
	);
}

/* Format duration in human readable format */
function kantan_format_duration($seconds) {
    if ($seconds < 60) {
        return $seconds . 's';
    } elseif ($seconds < 3600) {
        return floor($seconds / 60) . 'm ' . ($seconds % 60) . 's';
    } else {
        $hours = floor($seconds / 3600);
        $minutes = floor(($seconds % 3600) / 60);
        return $hours . 'h ' . $minutes . 'm';
    }
}

/* ========================================
   CLICK TRACKING FUNCTIONS
   ======================================== */

/* Get click heatmap data for a specific page */
function kantan_get_click_heatmap($page_url = '', $days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    if ($page_url) {
        return $wpdb->get_results($wpdb->prepare(
            "SELECT click_x_pct, click_y_pct, COUNT(*) as clicks
            FROM $table_name
            WHERE click_time >= %s AND page_url = %s
            GROUP BY click_x_pct, click_y_pct
            ORDER BY clicks DESC",
            $date,
            $page_url
        ));
    }

    return $wpdb->get_results($wpdb->prepare(
        "SELECT click_x_pct, click_y_pct, COUNT(*) as clicks
        FROM $table_name
        WHERE click_time >= %s
        GROUP BY click_x_pct, click_y_pct
        ORDER BY clicks DESC",
        $date
    ));
}

/* Get click region summary (3x3 grid) */
function kantan_get_click_regions($days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $results = $wpdb->get_results($wpdb->prepare(
        "SELECT click_region, COUNT(*) as clicks
        FROM $table_name
        WHERE click_time >= %s
        GROUP BY click_region
        ORDER BY click_region ASC",
        $date
    ));

    // Initialize all 9 regions with 0
    $regions = array_fill(1, 9, 0);
    foreach ($results as $row) {
        $regions[$row->click_region] = intval($row->clicks);
    }

    return $regions;
}

/* Get total clicks count */
function kantan_get_total_clicks($days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table_name WHERE click_time >= %s",
        $date
    )) ?: 0;
}

/**
 * SQL fragment that filters click rows down to genuinely-labelled interactive
 * controls. Excludes:
 *   - empty labels (dead-space clicks the tracker records with no text),
 *   - structural container tags that can never be a control, and
 *   - over-long labels (legacy rows where a container click slurped a whole
 *     page of text before the tracker was fixed).
 * Shared by the top-clicked, region-detail, and element-detail queries.
 */
function kantan_clicks_quality_filter() {
    return "TRIM(element_text) != ''
        AND CHAR_LENGTH(TRIM(element_text)) <= 80
        AND element_tag NOT IN ('body','html','main','section','header','footer','nav','article','aside','ul','ol','li','form','div')";
}

/* Get top clicked elements (merged across tag/case variants, with page spread) */
function kantan_get_top_clicked_elements($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date   = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    $filter = kantan_clicks_quality_filter();

    // Merge the same label across tag/case variants — an <a> and its inner
    // <span>, or "Book a Time" vs "BOOK A TIME", are one control. MIN(tag)
    // surfaces the most meaningful tag (a < button < span alphabetically).
    return $wpdb->get_results($wpdb->prepare(
        "SELECT
            MIN(element_tag)            AS element_tag,
            MAX(element_text)           AS element_text,
            COUNT(*)                    AS clicks,
            COUNT(DISTINCT page_url)    AS page_count
        FROM $table_name
        WHERE click_time >= %s AND $filter
        GROUP BY LOWER(TRIM(element_text))
        ORDER BY clicks DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/**
 * Per-page breakdown for a single clicked label — powers the Top Clicked
 * Elements drill-down (which pages was this control clicked on?).
 *
 * @param string $text Element label (matched case-insensitively).
 * @param int    $days Lookback window.
 * @return array {label, total_clicks, pages[]}
 */
function kantan_get_clicked_element_detail($text, $days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    $norm = strtolower(trim((string) $text));

    $where  = "click_time >= %s AND LOWER(TRIM(element_text)) = %s";
    $params = array($date, $norm);

    $total = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table_name WHERE $where",
        $params
    ));

    $pages = $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, COUNT(*) AS clicks
        FROM $table_name
        WHERE $where
        GROUP BY page_url
        ORDER BY clicks DESC
        LIMIT 25",
        $params
    ));

    return array(
        'label'        => (string) $text,
        'total_clicks' => $total,
        'pages'        => $pages,
    );
}

/* Get pages with click data */
function kantan_get_pages_with_clicks($days = 30, $limit = 20) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, COUNT(*) as clicks
        FROM $table_name
        WHERE click_time >= %s
        GROUP BY page_url
        ORDER BY clicks DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/**
 * Detail for a single click region (1-9) — powers the Click Regions drill-down.
 * The 3x3 grid aggregates every page together, so on its own it can't tell you
 * WHAT was clicked or WHERE. This returns the actual elements clicked in the
 * region (grouped by element + page, so the same label on two pages stays
 * distinct) plus a per-page rollup.
 *
 * @param int    $region   1-9.
 * @param int    $days     Lookback window.
 * @param string $page_url Optional — scope to a single page.
 * @return array {region, total_clicks, elements[], pages[]}
 */
function kantan_get_click_region_detail($region, $days = 30, $page_url = '') {
    global $wpdb;
    $table  = $wpdb->prefix . 'kantan_clicks';
    $region = max(1, min(9, (int) $region));
    $date   = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $where  = "click_region = %d AND click_time >= %s";
    $params = array($region, $date);
    if ($page_url !== '') {
        $where   .= " AND page_url = %s";
        $params[] = $page_url;
    }

    $total = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table WHERE $where",
        $params
    ));

    // What was actually clicked here. Grouped by normalized label + page so
    // "Book Now" on /contact and /pricing stay distinct, while tag/case variants
    // of the same control merge. Same quality filter as the Top Clicked table.
    $filter   = kantan_clicks_quality_filter();
    $elements = $wpdb->get_results($wpdb->prepare(
        "SELECT MIN(element_tag) AS element_tag, MAX(element_text) AS element_text, page_url, COUNT(*) AS clicks
        FROM $table
        WHERE $where AND $filter
        GROUP BY LOWER(TRIM(element_text)), page_url
        ORDER BY clicks DESC
        LIMIT 50",
        $params
    ));

    // Per-page rollup for this region.
    $pages = $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, COUNT(*) AS clicks
        FROM $table
        WHERE $where
        GROUP BY page_url
        ORDER BY clicks DESC
        LIMIT 25",
        $params
    ));

    return array(
        'region'       => $region,
        'total_clicks' => $total,
        'elements'     => $elements,
        'pages'        => $pages,
    );
}

/* ========================================
   JOURNEY TRACKING FUNCTIONS
   ======================================== */

/* Get session journeys (page sequences) */
function kantan_get_session_journeys($days = 30, $limit = 20) {
    global $wpdb;
    $pageviews_table = $wpdb->prefix . 'kantan_pageviews';
    $analytics_table = $wpdb->prefix . 'kantan_analytics';
    $utm_table = $wpdb->prefix . 'kantan_utm';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // Get unique sessions with their details (LEFT JOIN UTM for campaign attribution)
    $sessions = $wpdb->get_results($wpdb->prepare(
        "SELECT DISTINCT p.session_id,
            MIN(p.entered_at) as session_start,
            MAX(p.sequence_num) as page_count,
            SUM(p.time_on_page) as total_duration,
            a.traffic_source,
            u.utm_source,
            u.utm_medium,
            u.utm_campaign,
            u.utm_term,
            u.utm_content
        FROM $pageviews_table p
        LEFT JOIN $analytics_table a ON p.session_id = a.session_id
        LEFT JOIN $utm_table u ON u.session_id = p.session_id
        WHERE p.entered_at >= %s
        GROUP BY p.session_id
        ORDER BY session_start DESC
        LIMIT %d",
        $date,
        $limit
    ));

    // For each session, get the page sequence
    foreach ($sessions as &$session) {
        $session->pages = $wpdb->get_results($wpdb->prepare(
            "SELECT page_url, page_title, sequence_num, time_on_page, scroll_depth
            FROM $pageviews_table
            WHERE session_id = %s
            ORDER BY sequence_num ASC",
            $session->session_id
        ));
    }

    return $sessions;
}

/* Get page transitions (from -> to) */
function kantan_get_page_transitions($days = 30, $limit = 20) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT
            p1.page_url as from_page,
            p2.page_url as to_page,
            COUNT(*) as transitions
        FROM $table_name p1
        INNER JOIN $table_name p2 ON p1.session_id = p2.session_id
            AND p2.sequence_num = p1.sequence_num + 1
        WHERE p1.entered_at >= %s
        GROUP BY p1.page_url, p2.page_url
        ORDER BY transitions DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get entry pages (first page in session) */
function kantan_get_entry_pages($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, page_title, COUNT(*) as entries
        FROM $table_name
        WHERE entered_at >= %s AND sequence_num = 1
        GROUP BY page_url
        ORDER BY entries DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get exit pages (last page in session) */
function kantan_get_exit_pages($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT p.page_url, p.page_title, COUNT(*) as exits
        FROM $table_name p
        INNER JOIN (
            SELECT session_id, MAX(sequence_num) as max_seq
            FROM $table_name
            WHERE entered_at >= %s
            GROUP BY session_id
        ) m ON p.session_id = m.session_id AND p.sequence_num = m.max_seq
        WHERE p.entered_at >= %s
        GROUP BY p.page_url
        ORDER BY exits DESC
        LIMIT %d",
        $date,
        $date,
        $limit
    ));
}

/* ========================================
   SCROLL DEPTH FUNCTIONS
   ======================================== */

/* Get average scroll depth by page */
function kantan_get_scroll_depth_by_page($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    return $wpdb->get_results($wpdb->prepare(
        "SELECT page_url, page_title, AVG(scroll_depth) as avg_scroll, COUNT(*) as views
        FROM $table_name
        WHERE entered_at >= %s AND scroll_depth > 0
        GROUP BY page_url
        ORDER BY views DESC
        LIMIT %d",
        $date,
        $limit
    ));
}

/* Get scroll depth distribution for a page */
function kantan_get_scroll_distribution($page_url = '', $days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // Get counts for each quartile (0-25, 26-50, 51-75, 76-100). Build the
    // query in a single prepare() call per branch — appending the optional
    // page filter as a %s placeholder (rather than a pre-prepared fragment)
    // avoids double-substitution of any '%' in the page URL.
    if ($page_url) {
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT
                SUM(CASE WHEN scroll_depth <= 25 THEN 1 ELSE 0 END) as depth_25,
                SUM(CASE WHEN scroll_depth > 25 AND scroll_depth <= 50 THEN 1 ELSE 0 END) as depth_50,
                SUM(CASE WHEN scroll_depth > 50 AND scroll_depth <= 75 THEN 1 ELSE 0 END) as depth_75,
                SUM(CASE WHEN scroll_depth > 75 THEN 1 ELSE 0 END) as depth_100,
                COUNT(*) as total
            FROM $table_name
            WHERE entered_at >= %s AND scroll_depth > 0 AND page_url = %s",
            $date, $page_url
        ));
    } else {
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT
                SUM(CASE WHEN scroll_depth <= 25 THEN 1 ELSE 0 END) as depth_25,
                SUM(CASE WHEN scroll_depth > 25 AND scroll_depth <= 50 THEN 1 ELSE 0 END) as depth_50,
                SUM(CASE WHEN scroll_depth > 50 AND scroll_depth <= 75 THEN 1 ELSE 0 END) as depth_75,
                SUM(CASE WHEN scroll_depth > 75 THEN 1 ELSE 0 END) as depth_100,
                COUNT(*) as total
            FROM $table_name
            WHERE entered_at >= %s AND scroll_depth > 0",
            $date
        ));
    }

    return $results ? $results[0] : null;
}

/* Get average scroll depth across all pages */
function kantan_get_avg_scroll_depth($days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $avg = $wpdb->get_var($wpdb->prepare(
        "SELECT AVG(scroll_depth) FROM $table_name WHERE entered_at >= %s AND scroll_depth > 0",
        $date
    ));

    return round($avg ?: 0);
}

/* ========================================
   REFERRER DRILL-DOWN FUNCTIONS
   ======================================== */

/**
 * Count every distinct referrer domain in the period, including the Direct
 * pseudo-entry that kantan_get_top_referrer_domains() folds in.
 *
 * The dashboard only lists the top 10, so this is what lets it say how many
 * referrers are actually being held back ("View all 47").
 */
function kantan_get_referrer_domain_count($days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // Same domain expression as kantan_get_top_referrer_domains(), so the count
    // matches the list exactly.
    $domains = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(referrer, 'https://', ''), 'http://', ''), '/', 1), '?', 1))
        FROM $table_name
        WHERE visit_date >= %s
        AND referrer != ''
        AND referrer IS NOT NULL" . kantan_human_and(),
        $date
    ));

    $has_direct = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id)
        FROM $table_name
        WHERE visit_date >= %s
        AND (referrer = '' OR referrer IS NULL)" . kantan_human_and(),
        $date
    ));

    return $domains + ($has_direct > 0 ? 1 : 0);
}

/* Get top referrer domains (grouped by domain), including Direct traffic */
function kantan_get_top_referrer_domains($days = 30, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';

    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // Get external referrer domains
    $referrers = $wpdb->get_results($wpdb->prepare(
        "SELECT
            SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(referrer, 'https://', ''), 'http://', ''), '/', 1), '?', 1) as domain,
            COUNT(DISTINCT session_id) as visits
        FROM $table_name
        WHERE visit_date >= %s
        AND referrer != ''
        AND referrer IS NOT NULL" . kantan_human_and() . "
        GROUP BY domain
        ORDER BY visits DESC
        LIMIT %d",
        $date,
        $limit
    ));

    // Count direct visits (no referrer)
    $direct_visits = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(DISTINCT session_id)
        FROM $table_name
        WHERE visit_date >= %s
        AND (referrer = '' OR referrer IS NULL)" . kantan_human_and(),
        $date
    ));

    // Add Direct as an entry and re-sort
    if ($direct_visits > 0) {
        $direct = new \stdClass();
        $direct->domain = 'Direct / No Referrer';
        $direct->visits = $direct_visits;
        $referrers[] = $direct;

        usort($referrers, function($a, $b) {
            return $b->visits - $a->visits;
        });

        $referrers = array_slice($referrers, 0, $limit);
    }

    return $referrers;
}

/**
 * Full detail payload for a single referrer domain (or the Direct pseudo-domain).
 * Powers the Top Referrers click-through modal: KPI summary, daily trend, landing
 * pages, the specific referring URLs, and the most recent visitors.
 *
 * Host matching mirrors kantan_get_top_referrer_domains() (exact host extraction)
 * so the modal scopes to exactly the row the user clicked — not a loose substring.
 */
function kantan_get_referrer_domain_details($domain, $days = 30) {
    global $wpdb;
    $analytics   = $wpdb->prefix . 'kantan_analytics';
    $pageviews   = $wpdb->prefix . 'kantan_pageviews';
    $conversions = $wpdb->prefix . 'kantan_conversions';

    $date   = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
    $domain = trim((string) $domain);

    // The Top Referrers list folds no-referrer sessions into a "Direct" entry, so
    // the modal has to recognise that pseudo-domain and match on empty referrers.
    $is_direct = ($domain === '' || strtolower($domain) === 'direct' || stripos($domain, 'direct') === 0);

    $host_expr = "SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(a.referrer,'https://',''),'http://',''),'/',1),'?',1)";
    if ($is_direct) {
        $match        = "(a.referrer = '' OR a.referrer IS NULL)";
        $match_params = array();
    } else {
        $match        = "$host_expr = %s";
        $match_params = array($domain);
    }

    // Shared "rows from this referrer in the window" predicate + bound params.
    $where        = "a.visit_date >= %s AND $match";
    $where_params = array_merge(array($date), $match_params);

    // --- Summary KPIs ---
    $summary = $wpdb->get_row($wpdb->prepare(
        "SELECT
            COUNT(DISTINCT a.session_id)        AS sessions,
            COUNT(DISTINCT a.visitor_id)        AS visitors,
            AVG(LEAST(a.visit_duration, 1800))  AS avg_duration,
            SUM(a.is_bounce)                    AS bounces,
            MIN(a.visit_date)                   AS first_seen,
            MAX(a.visit_date)                   AS last_seen
        FROM $analytics a
        WHERE $where",
        $where_params
    ));

    $sessions    = $summary ? (int) $summary->sessions : 0;
    $bounce_rate = $sessions > 0 ? round(((int) $summary->bounces / $sessions) * 100, 1) : 0.0;

    // Total pageviews and conversions across these sessions (subquery keeps the
    // host predicate in one place rather than re-joining).
    $session_subquery = "SELECT a.session_id FROM $analytics a WHERE $where";

    $pageviews_count = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $pageviews pv WHERE pv.session_id IN ($session_subquery)",
        $where_params
    ));

    $conversions_count = 0;
    if ($wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($conversions) . "'") === $conversions) {
        $conversions_count = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $conversions c WHERE c.session_id IN ($session_subquery)",
            $where_params
        ));
    }

    // --- Daily visit trend (drives the modal sparkline) ---
    $by_day = $wpdb->get_results($wpdb->prepare(
        "SELECT DATE(a.visit_date) AS day, COUNT(DISTINCT a.session_id) AS visits
        FROM $analytics a
        WHERE $where
        GROUP BY DATE(a.visit_date)
        ORDER BY day ASC",
        $where_params
    ));

    // --- Landing pages (the analytics row's page_url is the session entry page) ---
    $pages_visited = $wpdb->get_results($wpdb->prepare(
        "SELECT a.page_url, a.page_title, COUNT(DISTINCT a.session_id) AS visits
        FROM $analytics a
        WHERE $where
        GROUP BY a.page_url
        ORDER BY visits DESC
        LIMIT 15",
        $where_params
    ));

    // --- Specific referring URLs (which page on the referrer linked here) ---
    $referrer_urls = array();
    if (!$is_direct) {
        $referrer_urls = $wpdb->get_results($wpdb->prepare(
            "SELECT a.referrer, COUNT(DISTINCT a.session_id) AS visits
            FROM $analytics a
            WHERE $where
            GROUP BY a.referrer
            ORDER BY visits DESC
            LIMIT 15",
            $where_params
        ));
    }

    // --- Recent visitors/sessions from this referrer ---
    $recent = $wpdb->get_results($wpdb->prepare(
        "SELECT a.session_id, a.visitor_id, a.visit_date, a.page_url, a.page_title,
                LEAST(a.visit_duration, 1800) AS visit_duration, a.is_bounce, a.traffic_source,
                (SELECT COUNT(*) FROM $conversions c WHERE c.session_id = a.session_id) AS conversions
        FROM $analytics a
        WHERE $where
        ORDER BY a.visit_date DESC
        LIMIT 25",
        $where_params
    ));
    foreach ($recent as &$row) {
        $row->display_name = kantan_get_visitor_display_name($row->visitor_id);
    }
    unset($row);

    return array(
        'domain'       => $is_direct ? 'Direct / No Referrer' : $domain,
        'is_direct'    => $is_direct,
        'total_visits' => $sessions,
        'summary'      => array(
            'sessions'     => $sessions,
            'visitors'     => $summary ? (int) $summary->visitors : 0,
            'pageviews'    => $pageviews_count,
            'avg_duration' => $summary ? (int) round((float) $summary->avg_duration) : 0,
            'bounce_rate'  => $bounce_rate,
            'conversions'  => $conversions_count,
            'first_seen'   => $summary ? $summary->first_seen : null,
            'last_seen'    => $summary ? $summary->last_seen : null,
        ),
        'by_day'        => $by_day,
        'referrer_urls' => $referrer_urls,
        'pages_visited' => $pages_visited,
        'visitors'      => $recent,
    );
}

/* ========================================
   DEVICE BREAKDOWN FUNCTIONS
   ======================================== */

/* ========================================
   DEVICE & USER DATA FUNCTIONS
   ======================================== */

/**
 * Device type CASE fragment for SQL queries.
 * Mobile ≤768, Tablet 769–1024, Laptop 1025–1440, Desktop 1441+.
 */
function kantan_device_case_sql() {
	return "CASE
		WHEN screen_width <= 768 THEN 'Mobile'
		WHEN screen_width <= 1024 THEN 'Tablet'
		WHEN screen_width <= 1440 THEN 'Laptop'
		WHEN screen_width > 1440 THEN 'Desktop'
		ELSE 'Unknown'
	END";
}

/* Get device type breakdown from screen width */
function kantan_get_device_breakdown($days = 30) {
	global $wpdb;
	$table_name  = $wpdb->prefix . 'kantan_analytics';
	$date        = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
	$device_case = kantan_device_case_sql();

	return $wpdb->get_results($wpdb->prepare(
		"SELECT
			{$device_case} as device_type,
			COUNT(DISTINCT session_id) as sessions,
			AVG(LEAST(visit_duration, 1800)) as avg_duration,
			SUM(CASE WHEN is_bounce = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT session_id) * 100 as bounce_rate
		FROM $table_name
		WHERE visit_date >= %s AND screen_width > 0
		GROUP BY device_type
		ORDER BY sessions DESC",
		$date
	));
}

/* Get device breakdown by traffic source */
function kantan_get_device_by_source($days = 30) {
	global $wpdb;
	$table_name  = $wpdb->prefix . 'kantan_analytics';
	$date        = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
	$device_case = kantan_device_case_sql();

	return $wpdb->get_results($wpdb->prepare(
		"SELECT
			traffic_source,
			{$device_case} as device_type,
			COUNT(DISTINCT session_id) as sessions
		FROM $table_name
		WHERE visit_date >= %s AND screen_width > 0
		GROUP BY traffic_source, device_type
		ORDER BY traffic_source, sessions DESC",
		$date
	));
}

/* Get screen resolution breakdown */
function kantan_get_screen_resolutions($days = 30, $limit = 10) {
	global $wpdb;
	$table_name = $wpdb->prefix . 'kantan_analytics';
	$date       = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

	return $wpdb->get_results($wpdb->prepare(
		"SELECT
			CONCAT(screen_width, 'x', screen_height) as resolution,
			COUNT(DISTINCT session_id) as sessions
		FROM $table_name
		WHERE visit_date >= %s AND screen_width > 0 AND screen_height > 0
		GROUP BY resolution
		ORDER BY sessions DESC
		LIMIT %d",
		$date,
		$limit
	));
}

/**
 * Parse a user agent string into browser name + version.
 *
 * @param string $ua User agent string.
 * @return array { name: string, version: string }
 */
function kantan_parse_browser($ua) {
	$browsers = array(
		'Edge'      => '/Edg(?:e|A|iOS)?\/([0-9.]+)/',
		'Opera'     => '/(?:OPR|Opera)\/([0-9.]+)/',
		'Samsung'   => '/SamsungBrowser\/([0-9.]+)/',
		'UCBrowser' => '/UCBrowser\/([0-9.]+)/',
		'Firefox'   => '/Firefox\/([0-9.]+)/',
		'Chrome'    => '/Chrome\/([0-9.]+)/',
		'Safari'    => '/Version\/([0-9.]+).*Safari/',
		'IE'        => '/(?:MSIE |rv:)([0-9.]+)/',
	);

	foreach ($browsers as $name => $pattern) {
		if (preg_match($pattern, $ua, $m)) {
			// Return major version only.
			$ver = explode('.', $m[1]);
			return array( 'name' => $name, 'version' => $ver[0] );
		}
	}

	return array( 'name' => 'Other', 'version' => '' );
}

/**
 * Parse a user agent string into OS name + version.
 *
 * @param string $ua User agent string.
 * @return string OS name (e.g. "Windows 11", "macOS", "iOS 17", "Android 14", "Linux").
 */
function kantan_parse_os($ua) {
	if (preg_match('/Windows NT ([0-9.]+)/', $ua, $m)) {
		$map = array(
			'10.0' => 'Windows',
			'6.3'  => 'Windows 8.1',
			'6.2'  => 'Windows 8',
			'6.1'  => 'Windows 7',
		);
		return $map[$m[1]] ?? 'Windows';
	}
	if (preg_match('/Mac OS X/', $ua)) {
		return 'macOS';
	}
	if (preg_match('/CrOS/', $ua)) {
		return 'Chrome OS';
	}
	if (preg_match('/iPhone|iPad|iPod/', $ua)) {
		if (preg_match('/OS (\d+)[_.]/', $ua, $m)) {
			return 'iOS ' . $m[1];
		}
		return 'iOS';
	}
	if (preg_match('/Android/', $ua)) {
		if (preg_match('/Android (\d+)/', $ua, $m)) {
			return 'Android ' . $m[1];
		}
		return 'Android';
	}
	if (preg_match('/Linux/', $ua)) {
		return 'Linux';
	}
	return 'Other';
}

/**
 * Get browser breakdown from user agents.
 *
 * Parses user_agent strings in PHP to avoid complex SQL regex.
 */
function kantan_get_browser_breakdown($days = 30, $limit = 10) {
	global $wpdb;
	$table_name = $wpdb->prefix . 'kantan_analytics';
	$date       = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

	$rows = $wpdb->get_results($wpdb->prepare(
		"SELECT user_agent, COUNT(DISTINCT session_id) as sessions
		FROM $table_name
		WHERE visit_date >= %s AND user_agent IS NOT NULL AND user_agent != ''
		GROUP BY user_agent
		ORDER BY sessions DESC",
		$date
	));

	$browsers = array();
	foreach ($rows as $row) {
		$parsed = kantan_parse_browser($row->user_agent);
		$key    = $parsed['name'];
		if (!isset($browsers[$key])) {
			$browsers[$key] = 0;
		}
		$browsers[$key] += (int) $row->sessions;
	}

	arsort($browsers);
	$result = array();
	$i      = 0;
	foreach ($browsers as $name => $sessions) {
		if ($i >= $limit) break;
		$result[] = (object) array( 'browser' => $name, 'sessions' => $sessions );
		$i++;
	}

	return $result;
}

/**
 * Get operating system breakdown from user agents.
 */
function kantan_get_os_breakdown($days = 30, $limit = 10) {
	global $wpdb;
	$table_name = $wpdb->prefix . 'kantan_analytics';
	$date       = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

	$rows = $wpdb->get_results($wpdb->prepare(
		"SELECT user_agent, COUNT(DISTINCT session_id) as sessions
		FROM $table_name
		WHERE visit_date >= %s AND user_agent IS NOT NULL AND user_agent != ''
		GROUP BY user_agent
		ORDER BY sessions DESC",
		$date
	));

	$oses = array();
	foreach ($rows as $row) {
		$os = kantan_parse_os($row->user_agent);
		if (!isset($oses[$os])) {
			$oses[$os] = 0;
		}
		$oses[$os] += (int) $row->sessions;
	}

	arsort($oses);
	$result = array();
	$i      = 0;
	foreach ($oses as $name => $sessions) {
		if ($i >= $limit) break;
		$result[] = (object) array( 'os' => $name, 'sessions' => $sessions );
		$i++;
	}

	return $result;
}

/* ========================================
   NEW VS RETURNING VISITOR FUNCTIONS
   ======================================== */

/* Get new vs returning visitor breakdown */
function kantan_get_visitor_type_breakdown($days = 30) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';
    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // A visitor is "returning" if their visitor_id appears in records before the current period
    return $wpdb->get_results($wpdb->prepare(
        "SELECT
            CASE
                WHEN visitor_id IS NULL OR visitor_id = '' THEN 'unknown'
                WHEN visitor_id IN (
                    SELECT DISTINCT visitor_id FROM $table_name
                    WHERE visit_date < %s AND visitor_id IS NOT NULL AND visitor_id != ''
                ) THEN 'returning'
                ELSE 'new'
            END as visitor_type,
            COUNT(DISTINCT session_id) as sessions,
            AVG(LEAST(visit_duration, 1800)) as avg_duration,
            SUM(CASE WHEN is_bounce = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT session_id) * 100 as bounce_rate
        FROM $table_name
        WHERE visit_date >= %s AND visitor_id IS NOT NULL AND visitor_id != ''
        GROUP BY visitor_type
        ORDER BY sessions DESC",
        $date, $date
    ));
}

/* ========================================
   VISITOR IDENTITY FUNCTIONS
   ======================================== */

/**
 * Generate a deterministic 3-part human name from a visitor_id.
 * Same visitor_id always produces the same name.
 */
function kantan_generate_visitor_name($visitor_id) {
    $first = ['Ada','Alice','Alma','Amber','Annie','Aria','Belle','Beth','Birdie','Blair',
        'Brooke','Callie','Chloe','Clara','Daisy','Della','Eden','Elena','Eliza','Ellie',
        'Ember','Esme','Eve','Faye','Flora','Gemma','Grace','Hazel','Holly','Hope',
        'Iris','Ivy','Jane','Josie','June','Kaia','Lacey','Leah','Lily','Lola',
        'Luna','Mae','Maple','Maren','Mila','Nell','Nora','Olive','Opal','Pearl',
        'Petra','Piper','Quinn','Reese','Riley','Rosa','Ruby','Sage','Sadie','Shea',
        'Skye','Stella','Tara','Tessa','Thea','Vera','Violet','Willa','Wren','Zelda'];

    $middle = ['Abbott','Archer','Banks','Barrett','Blake','Brooks','Burke','Carter','Chase','Cole',
        'Cooper','Cross','Dalton','Davis','Drake','Ellis','Emery','Finch','Flynn','Foster',
        'Grant','Gray','Griffin','Hale','Hart','Hayes','Heath','Holt','Hudson','Hunt',
        'James','Jensen','Kent','Kemp','Knox','Lane','Lawson','Lynch','Marsh','Mason',
        'Miles','Monroe','Nash','North','Oliver','Owen','Palmer','Parks','Pierce','Porter',
        'Quinn','Reed','Rhodes','Riley','Rivers','Rowan','Sawyer','Scott','Shaw','Sloan',
        'Stone','Swift','Tate','Thorne','Troy','Vance','Wade','Walsh','Wells','Wolfe'];

    $last = ['Adams','Allen','Bailey','Baker','Bell','Blair','Bond','Boyd','Burns','Clark',
        'Craig','Dale','Dean','Doyle','Dunn','Evans','Faulk','Field','Ford','Fox',
        'Frost','Gates','Glenn','Gould','Green','Grove','Haines','Hall','Hardy','Hicks',
        'Hill','Howe','Hull','Hyde','Ives','Jacobs','Joyce','Kane','Keane','Keith',
        'Kirby','Lake','Lamb','Lang','Locke','Long','Love','Lyon','March','Mercer',
        'Mills','Moon','Morse','Niles','Noble','Page','Park','Penn','Price','Pryor',
        'Raine','Rand','Roth','Sage','Sharp','Shea','Snow','Stark','Stone','Voss'];

    $hash = crc32($visitor_id);
    $f = abs($hash) % count($first);
    $m = abs(crc32($visitor_id . '_m')) % count($middle);
    $l = abs(crc32($visitor_id . '_l')) % count($last);

    return $first[$f] . '-' . $middle[$m] . '-' . $last[$l];
}

/**
 * Get display name for a visitor. Returns custom name if set, otherwise generated name.
 */
function kantan_get_visitor_display_name($visitor_id) {
    if (empty($visitor_id)) return __('Anonymous', 'kantan-analytics');

    $overrides = get_option('kantan_visitor_names', array());
    if (!empty($overrides[$visitor_id])) {
        return $overrides[$visitor_id];
    }

    return kantan_generate_visitor_name($visitor_id);
}

/**
 * Check if a visitor has been identified (has a custom name)
 */
function kantan_visitor_is_identified($visitor_id) {
    $overrides = get_option('kantan_visitor_names', array());
    return !empty($overrides[$visitor_id]);
}

/**
 * Set a custom name for a visitor
 */
function kantan_set_visitor_name($visitor_id, $name) {
    $overrides = get_option('kantan_visitor_names', array());
    $overrides[$visitor_id] = sanitize_text_field($name);
    update_option('kantan_visitor_names', $overrides);
}

/**
 * Get visitor profiles with session history
 */
function kantan_get_visitor_profiles($days = 30, $limit = 20) {
    global $wpdb;
    $analytics = $wpdb->prefix . 'kantan_analytics';
    $pageviews = $wpdb->prefix . 'kantan_pageviews';
    $conversions = $wpdb->prefix . 'kantan_conversions';
    $utm = $wpdb->prefix . 'kantan_utm';
    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    // Get active visitors with session counts
    $visitors = $wpdb->get_results($wpdb->prepare(
        "SELECT
            visitor_id,
            COUNT(DISTINCT session_id) as total_sessions,
            MIN(visit_date) as first_seen,
            MAX(visit_date) as last_seen,
            GROUP_CONCAT(DISTINCT traffic_source) as sources
        FROM $analytics
        WHERE visit_date >= %s AND visitor_id IS NOT NULL AND visitor_id != ''
        GROUP BY visitor_id
        ORDER BY last_seen DESC
        LIMIT %d",
        $date, $limit
    ));

    // Enrich each visitor with sessions, pages, and conversions
    foreach ($visitors as &$visitor) {
        $visitor->display_name = kantan_get_visitor_display_name($visitor->visitor_id);
        $visitor->is_identified = kantan_visitor_is_identified($visitor->visitor_id);

        // Get their sessions (LEFT JOIN UTM for pre-conversion campaign attribution)
        // user_agent surfaced for bot/source forensics. No IP: Kantan does not
        // store visitor IPs (the column is left NULL on write), so there is none
        // to expose here.
        $visitor->sessions = $wpdb->get_results($wpdb->prepare(
            "SELECT a.session_id, a.traffic_source, a.referrer, a.visit_date,
                    LEAST(a.visit_duration, 1800) as visit_duration, a.is_bounce, a.screen_width,
                    a.user_agent,
                    u.utm_source, u.utm_medium, u.utm_campaign, u.utm_term, u.utm_content
            FROM $analytics a
            LEFT JOIN $utm u ON u.session_id = a.session_id
            WHERE a.visitor_id = %s AND a.visit_date >= %s
            ORDER BY a.visit_date DESC",
            $visitor->visitor_id, $date
        ));

        // Get page sequences for each session
        foreach ($visitor->sessions as &$session) {
            $session->pages = $wpdb->get_results($wpdb->prepare(
                "SELECT page_url, page_title, sequence_num, scroll_depth,
                        LEAST(time_on_page, 1800) as time_on_page
                FROM $pageviews
                WHERE session_id = %s
                ORDER BY sequence_num ASC",
                $session->session_id
            ));

            // Get conversions for this session
            $session->conversions = $wpdb->get_results($wpdb->prepare(
                "SELECT conversion_type, conversion_label, converted_at
                FROM $conversions
                WHERE session_id = %s",
                $session->session_id
            ));
        }
    }

    return $visitors;
}

/**
 * Full profile for a single visitor — powers the Realtime/Profiles click-through
 * history modal. Same shape as one entry of kantan_get_visitor_profiles().
 *
 * @param string $visitor_id
 * @param int    $days
 * @return object|null
 */
function kantan_get_visitor_profile($visitor_id, $days = 90) {
    global $wpdb;
    $analytics   = $wpdb->prefix . 'kantan_analytics';
    $pageviews   = $wpdb->prefix . 'kantan_pageviews';
    $conversions = $wpdb->prefix . 'kantan_conversions';
    $utm         = $wpdb->prefix . 'kantan_utm';

    $visitor_id = sanitize_text_field((string) $visitor_id);
    if ($visitor_id === '') {
        return null;
    }
    $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

    $visitor = $wpdb->get_row($wpdb->prepare(
        "SELECT visitor_id,
            COUNT(DISTINCT session_id) AS total_sessions,
            MIN(visit_date) AS first_seen,
            MAX(visit_date) AS last_seen,
            GROUP_CONCAT(DISTINCT traffic_source) AS sources
        FROM $analytics
        WHERE visitor_id = %s AND visit_date >= %s
        GROUP BY visitor_id",
        $visitor_id, $date
    ));
    if (!$visitor) {
        return null;
    }

    $visitor->display_name  = kantan_get_visitor_display_name($visitor->visitor_id);
    $visitor->is_identified = kantan_visitor_is_identified($visitor->visitor_id);

    // user_agent surfaced for bot/source forensics. No IP stored (see the list
    // query above), so none to expose.
    $visitor->sessions = $wpdb->get_results($wpdb->prepare(
        "SELECT a.session_id, a.traffic_source, a.referrer, a.visit_date,
                LEAST(a.visit_duration, 1800) AS visit_duration, a.is_bounce, a.screen_width,
                a.user_agent,
                u.utm_source, u.utm_medium, u.utm_campaign
        FROM $analytics a
        LEFT JOIN $utm u ON u.session_id = a.session_id
        WHERE a.visitor_id = %s AND a.visit_date >= %s
        ORDER BY a.visit_date DESC
        LIMIT 50",
        $visitor_id, $date
    ));

    foreach ($visitor->sessions as &$session) {
        $session->pages = $wpdb->get_results($wpdb->prepare(
            "SELECT page_url, page_title, sequence_num, scroll_depth,
                    LEAST(time_on_page, 1800) AS time_on_page
            FROM $pageviews
            WHERE session_id = %s
            ORDER BY sequence_num ASC",
            $session->session_id
        ));
        $session->conversions = $wpdb->get_results($wpdb->prepare(
            "SELECT conversion_type, conversion_label, converted_at
            FROM $conversions
            WHERE session_id = %s",
            $session->session_id
        ));
    }
    unset($session);

    return $visitor;
}

/* Get unique visitor count */
function kantan_get_unique_visitors($days = 30) {
    // Delegates through the hybrid helper, which uses the SQLite visitors
    // table when the range extends beyond MySQL retention (the only cheap
    // way to get true DISTINCT counts over long windows).
    return kantan_long_range_unique_visitors($days);
}

/**
 * Active visitors in the last N minutes — drives the Realtime tab and nav badge.
 * Joins each active session to its most recent pageview so the "current page" is
 * the page the visitor is on *now*, not where they landed.
 */
function kantan_get_realtime_visitors($window_minutes = 5) {
    global $wpdb;
    $sessions_table  = $wpdb->prefix . 'kantan_analytics';
    $pageviews_table = $wpdb->prefix . 'kantan_pageviews';
    $window          = max(1, min(60, intval($window_minutes)));

    $sql = "
        SELECT
            a.session_id,
            a.visitor_id,
            a.traffic_source,
            a.referrer,
            a.visit_date,
            a.last_seen,
            TIMESTAMPDIFF(SECOND, a.last_seen, NOW())  AS seconds_since_seen,
            TIMESTAMPDIFF(SECOND, a.visit_date, NOW()) AS session_age_seconds,
            COALESCE(
                (SELECT pv.page_url   FROM $pageviews_table pv WHERE pv.session_id = a.session_id ORDER BY pv.sequence_num DESC LIMIT 1),
                a.page_url
            ) AS current_page,
            COALESCE(
                (SELECT pv.page_title FROM $pageviews_table pv WHERE pv.session_id = a.session_id ORDER BY pv.sequence_num DESC LIMIT 1),
                a.page_title
            ) AS current_page_title,
            (SELECT COUNT(*) FROM $pageviews_table pv WHERE pv.session_id = a.session_id) AS page_count
        FROM $sessions_table a
        WHERE a.last_seen >= DATE_SUB(NOW(), INTERVAL %d MINUTE)
        ORDER BY a.last_seen DESC
        LIMIT 100
    ";

    return $wpdb->get_results($wpdb->prepare($sql, $window));
}

/* ============================================================
 * Conversions — reporting queries for the Conversions tab.
 * Surfaces the data Analytics already collects via the
 * conversion-link tracking configured in Settings.
 * ============================================================ */

/**
 * Total conversions recorded in the period.
 */
function kantan_get_total_conversions($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_conversions';
    $date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
    return (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table WHERE converted_at >= %s",
        $date
    ));
}

/**
 * Conversion counts grouped by type. Returns rows of {conversion_type, total}.
 */
function kantan_get_conversion_summary($days = 30) {
    global $wpdb;
    $table = $wpdb->prefix . 'kantan_conversions';
    $date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT conversion_type, COUNT(*) AS total
         FROM $table
         WHERE converted_at >= %s
         GROUP BY conversion_type
         ORDER BY total DESC",
        $date
    ));
}

/**
 * Conversion counts grouped by the session's traffic source.
 * Returns rows of {traffic_source, total}.
 */
function kantan_get_conversions_by_source($days = 30) {
    global $wpdb;
    $conversions = $wpdb->prefix . 'kantan_conversions';
    $analytics   = $wpdb->prefix . 'kantan_analytics';
    $date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT COALESCE(NULLIF(a.traffic_source, ''), 'unknown') AS traffic_source,
                COUNT(*) AS total
         FROM $conversions c
         LEFT JOIN $analytics a ON c.session_id = a.session_id
         WHERE c.converted_at >= %s
         GROUP BY traffic_source
         ORDER BY total DESC",
        $date
    ));
}

/**
 * Most recent conversions with the session's traffic source attached.
 */
function kantan_get_recent_conversions($days = 30, $limit = 50) {
    global $wpdb;
    $conversions = $wpdb->prefix . 'kantan_conversions';
    $analytics   = $wpdb->prefix . 'kantan_analytics';
    $date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
    return $wpdb->get_results($wpdb->prepare(
        "SELECT c.conversion_type, c.conversion_label, c.conversion_value, c.page_url,
                c.converted_at, c.session_id, c.visitor_id,
                COALESCE(NULLIF(a.traffic_source, ''), 'unknown') AS traffic_source,
                a.referrer
         FROM $conversions c
         LEFT JOIN $analytics a ON c.session_id = a.session_id
         WHERE c.converted_at >= %s
         ORDER BY c.converted_at DESC
         LIMIT %d",
        $date, $limit
    ));
}

/**
 * Humanize a stored conversion_type slug for display.
 * e.g. 'phone_click' -> 'Phone', 'form_submit' -> 'Form Submit'.
 */
function kantan_format_conversion_type($type) {
    $type = preg_replace('/_click$/', '', (string) $type);
    $label = ucwords(str_replace(array('_', '-'), ' ', $type));
    return $label === '' ? __('Other', 'kantan-analytics') : $label;
}
