<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require 'vendor/autoload.php'; // PhpSpreadsheet autoload
use PhpOffice\PhpSpreadsheet\IOFactory;

$host = 'localhost';
$db   = 'school_checkin';
$user = 'root';
$pass = '';

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

$message = "";
if (isset($_FILES['file'])) {
    $file = $_FILES['file']['tmp_name'];
    if ($file) {
        try {
            $spreadsheet = IOFactory::load($file);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            for ($i = 1; $i < count($data); $i++) {
                $lrn      = trim($data[$i][0]);
                $name     = trim($data[$i][1]);
                $grade    = trim($data[$i][2]);
                $sex      = isset($data[$i][3]) ? trim($data[$i][3]) : null;
                $birthday = isset($data[$i][4]) ? trim($data[$i][4]) : null;
                $contact  = isset($data[$i][5]) ? trim($data[$i][5]) : null;

                if ($lrn && $name && $grade) {
                    $stmt = $pdo->prepare("SELECT id FROM students WHERE lrn = ?");
                    $stmt->execute([$lrn]);
                    if ($stmt->rowCount() > 0) {
                        // Update existing
                        $pdo->prepare("UPDATE students SET name = ?, grade = ?, sex = ?, birthday = ?, contact = ? WHERE lrn = ?")
                            ->execute([$name, $grade, $sex, $birthday, $contact, $lrn]);
                    } else {
                        // Insert new
                        $pdo->prepare("INSERT INTO students (lrn, name, grade, sex, birthday, contact) VALUES (?, ?, ?, ?, ?, ?)")
                            ->execute([$lrn, $name, $grade, $sex, $birthday, $contact]);
                    }
                }
            }

            $message = "✅ Students imported successfully!";
        } catch (Exception $e) {
            $message = "❌ Failed to read file: " . $e->getMessage();
        }
    } else {
        $message = "❌ File upload failed.";
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Import Students (Excel/CSV)</title>
    <style>
        body {
            font-family: Arial;
            text-align: center;
            padding: 40px;
            transition: background 0.3s, color 0.3s;
        }

        body.light {
            background: #f4f4f4;
            color: #000;
        }

        body.dark {
            background: #121212;
            color: #fff;
        }

        h2 {
            margin-bottom: 20px;
        }

        .btn {
            padding: 10px 20px;
            background: #007bff;
            color: white;
            border: none;
            border-radius: 5px;
            cursor: pointer;
        }

        .btn:hover {
            background: #0056b3;
        }

        .msg {
            margin: 15px;
            font-weight: bold;
        }

        input[type="file"] {
            padding: 10px;
            margin-top: 10px;
        }

        body.dark input[type="file"] {
            background-color: #333;
            color: white;
            border: 1px solid #555;
        }

        body.light input[type="file"] {
            background-color: #fff;
            color: black;
            border: 1px solid #ccc;
        }

        a {
            color: inherit;
            text-decoration: none;
            display: inline-block;
            margin-top: 20px;
        }
    </style>
</head>
<body class="light">

<h2>📥 Import Students (CSV / Excel)</h2>

<?php if ($message): ?>
    <div class="msg"><?= htmlspecialchars($message) ?></div>
<?php endif; ?>

<form method="post" enctype="multipart/form-data">
    <input type="file" name="file" accept=".csv, .xlsx, .xls" required><br><br>
    <button class="btn" type="submit">Upload</button>
</form>

<br><a href="dashboard.php">← Back to Dashboard</a>
<script>
    const theme = localStorage.getItem('theme');
    const body = document.body;
    if (theme === 'dark') {
        body.classList.remove('light');
        body.classList.add('dark');
    } else {
        body.classList.remove('dark');
        body.classList.add('light');
    }
</script>
</body>
</html>
