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

function extractCleanedData($file) {
    $spreadsheet = IOFactory::load($file);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();
    $dataRows = array_slice($rows, 6); // skip headers

    $cleaned = [];
    foreach ($dataRows as $row) {
        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'  => 'Cebuano',
            'RELIGION'  => 'Roman Catholic',
            'BARANGAY'  => 'Poblacion',
            'CITY'      => 'Siocon',
            'PROVINCE'  => 'Zamboanga del Norte',
            'FATHER'    => $row[26],
            'MOTHER'    => $row[27],
            'GUARDIAN'  => $row[36],
            'NUMBER'    => $row[42],
            'REL'       => $row[40],
            'GRADE'     => '6'
        ];
    }
    return $cleaned;
}

// Handle Excel download
session_start();
if (isset($_GET['download']) && isset($_SESSION['cleaned_data'])) {
    $data = $_SESSION['cleaned_data'];
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Adjust column width and row height
    foreach (range('A', 'O') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    $sheet->getDefaultRowDimension()->setRowHeight(20); // optional: adjust row height

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

    // Fill data row-by-row, handle LRN as string
    $rowIndex = 2;
    foreach ($data as $row) {
        $colIndex = 'A';
        foreach ($row as $key => $value) {
            if ($key === 'LRN') {
                $sheet->setCellValueExplicit($colIndex . $rowIndex, $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.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

$data = [];
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $file = $_FILES['excel_file']['tmp_name'];
    $data = extractCleanedData($file);
    $_SESSION['cleaned_data'] = $data;
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>SF1 Cleaned Format Generator</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; }
        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: #f2f2f2; }
        .btn { margin-top: 10px; margin-right: 10px; }
        @media print { .no-print { display: none; } }
    </style>
</head>
<body>
    <h2>SF1 Cleaned Format Generator</h2>
    <form method="POST" enctype="multipart/form-data" class="no-print">
        <input type="file" name="excel_file" required>
        <button type="submit">Upload & Convert</button>
    </form>

    <?php if (!empty($data)): ?>
        <a href="?download=true" class="btn no-print">⬇ Download as Excel</a>
        <button onclick="window.print()" class="no-print btn">🖨 Print</button>
        <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 $row): ?>
                    <tr>
                        <?php foreach ($row as $cell): ?>
                            <td><?php echo htmlspecialchars($cell); ?></td>
                        <?php endforeach; ?>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php endif; ?>
</body>
</html>