<?php
// Replicate the exact logic from student_filter.php

// Simulate the GET parameters exactly as they would be in the URL
$_GET['session'] = '2025';
$_GET['class_id'] = '10';
$_GET['group_name'] = 'HUMANITIES';
$_GET['subject_id'] = '35';
$_GET['action'] = 'view';

require_once 'config/database.php';

// Fetch filters (lines 18-23)
$session_name = $_GET['session'] ?? '';
$class_id     = $_GET['class_id'] ?? '';
$group_name   = $_GET['group_name'] ?? '';
$subject_id   = $_GET['subject_id'] ?? '';
$exam_type    = $_GET['exam_type'] ?? '';
$action       = $_GET['action'] ?? 'view'; // view | download

echo "Parameters extracted from \$_GET:\n";
echo "- session_name: '$session_name'\n";
echo "- class_id: '$class_id'\n";
echo "- group_name: '$group_name'\n";
echo "- subject_id: '$subject_id'\n";
echo "- action: '$action'\n\n";

// Load subjects for dropdown (lines 47-81)
$class_subjects = [];
if (!empty($class_id)) {
    if ($session_name !== '' && $group_name !== '') {
        // Normalize group equivalents: treat "General" and "সাধারণ" as the same group
        $group_variants = in_array($group_name, ['General','সাধারণ'], true) ? ['General','সাধারণ',''] : [$group_name];
        $placeholders = implode(',', array_fill(0, count($group_variants), '?'));
        
        // Group-aware subjects based on actual student assignments (consider variants)
        $stmt = $pdo->prepare("SELECT DISTINCT sub.id, sub.name, sub.code
                               FROM student_subjects ss
                               JOIN students st ON ss.student_id = st.id
                               JOIN subjects sub ON sub.id = ss.subject_id
                               WHERE st.status='active' AND sub.status='active'
                                 AND st.session = ? AND st.class_id = ? AND COALESCE(st.group_name,'') IN ($placeholders)
                               ORDER BY sub.name");
        $execParams = array_merge([$session_name, $class_id], $group_variants);
        $stmt->execute($execParams);
        $class_subjects = $stmt->fetchAll();
        echo "Loaded " . count($class_subjects) . " subjects from student assignments\n";
    }
    if (!$class_subjects) {
        // Fall back to class mapped subjects
        $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();
        echo "Loaded " . count($class_subjects) . " subjects from class mapping\n";
    }
}
if (!$class_subjects) {
    // Ultimate fallback: all active subjects
    $stmt = $pdo->query("SELECT id, name, code FROM subjects WHERE status='active' ORDER BY name");
    $class_subjects = $stmt->fetchAll();
    echo "Loaded " . count($class_subjects) . " subjects from ultimate fallback\n";
}

// Build base filters for students (lines 83-100)
$where = ["s.status='active'"];
$params = [];
if ($session_name !== '') { $where[] = 's.session = ?'; $params[] = $session_name; }
if ($class_id !== '') { $where[] = 's.class_id = ?'; $params[] = $class_id; }
if ($group_name !== '') {
    // Treat "General" and "সাধারণ" as same group in filter
    if (in_array($group_name, ['General','সাধারণ'], true)) {
        $where[] = "COALESCE(s.group_name,'') IN (?, ?, ?)";
        $params[] = 'General';
        $params[] = 'সাধারণ';
        $params[] = '';
    } else {
        $where[] = "COALESCE(s.group_name,'') = ?";
        $params[] = $group_name;
    }
}
$where_sql = implode(' AND ', $where);

echo "\nWHERE clause: $where_sql\n";
echo "Parameters: " . json_encode($params) . "\n\n";

// Execute student query (lines 131-144)
$students = [];
if ($action === 'view' && $subject_id !== '' && $class_id !== '' && $session_name !== '') {
    $sql = "SELECT s.id, s.roll_number, s.student_id, s.first_name, s.last_name,
                   s.session, c.name AS class_name, c.section, COALESCE(s.group_name,'') AS group_name,
                   sub.name AS subject_name, sub.code AS subject_code
            FROM students s
            JOIN classes c ON s.class_id = c.id
            JOIN student_subjects ss ON ss.student_id = s.id AND ss.subject_id = ?
            JOIN subjects sub ON sub.id = ss.subject_id
            WHERE $where_sql
            ORDER BY s.roll_number";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array_merge([$subject_id], $params));
    $students = $stmt->fetchAll();
    
    echo "QUERY EXECUTED SUCCESSFULLY\n";
    echo "Number of students found: " . count($students) . "\n";
    
    if (!empty($students)) {
        echo "\nFirst 3 students:\n";
        for ($i = 0; $i < min(3, count($students)); $i++) {
            $s = $students[$i];
            echo "- {$s['roll_number']}. {$s['first_name']} {$s['last_name']} (Session: {$s['session']}, Class: {$s['class_name']}-{$s['section']}, Group: '{$s['group_name']}')\n";
        }
    }
} else {
    echo "Student query NOT executed because conditions weren't met\n";
    echo "- action === 'view': " . ($action === 'view' ? 'YES' : 'NO') . "\n";
    echo "- subject_id !== '': " . ($subject_id !== '' ? 'YES' : 'NO') . " ('$subject_id')\n";
    echo "- class_id !== '': " . ($class_id !== '' ? 'YES' : 'NO') . " ('$class_id')\n";
    echo "- session_name !== '': " . ($session_name !== '' ? 'YES' : 'NO') . " ('$session_name')\n";
}

// Check if subject ID 35 is in the dropdown
$subject_found = false;
foreach ($class_subjects as $subj) {
    if ($subj['id'] == $subject_id) {
        $subject_found = true;
        echo "\nSubject ID $subject_id FOUND in dropdown: {$subj['name']} ({$subj['code']})\n";
        break;
    }
}
if (!$subject_found) {
    echo "\nSubject ID $subject_id NOT FOUND in dropdown\n";
    echo "Available subjects in dropdown:\n";
    foreach ($class_subjects as $subj) {
        echo "- ID: {$subj['id']}, Name: {$subj['name']} ({$subj['code']})\n";
    }
}
?>