<?php
// Subject Fetching Debug Script
error_reporting(E_ALL);
ini_set('display_errors', 1);

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "report_system";

$connection = new mysqli($servername, $username, $password, $dbname);

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

echo "<h2>Subject Fetching Debug</h2>";

// Get test parameters (use actual values)
$year = $_GET['year'] ?? '2025';
$term = $_GET['term'] ?? 'TERM3';
$exam_id = $_GET['exam_id'] ?? 40;
$class_id = $_GET['class_id'] ?? 58;

echo "<h3>Test Parameters</h3>";
echo "Year: $year, Term: $term, Exam ID: $exam_id, Class ID: $class_id<br><br>";

// Step 1: Check if exam exists
echo "<h3>Step 1: Check Exam</h3>";
$sql_exam = "SELECT * FROM exams WHERE id = ? AND year = ? AND term = ?";
$stmt_exam = $connection->prepare($sql_exam);
$stmt_exam->bind_param("iis", $exam_id, $year, $term);
$stmt_exam->execute();
$result_exam = $stmt_exam->get_result();
$exam = $result_exam->fetch_assoc();

if ($exam) {
    echo "✓ Exam found: " . $exam['name'] . " (ID: $exam_id)<br><br>";
} else {
    echo "✗ Exam NOT found<br><br>";
    exit;
}

// Step 2: Check class and students
echo "<h3>Step 2: Check Class and Students</h3>";
$sql_class = "SELECT c.*, cl.class_level, cl.school_id 
              FROM classes c 
              JOIN class_levels cl ON c.class_level_id = cl.id 
              WHERE c.id = ?";
$stmt_class = $connection->prepare($sql_class);
$stmt_class->bind_param("i", $class_id);
$stmt_class->execute();
$result_class = $stmt_class->get_result();
$class = $result_class->fetch_assoc();

if ($class) {
    echo "✓ Class found: " . $class['name'] . " (Level: " . $class['class_level'] . ")<br><br>";
} else {
    echo "✗ Class NOT found<br><br>";
    exit;
}

// Get a few students from this class
$sql_students = "SELECT s.user_id, s.firstname, s.lastname, s.combination_id, sc.combination_name
                 FROM students s
                 LEFT JOIN subject_combinations sc ON s.combination_id = sc.combination_id
                 WHERE s.class_id = ?
                 LIMIT 3";
$stmt_students = $connection->prepare($sql_students);
$stmt_students->bind_param("i", $class_id);
$stmt_students->execute();
$result_students = $stmt_students->get_result();

$students = [];
while ($student = $result_students->fetch_assoc()) {
    $students[] = $student;
}

echo "Found " . count($students) . " students in class<br><br>";

// Step 3: Test subject fetching for each student
foreach ($students as $index => $student) {
    echo "<h3>Step 3: Testing Subject Fetching for Student #" . ($index + 1) . "</h3>";
    echo "Student: " . $student['firstname'] . " " . $student['lastname'] . "<br>";
    echo "Combination: " . ($student['combination_name'] ?? 'None') . " (ID: " . ($student['combination_id'] ?? 'None') . ")<br><br>";
    
    // Test the subject fetching query
    $class_level = $class['class_level'];
    $class_school_id = $class['school_id'];
    
    if ($class_level === 'senior_level' && !empty($student['combination_id'])) {
        echo "<strong>A-LEVEL with Combination:</strong><br>";
        $sql_subjects = "SELECT DISTINCT s.subject_id, s.subject_name, s.subject_code
                       FROM subjects s
                       JOIN combination_subjects cs ON s.subject_id = cs.subject_id
                       WHERE cs.combination_id = ? AND s.school_id = ?
                       ORDER BY s.subject_name";
        $stmt_subjects = $connection->prepare($sql_subjects);
        $stmt_subjects->bind_param("ii", $student['combination_id'], $class_school_id);
        
        echo "Query: Combination filter for ID: " . $student['combination_id'] . "<br>";
    } else {
        echo "<strong>O-LEVEL or No Combination:</strong><br>";
        $sql_subjects = "SELECT DISTINCT s.subject_id, s.subject_name, s.subject_code
                       FROM subjects s
                       WHERE s.school_id = ?
                       ORDER BY s.subject_name";
        $stmt_subjects = $connection->prepare($sql_subjects);
        $stmt_subjects->bind_param("i", $class_school_id);
        
        echo "Query: All subjects for school<br>";
    }
    
    if ($stmt_subjects->execute()) {
        $result_subjects = $stmt_subjects->get_result();
        $subject_count = $result_subjects->num_rows;
        
        echo "Subjects found: $subject_count<br>";
        
        if ($subject_count > 0) {
            echo "<ul>";
            while ($subject = $result_subjects->fetch_assoc()) {
                echo "<li>" . $subject['subject_name'] . " (" . $subject['subject_code'] . ")</li>";
            }
            echo "</ul>";
        } else {
            echo "<strong style='color: red;'>No subjects found!</strong><br>";
        }
    } else {
        echo "Query failed: " . $stmt_subjects->error . "<br>";
    }
    
    echo "<hr>";
}

// Step 4: Check combination_subjects table
echo "<h3>Step 4: Check Combination-Subjects Relationships</h3>";
$sql_combo_subjects = "SELECT sc.combination_id, sc.combination_name, s.subject_id, s.subject_name, s.subject_code
                       FROM subject_combinations sc
                       JOIN combination_subjects cs ON sc.combination_id = cs.combination_id
                       JOIN subjects s ON cs.subject_id = s.subject_id
                       ORDER BY sc.combination_id, s.subject_name";
$result_combo_subjects = $connection->query($sql_combo_subjects);

if ($result_combo_subjects && $result_combo_subjects->num_rows > 0) {
    echo "Combination-Subject relationships found:<br>";
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Combination</th><th>Subject Name</th><th>Subject Code</th></tr>";
    
    while ($row = $result_combo_subjects->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row['combination_name'] . " (ID: " . $row['combination_id'] . ")</td>";
        echo "<td>" . $row['subject_name'] . "</td>";
        echo "<td>" . $row['subject_code'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<strong style='color: red;'>No combination-subject relationships found!</strong><br>";
    echo "This means combinations exist but have no subjects assigned.<br>";
}

echo "<br><h3>Step 5: Check subjects table</h3>";
$sql_subjects_all = "SELECT subject_id, subject_name, subject_code, school_id FROM subjects ORDER BY school_id, subject_name";
$result_subjects_all = $connection->query($sql_subjects_all);

if ($result_subjects_all) {
    $total_subjects = $result_subjects_all->num_rows;
    echo "Total subjects in database: $total_subjects<br>";
    
    // Group by school
    $schools = [];
    while ($subject = $result_subjects_all->fetch_assoc()) {
        $schools[$subject['school_id']][] = $subject;
    }
    
    foreach ($schools as $school_id => $subjects) {
        echo "School ID $school_id: " . count($subjects) . " subjects<br>";
        echo "<ul>";
        foreach (array_slice($subjects, 0, 5) as $subject) {
            echo "<li>" . $subject['subject_name'] . " (" . $subject['subject_code'] . ")</li>";
        }
        if (count($subjects) > 5) {
            echo "<li>... and " . (count($subjects) - 5) . " more</li>";
        }
        echo "</ul>";
    }
} else {
    echo "Could not query subjects table: " . $connection->error . "<br>";
}

$connection->close();
?>