<?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 from URL
$session_name = $_GET['session'] ?? '2025-2026';
$class_id = $_GET['class_id'] ?? '14';
$group_name = $_GET['group_name'] ?? 'BUSINESS STUDIES';
$subject_id = $_GET['subject_id'] ?? '96';

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Diagnose Session Students</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 Session Students</h4>
            <p>Session: $session_name, Class ID: $class_id, Group: $group_name, Subject ID: $subject_id</p>
        </div>
        <div class='card-body'>";

try {
    // 1. Check if session exists and is active
    echo "<h5>1. Session Validation</h5>";
    $stmt = $pdo->prepare("SELECT session_name, status FROM academic_sessions WHERE session_name = ?");
    $stmt->execute([$session_name]);
    $session = $stmt->fetch();
    
    if (!$session) {
        echo "<div class='alert alert-danger'>
                <i class='fas fa-times-circle'></i> 
                Session '$session_name' does not exist in academic_sessions table
              </div>";
    } else {
        $status = $session['status'];
        $alert_class = ($status == 'active') ? 'alert-success' : 'alert-warning';
        $status_text = ($status == 'active') ? 'ACTIVE' : 'INACTIVE';
        
        echo "<div class='alert $alert_class'>
                <i class='fas fa-check-circle'></i> 
                Session '$session_name' exists and is $status_text
              </div>";
    }
    
    // 2. Check if class exists
    echo "<h5>2. Class Validation</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-danger'>
                <i class='fas fa-times-circle'></i> 
                Class with ID '$class_id' does not exist
              </div>";
    } else {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Class found: {$class['name']} - {$class['section']} (ID: {$class['id']})
              </div>";
    }
    
    // 3. Check if group exists
    if (!empty($group_name)) {
        echo "<h5>3. Group Validation</h5>";
        $stmt = $pdo->prepare("SELECT group_name FROM student_groups WHERE group_name = ?");
        $stmt->execute([$group_name]);
        $group = $stmt->fetch();
        
        if (!$group) {
            echo "<div class='alert alert-warning'>
                    <i class='fas fa-exclamation-triangle'></i> 
                    Group '$group_name' does not exist in student_groups table
                  </div>";
        } else {
            echo "<div class='alert alert-success'>
                    <i class='fas fa-check-circle'></i> 
                    Group '$group_name' exists
                  </div>";
        }
    }
    
    // 4. Check if subject exists
    echo "<h5>4. Subject Validation</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-danger'>
                <i class='fas fa-times-circle'></i> 
                Subject with ID '$subject_id' does not exist
              </div>";
    } else {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Subject found: {$subject['name']} ({$subject['code']}) - ID: {$subject['id']}
              </div>";
    }
    
    // 5. Count students in this session/class/group
    echo "<h5>5. Student Count Analysis</h5>";
    
    // Count all students in this session
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE session = ? AND status = 'active'");
    $stmt->execute([$session_name]);
    $total_session_students = $stmt->fetch()['count'];
    
    echo "<div class='alert alert-info'>
            <i class='fas fa-users'></i> 
            Total active students in session '$session_name': $total_session_students
          </div>";
    
    // Count students in this session and class
    if ($class) {
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE session = ? AND class_id = ? AND status = 'active'");
        $stmt->execute([$session_name, $class_id]);
        $session_class_students = $stmt->fetch()['count'];
        
        echo "<div class='alert alert-info'>
                <i class='fas fa-users'></i> 
                Students in session '$session_name' AND class ID '$class_id': $session_class_students
              </div>";
    }
    
    // Count students in this session, class, and group
    if ($class && !empty($group_name)) {
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE session = ? AND class_id = ? AND group_name = ? AND status = 'active'");
        $stmt->execute([$session_name, $class_id, $group_name]);
        $session_class_group_students = $stmt->fetch()['count'];
        
        echo "<div class='alert alert-info'>
                <i class='fas fa-users'></i> 
                Students in session '$session_name', class ID '$class_id', AND group '$group_name': $session_class_group_students
              </div>";
    }
    
    // 6. Check students enrolled in the subject
    echo "<h5>6. Subject Enrollment Analysis</h5>";
    
    // Count total students enrolled in this subject
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM student_subjects WHERE subject_id = ?");
    $stmt->execute([$subject_id]);
    $total_subject_enrollments = $stmt->fetch()['count'];
    
    echo "<div class='alert alert-info'>
            <i class='fas fa-book'></i> 
            Total students enrolled in subject ID '$subject_id': $total_subject_enrollments
          </div>";
    
    // Check students enrolled in this subject who are in our session/class/group
    if ($class) {
        $sql = "SELECT COUNT(*) as count 
                FROM students s
                JOIN student_subjects ss ON s.id = ss.student_id
                WHERE s.session = ? AND s.class_id = ? AND ss.subject_id = ? AND s.status = 'active'";
        
        $params = [$session_name, $class_id, $subject_id];
        
        if (!empty($group_name)) {
            $sql = "SELECT COUNT(*) as count 
                    FROM students s
                    JOIN student_subjects ss ON s.id = ss.student_id
                    WHERE s.session = ? AND s.class_id = ? AND s.group_name = ? AND ss.subject_id = ? AND s.status = 'active'";
            $params = [$session_name, $class_id, $group_name, $subject_id];
        }
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $matched_students = $stmt->fetch()['count'];
        
        $alert_class = ($matched_students > 0) ? 'alert-success' : 'alert-warning';
        
        echo "<div class='alert $alert_class'>
                <i class='fas fa-user-graduate'></i> 
                Students matching ALL criteria (session, class, " . (!empty($group_name) ? "group, " : "") . "subject): $matched_students
              </div>";
        
        // Show actual students if any found
        if ($matched_students > 0) {
            $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name,
                           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.session = ? AND s.class_id = ? AND ss.subject_id = ? AND s.status = 'active'
                    ORDER BY s.roll_number";
            
            $params = [$session_name, $class_id, $subject_id];
            
            if (!empty($group_name)) {
                $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name,
                               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.session = ? AND s.class_id = ? AND s.group_name = ? AND ss.subject_id = ? AND s.status = 'active'
                        ORDER BY s.roll_number";
                $params = [$session_name, $class_id, $group_name, $subject_id];
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $students = $stmt->fetchAll();
            
            echo "<h6>Matching Students:</h6>";
            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>
                                <th>Group</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>
                        <td>" . ($student['group_name'] ?? 'N/A') . "</td>
                      </tr>";
            }
            
            echo "      </tbody>
                    </table>
                  </div>";
        } else {
            // Diagnostic: Show students in session/class but not enrolled in subject
            echo "<h6>Diagnostic: Students in session/class but not enrolled in subject</h6>";
            
            $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name,
                           c.name as class_name, c.section
                    FROM students s
                    JOIN classes c ON s.class_id = c.id
                    WHERE s.session = ? AND s.class_id = ? AND s.status = 'active'
                    ORDER BY s.roll_number";
            
            $params = [$session_name, $class_id];
            
            if (!empty($group_name)) {
                $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name,
                               c.name as class_name, c.section
                        FROM students s
                        JOIN classes c ON s.class_id = c.id
                        WHERE s.session = ? AND s.class_id = ? AND s.group_name = ? AND s.status = 'active'
                        ORDER BY s.roll_number";
                $params = [$session_name, $class_id, $group_name];
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $students = $stmt->fetchAll();
            
            if (!empty($students)) {
                echo "<div class='alert alert-info'>
                        Found " . count($students) . " students in session/class" . (!empty($group_name) ? "/group" : "") . " but none are enrolled in the subject
                      </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>
                                    <th>Group</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>
                            <td>" . ($student['group_name'] ?? 'N/A') . "</td>
                          </tr>";
                }
                
                echo "      </tbody>
                        </table>
                      </div>";
            } else {
                echo "<div class='alert alert-warning'>
                        No students found in session '$session_name', class ID '$class_id'" . (!empty($group_name) ? ", group '$group_name'" : "") . "
                      </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) . "&group_name=" . urlencode($group_name) . "&subject_id=" . urlencode($subject_id) . "&action=analyze' class='btn btn-primary'>
            <i class='fas fa-arrow-left'></i> Back to Subject Tools
        </a>
        <a href='check_sessions.php' class='btn btn-secondary'>
            <i class='fas fa-search'></i> Session Diagnostic
        </a>
      </div>";

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