<?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 REST API
 * 
 * REST API endpoints for analytics ingestion from Roku devices
 * and dashboard data retrieval for the admin interface.
 * 
 * @package CastConductor
 * @subpackage Analytics
 * @since 5.8.0
 */

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

class CC_Analytics_API {

    /**
     * API namespace
     */
    const NAMESPACE = 'castconductor/v5';

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

    /**
     * Ingestion handler
     */
    private $ingestion;

    /**
     * Aggregation handler
     */
    private $aggregation;

    /**
     * Database handler
     */
    private $db;

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

    /**
     * Constructor
     */
    private function __construct() {
        $this->db = CC_Analytics_Database::instance();
        $this->ingestion = CC_Analytics_Ingestion::instance();
        $this->aggregation = CC_Analytics_Aggregation::instance();
        
        add_action('rest_api_init', [$this, 'register_routes']);
    }

    /**
     * Register all analytics REST routes
     */
    public function register_routes() {
        // Event ingestion endpoint (from Roku)
        register_rest_route(self::NAMESPACE, '/analytics/ingest', [
            'methods'             => 'POST',
            'callback'            => [$this, 'ingest_events'],
            'permission_callback' => [$this, 'validate_analytics_key'],
        ]);
        
        // Dashboard summary endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/summary', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_summary'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => $this->get_period_args(),
        ]);
        
        // Session list endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/sessions', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_sessions'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => $this->get_period_args(),
        ]);
        
        // Content performance endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/content', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_content_performance'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => array_merge($this->get_period_args(), [
                'block_types' => [
                    'type'        => 'string',
                    'default'     => '',
                    'description' => 'Comma-separated list of block types to include (e.g., promo,sponsor). Empty = all',
                ],
                'exclude_types' => [
                    'type'        => 'string',
                    'default'     => '',
                    'description' => 'Comma-separated list of block types to exclude (e.g., track_info_hero)',
                ],
            ]),
        ]);
        
        // Device breakdown endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/devices', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_device_breakdown'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => $this->get_period_args(),
        ]);
        
        // Geographic data endpoint (admin, Pro+)
        register_rest_route(self::NAMESPACE, '/analytics/geo', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_geographic_data'],
            'permission_callback' => [$this, 'validate_geo_access'],
            'args'                => $this->get_period_args(),
        ]);
        
        // Sponsor reports endpoint (admin, Business+)
        register_rest_route(self::NAMESPACE, '/analytics/sponsors', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_sponsor_reports'],
            'permission_callback' => [$this, 'validate_sponsor_access'],
            'args'                => $this->get_period_args(),
        ]);
        
        // Manual aggregation trigger (admin)
        register_rest_route(self::NAMESPACE, '/analytics/aggregate', [
            'methods'             => 'POST',
            'callback'            => [$this, 'trigger_aggregation'],
            'permission_callback' => [$this, 'validate_admin_access'],
        ]);
        
        // CSV export endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/export/csv', [
            'methods'             => 'GET',
            'callback'            => [$this, 'export_csv'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => array_merge($this->get_period_args(), [
                'type' => [
                    'type'        => 'string',
                    'default'     => 'summary',
                    'enum'        => ['summary', 'sessions', 'content', 'devices', 'geo', 'sponsors'],
                ],
            ]),
        ]);
        
        // Settings endpoint (admin)
        register_rest_route(self::NAMESPACE, '/analytics/settings', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_settings'],
            'permission_callback' => [$this, 'validate_admin_access'],
        ]);
        
        register_rest_route(self::NAMESPACE, '/analytics/settings', [
            'methods'             => 'POST',
            'callback'            => [$this, 'update_settings'],
            'permission_callback' => [$this, 'validate_admin_access'],
        ]);
        
        // Feature availability endpoint (for dashboard UI)
        register_rest_route(self::NAMESPACE, '/analytics/features', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_available_features'],
            'permission_callback' => [$this, 'validate_admin_access'],
        ]);
        
        // Active sessions endpoint - real-time active users (admin)
        register_rest_route(self::NAMESPACE, '/analytics/active', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_active_sessions'],
            'permission_callback' => [$this, 'validate_admin_access'],
        ]);
        
        // Unified dashboard endpoint - all data in one call (admin)
        register_rest_route(self::NAMESPACE, '/analytics/dashboard', [
            'methods'             => 'GET',
            'callback'            => [$this, 'get_dashboard_data'],
            'permission_callback' => [$this, 'validate_admin_access'],
            'args'                => $this->get_period_args(),
        ]);
    }

    /**
     * Get common period arguments for date-range endpoints
     */
    private function get_period_args() {
        return [
            'period' => [
                'type'        => 'string',
                'default'     => '7d',
                'enum'        => ['7d', '30d', '90d', 'custom'],
                'description' => 'Time period for data',
            ],
            'start_date' => [
                'type'              => 'string',
                'format'            => 'date',
                'description'       => 'Start date for custom period (Y-m-d)',
                'sanitize_callback' => 'sanitize_text_field',
            ],
            'end_date' => [
                'type'              => 'string',
                'format'            => 'date',
                'description'       => 'End date for custom period (Y-m-d)',
                'sanitize_callback' => 'sanitize_text_field',
            ],
        ];
    }

    /**
     * Parse period parameters into date range
     * 
     * @param WP_REST_Request $request
     * @return array [start_date, end_date]
     */
    private function parse_period($request) {
        $period = $request->get_param('period') ?? '7d';
        
        if ($period === 'custom') {
            $start = $request->get_param('start_date');
            $end = $request->get_param('end_date');
            
            if (!$start || !$end) {
                $start = date('Y-m-d', strtotime('-7 days'));
                $end = date('Y-m-d');
            }
            
            // Extend end date by 1 day to catch UTC timestamps that are "tomorrow" in UTC
            // but still "today" in local timezone (e.g., 11 PM PST = next day UTC)
            $end = date('Y-m-d', strtotime($end . ' +1 day'));
            
            return [$start, $end];
        }
        
        $days = [
            '7d'  => 7,
            '30d' => 30,
            '90d' => 90,
        ];
        
        $day_count = $days[$period] ?? 7;
        
        // Extend end date by 1 day to catch UTC timestamps that are "tomorrow" in UTC
        // but still "today" in local timezone (e.g., 11 PM PST = next day UTC)
        return [
            date('Y-m-d', strtotime("-{$day_count} days")),
            date('Y-m-d', strtotime('+1 day')),
        ];
    }

    // =========================================================================
    // Permission Callbacks
    // =========================================================================

    /**
     * Validate analytics key for Roku ingestion
     */
    public function validate_analytics_key($request) {
        $auth_header = $request->get_header('Authorization');
        
        if (!$auth_header || !preg_match('/^Bearer\s+(.+)$/', $auth_header, $matches)) {
            return new WP_Error(
                'unauthorized',
                'Missing or invalid authorization header',
                ['status' => 401]
            );
        }
        
        $provided_key = $matches[1];
        $stored_key = CC_Analytics_Helpers::get_analytics_key();
        
        if (!hash_equals($stored_key, $provided_key)) {
            return new WP_Error(
                'forbidden',
                'Invalid analytics key',
                ['status' => 403]
            );
        }
        
        return true;
    }

    /**
     * Validate admin access for dashboard endpoints
     */
    public function validate_admin_access($request) {
        return current_user_can('manage_options');
    }

    /**
     * Validate access to geographic features (Pro+)
     */
    public function validate_geo_access($request) {
        if (!current_user_can('manage_options')) {
            return false;
        }
        
        if (!CC_Analytics_Helpers::has_feature('geo_heatmap')) {
            return new WP_Error(
                'feature_locked',
                'Geographic analytics requires Pro plan or higher',
                ['status' => 403, 'upgrade' => CC_Analytics_Helpers::get_upgrade_prompt('geo_heatmap')]
            );
        }
        
        return true;
    }

    /**
     * Validate access to sponsor reports (Business+)
     */
    public function validate_sponsor_access($request) {
        if (!current_user_can('manage_options')) {
            return false;
        }
        
        if (!CC_Analytics_Helpers::has_feature('sponsor_reports')) {
            return new WP_Error(
                'feature_locked',
                'Sponsor reports require Business plan or higher',
                ['status' => 403, 'upgrade' => CC_Analytics_Helpers::get_upgrade_prompt('sponsor_reports')]
            );
        }
        
        return true;
    }

    // =========================================================================
    // Endpoint Handlers
    // =========================================================================

    /**
     * Handle event ingestion from Roku devices
     */
    public function ingest_events($request) {
        $body = $request->get_json_params();
        
        // Get device hash from first event or header
        $device_hash = null;
        if (!empty($body['events'][0]['device_id_hash'])) {
            $device_hash = sanitize_text_field($body['events'][0]['device_id_hash']);
        }
        
        if (!$device_hash) {
            return new WP_Error(
                'missing_device',
                'Device ID hash is required',
                ['status' => 400]
            );
        }
        
        $result = $this->ingestion->process_batch($body, $device_hash);
        
        if (is_wp_error($result)) {
            return $result;
        }
        
        return rest_ensure_response($result);
    }

    /**
     * Get dashboard summary
     */
    public function get_summary($request) {
        list($start_date, $end_date) = $this->parse_period($request);
        
        $summary = $this->db->get_session_summary($start_date, $end_date);
        
        // Get daily trend data for charts
        $daily_sessions = $this->db->get_daily_metrics($start_date, $end_date, 'session_count');
        $daily_devices = $this->db->get_daily_metrics($start_date, $end_date, 'unique_devices');
        
        return rest_ensure_response([
            'period'  => [
                'start' => $start_date,
                'end'   => $end_date,
            ],
            'summary' => [
                'total_sessions'   => (int) ($summary['total_sessions'] ?? 0),
                'unique_devices'   => (int) ($summary['unique_devices'] ?? 0),
                'avg_duration'     => round((float) ($summary['avg_duration'] ?? 0), 1),
                'total_scene_views'=> (int) ($summary['total_scene_views'] ?? 0),
            ],
            'trends' => [
                'sessions' => $daily_sessions,
                'devices'  => $daily_devices,
            ],
            'features' => CC_Analytics_Helpers::get_available_features(),
        ]);
    }

    /**
     * Get sessions list
     */
    public function get_sessions($request) {
        global $wpdb;
        
        list($start_date, $end_date) = $this->parse_period($request);
        
        $table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        
        $sessions = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                session_id,
                device_id_hash,
                started_at,
                ended_at,
                duration_seconds,
                scene_count,
                device_model,
                country_code,
                city,
                zip_code,
                timezone
            FROM {$table}
            WHERE started_at BETWEEN %s AND %s
            AND is_restoration = 0
            ORDER BY started_at DESC
            LIMIT 100",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        return rest_ensure_response([
            'period'   => ['start' => $start_date, 'end' => $end_date],
            'sessions' => $sessions,
            'count'    => count($sessions),
        ]);
    }

    /**
     * Get content performance data
     */
    public function get_content_performance($request) {
        global $wpdb;
        
        list($start_date, $end_date) = $this->parse_period($request);
        
        // Parse filter parameters
        $block_types = array_filter(explode(',', $request->get_param('block_types') ?? ''));
        $exclude_types = array_filter(explode(',', $request->get_param('exclude_types') ?? ''));
        
        $content_daily_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_CONTENT_DAILY);
        $daily_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_DAILY);
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        
        // Build WHERE clause for type filtering
        $type_filter_agg = '';
        $type_filter_raw = '';
        $type_params = [];
        
        if (!empty($block_types)) {
            $placeholders = implode(',', array_fill(0, count($block_types), '%s'));
            $type_filter_agg .= " AND content_block_type IN ($placeholders)";
            $type_filter_raw .= " AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) IN ($placeholders)";
            $type_params = array_merge($type_params, $block_types);
        }
        
        if (!empty($exclude_types)) {
            $placeholders = implode(',', array_fill(0, count($exclude_types), '%s'));
            $type_filter_agg .= " AND content_block_type NOT IN ($placeholders)";
            $type_filter_raw .= " AND (JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) NOT IN ($placeholders) OR JSON_EXTRACT(properties, '$.content_block_type') IS NULL)";
            $type_params = array_merge($type_params, $exclude_types);
        }
        
        // First try aggregated content_daily table (faster for larger datasets)
        // Groups by content_item_id to show individual sponsors/promos within containers
        $agg_query = "SELECT 
                CASE WHEN content_item_id > 0 THEN content_item_id ELSE content_block_id END as content_id,
                content_item_title as content_name,
                content_block_type as content_type,
                SUM(impressions) as total_impressions,
                SUM(unique_devices) as unique_viewers,
                SUM(qr_scans) as total_qr_scans
            FROM {$content_daily_table}
            WHERE date BETWEEN %s AND %s
            {$type_filter_agg}
            GROUP BY content_id, content_item_title, content_block_type
            ORDER BY total_impressions DESC
            LIMIT 50";
        
        $all_params = array_merge([$start_date, $end_date], $type_params);
        $top_content = $wpdb->get_results($wpdb->prepare($agg_query, ...$all_params), ARRAY_A);
        
        // Fallback: Query raw events if aggregated table is empty
        // This handles real-time data before daily aggregation runs
        // Group by content_item_id for individual sponsor/promo/shoutout tracking
        if (empty($top_content)) {
            $raw_query = "SELECT 
                    COALESCE(
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_item_id')), '0'),
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.sponsor_id')), '0'),
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.promo_id')), '0'),
                        JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_id')),
                        '0'
                    ) as content_id,
                    COALESCE(
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_item_title')), ''),
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_title')), ''),
                        NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')), ''),
                        'Unknown'
                    ) as content_name,
                    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')), ''), 'unknown') as content_type,
                    SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as total_impressions,
                    COUNT(DISTINCT device_id_hash) as unique_viewers,
                    0 as total_qr_scans
                FROM {$events_table}
                WHERE event_type IN ('container_impression', 'promo_impression')
                AND DATE(timestamp) BETWEEN %s AND %s
                {$type_filter_raw}
                GROUP BY content_id, content_type, content_name
                ORDER BY total_impressions DESC
                LIMIT 50";
            
            $top_content = $wpdb->get_results($wpdb->prepare($raw_query, ...$all_params), ARRAY_A);
        }
        
        // Get available content block types for filter UI (from raw events for real-time)
        $available_types = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) as block_type
            FROM {$events_table}
            WHERE event_type IN ('container_impression', 'promo_impression')
            AND DATE(timestamp) BETWEEN %s AND %s
            AND JSON_EXTRACT(properties, '$.content_block_type') IS NOT NULL
            ORDER BY block_type",
            $start_date,
            $end_date
        ));
        
        // Get top scenes
        $top_scenes = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                metric_key as scene_id,
                SUM(count_value) as total_views
            FROM {$daily_table}
            WHERE date BETWEEN %s AND %s
            AND metric_type = 'scene_views'
            GROUP BY metric_key
            ORDER BY total_views DESC
            LIMIT 20",
            $start_date,
            $end_date
        ), ARRAY_A);
        
        return rest_ensure_response([
            'period'          => ['start' => $start_date, 'end' => $end_date],
            'top_content'     => $top_content,
            'top_scenes'      => $top_scenes,
            'available_types' => array_filter($available_types),
            'applied_filters' => [
                'include' => $block_types,
                'exclude' => $exclude_types,
            ],
        ]);
    }

    /**
     * Get device breakdown
     */
    public function get_device_breakdown($request) {
        global $wpdb;
        
        list($start_date, $end_date) = $this->parse_period($request);
        
        $sessions_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        
        $devices = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                device_model,
                COUNT(*) as session_count,
                COUNT(DISTINCT device_id_hash) as unique_devices,
                AVG(duration_seconds) as avg_duration
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND device_model IS NOT NULL
            AND is_restoration = 0
            GROUP BY device_model
            ORDER BY session_count DESC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        return rest_ensure_response([
            'period'  => ['start' => $start_date, 'end' => $end_date],
            'devices' => $devices,
        ]);
    }

    /**
     * Get active sessions (currently connected users)
     * 
     * Returns all sessions that have started but not yet ended,
     * with calculated live duration based on current time.
     * Uses last_activity_at for stale detection - sessions with no activity
     * in 30+ minutes are considered abandoned (even if they ran for hours).
     */
    public function get_active_sessions($request) {
        global $wpdb;
        
        $sessions_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        
        // Get sessions with no ended_at (still active)
        // Use last_activity_at for stale detection (not started_at)
        // Sessions with no activity in 30 min are likely abandoned
        // But sessions can run for hours if they keep sending events
        $active = $wpdb->get_results(
            "SELECT 
                session_id,
                device_id_hash,
                started_at,
                last_activity_at,
                TIMESTAMPDIFF(SECOND, started_at, NOW()) as live_duration_seconds,
                is_return_viewer,
                device_model,
                os_version,
                country_code,
                city,
                zip_code,
                timezone,
                current_scene_id,
                current_scene_name
            FROM {$sessions_table}
            WHERE ended_at IS NULL
            AND is_restoration = 0
            AND last_activity_at > DATE_SUB(NOW(), INTERVAL 30 MINUTE)
            ORDER BY started_at DESC",
            ARRAY_A
        );
        
        // Get WordPress timezone for local time display
        $wp_timezone = wp_timezone();
        $now = new DateTime('now', new DateTimeZone('UTC'));
        
        // Convert started_at to local time for each session
        foreach ($active as &$session) {
            if (!empty($session['started_at'])) {
                $started = new DateTime($session['started_at'], new DateTimeZone('UTC'));
                $started->setTimezone($wp_timezone);
                $session['started_at_local'] = $started->format('Y-m-d H:i:s');
            }
            // Cast is_return_viewer to boolean for JSON
            $session['is_return_viewer'] = (bool) ($session['is_return_viewer'] ?? false);
        }
        
        return rest_ensure_response([
            'active_count' => count($active),
            'sessions'     => $active,
            'server_time'  => $now->format('c'),
            'timezone'     => $wp_timezone->getName(),
        ]);
    }

    /**
     * Get geographic data (Pro+)
     */
    public function get_geographic_data($request) {
        global $wpdb;
        
        list($start_date, $end_date) = $this->parse_period($request);
        
        $sessions_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        
        // By country
        $countries = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                country_code,
                COUNT(*) as session_count,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND country_code IS NOT NULL
            AND is_restoration = 0
            GROUP BY country_code
            ORDER BY session_count DESC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        // By DMA (US markets)
        $dmas = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                dma_code,
                COUNT(*) as session_count,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND dma_code IS NOT NULL
            AND is_restoration = 0
            GROUP BY dma_code
            ORDER BY session_count DESC
            LIMIT 50",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        // By city
        $cities = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                city,
                country_code,
                COUNT(*) as session_count,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND city IS NOT NULL
            AND is_restoration = 0
            GROUP BY city, country_code
            ORDER BY session_count DESC
            LIMIT 50",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        // By timezone
        $timezones = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                timezone,
                COUNT(*) as session_count
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND timezone IS NOT NULL
            AND is_restoration = 0
            GROUP BY timezone
            ORDER BY session_count DESC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ), ARRAY_A);
        
        return rest_ensure_response([
            'period'    => ['start' => $start_date, 'end' => $end_date],
            'countries' => $countries,
            'cities'    => $cities,
            'dmas'      => $dmas,
            'timezones' => $timezones,
        ]);
    }

    /**
     * Get sponsor reports (Business+)
     */
    public function get_sponsor_reports($request) {
        global $wpdb;
        
        list($start_date, $end_date) = $this->parse_period($request);
        
        $sponsor_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SPONSOR_DAILY);
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        
        // First try aggregated data
        $sponsors = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                sponsor_id,
                SUM(impressions) as total_impressions,
                SUM(unique_devices) as total_unique_devices,
                SUM(qr_scans) as total_qr_scans
            FROM {$sponsor_table}
            WHERE date BETWEEN %s AND %s
            GROUP BY sponsor_id
            ORDER BY total_impressions DESC",
            $start_date,
            $end_date
        ), ARRAY_A);
        
        // Fallback: Query raw events - look for promo_impression with sponsor block type
        // Use SUM of count field for batched impressions
        if (empty($sponsors)) {
            $sponsors = $wpdb->get_results($wpdb->prepare(
                "SELECT 
                    COALESCE(
                        JSON_UNQUOTE(JSON_EXTRACT(properties, '$.sponsor_id')),
                        JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_id'))
                    ) as sponsor_id,
                    COALESCE(
                        JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_title')),
                        'Unknown'
                    ) as sponsor_name,
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) as block_type,
                    SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as total_impressions,
                    COUNT(DISTINCT device_id_hash) as total_unique_devices,
                    0 as total_qr_scans
                FROM {$events_table}
                WHERE event_type = 'promo_impression'
                AND DATE(timestamp) BETWEEN %s AND %s
                AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) IN ('sponsor', 'sponsors')
                GROUP BY sponsor_id, sponsor_name, block_type
                ORDER BY total_impressions DESC",
                $start_date,
                $end_date
            ), ARRAY_A);
        }
        
        // Get shoutouts separately - use SUM for batched counts
        $shoutouts = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_id')),
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_id'))
                ) as shoutout_id,
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_title')),
                    'Unknown'
                ) as shoutout_name,
                SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as total_impressions,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$events_table}
            WHERE event_type = 'promo_impression'
            AND DATE(timestamp) BETWEEN %s AND %s
            AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) IN ('shoutout', 'shoutouts')
            GROUP BY shoutout_id, shoutout_name
            ORDER BY total_impressions DESC",
            $start_date,
            $end_date
        ), ARRAY_A);
        
        // Get promos separately - use SUM for batched counts
        $promos = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.promo_id')),
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_id'))
                ) as promo_id,
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_title')),
                    'Unknown'
                ) as promo_name,
                SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as total_impressions,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$events_table}
            WHERE event_type = 'promo_impression'
            AND DATE(timestamp) BETWEEN %s AND %s
            AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) IN ('promo', 'promos')
            GROUP BY promo_id, promo_name
            ORDER BY total_impressions DESC",
            $start_date,
            $end_date
        ), ARRAY_A);
        
        // Get QR scan stats separately (independent of sponsors)
        $qr_stats = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.qr_id')) as qr_id,
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.qr_destination')) as destination,
                COUNT(*) as total_scans,
                COUNT(DISTINCT device_id_hash) as unique_scanners
            FROM {$events_table}
            WHERE event_type = 'qr_scan'
            AND DATE(timestamp) BETWEEN %s AND %s
            GROUP BY qr_id, destination
            ORDER BY total_scans DESC",
            $start_date,
            $end_date
        ), ARRAY_A);
        
        // Enrich sponsors with post titles if we have IDs
        foreach ($sponsors as &$sponsor) {
            $sponsor_id = intval($sponsor['sponsor_id'] ?? 0);
            if ($sponsor_id > 0 && empty($sponsor['sponsor_name'])) {
                $sponsor['sponsor_name'] = get_the_title($sponsor_id) ?: "Sponsor #{$sponsor_id}";
            } else if (empty($sponsor['sponsor_name'])) {
                $sponsor['sponsor_name'] = 'Unknown Sponsor';
            }
            // Calculate scan rate
            $impressions = intval($sponsor['total_impressions'] ?? 0);
            $scans = intval($sponsor['total_qr_scans'] ?? 0);
            $sponsor['scan_rate'] = $impressions > 0 ? round(($scans / $impressions) * 100, 2) : 0;
        }
        
        return rest_ensure_response([
            'period'    => ['start' => $start_date, 'end' => $end_date],
            'sponsors'  => $sponsors,
            'shoutouts' => $shoutouts,
            'promos'    => $promos,
            'qr_stats'  => $qr_stats,
        ]);
    }

    /**
     * Trigger manual aggregation
     */
    public function trigger_aggregation($request) {
        $results = $this->aggregation->run_manual_aggregation();
        
        return rest_ensure_response([
            'success' => true,
            'results' => $results,
        ]);
    }

    /**
     * Export data as CSV
     */
    public function export_csv($request) {
        $type = $request->get_param('type') ?? 'summary';
        list($start_date, $end_date) = $this->parse_period($request);
        
        // Get appropriate data
        $data = [];
        $filename = "castconductor-analytics-{$type}-{$start_date}-to-{$end_date}.csv";
        
        switch ($type) {
            case 'sessions':
                $response = $this->get_sessions($request);
                $data = $response->get_data()['sessions'] ?? [];
                break;
                
            case 'devices':
                $response = $this->get_device_breakdown($request);
                $data = $response->get_data()['devices'] ?? [];
                break;
                
            case 'content':
                $response = $this->get_content_performance($request);
                $data = $response->get_data()['top_content'] ?? [];
                break;
                
            default:
                $response = $this->get_summary($request);
                $summary = $response->get_data()['summary'] ?? [];
                $data = [$summary]; // Single row
        }
        
        if (empty($data)) {
            return rest_ensure_response([
                'success' => false,
                'message' => 'No data to export',
            ]);
        }
        
        // Generate CSV content
        $csv = $this->array_to_csv($data);
        
        return rest_ensure_response([
            'success'  => true,
            'filename' => $filename,
            'content'  => base64_encode($csv),
            'mime'     => 'text/csv',
        ]);
    }

    /**
     * Convert array to CSV string
     */
    private function array_to_csv($data) {
        if (empty($data)) {
            return '';
        }
        
        $output = fopen('php://temp', 'r+');
        
        // Header row
        fputcsv($output, array_keys($data[0]));
        
        // Data rows
        foreach ($data as $row) {
            fputcsv($output, array_values($row));
        }
        
        rewind($output);
        $csv = stream_get_contents($output);
        fclose($output);
        
        return $csv;
    }

    /**
     * Get analytics settings
     */
    public function get_settings($request) {
        return rest_ensure_response([
            'retention' => [
                'raw_events' => CC_Analytics_Helpers::get_retention_days('raw_events'),
                'sessions'   => CC_Analytics_Helpers::get_retention_days('sessions'),
                'hourly'     => CC_Analytics_Helpers::get_retention_days('hourly'),
                'daily'      => CC_Analytics_Helpers::get_retention_days('daily'),
                'sponsor'    => CC_Analytics_Helpers::get_retention_days('sponsor'),
            ],
            'collection' => CC_Analytics_Helpers::get_content_block_type_info(),
            'last_hourly_aggregation' => $this->utc_to_local(get_option('cc_analytics_last_hourly', '')),
            'last_daily_aggregation'  => $this->utc_to_local(get_option('cc_analytics_last_daily', '')),
            'last_prune'              => $this->utc_to_local(get_option('cc_analytics_last_prune', '')),
            'analytics_key_prefix'    => substr(CC_Analytics_Helpers::get_analytics_key(), 0, 20) . '...',
        ]);
    }

    /**
     * Convert UTC timestamp to WordPress local time
     * 
     * @param string $utc_timestamp UTC datetime string
     * @return string Local datetime string or 'Never'
     */
    private function utc_to_local($utc_timestamp) {
        if (empty($utc_timestamp) || $utc_timestamp === 'Never') {
            return 'Never';
        }
        
        try {
            $utc = new DateTime($utc_timestamp, new DateTimeZone('UTC'));
            $local_tz = wp_timezone();
            $utc->setTimezone($local_tz);
            return $utc->format('Y-m-d H:i:s');
        } catch (Exception $e) {
            return $utc_timestamp;
        }
    }

    /**
     * Update analytics settings
     */
    public function update_settings($request) {
        $body = $request->get_json_params();
        
        if (isset($body['retention']) && is_array($body['retention'])) {
            $current = get_option('cc_analytics_retention', []);
            
            foreach ($body['retention'] as $type => $days) {
                $current[$type] = CC_Analytics_Helpers::validate_retention($type, (int) $days);
            }
            
            update_option('cc_analytics_retention', $current);
        }
        
        // Handle collection settings (which content types to track)
        if (isset($body['collection']) && is_array($body['collection'])) {
            CC_Analytics_Helpers::update_collection_settings($body['collection']);
        }
        
        // Handle analytics key regeneration
        if (!empty($body['regenerate_key'])) {
            CC_Analytics_Helpers::regenerate_analytics_key();
        }
        
        return $this->get_settings($request);
    }

    /**
     * Get available features for current plan
     */
    public function get_available_features($request) {
        $available = CC_Analytics_Helpers::get_available_features();
        $all_features = array_keys(CC_Analytics_Helpers::FEATURE_MAP);
        
        $features = [];
        foreach ($all_features as $feature) {
            $has_access = in_array($feature, $available, true);
            $features[$feature] = [
                'available' => $has_access,
                'upgrade'   => $has_access ? null : CC_Analytics_Helpers::get_upgrade_prompt($feature),
            ];
        }
        
        return rest_ensure_response([
            'plan'     => CC_Analytics_Helpers::get_current_plan(),
            'features' => $features,
        ]);
    }

    /**
     * Get all dashboard data in one unified response
     * 
     * This endpoint returns all analytics data in a single call for efficient
     * initial dashboard loading. Individual endpoints remain available for
     * granular refresh operations.
     * 
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public function get_dashboard_data($request) {
        list($start_date, $end_date) = $this->parse_period($request);
        
        // Get plan and features first
        $plan = CC_Analytics_Helpers::get_current_plan();
        $features = CC_Analytics_Helpers::get_available_features();
        
        // Build unified response
        $wp_timezone = wp_timezone();
        
        $dashboard = [
            'meta' => [
                'plan'         => $plan,
                'period'       => ['start' => $start_date, 'end' => $end_date],
                'generated_at' => gmdate('c'),
                'timezone'     => $wp_timezone->getName(),
            ],
            'summary'  => [],
            'sessions' => [],
            'active'   => [],
            'content'  => [],
            'devices'  => [],
            'geo'      => [],
            'sponsors' => [],
            'settings' => [],
            'features' => [],
        ];
        
        // Get summary data
        $summary_response = $this->get_summary($request);
        if (!is_wp_error($summary_response)) {
            $dashboard['summary'] = $summary_response->get_data();
        }
        
        // Get sessions data (limit to recent for efficiency)
        $sessions_response = $this->get_sessions($request);
        if (!is_wp_error($sessions_response)) {
            $dashboard['sessions'] = $sessions_response->get_data();
        }
        
        // Get active sessions (real-time)
        $active_response = $this->get_active_sessions($request);
        if (!is_wp_error($active_response)) {
            $dashboard['active'] = $active_response->get_data();
        }
        
        // Get content performance
        $content_response = $this->get_content_performance($request);
        if (!is_wp_error($content_response)) {
            $dashboard['content'] = $content_response->get_data();
        }
        
        // Get device breakdown
        $devices_response = $this->get_device_breakdown($request);
        if (!is_wp_error($devices_response)) {
            $dashboard['devices'] = $devices_response->get_data();
        }
        
        // Get geo data (if available for plan)
        if (in_array('geo', $features, true)) {
            $geo_response = $this->get_geographic_data($request);
            if (!is_wp_error($geo_response)) {
                $dashboard['geo'] = $geo_response->get_data();
            }
        }
        
        // Get sponsor data (if available for plan)
        if (in_array('sponsors', $features, true)) {
            $sponsors_response = $this->get_sponsor_reports($request);
            if (!is_wp_error($sponsors_response)) {
                $dashboard['sponsors'] = $sponsors_response->get_data();
            }
        }
        
        // Get settings
        $settings_response = $this->get_settings($request);
        if (!is_wp_error($settings_response)) {
            $dashboard['settings'] = $settings_response->get_data();
        }
        
        // Get feature availability
        $features_response = $this->get_available_features($request);
        if (!is_wp_error($features_response)) {
            $dashboard['features'] = $features_response->get_data();
        }
        
        return rest_ensure_response($dashboard);
    }
}
