 <?php include "db.php"; ?>

<?php
session_start();

if (!isset($_SESSION["user_id"])) {
    header("Location: index.php");
    exit();
}

try {
    $db = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

if (isset($_GET['download']) && $_GET['download'] === 'data_csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="students_data.csv"');

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

    $header = [
        'student_id',
        'student_name',
        'course_name',
        'mobile_number',
        'email_id',
        'current_address',
        'permanent_address',
        'parents_contact_number',
        'aadhaar_number',
        'date_of_joining',
        'father_name',
        'mother_name',
        'date_of_birth'
    ];
    fputcsv($output, $header);

    $query = "SELECT student_id, student_name, course_name, mobile_number, email_id, current_address, permanent_address, parents_contact_number, aadhaar_number, date_of_joining, father_name, mother_name, date_of_birth FROM students";
    $stmt = $db->prepare($query);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit();
}

$query = "SELECT s.*, 
          (SELECT COUNT(*) FROM certificates c WHERE c.student_id = s.student_id) AS cert_count,
          (SELECT GROUP_CONCAT(c.certificate_id) FROM certificates c WHERE c.student_id = s.student_id) AS certificate_ids
          FROM students s";
$stmt = $db->prepare($query);
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
    <title>All Students</title>
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
    <?php include "nav.php"; ?>
    <div class="container mt-5">
        <h2>All Students</h2>

        <?php if (isset($_GET['success'])): ?>
            <div class="alert alert-success" role="alert">
                <?php echo htmlspecialchars($_GET['success']); ?>
            </div>
        <?php endif; ?>
        <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger" role="alert">
                <?php echo htmlspecialchars($_GET['error']); ?>
            </div>
        <?php endif; ?>

        <div class="mb-3">
            <a href="all-students.php?download=data_csv" class="btn btn-success">Download Data CSV</a>
            <a href="register.php?download=blank_csv" class="btn btn-info">Download Blank CSV Template</a>
            <a href="delete-all-students.php" class="btn btn-danger float-right" onclick="return confirm('Are you sure you want to delete ALL students? This action cannot be undone.')">Delete All Students</a>
        </div>

        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Student ID</th>
                    <th>Student Name</th>
                    <th>Course Name</th>
                    <th>Mobile Number</th>
                    <th>Email ID</th>
                    <th>Joining Date</th>
                    <th>Actions</th> 
                </tr>
            </thead>
            <tbody>
                <?php foreach ($students as $student): ?>
                    <tr>
                        <td><?php echo $student["student_id"]; ?></td>
                        <td><?php echo $student["student_name"]; ?></td>
                        <td><?php echo $student["course_name"]; ?></td>
                        <td><?php echo $student["mobile_number"]; ?></td>
                        <td><?php echo $student["email_id"]; ?></td>
                        <td><?php echo $student["date_of_joining"]; ?></td>
                        <td>
                            <a href="edit-student.php?id=<?php echo $student["student_id"]; ?>" class="btn btn-primary btn-sm">Edit</a>
                            <a href="delete-student.php?id=<?php echo $student["student_id"]; ?>" class="btn btn-danger btn-sm" onclick="return confirm('Are you sure you want to delete this student?')">Delete</a>
                            <?php if ($student["cert_count"] > 0): ?>
                                <span class="badge badge-success ml-2">Certificate is Generated</span>
                                <?php
                                    $cert_ids = explode(',', $student['certificate_ids']);
                                    foreach ($cert_ids as $cert_id) {
                                        if (!empty($cert_id)) {
echo '<a href="delete-certificate.php?certificate_id=' . htmlspecialchars($cert_id) . '" class="btn btn-warning btn-sm ml-2" style="background-color: #ffc107; border-color: #ffc107; color: #212529;" onclick="return confirm(\'Are you sure you want to delete this certificate?\')">Delete Certificate</a>';
                                        }
                                    }
                                ?>
                            <?php elseif ($student["isCertIssued"] == 0): ?>
                                <span class="badge badge-warning ml-2">Certificate Yet to be Transferred to Approval</span>
                                <a href="disapprove-certificate.php?student_id=<?php echo $student["student_id"]; ?>" class="btn btn-danger btn-sm ml-2">Disapprove Certificate</a>
                            <?php else: ?>
                                <span class="badge badge-danger ml-2">Certificate Not Generated</span>
                                <a href="approve-certificate.php?student_id=<?php echo $student["student_id"]; ?>" class="btn btn-success btn-sm ml-2">Approve Certificate</a>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <!-- Bootstrap JS -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>
