<?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();
}

// Assume Class 11 is class_id = 14 (based on your previous query)
$class_id = 14;
$session_name = '2025-2026';

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Class 11 (Eleven) Diagnostic Tool</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> Class 11 (Eleven) Diagnostic Tool</h4>
            <p>Checking enrollment for Class ID: $class_id, Session: $session_name</p>
        </div>
        <div class='card-body'>";

try {
    // 1. Check if class exists
    echo "<h5>1. Class Information</h5>";
    $stmt = $pdo->prepare("SELECT id, name, section FROM classes WHERE id = ?");
    $stmt->execute([$class_id]);
    $class = $stmt->fetch();
    
    if ($class) {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Class Found: {$class['name']} - {$class['section']} - ID: {$class['id']}
              </div>";
    } else {
        echo "<div class='alert alert-danger'>
                <i class='fas fa-times-circle'></i> 
                Class with ID $class_id not found
              </div>";
        exit();
    }
    
    // 2. Check session
    echo "<h5>2. Session Validation</h5>";
    $stmt = $pdo->prepare("SELECT session_name FROM academic_sessions WHERE session_name = ?");
    $stmt->execute([$session_name]);
    $session = $stmt->fetch();
    
    if ($session) {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Session Found: $session_name
              </div>";
    } else {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                Session $session_name not found in academic_sessions table
              </div>";
    }
    
    // 3. Count total students in this class and session
    echo "<h5>3. Students in Class 11 / Session</h5>";
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE class_id = ? AND session = ? AND status = 'active'");
    $stmt->execute([$class_id, $session_name]);
    $result = $stmt->fetch();
    $total_students = $result['count'];
    
    echo "<div class='alert alert-info'>
            <i class='fas fa-users'></i> 
            Total Active Students in Class 11, Session $session_name: $total_students
          </div>";
          
    if ($total_students == 0) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No active students found in Class 11 for session $session_name
              </div>";
        exit();
    }
    
    // 4. Get all subjects assigned to this class
    echo "<h5>4. Subjects Assigned to Class 11</h5>";
    $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 Class 11
              </div>";
    } else {
        echo "<div class='alert alert-success'>
                <i class='fas fa-book'></i> 
                Found " . count($class_subjects) . " subjects assigned to Class 11
              </div>";
              
        echo "<div class='table-responsive'>
                <table class='table table-striped table-bordered'>
                    <thead>
                        <tr>
                            <th>Subject ID</th>
                            <th>Subject Name</th>
                            <th>Code</th>
                            <th>Students Enrolled</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        foreach ($class_subjects as $subject) {
            // Count students enrolled in this subject for this class/session
            $stmt = $pdo->prepare("
                SELECT COUNT(*) as count 
                FROM students s
                JOIN student_subjects ss ON s.id = ss.student_id
                WHERE s.class_id = ? AND s.session = ? AND s.status = 'active' AND ss.subject_id = ?
            ");
            $stmt->execute([$class_id, $session_name, $subject['id']]);
            $result = $stmt->fetch();
            $enrolled_count = $result['count'];
            
            $row_class = $enrolled_count > 0 ? 'table-success' : 'table-warning';
            
            echo "<tr class='$row_class'>
                    <td>{$subject['id']}</td>
                    <td>{$subject['name']}</td>
                    <td>{$subject['code']}</td>
                    <td>$enrolled_count</td>
                  </tr>";
        }
        
        echo "      </tbody>
                </table>
              </div>";
    }
    
    // 5. Detailed student-subject enrollment analysis
    echo "<h5>5. Detailed Enrollment Analysis</h5>";
    
    // Get all students in this class
    $stmt = $pdo->prepare("
        SELECT id, student_id, first_name, last_name, roll_number
        FROM students 
        WHERE class_id = ? AND session = ? AND status = 'active'
        ORDER BY roll_number
    ");
    $stmt->execute([$class_id, $session_name]);
    $students = $stmt->fetchAll();
    
    if (!empty($students)) {
        echo "<div class='table-responsive'>
                <table class='table table-striped table-bordered'>
                    <thead>
                        <tr>
                            <th>Roll</th>
                            <th>Student ID</th>
                            <th>Name</th>
                            <th>Subjects Enrolled</th>
                            <th>Count</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        foreach ($students as $student) {
            // Get subjects for this student
            $stmt = $pdo->prepare("
                SELECT s.id, s.name, s.code
                FROM student_subjects ss
                JOIN subjects s ON ss.subject_id = s.id
                WHERE ss.student_id = ? AND s.status = 'active'
                ORDER BY s.name
            ");
            $stmt->execute([$student['id']]);
            $student_subjects = $stmt->fetchAll();
            
            $subject_names = array_map(function($s) { return $s['name'] . ' (' . $s['code'] . ')'; }, $student_subjects);
            $subjects_display = implode(', ', $subject_names);
            $subject_count = count($student_subjects);
            
            $row_class = $subject_count > 0 ? '' : 'table-warning';
            
            echo "<tr class='$row_class'>
                    <td>{$student['roll_number']}</td>
                    <td>{$student['student_id']}</td>
                    <td>{$student['first_name']} {$student['last_name']}</td>
                    <td>" . (empty($subjects_display) ? '<em>No subjects</em>' : $subjects_display) . "</td>
                    <td>$subject_count</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='subject_enrollment_tools.php?session=" . urlencode($session_name) . "&class_id=" . urlencode($class_id) . "&action=analyze' class='btn btn-primary'>
            <i class='fas fa-chart-pie'></i> Back to Subject Analysis
        </a>
        <a href='subject_selection.php?class_id=" . urlencode($class_id) . "' class='btn btn-secondary'>
            <i class='fas fa-tasks'></i> Subject Selection
        </a>
      </div>";

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