<?php
// 1. Database connection settings
$host    = 'localhost';
$db      = 'your_database_name';
$user    = 'your_username';
$pass    = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Initialize variables
$students = null;
$searchDepartment = '';
$searchLevel = '';

// 2. Handle Form Submission (Fetch Data)
if ($_SERVER["REQUEST_METHOD"] === "POST" && !isset($_POST['export_csv'])) {
    $searchDepartment = filter_input(INPUT_POST, 'department', FILTER_SANITIZE_SPECIAL_CHARS);
    $searchLevel      = filter_input(INPUT_POST, 'level', FILTER_SANITIZE_SPECIAL_CHARS);

    $sql = "SELECT id, fullname, matricnumber, department, level, password 
            FROM students 
            WHERE department = :department AND level = :level";
            
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['department' => $searchDepartment, 'level' => $searchLevel]);
    $students = $stmt->fetchAll();
}

// 3. Handle CSV Export Action
if ($_SERVER["REQUEST_METHOD"] === "POST" && isset($_POST['export_csv'])) {
    $searchDepartment = filter_input(INPUT_POST, 'department', FILTER_SANITIZE_SPECIAL_CHARS);
    $searchLevel      = filter_input(INPUT_POST, 'level', FILTER_SANITIZE_SPECIAL_CHARS);

    $sql = "SELECT id, fullname, matricnumber, department, level, password 
            FROM students 
            WHERE department = :department AND level = :level";
            
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['department' => $searchDepartment, 'level' => $searchLevel]);
    $exportData = $stmt->fetchAll();

    if ($exportData) {
        // Clean filename formatting
        $filename = "students_" . strtolower(str_replace(' ', '_', $searchDepartment)) . "_level" . $searchLevel . "_" . date('Y-m-d') . ".csv";
        
        // Force file download headers
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        // Output CSV headers
        fputcsv($output, ['ID', 'Full Name', 'Matric Number', 'Department', 'Level', 'Password']);
        
        // Output Data Rows
        foreach ($exportData as $row) {
            fputcsv($output, $row);
        }
        
        fclose($output);
        exit();
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Records Portal</title>
    <style>
        /* Modern Minimalist CSS Reset & Styling */
        :root {
            --primary-color: #2563eb;
            --primary-hover: #1d4ed8;
            --success-color: #16a34a;
            --success-hover: #15803d;
            --bg-color: #f8fafc;
            --card-bg: #ffffff;
            --text-color: #1e293b;
            --border-color: #e2e8f0;
        }

        body {
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            background-color: var(--bg-color);
            color: var(--text-color);
            margin: 0;
            padding: 20px;
            display: flex;
            flex-direction: column;
            align-items: center;
        }

        .container {
            width: 100%;
            max-width: 1000px;
            background: var(--card-bg);
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 4px 6px -1px rgb(0 0 0 / 0.1), 0 2px 4px -2px rgb(0 0 0 / 0.1);
            margin-bottom: 20px;
        }

        h2, h3 {
            margin-top: 0;
            color: #0f172a;
        }

        .form-group {
            display: inline-block;
            margin-right: 15px;
            margin-bottom: 15px;
        }

        label {
            display: block;
            font-weight: 600;
            margin-bottom: 6px;
            font-size: 14px;
        }

        select {
            padding: 10px 16px;
            border: 1px solid var(--border-color);
            border-radius: 6px;
            background-color: #fff;
            min-width: 220px;
            font-size: 14px;
            outline: none;
            transition: border 0.2s;
        }

        select:focus {
            border-color: var(--primary-color);
        }

        button {
            padding: 10px 20px;
            border: none;
            border-radius: 6px;
            font-size: 14px;
            font-weight: 500;
            cursor: pointer;
            transition: background 0.2s;
        }

        .btn-primary {
            background-color: var(--primary-color);
            color: white;
        }

        .btn-primary:hover {
            background-color: var(--primary-hover);
        }

        .btn-success {
            background-color: var(--success-color);
            color: white;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            margin-bottom: 15px;
        }

        .btn-success:hover {
            background-color: var(--success-hover);
        }

        /* Responsive Table Container Wrapper */
        .table-responsive {
            width: 100%;
            overflow-x: auto;
            border: 1px solid var(--border-color);
            border-radius: 8px;
            margin-top: 15px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            text-align: left;
            font-size: 14px;
        }

        th {
            background-color: #f1f5f9;
            color: #475569;
            font-weight: 600;
            padding: 14px 16px;
            border-bottom: 2px solid var(--border-color);
        }

        td {
            padding: 14px 16px;
            border-bottom: 1px solid var(--border-color);
        }

        tr:last-child td {
            border-bottom: none;
        }

        tr:hover {
            background-color: #f8fafc;
        }

        .alert-error {
            color: #b91c1c;
            background-color: #fef2f2;
            padding: 12px;
            border-radius: 6px;
            border: 1px solid #fee2e2;
        }
    </style>
</head>
<body>

    <div class="container">
        <h2>Student Records Portal</h2>
        
        <form action="" method="POST">
            <div class="form-group">
                <label for="department">Department</label>
                <select name="department" id="department" required>
                    <option value="">-- Select Department --</option>
                    <option value="Computer Science" <?php echo ($searchDepartment == 'Computer Science') ? 'selected' : ''; ?>>Computer Science</option>
                    <option value="Mechanical Engineering" <?php echo ($searchDepartment == 'Mechanical Engineering') ? 'selected' : ''; ?>>Mechanical Engineering</option>
                    <option value="Accounting" <?php echo ($searchDepartment == 'Accounting') ? 'selected' : ''; ?>>Accounting</option>
                </select>
            </div>

            <div class="form-group">
                <label for="level">Level</label>
                <select name="level" id="level" required>
                    <option value="">-- Select Level --</option>
                    <option value="100" <?php echo ($searchLevel == '100') ? 'selected' : ''; ?>>100 Level</option>
                    <option value="200" <?php echo ($searchLevel == '200') ? 'selected' : ''; ?>>200 Level</option>
                    <option value="300" <?php echo ($searchLevel == '300') ? 'selected' : ''; ?>>300 Level</option>
                    <option value="400" <?php echo ($searchLevel == '400') ? 'selected' : ''; ?>>400 Level</option>
                </select>
            </div>

            <button type="submit" class="btn-primary">Filter Results</button>
        </form>
    </div>

    <!-- Results Section -->
    <?php if ($_SERVER["REQUEST_METHOD"] === "POST" && !isset($_POST['export_csv'])): ?>
        <div class="container">
            <h3>Search Results</h3>
            <p>Showing records for: <strong><?php echo htmlspecialchars($searchDepartment); ?></strong> | Level: <strong><?php echo htmlspecialchars($searchLevel); ?></strong></p>
            
            <?php if ($students): ?>
                <!-- Export to CSV Form Button -->
                <form action="" method="POST" style="display: inline;">
                    <input type="hidden" name="department" value="<?php echo htmlspecialchars($searchDepartment); ?>">
                    <input type="hidden" name="level" value="<?php echo htmlspecialchars($searchLevel); ?>">
                    <button type="submit" name="export_csv" class="btn-success">📊 Export to CSV / Excel</button>
                </form>

                <!-- Responsive Table Wrapper -->
                <div class="table-responsive">
                    <table>
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Full Name</th>
                                <th>Matric Number</th>
                                <th>Department</th>
                                <th>Level</th>
                                <th>Password</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($students as $student): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($student['id']); ?></td>
