<?php
/**
 * Kantan Analytics — nightly aggregation
 *
 * Reads yesterday's (and any backlog of un-aggregated) MySQL data and writes:
 *   - daily_aggregates row per date (one insert per day)
 *   - visitors upsert per unique visitor_id seen that day
 *   - conversions_archive row per conversion that day (deduped by mysql_id)
 *
 * This runs independently of the raw-data cleanup, but the cleanup calls it
 * first as a safety net so we never purge MySQL rows that haven't been
 * archived yet.
 *
 * Scheduling: registered on its own daily cron hook,
 * `kantan_analytics_daily_aggregate`, scheduled at plugin activation and on
 * version bumps (see kantan-analytics.php).
 */

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

/**
 * Map screen_width to a device bucket.
 */
function kantan_device_from_width($width) {
    $w = intval($width);
    if ($w <= 0) return null;
    if ($w < 768) return 'mobile';
    if ($w < 1024) return 'tablet';
    if ($w < 1600) return 'laptop';
    return 'desktop';
}

/**
 * Entry point — catches up every missing day from last_aggregated_date+1
 * through yesterday. Self-limits to ~20 seconds of work per invocation so
 * first-run backfill over many months doesn't blow PHP's max execution.
 * The next daily cron firing will continue from where this stopped.
 *
 * @return array { days_processed, last_date, skipped_reason? }
 */
function kantan_analytics_run_aggregation() {
    if (!kantan_sqlite_available()) {
        return array('days_processed' => 0, 'skipped_reason' => 'sqlite_unavailable');
    }
    $db = kantan_sqlite_db();
    if (!$db) {
        return array('days_processed' => 0, 'skipped_reason' => 'sqlite_connect_failed');
    }

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

    // Determine the start date.
    $last_aggregated = kantan_sqlite_get_meta('last_aggregated_date');
    if ($last_aggregated) {
        $start = gmdate('Y-m-d', strtotime($last_aggregated . ' +1 day'));
    } else {
        // First run: backfill from the earliest visit_date in MySQL.
        $earliest = $wpdb->get_var("SELECT DATE(MIN(visit_date)) FROM $analytics_table");
        if (!$earliest) {
            // No data at all — mark as caught up to yesterday and exit.
            kantan_sqlite_set_meta('last_aggregated_date', gmdate('Y-m-d', strtotime('-1 day')));
            return array('days_processed' => 0, 'last_date' => null);
        }
        $start = $earliest;
    }

    $end = gmdate('Y-m-d', strtotime('-1 day')); // never aggregate today — it's still accumulating

    if ($start > $end) {
        return array('days_processed' => 0, 'last_date' => $last_aggregated);
    }

    $time_budget = 20.0; // seconds
    $started = microtime(true);
    $days_processed = 0;
    $last_date = null;

    $current = $start;
    while ($current <= $end) {
        kantan_analytics_aggregate_day($current);
        kantan_sqlite_set_meta('last_aggregated_date', $current);
        $days_processed++;
        $last_date = $current;

        if ((microtime(true) - $started) > $time_budget) {
            break;
        }

        $current = gmdate('Y-m-d', strtotime($current . ' +1 day'));
    }

    return array('days_processed' => $days_processed, 'last_date' => $last_date);
}

/**
 * Aggregate a single date into SQLite. Idempotent — safe to re-run.
 * Visitor counters in SQLite use UPSERT-with-increment semantics, so calling
 * this twice for the same date would double-count. Callers must ensure
 * last_aggregated_date is advanced only once per successful day.
 */
function kantan_analytics_aggregate_day($date) {
    global $wpdb;

    $analytics_table   = $wpdb->prefix . 'kantan_analytics';
    $pageviews_table   = $wpdb->prefix . 'kantan_pageviews';
    $conversions_table = $wpdb->prefix . 'kantan_conversions';

    // --- Per-visitor upsert + new/returning count -----------------------
    $visitor_rows = $wpdb->get_results($wpdb->prepare(
        "SELECT visitor_id, session_id, visit_date, last_seen, traffic_source,
                referrer, page_url, screen_width
         FROM $analytics_table
         WHERE DATE(visit_date) = %s AND visitor_id IS NOT NULL AND visitor_id != ''
         ORDER BY visitor_id ASC, visit_date ASC",
        $date
    ));

    $by_visitor = array();
    foreach ($visitor_rows as $row) {
        $vid = $row->visitor_id;
        if (!isset($by_visitor[$vid])) {
            $by_visitor[$vid] = array(
                'first_session' => $row,
                'last_session'  => $row,
                'sessions'      => array(),
            );
        }
        $by_visitor[$vid]['last_session'] = $row; // rows ordered ASC, so last in wins
        $by_visitor[$vid]['sessions'][$row->session_id] = true;
    }

    $new_visitors = 0;
    $returning_visitors = 0;

    foreach ($by_visitor as $vid => $info) {
        $existing = kantan_sqlite_get_visitor($vid);
        if ($existing) {
            $returning_visitors++;
        } else {
            $new_visitors++;
        }

        $pv_count = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $pageviews_table pv
             INNER JOIN $analytics_table a ON a.session_id = pv.session_id
             WHERE a.visitor_id = %s AND DATE(pv.entered_at) = %s",
            $vid, $date
        )));

        $conv_count = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $conversions_table
             WHERE visitor_id = %s AND DATE(converted_at) = %s",
            $vid, $date
        )));

        kantan_sqlite_upsert_visitor(array(
            'visitor_id'        => $vid,
            'first_seen'        => $existing ? null : $info['first_session']->visit_date,
            'last_seen'         => $info['last_session']->last_seen ?: $info['last_session']->visit_date,
            'session_count'     => count($info['sessions']),
            'pageview_count'    => $pv_count,
            'conversion_count'  => $conv_count,
            'first_source'      => $existing ? null : $info['first_session']->traffic_source,
            'first_referrer'    => $existing ? null : $info['first_session']->referrer,
            'first_landing_url' => $existing ? null : $info['first_session']->page_url,
            'last_source'       => $info['last_session']->traffic_source,
            'device_category'   => kantan_device_from_width($info['last_session']->screen_width),
        ));
    }

    // --- Daily aggregate row ----------------------------------------------
    $totals = $wpdb->get_row($wpdb->prepare(
        "SELECT
            COUNT(DISTINCT session_id) AS sessions,
            COUNT(DISTINCT CASE WHEN visitor_id != '' THEN visitor_id END) AS unique_visitors,
            COALESCE(SUM(is_bounce), 0) AS bounces,
            COALESCE(SUM(LEAST(visit_duration, 1800)), 0) AS total_duration
         FROM $analytics_table
         WHERE DATE(visit_date) = %s",
        $date
    ));

    $sources = $wpdb->get_results($wpdb->prepare(
        "SELECT traffic_source, COUNT(DISTINCT session_id) AS n
         FROM $analytics_table
         WHERE DATE(visit_date) = %s
         GROUP BY traffic_source",
        $date
    ));
    $source_map = array(
        'source_direct' => 0, 'source_organic' => 0,
        'source_social' => 0, 'source_referral' => 0, 'source_ai' => 0,
    );
    foreach ($sources as $s) {
        $key = 'source_' . $s->traffic_source;
        if (isset($source_map[$key])) {
            $source_map[$key] = intval($s->n);
        }
    }

    // Devices — unique session per device bucket.
    $device_rows = $wpdb->get_results($wpdb->prepare(
        "SELECT DISTINCT session_id, screen_width
         FROM $analytics_table
         WHERE DATE(visit_date) = %s",
        $date
    ));
    $device_map = array(
        'device_mobile' => 0, 'device_tablet' => 0,
        'device_laptop' => 0, 'device_desktop' => 0,
    );
    foreach ($device_rows as $d) {
        $cat = kantan_device_from_width($d->screen_width);
        if ($cat) {
            $device_map['device_' . $cat]++;
        }
    }

    $pv_totals = $wpdb->get_row($wpdb->prepare(
        "SELECT COUNT(*) AS pageviews, COALESCE(SUM(scroll_depth), 0) AS total_scroll
         FROM $pageviews_table
         WHERE DATE(entered_at) = %s",
        $date
    ));

    $conversions_count = intval($wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $conversions_table WHERE DATE(converted_at) = %s",
        $date
    )));

    kantan_sqlite_upsert_daily_aggregate($date, array_merge(
        array(
            'sessions'            => intval($totals->sessions ?? 0),
            'unique_visitors'     => intval($totals->unique_visitors ?? 0),
            'new_visitors'        => $new_visitors,
            'returning_visitors'  => $returning_visitors,
            'pageviews'           => intval($pv_totals->pageviews ?? 0),
            'bounces'             => intval($totals->bounces ?? 0),
            'total_duration'      => intval($totals->total_duration ?? 0),
            'total_scroll_depth'  => intval($pv_totals->total_scroll ?? 0),
            'conversions'         => $conversions_count,
        ),
        $source_map,
        $device_map
    ));

    // --- Archive conversions ---------------------------------------------
    $conversions = $wpdb->get_results($wpdb->prepare(
        "SELECT * FROM $conversions_table WHERE DATE(converted_at) = %s",
        $date
    ), ARRAY_A);
    foreach ($conversions as $conv) {
        kantan_sqlite_archive_conversion($conv);
    }
}

add_action('kantan_analytics_daily_aggregate', 'kantan_analytics_run_aggregation');
