<?php
// download_csv.php
require_once 'config/database.php';

// Get filter parameters from GET request
$session = isset($_GET['session']) ? trim($_GET['session']) : '';
$class_name = isset($_GET['class_name']) ? trim($_GET['class_name']) : '';
$group_name = isset($_GET['group_name']) ? trim($_GET['group_name']) : '';

// Build query with filters
$sql = "SELECT 
            s.first_name,
            s.last_name,
            s.father_name,
            s.mother_name,
            s.email,
            s.phone,
            s.address,
            s.date_of_birth,
            s.gender,
            c.name as class_name,
            s.session,
            s.group_name,
            s.roll_number,
            s.admission_date,
            s.guardian_name,
            s.guardian_phone,
            s.guardian_email,
            s.guardian_address,
            GROUP_CONCAT(sub.code ORDER BY sub.code SEPARATOR ',') as subject_codes
        FROM students s
        LEFT JOIN classes c ON s.class_id = c.id
        LEFT JOIN student_subjects ss ON s.id = ss.student_id
        LEFT JOIN subjects sub ON ss.subject_id = sub.id
        WHERE 1=1";

$params = [];

if (!empty($session)) {
    $sql .= " AND s.session = ?";
    $params[] = $session;
}

if (!empty($class_name)) {
    $sql .= " AND c.name = ?";
    $params[] = $class_name;
}

if (!empty($group_name)) {
    $sql .= " AND s.group_name = ?";
    $params[] = $group_name;
}

$sql .= " GROUP BY s.id ORDER BY s.roll_number";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    die("Database error: " . $e->getMessage());
}

// Set header for CSV download
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="student_data.csv"');

// Create output stream
$fp = fopen('php://output', 'w');

// Write header row
$fields = [
    'first_name',
    'last_name',
    'father_name',
    'mother_name',
    'email',
    'phone',
    'address',
    'date_of_birth',
    'gender',
    'class_name',
    'session',
    'group_name',
    'roll_number',
    'admission_date',
    'guardian_name',
    'guardian_phone',
    'guardian_email',
    'guardian_address',
    'subject_codes'
];

fputcsv($fp, $fields);

// Write data rows
foreach ($students as $student) {
    $row = [
        $student['first_name'],
        $student['last_name'],
        $student['father_name'],
        $student['mother_name'],
        $student['email'],
        $student['phone'],
        $student['address'],
        $student['date_of_birth'],
        $student['gender'],
        $student['class_name'],
        $student['session'],
        $student['group_name'],
        $student['roll_number'],
        $student['admission_date'],
        $student['guardian_name'],
        $student['guardian_phone'],
        $student['guardian_email'],
        $student['guardian_address'],
        $student['subject_codes'] ?? ''
    ];
    
    fputcsv($fp, $row);
}

fclose($fp);
?>