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

// Get parameters
$subject_id = $_GET['subject_id'] ?? '';
$class_id = $_GET['class_id'] ?? '';
$session_name = $_GET['session'] ?? '';

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Enrollment 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> Enrollment Diagnostic Tool</h4>
        </div>
        <div class='card-body'>";

if (empty($subject_id) || empty($class_id) || empty($session_name)) {
    echo "<div class='alert alert-warning'>
            <i class='fas fa-exclamation-triangle'></i> 
            Please provide subject_id, class_id, and session parameters
          </div>";
    echo "</div></div></div></body></html>";
    exit();
}

try {
    // 1. Check if subject exists
    echo "<h5>1. Subject Information</h5>";
    $stmt = $pdo->prepare("SELECT id, name, code FROM subjects WHERE id = ?");
    $stmt->execute([$subject_id]);
    $subject = $stmt->fetch();
    
    if ($subject) {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Subject Found: {$subject['name']} ({$subject['code']}) - ID: {$subject['id']}
              </div>";
    } else {
        echo "<div class='alert alert-danger'>
                <i class='fas fa-times-circle'></i> 
                Subject with ID $subject_id not found
              </div>";
        exit();
    }
    
    // 2. Check if class exists
    echo "<h5>2. 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();
    }
    
    // 3. Check session
    echo "<h5>3. 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>";
    }
    
    // 4. Count total students in this class and session
    echo "<h5>4. Students in Class/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 ID $class_id, Session $session_name: $total_students
          </div>";
    
    // 5. Count students enrolled in this subject (regardless of class)
    echo "<h5>5. Students Enrolled in Subject (All Classes)</h5>";
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM student_subjects WHERE subject_id = ?");
    $stmt->execute([$subject_id]);
    $result = $stmt->fetch();
    $total_enrolled = $result['count'];
    
    echo "<div class='alert alert-info'>
            <i class='fas fa-book'></i> 
            Total Students Enrolled in Subject ID $subject_id (All Classes): $total_enrolled
          </div>";
    
    // 6. Count students enrolled in this subject AND in this class/session
    echo "<h5>6. Students Enrolled in Subject AND in Class/Session</h5>";
    $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();
    $matching_students = $result['count'];
    
    if ($matching_students > 0) {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Found $matching_students students enrolled in Subject ID $subject_id who are also in Class ID $class_id and Session $session_name
              </div>";
              
        // Show the actual students
        echo "<h5>7. List of Matching Students</h5>";
        $stmt = $pdo->prepare("
            SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session,
                   c.name as class_name, c.section
            FROM students s
            JOIN classes c ON s.class_id = c.id
            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 = ?
            ORDER BY s.roll_number
        ");
        $stmt->execute([$class_id, $session_name, $subject_id]);
        $students = $stmt->fetchAll();
        
        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>Class</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        foreach ($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['class_name']}-{$student['section']}</td>
                  </tr>";
        }
        
        echo "      </tbody>
                </table>
              </div>";
    } else {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No students found who are enrolled in Subject ID $subject_id AND in Class ID $class_id and Session $session_name
              </div>";
              
        // Let's see if there are students in the subject but in different classes/sessions
        echo "<h5>7. Diagnostic: Where Are the Enrolled Students?</h5>";
        $stmt = $pdo->prepare("
            SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.class_id,
                   c.name as class_name, c.section
            FROM students s
            JOIN classes c ON s.class_id = c.id
            JOIN student_subjects ss ON s.id = ss.student_id
            WHERE ss.subject_id = ? AND s.status = 'active'
            ORDER BY s.class_id, s.session, s.roll_number
            LIMIT 20
        ");
        $stmt->execute([$subject_id]);
        $enrolled_students = $stmt->fetchAll();
        
        if (!empty($enrolled_students)) {
            echo "<div class='alert alert-info'>
                    <i class='fas fa-info-circle'></i> 
                    Found students enrolled in this subject, but they are in different classes/sessions:
                  </div>";
                  
            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>Class</th>
                            </tr>
                        </thead>
                        <tbody>";
            
            foreach ($enrolled_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['class_name']}-{$student['section']} (ID: {$student['class_id']})</td>
                      </tr>";
            }
            
            echo "      </tbody>
                    </table>
                  </div>";
        } else {
            echo "<div class='alert alert-warning'>
                    <i class='fas fa-exclamation-triangle'></i> 
                    No students are enrolled in this subject at all
                  </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) . "&subject_id=" . urlencode($subject_id) . "&action=seatcards' class='btn btn-primary'>
            <i class='fas fa-arrow-left'></i> Back to Seat Cards
        </a>
      </div>";

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