website/includes/wordpress_import.php

<?php
/**
 * WordPress Database Import Tool
 * Imports posts, categories, users, and comments from WordPress to the publication system
 */

class WordPressImport {
    private $wpDb;
    private $targetDb;
    private $wpConfig;
    private $importLog = [];
    private $errors = [];
    
    public function __construct($wpConfig) {
        $this->wpConfig = $wpConfig;
        $this->targetDb = Database::getInstance();
        // Don't connect in constructor - connect on demand to avoid hanging
    }
    
    /**
     * Connect to WordPress database
     */
    private function connectWordPress() {
        try {
            // Set timeout options
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_TIMEOUT => 10, // 10 second timeout
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
            ];
            
            $dsn = "mysql:host={$this->wpConfig['host']};dbname={$this->wpConfig['database']};charset=utf8mb4";
            $this->wpDb = new PDO($dsn, $this->wpConfig['username'], $this->wpConfig['password'], $options);
            
            // Test connection with a simple query
            $this->wpDb->query("SELECT 1");
            
            $this->log('Connected to WordPress database successfully');
        } catch (PDOException $e) {
            throw new Exception("Failed to connect to WordPress database: " . $e->getMessage());
        } catch (Exception $e) {
            throw new Exception("WordPress database connection error: " . $e->getMessage());
        }
    }
    
    /**
     * Test WordPress connection and verify structure
     */
    public function testConnection() {
        try {
            // Connect first
            $this->connectWordPress();
            
            // Check if WordPress tables exist
            $tables = ['wp_posts', 'wp_users', 'wp_terms', 'wp_term_taxonomy', 'wp_term_relationships', 'wp_comments'];
            $existingTables = [];
            
            foreach ($tables as $table) {
                $stmt = $this->wpDb->query("SHOW TABLES LIKE '{$table}'");
                if ($stmt->rowCount() > 0) {
                    $existingTables[] = $table;
                }
            }
            
            if (count($existingTables) < 6) {
                throw new Exception("WordPress database structure incomplete. Missing tables: " . implode(', ', array_diff($tables, $existingTables)));
            }
            
            // Get basic stats
            $stats = [
                'posts' => $this->getPostCount(),
                'pages' => $this->getPageCount(),
                'categories' => $this->getCategoryCount(),
                'users' => $this->getUserCount(),
                'comments' => $this->getCommentCount()
            ];
            
            return ['success' => true, 'stats' => $stats, 'tables' => $existingTables];
            
        } catch (Exception $e) {
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    /**
     * Import all WordPress data
     */
    public function importAll($options = []) {
        $results = [];
        
        try {
            // Connect to WordPress database first
            $this->connectWordPress();
            
            // Start transaction
            $this->targetDb->beginTransaction();
            
            // Import categories first (posts depend on them)
            if ($options['import_categories'] ?? true) {
                $results['categories'] = $this->importCategories();
            }
            
            // Import users
            if ($options['import_users'] ?? true) {
                $results['users'] = $this->importUsers();
            }
            
            // Import posts
            if ($options['import_posts'] ?? true) {
                $results['posts'] = $this->importPosts();
            }
            
            // Import comments
            if ($options['import_comments'] ?? true) {
                $results['comments'] = $this->importComments();
            }
            
            // Commit transaction
            $this->targetDb->commit();
            
            $this->log('Import completed successfully');
            return ['success' => true, 'results' => $results, 'log' => $this->importLog];
            
        } catch (Exception $e) {
            $this->targetDb->rollBack();
            $this->errors[] = $e->getMessage();
            $this->log('Import failed: ' . $e->getMessage(), 'error');
            return ['success' => false, 'error' => $e->getMessage(), 'log' => $this->importLog, 'errors' => $this->errors];
        }
    }
    
    /**
     * Import WordPress categories
     */
    public function importCategories() {
        $this->log('Starting categories import');
        $imported = 0;
        $skipped = 0;
        
        // Get WordPress categories
        $stmt = $this->wpDb->query("
            SELECT t.name, tt.description, tt.term_id
            FROM wp_terms t
            JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
            WHERE tt.taxonomy = 'category'
            ORDER BY t.name
        ");
        
        $categories = $stmt->fetchAll();
        
        foreach ($categories as $wpCategory) {
            try {
                // Check if category already exists
                $existing = $this->targetDb->fetch(
                    "SELECT id FROM categories WHERE name = ?",
                    [$wpCategory['name']]
                );
                
                if ($existing) {
                    $skipped++;
                    $this->log("Category '{$wpCategory['name']}' already exists, skipping");
                    continue;
                }
                
                // Insert new category
                $this->targetDb->execute(
                    "INSERT INTO categories (name, description, created_at) VALUES (?, ?, NOW())",
                    [
                        $wpCategory['name'],
                        $wpCategory['description'] ?? ''
                    ]
                );
                
                $imported++;
                $this->log("Imported category: {$wpCategory['name']}");
                
            } catch (Exception $e) {
                $this->errors[] = "Error importing category '{$wpCategory['name']}': " . $e->getMessage();
                $this->log("Error importing category '{$wpCategory['name']}': " . $e->getMessage(), 'error');
            }
        }
        
        $this->log("Categories import completed: {$imported} imported, {$skipped} skipped");
        return ['imported' => $imported, 'skipped' => $skipped];
    }
    
    /**
     * Import WordPress users
     */
    public function importUsers() {
        $this->log('Starting users import');
        $imported = 0;
        $skipped = 0;
        
        // Get WordPress users
        $stmt = $this->wpDb->query("
            SELECT ID, user_login, user_email, user_nicename, user_registered, display_name
            FROM wp_users
            WHERE user_status = 0
            ORDER BY ID
        ");
        
        $users = $stmt->fetchAll();
        
        foreach ($users as $wpUser) {
            try {
                // Check if user already exists
                $existing = $this->targetDb->fetch(
                    "SELECT id FROM users WHERE username = ?",
                    [$wpUser['user_login']]
                );
                
                if ($existing) {
                    $skipped++;
                    $this->log("User '{$wpUser['user_login']}' already exists, skipping");
                    continue;
                }
                
                // Determine user role (WordPress usermeta table)
                $role = $this->getUserRole($wpUser['ID']);
                
                // Insert new user
                $this->targetDb->execute(
                    "INSERT INTO users (username, email, role, auth_type, created_at) VALUES (?, ?, ?, 'wordpress', ?)",
                    [
                        $wpUser['user_login'],
                        $wpUser['user_email'],
                        $role,
                        $wpUser['user_registered']
                    ]
                );
                
                $imported++;
                $this->log("Imported user: {$wpUser['user_login']} (role: {$role})");
                
            } catch (Exception $e) {
                $this->errors[] = "Error importing user '{$wpUser['user_login']}': " . $e->getMessage();
                $this->log("Error importing user '{$wpUser['user_login']}': " . $e->getMessage(), 'error');
            }
        }
        
        $this->log("Users import completed: {$imported} imported, {$skipped} skipped");
        return ['imported' => $imported, 'skipped' => $skipped];
    }
    
    /**
     * Import WordPress posts
     */
    public function importPosts() {
        $this->log('Starting posts import');
        $imported = 0;
        $skipped = 0;
        
        // Get WordPress posts (only published posts, not pages)
        $stmt = $this->wpDb->query("
            SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_date, 
                   p.post_modified, p.post_status, p.post_author, p.post_name
            FROM wp_posts p
            WHERE p.post_type = 'post' AND p.post_status IN ('publish', 'draft')
            ORDER BY p.post_date
        ");
        
        $posts = $stmt->fetchAll();
        
        foreach ($posts as $wpPost) {
            try {
                // Get author name
                $author = $this->getAuthorName($wpPost['post_author']);
                
                // Get post categories
                $categories = $this->getPostCategories($wpPost['ID']);
                
                // Generate slug from post_name or title
                $slug = !empty($wpPost['post_name']) ? $wpPost['post_name'] : $this->generateSlug($wpPost['post_title']);
                
                // Map WordPress status to our status
                $status = ($wpPost['post_status'] === 'publish') ? 'published' : 'draft';
                
                // Insert post
                $this->targetDb->execute(
                    "INSERT INTO publications (title, slug, content, summary, author, status, created_at, updated_at, published_at) 
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    [
                        $wpPost['post_title'],
                        $slug,
                        $this->processContent($wpPost['post_content']),
                        $wpPost['post_excerpt'] ?? '',
                        $author,
                        $status,
                        $wpPost['post_date'],
                        $wpPost['post_modified'],
                        ($status === 'published') ? $wpPost['post_date'] : null
                    ]
                );
                
                $publicationId = $this->targetDb->lastInsertId();
                
                // Link categories
                if (!empty($categories)) {
                    $this->linkPostCategories($publicationId, $categories);
                }
                
                $imported++;
                $this->log("Imported post: '{$wpPost['post_title']}'");
                
            } catch (Exception $e) {
                $this->errors[] = "Error importing post '{$wpPost['post_title']}': " . $e->getMessage();
                $this->log("Error importing post '{$wpPost['post_title']}': " . $e->getMessage(), 'error');
            }
        }
        
        $this->log("Posts import completed: {$imported} imported, {$skipped} skipped");
        return ['imported' => $imported, 'skipped' => $skipped];
    }
    
    /**
     * Import WordPress comments
     */
    public function importComments() {
        $this->log('Starting comments import');
        $imported = 0;
        $skipped = 0;
        
        // Get WordPress comments
        $stmt = $this->wpDb->query("
            SELECT c.comment_ID, c.comment_post_ID, c.comment_author, c.comment_author_email,
                   c.comment_content, c.comment_date, c.comment_approved, c.comment_parent
            FROM wp_comments c
            JOIN wp_posts p ON c.comment_post_ID = p.ID
            WHERE p.post_type = 'post'
            ORDER BY c.comment_date
        ");
        
        $comments = $stmt->fetchAll();
        
        foreach ($comments as $wpComment) {
            try {
                // Find corresponding publication
                $publication = $this->targetDb->fetch(
                    "SELECT id FROM publications WHERE slug = ? OR title = ? LIMIT 1",
                    [$this->getPostSlugById($wpComment['comment_post_ID']), $this->getPostTitleById($wpComment['comment_post_ID'])]
                );
                
                if (!$publication) {
                    $skipped++;
                    $this->log("Comment skipped - no matching publication found for post ID {$wpComment['comment_post_ID']}");
                    continue;
                }
                
                // Map comment status
                $status = ($wpComment['comment_approved'] === '1') ? 'approved' : 'pending';
                
                // Handle parent comment
                $parentId = null;
                if ($wpComment['comment_parent'] > 0) {
                    $parentComment = $this->targetDb->fetch(
                        "SELECT id FROM comments WHERE wp_comment_id = ?",
                        [$wpComment['comment_parent']]
                    );
                    if ($parentComment) {
                        $parentId = $parentComment['id'];
                    }
                }
                
                // Insert comment
                $this->targetDb->execute(
                    "INSERT INTO comments (publication_id, parent_id, name, email, content, status, created_at, admin_reply) 
                     VALUES (?, ?, ?, ?, ?, ?, ?, FALSE)",
                    [
                        $publication['id'],
                        $parentId,
                        $wpComment['comment_author'],
                        $wpComment['comment_author_email'],
                        $wpComment['comment_content'],
                        $status,
                        $wpComment['comment_date']
                    ]
                );
                
                $commentId = $this->targetDb->lastInsertId();
                
                // Store WordPress comment ID for parent mapping
                $this->targetDb->execute(
                    "UPDATE comments SET wp_comment_id = ? WHERE id = ?",
                    [$wpComment['comment_ID'], $commentId]
                );
                
                $imported++;
                $this->log("Imported comment for post ID {$wpComment['comment_post_ID']}");
                
            } catch (Exception $e) {
                $this->errors[] = "Error importing comment: " . $e->getMessage();
                $this->log("Error importing comment: " . $e->getMessage(), 'error');
            }
        }
        
        $this->log("Comments import completed: {$imported} imported, {$skipped} skipped");
        return ['imported' => $imported, 'skipped' => $skipped];
    }
    
    /**
     * Helper methods
     */
    private function getUserRole($userId) {
        $stmt = $this->wpDb->prepare("
            SELECT meta_value FROM wp_usermeta 
            WHERE user_id = ? AND meta_key = 'wp_capabilities'
        ");
        $stmt->execute([$userId]);
        $capabilities = $stmt->fetchColumn();
        
        if ($capabilities && strpos($capabilities, 'administrator') !== false) {
            return 'admin';
        }
        
        return 'editor'; // Default role
    }
    
    private function getAuthorName($authorId) {
        $stmt = $this->wpDb->prepare("SELECT display_name FROM wp_users WHERE ID = ?");
        $stmt->execute([$authorId]);
        $name = $stmt->fetchColumn();
        return $name ?: 'Unknown Author';
    }
    
    private function getPostCategories($postId) {
        $stmt = $this->wpDb->prepare("
            SELECT t.name FROM wp_terms t
            JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
            JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
            WHERE tr.object_id = ? AND tt.taxonomy = 'category'
        ");
        $stmt->execute([$postId]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    
    private function getPostSlugById($postId) {
        $stmt = $this->wpDb->prepare("SELECT post_name FROM wp_posts WHERE ID = ?");
        $stmt->execute([$postId]);
        return $stmt->fetchColumn() ?: '';
    }
    
    private function getPostTitleById($postId) {
        $stmt = $this->wpDb->prepare("SELECT post_title FROM wp_posts WHERE ID = ?");
        $stmt->execute([$postId]);
        return $stmt->fetchColumn() ?: '';
    }
    
    private function linkPostCategories($publicationId, $categories) {
        foreach ($categories as $categoryName) {
            $category = $this->targetDb->fetch(
                "SELECT id FROM categories WHERE name = ?",
                [$categoryName]
            );
            
            if ($category) {
                $this->targetDb->execute(
                    "INSERT IGNORE INTO publication_categories (publication_id, category_id) VALUES (?, ?)",
                    [$publicationId, $category['id']]
                );
            }
        }
    }
    
    private function processContent($content) {
        // Basic WordPress content processing
        // You can extend this to handle shortcodes, etc.
        $content = str_replace('[caption]', '', $content);
        $content = str_replace('[/caption]', '', $content);
        $content = preg_replace('/\[gallery.*?\]/', '', $content);
        
        return $content;
    }
    
    private function generateSlug($title) {
        $slug = strtolower($title);
        $slug = preg_replace('/[^a-z0-9]+/', '-', $slug);
        $slug = trim($slug, '-');
        return $slug;
    }
    
    private function log($message, $level = 'info') {
        $this->importLog[] = [
            'timestamp' => date('Y-m-d H:i:s'),
            'level' => $level,
            'message' => $message
        ];
    }
    
    /**
     * Get statistics methods
     */
    public function getPostCount() {
        $stmt = $this->wpDb->query("SELECT COUNT(*) FROM wp_posts WHERE post_type = 'post'");
        return $stmt->fetchColumn();
    }
    
    public function getPageCount() {
        $stmt = $this->wpDb->query("SELECT COUNT(*) FROM wp_posts WHERE post_type = 'page'");
        return $stmt->fetchColumn();
    }
    
    public function getCategoryCount() {
        $stmt = $this->wpDb->query("
            SELECT COUNT(*) FROM wp_term_taxonomy 
            WHERE taxonomy = 'category'
        ");
        return $stmt->fetchColumn();
    }
    
    public function getUserCount() {
        $stmt = $this->wpDb->query("SELECT COUNT(*) FROM wp_users");
        return $stmt->fetchColumn();
    }
    
    public function getCommentCount() {
        $stmt = $this->wpDb->query("SELECT COUNT(*) FROM wp_comments");
        return $stmt->fetchColumn();
    }
    
    /**
     * Get import log
     */
    public function getLog() {
        return $this->importLog;
    }
    
    /**
     * Get errors
     */
    public function getErrors() {
        return $this->errors;
    }
}