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;
        
        try {
            // Get WordPress posts with author info in one query
            $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,
                       u.display_name as author_name
                FROM wp_posts p
                LEFT JOIN wp_users u ON p.post_author = u.ID
                WHERE p.post_type = 'post' AND p.post_status IN ('publish', 'draft')
                ORDER BY p.post_date
                LIMIT 1000
            ");
            
            $posts = $stmt->fetchAll();
            
            // Get all categories for all posts in one query
            $postIds = array_column($posts, 'ID');
            $categoriesMap = [];
            
            if (!empty($postIds)) {
                $placeholders = str_repeat('?,', count($postIds) - 1) . '?';
                $categoriesStmt = $this->wpDb->prepare("
                    SELECT tr.object_id as post_id, t.name as category_name
                    FROM wp_term_relationships tr
                    JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    JOIN wp_terms t ON tt.term_id = t.term_id
                    WHERE tt.taxonomy = 'category' AND tr.object_id IN ($placeholders)
                ");
                $categoriesStmt->execute($postIds);
                
                foreach ($categoriesStmt->fetchAll() as $cat) {
                    $categoriesMap[$cat['post_id']][] = $cat['category_name'];
                }
            }
            
            foreach ($posts as $wpPost) {
                try {
                    // 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';
                    
                    // Use author name from query or fallback
                    $author = $wpPost['author_name'] ?: 'Unknown Author';
                    
                    // Get categories from preloaded map
                    $categories = $categoriesMap[$wpPost['ID']] ?? [];
                    
                    // 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');
                }
            }
            
        } catch (Exception $e) {
            $this->errors[] = "Error in posts import: " . $e->getMessage();
            $this->log("Error in posts import: " . $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;
        
        try {
            // Get WordPress comments with post info in one query
            $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,
                       p.post_name, p.post_title
                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
                LIMIT 2000
            ");
            
            $comments = $stmt->fetchAll();
            
            // Build a map of post IDs to publication IDs
            $postIds = array_unique(array_column($comments, 'comment_post_ID'));
            $publicationMap = [];
            
            if (!empty($postIds)) {
                $placeholders = str_repeat('?,', count($postIds) - 1) . '?';
                $pubStmt = $this->targetDb->prepare("
                    SELECT id, slug, title FROM publications 
                    WHERE slug IN ($placeholders) OR title IN ($placeholders)
                ");
                
                // Duplicate the post IDs for slug and title matching
                $allParams = array_merge($postIds, $postIds);
                $pubStmt->execute($allParams);
                
                foreach ($pubStmt->fetchAll() as $pub) {
                    // Map both slug and title for easier lookup
                    $publicationMap[strtolower($pub['slug'])] = $pub['id'];
                    $publicationMap[strtolower($pub['title'])] = $pub['id'];
                }
            }
            
            foreach ($comments as $wpComment) {
                try {
                    // Find corresponding publication using preloaded map
                    $publicationId = null;
                    $postSlug = !empty($wpComment['post_name']) ? strtolower($wpComment['post_name']) : null;
                    $postTitle = strtolower($wpComment['post_title']);
                    
                    if ($postSlug && isset($publicationMap[$postSlug])) {
                        $publicationId = $publicationMap[$postSlug];
                    } elseif (isset($publicationMap[$postTitle])) {
                        $publicationId = $publicationMap[$postTitle];
                    }
                    
                    if (!$publicationId) {
                        $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 (skip for now to avoid complexity)
                    $parentId = null;
                    
                    // Insert comment
                    $this->targetDb->execute(
                        "INSERT INTO comments (publication_id, parent_id, name, email, content, status, created_at, admin_reply) 
                         VALUES (?, ?, ?, ?, ?, ?, ?, FALSE)",
                        [
                            $publicationId,
                            $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');
                }
            }
            
        } catch (Exception $e) {
            $this->errors[] = "Error in comments import: " . $e->getMessage();
            $this->log("Error in comments import: " . $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;
    }
}