<?php
/**
 * SEO Database Schema Initialization
 * Complete schema for Yoast-style SEO management system
 */

class SEODatabaseSchema {
    private $db;
    
    public function __construct($mysqli) {
        $this->db = $mysqli;
    }
    
    /**
     * Initialize all SEO-related tables
     */
    public function initializeAllTables() {
        $this->createSEOMetaTable();
        $this->createSEOAnalyticsTable();
        $this->createSEOAuditTable();
        $this->createSEOSchemaTable();
        $this->createSEOKeywordTable();
        $this->createSiteurlTable();
        return true;
    }
    
    /**
     * Main SEO Meta Table - linked to products and blogs
     */
    private function createSEOMetaTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_meta (
            id INT AUTO_INCREMENT PRIMARY KEY,
            entity_type ENUM('product', 'blog', 'page') NOT NULL,
            entity_id INT NOT NULL,
            focus_keyword VARCHAR(255),
            seo_title VARCHAR(255),
            meta_desc TEXT,
            canonical_url VARCHAR(500),
            is_noindex BOOLEAN DEFAULT FALSE,
            seo_score INT DEFAULT 0,
            readability_score INT DEFAULT 0,
            keyword_density FLOAT DEFAULT 0,
            schema_markup LONGTEXT,
            og_title VARCHAR(255),
            og_description TEXT,
            og_image VARCHAR(500),
            twitter_title VARCHAR(255),
            twitter_description TEXT,
            twitter_image VARCHAR(500),
            robots_meta VARCHAR(100),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_entity (entity_type, entity_id),
            INDEX idx_entity_type (entity_type),
            INDEX idx_seo_score (seo_score),
            INDEX idx_focus_keyword (focus_keyword)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
    
    /**
     * SEO Analytics Table
     */
    private function createSEOAnalyticsTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_analytics (
            id INT AUTO_INCREMENT PRIMARY KEY,
            entity_type ENUM('product', 'blog', 'page'),
            entity_id INT,
            impressions INT DEFAULT 0,
            clicks INT DEFAULT 0,
            avg_position FLOAT DEFAULT 0,
            click_through_rate FLOAT DEFAULT 0,
            bounce_rate FLOAT DEFAULT 0,
            avg_session_duration INT DEFAULT 0,
            conversion_rate FLOAT DEFAULT 0,
            top_keywords TEXT,
            traffic_source VARCHAR(100),
            date DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_analytics (entity_type, entity_id, date),
            INDEX idx_date (date),
            FOREIGN KEY (entity_id) REFERENCES seo_meta(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
    
    /**
     * SEO Audit Table - for page structure audits
     */
    private function createSEOAuditTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_audit (
            id INT AUTO_INCREMENT PRIMARY KEY,
            url VARCHAR(500) NOT NULL,
            audit_date DATE NOT NULL,
            h1_count INT DEFAULT 0,
            h2_count INT DEFAULT 0,
            images_without_alt INT DEFAULT 0,
            total_images INT DEFAULT 0,
            internal_links INT DEFAULT 0,
            external_links INT DEFAULT 0,
            has_breadcrumb BOOLEAN DEFAULT FALSE,
            has_schema BOOLEAN DEFAULT FALSE,
            page_speed_score INT DEFAULT 0,
            accessibility_score INT DEFAULT 0,
            seo_score INT DEFAULT 0,
            audit_details LONGTEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_audit (url, audit_date),
            INDEX idx_url (url)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
    
    /**
     * Schema Markup Table
     */
    private function createSEOSchemaTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_schema (
            id INT AUTO_INCREMENT PRIMARY KEY,
            entity_type ENUM('product', 'blog', 'page'),
            entity_id INT,
            schema_type VARCHAR(100) NOT NULL,
            schema_json LONGTEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_entity (entity_type, entity_id),
            INDEX idx_schema_type (schema_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
    
    /**
     * Keyword Tracking Table
     */
    private function createSEOKeywordTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_keywords (
            id INT AUTO_INCREMENT PRIMARY KEY,
            entity_type ENUM('product', 'blog', 'page'),
            entity_id INT,
            keyword VARCHAR(255) NOT NULL,
            search_volume INT DEFAULT 0,
            keyword_difficulty INT DEFAULT 0,
            current_position INT DEFAULT 0,
            trend VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_keyword (entity_type, entity_id, keyword),
            INDEX idx_keyword (keyword),
            INDEX idx_position (current_position)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
    
    /**
     * Sitemap URLs Table
     */
    private function createSiteurlTable() {
        $sql = "CREATE TABLE IF NOT EXISTS seo_siteurl (
            id INT AUTO_INCREMENT PRIMARY KEY,
            url VARCHAR(500) NOT NULL UNIQUE,
            entity_type ENUM('product', 'blog', 'page'),
            entity_id INT,
            priority FLOAT DEFAULT 0.5,
            change_frequency VARCHAR(50) DEFAULT 'weekly',
            last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_url (url),
            INDEX idx_entity (entity_type, entity_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        return $this->db->query($sql);
    }
}

// Initialize if called directly
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['init_seo_db'])) {
    require_once 'db_config.php';
    
    $db_config = [
        'host' => 'localhost',
        'user' => 'root',
        'password' => '',
        'database' => 'login-table'
    ];
    
    $db = new mysqli(
        $db_config['host'],
        $db_config['user'],
        $db_config['password'],
        $db_config['database']
    );
    
    if ($db->connect_error) {
        die('Connection failed: ' . $db->connect_error);
    }
    
    $schema = new SEODatabaseSchema($db);
    if ($schema->initializeAllTables()) {
        echo json_encode(['success' => true, 'message' => 'SEO database schema initialized successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Error initializing schema: ' . $db->error]);
    }
    exit;
}
?>
