<?php
// Show EXACT database content without any hardcoding
// This will display what your database actually contains

require_once 'config.php';

echo "<h2>🔍 EXACT Database Content - No Hardcoding</h2>";
echo "<p><strong>Generated:</strong> " . date('Y-m-d H:i:s') . "</p>";

try {
    $connection = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
    
    if ($connection->connect_error) {
        throw new Exception("Connection failed: " . $connection->connect_error);
    }

    echo "<h3>📚 All Subjects in Your Database</h3>";
    $sql_subjects = "SELECT subject_id, subject_name FROM subjects ORDER BY subject_name";
    $result_subjects = $connection->query($sql_subjects);
    
    if ($result_subjects && $result_subjects->num_rows > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Subject ID</th><th>Subject Name</th></tr>";
        while ($row = $result_subjects->fetch_assoc()) {
            echo "<tr><td>{$row['subject_id']}</td><td>{$row['subject_name']}</td></tr>";
        }
        echo "</table>";
        echo "<p><strong>Total subjects:</strong> " . $result_subjects->num_rows . "</p>";
    } else {
        echo "<p>❌ No subjects found in subjects table</p>";
    }

    echo "<h3>📝 All Subject Papers in Your Database</h3>";
    $sql_papers = "SELECT sp.paper_id, sp.subject_id, s.subject_name, sp.paper_name 
                   FROM subject_papers sp 
                   JOIN subjects s ON sp.subject_id = s.subject_id 
                   ORDER BY s.subject_name, sp.paper_name";
    $result_papers = $connection->query($sql_papers);
    
    if ($result_papers && $result_papers->num_rows > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Paper ID</th><th>Subject ID</th><th>Subject Name</th><th>Paper Name</th></tr>";
        while ($row = $result_papers->fetch_assoc()) {
            echo "<tr><td>{$row['paper_id']}</td><td>{$row['subject_id']}</td><td>{$row['subject_name']}</td><td>{$row['paper_name']}</td></tr>";
        }
        echo "</table>";
        echo "<p><strong>Total papers:</strong> " . $result_papers->num_rows . "</p>";
    } else {
        echo "<p>❌ No papers found in subject_papers table</p>";
    }

    echo "<h3>🧮 Papers per Subject (Exact Count)</h3>";
    $sql_paper_count = "SELECT s.subject_name, COUNT(sp.paper_id) as paper_count
                        FROM subjects s 
                        LEFT JOIN subject_papers sp ON s.subject_id = sp.subject_id 
                        GROUP BY s.subject_id, s.subject_name 
                        ORDER BY s.subject_name";
    $result_count = $connection->query($sql_paper_count);
    
    if ($result_count && $result_count->num_rows > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Subject Name</th><th>Paper Count</th></tr>";
        while ($row = $result_count->fetch_assoc()) {
            echo "<tr><td>{$row['subject_name']}</td><td>{$row['paper_count']}</td></tr>";
        }
        echo "</table>";
    }

    echo "<h3>🏫 Class Subjects Setup</h3>";
    $sql_class_subjects = "SELECT c.class_name, s.subject_name
                          FROM class_subjects cs
                          JOIN classes c ON cs.class_id = c.class_id
                          JOIN subjects s ON cs.subject_id = s.subject_id
                          ORDER BY c.class_name, s.subject_name";
    $result_class = $connection->query($sql_class_subjects);
    
    if ($result_class && $result_class->num_rows > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Class</th><th>Subject</th></tr>";
        while ($row = $result_class->fetch_assoc()) {
            echo "<tr><td>{$row['class_name']}</td><td>{$row['subject_name']}</td></tr>";
        }
        echo "</table>";
        echo "<p><strong>Total class-subject mappings:</strong> " . $result_class->num_rows . "</p>";
    } else {
        echo "<p>❌ No class-subject mappings found</p>";
    }

    echo "<h3>🎯 Student Combinations (S5-6)</h3>";
    $sql_combinations = "SELECT DISTINCT u.subject_combination, COUNT(*) as student_count
                        FROM users u 
                        WHERE u.role = 3 AND u.subject_combination != '' 
                        GROUP BY u.subject_combination 
                        ORDER BY u.subject_combination";
    $result_combo = $connection->query($sql_combinations);
    
    if ($result_combo && $result_combo->num_rows > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Combination</th><th>Student Count</th></tr>";
        while ($row = $result_combo->fetch_assoc()) {
            echo "<tr><td>{$row['subject_combination']}</td><td>{$row['student_count']}</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<p>❌ No student combinations found</p>";
    }

    echo "<hr>";
    echo "<h3>🎉 Summary</h3>";
    echo "<ul>";
    echo "<li><strong>Subjects in database:</strong> " . ($result_subjects ? $result_subjects->num_rows : 0) . "</li>";
    echo "<li><strong>Total papers:</strong> " . ($result_papers ? $result_papers->num_rows : 0) . "</li>";
    echo "<li><strong>Class-subject mappings:</strong> " . ($result_class ? $result_class->num_rows : 0) . "</li>";
    echo "<li><strong>Student combinations:</strong> " . ($result_combo ? $result_combo->num_rows : 0) . "</li>";
    echo "</ul>";
    
    echo "<p><strong>✅ The system now shows EXACTLY what is in your database - no hardcoded values!</strong></p>";
    echo "<p>If Physics shows only 2 papers, that's what will be displayed. If there are no S1-2 subjects in class_subjects, then no subjects will show (which is correct database behavior).</p>";

} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}

$connection->close();
?>