<?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>Compare Students List vs Subject Enrollment</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-balance-scale'></i> Compare Students List vs Subject Enrollment</h4>
        </div>
        <div class='card-body'>";

try {
    // First, let's find the 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>";
              
        // Let's show all classes to help identify
        echo "<h5>All Available Classes:</h5>";
        $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
        $all_classes = $stmt->fetchAll();
        
        echo "<div class='table-responsive'>
                <table class='table table-striped table-bordered'>
                    <thead>
                        <tr>
                            <th>Class ID</th>
                            <th>Class Name</th>
                            <th>Section</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        foreach ($all_classes as $class) {
            echo "<tr>
                    <td>{$class['id']}</td>
                    <td>{$class['name']}</td>
                    <td>{$class['section']}</td>
                  </tr>";
        }
        
        echo "      </tbody>
                </table>
              </div>";
    } else {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Found " . count($eleven_classes) . " classes with 'ELEVEN' in the name
              </div>";
        
        foreach ($eleven_classes as $class) {
            $class_id = $class['id'];
            $class_name = $class['name'];
            $class_section = $class['section'];
            
            echo "<h5>Class: {$class_name} - {$class_section} (ID: {$class_id})</h5>";
            
            // Get students in this class from students table
            echo "<h6>Students in this class (from students table):</h6>";
            $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]);
            $students_in_class = $stmt->fetchAll();
            
            echo "<div class='alert alert-info'>
                    Total students in class: " . count($students_in_class) . "
                  </div>";
            
            if (!empty($students_in_class)) {
                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>
                                    <th>Subject Count</th>
                                </tr>
                            </thead>
                            <tbody>";
                
                foreach ($students_in_class as $student) {
                    // Count subjects for this student
                    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM student_subjects WHERE student_id = ?");
                    $stmt->execute([$student['id']]);
                    $subject_count_result = $stmt->fetch();
                    $subject_count = $subject_count_result['count'];
                    
                    $row_class = $subject_count > 0 ? 'table-success' : 'table-warning';
                    
                    echo "<tr class='$row_class'>
                            <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>
                            <td>$subject_count</td>
                          </tr>";
                }
                
                echo "      </tbody>
                        </table>
                      </div>";
            }
            
            // Get active students in this class (status = 'active')
            echo "<h6>Active Students in this class (status = 'active'):</h6>";
            $stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, session FROM students WHERE class_id = ? AND status = 'active' ORDER BY roll_number");
            $stmt->execute([$class_id]);
            $active_students = $stmt->fetchAll();
            
            echo "<div class='alert alert-info'>
                    Total active students in class: " . count($active_students) . "
                  </div>";
            
            if (!empty($active_students)) {
                // Get subjects assigned to this class
                echo "<h6>Subjects assigned to this class:</h6>";
                $stmt = $pdo->prepare("
                    SELECT s.id, s.name, s.code
                    FROM class_subjects cs
                    JOIN subjects s ON cs.subject_id = s.id
                    WHERE cs.class_id = ? AND s.status = 'active'
                    ORDER BY s.name
                ");
                $stmt->execute([$class_id]);
                $class_subjects = $stmt->fetchAll();
                
                if (empty($class_subjects)) {
                    echo "<div class='alert alert-warning'>
                            <i class='fas fa-exclamation-triangle'></i> 
                            No subjects assigned to this class
                          </div>";
                } else {
                    echo "<div class='alert alert-success'>
                            <i class='fas fa-book'></i> 
                            Found " . count($class_subjects) . " subjects assigned to this class
                          </div>";
                    
                    echo "<ul>";
                    foreach ($class_subjects as $subject) {
                        echo "<li>{$subject['name']} ({$subject['code']}) - ID: {$subject['id']}</li>";
                    }
                    echo "</ul>";
                }
            }
        }
    }
    
    // Check academic sessions
    echo "<h5 class='mt-4'>Available Academic Sessions</h5>";
    $stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
    $sessions = $stmt->fetchAll();
    
    if (!empty($sessions)) {
        echo "<ul>";
        foreach ($sessions as $session) {
            echo "<li>{$session['session_name']}</li>";
        }
        echo "</ul>";
    }
    
} 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>";