<?php
require_once __DIR__ . '/SimpleXLSX.php';
use Shuchkin\SimpleXLSX;

$host = "localhost";
$user = "root";
$pass = "";
$db   = "balagbag";

$conn = new mysqli($host, $user, $pass, $db);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$message = "";

if (isset($_POST['submit'])) {

    if ($xlsx = SimpleXLSX::parse($_FILES['excel']['tmp_name'])) {

        foreach ($xlsx->rows() as $key => $row) {

            if ($key == 0) continue;

            $LRN         = $conn->real_escape_string($row[0]);
            $LN  = $conn->real_escape_string($row[1]);
            $FN   = $conn->real_escape_string($row[2]);
            $MD = $conn->real_escape_string($row[3]);
            $BIRTH_DATE        = $conn->real_escape_string($row[4]);
            $OC      = $conn->real_escape_string($row[5]);
            $OC1       = $conn->real_escape_string($row[6]);
            $OC_AVE       = $conn->real_escape_string($row[7]);
            $OC_RE      = $conn->real_escape_string($row[8]);
            $KOM       = $conn->real_escape_string($row[9]);
            $KOM1       = $conn->real_escape_string($row[10]);
            $KOM_AVE       = $conn->real_escape_string($row[11]);
            $KOM_RE       = $conn->real_escape_string($row[12]);
            $ST       = $conn->real_escape_string($row[13]);
            $ST1       = $conn->real_escape_string($row[14]);
            $ST_AVE       = $conn->real_escape_string($row[15]);
            $ST_RE       = $conn->real_escape_string($row[16]);
            $GM       = $conn->real_escape_string($row[17]);
            $GM1       = $conn->real_escape_string($row[18]);
            $GM_AVE       = $conn->real_escape_string($row[19]);
            $GM_RE       = $conn->real_escape_string($row[20]);
            $ELS       = $conn->real_escape_string($row[21]);
            $ELS1       = $conn->real_escape_string($row[22]);
            $ELS_AVE      = $conn->real_escape_string($row[23]);
            $ELS_RE       = $conn->real_escape_string($row[24]);
            $PE1       = $conn->real_escape_string($row[25]);
            $PE11       = $conn->real_escape_string($row[26]);
            $PE_AVE       = $conn->real_escape_string($row[27]);
            $PE_RE       = $conn->real_escape_string($row[28]);
            $INTRO       = $conn->real_escape_string($row[29]);
            $INTRO1       = $conn->real_escape_string($row[30]);
            $INTRO_AVE       = $conn->real_escape_string($row[31]);
            $INTRO_RE      = $conn->real_escape_string($row[32]);
            $PR       = $conn->real_escape_string($row[33]);
            $PR1      = $conn->real_escape_string($row[34]);
            $PR_AVE      = $conn->real_escape_string($row[35]);
            $PR_RE       = $conn->real_escape_string($row[36]);
            $COM      = $conn->real_escape_string($row[37]);
            $COM1       = $conn->real_escape_string($row[38]);
            $COM_AVE       = $conn->real_escape_string($row[39]);
            $COM_RE       = $conn->real_escape_string($row[40]);
            $SECTION       = $conn->real_escape_string($row[41]);
            $GRADE       = $conn->real_escape_string($row[42]);


            $sql = "INSERT INTO student_grades (
    LRN, LN, FN, MD, BIRTH_DATE,
    OC, OC1, OC_AVE, OC_RE,
    KOM, KOM1, KOM_AVE, KOM_RE,
    21ST, 21ST1, 21ST_AVE, 21ST_RE,
    GM, GM1, GM_AVE, GM_RE,
    ELS, ELS1, ELS_AVE, ELS_RE,
    PE1, PE11, PE_AVE, PE_RE,
    INTRO, INTRO1, INTRO_AVE, INTRO_RE,
    PR, PR1, PR_AVE, PR_RE,
    COM, COM1, COM_AVE, COM_RE, SECTION, GRADE
) VALUES (
    '$LRN', '$LN', '$FN', '$MD', '$BIRTH_DATE',
    '$OC', '$OC1', '$OC_AVE', '$OC_RE',
    '$KOM', '$KOM1', '$KOM_AVE', '$KOM_RE',
    '$ST', '$ST1', '$ST_AVE', '$ST_RE',
    '$GM', '$GM1', '$GM_AVE', '$GM_RE',
    '$ELS', '$ELS1', '$ELS_AVE', '$ELS_RE',
    '$PE1', '$PE11', '$PE_AVE', '$PE_RE',
    '$INTRO', '$INTRO1', '$INTRO_AVE', '$INTRO_RE',
    '$PR', '$PR1', '$PR_AVE', '$PR_RE',
    '$COM', '$COM1', '$COM_AVE', '$COM_RE', '$SECTION', '$GRADE'
)";

            $conn->query($sql);
        }

        $message = "Excel Imported Successfully!";
    } else {
        $message = SimpleXLSX::parseError();
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Babag SHS - Upload Students</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body class="bg-light">

<!-- NAVBAR -->
<nav class="navbar navbar-dark bg-primary shadow">
    <div class="container">
        <span class="navbar-brand mb-0 h1">
            <img src="assets/logo.png" width="40" class="me-2">
            Babag Senior High School
        </span>
    </div>
</nav>

<div class="container mt-5">

    <div class="card shadow-lg border-0 rounded-4">
        <div class="card-body p-5">

            <h3 class="text-center mb-4">Upload Student Excel File</h3>

            <?php if($message != ""): ?>
                <div class="alert alert-success text-center">
                    <?= $message ?>
                </div>
            <?php endif; ?>

            <form method="post" enctype="multipart/form-data">
                <div class="mb-3">
                    <label class="form-label">Select Excel File (.xlsx)</label>
                    <input type="file" name="excel" class="form-control" required>
                </div>

                <div class="d-grid">
                    <button type="submit" name="submit" class="btn btn-primary btn-lg rounded-3">
                        Upload & Save
                    </button>
                </div>
            </form>

            </div>

        </div>
    </div>

</div>

</body>
</html>