<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

session_start();

// Initialize session-based temp LRN counter for unique values
if (!isset($_SESSION['temp_lrn_counter'])) {
    $_SESSION['temp_lrn_counter'] = 1;
}

function extractScheduleFromFilename($filename) {
    $lower = strtolower($filename);
    if (strpos($lower, 'morning') !== false) return 'Morning';
    if (strpos($lower, 'am') !== false) return 'Morning';
    if (strpos($lower, 'afternoon') !== false) return 'Afternoon';
    if (strpos($lower, 'pm') !== false) return 'Afternoon';
    return '';
}

function extractCleanedData($file, $grade, $schedule = '') {
    global $_SESSION;
    $spreadsheet = IOFactory::load($file);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();
    $dataRows = array_slice($rows, 6); // skip headers
    $cleaned = [];
    $_SESSION['grade'] = $grade;

    foreach ($dataRows as $row) {
        if($grade == "Nursery" || $grade == "Kindergarten") {
            if (empty($row[2]) || str_contains(strtoupper($row[2]), 'TOTAL') || str_contains(strtoupper($row[2]), 'COMBINED')) continue;
            // Generate unique TEMP LRN using session counter
            $tempLrn = sprintf('TEMP-%04d', $_SESSION['temp_lrn_counter']++);
            $cleaned[] = [
                'LRN'       => $tempLrn,
                'NAME'      => $row[2],
                'SEX'       => $row[6],
                'BDAY'      => $row[7],
                'LANGUAGE'  => $row[11],
                'RELIGION'  => $row[14],
                'BARANGAY'  => $row[17],
                'CITY'      => $row[20],
                'PROVINCE'  => 'Zamboanga del Norte',
                'FATHER'    => $row[27],
                'MOTHER'    => $row[31],
                'GUARDIAN'  => '', // leave blank for nursery/kinder
                'NUMBER'    => '0' . $row[36], // CONTACT NUMBER from $row[36], not $row[41]
                'REL'       => $row[40],
                'GRADE'     => $grade,
                'SCHED'     => $schedule,
            ];
        } else {
            if (empty($row[0]) || empty($row[2]) || str_contains(strtoupper($row[2]), 'TOTAL') || str_contains(strtoupper($row[2]), 'COMBINED')) continue;
            $cleaned[] = [
                'LRN'       => $row[0],
                'NAME'      => $row[2],
                'SEX'       => $row[6],
                'BDAY'      => $row[7],
                'LANGUAGE'  => $row[11],
                'RELIGION'  => $row[14],
                'BARANGAY'  => $row[17],
                'CITY'      => $row[20],
                'PROVINCE'  => 'Zamboanga del Norte',
                'FATHER'    => $row[27],
                'MOTHER'    => $row[31],
                'GUARDIAN'  => $row[37],
                'NUMBER'    => '0' . $row[36],
                'REL'       => $row[40],
                'GRADE'     => $grade,
            ];
        }
    }
    return $cleaned;
}

$schedule_prompt_needed = false;
$filename_schedule = '';

// Handle saving of edits
if (isset($_POST['save_edited_table']) && isset($_POST['table_data'])) {
    $edited = json_decode($_POST['table_data'], true);
    $_SESSION['cleaned_data'] = $edited;
    $data = $edited;
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $file = $_FILES['excel_file']['tmp_name'];
    $grade = $_POST['grade'];

    if ($grade == "Nursery" || $grade == "Kindergarten") {
        $filename = $_FILES['excel_file']['name'];
        $filename_schedule = extractScheduleFromFilename($filename);

        if (empty($filename_schedule) && isset($_POST['sched_select'])) {
            $filename_schedule = $_POST['sched_select'];
        }

        if (empty($filename_schedule)) {
            $schedule_prompt_needed = true;
        } else {
            $data = extractCleanedData($file, $grade, $filename_schedule);
            $_SESSION['cleaned_data'] = $data;
            $_SESSION['sched'] = $filename_schedule;
        }
    } else {
        $data = extractCleanedData($file, $grade);
        $_SESSION['cleaned_data'] = $data;
        $_SESSION['sched'] = '';
    }
} elseif (isset($_POST['clear_data'])) {
    unset($_SESSION['cleaned_data']);
    unset($_SESSION['sched']);
    unset($_SESSION['temp_lrn_counter']); // also reset LRN counter
    $data = [];
} else {
    $data = isset($_SESSION['cleaned_data']) ? $_SESSION['cleaned_data'] : null;
}

// Handle Excel download
if (isset($_GET['download']) && isset($_SESSION['cleaned_data'])) {
    $grade = $_SESSION['grade'];
    $data = $_SESSION['cleaned_data'];
    $sched = '';
    if (($grade === 'Nursery' || $grade === 'Kindergarten') && !empty($data[0]['SCHED'])) {
        $sched = '_' . $data[0]['SCHED'];
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    foreach (range('A', chr(65+count($data[0])-1)) as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    $sheet->getDefaultRowDimension()->setRowHeight(20);

    $headers = array_keys($data[0]);
    $sheet->fromArray($headers, NULL, 'A1');

    $rowIndex = 2;
    foreach ($data as $i => $row) {
        $colIndex = 'A';
        foreach ($row as $key => $value) {
            if ($key === 'LRN') {
                $sheet->setCellValueExplicit($colIndex . $rowIndex, $value, DataType::TYPE_STRING);
            } elseif ($key === '88NUMBER') {
                $sheet->setCellValueExplicit($colIndex . $rowIndex, "0" . $value, DataType::TYPE_STRING);
            } else {
                $sheet->setCellValue($colIndex . $rowIndex, $value);
            }
            $colIndex++;
        }
        $rowIndex++;
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="SF1_CLEANED_OUTPUT_GRADE_' . $grade . $sched . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>SF1 Cleaned Format Generator</title>
    <style>
        :root {
            --bg: #fff;
            --text: #232323;
            --th: #f2f2f2;
            --btn-bg: #007bff;
            --btn-hover: #0056b3;
        }
        body.dark-mode {
            --bg: #181818;
            --text: #e3e3e3;
            --th: #242424;
            --btn-bg: #343a40;
            --btn-hover: #23272b;
        }
        body { font-family: Arial, sans-serif; padding: 20px; background: var(--bg); color: var(--text); transition: background 0.2s, color 0.2s; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; table-layout: fixed; word-wrap: break-word; }
        th, td { border: 1px solid #000; padding: 6px; text-align: left; font-size: 13px; }
        th { background-color: var(--th); }
        .btn { margin-top: 10px; margin-right: 10px; padding: 6px 14px; border: none; border-radius: 4px; background: var(--btn-bg); color: #fff; cursor: pointer; text-decoration: none; display: inline-block;}
        .btn:hover { background: var(--btn-hover); }
        .header-btns { display: flex; gap: 12px; margin-bottom: 20px; }
        input.table-input { width: 100%; border: none; background: transparent; color: inherit; font-size: inherit; padding: 2px 4px;}
        @media print { .no-print { display: none; } }
        .sched-select-form { margin: 20px 0; }
        .sched-select-form label { font-weight: bold; }
    </style>
</head>
<body>
    <div class="header-btns no-print">
        <a href="dashboard.php" class="btn">⏪ Back to Dashboard</a>
        <button type="button" class="btn" id="toggle-darkmode">🌙 Dark Mode</button>
        <form method="POST" style="display:inline;">
            <button type="submit" class="btn" name="clear_data" value="1" onclick="return confirm('Clear all viewed SF1 data?');">🗑 Clear</button>
        </form>
    </div>
    <h2>SF1 Cleaned Format Generator</h2>
    <form method="POST" enctype="multipart/form-data" class="no-print">
        <input type="file" name="excel_file" required>
        <label for="grade">Grade: </label>
        <select id="grade" name="grade" style='width:100px'>
            <option value="">All</option>
            <option value="Nursery">Nursery</option>
            <option value="Kindergarten">Kindergarten</option>
            <?php for ($i = 1; $i <= 6; $i++): ?>
                <option value="<?= $i ?>">Grade <?= $i ?></option>
            <?php endfor; ?>
        </select>
        <button type="submit">Upload & Convert</button>
    </form>

    <?php
    if ($schedule_prompt_needed):
    ?>
        <form method="POST" enctype="multipart/form-data" class="sched-select-form no-print">
            <input type="hidden" name="grade" value="<?= htmlspecialchars($_POST['grade']) ?>">
            <input type="hidden" name="excel_file" value="<?= htmlspecialchars($_FILES['excel_file']['tmp_name']) ?>">
            <label>Select Schedule for Nursery/Kindergarten:</label>
            <select name="sched_select" required>
                <option value="">-- Select --</option>
                <option value="Morning">Morning</option>
                <option value="Afternoon">Afternoon</option>
            </select>
            <input type="hidden" name="sched_prompt" value="1">
            <button type="submit" class="btn">Continue</button>
        </form>
        <script>
        document.querySelectorAll("form:not(.sched-select-form), table, p, h2").forEach(function(el) {
            el.style.display = "none";
        });
        </script>
    <?php endif; ?>

    <?php if (!empty($data) && !$schedule_prompt_needed): ?>
        <p><strong>Total Students: <?php echo count($data); ?></strong></p>
        <form method="POST" id="editTableForm">
            <input type="hidden" name="table_data" id="table_data">
            <button type="button" class="btn no-print" onclick="saveTableEdits()">💾 Save</button>
            <a href="?download=true" class="btn no-print">⬇ Download as Excel</a>
            <button onclick="window.print()" class="no-print btn" type="button">🖨 Print</button>
            <input type="hidden" name="save_edited_table" value="1">
            <table>
                <thead>
                    <tr>
                        <?php foreach (array_keys($data[0]) as $col): ?>
                            <th><?php echo htmlspecialchars($col); ?></th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($data as $rowIdx => $row): ?>
                        <tr>
                            <?php foreach ($row as $colIdx => $cell): ?>
                                <td>
                                    <input
                                        class="table-input"
                                        type="text"
                                        value="<?php echo htmlspecialchars($cell); ?>"
                                        <?php echo $colIdx === 'LRN' ? 'readonly' : ''; ?>
                                        data-row="<?php echo $rowIdx; ?>"
                                        data-col="<?php echo htmlspecialchars($colIdx); ?>"
                                    >
                                </td>
                            <?php endforeach; ?>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </form>
        <script>
        function saveTableEdits() {
            let data = <?php echo json_encode($data); ?>;
            let inputs = document.querySelectorAll('input.table-input');
            inputs.forEach(function(input) {
                let r = input.getAttribute('data-row');
                let c = input.getAttribute('data-col');
                data[r][c] = input.value;
            });
            document.getElementById('table_data').value = JSON.stringify(data);
            document.getElementById('editTableForm').submit();
        }
        </script>
    <?php endif; ?>

    <script>
    function setDarkMode(enabled) {
        document.body.classList.toggle('dark-mode', enabled);
        localStorage.setItem('darkmode', enabled ? '1' : '0');
        document.getElementById('toggle-darkmode').innerText = enabled ? '☀️ Light Mode' : '🌙 Dark Mode';
    }
    document.getElementById('toggle-darkmode').onclick = function() {
        setDarkMode(!document.body.classList.contains('dark-mode'));
    };
    (function() {
        const enabled = localStorage.getItem('darkmode') === '1';
        setDarkMode(enabled);
    })();
    </script>
</body>
</html>