<?php
/**
 * SEO Analytics Tracker
 * Tracks and analyzes SEO performance metrics
 */

class SEOAnalytics {
    private $db;
    private $table = 'seo_analytics';
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    /**
     * Get performance overview
     */
    public function getPerformanceOverview($days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    SUM(impressions) as total_impressions,
                    SUM(clicks) as total_clicks,
                    CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as avg_ctr,
                    AVG(avg_position) as avg_position,
                    AVG(bounce_rate) as avg_bounce_rate,
                    AVG(avg_session_duration) as avg_session_duration,
                    SUM(conversion_rate) / COUNT(*) as avg_conversion_rate
                FROM {$this->table}
                WHERE date >= '$date'";
        
        $result = $this->db->query($sql);
        return $result ? $result->fetch_assoc() : null;
    }
    
    /**
     * Get top performing pages
     */
    public function getTopPerformingPages($limit = 10, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    sa.page_type,
                    sa.seo_id,
                    SUM(sa.clicks) as total_clicks,
                    SUM(sa.impressions) as total_impressions,
                    CASE WHEN SUM(sa.impressions) > 0 THEN (SUM(sa.clicks) / SUM(sa.impressions)) * 100 ELSE 0 END as avg_ctr,
                    AVG(sa.avg_position) as avg_position,
                    MAX(sa.date) as last_updated
                FROM {$this->table} sa
                WHERE sa.date >= '$date'
                GROUP BY sa.seo_id
                ORDER BY total_clicks DESC
                LIMIT $limit";
        
        return $this->db->query($sql);
    }
    
    /**
     * Get trending keywords
     */
    public function getTrendingKeywords($limit = 20, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    top_keywords,
                    COUNT(*) as frequency,
                    CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as avg_ctr,
                    SUM(clicks) as total_clicks
                FROM {$this->table}
                WHERE date >= '$date' AND top_keywords IS NOT NULL
                GROUP BY top_keywords
                ORDER BY frequency DESC
                LIMIT $limit";
        
        return $this->db->query($sql);
    }
    
    /**
     * Get position tracking data
     */
    public function getPositionTracking($seo_id, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    date,
                    avg_position,
                    impressions,
                    clicks,
                    CASE WHEN impressions > 0 THEN (clicks / impressions) * 100 ELSE 0 END as click_through_rate
                FROM {$this->table}
                WHERE seo_id = $seo_id AND date >= '$date'
                ORDER BY date ASC";
        
        return $this->db->query($sql);
    }
    
    /**
     * Get traffic analysis by source
     */
    public function getTrafficBySource($days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    traffic_source,
                    COUNT(*) as pages,
                    SUM(impressions) as total_impressions,
                    SUM(clicks) as total_clicks,
                    CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as avg_ctr,
                    AVG(bounce_rate) as avg_bounce_rate
                FROM {$this->table}
                WHERE date >= '$date'
                GROUP BY traffic_source";
        
        return $this->db->query($sql);
    }
    
    /**
     * Get conversion tracking
     */
    public function getConversionMetrics($days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    date,
                    SUM(clicks) as total_clicks,
                    AVG(conversion_rate) as avg_conversion_rate,
                    COUNT(DISTINCT seo_id) as pages_converting
                FROM {$this->table}
                WHERE date >= '$date'
                GROUP BY date
                ORDER BY date ASC";
        
        return $this->db->query($sql);
    }
    
    /**
     * Calculate growth metrics
     */
    public function getGrowthMetrics($days = 30) {
        $current_date = date('Y-m-d');
        $past_date = date('Y-m-d', strtotime("-$days days"));
        $comparison_date = date('Y-m-d', strtotime("-" . ($days * 2) . " days"));
        
        // Current period
        $current = $this->db->query(
            "SELECT 
                SUM(impressions) as impressions,
                SUM(clicks) as clicks,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as ctr
            FROM {$this->table}
            WHERE date BETWEEN '$past_date' AND '$current_date'"
        )->fetch_assoc();
        
        // Previous period
        $previous = $this->db->query(
            "SELECT 
                SUM(impressions) as impressions,
                SUM(clicks) as clicks,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as ctr
            FROM {$this->table}
            WHERE date BETWEEN '$comparison_date' AND '$past_date'"
        )->fetch_assoc();
        
        return [
            'impressions_growth' => $this->calculateGrowth($current['impressions'], $previous['impressions']),
            'clicks_growth' => $this->calculateGrowth($current['clicks'], $previous['clicks']),
            'ctr_growth' => $this->calculateGrowth($current['ctr'], $previous['ctr']),
            'current' => $current,
            'previous' => $previous
        ];
    }
    
    /**
     * Calculate percentage growth
     */
    private function calculateGrowth($current, $previous) {
        if ($previous == 0 || $previous === null) {
            return $current > 0 ? 100 : 0;
        }
        
        return round((($current - $previous) / $previous) * 100, 2);
    }
    
    /**
     * Get pages needing attention
     */
    public function getPagesNeedingAttention($days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    seo_id,
                    SUM(impressions) as impressions,
                    SUM(clicks) as clicks,
                    CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) * 100 ELSE 0 END as ctr,
                    AVG(avg_position) as avg_position
                FROM {$this->table}
                WHERE date >= '$date'
                GROUP BY seo_id
                HAVING 
                    impressions > 0 AND clicks = 0 OR
                    (clicks / impressions) < 0.01 OR
                    avg_position > 10
                ORDER BY impressions DESC";
        
        return $this->db->query($sql);
    }
    
    /**
     * Get keyword performance
     */
    public function getKeywordPerformance($keyword, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        $keyword_escaped = $this->db->real_escape_string($keyword);
        
        $sql = "SELECT 
                    date,
                    impressions,
                    clicks,
                    CASE WHEN impressions > 0 THEN (clicks / impressions) * 100 ELSE 0 END as click_through_rate,
                    avg_position,
                    traffic_source
                FROM {$this->table}
                WHERE top_keywords LIKE '%$keyword_escaped%' AND date >= '$date'
                ORDER BY date ASC";
        
        return $this->db->query($sql);
    }
    
    /**
     * Generate SEO health score
     */
    public function generateHealthScore($days = 30) {
        $overview = $this->getPerformanceOverview($days);
        
        if (!$overview) {
            return 0;
        }
        
        $score = 0;
        
        // Impressions (20 points)
        if ($overview['total_impressions'] > 1000) {
            $score += 20;
        } elseif ($overview['total_impressions'] > 100) {
            $score += 10;
        }
        
        // Click-through rate (20 points)
        if ($overview['avg_ctr'] > 0.05) {
            $score += 20;
        } elseif ($overview['avg_ctr'] > 0.03) {
            $score += 15;
        } elseif ($overview['avg_ctr'] > 0.01) {
            $score += 10;
        }
        
        // Average position (20 points)
        if ($overview['avg_position'] < 5) {
            $score += 20;
        } elseif ($overview['avg_position'] < 10) {
            $score += 15;
        } elseif ($overview['avg_position'] < 20) {
            $score += 10;
        }
        
        // Bounce rate (20 points)
        if ($overview['avg_bounce_rate'] < 40) {
            $score += 20;
        } elseif ($overview['avg_bounce_rate'] < 60) {
            $score += 15;
        } elseif ($overview['avg_bounce_rate'] < 80) {
            $score += 10;
        }
        
        // Conversion rate (20 points)
        if ($overview['avg_conversion_rate'] > 0.05) {
            $score += 20;
        } elseif ($overview['avg_conversion_rate'] > 0.02) {
            $score += 15;
        } elseif ($overview['avg_conversion_rate'] > 0) {
            $score += 10;
        }
        
        return min(100, $score);
    }
    
    /**
     * Generate analytics report
     */
    public function generateReport($days = 30) {
        $overview = $this->getPerformanceOverview($days);
        $growth = $this->getGrowthMetrics($days);
        $top_pages = $this->getTopPerformingPages(5, $days);
        $health_score = $this->generateHealthScore($days);
        
        return [
            'period' => "$days days",
            'health_score' => $health_score,
            'overview' => $overview,
            'growth' => $growth,
            'top_pages' => $top_pages,
            'generated_at' => date('Y-m-d H:i:s')
        ];
    }
    
    /**
     * Get chart data
     */
    public function getChartData($metric, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT 
                    date,
                    " . $this->db->real_escape_string($metric) . " as value
                FROM {$this->table}
                WHERE date >= '$date'
                GROUP BY date
                ORDER BY date ASC";
        
        $result = $this->db->query($sql);
        $data = [];
        
        while ($row = $result->fetch_assoc()) {
            $data[] = [
                'date' => $row['date'],
                'value' => (float)$row['value']
            ];
        }
        
        return $data;
    }
}
?>
