<?php
// Remove deprecated parameter warnings: Set error reporting to exclude deprecated warnings
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
// Exclude deprecated warnings
error_reporting(E_ALL & ~E_DEPRECATED);

// Check if required files exist before including
if (!file_exists('phpqrcode/qrlib.php')) die("Missing file: phpqrcode/qrlib.php");
if (!file_exists('pdo_config.php')) die("Missing file: pdo_config.php");
if (!file_exists('vendor/autoload.php')) die("Missing file: vendor/autoload.php");

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

session_start();

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

// Column mapping
$COLUMN = [
    'lrn'        => 0,
    'full_name'  => 1,
    'sex'        => 2,
    'birthday'   => 3,
    'language'   => 4,
    'religion'   => 5,
    'barangay'   => 6,
    'city'       => 7,
    'province'   => 8,
    'father'     => 9,
    'mother'     => 10,
    'guardian'   => 11,
    'contact'    => 12,
    'relationship'=> 13,
    'grade'      => 14,
    'sched'      => 15,
];
$skipHeaderRows = 1;
$preview_qr = null;

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    unset($_SESSION['students_list']);
    unset($_SESSION['preview']);
    unset($_SESSION['zips']);
}

if (!isset($_SESSION['students_list']) || !is_array($_SESSION['students_list'])) {
    $_SESSION['students_list'] = [];
}
$students_list = $_SESSION['students_list'];

$selected_grade = $_POST['grade_select'] ?? '';
$selected_session = $_POST['session_select'] ?? '';

$error_msgs = [];
$success_msgs = [];

if (isset($_GET['clear'])) {
    unset($_SESSION['students_list']);
    unset($_SESSION['preview']);
    unset($_SESSION['zips']);
    header("Location: generator.php");
    exit;
}

// THIS IS THE PART YOU SHOULD CHANGE FOR SMALL QR CODE
$desired_pixels = 150; // <-- GINAGMAY NA QR CODE (150x150 px)

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['excel_file'])) {
    $file = $_FILES['excel_file']['tmp_name'];
    if (!file_exists($file)) {
        $error_msgs[] = "Uploaded file not found!";
    } else {
        $spreadsheet = IOFactory::load($file);
        $worksheet = $spreadsheet->getActiveSheet();
        $rows = $worksheet->toArray();
        $data = array_slice($rows, $skipHeaderRows);

        $grade_display = $selected_grade ?: "Nursery";
        if ($grade_display === "Nursery" || $grade_display === "Kindergarten") {
            $session_display = $selected_session ?: "Morning";
            $grade_display_with_session = $grade_display;
        } else {
            $session_display = "";
            $grade_display_with_session = $grade_display;
        }
        $safe_grade_folder = preg_replace('/[^A-Za-z0-9_]/', '_', $grade_display_with_session);

        $folder = "qr_codes/$safe_grade_folder";
        if (!file_exists($folder)) mkdir($folder, 0777, true);

        foreach (glob($folder . '/*.png') as $f) {
            if (is_file($f)) unlink($f);
        }

        $batch_students = [];
        $all_qr_files = [];
        $blank_lrn_count = 0;
        foreach ($data as $row) {
            $lrn = trim((string)($row[$COLUMN['lrn']] ?? ''));
            $lrn = preg_replace('/[^A-Za-z0-9]/', '', $lrn);
            if (strtolower($lrn) === 'na' || strtolower($lrn) === 'n/a' || empty($lrn)) {
                $blank_lrn_count++;
            }
        }

        // TEMP LRN reservation
        $temp_lrns = [];
        $temp_lrn_index = 0;
        if ($blank_lrn_count > 0) {
            $stmt = $pdo->query("SELECT lrn FROM students WHERE lrn LIKE 'TEMP%' ORDER BY CAST(SUBSTRING(lrn,5) AS UNSIGNED) DESC LIMIT 1");
            $last_temp_lrn = $stmt->fetchColumn();
            $temp_start = $last_temp_lrn ? intval(substr($last_temp_lrn, 4)) + 1 : 1;
            for ($i = 0; $i < $blank_lrn_count; $i++) {
                $temp_lrns[] = 'TEMP' . str_pad($temp_start + $i, 4, '0', STR_PAD_LEFT);
            }
        }

        $pdo->beginTransaction();
        try {
            foreach ($data as $row_idx => $row) {
                $row = array_pad($row, max($COLUMN) + 1, '');
                foreach ($COLUMN as $key => $idx) {
                    $row[$idx] = trim((string)($row[$idx] ?? ''));
                }

                // Clean LRN
                $lrn = preg_replace('/[^A-Za-z0-9]/', '', $row[$COLUMN['lrn']]);
                if (strtolower($lrn) === 'na' || strtolower($lrn) === 'n/a' || empty($lrn)) {
                    $lrn = $temp_lrns[$temp_lrn_index++] ?? 'TEMPX' . mt_rand(1000, 9999);
                }

                $fullNameRaw = $row[$COLUMN['full_name']];
                $contact = preg_replace('/[^0-9\-\+]/', '', $row[$COLUMN['contact']]);
                $grade = $row[$COLUMN['grade']];

                // Name parsing
                $first = $middle = $last = '';
                if (strpos($fullNameRaw, ',') !== false) {
                    list($last, $remainder) = explode(',', $fullNameRaw, 2);
                    $last = trim($last);
                    $parts = preg_split('/\s+/', trim($remainder));
                    if (count($parts) > 1) {
                        $middle = array_pop($parts);
                        $first = implode(' ', $parts);
                    } else {
                        $first = $parts[0];
                        $middle = '';
                    }
                } else {
                    $parts = preg_split('/\s+/', $fullNameRaw);
                    if (count($parts) == 1) $first = $parts[0];
                    elseif (count($parts) == 2) { $first = $parts[0]; $last = $parts[1]; }
                    else { $first = $parts[0]; $last = array_pop($parts); $middle = implode(' ', array_slice($parts, 1, -1)); }
                }
                if (empty($first) && empty($last)) $first = 'NONAME';

                $clean_first = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $first));
                $clean_middle = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $middle));
                $clean_last = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $last));
                $filename_parts = array_filter([$clean_last, $clean_first, $clean_middle]);
                $safe_name = empty($filename_parts) ? 'NONAME_' . substr($lrn, 4) : implode('_', $filename_parts);

                $barangay = $row[$COLUMN['barangay']];
                $city = $row[$COLUMN['city']];
                $province = $row[$COLUMN['province']];
                $address = trim("$barangay, $city, $province");

                $sex = $row[$COLUMN['sex']];
                $birthday = $row[$COLUMN['birthday']];
                $language = $row[$COLUMN['language']];
                $religion = $row[$COLUMN['religion']];
                $father = $row[$COLUMN['father']];
                $mother = $row[$COLUMN['mother']];
                $guardian = $row[$COLUMN['guardian']];
                $relationship = $row[$COLUMN['relationship']];
                $grade_im = $row[$COLUMN['grade']];
                $sched =  $row[$COLUMN['sched']];

                // Birthday cleaning
                if ($birthday === '' || $birthday === '0000-00-00' || $birthday === '00' || $birthday === '000' || $birthday === '0') {
                    $birthday = null;
                } elseif (is_numeric($birthday)) {
                    $birthday_dt = Date::excelToDateTimeObject($birthday);
                    $birthday = $birthday_dt ? $birthday_dt->format('Y-m-d') : null;
                    if ($birthday === '0000-00-00') $birthday = null;
                } else {
                    $formats = ['m-d-Y', 'm/d/Y', 'Y-m-d', 'Y/m/d', 'd-m-Y', 'd/m/Y'];
                    $birthday_dt = false;
                    foreach ($formats as $fmt) {
                        $birthday_dt = DateTime::createFromFormat($fmt, $birthday);
                        if ($birthday_dt !== false) break;
                    }
                    $birthday = $birthday_dt !== false ? $birthday_dt->format('Y-m-d') : null;
                }
                $grade_display_qr = $grade_display_with_session;

                $base_url = "http://159.223.47.179/student_checkin/checkin_auto.php";
                $qr_url = "$base_url?lrn=" . urlencode($lrn)
                    . "&name=" . str_replace(' ', '+', trim("$last $first $middle"))
                    . "&grade=" . urlencode($grade_display_qr)
                    . "&number=" . urlencode($contact);

                // --- QR code block with 150x150px (mas gamay) and white margin ---
                $qr_margin = 2; // Uniform, small margin

                QRcode::png($qr_url, "$folder/temp_$safe_name.png", QR_ECLEVEL_H, 6, $qr_margin);
                $QR = @imagecreatefrompng("$folder/temp_$safe_name.png");

                // Create a white background PNG
                $resizedQR = imagecreatetruecolor($desired_pixels, $desired_pixels);
                $white = imagecolorallocate($resizedQR, 255,255,255);
                imagefilledrectangle($resizedQR, 0, 0, $desired_pixels-1, $desired_pixels-1, $white);

                // Copy QR onto white, preserving white margin
                imagecopyresampled(
                    $resizedQR, $QR,
                    0, 0, 0, 0,
                    $desired_pixels, $desired_pixels,
                    imagesx($QR), imagesy($QR)
                );

                // Add logo (optional)
                $logo = 'school_logo.png';
                if (file_exists($logo)) {
                    $logo_img = @imagecreatefrompng($logo);
                    if ($logo_img) {
                        $qr_width = $desired_pixels;
                        $logo_width = imagesx($logo_img);
                        $logo_height = imagesy($logo_img);
                        $logo_qr_width = $qr_width / 4;
                        $scale = $logo_width / $logo_qr_width;
                        $logo_qr_height = $logo_height / $scale;
                        $from_width = ($qr_width - $logo_qr_width) / 2;
                        imagecopyresampled(
                            $resizedQR, $logo_img,
                            $from_width, $from_width,
                            0, 0,
                            $logo_qr_width, $logo_qr_height,
                            $logo_width, $logo_height
                        );
                        imagedestroy($logo_img);
                    }
                }

                $qr_file = "$folder/$safe_name.png";
                imagepng($resizedQR, $qr_file);
                imagedestroy($QR);
                imagedestroy($resizedQR);
                unlink("$folder/temp_$safe_name.png");

                if (!$preview_qr) $preview_qr = $qr_file;

                // Try to insert with retry logic
                $inserted = false;
                $retry_count = 0;
                $max_retries = 3;
                while (!$inserted && $retry_count < $max_retries) {
                    try {
                        $stmt = $pdo->prepare("INSERT INTO students (lrn, first_name, middle_name, last_name, sex, birthday, contact, grade, address, mother, father, municipality, religion, mothertongue, relationship, schedule)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                        $stmt->execute([
                            $lrn, $first, $middle, $last, $sex, $birthday, $contact, $grade_display_qr, $address,
                            $mother, $father, $city, $religion, $language, $relationship, $sched
                        ]);
                        $inserted = true;
                    } catch (PDOException $e) {
                        if ($e->getCode() == '23000' && $retry_count < $max_retries - 1) {
                            if (substr($lrn, 0, 4) == 'TEMP') {
                                $lrn = 'TEMP' . mt_rand(10000, 99999);
                            }
                            $retry_count++;
                        } else {
                            $error_msgs[] = "DB error for $fullNameRaw: " . $e->getMessage();
                            break;
                        }
                    }
                }

                $all_qr_files[] = $qr_file;

                $batch_students[] = [
                    'lrn' => $lrn,
                    'first_name' => $first,
                    'middle_name' => $middle,
                    'last_name' => $last,
                    'grade' => $grade_display_qr,
                    'sex' => $sex,
                    'birthday' => $birthday,
                    'contact' => $contact,
                ];
            }
            $pdo->commit();
        } catch (Exception $e) {
            $pdo->rollBack();
            $error_msgs[] = $e->getMessage();
        }

        $existing_lrns = array_column($students_list, 'lrn');
        foreach ($batch_students as $stud) {
            if (!in_array($stud['lrn'], $existing_lrns)) {
                $students_list[] = $stud;
            }
        }

        // ZIP filename logic
        if ($grade_display === "Nursery" || $grade_display === "Kindergarten") {
            // For Nursery/Kindergarten, add session to zip file name
            $zip_safe_grade = $safe_grade_folder . '_' . $session_display;
        } else {
            // For Grade 1-6, normal zip file name
            $zip_safe_grade = $safe_grade_folder;
        }
        $zipname = "qr_codes/{$zip_safe_grade}_QRCodes.zip";
        $zip = new ZipArchive;
        $zip_success = false;
        if ($zip->open($zipname, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
            foreach ($all_qr_files as $file) {
                if (file_exists($file)) {
                    $zip->addFile($file, basename($file));
                } else {
                    $error_msgs[] = "Missing QR file: $file";
                }
            }
            $zip->close();
            $zip_success = true;
            $_SESSION['zips'] = [[
                'display' => $grade_display . ($session_display ? " ($session_display)" : ""),
                'safe' => $zip_safe_grade
            ]];
        } else {
            $error_msgs[] = "Failed to create zip file";
        }

        $_SESSION['preview'] = $preview_qr;
        $_SESSION['students_list'] = $students_list;

        $success_msgs[] = "Students in Excel: <b>" . count($data) . "</b>";
        $success_msgs[] = "QR PNGs generated: <b>" . count($all_qr_files) . "</b>";
        $success_msgs[] = "Students in table: <b>" . count($students_list) . "</b>";
        if ($zip_success && file_exists($zipname)) {
            $zip_archive = new ZipArchive();
            if ($zip_archive->open($zipname) === TRUE) {
                $success_msgs[] = "Files in ZIP: <b>" . $zip_archive->numFiles . "</b>";
                $zip_archive->close();
            }
        }
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>SDAES QRCode Generator</title>
    <style>
        body { background: #181818; color: #fff; font-family: Arial, sans-serif; margin: 0; }
        .menu-bar { width: 100%; padding: 20px 0 0 0; text-align: center; }
        .menu-bar .btn { background: #007bff; color: #fff; padding: 10px 24px; border: none; border-radius: 7px; font-weight: bold; cursor: pointer; text-decoration: none; margin-bottom: 12px; margin-top: 0; }
        .menu-bar .btn:hover { background: #0056b3; }
        .container { background: #232323; padding: 40px 35px 35px 35px; border-radius: 18px; max-width: 420px; margin: 40px auto 0 auto; box-shadow: 0 4px 20px #0007; }
        h2 { font-size: 2.1em; margin: 18px 0 22px 0; font-weight: bold; letter-spacing: 1px; }
        label, select, input[type=file], button { display: block; width: 100%; margin-bottom: 16px; }
        select, input[type=file] { padding: 9px 7px; border-radius: 6px; border: none; font-size: 1.05em; }
        button[type=submit] { background: #007bff; color: #fff; font-weight: bold; font-size: 1.1em; border-radius: 6px; border: none; cursor: pointer; margin-top: 8px; }
        button[type=submit]:hover { background: #0056b3; }
        img { max-width: 250px; margin: 22px 0 18px 0; display: block; margin-left: auto; margin-right: auto; }
        a.download-link { display: block; margin: 18px auto 6px auto; color: #00c0ff; text-align: center; font-weight: bold; font-size: 1.1em; }
        table {
            margin: 28px auto 0 auto;
            border-collapse: collapse;
            width: 100%;
            background: #232323;
            table-layout: fixed;
        }
        th, td {
            border: 1px solid #444;
            padding: 7px 10px;
            font-size: 1em;
            text-align: center;
            word-break: break-word;
        }
        th {
            background: #007bff;
            color: #fff;
        }
        .logo-center {
            display: block;
            margin: 0 auto 18px auto;
            width: 80px;
        }
        .form-title { margin-bottom: 25px; font-size: 1.7em; font-weight: bold; }
        .msg { margin: 15px 0; padding: 12px; border-radius: 7px; }
        .msg-error { background: #ffcccc; color: #560000; }
        .msg-success { background: #e0ffe0; color: #004400; }
    </style>
    <script>
        function toggleSession() {
            var grade = document.getElementById('grade_select').value;
            var sessionDiv = document.getElementById('session_div');
            if (grade === 'Nursery' || grade === 'Kindergarten') {
                sessionDiv.style.display = 'block';
            } else {
                sessionDiv.style.display = 'none';
            }
        }
        window.onload = function() {
            toggleSession();
        };
    </script>
</head>
<body>
    <div class="menu-bar">
        <a href="dashboard.php" class="btn">&larr; Back to Dashboard</a>
        <a href="?clear=1" class="btn" style="background:#ff4444;">Clear Results</a>
    </div>
    <div class="container">
        <img src="school_logo.png" class="logo-center">
        <div class="form-title">SDAES QRCode Generator</div>
        <?php foreach($success_msgs as $msg): ?>
            <div class="msg msg-success"><?= $msg ?></div>
        <?php endforeach; ?>
        <?php foreach($error_msgs as $msg): ?>
            <div class="msg msg-error"><?= $msg ?></div>
        <?php endforeach; ?>
        <form method="POST" enctype="multipart/form-data">
            <label for="grade_select">Select Grade Level:</label>
            <select id="grade_select" name="grade_select" required onchange="toggleSession()">
                <option value="Nursery" <?= ($selected_grade == 'Nursery' ? 'selected' : '') ?>>Nursery</option>
                <option value="Kindergarten" <?= ($selected_grade == 'Kindergarten' ? 'selected' : '') ?>>Kindergarten</option>
                <option value="Grade 1" <?= ($selected_grade == 'Grade 1' ? 'selected' : '') ?>>Grade 1</option>
                <option value="Grade 2" <?= ($selected_grade == 'Grade 2' ? 'selected' : '') ?>>Grade 2</option>
                <option value="Grade 3" <?= ($selected_grade == 'Grade 3' ? 'selected' : '') ?>>Grade 3</option>
                <option value="Grade 4" <?= ($selected_grade == 'Grade 4' ? 'selected' : '') ?>>Grade 4</option>
                <option value="Grade 5" <?= ($selected_grade == 'Grade 5' ? 'selected' : '') ?>>Grade 5</option>
                <option value="Grade 6" <?= ($selected_grade == 'Grade 6' ? 'selected' : '') ?>>Grade 6</option>
            </select>
            <div id="session_div" style="display:none;">
                <label for="session_select">Select Session:</label>
                <select id="session_select" name="session_select">
                    <option value="Morning" <?= ($selected_session == 'Morning' ? 'selected' : '') ?>>Morning</option>
                    <option value="Afternoon" <?= ($selected_session == 'Afternoon' ? 'selected' : '') ?>>Afternoon</option>
                </select>
            </div>
            <input type="file" name="excel_file" accept=".xlsx" required>
            <button type="submit">🟦 Generate All QR Codes</button>
        </form>
        <?php if (isset($_SESSION['preview']) && file_exists($_SESSION['preview'])): ?>
            <img src="<?= $_SESSION['preview'] ?>" alt="Preview QR">
        <?php endif; ?>

        <?php if (isset($_SESSION['zips'])): ?>
            <?php foreach ($_SESSION['zips'] as $z): ?>
                <a class="download-link" href="qr_codes/<?= htmlspecialchars($z['safe']) ?>_QRCodes.zip" download>
                    ⬇ Download ZIP for <?= htmlspecialchars($z['display']) ?>
                </a>
            <?php endforeach; ?>
        <?php endif; ?>
        <?php if (isset($_SESSION['students_list']) && count($_SESSION['students_list'])): ?>
        <table>
            <tr>
                <th style="width:18%;">LRN</th>
                <th style="width:23%;">Name</th>
                <th style="width:13%;">Grade</th>
                <th style="width:9%;">Sex</th>
                <th style="width:19%;">Birthday</th>
                <th style="width:18%;">Contact</th>
            </tr>
            <?php foreach ($_SESSION['students_list'] as $stud): ?>
                <?php
                $lname = isset($stud['last_name']) ? strtoupper(trim($stud['last_name'])) : '';
                $fname = isset($stud['first_name']) ? strtoupper(trim($stud['first_name'])) : '';
                $mname = isset($stud['middle_name']) ? strtoupper(trim($stud['middle_name'])) : '';
                $full = trim(($lname ? $lname . ', ' : '') . $fname . ($mname ? ' ' . $mname : ''));
                ?>
                <tr>
                    <td><?= htmlspecialchars($stud['lrn']) ?></td>
                    <td><?= htmlspecialchars($full) ?></td>
                    <td><?= htmlspecialchars($stud['grade']) ?></td>
                    <td><?= htmlspecialchars($stud['sex']) ?></td>
                    <td><?= htmlspecialchars($stud['birthday']) ?></td>
                    <td><?= htmlspecialchars($stud['contact']) ?></td>
                </tr>
            <?php endforeach; ?>
        </table>
        <?php endif; ?>
    </div>
</body>
</html>