<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Upload Students from CSV</title>
	    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 20px;
        }

        form {
            background-color: #ffffff;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            max-width: 600px;
            margin: auto;
        }

        label {
            display: block;
            margin: 10px 0 5px;
        }

        input[type="text"], input[type="file"], input[type="submit"], select {
            width: 100%;
            padding: 10px;
            margin: 5px 0 15px;
            border: 1px solid #cccccc;
            border-radius: 4px;
        }

        input[type="submit"] {
            background-color: #007BFF;
            color: white;
            border: none;
            cursor: pointer;
        }

        input[type="submit"]:hover {
            background-color: #0056b3;
        }
    </style>
</head>
<body>

<h2>Upload Students CSV</h2>

<!-- Dropdown to select level and CSV file upload form -->
<form id="studentForm" enctype="multipart/form-data" method="POST">
    <label for="level">Select Level:</label>
    <select name="level" id="level" required>
        <option value="">--Select Level--</option>
        <option value="NDI">NDI</option>
        <option value="NDII">NDII</option>
        <option value="HNDI">HNDI</option>
        <option value="HNDII">HNDII</option>
    </select><br><br>

    <!-- File input for CSV upload -->
    <label for="file">Upload CSV File:</label>
    <input type="file" name="file" id="file" accept=".csv" required /><br><br>

    <input type="submit" name="submit">Upload Students</button>
</form>

<?php
// Database connection
include 'connection/config.php';
$pdo = new PDO('mysql:host=localhost;dbname=cbt', 'root', '');

if (isset($_POST['submit'])) {
    // Ensure a file is uploaded and the level is selected
    if (!empty($_FILES['file']['tmp_name']) && !empty($_POST['level'])) {
        $level = $_POST['level'];
        $file = $_FILES['file']['tmp_name'];

        // Open the CSV file
        $handle = fopen($file, "r");

        // Prepare statements for checking existing matric and inserting
        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM students WHERE matric = ?");
        $insertStmt = $pdo->prepare("INSERT INTO students (matric, fname, level, dept) VALUES (?, ?, ?, ?)");

        // Track duplicates and successful inserts
        $duplicates = [];
        $inserted = [];

        // Read CSV file row by row
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $matric = $data[0]; // First column in CSV (Matric Number)
            $fname = $data[1];  // Second column in CSV (First Name)
            $dept = $data[2];   // Third column in CSV (Department)

            // Check if the matric number already exists
            $checkStmt->execute([$matric]);
            $count = $checkStmt->fetchColumn();

            if ($count == 0) {
                // Matric number doesn't exist, insert the student
                $insertStmt->execute([$matric, $fname, $level, $dept]);
                $inserted[] = $matric; // Keep track of inserted matric numbers
            } else {
                // Matric number exists, mark as duplicate
                $duplicates[] = $matric; // Keep track of duplicate matric numbers
            }
        }

        fclose($handle);

        // Display results
        if (!empty($inserted)) {
            echo "Students successfully uploaded for matric numbers: " . implode(", ", $inserted) . "<br>";
					/////Update time ////
			
			// Database connection (Assuming you have a PDO connection stored in $pdo)
include('connection/config.php'); // Include your database connection file

// Set the $level variable dynamically (you can get it from a form or session)
//$level = $_POST['level']; // Assuming you're getting level from a form submission

// SQL query to update the `duration` column to 30 for all students at the specified level
$updateSQL = "UPDATE students SET duration = 30 WHERE level = :level";

// Prepare the statement
$stmttime = $pdo->prepare($updateSQL);

// Bind the level parameter to avoid SQL injection
$stmttime->bindParam(':level', $level, PDO::PARAM_STR); // Use PDO::PARAM_STR if level is a string, PDO::PARAM_INT if it's an integer
$stmttime->execute();
// Execute the update statement
        }

        if (!empty($duplicates)) {
            echo "Duplicate entries found for matric numbers: " . implode(", ", $duplicates) . "<br>";
        }
    } else {
        echo "Please select a level and upload a CSV file.";
    }
}
?>

</body>
</html>
