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

// Check if user is logged in and has permission
if (!isset($_SESSION['user_id']) || ($_SESSION['user_role'] != 'admin' && $_SESSION['user_role'] != 'teacher')) {
    header('Location: ../login.php');
    exit();
}

// Get teacher ID if user is a teacher
$teacher_id = null;
$user_role = $_SESSION['user_role'];
$user_id = $_SESSION['user_id'];

if ($user_role == 'teacher') {
    $stmt = $pdo->prepare("SELECT id FROM teachers WHERE user_id = ?");
    $stmt->execute([$user_id]);
    $teacher = $stmt->fetch();
    if ($teacher) {
        $teacher_id = $teacher['id'];
    }
}

// Get academic sessions
$stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
$sessions = $stmt->fetchAll();

// Also get sessions that exist in the students table (to handle mismatched session names)
$stmt2 = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session DESC");
$student_sessions = $stmt2->fetchAll();

// Merge sessions, avoiding duplicates
$all_sessions = $sessions;
foreach ($student_sessions as $student_session) {
    $found = false;
    foreach ($sessions as $session) {
        if ($session['session_name'] == $student_session['session']) {
            $found = true;
            break;
        }
    }
    if (!$found) {
        $all_sessions[] = ['session_name' => $student_session['session']];
    }
}
$sessions = $all_sessions;

// Get classes based on user role
if ($user_role == 'admin') {
    // Admin can see all classes
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
    $classes = $stmt->fetchAll();
} else if ($user_role == 'teacher' && $teacher_id) {
    // Teachers can only see their assigned classes
    $stmt = $pdo->prepare("
        SELECT DISTINCT c.id, c.name, c.section 
        FROM classes c 
        JOIN class_teachers ct ON c.id = ct.class_id
        WHERE ct.teacher_id = ? AND ct.status = 'active' AND c.status = 'active'
        ORDER BY c.name, c.section
    ");
    $stmt->execute([$teacher_id]);
    $classes = $stmt->fetchAll();
    
    // Also get classes where this teacher teaches subjects (for backward compatibility)
    $stmt = $pdo->prepare("
        SELECT DISTINCT c.id, c.name, c.section
        FROM classes c
        JOIN class_subjects cs ON c.id = cs.class_id
        WHERE cs.teacher_id = ? AND c.status = 'active'
        ORDER BY c.name, c.section
    ");
    $stmt->execute([$teacher_id]);
    $subject_classes = $stmt->fetchAll();
    
    // Merge both arrays, avoiding duplicates
    foreach ($subject_classes as $class) {
        $found = false;
        foreach ($classes as $assigned) {
            if ($assigned['id'] == $class['id']) {
                $found = true;
                break;
            }
        }
        if (!$found) {
            $classes[] = $class;
        }
    }
} else {
    $classes = [];
}

// Get student groups
$stmt = $pdo->query("SELECT group_name, group_code, description FROM student_groups WHERE status = 'active' ORDER BY group_name");
$groups = $stmt->fetchAll();

// Get filter parameters
$filter_session = $_GET['session'] ?? '';
$filter_class_id = $_GET['class_id'] ?? '';
$filter_group_name = $_GET['group_name'] ?? '';

// Get students based on filters
$students = [];
if ($filter_session || $filter_class_id || $filter_group_name) {
    $where_conditions = ["s.status = 'active'"];
    $params = [];
    
    if (!empty($filter_session)) {
        $where_conditions[] = "s.session = ?";
        $params[] = $filter_session;
    }
    
    if (!empty($filter_class_id)) {
        $where_conditions[] = "s.class_id = ?";
        $params[] = $filter_class_id;
    }
    
    if (!empty($filter_group_name)) {
        $where_conditions[] = "s.group_name = ?";
        $params[] = $filter_group_name;
    }
    
    $where_clause = implode(' AND ', $where_conditions);
    
    $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.class_id, 
               c.name as class_name, c.section as class_section,
               s.session, s.group_name
        FROM students s
        LEFT JOIN classes c ON s.class_id = c.id
        WHERE $where_clause
        ORDER BY s.roll_number";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get subjects for each student
    foreach ($students as &$student) {
        $stmt = $pdo->prepare("
            SELECT ss.subject_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(PDO::FETCH_ASSOC);
    }
    unset($student);
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>বিষয় নির্বাচন তালিকা - স্কুল ম্যানেজমেন্ট সিস্টেম</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">
    <link href="../assets/css/style.css" rel="stylesheet">
    <style>
        .filter-section {
            background-color: #f8f9fa;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
        }
        .table th {
            background-color: #f8f9fa;
        }
        .subject-badge {
            display: inline-block;
            background-color: #e9ecef;
            padding: 2px 8px;
            border-radius: 12px;
            margin: 2px;
            font-size: 0.85rem;
        }
    </style>
</head>
<body>
    <!-- Navigation -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
        <div class="container-fluid">
            <a class="navbar-brand" href="../index.php">
                <i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম
            </a>
            <div class="navbar-nav ms-auto">
                <?php if (isset($_SESSION['user_role']) && $_SESSION['user_role'] == 'teacher'): ?>
                    <a class="nav-link" href="../teachers/dashboard.php">
                        <i class="fas fa-home"></i> হোম
                    </a>
                <?php else: ?>
                    <a class="nav-link" href="../index.php">
                        <i class="fas fa-home"></i> হোম
                    </a>
                <?php endif; ?>
                <a class="nav-link" href="subject_selection.php">
                    <i class="fas fa-tasks"></i> বিষয় নির্বাচন
                </a>
                <a class="nav-link" href="../logout.php">
                    <i class="fas fa-sign-out-alt"></i> লগআউট
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4">
        <div class="row">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header">
                        <h5><i class="fas fa-list"></i> বিষয় নির্বাচন তালিকা</h5>
                        <p class="mb-0 text-muted">সেশন, ক্লাস, গ্রুপ ও শিক্ষার্থী ভিত্তিক বিষয় নির্বাচন তালিকা</p>
                    </div>
                    <div class="card-body">
                        <!-- Filter Section -->
                        <div class="filter-section">
                            <h6><i class="fas fa-filter"></i> ফিল্টার অপশন</h6>
                            <form method="GET">
                                <div class="row">
                                    <div class="col-md-3">
                                        <label for="filter_session" class="form-label">সেশন</label>
                                        <select class="form-select" id="filter_session" name="session">
                                            <option value="">সব সেশন</option>
                                            <?php foreach ($sessions as $session): ?>
                                                <option value="<?php echo $session['session_name']; ?>" <?php echo ($filter_session == $session['session_name']) ? 'selected' : ''; ?>>
                                                    <?php echo $session['session_name']; ?>
                                                </option>
                                            <?php endforeach; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label for="filter_class" class="form-label">ক্লাস</label>
                                        <select class="form-select" id="filter_class" name="class_id">
                                            <option value="">সব ক্লাস</option>
                                            <?php foreach ($classes as $class): ?>
                                                <option value="<?php echo $class['id']; ?>" <?php echo ($filter_class_id == $class['id']) ? 'selected' : ''; ?>>
                                                    <?php echo $class['name'] . ' - ' . $class['section']; ?>
                                                </option>
                                            <?php endforeach; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label for="filter_group" class="form-label">গ্রুপ</label>
                                        <select class="form-select" id="filter_group" name="group_name">
                                            <option value="">সব গ্রুপ</option>
                                            <?php foreach ($groups as $group): ?>
                                                <option value="<?php echo $group['group_name']; ?>" <?php echo ($filter_group_name == $group['group_name']) ? 'selected' : ''; ?>>
                                                    <?php echo $group['group_name'] . ' (' . $group['group_code'] . ')'; ?>
                                                </option>
                                            <?php endforeach; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label class="form-label">&nbsp;</label>
                                        <div>
                                            <button type="submit" class="btn btn-primary">
                                                <i class="fas fa-search"></i> খুঁজুন
                                            </button>
                                            <a href="subject_selection_list.php" class="btn btn-outline-secondary">
                                                <i class="fas fa-sync"></i> রিসেট
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </form>
                        </div>

                        <!-- Students Table -->
                        <?php if (!empty($students)): ?>
                            <div class="table-responsive">
                                <table class="table table-striped table-hover">
                                    <thead>
                                        <tr>
                                            <th>নাম</th>
                                            <th>আইডি</th>
                                            <th>রোল</th>
                                            <th>ক্লাস</th>
                                            <th>গ্রুপ</th>
                                            <th>সেশন</th>
                                            <th>বিষয়সমূহ</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($students as $student): ?>
                                            <tr>
                                                <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                                <td><?php echo htmlspecialchars($student['student_id']); ?></td>
                                                <td><?php echo htmlspecialchars($student['roll_number']); ?></td>
                                                <td><?php echo htmlspecialchars($student['class_name'] . ' - ' . $student['class_section']); ?></td>
                                                <td><?php echo htmlspecialchars($student['group_name']); ?></td>
                                                <td><?php echo htmlspecialchars($student['session']); ?></td>
                                                <td>
                                                    <?php if (!empty($student['subjects'])): ?>
                                                        <?php foreach ($student['subjects'] as $subject): ?>
                                                            <span class="subject-badge">
                                                                <?php echo htmlspecialchars($subject['code']); ?>
                                                            </span>
                                                        <?php endforeach; ?>
                                                    <?php else: ?>
                                                        <span class="text-muted">কোন বিষয় নির্বাচিত নেই</span>
                                                    <?php endif; ?>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        <?php elseif ($filter_session || $filter_class_id || $filter_group_name): ?>
                            <div class="alert alert-info">
                                <i class="fas fa-info-circle"></i> নির্বাচিত ফিল্টারের জন্য কোন শিক্ষার্থী খুঁজে পাওয়া যায়নি।
                            </div>
                        <?php else: ?>
                            <div class="text-center text-muted py-5">
                                <i class="fas fa-user-graduate fa-3x mb-3"></i>
                                <h5>শিক্ষার্থী খুঁজতে উপরের ফিল্টার ব্যবহার করুন</h5>
                                <p>সেশন, ক্লাস বা গ্রুপ নির্বাচন করে "খুঁজুন" বাটনে ক্লিক করুন</p>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>