<?php
session_start();
require_once '../config/database.php';

// Auth check
if (!isset($_SESSION['user_id']) || ($_SESSION['user_role'] != 'admin' && $_SESSION['user_role'] != 'teacher')) {
    header('Location: ../login.php');
    exit();
}

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Diagnose ELEVEN Class Issue</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css' rel='stylesheet'>
    <link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css' rel='stylesheet'>
</head>
<body>
<div class='container mt-4'>
    <div class='card'>
        <div class='card-header'>
            <h4><i class='fas fa-diagnoses'></i> Diagnose ELEVEN Class Issue</h4>
        </div>
        <div class='card-body'>";

try {
    // Find ELEVEN class
    echo "<h5>1. Finding ELEVEN Class</h5>";
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE name LIKE '%ELEVEN%' AND status = 'active' ORDER BY name, section");
    $eleven_classes = $stmt->fetchAll();
    
    if (empty($eleven_classes)) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No classes found with 'ELEVEN' in the name
              </div>";
        exit();
    }
    
    foreach ($eleven_classes as $class) {
        $class_id = $class['id'];
        $class_name = $class['name'];
        $class_section = $class['section'];
        
        echo "<div class='alert alert-success'>
                Found ELEVEN class: {$class_name} - {$class_section} (ID: {$class_id})
              </div>";
        
        // Get all students in this class (no session filter)
        echo "<h5>2. All Students in ELEVEN Class (No Session Filter)</h5>";
        $stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, session, status FROM students WHERE class_id = ? ORDER BY roll_number");
        $stmt->execute([$class_id]);
        $all_students = $stmt->fetchAll();
        
        echo "<div class='alert alert-info'>
                Total students in class (any session): " . count($all_students) . "
              </div>";
        
        if (!empty($all_students)) {
            // Show session distribution
            $session_counts = [];
            foreach ($all_students as $student) {
                $session = $student['session'] ?: 'NULL';
                if (!isset($session_counts[$session])) {
                    $session_counts[$session] = 0;
                }
                $session_counts[$session]++;
            }
            
            echo "<h6>Session Distribution:</h6>";
            echo "<ul>";
            foreach ($session_counts as $session => $count) {
                echo "<li>Session '$session': $count students</li>";
            }
            echo "</ul>";
            
            // Get students with status = 'active' (no session filter)
            echo "<h5>3. Active Students in ELEVEN Class (No Session Filter)</h5>";
            $stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, session, status FROM students WHERE class_id = ? AND status = 'active' ORDER BY roll_number");
            $stmt->execute([$class_id]);
            $active_students_no_session = $stmt->fetchAll();
            
            echo "<div class='alert alert-info'>
                    Total active students in class (any session): " . count($active_students_no_session) . "
                  </div>";
            
            // Now test what happens when we filter by session like the subject selection system does
            echo "<h5>4. Testing Session-Based Filters (Like Subject Selection)</h5>";
            
            // Get available sessions
            $stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
            $sessions = $stmt->fetchAll();
            
            if (!empty($sessions)) {
                foreach ($sessions as $session_row) {
                    $session_name = $session_row['session_name'];
                    
                    echo "<h6>Testing with Session: $session_name</h6>";
                    
                    // Get students with this specific session and active status
                    $stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, session, status FROM students WHERE class_id = ? AND session = ? AND status = 'active' ORDER BY roll_number");
                    $stmt->execute([$class_id, $session_name]);
                    $session_filtered_students = $stmt->fetchAll();
                    
                    $alert_class = count($session_filtered_students) > 0 ? 'alert-success' : 'alert-warning';
                    
                    echo "<div class='alert $alert_class'>
                            Students found with session '$session_name' and active status: " . count($session_filtered_students) . "
                          </div>";
                    
                    if (!empty($session_filtered_students) && count($session_filtered_students) > 0) {
                        echo "<div class='table-responsive'>
                                <table class='table table-striped table-bordered'>
                                    <thead>
                                        <tr>
                                            <th>Student ID</th>
                                            <th>Name</th>
                                            <th>Roll Number</th>
                                            <th>Session</th>
                                            <th>Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>";
                        
                        foreach ($session_filtered_students as $student) {
                            echo "<tr>
                                    <td>{$student['student_id']}</td>
                                    <td>{$student['first_name']} {$student['last_name']}</td>
                                    <td>{$student['roll_number']}</td>
                                    <td>{$student['session']}</td>
                                    <td>{$student['status']}</td>
                                  </tr>";
                        }
                        
                        echo "      </tbody>
                                </table>
                              </div>";
                    }
                }
            }
        }
    }
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger'>
            <i class='fas fa-exclamation-triangle'></i> 
            Error: " . $e->getMessage() . "
          </div>";
}

echo "<div class='mt-4'>
        <a href='find_classes.php' class='btn btn-primary'>
            <i class='fas fa-school'></i> Find Classes
        </a>
        <a href='../students/list.php' class='btn btn-secondary'>
            <i class='fas fa-users'></i> Students List
        </a>
        <a href='subject_selection.php' class='btn btn-info'>
            <i class='fas fa-tasks'></i> Subject Selection
        </a>
      </div>";

echo "</div></div></div></body></html>";