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) {
        error_log('WordPressImport constructor called');
        $this->wpConfig = $wpConfig;
        $this->targetDb = Database::getInstance();
        error_log('WordPressImport constructor completed');
        // 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();
            
            // Simple connection test
            $stmt = $this->wpDb->query("SELECT 1 as test");
            $test = $stmt->fetch();
            
            if (!$test || $test['test'] != 1) {
                throw new Exception("WordPress database connection test failed");
            }
            
            // Check if WordPress tables exist (simplified)
            $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 with simple queries (avoid hanging)
            $stats = [];
            try {
                $stats['posts'] = $this->wpDb->query("SELECT COUNT(*) FROM wp_posts WHERE post_type = 'post'")->fetchColumn();
            } catch (Exception $e) {
                $stats['posts'] = 0;
            }
            
            try {
                $stats['categories'] = $this->wpDb->query("SELECT COUNT(*) FROM wp_term_taxonomy WHERE taxonomy = 'category'")->fetchColumn();
            } catch (Exception $e) {
                $stats['categories'] = 0;
            }
            
            try {
                $stats['users'] = $this->wpDb->query("SELECT COUNT(*) FROM wp_users")->fetchColumn();
            } catch (Exception $e) {
                $stats['users'] = 0;
            }
            
            try {
                $stats['comments'] = $this->wpDb->query("SELECT COUNT(*) FROM wp_comments")->fetchColumn();
            } catch (Exception $e) {
                $stats['comments'] = 0;
            }
            
            return ['success' => true, 'stats' => $stats, 'tables' => $existingTables];
            
        } catch (Exception $e) {
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    /**
     * Import all WordPress data
     */
    public function importAll($options = []) {
        error_log('importAll() called with options: ' . print_r($options, true));
        $results = [];
        
        try {
            // Connect to WordPress database first
            error_log('About to call connectWordPress()');
            $this->connectWordPress();
            error_log('connectWordPress() completed');
            
            // Start transaction
            error_log('About to start transaction');
            $this->targetDb->beginTransaction();
            error_log('Transaction started');
            
            // 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;
        
        // Connect to WordPress database if not already connected
        if ($this->wpDb === null) {
            $this->connectWordPress();
        }
        
        // 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->query(
                    "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;
        
        // Connect to WordPress database if not already connected
        if ($this->wpDb === null) {
            $this->connectWordPress();
        }
        
        // 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->query(
                    "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 {
            // Connect to WordPress database if not already connected
            if ($this->wpDb === null) {
                $this->connectWordPress();
            }
            
            // 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();
            
            $this->log("Found " . count($posts) . " WordPress posts to import");
            
            // 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 $index => $wpPost) {
                try {
                    $this->log("Processing post {$index}: '{$wpPost['post_title']}' (ID: {$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';
                    
                    // 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->query(
                        "INSERT INTO publications (title, slug, content, summary, author, status, created_at, updated_at, published_at, wp_post_id) 
                         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,
                            $wpPost['ID']
                        ]
                    );
                    
                    $publicationId = $this->targetDb->lastInsertId();
                    
                    // Link categories
                    if (!empty($categories)) {
                        $placeholders = str_repeat('?,', count($categories) - 1) . '?';
                        $this->targetDb->query("
                            INSERT INTO publication_categories (publication_id, category_id) 
                            SELECT ?, c.id FROM categories c 
                            WHERE c.name IN ($placeholders)
                        ", array_merge([$publicationId], $categories));
                    }
                    
                    $imported++;
                    $this->log("Successfully imported post: '{$wpPost['post_title']}' (ID: {$wpPost['ID']})");
                    
                } catch (Exception $e) {
                    $skipped++;
                    $this->log("Skipped post '{$wpPost['post_title']}' (ID: {$wpPost['ID']}): " . $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 {
            // Connect to WordPress database if not already connected
            if ($this->wpDb === null) {
                $this->connectWordPress();
            }
            
            // Test basic WordPress comments query first
            $testStmt = $this->wpDb->query("SELECT COUNT(*) FROM wp_comments");
            $commentCount = $testStmt->fetchColumn();
            $this->log("WordPress comments table has {$commentCount} total comments");
            
            // 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();
            
            $this->log("Found " . count($comments) . " WordPress comments to import");
            
            // Build a map of WordPress post IDs to publication IDs using wp_post_id field
            $postIds = array_unique(array_column($comments, 'comment_post_ID'));
            $publicationMap = [];
            
            if (!empty($postIds)) {
                $placeholders = str_repeat('?,', count($postIds) - 1) . '?';
                $this->log("Executing publication mapping query with " . count($postIds) . " post IDs");
                $pubStmt = $this->targetDb->query("
                    SELECT id, wp_post_id FROM publications 
                    WHERE wp_post_id IN ($placeholders)
                ", $postIds);
                
                foreach ($pubStmt->fetchAll() as $pub) {
                    // Map WordPress post ID to publication ID
                    $publicationMap[$pub['wp_post_id']] = $pub['id'];
                }
                
                $this->log("Found " . count($publicationMap) . " publication mappings for comments");
            }
            
            foreach ($comments as $index => $wpComment) {
                try {
                    $this->log("Processing comment {$index}: '{$wpComment['comment_content']}' (Post ID: {$wpComment['comment_post_ID']})");
                    
                    // Find corresponding publication using wp_post_id mapping
                    $publicationId = null;
                    $wpPostId = $wpComment['comment_post_ID'];
                    
                    if (isset($publicationMap[$wpPostId])) {
                        $publicationId = $publicationMap[$wpPostId];
                    }
                    
                    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->log("Executing INSERT for comment: '{$wpComment['comment_content']}' (Post ID: {$wpPostId})");
                    $this->targetDb->query(
                        "INSERT INTO comments (publication_id, parent_id, name, email, content, status, created_at, admin_reply, wp_comment_id) 
                         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        [
                            $publicationId,
                            $parentId,
                            $wpComment['comment_author'],
                            $wpComment['comment_author_email'],
                            $wpComment['comment_content'],
                            $status,
                            $wpComment['comment_date'],
                            false,
                            $wpComment['comment_ID']
                        ]
                    );
                    
                    $commentId = $this->targetDb->getConnection()->lastInsertId();
                    
                    $imported++;
                    $this->log("Successfully imported comment: '{$wpComment['comment_content']}' (ID: {$wpComment['comment_ID']})");
                    
                } catch (Exception $e) {
                    $skipped++;
                    $this->log("Skipped comment '{$wpComment['comment_content']}' (ID: {$wpComment['comment_ID']}): " . $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->query(
                    "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
        ];
    }
    
    public function getImportLog() {
        return $this->importLog;
    }
    
    /**
     * 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;
    }
}