<?php
/**
 * SEO Management Plugin - Core Functionality
 * Comprehensive SEO tool similar to Yoast with meta tag editing, readability analysis, keyword optimization, and schema markup
 */

class SEOPlugin {
    private $db;
    private $table = 'seo_settings';
    private $analytics_table = 'seo_analytics';
    
    public function __construct($db_config) {
        $this->db = new mysqli(
            $db_config['host'],
            $db_config['user'],
            $db_config['password'],
            $db_config['database']
        );
        
        if ($this->db->connect_error) {
            die('Connection failed: ' . $this->db->connect_error);
        }
        
        $this->initializeTables();
    }
    
    /**
     * Initialize database tables for SEO plugin
     */
    private function initializeTables() {
        // SEO Settings Table
        $sql_seo = "CREATE TABLE IF NOT EXISTS {$this->table} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            page_type VARCHAR(50) NOT NULL,
            page_id INT,
            title VARCHAR(255) NOT NULL,
            meta_description TEXT,
            meta_keywords TEXT,
            focus_keyword VARCHAR(255),
            slug VARCHAR(255) UNIQUE,
            canonical_url VARCHAR(255),
            robots_meta VARCHAR(100),
            og_title VARCHAR(255),
            og_description TEXT,
            og_image VARCHAR(255),
            twitter_title VARCHAR(255),
            twitter_description TEXT,
            twitter_image VARCHAR(255),
            readability_score INT DEFAULT 0,
            keyword_density FLOAT DEFAULT 0,
            keyword_rank INT,
            schema_markup LONGTEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_page (page_type, page_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->db->query($sql_seo);
        
        // SEO Analytics Table
        $sql_analytics = "CREATE TABLE IF NOT EXISTS {$this->analytics_table} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            seo_id INT NOT NULL,
            page_type VARCHAR(50),
            impressions INT DEFAULT 0,
            clicks INT DEFAULT 0,
            avg_position FLOAT DEFAULT 0,
            click_through_rate FLOAT DEFAULT 0,
            top_keywords TEXT,
            traffic_source VARCHAR(100),
            bounce_rate FLOAT,
            avg_session_duration INT,
            conversion_rate FLOAT DEFAULT 0,
            date DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (seo_id) REFERENCES {$this->table}(id) ON DELETE CASCADE,
            UNIQUE KEY unique_analytics (seo_id, date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->db->query($sql_analytics);
    }
    
    /**
     * Save or update SEO data for a page
     */
    public function saveSEOData($page_type, $page_id, $data) {
        $check = $this->db->query(
            "SELECT id FROM {$this->table} WHERE page_type='$page_type' AND page_id=$page_id"
        );
        
        if ($check === false) {
            throw new Exception("Database check query failed: " . $this->db->error);
        }
        
        $id = null;
        if ($check && $check->num_rows > 0) {
            $row = $check->fetch_assoc();
            $id = $row['id'];
        }
        
        $title = $this->db->real_escape_string($data['title'] ?? '');
        $meta_desc = $this->db->real_escape_string($data['meta_description'] ?? '');
        $keywords = $this->db->real_escape_string($data['meta_keywords'] ?? '');
        $focus_kw = $this->db->real_escape_string($data['focus_keyword'] ?? '');
        $slug = $this->db->real_escape_string($data['slug'] ?? '');
        $canonical = $this->db->real_escape_string($data['canonical_url'] ?? '');
        $robots = $this->db->real_escape_string($data['robots_meta'] ?? 'index, follow');
        
        $og_title = $this->db->real_escape_string($data['og_title'] ?? '');
        $og_desc = $this->db->real_escape_string($data['og_description'] ?? '');
        $og_image = $this->db->real_escape_string($data['og_image'] ?? '');
        
        $twitter_title = $this->db->real_escape_string($data['twitter_title'] ?? '');
        $twitter_desc = $this->db->real_escape_string($data['twitter_description'] ?? '');
        $twitter_image = $this->db->real_escape_string($data['twitter_image'] ?? '');
        
        $schema = $this->db->real_escape_string($data['schema_markup'] ?? '');
        
        if ($id) {
            $sql = "UPDATE {$this->table} SET 
                    title='$title',
                    meta_description='$meta_desc',
                    meta_keywords='$keywords',
                    focus_keyword='$focus_kw',
                    slug='$slug',
                    canonical_url='$canonical',
                    robots_meta='$robots',
                    og_title='$og_title',
                    og_description='$og_desc',
                    og_image='$og_image',
                    twitter_title='$twitter_title',
                    twitter_description='$twitter_desc',
                    twitter_image='$twitter_image',
                    schema_markup='$schema'
                    WHERE id=$id";
        } else {
            $sql = "INSERT INTO {$this->table} (
                    page_type, page_id, title, meta_description, meta_keywords,
                    focus_keyword, slug, canonical_url, robots_meta,
                    og_title, og_description, og_image,
                    twitter_title, twitter_description, twitter_image,
                    schema_markup
                    ) VALUES (
                    '$page_type', $page_id, '$title', '$meta_desc', '$keywords',
                    '$focus_kw', '$slug', '$canonical', '$robots',
                    '$og_title', '$og_desc', '$og_image',
                    '$twitter_title', '$twitter_desc', '$twitter_image',
                    '$schema'
                    )";
        }
        
        $result = $this->db->query($sql);
        if ($result === false) {
            throw new Exception("Failed to save SEO data: " . $this->db->error);
        }
        
        return $result;
    }
    
    /**
     * Get SEO data for a specific page
     */
    public function getSEOData($page_type, $page_id) {
        $result = $this->db->query(
            "SELECT * FROM {$this->table} WHERE page_type='$page_type' AND page_id=$page_id"
        );
        
        if ($result && $result->num_rows > 0) {
            return $result->fetch_assoc();
        }
        
        return null;
    }
    
    /**
     * Calculate SEO score (0-100)
     */
    public function calculateSEOScore($data) {
        $score = 0;
        
        // Title (10 points)
        if (!empty($data['title'])) {
            $title_len = strlen($data['title']);
            if ($title_len >= 30 && $title_len <= 60) {
                $score += 10;
            } elseif ($title_len > 0) {
                $score += 5;
            }
        }
        
        // Meta Description (10 points)
        if (!empty($data['meta_description'])) {
            $desc_len = strlen($data['meta_description']);
            if ($desc_len >= 120 && $desc_len <= 160) {
                $score += 10;
            } elseif ($desc_len > 0) {
                $score += 5;
            }
        }
        
        // Focus Keyword (15 points)
        if (!empty($data['focus_keyword'])) {
            $score += 15;
        }
        
        // Meta Keywords (5 points)
        if (!empty($data['meta_keywords'])) {
            $score += 5;
        }
        
        // Slug (10 points)
        if (!empty($data['slug'])) {
            if (preg_match('/^[a-z0-9\-]+$/', $data['slug'])) {
                $score += 10;
            } else {
                $score += 5;
            }
        }
        
        // Canonical URL (10 points)
        if (!empty($data['canonical_url'])) {
            $score += 10;
        }
        
        // Readability (15 points)
        if (isset($data['readability_score'])) {
            $score += ($data['readability_score'] / 100) * 15;
        }
        
        // Schema Markup (15 points)
        if (!empty($data['schema_markup'])) {
            $score += 15;
        }
        
        // Social Tags (5 points)
        if (!empty($data['og_title']) && !empty($data['og_description'])) {
            $score += 5;
        }
        
        return min(100, (int)$score);
    }
    
    /**
     * Generate SEO score color and status
     */
    public function getSEOScoreStatus($score) {
        if ($score >= 80) {
            return ['color' => '#22c55e', 'status' => 'Excellent', 'icon' => '✓'];
        } elseif ($score >= 60) {
            return ['color' => '#f59e0b', 'status' => 'Good', 'icon' => '!'];
        } elseif ($score >= 40) {
            return ['color' => '#ef4444', 'status' => 'Needs Work', 'icon' => '⚠'];
        } else {
            return ['color' => '#dc2626', 'status' => 'Poor', 'icon' => '✕'];
        }
    }
    
    /**
     * Get all SEO data with analytics
     */
    public function getAllSEOData($page_type = null, $limit = 50, $offset = 0) {
        $where = '';
        if ($page_type) {
            $where = "WHERE page_type='$page_type'";
        }
        
        $sql = "SELECT s.*, COUNT(a.id) as analytics_records 
                FROM {$this->table} s
                LEFT JOIN {$this->analytics_table} a ON s.id = a.seo_id
                $where
                GROUP BY s.id
                ORDER BY s.updated_at DESC
                LIMIT $limit OFFSET $offset";
        
        return $this->db->query($sql);
    }
    
    /**
     * Record SEO analytics data
     */
    public function recordAnalytics($seo_id, $analytics_data) {
        $date = date('Y-m-d');
        $impressions = (int)($analytics_data['impressions'] ?? 0);
        $clicks = (int)($analytics_data['clicks'] ?? 0);
        $avg_position = (float)($analytics_data['avg_position'] ?? 0);
        $ctr = $impressions > 0 ? ($clicks / $impressions) : 0;
        $top_keywords = $this->db->real_escape_string($analytics_data['top_keywords'] ?? '');
        $traffic_source = $this->db->real_escape_string($analytics_data['traffic_source'] ?? 'organic');
        $bounce_rate = (float)($analytics_data['bounce_rate'] ?? 0);
        $session_duration = (int)($analytics_data['avg_session_duration'] ?? 0);
        $conversion_rate = (float)($analytics_data['conversion_rate'] ?? 0);
        
        $check = $this->db->query(
            "SELECT id FROM {$this->analytics_table} WHERE seo_id=$seo_id AND date='$date'"
        );
        
        if ($check && $check->num_rows > 0) {
            $sql = "UPDATE {$this->analytics_table} SET 
                    impressions=$impressions,
                    clicks=$clicks,
                    avg_position=$avg_position,
                    click_through_rate=$ctr,
                    top_keywords='$top_keywords',
                    traffic_source='$traffic_source',
                    bounce_rate=$bounce_rate,
                    avg_session_duration=$session_duration,
                    conversion_rate=$conversion_rate
                    WHERE seo_id=$seo_id AND date='$date'";
        } else {
            $sql = "INSERT INTO {$this->analytics_table} (
                    seo_id, impressions, clicks, avg_position, click_through_rate,
                    top_keywords, traffic_source, bounce_rate, avg_session_duration,
                    conversion_rate, date
                    ) VALUES (
                    $seo_id, $impressions, $clicks, $avg_position, $ctr,
                    '$top_keywords', '$traffic_source', $bounce_rate, $session_duration,
                    $conversion_rate, '$date'
                    )";
        }
        
        return $this->db->query($sql);
    }
    
    /**
     * Get analytics for SEO item
     */
    public function getAnalytics($seo_id, $days = 30) {
        $date = date('Y-m-d', strtotime("-$days days"));
        
        $sql = "SELECT * FROM {$this->analytics_table} 
                WHERE seo_id=$seo_id AND date >= '$date'
                ORDER BY date ASC";
        
        return $this->db->query($sql);
    }
    
    /**
     * Generate SEO suggestions based on data
     */
    public function generateSuggestions($data) {
        $suggestions = [];
        
        // Title suggestions
        if (empty($data['title'])) {
            $suggestions[] = [
                'type' => 'error',
                'category' => 'Title',
                'message' => 'Add a page title for better SEO'
            ];
        } elseif (strlen($data['title']) < 30) {
            $suggestions[] = [
                'type' => 'warning',
                'category' => 'Title',
                'message' => 'Title is too short. Aim for 30-60 characters.'
            ];
        } elseif (strlen($data['title']) > 60) {
            $suggestions[] = [
                'type' => 'warning',
                'category' => 'Title',
                'message' => 'Title is too long. Keep it under 60 characters.'
            ];
        }
        
        // Meta description suggestions
        if (empty($data['meta_description'])) {
            $suggestions[] = [
                'type' => 'error',
                'category' => 'Meta Description',
                'message' => 'Add a meta description to improve CTR'
            ];
        } elseif (strlen($data['meta_description']) < 120) {
            $suggestions[] = [
                'type' => 'warning',
                'category' => 'Meta Description',
                'message' => 'Meta description is too short. Aim for 120-160 characters.'
            ];
        } elseif (strlen($data['meta_description']) > 160) {
            $suggestions[] = [
                'type' => 'warning',
                'category' => 'Meta Description',
                'message' => 'Meta description is too long. Keep it under 160 characters.'
            ];
        }
        
        // Focus keyword suggestions
        if (empty($data['focus_keyword'])) {
            $suggestions[] = [
                'type' => 'error',
                'category' => 'Focus Keyword',
                'message' => 'Set a focus keyword to target specific search terms'
            ];
        }
        
        // Slug suggestions
        if (empty($data['slug'])) {
            $suggestions[] = [
                'type' => 'error',
                'category' => 'Slug',
                'message' => 'Create a clean, descriptive URL slug'
            ];
        } elseif (!preg_match('/^[a-z0-9\-]+$/', $data['slug'])) {
            $suggestions[] = [
                'type' => 'warning',
                'category' => 'Slug',
                'message' => 'Slug contains invalid characters. Use only lowercase letters, numbers, and hyphens.'
            ];
        }
        
        // Schema markup suggestions
        if (empty($data['schema_markup'])) {
            $suggestions[] = [
                'type' => 'info',
                'category' => 'Schema Markup',
                'message' => 'Add structured data to enhance rich snippets'
            ];
        }
        
        // Social media suggestions
        if (empty($data['og_title']) || empty($data['og_description'])) {
            $suggestions[] = [
                'type' => 'info',
                'category' => 'Social Media',
                'message' => 'Add Open Graph tags to improve social sharing'
            ];
        }
        
        return $suggestions;
    }
    
    /**
     * Delete SEO data
     */
    public function deleteSEOData($page_type, $page_id) {
        $sql = "DELETE FROM {$this->table} WHERE page_type='$page_type' AND page_id=$page_id";
        return $this->db->query($sql);
    }
    
    /**
     * Export SEO data
     */
    public function exportSEOData($format = 'csv') {
        $result = $this->db->query("SELECT * FROM {$this->table}");
        
        if ($format === 'csv') {
            return $this->exportAsCSV($result);
        } elseif ($format === 'json') {
            return $this->exportAsJSON($result);
        }
        
        return null;
    }
    
    private function exportAsCSV($result) {
        $csv = "Title,Meta Description,Focus Keyword,Slug,SEO Score,Readability,Created\n";
        
        while ($row = $result->fetch_assoc()) {
            $score = $this->calculateSEOScore($row);
            $csv .= '"' . implode('","', [
                $row['title'],
                $row['meta_description'],
                $row['focus_keyword'],
                $row['slug'],
                $score,
                $row['readability_score'],
                $row['created_at']
            ]) . "\"\n";
        }
        
        return $csv;
    }
    
    private function exportAsJSON($result) {
        $data = [];
        while ($row = $result->fetch_assoc()) {
            $row['seo_score'] = $this->calculateSEOScore($row);
            $data[] = $row;
        }
        
        return json_encode($data, JSON_PRETTY_PRINT);
    }
    
    /**
     * Close database connection
     */
    public function closeConnection() {
        $this->db->close();
    }
}
?>
