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

// Auth check
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

// Collect filters (align broadly with students/list.php)
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$class_id = isset($_GET['class_id']) ? (int)$_GET['class_id'] : 0;
$status = isset($_GET['status']) ? $_GET['status'] : 'active';
$first_name = isset($_GET['first_name']) ? trim($_GET['first_name']) : '';
$last_name = isset($_GET['last_name']) ? trim($_GET['last_name']) : '';
$student_id_search = isset($_GET['student_id']) ? trim($_GET['student_id']) : '';
$roll_number_search = isset($_GET['roll_number']) ? trim($_GET['roll_number']) : '';
$email_search = isset($_GET['email']) ? trim($_GET['email']) : '';
$phone_search = isset($_GET['phone']) ? trim($_GET['phone']) : '';
$session_search = isset($_GET['session']) ? trim($_GET['session']) : '';
$group_name_search = isset($_GET['group_name']) ? trim($_GET['group_name']) : '';
$gender_filter = isset($_GET['gender']) ? $_GET['gender'] : '';

$where = ["s.status = :status"];
$params = [':status' => $status];

if (!empty($search)) {
    $where[] = "(s.first_name LIKE :search OR s.last_name LIKE :search OR s.student_id LIKE :search OR s.email LIKE :search OR s.phone LIKE :search)";
    $params[':search'] = "%$search%";
}
if (!empty($first_name)) { $where[] = "s.first_name LIKE :first_name"; $params[':first_name'] = "%$first_name%"; }
if (!empty($last_name)) { $where[] = "s.last_name LIKE :last_name"; $params[':last_name'] = "%$last_name%"; }
if (!empty($student_id_search)) { $where[] = "s.student_id LIKE :student_id"; $params[':student_id'] = "%$student_id_search%"; }
if (!empty($roll_number_search)) { $where[] = "s.roll_number = :roll_number"; $params[':roll_number'] = $roll_number_search; }
if (!empty($email_search)) { $where[] = "s.email LIKE :email"; $params[':email'] = "%$email_search%"; }
if (!empty($phone_search)) { $where[] = "s.phone LIKE :phone"; $params[':phone'] = "%$phone_search%"; }
if (!empty($session_search)) { $where[] = "s.session LIKE :session"; $params[':session'] = "%$session_search%"; }
if (!empty($group_name_search)) { $where[] = "COALESCE(s.group_name,'') LIKE :group_name"; $params[':group_name'] = "%$group_name_search%"; }
if (!empty($gender_filter)) { $where[] = "s.gender = :gender"; $params[':gender'] = $gender_filter; }
if ($class_id > 0) { $where[] = "s.class_id = :class_id"; $params[':class_id'] = $class_id; }

$where_clause = implode(' AND ', $where);

$sql = "
    SELECT 
        s.id,
        s.student_id,
        s.first_name,
        s.last_name,
        s.roll_number,
        s.session,
        COALESCE(s.group_name,'') AS group_name,
        s.gender,
        s.date_of_birth,
        s.email,
        s.phone,
        s.guardian_name,
        s.guardian_phone,
        s.address,
        c.name AS class_name,
        c.section AS class_section
    FROM students s
    LEFT JOIN classes c ON s.class_id = c.id
    WHERE $where_clause
    ORDER BY CAST(s.roll_number AS UNSIGNED) ASC, s.first_name ASC, s.last_name ASC
";

$stmt = $pdo->prepare($sql);
foreach ($params as $k => $v) { $stmt->bindValue($k, $v); }
$stmt->execute();
$rows = $stmt->fetchAll();

// Prepare CSV download
$filename_suffix = $class_id > 0 ? ("class_".$class_id) : "all";
$filename = "students_".$filename_suffix."_".date('Ymd_His').".csv";

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="'.$filename.'"');
// BOM for Excel (UTF-8)
echo "\xEF\xBB\xBF";

$out = fopen('php://output', 'w');

// Header
fputcsv($out, [
    'Student ID', 'Roll', 'First Name', 'Last Name', 'Class', 'Section', 'Session', 'Group', 'Gender', 'DOB', 'Phone', 'Email', 'Guardian Name', 'Guardian Phone', 'Address'
]);

foreach ($rows as $r) {
    fputcsv($out, [
        $r['student_id'],
        $r['roll_number'],
        $r['first_name'],
        $r['last_name'],
        $r['class_name'],
        $r['class_section'],
        $r['session'],
        $r['group_name'],
        $r['gender'],
        $r['date_of_birth'],
        $r['phone'],
        $r['email'],
        $r['guardian_name'],
        $r['guardian_phone'],
        $r['address'],
    ]);
}

fclose($out);
exit();