<?php
/**
 * SEO Manager Database Initialization
 * Run this once to set up all required tables and data
 */

// Check if session is already started before starting
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Check if user is admin (optional - comment out for first setup)
// if (!isset($_SESSION['admin_logged_in']) || $_SESSION['admin_logged_in'] !== true) {
//     header('Location: admin_login.php');
//     exit;
// }

class SEODatabaseInitializer {
    private $db;
    
    public function __construct() {
        // Get database config from existing config or use defaults
        $db_host = 'localhost';
        $db_user = 'root';
        $db_pass = '';
        $db_name = 'login-table'; // Using the existing database from db_config.php
        
        $this->db = new mysqli($db_host, $db_user, $db_pass, $db_name);
        
        if ($this->db->connect_error) {
            die('Connection failed: ' . $this->db->connect_error);
        }
        
        $this->db->set_charset('utf8mb4');
    }
    
    /**
     * Initialize all SEO tables
     */
    public function initialize() {
        try {
            $this->createSEOSettingsTable();
            $this->createAnalyticsTable();
            $this->createSampleData();
            return true;
        } catch (Exception $e) {
            echo "Error: " . $e->getMessage();
            return false;
        }
    }
    
    /**
     * Close database connection
     */
    public function closeConnection() {
        if ($this->db) {
            $this->db->close();
        }
    }
    
    /**
     * Create SEO settings table
     */
    private function createSEOSettingsTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_settings (
            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) DEFAULT 'index, follow',
            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),
            INDEX idx_page_type (page_type),
            INDEX idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        if ($this->db->query($sql)) {
            echo "✓ SEO Settings table created successfully<br>";
        } else {
            echo "✗ Error creating SEO Settings table: " . $this->db->error . "<br>";
        }
    }
    
    /**
     * Create analytics table
     */
    private function createAnalyticsTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_analytics (
            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) DEFAULT 'organic',
            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 seo_settings(id) ON DELETE CASCADE,
            UNIQUE KEY unique_analytics (seo_id, date),
            INDEX idx_date (date),
            INDEX idx_seo_id (seo_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        if ($this->db->query($sql)) {
            echo "✓ SEO Analytics table created successfully<br>";
        } else {
            echo "✗ Error creating SEO Analytics table: " . $this->db->error . "<br>";
        }
    }
    
    /**
     * Insert sample data for testing
     */
    private function createSampleData() {
        // Check if sample data already exists
        $check = $this->db->query("SELECT COUNT(*) as count FROM seo_settings");
        $result = $check->fetch_assoc();
        
        if ($result['count'] > 0) {
            echo "✓ Sample data already exists<br>";
            return;
        }
        
        // Insert sample SEO data
        $samples = [
            [
                'page_type' => 'page',
                'page_id' => 1,
                'title' => 'Home - Premium Products',
                'meta_description' => 'Discover our premium collection of products with free shipping',
                'focus_keyword' => 'premium products',
                'slug' => 'home',
                'og_title' => 'Home - Premium Products'
            ],
            [
                'page_type' => 'page',
                'page_id' => 2,
                'title' => 'About Us - Our Story',
                'meta_description' => 'Learn about our company mission and values',
                'focus_keyword' => 'about us',
                'slug' => 'about',
                'og_title' => 'About Us'
            ],
            [
                'page_type' => 'page',
                'page_id' => 3,
                'title' => 'Contact Us - Get in Touch',
                'meta_description' => 'Contact our team for support and inquiries',
                'focus_keyword' => 'contact us',
                'slug' => 'contact',
                'og_title' => 'Contact Us'
            ]
        ];
        
        foreach ($samples as $sample) {
            $page_type = $this->db->real_escape_string($sample['page_type']);
            $title = $this->db->real_escape_string($sample['title']);
            $meta_desc = $this->db->real_escape_string($sample['meta_description']);
            $focus_kw = $this->db->real_escape_string($sample['focus_keyword']);
            $slug = $this->db->real_escape_string($sample['slug']);
            $og_title = $this->db->real_escape_string($sample['og_title']);
            
            $sql = "INSERT INTO seo_settings 
                    (page_type, page_id, title, meta_description, focus_keyword, slug, og_title, readability_score)
                    VALUES 
                    ('$page_type', {$sample['page_id']}, '$title', '$meta_desc', '$focus_kw', '$slug', '$og_title', 75)";
            
            $this->db->query($sql);
        }
        
        echo "✓ Sample data inserted successfully<br>";
    }
}

// Only run if accessed directly
if (basename($_SERVER['PHP_SELF']) === 'seo_db_init.php') {
    // Check if session is already started
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    
    // Allow direct access for initialization (no strict auth needed)
    // Users can access this page freely, the database init is safe
    
    ?>
    <!DOCTYPE html>
    <html>
    <head>
        <title>SEO Manager Database Setup</title>
        <style>
            body { font-family: Arial, sans-serif; padding: 40px; background: #f5f5f5; }
            .container { max-width: 600px; margin: 0 auto; background: #fff; padding: 30px; border-radius: 8px; }
            h1 { color: #333; }
            .message { padding: 15px; margin: 10px 0; border-radius: 4px; }
            .success { background: #d4edda; color: #155724; }
            .error { background: #f8d7da; color: #721c24; }
        </style>
    </head>
    <body>
        <div class="container">
            <h1>🔧 SEO Manager Database Setup</h1>
            <p>Initializing SEO Manager database tables...</p>
            
            <div style="margin-top: 30px;">
                <?php
                $init = new SEODatabaseInitializer();
                if ($init->initialize()) {
                    echo '<div class="message success">
                        ✓ Database initialized successfully!<br>
                        <br>
                        <a href="admin_seo_manager.php" style="color: #155724; text-decoration: none; font-weight: bold;">
                            → Go to SEO Manager
                        </a>
                    </div>';
                } else {
                    echo '<div class="message error">
                        ✗ Error during initialization<br>
                        Please check your database connection and try again
                    </div>';
                }
                
                $init->closeConnection();
                ?>
            </div>
        </div>
    </body>
    </html>
    <?php
}
?>
