<?php
/**
 * Analytics Tracking and Reporting Class
 * Handles event tracking, aggregation, and dashboard metrics
 */

class VAnalytics {
    private $db;
    private $logger;
    
    public function __construct() {
        global $class_database;
        $this->db = $class_database;
        $this->logger = new VLogger('analytics');
    }
    
    /**
     * Track a video event
     */
    public function trackEvent($video_id, $event_type, $data = []) {
        $usr_id = $data['usr_id'] ?? null;
        $watch_duration = $data['watch_duration'] ?? 0;
        $completion_percentage = $data['completion_percentage'] ?? 0;
        $device_type = $this->detectDeviceType();
        $browser = $this->getBrowser();
        $os = $this->getOS();
        $country_code = $data['country_code'] ?? $this->getCountryCode();
        $referrer = $_SERVER['HTTP_REFERER'] ?? '';
        $ip_hash = hash('sha256', $_SERVER['REMOTE_ADDR'] ?? '');
        $session_id = session_id() ?: bin2hex(random_bytes(16));
        
        $sql = "INSERT INTO db_video_analytics 
                (video_id, usr_id, event_type, watch_duration, completion_percentage, 
                 device_type, browser, os, country_code, referrer, ip_hash, session_id) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $result = $this->db->execute($sql, [
            $video_id, $usr_id, $event_type, $watch_duration, $completion_percentage,
            $device_type, $browser, $os, $country_code, $referrer, $ip_hash, $session_id
        ]);
        
        // Update real-time stats
        if ($event_type === 'view') {
            $this->updateRealtimeStats($video_id);
        }
        
        return $result;
    }
    
    /**
     * Track audience retention (minute-by-minute)
     */
    public function trackRetention($video_id, $minute) {
        $date = date('Y-m-d');
        
        $sql = "INSERT INTO db_audience_retention (video_id, minute, viewer_count, date) 
                VALUES (?, ?, 1, ?)
                ON DUPLICATE KEY UPDATE viewer_count = viewer_count + 1";
        
        return $this->db->execute($sql, [$video_id, $minute, $date]);
    }
    
    /**
     * Track traffic source
     */
    public function trackTrafficSource($video_id, $usr_id, $source_type, $source_detail = '') {
        $date = date('Y-m-d');
        
        $sql = "INSERT INTO db_traffic_sources (video_id, usr_id, source_type, source_detail, view_count, date) 
                VALUES (?, ?, ?, ?, 1, ?)
                ON DUPLICATE KEY UPDATE view_count = view_count + 1";
        
        return $this->db->execute($sql, [$video_id, $usr_id, $source_type, $source_detail, $date]);
    }
    
    /**
     * Get channel overview stats
     */
    public function getChannelOverview($usr_id, $days = 30) {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                SUM(views) as total_views,
                SUM(watch_time_minutes) as total_watch_time,
                SUM(likes) as total_likes,
                SUM(comments) as total_comments,
                SUM(shares) as total_shares,
                SUM(subscribers_gained) as subscribers_gained,
                SUM(subscribers_lost) as subscribers_lost,
                AVG(avg_view_duration) as avg_view_duration,
                AVG(avg_completion_rate) as avg_completion_rate
                FROM db_channel_analytics_daily
                WHERE usr_id = ? AND date >= ?";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        
        if (!$result) {
            return null;
        }
        
        $overview = $this->db->fetch($result);
        
        // Get real-time stats
        $realtime = $this->getRealtimeStats($usr_id);
        
        return array_merge($overview ?: [], $realtime ?: []);
    }
    
    /**
     * Get channel analytics over time (for charts)
     */
    public function getChannelTimeSeries($usr_id, $days = 30, $metric = 'views') {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        $allowed_metrics = ['views', 'watch_time_minutes', 'likes', 'comments', 'shares', 'subscribers_gained', 'unique_viewers'];
        
        if (!in_array($metric, $allowed_metrics)) {
            $metric = 'views';
        }
        
        $sql = "SELECT date, $metric as value 
                FROM db_channel_analytics_daily 
                WHERE usr_id = ? AND date >= ?
                ORDER BY date ASC";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        
        if (!$result) {
            return [];
        }
        
        return $this->db->resultsToArray($result);
    }
    
    /**
     * Get top videos by metric
     */
    public function getTopVideos($usr_id, $days = 30, $metric = 'views', $limit = 10) {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        $metric_field = match($metric) {
            'watch_time' => 'SUM(va.watch_duration)',
            'likes' => 'COUNT(CASE WHEN va.event_type = "like" THEN 1 END)',
            'comments' => 'COUNT(CASE WHEN va.event_type = "comment" THEN 1 END)',
            'shares' => 'COUNT(CASE WHEN va.event_type = "share" THEN 1 END)',
            'completion' => 'AVG(va.completion_percentage)',
            default => 'COUNT(CASE WHEN va.event_type = "view" THEN 1 END)'
        };
        
        $sql = "SELECT v.video_id, v.file_title as title, v.file_thumb as thumbnail,
                $metric_field as metric_value
                FROM db_videofiles v
                LEFT JOIN db_video_analytics va ON v.video_id = va.video_id 
                    AND va.created_at >= ?
                WHERE v.usr_id = ?
                GROUP BY v.video_id
                ORDER BY metric_value DESC
                LIMIT ?";
        
        $result = $this->db->execute($sql, [$start_date, $usr_id, $limit]);
        
        if (!$result) {
            return [];
        }
        
        return $this->db->resultsToArray($result);
    }
    
    /**
     * Get audience demographics
     */
    public function getDemographics($usr_id, $days = 30) {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        // Countries
        $sql = "SELECT country_code, SUM(view_count) as views 
                FROM db_audience_demographics 
                WHERE usr_id = ? AND date >= ?
                GROUP BY country_code 
                ORDER BY views DESC 
                LIMIT 10";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        $countries = $this->db->resultsToArray($result) ?: [];
        
        // Devices
        $sql = "SELECT device_type, SUM(view_count) as views 
                FROM db_audience_demographics 
                WHERE usr_id = ? AND date >= ?
                GROUP BY device_type 
                ORDER BY views DESC";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        $devices = $this->db->resultsToArray($result) ?: [];
        
        // Age ranges
        $sql = "SELECT age_range, SUM(view_count) as views 
                FROM db_audience_demographics 
                WHERE usr_id = ? AND date >= ?
                GROUP BY age_range 
                ORDER BY views DESC";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        $ages = $this->db->resultsToArray($result) ?: [];
        
        return [
            'countries' => $countries,
            'devices' => $devices,
            'age_ranges' => $ages
        ];
    }
    
    /**
     * Get traffic sources breakdown
     */
    public function getTrafficSources($usr_id, $days = 30) {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT source_type, source_detail, SUM(view_count) as views 
                FROM db_traffic_sources 
                WHERE usr_id = ? AND date >= ?
                GROUP BY source_type, source_detail 
                ORDER BY views DESC";
        
        $result = $this->db->execute($sql, [$usr_id, $start_date]);
        
        if (!$result) {
            return [];
        }
        
        return $this->db->resultsToArray($result);
    }
    
    /**
     * Get audience retention graph for a video
     */
    public function getAudienceRetention($video_id, $days = 30) {
        $start_date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT minute, SUM(viewer_count) as viewers 
                FROM db_audience_retention 
                WHERE video_id = ? AND date >= ?
                GROUP BY minute 
                ORDER BY minute ASC";
        
        $result = $this->db->execute($sql, [$video_id, $start_date]);
        
        if (!$result) {
            return [];
        }
        
        return $this->db->resultsToArray($result);
    }
    
    /**
     * Get real-time stats
     */
    private function getRealtimeStats($usr_id) {
        $sql = "SELECT * FROM db_realtime_stats WHERE usr_id = ?";
        $result = $this->db->execute($sql, [$usr_id]);
        
        if (!$result || $this->db->rowCount($result) == 0) {
            return null;
        }
        
        return $this->db->fetch($result);
    }
    
    /**
     * Update real-time stats cache
     */
    private function updateRealtimeStats($video_id) {
        // Get video owner
        $sql = "SELECT usr_id FROM db_videofiles WHERE video_id = ?";
        $result = $this->db->execute($sql, [$video_id]);
        
        if (!$result) return;
        
        $row = $this->db->fetch($result);
        $usr_id = $row['usr_id'];
        
        // Calculate stats
        $views_1h = $this->getViewCount($usr_id, 1);
        $views_24h = $this->getViewCount($usr_id, 24);
        $subscribers_24h = $this->getSubscriberChange($usr_id, 24);
        
        // Upsert real-time stats
        $sql = "INSERT INTO db_realtime_stats 
                (usr_id, views_last_hour, views_last_24h, subscribers_last_24h) 
                VALUES (?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                views_last_hour = VALUES(views_last_hour),
                views_last_24h = VALUES(views_last_24h),
                subscribers_last_24h = VALUES(subscribers_last_24h)";
        
        $this->db->execute($sql, [$usr_id, $views_1h, $views_24h, $subscribers_24h]);
    }
    
    /**
     * Aggregate daily stats (called by cron)
     */
    public function aggregateDailyStats($date = null) {
        $date = $date ?: date('Y-m-d', strtotime('-1 day'));
        
        $sql = "INSERT INTO db_channel_analytics_daily 
                (usr_id, date, views, watch_time_minutes, likes, comments, shares, unique_viewers, avg_view_duration, avg_completion_rate)
                SELECT 
                    v.usr_id,
                    DATE(va.created_at) as date,
                    COUNT(CASE WHEN va.event_type = 'view' THEN 1 END) as views,
                    SUM(CASE WHEN va.event_type = 'view' THEN va.watch_duration END) / 60 as watch_time_minutes,
                    COUNT(CASE WHEN va.event_type = 'like' THEN 1 END) as likes,
                    COUNT(CASE WHEN va.event_type = 'comment' THEN 1 END) as comments,
                    COUNT(CASE WHEN va.event_type = 'share' THEN 1 END) as shares,
                    COUNT(DISTINCT va.session_id) as unique_viewers,
                    AVG(CASE WHEN va.event_type = 'view' THEN va.watch_duration END) as avg_view_duration,
                    AVG(CASE WHEN va.event_type = 'view' THEN va.completion_percentage END) as avg_completion_rate
                FROM db_video_analytics va
                JOIN db_videofiles v ON va.video_id = v.video_id
                WHERE DATE(va.created_at) = ?
                GROUP BY v.usr_id
                ON DUPLICATE KEY UPDATE
                    views = VALUES(views),
                    watch_time_minutes = VALUES(watch_time_minutes),
                    likes = VALUES(likes),
                    comments = VALUES(comments),
                    shares = VALUES(shares),
                    unique_viewers = VALUES(unique_viewers),
                    avg_view_duration = VALUES(avg_view_duration),
                    avg_completion_rate = VALUES(avg_completion_rate)";
        
        return $this->db->execute($sql, [$date]);
    }
    
    // Helper methods
    
    private function getViewCount($usr_id, $hours) {
        $start_time = date('Y-m-d H:i:s', strtotime("-$hours hours"));
        
        $sql = "SELECT COUNT(*) as count 
                FROM db_video_analytics va
                JOIN db_videofiles v ON va.video_id = v.video_id
                WHERE v.usr_id = ? AND va.event_type = 'view' AND va.created_at >= ?";
        
        $result = $this->db->execute($sql, [$usr_id, $start_time]);
        
        if (!$result) return 0;
        
        $row = $this->db->fetch($result);
        return intval($row['count'] ?? 0);
    }
    
    private function getSubscriberChange($usr_id, $hours) {
        $start_time = date('Y-m-d H:i:s', strtotime("-$hours hours"));
        
        $sql = "SELECT COUNT(*) as count 
                FROM db_subscribers 
                WHERE channel_id = ? AND subscribed_at >= ?";
        
        $result = $this->db->execute($sql, [$usr_id, $start_time]);
        
        if (!$result) return 0;
        
        $row = $this->db->fetch($result);
        return intval($row['count'] ?? 0);
    }
    
    private function detectDeviceType() {
        $ua = $_SERVER['HTTP_USER_AGENT'] ?? '';
        
        if (preg_match('/mobile|android|iphone|ipad|tablet/i', $ua)) {
            if (preg_match('/tablet|ipad/i', $ua)) return 'tablet';
            return 'mobile';
        }
        if (preg_match('/tv|smarttv/i', $ua)) return 'tv';
        return 'desktop';
    }
    
    private function getBrowser() {
        $ua = $_SERVER['HTTP_USER_AGENT'] ?? '';
        
        if (preg_match('/Edge/i', $ua)) return 'Edge';
        if (preg_match('/Chrome/i', $ua)) return 'Chrome';
        if (preg_match('/Firefox/i', $ua)) return 'Firefox';
        if (preg_match('/Safari/i', $ua)) return 'Safari';
        if (preg_match('/Opera/i', $ua)) return 'Opera';
        
        return 'Other';
    }
    
    private function getOS() {
        $ua = $_SERVER['HTTP_USER_AGENT'] ?? '';
        
        if (preg_match('/Windows/i', $ua)) return 'Windows';
        if (preg_match('/Mac/i', $ua)) return 'macOS';
        if (preg_match('/Linux/i', $ua)) return 'Linux';
        if (preg_match('/Android/i', $ua)) return 'Android';
        if (preg_match('/iOS|iPhone|iPad/i', $ua)) return 'iOS';
        
        return 'Other';
    }
    
    private function getCountryCode() {
        // Placeholder - integrate with GeoIP service
        return 'US';
    }
}