<?php
/**
 * Kantan Analytics — long-range query helpers
 *
 * For any dashboard request whose date range exceeds the MySQL raw-data
 * retention window, the canonical analytics functions split the query:
 *   - historical portion  → SQLite daily_aggregates (indefinite retention)
 *   - recent portion      → MySQL (up to retention_days ago, current detail)
 *
 * These helpers return normalized day-by-day rows with consistent shape so
 * callers can merge and sum without worrying about which store a day came
 * from.
 */

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

/**
 * Decide how to split a $days request across SQLite + MySQL.
 *
 * Returns an array:
 *   needs_hybrid : true iff any portion of the range falls outside MySQL retention
 *   mysql_days   : how many recent days MySQL can answer (<= retention, <= $days)
 *   sqlite_start : YYYY-MM-DD of SQLite range start (inclusive), or null
 *   sqlite_end   : YYYY-MM-DD of SQLite range end (inclusive), or null
 *   retention    : configured raw-data retention in days
 */
function kantan_long_range_bounds($days) {
    $retention = intval(get_option('kantan_retention_raw_days', 90));
    if ($retention < 7) $retention = 90;

    $days = intval($days);
    if ($days <= $retention) {
        return array(
            'needs_hybrid' => false,
            'mysql_days'   => $days,
            'sqlite_start' => null,
            'sqlite_end'   => null,
            'retention'    => $retention,
        );
    }

    return array(
        'needs_hybrid' => kantan_sqlite_available(),
        'mysql_days'   => $retention,
        'sqlite_start' => gmdate('Y-m-d', strtotime("-{$days} days")),
        'sqlite_end'   => gmdate('Y-m-d', strtotime("-" . ($retention + 1) . " days")),
        'retention'    => $retention,
    );
}

/**
 * Merge MySQL daily rows (objects with `date` and `visits`) and SQLite
 * daily rows (assoc arrays from daily_aggregates) into a single
 * time-ordered list of `{ date, visits }` objects.
 *
 * Used by kantan_get_visits_by_day() and as a building block for
 * aggregates (total / bounce / duration) so we only pay for one query set.
 */
function kantan_long_range_visits_by_day($days) {
    global $wpdb;
    $bounds = kantan_long_range_bounds($days);
    $table = $wpdb->prefix . 'kantan_analytics';

    $mysql_start = gmdate('Y-m-d', strtotime("-{$bounds['mysql_days']} days"));

    $mysql_rows = $wpdb->get_results($wpdb->prepare(
        "SELECT DATE(visit_date) as date, COUNT(DISTINCT session_id) as visits
         FROM $table
         WHERE visit_date >= %s
         GROUP BY DATE(visit_date)
         ORDER BY date ASC",
        $mysql_start
    ));

    if (!$bounds['needs_hybrid']) {
        return $mysql_rows;
    }

    $sqlite_rows = kantan_sqlite_get_aggregates($bounds['sqlite_start'], $bounds['sqlite_end']);

    $merged = array();
    foreach ($sqlite_rows as $row) {
        $merged[] = (object) array(
            'date'   => $row['date'],
            'visits' => intval($row['sessions']),
        );
    }
    foreach ($mysql_rows as $row) {
        $merged[] = $row;
    }
    return $merged;
}

/**
 * Return a combined per-day rows array including all fields needed by
 * aggregate computations (sessions, bounces, duration, pageviews, etc.).
 * MySQL rows are computed on the fly; SQLite rows come from daily_aggregates.
 *
 * This is the "do-everything" hybrid source — callers pick the fields they
 * need. Using it means one DB round-trip per store instead of N.
 */
function kantan_long_range_daily_metrics($days) {
    global $wpdb;
    $bounds = kantan_long_range_bounds($days);
    $analytics_table = $wpdb->prefix . 'kantan_analytics';
    $pv_table        = $wpdb->prefix . 'kantan_pageviews';

    $mysql_start = gmdate('Y-m-d', strtotime("-{$bounds['mysql_days']} days"));

    $mysql = $wpdb->get_results($wpdb->prepare(
        "SELECT DATE(visit_date) AS date,
                COUNT(DISTINCT session_id) AS sessions,
                COALESCE(SUM(is_bounce), 0) AS bounces,
                COALESCE(SUM(LEAST(visit_duration, 1800)), 0) AS total_duration,
                SUM(CASE WHEN traffic_source='direct'   THEN 1 ELSE 0 END) AS source_direct,
                SUM(CASE WHEN traffic_source='organic'  THEN 1 ELSE 0 END) AS source_organic,
                SUM(CASE WHEN traffic_source='social'   THEN 1 ELSE 0 END) AS source_social,
                SUM(CASE WHEN traffic_source='referral' THEN 1 ELSE 0 END) AS source_referral,
                SUM(CASE WHEN traffic_source='ai'       THEN 1 ELSE 0 END) AS source_ai
         FROM $analytics_table
         WHERE visit_date >= %s
         GROUP BY DATE(visit_date)
         ORDER BY date ASC",
        $mysql_start
    ), ARRAY_A);

    $pv_by_day = $wpdb->get_results($wpdb->prepare(
        "SELECT DATE(entered_at) AS date,
                COUNT(*) AS pageviews,
                COALESCE(SUM(scroll_depth), 0) AS total_scroll_depth
         FROM $pv_table
         WHERE entered_at >= %s
         GROUP BY DATE(entered_at)",
        $mysql_start
    ), ARRAY_A);

    $pv_map = array();
    foreach ($pv_by_day as $r) {
        $pv_map[$r['date']] = $r;
    }

    $combined = array();

    if ($bounds['needs_hybrid']) {
        $sqlite_rows = kantan_sqlite_get_aggregates($bounds['sqlite_start'], $bounds['sqlite_end']);
        foreach ($sqlite_rows as $row) {
            $combined[] = array(
                'date'               => $row['date'],
                'sessions'           => intval($row['sessions']),
                'bounces'            => intval($row['bounces']),
                'total_duration'     => intval($row['total_duration']),
                'pageviews'          => intval($row['pageviews']),
                'total_scroll_depth' => intval($row['total_scroll_depth']),
                'source_direct'      => intval($row['source_direct']),
                'source_organic'     => intval($row['source_organic']),
                'source_social'      => intval($row['source_social']),
                'source_referral'    => intval($row['source_referral']),
                'source_ai'          => intval($row['source_ai'] ?? 0),
                'device_mobile'      => intval($row['device_mobile']),
                'device_tablet'      => intval($row['device_tablet']),
                'device_laptop'      => intval($row['device_laptop']),
                'device_desktop'     => intval($row['device_desktop']),
                'new_visitors'       => intval($row['new_visitors']),
                'returning_visitors' => intval($row['returning_visitors']),
                'unique_visitors'    => intval($row['unique_visitors']),
                'conversions'        => intval($row['conversions']),
                'source'             => 'sqlite',
            );
        }
    }

    foreach ($mysql as $row) {
        $pv_row = $pv_map[$row['date']] ?? array('pageviews' => 0, 'total_scroll_depth' => 0);
        $combined[] = array(
            'date'               => $row['date'],
            'sessions'           => intval($row['sessions']),
            'bounces'            => intval($row['bounces']),
            'total_duration'     => intval($row['total_duration']),
            'pageviews'          => intval($pv_row['pageviews']),
            'total_scroll_depth' => intval($pv_row['total_scroll_depth']),
            'source_direct'      => intval($row['source_direct']),
            'source_organic'     => intval($row['source_organic']),
            'source_social'      => intval($row['source_social']),
            'source_referral'    => intval($row['source_referral']),
            'source_ai'          => intval($row['source_ai'] ?? 0),
            // Per-day device counts aren't available from MySQL cheaply, so
            // callers needing device breakdown for the MySQL portion should
            // still run the existing kantan_get_device_breakdown() query and
            // accept that dashboards spanning retention boundaries combine
            // a MySQL total with per-day SQLite detail.
            'device_mobile'      => 0,
            'device_tablet'      => 0,
            'device_laptop'      => 0,
            'device_desktop'     => 0,
            'new_visitors'       => 0,
            'returning_visitors' => 0,
            'unique_visitors'    => 0,
            'conversions'        => 0,
            'source'             => 'mysql',
        );
    }

    return $combined;
}

/**
 * Total unique visitors across an arbitrary range.
 *
 * For ranges within MySQL retention, delegates to a DISTINCT visitor_id count
 * over wp_kantan_analytics. For longer ranges, uses the SQLite visitors table
 * filtered by last_seen — this is actually the only place where true DISTINCT
 * is cheap beyond 90 days since we keep one row per visitor indefinitely.
 */
function kantan_long_range_unique_visitors($days) {
    global $wpdb;
    $bounds = kantan_long_range_bounds($days);

    if (!$bounds['needs_hybrid']) {
        $table = $wpdb->prefix . 'kantan_analytics';
        $date  = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT visitor_id) FROM $table
             WHERE visit_date >= %s AND visitor_id != ''",
            $date
        )));
    }

    $db = kantan_sqlite_db();
    if (!$db) return 0;

    $start = gmdate('Y-m-d 00:00:00', strtotime("-{$days} days"));
    $end   = current_time('mysql');
    $stmt = $db->prepare("SELECT COUNT(*) AS c FROM visitors
        WHERE last_seen >= :start AND last_seen <= :end");
    $stmt->bindValue(':start', $start, SQLITE3_TEXT);
    $stmt->bindValue(':end',   $end,   SQLITE3_TEXT);
    $row = $stmt->execute()->fetchArray(SQLITE3_ASSOC);
    return intval($row['c'] ?? 0);
}
