<?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 Aggregation
 * 
 * Handles hourly and daily data aggregation, WP-Cron scheduling,
 * and data pruning based on retention policies.
 * 
 * @package CastConductor
 * @subpackage Analytics
 * @since 5.8.0
 */

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

class CC_Analytics_Aggregation {

    /**
     * Cron hook names
     */
    const CRON_HOURLY = 'cc_analytics_hourly_aggregation';
    const CRON_DAILY  = 'cc_analytics_daily_aggregation';
    const CRON_PRUNE  = 'cc_analytics_data_pruning';

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

    /**
     * Database instance
     */
    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();
        
        // Register cron hooks
        add_action(self::CRON_HOURLY, [$this, 'run_hourly_aggregation']);
        add_action(self::CRON_DAILY, [$this, 'run_daily_aggregation']);
        add_action(self::CRON_PRUNE, [$this, 'run_data_pruning']);
    }

    /**
     * Schedule all cron jobs
     * Called on plugin activation
     */
    public function schedule_cron_jobs() {
        // Hourly aggregation
        if (!wp_next_scheduled(self::CRON_HOURLY)) {
            wp_schedule_event(time(), 'hourly', self::CRON_HOURLY);
        }
        
        // Daily aggregation (run at 2 AM to avoid peak traffic)
        if (!wp_next_scheduled(self::CRON_DAILY)) {
            $next_2am = strtotime('tomorrow 2:00 AM');
            wp_schedule_event($next_2am, 'daily', self::CRON_DAILY);
        }
        
        // Data pruning (run at 3 AM)
        if (!wp_next_scheduled(self::CRON_PRUNE)) {
            $next_3am = strtotime('tomorrow 3:00 AM');
            wp_schedule_event($next_3am, 'daily', self::CRON_PRUNE);
        }
    }

    /**
     * Clear all scheduled cron jobs
     * Called on plugin deactivation
     */
    public static function clear_cron_jobs() {
        wp_clear_scheduled_hook(self::CRON_HOURLY);
        wp_clear_scheduled_hook(self::CRON_DAILY);
        wp_clear_scheduled_hook(self::CRON_PRUNE);
    }

    /**
     * Run hourly aggregation
     * 
     * @param string|null $hour_start Optional specific hour to aggregate (for backfill)
     * @return array Aggregation results
     */
    public function run_hourly_aggregation($hour_start = null) {
        global $wpdb;
        
        // Determine which hour to aggregate
        if ($hour_start === null) {
            // Aggregate the previous complete hour
            $hour_start = date('Y-m-d H:00:00', strtotime('-1 hour'));
        }
        
        $hour_end = date('Y-m-d H:59:59', strtotime($hour_start));
        
        $results = [
            'hour'    => $hour_start,
            'metrics' => [],
        ];
        
        // Aggregate sessions
        $results['metrics']['sessions'] = $this->aggregate_hourly_sessions($hour_start, $hour_end);
        
        // Aggregate events by type
        $results['metrics']['events'] = $this->aggregate_hourly_events($hour_start, $hour_end);
        
        // Aggregate content views
        $results['metrics']['content'] = $this->aggregate_hourly_content($hour_start, $hour_end);
        
        // Aggregate device breakdown
        $results['metrics']['devices'] = $this->aggregate_hourly_devices($hour_start, $hour_end);
        
        // Update last aggregation timestamp
        update_option('cc_analytics_last_hourly', CC_Analytics_Helpers::now_utc());
        
        do_action('cc_analytics_hourly_complete', $results);
        
        return $results;
    }

    /**
     * Aggregate session metrics for an hour
     */
    private function aggregate_hourly_sessions($hour_start, $hour_end) {
        global $wpdb;
        
        $sessions_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        $hourly_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_HOURLY);
        
        // Get session stats for this hour
        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(*) as session_count,
                COUNT(DISTINCT device_id_hash) as unique_devices,
                AVG(duration_seconds) as avg_duration,
                SUM(duration_seconds) as total_duration
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND is_restoration = 0",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        // Upsert session count
        $this->upsert_hourly_metric($hour_start, 'session_count', null, [
            'count_value' => (int) ($stats['session_count'] ?? 0),
        ]);
        
        // Upsert unique devices
        $this->upsert_hourly_metric($hour_start, 'unique_devices', null, [
            'count_value' => (int) ($stats['unique_devices'] ?? 0),
        ]);
        
        // Upsert average duration
        $this->upsert_hourly_metric($hour_start, 'avg_duration', null, [
            'avg_value' => (float) ($stats['avg_duration'] ?? 0),
            'sum_value' => (int) ($stats['total_duration'] ?? 0),
        ]);
        
        return $stats;
    }

    /**
     * Aggregate event counts by type for an hour
     */
    private function aggregate_hourly_events($hour_start, $hour_end) {
        global $wpdb;
        
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        
        // Get counts per event type
        $events = $wpdb->get_results($wpdb->prepare(
            "SELECT event_type, COUNT(*) as count
            FROM {$events_table}
            WHERE timestamp BETWEEN %s AND %s
            GROUP BY event_type",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        foreach ($events as $event) {
            $this->upsert_hourly_metric($hour_start, 'event_count', $event['event_type'], [
                'count_value' => (int) $event['count'],
            ]);
        }
        
        return $events;
    }

    /**
     * Aggregate content views for an hour
     */
    private function aggregate_hourly_content($hour_start, $hour_end) {
        global $wpdb;
        
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        
        // Get scene views
        $scene_views = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.scene_id')) as scene_id,
                COUNT(*) as view_count
            FROM {$events_table}
            WHERE event_type = 'scene_view'
            AND timestamp BETWEEN %s AND %s
            GROUP BY scene_id",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        foreach ($scene_views as $view) {
            if ($view['scene_id']) {
                $this->upsert_hourly_metric($hour_start, 'scene_views', $view['scene_id'], [
                    'count_value' => (int) $view['view_count'],
                ]);
            }
        }
        
        // Get stream starts
        $streams = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_id')) as content_block_id,
                COUNT(*) as play_count
            FROM {$events_table}
            WHERE event_type = 'stream_start'
            AND timestamp BETWEEN %s AND %s
            GROUP BY content_block_id",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        foreach ($streams as $stream) {
            if ($stream['content_block_id']) {
                $this->upsert_hourly_metric($hour_start, 'stream_starts', $stream['content_block_id'], [
                    'count_value' => (int) $stream['play_count'],
                ]);
            }
        }
        
        // Get container/content block impressions
        // Use SUM of count field (defaults to 1 for legacy single-impression events)
        // This supports batched impressions which have count > 1
        $impressions = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_id')) as content_block_id,
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) as content_block_type,
                SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as impression_count
            FROM {$events_table}
            WHERE event_type = 'container_impression'
            AND timestamp BETWEEN %s AND %s
            GROUP BY content_block_id, content_block_type",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        foreach ($impressions as $imp) {
            if ($imp['content_block_id']) {
                $key = $imp['content_block_id'];
                if (!empty($imp['content_block_type'])) {
                    $key .= ':' . $imp['content_block_type'];
                }
                $this->upsert_hourly_metric($hour_start, 'content_impressions', $key, [
                    'count_value' => (int) $imp['impression_count'],
                ]);
            }
        }
        
        return [
            'scene_views'  => $scene_views,
            'streams'      => $streams,
            'impressions'  => $impressions,
        ];
    }

    /**
     * Aggregate device breakdown for an hour
     */
    private function aggregate_hourly_devices($hour_start, $hour_end) {
        global $wpdb;
        
        $sessions_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SESSIONS);
        
        // Get device model breakdown
        $devices = $wpdb->get_results($wpdb->prepare(
            "SELECT device_model, COUNT(*) as count
            FROM {$sessions_table}
            WHERE started_at BETWEEN %s AND %s
            AND device_model IS NOT NULL
            AND is_restoration = 0
            GROUP BY device_model",
            $hour_start,
            $hour_end
        ), ARRAY_A);
        
        foreach ($devices as $device) {
            $this->upsert_hourly_metric($hour_start, 'device_model', $device['device_model'], [
                'count_value' => (int) $device['count'],
            ]);
        }
        
        return $devices;
    }

    /**
     * Upsert a metric into the hourly table
     */
    private function upsert_hourly_metric($hour_start, $metric_type, $metric_key, $values) {
        global $wpdb;
        
        $table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_HOURLY);
        
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$table} (hour_start, metric_type, metric_key, count_value, sum_value, avg_value)
            VALUES (%s, %s, %s, %d, %d, %f)
            ON DUPLICATE KEY UPDATE
                count_value = VALUES(count_value),
                sum_value = VALUES(sum_value),
                avg_value = VALUES(avg_value)",
            $hour_start,
            $metric_type,
            $metric_key,
            $values['count_value'] ?? 0,
            $values['sum_value'] ?? 0,
            $values['avg_value'] ?? 0.0
        ));
    }

    /**
     * Run daily aggregation
     * 
     * @param string|null $date Optional specific date to aggregate (for backfill)
     * @return array Aggregation results
     */
    public function run_daily_aggregation($date = null) {
        global $wpdb;
        
        // Determine which date to aggregate
        if ($date === null) {
            // Aggregate yesterday
            $date = date('Y-m-d', strtotime('-1 day'));
        }
        
        $results = [
            'date'    => $date,
            'metrics' => [],
        ];
        
        // Roll up hourly data into daily
        $hourly_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_HOURLY);
        $daily_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_DAILY);
        
        // Aggregate from hourly table
        $hourly_data = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                metric_type,
                metric_key,
                SUM(count_value) as total_count,
                SUM(sum_value) as total_sum,
                AVG(avg_value) as overall_avg
            FROM {$hourly_table}
            WHERE DATE(hour_start) = %s
            GROUP BY metric_type, metric_key",
            $date
        ), ARRAY_A);
        
        foreach ($hourly_data as $row) {
            $wpdb->query($wpdb->prepare(
                "INSERT INTO {$daily_table} (date, metric_type, metric_key, count_value, sum_value, avg_value)
                VALUES (%s, %s, %s, %d, %d, %f)
                ON DUPLICATE KEY UPDATE
                    count_value = VALUES(count_value),
                    sum_value = VALUES(sum_value),
                    avg_value = VALUES(avg_value)",
                $date,
                $row['metric_type'],
                $row['metric_key'],
                (int) $row['total_count'],
                (int) $row['total_sum'],
                (float) $row['overall_avg']
            ));
            
            $results['metrics'][] = $row;
        }
        
        // Aggregate sponsor daily metrics
        $results['sponsors'] = $this->aggregate_sponsor_daily($date);
        
        // Aggregate unified content daily metrics (replaces sponsor-specific aggregation)
        $results['content'] = $this->aggregate_content_daily($date);
        
        // Update last aggregation timestamp
        update_option('cc_analytics_last_daily', CC_Analytics_Helpers::now_utc());
        
        do_action('cc_analytics_daily_complete', $results);
        
        return $results;
    }

    /**
     * Aggregate sponsor metrics for a day
     */
    private function aggregate_sponsor_daily($date) {
        global $wpdb;
        
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        $qr_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_QR_SCANS);
        $sponsor_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_SPONSOR_DAILY);
        
        // Get promo impressions
        // Use SUM of count field (defaults to 1 for legacy single-impression events)
        // This supports batched impressions which have count > 1
        $impressions = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.sponsor_id')) as sponsor_id,
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.promo_id')) as promo_id,
                SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as impression_count,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$events_table}
            WHERE event_type = 'promo_impression'
            AND DATE(timestamp) = %s
            GROUP BY sponsor_id, promo_id",
            $date
        ), ARRAY_A);
        
        // Get QR scans
        $scans = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                sponsor_id,
                promo_id,
                COUNT(*) as scan_count
            FROM {$qr_table}
            WHERE DATE(scanned_at) = %s
            GROUP BY sponsor_id, promo_id",
            $date
        ), ARRAY_A);
        
        // Index scans by sponsor/promo
        $scan_map = [];
        foreach ($scans as $scan) {
            $key = $scan['sponsor_id'] . '_' . ($scan['promo_id'] ?? 0);
            $scan_map[$key] = (int) $scan['scan_count'];
        }
        
        // Upsert sponsor daily metrics
        foreach ($impressions as $imp) {
            if (!$imp['sponsor_id']) continue;
            
            $key = $imp['sponsor_id'] . '_' . ($imp['promo_id'] ?? 0);
            $qr_scans = $scan_map[$key] ?? 0;
            
            $wpdb->query($wpdb->prepare(
                "INSERT INTO {$sponsor_table} (date, sponsor_id, promo_id, impressions, unique_devices, qr_scans)
                VALUES (%s, %d, %d, %d, %d, %d)
                ON DUPLICATE KEY UPDATE
                    impressions = VALUES(impressions),
                    unique_devices = VALUES(unique_devices),
                    qr_scans = VALUES(qr_scans)",
                $date,
                (int) $imp['sponsor_id'],
                (int) ($imp['promo_id'] ?? 0),
                (int) $imp['impression_count'],
                (int) $imp['unique_devices'],
                $qr_scans
            ));
        }
        
        return $impressions;
    }

    /**
     * Aggregate unified content metrics for a day
     * 
     * Aggregates impression data for ALL content blocks (sponsors, promos, shoutouts,
     * track_info_hero, etc.) into a single content_daily table. This replaces the
     * sponsor-centric approach with a type-agnostic design.
     * 
     * Groups by content_item_id to track individual sponsors/promos within a content block.
     * 
     * @param string $date The date to aggregate (Y-m-d format)
     * @return array Aggregation results
     * @since 5.8.0
     */
    private function aggregate_content_daily($date) {
        global $wpdb;
        
        $events_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_EVENTS);
        $content_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_CONTENT_DAILY);
        $qr_table = CC_Analytics_Database::table(CC_Analytics_Database::TABLE_QR_SCANS);
        
        // Get ALL content impressions grouped by content_item_id (individual sponsor/promo)
        // Falls back to content_block_id for non-rotating content
        // Supports batched impressions with count field
        $impressions = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_id')),
                    JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_id')),
                    '0'
                ) as content_block_id,
                JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) as content_block_type,
                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'),
                    '0'
                ) as content_item_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, '$.title')), ''),
                    'Unknown'
                ) as content_item_title,
                SUM(COALESCE(JSON_EXTRACT(properties, '$.count'), 1)) as impression_count,
                COUNT(DISTINCT device_id_hash) as unique_devices
            FROM {$events_table}
            WHERE event_type IN ('container_impression', 'promo_impression')
            AND DATE(timestamp) = %s
            AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) IS NOT NULL
            AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.content_block_type')) != ''
            GROUP BY content_block_id, content_block_type, content_item_id, content_item_title",
            $date
        ), ARRAY_A);
        
        // Get QR scans grouped by content_item_id (where applicable)
        $scans = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                COALESCE(sponsor_id, promo_id, 0) as content_item_id,
                COUNT(*) as scan_count
            FROM {$qr_table}
            WHERE DATE(scanned_at) = %s
            GROUP BY content_item_id",
            $date
        ), ARRAY_A);
        
        // Index scans by content_item_id
        $scan_map = [];
        foreach ($scans as $scan) {
            $scan_map[$scan['content_item_id']] = (int) $scan['scan_count'];
        }
        
        $results = [];
        
        // Upsert content daily metrics
        foreach ($impressions as $imp) {
            $content_block_id = (int) $imp['content_block_id'];
            if ($content_block_id <= 0) continue;
            
            $content_item_id = (int) ($imp['content_item_id'] ?? 0);
            $qr_scans = $scan_map[$content_item_id] ?? 0;
            
            $wpdb->query($wpdb->prepare(
                "INSERT INTO {$content_table} 
                    (date, content_block_id, content_block_type, content_item_id, content_item_title, impressions, unique_devices, qr_scans)
                VALUES (%s, %d, %s, %d, %s, %d, %d, %d)
                ON DUPLICATE KEY UPDATE
                    content_block_type = VALUES(content_block_type),
                    content_item_title = VALUES(content_item_title),
                    impressions = VALUES(impressions),
                    unique_devices = VALUES(unique_devices),
                    qr_scans = VALUES(qr_scans)",
                $date,
                $content_block_id,
                $imp['content_block_type'],
                $content_item_id,
                $imp['content_item_title'],
                (int) $imp['impression_count'],
                (int) $imp['unique_devices'],
                $qr_scans
            ));
            
            $results[] = $imp;
        }
        
        return $results;
    }

    /**
     * Run data pruning based on retention settings
     * 
     * @return array Pruning results
     */
    public function run_data_pruning() {
        $results = $this->db->prune_old_data();
        
        // Update last pruning timestamp
        update_option('cc_analytics_last_prune', CC_Analytics_Helpers::now_utc());
        
        do_action('cc_analytics_prune_complete', $results);
        
        return $results;
    }

    /**
     * Manually trigger aggregation (for admin button)
     * 
     * @return array Combined results
     */
    public function run_manual_aggregation() {
        $results = [
            'hourly' => $this->run_hourly_aggregation(),
            'daily'  => $this->run_daily_aggregation(),
            'prune'  => $this->run_data_pruning(),
        ];
        
        return $results;
    }

    /**
     * Backfill aggregation for a date range
     * Useful after importing historical data
     * 
     * @param string $start_date Start date (Y-m-d)
     * @param string $end_date End date (Y-m-d)
     * @return array Backfill results
     */
    public function backfill($start_date, $end_date) {
        $results = [];
        
        $current = new DateTime($start_date);
        $end = new DateTime($end_date);
        
        while ($current <= $end) {
            $date = $current->format('Y-m-d');
            
            // Run hourly aggregations for each hour of the day
            for ($hour = 0; $hour < 24; $hour++) {
                $hour_start = $date . ' ' . sprintf('%02d', $hour) . ':00:00';
                $this->run_hourly_aggregation($hour_start);
            }
            
            // Run daily aggregation
            $results[$date] = $this->run_daily_aggregation($date);
            
            $current->modify('+1 day');
        }
        
        return $results;
    }
}
