<?php

namespace App\Models;

use CodeIgniter\Model;

class AnalyticsModel extends Model
{
    protected $db;
    
    public function __construct()
    {
        parent::__construct();
        $this->db = \Config\Database::connect();
    }
    /**
 * Agent istatistiklerini getir
 */
public function getAgentStatistics($agentId)
{
    try {
        $stats = array();
        
        // 1. Toplam ilan sayısı
        $builder = $this->db->table('property_user');
        $builder->where('user_id', $agentId);
        $stats['total_listings'] = $builder->countAllResults();
        
        // 2. Toplam aktif ilan sayısı
        $builder = $this->db->table('property_user pu');
        $builder->join('property p', 'p.id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('p.is_activated', '1');
        $stats['active_listings'] = $builder->countAllResults();
        
        // 3. İstatistikler toplamı (görüntülenme, favori, gizlenme, enquiry)
        $builder = $this->db->table('property_statistics ps');
        $builder->select('
            SUM(ps.total_views) as total_views,
            SUM(ps.unique_views) as unique_views,
            SUM(ps.total_favorites) as total_favorites,
            SUM(ps.total_hidden) as total_hidden,
            SUM(ps.total_enquiries) as total_enquiries,
            AVG(ps.avg_view_duration) as avg_view_duration
        ');
        $builder->join('property_user pu', 'ps.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $result = $builder->get()->getRowArray();
        
        $stats['total_views'] = intval($result['total_views'] ?? 0);
        $stats['unique_views'] = intval($result['unique_views'] ?? 0);
        $stats['total_favorites'] = intval($result['total_favorites'] ?? 0);
        $stats['total_hidden'] = intval($result['total_hidden'] ?? 0);
        $stats['total_enquiries'] = intval($result['total_enquiries'] ?? 0);
        $stats['avg_view_duration'] = intval($result['avg_view_duration'] ?? 0);
        
        // 4. Dönüşüm oranı hesaplama
        if($stats['unique_views'] > 0) {
            $stats['conversion_rate'] = round(($stats['total_enquiries'] / $stats['unique_views']) * 100, 2) . '%';
        } else {
            $stats['conversion_rate'] = '0%';
        }
        
        // 5. Son 7 gün performansı
        $builder = $this->db->table('property_views pv');
        $builder->select('COUNT(*) as recent_views');
        $builder->join('property_user pu', 'pv.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('pv.created_at >=', date('Y-m-d H:i:s', strtotime('-7 days')));
        $recentResult = $builder->get()->getRowArray();
        $stats['last_7_days_views'] = intval($recentResult['recent_views'] ?? 0);
        
        // 6. Ortalama yanıt süresi (enquiry için)
        $builder = $this->db->table('enquire e');
        $builder->select('AVG(TIMESTAMPDIFF(HOUR, e.date, e.updated_at)) as avg_hours');
        $builder->where('e.agent_id', $agentId);
        $builder->where('e.readed', 1);
        $responseResult = $builder->get()->getRowArray();
        
        if($responseResult['avg_hours']) {
            $hours = round($responseResult['avg_hours']);
            if($hours < 24) {
                $stats['avg_response_time'] = $hours . ' saat';
            } else {
                $stats['avg_response_time'] = round($hours / 24) . ' gün';
            }
        } else {
            $stats['avg_response_time'] = 'N/A';
        }
        
        // 7. En popüler ilanlar
        $builder = $this->db->table('property_statistics ps');
        $builder->select('ps.property_id, ps.total_views');
        $builder->join('property_user pu', 'ps.property_id = pu.property_id');
        $builder->join('property_lang pl', 'pl.property_id = ps.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
        $builder->orderBy('ps.total_views', 'DESC');
        $builder->limit(5);
        $stats['top_properties'] = $builder->get()->getResultArray();
        
        return $stats;
        
    } catch (\Exception $e) {
        log_message('error', 'getAgentStatistics error: ' . $e->getMessage());
        
        // Hata durumunda default değerler
        return [
            'total_listings' => 0,
            'active_listings' => 0,
            'total_views' => 0,
            'unique_views' => 0,
            'total_favorites' => 0,
            'total_hidden' => 0,
            'total_enquiries' => 0,
            'conversion_rate' => '0%',
            'avg_response_time' => 'N/A',
            'last_7_days_views' => 0,
            'top_properties' => []
        ];
    }
}

/**
 * Agent portföy detaylı istatistikleri
 */
public function getAgentPortfolioStats($agentId)
{
    try {
        $stats = array();
        
        // 1. Kategorilere göre dağılım
        $builder = $this->db->table('property_lang pl');
        $builder->select('
            JSON_UNQUOTE(JSON_EXTRACT(pl.json_object, "$.field_79")) as category,
            COUNT(*) as count,
            AVG(pl.field_36_int) as avg_price
        ');
        $builder->join('property_user pu', 'pl.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
        $builder->groupBy('category');
        $builder->orderBy('count', 'DESC');
        $stats['categories'] = $builder->get()->getResultArray();
        
        // 2. Lokasyona göre dağılım
        $builder = $this->db->table('property_lang pl');
        $builder->select('
            JSON_UNQUOTE(JSON_EXTRACT(pl.json_object, "$.field_64")) as location,
            COUNT(*) as count
        ');
        $builder->join('property_user pu', 'pl.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
        $builder->groupBy('location');
        $builder->orderBy('count', 'DESC');
        $builder->limit(10);
        $stats['locations'] = $builder->get()->getResultArray();
        
        // 3. Fiyat aralığı dağılımı
        $builder = $this->db->table('property_lang pl');
        $builder->select('
            CASE 
                WHEN field_36_int < 1000000 THEN "0-1M"
                WHEN field_36_int < 2000000 THEN "1M-2M"
                WHEN field_36_int < 5000000 THEN "2M-5M"
                WHEN field_36_int < 10000000 THEN "5M-10M"
                ELSE "10M+"
            END as price_range,
            COUNT(*) as count,
            MIN(field_36_int) as min_price,
            MAX(field_36_int) as max_price
        ');
        $builder->join('property_user pu', 'pl.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
        $builder->groupBy('price_range');
        $builder->orderBy('min_price', 'ASC');
        $stats['price_distribution'] = $builder->get()->getResultArray();
        
        // 4. Son 30 gün performansı (günlük detay)
        $sql = "SELECT 
                DATE(pv.created_at) as date,
                COUNT(*) as views,
                COUNT(DISTINCT pv.session_id) as unique_views,
                COUNT(DISTINCT pv.property_id) as properties_viewed
            FROM property_views pv
            JOIN property_user pu ON pv.property_id = pu.property_id
            WHERE pu.user_id = ?
            AND pv.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            GROUP BY DATE(pv.created_at)
            ORDER BY date DESC";
        
        $stats['last_30_days'] = $this->db->query($sql, [$agentId])->getResultArray();
        
        // 5. Performans karşılaştırması (ortalama ile)
        $builder = $this->db->table('property_statistics ps');
        $builder->select('AVG(ps.total_views) as avg_views_per_property');
        $builder->join('property_user pu', 'ps.property_id = pu.property_id');
        $builder->where('pu.user_id', $agentId);
        $agentAvg = $builder->get()->getRowArray();
        
        $builder = $this->db->table('property_statistics');
        $builder->selectAvg('total_views', 'system_avg');
        $systemAvg = $builder->get()->getRowArray();
        
        $stats['performance_comparison'] = [
            'agent_avg_views' => round($agentAvg['avg_views_per_property'] ?? 0),
            'system_avg_views' => round($systemAvg['system_avg'] ?? 0),
            'performance_index' => $systemAvg['system_avg'] > 0 
                ? round((($agentAvg['avg_views_per_property'] ?? 0) / $systemAvg['system_avg']) * 100) 
                : 0
        ];
        
        // 6. Aylık özet
        $sql = "SELECT 
                DATE_FORMAT(pv.created_at, '%Y-%m') as month,
                COUNT(*) as total_views,
                COUNT(DISTINCT pv.session_id) as unique_views,
                COUNT(DISTINCT pv.property_id) as properties_viewed
            FROM property_views pv
            JOIN property_user pu ON pv.property_id = pu.property_id
            WHERE pu.user_id = ?
            AND pv.created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            GROUP BY DATE_FORMAT(pv.created_at, '%Y-%m')
            ORDER BY month DESC";
        
        $stats['monthly_summary'] = $this->db->query($sql, [$agentId])->getResultArray();
        
        return $stats;
        
    } catch (\Exception $e) {
        log_message('error', 'getAgentPortfolioStats error: ' . $e->getMessage());
        return [];
    }
}
   
    /**
     * Favori ekleme/çıkarma kaydı
     */
    public function recordFavoriteAction($propertyId, $userId, $action = 'add')
    {
        try {
            // 1. Activity log'a kaydet
            $this->logActivity([
                'user_id' => $userId,
                'activity_type' => $action === 'add' ? 'favorite' : 'unfavorite',
                'property_id' => $propertyId
            ]);
            
            // 2. Property statistics güncelle
            $builder = $this->db->table('property_statistics');
            
            if ($action === 'add') {
                $builder->set('total_favorites', 'total_favorites + 1', false);
            } else {
                $builder->set('total_favorites', 'total_favorites - 1', false);
            }
            
            $builder->where('property_id', $propertyId);
            $builder->update();
            
            // Eğer kayıt yoksa oluştur
            if ($this->db->affectedRows() == 0) {
                $this->createPropertyStatisticsRecord($propertyId);
                if ($action === 'add') {
                    $builder->set('total_favorites', 1);
                    $builder->where('property_id', $propertyId);
                    $builder->update();
                }
            }
            
            // 3. User statistics güncelle
            $this->updateUserStatistics($userId, $action === 'add' ? 'favorite' : 'unfavorite');
            
            // 4. Agent portfolio stats güncelle
            $this->updateAgentPortfolioStats($propertyId);
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'recordFavoriteAction error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Gizleme/gösterme kaydı
     */
    public function recordHiddenAction($propertyId, $userId, $action = 'hide')
    {
        try {
            // 1. Activity log'a kaydet
            $this->logActivity([
                'user_id' => $userId,
                'activity_type' => $action === 'hide' ? 'hide' : 'unhide',
                'property_id' => $propertyId
            ]);
            
            // 2. Property statistics güncelle
            $builder = $this->db->table('property_statistics');
            
            if ($action === 'hide') {
                $builder->set('total_hidden', 'total_hidden + 1', false);
            } else {
                $builder->set('total_hidden', 'total_hidden - 1', false);
            }
            
            $builder->where('property_id', $propertyId);
            $builder->update();
            
            // Eğer kayıt yoksa oluştur
            if ($this->db->affectedRows() == 0) {
                $this->createPropertyStatisticsRecord($propertyId);
                if ($action === 'hide') {
                    $builder->set('total_hidden', 1);
                    $builder->where('property_id', $propertyId);
                    $builder->update();
                }
            }
            
            // 3. User statistics güncelle
            $this->updateUserStatistics($userId, $action === 'hide' ? 'hide' : 'unhide');
            
            // 4. Agent portfolio stats güncelle
            $this->updateAgentPortfolioStats($propertyId);
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'recordHiddenAction error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Soru/mesaj kaydı
     */
    public function recordEnquiry($propertyId, $userId = null)
    {
        try {
            // 1. Activity log'a kaydet
            $this->logActivity([
                'user_id' => $userId,
                'activity_type' => 'enquiry',
                'property_id' => $propertyId
            ]);
            
            // 2. Property statistics güncelle
            $builder = $this->db->table('property_statistics');
            $builder->set('total_enquiries', 'total_enquiries + 1', false);
            $builder->where('property_id', $propertyId);
            $builder->update();
            
            // Eğer kayıt yoksa oluştur
            if ($this->db->affectedRows() == 0) {
                $this->createPropertyStatisticsRecord($propertyId);
                $builder->set('total_enquiries', 1);
                $builder->where('property_id', $propertyId);
                $builder->update();
            }
            
            // 3. User statistics güncelle
            if ($userId) {
                $this->updateUserStatistics($userId, 'enquiry');
            }
            
            // 4. Agent portfolio stats güncelle
            $this->updateAgentPortfolioStats($propertyId);
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'recordEnquiry error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Arama kaydı
     */
    public function recordSearch($userId, $searchCriteria, $resultsCount = 0)
    {
        try {
            // 1. Search history'e kaydet
            $builder = $this->db->table('user_search_history');
            $builder->insert([
                'user_id' => $userId,
                'session_id' => session_id(),
                'search_criteria' => json_encode($searchCriteria),
                'search_query' => http_build_query($searchCriteria),
                'results_count' => $resultsCount,
                'created_at' => date('Y-m-d H:i:s')
            ]);
            
            // 2. Activity log'a kaydet
            $this->logActivity([
                'user_id' => $userId,
                'activity_type' => 'search',
                'activity_data' => json_encode($searchCriteria)
            ]);
            
            // 3. User statistics güncelle
            if ($userId) {
                $this->updateUserStatistics($userId, 'search');
            }
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'recordSearch error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Uygulama açılış kaydı
     */
    public function recordAppOpen($userId)
    {
        try {
            // 1. Activity log'a kaydet
            $this->logActivity([
                'user_id' => $userId,
                'activity_type' => 'app_open'
            ]);
            
            // 2. User statistics güncelle
            $builder = $this->db->table('user_statistics');
            $builder->set('total_app_opens', 'total_app_opens + 1', false);
            $builder->set('last_active_at', date('Y-m-d H:i:s'));
            $builder->where('user_id', $userId);
            $builder->update();
            
            // Eğer kayıt yoksa oluştur
            if ($this->db->affectedRows() == 0) {
                $this->createUserStatisticsRecord($userId);
                $builder->set('total_app_opens', 1);
                $builder->set('last_active_at', date('Y-m-d H:i:s'));
                $builder->where('user_id', $userId);
                $builder->update();
            }
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'recordAppOpen error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Görüntüleme süresi güncelleme
     */
public function updateViewDuration($viewId, $duration)
{
    try {
        // ✅ Sadece tek bir UPDATE query - çok hızlı
        $builder = $this->db->table('property_views');
        $builder->set('view_duration', $duration);
        $builder->set('updated_at', date('Y-m-d H:i:s'));
        $builder->where('id', $viewId);
        $result = $builder->update();
        
        if ($result) {
            // ✅ Property statistics'deki average'ı güncelle (async olarak)
            $this->updateAverageViewDuration($viewId);
        }
        
        return $result;
        
    } catch (\Exception $e) {
        log_message('error', 'updateViewDuration error: ' . $e->getMessage());
        return false;
    }
}
    
      /**
     * İlan görüntüleme kaydı - GÜNCELLENMİŞ
     */
    public function recordPropertyView($data)
    {
        try {
            log_message('info', '📊 AnalyticsModel::recordPropertyView - Data: ' . json_encode($data));
            
            // 1. property_views tablosuna kayıt ekle
            $viewData = [
                'property_id' => $data['property_id'],
                'user_id' => $data['user_id'] ?? null,
                'session_id' => $data['session_id'] ?? session_id(),
                'ip_address' => $data['ip_address'] ?? $_SERVER['REMOTE_ADDR'],
                'user_agent' => $data['user_agent'] ?? $_SERVER['HTTP_USER_AGENT'],
                'platform' => $data['platform'] ?? 'unknown', // ✅ Platform ekle
                'device_type' => $this->detectDeviceType($data['user_agent'] ?? $_SERVER['HTTP_USER_AGENT']),
                'referrer' => $data['referrer'] ?? ($_SERVER['HTTP_REFERER'] ?? null),
                'is_unique' => $this->isUniqueView($data['property_id'], $data['user_id'] ?? null, $data['session_id'] ?? null),
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            // ✅ Eğer data'dan device_type geliyorsa, onu kullan (öncelikli)
            if (isset($data['device_type']) && !empty($data['device_type'])) {
                $viewData['device_type'] = $data['device_type'];
            }
            
            log_message('debug', 'View data prepared for insert: ' . json_encode($viewData));
            
            $builder = $this->db->table('property_views');
            $insertResult = $builder->insert($viewData);
            
            if (!$insertResult) {
                log_message('error', 'Database insert failed: ' . json_encode($this->db->error()));
                return false;
            }
            
            $viewId = $this->db->insertID();
            log_message('info', '✅ View inserted - View ID: ' . $viewId . 
                              ', User ID: ' . ($data['user_id'] ?? 'NULL') . 
                              ', IP: ' . $data['ip_address'] . 
                              ', Platform: ' . ($data['platform'] ?? 'unknown') . 
                              ', Device: ' . $viewData['device_type']);
            
            // 2. property_statistics tablosunu güncelle
            $this->updatePropertyStatistics($data['property_id']);
            
            // 3. user_statistics tablosunu güncelle
            if (!empty($data['user_id'])) {
                $this->updateUserStatistics($data['user_id'], 'view');
            }
            
            // 4. activity_log'a kaydet
            $this->logActivity([
                'user_id' => $data['user_id'] ?? null,
                'session_id' => $data['session_id'] ?? session_id(),
                'activity_type' => 'view',
                'property_id' => $data['property_id'],
                'platform' => $data['platform'] ?? 'unknown', // ✅ Platform ekle
                'device_type' => $viewData['device_type'],
                'activity_data' => json_encode($viewData)
            ]);
            
            // 5. Agent portfolio stats güncelle
            $this->updateAgentPortfolioStats($data['property_id']);
            
            return $viewId;
            
        } catch (\Exception $e) {
            log_message('error', 'recordPropertyView error: ' . $e->getMessage());
            return false;
        }
    }
    
    // detectDeviceType metodu - Controller'dan model'e taşıyalım
    private function detectDeviceType($userAgent)
    {
        $userAgent = strtolower($userAgent);
        
        // SatisaHazir custom user agent kontrolü
        if (strpos($userAgent, 'satisahazir-android') !== false) {
            if (strpos($userAgent, 'tab') !== false || strpos($userAgent, 'pad') !== false) {
                return 'tablet';
            }
            return 'mobile';
        }
        
        if (strpos($userAgent, 'satisahazir-ios') !== false) {
            if (strpos($userAgent, 'ipad') !== false) {
                return 'tablet';
            }
            return 'mobile';
        }
        
        // Genel mobil pattern'ler
        if (preg_match('/(android|iphone|ipod|mobile|webos|blackberry|bb10|windows phone)/i', $userAgent)) {
            return 'mobile';
        }
        
        // Tablet pattern'ler
        if (preg_match('/(ipad|tablet|kindle|playbook|nexus 7|nexus 10)/i', $userAgent)) {
            return 'tablet';
        }
        
        return 'mobile'; // Default
    }

    // Diğer metodlar aynı kalacak...
    
    private function isUniqueView($propertyId, $userId = null, $sessionId = null)
    {
        $builder = $this->db->table('property_views');
        $builder->where('property_id', $propertyId);
        
        if ($userId) {
            $builder->where('user_id', $userId);
        } else {
            $builder->where('session_id', $sessionId);
        }
        
        $builder->where('created_at >', date('Y-m-d H:i:s', strtotime('-24 hours')));
        
        return $builder->countAllResults() == 0;
    }
    
    private function updatePropertyStatistics($propertyId)
    {
        try {
            $builder = $this->db->table('property_statistics');
            $exists = $builder->where('property_id', $propertyId)->countAllResults() > 0;
            
            if (!$exists) {
                $this->createPropertyStatisticsRecord($propertyId);
            }
            
            $builder = $this->db->table('property_views');
            $totalViews = $builder->where('property_id', $propertyId)->countAllResults();
            
            $builder = $this->db->table('property_views');
            $builder->select('COUNT(DISTINCT COALESCE(user_id, session_id)) as unique_count');
            $builder->where('property_id', $propertyId);
            $uniqueViews = $builder->get()->getRow()->unique_count;
            
            $builder = $this->db->table('property_statistics');
            $builder->set('total_views', $totalViews);
            $builder->set('unique_views', $uniqueViews);
            $builder->set('last_viewed_at', date('Y-m-d H:i:s'));
            $builder->where('property_id', $propertyId);
            $builder->update();
            
        } catch (\Exception $e) {
            log_message('error', 'updatePropertyStatistics error: ' . $e->getMessage());
        }
    }
    
    private function updateUserStatistics($userId, $action)
    {
        try {
            $builder = $this->db->table('user_statistics');
            $exists = $builder->where('user_id', $userId)->countAllResults() > 0;
            
            if (!$exists) {
                $this->createUserStatisticsRecord($userId);
            }
            
            $builder = $this->db->table('user_statistics');
            
            switch ($action) {
                case 'view':
                    $builder->set('total_properties_viewed', 'total_properties_viewed + 1', false);
                    break;
                case 'favorite':
                    $builder->set('total_favorites', 'total_favorites + 1', false);
                    break;
                case 'unfavorite':
                    $builder->set('total_favorites', 'total_favorites - 1', false);
                    break;
                case 'hide':
                    $builder->set('total_hidden', 'total_hidden + 1', false);
                    break;
                case 'unhide':
                    $builder->set('total_hidden', 'total_hidden - 1', false);
                    break;
                case 'enquiry':
                    $builder->set('total_enquiries', 'total_enquiries + 1', false);
                    break;
                case 'search':
                    $builder->set('total_searches', 'total_searches + 1', false);
                    break;
            }
            
            $builder->set('last_active_at', date('Y-m-d H:i:s'));
            $builder->where('user_id', $userId);
            $builder->update();
            
        } catch (\Exception $e) {
            log_message('error', 'updateUserStatistics error: ' . $e->getMessage());
        }
    }
    
    private function updateAgentPortfolioStats($propertyId)
    {
        try {
            $builder = $this->db->table('property_user');
            $propertyUser = $builder->where('property_id', $propertyId)->get()->getRowArray();
            
            if (!$propertyUser) {
                return;
            }
            
            $agentId = $propertyUser['user_id'];
            
            $builder = $this->db->table('agent_portfolio_stats');
            $exists = $builder->where('agent_id', $agentId)
                             ->where('property_id', $propertyId)
                             ->countAllResults() > 0;
            
            if (!$exists) {
                $builder->insert([
                    'agent_id' => $agentId,
                    'property_id' => $propertyId,
                    'total_views' => 0,
                    'unique_views' => 0,
                    'total_favorites' => 0,
                    'total_hidden' => 0,
                    'total_enquiries' => 0,
                    'conversion_rate' => 0
                ]);
            }
            
            $propertyStats = $this->getPropertyStatistics($propertyId);
            
            $builder = $this->db->table('agent_portfolio_stats');
            $builder->set('total_views', $propertyStats['total_views']);
            $builder->set('unique_views', $propertyStats['unique_views']);
            $builder->set('total_favorites', $propertyStats['total_favorites']);
            $builder->set('total_hidden', $propertyStats['total_hidden']);
            $builder->set('total_enquiries', $propertyStats['total_enquiries']);
            
            if ($propertyStats['unique_views'] > 0) {
                $conversionRate = ($propertyStats['total_enquiries'] / $propertyStats['unique_views']) * 100;
                $builder->set('conversion_rate', round($conversionRate, 2));
            }
            
            $builder->where('agent_id', $agentId);
            $builder->where('property_id', $propertyId);
            $builder->update();
            
        } catch (\Exception $e) {
            log_message('error', 'updateAgentPortfolioStats error: ' . $e->getMessage());
        }
    }
    
    private function logActivity($data)
    {
        try {
            $activityData = [
                'user_id' => $data['user_id'] ?? null,
                'session_id' => $data['session_id'] ?? session_id(),
                'activity_type' => $data['activity_type'] ?? 'unknown',
                'property_id' => $data['property_id'] ?? null,
                'platform' => $data['platform'] ?? 'unknown', // ✅ Platform ekle
                'device_type' => $data['device_type'] ?? 'mobile', // ✅ Device type ekle
                'activity_data' => $data['activity_data'] ?? null,
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            $builder = $this->db->table('user_activity_log');
            $builder->insert($activityData);
            
        } catch (\Exception $e) {
            log_message('error', 'logActivity error: ' . $e->getMessage());
        }
    }
    
    private function createPropertyStatisticsRecord($propertyId)
    {
        try {
            $builder = $this->db->table('property_statistics');
            $builder->insert([
                'property_id' => $propertyId,
                'total_views' => 0,
                'unique_views' => 0,
                'total_favorites' => 0,
                'total_hidden' => 0,
                'total_enquiries' => 0,
                'avg_view_duration' => 0
            ]);
        } catch (\Exception $e) {
            log_message('error', 'createPropertyStatisticsRecord error: ' . $e->getMessage());
        }
    }
    
    private function createUserStatisticsRecord($userId)
    {
        try {
            $builder = $this->db->table('user_statistics');
            $builder->insert([
                'user_id' => $userId,
                'total_properties_viewed' => 0,
                'total_favorites' => 0,
                'total_hidden' => 0,
                'total_searches' => 0,
                'total_enquiries' => 0,
                'total_app_opens' => 0,
                'total_time_spent' => 0
            ]);
        } catch (\Exception $e) {
            log_message('error', 'createUserStatisticsRecord error: ' . $e->getMessage());
        }
    }

    public function getPropertyStatistics($propertyId)
    {
        try {
            $builder = $this->db->table('property_statistics');
            $stats = $builder->where('property_id', $propertyId)->get()->getRowArray();
            
            if (!$stats) {
                return [
                    'total_views' => 0,
                    'unique_views' => 0,
                    'total_favorites' => 0,
                    'total_hidden' => 0,
                    'total_enquiries' => 0,
                    'avg_view_duration' => 0
                ];
            }
            
            return $stats;
            
        } catch (\Exception $e) {
            log_message('error', 'getPropertyStatistics error: ' . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Kullanıcı istatistiklerini getir
     */
    public function getUserStatistics($userId)
    {
        try {
            $builder = $this->db->table('user_statistics');
            $stats = $builder->where('user_id', $userId)->get()->getRowArray();
            
            if (!$stats) {
                $this->createUserStatisticsRecord($userId);
                $stats = $builder->where('user_id', $userId)->get()->getRowArray();
            }
            
            // Detaylı bilgileri ekle
            $stats['viewed_properties'] = $this->getUserViewedProperties($userId);
            $stats['favorite_properties'] = $this->getUserFavoriteProperties($userId);
            $stats['hidden_properties'] = $this->getUserHiddenProperties($userId);
            $stats['search_history'] = $this->getUserSearchHistory($userId);
            
            return $stats;
            
        } catch (\Exception $e) {
            log_message('error', 'getUserStatistics error: ' . $e->getMessage());
            return null;
        }
    }
    
public function getAgentPortfolioStatistics($agentId)
{
    try {
        log_message('info', 'getAgentPortfolioStatistics başlatıldı - Agent ID: ' . $agentId);
        
        $stats = [];
        
        // 1. Doğrudan SQL ile agent'ın ilanlarını ve analytics verilerini getir
        $sql = "SELECT 
            p.id as property_id,
            p.address,
            pl.json_object,
            pl.field_36_int as price,
            COALESCE(ps.total_views, 0) as total_views,
            COALESCE(ps.unique_views, 0) as unique_views,
            COALESCE(ps.total_favorites, 0) as total_favorites,
            COALESCE(ps.total_enquiries, 0) as total_enquiries,
            COALESCE(ps.avg_view_duration, 0) as avg_view_duration
        FROM property_user pu
        JOIN property p ON p.id = pu.property_id
        JOIN property_lang pl ON pl.property_id = p.id
        LEFT JOIN property_statistics ps ON ps.property_id = p.id
        WHERE pu.user_id = ?
        AND p.is_activated = '1'
        AND pl.language_id = ?";
        
        $listings = $this->db->query($sql, [$agentId, getenv('LANGUAGE_ID')])->getResultArray();
        
        if (empty($listings)) {
            log_message('info', 'Agent için ilan bulunamadı');
            return $this->getDefaultAgentStats();
        }
        
        // 2. Temel istatistikleri hesapla
        $stats['total_listings'] = count($listings);
        $stats['total_views'] = 0;
        $stats['unique_views'] = 0;
        $stats['total_favorites'] = 0;
        $stats['total_enquiries'] = 0;
        $totalDuration = 0;
        $durationCount = 0;
        
        $categories = [];
        $locations = [];
        $priceRanges = [
            '0-40K' => 0,        // 0-40.000
            '40K-80K' => 0,      // 40.000-80.000  
            '80K-120K' => 0,     // 80.000-120.000
            '120K-160K' => 0,    // 120.000-160.000
            '160K-200K' => 0,    // 160.000-200.000
            '200K+' => 0         // 200.000 üzeri
        ];
        
        foreach ($listings as $listing) {
            // Analytics topla
            $stats['total_views'] += intval($listing['total_views']);
            $stats['unique_views'] += intval($listing['unique_views']);
            $stats['total_favorites'] += intval($listing['total_favorites']);
            $stats['total_enquiries'] += intval($listing['total_enquiries']);
            
            if ($listing['avg_view_duration'] > 0) {
                $totalDuration += intval($listing['avg_view_duration']);
                $durationCount++;
            }
            
            // JSON verisini parse et
            $jsonData = json_decode($listing['json_object'], true);
            
            // Kategori dağılımı
            if (isset($jsonData['field_79'])) {
                $category = trim(str_replace(' -', '', $jsonData['field_79']));
                $categories[$category] = ($categories[$category] ?? 0) + 1;
            }
            
            // Lokasyon dağılımı - Sadece ilçe adı
            if (isset($jsonData['field_64']) && !is_numeric($jsonData['field_64'])) {
                $fullLocation = trim($jsonData['field_64']);
                // "Türkiye - İstanbul Anadolu - Kadıköy" -> "Kadıköy"
                $locationParts = explode('-', $fullLocation);
                $district = trim($locationParts[2]); // Son kısım = İlçe
                
                if (!empty($district)) {
                    $locations[$district] = ($locations[$district] ?? 0) + 1;
                }
            }
            // Eğer field_64 ID ise TreefieldModel'den çevir
            elseif (isset($jsonData['field_64']) && is_numeric($jsonData['field_64'])) {
                $treeModel = model('App\Models\TreefieldModel');
                $locationData = $treeModel->where('treefield_id', $jsonData['field_64'])
                                         ->where('language_id', getenv('LANGUAGE_ID'))
                                         ->first();
                if ($locationData) {
                    $fullLocation = $locationData['value'];
                    $locationParts = explode(' - ', $fullLocation);
                    $district = trim(end($locationParts));
                    
                    if (!empty($district)) {
                        $locations[$district] = ($locations[$district] ?? 0) + 1;
                    }
                }
            }
            // Fallback: address kullan
            elseif (!empty($listing['address'])) {
                // "Kadıköy Moda" -> "Kadıköy"
                $addressParts = explode(' ', trim($listing['address']));
                $district = $addressParts[0]; // İlk kelime genelde ilçe
                $locations[$district] = ($locations[$district] ?? 0) + 1;
            }
            
            // Fiyat dağılımı - 6 kategori (0-200K arası 5 parça + 200K üzeri)
            $price = intval($listing['price']);
            if ($price > 0) {
                if ($price <= 40000) {
                    $priceRanges['0-40K']++;
                } elseif ($price <= 80000) {
                    $priceRanges['40K-80K']++;
                } elseif ($price <= 120000) {
                    $priceRanges['80K-120K']++;
                } elseif ($price <= 160000) {
                    $priceRanges['120K-160K']++;
                } elseif ($price <= 200000) {
                    $priceRanges['160K-200K']++;
                } else { // 200K üzeri
                    $priceRanges['200K+']++;
                }
            }
        }
        
        // 3. Hesaplanan değerler
        $stats['avg_view_duration'] = $durationCount > 0 ? round($totalDuration / $durationCount) : 0;
        
        $stats['conversion_rate'] = $stats['total_views'] > 0 
            ? round(($stats['total_enquiries'] / $stats['total_views']) * 100, 2) . '%'
            : '0%';
        
        // 4. Kategoriler
        arsort($categories);
        $stats['categories'] = [];
        foreach ($categories as $category => $count) {
            $stats['categories'][] = ['category' => $category, 'count' => $count];
        }
        
        // 5. Lokasyonlar - Sadece ilçe adları
        arsort($locations);
        $stats['locations'] = [];
        $count = 0;
        foreach ($locations as $location => $listingCount) {
            if ($count >= 10) break;
            $stats['locations'][] = ['location' => $location, 'count' => $listingCount];
            $count++;
        }
        
        // 6. Fiyat dağılımı
        $stats['price_distribution'] = [];
        foreach ($priceRanges as $range => $count) {
            if ($count > 0) {
                $stats['price_distribution'][] = ['price_range' => $range, 'count' => $count];
            }
        }
        
        // 7. Performans karşılaştırması
        $agentAvg = $stats['total_listings'] > 0 ? ($stats['total_views'] / $stats['total_listings']) : 0;
        
        $builder = $this->db->table('property_statistics');
        $builder->selectAvg('total_views', 'system_avg');
        $systemAvgResult = $builder->get()->getRowArray();
        $systemAvg = floatval($systemAvgResult['system_avg'] ?? 50);
        
        $stats['performance_comparison'] = [
            'agent_avg_views' => round($agentAvg, 1),
            'system_avg_views' => round($systemAvg, 1),
            'performance_index' => $systemAvg > 0 ? round(($agentAvg / $systemAvg) * 100, 0) : 100
        ];
        
        // 8. Yanıt süresi
        $builder = $this->db->table('enquire');
        $builder->select('AVG(TIMESTAMPDIFF(HOUR, date, COALESCE(updated_at, NOW()))) as avg_hours');
        $builder->where('agent_id', $agentId);
        $responseResult = $builder->get()->getRowArray();
        
        if ($responseResult && $responseResult['avg_hours'] > 0) {
            $hours = round($responseResult['avg_hours']);
            $stats['avg_response_time'] = $hours < 24 ? $hours . ' saat' : round($hours / 24) . ' gün';
        } else {
            $stats['avg_response_time'] = 'N/A';
        }
        
        log_message('info', 'Agent portfolio stats başarıyla hesaplandı: ' . json_encode($stats));
        
        return $stats;
        
    } catch (\Exception $e) {
        log_message('error', 'getAgentPortfolioStatistics error: ' . $e->getMessage());
        return $this->getDefaultAgentStats();
    }
}

/**
 * Default stats
 */
private function getDefaultAgentStats()
{
    return [
        'total_listings' => 0,
        'total_views' => 0,
        'unique_views' => 0,
        'total_favorites' => 0,
        'total_enquiries' => 0,
        'conversion_rate' => '0%',
        'avg_response_time' => 'N/A',
        'categories' => [],
        'locations' => [],
        'price_distribution' => [],
        'performance_comparison' => [
            'agent_avg_views' => 0,
            'system_avg_views' => 0,
            'performance_index' => 100
        ]
    ];
}
    /**
     * Admin için tüm istatistikler
     */
    public function getAllStatistics($filters = [])
    {
        try {
            $stats = [];
            
            // 1. Genel istatistikler
            $stats['overview'] = [
                'total_properties' => $this->db->table('property')->countAllResults(),
                'total_users' => $this->db->table('user')->where('type', 'USER')->countAllResults(),
                'total_agents' => $this->db->table('user')->where('type', 'AGENT')->countAllResults(),
                'total_views' => $this->db->table('property_views')->countAllResults(),
                'total_favorites' => $this->db->table('favorites')->countAllResults(),
                'total_enquiries' => $this->db->table('enquire')->countAllResults()
            ];
            
            // 2. En çok görüntülenen ilanlar
            $builder = $this->db->table('property_statistics ps');
            $builder->select('ps.*');
            $builder->join('property_lang pl', 'pl.property_id = ps.property_id');
            $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
            $builder->orderBy('ps.total_views', 'DESC');
            $builder->limit(10);
            $stats['top_viewed'] = $builder->get()->getResultArray();
            
            // 3. En çok favorilenen ilanlar
            $builder = $this->db->table('property_statistics ps');
            $builder->select('ps.*');
            $builder->join('property_lang pl', 'pl.property_id = ps.property_id');
            $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
            $builder->orderBy('ps.total_favorites', 'DESC');
            $builder->limit(10);
            $stats['top_favorited'] = $builder->get()->getResultArray();
            
            // 4. En aktif kullanıcılar
            $builder = $this->db->table('user_statistics us');
            $builder->select('us.*, u.name_surname, u.mail');
            $builder->join('user u', 'u.id = us.user_id');
            $builder->orderBy('us.total_properties_viewed', 'DESC');
            $builder->limit(10);
            $stats['top_users'] = $builder->get()->getResultArray();
            
            // 5. Agent performansları
            $builder = $this->db->table('agent_portfolio_stats');
            $builder->select('agent_id, COUNT(property_id) as total_properties, 
                             SUM(total_views) as total_views, 
                             SUM(total_favorites) as total_favorites,
                             SUM(total_enquiries) as total_enquiries,
                             AVG(conversion_rate) as avg_conversion_rate');
            $builder->groupBy('agent_id');
            $stats['agent_performance'] = $builder->get()->getResultArray();
            
            // 6. Günlük/Haftalık/Aylık trendler
            if (!empty($filters['date_from']) && !empty($filters['date_to'])) {
                $stats['trends'] = $this->getStatisticsTrends($filters['date_from'], $filters['date_to']);
            }
            
            return $stats;
            
        } catch (\Exception $e) {
            log_message('error', 'getAllStatistics error: ' . $e->getMessage());
            return [];
        }
    }
    
  

    
/**
 * ✅ Property için ortalama view duration hesapla
 */
private function updateAverageViewDuration($viewId)
{
    try {
        // View ID'den property ID'yi al
        $builder = $this->db->table('property_views');
        $view = $builder->select('property_id')->where('id', $viewId)->get()->getRowArray();
        
        if (!$view) {
            return;
        }
        
        $propertyId = $view['property_id'];
        
        // Bu property için ortalama duration hesapla
        $builder = $this->db->table('property_views');
        $builder->select('AVG(view_duration) as avg_duration');
        $builder->where('property_id', $propertyId);
        $builder->where('view_duration >', 0); // Sadece 0'dan büyük olanlar
        $result = $builder->get()->getRow();
        
        $avgDuration = $result->avg_duration ?? 0;
        
        // Property statistics'i güncelle
        $builder = $this->db->table('property_statistics');
        $builder->set('avg_view_duration', round($avgDuration));
        $builder->where('property_id', $propertyId);
        $builder->update();
        
        log_message('debug', '📊 Average view duration updated for property ' . 
                           $propertyId . ': ' . round($avgDuration) . 's');
        
    } catch (\Exception $e) {
        log_message('error', 'updateAverageViewDuration error: ' . $e->getMessage());
    }
}
    

    

    
    private function getUserViewedProperties($userId, $limit = 50)
    {
        try {
            $builder = $this->db->table('property_views pv');
            $builder->select('pv.property_id,   COUNT(*) as view_count, MAX(pv.created_at) as last_viewed');
            $builder->join('property_lang pl', 'pl.property_id = pv.property_id');
            $builder->where('pv.user_id', $userId);
            $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
            $builder->groupBy('pv.property_id');
            $builder->orderBy('last_viewed', 'DESC');
            $builder->limit($limit);
            
            return $builder->get()->getResultArray();
            
        } catch (\Exception $e) {
            log_message('error', 'getUserViewedProperties error: ' . $e->getMessage());
            return [];
        }
    }
    
    private function getUserFavoriteProperties($userId)
    {
        try {
            $builder = $this->db->table('favorites f');
            $builder->select('f.property_id,   f.date_saved');
            $builder->join('property_lang pl', 'pl.property_id = f.property_id');
            $builder->where('f.user_id', $userId);
            $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
            $builder->orderBy('f.date_saved', 'DESC');
            
            return $builder->get()->getResultArray();
            
        } catch (\Exception $e) {
            log_message('error', 'getUserFavoriteProperties error: ' . $e->getMessage());
            return [];
        }
    }
    
    private function getUserHiddenProperties($userId)
    {
        try {
            $builder = $this->db->table('hiddens h');
            $builder->select('h.property_id,   h.date_saved');
            $builder->join('property_lang pl', 'pl.property_id = h.property_id');
            $builder->where('h.user_id', $userId);
            $builder->where('pl.language_id', getenv('LANGUAGE_ID'));
            $builder->orderBy('h.date_saved', 'DESC');
            
            return $builder->get()->getResultArray();
            
        } catch (\Exception $e) {
            log_message('error', 'getUserHiddenProperties error: ' . $e->getMessage());
            return [];
        }
    }
    
    private function getUserSearchHistory($userId, $limit = 20)
    {
        try {
            $builder = $this->db->table('user_search_history');
            $builder->where('user_id', $userId);
            $builder->orderBy('created_at', 'DESC');
            $builder->limit($limit);
            
            return $builder->get()->getResultArray();
            
        } catch (\Exception $e) {
            log_message('error', 'getUserSearchHistory error: ' . $e->getMessage());
            return [];
        }
    }
    
    private function getStatisticsTrends($dateFrom, $dateTo)
    {
        try {
            $trends = [];
            
            // Günlük görüntüleme trendi
            $sql = "SELECT DATE(created_at) as date, COUNT(*) as total_views, 
                    COUNT(DISTINCT COALESCE(user_id, session_id)) as unique_views
                    FROM property_views 
                    WHERE created_at BETWEEN ? AND ?
                    GROUP BY DATE(created_at)
                    ORDER BY date";
            
            $trends['daily_views'] = $this->db->query($sql, [$dateFrom, $dateTo])->getResultArray();
            
            // Günlük favori trendi
            $sql = "SELECT DATE(created_at) as date, 
                    SUM(CASE WHEN activity_type = 'favorite' THEN 1 ELSE 0 END) as favorites_added,
                    SUM(CASE WHEN activity_type = 'unfavorite' THEN 1 ELSE 0 END) as favorites_removed
                    FROM user_activity_log
                    WHERE created_at BETWEEN ? AND ?
                    AND activity_type IN ('favorite', 'unfavorite')
                    GROUP BY DATE(created_at)
                    ORDER BY date";
            
            $trends['daily_favorites'] = $this->db->query($sql, [$dateFrom, $dateTo])->getResultArray();
            
            // Günlük soru trendi
            $sql = "SELECT DATE(created_at) as date, COUNT(*) as total_enquiries
                    FROM user_activity_log
                    WHERE created_at BETWEEN ? AND ?
                    AND activity_type = 'enquiry'
                    GROUP BY DATE(created_at)
                    ORDER BY date";
            
            $trends['daily_enquiries'] = $this->db->query($sql, [$dateFrom, $dateTo])->getResultArray();
            
            return $trends;
            
        } catch (\Exception $e) {
            log_message('error', 'getStatisticsTrends error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Günlük istatistikleri kaydet (Cron job için)
     */
    public function saveDailyStatistics($date = null)
    {
        if (!$date) {
            $date = date('Y-m-d', strtotime('-1 day'));
        }
        
        try {
            // Property daily stats
            $sql = "INSERT INTO daily_statistics (date, entity_type, entity_id, metrics)
                    SELECT ?, 'property', property_id, 
                    JSON_OBJECT(
                        'views', COUNT(*),
                        'unique_views', COUNT(DISTINCT COALESCE(user_id, session_id)),
                        'avg_duration', AVG(view_duration)
                    )
                    FROM property_views
                    WHERE DATE(created_at) = ?
                    GROUP BY property_id
                    ON DUPLICATE KEY UPDATE metrics = VALUES(metrics)";
            
            $this->db->query($sql, [$date, $date]);
            
            // User daily stats
            $sql = "INSERT INTO daily_statistics (date, entity_type, entity_id, metrics)
                    SELECT ?, 'user', user_id,
                    JSON_OBJECT(
                        'activities', COUNT(*),
                        'views', SUM(CASE WHEN activity_type = 'view' THEN 1 ELSE 0 END),
                        'searches', SUM(CASE WHEN activity_type = 'search' THEN 1 ELSE 0 END),
                        'favorites', SUM(CASE WHEN activity_type = 'favorite' THEN 1 ELSE 0 END),
                        'enquiries', SUM(CASE WHEN activity_type = 'enquiry' THEN 1 ELSE 0 END)
                    )
                    FROM user_activity_log
                    WHERE DATE(created_at) = ?
                    AND user_id IS NOT NULL
                    GROUP BY user_id
                    ON DUPLICATE KEY UPDATE metrics = VALUES(metrics)";
            
            $this->db->query($sql, [$date, $date]);
            
            // System daily stats
            $systemMetrics = [
                'total_views' => $this->db->table('property_views')
                    ->where('DATE(created_at)', $date)
                    ->countAllResults(),
                'unique_users' => $this->db->table('property_views')
                    ->select('COUNT(DISTINCT user_id) as count')
                    ->where('DATE(created_at)', $date)
                    ->where('user_id IS NOT NULL')
                    ->get()->getRow()->count,
                'total_searches' => $this->db->table('user_search_history')
                    ->where('DATE(created_at)', $date)
                    ->countAllResults(),
                'total_enquiries' => $this->db->table('user_activity_log')
                    ->where('DATE(created_at)', $date)
                    ->where('activity_type', 'enquiry')
                    ->countAllResults()
            ];
            
            $builder = $this->db->table('daily_statistics');
            $builder->replace([
                'date' => $date,
                'entity_type' => 'system',
                'entity_id' => null,
                'metrics' => json_encode($systemMetrics)
            ]);
            
            return true;
            
        } catch (\Exception $e) {
            log_message('error', 'saveDailyStatistics error: ' . $e->getMessage());
            return false;
        }
    }
}