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

require_once 'vendor/autoload.php'; // PhpSpreadsheet
require_once 'phpqrcode/qrlib.php';
require_once 'pdo_config.php';

use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$base_url = "http://159.223.47.179/student_checkin/checkin_auto.php";
$logo = "school_logo.png";

if (!file_exists("qr_codes")) {
    mkdir("qr_codes", 0777, true);
}

if (isset($_POST['upload_sf1']) && isset($_FILES['sf1'])) {
    $file = $_FILES['sf1']['tmp_name'];
    $ext = pathinfo($_FILES['sf1']['name'], PATHINFO_EXTENSION);

    // Detect Excel file type
    $reader = strtolower($ext) === 'xls' ? new Xls() : new Xlsx();
    $spreadsheet = $reader->load($file);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();

    echo "<!DOCTYPE html>
    <html>
    <head><title>SDAESI QRCODE</title></head>
    <body style='background:#121212; color:white; font-family:sans-serif; padding:20px;'>";

    $count = 0;
    $grade_used = '';

    foreach ($rows as $i => $row) {
        if ($i === 0) continue; // skip header row

        // Skip empty or invalid rows
        if (empty($row[0]) || empty($row[1]) || empty($row[2])) continue;

        // Get data from columns
        $lrn     = trim($row[0]);
        $last    = trim($row[1]);
        $first   = trim($row[2]);
        $middle  = isset($row[3]) ? trim($row[3]) : '';
        $grade   = isset($row[4]) ? trim($row[4]) : '0';
        $sex     = isset($row[5]) ? trim($row[5]) : '';
        $birthday= isset($row[6]) ? trim($row[6]) : '';
        $contact = isset($row[7]) ? trim($row[7]) : '';

        $name = "$first $middle $last";
        $grade_display = ($grade === 'Nursery' || $grade === 'Kindergarten') ? $grade : "Grade $grade";
        $grade_used = $grade;

        // Build QR content
        $qr_url = "$base_url?lrn=" . urlencode($lrn) .
                  "&name=" . urlencode($name) .
                  "&grade=" . urlencode($grade_display) .
                  "&number=" . urlencode($contact);

        // Build QR filenames
        $filename_safe = strtolower(str_replace(' ', '_', $name));
        $filename = "qr_codes/qr_" . $lrn . "_" . $filename_safe . ".png";
        $temp_qr = "qr_codes/temp_" . $lrn . "_" . $filename_safe . ".png";

        // Generate QR with logo
        QRcode::png($qr_url, $temp_qr, QR_ECLEVEL_H, 6);
        $QR = imagecreatefrompng($temp_qr);
        $logo_img = imagecreatefrompng($logo);

        $qr_width = imagesx($QR);
        $logo_width = imagesx($logo_img);
        $logo_qr_width = $qr_width / 4;
        $scale = $logo_width / $logo_qr_width;
        $logo_qr_height = imagesy($logo_img) / $scale;
        $from_width = ($qr_width - $logo_qr_width) / 2;

        imagecopyresampled($QR, $logo_img, $from_width, $from_width, 0, 0,
            $logo_qr_width, $logo_qr_height, $logo_width, imagesy($logo_img));

        imagepng($QR, $filename);
        unlink($temp_qr);

        // Save to DB
        $stmt = $pdo->prepare("INSERT INTO students (lrn, name, grade, sex, birthday, contact)
            VALUES (?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE 
                name=VALUES(name), grade=VALUES(grade), sex=VALUES(sex), 
                birthday=VALUES(birthday), contact=VALUES(contact)");
        $stmt->execute([$lrn, $name, $grade, $sex, $birthday, $contact]);

        // Display preview
        echo "<div style='margin-bottom:20px;'>
                <strong>✅ QR created for: $name</strong><br>
                <img src='$filename' width='150'><br>
                <a href='$filename' download style='color:#00c0ff;'>📥 Download</a>
              </div>";

        $count++;
    }

    // ZIP creation
    $zip = new ZipArchive();
    $grade_label = ($grade_used === 'Nursery' || $grade_used === 'Kindergarten') ? $grade_used : "Grade_" . $grade_used;
    $zip_filename = $grade_label . "_SF1_QRCodes.zip";

    if ($zip->open($zip_filename, ZipArchive::CREATE) === TRUE) {
        foreach ($rows as $i => $row) {
            if ($i === 0 || empty($row[0]) || empty($row[1]) || empty($row[2])) continue;

            $lrn = trim($row[0]);
            $last = trim($row[1]);
            $first = trim($row[2]);
            $middle = isset($row[3]) ? trim($row[3]) : '';
            $name = "$first $middle $last";
            $filename_safe = strtolower(str_replace(' ', '_', $name));
            $filepath = "qr_codes/qr_" . $lrn . "_" . $filename_safe . ".png";

            if (file_exists($filepath)) {
                $zip->addFile($filepath, basename($filepath));
            }
        }
        $zip->close();

        echo "<div style='margin-top:30px;'>
                <strong>📦 ZIP file created:</strong><br>
                <a href='$zip_filename' style='color:#00c0ff;'>⬇️ Download All QR Codes (ZIP)</a><br><br>
                👥 <strong>Total students processed: $count</strong>
              </div>";
    }

    echo "<br><br><a href='upload_sf1.html' style='color:#aaa;'>← Upload another SF1</a>";
    echo "</body></html>";
}
?>
