<?php
/**
 * Cast Conductor Proprietary License v5
 * SPDX-License-Identifier: LicenseRef-CastConductor-Proprietary-v5
 * 
 * Copyright (c) 2025 CastConductor.com. All Rights Reserved.
 * See LICENSE and EULA-v5.2.md for full terms.
 */

/**
 * CastConductor Analytics Database
 * 
 * Handles database schema creation, table management, and raw SQL queries
 * for the analytics system. Creates all 7 analytics tables on activation.
 * 
 * @package CastConductor
 * @subpackage Analytics
 * @since 5.8.0
 */

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

class CC_Analytics_Database {

    /**
     * Analytics database version for migrations
     */
    const DB_VERSION = '1.4.0';

    /**
     * Option key for tracking database version
     */
    const DB_VERSION_OPTION = 'cc_analytics_db_version';

    /**
     * Table names (without prefix)
     */
    const TABLE_EVENTS        = 'cc_analytics_events';
    const TABLE_SESSIONS      = 'cc_analytics_sessions';
    const TABLE_HOURLY        = 'cc_analytics_hourly';
    const TABLE_DAILY         = 'cc_analytics_daily';
    const TABLE_CONTENT_DAILY = 'cc_analytics_content_daily';
    const TABLE_QR_SCANS      = 'cc_analytics_qr_scans';
    const TABLE_API_KEYS      = 'cc_analytics_api_keys';
    const TABLE_DEVICES       = 'cc_analytics_devices';

    /**
     * @deprecated Use TABLE_CONTENT_DAILY instead
     */
    const TABLE_SPONSOR_DAILY = 'cc_analytics_sponsor_daily';

    /**
     * Single instance
     */
    private static $instance = null;

    /**
     * Get singleton instance
     */
    public static function instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Constructor
     */
    private function __construct() {
        // Check for database updates on admin init
        add_action('admin_init', [$this, 'maybe_upgrade_database']);
    }

    /**
     * Get full table name with prefix
     * 
     * @param string $table Table constant (without prefix)
     * @return string Full table name
     */
    public static function table($table) {
        global $wpdb;
        return $wpdb->prefix . $table;
    }

    /**
     * Create all analytics tables
     * Called during plugin activation
     */
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        // Create all 9 tables
        $this->create_events_table($charset_collate);
        $this->create_sessions_table($charset_collate);
        $this->create_hourly_table($charset_collate);
        $this->create_daily_table($charset_collate);
        $this->create_content_daily_table($charset_collate);
        $this->create_sponsor_daily_table($charset_collate); // Deprecated, kept for migration
        $this->create_qr_scans_table($charset_collate);
        $this->create_api_keys_table($charset_collate);
        $this->create_devices_table($charset_collate);
        
        // Update database version
        update_option(self::DB_VERSION_OPTION, self::DB_VERSION);
        
        // Generate analytics key if not exists
        CC_Analytics_Helpers::get_analytics_key();
        
        error_log('CastConductor Analytics: Database tables created successfully');
    }

    /**
     * Create raw events table (7-day retention by default)
     */
    private function create_events_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_EVENTS);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            event_type VARCHAR(50) NOT NULL,
            session_id VARCHAR(64) NOT NULL,
            device_id_hash VARCHAR(64) NOT NULL,
            timestamp DATETIME NOT NULL,
            properties JSON,
            context JSON,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_session (session_id),
            KEY idx_timestamp (timestamp),
            KEY idx_event_type (event_type),
            KEY idx_device (device_id_hash)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create sessions table (30-day retention by default)
     */
    private function create_sessions_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_SESSIONS);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_id VARCHAR(64) NOT NULL,
            device_id_hash VARCHAR(64) NOT NULL,
            started_at DATETIME NOT NULL,
            ended_at DATETIME,
            last_activity_at DATETIME NOT NULL,
            duration_seconds INT UNSIGNED DEFAULT 0,
            is_restoration BOOLEAN DEFAULT FALSE,
            is_return_viewer BOOLEAN DEFAULT FALSE,
            scene_count INT UNSIGNED DEFAULT 0,
            current_scene_id BIGINT UNSIGNED DEFAULT 0,
            current_scene_name VARCHAR(100),
            country_code CHAR(2),
            city VARCHAR(100),
            dma_code VARCHAR(10),
            zip_code VARCHAR(10),
            timezone VARCHAR(64),
            device_model VARCHAR(100),
            os_version VARCHAR(20),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY session_id (session_id),
            KEY idx_device (device_id_hash),
            KEY idx_started (started_at),
            KEY idx_last_activity (last_activity_at),
            KEY idx_country (country_code),
            KEY idx_city (city),
            KEY idx_dma (dma_code),
            KEY idx_zip (zip_code),
            KEY idx_scene (current_scene_id)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create hourly aggregates table (90-day retention by default)
     */
    private function create_hourly_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_HOURLY);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            hour_start DATETIME NOT NULL,
            metric_type VARCHAR(50) NOT NULL,
            metric_key VARCHAR(100),
            count_value INT UNSIGNED DEFAULT 0,
            sum_value BIGINT UNSIGNED DEFAULT 0,
            avg_value DECIMAL(10,2),
            PRIMARY KEY  (id),
            UNIQUE KEY unique_metric (hour_start, metric_type, metric_key),
            KEY idx_hour (hour_start)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create daily aggregates table (forever retention)
     */
    private function create_daily_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DAILY);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            date DATE NOT NULL,
            metric_type VARCHAR(50) NOT NULL,
            metric_key VARCHAR(100),
            count_value INT UNSIGNED DEFAULT 0,
            sum_value BIGINT UNSIGNED DEFAULT 0,
            avg_value DECIMAL(10,2),
            PRIMARY KEY  (id),
            UNIQUE KEY unique_metric (date, metric_type, metric_key),
            KEY idx_date (date)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create unified content daily metrics table (forever retention)
     * 
     * Stores daily aggregated metrics for ALL content blocks, regardless of type.
     * Replaces the sponsor-specific sponsor_daily table with a type-agnostic design.
     * 
     * @since 5.8.0
     */
    private function create_content_daily_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_CONTENT_DAILY);
        
        // content_block_id = the container content block (e.g., "Sponsors - Lower Third")
        // content_item_id = the individual item within (e.g., sponsor ID 19 for "Cafe y Dulce")
        // If content_item_id is 0, this is a non-rotating content block
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            date DATE NOT NULL,
            content_block_id BIGINT UNSIGNED NOT NULL,
            content_block_type VARCHAR(50) NOT NULL,
            content_item_id BIGINT UNSIGNED DEFAULT 0,
            content_item_title VARCHAR(255),
            impressions INT UNSIGNED DEFAULT 0,
            unique_devices INT UNSIGNED DEFAULT 0,
            qr_scans INT UNSIGNED DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY unique_content_item_date (date, content_block_id, content_item_id),
            KEY idx_content_block (content_block_id),
            KEY idx_content_item (content_item_id),
            KEY idx_content_type (content_block_type),
            KEY idx_date (date)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create sponsor daily metrics table (forever retention)
     * 
     * @deprecated 5.8.0 Use create_content_daily_table() instead
     */
    private function create_sponsor_daily_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_SPONSOR_DAILY);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            date DATE NOT NULL,
            sponsor_id BIGINT UNSIGNED NOT NULL,
            promo_id BIGINT UNSIGNED,
            impressions INT UNSIGNED DEFAULT 0,
            unique_devices INT UNSIGNED DEFAULT 0,
            total_dwell_seconds INT UNSIGNED DEFAULT 0,
            qr_scans INT UNSIGNED DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY unique_sponsor_date (date, sponsor_id, promo_id),
            KEY idx_sponsor (sponsor_id),
            KEY idx_date (date)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create QR scans tracking table
     */
    private function create_qr_scans_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_QR_SCANS);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            scan_id VARCHAR(64) NOT NULL,
            session_id VARCHAR(64),
            promo_id BIGINT UNSIGNED,
            sponsor_id BIGINT UNSIGNED,
            destination_url TEXT,
            utm_source VARCHAR(100),
            utm_medium VARCHAR(100),
            utm_campaign VARCHAR(100),
            utm_content VARCHAR(100),
            scanned_at DATETIME NOT NULL,
            user_agent TEXT,
            referrer TEXT,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_id (scan_id),
            KEY idx_session (session_id),
            KEY idx_promo (promo_id),
            KEY idx_sponsor (sponsor_id),
            KEY idx_scanned (scanned_at)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create partner API keys table
     */
    private function create_api_keys_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_API_KEYS);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            key_hash VARCHAR(64) NOT NULL,
            key_prefix VARCHAR(20) NOT NULL,
            name VARCHAR(100) NOT NULL,
            permissions JSON NOT NULL,
            rate_limit INT UNSIGNED DEFAULT 1000,
            ip_whitelist JSON,
            expires_at DATETIME,
            last_used_at DATETIME,
            created_by BIGINT UNSIGNED,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY key_hash (key_hash),
            KEY idx_prefix (key_prefix),
            KEY idx_expires (expires_at)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Create device registry table for tracking unique devices and return viewers
     * This table tracks lifetime stats per device_id_hash
     */
    private function create_devices_table($charset_collate) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DEVICES);
        
        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            device_id_hash VARCHAR(64) NOT NULL,
            first_seen_at DATETIME NOT NULL,
            last_seen_at DATETIME NOT NULL,
            total_sessions INT UNSIGNED DEFAULT 1,
            total_view_seconds BIGINT UNSIGNED DEFAULT 0,
            total_impressions BIGINT UNSIGNED DEFAULT 0,
            device_model VARCHAR(100),
            country_code CHAR(2),
            city VARCHAR(100),
            timezone VARCHAR(64),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY device_hash (device_id_hash),
            KEY idx_first_seen (first_seen_at),
            KEY idx_last_seen (last_seen_at),
            KEY idx_country (country_code),
            KEY idx_city (city)
        ) {$charset_collate};";
        
        dbDelta($sql);
    }

    /**
     * Check if database needs upgrade
     */
    public function maybe_upgrade_database() {
        $installed_version = get_option(self::DB_VERSION_OPTION, '0.0.0');
        
        if (version_compare($installed_version, self::DB_VERSION, '<')) {
            $this->create_tables();
        }
    }

    /**
     * Drop all analytics tables (for uninstall)
     */
    public function drop_tables() {
        global $wpdb;
        
        $tables = [
            self::TABLE_EVENTS,
            self::TABLE_SESSIONS,
            self::TABLE_HOURLY,
            self::TABLE_DAILY,
            self::TABLE_CONTENT_DAILY,
            self::TABLE_SPONSOR_DAILY, // Deprecated but still drop if exists
            self::TABLE_QR_SCANS,
            self::TABLE_API_KEYS,
            self::TABLE_DEVICES,
        ];
        
        foreach ($tables as $table) {
            $full_table = self::table($table);
            $wpdb->query("DROP TABLE IF EXISTS {$full_table}");
        }
        
        delete_option(self::DB_VERSION_OPTION);
        delete_option('cc_analytics_key');
        delete_option('cc_analytics_retention');
    }

    /**
     * Insert a raw event
     * 
     * @param array $event Event data
     * @return int|false Insert ID or false on failure
     */
    public function insert_event($event) {
        global $wpdb;
        
        $table = self::table(self::TABLE_EVENTS);
        
        $result = $wpdb->insert(
            $table,
            [
                'event_type'     => $event['event_type'],
                'session_id'     => $event['session_id'],
                'device_id_hash' => $event['device_id_hash'],
                'timestamp'      => $event['timestamp'],
                'properties'     => json_encode($event['properties'] ?? []),
                'context'        => json_encode($event['context'] ?? []),
                'created_at'     => CC_Analytics_Helpers::now_utc(),
            ],
            ['%s', '%s', '%s', '%s', '%s', '%s', '%s']
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Insert or update a session
     * 
     * @param array $session Session data
     * @return int|false Insert/update ID or false on failure
     */
    public function upsert_session($session) {
        global $wpdb;
        
        $table = self::table(self::TABLE_SESSIONS);
        
        // Check if session exists
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table} WHERE session_id = %s",
            $session['session_id']
        ));
        
        if ($existing) {
            // Update existing session
            $wpdb->update(
                $table,
                [
                    'ended_at'         => $session['ended_at'] ?? null,
                    'duration_seconds' => $session['duration_seconds'] ?? 0,
                    'scene_count'      => $session['scene_count'] ?? 0,
                    'last_activity_at' => CC_Analytics_Helpers::now_utc(),
                ],
                ['session_id' => $session['session_id']],
                ['%s', '%d', '%d', '%s'],
                ['%s']
            );
            return $existing;
        }
        
        // Check if this device is a return viewer
        $device_id_hash = $session['device_id_hash'];
        $is_return_viewer = $this->is_return_viewer($device_id_hash);
        
        // Register/update device in registry
        $this->update_device_registry($device_id_hash, $session);
        
        $now_utc = CC_Analytics_Helpers::now_utc();
        
        // Insert new session
        $result = $wpdb->insert(
            $table,
            [
                'session_id'       => $session['session_id'],
                'device_id_hash'   => $device_id_hash,
                'started_at'       => $session['started_at'],
                'ended_at'         => $session['ended_at'] ?? null,
                'last_activity_at' => $now_utc,
                'duration_seconds' => $session['duration_seconds'] ?? 0,
                'is_restoration'   => $session['is_restoration'] ?? false,
                'is_return_viewer' => $is_return_viewer,
                'scene_count'      => $session['scene_count'] ?? 0,
                'country_code'     => $session['country_code'] ?? null,
                'city'             => $session['city'] ?? null,
                'dma_code'         => $session['dma_code'] ?? null,
                'zip_code'         => $session['zip_code'] ?? null,
                'timezone'         => $session['timezone'] ?? null,
                'device_model'     => $session['device_model'] ?? null,
                'os_version'       => $session['os_version'] ?? null,
                'created_at'       => $now_utc,
            ],
            ['%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Check if a device is a return viewer (has prior sessions)
     * 
     * @param string $device_id_hash Device ID hash
     * @return bool True if device has been seen before
     */
    public function is_return_viewer($device_id_hash) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DEVICES);
        
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$table} WHERE device_id_hash = %s LIMIT 1",
            $device_id_hash
        ));
        
        return (bool) $exists;
    }

    /**
     * Register or update a device in the registry
     * 
     * @param string $device_id_hash Device ID hash
     * @param array $session Session data with device info
     * @return int|false Device ID or false on failure
     */
    public function update_device_registry($device_id_hash, $session) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DEVICES);
        $now_utc = CC_Analytics_Helpers::now_utc();
        
        // Check if device exists
        $existing = $wpdb->get_row($wpdb->prepare(
            "SELECT id, total_sessions FROM {$table} WHERE device_id_hash = %s",
            $device_id_hash
        ), ARRAY_A);
        
        if ($existing) {
            // Update existing device - increment session count and update last_seen
            $wpdb->update(
                $table,
                [
                    'last_seen_at'    => $now_utc,
                    'total_sessions'  => (int) $existing['total_sessions'] + 1,
                    'device_model'    => $session['device_model'] ?? null,
                    'country_code'    => $session['country_code'] ?? null,
                    'city'            => $session['city'] ?? null,
                    'timezone'        => $session['timezone'] ?? null,
                ],
                ['id' => $existing['id']],
                ['%s', '%d', '%s', '%s', '%s', '%s'],
                ['%d']
            );
            return $existing['id'];
        }
        
        // Insert new device
        $result = $wpdb->insert(
            $table,
            [
                'device_id_hash'    => $device_id_hash,
                'first_seen_at'     => $now_utc,
                'last_seen_at'      => $now_utc,
                'total_sessions'    => 1,
                'total_view_seconds'=> 0,
                'total_impressions' => 0,
                'device_model'      => $session['device_model'] ?? null,
                'country_code'      => $session['country_code'] ?? null,
                'city'              => $session['city'] ?? null,
                'timezone'          => $session['timezone'] ?? null,
                'created_at'        => $now_utc,
            ],
            ['%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s']
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Update device lifetime stats (called when session ends)
     * 
     * @param string $device_id_hash Device ID hash
     * @param int $view_seconds Session duration in seconds
     * @param int $impressions Number of impressions in session
     */
    public function update_device_lifetime_stats($device_id_hash, $view_seconds, $impressions = 0) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DEVICES);
        
        $wpdb->query($wpdb->prepare(
            "UPDATE {$table} 
             SET total_view_seconds = total_view_seconds + %d,
                 total_impressions = total_impressions + %d,
                 last_seen_at = %s
             WHERE device_id_hash = %s",
            $view_seconds,
            $impressions,
            CC_Analytics_Helpers::now_utc(),
            $device_id_hash
        ));
    }

    /**
     * Update session last_activity_at timestamp
     * Called with every incoming event to keep session "alive"
     * 
     * @param string $session_id Session ID
     */
    public function touch_session($session_id) {
        global $wpdb;
        
        $table = self::table(self::TABLE_SESSIONS);
        
        $wpdb->update(
            $table,
            ['last_activity_at' => CC_Analytics_Helpers::now_utc()],
            ['session_id' => $session_id],
            ['%s'],
            ['%s']
        );
    }

    /**
     * Get device lifetime stats
     * 
     * @param string $device_id_hash Device ID hash
     * @return array|null Device stats or null if not found
     */
    public function get_device_stats($device_id_hash) {
        global $wpdb;
        
        $table = self::table(self::TABLE_DEVICES);
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE device_id_hash = %s",
            $device_id_hash
        ), ARRAY_A);
    }

    /**
     * Get session summary for a date range
     * 
     * @param string $start_date Start date (Y-m-d)
     * @param string $end_date End date (Y-m-d)
     * @return array Summary data
     */
    public function get_session_summary($start_date, $end_date) {
        global $wpdb;
        
        $table = self::table(self::TABLE_SESSIONS);
        
        // Calculate avg_duration using:
        // - duration_seconds for ended sessions
        // - TIMESTAMPDIFF for still-active sessions (with 30 min timeout)
        return $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(*) as total_sessions,
                COUNT(DISTINCT device_id_hash) as unique_devices,
                AVG(
                    CASE 
                        WHEN ended_at IS NOT NULL THEN duration_seconds
                        WHEN started_at > DATE_SUB(NOW(), INTERVAL 30 MINUTE) THEN TIMESTAMPDIFF(SECOND, started_at, NOW())
                        ELSE 1800  -- Cap stale sessions at 30 minutes
                    END
                ) as avg_duration,
                SUM(scene_count) as total_scene_views
            FROM {$table}
            WHERE started_at BETWEEN %s AND %s
            AND is_restoration = 0",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
    }

    /**
     * Get daily metrics for charting
     * 
     * @param string $start_date Start date (Y-m-d)
     * @param string $end_date End date (Y-m-d)
     * @param string $metric_type Metric type to retrieve
     * @return array Daily metric values
     */
    public function get_daily_metrics($start_date, $end_date, $metric_type = 'sessions') {
        global $wpdb;
        
        $table = self::table(self::TABLE_DAILY);
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT date, count_value, sum_value, avg_value
            FROM {$table}
            WHERE date BETWEEN %s AND %s
            AND metric_type = %s
            ORDER BY date ASC",
            $start_date,
            $end_date,
            $metric_type
        ), ARRAY_A);
    }

    /**
     * Close stale sessions (no activity for 30+ minutes)
     * 
     * Sessions without an ended_at that haven't had activity in 30+ minutes
     * are assumed to be abandoned (app closed without sending session_end).
     * Uses last_activity_at to detect staleness, not started_at.
     * This allows long-running sessions (hours) that keep sending events.
     * 
     * @return int Number of sessions closed
     */
    public function close_stale_sessions() {
        global $wpdb;
        
        $table = self::table(self::TABLE_SESSIONS);
        $devices_table = self::table(self::TABLE_DEVICES);
        
        // First, get sessions we're about to close so we can update device lifetime stats
        $stale_sessions = $wpdb->get_results(
            "SELECT session_id, device_id_hash, 
                    TIMESTAMPDIFF(SECOND, started_at, last_activity_at) as duration
             FROM {$table}
             WHERE ended_at IS NULL
             AND last_activity_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)",
            ARRAY_A
        );
        
        // Update device lifetime view seconds for each closing session
        foreach ($stale_sessions as $session) {
            if (!empty($session['device_id_hash']) && $session['duration'] > 0) {
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$devices_table} 
                     SET total_view_seconds = total_view_seconds + %d
                     WHERE device_id_hash = %s",
                    $session['duration'],
                    $session['device_id_hash']
                ));
            }
        }
        
        // Close sessions with no activity in 30+ minutes
        // Set ended_at to last_activity_at and calculate actual duration
        return $wpdb->query(
            "UPDATE {$table}
            SET 
                ended_at = last_activity_at,
                duration_seconds = TIMESTAMPDIFF(SECOND, started_at, last_activity_at)
            WHERE ended_at IS NULL
            AND last_activity_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)"
        );
    }

    /**
     * Prune old data based on retention settings
     * 
     * @return array Number of rows deleted per table
     */
    public function prune_old_data() {
        global $wpdb;
        
        $results = [];
        
        // First, close any stale sessions (no activity for 30+ minutes)
        $results['stale_closed'] = $this->close_stale_sessions();
        
        // Prune raw events
        $days = CC_Analytics_Helpers::get_retention_days('raw_events');
        if ($days > 0) {
            $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));
            $table = self::table(self::TABLE_EVENTS);
            $results['events'] = $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE timestamp < %s",
                $cutoff
            ));
        }
        
        // Prune sessions
        $days = CC_Analytics_Helpers::get_retention_days('sessions');
        if ($days > 0) {
            $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));
            $table = self::table(self::TABLE_SESSIONS);
            $results['sessions'] = $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE started_at < %s",
                $cutoff
            ));
        }
        
        // Prune hourly aggregates
        $days = CC_Analytics_Helpers::get_retention_days('hourly');
        if ($days > 0) {
            $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));
            $table = self::table(self::TABLE_HOURLY);
            $results['hourly'] = $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE hour_start < %s",
                $cutoff
            ));
        }
        
        return $results;
    }
}
