<?php
/**
 * Debug script for comments API issues
 */

require_once 'config.php';

header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Origin: *');

// Enable detailed error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    echo "=== COMMENTS API DEBUG ===\n\n";
    
    // 1. Test database connection
    echo "1. Testing database connection...\n";
    $conn = getDBConnection();
    
    if ($conn === null) {
        echo "❌ Database connection FAILED\n";
        echo "Check config.php credentials\n\n";
        exit;
    } else {
        echo "✅ Database connection SUCCESS\n\n";
    }
    
    // 2. Check if lesson_comments table exists
    echo "2. Checking lesson_comments table...\n";
    try {
        $tableCheck = $conn->query("SHOW TABLES LIKE 'lesson_comments'");
        if ($tableCheck->rowCount() > 0) {
            echo "✅ lesson_comments table EXISTS\n";
            
            // Get table structure
            $structure = $conn->query("DESCRIBE lesson_comments")->fetchAll(PDO::FETCH_ASSOC);
            echo "Table structure:\n";
            foreach ($structure as $column) {
                echo "  - {$column['Field']}: {$column['Type']}\n";
            }
        } else {
            echo "❌ lesson_comments table MISSING\n";
            echo "Run create_comments_table.php first\n\n";
            exit;
        }
    } catch (Exception $e) {
        echo "❌ Error checking table: " . $e->getMessage() . "\n\n";
        exit;
    }
    
    echo "\n3. Testing basic query...\n";
    try {
        $stmt = $conn->prepare("SELECT COUNT(*) as total FROM lesson_comments WHERE lesson_id = ? AND is_approved = 1");
        $stmt->execute([1]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "✅ Basic query SUCCESS: {$result['total']} approved comments for lesson 1\n\n";
    } catch (Exception $e) {
        echo "❌ Basic query FAILED: " . $e->getMessage() . "\n\n";
        exit;
    }
    
    // 4. Check users table (needed for JOIN)
    echo "4. Checking users table...\n";
    try {
        $userCheck = $conn->query("SHOW TABLES LIKE 'users'");
        if ($userCheck->rowCount() > 0) {
            echo "✅ users table EXISTS\n";
            
            $userCount = $conn->query("SELECT COUNT(*) as total FROM users")->fetch(PDO::FETCH_ASSOC);
            echo "Total users: {$userCount['total']}\n";
        } else {
            echo "❌ users table MISSING\n";
            echo "This is needed for the JOIN in comments query\n\n";
            exit;
        }
    } catch (Exception $e) {
        echo "❌ Error checking users table: " . $e->getMessage() . "\n\n";
        exit;
    }
    
    echo "\n5. Testing the exact query from comments.php...\n";
    try {
        $stmt = $conn->prepare("
            SELECT 
                c.id,
                c.lesson_id,
                c.user_id,
                c.comment,
                c.is_approved,
                c.created_at,
                c.updated_at,
                u.user_name,
                u.email
            FROM lesson_comments c
            JOIN users u ON c.user_id = u.id
            WHERE c.lesson_id = ? AND c.is_approved = 1
            ORDER BY c.created_at DESC
            LIMIT ? OFFSET ?
        ");
        $stmt->execute([1, 5, 0]);
        $comments = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "✅ Full query SUCCESS: " . count($comments) . " comments returned\n\n";
    } catch (Exception $e) {
        echo "❌ Full query FAILED: " . $e->getMessage() . "\n";
        echo "This is likely the exact error causing the 500!\n\n";
        
        // Try to identify the specific issue
        if (strpos($e->getMessage(), 'user_name') !== false) {
            echo "💡 HINT: users table might not have 'user_name' column\n";
            echo "Check users table structure:\n";
            try {
                $userStructure = $conn->query("DESCRIBE users")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($userStructure as $column) {
                    echo "  - {$column['Field']}: {$column['Type']}\n";
                }
            } catch (Exception $e2) {
                echo "Error getting users table structure: " . $e2->getMessage() . "\n";
            }
        }
        exit;
    }
    
    echo "🎉 ALL TESTS PASSED!\n";
    echo "The comments API should be working now.\n";
    echo "If you're still getting 500 errors, check the server error logs.\n";
    
} catch (Exception $e) {
    echo "❌ FATAL ERROR: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
}
?>